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.36.0 as of this release) for consistency. However, a
47 different version of SQLite may sometimes be used for some reasons like
48 security, or some new experimental features.
49 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
128 disconnect databases explicitly before the temporary directory is
129 gone (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('libsqlitefunctions.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 you need to, say,
1114 create other functions from an extension, use this method. $file (a
1115 path to the extension) is mandatory, and $proc (an entry point name) is
1116 optional. You need to call "sqlite_enable_load_extension" before
1117 calling "sqlite_load_extension".
1118 If the extension uses SQLite mutex functions like
1120 "sqlite3_mutex_enter", then the extension should be compiled with the
1121 same "SQLITE_THREADSAFE" compile-time setting as this module, see
1122 "DBD::SQLite::compile_options()".
1123 $dbh->sqlite_trace( $code_ref )
1125 This method registers a trace callback to be invoked whenever SQL
1126 statements are being run.
1127 The callback will be called as
1129 $code_ref->($statement)
1131 where
1133 $statement
1135 is a UTF-8 rendering of the SQL statement text as the statement
1136 first begins executing.
1137 Additional callbacks might occur as each triggered subprogram is
1139 entered. The callbacks for triggers contain a UTF-8 SQL comment that
1140 identifies the trigger.
1141 See also "TRACING" in DBI for better tracing options.
1143 $dbh->sqlite_profile( $code_ref )
1145 This method registers a profile callback to be invoked whenever a SQL
1146 statement finishes.
1147 The callback will be called as
1149 $code_ref->($statement, $elapsed_time)
1151 where
1153 $statement
1155 is the original statement text (without bind parameters).
1156 $elapsed_time
1158 is an estimate of wall-clock time of how long that statement took
1159 to run (in milliseconds).
1160 This method is considered experimental and is subject to change in
1162 future versions of SQLite.
1163 See also DBI::Profile for better profiling options.
1165 $dbh->sqlite_table_column_metadata( $dbname, $tablename, $columnname )
1167 is for internal use only.
1168 $dbh->sqlite_db_status()
1170 Returns a hash reference that holds a set of status information of
1171 database connection such as cache usage. See
1172 <https://www.sqlite.org/c3ref/c_dbstatus_options.html> for details. You
1173 may also pass 0 as an argument to reset the status.
1174 $sth->sqlite_st_status()
1176 Returns a hash reference that holds a set of status information of
1177 SQLite statement handle such as full table scan count. See
1178 <https://www.sqlite.org/c3ref/c_stmtstatus_counter.html> for details.
1179 Statement status only holds the current value.
1180 my $status = $sth->sqlite_st_status();
1182 my $cur = $status->{fullscan_step};
1183 You may also pass 0 as an argument to reset the status.
1185 $dbh->sqlite_db_config( $id, $new_integer_value )
1187 You can change how the connected database should behave like this:
1188 use DBD::SQLite::Constants qw/:database_connection_configuration_options/;
1190 my $dbh = DBI->connect('dbi:SQLite::memory:');
1192 # This disables language features that allow ordinary SQL
1194 # to deliberately corrupt the database file
1195 $dbh->sqlite_db_config( SQLITE_DBCONFIG_DEFENSIVE, 1 );
1196 # This disables two-arg version of fts3_tokenizer.
1198 $dbh->sqlite_db_config( SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, 0 );
1199 "sqlite_db_config" returns the new value after the call. If you just
1201 want to know the current value without changing anything, pass a
1202 negative integer value.
1203 my $current_value = $dbh->sqlite_db_config( SQLITE_DBCONFIG_DEFENSIVE, -1 );
1205 As of this writing, "sqlite_db_config" only supports options that set
1207 an integer value. "SQLITE_DBCONFIG_LOOKASIDE" and
1208 "SQLITE_DBCONFIG_MAINDBNAME" are not supported. See also
1209 "https://www.sqlite.org/capi3ref.html#sqlite3_db_config" for details.
1210 $dbh->sqlite_create_module()
1212 Registers a name for a virtual table module. Module names must be
1213 registered before creating a new virtual table using the module and
1214 before using a preexisting virtual table for the module. Virtual
1215 tables are explained in DBD::SQLite::VirtualTable.
1216 $dbh->sqlite_limit( $category_id, $new_value )
1218 Sets a new run-time limit for the category, and returns the current
1219 limit. If the new value is a negative number (or omitted), the limit
1220 is unchanged and just returns the current limit. Category ids
1221 (SQLITE_LIMIT_LENGTH, SQLITE_LIMIT_VARIABLE_NUMBER, etc) can be
1222 imported from DBD::SQLite::Constants.
1223 $dbh->sqlite_get_autocommit()
1225 Returns true if the internal SQLite connection is in an autocommit
1226 mode. This does not always return the same value as
1227 "$dbh->{AutoCommit}". This returns false if you explicitly issue a
1228 "<BEGIN"> statement.
1229 $dbh->sqlite_txn_state()
1231 Returns the internal transaction status of SQLite (not of DBI). Return
1232 values (SQLITE_TXN_NONE, SQLITE_TXN_READ, SQLITE_TXN_WRITE) can be
1233 imported from DBD::SQLite::Constants. You may pass an optional schema
1234 name (usually "main"). If SQLite does not support this function, or if
1235 you pass a wrong schema name, -1 is returned.
1236
1238 DBD::SQLite::compile_options()
1239 Returns an array of compile options (available since SQLite 3.6.23,
1240 bundled in DBD::SQLite 1.30_01), or an empty array if the bundled
1241 library is old or compiled with SQLITE_OMIT_COMPILEOPTION_DIAGS.
1242 DBD::SQLite::sqlite_status()
1244 Returns a hash reference that holds a set of status information of
1245 SQLite runtime such as memory usage or page cache usage (see
1246 <https://www.sqlite.org/c3ref/c_status_malloc_count.html> for details).
1247 Each of the entry contains the current value and the highwater value.
1248 my $status = DBD::SQLite::sqlite_status();
1250 my $cur = $status->{memory_used}{current};
1251 my $high = $status->{memory_used}{highwater};
1252 You may also pass 0 as an argument to reset the status.
1254 DBD::SQLite::strlike($pattern, $string, $escape_char),
1256 DBD::SQLite::strglob($pattern, $string)
1257 As of 1.49_05 (SQLite 3.10.0), you can use these two functions to see
1258 if a string matches a pattern. These may be useful when you create a
1259 virtual table or a custom function. See
1260 <http://sqlite.org/c3ref/strlike.html> and
1261 <http://sqlite.org/c3ref/strglob.html> for details.
1262
1264 A subset of SQLite C constants are made available to Perl, because they
1265 may be needed when writing hooks or authorizer callbacks. For accessing
1266 such constants, the "DBD::SQLite" module must be explicitly "use"d at
1267 compile time. For example, an authorizer that forbids any DELETE
1268 operation would be written as follows :
1269 use DBD::SQLite;
1271 $dbh->sqlite_set_authorizer(sub {
1272 my $action_code = shift;
1273 return $action_code == DBD::SQLite::DELETE ? DBD::SQLite::DENY
1274 : DBD::SQLite::OK;
1275 });
1276 The list of constants implemented in "DBD::SQLite" is given below; more
1278 information can be found ad at
1279 <https://www.sqlite.org/c3ref/constlist.html>.
1280 Authorizer Return Codes
1282 OK
1283 DENY
1284 IGNORE
1285 Action Codes
1287 The "set_authorizer" method registers a callback function that is
1288 invoked to authorize certain SQL statement actions. The first parameter
1289 to the callback is an integer code that specifies what action is being
1290 authorized. The second and third parameters to the callback are
1291 strings, the meaning of which varies according to the action code.
1292 Below is the list of action codes, together with their associated
1293 strings.
1294 # constant string1 string2
1296 # ======== ======= =======
1297 CREATE_INDEX Index Name Table Name
1298 CREATE_TABLE Table Name undef
1299 CREATE_TEMP_INDEX Index Name Table Name
1300 CREATE_TEMP_TABLE Table Name undef
1301 CREATE_TEMP_TRIGGER Trigger Name Table Name
1302 CREATE_TEMP_VIEW View Name undef
1303 CREATE_TRIGGER Trigger Name Table Name
1304 CREATE_VIEW View Name undef
1305 DELETE Table Name undef
1306 DROP_INDEX Index Name Table Name
1307 DROP_TABLE Table Name undef
1308 DROP_TEMP_INDEX Index Name Table Name
1309 DROP_TEMP_TABLE Table Name undef
1310 DROP_TEMP_TRIGGER Trigger Name Table Name
1311 DROP_TEMP_VIEW View Name undef
1312 DROP_TRIGGER Trigger Name Table Name
1313 DROP_VIEW View Name undef
1314 INSERT Table Name undef
1315 PRAGMA Pragma Name 1st arg or undef
1316 READ Table Name Column Name
1317 SELECT undef undef
1318 TRANSACTION Operation undef
1319 UPDATE Table Name Column Name
1320 ATTACH Filename undef
1321 DETACH Database Name undef
1322 ALTER_TABLE Database Name Table Name
1323 REINDEX Index Name undef
1324 ANALYZE Table Name undef
1325 CREATE_VTABLE Table Name Module Name
1326 DROP_VTABLE Table Name Module Name
1327 FUNCTION undef Function Name
1328 SAVEPOINT Operation Savepoint Name
1329
1331 Definition
1332 SQLite v3 provides the ability for users to supply arbitrary comparison
1333 functions, known as user-defined "collation sequences" or "collating
1334 functions", to be used for comparing two text values.
1335 <https://www.sqlite.org/datatype3.html#collation> explains how
1336 collations are used in various SQL expressions.
1337 Builtin collation sequences
1339 The following collation sequences are builtin within SQLite :
1340 BINARY
1342 Compares string data using memcmp(), regardless of text encoding.
1343 NOCASE
1345 The same as binary, except the 26 upper case characters of ASCII
1346 are folded to their lower case equivalents before the comparison is
1347 performed. Note that only ASCII characters are case folded. SQLite
1348 does not attempt to do full UTF case folding due to the size of the
1349 tables required.
1350 RTRIM
1352 The same as binary, except that trailing space characters are
1353 ignored.
1354 In addition, "DBD::SQLite" automatically installs the following
1356 collation sequences :
1357 perl
1359 corresponds to the Perl "cmp" operator
1360 perllocale
1362 Perl "cmp" operator, in a context where "use locale" is activated.
1363 Usage
1365 You can write for example
1366 CREATE TABLE foo(
1368 txt1 COLLATE perl,
1369 txt2 COLLATE perllocale,
1370 txt3 COLLATE nocase
1371 )
1372 or
1374 SELECT * FROM foo ORDER BY name COLLATE perllocale
1376 Unicode handling
1378 If the attribute "$dbh->{sqlite_unicode}" is set, strings coming from
1379 the database and passed to the collation function will be properly
1380 tagged with the utf8 flag; but this only works if the "sqlite_unicode"
1381 attribute is set before the first call to a perl collation sequence .
1382 The recommended way to activate unicode is to set the parameter at
1383 connection time :
1384 my $dbh = DBI->connect(
1386 "dbi:SQLite:dbname=foo", "", "",
1387 {
1388 RaiseError => 1,
1389 sqlite_unicode => 1,
1390 }
1391 );
1392 Adding user-defined collations
1394 The native SQLite API for adding user-defined collations is exposed
1395 through methods "sqlite_create_collation" and
1396 "sqlite_collation_needed".
1397 To avoid calling these functions every time a $dbh handle is created,
1399 "DBD::SQLite" offers a simpler interface through the
1400 %DBD::SQLite::COLLATION hash : just insert your own collation functions
1401 in that hash, and whenever an unknown collation name is encountered in
1402 SQL, the appropriate collation function will be loaded on demand from
1403 the hash. For example, here is a way to sort text values regardless of
1404 their accented characters :
1405 use DBD::SQLite;
1407 $DBD::SQLite::COLLATION{no_accents} = sub {
1408 my ( $a, $b ) = map lc, @_;
1409 tr[����������������������������]
1410 [aaaaaacdeeeeiiiinoooooouuuuy] for $a, $b;
1411 $a cmp $b;
1412 };
1413 my $dbh = DBI->connect("dbi:SQLite:dbname=dbfile");
1414 my $sql = "SELECT ... FROM ... ORDER BY ... COLLATE no_accents");
1415 my $rows = $dbh->selectall_arrayref($sql);
1416 The builtin "perl" or "perllocale" collations are predefined in that
1418 same hash.
1419 The COLLATION hash is a global registry within the current process;
1421 hence there is a risk of undesired side-effects. Therefore, to prevent
1422 action at distance, the hash is implemented as a "write-only" hash,
1423 that will happily accept new entries, but will raise an exception if
1424 any attempt is made to override or delete a existing entry (including
1425 the builtin "perl" and "perllocale").
1426 If you really, really need to change or delete an entry, you can always
1428 grab the tied object underneath %DBD::SQLite::COLLATION --- but don't
1429 do that unless you really know what you are doing. Also observe that
1430 changes in the global hash will not modify existing collations in
1431 existing database handles: it will only affect new requests for
1432 collations. In other words, if you want to change the behaviour of a
1433 collation within an existing $dbh, you need to call the
1434 "create_collation" method directly.
1435
1437 SQLite is bundled with an extension module for full-text indexing.
1438 Tables with this feature enabled can be efficiently queried to find
1439 rows that contain one or more instances of some specified words, in any
1440 column, even if the table contains many large documents.
1441 Explanations for using this feature are provided in a separate
1443 document: see DBD::SQLite::Fulltext_search.
1444
1446 The RTREE extension module within SQLite adds support for creating a
1447 R-Tree, a special index for range and multidimensional queries. This
1448 allows users to create tables that can be loaded with (as an example)
1449 geospatial data such as latitude/longitude coordinates for buildings
1450 within a city :
1451 CREATE VIRTUAL TABLE city_buildings USING rtree(
1453 id, -- Integer primary key
1454 minLong, maxLong, -- Minimum and maximum longitude
1455 minLat, maxLat -- Minimum and maximum latitude
1456 );
1457 then query which buildings overlap or are contained within a specified
1459 region:
1460 # IDs that are contained within query coordinates
1462 my $contained_sql = <<"";
1463 SELECT id FROM city_buildings
1464 WHERE minLong >= ? AND maxLong <= ?
1465 AND minLat >= ? AND maxLat <= ?
1466 # ... and those that overlap query coordinates
1468 my $overlap_sql = <<"";
1469 SELECT id FROM city_buildings
1470 WHERE maxLong >= ? AND minLong <= ?
1471 AND maxLat >= ? AND minLat <= ?
1472 my $contained = $dbh->selectcol_arrayref($contained_sql,undef,
1474 $minLong, $maxLong, $minLat, $maxLat);
1475 my $overlapping = $dbh->selectcol_arrayref($overlap_sql,undef,
1477 $minLong, $maxLong, $minLat, $maxLat);
1478 For more detail, please see the SQLite R-Tree page
1480 (<https://www.sqlite.org/rtree.html>). Note that custom R-Tree queries
1481 using callbacks, as mentioned in the prior link, have not been
1482 implemented yet.
1483
1485 SQLite has a concept of "virtual tables" which look like regular tables
1486 but are implemented internally through specific functions. The
1487 fulltext or R* tree features described in the previous chapters are
1488 examples of such virtual tables, implemented in C code.
1489 "DBD::SQLite" also supports virtual tables implemented in Perl code:
1491 see DBD::SQLite::VirtualTable for using or implementing such virtual
1492 tables. These can have many interesting uses for joining regular DBMS
1493 data with some other kind of data within your Perl programs. Bundled
1494 with the present distribution are :
1495 • DBD::SQLite::VirtualTable::FileContent : implements a virtual
1497 column that exposes file contents. This is especially useful in
1498 conjunction with a fulltext index; see
1499 DBD::SQLite::Fulltext_search.
1500 • DBD::SQLite::VirtualTable::PerlData : binds to a Perl array within
1502 the Perl program. This can be used for simple import/export
1503 operations, for debugging purposes, for joining data from different
1504 sources, etc.
1505 Other Perl virtual tables may also be published separately on CPAN.
1507
1509 The following items remain to be done.
1510 Leak Detection
1512 Implement one or more leak detection tests that only run during
1513 AUTOMATED_TESTING and RELEASE_TESTING and validate that none of the C
1514 code we work with leaks.
1515 Stream API for Blobs
1517 Reading/writing into blobs using "sqlite2_blob_open" /
1518 "sqlite2_blob_close".
1519 Support for custom callbacks for R-Tree queries
1521 Custom queries of a R-Tree index using a callback are possible with the
1522 SQLite C API (<https://www.sqlite.org/rtree.html>), so one could
1523 potentially use a callback that narrowed the result set down based on a
1524 specific need, such as querying for overlapping circles.
1525
1527 Bugs should be reported to GitHub issues:
1528 <https://github.com/DBD-SQLite/DBD-SQLite/issues>
1530 or via RT if you prefer:
1532 <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
1534 Note that bugs of bundled SQLite library (i.e. bugs in "sqlite3.[ch]")
1536 should be reported to the SQLite developers at sqlite.org via their bug
1537 tracker or via their mailing list.
1538 The master repository is on GitHub:
1540 <https://github.com/DBD-SQLite/DBD-SQLite>.
1542 We also have a mailing list:
1544 <http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite>
1546
1548 Matt Sergeant <matt@sergeant.org>
1549 Francis J. Lacoste <flacoste@logreport.org>
1551 Wolfgang Sourdeau <wolfgang@logreport.org>
1553 Adam Kennedy <adamk@cpan.org>
1555 Max Maischein <corion@cpan.org>
1557 Laurent Dami <dami@cpan.org>
1559 Kenichi Ishigaki <ishigaki@cpan.org>
1561
1563 The bundled SQLite code in this distribution is Public Domain.
1564 DBD::SQLite is copyright 2002 - 2007 Matt Sergeant.
1566 Some parts copyright 2008 Francis J. Lacoste.
1568 Some parts copyright 2008 Wolfgang Sourdeau.
1570 Some parts copyright 2008 - 2013 Adam Kennedy.
1572 Some parts copyright 2009 - 2013 Kenichi Ishigaki.
1574 Some parts derived from DBD::SQLite::Amalgamation copyright 2008 Audrey
1576 Tang.
1577 This program is free software; you can redistribute it and/or modify it
1579 under the same terms as Perl itself.
1580 The full text of the license can be found in the LICENSE file included
1582 with this module.
1583perl v5.36.0 2022-07-22 DBD::SQLite(3)