/ 
DB-Handy-1.00
/ DB::Handy

NAME

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

VERSION

Version 1.00

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, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET, aggregates, CASE expressions, and more.

  • File locking - shared/exclusive flock on data files for safe concurrent access from multiple processes.

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

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 / NUM_OF_FIELDS - $sth->{NAME} (array-ref of column names) and $sth->{NUM_OF_FIELDS} (integer count) are set after execute, matching DBI statement-handle attributes.

  • 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 - DBI supports begin_work, commit, and rollback. DB::Handy always operates in AutoCommit mode; there is no way to group statements into an atomic transaction. Calls to begin_work, commit, or rollback are not implemented and will cause an error.

  • No AutoCommit attribute - DBI's AutoCommit connection attribute controls whether statements are automatically committed. DB::Handy has no such attribute; all writes are immediately persistent.

  • Column order in array fetches is alphabetical - DBI drivers preserve the column order declared in the SELECT list. DB::Handy stores rows internally as hash references and therefore sorts column names alphabetically when populating $sth->{NAME} and when returning rows via fetchrow_arrayref / fetchrow_array.

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

  • No statement-level attributes beyond NAME/NUM_OF_FIELDS - DBI statement handles expose many attributes (TYPE, PRECISION, SCALE, NULLABLE, CursorName, etc.). DB::Handy only supports NAME and NUM_OF_FIELDS.

  • No standard last_insert_id signature - DBI's last_insert_id takes four positional arguments ($catalog, $schema, $table, $field). DB::Handy's last_insert_id takes no arguments and simply returns the row count of the most recent INSERT, which is always 1 on success.

  • 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 either:

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

  • A DSN string of the form "base_dir=DIR;database=DB" (or "dir=DIR;db=DB").

$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 alphabetical)
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 alphabetical column order.

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 alphabetical 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 is alphabetical, not declaration order. See "Column order in array fetches is alphabetical". Compatible with DBI (except for column order).

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.

Note: DBI's last_insert_id takes four arguments ($catalog, $schema, $table, $field) and returns the auto-generated key value of the last INSERT. DB::Handy takes no arguments and returns the row count, not a key value. The two are not compatible.

table_info()

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

Return an array-ref of hashes, one per table in the current database. Each hash contains:

TABLE_NAME   => 'emp'
TABLE_TYPE   => 'TABLE'

Note: DBI's table_info returns a statement handle; DB::Handy returns an array-ref directly. The key names follow DBI conventions but the return type is different.

column_info( $table )

my $cols = $dbh->column_info('emp');
for my $c (@$cols) {
    printf "%d  %-20s  %s\n",
        $c->{ORDINAL_POSITION}, $c->{COLUMN_NAME}, $c->{DATA_TYPE};
}

Return an array-ref of hashes, one per column of $table, in declaration order. Each hash contains:

COLUMN_NAME      => 'salary'
DATA_TYPE        => 'INT'
ORDINAL_POSITION => 4
IS_NULLABLE      => 'YES'  (or 'NO' when NOT NULL is declared)
COLUMN_DEF       => '0'    (or undef when no DEFAULT)

Note: DBI's column_info returns a statement handle; DB::Handy returns an array-ref directly. Key names follow DBI conventions but the return type is different.

ping()

if ($dbh->ping) { print "connection is alive\n" }

Returns 1 if the connection has not been disconnected, 0 otherwise. 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.

The package-level variable $DB::Handy::errstr holds the last error from any handle, analogous to $DBI::errstr. 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). Analogous to $DBI::err. Compatible with DBI.

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.

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} (alphabetical by column name). Returns undef at end of result.

Note: Column order is alphabetical. See "Column order in array fetches is alphabetical". Compatible with DBI (except for column order).

fetchrow_array()

my @row = $sth->fetchrow_array;

Return the next row as a plain list in alphabetical column order, or an empty list at end of result.

Note: Column order is alphabetical. Compatible with DBI (except for column order).

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 alphabetical column order.

Returns undef if no statement has been executed. Compatible with DBI.

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, in alphabetical order. For example, for SELECT salary, name FROM emp the value will be ['name', 'salary'], not ['salary', 'name'].

Compatible with DBI (except alphabetical order).

$sth->{NUM_OF_FIELDS}

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

