1<?php
2
3/*
4@version   v5.20.20  01-Feb-2021
5@copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
6@copyright (c) 2014      Damien Regad, Mark Newnham and the ADOdb community
7  Released under both BSD license and Lesser GPL library license.
8  Whenever there is any discrepancy between the two licenses,
9  the BSD license will take precedence.
10  Set tabs to 8.
11
12*/
13
14class ADODB_pdo_pgsql extends ADODB_pdo {
15	var $metaDatabasesSQL = "select datname from pg_database where datname not in ('template0','template1') order by 1";
16    var $metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%'
17	and tablename not in ('sql_features', 'sql_implementation_info', 'sql_languages',
18	 'sql_packages', 'sql_sizing', 'sql_sizing_profiles')
19	union
20        select viewname,'V' from pg_views where viewname not like 'pg\_%'";
21	//"select tablename from pg_tables where tablename not like 'pg_%' order by 1";
22	var $isoDates = true; // accepts dates in ISO format
23	var $sysDate = "CURRENT_DATE";
24	var $sysTimeStamp = "CURRENT_TIMESTAMP";
25	var $blobEncodeType = 'C';
26	var $metaColumnsSQL = "SELECT a.attname,t.typname,a.attlen,a.atttypmod,a.attnotnull,a.atthasdef,a.attnum
27		FROM pg_class c, pg_attribute a,pg_type t
28		WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s')) and a.attname not like '....%%'
29AND a.attnum > 0 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
30
31	// used when schema defined
32	var $metaColumnsSQL1 = "SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
33FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n
34WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s'))
35 and c.relnamespace=n.oid and n.nspname='%s'
36	and a.attname not like '....%%' AND a.attnum > 0
37	AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
38
39	// get primary key etc -- from Freek Dijkstra
40	var $metaKeySQL = "SELECT ic.relname AS index_name, a.attname AS column_name,i.indisunique AS unique_key, i.indisprimary AS primary_key
41	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'";
42
43	var $hasAffectedRows = true;
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 G: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 START %s";
54	var $_dropSeqSQL = "DROP SEQUENCE %s";
55	var $metaDefaultsSQL = "SELECT d.adnum as num, d.adsrc as def from pg_attrdef d, pg_class c where d.adrelid=c.oid and c.relname='%s' order by d.adnum";
56	var $random = 'random()';		/// random function
57	var $concat_operator='||';
58
59	function _init($parentDriver)
60	{
61
62		$parentDriver->hasTransactions = false; ## <<< BUG IN PDO pgsql driver
63		$parentDriver->hasInsertID = true;
64		$parentDriver->_nestedSQL = true;
65	}
66
67	function ServerInfo()
68	{
69		$arr['description'] = ADOConnection::GetOne("select version()");
70		$arr['version'] = ADOConnection::_findvers($arr['description']);
71		return $arr;
72	}
73
74	function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
75	{
76		$nrows = (int) $nrows;
77		$offset = (int) $offset;
78		$offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
79		$limitStr  = ($nrows >= 0)  ? " LIMIT $nrows" : '';
80		if ($secs2cache)
81			$rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
82		else
83			$rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
84
85		return $rs;
86	}
87
88	function MetaTables($ttype=false,$showSchema=false,$mask=false)
89	{
90		$info = $this->ServerInfo();
91		if ($info['version'] >= 7.3) {
92	    	$this->metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%'
93			  and schemaname  not in ( 'pg_catalog','information_schema')
94	union
95        select viewname,'V' from pg_views where viewname not like 'pg\_%'  and schemaname  not in ( 'pg_catalog','information_schema') ";
96		}
97		if ($mask) {
98			$save = $this->metaTablesSQL;
99			$mask = $this->qstr(strtolower($mask));
100			if ($info['version']>=7.3)
101				$this->metaTablesSQL = "
102select tablename,'T' from pg_tables where tablename like $mask and schemaname not in ( 'pg_catalog','information_schema')
103 union
104select viewname,'V' from pg_views where viewname like $mask and schemaname  not in ( 'pg_catalog','information_schema')  ";
105			else
106				$this->metaTablesSQL = "
107select tablename,'T' from pg_tables where tablename like $mask
108 union
109select viewname,'V' from pg_views where viewname like $mask";
110		}
111		$ret = ADOConnection::MetaTables($ttype,$showSchema);
112
113		if ($mask) {
114			$this->metaTablesSQL = $save;
115		}
116		return $ret;
117	}
118
119	function MetaColumns($table,$normalize=true)
120	{
121	global $ADODB_FETCH_MODE;
122
123		$schema = false;
124		$this->_findschema($table,$schema);
125
126		if ($normalize) $table = strtolower($table);
127
128		$save = $ADODB_FETCH_MODE;
129		$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
130		if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
131
132		if ($schema) $rs = $this->Execute(sprintf($this->metaColumnsSQL1,$table,$table,$schema));
133		else $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table,$table));
134		if (isset($savem)) $this->SetFetchMode($savem);
135		$ADODB_FETCH_MODE = $save;
136
137		if ($rs === false) {
138			$false = false;
139			return $false;
140		}
141		if (!empty($this->metaKeySQL)) {
142			// If we want the primary keys, we have to issue a separate query
143			// Of course, a modified version of the metaColumnsSQL query using a
144			// LEFT JOIN would have been much more elegant, but postgres does
145			// not support OUTER JOINS. So here is the clumsy way.
146
147			$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
148
149			$rskey = $this->Execute(sprintf($this->metaKeySQL,($table)));
150			// fetch all result in once for performance.
151			$keys = $rskey->GetArray();
152			if (isset($savem)) $this->SetFetchMode($savem);
153			$ADODB_FETCH_MODE = $save;
154
155			$rskey->Close();
156			unset($rskey);
157		}
158
159		$rsdefa = array();
160		if (!empty($this->metaDefaultsSQL)) {
161			$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
162			$sql = sprintf($this->metaDefaultsSQL, ($table));
163			$rsdef = $this->Execute($sql);
164			if (isset($savem)) $this->SetFetchMode($savem);
165			$ADODB_FETCH_MODE = $save;
166
167			if ($rsdef) {
168				while (!$rsdef->EOF) {
169					$num = $rsdef->fields['num'];
170					$s = $rsdef->fields['def'];
171					if (strpos($s,'::')===false && substr($s, 0, 1) == "'") { /* quoted strings hack... for now... fixme */
172						$s = substr($s, 1);
173						$s = substr($s, 0, strlen($s) - 1);
174					}
175
176					$rsdefa[$num] = $s;
177					$rsdef->MoveNext();
178				}
179			} else {
180				ADOConnection::outp( "==> SQL => " . $sql);
181			}
182			unset($rsdef);
183		}
184
185		$retarr = array();
186		while (!$rs->EOF) {
187			$fld = new ADOFieldObject();
188			$fld->name = $rs->fields[0];
189			$fld->type = $rs->fields[1];
190			$fld->max_length = $rs->fields[2];
191			if ($fld->max_length <= 0) $fld->max_length = $rs->fields[3]-4;
192			if ($fld->max_length <= 0) $fld->max_length = -1;
193			if ($fld->type == 'numeric') {
194				$fld->scale = $fld->max_length & 0xFFFF;
195				$fld->max_length >>= 16;
196			}
197			// dannym
198			// 5 hasdefault; 6 num-of-column
199			$fld->has_default = ($rs->fields[5] == 't');
200			if ($fld->has_default) {
201				$fld->default_value = $rsdefa[$rs->fields[6]];
202			}
203
204			//Freek
205			if ($rs->fields[4] == $this->true) {
206				$fld->not_null = true;
207			}
208
209			// Freek
210			if (is_array($keys)) {
211				foreach($keys as $key) {
212					if ($fld->name == $key['column_name'] AND $key['primary_key'] == $this->true)
213						$fld->primary_key = true;
214					if ($fld->name == $key['column_name'] AND $key['unique_key'] == $this->true)
215						$fld->unique = true; // What name is more compatible?
216				}
217			}
218
219			if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;
220			else $retarr[($normalize) ? strtoupper($fld->name) : $fld->name] = $fld;
221
222			$rs->MoveNext();
223		}
224		$rs->Close();
225		if (empty($retarr)) {
226			$false = false;
227			return $false;
228		} else return $retarr;
229
230	}
231
232}
233