Changeset 441


Ignore:
Timestamp:
May 26, 2008, 7:44:26 PM (14 years ago)
Author:
Dominic Hargreaves
Message:

Move to new schema version 10, including some missing indexes
and support for deletion flags and verified flags. Note that
the code using these columns has not yet been written (closes #25, #34).

Location:
wiki-toolkit/trunk
Files:
7 edited

Legend:

Unmodified
Added
Removed
  • wiki-toolkit/trunk/Changes

    r440 r441  
    110.76
    22        Really add missing prereq of DBI!
    3         Add testing of database schema upgrades
     3        Add testing of database schema upgrades (#32)
     4        Move to new schema version 10, including some missing indexes
     5          and support for deletion flags and verified flags. Note that
     6          the code using these columns has not yet been written (#25, #34).
    47
    580.75    11 May 2008
  • wiki-toolkit/trunk/lib/Wiki/Toolkit/Setup/Database.pm

    r440 r441  
    55use vars qw( $VERSION @SUPPORTED_SCHEMAS);
    66
    7 $VERSION = 0.08;
    8 @SUPPORTED_SCHEMAS = qw(8 9);
     7$VERSION = 0.09;
     8@SUPPORTED_SCHEMAS = qw(8 9 10);
    99
    1010=head1 NAME
     
    1515=cut
    1616
    17 # Fetch from the old style database, ready for an upgrade to db version 8
    1817sub fetch_upgrade_old_to_8 {
    19     # Compatible with old_to_9
    20     fetch_upgrade_old_to_9(@_);
    21 }
    22 
    23 # Fetch from the old style database, ready for an upgrade to db version 9
     18    # Compatible with old_to_10
     19    fetch_upgrade_old_to_10(@_);
     20}
     21
    2422sub fetch_upgrade_old_to_9 {
     23    # Compatible with old_to_10
     24    fetch_upgrade_old_to_10(@_);
     25}
     26
     27# Fetch from the old style database, ready for an upgrade to db version 10
     28sub fetch_upgrade_old_to_10 {
    2529    my $dbh = shift;
    2630    my %nodes;
     
    104108}
    105109
    106 # Fetch from schema version 8, and upgrade to version 9
    107110sub fetch_upgrade_8_to_9 {
     111    # Compatible with 8_to_10
     112    fetch_upgrade_8_to_10(@_);
     113}
     114
     115# Fetch from schema version 8, and upgrade to version 10
     116sub fetch_upgrade_8_to_10 {
    108117    my $dbh = shift;
    109118    my %nodes;
     
    139148        $content{'comment'} = $comment;
    140149        $content{'moderated'} = 1;
     150        $contents{$node_id."-".$version} = \%content;
     151    }
     152
     153    # Grab all the metadata
     154    $sth = $dbh->prepare("SELECT node_id,version,metadata_type,metadata_value FROM metadata");
     155    $sth->execute;
     156    my $i = 0;
     157    while( my($node_id,$version,$metadata_type,$metadata_value) = $sth->fetchrow_array) {
     158        my %metadata;
     159        $metadata{'node_id'} = $node_id;
     160        $metadata{'version'} = $version;
     161        $metadata{'metadata_type'} = $metadata_type;
     162        $metadata{'metadata_value'} = $metadata_value;
     163        $metadatas{$node_id."-".($i++)} = \%metadata;
     164    }
     165
     166    # Grab all the internal links
     167    $sth = $dbh->prepare("SELECT link_from,link_to FROM internal_links");
     168    $sth->execute;
     169    while( my($link_from,$link_to) = $sth->fetchrow_array) {
     170        my %il;
     171        $il{'link_from'} = $link_from;
     172        $il{'link_to'} = $link_to;
     173        push @internal_links, \%il;
     174    }
     175
     176    print "done\n";
     177
     178    # Return it all
     179    return (\%nodes,\%contents,\%metadatas,\@internal_links);
     180}
     181
     182# Fetch from schema version 9, and upgrade to version 10
     183sub fetch_upgrade_9_to_10 {
     184    my $dbh = shift;
     185    my %nodes;
     186    my %metadatas;
     187    my %contents;
     188    my @internal_links;
     189
     190    print "Grabbing and upgrading old data... ";
     191
     192    # Grab all the nodes
     193    my $sth = $dbh->prepare("SELECT id,name,version,text,modified,moderate FROM node");
     194    $sth->execute;
     195    while( my($id,$name,$version,$text,$modified,$moderate) = $sth->fetchrow_array) {
     196        my %node;
     197        $node{'name'} = $name;
     198        $node{'version'} = $version;
     199        $node{'text'} = $text;
     200        $node{'modified'} = $modified;
     201        $node{'id'} = $id;
     202        $node{'moderate'} = $moderate;
     203        $nodes{$name} = \%node;
     204    }
     205
     206    # Grab all the content
     207    $sth = $dbh->prepare("SELECT node_id,version,text,modified,comment,moderated FROM content");
     208    $sth->execute;
     209    while ( my($node_id,$version,$text,$modified,$comment,$moderated) = $sth->fetchrow_array) {
     210        my %content;
     211        $content{'node_id'} = $node_id;
     212        $content{'version'} = $version;
     213        $content{'text'} = $text;
     214        $content{'modified'} = $modified;
     215        $content{'comment'} = $comment;
     216        $content{'moderated'} = $moderated;
    141217        $contents{$node_id."-".$version} = \%content;
    142218    }
  • wiki-toolkit/trunk/lib/Wiki/Toolkit/Setup/MySQL.pm

    r440 r441  
    88
    99@ISA = qw( Wiki::Toolkit::Setup::Database );
    10 $VERSION = '0.09';
     10$VERSION = '0.10';
    1111
    1212use DBI;
     
    114114| ]
    115115    },
     116    10 => {
     117        schema_info => [ qq|
     118CREATE TABLE schema_info (
     119  version   int(10)      NOT NULL default 0
     120)
     121|, qq|
     122INSERT INTO schema_info VALUES (10)
     123| ],
     124
     125        node => [ qq|
     126CREATE TABLE node (
     127  id        integer      NOT NULL AUTO_INCREMENT,
     128  name      varchar(200) NOT NULL DEFAULT '',
     129  version   int(10)      NOT NULL default 0,
     130  text      mediumtext   NOT NULL default '',
     131  modified  datetime     default NULL,
     132  moderate  bool         NOT NULL default '0',
     133  deleted   bool         NOT NULL default '0',
     134  PRIMARY KEY (id)
     135)
     136|, qq|
     137CREATE UNIQUE INDEX node_name ON node (name)
     138|, qq|
     139CREATE INDEX node_deleted_index ON node (deleted)
     140| ],
     141
     142        content => [ qq|
     143CREATE TABLE content (
     144  node_id   integer      NOT NULL,
     145  version   int(10)      NOT NULL default 0,
     146  text      mediumtext   NOT NULL default '',
     147  modified  datetime     default NULL,
     148  comment   mediumtext   NOT NULL default '',
     149  moderated bool         NOT NULL default '1',
     150  deleted   bool         NOT NULL DEFAULT '0',
     151  verified  datetime     default NULL,
     152  PRIMARY KEY (node_id, version)
     153)
     154|, qq|
     155CREATE INDEX content_deleted_index ON content (deleted)
     156| ],
     157        internal_links => [ qq|
     158CREATE TABLE internal_links (
     159  link_from varchar(200) NOT NULL default '',
     160  link_to   varchar(200) NOT NULL default '',
     161  deleted   bool         NOT NULL default '0',
     162  PRIMARY KEY (link_from, link_to)
     163)
     164|, qq|
     165CREATE INDEX internal_links_deleted_index ON internal_links (deleted)
     166| ],
     167        metadata => [ qq|
     168CREATE TABLE metadata (
     169  node_id        integer      NOT NULL,
     170  version        int(10)      NOT NULL default 0,
     171  metadata_type  varchar(200) NOT NULL DEFAULT '',
     172  metadata_value mediumtext   NOT NULL DEFAULT '',
     173  deleted        bool         NOT NULL DEFAULT '0'
     174)
     175|, qq|
     176CREATE INDEX metadata_index ON metadata(node_id, version, metadata_type, metadata_value(10))
     177|, qq|
     178CREATE INDEX metadata_deleted_index ON metadata (deleted)
     179| ]
     180    },
    116181};
     182
     183my %fetch_upgrades = (
     184    old_to_8  => 1,
     185    old_to_9  => 1,
     186    old_to_10 => 1,
     187    '8_to_9'  => 1,
     188    '8_to_10' => 1,
     189);
     190
     191my %upgrades = (
     192'9_to_10' => [ qq|
     193CREATE UNIQUE INDEX node_name ON node (name)
     194|, qq|
     195ALTER TABLE node ADD COLUMN deleted boolean
     196|, qq|
     197UPDATE node SET deleted = '0'
     198|, qq|
     199ALTER TABLE node MODIFY COLUMN deleted bool NOT NULL DEFAULT '0'
     200|, qq|
     201CREATE INDEX node_deleted_index ON node (deleted)
     202|, qq|
     203ALTER TABLE content ADD COLUMN deleted boolean
     204|, qq|
     205UPDATE content SET deleted = '0'
     206|, qq|
     207ALTER TABLE content MODIFY COLUMN deleted bool NOT NULL DEFAULT '0'
     208|, qq|
     209CREATE INDEX content_deleted_index ON content (deleted)
     210|, qq|
     211ALTER TABLE internal_links ADD COLUMN deleted boolean
     212|, qq|
     213UPDATE internal_links SET deleted = '0'
     214|, qq|
     215ALTER TABLE internal_links MODIFY COLUMN deleted bool NOT NULL DEFAULT '0'
     216|, qq|
     217CREATE INDEX internal_links_deleted_index ON internal_links (deleted)
     218|, qq|
     219ALTER TABLE metadata ADD COLUMN deleted boolean
     220|, qq|
     221UPDATE metadata SET deleted = '0'
     222|, qq|
     223ALTER TABLE metadata MODIFY COLUMN deleted bool NOT NULL DEFAULT '0'
     224|, qq|
     225CREATE INDEX metadata_deleted_index ON metadata (deleted)
     226|, qq|
     227ALTER TABLE content ADD COLUMN verified datetime default NULL
     228|, qq|
     229UPDATE schema_info SET version = 10
     230|
     231],
     232);
    117233
    118234=head1 NAME
     
    181297    }
    182298    if($upgrade_schema) {
    183         # Grab current data
    184         print "Upgrading: $upgrade_schema\n";
    185         @cur_data = eval("&Wiki::Toolkit::Setup::Database::fetch_upgrade_".$upgrade_schema."(\$dbh)");
    186         if($@) { warn $@; }
    187 
    188         # Check to make sure we can create, index and drop tables
    189         # before doing any more
    190         my $perm_check = Wiki::Toolkit::Setup::Database::perm_check($dbh);
    191         if ($perm_check) {
    192             die "Unable to create/drop database tables as required by upgrade: $perm_check";
     299        if ($fetch_upgrades{$upgrade_schema}) {
     300            # Grab current data
     301            print "Upgrading: $upgrade_schema\n";
     302            @cur_data = eval("&Wiki::Toolkit::Setup::Database::fetch_upgrade_".$upgrade_schema."(\$dbh)");
     303            if($@) { warn $@; }
     304
     305            # Check to make sure we can create, index and drop tables
     306            # before doing any more
     307            my $perm_check = Wiki::Toolkit::Setup::Database::perm_check($dbh);
     308            if ($perm_check) {
     309                die "Unable to create/drop database tables as required by upgrade: $perm_check";
     310            }
     311       
     312            # Drop the current tables
     313            cleardb($dbh);
     314
     315            # Grab new list of tables
     316            %tables = fetch_tables_listing($dbh, $wanted_schema);
    193317        }
    194        
    195         # Drop the current tables
    196         cleardb($dbh);
    197 
    198         # Grab new list of tables
    199         %tables = fetch_tables_listing($dbh, $wanted_schema);
    200318    }
    201319
     
    214332    # If upgrading, load in the new data
    215333    if($upgrade_schema) {
    216         Wiki::Toolkit::Setup::Database::bulk_data_insert($dbh,@cur_data);
     334        if ($fetch_upgrades{$upgrade_schema}) {
     335            Wiki::Toolkit::Setup::Database::bulk_data_insert($dbh,@cur_data);
     336        } else {
     337            print "Upgrading schema: $upgrade_schema\n";
     338            my @updates = @{$upgrades{$upgrade_schema}};
     339            foreach my $update (@updates) {
     340                if(ref($update) eq "CODE") {
     341                    &$update($dbh);
     342                } elsif(ref($update) eq "ARRAY") {
     343                    foreach my $nupdate (@$update) {
     344                        $dbh->do($nupdate);
     345                    }
     346                } else {
     347                    $dbh->do($update);
     348                }
     349            }
     350        }
    217351    }
    218352
  • wiki-toolkit/trunk/lib/Wiki/Toolkit/Setup/Pg.pm

    r440 r441  
    88
    99@ISA = qw( Wiki::Toolkit::Setup::Database );
    10 $VERSION = '0.09';
     10$VERSION = '0.10';
    1111
    1212use DBI;
     
    133133| ]
    134134    },
     135    10 => {
     136        schema_info => [ qq|
     137CREATE TABLE schema_info (
     138  version   integer      NOT NULL default 0
     139)
     140|, qq|
     141INSERT INTO schema_info VALUES (10)
     142| ],
     143
     144        node => [ qq|
     145CREATE SEQUENCE node_seq
     146|, qq|
     147CREATE TABLE node (
     148  id        integer      NOT NULL DEFAULT NEXTVAL('node_seq'),
     149  name      varchar(200) NOT NULL DEFAULT '',
     150  version   integer      NOT NULL default 0,
     151  text      text         NOT NULL default '',
     152  modified  timestamp without time zone    default NULL,
     153  moderate  boolean      NOT NULL default '0',
     154  deleted   boolean      NOT NULL default '0',
     155  CONSTRAINT pk_id PRIMARY KEY (id)
     156)
     157|, qq|
     158CREATE UNIQUE INDEX node_name ON node (name)
     159|, qq|
     160CREATE INDEX node_deleted_index ON node (deleted)
     161| ],
     162
     163        content => [ qq|
     164CREATE TABLE content (
     165  node_id   integer      NOT NULL,
     166  version   integer      NOT NULL default 0,
     167  text      text         NOT NULL default '',
     168  modified  timestamp without time zone    default NULL,
     169  comment   text         NOT NULL default '',
     170  moderated boolean      NOT NULL default '1',
     171  deleted   boolean      NOT NULL default '0',
     172  verified  timestamp without time zone    default NULL,
     173  CONSTRAINT pk_node_id PRIMARY KEY (node_id,version),
     174  CONSTRAINT fk_node_id FOREIGN KEY (node_id) REFERENCES node (id)
     175)
     176|, qq|
     177CREATE INDEX content_deleted_index ON content (deleted)
     178| ],
     179
     180        internal_links => [ qq|
     181CREATE TABLE internal_links (
     182  link_from varchar(200) NOT NULL default '',
     183  link_to   varchar(200) NOT NULL default ''
     184)
     185|, qq|
     186CREATE UNIQUE INDEX internal_links_pkey ON internal_links (link_from, link_to)
     187| ],
     188
     189        metadata => [ qq|
     190CREATE TABLE metadata (
     191  node_id        integer      NOT NULL,
     192  version        integer      NOT NULL default 0,
     193  metadata_type  varchar(200) NOT NULL DEFAULT '',
     194  metadata_value text         NOT NULL DEFAULT '',
     195  CONSTRAINT fk_node_id FOREIGN KEY (node_id) REFERENCES node (id)
     196)
     197|, qq|
     198CREATE INDEX metadata_index ON metadata (node_id, version, metadata_type, metadata_value)
     199| ]
     200    },
    135201};
    136202
     
    193259],
    194260
     261'9_to_10' => [ qq|
     262ALTER TABLE node ADD COLUMN deleted boolean;
     263UPDATE node SET deleted = '0';
     264ALTER TABLE node ALTER COLUMN deleted SET DEFAULT '0';
     265ALTER TABLE node ALTER COLUMN deleted SET NOT NULL;
     266CREATE INDEX node_deleted_index ON node (deleted);
     267|, qq|
     268ALTER TABLE content ADD COLUMN deleted boolean;
     269UPDATE content SET deleted = '0';
     270ALTER TABLE content ALTER COLUMN deleted SET DEFAULT '0';
     271ALTER TABLE content ALTER COLUMN deleted SET NOT NULL;
     272CREATE INDEX content_deleted_index ON content (deleted);
     273|, qq|
     274ALTER TABLE internal_links ADD COLUMN deleted boolean;
     275UPDATE internal_links SET deleted = '0';
     276ALTER TABLE internal_links ALTER COLUMN deleted SET DEFAULT '0';
     277ALTER TABLE internal_links ALTER COLUMN deleted SET NOT NULL;
     278CREATE INDEX internal_links_deleted_index ON internal_links (deleted);
     279|, qq|
     280ALTER TABLE metadata ADD COLUMN deleted boolean;
     281UPDATE metadata SET deleted = '0';
     282ALTER TABLE metadata ALTER COLUMN deleted SET DEFAULT '0';
     283ALTER TABLE metadata ALTER COLUMN deleted SET NOT NULL;
     284CREATE INDEX metadata_deleted_index ON metadata (deleted);
     285|, qq|
     286ALTER TABLE content ADD COLUMN verified timestamp without time zone default NULL;
     287|, qq|
     288UPDATE schema_info SET version = 10;
     289|
     290],
     291
    195292);
    196293
    197 my @old_to_9 = ($upgrades{'old_to_8'},$upgrades{'8_to_9'});
    198 $upgrades{'old_to_9'} = \@old_to_9;
     294my @old_to_10 = ($upgrades{'old_to_8'},$upgrades{'8_to_9'},$upgrades{'9_to_10'});
     295my @eight_to_10 = ($upgrades{'8_to_9'},$upgrades{'9_to_10'});
     296$upgrades{'old_to_10'} = \@old_to_10;
     297$upgrades{'8_to_10'} = \@eight_to_10;
    199298
    200299=head1 NAME
  • wiki-toolkit/trunk/lib/Wiki/Toolkit/Setup/SQLite.pm

    r440 r441  
    88
    99@ISA = qw( Wiki::Toolkit::Setup::Database );
    10 $VERSION = '0.09';
     10$VERSION = '0.10';
    1111
    1212use DBI;
     
    105105| ]
    106106    },
     107    10 => {
     108        schema_info => [ qq|
     109CREATE TABLE schema_info (
     110  version   integer      NOT NULL default 0
     111);
     112|, qq|
     113INSERT INTO schema_info VALUES (10)
     114| ],
     115
     116        node => [ qq|
     117CREATE TABLE node (
     118  id        integer      NOT NULL PRIMARY KEY AUTOINCREMENT,
     119  name      varchar(200) NOT NULL DEFAULT '',
     120  version   integer      NOT NULL default 0,
     121  text      mediumtext   NOT NULL default '',
     122  modified  datetime     default NULL,
     123  moderate  boolean      NOT NULL default '0',
     124  deleted   boolean      NOT NULL default '0'
     125)
     126|, qq|
     127CREATE UNIQUE INDEX node_name ON node (name)
     128|, qq|
     129CREATE INDEX node_deleted_index ON node (deleted)
     130| ],
     131        content => [ qq|
     132CREATE TABLE content (
     133  node_id   integer      NOT NULL,
     134  version   integer      NOT NULL default 0,
     135  text      mediumtext   NOT NULL default '',
     136  modified  datetime     default NULL,
     137  comment   mediumtext   NOT NULL default '',
     138  moderated boolean      NOT NULL default '1',
     139  deleted   boolean      NOT NULL default '0',
     140  verified  datetime     default NULL,
     141  PRIMARY KEY (node_id, version)
     142)
     143|, qq|
     144CREATE INDEX content_deleted_index ON content (deleted)
     145| ],
     146        internal_links => [ qq|
     147CREATE TABLE internal_links (
     148  link_from varchar(200) NOT NULL default '',
     149  link_to   varchar(200) NOT NULL default '',
     150  deleted   boolean      NOT NULL default '0',
     151  PRIMARY KEY (link_from, link_to)
     152)
     153|, qq|
     154CREATE INDEX internal_links_deleted_index ON internal_links (deleted)
     155| ],
     156        metadata => [ qq|
     157CREATE TABLE metadata (
     158  node_id        integer      NOT NULL,
     159  version        integer      NOT NULL default 0,
     160  metadata_type  varchar(200) NOT NULL DEFAULT '',
     161  metadata_value mediumtext   NOT NULL DEFAULT '',
     162  deleted        boolean      NOT NULL DEFAULT '0'
     163)
     164|, qq|
     165CREATE INDEX metadata_deleted_index ON metadata (deleted)
     166| ]
     167    },
    107168};
     169
     170my %fetch_upgrades = (
     171    old_to_8  => 1,
     172    old_to_9  => 1,
     173    old_to_10 => 1,
     174    '8_to_9'  => 1,
     175    '8_to_10' => 1,
     176    '9_to_10' => 1,
     177);
     178
     179my %upgrades = ();
    108180
    109181=head1 NAME
     
    169241    }
    170242    if($upgrade_schema) {
    171         # Grab current data
    172         print "Upgrading: $upgrade_schema\n";
    173         @cur_data = eval("&Wiki::Toolkit::Setup::Database::fetch_upgrade_".$upgrade_schema."(\$dbh)");
    174 
    175         # Drop the current tables
    176         cleardb($dbh);
    177 
    178         # Grab new list of tables
    179         %tables = fetch_tables_listing($dbh, $wanted_schema);
     243        if ($fetch_upgrades{$upgrade_schema}) {
     244            # Grab current data
     245            print "Upgrading: $upgrade_schema\n";
     246            @cur_data = eval("&Wiki::Toolkit::Setup::Database::fetch_upgrade_".$upgrade_schema."(\$dbh)");
     247
     248            # Drop the current tables
     249            cleardb($dbh);
     250
     251            # Grab new list of tables
     252            %tables = fetch_tables_listing($dbh, $wanted_schema);
     253        }
    180254    }
    181255
     
    194268    # If upgrading, load in the new data
    195269    if($upgrade_schema) {
    196         Wiki::Toolkit::Setup::Database::bulk_data_insert($dbh,@cur_data);
     270        if ($fetch_upgrades{$upgrade_schema}) {
     271            Wiki::Toolkit::Setup::Database::bulk_data_insert($dbh,@cur_data);
     272        } else {
     273            print "Upgrading schema: $upgrade_schema\n";
     274            my @updates = @{$upgrades{$upgrade_schema}};
     275            foreach my $update (@updates) {
     276                if(ref($update) eq "CODE") {
     277                    &$update($dbh);
     278                } elsif(ref($update) eq "ARRAY") {
     279                    foreach my $nupdate (@$update) {
     280                        $dbh->do($nupdate);
     281                    }
     282                } else {
     283                    $dbh->do($update);
     284                }
     285            }
     286        }
    197287    }
    198288
  • wiki-toolkit/trunk/lib/Wiki/Toolkit/Store/Database.pm

    r431 r441  
    1313
    1414$VERSION = '0.29';
    15 my $SCHEMA_VER = 9;
     15my $SCHEMA_VER = 10;
    1616
    1717# first, detect if Encode is available - it's not under 5.6. If we _are_
  • wiki-toolkit/trunk/t/400_upgrade.t

    r440 r441  
    1515INSERT INTO content VALUES (2, 1, 'More content', 'now', 'no comment')|, qq|
    1616INSERT INTO metadata VALUES (1, 1, 'foo', 'bar')|, qq|
    17 INSERT INTO metadata VALUES (2, 1, 'baz', 'quux')| ]
     17INSERT INTO metadata VALUES (2, 1, 'baz', 'quux')| ],
     18    9 => [ qq|
     19INSERT INTO node (id, name, version, text, modified) VALUES (1, 'Test node 1', 1, 'Some content', 'now')|, qq|
     20INSERT INTO node (id, name, version, text, modified) VALUES (2, 'Test node 2', 1, 'More content', 'now')|, qq|
     21INSERT INTO content (node_id, version, text, modified, comment) VALUES (1, 1, 'Some content', 'now', 'no comment')|, qq|
     22INSERT INTO content (node_id, version, text, modified, comment) VALUES (2, 1, 'More content', 'now', 'no comment')|, qq|
     23INSERT INTO metadata VALUES (1, 1, 'foo', 'bar')|, qq|
     24INSERT INTO metadata VALUES (2, 1, 'baz', 'quux')| ],
    1825};
    1926
Note: See TracChangeset for help on using the changeset viewer.