Admin:db

From The Scuba Wiki

You do not have permission to edit this page, for the following reason:

You can view and copy the source of this page:

===== DB Stuff ===== This page describes how to search the database directly for information and how things are laid out. Categories can easily be searched in the categorylinks table select * FROM categorylinks WHERE cl_to = 'Test' Here is the DB structure: <pre> 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) </pre> 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. <pre> 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 | | +------------+-----------------+------+-----+---------+-------+ </pre> '''s_id''' - corresponds with the smw_id from the smw_ids table, this references the page for the dive site<br> '''p_id''' - appears to be a property ID, for like depth, temp, etc.<br> '''value_unit''' - This describes what the value_xsd is, for example ft, or K for kelvin (temp)<br> '''value_xsd''' - is the primary value, most only utilize this<br> '''value_num''' - for those fields that are populated with a numerical value_xsd and this are both set to that numerical value<br> s_id is what ties all the smw tables together and is how all things within smw are referenced back to a page. Below I have documented the p_id value for the various variables on the site pages. Format is <variable name> - <p_id> - <format they are stored in>. The value will be stored in the value_xsd field. unknown - 52 - a date code like 2010/2/15T18:28:28 (possibly create or last edit date?)<br> bottomdepth - 2003 - float in ft<br> cbfrequency - 2457 - text<br> chamber - 9 - text<br> collect - 915 - 0 or 1 (need to verify)<br> current - 917 - text<br> divetype - 2945 - text<br> emsnumber - 5 - text<br> externalurl - 2441 - text<br> gpscoord - 52 - 60.074844444444,-107.99569722222<br> hospital - 2460 - text<br> skilllevel - 1008 - text<br> spear - 923 - 0 or 1 (need to verify)<br> summertemp - 2000 - float in kelvin<br> techdivetype - 2947 - text<br> topdepth - 2002 - float in ft<br> vhffrequency - 2456 - text<br> watertype - 1043 - text<br> wintertemp - 2001 - float in kelvin<br> Below is an example of the output for s_id 2043 (Lake Nowhere Test). <pre> 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) </pre> To get the page name for an s_id (or vice versa, just change the query) you just need to check the smw_ids table, as below. <pre> mysql> select * from smw_ids where smw_id like 2043; +--------+---------------+-------------------+--------+-------------------+ | smw_id | smw_namespace | smw_title | smw_iw | smw_sortkey | +--------+---------------+-------------------+--------+-------------------+ | 2043 | 0 | Lake_Nowhere_Test | NULL | Lake Nowhere Test | +--------+---------------+-------------------+--------+-------------------+ 1 row in set (0.01 sec) </pre> There is one other table that might be of interest to us that is used to store EMS comments. <pre> 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 | | +------------+-----------------+------+-----+---------+-------+ </pre>

Return to Admin:db.