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.32.3 as of this release) for consistency. However, a
47 different version of SQLite may sometimes be used for some reasons like
48 security, or some new experimental features.
49 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_unicode
534 If set to a true value, DBD::SQLite will turn the UTF-8 flag on for
535 all text strings coming out of the database (this feature is
536 currently disabled for perl < 5.8.5). For more details on the UTF-8
537 flag see perlunicode. The default is for the UTF-8 flag to be
538 turned off.
539 Also note that due to some bizarreness in SQLite's type system (see
541 <https://www.sqlite.org/datatype3.html>), if you want to retain
542 blob-style behavior for some columns under "$dbh->{sqlite_unicode}
543 = 1" (say, to store images in the database), you have to state so
544 explicitly using the 3-argument form of "bind_param" in DBI when
545 doing updates:
546 use DBI qw(:sql_types);
548 $dbh->{sqlite_unicode} = 1;
549 my $sth = $dbh->prepare("INSERT INTO mytable (blobcolumn) VALUES (?)");
550 # Binary_data will be stored as is.
552 $sth->bind_param(1, $binary_data, SQL_BLOB);
553 Defining the column type as "BLOB" in the DDL is not sufficient.
555 This attribute was originally named as "unicode", and renamed to
557 "sqlite_unicode" for integrity since version 1.26_06. Old "unicode"
558 attribute is still accessible but will be deprecated in the near
559 future.
560 sqlite_allow_multiple_statements
562 If you set this to true, "do" method will process multiple
563 statements at one go. This may be handy, but with performance
564 penalty. See above for details.
565 sqlite_use_immediate_transaction
567 If you set this to true, DBD::SQLite tries to issue a "begin
568 immediate transaction" (instead of "begin transaction") when
569 necessary. See above for details.
570 As of version 1.38_01, this attribute is set to true by default.
572 If you really need to use "deferred" transactions for some reasons,
573 set this to false explicitly.
574 sqlite_see_if_its_a_number
576 If you set this to true, DBD::SQLite tries to see if the bind
577 values are number or not, and does not quote if they are numbers.
578 See above for details.
579 sqlite_extended_result_codes
581 If set to true, DBD::SQLite uses extended result codes where
582 appropriate (see <https://www.sqlite.org/rescode.html>).
583 sqlite_defensive
585 If set to true, language features that allow ordinary SQL to
586 deliberately corrupt the database file are prohibited.
587 Statement Handle Attributes
589 sqlite_unprepared_statements
590 Returns an unprepared part of the statement you pass to "prepare".
591 Typically this contains nothing but white spaces after a semicolon.
592 See above for details.
593
595 See also to the DBI documentation for the details of other common
596 methods.
597 table_info
599 $sth = $dbh->table_info(undef, $schema, $table, $type, \%attr);
600 Returns all tables and schemas (databases) as specified in "table_info"
602 in DBI. The schema and table arguments will do a "LIKE" search. You
603 can specify an ESCAPE character by including an 'Escape' attribute in
604 \%attr. The $type argument accepts a comma separated list of the
605 following types 'TABLE', 'VIEW', 'LOCAL TEMPORARY' and 'SYSTEM TABLE'
606 (by default all are returned). Note that a statement handle is
607 returned, and not a direct list of tables.
608 The following fields are returned:
610 TABLE_CAT: Always NULL, as SQLite does not have the concept of
612 catalogs.
613 TABLE_SCHEM: The name of the schema (database) that the table or view
615 is in. The default schema is 'main', temporary tables are in 'temp' and
616 other databases will be in the name given when the database was
617 attached.
618 TABLE_NAME: The name of the table or view.
620 TABLE_TYPE: The type of object returned. Will be one of 'TABLE',
622 'VIEW', 'LOCAL TEMPORARY' or 'SYSTEM TABLE'.
623 primary_key, primary_key_info
625 @names = $dbh->primary_key(undef, $schema, $table);
626 $sth = $dbh->primary_key_info(undef, $schema, $table, \%attr);
627 You can retrieve primary key names or more detailed information. As
629 noted above, SQLite does not have the concept of catalogs, so the first
630 argument of the methods is usually "undef", and you'll usually set
631 "undef" for the second one (unless you want to know the primary keys of
632 temporary tables).
633 foreign_key_info
635 $sth = $dbh->foreign_key_info(undef, $pk_schema, $pk_table,
636 undef, $fk_schema, $fk_table);
637 Returns information about foreign key constraints, as specified in
639 "foreign_key_info" in DBI, but with some limitations :
640 · information in rows returned by the $sth is incomplete with respect
642 to the "foreign_key_info" in DBI specification. All requested
643 fields are present, but the content is "undef" for some of them.
644 The following nonempty fields are returned :
646 PKTABLE_NAME: The primary (unique) key table identifier.
648 PKCOLUMN_NAME: The primary (unique) key column identifier.
650 FKTABLE_NAME: The foreign key table identifier.
652 FKCOLUMN_NAME: The foreign key column identifier.
654 KEY_SEQ: The column sequence number (starting with 1), when several
656 columns belong to a same constraint.
657 UPDATE_RULE: The referential action for the UPDATE rule. The following
659 codes are defined:
660 CASCADE 0
662 RESTRICT 1
663 SET NULL 2
664 NO ACTION 3
665 SET DEFAULT 4
666 Default is 3 ('NO ACTION').
668 DELETE_RULE: The referential action for the DELETE rule. The codes are
670 the same as for UPDATE_RULE.
671 DEFERRABILITY: The following codes are defined:
673 INITIALLY DEFERRED 5
675 INITIALLY IMMEDIATE 6
676 NOT DEFERRABLE 7
677 UNIQUE_OR_PRIMARY: Whether the column is primary or unique.
679 Note: foreign key support in SQLite must be explicitly turned on
681 through a "PRAGMA" command; see "Foreign keys" earlier in this manual.
682 statistics_info
684 $sth = $dbh->statistics_info(undef, $schema, $table,
685 $unique_only, $quick);
686 Returns information about a table and it's indexes, as specified in
688 "statistics_info" in DBI, but with some limitations :
689 · information in rows returned by the $sth is incomplete with respect
691 to the "statistics_info" in DBI specification. All requested fields
692 are present, but the content is "undef" for some of them.
693 The following nonempty fields are returned :
695 TABLE_SCHEM: The name of the schema (database) that the table is in.
697 The default schema is 'main', temporary tables are in 'temp' and other
698 databases will be in the name given when the database was attached.
699 TABLE_NAME: The name of the table
701 NON_UNIQUE: Contains 0 for unique indexes, 1 for non-unique indexes
703 INDEX_NAME: The name of the index
705 TYPE: SQLite uses 'btree' for all it's indexes
707 ORDINAL_POSITION: Column sequence number (starting with 1).
709 COLUMN_NAME: The name of the column
711 ping
713 my $bool = $dbh->ping;
714 returns true if the database file exists (or the database is in-
716 memory), and the database connection is active.
717
719 The following methods can be called via the func() method with a little
720 tweak, but the use of func() method is now discouraged by the DBI
721 author for various reasons (see DBI's document
722 <https://metacpan.org/pod/DBI::DBD#Using-install_method()-to-expose-driver-private-methods>
723 for details). So, if you're using DBI >= 1.608, use these "sqlite_"
724 methods. If you need to use an older DBI, you can call these like this:
725 $dbh->func( ..., "(method name without sqlite_ prefix)" );
727 Exception: "sqlite_trace" should always be called as is, even with
729 "func()" method (to avoid conflict with DBI's trace() method).
730 $dbh->func( ..., "sqlite_trace");
732 $dbh->sqlite_last_insert_rowid()
734 This method returns the last inserted rowid. If you specify an INTEGER
735 PRIMARY KEY as the first column in your table, that is the column that
736 is returned. Otherwise, it is the hidden ROWID column. See the SQLite
737 docs for details.
738 Generally you should not be using this method. Use the DBI
740 last_insert_id method instead. The usage of this is:
741 $h->last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ])
743 Running "$h->last_insert_id("","","","")" is the equivalent of running
745 "$dbh->sqlite_last_insert_rowid()" directly.
746 $dbh->sqlite_db_filename()
748 Retrieve the current (main) database filename. If the database is in-
749 memory or temporary, this returns an empty string, or "undef".
750 $dbh->sqlite_busy_timeout()
752 Retrieve the current busy timeout.
753 $dbh->sqlite_busy_timeout( $ms )
755 Set the current busy timeout. The timeout is in milliseconds.
756 $dbh->sqlite_create_function( $name, $argc, $code_ref, $flags )
758 This method will register a new function which will be usable in an SQL
759 query. The method's parameters are:
760 $name
762 The name of the function. This is the name of the function as it
763 will be used from SQL.
764 $argc
766 The number of arguments taken by the function. If this number is
767 -1, the function can take any number of arguments.
768 $code_ref
770 This should be a reference to the function's implementation.
771 $flags
773 You can optionally pass an extra flag bit to create_function, which
774 then would be ORed with SQLITE_UTF8 (default). As of 1.47_02
775 (SQLite 3.8.9), only meaning bit is SQLITE_DETERMINISTIC
776 (introduced at SQLite 3.8.3), which can make the function perform
777 better. See C API documentation at
778 <http://sqlite.org/c3ref/create_function.html> for details.
779 For example, here is how to define a now() function which returns the
781 current number of seconds since the epoch:
782 $dbh->sqlite_create_function( 'now', 0, sub { return time } );
784 After this, it could be used from SQL as:
786 INSERT INTO mytable ( now() );
788 The function should return a scalar value, and the value is treated as
790 a text (or a number if appropriate) by default. If you do need to
791 specify a type of the return value (like BLOB), you can return a
792 reference to an array that contains the value and the type, as of
793 1.65_01.
794 $dbh->sqlite_create_function( 'md5', 1, sub { return [md5($_[0]), SQL_BLOB] } );
796 REGEXP function
798 SQLite includes syntactic support for an infix operator 'REGEXP', but
800 without any implementation. The "DBD::SQLite" driver automatically
801 registers an implementation that performs standard perl regular
802 expression matching, using current locale. So for example you can
803 search for words starting with an 'A' with a query like
804 SELECT * from table WHERE column REGEXP '\bA\w+'
806 If you want case-insensitive searching, use perl regex flags, like this
808 :
809 SELECT * from table WHERE column REGEXP '(?i:\bA\w+)'
811 The default REGEXP implementation can be overridden through the
813 "create_function" API described above.
814 Note that regexp matching will not use SQLite indices, but will iterate
816 over all rows, so it could be quite costly in terms of performance.
817 $dbh->sqlite_create_collation( $name, $code_ref )
819 This method manually registers a new function which will be usable in
820 an SQL query as a COLLATE option for sorting. Such functions can also
821 be registered automatically on demand: see section "COLLATION
822 FUNCTIONS" below.
823 The method's parameters are:
825 $name
827 The name of the function exposed to SQL.
828 $code_ref
830 Reference to the function's implementation. The driver will check
831 that this is a proper sorting function.
832 $dbh->sqlite_collation_needed( $code_ref )
834 This method manually registers a callback function that will be invoked
835 whenever an undefined collation sequence is required from an SQL
836 statement. The callback is invoked as
837 $code_ref->($dbh, $collation_name)
839 and should register the desired collation using
841 "sqlite_create_collation".
842 An initial callback is already registered by "DBD::SQLite", so for most
844 common cases it will be simpler to just add your collation sequences in
845 the %DBD::SQLite::COLLATION hash (see section "COLLATION FUNCTIONS"
846 below).
847 $dbh->sqlite_create_aggregate( $name, $argc, $pkg, $flags )
849 This method will register a new aggregate function which can then be
850 used from SQL. The method's parameters are:
851 $name
853 The name of the aggregate function, this is the name under which
854 the function will be available from SQL.
855 $argc
857 This is an integer which tells the SQL parser how many arguments
858 the function takes. If that number is -1, the function can take any
859 number of arguments.
860 $pkg
862 This is the package which implements the aggregator interface.
863 $flags
865 You can optionally pass an extra flag bit to create_aggregate,
866 which then would be ORed with SQLITE_UTF8 (default). As of 1.47_02
867 (SQLite 3.8.9), only meaning bit is SQLITE_DETERMINISTIC
868 (introduced at SQLite 3.8.3), which can make the function perform
869 better. See C API documentation at
870 <http://sqlite.org/c3ref/create_function.html> for details.
871 The aggregator interface consists of defining three methods:
873 new()
875 This method will be called once to create an object which should be
876 used to aggregate the rows in a particular group. The step() and
877 finalize() methods will be called upon the reference return by the
878 method.
879 step(@_)
881 This method will be called once for each row in the aggregate.
882 finalize()
884 This method will be called once all rows in the aggregate were
885 processed and it should return the aggregate function's result.
886 When there is no rows in the aggregate, finalize() will be called
887 right after new().
888 Here is a simple aggregate function which returns the variance (example
890 adapted from pysqlite):
891 package variance;
893 sub new { bless [], shift; }
895 sub step {
897 my ( $self, $value ) = @_;
898 push @$self, $value;
900 }
901 sub finalize {
903 my $self = $_[0];
904 my $n = @$self;
906 # Variance is NULL unless there is more than one row
908 return undef unless $n || $n == 1;
909 my $mu = 0;
911 foreach my $v ( @$self ) {
912 $mu += $v;
913 }
914 $mu /= $n;
915 my $sigma = 0;
917 foreach my $v ( @$self ) {
918 $sigma += ($v - $mu)**2;
919 }
920 $sigma = $sigma / ($n - 1);
921 return $sigma;
923 }
924 $dbh->sqlite_create_aggregate( "variance", 1, 'variance' );
926 The aggregate function can then be used as:
928 SELECT group_name, variance(score)
930 FROM results
931 GROUP BY group_name;
932 For more examples, see the DBD::SQLite::Cookbook.
934 $dbh->sqlite_progress_handler( $n_opcodes, $code_ref )
936 This method registers a handler to be invoked periodically during long
937 running calls to SQLite.
938 An example use for this interface is to keep a GUI updated during a
940 large query. The parameters are:
941 $n_opcodes
943 The progress handler is invoked once for every $n_opcodes virtual
944 machine opcodes in SQLite.
945 $code_ref
947 Reference to the handler subroutine. If the progress handler
948 returns non-zero, the SQLite operation is interrupted. This feature
949 can be used to implement a "Cancel" button on a GUI dialog box.
950 Set this argument to "undef" if you want to unregister a previous
952 progress handler.
953 $dbh->sqlite_commit_hook( $code_ref )
955 This method registers a callback function to be invoked whenever a
956 transaction is committed. Any callback set by a previous call to
957 "sqlite_commit_hook" is overridden. A reference to the previous
958 callback (if any) is returned. Registering an "undef" disables the
959 callback.
960 When the commit hook callback returns zero, the commit operation is
962 allowed to continue normally. If the callback returns non-zero, then
963 the commit is converted into a rollback (in that case, any attempt to
964 explicitly call "$dbh->rollback()" afterwards would yield an error).
965 $dbh->sqlite_rollback_hook( $code_ref )
967 This method registers a callback function to be invoked whenever a
968 transaction is rolled back. Any callback set by a previous call to
969 "sqlite_rollback_hook" is overridden. A reference to the previous
970 callback (if any) is returned. Registering an "undef" disables the
971 callback.
972 $dbh->sqlite_update_hook( $code_ref )
974 This method registers a callback function to be invoked whenever a row
975 is updated, inserted or deleted. Any callback set by a previous call to
976 "sqlite_update_hook" is overridden. A reference to the previous
977 callback (if any) is returned. Registering an "undef" disables the
978 callback.
979 The callback will be called as
981 $code_ref->($action_code, $database, $table, $rowid)
983 where
985 $action_code
987 is an integer equal to either "DBD::SQLite::INSERT",
988 "DBD::SQLite::DELETE" or "DBD::SQLite::UPDATE" (see "Action
989 Codes");
990 $database
992 is the name of the database containing the affected row;
993 $table
995 is the name of the table containing the affected row;
996 $rowid
998 is the unique 64-bit signed integer key of the affected row within
999 that table.
1000 $dbh->sqlite_set_authorizer( $code_ref )
1002 This method registers an authorizer callback to be invoked whenever SQL
1003 statements are being compiled by the "prepare" in DBI method. The
1004 authorizer callback should return "DBD::SQLite::OK" to allow the
1005 action, "DBD::SQLite::IGNORE" to disallow the specific action but allow
1006 the SQL statement to continue to be compiled, or "DBD::SQLite::DENY" to
1007 cause the entire SQL statement to be rejected with an error. If the
1008 authorizer callback returns any other value, then "prepare" call that
1009 triggered the authorizer will fail with an error message.
1010 An authorizer is used when preparing SQL statements from an untrusted
1012 source, to ensure that the SQL statements do not try to access data
1013 they are not allowed to see, or that they do not try to execute
1014 malicious statements that damage the database. For example, an
1015 application may allow a user to enter arbitrary SQL queries for
1016 evaluation by a database. But the application does not want the user to
1017 be able to make arbitrary changes to the database. An authorizer could
1018 then be put in place while the user-entered SQL is being prepared that
1019 disallows everything except SELECT statements.
1020 The callback will be called as
1022 $code_ref->($action_code, $string1, $string2, $database, $trigger_or_view)
1024 where
1026 $action_code
1028 is an integer that specifies what action is being authorized (see
1029 "Action Codes").
1030 $string1, $string2
1032 are strings that depend on the action code (see "Action Codes").
1033 $database
1035 is the name of the database ("main", "temp", etc.) if applicable.
1036 $trigger_or_view
1038 is the name of the inner-most trigger or view that is responsible
1039 for the access attempt, or "undef" if this access attempt is
1040 directly from top-level SQL code.
1041 $dbh->sqlite_backup_from_file( $filename )
1043 This method accesses the SQLite Online Backup API, and will take a
1044 backup of the named database file, copying it to, and overwriting, your
1045 current database connection. This can be particularly handy if your
1046 current connection is to the special :memory: database, and you wish to
1047 populate it from an existing DB.
1048 $dbh->sqlite_backup_to_file( $filename )
1050 This method accesses the SQLite Online Backup API, and will take a
1051 backup of the currently connected database, and write it out to the
1052 named file.
1053 $dbh->sqlite_backup_from_dbh( $another_dbh )
1055 This method accesses the SQLite Online Backup API, and will take a
1056 backup of the database for the passed handle, copying it to, and
1057 overwriting, your current database connection. This can be particularly
1058 handy if your current connection is to the special :memory: database,
1059 and you wish to populate it from an existing DB. You can use this to
1060 backup from an in-memory database to another in-memory database.
1061 $dbh->sqlite_backup_to_dbh( $another_dbh )
1063 This method accesses the SQLite Online Backup API, and will take a
1064 backup of the currently connected database, and write it out to the
1065 passed database handle.
1066 $dbh->sqlite_enable_load_extension( $bool )
1068 Calling this method with a true value enables loading (external)
1069 SQLite3 extensions. After the call, you can load extensions like this:
1070 $dbh->sqlite_enable_load_extension(1);
1072 $sth = $dbh->prepare("select load_extension('libsqlitefunctions.so')")
1073 or die "Cannot prepare: " . $dbh->errstr();
1074 $dbh->sqlite_load_extension( $file, $proc )
1076 Loading an extension by a select statement (with the "load_extension"
1077 SQLite3 function like above) has some limitations. If you need to, say,
1078 create other functions from an extension, use this method. $file (a
1079 path to the extension) is mandatory, and $proc (an entry point name) is
1080 optional. You need to call "sqlite_enable_load_extension" before
1081 calling "sqlite_load_extension".
1082 $dbh->sqlite_trace( $code_ref )
1084 This method registers a trace callback to be invoked whenever SQL
1085 statements are being run.
1086 The callback will be called as
1088 $code_ref->($statement)
1090 where
1092 $statement
1094 is a UTF-8 rendering of the SQL statement text as the statement
1095 first begins executing.
1096 Additional callbacks might occur as each triggered subprogram is
1098 entered. The callbacks for triggers contain a UTF-8 SQL comment that
1099 identifies the trigger.
1100 See also "TRACING" in DBI for better tracing options.
1102 $dbh->sqlite_profile( $code_ref )
1104 This method registers a profile callback to be invoked whenever a SQL
1105 statement finishes.
1106 The callback will be called as
1108 $code_ref->($statement, $elapsed_time)
1110 where
1112 $statement
1114 is the original statement text (without bind parameters).
1115 $elapsed_time
1117 is an estimate of wall-clock time of how long that statement took
1118 to run (in milliseconds).
1119 This method is considered experimental and is subject to change in
1121 future versions of SQLite.
1122 See also DBI::Profile for better profiling options.
1124 $dbh->sqlite_table_column_metadata( $dbname, $tablename, $columnname )
1126 is for internal use only.
1127 $dbh->sqlite_db_status()
1129 Returns a hash reference that holds a set of status information of
1130 database connection such as cache usage. See
1131 <https://www.sqlite.org/c3ref/c_dbstatus_options.html> for details. You
1132 may also pass 0 as an argument to reset the status.
1133 $sth->sqlite_st_status()
1135 Returns a hash reference that holds a set of status information of
1136 SQLite statement handle such as full table scan count. See
1137 <https://www.sqlite.org/c3ref/c_stmtstatus_counter.html> for details.
1138 Statement status only holds the current value.
1139 my $status = $sth->sqlite_st_status();
1141 my $cur = $status->{fullscan_step};
1142 You may also pass 0 as an argument to reset the status.
1144 $dbh->sqlite_db_config( $id, $new_integer_value )
1146 You can change how the connected database should behave like this:
1147 use DBD::SQLite::Constants qw/:database_connection_configuration_options/;
1149 my $dbh = DBI->connect('dbi:SQLite::memory:');
1151 # This disables language features that allow ordinary SQL
1153 # to deliberately corrupt the database file
1154 $dbh->sqlite_db_config( SQLITE_DBCONFIG_DEFENSIVE, 1 );
1155 # This disables two-arg version of fts3_tokenizer.
1157 $dbh->sqlite_db_config( SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, 0 );
1158 "sqlite_db_config" returns the new value after the call. If you just
1160 want to know the current value without changing anything, pass a
1161 negative integer value.
1162 my $current_value = $dbh->sqlite_db_config( SQLITE_DBCONFIG_DEFENSIVE, -1 );
1164 As of this writing, "sqlite_db_config" only supports options that set
1166 an integer value. "SQLITE_DBCONFIG_LOOKASIDE" and
1167 "SQLITE_DBCONFIG_MAINDBNAME" are not supported. See also
1168 "https://www.sqlite.org/capi3ref.html#sqlite3_db_config" for details.
1169 $dbh->sqlite_create_module()
1171 Registers a name for a virtual table module. Module names must be
1172 registered before creating a new virtual table using the module and
1173 before using a preexisting virtual table for the module. Virtual
1174 tables are explained in DBD::SQLite::VirtualTable.
1175 $dbh->sqlite_limit( $category_id, $new_value )
1177 Sets a new run-time limit for the category, and returns the current
1178 limit. If the new value is a negative number (or omitted), the limit
1179 is unchanged and just returns the current limit. Category ids
1180 (SQLITE_LIMIT_LENGTH, SQLITE_LIMIT_VARIABLE_NUMBER, etc) can be
1181 imported from DBD::SQLite::Constants.
1182 $dbh->sqlite_get_autocommit()
1184 Returns true if the internal SQLite connection is in an autocommit
1185 mode. This does not always return the same value as
1186 "$dbh->{AutoCommit}". This returns false if you explicitly issue a
1187 "<BEGIN"> statement.
1188
1190 DBD::SQLite::compile_options()
1191 Returns an array of compile options (available since SQLite 3.6.23,
1192 bundled in DBD::SQLite 1.30_01), or an empty array if the bundled
1193 library is old or compiled with SQLITE_OMIT_COMPILEOPTION_DIAGS.
1194 DBD::SQLite::sqlite_status()
1196 Returns a hash reference that holds a set of status information of
1197 SQLite runtime such as memory usage or page cache usage (see
1198 <https://www.sqlite.org/c3ref/c_status_malloc_count.html> for details).
1199 Each of the entry contains the current value and the highwater value.
1200 my $status = DBD::SQLite::sqlite_status();
1202 my $cur = $status->{memory_used}{current};
1203 my $high = $status->{memory_used}{highwater};
1204 You may also pass 0 as an argument to reset the status.
1206 DBD::SQLite::strlike($pattern, $string, $escape_char),
1208 DBD::SQLite::strglob($pattern, $string)
1209 As of 1.49_05 (SQLite 3.10.0), you can use these two functions to see
1210 if a string matches a pattern. These may be useful when you create a
1211 virtual table or a custom function. See
1212 <http://sqlite.org/c3ref/strlike.html> and
1213 <http://sqlite.org/c3ref/strglob.html> for details.
1214
1216 A subset of SQLite C constants are made available to Perl, because they
1217 may be needed when writing hooks or authorizer callbacks. For accessing
1218 such constants, the "DBD::SQLite" module must be explicitly "use"d at
1219 compile time. For example, an authorizer that forbids any DELETE
1220 operation would be written as follows :
1221 use DBD::SQLite;
1223 $dbh->sqlite_set_authorizer(sub {
1224 my $action_code = shift;
1225 return $action_code == DBD::SQLite::DELETE ? DBD::SQLite::DENY
1226 : DBD::SQLite::OK;
1227 });
1228 The list of constants implemented in "DBD::SQLite" is given below; more
1230 information can be found ad at
1231 <https://www.sqlite.org/c3ref/constlist.html>.
1232 Authorizer Return Codes
1234 OK
1235 DENY
1236 IGNORE
1237 Action Codes
1239 The "set_authorizer" method registers a callback function that is
1240 invoked to authorize certain SQL statement actions. The first parameter
1241 to the callback is an integer code that specifies what action is being
1242 authorized. The second and third parameters to the callback are
1243 strings, the meaning of which varies according to the action code.
1244 Below is the list of action codes, together with their associated
1245 strings.
1246 # constant string1 string2
1248 # ======== ======= =======
1249 CREATE_INDEX Index Name Table Name
1250 CREATE_TABLE Table Name undef
1251 CREATE_TEMP_INDEX Index Name Table Name
1252 CREATE_TEMP_TABLE Table Name undef
1253 CREATE_TEMP_TRIGGER Trigger Name Table Name
1254 CREATE_TEMP_VIEW View Name undef
1255 CREATE_TRIGGER Trigger Name Table Name
1256 CREATE_VIEW View Name undef
1257 DELETE Table Name undef
1258 DROP_INDEX Index Name Table Name
1259 DROP_TABLE Table Name undef
1260 DROP_TEMP_INDEX Index Name Table Name
1261 DROP_TEMP_TABLE Table Name undef
1262 DROP_TEMP_TRIGGER Trigger Name Table Name
1263 DROP_TEMP_VIEW View Name undef
1264 DROP_TRIGGER Trigger Name Table Name
1265 DROP_VIEW View Name undef
1266 INSERT Table Name undef
1267 PRAGMA Pragma Name 1st arg or undef
1268 READ Table Name Column Name
1269 SELECT undef undef
1270 TRANSACTION Operation undef
1271 UPDATE Table Name Column Name
1272 ATTACH Filename undef
1273 DETACH Database Name undef
1274 ALTER_TABLE Database Name Table Name
1275 REINDEX Index Name undef
1276 ANALYZE Table Name undef
1277 CREATE_VTABLE Table Name Module Name
1278 DROP_VTABLE Table Name Module Name
1279 FUNCTION undef Function Name
1280 SAVEPOINT Operation Savepoint Name
1281
1283 Definition
1284 SQLite v3 provides the ability for users to supply arbitrary comparison
1285 functions, known as user-defined "collation sequences" or "collating
1286 functions", to be used for comparing two text values.
1287 <https://www.sqlite.org/datatype3.html#collation> explains how
1288 collations are used in various SQL expressions.
1289 Builtin collation sequences
1291 The following collation sequences are builtin within SQLite :
1292 BINARY
1294 Compares string data using memcmp(), regardless of text encoding.
1295 NOCASE
1297 The same as binary, except the 26 upper case characters of ASCII
1298 are folded to their lower case equivalents before the comparison is
1299 performed. Note that only ASCII characters are case folded. SQLite
1300 does not attempt to do full UTF case folding due to the size of the
1301 tables required.
1302 RTRIM
1304 The same as binary, except that trailing space characters are
1305 ignored.
1306 In addition, "DBD::SQLite" automatically installs the following
1308 collation sequences :
1309 perl
1311 corresponds to the Perl "cmp" operator
1312 perllocale
1314 Perl "cmp" operator, in a context where "use locale" is activated.
1315 Usage
1317 You can write for example
1318 CREATE TABLE foo(
1320 txt1 COLLATE perl,
1321 txt2 COLLATE perllocale,
1322 txt3 COLLATE nocase
1323 )
1324 or
1326 SELECT * FROM foo ORDER BY name COLLATE perllocale
1328 Unicode handling
1330 If the attribute "$dbh->{sqlite_unicode}" is set, strings coming from
1331 the database and passed to the collation function will be properly
1332 tagged with the utf8 flag; but this only works if the "sqlite_unicode"
1333 attribute is set before the first call to a perl collation sequence .
1334 The recommended way to activate unicode is to set the parameter at
1335 connection time :
1336 my $dbh = DBI->connect(
1338 "dbi:SQLite:dbname=foo", "", "",
1339 {
1340 RaiseError => 1,
1341 sqlite_unicode => 1,
1342 }
1343 );
1344 Adding user-defined collations
1346 The native SQLite API for adding user-defined collations is exposed
1347 through methods "sqlite_create_collation" and
1348 "sqlite_collation_needed".
1349 To avoid calling these functions every time a $dbh handle is created,
1351 "DBD::SQLite" offers a simpler interface through the
1352 %DBD::SQLite::COLLATION hash : just insert your own collation functions
1353 in that hash, and whenever an unknown collation name is encountered in
1354 SQL, the appropriate collation function will be loaded on demand from
1355 the hash. For example, here is a way to sort text values regardless of
1356 their accented characters :
1357 use DBD::SQLite;
1359 $DBD::SQLite::COLLATION{no_accents} = sub {
1360 my ( $a, $b ) = map lc, @_;
1361 tr[����������������������������]
1362 [aaaaaacdeeeeiiiinoooooouuuuy] for $a, $b;
1363 $a cmp $b;
1364 };
1365 my $dbh = DBI->connect("dbi:SQLite:dbname=dbfile");
1366 my $sql = "SELECT ... FROM ... ORDER BY ... COLLATE no_accents");
1367 my $rows = $dbh->selectall_arrayref($sql);
1368 The builtin "perl" or "perllocale" collations are predefined in that
1370 same hash.
1371 The COLLATION hash is a global registry within the current process;
1373 hence there is a risk of undesired side-effects. Therefore, to prevent
1374 action at distance, the hash is implemented as a "write-only" hash,
1375 that will happily accept new entries, but will raise an exception if
1376 any attempt is made to override or delete a existing entry (including
1377 the builtin "perl" and "perllocale").
1378 If you really, really need to change or delete an entry, you can always
1380 grab the tied object underneath %DBD::SQLite::COLLATION --- but don't
1381 do that unless you really know what you are doing. Also observe that
1382 changes in the global hash will not modify existing collations in
1383 existing database handles: it will only affect new requests for
1384 collations. In other words, if you want to change the behaviour of a
1385 collation within an existing $dbh, you need to call the
1386 "create_collation" method directly.
1387
1389 SQLite is bundled with an extension module for full-text indexing.
1390 Tables with this feature enabled can be efficiently queried to find
1391 rows that contain one or more instances of some specified words, in any
1392 column, even if the table contains many large documents.
1393 Explanations for using this feature are provided in a separate
1395 document: see DBD::SQLite::Fulltext_search.
1396
1398 The RTREE extension module within SQLite adds support for creating a
1399 R-Tree, a special index for range and multidimensional queries. This
1400 allows users to create tables that can be loaded with (as an example)
1401 geospatial data such as latitude/longitude coordinates for buildings
1402 within a city :
1403 CREATE VIRTUAL TABLE city_buildings USING rtree(
1405 id, -- Integer primary key
1406 minLong, maxLong, -- Minimum and maximum longitude
1407 minLat, maxLat -- Minimum and maximum latitude
1408 );
1409 then query which buildings overlap or are contained within a specified
1411 region:
1412 # IDs that are contained within query coordinates
1414 my $contained_sql = <<"";
1415 SELECT id FROM city_buildings
1416 WHERE minLong >= ? AND maxLong <= ?
1417 AND minLat >= ? AND maxLat <= ?
1418 # ... and those that overlap query coordinates
1420 my $overlap_sql = <<"";
1421 SELECT id FROM city_buildings
1422 WHERE maxLong >= ? AND minLong <= ?
1423 AND maxLat >= ? AND minLat <= ?
1424 my $contained = $dbh->selectcol_arrayref($contained_sql,undef,
1426 $minLong, $maxLong, $minLat, $maxLat);
1427 my $overlapping = $dbh->selectcol_arrayref($overlap_sql,undef,
1429 $minLong, $maxLong, $minLat, $maxLat);
1430 For more detail, please see the SQLite R-Tree page
1432 (<https://www.sqlite.org/rtree.html>). Note that custom R-Tree queries
1433 using callbacks, as mentioned in the prior link, have not been
1434 implemented yet.
1435
1437 SQLite has a concept of "virtual tables" which look like regular tables
1438 but are implemented internally through specific functions. The
1439 fulltext or R* tree features described in the previous chapters are
1440 examples of such virtual tables, implemented in C code.
1441 "DBD::SQLite" also supports virtual tables implemented in Perl code:
1443 see DBD::SQLite::VirtualTable for using or implementing such virtual
1444 tables. These can have many interesting uses for joining regular DBMS
1445 data with some other kind of data within your Perl programs. Bundled
1446 with the present distribution are :
1447 · DBD::SQLite::VirtualTable::FileContent : implements a virtual
1449 column that exposes file contents. This is especially useful in
1450 conjunction with a fulltext index; see
1451 DBD::SQLite::Fulltext_search.
1452 · DBD::SQLite::VirtualTable::PerlData : binds to a Perl array within
1454 the Perl program. This can be used for simple import/export
1455 operations, for debugging purposes, for joining data from different
1456 sources, etc.
1457 Other Perl virtual tables may also be published separately on CPAN.
1459
1461 The following items remain to be done.
1462 Leak Detection
1464 Implement one or more leak detection tests that only run during
1465 AUTOMATED_TESTING and RELEASE_TESTING and validate that none of the C
1466 code we work with leaks.
1467 Stream API for Blobs
1469 Reading/writing into blobs using "sqlite2_blob_open" /
1470 "sqlite2_blob_close".
1471 Support for custom callbacks for R-Tree queries
1473 Custom queries of a R-Tree index using a callback are possible with the
1474 SQLite C API (<https://www.sqlite.org/rtree.html>), so one could
1475 potentially use a callback that narrowed the result set down based on a
1476 specific need, such as querying for overlapping circles.
1477
1479 Bugs should be reported to GitHub issues:
1480 <https://github.com/DBD-SQLite/DBD-SQLite/issues>
1482 or via RT if you prefer:
1484 <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
1486 Note that bugs of bundled SQLite library (i.e. bugs in "sqlite3.[ch]")
1488 should be reported to the SQLite developers at sqlite.org via their bug
1489 tracker or via their mailing list.
1490 The master repository is on GitHub:
1492 <https://github.com/DBD-SQLite/DBD-SQLite>.
1494 We also have a mailing list:
1496 <http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite>
1498
1500 Matt Sergeant <matt@sergeant.org>
1501 Francis J. Lacoste <flacoste@logreport.org>
1503 Wolfgang Sourdeau <wolfgang@logreport.org>
1505 Adam Kennedy <adamk@cpan.org>
1507 Max Maischein <corion@cpan.org>
1509 Laurent Dami <dami@cpan.org>
1511 Kenichi Ishigaki <ishigaki@cpan.org>
1513
1515 The bundled SQLite code in this distribution is Public Domain.
1516 DBD::SQLite is copyright 2002 - 2007 Matt Sergeant.
1518 Some parts copyright 2008 Francis J. Lacoste.
1520 Some parts copyright 2008 Wolfgang Sourdeau.
1522 Some parts copyright 2008 - 2013 Adam Kennedy.
1524 Some parts copyright 2009 - 2013 Kenichi Ishigaki.
1526 Some parts derived from DBD::SQLite::Amalgamation copyright 2008 Audrey
1528 Tang.
1529 This program is free software; you can redistribute it and/or modify it
1531 under the same terms as Perl itself.
1532 The full text of the license can be found in the LICENSE file included
1534 with this module.
1535perl v5.32.0 2020-08-31 DBD::SQLite(3)