NAME

Para::Frame::DBIx - Wrapper module for DBI

DESCRIPTION

Para::Frame::DBIx is an optional module. It will not be used unless the application calls it.

The application should initiate the connection and store the object in a global or site variable.

Multipple connections are supported.

On error, an 'dbi' exception is thrown.

The object will blessed into a subclass via /rebless, if availible.

Exported TT functions

/cached_select_list
/select_list
/select_record
/select_possible_record
/select_key

METHODS

new

  Para::Frame::DBIx->new( \%params )

Param connect can be a string or a ref to a list with up to four values. Those values will be passed to DBI/connect. Those will be $data_source, $username, $password and \%attr.

A connect data_source must be given. If username or password is not given or undef, the DBI default will be used.

If the connect attr is not given the paraframe will use the default of: RaiseError => 1, ShowErrorStatement => 1, PrintError => 0, AutoCommit => 0,

If param import_tt_params is true, the TT select functions will be globaly exported, connected to this database. Alternatively, you could export the $dbix object as a TT variable globaly or for a particular site. That would allow you to access all methods. Only one dbix should import_tt_params.

The default for import_tt_params is false.

The param bind_dbh can be used to bind the $dbh object (returned by DBI/connect) to a specific variable, that will be updated after each connection. Example: bind_dbh => \ $MyProj::dbh,

The object uses the hooks Para::Frame/on_fork for reconnecting to the database on forks.

It uses hook Para::Frame/on_error_detect to retrieve error information from exceptions and /rollback the database.

It uses hook Para::Frame/done, Para::Frame/before_switch_req and Para::Frame/gefore_render_output for times to /commit.

The constructor will not connect to the database, since we will probably fork after creation. But you may want to, after you have the object, use Para::Frame/on_startup.

Returns a object that inherits from Para::Frame::DBIx.

Calls init method in the subclass.

Example:

  $MyProj::dbix = Para::Frame::DBIx ->
	new({
	     connect            => ['dbi:Pg:dbname=myproj'],
	     import_tt_params   => 1,
	});
  Para::Frame->add_hook('on_startup', sub
			  {
			      $MyProj::dbix->connect;
			  });

init

  $dbix->init(\%args);

This may be implemented in subclasses.

The param datetime_formatter should hold the module name to use to format dates for this database. The default is based on the DB driver. It's used by /format_datetime. The module will be required during object construction.

datetime_formatter defaults to DateTime::Format::Pg which uses SQL standard ISO 8601 (2003-01-16T23:12:01+0200), which should work for most databases.

cached_select_list

See /select_list

Stores the result in the session or uses the previously stored result.

cached_forked_select_list

See /select_list

Stores the result in the session or uses the previously stored result.

Does the actual select in a fork.

select_list

  Perl: $dbix->select_list($statement, @vals)
  Tmpl: select_list(statement, val1, val2, ... )

Executes the $statement, substituting all '?' within with the values.

The select * part of the statement can be left out.

Template example:

  [% FOREACH select_list('from user where age > ?', agelimit) %]
     <li>[% name ] is [% age %] years old
  [% END %]

Template example:

  [% FOREACH rec IN select_list('from user where age > ?', agelimit) %]
     <li>[% rec.name ] is [% rec.age %] years old
  [% END %]

Returns:

a Para::Frame::List with the hash records

Exceptions:

dbi : DBI returned error

select_record

  Perl: $dbix->select_record($statement, @vals)
  Tmpl: select_record(statement, val1, val2, ... )

Executes the $statement, substituting all '?' within with the values.

The select * part of the statement can be left out.

Template example:

  [% user = select_record('from user where uid = ?', uid) %]
  <p>[% user.name ] is [% user.age %] years old

Returns:

a ref to the first hash record

Exceptions:

dbi : DBI returned error

dbi : no records was found

select_possible_record

  Perl: $dbix->select_possible_record($statement, @vals)
  Tmpl: select_possible_record(statement, val1, val2, ... )

Executes the $statement, substituting all '?' within with the values.

The select * part of the statement can be left out.

Template example:

  [% user = select_record('from user where uid = ?', uid) %]
  [% IF user %]
     <p>[% user.name ] is [% user.age %] years old
  [% END %]

Returns:

a ref to the first hash record or undef if no record was found

Exceptions:

dbi : DBI returned error

select_key

  Perl: $dbix->select_key($field, $statement, @vals)
  Tmpl: select_key(field, statement, val1, val2, ... )

Executes the $statement, substituting all '?' within with the values. $field should be the primary key or have unique values.

The select * part of the statement can be left out.

Template example:

  [% user = select_key('uid', 'from user') %]
  [% FOREACH id = user.keys %]
     <p>$id: [% user.$id.name ] is [% user.$id.age %] years old
  [% END %]

