2de50364add7944faf1fb5f7c13034ad7bb3c46e
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / drivers / adodb-pdo_pgsql.inc.php
1 <?php
2
3 /*
4 V4.81 3 May 2006 (c) 2000-2006 John Lim (jlim#natsoft.com.my). All rights reserved.
5 Released under both BSD license and Lesser GPL library license.
6 Whenever there is any discrepancy between the two licenses,
7 the BSD license will take precedence.
8 Set tabs to 8.
9
10 */
11
12 class ADODB_pdo_pgsql extends ADODB_pdo {
13 var $metaDatabasesSQL = "select datname from pg_database where datname not in ('template0','template1') order by 1";
14 var $metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%'
15 and tablename not in ('sql_features', 'sql_implementation_info', 'sql_languages',
16 'sql_packages', 'sql_sizing', 'sql_sizing_profiles')
17 union
18 select viewname,'V' from pg_views where viewname not like 'pg\_%'";
19 //"select tablename from pg_tables where tablename not like 'pg_%' order by 1";
20 var $isoDates = true; // accepts dates in ISO format
21 var $sysDate = "CURRENT_DATE";
22 var $sysTimeStamp = "CURRENT_TIMESTAMP";
23 var $blobEncodeType = 'C';
24 var $metaColumnsSQL = "SELECT a.attname,t.typname,a.attlen,a.atttypmod,a.attnotnull,a.atthasdef,a.attnum
25 FROM pg_class c, pg_attribute a,pg_type t
26 WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s')) and a.attname not like '....%%'
27 AND a.attnum > 0 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
28
29 // used when schema defined
30 var $metaColumnsSQL1 = "SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
31 FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n
32 WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s'))
33 and c.relnamespace=n.oid and n.nspname='%s'
34 and a.attname not like '....%%' AND a.attnum > 0
35 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
36
37 // get primary key etc -- from Freek Dijkstra
38 var $metaKeySQL = "SELECT ic.relname AS index_name, a.attname AS column_name,i.indisunique AS unique_key, i.indisprimary AS primary_key
39 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'";
40
41 var $hasAffectedRows = true;
42 var $hasLimit = false; // set to true for pgsql 7 only. support pgsql/mysql SELECT * FROM TABLE LIMIT 10
43 // below suggested by Freek Dijkstra
44 var $true = 't'; // string that represents TRUE for a database
45 var $false = 'f'; // string that represents FALSE for a database
46 var $fmtDate = "'Y-m-d'"; // used by DBDate() as the default date format used by the database
47 var $fmtTimeStamp = "'Y-m-d G:i:s'"; // used by DBTimeStamp as the default timestamp fmt.
48 var $hasMoveFirst = true;
49 var $hasGenID = true;
50 var $_genIDSQL = "SELECT NEXTVAL('%s')";
51 var $_genSeqSQL = "CREATE SEQUENCE %s START %s";
52 var $_dropSeqSQL = "DROP SEQUENCE %s";
53 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";
54 var $random = 'random()'; /// random function
55 var $concat_operator='||';
56
57 function _init($parentDriver)
58 {
59
60 $parentDriver->hasTransactions = false; ## <<< BUG IN PDO pgsql driver
61 $parentDriver->hasInsertID = true;
62 }
63
64 function ServerInfo()
65 {
66 $arr['description'] = ADOConnection::GetOne("select version()");
67 $arr['version'] = ADOConnection::_findvers($arr['description']);
68 return $arr;
69 }
70
71 function &SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
72 {
73 $offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
74 $limitStr = ($nrows >= 0) ? " LIMIT $nrows" : '';
75 if ($secs2cache)
76 $rs =& $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
77 else
78 $rs =& $this->Execute($sql."$limitStr$offsetStr",$inputarr);
79
80 return $rs;
81 }
82
83 function &MetaTables($ttype=false,$showSchema=false,$mask=false)
84 {
85 $info = $this->ServerInfo();
86 if ($info['version'] >= 7.3) {
87 $this->metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%'
88 and schemaname not in ( 'pg_catalog','information_schema')
89 union
90 select viewname,'V' from pg_views where viewname not like 'pg\_%' and schemaname not in ( 'pg_catalog','information_schema') ";
91 }
92 if ($mask) {
93 $save = $this->metaTablesSQL;
94 $mask = $this->qstr(strtolower($mask));
95 if ($info['version']>=7.3)
96 $this->metaTablesSQL = "
97 select tablename,'T' from pg_tables where tablename like $mask and schemaname not in ( 'pg_catalog','information_schema')
98 union
99 select viewname,'V' from pg_views where viewname like $mask and schemaname not in ( 'pg_catalog','information_schema') ";
100 else
101 $this->metaTablesSQL = "
102 select tablename,'T' from pg_tables where tablename like $mask
103 union
104 select viewname,'V' from pg_views where viewname like $mask";
105 }
106 $ret =& ADOConnection::MetaTables($ttype,$showSchema);
107
108 if ($mask) {
109 $this->metaTablesSQL = $save;
110 }
111 return $ret;
112 }
113
114 function &MetaColumns($table,$normalize=true)
115 {
116 global $ADODB_FETCH_MODE;
117
118 $schema = false;
119 $this->_findschema($table,$schema);
120
121 if ($normalize) $table = strtolower($table);
122
123 $save = $ADODB_FETCH_MODE;
124 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
125 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
126
127 if ($schema) $rs =& $this->Execute(sprintf($this->metaColumnsSQL1,$table,$table,$schema));
128 else $rs =& $this->Execute(sprintf($this->metaColumnsSQL,$table,$table));
129 if (isset($savem)) $this->SetFetchMode($savem);
130 $ADODB_FETCH_MODE = $save;
131
132 if ($rs === false) {
133 $false = false;
134 return $false;
135 }
136 if (!empty($this->metaKeySQL)) {
137 // If we want the primary keys, we have to issue a separate query
138 // Of course, a modified version of the metaColumnsSQL query using a
139 // LEFT JOIN would have been much more elegant, but postgres does
140 // not support OUTER JOINS. So here is the clumsy way.
141
142 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
143
144 $rskey = $this->Execute(sprintf($this->metaKeySQL,($table)));
145 // fetch all result in once for performance.
146 $keys =& $rskey->GetArray();
147 if (isset($savem)) $this->SetFetchMode($savem);
148 $ADODB_FETCH_MODE = $save;
149
150 $rskey->Close();
151 unset($rskey);
152 }
153
154 $rsdefa = array();
155 if (!empty($this->metaDefaultsSQL)) {
156 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
157 $sql = sprintf($this->metaDefaultsSQL, ($table));
158 $rsdef = $this->Execute($sql);
159 if (isset($savem)) $this->SetFetchMode($savem);
160 $ADODB_FETCH_MODE = $save;
161
162 if ($rsdef) {
163 while (!$rsdef->EOF) {
164 $num = $rsdef->fields['num'];
165 $s = $rsdef->fields['def'];
166 if (strpos($s,'::')===false && substr($s, 0, 1) == "'") { /* quoted strings hack... for now... fixme */
167 $s = substr($s, 1);
168 $s = substr($s, 0, strlen($s) - 1);
169 }
170
171 $rsdefa[$num] = $s;
172 $rsdef->MoveNext();
173 }
174 } else {
175 ADOConnection::outp( "==> SQL => " . $sql);
176 }
177 unset($rsdef);
178 }
179
180 $retarr = array();
181 while (!$rs->EOF) {
182 $fld = new ADOFieldObject();
183 $fld->name = $rs->fields[0];
184 $fld->type = $rs->fields[1];
185 $fld->max_length = $rs->fields[2];
186 if ($fld->max_length <= 0) $fld->max_length = $rs->fields[3]-4;
187 if ($fld->max_length <= 0) $fld->max_length = -1;
188 if ($fld->type == 'numeric') {
189 $fld->scale = $fld->max_length & 0xFFFF;
190 $fld->max_length >>= 16;
191 }
192 // dannym
193 // 5 hasdefault; 6 num-of-column
194 $fld->has_default = ($rs->fields[5] == 't');
195 if ($fld->has_default) {
196 $fld->default_value = $rsdefa[$rs->fields[6]];
197 }
198
199 //Freek
200 if ($rs->fields[4] == $this->true) {
201 $fld->not_null = true;
202 }
203
204 // Freek
205 if (is_array($keys)) {
206 foreach($keys as $key) {
207 if ($fld->name == $key['column_name'] AND $key['primary_key'] == $this->true)
208 $fld->primary_key = true;
209 if ($fld->name == $key['column_name'] AND $key['unique_key'] == $this->true)
210 $fld->unique = true; // What name is more compatible?
211 }
212 }
213
214 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;
215 else $retarr[($normalize) ? strtoupper($fld->name) : $fld->name] = $fld;
216
217 $rs->MoveNext();
218 }
219 $rs->Close();
220 if (empty($retarr)) {
221 $false = false;
222 return $false;
223 } else return $retarr;
224
225 }
226
227 }
228
229 ?>