DBD-SQLite
view release on metacpan or search on metacpan
lib/DBD/SQLite.pm view on Meta::CPAN
$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.
=head1 DRIVER PRIVATE ATTRIBUTES
=head2 Database Handle Attributes
=over 4
=item sqlite_version
Returns the version of the SQLite library which B<DBD::SQLite> is using,
e.g., "3.26.0". Can only be read.
=item sqlite_string_mode
SQLite strings are simple arrays of bytes, but Perl strings can store any
arbitrary Unicode code point. Thus, DBD::SQLite has to adopt some method
of translating between those two models. This parameter defines that
translation.
Accepted values are the following constants:
=over
=item * DBD_SQLITE_STRING_MODE_BYTES: All strings are assumed to
represent bytes. A Perl string that contains any code point above 255
will trigger an exception. This is appropriate for Latin-1 strings,
binary data, pre-encoded UTF-8 strings, etc.
=item * DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK: All Perl strings are encoded
to UTF-8 before being given to SQLite. Perl will B<try> to decode SQLite
strings as UTF-8 when giving them to Perl. Should any such string not be
valid UTF-8, a warning is thrown, and the string is left undecoded.
This is appropriate for strings that are decoded to characters via,
e.g., L<Encode/decode>.
Also note that, due to some bizarreness in SQLite's type system (see
L<https://www.sqlite.org/datatype3.html>), if you want to retain
blob-style behavior for B<some> columns under DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK
(say, to store images in the database), you have to state so
explicitly using the 3-argument form of L<DBI/bind_param> when doing
updates:
use DBI qw(:sql_types);
use DBD::SQLite::Constants ':dbd_sqlite_string_mode';
$dbh->{sqlite_string_mode} = DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK;
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 C<BLOB> in the DDL is B<not> sufficient.
=item * DBD_SQLITE_STRING_MODE_UNICODE_STRICT: Like
DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK but usually throws an exception
rather than a warning if SQLite sends invalid UTF-8. (In Perl callbacks
from SQLite we still warn instead.)
=item * DBD_SQLITE_STRING_MODE_UNICODE_NAIVE: Like
DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK but uses a "naïve" UTF-8 decoding
method that forgoes validation. This is marginally faster than a validated
decode, but it can also B<corrupt> B<Perl> B<itself!>
=item * DBD_SQLITE_STRING_MODE_PV (default, but B<DO> B<NOT> B<USE>): Like
DBD_SQLITE_STRING_MODE_BYTES, but when translating Perl strings to SQLite
the Perl string's internal byte buffer is given to SQLite. B<This> B<is>
B<bad>, but it's been the default for many years, and changing that would
break existing applications.
=back
=item C<sqlite_unicode> or C<unicode> (deprecated)
If truthy, equivalent to setting C<sqlite_string_mode> to
DBD_SQLITE_STRING_MODE_UNICODE_NAIVE; if falsy, equivalent to
DBD_SQLITE_STRING_MODE_PV.
Prefer C<sqlite_string_mode> in all new code.
=item sqlite_allow_multiple_statements
If you set this to true, C<do> method will process multiple
statements at one go. This may be handy, but with performance
penalty. See above for details.
=item sqlite_use_immediate_transaction
If you set this to true, DBD::SQLite tries to issue a C<begin
immediate transaction> (instead of C<begin transaction>) when
necessary. See above for details.
As of version 1.38_01, this attribute is set to true by default.
If you really need to use C<deferred> transactions for some reasons,
set this to false explicitly.
=item 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.
=item sqlite_extended_result_codes
If set to true, DBD::SQLite uses extended result codes where appropriate
(see L<https://www.sqlite.org/rescode.html>).
=item sqlite_defensive
If set to true, language features that allow ordinary SQL to deliberately
corrupt the database file are prohibited.
=back
=head2 Statement Handle Attributes
=over 4
lib/DBD/SQLite.pm view on Meta::CPAN
=head2 $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.
=head2 $dbh->sqlite_backup_from_dbh( $another_dbh )
This method accesses the SQLite Online Backup API, and will take a backup of
the database for the passed handle, 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.
You can use this to backup from an in-memory database to another in-memory database.
=head2 $dbh->sqlite_backup_to_dbh( $another_dbh )
This method accesses the SQLite Online Backup API, and will take a backup of
the currently connected database, and write it out to the passed database handle.
=head2 $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('libmemvfs.so')")
or die "Cannot prepare: " . $dbh->errstr();
=head2 $dbh->sqlite_load_extension( $file, $proc )
Loading an extension by a select statement (with the "load_extension" SQLite3 function like above) has some limitations. If the extension you want to use creates other functions that are not native to SQLite, use this method instead. $file (a path to...
$dbh->sqlite_enable_load_extension(1);
$dbh->sqlite_load_extension('libsqlitefunctions.so')
or die "Cannot load extension: " . $dbh->errstr();
If the extension uses SQLite mutex functions like C<sqlite3_mutex_enter>, then
the extension should be compiled with the same C<SQLITE_THREADSAFE> compile-time
setting as this module, see C<DBD::SQLite::compile_options()>.
=head2 $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
=over
=item $statement
is a UTF-8 rendering of the SQL statement text as the statement
first begins executing.
=back
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 L<DBI/TRACING> for better tracing options.
=head2 $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
=over
=item $statement
is the original statement text (without bind parameters).
=item $elapsed_time
is an estimate of wall-clock time of how long that statement took to run (in milliseconds).
=back
This method is considered experimental and is subject to change in future versions of SQLite.
See also L<DBI::Profile> for better profiling options.
=head2 $dbh->sqlite_table_column_metadata( $dbname, $tablename, $columnname )
is for internal use only.
=head2 $dbh->sqlite_db_status()
Returns a hash reference that holds a set of status information of database connection such as cache usage. See L<https://www.sqlite.org/c3ref/c_dbstatus_options.html> for details. You may also pass 0 as an argument to reset the status.
=head2 $sth->sqlite_st_status()
Returns a hash reference that holds a set of status information of SQLite statement handle such as full table scan count. See L<https://www.sqlite.org/c3ref/c_stmtstatus_counter.html> for details. Statement status only holds the current value.
my $status = $sth->sqlite_st_status();
my $cur = $status->{fullscan_step};
You may also pass 0 as an argument to reset the status.
=head2 $dbh->sqlite_db_config( $id, $new_integer_value )
You can change how the connected database should behave like this:
use DBD::SQLite::Constants qw/:database_connection_configuration_options/;
my $dbh = DBI->connect('dbi:SQLite::memory:');
# This disables language features that allow ordinary SQL
# to deliberately corrupt the database file
$dbh->sqlite_db_config( SQLITE_DBCONFIG_DEFENSIVE, 1 );
lib/DBD/SQLite.pm view on Meta::CPAN
before using a preexisting virtual table for the module.
Virtual tables are explained in L<DBD::SQLite::VirtualTable>.
=head2 $dbh->sqlite_limit( $category_id, $new_value )
Sets a new run-time limit for the category, and returns the current limit.
If the new value is a negative number (or omitted), the limit is unchanged
and just returns the current limit. Category ids (SQLITE_LIMIT_LENGTH,
SQLITE_LIMIT_VARIABLE_NUMBER, etc) can be imported from DBD::SQLite::Constants.
=head2 $dbh->sqlite_get_autocommit()
Returns true if the internal SQLite connection is in an autocommit mode.
This does not always return the same value as C<< $dbh->{AutoCommit} >>.
This returns false if you explicitly issue a C<<BEGIN>> statement.
=head2 $dbh->sqlite_txn_state()
Returns the internal transaction status of SQLite (not of DBI).
Return values (SQLITE_TXN_NONE, SQLITE_TXN_READ, SQLITE_TXN_WRITE)
can be imported from DBD::SQLite::Constants. You may pass an optional
schema name (usually "main"). If SQLite does not support this function,
or if you pass a wrong schema name, -1 is returned.
=head2 $dbh->sqlite_error_offset()
Returns the byte offset of the start of a problematic input SQL token
or -1 if the most recent error does not reference a specific token in
the input SQL (or DBD::SQLite is built with an older version of SQLite).
=head1 DRIVER FUNCTIONS
=head2 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.
=head2 DBD::SQLite::sqlite_status()
Returns a hash reference that holds a set of status information of SQLite runtime such as memory usage or page cache usage (see L<https://www.sqlite.org/c3ref/c_status_malloc_count.html> for details). Each of the entry contains the current value and ...
my $status = DBD::SQLite::sqlite_status();
my $cur = $status->{memory_used}{current};
my $high = $status->{memory_used}{highwater};
You may also pass 0 as an argument to reset the status.
=head2 DBD::SQLite::strlike($pattern, $string, $escape_char), DBD::SQLite::strglob($pattern, $string)
As of 1.49_05 (SQLite 3.10.0), you can use these two functions to
see if a string matches a pattern. These may be useful when you
create a virtual table or a custom function.
See L<http://sqlite.org/c3ref/strlike.html> and
L<http://sqlite.org/c3ref/strglob.html> for details.
=head1 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 C<DBD::SQLite> module must be explicitly C<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 C<DBD::SQLite> is given
below; more information can be found ad
at L<https://www.sqlite.org/c3ref/constlist.html>.
=head2 Authorizer Return Codes
OK
DENY
IGNORE
=head2 Action Codes
The L</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
DROP_VIEW View Name undef
INSERT Table Name undef
PRAGMA Pragma Name 1st arg or undef
READ Table Name Column Name
SELECT undef undef
TRANSACTION Operation undef
UPDATE Table Name Column Name
ATTACH Filename undef
DETACH Database Name undef
ALTER_TABLE Database Name Table Name
REINDEX Index Name undef
lib/DBD/SQLite.pm view on Meta::CPAN
hash, that will happily accept new entries, but will raise an
exception if any attempt is made to override or delete a existing
entry (including the builtin C<perl> and C<perllocale>).
If you really, really need to change or delete an entry, you can
always grab the tied object underneath C<%DBD::SQLite::COLLATION> ---
but don't do that unless you really know what you are doing. Also
observe that changes in the global hash will not modify existing
collations in existing database handles: it will only affect new
I<requests> for collations. In other words, if you want to change
the behaviour of a collation within an existing C<$dbh>, you
need to call the L</create_collation> method directly.
=head1 FULLTEXT SEARCH
SQLite is bundled with an extension module for full-text
indexing. Tables with this feature enabled can be efficiently queried
to find rows that contain one or more instances of some specified
words, in any column, even if the table contains many large documents.
Explanations for using this feature are provided in a separate document:
see L<DBD::SQLite::Fulltext_search>.
=head1 R* TREE SUPPORT
The RTREE extension module within SQLite adds support for creating
a R-Tree, a special index for range and multidimensional queries. This
allows users to create tables that can be loaded with (as an example)
geospatial data such as latitude/longitude coordinates for buildings within
a city :
CREATE VIRTUAL TABLE city_buildings USING rtree(
id, -- Integer primary key
minLong, maxLong, -- Minimum and maximum longitude
minLat, maxLat -- Minimum and maximum latitude
);
then query which buildings overlap or are contained within a specified region:
# IDs that are contained within query coordinates
my $contained_sql = <<"";
SELECT id FROM city_buildings
WHERE minLong >= ? AND maxLong <= ?
AND minLat >= ? AND maxLat <= ?
# ... and those that overlap query coordinates
my $overlap_sql = <<"";
SELECT id FROM city_buildings
WHERE maxLong >= ? AND minLong <= ?
AND maxLat >= ? AND minLat <= ?
my $contained = $dbh->selectcol_arrayref($contained_sql,undef,
$minLong, $maxLong, $minLat, $maxLat);
my $overlapping = $dbh->selectcol_arrayref($overlap_sql,undef,
$minLong, $maxLong, $minLat, $maxLat);
For more detail, please see the SQLite R-Tree page
(L<https://www.sqlite.org/rtree.html>). Note that custom R-Tree
queries using callbacks, as mentioned in the prior link, have not been
implemented yet.
=head1 VIRTUAL TABLES IMPLEMENTED IN PERL
SQLite has a concept of "virtual tables" which look like regular
tables but are implemented internally through specific functions.
The fulltext or R* tree features described in the previous chapters
are examples of such virtual tables, implemented in C code.
C<DBD::SQLite> also supports virtual tables implemented in I<Perl code>:
see L<DBD::SQLite::VirtualTable> for using or implementing such
virtual tables. These can have many interesting uses
for joining regular DBMS data with some other kind of data within your
Perl programs. Bundled with the present distribution are :
=over
=item *
L<DBD::SQLite::VirtualTable::FileContent> : implements a virtual
column that exposes file contents. This is especially useful
in conjunction with a fulltext index; see L<DBD::SQLite::Fulltext_search>.
=item *
L<DBD::SQLite::VirtualTable::PerlData> : binds to a Perl array
within the Perl program. This can be used for simple import/export
operations, for debugging purposes, for joining data from different
sources, etc.
=back
Other Perl virtual tables may also be published separately on CPAN.
=head1 FOR DBD::SQLITE EXTENSION AUTHORS
Since 1.30_01, you can retrieve the bundled SQLite C source and/or
header like this:
use File::ShareDir 'dist_dir';
use File::Spec::Functions 'catfile';
# the whole sqlite3.h header
my $sqlite3_h = catfile(dist_dir('DBD-SQLite'), 'sqlite3.h');
# or only a particular header, amalgamated in sqlite3.c
my $what_i_want = 'parse.h';
my $sqlite3_c = catfile(dist_dir('DBD-SQLite'), 'sqlite3.c');
open my $fh, '<', $sqlite3_c or die $!;
my $code = do { local $/; <$fh> };
my ($parse_h) = $code =~ m{(
/\*+[ ]Begin[ ]file[ ]$what_i_want[ ]\*+
.+?
/\*+[ ]End[ ]of[ ]$what_i_want[ ]\*+/
)}sx;
open my $out, '>', $what_i_want or die $!;
print $out $parse_h;
close $out;
You usually want to use this in your extension's C<Makefile.PL>,
and you may want to add DBD::SQLite to your extension's C<CONFIGURE_REQUIRES>
to ensure your extension users use the same C source/header they use
to build DBD::SQLite itself (instead of the ones installed in their
system).
=head1 TO DO
The following items remain to be done.
=head2 Leak Detection
Implement one or more leak detection tests that only run during
AUTOMATED_TESTING and RELEASE_TESTING and validate that none of the C
code we work with leaks.
=head2 Stream API for Blobs
Reading/writing into blobs using C<sqlite2_blob_open> / C<sqlite2_blob_close>.
=head2 Support for custom callbacks for R-Tree queries
Custom queries of a R-Tree index using a callback are possible with
the SQLite C API (L<https://www.sqlite.org/rtree.html>), so one could
potentially use a callback that narrowed the result set down based
on a specific need, such as querying for overlapping circles.
=head1 SUPPORT
Bugs should be reported to GitHub issues:
L<https://github.com/DBD-SQLite/DBD-SQLite/issues>
or via RT if you prefer:
L<http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
Note that bugs of bundled SQLite library (i.e. bugs in C<sqlite3.[ch]>)
should be reported to the SQLite developers at sqlite.org via their bug
tracker or via their mailing list.
The master repository is on GitHub:
L<https://github.com/DBD-SQLite/DBD-SQLite>.
We also have a mailing list:
L<http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite>
=head1 AUTHORS
Matt Sergeant E<lt>matt@sergeant.orgE<gt>
Francis J. Lacoste E<lt>flacoste@logreport.orgE<gt>
Wolfgang Sourdeau E<lt>wolfgang@logreport.orgE<gt>
Adam Kennedy E<lt>adamk@cpan.orgE<gt>
Max Maischein E<lt>corion@cpan.orgE<gt>
Laurent Dami E<lt>dami@cpan.orgE<gt>
Kenichi Ishigaki E<lt>ishigaki@cpan.orgE<gt>
=head1 COPYRIGHT
The bundled SQLite code in this distribution is Public Domain.
DBD::SQLite is copyright 2002 - 2007 Matt Sergeant.
Some parts copyright 2008 Francis J. Lacoste.
Some parts copyright 2008 Wolfgang Sourdeau.
Some parts copyright 2008 - 2013 Adam Kennedy.
Some parts copyright 2009 - 2013 Kenichi Ishigaki.
Some parts derived from L<DBD::SQLite::Amalgamation>
copyright 2008 Audrey Tang.
( run in 1.662 second using v1.01-cache-2.11-cpan-cdf2f3d4e48 )