ChangeLog
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / drivers / adodb-postgres64.inc.php
1 <?php
2 /*
3 V4.90 8 June 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 ".$this->_transmode);
178 }
179
180 function RowLock($tables,$where,$flds='1 as ignore')
181 {
182 if (!$this->transCnt) $this->BeginTrans();
183 return $this->GetOne("select $flds from $tables where $where for update");
184 }
185
186 // returns true/false.
187 function CommitTrans($ok=true)
188 {
189 if ($this->transOff) return true;
190 if (!$ok) return $this->RollbackTrans();
191
192 $this->transCnt -= 1;
193 return @pg_Exec($this->_connectionID, "commit");
194 }
195
196 // returns true/false
197 function RollbackTrans()
198 {
199 if ($this->transOff) return true;
200 $this->transCnt -= 1;
201 return @pg_Exec($this->_connectionID, "rollback");
202 }
203
204 function &MetaTables($ttype=false,$showSchema=false,$mask=false)
205 {
206 $info = $this->ServerInfo();
207 if ($info['version'] >= 7.3) {
208 $this->metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%'
209 and schemaname not in ( 'pg_catalog','information_schema')
210 union
211 select viewname,'V' from pg_views where viewname not like 'pg\_%' and schemaname not in ( 'pg_catalog','information_schema') ";
212 }
213 if ($mask) {
214 $save = $this->metaTablesSQL;
215 $mask = $this->qstr(strtolower($mask));
216 if ($info['version']>=7.3)
217 $this->metaTablesSQL = "
218 select tablename,'T' from pg_tables where tablename like $mask and schemaname not in ( 'pg_catalog','information_schema')
219 union
220 select viewname,'V' from pg_views where viewname like $mask and schemaname not in ( 'pg_catalog','information_schema') ";
221 else
222 $this->metaTablesSQL = "
223 select tablename,'T' from pg_tables where tablename like $mask
224 union
225 select viewname,'V' from pg_views where viewname like $mask";
226 }
227 $ret =& ADOConnection::MetaTables($ttype,$showSchema);
228
229 if ($mask) {
230 $this->metaTablesSQL = $save;
231 }
232 return $ret;
233 }
234
235
236 // if magic quotes disabled, use pg_escape_string()
237 function qstr($s,$magic_quotes=false)
238 {
239 if (!$magic_quotes) {
240 if (ADODB_PHPVER >= 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 $fld->attnum = $rs->fields[6];
536
537 if ($fld->max_length <= 0) $fld->max_length = $rs->fields[3]-4;
538 if ($fld->max_length <= 0) $fld->max_length = -1;
539 if ($fld->type == 'numeric') {
540 $fld->scale = $fld->max_length & 0xFFFF;
541 $fld->max_length >>= 16;
542 }
543 // dannym
544 // 5 hasdefault; 6 num-of-column
545 $fld->has_default = ($rs->fields[5] == 't');
546 if ($fld->has_default) {
547 $fld->default_value = $rsdefa[$rs->fields[6]];
548 }
549
550 //Freek
551 $fld->not_null = $rs->fields[4] == 't';
552
553
554 // Freek
555 if (is_array($keys)) {
556 foreach($keys as $key) {
557 if ($fld->name == $key['column_name'] AND $key['primary_key'] == 't')
558 $fld->primary_key = true;
559 if ($fld->name == $key['column_name'] AND $key['unique_key'] == 't')
560 $fld->unique = true; // What name is more compatible?
561 }
562 }
563
564 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;
565 else $retarr[($normalize) ? strtoupper($fld->name) : $fld->name] = $fld;
566
567 $rs->MoveNext();
568 }
569 $rs->Close();
570 if (empty($retarr))
571 return $false;
572 else
573 return $retarr;
574
575 }
576
577 function &MetaIndexes ($table, $primary = FALSE)
578 {
579 global $ADODB_FETCH_MODE;
580
581 $schema = false;
582 $this->_findschema($table,$schema);
583
584 if ($schema) { // requires pgsql 7.3+ - pg_namespace used.
585 $sql = '
586 SELECT c.relname as "Name", i.indisunique as "Unique", i.indkey as "Columns"
587 FROM pg_catalog.pg_class c
588 JOIN pg_catalog.pg_index i ON i.indexrelid=c.oid
589 JOIN pg_catalog.pg_class c2 ON c2.oid=i.indrelid
590 ,pg_namespace n
591 WHERE (c2.relname=\'%s\' or c2.relname=lower(\'%s\')) and c.relnamespace=c2.relnamespace and c.relnamespace=n.oid and n.nspname=\'%s\'';
592 } else {
593 $sql = '
594 SELECT c.relname as "Name", i.indisunique as "Unique", i.indkey as "Columns"
595 FROM pg_catalog.pg_class c
596 JOIN pg_catalog.pg_index i ON i.indexrelid=c.oid
597 JOIN pg_catalog.pg_class c2 ON c2.oid=i.indrelid
598 WHERE (c2.relname=\'%s\' or c2.relname=lower(\'%s\'))';
599 }
600
601 if ($primary == FALSE) {
602 $sql .= ' AND i.indisprimary=false;';
603 }
604
605 $save = $ADODB_FETCH_MODE;
606 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
607 if ($this->fetchMode !== FALSE) {
608 $savem = $this->SetFetchMode(FALSE);
609 }
610
611 $rs = $this->Execute(sprintf($sql,$table,$table,$schema));
612 if (isset($savem)) {
613 $this->SetFetchMode($savem);
614 }
615 $ADODB_FETCH_MODE = $save;
616
617 if (!is_object($rs)) {
618 $false = false;
619 return $false;
620 }
621
622 $col_names = $this->MetaColumnNames($table,true,true);
623 //3rd param is use attnum,
624 // see http://sourceforge.net/tracker/index.php?func=detail&aid=1451245&group_id=42718&atid=433976
625 $indexes = array();
626 while ($row = $rs->FetchRow()) {
627 $columns = array();
628 foreach (explode(' ', $row[2]) as $col) {
629 $columns[] = $col_names[$col];
630 }
631
632 $indexes[$row[0]] = array(
633 'unique' => ($row[1] == 't'),
634 'columns' => $columns
635 );
636 }
637 return $indexes;
638 }
639
640 // returns true or false
641 //
642 // examples:
643 // $db->Connect("host=host1 user=user1 password=secret port=4341");
644 // $db->Connect('host1','user1','secret');
645 function _connect($str,$user='',$pwd='',$db='',$ctype=0)
646 {
647
648 if (!function_exists('pg_connect')) return null;
649
650 $this->_errorMsg = false;
651
652 if ($user || $pwd || $db) {
653 $user = adodb_addslashes($user);
654 $pwd = adodb_addslashes($pwd);
655 if (strlen($db) == 0) $db = 'template1';
656 $db = adodb_addslashes($db);
657 if ($str) {
658 $host = split(":", $str);
659 if ($host[0]) $str = "host=".adodb_addslashes($host[0]);
660 else $str = 'host=localhost';
661 if (isset($host[1])) $str .= " port=$host[1]";
662 else if (!empty($this->port)) $str .= " port=".$this->port;
663 }
664 if ($user) $str .= " user=".$user;
665 if ($pwd) $str .= " password=".$pwd;
666 if ($db) $str .= " dbname=".$db;
667 }
668
669 //if ($user) $linea = "user=$user host=$linea password=$pwd dbname=$db port=5432";
670
671 if ($ctype === 1) { // persistent
672 $this->_connectionID = pg_pconnect($str);
673 } else {
674 if ($ctype === -1) { // nconnect, we trick pgsql ext by changing the connection str
675 static $ncnt;
676
677 if (empty($ncnt)) $ncnt = 1;
678 else $ncnt += 1;
679
680 $str .= str_repeat(' ',$ncnt);
681 }
682 $this->_connectionID = pg_connect($str);
683 }
684 if ($this->_connectionID === false) return false;
685 $this->Execute("set datestyle='ISO'");
686 return true;
687 }
688
689 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabaseName)
690 {
691 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabaseName,-1);
692 }
693
694 // returns true or false
695 //
696 // examples:
697 // $db->PConnect("host=host1 user=user1 password=secret port=4341");
698 // $db->PConnect('host1','user1','secret');
699 function _pconnect($str,$user='',$pwd='',$db='')
700 {
701 return $this->_connect($str,$user,$pwd,$db,1);
702 }
703
704
705 // returns queryID or false
706 function _query($sql,$inputarr)
707 {
708 $this->_errorMsg = false;
709 if ($inputarr) {
710 /*
711 It appears that PREPARE/EXECUTE is slower for many queries.
712
713 For query executed 1000 times:
714 "select id,firstname,lastname from adoxyz
715 where firstname not like ? and lastname not like ? and id = ?"
716
717 with plan = 1.51861286163 secs
718 no plan = 1.26903700829 secs
719
720
721
722 */
723 $plan = 'P'.md5($sql);
724
725 $execp = '';
726 foreach($inputarr as $v) {
727 if ($execp) $execp .= ',';
728 if (is_string($v)) {
729 if (strncmp($v,"'",1) !== 0) $execp .= $this->qstr($v);
730 } else {
731 $execp .= $v;
732 }
733 }
734
735 if ($execp) $exsql = "EXECUTE $plan ($execp)";
736 else $exsql = "EXECUTE $plan";
737
738
739 $rez = @pg_exec($this->_connectionID,$exsql);
740 if (!$rez) {
741 # Perhaps plan does not exist? Prepare/compile plan.
742 $params = '';
743 foreach($inputarr as $v) {
744 if ($params) $params .= ',';
745 if (is_string($v)) {
746 $params .= 'VARCHAR';
747 } else if (is_integer($v)) {
748 $params .= 'INTEGER';
749 } else {
750 $params .= "REAL";
751 }
752 }
753 $sqlarr = explode('?',$sql);
754 //print_r($sqlarr);
755 $sql = '';
756 $i = 1;
757 foreach($sqlarr as $v) {
758 $sql .= $v.' $'.$i;
759 $i++;
760 }
761 $s = "PREPARE $plan ($params) AS ".substr($sql,0,strlen($sql)-2);
762 //adodb_pr($s);
763 pg_exec($this->_connectionID,$s);
764 //echo $this->ErrorMsg();
765 }
766
767 $rez = pg_exec($this->_connectionID,$exsql);
768 } else {
769 //adodb_backtrace();
770 $rez = pg_exec($this->_connectionID,$sql);
771 }
772 // check if no data returned, then no need to create real recordset
773 if ($rez && pg_numfields($rez) <= 0) {
774 if (is_resource($this->_resultid) && get_resource_type($this->_resultid) === 'pgsql result') {
775 pg_freeresult($this->_resultid);
776 }
777 $this->_resultid = $rez;
778 return true;
779 }
780
781 return $rez;
782 }
783
784 function _errconnect()
785 {
786 if (defined('DB_ERROR_CONNECT_FAILED')) return DB_ERROR_CONNECT_FAILED;
787 else return 'Database connection failed';
788 }
789
790 /* Returns: the last error message from previous database operation */
791 function ErrorMsg()
792 {
793 if ($this->_errorMsg !== false) return $this->_errorMsg;
794 if (ADODB_PHPVER >= 0x4300) {
795 if (!empty($this->_resultid)) {
796 $this->_errorMsg = @pg_result_error($this->_resultid);
797 if ($this->_errorMsg) return $this->_errorMsg;
798 }
799
800 if (!empty($this->_connectionID)) {
801 $this->_errorMsg = @pg_last_error($this->_connectionID);
802 } else $this->_errorMsg = $this->_errconnect();
803 } else {
804 if (empty($this->_connectionID)) $this->_errconnect();
805 else $this->_errorMsg = @pg_errormessage($this->_connectionID);
806 }
807 return $this->_errorMsg;
808 }
809
810 function ErrorNo()
811 {
812 $e = $this->ErrorMsg();
813 if (strlen($e)) {
814 return ADOConnection::MetaError($e);
815 }
816 return 0;
817 }
818
819 // returns true or false
820 function _close()
821 {
822 if ($this->transCnt) $this->RollbackTrans();
823 if ($this->_resultid) {
824 @pg_freeresult($this->_resultid);
825 $this->_resultid = false;
826 }
827 @pg_close($this->_connectionID);
828 $this->_connectionID = false;
829 return true;
830 }
831
832
833 /*
834 * Maximum size of C field
835 */
836 function CharMax()
837 {
838 return 1000000000; // should be 1 Gb?
839 }
840
841 /*
842 * Maximum size of X field
843 */
844 function TextMax()
845 {
846 return 1000000000; // should be 1 Gb?
847 }
848
849
850 }
851
852 /*--------------------------------------------------------------------------------------
853 Class Name: Recordset
854 --------------------------------------------------------------------------------------*/
855
856 class ADORecordSet_postgres64 extends ADORecordSet{
857 var $_blobArr;
858 var $databaseType = "postgres64";
859 var $canSeek = true;
860 function ADORecordSet_postgres64($queryID,$mode=false)
861 {
862 if ($mode === false) {
863 global $ADODB_FETCH_MODE;
864 $mode = $ADODB_FETCH_MODE;
865 }
866 switch ($mode)
867 {
868 case ADODB_FETCH_NUM: $this->fetchMode = PGSQL_NUM; break;
869 case ADODB_FETCH_ASSOC:$this->fetchMode = PGSQL_ASSOC; break;
870
871 case ADODB_FETCH_DEFAULT:
872 case ADODB_FETCH_BOTH:
873 default: $this->fetchMode = PGSQL_BOTH; break;
874 }
875 $this->adodbFetchMode = $mode;
876 $this->ADORecordSet($queryID);
877 }
878
879 function &GetRowAssoc($upper=true)
880 {
881 if ($this->fetchMode == PGSQL_ASSOC && !$upper) return $this->fields;
882 $row =& ADORecordSet::GetRowAssoc($upper);
883 return $row;
884 }
885
886 function _initrs()
887 {
888 global $ADODB_COUNTRECS;
889 $qid = $this->_queryID;
890 $this->_numOfRows = ($ADODB_COUNTRECS)? @pg_numrows($qid):-1;
891 $this->_numOfFields = @pg_numfields($qid);
892
893 // cache types for blob decode check
894 // apparently pg_fieldtype actually performs an sql query on the database to get the type.
895 if (empty($this->connection->noBlobs))
896 for ($i=0, $max = $this->_numOfFields; $i < $max; $i++) {
897 if (pg_fieldtype($qid,$i) == 'bytea') {
898 $this->_blobArr[$i] = pg_fieldname($qid,$i);
899 }
900 }
901 }
902
903 /* Use associative array to get fields array */
904 function Fields($colname)
905 {
906 if ($this->fetchMode != PGSQL_NUM) return @$this->fields[$colname];
907
908 if (!$this->bind) {
909 $this->bind = array();
910 for ($i=0; $i < $this->_numOfFields; $i++) {
911 $o = $this->FetchField($i);
912 $this->bind[strtoupper($o->name)] = $i;
913 }
914 }
915 return $this->fields[$this->bind[strtoupper($colname)]];
916 }
917
918 function &FetchField($off = 0)
919 {
920 // offsets begin at 0
921
922 $o= new ADOFieldObject();
923 $o->name = @pg_fieldname($this->_queryID,$off);
924 $o->type = @pg_fieldtype($this->_queryID,$off);
925 $o->max_length = @pg_fieldsize($this->_queryID,$off);
926 return $o;
927 }
928
929 function _seek($row)
930 {
931 return @pg_fetch_row($this->_queryID,$row);
932 }
933
934 function _decode($blob)
935 {
936 eval('$realblob="'.adodb_str_replace(array('"','$'),array('\"','\$'),$blob).'";');
937 return $realblob;
938 }
939
940 function _fixblobs()
941 {
942 if ($this->fetchMode == PGSQL_NUM || $this->fetchMode == PGSQL_BOTH) {
943 foreach($this->_blobArr as $k => $v) {
944 $this->fields[$k] = ADORecordSet_postgres64::_decode($this->fields[$k]);
945 }
946 }
947 if ($this->fetchMode == PGSQL_ASSOC || $this->fetchMode == PGSQL_BOTH) {
948 foreach($this->_blobArr as $k => $v) {
949 $this->fields[$v] = ADORecordSet_postgres64::_decode($this->fields[$v]);
950 }
951 }
952 }
953
954 // 10% speedup to move MoveNext to child class
955 function MoveNext()
956 {
957 if (!$this->EOF) {
958 $this->_currentRow++;
959 if ($this->_numOfRows < 0 || $this->_numOfRows > $this->_currentRow) {
960 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
961 if (is_array($this->fields) && $this->fields) {
962 if (isset($this->_blobArr)) $this->_fixblobs();
963 return true;
964 }
965 }
966 $this->fields = false;
967 $this->EOF = true;
968 }
969 return false;
970 }
971
972 function _fetch()
973 {
974
975 if ($this->_currentRow >= $this->_numOfRows && $this->_numOfRows >= 0)
976 return false;
977
978 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
979
980 if ($this->fields && isset($this->_blobArr)) $this->_fixblobs();
981
982 return (is_array($this->fields));
983 }
984
985 function _close()
986 {
987 return @pg_freeresult($this->_queryID);
988 }
989
990 function MetaType($t,$len=-1,$fieldobj=false)
991 {
992 if (is_object($t)) {
993 $fieldobj = $t;
994 $t = $fieldobj->type;
995 $len = $fieldobj->max_length;
996 }
997 switch (strtoupper($t)) {
998 case 'MONEY': // stupid, postgres expects money to be a string
999 case 'INTERVAL':
1000 case 'CHAR':
1001 case 'CHARACTER':
1002 case 'VARCHAR':
1003 case 'NAME':
1004 case 'BPCHAR':
1005 case '_VARCHAR':
1006 case 'INET':
1007 case 'MACADDR':
1008 if ($len <= $this->blobSize) return 'C';
1009
1010 case 'TEXT':
1011 return 'X';
1012
1013 case 'IMAGE': // user defined type
1014 case 'BLOB': // user defined type
1015 case 'BIT': // This is a bit string, not a single bit, so don't return 'L'
1016 case 'VARBIT':
1017 case 'BYTEA':
1018 return 'B';
1019
1020 case 'BOOL':
1021 case 'BOOLEAN':
1022 return 'L';
1023
1024 case 'DATE':
1025 return 'D';
1026
1027
1028 case 'TIMESTAMP WITHOUT TIME ZONE':
1029 case 'TIME':
1030 case 'DATETIME':
1031 case 'TIMESTAMP':
1032 case 'TIMESTAMPTZ':
1033 return 'T';
1034
1035 case 'SMALLINT':
1036 case 'BIGINT':
1037 case 'INTEGER':
1038 case 'INT8':
1039 case 'INT4':
1040 case 'INT2':
1041 if (isset($fieldobj) &&
1042 empty($fieldobj->primary_key) && empty($fieldobj->unique)) return 'I';
1043
1044 case 'OID':
1045 case 'SERIAL':
1046 return 'R';
1047
1048 default:
1049 return 'N';
1050 }
1051 }
1052
1053 }
1054 ?>