Admin:db

From The Scuba Wiki

Revision as of 19:22, 11 April 2010

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