1DBD::SQLite(3) User Contributed Perl Documentation DBD::SQLite(3)
2
6 DBD::SQLite - Self-contained RDBMS in a DBI Driver
7
9 use DBI;
10 my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");
11
13 SQLite is a public domain file-based relational database engine that
14 you can find at <https://www.sqlite.org/>.
15 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 SQLite supports the following features:
22 Implements a large subset of SQL92
24 See <https://www.sqlite.org/lang.html> for details.
25 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 Atomic commit and rollback
31 Yes, DBD::SQLite is small and light, but it supports full
32 transactions!
33 Extensible
35 User-defined aggregate or regular functions can be registered with
36 the SQL parser.
37 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
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 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 You can also find how the library is compiled by calling
56 DBD::SQLite::compile_options() (see below).
57
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 my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");
65 The file is opened in read/write mode, and will be created if it does
67 not exist yet.
68 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 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 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 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 As of 1.41_01, you can pass URI filename (see
89 <https://www.sqlite.org/uri.html>) as well for finer control:
90 my $dbh = DBI->connect("dbi:SQLite:uri=file:$path_to_dbfile?mode=rwc");
92 Note that this is not for remote SQLite database connection. You can
94 only connect to a local database.
95 Read-Only Database
97 You can set sqlite_open_flags (only) when you connect to a database:
98 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 See <https://www.sqlite.org/c3ref/open.html> for details.
105 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 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 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 ($fh, $filename) = tempfile($template, EXLOCK => 0);
124 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 (The above is quoted from the pod of File::Temp.)
132 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 DBD::SQLite and fork()
137 Follow the advice in the SQLite FAQ (<https://sqlite.org/faq.html>).
138 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 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 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 If you need a higher level of concurrency than SQLite supports,
153 consider using other client/server database engines.
154 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 dbish dbi:SQLite:foo.db
160 On the command line to access the file foo.db.
162 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 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 use DBI qw(:sql_types);
175 my $dbh = DBI->connect("dbi:SQLite:dbfile","","");
176 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 And then retrieval just works:
183 $sth = $dbh->prepare("SELECT * FROM mytable WHERE id = 1");
185 $sth->execute();
186 my $row = $sth->fetch;
187 my $blobo = $row->[1];
188 # now $blobo == $blob
190 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 my $sth = $dbh->prepare(q{
196 SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
197 });
198 $sth->execute(5);
199 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 SELECT bar FROM foo GROUP BY bar HAVING count(*) > "5";
205 There are four workarounds for this.
207 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 use DBI qw(:sql_types); # Don't forget this
213 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 Add zero to make it a number
221 This is somewhat weird, but works anyway.
222 my $sth = $dbh->prepare(q{
224 SELECT bar FROM foo GROUP BY bar HAVING count(*) > (? + 0);
225 });
226 $sth->execute(5);
227 Use SQL cast() function
229 This is more explicit way to do the above.
230 my $sth = $dbh->prepare(q{
232 SELECT bar FROM foo GROUP BY bar HAVING count(*) > cast(? as integer);
233 });
234 $sth->execute(5);
235 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 $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 You can set it to true when you connect to a database.
247 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 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 As of 1.41_04, "sqlite_see_if_its_a_number" works only for bind
262 values with no explicit type.
263 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 Placeholders
276 SQLite supports several placeholder expressions, including "?" and
277 ":AAAA". Consult the DBI and SQLite documentation for details.
278 <https://www.sqlite.org/lang_expr.html#varparam>
280 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 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 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 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 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 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 To fix this, You need to issue "PRAGMA journal_mode = DELETE" (or
315 "TRUNCATE") beforehand, or install a newer version of "sqlite3".
316 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 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 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 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 See <https://www.sqlite.org/pragma.html> for more details.
339 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 $dbh->do("PRAGMA foreign_keys = ON");
350 And you can explicitly disable the feature whenever you like by turning
352 the pragma off:
353 $dbh->do("PRAGMA foreign_keys = OFF");
355 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 See <https://www.sqlite.org/foreignkeys.html> for details.
365 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 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 $dbh->{AutoCommit} = 1;
379 $dbh->begin_work; # or $dbh->do('BEGIN TRANSACTION');
381 # $dbh->{AutoCommit} is turned off temporarily during a transaction;
383 $dbh->commit; # or $dbh->do('COMMIT');
385 # $dbh->{AutoCommit} is turned on again;
387 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 $dbh->{AutoCommit} = 0;
398 # $dbh->do('BEGIN TRANSACTION') is not necessary, but possible
400 ...
402 $dbh->commit; # or $dbh->do('COMMIT');
404 # $dbh->{AutoCommit} stays intact;
406 $dbh->{AutoCommit} = 1; # ends the transactional mode
408 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 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 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 my $dbh = DBI->connect("dbi:SQLite::memory:", "", "", {
428 sqlite_use_immediate_transaction => 0,
429 });
430 Or, issue a "BEGIN" statement explicitly each time you begin a
432 transaction.
433 See <http://sqlite.org/lockingv3.html> for locking details.
435 "$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 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 $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 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 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 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 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 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 Queries like count(*) and avg(bytes) took fractions of a second to
496 return, but what surprised him most of all was:
497 SELECT url, count(*) as count
499 FROM access_log
500 GROUP BY url
501 ORDER BY count desc
502 LIMIT 20
503 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 Oh yeah, and that was with no indexes on the table, on a 400MHz PIII.
509 For best performance be sure to tune your hdparm settings if you are
511 using linux. Also you might want to set:
512 PRAGMA synchronous = OFF
514 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 The memory usage of SQLite can also be tuned using the cache_size
520 pragma.
521 $dbh->do("PRAGMA cache_size = 800000");
523 The above will allocate 800M for DB cache; the default is 2M. Your
525 sweet spot probably lies somewhere in between.
526
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 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 Accepted values are the following constants:
540 • 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 • 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 This is appropriate for strings that are decoded to characters
553 via, e.g., "decode" in Encode.
554 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 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 # Binary_data will be stored as is.
568 $sth->bind_param(1, $binary_data, SQL_BLOB);
569 Defining the column type as "BLOB" in the DDL is not
571 sufficient.
572 • 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 • 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 • 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 "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 Prefer "sqlite_string_mode" in all new code.
596 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 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 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 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 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 sqlite_defensive
621 If set to true, language features that allow ordinary SQL to
622 deliberately corrupt the database file are prohibited.
623 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
631 See also to the DBI documentation for the details of other common
632 methods.
633 table_info
635 $sth = $dbh->table_info(undef, $schema, $table, $type, \%attr);
636 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 The following fields are returned:
646 TABLE_CAT: Always NULL, as SQLite does not have the concept of
648 catalogs.
649 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 TABLE_NAME: The name of the table or view.
656 TABLE_TYPE: The type of object returned. Will be one of 'TABLE',
658 'VIEW', 'LOCAL TEMPORARY' or 'SYSTEM TABLE'.
659 primary_key, primary_key_info
661 @names = $dbh->primary_key(undef, $schema, $table);
662 $sth = $dbh->primary_key_info(undef, $schema, $table, \%attr);
663 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 foreign_key_info
671 $sth = $dbh->foreign_key_info(undef, $pk_schema, $pk_table,
672 undef, $fk_schema, $fk_table);
673 Returns information about foreign key constraints, as specified in
675 "foreign_key_info" in DBI, but with some limitations :
676 • 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 The following nonempty fields are returned :
682 PKTABLE_NAME: The primary (unique) key table identifier.
684 PKCOLUMN_NAME: The primary (unique) key column identifier.
686 FKTABLE_NAME: The foreign key table identifier.
688 FKCOLUMN_NAME: The foreign key column identifier.
690 KEY_SEQ: The column sequence number (starting with 1), when several
692 columns belong to a same constraint.
693 UPDATE_RULE: The referential action for the UPDATE rule. The following
695 codes are defined:
696 CASCADE 0
698 RESTRICT 1
699 SET NULL 2
700 NO ACTION 3
701 SET DEFAULT 4
702 Default is 3 ('NO ACTION').
704 DELETE_RULE: The referential action for the DELETE rule. The codes are
706 the same as for UPDATE_RULE.
707 DEFERRABILITY: The following codes are defined:
709 INITIALLY DEFERRED 5
711 INITIALLY IMMEDIATE 6
712 NOT DEFERRABLE 7
713 UNIQUE_OR_PRIMARY: Whether the column is primary or unique.
715 Note: foreign key support in SQLite must be explicitly turned on
717 through a "PRAGMA" command; see "Foreign keys" earlier in this manual.
718 statistics_info
720 $sth = $dbh->statistics_info(undef, $schema, $table,
721 $unique_only, $quick);
722 Returns information about a table and it's indexes, as specified in
724 "statistics_info" in DBI, but with some limitations :
725 • 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 The following nonempty fields are returned :
731 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 TABLE_NAME: The name of the table
737 NON_UNIQUE: Contains 0 for unique indexes, 1 for non-unique indexes
739 INDEX_NAME: The name of the index
741 TYPE: SQLite uses 'btree' for all it's indexes
743 ORDINAL_POSITION: Column sequence number (starting with 1).
745 COLUMN_NAME: The name of the column
747 ping
749 my $bool = $dbh->ping;
750 returns true if the database file exists (or the database is in-
752 memory), and the database connection is active.
753
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 $dbh->func( ..., "(method name without sqlite_ prefix)" );
763 Exception: "sqlite_trace" should always be called as is, even with
765 func() method (to avoid conflict with DBI's trace() method).
766 $dbh->func( ..., "sqlite_trace");
768 $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 Generally you should not be using this method. Use the DBI
776 last_insert_id method instead. The usage of this is:
777 $h->last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ])
779 Running "$h->last_insert_id("","","","")" is the equivalent of running
781 "$dbh->sqlite_last_insert_rowid()" directly.
782 $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 $dbh->sqlite_busy_timeout()
788 Retrieve the current busy timeout.
789 $dbh->sqlite_busy_timeout( $ms )
791 Set the current busy timeout. The timeout is in milliseconds.
792 $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 $name
798 The name of the function. This is the name of the function as it
799 will be used from SQL.
800 $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 $code_ref
806 This should be a reference to the function's implementation.
807 $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 For example, here is how to define a now() function which returns the
817 current number of seconds since the epoch:
818 $dbh->sqlite_create_function( 'now', 0, sub { return time } );
820 After this, it could be used from SQL as:
822 INSERT INTO mytable ( now() );
824 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 $dbh->sqlite_create_function( 'md5', 1, sub { return [md5($_[0]), SQL_BLOB] } );
832 REGEXP function
834 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 SELECT * from table WHERE column REGEXP '\bA\w+'
842 If you want case-insensitive searching, use perl regex flags, like this
844 :
845 SELECT * from table WHERE column REGEXP '(?i:\bA\w+)'
847 The default REGEXP implementation can be overridden through the
849 "create_function" API described above.
850 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 $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 The method's parameters are:
861 $name
863 The name of the function exposed to SQL.
864 $code_ref
866 Reference to the function's implementation. The driver will check
867 that this is a proper sorting function.
868 $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 $code_ref->($dbh, $collation_name)
875 and should register the desired collation using
877 "sqlite_create_collation".
878 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 $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 $name
889 The name of the aggregate function, this is the name under which
890 the function will be available from SQL.
891 $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 $pkg
898 This is the package which implements the aggregator interface.
899 $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 The aggregator interface consists of defining three methods:
909 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 step(@_)
917 This method will be called once for each row in the aggregate.
918 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 Here is a simple aggregate function which returns the variance (example
926 adapted from pysqlite):
927 package variance;
929 sub new { bless [], shift; }
931 sub step {
933 my ( $self, $value ) = @_;
934 push @$self, $value;
936 }
937 sub finalize {
939 my $self = $_[0];
940 my $n = @$self;
942 # Variance is NULL unless there is more than one row
944 return undef unless $n || $n == 1;
945 my $mu = 0;
947 foreach my $v ( @$self ) {
948 $mu += $v;
949 }
950 $mu /= $n;
951 my $sigma = 0;
953 foreach my $v ( @$self ) {
954 $sigma += ($v - $mu)**2;
955 }
956 $sigma = $sigma / ($n - 1);
957 return $sigma;
959 }
960 $dbh->sqlite_create_aggregate( "variance", 1, 'variance' );
962 The aggregate function can then be used as:
964 SELECT group_name, variance(score)
966 FROM results
967 GROUP BY group_name;
968 For more examples, see the DBD::SQLite::Cookbook.
970 $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 An example use for this interface is to keep a GUI updated during a
976 large query. The parameters are:
977 $n_opcodes
979 The progress handler is invoked once for every $n_opcodes virtual
980 machine opcodes in SQLite.
981 $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 Set this argument to "undef" if you want to unregister a previous
988 progress handler.
989 $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 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 $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 $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 The callback will be called as
1017 $code_ref->($action_code, $database, $table, $rowid)
1019 where
1021 $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 $database
1028 is the name of the database containing the affected row;
1029 $table
1031 is the name of the table containing the affected row;
1032 $rowid
1034 is the unique 64-bit signed integer key of the affected row within
1035 that table.
1036 $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 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 The callback will be called as
1058 $code_ref->($action_code, $string1, $string2, $database, $trigger_or_view)
1060 where
1062 $action_code
1064 is an integer that specifies what action is being authorized (see
1065 "Action Codes").
1066 $string1, $string2
1068 are strings that depend on the action code (see "Action Codes").
1069 $database
1071 is the name of the database ("main", "temp", etc.) if applicable.
1072 $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 $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 $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 $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 $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 $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 $dbh->sqlite_enable_load_extension(1);
1108 $sth = $dbh->prepare("select load_extension('libmemvfs.so')")
1109 or die "Cannot prepare: " . $dbh->errstr();
1110 $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 $dbh->sqlite_enable_load_extension(1);
1120 $dbh->sqlite_load_extension('libsqlitefunctions.so')
1121 or die "Cannot load extension: " . $dbh->errstr();
1122 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 $dbh->sqlite_trace( $code_ref )
1129 This method registers a trace callback to be invoked whenever SQL
1130 statements are being run.
1131 The callback will be called as
1133 $code_ref->($statement)
1135 where
1137 $statement
1139 is a UTF-8 rendering of the SQL statement text as the statement
1140 first begins executing.
1141 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 See also "TRACING" in DBI for better tracing options.
1147 $dbh->sqlite_profile( $code_ref )
1149 This method registers a profile callback to be invoked whenever a SQL
1150 statement finishes.
1151 The callback will be called as
1153 $code_ref->($statement, $elapsed_time)
1155 where
1157 $statement
1159 is the original statement text (without bind parameters).
1160 $elapsed_time
1162 is an estimate of wall-clock time of how long that statement took
1163 to run (in milliseconds).
1164 This method is considered experimental and is subject to change in
1166 future versions of SQLite.
1167 See also DBI::Profile for better profiling options.
1169 $dbh->sqlite_table_column_metadata( $dbname, $tablename, $columnname )
1171 is for internal use only.
1172 $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 $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 my $status = $sth->sqlite_st_status();
1186 my $cur = $status->{fullscan_step};
1187 You may also pass 0 as an argument to reset the status.
1189 $dbh->sqlite_db_config( $id, $new_integer_value )
1191 You can change how the connected database should behave like this:
1192 use DBD::SQLite::Constants qw/:database_connection_configuration_options/;
1194 my $dbh = DBI->connect('dbi:SQLite::memory:');
1196 # 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 # This disables two-arg version of fts3_tokenizer.
1202 $dbh->sqlite_db_config( SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, 0 );
1203 "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 my $current_value = $dbh->sqlite_db_config( SQLITE_DBCONFIG_DEFENSIVE, -1 );
1209 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 $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 $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 $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 $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 $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
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 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 my $status = DBD::SQLite::sqlite_status();
1260 my $cur = $status->{memory_used}{current};
1261 my $high = $status->{memory_used}{highwater};
1262 You may also pass 0 as an argument to reset the status.
1264 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
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 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 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 Authorizer Return Codes
1292 OK
1293 DENY
1294 IGNORE
1295 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 # 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
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 Builtin collation sequences
1349 The following collation sequences are builtin within SQLite :
1350 BINARY
1352 Compares string data using memcmp(), regardless of text encoding.
1353 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 RTRIM
1362 The same as binary, except that trailing space characters are
1363 ignored.
1364 In addition, "DBD::SQLite" automatically installs the following
1366 collation sequences :
1367 perl
1369 corresponds to the Perl "cmp" operator
1370 perllocale
1372 Perl "cmp" operator, in a context where "use locale" is activated.
1373 Usage
1375 You can write for example
1376 CREATE TABLE foo(
1378 txt1 COLLATE perl,
1379 txt2 COLLATE perllocale,
1380 txt3 COLLATE nocase
1381 )
1382 or
1384 SELECT * FROM foo ORDER BY name COLLATE perllocale
1386 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 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 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 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 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 The builtin "perl" or "perllocale" collations are predefined in that
1428 same hash.
1429 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 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 Explanations for using this feature are provided in a separate
1453 document: see DBD::SQLite::Fulltext_search.
1454
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 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 then query which buildings overlap or are contained within a specified
1469 region:
1470 # 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 # ... 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 my $contained = $dbh->selectcol_arrayref($contained_sql,undef,
1484 $minLong, $maxLong, $minLat, $maxLat);
1485 my $overlapping = $dbh->selectcol_arrayref($overlap_sql,undef,
1487 $minLong, $maxLong, $minLat, $maxLat);
1488 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
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 "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 • 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 • 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 Other Perl virtual tables may also be published separately on CPAN.
1517
1519 The following items remain to be done.
1520 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 Stream API for Blobs
1527 Reading/writing into blobs using "sqlite2_blob_open" /
1528 "sqlite2_blob_close".
1529 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
1537 Bugs should be reported to GitHub issues:
1538 <https://github.com/DBD-SQLite/DBD-SQLite/issues>
1540 or via RT if you prefer:
1542 <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
1544 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 The master repository is on GitHub:
1550 <https://github.com/DBD-SQLite/DBD-SQLite>.
1552 We also have a mailing list:
1554 <http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite>
1556
1558 Matt Sergeant <matt@sergeant.org>
1559 Francis J. Lacoste <flacoste@logreport.org>
1561 Wolfgang Sourdeau <wolfgang@logreport.org>
1563 Adam Kennedy <adamk@cpan.org>
1565 Max Maischein <corion@cpan.org>
1567 Laurent Dami <dami@cpan.org>
1569 Kenichi Ishigaki <ishigaki@cpan.org>
1571
1573 The bundled SQLite code in this distribution is Public Domain.
1574 DBD::SQLite is copyright 2002 - 2007 Matt Sergeant.
1576 Some parts copyright 2008 Francis J. Lacoste.
1578 Some parts copyright 2008 Wolfgang Sourdeau.
1580 Some parts copyright 2008 - 2013 Adam Kennedy.
1582 Some parts copyright 2009 - 2013 Kenichi Ishigaki.
1584 Some parts derived from DBD::SQLite::Amalgamation copyright 2008 Audrey
1586 Tang.
1587 This program is free software; you can redistribute it and/or modify it
1589 under the same terms as Perl itself.
1590 The full text of the license can be found in the LICENSE file included
1592 with this module.
1593perl v5.38.0 2023-07-20 DBD::SQLite(3)