Admin:db
From The Scuba Wiki
DB Stuff
Note, edit this page to actually be able to see anything. It's gibberish in wiki format.
Categories can easily be searched in the categorylinks table
select * FROM categorylinks WHERE cl_to = 'Test'
Here is the DB structure:
mysql> describe categorylinks;<br> +--------------+------------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+-------------------+-------+ | cl_from | int(10) unsigned | NO | PRI | 0 | | | cl_to | varbinary(255) | NO | PRI | NULL | | | cl_sortkey | varbinary(70) | NO | | NULL | | | cl_timestamp | timestamp | YES | | CURRENT_TIMESTAMP | | +--------------+------------------+------+-----+-------------------+-------+ Text fields are stored here: mysql> describe smw_text2;<br> +------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-----------------+------+-----+---------+-------+ | s_id | int(8) unsigned | NO | MUL | NULL | | | p_id | int(8) unsigned | NO | MUL | NULL | | | value_blob | mediumblob | YES | | NULL | | +------------+-----------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
I have now deciphered how things are stored within the smw tables. Almost everything we're looking for is in the smw_atts2 table. This table has the following table description.
mysql> describe smw_atts2; +------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-----------------+------+-----+---------+-------+ | s_id | int(8) unsigned | NO | MUL | NULL | | | p_id | int(8) unsigned | NO | MUL | NULL | | | value_unit | varchar(63) | YES | | NULL | | | value_xsd | varbinary(255) | NO | MUL | NULL | | | value_num | double | YES | MUL | NULL | | +------------+-----------------+------+-----+---------+-------+
s_id - corresponds with the smw_id from the smw_ids table, this references the page for the dive site\\ p_id - appears to be a property ID, for like depth, temp, etc.\\ value_unit - This describes what the value_xsd is, for example ft, or K for kelvin (temp)\\ value_xsd - is the primary value, most only utilize this\\ value_num - for those fields that are populated with a numerical value_xsd and this are both set to that numerical value\\
s_id is the smw_id and is how all things within smw are referenced back to a page. Below are the varibles we use and the p_id (property ID I presume) for that variable as well as the format of that p_id in the value_xsd
unknown - 52 - a date code like 2010/2/15T18:28:28 (possibly create or last edit date?) bottomdepth - 2003 - float in ft cbfrequency - 2457 - text chamber - 9 - text collect - 915 - 0 or 1 (need to verify) current - 917 - text emsnumber - 5 - text externalurl - 2441 - text gpscoord - 52 - 60.074844444444,-107.99569722222 hospital - 2460 - text skilllevel - 1008 - text spear - 923 - 0 or 1 (need to verify) summertemp - 2000 - float in kelvin techdivetype - 2947 - text topdepth - 2002 - float in ft vhffrequency - 2456 - text watertype - 1043 - text wintertemp - 2001 - float in kelvin
mysql> select * from smw_atts2 where s_id like 2043; +------+------+------------+----------------------------------+-----------------+ | s_id | p_id | value_unit | value_xsd | value_num | +------+------+------------+----------------------------------+-----------------+ | 2043 | 52 | NULL | 2010/2/15T18:28:28 | 2455243.7697685 | | 2043 | 2003 | ft | 3280.8398950131 | 3280.8398950131 | | 2043 | 2457 | NULL | 7 | NULL | | 2043 | 2461 | NULL | 9 | NULL | | 2043 | 915 | NULL | 0 | 0 | | 2043 | 917 | NULL | None | NULL | | 2043 | 2452 | NULL | 5 | NULL | | 2043 | 2441 | NULL | http://www.testurl.url1 | NULL | | 2043 | 910 | NULL | 60.074844444444,-107.99569722222 | NULL | | 2043 | 2460 | NULL | 8 | NULL | | 2043 | 1008 | NULL | Novice | NULL | | 2043 | 1008 | NULL | Experienced | NULL | | 2043 | 923 | NULL | 1 | 1 | | 2043 | 2000 | K | 255.92777777778 | 255.92777777778 | | 2043 | 2947 | NULL | Cave | NULL | | 2043 | 2002 | ft | 9.8425196850394 | 9.8425196850394 | | 2043 | 2456 | NULL | 6 | NULL | | 2043 | 1043 | NULL | Fresh Water | NULL | | 2043 | 2001 | K | 256.48333333333 | 256.48333333333 | +------+------+------------+----------------------------------+-----------------+ 19 rows in set (0.03 sec)
SMW makes it's own ID, different than the MW page ID from the page table.
mysql> describe smw_ids; +---------------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-----------------+------+-----+---------+----------------+ | smw_id | int(8) unsigned | NO | PRI | NULL | auto_increment | | smw_namespace | int(11) | NO | | NULL | | | smw_title | varbinary(255) | NO | MUL | NULL | | | smw_iw | varchar(32) | YES | | NULL | | | smw_sortkey | varbinary(255) | NO | MUL | NULL | | +---------------+-----------------+------+-----+---------+----------------+
Semantic Media Wiki Tables:
| smw_atts2 | | smw_conc2 | | smw_conccache | | smw_ids | | smw_inst2 | | smw_redi2 | | smw_rels2 | | smw_spec2 | | smw_subs2 | | smw_text2 |
mysql> describe smw_conc2; +------------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-----------------+------+-----+---------+-------+ | s_id | int(8) unsigned | NO | PRI | NULL | | | concept_txt | mediumblob | YES | | NULL | | | concept_docu | mediumblob | YES | | NULL | | | concept_features | int(8) | YES | | NULL | | | concept_size | int(8) | YES | | NULL | | | concept_depth | int(8) | YES | | NULL | | | cache_date | int(8) unsigned | YES | | NULL | | | cache_count | int(8) unsigned | YES | | NULL | | +------------------+-----------------+------+-----+---------+-------+
This is for SMW concepts, not really used at this time.
mysql> describe smw_conccache; +-------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------+------+-----+---------+-------+ | s_id | int(8) unsigned | NO | | NULL | | | o_id | int(8) unsigned | NO | MUL | NULL | | +-------+-----------------+------+-----+---------+-------+
This table is currently empty.
mysql> describe smw_ids; +---------------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-----------------+------+-----+---------+----------------+ | smw_id | int(8) unsigned | NO | PRI | NULL | auto_increment | | smw_namespace | int(11) | NO | | NULL | | | smw_title | varbinary(255) | NO | MUL | NULL | | | smw_iw | varchar(32) | YES | | NULL | | | smw_sortkey | varbinary(255) | NO | MUL | NULL | | +---------------+-----------------+------+-----+---------+----------------+
smw_id - This is a unique ID given to many things including pages smw_namespace - This is the name space this page belongs to, currently all are in name space 0 smw_title - This is the title of the page smw_iw - This is set to NULL on all pages, not sure what this is smw_sort_key - This is the title of the page again
mysql> describe smw_inst2; +-------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------+------+-----+---------+-------+ | s_id | int(8) unsigned | NO | MUL | NULL | | | o_id | int(8) unsigned | NO | MUL | NULL | | +-------+-----------------+------+-----+---------+-------+
s_id - This is the SMW ID o_id - Not sure what this is
mysql> describe smw_redi2; +-------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------+------+-----+---------+-------+ | s_title | varbinary(255) | NO | MUL | NULL | | | s_namespace | int(11) | NO | | NULL | | | o_id | int(8) unsigned | NO | MUL | NULL | | +-------------+-----------------+------+-----+---------+-------+
This appears to be some sort of table for storing redirect information for pages, not 100% sure on that though
s_title - SMW page title s_namespace - SMW name space o_id - No idea
mysql> describe smw_rels2; +-------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------+------+-----+---------+-------+ | s_id | int(8) unsigned | NO | MUL | NULL | | | p_id | int(8) unsigned | NO | MUL | NULL | | | o_id | int(8) unsigned | NO | MUL | NULL | | +-------+-----------------+------+-----+---------+-------+
Not sure what this is
s_id - SMW id p_id - Property ID? o_id - No idea
mysql> describe smw_spec2; +--------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------+------+-----+---------+-------+ | s_id | int(8) unsigned | NO | MUL | NULL | | | p_id | int(8) unsigned | NO | MUL | NULL | | | value_string | varbinary(255) | NO | | NULL | | +--------------+-----------------+------+-----+---------+-------+
Appears to define allowed values for a given s_id (water type for example).
s_id - SMW id for the SMW variable p_id - Not real sure if this is property id or not now value_string - One of the allowed value strings for that ID
mysql> describe smw_subs2; +-------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------+------+-----+---------+-------+ | s_id | int(8) unsigned | NO | MUL | NULL | | | o_id | int(8) unsigned | NO | MUL | NULL | | +-------+-----------------+------+-----+---------+-------+
Not really sure what this is.
mysql> describe smw_subs2;mysql> describe smw_text2; +------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-----------------+------+-----+---------+-------+ | s_id | int(8) unsigned | NO | MUL | NULL | | | p_id | int(8) unsigned | NO | MUL | NULL | | | value_blob | mediumblob | YES | | NULL | | +------------+-----------------+------+-----+---------+-------+
This appears to be text based inputs from pages. Comments under EMS section for instance.
s_id - SMW id p_id - property id? value_blob - The text being stored
