Admin:db

From The Scuba Wiki

Revision as of 18:39, 15 February 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)

Here is where the SMW attributes appear to be. s_id appears to be the page identifier, but I still haven't found that elsewhere. p_id appears to be a property ID or some such, it is not the page id.

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

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