[6779] | 1 | <?php |
---|
| 2 | |
---|
| 3 | /* |
---|
| 4 | V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). 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 | |
---|
| 12 | class 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 '....%%' |
---|
| 27 | AND 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 |
---|
| 31 | FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n |
---|
| 32 | WHERE 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 = " |
---|
| 98 | select tablename,'T' from pg_tables where tablename like $mask and schemaname not in ( 'pg_catalog','information_schema') |
---|
| 99 | union |
---|
| 100 | select viewname,'V' from pg_views where viewname like $mask and schemaname not in ( 'pg_catalog','information_schema') "; |
---|
| 101 | else |
---|
| 102 | $this->metaTablesSQL = " |
---|
| 103 | select tablename,'T' from pg_tables where tablename like $mask |
---|
| 104 | union |
---|
| 105 | select 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 | ?> |
---|