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               rotate    => 1,
20               null      => '(Null)',
21           );
22
23           # now run your tests with a DB setup fresh from setup.sql
24

METHODS

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

PROGRAMMER USAGE

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

NOTES

329   COMMENT FILTERING
330       The module tries to keep the SQL as much verbatim as possible. It
331       filters all lines that end in semicolons but contain only SQL comments.
332       All other comments are passed through to the database with the next
333       statement.
334
335   TRIGGER HANDLING
336       This module uses a very simplicistic approach to recognize triggers.
337       Triggers are problematic because they consist of multiple SQL
338       statements and this module does not implement a full SQL parser. An
339       trigger is recognized by the following sequence of lines
340
341           CREATE TRIGGER
342               ...
343           END;
344
345       If your SQL dialect uses a different syntax, it might still work to put
346       the whole trigger on a single line in the input file.
347
348   OTHER APPROACHES
349       If you find yourself wanting to write SELECT statements, consider
350       looking at Querylet instead, which is geared towards that and even has
351       an interface for Excel or HTML output.
352
353       If you find yourself wanting to write parametrized queries as ".sql"
354       files, consider looking at Data::Phrasebook::SQL or potentially
355       DBIx::SQLHandler.
356

REPOSITORY

364       The public repository of this module is
365       <https://github.com/Corion/DBIx--RunSQL>.
366

SUPPORT

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

BUG TRACKER

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

AUTHOR

376       Max Maischein "corion@cpan.org"
377

COPYRIGHT (c)

379       Copyright 2009-2021 by Max Maischein "corion@cpan.org".
380

LICENSE

382       This module is released under the same terms as Perl itself.
383
384
385
386perl v5.36.1                      2023-06-14                   DBIx::RunSQL(3)