path: root/lib/Catmandu/Store/DBI.pm

package Catmandu::Store::DBI;

use Catmandu::Sane;
use Catmandu::Util qw(require_package);
use DBI;
use Catmandu::Store::DBI::Bag;
use Moo;
use MooX::Aliases;
use Catmandu::Error;
use namespace::clean;

our $VERSION = "0.13";

with 'Catmandu::Store';
with 'Catmandu::Transactional';

has data_source => (
    is       => 'ro',
    required => 1,
    alias    => 'dsn',
    trigger  => sub {
        my $ds = $_[0]->{data_source};
        $ds = $ds =~ /^DBI:/i ? $ds : "DBI:$ds";
        $_[0]->{data_source} = $ds;
    },
);
has username => (is => 'ro', default => sub {''}, alias => 'user');
has password => (is => 'ro', default => sub {''}, alias => 'pass');
has default_order           => (is => 'ro', default => sub {'ID'});
has handler                 => (is => 'lazy');
has _in_transaction         => (is => 'rw', writer => '_set_in_transaction',);
has _dbh => (is => 'lazy', builder => '_build_dbh', writer => '_set_dbh',);

# DEPRECATED methods. Were only invented to tackle of problem of reconnection
sub timeout {
    warn "method timeout has been replaced by auto reconnect";
}

sub has_timeout {
    warn "method has_timeout has been replaced by auto reconnect";
    0;
}

sub reconnect_after_timeout {
    warn "method reconnect_after_timeout has been replaced by auto reconnect";
}

sub handler_namespace {
    'Catmandu::Store::DBI::Handler';
}

sub _build_handler {
    my ($self) = @_;
    my $driver = $self->dbh->{Driver}{Name} // '';
    my $ns     = $self->handler_namespace;
    my $pkg;
    if ($driver =~ /pg/i) {
        $pkg = 'Pg';
    }
    elsif ($driver =~ /sqlite/i) {
        $pkg = 'SQLite';
    }
    elsif ($driver =~ /mysql/i) {
        $pkg = 'MySQL';
    }
    else {
        Catmandu::NotImplemented->throw(
            'Only Pg, SQLite and MySQL are supported.');
    }
    require_package($pkg, $ns)->new;
}

sub _build_dbh {
    my ($self) = @_;
    my $opts = {
        AutoCommit                       => 1,
        RaiseError                       => 1,
        mysql_auto_reconnect             => 1,
        mysql_enable_utf8                => 1,
        pg_utf8_strings                  => 1,
        sqlite_use_immediate_transaction => 1,
        sqlite_unicode                   => 1,
    };
    my $dbh
        = DBI->connect($self->data_source, $self->username, $self->password,
        $opts,);
    $dbh;
}

sub dbh {

    my $self = $_[0];
    my $dbh  = $self->_dbh;

    # reconnect when dbh is not set (should never happen)
    return $self->reconnect
        unless defined $dbh;

    # check validity of dbh
    # for performance reasons only check every second
    if ( defined( $self->{last_ping_t} ) ) {

        return $dbh if (time - $self->{last_ping_t}) < 1;

    }

    $self->{last_ping_t} = time;
    return $dbh if $dbh->ping;

    # one should never reconnect to a database during a transaction
    # because that would initiate a new transaction
    Catmandu::Error->throw("Connection to DBI backend lost, and cannot reconnect during a transaction")
        unless $dbh->{AutoCommit};

    # reconnect and return dbh
    # note: mysql_auto_reconnect only works when AutoCommit is 1
    $self->reconnect;

}

sub reconnect {

    my $self = $_[0];
    my $dbh  = $self->_dbh;
    $dbh->disconnect if defined($dbh);
    $self->_set_dbh($self->_build_dbh);
    $self->_dbh;

}

sub transaction {
    my ($self, $sub) = @_;

    if ($self->_in_transaction) {
        return $sub->();
    }

    my $dbh = $self->dbh;
    my @res;

    eval {
        $self->_set_in_transaction(1);
        $dbh->begin_work;
        @res = $sub->();
        $dbh->commit;
        $self->_set_in_transaction(0);
        1;
    } or do {
        my $err = $@;
        eval {$dbh->rollback};
        $self->_set_in_transaction(0);
        die $err;
    };

    @res;
}

sub DEMOLISH {
    my ($self) = @_;
    $self->{_dbh}->disconnect if $self->{_dbh};
}

1;

__END__

=pod

=encoding utf8

=head1 NAME

Catmandu::Store::DBI - A Catmandu::Store backed by DBI

=head1 VERSION

Version 0.0424

