NAME

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

VERSION

version 1.00

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)

Executes $query and calls $row_handler once for each row. Does not return anything.

query_for_list($query, [$row_mapper])

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.

query_for_map($query, $row_mapper)

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.

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