DBD-SQLcipher
view release on metacpan or search on metacpan
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 SQLcipher 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 SQLcipher 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 SQLcipher.
See also DBI::Profile for better profiling options.
DBD::SQLcipher::compile_options()
Returns an array of compile options (available since sqlite 3.6.23,
bundled in DBD::SQLcipher 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 SQLcipher C constants are made available to Perl, because they
may be needed when writing hooks or authorizer callbacks. For accessing
such constants, the "DBD::SQLcipher" 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::SQLcipher;
$dbh->sqlite_set_authorizer(sub {
my $action_code = shift;
return $action_code == DBD::SQLcipher::DELETE ? DBD::SQLcipher::DENY
: DBD::SQLcipher::OK;
});
The list of constants implemented in "DBD::SQLcipher" 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
DROP_VTABLE Table Name Module Name
Instead of writing tokenizers by hand, you can grab one of those already
implemented in the Search::Tokenizer module :
use Search::Tokenizer;
$dbh->do(<<"") or die DBI::errstr;
CREATE ... USING fts3(<columns>,
tokenize=perl 'Search::Tokenizer::unaccent')
or you can use "new" in Search::Tokenizer to build your own tokenizer.
Incomplete handling of utf8 characters
The current FTS3 implementation in SQLcipher is far from complete with
respect to utf8 handling : in particular, variable-length characters are
not treated correctly by the builtin functions "offsets()" and
"snippet()".
Database space for FTS3
FTS3 stores a complete copy of the indexed documents, together with the
fulltext index. On a large collection of documents, this can consume
quite a lot of disk space. If copies of documents are also available as
external resources (for example files on the filesystem), that space can
sometimes be spared --- see the tip in the Cookbook.
R* TREE SUPPORT
The RTREE extension module within SQLcipher 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 try_rtree
WHERE minLong >= ? AND maxLong <= ?
AND minLat >= ? AND maxLat <= ?
# ... and those that overlap query coordinates
my $overlap_sql = <<"";
SELECT id FROM try_rtree
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 SQLcipher 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.
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-SQLcipher'), 'sqlite3.h');
# or only a particular header, amalgamated in sqlite3.c
my $what_i_want = 'parse.h';
my $sqlite3_c = catfile(dist_dir('DBD-SQLcipher'), '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::SQLcipher to your extension's "CONFIGURE_REQUIRES" to
ensure your extension users use the same C source/header they use to
build DBD::SQLcipher 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".
Flags for sqlite3_open_v2
Support the full API of sqlite3_open_v2 (flags for opening the file).
Support for custom callbacks for R-Tree queries
Custom queries of a R-Tree index using a callback are possible with the
SQLcipher 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-SQLcipher>
Note that bugs of bundled sqlite library (i.e. bugs in "sqlite3.[ch]")
should be reported to the sqlite developers at zetetic.net via their bug
tracker or via their mailing list.
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>
Tiago Gasiba <tgasiba@cpan.org>
COPYRIGHT
The bundled SQLcipher code in this distribution is Open Source (see https://zetetic.net/sqlcipher).
See the file zetetic.txt for a description of the license.
DBD::SQLcipher is copyright 2002 - 2007 Matt Sergeant.
Some parts copyright 2008 Francis J. Lacoste.
Some parts copyright 2008 Wolfgang Sourdeau.
Some parts copyright 2008 - 2012 Adam Kennedy.
Some parts copyright 2009 - 2012 Kenichi Ishigaki.
Some parts derived from DBD::SQLcipher::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 2.318 seconds using v1.01-cache-2.11-cpan-cdf2f3d4e48 )