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

DRIVER FUNCTIONS

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

DRIVER CONSTANTS

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

COLLATION FUNCTIONS

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

R* TREE SUPPORT

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

VIRTUAL TABLES IMPLEMENTED IN PERL

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

TO DO

1461       The following items remain to be done.
1462
1463   Leak Detection
1464       Implement one or more leak detection tests that only run during
1465       AUTOMATED_TESTING and RELEASE_TESTING and validate that none of the C
1466       code we work with leaks.
1467
1468   Stream API for Blobs
1469       Reading/writing into blobs using "sqlite2_blob_open" /
1470       "sqlite2_blob_close".
1471
1472   Support for custom callbacks for R-Tree queries
1473       Custom queries of a R-Tree index using a callback are possible with the
1474       SQLite C API (<https://www.sqlite.org/rtree.html>), so one could
1475       potentially use a callback that narrowed the result set down based on a
1476       specific need, such as querying for overlapping circles.
1477

SUPPORT

1479       Bugs should be reported to GitHub issues:
1480
1481       <https://github.com/DBD-SQLite/DBD-SQLite/issues>
1482
1483       or via RT if you prefer:
1484
1485       <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
1486
1487       Note that bugs of bundled SQLite library (i.e. bugs in "sqlite3.[ch]")
1488       should be reported to the SQLite developers at sqlite.org via their bug
1489       tracker or via their mailing list.
1490
1491       The master repository is on GitHub:
1492
1493       <https://github.com/DBD-SQLite/DBD-SQLite>.
1494
1495       We also have a mailing list:
1496
1497       <http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite>
1498

AUTHORS

1500       Matt Sergeant <matt@sergeant.org>
1501
1502       Francis J. Lacoste <flacoste@logreport.org>
1503
1504       Wolfgang Sourdeau <wolfgang@logreport.org>
1505
1506       Adam Kennedy <adamk@cpan.org>
1507
1508       Max Maischein <corion@cpan.org>
1509
1510       Laurent Dami <dami@cpan.org>
1511
1512       Kenichi Ishigaki <ishigaki@cpan.org>
1513
1515       The bundled SQLite code in this distribution is Public Domain.
1516
1517       DBD::SQLite is copyright 2002 - 2007 Matt Sergeant.
1518
1519       Some parts copyright 2008 Francis J. Lacoste.
1520
1521       Some parts copyright 2008 Wolfgang Sourdeau.
1522
1523       Some parts copyright 2008 - 2013 Adam Kennedy.
1524
1525       Some parts copyright 2009 - 2013 Kenichi Ishigaki.
1526
1527       Some parts derived from DBD::SQLite::Amalgamation copyright 2008 Audrey
1528       Tang.
1529
1530       This program is free software; you can redistribute it and/or modify it
1531       under the same terms as Perl itself.
1532
1533       The full text of the license can be found in the LICENSE file included
1534       with this module.
1535
1536
1537
1538perl v5.32.0                      2020-08-31                    DBD::SQLite(3)
Impressum