NAME

Module::CoreList::DBSchema - A database schema for Module::CoreList

SYNOPSIS

  # this requires DBI and DBD::SQLite which are available from CPAN

  use strict;
  use warnings;
  use DBI;
  use Module::CoreList::DBSchema;
  
  $|=1;
  
  my $dbh = DBI->connect('dbi:SQLite:dbname=corelist.db','','') or die $DBI::errstr;
  $dbh->do(qq{PRAGMA synchronous = OFF}) or die $dbh->errstr;
  
  my $mcdbs = Module::CoreList::DBSchema->new();
  
  # create tables
  
  my %tables = $mcdbs->tables();
  
  print "Creating tables ... ";
  
  foreach my $table ( keys %tables ) {
    my $sql = 'CREATE TABLE IF NOT EXISTS ' . $table . ' ( ';
    $sql .= join ', ', @{ $tables{$table} };
    $sql .= ' )';
    $dbh->do($sql) or die $dbh->errstr;
    $dbh->do('DELETE FROM ' . $table) or die $dbh->errstr;
  }
  
  print "DONE\n";
  
  # populate with data
  
  my @data = $mcdbs->data();
  
  print "Populating tables ... ";
  
  $dbh->begin_work;
  
  foreach my $row ( @data ) {
    my $sql = shift @{ $row };
    my $sth = $dbh->prepare_cached($sql) or die $dbh->errstr;
    $sth->execute( @{ $row } ) or die $dbh->errstr;
  }
  
  $dbh->commit;
  
  print "DONE\n";
  
  # done

DESCRIPTION

Module::CoreList::DBSchema provides methods for building a database from the information that is provided by Module::CoreList.

CONSTRUCTOR

new

Creates a new Module::CoreList::DBSchema object.

  my $mcdbs = Module::CoreList::DBSchema->new();

METHODS

tables

In a scalar context returns a hashref data structure keyed on table name.

In a list context returns a list of the same data structure.

  my %tables = $mcdbs->tables();
  
  foreach my $table ( keys %tables ) {
    my $sql = 'CREATE TABLE IF NOT EXISTS ' . $table . ' ( ';
    $sql .= join ', ', @{ $tables{$table} };
    $sql .= ' )';
    $dbh->do($sql) or die $dbh->errstr;
    $dbh->do('DELETE FROM ' . $table) or die $dbh->errstr;
  }
  

data

In a list context returns a list of arrayrefs which contain a SQL statement as the first element and the remaining elements being bind values for the SQL statement.

In a scalar context returns an arrayref which contains the above arrayrefs.

  my @data = $mcdbs->data();
  
  foreach my $row ( @data ) {
    my $sql = shift @{ $row };
    my $sth = $dbh->prepare_cached($sql) or die $dbh->errstr;
    $sth->execute( @{ $row } ) or die $dbh->errstr;
  }

You may provide some optional arguments:

  prefix, a string to prefix to the table names in the resultant SQL;
  

queries

Returns a list of the available SQL queries.

  my @queries = $mcdbs->queries();

query

Takes one argument, the name of a query to lookup.

Returns in list context a list consisting of a SQL string and a flag indicating whether the SQL string includes placeholders.

In scalar context returns an array reference containing the same as above.

  my $sql = $mcdbs->query('corelist');

AUTHOR

Chris BinGOs Williams <chris@bingosnet.co.uk>

LICENSE

Copyright © Chris Williams

This module may be used, modified, and distributed under the same terms as Perl itself. Please see the license that came with your Perl distribution for details.

SEE ALSO

Module::CoreList

DBI