view release on metacpan or  search on metacpan

README  view on Meta::CPAN

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

README  view on Meta::CPAN

  The following nonempty fields are returned :

  TABLE_SCHEM: The name of the schema (database) that the table 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

  NON_UNIQUE: Contains 0 for unique indexes, 1 for non-unique
  indexes

  INDEX_NAME: The name of the index

  TYPE: SQLite uses 'btree' for all it's indexes

  ORDINAL_POSITION: Column sequence number (starting with 1).

  COLUMN_NAME: The name of the column

 ping
    my $bool = $dbh->ping;

  returns true if the database file exists (or the database is
  in-memory), and the database connection is active.

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
  <https://metacpan.org/pod/DBI::DBD#Using-install_method()-to-expos
  e-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_db_filename()
  Retrieve the current (main) database filename. If the database is
  in-memory or temporary, this returns "undef".

 $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, $flags )
  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.

  $flags
      You can optionally pass an extra flag bit to create_function,
      which then would be ORed with SQLITE_UTF8 (default). As of
      1.47_02 (SQLite 3.8.9), only meaning bit is
      SQLITE_DETERMINISTIC (introduced at SQLite 3.8.3), which can
      make the function perform better. See C API documentation at
      <http://sqlite.org/c3ref/create_function.html> for details.

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