ÿØÿà JFIF    ÿÛ „ ( %!1!%*+...983,7(-.- SQLite/VirtualTable/FileContent.pm000044400000021343152344563120013061 0ustar00#====================================================================== package DBD::SQLite::VirtualTable::FileContent; #====================================================================== use strict; use warnings; use base 'DBD::SQLite::VirtualTable'; my %option_ok = map {($_ => 1)} qw/source content_col path_col expose root get_content/; my %defaults = ( content_col => "content", path_col => "path", expose => "*", get_content => "DBD::SQLite::VirtualTable::FileContent::get_content", ); #---------------------------------------------------------------------- # object instanciation #---------------------------------------------------------------------- sub NEW { my $class = shift; my $self = $class->_PREPARE_SELF(@_); local $" = ", "; # for array interpolation in strings # initial parameter check !@{$self->{columns}} or die "${class}->NEW(): illegal options: @{$self->{columns}}"; $self->{options}{source} or die "${class}->NEW(): missing (source=...)"; my @bad_options = grep {!$option_ok{$_}} keys %{$self->{options}}; !@bad_options or die "${class}->NEW(): bad options: @bad_options"; # defaults ... tempted to use //= but we still want to support perl 5.8 :-( foreach my $k (keys %defaults) { defined $self->{options}{$k} or $self->{options}{$k} = $defaults{$k}; } # get list of columns from the source table my $src_table = $self->{options}{source}; my $sql = "PRAGMA table_info($src_table)"; my $dbh = ${$self->{dbh_ref}}; # can't use method ->dbh, not blessed yet my $src_info = $dbh->selectall_arrayref($sql, {Slice => [1, 2]}); @$src_info or die "${class}->NEW(source=$src_table): no such table in database"; # associate each source colname with its type info or " " (should eval true) my %src_col = map { ($_->[0] => $_->[1] || " ") } @$src_info; # check / complete the exposed columns my @exposed_cols; if ($self->{options}{expose} eq '*') { @exposed_cols = map {$_->[0]} @$src_info; } else { @exposed_cols = split /\s*,\s*/, $self->{options}{expose}; my @bad_cols = grep { !$src_col{$_} } @exposed_cols; die "table $src_table has no column named @bad_cols" if @bad_cols; } for (@exposed_cols) { die "$class: $self->{options}{content_col} cannot be both the " . "content_col and an exposed col" if $_ eq $self->{options}{content_col}; } # build the list of columns for this table $self->{columns} = [ "$self->{options}{content_col} TEXT", map {"$_ $src_col{$_}"} @exposed_cols ]; # acquire a coderef to the get_content() implementation, which # was given as a symbolic reference in %options no strict 'refs'; $self->{get_content} = \ &{$self->{options}{get_content}}; bless $self, $class; } sub _build_headers { my $self = shift; my $cols = $self->sqlite_table_info; # headers : names of columns, without type information $self->{headers} = [ map {$_->{name}} @$cols ]; } #---------------------------------------------------------------------- # method for initiating a search #---------------------------------------------------------------------- sub BEST_INDEX { my ($self, $constraints, $order_by) = @_; $self->_build_headers if !$self->{headers}; my @conditions; my $ix = 0; foreach my $constraint (grep {$_->{usable}} @$constraints) { my $col = $constraint->{col}; # if this is the content column, skip because we can't filter on it next if $col == 0; # for other columns, build a fragment for SQL WHERE on the underlying table my $colname = $col == -1 ? "rowid" : $self->{headers}[$col]; push @conditions, "$colname $constraint->{op} ?"; $constraint->{argvIndex} = $ix++; $constraint->{omit} = 1; # SQLite doesn't need to re-check the op } # TODO : exploit $order_by to add ordering clauses within idxStr my $outputs = { idxNum => 1, idxStr => join(" AND ", @conditions), orderByConsumed => 0, estimatedCost => 1.0, estimatedRows => undef, }; return $outputs; } #---------------------------------------------------------------------- # method for preventing updates #---------------------------------------------------------------------- sub _SQLITE_UPDATE { my ($self, $old_rowid, $new_rowid, @values) = @_; die "attempt to update a readonly virtual table"; } #---------------------------------------------------------------------- # file slurping function (not a method!) #---------------------------------------------------------------------- sub get_content { my ($path, $root) = @_; $path = "$root/$path" if $root; my $content = ""; if (open my $fh, "<", $path) { local $/; # slurp the whole file into a scalar $content = <$fh>; close $fh; } else { warn "can't open $path"; } return $content; } #====================================================================== package DBD::SQLite::VirtualTable::FileContent::Cursor; #====================================================================== use strict; use warnings; use base "DBD::SQLite::VirtualTable::Cursor"; sub FILTER { my ($self, $idxNum, $idxStr, @values) = @_; my $vtable = $self->{vtable}; # build SQL local $" = ", "; my @cols = @{$vtable->{headers}}; $cols[0] = 'rowid'; # replace the content column by the rowid push @cols, $vtable->{options}{path_col}; # path col in last position my $sql = "SELECT @cols FROM $vtable->{options}{source}"; $sql .= " WHERE $idxStr" if $idxStr; # request on the index table my $dbh = $vtable->dbh; $self->{sth} = $dbh->prepare($sql) or die DBI->errstr; $self->{sth}->execute(@values); $self->{row} = $self->{sth}->fetchrow_arrayref; return; } sub EOF { my ($self) = @_; return !$self->{row}; } sub NEXT { my ($self) = @_; $self->{row} = $self->{sth}->fetchrow_arrayref; } sub COLUMN { my ($self, $idxCol) = @_; return $idxCol == 0 ? $self->file_content : $self->{row}[$idxCol]; } sub ROWID { my ($self) = @_; return $self->{row}[0]; } sub file_content { my ($self) = @_; my $root = $self->{vtable}{options}{root}; my $path = $self->{row}[-1]; my $get_content_func = $self->{vtable}{get_content}; return $get_content_func->($path, $root); } 1; __END__ =head1 NAME DBD::SQLite::VirtualTable::FileContent -- virtual table for viewing file contents =head1 SYNOPSIS Within Perl : $dbh->sqlite_create_module(fcontent => "DBD::SQLite::VirtualTable::FileContent"); Then, within SQL : CREATE VIRTUAL TABLE tbl USING fcontent( source = src_table, content_col = content, path_col = path, expose = "path, col1, col2, col3", -- or "*" root = "/foo/bar" get_content = Foo::Bar::read_from_file ); SELECT col1, path, content FROM tbl WHERE ...; =head1 DESCRIPTION A "FileContent" virtual table is bound to some underlying I, which has a column containing paths to files. The virtual table behaves like a database view on the source table, with an added column which exposes the content from those files. This is especially useful as an "external content" to some fulltext table (see L) : the index table stores some metadata about files, and then the fulltext engine can index both the metadata and the file contents. =head1 PARAMETERS Parameters for creating a C virtual table are specified within the C statement, just like regular column declarations, but with an '=' sign. Authorized parameters are : =over =item C The name of the I. This parameter is mandatory. All other parameters are optional. =item C The name of the virtual column exposing file contents. The default is C. =item C The name of the column in C that contains paths to files. The default is C. =item C A comma-separated list (within double quotes) of source column names to be exposed by the virtual table. The default is C<"*">, which means all source columns. =item C An optional root directory that will be prepended to the I column when opening files. =item C Fully qualified name of a Perl function for reading file contents. The default implementation just slurps the entire file into a string; but this hook can point to more sophisticated implementations, like for example a function that would remove html tags. The hooked function is called like this : $file_content = $get_content->($path, $root); =back =head1 AUTHOR Laurent Dami Edami@cpan.orgE =head1 COPYRIGHT AND LICENSE Copyright Laurent Dami, 2014. This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself. =cut SQLite/VirtualTable/PerlData.pm000044400000035054152344563120012347 0ustar00#====================================================================== package DBD::SQLite::VirtualTable::PerlData; #====================================================================== use strict; use warnings; use base 'DBD::SQLite::VirtualTable'; use DBD::SQLite; use constant SQLITE_3010000 => $DBD::SQLite::sqlite_version_number >= 3010000 ? 1 : 0; use constant SQLITE_3021000 => $DBD::SQLite::sqlite_version_number >= 3021000 ? 1 : 0; # private data for translating comparison operators from Sqlite to Perl my $TXT = 0; my $NUM = 1; my %SQLOP2PERLOP = ( # TXT NUM '=' => [ 'eq', '==' ], '<' => [ 'lt', '<' ], '<=' => [ 'le', '<=' ], '>' => [ 'gt', '>' ], '>=' => [ 'ge', '>=' ], 'MATCH' => [ '=~', '=~' ], (SQLITE_3010000 ? ( 'LIKE' => [ 'DBD::SQLite::strlike', 'DBD::SQLite::strlike' ], 'GLOB' => [ 'DBD::SQLite::strglob', 'DBD::SQLite::strglob' ], 'REGEXP'=> [ '=~', '=~' ], ) : ()), (SQLITE_3021000 ? ( 'NE' => [ 'ne', '!=' ], 'ISNOT' => [ 'defined', 'defined' ], 'ISNOTNULL' => [ 'defined', 'defined' ], 'ISNULL' => [ '!defined', '!defined' ], 'IS' => [ '!defined', '!defined' ], ) : ()), ); #---------------------------------------------------------------------- # instanciation methods #---------------------------------------------------------------------- sub NEW { my $class = shift; my $self = $class->_PREPARE_SELF(@_); # verifications my $n_cols = @{$self->{columns}}; $n_cols > 0 or die "$class: no declared columns"; !$self->{options}{colref} || $n_cols == 1 or die "$class: must have exactly 1 column when using 'colref'"; my $symbolic_ref = $self->{options}{arrayrefs} || $self->{options}{hashrefs} || $self->{options}{colref} or die "$class: missing option 'arrayrefs' or 'hashrefs' or 'colref'"; # bind to the Perl variable no strict "refs"; defined ${$symbolic_ref} or die "$class: can't find global variable \$$symbolic_ref"; $self->{rows} = \ ${$symbolic_ref}; bless $self, $class; } sub _build_headers_optypes { my $self = shift; my $cols = $self->sqlite_table_info; # headers : names of columns, without type information $self->{headers} = [ map {$_->{name}} @$cols ]; # optypes : either $NUM or $TEXT for each column # (applying algorithm from datatype3.html" for type affinity) $self->{optypes} = [ map {$_->{type} =~ /INT|REAL|FLOA|DOUB/i ? $NUM : $TXT} @$cols ]; } #---------------------------------------------------------------------- # method for initiating a search #---------------------------------------------------------------------- sub BEST_INDEX { my ($self, $constraints, $order_by) = @_; $self->_build_headers_optypes if !$self->{headers}; # for each constraint, build a Perl code fragment. Those will be gathered # in FILTER() for deciding which rows match the constraints. my @conditions; my $ix = 0; foreach my $constraint (grep {$_->{usable} and exists $SQLOP2PERLOP{ $_->{op} } } @$constraints) { my $col = $constraint->{col}; my ($member, $optype); # build a Perl code fragment. Those fragments will be gathered # and eval-ed in FILTER(), for deciding which rows match the constraints. if ($col == -1) { # constraint on rowid $member = '$i'; $optype = $NUM; } else { # constraint on regular column my $opts = $self->{options}; $member = $opts->{arrayrefs} ? "\$row->[$col]" : $opts->{hashrefs} ? "\$row->{$self->{headers}[$col]}" : $opts->{colref} ? "\$row" : die "corrupted data in ->{options}"; $optype = $self->{optypes}[$col]; } my $op = $SQLOP2PERLOP{$constraint->{op}}[$optype]; if (SQLITE_3021000 && $op =~ /defined/) { if ($constraint->{op} =~ /NULL/) { push @conditions, "($op($member))"; } else { push @conditions, "($op($member) && !defined(\$vals[$ix]))"; } } elsif (SQLITE_3010000 && $op =~ /str/) { push @conditions, "(defined($member) && defined(\$vals[$ix]) && !$op(\$vals[$ix], $member))"; } else { push @conditions, "(defined($member) && defined(\$vals[$ix]) && $member $op \$vals[$ix])"; } # Note : $vals[$ix] refers to an array of values passed to the # FILTER method (see below); so the eval-ed perl code will be a # closure on those values # info passed back to the SQLite core -- see vtab.html in sqlite doc $constraint->{argvIndex} = $ix++; $constraint->{omit} = 1; } # further info for the SQLite core my $outputs = { idxNum => 1, idxStr => (join(" && ", @conditions) || "1"), orderByConsumed => 0, estimatedCost => 1.0, estimatedRows => undef, }; return $outputs; } #---------------------------------------------------------------------- # methods for data update #---------------------------------------------------------------------- sub _build_new_row { my ($self, $values) = @_; my $opts = $self->{options}; return $opts->{arrayrefs} ? $values : $opts->{hashrefs} ? { map {$self->{headers}->[$_], $values->[$_]} (0 .. @{$self->{headers}} - 1) } : $opts->{colref} ? $values->[0] : die "corrupted data in ->{options}"; } sub INSERT { my ($self, $new_rowid, @values) = @_; my $new_row = $self->_build_new_row(\@values); if (defined $new_rowid) { not ${$self->{rows}}->[$new_rowid] or die "can't INSERT : rowid $new_rowid already in use"; ${$self->{rows}}->[$new_rowid] = $new_row; } else { push @${$self->{rows}}, $new_row; return $#${$self->{rows}}; } } sub DELETE { my ($self, $old_rowid) = @_; delete ${$self->{rows}}->[$old_rowid]; } sub UPDATE { my ($self, $old_rowid, $new_rowid, @values) = @_; my $new_row = $self->_build_new_row(\@values); if ($new_rowid == $old_rowid) { ${$self->{rows}}->[$old_rowid] = $new_row; } else { delete ${$self->{rows}}->[$old_rowid]; ${$self->{rows}}->[$new_rowid] = $new_row; } } #====================================================================== package DBD::SQLite::VirtualTable::PerlData::Cursor; #====================================================================== use strict; use warnings; use base "DBD::SQLite::VirtualTable::Cursor"; sub row { my ($self, $i) = @_; return ${$self->{vtable}{rows}}->[$i]; } sub FILTER { my ($self, $idxNum, $idxStr, @vals) = @_; # build a method coderef to fetch matching rows my $perl_code = 'sub {my ($self, $i) = @_; my $row = $self->row($i); ' . $idxStr . '}'; # print STDERR "PERL CODE:\n", $perl_code, "\n"; $self->{is_wanted_row} = do { no warnings; eval $perl_code } or die "couldn't eval q{$perl_code} : $@"; # position the cursor to the first matching row (or to eof) $self->{row_ix} = -1; $self->NEXT; } sub EOF { my ($self) = @_; return $self->{row_ix} > $#${$self->{vtable}{rows}}; } sub NEXT { my ($self) = @_; do { $self->{row_ix} += 1 } until $self->EOF || eval {$self->{is_wanted_row}->($self, $self->{row_ix})}; # NOTE: the eval above is required for cases when user data, injected # into Perl comparison operators, generates errors; for example # WHERE col MATCH '(foo' will die because the regex is not well formed # (no matching parenthesis). In such cases no row is selected and the # query just returns an empty list. } sub COLUMN { my ($self, $idxCol) = @_; my $row = $self->row($self->{row_ix}); my $opts = $self->{vtable}{options}; return $opts->{arrayrefs} ? $row->[$idxCol] : $opts->{hashrefs} ? $row->{$self->{vtable}{headers}[$idxCol]} : $opts->{colref} ? $row : die "corrupted data in ->{options}"; } sub ROWID { my ($self) = @_; return $self->{row_ix} + 1; # rowids start at 1 in SQLite } 1; __END__ =head1 NAME DBD::SQLite::VirtualTable::PerlData -- virtual table hooked to Perl data =head1 SYNOPSIS Within Perl : $dbh->sqlite_create_module(perl => "DBD::SQLite::VirtualTable::PerlData"); Then, within SQL : CREATE VIRTUAL TABLE atbl USING perl(foo, bar, etc, arrayrefs="some::global::var::aref") CREATE VIRTUAL TABLE htbl USING perl(foo, bar, etc, hashrefs="some::global::var::href") CREATE VIRTUAL TABLE ctbl USING perl(single_col colref="some::global::var::ref") SELECT foo, bar FROM atbl WHERE ...; =head1 DESCRIPTION A C virtual table is a database view on some datastructure within a Perl program. The data can be read or modified both from SQL and from Perl. This is useful for simple import/export operations, for debugging purposes, for joining data from different sources, etc. =head1 PARAMETERS Parameters for creating a C virtual table are specified within the C statement, mixed with regular column declarations, but with an '=' sign. The only authorized (and mandatory) parameter is the one that specifies the Perl datastructure to which the virtual table is bound. It must be given as the fully qualified name of a global variable; the parameter can be one of three different kinds : =over =item C arrayref that contains an arrayref for each row. Each such row will have a size equivalent to the number of columns declared for the virtual table. =item C arrayref that contains a hashref for each row. Keys in each hashref should correspond to the columns declared for the virtual table. =item C arrayref that contains a single scalar for each row; obviously, this is a single-column virtual table. =back =head1 USAGE =head2 Common part of all examples : declaring the module In all examples below, the common part is that the Perl program should connect to the database and then declare the C virtual table module, like this # connect to the database my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile", '', '', {RaiseError => 1, AutoCommit => 1}); # or any other options suitable to your needs # register the module $dbh->sqlite_create_module(perl => "DBD::SQLite::VirtualTable::PerlData"); Then create a global arrayref variable, using C instead of C, so that the variable is stored in the symbol table of the enclosing module. package Foo::Bar; # could as well be just "main" our $rows = [ ... ]; Finally, create the virtual table and bind it to the global variable (here we assume that C<@$rows> contains arrayrefs) : $dbh->do('CREATE VIRTUAL TABLE temp.vtab' .' USING perl(col1 INT, col2 TEXT, etc, arrayrefs="Foo::Bar::rows'); In most cases, the virtual table will be for temporary use, which is the reason why this example prepends C in front of the table name : this tells SQLite to cleanup that table when the database handle will be disconnected, without the need to emit an explicit DROP statement. Column names (and optionally their types) are specified in the virtual table declaration, just like for any regular table. =head2 Arrayref example : statistics from files Let's suppose we want to perform some searches over a collection of files, where search constraints may be based on some of the fields returned by L, such as the size of the file or its last modify time. Here is a way to do it with a virtual table : my @files = ... ; # list of files to inspect # apply the L function to each file our $file_stats = [ map { [ $_, stat $_ ] } @files]; # create a temporary virtual table $dbh->do(<<""); CREATE VIRTUAL TABLE temp.file_stats' USING perl(path, dev, ino, mode, nlink, uid, gid, rdev, size, atime, mtime, ctime, blksize, blocks, arrayrefs="main::file_stats"); # search files my $sth = $dbh->prepare(<<""); SELECT * FROM file_stats WHERE mtime BETWEEN ? AND ? AND uid IN (...) =head2 Hashref example : unicode characters Given any unicode character, the L function returns a hashref with various bits of information about that character. So this can be exploited in a virtual table : use Unicode::UCD 'charinfo'; our $chars = [map {charinfo($_)} 0x300..0x400]; # arbitrary subrange # create a temporary virtual table $dbh->do(<<""); CREATE VIRTUAL TABLE charinfo USING perl( code, name, block, script, category, hashrefs="main::chars" ) # search characters my $sth = $dbh->prepare(<<""); SELECT * FROM charinfo WHERE script='Greek' AND name LIKE '%SIGMA%' =head2 Colref example: SELECT WHERE ... IN ... I file in SQLite's source (L).> A C virtual table is designed to facilitate using an array of values as the right-hand side of an IN operator. The usual syntax for IN is to prepare a statement like this: SELECT * FROM table WHERE x IN (?,?,?,...,?); and then bind individual values to each of the ? slots; but this has the disadvantage that the number of values must be known in advance. Instead, we can store values in a Perl array, bind that array to a virtual table, and then write a statement like this SELECT * FROM table WHERE x IN perl_array; Here is how such a program would look like : # connect to the database my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile", '', '', {RaiseError => 1, AutoCommit => 1}); # Declare a global arrayref containing the values. Here we assume # they are taken from @ARGV, but any other datasource would do. # Note the use of "our" instead of "my". our $values = \@ARGV; # register the module and declare the virtual table $dbh->sqlite_create_module(perl => "DBD::SQLite::VirtualTable::PerlData"); $dbh->do('CREATE VIRTUAL TABLE temp.intarray' .' USING perl(i INT, colref="main::values'); # now we can SELECT from another table, using the intarray as a constraint my $sql = "SELECT * FROM some_table WHERE some_col IN intarray"; my $result = $dbh->selectall_arrayref($sql); Beware that the virtual table is read-write, so the statement below would push 99 into @ARGV ! INSERT INTO intarray VALUES (99); =head1 AUTHOR Laurent Dami Edami@cpan.orgE =head1 COPYRIGHT AND LICENSE Copyright Laurent Dami, 2014. This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself. =cut SQLite/VirtualTable.pm000044400000053531152344563120010653 0ustar00#====================================================================== package DBD::SQLite::VirtualTable; #====================================================================== use strict; use warnings; use Scalar::Util qw/weaken/; our $VERSION = '1.76'; our @ISA; #---------------------------------------------------------------------- # methods for registering/destroying the module #---------------------------------------------------------------------- sub CREATE_MODULE { my ($class, $mod_name) = @_; } sub DESTROY_MODULE { my ($class, $mod_name) = @_; } #---------------------------------------------------------------------- # methods for creating/destroying instances #---------------------------------------------------------------------- sub CREATE { my $class = shift; return $class->NEW(@_); } sub CONNECT { my $class = shift; return $class->NEW(@_); } sub _PREPARE_SELF { my ($class, $dbh_ref, $module_name, $db_name, $vtab_name, @args) = @_; my @columns; my %options; # args containing '=' are options; others are column declarations foreach my $arg (@args) { if ($arg =~ /^([^=\s]+)\s*=\s*(.*)/) { my ($key, $val) = ($1, $2); $val =~ s/^"(.*)"$/$1/; $options{$key} = $val; } else { push @columns, $arg; } } # build $self my $self = { dbh_ref => $dbh_ref, module_name => $module_name, db_name => $db_name, vtab_name => $vtab_name, columns => \@columns, options => \%options, }; weaken $self->{dbh_ref}; return $self; } sub NEW { my $class = shift; my $self = $class->_PREPARE_SELF(@_); bless $self, $class; } sub VTAB_TO_DECLARE { my $self = shift; local $" = ", "; my $sql = "CREATE TABLE $self->{vtab_name}(@{$self->{columns}})"; return $sql; } sub DROP { my $self = shift; } sub DISCONNECT { my $self = shift; } #---------------------------------------------------------------------- # methods for initiating a search #---------------------------------------------------------------------- sub BEST_INDEX { my ($self, $constraints, $order_by) = @_; my $ix = 0; foreach my $constraint (grep {$_->{usable}} @$constraints) { $constraint->{argvIndex} = $ix++; $constraint->{omit} = 0; } # stupid default values -- subclasses should put real values instead my $outputs = { idxNum => 1, idxStr => "", orderByConsumed => 0, estimatedCost => 1.0, estimatedRows => undef, }; return $outputs; } sub OPEN { my $self = shift; my $class = ref $self; my $cursor_class = $class . "::Cursor"; return $cursor_class->NEW($self, @_); } #---------------------------------------------------------------------- # methods for insert/delete/update #---------------------------------------------------------------------- sub _SQLITE_UPDATE { my ($self, $old_rowid, $new_rowid, @values) = @_; if (! defined $old_rowid) { return $self->INSERT($new_rowid, @values); } elsif (!@values) { return $self->DELETE($old_rowid); } else { return $self->UPDATE($old_rowid, $new_rowid, @values); } } sub INSERT { my ($self, $new_rowid, @values) = @_; die "INSERT() should be redefined in subclass"; } sub DELETE { my ($self, $old_rowid) = @_; die "DELETE() should be redefined in subclass"; } sub UPDATE { my ($self, $old_rowid, $new_rowid, @values) = @_; die "UPDATE() should be redefined in subclass"; } #---------------------------------------------------------------------- # remaining methods of the sqlite API #---------------------------------------------------------------------- sub BEGIN_TRANSACTION {return 0} sub SYNC_TRANSACTION {return 0} sub COMMIT_TRANSACTION {return 0} sub ROLLBACK_TRANSACTION {return 0} sub SAVEPOINT {return 0} sub RELEASE {return 0} sub ROLLBACK_TO {return 0} sub FIND_FUNCTION {return 0} sub RENAME {return 0} #---------------------------------------------------------------------- # utility methods #---------------------------------------------------------------------- sub dbh { my $self = shift; return ${$self->{dbh_ref}}; } sub sqlite_table_info { my $self = shift; my $sql = "PRAGMA table_info($self->{vtab_name})"; return $self->dbh->selectall_arrayref($sql, {Slice => {}}); } #====================================================================== package DBD::SQLite::VirtualTable::Cursor; #====================================================================== use strict; use warnings; sub NEW { my ($class, $vtable, @args) = @_; my $self = {vtable => $vtable, args => \@args}; bless $self, $class; } sub FILTER { my ($self, $idxNum, $idxStr, @values) = @_; die "FILTER() should be redefined in cursor subclass"; } sub EOF { my ($self) = @_; die "EOF() should be redefined in cursor subclass"; } sub NEXT { my ($self) = @_; die "NEXT() should be redefined in cursor subclass"; } sub COLUMN { my ($self, $idxCol) = @_; die "COLUMN() should be redefined in cursor subclass"; } sub ROWID { my ($self) = @_; die "ROWID() should be redefined in cursor subclass"; } 1; __END__ =head1 NAME DBD::SQLite::VirtualTable -- SQLite virtual tables implemented in Perl =head1 SYNOPSIS # register the virtual table module within sqlite $dbh->sqlite_create_module(mod_name => "DBD::SQLite::VirtualTable::Subclass"); # create a virtual table $dbh->do("CREATE VIRTUAL TABLE vtbl USING mod_name(arg1, arg2, ...)") # use it as any regular table my $sth = $dbh->prepare("SELECT * FROM vtbl WHERE ..."); B : VirtualTable subclasses or instances are not called directly from Perl code; everything happens indirectly through SQL statements within SQLite. =head1 DESCRIPTION This module is an abstract class for implementing SQLite virtual tables, written in Perl. Such tables look like regular tables, and are accessed through regular SQL instructions and regular L API; but the implementation is done through hidden calls to a Perl class. This is the same idea as Perl's L, but at the SQLite level. The current abstract class cannot be used directly, so the synopsis above is just to give a general idea. Concrete, usable classes bundled with the present distribution are : =over =item * L : implements a virtual column that exposes file contents. This is especially useful in conjunction with a fulltext index; see L. =item * L : 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. The following chapters document the structure of the abstract class and explain how to write new subclasses; this is meant for B, not for end users. If you just need to use a virtual table module, refer to that module's documentation. =head1 ARCHITECTURE =head2 Classes A virtual table module for SQLite is implemented through a pair of classes : =over =item * the B class implements methods for creating or connecting a virtual table, for destroying it, for opening new searches, etc. =item * the B class implements methods for performing a specific SQL statement =back =head2 Methods Most methods in both classes are not called directly from Perl code : instead, they are callbacks, called from the sqlite kernel. Following common Perl conventions, such methods have names in uppercase. =head1 TABLE METHODS =head2 Class methods for registering the module =head3 CREATE_MODULE $class->CREATE_MODULE($sqlite_module_name); Called when the client code invokes $dbh->sqlite_create_module($sqlite_module_name => $class); The default implementation is empty. =head3 DESTROY_MODULE $class->DESTROY_MODULE(); Called automatically when the database handle is disconnected. The default implementation is empty. =head2 Class methods for creating a vtable instance =head3 CREATE $class->CREATE($dbh_ref, $module_name, $db_name, $vtab_name, @args); Called when sqlite receives a statement CREATE VIRTUAL TABLE $db_name.$vtab_name USING $module_name(@args) The default implementation just calls L. =head3 CONNECT $class->CONNECT($dbh_ref, $module_name, $db_name, $vtab_name, @args); Called when attempting to access a virtual table that had been created during previous database connection. The creation arguments were stored within the sqlite database and are passed again to the CONNECT method. The default implementation just calls L. =head3 _PREPARE_SELF $class->_PREPARE_SELF($dbh_ref, $module_name, $db_name, $vtab_name, @args); Prepares the datastructure for a virtual table instance. C<@args> is just the collection of strings (comma-separated) that were given within the C statement; each subclass should decide what to do with this information, The method parses C<@args> to differentiate between I (strings of shape C<$key>=C<$value> or C<$key>=C<"$value">, stored in C<< $self->{options} >>), and I (other C<@args>, stored in C<< $self->{columns} >>). It creates a hashref with the following fields : =over =item C a weak reference to the C<$dbh> database handle (see L for an explanation of weak references). =item C name of the module as declared to sqlite (not to be confounded with the Perl class name). =item C name of the database (usuallly C<'main'> or C<'temp'>), but it may also be an attached database =item C name of the virtual table =item C arrayref of column declarations =item C hashref of option declarations =back This method should not be redefined, since it performs general work which is supposed to be useful for all subclasses. Instead, subclasses may override the L method. =head3 NEW $class->NEW($dbh_ref, $module_name, $db_name, $vtab_name, @args); Instantiates a virtual table. =head2 Instance methods called from the sqlite kernel =head3 DROP Called whenever a virtual table is destroyed from the database through the C SQL instruction. Just after the C call, the Perl instance will be destroyed (and will therefore automatically call the C method if such a method is present). The default implementation for DROP is empty. B : this corresponds to the C method in the SQLite documentation; here it was not named C, to avoid any confusion with the standard Perl method C for object destruction. =head3 DISCONNECT Called for every virtual table just before the database handle is disconnected. Just after the C call, the Perl instance will be destroyed (and will therefore automatically call the C method if such a method is present). The default implementation for DISCONNECT is empty. =head3 VTAB_TO_DECLARE This method is called automatically just after L or L, to register the columns of the virtual table within the sqlite kernel. The method should return a string containing a SQL C statement; but only the column declaration parts will be considered. Columns may be declared with the special keyword "HIDDEN", which means that they are used internally for the the virtual table implementation, and are not visible to users -- see L and L for detailed explanations. The default implementation returns: CREATE TABLE $self->{vtab_name}(@{$self->{columns}}) =head3 BEST_INDEX my $index_info = $vtab->BEST_INDEX($constraints, $order_by) This is the most complex method to redefined in subclasses. This method will be called at the beginning of a new query on the virtual table; the job of the method is to assemble some information that will be used =over =item a) by the sqlite kernel to decide about the best search strategy =item b) by the cursor L method to produce the desired subset of rows from the virtual table. =back By calling this method, the SQLite core is saying to the virtual table that it needs to access some subset of the rows in the virtual table and it wants to know the most efficient way to do that access. The C method replies with information that the SQLite core can then use to conduct an efficient search of the virtual table. The method takes as input a list of C<$constraints> and a list of C<$order_by> instructions. It returns a hashref of indexing properties, described below; furthermore, the method also adds supplementary information within the input C<$constraints>. Detailed explanations are given in L. =head4 Input constraints Elements of the C<$constraints> arrayref correspond to specific clauses of the C part of the SQL query. Each constraint is a hashref with keys : =over =item Cthe integer index of the column on the left-hand side of the constraint =item C the comparison operator, expressed as string containing C<< '=' >>, C<< '>' >>, C<< '>=' >>, C<< '<' >>, C<< '<=' >> or C<< 'MATCH' >>. =item C a boolean indicating if that constraint is usable; some constraints might not be usable because of the way tables are ordered in a join. =back The C<$constraints> arrayref is used both for input and for output. While iterating over the array, the method should add the following keys into usable constraints : =over =item C An index into the C<@values> array that will be passed to the cursor's L method. In other words, if the current constraint corresponds to the SQL fragment C, and the corresponding C takes value 5, this means that the C method will receive C<123> in C<$values[5]>. =item C A boolean telling to the sqlite core that it can safely omit to double check that constraint before returning the resultset to the calling program; this means that the FILTER method has fulfilled the filtering job on that constraint and there is no need to do any further checking. =back The C method will not necessarily receive all constraints from the SQL C clause : for example a constraint like C<< col1 < col2 + col3 >> cannot be handled at this level. Furthemore, the C might decide to ignore some of the received constraints. This is why a second pass over the results will be performed by the sqlite core. =head4 "order_by" input information The C<$order_by> arrayref corresponds to the C clauses in the SQL query. Each entry is a hashref with keys : =over =item Cthe integer index of the column being ordered =item C a boolean telling of the ordering is DESCending or ascending =back This information could be used by some subclasses for optimizing the query strategfy; but usually the sqlite core will perform another sorting pass once all results are gathered. =head4 Hashref information returned by BEST_INDEX The method should return a hashref with the following keys : =over =item C An arbitrary integer associated with that index; this information will be passed back to L. =item C An arbitrary str associated with that index; this information will be passed back to L. =item C A boolean telling the sqlite core if the C<$order_by> information has been taken into account or not. =item C A float that should be set to the estimated number of disk access operations required to execute this query against the virtual table. The SQLite core will often call BEST_INDEX multiple times with different constraints, obtain multiple cost estimates, then choose the query plan that gives the lowest estimate. =item C An integer giving the estimated number of rows returned by that query. =back =head3 OPEN Called to instantiate a new cursor. The default implementation appends C<"::Cursor"> to the current classname and calls C within that cursor class. =head3 _SQLITE_UPDATE This is the dispatch method implementing the C callback for virtual tables. The default implementation applies the algorithm described in L to decide to call L, L or L; so there is no reason to override this method in subclasses. =head3 INSERT my $rowid = $vtab->INSERT($new_rowid, @values); This method should be overridden in subclasses to implement insertion of a new row into the virtual table. The size of the C<@values> array corresponds to the number of columns declared through L. The C<$new_rowid> may be explicitly given, or it may be C, in which case the method must compute a new id and return it as the result of the method call. =head3 DELETE $vtab->INSERT($old_rowid); This method should be overridden in subclasses to implement deletion of a row from the virtual table. =head3 UPDATE $vtab->UPDATE($old_rowid, $new_rowid, @values); This method should be overridden in subclasses to implement a row update within the virtual table. Usually C<$old_rowid> is equal to C<$new_rowid>, which is a regular update; however, the rowid could be changed from a SQL statement such as UPDATE table SET rowid=rowid+1 WHERE ...; =head3 FIND_FUNCTION $vtab->FIND_FUNCTION($num_args, $func_name); When a function uses a column from a virtual table as its first argument, this method is called to see if the virtual table would like to overload the function. Parameters are the number of arguments to the function, and the name of the function. If no overloading is desired, this method should return false. To overload the function, this method should return a coderef to the function implementation. Each virtual table keeps a cache of results from L calls, so the method will be called only once for each pair C<< ($num_args, $func_name) >>. =head3 BEGIN_TRANSACTION Called to begin a transaction on the virtual table. =head3 SYNC_TRANSACTION Called to signal the start of a two-phase commit on the virtual table. =head3 SYNC_TRANSACTION Called to commit a virtual table transaction. =head3 ROLLBACK_TRANSACTION Called to rollback a virtual table transaction. =head3 RENAME $vtab->RENAME($new_name) Called to rename a virtual table. =head3 SAVEPOINT $vtab->SAVEPOINT($savepoint) Called to signal the virtual table to save its current state at savepoint C<$savepoint> (an integer). =head3 ROLLBACK_TO $vtab->ROLLBACK_TO($savepoint) Called to signal the virtual table to return to the state C<$savepoint>. This will invalidate all savepoints with values greater than C<$savepoint>. =head3 RELEASE $vtab->RELEASE($savepoint) Called to invalidate all savepoints with values greater or equal to C<$savepoint>. =head2 Utility instance methods Methods in this section are in lower case, because they are not called directly from the sqlite kernel; these are utility methods to be called from other methods described above. =head3 dbh This method returns the database handle (C<$dbh>) associated with the current virtual table. =head1 CURSOR METHODS =head2 Class methods =head3 NEW my $cursor = $cursor_class->NEW($vtable, @args) Instantiates a new cursor. The default implementation just returns a blessed hashref with keys C and C. =head2 Instance methods =head3 FILTER $cursor->FILTER($idxNum, $idxStr, @values); This method begins a search of a virtual table. The C<$idxNum> and C<$idxStr> arguments correspond to values returned by L for the chosen index. The specific meanings of those values are unimportant to SQLite, as long as C and C agree on what that meaning is. The C method may have requested the values of certain expressions using the C values of the C<$constraints> list. Those values are passed to C through the C<@values> array. If the virtual table contains one or more rows that match the search criteria, then the cursor must be left point at the first row. Subsequent calls to L must return false. If there are no rows match, then the cursor must be left in a state that will cause L to return true. The SQLite engine will use the L and L methods to access that row content. The L method will be used to advance to the next row. =head3 EOF This method must return false if the cursor currently points to a valid row of data, or true otherwise. This method is called by the SQL engine immediately after each L and L invocation. =head3 NEXT This method advances the cursor to the next row of a result set initiated by L. If the cursor is already pointing at the last row when this method is called, then the cursor no longer points to valid data and a subsequent call to the L method must return true. If the cursor is successfully advanced to another row of content, then subsequent calls to L must return false. =head3 COLUMN my $value = $cursor->COLUMN($idxCol); The SQLite core invokes this method in order to find the value for the N-th column of the current row. N is zero-based so the first column is numbered 0. =head3 ROWID my $value = $cursor->ROWID; Returns the I of row that the cursor is currently pointing at. =head1 SEE ALSO L is another module for virtual tables written in Perl, but designed for the reverse use case : instead of starting a Perl program, and embedding the SQLite library into it, the intended use is to start an sqlite program, and embed the Perl interpreter into it. =head1 AUTHOR Laurent Dami Edami@cpan.orgE =head1 COPYRIGHT AND LICENSE Copyright Laurent Dami, 2014. Parts of the code are borrowed from L, copyright (C) 2006, 2009 by Qindel Formacion y Servicios, S. L. This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself. =cut SQLite/Constants.pm000044400000062403152344563120010227 0ustar00package DBD::SQLite::Constants; # This module is generated by a script. # Do not edit manually. use strict; use warnings; use base 'Exporter'; use DBD::SQLite; our @EXPORT_OK = ( 'DBD_SQLITE_STRING_MODE_PV', 'DBD_SQLITE_STRING_MODE_BYTES', 'DBD_SQLITE_STRING_MODE_UNICODE_NAIVE', 'DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK', 'DBD_SQLITE_STRING_MODE_UNICODE_STRICT', # allowed_return_values_from_sqlite3_txn_state qw/ SQLITE_TXN_NONE SQLITE_TXN_READ SQLITE_TXN_WRITE /, # authorizer_action_codes qw/ SQLITE_ALTER_TABLE SQLITE_ANALYZE SQLITE_ATTACH SQLITE_COPY SQLITE_CREATE_INDEX SQLITE_CREATE_TABLE SQLITE_CREATE_TEMP_INDEX SQLITE_CREATE_TEMP_TABLE SQLITE_CREATE_TEMP_TRIGGER SQLITE_CREATE_TEMP_VIEW SQLITE_CREATE_TRIGGER SQLITE_CREATE_VIEW SQLITE_CREATE_VTABLE SQLITE_DELETE SQLITE_DETACH SQLITE_DROP_INDEX SQLITE_DROP_TABLE SQLITE_DROP_TEMP_INDEX SQLITE_DROP_TEMP_TABLE SQLITE_DROP_TEMP_TRIGGER SQLITE_DROP_TEMP_VIEW SQLITE_DROP_TRIGGER SQLITE_DROP_VIEW SQLITE_DROP_VTABLE SQLITE_FUNCTION SQLITE_INSERT SQLITE_PRAGMA SQLITE_READ SQLITE_RECURSIVE SQLITE_REINDEX SQLITE_SAVEPOINT SQLITE_SELECT SQLITE_TRANSACTION SQLITE_UPDATE /, # authorizer_return_codes qw/ SQLITE_DENY SQLITE_IGNORE /, # compile_time_library_version_numbers qw/ SQLITE_VERSION_NUMBER /, # database_connection_configuration_options qw/ SQLITE_DBCONFIG_DEFENSIVE SQLITE_DBCONFIG_DQS_DDL SQLITE_DBCONFIG_DQS_DML SQLITE_DBCONFIG_ENABLE_FKEY SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION SQLITE_DBCONFIG_ENABLE_QPSG SQLITE_DBCONFIG_ENABLE_TRIGGER SQLITE_DBCONFIG_ENABLE_VIEW SQLITE_DBCONFIG_LEGACY_ALTER_TABLE SQLITE_DBCONFIG_LEGACY_FILE_FORMAT SQLITE_DBCONFIG_LOOKASIDE SQLITE_DBCONFIG_MAINDBNAME SQLITE_DBCONFIG_MAX SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE SQLITE_DBCONFIG_RESET_DATABASE SQLITE_DBCONFIG_REVERSE_SCANORDER SQLITE_DBCONFIG_STMT_SCANSTATUS SQLITE_DBCONFIG_TRIGGER_EQP SQLITE_DBCONFIG_TRUSTED_SCHEMA SQLITE_DBCONFIG_WRITABLE_SCHEMA /, # extended_result_codes qw/ SQLITE_ABORT_ROLLBACK SQLITE_AUTH_USER SQLITE_BUSY_RECOVERY SQLITE_BUSY_SNAPSHOT SQLITE_BUSY_TIMEOUT SQLITE_CANTOPEN_CONVPATH SQLITE_CANTOPEN_DIRTYWAL SQLITE_CANTOPEN_FULLPATH SQLITE_CANTOPEN_ISDIR SQLITE_CANTOPEN_NOTEMPDIR SQLITE_CANTOPEN_SYMLINK SQLITE_CONSTRAINT_CHECK SQLITE_CONSTRAINT_COMMITHOOK SQLITE_CONSTRAINT_DATATYPE SQLITE_CONSTRAINT_FOREIGNKEY SQLITE_CONSTRAINT_FUNCTION SQLITE_CONSTRAINT_NOTNULL SQLITE_CONSTRAINT_PINNED SQLITE_CONSTRAINT_PRIMARYKEY SQLITE_CONSTRAINT_ROWID SQLITE_CONSTRAINT_TRIGGER SQLITE_CONSTRAINT_UNIQUE SQLITE_CONSTRAINT_VTAB SQLITE_CORRUPT_INDEX SQLITE_CORRUPT_SEQUENCE SQLITE_CORRUPT_VTAB SQLITE_ERROR_MISSING_COLLSEQ SQLITE_ERROR_RETRY SQLITE_ERROR_SNAPSHOT SQLITE_IOERR_ACCESS SQLITE_IOERR_AUTH SQLITE_IOERR_BEGIN_ATOMIC SQLITE_IOERR_BLOCKED SQLITE_IOERR_CHECKRESERVEDLOCK SQLITE_IOERR_CLOSE SQLITE_IOERR_COMMIT_ATOMIC SQLITE_IOERR_CONVPATH SQLITE_IOERR_CORRUPTFS SQLITE_IOERR_DATA SQLITE_IOERR_DELETE SQLITE_IOERR_DELETE_NOENT SQLITE_IOERR_DIR_CLOSE SQLITE_IOERR_DIR_FSYNC SQLITE_IOERR_FSTAT SQLITE_IOERR_FSYNC SQLITE_IOERR_GETTEMPPATH SQLITE_IOERR_IN_PAGE SQLITE_IOERR_LOCK SQLITE_IOERR_MMAP SQLITE_IOERR_NOMEM SQLITE_IOERR_RDLOCK SQLITE_IOERR_READ SQLITE_IOERR_ROLLBACK_ATOMIC SQLITE_IOERR_SEEK SQLITE_IOERR_SHMLOCK SQLITE_IOERR_SHMMAP SQLITE_IOERR_SHMOPEN SQLITE_IOERR_SHMSIZE SQLITE_IOERR_SHORT_READ SQLITE_IOERR_TRUNCATE SQLITE_IOERR_UNLOCK SQLITE_IOERR_VNODE SQLITE_IOERR_WRITE SQLITE_LOCKED_SHAREDCACHE SQLITE_LOCKED_VTAB SQLITE_NOTICE_RBU SQLITE_NOTICE_RECOVER_ROLLBACK SQLITE_NOTICE_RECOVER_WAL SQLITE_OK_SYMLINK SQLITE_READONLY_CANTINIT SQLITE_READONLY_CANTLOCK SQLITE_READONLY_DBMOVED SQLITE_READONLY_DIRECTORY SQLITE_READONLY_RECOVERY SQLITE_READONLY_ROLLBACK SQLITE_WARNING_AUTOINDEX /, # flags_for_file_open_operations qw/ SQLITE_OPEN_CREATE SQLITE_OPEN_EXRESCODE SQLITE_OPEN_FULLMUTEX SQLITE_OPEN_MEMORY SQLITE_OPEN_NOFOLLOW SQLITE_OPEN_NOMUTEX SQLITE_OPEN_PRIVATECACHE SQLITE_OPEN_READONLY SQLITE_OPEN_READWRITE SQLITE_OPEN_SHAREDCACHE SQLITE_OPEN_SUPER_JOURNAL SQLITE_OPEN_URI /, # function_flags qw/ SQLITE_DETERMINISTIC SQLITE_DIRECTONLY SQLITE_INNOCUOUS SQLITE_RESULT_SUBTYPE SQLITE_SUBTYPE /, # fundamental_datatypes qw/ SQLITE_BLOB SQLITE_FLOAT SQLITE_INTEGER SQLITE_NULL SQLITE_TEXT /, # result_codes qw/ SQLITE_ABORT SQLITE_AUTH SQLITE_BUSY SQLITE_CANTOPEN SQLITE_CONSTRAINT SQLITE_CORRUPT SQLITE_DONE SQLITE_EMPTY SQLITE_ERROR SQLITE_FORMAT SQLITE_FULL SQLITE_INTERNAL SQLITE_INTERRUPT SQLITE_IOERR SQLITE_LOCKED SQLITE_MISMATCH SQLITE_MISUSE SQLITE_NOLFS SQLITE_NOMEM SQLITE_NOTADB SQLITE_NOTFOUND SQLITE_NOTICE SQLITE_OK SQLITE_PERM SQLITE_PROTOCOL SQLITE_RANGE SQLITE_READONLY SQLITE_ROW SQLITE_SCHEMA SQLITE_TOOBIG SQLITE_WARNING /, # run_time_limit_categories qw/ SQLITE_LIMIT_ATTACHED SQLITE_LIMIT_COLUMN SQLITE_LIMIT_COMPOUND_SELECT SQLITE_LIMIT_EXPR_DEPTH SQLITE_LIMIT_FUNCTION_ARG SQLITE_LIMIT_LENGTH SQLITE_LIMIT_LIKE_PATTERN_LENGTH SQLITE_LIMIT_SQL_LENGTH SQLITE_LIMIT_TRIGGER_DEPTH SQLITE_LIMIT_VARIABLE_NUMBER SQLITE_LIMIT_VDBE_OP SQLITE_LIMIT_WORKER_THREADS /, ); our %EXPORT_TAGS = ( all => [qw/ SQLITE_ABORT SQLITE_ABORT_ROLLBACK SQLITE_ALTER_TABLE SQLITE_ANALYZE SQLITE_ATTACH SQLITE_AUTH SQLITE_AUTH_USER SQLITE_BLOB SQLITE_BUSY SQLITE_BUSY_RECOVERY SQLITE_BUSY_SNAPSHOT SQLITE_BUSY_TIMEOUT SQLITE_CANTOPEN SQLITE_CANTOPEN_CONVPATH SQLITE_CANTOPEN_DIRTYWAL SQLITE_CANTOPEN_FULLPATH SQLITE_CANTOPEN_ISDIR SQLITE_CANTOPEN_NOTEMPDIR SQLITE_CANTOPEN_SYMLINK SQLITE_CONSTRAINT SQLITE_CONSTRAINT_CHECK SQLITE_CONSTRAINT_COMMITHOOK SQLITE_CONSTRAINT_DATATYPE SQLITE_CONSTRAINT_FOREIGNKEY SQLITE_CONSTRAINT_FUNCTION SQLITE_CONSTRAINT_NOTNULL SQLITE_CONSTRAINT_PINNED SQLITE_CONSTRAINT_PRIMARYKEY SQLITE_CONSTRAINT_ROWID SQLITE_CONSTRAINT_TRIGGER SQLITE_CONSTRAINT_UNIQUE SQLITE_CONSTRAINT_VTAB SQLITE_COPY SQLITE_CORRUPT SQLITE_CORRUPT_INDEX SQLITE_CORRUPT_SEQUENCE SQLITE_CORRUPT_VTAB SQLITE_CREATE_INDEX SQLITE_CREATE_TABLE SQLITE_CREATE_TEMP_INDEX SQLITE_CREATE_TEMP_TABLE SQLITE_CREATE_TEMP_TRIGGER SQLITE_CREATE_TEMP_VIEW SQLITE_CREATE_TRIGGER SQLITE_CREATE_VIEW SQLITE_CREATE_VTABLE SQLITE_DBCONFIG_DEFENSIVE SQLITE_DBCONFIG_DQS_DDL SQLITE_DBCONFIG_DQS_DML SQLITE_DBCONFIG_ENABLE_FKEY SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION SQLITE_DBCONFIG_ENABLE_QPSG SQLITE_DBCONFIG_ENABLE_TRIGGER SQLITE_DBCONFIG_ENABLE_VIEW SQLITE_DBCONFIG_LEGACY_ALTER_TABLE SQLITE_DBCONFIG_LEGACY_FILE_FORMAT SQLITE_DBCONFIG_LOOKASIDE SQLITE_DBCONFIG_MAINDBNAME SQLITE_DBCONFIG_MAX SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE SQLITE_DBCONFIG_RESET_DATABASE SQLITE_DBCONFIG_REVERSE_SCANORDER SQLITE_DBCONFIG_STMT_SCANSTATUS SQLITE_DBCONFIG_TRIGGER_EQP SQLITE_DBCONFIG_TRUSTED_SCHEMA SQLITE_DBCONFIG_WRITABLE_SCHEMA DBD_SQLITE_STRING_MODE_BYTES DBD_SQLITE_STRING_MODE_PV DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK DBD_SQLITE_STRING_MODE_UNICODE_NAIVE DBD_SQLITE_STRING_MODE_UNICODE_STRICT SQLITE_DELETE SQLITE_DENY SQLITE_DETACH SQLITE_DETERMINISTIC SQLITE_DIRECTONLY SQLITE_DONE SQLITE_DROP_INDEX SQLITE_DROP_TABLE SQLITE_DROP_TEMP_INDEX SQLITE_DROP_TEMP_TABLE SQLITE_DROP_TEMP_TRIGGER SQLITE_DROP_TEMP_VIEW SQLITE_DROP_TRIGGER SQLITE_DROP_VIEW SQLITE_DROP_VTABLE SQLITE_EMPTY SQLITE_ERROR SQLITE_ERROR_MISSING_COLLSEQ SQLITE_ERROR_RETRY SQLITE_ERROR_SNAPSHOT SQLITE_FLOAT SQLITE_FORMAT SQLITE_FULL SQLITE_FUNCTION SQLITE_IGNORE SQLITE_INNOCUOUS SQLITE_INSERT SQLITE_INTEGER SQLITE_INTERNAL SQLITE_INTERRUPT SQLITE_IOERR SQLITE_IOERR_ACCESS SQLITE_IOERR_AUTH SQLITE_IOERR_BEGIN_ATOMIC SQLITE_IOERR_BLOCKED SQLITE_IOERR_CHECKRESERVEDLOCK SQLITE_IOERR_CLOSE SQLITE_IOERR_COMMIT_ATOMIC SQLITE_IOERR_CONVPATH SQLITE_IOERR_CORRUPTFS SQLITE_IOERR_DATA SQLITE_IOERR_DELETE SQLITE_IOERR_DELETE_NOENT SQLITE_IOERR_DIR_CLOSE SQLITE_IOERR_DIR_FSYNC SQLITE_IOERR_FSTAT SQLITE_IOERR_FSYNC SQLITE_IOERR_GETTEMPPATH SQLITE_IOERR_IN_PAGE SQLITE_IOERR_LOCK SQLITE_IOERR_MMAP SQLITE_IOERR_NOMEM SQLITE_IOERR_RDLOCK SQLITE_IOERR_READ SQLITE_IOERR_ROLLBACK_ATOMIC SQLITE_IOERR_SEEK SQLITE_IOERR_SHMLOCK SQLITE_IOERR_SHMMAP SQLITE_IOERR_SHMOPEN SQLITE_IOERR_SHMSIZE SQLITE_IOERR_SHORT_READ SQLITE_IOERR_TRUNCATE SQLITE_IOERR_UNLOCK SQLITE_IOERR_VNODE SQLITE_IOERR_WRITE SQLITE_LIMIT_ATTACHED SQLITE_LIMIT_COLUMN SQLITE_LIMIT_COMPOUND_SELECT SQLITE_LIMIT_EXPR_DEPTH SQLITE_LIMIT_FUNCTION_ARG SQLITE_LIMIT_LENGTH SQLITE_LIMIT_LIKE_PATTERN_LENGTH SQLITE_LIMIT_SQL_LENGTH SQLITE_LIMIT_TRIGGER_DEPTH SQLITE_LIMIT_VARIABLE_NUMBER SQLITE_LIMIT_VDBE_OP SQLITE_LIMIT_WORKER_THREADS SQLITE_LOCKED SQLITE_LOCKED_SHAREDCACHE SQLITE_LOCKED_VTAB SQLITE_MISMATCH SQLITE_MISUSE SQLITE_NOLFS SQLITE_NOMEM SQLITE_NOTADB SQLITE_NOTFOUND SQLITE_NOTICE SQLITE_NOTICE_RBU SQLITE_NOTICE_RECOVER_ROLLBACK SQLITE_NOTICE_RECOVER_WAL SQLITE_NULL SQLITE_OK SQLITE_OK_SYMLINK SQLITE_OPEN_CREATE SQLITE_OPEN_EXRESCODE SQLITE_OPEN_FULLMUTEX SQLITE_OPEN_MEMORY SQLITE_OPEN_NOFOLLOW SQLITE_OPEN_NOMUTEX SQLITE_OPEN_PRIVATECACHE SQLITE_OPEN_READONLY SQLITE_OPEN_READWRITE SQLITE_OPEN_SHAREDCACHE SQLITE_OPEN_SUPER_JOURNAL SQLITE_OPEN_URI SQLITE_PERM SQLITE_PRAGMA SQLITE_PROTOCOL SQLITE_RANGE SQLITE_READ SQLITE_READONLY SQLITE_READONLY_CANTINIT SQLITE_READONLY_CANTLOCK SQLITE_READONLY_DBMOVED SQLITE_READONLY_DIRECTORY SQLITE_READONLY_RECOVERY SQLITE_READONLY_ROLLBACK SQLITE_RECURSIVE SQLITE_REINDEX SQLITE_RESULT_SUBTYPE SQLITE_ROW SQLITE_SAVEPOINT SQLITE_SCHEMA SQLITE_SELECT SQLITE_SUBTYPE SQLITE_TEXT SQLITE_TOOBIG SQLITE_TRANSACTION SQLITE_TXN_NONE SQLITE_TXN_READ SQLITE_TXN_WRITE SQLITE_UPDATE SQLITE_VERSION_NUMBER SQLITE_WARNING SQLITE_WARNING_AUTOINDEX /], allowed_return_values_from_sqlite3_txn_state => [qw/ SQLITE_TXN_NONE SQLITE_TXN_READ SQLITE_TXN_WRITE /], authorizer_action_codes => [qw/ SQLITE_ALTER_TABLE SQLITE_ANALYZE SQLITE_ATTACH SQLITE_COPY SQLITE_CREATE_INDEX SQLITE_CREATE_TABLE SQLITE_CREATE_TEMP_INDEX SQLITE_CREATE_TEMP_TABLE SQLITE_CREATE_TEMP_TRIGGER SQLITE_CREATE_TEMP_VIEW SQLITE_CREATE_TRIGGER SQLITE_CREATE_VIEW SQLITE_CREATE_VTABLE SQLITE_DELETE SQLITE_DETACH SQLITE_DROP_INDEX SQLITE_DROP_TABLE SQLITE_DROP_TEMP_INDEX SQLITE_DROP_TEMP_TABLE SQLITE_DROP_TEMP_TRIGGER SQLITE_DROP_TEMP_VIEW SQLITE_DROP_TRIGGER SQLITE_DROP_VIEW SQLITE_DROP_VTABLE SQLITE_FUNCTION SQLITE_INSERT SQLITE_PRAGMA SQLITE_READ SQLITE_RECURSIVE SQLITE_REINDEX SQLITE_SAVEPOINT SQLITE_SELECT SQLITE_TRANSACTION SQLITE_UPDATE /], authorizer_return_codes => [qw/ SQLITE_DENY SQLITE_IGNORE /], compile_time_library_version_numbers => [qw/ SQLITE_VERSION_NUMBER /], database_connection_configuration_options => [qw/ SQLITE_DBCONFIG_DEFENSIVE SQLITE_DBCONFIG_DQS_DDL SQLITE_DBCONFIG_DQS_DML SQLITE_DBCONFIG_ENABLE_FKEY SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION SQLITE_DBCONFIG_ENABLE_QPSG SQLITE_DBCONFIG_ENABLE_TRIGGER SQLITE_DBCONFIG_ENABLE_VIEW SQLITE_DBCONFIG_LEGACY_ALTER_TABLE SQLITE_DBCONFIG_LEGACY_FILE_FORMAT SQLITE_DBCONFIG_LOOKASIDE SQLITE_DBCONFIG_MAINDBNAME SQLITE_DBCONFIG_MAX SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE SQLITE_DBCONFIG_RESET_DATABASE SQLITE_DBCONFIG_REVERSE_SCANORDER SQLITE_DBCONFIG_STMT_SCANSTATUS SQLITE_DBCONFIG_TRIGGER_EQP SQLITE_DBCONFIG_TRUSTED_SCHEMA SQLITE_DBCONFIG_WRITABLE_SCHEMA /], dbd_sqlite_string_mode => [qw/ DBD_SQLITE_STRING_MODE_BYTES DBD_SQLITE_STRING_MODE_PV DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK DBD_SQLITE_STRING_MODE_UNICODE_NAIVE DBD_SQLITE_STRING_MODE_UNICODE_STRICT /], extended_result_codes => [qw/ SQLITE_ABORT_ROLLBACK SQLITE_AUTH_USER SQLITE_BUSY_RECOVERY SQLITE_BUSY_SNAPSHOT SQLITE_BUSY_TIMEOUT SQLITE_CANTOPEN_CONVPATH SQLITE_CANTOPEN_DIRTYWAL SQLITE_CANTOPEN_FULLPATH SQLITE_CANTOPEN_ISDIR SQLITE_CANTOPEN_NOTEMPDIR SQLITE_CANTOPEN_SYMLINK SQLITE_CONSTRAINT_CHECK SQLITE_CONSTRAINT_COMMITHOOK SQLITE_CONSTRAINT_DATATYPE SQLITE_CONSTRAINT_FOREIGNKEY SQLITE_CONSTRAINT_FUNCTION SQLITE_CONSTRAINT_NOTNULL SQLITE_CONSTRAINT_PINNED SQLITE_CONSTRAINT_PRIMARYKEY SQLITE_CONSTRAINT_ROWID SQLITE_CONSTRAINT_TRIGGER SQLITE_CONSTRAINT_UNIQUE SQLITE_CONSTRAINT_VTAB SQLITE_CORRUPT_INDEX SQLITE_CORRUPT_SEQUENCE SQLITE_CORRUPT_VTAB SQLITE_ERROR_MISSING_COLLSEQ SQLITE_ERROR_RETRY SQLITE_ERROR_SNAPSHOT SQLITE_IOERR_ACCESS SQLITE_IOERR_AUTH SQLITE_IOERR_BEGIN_ATOMIC SQLITE_IOERR_BLOCKED SQLITE_IOERR_CHECKRESERVEDLOCK SQLITE_IOERR_CLOSE SQLITE_IOERR_COMMIT_ATOMIC SQLITE_IOERR_CONVPATH SQLITE_IOERR_CORRUPTFS SQLITE_IOERR_DATA SQLITE_IOERR_DELETE SQLITE_IOERR_DELETE_NOENT SQLITE_IOERR_DIR_CLOSE SQLITE_IOERR_DIR_FSYNC SQLITE_IOERR_FSTAT SQLITE_IOERR_FSYNC SQLITE_IOERR_GETTEMPPATH SQLITE_IOERR_IN_PAGE SQLITE_IOERR_LOCK SQLITE_IOERR_MMAP SQLITE_IOERR_NOMEM SQLITE_IOERR_RDLOCK SQLITE_IOERR_READ SQLITE_IOERR_ROLLBACK_ATOMIC SQLITE_IOERR_SEEK SQLITE_IOERR_SHMLOCK SQLITE_IOERR_SHMMAP SQLITE_IOERR_SHMOPEN SQLITE_IOERR_SHMSIZE SQLITE_IOERR_SHORT_READ SQLITE_IOERR_TRUNCATE SQLITE_IOERR_UNLOCK SQLITE_IOERR_VNODE SQLITE_IOERR_WRITE SQLITE_LOCKED_SHAREDCACHE SQLITE_LOCKED_VTAB SQLITE_NOTICE_RBU SQLITE_NOTICE_RECOVER_ROLLBACK SQLITE_NOTICE_RECOVER_WAL SQLITE_OK_SYMLINK SQLITE_READONLY_CANTINIT SQLITE_READONLY_CANTLOCK SQLITE_READONLY_DBMOVED SQLITE_READONLY_DIRECTORY SQLITE_READONLY_RECOVERY SQLITE_READONLY_ROLLBACK SQLITE_WARNING_AUTOINDEX /], flags_for_file_open_operations => [qw/ SQLITE_OPEN_CREATE SQLITE_OPEN_EXRESCODE SQLITE_OPEN_FULLMUTEX SQLITE_OPEN_MEMORY SQLITE_OPEN_NOFOLLOW SQLITE_OPEN_NOMUTEX SQLITE_OPEN_PRIVATECACHE SQLITE_OPEN_READONLY SQLITE_OPEN_READWRITE SQLITE_OPEN_SHAREDCACHE SQLITE_OPEN_SUPER_JOURNAL SQLITE_OPEN_URI /], function_flags => [qw/ SQLITE_DETERMINISTIC SQLITE_DIRECTONLY SQLITE_INNOCUOUS SQLITE_RESULT_SUBTYPE SQLITE_SUBTYPE /], fundamental_datatypes => [qw/ SQLITE_BLOB SQLITE_FLOAT SQLITE_INTEGER SQLITE_NULL SQLITE_TEXT /], result_codes => [qw/ SQLITE_ABORT SQLITE_AUTH SQLITE_BUSY SQLITE_CANTOPEN SQLITE_CONSTRAINT SQLITE_CORRUPT SQLITE_DONE SQLITE_EMPTY SQLITE_ERROR SQLITE_FORMAT SQLITE_FULL SQLITE_INTERNAL SQLITE_INTERRUPT SQLITE_IOERR SQLITE_LOCKED SQLITE_MISMATCH SQLITE_MISUSE SQLITE_NOLFS SQLITE_NOMEM SQLITE_NOTADB SQLITE_NOTFOUND SQLITE_NOTICE SQLITE_OK SQLITE_PERM SQLITE_PROTOCOL SQLITE_RANGE SQLITE_READONLY SQLITE_ROW SQLITE_SCHEMA SQLITE_TOOBIG SQLITE_WARNING /], run_time_limit_categories => [qw/ SQLITE_LIMIT_ATTACHED SQLITE_LIMIT_COLUMN SQLITE_LIMIT_COMPOUND_SELECT SQLITE_LIMIT_EXPR_DEPTH SQLITE_LIMIT_FUNCTION_ARG SQLITE_LIMIT_LENGTH SQLITE_LIMIT_LIKE_PATTERN_LENGTH SQLITE_LIMIT_SQL_LENGTH SQLITE_LIMIT_TRIGGER_DEPTH SQLITE_LIMIT_VARIABLE_NUMBER SQLITE_LIMIT_VDBE_OP SQLITE_LIMIT_WORKER_THREADS /], ); $EXPORT_TAGS{version} = $EXPORT_TAGS{compile_time_library_version_numbers}; $EXPORT_TAGS{file_open} = $EXPORT_TAGS{flags_for_file_open_operations}; $EXPORT_TAGS{datatypes} = $EXPORT_TAGS{fundamental_datatypes}; 1; __END__ =encoding utf-8 =head1 NAME DBD::SQLite::Constants - common SQLite constants =head1 SYNOPSIS DBD::SQLite::Constants qw/:result_codes/; =head1 DESCRIPTION You can import necessary SQLite constants from this module. Available tags are C, C, C, C, C (C), C, C, C, C (C), C, C (C), C, C. See L for the complete list of constants. This module does not export anything by default. =head1 CONSTANTS =head2 allowed_return_values_from_sqlite3_txn_state =over 4 =item SQLITE_TXN_NONE =item SQLITE_TXN_READ =item SQLITE_TXN_WRITE =back =head2 authorizer_action_codes =over 4 =item SQLITE_CREATE_INDEX =item SQLITE_CREATE_TABLE =item SQLITE_CREATE_TEMP_INDEX =item SQLITE_CREATE_TEMP_TABLE =item SQLITE_CREATE_TEMP_TRIGGER =item SQLITE_CREATE_TEMP_VIEW =item SQLITE_CREATE_TRIGGER =item SQLITE_CREATE_VIEW =item SQLITE_DELETE =item SQLITE_DROP_INDEX =item SQLITE_DROP_TABLE =item SQLITE_DROP_TEMP_INDEX =item SQLITE_DROP_TEMP_TABLE =item SQLITE_DROP_TEMP_TRIGGER =item SQLITE_DROP_TEMP_VIEW =item SQLITE_DROP_TRIGGER =item SQLITE_DROP_VIEW =item SQLITE_INSERT =item SQLITE_PRAGMA =item SQLITE_READ =item SQLITE_SELECT =item SQLITE_TRANSACTION =item SQLITE_UPDATE =item SQLITE_ATTACH =item SQLITE_DETACH =item SQLITE_ALTER_TABLE =item SQLITE_REINDEX =item SQLITE_ANALYZE =item SQLITE_CREATE_VTABLE =item SQLITE_DROP_VTABLE =item SQLITE_FUNCTION =item SQLITE_COPY =item SQLITE_SAVEPOINT =item SQLITE_RECURSIVE =back =head2 authorizer_return_codes =over 4 =item SQLITE_DENY =item SQLITE_IGNORE =back =head2 version (compile_time_library_version_numbers) =over 4 =item SQLITE_VERSION_NUMBER =back =head2 database_connection_configuration_options =over 4 =item SQLITE_DBCONFIG_LOOKASIDE =item SQLITE_DBCONFIG_ENABLE_FKEY =item SQLITE_DBCONFIG_ENABLE_TRIGGER =item SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER =item SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION =item SQLITE_DBCONFIG_MAINDBNAME =item SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE =item SQLITE_DBCONFIG_ENABLE_QPSG =item SQLITE_DBCONFIG_TRIGGER_EQP =item SQLITE_DBCONFIG_MAX =item SQLITE_DBCONFIG_RESET_DATABASE =item SQLITE_DBCONFIG_DEFENSIVE =item SQLITE_DBCONFIG_WRITABLE_SCHEMA =item SQLITE_DBCONFIG_LEGACY_ALTER_TABLE =item SQLITE_DBCONFIG_DQS_DML =item SQLITE_DBCONFIG_DQS_DDL =item SQLITE_DBCONFIG_ENABLE_VIEW =item SQLITE_DBCONFIG_LEGACY_FILE_FORMAT =item SQLITE_DBCONFIG_TRUSTED_SCHEMA =item SQLITE_DBCONFIG_STMT_SCANSTATUS =item SQLITE_DBCONFIG_REVERSE_SCANORDER =back =head2 dbd_sqlite_string_mode =over 4 =item DBD_SQLITE_STRING_MODE_PV =item DBD_SQLITE_STRING_MODE_BYTES =item DBD_SQLITE_STRING_MODE_UNICODE_NAIVE =item DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK =item DBD_SQLITE_STRING_MODE_UNICODE_STRICT =back =head2 extended_result_codes =over 4 =item SQLITE_IOERR_LOCK =item SQLITE_IOERR_READ =item SQLITE_IOERR_SHORT_READ =item SQLITE_IOERR_WRITE =item SQLITE_IOERR_FSYNC =item SQLITE_IOERR_DIR_FSYNC =item SQLITE_IOERR_TRUNCATE =item SQLITE_IOERR_FSTAT =item SQLITE_IOERR_UNLOCK =item SQLITE_IOERR_RDLOCK =item SQLITE_IOERR_DELETE =item SQLITE_IOERR_BLOCKED =item SQLITE_IOERR_NOMEM =item SQLITE_IOERR_ACCESS =item SQLITE_IOERR_CHECKRESERVEDLOCK =item SQLITE_IOERR_CLOSE =item SQLITE_IOERR_DIR_CLOSE =item SQLITE_LOCKED_SHAREDCACHE =item SQLITE_IOERR_SHMOPEN =item SQLITE_IOERR_SHMSIZE =item SQLITE_IOERR_SHMLOCK =item SQLITE_BUSY_RECOVERY =item SQLITE_CANTOPEN_NOTEMPDIR =item SQLITE_IOERR_SHMMAP =item SQLITE_IOERR_SEEK =item SQLITE_CORRUPT_VTAB =item SQLITE_READONLY_RECOVERY =item SQLITE_READONLY_CANTLOCK =item SQLITE_ABORT_ROLLBACK =item SQLITE_CANTOPEN_ISDIR =item SQLITE_IOERR_DELETE_NOENT =item SQLITE_CANTOPEN_FULLPATH =item SQLITE_READONLY_ROLLBACK =item SQLITE_CONSTRAINT_CHECK =item SQLITE_CONSTRAINT_COMMITHOOK =item SQLITE_CONSTRAINT_FOREIGNKEY =item SQLITE_CONSTRAINT_FUNCTION =item SQLITE_CONSTRAINT_NOTNULL =item SQLITE_CONSTRAINT_PRIMARYKEY =item SQLITE_CONSTRAINT_TRIGGER =item SQLITE_CONSTRAINT_UNIQUE =item SQLITE_CONSTRAINT_VTAB =item SQLITE_IOERR_MMAP =item SQLITE_NOTICE_RECOVER_WAL =item SQLITE_NOTICE_RECOVER_ROLLBACK =item SQLITE_IOERR_GETTEMPPATH =item SQLITE_BUSY_SNAPSHOT =item SQLITE_WARNING_AUTOINDEX =item SQLITE_IOERR_CONVPATH =item SQLITE_CANTOPEN_CONVPATH =item SQLITE_CONSTRAINT_ROWID =item SQLITE_READONLY_DBMOVED =item SQLITE_AUTH_USER =item SQLITE_IOERR_VNODE =item SQLITE_IOERR_AUTH =item SQLITE_IOERR_BEGIN_ATOMIC =item SQLITE_IOERR_COMMIT_ATOMIC =item SQLITE_IOERR_ROLLBACK_ATOMIC =item SQLITE_ERROR_MISSING_COLLSEQ =item SQLITE_ERROR_RETRY =item SQLITE_READONLY_CANTINIT =item SQLITE_READONLY_DIRECTORY =item SQLITE_LOCKED_VTAB =item SQLITE_CORRUPT_SEQUENCE =item SQLITE_ERROR_SNAPSHOT =item SQLITE_CANTOPEN_DIRTYWAL =item SQLITE_CANTOPEN_SYMLINK =item SQLITE_CONSTRAINT_PINNED =item SQLITE_OK_SYMLINK =item SQLITE_IOERR_DATA =item SQLITE_BUSY_TIMEOUT =item SQLITE_CORRUPT_INDEX =item SQLITE_IOERR_CORRUPTFS =item SQLITE_CONSTRAINT_DATATYPE =item SQLITE_NOTICE_RBU =item SQLITE_IOERR_IN_PAGE =back =head2 file_open (flags_for_file_open_operations) =over 4 =item SQLITE_OPEN_READONLY =item SQLITE_OPEN_READWRITE =item SQLITE_OPEN_CREATE =item SQLITE_OPEN_NOMUTEX =item SQLITE_OPEN_FULLMUTEX =item SQLITE_OPEN_SHAREDCACHE =item SQLITE_OPEN_PRIVATECACHE =item SQLITE_OPEN_URI =item SQLITE_OPEN_MEMORY =item SQLITE_OPEN_NOFOLLOW =item SQLITE_OPEN_SUPER_JOURNAL =item SQLITE_OPEN_EXRESCODE =back =head2 function_flags =over 4 =item SQLITE_DETERMINISTIC =item SQLITE_DIRECTONLY =item SQLITE_SUBTYPE =item SQLITE_INNOCUOUS =item SQLITE_RESULT_SUBTYPE =back =head2 datatypes (fundamental_datatypes) =over 4 =item SQLITE_INTEGER =item SQLITE_FLOAT =item SQLITE_BLOB =item SQLITE_NULL =item SQLITE_TEXT =back =head2 result_codes =over 4 =item SQLITE_OK =item SQLITE_ERROR =item SQLITE_INTERNAL =item SQLITE_PERM =item SQLITE_ABORT =item SQLITE_BUSY =item SQLITE_LOCKED =item SQLITE_NOMEM =item SQLITE_READONLY =item SQLITE_INTERRUPT =item SQLITE_IOERR =item SQLITE_CORRUPT =item SQLITE_NOTFOUND =item SQLITE_FULL =item SQLITE_CANTOPEN =item SQLITE_PROTOCOL =item SQLITE_EMPTY =item SQLITE_SCHEMA =item SQLITE_TOOBIG =item SQLITE_CONSTRAINT =item SQLITE_MISMATCH =item SQLITE_MISUSE =item SQLITE_NOLFS =item SQLITE_AUTH =item SQLITE_FORMAT =item SQLITE_RANGE =item SQLITE_NOTADB =item SQLITE_ROW =item SQLITE_DONE =item SQLITE_NOTICE =item SQLITE_WARNING =back =head2 run_time_limit_categories =over 4 =item SQLITE_LIMIT_LENGTH =item SQLITE_LIMIT_SQL_LENGTH =item SQLITE_LIMIT_COLUMN =item SQLITE_LIMIT_EXPR_DEPTH =item SQLITE_LIMIT_COMPOUND_SELECT =item SQLITE_LIMIT_VDBE_OP =item SQLITE_LIMIT_FUNCTION_ARG =item SQLITE_LIMIT_ATTACHED =item SQLITE_LIMIT_LIKE_PATTERN_LENGTH =item SQLITE_LIMIT_VARIABLE_NUMBER =item SQLITE_LIMIT_TRIGGER_DEPTH =item SQLITE_LIMIT_WORKER_THREADS =back SQLite/GetInfo.pm000044400000052054152344563120007607 0ustar00package DBD::SQLite::GetInfo; use 5.006; use strict; use warnings; use DBD::SQLite; # SQL_DRIVER_VER should be formatted as dd.dd.dddd my $dbdversion = $DBD::SQLite::VERSION; $dbdversion .= '_00' if $dbdversion =~ /^\d+\.\d+$/; my $sql_driver_ver = sprintf("%02d.%02d.%04d", split(/[\._]/, $dbdversion)); # Full list of keys and their return types: DBI::Const::GetInfo::ODBC # Most of the key definitions can be gleaned from: # # https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlgetinfo-function our %info = ( 20 => 'N', # SQL_ACCESSIBLE_PROCEDURES - No stored procedures to access 19 => 'Y', # SQL_ACCESSIBLE_TABLES - SELECT access to all tables in table_info 0 => 0, # SQL_ACTIVE_CONNECTIONS - No maximum connection limit 116 => 0, # SQL_ACTIVE_ENVIRONMENTS - No "active environment" limit 1 => 0, # SQL_ACTIVE_STATEMENTS - No concurrent activity limit 169 => 127, # SQL_AGGREGATE_FUNCTIONS - Supports all SQL-92 aggregrate functions 117 => 0, # SQL_ALTER_DOMAIN - No ALTER DOMAIN support 86 => 1, # SQL_ALTER_TABLE - Only supports ADD COLUMN and table rename (not listed in enum) in ALTER TABLE statements 10021 => 0, # SQL_ASYNC_MODE - No asynchronous support (in vanilla SQLite) 120 => 0, # SQL_BATCH_ROW_COUNT - No special row counting access 121 => 0, # SQL_BATCH_SUPPORT - No batches 82 => 0, # SQL_BOOKMARK_PERSISTENCE - No bookmark support 114 => 1, # SQL_CATALOG_LOCATION - Database comes first in identifiers 10003 => 'Y', # SQL_CATALOG_NAME - Supports database names 41 => '.', # SQL_CATALOG_NAME_SEPARATOR - Separated by dot 42 => 'database', # SQL_CATALOG_TERM - SQLite calls catalogs databases 92 => 1+4+8, # SQL_CATALOG_USAGE - Supported in calls to DML & table/index definiton (no procedures or permissions) 10004 => 'UTF-8', # SQL_COLLATION_SEQ - SQLite 3 uses UTF-8 by default 87 => 'Y', # SQL_COLUMN_ALIAS - Supports column aliases 22 => 0, # SQL_CONCAT_NULL_BEHAVIOR - 'a'||NULL = NULL # SQLite has no CONVERT function, only CAST. However, it converts to every "affinity" it supports. # # The only SQL_CVT_* types it doesn't support are date/time types, as it has no concept of # date/time values once inserted. These are only convertable to text-like types. GUIDs are in # the same boat, having no real means of switching to a numeric format. # # text/binary types = 31723265 # numeric types = 28926 # date/time types = 1802240 # total = 33554431 48 => 1, # SQL_CONVERT_FUNCTIONS - CAST only 53 => 31723265+28926, # SQL_CONVERT_BIGINT 54 => 31723265+28926, # SQL_CONVERT_BINARY 55 => 31723265+28926, # SQL_CONVERT_BIT 56 => 33554431, # SQL_CONVERT_CHAR 57 => 31723265+1802240, # SQL_CONVERT_DATE 58 => 31723265+28926, # SQL_CONVERT_DECIMAL 59 => 31723265+28926, # SQL_CONVERT_DOUBLE 60 => 31723265+28926, # SQL_CONVERT_FLOAT 173 => 31723265, # SQL_CONVERT_GUID 61 => 31723265+28926, # SQL_CONVERT_INTEGER 123 => 31723265+1802240, # SQL_CONVERT_INTERVAL_DAY_TIME 124 => 31723265+1802240, # SQL_CONVERT_INTERVAL_YEAR_MONTH 71 => 31723265+28926, # SQL_CONVERT_LONGVARBINARY 62 => 31723265+28926, # SQL_CONVERT_LONGVARCHAR 63 => 31723265+28926, # SQL_CONVERT_NUMERIC 64 => 31723265+28926, # SQL_CONVERT_REAL 65 => 31723265+28926, # SQL_CONVERT_SMALLINT 66 => 31723265+1802240, # SQL_CONVERT_TIME 67 => 31723265+1802240, # SQL_CONVERT_TIMESTAMP 68 => 31723265+28926, # SQL_CONVERT_TINYINT 69 => 33554431, # SQL_CONVERT_VARBINARY 70 => 33554431, # SQL_CONVERT_VARCHAR 122 => 33554431, # SQL_CONVERT_WCHAR 125 => 33554431, # SQL_CONVERT_WLONGVARCHAR 126 => 33554431, # SQL_CONVERT_WVARCHAR 74 => 1, # SQL_CORRELATION_NAME - Table aliases are supported, but must be named differently 127 => 0, # SQL_CREATE_ASSERTION - No CREATE ASSERTION support 128 => 0, # SQL_CREATE_CHARACTER_SET - No CREATE CHARACTER SET support 129 => 0, # SQL_CREATE_COLLATION - No CREATE COLLATION support 130 => 0, # SQL_CREATE_DOMAIN - No CREATE DOMAIN support 131 => 0, # SQL_CREATE_SCHEMA - No CREATE SCHEMA support 132 => 16383-2-8-4096, # SQL_CREATE_TABLE - Most of the functionality of CREATE TABLE support 133 => 0, # SQL_CREATE_TRANSLATION - No CREATE TRANSLATION support 134 => 1, # SQL_CREATE_VIEW - CREATE VIEW, no WITH CHECK OPTION support 23 => 2, # SQL_CURSOR_COMMIT_BEHAVIOR - Cursors are preserved 24 => 2, # SQL_CURSOR_ROLLBACK_BEHAVIOR - Cursors are preserved 10001 => 0, # SQL_CURSOR_SENSITIVITY - Cursors have a concept of snapshots, though this depends on the transaction type 2 => \&sql_data_source_name, # SQL_DATA_SOURCE_NAME - The DSN 25 => \&sql_data_source_read_only, # SQL_DATA_SOURCE_READ_ONLY - Might have a SQLITE_OPEN_READONLY flag 16 => \&sql_database_name, # SQL_DATABASE_NAME - Self-explanatory 119 => 0, # SQL_DATETIME_LITERALS - No support for SQL-92's super weird date/time literal format (ie: {d '2999-12-12'}) 17 => 'SQLite', # SQL_DBMS_NAME - You are here 18 => \&sql_dbms_ver, # SQL_DBMS_VER - This driver version 170 => 1+2, # SQL_DDL_INDEX - Supports CREATE/DROP INDEX 26 => 8, # SQL_DEFAULT_TXN_ISOLATION - Default is SERIALIZABLE (See "PRAGMA read_uncommitted") 10002 => 'N', # SQL_DESCRIBE_PARAMETER - No DESCRIBE INPUT support # XXX: MySQL/Oracle fills in HDBC and HENV, but information on what should actually go there is # hard to acquire. # 171 => undef, # SQL_DM_VER - Not a Driver Manager # 3 => undef, # SQL_DRIVER_HDBC - Not a Driver Manager # 135 => undef, # SQL_DRIVER_HDESC - Not a Driver Manager # 4 => undef, # SQL_DRIVER_HENV - Not a Driver Manager # 76 => undef, # SQL_DRIVER_HLIB - Not a Driver Manager # 5 => undef, # SQL_DRIVER_HSTMT - Not a Driver Manager 6 => 'libsqlite3odbc.so', # SQL_DRIVER_NAME - SQLite3 ODBC driver (if installed) 77 => '03.00', # SQL_DRIVER_ODBC_VER - Same as sqlite3odbc.c 7 => $sql_driver_ver, # SQL_DRIVER_VER - Self-explanatory 136 => 0, # SQL_DROP_ASSERTION - No DROP ASSERTION support 137 => 0, # SQL_DROP_CHARACTER_SET - No DROP CHARACTER SET support 138 => 0, # SQL_DROP_COLLATION - No DROP COLLATION support 139 => 0, # SQL_DROP_DOMAIN - No DROP DOMAIN support 140 => 0, # SQL_DROP_SCHEMA - No DROP SCHEMA support 141 => 1, # SQL_DROP_TABLE - DROP TABLE support, no RESTRICT/CASCADE 142 => 0, # SQL_DROP_TRANSLATION - No DROP TRANSLATION support 143 => 1, # SQL_DROP_VIEW - DROP VIEW support, no RESTRICT/CASCADE # NOTE: This is based purely on what sqlite3odbc supports. # # Static CA1: NEXT, ABSOLUTE, RELATIVE, BOOKMARK, LOCK_NO_CHANGE, POSITION, UPDATE, DELETE, REFRESH, # BULK_ADD, BULK_UPDATE_BY_BOOKMARK, BULK_DELETE_BY_BOOKMARK = 466511 # # Forward-only CA1: NEXT, BOOKMARK # # CA2: READ_ONLY_CONCURRENCY, LOCK_CONCURRENCY 144 => 0, # SQL_DYNAMIC_CURSOR_ATTRIBUTES1 - No dynamic cursor support 145 => 0, # SQL_DYNAMIC_CURSOR_ATTRIBUTES2 - No dynamic cursor support 146 => 1+8, # SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1 147 => 1+2, # SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES2 150 => 0, # SQL_KEYSET_CURSOR_ATTRIBUTES1 - No keyset cursor support 151 => 0, # SQL_KEYSET_CURSOR_ATTRIBUTES2 - No keyset cursor support 167 => 466511, # SQL_STATIC_CURSOR_ATTRIBUTES1 168 => 1+2, # SQL_STATIC_CURSOR_ATTRIBUTES2 27 => 'Y', # SQL_EXPRESSIONS_IN_ORDERBY - ORDER BY allows expressions 8 => 63, # SQL_FETCH_DIRECTION - Cursors support next, first, last, prior, absolute, relative 84 => 2, # SQL_FILE_USAGE - Single-tier driver, treats files as databases 81 => 1+2+8, # SQL_GETDATA_EXTENSIONS - Same as sqlite3odbc.c 88 => 3, # SQL_GROUP_BY - SELECT columns are independent of GROUP BY columns 28 => 4, # SQL_IDENTIFIER_CASE - Not case-sensitive, stored in mixed case 29 => '"', # SQL_IDENTIFIER_QUOTE_CHAR - Uses " for identifiers, though supports [] and ` as well 148 => 0, # SQL_INDEX_KEYWORDS - No support for ASC/DESC/ALL for CREATE INDEX 149 => 0, # SQL_INFO_SCHEMA_VIEWS - No support for INFORMATION_SCHEMA 172 => 1+2, # SQL_INSERT_STATEMENT - INSERT...VALUES & INSERT...SELECT 73 => 'N', # SQL_INTEGRITY - No support for "Integrity Enhancement Facility" 89 => \&sql_keywords, # SQL_KEYWORDS - List of non-ODBC keywords 113 => 'Y', # SQL_LIKE_ESCAPE_CLAUSE - Supports LIKE...ESCAPE 78 => 1, # SQL_LOCK_TYPES - Only NO_CHANGE 10022 => 0, # SQL_MAX_ASYNC_CONCURRENT_STATEMENTS - No async mode 112 => 1_000_000, # SQL_MAX_BINARY_LITERAL_LEN - SQLITE_MAX_SQL_LENGTH 34 => 1_000_000, # SQL_MAX_CATALOG_NAME_LEN - SQLITE_MAX_SQL_LENGTH 108 => 1_000_000, # SQL_MAX_CHAR_LITERAL_LEN - SQLITE_MAX_SQL_LENGTH 97 => 2000, # SQL_MAX_COLUMNS_IN_GROUP_BY - SQLITE_MAX_COLUMN 98 => 2000, # SQL_MAX_COLUMNS_IN_INDEX - SQLITE_MAX_COLUMN 99 => 2000, # SQL_MAX_COLUMNS_IN_ORDER_BY - SQLITE_MAX_COLUMN 100 => 2000, # SQL_MAX_COLUMNS_IN_SELECT - SQLITE_MAX_COLUMN 101 => 2000, # SQL_MAX_COLUMNS_IN_TABLE - SQLITE_MAX_COLUMN 30 => 1_000_000, # SQL_MAX_COLUMN_NAME_LEN - SQLITE_MAX_SQL_LENGTH 1 => 1021, # SQL_MAX_CONCURRENT_ACTIVITIES - Typical filehandle limits 31 => 1_000_000, # SQL_MAX_CURSOR_NAME_LEN - SQLITE_MAX_SQL_LENGTH 0 => 1021, # SQL_MAX_DRIVER_CONNECTIONS - Typical filehandle limits 10005 => 1_000_000, # SQL_MAX_IDENTIFIER_LEN - SQLITE_MAX_SQL_LENGTH 102 => 2147483646*65536, # SQL_MAX_INDEX_SIZE - Tied to DB size, which is theortically 140TB 32 => 1_000_000, # SQL_MAX_OWNER_NAME_LEN - SQLITE_MAX_SQL_LENGTH 33 => 1_000_000, # SQL_MAX_PROCEDURE_NAME_LEN - SQLITE_MAX_SQL_LENGTH 34 => 1_000_000, # SQL_MAX_QUALIFIER_NAME_LEN - SQLITE_MAX_SQL_LENGTH 104 => 1_000_000, # SQL_MAX_ROW_SIZE - SQLITE_MAX_SQL_LENGTH (since INSERT has to be used) 103 => 'Y', # SQL_MAX_ROW_SIZE_INCLUDES_LONG 32 => 1_000_000, # SQL_MAX_SCHEMA_NAME_LEN - SQLITE_MAX_SQL_LENGTH 105 => 1_000_000, # SQL_MAX_STATEMENT_LEN - SQLITE_MAX_SQL_LENGTH 106 => 64, # SQL_MAX_TABLES_IN_SELECT - 64 tables, because of the bitmap in the query optimizer 35 => 1_000_000, # SQL_MAX_TABLE_NAME_LEN - SQLITE_MAX_SQL_LENGTH 107 => 0, # SQL_MAX_USER_NAME_LEN - No user support 37 => 'Y', # SQL_MULTIPLE_ACTIVE_TXN - Supports mulitple txns, though not nested 36 => 'N', # SQL_MULT_RESULT_SETS - No batches 111 => 'N', # SQL_NEED_LONG_DATA_LEN - Doesn't care about LONG 75 => 1, # SQL_NON_NULLABLE_COLUMNS - Supports NOT NULL 85 => 1, # SQL_NULL_COLLATION - NULLs first on ASC (low end) 49 => 4194304+1, # SQL_NUMERIC_FUNCTIONS - Just ABS & ROUND (has RANDOM, but not RAND) 9 => 1, # SQL_ODBC_API_CONFORMANCE - Same as sqlite3odbc.c 152 => 1, # SQL_ODBC_INTERFACE_CONFORMANCE - Same as sqlite3odbc.c 12 => 0, # SQL_ODBC_SAG_CLI_CONFORMANCE - Same as sqlite3odbc.c 15 => 0, # SQL_ODBC_SQL_CONFORMANCE - Same as sqlite3odbc.c 10 => '03.00', # SQL_ODBC_VER - Same as sqlite3odbc.c 115 => 1+8+16+32+64, # SQL_OJ_CAPABILITIES - Supports all OUTER JOINs except RIGHT & FULL 90 => 'N', # SQL_ORDER_BY_COLUMNS_IN_SELECT - ORDER BY columns don't have to be in the SELECT list 38 => 'Y', # SQL_OUTER_JOINS - Supports OUTER JOINs 153 => 2, # SQL_PARAM_ARRAY_ROW_COUNTS - Only has row counts for executed statements 154 => 3, # SQL_PARAM_ARRAY_SELECTS - No support for arrays of parameters 80 => 0, # SQL_POSITIONED_STATEMENTS - No support for positioned statements (WHERE CURRENT OF or SELECT FOR UPDATE) 79 => 31, # SQL_POS_OPERATIONS - Supports all SQLSetPos operations 21 => 'N', # SQL_PROCEDURES - No procedures 40 => '', # SQL_PROCEDURE_TERM - No procedures 93 => 4, # SQL_QUOTED_IDENTIFIER_CASE - Even quoted identifiers are case-insensitive 11 => 'N', # SQL_ROW_UPDATES - No fancy cursor update support 39 => '', # SQL_SCHEMA_TERM - No schemas 91 => 0, # SQL_SCHEMA_USAGE - No schemas 43 => 2, # SQL_SCROLL_CONCURRENCY - Updates/deletes on cursors lock the database 44 => 1+16, # SQL_SCROLL_OPTIONS - Only supports static & forward-only cursors 14 => '\\', # SQL_SEARCH_PATTERN_ESCAPE - Default escape character for LIKE is \ 13 => \&sql_server_name, # SQL_SERVER_NAME - Just $dbh->{Name} 94 => '', # SQL_SPECIAL_CHARACTERS - Other drivers tend to stick to the ASCII/Latin-1 range, and SQLite uses all of # the lower 7-bit punctuation for other things 155 => 7, # SQL_SQL92_DATETIME_FUNCTIONS - Supports CURRENT_(DATE|TIME|TIMESTAMP) 156 => 1+2+4+8, # SQL_SQL92_FOREIGN_KEY_DELETE_RULE - Support all ON DELETE options 157 => 1+2+4+8, # SQL_SQL92_FOREIGN_KEY_UPDATE_RULE - Support all ON UPDATE options 158 => 0, # SQL_SQL92_GRANT - No users; no support for GRANT 159 => 0, # SQL_SQL92_NUMERIC_VALUE_FUNCTIONS - No support for any of the listed functions 160 => 1+2+4+512+1024+2048+4096+8192, # SQL_SQL92_PREDICATES - Supports the important comparison operators 161 => 2+16+64+128, # SQL_SQL92_RELATIONAL_JOIN_OPERATORS - Supports the important ones except RIGHT/FULL OUTER JOINs 162 => 0, # SQL_SQL92_REVOKE - No users; no support for REVOKE 163 => 1+2+8, # SQL_SQL92_ROW_VALUE_CONSTRUCTOR - Supports most row value constructors 164 => 2+4, # SQL_SQL92_STRING_FUNCTIONS - Just UPPER & LOWER (has SUBSTR, but not SUBSTRING and SQL-92's weird TRIM syntax) 165 => 1+2+4+8, # SQL_SQL92_VALUE_EXPRESSIONS - Supports all SQL-92 value expressions 118 => 1, # SQL_SQL_CONFORMANCE - SQL-92 Entry level 83 => 0, # SQL_STATIC_SENSITIVITY - Cursors would lock the DB, so only old data is visible 50 => 8+16+256+1024+16384+131072, # SQL_STRING_FUNCTIONS - LTRIM, LENGTH, REPLACE, RTRIM, CHAR, SOUNDEX 95 => 1+2+4+8+16, # SQL_SUBQUERIES - Supports all of the subquery types 51 => 4, # SQL_SYSTEM_FUNCTIONS - Only IFNULL 45 => 'table', # SQL_TABLE_TERM - Tables are called tables 109 => 0, # SQL_TIMEDATE_ADD_INTERVALS - No support for INTERVAL 110 => 0, # SQL_TIMEDATE_DIFF_INTERVALS - No support for INTERVAL 52 => 0x20000+0x40000+0x80000, # SQL_TIMEDATE_FUNCTIONS - Only supports CURRENT_(DATE|TIME|TIMESTAMP) 46 => 2, # SQL_TXN_CAPABLE - Full transaction support for both DML & DDL 72 => 1+8, # SQL_TXN_ISOLATION_OPTION - Supports read uncommitted and serializable 96 => 1+2, # SQL_UNION - Supports UNION and UNION ALL 47 => '', # SQL_USER_NAME - No users 166 => 1, # SQL_STANDARD_CLI_CONFORMANCE - X/Open CLI Version 1.0 10000 => 1992, # SQL_XOPEN_CLI_YEAR - Year for V1.0 ); sub sql_dbms_ver { my $dbh = shift; return $dbh->FETCH('sqlite_version'); } sub sql_data_source_name { my $dbh = shift; return "dbi:SQLite:".$dbh->{Name}; } sub sql_data_source_read_only { my $dbh = shift; my $flags = $dbh->FETCH('sqlite_open_flags') || 0; return $dbh->{ReadOnly} || ($flags & DBD::SQLite::OPEN_READONLY()) ? 'Y' : 'N'; } sub sql_database_name { my $dbh = shift; my $databases = $dbh->selectall_hashref('PRAGMA database_list', 'seq'); return $databases->{0}{name}; } sub sql_keywords { # SQLite keywords minus ODBC keywords return join ',', (qw< ABORT AFTER ANALYZE ATTACH AUTOINCREMENT BEFORE CONFLICT DATABASE DETACH EACH EXCLUSIVE EXPLAIN FAIL GLOB IF IGNORE INDEXED INSTEAD ISNULL LIMIT NOTNULL OFFSET PLAN PRAGMA QUERY RAISE RECURSIVE REGEXP REINDEX RELEASE RENAME REPLACE ROW SAVEPOINT TEMP TRIGGER VACUUM VIRTUAL WITHOUT >); } sub sql_server_name { my $dbh = shift; return $dbh->{Name}; } 1; __END__ SQLite/Cookbook.pod000044400000007246152344563120010173 0ustar00=head1 NAME DBD::SQLite::Cookbook - The DBD::SQLite Cookbook =head1 DESCRIPTION This is the L cookbook. It is intended to provide a place to keep a variety of functions and formals for use in callback APIs in L. =head1 AGGREGATE FUNCTIONS =head2 Variance This is a simple aggregate function which returns a variance. It is adapted from an example implementation in pysqlite. package variance; sub new { bless [], shift; } sub step { my ( $self, $value ) = @_; push @$self, $value; } sub finalize { my $self = $_[0]; my $n = @$self; # Variance is NULL unless there is more than one row return undef unless $n || $n == 1; my $mu = 0; foreach my $v ( @$self ) { $mu += $v; } $mu /= $n; my $sigma = 0; foreach my $v ( @$self ) { $sigma += ($v - $mu)**2; } $sigma = $sigma / ($n - 1); return $sigma; } # NOTE: If you use an older DBI (< 1.608), # use $dbh->func(..., "create_aggregate") instead. $dbh->sqlite_create_aggregate( "variance", 1, 'variance' ); The function can then be used as: SELECT group_name, variance(score) FROM results GROUP BY group_name; =head2 Variance (Memory Efficient) A more efficient variance function, optimized for memory usage at the expense of precision: package variance2; sub new { bless {sum => 0, count=>0, hash=> {} }, shift; } sub step { my ( $self, $value ) = @_; my $hash = $self->{hash}; # by truncating and hashing, we can comsume many more data points $value = int($value); # change depending on need for precision # use sprintf for arbitrary fp precision if (exists $hash->{$value}) { $hash->{$value}++; } else { $hash->{$value} = 1; } $self->{sum} += $value; $self->{count}++; } sub finalize { my $self = $_[0]; # Variance is NULL unless there is more than one row return undef unless $self->{count} > 1; # calculate avg my $mu = $self->{sum} / $self->{count}; my $sigma = 0; while (my ($h, $v) = each %{$self->{hash}}) { $sigma += (($h - $mu)**2) * $v; } $sigma = $sigma / ($self->{count} - 1); return $sigma; } The function can then be used as: SELECT group_name, variance2(score) FROM results GROUP BY group_name; =head2 Variance (Highly Scalable) A third variable implementation, designed for arbitrarily large data sets: package variance3; sub new { bless {mu=>0, count=>0, S=>0}, shift; } sub step { my ( $self, $value ) = @_; $self->{count}++; my $delta = $value - $self->{mu}; $self->{mu} += $delta/$self->{count}; $self->{S} += $delta*($value - $self->{mu}); } sub finalize { my $self = $_[0]; return $self->{S} / ($self->{count} - 1); } The function can then be used as: SELECT group_name, variance3(score) FROM results GROUP BY group_name; =head1 SUPPORT Bugs should be reported via the CPAN bug tracker at L =head1 TO DO =over =item * Add more and varied cookbook recipes, until we have enough to turn them into a separate CPAN distribution. =item * Create a series of tests scripts that validate the cookbook recipes. =back =head1 AUTHOR Adam Kennedy Eadamk@cpan.orgE =head1 COPYRIGHT Copyright 2009 - 2012 Adam Kennedy. 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. SQLite/Fulltext_search.pod000044400000045356152344563120011565 0ustar00=head1 NAME DBD::SQLite::Fulltext_search - Using fulltext searches with DBD::SQLite =head1 DESCRIPTION =head2 Introduction SQLite is bundled with an extension module called "FTS" 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 (also called "tokens"), in any column, even if the table contains many large documents. The first full-text search modules for SQLite were called C and C and are now obsolete. The latest version is C, but it shares many features with the former module C, which is why parts of the API and parts of the documentation still refer to C; from a client point of view, both can be considered largely equivalent. Detailed documentation can be found at L. =head2 Short example Here is a very short example of using FTS : $dbh->do(<<"") or die DBI::errstr; CREATE VIRTUAL TABLE fts_example USING fts4(content) my $sth = $dbh->prepare("INSERT INTO fts_example(content) VALUES (?)"); $sth->execute($_) foreach @docs_to_insert; my $results = $dbh->selectall_arrayref(<<""); SELECT docid, snippet(fts_example) FROM fts_example WHERE content MATCH 'foo' The key points in this example are : =over =item * The syntax for creating FTS tables is CREATE VIRTUAL TABLE USING fts4() where C<< >> is a list of column names. Columns may be typed, but the type information is ignored. If no columns are specified, the default is a single column named C. In addition, FTS tables have an implicit column called C (or also C) for numbering the stored documents. =item * Statements for inserting, updating or deleting records use the same syntax as for regular SQLite tables. =item * Full-text searches are specified with the C operator, and an operand which may be a single word, a word prefix ending with '*', a list of words, a "phrase query" in double quotes, or a boolean combination of the above. =item * The builtin function C builds a formatted excerpt of the document text, where the words pertaining to the query are highlighted. =back There are many more details to building and searching FTS tables, so we strongly invite you to read the full documentation at L. =head1 QUERY SYNTAX Here are some explanation about FTS queries, borrowed from the sqlite documentation. =head2 Token or token prefix queries An FTS table may be queried for all documents that contain a specified term, or for all documents that contain a term with a specified prefix. The query expression for a specific term is simply the term itself. The query expression used to search for a term prefix is the prefix itself with a '*' character appended to it. For example: -- Virtual table declaration CREATE VIRTUAL TABLE docs USING fts3(title, body); -- Query for all documents containing the term "linux": SELECT * FROM docs WHERE docs MATCH 'linux'; -- Query for all documents containing a term with the prefix "lin". SELECT * FROM docs WHERE docs MATCH 'lin*'; If a search token (on the right-hand side of the MATCH operator) begins with "^" then that token must be the first in its field of the document : so for example C<^lin*> matches 'linux kernel changes ...' but does not match 'new linux implementation'. =head2 Column specifications Normally, a token or token prefix query is matched against the FTS table column specified as the right-hand side of the MATCH operator. Or, if the special column with the same name as the FTS table itself is specified, against all columns. This may be overridden by specifying a column-name followed by a ":" character before a basic term query. There may be space between the ":" and the term to query for, but not between the column-name and the ":" character. For example: -- Query the database for documents for which the term "linux" appears in -- the document title, and the term "problems" appears in either the title -- or body of the document. SELECT * FROM docs WHERE docs MATCH 'title:linux problems'; -- Query the database for documents for which the term "linux" appears in -- the document title, and the term "driver" appears in the body of the document -- ("driver" may also appear in the title, but this alone will not satisfy the. -- query criteria). SELECT * FROM docs WHERE body MATCH 'title:linux driver'; =head2 Phrase queries A phrase query is a query that retrieves all documents that contain a nominated set of terms or term prefixes in a specified order with no intervening tokens. Phrase queries are specified by enclosing a space separated sequence of terms or term prefixes in double quotes ("). For example: -- Query for all documents that contain the phrase "linux applications". SELECT * FROM docs WHERE docs MATCH '"linux applications"'; -- Query for all documents that contain a phrase that matches "lin* app*". -- As well as "linux applications", this will match common phrases such -- as "linoleum appliances" or "link apprentice". SELECT * FROM docs WHERE docs MATCH '"lin* app*"'; =head2 NEAR queries. A NEAR query is a query that returns documents that contain a two or more nominated terms or phrases within a specified proximity of each other (by default with 10 or less intervening terms). A NEAR query is specified by putting the keyword "NEAR" between two phrase, term or prefix queries. To specify a proximity other than the default, an operator of the form "NEAR/" may be used, where is the maximum number of intervening terms allowed. For example: -- Virtual table declaration. CREATE VIRTUAL TABLE docs USING fts4(); -- Virtual table data. INSERT INTO docs VALUES('SQLite is an ACID compliant embedded relational database management system'); -- Search for a document that contains the terms "sqlite" and "database" with -- not more than 10 intervening terms. This matches the only document in -- table docs (since there are only six terms between "SQLite" and "database" -- in the document). SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR database'; -- Search for a document that contains the terms "sqlite" and "database" with -- not more than 6 intervening terms. This also matches the only document in -- table docs. Note that the order in which the terms appear in the document -- does not have to be the same as the order in which they appear in the query. SELECT * FROM docs WHERE docs MATCH 'database NEAR/6 sqlite'; -- Search for a document that contains the terms "sqlite" and "database" with -- not more than 5 intervening terms. This query matches no documents. SELECT * FROM docs WHERE docs MATCH 'database NEAR/5 sqlite'; -- Search for a document that contains the phrase "ACID compliant" and the term -- "database" with not more than 2 terms separating the two. This matches the -- document stored in table docs. SELECT * FROM docs WHERE docs MATCH 'database NEAR/2 "ACID compliant"'; -- Search for a document that contains the phrase "ACID compliant" and the term -- "sqlite" with not more than 2 terms separating the two. This also matches -- the only document stored in table docs. SELECT * FROM docs WHERE docs MATCH '"ACID compliant" NEAR/2 sqlite'; More than one NEAR operator may appear in a single query. In this case each pair of terms or phrases separated by a NEAR operator must appear within the specified proximity of each other in the document. Using the same table and data as in the block of examples above: -- The following query selects documents that contains an instance of the term -- "sqlite" separated by two or fewer terms from an instance of the term "acid", -- which is in turn separated by two or fewer terms from an instance of the term -- "relational". SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR/2 acid NEAR/2 relational'; -- This query matches no documents. There is an instance of the term "sqlite" with -- sufficient proximity to an instance of "acid" but it is not sufficiently close -- to an instance of the term "relational". SELECT * FROM docs WHERE docs MATCH 'acid NEAR/2 sqlite NEAR/2 relational'; Phrase and NEAR queries may not span multiple columns within a row. =head2 Set operations The three basic query types described above may be used to query the full-text index for the set of documents that match the specified criteria. Using the FTS query expression language it is possible to perform various set operations on the results of basic queries. There are currently three supported operations: =over =item * The AND operator determines the intersection of two sets of documents. =item * The OR operator calculates the union of two sets of documents. =item * The NOT operator may be used to compute the relative complement of one set of documents with respect to another. =back The AND, OR and NOT binary set operators must be entered using capital letters; otherwise, they are interpreted as basic term queries instead of set operators. Each of the two operands to an operator may be a basic FTS query, or the result of another AND, OR or NOT set operation. Parenthesis may be used to control precedence and grouping. The AND operator is implicit for adjacent basic queries without any explicit operator. For example, the query expression "implicit operator" is a more succinct version of "implicit AND operator". Boolean operations as just described correspond to the so-called "enhanced query syntax" of sqlite; this is the version compiled with C, starting from version 1.31. A former version, called the "standard query syntax", used to support tokens prefixed with '+' or '-' signs (for token inclusion or exclusion); if your application needs to support this old syntax, use L (published in a separate distribution) for doing the conversion. =head1 TOKENIZERS =head2 Concept The behaviour of full-text indexes strongly depends on how documents are split into I; therefore FTS table declarations can explicitly specify how to perform tokenization: CREATE ... USING fts4(, tokenize=) where C<< >> is a sequence of space-separated words that triggers a specific tokenizer. Tokenizers can be SQLite builtins, written in C code, or Perl tokenizers. Both are as explained below. =head2 SQLite builtin tokenizers SQLite comes with some builtin tokenizers (see L) : =over =item simple Under the I tokenizer, a term is a contiguous sequence of eligible characters, where eligible characters are all alphanumeric characters, the "_" character, and all characters with UTF codepoints greater than or equal to 128. All other characters are discarded when splitting a document into terms. They serve only to separate adjacent terms. All uppercase characters within the ASCII range (UTF codepoints less than 128), are transformed to their lowercase equivalents as part of the tokenization process. Thus, full-text queries are case-insensitive when using the simple tokenizer. =item porter The I tokenizer uses the same rules to separate the input document into terms, but as well as folding all terms to lower case it uses the Porter Stemming algorithm to reduce related English language words to a common root. =item icu The I tokenizer uses the ICU library to decide how to identify word characters in different languages; however, this requires SQLite to be compiled with the C pre-processor symbol defined. So, to use this tokenizer, you need edit F to add this flag in C<@CC_DEFINE>, and then recompile C; of course, the prerequisite is to have an ICU library available on your system. =item unicode61 The I tokenizer works very much like "simple" except that it does full unicode case folding according to rules in Unicode Version 6.1 and it recognizes unicode space and punctuation characters and uses those to separate tokens. By contrast, the simple tokenizer only does case folding of ASCII characters and only recognizes ASCII space and punctuation characters as token separators. By default, "unicode61" also removes all diacritics from Latin script characters. This behaviour can be overridden by adding the tokenizer argument C<"remove_diacritics=0">. For example: -- Create tables that remove diacritics from Latin script characters -- as part of tokenization. CREATE VIRTUAL TABLE txt1 USING fts4(tokenize=unicode61); CREATE VIRTUAL TABLE txt2 USING fts4(tokenize=unicode61 "remove_diacritics=1"); -- Create a table that does not remove diacritics from Latin script -- characters as part of tokenization. CREATE VIRTUAL TABLE txt3 USING fts4(tokenize=unicode61 "remove_diacritics=0"); Additional options can customize the set of codepoints that unicode61 treats as separator characters or as token characters -- see the documentation in L. =back If a more complex tokenizing algorithm is required, for example to implement stemming, discard punctuation, or to recognize compound words, use the perl tokenizer to implement your own logic, as explained below. =head2 Perl tokenizers =head3 Declaring a perl tokenizer In addition to the builtin SQLite tokenizers, C implements a I tokenizer, that can hook to any tokenizing algorithm written in Perl. This is specified as follows : CREATE ... USING fts4(, tokenize=perl '') where C<< >> is a fully qualified Perl function name (i.e. prefixed by the name of the package in which that function is declared). So for example if the function is C in the main program, write CREATE ... USING fts4(, tokenize=perl 'main::my_func') =head3 Writing a perl tokenizer by hand That function should return a code reference that takes a string as single argument, and returns an iterator (another function), which returns a tuple C<< ($term, $len, $start, $end, $index) >> for each term. Here is a simple example that tokenizes on words according to the current perl locale sub locale_tokenizer { return sub { my $string = shift; use locale; my $regex = qr/\w+/; my $term_index = 0; return sub { # closure $string =~ /$regex/g or return; # either match, or no more token my ($start, $end) = ($-[0], $+[0]); my $len = $end-$start; my $term = substr($string, $start, $len); return ($term, $len, $start, $end, $term_index++); } }; } There must be three levels of subs, in a kind of "Russian dolls" structure, because : =over =item * the external, named sub is called whenever accessing a FTS table with that tokenizer =item * the inner, anonymous sub is called whenever a new string needs to be tokenized (either for inserting new text into the table, or for analyzing a query). =item * the innermost, anonymous sub is called repeatedly for retrieving all terms within that string. =back =head3 Using Search::Tokenizer Instead of writing tokenizers by hand, you can grab one of those already implemented in the L module. For example, if you want ignore differences between accented characters, you can write : use Search::Tokenizer; $dbh->do(<<"") or die DBI::errstr; CREATE ... USING fts4(, tokenize=perl 'Search::Tokenizer::unaccent') Alternatively, you can use L to build your own tokenizer. Here is an example that treats compound words (words with an internal dash or dot) as single tokens : sub my_tokenizer { return Search::Tokenizer->new( regex => qr{\p{Word}+(?:[-./]\p{Word}+)*}, ); } =head1 Fts4aux - Direct Access to the Full-Text Index The content of a full-text index can be accessed through the virtual table module "fts4aux". For example, assuming that our database contains a full-text indexed table named "ft", we can declare : CREATE VIRTUAL TABLE ft_terms USING fts4aux(ft) and then query the C table to access the list of terms, their frequency, etc. Examples are documented in L. =head1 How to spare database space By default, FTS 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. However, FTS has some options for compressing the documents, or even for not storing them at all -- see L. In particular, the option for I only stores the fulltext index, without the original document content. This is specified as C, like in the following example : CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b) Data can be inserted into such an FTS4 table using an INSERT statements. However, unlike ordinary FTS4 tables, the user must supply an explicit integer docid value. For example: -- This statement is Ok: INSERT INTO t1(docid, a, b) VALUES(1, 'a b c', 'd e f'); -- This statement causes an error, as no docid value has been provided: INSERT INTO t1(a, b) VALUES('j k l', 'm n o'); Of course your application will need an algorithm for finding the external resource corresponding to any I stored within SQLite. When using placeholders, the docid must be explicitly typed to INTEGER, because this is a "hidden column" for which sqlite is not able to automatically infer the proper type. So the following doesn't work : my $sth = $dbh->prepare("INSERT INTO t1(docid, a, b) VALUES(?, ?, ?)"); $sth->execute(2, 'aa', 'bb'); # constraint error but it works with an explicitly cast : my $sql = "INSERT INTO t1(docid, a, b) VALUES(CAST(? AS INTEGER), ?, ?)", my $sth = $dbh->prepare(sql); $sth->execute(2, 'aa', 'bb'); or with an explicitly typed L : use DBI qw/SQL_INTEGER/; my $sql = "INSERT INTO t1(docid, a, b) VALUES(?, ?, ?)"; my $sth = $dbh->prepare(sql); $sth->bind_param(1, 2, SQL_INTEGER); $sth->bind_param(2, "aa"); $sth->bind_param(3, "bb"); $sth->execute(); It is not possible to UPDATE or DELETE a row stored in a contentless FTS4 table. Attempting to do so is an error. Contentless FTS4 tables also support SELECT statements. However, it is an error to attempt to retrieve the value of any table column other than the docid column. The auxiliary function C may be used, but C and C may not, so if such functionality is needed, it has to be directly programmed within the Perl application. =head1 AUTHOR Laurent Dami Edami@cpan.orgE =head1 COPYRIGHT Copyright 2014 Laurent Dami. Some parts borrowed from the L documentation, copyright 2014. This documentation is in the public domain; you can redistribute it and/or modify it under the same terms as Perl itself. SQLite.pm000044400000276312152344563120006261 0ustar00package DBD::SQLite; use 5.006; use strict; use DBI 1.57 (); use XSLoader (); our $VERSION = '1.76'; # sqlite_version cache (set in the XS bootstrap) our ($sqlite_version, $sqlite_version_number); # not sure if we still need these... our ($err, $errstr); XSLoader::load('DBD::SQLite', $VERSION); # New or old API? use constant NEWAPI => ($DBI::VERSION >= 1.608); # global registry of collation functions, initialized with 2 builtins our %COLLATION; tie %COLLATION, 'DBD::SQLite::_WriteOnceHash'; $COLLATION{perl} = sub { $_[0] cmp $_[1] }; $COLLATION{perllocale} = sub { use locale; $_[0] cmp $_[1] }; our $drh; my $methods_are_installed = 0; sub driver { return $drh if $drh; if (!$methods_are_installed && DBD::SQLite::NEWAPI ) { DBI->setup_driver('DBD::SQLite'); DBD::SQLite::db->install_method('sqlite_last_insert_rowid'); DBD::SQLite::db->install_method('sqlite_busy_timeout'); DBD::SQLite::db->install_method('sqlite_create_function'); DBD::SQLite::db->install_method('sqlite_create_aggregate'); DBD::SQLite::db->install_method('sqlite_create_collation'); DBD::SQLite::db->install_method('sqlite_collation_needed'); DBD::SQLite::db->install_method('sqlite_progress_handler'); DBD::SQLite::db->install_method('sqlite_commit_hook'); DBD::SQLite::db->install_method('sqlite_rollback_hook'); DBD::SQLite::db->install_method('sqlite_update_hook'); DBD::SQLite::db->install_method('sqlite_set_authorizer'); DBD::SQLite::db->install_method('sqlite_backup_from_file'); DBD::SQLite::db->install_method('sqlite_backup_to_file'); DBD::SQLite::db->install_method('sqlite_backup_from_dbh'); DBD::SQLite::db->install_method('sqlite_backup_to_dbh'); DBD::SQLite::db->install_method('sqlite_enable_load_extension'); DBD::SQLite::db->install_method('sqlite_load_extension'); DBD::SQLite::db->install_method('sqlite_register_fts3_perl_tokenizer'); DBD::SQLite::db->install_method('sqlite_trace', { O => 0x0004 }); DBD::SQLite::db->install_method('sqlite_profile', { O => 0x0004 }); DBD::SQLite::db->install_method('sqlite_table_column_metadata', { O => 0x0004 }); DBD::SQLite::db->install_method('sqlite_db_filename', { O => 0x0004 }); DBD::SQLite::db->install_method('sqlite_db_status', { O => 0x0004 }); DBD::SQLite::st->install_method('sqlite_st_status', { O => 0x0004 }); 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 ) { $dbh->sqlite_create_collation( $name => $collation ); } else { $dbh->func( $name => $collation, "create_collation" ); } } # default implementation for sqlite 'REGEXP' infix operator. # Note : args are reversed, i.e. "a REGEXP b" calls REGEXP(b, a) # (see https://www.sqlite.org/vtab.html#xfindfunction) sub regexp { use locale; return if !defined $_[0] || !defined $_[1]; return scalar($_[1] =~ $_[0]); } package # hide from PAUSE DBD::SQLite::db; use DBI qw/:sql_types/; sub prepare { my $dbh = shift; my $sql = shift; $sql = '' unless defined $sql; my $sth = DBI::_new_sth( $dbh, { Statement => $sql, } ); DBD::SQLite::st::_prepare($sth, $sql, @_) or return undef; return $sth; } sub do { my ($dbh, $statement, $attr, @bind_values) = @_; # shortcut my $allow_multiple_statements = $dbh->FETCH('sqlite_allow_multiple_statements'); if (defined $statement && !defined $attr && !@bind_values) { # _do() (i.e. sqlite3_exec()) runs semicolon-separate SQL # statements, which is handy but insecure sometimes. # Use this only when it's safe or explicitly allowed. if (index($statement, ';') == -1 or $allow_multiple_statements) { return DBD::SQLite::db::_do($dbh, $statement); } } my @copy = @{[@bind_values]}; my $rows = 0; while ($statement) { my $sth = $dbh->prepare($statement, $attr) or return undef; $sth->execute(splice @copy, 0, $sth->{NUM_OF_PARAMS}) or return undef; $rows += $sth->rows; # XXX: not sure why but $dbh->{sqlite...} wouldn't work here last unless $allow_multiple_statements; $statement = $sth->{sqlite_unprepared_statements}; } # always return true if no error return ($rows == 0) ? "0E0" : $rows; } sub ping { my $dbh = shift; # $file may be undef (ie. in-memory/temporary database) my $file = DBD::SQLite::NEWAPI ? $dbh->sqlite_db_filename : $dbh->func("db_filename"); return 0 if $file && !-f $file; return $dbh->FETCH('Active') ? 1 : 0; } sub quote { my ($self, $value, $data_type) = @_; return "NULL" unless defined $value; if (defined $data_type and ( $data_type == DBI::SQL_BIT || $data_type == DBI::SQL_BLOB || $data_type == DBI::SQL_BINARY || $data_type == DBI::SQL_VARBINARY || $data_type == DBI::SQL_LONGVARBINARY)) { return q(X') . unpack('H*', $value) . q('); } $value =~ s/'/''/g; return "'$value'"; } sub get_info { my ($dbh, $info_type) = @_; require DBD::SQLite::GetInfo; my $v = $DBD::SQLite::GetInfo::info{int($info_type)}; $v = $v->($dbh) if ref $v eq 'CODE'; return $v; } sub _attached_database_list { my $dbh = shift; my @attached; my $sth_databases = $dbh->prepare( 'PRAGMA database_list' ) or return; $sth_databases->execute or return; while ( my $db_info = $sth_databases->fetchrow_hashref ) { push @attached, $db_info->{name} if $db_info->{seq} >= 2; } return @attached; } # SQL/CLI (ISO/IEC JTC 1/SC 32 N 0595), 6.63 Tables # Based on DBD::Oracle's # See also http://www.ch-werner.de/sqliteodbc/html/sqlite3odbc_8c.html#a213 sub table_info { my ($dbh, $cat_val, $sch_val, $tbl_val, $typ_val, $attr) = @_; my @where = (); my $sql; if ( defined($cat_val) && $cat_val eq '%' && defined($sch_val) && $sch_val eq '' && defined($tbl_val) && $tbl_val eq '') { # Rule 19a $sql = <<'END_SQL'; SELECT NULL TABLE_CAT , NULL TABLE_SCHEM , NULL TABLE_NAME , NULL TABLE_TYPE , NULL REMARKS END_SQL } elsif ( defined($cat_val) && $cat_val eq '' && defined($sch_val) && $sch_val eq '%' && defined($tbl_val) && $tbl_val eq '') { # Rule 19b $sql = <<'END_SQL'; SELECT NULL TABLE_CAT , t.tn TABLE_SCHEM , NULL TABLE_NAME , NULL TABLE_TYPE , NULL REMARKS FROM ( SELECT 'main' tn UNION SELECT 'temp' tn END_SQL for my $db_name (_attached_database_list($dbh)) { $sql .= " UNION SELECT '$db_name' tn\n"; } $sql .= ") t\n"; } elsif ( defined($cat_val) && $cat_val eq '' && defined($sch_val) && $sch_val eq '' && defined($tbl_val) && $tbl_val eq '' && defined($typ_val) && $typ_val eq '%') { # Rule 19c $sql = <<'END_SQL'; SELECT NULL TABLE_CAT , NULL TABLE_SCHEM , NULL TABLE_NAME , t.tt TABLE_TYPE , NULL REMARKS FROM ( SELECT 'TABLE' tt UNION SELECT 'VIEW' tt UNION SELECT 'LOCAL TEMPORARY' tt UNION SELECT 'SYSTEM TABLE' tt ) t ORDER BY TABLE_TYPE END_SQL } else { $sql = <<'END_SQL'; SELECT * FROM ( SELECT NULL TABLE_CAT , TABLE_SCHEM , tbl_name TABLE_NAME , TABLE_TYPE , NULL REMARKS , sql sqlite_sql FROM ( SELECT 'main' TABLE_SCHEM, tbl_name, upper(type) TABLE_TYPE, sql FROM sqlite_master UNION ALL SELECT 'temp' TABLE_SCHEM, tbl_name, 'LOCAL TEMPORARY' TABLE_TYPE, sql FROM sqlite_temp_master END_SQL for my $db_name (_attached_database_list($dbh)) { $sql .= <<"END_SQL"; UNION ALL SELECT '$db_name' TABLE_SCHEM, tbl_name, upper(type) TABLE_TYPE, sql FROM "$db_name".sqlite_master END_SQL } $sql .= <<'END_SQL'; UNION ALL SELECT 'main' TABLE_SCHEM, 'sqlite_master' tbl_name, 'SYSTEM TABLE' TABLE_TYPE, NULL sql UNION ALL SELECT 'temp' TABLE_SCHEM, 'sqlite_temp_master' tbl_name, 'SYSTEM TABLE' TABLE_TYPE, NULL sql ) ) END_SQL $attr = {} unless ref $attr eq 'HASH'; my $escape = defined $attr->{Escape} ? " ESCAPE '$attr->{Escape}'" : ''; if ( defined $sch_val ) { push @where, "TABLE_SCHEM LIKE '$sch_val'$escape"; } if ( defined $tbl_val ) { push @where, "TABLE_NAME LIKE '$tbl_val'$escape"; } if ( defined $typ_val ) { my $table_type_list; $typ_val =~ s/^\s+//; $typ_val =~ s/\s+$//; my @ttype_list = split (/\s*,\s*/, $typ_val); foreach my $table_type (@ttype_list) { if ($table_type !~ /^'.*'$/) { $table_type = "'" . $table_type . "'"; } } $table_type_list = join(', ', @ttype_list); push @where, "TABLE_TYPE IN (\U$table_type_list)" if $table_type_list; } $sql .= ' WHERE ' . join("\n AND ", @where ) . "\n" if @where; $sql .= " ORDER BY TABLE_TYPE, TABLE_SCHEM, TABLE_NAME\n"; } my $sth = $dbh->prepare($sql) or return undef; $sth->execute or return undef; $sth; } sub primary_key_info { my ($dbh, $catalog, $schema, $table, $attr) = @_; my $databases = $dbh->selectall_arrayref("PRAGMA database_list", {Slice => {}}); my @pk_info; for my $database (@$databases) { my $dbname = $database->{name}; next if defined $schema && $schema ne '%' && $schema ne $dbname; my $quoted_dbname = $dbh->quote_identifier($dbname); my $master_table = ($dbname eq 'main') ? 'sqlite_master' : ($dbname eq 'temp') ? 'sqlite_temp_master' : $quoted_dbname.'.sqlite_master'; my $sth = $dbh->prepare("SELECT name, sql FROM $master_table WHERE type = ?") or return; $sth->execute("table") or return; while(my $row = $sth->fetchrow_hashref) { my $tbname = $row->{name}; next if defined $table && $table ne '%' && $table ne $tbname; my $quoted_tbname = $dbh->quote_identifier($tbname); my $t_sth = $dbh->prepare("PRAGMA $quoted_dbname.table_info($quoted_tbname)") or return; $t_sth->execute or return; my @pk; while(my $col = $t_sth->fetchrow_hashref) { push @pk, $col->{name} if $col->{pk}; } # If there're multiple primary key columns, we need to # find their order from one of the auto-generated unique # indices (note that single column integer primary key # doesn't create an index). if (@pk > 1 and $row->{sql} =~ /\bPRIMARY\s+KEY\s*\(\s* ( (?: ( [a-z_][a-z0-9_]* | (["'`])(?:\3\3|(?!\3).)+?\3(?!\3) | \[[^\]]+\] ) \s*,\s* )+ ( [a-z_][a-z0-9_]* | (["'`])(?:\5\5|(?!\5).)+?\5(?!\5) | \[[^\]]+\] ) ) \s*\)/six) { my $pk_sql = $1; @pk = (); while($pk_sql =~ / ( [a-z_][a-z0-9_]* | (["'`])(?:\2\2|(?!\2).)+?\2(?!\2) | \[([^\]]+)\] ) (?:\s*,\s*|$) /sixg) { my($col, $quote, $brack) = ($1, $2, $3); if ( defined $quote ) { # Dequote "'` $col = substr $col, 1, -1; $col =~ s/$quote$quote/$quote/g; } elsif ( defined $brack ) { # Dequote [] $col = $brack; } push @pk, $col; } } my $key_name = $row->{sql} =~ /\bCONSTRAINT\s+(\S+|"[^"]+")\s+PRIMARY\s+KEY\s*\(/i ? $1 : 'PRIMARY KEY'; my $key_seq = 0; foreach my $pk_field (@pk) { push @pk_info, { TABLE_SCHEM => $dbname, TABLE_NAME => $tbname, COLUMN_NAME => $pk_field, KEY_SEQ => ++$key_seq, PK_NAME => $key_name, }; } } } my $sponge = DBI->connect("DBI:Sponge:", '','') or return $dbh->DBI::set_err($DBI::err, "DBI::Sponge: $DBI::errstr"); my @names = qw(TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME); my $sth = $sponge->prepare( "primary_key_info", { rows => [ map { [ @{$_}{@names} ] } @pk_info ], NUM_OF_FIELDS => scalar @names, NAME => \@names, }) or return $dbh->DBI::set_err( $sponge->err, $sponge->errstr, ); return $sth; } our %DBI_code_for_rule = ( # from DBI doc; curiously, they are not exported # by the DBI module. # codes for update/delete constraints 'CASCADE' => 0, 'RESTRICT' => 1, 'SET NULL' => 2, 'NO ACTION' => 3, 'SET DEFAULT' => 4, # codes for deferrability 'INITIALLY DEFERRED' => 5, 'INITIALLY IMMEDIATE' => 6, 'NOT DEFERRABLE' => 7, ); my @FOREIGN_KEY_INFO_ODBC = ( 'PKTABLE_CAT', # The primary (unique) key table catalog identifier. 'PKTABLE_SCHEM', # The primary (unique) key table schema identifier. 'PKTABLE_NAME', # The primary (unique) key table identifier. 'PKCOLUMN_NAME', # The primary (unique) key column identifier. 'FKTABLE_CAT', # The foreign key table catalog identifier. 'FKTABLE_SCHEM', # The foreign key table schema identifier. 'FKTABLE_NAME', # The foreign key table identifier. 'FKCOLUMN_NAME', # The foreign key column identifier. 'KEY_SEQ', # The column sequence number (starting with 1). 'UPDATE_RULE', # The referential action for the UPDATE rule. 'DELETE_RULE', # The referential action for the DELETE rule. 'FK_NAME', # The foreign key name. 'PK_NAME', # The primary (unique) key name. 'DEFERRABILITY', # The deferrability of the foreign key constraint. 'UNIQUE_OR_PRIMARY', # qualifies the key referenced by the foreign key ); # Column names below are not used, but listed just for completeness's sake. # Maybe we could add an option so that the user can choose which field # names will be returned; the DBI spec is not very clear about ODBC vs. CLI. my @FOREIGN_KEY_INFO_SQL_CLI = qw( UK_TABLE_CAT UK_TABLE_SCHEM UK_TABLE_NAME UK_COLUMN_NAME FK_TABLE_CAT FK_TABLE_SCHEM FK_TABLE_NAME FK_COLUMN_NAME ORDINAL_POSITION UPDATE_RULE DELETE_RULE FK_NAME UK_NAME DEFERABILITY UNIQUE_OR_PRIMARY ); my $DEFERRABLE_RE = qr/ (?:(?: on \s+ (?:delete|update) \s+ (?:set \s+ null|set \s+ default|cascade|restrict|no \s+ action) | match \s* (?:\S+|".+?(?selectall_arrayref("PRAGMA database_list", {Slice => {}}) or return; my @fk_info; my %table_info; for my $database (@$databases) { my $dbname = $database->{name}; next if defined $fk_schema && $fk_schema ne '%' && $fk_schema ne $dbname; my $quoted_dbname = $dbh->quote_identifier($dbname); my $master_table = ($dbname eq 'main') ? 'sqlite_master' : ($dbname eq 'temp') ? 'sqlite_temp_master' : $quoted_dbname.'.sqlite_master'; my $tables = $dbh->selectall_arrayref("SELECT name, sql FROM $master_table WHERE type = ?", undef, "table") or return; for my $table (@$tables) { my $tbname = $table->[0]; my $ddl = $table->[1]; my (@rels, %relid2rels); next if defined $fk_table && $fk_table ne '%' && $fk_table ne $tbname; my $quoted_tbname = $dbh->quote_identifier($tbname); my $sth = $dbh->prepare("PRAGMA $quoted_dbname.foreign_key_list($quoted_tbname)") or return; $sth->execute or return; while(my $row = $sth->fetchrow_hashref) { next if defined $pk_table && $pk_table ne '%' && $pk_table ne $row->{table}; unless ($table_info{$row->{table}}) { my $quoted_tb = $dbh->quote_identifier($row->{table}); for my $db (@$databases) { my $quoted_db = $dbh->quote_identifier($db->{name}); my $t_sth = $dbh->prepare("PRAGMA $quoted_db.table_info($quoted_tb)") or return; $t_sth->execute or return; my $cols = {}; while(my $r = $t_sth->fetchrow_hashref) { $cols->{$r->{name}} = $r->{pk}; } if (keys %$cols) { $table_info{$row->{table}} = { schema => $db->{name}, columns => $cols, }; last; } } } next if defined $pk_schema && $pk_schema ne '%' && $pk_schema ne $table_info{$row->{table}}{schema}; # cribbed from DBIx::Class::Schema::Loader::DBI::SQLite my $rel = $rels[ $row->{id} ] ||= { local_columns => [], remote_columns => undef, remote_table => $row->{table}, }; push @{ $rel->{local_columns} }, $row->{from}; push @{ $rel->{remote_columns} }, $row->{to} if defined $row->{to}; my $fk_row = { PKTABLE_CAT => undef, PKTABLE_SCHEM => $table_info{$row->{table}}{schema}, PKTABLE_NAME => $row->{table}, PKCOLUMN_NAME => $row->{to}, FKTABLE_CAT => undef, FKTABLE_SCHEM => $dbname, FKTABLE_NAME => $tbname, FKCOLUMN_NAME => $row->{from}, KEY_SEQ => $row->{seq} + 1, UPDATE_RULE => $DBI_code_for_rule{$row->{on_update}}, DELETE_RULE => $DBI_code_for_rule{$row->{on_delete}}, FK_NAME => undef, PK_NAME => undef, DEFERRABILITY => undef, UNIQUE_OR_PRIMARY => $table_info{$row->{table}}{columns}{$row->{to}} ? 'PRIMARY' : 'UNIQUE', }; push @fk_info, $fk_row; push @{ $relid2rels{$row->{id}} }, $fk_row; # keep so can fixup } # cribbed from DBIx::Class::Schema::Loader::DBI::SQLite # but with additional parsing of which kind of deferrable REL: for my $relid (keys %relid2rels) { my $rel = $rels[$relid]; my $deferrable = $DBI_code_for_rule{'NOT DEFERRABLE'}; my $local_cols = '"?' . (join '"? \s* , \s* "?', map quotemeta, @{ $rel->{local_columns} }) . '"?'; my $remote_cols = '"?' . (join '"? \s* , \s* "?', map quotemeta, @{ $rel->{remote_columns} || [] }) . '"?'; my ($deferrable_clause) = $ddl =~ / foreign \s+ key \s* \( \s* $local_cols \s* \) \s* references \s* (?:\S+|".+?(?{local_columns} } == 1) { my ($local_col) = @{ $rel->{local_columns} }; my ($remote_col) = @{ $rel->{remote_columns} || [] }; $remote_col ||= ''; ($deferrable_clause) = $ddl =~ / "?\Q$local_col\E"? \s* (?:\w+\s*)* (?: \( \s* \d\+ (?:\s*,\s*\d+)* \s* \) )? \s* references \s+ (?:\S+|".+?(?{DEFERRABILITY} = $deferrable for @{ $relid2rels{$relid} }; } } } my $sponge_dbh = DBI->connect("DBI:Sponge:", "", "") or return $dbh->DBI::set_err($DBI::err, "DBI::Sponge: $DBI::errstr"); my $sponge_sth = $sponge_dbh->prepare("foreign_key_info", { NAME => \@FOREIGN_KEY_INFO_ODBC, rows => [ map { [@{$_}{@FOREIGN_KEY_INFO_ODBC} ] } @fk_info ], NUM_OF_FIELDS => scalar(@FOREIGN_KEY_INFO_ODBC), }) or return $dbh->DBI::set_err( $sponge_dbh->err, $sponge_dbh->errstr, ); return $sponge_sth; } my @STATISTICS_INFO_ODBC = ( 'TABLE_CAT', # The catalog identifier. 'TABLE_SCHEM', # The schema identifier. 'TABLE_NAME', # The table identifier. 'NON_UNIQUE', # Unique index indicator. 'INDEX_QUALIFIER', # Index qualifier identifier. 'INDEX_NAME', # The index identifier. 'TYPE', # The type of information being returned. 'ORDINAL_POSITION', # Column sequence number (starting with 1). 'COLUMN_NAME', # The column identifier. 'ASC_OR_DESC', # Column sort sequence. 'CARDINALITY', # Cardinality of the table or index. 'PAGES', # Number of storage pages used by this table or index. 'FILTER_CONDITION', # The index filter condition as a string. ); sub statistics_info { my ($dbh, $catalog, $schema, $table, $unique_only, $quick) = @_; my $databases = $dbh->selectall_arrayref("PRAGMA database_list", {Slice => {}}) or return; my @statistics_info; for my $database (@$databases) { my $dbname = $database->{name}; next if defined $schema && $schema ne '%' && $schema ne $dbname; my $quoted_dbname = $dbh->quote_identifier($dbname); my $master_table = ($dbname eq 'main') ? 'sqlite_master' : ($dbname eq 'temp') ? 'sqlite_temp_master' : $quoted_dbname.'.sqlite_master'; my $tables = $dbh->selectall_arrayref("SELECT name FROM $master_table WHERE type = ?", undef, "table") or return; for my $table_ref (@$tables) { my $tbname = $table_ref->[0]; next if defined $table && $table ne '%' && uc($table) ne uc($tbname); my $quoted_tbname = $dbh->quote_identifier($tbname); my $sth = $dbh->prepare("PRAGMA $quoted_dbname.index_list($quoted_tbname)") or return; $sth->execute or return; while(my $row = $sth->fetchrow_hashref) { next if $unique_only && !$row->{unique}; my $quoted_idx = $dbh->quote_identifier($row->{name}); for my $db (@$databases) { my $quoted_db = $dbh->quote_identifier($db->{name}); my $i_sth = $dbh->prepare("PRAGMA $quoted_db.index_info($quoted_idx)") or return; $i_sth->execute or return; my $cols = {}; while(my $info = $i_sth->fetchrow_hashref) { push @statistics_info, { TABLE_CAT => undef, TABLE_SCHEM => $db->{name}, TABLE_NAME => $tbname, NON_UNIQUE => $row->{unique} ? 0 : 1, INDEX_QUALIFIER => undef, INDEX_NAME => $row->{name}, TYPE => 'btree', # see https://www.sqlite.org/version3.html esp. "Traditional B-trees are still used for indices" ORDINAL_POSITION => $info->{seqno} + 1, COLUMN_NAME => $info->{name}, ASC_OR_DESC => undef, CARDINALITY => undef, PAGES => undef, FILTER_CONDITION => undef, }; } } } } } my $sponge_dbh = DBI->connect("DBI:Sponge:", "", "") or return $dbh->DBI::set_err($DBI::err, "DBI::Sponge: $DBI::errstr"); my $sponge_sth = $sponge_dbh->prepare("statistics_info", { NAME => \@STATISTICS_INFO_ODBC, rows => [ map { [@{$_}{@STATISTICS_INFO_ODBC} ] } @statistics_info ], NUM_OF_FIELDS => scalar(@STATISTICS_INFO_ODBC), }) or return $dbh->DBI::set_err( $sponge_dbh->err, $sponge_dbh->errstr, ); return $sponge_sth; } my @TypeInfoKeys = qw/ TYPE_NAME DATA_TYPE COLUMN_SIZE LITERAL_PREFIX LITERAL_SUFFIX CREATE_PARAMS NULLABLE CASE_SENSITIVE SEARCHABLE UNSIGNED_ATTRIBUTE FIXED_PREC_SCALE AUTO_UNIQUE_VALUE LOCAL_TYPE_NAME MINIMUM_SCALE MAXIMUM_SCALE SQL_DATA_TYPE SQL_DATETIME_SUB NUM_PREC_RADIX INTERVAL_PRECISION /; my %TypeInfo = ( SQL_INTEGER ,=> { TYPE_NAME => 'INTEGER', DATA_TYPE => SQL_INTEGER, NULLABLE => 2, # no for integer primary key, otherwise yes SEARCHABLE => 3, }, SQL_DOUBLE ,=> { TYPE_NAME => 'REAL', DATA_TYPE => SQL_DOUBLE, NULLABLE => 1, SEARCHABLE => 3, }, SQL_VARCHAR ,=> { TYPE_NAME => 'TEXT', DATA_TYPE => SQL_VARCHAR, LITERAL_PREFIX => "'", LITERAL_SUFFIX => "'", NULLABLE => 1, SEARCHABLE => 3, }, SQL_BLOB ,=> { TYPE_NAME => 'BLOB', DATA_TYPE => SQL_BLOB, NULLABLE => 1, SEARCHABLE => 3, }, SQL_UNKNOWN_TYPE ,=> { DATA_TYPE => SQL_UNKNOWN_TYPE, }, ); sub type_info_all { my $idx = 0; my @info = ({map {$_ => $idx++} @TypeInfoKeys}); for my $id (sort {$a <=> $b} keys %TypeInfo) { push @info, [map {$TypeInfo{$id}{$_}} @TypeInfoKeys]; } return \@info; } my @COLUMN_INFO = qw( TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE ); sub column_info { my ($dbh, $cat_val, $sch_val, $tbl_val, $col_val) = @_; if ( defined $col_val and $col_val eq '%' ) { $col_val = undef; } # Get a list of all tables ordered by TABLE_SCHEM, TABLE_NAME my $sql = <<'END_SQL'; SELECT TABLE_SCHEM, tbl_name TABLE_NAME FROM ( SELECT 'main' TABLE_SCHEM, tbl_name FROM sqlite_master WHERE type IN ('table','view') UNION ALL SELECT 'temp' TABLE_SCHEM, tbl_name FROM sqlite_temp_master WHERE type IN ('table','view') END_SQL for my $db_name (_attached_database_list($dbh)) { $sql .= <<"END_SQL"; UNION ALL SELECT '$db_name' TABLE_SCHEM, tbl_name FROM "$db_name".sqlite_master WHERE type IN ('table','view') END_SQL } $sql .= <<'END_SQL'; UNION ALL SELECT 'main' TABLE_SCHEM, 'sqlite_master' tbl_name UNION ALL SELECT 'temp' TABLE_SCHEM, 'sqlite_temp_master' tbl_name ) END_SQL my @where; if ( defined $sch_val ) { push @where, "TABLE_SCHEM LIKE '$sch_val'"; } if ( defined $tbl_val ) { push @where, "TABLE_NAME LIKE '$tbl_val'"; } $sql .= ' WHERE ' . join("\n AND ", @where ) . "\n" if @where; $sql .= " ORDER BY TABLE_SCHEM, TABLE_NAME\n"; my $sth_tables = $dbh->prepare($sql) or return undef; $sth_tables->execute or return undef; # Taken from Fey::Loader::SQLite my @cols; while ( my ($schema, $table) = $sth_tables->fetchrow_array ) { my $sth_columns = $dbh->prepare(qq{PRAGMA "$schema".table_info("$table")}) or return; $sth_columns->execute or return; for ( my $position = 1; my $col_info = $sth_columns->fetchrow_hashref; $position++ ) { if ( defined $col_val ) { # This must do a LIKE comparison my $sth = $dbh->prepare("SELECT '$col_info->{name}' LIKE '$col_val'") or return undef; $sth->execute or return undef; # Skip columns that don't match $col_val next unless ($sth->fetchrow_array)[0]; } my %col = ( TABLE_SCHEM => $schema, TABLE_NAME => $table, COLUMN_NAME => $col_info->{name}, ORDINAL_POSITION => $position, ); my $type = $col_info->{type}; if ( $type =~ s/(\w+)\s*\(\s*(\d+)(?:\s*,\s*(\d+))?\s*\)/$1/ ) { $col{COLUMN_SIZE} = $2; $col{DECIMAL_DIGITS} = $3; } $col{TYPE_NAME} = $type; if ( defined $col_info->{dflt_value} ) { $col{COLUMN_DEF} = $col_info->{dflt_value} } if ( $col_info->{notnull} ) { $col{NULLABLE} = 0; $col{IS_NULLABLE} = 'NO'; } else { $col{NULLABLE} = 1; $col{IS_NULLABLE} = 'YES'; } push @cols, \%col; } $sth_columns->finish; } $sth_tables->finish; my $sponge = DBI->connect("DBI:Sponge:", '','') or return $dbh->DBI::set_err($DBI::err, "DBI::Sponge: $DBI::errstr"); $sponge->prepare( "column_info", { rows => [ map { [ @{$_}{@COLUMN_INFO} ] } @cols ], NUM_OF_FIELDS => scalar @COLUMN_INFO, NAME => [ @COLUMN_INFO ], } ) or return $dbh->DBI::set_err( $sponge->err, $sponge->errstr, ); } #====================================================================== # An internal tied hash package used for %DBD::SQLite::COLLATION, to # prevent people from unintentionally overriding globally registered collations. package # hide from PAUSE DBD::SQLite::_WriteOnceHash; require Tie::Hash; our @ISA = qw(Tie::StdHash); sub TIEHASH { bless {}, $_[0]; } sub STORE { ! exists $_[0]->{$_[1]} or die "entry $_[1] already registered"; $_[0]->{$_[1]} = $_[2]; } sub DELETE { die "deletion of entry $_[1] is forbidden"; } 1; __END__ =pod =encoding utf-8 =head1 NAME DBD::SQLite - Self-contained RDBMS in a DBI Driver =head1 SYNOPSIS use DBI; my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","",""); =head1 DESCRIPTION SQLite is a public domain file-based relational database engine that you can find at L. B is a Perl DBI driver for SQLite, that includes the entire thing in the distribution. So in order to get a fast transaction capable RDBMS working for your perl project you simply have to install this module, and B else. SQLite supports the following features: =over 4 =item Implements a large subset of SQL92 See L for details. =item A complete DB in a single disk file Everything for your database is stored in a single disk file, making it easier to move things around than with L. =item Atomic commit and rollback Yes, B is small and light, but it supports full transactions! =item Extensible User-defined aggregate or regular functions can be registered with the SQL parser. =back There's lots more to it, so please refer to the docs on the SQLite web page, listed above, for SQL details. Also refer to L for details on how to use DBI itself. The API works like every DBI module does. However, currently many statement attributes are not implemented or are limited by the typeless nature of the SQLite database. =head1 SQLITE VERSION DBD::SQLite is usually compiled with a bundled SQLite library (SQLite version S<3.46.1> as of this release) for consistency. However, a different version of SQLite may sometimes be used for some reasons like security, or some new experimental features. You can look at C<$DBD::SQLite::sqlite_version> (C<3.x.y> format) or C<$DBD::SQLite::sqlite_version_number> (C<3xxxyyy> format) to find which version of SQLite is actually used. You can also check C. You can also find how the library is compiled by calling C (see below). =head1 NOTABLE DIFFERENCES FROM OTHER DRIVERS =head2 Database Name Is A File Name SQLite creates a file per a database. You should pass the C of the database file (with or without a parent directory) in the DBI connection string (as a database C): my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","",""); The file is opened in read/write mode, and will be created if it does not exist yet. Although the database is stored in a single file, the directory containing the database file must be writable by SQLite because the library will create several temporary files there. If the filename C<$dbfile> is ":memory:", then a private, temporary in-memory database is created for the connection. This in-memory database will vanish when the database connection is closed. It is handy for your library tests. Note that future versions of SQLite might make use of additional special filenames that begin with the ":" character. It is recommended that when a database filename actually does begin with a ":" character you should prefix the filename with a pathname such as "./" to avoid ambiguity. If the filename C<$dbfile> is an empty string, then a private, temporary on-disk database will be created. This private database will be automatically deleted as soon as the database connection is closed. As of 1.41_01, you can pass URI filename (see L) as well for finer control: my $dbh = DBI->connect("dbi:SQLite:uri=file:$path_to_dbfile?mode=rwc"); Note that this is not for remote SQLite database connection. You can only connect to a local database. =head2 Read-Only Database You can set sqlite_open_flags (only) when you connect to a database: use DBD::SQLite::Constants qw/:file_open/; my $dbh = DBI->connect("dbi:SQLite:$dbfile", undef, undef, { sqlite_open_flags => SQLITE_OPEN_READONLY, }); See L for details. As of 1.49_05, you can also make a database read-only by setting C attribute to true (only) when you connect to a database. Actually you can set it after you connect, but in that case, it can't make the database read-only, and you'll see a warning (which you can hide by turning C off). =head2 DBD::SQLite And File::Temp When you use L to create a temporary file/directory for SQLite databases, you need to remember: =over 4 =item tempfile may be locked exclusively You may want to use C to create a temporary database filename for DBD::SQLite, but as noted in L's POD, this file may have an exclusive lock under some operating systems (notably Mac OSX), and result in a "database is locked" error. To avoid this, set EXLOCK option to false when you call tempfile(). ($fh, $filename) = tempfile($template, EXLOCK => 0); =item CLEANUP may not work unless a database is disconnected When you set CLEANUP option to true when you create a temporary directory with C or C, you may have to disconnect databases explicitly before the temporary directory is gone (notably under MS Windows). =back (The above is quoted from the pod of File::Temp.) If you don't need to keep or share a temporary database, use ":memory:" database instead. It's much handier and cleaner for ordinary testing. =head2 DBD::SQLite and fork() Follow the advice in the SQLite FAQ (L). =over 4 Under Unix, you should not carry an open SQLite database across a fork() system call into the child process. Problems will result if you do. =back You shouldn't (re)use a database handle you created (probably to set up a database schema etc) before you fork(). Otherwise, you might see a database corruption in the worst case. If you need to fork(), (re)open a database after you fork(). You might also want to tweak C and C (see below), depending on your needs. If you need a higher level of concurrency than SQLite supports, consider using other client/server database engines. =head2 Accessing A Database With Other Tools To access the database from the command line, try using C which comes with the L module. Just type: dbish dbi:SQLite:foo.db On the command line to access the file F. Alternatively you can install SQLite from the link above without conflicting with B and use the supplied C command line tool. =head2 Blobs As of version 1.11, blobs should "just work" in SQLite as text columns. However this will cause the data to be treated as a string, so SQL statements such as length(x) will return the length of the column as a NUL terminated string, rather than the size of the blob in bytes. In order to store natively as a BLOB use the following code: use DBI qw(:sql_types); my $dbh = DBI->connect("dbi:SQLite:dbfile","",""); my $blob = `cat foo.jpg`; my $sth = $dbh->prepare("INSERT INTO mytable VALUES (1, ?)"); $sth->bind_param(1, $blob, SQL_BLOB); $sth->execute(); And then retrieval just works: $sth = $dbh->prepare("SELECT * FROM mytable WHERE id = 1"); $sth->execute(); my $row = $sth->fetch; my $blobo = $row->[1]; # now $blobo == $blob =head2 Functions And Bind Parameters As of this writing, a SQL that compares a return value of a function with a numeric bind value like this doesn't work as you might expect. my $sth = $dbh->prepare(q{ SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?; }); $sth->execute(5); This is because DBD::SQLite assumes that all the bind values are text (and should be quoted) by default. Thus the above statement becomes like this while executing: SELECT bar FROM foo GROUP BY bar HAVING count(*) > "5"; There are four workarounds for this. =over 4 =item Use bind_param() explicitly As shown above in the C section, you can always use C to tell the type of a bind value. use DBI qw(:sql_types); # Don't forget this my $sth = $dbh->prepare(q{ SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?; }); $sth->bind_param(1, 5, SQL_INTEGER); $sth->execute(); =item Add zero to make it a number This is somewhat weird, but works anyway. my $sth = $dbh->prepare(q{ SELECT bar FROM foo GROUP BY bar HAVING count(*) > (? + 0); }); $sth->execute(5); =item Use SQL cast() function This is more explicit way to do the above. my $sth = $dbh->prepare(q{ SELECT bar FROM foo GROUP BY bar HAVING count(*) > cast(? as integer); }); $sth->execute(5); =item Set C database handle attribute As of version 1.32_02, you can use C to let DBD::SQLite to see if the bind values are numbers or not. $dbh->{sqlite_see_if_its_a_number} = 1; my $sth = $dbh->prepare(q{ SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?; }); $sth->execute(5); You can set it to true when you connect to a database. my $dbh = DBI->connect('dbi:SQLite:foo', undef, undef, { AutoCommit => 1, RaiseError => 1, sqlite_see_if_its_a_number => 1, }); This is the most straightforward solution, but as noted above, existing data in your databases created by DBD::SQLite have not always been stored as numbers, so this *might* cause other obscure problems. Use this sparingly when you handle existing databases. If you handle databases created by other tools like native C command line tool, this attribute would help you. As of 1.41_04, C works only for bind values with no explicit type. my $dbh = DBI->connect('dbi:SQLite:foo', undef, undef, { AutoCommit => 1, RaiseError => 1, sqlite_see_if_its_a_number => 1, }); my $sth = $dbh->prepare('INSERT INTO foo VALUES(?)'); # '1.230' will be inserted as a text, instead of 1.23 as a number, # even though sqlite_see_if_its_a_number is set. $sth->bind_param(1, '1.230', SQL_VARCHAR); $sth->execute; =back =head2 Placeholders SQLite supports several placeholder expressions, including C and C<:AAAA>. Consult the L and SQLite documentation for details. L Note that a question mark actually means a next unused (numbered) placeholder. You're advised not to use it with other (numbered or named) placeholders to avoid confusion. my $sth = $dbh->prepare( 'update TABLE set a=?1 where b=?2 and a IS NOT ?1' ); $sth->execute(1, 2); =head2 Pragma SQLite has a set of "Pragma"s to modify its operation or to query for its internal data. These are specific to SQLite and are not likely to work with other DBD libraries, but you may find some of these are quite useful, including: =over 4 =item journal_mode You can use this pragma to change the journal mode for SQLite databases, maybe for better performance, or for compatibility. Its default mode is C, which means SQLite uses a rollback journal to implement transactions, and the journal is deleted at the conclusion of each transaction. If you use C instead of C, the journal will be truncated, which is usually much faster. A C (write-ahead log) mode is introduced as of SQLite 3.7.0. This mode is persistent, and it stays in effect even after closing and reopening the database. In other words, once the C mode is set in an application or in a test script, the database becomes inaccessible by older clients. This tends to be an issue when you use a system C executable under a conservative operating system. To fix this, You need to issue C (or C) beforehand, or install a newer version of C. =item legacy_file_format If you happen to need to create a SQLite database that will also be accessed by a very old SQLite client (prior to 3.3.0 released in Jan. 2006), you need to set this pragma to ON before you create a database. =item reverse_unordered_selects You can set this pragma to ON to reverse the order of results of SELECT statements without an ORDER BY clause so that you can see if applications are making invalid assumptions about the result order. Note that SQLite 3.7.15 (bundled with DBD::SQLite 1.38_02) enhanced its query optimizer and the order of results of a SELECT statement without an ORDER BY clause may be different from the one of the previous versions. =item synchronous You can set set this pragma to OFF to make some of the operations in SQLite faster with a possible risk of database corruption in the worst case. See also L section below. =back See L for more details. =head2 Foreign Keys SQLite has started supporting foreign key constraints since 3.6.19 (released on Oct 14, 2009; bundled in DBD::SQLite 1.26_05). To be exact, SQLite has long been able to parse a schema with foreign keys, but the constraints has not been enforced. Now you can issue a C pragma to enable this feature and enforce the constraints, preferably as soon as you connect to a database and you're not in a transaction: $dbh->do("PRAGMA foreign_keys = ON"); And you can explicitly disable the feature whenever you like by turning the pragma off: $dbh->do("PRAGMA foreign_keys = OFF"); As of this writing, this feature is disabled by default by the SQLite team, and by us, to secure backward compatibility, as this feature may break your applications, and actually broke some for us. If you have used a schema with foreign key constraints but haven't cared them much and supposed they're always ignored for SQLite, be prepared, and please do extensive testing to ensure that your applications will continue to work when the foreign keys support is enabled by default. See L for details. =head2 Transactions DBI/DBD::SQLite's transactions may be a bit confusing. They behave differently according to the status of the C flag: =over 4 =item When the AutoCommit flag is on You're supposed to always use the auto-commit mode, except you explicitly begin a transaction, and when the transaction ended, you're supposed to go back to the auto-commit mode. To begin a transaction, call C method, or issue a C statement. To end it, call C methods, or issue the corresponding statements. $dbh->{AutoCommit} = 1; $dbh->begin_work; # or $dbh->do('BEGIN TRANSACTION'); # $dbh->{AutoCommit} is turned off temporarily during a transaction; $dbh->commit; # or $dbh->do('COMMIT'); # $dbh->{AutoCommit} is turned on again; =item When the AutoCommit flag is off You're supposed to always use the transactional mode, until you explicitly turn on the AutoCommit flag. You can explicitly issue a C statement (only when an actual transaction has not begun yet) but you're not allowed to call C method (if you don't issue a C, it will be issued internally). You can commit or roll it back freely. Another transaction will automatically begin if you execute another statement. $dbh->{AutoCommit} = 0; # $dbh->do('BEGIN TRANSACTION') is not necessary, but possible ... $dbh->commit; # or $dbh->do('COMMIT'); # $dbh->{AutoCommit} stays intact; $dbh->{AutoCommit} = 1; # ends the transactional mode =back This C mode is independent from the autocommit mode of the internal SQLite library, which always begins by a C statement, and ends by a C or a C. =head2 Transaction and Database Locking The default transaction behavior of SQLite is C, that means, locks are not acquired until the first read or write operation, and thus it is possible that another thread or process could create a separate transaction and write to the database after the C on the current thread has executed, and eventually cause a "deadlock". To avoid this, DBD::SQLite internally issues a C if you begin a transaction by calling C or by turning off C (since 1.38_01). If you really need to turn off this feature for some reasons, set C database handle attribute to false, and the default C transaction will be used. my $dbh = DBI->connect("dbi:SQLite::memory:", "", "", { sqlite_use_immediate_transaction => 0, }); Or, issue a C statement explicitly each time you begin a transaction. See L for locking details. =head2 C<< $sth->finish >> and Transaction Rollback As the L doc says, you almost certainly do B need to call L method if you fetch all rows (probably in a loop). However, there are several exceptions to this rule, and rolling-back of an unfinished C statements in a transaction (See L for details). So you need to call C before you issue a rollback. $sth = $dbh->prepare("SELECT * FROM t"); $dbh->begin_work; eval { $sth->execute; $row = $sth->fetch; ... die "For some reason"; ... }; if($@) { $sth->finish; # You need this for SQLite $dbh->rollback; } else { $dbh->commit; } =head2 Processing Multiple Statements At A Time L's statement handle is not supposed to process multiple statements at a time. So if you pass a string that contains multiple statements (a C) to a statement handle (via C or C), L only processes the first statement, and discards the rest. If you need to process multiple statements at a time, set a C attribute of a database handle to true when you connect to a database, and C method takes care of the rest (since 1.30_01, and without creating DBI's statement handles internally since 1.47_01). If you do need to use C or C (which I don't recommend in this case, because typically there's no placeholder nor reusable part in a dump), you can look at C<< $sth->{sqlite_unprepared_statements} >> to retrieve what's left, though it usually contains nothing but white spaces. =head2 TYPE statement attribute Because of historical reasons, DBD::SQLite's C 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). As of version 1.61_02, if you set C database handle attribute to true, C statement handle attribute returns an array of integer, as an experiment. =head2 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 (L), 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. =head1 DRIVER PRIVATE ATTRIBUTES =head2 Database Handle Attributes =over 4 =item sqlite_version Returns the version of the SQLite library which B is using, e.g., "3.26.0". Can only be read. =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 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. Also note that, due to some bizarreness in SQLite's type system (see L), if you want to retain blob-style behavior for B 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 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 in the DDL is B 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 B B =item * DBD_SQLITE_STRING_MODE_PV (default, but B B B): 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 B B, but it's been the default for many years, and changing that would break existing applications. =back =item C or C (deprecated) If truthy, equivalent to setting C to DBD_SQLITE_STRING_MODE_UNICODE_NAIVE; if falsy, equivalent to DBD_SQLITE_STRING_MODE_PV. Prefer C in all new code. =item sqlite_allow_multiple_statements If you set this to true, C 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 (instead of C) 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 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). =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. Typically this contains nothing but white spaces after a semicolon. See above for details. =back =head1 METHODS See also to the L documentation for the details of other common methods. =head2 table_info $sth = $dbh->table_info(undef, $schema, $table, $type, \%attr); Returns all tables and schemas (databases) as specified in L. The schema and table arguments will do a C search. You can specify an ESCAPE character by including an 'Escape' attribute in \%attr. The C<$type> argument accepts a comma separated list of the following types 'TABLE', 'INDEX', 'VIEW', 'TRIGGER', '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: B: Always NULL, as SQLite does not have the concept of catalogs. B: 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. B: The name of the table or view. B: The type of object returned. Will be one of 'TABLE', 'INDEX', 'VIEW', 'TRIGGER', 'LOCAL TEMPORARY' or 'SYSTEM TABLE'. =head2 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, SQLite does not have the concept of catalogs, so the first argument of the methods is usually C, and you'll usually set C for the second one (unless you want to know the primary keys of temporary tables). =head2 foreign_key_info $sth = $dbh->foreign_key_info(undef, $pk_schema, $pk_table, undef, $fk_schema, $fk_table); Returns information about foreign key constraints, as specified in L, but with some limitations : =over =item * information in rows returned by the C<$sth> is incomplete with respect to the L specification. All requested fields are present, but the content is C for some of them. =back The following nonempty fields are returned : B: The primary (unique) key table identifier. B: The primary (unique) key column identifier. B: The foreign key table identifier. B: The foreign key column identifier. B: The column sequence number (starting with 1), when several columns belong to a same constraint. B: The referential action for the UPDATE rule. The following codes are defined: CASCADE 0 RESTRICT 1 SET NULL 2 NO ACTION 3 SET DEFAULT 4 Default is 3 ('NO ACTION'). B: The referential action for the DELETE rule. The codes are the same as for UPDATE_RULE. B: The following codes are defined: INITIALLY DEFERRED 5 INITIALLY IMMEDIATE 6 NOT DEFERRABLE 7 B: Whether the column is primary or unique. B: foreign key support in SQLite must be explicitly turned on through a C command; see L earlier in this manual. =head2 statistics_info $sth = $dbh->statistics_info(undef, $schema, $table, $unique_only, $quick); Returns information about a table and it's indexes, as specified in L, but with some limitations : =over =item * information in rows returned by the C<$sth> is incomplete with respect to the L specification. All requested fields are present, but the content is C for some of them. =back The following nonempty fields are returned : B: The name of the schema (database) that the table 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. B: The name of the table B: Contains 0 for unique indexes, 1 for non-unique indexes B: The name of the index B: SQLite uses 'btree' for all it's indexes B: Column sequence number (starting with 1). B: The name of the column =head2 ping my $bool = $dbh->ping; returns true if the database file exists (or the database is in-memory), and the database connection is active. =head1 DRIVER PRIVATE METHODS The following methods can be called via the func() method with a little tweak, but the use of func() method is now discouraged by the L author for various reasons (see DBI's document L for details). So, if you're using L >= 1.608, use these C methods. If you need to use an older L, you can call these like this: $dbh->func( ..., "(method name without sqlite_ prefix)" ); Exception: C should always be called as is, even with C method (to avoid conflict with DBI's trace() method). $dbh->func( ..., "sqlite_trace"); =head2 $dbh->sqlite_last_insert_rowid() This method returns the last inserted rowid. If you specify an INTEGER PRIMARY KEY as the first column in your table, that is the column that is returned. Otherwise, it is the hidden ROWID column. See the SQLite docs for details. Generally you should not be using this method. Use the L last_insert_id method instead. The usage of this is: $h->last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ]) Running C<$h-Elast_insert_id("","","","")> is the equivalent of running C<$dbh-Esqlite_last_insert_rowid()> directly. =head2 $dbh->sqlite_db_filename() Retrieve the current (main) database filename. If the database is in-memory or temporary, this returns an empty string, or C. =head2 $dbh->sqlite_busy_timeout() Retrieve the current busy timeout. =head2 $dbh->sqlite_busy_timeout( $ms ) Set the current busy timeout. The timeout is in milliseconds. =head2 $dbh->sqlite_create_function( $name, $argc, $code_ref, $flags ) This method will register a new function which will be usable in an SQL query. The method's parameters are: =over =item $name The name of the function. This is the name of the function as it will be used from SQL. =item $argc The number of arguments taken by the function. If this number is -1, the function can take any number of arguments. =item $code_ref This should be a reference to the function's implementation. =item $flags You can optionally pass an extra flag bit to create_function, which then would be ORed with SQLITE_UTF8 (default). As of 1.47_02 (SQLite 3.8.9), only meaning bit is SQLITE_DETERMINISTIC (introduced at SQLite 3.8.3), which can make the function perform better. See C API documentation at L for details. =back For example, here is how to define a now() function which returns the current number of seconds since the epoch: $dbh->sqlite_create_function( 'now', 0, sub { return time } ); After this, it could be used from SQL as: INSERT INTO mytable ( now() ); The function should return a scalar value, and the value is treated as a text (or a number if appropriate) by default. If you do need to specify a type of the return value (like BLOB), you can return a reference to an array that contains the value and the type, as of 1.65_01. $dbh->sqlite_create_function( 'md5', 1, sub { return [md5($_[0]), SQL_BLOB] } ); =head3 REGEXP function SQLite includes syntactic support for an infix operator 'REGEXP', but without any implementation. The C driver automatically registers an implementation that performs standard perl regular expression matching, using current locale. So for example you can search for words starting with an 'A' with a query like SELECT * from table WHERE column REGEXP '\bA\w+' If you want case-insensitive searching, use perl regex flags, like this : SELECT * from table WHERE column REGEXP '(?i:\bA\w+)' The default REGEXP implementation can be overridden through the C API described above. Note that regexp matching will B use SQLite indices, but will iterate over all rows, so it could be quite costly in terms of performance. =head2 $dbh->sqlite_create_collation( $name, $code_ref ) This method manually registers a new function which will be usable in an SQL query as a COLLATE option for sorting. Such functions can also be registered automatically on demand: see section L below. The method's parameters are: =over =item $name The name of the function exposed to SQL. =item $code_ref Reference to the function's implementation. The driver will check that this is a proper sorting function. =back =head2 $dbh->sqlite_collation_needed( $code_ref ) This method manually registers a callback function that will be invoked whenever an undefined collation sequence is required from an SQL statement. The callback is invoked as $code_ref->($dbh, $collation_name) and should register the desired collation using L. An initial callback is already registered by C, so for most common cases it will be simpler to just add your collation sequences in the C<%DBD::SQLite::COLLATION> hash (see section L below). =head2 $dbh->sqlite_create_aggregate( $name, $argc, $pkg, $flags ) This method will register a new aggregate function which can then be used from SQL. The method's parameters are: =over =item $name The name of the aggregate function, this is the name under which the function will be available from SQL. =item $argc This is an integer which tells the SQL parser how many arguments the function takes. If that number is -1, the function can take any number of arguments. =item $pkg This is the package which implements the aggregator interface. =item $flags You can optionally pass an extra flag bit to create_aggregate, which then would be ORed with SQLITE_UTF8 (default). As of 1.47_02 (SQLite 3.8.9), only meaning bit is SQLITE_DETERMINISTIC (introduced at SQLite 3.8.3), which can make the function perform better. See C API documentation at L for details. =back The aggregator interface consists of defining three methods: =over =item new() This method will be called once to create an object which should be used to aggregate the rows in a particular group. The step() and finalize() methods will be called upon the reference return by the method. =item step(@_) This method will be called once for each row in the aggregate. =item finalize() This method will be called once all rows in the aggregate were processed and it should return the aggregate function's result. When there is no rows in the aggregate, finalize() will be called right after new(). =back Here is a simple aggregate function which returns the variance (example adapted from pysqlite): package variance; sub new { bless [], shift; } sub step { my ( $self, $value ) = @_; push @$self, $value; } sub finalize { my $self = $_[0]; my $n = @$self; # Variance is NULL unless there is more than one row return undef unless $n || $n == 1; my $mu = 0; foreach my $v ( @$self ) { $mu += $v; } $mu /= $n; my $sigma = 0; foreach my $v ( @$self ) { $sigma += ($v - $mu)**2; } $sigma = $sigma / ($n - 1); return $sigma; } $dbh->sqlite_create_aggregate( "variance", 1, 'variance' ); The aggregate function can then be used as: SELECT group_name, variance(score) FROM results GROUP BY group_name; For more examples, see the L. =head2 $dbh->sqlite_progress_handler( $n_opcodes, $code_ref ) This method registers a handler to be invoked periodically during long running calls to SQLite. An example use for this interface is to keep a GUI updated during a large query. The parameters are: =over =item $n_opcodes The progress handler is invoked once for every C<$n_opcodes> virtual machine opcodes in SQLite. =item $code_ref Reference to the handler subroutine. If the progress handler returns non-zero, the SQLite operation is interrupted. This feature can be used to implement a "Cancel" button on a GUI dialog box. Set this argument to C if you want to unregister a previous progress handler. =back =head2 $dbh->sqlite_commit_hook( $code_ref ) This method registers a callback function to be invoked whenever a transaction is committed. Any callback set by a previous call to C is overridden. A reference to the previous callback (if any) is returned. Registering an C disables the callback. When the commit hook callback returns zero, the commit operation is allowed to continue normally. If the callback returns non-zero, then the commit is converted into a rollback (in that case, any attempt to I call C<< $dbh->rollback() >> afterwards would yield an error). =head2 $dbh->sqlite_rollback_hook( $code_ref ) This method registers a callback function to be invoked whenever a transaction is rolled back. Any callback set by a previous call to C is overridden. A reference to the previous callback (if any) is returned. Registering an C disables the callback. =head2 $dbh->sqlite_update_hook( $code_ref ) This method registers a callback function to be invoked whenever a row is updated, inserted or deleted. Any callback set by a previous call to C is overridden. A reference to the previous callback (if any) is returned. Registering an C disables the callback. The callback will be called as $code_ref->($action_code, $database, $table, $rowid) where =over =item $action_code is an integer equal to either C, C or C (see L); =item $database is the name of the database containing the affected row; =item $table is the name of the table containing the affected row; =item $rowid is the unique 64-bit signed integer key of the affected row within that table. =back =head2 $dbh->sqlite_set_authorizer( $code_ref ) This method registers an authorizer callback to be invoked whenever SQL statements are being compiled by the L method. The authorizer callback should return C to allow the action, C to disallow the specific action but allow the SQL statement to continue to be compiled, or C to cause the entire SQL statement to be rejected with an error. If the authorizer callback returns any other value, then C call that triggered the authorizer will fail with an error message. An authorizer is used when preparing SQL statements from an untrusted source, to ensure that the SQL statements do not try to access data they are not allowed to see, or that they do not try to execute malicious statements that damage the database. For example, an application may allow a user to enter arbitrary SQL queries for evaluation by a 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 =over =item $action_code is an integer that specifies what action is being authorized (see L). =item $string1, $string2 are strings that depend on the action code (see L). =item $database is the name of the database (C
, C, 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 if this access attempt is directly from top-level SQL code. =back =head2 $dbh->sqlite_backup_from_file( $filename ) This method accesses the SQLite 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 SQLite Online Backup API, and will take a backup of the currently connected database, and write it out to the named file. =head2 $dbh->sqlite_backup_from_dbh( $another_dbh ) This method accesses the SQLite Online Backup API, and will take a backup of the database for the passed handle, 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. You can use this to backup from an in-memory database to another in-memory database. =head2 $dbh->sqlite_backup_to_dbh( $another_dbh ) This method accesses the SQLite Online Backup API, and will take a backup of the currently connected database, and write it out to the passed database handle. =head2 $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('libmemvfs.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" SQLite3 function like above) has some limitations. If the extension you want to use creates other functions that are not native to SQLite, use this method instead. $file (a path to the extension) is mandatory, and $proc (an entry point name) is optional. You need to call C before calling C: $dbh->sqlite_enable_load_extension(1); $dbh->sqlite_load_extension('libsqlitefunctions.so') or die "Cannot load extension: " . $dbh->errstr(); If the extension uses SQLite mutex functions like C, then the extension should be compiled with the same C compile-time setting as this module, see C. =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 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 SQLite. See also L for better profiling options. =head2 $dbh->sqlite_table_column_metadata( $dbname, $tablename, $columnname ) is for internal use only. =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 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 SQLite statement handle such as full table scan count. See L 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 $dbh->sqlite_db_config( $id, $new_integer_value ) You can change how the connected database should behave like this: use DBD::SQLite::Constants qw/:database_connection_configuration_options/; my $dbh = DBI->connect('dbi:SQLite::memory:'); # This disables language features that allow ordinary SQL # to deliberately corrupt the database file $dbh->sqlite_db_config( SQLITE_DBCONFIG_DEFENSIVE, 1 ); # This disables two-arg version of fts3_tokenizer. $dbh->sqlite_db_config( SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, 0 ); C returns the new value after the call. If you just want to know the current value without changing anything, pass a negative integer value. my $current_value = $dbh->sqlite_db_config( SQLITE_DBCONFIG_DEFENSIVE, -1 ); As of this writing, C only supports options that set an integer value. C and C are not supported. See also C for details. =head2 $dbh->sqlite_create_module() Registers a name for a I. 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. =head2 $dbh->sqlite_limit( $category_id, $new_value ) Sets a new run-time limit for the category, and returns the current limit. If the new value is a negative number (or omitted), the limit is unchanged and just returns the current limit. Category ids (SQLITE_LIMIT_LENGTH, SQLITE_LIMIT_VARIABLE_NUMBER, etc) can be imported from DBD::SQLite::Constants. =head2 $dbh->sqlite_get_autocommit() Returns true if the internal SQLite connection is in an autocommit mode. This does not always return the same value as C<< $dbh->{AutoCommit} >>. This returns false if you explicitly issue a C<> statement. =head2 $dbh->sqlite_txn_state() Returns the internal transaction status of SQLite (not of DBI). Return values (SQLITE_TXN_NONE, SQLITE_TXN_READ, SQLITE_TXN_WRITE) can be imported from DBD::SQLite::Constants. You may pass an optional schema name (usually "main"). If SQLite does not support this function, or if you pass a wrong schema name, -1 is returned. =head2 $dbh->sqlite_error_offset() Returns the byte offset of the start of a problematic input SQL token or -1 if the most recent error does not reference a specific token in the input SQL (or DBD::SQLite is built with an older version of SQLite). =head1 DRIVER FUNCTIONS =head2 DBD::SQLite::compile_options() Returns an array of compile options (available since SQLite 3.6.23, bundled in DBD::SQLite 1.30_01), or an empty array if the bundled library is old or compiled with SQLITE_OMIT_COMPILEOPTION_DIAGS. =head2 DBD::SQLite::sqlite_status() Returns a hash reference that holds a set of status information of SQLite runtime such as memory usage or page cache usage (see L for details). Each of the entry contains the current value and the highwater value. my $status = DBD::SQLite::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 DBD::SQLite::strlike($pattern, $string, $escape_char), DBD::SQLite::strglob($pattern, $string) As of 1.49_05 (SQLite 3.10.0), you can use these two functions to see if a string matches a pattern. These may be useful when you create a virtual table or a custom function. See L and L for details. =head1 DRIVER CONSTANTS A subset of SQLite C constants are made available to Perl, because they may be needed when writing hooks or authorizer callbacks. For accessing such constants, the C module must be explicitly Cd at compile time. For example, an authorizer that forbids any DELETE operation would be written as follows : use DBD::SQLite; $dbh->sqlite_set_authorizer(sub { my $action_code = shift; return $action_code == DBD::SQLite::DELETE ? DBD::SQLite::DENY : DBD::SQLite::OK; }); The list of constants implemented in C is given below; more information can be found ad at L. =head2 Authorizer Return Codes OK DENY IGNORE =head2 Action Codes The L 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 FUNCTION undef Function Name SAVEPOINT Operation Savepoint Name =head1 COLLATION FUNCTIONS =head2 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. L explains how collations are used in various SQL expressions. =head2 Builtin collation sequences The following collation sequences are builtin within SQLite : =over =item B Compares string data using memcmp(), regardless of text encoding. =item B 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 The same as binary, except that trailing space characters are ignored. =back In addition, C automatically installs the following collation sequences : =over =item B corresponds to the Perl C operator =item B Perl C operator, in a context where C 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 attribute is set B the first call to a perl collation sequence. The recommended way to activate unicode is to set C 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 and L. To avoid calling these functions every time a C<$dbh> handle is created, C 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 or C 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 and C). 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 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 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. Explanations for using this feature are provided in a separate document: see L. =head1 R* TREE SUPPORT The RTREE extension module within SQLite 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 city_buildings WHERE minLong >= ? AND maxLong <= ? AND minLat >= ? AND maxLat <= ? # ... and those that overlap query coordinates my $overlap_sql = <<""; SELECT id FROM city_buildings 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 SQLite R-Tree page (L). 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 SQLite 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 also supports virtual tables implemented in I: see L 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 : implements a virtual column that exposes file contents. This is especially useful in conjunction with a fulltext index; see L. =item * L : 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 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-SQLite'), 'sqlite3.h'); # or only a particular header, amalgamated in sqlite3.c my $what_i_want = 'parse.h'; my $sqlite3_c = catfile(dist_dir('DBD-SQLite'), '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, and you may want to add DBD::SQLite to your extension's C to ensure your extension users use the same C source/header they use to build DBD::SQLite 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 / C. =head2 Support for custom callbacks for R-Tree queries Custom queries of a R-Tree index using a callback are possible with the SQLite C API (L), 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 to GitHub issues: L or via RT if you prefer: L Note that bugs of bundled SQLite library (i.e. bugs in C) should be reported to the SQLite developers at sqlite.org via their bug tracker or via their mailing list. The master repository is on GitHub: L. We also have a mailing list: L =head1 AUTHORS Matt Sergeant Ematt@sergeant.orgE Francis J. Lacoste Eflacoste@logreport.orgE Wolfgang Sourdeau Ewolfgang@logreport.orgE Adam Kennedy Eadamk@cpan.orgE Max Maischein Ecorion@cpan.orgE Laurent Dami Edami@cpan.orgE Kenichi Ishigaki Eishigaki@cpan.orgE =head1 COPYRIGHT The bundled SQLite code in this distribution is Public Domain. DBD::SQLite 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 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. =cut