The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
CGI-AppToolkit-0.051
River stage zero No dependents
++
/ CGI::AppToolkit::Data::SQLObject

NAME

CGI::AppToolkit::Data::SQLObject - A SQL data source component of CGI::AppToolkit that inherits from CGI::AppToolkit::Data::Object

DESCRIPTION

CGI::AppToolkit::Data::SQLObjects provide a common interface to multiple data sources. This interface is an extension of the CGI::AppToolkit::Data::Object interface. Providing a SQL data source requires creating an object in the CGI::AppToolkit::Data:: namespace that inherits from CGI::AppToolkit::Data::SQLObject.

There is generally a one-to-one correlation between the CGI::AppToolkit::Data::SQLObjects and the SQL tables.

You do not use this module or it's descendants in your code directly, but instead call CGI::AppToolkit->data() to load it for you.

WHAT'S SO GREAT ABOUT SQLObject?

CGI::AppToolkit::Data::SQLObject provides overriden fetch(), fetch_one(), store(), update(), and delete() functions that handle most of the common tasks for you, and allow you to simply inherit them and provide specialized SQL when you need to.

METHODS TO USE FROM YOUR BASE SCRIPT

fetch(ARGUMENTS)
fetch_one(ARGUMENTS)

This method takes a hash (a hash reference or key => value pairs that can easily be placed into a hash) as arguments, makes a SELECT SQL statement based upon those arguments, and returns either an arrayref of hashrefs (called as fetch()) or a hashref (called as fetch_one()). In either case, each hashref represents one row returned from the SELECT statement, with the column names as the keys, like returned from DBI's $sth->fetchall_arrayref({}). (The column names may be mapped to other names. See the key_map variable later in this document.)

The keys of the hash you provide are used as column names, and the values are used literally. For example:

  $kit->data('person')->fetch_one(name => 'Rob');
  # also: $kit->data('person')->fetch_one({name => 'Rob'});

