Help-Site Computer Manuals
Software
Hardware
Programming
Networking
  Algorithms & Data Structures   Programming Languages   Revision Control
  Protocols
  Cameras   Computers   Displays   Keyboards & Mice   Motherboards   Networking   Printers & Scanners   Storage
  Windows   Linux & Unix   Mac

Postgres::Handler
Builds upon DBD::Pg for advanced CGI web apps

Postgres::Handler - Builds upon DBD::Pg for advanced CGI web apps


NAME

Postgres::Handler - Builds upon DBD::Pg for advanced CGI web apps


DESCRIPTION

Postgres::Handler builds upon the foundation set by DBI and DBD::Pg to create a superset of methods for tying together some of the basic interface concepts of DB management when used in a web server environment. Postgres::Handler is meant to build upon the strengths of DBD::Pg and DBI and add common usability features for a variety of Internet applications.

Postgres::Handler encapsulates error message handling, information message handling, simple caching of requests through a complete iteration of a server CGI request. You will also find some key elements that hook the CGI class to the DBI class to simplify data IO to & from web forms and dynamic pages.


SYNOPSIS


 # Instantiate Object

 #

 use Postgres::Handler;

 my $DB = Postgres::Handler->new(dbname=>'products',dbuser=>'postgres',dbpass=>'pgpassword');

 

 # Retrieve Data & List Records

 #

 $DB->PrepLEX('SELECT * FROM products');

 while ($item=$DB->GetRecord()) {

     print "$item->{PROD_ID}\t$item->{PROD_TITLE}\t$item->{PROD_QTY}\n";

 }

 

 # Add / Update Record based on CGI Form

 # assuming objCGI is an instatiated CGI object

 # if the CGI param 'prod_id' is set we update

 # if it is not set we add

 #

 my %cgimap;

 foreach ('prod_id','prod_title','prod_qty') { $cgimap{$_} = $_; }

 $DB->AddUpdate( CGI=>$objCGI     , CGIKEY=>'prod_id', 

                 TABLE=>'products', DBKEY=>'prod_id',

                 hrCGIMAP=>\%cgimap

                );


REQUIRES


 CGI::Carp

 CGI::Util

 Class::Struct

 DBD::Pg 1.43 or greater (fixes a bug when fetching Postgres varchar[] array data)

 DBI


EXPORT

None by default.


DATA ACCESS METHODS

new()


 Create a new Postgres::Handler object.
Parameters

 dbname => name of the database to connect to

 dbuser => postgres user

 dbpass => password for that user

data()


 Get/set the data hash - this is where data fields are stored

 for the active record.

dbh()


 Returns the database handle for the DB connection.

dbpass()


 Get/set postgres user's password.

dbname()


 Get/set database name.

 Simple string name of the database.

dbuser()


 Get/set postgres username.

sth()


 Returns the statement handle for the active record selection.

 

=cut

#==============================================================================

our $VERSION = 2.2;# Set our version
our $BUILD= '2006-04-13 11:22';# BUILD

struct ( dbname => '$', dbuser => '$', dbpass => '$', dbh => '$', sth => '$', data => '%' );

#==============================================================================


PUBLIC METHODS

AddUpdate()


 Adds a new record or updates an existing record in the database

 depending on whether or not a specific CGI parameter has been set.

 Useful for processing a posted form that contains form fields

 that match data fields.   Pre-populate the form field that contains

 the database key field and an update occurs.  Set it to blank and

 a new record is added.

 Your database key field should have a default value that is unique

 and should be set as type 'PRIMARY KEY'.  We always use serial primary 

 key to auto-increment our keys when adding new records.
example

 --

 -- Table: xyz

 --

 

 CREATE TABLE xyz (

    xyz_pkid    serial       primary key,

         xyz_update  timestamp    default now(),

         xyz_ipadd   char(32)

         );

 If a key is provided but is doesn't match anything in the existing

 data then the update fails, UNLESS... CHECKKEY=> 1 in which case it

 will attempt to add the record.

 Your CGI->DB key hash reference should look something like this:

 %mymap = ( tablefld_name => 'form_name', tablefld_ssn => 'htmlform_ssn' );

 And is passed with a simple \%mymap as the hrCGIMAP parameter to this function.

 -or-

 Even better, name your CGI form fields the same thing as your Postgres DB field

 names.  Then you can skip the map altogether and just provide the CGISTART

 variable.  All fields that start with the the CGISTART string will be mapped.

 Want to map every field?  Set CGISTART = '.'.
