Admin:db

From The Scuba Wiki

Revision as of 18:32, 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.

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

Found the page id numbers, but they do not match up with s_id above, got to figure out what s_id matches up with:

mysql> describe page;
+-------------------+---------------------+------+-----+----------------+----------------+
| Field             | Type                | Null | Key | Default        | Extra          |
+-------------------+---------------------+------+-----+----------------+----------------+
| page_id           | int(10) unsigned    | NO   | PRI | NULL           | auto_increment | 
| page_namespace    | int(11)             | NO   | MUL | NULL           |                | 
| page_title        | varbinary(255)      | NO   |     | NULL           |                | 
| page_restrictions | tinyblob            | NO   |     | NULL           |                | 
| page_counter      | bigint(20) unsigned | NO   |     | 0              |                | 
| page_is_redirect  | tinyint(3) unsigned | NO   |     | 0              |                | 
| page_is_new       | tinyint(3) unsigned | NO   |     | 0              |                | 
| page_random       | double unsigned     | NO   | MUL | NULL           |                | 
| page_touched      | binary(14)          | NO   |     |                |                | 
| page_latest       | int(10) unsigned    | NO   |     | NULL           |                | 
| page_len          | int(10) unsigned    | NO   | MUL | NULL           |                | 
+-------------------+---------------------+------+-----+----------------+----------------+