The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
Dancer-Plugin-ORMesque-1.103160
++
/ Dancer::Plugin::ORMesque

NAME

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

VERSION

version 1.103160

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;

next

    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
    
    dbi->table->read(1);
    
    # return arrayref from read (select) method
    my $records = dbi->table->read->collection

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

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