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.