view release on metacpan or  search on metacpan

lib/DB/Handy.pm  view on Meta::CPAN

  # $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";
      }
  }

=head1 TABLE OF CONTENTS

=over 4

=item * L</DESCRIPTION>

=item * L</INCLUDED DOCUMENTATION> -- eg/ samples and doc/ cheat sheets

=item * L</DBI COMPATIBILITY> -- What is and is not compatible with DBI

=item * L</METHODS - Connection handle (DB::Handy::Connection)>

=item * L</METHODS - Statement handle (DB::Handy::Statement)>

=item * L</ATTRIBUTES> -- Handle attributes such as RaiseError and NAME

=item * L</METHODS - Low-level API>

=item * L</SUPPORTED SQL> -- Full SQL syntax reference

=item * L</DATA TYPES>

=item * L</CONSTRAINTS>

=item * L</INDEXES>

=item * L</FILE LAYOUT>

=item * L</EXAMPLES> -- Practical usage patterns

=item * L</DIFFERENCES FROM DBI> -- Detailed incompatibility list

=item * L</DIAGNOSTICS> -- Error messages

=item * L</BUGS AND LIMITATIONS>

=item * L</SEE ALSO>

=back

=head1 DESCRIPTION

DB::Handy is a self-contained, pure-Perl relational database engine that
stores data in fixed-length binary flat files.  It requires B<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:

=over 4

=item * B<Zero dependencies> - only core Perl modules (Fcntl, File::Path,
File::Spec, POSIX).

=item * B<DBI-like interface> - C<connect>, C<prepare>, C<execute>,
C<fetchrow_hashref>, etc.  This allows code written for DB::Handy to
be easily adapted to DBI. B<This is the recommended interface.>
B<Note:> DB::Handy is B<not> a DBI driver and does not load the
L<DBI> module.  See L</"DBI COMPATIBILITY">.

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

=item * B<SQL support> - SELECT with JOIN, subqueries, UNION/INTERSECT/EXCEPT,
GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET, aggregates, CASE expressions,
and more.  C<IN (...)>, C<NOT IN (...)>, and pure C<OR> expressions on
indexed columns use index lookups.

=item * B<File locking> - shared/exclusive C<flock> on data files for safe
concurrent access from multiple processes.

=item * B<Portable> - works on Windows and UNIX/Linux without modification.

=back

=head1 INCLUDED DOCUMENTATION

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

=head1 DBI COMPATIBILITY

DB::Handy intentionally mirrors the L<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.

=head2 Compatible (works the same way as DBI)

=over 4

lib/DB/Handy.pm  view on Meta::CPAN

=item C<Cannot parse column def: E<lt>textE<gt>>

The C<CREATE TABLE> parser could not interpret a column definition.

=item C<Unsupported SQL: E<lt>sqlE<gt>>

The SQL string does not match any known pattern.

=item C<Database 'E<lt>nameE<gt>' already exists>

C<create_database> was called for a database directory that already exists.

=item C<Database 'E<lt>nameE<gt>' does not exist>

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

=item C<Cannot open base_dir: E<lt>reasonE<gt>>

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

=item C<Cannot open dat 'E<lt>fileE<gt>': E<lt>reasonE<gt>>

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

=item C<Cannot read schema: E<lt>reasonE<gt>>

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

=item C<Cannot create base_dir: E<lt>reasonE<gt>>

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

=item C<Cannot create database 'E<lt>nameE<gt>': E<lt>reasonE<gt>>

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

=item C<Cannot drop database 'E<lt>nameE<gt>': E<lt>reasonE<gt>>

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

=item C<DB::Handy connect failed: E<lt>messageE<gt>>

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

=item C<DB::Handy: E<lt>messageE<gt>>

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

=back

=head1 BUGS AND LIMITATIONS

Please report any bugs or feature requests by e-mail to
E<lt>ina@cpan.orgE<gt>.

When reporting a bug, please include:

=over 4

=item *

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

=item *

The version of DB::Handy:

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

=item *

Your Perl version:

  perl -V

=item *

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

=back

Known limitations:

=over 4

=item *

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

=item *

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

=item *

B<No FOREIGN KEY constraints or VIEW support.>

=item *

B<No WINDOW functions> (ROW_NUMBER, RANK, LEAD, LAG, etc.).

=item *

B<No BLOB/CLOB> large-object types.