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 <https://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 <https://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.26.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       <https://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 <https://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 four 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       Use SQL cast() function
229           This is more explicit way to do the above.
230
231             my $sth = $dbh->prepare(q{
232               SELECT bar FROM foo GROUP BY bar HAVING count(*) > cast(? as integer);
233             });
234             $sth->execute(5);
235
236       Set "sqlite_see_if_its_a_number" database handle attribute
237           As of version 1.32_02, you can use "sqlite_see_if_its_a_number" to
238           let DBD::SQLite to see if the bind values are numbers or not.
239
240             $dbh->{sqlite_see_if_its_a_number} = 1;
241             my $sth = $dbh->prepare(q{
242               SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
243             });
244             $sth->execute(5);
245
246           You can set it to true when you connect to a database.
247
248             my $dbh = DBI->connect('dbi:SQLite:foo', undef, undef, {
249               AutoCommit => 1,
250               RaiseError => 1,
251               sqlite_see_if_its_a_number => 1,
252             });
253
254           This is the most straightforward solution, but as noted above,
255           existing data in your databases created by DBD::SQLite have not
256           always been stored as numbers, so this *might* cause other obscure
257           problems. Use this sparingly when you handle existing databases.
258           If you handle databases created by other tools like native
259           "sqlite3" command line tool, this attribute would help you.
260
261           As of 1.41_04, "sqlite_see_if_its_a_number" works only for bind
262           values with no explicit type.
263
264             my $dbh = DBI->connect('dbi:SQLite:foo', undef, undef, {
265               AutoCommit => 1,
266               RaiseError => 1,
267               sqlite_see_if_its_a_number => 1,
268             });
269             my $sth = $dbh->prepare('INSERT INTO foo VALUES(?)');
270             # '1.230' will be inserted as a text, instead of 1.23 as a number,
271             # even though sqlite_see_if_its_a_number is set.
272             $sth->bind_param(1, '1.230', SQL_VARCHAR);
273             $sth->execute;
274
275   Placeholders
276       SQLite supports several placeholder expressions, including "?"  and
277       ":AAAA". Consult the DBI and SQLite documentation for details.
278
279       <https://www.sqlite.org/lang_expr.html#varparam>
280
281       Note that a question mark actually means a next unused (numbered)
282       placeholder. You're advised not to use it with other (numbered or
283       named) placeholders to avoid confusion.
284
285         my $sth = $dbh->prepare(
286           'update TABLE set a=?1 where b=?2 and a IS NOT ?1'
287         );
288         $sth->execute(1, 2);
289
290   Pragma
291       SQLite has a set of "Pragma"s to modify its operation or to query for
292       its internal data. These are specific to SQLite and are not likely to
293       work with other DBD libraries, but you may find some of these are quite
294       useful, including:
295
296       journal_mode
297           You can use this pragma to change the journal mode for SQLite
298           databases, maybe for better performance, or for compatibility.
299
300           Its default mode is "DELETE", which means SQLite uses a rollback
301           journal to implement transactions, and the journal is deleted at
302           the conclusion of each transaction. If you use "TRUNCATE" instead
303           of "DELETE", the journal will be truncated, which is usually much
304           faster.
305
306           A "WAL" (write-ahead log) mode is introduced as of SQLite 3.7.0.
307           This mode is persistent, and it stays in effect even after closing
308           and reopening the database. In other words, once the "WAL" mode is
309           set in an application or in a test script, the database becomes
310           inaccessible by older clients. This tends to be an issue when you
311           use a system "sqlite3" executable under a conservative operating
312           system.
313
314           To fix this, You need to issue "PRAGMA journal_mode = DELETE" (or
315           "TRUNCATE") beforehand, or install a newer version of "sqlite3".
316
317       legacy_file_format
318           If you happen to need to create a SQLite database that will also be
319           accessed by a very old SQLite client (prior to 3.3.0 released in
320           Jan. 2006), you need to set this pragma to ON before you create a
321           database.
322
323       reverse_unordered_selects
324           You can set this pragma to ON to reverse the order of results of
325           SELECT statements without an ORDER BY clause so that you can see if
326           applications are making invalid assumptions about the result order.
327
328           Note that SQLite 3.7.15 (bundled with DBD::SQLite 1.38_02) enhanced
329           its query optimizer and the order of results of a SELECT statement
330           without an ORDER BY clause may be different from the one of the
331           previous versions.
332
333       synchronous
334           You can set set this pragma to OFF to make some of the operations
335           in SQLite faster with a possible risk of database corruption in the
336           worst case. See also "Performance" section below.
337
338       See <https://www.sqlite.org/pragma.html> for more details.
339
340   Foreign Keys
341       SQLite has started supporting foreign key constraints since 3.6.19
342       (released on Oct 14, 2009; bundled in DBD::SQLite 1.26_05).  To be
343       exact, SQLite has long been able to parse a schema with foreign keys,
344       but the constraints has not been enforced. Now you can issue a
345       "foreign_keys" pragma to enable this feature and enforce the
346       constraints, preferably as soon as you connect to a database and you're
347       not in a transaction:
348
349         $dbh->do("PRAGMA foreign_keys = ON");
350
351       And you can explicitly disable the feature whenever you like by turning
352       the pragma off:
353
354         $dbh->do("PRAGMA foreign_keys = OFF");
355
356       As of this writing, this feature is disabled by default by the SQLite
357       team, and by us, to secure backward compatibility, as this feature may
358       break your applications, and actually broke some for us. If you have
359       used a schema with foreign key constraints but haven't cared them much
360       and supposed they're always ignored for SQLite, be prepared, and please
361       do extensive testing to ensure that your applications will continue to
362       work when the foreign keys support is enabled by default.
363
364       See <https://www.sqlite.org/foreignkeys.html> for details.
365
366   Transactions
367       DBI/DBD::SQLite's transactions may be a bit confusing. They behave
368       differently according to the status of the "AutoCommit" flag:
369
370       When the AutoCommit flag is on
371           You're supposed to always use the auto-commit mode, except you
372           explicitly begin a transaction, and when the transaction ended,
373           you're supposed to go back to the auto-commit mode. To begin a
374           transaction, call "begin_work" method, or issue a "BEGIN"
375           statement. To end it, call "commit/rollback" methods, or issue the
376           corresponding statements.
377
378             $dbh->{AutoCommit} = 1;
379
380             $dbh->begin_work; # or $dbh->do('BEGIN TRANSACTION');
381
382             # $dbh->{AutoCommit} is turned off temporarily during a transaction;
383
384             $dbh->commit; # or $dbh->do('COMMIT');
385
386             # $dbh->{AutoCommit} is turned on again;
387
388       When the AutoCommit flag is off
389           You're supposed to always use the transactional mode, until you
390           explicitly turn on the AutoCommit flag. You can explicitly issue a
391           "BEGIN" statement (only when an actual transaction has not begun
392           yet) but you're not allowed to call "begin_work" method (if you
393           don't issue a "BEGIN", it will be issued internally).  You can
394           commit or roll it back freely. Another transaction will
395           automatically begin if you execute another statement.
396
397             $dbh->{AutoCommit} = 0;
398
399             # $dbh->do('BEGIN TRANSACTION') is not necessary, but possible
400
401             ...
402
403             $dbh->commit; # or $dbh->do('COMMIT');
404
405             # $dbh->{AutoCommit} stays intact;
406
407             $dbh->{AutoCommit} = 1;  # ends the transactional mode
408
409       This "AutoCommit" mode is independent from the autocommit mode of the
410       internal SQLite library, which always begins by a "BEGIN" statement,
411       and ends by a "COMMIT" or a "ROLLBACK".
412
413   Transaction and Database Locking
414       The default transaction behavior of SQLite is "deferred", that means,
415       locks are not acquired until the first read or write operation, and
416       thus it is possible that another thread or process could create a
417       separate transaction and write to the database after the "BEGIN" on the
418       current thread has executed, and eventually cause a "deadlock". To
419       avoid this, DBD::SQLite internally issues a "BEGIN IMMEDIATE" if you
420       begin a transaction by calling "begin_work" or by turning off
421       "AutoCommit" (since 1.38_01).
422
423       If you really need to turn off this feature for some reasons, set
424       "sqlite_use_immediate_transaction" database handle attribute to false,
425       and the default "deferred" transaction will be used.
426
427         my $dbh = DBI->connect("dbi:SQLite::memory:", "", "", {
428           sqlite_use_immediate_transaction => 0,
429         });
430
431       Or, issue a "BEGIN" statement explicitly each time you begin a
432       transaction.
433
434       See <http://sqlite.org/lockingv3.html> for locking details.
435
436   "$sth->finish" and Transaction Rollback
437       As the DBI doc says, you almost certainly do not need to call "finish"
438       in DBI method if you fetch all rows (probably in a loop).  However,
439       there are several exceptions to this rule, and rolling-back of an
440       unfinished "SELECT" statement is one of such exceptional cases.
441
442       SQLite prohibits "ROLLBACK" of unfinished "SELECT" statements in a
443       transaction (See <http://sqlite.org/lang_transaction.html> for
444       details). So you need to call "finish" before you issue a rollback.
445
446         $sth = $dbh->prepare("SELECT * FROM t");
447         $dbh->begin_work;
448         eval {
449             $sth->execute;
450             $row = $sth->fetch;
451             ...
452             die "For some reason";
453             ...
454         };
455         if($@) {
456            $sth->finish;  # You need this for SQLite
457            $dbh->rollback;
458         } else {
459            $dbh->commit;
460         }
461
462   Processing Multiple Statements At A Time
463       DBI's statement handle is not supposed to process multiple statements
464       at a time. So if you pass a string that contains multiple statements (a
465       "dump") to a statement handle (via "prepare" or "do"), DBD::SQLite only
466       processes the first statement, and discards the rest.
467
468       If you need to process multiple statements at a time, set a
469       "sqlite_allow_multiple_statements" attribute of a database handle to
470       true when you connect to a database, and "do" method takes care of the
471       rest (since 1.30_01, and without creating DBI's statement handles
472       internally since 1.47_01). If you do need to use "prepare" or
473       "prepare_cached" (which I don't recommend in this case, because
474       typically there's no placeholder nor reusable part in a dump), you can
475       look at "$sth->{sqlite_unprepared_statements}" to retrieve what's left,
476       though it usually contains nothing but white spaces.
477
478   TYPE statement attribute
479       Because of historical reasons, DBD::SQLite's "TYPE" statement handle
480       attribute returns an array ref of string values, contrary to the DBI
481       specification. This value is also less useful for SQLite users because
482       SQLite uses dynamic type system (that means, the datatype of a value is
483       associated with the value itself, not with its container).
484
485       As of version 1.61_02, if you set "sqlite_prefer_numeric_type" database
486       handle attribute to true, "TYPE" statement handle attribute returns an
487       array of integer, as an experiment.
488
489   Performance
490       SQLite is fast, very fast. Matt processed his 72MB log file with it,
491       inserting the data (400,000+ rows) by using transactions and only
492       committing every 1000 rows (otherwise the insertion is quite slow), and
493       then performing queries on the data.
494
495       Queries like count(*) and avg(bytes) took fractions of a second to
496       return, but what surprised him most of all was:
497
498         SELECT url, count(*) as count
499         FROM access_log
500         GROUP BY url
501         ORDER BY count desc
502         LIMIT 20
503
504       To discover the top 20 hit URLs on the site (<http://axkit.org>), and
505       it returned within 2 seconds. He was seriously considering switching
506       his log analysis code to use this little speed demon!
507
508       Oh yeah, and that was with no indexes on the table, on a 400MHz PIII.
509
510       For best performance be sure to tune your hdparm settings if you are
511       using linux. Also you might want to set:
512
513         PRAGMA synchronous = OFF
514
515       Which will prevent SQLite from doing fsync's when writing (which slows
516       down non-transactional writes significantly) at the expense of some
517       peace of mind. Also try playing with the cache_size pragma.
518
519       The memory usage of SQLite can also be tuned using the cache_size
520       pragma.
521
522         $dbh->do("PRAGMA cache_size = 800000");
523
524       The above will allocate 800M for DB cache; the default is 2M.  Your
525       sweet spot probably lies somewhere in between.
526

DRIVER PRIVATE ATTRIBUTES

528   Database Handle Attributes
529       sqlite_version
530           Returns the version of the SQLite library which DBD::SQLite is
531           using, e.g., "3.26.0". Can only be read.
532
533       sqlite_unicode
534           If set to a true value, DBD::SQLite will turn the UTF-8 flag on for
535           all text strings coming out of the database (this feature is
536           currently disabled for perl < 5.8.5). For more details on the UTF-8
537           flag see perlunicode. The default is for the UTF-8 flag to be
538           turned off.
539
540           Also note that due to some bizarreness in SQLite's type system (see
541           <https://www.sqlite.org/datatype3.html>), if you want to retain
542           blob-style behavior for some columns under "$dbh->{sqlite_unicode}
543           = 1" (say, to store images in the database), you have to state so
544           explicitly using the 3-argument form of "bind_param" in DBI when
545           doing updates:
546
547             use DBI qw(:sql_types);
548             $dbh->{sqlite_unicode} = 1;
549             my $sth = $dbh->prepare("INSERT INTO mytable (blobcolumn) VALUES (?)");
550
551             # Binary_data will be stored as is.
552             $sth->bind_param(1, $binary_data, SQL_BLOB);
553
554           Defining the column type as "BLOB" in the DDL is not sufficient.
555
556           This attribute was originally named as "unicode", and renamed to
557           "sqlite_unicode" for integrity since version 1.26_06. Old "unicode"
558           attribute is still accessible but will be deprecated in the near
559           future.
560
561       sqlite_allow_multiple_statements
562           If you set this to true, "do" method will process multiple
563           statements at one go. This may be handy, but with performance
564           penalty. See above for details.
565
566       sqlite_use_immediate_transaction
567           If you set this to true, DBD::SQLite tries to issue a "begin
568           immediate transaction" (instead of "begin transaction") when
569           necessary. See above for details.
570
571           As of version 1.38_01, this attribute is set to true by default.
572           If you really need to use "deferred" transactions for some reasons,
573           set this to false explicitly.
574
575       sqlite_see_if_its_a_number
576           If you set this to true, DBD::SQLite tries to see if the bind
577           values are number or not, and does not quote if they are numbers.
578           See above for details.
579
580       sqlite_extended_result_codes
581           If set to true, DBD::SQLite uses extended result codes where
582           appropriate (see <https://www.sqlite.org/rescode.html>).
583
584       sqlite_defensive
585           If set to true, language features that allow ordinary SQL to
586           deliberately corrupt the database file are prohibited.
587
588   Statement Handle Attributes
589       sqlite_unprepared_statements
590           Returns an unprepared part of the statement you pass to "prepare".
591           Typically this contains nothing but white spaces after a semicolon.
592           See above for details.
593

METHODS

595       See also to the DBI documentation for the details of other common
596       methods.
597
598   table_info
599         $sth = $dbh->table_info(undef, $schema, $table, $type, \%attr);
600
601       Returns all tables and schemas (databases) as specified in "table_info"
602       in DBI.  The schema and table arguments will do a "LIKE" search. You
603       can specify an ESCAPE character by including an 'Escape' attribute in
604       \%attr. The $type argument accepts a comma separated list of the
605       following types 'TABLE', 'VIEW', 'LOCAL TEMPORARY' and 'SYSTEM TABLE'
606       (by default all are returned).  Note that a statement handle is
607       returned, and not a direct list of tables.
608
609       The following fields are returned:
610
611       TABLE_CAT: Always NULL, as SQLite does not have the concept of
612       catalogs.
613
614       TABLE_SCHEM: The name of the schema (database) that the table or view
615       is in. The default schema is 'main', temporary tables are in 'temp' and
616       other databases will be in the name given when the database was
617       attached.
618
619       TABLE_NAME: The name of the table or view.
620
621       TABLE_TYPE: The type of object returned. Will be one of 'TABLE',
622       'VIEW', 'LOCAL TEMPORARY' or 'SYSTEM TABLE'.
623
624   primary_key, primary_key_info
625         @names = $dbh->primary_key(undef, $schema, $table);
626         $sth   = $dbh->primary_key_info(undef, $schema, $table, \%attr);
627
628       You can retrieve primary key names or more detailed information.  As
629       noted above, SQLite does not have the concept of catalogs, so the first
630       argument of the methods is usually "undef", and you'll usually set
631       "undef" for the second one (unless you want to know the primary keys of
632       temporary tables).
633
634   foreign_key_info
635         $sth = $dbh->foreign_key_info(undef, $pk_schema, $pk_table,
636                                       undef, $fk_schema, $fk_table);
637
638       Returns information about foreign key constraints, as specified in
639       "foreign_key_info" in DBI, but with some limitations :
640
641       ·   information in rows returned by the $sth is incomplete with respect
642           to the "foreign_key_info" in DBI specification. All requested
643           fields are present, but the content is "undef" for some of them.
644
645       The following nonempty fields are returned :
646
647       PKTABLE_NAME: The primary (unique) key table identifier.
648
649       PKCOLUMN_NAME: The primary (unique) key column identifier.
650
651       FKTABLE_NAME: The foreign key table identifier.
652
653       FKCOLUMN_NAME: The foreign key column identifier.
654
655       KEY_SEQ: The column sequence number (starting with 1), when several
656       columns belong to a same constraint.
657
658       UPDATE_RULE: The referential action for the UPDATE rule.  The following
659       codes are defined:
660
661         CASCADE              0
662         RESTRICT             1
663         SET NULL             2
664         NO ACTION            3
665         SET DEFAULT          4
666
667       Default is 3 ('NO ACTION').
668
669       DELETE_RULE: The referential action for the DELETE rule.  The codes are
670       the same as for UPDATE_RULE.
671
672       DEFERRABILITY: The following codes are defined:
673
674         INITIALLY DEFERRED   5
675         INITIALLY IMMEDIATE  6
676         NOT DEFERRABLE       7
677
678       UNIQUE_OR_PRIMARY: Whether the column is primary or unique.
679
680       Note: foreign key support in SQLite must be explicitly turned on
681       through a "PRAGMA" command; see "Foreign keys" earlier in this manual.
682
683   statistics_info
684         $sth = $dbh->statistics_info(undef, $schema, $table,
685                                       $unique_only, $quick);
686
687       Returns information about a table and it's indexes, as specified in
688       "statistics_info" in DBI, but with some limitations :
689
690       ·   information in rows returned by the $sth is incomplete with respect
691           to the "statistics_info" in DBI specification. All requested fields
692           are present, but the content is "undef" for some of them.
693
694       The following nonempty fields are returned :
695
696       TABLE_SCHEM: The name of the schema (database) that the table is in.
697       The default schema is 'main', temporary tables are in 'temp' and other
698       databases will be in the name given when the database was attached.
699
700       TABLE_NAME: The name of the table
701
702       NON_UNIQUE: Contains 0 for unique indexes, 1 for non-unique indexes
703
704       INDEX_NAME: The name of the index
705
706       TYPE: SQLite uses 'btree' for all it's indexes
707
708       ORDINAL_POSITION: Column sequence number (starting with 1).
709
710       COLUMN_NAME: The name of the column
711
712   ping
713         my $bool = $dbh->ping;
714
715       returns true if the database file exists (or the database is in-
716       memory), and the database connection is active.
717

DRIVER PRIVATE METHODS

719       The following methods can be called via the func() method with a little
720       tweak, but the use of func() method is now discouraged by the DBI
721       author for various reasons (see DBI's document
722       <https://metacpan.org/pod/DBI::DBD#Using-install_method()-to-expose-driver-private-methods>
723       for details). So, if you're using DBI >= 1.608, use these "sqlite_"
724       methods. If you need to use an older DBI, you can call these like this:
725
726         $dbh->func( ..., "(method name without sqlite_ prefix)" );
727
728       Exception: "sqlite_trace" should always be called as is, even with
729       "func()" method (to avoid conflict with DBI's trace() method).
730
731         $dbh->func( ..., "sqlite_trace");
732
733   $dbh->sqlite_last_insert_rowid()
734       This method returns the last inserted rowid. If you specify an INTEGER
735       PRIMARY KEY as the first column in your table, that is the column that
736       is returned.  Otherwise, it is the hidden ROWID column. See the SQLite
737       docs for details.
738
739       Generally you should not be using this method. Use the DBI
740       last_insert_id method instead. The usage of this is:
741
742         $h->last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ])
743
744       Running "$h->last_insert_id("","","","")" is the equivalent of running
745       "$dbh->sqlite_last_insert_rowid()" directly.
746
747   $dbh->sqlite_db_filename()
748       Retrieve the current (main) database filename. If the database is in-
749       memory or temporary, this returns "undef".
750
751   $dbh->sqlite_busy_timeout()
752       Retrieve the current busy timeout.
753
754   $dbh->sqlite_busy_timeout( $ms )
755       Set the current busy timeout. The timeout is in milliseconds.
756
757   $dbh->sqlite_create_function( $name, $argc, $code_ref, $flags )
758       This method will register a new function which will be usable in an SQL
759       query. The method's parameters are:
760
761       $name
762           The name of the function. This is the name of the function as it
763           will be used from SQL.
764
765       $argc
766           The number of arguments taken by the function. If this number is
767           -1, the function can take any number of arguments.
768
769       $code_ref
770           This should be a reference to the function's implementation.
771
772       $flags
773           You can optionally pass an extra flag bit to create_function, which
774           then would be ORed with SQLITE_UTF8 (default). As of 1.47_02
775           (SQLite 3.8.9), only meaning bit is SQLITE_DETERMINISTIC
776           (introduced at SQLite 3.8.3), which can make the function perform
777           better. See C API documentation at
778           <http://sqlite.org/c3ref/create_function.html> for details.
779
780       For example, here is how to define a now() function which returns the
781       current number of seconds since the epoch:
782
783         $dbh->sqlite_create_function( 'now', 0, sub { return time } );
784
785       After this, it could be used from SQL as:
786
787         INSERT INTO mytable ( now() );
788
789       REGEXP function
790
791       SQLite includes syntactic support for an infix operator 'REGEXP', but
792       without any implementation. The "DBD::SQLite" driver automatically
793       registers an implementation that performs standard perl regular
794       expression matching, using current locale. So for example you can
795       search for words starting with an 'A' with a query like
796
797         SELECT * from table WHERE column REGEXP '\bA\w+'
798
799       If you want case-insensitive searching, use perl regex flags, like this
800       :
801
802         SELECT * from table WHERE column REGEXP '(?i:\bA\w+)'
803
804       The default REGEXP implementation can be overridden through the
805       "create_function" API described above.
806
807       Note that regexp matching will not use SQLite indices, but will iterate
808       over all rows, so it could be quite costly in terms of performance.
809
810   $dbh->sqlite_create_collation( $name, $code_ref )
811       This method manually registers a new function which will be usable in
812       an SQL query as a COLLATE option for sorting. Such functions can also
813       be registered automatically on demand: see section "COLLATION
814       FUNCTIONS" below.
815
816       The method's parameters are:
817
818       $name
819           The name of the function exposed to SQL.
820
821       $code_ref
822           Reference to the function's implementation.  The driver will check
823           that this is a proper sorting function.
824
825   $dbh->sqlite_collation_needed( $code_ref )
826       This method manually registers a callback function that will be invoked
827       whenever an undefined collation sequence is required from an SQL
828       statement. The callback is invoked as
829
830         $code_ref->($dbh, $collation_name)
831
832       and should register the desired collation using
833       "sqlite_create_collation".
834
835       An initial callback is already registered by "DBD::SQLite", so for most
836       common cases it will be simpler to just add your collation sequences in
837       the %DBD::SQLite::COLLATION hash (see section "COLLATION FUNCTIONS"
838       below).
839
840   $dbh->sqlite_create_aggregate( $name, $argc, $pkg, $flags )
841       This method will register a new aggregate function which can then be
842       used from SQL. The method's parameters are:
843
844       $name
845           The name of the aggregate function, this is the name under which
846           the function will be available from SQL.
847
848       $argc
849           This is an integer which tells the SQL parser how many arguments
850           the function takes. If that number is -1, the function can take any
851           number of arguments.
852
853       $pkg
854           This is the package which implements the aggregator interface.
855
856       $flags
857           You can optionally pass an extra flag bit to create_aggregate,
858           which then would be ORed with SQLITE_UTF8 (default). As of 1.47_02
859           (SQLite 3.8.9), only meaning bit is SQLITE_DETERMINISTIC
860           (introduced at SQLite 3.8.3), which can make the function perform
861           better. See C API documentation at
862           <http://sqlite.org/c3ref/create_function.html> for details.
863
864       The aggregator interface consists of defining three methods:
865
866       new()
867           This method will be called once to create an object which should be
868           used to aggregate the rows in a particular group. The step() and
869           finalize() methods will be called upon the reference return by the
870           method.
871
872       step(@_)
873           This method will be called once for each row in the aggregate.
874
875       finalize()
876           This method will be called once all rows in the aggregate were
877           processed and it should return the aggregate function's result.
878           When there is no rows in the aggregate, finalize() will be called
879           right after new().
880
881       Here is a simple aggregate function which returns the variance (example
882       adapted from pysqlite):
883
884         package variance;
885
886         sub new { bless [], shift; }
887
888         sub step {
889             my ( $self, $value ) = @_;
890
891             push @$self, $value;
892         }
893
894         sub finalize {
895             my $self = $_[0];
896
897             my $n = @$self;
898
899             # Variance is NULL unless there is more than one row
900             return undef unless $n || $n == 1;
901
902             my $mu = 0;
903             foreach my $v ( @$self ) {
904                 $mu += $v;
905             }
906             $mu /= $n;
907
908             my $sigma = 0;
909             foreach my $v ( @$self ) {
910                 $sigma += ($v - $mu)**2;
911             }
912             $sigma = $sigma / ($n - 1);
913
914             return $sigma;
915         }
916
917         $dbh->sqlite_create_aggregate( "variance", 1, 'variance' );
918
919       The aggregate function can then be used as:
920
921         SELECT group_name, variance(score)
922         FROM results
923         GROUP BY group_name;
924
925       For more examples, see the DBD::SQLite::Cookbook.
926
927   $dbh->sqlite_progress_handler( $n_opcodes, $code_ref )
928       This method registers a handler to be invoked periodically during long
929       running calls to SQLite.
930
931       An example use for this interface is to keep a GUI updated during a
932       large query. The parameters are:
933
934       $n_opcodes
935           The progress handler is invoked once for every $n_opcodes virtual
936           machine opcodes in SQLite.
937
938       $code_ref
939           Reference to the handler subroutine.  If the progress handler
940           returns non-zero, the SQLite operation is interrupted. This feature
941           can be used to implement a "Cancel" button on a GUI dialog box.
942
943           Set this argument to "undef" if you want to unregister a previous
944           progress handler.
945
946   $dbh->sqlite_commit_hook( $code_ref )
947       This method registers a callback function to be invoked whenever a
948       transaction is committed. Any callback set by a previous call to
949       "sqlite_commit_hook" is overridden. A reference to the previous
950       callback (if any) is returned.  Registering an "undef" disables the
951       callback.
952
953       When the commit hook callback returns zero, the commit operation is
954       allowed to continue normally. If the callback returns non-zero, then
955       the commit is converted into a rollback (in that case, any attempt to
956       explicitly call "$dbh->rollback()" afterwards would yield an error).
957
958   $dbh->sqlite_rollback_hook( $code_ref )
959       This method registers a callback function to be invoked whenever a
960       transaction is rolled back. Any callback set by a previous call to
961       "sqlite_rollback_hook" is overridden. A reference to the previous
962       callback (if any) is returned.  Registering an "undef" disables the
963       callback.
964
965   $dbh->sqlite_update_hook( $code_ref )
966       This method registers a callback function to be invoked whenever a row
967       is updated, inserted or deleted. Any callback set by a previous call to
968       "sqlite_update_hook" is overridden. A reference to the previous
969       callback (if any) is returned.  Registering an "undef" disables the
970       callback.
971
972       The callback will be called as
973
974         $code_ref->($action_code, $database, $table, $rowid)
975
976       where
977
978       $action_code
979           is an integer equal to either "DBD::SQLite::INSERT",
980           "DBD::SQLite::DELETE" or "DBD::SQLite::UPDATE" (see "Action
981           Codes");
982
983       $database
984           is the name of the database containing the affected row;
985
986       $table
987           is the name of the table containing the affected row;
988
989       $rowid
990           is the unique 64-bit signed integer key of the affected row within
991           that table.
992
993   $dbh->sqlite_set_authorizer( $code_ref )
994       This method registers an authorizer callback to be invoked whenever SQL
995       statements are being compiled by the "prepare" in DBI method.  The
996       authorizer callback should return "DBD::SQLite::OK" to allow the
997       action, "DBD::SQLite::IGNORE" to disallow the specific action but allow
998       the SQL statement to continue to be compiled, or "DBD::SQLite::DENY" to
999       cause the entire SQL statement to be rejected with an error. If the
1000       authorizer callback returns any other value, then "prepare" call that
1001       triggered the authorizer will fail with an error message.
1002
1003       An authorizer is used when preparing SQL statements from an untrusted
1004       source, to ensure that the SQL statements do not try to access data
1005       they are not allowed to see, or that they do not try to execute
1006       malicious statements that damage the database. For example, an
1007       application may allow a user to enter arbitrary SQL queries for
1008       evaluation by a database. But the application does not want the user to
1009       be able to make arbitrary changes to the database. An authorizer could
1010       then be put in place while the user-entered SQL is being prepared that
1011       disallows everything except SELECT statements.
1012
1013       The callback will be called as
1014
1015         $code_ref->($action_code, $string1, $string2, $database, $trigger_or_view)
1016
1017       where
1018
1019       $action_code
1020           is an integer that specifies what action is being authorized (see
1021           "Action Codes").
1022
1023       $string1, $string2
1024           are strings that depend on the action code (see "Action Codes").
1025
1026       $database
1027           is the name of the database ("main", "temp", etc.) if applicable.
1028
1029       $trigger_or_view
1030           is the name of the inner-most trigger or view that is responsible
1031           for the access attempt, or "undef" if this access attempt is
1032           directly from top-level SQL code.
1033
1034   $dbh->sqlite_backup_from_file( $filename )
1035       This method accesses the SQLite Online Backup API, and will take a
1036       backup of the named database file, copying it to, and overwriting, your
1037       current database connection. This can be particularly handy if your
1038       current connection is to the special :memory: database, and you wish to
1039       populate it from an existing DB.
1040
1041   $dbh->sqlite_backup_to_file( $filename )
1042       This method accesses the SQLite Online Backup API, and will take a
1043       backup of the currently connected database, and write it out to the
1044       named file.
1045
1046   $dbh->sqlite_backup_from_dbh( $another_dbh )
1047       This method accesses the SQLite Online Backup API, and will take a
1048       backup of the database for the passed handle, copying it to, and
1049       overwriting, your current database connection. This can be particularly
1050       handy if your current connection is to the special :memory: database,
1051       and you wish to populate it from an existing DB.  You can use this to
1052       backup from an in-memory database to another in-memory database.
1053
1054   $dbh->sqlite_backup_to_dbh( $another_dbh )
1055       This method accesses the SQLite Online Backup API, and will take a
1056       backup of the currently connected database, and write it out to the
1057       passed database handle.
1058
1059   $dbh->sqlite_enable_load_extension( $bool )
1060       Calling this method with a true value enables loading (external)
1061       SQLite3 extensions. After the call, you can load extensions like this:
1062
1063         $dbh->sqlite_enable_load_extension(1);
1064         $sth = $dbh->prepare("select load_extension('libsqlitefunctions.so')")
1065         or die "Cannot prepare: " . $dbh->errstr();
1066
1067   $dbh->sqlite_load_extension( $file, $proc )
1068       Loading an extension by a select statement (with the "load_extension"
1069       SQLite3 function like above) has some limitations. If you need to, say,
1070       create other functions from an extension, use this method. $file (a
1071       path to the extension) is mandatory, and $proc (an entry point name) is
1072       optional. You need to call "sqlite_enable_load_extension" before
1073       calling "sqlite_load_extension".
1074
1075   $dbh->sqlite_trace( $code_ref )
1076       This method registers a trace callback to be invoked whenever SQL
1077       statements are being run.
1078
1079       The callback will be called as
1080
1081         $code_ref->($statement)
1082
1083       where
1084
1085       $statement
1086           is a UTF-8 rendering of the SQL statement text as the statement
1087           first begins executing.
1088
1089       Additional callbacks might occur as each triggered subprogram is
1090       entered. The callbacks for triggers contain a UTF-8 SQL comment that
1091       identifies the trigger.
1092
1093       See also "TRACING" in DBI for better tracing options.
1094
1095   $dbh->sqlite_profile( $code_ref )
1096       This method registers a profile callback to be invoked whenever a SQL
1097       statement finishes.
1098
1099       The callback will be called as
1100
1101         $code_ref->($statement, $elapsed_time)
1102
1103       where
1104
1105       $statement
1106           is the original statement text (without bind parameters).
1107
1108       $elapsed_time
1109           is an estimate of wall-clock time of how long that statement took
1110           to run (in milliseconds).
1111
1112       This method is considered experimental and is subject to change in
1113       future versions of SQLite.
1114
1115       See also DBI::Profile for better profiling options.
1116
1117   $dbh->sqlite_table_column_metadata( $dbname, $tablename, $columnname )
1118       is for internal use only.
1119
1120   $dbh->sqlite_db_status()
1121       Returns a hash reference that holds a set of status information of
1122       database connection such as cache usage. See
1123       <https://www.sqlite.org/c3ref/c_dbstatus_options.html> for details. You
1124       may also pass 0 as an argument to reset the status.
1125
1126   $sth->sqlite_st_status()
1127       Returns a hash reference that holds a set of status information of
1128       SQLite statement handle such as full table scan count. See
1129       <https://www.sqlite.org/c3ref/c_stmtstatus_counter.html> for details.
1130       Statement status only holds the current value.
1131
1132         my $status = $sth->sqlite_st_status();
1133         my $cur = $status->{fullscan_step};
1134
1135       You may also pass 0 as an argument to reset the status.
1136
1137   $dbh->sqlite_db_config( $id, $new_integer_value )
1138       You can change how the connected database should behave like this:
1139
1140         use DBD::SQLite::Constants qw/:database_connection_configuration_options/;
1141
1142         my $dbh = DBI->connect('dbi:SQLite::memory:');
1143
1144         # This disables language features that allow ordinary SQL
1145         # to deliberately corrupt the database file
1146         $dbh->sqlite_db_config( SQLITE_DBCONFIG_DEFENSIVE, 1 );
1147
1148         # This disables two-arg version of fts3_tokenizer.
1149         $dbh->sqlite_db_config( SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, 0 );
1150
1151       "sqlite_db_config" returns the new value after the call. If you just
1152       want to know the current value without changing anything, pass a
1153       negative integer value.
1154
1155         my $current_value = $dbh->sqlite_db_config( SQLITE_DBCONFIG_DEFENSIVE, -1 );
1156
1157       As of this writing, "sqlite_db_config" only supports options that set
1158       an integer value. "SQLITE_DBCONFIG_LOOKASIDE" and
1159       "SQLITE_DBCONFIG_MAINDBNAME" are not supported. See also
1160       "https://www.sqlite.org/capi3ref.html#sqlite3_db_config" for details.
1161
1162   $dbh->sqlite_create_module()
1163       Registers a name for a virtual table module. Module names must be
1164       registered before creating a new virtual table using the module and
1165       before using a preexisting virtual table for the module.  Virtual
1166       tables are explained in DBD::SQLite::VirtualTable.
1167
1168   $dbh->sqlite_limit( $category_id, $new_value )
1169       Sets a new run-time limit for the category, and returns the current
1170       limit.  If the new value is a negative number (or omitted), the limit
1171       is unchanged and just returns the current limit. Category ids
1172       (SQLITE_LIMIT_LENGTH, SQLITE_LIMIT_VARIABLE_NUMBER, etc) can be
1173       imported from DBD::SQLite::Constants.
1174

DRIVER FUNCTIONS

1176   DBD::SQLite::compile_options()
1177       Returns an array of compile options (available since SQLite 3.6.23,
1178       bundled in DBD::SQLite 1.30_01), or an empty array if the bundled
1179       library is old or compiled with SQLITE_OMIT_COMPILEOPTION_DIAGS.
1180
1181   DBD::SQLite::sqlite_status()
1182       Returns a hash reference that holds a set of status information of
1183       SQLite runtime such as memory usage or page cache usage (see
1184       <https://www.sqlite.org/c3ref/c_status_malloc_count.html> for details).
1185       Each of the entry contains the current value and the highwater value.
1186
1187         my $status = DBD::SQLite::sqlite_status();
1188         my $cur  = $status->{memory_used}{current};
1189         my $high = $status->{memory_used}{highwater};
1190
1191       You may also pass 0 as an argument to reset the status.
1192
1193   DBD::SQLite::strlike($pattern, $string, $escape_char),
1194       DBD::SQLite::strglob($pattern, $string)
1195       As of 1.49_05 (SQLite 3.10.0), you can use these two functions to see
1196       if a string matches a pattern. These may be useful when you create a
1197       virtual table or a custom function.  See
1198       <http://sqlite.org/c3ref/strlike.html> and
1199       <http://sqlite.org/c3ref/strglob.html> for details.
1200

DRIVER CONSTANTS

1202       A subset of SQLite C constants are made available to Perl, because they
1203       may be needed when writing hooks or authorizer callbacks. For accessing
1204       such constants, the "DBD::SQLite" module must be explicitly "use"d at
1205       compile time. For example, an authorizer that forbids any DELETE
1206       operation would be written as follows :
1207
1208         use DBD::SQLite;
1209         $dbh->sqlite_set_authorizer(sub {
1210           my $action_code = shift;
1211           return $action_code == DBD::SQLite::DELETE ? DBD::SQLite::DENY
1212                                                      : DBD::SQLite::OK;
1213         });
1214
1215       The list of constants implemented in "DBD::SQLite" is given below; more
1216       information can be found ad at
1217       <https://www.sqlite.org/c3ref/constlist.html>.
1218
1219   Authorizer Return Codes
1220         OK
1221         DENY
1222         IGNORE
1223
1224   Action Codes
1225       The "set_authorizer" method registers a callback function that is
1226       invoked to authorize certain SQL statement actions. The first parameter
1227       to the callback is an integer code that specifies what action is being
1228       authorized. The second and third parameters to the callback are
1229       strings, the meaning of which varies according to the action code.
1230       Below is the list of action codes, together with their associated
1231       strings.
1232
1233         # constant              string1         string2
1234         # ========              =======         =======
1235         CREATE_INDEX            Index Name      Table Name
1236         CREATE_TABLE            Table Name      undef
1237         CREATE_TEMP_INDEX       Index Name      Table Name
1238         CREATE_TEMP_TABLE       Table Name      undef
1239         CREATE_TEMP_TRIGGER     Trigger Name    Table Name
1240         CREATE_TEMP_VIEW        View Name       undef
1241         CREATE_TRIGGER          Trigger Name    Table Name
1242         CREATE_VIEW             View Name       undef
1243         DELETE                  Table Name      undef
1244         DROP_INDEX              Index Name      Table Name
1245         DROP_TABLE              Table Name      undef
1246         DROP_TEMP_INDEX         Index Name      Table Name
1247         DROP_TEMP_TABLE         Table Name      undef
1248         DROP_TEMP_TRIGGER       Trigger Name    Table Name
1249         DROP_TEMP_VIEW          View Name       undef
1250         DROP_TRIGGER            Trigger Name    Table Name
1251         DROP_VIEW               View Name       undef
1252         INSERT                  Table Name      undef
1253         PRAGMA                  Pragma Name     1st arg or undef
1254         READ                    Table Name      Column Name
1255         SELECT                  undef           undef
1256         TRANSACTION             Operation       undef
1257         UPDATE                  Table Name      Column Name
1258         ATTACH                  Filename        undef
1259         DETACH                  Database Name   undef
1260         ALTER_TABLE             Database Name   Table Name
1261         REINDEX                 Index Name      undef
1262         ANALYZE                 Table Name      undef
1263         CREATE_VTABLE           Table Name      Module Name
1264         DROP_VTABLE             Table Name      Module Name
1265         FUNCTION                undef           Function Name
1266         SAVEPOINT               Operation       Savepoint Name
1267

COLLATION FUNCTIONS

1269   Definition
1270       SQLite v3 provides the ability for users to supply arbitrary comparison
1271       functions, known as user-defined "collation sequences" or "collating
1272       functions", to be used for comparing two text values.
1273       <https://www.sqlite.org/datatype3.html#collation> explains how
1274       collations are used in various SQL expressions.
1275
1276   Builtin collation sequences
1277       The following collation sequences are builtin within SQLite :
1278
1279       BINARY
1280           Compares string data using memcmp(), regardless of text encoding.
1281
1282       NOCASE
1283           The same as binary, except the 26 upper case characters of ASCII
1284           are folded to their lower case equivalents before the comparison is
1285           performed. Note that only ASCII characters are case folded. SQLite
1286           does not attempt to do full UTF case folding due to the size of the
1287           tables required.
1288
1289       RTRIM
1290           The same as binary, except that trailing space characters are
1291           ignored.
1292
1293       In addition, "DBD::SQLite" automatically installs the following
1294       collation sequences :
1295
1296       perl
1297           corresponds to the Perl "cmp" operator
1298
1299       perllocale
1300           Perl "cmp" operator, in a context where "use locale" is activated.
1301
1302   Usage
1303       You can write for example
1304
1305         CREATE TABLE foo(
1306             txt1 COLLATE perl,
1307             txt2 COLLATE perllocale,
1308             txt3 COLLATE nocase
1309         )
1310
1311       or
1312
1313         SELECT * FROM foo ORDER BY name COLLATE perllocale
1314
1315   Unicode handling
1316       If the attribute "$dbh->{sqlite_unicode}" is set, strings coming from
1317       the database and passed to the collation function will be properly
1318       tagged with the utf8 flag; but this only works if the "sqlite_unicode"
1319       attribute is set before the first call to a perl collation sequence .
1320       The recommended way to activate unicode is to set the parameter at
1321       connection time :
1322
1323         my $dbh = DBI->connect(
1324             "dbi:SQLite:dbname=foo", "", "",
1325             {
1326                 RaiseError     => 1,
1327                 sqlite_unicode => 1,
1328             }
1329         );
1330
1331   Adding user-defined collations
1332       The native SQLite API for adding user-defined collations is exposed
1333       through methods "sqlite_create_collation" and
1334       "sqlite_collation_needed".
1335
1336       To avoid calling these functions every time a $dbh handle is created,
1337       "DBD::SQLite" offers a simpler interface through the
1338       %DBD::SQLite::COLLATION hash : just insert your own collation functions
1339       in that hash, and whenever an unknown collation name is encountered in
1340       SQL, the appropriate collation function will be loaded on demand from
1341       the hash. For example, here is a way to sort text values regardless of
1342       their accented characters :
1343
1344         use DBD::SQLite;
1345         $DBD::SQLite::COLLATION{no_accents} = sub {
1346           my ( $a, $b ) = map lc, @_;
1347           tr[����������������������������]
1348             [aaaaaacdeeeeiiiinoooooouuuuy] for $a, $b;
1349           $a cmp $b;
1350         };
1351         my $dbh  = DBI->connect("dbi:SQLite:dbname=dbfile");
1352         my $sql  = "SELECT ... FROM ... ORDER BY ... COLLATE no_accents");
1353         my $rows = $dbh->selectall_arrayref($sql);
1354
1355       The builtin "perl" or "perllocale" collations are predefined in that
1356       same hash.
1357
1358       The COLLATION hash is a global registry within the current process;
1359       hence there is a risk of undesired side-effects. Therefore, to prevent
1360       action at distance, the hash is implemented as a "write-only" hash,
1361       that will happily accept new entries, but will raise an exception if
1362       any attempt is made to override or delete a existing entry (including
1363       the builtin "perl" and "perllocale").
1364
1365       If you really, really need to change or delete an entry, you can always
1366       grab the tied object underneath %DBD::SQLite::COLLATION --- but don't
1367       do that unless you really know what you are doing. Also observe that
1368       changes in the global hash will not modify existing collations in
1369       existing database handles: it will only affect new requests for
1370       collations. In other words, if you want to change the behaviour of a
1371       collation within an existing $dbh, you need to call the
1372       "create_collation" method directly.
1373
1375       SQLite is bundled with an extension module for full-text indexing.
1376       Tables with this feature enabled can be efficiently queried to find
1377       rows that contain one or more instances of some specified words, in any
1378       column, even if the table contains many large documents.
1379
1380       Explanations for using this feature are provided in a separate
1381       document: see DBD::SQLite::Fulltext_search.
1382

R* TREE SUPPORT

1384       The RTREE extension module within SQLite adds support for creating a
1385       R-Tree, a special index for range and multidimensional queries.  This
1386       allows users to create tables that can be loaded with (as an example)
1387       geospatial data such as latitude/longitude coordinates for buildings
1388       within a city :
1389
1390         CREATE VIRTUAL TABLE city_buildings USING rtree(
1391            id,               -- Integer primary key
1392            minLong, maxLong, -- Minimum and maximum longitude
1393            minLat, maxLat    -- Minimum and maximum latitude
1394         );
1395
1396       then query which buildings overlap or are contained within a specified
1397       region:
1398
1399         # IDs that are contained within query coordinates
1400         my $contained_sql = <<"";
1401         SELECT id FROM city_buildings
1402            WHERE  minLong >= ? AND maxLong <= ?
1403            AND    minLat  >= ? AND maxLat  <= ?
1404
1405         # ... and those that overlap query coordinates
1406         my $overlap_sql = <<"";
1407         SELECT id FROM city_buildings
1408            WHERE    maxLong >= ? AND minLong <= ?
1409            AND      maxLat  >= ? AND minLat  <= ?
1410
1411         my $contained = $dbh->selectcol_arrayref($contained_sql,undef,
1412                               $minLong, $maxLong, $minLat, $maxLat);
1413
1414         my $overlapping = $dbh->selectcol_arrayref($overlap_sql,undef,
1415                               $minLong, $maxLong, $minLat, $maxLat);
1416
1417       For more detail, please see the SQLite R-Tree page
1418       (<https://www.sqlite.org/rtree.html>). Note that custom R-Tree queries
1419       using callbacks, as mentioned in the prior link, have not been
1420       implemented yet.
1421

VIRTUAL TABLES IMPLEMENTED IN PERL

1423       SQLite has a concept of "virtual tables" which look like regular tables
1424       but are implemented internally through specific functions.  The
1425       fulltext or R* tree features described in the previous chapters are
1426       examples of such virtual tables, implemented in C code.
1427
1428       "DBD::SQLite" also supports virtual tables implemented in Perl code:
1429       see DBD::SQLite::VirtualTable for using or implementing such virtual
1430       tables. These can have many interesting uses for joining regular DBMS
1431       data with some other kind of data within your Perl programs. Bundled
1432       with the present distribution are :
1433
1434       ·   DBD::SQLite::VirtualTable::FileContent : implements a virtual
1435           column that exposes file contents. This is especially useful in
1436           conjunction with a fulltext index; see
1437           DBD::SQLite::Fulltext_search.
1438
1439       ·   DBD::SQLite::VirtualTable::PerlData : binds to a Perl array within
1440           the Perl program. This can be used for simple import/export
1441           operations, for debugging purposes, for joining data from different
1442           sources, etc.
1443
1444       Other Perl virtual tables may also be published separately on CPAN.
1445

TO DO

1447       The following items remain to be done.
1448
1449   Leak Detection
1450       Implement one or more leak detection tests that only run during
1451       AUTOMATED_TESTING and RELEASE_TESTING and validate that none of the C
1452       code we work with leaks.
1453
1454   Stream API for Blobs
1455       Reading/writing into blobs using "sqlite2_blob_open" /
1456       "sqlite2_blob_close".
1457
1458   Support for custom callbacks for R-Tree queries
1459       Custom queries of a R-Tree index using a callback are possible with the
1460       SQLite C API (<https://www.sqlite.org/rtree.html>), so one could
1461       potentially use a callback that narrowed the result set down based on a
1462       specific need, such as querying for overlapping circles.
1463

SUPPORT

1465       Bugs should be reported to GitHub issues:
1466
1467       <https://github.com/DBD-SQLite/DBD-SQLite/issues>
1468
1469       or via RT if you prefer:
1470
1471       <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
1472
1473       Note that bugs of bundled SQLite library (i.e. bugs in "sqlite3.[ch]")
1474       should be reported to the SQLite developers at sqlite.org via their bug
1475       tracker or via their mailing list.
1476
1477       The master repository is on GitHub:
1478
1479       <https://github.com/DBD-SQLite/DBD-SQLite>.
1480
1481       We also have a mailing list:
1482
1483       <http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite>
1484

AUTHORS

1486       Matt Sergeant <matt@sergeant.org>
1487
1488       Francis J. Lacoste <flacoste@logreport.org>
1489
1490       Wolfgang Sourdeau <wolfgang@logreport.org>
1491
1492       Adam Kennedy <adamk@cpan.org>
1493
1494       Max Maischein <corion@cpan.org>
1495
1496       Laurent Dami <dami@cpan.org>
1497
1498       Kenichi Ishigaki <ishigaki@cpan.org>
1499
1501       The bundled SQLite code in this distribution is Public Domain.
1502
1503       DBD::SQLite is copyright 2002 - 2007 Matt Sergeant.
1504
1505       Some parts copyright 2008 Francis J. Lacoste.
1506
1507       Some parts copyright 2008 Wolfgang Sourdeau.
1508
1509       Some parts copyright 2008 - 2013 Adam Kennedy.
1510
1511       Some parts copyright 2009 - 2013 Kenichi Ishigaki.
1512
1513       Some parts derived from DBD::SQLite::Amalgamation copyright 2008 Audrey
1514       Tang.
1515
1516       This program is free software; you can redistribute it and/or modify it
1517       under the same terms as Perl itself.
1518
1519       The full text of the license can be found in the LICENSE file included
1520       with this module.
1521
1522
1523
1524perl v5.28.1                      2019-01-02                    DBD::SQLite(3)
Impressum