Context Navigation

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

Revision 370, 30.9 KB checked in by niltonneto, 16 years ago (diff)
Ver ocorrência #270 do Trac.
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->_nestedSQL = true;
682
683	/* jakjr: I really do not know why this is executed.
684	$this->Execute("set datestyle='ISO'");
685
686	$info = $this->ServerInfo();
687	$this->pgVersion = (float) substr($info['version'],0,3);
688	if ($this->pgVersion >= 7.1) { // good till version 999
689	$this->_nestedSQL = true;
690	}
691	*/
692	return true;
693	}
694
695	function _nconnect($argHostname, $argUsername, $argPassword, $argDatabaseName)
696	{
697	return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabaseName,-1);
698	}
699
700	// returns true or false
701	//
702	// examples:
703	// $db->PConnect("host=host1 user=user1 password=secret port=4341");
704	// $db->PConnect('host1','user1','secret');
705	function _pconnect($str,$user='',$pwd='',$db='')
706	{
707	return $this->_connect($str,$user,$pwd,$db,1);
708	}
709
710
711	// returns queryID or false
712	function _query($sql,$inputarr)
713	{
714	$this->_errorMsg = false;
715	if ($inputarr) {
716	/*
717	It appears that PREPARE/EXECUTE is slower for many queries.
718
719	For query executed 1000 times:
720	"select id,firstname,lastname from adoxyz
721	where firstname not like ? and lastname not like ? and id = ?"
722
723	with plan = 1.51861286163 secs
724	no plan = 1.26903700829 secs
725
726
727
728	*/
729	$plan = 'P'.md5($sql);
730
731	$execp = '';
732	foreach($inputarr as $v) {
733	if ($execp) $execp .= ',';
734	if (is_string($v)) {
735	if (strncmp($v,"'",1) !== 0) $execp .= $this->qstr($v);
736	} else {
737	$execp .= $v;
738	}
739	}
740
741	if ($execp) $exsql = "EXECUTE $plan ($execp)";
742	else $exsql = "EXECUTE $plan";
743
744
745	$rez = @pg_exec($this->_connectionID,$exsql);
746	if (!$rez) {
747	# Perhaps plan does not exist? Prepare/compile plan.
748	$params = '';
749	foreach($inputarr as $v) {
750	if ($params) $params .= ',';
751	if (is_string($v)) {
752	$params .= 'VARCHAR';
753	} else if (is_integer($v)) {
754	$params .= 'INTEGER';
755	} else {
756	$params .= "REAL";
757	}
758	}
759	$sqlarr = explode('?',$sql);
760	//print_r($sqlarr);
761	$sql = '';
762	$i = 1;
763	foreach($sqlarr as $v) {
764	$sql .= $v.' $'.$i;
765	$i++;
766	}
767	$s = "PREPARE $plan ($params) AS ".substr($sql,0,strlen($sql)-2);
768	//adodb_pr($s);
769	$rez = pg_exec($this->_connectionID,$s);
770	//echo $this->ErrorMsg();
771	}
772	if ($rez)
773	$rez = pg_exec($this->_connectionID,$exsql);
774	} else {
775	//adodb_backtrace();
776	$rez = pg_exec($this->_connectionID,$sql);
777	}
778	// check if no data returned, then no need to create real recordset
779	if ($rez && pg_numfields($rez) <= 0) {
780	if (is_resource($this->_resultid) && get_resource_type($this->_resultid) === 'pgsql result') {
781	pg_freeresult($this->_resultid);
782	}
783	$this->_resultid = $rez;
784	return true;
785	}
786
787	return $rez;
788	}
789
790	function _errconnect()
791	{
792	if (defined('DB_ERROR_CONNECT_FAILED')) return DB_ERROR_CONNECT_FAILED;
793	else return 'Database connection failed';
794	}
795
796	/* Returns: the last error message from previous database operation */
797	function ErrorMsg()
798	{
799	if ($this->_errorMsg !== false) return $this->_errorMsg;
800	if (ADODB_PHPVER >= 0x4300) {
801	if (!empty($this->_resultid)) {
802	$this->_errorMsg = @pg_result_error($this->_resultid);
803	if ($this->_errorMsg) return $this->_errorMsg;
804	}
805
806	if (!empty($this->_connectionID)) {
807	$this->_errorMsg = @pg_last_error($this->_connectionID);
808	} else $this->_errorMsg = $this->_errconnect();
809	} else {
810	if (empty($this->_connectionID)) $this->_errconnect();
811	else $this->_errorMsg = @pg_errormessage($this->_connectionID);
812	}
813	return $this->_errorMsg;
814	}
815
816	function ErrorNo()
817	{
818	$e = $this->ErrorMsg();
819	if (strlen($e)) {
820	return ADOConnection::MetaError($e);
821	}
822	return 0;
823	}
824
825	// returns true or false
826	function _close()
827	{
828	if ($this->transCnt) $this->RollbackTrans();
829	if ($this->_resultid) {
830	@pg_freeresult($this->_resultid);
831	$this->_resultid = false;
832	}
833	@pg_close($this->_connectionID);
834	$this->_connectionID = false;
835	return true;
836	}
837
838
839	/*
840	* Maximum size of C field
841	*/
842	function CharMax()
843	{
844	return 1000000000; // should be 1 Gb?
845	}
846
847	/*
848	* Maximum size of X field
849	*/
850	function TextMax()
851	{
852	return 1000000000; // should be 1 Gb?
853	}
854
855
856	}
857
858	/*--------------------------------------------------------------------------------------
859	Class Name: Recordset
860	--------------------------------------------------------------------------------------*/
861
862	class ADORecordSet_postgres64 extends ADORecordSet{
863	var $_blobArr;
864	var $databaseType = "postgres64";
865	var $canSeek = true;
866	function ADORecordSet_postgres64($queryID,$mode=false)
867	{
868	if ($mode === false) {
869	global $ADODB_FETCH_MODE;
870	$mode = $ADODB_FETCH_MODE;
871	}
872	switch ($mode)
873	{
874	case ADODB_FETCH_NUM: $this->fetchMode = PGSQL_NUM; break;
875	case ADODB_FETCH_ASSOC:$this->fetchMode = PGSQL_ASSOC; break;
876
877	case ADODB_FETCH_DEFAULT:
878	case ADODB_FETCH_BOTH:
879	default: $this->fetchMode = PGSQL_BOTH; break;
880	}
881	$this->adodbFetchMode = $mode;
882	$this->ADORecordSet($queryID);
883	}
884
885	function &GetRowAssoc($upper=true)
886	{
887	if ($this->fetchMode == PGSQL_ASSOC && !$upper) return $this->fields;
888	$row =& ADORecordSet::GetRowAssoc($upper);
889	return $row;
890	}
891
892	function _initrs()
893	{
894	global $ADODB_COUNTRECS;
895	$qid = $this->_queryID;
896	$this->_numOfRows = ($ADODB_COUNTRECS)? @pg_numrows($qid):-1;
897	$this->_numOfFields = @pg_numfields($qid);
898
899	// cache types for blob decode check
900	// apparently pg_fieldtype actually performs an sql query on the database to get the type.
901	if (empty($this->connection->noBlobs))
902	for ($i=0, $max = $this->_numOfFields; $i < $max; $i++) {
903	if (pg_fieldtype($qid,$i) == 'bytea') {
904	$this->_blobArr[$i] = pg_fieldname($qid,$i);
905	}
906	}
907	}
908
909	/* Use associative array to get fields array */
910	function Fields($colname)
911	{
912	if ($this->fetchMode != PGSQL_NUM) return @$this->fields[$colname];
913
914	if (!$this->bind) {
915	$this->bind = array();
916	for ($i=0; $i < $this->_numOfFields; $i++) {
917	$o = $this->FetchField($i);
918	$this->bind[strtoupper($o->name)] = $i;
919	}
920	}
921	return $this->fields[$this->bind[strtoupper($colname)]];
922	}
923
924	function &FetchField($off = 0)
925	{
926	// offsets begin at 0
927
928	$o= new ADOFieldObject();
929	$o->name = @pg_fieldname($this->_queryID,$off);
930	$o->type = @pg_fieldtype($this->_queryID,$off);
931	$o->max_length = @pg_fieldsize($this->_queryID,$off);
932	return $o;
933	}
934
935	function _seek($row)
936	{
937	return @pg_fetch_row($this->_queryID,$row);
938	}
939
940	function _decode($blob)
941	{
942	eval('$realblob="'.adodb_str_replace(array('"','$'),array('\"','\$'),$blob).'";');
943	return $realblob;
944	}
945
946	function _fixblobs()
947	{
948	if ($this->fetchMode == PGSQL_NUM \|\| $this->fetchMode == PGSQL_BOTH) {
949	foreach($this->_blobArr as $k => $v) {
950	$this->fields[$k] = ADORecordSet_postgres64::_decode($this->fields[$k]);
951	}
952	}
953	if ($this->fetchMode == PGSQL_ASSOC \|\| $this->fetchMode == PGSQL_BOTH) {
954	foreach($this->_blobArr as $k => $v) {
955	$this->fields[$v] = ADORecordSet_postgres64::_decode($this->fields[$v]);
956	}
957	}
958	}
959
960	// 10% speedup to move MoveNext to child class
961	function MoveNext()
962	{
963	if (!$this->EOF) {
964	$this->_currentRow++;
965	if ($this->_numOfRows < 0 \|\| $this->_numOfRows > $this->_currentRow) {
966	$this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
967	if (is_array($this->fields) && $this->fields) {
968	if (isset($this->_blobArr)) $this->_fixblobs();
969	return true;
970	}
971	}
972	$this->fields = false;
973	$this->EOF = true;
974	}
975	return false;
976	}
977
978	function _fetch()
979	{
980
981	if ($this->_currentRow >= $this->_numOfRows && $this->_numOfRows >= 0)
982	return false;
983
984	$this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
985
986	if ($this->fields && isset($this->_blobArr)) $this->_fixblobs();
987
988	return (is_array($this->fields));
989	}
990
991	function _close()
992	{
993	return @pg_freeresult($this->_queryID);
994	}
995
996	function MetaType($t,$len=-1,$fieldobj=false)
997	{
998	if (is_object($t)) {
999	$fieldobj = $t;
1000	$t = $fieldobj->type;
1001	$len = $fieldobj->max_length;
1002	}
1003	switch (strtoupper($t)) {
1004	case 'MONEY': // stupid, postgres expects money to be a string
1005	case 'INTERVAL':
1006	case 'CHAR':
1007	case 'CHARACTER':
1008	case 'VARCHAR':
1009	case 'NAME':
1010	case 'BPCHAR':
1011	case '_VARCHAR':
1012	case 'INET':
1013	case 'MACADDR':
1014	if ($len <= $this->blobSize) return 'C';
1015
1016	case 'TEXT':
1017	return 'X';
1018
1019	case 'IMAGE': // user defined type
1020	case 'BLOB': // user defined type
1021	case 'BIT': // This is a bit string, not a single bit, so don't return 'L'
1022	case 'VARBIT':
1023	case 'BYTEA':
1024	return 'B';
1025
1026	case 'BOOL':
1027	case 'BOOLEAN':
1028	return 'L';
1029
1030	case 'DATE':
1031	return 'D';
1032
1033
1034	case 'TIMESTAMP WITHOUT TIME ZONE':
1035	case 'TIME':
1036	case 'DATETIME':
1037	case 'TIMESTAMP':
1038	case 'TIMESTAMPTZ':
1039	return 'T';
1040
1041	case 'SMALLINT':
1042	case 'BIGINT':
1043	case 'INTEGER':
1044	case 'INT8':
1045	case 'INT4':
1046	case 'INT2':
1047	if (isset($fieldobj) &&
1048	empty($fieldobj->primary_key) && empty($fieldobj->unique)) return 'I';
1049
1050	case 'OID':
1051	case 'SERIAL':
1052	return 'R';
1053
1054	default:
1055	return 'N';
1056	}
1057	}
1058
1059	}
1060	?>

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

Download in other formats: