1DBIx::RunSQL(3) User Contributed Perl Documentation DBIx::RunSQL(3)
2
6 DBIx::RunSQL - run SQL from a file
7
9 #!/usr/bin/perl -w
10 use strict;
11 use DBIx::RunSQL;
12 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 # now run your tests with a DB setup fresh from setup.sql
21
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 · "sql" - name of the file containing the SQL statements
29 The default is "sql/create.sql"
31 If "sql" is a reference to a glob or a filehandle, the SQL will be
33 read from that. not implemented
34 If "sql" is undefined, the $::DATA or the 0 filehandle will be read
36 until exhaustion. not implemented
37 This allows one to create SQL-as-programs as follows:
39 #!/usr/bin/perl -w -MDBIx::RunSQL -e 'create()'
41 create table ...
42 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 sql => \"update mytable set foo='bar';",
47 · "dsn", "user", "password" - DBI parameters for connecting to the DB
49 · "dbh" - a premade database handle to be used instead of "dsn"
51 · "force" - continue even if errors are encountered
53 · "verbose" - print each SQL statement as it is run
55 · "verbose_handler" - callback to call with each SQL statement
57 instead of "print"
58 · "verbose_fh" - filehandle to write to instead of "STDOUT"
60 "DBIx::RunSQL->run_sql_file ARGS"
62 my $dbh = DBI->connect(...)
63 for my $file (sort glob '*.sql') {
65 DBIx::RunSQL->run_sql_file(
66 verbose => 1,
67 dbh => $dbh,
68 sql => $file,
69 );
70 };
71 Runs an SQL file on a prepared database handle. Returns the number of
73 errors encountered.
74 If the statement returns rows, these are printed separated with tabs.
76 · "dbh" - a premade database handle
78 · "sql" - name of the file containing the SQL statements
80 · "fh" - filehandle to the file containing the SQL statements
82 · "force" - continue even if errors are encountered
84 · "verbose" - print each SQL statement as it is run
86 · "verbose_handler" - callback to call with each SQL statement
88 instead of "print"
89 · "verbose_fh" - filehandle to write to instead of "STDOUT"
91 · "output_bool" - whether to exit with a nonzero exit code if any row
93 is found
94 This makes the function return a nonzero value even if there is no
96 error but a row was found.
97 · "output_string" - whether to output the (one) row and column,
99 without any headers
100 "DBIx::RunSQL->run_sql ARGS"
102 my $dbh = DBI->connect(...)
103 DBIx::RunSQL->run_sql(
105 verbose => 1,
106 dbh => $dbh,
107 sql => \@sql_statements,
108 );
109 Runs an SQL string on a prepared database handle. Returns the number
111 of errors encountered.
112 If the statement returns rows, these are printed separated with tabs,
114 but see the "output_bool" and "output_string" options.
115 · "dbh" - a premade database handle
117 · "sql" - string or array reference containing the SQL statements
119 · "force" - continue even if errors are encountered
121 · "verbose" - print each SQL statement as it is run
123 · "verbose_handler" - callback to call with each SQL statement
125 instead of "print"
126 · "verbose_fh" - filehandle to write to instead of "STDOUT"
128 · "output_bool" - whether to exit with a nonzero exit code if any row
130 is found
131 This makes the function return a nonzero value even if there is no
133 error but a row was found.
134 · "output_string" - whether to output the (one) row and column,
136 without any headers
137 "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 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 If you find yourself using this often to create reports, you may really
148 want to look at Querylet instead.
149 · "sth" - the executed statement handle
151 · "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 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 "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 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 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 "DBIx::RunSQL->parse_command_line"
182 my $options = DBIx::RunSQL->parse_command_line( 'my_application', \@ARGV );
183 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 If the reference to the array of command line items is missing, @ARGV
189 will be modified instead.
190 "DBIx::RunSQL->handle_command_line"
192 DBIx::RunSQL->handle_command_line( 'my_application', \@ARGV );
193 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 --user
200 --password
201 --dsn
202 --sql
203 --quiet
204 --format
205 --force
206 --verbose
207 --bool
208 --string
209 In addition, it handles the following switches through Pod::Usage:
211 --help
213 --man
214 If no SQL is given, this function will read the SQL from STDIN.
216 If no dsn is given, this function will use "
218 dbi:SQLite:dbname=db/$appname.sqlite " as the default database.
219 See also the section PROGRAMMER USAGE for a sample program to set up a
221 database from an SQL file.
222
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 #!/usr/bin/perl -w
233 use strict;
234 use DBIx::RunSQL;
235 my $exitcode = DBIx::RunSQL->handle_command_line('myapp', \@ARGV);
237 exit $exitcode;
238 =head1 NAME
240 create-db.pl - Create the database
242 =head1 SYNOPSIS
244 create-db.pl "select * from mytable where 1=0"
246 =head1 ABSTRACT
248 This sets up the database. The following
250 options are recognized:
251 =head1 OPTIONS
253 =over 4
255 =item C<--user> USERNAME
257 =item C<--password> PASSWORD
259 =item C<--dsn> DSN
261 The DBI DSN to use for connecting to
263 the database
264 =item C<--sql> SQLFILE
266 The alternative SQL file to use
268 instead of C<sql/create.sql>.
269 =item C<--quiet>
271 Output no headers for empty SELECT resultsets
273 =item C<--bool>
275 Set the exit code to 1 if at least one result row was found
277 =item C<--string>
279 Output the (single) column that the query returns as a string without
281 any headers
282 =item C<--format> formatter
284 Use a different formatter for table output. Supported formatters are
286 tab - output results as tab delimited columns
288 Text::Table - output results as ASCII table
290 =item C<--force>
292 Don't stop on errors
294 =item C<--help>
296 Show this message.
298 =back
300 =cut
302
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 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 CREATE TRIGGER
317 ...
318 END;
319 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 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 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
333 ORLite::Migrate
334
336 The public repository of this module is
337 <http://github.com/Corion/DBIx--RunSQL>.
338
340 The public support forum of this module is <http://perlmonks.org/>.
341
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
348 Max Maischein "corion@cpan.org"
349
351 Copyright 2009-2018 by Max Maischein "corion@cpan.org".
352
354 This module is released under the same terms as Perl itself.
355perl v5.32.0 2020-07-28 DBIx::RunSQL(3)