/ 
DB-Handy-1.09
/ DB::Handy

NAME

DB::Handy - Pure-Perl flat-file relational database with DBI-like interface

VERSION

Version 1.09

SYNOPSIS

use DB::Handy;

# -------------------------------------------------------
# DBI-like interface (recommended)
# -------------------------------------------------------

# 1. Connect to the database (Creates directory and DB if not exists)
# Note: Uses DB::Handy->connect instead of DBI->connect
my $dbh = DB::Handy->connect('./mydata', 'mydb', {
    RaiseError => 1,
    PrintError => 0,
});

# 2. DDL operations
$dbh->do("CREATE TABLE emp (
    id     INT         NOT NULL,
    name   VARCHAR(40) NOT NULL,
    dept   VARCHAR(20),
    salary INT         DEFAULT 0
)");
$dbh->do("CREATE UNIQUE INDEX emp_id ON emp (id)");

# 3. DML operations with placeholders
$dbh->do("INSERT INTO emp (id,name,dept,salary) VALUES (?,?,?,?)",
         1, 'Alice', 'Eng', 75000);
$dbh->do("INSERT INTO emp (id,name,dept,salary) VALUES (?,?,?,?)",
         2, 'Bob',   'Eng', 60000);
$dbh->do("INSERT INTO emp (id,name,dept,salary) VALUES (?,?,?,?)",
         3, 'Carol', 'HR',  55000);

# 4. Querying data (Prepared statement + fetch loop)
my $sth = $dbh->prepare(
    "SELECT name, salary FROM emp WHERE salary >= ? ORDER BY salary DESC");
$sth->execute(60000);
while (my $row = $sth->fetchrow_hashref) {
    printf "%s: %d\n", $row->{name}, $row->{salary};
}
$sth->finish;

# 5. Utility fetching methods
my $rows = $dbh->selectall_arrayref(
    "SELECT name, dept FROM emp ORDER BY name", {Slice => {}});
# $rows = [ {name=>'Alice', dept=>'Eng'}, ... ]

my $row = $dbh->selectrow_hashref(
    "SELECT * FROM emp WHERE id = ?", {}, 1);
# $row = {id=>1, name=>'Alice', dept=>'Eng', salary=>75000}

my $h = $dbh->selectall_hashref("SELECT * FROM emp", 'id');
# $h->{1}{name} eq 'Alice'

# 6. Error handling
$dbh->do("INSERT INTO emp (id,name) VALUES (?,?)", 1, 'Dup')
    or die $dbh->errstr;

# 7. Disconnect
$dbh->disconnect;

# -------------------------------------------------------
# Low-level interface
# -------------------------------------------------------

my $db = DB::Handy->new(base_dir => './mydata');
$db->execute("USE mydb");

my $res = $db->execute("SELECT * FROM emp WHERE salary > 50000");
if ($res->{type} eq 'rows') {
    for my $row (@{ $res->{data} }) {
        print "$row->{name}: $row->{salary}\n";
    }
}

TABLE OF CONTENTS

DESCRIPTION

DB::Handy is a self-contained, pure-Perl relational database engine that stores data in fixed-length binary flat files. It requires no external database server, no C compiler, and no XS modules.

It is designed to be highly portable and works on any environment where Perl 5.005_03 or later runs.

Key features:

  • Zero dependencies - only core Perl modules (Fcntl, File::Path, File::Spec, POSIX).

  • DBI-like interface - connect, prepare, execute, fetchrow_hashref, etc. This allows code written for DB::Handy to be easily adapted to DBI. This is the recommended interface. Note: DB::Handy is not a DBI driver and does not load the DBI module. See "DBI COMPATIBILITY".

  • Low-level interface - DB::Handy->new plus The native engine interface (DB::Handy->new and $db->execute($sql)). SQL statements are executed directly, and results are returned as specific Perl hash data structures containing execution status and data.

  • SQL support - SELECT with JOIN, subqueries, UNION/INTERSECT/EXCEPT, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET, aggregates, CASE expressions, and more. IN (...), NOT IN (...), and pure OR expressions on indexed columns use index lookups. -- and /* ... */ comments are removed before parsing, and runs of whitespace between tokens are collapsed, but neither happens inside a string literal: a newline, tab or carriage return in a quoted value is stored as written, so a statement may be laid out across several lines without disturbing its data.

  • File locking - a shared or exclusive flock is taken on the data and index files for the duration of every read and write, which serialises concurrent access from multiple processes on a local file system. The return value of flock is not checked; see "BUGS AND LIMITATIONS" for what that means on a file system where locking is unavailable.

  • Portable - works on Windows and UNIX/Linux without modification.

INCLUDED DOCUMENTATION

The doc/ directory contains SQL cheat sheets in 21 languages for use as learning materials.

DBI COMPATIBILITY

DB::Handy intentionally mirrors the DBI programming interface so that application code can be ported between the two with minimal change. The table below summarises which parts of DBI are supported and which are not.

Compatible (works the same way as DBI)

  • connect / disconnect - DB::Handy->connect($dir, $db, \%opts) and $dbh-disconnect> follow DBI conventions. RaiseError and PrintError behave as in DBI.

  • do - $dbh->do($sql, @bind) prepares, executes, and discards the result in one call, returning the number of affected rows or '0E0' for zero rows, just like DBI.

  • prepare / execute - $dbh->prepare($sql) returns a statement handle. $sth->execute(@bind) substitutes ? positional placeholders and runs the statement. The return value semantics (affected rows, '0E0', undef on error) match DBI.

  • bind_param - $sth->bind_param($pos, $value) (1-based position) works the same as DBI.

  • fetchrow_hashref / fetchrow_arrayref / fetchrow_array / fetch - All four fetch methods work as in DBI. fetch is an alias for fetchrow_arrayref.

  • fetchall_arrayref - Accepts {Slice => {}} (array of hash-refs) and {Slice => []} (array of array-refs, the default), matching DBI.

  • fetchall_hashref - Works as in DBI.

  • selectall_arrayref / selectall_hashref / selectrow_hashref / selectrow_arrayref - All four convenience methods have the same signature and return values as their DBI counterparts.

  • quote - Single-quotes a scalar and doubles embedded single-quotes; returns NULL for undef. Behaviour matches DBI's default quote.

  • finish - Resets the cursor; returns 1.

  • rows - $sth->rows returns the row count for the last execute, as in DBI.

  • errstr / err - Both the handle-level accessors ($dbh->errstr, $sth->errstr) and the package-level variable ($DB::Handy::errstr) work the same way as $DBI::errstr / $DBI::err.

  • NAME / NAME_lc / NAME_uc / NUM_OF_FIELDS / NUM_OF_PARAMS / Statement - $sth->{NAME} (array-ref of column names in SELECT list order for a named column list, CREATE TABLE declaration order for SELECT * / JOIN) and $sth->{NUM_OF_FIELDS} (integer count) are set after execute, matching DBI statement-handle attributes. NAME_lc and NAME_uc carry the same list case-folded. $sth->{NUM_OF_PARAMS} and $sth->{Statement} are available as soon as prepare returns. NAME is also populated from the SQL for zero-row results.

  • table_info / column_info - Return data in the same key-naming convention as DBI (TABLE_NAME, TABLE_TYPE, COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, IS_NULLABLE, COLUMN_DEF).

  • ping - Returns 1 when active, 0 after disconnect.

