• NAME
• SYNOPSIS
• To make it useful:
• To use the useful object:
• DESCRIPTION
• Subclassing
• Methods
• Examples
• BUGS
• AUTHOR
• SEE ALSO

NAME

DBIx::Table - Class used to represent DBI database tables.

SYNOPSIS

To make it useful:

  package SUBCLASS;
  @ISA = qw(DBIx::Table);
  sub describe {
      my($self) = shift;
      $self->{'table'}       = $table_name;
      $self->{'unique_keys'} = [ [ $column, ... ], ... ];
      $self->{'columns'}     = { $col_name => { %col_options },
                                 [ ... ]
                               };
    [ $self->{'related'}     = { $class_name => { %relationship },
                                 [ ... ]
                               }; ]
  }

To use the useful object:

  $table = load SUBCLASS( db      => $dbi_object,
                        [ where   => { $column => $value, ... }, ]
                        [ columns => [ $column1, $column2 ], ]
                        [ index   => $index, ]
                        [ count   => $count, ]
                        [ groupby => $groupby, ]
                        [ orderby => ['+'|'-'] . $column ]);
  $table = create SUBCLASS( db => $dbi_object);

  $new_table  = $table->load_related( type => $classname,
                                      row  => $row,
                                    [ %where_arguments ] );
  $num_rows   = $table->num_rows();
  $query_rows = $table->query_rows();
  $columns    = $table->columns();
  $db         = $table->db();
  $level      = $table->debug_level( [ level => $level ] );
  $value      = $table->get( column => $column, [ row => $row ] );
  $retval     = $table->set( change => { $column => $value, [ ..., ] },
                             [ row => $row ] );
  $retval     = $table->refresh( columns = [ $column1, $column2 ],
                                 [ row => $row ] );
  $retval     = $table->commit( [ row => $row ] );
  $retval     = $table->remove( [ row => $row ] );
  $count      = $table->count( [ where => { $column => $value, ... } ]

DESCRIPTION

DBIx::Table is a class designed to act as an abstraction layer around a fairly large subset of SQL queries. It is called 'Table' because it is primarily designed such that a single subclass provides an object-oriented interface to a single database table. The module is flexible enough, though, that it can be used to abstract most any schema in a way that is comfortable to the perl coder who is not familiar with the underlying schema, or even SQL.

As the synopsis above points out, this class is not useful when simply used by itself. Instead, it should be subclassed. The subclass follows a particular syntax for describing the structure of the table, which the Table module uses internally to control its behavior.

The subclass can then be used to access the underlying data, with the Table module taking care of writing the actual SQL. The current version can write SELECT, UPDATE, INSERT and DELETE statements. Depending on the complexity of the definition, it can also do joins across multiple tables and intelligently load related table objects.

The rest of the documentation is split: first, how to create a useful subclass. Second, constructors and access methods on the subclass. Third, some examples. Without further ado...

Subclassing

See the perltoot(1) and perlobj(1) manuals if you don't know how to create a class, subclass, or if you don't understand inheritance or overriding inherited functions.

Every subclass of DBIx::Table is required to provide a method called ``describe'', which, at a minimum, needs to provide some clues as to the form of the underlying data. This is done by modifying a few key parts of the data stored in the object itself:

$self->{'table'}

This should contain a string; the name of the table from which data is going to be retrieved. This should be the primary table in the case of complex classes joining from multiple tables - this table name will be used for columns which do not provide any other table name.

$self->{'unique_keys'}

If you plan on using the commit() or remove() methods, you'll need to provide at least one unique key combination. This parameter takes a reference to an array of references to arrays of strings. The listed strings in the second level array are the names of columns which, taken in conjunction, are guaranteed to be unique in the database. These are tried in order, so put them in order of preference.

$self->{'columns'}

This should contain a reference to a hash, where most of the interesting bits of configuration go. The hash referenced should be keyed by column names, and have values consisting of hash references. These nested hashes should contain configuration options for the column in question. This all sounds pretty hairy, but in practice it's really not so bad - see the Examples section below. Here are the available column options:

null

DBIx::Table only cares if this is defined or not defined. If it is defined, a commit() call will fail if a value for this column is not set() first, or no default is supplied. This only applies to local columns.

quoted

DBIx::Table only cares if this is defined or not defined. If it is defined, data which is set() to this column will be quoted using $self->{'db'}->quote(), the quote method on the DBI object passed into the constructor.

immutable

DBIx::Table only cares if this is defined or not defined. If it is defined, trying to set() a value to this column will cause set() to fail. Immutability is, for now at least, implicit on all ``foreign'' and ``special'' columns - i.e. you can't update foreign data!

autoincrement

DBIx::Table only cares if this is defined or not defined. If it is defined, some magic will take place to ensure that, after an INSERT, the autoincremented value is correctly stored in the object. This is probably MySQL specific.

default

The contents of this parameter will be used by commit() to UPDATE or INSERT data on a column without the null attribute. It will be quoted if the quoted attribute is set.

foreign

This contains another hash reference. It is used to define the simplest case foreign columns. The hash requires the keys 'table', 'lkey', and 'rkey' - the name of the table to join, relationship column name in the joined table, and relationship column name in the local table, respectively. Optionally, it can also take 'actual_table' and 'actual_column' keys, with their values being the real names of the foreign table and column. This can be used to fetch the same column from a table more than once, based on different WHERE clauses. See the Examples section to visualize this in action.

special

This contains yet another hash reference (doh!) It is used to define columns which defy the abstraction currently provided by DBIx::Table. Most frequently, this will be any column which has functions act on it, and/or complicated joins. The recognized keys are 'select', 'join', 'where' and 'groupby'. The values for each of these keys are raw SQL pieces, which will be stuffed into the appropriate place in the generated SQL. One important note is that the constructor expects the column name defined in $self->{'columns'} to match the name of the column where this data is returned by the SQL statement, so you will want to always add 'AS column_name' to the end of the select chunk of SQL. And again, check out the Examples to help visualize how this works.

$self->{'related'}

This is not mandatory! If defined, it should contain a reference to a hash, keyed by package names. The values are hash references, keyed by column names from the foreign package, with values being the column name of a local column. This is used by the load_related convenience method - see the description of this method below, as well as the Examples section, to get more of a grasp of how this works.

Methods

All of the public methods use hash-style arguments. I've tried to be consistent and obvious in the naming of arguments.

The only class methods are the constructors, all other methods are strictly object methods (i.e. you can't call SUBCLASS::get(...), you have to call $subclass_object->get(...)).

All methods return undef if they fail.

General issues aside, here are the descriptions of the specific methods:

There are two constructors:

load()

This is the primary constructor and SELECT statement generator. It takes a bunch of arguments, though only the db argument is mandatory:

db

This argument should contain a reference to a DBI object. It is assumed to be connected and valid for the lifetime of the object to which it is passed.

where

This argument should contain a hash reference, with keys being column names and values being the value that column must equal. As a bonus hack, if the value is the string 'IS NULL', it'll work as you want it to (unless you're actually looking for the string 'IS NULL' in the database, in which case you're screwed.) If this argument is excluded, no WHERE clause will be used (all rows will be returned.)

columns

This argument should contain an array reference. The elements of the array should be column names, or the special string '*'. It is important to realize that columns with the 'foreign' or 'special' attributes are not loaded if this argument is left out of the load() call. The special string '*' is expanded to all columns which are not 'foreign' or 'special'. If this argument is left out, a literal '*' will go in the SQL generated, indicating that all local columns in this table should be loaded.

index

This argument should contain a numeric scalar, which is used to limit the amount of data stored (but not queried!) by the object returned by load(). It is the number of the first row to store, starting from zero.

count

This argument should contain a numeric scalar, which is also used to limit the amount of data stored (but, again, not queried!) by load(). This is a zero-based count of the maximum number of rows to store.

groupby

This argument should contain a string scalar. It is the name of a column, and it causes the addition of a GROUP BY column_name clause to the end of the SQL generated.

orderby

This argument should contain a string scalar, the name of a column which to be used to sort the returned data. It causes the addition of an ORDER BY column_name clause to the SQL generated. Bonus hack: if you prepend a ``+'' to the column name, the clause has ``ASC'' appended to it, and if you prepend a ``-'' to the column name, the clause has ``DESC'' appended to it.

create()

This simple constructor builds and returns an empty object. It is useful for inserting new data into a table. It is also useful for creating an empty instance of the object with which to use the count() method. It takes only one argument, 'db', which is identical to the 'db' argument to load(), described above.

Two methods for using the data in the object:

get()

This method is used to fetch data from the object. It takes only two possible arguments; only the 'column' argument is mandatory.

column

This should contain the name of the column to retrieve. At the moment, you can only retrieve one value at a time.

row

This should contain the number of the row to retrieve data from. Rows in the object are always indexed by 0. If this argument is excluded, it defaults to 0.

set()

This method is used to change data in the object. It has only two possible arguments. Only the 'change' argument is mandatory, and the 'row' argument is identical to that described in get() above.

change

This should contain a hash reference. The keys of the hash should be column names, and the values should be the values you wish to place in those columns. You can change as many columns as you want at once, but remember that 'foreign', 'special' and 'immutable' columns cannot be changed.

Three methods can make additional SQL queries based on the current data:

refresh()

This method can perform additional SELECT queries to the database, using the data already loaded to ensure that the new data relates to the existing row. It takes a mandatory 'columns' argument, an array reference containing column names to load. It can also take a 'row' argument, as described under get() above.

commit()

This method is responsible for writing UPDATE or INSERT SQL statements. It takes one optional argument, 'row', which is identical to the 'row' argument described under get() above.

Please be careful with this method, as it has only been tested for fairly simple cases.

remove()

This method writes DELETE SQL statements, attempting to remove the current row from the database permanently. It takes one optional argument, 'row', which is identical to the 'row' argument described under get() above.

Please be careful with this method, as it has only been tested for fairly simple cases.

Several methods can be used to get meta-data about the object, and the data retrieved, and configure behavior of the object:

num_rows()

Returns the number of rows stored in the current object.

query_rows()

Returns the number of rows returned by the last query (this may be different from num_rows() if 'index' or 'count' parameters to load() were used).

columns()

Returns a reference to an array containing the names of all the columns in the object. Not only the ones with data in them, mind you! All column names are returned.

db()

Returns a reference to the database object that is being used.

debug_level()

This takes a 'level' parameter, with a numeric value. The range from 0 to 2 is significant: 0 is trivial debugging information, 1 is informational messages, and 2 is errors. Debugging information is only printed (to STDERR) if the debug_level is set to the priority level of the message or lower. This function can also be used with no arguments to return the current debug_level.

And a couple of ``miscellaneous'' utility functions:

count()

This is a function for generating count(*) style SELECT statements. It does not store the return from the query; instead, it returns it to the caller. It takes an optional 'where' argument, identical to the one described under load().

load_related()

This is a utility function for constructing objects from different classes, which are related to the current class. It takes two arguments of its own, but only the 'type' argument is mandatory:

type

This should contain the name of the class from which to load a new object.

row

This should contain the row number to which the new object should be related. It defaults to 0. The row number is used in the substitution process described below.

All other arguments will be passed on to the load() constructor for the class passed in in 'type'. It is not necessary to provide a 'db' argument; it will simply pass on the one it already has. And finally, the real nicety provided by load_related is that it will check the 'where' argument (if any) and will use the information in $self->{'related'} to substitute values. So you can say where => { 'that_column' => 'this_column' }, and load_related will convert the literal 'this_column' into the current value of this_column.

Examples

These examples are simple but are designed to show you how this module can be used. They progress from table descriptions to complete subclasses to usage, including showing the SQL that is output.

Beginning with two tables (as you would create them with MySQL). First, a simple users table, very straightforward:

  CREATE TABLE users (
    uid      INT UNSIGNED NOT NULL AUTO_INCREMENT,
    email    VARCHAR(70)  NOT NULL,
    password VARCHAR(10)  NOT NULL,
    PRIMARY KEY(uid),
    UNIQUE (uid),
  );

Second, a feedback table. This table is complexly related to the users table; each user can both send and receive multiple feedback, so both the to_uid and from_uid columns point back to the users table.

  CREATE TABLE feedback (
    fid      INT UNSIGNED                   NOT NULL AUTO_INCREMENT,
    to_uid   INT UNSIGNED                   NOT NULL,
    from_uid INT UNSIGNED                   NOT NULL,
    time     DATETIME                       NOT NULL,
    type     ENUM('good', 'bad', 'neutral') NOT NULL,
    text     VARCHAR(100)                   NOT NULL,
    PRIMARY KEY (fid),
    INDEX (to_uid),
    INDEX (from_uid),
    UNIQUE uid_combo (to_uid, from_uid)
  );

Okay, now we need to create objects to represent them. For users, I want to be able to fetch the counts of the feedback recieved by the user in question for each of the three feedback types, which requires 'special' columns:

  package User;
  use strict;
  use DBIx::Table;
  @User::ISA = qw(DBIx::Table);

  sub describe {
      my($self) = shift || return(undef);

      $self->{'table'}       = 'users';
      $self->{'unique_keys'} = [ ['uid'] ];
      $self->{'columns'}     = {
        'uid'        => { 'immutable'     => 1,
                          'autoincrement' => 1,
                          'default'       => 'NULL' },
        'email'      => { 'quoted'        => 1 },
        'password'   => { 'quoted'        => 1 },
        'good_fb'    => { 'special'       => 
           { 'select'  => 'count(fb_g.type) AS good_fb',
             'join'    => 'LEFT JOIN feedback AS fb_g ON (fb_g.type = '
                          . '\'good\' AND fb_g.to_uid = users.uid)',
             'groupby' => 'uid' } },
        'bad_fb'     => { 'special'       =>
           { 'select'  => 'count(fb_b.type) AS bad_fb',
             'join'    => 'LEFT JOIN feedback AS fb_b ON (fb_b.type = '
                          . '\'bad\' AND fb_b.to_uid = users.uid)',
             'groupby' => 'uid' } },
        'neutral_fb' => { 'special'       =>
           { 'select'  => 'count(fb_n.type) AS neutral_fb',
             'join'    => 'LEFT JOIN feedback AS fb_n ON (fb_n.type = '
                          . '\'neutral\' AND fb_n.to_uid = users.uid)',
             'groupby' => 'uid' } }
      };
      $self->{'related'}    = { 'Feedback' => { 'from_uid' => 'uid',
                                                'to_uid'   => 'uid' } };   
  }
  1;

Phew. Now how about the feedback table. In this case, we'd like to be able to fetch the email addresses of both the sender and recipient users in the same query. This can be done with 'foreign' columns. Here's the class:

  package Feedback;
  use strict;
  use DBIx::Table;
  @Feedback::ISA = qw(DBIx::Table);

  sub describe {
      my($self) = shift || return(undef);

      $self->{'table'}       = 'feedback';
      $self->{'unique_keys'} = [ ['fid'] ];
      $self->{'columns'}     = {
        'fid'        => { 'immutable'     => 1,
                          'autoincrement' => 1,
                          'default'       => 'NULL' },
        'to_uid'     => { },
        'from_uid'   => { },
        'time'       => { 'default'       => 'NOW()' },
        'type'       => { 'quoted'        => 1 },
        'text'       => { 'quoted'        => 1 },
        'to_email'   => { 'foreign'       =>
           { 'table'         => 'users_to',
             'lkey'          => 'to_uid',
             'rkey'          => 'uid',
             'actual_table'  => 'users',
             'actual_column' => 'email' } },
        'from_email' => { 'foreign'       =>
           { 'table'         => 'users_from',
             'lkey'          => 'from_uid',
             'rkey'          => 'uid',
             'actual_table'  => 'users',
             'actual_column' => 'email' } }
      };
  }
  1;

Using these is simple enough. The simplest case would be:

  $obj = load User( db => $db );

which generates the SQL:

  SELECT * from users

Not very intersting. How about:

  $obj = load User db      => $db,
                   where   => { uid => 1 },
                   columns => [ '*', 'good_fb', 'bad_fb', 'neutral_fb' ];

Which generates the SQL (formatted for viewing ease):

  SELECT      count(fb_g.type) AS good_fb,
              count(fb_b.type) AS bad_fb,
              count(fb_n.type) AS neutral_fb,
              users.password,
              users.email,
              users.uid
  FROM        users
    LEFT JOIN feedback AS fb_g
           ON (fb_g.type = 'good'    AND fb_g.to_uid = users.uid)
    LEFT JOIN feedback AS fb_b
           ON (fb_b.type = 'bad'     AND fb_b.to_uid = users.uid)
    LEFT JOIN feedback AS fb_n
           ON (fb_n.type = 'neutral' AND fb_n.to_uid = users.uid)
  WHERE       users.uid = 1
  GROUP BY    uid

Let's load up all feedback with the associated e-mail addresses, arranged by descending time sent:

  $obj = load Feedback db      => $db,
                       columns => [ '*', 'to_email', 'from_email' ],
                       orderby => '-time' ;

Generates the SQL (again, formatted):

  SELECT   users_to.email   AS to_email,
           users_from.email AS from_email,
           feedback.type,
           feedback.vs_fid,
           feedback.to_uid,
           feedback.text,
           feedback.from_uid,
           feedback.time
  FROM     feedback
      JOIN users AS users_to
      JOIN users AS users_from
  WHERE    feedback.to_uid = 2
       AND users_to.uid   = feedback.to_uid
       AND users_from.uid = feedback.from_uid
  ORDER BY feedback.time DESC

That seems like enough to get started.

BUGS

Autoincrement columns are known to be mySQL specific. There's probably more, since this has really only been tested with MySQL. If anyone tries it with another DBD, I'd love to hear from you!
In a stupid design oversight, the current incarnation can only automatically generate ``... WHERE foo = bar ...'' in SELECT statements, emphasis on the '='. Sorry! I'll work on it!

AUTHOR

J. David Lowe, dlowe@pootpoot.com

SEE ALSO

perl(1)