view release on metacpan or  search on metacpan

README  view on Meta::CPAN

  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.

  Performance
    SQLcipher is fast, very fast. Matt processed his 72MB log file with it,

README  view on Meta::CPAN

    specify an ESCAPE character by including an 'Escape' attribute in
    \%attr. The $type argument accepts a comma separated list of the
    following types 'TABLE', 'VIEW', 'LOCAL TEMPORARY' and 'SYSTEM TABLE'
    (by default all are returned). Note that a statement handle is returned,
    and not a direct list of tables.

    The following fields are returned:

    TABLE_CAT: Always NULL, as SQLcipher does not have the concept of catalogs.

    TABLE_SCHEM: The name of the schema (database) that the table or view is
    in. The default schema is 'main', temporary tables are in 'temp' and
    other databases will be in the name given when the database was
    attached.

    TABLE_NAME: The name of the table or view.

    TABLE_TYPE: The type of object returned. Will be one of 'TABLE', 'VIEW',
    'LOCAL TEMPORARY' or 'SYSTEM TABLE'.

  primary_key, primary_key_info
      @names = $dbh->primary_key(undef, $schema, $table);
      $sth   = $dbh->primary_key_info(undef, $schema, $table, \%attr);

    You can retrieve primary key names or more detailed information. As
    noted above, SQLcipher does not have the concept of catalogs, so the first
    argument of the mothods is usually "undef", and you'll usually set
    "undef" for the second one (unless you want to know the primary keys of
    temporary tables).

DRIVER PRIVATE METHODS
    The following methods can be called via the func() method with a little
    tweak, but the use of func() method is now discouraged by the DBI author
    for various reasons (see DBI's document
    <http://search.cpan.org/dist/DBI/lib/DBI/DBD.pm#Using_install_method()_t
    o_expose_driver-private_methods> for details). So, if you're using DBI
    >= 1.608, use these "sqlite_" methods. If you need to use an older DBI,
    you can call these like this:

      $dbh->func( ..., "(method name without sqlite_ prefix)" );

    Exception: "sqlite_trace" should always be called as is, even with
    "func()" method (to avoid conflict with DBI's trace() method).

      $dbh->func( ..., "sqlite_trace");

  $dbh->sqlite_last_insert_rowid()
    This method returns the last inserted rowid. If you specify an INTEGER
    PRIMARY KEY as the first column in your table, that is the column that
    is returned. Otherwise, it is the hidden ROWID column. See the sqlite
    docs for details.

    Generally you should not be using this method. Use the DBI
    last_insert_id method instead. The usage of this is:

      $h->last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ])

    Running "$h->last_insert_id("","","","")" is the equivalent of running
    "$dbh->sqlite_last_insert_rowid()" directly.

  $dbh->sqlite_busy_timeout()
    Retrieve the current busy timeout.

  $dbh->sqlite_busy_timeout( $ms )
    Set the current busy timeout. The timeout is in milliseconds.

  $dbh->sqlite_create_function( $name, $argc, $code_ref )
    This method will register a new function which will be usable in an SQL
    query. The method's parameters are:

    $name
        The name of the function. This is the name of the function as it
        will be used from SQL.

    $argc
        The number of arguments taken by the function. If this number is -1,
        the function can take any number of arguments.

    $code_ref
        This should be a reference to the function's implementation.

    For example, here is how to define a now() function which returns the
    current number of seconds since the epoch:

      $dbh->sqlite_create_function( 'now', 0, sub { return time } );

    After this, it could be use from SQL as:

      INSERT INTO mytable ( now() );

   REGEXP function
    SQLcipher includes syntactic support for an infix operator 'REGEXP', but
    without any implementation. The "DBD::SQLcipher" driver automatically
    registers an implementation that performs standard perl regular
    expression matching, using current locale. So for example you can search
    for words starting with an 'A' with a query like

      SELECT * from table WHERE column REGEXP '\bA\w+'

    If you want case-insensitive searching, use perl regex flags, like this
    :

      SELECT * from table WHERE column REGEXP '(?i:\bA\w+)'

    The default REGEXP implementation can be overridden through the
    "create_function" API described above.

    Note that regexp matching will not use SQLcipher indices, but will iterate
    over all rows, so it could be quite costly in terms of performance.

  $dbh->sqlite_create_collation( $name, $code_ref )
    This method manually registers a new function which will be usable in an
    SQL query as a COLLATE option for sorting. Such functions can also be
    registered automatically on demand: see section "COLLATION FUNCTIONS"
    below.

    The method's parameters are:

    $name
        The name of the function exposed to SQL.

    $code_ref
        Reference to the function's implementation. The driver will check
        that this is a proper sorting function.