In case someone like me should have played so much with the alpha 1.5
version,
here is a quick and dirty script to remove all old revisions.
!!! This is a developer tool, don't use it on your precious parts, and make
a backup before you use it !!!
It's probably rather safe on the first time you run it, because, backups are
made, but on the second run, if something goes wrong, data loss might occur.
Dieter Menne
dieter.menne(a)menne-biomed.de
-- Script to cleanup all revision in MediaWiki 1.5 alpha (not for earlier
version)
-- This is quick and dirty, but all original table are preserver with _old
-- If everything works as request, you can delete the xx_old tables created
-- by this script.
-- Only tested once, don't try it with your database without making a
-- backup first
-- dieter.menne(a)menne-biomed.de
-- This script does not use nested queries, it should work with MySql 4.0
CREATE TABLE `mw_text1` ( `old_id` int( 8 ) unsigned NOT NULL
AUTO_INCREMENT ,
`old_namespace` tinyint( 2 ) unsigned NOT NULL default '0',
`old_title` varchar( 255 ) binary NOT NULL default '',
`old_text` mediumtext NOT NULL ,
`old_comment` tinyblob NOT NULL ,
`old_user` int( 5 ) unsigned NOT NULL default '0',
`old_user_text` varchar( 255 ) binary NOT NULL default '',
`old_timestamp` varchar( 14 ) binary NOT NULL default '',
`old_minor_edit` tinyint( 1 ) NOT NULL default '0',
`old_flags` tinyblob NOT NULL ,
`inverse_timestamp` varchar( 14 ) binary NOT NULL default '',
PRIMARY KEY ( `old_id` ) ,
KEY `old_timestamp` ( `old_timestamp` ) ,
KEY `name_title_timestamp` ( `old_namespace` , `old_title` ,
`inverse_timestamp` ) ,
KEY `user_timestamp` ( `old_user` , `inverse_timestamp` ) ,
KEY `usertext_timestamp` ( `old_user_text` , `inverse_timestamp` ) )
TYPE = MYISAM ;
-- Table with latest changes
CREATE TABLE `latest` (
`latest_id` INT NOT NULL
);
-- get unique
insert into latest
select old_id from mw_text
left outer join mw_page
on old_id=page_latest
where page_latest is not NULL;
-- Copy unique into text1
insert into mw_text1 (
select mw_text1.* from mw_text
join latest
on latest_id=old_id);
CREATE TABLE `mw_revision1` (
`rev_id` int( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
`rev_page` int( 8 ) unsigned NOT NULL default '0',
`rev_comment` tinyblob NOT NULL ,
`rev_user` int( 5 ) unsigned NOT NULL default '0',
`rev_user_text` varchar( 255 ) binary NOT NULL default '',
`rev_timestamp` varchar( 14 ) binary NOT NULL default '',
`rev_minor_edit` tinyint( 1 ) unsigned NOT NULL default '0',
`inverse_timestamp` varchar( 14 ) binary NOT NULL default '',
PRIMARY KEY ( `rev_page` , `rev_id` ) ,
UNIQUE KEY `rev_id` ( `rev_id` ) ,
KEY `rev_timestamp` ( `rev_timestamp` ) ,
KEY `page_timestamp` ( `rev_page` , `inverse_timestamp` ) ,
KEY `user_timestamp` ( `rev_user` , `inverse_timestamp` ) ,
KEY `usertext_timestamp` ( `rev_user_text` , `inverse_timestamp` )
) TYPE = MYISAM ;
-- Copy most recent revisions
insert into mw_revision1 (
select mw_revision.* from mw_revision
join latest
on latest_id=rev_id);
--- Remove list of recent changes, we first make a copy and the
-- truncate the original. This could have been made easier without the
-- copy, but I wanted to play safe.
CREATE TABLE `mw_recentchanges_old` (
`rc_id` int( 8 ) NOT NULL AUTO_INCREMENT ,
`rc_timestamp` varchar( 14 ) binary NOT NULL default '',
`rc_cur_time` varchar( 14 ) binary NOT NULL default '',
`rc_user` int( 10 ) unsigned NOT NULL default '0',
`rc_user_text` varchar( 255 ) binary NOT NULL default '',
`rc_namespace` tinyint( 3 ) NOT NULL default '0',
`rc_title` varchar( 255 ) binary NOT NULL default '',
`rc_comment` varchar( 255 ) binary NOT NULL default '',
`rc_minor` tinyint( 3 ) unsigned NOT NULL default '0',
`rc_bot` tinyint( 3 ) unsigned NOT NULL default '0',
`rc_new` tinyint( 3 ) unsigned NOT NULL default '0',
`rc_cur_id` int( 10 ) unsigned NOT NULL default '0',
`rc_this_oldid` int( 10 ) unsigned NOT NULL default '0',
`rc_last_oldid` int( 10 ) unsigned NOT NULL default '0',
`rc_type` tinyint( 3 ) unsigned NOT NULL default '0',
`rc_moved_to_ns` tinyint( 3 ) unsigned NOT NULL default '0',
`rc_moved_to_title` varchar( 255 ) binary NOT NULL default '',
`rc_patrolled` tinyint( 3 ) unsigned NOT NULL default '0',
`rc_ip` varchar( 15 ) NOT NULL default '',
PRIMARY KEY ( `rc_id` ) ,
KEY `rc_timestamp` ( `rc_timestamp` ) ,
KEY `rc_namespace_title` ( `rc_namespace` , `rc_title` ) ,
KEY `rc_cur_id` ( `rc_cur_id` ) ,
KEY `new_name_timestamp` ( `rc_new` , `rc_namespace` , `rc_timestamp` ) ,
KEY `rc_ip` ( `rc_ip` )
) TYPE = MYISAM ;
-- Copy into database
INSERT INTO `mw_recentchanges_old`
SELECT *
FROM `mw_recentchanges` ;
-- Remove date from original
TRUNCATE TABLE mw_recentchanges;
--- Swap table names
ALTER TABLE `mw_revision` RENAME `mw_revision_old` ;
ALTER TABLE `mw_text` RENAME `mw_text_old` ;
ALTER TABLE `mw_text1` RENAME `mw_text` ;
ALTER TABLE `mw_revision1` RENAME `mw_revision` ;
-- We don't need the latest any more
DROP Table latest;