DBD-SQLite
view release on metacpan or search on metacpan
lib/DBD/SQLite.pm view on Meta::CPAN
DBD::SQLite::db->install_method('sqlite_create_module');
DBD::SQLite::db->install_method('sqlite_limit');
DBD::SQLite::db->install_method('sqlite_db_config');
DBD::SQLite::db->install_method('sqlite_get_autocommit');
DBD::SQLite::db->install_method('sqlite_txn_state');
DBD::SQLite::db->install_method('sqlite_error_offset');
$methods_are_installed++;
}
$drh = DBI::_new_drh( "$_[0]::dr", {
Name => 'SQLite',
Version => $VERSION,
Attribution => 'DBD::SQLite by Matt Sergeant et al',
} );
return $drh;
}
sub CLONE {
undef $drh;
}
package # hide from PAUSE
DBD::SQLite::dr;
sub connect {
my ($drh, $dbname, $user, $auth, $attr) = @_;
# Default PrintWarn to the value of $^W
# unless ( defined $attr->{PrintWarn} ) {
# $attr->{PrintWarn} = $^W ? 1 : 0;
# }
my $dbh = DBI::_new_dbh( $drh, {
Name => $dbname,
} );
my $real = $dbname;
if ( $dbname =~ /=/ ) {
foreach my $attrib ( split(/;/, $dbname) ) {
my ($key, $value) = split(/=/, $attrib, 2);
if ( $key =~ /^(?:db(?:name)?|database)$/ ) {
$real = $value;
} elsif ( $key eq 'uri' ) {
$real = $value;
$attr->{sqlite_open_flags} |= DBD::SQLite::OPEN_URI();
} else {
$attr->{$key} = $value;
}
}
}
if (my $flags = $attr->{sqlite_open_flags}) {
unless ($flags & (DBD::SQLite::OPEN_READONLY() | DBD::SQLite::OPEN_READWRITE())) {
$attr->{sqlite_open_flags} |= DBD::SQLite::OPEN_READWRITE() | DBD::SQLite::OPEN_CREATE();
}
}
# To avoid unicode and long file name problems on Windows,
# convert to the shortname if the file (or parent directory) exists.
if ( $^O =~ /MSWin32/ and $real ne ':memory:' and $real ne '' and $real !~ /^file:/ and !-f $real ) {
require File::Basename;
my ($file, $dir, $suffix) = File::Basename::fileparse($real);
# We are creating a new file.
# Does the directory it's in at least exist?
if ( -d $dir ) {
require Win32;
$real = join '', grep { defined } Win32::GetShortPathName($dir), $file, $suffix;
} else {
# SQLite can't do mkpath anyway.
# So let it go through as it and fail.
}
}
# Hand off to the actual login function
DBD::SQLite::db::_login($dbh, $real, $user, $auth, $attr) or return undef;
# Register the on-demand collation installer, REGEXP function and
# perl tokenizer
if ( DBD::SQLite::NEWAPI ) {
$dbh->sqlite_collation_needed( \&install_collation );
$dbh->sqlite_create_function( "REGEXP", 2, \®exp );
$dbh->sqlite_register_fts3_perl_tokenizer();
} else {
$dbh->func( \&install_collation, "collation_needed" );
$dbh->func( "REGEXP", 2, \®exp, "create_function" );
$dbh->func( "register_fts3_perl_tokenizer" );
}
# HACK: Since PrintWarn = 0 doesn't seem to actually prevent warnings
# in DBD::SQLite we set Warn to false if PrintWarn is false.
# NOTE: According to the explanation by timbunce,
# "Warn is meant to report on bad practices or problems with
# the DBI itself (hence always on by default), while PrintWarn
# is meant to report warnings coming from the database."
# That is, if you want to disable an ineffective rollback warning
# etc (due to bad practices), you should turn off Warn,
# and to silence other warnings, turn off PrintWarn.
# Warn and PrintWarn are independent, and turning off PrintWarn
# does not silence those warnings that should be controlled by
# Warn.
# unless ( $attr->{PrintWarn} ) {
# $attr->{Warn} = 0;
# }
return $dbh;
}
sub install_collation {
my $dbh = shift;
my $name = shift;
my $collation = $DBD::SQLite::COLLATION{$name};
unless ($collation) {
warn "Can't install unknown collation: $name" if $dbh->{PrintWarn};
return;
}
if ( DBD::SQLite::NEWAPI ) {
lib/DBD/SQLite.pm view on Meta::CPAN
=item sqlite_string_mode
SQLite strings are simple arrays of bytes, but Perl strings can store any
arbitrary Unicode code point. Thus, DBD::SQLite has to adopt some method
of translating between those two models. This parameter defines that
translation.
Accepted values are the following constants:
=over
=item * DBD_SQLITE_STRING_MODE_BYTES: All strings are assumed to
represent bytes. A Perl string that contains any code point above 255
will trigger an exception. This is appropriate for Latin-1 strings,
binary data, pre-encoded UTF-8 strings, etc.
=item * DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK: All Perl strings are encoded
to UTF-8 before being given to SQLite. Perl will B<try> to decode SQLite
strings as UTF-8 when giving them to Perl. Should any such string not be
valid UTF-8, a warning is thrown, and the string is left undecoded.
This is appropriate for strings that are decoded to characters via,
e.g., L<Encode/decode>.
Also note that, due to some bizarreness in SQLite's type system (see
L<https://www.sqlite.org/datatype3.html>), if you want to retain
blob-style behavior for B<some> columns under DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK
(say, to store images in the database), you have to state so
explicitly using the 3-argument form of L<DBI/bind_param> when doing
updates:
use DBI qw(:sql_types);
use DBD::SQLite::Constants ':dbd_sqlite_string_mode';
$dbh->{sqlite_string_mode} = DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK;
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 C<BLOB> in the DDL is B<not> sufficient.
=item * DBD_SQLITE_STRING_MODE_UNICODE_STRICT: Like
DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK but usually throws an exception
rather than a warning if SQLite sends invalid UTF-8. (In Perl callbacks
from SQLite we still warn instead.)
=item * DBD_SQLITE_STRING_MODE_UNICODE_NAIVE: Like
DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK but uses a "naïve" UTF-8 decoding
method that forgoes validation. This is marginally faster than a validated
decode, but it can also B<corrupt> B<Perl> B<itself!>
=item * DBD_SQLITE_STRING_MODE_PV (default, but B<DO> B<NOT> B<USE>): Like
DBD_SQLITE_STRING_MODE_BYTES, but when translating Perl strings to SQLite
the Perl string's internal byte buffer is given to SQLite. B<This> B<is>
B<bad>, but it's been the default for many years, and changing that would
break existing applications.
=back
=item C<sqlite_unicode> or C<unicode> (deprecated)
If truthy, equivalent to setting C<sqlite_string_mode> to
DBD_SQLITE_STRING_MODE_UNICODE_NAIVE; if falsy, equivalent to
DBD_SQLITE_STRING_MODE_PV.
Prefer C<sqlite_string_mode> in all new code.
=item sqlite_allow_multiple_statements
If you set this to true, C<do> method will process multiple
statements at one go. This may be handy, but with performance
penalty. See above for details.
=item sqlite_use_immediate_transaction
If you set this to true, DBD::SQLite tries to issue a C<begin
immediate transaction> (instead of C<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 C<deferred> transactions for some reasons,
set this to false explicitly.
=item 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.
=item sqlite_extended_result_codes
If set to true, DBD::SQLite uses extended result codes where appropriate
(see L<https://www.sqlite.org/rescode.html>).
=item sqlite_defensive
If set to true, language features that allow ordinary SQL to deliberately
corrupt the database file are prohibited.
=back
=head2 Statement Handle Attributes
=over 4
=item sqlite_unprepared_statements
Returns an unprepared part of the statement you pass to C<prepare>.
Typically this contains nothing but white spaces after a semicolon.
See above for details.
=back
=head1 METHODS
See also to the L<DBI> documentation for the details of other common
methods.
=head2 table_info
lib/DBD/SQLite.pm view on Meta::CPAN
=head2 Builtin collation sequences
The following collation sequences are builtin within SQLite :
=over
=item B<BINARY>
Compares string data using memcmp(), regardless of text encoding.
=item B<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.
=item B<RTRIM>
The same as binary, except that trailing space characters are ignored.
=back
In addition, C<DBD::SQLite> automatically installs the
following collation sequences :
=over
=item B<perl>
corresponds to the Perl C<cmp> operator
=item B<perllocale>
Perl C<cmp> operator, in a context where C<use locale> is activated.
=back
=head2 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
=head2 Unicode handling
Depending on the C<< $dbh->{sqlite_string_mode} >> value, strings coming
from the database and passed to the collation function may be decoded as
UTF-8. This only works, though, if the C<sqlite_string_mode> attribute is
set B<before> the first call to a perl collation sequence. The recommended
way to activate unicode is to set C<sqlite_string_mode> at connection time:
my $dbh = DBI->connect(
"dbi:SQLite:dbname=foo", "", "",
{
RaiseError => 1,
sqlite_string_mode => DBD_SQLITE_STRING_MODE_UNICODE_STRICT,
}
);
=head2 Adding user-defined collations
The native SQLite API for adding user-defined collations is
exposed through methods L</"sqlite_create_collation"> and
L</"sqlite_collation_needed">.
To avoid calling these functions every time a C<$dbh> handle is
created, C<DBD::SQLite> offers a simpler interface through the
C<%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 C<perl> or C<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 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::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
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
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.
( run in 0.736 second using v1.01-cache-2.11-cpan-63c85eba8c4 )