From: robert_hiltibidal_at_cms08405@ccmailgw.state.il.us
Message-Id: <9906239301.AA930165316@ccmailgw.state.il.us>
Date: Wed, 23 Jun 99 14:14:50 -0600
To: <pgsql-interfaces@postgresql.org>
Subject: Perl module
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit


     Afternoon,
     
     I'm still looking for a simpler way to take multiple line items and 
     put them into a simple delimitted array. I came up with the DataEase 
     subroutine in the module below. It uses the getvalue function. It 
     ain't the prettiest but... it get the job done.
     
     rob.pm is a personal library module I use for NT and Linux. Just 
     simple grind 'em out kinda stuff. Should work where ever perl and 
     postgres reside...
     
     -Rob 
     
     
     # Modified for LINUX
     # By Robert Hiltibidal
     # 14-JUN-99
     # Y2K Project
     
     # library package for web usage
     
     # Made by Robert Hiltibidal
     # 9-JUN-99
     # Y2K Project
     # Perl for NT
     
     # NOTE: The LINUX version uses the PostgreSQl Perl Interface.
     #       Comment out the use if the interface is not available.
     
     # Variables Shared:
     # $title                        Web Page Title
     # %FORM                         Associative Array of FORM variables
     # $cpw                          Encrypted Password for HTACCESS Files
     # $user                         Remote User
     # $ip                           Remote IP
     # $date                         Date in MsSql, PostgreSQL format
     # $query                        Incoming Data Query
     # $status                       Query Status
     # @set                          Array of SELECT Query
     #                               delimitter is set to "|"
     # $dbname                       Defined in main program
     
     # Variables used but not intended for share:
     # I don't use the package statement. Makes it really easy to share
     # variables across different files. Yet that strength could cause 
     # problems. If you get really unexpected results, check for variables
     # that may be already in use.
     
     
     # Subroutines used:
     # &grabit                       Grabs the POST methods
     #                               Not enabled for MIME yet
     # &html_header                  Creates html header for http
     # &html_trailer                 Ditto for the footer
     # &MakeNewPassword              PERL Encryption algorythm for HTACCESS 
     # &DataEase                     Stores SELECT query into @set
     # &ExecQuery                    Executing Query 
     #                               May be INSERT, UPDATE, CREATE etc,
     
     # DataEase Query usage:
     # I set the query in the main program and then call DataEase or 
     ExecQuery. 
     # Once the DataEase routine returns I have a delimitted array called
     set that 
     # has  the information needed. Frequently I'll rename that array to
     # something more logical and undef @set.  
     # Example:
     # $dbname="y2k";
     # $query = "Select username,password,email from users";
     # &DataEase;
     # @users = @set;
     # undef(@set);
     #
     # The @users looks like this:
     # marty|rjk889|marty@foo.org
     
     # ExecQuery Query usage:
     # Executing queries are really not complex. I use this for
     # INSERT,UPDATE, and DELETE. I try not to use CREATE in automation.
     # Example:
     # $dbname="y2k";
     # $query = "INSERT into users ";
     # $query .= "VALUES (\'marty\',\'sdfg56\',\'marty\@foo.org\')";
     # &ExecQuery;
      
     
     # Caveats:
     # I have not used DataEase on rather large queries. So far 10,000
     elements
     # seems to be ok. DataEase was designed for simple, web based
     # administration. I might suggest looking at C or Pascal if your
     project 
     # requires processing of vast amounts of data. 
     
     # Terms of Use:
     # This is an ad hoc collection of utilities that makes my life easier.
     My
     # thanks to individual authors who have shared their work. In the same
     # spirit, feel free to use this library.  
     #
     # -Rob
     # rob@fgi.net
     
     
     use Pg;
     
     # Environment stuff
     $user = $ENV{"REMOTE_USER"};
     $ip = $ENV{"REMOTE_ADDR"};
     
     # Create the date (mm/dd/yy hh:mm:ss)
     ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdat)= 
     localtime(time);
     $mon++;
     ($wanted,$dumped) = split(/\./,$sec);
     $sec = $wanted;
     $date = $mon."\/".$mday."\/".$year." ".$hour."\:".$min."\:".$sec;
     
     # Get web string
     #########################################
     #   Grabs the info and puts into %FORM  #
     #########################################
     
     #
     # Pareses out POST methods
     #
     
     sub grabit {
      if ($ENV{'REQUEST_METHOD'} eq 'POST') {
         read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
         #Split the named pairs
         @pairs = split(/&/, $buffer);
         foreach $pair (@pairs) {
             ($name,$value)=split(/=/,$pair);
             $value =~ tr/+/ /;
             $value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C",hex($1))/eg;
             $FORM{$name} = $value;
         }
      }
     }
     
     # Html headers and footers
     #########################################
     #            Html Header                #
     #########################################
     
     # 
     # Prints out http protocols
     #
     sub html_header {
     
        print << "TEXT99999";
     Content-type: text/html 
     
     <html>
     
     <head>
     <title>$title</title>
     </head>
     
     <body text="#000000" vlink="#FF00FF" alink="#FF00FF" link="#FF00FF"
     bgcolor="#FEFFF2">
     <center>
     <font
     color="#288C39"><big><big><big><em>$title</em></big></big></big></font
     >
     </center>
     TEXT99999
     
     }
     
     #########################################
     #            Html Trailer               #
     #########################################
     
     # 
     # Closes out the html page
     #
     sub html_trailer {
     print << "TEXT99999";
     </body>
     </html>
     
     TEXT99999
     }
     
     #########################################
     #            MakeNewPassword            #
     #########################################
     
     #
     # Makes new htpasswd passwords
     #
     
     sub MakeNewPassword {
       # Random seed from p.223 of _Programming Perl_ (O'Reilly & Assoc)
       srand(time() ^ ($$ + ($$ << 15)) );
       @saltchars=(a..z,A..Z,0..9,'.','/');
       # valid salt chars
       $salt=$saltchars[int(rand($#saltchars+1))];
       # first random salt char
       $salt.=$saltchars[int(rand($#saltchars+1))];    
       # second random salt char
       $cpw = crypt($password,$salt);
     }
            
     #########################################
     #               DataEase                #
     #########################################
     
     sub DataEase {
     # Creates standard arrays with the "|" character as the delimitter
         $conn = Pg::connectdb("dbname=$dbname");
         $result = $conn->exec($query);
         $status = $conn->status;
         $tuples = $result->ntuples;
         $fields = $result->nfields;
         $fields--;
         $count = 0;
         while ($count < $tuples ) {
               $fieldcount = 0;
               for ($fieldcount = 0;;$fieldcount++) {
                   $entry .= $result->getvalue($count,$fieldcount);
                   if ($fieldcount != $fields) {
                      $entry .= "\|";
                   }
                   else {
                      last;
                  }
               }
         push(@set,$entry);
         $entry=""; 
         $count++;
         }
     
     }
     
     #########################################
     #               ExecQuery               #
     #########################################
     
     #
     # Makes a single executing query
     #
     
     sub ExecQuery {
         $conn = Pg::connectdb("dbname=$dbname");
         $result = $conn->exec($query);
         $status = $conn->status
     }