/ 
Data-Record-Serialize-Encode-dbi-1.05
River stage zero No dependents
/ Data::Record::Serialize::Encode::dbi

NAME

Data::Record::Serialize::Encode::dbi - store a record in a database

VERSION

version 1.05

SYNOPSIS

use Data::Record::Serialize;

my $s = Data::Record::Serialize->new( encode => 'sqlite', ... );

$s->send( \%record );

DESCRIPTION

Data::Record::Serialize::Encode::dbi writes a record to a database using DBI.

It performs both the Data::Record::Serialize::Role::Encode and Data::Record::Serialize::Role::Sink roles.

You cannot construct this directly. You must use "new" in Data::Record::Serialize.

Types

Field types are recognized and converted to SQL types via the following map:

S => 'text'
N => 'real'
I => 'integer'

For Postgres, B => 'boolean'. For other databases, B => 'integer'. This encoder handles transformation of the input "truthy" Boolean value into a form appropriate for the database to ingest.

NULL values

By default numeric fields are set to NULL if they are empty. This can be changed by setting the nullify attribute.

Performance

Records are by default written to the database in batches (see the batch attribute) to improve performance. Each batch is performed as a single transaction. If there is an error during the transaction, record insertions during the transaction are not rolled back.

Errors

Transaction errors result in an exception in the Data::Record::Serialize::Error::Encode::dbi::insert class. See Data::Record::Serialize::Error for more information on exception objects.

Compatibility

This module has been tested on SQLite, PostgreSQL, and Sybase. See t/encoders/dbi.t for more information on how to test against non-SQLite databases.

SQL::Translator is used to generate the DDL; unfortunately its Sybase DDL producer has some issues/bugs and is temporarily monkey-patched to work around them.

OBJECT ATTRIBUTES

dsn

The value passed to the constructor.

table

The value passed to the constructor.

schema

The value passed to the constructor.

drop_table

The value passed to the constructor.

create_table

The value passed to the constructor.

primary

The value passed to the constructor.

db_user

The value passed to the constructor.

db_pass

The value passed to the constructor.

batch

The value passed to the constructor.

dbitrace

The value passed to the constructor.

METHODS

queue

$queue = $obj->queue;

The queue containing records not yet successfully transmitted to the database. This is only of interest if "batch" is not 0.

Each element is an array containing values to be inserted into the database, in the same order as the fields in "output_fields" in Data::Serialize.

to_bool

$bool = $self->to_bool( $truthy );

Convert a truthy value to something that the JSON encoders will recognize as a boolean.

flush

$s->flush;

Flush the queue of records to the database. It returns true if all of the records have been successfully written.

If writing fails:

  • Writing of records ceases.

  • The failing record is left at the head of the queue. This ensures that it is possible to retry writing the record.

  • an exception object (in the Data::Record::Serialize::Error::Encode::dbi::insert class) will be thrown. The failing record (in its final form after formatting, etc) is available via the object's payload method.

If a record fails to be written, it will still be queued for the next attempt at writing to the database. If this behavior is undesired, make sure to remove it from the queue:

use Data::Dumper;

if ( ! eval { $output->flush } ) {
    warn "$@", Dumper( $@->payload );
    shift $output->queue->@*;
}

As an example of completely flushing the queue while notifying of errors:

use Data::Dumper;

until ( eval { $output->flush } ) {
    warn "$@", Dumper( $@->payload );
    shift $output->queue->@*;
}

send

$s->send( \%record );

Send a record to the database. If there is an error, an exception object (with class Data::Record::Serialize::Error::Encode::dbi::insert) will be thrown, and the record which failed to be written will be available via the object's payload method.

If in "batch" mode, the record is queued for later transmission. When the number of records queued reaches that specified by the "batch" attribute, the flush method is called. See "flush" for more information on how errors are handled.

close

$s->close;

Close the database handle. If writing is batched, records in the queue are written to the database via "flush". An exception will be thrown if a record cannot be written. See "flush" for more details.

As an example of draining the queue while notifying of errors:

use Data::Dumper;

until ( eval { $output->close } ) {
    warn "$@", Dumper( $@->payload );
    shift $output->queue->@*;
}

DEMOLISH

This method is called when the object is destroyed. It closes the database handle but does not flush the record queue.

A warning is emitted if the record queue is not empty; turn off the Data::Record::Serialize::Encode::dbi::queue warning to silence it.

INTERNALS

CONSTRUCTOR OPTIONS

dsn

Required The DBI Data Source Name (DSN) passed to DBI. It may either be a string or an arrayref containing strings or arrayrefs, which should contain key-value pairs. Elements in the sub-arrays are joined with =, elements in the top array are joined with :. For example,

[ 'SQLite', { dbname => $db } ]

is transformed to

SQLite:dbname=$db

The standard prefix of dbi: will be added if not present.

cached

If true, the database connection is made with DBI::connect_cached rather than DBI::connect

table

Required The name of the table in the database which will contain the records. It will be created if it does not exist.

schema

The schema to which the table belongs. Optional.

drop_table

If true, the table is dropped and a new one is created.

create_table

If true, a table will be created if it does not exist.

primary

A single output column name or an array of output column names which should be the primary key(s). If not specified, no primary keys are defined.

db_user

The name of the database user

db_pass

The database password

batch

The number of rows to write to the database at once. This defaults to 100.

If greater than 1, batch rows are cached and then sent out in a single transaction. See "Performance" for more information.

dbitrace

A trace setting passed to DBI.

ATTRIBUTES

These attributes are available in addition to the standard attributes defined for Data::Record::Serialize::new.

SUPPORT

Bugs

Please report any bugs or feature requests to bug-data-record-serialize-encode-dbi@rt.cpan.org or through the web interface at: https://rt.cpan.org/Public/Dist/Display.html?Name=Data-Record-Serialize-Encode-dbi

Source

Source is available at

https://gitlab.com/djerius/data-record-serialize-encode-dbi

and may be cloned from

https://gitlab.com/djerius/data-record-serialize-encode-dbi.git

AUTHOR

Diab Jerius <djerius@cpan.org>

COPYRIGHT AND LICENSE

This software is Copyright (c) 2017 by Smithsonian Astrophysical Observatory.

This is free software, licensed under:

The GNU General Public License, Version 3, June 2007