=head1 SYNOPSIS

    # From the command line
    $ catmandu import JSON to DBI --data_source SQLite:mydb.sqlite < data.json

    # Or via a configuration file
    $ cat catmandu.yml
    ---
    store:
       mydb:
         package: DBI
         options:
            data_source: "dbi:mysql:database=mydb"
            username: xyz
            password: xyz
    ...
    $ catmandu import JSON to mydb < data.json
    $ catmandu export mydb to YAML > data.yml
    $ catmandu export mydb --id 012E929E-FF44-11E6-B956-AE2804ED5190 to JSON > record.json
    $ catmandu count mydb
    $ catmandy delete mydb

    # From perl
    use Catmandu::Store::DBI;

    my $store = Catmandu::Store::DBI->new(
        data_source => 'DBI:mysql:database=mydb', # prefix "DBI:" optional
        username => 'xyz', # optional
        password => 'xyz', # optional
    );

    my $obj1 = $store->bag->add({ name => 'Patrick' });

    printf "obj1 stored as %s\n" , $obj1->{_id};

    # Force an id in the store
    my $obj2 = $store->bag->add({ _id => 'test123' , name => 'Nicolas' });

    my $obj3 = $store->bag->get('test123');

    $store->bag->delete('test123');

    $store->bag->delete_all;

    # All bags are iterators
    $store->bag->each(sub { ... });
    $store->bag->take(10)->each(sub { ... });

=head1 DESCRIPTION

A Catmandu::Store::DBI is a Perl package that can store data into
DBI backed databases. The database as a whole is  a 'store'
L<Catmandu::Store>. Databases tables are 'bags' (L<Catmandu::Bag>).

Databases need to be preconfigured for accepting Catmandu data. When
no specialized Catmandu tables exist in a database then Catmandu will
create them automatically. See  "DATABASE CONFIGURATION" below.

DO NOT USE Catmandu::Store::DBI on an existing database! Tables and
data can be deleted and changed.

=head1 LIMITATIONS

Currently only MySQL, Postgres and SQLite are supported. Text columns are also
assumed to be utf-8.

=head1 CONFIGURATION

=over

=item data_source

Required. The connection parameters to the database. See L<DBI> for more information.

Examples:

      dbi:mysql:foobar   <= a local mysql database 'foobar'
      dbi:Pg:dbname=foobar;host=myserver.org;port=5432 <= a remote PostGres database
      dbi:SQLite:mydb.sqlite <= a local SQLLite file based database mydb.sqlite
      dbi:Oracle:host=myserver.org;sid=data01 <= a remote Oracle database

Drivers for each database need to be available on your computer. Install then with:

    cpanm DBD::mysql
    cpanm DBD::Pg
    cpanm DBD::SQLite

=item user

Optional. A user name to connect to the database

=item password

Optional. A password for connecting to the database

=item default_order

Optional. Default the default sorting of results when returning an iterator.
Choose 'ID' to order on the configured identifier field, 'NONE' to skip all
ordering, or "$field" where $field is the name of a table column. By default
set to 'ID'.

=back

=head1 DATABASE CONFIGURATION

When no tables exists for storing data in the database, then Catmandu
will create them. By default tables are created for each L<Catmandu::Bag>
which contain an '_id' and 'data' column.

This behavior can be changed with mapping option:

    my $store = Catmandu::Store::DBI->new(
        data_source => 'DBI:mysql:database=test',
        bags => {
            # books table
            books => {
                mapping => {
                    # these keys will be directly mapped to columns
                    # all other keys will be serialized in the data column
                    title => {type => 'string', required => 1, column => 'book_title'},
                    isbn => {type => 'string', unique => 1},
                    authors => {type => 'string', array => 1}
                }
            }
        }
    );

For keys that have a corresponding table column configured, the method 'select' of class L<Catmandu::Store::DBI::Bag> provides
a more efficiënt way to query records.

See L<Catmandu::Store::DBI::Bag> for more information.

=head2 Column types

=over

=item string

=item integer

=item binary

=item datetime

Only MySQL, PostgreSQL

=item datetime_milli

Only MySQL, PostgreSQL

=item json

Only PostgreSQL

This is mapped internally to postgres field of type "jsonb".

Please use the serializer L<Catmandu::Serializer::json_string>,

if you choose to store the perl data structure into this type of field.

Reasons:

* there are several types of serializers. E.g. serializer "messagepack"
  produces a string that is not accepted by a jsonb field in postgres

* the default serializer L<Catmandu::Serializer::json> converts the perl data structure to a binary json string,
  and the DBI client reencodes that utf8 string (because jsonb is a sort of text field),
  so you end up having a double encoded string.

=back

=head2 Column options

=over

=item column

Name of the table column if it differs from the key in your data.

=item array

Boolean option, default is C<0>. Note that this is only supported for PostgreSQL.

=item unique

Boolean option, default is C<0>.

=item index

Boolean option, default is C<0>. Ignored if C<unique> is true.

=item required

Boolean option, default is C<0>.

=back

=head1 AUTO RECONNECT

This library automatically connects to the underlying

database, and reconnects when that connection is lost.

There is one exception though: when the connection is lost

in the middle of a transaction, this is skipped and

a L<Catmandu::Error> is thrown. Reconnecting during a

transaction would have returned a new transaction,

and (probably?) committed the lost transaction

contrary to your expectation. There is actually no way to

recover from that, so throwing an error seemed

liked to a "good" way to solve that.


In order to avoid this situation, try to avoid

a big time lap between database actions during

a transaction, as your server may have thrown

you out.

P.S. the mysql option C<< mysql_auto_reconnect >>

does NOT automatically reconnect during a transaction

exactly for this reason.

=head1 SEE ALSO

L<Catmandu::Bag>, L<DBI>

=cut