view release on metacpan or  search on metacpan

README  view on Meta::CPAN

NOTABLE DIFFERENCES FROM OTHER DRIVERS
 Database Name Is A File Name
  SQLite creates a file per a database. You should pass the "path"
  of the database file (with or without a parent directory) in the
  DBI connection string (as a database "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 $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 $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
  <http://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.

 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 <http://www.sqlite.org/c3ref/open.html> for details.

  As of 1.49_05, you can also make a database read-only by setting
  "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 "PrintWarn" off).

 DBD::SQLite And File::Temp
  When you use File::Temp to create a temporary file/directory for
  SQLite databases, you need to remember:

  tempfile may be locked exclusively
      You may want to use "tempfile()" to create a temporary
      database filename for DBD::SQLite, but as noted in
      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);

  CLEANUP may not work unless a database is disconnected
      When you set CLEANUP option to true when you create a
      temporary directory with "tempdir()" or "newdir()", you may
      have to disconnect databases explicitly before the temporary
      directory is gone (notably under MS Windows).

  (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.

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

      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.

  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 "sqlite_busy_timeout" and
  "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.

 Accessing A Database With Other Tools
  To access the database from the command line, try using "dbish"
  which comes with the DBI::Shell module. Just type:

    dbish dbi:SQLite:foo.db

  On the command line to access the file foo.db.

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

 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 statements such as length(x) will return the length
  of the column as a NUL terminated string, rather than the size of
  the blob in bytes. In order to store natively as a BLOB use the
  following code:

    use DBI qw(:sql_types);
    my $dbh = DBI->connect("dbi:SQLite:dbfile","","");
  
    my $blob = `cat foo.jpg`;