.\" Automatically generated by Pod::Man 4.11 (Pod::Simple 3.35)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
.    if \nF \{\
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{\
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "DBIx::RunSQL 3pm"
.TH DBIx::RunSQL 3pm "2019-11-03" "perl v5.30.0" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
DBIx::RunSQL \- run SQL from a file
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 3
\&    #!/usr/bin/perl \-w
\&    use strict;
\&    use DBIx::RunSQL;
\&
\&    my $test_dbh = DBIx::RunSQL\->create(
\&        dsn     => \*(Aqdbi:SQLite:dbname=:memory:\*(Aq,
\&        sql     => \*(Aqsql/create.sql\*(Aq,
\&        force   => 1,
\&        verbose => 1,
\&    );
\&
\&    # now run your tests with a DB setup fresh from setup.sql
.Ve
.SH "METHODS"
.IX Header "METHODS"
.ie n .SS """DBIx::RunSQL\->create ARGS"""
.el .SS "\f(CWDBIx::RunSQL\->create ARGS\fP"
.IX Subsection "DBIx::RunSQL->create ARGS"
.ie n .SS """DBIx::RunSQL\->run ARGS"""
.el .SS "\f(CWDBIx::RunSQL\->run ARGS\fP"
.IX Subsection "DBIx::RunSQL->run ARGS"
Runs the \s-1SQL\s0 commands and returns the database handle.
In list context, it returns the database handle and the
suggested exit code.
.IP "\(bu" 4
\&\f(CW\*(C`sql\*(C'\fR \- name of the file containing the \s-1SQL\s0 statements
.Sp
The default is \f(CW\*(C`sql/create.sql\*(C'\fR
.Sp
If \f(CW\*(C`sql\*(C'\fR is a reference to a glob or a filehandle,
the \s-1SQL\s0 will be read from that. \fBnot implemented\fR
.Sp
If \f(CW\*(C`sql\*(C'\fR is undefined, the \f(CW$::DATA\fR or the \f(CW0\fR filehandle will
be read until exhaustion.  \fBnot implemented\fR
.Sp
This allows one to create SQL-as-programs as follows:
.Sp
.Vb 2
\&  #!/usr/bin/perl \-w \-MDBIx::RunSQL \-e \*(Aqcreate()\*(Aq
\&  create table ...
.Ve
.Sp
If you want to run \s-1SQL\s0 statements from a scalar,
you can simply pass in a reference to a scalar containing the \s-1SQL:\s0
.Sp
.Vb 1
\&    sql => \e"update mytable set foo=\*(Aqbar\*(Aq;",
.Ve
.IP "\(bu" 4
\&\f(CW\*(C`dsn\*(C'\fR, \f(CW\*(C`user\*(C'\fR, \f(CW\*(C`password\*(C'\fR \- \s-1DBI\s0 parameters for connecting to the \s-1DB\s0
.IP "\(bu" 4
\&\f(CW\*(C`dbh\*(C'\fR \- a premade database handle to be used instead of \f(CW\*(C`dsn\*(C'\fR
.IP "\(bu" 4
\&\f(CW\*(C`force\*(C'\fR \- continue even if errors are encountered
.IP "\(bu" 4
\&\f(CW\*(C`verbose\*(C'\fR \- print each \s-1SQL\s0 statement as it is run
.IP "\(bu" 4
\&\f(CW\*(C`verbose_handler\*(C'\fR \- callback to call with each \s-1SQL\s0 statement instead of \f(CW\*(C`print\*(C'\fR
.IP "\(bu" 4
\&\f(CW\*(C`verbose_fh\*(C'\fR \- filehandle to write to instead of \f(CW\*(C`STDOUT\*(C'\fR
.ie n .SS """DBIx::RunSQL\->run_sql_file ARGS"""
.el .SS "\f(CWDBIx::RunSQL\->run_sql_file ARGS\fP"
.IX Subsection "DBIx::RunSQL->run_sql_file ARGS"
.Vb 1
\&    my $dbh = DBI\->connect(...)
\&
\&    for my $file (sort glob \*(Aq*.sql\*(Aq) {
\&        DBIx::RunSQL\->run_sql_file(
\&            verbose => 1,
\&            dbh     => $dbh,
\&            sql     => $file,
\&        );
\&    };
.Ve
.PP
Runs an \s-1SQL\s0 file on a prepared database handle.
Returns the number of errors encountered.
.PP
If the statement returns rows, these are printed
separated with tabs.
.IP "\(bu" 4
\&\f(CW\*(C`dbh\*(C'\fR \- a premade database handle
.IP "\(bu" 4
\&\f(CW\*(C`sql\*(C'\fR \- name of the file containing the \s-1SQL\s0 statements
.IP "\(bu" 4
\&\f(CW\*(C`fh\*(C'\fR \- filehandle to the file containing the \s-1SQL\s0 statements
.IP "\(bu" 4
\&\f(CW\*(C`force\*(C'\fR \- continue even if errors are encountered
.IP "\(bu" 4
\&\f(CW\*(C`verbose\*(C'\fR \- print each \s-1SQL\s0 statement as it is run
.IP "\(bu" 4
\&\f(CW\*(C`verbose_handler\*(C'\fR \- callback to call with each \s-1SQL\s0 statement instead of
\&\f(CW\*(C`print\*(C'\fR
.IP "\(bu" 4
\&\f(CW\*(C`verbose_fh\*(C'\fR \- filehandle to write to instead of \f(CW\*(C`STDOUT\*(C'\fR
.IP "\(bu" 4
\&\f(CW\*(C`output_bool\*(C'\fR \- whether to exit with a nonzero exit code if any row is found
.Sp
This makes the function return a nonzero value even if there is no error
but a row was found.
.IP "\(bu" 4
\&\f(CW\*(C`output_string\*(C'\fR \- whether to output the (one) row and column, without any
headers
.ie n .SS """DBIx::RunSQL\->run_sql ARGS"""
.el .SS "\f(CWDBIx::RunSQL\->run_sql ARGS\fP"
.IX Subsection "DBIx::RunSQL->run_sql ARGS"
.Vb 1
\&    my $dbh = DBI\->connect(...)
\&
\&    DBIx::RunSQL\->run_sql(
\&        verbose => 1,
\&        dbh     => $dbh,
\&        sql     => \e@sql_statements,
\&    );
.Ve
.PP
Runs an \s-1SQL\s0 string on a prepared database handle.
Returns the number of errors encountered.
.PP
If the statement returns rows, these are printed
separated with tabs, but see the \f(CW\*(C`output_bool\*(C'\fR and \f(CW\*(C`output_string\*(C'\fR options.
.IP "\(bu" 4
\&\f(CW\*(C`dbh\*(C'\fR \- a premade database handle
.IP "\(bu" 4
\&\f(CW\*(C`sql\*(C'\fR \- string or array reference containing the \s-1SQL\s0 statements
.IP "\(bu" 4
\&\f(CW\*(C`force\*(C'\fR \- continue even if errors are encountered
.IP "\(bu" 4
\&\f(CW\*(C`verbose\*(C'\fR \- print each \s-1SQL\s0 statement as it is run
.IP "\(bu" 4
\&\f(CW\*(C`verbose_handler\*(C'\fR \- callback to call with each \s-1SQL\s0 statement instead of \f(CW\*(C`print\*(C'\fR
.IP "\(bu" 4
\&\f(CW\*(C`verbose_fh\*(C'\fR \- filehandle to write to instead of \f(CW\*(C`STDOUT\*(C'\fR
.IP "\(bu" 4
\&\f(CW\*(C`output_bool\*(C'\fR \- whether to exit with a nonzero exit code if any row is found
.Sp
This makes the function return a nonzero value even if there is no error
but a row was found.
.IP "\(bu" 4
\&\f(CW\*(C`output_string\*(C'\fR \- whether to output the (one) row and column, without any headers
.ie n .SS """DBIx::RunSQL\->format_results %options"""
.el .SS "\f(CWDBIx::RunSQL\->format_results %options\fP"
.IX Subsection "DBIx::RunSQL->format_results %options"
.Vb 3
\&  my $sth= $dbh\->prepare( \*(Aqselect * from foo\*(Aq );
\&  $sth\->execute();
\&  print DBIx::RunSQL\->format_results( sth => $sth );
.Ve
.PP
Executes \f(CW\*(C`$sth\->fetchall_arrayref\*(C'\fR and returns
the results either as tab separated string
or formatted using Text::Table if the module is available.
.PP
If you find yourself using this often to create reports,
you may really want to look at Querylet instead.
.IP "\(bu" 4
\&\f(CW\*(C`sth\*(C'\fR \- the executed statement handle
.IP "\(bu" 4
\&\f(CW\*(C`formatter\*(C'\fR \- if you want to force \f(CW\*(C`tab\*(C'\fR or \f(CW\*(C`Text::Table\*(C'\fR
usage, you can do it through that parameter.
In fact, the module will use anything other than \f(CW\*(C`tab\*(C'\fR
as the class name and assume that the interface is compatible
to \f(CW\*(C`Text::Table\*(C'\fR.
.PP
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.
.ie n .SS """DBIx::RunSQL\->split_sql ARGS"""
.el .SS "\f(CWDBIx::RunSQL\->split_sql ARGS\fP"
.IX Subsection "DBIx::RunSQL->split_sql ARGS"
.Vb 8
\&  my @statements= DBIx::RunSQL\->split_sql( <<\*(AqSQL\*(Aq);
\&      create table foo (name varchar(64));
\&      create trigger foo_insert on foo before insert;
\&          new.name= \*(Aqfoo\-\*(Aq||old.name;
\&      end;
\&      insert into foo name values (\*(Aqbar\*(Aq);
\&  SQL
\&  # Returns three elements
.Ve
.PP
This is a helper subroutine to split a sequence of (semicolon-newline-delimited)
\&\s-1SQL\s0 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 \s-1SQL\s0 statements into smaller pieces.
.PP
The subroutine needs the whole sequence of \s-1SQL\s0 statements in memory.
If you are attempting to restore a large \s-1SQL\s0 dump backup into your
database, this approach might not be suitable.
.ie n .SS """DBIx::RunSQL\->parse_command_line"""
.el .SS "\f(CWDBIx::RunSQL\->parse_command_line\fP"
.IX Subsection "DBIx::RunSQL->parse_command_line"
.Vb 1
\&    my $options = DBIx::RunSQL\->parse_command_line( \*(Aqmy_application\*(Aq, \e@ARGV );
.Ve
.PP
Helper function to turn a command line array into options for DBIx::RunSQL
invocations. The array of command line items is modified in-place.
.PP
If the reference to the array of command line items is missing, \f(CW@ARGV\fR
will be modified instead.
.ie n .SS """DBIx::RunSQL\->handle_command_line"""
.el .SS "\f(CWDBIx::RunSQL\->handle_command_line\fP"
.IX Subsection "DBIx::RunSQL->handle_command_line"
.Vb 1
\&    DBIx::RunSQL\->handle_command_line( \*(Aqmy_application\*(Aq, \e@ARGV );
.Ve
.PP
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 \f(CW\*(C`\->create\*(C'\fR:
.PP
.Vb 10
\&  \-\-user
\&  \-\-password
\&  \-\-dsn
\&  \-\-sql
\&  \-\-quiet
\&  \-\-format
\&  \-\-force
\&  \-\-verbose
\&  \-\-bool
\&  \-\-string
.Ve
.PP
In addition, it handles the following switches through Pod::Usage:
.PP
.Vb 2
\&  \-\-help
\&  \-\-man
.Ve
.PP
If no \s-1SQL\s0 is given, this function will read the \s-1SQL\s0 from \s-1STDIN.\s0
.PP
If no dsn is given, this function will use
\&\f(CW\*(C` dbi:SQLite:dbname=db/$appname.sqlite \*(C'\fR
as the default database.
.PP
See also the section \s-1PROGRAMMER USAGE\s0 for a sample program to set
up a database from an \s-1SQL\s0 file.
.SH "PROGRAMMER USAGE"
.IX Header "PROGRAMMER USAGE"
This module abstracts away the \*(L"run these \s-1SQL\s0 statements to set up
your database\*(R" into a module. In some situations you want to give the
setup \s-1SQL\s0 to a database admin, but in other situations, for example testing,
you want to run the \s-1SQL\s0 statements against an in-memory database. This
module abstracts away the reading of \s-1SQL\s0 from a file and allows for various
command line parameters to be passed in. A skeleton \f(CW\*(C`create\-db.sql\*(C'\fR
looks like this:
.PP
.Vb 3
\&    #!/usr/bin/perl \-w
\&    use strict;
\&    use DBIx::RunSQL;
\&
\&    my $exitcode = DBIx::RunSQL\->handle_command_line(\*(Aqmyapp\*(Aq, \e@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\*(Aqt stop on errors
\&
\&    =item C<\-\-help>
\&
\&    Show this message.
\&
\&    =back
\&
\&    =cut
.Ve
.SH "NOTES"
.IX Header "NOTES"
.SS "\s-1COMMENT FILTERING\s0"
.IX Subsection "COMMENT FILTERING"
The module tries to keep the \s-1SQL\s0 as much verbatim as possible. It
filters all lines that end in semicolons but contain only \s-1SQL\s0 comments. All
other comments are passed through to the database with the next statement.
.SS "\s-1TRIGGER HANDLING\s0"
.IX Subsection "TRIGGER HANDLING"
This module uses a very simplicistic approach to recognize triggers.
Triggers are problematic because they consist of multiple \s-1SQL\s0 statements
and this module does not implement a full \s-1SQL\s0 parser. An trigger is
recognized by the following sequence of lines
.PP
.Vb 3
\&    CREATE TRIGGER
\&        ...
\&    END;
.Ve
.PP
If your \s-1SQL\s0 dialect uses a different syntax, it might still work to put
the whole trigger on a single line in the input file.
.SS "\s-1OTHER APPROACHES\s0"
.IX Subsection "OTHER APPROACHES"
If you find yourself wanting to write \s-1SELECT\s0 statements,
consider looking at Querylet instead, which is geared towards that
and even has an interface for Excel or \s-1HTML\s0 output.
.PP
If you find yourself wanting to write parametrized queries as
\&\f(CW\*(C`.sql\*(C'\fR files, consider looking at Data::Phrasebook::SQL
or potentially DBIx::SQLHandler.
.SH "SEE ALSO"
.IX Header "SEE ALSO"
ORLite::Migrate
.SH "REPOSITORY"
.IX Header "REPOSITORY"
The public repository of this module is
<http://github.com/Corion/DBIx\*(--RunSQL>.
.SH "SUPPORT"
.IX Header "SUPPORT"
The public support forum of this module is
<http://perlmonks.org/>.
.SH "BUG TRACKER"
.IX Header "BUG TRACKER"
Please report bugs in this module via the \s-1RT CPAN\s0 bug queue at
<https://rt.cpan.org/Public/Dist/Display.html?Name=DBIx\-RunSQL>
or via mail to bug\-dbix\-runsql@rt.cpan.org.
.SH "AUTHOR"
.IX Header "AUTHOR"
Max Maischein \f(CW\*(C`corion@cpan.org\*(C'\fR
.SH "COPYRIGHT (c)"
.IX Header "COPYRIGHT (c)"
Copyright 2009\-2018 by Max Maischein \f(CW\*(C`corion@cpan.org\*(C'\fR.
.SH "LICENSE"
.IX Header "LICENSE"
This module is released under the same terms as Perl itself.