Location: PHPKode > scripts > SQL Solution > convissor-sql_solution-f53b170/SQLSolution/PostgreSQLSpecifics.php
<?php

/**
 * SQL Solution's PostgreSQL specific code
 *
 * @package SQLSolution
 * @author Daniel Convissor <hide@address.com>
 * @copyright The Analysis and Solutions Company, 2001-2011
 * @license http://www.analysisandsolutions.com/software/license.htm Simple Public License
 * @link http://www.analysisandsolutions.com/software/sql/sql.htm
 * @see SQLSolution/General.php
 */

/**
 * SQL Solution's PostgreSQL specific methods
 *
 * @package SQLSolution
 * @author Daniel Convissor <hide@address.com>
 * @copyright The Analysis and Solutions Company, 2001-2011
 * @license http://www.analysisandsolutions.com/software/license.htm Simple Public License
 * @link http://www.analysisandsolutions.com/software/sql/sql.htm
 */
class SQLSolution_PostgreSQLSpecifics extends SQLSolution_Customizations {
	/**
	 * The row counter
	 * @var integer
	 */
	public $SQLPgRow = 0;

	/*
	 * C O N N E C T I O N      S E C T I O N
	 */

	/**
	 * Establishes a connection to the database server
	 *
	 * @return void
	 *
	 * @uses SQLSolution_PostgreSQLUser  for the authentication information
	 * @link http://www.SqlSolution.info/sql-man.htm#Connect
	 */
	public function Connect($FileName, $FileLine) {
		ini_set('track_errors', 1);
		$php_errormsg = '';

		$Connect = '';
		if ($this->SQLHost) {
			$Connect .= 'host=' . $this->SQLHost;
		}
		if ($this->SQLPort) {
			$Connect .= ' port=' . $this->SQLPort;
		}
		if ($this->SQLDbName) {
			$Connect .= " dbname='" . addslashes($this->SQLDbName) . "'";
		}
		if ($this->SQLUser) {
			$Connect .= " user='" . addslashes($this->SQLUser) . "'";
		}
		if ($this->SQLPassword) {
			$Connect .= " password='" . addslashes($this->SQLPassword) . "'";
		}
		if ($this->SQLOptions) {
			$Connect .= ' options=' . $this->SQLOptions;
		}
		if ($this->SQLTTY) {
			$Connect .= ' tty=' . $this->SQLTTY;
		}
		$this->SQLConnection = @pg_connect($Connect)
				or die ($this->KillQuery($FileName, $FileLine, $php_errormsg));
	}

	/**
	 * Establishes a persistent connection to the database server
	 *
	 * @return void
	 *
	 * @uses SQLSolution_PostgreSQLUser  for the authentication information
	 * @link http://www.SqlSolution.info/sql-man.htm#PersistentConnect
	 */
	public function PersistentConnect($FileName, $FileLine) {
		ini_set('track_errors', 1);
		$Connect = '';
		if ($this->SQLHost) {
			$Connect .= 'host=' . $this->SQLHost;
		}
		if ($this->SQLPort) {
			$Connect .= ' port=' . $this->SQLPort;
		}
		if ($this->SQLDbName) {
			$Connect .= " dbname='" . addslashes($this->SQLDbName) . "'";
		}
		if ($this->SQLUser) {
			$Connect .= " user='" . addslashes($this->SQLUser) . "'";
		}
		if ($this->SQLPassword) {
			$Connect .= " password='" . addslashes($this->SQLPassword) . "'";
		}
		if ($this->SQLOptions) {
			$Connect .= ' options=' . $this->SQLOptions;
		}
		if ($this->SQLTTY) {
			$Connect .= ' tty=' . $this->SQLTTY;
		}
		$this->SQLConnection = @pg_pconnect($Connect)
				or die ($this->KillQuery($FileName, $FileLine, $php_errormsg));
	}

	/**
	 * This extension doesn't have this feature; calls are
	 * forwarded to Connect()
	 *
	 * @return void
	 *
	 * @link http://www.SqlSolution.info/sql-man.htm#ObtainHandle
	 */
	public function ObtainHandle($FileName, $FileLine) {
		$this->Connect($FileName, $FileLine);
	}

	/**
	 * Closes the current database server connection
	 *
	 * @return void
	 *
	 * @link http://www.SqlSolution.info/sql-man.htm#Disconnect
	 */
	public function Disconnect($FileName, $FileLine) {
		if ($this->CheckConnection()) {
			@pg_close($this->SQLConnection);
		}
		$this->SQLConnection = null;
	}

	/**
	 * Determines if a database connection exists
	 *
	 * @return bool  does a connection exist?
	 *
	 * @since Method available since release 7.0
	 */
	public function CheckConnection() {
		if (is_resource($this->SQLConnection)
			&& strpos(get_resource_type($this->SQLConnection), 'pgsql link') !== false)
		{
			return true;
		} else {
			return false;
		}
	}


	/*
	 * Q U E R Y      S E C T I O N
	 */


	/**
	 * Executes $this->SQLQueryString
	 *
	 * Creates a database connection if one doesn't exist yet.
	 *
	 * @return void
	 *
	 * @link http://www.SqlSolution.info/sql-man.htm#RunQuery
	 */
	public function RunQuery($FileName, $FileLine) {
		if (empty($this->SQLQueryString)) {
			$this->KillQuery($FileName, $FileLine, 'Must set SQLQueryString first');
		}

		if (!$this->CheckConnection()) {
			$this->Connect($FileName, $FileLine);
		}

		$php_errormsg = '';
		$this->SQLPgRow = 0;
		$this->SQLRecordSetFieldCount = 0;
		$this->SQLRecordSetRowCount = 0;

		$this->SQLRecordSet = @pg_exec($this->SQLConnection, $this->SQLQueryString);
		if ($this->SQLRecordSet) {
			if (!$this->SQLRecordSetFieldCount = @pg_numfields($this->SQLRecordSet)) {
				$this->SQLRecordSetFieldCount = 0;
			}
			if (!$this->SQLRecordSetRowCount = @pg_numrows($this->SQLRecordSet)) {
				$this->SQLRecordSetRowCount = 0;
			}

		} elseif ($php_errormsg == '') {
			// Probably a database error.
			$this->KillQuery($FileName, $FileLine,
							 @pg_errormessage($this->SQLConnection));
		} else {
			// Some PHP error.  Probably a bad Connection.  Complain.
			$this->KillQuery($FileName, $FileLine, $php_errormsg);
		}
	}

	/**
	 * Executes $this->SQLQueryString and ensures the insert did not create
	 * duplicate records
	 *
	 * Creates a database connection if one doesn't exist yet.
	 *
	 * @return mixed  1 if the insert went well, null if not
	 *
	 * @link http://www.SqlSolution.info/sql-man.htm#RunQuery_NoDuplicates
	 */
	public function RunQuery_NoDuplicates($FileName, $FileLine) {
		if (empty($this->SQLQueryString)) {
			$this->KillQuery($FileName, $FileLine, 'Must set SQLQueryString first');
		}

		if (!$this->CheckConnection()) {
			$this->Connect($FileName, $FileLine);
		}

		$php_errormsg = '';
		$this->SQLPgRow = 0;
		$this->SQLRecordSetFieldCount = 0;
		$this->SQLRecordSetRowCount = 0;

		$this->SQLRecordSet = @pg_exec($this->SQLConnection, $this->SQLQueryString);
		if ($this->SQLRecordSet) {
			return 1;
		} else {
			$Msg = @pg_errormessage($this->SQLConnection);
			if (preg_match('/violates unique constraint/', $Msg)) {
				// Couldn't insert/update record due to duplicate key.
			} else {
				$this->KillQuery($FileName, $FileLine, $Msg);
			}
		}
	}

	/**
	 * Frees the current query result
	 *
	 * @return void
	 *
	 * @link http://www.SqlSolution.info/sql-man.htm#ReleaseRecordSet
	 */
	public function ReleaseRecordSet($FileName, $FileLine) {
		@pg_freeresult($this->SQLRecordSet);
		$this->SQLRecordSet = null;
	}


	/*
	 * F I E L D      D E F I N I T I O N S      S E C T I O N
	 */


	/**
	 * Returns the name of the column at the specified offset in the current
	 * result set
	 *
	 * @return string  the column's name
	 *
	 * @link http://www.SqlSolution.info/sql-man.htm#FieldName
	 */
	public function FieldName($FileName, $FileLine, $FieldNumber) {
		$php_errormsg = '';

		$Output = @pg_fieldname($this->SQLRecordSet, $FieldNumber);
		if ($Output) {
			return $Output;
		} else {
			$this->KillQuery($FileName, $FileLine, $php_errormsg);
		}
	}

