NAME

Database::DumpTruck - Relaxing interface to SQLite

SYNOPSIS

my $dt = new Database::DumpTruck;

$dt->insert({Hello => 'World'});
$dt->create_index(['Hello']);
$dt->upsert({Hello => 'World', Yolo => 8086});
my $data = $dt->dump;

$dt->insert([
    {Hello => 'World'},
    {Hello => 'Hell', Structured => {
        key => value,
        array => [ 1, 2, 3, {} ],
    }}], 'table2');
my $data2 = $dt->dump('table2');
$dt->drop('table2');
$dt->execute('SELECT 666');

my @columns = $dt->column_names();

$dt->save_var('number_of_the_beast', 666);
my $number_of_the_beast = $dt->get_var('number_of_the_beast');

DESCRIPTION

This is a simple document-oriented interface to a SQLite database, modelled after Scraperwiki's Python dumptruck module. It allows for easy (and maybe inefficient) storage and retrieval of structured data to and from a database without interfacing with SQL.

Database::DumpTruck attempts to identify the type of the data you're inserting and uses an appropriate SQLite type:

integer

This is used for integer values. Will be used for 8086, but not "8086" or 8086.0.

real

This is used for numeric values that are not integer. Will be used for 8086.0, but not "8086" or 8086.

bool

This is used for values that look like result of logical statemen. A crude check for values that are both "" and 0 or both "1" and 1 at the same time is in place. This is a result of comparison or a negation.

To force a value to look like boolean, prepend it with a double negation: e.g. !!0 or !!1.

json text

Used for ARRAY and HASH references. Values are converted into and from JSON strings upon insert and dump.

text

Pretty much everything else.

METHODS

new ([params])

Initialize the database handle. Accepts optional hash with parameters:

dbname (Default: dumptruck.db)

The database file.

table (Default: dumptruck)

Name for the default table.

vars_table (Default: _dumptruckvars)

Name of the variables table.

vars_table_tmp (Default: _dumptruckvarstmp)

Name of the temporary table used when converting the values for variables table.

auto_commit (Default: 1)

Enable automatic commit.

column_names ([table_name])

Return a list of names of all columns in given table, or table dumptruck.

execute (sql, [params])

Run a raw SQL statement and get structured output. Optional parameters for ? placeholders can be specified.

commit ()

Commit outstanding transaction. Useful when auto_commit is off.

close ()

Close the database handle. You should not need to call this explicitly.

create_index (columns, [table_name], [if_not_exists], [unique])

Create an optionally unique index on columns in a given table. Can be told to do nothing if the index already exists.

create_table (data, table_name, [error_if_exists])

Create a table and optionally error out if it already exists. The data structure will be based on data, though no data will be inserted.

insert (data, [table_name], [upsert])

Insert (and optionally replace) data into a given table or dumptruck. Creates the table with proper structure if it does not exist already.

upsert (data, [table_name])

Replace data into a given table or dumptruck. Creates the table with proper structure if it does not exist already.

Equivalent to calling insert with upsert parameter set to 1.

get_var (key)

Retrieve a saved value for given key from the variable database.

save_var (key, value)

Insert a value for given key into the variable database.

tables ()

Returns a list of names of all tables in the database.

dump ([table_name])

Returns all data from the given table or dumptduck nicely structured.

drop ([table_name])

Drop the given table or dumptruck.

BUGS

None known.

SEE ALSO

• https://github.com/scraperwiki/dumptruck - Python module this one is heavily inspired by.

COPYRIGHT

Copyright 2014, Lubomir Rintel

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

AUTHOR

Lubomir Rintel <lkundrak@v3.sk>