Class
Class
Mode
Enabled
| CleanCode Perl Libraries |
| Home | Perl | Java | PowerShell | C# | SQL | Index | Tools | Download | What's New |
| Multi-Lingual Library | Maintainability | ||||||||||||
|
Perl | |
Java | |
JavaScript | |
Certified Class |
|
Testable Class |
|
Standalone Mode |
|
Diagnostic Enabled |
DBI::Helper - Provides DBI convenience methods.
use DBI::Helper;
$dbHelper = DBI::Helper->new($cgiApp, $cgiAppHelper, "username");
my $dsn = $dbHelper->getMysqlDSN($db, $server);
$result = $dbh->selectall_arrayref($stmt);
$dbHelper->checkErr();
foreach my $rowRef (@$result) {
my $fields = $dbHelper->row2hash(\@fieldNames, $rowRef);
...
}
$timeval = $dbHelper->timestamp2time($create_date);
$dbHelper->setAutoParamList($USER_TABLE);
$dbHelper->setRequiredFlags($USER_TABLE);
$stmt = $dbHelper->buildUpdateQuery($USER_TABLE);
$stmt = $dbHelper->buildInsertQuery(
$AUTH_TABLE, [$AUTH_TABLE_ID],[$newUserID]);
$result = $dbHelper->do($stmt);
@result = $dbHelper->getDBvalue($tableName, join(",",@fields),
$dbHelper->getWhereClause($addlWhereClause));
$valueClause = " VALUES(".$dbHelper->quoteList(\@valueList).");";
@valueList = $dbHelper->getDBcolumn($rangeTableName, $idname);
$columns = $dbHelper->getColumnInfo($tableName);
$listref = $dbHelper->getDBListForTemplate($lookupTable);
$listref = $dbHelper->getDBMultiListForTemplate("adtype", "reservations");
if (my $invalidEmpties=$dbHelper->validateBlanks($USER_TABLE)) ...
$diag->print($dbHelper->diagQueryResult(
$tableName, $stmt, $errString || $rows));
Perl5.005, CGI, Time::Local, Data::Handy, Data::Diagnostic
Provides a variety of convenience methods for use with the standard DBI module.
Current version of this class.
PACKAGE->new(cgiApp, cgiAppHelper, idField)
Creates a DBI::Helper object.
cgiApp - instance of CGI::Application
cgiAppHelper - instance of CGI::Application::Helper
idField - name of the DB field that has the user ID
a newly created object
OBJ->param(name, value)
OBJ->param(name)
Sets or gets the value of a parameter stored in the current instance.
name - string; name of parameter
value - optional; value to assign to parameter
current or updated value of the named parameter
OBJ->getMysqlDSN(db, server)
Returns the DSN string to connect to a MySQL database
db - string; name of database
server - string; name of host server
DSN string to connect to a database
OBJ->timestamp2time()
Convert from DB string (yyyymmddhhmmss or yyyy-mm-dd hh:mm:ss) to standard time value (int).
integer representing standard time (in seconds since epoch), or 0 if input string doesn't match expected length.
OBJ->clientClause()
Returns an SQL where clause to select by the current user ID.
SQL where clause to select the current user.
OBJ->getWhereClause(addlWhereClause)
Get an SQL where clause that selects by the current user ID at a minimum. You may optionally provide and additional clause that further restricts the selection.
addlWhereClause - optional; string; SQL where clause
SQL where clause
OBJ->quoteList(listRef)
Prepares a list of values for use in an SQL statement. This invokes the DBI quote method on an entire list, formatting for use in a value clause of an SQL statement.
listRef - reference to list of values
comma-separated string with each element properly quoted.
OBJ->row2hash(nameList, rowRef)
Match names to values in a hash. Excess names will have a value of undef. Excess values will be ignored.
nameList - reference to list of keys
rowRef - reference to list of values
constructed hash
OBJ->getDBvalue(table, column, keyColumn, keyValue)
OBJ->getDBvalue(table, column, whereClause)
OBJ->getDBvalue(table, column)
OBJ->getDBvalue(stmt)
Returns a single row result from a DB table as an array. If keyColumn and keyValue are specified, that is the only selector criteria. Alternately, one may pass in a where clause for more flexibility. If no selector is provided, defaults to selecting the current user ID from the table. For ultimate flexibility, one may simply pass in a complete SQL statement and that will be executed "as is".
table - string; table name to query
column - string; field name specifying column to return
keyColumn - optional; string; field name in selector
keyValue - optional; string; value for field in selector
whereClause - SQL where clause
stmt - string; SQL statement to execute
array containing values from selected row in table (or from stmt)
OBJ->getDBcolumn(table, column, keyColumn, keyValue)
OBJ->getDBcolumn(table, column, whereClause)
OBJ->getDBcolumn(table, column)
Returns a column from a DB table as an array. If keyColumn and keyValue are specified, that is the only selector criteria. Alternately, one may pass in a where clause for more flexibility. If no selector is provided, defaults to selecting the current user ID from the table.
table - string; table name to query
column - string; field name specifying column to return
keyColumn - optional; string; field name in selector
keyValue - optional; string; value for field in selector
whereClause - SQL where clause
array containing selected values from column in table
OBJ->getColumnInfo(tableName)
Returns a hash reference with details of all columns for the specified table. Keys of the hash are field names from the table. Values of the hash are themselves hashes where each key is a column name from the result table of an SQL describe command.
tableName - string; target DB table name to examine
hash reference with column info
OBJ->checkErr()
If a database error has occurred, the error method of the parent CGI::Application is invoked to, for example, write the error to a log file. Additionally, setSimpleMsg from the CGI::Application::Helper is called, to give the user a message on-screen. If the user is an admin, the specific error will be displayed; otherwise, just a generic problem message. This method should be called after each database access.
If a DB error occurs, returns an error string; otherwise, an empty string.
OBJ->do(stmt, tableName)
OBJ->do(stmt)
A wrapper around the standard DBH do method that adds the statement and its results to the diagnostic output trace.
stmt - string; SQL statement to execute
tableName - optional; string; if present, also adds a list of all fields of that table to the diagnostic output trace
If a DB error occurs, returns an error string; otherwise, an empty string.
OBJ->diagQueryResult(tableName, stmt, errString)
Adds additional diagnostic info on screen for database queries. $errString is either an error msg OR a count of rows successfully updated.
tableName - optional; string; target DB table name to examine
stmt - string; statement that was executed
errString - string; either a count of rows operated on by the query, or an error message.
HTML fragment for insertion into diagnostic output
OBJ->setAutoParamList(tableName, addlWhereClause)
OBJ->setAutoParamList(tableName)
OBJ->setAutoParamList()
Automatically fill template fields from a specified DB table or CGI list. Get a list of parameters from the current template that are designated to be fillable "automatically" (i.e. by direct copying). Each such parameter is recognized by the prefix $AUTO_PREFIX in the template. After stripping that prefix, the name must match the cgi/db field.
With a tableName specified, values come from that table in the database. The particular database row is selected by the user ID and optionally an additional where clause that you may provide. With no arguments, values come from CGI inputs.
tableName - optional; string; target DB table name to examine
addlWhereClause - optional; string; SQL where clause to narrow down the DB selection
OBJ->getDBListForTemplate(lookupTable, tableName, fieldName, addlWhereClause)
OBJ->getDBListForTemplate(lookupTable, tableName, fieldName)
OBJ->getDBListForTemplate(lookupTable, fieldName)
OBJ->getDBListForTemplate(lookupTable)
Create a parameter list for feeding to current HTML template from the constant values in a lookup table. The DB table must have an index column named "${lookupTable}_id" and an item column called "name" (literally "name"!). Optionally, one may specify an active value, i.e. the one to be pre-selected on the HTML form. The active value may come from either the database or the CGI input, depending on the method signature you use, as explained next.
With all parameters, returns a hash with an active value marked, the active value coming from $tableName[$client_id&$addlWhereClause][$fieldName]. If the addlWhereClause is omitted, returns a hash with an active value marked, the active value coming from $tableName[$client_id][$fieldName] If the tableName is also omitted, returns a hash with an active value marked, the active value coming from $cgi[$fieldName]. Finally, if only the lookupTable is specified, returns a hash of the lookup table with no active value marked.
Example: Say we have a currency table with fields currency_id, abbr, and name. Tuples in this table might be (1, EUR, Euro), (2, USD, US Dollar), (3, GBP, British pound), etc. getDBListForTemplate("currency") would return:
{ 'currencyIndex' => 1, 'currencyValue' => 'Euro' },
{ 'currencyIndex' => 2, 'currencyValue' => 'US Dollar' },
{ 'currencyIndex' => 3, 'currencyValue' => 'British pound' },
etc.
That is, an enumerated constant list suitable for passing to HTML::Template. The receiving portion of the template might appear as:
<select name="currency_type">
<TMPL_LOOP currency>
<option value="<TMPL_VAR currencyIndex>"
<TMPL_IF active> selected="selected"</TMPL_IF>><TMPL_VAR currencyValue>
</option>
</TMPL_LOOP currency>
</select>If, on the other hand, we ask it to also assign an active value via something like getDBListForTemplate("currency","currency_type"), we might get:
{ 'currencyIndex' => 1, 'currencyValue' => 'Euro' },
{ 'currencyIndex' => 2, 'currencyValue' => 'US Dollar', active => 1 },
{ 'currencyIndex' => 3, 'currencyValue' => 'British pound' },
etc.
indicating that the dollar was the currently selected value. This active value may come from either a database table or from CGI input. The above example gets its value from CGI input, specifically from the currency_type field, which might appear as:
<select name="currency_type">
<option value="1">Euro</option>
<option value="2" selected="selected">US Dollar</option>
<option value="3">British Pound</option>
etc.lookupTable - string; DB table name to retrieve constant id/value pairs
tableName - optional; string; target DB table name to examine, from which an id is retrieved that must match one of the ids found from the lookupTable. If not specified, CGI input is used instead.
fieldName - optional; string; field containing the id to lookup in the lookupTable to set as the active value.
addlWhereClause - optional; string; SQL where clause to isolate a row in tableName
List of hashes for feeding to the current HTML template; hash keys for each hash are lookupTableIndex, lookupTableValue, and optionally active.
OBJ->getDBMultiListForTemplate(lookupTable, rangeTableName)
OBJ->getDBMultiListForTemplate(lookupTable)
Create a parameter list for feeding to current HTML template from the constant values in a lookup table. This method differs from the getDBListForTemplate method, by marking multiple active values. If rangeTableName is specified, the active values are gleaned from that DB table. Say for example we had a mediaChoice table with fields (user_id, media_id, ...). Assume our user_id of interest was 41392, and the table contained the tuples (41392, 1, ...) and (41392, 3, ...). Let us also assume that we have a corresponding lookup table called media with field names media_id and name, containing the tuples (1, TV), (2, radio), (3, web), (4, newspaper). We would then use getDBMultiListForTemplate("media", "mediaChoice") to generate this:
{ 'mediaIndex' => 1, 'mediaValue' => 'TV', active => 1 },
{ 'mediaIndex' => 2, 'mediaValue' => 'radio' },
{ 'mediaIndex' => 3, 'mediaValue' => 'web', active => 1 },
{ 'mediaIndex' => 4, 'mediaValue' => 'newspaper' },
which may be fed to an instance of HTML::Template containing something like:
<TMPL_LOOP media>
<input type="checkbox" name="media" id="choice<TMPL_VAR mediaIndex>"
<TMPL_IF active>checked="checked"</TMPL_IF>
value="<TMPL_VAR mediaIndex>"><TMPL_VAR mediaValue></input>
</TMPL_LOOP media>If however, we were getting initial user input rather than getting previously stored input from the database, our source might be a web page like:
<input type="checkbox" name="media" id="choice1" checked="checked"
value="1" />TV</input>
<input type="checkbox" name="media" id="choice2"
value="2" />radio</input>
<input type="checkbox" name="media" id="choice3" checked="checked"
value="3" />web</input>
<input type="checkbox" name="media" id="choice4"
value="4" />newspaper</input>We then execute getDBMultiListForTemplate("media") to get the CGI input; the HTML checkboxes above will be evaluated for the active members, and cross-referenced to the constant enumerated list obtained from the database media table given earlier, generated the same list to feed back to an instance of HTML::Template.
lookupTable - string; DB tablename of constant list of applicable values
rangeTableName - optional; string; DB tablename from which active state of values may be determined; if omitted, active states come from CGI inputs
reference to array with active values marked
OBJ->setRequiredFlags(tableName)
Sets a flag in the current HTML template for each field that exists in the specified $tableName, where the "NOT NULL" clause is present. This typically is used to show an asterisk or other indicator of required fields.
tableName - string; target DB table name to examine
OBJ->validateBlanks(tableName,...)
Validates that all required fields (as specified in the database via "NOT NULL" or its absence) are, in fact, non-empty for one or more tables. If you provide multiple tables, all CGI fields are checked against all tables, so it is important that your database tables are consistent (i.e. one saying "not null" and another omitting the clause for the same field yields an indeterminate result).
tableName - string; target DB table name to examine
If problems are found, an error string indicating fields in violation; otherwise, an empty string.
OBJ->buildUpdateQuery(tableName, whereClause, addlNameList, addlValueList)
OBJ->buildUpdateQuery(tableName, whereClause)
OBJ->buildUpdateQuery(tableName)
Builds an update query for $tableName based on current form input. Each field in the specified table that exists on the form will be filled and the database record updated. The $whereClause allows you to specify which record(s) to update. You may also add additional values to the update via by adding the field name to @$addlNameList and its corresponding value to the same position in the @$addlValueList.
tableName - string; target DB table name for the constructed query
whereClause - optional; SQL where clause; if omitted, uses a default where clause to select by the current user ID.
addlNameList - optional; reference to additional field name list to include in the insert statement
addlValueList - optional; corresponding value list reference
Constructed SQL statement
OBJ->buildInsertQuery(tableName, addlNameList, addlValueList)
OBJ->buildInsertQuery(tableName)
Build an insert query for the specified table from CGI inputs where field names correspond. May optionally supply additional fields via list references to also be included in the insert statement. NB: Length of @$addlNameList must equal length of @$addlValueList.
tableName - string; target DB table name for the constructed query
addlNameList - optional; reference to additional field name list to include in the insert statement
addlValueList - optional; corresponding value list reference
Constructed SQL statement
N/A
None
Michael Sorens
$Revision: 8 $ $Date: 2006-12-19 21:13:43 -0800 (Tue, 19 Dec 2006) $
CleanCode 0.9.05
Hey! The above document had some coding errors, which are explained below:
=back doesn't take any parameters, but you said =back -- end of CLASS VARIABLES section
=back doesn't take any parameters, but you said =back -- end of CONSTRUCTOR section
=back doesn't take any parameters, but you said =back -- end of METHOD section
| Home | Perl | Java | PowerShell | C# | SQL | Index | Tools | Download | What's New |
| CleanCode Perl Libraries | Copyright © 2001-2013 Michael Sorens - Revised 2013.06.30 |