Admin:db

From The Scuba Wiki

(Difference between revisions)
Jump to: navigation, search
(→DB Stuff)
(→DB Stuff)
 
(3 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 47: Line 47:
</pre>
</pre>
-
s_id - corresponds with the smw_id from the smw_ids table, this references the page for the dive site\\
+
'''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 - This describes what the value_xsd is, for example ft, or K for kelvin (temp)\\
+
'''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 - for those fields that are populated with a numerical value_xsd and this are both set to that numerical value\\
+
'''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 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  
+
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?)
+
unknown - 52 - a date code like 2010/2/15T18:28:28 (possibly create or last edit date?)<br>
-
bottomdepth - 2003 - float in ft
+
bottomdepth - 2003 - float in ft<br>
-
cbfrequency - 2457 - text
+
cbfrequency - 2457 - text<br>
-
chamber - 9 - text
+
chamber - 9 - text<br>
-
collect - 915 - 0 or 1 (need to verify)
+
collect - 915 - 0 or 1 (need to verify)<br>
-
current - 917 - text
+
current - 917 - text<br>
-
emsnumber - 5 - text
+
divetype - 2945 - text<br>
-
externalurl - 2441 - text
+
emsnumber - 5 - text<br>
-
gpscoord - 52 - 60.074844444444,-107.99569722222
+
externalurl - 2441 - text<br>
-
hospital - 2460 - text
+
gpscoord - 52 - 60.074844444444,-107.99569722222<br>
-
skilllevel - 1008 - text
+
hospital - 2460 - text<br>
-
spear - 923 - 0 or 1 (need to verify)
+
skilllevel - 1008 - text<br>
-
summertemp - 2000 - float in kelvin
+
spear - 923 - 0 or 1 (need to verify)<br>
-
techdivetype - 2947 - text
+
summertemp - 2000 - float in kelvin<br>
-
topdepth - 2002 - float in ft
+
techdivetype - 2947 - text<br>
-
vhffrequency - 2456 - text
+
topdepth - 2002 - float in ft<br>
-
watertype - 1043 - text
+
vhffrequency - 2456 - text<br>
-
wintertemp - 2001 - float in kelvin
+
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>
<pre>
Line 102: Line 105:
</pre>
</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.
-
SMW makes it's own ID, different than the MW page ID from the page table.
+
<pre>
<pre>
-
mysql> describe smw_ids;
+
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      |
-
+---------------+-----------------+------+-----+---------+----------------+
+
+--------+---------------+-------------------+--------+-------------------+
-
| smw_id        | int(8) unsigned | NO   | PRI | NULL    | auto_increment |
+
|  2043 |             0 | Lake_Nowhere_Test | NULL  | Lake Nowhere Test |  
-
| smw_namespace | int(11)        | NO  |    | NULL   |                |
+
+--------+---------------+-------------------+--------+-------------------+
-
| smw_title    | varbinary(255)  | NO   | MUL | NULL    |                |
+
1 row in set (0.01 sec)
-
| smw_iw        | varchar(32)    | YES  |    | NULL    |                |
+
-
| smw_sortkey  | varbinary(255)  | NO  | MUL | NULL    |                |  
+
-
+---------------+-----------------+------+-----+---------+----------------+
+
</pre>
</pre>
-
Semantic Media Wiki Tables:
+
There is one other table that might be of interest to us that is used to store EMS comments.
<pre>
<pre>
-
| smw_atts2        |
+
mysql> describe smw_text2;
-
| smw_conc2        |
+
-
| smw_conccache    |
+
-
| smw_ids          |
+
-
| smw_inst2        |
+
-
| smw_redi2        |
+
-
| smw_rels2        |
+
-
| smw_spec2        |
+
-
| smw_subs2        |
+
-
| smw_text2        |
+
-
</pre>
+
-
 
+
-
 
+
-
 
+
-
<pre>
+
-
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.
+
-
 
+
-
<pre>
+
-
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    |      |
+
-
+-------+-----------------+------+-----+---------+-------+
+
-
</pre>
+
-
 
+
-
This table is currently empty.
+
-
 
+
-
<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>
+
-
 
+
-
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
+
-
 
+
-
<pre>
+
-
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    |      |
+
-
+-------+-----------------+------+-----+---------+-------+
+
-
</pre>
+
-
 
+
-
s_id - This is the SMW ID
+
-
o_id - Not sure what this is
+
-
 
+
-
<pre>
+
-
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    |      |
+
-
+-------------+-----------------+------+-----+---------+-------+
+
-
</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 270: 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