ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
pgsql.php
Go to the documentation of this file.
1<?php
2// +----------------------------------------------------------------------+
3// | PHP versions 4 and 5 |
4// +----------------------------------------------------------------------+
5// | Copyright (c) 1998-2007 Manuel Lemos, Tomas V.V.Cox, |
6// | Stig. S. Bakken, Lukas Smith |
7// | All rights reserved. |
8// +----------------------------------------------------------------------+
9// | MDB2 is a merge of PEAR DB and Metabases that provides a unified DB |
10// | API as well as database abstraction for PHP applications. |
11// | This LICENSE is in the BSD license style. |
12// | |
13// | Redistribution and use in source and binary forms, with or without |
14// | modification, are permitted provided that the following conditions |
15// | are met: |
16// | |
17// | Redistributions of source code must retain the above copyright |
18// | notice, this list of conditions and the following disclaimer. |
19// | |
20// | Redistributions in binary form must reproduce the above copyright |
21// | notice, this list of conditions and the following disclaimer in the |
22// | documentation and/or other materials provided with the distribution. |
23// | |
24// | Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken, |
25// | Lukas Smith nor the names of his contributors may be used to endorse |
26// | or promote products derived from this software without specific prior|
27// | written permission. |
28// | |
29// | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS |
30// | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT |
31// | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS |
32// | FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE |
33// | REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, |
34// | INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, |
35// | BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS|
36// | OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED |
37// | AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT |
38// | LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY|
39// | WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE |
40// | POSSIBILITY OF SUCH DAMAGE. |
41// +----------------------------------------------------------------------+
42// | Authors: Paul Cooper <pgc@ucecom.com> |
43// | Lorenzo Alberton <l.alberton@quipo.it> |
44// +----------------------------------------------------------------------+
45//
46// $Id: pgsql.php,v 1.60 2007/03/29 18:18:06 quipo Exp $
47
48require_once 'MDB2/Driver/Reverse/Common.php';
49
57class MDB2_Driver_Reverse_pgsql extends MDB2_Driver_Reverse_Common
58{
59 // {{{ getTableFieldDefinition()
60
69 function getTableFieldDefinition($table, $field_name)
70 {
71 $db =& $this->getDBInstance();
72 if (PEAR::isError($db)) {
73 return $db;
74 }
75
76 $result = $db->loadModule('Datatype', null, true);
77 if (PEAR::isError($result)) {
78 return $result;
79 }
80
81 $query = "SELECT a.attname AS name,
82 t.typname AS type,
83 CASE a.attlen
84 WHEN -1 THEN
85 CASE t.typname
86 WHEN 'numeric' THEN (a.atttypmod / 65536)
87 WHEN 'decimal' THEN (a.atttypmod / 65536)
88 WHEN 'money' THEN (a.atttypmod / 65536)
89 ELSE CASE a.atttypmod
90 WHEN -1 THEN NULL
91 ELSE a.atttypmod - 4
92 END
93 END
94 ELSE a.attlen
95 END AS length,
96 CASE t.typname
97 WHEN 'numeric' THEN (a.atttypmod % 65536) - 4
98 WHEN 'decimal' THEN (a.atttypmod % 65536) - 4
99 WHEN 'money' THEN (a.atttypmod % 65536) - 4
100 ELSE 0
101 END AS scale,
102 a.attnotnull,
103 a.atttypmod,
104 a.atthasdef,
105 (SELECT substring(pg_get_expr(d.adbin, d.adrelid) for 128)
106 FROM pg_attrdef d
107 WHERE d.adrelid = a.attrelid
108 AND d.adnum = a.attnum
109 AND a.atthasdef
110 ) as default
111 FROM pg_attribute a,
112 pg_class c,
113 pg_type t
114 WHERE c.relname = ".$db->quote($table, 'text')."
115 AND a.atttypid = t.oid
116 AND c.oid = a.attrelid
117 AND NOT a.attisdropped
118 AND a.attnum > 0
119 AND a.attname = ".$db->quote($field_name, 'text')."
120 ORDER BY a.attnum";
121 $column = $db->queryRow($query, null, MDB2_FETCHMODE_ASSOC);
122 if (PEAR::isError($column)) {
123 return $column;
124 }
125
126 if (empty($column)) {
127 return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
128 'it was not specified an existing table column', __FUNCTION__);
129 }
130
131 $column = array_change_key_case($column, CASE_LOWER);
132 $mapped_datatype = $db->datatype->mapNativeDatatype($column);
133 if (PEAR::IsError($mapped_datatype)) {
134 return $mapped_datatype;
135 }
136 list($types, $length, $unsigned, $fixed) = $mapped_datatype;
137 $notnull = false;
138 if (!empty($column['attnotnull']) && $column['attnotnull'] == 't') {
139 $notnull = true;
140 }
141 $default = null;
142 if ($column['atthasdef'] === 't'
143 && !preg_match("/nextval\‍('([^']+)'/", $column['default'])
144 ) {
145 $default = $column['default'];#substr($column['adsrc'], 1, -1);
146 if (is_null($default) && $notnull) {
147 $default = '';
148 }
149 }
150 $autoincrement = false;
151 if (preg_match("/nextval\‍('([^']+)'/", $column['default'], $nextvals)) {
152 $autoincrement = true;
153 }
154 $definition[0] = array('notnull' => $notnull, 'nativetype' => $column['type']);
155 if (!is_null($length)) {
156 $definition[0]['length'] = $length;
157 }
158 if (!is_null($unsigned)) {
159 $definition[0]['unsigned'] = $unsigned;
160 }
161 if (!is_null($fixed)) {
162 $definition[0]['fixed'] = $fixed;
163 }
164 if ($default !== false) {
165 $definition[0]['default'] = $default;
166 }
167 if ($autoincrement !== false) {
168 $definition[0]['autoincrement'] = $autoincrement;
169 }
170 foreach ($types as $key => $type) {
171 $definition[$key] = $definition[0];
172 if ($type == 'clob' || $type == 'blob') {
173 unset($definition[$key]['default']);
174 }
175 $definition[$key]['type'] = $type;
176 $definition[$key]['mdb2type'] = $type;
177 }
178 return $definition;
179 }
180
181 // }}}
182 // {{{ getTableIndexDefinition()
191 function getTableIndexDefinition($table, $index_name)
192 {
193 $db =& $this->getDBInstance();
194 if (PEAR::isError($db)) {
195 return $db;
196 }
197
198 $query = 'SELECT relname, indkey FROM pg_index, pg_class';
199 $query.= ' WHERE pg_class.oid = pg_index.indexrelid';
200 $query.= " AND indisunique != 't' AND indisprimary != 't'";
201 $query.= ' AND pg_class.relname = %s';
202 $index_name_mdb2 = $db->getIndexName($index_name);
203 $row = $db->queryRow(sprintf($query, $db->quote($index_name_mdb2, 'text')), null, MDB2_FETCHMODE_ASSOC);
204 if (PEAR::isError($row) || empty($row)) {
205 // fallback to the given $index_name, without transformation
206 $row = $db->queryRow(sprintf($query, $db->quote($index_name, 'text')), null, MDB2_FETCHMODE_ASSOC);
207 }
208 if (PEAR::isError($row)) {
209 return $row;
210 }
211
212 if (empty($row)) {
213 return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
214 'it was not specified an existing table index', __FUNCTION__);
215 }
216
217 $row = array_change_key_case($row, CASE_LOWER);
218
219 $db->loadModule('Manager', null, true);
220 $columns = $db->manager->listTableFields($table);
221
222 $definition = array();
223
224 $index_column_numbers = explode(' ', $row['indkey']);
225
226 $colpos = 1;
227 foreach ($index_column_numbers as $number) {
228 $definition['fields'][$columns[($number - 1)]] = array(
229 'position' => $colpos++,
230 'sorting' => 'ascending',
231 );
232 }
233 return $definition;
234 }
235
236 // }}}
237 // {{{ getTableConstraintDefinition()
246 function getTableConstraintDefinition($table, $constraint_name)
247 {
248 $db =& $this->getDBInstance();
249 if (PEAR::isError($db)) {
250 return $db;
251 }
252
253 $query = 'SELECT relname, indisunique, indisprimary, indkey FROM pg_index, pg_class';
254 $query.= ' WHERE pg_class.oid = pg_index.indexrelid';
255 $query.= " AND (indisunique = 't' OR indisprimary = 't')";
256 $query.= ' AND pg_class.relname = %s';
257 $constraint_name_mdb2 = $db->getIndexName($constraint_name);
258 $row = $db->queryRow(sprintf($query, $db->quote($constraint_name_mdb2, 'text')), null, MDB2_FETCHMODE_ASSOC);
259 if (PEAR::isError($row) || empty($row)) {
260 // fallback to the given $index_name, without transformation
261 $row = $db->queryRow(sprintf($query, $db->quote($constraint_name, 'text')), null, MDB2_FETCHMODE_ASSOC);
262 }
263 if (PEAR::isError($row)) {
264 return $row;
265 }
266
267 if (empty($row)) {
268 return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
269 $constraint_name . ' is not an existing table constraint', __FUNCTION__);
270 }
271
272 $row = array_change_key_case($row, CASE_LOWER);
273
274 $db->loadModule('Manager', null, true);
275 $columns = $db->manager->listTableFields($table);
276
277 $definition = array();
278 if ($row['indisprimary'] == 't') {
279 $definition['primary'] = true;
280 } elseif ($row['indisunique'] == 't') {
281 $definition['unique'] = true;
282 }
283
284 $index_column_numbers = explode(' ', $row['indkey']);
285
286 $colpos = 1;
287 foreach ($index_column_numbers as $number) {
288 $definition['fields'][$columns[($number - 1)]] = array(
289 'position' => $colpos++,
290 'sorting' => 'ascending',
291 );
292 }
293 return $definition;
294 }
295
296 // }}}
297 // {{{ getTriggerDefinition()
298
313 function getTriggerDefinition($trigger)
314 {
315 $db =& $this->getDBInstance();
316 if (PEAR::isError($db)) {
317 return $db;
318 }
319
320 $query = "SELECT trg.tgname AS trigger_name,
321 tbl.relname AS table_name,
322 CASE
323 WHEN p.proname IS NOT NULL THEN 'EXECUTE PROCEDURE ' || p.proname || '();'
324 ELSE ''
325 END AS trigger_body,
326 CASE trg.tgtype & cast(2 as int2)
327 WHEN 0 THEN 'AFTER'
328 ELSE 'BEFORE'
329 END AS trigger_type,
330 CASE trg.tgtype & cast(28 as int2)
331 WHEN 16 THEN 'UPDATE'
332 WHEN 8 THEN 'DELETE'
333 WHEN 4 THEN 'INSERT'
334 WHEN 20 THEN 'INSERT, UPDATE'
335 WHEN 28 THEN 'INSERT, UPDATE, DELETE'
336 WHEN 24 THEN 'UPDATE, DELETE'
337 WHEN 12 THEN 'INSERT, DELETE'
338 END AS trigger_event,
339 trg.tgenabled AS trigger_enabled,
340 obj_description(trg.oid, 'pg_trigger') AS trigger_comment
341 FROM pg_trigger trg,
342 pg_class tbl,
343 pg_proc p
344 WHERE trg.tgrelid = tbl.oid
345 AND trg.tgfoid = p.oid
346 AND trg.tgname = ". $db->quote($trigger, 'text');
347 $types = array(
348 'trigger_name' => 'text',
349 'table_name' => 'text',
350 'trigger_body' => 'text',
351 'trigger_type' => 'text',
352 'trigger_event' => 'text',
353 'trigger_comment' => 'text',
354 'trigger_enabled' => 'boolean',
355 );
356 return $db->queryRow($query, $types, MDB2_FETCHMODE_ASSOC);
357 }
358
359 // }}}
360 // {{{ tableInfo()
361
380 function tableInfo($result, $mode = null)
381 {
382 if (is_string($result)) {
383 return parent::tableInfo($result, $mode);
384 }
385
386 $db =& $this->getDBInstance();
387 if (PEAR::isError($db)) {
388 return $db;
389 }
390
391 $resource = MDB2::isResultCommon($result) ? $result->getResource() : $result;
392 if (!is_resource($resource)) {
393 return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,
394 'Could not generate result resource', __FUNCTION__);
395 }
396
397 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
398 if ($db->options['field_case'] == CASE_LOWER) {
399 $case_func = 'strtolower';
400 } else {
401 $case_func = 'strtoupper';
402 }
403 } else {
404 $case_func = 'strval';
405 }
406
407 $count = @pg_num_fields($resource);
408 $res = array();
409
410 if ($mode) {
411 $res['num_fields'] = $count;
412 }
413
414 $db->loadModule('Datatype', null, true);
415 for ($i = 0; $i < $count; $i++) {
416 $res[$i] = array(
417 'table' => function_exists('pg_field_table') ? @pg_field_table($resource, $i) : '',
418 'name' => $case_func(@pg_field_name($resource, $i)),
419 'type' => @pg_field_type($resource, $i),
420 'length' => @pg_field_size($resource, $i),
421 'flags' => '',
422 );
423 $mdb2type_info = $db->datatype->mapNativeDatatype($res[$i]);
424 if (PEAR::isError($mdb2type_info)) {
425 return $mdb2type_info;
426 }
427 $res[$i]['mdb2type'] = $mdb2type_info[0][0];
428 if ($mode & MDB2_TABLEINFO_ORDER) {
429 $res['order'][$res[$i]['name']] = $i;
430 }
431 if ($mode & MDB2_TABLEINFO_ORDERTABLE) {
432 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
433 }
434 }
435
436 return $res;
437 }
438}
439?>
$result
$result
Definition: CleanUpTest.php:407
MDB2_PORTABILITY_FIX_CASE
const MDB2_PORTABILITY_FIX_CASE
Portability: convert names of tables and fields to case defined in the "field_case" option when using...
Definition: MDB2.php:158
MDB2_ERROR_NOT_FOUND
const MDB2_ERROR_NOT_FOUND
Definition: MDB2.php:71
MDB2_ERROR_NEED_MORE_DATA
const MDB2_ERROR_NEED_MORE_DATA
Definition: MDB2.php:87
MDB2_FETCHMODE_ASSOC
const MDB2_FETCHMODE_ASSOC
Column data indexed by column names.
Definition: MDB2.php:129
MDB2_TABLEINFO_ORDERTABLE
const MDB2_TABLEINFO_ORDERTABLE
Definition: Common.php:60
MDB2_TABLEINFO_ORDER
const MDB2_TABLEINFO_ORDER
These are constants for the tableInfo-function they are bitwised or'ed.
Definition: Common.php:59
$columns
if(! $in) $columns
Definition: Utf8Test.php:46
MDB2_Driver_Reverse_Common
Definition: Common.php:71
MDB2_Driver_Reverse_pgsql
Definition: pgsql.php:58
MDB2_Driver_Reverse_pgsql\getTriggerDefinition
getTriggerDefinition($trigger)
Get the structure of a trigger into an array.
Definition: pgsql.php:313
MDB2_Driver_Reverse_pgsql\getTableConstraintDefinition
getTableConstraintDefinition($table, $constraint_name)
Get the structure of a constraint into an array.
Definition: pgsql.php:246
MDB2_Driver_Reverse_pgsql\tableInfo
tableInfo($result, $mode=null)
Returns information about a table or a result set.
Definition: pgsql.php:380
MDB2_Driver_Reverse_pgsql\getTableIndexDefinition
getTableIndexDefinition($table, $index_name)
Get the structure of an index into an array.
Definition: pgsql.php:191
MDB2_Driver_Reverse_pgsql\getTableFieldDefinition
getTableFieldDefinition($table, $field_name)
Get the structure of a field into an array.
Definition: pgsql.php:69
MDB2_Module_Common\getDBInstance
& getDBInstance()
Get the instance of MDB2 associated with the module instance.
Definition: MDB2.php:4206
MDB2\isResultCommon
isResultCommon($value)
Tell whether a value is a MDB2 result implementing the common interface.
Definition: MDB2.php:655
PEAR\isError
isError($data, $code=null)
Tell whether a value is a PEAR error.
Definition: PEAR.php:279