NAME

DBIO::Storage::DBI - DBI storage handler

VERSION

version 0.900000

SYNOPSIS

my $schema = MySchema->connect('dbi:SQLite:my.db');

$schema->storage->debug(1);

my @stuff = $schema->storage->dbh_do(
  sub {
    my ($storage, $dbh, @args) = @_;
    $dbh->do("DROP TABLE authors");
  },
  @column_list
);

$schema->resultset('Book')->search({
   written_on => $schema->storage->datetime_parser->format_datetime(DateTime->now)
});

DESCRIPTION

This is DBIO's common storage backend for DBI-based databases. It owns the live database handle, transaction state, SQL maker integration, and the driver detection path that later reblesses into database-specific storage subclasses such as DBIO::PostgreSQL::Storage, DBIO::MySQL::Storage, or DBIO::SQLite::Storage.

See DBIO::Storage for the storage API shared across all backends. This POD focuses on the DBI-specific layer, including connection handling, capability checks, and the hooks that driver subclasses build on.

METHODS

connect_info

This method is normally called by "connection" in DBIO::Schema, which encapsulates its argument list in an arrayref before passing them here.

The argument list may contain:

The same 4-element argument set one would normally pass to "connect" in DBI, optionally followed by extra attributes recognized by DBIO:
```
$connect_info_args = [ $dsn, $user, $password, \%dbi_attributes?, \%extra_attributes? ];
```
A single code reference which returns a connected DBI database handle optionally followed by extra attributes recognized by DBIO:
```
$connect_info_args = [ sub { DBI->connect (...) }, \%extra_attributes? ];
```

A single hashref with all the attributes and the dsn/user/password mixed together:

$connect_info_args = [{
  dsn => $dsn,
  user => $user,
  password => $pass,
  %dbi_attributes,
  %extra_attributes,
}];

$connect_info_args = [{
  dbh_maker => sub { DBI->connect (...) },
  %dbi_attributes,
  %extra_attributes,
}];

This is particularly useful for Catalyst based applications, allowing the following config (Config::General style):

<Model::DB>
  schema_class   App::DB
  <connect_info>
    dsn          dbi:mysql:database=test
    user         testuser
    password     TestPass
    AutoCommit   1
  </connect_info>
</Model::DB>

The dsn/user/password combination can be substituted by the dbh_maker key whose value is a coderef that returns a connected DBI database handle

Please note that the DBI docs recommend that you always explicitly set AutoCommit to either 0 or 1. DBIO further recommends that it be set to 1, and that you perform transactions via our "txn_do" in DBIO::Schema method. DBIO will set it to 1 if you do not do explicitly set it to zero. This is the default for most DBDs. See "DBIO and AutoCommit" for details.

DBIO specific connection attributes

In addition to the standard DBI connection attributes, DBIO recognizes the following connection options. These options can be mixed in with your other DBI connection attributes, or placed in a separate hashref (\%extra_attributes) as shown above.

Every time connect_info is invoked, any previous settings for these options will be cleared before setting the new ones, regardless of whether any options are specified in the new connect_info.

on_connect_do

Specifies things to do immediately after connecting or re-connecting to the database. Its value may contain:

a scalar: This contains one SQL statement to execute.
an array reference: This contains SQL statements to execute in order. Each element contains a string or a code reference that returns a string.
a code reference: This contains some code to execute. Unlike code references within an array reference, its return value is ignored.

on_disconnect_do

Takes arguments in the same form as "on_connect_do" and executes them immediately before disconnecting from the database.

Note, this only runs if you explicitly call "disconnect" on the storage object.

on_connect_call

A more generalized form of "on_connect_do" that calls the specified connect_call_METHOD methods in your storage driver.

on_connect_do => 'select 1'

is equivalent to:

on_connect_call => [ [ do_sql => 'select 1' ] ]

Its values may contain:

a scalar: Will call the connect_call_METHOD method.
a code reference: Will execute $code->($storage)
an array reference: Each value can be a method name or code reference.
an array of arrays: For each array, the first item is taken to be the connect_call_ method name or code reference, and the rest are parameters to it.

Some predefined storage methods you may use:

do_sql

Executes a SQL string or a code reference that returns a SQL string. This is what "on_connect_do" and "on_disconnect_do" use.

It can take:

a scalar: Will execute the scalar as SQL.
an arrayref: Taken to be arguments to "do" in DBI, the SQL string optionally followed by the attributes hashref and bind values.
a code reference: Will execute $code->($storage) and execute the return array refs as above.