The following DBI statement-handle attributes are not implemented: TYPE, PRECISION, SCALE, NULLABLE, CursorName, ParamValues, Statement, 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.

The following DBI connection-handle attributes are not implemented: AutoCommit, 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.

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 (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 position, not by name. The first column in the INSERT list receives the value of the first column in the SELECT list, the second receives the second, and so on. This works correctly whether or not the column names match:

-- same names: works as expected
INSERT INTO dst (dept, amt) SELECT dept, amt FROM src

-- different names: positional mapping
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] | CROSS] JOIN table [AS alias]
           ON condition
[WHERE condition]
[GROUP BY col, ...]
[HAVING condition]
[ORDER BY col [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.

Set operations

SELECT ... UNION     SELECT ...
SELECT ... UNION ALL SELECT ...

INTERSECT and EXCEPT are not supported.

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

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.

FLOAT

An 8-byte IEEE 754 double stored using an order-preserving encoding that keeps the binary sort order consistent with numeric order. Stored size on disk: 8 bytes.

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. No date validation or arithmetic is performed; DATE is simply a convenient alias for CHAR(10) with an implied YYYY-MM-DD format.

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 (via index)
CREATE UNIQUE INDEX emp_id ON emp (id)

Enforced at INSERT and UPDATE time. Multiple NULL (empty string) values are allowed.

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

Implies both NOT NULL and a UNIQUE index named after the column.

CHECK
salary INT CHECK (salary >= 0)

A simple expression evaluated on INSERT only. CHECK is not evaluated on 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 condition on an indexed column (e.g., WHERE id = 42). Range conditions and multi-column conditions trigger 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

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

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.

No dbi:Handy DSN

DBI connections always use a three-part DSN: "dbi:Driver:params". DB::Handy's connect takes a plain directory path or a proprietary "base_dir=DIR;database=DB" string. There is no dbi:Handy:... DSN, and DB::Handy cannot be loaded as a DBI driver.

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 AutoCommit, begin_work, commit, and rollback methods are not implemented.

Column order in array fetches is alphabetical

DBI drivers present columns in the order they appear in the SELECT list. DB::Handy stores all rows internally as Perl hash references and loses positional metadata. As a result, fetchrow_arrayref, fetchrow_array, and $sth->{NAME} always reflect alphabetical column order, regardless of the SELECT list order.

# With DBI + real driver:
# "SELECT salary, name FROM emp" -> NAME = ['salary', 'name']

# With DB::Handy:
# "SELECT salary, name FROM emp" -> NAME = ['name', 'salary']

Use fetchrow_hashref to access columns by name and avoid this issue.

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 signature and semantics

DBI's last_insert_id($catalog, $schema, $table, $field) returns the auto-generated key value from the most recent INSERT. DB::Handy's last_insert_id() takes no arguments and returns the row count of the most recent INSERT (always 1 for a single-row insert). The two are not interchangeable.

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.

No INTERSECT / EXCEPT

UNION and UNION ALL are supported. INTERSECT and EXCEPT are not.

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. There is no variable-length storage.

No WINDOW functions

SQL window functions (ROW_NUMBER(), RANK(), PARTITION BY, etc.) are not supported.

No FOREIGN KEY or VIEW

FOREIGN KEY constraints and CREATE VIEW are not supported.

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

These variables are set on every failed operation and cleared on success.

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.

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.

BUGS AND LIMITATIONS

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

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:

  • No transaction support. AutoCommit is always in effect. Power failure or process termination during a write may leave data files in an inconsistent state.

  • VARCHAR is always 255 bytes on disk. Declaring VARCHAR(10) does not save space; the full 255 bytes are always reserved.

  • Column order in array fetches is alphabetical, not declaration order.

  • INSERT...SELECT maps columns by position, not by name. The n-th column in the INSERT list receives the value of the n-th column in the SELECT list. When the column names differ between source and destination, ensure the SELECT list order matches the INSERT list order.

  • No FOREIGN KEY constraints or VIEW support.

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

  • No INTERSECT or EXCEPT set operations.

  • No BLOB/CLOB large-object types.

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

  • Range scans do not use indexes. Only equality conditions (col = val) can exploit an index; col > val and col BETWEEN always perform a full table scan.

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

  • 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.org>

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.