source: wiki-toolkit/trunk/lib/Wiki/Toolkit/Setup/MySQL.pm

Last change on this file was 450, checked in by Dominic Hargreaves, 13 years ago

Cater for the case where the index being added in MySQL schema 10 was manually created in the database beforehand

  • Property svn:eol-style set to native
  • Property svn:keywords set to Author Date Id Revision
File size: 13.3 KB
Line 
1package Wiki::Toolkit::Setup::MySQL;
2
3use strict;
4
5use vars qw( @ISA $VERSION $SCHEMA_VERSION );
6
7use Wiki::Toolkit::Setup::Database;
8
9@ISA = qw( Wiki::Toolkit::Setup::Database );
10$VERSION = '0.10';
11
12use DBI;
13use Carp;
14
15$SCHEMA_VERSION = $VERSION*100;
16
17my $create_sql = {
18    8 => {
19        schema_info => [ qq|
20CREATE TABLE schema_info (
21  version   int(10)      NOT NULL default 0
22)
23|, qq|
24INSERT INTO schema_info VALUES (8)
25| ],
26
27        node => [ qq|
28CREATE TABLE node (
29  id        integer      NOT NULL AUTO_INCREMENT,
30  name      varchar(200) NOT NULL DEFAULT '',
31  version   int(10)      NOT NULL default 0,
32  text      mediumtext   NOT NULL default '',
33  modified  datetime     default NULL,
34  PRIMARY KEY (id)
35)
36| ],
37
38        content => [ qq|
39CREATE TABLE content (
40  node_id   integer      NOT NULL,
41  version   int(10)      NOT NULL default 0,
42  text      mediumtext   NOT NULL default '',
43  modified  datetime     default NULL,
44  comment   mediumtext   NOT NULL default '',
45  PRIMARY KEY (node_id, version)
46)
47| ],
48        internal_links => [ qq|
49CREATE TABLE internal_links (
50  link_from varchar(200) NOT NULL default '',
51  link_to   varchar(200) NOT NULL default '',
52  PRIMARY KEY (link_from, link_to)
53)
54| ],
55        metadata => [ qq|
56CREATE TABLE metadata (
57  node_id        integer      NOT NULL,
58  version        int(10)      NOT NULL default 0,
59  metadata_type  varchar(200) NOT NULL DEFAULT '',
60  metadata_value mediumtext   NOT NULL DEFAULT ''
61)
62|, qq|
63CREATE INDEX metadata_index ON metadata(node_id, version, metadata_type, metadata_value(10))
64| ]
65    },
66    9 => {
67        schema_info => [ qq|
68CREATE TABLE schema_info (
69  version   int(10)      NOT NULL default 0
70)
71|, qq|
72INSERT INTO schema_info VALUES (9)
73| ],
74
75        node => [ qq|
76CREATE TABLE node (
77  id        integer      NOT NULL AUTO_INCREMENT,
78  name      varchar(200) NOT NULL DEFAULT '',
79  version   int(10)      NOT NULL default 0,
80  text      mediumtext   NOT NULL default '',
81  modified  datetime     default NULL,
82  moderate  bool         NOT NULL default '0',
83  PRIMARY KEY (id)
84)
85| ],
86
87        content => [ qq|
88CREATE TABLE content (
89  node_id   integer      NOT NULL,
90  version   int(10)      NOT NULL default 0,
91  text      mediumtext   NOT NULL default '',
92  modified  datetime     default NULL,
93  comment   mediumtext   NOT NULL default '',
94  moderated bool         NOT NULL default '1',
95  PRIMARY KEY (node_id, version)
96)
97| ],
98        internal_links => [ qq|
99CREATE TABLE internal_links (
100  link_from varchar(200) NOT NULL default '',
101  link_to   varchar(200) NOT NULL default '',
102  PRIMARY KEY (link_from, link_to)
103)
104| ],
105        metadata => [ qq|
106CREATE TABLE metadata (
107  node_id        integer      NOT NULL,
108  version        int(10)      NOT NULL default 0,
109  metadata_type  varchar(200) NOT NULL DEFAULT '',
110  metadata_value mediumtext   NOT NULL DEFAULT ''
111)
112|, qq|
113CREATE INDEX metadata_index ON metadata(node_id, version, metadata_type, metadata_value(10))
114| ]
115    },
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  PRIMARY KEY (id)
134)
135|, qq|
136CREATE UNIQUE INDEX node_name ON node (name)
137| ],
138
139        content => [ qq|
140CREATE TABLE content (
141  node_id   integer      NOT NULL,
142  version   int(10)      NOT NULL default 0,
143  text      mediumtext   NOT NULL default '',
144  modified  datetime     default NULL,
145  comment   mediumtext   NOT NULL default '',
146  moderated bool         NOT NULL default '1',
147  verified  datetime     default NULL,
148  verified_info mediumtext NOT NULL default '',
149  PRIMARY KEY (node_id, version)
150)
151| ],
152        internal_links => [ qq|
153CREATE TABLE internal_links (
154  link_from varchar(200) NOT NULL default '',
155  link_to   varchar(200) NOT NULL default '',
156  PRIMARY KEY (link_from, link_to)
157)
158| ],
159        metadata => [ qq|
160CREATE TABLE metadata (
161  node_id        integer      NOT NULL,
162  version        int(10)      NOT NULL default 0,
163  metadata_type  varchar(200) NOT NULL DEFAULT '',
164  metadata_value mediumtext   NOT NULL DEFAULT ''
165)
166|, qq|
167CREATE INDEX metadata_index ON metadata(node_id, version, metadata_type, metadata_value(10))
168| ]
169    },
170};
171
172my %fetch_upgrades = (
173    old_to_8  => 1,
174    old_to_9  => 1,
175    old_to_10 => 1,
176    '8_to_9'  => 1,
177    '8_to_10' => 1,
178);
179
180my %upgrades = (
181    '9_to_10' => [ sub {
182        my $dbh = shift;
183        my $sth = $dbh->prepare('SHOW INDEX FROM node WHERE key_name="node_name"');
184        $sth->execute();
185        unless ( $sth->rows ) {
186            $dbh->do('CREATE UNIQUE INDEX node_name ON node (name)')
187                or croak $dbh->errstr;
188        }
189    },
190    qq|
191ALTER TABLE content ADD COLUMN verified datetime default NULL
192|, qq|
193ALTER TABLE content ADD COLUMN verified_info mediumtext NOT NULL default ''
194|, qq|
195UPDATE schema_info SET version = 10
196| ]
197
198);
199
200=head1 NAME
201
202Wiki::Toolkit::Setup::MySQL - Set up tables for a Wiki::Toolkit store in a MySQL database.
203
204=head1 SYNOPSIS
205
206  use Wiki::Toolkit::Setup::MySQL;
207  Wiki::Toolkit::Setup::MySQL::setup($dbname, $dbuser, $dbpass, $dbhost);
208
209Omit $dbhost if the database is local.
210
211=head1 DESCRIPTION
212
213Set up a MySQL database for use as a Wiki::Toolkit store.
214
215=head1 FUNCTIONS
216
217=over 4
218
219=item B<setup>
220
221  use Wiki::Toolkit::Setup::MySQL;
222  Wiki::Toolkit::Setup::MySQL::setup($dbname, $dbuser, $dbpass, $dbhost);
223
224or
225
226  Wiki::Toolkit::Setup::Mysql::setup( $dbh );
227
228You can either provide an active database handle C<$dbh> or connection
229parameters.                                                                   
230
231If you provide connection parameters the following arguments are
232mandatory -- the database name, the username and the password. The
233username must be able to create and drop tables in the database.
234
235The $dbhost argument is optional -- omit it if the database is local.
236
237B<NOTE:> If a table that the module wants to create already exists,
238C<setup> will leave it alone. This means that you can safely run this
239on an existing L<Wiki::Toolkit> database to bring the schema up to date
240with the current L<Wiki::Toolkit> version. If you wish to completely start
241again with a fresh database, run C<cleardb> first.
242
243=cut
244
245sub setup {
246    my @args = @_;
247    my $dbh = _get_dbh( @args );
248    my $disconnect_required = _disconnect_required( @args );
249    my $wanted_schema = _get_wanted_schema( @args ) || $SCHEMA_VERSION;
250
251    die "No schema information for requested schema version $wanted_schema\n"
252        unless $create_sql->{$wanted_schema};
253
254    # Check whether tables exist
255    my %tables = fetch_tables_listing($dbh, $wanted_schema);
256
257    # Do we need to upgrade the schema of existing tables?
258    # (Don't check if no tables currently exist)
259    my $upgrade_schema;
260    my @cur_data;
261    if(scalar keys %tables > 0) {
262        $upgrade_schema = Wiki::Toolkit::Setup::Database::get_database_upgrade_required($dbh,$wanted_schema);
263    }
264    if($upgrade_schema) {
265        if ($fetch_upgrades{$upgrade_schema}) {
266            # Grab current data
267            print "Upgrading: $upgrade_schema\n";
268            @cur_data = eval("&Wiki::Toolkit::Setup::Database::fetch_upgrade_".$upgrade_schema."(\$dbh)");
269            if($@) { warn $@; }
270
271            # Check to make sure we can create, index and drop tables
272            # before doing any more
273            my $perm_check = Wiki::Toolkit::Setup::Database::perm_check($dbh);
274            if ($perm_check) {
275                die "Unable to create/drop database tables as required by upgrade: $perm_check";
276            }
277       
278            # Drop the current tables
279            cleardb($dbh);
280
281            # Grab new list of tables
282            %tables = fetch_tables_listing($dbh, $wanted_schema);
283        }
284    }
285
286    # Set up tables if not found
287    foreach my $required ( keys %{$create_sql->{$wanted_schema}} ) {
288        if ( $tables{$required} ) {
289            print "Table $required already exists... skipping...\n";
290        } else {
291            print "Creating table $required... done\n";
292            foreach my $sql ( @{$create_sql->{$wanted_schema}->{$required}} ) {
293                $dbh->do($sql) or croak $dbh->errstr;
294            }
295        }
296    }
297
298    # If upgrading, load in the new data
299    if($upgrade_schema) {
300        if ($fetch_upgrades{$upgrade_schema}) {
301            Wiki::Toolkit::Setup::Database::bulk_data_insert($dbh,@cur_data);
302        } else {
303            print "Upgrading schema: $upgrade_schema\n";
304            my @updates = @{$upgrades{$upgrade_schema}};
305            foreach my $update (@updates) {
306                if(ref($update) eq "CODE") {
307                    &$update($dbh);
308                } elsif(ref($update) eq "ARRAY") {
309                    foreach my $nupdate (@$update) {
310                        $dbh->do($nupdate);
311                    }
312                } else {
313                    $dbh->do($update);
314                }
315            } 
316        }
317    }
318
319    # Clean up if we made our own dbh.
320    $dbh->disconnect if $disconnect_required;
321}
322
323# Internal method - what Wiki::Toolkit tables are defined?
324sub fetch_tables_listing {
325    my $dbh = shift;
326    my $wanted_schema = shift;
327
328    # Check what tables exist
329    my $sth = $dbh->prepare("SHOW TABLES") or croak $dbh->errstr;
330    $sth->execute;
331    my %tables;
332    while ( my $table = $sth->fetchrow_array ) {
333        exists $create_sql->{$wanted_schema}->{$table} and $tables{$table} = 1;
334    }
335    return %tables;
336}
337
338=item B<cleardb>
339
340  use Wiki::Toolkit::Setup::MySQL;
341
342  # Clear out all Wiki::Toolkit tables from the database.
343  Wiki::Toolkit::Setup::MySQL::cleardb($dbname, $dbuser, $dbpass, $dbhost);
344
345or
346
347  Wiki::Toolkit::Setup::Mysql::cleardb( $dbh );
348
349You can either provide an active database handle C<$dbh> or connection
350parameters.                                                                   
351
352If you provide connection parameters the following arguments are
353mandatory -- the database name, the username and the password. The
354username must be able to drop tables in the database.
355
356The $dbhost argument is optional -- omit if the database is local.
357
358Clears out all L<Wiki::Toolkit> store tables from the database. B<NOTE>
359that this will lose all your data; you probably only want to use this
360for testing purposes or if you really screwed up somewhere. Note also
361that it doesn't touch any L<Wiki::Toolkit> search backend tables; if you
362have any of those in the same or a different database see either
363L<Wiki::Toolkit::Setup::DBIxFTS> or L<Wiki::Toolkit::Setup::SII>, depending on
364which search backend you're using.
365
366=cut
367
368sub cleardb {
369    my @args = @_;
370    my $dbh = _get_dbh( @args );
371    my $disconnect_required = _disconnect_required( @args );
372
373    print "Dropping tables... ";
374    $dbh->do("DROP TABLE IF EXISTS " . join( ",", keys %{$create_sql->{$SCHEMA_VERSION}} ) )
375      or croak $dbh->errstr;
376    print "done\n";
377
378    # Clean up if we made our own dbh.
379    $dbh->disconnect if $disconnect_required;
380}
381
382sub _get_dbh {
383    # Database handle passed in.
384    if ( ref $_[0] and ref $_[0] eq 'DBI::db' ) {
385        return $_[0];
386    }
387
388    # Args passed as hashref.
389    if ( ref $_[0] and ref $_[0] eq 'HASH' ) {
390        my %args = %{$_[0]};
391        if ( $args{dbh} ) {
392            return $args{dbh};
393    } else {
394            return _make_dbh( %args );
395        }
396    }
397
398    # Args passed as list of connection details.
399    return _make_dbh(
400                      dbname => $_[0],
401                      dbuser => $_[1],
402                      dbpass => $_[2],
403                      dbhost => $_[3],
404                    );
405}
406
407sub _get_wanted_schema {
408    # Database handle passed in.
409    if ( ref $_[0] and ref $_[0] eq 'DBI::db' ) {
410        return undef;
411    }
412
413    # Args passed as hashref.
414    if ( ref $_[0] and ref $_[0] eq 'HASH' ) {
415        my %args = %{$_[0]};
416        return $args{wanted_schema};
417    }
418}
419
420sub _disconnect_required {
421    # Database handle passed in.
422    if ( ref $_[0] and ref $_[0] eq 'DBI::db' ) {
423        return 0;
424    }
425
426    # Args passed as hashref.
427    if ( ref $_[0] and ref $_[0] eq 'HASH' ) {
428        my %args = %{$_[0]};
429        if ( $args{dbh} ) {
430            return 0;
431    } else {
432            return 1;
433        }
434    }
435
436    # Args passed as list of connection details.
437    return 1;
438}
439
440sub _make_dbh {
441    my %args = @_;
442    my $dsn = "dbi:mysql:$args{dbname}";
443    $dsn .= ";host=$args{dbhost}" if $args{dbhost};
444    my $dbh = DBI->connect($dsn, $args{dbuser}, $args{dbpass},
445               { PrintError => 1, RaiseError => 1,
446                 AutoCommit => 1 } )
447        or croak DBI::errstr;
448    return $dbh;
449}
450
451=back
452
453=head1 ALTERNATIVE CALLING SYNTAX
454
455As requested by Podmaster.  Instead of passing arguments to the methods as
456
457  ($dbname, $dbuser, $dbpass, $dbhost)
458
459you can pass them as
460
461  ( { dbname => $dbname,
462      dbuser => $dbuser,
463      dbpass => $dbpass,
464      dbhost => $dbhost
465    }
466  )
467
468or indeed as
469
470  ( { dbh => $dbh } )
471
472Note that's a hashref, not a hash.
473
474=head1 AUTHOR
475
476Kake Pugh (kake@earth.li).
477
478=head1 COPYRIGHT
479
480     Copyright (C) 2002-2004 Kake Pugh.  All Rights Reserved.
481     Copyright (C) 2006-2008 the Wiki::Toolkit team. All Rights Reserved.
482
483This module is free software; you can redistribute it and/or modify it
484under the same terms as Perl itself.
485
486=head1 SEE ALSO
487
488L<Wiki::Toolkit>, L<Wiki::Toolkit::Setup::DBIxMySQL>, L<Wiki::Toolkit::Setup::SII>
489
490=cut
491
4921;
493
Note: See TracBrowser for help on using the repository browser.