1DBIx::RunSQL(3)       User Contributed Perl Documentation      DBIx::RunSQL(3)
2
3
4

NAME

6       DBIx::RunSQL - run SQL from a file
7

SYNOPSIS

9           #!/usr/bin/perl -w
10           use strict;
11           use DBIx::RunSQL;
12
13           my $test_dbh = DBIx::RunSQL->create(
14               dsn     => 'dbi:SQLite:dbname=:memory:',
15               sql     => 'sql/create.sql',
16               force   => 1,
17               verbose => 1,
18           );
19
20           # now run your tests with a DB setup fresh from setup.sql
21

METHODS

23   "DBIx::RunSQL->create ARGS"
24   "DBIx::RunSQL->run ARGS"
25       Runs the SQL commands and returns the database handle.  In list
26       context, it returns the database handle and the suggested exit code.
27
28       •   "sql" - name of the file containing the SQL statements
29
30           The default is "sql/create.sql"
31
32           If "sql" is a reference to a glob or a filehandle, the SQL will be
33           read from that. not implemented
34
35           If "sql" is undefined, the $::DATA or the 0 filehandle will be read
36           until exhaustion.  not implemented
37
38           This allows one to create SQL-as-programs as follows:
39
40             #!/usr/bin/perl -w -MDBIx::RunSQL -e 'create()'
41             create table ...
42
43           If you want to run SQL statements from a scalar, you can simply
44           pass in a reference to a scalar containing the SQL:
45
46               sql => \"update mytable set foo='bar';",
47
48       •   "dsn", "user", "password" - DBI parameters for connecting to the DB
49
50       •   "dbh" - a premade database handle to be used instead of "dsn"
51
52       •   "force" - continue even if errors are encountered
53
54       •   "verbose" - print each SQL statement as it is run
55
56       •   "verbose_handler" - callback to call with each SQL statement
57           instead of "print"
58
59       •   "verbose_fh" - filehandle to write to instead of "STDOUT"
60
61   "DBIx::RunSQL->run_sql_file ARGS"
62           my $dbh = DBI->connect(...)
63
64           for my $file (sort glob '*.sql') {
65               DBIx::RunSQL->run_sql_file(
66                   verbose => 1,
67                   dbh     => $dbh,
68                   sql     => $file,
69               );
70           };
71
72       Runs an SQL file on a prepared database handle.  Returns the number of
73       errors encountered.
74
75       If the statement returns rows, these are printed separated with tabs.
76
77       •   "dbh" - a premade database handle
78
79       •   "sql" - name of the file containing the SQL statements
80
81       •   "fh" - filehandle to the file containing the SQL statements
82
83       •   "force" - continue even if errors are encountered
84
85       •   "verbose" - print each SQL statement as it is run
86
87       •   "verbose_handler" - callback to call with each SQL statement
88           instead of "print"
89
90       •   "verbose_fh" - filehandle to write to instead of "STDOUT"
91
92       •   "output_bool" - whether to exit with a nonzero exit code if any row
93           is found
94
95           This makes the function return a nonzero value even if there is no
96           error but a row was found.
97
98       •   "output_string" - whether to output the (one) row and column,
99           without any headers
100
101   "DBIx::RunSQL->run_sql ARGS"
102           my $dbh = DBI->connect(...)
103
104           DBIx::RunSQL->run_sql(
105               verbose => 1,
106               dbh     => $dbh,
107               sql     => \@sql_statements,
108           );
109
110       Runs an SQL string on a prepared database handle.  Returns the number
111       of errors encountered.
112
113       If the statement returns rows, these are printed separated with tabs,
114       but see the "output_bool" and "output_string" options.
115
116       •   "dbh" - a premade database handle
117
118       •   "sql" - string or array reference containing the SQL statements
119
120       •   "force" - continue even if errors are encountered
121
122       •   "verbose" - print each SQL statement as it is run
123
124       •   "verbose_handler" - callback to call with each SQL statement
125           instead of "print"
126
127       •   "verbose_fh" - filehandle to write to instead of "STDOUT"
128
129       •   "output_bool" - whether to exit with a nonzero exit code if any row
130           is found
131
132           This makes the function return a nonzero value even if there is no
133           error but a row was found.
134
135       •   "output_string" - whether to output the (one) row and column,
136           without any headers
137
138   "DBIx::RunSQL->format_results %options"
139         my $sth= $dbh->prepare( 'select * from foo' );
140         $sth->execute();
141         print DBIx::RunSQL->format_results( sth => $sth );
142
143       Executes "$sth->fetchall_arrayref" and returns the results either as
144       tab separated string or formatted using Text::Table if the module is
145       available.
146
147       If you find yourself using this often to create reports, you may really
148       want to look at Querylet instead.
149
150       •   "sth" - the executed statement handle
151
152       •   "formatter" - if you want to force "tab" or "Text::Table" usage,
153           you can do it through that parameter.  In fact, the module will use
154           anything other than "tab" as the class name and assume that the
155           interface is compatible to "Text::Table".
156
157       Note that the query results are returned as one large string, so you
158       really do not want to run this for large(r) result sets.
159
160   "DBIx::RunSQL->split_sql ARGS"
161         my @statements= DBIx::RunSQL->split_sql( <<'SQL');
162             create table foo (name varchar(64));
163             create trigger foo_insert on foo before insert;
164                 new.name= 'foo-'||old.name;
165             end;
166             insert into foo name values ('bar');
167         SQL
168         # Returns three elements
169
170       This is a helper subroutine to split a sequence of (semicolon-newline-
171       delimited) SQL statements into separate statements. It is documented
172       because it is not a very smart subroutine and you might want to
173       override or replace it. It might also be useful outside the context of
174       DBIx::RunSQL if you need to split up a large blob of SQL statements
175       into smaller pieces.
176
177       The subroutine needs the whole sequence of SQL statements in memory.
178       If you are attempting to restore a large SQL dump backup into your
179       database, this approach might not be suitable.
180
181   "DBIx::RunSQL->parse_command_line"
182           my $options = DBIx::RunSQL->parse_command_line( 'my_application', \@ARGV );
183
184       Helper function to turn a command line array into options for
185       DBIx::RunSQL invocations. The array of command line items is modified
186       in-place.
187
188       If the reference to the array of command line items is missing, @ARGV
189       will be modified instead.
190
191   "DBIx::RunSQL->handle_command_line"
192           DBIx::RunSQL->handle_command_line( 'my_application', \@ARGV );
193
194       Helper function to run the module functionality from the command line.
195       See below how to use this function in a good self-contained script.
196       This function passes the following command line arguments and options
197       to "->create":
198
199         --user
200         --password
201         --dsn
202         --sql
203         --quiet
204         --format
205         --force
206         --verbose
207         --bool
208         --string
209
210       In addition, it handles the following switches through Pod::Usage:
211
212         --help
213         --man
214
215       If no SQL is given, this function will read the SQL from STDIN.
216
217       If no dsn is given, this function will use "
218       dbi:SQLite:dbname=db/$appname.sqlite " as the default database.
219
220       See also the section PROGRAMMER USAGE for a sample program to set up a
221       database from an SQL file.
222