Not Compatible (differs from or absent in DBI)

  • No DBI DSN format - DBI uses "dbi:Driver:param=val" DSNs. DB::Handy uses a plain directory path or the proprietary "base_dir=DIR;database=DB" mini-DSN. dbi:Handy:... strings are not recognised.

  • No transaction support - DB::Handy always operates in AutoCommit mode; there is no way to group statements into an atomic transaction. begin_work, commit, and rollback are implemented but always return undef and set errstr. AutoCommit always returns 1.

  • Column order - DB::Handy preserves column order for named SELECT lists (including AS aliases), SELECT * (uses CREATE TABLE order), and JOIN with SELECT * (table appearance order, each in CREATE order). Compatible with DBI.

  • RaiseError / PrintError are standalone - In DBI, RaiseError and PrintError are handled by the DBI framework itself. In DB::Handy they are implemented by the connection-handle code only and may not fire in every error path that DBI would cover.

  • No type_info / type_info_all - DBI provides type_info and type_info_all to query data-type capabilities. These methods are not implemented.

  • Limited statement-level attributes - DBI statement handles expose many attributes. DB::Handy supports NAME, NAME_lc, NAME_uc, NUM_OF_FIELDS, NUM_OF_PARAMS and Statement; TYPE, PRECISION, SCALE, NULLABLE, CursorName, ParamValues and RowsInCache are not implemented.

  • last_insert_id semantics - last_insert_id accepts the same four positional arguments as DBI ($catalog, $schema, $table, $field) but ignores them. It returns the row count of the most recent INSERT (always 1 for a single-row insert), not a generated key value: there are no auto-increment columns, so there is no key to return. Code ported from DBI that uses the return value as a row identifier will be wrong.

  • No BLOB / CLOB types - DBI supports large-object binding via special type constants. DB::Handy has no BLOB/CLOB storage; VARCHAR is capped at 255 bytes.

  • No database-handle cloning or fork safety - DBI provides clone and handles fork safely. DB::Handy does not implement clone and makes no special provision for forked processes.

  • No HandleError callback - DBI supports the HandleError attribute for custom error callbacks. DB::Handy does not implement HandleError.

METHODS - Connection handle (DB::Handy::Connection)

A connection handle is returned by connect. It is an instance of DB::Handy::Connection and provides a DBI-like interface for executing SQL and fetching results.

connect( $dsn, $database [, \%opts] )

# Positional arguments
my $dbh = DB::Handy->connect('./data', 'mydb');

# DSN string
my $dbh = DB::Handy->connect('base_dir=./data;database=mydb');

# With options
my $dbh = DB::Handy->connect('./data', 'mydb', {
    RaiseError => 1,
    PrintError => 0,
});

Creates and returns a connection handle (DB::Handy::Connection).

The first argument ($dsn) is one of:

  • A plain directory path used as the base storage directory.

  • A dbi:Handy:key=val;... DSN string (dbi:Handy: prefix is stripped before parsing).

  • A bare key=val;... parameter string (no prefix).

Recognised DSN keys: base_dir (alias dir) and database (alias db). See "dbi:Handy DSN" in DIFFERENCES FROM DBI for a full parameter table.

$database is the logical name of the database. The corresponding directory ($dsn/$database/) is created automatically if it does not exist. To avoid automatic creation, pass AutoUse => 0 in the options hash.

Options:

RaiseError => 1

Die (die) on any error. Default: 0. Compatible with DBI.

PrintError => 1

Warn (warn) on any error. Default: 0. Compatible with DBI.

AutoUse => 0

Do not automatically issue USE $database after connecting. Useful when you want to create the database programmatically. Default: 1 (auto-use). This option does not exist in DBI.

Returns undef on failure; check $DB::Handy::errstr.

DBI note: In DBI, the DSN is always in the form "dbi:Driver:..." and the second and third arguments are username and password. DB::Handy uses the second argument as the database name and has no authentication concept.

do( $sql [, @bind_values] )

$dbh->do("CREATE TABLE t (id INT, val VARCHAR(20))");
$dbh->do("INSERT INTO t (id,val) VALUES (?,?)", 42, 'hello');
my $n = $dbh->do("DELETE FROM t WHERE id = ?", 42);

Prepare and execute $sql in one call, discarding the result set. ? placeholders are substituted left-to-right with @bind_values.

Returns:

  • The number of rows affected for INSERT/UPDATE/DELETE.

  • '0E0' (the string "zero but true") when zero rows are affected. This is numerically zero but evaluates to true in boolean context.

  • undef on error. Check $dbh->errstr.

This is the recommended method for DDL statements and DML that does not return rows. Compatible with DBI.

prepare( $sql )

my $sth = $dbh->prepare("SELECT * FROM emp WHERE dept = ?");

Parses and stores $sql, returning a statement handle (DB::Handy::Statement). The SQL is not executed at this point.

? characters in the SQL string are treated as positional placeholders. Values are supplied at execute() time.

Returns the statement handle, or undef on error. Compatible with DBI.

selectall_arrayref( $sql, \%attr, @bind_values )

# Array of hash-refs (one per row)
my $rows = $dbh->selectall_arrayref(
    "SELECT * FROM emp WHERE dept = ?",
    {Slice => {}},
    'Eng');

# Array of array-refs (one per row, columns in SELECT list order)
my $rows = $dbh->selectall_arrayref(
    "SELECT id, name FROM emp ORDER BY id");

Execute $sql (with optional @bind_values) and return all rows as an array-ref.

Attribute Slice:

{Slice => {}}

Each row is a hash-ref { column_name => value, ... }.

{Slice => []} (default, or omit \%attr)

Each row is an array-ref with values in $sth->{NAME} order: the SELECT list order for a named column list, and the CREATE TABLE declaration order for SELECT * (qualified as alias.col, table by table, for a JOIN).

Returns undef on error. Compatible with DBI.

selectall_hashref( $sql, $key_field, \%attr, @bind_values )

my $emp = $dbh->selectall_hashref("SELECT * FROM emp", 'id');
print $emp->{1}{name};   # 'Alice'
print $emp->{2}{salary}; # 60000

Execute $sql and return a hash-ref whose keys are the values of $key_field and whose values are row hash-refs.

If $key_field appears more than once in the result set, later rows silently overwrite earlier ones (same behaviour as DBI).

Returns undef on error. Compatible with DBI.

selectrow_hashref( $sql, \%attr, @bind_values )

my $row = $dbh->selectrow_hashref(
    "SELECT * FROM emp WHERE id = ?", {}, 1);
# $row = {id=>1, name=>'Alice', dept=>'Eng', salary=>75000}
# or undef if no row matches

Execute $sql, fetch the first row as a hash-ref, then call finish. Returns undef if no rows match or on error. Compatible with DBI.

selectrow_arrayref( $sql, \%attr, @bind_values )

my $row = $dbh->selectrow_arrayref(
    "SELECT name, salary FROM emp WHERE id = ?", {}, 1);
# $row = ['Alice', 75000]  (columns in SELECT list order)

Execute $sql, fetch the first row as an array-ref, then call finish. Returns undef if no rows match or on error.

Note: Column order follows the SELECT list for named columns; for SELECT * it follows CREATE TABLE declaration order. See "Column order". Compatible with DBI.

quote( $value )

my $s = $dbh->quote("O'Brien");  # "'O''Brien'"
my $n = $dbh->quote(42);         # "'42'"
my $u = $dbh->quote(undef);      # "NULL"

Return a SQL string literal suitable for direct interpolation into a SQL statement. Single-quote the value and double any embedded single-quote characters. Return the unquoted string NULL for undef.

Note: Numeric values are also quoted as strings ("'42'"), unlike some DBI drivers that pass integers through unquoted. Prefer ? placeholders over quote wherever possible.

Compatible with DBI (default quote behaviour).

last_insert_id()

$dbh->do("INSERT INTO emp (id,name) VALUES (?,?)", 4, 'Dave');
my $n = $dbh->last_insert_id;   # 1 (one row was inserted)

Return the row count of the most recent INSERT statement. This is always 1 on a successful single-row INSERT, or the total count for a bulk INSERT ... SELECT.

last_insert_id accepts the same four positional arguments as DBI ($catalog, $schema, $table, $field) but ignores them; only the connection object is used. Compatible with DBI.

disconnect()

$dbh->disconnect;

Mark the connection as closed. Subsequent calls to do, prepare, etc. will fail. Always returns 1.

In DBI, disconnect may flush uncommitted transactions; DB::Handy has no transactions, so disconnect is a no-op beyond setting the disconnected flag. Compatible with DBI.

errstr()

my $msg = $dbh->errstr;

Return the error message from the most recent failed operation on this handle, or the empty string if there was no error.

Like DBI, DB::Handy resets errstr and err when a statement is prepared or executed, so a message left over from an earlier failure is never mistaken for the outcome of a call that has just succeeded.

The package-level variable $DB::Handy::errstr holds the last error from any handle, analogous to $DBI::errstr. It is not reset. Compatible with DBI.

err()

my $code = $dbh->err;

Return the error code from the most recent failed operation (always 1 for any error, 0 for no error). Reset together with errstr. Analogous to $DBI::err. Compatible with DBI.

