NAME

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

VERSION

version 0.08

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');

SEE ALSO

Module::CoreList

DBI

AUTHOR

Chris Williams

COPYRIGHT AND LICENSE

This software is copyright (c) 2017 by Chris Williams.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.