DBIx::RunSQL(3pm)

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               formatter => 'Text::Table',
19           );
20
21           # now run your tests with a DB setup fresh from setup.sql
22

METHODS

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

PROGRAMMER USAGE

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

NOTES

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

REPOSITORY

345       The public repository of this module is
346       <https://github.com/Corion/DBIx--RunSQL>.
347

SUPPORT

349       The public support forum of this module is <https://perlmonks.org/>.
350

BUG TRACKER

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

AUTHOR

357       Max Maischein "corion@cpan.org"
358

COPYRIGHT (c)

360       Copyright 2009-2021 by Max Maischein "corion@cpan.org".
361

LICENSE

363       This module is released under the same terms as Perl itself.
364
365
366
367perl v5.36.0                      2022-07-22                   DBIx::RunSQL(3)