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

                        mysqlAuto       => 1,

                        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

                        insertdefaults  => {

                                unitprice        => {

                                        value => 'lots',

                                        handle  => $ref,

                                },

                                invoiceid         => {

                                        sql     => 'select something.nextval from dual',
                                        
                                        primarykey      => 1,

                                },

                        },

        });


        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 its 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 CGI::Lazy::Widget::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.

Options:

        {

                table           => $table, 

                mysqlAuto       => 1,

                basewhere       => $where,

                orderby         => $order by, 

                primarykey      => $keyfield, 

                fieldlist       => [

                        {

                                name            => 'field1', 

                                label           => 'some field',

                                validator       => { rules => ['/\d+/'},

                                outputMask      => "%1.f",

                                inputMask       => "%1.f",

                        },

                        {

                                name            => 'field2', 

                                label           => 'some other field',

                                outputMask      => "%1.f",

                                readOnly        => 1,

                        },

                        {

                                name            => 'post_date',

                                label           => 'Post Date',

                                readfunc        => "to_char(post_date, 'YYYY-MM-DD')",

                                writefunc       => "to_date(?, 'YYYY-MM-DD')",

                        },

                        {

                                name            => 'username',

                                label           => 'Username',

                        },

                        {       

                                name            => 'passwd',
                                
                                label           => 'Password',

                                passwd          => {

                                                        userField       => 'username',

                                },

                        },
                                

                ],

                insertdefaults  => {

                        field1 => {

                                value   => 'some value',

                        },

                        field2 => {

                                sql     => ['select foo from bar where ?', $bind1],
                        },

                        field3  => {

                                sql     => ['select foo.nextvar from dual'],
                                handle  => $ref,
                        }

                updatedefaults  => {

                        field1 => {

                                value   => 'some value',

                        },

                        field2 => {

                                sql     => ['select foo from bar where ?', $bind1],
                        },

                        field3  => {

                                sql     => ['select foo.nextvar from dual'],
                                handle  => $ref,
                        }

                }


        }

table

string. name of table

mysqlAuto

set flag if primary key for this recordset is created by mysql auto_increment column. If set, composite widgets will automatically make this value available to member widgets on insert.

basewhere

Sql string. This forms the base where clause for all selects. This string should not contain any variables from the outside world, as it is NOT bound, and could be used in sql injection attacks were cgi parameters used here. If you want to use cgi params, see the 'where' method which is intended to be dynamic, and can take binds.

orderby

string. orderby clause

primarykey

field name of primary key for table

fieldlist

array ref. list of fields with their attributes

Fieldlist Options

        name            => name of field

        hidden          => if true, field is never displayed, but is selected

        readOnly        => if true, field is displayed, but never written

        label           => displayed label of field.  if blank label defaults to fieldname

        noLabel         => if true, no label is displayed for field.

        oututMask       => sprintf string that transforms data on the way out of db to screen

        inputMask       => sprintf string that transforms data on the way into db

        validator       => rules for field validation

                        rules   =>  arrayref of tests to check.  if all return true, field is valid. Currently only supports regexes. 

                        message => canned error message to display onerror.  (not currently used by anything, but you can grab this in your own code and display it)

        readfunc        => database function to perform on read

        writefunc       => db function to perform on write      

        insertdefaults  => default values inserted on insert
                
                value   => value to insert

                sql     => array ref.  first value is sql to generate value to insert, rest is binds

                handle  => reference whose referrent will contain whatever value is set into db.  Useful for later use in cgi.

        updatedefaults  => default values updated on update
                
                value   => value to insert

                sql     => array ref. first value is sql to generate value to insert, rest is binds

                handle  => reference whose referrent will contain whatever value is set into db.  Useful for later use in cgi.

        webcontrol      => type of input displayed to browser.  Defaults to text input field.
                
                type    => select, checkbox, text.  Text is the default.

                value   => for a checkbox, only a single value can be specified

                values  => arrayref, or hashref.  If arrayref, both displayed value and value will be the same.  If hashref, key will be label, value will be value.  

                sql     => [$query, @binds]     You can specify a query that will build the values, but it expected to return 2 values per row, the first being the label, the second being the value.

                notNull => 1   Set this for selects if you don't want the first item of the select to be blank

        passwd          => This field is password field used with the authn plugin

                userField       => name of field in recordset that will contain the username

updatedefaults

means for setting default values on any update

insertdefaults

means for setting default values on any insert

noLabel ( field )

Returns true if field in question has been set with the noLabel option

field

Name of field to test. =head2 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.

mysqlAuto ()

Returns true if recordset was created with mysqlAuto => 1 .

passwd ( field )

Returns passwd hashref from fieldlist config.

fieldname

name of the field in question

primarykey ( fieldname )

returns or sets the primary key for the object

fieldname

The name of the field in the database

primarykeyhandle ()

Returns scalar ref to primary key of record being processed. Used by Composite widget to get primary key of parent. Only really useful for datasets in single mode. Use with multiple row datasets at your own risk.

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 queried

table

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 Widget 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 its 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

webcontrol (field)

Returns webcontrol hashref for field.

field

Name of field.

where($sql, binds)

Gets or sets the where clause. If called with a single argument, argument is assumed to be sql string for the where clause. If called with multiple args, first element is sql string, everything else is bind values. When called without arguments, it returns whatever's been set. This could be a single string, or an array ref, depending on how it was last called.

sql

string.

binds

list of bind vars

writefunc ( field )

Returns writefunc set for field.

field

field to be tested