NAME

Generic::UI::DB::Mysql - Mysql Database Backend Module for Generic::UI

SYNOPSIS

 use Generic::UI::DB::Mysql;                                  # load the module
 my $db = new Generic::UI::DB::Mysql(%options);               # create new object
 my %users = $db->get_multiple(); 

DESCRIPTION

This module is an internal perl module used by both the CGI and the server programs to simplify database access and to provide a higher level view on the database. It also hides the database internals. This makes it much easier to extend the database structure or to migrate the whole application to another DBMS, for example oracle or the like.

All parameters or hash keys correspond to table fields defined in the database structures. So you need to know about it anyway. And if you change existing fields in the database structure without providing code to be backward compatible you will get much trouble. You've been warned.

METHODS

new()

new() creates a new Generic::UI::DB::mysql object. All parameters must be provided as a hash.

The following parameters are available:

database => 'database name'

The name of the database to be used.

Required parameter.

host => 'host name'

The host on which the dbms is running on.

Optional parameter. If not supplied, "localhost" will be used by default.

port => tcp port number

The tcp port number on which the dbms listens to incoming connection requests.

Optional parameter. If not supplied, the DBMS specific default port will be used.

user => 'username'

The user to connect to the database as.

Optional parameter. If not supplied, "" will be used by default.

password => 'password'

The cleartext password to authenticate the username on the database.

Optional parameter. If not supplied, "" will be used by default. It will be "" too, if no username was supplied.

debug => 'on' | 'off'

"on" turns debugging on. This causes the module to print various stuff. You can provide a filehandle to use to print the debugging output, see next.

Optional parameter. By default, debugging is turned off.

debugfd => FILEHANDLE

The filehandle to print the debugging output to. You can open a file, or just point STDERR to it.

Generic::UI::DB::Mysql will not close the filehandle. You must do this yourself.

Optional parameter. If debugging is turned off (see above), it will be ignored. If debugging is turned on and this parameter was not supplied, STDOUT will be used.

attribs => \%attributes

A hash reference containing DBI connection attributes. I.e.:

 { RaiseError => 1, AutoCommit => 0 } 

Optional parameter. If not supplied, no attributes will be used.

See DBI? section 'ATTRIBUTES COMMON TO ALL HANDLES' for more on this topic.

transactions => 1 | 0

if your database supports transactions (i.e. compiled with BerkeleyDB support), then set this parameter to "1". The default is "0" - no transaction support.

In the latter case a call to the rollback() method will do nothing.

rollback()

   params: 
  returns: 

commit()

   params: 
  returns: 

select()

Method used to do selects.

A returns an array of hashrefs per record.

Parameters in this order:

$sql

The select statement which may contain ? for fields in question

$params

Arrayref of the fields to quote, or hashref of fieldname => value to create the select from. The latter case will construct a select with AND linked fields and the supplied $sql must end after the table name. If values of the hash param contains the % character, then a like clause will be created rather than an equal (=).

$order

sql clause to order the select, i.e. "ORDER by username"

Examples:

 $db->select("SELECT * FROM blah WHERE idx = ?", [ 123 ]);
 $db->select("SELECT * FROM blah", { idx => 4, username => "ha%" }, "ORDER by username" ); 

insert()

Method used to do inserts.

Returns nothing.

Parameters in this order:

$sql

The insert statement which may contain ? for fields in question.

$params

If it is a hashref, then the keys will be suggested as fieldnames and the corresponding values as its values. if it is an arrayref, it will simply supplied to execute().

Examples:

 $db->insert("INSERT INTO blah SET idx = ?, user = ?", [ 1443, "scip" ] );
 $db->insert("INSERT INTO blah", { idx => 1443, user => "scip" } ); 

update()

Method used to do updates.

Returns nothing.

Parameters in this order:

$sql

The update statement which may contain ? for fields in question

$params

If it is a hashref, then the keys will be suggested as fieldnames and the corresponding values as its values. if it is an arrayref, it will simply supplied to execute().

Examples:

 $db->update("UPDATE blah SET password = ? WHERE idx = ?", [ "glubbibulgah", 1443 ] );
 $db->update("UPDATE blah", { password => "glubbibulgah", idx => 1443 } ); 

delete()

Method used to do deletes.

Returns nothing.

Parameters in this order:

$sql

The delete statement which may contain ? for fields in question

$params

Arrayref of the fields to quote.

Examples:

 $db->insert("DELETE FROM blah WHERE idx = ?", [ 1443 ] ); 

WRAPPER METHODS

Those methods are for easier access to the database. Using these wrappers avoids using SQL statements at all.

Since the parameters always have the same meaning they are all described here.

Possible parameters:

$table

The table to work on.

$order

The order in which entries shall be fetched.

$param

Hash reference of field/value assignments which fields shall be fetched/searched/modified/created and so on.

get_single()

Get a single entry of the db.

Parameters:

 $table, $param 

Returns:

Hash.

get_max()

Get the maximum value of a certain field in a table.

Parameters:

 $table, $field 

Returns:

Integer.

get_multiple()

Get multiple entries of the db.

Parameters:

 $table, $order, $param 

Returns:

Array of hash references.

add_entry()

Add an entry to the db.

Parameters:

 $table, $param 

Returns:

Nothing.

modify_entry()

Update an existing entry to the db. $param must contain a key idx for this to work.

Parameters:

 $table, $param 

Returns:

Nothing.

remove_entry()

Remove an existing entry from the db.

Parameters:

 $table, $param 

Returns:

Nothing.

COPYRIGHT

Copyright (C) 2002 T.L..