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 <http://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 <http://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 Database Name Is A File Name
46 SQLite creates a file per a database. You should pass the "path" of the
47 database file (with or without a parent directory) in the DBI
48 connection string (as a database "name"):
49 my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");
51 The file is opened in read/write mode, and will be created if it does
53 not exist yet.
54 Although the database is stored in a single file, the directory
56 containing the database file must be writable by SQLite because the
57 library will create several temporary files there.
58 If the filename $dbfile is ":memory:", then a private, temporary in-
60 memory database is created for the connection. This in-memory database
61 will vanish when the database connection is closed. It is handy for
62 your library tests.
63 Note that future versions of SQLite might make use of additional
65 special filenames that begin with the ":" character. It is recommended
66 that when a database filename actually does begin with a ":" character
67 you should prefix the filename with a pathname such as "./" to avoid
68 ambiguity.
69 If the filename $dbfile is an empty string, then a private, temporary
71 on-disk database will be created. This private database will be
72 automatically deleted as soon as the database connection is closed.
73 Accessing A Database With Other Tools
75 To access the database from the command line, try using "dbish" which
76 comes with the DBI::Shell module. Just type:
77 dbish dbi:SQLite:foo.db
79 On the command line to access the file foo.db.
81 Alternatively you can install SQLite from the link above without
83 conflicting with DBD::SQLite and use the supplied "sqlite3" command
84 line tool.
85 Blobs
87 As of version 1.11, blobs should "just work" in SQLite as text columns.
88 However this will cause the data to be treated as a string, so SQL
89 statements such as length(x) will return the length of the column as a
90 NUL terminated string, rather than the size of the blob in bytes. In
91 order to store natively as a BLOB use the following code:
92 use DBI qw(:sql_types);
94 my $dbh = DBI->connect("dbi:SQLite:dbfile","","");
95 my $blob = `cat foo.jpg`;
97 my $sth = $dbh->prepare("INSERT INTO mytable VALUES (1, ?)");
98 $sth->bind_param(1, $blob, SQL_BLOB);
99 $sth->execute();
100 And then retrieval just works:
102 $sth = $dbh->prepare("SELECT * FROM mytable WHERE id = 1");
104 $sth->execute();
105 my $row = $sth->fetch;
106 my $blobo = $row->[1];
107 # now $blobo == $blob
109 Functions And Bind Parameters
111 As of this writing, a SQL that compares a return value of a function
112 with a numeric bind value like this doesn't work as you might expect.
113 my $sth = $dbh->prepare(q{
115 SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
116 });
117 $sth->execute(5);
118 This is because DBD::SQLite assumes that all the bind values are text
120 (and should be quoted) by default. Thus the above statement becomes
121 like this while executing:
122 SELECT bar FROM foo GROUP BY bar HAVING count(*) > "5";
124 There are two workarounds for this.
126 Use bind_param() explicitly
128 As shown above in the "BLOB" section, you can always use
129 "bind_param()" to tell the type of a bind value.
130 use DBI qw(:sql_types); # Don't forget this
132 my $sth = $dbh->prepare(q{
134 SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
135 });
136 $sth->bind_param(1, 5, SQL_INTEGER);
137 $sth->execute();
138 Add zero to make it a number
140 This is somewhat weird, but works anyway.
141 my $sth = $dbh->prepare(q{
143 SELECT bar FROM foo GROUP BY bar HAVING count(*) > (? + 0);
144 });
145 $sth->execute(5);
146 Foreign Keys
148 BE PREPARED! WOLVES APPROACH!!
149 SQLite has started supporting foreign key constraints since 3.6.19
151 (released on Oct 14, 2009; bundled with DBD::SQLite 1.26_05). To be
152 exact, SQLite has long been able to parse a schema with foreign keys,
153 but the constraints has not been enforced. Now you can issue a pragma
154 actually to enable this feature and enforce the constraints.
155 To do this, issue the following pragma (see below), preferably as soon
157 as you connect to a database and you're not in a transaction:
158 $dbh->do("PRAGMA foreign_keys = ON");
160 And you can explicitly disable the feature whenever you like by turning
162 the pragma off:
163 $dbh->do("PRAGMA foreign_keys = OFF");
165 As of this writing, this feature is disabled by default by the sqlite
167 team, and by us, to secure backward compatibility, as this feature may
168 break your applications, and actually broke some for us. If you have
169 used a schema with foreign key constraints but haven't cared them much
170 and supposed they're always ignored for SQLite, be prepared, and please
171 do extensive testing to ensure that your applications will continue to
172 work when the foreign keys support is enabled by default. It is very
173 likely that the sqlite team will turn it default-on in the future, and
174 we plan to do it NO LATER THAN they do so.
175 See <http://www.sqlite.org/foreignkeys.html> for details.
177 Pragma
179 SQLite has a set of "Pragma"s to modifiy its operation or to query for
180 its internal data. These are specific to SQLite and are not likely to
181 work with other DBD libraries, but you may find some of these are quite
182 useful. DBD::SQLite actually sets some (like "show_datatypes") for you
183 when you connect to a database. See
184 <http://www.sqlite.org/pragma.html> for details.
185 Transactions
187 DBI/DBD::SQLite's transactions may be a bit confusing. They behave
188 differently according to the status of the "AutoCommit" flag:
189 When the AutoCommit flag is on
191 You're supposed to always use the auto-commit mode, except you
192 explicitly begin a transaction, and when the transaction ended,
193 you're supposed to go back to the auto-commit mode. To begin a
194 transaction, call "begin_work" method, or issue a "BEGIN"
195 statement. To end it, call "commit/rollback" methods, or issue the
196 corresponding statements.
197 $dbh->{AutoCommit} = 1;
199 $dbh->begin_work; # or $dbh->do('BEGIN TRANSACTION');
201 # $dbh->{AutoCommit} is turned off temporarily during a transaction;
203 $dbh->commit; # or $dbh->do('COMMIT');
205 # $dbh->{AutoCommit} is turned on again;
207 When the AutoCommit flag is off
209 You're supposed to always use the transactinal mode, until you
210 explicitly turn on the AutoCommit flag. You can explicitly issue a
211 "BEGIN" statement (only when an actual transaction has not begun
212 yet) but you're not allowed to call "begin_work" method (if you
213 don't issue a "BEGIN", it will be issued internally). You can
214 commit or roll it back freely. Another transaction will
215 automatically begins if you execute another statement.
216 $dbh->{AutoCommit} = 0;
218 # $dbh->do('BEGIN TRANSACTION') is not necessary, but possible
220 ...
222 $dbh->commit; # or $dbh->do('COMMIT');
224 # $dbh->{AutoCommit} stays intact;
226 $dbh->{AutoCommit} = 1; # ends the transactional mode
228 This "AutoCommit" mode is independent from the autocommit mode of the
230 internal SQLite library, which always begins by a "BEGIN" statement,
231 and ends by a "COMMIT" or a <ROLLBACK>.
232 Performance
234 SQLite is fast, very fast. Matt processed my 72MB log file with it,
235 inserting the data (400,000+ rows) by using transactions and only
236 committing every 1000 rows (otherwise the insertion is quite slow), and
237 then performing queries on the data.
238 Queries like count(*) and avg(bytes) took fractions of a second to
240 return, but what surprised him most of all was:
241 SELECT url, count(*) as count
243 FROM access_log
244 GROUP BY url
245 ORDER BY count desc
246 LIMIT 20
247 To discover the top 20 hit URLs on the site (<http://axkit.org>), and
249 it returned within 2 seconds. He was seriously considering switching
250 his log analysis code to use this little speed demon!
251 Oh yeah, and that was with no indexes on the table, on a 400MHz PIII.
253 For best performance be sure to tune your hdparm settings if you are
255 using linux. Also you might want to set:
256 PRAGMA default_synchronous = OFF
258 Which will prevent sqlite from doing fsync's when writing (which slows
260 down non-transactional writes significantly) at the expense of some
261 peace of mind. Also try playing with the cache_size pragma.
262 The memory usage of SQLite can also be tuned using the cache_size
264 pragma.
265 $dbh->do("PRAGMA cache_size = 800000");
267 The above will allocate 800M for DB cache; the default is 2M. Your
269 sweet spot probably lies somewhere in between.
270
272 Database Handle Attributes
273 sqlite_version
274 Returns the version of the SQLite library which DBD::SQLite is
275 using, e.g., "2.8.0". Can only be read.
276 sqlite_unicode
278 If set to a true value, DBD::SQLite will turn the UTF-8 flag on for
279 all text strings coming out of the database (this feature is
280 currently disabled for perl < 5.8.5). For more details on the UTF-8
281 flag see perlunicode. The default is for the UTF-8 flag to be
282 turned off.
283 Also note that due to some bizarreness in SQLite's type system (see
285 <http://www.sqlite.org/datatype3.html>), if you want to retain
286 blob-style behavior for some columns under "$dbh->{sqlite_unicode}
287 = 1" (say, to store images in the database), you have to state so
288 explicitly using the 3-argument form of "bind_param" in DBI when
289 doing updates:
290 use DBI qw(:sql_types);
292 $dbh->{sqlite_unicode} = 1;
293 my $sth = $dbh->prepare("INSERT INTO mytable (blobcolumn) VALUES (?)");
294 # Binary_data will be stored as is.
296 $sth->bind_param(1, $binary_data, SQL_BLOB);
297 Defining the column type as "BLOB" in the DDL is not sufficient.
299 This attribute was originally named as "unicode", and renamed to
301 "sqlite_unicode" for integrity since version 1.26_06. Old "unicode"
302 attribute is still accessible but will be deprecated in the near
303 future.
304
306 table_info
307 $sth = $dbh->table_info(undef, $schema, $table, $type, \%attr);
308 Returns all tables and schemas (databases) as specified in "table_info"
310 in DBI. The schema and table arguments will do a "LIKE" search. You
311 can specify an ESCAPE character by including an 'Escape' attribute in
312 \%attr. The $type argument accepts a comma seperated list of the
313 following types 'TABLE', 'VIEW', 'LOCAL TEMPORARY' and 'SYSTEM TABLE'
314 (by default all are returned). Note that a statement handle is
315 returned, and not a direct list of tables.
316 The following fields are returned:
318 TABLE_CAT: Always NULL, as SQLite does not have the concept of
320 catalogs.
321 TABLE_SCHEM: The name of the schema (database) that the table or view
323 is in. The default schema is 'main', temporary tables are in 'temp' and
324 other databases will be in the name given when the database was
325 attached.
326 TABLE_NAME: The name of the table or view.
328 TABLE_TYPE: The type of object returned. Will be one of 'TABLE',
330 'VIEW', 'LOCAL TEMPORARY' or 'SYSTEM TABLE'.
331
333 The following methods can be called via the func() method with a little
334 tweak, but the use of func() method is now discouraged by the DBI
335 author for various reasons (see DBI's document
336 http://search.cpan.org/dist/DBI/lib/DBI/DBD.pm#Using_install_method()_to_expose_driver-private_methods
337 <http://search.cpan.org/dist/DBI/lib/DBI/DBD.pm#Using_install_method()_to_expose_driver-
338 private_methods> for details). So, if you're using DBI >= 1.608, use
339 these "sqlite_" methods. If you need to use an older DBI, you can call
340 these like this:
341 $dbh->func( ..., "(method name without sqlite_ prefix)" );
343 $dbh->sqlite_last_insert_rowid()
345 This method returns the last inserted rowid. If you specify an INTEGER
346 PRIMARY KEY as the first column in your table, that is the column that
347 is returned. Otherwise, it is the hidden ROWID column. See the sqlite
348 docs for details.
349 Generally you should not be using this method. Use the DBI
351 last_insert_id method instead. The usage of this is:
352 $h->last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ])
354 Running "$h->last_insert_id("","","","")" is the equivalent of running
356 "$dbh->sqlite_last_insert_rowid()" directly.
357 $dbh->sqlite_busy_timeout()
359 Retrieve the current busy timeout.
360 $dbh->sqlite_busy_timeout( $ms )
362 Set the current busy timeout. The timeout is in milliseconds.
363 $dbh->sqlite_create_function( $name, $argc, $code_ref )
365 This method will register a new function which will be useable in an
366 SQL query. The method's parameters are:
367 $name
369 The name of the function. This is the name of the function as it
370 will be used from SQL.
371 $argc
373 The number of arguments taken by the function. If this number is
374 -1, the function can take any number of arguments.
375 $code_ref
377 This should be a reference to the function's implementation.
378 For example, here is how to define a now() function which returns the
380 current number of seconds since the epoch:
381 $dbh->sqlite_create_function( 'now', 0, sub { return time } );
383 After this, it could be use from SQL as:
385 INSERT INTO mytable ( now() );
387 REGEXP function
389 SQLite includes syntactic support for an infix operator 'REGEXP', but
391 without any implementation. The "DBD::SQLite" driver automatically
392 registers an implementation that performs standard perl regular
393 expression matching, using current locale. So for example you can
394 search for words starting with an 'A' with a query like
395 SELECT * from table WHERE column REGEXP '\bA\w+'
397 If you want case-insensitive searching, use perl regex flags, like this
399 :
400 SELECT * from table WHERE column REGEXP '(?i:\bA\w+)'
402 The default REGEXP implementation can be overriden through the
404 "create_function" API described above.
405 Note that regexp matching will not use SQLite indices, but will iterate
407 over all rows, so it could be quite costly in terms of performance.
408 $dbh->sqlite_create_collation( $name, $code_ref )
410 This method manually registers a new function which will be useable in
411 an SQL query as a COLLATE option for sorting. Such functions can also
412 be registered automatically on demand: see section "COLLATION
413 FUNCTIONS" below.
414 The method's parameters are:
416 $name
418 The name of the function exposed to SQL.
419 $code_ref
421 Reference to the function's implementation. The driver will check
422 that this is a proper sorting function.
423 $dbh->sqlite_collation_needed( $code_ref )
425 This method manually registers a callback function that will be invoked
426 whenever an undefined collation sequence is required from an SQL
427 statement. The callback is invoked as
428 $code_ref->($dbh, $collation_name)
430 and should register the desired collation using
432 "sqlite_create_collation".
433 An initial callback is already registered by "DBD::SQLite", so for most
435 common cases it will be simpler to just add your collation sequences in
436 the %DBD::SQLite::COLLATION hash (see section "COLLATION FUNCTIONS"
437 below).
438 $dbh->sqlite_create_aggregate( $name, $argc, $pkg )
440 This method will register a new aggregate function which can then be
441 used from SQL. The method's parameters are:
442 $name
444 The name of the aggregate function, this is the name under which
445 the function will be available from SQL.
446 $argc
448 This is an integer which tells the SQL parser how many arguments
449 the function takes. If that number is -1, the function can take any
450 number of arguments.
451 $pkg
453 This is the package which implements the aggregator interface.
454 The aggregator interface consists of defining three methods:
456 new()
458 This method will be called once to create an object which should be
459 used to aggregate the rows in a particular group. The step() and
460 finalize() methods will be called upon the reference return by the
461 method.
462 step(@_)
464 This method will be called once for each row in the aggregate.
465 finalize()
467 This method will be called once all rows in the aggregate were
468 processed and it should return the aggregate function's result.
469 When there is no rows in the aggregate, finalize() will be called
470 right after new().
471 Here is a simple aggregate function which returns the variance (example
473 adapted from pysqlite):
474 package variance;
476 sub new { bless [], shift; }
478 sub step {
480 my ( $self, $value ) = @_;
481 push @$self, $value;
483 }
484 sub finalize {
486 my $self = $_[0];
487 my $n = @$self;
489 # Variance is NULL unless there is more than one row
491 return undef unless $n || $n == 1;
492 my $mu = 0;
494 foreach my $v ( @$self ) {
495 $mu += $v;
496 }
497 $mu /= $n;
498 my $sigma = 0;
500 foreach my $v ( @$self ) {
501 $sigma += ($x - $mu)**2;
502 }
503 $sigma = $sigma / ($n - 1);
504 return $sigma;
506 }
507 $dbh->sqlite_create_aggregate( "variance", 1, 'variance' );
509 The aggregate function can then be used as:
511 SELECT group_name, variance(score)
513 FROM results
514 GROUP BY group_name;
515 For more examples, see the DBD::SQLite::Cookbook.
517 $dbh->sqlite_progress_handler( $n_opcodes, $code_ref )
519 This method registers a handler to be invoked periodically during long
520 running calls to SQLite.
521 An example use for this interface is to keep a GUI updated during a
523 large query. The parameters are:
524 $n_opcodes
526 The progress handler is invoked once for every $n_opcodes virtual
527 machine opcodes in SQLite.
528 $code_ref
530 Reference to the handler subroutine. If the progress handler
531 returns non-zero, the SQLite operation is interrupted. This feature
532 can be used to implement a "Cancel" button on a GUI dialog box.
533 Set this argument to "undef" if you want to unregister a previous
535 progress handler.
536 $dbh->sqlite_commit_hook( $code_ref )
538 This method registers a callback function to be invoked whenever a
539 transaction is committed. Any callback set by a previous call to
540 "sqlite_commit_hook" is overridden. A reference to the previous
541 callback (if any) is returned. Registering an "undef" disables the
542 callback.
543 When the commit hook callback returns zero, the commit operation is
545 allowed to continue normally. If the callback returns non-zero, then
546 the commit is converted into a rollback (in that case, any attempt to
547 explicitly call "$dbh->rollback()" afterwards would yield an error).
548 $dbh->sqlite_rollback_hook( $code_ref )
550 This method registers a callback function to be invoked whenever a
551 transaction is rolled back. Any callback set by a previous call to
552 "sqlite_rollback_hook" is overridden. A reference to the previous
553 callback (if any) is returned. Registering an "undef" disables the
554 callback.
555 $dbh->sqlite_update_hook( $code_ref )
557 This method registers a callback function to be invoked whenever a row
558 is updated, inserted or deleted. Any callback set by a previous call to
559 "sqlite_update_hook" is overridden. A reference to the previous
560 callback (if any) is returned. Registering an "undef" disables the
561 callback.
562 The callback will be called as
564 $code_ref->($action_code, $database, $table, $rowid)
566 where
568 $action_code
570 is an integer equal to either "DBD::SQLite::INSERT",
571 "DBD::SQLite::DELETE" or "DBD::SQLite::UPDATE" (see "Action
572 Codes");
573 $database
575 is the name of the database containing the affected row;
576 $table
578 is the name of the table containing the affected row;
579 $rowid
581 is the unique 64-bit signed integer key of the affected row within
582 that table.
583 $dbh->sqlite_set_authorizer( $code_ref )
585 This method registers an authorizer callback to be invoked whenever SQL
586 statements are being compiled by the "prepare" in DBI method. The
587 authorizer callback should return "DBD::SQLite::OK" to allow the
588 action, "DBD::SQLite::IGNORE" to disallow the specific action but allow
589 the SQL statement to continue to be compiled, or "DBD::SQLite::DENY" to
590 cause the entire SQL statement to be rejected with an error. If the
591 authorizer callback returns any other value, then then "prepare" call
592 that triggered the authorizer will fail with an error message.
593 An authorizer is used when preparing SQL statements from an untrusted
595 source, to ensure that the SQL statements do not try to access data
596 they are not allowed to see, or that they do not try to execute
597 malicious statements that damage the database. For example, an
598 application may allow a user to enter arbitrary SQL queries for
599 evaluation by a database. But the application does not want the user to
600 be able to make arbitrary changes to the database. An authorizer could
601 then be put in place while the user-entered SQL is being prepared that
602 disallows everything except SELECT statements.
603 The callback will be called as
605 $code_ref->($action_code, $string1, $string2, $database, $trigger_or_view)
607 where
609 $action_code
611 is an integer that specifies what action is being authorized (see
612 "Action Codes").
613 $string1, $string2
615 are strings that depend on the action code (see "Action Codes").
616 $database
618 is the name of the database ("main", "temp", etc.) if applicable.
619 $trigger_or_view
621 is the name of the inner-most trigger or view that is responsible
622 for the access attempt, or "undef" if this access attempt is
623 directly from top-level SQL code.
624 $dbh->sqlite_backup_from_file( $filename )
626 This method accesses the SQLite Online Backup API, and will take a
627 backup of the named database file, copying it to, and overwriting, your
628 current database connection. This can be particularly handy if your
629 current connection is to the special :memory: database, and you wish to
630 populate it from an existing DB.
631 $dbh->sqlite_backup_to_file( $filename )
633 This method accesses the SQLite Online Backup API, and will take a
634 backup of the currently connected database, and write it out to the
635 named file.
636 $dbh->sqlite_enable_load_extension( $bool )
638 Calling this method with a true value enables loading (external)
639 sqlite3 extensions. After the call, you can load extensions like this:
640 $dbh->sqlite_enable_load_extension(1);
642 $sth = $dbh->prepare("select load_extension('libsqlitefunctions.so')")
643 or die "Cannot prepare: " . $dbh->errstr();
644
646 A subset of SQLite C constants are made available to Perl, because they
647 may be needed when writing hooks or authorizer callbacks. For accessing
648 such constants, the "DBD::Sqlite" module must be explicitly "use"d at
649 compile time. For example, an authorizer that forbids any DELETE
650 operation would be written as follows :
651 use DBD::SQLite;
653 $dbh->sqlite_set_authorizer(sub {
654 my $action_code = shift;
655 return $action_code == DBD::SQLite::DELETE ? DBD::SQLite::DENY
656 : DBD::SQLite::OK;
657 });
658 The list of constants implemented in "DBD::SQLite" is given below; more
660 information can be found ad at
661 <http://www.sqlite.org/c3ref/constlist.html>.
662 Authorizer Return Codes
664 OK
665 DENY
666 IGNORE
667 Action Codes
669 The "set_authorizer" method registers a callback function that is
670 invoked to authorize certain SQL statement actions. The first parameter
671 to the callback is an integer code that specifies what action is being
672 authorized. The second and third parameters to the callback are
673 strings, the meaning of which varies according to the action code.
674 Below is the list of action codes, together with their associated
675 strings.
676 # constant string1 string2
678 # ======== ======= =======
679 CREATE_INDEX Index Name Table Name
680 CREATE_TABLE Table Name undef
681 CREATE_TEMP_INDEX Index Name Table Name
682 CREATE_TEMP_TABLE Table Name undef
683 CREATE_TEMP_TRIGGER Trigger Name Table Name
684 CREATE_TEMP_VIEW View Name undef
685 CREATE_TRIGGER Trigger Name Table Name
686 CREATE_VIEW View Name undef
687 DELETE Table Name undef
688 DROP_INDEX Index Name Table Name
689 DROP_TABLE Table Name undef
690 DROP_TEMP_INDEX Index Name Table Name
691 DROP_TEMP_TABLE Table Name undef
692 DROP_TEMP_TRIGGER Trigger Name Table Name
693 DROP_TEMP_VIEW View Name undef
694 DROP_TRIGGER Trigger Name Table Name
695 DROP_VIEW View Name undef
696 INSERT Table Name undef
697 PRAGMA Pragma Name 1st arg or undef
698 READ Table Name Column Name
699 SELECT undef undef
700 TRANSACTION Operation undef
701 UPDATE Table Name Column Name
702 ATTACH Filename undef
703 DETACH Database Name undef
704 ALTER_TABLE Database Name Table Name
705 REINDEX Index Name undef
706 ANALYZE Table Name undef
707 CREATE_VTABLE Table Name Module Name
708 DROP_VTABLE Table Name Module Name
709 FUNCTION undef Function Name
710 SAVEPOINT Operation Savepoint Name
711
713 Definition
714 SQLite v3 provides the ability for users to supply arbitrary comparison
715 functions, known as user-defined "collation sequences" or "collating
716 functions", to be used for comparing two text values.
717 <http://www.sqlite.org/datatype3.html#collation> explains how
718 collations are used in various SQL expressions.
719 Builtin collation sequences
721 The following collation sequences are builtin within SQLite :
722 BINARY
724 Compares string data using memcmp(), regardless of text encoding.
725 NOCASE
727 The same as binary, except the 26 upper case characters of ASCII
728 are folded to their lower case equivalents before the comparison is
729 performed. Note that only ASCII characters are case folded. SQLite
730 does not attempt to do full UTF case folding due to the size of the
731 tables required.
732 RTRIM
734 The same as binary, except that trailing space characters are
735 ignored.
736 In addition, "DBD::SQLite" automatically installs the following
738 collation sequences :
739 perl
741 corresponds to the Perl "cmp" operator
742 perllocale
744 Perl "cmp" operator, in a context where "use locale" is activated.
745 Usage
747 You can write for example
748 CREATE TABLE foo(
750 txt1 COLLATE perl,
751 txt2 COLLATE perllocale,
752 txt3 COLLATE nocase
753 )
754 or
756 SELECT * FROM foo ORDER BY name COLLATE perllocale
758 Unicode handling
760 If the attribute "$dbh->{sqlite_unicode}" is set, strings coming from
761 the database and passed to the collation function will be properly
762 tagged with the utf8 flag; but this only works if the "sqlite_unicode"
763 attribute is set before the first call to a perl collation sequence .
764 The recommended way to activate unicode is to set the parameter at
765 connection time :
766 my $dbh = DBI->connect(
768 "dbi:SQLite:dbname=foo", "", "",
769 {
770 RaiseError => 1,
771 sqlite_unicode => 1,
772 }
773 );
774 Adding user-defined collations
776 The native SQLite API for adding user-defined collations is exposed
777 through methods "sqlite_create_collation" and
778 "sqlite_collation_needed".
779 To avoid calling these functions every time a $dbh handle is created,
781 "DBD::SQLite" offers a simpler interface through the
782 %DBD::SQLite::COLLATION hash : just insert your own collation functions
783 in that hash, and whenever an unknown collation name is encountered in
784 SQL, the appropriate collation function will be loaded on demand from
785 the hash. For example, here is a way to sort text values regardless of
786 their accented characters :
787 use DBD::SQLite;
789 $DBD::SQLite::COLLATION{no_accents} = sub {
790 my ( $a, $b ) = map lc, @_;
791 tr[aaaaeaaa~cd`eeeeeiiiien~ooooeo~ouuuuey]
792 [aaaaaacdeeeeiiiinoooooouuuuy] for $a, $b;
793 $a cmp $b;
794 };
795 my $dbh = DBI->connect("dbi:SQLite:dbname=dbfile");
796 my $sql = "SELECT ... FROM ... ORDER BY ... COLLATE no_accents");
797 my $rows = $dbh->selectall_arrayref($sql);
798 The builtin "perl" or "perllocale" collations are predefined in that
800 same hash.
801 The COLLATION hash is a global registry within the current process;
803 hence there is a risk of undesired side-effects. Therefore, to prevent
804 action at distance, the hash is implemented as a "write-only" hash,
805 that will happily accept new entries, but will raise an exception if
806 any attempt is made to override or delete a existing entry (including
807 the builtin "perl" and "perllocale").
808 If you really, really need to change or delete an entry, you can always
810 grab the tied object underneath %DBD::SQLite::COLLATION --- but don't
811 do that unless you really know what you are doing. Also observe that
812 changes in the global hash will not modify existing collations in
813 existing database handles: it will only affect new requests for
814 collations. In other words, if you want to change the behaviour of a
815 collation within an existing $dbh, you need to call the
816 "create_collation" method directly.
817
819 The following items remain to be done.
820 Warnings Upgrade
822 We currently use a horridly hacky method to issue and suppress
823 warnings. It suffices for now, but just barely.
824 Migrate all of the warning code to use the recommended DBI warnings.
826 Leak Detection
828 Implement one or more leak detection tests that only run during
829 AUTOMATED_TESTING and RELEASE_TESTING and validate that none of the C
830 code we work with leaks.
831 Stream API for Blobs
833 Reading/writing into blobs using "sqlite2_blob_open" /
834 "sqlite2_blob_close".
835 Flags for sqlite3_open_v2
837 Support the full API of sqlite3_open_v2 (flags for opening the file).
838
840 Bugs should be reported via the CPAN bug tracker at
841 http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite
843 <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
844 Note that bugs of bundled sqlite library (i.e. bugs in "sqlite3.[ch]")
846 should be reported to the sqlite developers at sqlite.org via their bug
847 tracker or via their mailing list.
848
850 Matt Sergeant <matt@sergeant.org>
851 Francis J. Lacoste <flacoste@logreport.org>
853 Wolfgang Sourdeau <wolfgang@logreport.org>
855 Adam Kennedy <adamk@cpan.org>
857 Max Maischein <corion@cpan.org>
859 Laurent Dami <dami@cpan.org>
861 Kenichi Ishigaki <ishigaki@cpan.org>
863
865 The bundled SQLite code in this distribution is Public Domain.
866 DBD::SQLite is copyright 2002 - 2007 Matt Sergeant.
868 Some parts copyright 2008 Francis J. Lacoste.
870 Some parts copyright 2008 Wolfgang Sourdeau.
872 Some parts copyright 2008 - 2010 Adam Kennedy.
874 Some parts derived from DBD::SQLite::Amalgamation copyright 2008 Audrey
876 Tang.
877 This program is free software; you can redistribute it and/or modify it
879 under the same terms as Perl itself.
880 The full text of the license can be found in the LICENSE file included
882 with this module.
883perl v5.12.1 2010-01-08 DBD::SQLite(3)