Linux Certif

Toute la documentation sur la certification Linux LPI

SnowPg.3pm

NAME

SnowPg - A simple wrapper class for Pg.

SYNOPSIS

use SnowPg;

Connecting to the database

# Connect to the database and don't kill the connection even when $db goes # out of scope. If a connection is already found open matching supplied # connection string, it will be used (with a rollback if necessary)

         $db = SnowPg->new(connect_string => 'dbname=abc user=123',
                 persist => 0,           # don't re-use connections 
                 debug => 1,             # send debug info to STDERR
                 debugfile => '/var/log/db.log',         # append debug info to db.log
         );
 
 

Performing queries

         $db->query( q[SELECT %q AS Quoted], 'quoted escaped string' );
 
 

         # %q    => quoted and escaped
         #       eg. bl'ah -> 'bl\'ah'
         #           undef -> NULL
 
 

         # %l    => quoted list of strings
         #       eg. [ 'one', 'two' ]  -> 'one','two'
         #           [ @list ] ->  'abc','def'
         #               etc..
 
 

         # %u    => unquoted and unescaped (e.g. for numerical)
         #       eg. 12345 -> 12345
         #           undef -> NULL
 
 

         # %n    => boolean evaluated to NULL or NOT NULL
         #       eg. 0 or empty set or undef -> NULL
         #           1 -> NOT NULL
 
 

         # %b    => boolean evaluated to 't' or 'f'
         #       eg. 1 -> 't'
         #           0 -> 'f'
         #           undef -> NULL
 
 

         # %x    => bytea escaping (see below)
 
 

Fetching query results

# Number of result rows

         $n = $db->ntuples();
 
 

# Get a single row of results

         if (%result = $db->fetchrow) {
                 $quoted = $result{quoted};
         }
 
 

# Get multiple rows of results

         # Spelling it out
                 while (%res = $db->fetchrow()) {
                         foreach $k (keys %res) {
                                 print "$k = $res{$k}\n";
                         }
                 }
 
 

         # Getting a single column of rows into a list
 
 

                 @values = $db->fetchlist('fieldname');
 
 

         # Getting all rows into a list of hashes
 
 

                 @rows = $db->fetchlist();
                 # access example:
                 print "Result is " . $rows[5]->{columnname};
 
 

         # Getting two columns into a hash
 
 

                 %values = $db->fetchhash('fieldname1' => 'fieldname2');
 
 

         # Getting multiple columns into a hash using any field as a key
 
 

                 %values = $db->fetchhash('fieldname1');
                 # access example:
                 foreach $key (keys %values) {
                         $field2 = $values{$key}->{fieldname2};
                         $field3 = $values{$key}->{fieldname3};
                         print "Row: $key has field2=$field2 and field3=$field3\n";
                 }
                 # NOTE: specifying a non-unique field will cause older rows
                 # to be replaced with new rows in the order they are
                 # received from the server!
 
 

Transactions

         $db->begin();
 
 

         $db->commit();
 
 

         $db->rollback();
         # Rollback gets called automatically if object is destroyed. 
         # (Connection may be persistent)
 
 

Byte array handling (BYTEA)

         Simply send SnowPG the byte array as if it was a normal variable using
         query() and %q, eg:
 
 

         $db->query( q[INSERT INTO blobtable (blobcol) VALUES (%x)], $bytes );
 
 

         To decode postgres reponse, use:
 
 

         $bytes = SnowPg->unbytea( $postgres_output );
 
 

PostgreSQL signalling

         # (see Pg for more information)
         # To get the socket fd:
 
 

                 $sock = $db->socket();
 
 

         # To get notifications:
 
 

                 @notes = $db->notifies();
 
 

         # To consume input
 
 

                 $db->consume();
 
 

CONTACT DETAILS

Author: Andrew Snow, andrew@modulus.org