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
   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.

 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 <[email protected]>

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.

You are viewing proxied material from ftp.icm.edu.pl. The copyright of proxied material belongs to its original authors. Any comments or complaints in relation to proxied material should be directed to the original authors of the content concerned. Please see the disclaimer for more details.