1ORLite(3) User Contributed Perl Documentation ORLite(3)
2
6 ORLite - Extremely light weight SQLite-specific ORM
7
9 package Foo;
10 # Simplest possible usage
12 use strict;
14 use ORLite 'data/sqlite.db';
15 my @awesome = Foo::Person->select(
17 'where first_name = ?',
18 'Adam',
19 );
20 package Bar;
22 # All available options enabled or specified.
24 # Some options shown are mutually exclusive,
25 # this code would not actually run.
26 use ORLite {
28 package => 'My::ORM',
29 file => 'data/sqlite.db',
30 user_version => 12,
31 readonly => 1,
32 create => sub {
33 my $dbh = shift;
34 $dbh->do('CREATE TABLE foo ( bar TEXT NOT NULL )');
35 },
36 tables => [ 'table1', 'table2' ],
37 cleanup => 'VACUUM',
38 prune => 1,
39 );
40
42 SQLite is a light single file SQL database that provides an excellent
43 platform for embedded storage of structured data.
44 However, while it is superficially similar to a regular server-side SQL
46 database, SQLite has some significant attributes that make using it
47 like a traditional database difficult.
48 For example, SQLite is extremely fast to connect to compared to server
50 databases (1000 connections per second is not unknown) and is
51 particularly bad at concurrency, as it can only lock transactions at a
52 database-wide level.
53 This role as a superfast internal data store can clash with the roles
55 and designs of traditional object-relational modules like Class::DBI or
56 DBIx::Class.
57 What this situation would seem to need is an object-relation system
59 that is designed specifically for SQLite and is aligned with its
60 idiosyncracies.
61 ORLite is an object-relation system specifically tailored for SQLite
63 that follows many of the same principles as the ::Tiny series of
64 modules and has a design and feature set that aligns directly to the
65 capabilities of SQLite.
66 Further documentation will be available at a later time, but the
68 synopsis gives a pretty good idea of how it works.
69 How ORLite Works
71 In short, ORLite discovers the schema of a SQLite database, and then
72 uses code generation to build a set of packages for talking to that
73 database.
74 In the simplest form, your target root package "uses" ORLite, which
76 will do the schema discovery and code generation at compile-time.
77 When called, ORLite generates two types of package.
79 Firstly, it builds database connectivity, transaction support, and
81 other purely database level functionality into your root namespace.
82 Then it will create one sub-package underneath the root package for
84 each table contained in the database.
85
87 ORLite takes a set of options for the class construction at compile
88 time as a HASH parameter to the "use" line.
89 As a convenience, you can pass just the name of an existing SQLite file
91 to load, and ORLite will apply defaults to all other options.
92 # The following are equivalent
94 use ORLite $filename;
96 use ORLite {
98 file => $filename,
99 };
100 The behaviour of each of the options is as follows:
102 package
104 The optional "package" parameter is used to provide the Perl root
105 namespace to generate the code for. This class does not need to exist
106 as a module on disk, nor does it need to have anything loaded or in the
107 namespace.
108 By default, the package used is the package that is calling ORLite's
110 import method (typically via the "use ORLite { ... }" line).
111 file
113 The compulsory "file" parameter (the only compulsory parameter)
114 provides the path to the SQLite file to use for the ORM class tree.
115 If the file already exists, it must be a valid SQLite file match that
117 supported by the version of DBD::SQLite that is installed on your
118 system.
119 ORLite will throw an exception if the file does not exist, unless you
121 also provide the "create" option to signal that ORLite should create a
122 new SQLite file on demand.
123 If the "create" option is provided, the path provided must be
125 creatable. When creating the database, ORLite will also create any
126 missing directories as needed.
127 user_version
129 When working with ORLite, the biggest risk to the stability of your
130 code is often the reliability of the SQLite schema structure over time.
131 When the database schema changes the code generated by ORLite will also
133 change. This can easily result in an unexpected change in the API of
134 your class tree, breaking the code that sits on top of those generated
135 APIs.
136 To resolve this, ORLite supports a feature called schema version-
138 locking.
139 Via the "user_version" SQLite pragma, you can set a revision for your
141 database schema, increasing the number each time to make a non-trivial
142 chance to your schema.
143 SQLite> PRAGMA user_version = 7
145 When creating your ORLite package, you should specificy this schema
147 version number via the "user_version" option.
148 use ORLite {
150 file => $filename,
151 user_version => 7,
152 };
153 When connecting to the SQLite database, the "user_version" you provide
155 will be checked against the version in the schema. If the versions do
156 not match, then the schema has unexpectedly changed, and the code that
157 is generated by ORLite would be different to the expected API.
158 Rather than risk potentially destructive errors caused by the changing
160 code, ORLite will simply refuse to run and throw an exception.
161 Thus, using the "user_version" feature allows you to write code against
163 a SQLite database with high-certainty that it will continue to work. Or
164 at the very least, that should the SQLite schema change in the future
165 your code fill fail quickly and safely instead of running away and
166 causing unknown behaviour.
167 By default, the "user_version" option is false and the value of the
169 SQLite "PRAGMA user_version" will not be checked.
170 readonly
172 To conserve memory and reduce complexity, ORLite will generate the API
173 differently based on the writability of the SQLite database.
174 Features like transaction support and methods that result in "INSERT",
176 "UPDATE" and "DELETE" queries will only be added if they can actually
177 be run, resulting in an immediate "no such method" exception at the
178 Perl level instead of letting the application do more work only to hit
179 an inevitable SQLite error.
180 By default, the "reaodnly" option is based on the filesystem
182 permissions of the SQLite database (which matches SQLite's own
183 writability behaviour).
184 However the "readonly" option can be explicitly provided if you wish.
186 Generally you would do this if you are working with a read-write
187 database, but you only plan to read from it.
188 Forcing "readonly" to true will halve the size of the code that is
190 generated to produce your ORM, reducing the size of any auto-generated
191 API documentation using ORLite::Pod by a similar amount.
192 It also ensures that this process will only take shared read locks on
194 the database (preventing the chance of creating a dead-lock on the
195 SQLite database).
196 create
198 The "create" option is used to expand ORLite beyond just consuming
199 other people's databases to produce and operating on databases user the
200 direct control of your code.
201 The "create" option supports two alternative forms.
203 If "create" is set to a simple true value, an empty SQLite file will be
205 created if the location provided in the "file" option does not exist.
206 If "create" is set to a "CODE" reference, this function will be
208 executed on the new database before ORLite attempts to scan the schema.
209 The "CODE" reference will be passed a plain DBI connection handle,
211 which you should operate on normally. Note that because "create" is
212 fired before the code generation phase, none of the functionality
213 produced by the generated classes is available during the execution of
214 the "create" code.
215 The use of "create" option is incompatible with the "readonly" option.
217 tables
219 The "tables" option should be a reference to an array containing a list
220 of table names. For large or complex SQLite databases where you only
221 need to make use of a fraction of the schema limiting the set of tables
222 will reduce both the startup time needed to scan the structure of the
223 SQLite schema, and reduce the memory cost of the class tree.
224 If the "tables" option is not provided, ORLite will attempt to produce
226 a class for every table in the main schema that is not prefixed with
227 with "sqlite_".
228 cleanup
230 When working with embedded SQLite database containing rapidly changing
231 state data, it is important for database performance and general health
232 to make sure you VACUUM or ANALYZE the database regularly.
233 The "cleanup" option should be a single literal SQL statement.
235 If provided, this statement will be automatically run on the database
237 during "END"-time, after the last transaction has been completed.
238 This will typically either by a full 'VACUUM ANALYZE' or the more
240 simple 'VACUUM'.
241 prune
243 In some situation, such as during test scripts, an application will
244 only need the created SQLite database temporarily. In these situations,
245 the "prune" option can be provided to instruct ORLite to delete the
246 SQLite database when the program ends.
247 If any directories were made in order to create the SQLite file, these
249 directories will be cleaned up and removed as well.
250 If "prune" is enabled, you should generally not use "cleanup" as any
252 cleanup operation will be made pointless when "prune" deletes the file.
253 By default, the "prune" option is set to false.
255
257 All ORLite root packages receive an identical set of methods for
258 controlling connections to the database, transactions, and the issueing
259 of queries of various types to the database.
260 The example root package Foo::Bar is used in any examples.
262 All methods are static, ORLite does not allow the creation of a
264 Foo::Bar object (although you may wish to add this capability
265 yourself).
266 dsn
268 my $string = Foo::Bar->dsn;
269 The "dsn" accessor returns the dbi connection string used to connect to
271 the SQLite database as a string.
272 dbh
274 my $handle = Foo::Bar->dbh;
275 To reliably prevent potential SQLite deadlocks resulting from multiple
277 connections in a single process, each ORLite package will only ever
278 maintain a single connection to the database.
279 During a transaction, this will be the same (cached) database handle.
281 Although in most situations you should not need a direct DBI connection
283 handle, the "dbh" method provides a method for getting a direct
284 connection in a way that is compatible with ORLite's connection
285 management.
286 Please note that these connections should be short-lived, you should
288 never hold onto a connection beyond the immediate scope.
289 The transaction system in ORLite is specifically designed so that code
291 using the database should never have to know whether or not it is in a
292 transation.
293 Because of this, you should never call the ->disconnect method on the
295 database handles yourself, as the handle may be that of a currently
296 running transaction.
297 Further, you should do your own transaction management on a handle
299 provided by the <dbh> method.
300 In cases where there are extreme needs, and you absolutely have to
302 violate these connection handling rules, you should create your own
303 completely manual DBI->connect call to the database, using the connect
304 string provided by the "dsn" method.
305 The "dbh" method returns a DBI::db object, or throws an exception on
307 error.
308 connect
310 my $dbh = Foo::Bar->connect;
311 The "connect" method is provided for the (extremely rare) situation in
313 which you need a raw connection to the database, evading the normal
314 tracking and management provided of the ORM.
315 The use of raw connections in this manner is strongly discouraged, as
317 you can create fatal deadlocks in SQLite if either the core ORM or the
318 raw connection uses a transaction at any time.
319 To summarise, do not use this method unless you REALLY know what you
321 are doing.
322 YOU HAVE BEEN WARNED!
324 connected
326 my $active = Foo::Bar->connected;
327 The "connected" method provides introspection of the connection status
329 of the library. It returns true if there is any connection or
330 transaction open to the database, or false otherwise.
331 begin
333 Foo::Bar->begin;
334 The "begin" method indicates the start of a transaction.
336 In the same way that ORLite allows only a single connection, likewise
338 it allows only a single application-wide transaction.
339 No indication is given as to whether you are currently in a transaction
341 or not, all code should be written neutrally so that it works either
342 way or doesn't need to care.
343 Returns true or throws an exception on error.
345 While transaction support is always built for every ORLite-generated
347 class tree, if the database is opened "readonly" the "commit" method
348 will not exist at all in the API, and your only way of ending the
349 transaction (and the resulting persistant connection) will be
350 "rollback".
351 commit
353 Foo::Bar->commit;
354 The "commit" method commits the current transaction. If called outside
356 of a current transaction, it is accepted and treated as a null
357 operation.
358 Once the commit has been completed, the database connection falls back
360 into auto-commit state. If you wish to immediately start another
361 transaction, you will need to issue a separate ->begin call.
362 Returns true or throws an exception on error.
364 commit_begin
366 Foo::Bar->begin;
367 # Code for the first transaction...
369 Foo::Bar->commit_begin;
371 # Code for the last transaction...
373 Foo::Bar->commit;
375 By default, ORLite-generated code uses opportunistic connections.
377 Every <select> you call results in a fresh DBI "connect", and a
379 "disconnect" occurs after query processing and before the data is
380 returned. Connections are only held open indefinitely during a
381 transaction, with an immediate "disconnect" after your "commit".
382 This makes ORLite very easy to use in an ad-hoc manner, but can have
384 performance implications.
385 While SQLite itself can handle 1000 connections per second, the
387 repeated destruction and repopulation of SQLite's data page caches
388 between your statements (or between transactions) can slow things down
389 dramatically.
390 The "commit_begin" method is used to "commit" the current transaction
392 and immediately start a new transaction, without disconnecting from the
393 database.
394 Its exception behaviour and return value is identical to that of a
396 plain "commit" call.
397 rollback
399 The "rollback" method rolls back the current transaction. If called
400 outside of a current transaction, it is accepted and treated as a null
401 operation.
402 Once the rollback has been completed, the database connection falls
404 back into auto-commit state. If you wish to immediately start another
405 transaction, you will need to issue a separate ->begin call.
406 If a transaction exists at END-time as the process exits, it will be
408 automatically rolled back.
409 Returns true or throws an exception on error.
411 rollback_begin
413 Foo::Bar->begin;
414 # Code for the first transaction...
416 Foo::Bar->rollback_begin;
418 # Code for the last transaction...
420 Foo::Bar->commit;
422 By default, ORLite-generated code uses opportunistic connections.
424 Every <select> you call results in a fresh DBI "connect", and a
426 "disconnect" occurs after query processing and before the data is
427 returned. Connections are only held open indefinitely during a
428 transaction, with an immediate "disconnect" after your "commit".
429 This makes ORLite very easy to use in an ad-hoc manner, but can have
431 performance implications.
432 While SQLite itself can handle 1000 connections per second, the
434 repeated destruction and repopulation of SQLite's data page caches
435 between your statements (or between transactions) can slow things down
436 dramatically.
437 The "rollback_begin" method is used to "rollback" the current
439 transaction and immediately start a new transaction, without
440 disconnecting from the database.
441 Its exception behaviour and return value is identical to that of a
443 plain "commit" call.
444 do
446 Foo::Bar->do(
447 'insert into table (foo, bar) values (?, ?)',
448 {},
449 $foo_value,
450 $bar_value,
451 );
452 The "do" method is a direct wrapper around the equivalent DBI method,
454 but applied to the appropriate locally-provided connection or
455 transaction.
456 It takes the same parameters and has the same return values and error
458 behaviour.
459 selectall_arrayref
461 The "selectall_arrayref" method is a direct wrapper around the
462 equivalent DBI method, but applied to the appropriate locally-provided
463 connection or transaction.
464 It takes the same parameters and has the same return values and error
466 behaviour.
467 selectall_hashref
469 The "selectall_hashref" method is a direct wrapper around the
470 equivalent DBI method, but applied to the appropriate locally-provided
471 connection or transaction.
472 It takes the same parameters and has the same return values and error
474 behaviour.
475 selectcol_arrayref
477 The "selectcol_arrayref" method is a direct wrapper around the
478 equivalent DBI method, but applied to the appropriate locally-provided
479 connection or transaction.
480 It takes the same parameters and has the same return values and error
482 behaviour.
483 selectrow_array
485 The "selectrow_array" method is a direct wrapper around the equivalent
486 DBI method, but applied to the appropriate locally-provided connection
487 or transaction.
488 It takes the same parameters and has the same return values and error
490 behaviour.
491 selectrow_arrayref
493 The "selectrow_arrayref" method is a direct wrapper around the
494 equivalent DBI method, but applied to the appropriate locally-provided
495 connection or transaction.
496 It takes the same parameters and has the same return values and error
498 behaviour.
499 selectrow_hashref
501 The "selectrow_hashref" method is a direct wrapper around the
502 equivalent DBI method, but applied to the appropriate locally-provided
503 connection or transaction.
504 It takes the same parameters and has the same return values and error
506 behaviour.
507 prepare
509 The "prepare" method is a direct wrapper around the equivalent DBI
510 method, but applied to the appropriate locally-provided connection or
511 transaction
512 It takes the same parameters and has the same return values and error
514 behaviour.
515 In general though, you should try to avoid the use of your own prepared
517 statements if possible, although this is only a recommendation and by
518 no means prohibited.
519 pragma
521 # Get the user_version for the schema
522 my $version = Foo::Bar->pragma('user_version');
523 The "pragma" method provides a convenient method for fetching a pragma
525 for a datase. See the SQLite documentation for more details.
526
528 When you use ORLite, your database tables will be available as objects
529 named in a camel-cased fashion. So, if your model name is Foo::Bar...
530 use ORLite {
532 package => 'Foo::Bar',
533 file => 'data/sqlite.db',
534 };
535 ... then a table named 'user' would be accessed as "Foo::Bar::User",
537 while a table named 'user_data' would become "Foo::Bar::UserData".
538 base
540 my $namespace = Foo::Bar::User->base; # Returns 'Foo::Bar'
541 Normally you will only need to work directly with a table class, and
543 only with one ORLite package.
544 However, if for some reason you need to work with multiple ORLite
546 packages at the same time without hardcoding the root namespace all the
547 time, you can determine the root namespace from an object or table
548 class with the "base" method.
549 table
551 print Foo::Bar::UserData->table; # 'user_data'
552 While you should not need the name of table for any simple operations,
554 from time to time you may need it programatically. If you do need it,
555 you can use the "table" method to get the table name.
556 new
558 my $user = Foo::Bar::User->new(
559 name => 'Your Name',
560 age => 23,
561 );
562 The "new" constructor creates an anonymous object, without reading it
564 from or writing it to the database. It also won't do validation of any
565 kind, since ORLite is designed for use with embedded databases and
566 presumes that you know what you are doing.
567 insert
569 my $user = Foo::Bar::User->new(
570 name => 'Your Name',
571 age => 23,
572 )->insert;
573 The "insert" method takes an existing anonymous object and inserts it
575 into the database, returning the object as a convenience.
576 It provides the second half of the slower manual two-phase object
578 construction process.
579 If the table has an auto-incrementing primary key (and you have not
581 provided a value for it yourself) the identifier for the new record
582 will be fetched back from the database and set in your object.
583 my $object = Foo::Bar::User->new( name => 'Foo' )->insert;
585 print "Created new user with id " . $user->id . "\n";
587 create
589 my $user = Foo::Bar::User->create(
590 name => 'Your Name',
591 age => 23,
592 );
593 While the "new" + "insert" methods are useful when you need to do
595 interesting constructor mechanisms, for most situations you already
596 have all the attributes ready and just want to create and insert the
597 record in a single step.
598 The "create" method provides this shorthand mechanism and is just the
600 functional equivalent of the following.
601 sub create {
603 shift->new(@_)->insert;
604 }
605 It returns the newly created object after it has been inserted.
607 load
609 my $user = Foo::Bar::User->load( $id );
610 If your table has single column primary key, a "load" method will be
612 generated in the class. If there is no primary key, the method is not
613 created.
614 The "load" method provides a shortcut mechanism for fetching a single
616 object based on the value of the primary key. However it should only be
617 used for cases where your code trusts the record to already exists.
618 It returns a "Foo::Bar::User" object, or throws an exception if the
620 object does not exist.
621 select
623 my @users = Foo::Bar::User->select;
624 my $users = Foo::Bar::User->select( 'where name = ?', @args );
626 The "select" method is used to retrieve objects from the database.
628 In list context, returns an array with all matching elements. In
630 scalar context an array reference is returned with that same data.
631 You can filter the results or order them by passing SQL code to the
633 method.
634 my @users = DB::User->select( 'where name = ?', $name );
636 my $users = DB::User->select( 'order by name' );
638 Because "select" provides only the thinnest of layers around pure SQL
640 (it merely generates the "SELECT ... FROM table_name") you are free to
641 use anything you wish in your query, including subselects and function
642 calls.
643 If called without any arguments, it will return all rows of the table
645 in the natural sort order of SQLite.
646 iterate
648 Foo::Bar::User->iterate( sub {
649 print $_->name . "\n";
650 } );
651 The "iterate" method enables the processing of large tables one record
653 at a time without loading having to them all into memory in advance.
654 This plays well to the strength of SQLite, allowing it to do the work
656 of loading arbitrarily large stream of records from disk while
657 retaining the full power of Perl when processing the records.
658 The last argument to "iterate" must be a subroutine reference that will
660 be called for each element in the list, with the object provided in the
661 topic variable $_.
662 This makes the "iterate" code fragment above functionally equivalent to
664 the following, except with an O(1) memory cost instead of O(n).
665 foreach ( Foo::Bar::User->select ) {
667 print $_->name . "\n";
668 }
669 You can filter the list via SQL in the same way you can with "select".
671 Foo::Bar::User->iterate(
673 'order by ?', 'name',
674 sub {
675 print $_->name . "\n";
676 }
677 );
678 You can also use it in raw form from the root namespace for better
680 control. Using this form also allows for the use of arbitrarily
681 complex queries, including joins. Instead of being objects, rows are
682 provided as ARRAY references when used in this form.
683 Foo::Bar->iterate(
685 'select name from user order by name',
686 sub {
687 print $_->[0] . "\n";
688 }
689 );
690 count
692 my $everyone = Foo::Bar::User->count;
693 my $young = Foo::Bar::User->count( 'where age <= ?', 13 );
695 You can count the total number of elements in a table by calling the
697 "count" method with no arguments. You can also narrow your count by
698 passing sql conditions to the method in the same manner as with the
699 "select" method.
700 delete
702 # Delete a single object from the database
703 $user->delete;
704 # Delete a range of rows from the database
706 Foo::Bar::User->delete( 'where age <= ?', 13 );
707 The "delete" method comes in two variations, depending on whether you
709 call it on a single object or an entire class.
710 When called as an instance methods "delete" will delete the single row
712 representing that object, based on the primary key of that object.
713 The object that you delete will be left intact and untouched, and you
715 remain free to do with it whatever you wish.
716 When called as a static method on the class name "delete" allows the
718 deletion of a range of zero or more records from that table.
719 It takes the same parameters for deleting as the "select" method, with
721 the exception that you MUST provide a condition parameter and can NOT
722 use a conditionless "delete" call to clear out the entire table. This
723 is done to limit accidental deletion.
724 truncate
726 # Clear out all records from the table
727 Foo::Bar::User->truncate;
728 The "truncate" method takes no parameters and is used for only one
730 purpose, to completely empty a table of all rows.
731 Having a separate method from "delete" not only prevents accidents, but
733 will also do the deletion via the direct SQLite "TRUNCATE TABLE" query.
734 This uses a different deletion mechanism, and is significantly faster
735 than a plain SQL "DELETE".
736
738 - Support for intuiting reverse relations from foreign keys
739 - Document the 'create' and 'table' params
741
743 Bugs should be reported via the CPAN bug tracker at
744 <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=ORLite>
746 For other issues, contact the author.
748
750 Adam Kennedy <adamk@cpan.org>
751
753 ORLite::Mirror, ORLite::Migrate
754
756 Copyright 2008 - 2010 Adam Kennedy.
757 This program is free software; you can redistribute it and/or modify it
759 under the same terms as Perl itself.
760 The full text of the license can be found in the LICENSE file included
762 with this module.
763perl v5.12.0 2010-03-16 ORLite(3)