DBIx::RunSQL - run SQL from a file
#!/usr/bin/perl -w use strict; use DBIx::RunSQL; my $test_dbh = DBIx::RunSQL->create( dsn => 'dbi:SQLite:dbname=:memory:', sql => 'sql/create.sql', force => 1, verbose => 1, formatter => 'Text::Table', rotate => 1, null => '(Null)', ); # now run your tests with a DB setup fresh from setup.sql
DBIx::RunSQL->create ARGS
DBIx::RunSQL->run ARGS
Runs the SQL commands and returns the database handle. In list context, it returns the database handle and the suggested exit code.
sql - name of the file containing the SQL statements
sql
The default is sql/create.sql
sql/create.sql
If sql is a reference to a glob or a filehandle, the SQL will be read from that. not implemented
If sql is undefined, the $::DATA or the 0 filehandle will be read until exhaustion. not implemented
$::DATA
0
This allows one to create SQL-as-programs as follows:
#!/usr/bin/perl -w -MDBIx::RunSQL -e 'create()' create table ...
If you want to run SQL statements from a scalar, you can simply pass in a reference to a scalar containing the SQL:
sql => \"update mytable set foo='bar';",
dsn, user, password, options - DBI parameters for connecting to the DB
dsn
user
password
options
dbh - a premade database handle to be used instead of dsn
dbh
force - continue even if errors are encountered
force
verbose - print each SQL statement as it is run
verbose
verbose_handler - callback to call with each SQL statement instead of print
verbose_handler
print
verbose_fh - filehandle to write to instead of STDOUT
verbose_fh
STDOUT
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. Returns the number of errors encountered.
If the statement returns rows, these are printed separated with tabs.
dbh - a premade database handle
fh - filehandle to the file containing the SQL statements
fh
output_bool - whether to exit with a nonzero exit code if any row is found
output_bool
This makes the function return a nonzero value even if there is no error but a row was found.
output_string - whether to output the (one) row and column, without any headers
output_string
formatter - see the <formatter> option of ->format_results
formatter
<formatter
->format_results
rotate - rotate the table by 90° , outputting columns as rows
rotate
null - string to replace SQL NULL columns by
null
NULL
DBIx::RunSQL->run_sql ARGS
my $dbh = DBI->connect(...) DBIx::RunSQL->run_sql( verbose => 1, dbh => $dbh, sql => \@sql_statements, );
Runs an SQL string on a prepared database handle. Returns the number of errors encountered.
If the statement returns rows, these are printed separated with tabs, but see the output_bool and output_string options.
sql - string or array reference containing the SQL statements
DBIx::RunSQL->format_results %options
my $sth= $dbh->prepare( 'select * from foo' ); $sth->execute(); print DBIx::RunSQL->format_results( sth => $sth );
Executes $sth->fetchall_arrayref and returns the results either as tab separated string or formatted using Text::Table if the module is available.
$sth->fetchall_arrayref
If you find yourself using this often to create reports, you may really want to look at Querylet instead.
sth - the executed statement handle
sth
formatter - if you want to force tab or Text::Table usage, you can do it through that parameter. In fact, the module will use anything other than tab as the class name and assume that the interface is compatible to Text::Table.
tab
Text::Table
no_header_when_empty - don't print anything if there are no results
no_header_when_empty
Note that the query results are returned as one large string, so you really do not want to run this for large(r) result sets.
DBIx::RunSQL->split_sql ARGS
my @statements= DBIx::RunSQL->split_sql( <<'SQL'); create table foo (name varchar(64)); create trigger foo_insert on foo before insert; new.name= 'foo-'||old.name; end; insert into foo name values ('bar'); SQL # Returns three elements
This is a helper subroutine to split a sequence of (semicolon-newline-delimited) SQL statements into separate statements. It is documented because it is not a very smart subroutine and you might want to override or replace it. It might also be useful outside the context of DBIx::RunSQL if you need to split up a large blob of SQL statements into smaller pieces.
The subroutine needs the whole sequence of SQL statements in memory. If you are attempting to restore a large SQL dump backup into your database, this approach might not be suitable.
DBIx::RunSQL->parse_command_line
my $options = DBIx::RunSQL->parse_command_line( 'my_application', \@ARGV );
Helper function to turn a command line array into options for DBIx::RunSQL invocations. The array of command line items is modified in-place.
If the reference to the array of command line items is missing, @ARGV will be modified instead.
@ARGV
DBIx::RunSQL->handle_command_line
DBIx::RunSQL->handle_command_line( 'my_application', \@ARGV );
Helper function to run the module functionality from the command line. See below how to use this function in a good self-contained script. This function passes the following command line arguments and options to ->create:
->create
--user --password --dsn --sql --quiet --format --force --verbose --bool --string --rotate --null
In addition, it handles the following switches through Pod::Usage:
--help --man
If no SQL is given, this function will read the SQL from STDIN.
If no dsn is given, this function will use dbi:SQLite:dbname=db/$appname.sqlite as the default database.
dbi:SQLite:dbname=db/$appname.sqlite
See also the section PROGRAMMER USAGE for a sample program to set up a database from an SQL file.
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.pl looks like this:
create-db.pl
#!/usr/bin/perl -w use strict; use DBIx::RunSQL; my $exitcode = DBIx::RunSQL->handle_command_line('myapp', \@ARGV); exit $exitcode; =head1 NAME create-db.pl - Create the database =head1 SYNOPSIS create-db.pl "select * from mytable where 1=0" =head1 ABSTRACT This sets up the database. The following options are recognized: =head1 OPTIONS =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<--quiet> Output no headers for empty SELECT resultsets =item C<--bool> Set the exit code to 1 if at least one result row was found =item C<--string> Output the (single) column that the query returns as a string without any headers =item C<--format> formatter Use a different formatter for table output. Supported formatters are tab - output results as tab delimited columns Text::Table - output results as ASCII table =item C<--force> Don't stop on errors =item C<--help> Show this message. =back =cut
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.
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.
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.
.sql
ORLite::Migrate
Test::SQLite - SQLite setup/teardown for tests, mostly geared towards testing, not general database setup
The public repository of this module is https://github.com/Corion/DBIx--RunSQL.
The public support forum of this module is https://perlmonks.org/.
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.
Max Maischein corion@cpan.org
corion@cpan.org
Copyright 2009-2021 by Max Maischein corion@cpan.org.
This module is released under the same terms as Perl itself.
To install DBIx::RunSQL, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::RunSQL
CPAN shell
perl -MCPAN -e shell install DBIx::RunSQL
For more information on module installation, please visit the detailed CPAN module installation guide.