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