.\" -*- mode: troff; coding: utf-8 -*-
.\" Automatically generated by Pod::Man 5.01 (Pod::Simple 3.43)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" \*(C` and \*(C' are quotes in nroff, nothing in troff, for use with C<>.
.ie n \{\
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
.    if \nF \{\
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{\
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "Catmandu::Store::DBI 3pm"
.TH Catmandu::Store::DBI 3pm 2024-03-24 "perl v5.38.2" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH NAME
Catmandu::Store::DBI \- A Catmandu::Store backed by DBI
.SH VERSION
.IX Header "VERSION"
Version 0.0424
.SH SYNOPSIS
.IX Header "SYNOPSIS"
.Vb 2
\&    # 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 => \*(AqDBI:mysql:database=mydb\*(Aq, # prefix "DBI:" optional
\&        username => \*(Aqxyz\*(Aq, # optional
\&        password => \*(Aqxyz\*(Aq, # optional
\&    );
\&
\&    my $obj1 = $store\->bag\->add({ name => \*(AqPatrick\*(Aq });
\&
\&    printf "obj1 stored as %s\en" , $obj1\->{_id};
\&
\&    # Force an id in the store
\&    my $obj2 = $store\->bag\->add({ _id => \*(Aqtest123\*(Aq , name => \*(AqNicolas\*(Aq });
\&
\&    my $obj3 = $store\->bag\->get(\*(Aqtest123\*(Aq);
\&
\&    $store\->bag\->delete(\*(Aqtest123\*(Aq);
\&
\&    $store\->bag\->delete_all;
\&
\&    # All bags are iterators
\&    $store\->bag\->each(sub { ... });
\&    $store\->bag\->take(10)\->each(sub { ... });
.Ve
.SH DESCRIPTION
.IX Header "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).
.PP
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.
.PP
DO NOT USE Catmandu::Store::DBI on an existing database! Tables and
data can be deleted and changed.
.SH LIMITATIONS
.IX Header "LIMITATIONS"
Currently only MySQL, Postgres and SQLite are supported. Text columns are also
assumed to be utf\-8.
.SH CONFIGURATION
.IX Header "CONFIGURATION"
.IP data_source 4
.IX Item "data_source"
Required. The connection parameters to the database. See DBI for more information.
.Sp
Examples:
.Sp
.Vb 4
\&      dbi:mysql:foobar   <= a local mysql database \*(Aqfoobar\*(Aq
\&      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
.Ve
.Sp
Drivers for each database need to be available on your computer. Install then with:
.Sp
.Vb 3
\&    cpanm DBD::mysql
\&    cpanm DBD::Pg
\&    cpanm DBD::SQLite
.Ve
.IP user 4
.IX Item "user"
Optional. A user name to connect to the database
.IP password 4
.IX Item "password"
Optional. A password for connecting to the database
.IP default_order 4
.IX Item "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 \f(CW$field\fR is the name of a table column. By default
set to 'ID'.
.SH "DATABASE CONFIGURATION"
.IX Header "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.
.PP
This behavior can be changed with mapping option:
.PP
.Vb 10
\&    my $store = Catmandu::Store::DBI\->new(
\&        data_source => \*(AqDBI:mysql:database=test\*(Aq,
\&        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 => \*(Aqstring\*(Aq, required => 1, column => \*(Aqbook_title\*(Aq},
\&                    isbn => {type => \*(Aqstring\*(Aq, unique => 1},
\&                    authors => {type => \*(Aqstring\*(Aq, array => 1}
\&                }
\&            }
\&        }
\&    );
.Ve
.PP
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.
.PP
See Catmandu::Store::DBI::Bag for more information.
.SS "Column types"
.IX Subsection "Column types"
.IP string 4
.IX Item "string"
.PD 0
.IP integer 4
.IX Item "integer"
.IP binary 4
.IX Item "binary"
.IP datetime 4
.IX Item "datetime"
.PD
Only MySQL, PostgreSQL
.IP datetime_milli 4
.IX Item "datetime_milli"
Only MySQL, PostgreSQL
.IP json 4
.IX Item "json"
Only PostgreSQL
.Sp
This is mapped internally to postgres field of type "jsonb".
.Sp
Please use the serializer Catmandu::Serializer::json_string,
.Sp
if you choose to store the perl data structure into this type of field.
.Sp
Reasons:
.Sp
* there are several types of serializers. E.g. serializer "messagepack"
  produces a string that is not accepted by a jsonb field in postgres
.Sp
* 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.
.SS "Column options"
.IX Subsection "Column options"
.IP column 4
.IX Item "column"
Name of the table column if it differs from the key in your data.
.IP array 4
.IX Item "array"
Boolean option, default is \f(CW0\fR. Note that this is only supported for PostgreSQL.
.IP unique 4
.IX Item "unique"
Boolean option, default is \f(CW0\fR.
.IP index 4
.IX Item "index"
Boolean option, default is \f(CW0\fR. Ignored if \f(CW\*(C`unique\*(C'\fR is true.
.IP required 4
.IX Item "required"
Boolean option, default is \f(CW0\fR.
.SH "AUTO RECONNECT"
.IX Header "AUTO RECONNECT"
This library automatically connects to the underlying
.PP
database, and reconnects when that connection is lost.
.PP
There is one exception though: when the connection is lost
.PP
in the middle of a transaction, this is skipped and
.PP
a Catmandu::Error is thrown. Reconnecting during a
.PP
transaction would have returned a new transaction,
.PP
and (probably?) committed the lost transaction
.PP
contrary to your expectation. There is actually no way to
.PP
recover from that, so throwing an error seemed
.PP
liked to a "good" way to solve that.
.PP
In order to avoid this situation, try to avoid
.PP
a big time lap between database actions during
.PP
a transaction, as your server may have thrown
.PP
you out.
.PP
P.S. the mysql option \f(CW\*(C`mysql_auto_reconnect\*(C'\fR
.PP
does NOT automatically reconnect during a transaction
.PP
exactly for this reason.
.SH "SEE ALSO"
.IX Header "SEE ALSO"
Catmandu::Bag, DBI