1 | package Wiki::Toolkit::Setup::MySQL; |
---|
2 | |
---|
3 | use strict; |
---|
4 | |
---|
5 | use vars qw( @ISA $VERSION $SCHEMA_VERSION ); |
---|
6 | |
---|
7 | use Wiki::Toolkit::Setup::Database; |
---|
8 | |
---|
9 | @ISA = qw( Wiki::Toolkit::Setup::Database ); |
---|
10 | $VERSION = '0.10'; |
---|
11 | |
---|
12 | use DBI; |
---|
13 | use Carp; |
---|
14 | |
---|
15 | $SCHEMA_VERSION = $VERSION*100; |
---|
16 | |
---|
17 | my $create_sql = { |
---|
18 | 8 => { |
---|
19 | schema_info => [ qq| |
---|
20 | CREATE TABLE schema_info ( |
---|
21 | version int(10) NOT NULL default 0 |
---|
22 | ) |
---|
23 | |, qq| |
---|
24 | INSERT INTO schema_info VALUES (8) |
---|
25 | | ], |
---|
26 | |
---|
27 | node => [ qq| |
---|
28 | CREATE 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| |
---|
39 | CREATE 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| |
---|
49 | CREATE 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| |
---|
56 | CREATE 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| |
---|
63 | CREATE INDEX metadata_index ON metadata(node_id, version, metadata_type, metadata_value(10)) |
---|
64 | | ] |
---|
65 | }, |
---|
66 | 9 => { |
---|
67 | schema_info => [ qq| |
---|
68 | CREATE TABLE schema_info ( |
---|
69 | version int(10) NOT NULL default 0 |
---|
70 | ) |
---|
71 | |, qq| |
---|
72 | INSERT INTO schema_info VALUES (9) |
---|
73 | | ], |
---|
74 | |
---|
75 | node => [ qq| |
---|
76 | CREATE 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| |
---|
88 | CREATE 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| |
---|
99 | CREATE 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| |
---|
106 | CREATE 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| |
---|
113 | CREATE INDEX metadata_index ON metadata(node_id, version, metadata_type, metadata_value(10)) |
---|
114 | | ] |
---|
115 | }, |
---|
116 | 10 => { |
---|
117 | schema_info => [ qq| |
---|
118 | CREATE TABLE schema_info ( |
---|
119 | version int(10) NOT NULL default 0 |
---|
120 | ) |
---|
121 | |, qq| |
---|
122 | INSERT INTO schema_info VALUES (10) |
---|
123 | | ], |
---|
124 | |
---|
125 | node => [ qq| |
---|
126 | CREATE 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| |
---|
136 | CREATE UNIQUE INDEX node_name ON node (name) |
---|
137 | | ], |
---|
138 | |
---|
139 | content => [ qq| |
---|
140 | CREATE 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| |
---|
153 | CREATE 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| |
---|
160 | CREATE 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| |
---|
167 | CREATE INDEX metadata_index ON metadata(node_id, version, metadata_type, metadata_value(10)) |
---|
168 | | ] |
---|
169 | }, |
---|
170 | }; |
---|
171 | |
---|
172 | my %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 | |
---|
180 | my %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| |
---|
191 | ALTER TABLE content ADD COLUMN verified datetime default NULL |
---|
192 | |, qq| |
---|
193 | ALTER TABLE content ADD COLUMN verified_info mediumtext NOT NULL default '' |
---|
194 | |, qq| |
---|
195 | UPDATE schema_info SET version = 10 |
---|
196 | | ] |
---|
197 | |
---|
198 | ); |
---|
199 | |
---|
200 | =head1 NAME |
---|
201 | |
---|
202 | Wiki::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 | |
---|
209 | Omit $dbhost if the database is local. |
---|
210 | |
---|
211 | =head1 DESCRIPTION |
---|
212 | |
---|
213 | Set 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 | |
---|
224 | or |
---|
225 | |
---|
226 | Wiki::Toolkit::Setup::Mysql::setup( $dbh ); |
---|
227 | |
---|
228 | You can either provide an active database handle C<$dbh> or connection |
---|
229 | parameters. |
---|
230 | |
---|
231 | If you provide connection parameters the following arguments are |
---|
232 | mandatory -- the database name, the username and the password. The |
---|
233 | username must be able to create and drop tables in the database. |
---|
234 | |
---|
235 | The $dbhost argument is optional -- omit it if the database is local. |
---|
236 | |
---|
237 | B<NOTE:> If a table that the module wants to create already exists, |
---|
238 | C<setup> will leave it alone. This means that you can safely run this |
---|
239 | on an existing L<Wiki::Toolkit> database to bring the schema up to date |
---|
240 | with the current L<Wiki::Toolkit> version. If you wish to completely start |
---|
241 | again with a fresh database, run C<cleardb> first. |
---|
242 | |
---|
243 | =cut |
---|
244 | |
---|
245 | sub 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? |
---|
324 | sub 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 | |
---|
345 | or |
---|
346 | |
---|
347 | Wiki::Toolkit::Setup::Mysql::cleardb( $dbh ); |
---|
348 | |
---|
349 | You can either provide an active database handle C<$dbh> or connection |
---|
350 | parameters. |
---|
351 | |
---|
352 | If you provide connection parameters the following arguments are |
---|
353 | mandatory -- the database name, the username and the password. The |
---|
354 | username must be able to drop tables in the database. |
---|
355 | |
---|
356 | The $dbhost argument is optional -- omit if the database is local. |
---|
357 | |
---|
358 | Clears out all L<Wiki::Toolkit> store tables from the database. B<NOTE> |
---|
359 | that this will lose all your data; you probably only want to use this |
---|
360 | for testing purposes or if you really screwed up somewhere. Note also |
---|
361 | that it doesn't touch any L<Wiki::Toolkit> search backend tables; if you |
---|
362 | have any of those in the same or a different database see either |
---|
363 | L<Wiki::Toolkit::Setup::DBIxFTS> or L<Wiki::Toolkit::Setup::SII>, depending on |
---|
364 | which search backend you're using. |
---|
365 | |
---|
366 | =cut |
---|
367 | |
---|
368 | sub 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 | |
---|
382 | sub _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 | |
---|
407 | sub _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 | |
---|
420 | sub _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 | |
---|
440 | sub _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 | |
---|
455 | As requested by Podmaster. Instead of passing arguments to the methods as |
---|
456 | |
---|
457 | ($dbname, $dbuser, $dbpass, $dbhost) |
---|
458 | |
---|
459 | you can pass them as |
---|
460 | |
---|
461 | ( { dbname => $dbname, |
---|
462 | dbuser => $dbuser, |
---|
463 | dbpass => $dbpass, |
---|
464 | dbhost => $dbhost |
---|
465 | } |
---|
466 | ) |
---|
467 | |
---|
468 | or indeed as |
---|
469 | |
---|
470 | ( { dbh => $dbh } ) |
---|
471 | |
---|
472 | Note that's a hashref, not a hash. |
---|
473 | |
---|
474 | =head1 AUTHOR |
---|
475 | |
---|
476 | Kake 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 | |
---|
483 | This module is free software; you can redistribute it and/or modify it |
---|
484 | under the same terms as Perl itself. |
---|
485 | |
---|
486 | =head1 SEE ALSO |
---|
487 | |
---|
488 | L<Wiki::Toolkit>, L<Wiki::Toolkit::Setup::DBIxMySQL>, L<Wiki::Toolkit::Setup::SII> |
---|
489 | |
---|
490 | =cut |
---|
491 | |
---|
492 | 1; |
---|
493 | |
---|