DBD-SQLite
view release on metacpan or search on metacpan
handles internally since 1.47_01). If you do need to use "prepare"
or "prepare_cached" (which I don't recommend in this case, because
typically there's no placeholder nor reusable part in a dump), you
can look at << $sth->{sqlite_unprepared_statements} >> to retrieve
what's left, though it usually contains nothing but white spaces.
TYPE statement attribute
Because of historical reasons, DBD::SQLite's "TYPE" statement
handle attribute returns an array ref of string values, contrary
to the DBI specification. This value is also less useful for
SQLite users because SQLite uses dynamic type system (that means,
the datatype of a value is associated with the value itself, not
with its container).
Performance
SQLite 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 SQLite 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 SQLite library which DBD::SQLite is
using, e.g., "2.8.0". Can only be read.
sqlite_unicode
If set to a true value, DBD::SQLite 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 SQLite'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::SQLite tries to issue a "begin
immediate transaction" (instead of "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 "deferred" transactions for
some reasons, set this to false explicitly.
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.
sqlite_extended_result_codes
If set to true, DBD::SQLite uses extended result codes where
appropriate (see <http://www.sqlite.org/rescode.html>).
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 SQLite 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',
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
SQLite 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 SQLite :
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. SQLite 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::SQLite" 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:SQLite:dbname=foo", "", "",
{
RaiseError => 1,
sqlite_unicode => 1,
}
);
Adding user-defined collations
The native SQLite 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::SQLite" offers a simpler interface through the
%DBD::SQLite::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::SQLite;
$DBD::SQLite::COLLATION{no_accents} = sub {
my ( $a, $b ) = map lc, @_;
tr[��������������������������
[aaaaaacdeeeeiiiinoooooouuuuy] for $a, $b;
$a cmp $b;
};
my $dbh = DBI->connect("dbi:SQLite: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::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.
( run in 0.578 second using v1.01-cache-2.11-cpan-140bd7fdf52 )