view release on metacpan or  search on metacpan

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

SQLite 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:SQLite: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 SQLite 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 SQLite 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<https://www.sqlite.org/uri.html>)
as well for finer control:

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

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

=head2 Read-Only Database

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

  use DBD::SQLite::Constants qw/:file_open/;
  my $dbh = DBI->connect("dbi:SQLite:$dbfile", undef, undef, {
    sqlite_open_flags => SQLITE_OPEN_READONLY,
  });

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

As of 1.49_05, you can also make a database read-only by setting
C<ReadOnly> attribute to true (only) when you connect to a database.
Actually you can set it after you connect, but in that case, it
can't make the database read-only, and you'll see a warning (which
you can hide by turning C<PrintWarn> off).

=head2 DBD::SQLite And File::Temp

When you use L<File::Temp> to create a temporary file/directory for
SQLite 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::SQLite, 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::SQLite and fork()

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

=over 4

Under Unix, you should not carry an open SQLite 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 SQLite 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:SQLite:foo.db

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

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

=head2 Blobs

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