DBD-SQLcipher
view release on metacpan or search on metacpan
Processing Multiple Statements At A Time
DBI's statement handle is not supposed to process multiple statements at
a time. So if you pass a string that contains multiple statements (a
"dump") to a statement handle (via "prepare" or "do"), DBD::SQLcipher only
processes the first statement, and discards the rest.
Since 1.30_01, you can retrieve those ignored (unprepared) statements
via "$sth->{sqlite_unprepared_statements}". It usually contains nothing
but white spaces, but if you really care, you can check this attribute
to see if there's anything left undone. Also, if you set a
"sqlite_allow_multiple_statements" attribute of a database handle to
true when you connect to a database, "do" method automatically checks
the "sqlite_unprepared_statements" attribute, and if it finds anything
undone (even if what's left is just a single white space), it repeats
the process again, to the end.
Performance
SQLcipher is fast, very fast. Matt processed his 72MB log file with it,
inserting the data (400,000+ rows) by using transactions and only
committing every 1000 rows (otherwise the insertion is quite slow), and
then performing queries on the data.
Queries like count(*) and avg(bytes) took fractions of a second to
return, but what surprised him most of all was:
SELECT url, count(*) as count
FROM access_log
GROUP BY url
ORDER BY count desc
LIMIT 20
To discover the top 20 hit URLs on the site (<http://axkit.org>), and it
returned within 2 seconds. He was seriously considering switching his
log analysis code to use this little speed demon!
Oh yeah, and that was with no indexes on the table, on a 400MHz PIII.
For best performance be sure to tune your hdparm settings if you are
using linux. Also you might want to set:
PRAGMA synchronous = OFF
Which will prevent sqlite from doing fsync's when writing (which slows
down non-transactional writes significantly) at the expense of some
peace of mind. Also try playing with the cache_size pragma.
The memory usage of SQLcipher can also be tuned using the cache_size
pragma.
$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.
DRIVER PRIVATE ATTRIBUTES
Database Handle Attributes
sqlite_version
Returns the version of the SQLcipher library which DBD::SQLcipher is
using, e.g., "2.8.0". Can only be read.
sqlite_unicode
If set to a true value, DBD::SQLcipher will turn the UTF-8 flag on for
all text strings coming out of the database (this feature is
currently disabled for perl < 5.8.5). For more details on the UTF-8
flag see perlunicode. The default is for the UTF-8 flag to be turned
off.
Also note that due to some bizarreness in SQLcipher's type system (see
<http://www.sqlite.org/datatype3.html>), if you want to retain
blob-style behavior for some columns under "$dbh->{sqlite_unicode} =
1" (say, to store images in the database), you have to state so
explicitly using the 3-argument form of "bind_param" in DBI when
doing updates:
use DBI qw(:sql_types);
$dbh->{sqlite_unicode} = 1;
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 "BLOB" in the DDL is not sufficient.
This attribute was originally named as "unicode", and renamed to
"sqlite_unicode" for integrity since version 1.26_06. Old "unicode"
attribute is still accessible but will be deprecated in the near
future.
sqlite_allow_multiple_statements
If you set this to true, "do" method will process multiple
statements at one go. This may be handy, but with performance
penalty. See above for details.
sqlite_use_immediate_transaction
If you set this to true, DBD::SQLcipher tries to issue a "begin
immediate transaction" (instead of "begin transaction") when
necessary. See above for details.
sqlite_see_if_its_a_number
If you set this to true, DBD::SQLcipher tries to see if the bind values
are number or not, and does not quote if they are numbers. See above
for details.
Statement Handle Attributes
sqlite_unprepared_statements
Returns an unprepared part of the statement you pass to "prepare".
Typically this contains nothing but white spaces after a semicolon.
See above for details.
METHODS
See also to the DBI documentation for the details of other common
methods.
table_info
$sth = $dbh->table_info(undef, $schema, $table, $type, \%attr);
Returns all tables and schemas (databases) as specified in "table_info"
in DBI. The schema and table arguments will do a "LIKE" search. You can
specify an ESCAPE character by including an 'Escape' attribute in
\%attr. The $type argument accepts a comma separated list of the
following types 'TABLE', 'VIEW', 'LOCAL TEMPORARY' and 'SYSTEM TABLE'
(by default all are returned). Note that a statement handle is returned,
and not a direct list of tables.
The following fields are returned:
TABLE_CAT: Always NULL, as SQLcipher does not have the concept of catalogs.
TABLE_SCHEM: The name of the schema (database) that the table or view is
in. The default schema is 'main', temporary tables are in 'temp' and
other databases will be in the name given when the database was
attached.
TABLE_NAME: The name of the table or view.
TABLE_TYPE: The type of object returned. Will be one of 'TABLE', 'VIEW',
'LOCAL TEMPORARY' or 'SYSTEM TABLE'.
primary_key, primary_key_info
@names = $dbh->primary_key(undef, $schema, $table);
$sth = $dbh->primary_key_info(undef, $schema, $table, \%attr);
You can retrieve primary key names or more detailed information. As
noted above, SQLcipher does not have the concept of catalogs, so the first
argument of the mothods is usually "undef", and you'll usually set
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
FUNCTION undef Function Name
SAVEPOINT Operation Savepoint Name
COLLATION FUNCTIONS
Definition
SQLcipher v3 provides the ability for users to supply arbitrary comparison
functions, known as user-defined "collation sequences" or "collating
functions", to be used for comparing two text values.
<http://www.sqlite.org/datatype3.html#collation> explains how collations
are used in various SQL expressions.
Builtin collation sequences
The following collation sequences are builtin within SQLcipher :
BINARY
Compares string data using memcmp(), regardless of text encoding.
NOCASE
The same as binary, except the 26 upper case characters of ASCII are
folded to their lower case equivalents before the comparison is
performed. Note that only ASCII characters are case folded. SQLcipher
does not attempt to do full UTF case folding due to the size of the
tables required.
RTRIM
The same as binary, except that trailing space characters are
ignored.
In addition, "DBD::SQLcipher" automatically installs the following
collation sequences :
perl
corresponds to the Perl "cmp" operator
perllocale
Perl "cmp" operator, in a context where "use locale" is activated.
Usage
You can write for example
CREATE TABLE foo(
txt1 COLLATE perl,
txt2 COLLATE perllocale,
txt3 COLLATE nocase
)
or
SELECT * FROM foo ORDER BY name COLLATE perllocale
Unicode handling
If the attribute "$dbh->{sqlite_unicode}" is set, strings coming from
the database and passed to the collation function will be properly
tagged with the utf8 flag; but this only works if the "sqlite_unicode"
attribute is set before the first call to a perl collation sequence .
The recommended way to activate unicode is to set the parameter at
connection time :
my $dbh = DBI->connect(
"dbi:SQLcipher:dbname=foo", "", "",
{
RaiseError => 1,
sqlite_unicode => 1,
}
);
Adding user-defined collations
The native SQLcipher API for adding user-defined collations is exposed
through methods "sqlite_create_collation" and "sqlite_collation_needed".
To avoid calling these functions every time a $dbh handle is created,
"DBD::SQLcipher" offers a simpler interface through the
%DBD::SQLcipher::COLLATION hash : just insert your own collation functions
in that hash, and whenever an unknown collation name is encountered in
SQL, the appropriate collation function will be loaded on demand from
the hash. For example, here is a way to sort text values regardless of
their accented characters :
use DBD::SQLcipher;
$DBD::SQLcipher::COLLATION{no_accents} = sub {
my ( $a, $b ) = map lc, @_;
tr[????????????????????????????]
[aaaaaacdeeeeiiiinoooooouuuuy] for $a, $b;
$a cmp $b;
};
my $dbh = DBI->connect("dbi:SQLcipher:dbname=dbfile");
my $sql = "SELECT ... FROM ... ORDER BY ... COLLATE no_accents");
my $rows = $dbh->selectall_arrayref($sql);
The builtin "perl" or "perllocale" collations are predefined in that
same hash.
The COLLATION hash is a global registry within the current process;
hence there is a risk of undesired side-effects. Therefore, to prevent
action at distance, the hash is implemented 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::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 *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
The FTS3 extension module within SQLcipher allows users to create special
tables with a built-in full-text index (hereafter "FTS3 tables"). The
full-text index allows the user to efficiently query the database for
all rows that contain one or more instances of a specified word
(hereafter a "token"), even if the table contains many large documents.
Short introduction to FTS3
The detailed documentation for FTS3 can be found at
<http://www.sqlite.org/fts3.html>. Here is a very short example :
$dbh->do(<<"") or die DBI::errstr;
CREATE VIRTUAL TABLE fts_example USING fts3(content)
my $sth = $dbh->prepare("INSERT INTO fts_example(content) VALUES (?))");
( run in 0.504 second using v1.01-cache-2.11-cpan-3d66aa2751a )