Catman...e::DBI(3pm)

💖 Catmandu::Store::DBI 💖

okay bestie, gather round, because this is the User Contributed Perl Documentation for a lil module called Catmandu::Store::DBI and it's genuinely so useful, no cap 💅

✨ VERSION ✨

Version 0.0424 — yes she's got a version number, she's official 🎀

---

🌸 SYNOPSIS 🌸

ok so here's the vibe, from the command line first:

# 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 { ... });

look at her go 🦋 adding, getting, deleting, iterating — full main character energy, periodt.

---

👑 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' — that's a Catmandu::Store. Database tables are 'bags' — that's a Catmandu::Bag. iykyk 💅

Databases need to be preconfigured for accepting Catmandu data. When no specialized Catmandu tables exist in a database then Catmandu will create them automatically. Peep "DATABASE CONFIGURATION" below for the tea ☕

DO NOT USE Catmandu::Store::DBI on an existing database! Tables and data can be deleted and changed. 😭 fr fr, this is not a drill, she will absolutely rearrange your whole life and your data too.

---

💀 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, bestie:

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. (yes, with the ë, she's fancy like that 🧚)

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

---

and that's the whole page bestie, you ate it 🫶

sign-off: this was served up by perl v5.38.2 on 2024-03-24 — mwah, take care of her, she's iconic ⭐💖