ÿØÿà JFIF    ÿÛ „ ( %!1!%*+...983,7(-.- PKЩ].S"""SQLite/VirtualTable/FileContent.pmnu6$#====================================================================== 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 PKЩ]3,:,:SQLite/VirtualTable/PerlData.pmnu6$#====================================================================== 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 PKЩ]ǤYWYWSQLite/VirtualTable.pmnu6$#====================================================================== 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 PKЩ]HeeSQLite/Constants.pmnu6$package 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 PKЩ]tl,T,TSQLite/GetInfo.pmnu6$package 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__ PKЩ]SQLite/Cookbook.podnu6$=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. PKЩ]{hJJSQLite/Fulltext_search.podnu6$=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. PKЩ]J|| SQLite.pmnu6$package 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 PK]"CM''Mem.pmnu[# -*- perl -*- # # DBD::Mem - A DBI driver for in-memory tables # # This module is currently maintained by # # Jens Rehsack # # Copyright (C) 2016,2017 by Jens Rehsack # # All rights reserved. # # You may distribute this module under the terms of either the GNU # General Public License or the Artistic License, as specified in # the Perl README file. require 5.008; use strict; ################# package DBD::Mem; ################# use base qw( DBI::DBD::SqlEngine ); use vars qw($VERSION $ATTRIBUTION $drh); $VERSION = '0.001'; $ATTRIBUTION = 'DBD::Mem by Jens Rehsack'; # no need to have driver() unless you need private methods # sub driver ($;$) { my ( $class, $attr ) = @_; return $drh if ($drh); # do the real work in DBI::DBD::SqlEngine # $attr->{Attribution} = 'DBD::Mem by Jens Rehsack'; $drh = $class->SUPER::driver($attr); return $drh; } sub CLONE { undef $drh; } ##################### package DBD::Mem::dr; ##################### $DBD::Mem::dr::imp_data_size = 0; @DBD::Mem::dr::ISA = qw(DBI::DBD::SqlEngine::dr); # you could put some :dr private methods here # you may need to over-ride some DBI::DBD::SqlEngine::dr methods here # but you can probably get away with just letting it do the work # in most cases ##################### package DBD::Mem::db; ##################### $DBD::Mem::db::imp_data_size = 0; @DBD::Mem::db::ISA = qw(DBI::DBD::SqlEngine::db); use Carp qw/carp/; sub set_versions { my $this = $_[0]; $this->{mem_version} = $DBD::Mem::VERSION; return $this->SUPER::set_versions(); } sub init_valid_attributes { my $dbh = shift; # define valid private attributes # # attempts to set non-valid attrs in connect() or # with $dbh->{attr} will throw errors # # the attrs here *must* start with mem_ or foo_ # # see the STORE methods below for how to check these attrs # $dbh->{mem_valid_attrs} = { mem_version => 1, # verbose DBD::Mem version mem_valid_attrs => 1, # DBD::Mem::db valid attrs mem_readonly_attrs => 1, # DBD::Mem::db r/o attrs mem_meta => 1, # DBD::Mem public access for f_meta mem_tables => 1, # DBD::Mem public access for f_meta }; $dbh->{mem_readonly_attrs} = { mem_version => 1, # verbose DBD::Mem version mem_valid_attrs => 1, # DBD::Mem::db valid attrs mem_readonly_attrs => 1, # DBD::Mem::db r/o attrs mem_meta => 1, # DBD::Mem public access for f_meta }; $dbh->{mem_meta} = "mem_tables"; return $dbh->SUPER::init_valid_attributes(); } sub get_mem_versions { my ( $dbh, $table ) = @_; $table ||= ''; my $meta; my $class = $dbh->{ImplementorClass}; $class =~ s/::db$/::Table/; $table and ( undef, $meta ) = $class->get_table_meta( $dbh, $table, 1 ); $meta or ( $meta = {} and $class->bootstrap_table_meta( $dbh, $meta, $table ) ); return sprintf( "%s using %s", $dbh->{mem_version}, $AnyData2::VERSION ); } package DBD::Mem::st; use strict; use warnings; our $imp_data_size = 0; our @ISA = qw(DBI::DBD::SqlEngine::st); ############################ package DBD::Mem::Statement; ############################ @DBD::Mem::Statement::ISA = qw(DBI::DBD::SqlEngine::Statement); sub open_table ($$$$$) { my ( $self, $data, $table, $createMode, $lockMode ) = @_; my $class = ref $self; $class =~ s/::Statement/::Table/; my $flags = { createMode => $createMode, lockMode => $lockMode, }; if( defined( $data->{Database}->{mem_table_data}->{$table} ) && $data->{Database}->{mem_table_data}->{$table}) { my $t = $data->{Database}->{mem_tables}->{$table}; $t->seek( $data, 0, 0 ); return $t; } return $self->SUPER::open_table($data, $table, $createMode, $lockMode); } # ====== DataSource ============================================================ package DBD::Mem::DataSource; use strict; use warnings; use Carp; @DBD::Mem::DataSource::ISA = "DBI::DBD::SqlEngine::DataSource"; sub complete_table_name ($$;$) { my ( $self, $meta, $table, $respect_case ) = @_; $table; } sub open_data ($) { my ( $self, $meta, $attrs, $flags ) = @_; defined $meta->{data_tbl} or $meta->{data_tbl} = []; } ######################## package DBD::Mem::Table; ######################## # shamelessly stolen from SQL::Statement::RAM use Carp qw/croak/; @DBD::Mem::Table::ISA = qw(DBI::DBD::SqlEngine::Table); use Carp qw(croak); sub new { #my ( $class, $tname, $col_names, $data_tbl ) = @_; my ( $class, $data, $attrs, $flags ) = @_; my $self = $class->SUPER::new($data, $attrs, $flags); my $meta = $self->{meta}; $self->{records} = $meta->{data_tbl}; $self->{index} = 0; $self; } sub bootstrap_table_meta { my ( $self, $dbh, $meta, $table ) = @_; defined $meta->{sql_data_source} or $meta->{sql_data_source} = "DBD::Mem::DataSource"; $meta; } sub fetch_row { my ( $self, $data ) = @_; return $self->{row} = ( $self->{records} and ( $self->{index} < scalar( @{ $self->{records} } ) ) ) ? [ @{ $self->{records}->[ $self->{index}++ ] } ] : undef; } sub push_row { my ( $self, $data, $fields ) = @_; my $currentRow = $self->{index}; $self->{index} = $currentRow + 1; $self->{records}->[$currentRow] = $fields; return 1; } sub truncate { my $self = shift; return splice @{ $self->{records} }, $self->{index}, 1; } sub push_names { my ( $self, $data, $names ) = @_; my $meta = $self->{meta}; $meta->{col_names} = $self->{col_names} = $names; $self->{org_col_names} = [ @{$names} ]; $self->{col_nums} = {}; $self->{col_nums}{ $names->[$_] } = $_ for ( 0 .. scalar @$names - 1 ); } sub drop ($) { my ($self, $data) = @_; delete $data->{Database}{sql_meta}{$self->{table}}; return 1; } # drop sub seek { my ( $self, $data, $pos, $whence ) = @_; return unless defined $self->{records}; my ($currentRow) = $self->{index}; if ( $whence == 0 ) { $currentRow = $pos; } elsif ( $whence == 1 ) { $currentRow += $pos; } elsif ( $whence == 2 ) { $currentRow = @{ $self->{records} } + $pos; } else { croak $self . "->seek: Illegal whence argument ($whence)"; } $currentRow < 0 and croak "Illegal row number: $currentRow"; $self->{index} = $currentRow; } 1; =head1 NAME DBD::Mem - a DBI driver for Mem & MLMem files =head1 SYNOPSIS use DBI; $dbh = DBI->connect('dbi:Mem:', undef, undef, {}); $dbh = DBI->connect('dbi:Mem:', undef, undef, {RaiseError => 1}); # or $dbh = DBI->connect('dbi:Mem:'); $dbh = DBI->connect('DBI:Mem(RaiseError=1):'); and other variations on connect() as shown in the L docs and . Use standard DBI prepare, execute, fetch, placeholders, etc., see L for an example. =head1 DESCRIPTION DBD::Mem is a database management system that works right out of the box. If you have a standard installation of Perl and DBI you can begin creating, accessing, and modifying simple database tables without any further modules. You can add other modules (e.g., SQL::Statement) for improved functionality. DBD::Mem doesn't store any data persistently - all data has the lifetime of the instantiated C<$dbh>. The main reason to use DBD::Mem is to use extended features of L where temporary tables are required. One can use DBD::Mem to simulate C or sub-queries. Bundling C with L will allow us further compatibility checks of L beyond the capabilities of L and L. This will ensure DBI provided basis for drivers like L or L are better prepared and tested for not-file based backends. =head2 Metadata There're no new meta data introduced by C. See L for full description. =head1 GETTING HELP, MAKING SUGGESTIONS, AND REPORTING BUGS If you need help installing or using DBD::Mem, please write to the DBI users mailing list at L or to the comp.lang.perl.modules newsgroup on usenet. I cannot always answer every question quickly but there are many on the mailing list or in the newsgroup who can. DBD developers for DBD's which rely on DBI::DBD::SqlEngine or DBD::Mem or use one of them as an example are suggested to join the DBI developers mailing list at L and strongly encouraged to join our IRC channel at L. If you have suggestions, ideas for improvements, or bugs to report, please report a bug as described in DBI. Do not mail any of the authors directly, you might not get an answer. When reporting bugs, please send the output of C<< $dbh->mem_versions($table) >> for a table that exhibits the bug and as small a sample as you can make of the code that produces the bug. And of course, patches are welcome, too :-). If you need enhancements quickly, you can get commercial support as described at L or you can contact Jens Rehsack at rehsack@cpan.org for commercial support. =head1 AUTHOR AND COPYRIGHT This module is written by Jens Rehsack < rehsack AT cpan.org >. Copyright (c) 2016- by Jens Rehsack, all rights reserved. You may freely distribute and/or modify this module under the terms of either the GNU General Public License (GPL) or the Artistic License, as specified in the Perl README file. =head1 SEE ALSO L for the Database interface of the Perl Programming Language. L and L for the available SQL engines. L where the implementation is shamelessly stolen from to allow DBI bundled Pure-Perl drivers increase the test coverage. L using C for an incredible fast in-memory database engine. =cut PK]}0Gofer.pmnu[{ package DBD::Gofer; use strict; require DBI; require DBI::Gofer::Request; require DBI::Gofer::Response; require Carp; our $VERSION = "0.015327"; # $Id: Gofer.pm 15326 2012-06-06 16:32:38Z Tim $ # # Copyright (c) 2007, Tim Bunce, Ireland # # You may distribute under the terms of either the GNU General Public # License or the Artistic License, as specified in the Perl README file. # attributes we'll allow local STORE our %xxh_local_store_attrib = map { $_=>1 } qw( Active CachedKids Callbacks DbTypeSubclass ErrCount Executed FetchHashKeyName HandleError HandleSetErr InactiveDestroy AutoInactiveDestroy PrintError PrintWarn Profile RaiseError RootClass ShowErrorStatement Taint TaintIn TaintOut TraceLevel Warn dbi_quote_identifier_cache dbi_connect_closure dbi_go_execute_unique ); our %xxh_local_store_attrib_if_same_value = map { $_=>1 } qw( Username dbi_connect_method ); our $drh = undef; # holds driver handle once initialized our $methods_already_installed; sub driver{ return $drh if $drh; DBI->setup_driver('DBD::Gofer'); unless ($methods_already_installed++) { my $opts = { O=> 0x0004 }; # IMA_KEEP_ERR DBD::Gofer::db->install_method('go_dbh_method', $opts); DBD::Gofer::st->install_method('go_sth_method', $opts); DBD::Gofer::st->install_method('go_clone_sth', $opts); DBD::Gofer::db->install_method('go_cache', $opts); DBD::Gofer::st->install_method('go_cache', $opts); } my($class, $attr) = @_; $class .= "::dr"; ($drh) = DBI::_new_drh($class, { 'Name' => 'Gofer', 'Version' => $VERSION, 'Attribution' => 'DBD Gofer by Tim Bunce', }); $drh; } sub CLONE { undef $drh; } sub go_cache { my $h = shift; $h->{go_cache} = shift if @_; # return handle's override go_cache, if it has one return $h->{go_cache} if defined $h->{go_cache}; # or else the transports default go_cache return $h->{go_transport}->{go_cache}; } sub set_err_from_response { # set error/warn/info and propagate warnings my $h = shift; my $response = shift; if (my $warnings = $response->warnings) { warn $_ for @$warnings; } my ($err, $errstr, $state) = $response->err_errstr_state; # Only set_err() if there's an error else leave the current values # (The current values will normally be set undef by the DBI dispatcher # except for methods marked KEEPERR such as ping.) $h->set_err($err, $errstr, $state) if defined $err; return undef; } sub install_methods_proxy { my ($installed_methods) = @_; while ( my ($full_method, $attr) = each %$installed_methods ) { # need to install both a DBI dispatch stub and a proxy stub # (the dispatch stub may be already here due to local driver use) DBI->_install_method($full_method, "", $attr||{}) unless defined &{$full_method}; # now install proxy stubs on the driver side $full_method =~ m/^DBI::(\w\w)::(\w+)$/ or die "Invalid method name '$full_method' for install_method"; my ($type, $method) = ($1, $2); my $driver_method = "DBD::Gofer::${type}::${method}"; next if defined &{$driver_method}; my $sub; if ($type eq 'db') { $sub = sub { return shift->go_dbh_method(undef, $method, @_) }; } else { $sub = sub { shift->set_err($DBI::stderr, "Can't call \$${type}h->$method when using DBD::Gofer"); return; }; } no strict 'refs'; *$driver_method = $sub; } } } { package DBD::Gofer::dr; # ====== DRIVER ====== $imp_data_size = 0; use strict; sub connect_cached { my ($drh, $dsn, $user, $auth, $attr)= @_; $attr ||= {}; return $drh->SUPER::connect_cached($dsn, $user, $auth, { (%$attr), go_connect_method => $attr->{go_connect_method} || 'connect_cached', }); } sub connect { my($drh, $dsn, $user, $auth, $attr)= @_; my $orig_dsn = $dsn; # first remove dsn= and everything after it my $remote_dsn = ($dsn =~ s/;?\bdsn=(.*)$// && $1) or return $drh->set_err($DBI::stderr, "No dsn= argument in '$orig_dsn'"); if ($attr->{go_bypass}) { # don't use DBD::Gofer for this connection # useful for testing with DBI_AUTOPROXY, e.g., t/03handle.t return DBI->connect($remote_dsn, $user, $auth, $attr); } my %go_attr; # extract any go_ attributes from the connect() attr arg for my $k (grep { /^go_/ } keys %$attr) { $go_attr{$k} = delete $attr->{$k}; } # then override those with any attributes embedded in our dsn (not remote_dsn) for my $kv (grep /=/, split /;/, $dsn, -1) { my ($k, $v) = split /=/, $kv, 2; $go_attr{ "go_$k" } = $v; } if (not ref $go_attr{go_policy}) { # if not a policy object already my $policy_class = $go_attr{go_policy} || 'classic'; $policy_class = "DBD::Gofer::Policy::$policy_class" unless $policy_class =~ /::/; _load_class($policy_class) or return $drh->set_err($DBI::stderr, "Can't load $policy_class: $@"); # replace policy name in %go_attr with policy object $go_attr{go_policy} = eval { $policy_class->new(\%go_attr) } or return $drh->set_err($DBI::stderr, "Can't instanciate $policy_class: $@"); } # policy object is left in $go_attr{go_policy} so transport can see it my $go_policy = $go_attr{go_policy}; if ($go_attr{go_cache} and not ref $go_attr{go_cache}) { # if not a cache object already my $cache_class = $go_attr{go_cache}; $cache_class = "DBI::Util::CacheMemory" if $cache_class eq '1'; _load_class($cache_class) or return $drh->set_err($DBI::stderr, "Can't load $cache_class $@"); $go_attr{go_cache} = eval { $cache_class->new() } or $drh->set_err(0, "Can't instanciate $cache_class: $@"); # warning } # delete any other attributes that don't apply to transport my $go_connect_method = delete $go_attr{go_connect_method}; my $transport_class = delete $go_attr{go_transport} or return $drh->set_err($DBI::stderr, "No transport= argument in '$orig_dsn'"); $transport_class = "DBD::Gofer::Transport::$transport_class" unless $transport_class =~ /::/; _load_class($transport_class) or return $drh->set_err($DBI::stderr, "Can't load $transport_class: $@"); my $go_transport = eval { $transport_class->new(\%go_attr) } or return $drh->set_err($DBI::stderr, "Can't instanciate $transport_class: $@"); my $request_class = "DBI::Gofer::Request"; my $go_request = eval { my $go_attr = { %$attr }; # XXX user/pass of fwd server vs db server ? also impact of autoproxy if ($user) { $go_attr->{Username} = $user; $go_attr->{Password} = $auth; } # delete any attributes we can't serialize (or don't want to) delete @{$go_attr}{qw(Profile HandleError HandleSetErr Callbacks)}; # delete any attributes that should only apply to the client-side delete @{$go_attr}{qw(RootClass DbTypeSubclass)}; $go_connect_method ||= $go_policy->connect_method($remote_dsn, $go_attr) || 'connect'; $request_class->new({ dbh_connect_call => [ $go_connect_method, $remote_dsn, $user, $auth, $go_attr ], }) } or return $drh->set_err($DBI::stderr, "Can't instanciate $request_class: $@"); my ($dbh, $dbh_inner) = DBI::_new_dbh($drh, { 'Name' => $dsn, 'USER' => $user, go_transport => $go_transport, go_request => $go_request, go_policy => $go_policy, }); # mark as inactive temporarily for STORE. Active not set until connected() called. $dbh->STORE(Active => 0); # should we ping to check the connection # and fetch dbh attributes my $skip_connect_check = $go_policy->skip_connect_check($attr, $dbh); if (not $skip_connect_check) { if (not $dbh->go_dbh_method(undef, 'ping')) { return undef if $dbh->err; # error already recorded, typically return $dbh->set_err($DBI::stderr, "ping failed"); } } return $dbh; } sub _load_class { # return true or false+$@ my $class = shift; (my $pm = $class) =~ s{::}{/}g; $pm .= ".pm"; return 1 if eval { require $pm }; delete $INC{$pm}; # shouldn't be needed (perl bug?) and assigning undef isn't enough undef; # error in $@ } } { package DBD::Gofer::db; # ====== DATABASE ====== $imp_data_size = 0; use strict; use Carp qw(carp croak); my %dbh_local_store_attrib = %DBD::Gofer::xxh_local_store_attrib; sub connected { shift->STORE(Active => 1); } sub go_dbh_method { my $dbh = shift; my $meta = shift; # @_ now contains ($method_name, @args) my $request = $dbh->{go_request}; $request->init_request([ wantarray, @_ ], $dbh); ++$dbh->{go_request_count}; my $go_policy = $dbh->{go_policy}; my $dbh_attribute_update = $go_policy->dbh_attribute_update(); $request->dbh_attributes( $go_policy->dbh_attribute_list() ) if $dbh_attribute_update eq 'every' or $dbh->{go_request_count}==1; $request->dbh_last_insert_id_args($meta->{go_last_insert_id_args}) if $meta->{go_last_insert_id_args}; my $transport = $dbh->{go_transport} or return $dbh->set_err($DBI::stderr, "Not connected (no transport)"); local $transport->{go_cache} = $dbh->{go_cache} if defined $dbh->{go_cache}; my ($response, $retransmit_sub) = $transport->transmit_request($request); $response ||= $transport->receive_response($request, $retransmit_sub); $dbh->{go_response} = $response or die "No response object returned by $transport"; die "response '$response' returned by $transport is not a response object" unless UNIVERSAL::isa($response,"DBI::Gofer::Response"); if (my $dbh_attributes = $response->dbh_attributes) { # XXX installed_methods piggybacks on dbh_attributes for now if (my $installed_methods = delete $dbh_attributes->{dbi_installed_methods}) { DBD::Gofer::install_methods_proxy($installed_methods) if $dbh->{go_request_count}==1; } # XXX we don't STORE here, we just stuff the value into the attribute cache $dbh->{$_} = $dbh_attributes->{$_} for keys %$dbh_attributes; } my $rv = $response->rv; if (my $resultset_list = $response->sth_resultsets) { # dbh method call returned one or more resultsets # (was probably a metadata method like table_info) # # setup an sth but don't execute/forward it my $sth = $dbh->prepare(undef, { go_skip_prepare_check => 1 }); # set the sth response to our dbh response (tied %$sth)->{go_response} = $response; # setup the sth with the results in our response $sth->more_results; # and return that new sth as if it came from original request $rv = [ $sth ]; } elsif (!$rv) { # should only occur for major transport-level error #carp("no rv in response { @{[ %$response ]} }"); $rv = [ ]; } DBD::Gofer::set_err_from_response($dbh, $response); return (wantarray) ? @$rv : $rv->[0]; } # Methods that should be forwarded but can be cached for my $method (qw( tables table_info column_info primary_key_info foreign_key_info statistics_info data_sources type_info_all get_info parse_trace_flags parse_trace_flag func )) { my $policy_name = "cache_$method"; my $super_name = "SUPER::$method"; my $sub = sub { my $dbh = shift; my $rv; # if we know the remote side doesn't override the DBI's default method # then we might as well just call the DBI's default method on the client # (which may, in turn, call other methods that are forwarded, like get_info) if ($dbh->{dbi_default_methods}{$method} && $dbh->{go_policy}->skip_default_methods()) { $dbh->trace_msg(" !! $method: using local default as remote method is also default\n"); return $dbh->$super_name(@_); } my $cache; my $cache_key; if (my $cache_it = $dbh->{go_policy}->$policy_name(undef, $dbh, @_)) { $cache = $dbh->{go_meta_cache} ||= {}; # keep separate from go_cache $cache_key = sprintf "%s_wa%d(%s)", $policy_name, wantarray||0, join(",\t", map { # XXX basic but sufficient for now !ref($_) ? DBI::neat($_,1e6) : ref($_) eq 'ARRAY' ? DBI::neat_list($_,1e6,",\001") : ref($_) eq 'HASH' ? do { my @k = sort keys %$_; DBI::neat_list([@k,@{$_}{@k}],1e6,",\002") } : do { warn "unhandled argument type ($_)"; $_ } } @_); if ($rv = $cache->{$cache_key}) { $dbh->trace_msg("$method(@_) returning previously cached value ($cache_key)\n",4); my @cache_rv = @$rv; # if it's an sth we have to clone it $cache_rv[0] = $cache_rv[0]->go_clone_sth if UNIVERSAL::isa($cache_rv[0],'DBI::st'); return (wantarray) ? @cache_rv : $cache_rv[0]; } } $rv = [ (wantarray) ? ($dbh->go_dbh_method(undef, $method, @_)) : scalar $dbh->go_dbh_method(undef, $method, @_) ]; if ($cache) { $dbh->trace_msg("$method(@_) caching return value ($cache_key)\n",4); my @cache_rv = @$rv; # if it's an sth we have to clone it #$cache_rv[0] = $cache_rv[0]->go_clone_sth # if UNIVERSAL::isa($cache_rv[0],'DBI::st'); $cache->{$cache_key} = \@cache_rv unless UNIVERSAL::isa($cache_rv[0],'DBI::st'); # XXX cloning sth not yet done } return (wantarray) ? @$rv : $rv->[0]; }; no strict 'refs'; *$method = $sub; } # Methods that can use the DBI defaults for some situations/drivers for my $method (qw( quote quote_identifier )) { # XXX keep DBD::Gofer::Policy::Base in sync my $policy_name = "locally_$method"; my $super_name = "SUPER::$method"; my $sub = sub { my $dbh = shift; # if we know the remote side doesn't override the DBI's default method # then we might as well just call the DBI's default method on the client # (which may, in turn, call other methods that are forwarded, like get_info) if ($dbh->{dbi_default_methods}{$method} && $dbh->{go_policy}->skip_default_methods()) { $dbh->trace_msg(" !! $method: using local default as remote method is also default\n"); return $dbh->$super_name(@_); } # false: use remote gofer # 1: use local DBI default method # code ref: use the code ref my $locally = $dbh->{go_policy}->$policy_name($dbh, @_); if ($locally) { return $locally->($dbh, @_) if ref $locally eq 'CODE'; return $dbh->$super_name(@_); } return $dbh->go_dbh_method(undef, $method, @_); # propagate context }; no strict 'refs'; *$method = $sub; } # Methods that should always fail for my $method (qw( begin_work commit rollback )) { no strict 'refs'; *$method = sub { return shift->set_err($DBI::stderr, "$method not available with DBD::Gofer") } } sub do { my ($dbh, $sql, $attr, @args) = @_; delete $dbh->{Statement}; # avoid "Modification of non-creatable hash value attempted" $dbh->{Statement} = $sql; # for profiling and ShowErrorStatement my $meta = { go_last_insert_id_args => $attr->{go_last_insert_id_args} }; return $dbh->go_dbh_method($meta, 'do', $sql, $attr, @args); } sub ping { my $dbh = shift; return $dbh->set_err('', "can't ping while not connected") # info unless $dbh->SUPER::FETCH('Active'); my $skip_ping = $dbh->{go_policy}->skip_ping(); return ($skip_ping) ? 1 : $dbh->go_dbh_method(undef, 'ping', @_); } sub last_insert_id { my $dbh = shift; my $response = $dbh->{go_response} or return undef; return $response->last_insert_id; } sub FETCH { my ($dbh, $attrib) = @_; # FETCH is effectively already cached because the DBI checks the # attribute cache in the handle before calling FETCH # and this FETCH copies the value into the attribute cache # forward driver-private attributes (except ours) if ($attrib =~ m/^[a-z]/ && $attrib !~ /^go_/) { my $value = $dbh->go_dbh_method(undef, 'FETCH', $attrib); $dbh->{$attrib} = $value; # XXX forces caching by DBI return $dbh->{$attrib} = $value; } # else pass up to DBI to handle return $dbh->SUPER::FETCH($attrib); } sub STORE { my ($dbh, $attrib, $value) = @_; if ($attrib eq 'AutoCommit') { croak "Can't enable transactions when using DBD::Gofer" if !$value; return $dbh->SUPER::STORE($attrib => ($value) ? -901 : -900); } return $dbh->SUPER::STORE($attrib => $value) # we handle this attribute locally if $dbh_local_store_attrib{$attrib} # or it's a private_ (application) attribute or $attrib =~ /^private_/ # or not yet connected (ie being called by DBI->connect) or not $dbh->FETCH('Active'); return $dbh->SUPER::STORE($attrib => $value) if $DBD::Gofer::xxh_local_store_attrib_if_same_value{$attrib} && do { # values are the same my $crnt = $dbh->FETCH($attrib); local $^W; (defined($value) ^ defined($crnt)) ? 0 # definedness differs : $value eq $crnt; }; # dbh attributes are set at connect-time - see connect() carp("Can't alter \$dbh->{$attrib} after handle created with DBD::Gofer") if $dbh->FETCH('Warn'); return $dbh->set_err($DBI::stderr, "Can't alter \$dbh->{$attrib} after handle created with DBD::Gofer"); } sub disconnect { my $dbh = shift; $dbh->{go_transport} = undef; $dbh->STORE(Active => 0); } sub prepare { my ($dbh, $statement, $attr)= @_; return $dbh->set_err($DBI::stderr, "Can't prepare when disconnected") unless $dbh->FETCH('Active'); $attr = { %$attr } if $attr; # copy so we can edit my $policy = delete($attr->{go_policy}) || $dbh->{go_policy}; my $lii_args = delete $attr->{go_last_insert_id_args}; my $go_prepare = delete($attr->{go_prepare_method}) || $dbh->{go_prepare_method} || $policy->prepare_method($dbh, $statement, $attr) || 'prepare'; # e.g. for code not using placeholders my $go_cache = delete $attr->{go_cache}; # set to undef if there are no attributes left for the actual prepare call $attr = undef if $attr and not %$attr; my ($sth, $sth_inner) = DBI::_new_sth($dbh, { Statement => $statement, go_prepare_call => [ 0, $go_prepare, $statement, $attr ], # go_method_calls => [], # autovivs if needed go_request => $dbh->{go_request}, go_transport => $dbh->{go_transport}, go_policy => $policy, go_last_insert_id_args => $lii_args, go_cache => $go_cache, }); $sth->STORE(Active => 0); # XXX needed? It should be the default my $skip_prepare_check = $policy->skip_prepare_check($attr, $dbh, $statement, $attr, $sth); if (not $skip_prepare_check) { $sth->go_sth_method() or return undef; } return $sth; } sub prepare_cached { my ($dbh, $sql, $attr, $if_active)= @_; $attr ||= {}; return $dbh->SUPER::prepare_cached($sql, { %$attr, go_prepare_method => $attr->{go_prepare_method} || 'prepare_cached', }, $if_active); } *go_cache = \&DBD::Gofer::go_cache; } { package DBD::Gofer::st; # ====== STATEMENT ====== $imp_data_size = 0; use strict; my %sth_local_store_attrib = (%DBD::Gofer::xxh_local_store_attrib, NUM_OF_FIELDS => 1); sub go_sth_method { my ($sth, $meta) = @_; if (my $ParamValues = $sth->{ParamValues}) { my $ParamAttr = $sth->{ParamAttr}; # XXX the sort here is a hack to work around a DBD::Sybase bug # but only works properly for params 1..9 # (reverse because of the unshift) my @params = reverse sort keys %$ParamValues; if (@params > 9 && ($sth->{Database}{go_dsn}||'') =~ /dbi:Sybase/) { # if more than 9 then we need to do a proper numeric sort # also warn to alert user of this issue warn "Sybase param binding order hack in use"; @params = sort { $b <=> $a } @params; } for my $p (@params) { # unshift to put binds before execute call unshift @{ $sth->{go_method_calls} }, [ 'bind_param', $p, $ParamValues->{$p}, $ParamAttr->{$p} ]; } } my $dbh = $sth->{Database} or die "panic"; ++$dbh->{go_request_count}; my $request = $sth->{go_request}; $request->init_request($sth->{go_prepare_call}, $sth); $request->sth_method_calls(delete $sth->{go_method_calls}) if $sth->{go_method_calls}; $request->sth_result_attr({}); # (currently) also indicates this is an sth request $request->dbh_last_insert_id_args($meta->{go_last_insert_id_args}) if $meta->{go_last_insert_id_args}; my $go_policy = $sth->{go_policy}; my $dbh_attribute_update = $go_policy->dbh_attribute_update(); $request->dbh_attributes( $go_policy->dbh_attribute_list() ) if $dbh_attribute_update eq 'every' or $dbh->{go_request_count}==1; my $transport = $sth->{go_transport} or return $sth->set_err($DBI::stderr, "Not connected (no transport)"); local $transport->{go_cache} = $sth->{go_cache} if defined $sth->{go_cache}; my ($response, $retransmit_sub) = $transport->transmit_request($request); $response ||= $transport->receive_response($request, $retransmit_sub); $sth->{go_response} = $response or die "No response object returned by $transport"; $dbh->{go_response} = $response; # mainly for last_insert_id if (my $dbh_attributes = $response->dbh_attributes) { # XXX we don't STORE here, we just stuff the value into the attribute cache $dbh->{$_} = $dbh_attributes->{$_} for keys %$dbh_attributes; # record the values returned, so we know that we have fetched # values are which we have fetched (see dbh->FETCH method) $dbh->{go_dbh_attributes_fetched} = $dbh_attributes; } my $rv = $response->rv; # may be undef on error if ($response->sth_resultsets) { # setup first resultset - including sth attributes $sth->more_results; } else { $sth->STORE(Active => 0); $sth->{go_rows} = $rv; } # set error/warn/info (after more_results as that'll clear err) DBD::Gofer::set_err_from_response($sth, $response); return $rv; } sub bind_param { my ($sth, $param, $value, $attr) = @_; $sth->{ParamValues}{$param} = $value; $sth->{ParamAttr}{$param} = $attr if defined $attr; # attr is sticky if not explicitly set return 1; } sub execute { my $sth = shift; $sth->bind_param($_, $_[$_-1]) for (1..@_); push @{ $sth->{go_method_calls} }, [ 'execute' ]; my $meta = { go_last_insert_id_args => $sth->{go_last_insert_id_args} }; return $sth->go_sth_method($meta); } sub more_results { my $sth = shift; $sth->finish; my $response = $sth->{go_response} or do { # e.g., we haven't sent a request yet (ie prepare then more_results) $sth->trace_msg(" No response object present", 3); return; }; my $resultset_list = $response->sth_resultsets or return $sth->set_err($DBI::stderr, "No sth_resultsets"); my $meta = shift @$resultset_list or return undef; # no more result sets #warn "more_results: ".Data::Dumper::Dumper($meta); # pull out the special non-attributes first my ($rowset, $err, $errstr, $state) = delete @{$meta}{qw(rowset err errstr state)}; # copy meta attributes into attribute cache my $NUM_OF_FIELDS = delete $meta->{NUM_OF_FIELDS}; $sth->STORE('NUM_OF_FIELDS', $NUM_OF_FIELDS); # XXX need to use STORE for some? $sth->{$_} = $meta->{$_} for keys %$meta; if (($NUM_OF_FIELDS||0) > 0) { $sth->{go_rows} = ($rowset) ? @$rowset : -1; $sth->{go_current_rowset} = $rowset; $sth->{go_current_rowset_err} = [ $err, $errstr, $state ] if defined $err; $sth->STORE(Active => 1) if $rowset; } return $sth; } sub go_clone_sth { my ($sth1) = @_; # clone an (un-fetched-from) sth - effectively undoes the initial more_results # not 100% so just for use in caching returned sth e.g. table_info my $sth2 = $sth1->{Database}->prepare($sth1->{Statement}, { go_skip_prepare_check => 1 }); $sth2->STORE($_, $sth1->{$_}) for qw(NUM_OF_FIELDS Active); my $sth2_inner = tied %$sth2; $sth2_inner->{$_} = $sth1->{$_} for qw(NUM_OF_PARAMS FetchHashKeyName); die "not fully implemented yet"; return $sth2; } sub fetchrow_arrayref { my ($sth) = @_; my $resultset = $sth->{go_current_rowset} || do { # should only happen if fetch called after execute failed my $rowset_err = $sth->{go_current_rowset_err} || [ 1, 'no result set (did execute fail)' ]; return $sth->set_err( @$rowset_err ); }; return $sth->_set_fbav(shift @$resultset) if @$resultset; $sth->finish; # no more data so finish return undef; } *fetch = \&fetchrow_arrayref; # alias sub fetchall_arrayref { my ($sth, $slice, $max_rows) = @_; my $resultset = $sth->{go_current_rowset} || do { # should only happen if fetch called after execute failed my $rowset_err = $sth->{go_current_rowset_err} || [ 1, 'no result set (did execute fail)' ]; return $sth->set_err( @$rowset_err ); }; my $mode = ref($slice) || 'ARRAY'; return $sth->SUPER::fetchall_arrayref($slice, $max_rows) if ref($slice) or defined $max_rows; $sth->finish; # no more data after this so finish return $resultset; } sub rows { return shift->{go_rows}; } sub STORE { my ($sth, $attrib, $value) = @_; return $sth->SUPER::STORE($attrib => $value) if $sth_local_store_attrib{$attrib} # handle locally # or it's a private_ (application) attribute or $attrib =~ /^private_/; # otherwise warn but do it anyway # this will probably need refining later my $msg = "Altering \$sth->{$attrib} won't affect proxied handle"; Carp::carp($msg) if $sth->FETCH('Warn'); # XXX could perhaps do # push @{ $sth->{go_method_calls} }, [ 'STORE', $attrib, $value ] # if not $sth->FETCH('Executed'); # but how to handle repeat executions? How to we know when an # attribute is being set to affect the current resultset or the # next execution? # Could just always use go_method_calls I guess. # do the store locally anyway, just in case $sth->SUPER::STORE($attrib => $value); return $sth->set_err($DBI::stderr, $msg); } # sub bind_param_array # we use DBI's default, which sets $sth->{ParamArrays}{$param} = $value # and calls bind_param($param, undef, $attr) if $attr. sub execute_array { my $sth = shift; my $attr = shift; $sth->bind_param_array($_, $_[$_-1]) for (1..@_); push @{ $sth->{go_method_calls} }, [ 'execute_array', $attr ]; return $sth->go_sth_method($attr); } *go_cache = \&DBD::Gofer::go_cache; } 1; __END__ =head1 NAME DBD::Gofer - A stateless-proxy driver for communicating with a remote DBI =head1 SYNOPSIS use DBI; $original_dsn = "dbi:..."; # your original DBI Data Source Name $dbh = DBI->connect("dbi:Gofer:transport=$transport;...;dsn=$original_dsn", $user, $passwd, \%attributes); ... use $dbh as if it was connected to $original_dsn ... The C part specifies the name of the module to use to transport the requests to the remote DBI. If $transport doesn't contain any double colons then it's prefixed with C. The C part I of the DSN because everything after C is assumed to be the DSN that the remote DBI should use. The C<...> represents attributes that influence the operation of the Gofer driver or transport. These are described below or in the documentation of the transport module being used. =encoding ISO8859-1 =head1 DESCRIPTION DBD::Gofer is a DBI database driver that forwards requests to another DBI driver, usually in a separate process, often on a separate machine. It tries to be as transparent as possible so it appears that you are using the remote driver directly. DBD::Gofer is very similar to DBD::Proxy. The major difference is that with DBD::Gofer no state is maintained on the remote end. That means every request contains all the information needed to create the required state. (So, for example, every request includes the DSN to connect to.) Each request can be sent to any available server. The server executes the request and returns a single response that includes all the data. This is very similar to the way http works as a stateless protocol for the web. Each request from your web browser can be handled by a different web server process. =head2 Use Cases This may seem like pointless overhead but there are situations where this is a very good thing. Let's consider a specific case. Imagine using DBD::Gofer with an http transport. Your application calls connect(), prepare("select * from table where foo=?"), bind_param(), and execute(). At this point DBD::Gofer builds a request containing all the information about the method calls. It then uses the httpd transport to send that request to an apache web server. This 'dbi execute' web server executes the request (using DBI::Gofer::Execute and related modules) and builds a response that contains all the rows of data, if the statement returned any, along with all the attributes that describe the results, such as $sth->{NAME}. This response is sent back to DBD::Gofer which unpacks it and presents it to the application as if it had executed the statement itself. =head2 Advantages Okay, but you still don't see the point? Well let's consider what we've gained: =head3 Connection Pooling and Throttling The 'dbi execute' web server leverages all the functionality of web infrastructure in terms of load balancing, high-availability, firewalls, access management, proxying, caching. At its most basic level you get a configurable pool of persistent database connections. =head3 Simple Scaling Got thousands of processes all trying to connect to the database? You can use DBD::Gofer to connect them to your smaller pool of 'dbi execute' web servers instead. =head3 Caching Client-side caching is as simple as adding "C" to the DSN. This feature alone can be worth using DBD::Gofer for. =head3 Fewer Network Round-trips DBD::Gofer sends as few requests as possible (dependent on the policy being used). =head3 Thin Clients / Unsupported Platforms You no longer need drivers for your database on every system. DBD::Gofer is pure perl. =head1 CONSTRAINTS There are some natural constraints imposed by the DBD::Gofer 'stateless' approach. But not many: =head2 You can't change database handle attributes after connect() You can't change database handle attributes after you've connected. Use the connect() call to specify all the attribute settings you want. This is because it's critical that when a request is complete the database handle is left in the same state it was when first connected. An exception is made for attributes with names starting "C": They can be set after connect() but the change is only applied locally. =head2 You can't change statement handle attributes after prepare() You can't change statement handle attributes after prepare. An exception is made for attributes with names starting "C": They can be set after prepare() but the change is only applied locally. =head2 You can't use transactions AutoCommit only. Transactions aren't supported. (In theory transactions could be supported when using a transport that maintains a connection, like C does. If you're interested in this please get in touch via dbi-dev@perl.org) =head2 You can't call driver-private sth methods But that's rarely needed anyway. =head1 GENERAL CAVEATS A few important things to keep in mind when using DBD::Gofer: =head2 Temporary tables, locks, and other per-connection persistent state You shouldn't expect any per-session state to persist between requests. This includes locks and temporary tables. Because the server-side may execute your requests via a different database connections, you can't rely on any per-connection persistent state, such as temporary tables, being available from one request to the next. This is an easy trap to fall into. A good way to check for this is to test your code with a Gofer policy package that sets the C policy to 'connect' to force a new connection for each request. The C policy does this. =head2 Driver-private Database Handle Attributes Some driver-private dbh attributes may not be available if the driver has not implemented the private_attribute_info() method (added in DBI 1.54). =head2 Driver-private Statement Handle Attributes Driver-private sth attributes can be set in the prepare() call. TODO Some driver-private sth attributes may not be available if the driver has not implemented the private_attribute_info() method (added in DBI 1.54). =head2 Multiple Resultsets Multiple resultsets are supported only if the driver supports the more_results() method (an exception is made for DBD::Sybase). =head2 Statement activity that also updates dbh attributes Some drivers may update one or more dbh attributes after performing activity on a child sth. For example, DBD::mysql provides $dbh->{mysql_insertid} in addition to $sth->{mysql_insertid}. Currently mysql_insertid is supported via a hack but a more general mechanism is needed for other drivers to use. =head2 Methods that report an error always return undef With DBD::Gofer, a method that sets an error always return an undef or empty list. That shouldn't be a problem in practice because the DBI doesn't define any methods that return meaningful values while also reporting an error. =head2 Subclassing only applies to client-side The RootClass and DbTypeSubclass attributes are not passed to the Gofer server. =head1 CAVEATS FOR SPECIFIC METHODS =head2 last_insert_id To enable use of last_insert_id you need to indicate to DBD::Gofer that you'd like to use it. You do that my adding a C attribute to the do() or prepare() method calls. For example: $dbh->do($sql, { go_last_insert_id_args => [...] }); or $sth = $dbh->prepare($sql, { go_last_insert_id_args => [...] }); The array reference should contains the args that you want passed to the last_insert_id() method. =head2 execute_for_fetch The array methods bind_param_array() and execute_array() are supported. When execute_array() is called the data is serialized and executed in a single round-trip to the Gofer server. This makes it very fast, but requires enough memory to store all the serialized data. The execute_for_fetch() method currently isn't optimised, it uses the DBI fallback behaviour of executing each tuple individually. (It could be implemented as a wrapper for execute_array() - patches welcome.) =head1 TRANSPORTS DBD::Gofer doesn't concern itself with transporting requests and responses to and fro. For that it uses special Gofer transport modules. Gofer transport modules usually come in pairs: one for the 'client' DBD::Gofer driver to use and one for the remote 'server' end. They have very similar names: DBD::Gofer::Transport:: DBI::Gofer::Transport:: Sometimes the transports on the DBD and DBI sides may have different names. For example DBD::Gofer::Transport::http is typically used with DBI::Gofer::Transport::mod_perl (DBD::Gofer::Transport::http and DBI::Gofer::Transport::mod_perl modules are part of the GoferTransport-http distribution). =head2 Bundled Transports Several transport modules are provided with DBD::Gofer: =head3 null The null transport is the simplest of them all. It doesn't actually transport the request anywhere. It just serializes (freezes) the request into a string, then thaws it back into a data structure before passing it to DBI::Gofer::Execute to execute. The same freeze and thaw is applied to the results. The null transport is the best way to test if your application will work with Gofer. Just set the DBI_AUTOPROXY environment variable to "C" (see L below) and run your application, or ideally its test suite, as usual. It doesn't take any parameters. =head3 pipeone The pipeone transport launches a subprocess for each request. It passes in the request and reads the response. The fact that a new subprocess is started for each request ensures that the server side is truly stateless. While this does make the transport I slow, it is useful as a way to test that your application doesn't depend on per-connection state, such as temporary tables, persisting between requests. It's also useful both as a proof of concept and as a base class for the stream driver. =head3 stream The stream driver also launches a subprocess and writes requests and reads responses, like the pipeone transport. In this case, however, the subprocess is expected to handle more that one request. (Though it will be automatically restarted if it exits.) This is the first transport that is truly useful because it can launch the subprocess on a remote machine using C. This means you can now use DBD::Gofer to easily access any databases that's accessible from any system you can login to. You also get all the benefits of ssh, including encryption and optional compression. See L below for an example. =head2 Other Transports Implementing a Gofer transport is I simple, and more transports are very welcome. Just take a look at any existing transports that are similar to your needs. =head3 http See the GoferTransport-http distribution on CPAN: http://search.cpan.org/dist/GoferTransport-http/ =head3 Gearman I know Ask Bjørn Hansen has implemented a transport for the C distributed job system, though it's not on CPAN at the time of writing this. =head1 CONNECTING Simply prefix your existing DSN with "C" where $transport is the name of the Gofer transport you want to use (see L). The C and C attributes must be specified and the C attributes must be last. Other attributes can be specified in the DSN to configure DBD::Gofer and/or the Gofer transport module being used. The main attributes after C, are C and C. These and other attributes are described below. =head2 Using DBI_AUTOPROXY The simplest way to try out DBD::Gofer is to set the DBI_AUTOPROXY environment variable. In this case you don't include the C part. For example: export DBI_AUTOPROXY="dbi:Gofer:transport=null" or, for a more useful example, try: export DBI_AUTOPROXY="dbi:Gofer:transport=stream;url=ssh:user@example.com" =head2 Connection Attributes These attributes can be specified in the DSN. They can also be passed in the \%attr parameter of the DBI connect method by adding a "C" prefix to the name. =head3 transport Specifies the Gofer transport class to use. Required. See L above. If the value does not include C<::> then "C" is prefixed. The transport object can be accessed via $h->{go_transport}. =head3 dsn Specifies the DSN for the remote side to connect to. Required, and must be last. =head3 url Used to tell the transport where to connect to. The exact form of the value depends on the transport used. =head3 policy Specifies the policy to use. See L. If the value does not include C<::> then "C" is prefixed. The policy object can be accessed via $h->{go_policy}. =head3 timeout Specifies a timeout, in seconds, to use when waiting for responses from the server side. =head3 retry_limit Specifies the number of times a failed request will be retried. Default is 0. =head3 retry_hook Specifies a code reference to be called to decide if a failed request should be retried. The code reference is called like this: $transport = $h->{go_transport}; $retry = $transport->go_retry_hook->($request, $response, $transport); If it returns true then the request will be retried, up to the C. If it returns a false but defined value then the request will not be retried. If it returns undef then the default behaviour will be used, as if C had not been specified. The default behaviour is to retry requests where $request->is_idempotent is true, or the error message matches C. =head3 cache Specifies that client-side caching should be performed. The value is the name of a cache class to use. Any class implementing get($key) and set($key, $value) methods can be used. That includes a great many powerful caching classes on CPAN, including the Cache and Cache::Cache distributions. You can use "C" is a shortcut for "C". See L for a description of this simple fast default cache. The cache object can be accessed via $h->go_cache. For example: $dbh->go_cache->clear; # free up memory being used by the cache The cache keys are the frozen (serialized) requests, and the values are the frozen responses. The default behaviour is to only use the cache for requests where $request->is_idempotent is true (i.e., the dbh has the ReadOnly attribute set or the SQL statement is obviously a SELECT without a FOR UPDATE clause.) For even more control you can use the C attribute to pass in an instantiated cache object. Individual methods, including prepare(), can also specify alternative caches via the C attribute. For example, to specify no caching for a particular query, you could use $sth = $dbh->prepare( $sql, { go_cache => 0 } ); This can be used to implement different caching policies for different statements. It's interesting to note that DBD::Gofer can be used to add client-side caching to any (gofer compatible) application, with no code changes and no need for a gofer server. Just set the DBI_AUTOPROXY environment variable like this: DBI_AUTOPROXY='dbi:Gofer:transport=null;cache=1' =head1 CONFIGURING BEHAVIOUR POLICY DBD::Gofer supports a 'policy' mechanism that allows you to fine-tune the number of round-trips to the Gofer server. The policies are grouped into classes (which may be subclassed) and referenced by the name of the class. The L class is the base class for all the policy packages and describes all the available policies. Three policy packages are supplied with DBD::Gofer: L is most 'transparent' but slowest because it makes more round-trips to the Gofer server. L is a reasonable compromise - it's the default policy. L is fastest, but may require code changes in your applications. Generally the default C policy is fine. When first testing an existing application with Gofer it is a good idea to start with the C policy first and then switch to C or a custom policy, for final testing. =head1 AUTHOR Tim Bunce, L =head1 LICENCE AND COPYRIGHT Copyright (c) 2007, Tim Bunce, Ireland. All rights reserved. This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself. See L. =head1 ACKNOWLEDGEMENTS The development of DBD::Gofer and related modules was sponsored by Shopzilla.com (L), where I currently work. =head1 SEE ALSO L, L, L. L, L. L =head1 Caveats for specific drivers This section aims to record issues to be aware of when using Gofer with specific drivers. It usually only documents issues that are not natural consequences of the limitations of the Gofer approach - as documented above. =head1 TODO This is just a random brain dump... (There's more in the source of the Changes file, not the pod) Document policy mechanism Add mechanism for transports to list config params and for Gofer to apply any that match (and warn if any left over?) Driver-private sth attributes - set via prepare() - change DBI spec add hooks into transport base class for checking & updating a result set cache ie via a standard cache interface such as: http://search.cpan.org/~robm/Cache-FastMmap/FastMmap.pm http://search.cpan.org/~bradfitz/Cache-Memcached/lib/Cache/Memcached.pm http://search.cpan.org/~dclinton/Cache-Cache/ http://search.cpan.org/~cleishman/Cache/ Also caching instructions could be passed through the httpd transport layer in such a way that appropriate http cache headers are added to the results so that web caches (squid etc) could be used to implement the caching. (MUST require the use of GET rather than POST requests.) Rework handling of installed_methods to not piggyback on dbh_attributes? Perhaps support transactions for transports where it's possible (ie null and stream)? Would make stream transport (ie ssh) more useful to more people. Make sth_result_attr more like dbh_attributes (using '*' etc) Add @val = FETCH_many(@names) to DBI in C and use in Gofer/Execute? Implement _new_sth in C. =cut PK]kDBM.pmnu[####################################################################### # # DBD::DBM - a DBI driver for DBM files # # Copyright (c) 2004 by Jeff Zucker < jzucker AT cpan.org > # Copyright (c) 2010-2013 by Jens Rehsack & H.Merijn Brand # # All rights reserved. # # You may freely distribute and/or modify this module under the terms # of either the GNU General Public License (GPL) or the Artistic License, # as specified in the Perl README file. # # USERS - see the pod at the bottom of this file # # DBD AUTHORS - see the comments in the code # ####################################################################### require 5.008; use strict; ################# package DBD::DBM; ################# use base qw( DBD::File ); use vars qw($VERSION $ATTRIBUTION $drh $methods_already_installed); $VERSION = '0.08'; $ATTRIBUTION = 'DBD::DBM by Jens Rehsack'; # no need to have driver() unless you need private methods # sub driver ($;$) { my ( $class, $attr ) = @_; return $drh if ($drh); # do the real work in DBD::File # $attr->{Attribution} = 'DBD::DBM by Jens Rehsack'; $drh = $class->SUPER::driver($attr); # install private methods # # this requires that dbm_ (or foo_) be a registered prefix # but you can write private methods before official registration # by hacking the $dbd_prefix_registry in a private copy of DBI.pm # unless ( $methods_already_installed++ ) { DBD::DBM::st->install_method('dbm_schema'); } return $drh; } sub CLONE { undef $drh; } ##################### package DBD::DBM::dr; ##################### $DBD::DBM::dr::imp_data_size = 0; @DBD::DBM::dr::ISA = qw(DBD::File::dr); # you could put some :dr private methods here # you may need to over-ride some DBD::File::dr methods here # but you can probably get away with just letting it do the work # in most cases ##################### package DBD::DBM::db; ##################### $DBD::DBM::db::imp_data_size = 0; @DBD::DBM::db::ISA = qw(DBD::File::db); use Carp qw/carp/; sub validate_STORE_attr { my ( $dbh, $attrib, $value ) = @_; if ( $attrib eq "dbm_ext" or $attrib eq "dbm_lockfile" ) { ( my $newattrib = $attrib ) =~ s/^dbm_/f_/g; carp "Attribute '$attrib' is depreciated, use '$newattrib' instead" if ($^W); $attrib = $newattrib; } return $dbh->SUPER::validate_STORE_attr( $attrib, $value ); } sub validate_FETCH_attr { my ( $dbh, $attrib ) = @_; if ( $attrib eq "dbm_ext" or $attrib eq "dbm_lockfile" ) { ( my $newattrib = $attrib ) =~ s/^dbm_/f_/g; carp "Attribute '$attrib' is depreciated, use '$newattrib' instead" if ($^W); $attrib = $newattrib; } return $dbh->SUPER::validate_FETCH_attr($attrib); } sub set_versions { my $this = $_[0]; $this->{dbm_version} = $DBD::DBM::VERSION; return $this->SUPER::set_versions(); } sub init_valid_attributes { my $dbh = shift; # define valid private attributes # # attempts to set non-valid attrs in connect() or # with $dbh->{attr} will throw errors # # the attrs here *must* start with dbm_ or foo_ # # see the STORE methods below for how to check these attrs # $dbh->{dbm_valid_attrs} = { dbm_type => 1, # the global DBM type e.g. SDBM_File dbm_mldbm => 1, # the global MLDBM serializer dbm_cols => 1, # the global column names dbm_version => 1, # verbose DBD::DBM version dbm_store_metadata => 1, # column names, etc. dbm_berkeley_flags => 1, # for BerkeleyDB dbm_valid_attrs => 1, # DBD::DBM::db valid attrs dbm_readonly_attrs => 1, # DBD::DBM::db r/o attrs dbm_meta => 1, # DBD::DBM public access for f_meta dbm_tables => 1, # DBD::DBM public access for f_meta }; $dbh->{dbm_readonly_attrs} = { dbm_version => 1, # verbose DBD::DBM version dbm_valid_attrs => 1, # DBD::DBM::db valid attrs dbm_readonly_attrs => 1, # DBD::DBM::db r/o attrs dbm_meta => 1, # DBD::DBM public access for f_meta }; $dbh->{dbm_meta} = "dbm_tables"; return $dbh->SUPER::init_valid_attributes(); } sub init_default_attributes { my ( $dbh, $phase ) = @_; $dbh->SUPER::init_default_attributes($phase); $dbh->{f_lockfile} = '.lck'; return $dbh; } sub get_dbm_versions { my ( $dbh, $table ) = @_; $table ||= ''; my $meta; my $class = $dbh->{ImplementorClass}; $class =~ s/::db$/::Table/; $table and ( undef, $meta ) = $class->get_table_meta( $dbh, $table, 1 ); $meta or ( $meta = {} and $class->bootstrap_table_meta( $dbh, $meta, $table ) ); my $dver; my $dtype = $meta->{dbm_type}; eval { $dver = $meta->{dbm_type}->VERSION(); # *) when we're still alive here, everything went ok - no need to check for $@ $dtype .= " ($dver)"; }; if ( $meta->{dbm_mldbm} ) { $dtype .= ' + MLDBM'; eval { $dver = MLDBM->VERSION(); $dtype .= " ($dver)"; # (*) }; eval { my $ser_class = "MLDBM::Serializer::" . $meta->{dbm_mldbm}; my $ser_mod = $ser_class; $ser_mod =~ s|::|/|g; $ser_mod .= ".pm"; require $ser_mod; $dver = $ser_class->VERSION(); $dtype .= ' + ' . $ser_class; # (*) $dver and $dtype .= " ($dver)"; # (*) }; } return sprintf( "%s using %s", $dbh->{dbm_version}, $dtype ); } # you may need to over-ride some DBD::File::db methods here # but you can probably get away with just letting it do the work # in most cases ##################### package DBD::DBM::st; ##################### $DBD::DBM::st::imp_data_size = 0; @DBD::DBM::st::ISA = qw(DBD::File::st); sub FETCH { my ( $sth, $attr ) = @_; if ( $attr eq "NULLABLE" ) { my @colnames = $sth->sql_get_colnames(); # XXX only BerkeleyDB fails having NULL values for non-MLDBM databases, # none accept it for key - but it requires more knowledge between # queries and tables storage to return fully correct information $attr eq "NULLABLE" and return [ map { 0 } @colnames ]; } return $sth->SUPER::FETCH($attr); } # FETCH sub dbm_schema { my ( $sth, $tname ) = @_; return $sth->set_err( $DBI::stderr, 'No table name supplied!' ) unless $tname; my $tbl_meta = $sth->{Database}->func( $tname, "f_schema", "get_sql_engine_meta" ) or return $sth->set_err( $sth->{Database}->err(), $sth->{Database}->errstr() ); return $tbl_meta->{$tname}->{f_schema}; } # you could put some :st private methods here # you may need to over-ride some DBD::File::st methods here # but you can probably get away with just letting it do the work # in most cases ############################ package DBD::DBM::Statement; ############################ @DBD::DBM::Statement::ISA = qw(DBD::File::Statement); ######################## package DBD::DBM::Table; ######################## use Carp; use Fcntl; @DBD::DBM::Table::ISA = qw(DBD::File::Table); my $dirfext = $^O eq 'VMS' ? '.sdbm_dir' : '.dir'; my %reset_on_modify = ( dbm_type => "dbm_tietype", dbm_mldbm => "dbm_tietype", ); __PACKAGE__->register_reset_on_modify( \%reset_on_modify ); my %compat_map = ( ( map { $_ => "dbm_$_" } qw(type mldbm store_metadata) ), dbm_ext => 'f_ext', dbm_file => 'f_file', dbm_lockfile => ' f_lockfile', ); __PACKAGE__->register_compat_map( \%compat_map ); sub bootstrap_table_meta { my ( $self, $dbh, $meta, $table ) = @_; $meta->{dbm_type} ||= $dbh->{dbm_type} || 'SDBM_File'; $meta->{dbm_mldbm} ||= $dbh->{dbm_mldbm} if ( $dbh->{dbm_mldbm} ); $meta->{dbm_berkeley_flags} ||= $dbh->{dbm_berkeley_flags}; defined $meta->{f_ext} or $meta->{f_ext} = $dbh->{f_ext}; unless ( defined( $meta->{f_ext} ) ) { my $ext; if ( $meta->{dbm_type} eq 'SDBM_File' or $meta->{dbm_type} eq 'ODBM_File' ) { $ext = '.pag/r'; } elsif ( $meta->{dbm_type} eq 'NDBM_File' ) { # XXX NDBM_File on FreeBSD (and elsewhere?) may actually be Berkeley # behind the scenes and so create a single .db file. if ( $^O =~ /bsd/i or lc($^O) eq 'darwin' ) { $ext = '.db/r'; } elsif ( $^O eq 'SunOS' or $^O eq 'Solaris' or $^O eq 'AIX' ) { $ext = '.pag/r'; # here it's implemented like dbm - just a bit improved } # else wrapped GDBM } defined($ext) and $meta->{f_ext} = $ext; } $self->SUPER::bootstrap_table_meta( $dbh, $meta, $table ); } sub init_table_meta { my ( $self, $dbh, $meta, $table ) = @_; $meta->{f_dontopen} = 1; unless ( defined( $meta->{dbm_tietype} ) ) { my $tie_type = $meta->{dbm_type}; $INC{"$tie_type.pm"} or require "$tie_type.pm"; $tie_type eq 'BerkeleyDB' and $tie_type = 'BerkeleyDB::Hash'; if ( $meta->{dbm_mldbm} ) { $INC{"MLDBM.pm"} or require "MLDBM.pm"; $meta->{dbm_usedb} = $tie_type; $tie_type = 'MLDBM'; } $meta->{dbm_tietype} = $tie_type; } unless ( defined( $meta->{dbm_store_metadata} ) ) { my $store = $dbh->{dbm_store_metadata}; defined($store) or $store = 1; $meta->{dbm_store_metadata} = $store; } unless ( defined( $meta->{col_names} ) ) { defined( $dbh->{dbm_cols} ) and $meta->{col_names} = $dbh->{dbm_cols}; } $self->SUPER::init_table_meta( $dbh, $meta, $table ); } sub open_data { my ( $className, $meta, $attrs, $flags ) = @_; $className->SUPER::open_data( $meta, $attrs, $flags ); unless ( $flags->{dropMode} ) { # TIEING # # XXX allow users to pass in a pre-created tied object # my @tie_args; if ( $meta->{dbm_type} eq 'BerkeleyDB' ) { my $DB_CREATE = BerkeleyDB::DB_CREATE(); my $DB_RDONLY = BerkeleyDB::DB_RDONLY(); my %tie_flags; if ( my $f = $meta->{dbm_berkeley_flags} ) { defined( $f->{DB_CREATE} ) and $DB_CREATE = delete $f->{DB_CREATE}; defined( $f->{DB_RDONLY} ) and $DB_RDONLY = delete $f->{DB_RDONLY}; %tie_flags = %$f; } my $open_mode = $flags->{lockMode} || $flags->{createMode} ? $DB_CREATE : $DB_RDONLY; @tie_args = ( -Filename => $meta->{f_fqbn}, -Flags => $open_mode, %tie_flags ); } else { my $open_mode = O_RDONLY; $flags->{lockMode} and $open_mode = O_RDWR; $flags->{createMode} and $open_mode = O_RDWR | O_CREAT | O_TRUNC; @tie_args = ( $meta->{f_fqbn}, $open_mode, 0666 ); } if ( $meta->{dbm_mldbm} ) { $MLDBM::UseDB = $meta->{dbm_usedb}; $MLDBM::Serializer = $meta->{dbm_mldbm}; } $meta->{hash} = {}; my $tie_class = $meta->{dbm_tietype}; eval { tie %{ $meta->{hash} }, $tie_class, @tie_args }; $@ and croak "Cannot tie(\%h $tie_class @tie_args): $@"; -f $meta->{f_fqfn} or croak( "No such file: '" . $meta->{f_fqfn} . "'" ); } unless ( $flags->{createMode} ) { my ( $meta_data, $schema, $col_names ); if ( $meta->{dbm_store_metadata} ) { $meta_data = $col_names = $meta->{hash}->{"_metadata \0"}; if ( $meta_data and $meta_data =~ m~(.+)~is ) { $schema = $col_names = $1; $schema =~ s~.*(.+).*~$1~is; $col_names =~ s~.*(.+).*~$1~is; } } $col_names ||= $meta->{col_names} || [ 'k', 'v' ]; $col_names = [ split /,/, $col_names ] if ( ref $col_names ne 'ARRAY' ); if ( $meta->{dbm_store_metadata} and not $meta->{hash}->{"_metadata \0"} ) { $schema or $schema = ''; $meta->{hash}->{"_metadata \0"} = "" . "$schema" . "" . join( ",", @{$col_names} ) . "" . ""; } $meta->{schema} = $schema; $meta->{col_names} = $col_names; } } # you must define drop # it is called from execute of a SQL DROP statement # sub drop ($$) { my ( $self, $data ) = @_; my $meta = $self->{meta}; $meta->{hash} and untie %{ $meta->{hash} }; $self->SUPER::drop($data); # XXX extra_files -f $meta->{f_fqbn} . $dirfext and $meta->{f_ext} eq '.pag/r' and unlink( $meta->{f_fqbn} . $dirfext ); return 1; } # you must define fetch_row, it is called on all fetches; # it MUST return undef when no rows are left to fetch; # checking for $ary[0] is specific to hashes so you'll # probably need some other kind of check for nothing-left. # as Janis might say: "undef's just another word for # nothing left to fetch" :-) # sub fetch_row ($$) { my ( $self, $data ) = @_; my $meta = $self->{meta}; # fetch with %each # my @ary = each %{ $meta->{hash} }; $meta->{dbm_store_metadata} and $ary[0] and $ary[0] eq "_metadata \0" and @ary = each %{ $meta->{hash} }; my ( $key, $val ) = @ary; unless ($key) { delete $self->{row}; return; } my @row = ( ref($val) eq 'ARRAY' ) ? ( $key, @$val ) : ( $key, $val ); $self->{row} = @row ? \@row : undef; return wantarray ? @row : \@row; } # you must define push_row except insert_new_row and update_specific_row is defined # it is called on inserts and updates as primitive # sub insert_new_row ($$$) { my ( $self, $data, $row_aryref ) = @_; my $meta = $self->{meta}; my $ncols = scalar( @{ $meta->{col_names} } ); my $nitems = scalar( @{$row_aryref} ); $ncols == $nitems or croak "You tried to insert $nitems, but table is created with $ncols columns"; my $key = shift @$row_aryref; my $exists; eval { $exists = exists( $meta->{hash}->{$key} ); }; $exists and croak "Row with PK '$key' already exists"; $meta->{hash}->{$key} = $meta->{dbm_mldbm} ? $row_aryref : $row_aryref->[0]; return 1; } # this is where you grab the column names from a CREATE statement # if you don't need to do that, it must be defined but can be empty # sub push_names ($$$) { my ( $self, $data, $row_aryref ) = @_; my $meta = $self->{meta}; # some sanity checks ... my $ncols = scalar(@$row_aryref); $ncols < 2 and croak "At least 2 columns are required for DBD::DBM tables ..."; !$meta->{dbm_mldbm} and $ncols > 2 and croak "Without serializing with MLDBM only 2 columns are supported, you give $ncols"; $meta->{col_names} = $row_aryref; return unless $meta->{dbm_store_metadata}; my $stmt = $data->{sql_stmt}; my $col_names = join( ',', @{$row_aryref} ); my $schema = $data->{Database}->{Statement}; $schema =~ s/^[^\(]+\((.+)\)$/$1/s; $schema = $stmt->schema_str() if ( $stmt->can('schema_str') ); $meta->{hash}->{"_metadata \0"} = "" . "$schema" . "$col_names" . ""; } # fetch_one_row, delete_one_row, update_one_row # are optimized for hash-style lookup without looping; # if you don't need them, omit them, they're optional # but, in that case you may need to define # truncate() and seek(), see below # sub fetch_one_row ($$;$) { my ( $self, $key_only, $key ) = @_; my $meta = $self->{meta}; $key_only and return $meta->{col_names}->[0]; exists $meta->{hash}->{$key} or return; my $val = $meta->{hash}->{$key}; $val = ( ref($val) eq 'ARRAY' ) ? $val : [$val]; my $row = [ $key, @$val ]; return wantarray ? @{$row} : $row; } sub delete_one_row ($$$) { my ( $self, $data, $aryref ) = @_; my $meta = $self->{meta}; delete $meta->{hash}->{ $aryref->[0] }; } sub update_one_row ($$$) { my ( $self, $data, $aryref ) = @_; my $meta = $self->{meta}; my $key = shift @$aryref; defined $key or return; my $row = ( ref($aryref) eq 'ARRAY' ) ? $aryref : [$aryref]; $meta->{hash}->{$key} = $meta->{dbm_mldbm} ? $row : $row->[0]; } sub update_specific_row ($$$$) { my ( $self, $data, $aryref, $origary ) = @_; my $meta = $self->{meta}; my $key = shift @$origary; my $newkey = shift @$aryref; return unless ( defined $key ); $key eq $newkey or delete $meta->{hash}->{$key}; my $row = ( ref($aryref) eq 'ARRAY' ) ? $aryref : [$aryref]; $meta->{hash}->{$newkey} = $meta->{dbm_mldbm} ? $row : $row->[0]; } # you may not need to explicitly DESTROY the ::Table # put cleanup code to run when the execute is done # sub DESTROY ($) { my $self = shift; my $meta = $self->{meta}; $meta->{hash} and untie %{ $meta->{hash} }; $self->SUPER::DESTROY(); } # truncate() and seek() must be defined to satisfy DBI::SQL::Nano # *IF* you define the *_one_row methods above, truncate() and # seek() can be empty or you can use them without actually # truncating or seeking anything but if you don't define the # *_one_row methods, you may need to define these # if you need to do something after a series of # deletes or updates, you can put it in truncate() # which is called at the end of executing # sub truncate ($$) { # my ( $self, $data ) = @_; return 1; } # seek() is only needed if you use IO::File # though it could be used for other non-file operations # that you need to do before "writes" or truncate() # sub seek ($$$$) { # my ( $self, $data, $pos, $whence ) = @_; return 1; } # Th, th, th, that's all folks! See DBD::File and DBD::CSV for other # examples of creating pure perl DBDs. I hope this helped. # Now it's time to go forth and create your own DBD! # Remember to check in with dbi-dev@perl.org before you get too far. # We may be able to make suggestions or point you to other related # projects. 1; __END__ =pod =head1 NAME DBD::DBM - a DBI driver for DBM & MLDBM files =head1 SYNOPSIS use DBI; $dbh = DBI->connect('dbi:DBM:'); # defaults to SDBM_File $dbh = DBI->connect('DBI:DBM(RaiseError=1):'); # defaults to SDBM_File $dbh = DBI->connect('dbi:DBM:dbm_type=DB_File'); # defaults to DB_File $dbh = DBI->connect('dbi:DBM:dbm_mldbm=Storable'); # MLDBM with SDBM_File # or $dbh = DBI->connect('dbi:DBM:', undef, undef); $dbh = DBI->connect('dbi:DBM:', undef, undef, { f_ext => '.db/r', f_dir => '/path/to/dbfiles/', f_lockfile => '.lck', dbm_type => 'BerkeleyDB', dbm_mldbm => 'FreezeThaw', dbm_store_metadata => 1, dbm_berkeley_flags => { '-Cachesize' => 1000, # set a ::Hash flag }, }); and other variations on connect() as shown in the L docs, L and L shown below. Use standard DBI prepare, execute, fetch, placeholders, etc., see L for an example. =head1 DESCRIPTION DBD::DBM is a database management system that works right out of the box. If you have a standard installation of Perl and DBI you can begin creating, accessing, and modifying simple database tables without any further modules. You can add other modules (e.g., SQL::Statement, DB_File etc) for improved functionality. The module uses a DBM file storage layer. DBM file storage is common on many platforms and files can be created with it in many programming languages using different APIs. That means, in addition to creating files with DBI/SQL, you can also use DBI/SQL to access and modify files created by other DBM modules and programs and vice versa. B that in those cases it might be necessary to use a common subset of the provided features. DBM files are stored in binary format optimized for quick retrieval when using a key field. That optimization can be used advantageously to make DBD::DBM SQL operations that use key fields very fast. There are several different "flavors" of DBM which use different storage formats supported by perl modules such as SDBM_File and MLDBM. This module supports all of the flavors that perl supports and, when used with MLDBM, supports tables with any number of columns and insertion of Perl objects into tables. DBD::DBM has been tested with the following DBM types: SDBM_File, NDBM_File, ODBM_File, GDBM_File, DB_File, BerkeleyDB. Each type was tested both with and without MLDBM and with the Data::Dumper, Storable, FreezeThaw, YAML and JSON serializers using the DBI::SQL::Nano or the SQL::Statement engines. =head1 QUICK START DBD::DBM operates like all other DBD drivers - it's basic syntax and operation is specified by DBI. If you're not familiar with DBI, you should start by reading L and the documents it points to and then come back and read this file. If you are familiar with DBI, you already know most of what you need to know to operate this module. Just jump in and create a test script something like the one shown below. You should be aware that there are several options for the SQL engine underlying DBD::DBM, see L. There are also many options for DBM support, see especially the section on L. But here's a sample to get you started. use DBI; my $dbh = DBI->connect('dbi:DBM:'); $dbh->{RaiseError} = 1; for my $sql( split /;\n+/," CREATE TABLE user ( user_name TEXT, phone TEXT ); INSERT INTO user VALUES ('Fred Bloggs','233-7777'); INSERT INTO user VALUES ('Sanjay Patel','777-3333'); INSERT INTO user VALUES ('Junk','xxx-xxxx'); DELETE FROM user WHERE user_name = 'Junk'; UPDATE user SET phone = '999-4444' WHERE user_name = 'Sanjay Patel'; SELECT * FROM user "){ my $sth = $dbh->prepare($sql); $sth->execute; $sth->dump_results if $sth->{NUM_OF_FIELDS}; } $dbh->disconnect; =head1 USAGE This section will explain some usage cases in more detail. To get an overview about the available attributes, see L. =head2 Specifying Files and Directories DBD::DBM will automatically supply an appropriate file extension for the type of DBM you are using. For example, if you use SDBM_File, a table called "fruit" will be stored in two files called "fruit.pag" and "fruit.dir". You should B specify the file extensions in your SQL statements. DBD::DBM recognizes following default extensions for following types: =over 4 =item .pag/r Chosen for dbm_type C<< SDBM_File >>, C<< ODBM_File >> and C<< NDBM_File >> when an implementation is detected which wraps C<< -ldbm >> for C<< NDBM_File >> (e.g. Solaris, AIX, ...). For those types, the C<< .dir >> extension is recognized, too (for being deleted when dropping a table). =item .db/r Chosen for dbm_type C<< NDBM_File >> when an implementation is detected which wraps BerkeleyDB 1.x for C<< NDBM_File >> (typically BSD's, Darwin). =back C<< GDBM_File >>, C<< DB_File >> and C<< BerkeleyDB >> don't usually use a file extension. If your DBM type uses an extension other than one of the recognized types of extensions, you should set the I attribute to the extension B file a bug report as described in DBI with the name of the implementation and extension so we can add it to DBD::DBM. Thanks in advance for that :-). $dbh = DBI->connect('dbi:DBM:f_ext=.db'); # .db extension is used $dbh = DBI->connect('dbi:DBM:f_ext='); # no extension is used # or $dbh->{f_ext}='.db'; # global setting $dbh->{f_meta}->{'qux'}->{f_ext}='.db'; # setting for table 'qux' By default files are assumed to be in the current working directory. To use other directories specify the I attribute in either the connect string or by setting the database handle attribute. For example, this will look for the file /foo/bar/fruit (or /foo/bar/fruit.pag for DBM types that use that extension) my $dbh = DBI->connect('dbi:DBM:f_dir=/foo/bar'); # and this will too: my $dbh = DBI->connect('dbi:DBM:'); $dbh->{f_dir} = '/foo/bar'; # but this is recommended my $dbh = DBI->connect('dbi:DBM:', undef, undef, { f_dir => '/foo/bar' } ); # now you can do my $ary = $dbh->selectall_arrayref(q{ SELECT x FROM fruit }); You can also use delimited identifiers to specify paths directly in SQL statements. This looks in the same place as the two examples above but without setting I: my $dbh = DBI->connect('dbi:DBM:'); my $ary = $dbh->selectall_arrayref(q{ SELECT x FROM "/foo/bar/fruit" }); You can also tell DBD::DBM to use a specified path for a specific table: $dbh->{dbm_tables}->{f}->{file} = q(/foo/bar/fruit); Please be aware that you cannot specify this during connection. If you have SQL::Statement installed, you can use table aliases: my $dbh = DBI->connect('dbi:DBM:'); my $ary = $dbh->selectall_arrayref(q{ SELECT f.x FROM "/foo/bar/fruit" AS f }); See the L for using DROP on tables. =head2 Table locking and flock() Table locking is accomplished using a lockfile which has the same basename as the table's file but with the file extension '.lck' (or a lockfile extension that you supply, see below). This lock file is created with the table during a CREATE and removed during a DROP. Every time the table itself is opened, the lockfile is flocked(). For SELECT, this is a shared lock. For all other operations, it is an exclusive lock (except when you specify something different using the I attribute). Since the locking depends on flock(), it only works on operating systems that support flock(). In cases where flock() is not implemented, DBD::DBM will simply behave as if the flock() had occurred although no actual locking will happen. Read the documentation for flock() for more information. Even on those systems that do support flock(), locking is only advisory - as is always the case with flock(). This means that if another program tries to access the table file while DBD::DBM has the table locked, that other program will *succeed* at opening unless it is also using flock on the '.lck' file. As a result DBD::DBM's locking only really applies to other programs using DBD::DBM or other program written to cooperate with DBD::DBM locking. =head2 Specifying the DBM type Each "flavor" of DBM stores its files in a different format and has different capabilities and limitations. See L for a comparison of DBM types. By default, DBD::DBM uses the C<< SDBM_File >> type of storage since C<< SDBM_File >> comes with Perl itself. If you have other types of DBM storage available, you can use any of them with DBD::DBM. It is strongly recommended to use at least C<< DB_File >>, because C<< SDBM_File >> has quirks and limitations and C<< ODBM_file >>, C<< NDBM_File >> and C<< GDBM_File >> are not always available. You can specify the DBM type using the I attribute which can be set in the connection string or with C<< $dbh->{dbm_type} >> and C<< $dbh->{f_meta}->{$table_name}->{type} >> for per-table settings in cases where a single script is accessing more than one kind of DBM file. In the connection string, just set C<< dbm_type=TYPENAME >> where C<< TYPENAME >> is any DBM type such as GDBM_File, DB_File, etc. Do I use MLDBM as your I as that is set differently, see below. my $dbh=DBI->connect('dbi:DBM:'); # uses the default SDBM_File my $dbh=DBI->connect('dbi:DBM:dbm_type=GDBM_File'); # uses the GDBM_File # You can also use $dbh->{dbm_type} to set the DBM type for the connection: $dbh->{dbm_type} = 'DB_File'; # set the global DBM type print $dbh->{dbm_type}; # display the global DBM type If you have several tables in your script that use different DBM types, you can use the $dbh->{dbm_tables} hash to store different settings for the various tables. You can even use this to perform joins on files that have completely different storage mechanisms. # sets global default of GDBM_File my $dbh->('dbi:DBM:type=GDBM_File'); # overrides the global setting, but only for the tables called # I and I my $dbh->{f_meta}->{foo}->{dbm_type} = 'DB_File'; my $dbh->{f_meta}->{bar}->{dbm_type} = 'BerkeleyDB'; # prints the dbm_type for the table "foo" print $dbh->{f_meta}->{foo}->{dbm_type}; B that you must change the I of a table before you access it for first time. =head2 Adding multi-column support with MLDBM Most of the DBM types only support two columns and even if it would support more, DBD::DBM would only use two. However a CPAN module called MLDBM overcomes this limitation by allowing more than two columns. MLDBM does this by serializing the data - basically it puts a reference to an array into the second column. It can also put almost any kind of Perl object or even B into columns. If you want more than two columns, you B install MLDBM. It's available for many platforms and is easy to install. MLDBM is by default distributed with three serializers - Data::Dumper, Storable, and FreezeThaw. Data::Dumper is the default and Storable is the fastest. MLDBM can also make use of user-defined serialization methods or other serialization modules (e.g. L or L. You select the serializer using the I attribute. Some examples: $dbh=DBI->connect('dbi:DBM:dbm_mldbm=Storable'); # use MLDBM with Storable $dbh=DBI->connect( 'dbi:DBM:dbm_mldbm=MySerializer' # use MLDBM with a user defined module ); $dbh=DBI->connect('dbi::dbm:', undef, undef, { dbm_mldbm => 'YAML' }); # use 3rd party serializer $dbh->{dbm_mldbm} = 'YAML'; # same as above print $dbh->{dbm_mldbm} # show the MLDBM serializer $dbh->{f_meta}->{foo}->{dbm_mldbm}='Data::Dumper'; # set Data::Dumper for table "foo" print $dbh->{f_meta}->{foo}->{mldbm}; # show serializer for table "foo" MLDBM works on top of other DBM modules so you can also set a DBM type along with setting dbm_mldbm. The examples above would default to using SDBM_File with MLDBM. If you wanted GDBM_File instead, here's how: # uses DB_File with MLDBM and Storable $dbh = DBI->connect('dbi:DBM:', undef, undef, { dbm_type => 'DB_File', dbm_mldbm => 'Storable', }); SDBM_File, the default I is quite limited, so if you are going to use MLDBM, you should probably use a different type, see L. See below for some L about MLDBM. =head2 Support for Berkeley DB The Berkeley DB storage type is supported through two different Perl modules - DB_File (which supports only features in old versions of Berkeley DB) and BerkeleyDB (which supports all versions). DBD::DBM supports specifying either "DB_File" or "BerkeleyDB" as a I, with or without MLDBM support. The "BerkeleyDB" dbm_type is experimental and it's interface is likely to change. It currently defaults to BerkeleyDB::Hash and does not currently support ::Btree or ::Recno. With BerkeleyDB, you can specify initialization flags by setting them in your script like this: use BerkeleyDB; my $env = new BerkeleyDB::Env -Home => $dir; # and/or other Env flags $dbh = DBI->connect('dbi:DBM:', undef, undef, { dbm_type => 'BerkeleyDB', dbm_mldbm => 'Storable', dbm_berkeley_flags => { 'DB_CREATE' => DB_CREATE, # pass in constants 'DB_RDONLY' => DB_RDONLY, # pass in constants '-Cachesize' => 1000, # set a ::Hash flag '-Env' => $env, # pass in an environment }, }); Do I set the -Flags or -Filename flags as those are determined and overwritten by the SQL (e.g. -Flags => DB_RDONLY is set automatically when you issue a SELECT statement). Time has not permitted us to provide support in this release of DBD::DBM for further Berkeley DB features such as transactions, concurrency, locking, etc. We will be working on these in the future and would value suggestions, patches, etc. See L and L for further details. =head2 Optimizing the use of key fields Most "flavors" of DBM have only two physical columns (but can contain multiple logical columns as explained above in L). They work similarly to a Perl hash with the first column serving as the key. Like a Perl hash, DBM files permit you to do quick lookups by specifying the key and thus avoid looping through all records (supported by DBI::SQL::Nano only). Also like a Perl hash, the keys must be unique. It is impossible to create two records with the same key. To put this more simply and in SQL terms, the key column functions as the I or UNIQUE INDEX. In DBD::DBM, you can take advantage of the speed of keyed lookups by using DBI::SQL::Nano and a WHERE clause with a single equal comparison on the key field. For example, the following SQL statements are optimized for keyed lookup: CREATE TABLE user ( user_name TEXT, phone TEXT); INSERT INTO user VALUES ('Fred Bloggs','233-7777'); # ... many more inserts SELECT phone FROM user WHERE user_name='Fred Bloggs'; The "user_name" column is the key column since it is the first column. The SELECT statement uses the key column in a single equal comparison - "user_name='Fred Bloggs'" - so the search will find it very quickly without having to loop through all the names which were inserted into the table. In contrast, these searches on the same table are not optimized: 1. SELECT phone FROM user WHERE user_name < 'Fred'; 2. SELECT user_name FROM user WHERE phone = '233-7777'; In #1, the operation uses a less-than (<) comparison rather than an equals comparison, so it will not be optimized for key searching. In #2, the key field "user_name" is not specified in the WHERE clause, and therefore the search will need to loop through all rows to find the requested row(s). B that the underlying DBM storage needs to loop over all I pairs when the optimized fetch is used. SQL::Statement has a massively improved where clause evaluation which costs around 15% of the evaluation in DBI::SQL::Nano - combined with the loop in the DBM storage the speed improvement isn't so impressive. Even if lookups are faster by around 50%, DBI::SQL::Nano and SQL::Statement can benefit from the key field optimizations on updating and deleting rows - and here the improved where clause evaluation of SQL::Statement might beat DBI::SQL::Nano every time the where clause contains not only the key field (or more than one). =head2 Supported SQL syntax DBD::DBM uses a subset of SQL. The robustness of that subset depends on what other modules you have installed. Both options support basic SQL operations including CREATE TABLE, DROP TABLE, INSERT, DELETE, UPDATE, and SELECT. B
for gotchas and warnings about the use of flock(). =head1 BUGS AND LIMITATIONS This module uses hash interfaces of two column file databases. While none of supported SQL engines have support for indices, the following statements really do the same (even if they mean something completely different) for each dbm type which lacks C support: $sth->do( "insert into foo values (1, 'hello')" ); # this statement does ... $sth->do( "update foo set v='world' where k=1" ); # ... the same as this statement $sth->do( "insert into foo values (1, 'world')" ); This is considered to be a bug and might change in a future release. Known affected dbm types are C and C. We highly recommended you use a more modern dbm type such as C. =head1 GETTING HELP, MAKING SUGGESTIONS, AND REPORTING BUGS If you need help installing or using DBD::DBM, please write to the DBI users mailing list at dbi-users@perl.org or to the comp.lang.perl.modules newsgroup on usenet. I cannot always answer every question quickly but there are many on the mailing list or in the newsgroup who can. DBD developers for DBD's which rely on DBD::File or DBD::DBM or use one of them as an example are suggested to join the DBI developers mailing list at dbi-dev@perl.org and strongly encouraged to join our IRC channel at L. If you have suggestions, ideas for improvements, or bugs to report, please report a bug as described in DBI. Do not mail any of the authors directly, you might not get an answer. When reporting bugs, please send the output of $dbh->dbm_versions($table) for a table that exhibits the bug and as small a sample as you can make of the code that produces the bug. And of course, patches are welcome, too :-). If you need enhancements quickly, you can get commercial support as described at L or you can contact Jens Rehsack at rehsack@cpan.org for commercial support in Germany. Please don't bother Jochen Wiedmann or Jeff Zucker for support - they handed over further maintenance to H.Merijn Brand and Jens Rehsack. =head1 ACKNOWLEDGEMENTS Many, many thanks to Tim Bunce for prodding me to write this, and for copious, wise, and patient suggestions all along the way. (Jeff Zucker) I send my thanks and acknowledgements to H.Merijn Brand for his initial refactoring of DBD::File and his strong and ongoing support of SQL::Statement. Without him, the current progress would never have been made. And I have to name Martin J. Evans for each laugh (and correction) of all those funny word creations I (as non-native speaker) made to the documentation. And - of course - I have to thank all those unnamed contributors and testers from the Perl community. (Jens Rehsack) =head1 AUTHOR AND COPYRIGHT This module is written by Jeff Zucker < jzucker AT cpan.org >, who also maintained it till 2007. After that, in 2010, Jens Rehsack & H.Merijn Brand took over maintenance. Copyright (c) 2004 by Jeff Zucker, all rights reserved. Copyright (c) 2010-2013 by Jens Rehsack & H.Merijn Brand, all rights reserved. You may freely distribute and/or modify this module under the terms of either the GNU General Public License (GPL) or the Artistic License, as specified in the Perl README file. =head1 SEE ALSO L, L, L, L, L, L, L, L, L =cut PK]! Sponge.pmnu[use strict; { package DBD::Sponge; require DBI; require Carp; our @EXPORT = qw(); # Do NOT @EXPORT anything. our $VERSION = "12.010003"; # $Id: Sponge.pm 10002 2007-09-26 21:03:25Z Tim $ # # Copyright (c) 1994-2003 Tim Bunce Ireland # # You may distribute under the terms of either the GNU General Public # License or the Artistic License, as specified in the Perl README file. our $drh = undef; # holds driver handle once initialised my $methods_already_installed; sub driver{ return $drh if $drh; DBD::Sponge::db->install_method("sponge_test_installed_method") unless $methods_already_installed++; my($class, $attr) = @_; $class .= "::dr"; ($drh) = DBI::_new_drh($class, { 'Name' => 'Sponge', 'Version' => $VERSION, 'Attribution' => "DBD::Sponge $VERSION (fake cursor driver) by Tim Bunce", }); $drh; } sub CLONE { undef $drh; } } { package DBD::Sponge::dr; # ====== DRIVER ====== our $imp_data_size = 0; # we use default (dummy) connect method } { package DBD::Sponge::db; # ====== DATABASE ====== our $imp_data_size = 0; use strict; sub prepare { my($dbh, $statement, $attribs) = @_; my $rows = delete $attribs->{'rows'} or return $dbh->set_err($DBI::stderr,"No rows attribute supplied to prepare"); my ($outer, $sth) = DBI::_new_sth($dbh, { 'Statement' => $statement, 'rows' => $rows, (map { exists $attribs->{$_} ? ($_=>$attribs->{$_}) : () } qw(execute_hook) ), }); if (my $behave_like = $attribs->{behave_like}) { $outer->{$_} = $behave_like->{$_} foreach (qw(RaiseError PrintError HandleError ShowErrorStatement)); } if ($statement =~ /^\s*insert\b/) { # very basic, just for testing execute_array() $sth->{is_insert} = 1; my $NUM_OF_PARAMS = $attribs->{NUM_OF_PARAMS} or return $dbh->set_err($DBI::stderr,"NUM_OF_PARAMS not specified for INSERT statement"); $sth->STORE('NUM_OF_PARAMS' => $attribs->{NUM_OF_PARAMS} ); } else { #assume select # we need to set NUM_OF_FIELDS my $numFields; if ($attribs->{'NUM_OF_FIELDS'}) { $numFields = $attribs->{'NUM_OF_FIELDS'}; } elsif ($attribs->{'NAME'}) { $numFields = @{$attribs->{NAME}}; } elsif ($attribs->{'TYPE'}) { $numFields = @{$attribs->{TYPE}}; } elsif (my $firstrow = $rows->[0]) { $numFields = scalar @$firstrow; } else { return $dbh->set_err($DBI::stderr, 'Cannot determine NUM_OF_FIELDS'); } $sth->STORE('NUM_OF_FIELDS' => $numFields); $sth->{NAME} = $attribs->{NAME} || [ map { "col$_" } 1..$numFields ]; $sth->{TYPE} = $attribs->{TYPE} || [ (DBI::SQL_VARCHAR()) x $numFields ]; $sth->{PRECISION} = $attribs->{PRECISION} || [ map { length($sth->{NAME}->[$_]) } 0..$numFields -1 ]; $sth->{SCALE} = $attribs->{SCALE} || [ (0) x $numFields ]; $sth->{NULLABLE} = $attribs->{NULLABLE} || [ (2) x $numFields ]; } $outer; } sub type_info_all { my ($dbh) = @_; my $ti = [ { TYPE_NAME => 0, DATA_TYPE => 1, PRECISION => 2, LITERAL_PREFIX => 3, LITERAL_SUFFIX => 4, CREATE_PARAMS => 5, NULLABLE => 6, CASE_SENSITIVE => 7, SEARCHABLE => 8, UNSIGNED_ATTRIBUTE=> 9, MONEY => 10, AUTO_INCREMENT => 11, LOCAL_TYPE_NAME => 12, MINIMUM_SCALE => 13, MAXIMUM_SCALE => 14, }, [ 'VARCHAR', DBI::SQL_VARCHAR(), undef, "'","'", undef, 0, 1, 1, 0, 0,0,undef,0,0 ], ]; return $ti; } sub FETCH { my ($dbh, $attrib) = @_; # In reality this would interrogate the database engine to # either return dynamic values that cannot be precomputed # or fetch and cache attribute values too expensive to prefetch. return 1 if $attrib eq 'AutoCommit'; # else pass up to DBI to handle return $dbh->SUPER::FETCH($attrib); } sub STORE { my ($dbh, $attrib, $value) = @_; # would normally validate and only store known attributes # else pass up to DBI to handle if ($attrib eq 'AutoCommit') { return 1 if $value; # is already set Carp::croak("Can't disable AutoCommit"); } return $dbh->SUPER::STORE($attrib, $value); } sub sponge_test_installed_method { my ($dbh, @args) = @_; return $dbh->set_err(42, "not enough parameters") unless @args >= 2; return \@args; } } { package DBD::Sponge::st; # ====== STATEMENT ====== our $imp_data_size = 0; use strict; sub execute { my $sth = shift; # hack to support ParamValues (when not using bind_param) $sth->{ParamValues} = (@_) ? { map { $_ => $_[$_-1] } 1..@_ } : undef; if (my $hook = $sth->{execute_hook}) { &$hook($sth, @_) or return; } if ($sth->{is_insert}) { my $row; $row = (@_) ? [ @_ ] : die "bind_param not supported yet" ; my $NUM_OF_PARAMS = $sth->{NUM_OF_PARAMS}; return $sth->set_err($DBI::stderr, @$row." values bound (@$row) but $NUM_OF_PARAMS expected") if @$row != $NUM_OF_PARAMS; { local $^W; $sth->trace_msg("inserting (@$row)\n"); } push @{ $sth->{rows} }, $row; } else { # mark select sth as Active $sth->STORE(Active => 1); } # else do nothing for select as data is already in $sth->{rows} return 1; } sub fetch { my ($sth) = @_; my $row = shift @{$sth->{'rows'}}; unless ($row) { $sth->STORE(Active => 0); return undef; } return $sth->_set_fbav($row); } *fetchrow_arrayref = \&fetch; sub FETCH { my ($sth, $attrib) = @_; # would normally validate and only fetch known attributes # else pass up to DBI to handle return $sth->SUPER::FETCH($attrib); } sub STORE { my ($sth, $attrib, $value) = @_; # would normally validate and only store known attributes # else pass up to DBI to handle return $sth->SUPER::STORE($attrib, $value); } } 1; __END__ =pod =head1 NAME DBD::Sponge - Create a DBI statement handle from Perl data =head1 SYNOPSIS my $sponge = DBI->connect("dbi:Sponge:","","",{ RaiseError => 1 }); my $sth = $sponge->prepare($statement, { rows => $data, NAME => $names, %attr } ); =head1 DESCRIPTION DBD::Sponge is useful for making a Perl data structure accessible through a standard DBI statement handle. This may be useful to DBD module authors who need to transform data in this way. =head1 METHODS =head2 connect() my $sponge = DBI->connect("dbi:Sponge:","","",{ RaiseError => 1 }); Here's a sample syntax for creating a database handle for the Sponge driver. No username and password are needed. =head2 prepare() my $sth = $sponge->prepare($statement, { rows => $data, NAME => $names, %attr } ); =over 4 =item * The C<$statement> here is an arbitrary statement or name you want to provide as identity of your data. If you're using DBI::Profile it will appear in the profile data. Generally it's expected that you are preparing a statement handle as if a C
. =item * makes the table name the filename minus the extension. =back DBI:CSV:f_dir=data;f_ext=.csv In the above example and when C contains both F and F
, DBD::File will open F and the table will be named "table". If F does not exist but F
does that file is opened and the table is also called "table". If C is not specified and F exists it will be opened and the table will be called "table.csv" which is probably not what you want. NOTE: even though extensions are case-insensitive, table names are not. DBI:CSV:f_dir=data;f_ext=.csv/r The C flag means the file extension is required and any filename that does not match the extension is ignored. Usually you set it on the dbh but it may be overridden per table (see L). =head4 f_schema This will set the schema name and defaults to the owner of the directory in which the table file resides. You can set C to C. my $dbh = DBI->connect ("dbi:CSV:", "", "", { f_schema => undef, f_dir => "data", f_ext => ".csv/r", }) or die $DBI::errstr; By setting the schema you affect the results from the tables call: my @tables = $dbh->tables (); # no f_schema "merijn".foo "merijn".bar # f_schema => "dbi" "dbi".foo "dbi".bar # f_schema => undef foo bar Defining C to the empty string is equal to setting it to C so the DSN can be C<"dbi:CSV:f_schema=;f_dir=.">. =head4 f_lock The C attribute is used to set the locking mode on the opened table files. Note that not all platforms support locking. By default, tables are opened with a shared lock for reading, and with an exclusive lock for writing. The supported modes are: 0: No locking at all. 1: Shared locks will be used. 2: Exclusive locks will be used. But see L below. =head4 f_lockfile If you wish to use a lockfile extension other than C<.lck>, simply specify the C attribute: $dbh = DBI->connect ("dbi:DBM:f_lockfile=.foo"); $dbh->{f_lockfile} = ".foo"; $dbh->{dbm_tables}{qux}{f_lockfile} = ".foo"; If you wish to disable locking, set the C to C<0>. $dbh = DBI->connect ("dbi:DBM:f_lockfile=0"); $dbh->{f_lockfile} = 0; $dbh->{dbm_tables}{qux}{f_lockfile} = 0; =head4 f_encoding With this attribute, you can set the encoding in which the file is opened. This is implemented using C<< binmode $fh, ":encoding()" >>. =head4 f_meta Private data area aliasing L which contains information about the tables this module handles. Table meta data might not be available until the table has been accessed for the first time e.g., by issuing a select on it however it is possible to pre-initialize attributes for each table you use. DBD::File recognizes the (public) attributes C, C, C, C, C, C, C, in addition to the attributes L already supports. Be very careful when modifying attributes you do not know, the consequence might be a destroyed or corrupted table. C is an attribute applicable to table meta data only and you will not find a corresponding attribute in the dbh. Whilst it may be reasonable to have several tables with the same column names, it is not for the same file name. If you need access to the same file using different table names, use C as the SQL engine and the C keyword: SELECT * FROM tbl AS t1, tbl AS t2 WHERE t1.id = t2.id C can be an absolute path name or a relative path name but if it is relative, it is interpreted as being relative to the C attribute of the table meta data. When C is set DBD::File will use C as specified and will not attempt to work out an alternative for C using the C
and C attribute. While C is a private and readonly attribute (which means, you cannot modify it's values), derived drivers might provide restricted write access through another attribute. Well known accessors are C for L, C for L and C for L. =head3 New opportunities for attributes from DBI::DBD::SqlEngine =head4 sql_table_source C<< $dbh->{sql_table_source} >> can be set to I (and is the default setting of DBD::File). This provides usual behaviour of previous DBD::File releases on @ary = DBI->data_sources ($driver); @ary = DBI->data_sources ($driver, \%attr); @ary = $dbh->data_sources (); @ary = $dbh->data_sources (\%attr); @names = $dbh->tables ($catalog, $schema, $table, $type); $sth = $dbh->table_info ($catalog, $schema, $table, $type); $sth = $dbh->table_info ($catalog, $schema, $table, $type, \%attr); $dbh->func ("list_tables"); =head4 sql_data_source C<< $dbh->{sql_data_source} >> can be set to either I, which is default and provides the well known behavior of DBD::File releases prior to 0.41, or I, which reuses already opened file-handle for operations. =head3 Internally private attributes to deal with SQL backends Do not modify any of these private attributes unless you understand the implications of doing so. The behavior of DBD::File and derived DBDs might be unpredictable when one or more of those attributes are modified. =head4 sql_nano_version Contains the version of loaded DBI::SQL::Nano. =head4 sql_statement_version Contains the version of loaded SQL::Statement. =head4 sql_handler Contains either the text 'SQL::Statement' or 'DBI::SQL::Nano'. =head4 sql_ram_tables Contains optionally temporary tables. =head4 sql_flags Contains optional flags to instantiate the SQL::Parser parsing engine when SQL::Statement is used as SQL engine. See L for valid flags. =head2 Driver private methods =head3 Default DBI methods =head4 data_sources The C method returns a list of subdirectories of the current directory in the form "dbi:CSV:f_dir=$dirname". If you want to read the subdirectories of another directory, use my ($drh) = DBI->install_driver ("CSV"); my (@list) = $drh->data_sources (f_dir => "/usr/local/csv_data"); =head3 Additional methods The following methods are only available via their documented name when DBD::File is used directly. Because this is only reasonable for testing purposes, the real names must be used instead. Those names can be computed by replacing the C in the method name with the driver prefix. =head4 f_versions Signature: sub f_versions (;$) { my ($table_name) = @_; $table_name ||= "."; ... } Returns the versions of the driver, including the DBI version, the Perl version, DBI::PurePerl version (if DBI::PurePerl is active) and the version of the SQL engine in use. my $dbh = DBI->connect ("dbi:File:"); my $f_versions = $dbh->func ("f_versions"); print "$f_versions\n"; __END__ # DBD::File 0.41 using IO::File (1.16) # DBI::DBD::SqlEngine 0.05 using SQL::Statement 1.406 # DBI 1.623 # OS darwin (12.2.1) # Perl 5.017006 (darwin-thread-multi-ld-2level) Called in list context, f_versions will return an array containing each line as single entry. Some drivers might use the optional (table name) argument and modify version information related to the table (e.g. DBD::DBM provides storage backend information for the requested table, when it has a table name). =head1 KNOWN BUGS AND LIMITATIONS =over 4 =item * This module uses flock () internally but flock is not available on all platforms. On MacOS and Windows 95 there is no locking at all (perhaps not so important on MacOS and Windows 95, as there is only a single user). =item * The module stores details about the handled tables in a private area of the driver handle (C<$drh>). This data area is not shared between different driver instances, so several C<< DBI->connect () >> calls will cause different table instances and private data areas. This data area is filled for the first time when a table is accessed, either via an SQL statement or via C and is not destroyed until the table is dropped or the driver handle is released. Manual destruction is possible via L. The following attributes are preserved in the data area and will evaluated instead of driver globals: =over 8 =item f_ext =item f_dir =item f_dir_search =item f_lock =item f_lockfile =item f_encoding =item f_schema =item col_names =item sql_identifier_case =back The following attributes are preserved in the data area only and cannot be set globally. =over 8 =item f_file =back The following attributes are preserved in the data area only and are computed when initializing the data area: =over 8 =item f_fqfn =item f_fqbn =item f_fqln =item table_name =back For DBD::CSV tables this means, once opened "foo.csv" as table named "foo", another table named "foo" accessing the file "foo.txt" cannot be opened. Accessing "foo" will always access the file "foo.csv" in memorized C, locking C via memorized C. You can use L or the C attribute for a specific table to work around this. =item * When used with SQL::Statement and temporary tables e.g., CREATE TEMP TABLE ... the table data processing bypasses DBD::File::Table. No file system calls will be made and there are no clashes with existing (file based) tables with the same name. Temporary tables are chosen over file tables, but they will not covered by C. =back =head1 AUTHOR This module is currently maintained by H.Merijn Brand < h.m.brand at xs4all.nl > and Jens Rehsack < rehsack at googlemail.com > The original author is Jochen Wiedmann. =head1 COPYRIGHT AND LICENSE Copyright (C) 2009-2013 by H.Merijn Brand & Jens Rehsack Copyright (C) 2004-2009 by Jeff Zucker Copyright (C) 1998-2004 by Jochen Wiedmann All rights reserved. You may freely distribute and/or modify this module under the terms of either the GNU General Public License (GPL) or the Artistic License, as specified in the Perl README file. =head1 SEE ALSO L, L, L, L, L, L, and L =cut PKЩ].S"""SQLite/VirtualTable/FileContent.pmnu6$PKЩ]3,:,:5#SQLite/VirtualTable/PerlData.pmnu6$PKЩ]ǤYWYW]SQLite/VirtualTable.pmnu6$PKЩ]HeeOSQLite/Constants.pmnu6$PKЩ]tl,T,TSQLite/GetInfo.pmnu6$PKЩ]oSQLite/Cookbook.podnu6$PKЩ]{hJJ}SQLite/Fulltext_search.podnu6$PKЩ]J|| #SQLite.pmnu6$PK]"CM''&FMem.pmnu[PK]}0mGofer.pmnu[PK]k-DBM.pmnu[PK]! Sponge.pmnu[PK]5vPvPFile/Developers.podnu[PK],0H;;iFile/Roadmap.podnu[PK]ɻKFile/HowTo.podnu[PK]7LNullP.pmnu[PK]+3 $ $ìGofer/Transport/stream.pmnu[PK] Dۃ,Gofer/Transport/pipeone.pmnu[PK]]Ïr Gofer/Transport/null.pmnu[PK]=o11Gofer/Transport/Base.pmnu[PK]\ ::,+Gofer/Policy/classic.pmnu[PK]_3Gofer/Policy/pedantic.pmnu[PK]3<ȍ% % 9Gofer/Policy/rush.pmnu[PK]cCGofer/Policy/Base.pmnu[PK]e!}0}0 XExampleP.pmnu[PK])bS99ֈFile.pmnu[PKQF(