Parameters (Required)

 CGI       => a CGI object from the CGI:: module

 DBKEY     => the name of the key field within the table

              defaults to Postgres::Handler Object Property <table>!PGHkeyfld

              must be provided 

                                  - or -

                             the <table>!PGHkeyfld option must have

              been setup when creating a new Postgres::Handler object

 TABLE     => the name of the table to play with

 CGISTART or hrCGIMAP must be set (see below)
Parameters (Optional)

 CGISTART  => map all CGI object fields starting with this string

              into equivalently named database fields

                                  only used when hrCGIMAP is not set

 CGIKEY    => the CGI parameter name that stores the data key

              defaults to DBKEY

 CHECKKEY  => set to 1 to perform ADD if the DBKEY is not found in the

              database.

 DBSTAMP   => the name of the timestamp field within the table

              defaults to Postgres::Handler Object Property <table>!PGHtimestamp

 DONTSTAMP => set to 1 to stop timestamping

              timestamp field must be set

 hrCGIMAP  => a reference to a hash that contains CGI params as keys and

              DB field names as values

 MD5      => the name of the md5 encrypted field within the table

              defaults to Postgres::Handler Object Property <table>!PGHmd5

 REQUIRED  => array reference pointing to array that holds list of CGI

              params that must contain data

 VERBOSE   => set to 1 to set lastinfo() = full command string

              otherwise returns 'INSERT' or 'UPDATE' on succesful execution

 BOOLEANS  => array reference pointing to the array that holds the list

              of database field booleans that we want to force to false

                                  if not set by the equivalently named CGI field

 RTNSEQ    => set to a sequence name and AddUpdate will return the value of this

              sequence for the newly added record.  Useful for getting keys back

                                  from new records.
Action

 Either adds or updates a record in the specified table.

 Record is added if CGI data key [1] is blank or if CHECKKEY is set

 and the value of the key is not already in the database.

 Record is updated if CGI data key [2] contains a value.
Returns

 1 for success, get message with lastinfo()

 0 for failure, get message with lasterror()

