<?php

/**
  V5.18 3 Sep 2012  (c) 2000-2012 John Lim (jlim#natsoft.com). All rights reserved.
  Released under both BSD license and Lesser GPL library license.
  Whenever there is any discrepancy between the two licenses,
  the BSD license will take precedence.

  Set tabs to 4 for best viewing.

*/

// security - hide paths
if (!defined('ADODB_DIR')) die();

class ADODB2_postgres extends ADODB_DataDict {

	var $databaseType = 'postgres';
	var $seqField = false;
	var $seqPrefix = 'SEQ_';
	var $addCol = ' ADD COLUMN';
	var $quote = '"';
	var $renameTable = 'ALTER TABLE %s RENAME TO %s'; // at least since 7.1
	var $dropTable = 'DROP TABLE %s CASCADE';

	function MetaType($t,$len=-1,$fieldobj=false)
	{
		if (is_object($t)) {
			$fieldobj = $t;
			$t = $fieldobj->type;
			$len = $fieldobj->max_length;
		}
		$is_serial = is_object($fieldobj) && !empty($fieldobj->primary_key) && !empty($fieldobj->unique) &&
			!empty($fieldobj->has_default) && substr($fieldobj->default_value,0,8) == 'nextval(';

		switch (strtoupper($t)) {
			case 'INTERVAL':
			case 'CHAR':
			case 'CHARACTER':
			case 'VARCHAR':
			case 'NAME':
			case 'BPCHAR':
				if ($len <= $this->blobSize) return 'C';

			case 'TEXT':
				return 'X';

			case 'IMAGE': // user defined type
			case 'BLOB': // user defined type
			case 'BIT':	// This is a bit string, not a single bit, so don't return 'L'
			case 'VARBIT':
			case 'BYTEA':
				return 'B';

			case 'BOOL':
			case 'BOOLEAN':
				return 'L';

			case 'DATE':
				return 'D';

			case 'TIME':
			case 'DATETIME':
			case 'TIMESTAMP':
			case 'TIMESTAMPTZ':
				return 'T';

			case 'INTEGER': return !$is_serial ? 'I' : 'R';
			case 'SMALLINT':
			case 'INT2': return !$is_serial ? 'I2' : 'R';
			case 'INT4': return !$is_serial ? 'I4' : 'R';
			case 'BIGINT':
			case 'INT8': return !$is_serial ? 'I8' : 'R';

			case 'OID':
			case 'SERIAL':
				return 'R';

			case 'FLOAT4':
			case 'FLOAT8':
			case 'DOUBLE PRECISION':
			case 'REAL':
				return 'F';

			 default:
			 	return 'N';
		}
	}

 	function ActualType($meta)
	{
		switch($meta) {
		case 'C': return 'VARCHAR';
		case 'XL':
		case 'X': return 'TEXT';

		case 'C2': return 'VARCHAR';
		case 'X2': return 'TEXT';

		case 'B': return 'BYTEA';

		case 'D': return 'DATE';
		case 'TS':
		case 'T': return 'TIMESTAMP';

		case 'L': return 'BOOLEAN';
		case 'I': return 'INTEGER';
		case 'I1': return 'SMALLINT';
		case 'I2': return 'INT2';
		case 'I4': return 'INT4';
		case 'I8': return 'INT8';

		case 'F': return 'FLOAT8';
		case 'N': return 'NUMERIC';
		default:
			return $meta;
		}
	}