Returns:

The result is indexed on $field. The records are returned as a ref to a indexhash och recordhashes.

Each index will hold the last record those $field holds that value.

Exceptions:

dbi : DBI returned error

delete

  Perl: $dbix->delete($statement, @vals)

Executes the $statement, substituting all '?' within with the values.

Exceptions:

dbi : DBI returned error

connect

  $dbix->connect()

Connects to the database and runs hook Para::Frame/after_db_connect.

The constructor adds ha hook for on_fork that will reconnect to dhe database in the child.

Returns true or throws an 'dbi' exception.

disconnect

  $dbix->disconnect()

Disconnect from the DB

commit

  $dbix->commit()

Runs the hook Para::Frame/before_db_commit.

Returns the result of DBI/commit

rollback

  $dbix->rollback()

Starts by doing the rollbak. Then runs the hook Para::Frame/after_db_rollback.

Calls Para::Frame::Change/reset

Returns the change object

dbh

  $dbix->dbh()

Returns the $dbh object, as returned by DBI/connect.

Example:

  $dbix->dbh->do("update mytable set a=?, b=? where c=?", {}, $a, $b, $c);

get_nextval

  $dbix->get_nextval($seq)

This is done by the SQL query "select nextval(?)" that doesn't work with mysql. Use /get_lastval.

Example:

  my $id = $dbix->get_nextval('person_id_sequence');

Returns:

The id

Exceptions:

dbi : Failed to get nextval

get_lastval

  $dbix->get_lastval

  $dbix->get_lastval($sequence)

For MySQL, this retrieves the AUTOINCREMENT value from mysql_insertid that corresponds to mysql_insert_id().

If given a sequence, it retrieves the value with "select currval(?)".

equals

  $dbix1->equals( $dbix2 )

Returns true is they are the same object.

parse_datetime

  $dbix->parse_datetime( $time, $class )

This uses the datetime_formatter property of $dbix (as set on construction).

$class defaults to Para::Frame::Time. Can be used to set the class to a suitable subclass to Para::Frame::Time.

Should be a litle more efficiant than using Para::Frame::Time/get directly.

Returns: a Para::Frame::Time object

format_datetime

  $dbix->format_datetime( $time )

This uses the datetime_formatter property of $dbix (as set on construction) for returning the time to a format suitable for the database.

It will parse most formats using Para::Frame::Time/get. Especially if $time already is a Para::Frame::Time object.

Returns:

The formatted string

Exceptions:

validation : "Time format '$time' not recognized"

update

  $dbix->update( $table, \%set, \%where )

$table is the name of the table as a string

\%set is a hashref of key/value pairs where the key is the field name and value is the new value of the field. The value will be formatted by /format_value with type undef.

\%where is a hashref of key/value pars where the key is the field name and value is the value that field should have.

Returns: The number of rows updated

Exceptions:

dbi : ... full explanation ...

Example:

  $dbix->update( 'my_users', { email => $new_email }, { user_id => $u->id })

insert

  $dbix->insert( $table, \%rec )
  $dbix->insert( \%params )

Insert a row in a table in $dbix

  Params are:
  table = name of table
  rec   = hashref of field/value pairs
  types = definition of field types

The values will be automaticly formatted for the database. types, if existing, helps in this formatting. /format_value is used for this.

Returns number of rows inserted.

Example:

  $dbix->insert({
	table => 'person',
	rec   =>
        {
          id      => 12,
          name    => 'Gandalf'
          updated => now(),
        },
	types =>
        {
          id      => 'string',
          name    => 'string',
          updated => 'boolean',
        },
  });

Exceptions:

dbi : ... full explanation ...

insert_wrapper

  $dbix->insert_wrapper( \%params )

High level for adding a record.

  Params are:
  rec    = hashref of name/value pairs for fields to update
  map    = hashref of translation map for interface to fieldname
  parser = hashref of fieldname/coderef for parsing values
  types  = hashref of fieldname/type
  table  = name of table
  unless_exists = listref of fields to check before inserting
  return_field  = what field value to return

Example:

  our %FIELDMAP =
  (
    id   => 'pid',                 # The DB field is named pid
    name => 'username',            # The DB field is named username
  );

  our %FIELDTYPES =
  (
    updated => 'date',             # Parse this as an date and format
  );

  our %FIELDPARSER =               # The coderef returns the object
  (                                # and the object has a id() method
    project => sub{ MyProj::Project->get(shift) },
  );

  ...

  my $rec =
  {
    id      => 12,
    name    => 'Gandalf',
    updated => now(),              # From Para::Frame::Time
    project => 'Destroy the Ring', # The db field holds the project id
  };
  my $newperson = MyProj::Person->insert($rec);

  ...

  sub insert
  {
    my( $class, $rec ) = @_;
    return MyProj::Person->get_by_id(
	       $Para::dbix->insert_wrapper({
	           rec    => $rec,
		   map    => \%FIELDMAP,
		   types  => \%FIELDTYPE,
                   parser => \%FIELDPARSER,
		   table  => 'person',
		   unless_exists => ['name'], # Avoid duplicates
		   return_field => 'id',      # used by get_by_id()
	       }));
  }

