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