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.36.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_string_mode
534           SQLite strings are simple arrays of bytes, but Perl strings can
535           store any arbitrary Unicode code point. Thus, DBD::SQLite has to
536           adopt some method of translating between those two models. This
537           parameter defines that translation.
538
539           Accepted values are the following constants:
540
541           •   DBD_SQLITE_STRING_MODE_BYTES: All strings are assumed to
542               represent bytes. A Perl string that contains any code point
543               above 255 will trigger an exception. This is appropriate for
544               Latin-1 strings, binary data, pre-encoded UTF-8 strings, etc.
545
546           •   DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK: All Perl strings are
547               encoded to UTF-8 before being given to SQLite. Perl will try to
548               decode SQLite strings as UTF-8 when giving them to Perl. Should
549               any such string not be valid UTF-8, a warning is thrown, and
550               the string is left undecoded.
551
552               This is appropriate for strings that are decoded to characters
553               via, e.g., "decode" in Encode.
554
555               Also note that, due to some bizarreness in SQLite's type system
556               (see <https://www.sqlite.org/datatype3.html>), if you want to
557               retain blob-style behavior for some columns under
558               DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK (say, to store images
559               in the database), you have to state so explicitly using the
560               3-argument form of "bind_param" in DBI when doing updates:
561
562                 use DBI qw(:sql_types);
563                 use DBD::SQLite::Constants ':dbd_sqlite_string_mode';
564                 $dbh->{sqlite_string_mode} = DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK;
565                 my $sth = $dbh->prepare("INSERT INTO mytable (blobcolumn) VALUES (?)");
566
567                 # Binary_data will be stored as is.
568                 $sth->bind_param(1, $binary_data, SQL_BLOB);
569
570               Defining the column type as "BLOB" in the DDL is not
571               sufficient.
572
573           •   DBD_SQLITE_STRING_MODE_UNICODE_STRICT: Like
574               DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK but usually throws an
575               exception rather than a warning if SQLite sends invalid UTF-8.
576               (In Perl callbacks from SQLite we still warn instead.)
577
578           •   DBD_SQLITE_STRING_MODE_UNICODE_NAIVE: Like
579               DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK but uses a "naïve"
580               UTF-8 decoding method that forgoes validation. This is
581               marginally faster than a validated decode, but it can also
582               corrupt Perl itself!
583
584           •   DBD_SQLITE_STRING_MODE_PV (default, but DO NOT USE): Like
585               DBD_SQLITE_STRING_MODE_BYTES, but when translating Perl strings
586               to SQLite the Perl string's internal byte buffer is given to
587               SQLite. This is bad, but it's been the default for many years,
588               and changing that would break existing applications.
589
590       "sqlite_unicode" or "unicode" (deprecated)
591           If truthy, equivalent to setting "sqlite_string_mode" to
592           DBD_SQLITE_STRING_MODE_UNICODE_NAIVE; if falsy, equivalent to
593           DBD_SQLITE_STRING_MODE_PV.
594
595           Prefer "sqlite_string_mode" in all new code.
596
597       sqlite_allow_multiple_statements
598           If you set this to true, "do" method will process multiple
599           statements at one go. This may be handy, but with performance
600           penalty. See above for details.
601
602       sqlite_use_immediate_transaction
603           If you set this to true, DBD::SQLite tries to issue a "begin
604           immediate transaction" (instead of "begin transaction") when
605           necessary. See above for details.
606
607           As of version 1.38_01, this attribute is set to true by default.
608           If you really need to use "deferred" transactions for some reasons,
609           set this to false explicitly.
610
611       sqlite_see_if_its_a_number
612           If you set this to true, DBD::SQLite tries to see if the bind
613           values are number or not, and does not quote if they are numbers.
614           See above for details.
615
616       sqlite_extended_result_codes
617           If set to true, DBD::SQLite uses extended result codes where
618           appropriate (see <https://www.sqlite.org/rescode.html>).
619
620       sqlite_defensive
621           If set to true, language features that allow ordinary SQL to
622           deliberately corrupt the database file are prohibited.
623
624   Statement Handle Attributes
625       sqlite_unprepared_statements
626           Returns an unprepared part of the statement you pass to "prepare".
627           Typically this contains nothing but white spaces after a semicolon.
628           See above for details.
629

METHODS

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

DRIVER PRIVATE METHODS

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

DRIVER FUNCTIONS

1238   DBD::SQLite::compile_options()
1239       Returns an array of compile options (available since SQLite 3.6.23,
1240       bundled in DBD::SQLite 1.30_01), or an empty array if the bundled
1241       library is old or compiled with SQLITE_OMIT_COMPILEOPTION_DIAGS.
1242
1243   DBD::SQLite::sqlite_status()
1244       Returns a hash reference that holds a set of status information of
1245       SQLite runtime such as memory usage or page cache usage (see
1246       <https://www.sqlite.org/c3ref/c_status_malloc_count.html> for details).
1247       Each of the entry contains the current value and the highwater value.
1248
1249         my $status = DBD::SQLite::sqlite_status();
1250         my $cur  = $status->{memory_used}{current};
1251         my $high = $status->{memory_used}{highwater};
1252
1253       You may also pass 0 as an argument to reset the status.
1254
1255   DBD::SQLite::strlike($pattern, $string, $escape_char),
1256       DBD::SQLite::strglob($pattern, $string)
1257       As of 1.49_05 (SQLite 3.10.0), you can use these two functions to see
1258       if a string matches a pattern. These may be useful when you create a
1259       virtual table or a custom function.  See
1260       <http://sqlite.org/c3ref/strlike.html> and
1261       <http://sqlite.org/c3ref/strglob.html> for details.
1262

DRIVER CONSTANTS

1264       A subset of SQLite C constants are made available to Perl, because they
1265       may be needed when writing hooks or authorizer callbacks. For accessing
1266       such constants, the "DBD::SQLite" module must be explicitly "use"d at
1267       compile time. For example, an authorizer that forbids any DELETE
1268       operation would be written as follows :
1269
1270         use DBD::SQLite;
1271         $dbh->sqlite_set_authorizer(sub {
1272           my $action_code = shift;
1273           return $action_code == DBD::SQLite::DELETE ? DBD::SQLite::DENY
1274                                                      : DBD::SQLite::OK;
1275         });
1276
1277       The list of constants implemented in "DBD::SQLite" is given below; more
1278       information can be found ad at
1279       <https://www.sqlite.org/c3ref/constlist.html>.
1280
1281   Authorizer Return Codes
1282         OK
1283         DENY
1284         IGNORE
1285
1286   Action Codes
1287       The "set_authorizer" method registers a callback function that is
1288       invoked to authorize certain SQL statement actions. The first parameter
1289       to the callback is an integer code that specifies what action is being
1290       authorized. The second and third parameters to the callback are
1291       strings, the meaning of which varies according to the action code.
1292       Below is the list of action codes, together with their associated
1293       strings.
1294
1295         # constant              string1         string2
1296         # ========              =======         =======
1297         CREATE_INDEX            Index Name      Table Name
1298         CREATE_TABLE            Table Name      undef
1299         CREATE_TEMP_INDEX       Index Name      Table Name
1300         CREATE_TEMP_TABLE       Table Name      undef
1301         CREATE_TEMP_TRIGGER     Trigger Name    Table Name
1302         CREATE_TEMP_VIEW        View Name       undef
1303         CREATE_TRIGGER          Trigger Name    Table Name
1304         CREATE_VIEW             View Name       undef
1305         DELETE                  Table Name      undef
1306         DROP_INDEX              Index Name      Table Name
1307         DROP_TABLE              Table Name      undef
1308         DROP_TEMP_INDEX         Index Name      Table Name
1309         DROP_TEMP_TABLE         Table Name      undef
1310         DROP_TEMP_TRIGGER       Trigger Name    Table Name
1311         DROP_TEMP_VIEW          View Name       undef
1312         DROP_TRIGGER            Trigger Name    Table Name
1313         DROP_VIEW               View Name       undef
1314         INSERT                  Table Name      undef
1315         PRAGMA                  Pragma Name     1st arg or undef
1316         READ                    Table Name      Column Name
1317         SELECT                  undef           undef
1318         TRANSACTION             Operation       undef
1319         UPDATE                  Table Name      Column Name
1320         ATTACH                  Filename        undef
1321         DETACH                  Database Name   undef
1322         ALTER_TABLE             Database Name   Table Name
1323         REINDEX                 Index Name      undef
1324         ANALYZE                 Table Name      undef
1325         CREATE_VTABLE           Table Name      Module Name
1326         DROP_VTABLE             Table Name      Module Name
1327         FUNCTION                undef           Function Name
1328         SAVEPOINT               Operation       Savepoint Name
1329

COLLATION FUNCTIONS

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

R* TREE SUPPORT

1446       The RTREE extension module within SQLite adds support for creating a
1447       R-Tree, a special index for range and multidimensional queries.  This
1448       allows users to create tables that can be loaded with (as an example)
1449       geospatial data such as latitude/longitude coordinates for buildings
1450       within a city :
1451
1452         CREATE VIRTUAL TABLE city_buildings USING rtree(
1453            id,               -- Integer primary key
1454            minLong, maxLong, -- Minimum and maximum longitude
1455            minLat, maxLat    -- Minimum and maximum latitude
1456         );
1457
1458       then query which buildings overlap or are contained within a specified
1459       region:
1460
1461         # IDs that are contained within query coordinates
1462         my $contained_sql = <<"";
1463         SELECT id FROM city_buildings
1464            WHERE  minLong >= ? AND maxLong <= ?
1465            AND    minLat  >= ? AND maxLat  <= ?
1466
1467         # ... and those that overlap query coordinates
1468         my $overlap_sql = <<"";
1469         SELECT id FROM city_buildings
1470            WHERE    maxLong >= ? AND minLong <= ?
1471            AND      maxLat  >= ? AND minLat  <= ?
1472
1473         my $contained = $dbh->selectcol_arrayref($contained_sql,undef,
1474                               $minLong, $maxLong, $minLat, $maxLat);
1475
1476         my $overlapping = $dbh->selectcol_arrayref($overlap_sql,undef,
1477                               $minLong, $maxLong, $minLat, $maxLat);
1478
1479       For more detail, please see the SQLite R-Tree page
1480       (<https://www.sqlite.org/rtree.html>). Note that custom R-Tree queries
1481       using callbacks, as mentioned in the prior link, have not been
1482       implemented yet.
1483

VIRTUAL TABLES IMPLEMENTED IN PERL

1485       SQLite has a concept of "virtual tables" which look like regular tables
1486       but are implemented internally through specific functions.  The
1487       fulltext or R* tree features described in the previous chapters are
1488       examples of such virtual tables, implemented in C code.
1489
1490       "DBD::SQLite" also supports virtual tables implemented in Perl code:
1491       see DBD::SQLite::VirtualTable for using or implementing such virtual
1492       tables. These can have many interesting uses for joining regular DBMS
1493       data with some other kind of data within your Perl programs. Bundled
1494       with the present distribution are :
1495
1496       •   DBD::SQLite::VirtualTable::FileContent : implements a virtual
1497           column that exposes file contents. This is especially useful in
1498           conjunction with a fulltext index; see
1499           DBD::SQLite::Fulltext_search.
1500
1501       •   DBD::SQLite::VirtualTable::PerlData : binds to a Perl array within
1502           the Perl program. This can be used for simple import/export
1503           operations, for debugging purposes, for joining data from different
1504           sources, etc.
1505
1506       Other Perl virtual tables may also be published separately on CPAN.
1507

TO DO

1509       The following items remain to be done.
1510
1511   Leak Detection
1512       Implement one or more leak detection tests that only run during
1513       AUTOMATED_TESTING and RELEASE_TESTING and validate that none of the C
1514       code we work with leaks.
1515
1516   Stream API for Blobs
1517       Reading/writing into blobs using "sqlite2_blob_open" /
1518       "sqlite2_blob_close".
1519
1520   Support for custom callbacks for R-Tree queries
1521       Custom queries of a R-Tree index using a callback are possible with the
1522       SQLite C API (<https://www.sqlite.org/rtree.html>), so one could
1523       potentially use a callback that narrowed the result set down based on a
1524       specific need, such as querying for overlapping circles.
1525

SUPPORT

1527       Bugs should be reported to GitHub issues:
1528
1529       <https://github.com/DBD-SQLite/DBD-SQLite/issues>
1530
1531       or via RT if you prefer:
1532
1533       <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
1534
1535       Note that bugs of bundled SQLite library (i.e. bugs in "sqlite3.[ch]")
1536       should be reported to the SQLite developers at sqlite.org via their bug
1537       tracker or via their mailing list.
1538
1539       The master repository is on GitHub:
1540
1541       <https://github.com/DBD-SQLite/DBD-SQLite>.
1542
1543       We also have a mailing list:
1544
1545       <http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite>
1546

AUTHORS

1548       Matt Sergeant <matt@sergeant.org>
1549
1550       Francis J. Lacoste <flacoste@logreport.org>
1551
1552       Wolfgang Sourdeau <wolfgang@logreport.org>
1553
1554       Adam Kennedy <adamk@cpan.org>
1555
1556       Max Maischein <corion@cpan.org>
1557
1558       Laurent Dami <dami@cpan.org>
1559
1560       Kenichi Ishigaki <ishigaki@cpan.org>
1561
1563       The bundled SQLite code in this distribution is Public Domain.
1564
1565       DBD::SQLite is copyright 2002 - 2007 Matt Sergeant.
1566
1567       Some parts copyright 2008 Francis J. Lacoste.
1568
1569       Some parts copyright 2008 Wolfgang Sourdeau.
1570
1571       Some parts copyright 2008 - 2013 Adam Kennedy.
1572
1573       Some parts copyright 2009 - 2013 Kenichi Ishigaki.
1574
1575       Some parts derived from DBD::SQLite::Amalgamation copyright 2008 Audrey
1576       Tang.
1577
1578       This program is free software; you can redistribute it and/or modify it
1579       under the same terms as Perl itself.
1580
1581       The full text of the license can be found in the LICENSE file included
1582       with this module.
1583
1584
1585
1586perl v5.36.0                      2022-07-22                    DBD::SQLite(3)
Impressum