1DBD::SQLite(3) User Contributed Perl Documentation DBD::SQLite(3)
2
6 DBD::SQLite - Self-contained RDBMS in a DBI Driver
7
9 use DBI;
10 my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");
11
13 SQLite is a public domain file-based relational database engine that
14 you can find at <http://www.sqlite.org/>.
15 DBD::SQLite is a Perl DBI driver for SQLite, that includes the entire
17 thing in the distribution. So in order to get a fast transaction
18 capable RDBMS working for your perl project you simply have to install
19 this module, and nothing else.
20 SQLite supports the following features:
22 Implements a large subset of SQL92
24 See <http://www.sqlite.org/lang.html> for details.
25 A complete DB in a single disk file
27 Everything for your database is stored in a single disk file,
28 making it easier to move things around than with DBD::CSV.
29 Atomic commit and rollback
31 Yes, DBD::SQLite is small and light, but it supports full
32 transactions!
33 Extensible
35 User-defined aggregate or regular functions can be registered with
36 the SQL parser.
37 There's lots more to it, so please refer to the docs on the SQLite web
39 page, listed above, for SQL details. Also refer to DBI for details on
40 how to use DBI itself. The API works like every DBI module does.
41 However, currently many statement attributes are not implemented or are
42 limited by the typeless nature of the SQLite database.
43
45 Database Name Is A File Name
46 SQLite creates a file per a database. You should pass the "path" of the
47 database file (with or without a parent directory) in the DBI
48 connection string (as a database "name"):
49 my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");
51 The file is opened in read/write mode, and will be created if it does
53 not exist yet.
54 Although the database is stored in a single file, the directory
56 containing the database file must be writable by SQLite because the
57 library will create several temporary files there.
58 If the filename $dbfile is ":memory:", then a private, temporary in-
60 memory database is created for the connection. This in-memory database
61 will vanish when the database connection is closed. It is handy for
62 your library tests.
63 Note that future versions of SQLite might make use of additional
65 special filenames that begin with the ":" character. It is recommended
66 that when a database filename actually does begin with a ":" character
67 you should prefix the filename with a pathname such as "./" to avoid
68 ambiguity.
69 If the filename $dbfile is an empty string, then a private, temporary
71 on-disk database will be created. This private database will be
72 automatically deleted as soon as the database connection is closed.
73 DBD::SQLite And File::Temp
75 When you use File::Temp to create a temporary file/directory for SQLite
76 databases, you need to remember:
77 tempfile may be locked exclusively
79 You may want to use "tempfile()" to create a temporary database
80 filename for DBD::SQLite, but as noted in File::Temp's POD, this
81 file may have an exclusive lock under some operating systems
82 (notably Mac OSX), and result in a "database is locked" error. To
83 avoid this, set EXLOCK option to false when you call tempfile().
84 ($fh, $filename) = tempfile($template, EXLOCK => 0);
86 CLEANUP may not work unless a database is disconnected
88 When you set CLEANUP option to true when you create a temporary
89 directory with "tempdir()" or "newdir()", you may have to
90 disconnect databases explicitly before the temporary directory is
91 gone (notably under MS Windows).
92 (The above is quoted from the pod of File::Temp.)
94 If you don't need to keep or share a temporary database, use ":memory:"
96 database instead. It's much handier and cleaner for ordinary testing.
97 Accessing A Database With Other Tools
99 To access the database from the command line, try using "dbish" which
100 comes with the DBI::Shell module. Just type:
101 dbish dbi:SQLite:foo.db
103 On the command line to access the file foo.db.
105 Alternatively you can install SQLite from the link above without
107 conflicting with DBD::SQLite and use the supplied "sqlite3" command
108 line tool.
109 Blobs
111 As of version 1.11, blobs should "just work" in SQLite as text columns.
112 However this will cause the data to be treated as a string, so SQL
113 statements such as length(x) will return the length of the column as a
114 NUL terminated string, rather than the size of the blob in bytes. In
115 order to store natively as a BLOB use the following code:
116 use DBI qw(:sql_types);
118 my $dbh = DBI->connect("dbi:SQLite:dbfile","","");
119 my $blob = `cat foo.jpg`;
121 my $sth = $dbh->prepare("INSERT INTO mytable VALUES (1, ?)");
122 $sth->bind_param(1, $blob, SQL_BLOB);
123 $sth->execute();
124 And then retrieval just works:
126 $sth = $dbh->prepare("SELECT * FROM mytable WHERE id = 1");
128 $sth->execute();
129 my $row = $sth->fetch;
130 my $blobo = $row->[1];
131 # now $blobo == $blob
133 Functions And Bind Parameters
135 As of this writing, a SQL that compares a return value of a function
136 with a numeric bind value like this doesn't work as you might expect.
137 my $sth = $dbh->prepare(q{
139 SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
140 });
141 $sth->execute(5);
142 This is because DBD::SQLite assumes that all the bind values are text
144 (and should be quoted) by default. Thus the above statement becomes
145 like this while executing:
146 SELECT bar FROM foo GROUP BY bar HAVING count(*) > "5";
148 There are three workarounds for this.
150 Use bind_param() explicitly
152 As shown above in the "BLOB" section, you can always use
153 "bind_param()" to tell the type of a bind value.
154 use DBI qw(:sql_types); # Don't forget this
156 my $sth = $dbh->prepare(q{
158 SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
159 });
160 $sth->bind_param(1, 5, SQL_INTEGER);
161 $sth->execute();
162 Add zero to make it a number
164 This is somewhat weird, but works anyway.
165 my $sth = $dbh->prepare(q{
167 SELECT bar FROM foo GROUP BY bar HAVING count(*) > (? + 0);
168 });
169 $sth->execute(5);
170 Set "sqlite_see_if_its_a_number" database handle attribute
172 As of version 1.32_02, you can use "sqlite_see_if_its_a_number" to
173 let DBD::SQLite to see if the bind values are numbers or not.
174 $dbh->{sqlite_see_if_its_a_number} = 1;
176 my $sth = $dbh->prepare(q{
177 SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
178 });
179 $sth->execute(5);
180 You can set it to true when you connect to a database.
182 my $dbh = DBI->connect('dbi:SQLite:foo', undef, undef, {
184 AutoCommit => 1,
185 RaiseError => 1,
186 sqlite_see_if_its_a_number => 1,
187 });
188 This is the most straightforward solution, but as noted above,
190 existing data in your databases created by DBD::SQLite have not
191 always been stored as numbers, so this *might* cause other obscure
192 problems. Use this sparingly when you handle existing databases.
193 If you handle databases created by other tools like native
194 "sqlite3" command line tool, this attribute would help you.
195 Placeholders
197 SQLite supports several placeholder expressions, including "?" and
198 ":AAAA". Consult the DBI and sqlite documentation for details.
199 <http://www.sqlite.org/lang_expr.html#varparam>
201 Note that a question mark actually means a next unused (numbered)
203 placeholder. You're advised not to use it with other (numbered or
204 named) placeholders to avoid confusion.
205 my $sth = $dbh->prepare(
207 'update TABLE set a=?1 where b=?2 and a IS NOT ?1'
208 );
209 $sth->execute(1, 2);
210 Foreign Keys
212 BE PREPARED! WOLVES APPROACH!!
213 SQLite has started supporting foreign key constraints since 3.6.19
215 (released on Oct 14, 2009; bundled in DBD::SQLite 1.26_05). To be
216 exact, SQLite has long been able to parse a schema with foreign keys,
217 but the constraints has not been enforced. Now you can issue a pragma
218 actually to enable this feature and enforce the constraints.
219 To do this, issue the following pragma (see below), preferably as soon
221 as you connect to a database and you're not in a transaction:
222 $dbh->do("PRAGMA foreign_keys = ON");
224 And you can explicitly disable the feature whenever you like by turning
226 the pragma off:
227 $dbh->do("PRAGMA foreign_keys = OFF");
229 As of this writing, this feature is disabled by default by the sqlite
231 team, and by us, to secure backward compatibility, as this feature may
232 break your applications, and actually broke some for us. If you have
233 used a schema with foreign key constraints but haven't cared them much
234 and supposed they're always ignored for SQLite, be prepared, and please
235 do extensive testing to ensure that your applications will continue to
236 work when the foreign keys support is enabled by default. It is very
237 likely that the sqlite team will turn it default-on in the future, and
238 we plan to do it NO LATER THAN they do so.
239 See <http://www.sqlite.org/foreignkeys.html> for details.
241 Pragma
243 SQLite has a set of "Pragma"s to modify its operation or to query for
244 its internal data. These are specific to SQLite and are not likely to
245 work with other DBD libraries, but you may find some of these are quite
246 useful, including:
247 journal_mode
249 You can use this pragma to change the journal mode for SQLite
250 databases, maybe for better performance, or for compatibility.
251 Its default mode is "DELETE", which means SQLite uses a rollback
253 journal to implement transactions, and the journal is deleted at
254 the conclusion of each transaction. If you use "TRUNCATE" instead
255 of "DELETE", the journal will be truncated, which is usually much
256 faster.
257 A "WAL" (write-ahead log) mode is introduced as of SQLite 3.7.0.
259 This mode is persistent, and it stays in effect even after closing
260 and reopening the database. In other words, once the "WAL" mode is
261 set in an application or in a test script, the database becomes
262 inaccessible by older clients. This tends to be an issue when you
263 use a system "sqlite3" executable under a conservative operating
264 system.
265 To fix this, You need to issue "PRAGMA journal_mode = DELETE" (or
267 "TRUNCATE") beforehand, or install a newer version of "sqlite3".
268 legacy_file_format
270 If you happen to need to create a SQLite database that will also be
271 accessed by a very old SQLite client (prior to 3.3.0 released in
272 Jan. 2006), you need to set this pragma to ON before you create a
273 database.
274 reverse_unordered_selects
276 You can set this pragma to ON to reverse the order of results of
277 SELECT statements without an ORDER BY clause so that you can see if
278 applications are making invalid assumptions about the result order.
279 Note that SQLite 3.7.15 (bundled with DBD::SQLite 1.38_02) enhanced
281 its query optimizer and the order of results of a SELECT statement
282 without an ORDER BY clause may be different from the one of the
283 previous versions.
284 synchronous
286 You can set set this pragma to OFF to make some of the operations
287 in SQLite faster with a possible risk of database corruption in the
288 worst case. See also "Performance" section below.
289 See <http://www.sqlite.org/pragma.html> for more details.
291 Transactions
293 DBI/DBD::SQLite's transactions may be a bit confusing. They behave
294 differently according to the status of the "AutoCommit" flag:
295 When the AutoCommit flag is on
297 You're supposed to always use the auto-commit mode, except you
298 explicitly begin a transaction, and when the transaction ended,
299 you're supposed to go back to the auto-commit mode. To begin a
300 transaction, call "begin_work" method, or issue a "BEGIN"
301 statement. To end it, call "commit/rollback" methods, or issue the
302 corresponding statements.
303 $dbh->{AutoCommit} = 1;
305 $dbh->begin_work; # or $dbh->do('BEGIN TRANSACTION');
307 # $dbh->{AutoCommit} is turned off temporarily during a transaction;
309 $dbh->commit; # or $dbh->do('COMMIT');
311 # $dbh->{AutoCommit} is turned on again;
313 When the AutoCommit flag is off
315 You're supposed to always use the transactional mode, until you
316 explicitly turn on the AutoCommit flag. You can explicitly issue a
317 "BEGIN" statement (only when an actual transaction has not begun
318 yet) but you're not allowed to call "begin_work" method (if you
319 don't issue a "BEGIN", it will be issued internally). You can
320 commit or roll it back freely. Another transaction will
321 automatically begins if you execute another statement.
322 $dbh->{AutoCommit} = 0;
324 # $dbh->do('BEGIN TRANSACTION') is not necessary, but possible
326 ...
328 $dbh->commit; # or $dbh->do('COMMIT');
330 # $dbh->{AutoCommit} stays intact;
332 $dbh->{AutoCommit} = 1; # ends the transactional mode
334 This "AutoCommit" mode is independent from the autocommit mode of the
336 internal SQLite library, which always begins by a "BEGIN" statement,
337 and ends by a "COMMIT" or a <ROLLBACK>.
338 Transaction and Database Locking
340 The default transaction behavior of SQLite is "deferred", that means,
341 locks are not acquired until the first read or write operation, and
342 thus it is possible that another thread or process could create a
343 separate transaction and write to the database after the "BEGIN" on the
344 current thread has executed, and eventually cause a "deadlock". To
345 avoid this, DBD::SQLite internally issues a "BEGIN IMMEDIATE" when you
346 begin a transaction by "begin_work" or under the "AutoCommit" mode
347 (since 1.38_01).
348 If you really need to turn off this feature for some reasons, set
350 "sqlite_use_immediate_transaction" database handle attribute to false,
351 and the default "deferred" transaction will be used.
352 my $dbh = DBI->connect("dbi:SQLite::memory:", "", "", {
354 sqlite_use_immediate_transaction => 0,
355 });
356 See <http://sqlite.org/lockingv3.html> for locking details.
358 "$sth->finish" and Transaction Rollback
360 As the DBI doc says, you almost certainly do not need to call "finish"
361 in DBI method if you fetch all rows (probably in a loop). However,
362 there are several exceptions to this rule, and rolling-back of an
363 unfinished "SELECT" statement is one of such exceptional cases.
364 SQLite prohibits "ROLLBACK" of unfinished "SELECT" statements in a
366 transaction (See <http://sqlite.org/lang_transaction.html> for
367 details). So you need to call "finish" before you issue a rollback.
368 $sth = $dbh->prepare("SELECT * FROM t");
370 $dbh->begin_work;
371 eval {
372 $sth->execute;
373 $row = $sth->fetch;
374 ...
375 die "For some reason";
376 ...
377 };
378 if($@) {
379 $sth->finish; # You need this for SQLite
380 $dbh->rollback;
381 } else {
382 $dbh->commit;
383 }
384 Processing Multiple Statements At A Time
386 DBI's statement handle is not supposed to process multiple statements
387 at a time. So if you pass a string that contains multiple statements (a
388 "dump") to a statement handle (via "prepare" or "do"), DBD::SQLite only
389 processes the first statement, and discards the rest.
390 Since 1.30_01, you can retrieve those ignored (unprepared) statements
392 via "$sth->{sqlite_unprepared_statements}". It usually contains nothing
393 but white spaces, but if you really care, you can check this attribute
394 to see if there's anything left undone. Also, if you set a
395 "sqlite_allow_multiple_statements" attribute of a database handle to
396 true when you connect to a database, "do" method automatically checks
397 the "sqlite_unprepared_statements" attribute, and if it finds anything
398 undone (even if what's left is just a single white space), it repeats
399 the process again, to the end.
400 Performance
402 SQLite is fast, very fast. Matt processed his 72MB log file with it,
403 inserting the data (400,000+ rows) by using transactions and only
404 committing every 1000 rows (otherwise the insertion is quite slow), and
405 then performing queries on the data.
406 Queries like count(*) and avg(bytes) took fractions of a second to
408 return, but what surprised him most of all was:
409 SELECT url, count(*) as count
411 FROM access_log
412 GROUP BY url
413 ORDER BY count desc
414 LIMIT 20
415 To discover the top 20 hit URLs on the site (<http://axkit.org>), and
417 it returned within 2 seconds. He was seriously considering switching
418 his log analysis code to use this little speed demon!
419 Oh yeah, and that was with no indexes on the table, on a 400MHz PIII.
421 For best performance be sure to tune your hdparm settings if you are
423 using linux. Also you might want to set:
424 PRAGMA synchronous = OFF
426 Which will prevent sqlite from doing fsync's when writing (which slows
428 down non-transactional writes significantly) at the expense of some
429 peace of mind. Also try playing with the cache_size pragma.
430 The memory usage of SQLite can also be tuned using the cache_size
432 pragma.
433 $dbh->do("PRAGMA cache_size = 800000");
435 The above will allocate 800M for DB cache; the default is 2M. Your
437 sweet spot probably lies somewhere in between.
438
440 Database Handle Attributes
441 sqlite_version
442 Returns the version of the SQLite library which DBD::SQLite is
443 using, e.g., "2.8.0". Can only be read.
444 sqlite_unicode
446 If set to a true value, DBD::SQLite will turn the UTF-8 flag on for
447 all text strings coming out of the database (this feature is
448 currently disabled for perl < 5.8.5). For more details on the UTF-8
449 flag see perlunicode. The default is for the UTF-8 flag to be
450 turned off.
451 Also note that due to some bizarreness in SQLite's type system (see
453 <http://www.sqlite.org/datatype3.html>), if you want to retain
454 blob-style behavior for some columns under "$dbh->{sqlite_unicode}
455 = 1" (say, to store images in the database), you have to state so
456 explicitly using the 3-argument form of "bind_param" in DBI when
457 doing updates:
458 use DBI qw(:sql_types);
460 $dbh->{sqlite_unicode} = 1;
461 my $sth = $dbh->prepare("INSERT INTO mytable (blobcolumn) VALUES (?)");
462 # Binary_data will be stored as is.
464 $sth->bind_param(1, $binary_data, SQL_BLOB);
465 Defining the column type as "BLOB" in the DDL is not sufficient.
467 This attribute was originally named as "unicode", and renamed to
469 "sqlite_unicode" for integrity since version 1.26_06. Old "unicode"
470 attribute is still accessible but will be deprecated in the near
471 future.
472 sqlite_allow_multiple_statements
474 If you set this to true, "do" method will process multiple
475 statements at one go. This may be handy, but with performance
476 penalty. See above for details.
477 sqlite_use_immediate_transaction
479 If you set this to true, DBD::SQLite tries to issue a "begin
480 immediate transaction" (instead of "begin transaction") when
481 necessary. See above for details.
482 As of version 1.38_01, this attribute is set to true by default.
484 If you really need to use "deferred" transactions for some reasons,
485 set this to false explicitly.
486 sqlite_see_if_its_a_number
488 If you set this to true, DBD::SQLite tries to see if the bind
489 values are number or not, and does not quote if they are numbers.
490 See above for details.
491 Statement Handle Attributes
493 sqlite_unprepared_statements
494 Returns an unprepared part of the statement you pass to "prepare".
495 Typically this contains nothing but white spaces after a semicolon.
496 See above for details.
497
499 See also to the DBI documentation for the details of other common
500 methods.
501 table_info
503 $sth = $dbh->table_info(undef, $schema, $table, $type, \%attr);
504 Returns all tables and schemas (databases) as specified in "table_info"
506 in DBI. The schema and table arguments will do a "LIKE" search. You
507 can specify an ESCAPE character by including an 'Escape' attribute in
508 \%attr. The $type argument accepts a comma separated list of the
509 following types 'TABLE', 'VIEW', 'LOCAL TEMPORARY' and 'SYSTEM TABLE'
510 (by default all are returned). Note that a statement handle is
511 returned, and not a direct list of tables.
512 The following fields are returned:
514 TABLE_CAT: Always NULL, as SQLite does not have the concept of
516 catalogs.
517 TABLE_SCHEM: The name of the schema (database) that the table or view
519 is in. The default schema is 'main', temporary tables are in 'temp' and
520 other databases will be in the name given when the database was
521 attached.
522 TABLE_NAME: The name of the table or view.
524 TABLE_TYPE: The type of object returned. Will be one of 'TABLE',
526 'VIEW', 'LOCAL TEMPORARY' or 'SYSTEM TABLE'.
527 primary_key, primary_key_info
529 @names = $dbh->primary_key(undef, $schema, $table);
530 $sth = $dbh->primary_key_info(undef, $schema, $table, \%attr);
531 You can retrieve primary key names or more detailed information. As
533 noted above, SQLite does not have the concept of catalogs, so the first
534 argument of the methods is usually "undef", and you'll usually set
535 "undef" for the second one (unless you want to know the primary keys of
536 temporary tables).
537 foreign_key_info
539 $sth = $dbh->foreign_key_info(undef, $pk_schema, $pk_table,
540 undef, $fk_schema, $fk_table);
541 Returns information about foreign key constraints, as specified in
543 "foreign_key_info" in DBI, but with some limitations :
544 · information in rows returned by the $sth is incomplete with respect
546 to the "foreign_key_info" in DBI specification. All requested
547 fields are present, but the content is "undef" for some of them.
548 The following nonempty fields are returned :
550 PKTABLE_NAME: The primary (unique) key table identifier.
552 PKCOLUMN_NAME: The primary (unique) key column identifier.
554 FKTABLE_NAME: The foreign key table identifier.
556 FKCOLUMN_NAME: The foreign key column identifier.
558 KEY_SEQ: The column sequence number (starting with 1), when several
560 columns belong to a same constraint.
561 UPDATE_RULE: The referential action for the UPDATE rule. The following
563 codes are defined:
564 CASCADE 0
566 RESTRICT 1
567 SET NULL 2
568 NO ACTION 3
569 SET DEFAULT 4
570 Default is 3 ('NO ACTION').
572 DELETE_RULE: The referential action for the DELETE rule. The codes are
574 the same as for UPDATE_RULE.
575 Unfortunately, the DEFERRABILITY field is always "undef"; as a matter
577 of fact, deferrability clauses are supported by SQLite, but they can't
578 be reported because the "PRAGMA foreign_key_list" tells nothing about
579 them.
580 UNIQUE_OR_PRIMARY: Whether the column is primary or unique.
582 Note: foreign key support in SQLite must be explicitly turned on
584 through a "PRAGMA" command; see "Foreign keys" earlier in this manual.
585 ping
587 my $bool = $dbh->ping;
588 returns true if the database file exists (or the database is in-
590 memory), and the database connection is active.
591
593 The following methods can be called via the func() method with a little
594 tweak, but the use of func() method is now discouraged by the DBI
595 author for various reasons (see DBI's document
596 <http://search.cpan.org/dist/DBI/lib/DBI/DBD.pm#Using_install_method()_to_expose_driver-private_methods>
597 for details). So, if you're using DBI >= 1.608, use these "sqlite_"
598 methods. If you need to use an older DBI, you can call these like this:
599 $dbh->func( ..., "(method name without sqlite_ prefix)" );
601 Exception: "sqlite_trace" should always be called as is, even with
603 "func()" method (to avoid conflict with DBI's trace() method).
604 $dbh->func( ..., "sqlite_trace");
606 $dbh->sqlite_last_insert_rowid()
608 This method returns the last inserted rowid. If you specify an INTEGER
609 PRIMARY KEY as the first column in your table, that is the column that
610 is returned. Otherwise, it is the hidden ROWID column. See the sqlite
611 docs for details.
612 Generally you should not be using this method. Use the DBI
614 last_insert_id method instead. The usage of this is:
615 $h->last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ])
617 Running "$h->last_insert_id("","","","")" is the equivalent of running
619 "$dbh->sqlite_last_insert_rowid()" directly.
620 $dbh->sqlite_db_filename()
622 Retrieve the current (main) database filename. If the database is in-
623 memory or temporary, this returns "undef".
624 $dbh->sqlite_busy_timeout()
626 Retrieve the current busy timeout.
627 $dbh->sqlite_busy_timeout( $ms )
629 Set the current busy timeout. The timeout is in milliseconds.
630 $dbh->sqlite_create_function( $name, $argc, $code_ref )
632 This method will register a new function which will be usable in an SQL
633 query. The method's parameters are:
634 $name
636 The name of the function. This is the name of the function as it
637 will be used from SQL.
638 $argc
640 The number of arguments taken by the function. If this number is
641 -1, the function can take any number of arguments.
642 $code_ref
644 This should be a reference to the function's implementation.
645 For example, here is how to define a now() function which returns the
647 current number of seconds since the epoch:
648 $dbh->sqlite_create_function( 'now', 0, sub { return time } );
650 After this, it could be use from SQL as:
652 INSERT INTO mytable ( now() );
654 REGEXP function
656 SQLite includes syntactic support for an infix operator 'REGEXP', but
658 without any implementation. The "DBD::SQLite" driver automatically
659 registers an implementation that performs standard perl regular
660 expression matching, using current locale. So for example you can
661 search for words starting with an 'A' with a query like
662 SELECT * from table WHERE column REGEXP '\bA\w+'
664 If you want case-insensitive searching, use perl regex flags, like this
666 :
667 SELECT * from table WHERE column REGEXP '(?i:\bA\w+)'
669 The default REGEXP implementation can be overridden through the
671 "create_function" API described above.
672 Note that regexp matching will not use SQLite indices, but will iterate
674 over all rows, so it could be quite costly in terms of performance.
675 $dbh->sqlite_create_collation( $name, $code_ref )
677 This method manually registers a new function which will be usable in
678 an SQL query as a COLLATE option for sorting. Such functions can also
679 be registered automatically on demand: see section "COLLATION
680 FUNCTIONS" below.
681 The method's parameters are:
683 $name
685 The name of the function exposed to SQL.
686 $code_ref
688 Reference to the function's implementation. The driver will check
689 that this is a proper sorting function.
690 $dbh->sqlite_collation_needed( $code_ref )
692 This method manually registers a callback function that will be invoked
693 whenever an undefined collation sequence is required from an SQL
694 statement. The callback is invoked as
695 $code_ref->($dbh, $collation_name)
697 and should register the desired collation using
699 "sqlite_create_collation".
700 An initial callback is already registered by "DBD::SQLite", so for most
702 common cases it will be simpler to just add your collation sequences in
703 the %DBD::SQLite::COLLATION hash (see section "COLLATION FUNCTIONS"
704 below).
705 $dbh->sqlite_create_aggregate( $name, $argc, $pkg )
707 This method will register a new aggregate function which can then be
708 used from SQL. The method's parameters are:
709 $name
711 The name of the aggregate function, this is the name under which
712 the function will be available from SQL.
713 $argc
715 This is an integer which tells the SQL parser how many arguments
716 the function takes. If that number is -1, the function can take any
717 number of arguments.
718 $pkg
720 This is the package which implements the aggregator interface.
721 The aggregator interface consists of defining three methods:
723 new()
725 This method will be called once to create an object which should be
726 used to aggregate the rows in a particular group. The step() and
727 finalize() methods will be called upon the reference return by the
728 method.
729 step(@_)
731 This method will be called once for each row in the aggregate.
732 finalize()
734 This method will be called once all rows in the aggregate were
735 processed and it should return the aggregate function's result.
736 When there is no rows in the aggregate, finalize() will be called
737 right after new().
738 Here is a simple aggregate function which returns the variance (example
740 adapted from pysqlite):
741 package variance;
743 sub new { bless [], shift; }
745 sub step {
747 my ( $self, $value ) = @_;
748 push @$self, $value;
750 }
751 sub finalize {
753 my $self = $_[0];
754 my $n = @$self;
756 # Variance is NULL unless there is more than one row
758 return undef unless $n || $n == 1;
759 my $mu = 0;
761 foreach my $v ( @$self ) {
762 $mu += $v;
763 }
764 $mu /= $n;
765 my $sigma = 0;
767 foreach my $v ( @$self ) {
768 $sigma += ($v - $mu)**2;
769 }
770 $sigma = $sigma / ($n - 1);
771 return $sigma;
773 }
774 $dbh->sqlite_create_aggregate( "variance", 1, 'variance' );
776 The aggregate function can then be used as:
778 SELECT group_name, variance(score)
780 FROM results
781 GROUP BY group_name;
782 For more examples, see the DBD::SQLite::Cookbook.
784 $dbh->sqlite_progress_handler( $n_opcodes, $code_ref )
786 This method registers a handler to be invoked periodically during long
787 running calls to SQLite.
788 An example use for this interface is to keep a GUI updated during a
790 large query. The parameters are:
791 $n_opcodes
793 The progress handler is invoked once for every $n_opcodes virtual
794 machine opcodes in SQLite.
795 $code_ref
797 Reference to the handler subroutine. If the progress handler
798 returns non-zero, the SQLite operation is interrupted. This feature
799 can be used to implement a "Cancel" button on a GUI dialog box.
800 Set this argument to "undef" if you want to unregister a previous
802 progress handler.
803 $dbh->sqlite_commit_hook( $code_ref )
805 This method registers a callback function to be invoked whenever a
806 transaction is committed. Any callback set by a previous call to
807 "sqlite_commit_hook" is overridden. A reference to the previous
808 callback (if any) is returned. Registering an "undef" disables the
809 callback.
810 When the commit hook callback returns zero, the commit operation is
812 allowed to continue normally. If the callback returns non-zero, then
813 the commit is converted into a rollback (in that case, any attempt to
814 explicitly call "$dbh->rollback()" afterwards would yield an error).
815 $dbh->sqlite_rollback_hook( $code_ref )
817 This method registers a callback function to be invoked whenever a
818 transaction is rolled back. Any callback set by a previous call to
819 "sqlite_rollback_hook" is overridden. A reference to the previous
820 callback (if any) is returned. Registering an "undef" disables the
821 callback.
822 $dbh->sqlite_update_hook( $code_ref )
824 This method registers a callback function to be invoked whenever a row
825 is updated, inserted or deleted. Any callback set by a previous call to
826 "sqlite_update_hook" is overridden. A reference to the previous
827 callback (if any) is returned. Registering an "undef" disables the
828 callback.
829 The callback will be called as
831 $code_ref->($action_code, $database, $table, $rowid)
833 where
835 $action_code
837 is an integer equal to either "DBD::SQLite::INSERT",
838 "DBD::SQLite::DELETE" or "DBD::SQLite::UPDATE" (see "Action
839 Codes");
840 $database
842 is the name of the database containing the affected row;
843 $table
845 is the name of the table containing the affected row;
846 $rowid
848 is the unique 64-bit signed integer key of the affected row within
849 that table.
850 $dbh->sqlite_set_authorizer( $code_ref )
852 This method registers an authorizer callback to be invoked whenever SQL
853 statements are being compiled by the "prepare" in DBI method. The
854 authorizer callback should return "DBD::SQLite::OK" to allow the
855 action, "DBD::SQLite::IGNORE" to disallow the specific action but allow
856 the SQL statement to continue to be compiled, or "DBD::SQLite::DENY" to
857 cause the entire SQL statement to be rejected with an error. If the
858 authorizer callback returns any other value, then "prepare" call that
859 triggered the authorizer will fail with an error message.
860 An authorizer is used when preparing SQL statements from an untrusted
862 source, to ensure that the SQL statements do not try to access data
863 they are not allowed to see, or that they do not try to execute
864 malicious statements that damage the database. For example, an
865 application may allow a user to enter arbitrary SQL queries for
866 evaluation by a database. But the application does not want the user to
867 be able to make arbitrary changes to the database. An authorizer could
868 then be put in place while the user-entered SQL is being prepared that
869 disallows everything except SELECT statements.
870 The callback will be called as
872 $code_ref->($action_code, $string1, $string2, $database, $trigger_or_view)
874 where
876 $action_code
878 is an integer that specifies what action is being authorized (see
879 "Action Codes").
880 $string1, $string2
882 are strings that depend on the action code (see "Action Codes").
883 $database
885 is the name of the database ("main", "temp", etc.) if applicable.
886 $trigger_or_view
888 is the name of the inner-most trigger or view that is responsible
889 for the access attempt, or "undef" if this access attempt is
890 directly from top-level SQL code.
891 $dbh->sqlite_backup_from_file( $filename )
893 This method accesses the SQLite Online Backup API, and will take a
894 backup of the named database file, copying it to, and overwriting, your
895 current database connection. This can be particularly handy if your
896 current connection is to the special :memory: database, and you wish to
897 populate it from an existing DB.
898 $dbh->sqlite_backup_to_file( $filename )
900 This method accesses the SQLite Online Backup API, and will take a
901 backup of the currently connected database, and write it out to the
902 named file.
903 $dbh->sqlite_enable_load_extension( $bool )
905 Calling this method with a true value enables loading (external)
906 sqlite3 extensions. After the call, you can load extensions like this:
907 $dbh->sqlite_enable_load_extension(1);
909 $sth = $dbh->prepare("select load_extension('libsqlitefunctions.so')")
910 or die "Cannot prepare: " . $dbh->errstr();
911 $dbh->sqlite_load_extension( $file, $proc )
913 Loading an extension by a select statement (with the "load_extension"
914 sqlite3 function like above) has some limitations. If you need to, say,
915 create other functions from an extension, use this method. $file (a
916 path to the extension) is mandatory, and $proc (an entry point name) is
917 optional. You need to call "sqlite_enable_load_extension" before
918 calling "sqlite_load_extension".
919 $dbh->sqlite_trace( $code_ref )
921 This method registers a trace callback to be invoked whenever SQL
922 statements are being run.
923 The callback will be called as
925 $code_ref->($statement)
927 where
929 $statement
931 is a UTF-8 rendering of the SQL statement text as the statement
932 first begins executing.
933 Additional callbacks might occur as each triggered subprogram is
935 entered. The callbacks for triggers contain a UTF-8 SQL comment that
936 identifies the trigger.
937 See also "TRACING" in DBI for better tracing options.
939 $dbh->sqlite_profile( $code_ref )
941 This method registers a profile callback to be invoked whenever a SQL
942 statement finishes.
943 The callback will be called as
945 $code_ref->($statement, $elapsed_time)
947 where
949 $statement
951 is the original statement text (without bind parameters).
952 $elapsed_time
954 is an estimate of wall-clock time of how long that statement took
955 to run (in milliseconds).
956 This method is considered experimental and is subject to change in
958 future versions of SQLite.
959 See also DBI::Profile for better profiling options.
961 $dbh->sqlite_table_column_metadata( $dbname, $tablename, $columnname )
963 is for internal use only.
964 DBD::SQLite::compile_options()
966 Returns an array of compile options (available since sqlite 3.6.23,
967 bundled in DBD::SQLite 1.30_01), or an empty array if the bundled
968 library is old or compiled with SQLITE_OMIT_COMPILEOPTION_DIAGS.
969 DBD::SQLite::sqlite_status()
971 Returns a hash reference that holds a set of status information of
972 SQLite runtime such as memory usage or page cache usage (see
973 <http://www.sqlite.org/c3ref/c_status_malloc_count.html> for details).
974 Each of the entry contains the current value and the highwater value.
975 my $status = DBD::SQLite::sqlite_status();
977 my $cur = $status->{memory_used}{current};
978 my $high = $status->{memory_used}{highwater};
979 You may also pass 0 as an argument to reset the status.
981 $dbh->sqlite_db_status()
983 Returns a hash reference that holds a set of status information of
984 database connection such as cache usage. See
985 <http://www.sqlite.org/c3ref/c_dbstatus_options.html> for details. You
986 may also pass 0 as an argument to reset the status.
987 $sth->sqlite_st_status()
989 Returns a hash reference that holds a set of status information of
990 SQLite statement handle such as full table scan count. See
991 <http://www.sqlite.org/c3ref/c_stmtstatus_counter.html> for details.
992 Statement status only holds the current value.
993 my $status = $sth->sqlite_st_status();
995 my $cur = $status->{fullscan_step};
996 You may also pass 0 as an argument to reset the status.
998
1000 A subset of SQLite C constants are made available to Perl, because they
1001 may be needed when writing hooks or authorizer callbacks. For accessing
1002 such constants, the "DBD::SQLite" module must be explicitly "use"d at
1003 compile time. For example, an authorizer that forbids any DELETE
1004 operation would be written as follows :
1005 use DBD::SQLite;
1007 $dbh->sqlite_set_authorizer(sub {
1008 my $action_code = shift;
1009 return $action_code == DBD::SQLite::DELETE ? DBD::SQLite::DENY
1010 : DBD::SQLite::OK;
1011 });
1012 The list of constants implemented in "DBD::SQLite" is given below; more
1014 information can be found ad at
1015 <http://www.sqlite.org/c3ref/constlist.html>.
1016 Authorizer Return Codes
1018 OK
1019 DENY
1020 IGNORE
1021 Action Codes
1023 The "set_authorizer" method registers a callback function that is
1024 invoked to authorize certain SQL statement actions. The first parameter
1025 to the callback is an integer code that specifies what action is being
1026 authorized. The second and third parameters to the callback are
1027 strings, the meaning of which varies according to the action code.
1028 Below is the list of action codes, together with their associated
1029 strings.
1030 # constant string1 string2
1032 # ======== ======= =======
1033 CREATE_INDEX Index Name Table Name
1034 CREATE_TABLE Table Name undef
1035 CREATE_TEMP_INDEX Index Name Table Name
1036 CREATE_TEMP_TABLE Table Name undef
1037 CREATE_TEMP_TRIGGER Trigger Name Table Name
1038 CREATE_TEMP_VIEW View Name undef
1039 CREATE_TRIGGER Trigger Name Table Name
1040 CREATE_VIEW View Name undef
1041 DELETE Table Name undef
1042 DROP_INDEX Index Name Table Name
1043 DROP_TABLE Table Name undef
1044 DROP_TEMP_INDEX Index Name Table Name
1045 DROP_TEMP_TABLE Table Name undef
1046 DROP_TEMP_TRIGGER Trigger Name Table Name
1047 DROP_TEMP_VIEW View Name undef
1048 DROP_TRIGGER Trigger Name Table Name
1049 DROP_VIEW View Name undef
1050 INSERT Table Name undef
1051 PRAGMA Pragma Name 1st arg or undef
1052 READ Table Name Column Name
1053 SELECT undef undef
1054 TRANSACTION Operation undef
1055 UPDATE Table Name Column Name
1056 ATTACH Filename undef
1057 DETACH Database Name undef
1058 ALTER_TABLE Database Name Table Name
1059 REINDEX Index Name undef
1060 ANALYZE Table Name undef
1061 CREATE_VTABLE Table Name Module Name
1062 DROP_VTABLE Table Name Module Name
1063 FUNCTION undef Function Name
1064 SAVEPOINT Operation Savepoint Name
1065
1067 Definition
1068 SQLite v3 provides the ability for users to supply arbitrary comparison
1069 functions, known as user-defined "collation sequences" or "collating
1070 functions", to be used for comparing two text values.
1071 <http://www.sqlite.org/datatype3.html#collation> explains how
1072 collations are used in various SQL expressions.
1073 Builtin collation sequences
1075 The following collation sequences are builtin within SQLite :
1076 BINARY
1078 Compares string data using memcmp(), regardless of text encoding.
1079 NOCASE
1081 The same as binary, except the 26 upper case characters of ASCII
1082 are folded to their lower case equivalents before the comparison is
1083 performed. Note that only ASCII characters are case folded. SQLite
1084 does not attempt to do full UTF case folding due to the size of the
1085 tables required.
1086 RTRIM
1088 The same as binary, except that trailing space characters are
1089 ignored.
1090 In addition, "DBD::SQLite" automatically installs the following
1092 collation sequences :
1093 perl
1095 corresponds to the Perl "cmp" operator
1096 perllocale
1098 Perl "cmp" operator, in a context where "use locale" is activated.
1099 Usage
1101 You can write for example
1102 CREATE TABLE foo(
1104 txt1 COLLATE perl,
1105 txt2 COLLATE perllocale,
1106 txt3 COLLATE nocase
1107 )
1108 or
1110 SELECT * FROM foo ORDER BY name COLLATE perllocale
1112 Unicode handling
1114 If the attribute "$dbh->{sqlite_unicode}" is set, strings coming from
1115 the database and passed to the collation function will be properly
1116 tagged with the utf8 flag; but this only works if the "sqlite_unicode"
1117 attribute is set before the first call to a perl collation sequence .
1118 The recommended way to activate unicode is to set the parameter at
1119 connection time :
1120 my $dbh = DBI->connect(
1122 "dbi:SQLite:dbname=foo", "", "",
1123 {
1124 RaiseError => 1,
1125 sqlite_unicode => 1,
1126 }
1127 );
1128 Adding user-defined collations
1130 The native SQLite API for adding user-defined collations is exposed
1131 through methods "sqlite_create_collation" and
1132 "sqlite_collation_needed".
1133 To avoid calling these functions every time a $dbh handle is created,
1135 "DBD::SQLite" offers a simpler interface through the
1136 %DBD::SQLite::COLLATION hash : just insert your own collation functions
1137 in that hash, and whenever an unknown collation name is encountered in
1138 SQL, the appropriate collation function will be loaded on demand from
1139 the hash. For example, here is a way to sort text values regardless of
1140 their accented characters :
1141 use DBD::SQLite;
1143 $DBD::SQLite::COLLATION{no_accents} = sub {
1144 my ( $a, $b ) = map lc, @_;
1145 tr[XXXXXXXXXXXXXXXXXXXXXXXXXXXX]
1146 [aaaaaacdeeeeiiiinoooooouuuuy] for $a, $b;
1147 $a cmp $b;
1148 };
1149 my $dbh = DBI->connect("dbi:SQLite:dbname=dbfile");
1150 my $sql = "SELECT ... FROM ... ORDER BY ... COLLATE no_accents");
1151 my $rows = $dbh->selectall_arrayref($sql);
1152 The builtin "perl" or "perllocale" collations are predefined in that
1154 same hash.
1155 The COLLATION hash is a global registry within the current process;
1157 hence there is a risk of undesired side-effects. Therefore, to prevent
1158 action at distance, the hash is implemented as a "write-only" hash,
1159 that will happily accept new entries, but will raise an exception if
1160 any attempt is made to override or delete a existing entry (including
1161 the builtin "perl" and "perllocale").
1162 If you really, really need to change or delete an entry, you can always
1164 grab the tied object underneath %DBD::SQLite::COLLATION --- but don't
1165 do that unless you really know what you are doing. Also observe that
1166 changes in the global hash will not modify existing collations in
1167 existing database handles: it will only affect new requests for
1168 collations. In other words, if you want to change the behaviour of a
1169 collation within an existing $dbh, you need to call the
1170 "create_collation" method directly.
1171
1173 The FTS extension module within SQLite allows users to create special
1174 tables with a built-in full-text index (hereafter "FTS tables"). The
1175 full-text index allows the user to efficiently query the database for
1176 all rows that contain one or more instances of a specified word
1177 (hereafter a "token"), even if the table contains many large documents.
1178 Short introduction to FTS
1180 The first full-text search modules for SQLite were called "FTS1" and
1181 "FTS2" and are now obsolete. The latest recommended module is "FTS4";
1182 however the former module "FTS3" is still supporter. Detailed
1183 documentation for both "FTS4" and "FTS3" can be found at
1184 <http://www.sqlite.org/fts3.html>, including explanations about the
1185 differences between these two versions.
1186 Here is a very short example of using FTS :
1188 $dbh->do(<<"") or die DBI::errstr;
1190 CREATE VIRTUAL TABLE fts_example USING fts4(content)
1191 my $sth = $dbh->prepare("INSERT INTO fts_example(content) VALUES (?))");
1193 $sth->execute($_) foreach @docs_to_insert;
1194 my $results = $dbh->selectall_arrayref(<<"");
1196 SELECT docid, snippet(content) FROM fts_example WHERE content MATCH 'foo'
1197 The key points in this example are :
1199 · The syntax for creating FTS tables is
1201 CREATE VIRTUAL TABLE <table_name> USING fts4(<columns>)
1203 where "<columns>" is a list of column names. Columns may be typed,
1205 but the type information is ignored. If no columns are specified,
1206 the default is a single column named "content". In addition, FTS
1207 tables have an implicit column called "docid" (or also "rowid") for
1208 numbering the stored documents.
1209 · Statements for inserting, updating or deleting records use the same
1211 syntax as for regular SQLite tables.
1212 · Full-text searches are specified with the "MATCH" operator, and an
1214 operand which may be a single word, a word prefix ending with '*',
1215 a list of words, a "phrase query" in double quotes, or a boolean
1216 combination of the above.
1217 · The builtin function "snippet(...)" builds a formatted excerpt of
1219 the document text, where the words pertaining to the query are
1220 highlighted.
1221 There are many more details to building and searching FTS tables, so we
1223 strongly invite you to read the full documentation at
1224 <http://www.sqlite.org/fts3.html>.
1225 Incompatible change : starting from version 1.31, "DBD::SQLite" uses
1227 the new, recommended "Enhanced Query Syntax" for binary set operators
1228 (AND, OR, NOT, possibly nested with parenthesis). Previous versions of
1229 "DBD::SQLite" used the "Standard Query Syntax" (see
1230 <http://www.sqlite.org/fts3.html#section_3_2>). Unfortunately this is
1231 a compilation switch, so it cannot be tuned at runtime; however, since
1232 FTS3 was never advertised in versions prior to 1.31, the change should
1233 be invisible to the vast majority of "DBD::SQLite" users. If, however,
1234 there are any applications that nevertheless were built using the
1235 "Standard Query" syntax, they have to be migrated, because the
1236 precedence of the "OR" operator has changed. Conversion from old to new
1237 syntax can be automated through DBD::SQLite::FTS3Transitional,
1238 published in a separate distribution.
1239 Tokenizers
1241 The behaviour of full-text indexes strongly depends on how documents
1242 are split into tokens; therefore FTS table declarations can explicitly
1243 specify how to perform tokenization:
1244 CREATE ... USING fts4(<columns>, tokenize=<tokenizer>)
1246 where "<tokenizer>" is a sequence of space-separated words that
1248 triggers a specific tokenizer. Tokenizers can be SQLite builtins,
1249 written in C code, or Perl tokenizers. Both are as explained below.
1250 SQLite builtin tokenizers
1252 SQLite comes with three builtin tokenizers :
1254 simple
1256 Under the simple tokenizer, a term is a contiguous sequence of
1257 eligible characters, where eligible characters are all alphanumeric
1258 characters, the "_" character, and all characters with UTF
1259 codepoints greater than or equal to 128. All other characters are
1260 discarded when splitting a document into terms. They serve only to
1261 separate adjacent terms.
1262 All uppercase characters within the ASCII range (UTF codepoints
1264 less than 128), are transformed to their lowercase equivalents as
1265 part of the tokenization process. Thus, full-text queries are case-
1266 insensitive when using the simple tokenizer.
1267 porter
1269 The porter tokenizer uses the same rules to separate the input
1270 document into terms, but as well as folding all terms to lower case
1271 it uses the Porter Stemming algorithm to reduce related English
1272 language words to a common root.
1273 icu If SQLite is compiled with the SQLITE_ENABLE_ICU pre-processor
1275 symbol defined, then there exists a built-in tokenizer named "icu"
1276 implemented using the ICU library, and taking an ICU locale
1277 identifier as argument (such as "tr_TR" for Turkish as used in
1278 Turkey, or "en_AU" for English as used in Australia). For example:
1279 CREATE VIRTUAL TABLE thai_text USING fts4(text, tokenize=icu th_TH)
1281 The ICU tokenizer implementation is very simple. It splits the
1283 input text according to the ICU rules for finding word boundaries
1284 and discards any tokens that consist entirely of white-space. This
1285 may be suitable for some applications in some locales, but not all.
1286 If more complex processing is required, for example to implement
1287 stemming or discard punctuation, use the perl tokenizer as
1288 explained below.
1289 Perl tokenizers
1291 In addition to the builtin SQLite tokenizers, "DBD::SQLite" implements
1293 a perl tokenizer, that can hook to any tokenizing algorithm written in
1294 Perl. This is specified as follows :
1295 CREATE ... USING fts4(<columns>, tokenize=perl '<perl_function>')
1297 where "<perl_function>" is a fully qualified Perl function name (i.e.
1299 prefixed by the name of the package in which that function is
1300 declared). So for example if the function is "my_func" in the main
1301 program, write
1302 CREATE ... USING fts4(<columns>, tokenize=perl 'main::my_func')
1304 That function should return a code reference that takes a string as
1306 single argument, and returns an iterator (another function), which
1307 returns a tuple "($term, $len, $start, $end, $index)" for each term.
1308 Here is a simple example that tokenizes on words according to the
1309 current perl locale
1310 sub locale_tokenizer {
1312 return sub {
1313 my $string = shift;
1314 use locale;
1316 my $regex = qr/\w+/;
1317 my $term_index = 0;
1318 return sub { # closure
1320 $string =~ /$regex/g or return; # either match, or no more token
1321 my ($start, $end) = ($-[0], $+[0]);
1322 my $len = $end-$start;
1323 my $term = substr($string, $start, $len);
1324 return ($term, $len, $start, $end, $term_index++);
1325 }
1326 };
1327 }
1328 There must be three levels of subs, in a kind of "Russian dolls"
1330 structure, because :
1331 · the external, named sub is called whenever accessing a FTS table
1333 with that tokenizer
1334 · the inner, anonymous sub is called whenever a new string needs to
1336 be tokenized (either for inserting new text into the table, or for
1337 analyzing a query).
1338 · the innermost, anonymous sub is called repeatedly for retrieving
1340 all terms within that string.
1341 Instead of writing tokenizers by hand, you can grab one of those
1343 already implemented in the Search::Tokenizer module. For example, if
1344 you want ignore differences between accented characters, you can write
1345 :
1346 use Search::Tokenizer;
1348 $dbh->do(<<"") or die DBI::errstr;
1349 CREATE ... USING fts4(<columns>,
1350 tokenize=perl 'Search::Tokenizer::unaccent')
1351 Alternatively, you can use "new" in Search::Tokenizer to build your own
1353 tokenizer.
1354 Incomplete handling of utf8 characters
1356 The current FTS implementation in SQLite is far from complete with
1357 respect to utf8 handling : in particular, variable-length characters
1358 are not treated correctly by the builtin functions "offsets()" and
1359 "snippet()".
1360 Database space for FTS
1362 By default, FTS stores a complete copy of the indexed documents,
1363 together with the fulltext index. On a large collection of documents,
1364 this can consume quite a lot of disk space. However, FTS has some
1365 options for compressing the documents, or even for not storing them at
1366 all -- see <http://www.sqlite.org/fts3.html#fts4_options>.
1367
1369 The RTREE extension module within SQLite adds support for creating a
1370 R-Tree, a special index for range and multidimensional queries. This
1371 allows users to create tables that can be loaded with (as an example)
1372 geospatial data such as latitude/longitude coordinates for buildings
1373 within a city :
1374 CREATE VIRTUAL TABLE city_buildings USING rtree(
1376 id, -- Integer primary key
1377 minLong, maxLong, -- Minimum and maximum longitude
1378 minLat, maxLat -- Minimum and maximum latitude
1379 );
1380 then query which buildings overlap or are contained within a specified
1382 region:
1383 # IDs that are contained within query coordinates
1385 my $contained_sql = <<"";
1386 SELECT id FROM try_rtree
1387 WHERE minLong >= ? AND maxLong <= ?
1388 AND minLat >= ? AND maxLat <= ?
1389 # ... and those that overlap query coordinates
1391 my $overlap_sql = <<"";
1392 SELECT id FROM try_rtree
1393 WHERE maxLong >= ? AND minLong <= ?
1394 AND maxLat >= ? AND minLat <= ?
1395 my $contained = $dbh->selectcol_arrayref($contained_sql,undef,
1397 $minLong, $maxLong, $minLat, $maxLat);
1398 my $overlapping = $dbh->selectcol_arrayref($overlap_sql,undef,
1400 $minLong, $maxLong, $minLat, $maxLat);
1401 For more detail, please see the SQLite R-Tree page
1403 (<http://www.sqlite.org/rtree.html>). Note that custom R-Tree queries
1404 using callbacks, as mentioned in the prior link, have not been
1405 implemented yet.
1406
1408 Since 1.30_01, you can retrieve the bundled sqlite C source and/or
1409 header like this:
1410 use File::ShareDir 'dist_dir';
1412 use File::Spec::Functions 'catfile';
1413 # the whole sqlite3.h header
1415 my $sqlite3_h = catfile(dist_dir('DBD-SQLite'), 'sqlite3.h');
1416 # or only a particular header, amalgamated in sqlite3.c
1418 my $what_i_want = 'parse.h';
1419 my $sqlite3_c = catfile(dist_dir('DBD-SQLite'), 'sqlite3.c');
1420 open my $fh, '<', $sqlite3_c or die $!;
1421 my $code = do { local $/; <$fh> };
1422 my ($parse_h) = $code =~ m{(
1423 /\*+[ ]Begin[ ]file[ ]$what_i_want[ ]\*+
1424 .+?
1425 /\*+[ ]End[ ]of[ ]$what_i_want[ ]\*+/
1426 )}sx;
1427 open my $out, '>', $what_i_want or die $!;
1428 print $out $parse_h;
1429 close $out;
1430 You usually want to use this in your extension's "Makefile.PL", and you
1432 may want to add DBD::SQLite to your extension's "CONFIGURE_REQUIRES" to
1433 ensure your extension users use the same C source/header they use to
1434 build DBD::SQLite itself (instead of the ones installed in their
1435 system).
1436
1438 The following items remain to be done.
1439 Leak Detection
1441 Implement one or more leak detection tests that only run during
1442 AUTOMATED_TESTING and RELEASE_TESTING and validate that none of the C
1443 code we work with leaks.
1444 Stream API for Blobs
1446 Reading/writing into blobs using "sqlite2_blob_open" /
1447 "sqlite2_blob_close".
1448 Flags for sqlite3_open_v2
1450 Support the full API of sqlite3_open_v2 (flags for opening the file).
1451 Support for custom callbacks for R-Tree queries
1453 Custom queries of a R-Tree index using a callback are possible with the
1454 SQLite C API (<http://www.sqlite.org/rtree.html>), so one could
1455 potentially use a callback that narrowed the result set down based on a
1456 specific need, such as querying for overlapping circles.
1457
1459 Bugs should be reported via the CPAN bug tracker at
1460 <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
1462 Note that bugs of bundled sqlite library (i.e. bugs in "sqlite3.[ch]")
1464 should be reported to the sqlite developers at sqlite.org via their bug
1465 tracker or via their mailing list.
1466
1468 Matt Sergeant <matt@sergeant.org>
1469 Francis J. Lacoste <flacoste@logreport.org>
1471 Wolfgang Sourdeau <wolfgang@logreport.org>
1473 Adam Kennedy <adamk@cpan.org>
1475 Max Maischein <corion@cpan.org>
1477 Laurent Dami <dami@cpan.org>
1479 Kenichi Ishigaki <ishigaki@cpan.org>
1481
1483 The bundled SQLite code in this distribution is Public Domain.
1484 DBD::SQLite is copyright 2002 - 2007 Matt Sergeant.
1486 Some parts copyright 2008 Francis J. Lacoste.
1488 Some parts copyright 2008 Wolfgang Sourdeau.
1490 Some parts copyright 2008 - 2013 Adam Kennedy.
1492 Some parts copyright 2009 - 2013 Kenichi Ishigaki.
1494 Some parts derived from DBD::SQLite::Amalgamation copyright 2008 Audrey
1496 Tang.
1497 This program is free software; you can redistribute it and/or modify it
1499 under the same terms as Perl itself.
1500 The full text of the license can be found in the LICENSE file included
1502 with this module.
1503perl v5.16.3 2013-06-09 DBD::SQLite(3)