Admin:db

From The Scuba Wiki

Latest revision as of 23:53, 25 April 2010

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:

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 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?)
bottomdepth - 2003 - float in ft
cbfrequency - 2457 - text
chamber - 9 - text
collect - 915 - 0 or 1 (need to verify)
current - 917 - text
divetype - 2945 - 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

Below is an example of the output for s_id 2043 (Lake Nowhere Test).

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)

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.

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)

There is one other table that might be of interest to us that is used to store EMS comments.

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    |       | 
+------------+-----------------+------+-----+---------+-------+