Skip to content


Subversion checkout URL

You can clone with
Download ZIP
tree: 90f360c8a9
Fetching contributors…

Cannot retrieve contributors at this time

474 lines (342 sloc) 15.691 kb
ORMesque - Lightweight To-The-Point ORM
version 1.103190
my $db = ORMesque->new('dbi:mysql:foo', 'root');
my $ta = $db->table_a
->page(1, 25)
->read({ column => 'value' });
my $tb = $db->table_b
->page(1, 25)
->read({ column => 'value' });
return to_json $ta->join($tb);
ORMesque is a lightweight ORM for Dancer supporting any database listed
under ORMesque::SchemaLoader making it a great alternative when you
don't have the time, need or desire to learn DBIx::Class. ORMesque is an
object relational mapper for Dancer that provides a database connection
to the database of your choice and automatically creates objects and
accessors for that database and its tables and columns. ORMesque uses
SQL::Abstract querying syntax. More usage examples...
my $db = ORMesque->new($dsn);
my $user = $db->users;
# Grab the first record, not neccessary if operating on only one record
# SQL::Abstract where clause passed to the "read" method
'column' => 'query'
# How many records in collection
for (0..$user->count) {
print $user->column;
$user->column('new stuff');
$user->update($user->current, $user->id);
# The database objects main accessors are CRUD (create, read, update, and delete)
# Also, need direct access to the resultset?
$user->collection; # returns an array of hashrefs
$user->current; # return a hashref of the current row in the collection
The dbi method/keyword instantiates a new ORMesque instance
which uses the datasource configuration details in your configuration file
to create database objects and accessors.
my $db = ORMesque->new(...);
Once the dbi() keyword analyzes the specified database, the schema is cached
to for speed and performance. Occassionally you may want to re-read the
database schema.
my $db = ORMesque->new(...);
The next method instructs the database object to continue to the next
row if it exists.
while ($db->table->next) {
The first method instructs the database object to continue to return the first
row in the resultset.
The last method instructs the database object to continue to return the last
row in the resultset.
The collection method return the raw resultset object.
The current method return the raw row resultset object of the position in
the resultset collection.
The clear method empties all resultset containers. This method should be used
when your ready to perform another operation (start over) without initializing
a new object.
The key method finds the database objects primary key if its defined.
The select method defines specific columns to be used in the generated
SQL query. This useful for database tables that have lots of columns
where only a few are actually needed.
my $table = $db->select('foo', 'bar')->read();
The return method queries the database for the last created object(s).
It is important to note that while return() can be used in most cases
like the last_insert_id() to fetch the recently last created entry,
function, you should not use it that way unless you know exactly what
this method does and what your database will return.
my $new_record = $db->table->create(...)->return();
The count method returns the number of items in the resultset of the
object it's called on. Note! If you make changes to the database, you
will need to call read() before calling count() to get an accurate
count as count() operates on the current collection.
my $count = $db->table->read->count;
Caveat 1: The create method will remove the primary key if the column
is marked as auto-incremented ...
The create method creates a new entry in the datastore.
takes 1 arg: hashref (SQL::Abstract fields parameter)
'column_a' => 'value_a',
# create a copy of an existing record
my $user = $db->users;
$user->full_name_column('Copy of ' . $user->full_name);
# get newly created record
print $user->id; # new record id
print $user->full_name;
The read method fetches records from the datastore.
Takes 2 arg.
arg 1: hashref (SQL::Abstract where parameter) or scalar
arg 2: arrayref (SQL::Abstract order parameter) - optional
'column_a' => 'value_a',
.. or read by primary key ..
.. or read and limit the resultset ..
$db->table->read({ 'column_a' => 'value_a' }, ['orderby_column_a'], $limit, $offset);
.. or return a paged resultset ..
$db->table->page(1, 25)->read;
The update method alters an existing record in the datastore.
Takes 2 arg.
arg 1: hashref (SQL::Abstract fields parameter)
arg 2: arrayref (SQL::Abstract where parameter) or scalar - optional
'column_a' => 'value_a',
'where_column_a' => '...'
'column_a' => 'value_a',
}, 1);
The delete method is prohibited from deleting an entire database table and
thus requires a where clause. If you intentionally desire to empty the entire
database then you may use the delete_all method.
'column_a' => 'value_a',
The delete_all method is use to intentionally empty the entire database table.
If you have used ORMesque with a project of any sophistication you will
have undoubtedly noticed that the is no mechanism for specifying joins
and this is intentional. ORMesque is an ORM, and object relational
mapper and that is its purpose, it is not a SQL substitute. Joins are
neccessary in SQL as they are the only means of gathering related data.
Such is not the case with Perl code, however, even in code the need to
join related datasets exists and that is the need we address. The join
method "Does Not Execute Any SQL", in-fact the join method is meant to
be called after the desired resultsets have be gathered. The join method
is merely an aggregator of result sets.
my ($cd, $artist) = ($db->cd, $db->artist);
$artist->read({ id => $aid });
$cd->read({ artist => $aid });
Always use the larger dataset to initiate the join, in the following
example, the list we want is "the list of cds" and we want to include
the artist information with every "cd" entry so we use the persist
my $resultset = $cd->join($artist, {
persist => 1
The join configuration option "persist" when set true will instruct the
aggregator to include the first entry of the associated table with each
entry in the primary list which is the list (collection) within the
object that initiated the join. Every table object may be passed an
options join configuration object as follows:
my $resultset = $cd->join($artist, {
persist => 1
.. which is the same as ..
my $resultset = $cd->join({
}, $artist, {
persist => 1
.. more complexity ..
my $resultset = $track->join($cd, {
persist => 1
}, $artist, {
persist => 1
By default, a joined resultset is returned as an arrayref of hashrefs
with all table columns as keys which are in $table_$columnName format.
This is not always ideal and so the "columns" join configuration option
allows you to specify exactly which columns to include as well as supply
an alias if desired. The following is an example of that:
my $resultset = $track->join({
columns => {
track_name => 'track',
}, $cd, {
persist => 1
columns => {
cd_name => 'cd',
}, $artist, {
persist => 1,
columns => {
artist_name => 'artist'
The page method creates a paged resultset and instructs the read() method to
only return the resultset of the desired page.
my $page = 1; # page of data to be returned
my $rows = 100; # number of rows to return
$db->table->page($page, $rows)->read;
The pager method provides access to the Data::Page object used in pagination.
Please see L<Data::Page> for more details...
$pager = $db->table->pager;
ORMesque provides columns accessors to the current record in the
resultset object which is accessible via current() by default,
collection() returns an arrayref of hashrefs based on the last read()
call. Alternatively you may use the following methods to further
transform and manipulate the returned resultset.
Returns a list of column names. In scalar context, returns an array reference.
Column names are lower cased if lc_columns was true when the query was executed.
Binds the columns returned from the query to variable(s)
$db->table->read(1)->into(my ($foo, $bar));
Fetches a single row and returns a list of values. In scalar context,
returns only the last value.
my @values = $db->table->read(1)->list;
Fetches a single row and returns an array reference.
my $row = $db->table->read(1)->array;
print $row->[0];
Fetches a single row and returns a hash reference.
Keys are lower cased if lc_columns was true when the query was executed.
my $row = $db->table->read(1)->hash;
print $row->{id};
Fetches all remaining rows and returns a flattened list.
In scalar context, returns an array reference.
my @records = $db->table->read(1)->flat;
print $records[0];
Fetches all remaining rows and returns a list of array references.
In scalar context, returns an array reference.
my $rows = $db->table->read(1)->arrays;
print $rows->[0];
Fetches all remaining rows and returns a list of hash references.
In scalar context, returns an array reference.
Keys are lower cased if lc_columns was true when the query was executed.
my $rows = $db->table->read(1)->hashes;
print $rows->[0]->{id};
Constructs a hash of hash references keyed by the values in the chosen column.
In scalar context, returns a hash reference.
In list context, returns interleaved keys and values.
my $customer = $db->table->read->map_hashes('id');
# $customers = { $id => { name => $name, location => $location } }
Constructs a hash of array references keyed by the values in the chosen column.
In scalar context, returns a hash reference.
In list context, returns interleaved keys and values.
my $customer = $db->table->read->map_arrays(0);
# $customers = { $id => [ $name, $location ] }
Returns the number of rows affected by the last row affecting command,
or -1 if the number of rows is not known or not available.
For SELECT statements, it is generally not possible to know how many
rows are returned. MySQL does provide this information. See DBI for a
detailed explanation.
my $changes = $db->table->insert($db->table->current)->rows;
ORMesque has as its sub-classes DBIx::Simple and SQL::Abstract as its
querying language, it also provides access to SQL::Interp for good
measure. For an in-depth look at what you can do with these utilities,
please check out DBIx::Simple::Examples.
The query function provides a simplified interface to DBI, Perl's
powerful database interfacing module. This function provides
auto-escaping/interpolation as well as resultset abstraction.
$db->query('DELETE FROM foo WHERE id = ?', $id);
$db->query('SELECT 1 + 1')->into(my $two);
$db->query('SELECT 3, 2 + 2')->into(my ($three, $four));
'SELECT name, email FROM people WHERE email = ? LIMIT 1',
)->into(my ($name, $email));
# One big flattened list (primarily for single column queries)
my @names = $db->query('SELECT name FROM people WHERE id > 5')->flat;
# Rows as array references
for my $row ($db->query('SELECT name, email FROM people')->arrays) {
print "Name: $row->[0], Email: $row->[1]\n";
The iquery function is used to interpolate Perl variables into SQL
statements, it converts a list of intermixed SQL fragments and variable
references into a conventional SQL string and list of bind values
suitable for passing onto DBI
my $result = $db->iquery('INSERT INTO table', \%item);
my $result = $db->iquery('UPDATE table SET', \%item, 'WHERE y <> ', \2);
my $result = $db->iquery('DELETE FROM table WHERE y = ', \2);
# These two select syntax produce the same result
my $result = $db->iquery('SELECT * FROM table WHERE x = ', \$s, 'AND y IN', \@v);
my $result = $db->iquery('SELECT * FROM table WHERE', {x => $s, y => \@v});
my $first_record = $result->hash;
for ($result->hashes) { ... }
Access to the underlying DBIx::Simple object.
Access to the underlying DBI object.
Determine whether a database connection exists. Returns true or false.
Al Newkirk <>
This software is copyright (c) 2010 by awncorp.
This is free software; you can redistribute it and/or modify it under
the same terms as the Perl 5 programming language system itself.
Jump to Line
Something went wrong with that request. Please try again.