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.39.4 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 disconnect
128           databases explicitly before the temporary directory is gone
129           (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('libmemvfs.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 the extension you
1114       want to use creates other functions that are not native to SQLite, use
1115       this method instead. $file (a path to the extension) is mandatory, and
1116       $proc (an entry point name) is optional. You need to call
1117       "sqlite_enable_load_extension" before calling "sqlite_load_extension":
1118
1119         $dbh->sqlite_enable_load_extension(1);
1120         $dbh->sqlite_load_extension('libsqlitefunctions.so')
1121         or die "Cannot load extension: " . $dbh->errstr();
1122
1123       If the extension uses SQLite mutex functions like
1124       "sqlite3_mutex_enter", then the extension should be compiled with the
1125       same "SQLITE_THREADSAFE" compile-time setting as this module, see
1126       DBD::SQLite::compile_options().
1127
1128   $dbh->sqlite_trace( $code_ref )
1129       This method registers a trace callback to be invoked whenever SQL
1130       statements are being run.
1131
1132       The callback will be called as
1133
1134         $code_ref->($statement)
1135
1136       where
1137
1138       $statement
1139           is a UTF-8 rendering of the SQL statement text as the statement
1140           first begins executing.
1141
1142       Additional callbacks might occur as each triggered subprogram is
1143       entered. The callbacks for triggers contain a UTF-8 SQL comment that
1144       identifies the trigger.
1145
1146       See also "TRACING" in DBI for better tracing options.
1147
1148   $dbh->sqlite_profile( $code_ref )
1149       This method registers a profile callback to be invoked whenever a SQL
1150       statement finishes.
1151
1152       The callback will be called as
1153
1154         $code_ref->($statement, $elapsed_time)
1155
1156       where
1157
1158       $statement
1159           is the original statement text (without bind parameters).
1160
1161       $elapsed_time
1162           is an estimate of wall-clock time of how long that statement took
1163           to run (in milliseconds).
1164
1165       This method is considered experimental and is subject to change in
1166       future versions of SQLite.
1167
1168       See also DBI::Profile for better profiling options.
1169
1170   $dbh->sqlite_table_column_metadata( $dbname, $tablename, $columnname )
1171       is for internal use only.
1172
1173   $dbh->sqlite_db_status()
1174       Returns a hash reference that holds a set of status information of
1175       database connection such as cache usage. See
1176       <https://www.sqlite.org/c3ref/c_dbstatus_options.html> for details. You
1177       may also pass 0 as an argument to reset the status.
1178
1179   $sth->sqlite_st_status()
1180       Returns a hash reference that holds a set of status information of
1181       SQLite statement handle such as full table scan count. See
1182       <https://www.sqlite.org/c3ref/c_stmtstatus_counter.html> for details.
1183       Statement status only holds the current value.
1184
1185         my $status = $sth->sqlite_st_status();
1186         my $cur = $status->{fullscan_step};
1187
1188       You may also pass 0 as an argument to reset the status.
1189
1190   $dbh->sqlite_db_config( $id, $new_integer_value )
1191       You can change how the connected database should behave like this:
1192
1193         use DBD::SQLite::Constants qw/:database_connection_configuration_options/;
1194
1195         my $dbh = DBI->connect('dbi:SQLite::memory:');
1196
1197         # This disables language features that allow ordinary SQL
1198         # to deliberately corrupt the database file
1199         $dbh->sqlite_db_config( SQLITE_DBCONFIG_DEFENSIVE, 1 );
1200
1201         # This disables two-arg version of fts3_tokenizer.
1202         $dbh->sqlite_db_config( SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, 0 );
1203
1204       "sqlite_db_config" returns the new value after the call. If you just
1205       want to know the current value without changing anything, pass a
1206       negative integer value.
1207
1208         my $current_value = $dbh->sqlite_db_config( SQLITE_DBCONFIG_DEFENSIVE, -1 );
1209
1210       As of this writing, "sqlite_db_config" only supports options that set
1211       an integer value. "SQLITE_DBCONFIG_LOOKASIDE" and
1212       "SQLITE_DBCONFIG_MAINDBNAME" are not supported. See also
1213       "https://www.sqlite.org/capi3ref.html#sqlite3_db_config" for details.
1214
1215   $dbh->sqlite_create_module()
1216       Registers a name for a virtual table module. Module names must be
1217       registered before creating a new virtual table using the module and
1218       before using a preexisting virtual table for the module.  Virtual
1219       tables are explained in DBD::SQLite::VirtualTable.
1220
1221   $dbh->sqlite_limit( $category_id, $new_value )
1222       Sets a new run-time limit for the category, and returns the current
1223       limit.  If the new value is a negative number (or omitted), the limit
1224       is unchanged and just returns the current limit. Category ids
1225       (SQLITE_LIMIT_LENGTH, SQLITE_LIMIT_VARIABLE_NUMBER, etc) can be
1226       imported from DBD::SQLite::Constants.
1227
1228   $dbh->sqlite_get_autocommit()
1229       Returns true if the internal SQLite connection is in an autocommit
1230       mode.  This does not always return the same value as
1231       "$dbh->{AutoCommit}".  This returns false if you explicitly issue a
1232       "<BEGIN"> statement.
1233
1234   $dbh->sqlite_txn_state()
1235       Returns the internal transaction status of SQLite (not of DBI).  Return
1236       values (SQLITE_TXN_NONE, SQLITE_TXN_READ, SQLITE_TXN_WRITE) can be
1237       imported from DBD::SQLite::Constants. You may pass an optional schema
1238       name (usually "main"). If SQLite does not support this function, or if
1239       you pass a wrong schema name, -1 is returned.
1240
1241   $dbh->sqlite_error_offset()
1242       Returns the byte offset of the start of a problematic input SQL token
1243       or -1 if the most recent error does not reference a specific token in
1244       the input SQL (or DBD::SQLite is built with an older version of
1245       SQLite).
1246

DRIVER FUNCTIONS

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

DRIVER CONSTANTS

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

COLLATION FUNCTIONS

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

R* TREE SUPPORT

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

VIRTUAL TABLES IMPLEMENTED IN PERL

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

TO DO

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

SUPPORT

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

AUTHORS

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