Admin:db

From The Scuba Wiki

(Difference between revisions)
Jump to: navigation, search
(→DB Stuff)
 
(4 intermediate revisions not shown)
Line 1: Line 1:
===== DB Stuff =====
===== DB Stuff =====
-
<s>Note, edit this page to actually be able to see anything.  It's gibberish in wiki format.</s>
+
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
Categories can easily be searched in the categorylinks table
Line 32: Line 32:
</pre>
</pre>
-
SMW makes it's own ID, different than the MW page ID from the page table.
+
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_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    |                |
+
-
+---------------+-----------------+------+-----+---------+----------------+
+
-
</pre>
+
-
 
+
-
Semantic Media Wiki Tables:
+
-
 
+
-
<pre>
+
-
| smw_atts2        |
+
-
| smw_conc2        |
+
-
| smw_conccache    |
+
-
| smw_ids          |
+
-
| smw_inst2        |
+
-
| smw_redi2        |
+
-
| smw_rels2        |
+
-
| smw_spec2        |
+
-
| smw_subs2        |
+
-
| smw_text2        |
+
-
</pre>
+
<pre>
<pre>
Line 74: Line 46:
+------------+-----------------+------+-----+---------+-------+
+------------+-----------------+------+-----+---------+-------+
</pre>
</pre>
-
Here is where the SMW attributes appear to be.s_id corresponds with the smw_id from the smw_ids table.  p_id appears to be a property ID or some such.
 
-
s_id - corresponds with the smw_id from the smw_ids table, this can reference a page or other SMW properties
+
'''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.
+
'''p_id''' - appears to be a property ID, for like depth, temp, etc.<br>
-
value_unit - appears to always be NULL
+
'''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
+
'''value_xsd''' - is the primary value, most only utilize this<br>
-
value_num - not quite sure yet
+
'''value_num''' - for those fields that are populated with a numerical value_xsd and this are both set to that numerical value<br>
-
<pre>
+
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.
-
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    |      |
+
-
+------------------+-----------------+------+-----+---------+-------+
+
-
</pre>
+
-
This is for SMW concepts, not really used at this time.
+
unknown - 52 - a date code like 2010/2/15T18:28:28 (possibly create or last edit date?)<br>
-
 
+
bottomdepth - 2003 - float in ft<br>
-
<pre>
+
cbfrequency - 2457 - text<br>
-
mysql> describe smw_conccache;
+
chamber - 9 - text<br>
-
+-------+-----------------+------+-----+---------+-------+
+
collect - 915 - 0 or 1 (need to verify)<br>
-
| Field | Type            | Null | Key | Default | Extra |
+
current - 917 - text<br>
-
+-------+-----------------+------+-----+---------+-------+
+
divetype - 2945 - text<br>
-
| s_id  | int(8) unsigned | NO  |    | NULL    |      |
+
emsnumber - 5 - text<br>
-
| o_id  | int(8) unsigned | NO  | MUL | NULL    |      |
+
externalurl - 2441 - text<br>
-
+-------+-----------------+------+-----+---------+-------+
+
gpscoord - 52 - 60.074844444444,-107.99569722222<br>
-
</pre>
+
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>
-
This table is currently empty.
+
Below is an example of the output for s_id 2043 (Lake Nowhere Test).
<pre>
<pre>
-
mysql> describe smw_ids;
+
mysql> select * from smw_atts2 where s_id like 2043;
-
+---------------+-----------------+------+-----+---------+----------------+
+
+------+------+------------+----------------------------------+-----------------+
-
| Field        | Type            | Null | Key | Default | Extra          |
+
| s_id | p_id | value_unit | value_xsd                        | value_num      |
-
+---------------+-----------------+------+-----+---------+----------------+
+
+------+------+------------+----------------------------------+-----------------+
-
| smw_id        | int(8) unsigned | NO   | PRI | NULL   | auto_increment |  
+
| 2043 |  52 | NULL       | 2010/2/15T18:28:28              | 2455243.7697685 |  
-
| smw_namespace | int(11)         | NO  |     | NULL   |               |  
+
| 2043 | 2003 | ft         | 3280.8398950131                  | 3280.8398950131 |
-
| smw_title    | varbinary(255) | NO  | MUL | NULL   |               |  
+
| 2043 | 2457 | NULL       | 7                                |            NULL |  
-
| smw_iw        | varchar(32)    | YES |     | NULL   |               |  
+
| 2043 | 2461 | NULL      | 9                                |            NULL |
-
| smw_sortkey  | varbinary(255) | NO  | MUL | 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>
</pre>
-
smw_id - This is a unique ID given to many things including pages
+
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.
-
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
+
<pre>
<pre>
-
mysql> describe smw_inst2;
+
mysql> select * from smw_ids where smw_id like 2043;
-
+-------+-----------------+------+-----+---------+-------+
+
+--------+---------------+-------------------+--------+-------------------+
-
| Field | Type            | Null | Key | Default | Extra |
+
| smw_id | smw_namespace | smw_title        | smw_iw | smw_sortkey      |
-
+-------+-----------------+------+-----+---------+-------+
+
+--------+---------------+-------------------+--------+-------------------+
-
| s_id  | int(8) unsigned | NO   | MUL | NULL    |       |
+
|  2043 |             0 | Lake_Nowhere_Test | NULL   | Lake Nowhere Test |  
-
| o_id  | int(8) unsigned | NO   | MUL | NULL    |      |  
+
+--------+---------------+-------------------+--------+-------------------+
-
+-------+-----------------+------+-----+---------+-------+
+
1 row in set (0.01 sec)
</pre>
</pre>
-
s_id - This is the SMW ID
+
There is one other table that might be of interest to us that is used to store EMS comments.
-
o_id - Not sure what this is
+
<pre>
<pre>
-
mysql> describe smw_redi2;
+
mysql> describe smw_text2;
-
+-------------+-----------------+------+-----+---------+-------+
+
-
| 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    |      |
+
-
+-------------+-----------------+------+-----+---------+-------+
+
-
</pre>
+
-
 
+
-
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
+
-
 
+
-
<pre>
+
-
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    |      |
+
-
+-------+-----------------+------+-----+---------+-------+
+
-
</pre>
+
-
 
+
-
Not sure what this is
+
-
 
+
-
s_id - SMW id
+
-
p_id - Property ID?
+
-
o_id - No idea
+
-
 
+
-
<pre>
+
-
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    |      |
+
-
+--------------+-----------------+------+-----+---------+-------+
+
-
</pre>
+
-
 
+
-
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
+
-
 
+
-
<pre>
+
-
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    |      |
+
-
+-------+-----------------+------+-----+---------+-------+
+
-
</pre>
+
-
 
+
-
Not really sure what this is.
+
-
 
+
-
<pre>
+
-
mysql> describe smw_subs2;mysql> describe smw_text2;
+
+------------+-----------------+------+-----+---------+-------+
+------------+-----------------+------+-----+---------+-------+
| Field      | Type            | Null | Key | Default | Extra |
| Field      | Type            | Null | Key | Default | Extra |
Line 217: Line 129:
+------------+-----------------+------+-----+---------+-------+
+------------+-----------------+------+-----+---------+-------+
</pre>
</pre>
-
 
-
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
 

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    |       | 
+------------+-----------------+------+-----+---------+-------+
Retrieved from "http://thescubawiki.com/Admin:db"
support the site