NAME DB::Handy - Pure-Perl flat-file relational database with DBI-like interface SYNOPSIS use DB::Handy; # DBI-like interface my $dbh = DB::Handy->connect('./mydata', 'mydb', { RaiseError => 1, PrintError => 0, }); $dbh->do("CREATE TABLE emp (id INT, name VARCHAR(40), salary INT)"); my $sth = $dbh->prepare("SELECT name FROM emp WHERE salary >= ?"); $sth->execute(70000); while (my $row = $sth->fetchrow_hashref) { print "$row->{name}\n"; } $sth->finish; $dbh->disconnect; # Low-level interface my $db = DB::Handy->new(base_dir => './mydata'); $db->execute("USE mydb"); my $res = $db->execute("SELECT * FROM emp"); 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 and no XS modules -- just Perl 5.005_03 or later. It provides both a low-level execute() API and a DBI-like interface (connect/prepare/execute/fetch) without depending on the DBI module. DB::Handy provides a DBI-inspired interface but is NOT a DBI driver and does NOT require the DBI module. This module is designed for: - Lightweight applications - Embedded or standalone environments - A wide range of Perl versions - Educational purposes It is not intended to replace full-featured RDBMS systems. See "DIFFERENCES FROM DBI" in the module POD for details. INCLUDED DOCUMENTATION The doc/ directory contains SQL cheat sheets in 21 languages for use as learning materials. INSTALLATION To install this module type the following: perl Makefile.PL make make test make install Or using pmake.bat (Windows): pmake.bat test pmake.bat install COMPATIBILITY This module works with Perl 5.005_03 and later. WHY PERL 5.005_03 SPECIFICATION? This module adheres to the Perl 5.005_03 specification--not because we use the old interpreter, but because this specification represents the simple, original Perl programming model that makes programming enjoyable. THE STRENGTH OF MODERN TIMES Some people think the strength of modern times is the ability to use modern technology. That thinking is insufficient. The strength of modern times is the ability to use ALL technology up to the present day. By adhering to the Perl 5.005_03 specification, we gain access to the entire history of Perl--from 5.005_03 to 5.42 and beyond--rather than limiting ourselves to only the latest versions. Key reasons: - Simplicity: Original Perl approach keeps programming "raku" (easy/fun) - JPerl: Final version of JPerl (Japanese Perl) - Universal: Runs on ALL Perl versions (5.005_03 through 5.42+) - Philosophy: Programming should be enjoyable (Camel Book readers know!) Perl 5.6+ introduced character encoding complexity that made programming harder. By following the 5.005_03 specification, we maintain the joy of Perl programming. TARGET USE CASES - Small tools and utilities - Embedded scripting - Prototyping - Educational use LIMITATIONS - Not a full SQL database - No transaction support; begin_work/commit/rollback return undef with errstr set (AutoCommit always on) - No query optimizer or planner - Limited concurrency support; not suitable for high-write workloads - VARCHAR is always stored as 255 bytes on disk; declared size is enforced on INSERT/UPDATE (values longer than declared size are rejected) - Single-column indexes only - A JOIN takes a single-equality ON clause between two table-qualified columns; either operand order is fine. A second condition, any other comparison operator, an unqualified name, USING, NATURAL and FULL OUTER JOIN are rejected with an error. Put extra restrictions in the WHERE clause; build a full outer join from LEFT JOIN and RIGHT JOIN with UNION - The WHERE clause of a JOIN query takes AND-separated comparisons plus IN, NOT IN, LIKE, BETWEEN and IS [NOT] NULL; OR, NOT and parentheses are rejected with an error. A single-table WHERE has no such restriction - The select list of a non-aggregate JOIN query takes column names, * and table.* only; an expression or an AS alias is rejected. An aggregate JOIN query does accept AS - Up to 1.08 all of the JOIN restrictions above were silent: the query returned a Cartesian product or an unfiltered result instead of an error. 1.09 answers correctly where it can and reports an error where it cannot, so code written against 1.08 may now raise an error - UNION, UNION ALL, INTERSECT and EXCEPT match their branches by column position and take the result column names from the first branch. Up to 1.08 they matched by column name, so a branch whose names differed from the first branch's contributed rows of NULL - connect() with AutoCommit => 0 is refused; there are no transactions to turn AutoCommit off for. $dbh->{AutoCommit} is a plain hash key that can be assigned to, but only the AutoCommit method is authoritative - fetchall_arrayref accepts DBI's $max_rows argument and ignores it; use LIMIT in the SQL instead - Database, table and index names must be word characters ([A-Za-z0-9_]); they become path components, so a name holding / \ : or .. is rejected with "Invalid name" rather than being turned into a path - $DB::Handy::errstr keeps the last error from any handle and is not cleared by a later success; test $dbh->errstr, or the return value of the method, instead - PRIMARY KEY and UNIQUE are enforced through an index built by CREATE TABLE, so a table created by 1.08 or earlier still accepts duplicates until CREATE UNIQUE INDEX is run on it - ORDER BY by select-list position is not available with SELECT * across a JOIN; name the column instead - Whitespace between SQL tokens is collapsed, but whitespace inside a quoted value is kept, so multi-line text can be stored as-is - INT is a 4-byte signed integer; a numeric value outside -2147483648..2147483647 is rejected on INSERT/UPDATE, a fractional value is truncated, and a non-numeric value is stored as 0 - DATE must be a valid YYYY-MM-DD calendar date; anything else is rejected on INSERT/UPDATE. No date arithmetic is provided - NOT IN with NULL in the value list returns no rows (SQL UNKNOWN semantics) - NULL is stored as the empty string; in an INT or FLOAT column it is stored as 0 and cannot be told apart from a real 0 - SUM/AVG/MIN/MAX return 0 rather than NULL over an empty set - LIKE is case-insensitive - An unknown column name in a SELECT list yields NULL, not an error - Declared column sizes count bytes, not characters; a VARCHAR(10) holds three UTF-8 Japanese characters, not ten - A trailing NUL byte in a value is stripped when the record is read back - An unterminated /* comments out the rest of the statement rather than raising a syntax error - fetchall_arrayref ignores a column-index slice such as [0, 2] and always returns every column - FLOAT values in the .dat file use the machine's native double, so a data file holding FLOAT columns is not portable between machines with a different byte order (INT, CHAR, VARCHAR and DATE are portable) - WINDOW functions (OVER clause) return type='error' - FOREIGN KEY syntax accepted but not enforced; CREATE VIEW returns error - No BLOB/CLOB - No strict SQL compliance AUTHOR 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.