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.