The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
CAM-SQLObject-1.01
River stage zero No dependents
++
/ CAM::SQLObject

NAME

CAM::SQLObject - Object parent class for SQL delegates

LICENSE

Copyright 2005 Clotho Advanced Media, Inc., <cpan@clotho.com>

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

COMPARISON

This is one of many modules that tries to apply an Object-Oriented front on SQL database content. The primary thing that's special about it is that the helper module, CAM::SQLManager, has a slick way of encapsulating the SQL so your Perl code has no SQL in it.

The advantage of externalizing your SQL is like the advantage of externalizing HTML content via templating systems. You can work on them separately and you don't need your programmer to also be a DBA. Having the SQL be separate also lets you easily test and optimize your queries separately from the main program logic. Very handy. See CAM::SQLManager for more info.

SYNOPSIS

    package Foo;
    use CAM::SQLObject;
    our @ISA = qw(CAM::SQLObject);
    
    sub sqlcmd { return "foo.xml" }
    sub keyName { return "foo_id" }
    sub insertQueryName { return "add" }
    [ ... other specific changes for this package ... ]
    
    sub renderfoo_name {
      my ($self) = @_;
      return "NAME: " . $self->getfoo_name();
    }
    
    sub getfoo_name {
      my ($self) = @_;
      return $self->{fields}->{foo_name};
    }
    
    sub setfoo_name {
      my ($self, $value) = @_;
      $self->{fields}->{foo_name} = $value;
    }
    
    sub setfoo_id {
      my ($self, $value) = @_;
      $self->{fields}->{foo_id} = $value;
      $self->{keyvalue} = $value;
    }

DESCRIPTION

This class is not meant to be instantiated directly. Instead, it is intended to be the superclass of real database frontend objects. Those objects will typically add several get<field> and set<field> routines to act as accessors and mutators for the database fields.

CLASS METHODS

AUTOLOAD

If you call a method on this class that does not exist, the AUTOLOAD function takes over. The CAM::SQLObject autoloader handles a few specialized dynamic methods:

If that method name looks like one of:

   $obj->set<field>(...)
   $obj->get<field>(...)
   $obj->render<field>(...)

then AUTOLOAD implements the appropriate call of the following:

   $obj->set(<field>, ...)
   $obj->get(<field>, ...)
   $obj->render(<field>, ...)

If a subclass overrides a particular get, set, or render method, then that one will be used. The SYNOPSIS above shows how to write an override method.

Special case: If the field starts with Table_, then that is replaced with the tableName() value plus and underscore. If the class does not define the table name, then the method will fail and a warning will be emitted.

If the method name looks like:

   $pkg->retrieve<query>(...)

then AUTOLOAD implements a call to:

   $pkg->retrieve(<query>, ...)

which can be overloaded in ways similar to the object methods above.

Note that get(), set() and render() are instance methods while retrieve() is a class method.

new

Creates a new stub object.

The following documenation is DEPRECATED as of version 0.50. New subclasses should instead override individual functions instead of changing the new() method.

Subclasses should consider overriding the following fields, in order of priority:

  Name         Default   Purpose
  ----         -------   -------
  sqlcmd       undef     location of the SQL templates (see SQLManager)
  keyname      undef     name of the primary key field (needed for save())
  tablename    undef     name of the SQL table (needed for fieldNames())
  update_name  "update"  name of query in sqlcmd (needed for save())
  insert_name  "insert"  name of query in sqlcmd (needed for save())
  delete_name  "delete"  name of query in sqlcmd
  keygen       undef     which technique should be used to get new keys
  keygen_data  undef     info needed for how to generate keys

See newkey() below for more information on keygen and keygen_data.

getMgr

Retrieves a CAM::SQLManager instance for this class. This can be called as a class method or as an instance method.

setDBH DBH

Tells the SQL manager to use the specified database handle for all interaction with objects of this class. If setDBH() is not called, the default database handle from CAM::SQLManager is used. This method must be called before any objects are instantiated.

retrieveByKey KEYVALUE

Class method to retrieve a single object for the specified key. Objects with complicated SQL representations should override this method.

This method executes an implicit query that looks like:

  select * from <table> where <keyname>=<keyvalue>
retrieve QUERYNAME, [KEY => VALUE, KEY => VALUE, ...]

Generic class method to retrieve objects from a specified query. The extra parameters are passed as bind variables to the query. This is pretty much just a handy wrapper around the CAM::SQLManager method retrieveObjects().

In scalar context, just the first object will be returned. In array context, all of the matching objects are returned.

Recommended usage: Use this via the autoloaded method retrieve<queryname>(). For example, if you have a query "GetOldClients" which takes "year" as a query parameter, then call it like:

    @clients = CAM::SQLObject->retrieveGetOldClients(year => "1998");

instead of

    @clients = CAM::SQLObject->retrieve("GetOldClients", year => "1998");

The former example has the advantage that subclasses can easily override it to do different and interesting things.

OVERRIDE METHODS

The following methods are all class or instance methods. Subclasses are encouraged to override them for more specific functionality.

sqlcmd

This class or instance method returns the name of the XML file used to hold SQL commands. Subclasses have the following options:

    - override the new() method to explicitly set the sqlcmd parameter
      (this is the old style and is deprecated, since it did not work
      as a class method)
    - override the sqlcmd() method to specify the file
      (recommended for unusual file names)
    - let CAM::SQLObject try to find the file

