NAME

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

VERSION

       Version 0.0424

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.

SEE ALSO

       Catmandu::Bag, DBI