Admin:db

From The Scuba Wiki

(Difference between revisions)
Jump to: navigation, search
(→DB Stuff)
(→DB Stuff)
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<br>
+
'''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>
+
'''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_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_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>
+
'''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
+
emsnumber - 5 - text<br>
-
externalurl - 2441 - text
+
externalurl - 2441 - text<br>
-
gpscoord - 52 - 60.074844444444,-107.99569722222
+
gpscoord - 52 - 60.074844444444,-107.99569722222<br>
-
hospital - 2460 - text
+
hospital - 2460 - text<br>
-
skilllevel - 1008 - text
+
skilllevel - 1008 - text<br>
-
spear - 923 - 0 or 1 (need to verify)
+
spear - 923 - 0 or 1 (need to verify)<br>
-
summertemp - 2000 - float in kelvin
+
summertemp - 2000 - float in kelvin<br>
-
techdivetype - 2947 - text
+
techdivetype - 2947 - text<br>
-
topdepth - 2002 - float in ft
+
topdepth - 2002 - float in ft<br>
-
vhffrequency - 2456 - text
+
vhffrequency - 2456 - text<br>
-
watertype - 1043 - text
+
watertype - 1043 - text<br>
-
wintertemp - 2001 - float in kelvin
+
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 104:
</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>
+
mysql> describe smw_text2;
-
| smw_atts2        |
+
-
| 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 127:
+------------+-----------------+------+-----+---------+-------+
+------------+-----------------+------+-----+---------+-------+
</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
 

Revision as of 19:46, 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 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
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 | | +------------+-----------------+------+-----+---------+-------+ </pre>

Retrieved from "http://thescubawiki.com/Admin:db"
Personal tools
support the site