[ NAME ]	[ SIZE ]	[ PERM ]	[ DATE ]	[ ACT ]
+FILE +DIR
File	dir	drwxr-xr-x	2025-06-26 06:14	R D
Gofer	dir	drwxr-xr-x	2025-06-26 06:15	R D
SQLite	dir	drwxr-xr-x	2022-08-08 06:03	R D
DBM.pm	50.874 KB	-rw-r--r--	2013-05-15 09:20	R E G D
ExampleP.pm	11.88 KB	-rw-r--r--	2013-04-04 22:17	R E G D
File.pm	36.438 KB	-rw-r--r--	2013-04-04 22:17	R E G D
Gofer.pm	47.926 KB	-rw-r--r--	2014-06-10 01:06	R E G D
NullP.pm	4.237 KB	-rw-r--r--	2013-04-04 22:17	R E G D
Proxy.pm	28.593 KB	-rw-r--r--	2014-06-10 01:06	R E G D
SQLite.pm	81.932 KB	-rw-r--r--	2013-06-09 15:04	R E G D
Sponge.pm	7.794 KB	-rw-r--r--	2013-04-04 22:17	R E G D
REQUEST EXIT

[ NAME ]

[ SIZE ]

[ PERM ]

[ DATE ]

[ ACT ]

+FILE +DIR

File

dir

drwxr-xr-x

2025-06-26 06:14

R D

Gofer

dir

drwxr-xr-x

2025-06-26 06:15

R D

SQLite

dir

drwxr-xr-x

2022-08-08 06:03

R D

DBM.pm

50.874 KB

-rw-r--r--

2013-05-15 09:20

R E G D

ExampleP.pm

11.88 KB

-rw-r--r--

2013-04-04 22:17

R E G D

File.pm

36.438 KB

-rw-r--r--

2013-04-04 22:17

R E G D

Gofer.pm

47.926 KB

-rw-r--r--

2014-06-10 01:06

R E G D

NullP.pm

4.237 KB

-rw-r--r--

2013-04-04 22:17

R E G D

Proxy.pm

28.593 KB

-rw-r--r--

2014-06-10 01:06

R E G D

SQLite.pm

81.932 KB

-rw-r--r--

2013-06-09 15:04

R E G D

Sponge.pm

7.794 KB

-rw-r--r--

2013-04-04 22:17

R E G D

REQUEST EXIT

, 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_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 before calling 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 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 $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. =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 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 attribute is set B 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 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 The FTS extension module within SQLite allows users to create special tables with a built-in full-text index (hereafter "FTS 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 FTS The first full-text search modules for SQLite were called C and C and are now obsolete. The latest recommended module is C; however the former module C is still supporter. Detailed documentation for both C and C can be found at L, including explanations about the differences between these two versions. 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(content) 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. B : starting from version 1.31, C uses the new, recommended "Enhanced Query Syntax" for binary set operators (AND, OR, NOT, possibly nested with parenthesis). Previous versions of C used the "Standard Query Syntax" (see L). 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 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 operator has changed. Conversion from old to new syntax can be automated through L, published in a separate distribution. =head2 Tokenizers 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. =head3 SQLite builtin tokenizers SQLite comes with three builtin tokenizers : =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 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 fts4(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 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') 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 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. =head2 Incomplete handling of utf8 characters The current FTS 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 and C. =head2 Database space for FTS 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. =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). 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, 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 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), 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 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. =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