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 "foreign_keys" above) for
183 you when you connect to a database. See
184 <http://www.sqlite.org/pragma.html> for details.
185 Performance
187 SQLite is fast, very fast. Matt processed my 72MB log file with it,
188 inserting the data (400,000+ rows) by using transactions and only
189 committing every 1000 rows (otherwise the insertion is quite slow), and
190 then performing queries on the data.
191 Queries like count(*) and avg(bytes) took fractions of a second to
193 return, but what surprised him most of all was:
194 SELECT url, count(*) as count
196 FROM access_log
197 GROUP BY url
198 ORDER BY count desc
199 LIMIT 20
200 To discover the top 20 hit URLs on the site (<http://axkit.org>), and
202 it returned within 2 seconds. He was seriously considering switching
203 his log analysis code to use this little speed demon!
204 Oh yeah, and that was with no indexes on the table, on a 400MHz PIII.
206 For best performance be sure to tune your hdparm settings if you are
208 using linux. Also you might want to set:
209 PRAGMA default_synchronous = OFF
211 Which will prevent sqlite from doing fsync's when writing (which slows
213 down non-transactional writes significantly) at the expense of some
214 peace of mind. Also try playing with the cache_size pragma.
215 The memory usage of SQLite can also be tuned using the cache_size
217 pragma.
218 $dbh->do("PRAGMA cache_size = 800000");
220 The above will allocate 800M for DB cache; the default is 2M. Your
222 sweet spot probably lies somewhere in between.
223
225 Database Handle Attributes
226 sqlite_version
227 Returns the version of the SQLite library which DBD::SQLite is
228 using, e.g., "2.8.0". Can only be read.
229 sqlite_unicode
231 If set to a true value, DBD::SQLite will turn the UTF-8 flag on for
232 all text strings coming out of the database (this feature is
233 currently disabled for perl < 5.8.5). For more details on the UTF-8
234 flag see perlunicode. The default is for the UTF-8 flag to be
235 turned off.
236 Also note that due to some bizarreness in SQLite's type system (see
238 <http://www.sqlite.org/datatype3.html>), if you want to retain
239 blob-style behavior for some columns under "$dbh->{sqlite_unicode}
240 = 1" (say, to store images in the database), you have to state so
241 explicitly using the 3-argument form of "bind_param" in DBI when
242 doing updates:
243 use DBI qw(:sql_types);
245 $dbh->{sqlite_unicode} = 1;
246 my $sth = $dbh->prepare("INSERT INTO mytable (blobcolumn) VALUES (?)");
247 # Binary_data will be stored as is.
249 $sth->bind_param(1, $binary_data, SQL_BLOB);
250 Defining the column type as "BLOB" in the DDL is not sufficient.
252 This attribute was originally named as "unicode", and renamed to
254 "sqlite_unicode" for integrity since version 1.26_06. Old "unicode"
255 attribute is still accessible but will be deprecated in the near
256 future.
257
259 table_info
260 $sth = $dbh->table_info(undef, $schema, $table, $type, \%attr);
261 Returns all tables and schemas (databases) as specified in "table_info"
263 in DBI. The schema and table arguments will do a "LIKE" search. You
264 can specify an ESCAPE character by including an 'Escape' attribute in
265 \%attr. The $type argument accepts a comma seperated list of the
266 following types 'TABLE', 'VIEW', 'LOCAL TEMPORARY' and 'SYSTEM TABLE'
267 (by default all are returned). Note that a statement handle is
268 returned, and not a direct list of tables.
269 The following fields are returned:
271 TABLE_CAT: Always NULL, as SQLite does not have the concept of
273 catalogs.
274 TABLE_SCHEM: The name of the schema (database) that the table or view
276 is in. The default schema is 'main', temporary tables are in 'temp' and
277 other databases will be in the name given when the database was
278 attached.
279 TABLE_NAME: The name of the table or view.
281 TABLE_TYPE: The type of object returned. Will be one of 'TABLE',
283 'VIEW', 'LOCAL TEMPORARY' or 'SYSTEM TABLE'.
284
286 The following methods can be called via the func() method with a little
287 tweak, but the use of func() method is now discouraged by the DBI
288 author for various reasons (see DBI's document
289 <http://search.cpan.org/dist/DBI/lib/DBI/DBD.pm#Using_install_method()_to_expose_driver-private_methods>
290 for details). So, if you're using DBI >= 1.608, use these "sqlite_"
291 methods. If you need to use an older DBI, you can call these like this:
292 $dbh->func( ..., "(method name without sqlite_ prefix)" );
294 $dbh->sqlite_last_insert_rowid()
296 This method returns the last inserted rowid. If you specify an INTEGER
297 PRIMARY KEY as the first column in your table, that is the column that
298 is returned. Otherwise, it is the hidden ROWID column. See the sqlite
299 docs for details.
300 Generally you should not be using this method. Use the DBI
302 last_insert_id method instead. The usage of this is:
303 $h->last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ])
305 Running "$h->last_insert_id("","","","")" is the equivalent of running
307 "$dbh->sqlite_last_insert_rowid()" directly.
308 $dbh->sqlite_busy_timeout()
310 Retrieve the current busy timeout.
311 $dbh->sqlite_busy_timeout( $ms )
313 Set the current busy timeout. The timeout is in milliseconds.
314 $dbh->sqlite_create_function( $name, $argc, $code_ref )
316 This method will register a new function which will be useable in an
317 SQL query. The method's parameters are:
318 $name
320 The name of the function. This is the name of the function as it
321 will be used from SQL.
322 $argc
324 The number of arguments taken by the function. If this number is
325 -1, the function can take any number of arguments.
326 $code_ref
328 This should be a reference to the function's implementation.
329 For example, here is how to define a now() function which returns the
331 current number of seconds since the epoch:
332 $dbh->sqlite_create_function( 'now', 0, sub { return time } );
334 After this, it could be use from SQL as:
336 INSERT INTO mytable ( now() );
338 REGEXP function
340 SQLite includes syntactic support for an infix operator 'REGEXP', but
342 without any implementation. The "DBD::SQLite" driver automatically
343 registers an implementation that performs standard perl regular
344 expression matching, using current locale. So for example you can
345 search for words starting with an 'A' with a query like
346 SELECT * from table WHERE column REGEXP '\bA\w+'
348 If you want case-insensitive searching, use perl regex flags, like this
350 :
351 SELECT * from table WHERE column REGEXP '(?i:\bA\w+)'
353 The default REGEXP implementation can be overriden through the
355 "create_function" API described above.
356 Note that regexp matching will not use SQLite indices, but will iterate
358 over all rows, so it could be quite costly in terms of performance.
359 $dbh->sqlite_create_collation( $name, $code_ref )
361 This method manually registers a new function which will be useable in
362 an SQL query as a COLLATE option for sorting. Such functions can also
363 be registered automatically on demand: see section "COLLATION
364 FUNCTIONS" below.
365 The method's parameters are:
367 $name
369 The name of the function exposed to SQL.
370 $code_ref
372 Reference to the function's implementation. The driver will check
373 that this is a proper sorting function.
374 $dbh->sqlite_collation_needed( $code_ref )
376 This method manually registers a callback function that will be invoked
377 whenever an undefined collation sequence is required from an SQL
378 statement. The callback is invoked as
379 $code_ref->($dbh, $collation_name)
381 and should register the desired collation using
383 "sqlite_create_collation".
384 An initial callback is already registered by "DBD::SQLite", so for most
386 common cases it will be simpler to just add your collation sequences in
387 the %DBD::SQLite::COLLATION hash (see section "COLLATION FUNCTIONS"
388 below).
389 $dbh->sqlite_create_aggregate( $name, $argc, $pkg )
391 This method will register a new aggregate function which can then be
392 used from SQL. The method's parameters are:
393 $name
395 The name of the aggregate function, this is the name under which
396 the function will be available from SQL.
397 $argc
399 This is an integer which tells the SQL parser how many arguments
400 the function takes. If that number is -1, the function can take any
401 number of arguments.
402 $pkg
404 This is the package which implements the aggregator interface.
405 The aggregator interface consists of defining three methods:
407 new()
409 This method will be called once to create an object which should be
410 used to aggregate the rows in a particular group. The step() and
411 finalize() methods will be called upon the reference return by the
412 method.
413 step(@_)
415 This method will be called once for each row in the aggregate.
416 finalize()
418 This method will be called once all rows in the aggregate were
419 processed and it should return the aggregate function's result.
420 When there is no rows in the aggregate, finalize() will be called
421 right after new().
422 Here is a simple aggregate function which returns the variance (example
424 adapted from pysqlite):
425 package variance;
427 sub new { bless [], shift; }
429 sub step {
431 my ( $self, $value ) = @_;
432 push @$self, $value;
434 }
435 sub finalize {
437 my $self = $_[0];
438 my $n = @$self;
440 # Variance is NULL unless there is more than one row
442 return undef unless $n || $n == 1;
443 my $mu = 0;
445 foreach my $v ( @$self ) {
446 $mu += $v;
447 }
448 $mu /= $n;
449 my $sigma = 0;
451 foreach my $v ( @$self ) {
452 $sigma += ($x - $mu)**2;
453 }
454 $sigma = $sigma / ($n - 1);
455 return $sigma;
457 }
458 $dbh->sqlite_create_aggregate( "variance", 1, 'variance' );
460 The aggregate function can then be used as:
462 SELECT group_name, variance(score)
464 FROM results
465 GROUP BY group_name;
466 For more examples, see the DBD::SQLite::Cookbook.
468 $dbh->sqlite_progress_handler( $n_opcodes, $code_ref )
470 This method registers a handler to be invoked periodically during long
471 running calls to SQLite.
472 An example use for this interface is to keep a GUI updated during a
474 large query. The parameters are:
475 $n_opcodes
477 The progress handler is invoked once for every $n_opcodes virtual
478 machine opcodes in SQLite.
479 $code_ref
481 Reference to the handler subroutine. If the progress handler
482 returns non-zero, the SQLite operation is interrupted. This feature
483 can be used to implement a "Cancel" button on a GUI dialog box.
484 Set this argument to "undef" if you want to unregister a previous
486 progress handler.
487 $dbh->sqlite_commit_hook( $code_ref )
489 This method registers a callback function to be invoked whenever a
490 transaction is committed. Any callback set by a previous call to
491 "sqlite_commit_hook" is overridden. A reference to the previous
492 callback (if any) is returned. Registering an "undef" disables the
493 callback.
494 When the commit hook callback returns zero, the commit operation is
496 allowed to continue normally. If the callback returns non-zero, then
497 the commit is converted into a rollback (in that case, any attempt to
498 explicitly call "$dbh->rollback()" afterwards would yield an error).
499 $dbh->sqlite_rollback_hook( $code_ref )
501 This method registers a callback function to be invoked whenever a
502 transaction is rolled back. Any callback set by a previous call to
503 "sqlite_rollback_hook" is overridden. A reference to the previous
504 callback (if any) is returned. Registering an "undef" disables the
505 callback.
506 $dbh->sqlite_update_hook( $code_ref )
508 This method registers a callback function to be invoked whenever a row
509 is updated, inserted or deleted. Any callback set by a previous call to
510 "sqlite_update_hook" is overridden. A reference to the previous
511 callback (if any) is returned. Registering an "undef" disables the
512 callback.
513 The callback will be called as
515 $code_ref->($action_code, $database, $table, $rowid)
517 where
519 $action_code
521 is an integer equal to either "DBD::SQLite::INSERT",
522 "DBD::SQLite::DELETE" or "DBD::SQLite::UPDATE" (see "Action
523 Codes");
524 $database
526 is the name of the database containing the affected row;
527 $table
529 is the name of the table containing the affected row;
530 $rowid
532 is the unique 64-bit signed integer key of the affected row within
533 that table.
534 $dbh->sqlite_set_authorizer( $code_ref )
536 This method registers an authorizer callback to be invoked whenever SQL
537 statements are being compiled by the "prepare" in DBI method. The
538 authorizer callback should return "DBD::SQLite::OK" to allow the
539 action, "DBD::SQLite::IGNORE" to disallow the specific action but allow
540 the SQL statement to continue to be compiled, or "DBD::SQLite::DENY" to
541 cause the entire SQL statement to be rejected with an error. If the
542 authorizer callback returns any other value, then then "prepare" call
543 that triggered the authorizer will fail with an error message.
544 An authorizer is used when preparing SQL statements from an untrusted
546 source, to ensure that the SQL statements do not try to access data
547 they are not allowed to see, or that they do not try to execute
548 malicious statements that damage the database. For example, an
549 application may allow a user to enter arbitrary SQL queries for
550 evaluation by a database. But the application does not want the user to
551 be able to make arbitrary changes to the database. An authorizer could
552 then be put in place while the user-entered SQL is being prepared that
553 disallows everything except SELECT statements.
554 The callback will be called as
556 $code_ref->($action_code, $string1, $string2, $database, $trigger_or_view)
558 where
560 $action_code
562 is an integer that specifies what action is being authorized (see
563 "Action Codes").
564 $string1, $string2
566 are strings that depend on the action code (see "Action Codes").
567 $database
569 is the name of the database ("main", "temp", etc.) if applicable.
570 $trigger_or_view
572 is the name of the inner-most trigger or view that is responsible
573 for the access attempt, or "undef" if this access attempt is
574 directly from top-level SQL code.
575 $dbh->sqlite_backup_from_file( $filename )
577 This method accesses the SQLite Online Backup API, and will take a
578 backup of the named database file, copying it to, and overwriting, your
579 current database connection. This can be particularly handy if your
580 current connection is to the special :memory: database, and you wish to
581 populate it from an existing DB.
582 $dbh->sqlite_backup_to_file( $filename )
584 This method accesses the SQLite Online Backup API, and will take a
585 backup of the currently connected database, and write it out to the
586 named file.
587 $dbh->sqlite_enable_load_extension( $bool )
589 Calling this method with a true value enables loading (external)
590 sqlite3 extensions. After the call, you can load extensions like this:
591 $dbh->sqlite_enable_load_extension(1);
593 $sth = $dbh->prepare("select load_extension('libsqlitefunctions.so')")
594 or die "Cannot prepare: " . $dbh->errstr();
595
597 A subset of SQLite C constants are made available to Perl, because they
598 may be needed when writing hooks or authorizer callbacks. For accessing
599 such constants, the "DBD::Sqlite" module must be explicitly "use"d at
600 compile time. For example, an authorizer that forbids any DELETE
601 operation would be written as follows :
602 use DBD::SQLite;
604 $dbh->sqlite_set_authorizer(sub {
605 my $action_code = shift;
606 return $action_code == DBD::SQLite::DELETE ? DBD::SQLite::DENY
607 : DBD::SQLite::OK;
608 });
609 The list of constants implemented in "DBD::SQLite" is given below; more
611 information can be found ad at
612 <http://www.sqlite.org/c3ref/constlist.html>.
613 Authorizer Return Codes
615 OK
616 DENY
617 IGNORE
618 Action Codes
620 The "set_authorizer" method registers a callback function that is
621 invoked to authorize certain SQL statement actions. The first parameter
622 to the callback is an integer code that specifies what action is being
623 authorized. The second and third parameters to the callback are
624 strings, the meaning of which varies according to the action code.
625 Below is the list of action codes, together with their associated
626 strings.
627 # constant string1 string2
629 # ======== ======= =======
630 CREATE_INDEX Index Name Table Name
631 CREATE_TABLE Table Name undef
632 CREATE_TEMP_INDEX Index Name Table Name
633 CREATE_TEMP_TABLE Table Name undef
634 CREATE_TEMP_TRIGGER Trigger Name Table Name
635 CREATE_TEMP_VIEW View Name undef
636 CREATE_TRIGGER Trigger Name Table Name
637 CREATE_VIEW View Name undef
638 DELETE Table Name undef
639 DROP_INDEX Index Name Table Name
640 DROP_TABLE Table Name undef
641 DROP_TEMP_INDEX Index Name Table Name
642 DROP_TEMP_TABLE Table Name undef
643 DROP_TEMP_TRIGGER Trigger Name Table Name
644 DROP_TEMP_VIEW View Name undef
645 DROP_TRIGGER Trigger Name Table Name
646 DROP_VIEW View Name undef
647 INSERT Table Name undef
648 PRAGMA Pragma Name 1st arg or undef
649 READ Table Name Column Name
650 SELECT undef undef
651 TRANSACTION Operation undef
652 UPDATE Table Name Column Name
653 ATTACH Filename undef
654 DETACH Database Name undef
655 ALTER_TABLE Database Name Table Name
656 REINDEX Index Name undef
657 ANALYZE Table Name undef
658 CREATE_VTABLE Table Name Module Name
659 DROP_VTABLE Table Name Module Name
660 FUNCTION undef Function Name
661 SAVEPOINT Operation Savepoint Name
662
664 Definition
665 SQLite v3 provides the ability for users to supply arbitrary comparison
666 functions, known as user-defined "collation sequences" or "collating
667 functions", to be used for comparing two text values.
668 <http://www.sqlite.org/datatype3.html#collation> explains how
669 collations are used in various SQL expressions.
670 Builtin collation sequences
672 The following collation sequences are builtin within SQLite :
673 BINARY
675 Compares string data using memcmp(), regardless of text encoding.
676 NOCASE
678 The same as binary, except the 26 upper case characters of ASCII
679 are folded to their lower case equivalents before the comparison is
680 performed. Note that only ASCII characters are case folded. SQLite
681 does not attempt to do full UTF case folding due to the size of the
682 tables required.
683 RTRIM
685 The same as binary, except that trailing space characters are
686 ignored.
687 In addition, "DBD::SQLite" automatically installs the following
689 collation sequences :
690 perl
692 corresponds to the Perl "cmp" operator
693 perllocale
695 Perl "cmp" operator, in a context where "use locale" is activated.
696 Usage
698 You can write for example
699 CREATE TABLE foo(
701 txt1 COLLATE perl,
702 txt2 COLLATE perllocale,
703 txt3 COLLATE nocase
704 )
705 or
707 SELECT * FROM foo ORDER BY name COLLATE perllocale
709 Unicode handling
711 If the attribute "$dbh->{sqlite_unicode}" is set, strings coming from
712 the database and passed to the collation function will be properly
713 tagged with the utf8 flag; but this only works if the "sqlite_unicode"
714 attribute is set before the first call to a perl collation sequence .
715 The recommended way to activate unicode is to set the parameter at
716 connection time :
717 my $dbh = DBI->connect(
719 "dbi:SQLite:dbname=foo", "", "",
720 {
721 RaiseError => 1,
722 sqlite_unicode => 1,
723 }
724 );
725 Adding user-defined collations
727 The native SQLite API for adding user-defined collations is exposed
728 through methods "sqlite_create_collation" and
729 "sqlite_collation_needed".
730 To avoid calling these functions every time a $dbh handle is created,
732 "DBD::SQLite" offers a simpler interface through the
733 %DBD::SQLite::COLLATION hash : just insert your own collation functions
734 in that hash, and whenever an unknown collation name is encountered in
735 SQL, the appropriate collation function will be loaded on demand from
736 the hash. For example, here is a way to sort text values regardless of
737 their accented characters :
738 use DBD::SQLite;
740 $DBD::SQLite::COLLATION{no_accents} = sub {
741 my ( $a, $b ) = map lc, @_;
742 tr[aaaaeaaa~cd`eeeeeiiiien~ooooeo~ouuuuey]
743 [aaaaaacdeeeeiiiinoooooouuuuy] for $a, $b;
744 $a cmp $b;
745 };
746 my $dbh = DBI->connect("dbi:SQLite:dbname=dbfile");
747 my $sql = "SELECT ... FROM ... ORDER BY ... COLLATE no_accents");
748 my $rows = $dbh->selectall_arrayref($sql);
749 The builtin "perl" or "perllocale" collations are predefined in that
751 same hash.
752 The COLLATION hash is a global registry within the current process;
754 hence there is a risk of undesired side-effects. Therefore, to prevent
755 action at distance, the hash is implemented as a "write-only" hash,
756 that will happily accept new entries, but will raise an exception if
757 any attempt is made to override or delete a existing entry (including
758 the builtin "perl" and "perllocale").
759 If you really, really need to change or delete an entry, you can always
761 grab the tied object underneath %DBD::SQLite::COLLATION --- but don't
762 do that unless you really know what you are doing. Also observe that
763 changes in the global hash will not modify existing collations in
764 existing database handles: it will only affect new requests for
765 collations. In other words, if you want to change the behaviour of a
766 collation within an existing $dbh, you need to call the
767 "create_collation" method directly.
768
770 The following items remain to be done.
771 Warnings Upgrade
773 We currently use a horridly hacky method to issue and suppress
774 warnings. It suffices for now, but just barely.
775 Migrate all of the warning code to use the recommended DBI warnings.
777 Leak Detection
779 Implement one or more leak detection tests that only run during
780 AUTOMATED_TESTING and RELEASE_TESTING and validate that none of the C
781 code we work with leaks.
782 Stream API for Blobs
784 Reading/writing into blobs using "sqlite2_blob_open" /
785 "sqlite2_blob_close".
786 Flags for sqlite3_open_v2
788 Support the full API of sqlite3_open_v2 (flags for opening the file).
789
791 Bugs should be reported via the CPAN bug tracker at
792 <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
794 Note that bugs of bundled sqlite library (i.e. bugs in "sqlite3.[ch]")
796 should be reported to the sqlite developers at sqlite.org via their bug
797 tracker or via their mailing list.
798
800 Matt Sergeant <matt@sergeant.org>
801 Francis J. Lacoste <flacoste@logreport.org>
803 Wolfgang Sourdeau <wolfgang@logreport.org>
805 Adam Kennedy <adamk@cpan.org>
807 Max Maischein <corion@cpan.org>
809 Laurent Dami <dami@cpan.org>
811 Kenichi Ishigaki <ishigaki@cpan.org>
813
815 The bundled SQLite code in this distribution is Public Domain.
816 DBD::SQLite is copyright 2002 - 2007 Matt Sergeant.
818 Some parts copyright 2008 Francis J. Lacoste.
820 Some parts copyright 2008 Wolfgang Sourdeau.
822 Some parts copyright 2008 - 2009 Adam Kennedy.
824 Some parts derived from DBD::SQLite::Amalgamation copyright 2008 Audrey
826 Tang.
827 This program is free software; you can redistribute it and/or modify it
829 under the same terms as Perl itself.
830 The full text of the license can be found in the LICENSE file included
832 with this module.
833perl v5.10.1 2009-11-23 DBD::SQLite(3)