pg2php: PostgreSQL to PHP script generator

by Douglas Toltzman
Oak Street Software, Inc.

Description and usage

I wrote pg2php when I got tired of hand-coding PHP scripts for maintaining the content of my PostgreSQL databases. Generic tools didn't offer the features, and customizations I needed to provide simple, robust editing interfaces for a wide range of clients and applications.

My goal was to generate 90 percent of the script, then go back and hand-code improvements and additional elements, like pulldown selection lists, links to related records, etc. Furthermore, I didn't want to hardcode too much syntax into the tool, so I could customize the tools output for each job.

What I came up with was a tool that would read the schema of a table directly from the database, read a template file, and output the contents of the template after making substitutions as specified in the template. Therefore, you must have a template (there is a pretty sophisticated sample included in this document) to feed pg2php in order to get it to do any work. When you invoke the pg2php command (on the command line), you pass in the database connection string, or the database name (if no authentication or special connection parameters are required), the name of the database table (relation in PG terms), and the name of the template file. The program writes it's output to stdout, so you must redirect the output to a file if you wish to keep it.

Execute pg2php with no parameters to get abbreviated usage instructions.

Syntax and program details

Legal directives

These directives are case sensitive.

Directive	Action/Description
EACHFIELD	repeat the nested lines for each attribute in the relation
EACHNUMFIELD	repeat the nested lines for each numeric attribute in the relation
EACHNONNUMFIELD	repeat the nested lines for each non-numeric attribute in the relation
EACHTEXTFIELD	repeat the nested lines for each text attribute in the relation
EACHTSFIELD	repeat the nested lines for each timestamp attribute in the relation
END	marks the end of the lines that should be acted on by one of the previous directives

Supported PostgreSQL field types

These are the only field types that are understood by the program at this time. Others could be added easily, but these are the ones I felt were useful in this context.

bool
bpchar
char
date
float4
float8
int2
int4

int8
money
numeric
text
time
timestamp
varchar

Addressable attributes (substitutions)

These tokens are not case sensitive, or rather, they are case insensitive; depending on whether your glass is half empty or half full.

Token	Description of replacement
dbname	Name of the database or connect string
relname	Name of the relation/table
attname	Current attribute/field name
attcomment	Comment on current attribute or attribute name, if no comment found
size	a guess at the input size of the attribute
maxlength	a guess at the maximum input length
required	if the attribute cannot be null, the following string is returned; <font color="red"><b><sup>*</sup></b></font> ... otherwise an empty string is returned
inputfield	This returns a form input field; usually of type text. The exception is for boolean attributes; a True/False selector is returned for boolean attributes.

You may note that the attribute comment is queried so you can use the database comment feature to provide additional input to your script. I generally use the comment to store a more user-friendly name. The example template uses the attribute comment as the label for the input field.

An example template

<html>
<head>
<title><$RELNAME> Update Form</title>
<meta name="generator" content="pg2php: PostgreSQL to PHP script generator by Oak Street Software, Inc.">
</head>
<body bgcolor="#ffffff">

<h1 align="center"><$RELNAME> Update Form</h1>

<?php
/* include("lconfig.php"); */
$text_fields=array(
#EACHTEXTFIELD
   "<$ATTNAME>",
#END
   "__end__");

$non_num_fields=array(
#EACHNONNUMFIELD
   "<$ATTNAME>",
#END
   "__end__");

$num_fields=array(
#EACHNUMFIELD
   "<$ATTNAME>",
#END
   "__end__");

$all_fields=array(
#EACHFIELD
   "<$ATTNAME>",
#END
   "__end__");

function fixup_text_inputs($need_slashes,$no_newlines)
{
	global $HTTP_POST_VARS, $text_fields;

   if ($no_newlines) {
     for ($fieldi=0; $fieldi < count($text_fields)-1; $fieldi++) {
       $fn=$text_fields[$fieldi];
       $HTTP_POST_VARS[$fn] = nl2br($HTTP_POST_VARS[$fn]);
     }
   }

   if ($need_slashes) {
     for ($fieldi=0; $fieldi < count($text_fields)-1; $fieldi++) {
       $fn=$text_fields[$fieldi];
       $HTTP_POST_VARS[$fn] = addslashes($HTTP_POST_VARS[$fn]);
     }
   }
}


