4 @version v5.20.2 27-Dec-2015
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.
14 class 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')
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 '....%%'
29 AND a.attnum > 0 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
31 // used when schema defined
32 var $metaColumnsSQL1 = "SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
33 FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n
34 WHERE 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";
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'";
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;
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='||';
59 function _init($parentDriver)
62 $parentDriver->hasTransactions
= false; ## <<< BUG IN PDO pgsql driver
63 $parentDriver->hasInsertID
= true;
64 $parentDriver->_nestedSQL
= true;
69 $arr['description'] = ADOConnection
::GetOne("select version()");
70 $arr['version'] = ADOConnection
::_findvers($arr['description']);
74 function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
76 $offsetStr = ($offset >= 0) ?
" OFFSET $offset" : '';
77 $limitStr = ($nrows >= 0) ?
" LIMIT $nrows" : '';
79 $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
81 $rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
86 function MetaTables($ttype=false,$showSchema=false,$mask=false)
88 $info = $this->ServerInfo();
89 if ($info['version'] >= 7.3) {
90 $this->metaTablesSQL
= "select tablename,'T' from pg_tables where tablename not like 'pg\_%'
91 and schemaname not in ( 'pg_catalog','information_schema')
93 select viewname,'V' from pg_views where viewname not like 'pg\_%' and schemaname not in ( 'pg_catalog','information_schema') ";
96 $save = $this->metaTablesSQL
;
97 $mask = $this->qstr(strtolower($mask));
98 if ($info['version']>=7.3)
99 $this->metaTablesSQL
= "
100 select tablename,'T' from pg_tables where tablename like $mask and schemaname not in ( 'pg_catalog','information_schema')
102 select viewname,'V' from pg_views where viewname like $mask and schemaname not in ( 'pg_catalog','information_schema') ";
104 $this->metaTablesSQL
= "
105 select tablename,'T' from pg_tables where tablename like $mask
107 select viewname,'V' from pg_views where viewname like $mask";
109 $ret = ADOConnection
::MetaTables($ttype,$showSchema);
112 $this->metaTablesSQL
= $save;
117 function MetaColumns($table,$normalize=true)
119 global $ADODB_FETCH_MODE;
122 $this->_findschema($table,$schema);
124 if ($normalize) $table = strtolower($table);
126 $save = $ADODB_FETCH_MODE;
127 $ADODB_FETCH_MODE = ADODB_FETCH_NUM
;
128 if ($this->fetchMode
!== false) $savem = $this->SetFetchMode(false);
130 if ($schema) $rs = $this->Execute(sprintf($this->metaColumnsSQL1
,$table,$table,$schema));
131 else $rs = $this->Execute(sprintf($this->metaColumnsSQL
,$table,$table));
132 if (isset($savem)) $this->SetFetchMode($savem);
133 $ADODB_FETCH_MODE = $save;
139 if (!empty($this->metaKeySQL
)) {
140 // If we want the primary keys, we have to issue a separate query
141 // Of course, a modified version of the metaColumnsSQL query using a
142 // LEFT JOIN would have been much more elegant, but postgres does
143 // not support OUTER JOINS. So here is the clumsy way.
145 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC
;
147 $rskey = $this->Execute(sprintf($this->metaKeySQL
,($table)));
148 // fetch all result in once for performance.
149 $keys = $rskey->GetArray();
150 if (isset($savem)) $this->SetFetchMode($savem);
151 $ADODB_FETCH_MODE = $save;
158 if (!empty($this->metaDefaultsSQL
)) {
159 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC
;
160 $sql = sprintf($this->metaDefaultsSQL
, ($table));
161 $rsdef = $this->Execute($sql);
162 if (isset($savem)) $this->SetFetchMode($savem);
163 $ADODB_FETCH_MODE = $save;
166 while (!$rsdef->EOF
) {
167 $num = $rsdef->fields
['num'];
168 $s = $rsdef->fields
['def'];
169 if (strpos($s,'::')===false && substr($s, 0, 1) == "'") { /* quoted strings hack... for now... fixme */
171 $s = substr($s, 0, strlen($s) - 1);
178 ADOConnection
::outp( "==> SQL => " . $sql);
185 $fld = new ADOFieldObject();
186 $fld->name
= $rs->fields
[0];
187 $fld->type
= $rs->fields
[1];
188 $fld->max_length
= $rs->fields
[2];
189 if ($fld->max_length
<= 0) $fld->max_length
= $rs->fields
[3]-4;
190 if ($fld->max_length
<= 0) $fld->max_length
= -1;
191 if ($fld->type
== 'numeric') {
192 $fld->scale
= $fld->max_length
& 0xFFFF;
193 $fld->max_length
>>= 16;
196 // 5 hasdefault; 6 num-of-column
197 $fld->has_default
= ($rs->fields
[5] == 't');
198 if ($fld->has_default
) {
199 $fld->default_value
= $rsdefa[$rs->fields
[6]];
203 if ($rs->fields
[4] == $this->true) {
204 $fld->not_null
= true;
208 if (is_array($keys)) {
209 foreach($keys as $key) {
210 if ($fld->name
== $key['column_name'] AND $key['primary_key'] == $this->true)
211 $fld->primary_key
= true;
212 if ($fld->name
== $key['column_name'] AND $key['unique_key'] == $this->true)
213 $fld->unique
= true; // What name is more compatible?
217 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM
) $retarr[] = $fld;
218 else $retarr[($normalize) ?
strtoupper($fld->name
) : $fld->name
] = $fld;
223 if (empty($retarr)) {
226 } else return $retarr;