	/**
	 * Adding a new Column
	 *
	 * reimplementation of the default function as postgres does NOT allow to set the default in the same statement
	 *
	 * @param string $tabname table-name
	 * @param string $flds column-names and types for the changed columns
	 * @return array with SQL strings
	 */
	function AddColumnSQL($tabname, $flds)
	{
		$tabname = $this->TableName ($tabname);
		$sql = array();
		$not_null = false;
		list($lines,$pkey) = $this->_GenFields($flds);
		$alter = 'ALTER TABLE ' . $tabname . $this->addCol . ' ';
		foreach($lines as $v) {
			if (($not_null = preg_match('/NOT NULL/i',$v))) {
				$v = preg_replace('/NOT NULL/i','',$v);
			}
			if (preg_match('/^([^ ]+) .*DEFAULT (\'[^\']+\'|\"[^\"]+\"|[^ ]+)/',$v,$matches)) {
				list(,$colname,$default) = $matches;
				$sql[] = $alter . str_replace('DEFAULT '.$default,'',$v);
				$sql[] = 'UPDATE '.$tabname.' SET '.$colname.'='.$default;
				$sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET DEFAULT ' . $default;
			} else {
				$sql[] = $alter . $v;
			}
			if ($not_null) {
				if (isset($default)) {
					//$defaultv = preg_split('/[\t ]+/', $default, 2);
					//if (!isset($defaultv[1])) $defaultv = array(null, '');
					$sql[] = 'UPDATE '.$tabname.' SET '.$colname.' = '.$this->connection->quote($default).' WHERE '.$colname.' IS NULL';
				}
				list($colname) = explode(' ',$v);
				$sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
			}
		}
		return $sql;
	}


	function DropIndexSQL ($idxname, $tabname = NULL)
	{
	   return array(sprintf($this->dropIndex, $this->TableName($idxname), $this->TableName($tabname)));
	}

	/**
	 * Change the definition of one column
	 *
	 * Postgres can't do that on it's own, you need to supply the complete defintion of the new table,
	 * to allow, recreating the table and copying the content over to the new table
	 * @param string $tabname table-name
	 * @param string $flds column-name and type for the changed column
	 * @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
	 * @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
	 * @return array with SQL strings
	 */
	 /*
	function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
	{
		if (!$tableflds) {
			if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
			return array();
		}
		return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
	}*/

	function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
	{
	   // Check if alter single column datatype available - works with 8.0+
	   $has_alter_column = 8.0 <= (float) @$this->serverInfo['version'];

	   if ($has_alter_column) {
	      $tabname = $this->TableName($tabname);
	      $sql = array();
	      list($lines,$pkey) = $this->_GenFields($flds);
		  $set_null = false;
	      $alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
	      foreach($lines as $v) {
	        if ($not_null = preg_match('/NOT NULL/i',$v)) {
	            $v = preg_replace('/NOT NULL/i','',$v);
	        }
	         // this next block doesn't work - there is no way that I can see to
	         // explicitly ask a column to be null using $flds
	        else if ($set_null = preg_match('/NULL/i',$v)) {
	            // if they didn't specify not null, see if they explicitely asked for null
	            $v = preg_replace('/\sNULL/i','',$v);
	        }

			if (preg_match('/^([^ ]+) .*DEFAULT (\'[^\']+\'|\"[^\"]+\"|[^ ]+)/',$v,$matches)) {
				$existing = $this->MetaColumns($tabname);
				list(,$colname,$default) = $matches;
				if ($this->connection) $old_coltype = $this->connection->MetaType($existing[strtoupper($colname)]);
				else $old_coltype = $t;
				$v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v);
				$t = trim(str_replace('DEFAULT '.$default,'',$v));

				// Type change from bool to int
				if ( $old_coltype == 'L' && $t == 'INTEGER' ) {
					$sql[] = $alter . $colname . ' DROP DEFAULT';
					$sql[] = $alter . $colname . " TYPE $t USING ($colname::BOOL)::INT";
					$sql[] = $alter . $colname . " SET DEFAULT $default";
				}
				// Type change from int to bool
				else if ( $old_coltype == 'I' && $t == 'BOOLEAN' ) {
					$sql[] = $alter . $colname . ' DROP DEFAULT';
					$sql[] = $alter . $colname . " TYPE $t USING CASE WHEN $colname = 0 THEN false ELSE true END";
					$sql[] = $alter . $colname . " SET DEFAULT " . $this->connection->qstr($default);
				}
				// Any other column types conversion
				else {
					$sql[] = $alter . $colname . " TYPE $t";
					$sql[] = $alter . $colname . " SET DEFAULT $default";
				}


	         }
	         else {
	            // drop default?
	            preg_match ('/^\s*(\S+)\s+(.*)$/',$v,$matches);
	            list (,$colname,$rest) = $matches;
	            $sql[] = $alter . $colname . ' TYPE ' . $rest;
	         }

#	         list($colname) = explode(' ',$v);
	         if ($not_null) {
	            // this does not error out if the column is already not null
				$sql[] = $alter . $colname . ' SET NOT NULL';
	         }
	         if ($set_null) {
	            // this does not error out if the column is already null
	            $sql[] = $alter . $colname . ' DROP NOT NULL';
	         }
	      }
	      return $sql;
	   }

