1DBIx::Class::Storage::DUBsIe(r3)Contributed Perl DocumenDtBaItxi:o:nClass::Storage::DBI(3)
2
6 DBIx::Class::Storage::DBI - DBI storage handler
7
9 my $schema = MySchema->connect('dbi:SQLite:my.db');
10 $schema->storage->debug(1);
12 my @stuff = $schema->storage->dbh_do(
14 sub {
15 my ($storage, $dbh, @args) = @_;
16 $dbh->do("DROP TABLE authors");
17 },
18 @column_list
19 );
20 $schema->resultset('Book')->search({
22 written_on => $schema->storage->datetime_parser(DateTime->now)
23 });
24
26 This class represents the connection to an RDBMS via DBI. See
27 DBIx::Class::Storage for general information. This pod only documents
28 DBI-specific methods and behaviors.
29
31 connect_info
32 This method is normally called by "connection" in DBIx::Class::Schema,
33 which encapsulates its argument list in an arrayref before passing them
34 here.
35 The argument list may contain:
37 · The same 4-element argument set one would normally pass to
39 "connect" in DBI, optionally followed by extra attributes
40 recognized by DBIx::Class:
41 $connect_info_args = [ $dsn, $user, $password, \%dbi_attributes?, \%extra_attributes? ];
43 · A single code reference which returns a connected DBI database
45 handle optionally followed by extra attributes recognized by
46 DBIx::Class:
47 $connect_info_args = [ sub { DBI->connect (...) }, \%extra_attributes? ];
49 · A single hashref with all the attributes and the dsn/user/password
51 mixed together:
52 $connect_info_args = [{
54 dsn => $dsn,
55 user => $user,
56 password => $pass,
57 %dbi_attributes,
58 %extra_attributes,
59 }];
60 $connect_info_args = [{
62 dbh_maker => sub { DBI->connect (...) },
63 %dbi_attributes,
64 %extra_attributes,
65 }];
66 This is particularly useful for Catalyst based applications,
68 allowing the following config (Config::General style):
69 <Model::DB>
71 schema_class App::DB
72 <connect_info>
73 dsn dbi:mysql:database=test
74 user testuser
75 password TestPass
76 AutoCommit 1
77 </connect_info>
78 </Model::DB>
79 The "dsn"/"user"/"password" combination can be substituted by the
81 "dbh_maker" key whose value is a coderef that returns a connected
82 DBI database handle
83 Please note that the DBI docs recommend that you always explicitly set
85 "AutoCommit" to either 0 or 1. DBIx::Class further recommends that it
86 be set to 1, and that you perform transactions via our "txn_do" in
87 DBIx::Class::Schema method. DBIx::Class will set it to 1 if you do not
88 do explicitly set it to zero. This is the default for most DBDs. See
89 "DBIx::Class and AutoCommit" for details.
90 DBIx::Class specific connection attributes
92 In addition to the standard DBI connection attributes, DBIx::Class
94 recognizes the following connection options. These options can be mixed
95 in with your other DBI connection attributes, or placed in a separate
96 hashref ("\%extra_attributes") as shown above.
97 Every time "connect_info" is invoked, any previous settings for these
99 options will be cleared before setting the new ones, regardless of
100 whether any options are specified in the new "connect_info".
101 on_connect_do
103 Specifies things to do immediately after connecting or re-
104 connecting to the database. Its value may contain:
105 a scalar
107 This contains one SQL statement to execute.
108 an array reference
110 This contains SQL statements to execute in order. Each element
111 contains a string or a code reference that returns a string.
112 a code reference
114 This contains some code to execute. Unlike code references
115 within an array reference, its return value is ignored.
116 on_disconnect_do
118 Takes arguments in the same form as "on_connect_do" and executes
119 them immediately before disconnecting from the database.
120 Note, this only runs if you explicitly call "disconnect" on the
122 storage object.
123 on_connect_call
125 A more generalized form of "on_connect_do" that calls the specified
126 "connect_call_METHOD" methods in your storage driver.
127 on_connect_do => 'select 1'
129 is equivalent to:
131 on_connect_call => [ [ do_sql => 'select 1' ] ]
133 Its values may contain:
135 a scalar
137 Will call the "connect_call_METHOD" method.
138 a code reference
140 Will execute "$code->($storage)"
141 an array reference
143 Each value can be a method name or code reference.
144 an array of arrays
146 For each array, the first item is taken to be the
147 "connect_call_" method name or code reference, and the rest are
148 parameters to it.
149 Some predefined storage methods you may use:
151 do_sql
153 Executes a SQL string or a code reference that returns a SQL
154 string. This is what "on_connect_do" and "on_disconnect_do"
155 use.
156 It can take:
158 a scalar
160 Will execute the scalar as SQL.
161 an arrayref
163 Taken to be arguments to "do" in DBI, the SQL string
164 optionally followed by the attributes hashref and bind
165 values.
166 a code reference
168 Will execute "$code->($storage)" and execute the return
169 array refs as above.
170 datetime_setup
172 Execute any statements necessary to initialize the database
173 session to return and accept datetime/timestamp values used
174 with DBIx::Class::InflateColumn::DateTime.
175 Only necessary for some databases, see your specific storage
177 driver for implementation details.
178 on_disconnect_call
180 Takes arguments in the same form as "on_connect_call" and executes
181 them immediately before disconnecting from the database.
182 Calls the "disconnect_call_METHOD" methods as opposed to the
184 "connect_call_METHOD" methods called by "on_connect_call".
185 Note, this only runs if you explicitly call "disconnect" on the
187 storage object.
188 disable_sth_caching
190 If set to a true value, this option will disable the caching of
191 statement handles via "prepare_cached" in DBI.
192 limit_dialect
194 Sets the limit dialect. This is useful for JDBC-bridge among others
195 where the remote SQL-dialect cannot be determined by the name of
196 the driver alone. See also SQL::Abstract::Limit.
197 quote_char
199 Specifies what characters to use to quote table and column names.
200 If you use this you will want to specify "name_sep" as well.
201 "quote_char" expects either a single character, in which case is it
203 is placed on either side of the table/column name, or an arrayref
204 of length 2 in which case the table/column name is placed between
205 the elements.
206 For example under MySQL you should use "quote_char => '`'", and for
208 SQL Server you should use "quote_char => [qw/[ ]/]".
209 name_sep
211 This only needs to be used in conjunction with "quote_char", and is
212 used to specify the character that separates elements (schemas,
213 tables, columns) from each other. In most cases this is simply a
214 ".".
215 The consequences of not supplying this value is that SQL::Abstract
217 will assume DBIx::Class' uses of aliases to be complete column
218 names. The output will look like "me.name" when it should actually
219 be "me"."name".
220 unsafe
222 This Storage driver normally installs its own "HandleError", sets
223 "RaiseError" and "ShowErrorStatement" on, and sets "PrintError" off
224 on all database handles, including those supplied by a coderef. It
225 does this so that it can have consistent and useful error behavior.
226 If you set this option to a true value, Storage will not do its
228 usual modifications to the database handle's attributes, and
229 instead relies on the settings in your connect_info DBI options (or
230 the values you set in your connection coderef, in the case that you
231 are connecting via coderef).
232 Note that your custom settings can cause Storage to malfunction,
234 especially if you set a "HandleError" handler that suppresses
235 exceptions and/or disable "RaiseError".
236 auto_savepoint
238 If this option is true, DBIx::Class will use savepoints when
239 nesting transactions, making it possible to recover from failure in
240 the inner transaction without having to abort all outer
241 transactions.
242 cursor_class
244 Use this argument to supply a cursor class other than the default
245 DBIx::Class::Storage::DBI::Cursor.
246 Some real-life examples of arguments to "connect_info" and "connect" in
248 DBIx::Class::Schema
249 # Simple SQLite connection
251 ->connect_info([ 'dbi:SQLite:./foo.db' ]);
252 # Connect via subref
254 ->connect_info([ sub { DBI->connect(...) } ]);
255 # Connect via subref in hashref
257 ->connect_info([{
258 dbh_maker => sub { DBI->connect(...) },
259 on_connect_do => 'alter session ...',
260 }]);
261 # A bit more complicated
263 ->connect_info(
264 [
265 'dbi:Pg:dbname=foo',
266 'postgres',
267 'my_pg_password',
268 { AutoCommit => 1 },
269 { quote_char => q{"}, name_sep => q{.} },
270 ]
271 );
272 # Equivalent to the previous example
274 ->connect_info(
275 [
276 'dbi:Pg:dbname=foo',
277 'postgres',
278 'my_pg_password',
279 { AutoCommit => 1, quote_char => q{"}, name_sep => q{.} },
280 ]
281 );
282 # Same, but with hashref as argument
284 # See parse_connect_info for explanation
285 ->connect_info(
286 [{
287 dsn => 'dbi:Pg:dbname=foo',
288 user => 'postgres',
289 password => 'my_pg_password',
290 AutoCommit => 1,
291 quote_char => q{"},
292 name_sep => q{.},
293 }]
294 );
295 # Subref + DBIx::Class-specific connection options
297 ->connect_info(
298 [
299 sub { DBI->connect(...) },
300 {
301 quote_char => q{`},
302 name_sep => q{@},
303 on_connect_do => ['SET search_path TO myschema,otherschema,public'],
304 disable_sth_caching => 1,
305 },
306 ]
307 );
308 on_connect_do
310 This method is deprecated in favour of setting via "connect_info".
311 on_disconnect_do
313 This method is deprecated in favour of setting via "connect_info".
314 dbh_do
316 Arguments: ($subref | $method_name), @extra_coderef_args?
317 Execute the given $subref or $method_name using the new exception-based
319 connection management.
320 The first two arguments will be the storage object that "dbh_do" was
322 called on and a database handle to use. Any additional arguments will
323 be passed verbatim to the called subref as arguments 2 and onwards.
324 Using this (instead of $self->_dbh or $self->dbh) ensures correct
326 exception handling and reconnection (or failover in future subclasses).
327 Your subref should have no side-effects outside of the database, as
329 there is the potential for your subref to be partially double-executed
330 if the database connection was stale/dysfunctional.
331 Example:
333 my @stuff = $schema->storage->dbh_do(
335 sub {
336 my ($storage, $dbh, @cols) = @_;
337 my $cols = join(q{, }, @cols);
338 $dbh->selectrow_array("SELECT $cols FROM foo");
339 },
340 @column_list
341 );
342 disconnect
344 Our "disconnect" method also performs a rollback first if the database
345 is not in "AutoCommit" mode.
346 with_deferred_fk_checks
348 Arguments: $coderef
349 Return Value: The return value of $coderef
350 Storage specific method to run the code ref with FK checks deferred or
352 in MySQL's case disabled entirely.
353 connected
355 Arguments: none
356 Return Value: 1|0
357 Verifies that the current database handle is active and ready to
359 execute an SQL statement (e.g. the connection did not get stale, server
360 is still answering, etc.) This method is used internally by "dbh".
361 dbh
363 Returns a $dbh - a data base handle of class DBI. The returned handle
364 is guaranteed to be healthy by implicitly calling "connected", and if
365 necessary performing a reconnection before returning. Keep in mind that
366 this is very expensive on some database engines. Consider using dbh_do
367 instead.
368 select
370 Arguments: $ident, $select, $condition, $attrs
371 Handle a SQL select statement.
373 sth
375 Arguments: $sql
376 Returns a DBI sth (statement handle) for the supplied SQL.
378 last_insert_id
380 Return the row id of the last insert.
381 _native_data_type
383 Arguments: $type_name
384 This API is EXPERIMENTAL, will almost definitely change in the future,
386 and currently only used by ::AutoCast and ::Sybase::ASE.
387 The default implementation returns "undef", implement in your Storage
389 driver if you need this functionality.
390 Should map types from other databases to the native RDBMS type, for
392 example "VARCHAR2" to "VARCHAR".
393 Types with modifiers should map to the underlying data type. For
395 example, "INTEGER AUTO_INCREMENT" should become "INTEGER".
396 Composite types should map to the container type, for example
398 "ENUM(foo,bar,baz)" becomes "ENUM".
399 sqlt_type
401 Returns the database driver name.
402 bind_attribute_by_data_type
404 Given a datatype from column info, returns a database specific bind
405 attribute for "$dbh->bind_param($val,$attribute)" or nothing if we will
406 let the database planner just handle it.
407 Generally only needed for special case column types, like bytea in
409 postgres.
410 is_datatype_numeric
412 Given a datatype from column_info, returns a boolean value indicating
413 if the current RDBMS considers it a numeric value. This controls how
414 "set_column" in DBIx::Class::Row decides whether to mark the column as
415 dirty - when the datatype is deemed numeric a "!=" comparison will be
416 performed instead of the usual "eq".
417 create_ddl_dir
419 Arguments: $schema \@databases, $version, $directory, $preversion,
420 \%sqlt_args
421 Creates a SQL file based on the Schema, for each of the specified
423 database engines in "\@databases" in the given directory. (note:
424 specify SQL::Translator names, not DBI driver names).
425 Given a previous version number, this will also create a file
427 containing the ALTER TABLE statements to transform the previous schema
428 into the current one. Note that these statements may contain "DROP
429 TABLE" or "DROP COLUMN" statements that can potentially destroy data.
430 The file names are created using the "ddl_filename" method below,
432 please override this method in your schema if you would like a
433 different file name format. For the ALTER file, the same format is
434 used, replacing $version in the name with "$preversion-$version".
435 See "METHODS" in SQL::Translator for a list of values for
437 "\%sqlt_args". The most common value for this would be "{
438 add_drop_table => 1 }" to have the SQL produced include a "DROP TABLE"
439 statement for each table created. For quoting purposes supply
440 "quote_table_names" and "quote_field_names".
441 If no arguments are passed, then the following default values are
443 assumed:
444 databases - ['MySQL', 'SQLite', 'PostgreSQL']
446 version - $schema->schema_version
447 directory - './'
448 preversion - <none>
449 By default, "\%sqlt_args" will have
451 { add_drop_table => 1, ignore_constraint_names => 1, ignore_index_names => 1 }
453 merged with the hash passed in. To disable any of those features, pass
455 in a hashref like the following
456 { ignore_constraint_names => 0, # ... other options }
458 WARNING: You are strongly advised to check all SQL files created,
460 before applying them.
461 deployment_statements
463 Arguments: $schema, $type, $version, $directory, $sqlt_args
464 Returns the statements used by "deploy" and "deploy" in
466 DBIx::Class::Schema.
467 The SQL::Translator (not DBI) database driver name can be explicitly
469 provided in $type, otherwise the result of "sqlt_type" is used as
470 default.
471 $directory is used to return statements from files in a previously
473 created "create_ddl_dir" directory and is optional. The filenames are
474 constructed from "ddl_filename" in DBIx::Class::Schema, the schema name
475 and the $version.
476 If no $directory is specified then the statements are constructed on
478 the fly using SQL::Translator and $version is ignored.
479 See "METHODS" in SQL::Translator for a list of values for $sqlt_args.
481 datetime_parser
483 Returns the datetime parser class
484 datetime_parser_type
486 Defines (returns) the datetime parser class - currently hardwired to
487 DateTime::Format::MySQL
488 build_datetime_parser
490 See "datetime_parser"
491 is_replicating
493 A boolean that reports if a particular DBIx::Class::Storage::DBI is set
494 to replicate from a master database. Default is undef, which is the
495 result returned by databases that don't support replication.
496 lag_behind_master
498 Returns a number that represents a certain amount of lag behind a
499 master db when a given storage is replicating. The number is database
500 dependent, but starts at zero and increases with the amount of lag.
501 Default in undef
502 relname_to_table_alias
504 Arguments: $relname, $join_count
505 DBIx::Class uses DBIx::Class::Relationship names as table aliases in
507 queries.
508 This hook is to allow specific DBIx::Class::Storage drivers to change
510 the way these aliases are named.
511 The default behavior is ""$relname_$join_count" if $join_count " 1>,
513 otherwise "$relname".
514
516 DBIx::Class and AutoCommit
517 DBIx::Class can do some wonderful magic with handling exceptions,
518 disconnections, and transactions when you use "AutoCommit => 1" (the
519 default) combined with "txn_do" for transaction support.
520 If you set "AutoCommit => 0" in your connect info, then you are always
522 in an assumed transaction between commits, and you're telling us you'd
523 like to manage that manually. A lot of the magic protections offered
524 by this module will go away. We can't protect you from exceptions due
525 to database disconnects because we don't know anything about how to
526 restart your transactions. You're on your own for handling all sorts
527 of exceptional cases if you choose the "AutoCommit => 0" path, just as
528 you would be with raw DBI.
529
531 Matt S. Trout <mst@shadowcatsystems.co.uk>
532 Andy Grundman <andy@hybridized.org>
534
536 You may distribute this code under the same terms as Perl itself.
537perl v5.12.0 2010-05-12 DBIx::Class::Storage::DBI(3)