/*
	Handle POST method
*/
function post_main($dbc)
{
	global $HTTP_POST_VARS, $offs, $no_newlines, $oid, $text_fields, $non_num_fields, $num_fields, $all_fields;

   $need_slashes = (get_cfg_var('magic_quotes_gpc') == false);
	
	if (isset($HTTP_POST_VARS['oid']) && isset($HTTP_POST_VARS['op'])) {
/*
	Delete operation
*/
     if ($HTTP_POST_VARS['op'] == "Delete Record") {
        $del_res = pg_Exec($dbc,"delete from <$RELNAME> where oid=".$HTTP_POST_VARS['oid']);
		  if ($del_res)
		    pg_FreeResult($del_res);
		  else
		    printf("<p><font color=\"red\">Error: %s</font></p>\n",pg_ErrorMessage($dbc));
	  }
	  else if ($HTTP_POST_VARS['op'] == "Submit Changes") {
/* >>> Update operation <<<
	Scrub text inputs for illegal characters; quote special chars, etc.
*/
       fixup_text_inputs($need_slashes,$no_newlines);

	    unset($set_list);
       for ($fieldi=0; $fieldi < count($num_fields)-1; $fieldi++) {
         $fn=$num_fields[$fieldi];
         if (isset($HTTP_POST_VARS[$fn])) {
           if ($set_list) $set_list .= ",";
           $nval = (strlen(trim($HTTP_POST_VARS[$fn])) == 0) ? 'NULL' : $HTTP_POST_VARS[$fn];
           $set_list .= $fn."=".$nval;
         }
       }

       for ($fieldi=0; $fieldi < count($non_num_fields)-1; $fieldi++) {
         $fn=$non_num_fields[$fieldi];
         if (isset($HTTP_POST_VARS[$fn])) {
           if ($set_list) $set_list .= ",";
	        $set_list .= $fn."='".$HTTP_POST_VARS[$fn]."'";
         }
       }

       $sql_update = "update <$RELNAME> set ".$set_list." where oid=".$HTTP_POST_VARS['oid'];
       if (!($sql_result = pg_Exec($dbc,$sql_update))) {
          printf("<hr><p><font color=\"red\">Error in update.<br>We said: %s<br>Postgres said: %s</p><hr>\n",
            $sql_update, pg_ErrorMessage($dbc));
       }
       else {
         $oid = $HTTP_POST_VARS['oid'];	/* trigger record fetch in main procedure */
         pg_FreeResult($sql_result);
       }
	  }
	  else if ($HTTP_POST_VARS['op'] == "Save New Record") {
/* >>> Insert operation <<<
	Scrub text inputs for illegal characters; quote special chars, etc.
*/
       fixup_text_inputs($need_slashes,$no_newlines);

	    unset($field_list);
	    unset($value_list);

       for ($fieldi=0; $fieldi < count($num_fields)-1; $fieldi++) {
         $fn=$num_fields[$fieldi];
         if (isset($HTTP_POST_VARS[$fn])) {
           if ($field_list) { $field_list .= ","; $value_list .= ","; }
	        $field_list .= $fn;
           if (strlen(trim($HTTP_POST_VARS[$fn])) == 0)
			    $value_list .= "NULL";
			  else
			    $value_list .= $HTTP_POST_VARS[$fn];
         }
       }

       for ($fieldi=0; $fieldi < count($non_num_fields)-1; $fieldi++) {
         $fn=$non_num_fields[$fieldi];
         if (isset($HTTP_POST_VARS[$fn])) {
            if ($field_list) { $field_list .= ","; $value_list .= ","; }
	         $field_list .= $fn;
	         $value_list .= "'".$HTTP_POST_VARS[$fn]."'";
         }
       }
       $sql_insert = "insert into <$RELNAME> (".$field_list.") values (".$value_list.")";
       if (!($sql_result = pg_Exec($dbc,$sql_insert))) {
          printf("<hr><p><font color=\"red\"><b>Error in insert</b><br>We said: %s<br>Postgres said: %s</p><hr>\n",
            $sql_insert, pg_ErrorMessage($dbc));
       }
       else {
		   $oid = pg_GetLastOid($sql_result);
         pg_FreeResult($sql_result);
       }
	  }
	  else
	     printf("<p><font color=\"red\"><b>Error:</b> Unknown operation; \"%s\"</p>\n", $HTTP_POST_VARS['op']);
   }
	else
	  echo("<p><b>Warning:</b> required variables not found in HTTP_POST_VARS, operation not performed.</p>\n");

	return $offs + 1;
}


