Difference between revisions of "WikiDB/Tables"

Revision as of 00:42, 9 July 2006

There are three new tables in the database that store the WikiDB data. If you have any suggestions about how these could be further optimised, please discuss them on the talk page.

(Note: The table definitions on this page are live. Changes to the DB will automatically be reflected here. This is achieved using my Debugging tools extension.)

wikidb_tables

CREATE TABLE IF NOT EXISTS `wikidb_tables` (
  `table_namespace` int(11) NOT NULL,
  `table_title` varchar(255) NOT NULL,
  `table_def` mediumblob NOT NULL,
  `redirect_namespace` int(11) DEFAULT NULL,
  `redirect_title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`table_namespace`,`table_title`),
  KEY `wikidb_tables_redirect_ns_title` (`redirect_namespace`,`redirect_title`)
) ENGINE=InnoDB;

Contains information about all tables defined in the wiki.

Purpose:

• To stop us having to parse the article text whenever data is requested.

Needs to be updated:

• Whenever a page in a DB namespace is created/saved.
• Whenever a page in a DB namespace is moved.
• Whenever a page in a DB namespace is deleted.

Considerations:

• What happens if a table namespace is deleted? Hopefully it should all continue working as normally (with some redundant records in the DB), but this needs checking.

wikidb_rowdata

CREATE TABLE IF NOT EXISTS `wikidb_rowdata` (
  `row_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `page_namespace` int(11) NOT NULL,
  `page_title` varchar(255) NOT NULL,
  `table_namespace` int(11) NOT NULL,
  `table_title` varchar(255) NOT NULL,
  `parsed_data` mediumblob NOT NULL,
  `is_stale` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`row_id`),
  KEY `wikidb_rowdata_table_ns_title` (`table_namespace`,`table_title`),
  KEY `wikidb_rowdata_ns_title` (`page_namespace`,`page_title`),
  KEY `wikidb_rowdata_is_stale` (`is_stale`)
) ENGINE=InnoDB;

Contains a record for each row in each database. These may defined anywhere in the wiki.

Purpose:

• To avoid having to parse all articles whenever data is required.

Needs to be updated:

• Whenever any page is created/saved.
• Whenever any page is moved.
• Whenever any page is deleted.
• If we decide to cache formatted versions of the fields, whenever wikidb_tables is updated.

wikidb_fielddata

CREATE TABLE IF NOT EXISTS `wikidb_fielddata` (
  `row_id` int(8) unsigned NOT NULL,
  `field_name` varchar(255) NOT NULL DEFAULT '',
  `field_value` varchar(255) NOT NULL DEFAULT '',
  KEY `wikidb_fielddata_row_id` (`row_id`),
  KEY `wikidb_fielddata_field_name` (`field_name`(75),`field_value`(75))
) ENGINE=InnoDB;

Contains a record for each field in each row in each database.

Purpose:

• To be searchable and sortable.
• Actual data for display is pulled from the row data.

Needs to be updated:

• Whenever wikidb_rowdata is updated.
• If we need to cache formatted versions of the fields (which we probably do), whenever wikidb_tables is updated.

Considerations:

• All values are in the same text field.
• Sorting via this field should always give the correct order (whether numeric or alphabetic).
  • One solution is for numeric values to be stored zero-padded. E.g. 9.5 becomes 00000009.50000000
  • Another solution is to cast the data in the query, based on the type of the field. However this may not be possible for various reasons.