Context Navigation

source: trunk/phpgwapi/inc/adodb/drivers/adodb-postgres64.inc.php @ 34

Revision 34, 30.8 KB checked in by niltonneto, 17 years ago (diff)
Versão nova do ADODB (4.5 para 4.95)
Property svn:eol-style set to `native` Property svn:executable set to ``*

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

Note: See TracBrowser for help on using the repository browser.

Download in other formats: