Context Navigation

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

Revision 2, 27.6 KB checked in by niltonneto, 17 years ago (diff)
Removida todas as tags usadas pelo CVS ($Id, $Source). Primeira versão no CVS externo.
Property svn:eol-style set to `native` Property svn:executable set to ``*

Line
1	<?php
2	/*
3	V4.51 29 July 2004 (c) 2000-2004 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 = 'r' 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	var $metaColumnsSQL1 = "SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
80	FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n
81	WHERE relkind = 'r' AND (c.relname='%s' or c.relname = lower('%s'))
82	and c.relnamespace=n.oid and n.nspname='%s'
83	and a.attname not like '....%%' AND a.attnum > 0
84	AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
85
86	// get primary key etc -- from Freek Dijkstra
87	var $metaKeySQL = "SELECT ic.relname AS index_name, a.attname AS column_name,i.indisunique AS unique_key, i.indisprimary AS primary_key
88	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'";
89
90	var $hasAffectedRows = true;
91	var $hasLimit = false; // set to true for pgsql 7 only. support pgsql/mysql SELECT * FROM TABLE LIMIT 10
92	// below suggested by Freek Dijkstra
93	var $true = 't'; // string that represents TRUE for a database
94	var $false = 'f'; // string that represents FALSE for a database
95	var $fmtDate = "'Y-m-d'"; // used by DBDate() as the default date format used by the database
96	var $fmtTimeStamp = "'Y-m-d G:i:s'"; // used by DBTimeStamp as the default timestamp fmt.
97	var $hasMoveFirst = true;
98	var $hasGenID = true;
99	var $_genIDSQL = "SELECT NEXTVAL('%s')";
100	var $_genSeqSQL = "CREATE SEQUENCE %s START %s";
101	var $_dropSeqSQL = "DROP SEQUENCE %s";
102	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";
103	var $random = 'random()'; /// random function
104	var $autoRollback = true; // apparently pgsql does not autorollback properly before 4.3.4
105	// http://bugs.php.net/bug.php?id=25404
106
107	var $_bindInputArray = false; // requires postgresql 7.3+ and ability to modify database
108
109	// The last (fmtTimeStamp is not entirely correct:
110	// PostgreSQL also has support for time zones,
111	// and writes these time in this format: "2001-03-01 18:59:26+02".
112	// There is no code for the "+02" time zone information, so I just left that out.
113	// I'm not familiar enough with both ADODB as well as Postgres
114	// to know what the concequences are. The other values are correct (wheren't in 0.94)
115	// -- Freek Dijkstra
116
117	function ADODB_postgres64()
118	{
119	// changes the metaColumnsSQL, adds columns: attnum[6]
120	}
121
122	function ServerInfo()
123	{
124	if (isset($this->version)) return $this->version;
125
126	$arr['description'] = $this->GetOne("select version()");
127	$arr['version'] = ADOConnection::_findvers($arr['description']);
128	$this->version = $arr;
129	return $arr;
130	}
131	/*
132	function IfNull( $field, $ifNull )
133	{
134	return " NULLIF($field, $ifNull) "; // if PGSQL
135	}
136	*/
137	// get the last id - never tested
138	function pg_insert_id($tablename,$fieldname)
139	{
140	$result=pg_exec($this->_connectionID, "SELECT last_value FROM ${tablename}_${fieldname}_seq");
141	if ($result) {
142	$arr = @pg_fetch_row($result,0);
143	pg_freeresult($result);
144	if (isset($arr[0])) return $arr[0];
145	}
146	return false;
147	}
148
149	/* Warning from http://www.php.net/manual/function.pg-getlastoid.php:
150	Using a OID as a unique identifier is not generally wise.
151	Unless you are very careful, you might end up with a tuple having
152	a different OID if a database must be reloaded. */
153	function _insertid()
154	{
155	if (!is_resource($this->_resultid) \|\| get_resource_type($this->_resultid) !== 'pgsql result') return false;
156	return pg_getlastoid($this->_resultid);
157	}
158
159	// I get this error with PHP before 4.0.6 - jlim
160	// Warning: This compilation does not support pg_cmdtuples() in d:/inetpub/wwwroot/php/adodb/adodb-postgres.inc.php on line 44
161	function _affectedrows()
162	{
163	if (!is_resource($this->_resultid) \|\| get_resource_type($this->_resultid) !== 'pgsql result') return false;
164	return pg_cmdtuples($this->_resultid);
165	}
166
167
168	// returns true/false
169	function BeginTrans()
170	{
171	if ($this->transOff) return true;
172	$this->transCnt += 1;
173	return @pg_Exec($this->_connectionID, "begin");
174	}
175
176	function RowLock($tables,$where)
177	{
178	if (!$this->transCnt) $this->BeginTrans();
179	return $this->GetOne("select 1 as ignore from $tables where $where for update");
180	}
181
182	// returns true/false.
183	function CommitTrans($ok=true)
184	{
185	if ($this->transOff) return true;
186	if (!$ok) return $this->RollbackTrans();
187
188	$this->transCnt -= 1;
189	return @pg_Exec($this->_connectionID, "commit");
190	}
191
192	// returns true/false
193	function RollbackTrans()
194	{
195	if ($this->transOff) return true;
196	$this->transCnt -= 1;
197	return @pg_Exec($this->_connectionID, "rollback");
198	}
199
200	function &MetaTables($ttype=false,$showSchema=false,$mask=false)
201	{
202	if ($mask) {
203	$save = $this->metaTablesSQL;
204	$mask = $this->qstr(strtolower($mask));
205	$this->metaTablesSQL = "
206	select tablename,'T' from pg_tables where tablename like $mask union
207	select viewname,'V' from pg_views where viewname like $mask";
208	}
209	$ret =& ADOConnection::MetaTables($ttype,$showSchema);
210
211	if ($mask) {
212	$this->metaTablesSQL = $save;
213	}
214	return $ret;
215	}
216
217	/*
218	// if magic quotes disabled, use pg_escape_string()
219	function qstr($s,$magic_quotes=false)
220	{
221	if (!$magic_quotes) {
222	if (ADODB_PHPVER >= 0x4200) {
223	return "'".pg_escape_string($s)."'";
224	}
225	if ($this->replaceQuote[0] == '\\'){
226	$s = adodb_str_replace(array('\\',"\0"),array('\\\\',"\\\0"),$s);
227	}
228	return "'".str_replace("'",$this->replaceQuote,$s)."'";
229	}
230
231	// undo magic quotes for "
232	$s = str_replace('\\"','"',$s);
233	return "'$s'";
234	}
235	*/
236
237
238	// Format date column in sql string given an input format that understands Y M D
239	function SQLDate($fmt, $col=false)
240	{
241	if (!$col) $col = $this->sysTimeStamp;
242	$s = 'TO_CHAR('.$col.",'";
243
244	$len = strlen($fmt);
245	for ($i=0; $i < $len; $i++) {
246	$ch = $fmt[$i];
247	switch($ch) {
248	case 'Y':
249	case 'y':
250	$s .= 'YYYY';
251	break;
252	case 'Q':
253	case 'q':
254	$s .= 'Q';
255	break;
256
257	case 'M':
258	$s .= 'Mon';
259	break;
260
261	case 'm':
262	$s .= 'MM';
263	break;
264	case 'D':
265	case 'd':
266	$s .= 'DD';
267	break;
268
269	case 'H':
270	$s.= 'HH24';
271	break;
272
273	case 'h':
274	$s .= 'HH';
275	break;
276
277	case 'i':
278	$s .= 'MI';
279	break;
280
281	case 's':
282	$s .= 'SS';
283	break;
284
285	case 'a':
286	case 'A':
287	$s .= 'AM';
288	break;
289
290	default:
291	// handle escape characters...
292	if ($ch == '\\') {
293	$i++;
294	$ch = substr($fmt,$i,1);
295	}
296	if (strpos('-/.:;, ',$ch) !== false) $s .= $ch;
297	else $s .= '"'.$ch.'"';
298
299	}
300	}
301	return $s. "')";
302	}
303
304
305
306	/*
307	* Load a Large Object from a file
308	* - the procedure stores the object id in the table and imports the object using
309	* postgres proprietary blob handling routines
310	*
311	* contributed by Mattia Rossi mattia@technologist.com
312	* modified for safe mode by juraj chlebec
313	*/
314	function UpdateBlobFile($table,$column,$path,$where,$blobtype='BLOB')
315	{
316	pg_exec ($this->_connectionID, "begin");
317
318	$fd = fopen($path,'r');
319	$contents = fread($fd,filesize($path));
320	fclose($fd);
321
322	$oid = pg_lo_create($this->_connectionID);
323	$handle = pg_lo_open($this->_connectionID, $oid, 'w');
324	pg_lo_write($handle, $contents);
325	pg_lo_close($handle);
326
327	// $oid = pg_lo_import ($path);
328	pg_exec($this->_connectionID, "commit");
329	$rs = ADOConnection::UpdateBlob($table,$column,$oid,$where,$blobtype);
330	$rez = !empty($rs);
331	return $rez;
332	}
333
334	/*
335	* If an OID is detected, then we use pg_lo_* to open the oid file and read the
336	* real blob from the db using the oid supplied as a parameter. If you are storing
337	* blobs using bytea, we autodetect and process it so this function is not needed.
338	*
339	* contributed by Mattia Rossi mattia@technologist.com
340	*
341	* see http://www.postgresql.org/idocs/index.php?largeobjects.html
342	*/
343	function BlobDecode( $blob)
344	{
345	if (strlen($blob) > 24) return $blob;
346
347	@pg_exec($this->_connectionID,"begin");
348	$fd = @pg_lo_open($this->_connectionID,$blob,"r");
349	if ($fd === false) {
350	@pg_exec($this->_connectionID,"commit");
351	return $blob;
352	}
353	$realblob = @pg_loreadall($fd);
354	@pg_loclose($fd);
355	@pg_exec($this->_connectionID,"commit");
356	return $realblob;
357	}
358
359	/*
360	See http://www.postgresql.org/idocs/index.php?datatype-binary.html
361
362	NOTE: SQL string literals (input strings) must be preceded with two backslashes
363	due to the fact that they must pass through two parsers in the PostgreSQL
364	backend.
365	*/
366	function BlobEncode($blob)
367	{
368	if (ADODB_PHPVER >= 0x4200 && ADODB_PHPVER < 0x5000) return pg_escape_bytea($blob);
369
370	/92=backslash, 0=null, 39=single-quote/
371	$badch = array(chr(92),chr(0),chr(39)); # \ null '
372	$fixch = array('\\\\134','\\\\000','\\\\047');
373	return adodb_str_replace($badch,$fixch,$blob);
374
375	// note that there is a pg_escape_bytea function only for php 4.2.0 or later
376	}
377
378	function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
379	{
380	// do not use bind params which uses qstr(), as blobencode() already quotes data
381	return $this->Execute("UPDATE $table SET $column='".$this->BlobEncode($val)."'::bytea WHERE $where");
382	}
383
384	function OffsetDate($dayFraction,$date=false)
385	{
386	if (!$date) $date = $this->sysDate;
387	return "($date+interval'$dayFraction days')";
388	}
389
390
391	// for schema support, pass in the $table param "$schema.$tabname".
392	// converts field names to lowercase, $upper is ignored
393	function &MetaColumns($table,$normalize=true)
394	{
395	global $ADODB_FETCH_MODE;
396
397	$schema = false;
398	$this->_findschema($table,$schema);
399
400	if ($normalize) $table = strtolower($table);
401
402	$save = $ADODB_FETCH_MODE;
403	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
404	if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
405
406	if ($schema) $rs =& $this->Execute(sprintf($this->metaColumnsSQL1,$table,$table,$schema));
407	else $rs =& $this->Execute(sprintf($this->metaColumnsSQL,$table,$table));
408	if (isset($savem)) $this->SetFetchMode($savem);
409	$ADODB_FETCH_MODE = $save;
410
411	if ($rs === false) return false;
412
413	if (!empty($this->metaKeySQL)) {
414	// If we want the primary keys, we have to issue a separate query
415	// Of course, a modified version of the metaColumnsSQL query using a
416	// LEFT JOIN would have been much more elegant, but postgres does
417	// not support OUTER JOINS. So here is the clumsy way.
418
419	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
420
421	$rskey = $this->Execute(sprintf($this->metaKeySQL,($table)));
422	// fetch all result in once for performance.
423	$keys =& $rskey->GetArray();
424	if (isset($savem)) $this->SetFetchMode($savem);
425	$ADODB_FETCH_MODE = $save;
426
427	$rskey->Close();
428	unset($rskey);
429	}
430
431	$rsdefa = array();
432	if (!empty($this->metaDefaultsSQL)) {
433	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
434	$sql = sprintf($this->metaDefaultsSQL, ($table));
435	$rsdef = $this->Execute($sql);
436	if (isset($savem)) $this->SetFetchMode($savem);
437	$ADODB_FETCH_MODE = $save;
438
439	if ($rsdef) {
440	while (!$rsdef->EOF) {
441	$num = $rsdef->fields['num'];
442	$s = $rsdef->fields['def'];
443	if (strpos($s,'::')===false && substr($s, 0, 1) == "'") { /* quoted strings hack... for now... fixme */
444	$s = substr($s, 1);
445	$s = substr($s, 0, strlen($s) - 1);
446	}
447
448	$rsdefa[$num] = $s;
449	$rsdef->MoveNext();
450	}
451	} else {
452	ADOConnection::outp( "==> SQL => " . $sql);
453	}
454	unset($rsdef);
455	}
456
457	$retarr = array();
458	while (!$rs->EOF) {
459	$fld = new ADOFieldObject();
460	$fld->name = $rs->fields[0];
461	$fld->type = $rs->fields[1];
462	$fld->max_length = $rs->fields[2];
463	if ($fld->max_length <= 0) $fld->max_length = $rs->fields[3]-4;
464	if ($fld->max_length <= 0) $fld->max_length = -1;
465
466	// dannym
467	// 5 hasdefault; 6 num-of-column
468	$fld->has_default = ($rs->fields[5] == 't');
469	if ($fld->has_default) {
470	$fld->default_value = $rsdefa[$rs->fields[6]];
471	}
472
473	//Freek
474	if ($rs->fields[4] == $this->true) {
475	$fld->not_null = true;
476	}
477
478	// Freek
479	if (is_array($keys)) {
480	foreach($keys as $key) {
481	if ($fld->name == $key['column_name'] AND $key['primary_key'] == $this->true)
482	$fld->primary_key = true;
483	if ($fld->name == $key['column_name'] AND $key['unique_key'] == $this->true)
484	$fld->unique = true; // What name is more compatible?
485	}
486	}
487
488	if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;
489	else $retarr[($normalize) ? strtoupper($fld->name) : $fld->name] = $fld;
490
491	$rs->MoveNext();
492	}
493	$rs->Close();
494	return $retarr;
495
496	}
497
498	function &MetaIndexes ($table, $primary = FALSE)
499	{
500	global $ADODB_FETCH_MODE;
501
502	$schema = false;
503	$this->_findschema($table,$schema);
504
505	if ($schema) { // requires pgsql 7.3+ - pg_namespace used.
506	$sql = '
507	SELECT c.relname as "Name", i.indisunique as "Unique", i.indkey as "Columns"
508	FROM pg_catalog.pg_class c
509	JOIN pg_catalog.pg_index i ON i.indexrelid=c.oid
510	JOIN pg_catalog.pg_class c2 ON c2.oid=i.indrelid
511	,pg_namespace n
512	WHERE (c2.relname=\'%s\' or c2.relname=lower(\'%s\')) and c.relnamespace=c2.relnamespace and c.relnamespace=n.oid and n.nspname=\'%s\' AND i.indisprimary=false';
513	} else {
514	$sql = '
515	SELECT c.relname as "Name", i.indisunique as "Unique", i.indkey as "Columns"
516	FROM pg_catalog.pg_class c
517	JOIN pg_catalog.pg_index i ON i.indexrelid=c.oid
518	JOIN pg_catalog.pg_class c2 ON c2.oid=i.indrelid
519	WHERE c2.relname=\'%s\' or c2.relname=lower(\'%s\')';
520	}
521
522	if ($primary == FALSE) {
523	$sql .= ' AND i.indisprimary=false;';
524	}
525
526	$save = $ADODB_FETCH_MODE;
527	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
528	if ($this->fetchMode !== FALSE) {
529	$savem = $this->SetFetchMode(FALSE);
530	}
531
532	$rs = $this->Execute(sprintf($sql,$table,$table,$schema));
533	if (isset($savem)) {
534	$this->SetFetchMode($savem);
535	}
536	$ADODB_FETCH_MODE = $save;
537
538	if (!is_object($rs)) {
539	return FALSE;
540	}
541
542	$col_names = $this->MetaColumnNames($table,true);
543	$indexes = array();
544	while ($row = $rs->FetchRow()) {
545	$columns = array();
546	foreach (explode(' ', $row[2]) as $col) {
547	$columns[] = $col_names[$col - 1];
548	}
549
550	$indexes[$row[0]] = array(
551	'unique' => ($row[1] == 't'),
552	'columns' => $columns
553	);
554	}
555	return $indexes;
556	}
557
558	// returns true or false
559	//
560	// examples:
561	// $db->Connect("host=host1 user=user1 password=secret port=4341");
562	// $db->Connect('host1','user1','secret');
563	function _connect($str,$user='',$pwd='',$db='',$ctype=0)
564	{
565
566	if (!function_exists('pg_pconnect')) return null;
567
568	$this->_errorMsg = false;
569
570	if ($user \|\| $pwd \|\| $db) {
571	$user = adodb_addslashes($user);
572	$pwd = adodb_addslashes($pwd);
573	if (strlen($db) == 0) $db = 'template1';
574	$db = adodb_addslashes($db);
575	if ($str) {
576	$host = split(":", $str);
577	if ($host[0]) $str = "host=".adodb_addslashes($host[0]);
578	else $str = 'host=localhost';
579	if (isset($host[1])) $str .= " port=$host[1]";
580	}
581	if ($user) $str .= " user=".$user;
582	if ($pwd) $str .= " password=".$pwd;
583	if ($db) $str .= " dbname=".$db;
584	}
585
586	//if ($user) $linea = "user=$user host=$linea password=$pwd dbname=$db port=5432";
587
588	if ($ctype === 1) { // persistent
589	$this->_connectionID = pg_pconnect($str);
590	} else {
591	if ($ctype === -1) { // nconnect, we trick pgsql ext by changing the connection str
592	static $ncnt;
593
594	if (empty($ncnt)) $ncnt = 1;
595	else $ncnt += 1;
596
597	$str .= str_repeat(' ',$ncnt);
598	}
599	$this->_connectionID = pg_connect($str);
600	}
601	if ($this->_connectionID === false) return false;
602	$this->Execute("set datestyle='ISO'");
603	return true;
604	}
605
606	function _nconnect($argHostname, $argUsername, $argPassword, $argDatabaseName)
607	{
608	return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabaseName,-1);
609	}
610
611	// returns true or false
612	//
613	// examples:
614	// $db->PConnect("host=host1 user=user1 password=secret port=4341");
615	// $db->PConnect('host1','user1','secret');
616	function _pconnect($str,$user='',$pwd='',$db='')
617	{
618	return $this->_connect($str,$user,$pwd,$db,1);
619	}
620
621
622	// returns queryID or false
623	function _query($sql,$inputarr)
624	{
625
626	if ($inputarr) {
627	/*
628	It appears that PREPARE/EXECUTE is slower for many queries.
629
630	For query executed 1000 times:
631	"select id,firstname,lastname from adoxyz
632	where firstname not like ? and lastname not like ? and id = ?"
633
634	with plan = 1.51861286163 secs
635	no plan = 1.26903700829 secs
636
637
638
639	*/
640	$plan = 'P'.md5($sql);
641
642	$execp = '';
643	foreach($inputarr as $v) {
644	if ($execp) $execp .= ',';
645	if (is_string($v)) {
646	if (strncmp($v,"'",1) !== 0) $execp .= $this->qstr($v);
647	} else {
648	$execp .= $v;
649	}
650	}
651
652	if ($execp) $exsql = "EXECUTE $plan ($execp)";
653	else $exsql = "EXECUTE $plan";
654
655	$rez = @pg_exec($this->_connectionID,$exsql);
656	if (!$rez) {
657	# Perhaps plan does not exist? Prepare/compile plan.
658	$params = '';
659	foreach($inputarr as $v) {
660	if ($params) $params .= ',';
661	if (is_string($v)) {
662	$params .= 'VARCHAR';
663	} else if (is_integer($v)) {
664	$params .= 'INTEGER';
665	} else {
666	$params .= "REAL";
667	}
668	}
669	$sqlarr = explode('?',$sql);
670	//print_r($sqlarr);
671	$sql = '';
672	$i = 1;
673	foreach($sqlarr as $v) {
674	$sql .= $v.' $'.$i;
675	$i++;
676	}
677	$s = "PREPARE $plan ($params) AS ".substr($sql,0,strlen($sql)-2);
678	//adodb_pr($s);
679	pg_exec($this->_connectionID,$s);
680	echo $this->ErrorMsg();
681	}
682
683	$rez = pg_exec($this->_connectionID,$exsql);
684	} else {
685	$this->_errorMsg = false;
686	//adodb_backtrace();
687	$rez = pg_exec($this->_connectionID,$sql);
688	}
689	// check if no data returned, then no need to create real recordset
690	if ($rez && pg_numfields($rez) <= 0) {
691	if (is_resource($this->_resultid) && get_resource_type($this->_resultid) === 'pgsql result') {
692	pg_freeresult($this->_resultid);
693	}
694	$this->_resultid = $rez;
695	return true;
696	}
697
698	return $rez;
699	}
700
701
702	/* Returns: the last error message from previous database operation */
703	function ErrorMsg()
704	{
705	if ($this->_errorMsg !== false) return $this->_errorMsg;
706	if (ADODB_PHPVER >= 0x4300) {
707	if (!empty($this->_resultid)) {
708	$this->_errorMsg = @pg_result_error($this->_resultid);
709	if ($this->_errorMsg) return $this->_errorMsg;
710	}
711
712	if (!empty($this->_connectionID)) {
713	$this->_errorMsg = @pg_last_error($this->_connectionID);
714	} else $this->_errorMsg = @pg_last_error();
715	} else {
716	if (empty($this->_connectionID)) $this->_errorMsg = @pg_errormessage();
717	else $this->_errorMsg = @pg_errormessage($this->_connectionID);
718	}
719	return $this->_errorMsg;
720	}
721
722	function ErrorNo()
723	{
724	$e = $this->ErrorMsg();
725	if (strlen($e)) {
726	return ADOConnection::MetaError($e);
727	}
728	return 0;
729	}
730
731	// returns true or false
732	function _close()
733	{
734	if ($this->transCnt) $this->RollbackTrans();
735	if ($this->_resultid) {
736	@pg_freeresult($this->_resultid);
737	$this->_resultid = false;
738	}
739	@pg_close($this->_connectionID);
740	$this->_connectionID = false;
741	return true;
742	}
743
744
745	/*
746	* Maximum size of C field
747	*/
748	function CharMax()
749	{
750	return 1000000000; // should be 1 Gb?
751	}
752
753	/*
754	* Maximum size of X field
755	*/
756	function TextMax()
757	{
758	return 1000000000; // should be 1 Gb?
759	}
760
761
762	}
763
764	/*--------------------------------------------------------------------------------------
765	Class Name: Recordset
766	--------------------------------------------------------------------------------------*/
767
768	class ADORecordSet_postgres64 extends ADORecordSet{
769	var $_blobArr;
770	var $databaseType = "postgres64";
771	var $canSeek = true;
772	function ADORecordSet_postgres64($queryID,$mode=false)
773	{
774	if ($mode === false) {
775	global $ADODB_FETCH_MODE;
776	$mode = $ADODB_FETCH_MODE;
777	}
778	switch ($mode)
779	{
780	case ADODB_FETCH_NUM: $this->fetchMode = PGSQL_NUM; break;
781	case ADODB_FETCH_ASSOC:$this->fetchMode = PGSQL_ASSOC; break;
782	default:
783	case ADODB_FETCH_DEFAULT:
784	case ADODB_FETCH_BOTH:$this->fetchMode = PGSQL_BOTH; break;
785	}
786	$this->ADORecordSet($queryID);
787	}
788
789	function &GetRowAssoc($upper=true)
790	{
791	if ($this->fetchMode == PGSQL_ASSOC && !$upper) return $this->fields;
792	$row =& ADORecordSet::GetRowAssoc($upper);
793	return $row;
794	}
795
796	function _initrs()
797	{
798	global $ADODB_COUNTRECS;
799	$qid = $this->_queryID;
800	$this->_numOfRows = ($ADODB_COUNTRECS)? @pg_numrows($qid):-1;
801	$this->_numOfFields = @pg_numfields($qid);
802
803	// cache types for blob decode check
804	for ($i=0, $max = $this->_numOfFields; $i < $max; $i++) {
805	if (pg_fieldtype($qid,$i) == 'bytea') {
806	$this->_blobArr[$i] = pg_fieldname($qid,$i);
807	}
808	}
809	}
810
811	/* Use associative array to get fields array */
812	function Fields($colname)
813	{
814	if ($this->fetchMode != PGSQL_NUM) return @$this->fields[$colname];
815
816	if (!$this->bind) {
817	$this->bind = array();
818	for ($i=0; $i < $this->_numOfFields; $i++) {
819	$o = $this->FetchField($i);
820	$this->bind[strtoupper($o->name)] = $i;
821	}
822	}
823	return $this->fields[$this->bind[strtoupper($colname)]];
824	}
825
826	function &FetchField($off = 0)
827	{
828	// offsets begin at 0
829
830	$o= new ADOFieldObject();
831	$o->name = @pg_fieldname($this->_queryID,$off);
832	$o->type = @pg_fieldtype($this->_queryID,$off);
833	$o->max_length = @pg_fieldsize($this->_queryID,$off);
834	return $o;
835	}
836
837	function _seek($row)
838	{
839	return @pg_fetch_row($this->_queryID,$row);
840	}
841
842	function _decode($blob)
843	{
844	eval('$realblob="'.adodb_str_replace(array('"','$'),array('\"','\$'),$blob).'";');
845	return $realblob;
846	}
847
848	function _fixblobs()
849	{
850	if ($this->fetchMode == PGSQL_NUM \|\| $this->fetchMode == PGSQL_BOTH) {
851	foreach($this->_blobArr as $k => $v) {
852	$this->fields[$k] = ADORecordSet_postgres64::_decode($this->fields[$k]);
853	}
854	}
855	if ($this->fetchMode == PGSQL_ASSOC \|\| $this->fetchMode == PGSQL_BOTH) {
856	foreach($this->_blobArr as $k => $v) {
857	$this->fields[$v] = ADORecordSet_postgres64::_decode($this->fields[$v]);
858	}
859	}
860	}
861
862	// 10% speedup to move MoveNext to child class
863	function MoveNext()
864	{
865	if (!$this->EOF) {
866	$this->_currentRow++;
867	if ($this->_numOfRows < 0 \|\| $this->_numOfRows > $this->_currentRow) {
868	$this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
869	if (is_array($this->fields) && $this->fields) {
870	if (isset($this->_blobArr)) $this->_fixblobs();
871	return true;
872	}
873	}
874	$this->fields = false;
875	$this->EOF = true;
876	}
877	return false;
878	}
879
880	function _fetch()
881	{
882
883	if ($this->_currentRow >= $this->_numOfRows && $this->_numOfRows >= 0)
884	return false;
885
886	$this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
887
888	if ($this->fields && isset($this->_blobArr)) $this->_fixblobs();
889
890	return (is_array($this->fields));
891	}
892
893	function _close()
894	{
895	return @pg_freeresult($this->_queryID);
896	}
897
898	function MetaType($t,$len=-1,$fieldobj=false)
899	{
900	if (is_object($t)) {
901	$fieldobj = $t;
902	$t = $fieldobj->type;
903	$len = $fieldobj->max_length;
904	}
905	switch (strtoupper($t)) {
906	case 'MONEY': // stupid, postgres expects money to be a string
907	case 'INTERVAL':
908	case 'CHAR':
909	case 'CHARACTER':
910	case 'VARCHAR':
911	case 'NAME':
912	case 'BPCHAR':
913	case '_VARCHAR':
914	if ($len <= $this->blobSize) return 'C';
915
916	case 'TEXT':
917	return 'X';
918
919	case 'IMAGE': // user defined type
920	case 'BLOB': // user defined type
921	case 'BIT': // This is a bit string, not a single bit, so don't return 'L'
922	case 'VARBIT':
923	case 'BYTEA':
924	return 'B';
925
926	case 'BOOL':
927	case 'BOOLEAN':
928	return 'L';
929
930	case 'DATE':
931	return 'D';
932
933	case 'TIME':
934	case 'DATETIME':
935	case 'TIMESTAMP':
936	case 'TIMESTAMPTZ':
937	return 'T';
938
939	case 'SMALLINT':
940	case 'BIGINT':
941	case 'INTEGER':
942	case 'INT8':
943	case 'INT4':
944	case 'INT2':
945	if (isset($fieldobj) &&
946	empty($fieldobj->primary_key) && empty($fieldobj->unique)) return 'I';
947
948	case 'OID':
949	case 'SERIAL':
950	return 'R';
951
952	default:
953	return 'N';
954	}
955	}
956
957	}
958	?>

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

Download in other formats: