Database::DumpTruck3pm

Usage

  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');

Overview

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".