ping()

my $alive = $dbh->ping;

Return true while the handle is usable, false after disconnect. There is no server to reach, so this reports the handle's own state. Compatible with DBI.

AutoCommit()

my $ac = $dbh->AutoCommit;   # always 1

Always returns 1. This method, not the $dbh->{AutoCommit} hash key, is the authoritative answer; see "ATTRIBUTES".

begin_work() / commit() / rollback()

$dbh->begin_work or die $dbh->errstr;

All three always fail: they return undef and set errstr to explain that DB::Handy has no transactions. They exist so that ported DBI code fails visibly instead of appearing to open a transaction. Connecting with AutoCommit => 0 is refused for the same reason.

table_info()

my $rows = $dbh->table_info;
for my $t (@$rows) { print "$t->{TABLE_NAME}\n" }

Return an array reference of hash references, one per table in the current database. Not compatible with DBI, in two ways: DBI's table_info takes four positional arguments ($catalog, $schema, $table, $type) and returns a statement handle, whereas this method takes no arguments and returns the rows directly. The hash keys follow DBI's naming (TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS).

column_info( $table )

my $rows = $dbh->column_info('emp');
for my $c (@$rows) { print "$c->{COLUMN_NAME} $c->{TYPE_NAME}\n" }

Return an array reference of hash references, one per column of $table, in declaration order. Not compatible with DBI: DBI's column_info takes $catalog, $schema, $table, $column and returns a statement handle; this method takes the table name as its only argument and returns the rows directly. Calling it with DBI's four arguments passes undef as the table name and returns undef.

The hash keys follow DBI's naming (TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, ORDINAL_POSITION, IS_NULLABLE, COLUMN_DEF).

METHODS - Statement handle (DB::Handy::Statement)

A statement handle is created by $dbh->prepare($sql). It is an instance of DB::Handy::Statement.

execute( [@bind_values] )

$sth->execute;                   # no placeholders
$sth->execute(42, 'Alice');      # substitute two ? placeholders

Execute the prepared statement. ? placeholders are substituted left-to-right with the supplied values. If no values are supplied and bind_param was called previously, the pre-bound values are used.

The number of values must equal $sth->{NUM_OF_PARAMS}. A mismatch sets errstr and returns undef rather than running a statement in which some placeholder was left unsubstituted or some value was silently dropped.

Returns:

  • For SELECT: the number of rows in the result set, or '0E0' for zero rows.

  • For INSERT/UPDATE/DELETE/DDL: the number of affected rows, or '0E0' for zero.

  • undef on error.

After a successful execute on a SELECT, call the fetch* methods to retrieve rows. Compatible with DBI.

bind_param( $position, $value [, \%attr] )

my $sth = $dbh->prepare("INSERT INTO t (id,name) VALUES (?,?)");
$sth->bind_param(1, 42);
$sth->bind_param(2, 'Alice');
$sth->execute;                   # uses pre-bound values

Pre-bind a value to the placeholder at $position (1-based). The \%attr argument is accepted but ignored (no type coercion is performed).

The bound values are consumed on the next execute() call that is invoked with no arguments. Compatible with DBI.

fetchrow_hashref( [$name] )

while (my $row = $sth->fetchrow_hashref) {
    print "$row->{name}: $row->{salary}\n";
}

Return the next row from the result set as a hash-ref mapping column names to values. Returns undef at end of result or if no SELECT has been executed.

The optional $name argument ("NAME", "NAME_lc", "NAME_uc") is accepted for DBI compatibility but does not change the returned keys; column names are always returned in their original (schema-defined) case.

Compatible with DBI.

fetchrow_arrayref()

while (my $row = $sth->fetchrow_arrayref) {
    print join(', ', @$row), "\n";
}

Return the next row as an array-ref with values in the order defined by $sth->{NAME}. For named SELECT lists the order matches the SELECT list; for SELECT * and JOIN results it is the CREATE TABLE declaration order. Returns undef at end of result.

Note: Column order follows the SELECT list for named columns; for SELECT * it follows CREATE TABLE declaration order. See "Column order". Compatible with DBI.

fetchrow_array()

my @row = $sth->fetchrow_array;

Return the next row as a plain list in $sth->{NAME} order (SELECT list order for a named column list, CREATE TABLE declaration order for SELECT *), or an empty list at end of result.

Note: Column order matches the SELECT list for named columns. Compatible with DBI.

fetch()

Alias for fetchrow_arrayref. Compatible with DBI.

fetchall_arrayref( [$slice] )

# Array of hash-refs
my $all = $sth->fetchall_arrayref({});

# Array of array-refs (default)
my $all = $sth->fetchall_arrayref([]);
my $all = $sth->fetchall_arrayref;

Consume all remaining rows and return them as an array-ref. The optional $slice argument controls the row format:

Hash-ref slice {}

Each row is returned as a hash-ref { col => val, ... }.

Array-ref slice [] or omitted

Each row is returned as an array-ref with values in $sth->{NAME} order: the SELECT list order for a named column list, and the CREATE TABLE declaration order for SELECT * (qualified as alias.col, table by table, for a JOIN).

Column-index slices are not supported. DBI lets you pass a list of column positions, as in fetchall_arrayref([0, 2]), to select a subset of the columns. DB::Handy ignores the contents of the array-ref and always returns every column; name the columns you want in the SELECT list instead.

The $max_rows argument is not supported. DBI accepts a second argument limiting how many rows are fetched, as in fetchall_arrayref(undef, 10). DB::Handy accepts the argument and ignores it, always returning every remaining row; use LIMIT in the SQL, or fetchrow_hashref in a loop, instead.

Returns undef if no statement has been executed.

fetchall_hashref( $key_field )

my $h = $sth->fetchall_hashref('id');
print $h->{1}{name};

Consume all remaining rows and return a hash-ref keyed by $key_field. Each value is a row hash-ref. If the key column has duplicate values, later rows overwrite earlier ones. Compatible with DBI.

rows()

my $count = $sth->rows;

Return the number of rows affected by the last DML statement or returned by the last SELECT. This value is also the return value of execute. Compatible with DBI.

finish()

$sth->finish;

Reset the cursor to the beginning of the result set and release any associated resources. Does not close the statement handle; the same $sth can be re-executed. Always returns 1. Compatible with DBI.

errstr() / err()

The error message and error code from the most recent failed operation on this statement handle. See "errstr()" and "err()" under the connection handle section. Compatible with DBI.

ATTRIBUTES

Statement-handle attributes

The following attributes are available on $sth after a successful execute:

$sth->{NAME}

An array-ref of column names in the result set:

  • Named SELECT list: follows the SELECT list order. SELECT salary, name gives ['salary', 'name'].

  • SELECT * on a single table: follows the CREATE TABLE declaration order. SELECT * FROM emp where emp has columns (id, name, dept) gives ['id', 'name', 'dept'].

  • SELECT * with JOIN: table appearance order (FROM first, then each JOIN table in order), each table's columns in declaration order, as qualified names alias.col.

The attribute is set correctly even for zero-row results. Compatible with DBI.

$sth->{NAME_lc} / $sth->{NAME_uc}

The same list as NAME, lower-cased and upper-cased respectively. Useful when the SQL and the calling code disagree about identifier case. Compatible with DBI.

$sth->{NUM_OF_FIELDS}

The number of columns in the result set (integer). Set to 0 for non-SELECT statements. Compatible with DBI.

$sth->{NUM_OF_PARAMS}

The number of ? placeholders in the prepared statement. Available immediately after prepare, before execute. A ? inside a string literal or a comment is not counted, because execute does not consume one for it either. Compatible with DBI.

$sth->{Statement}

The SQL text passed to prepare, unchanged. Compatible with DBI.

The following DBI statement-handle attributes are not implemented: TYPE, PRECISION, SCALE, NULLABLE, CursorName, ParamValues, RowsInCache.

Connection-handle attributes

RaiseError

When true, any error causes an immediate die. Can be set at connect time via the options hash. Compatible with DBI.

PrintError

When true, any error causes a warn. Can be set at connect time. Compatible with DBI.

AutoCommit

Set to 1 at connect time, so that DBI-style code which reads $dbh->{AutoCommit} gets the expected answer.

