So you're starting a new project, and you've designed a database
schema, and you want to write some code to set up the tables in the
database. You know this won't be a one-off, since you'll want to
initialise a test database every time you run your tests, plus you
plan to deploy your application in several places.
So you write the following module:
----------------------------------------------------------------------
package Foo::Setup::SQLite;
use strict;
use Carp;
use DBI;
my %create_sql = __PACKAGE__->_get_create_sql;
sub _get_create_sql {
my $class = shift;
return ( table1 => "CREATE TABLE table1 ( uid integer, ... )",
...
);
}
sub setup {
my ($class, %args) = @_;
my $dbh = DBI->connect("dbi:SQLite:dbname=$args{dbname}", "", "",
{ PrintError => 1, RaiseError => 1,
AutoCommit => 1 } )
or croak DBI::errstr;
# Check whether tables exist, set them up if not.
my $sql = "SELECT name FROM sqlite_master
WHERE type='table' AND name in ("
. join( ",", map { $dbh->quote($_) } keys %create_sql ) . ")";
my $sth = $dbh->prepare($sql) or croak $dbh->errstr;
$sth->execute;
my %tables;
while ( my $table = $sth->fetchrow_array ) {
$tables{$table} = 1;
}
foreach my $required ( keys %create_sql ) {
if ( $tables{$required} ) {
print "Table $required already exists... skipping...\n";
} else {
print "Creating table $required... done\n";
$dbh->do($create_sql{$required}) or croak $dbh->errstr;
}
}
$dbh->disconnect;
}
----------------------------------------------------------------------
(Except you write it better, without the $dbh->quote and with the
facility to execute more than one SQL statement per table if you're
using a database that wants the primary key defined separately and
blah blah blah, my DBI skillz are not the question here.)
Now I hate doing things over and over again. I just found myself
copying and pasting this code for the second time this month, and it
makes me feel dirty. Surely there is a simple module on CPAN that I
can pass a big pile of CREATE TABLE statements to, and it'll check
whether the tables exist, set them up if not, be silent or not as I
require, force clearing of existing tables at my whim. What is this
module called?
Kake
