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.29.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_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 REGEXP function
790 SQLite includes syntactic support for an infix operator 'REGEXP', but
792 without any implementation. The "DBD::SQLite" driver automatically
793 registers an implementation that performs standard perl regular
794 expression matching, using current locale. So for example you can
795 search for words starting with an 'A' with a query like
796 SELECT * from table WHERE column REGEXP '\bA\w+'
798 If you want case-insensitive searching, use perl regex flags, like this
800 :
801 SELECT * from table WHERE column REGEXP '(?i:\bA\w+)'
803 The default REGEXP implementation can be overridden through the
805 "create_function" API described above.
806 Note that regexp matching will not use SQLite indices, but will iterate
808 over all rows, so it could be quite costly in terms of performance.
809 $dbh->sqlite_create_collation( $name, $code_ref )
811 This method manually registers a new function which will be usable in
812 an SQL query as a COLLATE option for sorting. Such functions can also
813 be registered automatically on demand: see section "COLLATION
814 FUNCTIONS" below.
815 The method's parameters are:
817 $name
819 The name of the function exposed to SQL.
820 $code_ref
822 Reference to the function's implementation. The driver will check
823 that this is a proper sorting function.
824 $dbh->sqlite_collation_needed( $code_ref )
826 This method manually registers a callback function that will be invoked
827 whenever an undefined collation sequence is required from an SQL
828 statement. The callback is invoked as
829 $code_ref->($dbh, $collation_name)
831 and should register the desired collation using
833 "sqlite_create_collation".
834 An initial callback is already registered by "DBD::SQLite", so for most
836 common cases it will be simpler to just add your collation sequences in
837 the %DBD::SQLite::COLLATION hash (see section "COLLATION FUNCTIONS"
838 below).
839 $dbh->sqlite_create_aggregate( $name, $argc, $pkg, $flags )
841 This method will register a new aggregate function which can then be
842 used from SQL. The method's parameters are:
843 $name
845 The name of the aggregate function, this is the name under which
846 the function will be available from SQL.
847 $argc
849 This is an integer which tells the SQL parser how many arguments
850 the function takes. If that number is -1, the function can take any
851 number of arguments.
852 $pkg
854 This is the package which implements the aggregator interface.
855 $flags
857 You can optionally pass an extra flag bit to create_aggregate,
858 which then would be ORed with SQLITE_UTF8 (default). As of 1.47_02
859 (SQLite 3.8.9), only meaning bit is SQLITE_DETERMINISTIC
860 (introduced at SQLite 3.8.3), which can make the function perform
861 better. See C API documentation at
862 <http://sqlite.org/c3ref/create_function.html> for details.
863 The aggregator interface consists of defining three methods:
865 new()
867 This method will be called once to create an object which should be
868 used to aggregate the rows in a particular group. The step() and
869 finalize() methods will be called upon the reference return by the
870 method.
871 step(@_)
873 This method will be called once for each row in the aggregate.
874 finalize()
876 This method will be called once all rows in the aggregate were
877 processed and it should return the aggregate function's result.
878 When there is no rows in the aggregate, finalize() will be called
879 right after new().
880 Here is a simple aggregate function which returns the variance (example
882 adapted from pysqlite):
883 package variance;
885 sub new { bless [], shift; }
887 sub step {
889 my ( $self, $value ) = @_;
890 push @$self, $value;
892 }
893 sub finalize {
895 my $self = $_[0];
896 my $n = @$self;
898 # Variance is NULL unless there is more than one row
900 return undef unless $n || $n == 1;
901 my $mu = 0;
903 foreach my $v ( @$self ) {
904 $mu += $v;
905 }
906 $mu /= $n;
907 my $sigma = 0;
909 foreach my $v ( @$self ) {
910 $sigma += ($v - $mu)**2;
911 }
912 $sigma = $sigma / ($n - 1);
913 return $sigma;
915 }
916 $dbh->sqlite_create_aggregate( "variance", 1, 'variance' );
918 The aggregate function can then be used as:
920 SELECT group_name, variance(score)
922 FROM results
923 GROUP BY group_name;
924 For more examples, see the DBD::SQLite::Cookbook.
926 $dbh->sqlite_progress_handler( $n_opcodes, $code_ref )
928 This method registers a handler to be invoked periodically during long
929 running calls to SQLite.
930 An example use for this interface is to keep a GUI updated during a
932 large query. The parameters are:
933 $n_opcodes
935 The progress handler is invoked once for every $n_opcodes virtual
936 machine opcodes in SQLite.
937 $code_ref
939 Reference to the handler subroutine. If the progress handler
940 returns non-zero, the SQLite operation is interrupted. This feature
941 can be used to implement a "Cancel" button on a GUI dialog box.
942 Set this argument to "undef" if you want to unregister a previous
944 progress handler.
945 $dbh->sqlite_commit_hook( $code_ref )
947 This method registers a callback function to be invoked whenever a
948 transaction is committed. Any callback set by a previous call to
949 "sqlite_commit_hook" is overridden. A reference to the previous
950 callback (if any) is returned. Registering an "undef" disables the
951 callback.
952 When the commit hook callback returns zero, the commit operation is
954 allowed to continue normally. If the callback returns non-zero, then
955 the commit is converted into a rollback (in that case, any attempt to
956 explicitly call "$dbh->rollback()" afterwards would yield an error).
957 $dbh->sqlite_rollback_hook( $code_ref )
959 This method registers a callback function to be invoked whenever a
960 transaction is rolled back. Any callback set by a previous call to
961 "sqlite_rollback_hook" is overridden. A reference to the previous
962 callback (if any) is returned. Registering an "undef" disables the
963 callback.
964 $dbh->sqlite_update_hook( $code_ref )
966 This method registers a callback function to be invoked whenever a row
967 is updated, inserted or deleted. Any callback set by a previous call to
968 "sqlite_update_hook" is overridden. A reference to the previous
969 callback (if any) is returned. Registering an "undef" disables the
970 callback.
971 The callback will be called as
973 $code_ref->($action_code, $database, $table, $rowid)
975 where
977 $action_code
979 is an integer equal to either "DBD::SQLite::INSERT",
980 "DBD::SQLite::DELETE" or "DBD::SQLite::UPDATE" (see "Action
981 Codes");
982 $database
984 is the name of the database containing the affected row;
985 $table
987 is the name of the table containing the affected row;
988 $rowid
990 is the unique 64-bit signed integer key of the affected row within
991 that table.
992 $dbh->sqlite_set_authorizer( $code_ref )
994 This method registers an authorizer callback to be invoked whenever SQL
995 statements are being compiled by the "prepare" in DBI method. The
996 authorizer callback should return "DBD::SQLite::OK" to allow the
997 action, "DBD::SQLite::IGNORE" to disallow the specific action but allow
998 the SQL statement to continue to be compiled, or "DBD::SQLite::DENY" to
999 cause the entire SQL statement to be rejected with an error. If the
1000 authorizer callback returns any other value, then "prepare" call that
1001 triggered the authorizer will fail with an error message.
1002 An authorizer is used when preparing SQL statements from an untrusted
1004 source, to ensure that the SQL statements do not try to access data
1005 they are not allowed to see, or that they do not try to execute
1006 malicious statements that damage the database. For example, an
1007 application may allow a user to enter arbitrary SQL queries for
1008 evaluation by a database. But the application does not want the user to
1009 be able to make arbitrary changes to the database. An authorizer could
1010 then be put in place while the user-entered SQL is being prepared that
1011 disallows everything except SELECT statements.
1012 The callback will be called as
1014 $code_ref->($action_code, $string1, $string2, $database, $trigger_or_view)
1016 where
1018 $action_code
1020 is an integer that specifies what action is being authorized (see
1021 "Action Codes").
1022 $string1, $string2
1024 are strings that depend on the action code (see "Action Codes").
1025 $database
1027 is the name of the database ("main", "temp", etc.) if applicable.
1028 $trigger_or_view
1030 is the name of the inner-most trigger or view that is responsible
1031 for the access attempt, or "undef" if this access attempt is
1032 directly from top-level SQL code.
1033 $dbh->sqlite_backup_from_file( $filename )
1035 This method accesses the SQLite Online Backup API, and will take a
1036 backup of the named database file, copying it to, and overwriting, your
1037 current database connection. This can be particularly handy if your
1038 current connection is to the special :memory: database, and you wish to
1039 populate it from an existing DB.
1040 $dbh->sqlite_backup_to_file( $filename )
1042 This method accesses the SQLite Online Backup API, and will take a
1043 backup of the currently connected database, and write it out to the
1044 named file.
1045 $dbh->sqlite_backup_from_dbh( $another_dbh )
1047 This method accesses the SQLite Online Backup API, and will take a
1048 backup of the database for the passed handle, copying it to, and
1049 overwriting, your current database connection. This can be particularly
1050 handy if your current connection is to the special :memory: database,
1051 and you wish to populate it from an existing DB. You can use this to
1052 backup from an in-memory database to another in-memory database.
1053 $dbh->sqlite_backup_to_dbh( $another_dbh )
1055 This method accesses the SQLite Online Backup API, and will take a
1056 backup of the currently connected database, and write it out to the
1057 passed database handle.
1058 $dbh->sqlite_enable_load_extension( $bool )
1060 Calling this method with a true value enables loading (external)
1061 SQLite3 extensions. After the call, you can load extensions like this:
1062 $dbh->sqlite_enable_load_extension(1);
1064 $sth = $dbh->prepare("select load_extension('libsqlitefunctions.so')")
1065 or die "Cannot prepare: " . $dbh->errstr();
1066 $dbh->sqlite_load_extension( $file, $proc )
1068 Loading an extension by a select statement (with the "load_extension"
1069 SQLite3 function like above) has some limitations. If you need to, say,
1070 create other functions from an extension, use this method. $file (a
1071 path to the extension) is mandatory, and $proc (an entry point name) is
1072 optional. You need to call "sqlite_enable_load_extension" before
1073 calling "sqlite_load_extension".
1074 $dbh->sqlite_trace( $code_ref )
1076 This method registers a trace callback to be invoked whenever SQL
1077 statements are being run.
1078 The callback will be called as
1080 $code_ref->($statement)
1082 where
1084 $statement
1086 is a UTF-8 rendering of the SQL statement text as the statement
1087 first begins executing.
1088 Additional callbacks might occur as each triggered subprogram is
1090 entered. The callbacks for triggers contain a UTF-8 SQL comment that
1091 identifies the trigger.
1092 See also "TRACING" in DBI for better tracing options.
1094 $dbh->sqlite_profile( $code_ref )
1096 This method registers a profile callback to be invoked whenever a SQL
1097 statement finishes.
1098 The callback will be called as
1100 $code_ref->($statement, $elapsed_time)
1102 where
1104 $statement
1106 is the original statement text (without bind parameters).
1107 $elapsed_time
1109 is an estimate of wall-clock time of how long that statement took
1110 to run (in milliseconds).
1111 This method is considered experimental and is subject to change in
1113 future versions of SQLite.
1114 See also DBI::Profile for better profiling options.
1116 $dbh->sqlite_table_column_metadata( $dbname, $tablename, $columnname )
1118 is for internal use only.
1119 $dbh->sqlite_db_status()
1121 Returns a hash reference that holds a set of status information of
1122 database connection such as cache usage. See
1123 <https://www.sqlite.org/c3ref/c_dbstatus_options.html> for details. You
1124 may also pass 0 as an argument to reset the status.
1125 $sth->sqlite_st_status()
1127 Returns a hash reference that holds a set of status information of
1128 SQLite statement handle such as full table scan count. See
1129 <https://www.sqlite.org/c3ref/c_stmtstatus_counter.html> for details.
1130 Statement status only holds the current value.
1131 my $status = $sth->sqlite_st_status();
1133 my $cur = $status->{fullscan_step};
1134 You may also pass 0 as an argument to reset the status.
1136 $dbh->sqlite_db_config( $id, $new_integer_value )
1138 You can change how the connected database should behave like this:
1139 use DBD::SQLite::Constants qw/:database_connection_configuration_options/;
1141 my $dbh = DBI->connect('dbi:SQLite::memory:');
1143 # This disables language features that allow ordinary SQL
1145 # to deliberately corrupt the database file
1146 $dbh->sqlite_db_config( SQLITE_DBCONFIG_DEFENSIVE, 1 );
1147 # This disables two-arg version of fts3_tokenizer.
1149 $dbh->sqlite_db_config( SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, 0 );
1150 "sqlite_db_config" returns the new value after the call. If you just
1152 want to know the current value without changing anything, pass a
1153 negative integer value.
1154 my $current_value = $dbh->sqlite_db_config( SQLITE_DBCONFIG_DEFENSIVE, -1 );
1156 As of this writing, "sqlite_db_config" only supports options that set
1158 an integer value. "SQLITE_DBCONFIG_LOOKASIDE" and
1159 "SQLITE_DBCONFIG_MAINDBNAME" are not supported. See also
1160 "https://www.sqlite.org/capi3ref.html#sqlite3_db_config" for details.
1161 $dbh->sqlite_create_module()
1163 Registers a name for a virtual table module. Module names must be
1164 registered before creating a new virtual table using the module and
1165 before using a preexisting virtual table for the module. Virtual
1166 tables are explained in DBD::SQLite::VirtualTable.
1167 $dbh->sqlite_limit( $category_id, $new_value )
1169 Sets a new run-time limit for the category, and returns the current
1170 limit. If the new value is a negative number (or omitted), the limit
1171 is unchanged and just returns the current limit. Category ids
1172 (SQLITE_LIMIT_LENGTH, SQLITE_LIMIT_VARIABLE_NUMBER, etc) can be
1173 imported from DBD::SQLite::Constants.
1174 $dbh->sqlite_get_autocommit()
1176 Returns true if the internal SQLite connection is in an autocommit
1177 mode. This does not always return the same value as
1178 "$dbh->{AutoCommit}". This returns false if you explicitly issue a
1179 "<BEGIN"> statement.
1180
1182 DBD::SQLite::compile_options()
1183 Returns an array of compile options (available since SQLite 3.6.23,
1184 bundled in DBD::SQLite 1.30_01), or an empty array if the bundled
1185 library is old or compiled with SQLITE_OMIT_COMPILEOPTION_DIAGS.
1186 DBD::SQLite::sqlite_status()
1188 Returns a hash reference that holds a set of status information of
1189 SQLite runtime such as memory usage or page cache usage (see
1190 <https://www.sqlite.org/c3ref/c_status_malloc_count.html> for details).
1191 Each of the entry contains the current value and the highwater value.
1192 my $status = DBD::SQLite::sqlite_status();
1194 my $cur = $status->{memory_used}{current};
1195 my $high = $status->{memory_used}{highwater};
1196 You may also pass 0 as an argument to reset the status.
1198 DBD::SQLite::strlike($pattern, $string, $escape_char),
1200 DBD::SQLite::strglob($pattern, $string)
1201 As of 1.49_05 (SQLite 3.10.0), you can use these two functions to see
1202 if a string matches a pattern. These may be useful when you create a
1203 virtual table or a custom function. See
1204 <http://sqlite.org/c3ref/strlike.html> and
1205 <http://sqlite.org/c3ref/strglob.html> for details.
1206
1208 A subset of SQLite C constants are made available to Perl, because they
1209 may be needed when writing hooks or authorizer callbacks. For accessing
1210 such constants, the "DBD::SQLite" module must be explicitly "use"d at
1211 compile time. For example, an authorizer that forbids any DELETE
1212 operation would be written as follows :
1213 use DBD::SQLite;
1215 $dbh->sqlite_set_authorizer(sub {
1216 my $action_code = shift;
1217 return $action_code == DBD::SQLite::DELETE ? DBD::SQLite::DENY
1218 : DBD::SQLite::OK;
1219 });
1220 The list of constants implemented in "DBD::SQLite" is given below; more
1222 information can be found ad at
1223 <https://www.sqlite.org/c3ref/constlist.html>.
1224 Authorizer Return Codes
1226 OK
1227 DENY
1228 IGNORE
1229 Action Codes
1231 The "set_authorizer" method registers a callback function that is
1232 invoked to authorize certain SQL statement actions. The first parameter
1233 to the callback is an integer code that specifies what action is being
1234 authorized. The second and third parameters to the callback are
1235 strings, the meaning of which varies according to the action code.
1236 Below is the list of action codes, together with their associated
1237 strings.
1238 # constant string1 string2
1240 # ======== ======= =======
1241 CREATE_INDEX Index Name Table Name
1242 CREATE_TABLE Table Name undef
1243 CREATE_TEMP_INDEX Index Name Table Name
1244 CREATE_TEMP_TABLE Table Name undef
1245 CREATE_TEMP_TRIGGER Trigger Name Table Name
1246 CREATE_TEMP_VIEW View Name undef
1247 CREATE_TRIGGER Trigger Name Table Name
1248 CREATE_VIEW View Name undef
1249 DELETE Table Name undef
1250 DROP_INDEX Index Name Table Name
1251 DROP_TABLE Table Name undef
1252 DROP_TEMP_INDEX Index Name Table Name
1253 DROP_TEMP_TABLE Table Name undef
1254 DROP_TEMP_TRIGGER Trigger Name Table Name
1255 DROP_TEMP_VIEW View Name undef
1256 DROP_TRIGGER Trigger Name Table Name
1257 DROP_VIEW View Name undef
1258 INSERT Table Name undef
1259 PRAGMA Pragma Name 1st arg or undef
1260 READ Table Name Column Name
1261 SELECT undef undef
1262 TRANSACTION Operation undef
1263 UPDATE Table Name Column Name
1264 ATTACH Filename undef
1265 DETACH Database Name undef
1266 ALTER_TABLE Database Name Table Name
1267 REINDEX Index Name undef
1268 ANALYZE Table Name undef
1269 CREATE_VTABLE Table Name Module Name
1270 DROP_VTABLE Table Name Module Name
1271 FUNCTION undef Function Name
1272 SAVEPOINT Operation Savepoint Name
1273
1275 Definition
1276 SQLite v3 provides the ability for users to supply arbitrary comparison
1277 functions, known as user-defined "collation sequences" or "collating
1278 functions", to be used for comparing two text values.
1279 <https://www.sqlite.org/datatype3.html#collation> explains how
1280 collations are used in various SQL expressions.
1281 Builtin collation sequences
1283 The following collation sequences are builtin within SQLite :
1284 BINARY
1286 Compares string data using memcmp(), regardless of text encoding.
1287 NOCASE
1289 The same as binary, except the 26 upper case characters of ASCII
1290 are folded to their lower case equivalents before the comparison is
1291 performed. Note that only ASCII characters are case folded. SQLite
1292 does not attempt to do full UTF case folding due to the size of the
1293 tables required.
1294 RTRIM
1296 The same as binary, except that trailing space characters are
1297 ignored.
1298 In addition, "DBD::SQLite" automatically installs the following
1300 collation sequences :
1301 perl
1303 corresponds to the Perl "cmp" operator
1304 perllocale
1306 Perl "cmp" operator, in a context where "use locale" is activated.
1307 Usage
1309 You can write for example
1310 CREATE TABLE foo(
1312 txt1 COLLATE perl,
1313 txt2 COLLATE perllocale,
1314 txt3 COLLATE nocase
1315 )
1316 or
1318 SELECT * FROM foo ORDER BY name COLLATE perllocale
1320 Unicode handling
1322 If the attribute "$dbh->{sqlite_unicode}" is set, strings coming from
1323 the database and passed to the collation function will be properly
1324 tagged with the utf8 flag; but this only works if the "sqlite_unicode"
1325 attribute is set before the first call to a perl collation sequence .
1326 The recommended way to activate unicode is to set the parameter at
1327 connection time :
1328 my $dbh = DBI->connect(
1330 "dbi:SQLite:dbname=foo", "", "",
1331 {
1332 RaiseError => 1,
1333 sqlite_unicode => 1,
1334 }
1335 );
1336 Adding user-defined collations
1338 The native SQLite API for adding user-defined collations is exposed
1339 through methods "sqlite_create_collation" and
1340 "sqlite_collation_needed".
1341 To avoid calling these functions every time a $dbh handle is created,
1343 "DBD::SQLite" offers a simpler interface through the
1344 %DBD::SQLite::COLLATION hash : just insert your own collation functions
1345 in that hash, and whenever an unknown collation name is encountered in
1346 SQL, the appropriate collation function will be loaded on demand from
1347 the hash. For example, here is a way to sort text values regardless of
1348 their accented characters :
1349 use DBD::SQLite;
1351 $DBD::SQLite::COLLATION{no_accents} = sub {
1352 my ( $a, $b ) = map lc, @_;
1353 tr[����������������������������]
1354 [aaaaaacdeeeeiiiinoooooouuuuy] for $a, $b;
1355 $a cmp $b;
1356 };
1357 my $dbh = DBI->connect("dbi:SQLite:dbname=dbfile");
1358 my $sql = "SELECT ... FROM ... ORDER BY ... COLLATE no_accents");
1359 my $rows = $dbh->selectall_arrayref($sql);
1360 The builtin "perl" or "perllocale" collations are predefined in that
1362 same hash.
1363 The COLLATION hash is a global registry within the current process;
1365 hence there is a risk of undesired side-effects. Therefore, to prevent
1366 action at distance, the hash is implemented as a "write-only" hash,
1367 that will happily accept new entries, but will raise an exception if
1368 any attempt is made to override or delete a existing entry (including
1369 the builtin "perl" and "perllocale").
1370 If you really, really need to change or delete an entry, you can always
1372 grab the tied object underneath %DBD::SQLite::COLLATION --- but don't
1373 do that unless you really know what you are doing. Also observe that
1374 changes in the global hash will not modify existing collations in
1375 existing database handles: it will only affect new requests for
1376 collations. In other words, if you want to change the behaviour of a
1377 collation within an existing $dbh, you need to call the
1378 "create_collation" method directly.
1379
1381 SQLite is bundled with an extension module for full-text indexing.
1382 Tables with this feature enabled can be efficiently queried to find
1383 rows that contain one or more instances of some specified words, in any
1384 column, even if the table contains many large documents.
1385 Explanations for using this feature are provided in a separate
1387 document: see DBD::SQLite::Fulltext_search.
1388
1390 The RTREE extension module within SQLite adds support for creating a
1391 R-Tree, a special index for range and multidimensional queries. This
1392 allows users to create tables that can be loaded with (as an example)
1393 geospatial data such as latitude/longitude coordinates for buildings
1394 within a city :
1395 CREATE VIRTUAL TABLE city_buildings USING rtree(
1397 id, -- Integer primary key
1398 minLong, maxLong, -- Minimum and maximum longitude
1399 minLat, maxLat -- Minimum and maximum latitude
1400 );
1401 then query which buildings overlap or are contained within a specified
1403 region:
1404 # IDs that are contained within query coordinates
1406 my $contained_sql = <<"";
1407 SELECT id FROM city_buildings
1408 WHERE minLong >= ? AND maxLong <= ?
1409 AND minLat >= ? AND maxLat <= ?
1410 # ... and those that overlap query coordinates
1412 my $overlap_sql = <<"";
1413 SELECT id FROM city_buildings
1414 WHERE maxLong >= ? AND minLong <= ?
1415 AND maxLat >= ? AND minLat <= ?
1416 my $contained = $dbh->selectcol_arrayref($contained_sql,undef,
1418 $minLong, $maxLong, $minLat, $maxLat);
1419 my $overlapping = $dbh->selectcol_arrayref($overlap_sql,undef,
1421 $minLong, $maxLong, $minLat, $maxLat);
1422 For more detail, please see the SQLite R-Tree page
1424 (<https://www.sqlite.org/rtree.html>). Note that custom R-Tree queries
1425 using callbacks, as mentioned in the prior link, have not been
1426 implemented yet.
1427
1429 SQLite has a concept of "virtual tables" which look like regular tables
1430 but are implemented internally through specific functions. The
1431 fulltext or R* tree features described in the previous chapters are
1432 examples of such virtual tables, implemented in C code.
1433 "DBD::SQLite" also supports virtual tables implemented in Perl code:
1435 see DBD::SQLite::VirtualTable for using or implementing such virtual
1436 tables. These can have many interesting uses for joining regular DBMS
1437 data with some other kind of data within your Perl programs. Bundled
1438 with the present distribution are :
1439 · DBD::SQLite::VirtualTable::FileContent : implements a virtual
1441 column that exposes file contents. This is especially useful in
1442 conjunction with a fulltext index; see
1443 DBD::SQLite::Fulltext_search.
1444 · DBD::SQLite::VirtualTable::PerlData : binds to a Perl array within
1446 the Perl program. This can be used for simple import/export
1447 operations, for debugging purposes, for joining data from different
1448 sources, etc.
1449 Other Perl virtual tables may also be published separately on CPAN.
1451
1453 The following items remain to be done.
1454 Leak Detection
1456 Implement one or more leak detection tests that only run during
1457 AUTOMATED_TESTING and RELEASE_TESTING and validate that none of the C
1458 code we work with leaks.
1459 Stream API for Blobs
1461 Reading/writing into blobs using "sqlite2_blob_open" /
1462 "sqlite2_blob_close".
1463 Support for custom callbacks for R-Tree queries
1465 Custom queries of a R-Tree index using a callback are possible with the
1466 SQLite C API (<https://www.sqlite.org/rtree.html>), so one could
1467 potentially use a callback that narrowed the result set down based on a
1468 specific need, such as querying for overlapping circles.
1469
1471 Bugs should be reported to GitHub issues:
1472 <https://github.com/DBD-SQLite/DBD-SQLite/issues>
1474 or via RT if you prefer:
1476 <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
1478 Note that bugs of bundled SQLite library (i.e. bugs in "sqlite3.[ch]")
1480 should be reported to the SQLite developers at sqlite.org via their bug
1481 tracker or via their mailing list.
1482 The master repository is on GitHub:
1484 <https://github.com/DBD-SQLite/DBD-SQLite>.
1486 We also have a mailing list:
1488 <http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite>
1490
1492 Matt Sergeant <matt@sergeant.org>
1493 Francis J. Lacoste <flacoste@logreport.org>
1495 Wolfgang Sourdeau <wolfgang@logreport.org>
1497 Adam Kennedy <adamk@cpan.org>
1499 Max Maischein <corion@cpan.org>
1501 Laurent Dami <dami@cpan.org>
1503 Kenichi Ishigaki <ishigaki@cpan.org>
1505
1507 The bundled SQLite code in this distribution is Public Domain.
1508 DBD::SQLite is copyright 2002 - 2007 Matt Sergeant.
1510 Some parts copyright 2008 Francis J. Lacoste.
1512 Some parts copyright 2008 Wolfgang Sourdeau.
1514 Some parts copyright 2008 - 2013 Adam Kennedy.
1516 Some parts copyright 2009 - 2013 Kenichi Ishigaki.
1518 Some parts derived from DBD::SQLite::Amalgamation copyright 2008 Audrey
1520 Tang.
1521 This program is free software; you can redistribute it and/or modify it
1523 under the same terms as Perl itself.
1524 The full text of the license can be found in the LICENSE file included
1526 with this module.
1527perl v5.30.1 2020-02-04 DBD::SQLite(3)