The handle is an ordinary hash reference, so assigning to this key stores the value and it reads back -- it does not enable transactions, and it does not make commit work. Only the AutoCommit method is authoritative: it always returns 1. (Releases up to 1.08 documented this key as read-only, which it never was.) Passing AutoCommit => 0 to connect is refused outright rather than accepted and ignored.

The following DBI connection-handle attributes are not implemented: LongReadLen, LongTruncOk, ChopBlanks, FetchHashKeyName, HandleError, Profile.

METHODS - Low-level API

These methods operate directly on the DB::Handy engine object returned by DB::Handy->new. They are independent of the DBI-like layer.

Identifier restriction. Database, table and index names become path components on disk, so every method below restricts them to word characters ([A-Za-z0-9_], i.e. \w). A name containing /, \, : or .. is rejected with Invalid database name, Invalid table name or Invalid index name in $DB::Handy::errstr rather than being turned into a path. This matters when a name comes from outside the program: without the check, $db->drop_database($cgi_param) could be made to delete a directory tree outside base_dir. The SQL layer has always applied the same \w+ rule, so SQL statements are unaffected.

new( base_dir => $dir [, db_name => $name] )

my $db = DB::Handy->new(base_dir => './mydata');
my $db = DB::Handy->new(base_dir => './mydata', db_name => 'mydb');

Create a new engine instance. base_dir is created automatically if it does not exist. If db_name is supplied, the database is selected immediately (equivalent to calling use_database afterwards).

Returns undef on failure; check $DB::Handy::errstr.

execute( $sql )

my $res = $db->execute("SELECT * FROM emp WHERE salary > 50000");

Execute any SQL statement and return a result hash-ref. The hash always has a type key:

{ type => 'ok',       message => '1 row inserted' }   # DDL/DML success
{ type => 'rows',     data    => [ {...}, ... ]     }  # SELECT result
{ type => 'error',    message => '...'              }  # error
{ type => 'list',     data    => [ 'tbl1', ... ]    }  # SHOW result
{ type => 'describe', data    => [ {...}, ... ]     }  # DESCRIBE result
{ type => 'indexes',  table   => 'emp',
                      data    => [ {...}, ... ]     }  # SHOW INDEXES

For type => 'rows', each element of data is a hash-ref mapping column names to values.

create_database( $name )

$db->create_database('mydb') or die $DB::Handy::errstr;

Create the database directory. Returns 1 on success, 0 on failure.

use_database( $name )

$db->use_database('mydb') or die $DB::Handy::errstr;

Select a database for subsequent operations. Returns 1 on success, 0 if the database does not exist.

drop_database( $name )

$db->drop_database('mydb') or die $DB::Handy::errstr;

Remove the database directory and all its files. Returns 1 on success.

list_databases()

my @dbs = $db->list_databases;

Return a sorted list of database names found in base_dir.

create_table( $name, \@col_defs )

$db->create_table('emp', [
    ['id',     'INT'],
    ['name',   'VARCHAR', 40],
    ['salary', 'INT'],
]);

Create a table with the given column definitions. Each element of \@col_defs is a three-element array: [name, type, size]. size is required for CHAR columns and ignored for fixed-size types.

Returns 1 on success, 0 (with $DB::Handy::errstr set) on failure.

drop_table( $name )

Remove the table and all associated index files. Returns 1.

list_tables()

Return a sorted list of table names in the current database.

describe_table( $table )

Return an array-ref of column-definition hashes for $table:

[ { name => 'id',   type => 'INT',     size => 4  },
  { name => 'name', type => 'VARCHAR', size => 255 }, ... ]

create_index( $idxname, $table, $colname, $unique )

$db->create_index('emp_id', 'emp', 'id', 1);    # unique index
$db->create_index('emp_dept', 'emp', 'dept', 0); # non-unique index

Create a sorted binary index on $colname of $table. Set $unique to a true value to enforce a UNIQUE constraint.

Returns 1 on success, undef on error.

drop_index( $idxname, $table )

Remove the named index from $table. Returns 1 on success.

list_indexes( $table )

Return an array-ref of index-definition hashes for $table.

insert( $table, \%row )

$db->insert('emp', { id => 1, name => 'Alice', salary => 75000 });

Insert one row. Returns 1 on success, undef on error.

delete_rows( $table, $where )

$db->delete_rows('emp', sub { $_[0]{id} == 3 });

Mark matching rows as deleted (tombstone). Returns the number of deleted rows, or undef on error. Disk space is not reclaimed until vacuum is called.

vacuum( $table )

my $kept = $db->vacuum('emp');

Rewrite the data file, permanently removing rows that were marked as deleted. Returns the number of active rows kept, or undef on error.

Unlike SQL databases that run VACUUM as background maintenance, DB::Handy requires an explicit call to reclaim space.

SUPPORTED SQL

DB::Handy implements a surprisingly robust subset of SQL-92 in Pure Perl.

Data types

INT          4-byte signed integer
FLOAT        8-byte IEEE 754 double
CHAR(n)      Fixed-length string, n bytes (space-padded on write)
VARCHAR(n)   Variable-length string, stored in 255 bytes
TEXT         Alias for VARCHAR(255)
DATE         Fixed 10-byte string (YYYY-MM-DD convention, not enforced)

DDL

CREATE DATABASE name
DROP   DATABASE name
USE    name
SHOW   DATABASES

CREATE TABLE name (col_def, ...)
DROP   TABLE name
SHOW   TABLES

CREATE [UNIQUE] INDEX idxname ON table (col)
DROP   INDEX   idxname ON table
SHOW   INDEXES ON table
SHOW   INDICES ON table

DESCRIBE table

DML

INSERT INTO table (col, ...) VALUES (val, ...)
INSERT INTO table             VALUES (val, ...)  -- no column list
INSERT INTO table (col, ...) SELECT ...
SELECT ...
UPDATE table SET col=expr [, ...] [WHERE ...]
DELETE FROM table [WHERE ...]
VACUUM table

Note on INSERT...SELECT column mapping: Columns are matched by name when every destination column name appears as a key in the SELECT result row; otherwise the mapping falls back to positional order (left-to-right):

-- same names: name-based (order of SELECT list does not matter)
INSERT INTO dst (b, a) SELECT a, b FROM src   -- b <- b, a <- a

-- different names: positional fallback
INSERT INTO dst (a, b) SELECT x, y FROM src   -- a <- x, b <- y

SELECT syntax

SELECT [DISTINCT] col_expr [AS alias], ...
       | *
FROM   table [AS alias]
       [INNER | LEFT [OUTER] | RIGHT [OUTER]] JOIN table [AS alias]
           ON table.col = table.col
       | CROSS JOIN table [AS alias]
[WHERE condition]
[GROUP BY col, ...]
[HAVING condition]
[ORDER BY {col | position} [ASC|DESC], ...]
[LIMIT  n]
[OFFSET n]

Subqueries

WHERE col IN     (SELECT ...)
WHERE col NOT IN (SELECT ...)
WHERE col OP     (SELECT ...)    -- OP: = != <> < > <= >=
WHERE EXISTS     (SELECT ...)
WHERE NOT EXISTS (SELECT ...)
FROM  (SELECT ...) AS alias      -- derived table / inline view
SELECT (SELECT ...) AS alias     -- scalar subquery in SELECT list

Correlated subqueries (referencing outer table columns) are supported. Nesting depth is limited to 32 levels.

The outer query of a derived table accepts WHERE, GROUP BY, HAVING, ORDER BY, LIMIT and OFFSET, and its SELECT list may use the aggregate functions:

SELECT COUNT(*)      FROM (SELECT ...) AS sub
SELECT dept, SUM(n)  FROM (SELECT ...) AS sub GROUP BY dept
SELECT dept          FROM (SELECT ...) AS sub GROUP BY dept HAVING MAX(n) > 10

LIMIT and OFFSET are applied to the aggregated result, not to the rows going into it, so SELECT COUNT(*) FROM (...) AS sub LIMIT 1 counts every row.

Set operations

SELECT ... UNION         SELECT ...
SELECT ... UNION ALL     SELECT ...
SELECT ... INTERSECT     SELECT ...
SELECT ... INTERSECT ALL SELECT ...
SELECT ... EXCEPT        SELECT ...
SELECT ... EXCEPT ALL    SELECT ...

Set operators can be chained: SELECT ... UNION SELECT ... INTERSECT SELECT ....

