Archive:Semantic MediaWiki database tables

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.

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.

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.