DBD-SQLite
view release on metacpan or search on metacpan
lib/DBD/SQLite.pm view on Meta::CPAN
package DBD::SQLite;
use 5.006;
use strict;
use DBI 1.57 ();
use XSLoader ();
our $VERSION = '1.78';
# sqlite_version cache (set in the XS bootstrap)
our ($sqlite_version, $sqlite_version_number);
# not sure if we still need these...
our ($err, $errstr);
XSLoader::load('DBD::SQLite', $VERSION);
# New or old API?
use constant NEWAPI => ($DBI::VERSION >= 1.608);
# global registry of collation functions, initialized with 2 builtins
our %COLLATION;
tie %COLLATION, 'DBD::SQLite::_WriteOnceHash';
$COLLATION{perl} = sub { $_[0] cmp $_[1] };
$COLLATION{perllocale} = sub { use locale; $_[0] cmp $_[1] };
our $drh;
my $methods_are_installed = 0;
sub driver {
return $drh if $drh;
if (!$methods_are_installed && DBD::SQLite::NEWAPI ) {
DBI->setup_driver('DBD::SQLite');
DBD::SQLite::db->install_method('sqlite_last_insert_rowid');
DBD::SQLite::db->install_method('sqlite_busy_timeout');
DBD::SQLite::db->install_method('sqlite_create_function');
DBD::SQLite::db->install_method('sqlite_create_aggregate');
DBD::SQLite::db->install_method('sqlite_create_collation');
DBD::SQLite::db->install_method('sqlite_collation_needed');
DBD::SQLite::db->install_method('sqlite_progress_handler');
DBD::SQLite::db->install_method('sqlite_commit_hook');
DBD::SQLite::db->install_method('sqlite_rollback_hook');
DBD::SQLite::db->install_method('sqlite_update_hook');
DBD::SQLite::db->install_method('sqlite_set_authorizer');
DBD::SQLite::db->install_method('sqlite_backup_from_file');
DBD::SQLite::db->install_method('sqlite_backup_to_file');
DBD::SQLite::db->install_method('sqlite_backup_from_dbh');
DBD::SQLite::db->install_method('sqlite_backup_to_dbh');
DBD::SQLite::db->install_method('sqlite_enable_load_extension');
DBD::SQLite::db->install_method('sqlite_load_extension');
DBD::SQLite::db->install_method('sqlite_register_fts3_perl_tokenizer');
DBD::SQLite::db->install_method('sqlite_trace', { O => 0x0004 });
DBD::SQLite::db->install_method('sqlite_profile', { O => 0x0004 });
DBD::SQLite::db->install_method('sqlite_table_column_metadata', { O => 0x0004 });
DBD::SQLite::db->install_method('sqlite_db_filename', { O => 0x0004 });
DBD::SQLite::db->install_method('sqlite_db_status', { O => 0x0004 });
DBD::SQLite::st->install_method('sqlite_st_status', { O => 0x0004 });
DBD::SQLite::db->install_method('sqlite_create_module');
DBD::SQLite::db->install_method('sqlite_limit');
DBD::SQLite::db->install_method('sqlite_db_config');
DBD::SQLite::db->install_method('sqlite_get_autocommit');
DBD::SQLite::db->install_method('sqlite_txn_state');
DBD::SQLite::db->install_method('sqlite_error_offset');
$methods_are_installed++;
}
$drh = DBI::_new_drh( "$_[0]::dr", {
Name => 'SQLite',
Version => $VERSION,
Attribution => 'DBD::SQLite by Matt Sergeant et al',
} );
return $drh;
}
sub CLONE {
undef $drh;
}
package # hide from PAUSE
DBD::SQLite::dr;
sub connect {
my ($drh, $dbname, $user, $auth, $attr) = @_;
# Default PrintWarn to the value of $^W
# unless ( defined $attr->{PrintWarn} ) {
# $attr->{PrintWarn} = $^W ? 1 : 0;
# }
my $dbh = DBI::_new_dbh( $drh, {
Name => $dbname,
} );
lib/DBD/SQLite.pm view on Meta::CPAN
sqlite_open_flags => SQLITE_OPEN_READONLY,
});
See L<https://www.sqlite.org/c3ref/open.html> for details.
As of 1.49_05, you can also make a database read-only by setting
C<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 C<PrintWarn> off).
=head2 DBD::SQLite And File::Temp
When you use L<File::Temp> to create a temporary file/directory for
SQLite databases, you need to remember:
=over 4
=item tempfile may be locked exclusively
You may want to use C<tempfile()> to create a temporary database
filename for DBD::SQLite, but as noted in L<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);
=item CLEANUP may not work unless a database is disconnected
When you set CLEANUP option to true when you create a temporary
directory with C<tempdir()> or C<newdir()>, you may have to
disconnect databases explicitly before the temporary directory
is gone (notably under MS Windows).
=back
(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.
=head2 DBD::SQLite and fork()
Follow the advice in the SQLite FAQ (L<https://sqlite.org/faq.html>).
=over 4
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.
=back
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 C<sqlite_busy_timeout> and
C<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.
=head2 Accessing A Database With Other Tools
To access the database from the command line, try using C<dbish>
which comes with the L<DBI::Shell> module. Just type:
dbish dbi:SQLite:foo.db
On the command line to access the file F<foo.db>.
Alternatively you can install SQLite from the link above without
conflicting with B<DBD::SQLite> and use the supplied C<sqlite3>
command line tool.
=head2 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
=head2 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";
lib/DBD/SQLite.pm view on Meta::CPAN
B<TABLE_NAME>:
The name of the table
B<NON_UNIQUE>:
Contains 0 for unique indexes, 1 for non-unique indexes
B<INDEX_NAME>:
The name of the index
B<TYPE>:
SQLite uses 'btree' for all it's indexes
B<ORDINAL_POSITION>:
Column sequence number (starting with 1).
B<COLUMN_NAME>:
The name of the column
=head2 ping
my $bool = $dbh->ping;
returns true if the database file exists (or the database is in-memory), and the database connection is active.
=head1 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 L<DBI> author
for various reasons (see DBI's document
L<https://metacpan.org/pod/DBI::DBD#Using-install_method()-to-expose-driver-private-methods>
for details). So, if you're using L<DBI> >= 1.608, use these C<sqlite_>
methods. If you need to use an older L<DBI>, you can call these like this:
$dbh->func( ..., "(method name without sqlite_ prefix)" );
Exception: C<sqlite_trace> should always be called as is, even with C<func()>
method (to avoid conflict with DBI's trace() method).
$dbh->func( ..., "sqlite_trace");
=head2 $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 L<DBI> last_insert_id
method instead. The usage of this is:
$h->last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ])
Running C<$h-E<gt>last_insert_id("","","","")> is the equivalent of running
C<$dbh-E<gt>sqlite_last_insert_rowid()> directly.
=head2 $dbh->sqlite_db_filename()
Retrieve the current (main) database filename. If the database is in-memory
or temporary, this returns an empty string, or C<undef>.
=head2 $dbh->sqlite_busy_timeout()
Retrieve the current busy timeout.
=head2 $dbh->sqlite_busy_timeout( $ms )
Set the current busy timeout. The timeout is in milliseconds.
=head2 $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:
=over
=item $name
The name of the function. This is the name of the function as it will
be used from SQL.
=item $argc
The number of arguments taken by the function. If this number is -1,
the function can take any number of arguments.
=item $code_ref
This should be a reference to the function's implementation.
=item $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 perfor...
=back
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() );
The function should return a scalar value, and the value is treated as a text
(or a number if appropriate) by default. If you do need to specify a type
of the return value (like BLOB), you can return a reference to an array that
contains the value and the type, as of 1.65_01.
$dbh->sqlite_create_function( 'md5', 1, sub { return [md5($_[0]), SQL_BLOB] } );
=head3 REGEXP function
SQLite includes syntactic support for an infix operator 'REGEXP', but
without any implementation. The C<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
C<create_function> API described above.
( run in 0.699 second using v1.01-cache-2.11-cpan-cdf2f3d4e48 )