Generic::UI::DB::Mysql - Mysql Database Backend Module for Generic::U/p
use Generic::UI::DB::Mysql; # load the module my $db = new Generic::UI::DB::Mysql(%options); # create new object my %users = $db->get_multiple();
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.
new() creates a new Generic::UI::DB::mysql object. All parameters must be provided as a hash.
The following parameters are available:
The name of the database to be used.
Required parameter.
The host on which the dbms is running on.
Optional parameter. If not supplied, "localhost" will be used by default.
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.
The user to connect to the database as.
Optional parameter. If not supplied, "" will be used by default.
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.
"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.
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.
A hash reference containing DBI connection attributes. I.e.:
{ RaiseError => 1, AutoCommit => 0 }
Optional parameter. If not supplied, no attributes will be used.
See DB/a section 'ATTRIBUTES COMMON TO ALL HANDLES' for more on this topic.
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.
params:
returns:
params:
returns:
Method used to do selects.
A returns an array of hashrefs per record.
Parameters in this order:
The select statement which may contain ? for fields in question
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 (=).
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" );
Method used to do inserts.
Returns nothing.
Parameters in this order:
The insert statement which may contain ? for fields in question.
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" } );
Method used to do updates.
Returns nothing.
Parameters in this order:
The update statement which may contain ? for fields in question
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 } );
Method used to do deletes.
Returns nothing.
Parameters in this order:
The delete statement which may contain ? for fields in question
Arrayref of the fields to quote.
Examples:
$db->insert("DELETE FROM blah WHERE idx = ?", [ 1443 ] );
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:
The table to work on.
The order in which entries shall be fetched.
Hash reference of field/value assignments which fields shall be fetched/searched/modified/created and so on.
Get a single entry of the db.
Parameters:
$table, $param
Returns:
Hash.
Get the maximum value of a certain field in a table.
Parameters:
$table, $field
Returns:
Integer.
Get multiple entries of the db.
Parameters:
$table, $order, $param
Returns:
Array of hash references.
Add an entry to the db.
Parameters:
$table, $param
Returns:
Nothing.
Update an existing entry to the db. $param must contain a key idx for this to work.
Parameters:
$table, $param
Returns:
Nothing.
Remove an existing entry from the db.
Parameters:
$table, $param
Returns:
Nothing.
Copyright (C) 2002 T.L..