/*
	Main entry point for PHP script
*/
$c=pg_Connect("dbname=<$DBNAME>");
if ($c) {
  if ($REQUEST_METHOD == "GET") {
    $offs = isset($HTTP_GET_VARS['offs']) ? $HTTP_GET_VARS['offs'] : 0;
    if ($res = pg_Exec($c, "select *,oid from <$RELNAME> order by <$ATTNAME> limit 1 offset $offs")) {
      if (pg_NumRows($res) == 1) {
	     $rnext = $offs + 1;
        $arow = pg_Fetch_Array($res,0);
		  while (list($key,$val)=each($arow)) {
          if (!is_long($key)) $$key = trim($val);
        }
      }
      pg_FreeResult($res);
    }
  }
  else if ($REQUEST_METHOD == "POST") {
     unset($oid);
     $offs = isset($HTTP_POST_VARS['offs']) ? $HTTP_POST_VARS['offs'] : 0;
	  if ($HTTP_POST_VARS['op'] == "Make New Record") {
       for ($fieldi=0; $fieldi < count($all_fields)-1; $fieldi++) {
         $fn=$all_fields[$fieldi];
         unset($$fn);
       }
		 $oid = 0;
	  }
	  else {
       $rnext = post_main($c);
/*
  Post_main() will set $oid if a record-reload is required
*/
		 if ($oid) {
         if ($res = pg_Exec($c, "select * from <$RELNAME> where oid=$oid")) {
           if (pg_NumRows($res) == 1) {
             $arow = pg_Fetch_Array($res,0);
             while (list($key,$val)=each($arow)) {
               if (!is_long($key)) $$key = trim($val);
             }
           }
           pg_FreeResult($res);
         }
		 }
     }
  }

  if ($res = pg_Exec($c, "select count(*) from <$RELNAME>")) {
    if (pg_NumRows($res) == 1) {
      $record_count = pg_Result($res,0,0);
		if ($rnext >= $record_count) unset($rnext);
      pg_FreeResult($res);
    }
  }
  pg_Close($c);
}
?>

<p>
<table cellpadding="5"><tr>
<?php
  if ($offs > 0) {
    printf("<td><a href=\"%s?offs=0\"><b>&lt;&lt;</b></a></td>",$PHP_SELF);
    printf("<td><a href=\"%s?offs=%d\"><b>&lt;-</b></a></td>",$PHP_SELF,$offs-1);
  }
  else
    echo("<td>&lt;&lt;</td><td>&lt;-</td>");
  if ($rnext)
    printf("<td><a href=\"%s?offs=%d\"><b>-&gt;</b></a></td>",$PHP_SELF,$rnext);
  else
    echo("<td>-&gt;</td>");
  if ($record_count && $offs+1 < $record_count)
    printf("<td><a href=\"%s?offs=%d\"><b>&gt;&gt;</b></a></td>",$PHP_SELF,$record_count-1);
  else
    echo("<td>&gt;&gt;</td>");

  printf("<td>record %d of %d</td>",$offs+1,$record_count);
?>
</tr></table>
</p>

<form name="<$RELNAME>" action="<?php echo($PHP_SELF)?>" method="POST">
<input type="hidden" name="oid" value="<?php echo($oid)?>">
<input type="hidden" name="offs" value="<?php echo($offs)?>">
<table>

#EACHFIELD
<tr>
  <td align="right" valign="top"><b><$ATTCOMMENT></b></td>
  <td align="left"><$INPUTFIELD> <$REQUIRED></td>
</tr>
#END

</table>
<p></p>
<blockquote>
<?php if ($oid == 0) { ?>
	<input type="submit" name="op" value="Save New Record">
<?php } else { ?>
	<input type="submit" name="op" value="Submit Changes">
	<input type="submit" name="op" value="Make New Record">
	<input type="submit" name="op" value="Delete Record">
<?php } ?>
	<input type="reset" name="op" value="Reset Form">
</blockquote>

<p><font color="red"><sup>*</sup> denotes a required field.</font></p>

</form>

</body>
</html>

End of sample template

Shared library dependencies

ldd reports the following dependencies in the current pg2php build for my Linux Elf system.

libdb.so.3
libpq.so.2
libc.so.6
libcrypt.so.1
libresolv.so.2
libnsl.so.1
/lib/ld-linux.so.2