view release on metacpan or  search on metacpan

lib/DBD/SQLcipher.pm  view on Meta::CPAN

on how to use DBI itself. The API works like every DBI module does.
However, currently many statement attributes are not implemented or
are limited by the typeless nature of the SQLcipher database.

=head1 NOTABLE DIFFERENCES FROM OTHER DRIVERS

=head2 Database Name Is A File Name

SQLcipher creates a file per a database. You should pass the C<path> of
the database file (with or without a parent directory) in the DBI
connection string (as a database C<name>):

  my $dbh = DBI->connect("dbi:SQLcipher:dbname=$dbfile","","");

The file is opened in read/write mode, and will be created if
it does not exist yet.

Although the database is stored in a single file, the directory
containing the database file must be writable by SQLcipher because the
library will create several temporary files there.

If the filename C<$dbfile> is ":memory:", then a private, temporary
in-memory database is created for the connection. This in-memory
database will vanish when the database connection is closed.
It is handy for your library tests.

Note that future versions of SQLcipher might make use of additional
special filenames that begin with the ":" character. It is recommended
that when a database filename actually does begin with a ":" character
you should prefix the filename with a pathname such as "./" to avoid
ambiguity.

If the filename C<$dbfile> is an empty string, then a private,
temporary on-disk database will be created. This private database will
be automatically deleted as soon as the database connection is closed.

As of 1.41_01, you can pass URI filename (see L<http://www.sqlite.org/uri.html>)
as well for finer control:

  my $dbh = DBI->connect("dbi:SQLcipher:uri=file:$path_to_dbfile?mode=rwc");

Note that this is not for remote SQLcipher database connection. You only can
connect to a local database.

You can also set sqlite_open_flags (only) when you connect to a database:

  use DBD::SQLcipher;
  my $dbh = DBI->connect("dbi:SQLcipher:$dbfile", undef, undef, {
    sqlite_open_flags => DBD::SQLcipher::OPEN_READONLY,
  });

See L<http://www.sqlite.org/c3ref/open.html> for details.

=head2 DBD::SQLcipher And File::Temp

When you use L<File::Temp> to create a temporary file/directory for
SQLcipher databases, you need to remember:

=over 4

=item tempfile may be locked exclusively

You may want to use C<tempfile()> to create a temporary database
filename for DBD::SQLcipher, but as noted in L<File::Temp>'s POD,
this file may have an exclusive lock under some operating systems
(notably Mac OSX), and result in a "database is locked" error.
To avoid this, set EXLOCK option to false when you call tempfile().

  ($fh, $filename) = tempfile($template, EXLOCK => 0);

=item CLEANUP may not work unless a database is disconnected

When you set CLEANUP option to true when you create a temporary
directory with C<tempdir()> or C<newdir()>, you may have to
disconnect databases explicitly before the temporary directory
is gone (notably under MS Windows).

=back

(The above is quoted from the pod of File::Temp.)

If you don't need to keep or share a temporary database,
use ":memory:" database instead. It's much handier and cleaner
for ordinary testing.

=head2 DBD::SQLcipher and fork()

Follow the advice in the SQLcipher FAQ (L<https://sqlite.org/faq.html>).

=over 4

Under Unix, you should not carry an open SQLcipher database across
a fork() system call into the child process. Problems will result
if you do.

=back

You shouldn't (re)use a database handle you created (probably to
set up a database schema etc) before you fork(). Otherwise, you
might see a database corruption in the worst case.

If you need to fork(), (re)open a database after you fork().
You might also want to tweak C<sqlite_busy_timeout> and
C<sqlite_use_immediate_transaction> (see below), depending
on your needs.

If you need a higher level of concurrency than SQLcipher supports,
consider using other client/server database engines.

=head2 Accessing A Database With Other Tools

To access the database from the command line, try using C<dbish>
which comes with the L<DBI::Shell> module. Just type:

  dbish dbi:SQLcipher:foo.db

On the command line to access the file F<foo.db>.

Alternatively you can install SQLcipher from the link above without
conflicting with B<DBD::SQLcipher> and use the supplied C<sqlite3>
command line tool.

=head2 Blobs

As of version 1.11, blobs should "just work" in SQLcipher as text columns.
However this will cause the data to be treated as a string, so SQL