/ 
Class-Tables-0.25_1
River stage zero No dependents
/ Class::Tables

NAME

Class::Tables - Auto-vivification of persistent classes, based on RDBMS schema

SYNOPSIS

Telling your relational object persistence class about all your table relationships is no fun. Wouldn't it be nice to just include a few lines in a program:

use Class::Tables;
Class::Tables->dbh($dbh);

and magically have all the object persistence classes from the database, preconfigured, with table relations auto-detected, etc?

This is the goal of Class::Tables. Its aim is not to be an all-encompassing tool like Class::DBI, but to handle the most common and useful cases smartly, quickly, and without needing your help. Just pass in a database handle, and this module will read your mind (by way of your database's table schema) in terms of relational object persistence. The very simple (and flexible) rules it uses to determine your object relationships from your database's schema are so simple, you will probably find that you are already following them.

Introductory Example

Suppose your database schema were as unweildy as the following SQL. The incosistent naming, the plural table names and singular column names are not a problem for Class::Tables.

create table departments (
    id            int not null primary key auto_increment,
    name          varchar(50) not null
);
create table employees (
    employee_id   int not null primary key auto_increment,
    name          varchar(50) not null,
    salary        int not null,
    department_id int not null
);

To use Class::Tables, you need to do no more than this:

use Class::Tables;
my $dbh = DBI->connect($dsn, $user, $passwd) or die;
Class::Tables->dbh($dbh);

Et voila! Class::Tables looks at your table schema and generates two classes, Departments and Employees, each with constructor and search class methods:

my $marketing  = Departments->new( name => "Marketing" );
my @underpaid  = Employees->search( salary => 20_000 );
my @marketeers = Employees->search( department => $marketing );
my $self       = Employees->fetch($my_id);

It also generates the following instance methods:

A deletion method for both classes

This simply removes the object from the database.

$marketing->delete;
Readonly id accessor methods for both classes

For Departments objects, this corresponds to the id column in the table, and for Employees objects, this corresponds to the employee_id column. Class::Tables is smart enough to figure this out, even though "employee" is singular and "employees" is plural (See "Plural And Singular Nouns").

print "You're not just a name, you're a number: " . $self->id;
Normal accessor/mutator methods

Departments objects get a name accessor/mutator method, and Employees objects get name and salary accessor/mutator methods, referring to the respective columns in the database.

$self->salary(int rand 100_000);
print "Pass go, collect " . $self->salary . " dollars";
Foreign key methods

When Class::Tables sees the department_id column in the employees table, it knows that there is also a departments table, so it treats this column as a foreign key. Thus, Employees objects get a department accessor/mutator method, which returns (and can be set to) a Departments object.

print "I'd rather be in marketing than " . $self->department->name;
$self->department($marketing);

It also reverses the foreign key relationship, so that all Departments objects have a readonly employees method, which returns a list of all Employees objects referencing the particular Departments object.

my @overpaid  = $marketing->employees;
## same as:     Employees->search( department => $marketing )

my @coworkers = $self->department->employees;

Notice how the plural vs. singular names of the methods match their return values. This is all automatic! (See "Plural And Singular Nouns")

USAGE

Class::Tables offers more functionality than just the methods in this example.

Database Metadata

Here's a more concrete explanation of how Class::Tables will use your table schema to generate the persistent classes.

Class Names

Each table in the database must be associated with a class. The table name will be converted from underscore_separated style into StudlyCaps for the name of the class/package.

Primary Key

All tables must have a primary key column in the database, which is an integer column set to AUTO_INCREMENT. This column must be named either id, the table name followed by an _id suffix, or the singular form of the table name followed by an _id suffix.

In our above example, the employees table could have had a primary key column named employee_id, employees_id, or id. The flexibility allows for reasonable choices whether you name your tables as singular or plural nouns. (See "Plural And Singular Nouns")

For simplicity and transparency, the associated object accessor is always named id, regardless of the underlying column name.

Foreign Key Inflating

If a column has the same name as another table, that column is treated as a foreign key reference to that table. Alternately, the column may be the singular form of the table name, and an optional _id suffix may be added. The name of the accessor is the name of the column, minus the _id.

In our above example, the foreign key column relating each employee with a department could have been named departments, departments_id, department, or department_id. Again, the flexibility allows for a meaningful column name whether your table names are singular or plural. (See "Plural And Singular Nouns").

The foreign key relationship is also reversed as described in the example. The name of the accessor in the opposite direction is the name of the table. In our example, this means that objects of the Departments class get an accessor named employees. For this reason, it is often convenient to name the tables as plural nouns.

Lazy Loading

All *blob and *text columns will be lazy-loaded: not loaded from the database until their values are requested or changed.

Automatic Sort Order

The first column in the table which is not the primary key is the default sort order for the class. All operations that return a list of objects will be sorted in this order (ascending). In our above example, both tables are sorted on name.

Stringification

If the table has a name column, then any objects of that type will stringify to the value of $obj->name. Otherwise, the object will stringify to CLASS:ID.

Public Interface

use Class::Tables $option

$option may be either 'no_cascade' or 'cascade' to disable and enable cascading deletes, respectively. See delete below. The default is to enable cascading deletes. If you need to change cascading delete behavior on the fly, set $Class::Tables::CASCADE.

Class::Tables->dbh($dbh)

You must pass Class::Tables an active database handle before you can use any generated object classes.

Object Instance Methods

Every object in a Class::Tables-generated class has the following methods:

$obj->id

This readonly accessor returns the primary key of the object.

$obj->delete

Removes the object from the database. The behavior of further method calls on the object are undefined.

If cascading deletes are enabled, then all other objects in the database that have foreign keys pointing to $obj are deleted as well, and so on. Cyclic references are not handled gracefully, so if you have a complicated database structure, you should disable cascading deletes. You can roll your own cascading delete (to add finer control) very simply:

package Department;
sub delete {
    my $self = shift;
    $_->delete for $self->employees;
    $self->SUPER::delete;
}

It's important to point out that in this process, if an object looses all foreign key references to it, it is not deleted. For example, if all Employees in a certain department are deleted, the department object is not automatically deleted. If you want this behavior, you must add it yourself in the Employees::delete method.

$obj->attrib and $obj->attrib($new_val)

For normal columns in the table (that is, columns not determined to be a foreign key reference), accessor/mutator methods are provided to get and set the value of the column, depending if an argument is given.

For foreign key reference columns, calling the method as an accessor is equivalent to a fetch (see below) on the appropriate class, so will return the referent object or undef if there is no such object. When called as a mutator, the argument may be either an ID or an appropriate object:

## both are ok:
$self->department( $marketing );
$self->department( 10 );

For the reverse-mapped foreign key references, the method is readonly, and returns a list of objects. It is equivalent to a search (see below) on the appropriate class, which means you can also pass additional constraints:

my @marketeers = $marketing->employees;
## same as Employees->search( department => $marketing );

my @volunteers = $marketing->employees( salary => 0 );
## same as Employees->search( department => $marketing, salary => 0 );
$obj->field($field) and $obj->field($field, $new_val)

This is an alternative syntax to accessors/mutators. When $field is the name of a valid accessor/mutator method for the object, this is equivalent to saying $obj->$field and $obj->$field($new_val).

$obj->dump

Returns a hashref containing the object's attribute data. It recursively inflates foreign keys and maps reverse foreign keys to an array reference. This is particularly useful for generating structures to pass to HTML::Template and friends.

As an example, suppose we also have tables for Purchases and Products, with appropriate foreign keys. Then the result of dump on an Employees object may look something like this:

{
    'name'            => 'John Doe',
    'id'              => 7,
    
    'department.name' => 'Sales',
    'department.id'   => 4,
    
    'purchases'       => [
        {
            'date'           => '2002-12-13',
            'quantity'       => 1,
            'id'             => 5,
            
            'product.id'     => 1,
            'product.name'   => 'Widgets',
            'product.price'  => 200,
            'product.weight' => 10
        },
        {
            'date'           => '2002-12-15',
            'quantity'       => 2,
            'id'             => 6,
            
            'product.id'     => 3,
            'product.name'   => 'Foobars',
            'product.price'  => 150,
            'product.weight' => 200
        }
    ]
};

The amount of foreign key inflation is bounded: A foreign key accessor will only be followed if its corresponding table hasn't been seen before in the dump. This is necessary because, for example, our Purchases objects have a foreign key pointing back to the John Doe employee object. But following that foreign key would cause an infinite recursion. This means there is no quick way to get a list such as $john_doe->department->employees from the dump, because the employees table would be visited twice.

Data Class Methods

Every persistent object class that Class::Tables generates gets the following class methods:

Class->new( field1 => $value1, field2 => $value2, ... )

Creates a new object in the database with the given attributes set. If successful, returns the object, otherwise returns undef. This is equivalent to the following:

my $obj = Class->new;
$obj->field1($value1);
$obj->field2($value2);
...

So for foreign key attributes, you may pass an actual object or an ID:

## both are ok:
Employee->new( department => $marketing );
Employee->new( department => 10 );
Class->search( field1 => $value1, field2 => $value2, ... )

Searches the appropriate table for objects matching the given constraints. In list context, returns all objects that matched (or an empty list if no objects matched). In scalar context returns only the first object returned by the query (or undef if no objects matched). The scalar context SQL query is slightly optimized.

As usual, for foreign key attributes, you may pass an actual object or an ID. If no arguments are passed to search, every object in the class is returned.

Class->fetch($id)

Equivalent to Class->search( id => $id ) in scalar context, but slightly optimized internally. Returns the object, or undef if no object with the given ID exists in the database.

Notes On Persistent Classes

Objects in these persistent classes are implemented as lightweight blessed scalars in an inside-out mechanism. This has some benefits, mainly that concurrency across identical objects is always preserved:

my $bob1 = Employees->fetch(10);
my $bob2 = Employees->fetch(10);

## now $bob1 and $bob2 may not be the same physical object, but...

$bob1->name("Bob");
$bob2->name("Robert");

print $bob1->name, $bob2->name;

## will print "Robert" twice

You can still override/augment object methods if you need to with SUPER::

## Suppose the "last_seen" column in a "users" table was a
## YYYYMMDDHHMMSS timestamp column. We could override the last_seen
## method to return a Time::Piece object, and accept one when used
## as a mutator:

package Users;
my $date_fmt = "%Y%m%d%H%M%S";
sub last_seen {
    my $self = shift;
    my $ret  = @_
      ? $self->SUPER::last_seen( $_[0]->strftime($date_fmt) );
      : $self->SUPER::last_seen;

    Time::Piece->strptime($ret, $date_fmt);
}

But since these objects are implemented as blessed scalars, you have to use some sort of inside-out mechanism to store extra (non-persistent) subclass attributes with the objects:

package Employees;
my %foo;
sub foo {
    my $self = shift;
    @_ ? $foo{ $self->id } = shift
       : $foo{ $self->id };
}

Plural And Singular Nouns

Class::Tables makes strong use of Lingua::EN::Inflect to convert between singular and plural, in an effort to make accessor names more meaningful and allow a wide range of column-naming schemes. So when this documentation talks about plural and singular nouns, it does not just mean "adding an S at the end." You zoologists may have a mice table with a corresponding primary/foreign key named mouse_id! Goose to geese, child to children, etc. The only limitations are what Lingua::EN::Inflect doesn't know about.

I recommend naming tables with a plural noun and foreign key columns with a singular noun (optionally with the "_id" suffix). This combination makes the accessor names much more meaningful, and is (to my knowledge) the most common relational naming convention.

If Lingua::EN::Inflect is not available on your system, Class::Tables will still work fine, but without the distinction between plurals and singulars. Thus a primary key column can be named only id or the name of the table with an _id suffix. Similar statments are true for foreign key columns, etc.

You can manually disable the pluralization by setting $Class::Tables::PLURALIZE to a false value before you generate the classes.

CAVEATS

  • So far, the table parsing code only works with MySQL. Same with getting the ID of the last inserted object. Testers/patchers for other RDBMSs welcome!

  • Pluralization code is only for English at the moment, sorry.

  • All modifications to objects are instantaneous -- no asynchronous updates and/or rollbacks (yet?)

AUTHOR

Class::Tables is written by Mike Rosulek <mike@mikero.com>. Feel free to contact me with comments, questions, patches, or whatever.

COPYRIGHT

Copyright (c) 2003 Mike Rosulek. All rights reserved. This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself.