1		<?php
			2		/*
			3		V1.40 19 September 2001 (c) 2000, 2001 John Lim (jlim@natsoft.com.my). All rights reserved.
			4		Released under both BSD license and Lesser GPL library license.
			5		Whenever there is any discrepancy between the two licenses,
			6		the BSD license will take precedence.
			7		Set tabs to 8.
			8
			9		Original version derived from Alberto Cerezal (acerezalp@dbnet.es) - DBNet Informatica & Comunicaciones.
			10		08 Nov 2000 jlim - Minor corrections, removing mysql stuff
			11		09 Nov 2000 jlim - added insertid support suggested by "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
			12		jlim - changed concat operator to || and data types to MetaType to match documented pgsql types
			13		see http://www.postgresql.org/devel-corner/docs/postgres/datatype.htm
			14		22 Nov 2000 jlim - added changes to FetchField() and MetaTables() contributed by "raser" <raser@mail.zen.com.tw>
			15		27 Nov 2000 jlim - added changes to _connect/_pconnect from ideas by "Lennie" <leen@wirehub.nl>
			16		15 Dec 2000 jlim - added changes suggested by Additional code changes by "Eric G. Werk" egw@netguide.dk.
			17		31 Jan 2001 jlim - finally installed postgresql. testing
			18		01 Mar 2001 jlim - Freek Dijkstra changes, also support for text type
			19		*/
			20
			21		class ADODB_postgres extends ADODBConnection{
			22		var $databaseType = 'postgres';
			23		var $hasInsertID = true;
			24		var $_resultid = false;
			25		var $concat_operator='||';
			26		var $metaTablesSQL = "select tablename from pg_tables where tablename not like 'pg_%' order by 1";
			27
			28		/*
			29		# show tables and views suggestion
			30		"SELECT c.relname AS tablename FROM pg_class c
			31		WHERE (c.relhasrules AND (EXISTS (
			32		SELECT r.rulename FROM pg_rewrite r WHERE r.ev_class = c.oid AND bpchar(r.ev_type) = '1'
			33		))) OR (c.relkind = 'v') AND c.relname NOT LIKE 'pg_%'
			34		UNION
			35		SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%' ORDER BY 1"
			36		*/
			37		var $metaColumnsSQL = "SELECT a.attname,t.typname,a.attlen,a.atttypmod,a.attnotnull FROM pg_class c, pg_attribute a,pg_type t WHERE relkind = 'r' AND c.relname='%s' AND a.attnum > 0 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
			38		// get primary key etc -- from Freek Dijkstra
			39		var $metaKeySQL = "SELECT ic.relname AS index_name, a.attname AS column_name,i.indisunique AS unique_key, i.indisprimary AS primary_key FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a WHERE bc.oid = i.indrelid AND ic.oid = i.indexrelid AND (i.indkey[0] = a.attnum OR i.indkey[1] = a.attnum OR i.indkey[2] = a.attnum OR i.indkey[3] = a.attnum OR i.indkey[4] = a.attnum OR i.indkey[5] = a.attnum OR i.indkey[6] = a.attnum OR i.indkey[7] = a.attnum) AND a.attrelid = bc.oid AND bc.relname = '%s'";
			40
			41		var $_hastrans = false;
			42		var $hasAffectedRows = false;
			43		var $hasTop = false;
			44		var $hasLimit = false;  // set to true for pgsql 7 only. support pgsql/mysql SELECT * FROM TABLE LIMIT 10
			45		// below suggested by Freek Dijkstra
			46		var $true = 't';                // string that represents TRUE for a database
			47		var $false = 'f';               // string that represents FALSE for a database
			48		var $fmtDate = "'Y-m-d'";       // used by DBDate() as the default date format used by the database
			49		var $fmtTimeStamp = "'Y-m-d h:i:s'"; // used by DBTimeStamp as the default timestamp fmt.
			50		var $hasMoveFirst = true;
			51		var $hasGenID = true;
			52		var $_genIDSQL = "SELECT NEXTVAL('%s')";
			53		var $_genSeqSQL = "CREATE SEQUENCE %s";
			54
			55		// The last (fmtTimeStamp is not entirely correct:
			56		// PostgreSQL also has support for time zones,
			57		// and writes these time in this format: "2001-03-01 18:59:26+02".
			58		// There is no code for the "+02" time zone information, so I just left that out.
			59		// I'm not familiar enough with both ADODB as well as Postgres
			60		// to know what the concequences are. The other values are correct (wheren't in 0.94)
			61		// -- Freek Dijkstra
			62
			63		function ADODB_postgres()
			64		{
			65		}
			66
			67		/* Warning from http://www.php.net/manual/function.pg-getlastoid.php:
			68		Using a OID as a unique identifier is not generally wise.
			69		Unless you are very careful, you might end up with a tuple having
			70		a different OID if a database must be reloaded. */
			71		function _insertid()
			72		{
			73		return pg_getlastoid($this->_resultid);
			74		}
			75
			76		// I get this error ??? - jlim
			77		// Warning: This compilation does not support pg_cmdtuples() in d:/inetpub/wwwroot/php/adodb/adodb-postgres.inc.php on line 44
			78		function _Affected_Rows()
			79		{
			80		//print_r($this->_resultid);
			81		return pg_cmdtuples($this->_resultid);
			82		}
			83
			84
			85		// returns true/false
			86		function BeginTrans()
			87		{
			88		$this->_hastrans = true;
			89		return @pg_Exec($this->_connectionID, "begin");
			90		}
			91
			92		// returns true/false.
			93		function CommitTrans()
			94		{
			95		$this->_hastrans = false;
			96		return @pg_Exec($this->_connectionID, "commit");
			97		}
			98
			99		// returns true/false
			100		function RollbackTrans()
			101		{
			102		$this->_hastrans = false;
			103		return @pg_Exec($this->_connectionID, "rollback");
			104		}
			105
			106		// converts table to lowercase
			107		function &MetaColumns($table)
			108		{
			109		if (!empty($this->metaColumnsSQL)) {
			110		// the following is the only difference -- we lowercase it
			111		$rs = $this->Execute(sprintf($this->metaColumnsSQL,strtolower($table)));
			112		if ($rs === false) return false;
			113
			114		if (!empty($this->metaKeySQL)) {
			115		// If we want the primary keys, we have to issue a separate query
			116		// Of course, a modified version of the metaColumnsSQL query using a
			117		// LEFT JOIN would have been much more elegant, but postgres does
			118		// not support OUTER JOINS. So here is the clumsy way.
			119		$rskey = $this->Execute(sprintf($this->metaKeySQL,strtolower($table)));
			120		// fetch all result in once for performance.
			121		$keys = $rskey->GetArray();
			122		$rskey->Close();
			123		unset($rskey);
			124		}
			125
			126		$retarr = array();
			127		while (!$rs->EOF) { //print_r($rs->fields);
			128		$fld = new ADODBFieldObject();
			129		$fld->name = $rs->fields[0];
			130		$fld->type = $rs->fields[1];
			131		$fld->max_length = $rs->fields[2];
			132		if ($fld->max_length <= 0) $fld->max_length = $rs->fields[3]-4;
			133		if ($fld->max_length <= 0) $fld->max_length = -1;
			134
			135		//Freek
			136		if ($rs->fields[4] == $this->true) {
			137		$fld->not_null = true;
			138		}
			139
			140		// Freek
			141		if (is_array($keys)) {
			142		reset ($keys);
			143		while (list($x,$key) = each($keys)) {
			144		if ($fld->name == $key['column_name'] AND $key['primary_key'] == $this->true)
			145		$fld->primary_key = true;
			146		if ($fld->name == $key['column_name'] AND $key['unique_key'] == $this->true)
			147		$fld->unique = true; // What name is more compatible?
			148		}
			149		}
			150
			151		$retarr[strtoupper($fld->name)] = $fld;
			152
			153		$rs->MoveNext();
			154		}
			155		$rs->Close();
			156		return $retarr;
			157		}
			158		return false;
			159		}
			160
			161
			162		// returns true or false
			163		//
			164		// examples:
			165		//      $db->Connect("host=host1 user=user1 password=secret port=4341");
			166		//      $db->Connect('host1','user1','secret');
			167		function _connect($str,$user='',$pwd='',$db='')
			168		{
			169		if ($user || $pwd || $db) {
			170		if ($str)  {
			171		$host = split(":", $str);
			172		if ($host[0]) $str = "host=$host[0]";
			173		else $str = 'localhost';
			174		if (isset($host[1])) $str .= " port=$host[1]";
			175		}
			176		if ($user) $str .= " user=".$user;
			177		if ($pwd)  $str .= " password=".$pwd;
			178		if ($db)   $str .= " dbname=".$db;
			179		}
			180
			181		//if ($user) $linea = "user=$user host=$linea password=$pwd dbname=$db port=5432";
			182		$this->_connectionID = pg_connect($str);
			183		if ($this->_connectionID === false) return false;
			184		$this->Execute("set datestyle='ISO'");
			185		return true;
			186		}
			187
			188		// returns true or false
			189		//
			190		// examples:
			191		//      $db->PConnect("host=host1 user=user1 password=secret port=4341");
			192		//      $db->PConnect('host1','user1','secret');
			193		function _pconnect($str,$user='',$pwd='',$db='')
			194		{
			195		if ($user || $pwd || $db) {
			196		if ($str)  {
			197		$host = split(":", $str);
			198		if ($host[0]) $str = "host=$host[0]";
			199		else $str = 'localhost';
			200		if (isset($host[1])) $str .= " port=$host[1]";
			201		}
			202		if ($user) $str .= " user=".$user;
			203		if ($pwd)  $str .= " password=".$pwd;
			204		if ($db)   $str .= " dbname=".$db;
			205		}
			206		$this->_connectionID = pg_pconnect($str);
			207		if ($this->_connectionID === false) return false;
			208		$this->Execute("set datestyle='ISO'");
			209		return true;
			210		}
			211
			212		// returns queryID or false
			213		function _query($sql,$inputarr)
			214		{
			215		$this->_resultid= pg_Exec($this->_connectionID,$sql);
			216		return $this->_resultid;
			217		}
			218
			219
			220		/*      Returns: the last error message from previous database operation        */
			221		function ErrorMsg() {
			222		$this->_errorMsg = @pg_errormessage($this->_connectionID);
			223		return $this->_errorMsg;
			224		}
			225
			226		// returns true or false
			227		function _close()
			228		{
			229		if ($this->_hastrans) $this->RollbackTrans();
			230		@pg_close($this->_connectionID);
			231		return true;
			232		}
			233
			234		}
			235
			236		/*--------------------------------------------------------------------------------------
			237		Class Name: Recordset
			238		--------------------------------------------------------------------------------------*/
			239
			240		class ADORecordSet_postgres extends ADORecordSet{
			241
			242		var $databaseType = "postgres";
			243		var $canSeek = true;
			244		var $fetchMode;
			245		function ADORecordSet_postgres($queryID) {
			246		global $ADODB_FETCH_MODE;
			247
			248		switch ($ADODB_FETCH_MODE)
			249		{
			250		case ADODB_FETCH_NUM: $this->fetchMode = PGSQL_NUM; break;
			251		case ADODB_FETCH_ASSOC:$this->fetchMode = PGSQL_ASSOC; break;
			252		default:
			253		case ADODB_FETCH_DEFAULT:
			254		case ADODB_FETCH_BOTH:$this->fetchMode = PGSQL_BOTH; break;
			255		}
			256
			257		$this->ADORecordSet($queryID);
			258		}
			259
			260		function _initrs()
			261		{
			262		global $ADODB_COUNTRECS;
			263		$this->_numOfRows = ($ADODB_COUNTRECS)? @pg_numrows($this->_queryID):-1;
			264		$this->_numOfFields = @pg_numfields($this->_queryID);
			265		}
			266
			267		function &FetchField($fieldOffset = 0)
			268		{
			269		$off=$fieldOffset; // offsets begin at 0
			270
			271		$o= new ADODBFieldObject();
			272		$o->name = @pg_fieldname($this->_queryID,$off);
			273		$o->type = @pg_fieldtype($this->_queryID,$off);
			274		$o->max_length = @pg_fieldsize($this->_queryID,$off);
			275		//print_r($o);
			276		//print "off=$off name=$o->name type=$o->type len=$o->max_length<br>";
			277		return $o;
			278		}
			279
			280		function _seek($row)
			281		{
			282		return @pg_fetch_row($this->_queryID,$row);
			283		}
			284
			285		// 10% speedup to move MoveNext to child class
			286		function MoveNext($ignore_fields=false)
			287		{
			288		if ($this->_numOfRows != 0 && !$this->EOF) {
			289		$this->_currentRow++;
			290		$this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
			291		if (is_array($this->fields)) return true;
			292		}
			293		$this->EOF = true;
			294		return false;
			295		}
			296		function _fetch($ignore_fields=false)
			297		{
			298		$this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
			299		return (is_array($this->fields));
			300		}
			301
			302		function _close() {
			303		return @pg_freeresult($this->_queryID);
			304		}
			305
			306		function MetaType($t,$len=-1,$fieldobj=false)
			307		{
			308		switch (strtoupper($t)) {
			309		case 'CHAR':
			310		case 'CHARACTER':
			311		case 'VARCHAR':
			312		case 'NAME':
			313		case 'BPCHAR':
			314		if ($len <= $this->blobSize) return 'C';
			315
			316		case 'TEXT':
			317		return 'X';
			318
			319		case 'IMAGE': // user defined type
			320		case 'BLOB': // user defined type
			321		case 'BIT': // This is a bit string, not a single bit, so don't return 'L'
			322		case 'VARBIT':
			323		case 'BYTEA':
			324		return 'B';
			325
			326		case 'BOOL':
			327		case 'BOOLEAN':
			328		return 'L';
			329
			330		case 'DATE':
			331		return 'D';
			332
			333		case 'TIME':
			334		case 'DATETIME':
			335		case 'TIMESTAMP':
			336		return 'T';
			337
			338		case 'SMALLINT':
			339		case 'BIGINT':
			340		case 'INTEGER':
			341		case 'INT8':
			342		case 'INT4':
			343		case 'INT2':
			344		if (isset($fieldobj) &&
			345		empty($fieldobj->primary_key) && empty($fieldobj->unique)) return 'I';
			346
			347		case 'OID':
			348		case 'SERIAL':
			349		return 'R';
			350
			351		default:
			352		return 'N';
			353		}
			354		}
			355
			356		}
			357		?>