datetime_setup

Execute any statements necessary to initialize the database session to return and accept datetime/timestamp values used with DBIO::InflateColumn::DateTime.

Only necessary for some databases, see your specific storage driver for implementation details.

on_disconnect_call

Takes arguments in the same form as "on_connect_call" and executes them immediately before disconnecting from the database.

Calls the disconnect_call_METHOD methods as opposed to the connect_call_METHOD methods called by "on_connect_call".

Note, this only runs if you explicitly call "disconnect" on the storage object.

disable_sth_caching

If set to a true value, this option will disable the caching of statement handles via "prepare_cached" in DBI.

quote_names

When true automatically sets "quote_char" and "name_sep" to the characters appropriate for your particular RDBMS. This option is preferred over specifying "quote_char" directly.

quote_char

Specifies what characters to use to quote table and column names.

quote_char expects either a single character, in which case is it is placed on either side of the table/column name, or an arrayref of length 2 in which case the table/column name is placed between the elements.

For example under MySQL you should use quote_char => '`', and for SQL Server you should use quote_char => [qw/[ ]/].

name_sep

This parameter is only useful in conjunction with quote_char, and is used to specify the character that separates elements (schemas, tables, columns) from each other. If unspecified it defaults to the most commonly used ..

unsafe

This Storage driver normally installs its own HandleError, sets RaiseError and ShowErrorStatement on, and sets PrintError off on all database handles, including those supplied by a coderef. It does this so that it can have consistent and useful error behavior.

If you set this option to a true value, Storage will not do its usual modifications to the database handle's attributes, and instead relies on the settings in your connect_info DBI options (or the values you set in your connection coderef, in the case that you are connecting via coderef).

Note that your custom settings can cause Storage to malfunction, especially if you set a HandleError handler that suppresses exceptions and/or disable RaiseError.

auto_savepoint

If this option is true, DBIO will use savepoints when nesting transactions, making it possible to recover from failure in the inner transaction without having to abort all outer transactions.

cursor_class

Use this argument to supply a cursor class other than the default DBIO::Storage::DBI::Cursor.

Some real-life examples of arguments to "connect_info" and "connect" in DBIO::Schema

# Simple SQLite connection
->connect_info([ 'dbi:SQLite:./foo.db' ]);

# Connect via subref
->connect_info([ sub { DBI->connect(...) } ]);

# Connect via subref in hashref
->connect_info([{
  dbh_maker => sub { DBI->connect(...) },
  on_connect_do => 'alter session ...',
}]);

