Para::Frame::DBIx - Wrapper module for DBI
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.
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;
});
$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.
See /select_list
Stores the result in the session or uses the previously stored result.
See /select_list
Stores the result in the session or uses the previously stored result.
Does the actual select in a fork.
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
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
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
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
Perl: $dbix->delete($statement, @vals)
Executes the $statement, substituting all '?' within with the values.
Exceptions:
dbi : DBI returned error
$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.
$dbix->disconnect()
Disconnect from the DB
$dbix->commit()
Runs the hook Para::Frame/before_db_commit.
Returns the result of DBI/commit
$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
$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);
$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
$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(?)".
$dbix1->equals( $dbix2 )
Returns true is they are the same object.
$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
$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"
$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 })
$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 ...
$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...
$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,
});
$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.
$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
$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
$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.
$dbix->rebless
Checks if there exists a Para::Frame::DBIx::... module matching the connection, for customized interface.
$dbix->table( $name )
Returns: a Para::Frame::DBIx::Table object or undef if not
existing.
Must be implemented for the DB driver.
$dbix->tables()
Returns: A Para::Frame::List object of Para::Frame::DBIx::Table objects.
Must be implemented for the DB driver.
$dbix->bool($value)
Returns: a boolean true/false value fore use in SQL statements for the DB.
$dbix->state
Returns: a Para::Frame::DBIx::State object representing the current state.