view release on metacpan or  search on metacpan

README  view on Meta::CPAN

    See <http://www.sqlite.org/foreignkeys.html> for details.

  Pragma
    SQLcipher has a set of "Pragma"s to modifiy its operation or to query for
    its internal data. These are specific to SQLcipher and are not likely to
    work with other DBD libraries, but you may find some of these are quite
    useful. DBD::SQLcipher actually sets some (like "show_datatypes") for you
    when you connect to a database. See <http://www.sqlite.org/pragma.html>
    for details.

  Transactions
    DBI/DBD::SQLcipher's transactions may be a bit confusing. They behave
    differently according to the status of the "AutoCommit" flag:

    When the AutoCommit flag is on
        You're supposed to always use the auto-commit mode, except you
        explicitly begin a transaction, and when the transaction ended,
        you're supposed to go back to the auto-commit mode. To begin a
        transaction, call "begin_work" method, or issue a "BEGIN" statement.
        To end it, call "commit/rollback" methods, or issue the
        corresponding statements.

          $dbh->{AutoCommit} = 1;

  $dbh->begin_work; # or $dbh->do('BEGIN TRANSACTION');

  # $dbh->{AutoCommit} is turned off temporarily during a transaction;

  $dbh->commit; # or $dbh->do('COMMIT');

  # $dbh->{AutoCommit} is turned on again;

    When the AutoCommit flag is off
        You're supposed to always use the transactional mode, until you
        explicitly turn on the AutoCommit flag. You can explicitly issue a
        "BEGIN" statement (only when an actual transaction has not begun
        yet) but you're not allowed to call "begin_work" method (if you
        don't issue a "BEGIN", it will be issued internally). You can commit
        or roll it back freely. Another transaction will automatically
        begins if you execute another statement.

          $dbh->{AutoCommit} = 0;

  # $dbh->do('BEGIN TRANSACTION') is not necessary, but possible

  ...

  $dbh->commit; # or $dbh->do('COMMIT');

  # $dbh->{AutoCommit} stays intact;

  $dbh->{AutoCommit} = 1;  # ends the transactional mode

    This "AutoCommit" mode is independent from the autocommit mode of the
    internal SQLcipher library, which always begins by a "BEGIN" statement, and
    ends by a "COMMIT" or a <ROLLBACK>.

  Transaction and Database Locking
    Transaction by "AutoCommit" or "begin_work" is nice and handy, but
    sometimes you may get an annoying "database is locked" error. This
    typically happens when someone begins a transaction, and tries to write
    to a database while other person is reading from the database (in
    another transaction). You might be surprised but SQLcipher doesn't lock a
    database when you just begin a normal (deferred) transaction to maximize
    concurrency. It reserves a lock when you issue a statement to write, but
    until you actually try to write with a "commit" statement, it allows
    other people to read from the database. However, reading from the
    database also requires "shared lock", and that prevents to give you the
    "exclusive lock" you reserved, thus you get the "database is locked"
    error, and other people will get the same error if they try to write
    afterwards, as you still have a "pending" lock. "busy_timeout" doesn't
    help in this case.

    To avoid this, set a transaction type explicitly. You can issue a "begin
    immediate transaction" (or "begin exclusive transaction") for each
    transaction, or set "sqlite_use_immediate_transaction" database handle
    attribute to true (since 1.30_02) to always use an immediate transaction
    (even when you simply use "begin_work" or turn off the "AutoCommit".).

      my $dbh = DBI->connect("dbi:SQLcipher::memory:", "", "", {
        sqlite_use_immediate_transaction => 1,
      });

    Note that this works only when all of the connections use the same
    (non-deferred) transaction. See <http://sqlite.org/lockingv3.html> for
    locking details.

  "$sth->finish" and Transaction Rollback
    As the DBI doc says, you almost certainly do not need to call "finish"
    in DBI method if you fetch all rows (probably in a loop). However, there
    are several exceptions to this rule, and rolling-back of an unfinished
    "SELECT" statement is one of such exceptional cases.

    SQLcipher prohibits "ROLLBACK" of unfinished "SELECT" statements in a
    transaction (See <http://sqlite.org/lang_transaction.html> for details).
    So you need to call "finish" before you issue a rollback.

      $sth = $dbh->prepare("SELECT * FROM t");
      $dbh->begin_work;
      eval {
          $sth->execute;
          $row = $sth->fetch;
          ...
          die "For some reason";
          ...
      };
      if($@) {
         $sth->finish;  # You need this for SQLcipher
         $dbh->rollback;
      } else {
         $dbh->commit;
      }

  Processing Multiple Statements At A Time
    DBI's statement handle is not supposed to process multiple statements at
    a time. So if you pass a string that contains multiple statements (a
    "dump") to a statement handle (via "prepare" or "do"), DBD::SQLcipher only
    processes the first statement, and discards the rest.

    Since 1.30_01, you can retrieve those ignored (unprepared) statements
    via "$sth->{sqlite_unprepared_statements}". It usually contains nothing
    but white spaces, but if you really care, you can check this attribute
    to see if there's anything left undone. Also, if you set a
    "sqlite_allow_multiple_statements" attribute of a database handle to
    true when you connect to a database, "do" method automatically checks
    the "sqlite_unprepared_statements" attribute, and if it finds anything
    undone (even if what's left is just a single white space), it repeats
    the process again, to the end.