rec is the field/value pairs. Each field name may be translated by map. Defaults to an empty record.

map is used for the case there the name of the object property doesn't match the DB field name. We may want use opne naming scheme for field names and another naming scheme for object properties. One or the other may change over time.

parser is used for special translation of the given value to the value that should be inserted in the database. One good usage for this is for foreign keys there the field holds the key of a record in another table. The parser should return the related object. The /format_value method will be used to get the actual id by calling the objects method id.

types helps /format_value.

table is the name of the table. This is the only param that must be given.

unless_exists is a field name or a ref to a list of field names. If a record is found with those fields having the values from the $rec, no new record will be created.

return_field will return the value of this field if defined. If unless_exists find an existing record, the value of thats records field will be returned. May be used as in the example, for returning the id.

Returns the number of records inserted, unless return_field is used. If unless_exists finds an existing record, the number of records inserted is 0.

The usage of %FIELDTYPES, et al, in the example, demonstrates how each table should have each own Perl class set up in its own file as a module. The fields could be defined globaly for the class and used in each method dealing with the DB.

Validation of the data has not yet been integrated...

update_wrapper

  $dbix->update_wrapper( \%params )

High level for updating a record. This will compare two records to detect if anything has changed.

  Params are:
  rec_new = hashref of name/value pairs for fields to update
  rec_old = hashref of the record to be updated
  map     = hashref of translation map for interface to fieldname
  parser  = hashref of fieldname/coderef for parsing values
  types   = hashref of fieldname/type
  table   = name of table
  key     = hashref of field/value pairs to use as the record key
  on_update = hashref of field/value pairs to be set on update
  copy_data = obj to be updated with formatted values if any change

This is similar to /insert_wrapper but most of the job is done by /save_record.

Returns the number of fields changed.

copy_data will take each field from the translated fields from $rec_new and their corresponding formatted value, and place them in the given object or hashref. The point is to update the object with the given changes.

Example:

  my $changes = $dbix->update_wrapper({
    rec_new         => $rec,
    rec_old         => $me->get( $person->id ),
    types           => \%FIELDTYPE,
    table           => 'person',
    key =>
    {
        id => $person->id,
    },
    on_update =>
    {
        updated => now(),
    },
    fields_to_check => [values %FIELDMAP],
    map             => \%FIELDMAP,
    copy_data       => $person,
    });

format_value_list

  $dbix->format_value_list(@value_list)

Returns a list of formatted values

Plain scalars are not modified

DateTime objects are formatted

Objects with an id method uses the id

Other objects are stringyfied.

format_value

  $dbix->format_value( $type, $value )

  $dbix->format_value( undef, $value )

If type is undef, uses /format_value_list.

Returns the formatted value

Handled types are string, boolean and date.

Exceptions:

validation : Type $type not handled

validation : Value $valstr not a $type

save_record

  $dbix->save_record( \%params )

High level for saving a record.

  Params are:
  rec_new = hashref of name/value pairs for fields to update
  rec_old = hashref of the record to be updated
  table   = name of table
  on_update = hashref of field/value pairs to be set on update
  key     = hashref of field/value pairs to use as the record key
  keyval  = alternative to give all in key param
  fields_to_check = which fields to check

Returns the number of changes.

If any changes are made, determined by formatting the values and comparing rec_new with rec_old, those fields are updated for the record in the table.

The types handled are string, integer, float, boolean, date and email.

Exceptions:

action : Type $type not recoginzed

report_error

  $dbix->report_error( \@values, @params )

If $@ is true, throws a dbi exception, adding the calles sub name and the list of values. Those should be the values given to dbh for the SQL query.

If the connection may have been lost, tries to reestablish it and recalls the method with statement and values.

rebless

  $dbix->rebless

Checks if there exists a Para::Frame::DBIx::... module matching the connection, for customized interface.

table

  $dbix->table( $name )

Returns: a Para::Frame::DBIx::Table object or undef if not existing.

Must be implemented for the DB driver.

tables

  $dbix->tables()

Returns: A Para::Frame::List object of Para::Frame::DBIx::Table objects.

Must be implemented for the DB driver.

bool

  $dbix->bool($value)

Returns: a boolean true/false value fore use in SQL statements for the DB.

state

  $dbix->state

Returns: a Para::Frame::DBIx::State object representing the current state.

on_connect

SEE ALSO

Para::Frame, DBI