Max Maischein > DBIx-RunSQL-0.09 > DBIx::RunSQL

Download:
DBIx-RunSQL-0.09.tar.gz

Dependencies

Annotate this POD

CPAN RT

Open  0
View/Report Bugs
Module Version: 0.09   Source   Latest Release: DBIx-RunSQL-0.12

NAME ^

DBIx::RunSQL - run SQL to create a database schema

SYNOPSIS ^

    #!/usr/bin/perl -w
    use strict;
    use lib 'lib';
    use DBIx::RunSQL;

    my $test_dbh = DBIx::RunSQL->create(
        dsn     => 'dbi:SQLite:dbname=:memory:',
        sql     => 'sql/create.sql',
        force   => 1,
        verbose => 1,
    );
    
    ... # run your tests with a DB setup fresh from setup.sql

METHODS ^

DBIx::RunSQL->create ARGS

Creates the database and returns the database handle

DBIx::RunSQL->run_sql_file ARGS

    my $dbh = DBI->connect(...)
    
    for my $file (sort glob '*.sql') {
        DBIx::RunSQL->run_sql_file(
            verbose => 1,
            dbh     => $dbh,
            sql     => $file,
        );
    };

Runs an SQL file on a prepared database handle.

PROGRAMMER USAGE ^

This module abstracts away the "run these SQL statements to set up your database" into a module. In some situations you want to give the setup SQL to a database admin, but in other situations, for example testing, you want to run the SQL statements against an in-memory database. This module abstracts away the reading of SQL from a file and allows for various command line parameters to be passed in. A skeleton create-db.sql looks like this:

    #!/usr/bin/perl -w
    use strict;
    use lib 'lib';
    use DBIx::RunSQL;

    DBIx::RunSQL->handle_command_line('myapp');

    =head1 NAME

    create-db.pl - Create the database

    =head1 ABSTRACT

    This sets up the database. The following
    options are recognized:

    =over 4

    =item C<--user> USERNAME

    =item C<--password> PASSWORD

    =item C<--dsn> DSN

    The DBI DSN to use for connecting to
    the database

    =item C<--sql> SQLFILE

    The alternative SQL file to use
    instead of C<sql/create.sql>.

    =item C<--force>

    Don't stop on errors

    =item C<--help>

    Show this message.

    =cut

DBIx::RunSQL->handle_command_line

Parses the command line. This is a convenience method, which passes the following command line arguments to ->create:

  --user
  --password
  --dsn
  --sql
  --force
  --verbose

In addition, it handles the following switches through Pod::Usage:

  --help
  --man

See also the section PROGRAMMER USAGE for a sample program to set up a database from an SQL file.

NOTES ^

COMMENT FILTERING

The module tries to keep the SQL as much verbatim as possible. It filters all lines that end in semicolons but contain only SQL comments. All other comments are passed through to the database with the next statement.

TRIGGER HANDLING

This module uses a very simplicistic approach to recognize triggers. Triggers are problematic because they consist of multiple SQL statements and this module does not implement a full SQL parser. An trigger is recognized by the following sequence of lines

    CREATE TRIGGER
        ...
    END;

If your SQL dialect uses a different syntax, it might still work to put the whole trigger on a single line in the input file.

OTHER APPROACHES

If you find yourself wanting to write SELECT statements, consider looking at Querylet instead, which is geared towards that and even has an interface for Excel or HTML output.

If you find yourself wanting to write parametrized queries as .sql files, consider looking at Data::Phrasebook::SQL or potentially DBIx::SQLHandler.

SEE ALSO ^

ORLite::Migrate

REPOSITORY ^

The public repository of this module is http://github.com/Corion/DBIx--RunSQL.

SUPPORT ^

The public support forum of this module is http://perlmonks.org/.

BUG TRACKER ^

Please report bugs in this module via the RT CPAN bug queue at https://rt.cpan.org/Public/Dist/Display.html?Name=DBIx-RunSQL or via mail to bug-dbix-runsql@rt.cpan.org.

AUTHOR ^

Max Maischein corion@cpan.org

COPYRIGHT (c) ^

Copyright 2009-2011 by Max Maischein corion@cpan.org.

LICENSE ^

This module is released under the same terms as Perl itself.

syntax highlighting: