Context Navigation

source: Dev/trunk/rdfapi/util/adodb/drivers/adodb-postgres64.inc.php @ 12

Last change on this file since 12 was 12, checked in by basvannuland, 14 years ago
Added RAP RDF API Added RDF reader writer for save and load survey
File size: 32.1 KB

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

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

Download in other formats: