NAME

Model - simple ORM based on a mix of iBATIS and ActiveRecord

SYNOPSIS

package Models::Service;

use Model 'services';
our @ISA = qw(Pinwheel::Model::Base);

BEGIN {
    belongs_to 'parent', package => 'Models::Service';
    has_many 'broadcasts';
    query 'find_by_directory';
}

our %sql = (
    find_by_directory => q{
        SELECT * FROM services WHERE directory=?
    },
);

DESCRIPTION

Model uses simple schema conventions (adopted from ActiveRecord) to provide lightweight object wrappers around database tables. It deliberately avoids trying to generate SQL statements (with the exception of "find by id").

Each table is represented by a class under Models:: and inherits from Pinwheel::Model::Base. The table name is supplied by the use statement, and relations and query functions/methods are declared with one of belongs_to, has_one, has_many, and query.

All database access is performed via the Database module (which uses DBI). Only mysql data sources are supported.

CONVENTIONS

This module works best with a database schema that uses these ActiveRecord-derived naming conventions:

Table names

Use plural nouns, eg people and contracts, and separate words with underscores, eg line_items.

Keys

Each table with a model class should have a primary key called id.

Foreign keys should use a clean, descriptive name followed by _id. For example, a singular version of the foreign table name such as contract_id or line_item_id, or a description of the relationship, such as parent_pip_id or child_pip_id.

Column names

Avoid putting the table name or a data type in column names, eg customers.name rather than customers.customer_name, and created_at rather than created_date.

RELATIONSHIPS

belongs_to

Declare a one-to-one or many-to-one relationship where the foreign key is in the table containing the belongs_to. For example:

package Models::Broadcast;
...
belongs_to 'service';

This states that the broadcasts table contains a service_id column referencing the services table. Each instance of Models::Broadcast will have a service method which returns the linked Models::Service object.

has_one

Declare a one-to-one or many-to-one relationship where the foreign key is in a different table. For example:

package Models::Episode;
...
has_one 'brand';

With the above, each instance of Models::Episode will have a brand method which returns the linked Models::Brand object.

has_many

Declare a many-to-one relationship. For example:

package Models::Brand;
...
has_many 'episodes';

With the above, each instance of Models::Brand will have an episodes method which returns a list of linked Models::Episode objects.

Each of the relation functions takes three named arguments, package, finder and key:

package

The package name of the class at the other end of the relation. When omitted, the relation name is changed to the singular (by removing 's' from the end except when it ends with 'ies'), converted to a MixedCaseName, and prefixed with Models::. For example, belongs_to 'service' generates a package value of Models::Service.

In the following, the package value is the same as the default:

belongs_to 'service', package => 'Models::Service';
has_one 'brand', package => 'Models::Brand';
has_many 'series', package => 'Models::Series';

finder

The name of the query function to call in package to retrieve the object. For a belongs_to this defaults to find. For a has_many this defaults to find_all_by_ followed by the singular version of the table name, eg find_all_by_service. And for a has_one this defaults to find_by_ followed by the singular version of the table name, eg find_by_broadcast.

In the following, the finder value is the same as the default:

belongs_to 'service', finder => 'find';
has_one 'brand', finder => 'find_by_episode';
has_many 'series', finder => 'find_all_by_series';

key

The attribute to pass to the finder function. For has_one and has_many relations this is id. For belongs_to it is the relation name followed by _id.

In the following, the key value is the same as the default:

belongs_to 'service', key => 'service_id';
has_one 'brand', key => 'id';
has_many 'series', key => 'id';

QUERIES

The query function makes SQL from the package's %sql hash callable as a class or instance method, with parameters passed on as bind variables (model objects parameters are converted to keys via their id method). For example:

package Models::Service;

...

query 'find_by_directory';
our %sql = (
    find_by_directory => q{
        SELECT * FROM services WHERE directory=?
    },
);

...

$service = Models::Service->find_by_directory('radio1');

This would execute the following SQL and return an instance of the Models::Service class.

SELECT * FROM services WHERE directory='radio1'

Query Options

query also allows additional options to be passed:

query 'name_of_query', %opts;

The following options are recognised:

type

The type of value returned by the query can be varied with the type option, which must have one of the following values:

-

Fetch a single row and return it wrapped as an instance of this model class.

[-]

Fetch all the available rows and return a list of model objects.

1

Fetch a single row and return just the first column as a scalar.

[1]

Fetch all the rows and return a list containing just the first column from each as a scalar.

x

No return value.

The default is 1 if the query name begins with "count", - if it begins with "find" (but not "find_all"), x if it begins with any of: set, add, remove, create, replace, update, delete; or [-] otherwise.

Some examples:

# Return the number of rows
query 'count', type => '1';
# Return a list of the first column from each result row
query 'scheduled_days', type => '[1]';
# Return a single row, wrapped as a model object
query 'find_by_directory', type => '-';
# Return a list of model objects
query 'find_all_by_series', type => '[-]';

fn

The fn parameter provides a function to convert the provided arguments into a list of bind variables, and optionally also to declare which (if any) of the model relations will be in the result set. The function is called in list context with the provided arguments, including the leading class or object. The function should return a list of bind variables, optionally preceded by an array reference indicating the list of relations to be filled in from the result set.

postfn

TODO, document me.

include

TODO, document me.

METHODS

Columns are automatically exposed as methods on a model object, eg:

$brand = Models::Brand->find(1);
print $brand->name . "\n";

Model classes also gain the following methods (which also happen to work as object methods):

$foo = Models::Foo->find($id)

Return the row identified by the supplied primary key.

@foos = @{ Models::Foo->find_all }

Return all the rows in the table.

$foo = @foos = @{ Models::Foo->find_all_by_COLUMN($value) }

Return all rows where the given COLUMN matches the value.

$foo = Models::Foo->find_by_COLUMN($value)

Return the row where the given COLUMN matches the value.

See Pinwheel::Model::Base for additional methods gained by model objects.

BUGS

TODO, document the following: sql_param, hash refs as query parameters, the ?$...$ syntax, prefetch, inheritance key/value, how 'describe' is used at import time, wrapping of dates and times, caching. Plus anything marked as "TODO" above.

import should make use of Exporter, so the caller can avoid importing query etc. if they so wish.

The query type values ("-", "[-]", etc) should probably be made available as constants (e.g. QUERY_RETURN_ONE_MODEL, QUERY_RETURN_MANY_MODELS, etc).

AUTHOR

A&M Network Publishing <DLAMNetPub@bbc.co.uk>

cpanm Pinwheel

perl -MCPAN -e shell
install Pinwheel