Provided by: liborlite-perl_1.98-1_all 
NAME
ORLite - Extremely light weight SQLite-specific ORM
SYNOPSIS
package Foo;
# Simplest possible usage
use strict;
use ORLite 'data/sqlite.db';
my @awesome = Foo::Person->select(
'where first_name = ?',
'Adam',
);
package Bar;
# All available options enabled or specified.
# Some options shown are mutually exclusive,
# this code would not actually run.
use ORLite {
package => 'My::ORM',
file => 'data/sqlite.db',
user_version => 12,
readonly => 1,
create => sub {
my $dbh = shift;
$dbh->do('CREATE TABLE foo ( bar TEXT NOT NULL )');
},
tables => [ 'table1', 'table2' ],
cleanup => 'VACUUM',
prune => 1,
};
DESCRIPTION
SQLite is a light single file SQL database that provides an excellent platform for
embedded storage of structured data.
However, while it is superficially similar to a regular server-side SQL database, SQLite
has some significant attributes that make using it like a traditional database difficult.
For example, SQLite is extremely fast to connect to compared to server databases (1000
connections per second is not unknown) and is particularly bad at concurrency, as it can
only lock transactions at a database-wide level.
This role as a superfast internal data store can clash with the roles and designs of
traditional object-relational modules like Class::DBI or DBIx::Class.
What this situation would seem to need is an object-relation system that is designed
specifically for SQLite and is aligned with its idiosyncracies.
ORLite is an object-relation system specifically tailored for SQLite that follows many of
the same principles as the ::Tiny series of modules and has a design and feature set that
aligns directly to the capabilities of SQLite.
Further documentation will be available at a later time, but the synopsis gives a pretty
good idea of how it works.
How ORLite Works
ORLite discovers the schema of a SQLite database, and then generates the code for a
complete set of classes that let you work with the objects stored in that database.
In the simplest form, your target root package "uses" ORLite, which will do the schema
discovery and code generation at compile-time.
When called, ORLite generates two types of packages.
Firstly, it builds database connectivity, transaction support, and other purely database
level functionality into your root namespace.
Secondly, it will create one sub-package underneath the namespace of the root module for
each table or view it finds in the database.
Once the basic table support has been generated, it will also try to load an "overlay"
module of the same name. Thus, by created a Foo::TableName module on disk containing
"extra" code, you can extend the original and add additional functionality to it.
OPTIONS
ORLite takes a set of options for the class construction at compile time as a HASH
parameter to the "use" line.
As a convenience, you can pass just the name of an existing SQLite file to load, and
ORLite will apply defaults to all other options.
# The following are equivalent
use ORLite $filename;
use ORLite {
file => $filename,
};
The behaviour of each of the options is as follows:
package
The optional "package" parameter is used to provide the Perl root namespace to generate
the code for. This class does not need to exist as a module on disk, nor does it need to
have anything loaded or in the namespace.
By default, the package used is the package that is calling ORLite's import method
(typically via the "use ORLite { ... }" line).
file
The compulsory "file" parameter (the only compulsory parameter) provides the path to the
SQLite file to use for the ORM class tree.
If the file already exists, it must be a valid SQLite file match that supported by the
version of DBD::SQLite that is installed on your system.
ORLite will throw an exception if the file does not exist, unless you also provide the
"create" option to signal that ORLite should create a new SQLite file on demand.
If the "create" option is provided, the path provided must be creatable. When creating
the database, ORLite will also create any missing directories as needed.
user_version
When working with ORLite, the biggest risk to the stability of your code is often the
reliability of the SQLite schema structure over time.
When the database schema changes the code generated by ORLite will also change. This can
easily result in an unexpected change in the API of your class tree, breaking the code
that sits on top of those generated APIs.
To resolve this, ORLite supports a feature called schema version-locking.
Via the "user_version" SQLite pragma, you can set a revision for your database schema,
increasing the number each time to make a non-trivial chance to your schema.
SQLite> PRAGMA user_version = 7
When creating your ORLite package, you should specificy this schema version number via the
"user_version" option.
use ORLite {
file => $filename,
user_version => 7,
};
When connecting to the SQLite database, the "user_version" you provide will be checked
against the version in the schema. If the versions do not match, then the schema has
unexpectedly changed, and the code that is generated by ORLite would be different to the
expected API.
Rather than risk potentially destructive errors caused by the changing code, ORLite will
simply refuse to run and throw an exception.
Thus, using the "user_version" feature allows you to write code against a SQLite database
with high-certainty that it will continue to work. Or at the very least, that should the
SQLite schema change in the future your code fill fail quickly and safely instead of
running away and causing unknown behaviour.
By default, the "user_version" option is false and the value of the SQLite "PRAGMA
user_version" will not be checked.
readonly
To conserve memory and reduce complexity, ORLite will generate the API differently based
on the writability of the SQLite database.
Features like transaction support and methods that result in "INSERT", "UPDATE" and
"DELETE" queries will only be added if they can actually be run, resulting in an immediate
"no such method" exception at the Perl level instead of letting the application do more
work only to hit an inevitable SQLite error.
By default, the "readonly" option is based on the filesystem permissions of the SQLite
database (which matches SQLite's own writability behaviour).
However the "readonly" option can be explicitly provided if you wish. Generally you would
do this if you are working with a read-write database, but you only plan to read from it.
Forcing "readonly" to true will halve the size of the code that is generated to produce
your ORM, reducing the size of any auto-generated API documentation using ORLite::Pod by a
similar amount.
It also ensures that this process will only take shared read locks on the database
(preventing the chance of creating a dead-lock on the SQLite database).
create
The "create" option is used to expand ORLite beyond just consuming other people's
databases to produce and operating on databases user the direct control of your code.
The "create" option supports two alternative forms.
If "create" is set to a simple true value, an empty SQLite file will be created if the
location provided in the "file" option does not exist.
If "create" is set to a "CODE" reference, this function will be executed on the new
database before ORLite attempts to scan the schema.
The "CODE" reference will be passed a plain DBI connection handle, which you should
operate on normally. Note that because "create" is fired before the code generation phase,
none of the functionality produced by the generated classes is available during the
execution of the "create" code.
The use of "create" option is incompatible with the "readonly" option.
tables
The "tables" option should be a reference to an array containing a list of table names.
For large or complex SQLite databases where you only need to make use of a fraction of the
schema limiting the set of tables will reduce both the startup time needed to scan the
structure of the SQLite schema, and reduce the memory cost of the class tree.
If the "tables" option is not provided, ORLite will attempt to produce a class for every
table in the main schema that is not prefixed with with "sqlite_".
cache
use ORLite {
file => 'dbi:SQLite:sqlite.db',
user_version => 2,
cache => 'cache/directory',
};
The "cache" option is used to reduce the time needed to scan the SQLite database table
structures and generate the code for them, by saving the generated code to a cache
directory and loading from that file instead of generating it each time from scratch.
cleanup
When working with embedded SQLite databases containing rapidly changing state data, it is
important for database performance and general health to make sure you VACUUM or ANALYZE
the database regularly.
The "cleanup" option should be a single literal SQL statement.
If provided, this statement will be automatically run on the database during "END"-time,
after the last transaction has been completed.
This will typically either by a full 'VACUUM ANALYZE' or the more simple 'VACUUM'.
prune
In some situation, such as during test scripts, an application will only need the created
SQLite database temporarily. In these situations, the "prune" option can be provided to
instruct ORLite to delete the SQLite database when the program ends.
If any directories were made in order to create the SQLite file, these directories will be
cleaned up and removed as well.
If "prune" is enabled, you should generally not use "cleanup" as any cleanup operation
will be made pointless when "prune" deletes the file.
By default, the "prune" option is set to false.
shim
In some situtations you may wish to make extensive changes to the behaviour of the classes
and methods generated by ORLite. Under normal circumstances all code is generated into the
table class directly, which can make overriding method difficult.
The "shim" option will make ORLite generate all of it's methods into a separate
"Foo::TableName::Shim" class, and leave the main table class "Foo::TableName" as a
transparent subclass of the shim.
This allows you to alter the behaviour of a table class without having to do nasty tricks
with symbol tables in order to alter or replace methods.
package My::Person;
# Write a log message when we create a new object
sub create {
my $class = shift;
my $self = SUPER::create(@_);
my $name = $self->name;
print LOG "Created new person '$name'\n";
return $self;
}
The "shim" option is global. It will alter the structure of all table classes at once.
However, unless you are making alterations to a class the impact of this different class
structure should be zero.
unicode
You can use this option to tell ORLite that your database uses unicode.
At the moment, it just enables the "sqlite_unicode" option while connecting to your
database. There'll be more in the future.
ROOT PACKAGE METHODS
All ORLite root packages receive an identical set of methods for controlling connections
to the database, transactions, and the issueing of queries of various types to the
database.
The example root package Foo::Bar is used in any examples.
All methods are static, ORLite does not allow the creation of a Foo::Bar object (although
you may wish to add this capability yourself).
dsn
my $string = Foo::Bar->dsn;
The "dsn" accessor returns the dbi connection string used to connect to the SQLite
database as a string.
dbh
my $handle = Foo::Bar->dbh;
To reliably prevent potential SQLite deadlocks resulting from multiple connections in a
single process, each ORLite package will only ever maintain a single connection to the
database.
During a transaction, this will be the same (cached) database handle.
Although in most situations you should not need a direct DBI connection handle, the "dbh"
method provides a method for getting a direct connection in a way that is compatible with
ORLite's connection management.
Please note that these connections should be short-lived, you should never hold onto a
connection beyond the immediate scope.
The transaction system in ORLite is specifically designed so that code using the database
should never have to know whether or not it is in a transation.
Because of this, you should never call the ->disconnect method on the database handles
yourself, as the handle may be that of a currently running transaction.
Further, you should do your own transaction management on a handle provided by the <dbh>
method.
In cases where there are extreme needs, and you absolutely have to violate these
connection handling rules, you should create your own completely manual DBI->connect call
to the database, using the connect string provided by the "dsn" method.
The "dbh" method returns a DBI::db object, or throws an exception on error.
connect
my $dbh = Foo::Bar->connect;
The "connect" method is provided for the (extremely rare) situation in which you need a
raw connection to the database, evading the normal tracking and management provided of the
ORM.
The use of raw connections in this manner is strongly discouraged, as you can create fatal
deadlocks in SQLite if either the core ORM or the raw connection uses a transaction at any
time.
To summarise, do not use this method unless you REALLY know what you are doing.
YOU HAVE BEEN WARNED!
connected
my $active = Foo::Bar->connected;
The "connected" method provides introspection of the connection status of the library. It
returns true if there is any connection or transaction open to the database, or false
otherwise.
begin
Foo::Bar->begin;
The "begin" method indicates the start of a transaction.
In the same way that ORLite allows only a single connection, likewise it allows only a
single application-wide transaction.
No indication is given as to whether you are currently in a transaction or not, all code
should be written neutrally so that it works either way or doesn't need to care.
Returns true or throws an exception on error.
While transaction support is always built for every ORLite-generated class tree, if the
database is opened "readonly" the "commit" method will not exist at all in the API, and
your only way of ending the transaction (and the resulting persistent connection) will be
"rollback".
commit
Foo::Bar->commit;
The "commit" method commits the current transaction. If called outside of a current
transaction, it is accepted and treated as a null operation.
Once the commit has been completed, the database connection falls back into auto-commit
state. If you wish to immediately start another transaction, you will need to issue a
separate ->begin call.
Returns true or throws an exception on error.
commit_begin
Foo::Bar->begin;
# Code for the first transaction...
Foo::Bar->commit_begin;
# Code for the last transaction...
Foo::Bar->commit;
By default, ORLite-generated code uses opportunistic connections.
Every <select> you call results in a fresh DBI "connect", and a "disconnect" occurs after
query processing and before the data is returned. Connections are only held open
indefinitely during a transaction, with an immediate "disconnect" after your "commit".
This makes ORLite very easy to use in an ad-hoc manner, but can have performance
implications.
While SQLite itself can handle 1000 connections per second, the repeated destruction and
repopulation of SQLite's data page caches between your statements (or between
transactions) can slow things down dramatically.
The "commit_begin" method is used to "commit" the current transaction and immediately
start a new transaction, without disconnecting from the database.
Its exception behaviour and return value is identical to that of a plain "commit" call.
rollback
The "rollback" method rolls back the current transaction. If called outside of a current
transaction, it is accepted and treated as a null operation.
Once the rollback has been completed, the database connection falls back into auto-commit
state. If you wish to immediately start another transaction, you will need to issue a
separate ->begin call.
If a transaction exists at END-time as the process exits, it will be automatically rolled
back.
Returns true or throws an exception on error.
rollback_begin
Foo::Bar->begin;
# Code for the first transaction...
Foo::Bar->rollback_begin;
# Code for the last transaction...
Foo::Bar->commit;
By default, ORLite-generated code uses opportunistic connections.
Every <select> you call results in a fresh DBI "connect", and a "disconnect" occurs after
query processing and before the data is returned. Connections are only held open
indefinitely during a transaction, with an immediate "disconnect" after your "commit".
This makes ORLite very easy to use in an ad-hoc manner, but can have performance
implications.
While SQLite itself can handle 1000 connections per second, the repeated destruction and
repopulation of SQLite's data page caches between your statements (or between
transactions) can slow things down dramatically.
The "rollback_begin" method is used to "rollback" the current transaction and immediately
start a new transaction, without disconnecting from the database.
Its exception behaviour and return value is identical to that of a plain "commit" call.
do
Foo::Bar->do(
'insert into table (foo, bar) values (?, ?)',
{},
$foo_value,
$bar_value,
);
The "do" method is a direct wrapper around the equivalent DBI method, but applied to the
appropriate locally-provided connection or transaction.
It takes the same parameters and has the same return values and error behaviour.
selectall_arrayref
The "selectall_arrayref" method is a direct wrapper around the equivalent DBI method, but
applied to the appropriate locally-provided connection or transaction.
It takes the same parameters and has the same return values and error behaviour.
selectall_hashref
The "selectall_hashref" method is a direct wrapper around the equivalent DBI method, but
applied to the appropriate locally-provided connection or transaction.
It takes the same parameters and has the same return values and error behaviour.
selectcol_arrayref
The "selectcol_arrayref" method is a direct wrapper around the equivalent DBI method, but
applied to the appropriate locally-provided connection or transaction.
It takes the same parameters and has the same return values and error behaviour.
selectrow_array
The "selectrow_array" method is a direct wrapper around the equivalent DBI method, but
applied to the appropriate locally-provided connection or transaction.
It takes the same parameters and has the same return values and error behaviour.
selectrow_arrayref
The "selectrow_arrayref" method is a direct wrapper around the equivalent DBI method, but
applied to the appropriate locally-provided connection or transaction.
It takes the same parameters and has the same return values and error behaviour.
selectrow_hashref
The "selectrow_hashref" method is a direct wrapper around the equivalent DBI method, but
applied to the appropriate locally-provided connection or transaction.
It takes the same parameters and has the same return values and error behaviour.
prepare
The "prepare" method is a direct wrapper around the equivalent DBI method, but applied to
the appropriate locally-provided connection or transaction
It takes the same parameters and has the same return values and error behaviour.
In general though, you should try to avoid the use of your own prepared statements if
possible, although this is only a recommendation and by no means prohibited.
pragma
# Get the user_version for the schema
my $version = Foo::Bar->pragma('user_version');
The "pragma" method provides a convenient method for fetching a pragma for a datase. See
the SQLite documentation for more details.
TABLE PACKAGE METHODS
When you use ORLite, your database tables will be available as objects named in a camel-
cased fashion. So, if your model name is Foo::Bar...
use ORLite {
package => 'Foo::Bar',
file => 'data/sqlite.db',
};
... then a table named 'user' would be accessed as "Foo::Bar::User", while a table named
'user_data' would become "Foo::Bar::UserData".
base
my $namespace = Foo::Bar::User->base; # Returns 'Foo::Bar'
Normally you will only need to work directly with a table class, and only with one ORLite
package.
However, if for some reason you need to work with multiple ORLite packages at the same
time without hardcoding the root namespace all the time, you can determine the root
namespace from an object or table class with the "base" method.
table
print Foo::Bar::UserData->table; # 'user_data'
While you should not need the name of table for any simple operations, from time to time
you may need it programatically. If you do need it, you can use the "table" method to get
the table name.
table_info
# List the columns in the underlying table
my $columns = Foo::Bar::User->table_info;
foreach my $c ( @$columns ) {
print "Column $c->{name} $c->{type}";
print " not null" if $c->{notnull};
print " default $c->{dflt_value}" if defined $c->{dflt_value};
print " primary key" if $c->{pk};
print "\n";
}
The "table_info" method is a wrapper around the SQLite "table_info" pragma, and provides
simplified access to the column metadata for the underlying table should you need it for
some advanced function that needs direct access to the column list.
Returns a reference to an "ARRAY" containing a list of columns, where each column is a
reference to a "HASH" with the keys "cid", "dflt_value", "name", "notnull", "pk" and
"type".
new
my $user = Foo::Bar::User->new(
name => 'Your Name',
age => 23,
);
The "new" constructor creates an anonymous object, without reading or writing it to the
database. It also won't do validation of any kind, since ORLite is designed for use with
embedded databases and presumes that you know what you are doing.
insert
my $user = Foo::Bar::User->new(
name => 'Your Name',
age => 23,
)->insert;
The "insert" method takes an existing anonymous object and inserts it into the database,
returning the object back as a convenience.
It provides the second half of the slower manual two-phase object construction process.
If the table has an auto-incrementing primary key (and you have not provided a value for
it yourself) the identifier for the new record will be fetched back from the database and
set in your object.
my $object = Foo::Bar::User->new( name => 'Foo' )->insert;
print "Created new user with id " . $user->id . "\n";
create
my $user = Foo::Bar::User->create(
name => 'Your Name',
age => 23,
);
While the "new" + "insert" methods are useful when you need to do interesting constructor
mechanisms, for most situations you already have all the attributes ready and just want to
create and insert the record in a single step.
The "create" method provides this shorthand mechanism and is just the functional
equivalent of the following.
sub create {
shift->new(@_)->insert;
}
It returns the newly created object after it has been inserted.
load
my $user = Foo::Bar::User->load( $id );
If your table has single column primary key, a "load" method will be generated in the
class. If there is no primary key, the method is not created.
The "load" method provides a shortcut mechanism for fetching a single object based on the
value of the primary key. However it should only be used for cases where your code trusts
the record to already exists.
It returns a "Foo::Bar::User" object, or throws an exception if the object does not exist.
id
The "id" accessor is a convenience method that is added to your table class to increase
the readability of your code when ORLite detects certain patterns of column naming.
For example, take the following definition where convention is that all primary keys are
the table name followed by "_id".
create table foo_bar (
foo_bar_id integer not null primary key,
name string not null,
)
When ORLite detects the use of this pattern, and as long as the table does not have an
"id" column, the additional "id" accessor will be added to your class, making these
expressions equivalent both in function and performance.
my $foo_bar = My::FooBar->create( name => 'Hello' );
# Column name accessor
$foo_bar->foo_bar_id;
# Convenience id accessor
$foo_bar->id;
As you can see, the latter involves much less repetition and reads much more cleanly.
select
my @users = Foo::Bar::User->select;
my $users = Foo::Bar::User->select( 'where name = ?', @args );
The "select" method is used to retrieve objects from the database.
In list context, returns an array with all matching elements. In scalar context an array
reference is returned with that same data.
You can filter the results or order them by passing SQL code to the method.
my @users = DB::User->select( 'where name = ?', $name );
my $users = DB::User->select( 'order by name' );
Because "select" provides only the thinnest of layers around pure SQL (it merely generates
the "SELECT ... FROM table_name") you are free to use anything you wish in your query,
including subselects and function calls.
If called without any arguments, it will return all rows of the table in the natural sort
order of SQLite.
iterate
Foo::Bar::User->iterate( sub {
print $_->name . "\n";
} );
The "iterate" method enables the processing of large tables one record at a time without
loading having to them all into memory in advance.
This plays well to the strength of SQLite, allowing it to do the work of loading
arbitrarily large stream of records from disk while retaining the full power of Perl when
processing the records.
The last argument to "iterate" must be a subroutine reference that will be called for each
element in the list, with the object provided in the topic variable $_.
This makes the "iterate" code fragment above functionally equivalent to the following,
except with an O(1) memory cost instead of O(n).
foreach ( Foo::Bar::User->select ) {
print $_->name . "\n";
}
You can filter the list via SQL in the same way you can with "select".
Foo::Bar::User->iterate(
'order by ?', 'name',
sub {
print $_->name . "\n";
}
);
You can also use it in raw form from the root namespace for better control. Using this
form also allows for the use of arbitrarily complex queries, including joins. Instead of
being objects, rows are provided as ARRAY references when used in this form.
Foo::Bar->iterate(
'select name from user order by name',
sub {
print $_->[0] . "\n";
}
);
count
my $everyone = Foo::Bar::User->count;
my $young = Foo::Bar::User->count( 'where age <= ?', 13 );
You can count the total number of elements in a table by calling the "count" method with
no arguments. You can also narrow your count by passing sql conditions to the method in
the same manner as with the "select" method.
delete
# Delete a single object from the database
$user->delete;
# Delete a range of rows from the database
Foo::Bar::User->delete( 'where age <= ?', 13 );
The "delete" method will delete the single row representing an object, based on the
primary key or SQLite rowid of that object.
The object that you delete will be left intact and untouched, and you remain free to do
with it whatever you wish.
delete_where
# Delete a range of rows from the database
Foo::Bar::User->delete( 'age <= ?', 13 );
The "delete_where" static method allows the delete of large numbers of rows from a
database while protecting against accidentally doing a boundless delete (the "truncate"
method is provided specifically for this purpose).
It takes the same parameters for deleting as the "select" method, with the exception that
the "where" keyword is automatically provided for your and should not be passed in.
This ensures that providing an empty of null condition results in an invalid SQL query and
the deletion will not occur.
Returns the number of rows deleted from the database (which may be zero).
truncate
# Clear out all records from the table
Foo::Bar::User->truncate;
The "truncate" method takes no parameters and is used for only one purpose, to completely
empty a table of all rows.
Having a separate method from "delete" not only prevents accidents, but will also do the
deletion via the direct SQLite "TRUNCATE TABLE" query. This uses a different deletion
mechanism, and is significantly faster than a plain SQL "DELETE".
TO DO
- Support for intuiting reverse relations from foreign keys
- Document the 'create' and 'table' params
SUPPORT
Bugs should be reported via the CPAN bug tracker at
<http://rt.cpan.org/NoAuth/ReportBug.html?Queue=ORLite>
For other issues, contact the author.
AUTHOR
Adam Kennedy <adamk@cpan.org>
SEE ALSO
ORLite::Mirror, ORLite::Migrate, ORLite::Pod
COPYRIGHT
Copyright 2008 - 2012 Adam Kennedy.
This program is free software; you can redistribute it and/or modify it under the same
terms as Perl itself.
The full text of the license can be found in the LICENSE file included with this module.