ILIAS  release_4-3 Revision
 All Data Structures Namespaces Files Functions Variables Groups Pages
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 
48 require_once 'MDB2/Driver/Reverse/Common.php';
49 
57 class 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 ?>