With the latter option, this method will search in the following places for the sqlcmd file (in this order):

    - use the package name, replacing '::' with '/' (e.g. Foo::Bar
      becomes $sqldir/Foo/Bar.xml)
    - use the trailing component of the package name (e.g. Foo::Bar
      becomes $sqldir/Bar.xml)

Subclasses which are happy to use these default file names should not override this method, or change the sqlcmd proprty of any instances. Otherwise, this method should either be overridden by all subclasses (which is the recommended style), or those subclasses should override the new() method to set the sqlcmd field explicitly (which is the previous, now deprecated, style).

Here is a simple example override method:

     sub sqlcmd { return "foobar.xml"; }
keyName

Returns the name of the primary key field (needed for save() and retrieveByKey()). This default method returns the primary key name from the SQL Manager's XML file, or undef.

tableName

Returns the name of the SQL table (needed for fieldNames() and retrieveByKey()). This default method returns the table name from the SQL Manager's XML file, or undef.

updateQueryName

Returns the name of the default query to do record updates in SQL XML file (needed for save()). This default method returns "update".

insertQueryName

Returns the name of the default query to do record inserts in SQL XML file (needed for save()). This default method returns "insert".

deleteQueryName

Returns the name of the default query to do record deletes in SQL XML file. This default method returns "delete".

keygenAlgorithm

Returns the name of the algorithm that the newkey() method uses to generate its keys. This default method returns undef. See newkey() for more details.

keygenData

Returns the ancillary data needed to support the algorithm specified by keygenAlgorithm(). The contents of this data depend on the algorithm chosen. This default method returns undef. See newkey() for more details.

INSTANCE METHODS

get_key

Retrieve the object key.

set_key

Change the object key.

get FIELD

Retrieve a field. This method is intended for internal use only, i.e. from AUTOLOAD or from subclass accessors. An example of the latter:

    sub getFOO_ID {
       my $self = shift;
       return $self->get("FOO_ID") + $ID_offset;
    }
render FIELD

Retrieve a field, with output formatting applied. This method is intended for internal use only, i.e. from AUTOLOAD or from subclass accessors. An example of the latter:

    sub renderFOO_ID {
       my $self = shift;
       return "ID " . &html_escape($self->render("FOO_ID"));
    }
set FIELD, VALUE [FIELD, VALUE, ...]

Assign a field. This method is intended for internal use only, i.e. from AUTOLOAD or from subclass mutators. An example of the latter:

    sub setFOO_ID {
       my $self = shift;
       my $value = shift;
       return $self->set("FOO_ID", $value - $ID_offset);
    }
fill QUERYNAME

Given an object with partially filled fields, run an SQL query that will retrieve more fields. The query should be designed to return just one row. If any command in the query does not return exactly one row, the command will fail.

Example:

    $obj = new ACME::Towel;
    $obj->set_serial_number("0123456789");
    $obj->fill("get_towel_by_sn");
fieldNames
fieldNames TABLENAME

Retrieves an array of the names of the fields in the primary SQL table. If TABLENAME is omitted, this applies to the primary table (this only works if the subclass sets the $self->{tablename} property). This function uses some MySQL specific directives...

(Note: this is a kludge in that it runs the "describe <table>" SQL directly, instead of going through the SQLManager's XML interface)

query

Run the specified query against this object. All bound SQL parameters will be read from this object. This is applicable to both SELECT as well as UPDATE/INSERT queries. While usually called as an instance method, this can be called as a class method if all you are interested in is the side effects of the SQL query instead of the data.

NOTE! This method does not retrieve the results of SELECT statements into the object. If you wish to apply SELECT data to your objects, use either fill() or retrieve().

save

Either update or insert this object into the database. The keyname field must be set so this function can figure out whether to update or insert.

update

Run the default update SQL template. This function is usually just called from the save() function.

insert

Run the default insert SQL template. This function is usually just called from the save() function.

delete

Run the default delete SQL template.

getAllFields
allFields <deprecated>

Returns a hash of all the fields, all retrieved via the accessor functions. "allFields" is the old name for this function, and is here for backward compatibility only.

renderAllFields

Returns a hash of all the fields, retrieved via the render functions.

newkey
newkey KEYGEN, KEYGENDATA

Create a new, unique key. Note that this key is NOT added to the object. This is a wrapper for several different key generation techniques. The following techniques are provided:

keygen = <reference to function>, keygen_data = <anything>

The specified function is called with keygen_data as its argument. This function should return the new key.

keygen = query, keygen_data = 'queryname'

The key generation SQL is part of the SQL command template. <queryname> is run via SQLManager.

keygen = insertcountertable, keygen_data = 'table.keycol,randcol'

Insert into a counter table and retrieve the resulting key. This technique uses a random number to distinguish between concurrent inserts. This technique does not lock the counter table. This technique calls srand() and rand(). Note: this technique assumes that the keycolumn is an autoincrementing column that des not backtrack upon deletes.

keygen = lockcountertable, keygen_data = 'table.keycol'

Lock the counter table, add one to the counter, retrieve the counter, unlock the counter table.

keygen = mysqlcountertable, keygen_data = 'table.keycol'

Add one to the counter and use MySQL's atomic retrieval to return the new value of that counter. This technique does not lock the counter table.

keygen = maxcountertable, keygen_data = 'table.keycol'

Find the maximum value in the specified column, then add one to get the new key. This does not lock, so you may want to lock manually.

AUTHOR

Clotho Advanced Media Inc., cpan@clotho.com

Primary developer: Chris Dolan

SEE ALSO

CAM::SQLManager