PROGRAMMER USAGE

224       This module abstracts away the "run these SQL statements to set up your
225       database" into a module. In some situations you want to give the setup
226       SQL to a database admin, but in other situations, for example testing,
227       you want to run the SQL statements against an in-memory database. This
228       module abstracts away the reading of SQL from a file and allows for
229       various command line parameters to be passed in. A skeleton
230       "create-db.sql" looks like this:
231
232           #!/usr/bin/perl -w
233           use strict;
234           use DBIx::RunSQL;
235
236           my $exitcode = DBIx::RunSQL->handle_command_line('myapp', \@ARGV);
237           exit $exitcode;
238
239           =head1 NAME
240
241           create-db.pl - Create the database
242
243           =head1 SYNOPSIS
244
245             create-db.pl "select * from mytable where 1=0"
246
247           =head1 ABSTRACT
248
249           This sets up the database. The following
250           options are recognized:
251
252           =head1 OPTIONS
253
254           =over 4
255
256           =item C<--user> USERNAME
257
258           =item C<--password> PASSWORD
259
260           =item C<--dsn> DSN
261
262           The DBI DSN to use for connecting to
263           the database
264
265           =item C<--sql> SQLFILE
266
267           The alternative SQL file to use
268           instead of C<sql/create.sql>.
269
270           =item C<--quiet>
271
272           Output no headers for empty SELECT resultsets
273
274           =item C<--bool>
275
276           Set the exit code to 1 if at least one result row was found
277
278           =item C<--string>
279
280           Output the (single) column that the query returns as a string without
281           any headers
282
283           =item C<--format> formatter
284
285           Use a different formatter for table output. Supported formatters are
286
287             tab - output results as tab delimited columns
288
289             Text::Table - output results as ASCII table
290
291           =item C<--force>
292
293           Don't stop on errors
294
295           =item C<--help>
296
297           Show this message.
298
299           =back
300
301           =cut
302

NOTES

304   COMMENT FILTERING
305       The module tries to keep the SQL as much verbatim as possible. It
306       filters all lines that end in semicolons but contain only SQL comments.
307       All other comments are passed through to the database with the next
308       statement.
309
310   TRIGGER HANDLING
311       This module uses a very simplicistic approach to recognize triggers.
312       Triggers are problematic because they consist of multiple SQL
313       statements and this module does not implement a full SQL parser. An
314       trigger is recognized by the following sequence of lines
315
316           CREATE TRIGGER
317               ...
318           END;
319
320       If your SQL dialect uses a different syntax, it might still work to put
321       the whole trigger on a single line in the input file.
322
323   OTHER APPROACHES
324       If you find yourself wanting to write SELECT statements, consider
325       looking at Querylet instead, which is geared towards that and even has
326       an interface for Excel or HTML output.
327
328       If you find yourself wanting to write parametrized queries as ".sql"
329       files, consider looking at Data::Phrasebook::SQL or potentially
330       DBIx::SQLHandler.
331

SEE ALSO

333       ORLite::Migrate
334

REPOSITORY

336       The public repository of this module is
337       <http://github.com/Corion/DBIx--RunSQL>.
338

SUPPORT

340       The public support forum of this module is <http://perlmonks.org/>.
341

BUG TRACKER

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

AUTHOR

348       Max Maischein "corion@cpan.org"
349

COPYRIGHT (c)

351       Copyright 2009-2018 by Max Maischein "corion@cpan.org".
352

LICENSE

354       This module is released under the same terms as Perl itself.
355
356
357
358perl v5.32.1                      2021-01-27                   DBIx::RunSQL(3)