/ 
DB-Handy-1.08
/ DB::Handy

NAME

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

VERSION

Version 1.07

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.

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

  • 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 / NUM_OF_FIELDS - $sth->{NAME} (array-ref of column names in SELECT list order for named columns, alphabetical for SELECT * / JOIN) and $sth->{NUM_OF_FIELDS} (integer count) are set after execute, matching DBI statement-handle attributes. 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.

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

  • 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). Compatible with DBI.

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

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}. For named SELECT lists the order matches the SELECT list; for SELECT * and JOIN results the order is alphabetical. 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 named columns, alphabetical 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 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:

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

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

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.

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']

Compatible with DBI.

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. Compatible with DBI.

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. Compatible with DBI.

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

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.

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.

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

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

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