# A bit more complicated
->connect_info(
  [
    'dbi:Pg:dbname=foo',
    'postgres',
    'my_pg_password',
    { AutoCommit => 1 },
    { quote_char => q{"} },
  ]
);

# Equivalent to the previous example
->connect_info(
  [
    'dbi:Pg:dbname=foo',
    'postgres',
    'my_pg_password',
    { AutoCommit => 1, quote_char => q{"}, name_sep => q{.} },
  ]
);

# Same, but with hashref as argument
# See parse_connect_info for explanation
->connect_info(
  [{
    dsn         => 'dbi:Pg:dbname=foo',
    user        => 'postgres',
    password    => 'my_pg_password',
    AutoCommit  => 1,
    quote_char  => q{"},
    name_sep    => q{.},
  }]
);

# Subref + DBIO-specific connection options
->connect_info(
  [
    sub { DBI->connect(...) },
    {
        quote_char => q{`},
        name_sep => q{@},
        on_connect_do => ['SET search_path TO myschema,otherschema,public'],
        disable_sth_caching => 1,
    },
  ]
);

on_connect_do

This method is deprecated in favour of setting via "connect_info".

on_disconnect_do

This method is deprecated in favour of setting via "connect_info".

dbh_do

Arguments: ($subref | $method_name), @extra_coderef_args?

Execute the given $subref or $method_name using the new exception-based connection management.

The first two arguments will be the storage object that dbh_do was called on and a database handle to use. Any additional arguments will be passed verbatim to the called subref as arguments 2 and onwards.

Using this (instead of $self->_dbh or $self->dbh) ensures correct exception handling and reconnection (or failover in future subclasses).

Your subref should have no side-effects outside of the database, as there is the potential for your subref to be partially double-executed if the database connection was stale/dysfunctional.

Example:

my @stuff = $schema->storage->dbh_do(
  sub {
    my ($storage, $dbh, @cols) = @_;
    my $cols = join(q{, }, @cols);
    $dbh->selectrow_array("SELECT $cols FROM foo");
  },
  @column_list
);

disconnect

Our disconnect method also performs a rollback first if the database is not in AutoCommit mode.

with_deferred_fk_checks

Arguments: $coderef
Return Value: The return value of $coderef

Storage specific method to run the code ref with FK checks deferred or in MySQL's case disabled entirely.

connected

Arguments: none
Return Value: 1|0

Verifies that the current database handle is active and ready to execute an SQL statement (e.g. the connection did not get stale, server is still answering, etc.) This method is used internally by "dbh".

dbh

Returns a $dbh - a data base handle of class DBI. The returned handle is guaranteed to be healthy by implicitly calling "connected", and if necessary performing a reconnection before returning. Keep in mind that this is very expensive on some database engines. Consider using "dbh_do" instead.

register_driver

Arguments: $dbd_driver_name, $storage_class
Return value: none

Registers a mapping from a DBI driver name (for example Pg, mysql, SQLite) to a DBIO storage class.

This is primarily used by external DBIO driver distributions, so DBIO::Storage::DBI can rebless itself into the correct storage subclass during driver detection.

Example:

__PACKAGE__->register_driver('Pg' => 'DBIO::PostgreSQL::Storage');

register_driver

Registers a DBD driver-name to DBIO storage-class mapping.

register_connector_driver

Registers an ODBC/ADO SQL_DBMS_NAME to DBIO storage-class mapping.

last_insert_id

Returns autoincrement values for the columns requested by insert codepaths.

deploy_defaults

Returns a hash of default arguments merged into every $schema->deploy() call made by DBIO::Test. The base implementation returns an empty list; drivers override this to declare their requirements without hard-coding driver names in test infrastructure.

# DBIO::MySQL::Storage
sub deploy_defaults { return (add_drop_table => 1) }

deploy_setup

$storage->deploy_setup($schema);

Called by DBIO::Test immediately before $schema->deploy(). The default implementation is a no-op. Drivers override this to perform any one-time database-level setup that must happen before the schema is installed (for example, stripping incompatible sql_mode flags on MySQL).

register_connector_driver

Arguments: $sql_dbms_name, $storage_class
Return value: none

Registers a mapping from SQL_DBMS_NAME values (as reported by ODBC/ADO connectors) to a DBIO storage class. This is used by connector-based secondary driver detection in "_determine_connector_driver".

Example:

__PACKAGE__->register_connector_driver(
  'Microsoft_SQL_Server' => 'DBIO::MSSQL::Storage::Sybase',
);

connect_call_datetime_setup

A no-op stub method, provided so that one can always safely supply the connection option

on_connect_call => 'datetime_setup'

This way one does not need to know in advance whether the underlying storage requires any sort of hand-holding when dealing with calendar data.

connect_call_rebase_sqlmaker

This on-connect call takes as a single argument the name of a class to "rebase" the SQLMaker inheritance hierarchy upon. For this to work properly the target class MUST inherit from DBIO::SQLMaker::ClassicExtensions and either SQL::Abstract or SQL::Abstract::Classic as shown below.

This infrastructure is provided to aid recent activity around experimental new aproaches to SQL generation within DBIO. You can (and are encouraged to) mix and match old and new within the same codebase as follows:

package DBIO::Awesomer::SQLMaker;
# you MUST inherit in this order to get the composition right
# you are free to override-without-next::method any part you need
use base qw(
  DBIO::SQLMaker::ClassicExtensions
  << OPTIONAL::AWESOME::Class::Implementing::ExtraRainbowSauce >>
  SQL::Abstract
);
<< your new code goes here >>


... and then ...


my $experimental_schema = $original_schema->connect(
  sub {
    $original_schema->storage->dbh
  },
  {
    # the nested arrayref is important, as per
    # https://metacpan.org/pod/DBIO::Storage::DBI#on_connect_call
    on_connect_call => [ [ rebase_sqlmaker => 'DBIO::Awesomer::SQLMaker' ] ],
  },
);

select

Arguments: $ident, $select, $condition, $attrs

Handle a SQL select statement.

last_insert_id

Return the row id of the last insert.

sqlt_type

Returns the database driver name.

bind_attribute_by_data_type

Given a datatype from column info, returns a database specific bind attribute for $dbh->bind_param($val,$attribute) or nothing if we will let the database planner just handle it.

This method is always called after the driver has been determined and a DBI connection has been established. Therefore you can refer to DBI::$constant and/or DBD::$driver::$constant directly, without worrying about loading the correct modules.

is_datatype_numeric

Given a datatype from column_info, returns a boolean value indicating if the current RDBMS considers it a numeric value. This controls how "set_column" in DBIO::Row decides whether to mark the column as dirty - when the datatype is deemed numeric a != comparison will be performed instead of the usual eq.

create_ddl_dir

Arguments: $schema, \@databases, $version, $directory, $preversion, \%sqlt_args

DEPRECATED: This method is deprecated and will throw an exception if called. Use the native Deploy class on your storage instead.

Creates a SQL file based on the Schema, for each of the specified database engines in \@databases in the given directory. (note: specify database driver names, not DBI driver names).

Given a previous version number, this will also create a file containing the ALTER TABLE statements to transform the previous schema into the current one. Note that these statements may contain DROP TABLE or DROP COLUMN statements that can potentially destroy data.

The file names are created using the ddl_filename method below, please override this method in your schema if you would like a different file name format. For the ALTER file, the same format is used, replacing $version in the name with "$preversion-$version".

For quoting purposes supply quote_identifiers.

If no arguments are passed, then the following default values are assumed:

databases - ['MySQL', 'SQLite', 'PostgreSQL']
version - $schema->schema_version
directory - './'
preversion - <none>

By default, \%sqlt_args will have

{ add_drop_table => 1, ignore_constraint_names => 1, ignore_index_names => 1 }

merged with the hash passed in. To disable any of those features, pass in a hashref like the following

{ ignore_constraint_names => 0, # ... other options }

WARNING: You are strongly advised to check all SQL files created, before applying them.

deployment_statements

Arguments: $schema, $type, $version, $directory, $sqlt_args

Returns the SQL statements for deploying the schema.

If a DDL file exists in the directory (from a prior "create_ddl_dir" call), reads and returns its contents. Otherwise, throws an exception. There is no on-the-fly SQL generation without a Deploy class.

For deployments, use "deploy" in DBIO::Schema instead, which routes to the storage's native Deploy class if available.

datetime_parser

Returns the datetime parser class

datetime_parser_type

Defines the datetime parser class - currently defaults to DateTime::Format::MySQL

build_datetime_parser

See "datetime_parser"

is_replicating

A boolean that reports if a particular DBIO::Storage::DBI is set to replicate from a master database. Default is undef, which is the result returned by databases that don't support replication.

lag_behind_master

Returns a number that represents a certain amount of lag behind a master db when a given storage is replicating. The number is database dependent, but starts at zero and increases with the amount of lag. Default in undef

relname_to_table_alias

Arguments: $relname, $join_count
Return Value: $alias

DBIO uses DBIO::Relationship names as table aliases in queries.

This hook is to allow specific DBIO::Storage drivers to change the way these aliases are named.

The default behavior is "$relname_$join_count" if $join_count > 1, otherwise "$relname".

USAGE NOTES

DBIO and AutoCommit

DBIO can do some wonderful magic with handling exceptions, disconnections, and transactions when you use AutoCommit => 1 (the default) combined with txn_do for transaction support.

If you set AutoCommit => 0 in your connect info, then you are always in an assumed transaction between commits, and you're telling us you'd like to manage that manually. A lot of the magic protections offered by this module will go away. We can't protect you from exceptions due to database disconnects because we don't know anything about how to restart your transactions. You're on your own for handling all sorts of exceptional cases if you choose the AutoCommit => 0 path, just as you would be with raw DBI.

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

To install DBIO, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBIO

CPAN shell

perl -MCPAN -e shell
install DBIO

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	Go to GitHub issues (only if GitHub is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

VERSION

SYNOPSIS

DESCRIPTION

METHODS

connect_info

DBIO specific connection attributes

on_connect_do

on_disconnect_do

dbh_do

disconnect

with_deferred_fk_checks

connected

dbh

register_driver

register_driver

register_connector_driver

last_insert_id

deploy_defaults

deploy_setup

register_connector_driver

connect_call_datetime_setup

connect_call_rebase_sqlmaker

select

last_insert_id

sqlt_type

bind_attribute_by_data_type

is_datatype_numeric

create_ddl_dir

deployment_statements

datetime_parser

datetime_parser_type

build_datetime_parser

is_replicating

lag_behind_master

relname_to_table_alias

USAGE NOTES

DBIO and AutoCommit

AUTHOR

COPYRIGHT AND LICENSE

Module Install Instructions

Keyboard Shortcuts