DBD-SQLite
view release on metacpan or search on metacpan
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_load_extension( $file, $proc )
Loading an extension by a select statement (with the
"load_extension" SQLite3 function like above) has some
limitations. If you need to, say, create other functions from an
extension, use this method. $file (a path to the extension) is
mandatory, and $proc (an entry point name) is optional. You need
to call "sqlite_enable_load_extension" before calling
"sqlite_load_extension".
$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.
$dbh->sqlite_table_column_metadata( $dbname, $tablename, $columnname )
is for internal use only.
$dbh->sqlite_db_status()
Returns a hash reference that holds a set of status information of
database connection such as cache usage. See
<http://www.sqlite.org/c3ref/c_dbstatus_options.html> for details.
You may also pass 0 as an argument to reset the status.
$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
<http://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.
$dbh->sqlite_create_module()
Registers a name for a *virtual table module*. Module names must
be registered before creating a new virtual table using the module
and before using a preexisting virtual table for the module.
Virtual tables are explained in DBD::SQLite::VirtualTable.
$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.
DRIVER FUNCTIONS
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.
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
<http://www.sqlite.org/c3ref/c_status_malloc_count.html> for
details). Each of the entry contains the current value and the
highwater value.
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.
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
<http://sqlite.org/c3ref/strlike.html> and
<http://sqlite.org/c3ref/strglob.html> for details.
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
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
ANALYZE Table Name undef
CREATE_VTABLE Table Name Module Name
as a "write-only" 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 "perl" and
"perllocale").
If you really, really need to change or delete an entry, you can
always grab the tied object underneath %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
*requests* for collations. In other words, if you want to change
the behaviour of a collation within an existing $dbh, you need to
call the "create_collation" method directly.
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 DBD::SQLite::Fulltext_search.
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
(<http://www.sqlite.org/rtree.html>). Note that custom R-Tree
queries using callbacks, as mentioned in the prior link, have not
been implemented yet.
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.
"DBD::SQLite" also supports virtual tables implemented in *Perl
code*: see 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 :
* DBD::SQLite::VirtualTable::FileContent : implements a virtual
column that exposes file contents. This is especially useful
in conjunction with a fulltext index; see
DBD::SQLite::Fulltext_search.
* 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.
Other Perl virtual tables may also be published separately on
CPAN.
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 "Makefile.PL",
and you may want to add DBD::SQLite to your extension's
"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).
TO DO
The following items remain to be done.
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.
Stream API for Blobs
Reading/writing into blobs using "sqlite2_blob_open" /
"sqlite2_blob_close".
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 (<http://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.
SUPPORT
Bugs should be reported via the CPAN bug tracker at
<http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
Note that bugs of bundled SQLite library (i.e. bugs in
"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:
<https://github.com/DBD-SQLite/DBD-SQLite>.
We also have a mailing list:
<http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite>
AUTHORS
Matt Sergeant <matt@sergeant.org>
Francis J. Lacoste <flacoste@logreport.org>
Wolfgang Sourdeau <wolfgang@logreport.org>
Adam Kennedy <adamk@cpan.org>
Max Maischein <corion@cpan.org>
Laurent Dami <dami@cpan.org>
Kenichi Ishigaki <ishigaki@cpan.org>
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 DBD::SQLite::Amalgamation copyright 2008
Audrey Tang.
This program is free software; you can redistribute it and/or
modify it under the same terms as Perl itself.
The full text of the license can be found in the LICENSE file
included with this module.
( run in 0.617 second using v1.01-cache-2.11-cpan-cdf2f3d4e48 )