Catmandu::Store::DBI

NAME

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

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 { ... });

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' Catmandu::Store. Databases tables are 'bags' (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.

LIMITATIONS

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

CONFIGURATION

data_source

Required. The connection parameters to the database. See 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

user: Optional. A user name to connect to the database

password: Optional. A password for connecting to the database

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'.

DATABASE CONFIGURATION

When no tables exists for storing data in the database, then Catmandu will create them. By default tables are created for each 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 Catmandu::Store::DBI::Bag provides a more efficieent way to query records.

See Catmandu::Store::DBI::Bag for more information.

Column types

string

integer

binary

datetime: Only MySQL, PostgreSQL

datetime_milli: Only MySQL, PostgreSQL

json: Only PostgreSQL This is mapped internally to postgres field of type "jsonb". Please use the serializer 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 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.

Column options

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

array: Boolean option, default is 0. Note that this is only supported for PostgreSQL.

unique: Boolean option, default is 0.

index: Boolean option, default is 0. Ignored if "unique" is true.

required: Boolean option, default is 0.

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 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 "mysql_auto_reconnect"

does NOT automatically reconnect during a transaction

exactly for this reason.