1DBD::SQLite(3)        User Contributed Perl Documentation       DBD::SQLite(3)
2
3
4

NAME

6       DBD::SQLite - Self-contained RDBMS in a DBI Driver
7

SYNOPSIS

9         use DBI;
10         my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");
11

DESCRIPTION

13       SQLite is a public domain file-based relational database engine that
14       you can find at <http://www.sqlite.org/>.
15
16       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
21       SQLite supports the following features:
22
23       Implements a large subset of SQL92
24           See <http://www.sqlite.org/lang.html> for details.
25
26       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
30       Atomic commit and rollback
31           Yes, DBD::SQLite is small and light, but it supports full
32           transactions!
33
34       Extensible
35           User-defined aggregate or regular functions can be registered with
36           the SQL parser.
37
38       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

SQLITE VERSION

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
50       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
55       You can also find how the library is compiled by calling
56       "DBD::SQLite::compile_options()" (see below).
57

NOTABLE DIFFERENCES FROM OTHER DRIVERS

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
64         my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");
65
66       The file is opened in read/write mode, and will be created if it does
67       not exist yet.
68
69       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
73       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
78       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
84       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
88       As of 1.41_01, you can pass URI filename (see
89       <http://www.sqlite.org/uri.html>) as well for finer control:
90
91         my $dbh = DBI->connect("dbi:SQLite:uri=file:$path_to_dbfile?mode=rwc");
92
93       Note that this is not for remote SQLite database connection. You can
94       only connect to a local database.
95
96   Read-Only Database
97       You can set sqlite_open_flags (only) when you connect to a database:
98
99         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
104       See <http://www.sqlite.org/c3ref/open.html> for details.
105
106       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
112   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
116       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
123             ($fh, $filename) = tempfile($template, EXLOCK => 0);
124
125       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
131       (The above is quoted from the pod of File::Temp.)
132
133       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
136   DBD::SQLite and fork()
137       Follow the advice in the SQLite FAQ (<https://sqlite.org/faq.html>).
138
139           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
143       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
147       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
152       If you need a higher level of concurrency than SQLite supports,
153       consider using other client/server database engines.
154
155   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
159         dbish dbi:SQLite:foo.db
160
161       On the command line to access the file foo.db.
162
163       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
167   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
174         use DBI qw(:sql_types);
175         my $dbh = DBI->connect("dbi:SQLite:dbfile","","");
176
177         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
182       And then retrieval just works:
183
184         $sth = $dbh->prepare("SELECT * FROM mytable WHERE id = 1");
185         $sth->execute();
186         my $row = $sth->fetch;
187         my $blobo = $row->[1];
188
189         # now $blobo == $blob
190
191   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
195         my $sth = $dbh->prepare(q{
196           SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
197         });
198         $sth->execute(5);
199
200       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
204         SELECT bar FROM foo GROUP BY bar HAVING count(*) > "5";
205
206       There are three workarounds for this.
207
208       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
212             use DBI qw(:sql_types);  # Don't forget this
213
214             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
220       Add zero to make it a number
221           This is somewhat weird, but works anyway.
222
223             my $sth = $dbh->prepare(q{
224               SELECT bar FROM foo GROUP BY bar HAVING count(*) > (? + 0);
225             });
226             $sth->execute(5);
227
228       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
232             $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
238           You can set it to true when you connect to a database.
239
240             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
246           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
253           As of 1.41_04, "sqlite_see_if_its_a_number" works only for bind
254           values with no explicit type.
255
256             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
267   Placeholders
268       SQLite supports several placeholder expressions, including "?"  and
269       ":AAAA". Consult the DBI and SQLite documentation for details.
270
271       <http://www.sqlite.org/lang_expr.html#varparam>
272
273       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
277         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
282   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
288       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
292           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
298           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
306           To fix this, You need to issue "PRAGMA journal_mode = DELETE" (or
307           "TRUNCATE") beforehand, or install a newer version of "sqlite3".
308
309       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
315       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
320           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
325       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
330       See <http://www.sqlite.org/pragma.html> for more details.
331
332   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
341         $dbh->do("PRAGMA foreign_keys = ON");
342
343       And you can explicitly disable the feature whenever you like by turning
344       the pragma off:
345
346         $dbh->do("PRAGMA foreign_keys = OFF");
347
348       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
356       See <http://www.sqlite.org/foreignkeys.html> for details.
357
358   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
362       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
370             $dbh->{AutoCommit} = 1;
371
372             $dbh->begin_work; # or $dbh->do('BEGIN TRANSACTION');
373
374             # $dbh->{AutoCommit} is turned off temporarily during a transaction;
375
376             $dbh->commit; # or $dbh->do('COMMIT');
377
378             # $dbh->{AutoCommit} is turned on again;
379
380       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
389             $dbh->{AutoCommit} = 0;
390
391             # $dbh->do('BEGIN TRANSACTION') is not necessary, but possible
392
393             ...
394
395             $dbh->commit; # or $dbh->do('COMMIT');
396
397             # $dbh->{AutoCommit} stays intact;
398
399             $dbh->{AutoCommit} = 1;  # ends the transactional mode
400
401       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
405   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
415       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
419         my $dbh = DBI->connect("dbi:SQLite::memory:", "", "", {
420           sqlite_use_immediate_transaction => 0,
421         });
422
423       Or, issue a "BEGIN" statement explicitly each time you begin a
424       transaction.
425
426       See <http://sqlite.org/lockingv3.html> for locking details.
427
428   "$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
434       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
438         $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
454   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
460       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
470   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
476       Queries like count(*) and avg(bytes) took fractions of a second to
477       return, but what surprised him most of all was:
478
479         SELECT url, count(*) as count
480         FROM access_log
481         GROUP BY url
482         ORDER BY count desc
483         LIMIT 20
484
485       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
489       Oh yeah, and that was with no indexes on the table, on a 400MHz PIII.
490
491       For best performance be sure to tune your hdparm settings if you are
492       using linux. Also you might want to set:
493
494         PRAGMA synchronous = OFF
495
496       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
500       The memory usage of SQLite can also be tuned using the cache_size
501       pragma.
502
503         $dbh->do("PRAGMA cache_size = 800000");
504
505       The above will allocate 800M for DB cache; the default is 2M.  Your
506       sweet spot probably lies somewhere in between.
507

DRIVER PRIVATE ATTRIBUTES

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
514       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
521           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
528             use DBI qw(:sql_types);
529             $dbh->{sqlite_unicode} = 1;
530             my $sth = $dbh->prepare("INSERT INTO mytable (blobcolumn) VALUES (?)");
531
532             # Binary_data will be stored as is.
533             $sth->bind_param(1, $binary_data, SQL_BLOB);
534
535           Defining the column type as "BLOB" in the DDL is not sufficient.
536
537           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
542       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
547       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
552           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
556       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
561       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
565   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

METHODS

572       See also to the DBI documentation for the details of other common
573       methods.
574
575   table_info
576         $sth = $dbh->table_info(undef, $schema, $table, $type, \%attr);
577
578       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
586       The following fields are returned:
587
588       TABLE_CAT: Always NULL, as SQLite does not have the concept of
589       catalogs.
590
591       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
596       TABLE_NAME: The name of the table or view.
597
598       TABLE_TYPE: The type of object returned. Will be one of 'TABLE',
599       'VIEW', 'LOCAL TEMPORARY' or 'SYSTEM TABLE'.
600
601   primary_key, primary_key_info
602         @names = $dbh->primary_key(undef, $schema, $table);
603         $sth   = $dbh->primary_key_info(undef, $schema, $table, \%attr);
604
605       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
611   foreign_key_info
612         $sth = $dbh->foreign_key_info(undef, $pk_schema, $pk_table,
613                                       undef, $fk_schema, $fk_table);
614
615       Returns information about foreign key constraints, as specified in
616       "foreign_key_info" in DBI, but with some limitations :
617
618       ·   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
622       The following nonempty fields are returned :
623
624       PKTABLE_NAME: The primary (unique) key table identifier.
625
626       PKCOLUMN_NAME: The primary (unique) key column identifier.
627
628       FKTABLE_NAME: The foreign key table identifier.
629
630       FKCOLUMN_NAME: The foreign key column identifier.
631
632       KEY_SEQ: The column sequence number (starting with 1), when several
633       columns belong to a same constraint.
634
635       UPDATE_RULE: The referential action for the UPDATE rule.  The following
636       codes are defined:
637
638         CASCADE              0
639         RESTRICT             1
640         SET NULL             2
641         NO ACTION            3
642         SET DEFAULT          4
643
644       Default is 3 ('NO ACTION').
645
646       DELETE_RULE: The referential action for the DELETE rule.  The codes are
647       the same as for UPDATE_RULE.
648
649       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
654       UNIQUE_OR_PRIMARY: Whether the column is primary or unique.
655
656       Note: foreign key support in SQLite must be explicitly turned on
657       through a "PRAGMA" command; see "Foreign keys" earlier in this manual.
658
659   statistics_info
660         $sth = $dbh->statistics_info(undef, $schema, $table,
661                                       $unique_only, $quick);
662
663       Returns information about a table and it's indexes, as specified in
664       "statistics_info" in DBI, but with some limitations :
665
666       ·   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
670       The following nonempty fields are returned :
671
672       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
676       TABLE_NAME: The name of the table
677
678       NON_UNIQUE: Contains 0 for unique indexes, 1 for non-unique indexes
679
680       INDEX_NAME: The name of the index
681
682       TYPE: SQLite uses 'btree' for all it's indexes
683
684       ORDINAL_POSITION: Column sequence number (starting with 1).
685
686       COLUMN_NAME: The name of the column
687
688   ping
689         my $bool = $dbh->ping;
690
691       returns true if the database file exists (or the database is in-
692       memory), and the database connection is active.
693

DRIVER PRIVATE METHODS

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
702         $dbh->func( ..., "(method name without sqlite_ prefix)" );
703
704       Exception: "sqlite_trace" should always be called as is, even with
705       "func()" method (to avoid conflict with DBI's trace() method).
706
707         $dbh->func( ..., "sqlite_trace");
708
709   $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
715       Generally you should not be using this method. Use the DBI
716       last_insert_id method instead. The usage of this is:
717
718         $h->last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ])
719
720       Running "$h->last_insert_id("","","","")" is the equivalent of running
721       "$dbh->sqlite_last_insert_rowid()" directly.
722
723   $dbh->sqlite_db_filename()
724       Retrieve the current (main) database filename. If the database is in-
725       memory or temporary, this returns "undef".
726
727   $dbh->sqlite_busy_timeout()
728       Retrieve the current busy timeout.
729
730   $dbh->sqlite_busy_timeout( $ms )
731       Set the current busy timeout. The timeout is in milliseconds.
732
733   $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
737       $name
738           The name of the function. This is the name of the function as it
739           will be used from SQL.
740
741       $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
745       $code_ref
746           This should be a reference to the function's implementation.
747
748       $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
756       For example, here is how to define a now() function which returns the
757       current number of seconds since the epoch:
758
759         $dbh->sqlite_create_function( 'now', 0, sub { return time } );
760
761       After this, it could be used from SQL as:
762
763         INSERT INTO mytable ( now() );
764
765       REGEXP function
766
767       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
773         SELECT * from table WHERE column REGEXP '\bA\w+'
774
775       If you want case-insensitive searching, use perl regex flags, like this
776       :
777
778         SELECT * from table WHERE column REGEXP '(?i:\bA\w+)'
779
780       The default REGEXP implementation can be overridden through the
781       "create_function" API described above.
782
783       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
786   $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
792       The method's parameters are:
793
794       $name
795           The name of the function exposed to SQL.
796
797       $code_ref
798           Reference to the function's implementation.  The driver will check
799           that this is a proper sorting function.
800
801   $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
806         $code_ref->($dbh, $collation_name)
807
808       and should register the desired collation using
809       "sqlite_create_collation".
810
811       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
816   $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
820       $name
821           The name of the aggregate function, this is the name under which
822           the function will be available from SQL.
823
824       $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
829       $pkg
830           This is the package which implements the aggregator interface.
831
832       $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
840       The aggregator interface consists of defining three methods:
841
842       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
848       step(@_)
849           This method will be called once for each row in the aggregate.
850
851       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
857       Here is a simple aggregate function which returns the variance (example
858       adapted from pysqlite):
859
860         package variance;
861
862         sub new { bless [], shift; }
863
864         sub step {
865             my ( $self, $value ) = @_;
866
867             push @$self, $value;
868         }
869
870         sub finalize {
871             my $self = $_[0];
872
873             my $n = @$self;
874
875             # Variance is NULL unless there is more than one row
876             return undef unless $n || $n == 1;
877
878             my $mu = 0;
879             foreach my $v ( @$self ) {
880                 $mu += $v;
881             }
882             $mu /= $n;
883
884             my $sigma = 0;
885             foreach my $v ( @$self ) {
886                 $sigma += ($v - $mu)**2;
887             }
888             $sigma = $sigma / ($n - 1);
889
890             return $sigma;
891         }
892
893         $dbh->sqlite_create_aggregate( "variance", 1, 'variance' );
894
895       The aggregate function can then be used as:
896
897         SELECT group_name, variance(score)
898         FROM results
899         GROUP BY group_name;
900
901       For more examples, see the DBD::SQLite::Cookbook.
902
903   $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
907       An example use for this interface is to keep a GUI updated during a
908       large query. The parameters are:
909
910       $n_opcodes
911           The progress handler is invoked once for every $n_opcodes virtual
912           machine opcodes in SQLite.
913
914       $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
919           Set this argument to "undef" if you want to unregister a previous
920           progress handler.
921
922   $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
929       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
934   $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
941   $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
948       The callback will be called as
949
950         $code_ref->($action_code, $database, $table, $rowid)
951
952       where
953
954       $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
959       $database
960           is the name of the database containing the affected row;
961
962       $table
963           is the name of the table containing the affected row;
964
965       $rowid
966           is the unique 64-bit signed integer key of the affected row within
967           that table.
968
969   $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
979       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
989       The callback will be called as
990
991         $code_ref->($action_code, $string1, $string2, $database, $trigger_or_view)
992
993       where
994
995       $action_code
996           is an integer that specifies what action is being authorized (see
997           "Action Codes").
998
999       $string1, $string2
1000           are strings that depend on the action code (see "Action Codes").
1001
1002       $database
1003           is the name of the database ("main", "temp", etc.) if applicable.
1004
1005       $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
1010   $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
1017   $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
1022   $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
1026         $dbh->sqlite_enable_load_extension(1);
1027         $sth = $dbh->prepare("select load_extension('libsqlitefunctions.so')")
1028         or die "Cannot prepare: " . $dbh->errstr();
1029
1030   $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
1038   $dbh->sqlite_trace( $code_ref )
1039       This method registers a trace callback to be invoked whenever SQL
1040       statements are being run.
1041
1042       The callback will be called as
1043
1044         $code_ref->($statement)
1045
1046       where
1047
1048       $statement
1049           is a UTF-8 rendering of the SQL statement text as the statement
1050           first begins executing.
1051
1052       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
1056       See also "TRACING" in DBI for better tracing options.
1057
1058   $dbh->sqlite_profile( $code_ref )
1059       This method registers a profile callback to be invoked whenever a SQL
1060       statement finishes.
1061
1062       The callback will be called as
1063
1064         $code_ref->($statement, $elapsed_time)
1065
1066       where
1067
1068       $statement
1069           is the original statement text (without bind parameters).
1070
1071       $elapsed_time
1072           is an estimate of wall-clock time of how long that statement took
1073           to run (in milliseconds).
1074
1075       This method is considered experimental and is subject to change in
1076       future versions of SQLite.
1077
1078       See also DBI::Profile for better profiling options.
1079
1080   $dbh->sqlite_table_column_metadata( $dbname, $tablename, $columnname )
1081       is for internal use only.
1082
1083   $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
1089   $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
1095         my $status = $sth->sqlite_st_status();
1096         my $cur = $status->{fullscan_step};
1097
1098       You may also pass 0 as an argument to reset the status.
1099
1100   $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

DRIVER FUNCTIONS

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
1112   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
1118         my $status = DBD::SQLite::sqlite_status();
1119         my $cur  = $status->{memory_used}{current};
1120         my $high = $status->{memory_used}{highwater};
1121
1122       You may also pass 0 as an argument to reset the status.
1123
1124   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

DRIVER CONSTANTS

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
1139         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
1146       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
1150   Authorizer Return Codes
1151         OK
1152         DENY
1153         IGNORE
1154
1155   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
1164         # 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

COLLATION FUNCTIONS

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
1207   Builtin collation sequences
1208       The following collation sequences are builtin within SQLite :
1209
1210       BINARY
1211           Compares string data using memcmp(), regardless of text encoding.
1212
1213       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
1220       RTRIM
1221           The same as binary, except that trailing space characters are
1222           ignored.
1223
1224       In addition, "DBD::SQLite" automatically installs the following
1225       collation sequences :
1226
1227       perl
1228           corresponds to the Perl "cmp" operator
1229
1230       perllocale
1231           Perl "cmp" operator, in a context where "use locale" is activated.
1232
1233   Usage
1234       You can write for example
1235
1236         CREATE TABLE foo(
1237             txt1 COLLATE perl,
1238             txt2 COLLATE perllocale,
1239             txt3 COLLATE nocase
1240         )
1241
1242       or
1243
1244         SELECT * FROM foo ORDER BY name COLLATE perllocale
1245
1246   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
1254         my $dbh = DBI->connect(
1255             "dbi:SQLite:dbname=foo", "", "",
1256             {
1257                 RaiseError     => 1,
1258                 sqlite_unicode => 1,
1259             }
1260         );
1261
1262   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
1267       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
1275         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
1286       The builtin "perl" or "perllocale" collations are predefined in that
1287       same hash.
1288
1289       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
1296       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
1311       Explanations for using this feature are provided in a separate
1312       document: see DBD::SQLite::Fulltext_search.
1313

R* TREE SUPPORT

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
1321         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
1327       then query which buildings overlap or are contained within a specified
1328       region:
1329
1330         # 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
1336         # ... 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
1342         my $contained = $dbh->selectcol_arrayref($contained_sql,undef,
1343                               $minLong, $maxLong, $minLat, $maxLat);
1344
1345         my $overlapping = $dbh->selectcol_arrayref($overlap_sql,undef,
1346                               $minLong, $maxLong, $minLat, $maxLat);
1347
1348       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

VIRTUAL TABLES IMPLEMENTED IN PERL

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
1359       "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
1365       ·   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
1370       ·   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
1375       Other Perl virtual tables may also be published separately on CPAN.
1376

TO DO

1378       The following items remain to be done.
1379
1380   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
1385   Stream API for Blobs
1386       Reading/writing into blobs using "sqlite2_blob_open" /
1387       "sqlite2_blob_close".
1388
1389   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

SUPPORT

1396       Bugs should be reported via the CPAN bug tracker at
1397
1398       <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
1399
1400       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
1404       The master repository is on GitHub:
1405
1406       <https://github.com/DBD-SQLite/DBD-SQLite>.
1407
1408       We also have a mailing list:
1409
1410       <http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite>
1411

AUTHORS

1413       Matt Sergeant <matt@sergeant.org>
1414
1415       Francis J. Lacoste <flacoste@logreport.org>
1416
1417       Wolfgang Sourdeau <wolfgang@logreport.org>
1418
1419       Adam Kennedy <adamk@cpan.org>
1420
1421       Max Maischein <corion@cpan.org>
1422
1423       Laurent Dami <dami@cpan.org>
1424
1425       Kenichi Ishigaki <ishigaki@cpan.org>
1426
1428       The bundled SQLite code in this distribution is Public Domain.
1429
1430       DBD::SQLite is copyright 2002 - 2007 Matt Sergeant.
1431
1432       Some parts copyright 2008 Francis J. Lacoste.
1433
1434       Some parts copyright 2008 Wolfgang Sourdeau.
1435
1436       Some parts copyright 2008 - 2013 Adam Kennedy.
1437
1438       Some parts copyright 2009 - 2013 Kenichi Ishigaki.
1439
1440       Some parts derived from DBD::SQLite::Amalgamation copyright 2008 Audrey
1441       Tang.
1442
1443       This program is free software; you can redistribute it and/or modify it
1444       under the same terms as Perl itself.
1445
1446       The full text of the license can be found in the LICENSE file included
1447       with this module.
1448
1449
1450
1451perl v5.26.3                      2019-05-11                    DBD::SQLite(3)
Impressum