/ 
DB-Handy-1.09

Changes for version 1.09 - 2026-07-31

  • SQL engine -- JOIN. Every item below returned a wrong answer in silence up to 1.08; the parser matched only "ON col = col" as an optional group, so any ON it could not read simply did not participate and join_select fell through to its Cartesian-product branch. Nothing was reported, and the caller got a plausible-looking result set that answered a different question.
  • Fixed: ON with the operands the other way round ("ON right.col = left.col") is now the same join as "ON left.col = right.col". It used to produce a Cartesian product.
  • Fixed: an ON clause the engine cannot execute -- a second condition, a comparison other than =, an unqualified column name, an unknown alias, or one that never names the table being joined -- is now an error naming the offending text.
  • Fixed: JOIN ... USING, NATURAL JOIN and FULL OUTER JOIN are now errors. They used to give a Cartesian product, a result with the right-hand columns missing, and a result with the columns shifted.
  • Fixed: an INNER, LEFT or RIGHT JOIN written without an ON clause is now an error. CROSS JOIN still needs none.
  • Fixed: text left over in the FROM clause after the JOIN chain is now an error instead of being discarded.
  • Fixed: a WHERE condition the JOIN path cannot execute (OR, NOT, parentheses) is now an error. It used to be dropped, so the query returned the whole join unfiltered.
  • Added: IS NULL and IS NOT NULL in the WHERE clause of a JOIN query, which makes the LEFT JOIN anti-join idiom work.
  • Added: BETWEEN in the WHERE clause of a JOIN query. Its AND is no longer mistaken for a conjunction when the conditions are split.
  • Fixed: SELECT DISTINCT over a JOIN now deduplicates. DISTINCT used to stay glued to the first select item, which made the item unresolvable and returned rows with no values in them. DISTINCT is applied before OFFSET and LIMIT, as SQL requires.
  • Added: a multi-key ORDER BY over a JOIN, in both the plain and the aggregate branch. Only a single key was matched before, so "ORDER BY a, b DESC" was swallowed by the WHERE strip and dropped.
  • Fixed: an ORDER BY key that resolves to nothing is now an error. A bare column name is resolved against the joined tables first.
  • Fixed: a select item that is not a column, '*' or 'table.*' is now an error in a non-aggregate JOIN query. An expression or an AS alias used to return rows with no values in them. An aggregate JOIN query still accepts AS.
  • Fixed: an aggregate query over a JOIN now keeps the select-list column order. Its rows are keyed by the bare column name, so a qualified select item failed the name check and the whole list fell back to alphabetical order: "SELECT a.y, COUNT(*)" reported NAME as ['COUNT(*)', 'y'].
  • SQL engine -- set operations.
  • Fixed: UNION, UNION ALL, INTERSECT and EXCEPT now line their branches up by column position and take the result column names from the first branch, as SQL requires. They used to be matched by column name, so "SELECT a FROM t1 UNION SELECT b FROM t2" returned NULL for every row contributed by the second branch.
  • Added: branches that disagree on the number of columns are now an error.
  • DBI layer.
  • Fixed: connect() with AutoCommit => 0 is now refused with an explanatory errstr. It used to be accepted, leaving DBI code that opens a transaction that way believing its writes were batched.
  • Documentation.
  • Fixed: the AutoCommit attribute was documented as read-only. The handle is an ordinary hash reference, so assigning to the key stores the value and it reads back; only the AutoCommit method is authoritative. The POD now says so.
  • Fixed: four entries under DIFFERENCES FROM DBI ended with "Compatible with DBI.", which contradicted the section title. The last_insert_id entry was one of them and is not compatible: it returns a row count, not a generated key.
  • Added: =head2 entries for ping, AutoCommit, begin_work, commit, rollback, table_info and column_info, which had no reference entry at all. column_info takes the table name as its only argument, not DBI's four; that is now stated.
  • Added: DIAGNOSTICS entries for the value-too-long, integer-range, invalid-DATE and CHECK-constraint messages, for every new JOIN and set-operation message, and for the AutoCommit refusal.
  • Added: a statement of what a JOIN accepts, under EXAMPLES, and an anti-join example; the JOIN restrictions and the set-operation change under BUGS AND LIMITATIONS; and a note that the $max_rows argument of fetchall_arrayref is accepted and ignored.
  • Fixed: the SELECT grammar now shows the ON clause as a single column equality, and CROSS JOIN as taking no ON.
  • Tests.
  • Added: t/1023_join_negative.t (59 tests). Every construct listed above is pinned to one of two outcomes, the right answer or an error, so none of them can go back to returning something. t/1003_join.t only ever exercised a single "ON left.col = right.col" in that operand order, which is why this class of defect survived 1877 assertions.
  • Shared infrastructure:
  • t/lib/INA_CPAN_Check.pm: replaced with the shared ina@CPAN test library (0.41) used by the other distributions. It is now an Exporter-based module, adds category L (Changes file format), renames the file helper _find_pm to _find_pm_t, and adds the _code_only, _text_files, _primary_pm and _lib_pm_files helpers.
  • t/lib/INA_CPAN_Check.pm: the TAP harness now guards against a duplicate plan line and against a plan count that does not match the number of ok() calls, and sets the exit status from its own END block instead of calling exit().
  • pmake.bat: replaced with the shared build tool (0.43), which adds the selfcheck target and records its version in generated_by.
  • pmake.bat: ExtUtils::MakeMaker is no longer emitted as a runtime prerequisite. It used to be pushed into the same %requires hash that feeds PREREQ_PM, the META.yml requires block and all four prereq phases of META.json, so every distribution declared the build toolchain as something its users had to have installed at run time, which CPANTS marks down. It now lives in its own %configure_requires hash, which produces a configure_requires block in META.yml, a prereqs/configure/requires block in META.json, and a CONFIGURE_REQUIRES argument in Makefile.PL (guarded by ExtUtils::MakeMaker 6.52, which introduced it).
  • pmake.bat: the repository and bugtracker resources are no longer emitted into Makefile.PL, META.yml or META.json. The generated per-distribution https://github.com/ina-cpan/<Dist> URL was never verified and returned 404 for this distribution, and no CPANTS core Kwalitee indicator looks at either resource: the only indicator that mentions one is meta_yml_has_repository_resource, which is experimental and excluded from the score and the ranking. Removing them keeps the score at 100% for every distribution whether or not a public repository exists. The license resource is unchanged, and bug reports are directed to the author's address by the POD.
  • pmake.bat: a CR is stripped from every element of @ARGV. This file is a cmd.exe / sh / Perl polyglot whose preamble must use CRLF for cmd.exe, and sh passes the CR of its last line through to the last argument, so on Unix "./pmake.bat ptar" arrived as "ptar\r" and was reported as an unknown target -- which is exactly what the untar test at the end of "dist" hit. Option flags were broken the same way. "pmake dist" now completes its untar test on Unix as well.
  • pmake.bat: an argument beginning with "--" is no longer dispatched as a target. The flags are read by the targets that understand them, so the documented "pmake dist --no-check1" also printed "unknown target: --no-check1." after doing the right thing.
  • pmake.bat: MANIFEST is read line by line with trailing whitespace trimmed and blank and comment lines skipped, as ExtUtils::Manifest does. A MANIFEST saved with CRLF and read on Unix used to leave a CR on every entry, so every file appeared to be missing and "pmake test" silently ran nothing. Trailing whitespace is trimmed rather than split off, so a file name containing a space survives. The same reading is used where "dist" takes the primary module name from the first MANIFEST line.
  • pmake.bat: _runtests() quotes the interpreter path before building the qx{} command, as INA_CPAN_Check::selfcheck_suite already does, so a perl installed under a directory with a space in its name works.
  • pmake.bat: _runtests() reports a test file that produced no TAP plan (a compile error, say) and one that is listed in MANIFEST but missing from the working directory. Both used to be counted as neither ok nor not ok, so the run was reported as a failure with nothing named in the summary, and a missing file also failed to advance the script index, which misaligned every later entry of the failed-test list against its file name. The plan line is now located anywhere in the output rather than on the first line only, and the "Failed m/n subtests" denominator is the planned count, as prove reports it.
  • pmake.bat: the install target derives the interpreter directory with dirname($^X) instead of a backslash-only pattern, which left it undefined on anything but Windows, and a top-level executable is now copied to that directory rather than into the library tree -- the message already named the bin directory, only the copy did not.
  • t/9030-distribution.t: adjusted to the new check_J() signature (the distribution-specific j2_stale option no longer exists; J2 is now the prerequisite-version clash check). Category H dropped because t/9060-readme.t covers README in more depth; category L added for the Changes file format.
  • t/9050-pod.t: new G13 takes over what the removed check_J j2_stale option used to do -- it fails if =head1 BUGS AND LIMITATIONS still lists INTERSECT/EXCEPT, index range scans, or INSERT-only CHECK as missing. Plan is now 13 tests per .pm file.
  • t/9001-load.t: extended to verify check_L and count_L.
  • t/9040-style.t: the k3_exempt override is gone; the shared default (env, opts, args) is now sufficient.
  • Bug fixes:
  • lib/DB/Handy.pm: _load_schema() no longer clobbers the caller's $_. Its "while (<FH>)" loop assigned to the global $_, so calling any method that loads a schema from inside a for/map/grep block either corrupted the caller's list ("for (@ids) { $dbh->do(...) }" wiped out the first element) or died outright with "Modification of a read-only value attempted" when the list held constants. The loop now runs under "local $_".
  • lib/DB/Handy.pm: LIMIT and OFFSET are no longer ignored on a SELECT that has no WHERE, ORDER BY or GROUP BY clause. parse_select() strips leading whitespace from the clause tail before matching "\s+LIMIT", so a query whose tail started with LIMIT never matched. Both patterns now allow the start of the string.
  • lib/DB/Handy.pm: a CHECK constraint no longer rejects a NULL value. SQL-92 requires a constraint that evaluates to UNKNOWN to be treated as satisfied, but eval_bool() returns false for an undefined operand, so "INSERT INTO t (id) VALUES (1)" was refused whenever any other column carried a CHECK. Both the INSERT and the UPDATE path now skip the check when the column is NULL. A value that really violates the constraint is still rejected.
  • lib/DB/Handy.pm: LIKE patterns now escape regular-expression metacharacters. The three separate implementations each did no more than "s/%/.*/g; s/_/./g", so "LIKE 'a.c'" also matched 'abc' and "LIKE 'C++'" matched nothing. All three now call the new _like_to_re() helper, which passes % and _ through as wildcards and quotemeta()s everything else.
  • lib/DB/Handy.pm: a doubled single quote inside a LIKE pattern is un-escaped, so "LIKE 'O''B%'" matches "O'Brien" as it already did for the = operator.
  • lib/DB/Handy.pm: COUNT(col) and COUNT(DISTINCT col) skip NULL values, as SQL-92 requires. COUNT(*) is unchanged.
  • lib/DB/Handy.pm: _col_order_from_sql() keeps the SELECT list order when the list holds three or more expressions with no AS alias. It used to give up and fall back to alphabetical order, so "SELECT COUNT(*), SUM(n), MIN(n)" reported NAME and fetchrow_arrayref in the order COUNT(*), MIN(n), SUM(n). The expression text is now used as the column name, which is the same key project() stores the value under; the existing verification step still falls back if the two disagree.
  • lib/DB/Handy.pm: storing NULL in a FLOAT column no longer produces an "Argument \"\" isn't numeric in addition (+)" warning under -w. _pack_record() and _encode_key() now coerce an empty value to 0 before pack('d'), the way the INT branch already did.
  • lib/DB/Handy.pm: fixed a typo in four whitespace-trimming substitutions: s/^\s+|\s+\$//g matched a literal '$' rather than the end of the string, so trailing whitespace was never removed from INSERT column lists, SELECT column lists, derived-table column lists and GROUP BY items.
  • Bug fixes (second round):
  • lib/DB/Handy.pm: SQL comments are removed before parsing. Neither "--" nor "/* ... */" was recognised anywhere, so a comment placed in or after the WHERE clause became part of the condition and the statement silently returned no rows instead of raising an error. The new _strip_sql_comments() runs at the head of execute() and leaves a comment marker inside a string literal alone; an unterminated "/*" comments out the rest of the statement.
  • lib/DB/Handy.pm: _idx_write_all() no longer truncates the index file before it holds the lock. It opened with '>', which empties the file at open() time, so a second writer destroyed the first writer's data while waiting for the exclusive lock. The file is now opened with '+<' and truncated after the lock is taken.
  • lib/DB/Handy.pm: _idx_read_all() takes a shared lock, and _rebuild_index() takes one on the .dat file. Both used to read without any lock, so either could read a half-written file.
  • lib/DB/Handy.pm: insert() keeps the .dat lock until every index has been updated. It used to unlock and close first, leaving a window in which another process could see the new record with no index entry for it. delete_rows() already did this correctly; the two paths now agree, and both take the .dat lock before the index lock, so the ordering cannot deadlock.
  • lib/DB/Handy.pm: a '?' inside a string literal is no longer treated as a bind placeholder. "UPDATE t SET s='x?y' WHERE id=?" consumed the bind value for the literal's '?' and matched no rows. The substitution now skips string literals, and comments are stripped before it runs.
  • lib/DB/Handy.pm: the write paths check what they write. print(), close() and rename() return values were discarded throughout, so a full disk or a read-only file produced a silent success. insert(), delete_rows(), update(), vacuum(), create_table(), create_index(), _idx_write_all() and _rewrite_schema() now return undef with errstr set. The read paths are unchanged.
  • lib/DB/Handy.pm: the VARCHAR length error said "chars" while counting bytes. It now says "bytes" on both the INSERT and the UPDATE path.
  • Bug fixes (third round):
  • lib/DB/Handy.pm: an INT value outside the range of the 4-byte field is rejected instead of being silently clamped. Storing 3000000000 used to succeed and leave 2147483647 in the column. A value with a fractional part is still truncated towards zero and a value that is not numeric at all is still stored as 0; neither of those loses information the caller did not already give away, so they remain accepted. Checked on both INSERT and UPDATE.
  • lib/DB/Handy.pm: a DATE value that is not a well-formed calendar date is rejected. '2020-13-99' and '2021-02-29' used to be stored verbatim. The format must be YYYY-MM-DD with the month in 01-12 and the day valid for that month and year; the four-hundred-year leap rule is applied, so 2000-02-29 is accepted and 1900-02-29 is not. NULL and the empty string are always accepted. Checked on both INSERT and UPDATE.
  • lib/DB/Handy.pm: create_table() calls binmode on the .dat file it creates. This was the only file handle in the module that reached a binary file without it. The handle is closed without writing anything, so no data file was ever affected.
  • lib/DB/Handy.pm: storing a non-numeric value in an INT column no longer produces an "Argument isn't numeric in int" warning under -w. _pack_record() and _encode_key() now test the value before calling int(), matching the guard added to the FLOAT branch earlier in this release. The stored value is unchanged (0).
  • Bug fixes (fourth round):
  • lib/DB/Handy.pm: whitespace inside a string literal is preserved. execute() normalised the statement with a blanket s/\s+/ /g, which also collapsed the contents of quoted values, so a newline, tab or carriage return stored through SQL silently became a single space. Multi-line text could not be stored at all. Normalisation now runs only outside literals, and the two INSERT dispatch patterns plus parse_set_exprs() gained /s so that a value spanning lines still parses. The low-level insert() API was never affected.
  • lib/DB/Handy.pm: an aggregate over a derived table is evaluated. "SELECT COUNT(*) FROM (SELECT ...) AS sub" returned one empty row per row of the derived table instead of a count, because the outer projection step only copied columns. The grouping and aggregation code has been factored out of the table-backed SELECT path into group_and_aggregate(), and the derived-table path now calls it. GROUP BY and HAVING on the outer query are supported at the same time, and ORDER BY / LIMIT / OFFSET are applied to the aggregated result rather than to its input.
  • lib/DB/Handy.pm: $dbh->{AutoCommit} reads as 1. The AutoCommit method already returned 1 but the attribute was undef, so DBI-style code testing $dbh->{AutoCommit} saw the wrong answer. DB::Handy still has no transactions; assigning to the attribute changes nothing.
  • Bug fixes (fifth round):
  • lib/DB/Handy.pm: a JOIN whose tables carry no alias no longer loses its join type and its ON condition. The optional alias slot in the FROM clause matched any word, so in "FROM a LEFT JOIN b ON ..." the keyword LEFT was taken as the alias of a -- the LEFT JOIN then parsed as a plain JOIN and every "a.col" reference became unresolvable -- and in "JOIN b ON ..." the keyword ON was taken as the alias of b, which dropped the join condition and produced a silent cross join. Both alias positions now reject the SQL keywords that can legally follow a table name. Queries that already aliased every table are unaffected.
  • lib/DB/Handy.pm: PRIMARY KEY is enforced. CREATE TABLE now builds a unique index named <column>_pk for it, so a duplicate key is rejected on INSERT and on UPDATE. Previously the column name was recorded in the schema and NOT NULL was applied, but nothing ever checked uniqueness, contrary to what the POD stated.
  • lib/DB/Handy.pm: the UNIQUE column modifier and the table-level UNIQUE (col) constraint are enforced. Both were parsed away and silently ignored; CREATE TABLE now builds a unique index named <column>_unique for them. A column that is both PRIMARY KEY and UNIQUE gets one index, not two.
  • lib/DB/Handy.pm: a UNIQUE column accepts any number of NULLs, as SQL-92 requires and as the POD already promised. The check now skips an empty value instead of treating the empty string as an ordinary duplicate.
  • lib/DB/Handy.pm: the UNIQUE check runs after DEFAULT has been applied, so the value compared is the value actually stored.
  • lib/DB/Handy.pm: $sth->execute() rejects a bind list whose length does not match $sth->{NUM_OF_PARAMS}. A missing value used to leave a bare "?" in the statement, which then matched nothing, and a surplus value was dropped, so either mistake returned a silently wrong result set instead of an error.
  • lib/DB/Handy.pm: err and errstr are cleared when a statement is prepared or executed, as DBI does. A message left over from an earlier failure could otherwise be read back after a call that had just succeeded.
  • lib/DB/Handy.pm: ORDER BY by select-list position works. A sort key written as a plain number was compared as a constant, so the rows came back unsorted with no error at all. Positions now resolve against the select list on a single table, on SELECT * (using CREATE TABLE order), with GROUP BY, in a JOIN and on a derived table; a position outside the select list is reported as an error. The one case that cannot be resolved, SELECT * across a JOIN, is reported rather than ignored.
  • lib/DB/Handy.pm: ORDER BY on a derived table no longer warns about an undefined value under -w when the sort key has no alias prefix.
  • Bug fixes (sixth round):
  • lib/DB/Handy.pm: insert(), delete_rows(), update() and _idx_write_all() get their data onto disk before the exclusive lock is released. print() buffers, and the buffer was written out by close(), which runs after flock(LOCK_UN), so a second process could take the lock and read the file while the writer's record was still sitting in the buffer. The handle is now put into autoflush mode right after open() by the new _autoflush() helper, which uses the select() idiom rather than IO::Handle so that it works on 5.005_03. No corruption was observed in practice, because close() follows immediately; the change removes the window rather than a known failure.
  • lib/DB/Handy.pm: vacuum() closes the replacement file before it unlocks the source file, so .dat.tmp is complete on disk while the exclusive lock is still held. Both the normal and the write-error path were reordered.
  • lib/DB/Handy.pm: _idx_write_all() assembles the whole index image in memory and writes it with one print() instead of one print() per entry, so autoflush costs a single write() per index rebuild.
  • Bug fixes (seventh round):
  • lib/DB/Handy.pm: database, table and index names given to the low-level API are now checked against \w+ before they are used as path components. Without the check a name coming from outside the program could walk out of base_dir: drop_database('../victim') handed '../victim' straight to File::Path::rmtree() and deleted a directory tree that had nothing to do with the database, and create_database('../x') or create_database('a/b') created directories outside it. The guards are in new(), create_database(), use_database(), drop_database(), create_table(), drop_table(), create_index(), drop_index() and _load_schema(), the last of which covers describe_table(), list_indexes(), insert(), delete_rows() and vacuum() as well. A rejected name sets errstr to "Invalid database name '<name>'", "Invalid table name '<name>'" or "Invalid index name '<name>'". The SQL layer has always matched identifiers with \w+ and is unaffected, so no SQL statement that worked before is rejected now.
  • eg/db_dump.pl: the schema is scanned for /^recsize=(\d+)$/, but the engine writes the key in upper case (RECSIZE=264), so the script always died with "Could not find recsize" and had never produced a dump. The match is now case-insensitive.
  • lib/DB/Handy.pm: DB::Handy::Connection::$VERSION and DB::Handy::Statement::$VERSION were assigned from $DB::Handy::VERSION. That is invisible to a static parser, so Module::Metadata -- and therefore PAUSE, when it cannot fall back on the provides field -- read both as 0. Both are now literals.
  • New features:
  • lib/DB/Handy.pm: $sth->{NAME_lc} and $sth->{NAME_uc} carry the NAME list case-folded.
  • lib/DB/Handy.pm: $sth->{NUM_OF_PARAMS} reports the number of ? placeholders and $sth->{Statement} the SQL as prepared. Both are available as soon as prepare() returns. A '?' inside a string literal or a comment is not counted, matching what execute() does.
  • Incompatible changes:
  • An INSERT or UPDATE that supplies an out-of-range INT, or a DATE that is not a valid calendar date, now fails with errstr set where it used to succeed with a silently altered value. Existing rows are not re-validated on read, so a data file written by 1.08 or earlier keeps whatever it already holds.
  • COUNT(col) and COUNT(DISTINCT col) return a smaller number than before when the column holds NULL values. This is the SQL-92 result; code that relied on the old count must switch to COUNT(*).
  • fetchrow_arrayref(), fetchrow_array(), fetchall_arrayref(), selectall_arrayref(), selectrow_arrayref() and $sth->{NAME} report a different column order for a SELECT list of three or more unaliased expressions. The new order is the order written in the SQL. Code that depended on the old alphabetical order should use fetchrow_hashref() or add explicit AS aliases.
  • A LIKE pattern containing a regular-expression metacharacter now matches that character literally. Code that (knowingly or not) relied on '.' or '+' behaving as a regular expression must be rewritten to use the SQL wildcards % and _.
  • CREATE TABLE with a PRIMARY KEY or a UNIQUE column now creates a unique index for it, so an INSERT or UPDATE that duplicates such a value fails where it used to succeed. The index is part of the table, so a table created by 1.08 or earlier keeps the old behaviour until CREATE UNIQUE INDEX is run on it; the .sch and .dat formats are otherwise unchanged and remain readable.
  • $sth->execute() with the wrong number of bind values now returns undef with errstr set instead of running a statement with an unsubstituted placeholder or a dropped value.
  • ORDER BY <number> is now a select-list position rather than a constant. A query that passed a literal number expecting it to be ignored will now sort, or fail if the position does not exist.
  • Tests:
  • t/1022_names.t: new, 33 assertions covering the identifier check. The central case builds a victim directory next to base_dir, puts a file in it, and asserts that drop_database('../victim') is refused and the file survives. The rest covers every guarded method, the three errstr messages, that ordinary names with digits, underscores and mixed case still work end to end, and that the SQL layer is unchanged.
  • t/1001_dbms.t .. t/1016_unsupported.t: the scratch directory was hard-coded as /tmp/<name>_$$. On Windows that resolves to the root of the current drive (C:\tmp), which a standard user account may not be able to write to, and everywhere it ignored TMPDIR/TEMP and so wrote outside a smoker's working area. All sixteen now build the path with File::Spec->catdir(File::Spec->tmpdir, ...), matching t/1017 onwards.
  • t/1001_dbms.t .. t/1016_unsupported.t: the plan line was a hard-coded "1..N" that had to be recounted by hand whenever an assertion was added or removed. ok()/is() now buffer their output and the plan is printed from the number of assertions that actually ran, so it cannot drift. The plan still comes first in the stream, which is what the Test::Harness shipped with 5.005_03 expects. If the body dies before the plan is emitted, an END block flushes what did run and appends one failing assertion, so the harness gets a complete and definitely-failing stream instead of no plan at all.
  • t/1001_dbms.t .. t/1021_integrity.t: the scratch directory was removed by a statement at the end of the file, which never ran if the script died part way through. All twenty-one now remove it from an END block instead.
  • t/1021_integrity.t: new, 46 assertions covering PRIMARY KEY and UNIQUE enforcement and their index names, NULL exemption from UNIQUE, JOIN parsing without table aliases (INNER, LEFT, LEFT OUTER), the execute() bind-count check, err/errstr clearing, and ORDER BY by position across the single-table, SELECT *, GROUP BY, JOIN and derived-table paths.
  • t/1008_constraints.t: the PRIMARY KEY block asserted the old behaviour (a duplicate key being accepted) and now asserts that it is rejected and that the index id_pk was created.
  • t/1020_sql_layer.t: new, 31 assertions covering whitespace preservation inside literals across INSERT/SELECT/WHERE/LIKE/ UPDATE/DELETE and index lookups, statement layout normalisation outside literals, aggregates over derived tables, GROUP BY / HAVING / ORDER BY / LIMIT on a derived table, and the AutoCommit attribute.
  • t/1019_types.t: new, 37 assertions covering the INT range check, the values that stay accepted, DATE validity including the leap year rules, NULL handling, the same checks on UPDATE, and byte transparency of the .dat file.
  • t/1018_hardening.t: new, 33 assertions covering the second-round fixes: comment stripping and literal protection, index integrity across INSERT/UPDATE/DELETE/vacuum, the new statement attributes, placeholder handling, the documented fetchall_arrayref slice behaviour, and I/O failure reporting on the write paths. The last group is skipped when chmod does not stop the test process from writing (running as root, or a file system without it).
  • t/1017_regression.t: new, 29 assertions covering each of the eight fixes above. Its plan count is derived from the list of test closures rather than hard-coded, and the runner turns a die inside a closure into a single "not ok" so that one crashing case does not truncate the report.
  • Code:
  • lib/DB/Handy.pm: _load_schema() builds the schema as a hash reference from the start instead of taking \%sch of a named hash. Behaviour is unchanged -- the cached entry in $self->{_tables} and the returned value are still the same reference -- but the house style rule "use { %hash } instead of \%hash" now holds with no per-distribution exemption, which 'pmake dist' requires.
  • t/9080-cheatsheets.t: substr($raw,$i,1) respaced to substr($raw, $i, 1) (K1: a comma is followed by whitespace).
  • lib/DB/Handy.pm: the Fcntl import is now qw(:flock). The :DEFAULT tag brought in the O_* constants, none of which is used -- every open() in the module is the two-argument mode-string form.
  • Documentation:
  • lib/DB/Handy.pm: DIAGNOSTICS gains the three "Invalid ... name" messages, and METHODS - Low-level API opens with the identifier rule and the reason it is there.
  • lib/DB/Handy.pm, README: DIAGNOSTICS said that the error variables are "set on every failed operation and cleared on success". That holds for $dbh->errstr and $sth->errstr but not for the package-level $DB::Handy::errstr, which is only ever overwritten by the next error and so still holds a stale message after a success. Both now say so and point at the handle accessor instead.
  • eg/crud_sample.pl: the script leaves ./sample_db behind on purpose, so that eg/db_dump.pl has something to read, but said nothing about it. The header comment now explains this and the closing message gives the db_dump.pl command line and the one-liner that removes the directory.
  • Contact address changed from ina@cpan.org to ina.cpan@gmail.com throughout lib/DB/Handy.pm, README, Makefile.PL, META.yml, META.json and SECURITY.md.
  • lib/DB/Handy.pm: POD =head1 VERSION corrected from "Version 1.07" to "Version 1.09" (was not updated at the 1.08 release).
  • lib/DB/Handy.pm: the Subqueries section documents what the outer query of a derived table accepts, including aggregates and the order in which LIMIT is applied. The feature list notes that whitespace inside a quoted value is preserved. =head1 ATTRIBUTES documents AutoCommit, which is no longer listed as unimplemented.
  • lib/DB/Handy.pm: =head1 DATA TYPES describes the INT range check and the DATE validity rules, and no longer claims that no date validation is performed. The FLOAT entry now distinguishes the order-preserving encoding used for index keys from the native double written to the .dat file.
  • lib/DB/Handy.pm: corrected seven POD passages that described the array-ref column order as alphabetical. It is the SELECT list order for a named column list and the CREATE TABLE declaration order for SELECT * (qualified, table by table, for a JOIN); the =head2 NAME entry already said so, so the POD contradicted itself.
  • lib/DB/Handy.pm: fetchall_arrayref documents that a column-index slice such as [0, 2] is ignored and every column is returned.
  • lib/DB/Handy.pm: DBI COMPATIBILITY and =head1 ATTRIBUTES list the new NAME_lc / NAME_uc / NUM_OF_PARAMS / Statement attributes, and Statement is no longer listed as unimplemented.
  • lib/DB/Handy.pm: =head1 BUGS AND LIMITATIONS gains three entries: declared column sizes count bytes rather than 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; and an unterminated /* comments out the rest of the statement instead of raising a syntax error.
  • lib/DB/Handy.pm: =head1 BUGS AND LIMITATIONS documents five behaviours that were previously undocumented: NULL is stored as the empty string and is indistinguishable from 0 in an INT or FLOAT column; SUM/AVG/MIN/MAX return 0 rather than NULL over an empty set; LIKE is case-insensitive; an unknown column name yields NULL instead of an error; and 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 different byte order. The FLOAT-on-disk format is deliberately left unchanged so that data files written by 1.08 and earlier stay readable.
  • lib/DB/Handy.pm: the File locking bullet in =head1 DESCRIPTION no longer calls concurrent access "safe". It now says that a lock is held for the duration of every read and write, that this serialises access on a local file system, and that the return value of flock() is not checked, with a link to =head1 BUGS AND LIMITATIONS.
  • lib/DB/Handy.pm: =head1 BUGS AND LIMITATIONS gains an entry for the unchecked flock() return value: where locking is unavailable (NFS with no lock daemon, some network shares) the call fails silently and the access proceeds unlocked, so a single writer should be assumed there. The failure is ignored deliberately, since raising an error would make the module unusable on those file systems.
  • lib/DB/Handy.pm: the "Value too long" error now reads "declared VARCHAR(5), got 10 bytes" instead of "declared VARCHAR(5) bytes, got 10 bytes", which stated the unit twice, and it names the type the column was actually declared with. The check covers CHAR as well as VARCHAR, but the message said VARCHAR for both, so a CHAR(3) violation was reported as "declared VARCHAR(3)".
  • lib/DB/Handy.pm: FLOAT now applies the same non-numeric test that INT has applied since earlier in this release, and applies it on both the record path (_pack_record) and the index key path (_encode_key). Up to 1.08 the FLOAT branches handed the raw value to pack(), so storing a value that was not a number leaked an "isn't numeric" warning out of the module -- twice per row when the column was indexed, and once more when such a column was compared in a WHERE clause. The module sets $^W itself, so the warning appeared whatever the caller's own warning settings were. The two types also disagreed about what a value like '12abc' meant: INT failed the test and stored 0, while FLOAT let Perl stop at the first non-digit and stored 12. Both store 0 now. Values that really are numbers are unaffected: '1e3', ' 42 ' and -2.5 still round trip, and an index lookup on a FLOAT column still works.
  • lib/DB/Handy.pm: the numeric test itself was written out three times (index key encoding, type validation, record packing) and is now the single function _looks_numeric(). Three copies of one regular expression is how the FLOAT paths came to have no copy at all, and it is the same trap that produced three copies of the LIKE implementation.
  • lib/DB/Handy.pm: =head1 DATA TYPES documents the FLOAT rule. The INT entry said what happens to a value that is not numeric; the FLOAT entry said nothing.
  • t/1019_types.t: new group T7 covers the FLOAT rule -- no warning on insert, on an indexed insert, on update or on comparison; the value stored is 0; INT and FLOAT agree about '12abc'; genuine numbers still round trip; and both index lookups still find their rows. Seven of the fourteen new assertions fail against 1.09 as first built.
  • Documentation:
  • lib/DB/Handy.pm: =head1 BUGS AND LIMITATIONS records that a single-table WHERE clause is not syntax-checked. The JOIN parser added in this release rejects a condition it cannot read, but the single-table parser still treats one as matching nothing, so "WHERE x = 1 GARBAGE", "WHERE x ==== 1", "WHERE x = 1 AND" and "WHERE (x = 1" all return zero rows rather than reporting a syntax error. DELETE and UPDATE fail safe -- an unreadable condition changes and removes nothing -- but a SELECT gives a plausible empty answer to a question it never asked. This is the same class of fault the JOIN work removed, left in place here because rejecting an unreadable single-table condition would change the result of existing queries; it is documented rather than silently carried.
  • Build tool:
  • pmake.bat 0.44: files in the generated archive are 0644, and .pl, .bat, .exe, .com and bin/* are 0755. 0664 and 0775 marked every file group-writable, which is not the CPAN convention.
  • pmake.bat 0.44: 'perl' is no longer written into PREREQ_PM in the generated Makefile.PL. MIN_PERL_VERSION already carries it, and older ExtUtils::MakeMaker reports a missing prerequisite named "perl" when it appears in both. The META files still list it under requires.
  • pmake.bat 0.44: the generated CONTRIBUTING is rewritten. The old text was a GitHub template with "pull request" replaced by "e-mail", which left it asking for "some small e-mail", praising "Good e-mail, patches", and warning about "unrelated commits" and work that would not be "merged" in a distribution that has no repository. The new text says how to report a bug and how to send a diff.
  • pmake.bat 0.44: the generated SECURITY.md no longer tells the reader not to open a GitHub issue, since no GitHub URL appears in the distribution's resources. It says instead that there is no public issue tracker and that private e-mail is the channel.
  • created by INABA Hitoshi

Documentation

SECURITY.md

Modules

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

Provides

DB::Handy::Connection
in lib/DB/Handy.pm
DB::Handy::Statement
in lib/DB/Handy.pm

Examples

Other files