Update to upstream version 4.94
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / drivers / adodb-postgres64.inc.php
1 <?php
2 /*
3 V4.94 23 Jan 2007 (c) 2000-2007 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 ".$this->_transmode);
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 >= 0x5200) {
241 return "'".pg_escape_string($this->_connectionID,$s)."'";
242 }
243 if (ADODB_PHPVER >= 0x4200) {
244 return "'".pg_escape_string($s)."'";
245 }
246 if ($this->replaceQuote[0] == '\\'){
247 $s = adodb_str_replace(array('\\',"\0"),array('\\\\',"\\\\000"),$s);
248 }
249 return "'".str_replace("'",$this->replaceQuote,$s)."'";
250 }
251
252 // undo magic quotes for "
253 $s = str_replace('\\"','"',$s);
254 return "'$s'";
255 }
256
257
258
259 // Format date column in sql string given an input format that understands Y M D
260 function SQLDate($fmt, $col=false)
261 {
262 if (!$col) $col = $this->sysTimeStamp;
263 $s = 'TO_CHAR('.$col.",'";
264
265 $len = strlen($fmt);
266 for ($i=0; $i < $len; $i++) {
267 $ch = $fmt[$i];
268 switch($ch) {
269 case 'Y':
270 case 'y':
271 $s .= 'YYYY';
272 break;
273 case 'Q':
274 case 'q':
275 $s .= 'Q';
276 break;
277
278 case 'M':
279 $s .= 'Mon';
280 break;
281
282 case 'm':
283 $s .= 'MM';
284 break;
285 case 'D':
286 case 'd':
287 $s .= 'DD';
288 break;
289
290 case 'H':
291 $s.= 'HH24';
292 break;
293
294 case 'h':
295 $s .= 'HH';
296 break;
297
298 case 'i':
299 $s .= 'MI';
300 break;
301
302 case 's':
303 $s .= 'SS';
304 break;
305
306 case 'a':
307 case 'A':
308 $s .= 'AM';
309 break;
310
311 case 'w':
312 $s .= 'D';
313 break;
314
315 case 'l':
316 $s .= 'DAY';
317 break;
318
319 case 'W':
320 $s .= 'WW';
321 break;
322
323 default:
324 // handle escape characters...
325 if ($ch == '\\') {
326 $i++;
327 $ch = substr($fmt,$i,1);
328 }
329 if (strpos('-/.:;, ',$ch) !== false) $s .= $ch;
330 else $s .= '"'.$ch.'"';
331
332 }
333 }
334 return $s. "')";
335 }
336
337
338
339 /*
340 * Load a Large Object from a file
341 * - the procedure stores the object id in the table and imports the object using
342 * postgres proprietary blob handling routines
343 *
344 * contributed by Mattia Rossi mattia@technologist.com
345 * modified for safe mode by juraj chlebec
346 */
347 function UpdateBlobFile($table,$column,$path,$where,$blobtype='BLOB')
348 {
349 pg_exec ($this->_connectionID, "begin");
350
351 $fd = fopen($path,'r');
352 $contents = fread($fd,filesize($path));
353 fclose($fd);
354
355 $oid = pg_lo_create($this->_connectionID);
356 $handle = pg_lo_open($this->_connectionID, $oid, 'w');
357 pg_lo_write($handle, $contents);
358 pg_lo_close($handle);
359
360 // $oid = pg_lo_import ($path);
361 pg_exec($this->_connectionID, "commit");
362 $rs = ADOConnection::UpdateBlob($table,$column,$oid,$where,$blobtype);
363 $rez = !empty($rs);
364 return $rez;
365 }
366
367 /*
368 * Deletes/Unlinks a Blob from the database, otherwise it
369 * will be left behind
370 *
371 * Returns TRUE on success or FALSE on failure.
372 *
373 * contributed by Todd Rogers todd#windfox.net
374 */
375 function BlobDelete( $blob )
376 {
377 pg_exec ($this->_connectionID, "begin");
378 $result = @pg_lo_unlink($blob);
379 pg_exec ($this->_connectionID, "commit");
380 return( $result );
381 }
382
383 /*
384 Hueristic - not guaranteed to work.
385 */
386 function GuessOID($oid)
387 {
388 if (strlen($oid)>16) return false;
389 return is_numeric($oid);
390 }
391
392 /*
393 * If an OID is detected, then we use pg_lo_* to open the oid file and read the
394 * real blob from the db using the oid supplied as a parameter. If you are storing
395 * blobs using bytea, we autodetect and process it so this function is not needed.
396 *
397 * contributed by Mattia Rossi mattia@technologist.com
398 *
399 * see http://www.postgresql.org/idocs/index.php?largeobjects.html
400 *
401 * Since adodb 4.54, this returns the blob, instead of sending it to stdout. Also
402 * added maxsize parameter, which defaults to $db->maxblobsize if not defined.
403 */
404 function BlobDecode($blob,$maxsize=false,$hastrans=true)
405 {
406 if (!$this->GuessOID($blob)) return $blob;
407
408 if ($hastrans) @pg_exec($this->_connectionID,"begin");
409 $fd = @pg_lo_open($this->_connectionID,$blob,"r");
410 if ($fd === false) {
411 if ($hastrans) @pg_exec($this->_connectionID,"commit");
412 return $blob;
413 }
414 if (!$maxsize) $maxsize = $this->maxblobsize;
415 $realblob = @pg_loread($fd,$maxsize);
416 @pg_loclose($fd);
417 if ($hastrans) @pg_exec($this->_connectionID,"commit");
418 return $realblob;
419 }
420
421 /*
422 See http://www.postgresql.org/idocs/index.php?datatype-binary.html
423
424 NOTE: SQL string literals (input strings) must be preceded with two backslashes
425 due to the fact that they must pass through two parsers in the PostgreSQL
426 backend.
427 */
428 function BlobEncode($blob)
429 {
430 if (ADODB_PHPVER >= 0x5200) return pg_escape_bytea($this->_connectionID, $blob);
431 if (ADODB_PHPVER >= 0x4200) return pg_escape_bytea($blob);
432
433 /*92=backslash, 0=null, 39=single-quote*/
434 $badch = array(chr(92),chr(0),chr(39)); # \ null '
435 $fixch = array('\\\\134','\\\\000','\\\\047');
436 return adodb_str_replace($badch,$fixch,$blob);
437
438 // note that there is a pg_escape_bytea function only for php 4.2.0 or later
439 }
440
441 // assumes bytea for blob, and varchar for clob
442 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
443 {
444
445 if ($blobtype == 'CLOB') {
446 return $this->Execute("UPDATE $table SET $column=" . $this->qstr($val) . " WHERE $where");
447 }
448 // do not use bind params which uses qstr(), as blobencode() already quotes data
449 return $this->Execute("UPDATE $table SET $column='".$this->BlobEncode($val)."'::bytea WHERE $where");
450 }
451
452 function OffsetDate($dayFraction,$date=false)
453 {
454 if (!$date) $date = $this->sysDate;
455 else if (strncmp($date,"'",1) == 0) {
456 $len = strlen($date);
457 if (10 <= $len && $len <= 12) $date = 'date '.$date;
458 else $date = 'timestamp '.$date;
459 }
460 return "($date+interval'$dayFraction days')";
461 }
462
463
464 // for schema support, pass in the $table param "$schema.$tabname".
465 // converts field names to lowercase, $upper is ignored
466 // see http://phplens.com/lens/lensforum/msgs.php?id=14018 for more info
467 function &MetaColumns($table,$normalize=true)
468 {
469 global $ADODB_FETCH_MODE;
470
471 $schema = false;
472 $false = false;
473 $this->_findschema($table,$schema);
474
475 if ($normalize) $table = strtolower($table);
476
477 $save = $ADODB_FETCH_MODE;
478 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
479 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
480
481 if ($schema) $rs =& $this->Execute(sprintf($this->metaColumnsSQL1,$table,$table,$schema));
482 else $rs =& $this->Execute(sprintf($this->metaColumnsSQL,$table,$table));
483 if (isset($savem)) $this->SetFetchMode($savem);
484 $ADODB_FETCH_MODE = $save;
485
486 if ($rs === false) {
487 return $false;
488 }
489 if (!empty($this->metaKeySQL)) {
490 // If we want the primary keys, we have to issue a separate query
491 // Of course, a modified version of the metaColumnsSQL query using a
492 // LEFT JOIN would have been much more elegant, but postgres does
493 // not support OUTER JOINS. So here is the clumsy way.
494
495 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
496
497 $rskey = $this->Execute(sprintf($this->metaKeySQL,($table)));
498 // fetch all result in once for performance.
499 $keys =& $rskey->GetArray();
500 if (isset($savem)) $this->SetFetchMode($savem);
501 $ADODB_FETCH_MODE = $save;
502
503 $rskey->Close();
504 unset($rskey);
505 }
506
507 $rsdefa = array();
508 if (!empty($this->metaDefaultsSQL)) {
509 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
510 $sql = sprintf($this->metaDefaultsSQL, ($table));
511 $rsdef = $this->Execute($sql);
512 if (isset($savem)) $this->SetFetchMode($savem);
513 $ADODB_FETCH_MODE = $save;
514
515 if ($rsdef) {
516 while (!$rsdef->EOF) {
517 $num = $rsdef->fields['num'];
518 $s = $rsdef->fields['def'];
519 if (strpos($s,'::')===false && substr($s, 0, 1) == "'") { /* quoted strings hack... for now... fixme */
520 $s = substr($s, 1);
521 $s = substr($s, 0, strlen($s) - 1);
522 }
523
524 $rsdefa[$num] = $s;
525 $rsdef->MoveNext();
526 }
527 } else {
528 ADOConnection::outp( "==> SQL => " . $sql);
529 }
530 unset($rsdef);
531 }
532
533 $retarr = array();
534 while (!$rs->EOF) {
535 $fld = new ADOFieldObject();
536 $fld->name = $rs->fields[0];
537 $fld->type = $rs->fields[1];
538 $fld->max_length = $rs->fields[2];
539 $fld->attnum = $rs->fields[6];
540
541 if ($fld->max_length <= 0) $fld->max_length = $rs->fields[3]-4;
542 if ($fld->max_length <= 0) $fld->max_length = -1;
543 if ($fld->type == 'numeric') {
544 $fld->scale = $fld->max_length & 0xFFFF;
545 $fld->max_length >>= 16;
546 }
547 // dannym
548 // 5 hasdefault; 6 num-of-column
549 $fld->has_default = ($rs->fields[5] == 't');
550 if ($fld->has_default) {
551 $fld->default_value = $rsdefa[$rs->fields[6]];
552 }
553
554 //Freek
555 $fld->not_null = $rs->fields[4] == 't';
556
557
558 // Freek
559 if (is_array($keys)) {
560 foreach($keys as $key) {
561 if ($fld->name == $key['column_name'] AND $key['primary_key'] == 't')
562 $fld->primary_key = true;
563 if ($fld->name == $key['column_name'] AND $key['unique_key'] == 't')
564 $fld->unique = true; // What name is more compatible?
565 }
566 }
567
568 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;
569 else $retarr[($normalize) ? strtoupper($fld->name) : $fld->name] = $fld;
570
571 $rs->MoveNext();
572 }
573 $rs->Close();
574 if (empty($retarr))
575 return $false;
576 else
577 return $retarr;
578
579 }
580
581 function &MetaIndexes ($table, $primary = FALSE)
582 {
583 global $ADODB_FETCH_MODE;
584
585 $schema = false;
586 $this->_findschema($table,$schema);
587
588 if ($schema) { // requires pgsql 7.3+ - pg_namespace used.
589 $sql = '
590 SELECT c.relname as "Name", i.indisunique as "Unique", i.indkey as "Columns"
591 FROM pg_catalog.pg_class c
592 JOIN pg_catalog.pg_index i ON i.indexrelid=c.oid
593 JOIN pg_catalog.pg_class c2 ON c2.oid=i.indrelid
594 ,pg_namespace n
595 WHERE (c2.relname=\'%s\' or c2.relname=lower(\'%s\')) and c.relnamespace=c2.relnamespace and c.relnamespace=n.oid and n.nspname=\'%s\'';
596 } else {
597 $sql = '
598 SELECT c.relname as "Name", i.indisunique as "Unique", i.indkey as "Columns"
599 FROM pg_catalog.pg_class c
600 JOIN pg_catalog.pg_index i ON i.indexrelid=c.oid
601 JOIN pg_catalog.pg_class c2 ON c2.oid=i.indrelid
602 WHERE (c2.relname=\'%s\' or c2.relname=lower(\'%s\'))';
603 }
604
605 if ($primary == FALSE) {
606 $sql .= ' AND i.indisprimary=false;';
607 }
608
609 $save = $ADODB_FETCH_MODE;
610 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
611 if ($this->fetchMode !== FALSE) {
612 $savem = $this->SetFetchMode(FALSE);
613 }
614
615 $rs = $this->Execute(sprintf($sql,$table,$table,$schema));
616 if (isset($savem)) {
617 $this->SetFetchMode($savem);
618 }
619 $ADODB_FETCH_MODE = $save;
620
621 if (!is_object($rs)) {
622 $false = false;
623 return $false;
624 }
625
626 $col_names = $this->MetaColumnNames($table,true,true);
627 //3rd param is use attnum,
628 // see http://sourceforge.net/tracker/index.php?func=detail&aid=1451245&group_id=42718&atid=433976
629 $indexes = array();
630 while ($row = $rs->FetchRow()) {
631 $columns = array();
632 foreach (explode(' ', $row[2]) as $col) {
633 $columns[] = $col_names[$col];
634 }
635
636 $indexes[$row[0]] = array(
637 'unique' => ($row[1] == 't'),
638 'columns' => $columns
639 );
640 }
641 return $indexes;
642 }
643
644 // returns true or false
645 //
646 // examples:
647 // $db->Connect("host=host1 user=user1 password=secret port=4341");
648 // $db->Connect('host1','user1','secret');
649 function _connect($str,$user='',$pwd='',$db='',$ctype=0)
650 {
651
652 if (!function_exists('pg_connect')) return null;
653
654 $this->_errorMsg = false;
655
656 if ($user || $pwd || $db) {
657 $user = adodb_addslashes($user);
658 $pwd = adodb_addslashes($pwd);
659 if (strlen($db) == 0) $db = 'template1';
660 $db = adodb_addslashes($db);
661 if ($str) {
662 $host = split(":", $str);
663 if ($host[0]) $str = "host=".adodb_addslashes($host[0]);
664 else $str = '';
665 if (isset($host[1])) $str .= " port=$host[1]";
666 else if (!empty($this->port)) $str .= " port=".$this->port;
667 }
668 if ($user) $str .= " user=".$user;
669 if ($pwd) $str .= " password=".$pwd;
670 if ($db) $str .= " dbname=".$db;
671 }
672
673 //if ($user) $linea = "user=$user host=$linea password=$pwd dbname=$db port=5432";
674
675 if ($ctype === 1) { // persistent
676 $this->_connectionID = pg_pconnect($str);
677 } else {
678 if ($ctype === -1) { // nconnect, we trick pgsql ext by changing the connection str
679 static $ncnt;
680
681 if (empty($ncnt)) $ncnt = 1;
682 else $ncnt += 1;
683
684 $str .= str_repeat(' ',$ncnt);
685 }
686 $this->_connectionID = pg_connect($str);
687 }
688 if ($this->_connectionID === false) return false;
689 $this->Execute("set datestyle='ISO'");
690
691 $info = $this->ServerInfo();
692 $this->pgVersion = (float) substr($info['version'],0,3);
693 if ($this->pgVersion >= 7.1) { // good till version 999
694 $this->_nestedSQL = true;
695 }
696 return true;
697 }
698
699 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabaseName)
700 {
701 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabaseName,-1);
702 }
703
704 // returns true or false
705 //
706 // examples:
707 // $db->PConnect("host=host1 user=user1 password=secret port=4341");
708 // $db->PConnect('host1','user1','secret');
709 function _pconnect($str,$user='',$pwd='',$db='')
710 {
711 return $this->_connect($str,$user,$pwd,$db,1);
712 }
713
714
715 // returns queryID or false
716 function _query($sql,$inputarr)
717 {
718 $this->_errorMsg = false;
719 if ($inputarr) {
720 /*
721 It appears that PREPARE/EXECUTE is slower for many queries.
722
723 For query executed 1000 times:
724 "select id,firstname,lastname from adoxyz
725 where firstname not like ? and lastname not like ? and id = ?"
726
727 with plan = 1.51861286163 secs
728 no plan = 1.26903700829 secs
729
730
731
732 */
733 $plan = 'P'.md5($sql);
734
735 $execp = '';
736 foreach($inputarr as $v) {
737 if ($execp) $execp .= ',';
738 if (is_string($v)) {
739 if (strncmp($v,"'",1) !== 0) $execp .= $this->qstr($v);
740 } else {
741 $execp .= $v;
742 }
743 }
744
745 if ($execp) $exsql = "EXECUTE $plan ($execp)";
746 else $exsql = "EXECUTE $plan";
747
748
749 $rez = @pg_exec($this->_connectionID,$exsql);
750 if (!$rez) {
751 # Perhaps plan does not exist? Prepare/compile plan.
752 $params = '';
753 foreach($inputarr as $v) {
754 if ($params) $params .= ',';
755 if (is_string($v)) {
756 $params .= 'VARCHAR';
757 } else if (is_integer($v)) {
758 $params .= 'INTEGER';
759 } else {
760 $params .= "REAL";
761 }
762 }
763 $sqlarr = explode('?',$sql);
764 //print_r($sqlarr);
765 $sql = '';
766 $i = 1;
767 foreach($sqlarr as $v) {
768 $sql .= $v.' $'.$i;
769 $i++;
770 }
771 $s = "PREPARE $plan ($params) AS ".substr($sql,0,strlen($sql)-2);
772 //adodb_pr($s);
773 $rez = pg_exec($this->_connectionID,$s);
774 //echo $this->ErrorMsg();
775 }
776 if ($rez)
777 $rez = pg_exec($this->_connectionID,$exsql);
778 } else {
779 //adodb_backtrace();
780 $rez = pg_exec($this->_connectionID,$sql);
781 }
782 // check if no data returned, then no need to create real recordset
783 if ($rez && pg_numfields($rez) <= 0) {
784 if (is_resource($this->_resultid) && get_resource_type($this->_resultid) === 'pgsql result') {
785 pg_freeresult($this->_resultid);
786 }
787 $this->_resultid = $rez;
788 return true;
789 }
790
791 return $rez;
792 }
793
794 function _errconnect()
795 {
796 if (defined('DB_ERROR_CONNECT_FAILED')) return DB_ERROR_CONNECT_FAILED;
797 else return 'Database connection failed';
798 }
799
800 /* Returns: the last error message from previous database operation */
801 function ErrorMsg()
802 {
803 if ($this->_errorMsg !== false) return $this->_errorMsg;
804 if (ADODB_PHPVER >= 0x4300) {
805 if (!empty($this->_resultid)) {
806 $this->_errorMsg = @pg_result_error($this->_resultid);
807 if ($this->_errorMsg) return $this->_errorMsg;
808 }
809
810 if (!empty($this->_connectionID)) {
811 $this->_errorMsg = @pg_last_error($this->_connectionID);
812 } else $this->_errorMsg = $this->_errconnect();
813 } else {
814 if (empty($this->_connectionID)) $this->_errconnect();
815 else $this->_errorMsg = @pg_errormessage($this->_connectionID);
816 }
817 return $this->_errorMsg;
818 }
819
820 function ErrorNo()
821 {
822 $e = $this->ErrorMsg();
823 if (strlen($e)) {
824 return ADOConnection::MetaError($e);
825 }
826 return 0;
827 }
828
829 // returns true or false
830 function _close()
831 {
832 if ($this->transCnt) $this->RollbackTrans();
833 if ($this->_resultid) {
834 @pg_freeresult($this->_resultid);
835 $this->_resultid = false;
836 }
837 @pg_close($this->_connectionID);
838 $this->_connectionID = false;
839 return true;
840 }
841
842
843 /*
844 * Maximum size of C field
845 */
846 function CharMax()
847 {
848 return 1000000000; // should be 1 Gb?
849 }
850
851 /*
852 * Maximum size of X field
853 */
854 function TextMax()
855 {
856 return 1000000000; // should be 1 Gb?
857 }
858
859
860 }
861
862 /*--------------------------------------------------------------------------------------
863 Class Name: Recordset
864 --------------------------------------------------------------------------------------*/
865
866 class ADORecordSet_postgres64 extends ADORecordSet{
867 var $_blobArr;
868 var $databaseType = "postgres64";
869 var $canSeek = true;
870 function ADORecordSet_postgres64($queryID,$mode=false)
871 {
872 if ($mode === false) {
873 global $ADODB_FETCH_MODE;
874 $mode = $ADODB_FETCH_MODE;
875 }
876 switch ($mode)
877 {
878 case ADODB_FETCH_NUM: $this->fetchMode = PGSQL_NUM; break;
879 case ADODB_FETCH_ASSOC:$this->fetchMode = PGSQL_ASSOC; break;
880
881 case ADODB_FETCH_DEFAULT:
882 case ADODB_FETCH_BOTH:
883 default: $this->fetchMode = PGSQL_BOTH; break;
884 }
885 $this->adodbFetchMode = $mode;
886 $this->ADORecordSet($queryID);
887 }
888
889 function &GetRowAssoc($upper=true)
890 {
891 if ($this->fetchMode == PGSQL_ASSOC && !$upper) return $this->fields;
892 $row =& ADORecordSet::GetRowAssoc($upper);
893 return $row;
894 }
895
896 function _initrs()
897 {
898 global $ADODB_COUNTRECS;
899 $qid = $this->_queryID;
900 $this->_numOfRows = ($ADODB_COUNTRECS)? @pg_numrows($qid):-1;
901 $this->_numOfFields = @pg_numfields($qid);
902
903 // cache types for blob decode check
904 // apparently pg_fieldtype actually performs an sql query on the database to get the type.
905 if (empty($this->connection->noBlobs))
906 for ($i=0, $max = $this->_numOfFields; $i < $max; $i++) {
907 if (pg_fieldtype($qid,$i) == 'bytea') {
908 $this->_blobArr[$i] = pg_fieldname($qid,$i);
909 }
910 }
911 }
912
913 /* Use associative array to get fields array */
914 function Fields($colname)
915 {
916 if ($this->fetchMode != PGSQL_NUM) return @$this->fields[$colname];
917
918 if (!$this->bind) {
919 $this->bind = array();
920 for ($i=0; $i < $this->_numOfFields; $i++) {
921 $o = $this->FetchField($i);
922 $this->bind[strtoupper($o->name)] = $i;
923 }
924 }
925 return $this->fields[$this->bind[strtoupper($colname)]];
926 }
927
928 function &FetchField($off = 0)
929 {
930 // offsets begin at 0
931
932 $o= new ADOFieldObject();
933 $o->name = @pg_fieldname($this->_queryID,$off);
934 $o->type = @pg_fieldtype($this->_queryID,$off);
935 $o->max_length = @pg_fieldsize($this->_queryID,$off);
936 return $o;
937 }
938
939 function _seek($row)
940 {
941 return @pg_fetch_row($this->_queryID,$row);
942 }
943
944 function _decode($blob)
945 {
946 eval('$realblob="'.adodb_str_replace(array('"','$'),array('\"','\$'),$blob).'";');
947 return $realblob;
948 }
949
950 function _fixblobs()
951 {
952 if ($this->fetchMode == PGSQL_NUM || $this->fetchMode == PGSQL_BOTH) {
953 foreach($this->_blobArr as $k => $v) {
954 $this->fields[$k] = ADORecordSet_postgres64::_decode($this->fields[$k]);
955 }
956 }
957 if ($this->fetchMode == PGSQL_ASSOC || $this->fetchMode == PGSQL_BOTH) {
958 foreach($this->_blobArr as $k => $v) {
959 $this->fields[$v] = ADORecordSet_postgres64::_decode($this->fields[$v]);
960 }
961 }
962 }
963
964 // 10% speedup to move MoveNext to child class
965 function MoveNext()
966 {
967 if (!$this->EOF) {
968 $this->_currentRow++;
969 if ($this->_numOfRows < 0 || $this->_numOfRows > $this->_currentRow) {
970 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
971 if (is_array($this->fields) && $this->fields) {
972 if (isset($this->_blobArr)) $this->_fixblobs();
973 return true;
974 }
975 }
976 $this->fields = false;
977 $this->EOF = true;
978 }
979 return false;
980 }
981
982 function _fetch()
983 {
984
985 if ($this->_currentRow >= $this->_numOfRows && $this->_numOfRows >= 0)
986 return false;
987
988 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
989
990 if ($this->fields && isset($this->_blobArr)) $this->_fixblobs();
991
992 return (is_array($this->fields));
993 }
994
995 function _close()
996 {
997 return @pg_freeresult($this->_queryID);
998 }
999
1000 function MetaType($t,$len=-1,$fieldobj=false)
1001 {
1002 if (is_object($t)) {
1003 $fieldobj = $t;
1004 $t = $fieldobj->type;
1005 $len = $fieldobj->max_length;
1006 }
1007 switch (strtoupper($t)) {
1008 case 'MONEY': // stupid, postgres expects money to be a string
1009 case 'INTERVAL':
1010 case 'CHAR':
1011 case 'CHARACTER':
1012 case 'VARCHAR':
1013 case 'NAME':
1014 case 'BPCHAR':
1015 case '_VARCHAR':
1016 case 'INET':
1017 case 'MACADDR':
1018 if ($len <= $this->blobSize) return 'C';
1019
1020 case 'TEXT':
1021 return 'X';
1022
1023 case 'IMAGE': // user defined type
1024 case 'BLOB': // user defined type
1025 case 'BIT': // This is a bit string, not a single bit, so don't return 'L'
1026 case 'VARBIT':
1027 case 'BYTEA':
1028 return 'B';
1029
1030 case 'BOOL':
1031 case 'BOOLEAN':
1032 return 'L';
1033
1034 case 'DATE':
1035 return 'D';
1036
1037
1038 case 'TIMESTAMP WITHOUT TIME ZONE':
1039 case 'TIME':
1040 case 'DATETIME':
1041 case 'TIMESTAMP':
1042 case 'TIMESTAMPTZ':
1043 return 'T';
1044
1045 case 'SMALLINT':
1046 case 'BIGINT':
1047 case 'INTEGER':
1048 case 'INT8':
1049 case 'INT4':
1050 case 'INT2':
1051 if (isset($fieldobj) &&
1052 empty($fieldobj->primary_key) && empty($fieldobj->unique)) return 'I';
1053
1054 case 'OID':
1055 case 'SERIAL':
1056 return 'R';
1057
1058 default:
1059 return 'N';
1060 }
1061 }
1062
1063 }
1064 ?>