Will automatically generate the following SQL for you (assuming you don't override it):

  select * from person where name = ?

And then calls DBI like this (metaphorically speaking, of course):

  $sth->execute('Rob');

If you send an arrayref as the 'value', then the first item is assumed to be a comparison operator, and the rest of the items are what's to be compared to. For example:

  $kit->data('person')->fetch_one(id => ['<', 12]);

Generates the following SQL:

  select * from person where id < ?

And then calls DBI like this (again, metaphorically):

  $sth->execute(12);
store(ARGUMENTS)

This method takes the arguments provided as a hash or hash reference and makes an INSERT or UPDATE statement based upon them. The keys are used as column names (after column name mapping, see the key_map variable later in this document), and the values are taken literally as column values. If an index (a.k.a. primary key) column is persent and non-undef, then an UPDATE is done in a similar fashion. store() returns a CGI::AppToolkit::Data::Object::Error upon failure, otherwise it conventionally returns the data that was passed to it, possibly altered (e.g.: an unique ID was assigned, etc.).

update(ARGUMENTS)

This method is the same as store(), with the exception that an UPDATE statement is always generated reguarless of the index column.

delete(ARGUMENTS)

Similar to fetch, except a DELETE SQL statement is generated. The arguments are used in the same manner. Always returns '1' for now.

HOW TO EXTEND SQLObject FOR USE

To use CGI::AppToolkit::Data::SQLObject you have to create a module that inherits from CGI::AppToolkit::Data::SQLObject and overrides a few methods. You must override init() at least.

Please see CGI::AppToolkit::Data::TestSQLObject for example code.

METHODS TO USE

get_kit()

(Inherited from CGI::AppToolkit::Data::Object.)

Returns the creating CGI::AppToolkit object. This can be used to retrieve required data.

  my $dbi = $self->get_kit()->get_dbi();

In particular, CGI::AppToolkit->get_dbi() retrieves the DBI object stored from a call to CGI::AppToolkit->connect().

METHODS TO OVERRIDE

CGI::AppToolkit::Data::SQLObject has a few methods you can override. You must override init().

init(SELF)

You must override this method. In this method, you must provide certain variables using various accessor methods, like this for the variable table:

        $self->set_table('test_shebang');

Note that in all cases, case sensitivity of the values are that of the DBD you are using.

You MUST provide these variables (the type of value is shown after the name):

table - String

Sets the relation database table name accessed primarily by this object.

index - String

Provides the name of the relation database table that is the unique identifier.

all_columns - Array reference

Provides a list of the names of every column in the table.

See also the optional valiables all_insert_columns, default_insert_columns, all_update_columns, and default_update_columns. If you provide both all_insert_columns and all_update_columns, this variable will be ignored.

These variables are optional:

all_insert_columns - Array reference

Provides a list of the names of every column in the table used in SQL INSERTs.

  $self->set_all_insert_columns([qw/address zip password active verified html/]);
default_insert_columns - Hash reference

Provides a list of keys (column names) and absolute values of columns in the table used in SQL INSERTs. The columns mentioned must not be in all_insert_columns as well.

  $self->set_default_insert_columns({'start' => 'now()'});
all_update_columns - Array reference

Provides a list of the names of every column in the table used in SQL UPDATEs.

default_update_columns - Hash reference

Provides a list of keys (column names) and absolute values of columns in the table used in SQL UPDATEs. The columns mentioned must not be in all_update_columns as well.

key_map - Hash reference

Provides a hash of 'fake column name' to 'real column name' mappings. fetch(), fetch_one(), store(), update(), and delete() use this before generating or using any SQL. This can be used to create easy-to-use interface names for ugly table column names. For example:

  $self->set_key_map({id => 'TBL_PERSON_INDEX', name => 'TBL_PERSON_NAME', address => 'TBL_PERSON_ADDRESSX');

Column names that are not referenced by this map are used as is, so you don't have to provide a key mapping for every column.

get_db_statement_local(SELF, STATEMENT_NAME)

This method is used to provide SQL to the rest of the SQLObject methods. This method is not required to be overriden, but you most likely want to.

It is passed a reference to the SQLObject object and a statement name, and returns an array ref containing a DBD statement handle (as returned by DBD->prepare(...)) and an arrayref of keys to supply to DBD->execute() to replace the ('?') placeholders in the SQL. For example, get_db_statement_local() could return like:

  return [$db->prepare('select * from people where id=? and name=?'), [qw/id name/]];

Please see DBI or the DBD for your database engine for more information about prepare() and placeholders.

The statement name is generated as follows:

  1. If get_db_statement_local() is called from store(), the name is set to 'update' or 'insert', depending on the type of storing requested, and the rest of the steps are skipped.

  2. The arguments passed to the function (such as fetch) are sorted alphanumerically. The arguments that begin with a '-' are ignored.

  3. If the value of the argument is an array reference, the first item is assumed to be a comparison operator, and the rest the items to be compared. For example: id => ['<', 20] or id => ['in', 'this', 'that']. In this case, a forward slash ('/'), the comparison operator, and the number of items to be 'compared to' are added to the name.

  4. The names are combined with pipes ('|').

  5. If it's being called from delete() then 'delete:' is prepended to the name.

So, when calling:

  $kit->data('name')->fetch(name => ['in', 'frank', 'george']);

Then ($self->get_db_statement_local('name/in2') would be called by fetch(). Similarly,

  $kit->data('name')->delete(name => 'john', id => ['<', '12'], address => '1234 Main');

Then ($self->get_db_statement_local('delete:address|id/<1|name') would be called by delete().

An example get_db_statement_local method:

  sub get_db_statement_local {
    my $self = shift;
    my $name = shift;
    
    my $db = $self->get_kit->get_dbi();
    
    if ($name eq 'now') {
      return [$db->prepare('select now() as now'), []];
      
    } elsif ($name eq 'date/<1') {
      return [$db->prepare('select * from people, events where event.person = people.id and event.start < ?'), [qw/date/]];
    }
    
    undef
  }
prefetch(SELF, ARGS)

This method is called from fetch(), and is used to override the default SELECT statement.

Only use this method if get_db_statement_local() cannot be used, for some reason.

You must return an array reference and a hash reference. The first array reference contains a DBI statement handle (prepare()d SQL SELECT statement), and the key names of the ARGS to use, in the order to use them. The second returned value, a hash reference, contains the values to use. This can often be the ARGS that were passed.

Return undef to continue as if prefetch() were not defined for the given ARGS.

An example implementation of prefetch):

  sub prefetch {
    my $self = shift;
    my $args = shift;
    
    my $db = $self->get_kit->get_dbi();
    
    if ($args->{'id'}) {
      return [$db->prepare('select id, name, password where id=? order by id desc'), [qw/id/]], $args;
    }
    
    undef
  }
predelete(SELF, ARGS)

This method is basically the same as prefetch(), except called from delete(). It is used to override the UPDATE statement. It's called the same and has the same return style.

Only use this method if get_db_statement_local() cannot be used, for some reason.

prestore(SELF, ARGS, ERROR OBJECT)

This method is called during the data error checking portion of the store() call. It is passed the arguments that were passed to store(), and an empty CGI::AppToolkit::Data::Object::Error object. This method allows you to check the ARGS and alter them or use the error object to stop the store process and pass an error back to the caller of store().

preinsert(SELF, ARGS, ERROR OBJECT)

This method is called immediately before an INSERT statment is executed. It is passed the arguments that were passed to store() (after prestore() has been called, possibly altering them), and an empty CGI::AppToolkit::Data::Object::Error object. This method allows you to check the ARGS and alter them or use the error object to stop the store process and pass an error back to the caller of store().

postinsert(SELF, ARGS, STATEMENT HANDLE)

This method is called after the INSERT statement has ben executed. It is passed the arguments that were passed to store() (after prestore() and preinsert() have been called, possibly altering them), and the executed DBI/DBD statement handle. This method allows you to inspect the ARGS and statement handle and possibly alter the ARGS before they are returned to the caller of store().

preupdate(SELF, ARGS, OLD DATA, ERROR OBJECT)

This method is called immediately before an UPDATE statment is executed. It is passed the arguments that were passed to store() or update() (after prestore() has been called, possibly altering them), a hashef of the old data (fetched immediately before calling preupdate()), and an empty CGI::AppToolkit::Data::Object::Error object. This method allows you to check the ARGS and the old data and alter the ARGS or use the error object to stop the store process and pass an error back to the caller of store() or update().

postupdate(SELF, ARGS, OLD DATA, STATEMENT HANDLE)

This method is called after the UPDATE statement has ben executed. It is passed the arguments that were passed to store() or update() (after prestore() and preinsert() have been called, possibly altering them), a hashef of the old data (fetched immediately before calling preupdate()), and the executed DBI/DBD statement handle. This method allows you to inspect the ARGS and statement handle and possibly alter the ARGS before they are returned to the caller of store() or update().

TODO

The primary to-do for now is to provide automatic range/data checking facilities before INSERT/UPDATE statements are made. This way you can catch potentially fatal INSERTS before hand and return to the interface gracefully. This is where data checking should occur, after all.

AUTHOR

Copyright 2002 Robert Giseburt (rob@heavyhosting.net). All rights reserved.

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

Please visit http://www.heavyhosting.net/AppToolkit/ for complete documentation.