NAME

Dancer::Plugin::ORMesque - Light ORM for Dancer

VERSION

version 1.103180

SYNOPSIS

Dancer::Plugin::ORMesque is a lightweight ORM for Dancer supporting any database listed under Dancer::Plugin::ORMesque::SchemaLoader making it a great alternative to Dancer::Plugin::Database if you are looking for a bit more automation and a fair alternative to Dancer::Plugin::DBIC when you don't have the time, need or desire to learn Dancer::Plugin::DBIC and DBIx::Class. Dancer::Plugin::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. Dancer::Plugin::ORMesque uses SQL::Abstract querying syntax.

Connection details will be taken from your Dancer application config file, and should be specified as, for example:

plugins:
  Database:
    driver: 'mysql'
    database: 'test'
    host: 'localhost'
    username: 'myusername'
    password: 'mypassword'
    connectivity-check-threshold: 10
    dbi_params:
        RaiseError: 1
        AutoCommit: 1
    on_connect_do: ["SET NAMES 'utf8'", "SET CHARACTER SET 'utf8'" ]

NOTE! In your configuration file, under plugins, the plugin that should be configured is 'Database' and not 'ORMesque'.

The connection functionality is imported from Dancer::Plugin::Database, please look into that plugin for more information. Please note that even if you use supply a DSN directly in your configuration file you need to also specify a driver directive.

# Use the dbi (database interface) keyword to establish a new connection to
# the database then access users (the users table) and store the reference in
# local variable $users

my $user = dbi->users;

# Grab the first record, not neccessary if operating on only one record

$user->read;

# SQL::Abstract where clause passed to the "read" method

$user->read({
    'column' => 'query'
});

$user->first;
$user->last;

# How many records in collection

$user->count

for (0..$user->count) {
    print $user->column;
    $user->column('new stuff');
    $user->update($user->current, $user->id);
    $user->next;
}

# The database objects main accessors are CRUD (create, read, update, and delete)

$user->create;
  $user->read;
    $user->update;
      $user->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

dbi

The dbi method/keyword instantiates a new Dancer::Plugin::ORMesque instance
which uses the datasource configuration details in your configuration file
to create database objects and accessors.

my $db = dbi;

reset

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.

dbi->reset;
my $db = dbi;

The next method instructs the database object to continue to the next
row if it exists.

dbi->table->next;

while (dbi->table->next) {
    ...
}

first

The first method instructs the database object to continue to return the first
row in the resultset.

dbi->table->first;

last

The last method instructs the database object to continue to return the last
row in the resultset.

dbi->table->last;

collection

The collection method return the raw resultset object.

dbi->table->collection;

current

The current method return the raw row resultset object of the position in
the resultset collection.

dbi->table->current;

clear

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.

dbi->table->clear;

key

The key method finds the database objects primary key if its defined.

dbi->table->key;

select

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 = dbi->select('foo', 'bar')->read();

return

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 = dbi->table->create(...)->return();

count

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 = dbi->table->read->count;

create

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)

dbi->table->create({
    'column_a' => 'value_a',
});

# create a copy of an existing record
my $user = dbi->users;
$user->read;
$user->full_name_column('Copy of ' . $user->full_name);
$user->user_name_column('foobarbaz');
$user->create($user->current);

# get newly created record
$user->return;

print $user->id; # new record id
print $user->full_name;

read

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

dbi->table->read({
    'column_a' => 'value_a',
});

.. or read by primary key ..

dbi->table->read(1);

.. or read and limit the resultset ..

dbi->table->read({ 'column_a' => 'value_a' }, ['orderby_column_a'], $limit, $offset);

.. or return a paged resultset ..

dbi->table->page(1, 25)->read;

update

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

dbi->table->update({
    'column_a' => 'value_a',
},{
    'where_column_a' => '...'
});

or

dbi->table->update({
    'column_a' => 'value_a',
}, 1);

delete

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.

dbi->table->delete({
    'column_a' => 'value_a',
});

or

dbi->table->delete(1);

delete_all

The delete_all method is use to intentionally empty the entire database table.

dbi->table->delete_all;

join

If you have used Dancer::Plugin::ORMesque with a project of any sophistication you will have undoubtedly noticed that the is no mechanism for specifying joins and this is intentional. Dancer::Plugin::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) = (dbi->cd, dbi->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 option.

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'
    }
});

page

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

dbi->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 = dbi->table->pager;

$pager->first_page;
$pager->last_page;

RESULTSET METHODS

Dancer::Plugin::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.

columns

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.

into

Binds the columns returned from the query to variable(s)

dbi->table->read(1)->into(my ($foo, $bar));

list

Fetches a single row and returns a list of values. In scalar context,
returns only the last value.

my @values = dbi->table->read(1)->list;

array

Fetches a single row and returns an array reference.

my $row = dbi->table->read(1)->array;
print $row->[0];

hash

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 = dbi->table->read(1)->hash;
print $row->{id};

flat

Fetches all remaining rows and returns a flattened list.
In scalar context, returns an array reference.

my @records = dbi->table->read(1)->flat;
print $records[0];

arrays

Fetches all remaining rows and returns a list of array references.
In scalar context, returns an array reference.

my $rows = dbi->table->read(1)->arrays;
print $rows->[0];

hashes

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 = dbi->table->read(1)->hashes;
print $rows->[0]->{id};

map_hashes

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 = dbi->table->read->map_hashes('id');
# $customers = { $id => { name => $name, location => $location } }

map_arrays

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 = dbi->table->read->map_arrays(0);
# $customers = { $id => [ $name, $location ] }

rows

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 = dbi->table->insert(dbi->table->current)->rows;

UTILITIES

Dancer::Plugin::ORMesque is a sub-class of DBIx::Simple and uses 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 l<DBIx::Simple::Examples>.

query

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));

$db->query(
    'SELECT name, email FROM people WHERE email = ? LIMIT 1',
    $mail
)->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";
}

iquery

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) { ... }

AUTHOR

Al Newkirk <awncorp@cpan.org>

COPYRIGHT AND LICENSE

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

To install Dancer::Plugin::ORMesque, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Dancer::Plugin::ORMesque

CPAN shell

perl -MCPAN -e shell
install Dancer::Plugin::ORMesque

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)