eRx captured and displayed in AMC report,
[openemr.git] / library / adodb / drivers / adodb-postgres64.inc.php
blob3b07227ce497f072f0a55fa7f43f5bfe64ce8e81
1 <?php
2 /*
3 V5.14 8 Sept 2011 (c) 2000-2011 John Lim (jlim#natsoft.com). All rights reserved.
4 Released under both BSD license and Lesser GPL library license.
5 Whenever there is any discrepancy between the two licenses,
6 the BSD license will take precedence.
7 Set tabs to 8.
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
20 See http://www.varlena.com/varlena/GeneralBits/47.php
22 -- What indexes are on my table?
23 select * from pg_indexes where tablename = 'tablename';
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';
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';
45 // security - hide paths
46 if (!defined('ADODB_DIR')) die();
48 function adodb_addslashes($s)
50 $len = strlen($s);
51 if ($len == 0) return "''";
52 if (strncmp($s,"'",1) === 0 && substr($s,$len-1) == "'") return $s; // already quoted
54 return "'".addslashes($s)."'";
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";
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";
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'";
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
108 var $uniqueIisR = true;
109 var $_bindInputArray = false; // requires postgresql 7.3+ and ability to modify database
110 var $disableBlobs = false; // set to true to disable blob checking, resulting in 2-5% improvement in performance.
112 var $_pnum = 0;
114 // The last (fmtTimeStamp is not entirely correct:
115 // PostgreSQL also has support for time zones,
116 // and writes these time in this format: "2001-03-01 18:59:26+02".
117 // There is no code for the "+02" time zone information, so I just left that out.
118 // I'm not familiar enough with both ADODB as well as Postgres
119 // to know what the concequences are. The other values are correct (wheren't in 0.94)
120 // -- Freek Dijkstra
122 function ADODB_postgres64()
124 // changes the metaColumnsSQL, adds columns: attnum[6]
127 function ServerInfo()
129 if (isset($this->version)) return $this->version;
131 $arr['description'] = $this->GetOne("select version()");
132 $arr['version'] = ADOConnection::_findvers($arr['description']);
133 $this->version = $arr;
134 return $arr;
137 function IfNull( $field, $ifNull )
139 return " coalesce($field, $ifNull) ";
142 // get the last id - never tested
143 function pg_insert_id($tablename,$fieldname)
145 $result=pg_exec($this->_connectionID, "SELECT last_value FROM ${tablename}_${fieldname}_seq");
146 if ($result) {
147 $arr = @pg_fetch_row($result,0);
148 pg_freeresult($result);
149 if (isset($arr[0])) return $arr[0];
151 return false;
154 /* Warning from http://www.php.net/manual/function.pg-getlastoid.php:
155 Using a OID as a unique identifier is not generally wise.
156 Unless you are very careful, you might end up with a tuple having
157 a different OID if a database must be reloaded. */
158 function _insertid($table,$column)
160 if (!is_resource($this->_resultid) || get_resource_type($this->_resultid) !== 'pgsql result') return false;
161 $oid = pg_getlastoid($this->_resultid);
162 // to really return the id, we need the table and column-name, else we can only return the oid != id
163 return empty($table) || empty($column) ? $oid : $this->GetOne("SELECT $column FROM $table WHERE oid=".(int)$oid);
166 // I get this error with PHP before 4.0.6 - jlim
167 // Warning: This compilation does not support pg_cmdtuples() in adodb-postgres.inc.php on line 44
168 function _affectedrows()
170 if (!is_resource($this->_resultid) || get_resource_type($this->_resultid) !== 'pgsql result') return false;
171 return pg_cmdtuples($this->_resultid);
175 // returns true/false
176 function BeginTrans()
178 if ($this->transOff) return true;
179 $this->transCnt += 1;
180 return @pg_Exec($this->_connectionID, "begin ".$this->_transmode);
183 function RowLock($tables,$where,$col='1 as adodbignore')
185 if (!$this->transCnt) $this->BeginTrans();
186 return $this->GetOne("select $col from $tables where $where for update");
189 // returns true/false.
190 function CommitTrans($ok=true)
192 if ($this->transOff) return true;
193 if (!$ok) return $this->RollbackTrans();
195 $this->transCnt -= 1;
196 return @pg_Exec($this->_connectionID, "commit");
199 // returns true/false
200 function RollbackTrans()
202 if ($this->transOff) return true;
203 $this->transCnt -= 1;
204 return @pg_Exec($this->_connectionID, "rollback");
207 function MetaTables($ttype=false,$showSchema=false,$mask=false)
209 $info = $this->ServerInfo();
210 if ($info['version'] >= 7.3) {
211 $this->metaTablesSQL = "select table_name,'T' from information_schema.tables where table_schema not in ( 'pg_catalog','information_schema')
212 union
213 select table_name,'V' from information_schema.views where table_schema not in ( 'pg_catalog','information_schema') ";
215 if ($mask) {
216 $save = $this->metaTablesSQL;
217 $mask = $this->qstr(strtolower($mask));
218 if ($info['version']>=7.3)
219 $this->metaTablesSQL = "select table_name,'T' from information_schema.tables where table_name like $mask and table_schema not in ( 'pg_catalog','information_schema')
220 union
221 select table_name,'V' from information_schema.views where table_name like $mask and table_schema not in ( 'pg_catalog','information_schema') ";
222 else
223 $this->metaTablesSQL = "
224 select tablename,'T' from pg_tables where tablename like $mask
225 union
226 select viewname,'V' from pg_views where viewname like $mask";
228 $ret = ADOConnection::MetaTables($ttype,$showSchema);
230 if ($mask) {
231 $this->metaTablesSQL = $save;
233 return $ret;
237 // if magic quotes disabled, use pg_escape_string()
238 function qstr($s,$magic_quotes=false)
240 if (is_bool($s)) return $s ? 'true' : 'false';
242 if (!$magic_quotes) {
243 if (ADODB_PHPVER >= 0x5200) {
244 return "'".pg_escape_string($this->_connectionID,$s)."'";
246 if (ADODB_PHPVER >= 0x4200) {
247 return "'".pg_escape_string($s)."'";
249 if ($this->replaceQuote[0] == '\\'){
250 $s = adodb_str_replace(array('\\',"\0"),array('\\\\',"\\\\000"),$s);
252 return "'".str_replace("'",$this->replaceQuote,$s)."'";
255 // undo magic quotes for "
256 $s = str_replace('\\"','"',$s);
257 return "'$s'";
262 // Format date column in sql string given an input format that understands Y M D
263 function SQLDate($fmt, $col=false)
265 if (!$col) $col = $this->sysTimeStamp;
266 $s = 'TO_CHAR('.$col.",'";
268 $len = strlen($fmt);
269 for ($i=0; $i < $len; $i++) {
270 $ch = $fmt[$i];
271 switch($ch) {
272 case 'Y':
273 case 'y':
274 $s .= 'YYYY';
275 break;
276 case 'Q':
277 case 'q':
278 $s .= 'Q';
279 break;
281 case 'M':
282 $s .= 'Mon';
283 break;
285 case 'm':
286 $s .= 'MM';
287 break;
288 case 'D':
289 case 'd':
290 $s .= 'DD';
291 break;
293 case 'H':
294 $s.= 'HH24';
295 break;
297 case 'h':
298 $s .= 'HH';
299 break;
301 case 'i':
302 $s .= 'MI';
303 break;
305 case 's':
306 $s .= 'SS';
307 break;
309 case 'a':
310 case 'A':
311 $s .= 'AM';
312 break;
314 case 'w':
315 $s .= 'D';
316 break;
318 case 'l':
319 $s .= 'DAY';
320 break;
322 case 'W':
323 $s .= 'WW';
324 break;
326 default:
327 // handle escape characters...
328 if ($ch == '\\') {
329 $i++;
330 $ch = substr($fmt,$i,1);
332 if (strpos('-/.:;, ',$ch) !== false) $s .= $ch;
333 else $s .= '"'.$ch.'"';
337 return $s. "')";
343 * Load a Large Object from a file
344 * - the procedure stores the object id in the table and imports the object using
345 * postgres proprietary blob handling routines
347 * contributed by Mattia Rossi mattia@technologist.com
348 * modified for safe mode by juraj chlebec
350 function UpdateBlobFile($table,$column,$path,$where,$blobtype='BLOB')
352 pg_exec ($this->_connectionID, "begin");
354 $fd = fopen($path,'r');
355 $contents = fread($fd,filesize($path));
356 fclose($fd);
358 $oid = pg_lo_create($this->_connectionID);
359 $handle = pg_lo_open($this->_connectionID, $oid, 'w');
360 pg_lo_write($handle, $contents);
361 pg_lo_close($handle);
363 // $oid = pg_lo_import ($path);
364 pg_exec($this->_connectionID, "commit");
365 $rs = ADOConnection::UpdateBlob($table,$column,$oid,$where,$blobtype);
366 $rez = !empty($rs);
367 return $rez;
371 * Deletes/Unlinks a Blob from the database, otherwise it
372 * will be left behind
374 * Returns TRUE on success or FALSE on failure.
376 * contributed by Todd Rogers todd#windfox.net
378 function BlobDelete( $blob )
380 pg_exec ($this->_connectionID, "begin");
381 $result = @pg_lo_unlink($blob);
382 pg_exec ($this->_connectionID, "commit");
383 return( $result );
387 Hueristic - not guaranteed to work.
389 function GuessOID($oid)
391 if (strlen($oid)>16) return false;
392 return is_numeric($oid);
396 * If an OID is detected, then we use pg_lo_* to open the oid file and read the
397 * real blob from the db using the oid supplied as a parameter. If you are storing
398 * blobs using bytea, we autodetect and process it so this function is not needed.
400 * contributed by Mattia Rossi mattia@technologist.com
402 * see http://www.postgresql.org/idocs/index.php?largeobjects.html
404 * Since adodb 4.54, this returns the blob, instead of sending it to stdout. Also
405 * added maxsize parameter, which defaults to $db->maxblobsize if not defined.
407 function BlobDecode($blob,$maxsize=false,$hastrans=true)
409 if (!$this->GuessOID($blob)) return $blob;
411 if ($hastrans) @pg_exec($this->_connectionID,"begin");
412 $fd = @pg_lo_open($this->_connectionID,$blob,"r");
413 if ($fd === false) {
414 if ($hastrans) @pg_exec($this->_connectionID,"commit");
415 return $blob;
417 if (!$maxsize) $maxsize = $this->maxblobsize;
418 $realblob = @pg_loread($fd,$maxsize);
419 @pg_loclose($fd);
420 if ($hastrans) @pg_exec($this->_connectionID,"commit");
421 return $realblob;
425 See http://www.postgresql.org/idocs/index.php?datatype-binary.html
427 NOTE: SQL string literals (input strings) must be preceded with two backslashes
428 due to the fact that they must pass through two parsers in the PostgreSQL
429 backend.
431 function BlobEncode($blob)
433 if (ADODB_PHPVER >= 0x5200) return pg_escape_bytea($this->_connectionID, $blob);
434 if (ADODB_PHPVER >= 0x4200) return pg_escape_bytea($blob);
436 /*92=backslash, 0=null, 39=single-quote*/
437 $badch = array(chr(92),chr(0),chr(39)); # \ null '
438 $fixch = array('\\\\134','\\\\000','\\\\047');
439 return adodb_str_replace($badch,$fixch,$blob);
441 // note that there is a pg_escape_bytea function only for php 4.2.0 or later
444 // assumes bytea for blob, and varchar for clob
445 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
447 if ($blobtype == 'CLOB') {
448 return $this->Execute("UPDATE $table SET $column=" . $this->qstr($val) . " WHERE $where");
450 // do not use bind params which uses qstr(), as blobencode() already quotes data
451 return $this->Execute("UPDATE $table SET $column='".$this->BlobEncode($val)."'::bytea WHERE $where");
454 function OffsetDate($dayFraction,$date=false)
456 if (!$date) $date = $this->sysDate;
457 else if (strncmp($date,"'",1) == 0) {
458 $len = strlen($date);
459 if (10 <= $len && $len <= 12) $date = 'date '.$date;
460 else $date = 'timestamp '.$date;
464 return "($date+interval'".($dayFraction * 1440)." minutes')";
465 #return "($date+interval'$dayFraction days')";
469 // for schema support, pass in the $table param "$schema.$tabname".
470 // converts field names to lowercase, $upper is ignored
471 // see http://phplens.com/lens/lensforum/msgs.php?id=14018 for more info
472 function MetaColumns($table,$normalize=true)
474 global $ADODB_FETCH_MODE;
476 $schema = false;
477 $false = false;
478 $this->_findschema($table,$schema);
480 if ($normalize) $table = strtolower($table);
482 $save = $ADODB_FETCH_MODE;
483 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
484 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
486 if ($schema) $rs = $this->Execute(sprintf($this->metaColumnsSQL1,$table,$table,$schema));
487 else $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table,$table));
488 if (isset($savem)) $this->SetFetchMode($savem);
489 $ADODB_FETCH_MODE = $save;
491 if ($rs === false) {
492 return $false;
494 if (!empty($this->metaKeySQL)) {
495 // If we want the primary keys, we have to issue a separate query
496 // Of course, a modified version of the metaColumnsSQL query using a
497 // LEFT JOIN would have been much more elegant, but postgres does
498 // not support OUTER JOINS. So here is the clumsy way.
500 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
502 $rskey = $this->Execute(sprintf($this->metaKeySQL,($table)));
503 // fetch all result in once for performance.
504 $keys = $rskey->GetArray();
505 if (isset($savem)) $this->SetFetchMode($savem);
506 $ADODB_FETCH_MODE = $save;
508 $rskey->Close();
509 unset($rskey);
512 $rsdefa = array();
513 if (!empty($this->metaDefaultsSQL)) {
514 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
515 $sql = sprintf($this->metaDefaultsSQL, ($table));
516 $rsdef = $this->Execute($sql);
517 if (isset($savem)) $this->SetFetchMode($savem);
518 $ADODB_FETCH_MODE = $save;
520 if ($rsdef) {
521 while (!$rsdef->EOF) {
522 $num = $rsdef->fields['num'];
523 $s = $rsdef->fields['def'];
524 if (strpos($s,'::')===false && substr($s, 0, 1) == "'") { /* quoted strings hack... for now... fixme */
525 $s = substr($s, 1);
526 $s = substr($s, 0, strlen($s) - 1);
529 $rsdefa[$num] = $s;
530 $rsdef->MoveNext();
532 } else {
533 ADOConnection::outp( "==> SQL => " . $sql);
535 unset($rsdef);
538 $retarr = array();
539 while (!$rs->EOF) {
540 $fld = new ADOFieldObject();
541 $fld->name = $rs->fields[0];
542 $fld->type = $rs->fields[1];
543 $fld->max_length = $rs->fields[2];
544 $fld->attnum = $rs->fields[6];
546 if ($fld->max_length <= 0) $fld->max_length = $rs->fields[3]-4;
547 if ($fld->max_length <= 0) $fld->max_length = -1;
548 if ($fld->type == 'numeric') {
549 $fld->scale = $fld->max_length & 0xFFFF;
550 $fld->max_length >>= 16;
552 // dannym
553 // 5 hasdefault; 6 num-of-column
554 $fld->has_default = ($rs->fields[5] == 't');
555 if ($fld->has_default) {
556 $fld->default_value = $rsdefa[$rs->fields[6]];
559 //Freek
560 $fld->not_null = $rs->fields[4] == 't';
563 // Freek
564 if (is_array($keys)) {
565 foreach($keys as $key) {
566 if ($fld->name == $key['column_name'] AND $key['primary_key'] == 't')
567 $fld->primary_key = true;
568 if ($fld->name == $key['column_name'] AND $key['unique_key'] == 't')
569 $fld->unique = true; // What name is more compatible?
573 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;
574 else $retarr[($normalize) ? strtoupper($fld->name) : $fld->name] = $fld;
576 $rs->MoveNext();
578 $rs->Close();
579 if (empty($retarr))
580 return $false;
581 else
582 return $retarr;
586 function Param($name)
588 $this->_pnum += 1;
589 return '$'.$this->_pnum;
592 function MetaIndexes ($table, $primary = FALSE, $owner = false)
594 global $ADODB_FETCH_MODE;
596 $schema = false;
597 $this->_findschema($table,$schema);
599 if ($schema) { // requires pgsql 7.3+ - pg_namespace used.
600 $sql = '
601 SELECT c.relname as "Name", i.indisunique as "Unique", i.indkey as "Columns"
602 FROM pg_catalog.pg_class c
603 JOIN pg_catalog.pg_index i ON i.indexrelid=c.oid
604 JOIN pg_catalog.pg_class c2 ON c2.oid=i.indrelid
605 ,pg_namespace n
606 WHERE (c2.relname=\'%s\' or c2.relname=lower(\'%s\')) and c.relnamespace=c2.relnamespace and c.relnamespace=n.oid and n.nspname=\'%s\'';
607 } else {
608 $sql = '
609 SELECT c.relname as "Name", i.indisunique as "Unique", i.indkey as "Columns"
610 FROM pg_catalog.pg_class c
611 JOIN pg_catalog.pg_index i ON i.indexrelid=c.oid
612 JOIN pg_catalog.pg_class c2 ON c2.oid=i.indrelid
613 WHERE (c2.relname=\'%s\' or c2.relname=lower(\'%s\'))';
616 if ($primary == FALSE) {
617 $sql .= ' AND i.indisprimary=false;';
620 $save = $ADODB_FETCH_MODE;
621 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
622 if ($this->fetchMode !== FALSE) {
623 $savem = $this->SetFetchMode(FALSE);
626 $rs = $this->Execute(sprintf($sql,$table,$table,$schema));
627 if (isset($savem)) {
628 $this->SetFetchMode($savem);
630 $ADODB_FETCH_MODE = $save;
632 if (!is_object($rs)) {
633 $false = false;
634 return $false;
637 $col_names = $this->MetaColumnNames($table,true,true);
638 //3rd param is use attnum,
639 // see http://sourceforge.net/tracker/index.php?func=detail&aid=1451245&group_id=42718&atid=433976
640 $indexes = array();
641 while ($row = $rs->FetchRow()) {
642 $columns = array();
643 foreach (explode(' ', $row[2]) as $col) {
644 $columns[] = $col_names[$col];
647 $indexes[$row[0]] = array(
648 'unique' => ($row[1] == 't'),
649 'columns' => $columns
652 return $indexes;
655 // returns true or false
657 // examples:
658 // $db->Connect("host=host1 user=user1 password=secret port=4341");
659 // $db->Connect('host1','user1','secret');
660 function _connect($str,$user='',$pwd='',$db='',$ctype=0)
663 if (!function_exists('pg_connect')) return null;
665 $this->_errorMsg = false;
667 if ($user || $pwd || $db) {
668 $user = adodb_addslashes($user);
669 $pwd = adodb_addslashes($pwd);
670 if (strlen($db) == 0) $db = 'template1';
671 $db = adodb_addslashes($db);
672 if ($str) {
673 $host = explode(":", $str);
674 if ($host[0]) $str = "host=".adodb_addslashes($host[0]);
675 else $str = '';
676 if (isset($host[1])) $str .= " port=$host[1]";
677 else if (!empty($this->port)) $str .= " port=".$this->port;
679 if ($user) $str .= " user=".$user;
680 if ($pwd) $str .= " password=".$pwd;
681 if ($db) $str .= " dbname=".$db;
684 //if ($user) $linea = "user=$user host=$linea password=$pwd dbname=$db port=5432";
686 if ($ctype === 1) { // persistent
687 $this->_connectionID = pg_pconnect($str);
688 } else {
689 if ($ctype === -1) { // nconnect, we trick pgsql ext by changing the connection str
690 static $ncnt;
692 if (empty($ncnt)) $ncnt = 1;
693 else $ncnt += 1;
695 $str .= str_repeat(' ',$ncnt);
697 $this->_connectionID = pg_connect($str);
699 if ($this->_connectionID === false) return false;
700 $this->Execute("set datestyle='ISO'");
702 $info = $this->ServerInfo();
703 $this->pgVersion = (float) substr($info['version'],0,3);
704 if ($this->pgVersion >= 7.1) { // good till version 999
705 $this->_nestedSQL = true;
707 return true;
710 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabaseName)
712 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabaseName,-1);
715 // returns true or false
717 // examples:
718 // $db->PConnect("host=host1 user=user1 password=secret port=4341");
719 // $db->PConnect('host1','user1','secret');
720 function _pconnect($str,$user='',$pwd='',$db='')
722 return $this->_connect($str,$user,$pwd,$db,1);
726 // returns queryID or false
727 function _query($sql,$inputarr=false)
729 $this->_pnum = 0;
730 $this->_errorMsg = false;
731 if ($inputarr) {
733 It appears that PREPARE/EXECUTE is slower for many queries.
735 For query executed 1000 times:
736 "select id,firstname,lastname from adoxyz
737 where firstname not like ? and lastname not like ? and id = ?"
739 with plan = 1.51861286163 secs
740 no plan = 1.26903700829 secs
745 $plan = 'P'.md5($sql);
747 $execp = '';
748 foreach($inputarr as $v) {
749 if ($execp) $execp .= ',';
750 if (is_string($v)) {
751 if (strncmp($v,"'",1) !== 0) $execp .= $this->qstr($v);
752 } else {
753 $execp .= $v;
757 if ($execp) $exsql = "EXECUTE $plan ($execp)";
758 else $exsql = "EXECUTE $plan";
761 $rez = @pg_exec($this->_connectionID,$exsql);
762 if (!$rez) {
763 # Perhaps plan does not exist? Prepare/compile plan.
764 $params = '';
765 foreach($inputarr as $v) {
766 if ($params) $params .= ',';
767 if (is_string($v)) {
768 $params .= 'VARCHAR';
769 } else if (is_integer($v)) {
770 $params .= 'INTEGER';
771 } else {
772 $params .= "REAL";
775 $sqlarr = explode('?',$sql);
776 //print_r($sqlarr);
777 $sql = '';
778 $i = 1;
779 foreach($sqlarr as $v) {
780 $sql .= $v.' $'.$i;
781 $i++;
783 $s = "PREPARE $plan ($params) AS ".substr($sql,0,strlen($sql)-2);
784 //adodb_pr($s);
785 $rez = pg_exec($this->_connectionID,$s);
786 //echo $this->ErrorMsg();
788 if ($rez)
789 $rez = pg_exec($this->_connectionID,$exsql);
790 } else {
791 //adodb_backtrace();
792 $rez = pg_exec($this->_connectionID,$sql);
794 // check if no data returned, then no need to create real recordset
795 if ($rez && pg_numfields($rez) <= 0) {
796 if (is_resource($this->_resultid) && get_resource_type($this->_resultid) === 'pgsql result') {
797 pg_freeresult($this->_resultid);
799 $this->_resultid = $rez;
800 return true;
803 return $rez;
806 function _errconnect()
808 if (defined('DB_ERROR_CONNECT_FAILED')) return DB_ERROR_CONNECT_FAILED;
809 else return 'Database connection failed';
812 /* Returns: the last error message from previous database operation */
813 function ErrorMsg()
815 if ($this->_errorMsg !== false) return $this->_errorMsg;
816 if (ADODB_PHPVER >= 0x4300) {
817 if (!empty($this->_resultid)) {
818 $this->_errorMsg = @pg_result_error($this->_resultid);
819 if ($this->_errorMsg) return $this->_errorMsg;
822 if (!empty($this->_connectionID)) {
823 $this->_errorMsg = @pg_last_error($this->_connectionID);
824 } else $this->_errorMsg = $this->_errconnect();
825 } else {
826 if (empty($this->_connectionID)) $this->_errconnect();
827 else $this->_errorMsg = @pg_errormessage($this->_connectionID);
829 return $this->_errorMsg;
832 function ErrorNo()
834 $e = $this->ErrorMsg();
835 if (strlen($e)) {
836 return ADOConnection::MetaError($e);
838 return 0;
841 // returns true or false
842 function _close()
844 if ($this->transCnt) $this->RollbackTrans();
845 if ($this->_resultid) {
846 @pg_freeresult($this->_resultid);
847 $this->_resultid = false;
849 @pg_close($this->_connectionID);
850 $this->_connectionID = false;
851 return true;
856 * Maximum size of C field
858 function CharMax()
860 return 1000000000; // should be 1 Gb?
864 * Maximum size of X field
866 function TextMax()
868 return 1000000000; // should be 1 Gb?
874 /*--------------------------------------------------------------------------------------
875 Class Name: Recordset
876 --------------------------------------------------------------------------------------*/
878 class ADORecordSet_postgres64 extends ADORecordSet{
879 var $_blobArr;
880 var $databaseType = "postgres64";
881 var $canSeek = true;
882 function ADORecordSet_postgres64($queryID,$mode=false)
884 if ($mode === false) {
885 global $ADODB_FETCH_MODE;
886 $mode = $ADODB_FETCH_MODE;
888 switch ($mode)
890 case ADODB_FETCH_NUM: $this->fetchMode = PGSQL_NUM; break;
891 case ADODB_FETCH_ASSOC:$this->fetchMode = PGSQL_ASSOC; break;
893 case ADODB_FETCH_DEFAULT:
894 case ADODB_FETCH_BOTH:
895 default: $this->fetchMode = PGSQL_BOTH; break;
897 $this->adodbFetchMode = $mode;
898 $this->ADORecordSet($queryID);
901 function GetRowAssoc($upper=true)
903 if ($this->fetchMode == PGSQL_ASSOC && !$upper) return $this->fields;
904 $row = ADORecordSet::GetRowAssoc($upper);
905 return $row;
909 function _initrs()
911 global $ADODB_COUNTRECS;
912 $qid = $this->_queryID;
913 $this->_numOfRows = ($ADODB_COUNTRECS)? @pg_numrows($qid):-1;
914 $this->_numOfFields = @pg_numfields($qid);
916 // cache types for blob decode check
917 // apparently pg_fieldtype actually performs an sql query on the database to get the type.
918 if (empty($this->connection->noBlobs))
919 for ($i=0, $max = $this->_numOfFields; $i < $max; $i++) {
920 if (pg_fieldtype($qid,$i) == 'bytea') {
921 $this->_blobArr[$i] = pg_fieldname($qid,$i);
926 /* Use associative array to get fields array */
927 function Fields($colname)
929 if ($this->fetchMode != PGSQL_NUM) return @$this->fields[$colname];
931 if (!$this->bind) {
932 $this->bind = array();
933 for ($i=0; $i < $this->_numOfFields; $i++) {
934 $o = $this->FetchField($i);
935 $this->bind[strtoupper($o->name)] = $i;
938 return $this->fields[$this->bind[strtoupper($colname)]];
941 function FetchField($off = 0)
943 // offsets begin at 0
945 $o= new ADOFieldObject();
946 $o->name = @pg_fieldname($this->_queryID,$off);
947 $o->type = @pg_fieldtype($this->_queryID,$off);
948 $o->max_length = @pg_fieldsize($this->_queryID,$off);
949 return $o;
952 function _seek($row)
954 return @pg_fetch_row($this->_queryID,$row);
957 function _decode($blob)
959 if ($blob === NULL) return NULL;
960 // eval('$realblob="'.adodb_str_replace(array('"','$'),array('\"','\$'),$blob).'";');
961 return pg_unescape_bytea($blob);
964 function _fixblobs()
966 if ($this->fetchMode == PGSQL_NUM || $this->fetchMode == PGSQL_BOTH) {
967 foreach($this->_blobArr as $k => $v) {
968 $this->fields[$k] = ADORecordSet_postgres64::_decode($this->fields[$k]);
971 if ($this->fetchMode == PGSQL_ASSOC || $this->fetchMode == PGSQL_BOTH) {
972 foreach($this->_blobArr as $k => $v) {
973 $this->fields[$v] = ADORecordSet_postgres64::_decode($this->fields[$v]);
978 // 10% speedup to move MoveNext to child class
979 function MoveNext()
981 if (!$this->EOF) {
982 $this->_currentRow++;
983 if ($this->_numOfRows < 0 || $this->_numOfRows > $this->_currentRow) {
984 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
985 if (is_array($this->fields) && $this->fields) {
986 if (isset($this->_blobArr)) $this->_fixblobs();
987 return true;
990 $this->fields = false;
991 $this->EOF = true;
993 return false;
996 function _fetch()
999 if ($this->_currentRow >= $this->_numOfRows && $this->_numOfRows >= 0)
1000 return false;
1002 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
1004 if ($this->fields && isset($this->_blobArr)) $this->_fixblobs();
1006 return (is_array($this->fields));
1009 function _close()
1011 return @pg_freeresult($this->_queryID);
1014 function MetaType($t,$len=-1,$fieldobj=false)
1016 if (is_object($t)) {
1017 $fieldobj = $t;
1018 $t = $fieldobj->type;
1019 $len = $fieldobj->max_length;
1021 switch (strtoupper($t)) {
1022 case 'MONEY': // stupid, postgres expects money to be a string
1023 case 'INTERVAL':
1024 case 'CHAR':
1025 case 'CHARACTER':
1026 case 'VARCHAR':
1027 case 'NAME':
1028 case 'BPCHAR':
1029 case '_VARCHAR':
1030 case 'INET':
1031 case 'MACADDR':
1032 if ($len <= $this->blobSize) return 'C';
1034 case 'TEXT':
1035 return 'X';
1037 case 'IMAGE': // user defined type
1038 case 'BLOB': // user defined type
1039 case 'BIT': // This is a bit string, not a single bit, so don't return 'L'
1040 case 'VARBIT':
1041 case 'BYTEA':
1042 return 'B';
1044 case 'BOOL':
1045 case 'BOOLEAN':
1046 return 'L';
1048 case 'DATE':
1049 return 'D';
1052 case 'TIMESTAMP WITHOUT TIME ZONE':
1053 case 'TIME':
1054 case 'DATETIME':
1055 case 'TIMESTAMP':
1056 case 'TIMESTAMPTZ':
1057 return 'T';
1059 case 'SMALLINT':
1060 case 'BIGINT':
1061 case 'INTEGER':
1062 case 'INT8':
1063 case 'INT4':
1064 case 'INT2':
1065 if (isset($fieldobj) &&
1066 empty($fieldobj->primary_key) && (!$this->connection->uniqueIisR || empty($fieldobj->unique))) return 'I';
1068 case 'OID':
1069 case 'SERIAL':
1070 return 'R';
1072 default:
1073 return 'N';