NAME

    DBD::SQLite - Self-contained RDBMS in a DBI Driver

SYNOPSIS

      use DBI;

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

DESCRIPTION

    SQLite is a public domain file-based relational database engine that you

    can find at <http://www.sqlite.org/>.

    DBD::SQLite is a Perl DBI driver for SQLite, that includes the entire

    thing in the distribution. So in order to get a fast transaction capable

    RDBMS working for your perl project you simply have to install this

    module, and nothing else.

    SQLite supports the following features:

    Implements a large subset of SQL92

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

    A complete DB in a single disk file

        Everything for your database is stored in a single disk file, making

        it easier to move things around than with DBD::CSV.

    Atomic commit and rollback

        Yes, DBD::SQLite is small and light, but it supports full

        transactions!

    Extensible

        User-defined aggregate or regular functions can be registered with

        the SQL parser.

    There's lots more to it, so please refer to the docs on the SQLite web

    page, listed above, for SQL details. Also refer to DBI for details 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 SQLite database.

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.

  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`;

      my $sth = $dbh->prepare("INSERT INTO mytable VALUES (1, ?)");

      $sth->bind_param(1, $blob, SQL_BLOB);

      $sth->execute();

    And then retrieval just works:

      $sth = $dbh->prepare("SELECT * FROM mytable WHERE id = 1");

      $sth->execute();

      my $row = $sth->fetch;

      my $blobo = $row->[1];

  # now $blobo == $blob

  Functions And Bind Parameters

    As of this writing, a SQL that compares a return value of a function

    with a numeric bind value like this doesn't work as you might expect.

      my $sth = $dbh->prepare(q{

        SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;

      });

      $sth->execute(5);

    This is because DBD::SQLite assumes that all the bind values are text

    (and should be quoted) by default. Thus the above statement becomes like

    this while executing:

      SELECT bar FROM foo GROUP BY bar HAVING count(*) > "5";

    There are three workarounds for this.

    Use bind_param() explicitly

        As shown above in the "BLOB" section, you can always use

        "bind_param()" to tell the type of a bind value.

          use DBI qw(:sql_types);  # Don't forget this

  my $sth = $dbh->prepare(q{

            SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;

          });

          $sth->bind_param(1, 5, SQL_INTEGER);

          $sth->execute();

    Add zero to make it a number

        This is somewhat weird, but works anyway.

          my $sth = $dbh->prepare(q{

            SELECT bar FROM foo GROUP BY bar HAVING count(*) > (? + 0);

          });

          $sth->execute(5);

    Set "sqlite_see_if_its_a_number" database handle attribute

        As of version 1.32_02, you can use "sqlite_see_if_its_a_number" to

        let DBD::SQLite to see if the bind values are numbers or not.

          $dbh->{sqlite_see_if_its_a_number} = 1;

          my $sth = $dbh->prepare(q{

            SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;

          });

          $sth->execute(5);

        You can set it to true when you connect to a database.

          my $dbh = DBI->connect('dbi:SQLite:foo', undef, undef, {

            AutoCommit => 1,

            RaiseError => 1,

            sqlite_see_if_its_a_number => 1,

          });

        This is the most straightforward solution, but as noted above,

        existing data in your databases created by DBD::SQLite have not

        always been stored as numbers, so this *might* cause other obscure

        problems. Use this sparingly when you handle existing databases. If

        you handle databases created by other tools like native "sqlite3"

        command line tool, this attribute would help you.

  Placeholders

    SQLite supports several placeholder expressions, including "?" and

    ":AAAA". Consult the DBI and sqlite documentation for details.

    <http://www.sqlite.org/lang_expr.html#varparam>

    Note that a question mark actually means a next unused (numbered)

    placeholder. You're advised not to use it with other (numbered or named)

    placeholders to avoid confusion.

      my $sth = $dbh->prepare(

        'update TABLE set a=?1 where b=?2 and a IS NOT ?1'

      );

      $sth->execute(1, 2);

  Foreign Keys

    BE PREPARED! WOLVES APPROACH!!

    SQLite has started supporting foreign key constraints since 3.6.19

    (released on Oct 14, 2009; bundled in DBD::SQLite 1.26_05). To be exact,

    SQLite has long been able to parse a schema with foreign keys, but the

    constraints has not been enforced. Now you can issue a pragma actually

    to enable this feature and enforce the constraints.

    To do this, issue the following pragma (see below), preferably as soon

    as you connect to a database and you're not in a transaction:

      $dbh->do("PRAGMA foreign_keys = ON");

    And you can explicitly disable the feature whenever you like by turning

    the pragma off:

      $dbh->do("PRAGMA foreign_keys = OFF");

    As of this writing, this feature is disabled by default by the sqlite

    team, and by us, to secure backward compatibility, as this feature may

    break your applications, and actually broke some for us. If you have

    used a schema with foreign key constraints but haven't cared them much

    and supposed they're always ignored for SQLite, be prepared, and please

    do extensive testing to ensure that your applications will continue to

    work when the foreign keys support is enabled by default. It is very

    likely that the sqlite team will turn it default-on in the future, and

    we plan to do it NO LATER THAN they do so.

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

  Pragma

    SQLite has a set of "Pragma"s to modifiy its operation or to query for

    its internal data. These are specific to SQLite and are not likely to

    work with other DBD libraries, but you may find some of these are quite

    useful. DBD::SQLite 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::SQLite'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

        begin 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 SQLite 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 SQLite 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:SQLite::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.

    SQLite 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 SQLite

         $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::SQLite 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

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

    inserting the data (400,000+ rows) by using transactions and only

    committing every 1000 rows (otherwise the insertion is quite slow), and

    then performing queries on the data.

    Queries like count(*) and avg(bytes) took fractions of a second to

    return, but what surprised him most of all was:

      SELECT url, count(*) as count

      FROM access_log

      GROUP BY url

      ORDER BY count desc

      LIMIT 20

    To discover the top 20 hit URLs on the site (<http://axkit.org>), and it

    returned within 2 seconds. He was seriously considering switching his

    log analysis code to use this little speed demon!

    Oh yeah, and that was with no indexes on the table, on a 400MHz PIII.

    For best performance be sure to tune your hdparm settings if you are

    using linux. Also you might want to set:

      PRAGMA synchronous = OFF

    Which will prevent sqlite from doing fsync's when writing (which slows

    down non-transactional writes significantly) at the expense of some

    peace of mind. Also try playing with the cache_size pragma.

    The memory usage of SQLite can also be tuned using the cache_size

    pragma.

      $dbh->do("PRAGMA cache_size = 800000");

    The above will allocate 800M for DB cache; the default is 2M. Your sweet

    spot probably lies somewhere in between.

DRIVER PRIVATE ATTRIBUTES

  Database Handle Attributes

    sqlite_version

        Returns the version of the SQLite library which DBD::SQLite is

        using, e.g., "2.8.0". Can only be read.

    sqlite_unicode

        If set to a true value, DBD::SQLite will turn the UTF-8 flag on for

        all text strings coming out of the database (this feature is

        currently disabled for perl < 5.8.5). For more details on the UTF-8

        flag see perlunicode. The default is for the UTF-8 flag to be turned

        off.

        Also note that due to some bizarreness in SQLite's type system (see

        <http://www.sqlite.org/datatype3.html>), if you want to retain

        blob-style behavior for some columns under "$dbh->{sqlite_unicode} =

        1" (say, to store images in the database), you have to state so

        explicitly using the 3-argument form of "bind_param" in DBI when

        doing updates:

          use DBI qw(:sql_types);

          $dbh->{sqlite_unicode} = 1;

          my $sth = $dbh->prepare("INSERT INTO mytable (blobcolumn) VALUES (?)");

  # Binary_data will be stored as is.

          $sth->bind_param(1, $binary_data, SQL_BLOB);

        Defining the column type as "BLOB" in the DDL is not sufficient.

        This attribute was originally named as "unicode", and renamed to

        "sqlite_unicode" for integrity since version 1.26_06. Old "unicode"

        attribute is still accessible but will be deprecated in the near

        future.

    sqlite_allow_multiple_statements

        If you set this to true, "do" method will process multiple

        statements at one go. This may be handy, but with performance

        penalty. See above for details.

    sqlite_use_immediate_transaction

        If you set this to true, DBD::SQLite tries to issue a "begin

        immediate transaction" (instead of "begin transaction") when

        necessary. See above for details.

    sqlite_see_if_its_a_number

        If you set this to true, DBD::SQLite tries to see if the bind values

        are number or not, and does not quote if they are numbers. See above

        for details.

  Statement Handle Attributes

    sqlite_unprepared_statements

        Returns an unprepared part of the statement you pass to "prepare".

        Typically this contains nothing but white spaces after a semicolon.

        See above for details.

METHODS

    See also to the DBI documentation for the details of other common

    methods.

  table_info

      $sth = $dbh->table_info(undef, $schema, $table, $type, \%attr);

    Returns all tables and schemas (databases) as specified in "table_info"

    in DBI. The schema and table arguments will do a "LIKE" search. You can

    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 SQLite 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, SQLite 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

    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

    SQLite includes syntactic support for an infix operator 'REGEXP', but

    without any implementation. The "DBD::SQLite" 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 SQLite 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.

  $dbh->sqlite_collation_needed( $code_ref )

    This method manually registers a callback function that will be invoked

    whenever an undefined collation sequence is required from an SQL

    statement. The callback is invoked as

      $code_ref->($dbh, $collation_name)

    and should register the desired collation using

    "sqlite_create_collation".

    An initial callback is already registered by "DBD::SQLite", so for most

    common cases it will be simpler to just add your collation sequences in

    the %DBD::SQLite::COLLATION hash (see section "COLLATION FUNCTIONS"

    below).

  $dbh->sqlite_create_aggregate( $name, $argc, $pkg )

    This method will register a new aggregate function which can then be

    used from SQL. The method's parameters are:

    $name

        The name of the aggregate function, this is the name under which the

        function will be available from SQL.

    $argc

        This is an integer which tells the SQL parser how many arguments the

        function takes. If that number is -1, the function can take any

        number of arguments.

    $pkg

        This is the package which implements the aggregator interface.

    The aggregator interface consists of defining three methods:

    new()

        This method will be called once to create an object which should be

        used to aggregate the rows in a particular group. The step() and

        finalize() methods will be called upon the reference return by the

        method.

    step(@_)

        This method will be called once for each row in the aggregate.

    finalize()

        This method will be called once all rows in the aggregate were

        processed and it should return the aggregate function's result. When

        there is no rows in the aggregate, finalize() will be called right

        after new().

    Here is a simple aggregate function which returns the variance (example

    adapted from pysqlite):

      package variance;

  sub new { bless [], shift; }

  sub step {

          my ( $self, $value ) = @_;

      push @$self, $value;

      }

  sub finalize {

          my $self = $_[0];

      my $n = @$self;

      # Variance is NULL unless there is more than one row

          return undef unless $n || $n == 1;

      my $mu = 0;

          foreach my $v ( @$self ) {

              $mu += $v;

          }

          $mu /= $n;

      my $sigma = 0;

          foreach my $v ( @$self ) {

              $sigma += ($x - $mu)**2;

          }

          $sigma = $sigma / ($n - 1);

      return $sigma;

      }

  $dbh->sqlite_create_aggregate( "variance", 1, 'variance' );

    The aggregate function can then be used as:

      SELECT group_name, variance(score)

      FROM results

      GROUP BY group_name;

    For more examples, see the DBD::SQLite::Cookbook.

  $dbh->sqlite_progress_handler( $n_opcodes, $code_ref )

    This method registers a handler to be invoked periodically during long

    running calls to SQLite.

    An example use for this interface is to keep a GUI updated during a

    large query. The parameters are:

    $n_opcodes

        The progress handler is invoked once for every $n_opcodes virtual

        machine opcodes in SQLite.

    $code_ref

        Reference to the handler subroutine. If the progress handler returns

        non-zero, the SQLite operation is interrupted. This feature can be

        used to implement a "Cancel" button on a GUI dialog box.

        Set this argument to "undef" if you want to unregister a previous

        progress handler.

  $dbh->sqlite_commit_hook( $code_ref )

    This method registers a callback function to be invoked whenever a

    transaction is committed. Any callback set by a previous call to

    "sqlite_commit_hook" is overridden. A reference to the previous callback

    (if any) is returned. Registering an "undef" disables the callback.

    When the commit hook callback returns zero, the commit operation is

    allowed to continue normally. If the callback returns non-zero, then the

    commit is converted into a rollback (in that case, any attempt to

    *explicitly* call "$dbh->rollback()" afterwards would yield an error).

  $dbh->sqlite_rollback_hook( $code_ref )

    This method registers a callback function to be invoked whenever a

    transaction is rolled back. Any callback set by a previous call to

    "sqlite_rollback_hook" is overridden. A reference to the previous

    callback (if any) is returned. Registering an "undef" disables the

    callback.

  $dbh->sqlite_update_hook( $code_ref )

    This method registers a callback function to be invoked whenever a row

    is updated, inserted or deleted. Any callback set by a previous call to

    "sqlite_update_hook" is overridden. A reference to the previous callback

    (if any) is returned. Registering an "undef" disables the callback.

    The callback will be called as

      $code_ref->($action_code, $database, $table, $rowid)

    where

    $action_code

        is an integer equal to either "DBD::SQLite::INSERT",

        "DBD::SQLite::DELETE" or "DBD::SQLite::UPDATE" (see "Action Codes");

    $database

        is the name of the database containing the affected row;

    $table

        is the name of the table containing the affected row;

    $rowid

        is the unique 64-bit signed integer key of the affected row within

        that table.

  $dbh->sqlite_set_authorizer( $code_ref )

    This method registers an authorizer callback to be invoked whenever SQL

    statements are being compiled by the "prepare" in DBI method. The

    authorizer callback should return "DBD::SQLite::OK" to allow the action,

    "DBD::SQLite::IGNORE" to disallow the specific action but allow the SQL

    statement to continue to be compiled, or "DBD::SQLite::DENY" to cause

    the entire SQL statement to be rejected with an error. If the authorizer

    callback returns any other value, then then "prepare" call that

    triggered the authorizer will fail with an error message.

    An authorizer is used when preparing SQL statements from an untrusted

    source, to ensure that the SQL statements do not try to access data they

    are not allowed to see, or that they do not try to execute malicious

    statements that damage the database. For example, an application may

    allow a user to enter arbitrary SQL queries for evaluation by a

    database. But the application does not want the user to be able to make

    arbitrary changes to the database. An authorizer could then be put in

    place while the user-entered SQL is being prepared that disallows

    everything except SELECT statements.

    The callback will be called as

      $code_ref->($action_code, $string1, $string2, $database, $trigger_or_view)

    where

    $action_code

        is an integer that specifies what action is being authorized (see

        "Action Codes").

    $string1, $string2

        are strings that depend on the action code (see "Action Codes").

    $database

        is the name of the database ("main", "temp", etc.) if applicable.

    $trigger_or_view

        is the name of the inner-most trigger or view that is responsible

        for the access attempt, or "undef" if this access attempt is

        directly from top-level SQL code.

  $dbh->sqlite_backup_from_file( $filename )

    This method accesses the SQLite Online Backup API, and will take a

    backup of the named database file, copying it to, and overwriting, your

    current database connection. This can be particularly handy if your

    current connection is to the special :memory: database, and you wish to

    populate it from an existing DB.

  $dbh->sqlite_backup_to_file( $filename )

    This method accesses the SQLite Online Backup API, and will take a

    backup of the currently connected database, and write it out to the

    named file.

  $dbh->sqlite_enable_load_extension( $bool )

    Calling this method with a true value enables loading (external) sqlite3

    extensions. After the call, you can load extensions like this:

      $dbh->sqlite_enable_load_extension(1);

      $sth = $dbh->prepare("select load_extension('libsqlitefunctions.so')")

      or die "Cannot prepare: " . $dbh->errstr();

  $dbh->sqlite_trace( $code_ref )

    This method registers a trace callback to be invoked whenever SQL

    statements are being run.

    The callback will be called as

      $code_ref->($statement)

    where

    $statement

        is a UTF-8 rendering of the SQL statement text as the statement

        first begins executing.

    Additional callbacks might occur as each triggered subprogram is

    entered. The callbacks for triggers contain a UTF-8 SQL comment that

    identifies the trigger.

    See also "TRACING" in DBI for better tracing options.

  $dbh->sqlite_profile( $code_ref )

    This method registers a profile callback to be invoked whenever a SQL

    statement finishes.

    The callback will be called as

      $code_ref->($statement, $elapsed_time)

    where

    $statement

        is the original statement text (without bind parameters).

    $elapsed_time

        is an estimate of wall-clock time of how long that statement took to

        run (in milliseconds).

    This method is considered experimental and is subject to change in

    future versions of SQLite.

    See also DBI::Profile for better profiling options.

  DBD::SQLite::compile_options()

    Returns an array of compile options (available since sqlite 3.6.23,

    bundled in DBD::SQLite 1.30_01), or an empty array if the bundled

    library is old or compiled with SQLITE_OMIT_COMPILEOPTION_DIAGS.

DRIVER CONSTANTS

    A subset of SQLite C constants are made available to Perl, because they

    may be needed when writing hooks or authorizer callbacks. For accessing

    such constants, the "DBD::SQLite" module must be explicitly "use"d at

    compile time. For example, an authorizer that forbids any DELETE

    operation would be written as follows :

      use DBD::SQLite;

      $dbh->sqlite_set_authorizer(sub {

        my $action_code = shift;

        return $action_code == DBD::SQLite::DELETE ? DBD::SQLite::DENY

                                                   : DBD::SQLite::OK;

      });

    The list of constants implemented in "DBD::SQLite" is given below; more

    information can be found ad at

    <http://www.sqlite.org/c3ref/constlist.html>.

  Authorizer Return Codes

      OK

      DENY

      IGNORE

  Action Codes

    The "set_authorizer" method registers a callback function that is

    invoked to authorize certain SQL statement actions. The first parameter

    to the callback is an integer code that specifies what action is being

    authorized. The second and third parameters to the callback are strings,

    the meaning of which varies according to the action code. Below is the

    list of action codes, together with their associated strings.

      # constant              string1         string2

      # ========              =======         =======

      CREATE_INDEX            Index Name      Table Name

      CREATE_TABLE            Table Name      undef

      CREATE_TEMP_INDEX       Index Name      Table Name

      CREATE_TEMP_TABLE       Table Name      undef

      CREATE_TEMP_TRIGGER     Trigger Name    Table Name

      CREATE_TEMP_VIEW        View Name       undef

      CREATE_TRIGGER          Trigger Name    Table Name

      CREATE_VIEW             View Name       undef

      DELETE                  Table Name      undef

      DROP_INDEX              Index Name      Table Name

      DROP_TABLE              Table Name      undef

      DROP_TEMP_INDEX         Index Name      Table Name

      DROP_TEMP_TABLE         Table Name      undef

      DROP_TEMP_TRIGGER       Trigger Name    Table Name

      DROP_TEMP_VIEW          View Name       undef

      DROP_TRIGGER            Trigger Name    Table Name