UNION combines rows from both queries and removes duplicates. UNION ALL combines rows without removing duplicates. INTERSECT returns rows common to both queries (deduplicated). INTERSECT ALL returns common rows preserving multiplicity (min of the two counts). EXCEPT returns rows in the left query not present in the right (deduplicated). EXCEPT ALL returns the multiset difference.

WHERE predicates

col = val           col != val       col <> val
col < val           col <= val
col > val           col >= val
col BETWEEN low AND high
col IN (val, ...)   col NOT IN (val, ...)
col IS NULL         col IS NOT NULL
col LIKE pattern    col NOT LIKE pattern
expr AND expr       expr OR expr     NOT expr

Aggregate functions

COUNT(*)
COUNT(DISTINCT expr)
SUM(expr)
AVG(expr)
MIN(expr)
MAX(expr)

Scalar functions

UPPER(str)      LOWER(str)
LENGTH(str)     SUBSTR(str, pos [, len])
TRIM(str)
COALESCE(a, b, ...)
NULLIF(a, b)
CAST(expr AS type)

Conditional expressions

CASE WHEN cond THEN val [WHEN ...] [ELSE val] END

Operators

+   -   *   /   %       arithmetic
||                      string concatenation

Column aliases

SELECT salary * 1.1 AS new_salary FROM emp

Ordering by select-list position

SELECT name, salary FROM emp ORDER BY 2 DESC
SELECT name, salary FROM emp ORDER BY 2, 1

A sort key written as a plain number is the position of a column in the SELECT list, counting from 1, as in SQL-92. With SELECT * the positions follow the CREATE TABLE column order. A position outside that range is an error rather than a sort that quietly does nothing.

An expression is never read as a position, so ORDER BY 1+1 still sorts by the value of that expression.

The one place a position cannot be resolved is SELECT * across a JOIN, because the combined column list is not known when the sort key is parsed; use a column name there.

DATA TYPES

INT

A 4-byte signed integer stored in big-endian binary form. Range: -2,147,483,648 to 2,147,483,647. Stored size on disk: 4 bytes.

A numeric value outside that range is rejected by INSERT and UPDATE with an "Integer out of range" error; earlier releases clamped it silently to the nearest limit. A value with a fractional part is truncated towards zero (1.7 is stored as 1), and a value that is not numeric at all is stored as 0; neither is an error.

FLOAT

An 8-byte IEEE 754 double. Stored size on disk: 8 bytes. Index keys use an order-preserving encoding so that the binary sort order of the index matches numeric order. The .dat file itself holds the machine's native double -- see "BUGS AND LIMITATIONS" for what that means when a data file is copied between machines.

A value that is not numeric at all is stored as 0 and is not an error, the same rule INT follows. 'abc', '12abc', '0x10', 'Inf' and 'NaN' are all stored as 0; only a leading sign, digits, a decimal point and an exponent are read as a number. Up to 1.08 this column type had no such test and passed the value straight to pack, so a non-numeric value emitted an isn't numeric warning from inside DB::Handy -- twice per row when the column was indexed -- regardless of the caller's warning settings.

CHAR(n)

A fixed-length string of exactly n bytes. Values shorter than n are NUL-padded on write; trailing NULs are stripped on read.

VARCHAR(n) / TEXT

Stored as a fixed 255-byte field regardless of n. Values are NUL-padded on write; trailing NULs are stripped on read. Note: Unlike real databases, VARCHAR and TEXT always occupy 255 bytes on disk; there is no variable-length storage.

DATE

A 10-byte fixed string in YYYY-MM-DD form. INSERT and UPDATE reject a value that is not a well-formed calendar date: the format must be exactly four digits, a hyphen, two digits, a hyphen and two digits, the month must be 01-12, and the day must exist in that month of that year (2020-02-29 is accepted, 2021-02-29 is not; the four-hundred-year rule is applied, so 2000-02-29 is accepted and 1900-02-29 is not). NULL and the empty string are always accepted.

No date arithmetic is performed. Comparisons are plain string comparisons, which give the expected result because the format sorts chronologically. Values written by 1.08 or earlier are not re-validated on read, so an existing file may still hold something that INSERT would now refuse.

CONSTRAINTS

The following column constraints are recognised in CREATE TABLE:

NOT NULL
id INT NOT NULL

The column may not contain an empty or undefined value. Enforced on both INSERT and UPDATE.

DEFAULT value
salary INT DEFAULT 0
dept   VARCHAR(20) DEFAULT 'unknown'

Applied when an INSERT omits the column or supplies an empty value.

UNIQUE
CREATE TABLE emp (id INT, email VARCHAR(60) UNIQUE)
CREATE TABLE emp (id INT, email VARCHAR(60), UNIQUE (email))
CREATE UNIQUE INDEX emp_id ON emp (id)

Enforced at INSERT and UPDATE time. The column modifier and the table-level constraint both create a unique index called <column>_unique; CREATE UNIQUE INDEX names the index itself. Multiple NULL (empty string) values are allowed, as SQL-92 requires.

PRIMARY KEY
CREATE TABLE emp (id INT PRIMARY KEY, name VARCHAR(40))
CREATE TABLE emp (id INT, name VARCHAR(40), PRIMARY KEY (id))

Implies NOT NULL and creates a unique index called <column>_pk, so duplicate keys are rejected on INSERT and on UPDATE. A column that is both PRIMARY KEY and UNIQUE gets one index, not two. The index is created with the table, so a table that was created by DB::Handy 1.08 or earlier does not have one; add it with CREATE UNIQUE INDEX if the older table needs the constraint.

CHECK
salary INT CHECK (salary >= 0)

A simple expression evaluated on both INSERT and UPDATE. Supported in CREATE TABLE column definitions only; table-level CHECK constraints are not supported.

FOREIGN KEY constraints are not supported.

INDEXES

DB::Handy uses sorted binary index files to accelerate equality lookups.

Structure

Each index file (<table>.<idxname>.idx) begins with an 8-byte magic header ("SDBIDX1\n") followed by fixed-size entries sorted ascending by key. Each entry is [key_bytes][rec_no (4 bytes big-endian)].

Key encoding
INT    Sign-bit-flipped big-endian uint32 (order-preserving)
FLOAT  IEEE 754 order-preserving 8-byte encoding
Other  NUL-padded fixed-width string
When indexes are used

The query engine uses an index when the WHERE clause contains:

  • A simple equality or range condition on an indexed column: WHERE id = 42, WHERE id > 10, WHERE id <= 100.

  • A two-sided AND range on a single indexed column: WHERE id > 10 AND id < 20.

  • A BETWEEN predicate on an indexed column: WHERE id BETWEEN 10 AND 20.

  • An AND condition spanning different indexed columns. The engine picks the best single-column index to narrow the candidate set, then applies the full WHERE predicate as a post-filter: WHERE dept = 'Eng' AND salary > 70000.

  • A col IN (v1, v2, ...) predicate on an indexed column. The engine performs one equality index lookup per value and returns the union of matching records: WHERE id IN (10, 20, 30).

  • A pure OR expression where every atom has an index on its column. The engine performs one index lookup per OR atom and returns the union of matching records: WHERE dept = 'Eng' OR dept = 'HR', WHERE id = 1 OR id > 100. If any atom has no index the engine falls back to a full table scan.

  • A col NOT IN (v1, v2, ...) predicate on an indexed column. The engine collects the record numbers that match the IN list via the index, then returns all other records (index complement). If the column has no index a full table scan is used. NOT IN with NULL in the value list always falls back to a full table scan.

Index maintenance

Indexes are updated in-place on every INSERT, UPDATE, and DELETE. VACUUM rebuilds all indexes for a table.

FILE LAYOUT

DB::Handy stores data in flat files inside the specified base_dir.

<base_dir>/
  <database>/
    <table>.sch           schema definition (text, key=value lines)
    <table>.dat           record data (fixed-length binary, big-endian)
    <table>.<idxname>.idx sorted index file (binary)

Schema file format (.sch):

VERSION=1
RECSIZE=<bytes per record including the 1-byte active flag>
COL=<name>:<type>:<size>
[IDX=<idxname>:<colname>:<unique 0|1>]
[NN=<colname>]            NOT NULL
[DEF=<colname>:<val>]     DEFAULT value
[PK=<colname>]            PRIMARY KEY (also recorded as an IDX line)

