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