Entry
How can I parse a Postgresql array result in php?
Mar 7th, 2007 10:27
Thomas Harding, Maurice Makaay, Rick Ellis,
Since I see no answer on this faqt, I'll give it a try myself. I just
wrote a parser for the PostgreSQL array format in PHP. I'm not sure if
it works for 100% of the cases, but my ugly test cases all went well. I
wrote this in PHP5. To be able to use it in PHP4, lose the "public" in
front of the function name and replace "throw new Exception" by
returning NULL or by die(), to propagate format errors.
Note: this function only parses single dimension arrays, since I simply
did not need a multi-dimensional parser. Extending this parser to a
multi-dimensional parser should not be that hard if you can read and
understand this code.
#######################
#
# I improved this function to handle multi-dimensions arrays,
# and also released a function to encode multi-dimensions arrays
# see code below this function
#
# T.Harding
#######################
public function decode_pgarray($encarray)
{
# NULL translates to an empty array.
if (is_null($encarray)) return array();
# Character by character parser for the array format.
$fields = array();
$fldCount = 0;
$inQuotes = false;
$inFields = false;
for ($i = 0; $i < strlen($encarray); $i++)
{
# Start a new field.
if (!isset($fields[$fldCount])) $fields[$fldCount] = "";
# Get the next character to parse.
$tmp = substr($encarray,$i,1);
# Starting and ending curly braces.
if ($tmp == '{' && !$inFields)
$inFields = 1;
elseif ($inFields && $tmp == '}' && ! $inQuotes)
return $fields;
elseif (! $inFields)
throw new DatabaseException(
"String \"$encarray\" is not in postgresql array " .
"encoding format: missing starting curly brace");
# Delimiter and not in a quoted piece?
# Then we have found a new field.
elseif ($tmp === ',' && !$inQuotes)
$fldCount++;
# Did we start a new field and does that field start with
# a double quote? Then we have to handle a quoted field.
elseif ($fields[$fldCount] == "" &&
$encarray[$i] == '"' &&
!$inQuotes)
$inQuotes = true;
# Double quote found? Then this is the end of the quoted field.
elseif ($encarray[$i] == '"')
$inQuotes = false;
# Escaped character found? Add it without the escape
# to the field.
elseif ($encarray[$i] == "\\")
$fields[$fldCount] .= $encarray[++$i];
# Simply add the character to the field.
else
$fields[$fldCount] .= $encarray[$i];
}
throw new DatabaseException(
"String \"$encarray\" is not in postgresql array encoding " .
"format: missing ending curly brace");
}
#
#Good luck using this function!
#
#
# Now, multi-dimensionnal functions
#
<?php
#
#Copyright (c) 2007, Maurice Makaay, Thomas Harding
# <mailto: thomas.harding@laposte.net>
#
#Permission is hereby granted, free of charge, to any person obtaining a
copy
#of this software and associated documentation files (the "Software"),
to deal
#in the Software without restriction, including without limitation the
rights to
#use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies
#of the Software, and to permit persons to whom the Software is
furnished to do
#so, subject to the following conditions:
#
#The above copyright notice and this permission notice shall be included
in all
#copies or substantial portions of the Software.
#
#THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
#IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
#FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
#AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
#LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
FROM,
#OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
IN THE
#SOFTWARE.
#
#
#
# THIS file supply two functions:
#
# function encode_pgarray($array,$type = "STRING")
#
# encode a php array to a string suitable in postgresql INSERT/REPLACE query
# for an "something[]" type field
#
# handled types are "INTEGER","FLOAT".
# FIXME: there is no special case for other types
#
#
# function decode_pgarray($pg_array)
# returns array
#
# decode a postgresql array response and format it as php array
# this function is (c)2005, Maurice Makaay & (c)2007, Thomas Harding
#
/**
*
* function encode_pgarray($array,$type = "STRING")
*
* serialize an array into postgresql suitable form.
* array must be consistant:
*
* - you cannot mix arrays and values in slices
* - you cannot mix arrays and values other slices
* - you cannot have keys other than integers
*
* these limitations are the cope to comply with postgresql 7.4.x
* (postgres silently discard or trunk inconsistant arrays,
* so it is _not_ a good idea to supply this, isn't it?)
*
* usage:
try
{
$pg_encoded_array = encode_pgarray($array,$type);
}
catch (pgarray_exception $e)
{
print($e);
}
*/
/**
*
*
* function decode_pgarray($pg_array,$fields = array(),&$i = 0)
*
*
* decodes pg array fields
*
* usage:
try {
$result = decode_pgarray($pg_array);
if (is_null($result))
echo "null\n";
elseif (is_string($result))
echo "string: ".$result."\n"
elseif (is_array($result))
{ echo "array"; var_dump($result) ; echo "\n" ;}
else
echo "decode_pgarray: an error may occured\n";
}
catch (pgarray_exception $e)
{
print $e;
}
*
*
* returns NULL if array result is null,
*
* string if it is a string (as you selected only one
* slice in query),
*
* array if it is actually an array.
*/
###################################
#
# CODE ITSELF...
#
#
/**
* decodes pg array fields
*
* usage:
* $result = decode_pgarray($pg_array);
* if (is_null($result))
* echo "null\n";
* elseif (is_string($result))
* echo "string: ".$result."\n"
* elseif (is_array($result))
* { echo "array"; var_dump($result) ; echo "\n" ;}
* else
* echo "decode_pgarray: an error may occured\n";
*
*
* returns NULL if array result is null,
*
* string if it is a string (as you selected only one
* slice in query),
*
* array if it is actually an array.
*/
function decode_pgarray($pg_array,$fields = array(),&$i = 0)
{
// NULL returns NULL
if (is_null($pg_array)) return NULL;
// Character by character parser for the array format.
$field_count = 0;
$in_quotes = false;
$in_fields = false;
$is_string = false;
$return_from_child = false;
if ( strlen($pg_array) == 0
|| $pg_array{0} != "{"
|| $pg_array{(strlen($pg_array) - 1)} != "}"
# FIXME: comment next line if it is actually array
|| $pg_array == "{}"
)
{
$is_string = true;
$fields = "";
}
for ($i; $i < strlen($pg_array) ; $i++)
{
// Get the next character to parse.
$tmp = substr($pg_array,$i,1);
// Initialize the new field.
if (!isset($fields[$field_count])
&& $in_quotes == false
&& $tmp !="}"
&& !$is_string
)
#FIXME: fields in postgresql can not be null,
# chains can only be empty chains.
# and so on
# also all fields have same dimensions
//if (substr($pg_array,$i + 1,2) != "\"\"")
$fields[$field_count] = "";
// If it is null (=={""}), then unset it
//else
//{
// unset($fields[$field_count])
// $field_count --;
//}
// We enter in a new field zone
if ( $tmp == "{"
&& !$in_fields
&& !$in_quotes
&& !$is_string)
$in_fields = 1;
// Delimiter and not in a quoted piece?
// Then we have found a new field.
elseif ($tmp === ',' && !$in_quotes)
if(!$return_from_child)
$field_count++;
else
$return_from_child = false;
// End of field,returns result
elseif ($in_fields
&& $tmp == '}'
&& !$in_quotes)
return $fields;
// Found a new array delimiter?
// then parsing the nested array
// and not forget to starting a new field
elseif($tmp == '{'
&& !$in_quotes
&& !$is_string)
{
$value = decode_pgarray($pg_array,$fields[$field_count],$i);
$return_from_child = 1;
$fields[$field_count] = $value;
$field_count ++;
}
// Did we start a new field and does that field start with
// a double quote? Then we have to handle a quoted field.
elseif ( $tmp == '"'
&& !$in_quotes
&& $is_string == false
&& strlen($fields[$field_count]) == 0)
$in_quotes = true;
// Double quote found? Then this is the end of the quoted field.
elseif ($tmp == '"'
&& $in_quotes)
$in_quotes = false;
// Escaped character found? Add it without the escape
// to the field (except if it is string: write backslash).
elseif ($tmp == "\\")
if ($is_string)
$fields .= $tmp;
else
$fields[$field_count] .= substr($pg_array,++$i,1);
// Simply add the character to the field.
else
if ($is_string)
$fields .= $tmp;
else
$fields[$field_count] .= $tmp;
}
// $fields is string
// then returns $fields
if (is_string($fields))
return $fields;
else
throw new pgarray_exception('unattended error while scanning array',12);
}
/*
* serialize an array into postgresql suitable form.
* array must be consistant:
*
* - you cannot mix arrays and values in slices
* - you cannot mix arrays and values other slices
* - you cannot have keys other than integers
*
* these limitations are the cope to comply with postgresql 7.4.x
* (postgres silently discard or trunk inconsistant arrays,
* so it is _not_ a good idea to supply this, isn't it?)
*
*/
function encode_pgarray($array,$type = "STRING")
{
// conrol content
if (!is_array($array))
{
throw new pgarray_exception(
sprintf("value supplied is not an array\n"),1);
return false;
}
array_dims($array,$result);
while(array_key_exists('dims',$result))
{
$max_keys[] = $result['max'];
$result = $result['dims'];
}
fill_empty_pg_array_keys($array,$max_keys);
$result = serialize_pg_array($array,$type);
return sprintf("'%s'",$result);
}
####
####
#### FOLLOWING FUNCTIONS ARE 'private'
####
#### do not let you be confused by that, huh?
####
####
####
/**
* calculus of array dimensions
* returns array('max' => dimension,'dims' array('max' =>
dimension,'dims' ...)
*/
function array_dims(&$array,&$result,$max=0)
{
if (is_array($array))
$max = max(max(array_keys($array)),$max);
$result['max'] = $max;
if (is_array($array))
{
$result['dims'] = array();
$max = 0;
foreach($array as $slice)
if(is_array($slice))
{
$result['dims']['max'] = 0;
// iterate other slices
array_dims($slice,$result['dims'],$max);
$max = max($result['dims']['max'],$max);
}
}
}
function fill_empty_pg_array_keys(&$array,$max_keys,$level=0)
{
$max_key = $max_keys[$level];
// fails if it is not array and we are not in last dimension
if(!is_array($array))
throw new pgarray_exception(
"cannot mix types 'arrays' and other types in an array
slice"
,1);
// first, verify keys consistence (must be integers)
$keys = array_keys($array);
foreach($keys as $key)
if (!is_int($key))
throw new pgarray_exception(
sprintf("key \"%s\" is not integer\n",$key),1);
// do not run next parts if it is last dimension,
// sets empty keys at ""
if(!array_key_exists($level + 1,$max_keys))
{
for($i = 0; $i <= $max_key ; $i++)
if (!isset($array[$i]))
return true;
}
if(!isset($array))
$array = array("");
// for keys < dimension
for($i = 0; $i <= $max_key ; $i++)
{
//Then, fill empty keys
if (!array_key_exists($i,$array))
$array[$i] = array();
// checks slice concistence
$slice = $array[$i];
$slicetype = is_array($slice) ? true : false;
if (!$slicetype)
throw new pgarray_exception(
sprintf("cannot mix types 'arrays' and other types in an
array slice\n",$key),1);
// shift of max_keys done only once per dimension
$shift = ($i == 0) ? true : false;
fill_empty_pg_array_keys($array[$i],$max_keys,$level + 1);
}
}
/**
*
* Transform the cleaned array in string
* usable by postgres as array field
*
*/
function serialize_pg_array($array,$type)
{
$search=array('"',"\\","'");
$replace=array('\\"','\\\\\\\\',"''");
// explore the array
for($i = 0; $i <= max(array_keys($array)) ; $i++)
{
$content = $array[$i];
// initialize $result;
if (!isset($result))
$result = "";
// append field separator
else
$result .= ",";
if (!is_array($content))
// fill fields with values, fill holes
switch($type)
{
case "INTEGER":
if ($content == "")
{
trigger_error("encode_pgarray: not set integer
value will be fill with 0",E_USER_NOTICE);
$result .= '0';
}
else
if (!is_int($content))
throw new pgarray_exception(
sprintf("%s: value supplied is not an
integer\n",
$content),1);
else
$result .= $content;
continue;
case "FLOAT":
if ($content == "")
{
trigger_error("encode_pgarray: not set float
value will be fill with 0",E_USER_NOTICE);
$result .= '0';
}
else
if (!is_float($content))
throw new pgarray_exception(
sprintf("%s: value supplied is not a
float\n",
$content),1);
else
$result .= $content;
continue;
default:
$result .=
sprintf('"%s"',str_replace($search,$replace,$content));
continue;
}
else
// run other slice
$result .= sprintf("%s",serialize_pg_array($content,$type));
}
// return result
return sprintf('{%s}',$result);
}
/**
*
* exception handle for encode_pgarray()
*
* display a message with nice colors
*
* FIXME: you can make anithing more serious :)
*
*/
class pgarray_exception extends Exception
{
public function __construct($message, $code = 0)
{
switch($code)
{
case 1:
$color='red';
$level='Warning';
$function='encode_pgarray';
break;
case 2:
$color='green';
$level='Notice';
$function='encode_pgarray';
break;
case 3:
$color='blue';
$level='Warning';
$function='encode_pgarray';
break;
case 11:
$color='red';
$level='Warning';
$function='decode_pgarray';
break;
case 12:
$color='green';
$level='Notice';
$function='decode_pgarray';
break;
case 13:
$color='blue';
$level='Warning';
$function='decode_pgarray';
break;
default:
$level ='Warning';
$color='red';
$function='encode_pgarray / decode_pgarray';
break;
}
$this->message = sprintf("<div style='color: %s'>%s:
function %s: %s</div>",$color,$level,$function,$message);
parent::__construct($this->message, $code);
}
public function __toString()
{
return "{$this->message}\n";
}
}
?>
#
# have a nice day ;)
#
