Brion Vibber wrote:
Comments? Ideas? Complaints?
-- brion vibber (brion @ pobox.com) <patch-revisions.sql>
-- Change cur/old into page/revisions
-- page_sortkey will contain a munged version of the title for --- language-specific sorting -- page_current is a foreign key on revisions.rev_id DROP TABLE IF EXISTS page; CREATE TABLE page ( page_id int(8) unsigned NOT NULL auto_increment, page_namespace tinyint(2) unsigned NOT NULL default '0', page_title varchar(255) binary NOT NULL default '', page_sortkey varchar(255) binary NOT NULL default '', page_restrictions tinyblob NOT NULL default '', page_counter bigint(20) unsigned NOT NULL default '0', page_is_redirect tinyint(1) unsigned NOT NULL default '0', page_random real unsigned NOT NULL, page_touched char(14) binary NOT NULL default '', page_current int(8) unsigned NOT NULL default 0, UNIQUE KEY page_id (page_id), UNIQUE KEY namespace_title (page_namespace,page_title), KEY (page_random), );
-- rev_page is a foreign key to page.page_id -- rev_prior will key to another rev_id for the previous revision, to simplify -- making diff links etc DROP TABLE IF EXISTS revisions; CREATE TABLE revisions ( rev_id int(8) unsigned NOT NULL auto_increment, rev_page int(8) unsigned NOT NULL default 0, rev_prior int(8) unsigned NOT NULL default 0, rev_text mediumtext NOT NULL default '', rev_comment tinyblob NOT NULL default '', rev_user int(5) unsigned NOT NULL default '0', rev_user_text varchar(255) binary NOT NULL, rev_timestamp char(14) binary NOT NULL default '', rev_minor_edit tinyint(1) NOT NULL default '0', rev_current tinyint(1) NOT NULL default 0, UNIQUE KEY rev_id (rev_id), ADD INDEX (rev_timestamp), ADD INDEX page_timestamp (rev_page,rev_timestamp), ADD INDEX user_timestamp (rev_user,rev_timestamp), ADD INDEX usertext_timestamp (rev_user_text,rev_timestamp); );
-- Import old revisisons from old to revisions INSERT INTO revisions (rev_id,rev_page,rev_text,rev_comment,rev_user,rev_user_text, rev_timestamp,rev_minor_edit,rev_flags) SELECT old_id,cur_id,old_text,old_comment,old_user,old_user_text, old_timestamp,old_minor_edit,old_flags) FROM old,cur WHERE old_namespace=cur_namespace and old_title=cur_title;
-- Import cur revisions from cur to revisions -- This will create new rev_id revisions keys. INSERT INTO revisions (rev_page,rev_text,rev_comment,rev_user,rev_user_text, rev_timestamp,rev_minor_edit,rev_cur_edit) SELECT cur_id,cur_text,cur_comment,cur_user,cur_user_text, cur_timestamp,cur_minor_edit FROM cur;
-- Import page definitions from cur to page INSERT INTO page (page_id,page_namespace,page_title,page_restrictions, page_counter,page_is_redirect,page_random,page_touched, page_current) SELECT cur_id,cur_namespace,cur_title,cur_restrictions, cur_counter,cur_is_redirect,cur_random,cur_touched, rev_id FROM cur,revisions WHERE cur_id=rev_page and rev_is_current;
-- rev_prior and page_sortkey need to be filled out by a php script?
I like it, I just have one question, and pardon my ignorance, but why the use of binary fields? are they faster or smaller or less easly corrupted?
In any case, good work Brion, this schema will simplify things and make the overall code a lot cleaner.
Lightning