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 DBD::SQLite is usually compiled with a bundled SQLite library (SQLite
46 version 3.22.0 as of this release) for consistency. However, a
47 different version of SQLite may sometimes be used for some reasons like
48 security, or some new experimental features.
49 You can look at $DBD::SQLite::sqlite_version ("3.x.y" format) or
51 $DBD::SQLite::sqlite_version_number ("3xxxyyy" format) to find which
52 version of SQLite is actually used. You can also check
53 "DBD::SQLite::Constants::SQLITE_VERSION_NUMBER()".
54 You can also find how the library is compiled by calling
56 "DBD::SQLite::compile_options()" (see below).
57
59 Database Name Is A File Name
60 SQLite creates a file per a database. You should pass the "path" of the
61 database file (with or without a parent directory) in the DBI
62 connection string (as a database "name"):
63 my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");
65 The file is opened in read/write mode, and will be created if it does
67 not exist yet.
68 Although the database is stored in a single file, the directory
70 containing the database file must be writable by SQLite because the
71 library will create several temporary files there.
72 If the filename $dbfile is ":memory:", then a private, temporary in-
74 memory database is created for the connection. This in-memory database
75 will vanish when the database connection is closed. It is handy for
76 your library tests.
77 Note that future versions of SQLite might make use of additional
79 special filenames that begin with the ":" character. It is recommended
80 that when a database filename actually does begin with a ":" character
81 you should prefix the filename with a pathname such as "./" to avoid
82 ambiguity.
83 If the filename $dbfile is an empty string, then a private, temporary
85 on-disk database will be created. This private database will be
86 automatically deleted as soon as the database connection is closed.
87 As of 1.41_01, you can pass URI filename (see
89 <http://www.sqlite.org/uri.html>) as well for finer control:
90 my $dbh = DBI->connect("dbi:SQLite:uri=file:$path_to_dbfile?mode=rwc");
92 Note that this is not for remote SQLite database connection. You can
94 only connect to a local database.
95 Read-Only Database
97 You can set sqlite_open_flags (only) when you connect to a database:
98 use DBD::SQLite::Constants qw/:file_open/;
100 my $dbh = DBI->connect("dbi:SQLite:$dbfile", undef, undef, {
101 sqlite_open_flags => SQLITE_OPEN_READONLY,
102 });
103 See <http://www.sqlite.org/c3ref/open.html> for details.
105 As of 1.49_05, you can also make a database read-only by setting
107 "ReadOnly" attribute to true (only) when you connect to a database.
108 Actually you can set it after you connect, but in that case, it can't
109 make the database read-only, and you'll see a warning (which you can
110 hide by turning "PrintWarn" off).
111 DBD::SQLite And File::Temp
113 When you use File::Temp to create a temporary file/directory for SQLite
114 databases, you need to remember:
115 tempfile may be locked exclusively
117 You may want to use "tempfile()" to create a temporary database
118 filename for DBD::SQLite, but as noted in File::Temp's POD, this
119 file may have an exclusive lock under some operating systems
120 (notably Mac OSX), and result in a "database is locked" error. To
121 avoid this, set EXLOCK option to false when you call tempfile().
122 ($fh, $filename) = tempfile($template, EXLOCK => 0);
124 CLEANUP may not work unless a database is disconnected
126 When you set CLEANUP option to true when you create a temporary
127 directory with "tempdir()" or "newdir()", you may have to
128 disconnect databases explicitly before the temporary directory is
129 gone (notably under MS Windows).
130 (The above is quoted from the pod of File::Temp.)
132 If you don't need to keep or share a temporary database, use ":memory:"
134 database instead. It's much handier and cleaner for ordinary testing.
135 DBD::SQLite and fork()
137 Follow the advice in the SQLite FAQ (<https://sqlite.org/faq.html>).
138 Under Unix, you should not carry an open SQLite database across a
140 fork() system call into the child process. Problems will result if
141 you do.
142 You shouldn't (re)use a database handle you created (probably to set up
144 a database schema etc) before you fork(). Otherwise, you might see a
145 database corruption in the worst case.
146 If you need to fork(), (re)open a database after you fork(). You might
148 also want to tweak "sqlite_busy_timeout" and
149 "sqlite_use_immediate_transaction" (see below), depending on your
150 needs.
151 If you need a higher level of concurrency than SQLite supports,
153 consider using other client/server database engines.
154 Accessing A Database With Other Tools
156 To access the database from the command line, try using "dbish" which
157 comes with the DBI::Shell module. Just type:
158 dbish dbi:SQLite:foo.db
160 On the command line to access the file foo.db.
162 Alternatively you can install SQLite from the link above without
164 conflicting with DBD::SQLite and use the supplied "sqlite3" command
165 line tool.
166 Blobs
168 As of version 1.11, blobs should "just work" in SQLite as text columns.
169 However this will cause the data to be treated as a string, so SQL
170 statements such as length(x) will return the length of the column as a
171 NUL terminated string, rather than the size of the blob in bytes. In
172 order to store natively as a BLOB use the following code:
173 use DBI qw(:sql_types);
175 my $dbh = DBI->connect("dbi:SQLite:dbfile","","");
176 my $blob = `cat foo.jpg`;
178 my $sth = $dbh->prepare("INSERT INTO mytable VALUES (1, ?)");
179 $sth->bind_param(1, $blob, SQL_BLOB);
180 $sth->execute();
181 And then retrieval just works:
183 $sth = $dbh->prepare("SELECT * FROM mytable WHERE id = 1");
185 $sth->execute();
186 my $row = $sth->fetch;
187 my $blobo = $row->[1];
188 # now $blobo == $blob
190 Functions And Bind Parameters
192 As of this writing, a SQL that compares a return value of a function
193 with a numeric bind value like this doesn't work as you might expect.
194 my $sth = $dbh->prepare(q{
196 SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
197 });
198 $sth->execute(5);
199 This is because DBD::SQLite assumes that all the bind values are text
201 (and should be quoted) by default. Thus the above statement becomes
202 like this while executing:
203 SELECT bar FROM foo GROUP BY bar HAVING count(*) > "5";
205 There are three workarounds for this.
207 Use bind_param() explicitly
209 As shown above in the "BLOB" section, you can always use
210 "bind_param()" to tell the type of a bind value.
211 use DBI qw(:sql_types); # Don't forget this
213 my $sth = $dbh->prepare(q{
215 SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
216 });
217 $sth->bind_param(1, 5, SQL_INTEGER);
218 $sth->execute();
219 Add zero to make it a number
221 This is somewhat weird, but works anyway.
222 my $sth = $dbh->prepare(q{
224 SELECT bar FROM foo GROUP BY bar HAVING count(*) > (? + 0);
225 });
226 $sth->execute(5);
227 Set "sqlite_see_if_its_a_number" database handle attribute
229 As of version 1.32_02, you can use "sqlite_see_if_its_a_number" to
230 let DBD::SQLite to see if the bind values are numbers or not.
231 $dbh->{sqlite_see_if_its_a_number} = 1;
233 my $sth = $dbh->prepare(q{
234 SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
235 });
236 $sth->execute(5);
237 You can set it to true when you connect to a database.
239 my $dbh = DBI->connect('dbi:SQLite:foo', undef, undef, {
241 AutoCommit => 1,
242 RaiseError => 1,
243 sqlite_see_if_its_a_number => 1,
244 });
245 This is the most straightforward solution, but as noted above,
247 existing data in your databases created by DBD::SQLite have not
248 always been stored as numbers, so this *might* cause other obscure
249 problems. Use this sparingly when you handle existing databases.
250 If you handle databases created by other tools like native
251 "sqlite3" command line tool, this attribute would help you.
252 As of 1.41_04, "sqlite_see_if_its_a_number" works only for bind
254 values with no explicit type.
255 my $dbh = DBI->connect('dbi:SQLite:foo', undef, undef, {
257 AutoCommit => 1,
258 RaiseError => 1,
259 sqlite_see_if_its_a_number => 1,
260 });
261 my $sth = $dbh->prepare('INSERT INTO foo VALUES(?)');
262 # '1.230' will be inserted as a text, instead of 1.23 as a number,
263 # even though sqlite_see_if_its_a_number is set.
264 $sth->bind_param(1, '1.230', SQL_VARCHAR);
265 $sth->execute;
266 Placeholders
268 SQLite supports several placeholder expressions, including "?" and
269 ":AAAA". Consult the DBI and SQLite documentation for details.
270 <http://www.sqlite.org/lang_expr.html#varparam>
272 Note that a question mark actually means a next unused (numbered)
274 placeholder. You're advised not to use it with other (numbered or
275 named) placeholders to avoid confusion.
276 my $sth = $dbh->prepare(
278 'update TABLE set a=?1 where b=?2 and a IS NOT ?1'
279 );
280 $sth->execute(1, 2);
281 Pragma
283 SQLite has a set of "Pragma"s to modify its operation or to query for
284 its internal data. These are specific to SQLite and are not likely to
285 work with other DBD libraries, but you may find some of these are quite
286 useful, including:
287 journal_mode
289 You can use this pragma to change the journal mode for SQLite
290 databases, maybe for better performance, or for compatibility.
291 Its default mode is "DELETE", which means SQLite uses a rollback
293 journal to implement transactions, and the journal is deleted at
294 the conclusion of each transaction. If you use "TRUNCATE" instead
295 of "DELETE", the journal will be truncated, which is usually much
296 faster.
297 A "WAL" (write-ahead log) mode is introduced as of SQLite 3.7.0.
299 This mode is persistent, and it stays in effect even after closing
300 and reopening the database. In other words, once the "WAL" mode is
301 set in an application or in a test script, the database becomes
302 inaccessible by older clients. This tends to be an issue when you
303 use a system "sqlite3" executable under a conservative operating
304 system.
305 To fix this, You need to issue "PRAGMA journal_mode = DELETE" (or
307 "TRUNCATE") beforehand, or install a newer version of "sqlite3".
308 legacy_file_format
310 If you happen to need to create a SQLite database that will also be
311 accessed by a very old SQLite client (prior to 3.3.0 released in
312 Jan. 2006), you need to set this pragma to ON before you create a
313 database.
314 reverse_unordered_selects
316 You can set this pragma to ON to reverse the order of results of
317 SELECT statements without an ORDER BY clause so that you can see if
318 applications are making invalid assumptions about the result order.
319 Note that SQLite 3.7.15 (bundled with DBD::SQLite 1.38_02) enhanced
321 its query optimizer and the order of results of a SELECT statement
322 without an ORDER BY clause may be different from the one of the
323 previous versions.
324 synchronous
326 You can set set this pragma to OFF to make some of the operations
327 in SQLite faster with a possible risk of database corruption in the
328 worst case. See also "Performance" section below.
329 See <http://www.sqlite.org/pragma.html> for more details.
331 Foreign Keys
333 SQLite has started supporting foreign key constraints since 3.6.19
334 (released on Oct 14, 2009; bundled in DBD::SQLite 1.26_05). To be
335 exact, SQLite has long been able to parse a schema with foreign keys,
336 but the constraints has not been enforced. Now you can issue a
337 "foreign_keys" pragma to enable this feature and enforce the
338 constraints, preferably as soon as you connect to a database and you're
339 not in a transaction:
340 $dbh->do("PRAGMA foreign_keys = ON");
342 And you can explicitly disable the feature whenever you like by turning
344 the pragma off:
345 $dbh->do("PRAGMA foreign_keys = OFF");
347 As of this writing, this feature is disabled by default by the SQLite
349 team, and by us, to secure backward compatibility, as this feature may
350 break your applications, and actually broke some for us. If you have
351 used a schema with foreign key constraints but haven't cared them much
352 and supposed they're always ignored for SQLite, be prepared, and please
353 do extensive testing to ensure that your applications will continue to
354 work when the foreign keys support is enabled by default.
355 See <http://www.sqlite.org/foreignkeys.html> for details.
357 Transactions
359 DBI/DBD::SQLite's transactions may be a bit confusing. They behave
360 differently according to the status of the "AutoCommit" flag:
361 When the AutoCommit flag is on
363 You're supposed to always use the auto-commit mode, except you
364 explicitly begin a transaction, and when the transaction ended,
365 you're supposed to go back to the auto-commit mode. To begin a
366 transaction, call "begin_work" method, or issue a "BEGIN"
367 statement. To end it, call "commit/rollback" methods, or issue the
368 corresponding statements.
369 $dbh->{AutoCommit} = 1;
371 $dbh->begin_work; # or $dbh->do('BEGIN TRANSACTION');
373 # $dbh->{AutoCommit} is turned off temporarily during a transaction;
375 $dbh->commit; # or $dbh->do('COMMIT');
377 # $dbh->{AutoCommit} is turned on again;
379 When the AutoCommit flag is off
381 You're supposed to always use the transactional mode, until you
382 explicitly turn on the AutoCommit flag. You can explicitly issue a
383 "BEGIN" statement (only when an actual transaction has not begun
384 yet) but you're not allowed to call "begin_work" method (if you
385 don't issue a "BEGIN", it will be issued internally). You can
386 commit or roll it back freely. Another transaction will
387 automatically begin if you execute another statement.
388 $dbh->{AutoCommit} = 0;
390 # $dbh->do('BEGIN TRANSACTION') is not necessary, but possible
392 ...
394 $dbh->commit; # or $dbh->do('COMMIT');
396 # $dbh->{AutoCommit} stays intact;
398 $dbh->{AutoCommit} = 1; # ends the transactional mode
400 This "AutoCommit" mode is independent from the autocommit mode of the
402 internal SQLite library, which always begins by a "BEGIN" statement,
403 and ends by a "COMMIT" or a <ROLLBACK>.
404 Transaction and Database Locking
406 The default transaction behavior of SQLite is "deferred", that means,
407 locks are not acquired until the first read or write operation, and
408 thus it is possible that another thread or process could create a
409 separate transaction and write to the database after the "BEGIN" on the
410 current thread has executed, and eventually cause a "deadlock". To
411 avoid this, DBD::SQLite internally issues a "BEGIN IMMEDIATE" if you
412 begin a transaction by calling "begin_work" or by turning off
413 "AutoCommit" (since 1.38_01).
414 If you really need to turn off this feature for some reasons, set
416 "sqlite_use_immediate_transaction" database handle attribute to false,
417 and the default "deferred" transaction will be used.
418 my $dbh = DBI->connect("dbi:SQLite::memory:", "", "", {
420 sqlite_use_immediate_transaction => 0,
421 });
422 Or, issue a "BEGIN" statement explicitly each time you begin a
424 transaction.
425 See <http://sqlite.org/lockingv3.html> for locking details.
427 "$sth->finish" and Transaction Rollback
429 As the DBI doc says, you almost certainly do not need to call "finish"
430 in DBI method if you fetch all rows (probably in a loop). However,
431 there are several exceptions to this rule, and rolling-back of an
432 unfinished "SELECT" statement is one of such exceptional cases.
433 SQLite prohibits "ROLLBACK" of unfinished "SELECT" statements in a
435 transaction (See <http://sqlite.org/lang_transaction.html> for
436 details). So you need to call "finish" before you issue a rollback.
437 $sth = $dbh->prepare("SELECT * FROM t");
439 $dbh->begin_work;
440 eval {
441 $sth->execute;
442 $row = $sth->fetch;
443 ...
444 die "For some reason";
445 ...
446 };
447 if($@) {
448 $sth->finish; # You need this for SQLite
449 $dbh->rollback;
450 } else {
451 $dbh->commit;
452 }
453 Processing Multiple Statements At A Time
455 DBI's statement handle is not supposed to process multiple statements
456 at a time. So if you pass a string that contains multiple statements (a
457 "dump") to a statement handle (via "prepare" or "do"), DBD::SQLite only
458 processes the first statement, and discards the rest.
459 If you need to process multiple statements at a time, set a
461 "sqlite_allow_multiple_statements" attribute of a database handle to
462 true when you connect to a database, and "do" method takes care of the
463 rest (since 1.30_01, and without creating DBI's statement handles
464 internally since 1.47_01). If you do need to use "prepare" or
465 "prepare_cached" (which I don't recommend in this case, because
466 typically there's no placeholder nor reusable part in a dump), you can
467 look at << $sth->{sqlite_unprepared_statements} >> to retrieve what's
468 left, though it usually contains nothing but white spaces.
469 Performance
471 SQLite is fast, very fast. Matt processed his 72MB log file with it,
472 inserting the data (400,000+ rows) by using transactions and only
473 committing every 1000 rows (otherwise the insertion is quite slow), and
474 then performing queries on the data.
475 Queries like count(*) and avg(bytes) took fractions of a second to
477 return, but what surprised him most of all was:
478 SELECT url, count(*) as count
480 FROM access_log
481 GROUP BY url
482 ORDER BY count desc
483 LIMIT 20
484 To discover the top 20 hit URLs on the site (<http://axkit.org>), and
486 it returned within 2 seconds. He was seriously considering switching
487 his log analysis code to use this little speed demon!
488 Oh yeah, and that was with no indexes on the table, on a 400MHz PIII.
490 For best performance be sure to tune your hdparm settings if you are
492 using linux. Also you might want to set:
493 PRAGMA synchronous = OFF
495 Which will prevent SQLite from doing fsync's when writing (which slows
497 down non-transactional writes significantly) at the expense of some
498 peace of mind. Also try playing with the cache_size pragma.
499 The memory usage of SQLite can also be tuned using the cache_size
501 pragma.
502 $dbh->do("PRAGMA cache_size = 800000");
504 The above will allocate 800M for DB cache; the default is 2M. Your
506 sweet spot probably lies somewhere in between.
507
509 Database Handle Attributes
510 sqlite_version
511 Returns the version of the SQLite library which DBD::SQLite is
512 using, e.g., "2.8.0". Can only be read.
513 sqlite_unicode
515 If set to a true value, DBD::SQLite will turn the UTF-8 flag on for
516 all text strings coming out of the database (this feature is
517 currently disabled for perl < 5.8.5). For more details on the UTF-8
518 flag see perlunicode. The default is for the UTF-8 flag to be
519 turned off.
520 Also note that due to some bizarreness in SQLite's type system (see
522 <http://www.sqlite.org/datatype3.html>), if you want to retain
523 blob-style behavior for some columns under "$dbh->{sqlite_unicode}
524 = 1" (say, to store images in the database), you have to state so
525 explicitly using the 3-argument form of "bind_param" in DBI when
526 doing updates:
527 use DBI qw(:sql_types);
529 $dbh->{sqlite_unicode} = 1;
530 my $sth = $dbh->prepare("INSERT INTO mytable (blobcolumn) VALUES (?)");
531 # Binary_data will be stored as is.
533 $sth->bind_param(1, $binary_data, SQL_BLOB);
534 Defining the column type as "BLOB" in the DDL is not sufficient.
536 This attribute was originally named as "unicode", and renamed to
538 "sqlite_unicode" for integrity since version 1.26_06. Old "unicode"
539 attribute is still accessible but will be deprecated in the near
540 future.
541 sqlite_allow_multiple_statements
543 If you set this to true, "do" method will process multiple
544 statements at one go. This may be handy, but with performance
545 penalty. See above for details.
546 sqlite_use_immediate_transaction
548 If you set this to true, DBD::SQLite tries to issue a "begin
549 immediate transaction" (instead of "begin transaction") when
550 necessary. See above for details.
551 As of version 1.38_01, this attribute is set to true by default.
553 If you really need to use "deferred" transactions for some reasons,
554 set this to false explicitly.
555 sqlite_see_if_its_a_number
557 If you set this to true, DBD::SQLite tries to see if the bind
558 values are number or not, and does not quote if they are numbers.
559 See above for details.
560 sqlite_extended_result_codes
562 If set to true, DBD::SQLite uses extended result codes where
563 appropriate (see <http://www.sqlite.org/rescode.html>).
564 Statement Handle Attributes
566 sqlite_unprepared_statements
567 Returns an unprepared part of the statement you pass to "prepare".
568 Typically this contains nothing but white spaces after a semicolon.
569 See above for details.
570
572 See also to the DBI documentation for the details of other common
573 methods.
574 table_info
576 $sth = $dbh->table_info(undef, $schema, $table, $type, \%attr);
577 Returns all tables and schemas (databases) as specified in "table_info"
579 in DBI. The schema and table arguments will do a "LIKE" search. You
580 can specify an ESCAPE character by including an 'Escape' attribute in
581 \%attr. The $type argument accepts a comma separated list of the
582 following types 'TABLE', 'VIEW', 'LOCAL TEMPORARY' and 'SYSTEM TABLE'
583 (by default all are returned). Note that a statement handle is
584 returned, and not a direct list of tables.
585 The following fields are returned:
587 TABLE_CAT: Always NULL, as SQLite does not have the concept of
589 catalogs.
590 TABLE_SCHEM: The name of the schema (database) that the table or view
592 is in. The default schema is 'main', temporary tables are in 'temp' and
593 other databases will be in the name given when the database was
594 attached.
595 TABLE_NAME: The name of the table or view.
597 TABLE_TYPE: The type of object returned. Will be one of 'TABLE',
599 'VIEW', 'LOCAL TEMPORARY' or 'SYSTEM TABLE'.
600 primary_key, primary_key_info
602 @names = $dbh->primary_key(undef, $schema, $table);
603 $sth = $dbh->primary_key_info(undef, $schema, $table, \%attr);
604 You can retrieve primary key names or more detailed information. As
606 noted above, SQLite does not have the concept of catalogs, so the first
607 argument of the methods is usually "undef", and you'll usually set
608 "undef" for the second one (unless you want to know the primary keys of
609 temporary tables).
610 foreign_key_info
612 $sth = $dbh->foreign_key_info(undef, $pk_schema, $pk_table,
613 undef, $fk_schema, $fk_table);
614 Returns information about foreign key constraints, as specified in
616 "foreign_key_info" in DBI, but with some limitations :
617 · information in rows returned by the $sth is incomplete with respect
619 to the "foreign_key_info" in DBI specification. All requested
620 fields are present, but the content is "undef" for some of them.
621 The following nonempty fields are returned :
623 PKTABLE_NAME: The primary (unique) key table identifier.
625 PKCOLUMN_NAME: The primary (unique) key column identifier.
627 FKTABLE_NAME: The foreign key table identifier.
629 FKCOLUMN_NAME: The foreign key column identifier.
631 KEY_SEQ: The column sequence number (starting with 1), when several
633 columns belong to a same constraint.
634 UPDATE_RULE: The referential action for the UPDATE rule. The following
636 codes are defined:
637 CASCADE 0
639 RESTRICT 1
640 SET NULL 2
641 NO ACTION 3
642 SET DEFAULT 4
643 Default is 3 ('NO ACTION').
645 DELETE_RULE: The referential action for the DELETE rule. The codes are
647 the same as for UPDATE_RULE.
648 Unfortunately, the DEFERRABILITY field is always "undef"; as a matter
650 of fact, deferrability clauses are supported by SQLite, but they can't
651 be reported because the "PRAGMA foreign_key_list" tells nothing about
652 them.
653 UNIQUE_OR_PRIMARY: Whether the column is primary or unique.
655 Note: foreign key support in SQLite must be explicitly turned on
657 through a "PRAGMA" command; see "Foreign keys" earlier in this manual.
658 statistics_info
660 $sth = $dbh->statistics_info(undef, $schema, $table,
661 $unique_only, $quick);
662 Returns information about a table and it's indexes, as specified in
664 "statistics_info" in DBI, but with some limitations :
665 · information in rows returned by the $sth is incomplete with respect
667 to the "statistics_info" in DBI specification. All requested fields
668 are present, but the content is "undef" for some of them.
669 The following nonempty fields are returned :
671 TABLE_SCHEM: The name of the schema (database) that the table is in.
673 The default schema is 'main', temporary tables are in 'temp' and other
674 databases will be in the name given when the database was attached.
675 TABLE_NAME: The name of the table
677 NON_UNIQUE: Contains 0 for unique indexes, 1 for non-unique indexes
679 INDEX_NAME: The name of the index
681 TYPE: SQLite uses 'btree' for all it's indexes
683 ORDINAL_POSITION: Column sequence number (starting with 1).
685 COLUMN_NAME: The name of the column
687 ping
689 my $bool = $dbh->ping;
690 returns true if the database file exists (or the database is in-
692 memory), and the database connection is active.
693
695 The following methods can be called via the func() method with a little
696 tweak, but the use of func() method is now discouraged by the DBI
697 author for various reasons (see DBI's document
698 <http://search.cpan.org/dist/DBI/lib/DBI/DBD.pm#Using_install_method()_to_expose_driver-private_methods>
699 for details). So, if you're using DBI >= 1.608, use these "sqlite_"
700 methods. If you need to use an older DBI, you can call these like this:
701 $dbh->func( ..., "(method name without sqlite_ prefix)" );
703 Exception: "sqlite_trace" should always be called as is, even with
705 "func()" method (to avoid conflict with DBI's trace() method).
706 $dbh->func( ..., "sqlite_trace");
708 $dbh->sqlite_last_insert_rowid()
710 This method returns the last inserted rowid. If you specify an INTEGER
711 PRIMARY KEY as the first column in your table, that is the column that
712 is returned. Otherwise, it is the hidden ROWID column. See the SQLite
713 docs for details.
714 Generally you should not be using this method. Use the DBI
716 last_insert_id method instead. The usage of this is:
717 $h->last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ])
719 Running "$h->last_insert_id("","","","")" is the equivalent of running
721 "$dbh->sqlite_last_insert_rowid()" directly.
722 $dbh->sqlite_db_filename()
724 Retrieve the current (main) database filename. If the database is in-
725 memory or temporary, this returns "undef".
726 $dbh->sqlite_busy_timeout()
728 Retrieve the current busy timeout.
729 $dbh->sqlite_busy_timeout( $ms )
731 Set the current busy timeout. The timeout is in milliseconds.
732 $dbh->sqlite_create_function( $name, $argc, $code_ref, $flags )
734 This method will register a new function which will be usable in an SQL
735 query. The method's parameters are:
736 $name
738 The name of the function. This is the name of the function as it
739 will be used from SQL.
740 $argc
742 The number of arguments taken by the function. If this number is
743 -1, the function can take any number of arguments.
744 $code_ref
746 This should be a reference to the function's implementation.
747 $flags
749 You can optionally pass an extra flag bit to create_function, which
750 then would be ORed with SQLITE_UTF8 (default). As of 1.47_02
751 (SQLite 3.8.9), only meaning bit is SQLITE_DETERMINISTIC
752 (introduced at SQLite 3.8.3), which can make the function perform
753 better. See C API documentation at
754 <http://sqlite.org/c3ref/create_function.html> for details.
755 For example, here is how to define a now() function which returns the
757 current number of seconds since the epoch:
758 $dbh->sqlite_create_function( 'now', 0, sub { return time } );
760 After this, it could be used from SQL as:
762 INSERT INTO mytable ( now() );
764 REGEXP function
766 SQLite includes syntactic support for an infix operator 'REGEXP', but
768 without any implementation. The "DBD::SQLite" driver automatically
769 registers an implementation that performs standard perl regular
770 expression matching, using current locale. So for example you can
771 search for words starting with an 'A' with a query like
772 SELECT * from table WHERE column REGEXP '\bA\w+'
774 If you want case-insensitive searching, use perl regex flags, like this
776 :
777 SELECT * from table WHERE column REGEXP '(?i:\bA\w+)'
779 The default REGEXP implementation can be overridden through the
781 "create_function" API described above.
782 Note that regexp matching will not use SQLite indices, but will iterate
784 over all rows, so it could be quite costly in terms of performance.
785 $dbh->sqlite_create_collation( $name, $code_ref )
787 This method manually registers a new function which will be usable in
788 an SQL query as a COLLATE option for sorting. Such functions can also
789 be registered automatically on demand: see section "COLLATION
790 FUNCTIONS" below.
791 The method's parameters are:
793 $name
795 The name of the function exposed to SQL.
796 $code_ref
798 Reference to the function's implementation. The driver will check
799 that this is a proper sorting function.
800 $dbh->sqlite_collation_needed( $code_ref )
802 This method manually registers a callback function that will be invoked
803 whenever an undefined collation sequence is required from an SQL
804 statement. The callback is invoked as
805 $code_ref->($dbh, $collation_name)
807 and should register the desired collation using
809 "sqlite_create_collation".
810 An initial callback is already registered by "DBD::SQLite", so for most
812 common cases it will be simpler to just add your collation sequences in
813 the %DBD::SQLite::COLLATION hash (see section "COLLATION FUNCTIONS"
814 below).
815 $dbh->sqlite_create_aggregate( $name, $argc, $pkg, $flags )
817 This method will register a new aggregate function which can then be
818 used from SQL. The method's parameters are:
819 $name
821 The name of the aggregate function, this is the name under which
822 the function will be available from SQL.
823 $argc
825 This is an integer which tells the SQL parser how many arguments
826 the function takes. If that number is -1, the function can take any
827 number of arguments.
828 $pkg
830 This is the package which implements the aggregator interface.
831 $flags
833 You can optionally pass an extra flag bit to create_aggregate,
834 which then would be ORed with SQLITE_UTF8 (default). As of 1.47_02
835 (SQLite 3.8.9), only meaning bit is SQLITE_DETERMINISTIC
836 (introduced at SQLite 3.8.3), which can make the function perform
837 better. See C API documentation at
838 <http://sqlite.org/c3ref/create_function.html> for details.
839 The aggregator interface consists of defining three methods:
841 new()
843 This method will be called once to create an object which should be
844 used to aggregate the rows in a particular group. The step() and
845 finalize() methods will be called upon the reference return by the
846 method.
847 step(@_)
849 This method will be called once for each row in the aggregate.
850 finalize()
852 This method will be called once all rows in the aggregate were
853 processed and it should return the aggregate function's result.
854 When there is no rows in the aggregate, finalize() will be called
855 right after new().
856 Here is a simple aggregate function which returns the variance (example
858 adapted from pysqlite):
859 package variance;
861 sub new { bless [], shift; }
863 sub step {
865 my ( $self, $value ) = @_;
866 push @$self, $value;
868 }
869 sub finalize {
871 my $self = $_[0];
872 my $n = @$self;
874 # Variance is NULL unless there is more than one row
876 return undef unless $n || $n == 1;
877 my $mu = 0;
879 foreach my $v ( @$self ) {
880 $mu += $v;
881 }
882 $mu /= $n;
883 my $sigma = 0;
885 foreach my $v ( @$self ) {
886 $sigma += ($v - $mu)**2;
887 }
888 $sigma = $sigma / ($n - 1);
889 return $sigma;
891 }
892 $dbh->sqlite_create_aggregate( "variance", 1, 'variance' );
894 The aggregate function can then be used as:
896 SELECT group_name, variance(score)
898 FROM results
899 GROUP BY group_name;
900 For more examples, see the DBD::SQLite::Cookbook.
902 $dbh->sqlite_progress_handler( $n_opcodes, $code_ref )
904 This method registers a handler to be invoked periodically during long
905 running calls to SQLite.
906 An example use for this interface is to keep a GUI updated during a
908 large query. The parameters are:
909 $n_opcodes
911 The progress handler is invoked once for every $n_opcodes virtual
912 machine opcodes in SQLite.
913 $code_ref
915 Reference to the handler subroutine. If the progress handler
916 returns non-zero, the SQLite operation is interrupted. This feature
917 can be used to implement a "Cancel" button on a GUI dialog box.
918 Set this argument to "undef" if you want to unregister a previous
920 progress handler.
921 $dbh->sqlite_commit_hook( $code_ref )
923 This method registers a callback function to be invoked whenever a
924 transaction is committed. Any callback set by a previous call to
925 "sqlite_commit_hook" is overridden. A reference to the previous
926 callback (if any) is returned. Registering an "undef" disables the
927 callback.
928 When the commit hook callback returns zero, the commit operation is
930 allowed to continue normally. If the callback returns non-zero, then
931 the commit is converted into a rollback (in that case, any attempt to
932 explicitly call "$dbh->rollback()" afterwards would yield an error).
933 $dbh->sqlite_rollback_hook( $code_ref )
935 This method registers a callback function to be invoked whenever a
936 transaction is rolled back. Any callback set by a previous call to
937 "sqlite_rollback_hook" is overridden. A reference to the previous
938 callback (if any) is returned. Registering an "undef" disables the
939 callback.
940 $dbh->sqlite_update_hook( $code_ref )
942 This method registers a callback function to be invoked whenever a row
943 is updated, inserted or deleted. Any callback set by a previous call to
944 "sqlite_update_hook" is overridden. A reference to the previous
945 callback (if any) is returned. Registering an "undef" disables the
946 callback.
947 The callback will be called as
949 $code_ref->($action_code, $database, $table, $rowid)
951 where
953 $action_code
955 is an integer equal to either "DBD::SQLite::INSERT",
956 "DBD::SQLite::DELETE" or "DBD::SQLite::UPDATE" (see "Action
957 Codes");
958 $database
960 is the name of the database containing the affected row;
961 $table
963 is the name of the table containing the affected row;
964 $rowid
966 is the unique 64-bit signed integer key of the affected row within
967 that table.
968 $dbh->sqlite_set_authorizer( $code_ref )
970 This method registers an authorizer callback to be invoked whenever SQL
971 statements are being compiled by the "prepare" in DBI method. The
972 authorizer callback should return "DBD::SQLite::OK" to allow the
973 action, "DBD::SQLite::IGNORE" to disallow the specific action but allow
974 the SQL statement to continue to be compiled, or "DBD::SQLite::DENY" to
975 cause the entire SQL statement to be rejected with an error. If the
976 authorizer callback returns any other value, then "prepare" call that
977 triggered the authorizer will fail with an error message.
978 An authorizer is used when preparing SQL statements from an untrusted
980 source, to ensure that the SQL statements do not try to access data
981 they are not allowed to see, or that they do not try to execute
982 malicious statements that damage the database. For example, an
983 application may allow a user to enter arbitrary SQL queries for
984 evaluation by a database. But the application does not want the user to
985 be able to make arbitrary changes to the database. An authorizer could
986 then be put in place while the user-entered SQL is being prepared that
987 disallows everything except SELECT statements.
988 The callback will be called as
990 $code_ref->($action_code, $string1, $string2, $database, $trigger_or_view)
992 where
994 $action_code
996 is an integer that specifies what action is being authorized (see
997 "Action Codes").
998 $string1, $string2
1000 are strings that depend on the action code (see "Action Codes").
1001 $database
1003 is the name of the database ("main", "temp", etc.) if applicable.
1004 $trigger_or_view
1006 is the name of the inner-most trigger or view that is responsible
1007 for the access attempt, or "undef" if this access attempt is
1008 directly from top-level SQL code.
1009 $dbh->sqlite_backup_from_file( $filename )
1011 This method accesses the SQLite Online Backup API, and will take a
1012 backup of the named database file, copying it to, and overwriting, your
1013 current database connection. This can be particularly handy if your
1014 current connection is to the special :memory: database, and you wish to
1015 populate it from an existing DB.
1016 $dbh->sqlite_backup_to_file( $filename )
1018 This method accesses the SQLite Online Backup API, and will take a
1019 backup of the currently connected database, and write it out to the
1020 named file.
1021 $dbh->sqlite_enable_load_extension( $bool )
1023 Calling this method with a true value enables loading (external)
1024 SQLite3 extensions. After the call, you can load extensions like this:
1025 $dbh->sqlite_enable_load_extension(1);
1027 $sth = $dbh->prepare("select load_extension('libsqlitefunctions.so')")
1028 or die "Cannot prepare: " . $dbh->errstr();
1029 $dbh->sqlite_load_extension( $file, $proc )
1031 Loading an extension by a select statement (with the "load_extension"
1032 SQLite3 function like above) has some limitations. If you need to, say,
1033 create other functions from an extension, use this method. $file (a
1034 path to the extension) is mandatory, and $proc (an entry point name) is
1035 optional. You need to call "sqlite_enable_load_extension" before
1036 calling "sqlite_load_extension".
1037 $dbh->sqlite_trace( $code_ref )
1039 This method registers a trace callback to be invoked whenever SQL
1040 statements are being run.
1041 The callback will be called as
1043 $code_ref->($statement)
1045 where
1047 $statement
1049 is a UTF-8 rendering of the SQL statement text as the statement
1050 first begins executing.
1051 Additional callbacks might occur as each triggered subprogram is
1053 entered. The callbacks for triggers contain a UTF-8 SQL comment that
1054 identifies the trigger.
1055 See also "TRACING" in DBI for better tracing options.
1057 $dbh->sqlite_profile( $code_ref )
1059 This method registers a profile callback to be invoked whenever a SQL
1060 statement finishes.
1061 The callback will be called as
1063 $code_ref->($statement, $elapsed_time)
1065 where
1067 $statement
1069 is the original statement text (without bind parameters).
1070 $elapsed_time
1072 is an estimate of wall-clock time of how long that statement took
1073 to run (in milliseconds).
1074 This method is considered experimental and is subject to change in
1076 future versions of SQLite.
1077 See also DBI::Profile for better profiling options.
1079 $dbh->sqlite_table_column_metadata( $dbname, $tablename, $columnname )
1081 is for internal use only.
1082 $dbh->sqlite_db_status()
1084 Returns a hash reference that holds a set of status information of
1085 database connection such as cache usage. See
1086 <http://www.sqlite.org/c3ref/c_dbstatus_options.html> for details. You
1087 may also pass 0 as an argument to reset the status.
1088 $sth->sqlite_st_status()
1090 Returns a hash reference that holds a set of status information of
1091 SQLite statement handle such as full table scan count. See
1092 <http://www.sqlite.org/c3ref/c_stmtstatus_counter.html> for details.
1093 Statement status only holds the current value.
1094 my $status = $sth->sqlite_st_status();
1096 my $cur = $status->{fullscan_step};
1097 You may also pass 0 as an argument to reset the status.
1099 $dbh->sqlite_create_module()
1101 Registers a name for a virtual table module. Module names must be
1102 registered before creating a new virtual table using the module and
1103 before using a preexisting virtual table for the module. Virtual
1104 tables are explained in DBD::SQLite::VirtualTable.
1105
1107 DBD::SQLite::compile_options()
1108 Returns an array of compile options (available since SQLite 3.6.23,
1109 bundled in DBD::SQLite 1.30_01), or an empty array if the bundled
1110 library is old or compiled with SQLITE_OMIT_COMPILEOPTION_DIAGS.
1111 DBD::SQLite::sqlite_status()
1113 Returns a hash reference that holds a set of status information of
1114 SQLite runtime such as memory usage or page cache usage (see
1115 <http://www.sqlite.org/c3ref/c_status_malloc_count.html> for details).
1116 Each of the entry contains the current value and the highwater value.
1117 my $status = DBD::SQLite::sqlite_status();
1119 my $cur = $status->{memory_used}{current};
1120 my $high = $status->{memory_used}{highwater};
1121 You may also pass 0 as an argument to reset the status.
1123 DBD::SQLite::strlike($pattern, $string, $escape_char),
1125 DBD::SQLite::strglob($pattern, $string)
1126 As of 1.49_05 (SQLite 3.10.0), you can use these two functions to see
1127 if a string matches a pattern. These may be useful when you create a
1128 virtual table or a custom function. See
1129 <http://sqlite.org/c3ref/strlike.html> and
1130 <http://sqlite.org/c3ref/strglob.html> for details.
1131
1133 A subset of SQLite C constants are made available to Perl, because they
1134 may be needed when writing hooks or authorizer callbacks. For accessing
1135 such constants, the "DBD::SQLite" module must be explicitly "use"d at
1136 compile time. For example, an authorizer that forbids any DELETE
1137 operation would be written as follows :
1138 use DBD::SQLite;
1140 $dbh->sqlite_set_authorizer(sub {
1141 my $action_code = shift;
1142 return $action_code == DBD::SQLite::DELETE ? DBD::SQLite::DENY
1143 : DBD::SQLite::OK;
1144 });
1145 The list of constants implemented in "DBD::SQLite" is given below; more
1147 information can be found ad at
1148 <http://www.sqlite.org/c3ref/constlist.html>.
1149 Authorizer Return Codes
1151 OK
1152 DENY
1153 IGNORE
1154 Action Codes
1156 The "set_authorizer" method registers a callback function that is
1157 invoked to authorize certain SQL statement actions. The first parameter
1158 to the callback is an integer code that specifies what action is being
1159 authorized. The second and third parameters to the callback are
1160 strings, the meaning of which varies according to the action code.
1161 Below is the list of action codes, together with their associated
1162 strings.
1163 # constant string1 string2
1165 # ======== ======= =======
1166 CREATE_INDEX Index Name Table Name
1167 CREATE_TABLE Table Name undef
1168 CREATE_TEMP_INDEX Index Name Table Name
1169 CREATE_TEMP_TABLE Table Name undef
1170 CREATE_TEMP_TRIGGER Trigger Name Table Name
1171 CREATE_TEMP_VIEW View Name undef
1172 CREATE_TRIGGER Trigger Name Table Name
1173 CREATE_VIEW View Name undef
1174 DELETE Table Name undef
1175 DROP_INDEX Index Name Table Name
1176 DROP_TABLE Table Name undef
1177 DROP_TEMP_INDEX Index Name Table Name
1178 DROP_TEMP_TABLE Table Name undef
1179 DROP_TEMP_TRIGGER Trigger Name Table Name
1180 DROP_TEMP_VIEW View Name undef
1181 DROP_TRIGGER Trigger Name Table Name
1182 DROP_VIEW View Name undef
1183 INSERT Table Name undef
1184 PRAGMA Pragma Name 1st arg or undef
1185 READ Table Name Column Name
1186 SELECT undef undef
1187 TRANSACTION Operation undef
1188 UPDATE Table Name Column Name
1189 ATTACH Filename undef
1190 DETACH Database Name undef
1191 ALTER_TABLE Database Name Table Name
1192 REINDEX Index Name undef
1193 ANALYZE Table Name undef
1194 CREATE_VTABLE Table Name Module Name
1195 DROP_VTABLE Table Name Module Name
1196 FUNCTION undef Function Name
1197 SAVEPOINT Operation Savepoint Name
1198
1200 Definition
1201 SQLite v3 provides the ability for users to supply arbitrary comparison
1202 functions, known as user-defined "collation sequences" or "collating
1203 functions", to be used for comparing two text values.
1204 <http://www.sqlite.org/datatype3.html#collation> explains how
1205 collations are used in various SQL expressions.
1206 Builtin collation sequences
1208 The following collation sequences are builtin within SQLite :
1209 BINARY
1211 Compares string data using memcmp(), regardless of text encoding.
1212 NOCASE
1214 The same as binary, except the 26 upper case characters of ASCII
1215 are folded to their lower case equivalents before the comparison is
1216 performed. Note that only ASCII characters are case folded. SQLite
1217 does not attempt to do full UTF case folding due to the size of the
1218 tables required.
1219 RTRIM
1221 The same as binary, except that trailing space characters are
1222 ignored.
1223 In addition, "DBD::SQLite" automatically installs the following
1225 collation sequences :
1226 perl
1228 corresponds to the Perl "cmp" operator
1229 perllocale
1231 Perl "cmp" operator, in a context where "use locale" is activated.
1232 Usage
1234 You can write for example
1235 CREATE TABLE foo(
1237 txt1 COLLATE perl,
1238 txt2 COLLATE perllocale,
1239 txt3 COLLATE nocase
1240 )
1241 or
1243 SELECT * FROM foo ORDER BY name COLLATE perllocale
1245 Unicode handling
1247 If the attribute "$dbh->{sqlite_unicode}" is set, strings coming from
1248 the database and passed to the collation function will be properly
1249 tagged with the utf8 flag; but this only works if the "sqlite_unicode"
1250 attribute is set before the first call to a perl collation sequence .
1251 The recommended way to activate unicode is to set the parameter at
1252 connection time :
1253 my $dbh = DBI->connect(
1255 "dbi:SQLite:dbname=foo", "", "",
1256 {
1257 RaiseError => 1,
1258 sqlite_unicode => 1,
1259 }
1260 );
1261 Adding user-defined collations
1263 The native SQLite API for adding user-defined collations is exposed
1264 through methods "sqlite_create_collation" and
1265 "sqlite_collation_needed".
1266 To avoid calling these functions every time a $dbh handle is created,
1268 "DBD::SQLite" offers a simpler interface through the
1269 %DBD::SQLite::COLLATION hash : just insert your own collation functions
1270 in that hash, and whenever an unknown collation name is encountered in
1271 SQL, the appropriate collation function will be loaded on demand from
1272 the hash. For example, here is a way to sort text values regardless of
1273 their accented characters :
1274 use DBD::SQLite;
1276 $DBD::SQLite::COLLATION{no_accents} = sub {
1277 my ( $a, $b ) = map lc, @_;
1278 tr[����������������������������]
1279 [aaaaaacdeeeeiiiinoooooouuuuy] for $a, $b;
1280 $a cmp $b;
1281 };
1282 my $dbh = DBI->connect("dbi:SQLite:dbname=dbfile");
1283 my $sql = "SELECT ... FROM ... ORDER BY ... COLLATE no_accents");
1284 my $rows = $dbh->selectall_arrayref($sql);
1285 The builtin "perl" or "perllocale" collations are predefined in that
1287 same hash.
1288 The COLLATION hash is a global registry within the current process;
1290 hence there is a risk of undesired side-effects. Therefore, to prevent
1291 action at distance, the hash is implemented as a "write-only" hash,
1292 that will happily accept new entries, but will raise an exception if
1293 any attempt is made to override or delete a existing entry (including
1294 the builtin "perl" and "perllocale").
1295 If you really, really need to change or delete an entry, you can always
1297 grab the tied object underneath %DBD::SQLite::COLLATION --- but don't
1298 do that unless you really know what you are doing. Also observe that
1299 changes in the global hash will not modify existing collations in
1300 existing database handles: it will only affect new requests for
1301 collations. In other words, if you want to change the behaviour of a
1302 collation within an existing $dbh, you need to call the
1303 "create_collation" method directly.
1304
1306 SQLite is bundled with an extension module for full-text indexing.
1307 Tables with this feature enabled can be efficiently queried to find
1308 rows that contain one or more instances of some specified words, in any
1309 column, even if the table contains many large documents.
1310 Explanations for using this feature are provided in a separate
1312 document: see DBD::SQLite::Fulltext_search.
1313
1315 The RTREE extension module within SQLite adds support for creating a
1316 R-Tree, a special index for range and multidimensional queries. This
1317 allows users to create tables that can be loaded with (as an example)
1318 geospatial data such as latitude/longitude coordinates for buildings
1319 within a city :
1320 CREATE VIRTUAL TABLE city_buildings USING rtree(
1322 id, -- Integer primary key
1323 minLong, maxLong, -- Minimum and maximum longitude
1324 minLat, maxLat -- Minimum and maximum latitude
1325 );
1326 then query which buildings overlap or are contained within a specified
1328 region:
1329 # IDs that are contained within query coordinates
1331 my $contained_sql = <<"";
1332 SELECT id FROM city_buildings
1333 WHERE minLong >= ? AND maxLong <= ?
1334 AND minLat >= ? AND maxLat <= ?
1335 # ... and those that overlap query coordinates
1337 my $overlap_sql = <<"";
1338 SELECT id FROM city_buildings
1339 WHERE maxLong >= ? AND minLong <= ?
1340 AND maxLat >= ? AND minLat <= ?
1341 my $contained = $dbh->selectcol_arrayref($contained_sql,undef,
1343 $minLong, $maxLong, $minLat, $maxLat);
1344 my $overlapping = $dbh->selectcol_arrayref($overlap_sql,undef,
1346 $minLong, $maxLong, $minLat, $maxLat);
1347 For more detail, please see the SQLite R-Tree page
1349 (<http://www.sqlite.org/rtree.html>). Note that custom R-Tree queries
1350 using callbacks, as mentioned in the prior link, have not been
1351 implemented yet.
1352
1354 SQLite has a concept of "virtual tables" which look like regular tables
1355 but are implemented internally through specific functions. The
1356 fulltext or R* tree features described in the previous chapters are
1357 examples of such virtual tables, implemented in C code.
1358 "DBD::SQLite" also supports virtual tables implemented in Perl code:
1360 see DBD::SQLite::VirtualTable for using or implementing such virtual
1361 tables. These can have many interesting uses for joining regular DBMS
1362 data with some other kind of data within your Perl programs. Bundled
1363 with the present distribution are :
1364 · DBD::SQLite::VirtualTable::FileContent : implements a virtual
1366 column that exposes file contents. This is especially useful in
1367 conjunction with a fulltext index; see
1368 DBD::SQLite::Fulltext_search.
1369 · DBD::SQLite::VirtualTable::PerlData : binds to a Perl array within
1371 the Perl program. This can be used for simple import/export
1372 operations, for debugging purposes, for joining data from different
1373 sources, etc.
1374 Other Perl virtual tables may also be published separately on CPAN.
1376
1378 The following items remain to be done.
1379 Leak Detection
1381 Implement one or more leak detection tests that only run during
1382 AUTOMATED_TESTING and RELEASE_TESTING and validate that none of the C
1383 code we work with leaks.
1384 Stream API for Blobs
1386 Reading/writing into blobs using "sqlite2_blob_open" /
1387 "sqlite2_blob_close".
1388 Support for custom callbacks for R-Tree queries
1390 Custom queries of a R-Tree index using a callback are possible with the
1391 SQLite C API (<http://www.sqlite.org/rtree.html>), so one could
1392 potentially use a callback that narrowed the result set down based on a
1393 specific need, such as querying for overlapping circles.
1394
1396 Bugs should be reported via the CPAN bug tracker at
1397 <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
1399 Note that bugs of bundled SQLite library (i.e. bugs in "sqlite3.[ch]")
1401 should be reported to the SQLite developers at sqlite.org via their bug
1402 tracker or via their mailing list.
1403 The master repository is on GitHub:
1405 <https://github.com/DBD-SQLite/DBD-SQLite>.
1407 We also have a mailing list:
1409 <http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite>
1411
1413 Matt Sergeant <matt@sergeant.org>
1414 Francis J. Lacoste <flacoste@logreport.org>
1416 Wolfgang Sourdeau <wolfgang@logreport.org>
1418 Adam Kennedy <adamk@cpan.org>
1420 Max Maischein <corion@cpan.org>
1422 Laurent Dami <dami@cpan.org>
1424 Kenichi Ishigaki <ishigaki@cpan.org>
1426
1428 The bundled SQLite code in this distribution is Public Domain.
1429 DBD::SQLite is copyright 2002 - 2007 Matt Sergeant.
1431 Some parts copyright 2008 Francis J. Lacoste.
1433 Some parts copyright 2008 Wolfgang Sourdeau.
1435 Some parts copyright 2008 - 2013 Adam Kennedy.
1437 Some parts copyright 2009 - 2013 Kenichi Ishigaki.
1439 Some parts derived from DBD::SQLite::Amalgamation copyright 2008 Audrey
1441 Tang.
1442 This program is free software; you can redistribute it and/or modify it
1444 under the same terms as Perl itself.
1445 The full text of the license can be found in the LICENSE file included
1447 with this module.
1448perl v5.26.3 2019-05-11 DBD::SQLite(3)