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->format_datetime(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 array
169 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 a specific SQL::Abstract::Limit-style limit dialect,
195 overriding the default "sql_limit_dialect" setting of the storage
196 (if any). For a list of available limit dialects see
197 DBIx::Class::SQLMaker::LimitDialects.
198 quote_names
200 When true automatically sets "quote_char" and "name_sep" to the
201 characters appropriate for your particular RDBMS. This option is
202 preferred over specifying "quote_char" directly.
203 quote_char
205 Specifies what characters to use to quote table and column names.
206 "quote_char" expects either a single character, in which case is it
208 is placed on either side of the table/column name, or an arrayref
209 of length 2 in which case the table/column name is placed between
210 the elements.
211 For example under MySQL you should use "quote_char => '`'", and for
213 SQL Server you should use "quote_char => [qw/[ ]/]".
214 name_sep
216 This parameter is only useful in conjunction with "quote_char", and
217 is used to specify the character that separates elements (schemas,
218 tables, columns) from each other. If unspecified it defaults to the
219 most commonly used ".".
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{"} },
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
367 "dbh_do" instead.
368 connect_call_datetime_setup
370 A no-op stub method, provided so that one can always safely supply the
371 connection option
372 on_connect_call => 'datetime_setup'
374 This way one does not need to know in advance whether the underlying
376 storage requires any sort of hand-holding when dealing with calendar
377 data.
378 connect_call_rebase_sqlmaker
380 This on-connect call takes as a single argument the name of a class to
381 "rebase" the SQLMaker inheritance hierarchy upon. For this to work
382 properly the target class MUST inherit from
383 DBIx::Class::SQLMaker::ClassicExtensions and SQL::Abstract::Classic as
384 shown below.
385 This infrastructure is provided to aid recent activity around
387 experimental new aproaches to SQL generation within DBIx::Class. You
388 can (and are encouraged to) mix and match old and new within the same
389 codebase as follows:
390 package DBIx::Class::Awesomer::SQLMaker;
392 # you MUST inherit in this order to get the composition right
393 # you are free to override-without-next::method any part you need
394 use base qw(
395 DBIx::Class::SQLMaker::ClassicExtensions
396 << OPTIONAL::AWESOME::Class::Implementing::ExtraRainbowSauce >>
397 SQL::Abstract::Classic
398 );
399 << your new code goes here >>
400 ... and then ...
403 my $experimental_schema = $original_schema->connect(
406 sub {
407 $original_schema->storage->dbh
408 },
409 {
410 # the nested arrayref is important, as per
411 # https://metacpan.org/pod/DBIx::Class::Storage::DBI#on_connect_call
412 on_connect_call => [ [ rebase_sqlmaker => 'DBIx::Class::Awesomer::SQLMaker' ] ],
413 },
414 );
415 select
417 Arguments: $ident, $select, $condition, $attrs
418 Handle a SQL select statement.
420 sql_limit_dialect
422 This is an accessor for the default SQL limit dialect used by a
423 particular storage driver. Can be overridden by supplying an explicit
424 "limit_dialect" to "connect" in DBIx::Class::Schema. For a list of
425 available limit dialects see DBIx::Class::SQLMaker::LimitDialects.
426 last_insert_id
428 Return the row id of the last insert.
429 _native_data_type
431 Arguments: $type_name
432 This API is EXPERIMENTAL, will almost definitely change in the future,
434 and currently only used by ::AutoCast and ::Sybase::ASE.
435 The default implementation returns "undef", implement in your Storage
437 driver if you need this functionality.
438 Should map types from other databases to the native RDBMS type, for
440 example "VARCHAR2" to "VARCHAR".
441 Types with modifiers should map to the underlying data type. For
443 example, "INTEGER AUTO_INCREMENT" should become "INTEGER".
444 Composite types should map to the container type, for example
446 "ENUM(foo,bar,baz)" becomes "ENUM".
447 sqlt_type
449 Returns the database driver name.
450 bind_attribute_by_data_type
452 Given a datatype from column info, returns a database specific bind
453 attribute for "$dbh->bind_param($val,$attribute)" or nothing if we will
454 let the database planner just handle it.
455 This method is always called after the driver has been determined and a
457 DBI connection has been established. Therefore you can refer to
458 "DBI::$constant" and/or "DBD::$driver::$constant" directly, without
459 worrying about loading the correct modules.
460 is_datatype_numeric
462 Given a datatype from column_info, returns a boolean value indicating
463 if the current RDBMS considers it a numeric value. This controls how
464 "set_column" in DBIx::Class::Row decides whether to mark the column as
465 dirty - when the datatype is deemed numeric a "!=" comparison will be
466 performed instead of the usual "eq".
467 create_ddl_dir
469 Arguments: $schema, \@databases, $version, $directory, $preversion,
470 \%sqlt_args
471 Creates a SQL file based on the Schema, for each of the specified
473 database engines in "\@databases" in the given directory. (note:
474 specify SQL::Translator names, not DBI driver names).
475 Given a previous version number, this will also create a file
477 containing the ALTER TABLE statements to transform the previous schema
478 into the current one. Note that these statements may contain "DROP
479 TABLE" or "DROP COLUMN" statements that can potentially destroy data.
480 The file names are created using the "ddl_filename" method below,
482 please override this method in your schema if you would like a
483 different file name format. For the ALTER file, the same format is
484 used, replacing $version in the name with "$preversion-$version".
485 See "METHODS" in SQL::Translator for a list of values for
487 "\%sqlt_args". The most common value for this would be "{
488 add_drop_table => 1 }" to have the SQL produced include a "DROP TABLE"
489 statement for each table created. For quoting purposes supply
490 "quote_identifiers".
491 If no arguments are passed, then the following default values are
493 assumed:
494 databases - ['MySQL', 'SQLite', 'PostgreSQL']
496 version - $schema->schema_version
497 directory - './'
498 preversion - <none>
499 By default, "\%sqlt_args" will have
501 { add_drop_table => 1, ignore_constraint_names => 1, ignore_index_names => 1 }
503 merged with the hash passed in. To disable any of those features, pass
505 in a hashref like the following
506 { ignore_constraint_names => 0, # ... other options }
508 WARNING: You are strongly advised to check all SQL files created,
510 before applying them.
511 deployment_statements
513 Arguments: $schema, $type, $version, $directory, $sqlt_args
514 Returns the statements used by "deploy" in DBIx::Class::Storage and
516 "deploy" in DBIx::Class::Schema.
517 The SQL::Translator (not DBI) database driver name can be explicitly
519 provided in $type, otherwise the result of "sqlt_type" is used as
520 default.
521 $directory is used to return statements from files in a previously
523 created "create_ddl_dir" directory and is optional. The filenames are
524 constructed from "ddl_filename" in DBIx::Class::Schema, the schema name
525 and the $version.
526 If no $directory is specified then the statements are constructed on
528 the fly using SQL::Translator and $version is ignored.
529 See "METHODS" in SQL::Translator for a list of values for $sqlt_args.
531 datetime_parser
533 Returns the datetime parser class
534 datetime_parser_type
536 Defines the datetime parser class - currently defaults to
537 DateTime::Format::MySQL
538 build_datetime_parser
540 See "datetime_parser"
541 is_replicating
543 A boolean that reports if a particular DBIx::Class::Storage::DBI is set
544 to replicate from a master database. Default is undef, which is the
545 result returned by databases that don't support replication.
546 lag_behind_master
548 Returns a number that represents a certain amount of lag behind a
549 master db when a given storage is replicating. The number is database
550 dependent, but starts at zero and increases with the amount of lag.
551 Default in undef
552 relname_to_table_alias
554 Arguments: $relname, $join_count
555 Return Value: $alias
556 DBIx::Class uses DBIx::Class::Relationship names as table aliases in
558 queries.
559 This hook is to allow specific DBIx::Class::Storage drivers to change
561 the way these aliases are named.
562 The default behavior is ""$relname_$join_count" if $join_count > 1",
564 otherwise "$relname".
565
567 DBIx::Class and AutoCommit
568 DBIx::Class can do some wonderful magic with handling exceptions,
569 disconnections, and transactions when you use "AutoCommit => 1" (the
570 default) combined with txn_do for transaction support.
571 If you set "AutoCommit => 0" in your connect info, then you are always
573 in an assumed transaction between commits, and you're telling us you'd
574 like to manage that manually. A lot of the magic protections offered
575 by this module will go away. We can't protect you from exceptions due
576 to database disconnects because we don't know anything about how to
577 restart your transactions. You're on your own for handling all sorts
578 of exceptional cases if you choose the "AutoCommit => 0" path, just as
579 you would be with raw DBI.
580
582 Check the list of additional DBIC resources.
583
585 This module is free software copyright by the DBIx::Class (DBIC)
586 authors. You can redistribute it and/or modify it under the same terms
587 as the DBIx::Class library.
588perl v5.36.0 2023-01-20 DBIx::Class::Storage::DBI(3)