Data file format (.dat):

Each record is RECSIZE bytes. Because files are fixed-length binary, read and seek are used for fast record traversal.

The first byte is the active flag:

0x01 for an active record, 0x00 for a deleted record and are skipped during scans.

The remaining bytes are the column values in declaration order, packed in the type-specific binary format.

Use the VACUUM SQL command to physically remove deleted records and compact the data file.

EXAMPLES

Creating a database and table

use DB::Handy;

my $dbh = DB::Handy->connect('./data', 'hr');

$dbh->do(<<'SQL');
CREATE TABLE employee (
    id         INT         NOT NULL,
    name       VARCHAR(50) NOT NULL,
    department VARCHAR(30) DEFAULT 'Unassigned',
    salary     INT         DEFAULT 0,
    hire_date  DATE
)
SQL

$dbh->do("CREATE UNIQUE INDEX emp_pk ON employee (id)");
$dbh->do("CREATE INDEX emp_dept ON employee (department)");

Inserting rows with a prepared statement

my $ins = $dbh->prepare(
    "INSERT INTO employee (id,name,department,salary,hire_date)
     VALUES (?,?,?,?,?)");

$ins->execute(1, 'Alice',   'Engineering', 90000, '2020-03-01');
$ins->execute(2, 'Bob',     'Engineering', 75000, '2021-06-15');
$ins->execute(3, 'Carol',   'HR',          65000, '2019-11-20');
$ins->execute(4, 'Dave',    'Engineering', 80000, '2022-01-10');
$ins->execute(5, 'Eve',     'HR',          70000, '2020-08-05');

Basic SELECT

my $sth = $dbh->prepare(
    "SELECT name, salary FROM employee
     WHERE department = ? AND salary >= ?
     ORDER BY salary DESC");
$sth->execute('Engineering', 80000);
while (my $row = $sth->fetchrow_hashref) {
    printf "%-15s %6d\n", $row->{name}, $row->{salary};
}
$sth->finish;
# Alice            90000
# Dave             80000

Aggregation and GROUP BY

my $rows = $dbh->selectall_arrayref(<<'SQL', {Slice=>{}});
SELECT department,
       COUNT(*) AS cnt,
       AVG(salary) AS avg_sal,
       MAX(salary) AS top_sal
FROM employee
GROUP BY department
ORDER BY avg_sal DESC
SQL

for my $r (@$rows) {
    printf "%-15s  n=%d  avg=%d  max=%d\n",
        $r->{department}, $r->{cnt}, $r->{avg_sal}, $r->{top_sal};
}

JOIN

$dbh->do("CREATE TABLE dept (
    code  VARCHAR(30) NOT NULL,
    mgr   VARCHAR(50)
)");
$dbh->do("INSERT INTO dept (code,mgr) VALUES (?,?)", 'Engineering', 'Alice');
$dbh->do("INSERT INTO dept (code,mgr) VALUES (?,?)", 'HR',          'Carol');

my $rows = $dbh->selectall_arrayref(<<'SQL', {Slice=>{}});
SELECT e.name, e.salary, d.mgr
FROM employee AS e
LEFT JOIN dept AS d ON e.department = d.code
ORDER BY e.name
SQL

What a JOIN accepts is narrower than the rest of the SELECT support, so it is worth stating plainly:

  • The ON clause is one equality between two table-qualified columns. Either operand order is fine (ON e.department = d.code and ON d.code = e.department are the same join). A second condition, a comparison other than =, an unqualified name, USING and NATURAL are all rejected with an error. Put any further restriction in the WHERE clause.

  • CROSS JOIN needs no ON; every other join type requires one. An ON written on a CROSS JOIN is accepted and ignored.

  • FULL OUTER JOIN is not supported. Combine a LEFT JOIN and a RIGHT JOIN with UNION instead.

  • The WHERE clause of a JOIN query takes AND-separated comparisons between a column and a column or literal, plus IN, NOT IN, LIKE, BETWEEN and IS [NOT] NULL. OR, NOT and parentheses are rejected with an error.

  • The select list takes column names, * and table.*. An expression or an AS alias is rejected, except in an aggregate query (one with GROUP BY, HAVING or an aggregate function), where AS works normally.

  • SELECT DISTINCT and a multi-key ORDER BY both work.

Every construct listed above as rejected returns an error naming the offending text. Up to 1.08 most of them were accepted and quietly turned into a Cartesian product or an unfiltered result; see "BUGS AND LIMITATIONS".

Anti-join with LEFT JOIN

# Employees whose department is not in the dept table
my $rows = $dbh->selectall_arrayref(<<'SQL', {Slice=>{}});
SELECT e.name
FROM employee AS e
LEFT JOIN dept AS d ON e.department = d.code
WHERE d.code IS NULL
ORDER BY e.name
SQL

Subquery

# Employees earning above the company average
my $rows = $dbh->selectall_arrayref(<<'SQL', {Slice=>{}});
SELECT name, salary
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee)
ORDER BY salary DESC
SQL

Error handling with RaiseError

use DB::Handy;
my $dbh = DB::Handy->connect('./data', 'mydb', {RaiseError => 1});
eval {
    $dbh->do("INSERT INTO employee (id,name) VALUES (?,?)", 1, 'Dup');
};
if ($@) {
    print "Caught: $@";   # UNIQUE constraint violated
}

Using the low-level API

use DB::Handy;
my $db = DB::Handy->new(base_dir => './data');
$db->execute("USE hr");

my $res = $db->execute(
    "SELECT department, COUNT(*) AS n FROM employee GROUP BY department");
if ($res->{type} eq 'rows') {
    for my $r (@{ $res->{data} }) {
        print "$r->{department}: $r->{n}\n";
    }
}
elsif ($res->{type} eq 'error') {
    die $res->{message};
}

Reclaiming space with VACUUM

$dbh->do("DELETE FROM employee WHERE salary < 70000");

# The .dat file still contains tombstone records.
# Remove them to reclaim disk space:
my $kept = $dbh->{_engine}->vacuum('employee');
print "$kept active rows retained\n";

DIFFERENCES FROM DBI

DB::Handy provides a DBI-inspired interface but is not a DBI driver and does not require the DBI module. This section gives a detailed account of every known incompatibility. See also "DBI COMPATIBILITY" for the overview table.

A few entries below record the opposite: a place where DBI users often expect a difference and there is none. Those entries say so explicitly.

dbi:Handy DSN

connect accepts a dbi:Handy:key=val;... prefix in addition to a plain directory path or a bare key=val;... parameter string. Recognised DSN keys:

Key          Meaning
-----------  --------------------------------------------------
dir          Base storage directory (alias for base_dir)
base_dir     Base storage directory
db           Database name (alias for database)
database     Database name

Examples:

DB::Handy->connect('dbi:Handy:dir=./data;db=mydb', undef);
DB::Handy->connect('dbi:Handy:base_dir=./data;database=mydb', undef);
DB::Handy->connect('dir=./data;db=mydb');       # no dbi:Handy: prefix
DB::Handy->connect('./data', 'mydb');            # positional args

Note: DB::Handy cannot be loaded as a DBI driver via DBI-connect>; use DB::Handy-connect> directly.

No transaction support

DBI provides begin_work, commit, and rollback to group statements into atomic transactions. DB::Handy always operates in AutoCommit mode: every INSERT, UPDATE, and DELETE is immediately written to disk. The begin_work, commit, and rollback methods are implemented and return undef with errstr set rather than crashing. AutoCommit always returns 1.

Column order

DB::Handy presents columns in the order they are declared in CREATE TABLE:

# Named SELECT list: order follows the SELECT list
# "SELECT salary, name FROM emp" -> NAME = ['salary', 'name']

# SELECT *: order follows CREATE TABLE declaration order
# "SELECT * FROM emp" -> NAME = ['id', 'name', 'dept', 'salary']

# JOIN with SELECT *: table appearance order, each in CREATE order
# "SELECT * FROM emp AS e JOIN dept AS d ON ..."
#   -> NAME = ['e.id', 'e.name', 'e.dept', 'e.salary',
#               'd.did', 'd.dname', 'd.budget']

No difference here: this is what DBI drivers do. The entry exists because earlier releases of DB::Handy sorted the names alphabetically instead.

The one exception is an aggregate query over a JOIN, whose rows are keyed by the select-list alias rather than by alias.col; the order still follows the select list.

RaiseError / PrintError are standalone

In DBI, RaiseError and PrintError are managed by the DBI framework itself and fire for all error paths. In DB::Handy these attributes are implemented only in the connection-handle and statement-handle code; some low-level engine errors may not trigger them.

last_insert_id semantics

DBI's last_insert_id returns the auto-generated key value from the most recent INSERT. DB::Handy's last_insert_id accepts the same four arguments ($catalog, $schema, $table, $field) but ignores them and instead returns the row count of the most recent INSERT (always 1 for a single-row insert, or the total count for INSERT...SELECT).

table_info and column_info return array-refs, not statement handles

DBI's table_info and column_info return a statement handle that must be fetched with the usual fetch* methods. DB::Handy returns a plain array-ref directly.

INSERT without a column list

Standard SQL and DBI drivers support INSERT INTO t VALUES (v1, v2, ...) without an explicit column list. DB::Handy also supports this form; values are assigned to columns in CREATE TABLE declaration order. If the number of values does not match the number of columns, an error is returned. No difference here; the entry exists only because the form is easy to assume unsupported in a driver this small.

INTERSECT / EXCEPT

INTERSECT, INTERSECT ALL, EXCEPT, and EXCEPT ALL are supported in addition to UNION and UNION ALL. These follow standard SQL set-operation semantics. DBI is not a SQL engine and imposes nothing here, so there is no DBI behaviour to differ from; the entry records the support because most small drivers lack it.

The branches of a set operation are matched by position, and the result column names come from the first branch, as SQL requires. Up to 1.08 they were matched by column name, so a branch whose column names differed from the first branch's contributed rows of NULL.

VARCHAR is always 255 bytes on disk

Regardless of the declared VARCHAR(n), DB::Handy stores every VARCHAR value in a fixed 255-byte field on disk. There is no variable-length storage. However, the declared size is enforced on INSERT and UPDATE: a value longer than the declared n causes an error. VARCHAR without a size and VARCHAR(255) accept any value up to 255 bytes.

No WINDOW functions

SQL window functions (ROW_NUMBER(), RANK(), PARTITION BY, etc.) are not supported. Any SELECT containing an OVER (...) clause returns a type='error' result with a message explaining the limitation. Use GROUP BY with aggregate functions (SUM, COUNT, AVG, etc.) as an alternative.

No FOREIGN KEY or VIEW

FOREIGN KEY: The REFERENCES and FOREIGN KEY ... REFERENCES syntax is accepted in CREATE TABLE for SQL compatibility, but the constraint is not enforced. INSERT and UPDATE succeed regardless of whether the referenced row exists. The CREATE TABLE success message includes a note that the constraint is not enforced.

VIEW: CREATE VIEW returns a type='error' result.

No BLOB / CLOB

There is no large-object storage type. VARCHAR is the largest type and is limited to 255 bytes.

DIAGNOSTICS

Error attributes

Error handling in DB::Handy via the DBI-like API is controlled by the RaiseError and PrintError attributes.

RaiseError (set to 1)

If an error occurs (e.g., SQL syntax error, missing table, unique constraint violation), the module will call die with the error message. It is highly recommended to enable this and use eval { ... } to catch exceptions.

PrintError (set to 1)

If an error occurs, the module will call warn with the error message, but execution will continue (methods will return undef).

Error variables

$DB::Handy::errstr         last error from any handle (package-level)
$dbh->errstr               last error on this connection handle
$sth->errstr               last error on this statement handle

The two handle-level accessors are set on every failed operation and cleared on success. The package-level $DB::Handy::errstr is only ever overwritten by the next error, so it still holds the last error seen by any handle after a subsequent success; test the handle accessor, or the return value of the method, rather than the package variable.

Common error messages

No database selected

A table operation was attempted before calling use_database (or before connecting to a named database).

Table '<name>' already exists

create_table (or CREATE TABLE) was called for a table that already has a .sch file.

Table '<name>' does not exist

A DML or DDL operation referenced a table for which no .sch file was found.

UNIQUE constraint violated on '<idxname>' ...

An INSERT or UPDATE would have created a duplicate value in a column covered by a UNIQUE index. The index behind a PRIMARY KEY is named <column>_pk and the one behind a UNIQUE column or table constraint is named <column>_unique.

called with <n> bind variables when <m> are needed

$sth->execute was given a number of values that does not match the number of ? placeholders in the statement.

ORDER BY position <n> is not in the select list

A numeric ORDER BY key referred to a select-list position that does not exist. Positions start at 1, and they cannot be used with SELECT * across a JOIN.

NOT NULL constraint violated on column '<col>'

An INSERT or UPDATE supplied a NULL or empty string for a column declared NOT NULL.

Subquery returns more than one row

A scalar subquery (used in a context that expects a single value) returned multiple rows.

Cannot parse column def: <text>

The CREATE TABLE parser could not interpret a column definition.

Unsupported SQL: <sql>

The SQL string does not match any known pattern.

Invalid database name '<name>'

A database name was passed to new, create_database, use_database or drop_database that is not a plain identifier. Names are restricted to word characters ([A-Za-z0-9_]) because they are used directly as directory names; .., / and \\ are therefore rejected.

Invalid table name '<name>'

A table name that is not a plain identifier was passed to create_table, drop_table, or to any method that loads a schema. See Invalid database name above for the rule.

Invalid index name '<name>'

An index name that is not a plain identifier was passed to create_index or drop_index. See Invalid database name above for the rule.

Database '<name>' already exists

create_database was called for a database directory that already exists.

Database '<name>' does not exist

connect or drop_database was called for a database directory that does not exist.

Cannot open base_dir: <reason>

The base directory passed to new (or connect) could not be opened. Check that the path exists and that the process has read permission.

Cannot open dat '<file>': <reason>

A .dat record file could not be opened for reading or writing. Check file permissions and disk space.

Cannot read schema: <reason>

A .sch schema file exists but could not be read. Check file permissions.

Cannot create base_dir: <reason>

new could not create the base directory. Check parent-directory write permissions.

Cannot create database '<name>': <reason>

create_database could not create the database subdirectory. Check disk space and write permissions on base_dir.

Cannot drop database '<name>': <reason>

drop_database could not remove the database directory tree. Check that no files are locked and that write permission is granted.

DB::Handy connect failed: <message>

The low-level connect call failed. $DB::Handy::errstr contains the underlying error set by the failing operation.

DB::Handy: <message>

A fatal internal error was raised directly via die. RaiseError must be enabled (the default) for this message to propagate.

AutoCommit cannot be turned off: DB::Handy has no transactions

connect was called with AutoCommit => 0. DB::Handy has no transactions, so the request cannot be honoured and the connection is refused rather than accepted with a promise it could not keep.

Value too long for column '<col>': declared <type>(<n>), got <m> bytes

An INSERT or UPDATE supplied a value longer than the declared CHAR or VARCHAR size. The value is rejected; it is never truncated.

Integer out of range for column '<col>': '<value>' ...

An INSERT or UPDATE supplied a value outside the range the declared integer type can hold.

Invalid DATE for column '<col>': '<value>' (expected YYYY-MM-DD)

A DATE column was given a value that is not an ISO-8601 calendar date.

CHECK constraint failed on column '<col>'

An INSERT or UPDATE supplied a value the column's CHECK expression rejected.

FULL OUTER JOIN is not supported ...
NATURAL JOIN is not supported ...
JOIN ... USING is not supported ...
Unsupported JOIN condition 'ON <expr>' ...
Unqualified column in the ON clause of JOIN <table> ...
Unsupported WHERE condition in a JOIN query: '<part>' ...
Unsupported select item '<item>' in a JOIN query ...
Unknown ORDER BY column '<col>' in a JOIN query

The query used a JOIN construct the engine cannot execute. See "JOIN" for what a JOIN accepts. Each of these messages names the offending text and says what to write instead. Before 1.09 these constructs were accepted and quietly answered a different question, so code that used to "work" may now report an error; see "BUGS AND LIMITATIONS".

Each SELECT of a set operation must return the same number of columns (<n> and <m>)

The branches of a UNION, INTERSECT or EXCEPT disagree on how many columns they return.

BUGS AND LIMITATIONS

Please report any bugs or feature requests by e-mail to <ina.cpan@gmail.com>.

When reporting a bug, please include:

  • A minimal, self-contained test script that reproduces the problem.

  • The version of DB::Handy:

    perl -MDB::Handy -e 'print DB::Handy->VERSION, "\n"'

  • Your Perl version:

    perl -V

  • Your operating system and file system (Windows NTFS, Linux ext4, etc.)

Known limitations:

  • A JOIN accepts only a single-equality ON clause. The condition has to be one = between two table-qualified columns; either operand order is accepted. A second condition, any other comparison operator, an unqualified name, USING and NATURAL are rejected with an error, as is FULL OUTER JOIN. Move extra restrictions into the WHERE clause, and build a full outer join from a LEFT JOIN and a RIGHT JOIN combined with UNION.

  • The WHERE clause of a JOIN query does not accept OR, NOT or parentheses. It takes AND-separated comparisons between a column and a column or literal, plus IN, NOT IN, LIKE, BETWEEN and IS [NOT] NULL. Anything else is rejected with an error. A single-table WHERE has no such restriction.

  • The select list of a non-aggregate JOIN query does not accept expressions or AS aliases. Column names, * and table.* only. An aggregate JOIN query (GROUP BY, HAVING or an aggregate function) does accept AS.

  • Up to 1.08 these JOIN restrictions were silent. An ON clause the parser could not read, USING, NATURAL, FULL OUTER JOIN and an operand order of ON right.col = left.col all produced a Cartesian product; an unsupported WHERE condition was dropped and the rows came back unfiltered; SELECT DISTINCT over a JOIN returned empty rows; and a multi-key ORDER BY was ignored. 1.09 answers correctly where it can (reversed operand order, DISTINCT, multi-key ORDER BY, IS [NOT] NULL, BETWEEN) and reports an error where it cannot. Code written against 1.08 that appeared to work may now raise an error -- the error is the bug report.

  • A single-table WHERE clause is not syntax-checked. The JOIN parser rejects a condition it cannot read, but the single-table parser does not: a condition it fails to understand simply matches nothing. WHERE x = 1 GARBAGE, WHERE x ==== 1, WHERE x = 1 AND and WHERE (x = 1 all return zero rows instead of reporting a syntax error, so a typo in a WHERE clause looks like a query that legitimately found nothing. DELETE and UPDATE are affected in the safe direction -- an unreadable condition matches no rows, so nothing is changed or removed -- but a SELECT gives a plausible empty answer to a question it never asked. Until this is fixed, treat an unexpected empty result set as a possible typo and check the WHERE clause before concluding that the table holds no matching rows.

  • Set-operation branches are matched by position. UNION, UNION ALL, INTERSECT and EXCEPT line the branches up by column position and take the result column names from the first branch, as SQL requires. Up to 1.08 they were matched by column name, so SELECT a FROM t1 UNION SELECT b FROM t2 returned NULL for every row contributed by the second branch. A mismatch in the number of columns is now an error.

  • No transaction support. begin_work, commit, and rollback are implemented and return undef with errstr set rather than crashing. AutoCommit always returns 1. Every write is immediately committed.

  • VARCHAR is always 255 bytes on disk. Declaring VARCHAR(10) does not save disk space; the full 255 bytes are always reserved per record. However, the declared size is enforced on INSERT and UPDATE: a value longer than n causes an error. VARCHAR without a size and VARCHAR(255) accept any value up to 255 bytes.

  • NULL is represented as the empty string. There is no separate NULL marker on disk. For CHAR, VARCHAR and DATE columns a NULL value and an empty string are the same value, and IS NULL is true for both. For INT and FLOAT columns a NULL value is stored as 0 and reads back as 0, so NULL and 0 cannot be told apart and IS NULL is never true for a numeric column. Use a companion CHAR(1) flag column if a numeric column has to distinguish "unknown" from zero.

  • Aggregates over an empty set return 0, not NULL. SUM, AVG, MIN and MAX return 0 when no row qualifies; SQL-92 requires NULL for these four and 0 only for COUNT. COUNT(col) and COUNT(DISTINCT col) do skip NULL values, as SQL-92 requires.

  • LIKE is case-insensitive. SQL-92 compares LIKE patterns using the column collation, which is normally case-sensitive. DB::Handy always matches without regard to case, so LIKE 'abc' and LIKE 'ABC' select the same rows. Wrap the column in UPPER() or LOWER() on both sides if a deterministic comparison is needed.

  • A column name that does not exist yields NULL instead of an error. SELECT nosuchcol FROM t returns one NULL per row rather than failing, so a typo in a column name is silent.

  • FLOAT data files are not portable between machines. INT columns and every index file are written in a fixed byte order, but FLOAT values in the .dat file use the machine's native 8-byte double. A .dat file holding FLOAT columns is therefore readable only on machines with the same floating-point representation and byte order. Copying such a file between a little-endian and a big-endian machine will misread those columns. This is kept as-is so that data files written by earlier releases stay readable; INT, CHAR, VARCHAR and DATE columns are unaffected.

  • An unterminated /* comments out the rest of the statement. -- to end of line and /* ... */ are stripped before parsing (a marker inside a string literal is left alone), but a /* with no closing */ silently swallows everything after it instead of raising a syntax error.

  • Declared column sizes are counted in bytes, not characters. DB::Handy stores byte strings and never decodes them, so a VARCHAR(10) column holds ten bytes. Three Japanese characters encoded in UTF-8 occupy nine bytes and fit; six characters occupy eighteen bytes and are rejected. The same applies to CHAR and to the keysize of an index. Decode to characters in your own code if you need character-based limits.

  • A trailing NUL byte in a value is not preserved. CHAR, VARCHAR and DATE values are padded with NUL bytes on disk, and the padding is stripped when the record is read back, so a value that legitimately ends in "\0" loses those bytes. Interior NUL bytes are preserved.

  • A table created before 1.09 has no PRIMARY KEY index. PRIMARY KEY and UNIQUE are enforced through an index that CREATE TABLE builds, so a .sch file written by DB::Handy 1.08 or earlier still accepts duplicate keys. The data files are otherwise unchanged and are read and written normally; add CREATE UNIQUE INDEX to an older table if the constraint is wanted there too.

  • ORDER BY by position does not work with SELECT * across a JOIN. The combined column list of the joined tables is not known at the point the sort key is parsed, so the position is reported as an error. Name the column instead. Positions do work with SELECT * on a single table, and with an explicit select list anywhere.

  • No FOREIGN KEY constraints or VIEW support.

  • No WINDOW functions (ROW_NUMBER, RANK, LEAD, LAG, etc.).

  • No BLOB/CLOB large-object types.

  • Single-column indexes only. Composite (multi-column) indexes are not supported.

  • NOT IN with NULL in the value list returns no rows, as SQL semantics require. col NOT IN (v1, NULL, v2) is UNKNOWN for every row when the value is not found in the non-NULL elements, so no row matches. When the column is indexed the engine falls back to a full table scan before applying this rule.

  • No query planner. All queries have fixed execution plans; there is no cost-based optimiser.

  • The return value of flock is not checked. Every read takes a shared lock and every write an exclusive lock on the .dat file and on each index file, and a write is flushed before the lock is released, so concurrent access is serialised wherever flock works. Where it does not -- NFS with no lock daemon, some network shares, a platform that implements flock as a no-op -- the call fails silently and the access proceeds unlocked, so two processes writing the same table at the same time can interleave. The failure is ignored on purpose, because raising an error would make the module unusable on those file systems for the single-process use it is designed for; assume a single writer if the data directory is not on a local file system.

  • Cannot be used as a drop-in replacement via DBI->connect.

SEE ALSO

DBI - the standard Perl database interface that DB::Handy's API is modelled after.

DBD::SQLite - a full-featured, embeddable SQL database accessible via DBI, recommended when transaction support or a richer SQL dialect is needed.

Other modules by the same author:

HTTP::Handy, LTSV::LINQ, mb, UTF8::R2, Jacode, Jacode4e, Jacode4e::RoundTrip, mb::JSON

AUTHOR

INABA Hitoshi <ina.cpan@gmail.com>

This project was originated by INABA Hitoshi.

COPYRIGHT AND LICENSE

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