| [ PHPXref.com ] | [ Generated: Sun Jul 20 19:45:06 2008 ] | [ PhpLabware 0.6 ] |
| [ Index ] [ Variables ] [ Functions ] [ Classes ] [ Constants ] [ Statistics ] | ||
[Summary view] [Print] [Text view]
1 <?php 2 /* 3 V4.81 3 May 2006 (c) 2000-2006 John Lim (jlim#natsoft.com.my). All rights reserved. 4 Released under both BSD license and Lesser GPL library license. 5 Whenever there is any discrepancy between the two licenses, 6 the BSD license will take precedence. 7 Set tabs to 8. 8 9 Original version derived from Alberto Cerezal (acerezalp@dbnet.es) - DBNet Informatica & Comunicaciones. 10 08 Nov 2000 jlim - Minor corrections, removing mysql stuff 11 09 Nov 2000 jlim - added insertid support suggested by "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> 12 jlim - changed concat operator to || and data types to MetaType to match documented pgsql types 13 see http://www.postgresql.org/devel-corner/docs/postgres/datatype.htm 14 22 Nov 2000 jlim - added changes to FetchField() and MetaTables() contributed by "raser" <raser@mail.zen.com.tw> 15 27 Nov 2000 jlim - added changes to _connect/_pconnect from ideas by "Lennie" <leen@wirehub.nl> 16 15 Dec 2000 jlim - added changes suggested by Additional code changes by "Eric G. Werk" egw@netguide.dk. 17 31 Jan 2002 jlim - finally installed postgresql. testing 18 01 Mar 2001 jlim - Freek Dijkstra changes, also support for text type 19 20 See http://www.varlena.com/varlena/GeneralBits/47.php 21 22 -- What indexes are on my table? 23 select * from pg_indexes where tablename = 'tablename'; 24 25 -- What triggers are on my table? 26 select c.relname as "Table", t.tgname as "Trigger Name", 27 t.tgconstrname as "Constraint Name", t.tgenabled as "Enabled", 28 t.tgisconstraint as "Is Constraint", cc.relname as "Referenced Table", 29 p.proname as "Function Name" 30 from pg_trigger t, pg_class c, pg_class cc, pg_proc p 31 where t.tgfoid = p.oid and t.tgrelid = c.oid 32 and t.tgconstrrelid = cc.oid 33 and c.relname = 'tablename'; 34 35 -- What constraints are on my table? 36 select r.relname as "Table", c.conname as "Constraint Name", 37 contype as "Constraint Type", conkey as "Key Columns", 38 confkey as "Foreign Columns", consrc as "Source" 39 from pg_class r, pg_constraint c 40 where r.oid = c.conrelid 41 and relname = 'tablename'; 42 43 */ 44 45 // security - hide paths 46 if (!defined('ADODB_DIR')) die(); 47 48 function adodb_addslashes($s) 49 { 50 $len = strlen($s); 51 if ($len == 0) return "''"; 52 if (strncmp($s,"'",1) === 0 && substr($s,$len-1) == "'") return $s; // already quoted 53 54 return "'".addslashes($s)."'"; 55 } 56 57 class ADODB_postgres64 extends ADOConnection{ 58 var $databaseType = 'postgres64'; 59 var $dataProvider = 'postgres'; 60 var $hasInsertID = true; 61 var $_resultid = false; 62 var $concat_operator='||'; 63 var $metaDatabasesSQL = "select datname from pg_database where datname not in ('template0','template1') order by 1"; 64 var $metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%' 65 and tablename not in ('sql_features', 'sql_implementation_info', 'sql_languages', 66 'sql_packages', 'sql_sizing', 'sql_sizing_profiles') 67 union 68 select viewname,'V' from pg_views where viewname not like 'pg\_%'"; 69 //"select tablename from pg_tables where tablename not like 'pg_%' order by 1"; 70 var $isoDates = true; // accepts dates in ISO format 71 var $sysDate = "CURRENT_DATE"; 72 var $sysTimeStamp = "CURRENT_TIMESTAMP"; 73 var $blobEncodeType = 'C'; 74 var $metaColumnsSQL = "SELECT a.attname,t.typname,a.attlen,a.atttypmod,a.attnotnull,a.atthasdef,a.attnum 75 FROM pg_class c, pg_attribute a,pg_type t 76 WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s')) and a.attname not like '....%%' 77 AND a.attnum > 0 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum"; 78 79 // used when schema defined 80 var $metaColumnsSQL1 = "SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum 81 FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n 82 WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s')) 83 and c.relnamespace=n.oid and n.nspname='%s' 84 and a.attname not like '....%%' AND a.attnum > 0 85 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum"; 86 87 // get primary key etc -- from Freek Dijkstra 88 var $metaKeySQL = "SELECT ic.relname AS index_name, a.attname AS column_name,i.indisunique AS unique_key, i.indisprimary AS primary_key 89 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'"; 90 91 var $hasAffectedRows = true; 92 var $hasLimit = false; // set to true for pgsql 7 only. support pgsql/mysql SELECT * FROM TABLE LIMIT 10 93 // below suggested by Freek Dijkstra 94 var $true = 'TRUE'; // string that represents TRUE for a database 95 var $false = 'FALSE'; // string that represents FALSE for a database 96 var $fmtDate = "'Y-m-d'"; // used by DBDate() as the default date format used by the database 97 var $fmtTimeStamp = "'Y-m-d H:i:s'"; // used by DBTimeStamp as the default timestamp fmt. 98 var $hasMoveFirst = true; 99 var $hasGenID = true; 100 var $_genIDSQL = "SELECT NEXTVAL('%s')"; 101 var $_genSeqSQL = "CREATE SEQUENCE %s START %s"; 102 var $_dropSeqSQL = "DROP SEQUENCE %s"; 103 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"; 104 var $random = 'random()'; /// random function 105 var $autoRollback = true; // apparently pgsql does not autorollback properly before php 4.3.4 106 // http://bugs.php.net/bug.php?id=25404 107 108 var $_bindInputArray = false; // requires postgresql 7.3+ and ability to modify database 109 var $disableBlobs = false; // set to true to disable blob checking, resulting in 2-5% improvement in performance. 110 111 // The last (fmtTimeStamp is not entirely correct: 112 // PostgreSQL also has support for time zones, 113 // and writes these time in this format: "2001-03-01 18:59:26+02". 114 // There is no code for the "+02" time zone information, so I just left that out. 115 // I'm not familiar enough with both ADODB as well as Postgres 116 // to know what the concequences are. The other values are correct (wheren't in 0.94) 117 // -- Freek Dijkstra 118 119 function ADODB_postgres64() 120 { 121 // changes the metaColumnsSQL, adds columns: attnum[6] 122 } 123 124 function ServerInfo() 125 { 126 if (isset($this->version)) return $this->version; 127 128 $arr['description'] = $this->GetOne("select version()"); 129 $arr['version'] = ADOConnection::_findvers($arr['description']); 130 $this->version = $arr; 131 return $arr; 132 } 133 134 function IfNull( $field, $ifNull ) 135 { 136 return " coalesce($field, $ifNull) "; 137 } 138 139 // get the last id - never tested 140 function pg_insert_id($tablename,$fieldname) 141 { 142 $result=pg_exec($this->_connectionID, "SELECT last_value FROM $tablename}_$fieldname}_seq"); 143 if ($result) { 144 $arr = @pg_fetch_row($result,0); 145 pg_freeresult($result); 146 if (isset($arr[0])) return $arr[0]; 147 } 148 return false; 149 } 150 151 /* Warning from http://www.php.net/manual/function.pg-getlastoid.php: 152 Using a OID as a unique identifier is not generally wise. 153 Unless you are very careful, you might end up with a tuple having 154 a different OID if a database must be reloaded. */ 155 function _insertid($table,$column) 156 { 157 if (!is_resource($this->_resultid) || get_resource_type($this->_resultid) !== 'pgsql result') return false; 158 $oid = pg_getlastoid($this->_resultid); 159 // to really return the id, we need the table and column-name, else we can only return the oid != id 160 return empty($table) || empty($column) ? $oid : $this->GetOne("SELECT $column FROM $table WHERE oid=".(int)$oid); 161 } 162 163 // I get this error with PHP before 4.0.6 - jlim 164 // Warning: This compilation does not support pg_cmdtuples() in adodb-postgres.inc.php on line 44 165 function _affectedrows() 166 { 167 if (!is_resource($this->_resultid) || get_resource_type($this->_resultid) !== 'pgsql result') return false; 168 return pg_cmdtuples($this->_resultid); 169 } 170 171 172 // returns true/false 173 function BeginTrans() 174 { 175 if ($this->transOff) return true; 176 $this->transCnt += 1; 177 return @pg_Exec($this->_connectionID, "begin"); 178 } 179 180 function RowLock($tables,$where,$flds='1 as ignore') 181 { 182 if (!$this->transCnt) $this->BeginTrans(); 183 return $this->GetOne("select $flds from $tables where $where for update"); 184 } 185 186 // returns true/false. 187 function CommitTrans($ok=true) 188 { 189 if ($this->transOff) return true; 190 if (!$ok) return $this->RollbackTrans(); 191 192 $this->transCnt -= 1; 193 return @pg_Exec($this->_connectionID, "commit"); 194 } 195 196 // returns true/false 197 function RollbackTrans() 198 { 199 if ($this->transOff) return true; 200 $this->transCnt -= 1; 201 return @pg_Exec($this->_connectionID, "rollback"); 202 } 203 204 function &MetaTables($ttype=false,$showSchema=false,$mask=false) 205 { 206 $info = $this->ServerInfo(); 207 if ($info['version'] >= 7.3) { 208 $this->metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%' 209 and schemaname not in ( 'pg_catalog','information_schema') 210 union 211 select viewname,'V' from pg_views where viewname not like 'pg\_%' and schemaname not in ( 'pg_catalog','information_schema') "; 212 } 213 if ($mask) { 214 $save = $this->metaTablesSQL; 215 $mask = $this->qstr(strtolower($mask)); 216 if ($info['version']>=7.3) 217 $this->metaTablesSQL = " 218 select tablename,'T' from pg_tables where tablename like $mask and schemaname not in ( 'pg_catalog','information_schema') 219 union 220 select viewname,'V' from pg_views where viewname like $mask and schemaname not in ( 'pg_catalog','information_schema') "; 221 else 222 $this->metaTablesSQL = " 223 select tablename,'T' from pg_tables where tablename like $mask 224 union 225 select viewname,'V' from pg_views where viewname like $mask"; 226 } 227 $ret =& ADOConnection::MetaTables($ttype,$showSchema); 228 229 if ($mask) { 230 $this->metaTablesSQL = $save; 231 } 232 return $ret; 233 } 234 235 236 // if magic quotes disabled, use pg_escape_string() 237 function qstr($s,$magic_quotes=false) 238 { 239 if (!$magic_quotes) { 240 if (ADODB_PHPVER >= 0x4200) { 241 return "'".pg_escape_string($s)."'"; 242 } 243 if ($this->replaceQuote[0] == '\\'){ 244 $s = adodb_str_replace(array('\\',"\0"),array('\\\\',"\\\\000"),$s); 245 } 246 return "'".str_replace("'",$this->replaceQuote,$s)."'"; 247 } 248 249 // undo magic quotes for " 250 $s = str_replace('\\"','"',$s); 251 return "'$s'"; 252 } 253 254 255 256 // Format date column in sql string given an input format that understands Y M D 257 function SQLDate($fmt, $col=false) 258 { 259 if (!$col) $col = $this->sysTimeStamp; 260 $s = 'TO_CHAR('.$col.",'"; 261 262 $len = strlen($fmt); 263 for ($i=0; $i < $len; $i++) { 264 $ch = $fmt[$i]; 265 switch($ch) { 266 case 'Y': 267 case 'y': 268 $s .= 'YYYY'; 269 break; 270 case 'Q': 271 case 'q': 272 $s .= 'Q'; 273 break; 274 275 case 'M': 276 $s .= 'Mon'; 277 break; 278 279 case 'm': 280 $s .= 'MM'; 281 break; 282 case 'D': 283 case 'd': 284 $s .= 'DD'; 285 break; 286 287 case 'H': 288 $s.= 'HH24'; 289 break; 290 291 case 'h': 292 $s .= 'HH'; 293 break; 294 295 case 'i': 296 $s .= 'MI'; 297 break; 298 299 case 's': 300 $s .= 'SS'; 301 break; 302 303 case 'a': 304 case 'A': 305 $s .= 'AM'; 306 break; 307 308 case 'w': 309 $s .= 'D'; 310 break; 311 312 case 'l': 313 $s .= 'DAY'; 314 break; 315 316 case 'W': 317 $s .= 'WW'; 318 break; 319 320 default: 321 // handle escape characters... 322 if ($ch == '\\') { 323 $i++; 324 $ch = substr($fmt,$i,1); 325 } 326 if (strpos('-/.:;, ',$ch) !== false) $s .= $ch; 327 else $s .= '"'.$ch.'"'; 328 329 } 330 } 331 return $s. "')"; 332 } 333 334 335 336 /* 337 * Load a Large Object from a file 338 * - the procedure stores the object id in the table and imports the object using 339 * postgres proprietary blob handling routines 340 * 341 * contributed by Mattia Rossi mattia@technologist.com 342 * modified for safe mode by juraj chlebec 343 */ 344 function UpdateBlobFile($table,$column,$path,$where,$blobtype='BLOB') 345 { 346 pg_exec ($this->_connectionID, "begin"); 347 348 $fd = fopen($path,'r'); 349 $contents = fread($fd,filesize($path)); 350 fclose($fd); 351 352 $oid = pg_lo_create($this->_connectionID); 353 $handle = pg_lo_open($this->_connectionID, $oid, 'w'); 354 pg_lo_write($handle, $contents); 355 pg_lo_close($handle); 356 357 // $oid = pg_lo_import ($path); 358 pg_exec($this->_connectionID, "commit"); 359 $rs = ADOConnection::UpdateBlob($table,$column,$oid,$where,$blobtype); 360 $rez = !empty($rs); 361 return $rez; 362 } 363 364 /* 365 * Deletes/Unlinks a Blob from the database, otherwise it 366 * will be left behind 367 * 368 * Returns TRUE on success or FALSE on failure. 369 * 370 * contributed by Todd Rogers todd#windfox.net 371 */ 372 function BlobDelete( $blob ) 373 { 374 pg_exec ($this->_connectionID, "begin"); 375 $result = @pg_lo_unlink($blob); 376 pg_exec ($this->_connectionID, "commit"); 377 return( $result ); 378 } 379 380 /* 381 Hueristic - not guaranteed to work. 382 */ 383 function GuessOID($oid) 384 { 385 if (strlen($oid)>16) return false; 386 return is_numeric($oid); 387 } 388 389 /* 390 * If an OID is detected, then we use pg_lo_* to open the oid file and read the 391 * real blob from the db using the oid supplied as a parameter. If you are storing 392 * blobs using bytea, we autodetect and process it so this function is not needed. 393 * 394 * contributed by Mattia Rossi mattia@technologist.com 395 * 396 * see http://www.postgresql.org/idocs/index.php?largeobjects.html 397 * 398 * Since adodb 4.54, this returns the blob, instead of sending it to stdout. Also 399 * added maxsize parameter, which defaults to $db->maxblobsize if not defined. 400 */ 401 function BlobDecode($blob,$maxsize=false,$hastrans=true) 402 { 403 if (!$this->GuessOID($blob)) return $blob; 404 405 if ($hastrans) @pg_exec($this->_connectionID,"begin"); 406 $fd = @pg_lo_open($this->_connectionID,$blob,"r"); 407 if ($fd === false) { 408 if ($hastrans) @pg_exec($this->_connectionID,"commit"); 409 return $blob; 410 } 411 if (!$maxsize) $maxsize = $this->maxblobsize; 412 $realblob = @pg_loread($fd,$maxsize); 413 @pg_loclose($fd); 414 if ($hastrans) @pg_exec($this->_connectionID,"commit"); 415 return $realblob; 416 } 417 418 /* 419 See http://www.postgresql.org/idocs/index.php?datatype-binary.html 420 421 NOTE: SQL string literals (input strings) must be preceded with two backslashes 422 due to the fact that they must pass through two parsers in the PostgreSQL 423 backend. 424 */ 425 function BlobEncode($blob) 426 { 427 if (ADODB_PHPVER >= 0x4200) return pg_escape_bytea($blob); 428 429 /*92=backslash, 0=null, 39=single-quote*/ 430 $badch = array(chr(92),chr(0),chr(39)); # \ null ' 431 $fixch = array('\\\\134','\\\\000','\\\\047'); 432 return adodb_str_replace($badch,$fixch,$blob); 433 434 // note that there is a pg_escape_bytea function only for php 4.2.0 or later 435 } 436 437 // assumes bytea for blob, and varchar for clob 438 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB') 439 { 440 441 if ($blobtype == 'CLOB') { 442 return $this->Execute("UPDATE $table SET $column=" . $this->qstr($val) . " WHERE $where"); 443 } 444 // do not use bind params which uses qstr(), as blobencode() already quotes data 445 return $this->Execute("UPDATE $table SET $column='".$this->BlobEncode($val)."'::bytea WHERE $where"); 446 } 447 448 function OffsetDate($dayFraction,$date=false) 449 { 450 if (!$date) $date = $this->sysDate; 451 else if (strncmp($date,"'",1) == 0) { 452 $len = strlen($date); 453 if (10 <= $len && $len <= 12) $date = 'date '.$date; 454 else $date = 'timestamp '.$date; 455 } 456 return "($date+interval'$dayFraction days')"; 457 } 458 459 460 // for schema support, pass in the $table param "$schema.$tabname". 461 // converts field names to lowercase, $upper is ignored 462 // see http://phplens.com/lens/lensforum/msgs.php?id=14018 for more info 463 function &MetaColumns($table,$normalize=true) 464 { 465 global $ADODB_FETCH_MODE; 466 467 $schema = false; 468 $false = false; 469 $this->_findschema($table,$schema); 470 471 if ($normalize) $table = strtolower($table); 472 473 $save = $ADODB_FETCH_MODE; 474 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 475 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false); 476 477 if ($schema) $rs =& $this->Execute(sprintf($this->metaColumnsSQL1,$table,$table,$schema)); 478 else $rs =& $this->Execute(sprintf($this->metaColumnsSQL,$table,$table)); 479 if (isset($savem)) $this->SetFetchMode($savem); 480 $ADODB_FETCH_MODE = $save; 481 482 if ($rs === false) { 483 return $false; 484 } 485 if (!empty($this->metaKeySQL)) { 486 // If we want the primary keys, we have to issue a separate query 487 // Of course, a modified version of the metaColumnsSQL query using a 488 // LEFT JOIN would have been much more elegant, but postgres does 489 // not support OUTER JOINS. So here is the clumsy way. 490 491 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; 492 493 $rskey = $this->Execute(sprintf($this->metaKeySQL,($table))); 494 // fetch all result in once for performance. 495 $keys =& $rskey->GetArray(); 496 if (isset($savem)) $this->SetFetchMode($savem); 497 $ADODB_FETCH_MODE = $save; 498 499 $rskey->Close(); 500 unset($rskey); 501 } 502 503 $rsdefa = array(); 504 if (!empty($this->metaDefaultsSQL)) { 505 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; 506 $sql = sprintf($this->metaDefaultsSQL, ($table)); 507 $rsdef = $this->Execute($sql); 508 if (isset($savem)) $this->SetFetchMode($savem); 509 $ADODB_FETCH_MODE = $save; 510 511 if ($rsdef) { 512 while (!$rsdef->EOF) { 513 $num = $rsdef->fields['num']; 514 $s = $rsdef->fields['def']; 515 if (strpos($s,'::')===false && substr($s, 0, 1) == "'") { /* quoted strings hack... for now... fixme */ 516 $s = substr($s, 1); 517 $s = substr($s, 0, strlen($s) - 1); 518 } 519 520 $rsdefa[$num] = $s; 521