<?php
/**
* @package db
* @subpackage database
*/
// Copyright (c) 2008 Supernerd LLC and Contributors.
// All Rights Reserved.
//
// This software is subject to the provisions of the Zope Public License,
// Version 2.1 (ZPL). A copy of the ZPL should accompany this distribution.
// THIS SOFTWARE IS PROVIDED "AS IS" AND ANY AND ALL EXPRESS OR IMPLIED
// WARRANTIES ARE DISCLAIMED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
// WARRANTIES OF TITLE, MERCHANTABILITY, AGAINST INFRINGEMENT, AND FITNESS
// FOR A PARTICULAR PURPOSE.
/**
* database
*
* @package
* @version $id$
* @copyright 1997-2008 Supernerd LLC
* @author Steve Francia <steve.francia+hide@address.com>
* @author John Lesusur
* @author Rick Gigger
* @author Richard Bateman
* @license Zope Public License (ZPL) Version 2.1 {@link http://zoopframework.com/license}
*/
class database
{
/**
* db
*
* @var mixed
* @access public
*/
var $db = null;
/**
* transaction
*
* @var float
* @access public
*/
var $transaction = 0;
/**
* database
*
* @param mixed $dsn
* @access public
* @return void
*/
function database($dsn)
{
$options = array(
'debug' => 2
);
if (defined('db_persistent'))
$options['persistent'] = db_persistent;
$this->dsn = &$dsn;
global $globalTime;
logprofile($globalTime, true);
$this->db = DB::connect($dsn, $options);
logprofile($globalTime, "connect: {$dsn['phptype']}://{$dsn['hostspec']}:{$dsn['port']}/{$dsn['database']}");
if(DB::isError($this->db))
{
$this->error($this->db);
}
$this->db->setFetchMode(DB_FETCHMODE_ASSOC);
}
/**
* getDSN
*
* @access public
* @return void
*/
function getDSN()
{
return $this->dsn;
}
/**
* verifyQuery
*
* @param mixed $inQuery
* @access public
* @return void
*/
function verifyQuery($inQuery)
{
if(defined("verify_queries") && verify_queries)
{
$inQuote = 0;
for($i = 0 ; $i < strlen($inQuery); $i++)
{
if(!$inQuote && $inQuery[$i] == ';')
trigger_error("this query had a ;, and is not safe...");
else if($inQuery[$i] == '\'')
{
if($inQuote)
{
$inQuote = 0;
}
else
$inQuote = 1;
}
else if($inQuery[$i] == '\\')
{
$i++;
}
}
}
}
/**
* makeDSN
*
* @param mixed $dbtype
* @param mixed $host
* @param mixed $port
* @param mixed $username
* @param mixed $password
* @param mixed $database
* @access public
* @return void
*/
function makeDSN($dbtype, $host, $port, $username, $password, $database)
{
return array(
'phptype' => $dbtype,
//'dbsyntax' => false,
'username' => $username,
'password' => $password,
//'protocol' => false,
'hostspec' => $host,
'port' => $port,
//'socket' => false,
'database' => $database,
);
}
/**
* begin_transaction
*
* @access public
* @return void
*/
function begin_transaction( )
{
if($this->transaction == 0)
$this->db->query("BEGIN");
$this->transaction++;
}
/**
* commit_transaction
*
* @access public
* @return void
*/
function commit_transaction( )
{
$this->transaction--;
if($this->transaction == 0)
$this->db->query("COMMIT");
}
/**
* rollback_transaction
*
* @access public
* @return void
*/
function rollback_transaction( )
{
$this->transaction--;
if($this->transaction == 0)
$this->db->query("ROLLBACK");
}
/**
* error
*
* @param mixed $result
* @access public
* @return void
*/
function error($result)
{
while ($this->transaction)
{
sql_rollback_transaction();
}
//echo substr($inQueryString, 0, 1200) . "<br>" .
//echo_r($result);
trigger_error("PearDB returned an error. The error was " . $result->getMessage());
die();
}
function trusted_query($inQueryString)
{
$result = $this->db->query($inQueryString);
return $result;
}
/**
* query
*
* @param mixed $inQueryString
* @param mixed $Db
* @access public
* @return void
*/
function &query($inQueryString)
{
$this->verifyQuery($inQueryString);
global $globalTime;
logprofile($globalTime, true);
$result = &$this->db->query($inQueryString);
logprofile($globalTime, $inQueryString);
if(DB::isError($result))
{
$this->error($result);
}
return $result;
}
function &getOne($inQueryString)
{
$this->verifyQuery($inQueryString);
global $globalTime;
logprofile($globalTime, true);
$result = &$this->db->getOne($inQueryString);
logprofile($globalTime, $inQueryString);
if(DB::isError($result))
{
$this->error($result);
}
return $result;
}
function &getAll(&$inQueryString, $params = array(), $mode = DB_FETCHMODE_DEFAULT)
{
$this->verifyQuery($inQueryString);
global $globalTime;
logprofile($globalTime, true);
$result = &$this->db->getAll($inQueryString, $params, $mode);
logprofile($globalTime, $inQueryString);
if(DB::isError($result))
{
$this->error($result);
}
return $result;
}
function &getCol(&$query)
{
$this->verifyQuery($query);
global $globalTime;
logprofile($globalTime, true);
$result = &$this->db->getCol($query);
logprofile($globalTime, $query);
if(DB::isError($result))
{
$this->error($result);
}
return $result;
}
function &getAssoc($query)
{
$this->verifyQuery($query);
global $globalTime;
logprofile($globalTime, true);
$result = &$this->db->getAssoc($query);
logprofile($globalTime, $query);
if(DB::isError($result))
{
$this->error($result);
}
return $result;
}
/**
* get_fields
*
* @param mixed $table
* @access public
* @return void
*/
function get_fields($table)
{
return $this->db->tableInfo($table);
}
/**
* insert
*
* @param mixed $query
* @access public
* @return void
*/
function insert($query)
{
return $this->query($query);
}
/**
* fetch_sequence
*
* @param mixed $sequence
* @access public
* @return void
*/
function fetch_sequence( $sequence )
{
return $this->getOne("select nextval('\"$sequence\"'::text)");
}
/**
* returns true if rows are returned
*
* @param string $query the query for the database
* @return boolean
*/
function check($query)
{
$result = $this->query($query);
if($result->numRows() < 1)
{
$result->free();
return 0;
}
else
{
$result->free();
return 1;
}
}
/**
* fetch_into_arrays
*
* @param mixed $query
* @access public
* @return void
*/
function fetch_into_arrays($query)
{
$result = $this->getAll($query, array(), DB_FETCHMODE_ASSOC | DB_FETCHMODE_FLIPPED);
return $result;
}
/**
* fetch_into_arrobjs
*
* @param mixed $query
* @access public
* @return void
*/
function fetch_into_arrobjs($query)
{
bug("this function deprecated, please use a different one...");
$result = $this->getAll($query);
return $result;
}
/**
* new_fetch_into_array
*
* @param mixed $query
* @access public
* @return void
*/
function new_fetch_into_array($query)
{
return $this->fetch_column($query);
}
function fetch_column($query)
{
die('hi');
$result = &$this->getCol($query);
return $result;
}
/**
* fetch_into_array
*
* @param mixed $inTableName
* @param mixed $inFieldName
* @param string $inExtra
* @access public
* @return void
*/
function fetch_into_array($inTableName, $inFieldName, $inExtra = "")
{
bug("please change this to a query and use fetch_column");
$result = &$this->getCol("SELECT $inFieldName FROM $inTableName $inExtra");
return $result;;
}
/**
* Use this function to get a record from the database. It will be returned as an array with the key as the fieldname and the value as the value.
*
* @param string $query the query for the database
* @return associative array in the form [fieldname] => value;
*/
function fetch_one($inQueryString)
{
$result = &$this->query($inQueryString);
$numRows = $result->numRows();
if($numRows > 1)
{
trigger_error ( "Only one result was expected. " . $numRows . " were returned");
}
else if($numRows == 0)
{
return(false);
}
$row = $result->fetchRow();
$result->free();
return $row;
}
/**
* Use this function to get a record, or multiple records from the database.
* It will be returned as a two dimensional array. The first dimension will be an array with the key being the value of the primary key in each record.
* The second dimension would be identical to that returned from fetch_one but without the primary key.
*
* @param string $query the query for the database
* @return associative array in the form [primarykeyvalue][fieldname] => value;
*/
function fetch_assoc($inQuery)
{
$result = $this->getAssoc($inQuery);
return $result;
}
/**
* fetch_rows
*
* @param mixed $inQuery
* @param int $inReturnObjects
* @access public
* @return void
*/
function &fetch_rows($inQuery, $inReturnObjects = 0)
{
$rows = array();
if($inReturnObjects)
{
$rows = &$this->getAll($inQuery, array(), DB_FETCHMODE_OBJECT);
}
else
{
$rows = &$this->getAll($inQuery);
}
return $rows;
}
/**
* &fetch_map
*
* @param mixed $inQuery
* @param mixed $inKeyField
* @access public
* @return void
*/
function &fetch_map($inQuery, $inKeyField)
{
$rows = $this->getAll($inQuery);
$results = array();
foreach($rows as $row)
{
if( is_array($inKeyField))
{
$cur = &$results;
foreach( $inKeyField as $val )
{
$curKey = $row[ $val ];
if( !isset( $cur[ $curKey ] ) )
{
$cur[ $curKey ] = array();
}
$cur = &$cur[ $curKey ];
}
if(count($cur))
{
echo_r($results);
trigger_error("duplicate key $curKey, would silently destroy data");
}
$cur = $row;
}
else
{
$mapKey = $row[ $inKeyField ];
foreach($row as $key => $val)
{
$results[$mapKey][$key] = $val;
}
}
}
return $results;
}
/**
* fetch_simple_map
*
* @param mixed $inQuery
* @param mixed $inKeyField
* @param mixed $inValueField
* @access public
* @return void
*/
function fetch_simple_map($inQuery, $inKeyField, $inValueField)
{
$rows = $this->getAll($inQuery);
$results = array();
foreach($rows as $row)
//while($row = sql_fetch_array($rows))
{
$cur = &$results;
if(is_array($inKeyField))
{
foreach($inKeyField as $key)
{
$cur = &$cur[$row[$key]];
$lastKey = $row[$key];
}
}
else
{
$cur = &$cur[$row[$inKeyField]];
$lastKey = $row[$inKeyField];
}
if(isset($cur) && !empty($lastKey))
{
trigger_error("duplicate key in query: \n $inQuery \n");
}
$cur = $row[ $inValueField ];
}
return $results;
}
/**
* &fetch_complex_map
*
* @param mixed $inQuery
* @param mixed $inKeyField
* @access public
* @return void
*/
function &fetch_complex_map($inQuery, $inKeyField)
{
$rows = $this->getAll($inQuery);
$results = array();
// loop through each row in the result set
foreach($rows as $row)
{
if( gettype($inKeyField) == "array")
{
$cur = &$results;
foreach( $inKeyField as $val )
{
$curKey = $row[ $val ];
if( !isset( $cur[ $curKey ] ) )
{
$cur[ $curKey ] = array();
}
$cur = &$cur[ $curKey ];
}
$cur[] = $row;
}
else
{
// get the key for the result map
$mapKey = $row[ $inKeyField ];
$results[$mapKey][] = $row;
}
}
return $results;
}
/**
* fetch_one_cell
*
* @param mixed $inQueryString
* @param int $inField
* @access public
* @return void
*/
function fetch_one_cell($inQueryString, $inField = 0)
{
$result = $this->query($inQueryString, array(), DB_FETCHMODE_ORDERED);
$numRows = $result->numRows();
if($numRows > 1)
{
trigger_error(substr($inQueryString, 0, 150) . "<br>Only one result was expected. " . $numRows . " were returned.<br>");
}
else if($numRows == 0)
{
$result->free();
return(false);
}
$row = $result->fetchRow(DB_FETCHMODE_ORDERED);
$result->free();
if (!isset($row[$inField]))
{
$row[$inField] = null;
}
return $row[$inField];
}
/**
* &prepare_tree_query
*
* @param mixed $inQueryString
* @param string $idField
* @param string $parentField
* @access public
* @return void
*/
function &prepare_tree_query($inQueryString, $idField = "id", $parentField = "parent")
{
$map = &$this->fetch_map($inQueryString, $idField);
$complex = array();
foreach($map as $id => $obj)
{
$complex[$obj[$parentField]][] = &$map[$id];
}
$answer[$idField] = &$map;
$answer[$parentField] = &$complex;
return $answer;
}
/**
* &better_fetch_tree
*
* @param mixed $inQueryString
* @param mixed $rootNode
* @param string $idField
* @param string $parentField
* @param int $depth
* @access public
* @return void
*/
function &better_fetch_tree( $inQueryString, $rootNode, $idField = "id", $parentField = "parent", $depth = -1)
{
if(!is_array($inQueryString))
{
//do your own complex mapping...
//find the root nodes as you go...
$objects = &$this->prepare_tree_query($inQueryString, $idField, $parentField);
}
else
{
//php5 clone this
$objects = &$inQueryString;
}
if(is_array($rootNode) && in_array($object[$idField], $rootNode))
{
foreach($rootNode as $node)
{
$tree[$node] = $objects[$idField][$node];
}
}
else
{
$tree = $objects[$idField][$rootNode];
}
if(is_array($rootNode))
{
foreach($rootNode as $node)
{
$tree[$node]['children'] = $this->__sql_better_append_children($node, $objects, $idField, $parentField, $depth);
}
}
else
{
$tree['children'] = $this->__sql_better_append_children($rootNode, $objects, $idField, $parentField, $depth);
}
return $tree;
}
/**
* &fetch_tree
*
* @param mixed $inQueryString
* @param mixed $rootNode
* @param string $idField
* @param string $parentField
* @access public
* @return void
*/
function &fetch_tree( $inQueryString, $rootNode, $idField = "id", $parentField = "parent")
{
if(is_array($inQueryString))
{
$objects = $inQueryString;
}
else
{
$objects = $this->fetch_map($inQueryString, $idField);
}
if(is_array($rootNode))
{
foreach($rootNode as $node)
{
//php 5 need clone here
$node = $objects[$node];
$tree[] = $this->__sql_append_children($node, $objects, $idField, $parentField);
}
}
else
{
//php 5 need clone here
$rootNode = $objects[$rootNode];
$tree = $this->__sql_append_children($rootNode, $objects, $idField, $parentField);
}
return $tree;
}
/**
* &__sql_append_children
*
* @param mixed $rootObject
* @param mixed $objects
* @param mixed $idField
* @param mixed $parentField
* @access public
* @return void
*/
function &__sql_append_children(&$rootObject, $objects, $idField, $parentField)
{
foreach($objects as $object)
{
if(isset($object[$parentField]) && $object[$parentField] == $rootObject[$idField])
{
$rootObject["children"][$object[$idField]] = $object;
$this->__sql_append_children($rootObject["children"][$object[$idField]], $objects, $idField, $parentField);
}
}
return $rootObject;
}
/**
* &__sql_better_append_children
*
* @param mixed $rootObjectId
* @param mixed $objects
* @param mixed $idField
* @param mixed $parentField
* @param mixed $depth
* @access public
* @return void
*/
function &__sql_better_append_children(&$rootObjectId, &$objects, $idField, $parentField, $depth = -1)
{
if($depth != 0)
{
$children = array();
if(isset($objects[$parentField][$rootObjectId]))
{
foreach($objects[$parentField][$rootObjectId] as $object)
{
$children[$object[$idField]] = $object;
if(isset($objects[$parentField][$object[$idField]]))
$children[$object[$idField]]['children'] = $this->__sql_better_append_children($object[$idField], $objects, $idField, $parentField, $depth - 1);
}
}
}
return $children;
}
// inQuerystring can be a map (php array/hashtable), and then it will use the map instead of querying the database....
// This helps in making multiple calls when you need separate arrays for each parent node's children.
// Might be too much of a secret hack though - at least the var name should probably be changed
/**
* &fetch_children
*
* @param mixed $inQueryString
* @param mixed $rootNode
* @param string $idField
* @param string $parentField
* @access public
* @return void
*/
function &fetch_children( $inQueryString, $rootNode, $idField = "id", $parentField = "parent")
{
// get the set of rows that we are dealing with. It shoudld contain all of the rows that could possibly
// end up as nodes in the tree
if(is_array($inQueryString))
{
$objects = $inQueryString;
}
else
{
//markprofile();
$objects = $this->fetch_map($inQueryString, $idField);
//markprofile();
}
//markprofile();
if(is_array($rootNode))
{
foreach($rootNode as $node)
{
$children[$objects[$node][$idField]] = $objects[$node];
}
}
else
{
// get the id of the root node and and set it to the data for the root node
// in our result object (children)
$children[$objects[$rootNode][$idField]] = $objects[$rootNode];
}
//fixed point algorithm....
$done = false;
while(!$done)
{
$done = true;
foreach($objects as $object)
{
// if the db row has a parent and is not already in the tree
if(isset($children[$object[$parentField]]) && !isset($children[$object[$idField]]))
{
$done = false;
$children[$object[$idField]] = $object;
$keys = array_keys($children[$object[$parentField]]);
//fill in inherited properties from parents....
//is this a good idea?
//*
foreach($keys as $key)
{
if(!isset($children[$object[$idField]][$key]))
{
$children[$object[$idField]][$key] = $children[$object[$parentField]][$key];
}
}
//*/
}
}
}
//markprofile();
return $children;
}
// inQuerystring can be a map (php array/hashtable), and then it will use the map instead of querying the database....
// This helps in making multiple calls when you need separate arrays for each parent node's children.
// Might be too much of a secret hack though - at least the var name should probably be changed
/**
* &better_fetch_children
*
* @param mixed $inQueryString
* @param mixed $rootNode
* @param string $idField
* @param string $parentField
* @param mixed $depth
* @access public
* @return void
*/
function &better_fetch_children( $inQueryString, $rootNode, $idField = "id", $parentField = "parent", $depth = -1)
{
// get the set of rows that we are dealing with. It shoudld contain all of the rows that could possibly
// end up as nodes in the tree
if(is_array($inQueryString))
{
$objects = $inQueryString;
}
else
{
$objects = $this->prepare_tree_query($inQueryString, $idField);
}
if(is_array($rootNode))
{
foreach($rootNode as $node)
{
$children[$node] = $objects[$idField][$node];
}
}
else
{
// get the id of the root node and and set it to the data for the root node
// in our result object (children)
$children[$rootNode] = $objects[$idField][$rootNode];
}
foreach($children as $id => $node)
{
$this->_fetch_children($children, $objects, $id, $idField, $parentField, $depth);
}
//markprofile();
//echo_r($children);
return $children;
}
/**
* _fetch_children
*
* @param mixed $children
* @param mixed $objects
* @param mixed $id
* @param mixed $depth
* @access protected
* @return void
*/
function _fetch_children(&$children, &$objects, $id, $idField, $parentField, $depth = -1)
{
if(isset($objects[$parentField][$id]) && ($depth != 0))
{
foreach($objects[$parentField][$id] as $index => $node)
{
$children[$node[$idField]] = $node;
$this->_fetch_children($children, $objects, $node[$idField], $idField, $parentField, $depth - 1);
}
}
}
/**
* &fetch_parents
*
* @param mixed $inQueryString
* @param mixed $leafNode
* @param string $idField
* @param string $parentField
* @access public
* @return void
*/
function &fetch_parents($inQueryString, $leafNode, $idField = "id", $parentField = "parent")
{
// get the set of rows that we are dealing with. It should contain all of the rows that could possibly
// end up in the parent chain
if(!is_array($inQueryString))
$objects = $this->fetch_map($inQueryString, $idField);
else
$objects = $inQueryString['id'];
// set up the first node, we will go up from here
$parents[$leafNode] = $objects[$leafNode];
// walk up the tree to the root
$nextParent = $objects[$leafNode][$parentField];
while(isset($objects[$nextParent]) && $objects[$nextParent] != NULL && !isset($parents[$nextParent]))
{
$parents[$objects[$nextParent][$idField]] = $objects[$nextParent];
$nextParent = $objects[$nextParent][$parentField];
}
return $parents;
}
/**
* get_table_info
*
* @param mixed $inTable
* @access public
* @return void
*/
function get_table_info($inTable)
{
$result = $this->db->tableinfo($inTable);
if(db::isError($result))
{
$this->error($result);
}
return $result;
}
/**
* escape_string
*
* @param mixed $inString
* @access public
* @return void
*/
function escape_string($inString)
{
return $this->db->quoteSmart($inString);
}
/**
* escape_identifier
*
* @param mixed $inString
* @access public
* @return void
*/
function escape_identifier($inString)
{
return $this->db->quoteIdentifier($inString);
}
/**
* escape_tablename
*
* @param mixed $inString
* @access public
* @return void
*/
function escape_tablename($inString)
{
$name = explode(".", $inString);
foreach($name as $part)
{
$newname[] = $this->db->quoteIdentifier($part);
}
return implode('.', $newname);
}
}
?>