	   // does not have alter column
	   if (!$tableflds) {
	      if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
	      return array();
	   }
	   return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
	}

	/**
	 * Drop one column
	 *
	 * Postgres < 7.3 can't do that on it's own, you need to supply the complete defintion of the new table,
	 * to allow, recreating the table and copying the content over to the new table
	 * @param string $tabname table-name
	 * @param string $flds column-name and type for the changed column
	 * @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
	 * @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
	 * @return array with SQL strings
	 */
	function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
	{
		$has_drop_column = 7.3 <= (float) @$this->serverInfo['version'];
		if (!$has_drop_column && !$tableflds) {
			if ($this->debug) ADOConnection::outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3");
		return array();
	}
		if ($has_drop_column) {
			return ADODB_DataDict::DropColumnSQL($tabname, $flds);
		}
		return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions);
	}

	/**
	 * Save the content into a temp. table, drop and recreate the original table and copy the content back in
	 *
	 * We also take care to set the values of the sequenz and recreate the indexes.
	 * All this is done in a transaction, to not loose the content of the table, if something went wrong!
	 * @internal
	 * @param string $tabname table-name
	 * @param string $dropflds column-names to drop
	 * @param string $tableflds complete defintion of the new table, eg. for postgres
	 * @param array/string $tableoptions options for the new table see CreateTableSQL, default ''
	 * @return array with SQL strings
	 */
	function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='')
	{
		if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds);
		$copyflds = array();
		$insertflds = array();
		foreach($this->MetaColumns($tabname) as $fld) {
			if ((!$dropflds || !in_array($fld->name,$dropflds)) && isset($tableflds[strtoupper($fld->name)])) {
				// we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one
				if (((is_array($tableflds)
					&& in_array($tableflds[strtoupper($fld->name)]['TYPE'], array('I', 'I2', 'I4', 'I8', 'N', 'F')))
					|| (!is_array($tableflds)
					&& preg_match('/'.$fld->name.' (I|I2|I4|I8|N|F)/i',$tableflds,$matches))) &&
					in_array($fld->type,array('varchar','char','text','bytea'))) {
					$copyflds[] = "to_number($fld->name,'S9999999999999D99')";
				} else {
					$copyflds[] = $fld->name;
				}
				$insertflds[] = $fld->name;
				// identify the sequence name and the fld its on
				if (isset($fld->primary_key) && $fld->primary_key && $fld->has_default &&
					preg_match("/nextval\('(?:[^']+\.)*([^']+)'::(text|regclass)\)/",$fld->default_value,$matches)) {
					$seq_name = $matches[1];
					$seq_fld = $fld->name;
				}
			}
		}
		$copyflds = implode(', ',$copyflds);
		$insertflds = implode(', ',$insertflds);

		$tempname = $tabname.'_tmp';
		$aSql[] = 'BEGIN';		// we use a transaction, to make sure not to loose the content of the table
		$aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname";
		$aSql = array_merge($aSql,$this->DropTableSQL($tabname));
		$aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions));
		$aSql[] = "INSERT INTO $tabname ($insertflds) SELECT $copyflds FROM $tempname";
		if (isset($seq_name) && $seq_name && $seq_fld) {	// if we have a sequence we need to set it again
			// $seq_name = $tabname.'_'.$seq_fld.'_seq';	// has to be the name of the new implicit sequence
			$seq_name = $this->makeObjectName($tabname, $seq_fld, 'seq');
			$aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname";
		}
		$aSql[] = "DROP TABLE $tempname";
		// recreate the indexes, if they not contain one of the droped columns
		/* FIXME 2005-08-01 KJ - Temporarily disabled for XML schema upgrades
		foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data)
		{
			if (substr($idx_name,-5) != '_pkey' && (!$dropflds || !count(array_intersect($dropflds,$idx_data['columns'])))) {
				$aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'],
					$idx_data['unique'] ? array('UNIQUE') : False));
			}
		}
		*/
		$aSql[] = 'COMMIT';
		return $aSql;
	}

	/* --- Added by Alec 2005-09-14:
	 In PostgreSQL <7.3, SERIAL columns can't be used because they
	impose UNIQUE constraints on the column. In the best case (when
			we want a UNIQUE constraint), this means that the index is
	created twice -- once by ADODB, once by PostgreSQL -- and in
	the worst case, an unwanted UNIQUE condition is imposed.

	The makeObjectName function was ported from PostgreSQL 7.1's
	analyse.c.
	--- */

	function makeObjectName($name1, $name2, $typename) {
		$overhead = 0;

		$name1chars = strlen($name1);
		if ($name2) {
			$name2chars = strlen($name2);
			$overhead++; /* allow for separating underscore */
		}
		else $name2chars = 0;

		if ($typename) $overhead += strlen($typename) + 1;

		$availchars = 64 - 1 - $overhead; /* --- 32 = default NAMEDATALEN in PostgreSQL --- */

		/*
		 * If we must truncate, preferentially truncate the longer name. This
		* logic could be expressed without a loop, but it's simple and
		* obvious as a loop.
		*/
		while ($name1chars + $name2chars > $availchars) {
			if ($name1chars > $name2chars) $name1chars--;
			else $name2chars--;
		}

		/* Now construct the string using the chosen lengths */
		$name = substr($name1, 0, $name1chars);

		if ($name2) $name .= '_' . substr($name2, 0, $name2chars);
		if ($typename) $name .= '_' . $typename;

		return $name;
	}

	function CreateTableSQL($tabname, $flds, $tableoptions=false) {
		$sql = ADODB_DataDict::CreateTableSQL($tabname, $flds, $tableoptions);

		if (7.3 > (float) @$this->serverInfo['version']) {
			foreach ($flds as $fld) {
				$fld = _array_change_key_case($fld);

				$isAutoInc = false;
				foreach($fld as $attr => $v) switch ($attr) {
					case 'AUTOINCREMENT':
					case 'AUTO':
						$isAutoInc = true;
						break;
					case 'NAME':
						$fname = $v;
						break;
				}

				if (isset($fname) && $isAutoInc) {
					// This field is an AUTOINCREMENT. Create a sequence
					// for it.
					$sequenceName = $this->makeObjectName($tabname, $fname, 'seq');
					array_unshift($sql, "CREATE SEQUENCE $sequenceName");
					array_push($sql, "ALTER TABLE $tabname ALTER COLUMN $fname SET DEFAULT nextval('$sequenceName')");
				}
			}
		}
		return $sql;
	}

	/* --- End additions by Alec --- */

	function DropTableSQL($tabname)
	{
		$sql = ADODB_DataDict::DropTableSQL($tabname);

		$drop_seq = $this->_DropAutoIncrement($tabname);
		if ($drop_seq) $sql[] = $drop_seq;

		return $sql;
	}

	// return string must begin with space
	function _CreateSuffix($fname, &$ftype, $fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
	{
		if ($fautoinc) {
			// Added by Alec 2005-09-14: With PostgreSQL < 7.3, we cannot
			// use the SERIAL type because it forces the use of a unique
			// index on that column; at best, this causes duplicate indexes
			// to be created. At worst, it causes UNIQUE constraints to be
			// put on columns that shouldn't have them.

			if (7.3 > (float) @$this->serverInfo['version']) {
				$ftype = 'INTEGER';
				return '';
			}

			// ---

			$ftype = 'SERIAL';
			return '';
		}
		$suffix = '';
		if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
		if ($fnotnull) $suffix .= ' NOT NULL';
		if ($fconstraint) $suffix .= ' '.$fconstraint;
		return $suffix;
	}

	// search for a sequece for the given table (asumes the seqence-name contains the table-name!)
	// if yes return sql to drop it
	// this is still necessary if postgres < 7.3 or the SERIAL was created on an earlier version!!!
	function _DropAutoIncrement($tabname)
	{
		// FIXME This Code
		$seq = false;
		foreach($this->MetaColumns($tabname) as $fld) {
			if (isset($fld->primary_key) && $fld->primary_key && $fld->has_default &&
				preg_match("/nextval\('[\"]?(?:[^'\"]+\.)*([^'\"]+)[\"]?'::(?:text|regclass)\)/",$fld->default_value,$matches)) {
				$seq = $matches[1];
			}
		}

		//$seq = $this->connection->GetOne("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE $tabname AND relkind='S'");

		// check if a tables depends on the sequenz and it therefor cant and dont need to be droped separatly
		if (!$seq || $this->connection->GetOne("SELECT relname FROM pg_class JOIN pg_depend ON pg_class.relfilenode=pg_depend.objid WHERE relname='$seq' AND relkind='S' AND deptype='i'")) {
			return False;
		}
		return "DROP SEQUENCE IF EXISTS ".$seq;
	}

	function RenameTableSQL($tabname,$newname)
	{
		if (!empty($this->schema)) {
			$rename_from = $this->TableName($tabname);
			$schema_save = $this->schema;
			$this->schema = false;
			$rename_to = $this->TableName($newname);
			$this->schema = $schema_save;
			return array (sprintf($this->renameTable, $rename_from, $rename_to));
		}

		return array (sprintf($this->renameTable, $this->TableName($tabname),$this->TableName($newname)));
	}

	/*
	CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
	{ column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
	| table_constraint } [, ... ]
	)
	[ INHERITS ( parent_table [, ... ] ) ]
	[ WITH OIDS | WITHOUT OIDS ]
	where column_constraint is:
	[ CONSTRAINT constraint_name ]
	{ NOT NULL | NULL | UNIQUE | PRIMARY KEY |
	CHECK (expression) |
	REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
	[ ON DELETE action ] [ ON UPDATE action ] }
	[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
	and table_constraint is:
	[ CONSTRAINT constraint_name ]
	{ UNIQUE ( column_name [, ... ] ) |
	PRIMARY KEY ( column_name [, ... ] ) |
	CHECK ( expression ) |
	FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
	[ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
	[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
	*/


	/*
	CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_method ] ( column [ ops_name ] [, ...] )
[ WHERE predicate ]
CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
[ WHERE predicate ]
	*/
	function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
	{
		$sql = array();

		if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
			$sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
			if ( isset($idxoptions['DROP']) )
				return $sql;
		}

		if ( empty ($flds) ) {
			return $sql;
		}

		$unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';

		$s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' ';

		if (isset($idxoptions['HASH']))
			$s .= 'USING HASH ';

		if ( isset($idxoptions[$this->upperName]) )
			$s .= $idxoptions[$this->upperName];

		if ( is_array($flds) )
			$flds = implode(', ',$flds);
		$s .= '(' . $flds . ')';
		$sql[] = $s;

		return $sql;
	}

	function _GetSize($ftype, $ty, $fsize, $fprec)
	{
		if (strlen($fsize) && $ty != 'X' && $ty != 'B' && $ty  != 'I' && strpos($ftype,'(') === false) {
			$ftype .= "(".$fsize;
			if (strlen($fprec)) $ftype .= ",".$fprec;
			$ftype .= ')';
		}
		return $ftype;
	}

	// Functions for managing the database character encoding
	// (for CREATE DATABASE, CREATE TABLE, etc.)
	// Added 2004-06-20 by Kevin Jamieson (https://pkp.sfu.ca/)
	// NOTE: If a character set is specified, assumes the database server supports this
	function CreateDatabase($dbname,$options=false)
	{
		$options = $this->_Options($options);
		$sql = array();

		$s = 'CREATE DATABASE ' . $this->NameQuote($dbname);
		if (isset($options[$this->upperName]))
			$s .= ' '.$options[$this->upperName];
		if ($this->charSet)
			$s .= sprintf(' WITH ENCODING \'%s\'', $this->charSet);
		if (7.3 <= (float) @$this->serverInfo['version'])
			$s .= ' TEMPLATE template0'; // Deal with "template1 is being accessed by other users" errors (FIXME?)
		$sql[] = $s;
		return $sql;
	}
}
?>
