About
News
Downloads
Manual
Support
Examples
Releases
Community
Contact
Blog
Sources for file plugins/database/postgresql.php in version 4.0 Beta 1
Click on a comment to hide it. Click here to show all comments.
/**
* Project: Xnyo 4: Bubbles
* File: plugins/database/pgsql.php
*
* Version: 4.0-dev
* SVN Id: $Id: postgresql.php 5 2007-05-18 03:49:07Z bok $
* SVN URL: $HeadURL:
http://svn.syd.wholesalebroadband.com.au/xnyo/trunk/plugins/database/postgresql.php $
* Authors: Robert Amos <bok[at]odynia.org>
*
* Copyright (c) 2001-2007 Robert Amos <bok[at]odynia.org>
*
* Permission to use, copy, modify, and distribute this software for any
* purpose with or without fee is hereby granted, provided that the above
* copyright notice and this permission notice appear in all copies.
*
* THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
* WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
* MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
* ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
* WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
* ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
* OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
**/
class XnyoDatabasePostgreSQL implements XnyoDatabasePlugin
{
private $resource;
private $info;
public $statement;
/**
* Constructor
**/
public function __construct ($db, PDO $pdo)
{
// set the resource
$this->resource = $pdo;
$this->info = $db;
}
/**
* Get Columns
*
* Gets a list of all the columns and their types for a specified table
**/
public function getColumns($table)
{
// this query will get all the columns in a specified table as "attname" and "format_type" - trust
me
$sql = 'select a.attname AS name, a.attnotnull AS notnull, format_type(a.atttypid, atttypmod) AS
type FROM pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid)
INNER JOIN pg_catalog.pg_attribute a ON (a.attrelid = c.oid) WHERE n.nspname = \'public\' AND
c.relname = \''.$table.'\' AND a.attisdropped = false AND a.attnum > 0;';
$r = $this->resource->query($sql);
// store the results :)
$results = array();
while ($t = $r->fetchObject())
{
$t->type = $this->translateColType($t->type);
$results[$t->name] = array('type' => $t->type, 'notnull' => $t->notnull);
}
return $results;
}
/**
* Translate Col Type
*
* Translate the PGSQL defined column type to a PDO one
**/
public function translateColType ($type)
{
switch ($type)
{
case 'int':
case 'integer':
case 'int4':
case 'int8':
case 'bigint':
case 'bigserial':
case 'serial4':
case 'serial8':
case 'smallint':
case 'int2':
return XnyoDB::INT;
case 'text':
case 'character varying':
case 'varchar':
case 'cidr':
case 'macaddr':
return XnyoDB::TEXT;
case 'character':
case 'char':
return XnyoDB::CHAR;
case 'bit':
case 'bit varying':
case 'varbit':
return XnyoDB::BIT;
case 'boolean':
case 'bool':
return XnyoDB::BOOL;
case 'float':
case 'real':
case 'float4':
case 'money':
case 'double precision':
case 'float8':
return XnyoDB::FLOAT;
default:
return XnyoDB::TEXT;
}
}
/**
* PDO-based Methods
**/
/**
* Begins a Transaction
*
* http://php.net/pdo-begintransaction
**/
public function beginTransaction ()
{
return $this->resource->beginTransaction();
}
/**
* Commits the current transaction
*
* http://php.net/pdo-commit
**/
public function commit ()
{
return $this->resource->commit();
}
/**
* Returns the SQLSTATE
*
* http://php.net/pdo-errorcode
**/
public function errorCode ()
{
return $this->resource->errorCode();
}
/**
* Return an array of error information about the last operation performed.
*
* http://php.net/pdo-errorinfo
**/
public function errorInfo()
{
return $this->resource->errorInfo();
}
/**
* Executes an SQL statement and returns the number of rows affected.
*
* http://php.net/pdo-exec
**/
public function exec ($s)
{
return $this->resource->exec($s);
}
/**
* Get a database connection attribute
*
* http://php.net/pdo-getattribute
**/
public function getAttribute ($i)
{
return $this->resource->getAttribute($i);
}
/**
* Get an array of the available PDO drivers
*
* http://php.net/pdo-getavailabledrivers
**/
public function getAvailableDrivers ()
{
return $this->resource->getAvailableDrivers;
}
/**
* Gets the ID of the last inserted row or sequence value
*
* http://php.net/pdo-lastinsertid
**/
public function lastInsertId ($table)
{
// postgres requires the sequence name, make one out of the table name if necessary
if (substr($table, -4) == '_seq')
$seq = $table;
else
$seq = strtolower($table).'_id_seq';
return $this->resource->lastInsertId($seq);
}
/**
* Prepares an SQL statement.
*
* http://php.net/pdo-prepare
**/
public function prepare ($s, $opt=null)
{
if (is_null($opt))
$this->statement = $this->resource->prepare($s);
else
$this->statement = $this->resource->prepare($s, $opt);
return $this->statement;
}
/**
* Executes an SQL statement
*
* http://php.net/pdo-query
**/
public function query ($s)
{
$args = func_get_args();
$this->statement = call_user_func_array(array($this->resource, 'query'), $args);
return $this->statement;
}
/**
* Quotes a string for use in a query
*
* http://php.net/pdo-quote
**/
public function quote ($s, $p=null)
{
if (is_null($p))
$p = PDO::PARAM_STR;
return $this->resource->quote($s, $p);
}
/**
* Rolls back a transaction
*
* http://php.net/pdo-rollback
**/
public function rollBack ()
{
return $this->resource->rollBack();
}
/**
* Set a connection attribute
*
* http://php.net/pdo-setattribute
**/
public function setAttribute ($a, $v)
{
return $this->resource->setAttribute($a, $v);
}
/**
* Bind a column to a PHP variable
*
* http://php.net/pdostatement-bindcolumn
**/
public function bindColumn ($column, $param, $type=null)
{
if (!is_object($this->statement))
throw new XnyoError('Unable to bind to column <b>%s</b> because there is no current PDOStatement
object. Use $db->prepare($sql);', $column);
if (is_null($type))
return $this->statement->bindColumn($column, $param);
return $this->statement->bindColumn($column, $param);
}
/**
* Bind a parameter to the specified variable name (as a reference)
*
* http://php.net/pdostatement-bindparam
**/
public function bindParam ($param, $var, $type=null, $length=null, $opt=null)
{
if (!is_object($this->statement))
throw new XnyoError('Unable to bind to parameter <b>%s</b> because there is no current
PDOStatement object. Use $db->prepare($sql);', $param);
// too many args to do them all!
$args = func_get_args();
return call_user_func_array(array($this->statement, 'bindParam'), $args);
}
/**
* Bind a parameter to the specified varaible's value (no reference)
*
* http://php.net/pdostatement-bindvalue
**/
public function bindValue ($param, $value, $type=null)
{
if (!is_object($this->statement))
throw new XnyoError('Unable to set value for column <b>%s</b> to <b>%s</b> because there is no
current PDOStatement object. Use $db->prepare($sql);', $param, $value);
if (is_null($type))
return $this->statement->bindValue($param, $value);
return $this->statement->bindValue($param, $value, $type);
}
/**
* Closes the cursor, enabling the statement to be executed again.
*
* http://php.net/pdostatement-closecursor
**/
public function closeCursor ()
{
if (!is_object($this->statement))
throw new XnyoError('Unable to close active cursor as there is no PDOStatement object to close it
on. Use $db->prepare($sql);');
return $this->statement->closeCursor();
}
/**
* Gets the number of columns in the result set
*
* http://php.net/pdostatement-columncount
**/
public function columnCount ()
{
if (!is_object($this->statement))
throw new XnyoError('Unable to count columns because there is no PDOStatement object. Use
$db->prepare($sql);');
return $this->statement->columnCount();
}
/**
* Gets the SQLSTATE associated with a statement
*
* http://php.net/pdostatement-errorcode
**/
public function errorCodeStatement()
{
if (!is_object($this->statement))
throw new XnyoError('Unable to get error code because there is no PDOStatement object. Use
$db->prepare($sql);');
return $this->statement->errorCode();
}
/**
* Gets an array of error information about the statement
*
* http://php.net/pdostatement-errorinfo
**/
public function errorInfoStatement ()
{
if (!is_object($this->statement))
throw new XnyoError('Unable to get error information because there is no PDOStatement object. Use
$db->prepare($sql);');
return $this->statement->errorInfo();
}
/**
* Executes a prepared statement
*
* http://php.net/pdostatement-execute
**/
public function execute ($input=null)
{
if (!is_object($this->statement))
throw new XnyoError('Unable to execute statement because there is no PDOStatement object. Use
$db->prepare($sql);');
if (is_null($input))
return $this->statement->execute();
return $this->statement->execute($input);
}
/**
* Fetches the next row from a result set
*
* http://php.net/pdostatement-fetch
**/
public function fetch ($style=null, $ori=null, $offset=null)
{
if (!is_object($this->statement))
throw new XnyoError('Unable to fetch next row because there is no PDOStatement object. Use
$db->prepare($sql);');
if (is_null($style))
$style = PDO::FETCH_BOTH;
if (is_null($ori))
$ori = PDO::FETCH_ORI_NEXT;
if (is_null($offset))
return $this->connecton->statement->fetch($style, $ori);
return $this->statement->fetch($style, $ori, $offset);
}
/**
* Returns an array containing all of the result set rows
*
* http://php.net/pdostatement-fetchall
**/
public function fetchAll ($style=null, $index=null)
{
if (!is_object($this->statement))
throw new XnyoError('Unable to fetch all results because there is no PDOStatement object. Use
$db->prepare($sql);');
if (is_null($style))
$style = PDO::FETCH_COLUMN;
if (is_null($index))
$index = 0;
return $this->statement->fetchAll($style, $index);
}
/**
* Returns a single column from the next row of a result set
*
* http://php.net/pdostatement-fetchcolumn
**/
public function fetchColumn ($col=null)
{
if (!is_object($this->statement))
throw new XnyoError('Unable to fetch column because there is no PDOStatement object. Use
$db->prepare($sql);');
if (is_null($col))
$col = 0;
return $this->statement->fetchColumn($col);
}
/**
* Fetches the next row and returns it as an object.
*
* http://php.net/pdostatement-fetchobject
**/
public function fetchObject ($class=null, $const_args=null)
{
if (!is_object($this->statement))
throw new XnyoError('Unable to fetch object because there is no PDOStatement object. Use
$db->prepare($sql);');
if (is_null($class))
$class = 'stdClass';
if (is_null($const_args))
$const_args = array();
return $this->statement->fetchObject($class, $const_args);
}
/**
* Get a statement attribute
*
* http://php.net/pdostatement-getattribute
**/
public function getAttributeStatement ($a)
{
if (!is_object($this->statement))
throw new XnyoError('Unable to get statement attribute <b>%s</b> because there is no PDOStatement
object. Use $db->prepare($sql);', $a);
return $this->statement->getAttribute($a);
}
/**
* Gets the column meta data for the specified column
*
* http://php.net/pdostatement-getcolumnmeta
**/
public function getColumnMeta ($col=null)
{
if (!is_object($this->statement))
throw new XnyoError('Unable to get column metadata because there is no PDOStatement object. Use
$db->prepare($sql);');
if (is_null($col))
$col = 0;
return $this->statement->getColumnMeta($col);
}
/**
* Advances to the next rowset in a multi-rowset statement handle
*
* http://php.net/pdostatement-nextrowset
**/
public function nextRowset ()
{
if (!is_object($this->statement))
throw new XnyoError('Unable to advance to the next rowset because there is no PDOStatement
object. Use $db->prepare($sql);');
return $this->statement->nextRowset();
}
/**
* Returns the number of rows affected by the last SQL Statement
*
* http://php.net/pdostatement-rowcount
**/
public function rowCount ()
{
if (!is_object($this->statement))
throw new XnyoError('Unable to count the number of rows because there is no PDOStatement object.
Use $db->prepare($sql);');
return $this->statement->rowCount();
}
/**
* Sets a statement attribute
*
* http://php.net/pdostatement-setattribute
**/
public function setAttributeStatement ($a, $v)
{
if (!is_object($this->statement))
throw new XnyoError('Unable to set statement attribute because there is no PDOStatement object.
Use $db->prepare($sql);');
return $this->statement->setAttribute($a, $v);
}
/**
* Set the default fetch mode for this statement
*
* http://php.net/pdostatement-setfetchmode
**/
public function setFetchMode ()
{
if (!is_object($this->statement))
throw new XnyoError('Unable to set default fetch mode because there is no PDOStatement object.
Use $db->prepare($sql);');
$args = func_get_args();
return call_user_func_array(array($this->statement, 'setFetchMode'), $args);
}
}
/**
* Connection Class - Creates a new Connection to a Database
**/
class XnyoDatabasePostgreSQLConnection implements XnyoDatabaseConnect
{
/**
* Information Store
*
* Stores the info about this database
**/
private $dsn;
/**
* Constructor
**/
public function __construct ($details)
{
// we're building the DSN in the constructor.
$this->dsn = 'pgsql:';
// no hostname? using unix sockets then
if (isset($details['host']))
{
$this->dsn .= 'host='.$details['host'].';';
if (isset($details['port']))
$this->dsn .= 'port='.$details['port'].';';
}
// database - only required argument
$this->dsn .= 'dbname='.$details['dbname'].';';
// username / password
if (isset($details['user']))
{
$this->dsn .= 'user='.$details['user'].';';
if (isset($details['pass']))
$this->dsn .= 'password='.$details['pass'].';';
}
// trim off the last semi-colon because we're pedantic ;)
$this->dsn = substr($this->dsn, 0, -1);
}
/**
* Connect to the database!
**/
public function connect ()
{
// we already have our DSN, what more do we need to know?
return new PDO($this->dsn);
}
}