NAME

Footprintless::Plugin::Database::AbstractProvider - A base class for database providers

VERSION

version 1.06

SYNOPSIS

my $db = $footprintless->db('dev.db');
$db->execute('create table foo ( id int, name varchar(16) )');

my $rows_inserted = $db->execute(
    q[
        insert into foo (id, name) values
            (1, 'foo'),
            (2, 'bar')
    ]);

my $name_of_1 = $db->query_for_scalar(
    {
        sql => 'select id, name from foo where id = ?',
        parameters => [1]
    },
    sub {
        my ($id, $name) = @_;
        return $name;
    });

my $rows_count = $db->query_for_scalar('select count(*) from foo');

DESCRIPTION

Provides a base class implementing the common abstractions. Other providers should extend this class and override methods as desired.

There are a few core concepts used for the execute, and query methods. They are

query

A string containing a sql statement, or a hashref with a required sql entry containing the sql statement and an optional parameters entry contianing a list of values for the placeholders of the prepared statement. For example:

{
    sql => 'select name, phone from employees where dept = ? and title = ?',
    parameters => [$dept, $title]
}

row_handler

A callback that will be called once for each row. It will be passed the list of values requested in the query. This callback does not return anything.

row_mapper

A callback that will be called once for each row. It will be passed the list of values requested in the query. It must return a value that will be collected by the query_for_xxx method according to that methods behavior.

ENTITIES

A simple deployment:

db => {
    provider => 'mysql',
    schema => 'my_table',
    port => 3306,
    username => $properties->{db.username},
    pasword => $properties->{db.password}
}

A more complex situation, perhaps tunneling over ssh to your prod database:

db => {
    provider => 'postgres',
    database => 'my_database',
    schema => 'my_table',
    hostname => 'my.production.server',
    port => 5432,
    username => $properties->{db.username},
    pasword => $properties->{db.password},
    tunnel_hostname => 'my.bastion.host'
}

CONSTRUCTORS

new($entity, $coordinate, %options)

Constructs a new database provider instance. Should be called on a subclass. Subclasses should NOT override this method, rather, override _init. See Footprintless::MixableBase for details.

METHODS

backup($to, [%options])

Will backup the database to $to. The allowed values for $to are:

- Another instance of the same provider to pipe to the restore method - A callback method to call with each chunk of the backup - A GLOB to write to - A filename to write to

The options are determined by the implementation.

begin_transaction()

Begins a transaction.

client([%options])

Will open an interactive client connected to the database.

commit_transaction()

Commits the current transaction.

connect()

Opens a connection to the database.

disconnect()

Closes the current connection to the database.

execute($query)

Executes $query and returns the number of rows effected.

get_schema()

Returns the configured schema name.

query($query, $row_handler, %options)

Executes $query and calls $row_handler once for each row. Does not return anything. If you do not set the hash option, the $row_handler gets the field data in the @_ array (see hash option below).

The following options may be set:

column_info

To get column information, set this option to an array ref - when the query is executed, before the $row_handler is called for the first time, the array will be populated with the column information, the indexed by result column. This array may be empty if the underlying driver does not support column information.

Each item in the array will be a hash containing the following properties if the driver does not support a field it will be missing:

name: The column name
type: The SQL type identified by number - these are supposedly cataloged as part of the ISO/IEC 9075 type registry - but I would not know because this particular spec seems to be a particularly well guarded secret (I could not get it for free on the internet). I suggest looking directly at the type_name and type_info properties instead of worrying about this.
type_name: The SQL type identified by name.
type_info: A single type_info hash describing the type for the column as described at http://search.cpan.org/~timb/DBI-1.637/DBI.pm#type_info
column_size: The precision of the column. For numeric types, this is the number of digits (does not include sign, decimal point, or even exponent digits). For character based types, this is the number of bytes which may or may not correspond to the number of characters.
scale: An integer indicating "scale" or undef for types where scale is not used.
nullable: Indicates whether or not we can assign this column to null - undef if the nullability is unknown. Otherwise this may be evaluated a boolean.

hash

Set this to a true value to get the parameters to the $row_handler to be set up suitable for a hash assignment. The actual parameters are an array, but will now come as: column-name-1 => field-1, column-name-2 => field-2...

no_fetch

Set this to a true value to skip the fetching of data from a result set - this is useful for "queries" that have no result set and would throw an exception when we attempt to fetch a row (i.e. ALTER SESSION queries).

query_for_list($query, [$row_mapper,] %options)

Executes $query and calls $row_mapper once for each row. $row_mapper is expected to return a scalar representing the row. All of the returned scalars will be collected into a list and returned. When called in list context, a list is returned. In scalar context, an arrayref is returned. If $row_mapper is not supplied, each rows values will be returned as an arrayref (or as hashref if the hash option is selected). For information about the options, see the query() method - being that they are the same options.

query_for_map($query, [$row_mapper,] %options)

Executes $query and calls $row_mapper once for each row. $row_mapper is expected to return a hashref with a single key/value pair. All of the returned hashrefs will be collected into a single hash and returned. When called in list context, a hash is returned. In scalar context, a hashref is returned. If $row_mapper is not supplied, each rows values will be returned as a hashref using the first value as the key, and the whole rows arrayref as the value (or as hashref if the hash option is selected). For information about the options, see the query() method - being that they are the same options..

query_for_scalar($query, $row_mapper)

Executes $query and calls $row_mapper once for the first row of the result set. $row_mapper is expected to return a scalar representing the row. If $row_mapper is not supplied, the first value from the first row is returned. This can be useful for queries like select count(*) from foo.

restore($from, %options)

Will restore the database from $from. The allowed values for $from are:

- Another instance of the same provider to pipe from the backup method - A hashref containing a command key whose value is a command to pipe input from - A GLOB to read from - A filename to read from