LEGAL
#===========================================================================
Copyright (C) 2008 by Nik Ogura. All rights reserved.
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
Bug reports and comments to nik.ogura@gmail.com.
#===========================================================================
NAME
CGI::Lazy::RecordSet
SYNOPSIS
use CGI::Lazy;
our $q = CGI::Lazy->new({
tmplDir => "/templates",
jsDir => "/js",
plugins => {
mod_perl => {
PerlHandler => "ModPerl::Registry",
saveOnCleanup => 1,
},
ajax => 1,
dbh => {
dbDatasource => "dbi:mysql:somedatabase:localhost",
dbUser => "dbuser",
dbPasswd => "letmein",
dbArgs => {"RaiseError" => 1},
},
session => {
sessionTable => 'SessionData',
sessionCookie => 'frobnostication',
saveOnDestroy => 1,
expires => '+15m',
},
},
});my $recordset = $q->db->recordset({
table => 'detail', #table where records are coming from
fieldlist => [
{name => 'detail.ID', #name of field
hidden => 1}, #do not display to screen. Recordset cant do any operations on fields that are not a part of itself, however all fields need not be displayed
{name => 'invoiceid',
hidden => 1},
{name => 'prodCode',
label => 'Product Code',
validator => {rules => ['/\d+/'], msg => 'number only, and is required'}}, #validator for filed. msg is not implemented at present.
{name => 'quantity',
label => 'Quantity',
validator => {rules => ['/\d+/'], msg => 'number only, and is required'},
outputMask => "%.1f", #formatting to data applied on output to browser
},
{name => 'unitPrice',
label => 'Unit Price' ,
validator => {rules => ['/\d+/'], msg => 'number only, and is required'},
inputMask => "%.1f", #formatting to data applied on input to database
},
{name => 'productGross',
label => 'Product Gross' ,
validator => {rules => ['/\d+/'], msg => 'number only, and is required'}},
{name => 'prodCodeLookup.description',
label => 'Product Description',
readOnly => 1 }, #readOnly values display to the screen, but never get written to the db
],
basewhere => '', #baseline where clause for the select query. this is used in all selects, even if 'where is set later.
joins => [ #table joins
{type => 'inner', table => 'prodCodeLookup', field1 => 'prodCode', field2 => 'prodCodeLookup.ID',},
],
orderby => 'detail.ID', #order by clause for select wuery
primarykey => 'detail.ID', #primary key for recordset. This value is looked for for all updates and deletes
});my $thing = $q->ajax->dataset({
id => 'detailBlock',
type => 'multi',
template => "UsbInternalPOCDetailBlock.tmpl",
lookups => {
prodcodeLookup => {
sql => 'select ID, description from prodCodeLookup',
preload => 1,
orderby => ['ID'],
output => 'hash',
primarykey => 'ID',
},
},
recordset => $recordset,
});DESCRIPTION
CGI::Lazy::DB::Recordset is a container object for handling a set of records pulled out of a database. The big difference between using the Recordset object and just using a standard query is the Recordset, with it's defined internal structure allows for automated transformations to the data. The object builds the queries on the fly, and remembers where it got all the data in question, so it can edit it and put it back. Much of this functionality is seen in the Ajax::Dataset object, for which the Recordset object was originally written.
METHODS
basewhere ()
Returns the basewhere string for the recordset.
createSelect ()
Creates the Select statement out of the structure of the Recordset.
data ()
Returns data reference from Recordset. Will always be present, but will be empty until select() is called.
delete ( data )
Deletes records with primary keys in data.
data
Hashref who's keys are the primary keys of the records to be deleted.
displayOnly ( field )
Returns true if field has displayOnly key set to a true value.
field
name of field to test
db ()
Returns reference to CGI::Lazy::DB object
fieldlist ()
Returns array ref of field list with which recordset was built.
handle ( field )
Returns reference used as handle to value of field.
field
Name of field who's handle to retrieve
hidden ( field )
Returns true if field in question has been set to hidden
field
name of field to test
inputMask ( field )
Returns inputMask for field of given name, if one has been set.
field
Name of field to test.
insert ( data, vars )
Inserts data modified by vars into table accessed by Recordset.
data
Hashref of data to be inserted. Each key corresponds to a row of data
vars
modifiers for data to be inserted
insertadditonal
Returns reference of additional information to be inserted with each new record
insertdefaults
Returns reference of default values to be inserted with each new record
joins
Returns either list or arrayref of joins for Recordset
label ( field )
Returns label set for field, or name of field if no label has been specified
field
field name to test.
new ( vars )
Constructor
vars
Hashref with construction properties.
Minimum:
{
table=>$table,
where => $where,
orderby => $order by,
primarykey => $keyfield,
fieldlist => [{name => 'fieldname', label => 'some field'}]
}table
string. name of table
where
string. where clause
orderby
string. orderby clause
primarykey
field name of primary key for table
fieldlist
array ref. list of fields with their attributes
noLabel ( field )
Returns true if field in question has been set with the noLabel option
field
Name of field to test.
orderby ( sql )
returns or sets the order by clause
sql
sql string
outMask ( field )
Returns outputMask set for field.
field
Name of field to test.
multipleFiled ( field )
Returns true if field in question has multipleField option set (i.e. it's supposed to turn up on the mulitple record screen)
field
Name of field to test.
multipleFieldList
Returns arrayref or array of fields flagged to show up on multiple records page
multipleFieldLabels
Returns arrayref or array of labels for fields chosen to appear on multiple record pages.
primarykey ( fieldname )
returns or sets the primary key for the object
fieldname
The name of the field in the database
q ()
returns reference to CGI::Lazy object.
readfunc ( field )
Returns readfunction set for field in question, if any.
field
field to be tested.
readOnly ( field )
Returns true if field in question has been set to readOnly.
field
field to be tested
select ()
Runs select query based on $self->createSelect, fills $self->{_data}, and returns same.
If where clause is set up with bind placeholders, and select is called with bind variables as arguments, it will bind them and be safe from injection. if called with straight up variables from the net, it will be vulnerable. As you will.
table( tablename )
gets or sets the table queriedtable
string.update ( data, vars )
Updates fields in data, modified by vars
data
Hashref of data. Each key is the primary key off a record, and the value is a hash whose keys are fieldnames and values are field contents.
vars
modifiers to data
updateadditional ()
Returns updateadditional information for recordset.
updatedefaults ()
Returns updatedefaults information for recordset
validator ( field )
Returns validator hashref for field.
field
Name of field to be tested.
verify ( value )
Untaints and returns true only if the given string is a field included in the database
Due to the dynamic nature of the Ajax objects, it's not possible to bind all variables coming in from the web. This is not ideal. However, we can guard from sql injection attacks by refusing to include strings that contain characters beyond A-Za-z0-9_-, and verify that the field in question is part of your recordset. If your database structure has special characters in it's table names, go out back and hit yourself with a brick. Shame on you.
visibleFieldLabels ()
Returns array or arrayref of labels for non-hidden fields.
visibleFields
Returns array or arrayref of field names that are not hidden
where($where)
gets or sets the where clause$where
string.writefunc ( field )
Returns writefunc set for field.
field
field to be tested