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

In short, ORLite discovers the schema of a SQLite database, and then uses code generation to build a set of packages for talking to 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 reaodnly 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 seperate 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.

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.

This makes ORLite very easy to use in an ad-hoc manner, but can have performance implications.

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.