--- name: "Catmandu::Store::DBI" section: "3pm" platform: "linux" package: "libcatmandu-dbi-perl" version: "0.13-1" release: "Debian 13 (Trixie)" platforms: ["linux"] url: "https://new.carta.tech/Catmandu::Store::DBI" --- # Catmandu::Store::DBI(3pm) A Catmandu::Store backed by DBI ## 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 efficiënt 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