Provided by: libcatmandu-dbi-perl_0.12-3_all 
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