DBD-SQLcipher
view release on metacpan or search on metacpan
lib/DBD/SQLcipher.pm view on Meta::CPAN
are strings that depend on the action code
(see L</"Action Codes">).
=item $database
is the name of the database (C<main>, C<temp>, etc.) if applicable.
=item $trigger_or_view
is the name of the inner-most trigger or view that is responsible for
the access attempt, or C<undef> if this access attempt is directly from
top-level SQL code.
=back
=head2 $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.
=head2 $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.
=head2 $dbh->sqlite_enable_load_extension( $bool )
Calling this method with a true value enables loading (external)
SQLcipher3 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();
=head2 $dbh->sqlite_load_extension( $file, $proc )
Loading an extension by a select statement (with the "load_extension" SQLcipher3 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, ...
=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 SQLcipher.
See also L<DBI::Profile> for better profiling options.
=head2 $dbh->sqlite_table_column_metadata( $dbname, $tablename, $columnname )
is for internal use only.
=head2 DBD::SQLcipher::compile_options()
Returns an array of compile options (available since SQLcipher 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.
=head2 DBD::SQLcipher::sqlite_status()
Returns a hash reference that holds a set of status information of SQLcipher runtime such as memory usage or page cache usage (see L<http://www.sqlite.org/c3ref/c_status_malloc_count.html> for details). Each of the entry contains the current value an...
my $status = DBD::SQLcipher::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 $dbh->sqlite_db_status()
Returns a hash reference that holds a set of status information of database connection such as cache usage. See L<http://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 SQLcipher statement handle such as full table scan count. See L<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.
=head2 $sth->sqlite_create_module()
Registers a name for a I<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 L<DBD::SQLcipher::VirtualTable>.
=head1 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 C<DBD::SQLcipher> 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::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 C<DBD::SQLcipher> is given
below; more information can be found ad
at L<http://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/SQLcipher.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::SQLcipher::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
SQLcipher 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::SQLcipher::Fulltext_search>.
=head1 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
(L<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.
=head1 VIRTUAL TABLES IMPLEMENTED IN PERL
SQLcipher 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::SQLcipher> also supports virtual tables implemented in I<Perl code>:
see L<DBD::SQLcipher::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::SQLcipher::VirtualTable::FileContent> : implements a virtual
column that exposes file contents. This is especially useful
in conjunction with a fulltext index; see L<DBD::SQLcipher::Fulltext_search>.
=item *
L<DBD::SQLcipher::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 SQLcipher 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 C<Makefile.PL>,
and you may want to add DBD::SQLcipher to your extension's C<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).
=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 SQLcipher C API (L<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.
=head1 SUPPORT
Bugs should be reported via the CPAN bug tracker at
L<http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLcipher>
Note that bugs of bundled SQLcipher library (i.e. bugs in C<sqlite3.[ch]>)
should be reported to the SQLcipher developers at sqlite.org via their bug
tracker or via their mailing list.
The master repository is on GitHub:
L<https://github.com/DBD-SQLcipher/DBD-SQLcipher>.
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 SQLcipher code in this distribution is Public Domain.
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 - 2013 Adam Kennedy.
Some parts copyright 2009 - 2013 Kenichi Ishigaki.
Some parts derived from L<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.
( run in 0.994 second using v1.01-cache-2.11-cpan-cdf2f3d4e48 )