table of contents
Catmandu::Store::DBI(3pm) | User Contributed Perl Documentation | Catmandu::Store::DBI(3pm) |
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¶
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
2023-02-02 | perl v5.36.0 |