Catmandu::Store::DBI - A Catmandu::Store plugin for DBI based interfaces
Version 0.0424
# 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 { ... });
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.
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
Optional. A user name to connect to the database
Optional. A password for connecting to the database
Optional. Timeout for a inactive database handle. When timeout is reached, Catmandu checks if the connection is still alive (by use of ping) or it recreates the connection. See TIMEOUTS below for more information.
Optional. When a timeout is reached, Catmandu reconnects to the database. By default set to '0'
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.
Name of the table column if it differs from the key in your data.
Boolean option, default is 0. Note that this options is only supported for PostgreSQL.
0
Boolean option, default is 0.
It is a good practice to set the timeout high enough. When using transactions, one should avoid this situation:
$bag->store->transaction(sub{ $bag->add({ _id => "1" }); sleep $timeout; $bag->add({ _id => "2" }); });
The following warning appears:
commit ineffective with AutoCommit enabled at lib//Catmandu/Store/DBI.pm line 73. DBD::SQLite::db commit failed: attempt to commit on inactive database handle
This has the following reasons:
1. first record added 2. timeout is reached, the connection is recreated 3. the option AutoCommit is set. So the database handle commits the current transaction. The first record is committed. 4. this new connection handle is used now. We're still in the method "transaction", but there is no longer a real transaction at database level. 5. second record is added (committed) 6. commit is issued. But this unnecessary, so the database handle throws a warning.
Catmandu::Bag, DBI
To install Catmandu::DBI, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Catmandu::DBI
CPAN shell
perl -MCPAN -e shell install Catmandu::DBI
For more information on module installation, please visit the detailed CPAN module installation guide.