DoLE()


 Do DBH Command and log any errors to the log file.

        [0] = SQL command

        [1] = Die on error

        [2] = return error on 0 records affected

        [3] = quiet mode (don't log via carp)

 Set the object 'errortype' data element to 'simple' for short error messages.

 i.e.

 $self->data('errortype') = 'simple';
Returns

 1 for success

 0 for failure, get message with lasterror

Field()


 Retreive a field from the specified table.
Parameters (required)

 DATA     => Which data item to return, must be of form "table!field"

 KEY      => The table key to lookup in the database

               Used to determine if our current record is still valid.

               Also used as default for WHERE, key value is searched for 

               in the PGHkeyfld that has been set for the Postgres::Handler object.
Parameters (optional)

 WHERE   => Use this where clause to select the record instead of the key

 FORCE   => Force Reload
Returns

 The value of the field.

 Returns 0 and lasterror() is set to a value if an error occurs

               lasterror() is blank if there was no error
Example

 my $objPGDATA = new Postgres::Handler::HTML ('mytable!PGHkeyfld' => 'id');

 my $lookupID = '01123';

 my $data = $objPGDATA->Field(DATA=>'mytable!prod_title', KEY=>$lookupID);

 my $lookupSKU = 'SKU-MYITEM-LG';

 my $data = $objPGDATA->Field(DATA=>'mytable!prod_title', WHERE=>"sku=$lookupSKU");

GetRecord()


 Retrieves the record in a hash reference with uppercase field names.

 rtype not set or set to 'HASHREF',

 Calls fetchrow_hashref('NAME_uc') from the specified SQL statement.

 rtype not set or set to 'ARRAY',

 Calls fetchrow_array() from the specified SQL statement.

 rtype not set or set to 'ITEM',

 Calls fetchrow() from the specified SQL statement.
Parameters

 [0] or -name     select from the named statement handle,

                  if not set defaults to the last active statement handle

 [1] or -rtype    'HASHREF' (default) or 'ARRAY' or 'ITEM' - type of structure to return data in

 [2] or -finish   set to '1' to close the named statement handle after returning the data
Returns

 the hashref or array or scaler on success

 0 for failure, get message with lasterror

lasterror()


 Retrieve the latest error produced by a Postgres::Handler object.
Returns

 The error message

lastinfo()


 Retrieve the latest info message produced by a Postgres::Handler object.
Returns

 The info message

nsth()


 Retrieve a named statement handle
Returns

 The handle, as requested.

PrepLE()


 Prepare an SQL statement and returns the statement handle, log errors if any.
Parameters (positional or named)

        [0] or -cmd     - required -statement

        [1] or -exec    - execute flag (PREPLE) or die flag (PREPLEX)

        [2] or -die             - die flag     (PREPLE) or null     (PREPLEX)

        [3] or -param   - single parameter passed to execute 

        [4] or -name    - store the statement handle under this name
Returns

 1 for success

PrepLEX()


 Same as PrepLE but also executes the SQL statement
Parameters (positional or named)

        [0] or -cmd     - required -statement

        [1] or -die             - die flag     (PREPLE) or null     (PREPLEX)

        [2] or -param   - single parameter passed to execute 

        [3] or -name    - store the statement handle under this name
Returns

 1 for success

Quote()


 Quote a parameter for SQL processing via

 the DBI::quote() function

 Sets the data handle if necessary.


SEMI-PUBLIC METHODS


 Using these methods without understanding the implications of playing with their

 values can wreak havoc on the code.  Use with caution...

SetDH()


 Internal function to set data handles

 Returns Data Handle

 If you don't want the postgres username and password

 littering your perl code, create a subclass that

 overrides SetDH with DB specific connection info.

SetMethodParms()


 Allows for either ordered or positional parameters in

 a method call AND allows the method to be called as EITHER

 an instantiated object OR as an direct class call.
Parameters

 [0] - self, the instantiated object

 [1] - the class we are looking to instantiate if necessary

 [2] - reference to hash that will get our named parameters

 [3] - an array of the names of named parameters 

       IN THE ORDER that the positional parameters are expected to appear

 [4] - extra parameters, positional or otherwise
Action

 Populates the hash refered to in the first param with keys & values
Returns

 An object of type class, newly instantiated if necessary.
Example

 sub MyMethod() {

        my $self = shift;

        my %options;

                $self = SetMethodParms($self,'MYCLASS::SUBCLASS', \%options, [PARM1,PARM2,PARM3], @_ );

        print $options{PARM1} if ($options{PARM2} ne '');

        print $options{PARM3};

 }

CGIMap()


 Prepare a hash reference for mapping CGI parms to DB fields

 typically used with AddUpdate() from Postgres::Handler.
Parameters

 hrCGIMAP       - reference to hash that contains the map

 CGI                    - the CGI object

 CGISTART       - map all fields starting with this text

 CGIKEY                 - the cgi key field

 BOOLEANS       - address to list of boolean fields
Example

 @boolist = qw(form_field1 form_field2);

 $item->CGIMap(CGI => $objCGI, hrCGIMAP=>\%cgimap, CGISTART=>'cont_', CGIKEY=>'cont_id', BOOLEANS=>\@boolist);


NOTES


 Some methods allow for parameters to be passed in via both positional and named formats.

 If you decide to use named parameters with these "bi-modal" methods you must prefix the

 parameter with a hyphen.

 # Positional Example

 #

 use Postgres::Handler;

 my $DB = Postgres::Handler->new(dbname=>'products',dbuser=>'postgres',dbpass=>'pgpassword');

 $DB->PrepLEX('SELECT * FROM products');

 # Named Example

 #

 use Postgres::Handler;

 my $DB = Postgres::Handler->new(dbname=>'products',dbuser=>'postgres',dbpass=>'pgpassword');

 $DB->PrepLEX(  -cmd    =>      'SELECT * FROM products'        );

 

=head1 EXAMPLES

 # Instantiate Object

 #

 use Postgres::Handler;

 my $DB = Postgres::Handler->new(dbname=>'products',dbuser=>'postgres',dbpass=>'pgpassword');

 # Retrieve Data & List Records

 #

 $DB->PrepLEX('SELECT * FROM products');

 while ($item=$DB->GetRecord()) {

        print $item->{PROD_ID}\t$item->{PROD_TITLE}\t$item->{PROD_QTY}\n";

 }

 # Add / Update Record based on CGI Form

 # assuming objCGI is an instatiated CGI object

 # if the CGI param 'prod_id' is set we update

 # if it is not set we add

 #

 my %cgimap;

 foreach ('prod_id','prod_title','prod_qty') { $cgimap{$_} = $_; }

 $DB->AddUpdate( CGI=>$objCGI     , CGIKEY=>'prod_id', 

                 TABLE=>'products', DBKEY=>'prod_id',

                 hrCGIMAP=>\%cgimap

               );


AUTHOR


 Lance Cleveland, Advanced Internet Technology Consultant

 Contact info@charlestonsw.com for more info.


ABOUT CSA


 Charleston Software Associates (CSA) is and advanced internet technology

 consulting firm based in Charleston South Carolina.   We provide custom

 software, database, and consulting services for small to mid-sized

 businesses.

 For more information, or to schedule a consult, visit our website at

 www.CharlestonSW.com


CONTRIBUTIONS


 Like the script and want to contribute?  

 You can send payments via credit card or bank transfer using

 PayPal and sending money to our info@charlestonsw.com PayPal address.


COPYRIGHT


 (c) 2005, Charleston Software Associates

 This script is covered by the GNU GENERAL PUBLIC LICENSE.

 View the license at http://www.charlestonsw.com/community/gpl.txt

 or at http://www.gnu.org/copyleft/gpl.html


REVISION HISTORY


 v2.2 - Apr 2006

      Fixed problem with SetDH database handle management

 v2.1 - Mar 2006

      Added RTNSEQ feature to AddUpdate so we can get back the key of a newly added record

 v2.0 - Feb 2006

      Moved CGI::Carp outside of the package to prevent perl -w warnings

 v1.9 - Feb 2006

      Update Field() to prevent SIGV error when WHERE clause causes error on statement

                Field() now returns 0 + lasterror() set to value if failed execute

                            returns fldval + lasterror() is blank if execution OK

 v1.8 - Jan 2006

      Bug fix on PrepLE and PrepLEX for perl -w compatability

                Added DoLE param to return error status (0) if the command affects 0 records '0E0'

                Added DoLE param to keep quiet on errors (do not log to syslog via carp)

                Documentation updates

 v1.5 - Nov 2005

                Fixed @BOOLEANS on AddUpdate to force 'f' setting instead of NULL if blank or 0

 v1.5 - Oct 2005

                Fixed return value error on AddUpdate()

 v1.4 - Aug 2005

      Minor patches

 v1.3 - Jul 17 2005

      Minor patches

                Now requires DBD::Pg version 1.43 or greater

 v1.2 - Jun 10 2005

      GetRecord() mods, added 'ITEM'

                test file fix in distribution

                created yml file for added requisites on CPAN

 v1.1 - Jun 9 2005

      pod updates

                Field() cache bug fix

                GetRecord() expanded, added finish option

                Moved from root "PGHandler" namespace to better-suited "Postgres::Handler"

 v0.9 - May 2 2005

      pod updates

                AddUpdate() updated, CGIKEY optional - defaults to DBKEY

                AddUpdate() updated, BOOLEANS feature added

                GetRecord() updated, added check for sth active before executing

                Field() fixed hr cache bug and data bug and trap non-set hr issue

 v0.8 - Apr 26 2005

      Fixed GetRecord() (again) - needed to check $DBI::errstr not $err

 v0.7 - Apr 25 2005

      Added error check on ->Field to ensure hashref returned from getrecord

      Added CGIMAP method

      Invoke CGIMAP from within AddUpdate if missing map

      Fixed GetRecord Return system

 v0.5 - Apr/2005

      Added DBI error trap on DoLE function

      Added named statement handles for multiple/nested PrepLE(X) capability

      Added VERBOSE mode to AddUpdate

      Added NAME to retrieved named statements via GetRecord

      Updated FIELD to use named statement handles

 v0.4 - Apr/2005

                Fixed some stuff

 v0.3 - Apr/2005

      Added REQUIRED optional parameter to AddUpdate

      Improved documentation

      Quoted DBKEY on add/update to handle non-numeric keys

 v0.2 - Mar/2005 - 

      Added error messages to object

      Fixed issues with Class:Struct and the object properties

      Updated AddUpdate to use named parameters (hash) for clarity

 v0.1 - Dec/2004

      Initial private release
Programminig
Wy
Wy
yW
Wy
Programming
Wy
Wy
Wy
Wy