Semantic MediaWiki database tables

From semantic-mediawiki.org
Jump to: navigation, search

THIS PAGE IS DEPRECATED AND REFERS TO AN OLD VERSION OF THE DATABASE.

In general, do not work on the DB directly, but always use the Store API instead. An SMW instance does not require to have a SMW SQL Store! It may use different stores instead. The Store API abstracts from that. Therefore this page is not only deprecated, but also not that much needed anymore, if you do not want to work on the SMW SQL Store.

Looking at SMW SQL statements

To see the SQL statements made during a page request, add the following variables to LocalSettings.php:

$wgDebugLogFile = "/path/to/log/file/debug.log";
$wgDebugDumpSql = true;

And you will be able to see them in the log file you specified in $wgDebugLogFile. You shouldn't have the debug file in a location accessible from the web.

Some SQL statements SMW makes get truncated in the log file. To fix this, change the number 500 to something larger, like 5000, in the file includes/Database.php. It's also convenient to add a ";" to the end of the wfDegug string before the "\n", so that SQL statements can be directly copied from the logs and tested out in MySQL.

Before (line #s from MediaWiki 1.11):

   751                 if ( $this->debug() ) {
   752                         $sqlx = substr( $commentedSql, 0, 500 );
   753                         $sqlx = strtr( $sqlx, "\t\n", '  ' );
   754                         wfDebug( "SQL: $sqlx\n" );
   755                 }

After:

   751                 if ( $this->debug() ) {
   752                         $sqlx = substr( $commentedSql, 0, 5000 );
   753                         $sqlx = strtr( $sqlx, "\t\n", '  ' );
   754                         wfDebug( "SQL: $sqlx;\n" );
   755                 }

Looking at SMW database code

The SQL code is nearly all restricted to the file includes/storage/SMW_SQLStore.php. All or nearly all of the update and querying is through abstract functions defined by class SMWStore, such as SMWStore->getPropertySubjects() and getQueryResults(), which class SMWSQLStore implements.

Tables added by Semantic MediaWiki

smw_attributes

This table was present in SMW 0.7.

The subject page is marked as having an attribute with a given value.

Field Type Null Key Default Is indexed Description
subject_id int(8) unsigned NO MUL yes Matches the page_id in the page table for the subject page.
subject_namespace int(11) NO Matches the page_namespace in the page table for the subject page.
subject_title varchar(255) NO Matches the page_title in the page table for the subject page.
attribute_title varchar(255) NO MUL yes The name of the attribute.
value_unit varchar(63) YES NULL
value_datatype varchar(31) NO Removed in SMW version 1.1
value_xsd varchar(255) NO MUL yes
value_num double YES MUL NULL yes

smw_longstrings

This table was added in SMW 1.0.

Field Type Null Key Default Is indexed Description
subject_id int(8) unsigned NO MUL yes Matches the page_id in the page table for the subject page.
subject_namespace int(11) NO Matches the page_namespace in the page table for the subject page.
subject_title varchar(255) NO Matches the page_title in the page table for the subject page.
attribute_title varchar(255) NO MUL yes The name of the attribute.
value_blob mediumblob YES NULL

smw_nary

This table was added in SMW 1.0.

Field Type Null Key Default Is indexed Description
subject_id int(8) unsigned NO MUL yes, also in subject_id,nary_key Matches the page_id in the page table for the subject page.
subject_namespace int(11) NO Matches the page_namespace in the page table for the subject page.
subject_title varchar(255) NO Matches the page_title in the page table for the subject page.
attribute_title varchar(255) NO MUL yes The name of the attribute.
nary_key int(8) unsigned NO subject_id,nary_key

smw_nary_attributes

This table was added in SMW 1.0.

Field Type Null Key Default Is indexed Descriptions
subject_id int(8) unsigned NO MUL subject_id,nary_key Matches the page_id in the page table for the subject page.
nary_key int(8) unsigned NO subject_id,nary_key
nary_pos int(8) unsigned NO
value_unit varchar(63) YES NULL
value_xsd varchar(255) NO MUL yes
value_num double YES MUL NULL yes

smw_nary_longstrings

This table was added in SMW 1.0.

Field Type Null Key Default Is indexed Description
subject_id int(8) unsigned NO MUL subject_id,nary_key Matches the page_id in the page table for the subject page.
nary_key int(8) unsigned NO subject_id,nary_key
nary_pos int(8) unsigned NO
value_blob mediumblob YES NULL

smw_nary_relations

This table was added in SMW 1.0.

Field Type Null Key Default Is indexed Description
subject_id int(8) unsigned NO MUL subject_id,nary_key Matches the page_id in the page table for the subject page.
nary_key int(8) unsigned NO subject_id,nary_key
nary_pos int(8) unsigned NO
object_namespace int(11) NO object_title,object_namespace
object_title varchar(255) NO MUL object_title,object_namespace
object_id int(8) unsigned YES MUL NULL yes

smw_relations

This table was present in SMW 0.7.

The subject is the page the annotation takes place on, and the object is annotated as having a given relation to it.

Field Type Null Key Default Is indexed Description
subject_id int(8) unsigned NO MUL yes Matches the page_id in the page table for the subject page.
subject_namespace int(11) NO Matches the page_namespace in the page table for the subject page.
subject_title varchar(255) NO Matches the page_title in the page table for the subject page.
relation_title varchar(255) NO MUL yes The name of the relation.
object_namespace int(11) NO object_title,object_namespace Matches the page_namespace in the page table for the page associated with the object.
object_title varchar(255) NO MUL object_title,object_namespace Matches the page_title in the page table for the page associated with the object.
object_id int(8) unsigned YES MUL NULL yes Matches the page_id in the page table for the page associated object. (This column was added in SMW 1.0.)

smw_specialprops

This table was present in SMW 0.7.

Field Type Null Key Default Is indexed Description
subject_id int(8) unsigned NO MUL yes Matches the page_id in the page table for a property page.
subject_namespace int(11) NO Matches the page_namespace in the page table for a property page.
subject_title varchar(255) NO Matches the page_title in the page table for a property page.
property_id smallint(6) NO MUL yes If the value is 1, value_string holds a datatype string (_wpg, _dat, _str). If the value is 14, value_string seems to hold a valid enumeration value such as defined by [[has type::x]].
value_string varchar(255) NO Depends on the contents of property_id

smw_subprops

This table was present in SMW 0.7.

Field Type Null Key Default Is indexed Description
subject_title varchar(255) NO MUL yes Matches the page_title in the page table for the subject property.
object_title varchar(255) NO MUL yes The property the subject property is a subclass of.

SMW Temporary Tables

These describe temporary tables made during searches. They are all made in memory.

For categories and subcategories

cat

Contains the lower closure of the given category with respect to the category table. Made by getCategoryTable() in the class SMWSQLStore.

smw_newcats

smw_rescats

For properties and subproperties

prop

Contains the lower closure of the given property with respect to the subproperty relation. Made by getPropertyTable() in the class SMWSQLStore.

smw_new

smw_res

Tables SMW uses from the main MediaWiki database

These tables weren't made by Semantic MediaWiki, but Semantic MediaWiki refers to them and uses them in SQL statements.

page

Field Type Null Key Default Description
page_id int(10) unsigned NO PRI NULL auto_increment
page_namespace int(11) NO MUL
page_title varchar(255) NO
page_restrictions tinyblob NO
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
page_touched binary(14) NO
page_latest int(10) unsigned NO
page_len int(10) unsigned NO MUL

categorylinks

Field Type Null Key Default Description
cl_from int(10) unsigned NO PRI 0
cl_to varchar(255) NO PRI
cl_sortkey varchar(70) NO
cl_timestamp timestamp NO CURRENT_TIMESTAMP

redirect

Field Type Null Key Default Description
rd_from int(10) unsigned NO PRI 0 The page_id of the redirect's entry in the page table.
rd_namespace int(11) NO MUL 0 The page_namespace of what page the redirect is to in the page table.
rd_title varchar(255) NO The page_title of what the page the redirect is to in the page table.