Context Navigation

← Previous Revision
Latest Revision
Next Revision →
Annotate
Revision Log

adodb-postgres64.inc.php @ 8222

Revision 8222, 31.5 KB checked in by angelo, 11 years ago (diff)
Ticket #3491 - Compatibilizar Expresso com novas versoes do PHP
Property svn:eol-style set to `native` Property svn:executable set to ``*

Line
1	<?php
2	/*
3	V5.18 3 Sep 2012 (c) 2000-2012 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.
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 $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.
111
112	var $_pnum = 0;
113
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
121
122	function ADODB_postgres64()
123	{
124	// changes the metaColumnsSQL, adds columns: attnum[6]
125	}
126
127	function ServerInfo()
128	{
129	if (isset($this->version)) return $this->version;
130
131	$arr['description'] = $this->GetOne("select version()");
132	$arr['version'] = ADOConnection::_findvers($arr['description']);
133	$this->version = $arr;
134	return $arr;
135	}
136
137	function IfNull( $field, $ifNull )
138	{
139	return " coalesce($field, $ifNull) ";
140	}
141
142	// get the last id - never tested
143	function pg_insert_id($tablename,$fieldname)
144	{
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];
150	}
151	return false;
152	}
153
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)
159	{
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);
164	}
165
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()
169	{
170	if (!is_resource($this->_resultid) \|\| get_resource_type($this->_resultid) !== 'pgsql result') return false;
171	return pg_cmdtuples($this->_resultid);
172	}
173
174
175	// returns true/false
176	function BeginTrans()
177	{
178	if ($this->transOff) return true;
179	$this->transCnt += 1;
180	return @pg_Exec($this->_connectionID, "begin ".$this->_transmode);
181	}
182
183	function RowLock($tables,$where,$col='1 as adodbignore')
184	{
185	if (!$this->transCnt) $this->BeginTrans();
186	return $this->GetOne("select $col from $tables where $where for update");
187	}
188
189	// returns true/false.
190	function CommitTrans($ok=true)
191	{
192	if ($this->transOff) return true;
193	if (!$ok) return $this->RollbackTrans();
194
195	$this->transCnt -= 1;
196	return @pg_Exec($this->_connectionID, "commit");
197	}
198
199	// returns true/false
200	function RollbackTrans()
201	{
202	if ($this->transOff) return true;
203	$this->transCnt -= 1;
204	return @pg_Exec($this->_connectionID, "rollback");
205	}
206
207	function MetaTables($ttype=false,$showSchema=false,$mask=false)
208	{
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') ";
214	}
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";
227	}
228	$ret = ADOConnection::MetaTables($ttype,$showSchema);
229
230	if ($mask) {
231	$this->metaTablesSQL = $save;
232	}
233	return $ret;
234	}
235
236
237	// if magic quotes disabled, use pg_escape_string()
238	function qstr($s,$magic_quotes=false)
239	{
240	if (is_bool($s)) return $s ? 'true' : 'false';
241
242	if (!$magic_quotes) {
243	if (ADODB_PHPVER >= 0x5200 && $this->_connectionID) {
244	return "'".pg_escape_string($this->_connectionID,$s)."'";
245	}
246	if (ADODB_PHPVER >= 0x4200) {
247	return "'".pg_escape_string($s)."'";
248	}
249	if ($this->replaceQuote[0] == '\\'){
250	$s = adodb_str_replace(array('\\',"\0"),array('\\\\',"\\\\000"),$s);
251	}
252	return "'".str_replace("'",$this->replaceQuote,$s)."'";
253	}
254
255	// undo magic quotes for "
256	$s = str_replace('\\"','"',$s);
257	return "'$s'";
258	}
259
260
261
262	// Format date column in sql string given an input format that understands Y M D
263	function SQLDate($fmt, $col=false)
264	{
265	if (!$col) $col = $this->sysTimeStamp;
266	$s = 'TO_CHAR('.$col.",'";
267
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;
280
281	case 'M':
282	$s .= 'Mon';
283	break;
284
285	case 'm':
286	$s .= 'MM';
287	break;
288	case 'D':
289	case 'd':
290	$s .= 'DD';
291	break;
292
293	case 'H':
294	$s.= 'HH24';
295	break;
296
297	case 'h':
298	$s .= 'HH';
299	break;
300
301	case 'i':
302	$s .= 'MI';
303	break;
304
305	case 's':
306	$s .= 'SS';
307	break;
308
309	case 'a':
310	case 'A':
311	$s .= 'AM';
312	break;
313
314	case 'w':
315	$s .= 'D';
316	break;
317
318	case 'l':
319	$s .= 'DAY';
320	break;
321
322	case 'W':
323	$s .= 'WW';
324	break;
325
326	default:
327	// handle escape characters...
328	if ($ch == '\\') {
329	$i++;
330	$ch = substr($fmt,$i,1);
331	}
332	if (strpos('-/.:;, ',$ch) !== false) $s .= $ch;
333	else $s .= '"'.$ch.'"';
334
335	}
336	}
337	return $s. "')";
338	}
339
340
341
342	/*
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
346	*
347	* contributed by Mattia Rossi mattia@technologist.com
348	* modified for safe mode by juraj chlebec
349	*/
350	function UpdateBlobFile($table,$column,$path,$where,$blobtype='BLOB')
351	{
352	pg_exec ($this->_connectionID, "begin");
353
354	$fd = fopen($path,'r');
355	$contents = fread($fd,filesize($path));
356	fclose($fd);
357
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);
362
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;
368	}
369
370	/*
371	* Deletes/Unlinks a Blob from the database, otherwise it
372	* will be left behind
373	*
374	* Returns TRUE on success or FALSE on failure.
375	*
376	* contributed by Todd Rogers todd#windfox.net
377	*/
378	function BlobDelete( $blob )
379	{
380	pg_exec ($this->_connectionID, "begin");
381	$result = @pg_lo_unlink($blob);
382	pg_exec ($this->_connectionID, "commit");
383	return( $result );
384	}
385
386	/*
387	Hueristic - not guaranteed to work.
388	*/
389	function GuessOID($oid)
390	{
391	if (strlen($oid)>16) return false;
392	return is_numeric($oid);
393	}
394
395	/*
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.
399	*
400	* contributed by Mattia Rossi mattia@technologist.com
401	*
402	* see http://www.postgresql.org/idocs/index.php?largeobjects.html
403	*
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.
406	*/
407	function BlobDecode($blob,$maxsize=false,$hastrans=true)
408	{
409	if (!$this->GuessOID($blob)) return $blob;
410
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;
416	}
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;
422	}
423
424	/*
425	See http://www.postgresql.org/idocs/index.php?datatype-binary.html
426
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.
430	*/
431	function BlobEncode($blob)
432	{
433	if (ADODB_PHPVER >= 0x5200) return pg_escape_bytea($this->_connectionID, $blob);
434	if (ADODB_PHPVER >= 0x4200) return pg_escape_bytea($blob);
435
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);
440
441	// note that there is a pg_escape_bytea function only for php 4.2.0 or later
442	}
443
444	// assumes bytea for blob, and varchar for clob
445	function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
446	{
447	if ($blobtype == 'CLOB') {
448	return $this->Execute("UPDATE $table SET $column=" . $this->qstr($val) . " WHERE $where");
449	}
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");
452	}
453
454	function OffsetDate($dayFraction,$date=false)
455	{
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;
461	}
462
463
464	return "($date+interval'".($dayFraction * 1440)." minutes')";
465	#return "($date+interval'$dayFraction days')";
466	}
467
468
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)
473	{
474	global $ADODB_FETCH_MODE;
475
476	$schema = false;
477	$false = false;
478	$this->_findschema($table,$schema);
479
480	if ($normalize) $table = strtolower($table);
481
482	$save = $ADODB_FETCH_MODE;
483	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
484	if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
485
486	if ($schema) $rs = $this->Execute(sprintf($this->metaColumnsSQL1,$table,$table,$schema));
487	else $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table,$table,$table));
488	if (isset($savem)) $this->SetFetchMode($savem);
489	$ADODB_FETCH_MODE = $save;
490
491	if ($rs === false) {
492	return $false;
493	}
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.
499
500	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
501
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;
507
508	$rskey->Close();
509	unset($rskey);
510	}
511
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;
519
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);
527	}
528
529	$rsdefa[$num] = $s;
530	$rsdef->MoveNext();
531	}
532	} else {
533	ADOConnection::outp( "==> SQL => " . $sql);
534	}
535	unset($rsdef);
536	}
537
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];
545
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;
551	}
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]];
557	}
558
559	//Freek
560	$fld->not_null = $rs->fields[4] == 't';
561
562
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?
570	}
571	}
572
573	if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;
574	else $retarr[($normalize) ? strtoupper($fld->name) : $fld->name] = $fld;
575
576	$rs->MoveNext();
577	}
578	$rs->Close();
579	if (empty($retarr))
580	return $false;
581	else
582	return $retarr;
583
584	}
585
586	function Param($name,$type='C')
587	{
588	$this->_pnum += 1;
589	return '$'.$this->_pnum;
590	}
591
592	function MetaIndexes ($table, $primary = FALSE, $owner = false)
593	{
594	global $ADODB_FETCH_MODE;
595
596	$schema = false;
597	$this->_findschema($table,$schema);
598
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\'))';
614	}
615
616	if ($primary == FALSE) {
617	$sql .= ' AND i.indisprimary=false;';
618	}
619
620	$save = $ADODB_FETCH_MODE;
621	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
622	if ($this->fetchMode !== FALSE) {
623	$savem = $this->SetFetchMode(FALSE);
624	}
625
626	$rs = $this->Execute(sprintf($sql,$table,$table,$schema));
627	if (isset($savem)) {
628	$this->SetFetchMode($savem);
629	}
630	$ADODB_FETCH_MODE = $save;
631
632	if (!is_object($rs)) {
633	$false = false;
634	return $false;
635	}
636
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];
645	}
646
647	$indexes[$row[0]] = array(
648	'unique' => ($row[1] == 't'),
649	'columns' => $columns
650	);
651	}
652	return $indexes;
653	}
654
655	// returns true or false
656	//
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)
661	{
662
663	if (!function_exists('pg_connect')) return null;
664
665	$this->_errorMsg = false;
666
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;
678	}
679	if ($user) $str .= " user=".$user;
680	if ($pwd) $str .= " password=".$pwd;
681	if ($db) $str .= " dbname=".$db;
682	}
683
684	//if ($user) $linea = "user=$user host=$linea password=$pwd dbname=$db port=5432";
685
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;
691
692	if (empty($ncnt)) $ncnt = 1;
693	else $ncnt += 1;
694
695	$str .= str_repeat(' ',$ncnt);
696	}
697	$this->_connectionID = pg_connect($str);
698	}
699	if ($this->_connectionID === false) return false;
700	$this->Execute("set datestyle='ISO'");
701
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;
706	}
707	return true;
708	}
709
710	function _nconnect($argHostname, $argUsername, $argPassword, $argDatabaseName)
711	{
712	return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabaseName,-1);
713	}
714
715	// returns true or false
716	//
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='')
721	{
722	return $this->_connect($str,$user,$pwd,$db,1);
723	}
724
725
726	// returns queryID or false
727	function _query($sql,$inputarr=false)
728	{
729	$this->_pnum = 0;
730	$this->_errorMsg = false;
731	if ($inputarr) {
732	/*
733	It appears that PREPARE/EXECUTE is slower for many queries.
734
735	For query executed 1000 times:
736	"select id,firstname,lastname from adoxyz
737	where firstname not like ? and lastname not like ? and id = ?"
738
739	with plan = 1.51861286163 secs
740	no plan = 1.26903700829 secs
741
742
743
744	*/
745	$plan = 'P'.md5($sql);
746
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;
754	}
755	}
756
757	if ($execp) $exsql = "EXECUTE $plan ($execp)";
758	else $exsql = "EXECUTE $plan";
759
760
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";
773	}
774	}
775	$sqlarr = explode('?',$sql);
776	//print_r($sqlarr);
777	$sql = '';
778	$i = 1;
779	foreach($sqlarr as $v) {
780	$sql .= $v.' $'.$i;
781	$i++;
782	}
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();
787	}
788	if ($rez)
789	$rez = pg_exec($this->_connectionID,$exsql);
790	} else {
791	//adodb_backtrace();
792	$rez = pg_exec($this->_connectionID,$sql);
793	}
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);
798	}
799	$this->_resultid = $rez;
800	return true;
801	}
802
803	return $rez;
804	}
805
806	function _errconnect()
807	{
808	if (defined('DB_ERROR_CONNECT_FAILED')) return DB_ERROR_CONNECT_FAILED;
809	else return 'Database connection failed';
810	}
811
812	/* Returns: the last error message from previous database operation */
813	function ErrorMsg()
814	{
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;
820	}
821
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);
828	}
829	return $this->_errorMsg;
830	}
831
832	function ErrorNo()
833	{
834	$e = $this->ErrorMsg();
835	if (strlen($e)) {
836	return ADOConnection::MetaError($e);
837	}
838	return 0;
839	}
840
841	// returns true or false
842	function _close()
843	{
844	if ($this->transCnt) $this->RollbackTrans();
845	if ($this->_resultid) {
846	@pg_freeresult($this->_resultid);
847	$this->_resultid = false;
848	}
849	@pg_close($this->_connectionID);
850	$this->_connectionID = false;
851	return true;
852	}
853
854
855	/*
856	* Maximum size of C field
857	*/
858	function CharMax()
859	{
860	return 1000000000; // should be 1 Gb?
861	}
862
863	/*
864	* Maximum size of X field
865	*/
866	function TextMax()
867	{
868	return 1000000000; // should be 1 Gb?
869	}
870
871
872	}
873
874	/*--------------------------------------------------------------------------------------
875	Class Name: Recordset
876	--------------------------------------------------------------------------------------*/
877
878	class ADORecordSet_postgres64 extends ADORecordSet{
879	var $_blobArr;
880	var $databaseType = "postgres64";
881	var $canSeek = true;
882	function ADORecordSet_postgres64($queryID,$mode=false)
883	{
884	if ($mode === false) {
885	global $ADODB_FETCH_MODE;
886	$mode = $ADODB_FETCH_MODE;
887	}
888	switch ($mode)
889	{
890	case ADODB_FETCH_NUM: $this->fetchMode = PGSQL_NUM; break;
891	case ADODB_FETCH_ASSOC:$this->fetchMode = PGSQL_ASSOC; break;
892
893	case ADODB_FETCH_DEFAULT:
894	case ADODB_FETCH_BOTH:
895	default: $this->fetchMode = PGSQL_BOTH; break;
896	}
897	$this->adodbFetchMode = $mode;
898	$this->ADORecordSet($queryID);
899	}
900
901	function GetRowAssoc($upper=true)
902	{
903	if ($this->fetchMode == PGSQL_ASSOC && !$upper) return $this->fields;
904	$row = ADORecordSet::GetRowAssoc($upper);
905	return $row;
906	}
907
908
909	function _initrs()
910	{
911	global $ADODB_COUNTRECS;
912	$qid = $this->_queryID;
913	$this->_numOfRows = ($ADODB_COUNTRECS)? @pg_numrows($qid):-1;
914	$this->_numOfFields = @pg_numfields($qid);
915
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);
922	}
923	}
924	}
925
926	/* Use associative array to get fields array */
927	function Fields($colname)
928	{
929	if ($this->fetchMode != PGSQL_NUM) return @$this->fields[$colname];
930
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;
936	}
937	}
938	return $this->fields[$this->bind[strtoupper($colname)]];
939	}
940
941	function FetchField($off = 0)
942	{
943	// offsets begin at 0
944
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;
950	}
951
952	function _seek($row)
953	{
954	return @pg_fetch_row($this->_queryID,$row);
955	}
956
957	function _decode($blob)
958	{
959	if ($blob === NULL) return NULL;
960	// eval('$realblob="'.adodb_str_replace(array('"','$'),array('\"','\$'),$blob).'";');
961	return pg_unescape_bytea($blob);
962	}
963
964	function _fixblobs()
965	{
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]);
969	}
970	}
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]);
974	}
975	}
976	}
977
978	// 10% speedup to move MoveNext to child class
979	function MoveNext()
980	{
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;
988	}
989	}
990	$this->fields = false;
991	$this->EOF = true;
992	}
993	return false;
994	}
995
996	function _fetch()
997	{
998
999	if ($this->_currentRow >= $this->_numOfRows && $this->_numOfRows >= 0)
1000	return false;
1001
1002	$this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
1003
1004	if ($this->fields && isset($this->_blobArr)) $this->_fixblobs();
1005
1006	return (is_array($this->fields));
1007	}
1008
1009	function _close()
1010	{
1011	return @pg_freeresult($this->_queryID);
1012	}
1013
1014	function MetaType($t,$len=-1,$fieldobj=false)
1015	{
1016	if (is_object($t)) {
1017	$fieldobj = $t;
1018	$t = $fieldobj->type;
1019	$len = $fieldobj->max_length;
1020	}
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';
1033
1034	case 'TEXT':
1035	return 'X';
1036
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';
1043
1044	case 'BOOL':
1045	case 'BOOLEAN':
1046	return 'L';
1047
1048	case 'DATE':
1049	return 'D';
1050
1051
1052	case 'TIMESTAMP WITHOUT TIME ZONE':
1053	case 'TIME':
1054	case 'DATETIME':
1055	case 'TIMESTAMP':
1056	case 'TIMESTAMPTZ':
1057	return 'T';
1058
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';
1067
1068	case 'OID':
1069	case 'SERIAL':
1070	return 'R';
1071
1072	default:
1073	return 'N';
1074	}
1075	}
1076
1077	}
1078	?>

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

Context Navigation

source: sandbox/2.5.1-evolucao/phpgwapi/inc/adodb/drivers/adodb-postgres64.inc.php @ 8222

Download in other formats: