[!!!][TASK] Doctrine: Remove ext:dbal
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / drivers / adodb-postgres64.inc.php
1 <?php
2 /*
3 @version v5.20.3 01-Jan-2016
4 @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
5 @copyright (c) 2014 Damien Regad, Mark Newnham and the ADOdb community
6 Released under both BSD license and Lesser GPL library license.
7 Whenever there is any discrepancy between the two licenses,
8 the BSD license will take precedence.
9 Set tabs to 8.
10
11 Original version derived from Alberto Cerezal (acerezalp@dbnet.es) - DBNet Informatica & Comunicaciones.
12 08 Nov 2000 jlim - Minor corrections, removing mysql stuff
13 09 Nov 2000 jlim - added insertid support suggested by "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
14 jlim - changed concat operator to || and data types to MetaType to match documented pgsql types
15 see http://www.postgresql.org/devel-corner/docs/postgres/datatype.htm
16 22 Nov 2000 jlim - added changes to FetchField() and MetaTables() contributed by "raser" <raser@mail.zen.com.tw>
17 27 Nov 2000 jlim - added changes to _connect/_pconnect from ideas by "Lennie" <leen@wirehub.nl>
18 15 Dec 2000 jlim - added changes suggested by Additional code changes by "Eric G. Werk" egw@netguide.dk.
19 31 Jan 2002 jlim - finally installed postgresql. testing
20 01 Mar 2001 jlim - Freek Dijkstra changes, also support for text type
21
22 See http://www.varlena.com/varlena/GeneralBits/47.php
23
24 -- What indexes are on my table?
25 select * from pg_indexes where tablename = 'tablename';
26
27 -- What triggers are on my table?
28 select c.relname as "Table", t.tgname as "Trigger Name",
29 t.tgconstrname as "Constraint Name", t.tgenabled as "Enabled",
30 t.tgisconstraint as "Is Constraint", cc.relname as "Referenced Table",
31 p.proname as "Function Name"
32 from pg_trigger t, pg_class c, pg_class cc, pg_proc p
33 where t.tgfoid = p.oid and t.tgrelid = c.oid
34 and t.tgconstrrelid = cc.oid
35 and c.relname = 'tablename';
36
37 -- What constraints are on my table?
38 select r.relname as "Table", c.conname as "Constraint Name",
39 contype as "Constraint Type", conkey as "Key Columns",
40 confkey as "Foreign Columns", consrc as "Source"
41 from pg_class r, pg_constraint c
42 where r.oid = c.conrelid
43 and relname = 'tablename';
44
45 */
46
47 // security - hide paths
48 if (!defined('ADODB_DIR')) die();
49
50 function adodb_addslashes($s)
51 {
52 $len = strlen($s);
53 if ($len == 0) return "''";
54 if (strncmp($s,"'",1) === 0 && substr($s,$len-1) == "'") return $s; // already quoted
55
56 return "'".addslashes($s)."'";
57 }
58
59 class ADODB_postgres64 extends ADOConnection{
60 var $databaseType = 'postgres64';
61 var $dataProvider = 'postgres';
62 var $hasInsertID = true;
63 var $_resultid = false;
64 var $concat_operator='||';
65 var $metaDatabasesSQL = "select datname from pg_database where datname not in ('template0','template1') order by 1";
66 var $metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%'
67 and tablename not in ('sql_features', 'sql_implementation_info', 'sql_languages',
68 'sql_packages', 'sql_sizing', 'sql_sizing_profiles')
69 union
70 select viewname,'V' from pg_views where viewname not like 'pg\_%'";
71 //"select tablename from pg_tables where tablename not like 'pg_%' order by 1";
72 var $isoDates = true; // accepts dates in ISO format
73 var $sysDate = "CURRENT_DATE";
74 var $sysTimeStamp = "CURRENT_TIMESTAMP";
75 var $blobEncodeType = 'C';
76 var $metaColumnsSQL = "SELECT a.attname,t.typname,a.attlen,a.atttypmod,a.attnotnull,a.atthasdef,a.attnum
77 FROM pg_class c, pg_attribute a,pg_type t
78 WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s')) and a.attname not like '....%%'
79 AND a.attnum > 0 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
80
81 // used when schema defined
82 var $metaColumnsSQL1 = "SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
83 FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n
84 WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s'))
85 and c.relnamespace=n.oid and n.nspname='%s'
86 and a.attname not like '....%%' AND a.attnum > 0
87 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
88
89 // get primary key etc -- from Freek Dijkstra
90 var $metaKeySQL = "SELECT ic.relname AS index_name, a.attname AS column_name,i.indisunique AS unique_key, i.indisprimary AS primary_key
91 FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
92 WHERE bc.oid = i.indrelid AND ic.oid = i.indexrelid
93 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)
94 AND a.attrelid = bc.oid AND bc.relname = '%s'";
95
96 var $hasAffectedRows = true;
97 var $hasLimit = false; // set to true for pgsql 7 only. support pgsql/mysql SELECT * FROM TABLE LIMIT 10
98 // below suggested by Freek Dijkstra
99 var $true = 'TRUE'; // string that represents TRUE for a database
100 var $false = 'FALSE'; // string that represents FALSE for a database
101 var $fmtDate = "'Y-m-d'"; // used by DBDate() as the default date format used by the database
102 var $fmtTimeStamp = "'Y-m-d H:i:s'"; // used by DBTimeStamp as the default timestamp fmt.
103 var $hasMoveFirst = true;
104 var $hasGenID = true;
105 var $_genIDSQL = "SELECT NEXTVAL('%s')";
106 var $_genSeqSQL = "CREATE SEQUENCE %s START %s";
107 var $_dropSeqSQL = "DROP SEQUENCE %s";
108 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";
109 var $random = 'random()'; /// random function
110 var $autoRollback = true; // apparently pgsql does not autorollback properly before php 4.3.4
111 // http://bugs.php.net/bug.php?id=25404
112
113 var $uniqueIisR = true;
114 var $_bindInputArray = false; // requires postgresql 7.3+ and ability to modify database
115 var $disableBlobs = false; // set to true to disable blob checking, resulting in 2-5% improvement in performance.
116
117 var $_pnum = 0;
118
119 // The last (fmtTimeStamp is not entirely correct:
120 // PostgreSQL also has support for time zones,
121 // and writes these time in this format: "2001-03-01 18:59:26+02".
122 // There is no code for the "+02" time zone information, so I just left that out.
123 // I'm not familiar enough with both ADODB as well as Postgres
124 // to know what the concequences are. The other values are correct (wheren't in 0.94)
125 // -- Freek Dijkstra
126
127 function __construct()
128 {
129 // changes the metaColumnsSQL, adds columns: attnum[6]
130 }
131
132 function ServerInfo()
133 {
134 if (isset($this->version)) return $this->version;
135
136 $arr['description'] = $this->GetOne("select version()");
137 $arr['version'] = ADOConnection::_findvers($arr['description']);
138 $this->version = $arr;
139 return $arr;
140 }
141
142 function IfNull( $field, $ifNull )
143 {
144 return " coalesce($field, $ifNull) ";
145 }
146
147 // get the last id - never tested
148 function pg_insert_id($tablename,$fieldname)
149 {
150 $result=pg_query($this->_connectionID, 'SELECT last_value FROM '. $tablename .'_'. $fieldname .'_seq');
151 if ($result) {
152 $arr = @pg_fetch_row($result,0);
153 pg_free_result($result);
154 if (isset($arr[0])) return $arr[0];
155 }
156 return false;
157 }
158
159 /**
160 * Warning from http://www.php.net/manual/function.pg-getlastoid.php:
161 * Using a OID as a unique identifier is not generally wise.
162 * Unless you are very careful, you might end up with a tuple having
163 * a different OID if a database must be reloaded.
164 */
165 function _insertid($table,$column)
166 {
167 if (!is_resource($this->_resultid) || get_resource_type($this->_resultid) !== 'pgsql result') return false;
168 $oid = pg_getlastoid($this->_resultid);
169 // to really return the id, we need the table and column-name, else we can only return the oid != id
170 return empty($table) || empty($column) ? $oid : $this->GetOne("SELECT $column FROM $table WHERE oid=".(int)$oid);
171 }
172
173 function _affectedrows()
174 {
175 if (!is_resource($this->_resultid) || get_resource_type($this->_resultid) !== 'pgsql result') return false;
176 return pg_affected_rows($this->_resultid);
177 }
178
179
180 /**
181 * @return true/false
182 */
183 function BeginTrans()
184 {
185 if ($this->transOff) return true;
186 $this->transCnt += 1;
187 return pg_query($this->_connectionID, 'begin '.$this->_transmode);
188 }
189
190 function RowLock($tables,$where,$col='1 as adodbignore')
191 {
192 if (!$this->transCnt) $this->BeginTrans();
193 return $this->GetOne("select $col from $tables where $where for update");
194 }
195
196 // returns true/false.
197 function CommitTrans($ok=true)
198 {
199 if ($this->transOff) return true;
200 if (!$ok) return $this->RollbackTrans();
201
202 $this->transCnt -= 1;
203 return pg_query($this->_connectionID, 'commit');
204 }
205
206 // returns true/false
207 function RollbackTrans()
208 {
209 if ($this->transOff) return true;
210 $this->transCnt -= 1;
211 return pg_query($this->_connectionID, 'rollback');
212 }
213
214 function MetaTables($ttype=false,$showSchema=false,$mask=false)
215 {
216 $info = $this->ServerInfo();
217 if ($info['version'] >= 7.3) {
218 $this->metaTablesSQL = "
219 select table_name,'T' from information_schema.tables where table_schema not in ( 'pg_catalog','information_schema')
220 union
221 select table_name,'V' from information_schema.views where table_schema not in ( 'pg_catalog','information_schema') ";
222 }
223 if ($mask) {
224 $save = $this->metaTablesSQL;
225 $mask = $this->qstr(strtolower($mask));
226 if ($info['version']>=7.3)
227 $this->metaTablesSQL = "
228 select table_name,'T' from information_schema.tables where table_name like $mask and table_schema not in ( 'pg_catalog','information_schema')
229 union
230 select table_name,'V' from information_schema.views where table_name like $mask and table_schema not in ( 'pg_catalog','information_schema') ";
231 else
232 $this->metaTablesSQL = "
233 select tablename,'T' from pg_tables where tablename like $mask
234 union
235 select viewname,'V' from pg_views where viewname like $mask";
236 }
237 $ret = ADOConnection::MetaTables($ttype,$showSchema);
238
239 if ($mask) {
240 $this->metaTablesSQL = $save;
241 }
242 return $ret;
243 }
244
245
246 // if magic quotes disabled, use pg_escape_string()
247 function qstr($s,$magic_quotes=false)
248 {
249 if (is_bool($s)) return $s ? 'true' : 'false';
250
251 if (!$magic_quotes) {
252 if (ADODB_PHPVER >= 0x5200 && $this->_connectionID) {
253 return "'".pg_escape_string($this->_connectionID,$s)."'";
254 }
255 if (ADODB_PHPVER >= 0x4200) {
256 return "'".pg_escape_string($s)."'";
257 }
258 if ($this->replaceQuote[0] == '\\'){
259 $s = adodb_str_replace(array('\\',"\0"),array('\\\\',"\\\\000"),$s);
260 }
261 return "'".str_replace("'",$this->replaceQuote,$s)."'";
262 }
263
264 // undo magic quotes for "
265 $s = str_replace('\\"','"',$s);
266 return "'$s'";
267 }
268
269
270
271 // Format date column in sql string given an input format that understands Y M D
272 function SQLDate($fmt, $col=false)
273 {
274 if (!$col) $col = $this->sysTimeStamp;
275 $s = 'TO_CHAR('.$col.",'";
276
277 $len = strlen($fmt);
278 for ($i=0; $i < $len; $i++) {
279 $ch = $fmt[$i];
280 switch($ch) {
281 case 'Y':
282 case 'y':
283 $s .= 'YYYY';
284 break;
285 case 'Q':
286 case 'q':
287 $s .= 'Q';
288 break;
289
290 case 'M':
291 $s .= 'Mon';
292 break;
293
294 case 'm':
295 $s .= 'MM';
296 break;
297 case 'D':
298 case 'd':
299 $s .= 'DD';
300 break;
301
302 case 'H':
303 $s.= 'HH24';
304 break;
305
306 case 'h':
307 $s .= 'HH';
308 break;
309
310 case 'i':
311 $s .= 'MI';
312 break;
313
314 case 's':
315 $s .= 'SS';
316 break;
317
318 case 'a':
319 case 'A':
320 $s .= 'AM';
321 break;
322
323 case 'w':
324 $s .= 'D';
325 break;
326
327 case 'l':
328 $s .= 'DAY';
329 break;
330
331 case 'W':
332 $s .= 'WW';
333 break;
334
335 default:
336 // handle escape characters...
337 if ($ch == '\\') {
338 $i++;
339 $ch = substr($fmt,$i,1);
340 }
341 if (strpos('-/.:;, ',$ch) !== false) $s .= $ch;
342 else $s .= '"'.$ch.'"';
343
344 }
345 }
346 return $s. "')";
347 }
348
349
350
351 /*
352 * Load a Large Object from a file
353 * - the procedure stores the object id in the table and imports the object using
354 * postgres proprietary blob handling routines
355 *
356 * contributed by Mattia Rossi mattia@technologist.com
357 * modified for safe mode by juraj chlebec
358 */
359 function UpdateBlobFile($table,$column,$path,$where,$blobtype='BLOB')
360 {
361 pg_query($this->_connectionID, 'begin');
362
363 $fd = fopen($path,'r');
364 $contents = fread($fd,filesize($path));
365 fclose($fd);
366
367 $oid = pg_lo_create($this->_connectionID);
368 $handle = pg_lo_open($this->_connectionID, $oid, 'w');
369 pg_lo_write($handle, $contents);
370 pg_lo_close($handle);
371
372 // $oid = pg_lo_import ($path);
373 pg_query($this->_connectionID, 'commit');
374 $rs = ADOConnection::UpdateBlob($table,$column,$oid,$where,$blobtype);
375 $rez = !empty($rs);
376 return $rez;
377 }
378
379 /*
380 * Deletes/Unlinks a Blob from the database, otherwise it
381 * will be left behind
382 *
383 * Returns TRUE on success or FALSE on failure.
384 *
385 * contributed by Todd Rogers todd#windfox.net
386 */
387 function BlobDelete( $blob )
388 {
389 pg_query($this->_connectionID, 'begin');
390 $result = @pg_lo_unlink($blob);
391 pg_query($this->_connectionID, 'commit');
392 return( $result );
393 }
394
395 /*
396 Hueristic - not guaranteed to work.
397 */
398 function GuessOID($oid)
399 {
400 if (strlen($oid)>16) return false;
401 return is_numeric($oid);
402 }
403
404 /*
405 * If an OID is detected, then we use pg_lo_* to open the oid file and read the
406 * real blob from the db using the oid supplied as a parameter. If you are storing
407 * blobs using bytea, we autodetect and process it so this function is not needed.
408 *
409 * contributed by Mattia Rossi mattia@technologist.com
410 *
411 * see http://www.postgresql.org/idocs/index.php?largeobjects.html
412 *
413 * Since adodb 4.54, this returns the blob, instead of sending it to stdout. Also
414 * added maxsize parameter, which defaults to $db->maxblobsize if not defined.
415 */
416 function BlobDecode($blob,$maxsize=false,$hastrans=true)
417 {
418 if (!$this->GuessOID($blob)) return $blob;
419
420 if ($hastrans) pg_query($this->_connectionID,'begin');
421 $fd = @pg_lo_open($this->_connectionID,$blob,'r');
422 if ($fd === false) {
423 if ($hastrans) pg_query($this->_connectionID,'commit');
424 return $blob;
425 }
426 if (!$maxsize) $maxsize = $this->maxblobsize;
427 $realblob = @pg_lo_read($fd,$maxsize);
428 @pg_loclose($fd);
429 if ($hastrans) pg_query($this->_connectionID,'commit');
430 return $realblob;
431 }
432
433 /*
434 See http://www.postgresql.org/idocs/index.php?datatype-binary.html
435
436 NOTE: SQL string literals (input strings) must be preceded with two backslashes
437 due to the fact that they must pass through two parsers in the PostgreSQL
438 backend.
439 */
440 function BlobEncode($blob)
441 {
442 if (ADODB_PHPVER >= 0x5200) return pg_escape_bytea($this->_connectionID, $blob);
443 if (ADODB_PHPVER >= 0x4200) return pg_escape_bytea($blob);
444
445 /*92=backslash, 0=null, 39=single-quote*/
446 $badch = array(chr(92),chr(0),chr(39)); # \ null '
447 $fixch = array('\\\\134','\\\\000','\\\\047');
448 return adodb_str_replace($badch,$fixch,$blob);
449
450 // note that there is a pg_escape_bytea function only for php 4.2.0 or later
451 }
452
453 // assumes bytea for blob, and varchar for clob
454 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
455 {
456 if ($blobtype == 'CLOB') {
457 return $this->Execute("UPDATE $table SET $column=" . $this->qstr($val) . " WHERE $where");
458 }
459 // do not use bind params which uses qstr(), as blobencode() already quotes data
460 return $this->Execute("UPDATE $table SET $column='".$this->BlobEncode($val)."'::bytea WHERE $where");
461 }
462
463 function OffsetDate($dayFraction,$date=false)
464 {
465 if (!$date) $date = $this->sysDate;
466 else if (strncmp($date,"'",1) == 0) {
467 $len = strlen($date);
468 if (10 <= $len && $len <= 12) $date = 'date '.$date;
469 else $date = 'timestamp '.$date;
470 }
471
472
473 return "($date+interval'".($dayFraction * 1440)." minutes')";
474 #return "($date+interval'$dayFraction days')";
475 }
476
477 /**
478 * Generate the SQL to retrieve MetaColumns data
479 * @param string $table Table name
480 * @param string $schema Schema name (can be blank)
481 * @return string SQL statement to execute
482 */
483 protected function _generateMetaColumnsSQL($table, $schema)
484 {
485 if ($schema) {
486 return sprintf($this->metaColumnsSQL1, $table, $table, $schema);
487 }
488 else {
489 return sprintf($this->metaColumnsSQL, $table, $table, $schema);
490 }
491 }
492
493 // for schema support, pass in the $table param "$schema.$tabname".
494 // converts field names to lowercase, $upper is ignored
495 // see http://phplens.com/lens/lensforum/msgs.php?id=14018 for more info
496 function MetaColumns($table,$normalize=true)
497 {
498 global $ADODB_FETCH_MODE;
499
500 $schema = false;
501 $false = false;
502 $this->_findschema($table,$schema);
503
504 if ($normalize) $table = strtolower($table);
505
506 $save = $ADODB_FETCH_MODE;
507 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
508 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
509
510 $rs = $this->Execute($this->_generateMetaColumnsSQL($table, $schema));
511 if (isset($savem)) $this->SetFetchMode($savem);
512 $ADODB_FETCH_MODE = $save;
513
514 if ($rs === false) {
515 return $false;
516 }
517 if (!empty($this->metaKeySQL)) {
518 // If we want the primary keys, we have to issue a separate query
519 // Of course, a modified version of the metaColumnsSQL query using a
520 // LEFT JOIN would have been much more elegant, but postgres does
521 // not support OUTER JOINS. So here is the clumsy way.
522
523 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
524
525 $rskey = $this->Execute(sprintf($this->metaKeySQL,($table)));
526 // fetch all result in once for performance.
527 $keys = $rskey->GetArray();
528 if (isset($savem)) $this->SetFetchMode($savem);
529 $ADODB_FETCH_MODE = $save;
530
531 $rskey->Close();
532 unset($rskey);
533 }
534
535 $rsdefa = array();
536 if (!empty($this->metaDefaultsSQL)) {
537 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
538 $sql = sprintf($this->metaDefaultsSQL, ($table));
539 $rsdef = $this->Execute($sql);
540 if (isset($savem)) $this->SetFetchMode($savem);
541 $ADODB_FETCH_MODE = $save;
542
543 if ($rsdef) {
544 while (!$rsdef->EOF) {
545 $num = $rsdef->fields['num'];
546 $s = $rsdef->fields['def'];
547 if (strpos($s,'::')===false && substr($s, 0, 1) == "'") { /* quoted strings hack... for now... fixme */
548 $s = substr($s, 1);
549 $s = substr($s, 0, strlen($s) - 1);
550 }
551
552 $rsdefa[$num] = $s;
553 $rsdef->MoveNext();
554 }
555 } else {
556 ADOConnection::outp( "==> SQL => " . $sql);
557 }
558 unset($rsdef);
559 }
560
561 $retarr = array();
562 while (!$rs->EOF) {
563 $fld = new ADOFieldObject();
564 $fld->name = $rs->fields[0];
565 $fld->type = $rs->fields[1];
566 $fld->max_length = $rs->fields[2];
567 $fld->attnum = $rs->fields[6];
568
569 if ($fld->max_length <= 0) $fld->max_length = $rs->fields[3]-4;
570 if ($fld->max_length <= 0) $fld->max_length = -1;
571 if ($fld->type == 'numeric') {
572 $fld->scale = $fld->max_length & 0xFFFF;
573 $fld->max_length >>= 16;
574 }
575 // dannym
576 // 5 hasdefault; 6 num-of-column
577 $fld->has_default = ($rs->fields[5] == 't');
578 if ($fld->has_default) {
579 $fld->default_value = $rsdefa[$rs->fields[6]];
580 }
581
582 //Freek
583 $fld->not_null = $rs->fields[4] == 't';
584
585
586 // Freek
587 if (is_array($keys)) {
588 foreach($keys as $key) {
589 if ($fld->name == $key['column_name'] AND $key['primary_key'] == 't')
590 $fld->primary_key = true;
591 if ($fld->name == $key['column_name'] AND $key['unique_key'] == 't')
592 $fld->unique = true; // What name is more compatible?
593 }
594 }
595
596 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;
597 else $retarr[($normalize) ? strtoupper($fld->name) : $fld->name] = $fld;
598
599 $rs->MoveNext();
600 }
601 $rs->Close();
602 if (empty($retarr))
603 return $false;
604 else
605 return $retarr;
606
607 }
608
609 function Param($name,$type='C')
610 {
611 if ($name) {
612 $this->_pnum += 1;
613 } else {
614 // Reset param num if $name is false
615 $this->_pnum = 1;
616 }
617 return '$'.$this->_pnum;
618 }
619
620 function MetaIndexes ($table, $primary = FALSE, $owner = false)
621 {
622 global $ADODB_FETCH_MODE;
623
624 $schema = false;
625 $this->_findschema($table,$schema);
626
627 if ($schema) { // requires pgsql 7.3+ - pg_namespace used.
628 $sql = '
629 SELECT c.relname as "Name", i.indisunique as "Unique", i.indkey as "Columns"
630 FROM pg_catalog.pg_class c
631 JOIN pg_catalog.pg_index i ON i.indexrelid=c.oid
632 JOIN pg_catalog.pg_class c2 ON c2.oid=i.indrelid
633 ,pg_namespace n
634 WHERE (c2.relname=\'%s\' or c2.relname=lower(\'%s\'))
635 and c.relnamespace=c2.relnamespace
636 and c.relnamespace=n.oid
637 and n.nspname=\'%s\'';
638 } else {
639 $sql = '
640 SELECT c.relname as "Name", i.indisunique as "Unique", i.indkey as "Columns"
641 FROM pg_catalog.pg_class c
642 JOIN pg_catalog.pg_index i ON i.indexrelid=c.oid
643 JOIN pg_catalog.pg_class c2 ON c2.oid=i.indrelid
644 WHERE (c2.relname=\'%s\' or c2.relname=lower(\'%s\'))';
645 }
646
647 if ($primary == FALSE) {
648 $sql .= ' AND i.indisprimary=false;';
649 }
650
651 $save = $ADODB_FETCH_MODE;
652 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
653 if ($this->fetchMode !== FALSE) {
654 $savem = $this->SetFetchMode(FALSE);
655 }
656
657 $rs = $this->Execute(sprintf($sql,$table,$table,$schema));
658 if (isset($savem)) {
659 $this->SetFetchMode($savem);
660 }
661 $ADODB_FETCH_MODE = $save;
662
663 if (!is_object($rs)) {
664 $false = false;
665 return $false;
666 }
667
668 $col_names = $this->MetaColumnNames($table,true,true);
669 //3rd param is use attnum,
670 // see http://sourceforge.net/tracker/index.php?func=detail&aid=1451245&group_id=42718&atid=433976
671 $indexes = array();
672 while ($row = $rs->FetchRow()) {
673 $columns = array();
674 foreach (explode(' ', $row[2]) as $col) {
675 $columns[] = $col_names[$col];
676 }
677
678 $indexes[$row[0]] = array(
679 'unique' => ($row[1] == 't'),
680 'columns' => $columns
681 );
682 }
683 return $indexes;
684 }
685
686 // returns true or false
687 //
688 // examples:
689 // $db->Connect("host=host1 user=user1 password=secret port=4341");
690 // $db->Connect('host1','user1','secret');
691 function _connect($str,$user='',$pwd='',$db='',$ctype=0)
692 {
693 if (!function_exists('pg_connect')) return null;
694
695 $this->_errorMsg = false;
696
697 if ($user || $pwd || $db) {
698 $user = adodb_addslashes($user);
699 $pwd = adodb_addslashes($pwd);
700 if (strlen($db) == 0) $db = 'template1';
701 $db = adodb_addslashes($db);
702 if ($str) {
703 $host = explode(":", $str);
704 if ($host[0]) $str = "host=".adodb_addslashes($host[0]);
705 else $str = '';
706 if (isset($host[1])) $str .= " port=$host[1]";
707 else if (!empty($this->port)) $str .= " port=".$this->port;
708 }
709 if ($user) $str .= " user=".$user;
710 if ($pwd) $str .= " password=".$pwd;
711 if ($db) $str .= " dbname=".$db;
712 }
713
714 //if ($user) $linea = "user=$user host=$linea password=$pwd dbname=$db port=5432";
715
716 if ($ctype === 1) { // persistent
717 $this->_connectionID = pg_pconnect($str);
718 } else {
719 if ($ctype === -1) { // nconnect, we trick pgsql ext by changing the connection str
720 static $ncnt;
721
722 if (empty($ncnt)) $ncnt = 1;
723 else $ncnt += 1;
724
725 $str .= str_repeat(' ',$ncnt);
726 }
727 $this->_connectionID = pg_connect($str);
728 }
729 if ($this->_connectionID === false) return false;
730 $this->Execute("set datestyle='ISO'");
731
732 $info = $this->ServerInfo();
733 $this->pgVersion = (float) substr($info['version'],0,3);
734 if ($this->pgVersion >= 7.1) { // good till version 999
735 $this->_nestedSQL = true;
736 }
737
738 # PostgreSQL 9.0 changed the default output for bytea from 'escape' to 'hex'
739 # PHP does not handle 'hex' properly ('x74657374' is returned as 't657374')
740 # https://bugs.php.net/bug.php?id=59831 states this is in fact not a bug,
741 # so we manually set bytea_output
742 if ( !empty($this->connection->noBlobs) && version_compare($info['version'], '9.0', '>=')) {
743 $this->Execute('set bytea_output=escape');
744 }
745
746 return true;
747 }
748
749 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabaseName)
750 {
751 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabaseName,-1);
752 }
753
754 // returns true or false
755 //
756 // examples:
757 // $db->PConnect("host=host1 user=user1 password=secret port=4341");
758 // $db->PConnect('host1','user1','secret');
759 function _pconnect($str,$user='',$pwd='',$db='')
760 {
761 return $this->_connect($str,$user,$pwd,$db,1);
762 }
763
764
765 // returns queryID or false
766 function _query($sql,$inputarr=false)
767 {
768 $this->_pnum = 0;
769 $this->_errorMsg = false;
770 if ($inputarr) {
771 /*
772 It appears that PREPARE/EXECUTE is slower for many queries.
773
774 For query executed 1000 times:
775 "select id,firstname,lastname from adoxyz
776 where firstname not like ? and lastname not like ? and id = ?"
777
778 with plan = 1.51861286163 secs
779 no plan = 1.26903700829 secs
780 */
781 $plan = 'P'.md5($sql);
782
783 $execp = '';
784 foreach($inputarr as $v) {
785 if ($execp) $execp .= ',';
786 if (is_string($v)) {
787 if (strncmp($v,"'",1) !== 0) $execp .= $this->qstr($v);
788 } else {
789 $execp .= $v;
790 }
791 }
792
793 if ($execp) $exsql = "EXECUTE $plan ($execp)";
794 else $exsql = "EXECUTE $plan";
795
796
797 $rez = @pg_execute($this->_connectionID,$exsql);
798 if (!$rez) {
799 # Perhaps plan does not exist? Prepare/compile plan.
800 $params = '';
801 foreach($inputarr as $v) {
802 if ($params) $params .= ',';
803 if (is_string($v)) {
804 $params .= 'VARCHAR';
805 } else if (is_integer($v)) {
806 $params .= 'INTEGER';
807 } else {
808 $params .= "REAL";
809 }
810 }
811 $sqlarr = explode('?',$sql);
812 //print_r($sqlarr);
813 $sql = '';
814 $i = 1;
815 foreach($sqlarr as $v) {
816 $sql .= $v.' $'.$i;
817 $i++;
818 }
819 $s = "PREPARE $plan ($params) AS ".substr($sql,0,strlen($sql)-2);
820 //adodb_pr($s);
821 $rez = pg_execute($this->_connectionID,$s);
822 //echo $this->ErrorMsg();
823 }
824 if ($rez)
825 $rez = pg_execute($this->_connectionID,$exsql);
826 } else {
827 //adodb_backtrace();
828 $rez = pg_query($this->_connectionID,$sql);
829 }
830 // check if no data returned, then no need to create real recordset
831 if ($rez && pg_num_fields($rez) <= 0) {
832 if (is_resource($this->_resultid) && get_resource_type($this->_resultid) === 'pgsql result') {
833 pg_free_result($this->_resultid);
834 }
835 $this->_resultid = $rez;
836 return true;
837 }
838
839 return $rez;
840 }
841
842 function _errconnect()
843 {
844 if (defined('DB_ERROR_CONNECT_FAILED')) return DB_ERROR_CONNECT_FAILED;
845 else return 'Database connection failed';
846 }
847
848 /* Returns: the last error message from previous database operation */
849 function ErrorMsg()
850 {
851 if ($this->_errorMsg !== false) return $this->_errorMsg;
852 if (ADODB_PHPVER >= 0x4300) {
853 if (!empty($this->_resultid)) {
854 $this->_errorMsg = @pg_result_error($this->_resultid);
855 if ($this->_errorMsg) return $this->_errorMsg;
856 }
857
858 if (!empty($this->_connectionID)) {
859 $this->_errorMsg = @pg_last_error($this->_connectionID);
860 } else $this->_errorMsg = $this->_errconnect();
861 } else {
862 if (empty($this->_connectionID)) $this->_errconnect();
863 else $this->_errorMsg = @pg_errormessage($this->_connectionID);
864 }
865 return $this->_errorMsg;
866 }
867
868 function ErrorNo()
869 {
870 $e = $this->ErrorMsg();
871 if (strlen($e)) {
872 return ADOConnection::MetaError($e);
873 }
874 return 0;
875 }
876
877 // returns true or false
878 function _close()
879 {
880 if ($this->transCnt) $this->RollbackTrans();
881 if ($this->_resultid) {
882 @pg_free_result($this->_resultid);
883 $this->_resultid = false;
884 }
885 @pg_close($this->_connectionID);
886 $this->_connectionID = false;
887 return true;
888 }
889
890
891 /*
892 * Maximum size of C field
893 */
894 function CharMax()
895 {
896 return 1000000000; // should be 1 Gb?
897 }
898
899 /*
900 * Maximum size of X field
901 */
902 function TextMax()
903 {
904 return 1000000000; // should be 1 Gb?
905 }
906
907
908 }
909
910 /*--------------------------------------------------------------------------------------
911 Class Name: Recordset
912 --------------------------------------------------------------------------------------*/
913
914 class ADORecordSet_postgres64 extends ADORecordSet{
915 var $_blobArr;
916 var $databaseType = "postgres64";
917 var $canSeek = true;
918
919 function __construct($queryID, $mode=false)
920 {
921 if ($mode === false) {
922 global $ADODB_FETCH_MODE;
923 $mode = $ADODB_FETCH_MODE;
924 }
925 switch ($mode)
926 {
927 case ADODB_FETCH_NUM: $this->fetchMode = PGSQL_NUM; break;
928 case ADODB_FETCH_ASSOC:$this->fetchMode = PGSQL_ASSOC; break;
929
930 case ADODB_FETCH_DEFAULT:
931 case ADODB_FETCH_BOTH:
932 default: $this->fetchMode = PGSQL_BOTH; break;
933 }
934 $this->adodbFetchMode = $mode;
935
936 // Parent's constructor
937 parent::__construct($queryID);
938 }
939
940 function GetRowAssoc($upper = ADODB_ASSOC_CASE)
941 {
942 if ($this->fetchMode == PGSQL_ASSOC && $upper == ADODB_ASSOC_CASE_LOWER) {
943 return $this->fields;
944 }
945 $row = ADORecordSet::GetRowAssoc($upper);
946 return $row;
947 }
948
949
950 function _initrs()
951 {
952 global $ADODB_COUNTRECS;
953 $qid = $this->_queryID;
954 $this->_numOfRows = ($ADODB_COUNTRECS)? @pg_num_rows($qid):-1;
955 $this->_numOfFields = @pg_num_fields($qid);
956
957 // cache types for blob decode check
958 // apparently pg_field_type actually performs an sql query on the database to get the type.
959 if (empty($this->connection->noBlobs))
960 for ($i=0, $max = $this->_numOfFields; $i < $max; $i++) {
961 if (pg_field_type($qid,$i) == 'bytea') {
962 $this->_blobArr[$i] = pg_field_name($qid,$i);
963 }
964 }
965 }
966
967 /* Use associative array to get fields array */
968 function Fields($colname)
969 {
970 if ($this->fetchMode != PGSQL_NUM) return @$this->fields[$colname];
971
972 if (!$this->bind) {
973 $this->bind = array();
974 for ($i=0; $i < $this->_numOfFields; $i++) {
975 $o = $this->FetchField($i);
976 $this->bind[strtoupper($o->name)] = $i;
977 }
978 }
979 return $this->fields[$this->bind[strtoupper($colname)]];
980 }
981
982 function FetchField($off = 0)
983 {
984 // offsets begin at 0
985
986 $o= new ADOFieldObject();
987 $o->name = @pg_field_name($this->_queryID,$off);
988 $o->type = @pg_field_type($this->_queryID,$off);
989 $o->max_length = @pg_fieldsize($this->_queryID,$off);
990 return $o;
991 }
992
993 function _seek($row)
994 {
995 return @pg_fetch_row($this->_queryID,$row);
996 }
997
998 function _decode($blob)
999 {
1000 if ($blob === NULL) return NULL;
1001 // eval('$realblob="'.adodb_str_replace(array('"','$'),array('\"','\$'),$blob).'";');
1002 return pg_unescape_bytea($blob);
1003 }
1004
1005 function _fixblobs()
1006 {
1007 if ($this->fetchMode == PGSQL_NUM || $this->fetchMode == PGSQL_BOTH) {
1008 foreach($this->_blobArr as $k => $v) {
1009 $this->fields[$k] = ADORecordSet_postgres64::_decode($this->fields[$k]);
1010 }
1011 }
1012 if ($this->fetchMode == PGSQL_ASSOC || $this->fetchMode == PGSQL_BOTH) {
1013 foreach($this->_blobArr as $k => $v) {
1014 $this->fields[$v] = ADORecordSet_postgres64::_decode($this->fields[$v]);
1015 }
1016 }
1017 }
1018
1019 // 10% speedup to move MoveNext to child class
1020 function MoveNext()
1021 {
1022 if (!$this->EOF) {
1023 $this->_currentRow++;
1024 if ($this->_numOfRows < 0 || $this->_numOfRows > $this->_currentRow) {
1025 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
1026 if (is_array($this->fields) && $this->fields) {
1027 if (isset($this->_blobArr)) $this->_fixblobs();
1028 return true;
1029 }
1030 }
1031 $this->fields = false;
1032 $this->EOF = true;
1033 }
1034 return false;
1035 }
1036
1037 function _fetch()
1038 {
1039
1040 if ($this->_currentRow >= $this->_numOfRows && $this->_numOfRows >= 0)
1041 return false;
1042
1043 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
1044
1045 if ($this->fields && isset($this->_blobArr)) $this->_fixblobs();
1046
1047 return (is_array($this->fields));
1048 }
1049
1050 function _close()
1051 {
1052 return @pg_free_result($this->_queryID);
1053 }
1054
1055 function MetaType($t,$len=-1,$fieldobj=false)
1056 {
1057 if (is_object($t)) {
1058 $fieldobj = $t;
1059 $t = $fieldobj->type;
1060 $len = $fieldobj->max_length;
1061 }
1062 switch (strtoupper($t)) {
1063 case 'MONEY': // stupid, postgres expects money to be a string
1064 case 'INTERVAL':
1065 case 'CHAR':
1066 case 'CHARACTER':
1067 case 'VARCHAR':
1068 case 'NAME':
1069 case 'BPCHAR':
1070 case '_VARCHAR':
1071 case 'INET':
1072 case 'MACADDR':
1073 if ($len <= $this->blobSize) return 'C';
1074
1075 case 'TEXT':
1076 return 'X';
1077
1078 case 'IMAGE': // user defined type
1079 case 'BLOB': // user defined type
1080 case 'BIT': // This is a bit string, not a single bit, so don't return 'L'
1081 case 'VARBIT':
1082 case 'BYTEA':
1083 return 'B';
1084
1085 case 'BOOL':
1086 case 'BOOLEAN':
1087 return 'L';
1088
1089 case 'DATE':
1090 return 'D';
1091
1092
1093 case 'TIMESTAMP WITHOUT TIME ZONE':
1094 case 'TIME':
1095 case 'DATETIME':
1096 case 'TIMESTAMP':
1097 case 'TIMESTAMPTZ':
1098 return 'T';
1099
1100 case 'SMALLINT':
1101 case 'BIGINT':
1102 case 'INTEGER':
1103 case 'INT8':
1104 case 'INT4':
1105 case 'INT2':
1106 if (isset($fieldobj) &&
1107 empty($fieldobj->primary_key) && (!$this->connection->uniqueIisR || empty($fieldobj->unique))) return 'I';
1108
1109 case 'OID':
1110 case 'SERIAL':
1111 return 'R';
1112
1113 default:
1114 return 'N';
1115 }
1116 }
1117
1118 }