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.

COPYRIGHT

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>