diff options
Diffstat (limited to 'lib/DBD')
| -rw-r--r-- | lib/DBD/SQLite.pm | 2406 | ||||
| -rw-r--r-- | lib/DBD/SQLite/Cookbook.pod | 194 |
2 files changed, 2600 insertions, 0 deletions
diff --git a/lib/DBD/SQLite.pm b/lib/DBD/SQLite.pm new file mode 100644 index 0000000..6a04afa --- /dev/null +++ b/lib/DBD/SQLite.pm @@ -0,0 +1,2406 @@ +package DBD::SQLite; + +use 5.006; +use strict; +use DBI 1.57 (); +use DynaLoader (); + +our $VERSION = '1.38_01'; +our @ISA = 'DynaLoader'; + +# sqlite_version cache (set in the XS bootstrap) +our ($sqlite_version, $sqlite_version_number); + +# not sure if we still need these... +our ($err, $errstr); + +__PACKAGE__->bootstrap($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_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 }); + + $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 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; + } else { + $attr->{$key} = $value; + } + } + } + + # 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 '') { + require Win32; + require File::Basename; + my ($file, $dir, $suffix) = File::Basename::fileparse($real); + my $short = Win32::GetShortPathName($real); + if ( $short && -f $short ) { + # Existing files will work directly. + $real = $short; + } elsif ( -d $dir ) { + # We are creating a new file. + # Does the directory it's in at least exist? + $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 http://www.sqlite.org/vtab.html#xfindfunction) +sub regexp { + use locale; + return if !defined $_[0] || !defined $_[1]; + return scalar($_[1] =~ $_[0]); +} + +package DBD::SQLite::db; + +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) = @_; + + 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 $dbh->FETCH('sqlite_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 _get_version { + return ( DBD::SQLite::db::FETCH($_[0], 'sqlite_version') ); +} + +my %info = ( + 17 => 'SQLite', # SQL_DBMS_NAME + 18 => \&_get_version, # SQL_DBMS_VER + 29 => '"', # SQL_IDENTIFIER_QUOTE_CHAR +); + +sub get_info { + my($dbh, $info_type) = @_; + my $v = $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' ); + $sth_databases->execute; + 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 +) 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 FROM $master_table WHERE type = ?"); + $sth->execute("table"); + 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)"); + $t_sth->execute; + while(my $col = $t_sth->fetchrow_hashref) { + next unless $col->{pk}; + push @pk_info, { + TABLE_SCHEM => $dbname, + TABLE_NAME => $tbname, + COLUMN_NAME => $col->{name}, + KEY_SEQ => scalar @pk_info + 1, + PK_NAME => 'PRIMARY KEY', + }; + } + } + } + + 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 + ); + +sub foreign_key_info { + my ($dbh, $pk_catalog, $pk_schema, $pk_table, $fk_catalog, $fk_schema, $fk_table) = @_; + + my $databases = $dbh->selectall_arrayref("PRAGMA database_list", {Slice => {}}); + + 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 FROM $master_table WHERE type = ?", undef, "table"); + for my $table (@$tables) { + my $tbname = $table->[0]; + 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)"); + $sth->execute; + 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)"); + $t_sth->execute; + 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}; + + push @fk_info, { + 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', + }; + } + } + } + + 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; +} + +sub type_info_all { + return; # XXX code just copied from DBD::Oracle, not yet thought about +# return [ +# { +# TYPE_NAME => 0, +# DATA_TYPE => 1, +# COLUMN_SIZE => 2, +# LITERAL_PREFIX => 3, +# LITERAL_SUFFIX => 4, +# CREATE_PARAMS => 5, +# NULLABLE => 6, +# CASE_SENSITIVE => 7, +# SEARCHABLE => 8, +# UNSIGNED_ATTRIBUTE => 9, +# FIXED_PREC_SCALE => 10, +# AUTO_UNIQUE_VALUE => 11, +# LOCAL_TYPE_NAME => 12, +# MINIMUM_SCALE => 13, +# MAXIMUM_SCALE => 14, +# SQL_DATA_TYPE => 15, +# SQL_DATETIME_SUB => 16, +# NUM_PREC_RADIX => 17, +# }, +# [ 'CHAR', 1, 255, '\'', '\'', 'max length', 1, 1, 3, +# undef, '0', '0', undef, undef, undef, 1, undef, undef +# ], +# [ 'NUMBER', 3, 38, undef, undef, 'precision,scale', 1, '0', 3, +# '0', '0', '0', undef, '0', 38, 3, undef, 10 +# ], +# [ 'DOUBLE', 8, 15, undef, undef, undef, 1, '0', 3, +# '0', '0', '0', undef, undef, undef, 8, undef, 10 +# ], +# [ 'DATE', 9, 19, '\'', '\'', undef, 1, '0', 3, +# undef, '0', '0', undef, '0', '0', 11, undef, undef +# ], +# [ 'VARCHAR', 12, 1024*1024, '\'', '\'', 'max length', 1, 1, 3, +# undef, '0', '0', undef, undef, undef, 12, undef, undef +# ] +# ]; +} + +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")}); + $sth_columns->execute; + + 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+) ?\((\d+)(?:,(\d+))?\)/$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 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<http://www.sqlite.org/>. + +B<DBD::SQLite> 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<nothing> +else. + +SQLite supports the following features: + +=over 4 + +=item Implements a large subset of SQL92 + +See L<http://www.sqlite.org/lang.html> 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<DBD::CSV>. + +=item Atomic commit and rollback + +Yes, B<DBD::SQLite> 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<DBI> 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 NOTABLE DIFFERENCES FROM OTHER DRIVERS + +=head2 Database Name Is A File Name + +SQLite creates a file per a database. You should pass the C<path> of +the database file (with or without a parent directory) in the DBI +connection string (as a database C<name>): + + 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. + +=head2 Accessing A Database With Other Tools + +To access the database from the command line, try using C<dbish> +which comes with the L<DBI::Shell> module. Just type: + + dbish dbi:SQLite:foo.db + +On the command line to access the file F<foo.db>. + +Alternatively you can install SQLite from the link above without +conflicting with B<DBD::SQLite> and use the supplied C<sqlite3> +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 three workarounds for this. + +=over 4 + +=item Use bind_param() explicitly + +As shown above in the C<BLOB> section, you can always use +C<bind_param()> 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 Set C<sqlite_see_if_its_a_number> database handle attribute + +As of version 1.32_02, you can use C<sqlite_see_if_its_a_number> +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<sqlite3> +command line tool, this attribute would help you. + +=back + +=head2 Placeholders + +SQLite supports several placeholder expressions, including C<?> +and C<:AAAA>. Consult the L<DBI> and sqlite documentation for +details. + +L<http://www.sqlite.org/lang_expr.html#varparam> + +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 Foreign Keys + +B<BE PREPARED! WOLVES APPROACH!!> + +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 pragma actually to enable this feature and enforce the constraints. + +To do this, issue the following pragma (see below), 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 B<please do extensive testing to ensure +that your applications will continue to work when the foreign keys +support is enabled by default>. It is very likely that the sqlite +team will turn it default-on in the future, and we plan to do it +NO LATER THAN they do so. + +See L<http://www.sqlite.org/foreignkeys.html> for details. + +=head2 Pragma + +SQLite has a set of "Pragma"s to modifiy 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. DBD::SQLite actually sets some (like +C<show_datatypes>) for you when you connect to a database. +See L<http://www.sqlite.org/pragma.html> for details. + +=head2 Transactions + +DBI/DBD::SQLite's transactions may be a bit confusing. They behave +differently according to the status of the C<AutoCommit> 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<begin_work> method, or issue a C<BEGIN> +statement. To end it, call C<commit/rollback> 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<BEGIN> statement (only when an actual transaction has not +begun yet) but you're not allowed to call C<begin_work> method +(if you don't issue a C<BEGIN>, it will be issued internally). +You can commit or roll it back freely. Another transaction will +automatically begins 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<AutoCommit> mode is independent from the autocommit mode +of the internal SQLite library, which always begins by a C<BEGIN> +statement, and ends by a C<COMMIT> or a <ROLLBACK>. + +=head2 Transaction and Database Locking + +The default transaction behavior of SQLite is C<deferred>, 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<BEGIN> on the current thread has executed, and eventually +cause a "deadlock". To avoid this, DBD::SQLite internally issues +a C<BEGIN IMMEDIATE> when you begin a transaction by +C<begin_work> or under the C<AutoCommit> mode (since 1.38_01). + +If you really need to turn off this feature for some reasons, +set C<sqlite_use_immediate_transaction> database handle attribute +to false, and the default C<deferred> transaction will be used. + + my $dbh = DBI->connect("dbi:SQLite::memory:", "", "", { + sqlite_use_immediate_transaction => 0, + }); + +See L<http://sqlite.org/lockingv3.html> for locking details. + +=head2 C<< $sth->finish >> and Transaction Rollback + +As the L<DBI> doc says, you almost certainly do B<not> need to +call L<DBI/finish> 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<SELECT> statement is one of such exceptional +cases. + +SQLite prohibits C<ROLLBACK> of unfinished C<SELECT> statements in +a transaction (See L<http://sqlite.org/lang_transaction.html> for +details). So you need to call C<finish> 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<DBI>'s statement handle is not supposed to process multiple +statements at a time. So if you pass a string that contains multiple +statements (a C<dump>) to a statement handle (via C<prepare> or C<do>), +L<DBD::SQLite> only processes the first statement, and discards the +rest. + +Since 1.30_01, you can retrieve those ignored (unprepared) statements +via C<< $sth->{sqlite_unprepared_statements} >>. It usually contains +nothing but white spaces, but if you really care, you can check this +attribute to see if there's anything left undone. Also, if you set +a C<sqlite_allow_multiple_statements> attribute of a database handle +to true when you connect to a database, C<do> method automatically +checks the C<sqlite_unprepared_statements> attribute, and if it finds +anything undone (even if what's left is just a single white space), +it repeats the process again, to the end. + +=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<http://axkit.org>), +and it returned within 2 seconds. He was seriously considering +switching his log analysis code to use this little speed demon! + +Oh yeah, and that was with no indexes on the table, on a 400MHz PIII. + +For best performance be sure to tune your hdparm settings if you +are using linux. Also you might want to set: + + PRAGMA synchronous = OFF + +Which will prevent sqlite from doing fsync's when writing (which +slows down non-transactional writes significantly) at the expense +of some peace of mind. Also try playing with the cache_size pragma. + +The memory usage of SQLite can also be tuned using the cache_size +pragma. + + $dbh->do("PRAGMA cache_size = 800000"); + +The above will allocate 800M for DB cache; the default is 2M. +Your sweet spot probably lies somewhere in between. + +=head1 DRIVER PRIVATE ATTRIBUTES + +=head2 Database Handle Attributes + +=over 4 + +=item sqlite_version + +Returns the version of the SQLite library which B<DBD::SQLite> is using, +e.g., "2.8.0". Can only be read. + +=item sqlite_unicode + +If set to a true value, B<DBD::SQLite> will turn the UTF-8 flag on for all +text strings coming out of the database (this feature is currently disabled +for perl < 5.8.5). For more details on the UTF-8 flag see +L<perlunicode>. The default is for the UTF-8 flag to be turned off. + +Also note that due to some bizarreness in SQLite's type system (see +L<http://www.sqlite.org/datatype3.html>), if you want to retain +blob-style behavior for B<some> columns under C<< $dbh->{sqlite_unicode} = 1 +>> (say, to store images in the database), you have to state so +explicitly using the 3-argument form of L<DBI/bind_param> when doing +updates: + + use DBI qw(:sql_types); + $dbh->{sqlite_unicode} = 1; + my $sth = $dbh->prepare("INSERT INTO mytable (blobcolumn) VALUES (?)"); + + # Binary_data will be stored as is. + $sth->bind_param(1, $binary_data, SQL_BLOB); + +Defining the column type as C<BLOB> in the DDL is B<not> sufficient. + +This attribute was originally named as C<unicode>, and renamed to +C<sqlite_unicode> for integrity since version 1.26_06. Old C<unicode> +attribute is still accessible but will be deprecated in the near future. + +=item sqlite_allow_multiple_statements + +If you set this to true, C<do> method will process multiple +statements at one go. This may be handy, but with performance +penalty. See above for details. + +=item sqlite_use_immediate_transaction + +If you set this to true, DBD::SQLite tries to issue a C<begin +immediate transaction> (instead of C<begin transaction>) when +necessary. See above for details. + +As of version 1.38_01, this attribute is set to true by default. +If you really need to use C<deferred> transactions for some reasons, +set this to false explicitly. + +=item sqlite_see_if_its_a_number + +If you set this to true, DBD::SQLite tries to see if the bind values +are number or not, and does not quote if they are numbers. See above +for details. + +=back + +=head2 Statement Handle Attributes + +=over 4 + +=item sqlite_unprepared_statements + +Returns an unprepared part of the statement you pass to C<prepare>. +Typically this contains nothing but white spaces after a semicolon. +See above for details. + +=back + +=head1 METHODS + +See also to the L<DBI> documentation for the details of other common +methods. + +=head2 table_info + + $sth = $dbh->table_info(undef, $schema, $table, $type, \%attr); + +Returns all tables and schemas (databases) as specified in L<DBI/table_info>. +The schema and table arguments will do a C<LIKE> 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', +'VIEW', 'LOCAL TEMPORARY' and 'SYSTEM TABLE' (by default all are returned). +Note that a statement handle is returned, and not a direct list of tables. + +The following fields are returned: + +B<TABLE_CAT>: Always NULL, as SQLite does not have the concept of catalogs. + +B<TABLE_SCHEM>: The name of the schema (database) that the table or view is +in. The default schema is 'main', temporary tables are in 'temp' and other +databases will be in the name given when the database was attached. + +B<TABLE_NAME>: The name of the table or view. + +B<TABLE_TYPE>: The type of object returned. Will be one of 'TABLE', 'VIEW', +'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 mothods is usually C<undef>, and you'll usually +set C<undef> 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<DBI/foreign_key_info>, but with some limitations : + +=over + +=item * + +information in rows returned by the C<$sth> is incomplete with +respect to the L<DBI/foreign_key_info> specification. All requested fields +are present, but the content is C<undef> for some of them. + +=back + +The following nonempty fields are returned : + +B<PKTABLE_NAME>: +The primary (unique) key table identifier. + +B<PKCOLUMN_NAME>: +The primary (unique) key column identifier. + +B<FKTABLE_NAME>: +The foreign key table identifier. + +B<FKCOLUMN_NAME>: +The foreign key column identifier. + +B<KEY_SEQ>: +The column sequence number (starting with 1), when +several columns belong to a same constraint. + +B<UPDATE_RULE>: +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<DELETE_RULE>: +The referential action for the DELETE rule. +The codes are the same as for UPDATE_RULE. + +Unfortunately, the B<DEFERRABILITY> field is always C<undef>; +as a matter of fact, deferrability clauses are supported by SQLite, +but they can't be reported because the C<PRAGMA foreign_key_list> +tells nothing about them. + +B<UNIQUE_OR_PRIMARY>: +Whether the column is primary or unique. + +B<Note>: foreign key support in SQLite must be explicitly turned on through +a C<PRAGMA> command; see L</"Foreign keys"> earlier in this manual. + +=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<DBI> author +for various reasons (see DBI's document +L<http://search.cpan.org/dist/DBI/lib/DBI/DBD.pm#Using_install_method()_to_expose_driver-private_methods> +for details). So, if you're using L<DBI> >= 1.608, use these C<sqlite_> +methods. If you need to use an older L<DBI>, you can call these like this: + + $dbh->func( ..., "(method name without sqlite_ prefix)" ); + +Exception: C<sqlite_trace> should always be called as is, even with C<func()> +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<DBI> last_insert_id +method instead. The usage of this is: + + $h->last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ]) + +Running C<$h-E<gt>last_insert_id("","","","")> is the equivalent of running +C<$dbh-E<gt>sqlite_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 C<undef>. + +=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 ) + +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. + +=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 use from SQL as: + + INSERT INTO mytable ( now() ); + +=head3 REGEXP function + +SQLite includes syntactic support for an infix operator 'REGEXP', but +without any implementation. The C<DBD::SQLite> 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<create_function> API described above. + +Note that regexp matching will B<not> 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</"COLLATION FUNCTIONS"> 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</"sqlite_create_collation">. + +An initial callback is already registered by C<DBD::SQLite>, +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</"COLLATION FUNCTIONS"> below). + +=head2 $dbh->sqlite_create_aggregate( $name, $argc, $pkg ) + +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. + +=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<DBD::SQLite::Cookbook>. + +=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<undef> 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<sqlite_commit_hook> is overridden. A reference to the previous +callback (if any) is returned. Registering an C<undef> 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<explicitly> 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<sqlite_rollback_hook> is overridden. A reference to the previous +callback (if any) is returned. Registering an C<undef> 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<sqlite_update_hook> is overridden. A reference to the previous +callback (if any) is returned. Registering an C<undef> 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<DBD::SQLite::INSERT>, +C<DBD::SQLite::DELETE> or C<DBD::SQLite::UPDATE> +(see L</"Action Codes">); + +=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<DBI/prepare> method. The +authorizer callback should return C<DBD::SQLite::OK> to allow the +action, C<DBD::SQLite::IGNORE> to disallow the specific action but +allow the SQL statement to continue to be compiled, or +C<DBD::SQLite::DENY> to cause the entire SQL statement to be rejected +with an error. If the authorizer callback returns any other value, +then then C<prepare> 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</"Action Codes">). + +=item $string1, $string2 + +are strings that depend on the action code +(see L</"Action Codes">). + +=item $database + +is the name of the database (C<main>, C<temp>, 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<undef> 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_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('libsqlitefunctions.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 you need to, say, create other functions from an extension, use this method. $file (a path to the extension) is mandatory, and $proc (an entry point name) is optional. You need to call C<sqlite_enable_load_extension> before calling C<sqlite_load_extension>. + +=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<DBI/TRACING> 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<DBI::Profile> for better profiling options. + +=head2 $dbh->sqlite_table_column_metadata( $dbname, $tablename, $columnname ) + +is for internal use only. + +=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<http://www.sqlite.org/c3ref/c_status_malloc_count.html> 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 $dbh->sqlite_db_status() + +Returns a hash reference that holds a set of status information of database connection such as cache usage. See L<http://www.sqlite.org/c3ref/c_dbstatus_options.html> 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<http://www.sqlite.org/c3ref/c_stmtstatus_counter.html> 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. + +=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<DBD::SQLite> module must be explicitly C<use>d 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<DBD::SQLite> is given +below; more information can be found ad +at L<http://www.sqlite.org/c3ref/constlist.html>. + +=head2 Authorizer Return Codes + + OK + DENY + IGNORE + +=head2 Action Codes + +The L</set_authorizer> 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<http://www.sqlite.org/datatype3.html#collation> +explains how collations are used in various SQL expressions. + +=head2 Builtin collation sequences + +The following collation sequences are builtin within SQLite : + +=over + +=item B<BINARY> + +Compares string data using memcmp(), regardless of text encoding. + +=item B<NOCASE> + +The same as binary, except the 26 upper case characters of ASCII are +folded to their lower case equivalents before the comparison is +performed. Note that only ASCII characters are case folded. SQLite +does not attempt to do full UTF case folding due to the size of the +tables required. + +=item B<RTRIM> + +The same as binary, except that trailing space characters are ignored. + +=back + +In addition, C<DBD::SQLite> automatically installs the +following collation sequences : + +=over + +=item B<perl> + +corresponds to the Perl C<cmp> operator + +=item B<perllocale> + +Perl C<cmp> operator, in a context where C<use locale> is activated. + +=back + +=head2 Usage + +You can write for example + + CREATE TABLE foo( + txt1 COLLATE perl, + txt2 COLLATE perllocale, + txt3 COLLATE nocase + ) + +or + + SELECT * FROM foo ORDER BY name COLLATE perllocale + +=head2 Unicode handling + +If the attribute C<< $dbh->{sqlite_unicode} >> is set, strings coming from +the database and passed to the collation function will be properly +tagged with the utf8 flag; but this only works if the +C<sqlite_unicode> attribute is set B<before> the first call to +a perl collation sequence . The recommended way to activate unicode +is to set the parameter at connection time : + + my $dbh = DBI->connect( + "dbi:SQLite:dbname=foo", "", "", + { + RaiseError => 1, + sqlite_unicode => 1, + } + ); + +=head2 Adding user-defined collations + +The native SQLite API for adding user-defined collations is +exposed through methods L</"sqlite_create_collation"> and +L</"sqlite_collation_needed">. + +To avoid calling these functions every time a C<$dbh> handle is +created, C<DBD::SQLite> offers a simpler interface through the +C<%DBD::SQLite::COLLATION> hash : just insert your own +collation functions in that hash, and whenever an unknown +collation name is encountered in SQL, the appropriate collation +function will be loaded on demand from the hash. For example, +here is a way to sort text values regardless of their accented +characters : + + use DBD::SQLite; + $DBD::SQLite::COLLATION{no_accents} = sub { + my ( $a, $b ) = map lc, @_; + tr[àâáäåãçðèêéëìîíïñòôóöõøùûúüý] + [aaaaaacdeeeeiiiinoooooouuuuy] for $a, $b; + $a cmp $b; + }; + my $dbh = DBI->connect("dbi:SQLite:dbname=dbfile"); + my $sql = "SELECT ... FROM ... ORDER BY ... COLLATE no_accents"); + my $rows = $dbh->selectall_arrayref($sql); + +The builtin C<perl> or C<perllocale> collations are predefined +in that same hash. + +The COLLATION hash is a global registry within the current process; +hence there is a risk of undesired side-effects. Therefore, to +prevent action at distance, the hash is implemented as a "write-only" +hash, that will happily accept new entries, but will raise an +exception if any attempt is made to override or delete a existing +entry (including the builtin C<perl> and C<perllocale>). + +If you really, really need to change or delete an entry, you can +always grab the tied object underneath C<%DBD::SQLite::COLLATION> --- +but don't do that unless you really know what you are doing. Also +observe that changes in the global hash will not modify existing +collations in existing database handles: it will only affect new +I<requests> for collations. In other words, if you want to change +the behaviour of a collation within an existing C<$dbh>, you +need to call the L</create_collation> method directly. + +=head1 FULLTEXT SEARCH + +The FTS3 extension module within SQLite allows users to create special +tables with a built-in full-text index (hereafter "FTS3 tables"). The +full-text index allows the user to efficiently query the database for +all rows that contain one or more instances of a specified word (hereafter +a "token"), even if the table contains many large documents. + + +=head2 Short introduction to FTS3 + +The detailed documentation for FTS3 can be found +at L<http://www.sqlite.org/fts3.html>. Here is a very short example : + + $dbh->do(<<"") or die DBI::errstr; + CREATE VIRTUAL TABLE fts_example USING fts3(content) + + my $sth = $dbh->prepare("INSERT INTO fts_example(content) VALUES (?))"); + $sth->execute($_) foreach @docs_to_insert; + + my $results = $dbh->selectall_arrayref(<<""); + SELECT docid, snippet(content) FROM fts_example WHERE content MATCH 'foo' + + +The key points in this example are : + +=over + +=item * + +The syntax for creating FTS3 tables is + + CREATE VIRTUAL TABLE <table_name> USING fts3(<columns>) + +where C<< <columns> >> 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<content>. +In addition, FTS3 tables have an implicit column called C<docid> +(or also C<rowid>) 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<MATCH> 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<snippet(...)> 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 +FTS3 tables, so we strongly invite you to read +the full documentation at at L<http://www.sqlite.org/fts3.html>. + +B<Incompatible change> : +starting from version 1.31, C<DBD::SQLite> uses the new, recommended +"Enhanced Query Syntax" for binary set operators (AND, OR, NOT, possibly +nested with parenthesis). Previous versions of C<DBD::SQLite> used the +"Standard Query Syntax" (see L<http://www.sqlite.org/fts3.html#section_3_2>). +Unfortunately this is a compilation switch, so it cannot be tuned +at runtime; however, since FTS3 was never advertised in versions prior +to 1.31, the change should be invisible to the vast majority of +C<DBD::SQLite> users. If, however, there are any applications +that nevertheless were built using the "Standard Query" syntax, +they have to be migrated, because the precedence of the C<OR> operator +has changed. Conversion from old to new syntax can be +automated through L<DBD::SQLite::FTS3Transitional>, published +in a separate distribution. + +=head2 Tokenizers + +The behaviour of full-text indexes strongly depends on how +documents are split into I<tokens>; therefore FTS3 table +declarations can explicitly specify how to perform +tokenization: + + CREATE ... USING fts3(<columns>, tokenize=<tokenizer>) + +where C<< <tokenizer> >> is a sequence of space-separated +words that triggers a specific tokenizer, as explained below. + +=head3 SQLite builtin tokenizers + +SQLite comes with three builtin tokenizers : + +=over + +=item simple + +Under the I<simple> 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<porter> 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 + +If SQLite is compiled with the SQLITE_ENABLE_ICU +pre-processor symbol defined, then there exists a built-in tokenizer +named "icu" implemented using the ICU library, and taking an +ICU locale identifier as argument (such as "tr_TR" for +Turkish as used in Turkey, or "en_AU" for English as used in +Australia). For example: + + CREATE VIRTUAL TABLE thai_text USING fts3(text, tokenize=icu th_TH) + +The ICU tokenizer implementation is very simple. It splits the input +text according to the ICU rules for finding word boundaries and +discards any tokens that consist entirely of white-space. This may be +suitable for some applications in some locales, but not all. If more +complex processing is required, for example to implement stemming or +discard punctuation, use the perl tokenizer as explained below. + +=back + +=head3 Perl tokenizers + +In addition to the builtin SQLite tokenizers, C<DBD::SQLite> +implements a I<perl> tokenizer, that can hook to any tokenizing +algorithm written in Perl. This is specified as follows : + + CREATE ... USING fts3(<columns>, tokenize=perl '<perl_function>') + +where C<< <perl_function> >> 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<my_func> in the main +program, write + + CREATE ... USING fts3(<columns>, tokenize=perl 'main::my_func') + +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 FTS3 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 + +Instead of writing tokenizers by hand, you can grab one of those +already implemented in the L<Search::Tokenizer> module : + + use Search::Tokenizer; + $dbh->do(<<"") or die DBI::errstr; + CREATE ... USING fts3(<columns>, + tokenize=perl 'Search::Tokenizer::unaccent') + +or you can use L<Search::Tokenizer/new> to build +your own tokenizer. + + +=head2 Incomplete handling of utf8 characters + +The current FTS3 implementation in SQLite is far from complete with +respect to utf8 handling : in particular, variable-length characters +are not treated correctly by the builtin functions +C<offsets()> and C<snippet()>. + +=head2 Database space for FTS3 + +FTS3 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. If copies of documents are also +available as external resources (for example files on the filesystem), +that space can sometimes be spared --- see the tip in the +L<Cookbook|DBD::SQLite::Cookbook/"Sparing database disk space">. + +=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 try_rtree + WHERE minLong >= ? AND maxLong <= ? + AND minLat >= ? AND maxLat <= ? + + # ... and those that overlap query coordinates + my $overlap_sql = <<""; + SELECT id FROM try_rtree + 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<http://www.sqlite.org/rtree.html>). Note that custom R-Tree +queries using callbacks, as mentioned in the prior link, have not been +implemented yet. + +=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<Makefile.PL>, +and you may want to add DBD::SQLite to your extension's C<CONFIGURE_REQUIRES> +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<sqlite2_blob_open> / C<sqlite2_blob_close>. + +=head2 Flags for sqlite3_open_v2 + +Support the full API of sqlite3_open_v2 (flags for opening the file). + +=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<http://www.sqlite.org/rtree.html>), 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 via the CPAN bug tracker at + +L<http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite> + +Note that bugs of bundled sqlite library (i.e. bugs in C<sqlite3.[ch]>) +should be reported to the sqlite developers at sqlite.org via their bug +tracker or via their mailing list. + +=head1 AUTHORS + +Matt Sergeant E<lt>matt@sergeant.orgE<gt> + +Francis J. Lacoste E<lt>flacoste@logreport.orgE<gt> + +Wolfgang Sourdeau E<lt>wolfgang@logreport.orgE<gt> + +Adam Kennedy E<lt>adamk@cpan.orgE<gt> + +Max Maischein E<lt>corion@cpan.orgE<gt> + +Laurent Dami E<lt>dami@cpan.orgE<gt> + +Kenichi Ishigaki E<lt>ishigaki@cpan.orgE<gt> + +=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 - 2012 Adam Kennedy. + +Some parts copyright 2009 - 2012 Kenichi Ishigaki. + +Some parts derived from L<DBD::SQLite::Amalgamation> +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 diff --git a/lib/DBD/SQLite/Cookbook.pod b/lib/DBD/SQLite/Cookbook.pod new file mode 100644 index 0000000..8b40c5b --- /dev/null +++ b/lib/DBD/SQLite/Cookbook.pod @@ -0,0 +1,194 @@ +=head1 NAME + +DBD::SQLite::Cookbook - The DBD::SQLite Cookbook + +=head1 DESCRIPTION + +This is the L<DBD::SQLite> cookbook. + +It is intended to provide a place to keep a variety of functions and +formals for use in callback APIs in L<DBD::SQLite>. + +=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 FTS3 fulltext indexing + +=head2 Sparing database disk space + +As explained in L<http://www.sqlite.org/fts3.html#section_6>, each +FTS3 table C<I<t>> is stored internally within three regular tables +C<I<t>_content>, C<I<t>_segments> and C<I<t>_segdir>. The last two +tables contain the fulltext index. The first table C<I<t>_content> +stores the complete documents being indexed ... but if copies of the +same documents are already stored somewhere else, or can be computed +from external resources (for example as HTML or MsWord files in the +filesystem), then this is quite a waste of space. SQLite itself only +needs the C<I<t>_content> table for implementing the C<offsets()> and +C<snippet()> functions, which are not always usable anyway (in particular +when using utf8 characters greater than 255). + +So an alternative strategy is to use SQLite only for the fulltext +index and metadata, and to keep the full documents outside of SQLite : +to do so, after each insert or update in the FTS3 table, do an update +in the C<I<t>_content> table, setting the content column(s) to +NULL. Of course your application will need an algorithm for finding +the external resource corresponding to any I<docid> stored within +SQLite. Furthermore, SQLite C<offsets()> and C<snippet()> functions +cannot be used, so if such functionality is needed, it has to be +directly programmed within the Perl application. +In short, this strategy is really a hack, because FTS3 was not originally +programmed with that behaviour in mind; however it is workable +and has a strong impact on the size of the database file. + +=head1 SUPPORT + +Bugs should be reported via the CPAN bug tracker at + +L<http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite> + +=head1 TO DO + +* Add more and varied cookbook recipes, until we have enough to +turn them into a separate CPAN distribution. + +* Create a series of tests scripts that validate the cookbook recipies. + +=head1 AUTHOR + +Adam Kennedy E<lt>adamk@cpan.orgE<gt> + +Laurent Dami E<lt>dami@cpan.orgE<gt> + +=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. |