	/**
	 * Returns the data type of the column at the specified offset in the
	 * current result set
	 *
	 * @return string  the column's data type
	 *
	 * @link http://www.SqlSolution.info/sql-man.htm#FieldType
	 */
	public function FieldType($FileName, $FileLine, $FieldNumber) {
		$php_errormsg = '';

		$Output = @pg_fieldtype($this->SQLRecordSet, $FieldNumber);
		if ($Output) {
			return $Output;
		} else {
			$this->KillQuery($FileName, $FileLine, $php_errormsg);
		}
	}

	/**
	 * Returns the size of the column at the specified offset in the
	 * current result set
	 *
	 * @return string  the column's size
	 *
	 * @link http://www.SqlSolution.info/sql-man.htm#FieldLength
	 */
	public function FieldLength($FileName, $FileLine, $FieldNumber) {
		$php_errormsg = '';

		$Output = @pg_fieldsize($this->SQLRecordSet, $FieldNumber);
		if ($Output) {
			return $Output;
		} else {
			$this->KillQuery($FileName, $FileLine, $php_errormsg);
		}
	}


	/*
	 * R E C O R D      D A T A      S E C T I O N
	 */


	/**
	 * Places the next record's data into an associative array
	 *
	 * @param array $SkipSafeMarkup  an array of field names to not parse
	 *                               safe markup on
	 *
	 * @return array  an associative array containing the record's data
	 *
	 * @link http://www.SqlSolution.info/sql-man.htm#RecordAsAssocArray
	 */
	public function RecordAsAssocArray($FileName, $FileLine, $SkipSafeMarkup = array()) {
		if (empty($this->SQLRecordSet)) {
			return null;
		}

		if ($this->SQLPgRow < $this->SQLRecordSetRowCount) {
			$php_errormsg = '';

			$Row = @pg_fetch_array($this->SQLRecordSet, $this->SQLPgRow, PGSQL_ASSOC);
			if ($Row) {
				return $this->processRow($Row, $SkipSafeMarkup, $this->SQLPgRow);
			} elseif ($php_errormsg != '') {
				$this->KillQuery($FileName, $FileLine, $php_errormsg);
			}
		}
	}

	/**
	 * Places the next record's data into an enumerated array
	 *
	 * @param array $SkipSafeMarkup  an array of field numbers (starting at 0)
	 *                               to not parse safe markup on
	 *
	 * @return array  an enumerated array containing the record's data
	 *
	 * @link http://www.SqlSolution.info/sql-man.htm#RecordAsEnumArray
	 */
	public function RecordAsEnumArray($FileName, $FileLine, $SkipSafeMarkup = array()) {
		if (empty($this->SQLRecordSet)) {
			return null;
		}

		if ($this->SQLPgRow < $this->SQLRecordSetRowCount) {
			$php_errormsg = '';

			$Row = @pg_fetch_array($this->SQLRecordSet, $this->SQLPgRow, PGSQL_NUM);
			if ($Row) {
				return $this->processRow($Row, $SkipSafeMarkup, $this->SQLPgRow);
			} elseif ($php_errormsg != '') {
				$this->KillQuery($FileName, $FileLine, $php_errormsg);
			}
		}
	}

	/**
	 * Returns the auto increment ID from the last record inserted
	 *
	 * @return int  the insert id
	 *
	 * @link http://www.SqlSolution.info/sql-man.htm#InsertID
	 */
	public function InsertID($FileName, $FileLine, $Table = '', $Field = '',
			$Where = '', $Sequence = '')
	{
		$this->SQLQueryString = "SELECT CURRVAL('$Sequence')";
		$this->RunQuery(__FILE__, __LINE__);
		list($Val) = $this->RecordAsEnumArray(__FILE__, __LINE__);
		return $Val;
	}

	/**
	 * Moves the internal pointer to the specified row in a result set
	 *
	 * @return void
	 *
	 * @link http://www.SqlSolution.info/sql-man.htm#GoToRecord
	 */
	public function GoToRecord($FileName, $FileLine, $Row = 0) {
		$this->SQLPgRow = $Row;
	}

	/**
	 * Makes input safe for use as a value in queries
	 *
	 * Surrounds the string with quote marks.  If the value is NULL, change it
	 * to the unquoted string "NULL".
	 *
	 * @param mixed $Value  the value to be escaped
	 *
	 * @return string  the escaped string
	 */
	public function Escape($FileName, $FileLine, $Value) {
		if (!$this->CheckConnection()) {
			$this->Connect($FileName, $FileLine);
		}

		if ($Value === null) {
			return 'NULL';
		} else {
			return "'" . pg_escape_string($this->SQLConnection, $Value) . "'";
		}
	}
}
Return current item: SQL Solution