ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
class.ilDBPdoReversePostgres.php
Go to the documentation of this file.
1<?php
2require_once('class.ilDBPdoReverse.php');
3
10{
11
12 // {{{ getTableFieldDefinition()
13
22 public function getTableFieldDefinition($table, $field_name)
23 {
29 $result = $this->db_instance->loadModule(ilDBConstants::MODULE_REVERSE);
30
31 $query = "SELECT a.attname AS name,
32 t.typname AS type,
33 CASE a.attlen
34 WHEN -1 THEN
35 CASE t.typname
36 WHEN 'numeric' THEN (a.atttypmod / 65536)
37 WHEN 'decimal' THEN (a.atttypmod / 65536)
38 WHEN 'money' THEN (a.atttypmod / 65536)
39 ELSE CASE a.atttypmod
40 WHEN -1 THEN NULL
41 ELSE a.atttypmod - 4
42 END
43 END
44 ELSE a.attlen
45 END AS length,
46 CASE t.typname
47 WHEN 'numeric' THEN (a.atttypmod % 65536) - 4
48 WHEN 'decimal' THEN (a.atttypmod % 65536) - 4
49 WHEN 'money' THEN (a.atttypmod % 65536) - 4
50 ELSE 0
51 END AS scale,
52 a.attnotnull,
53 a.atttypmod,
54 a.atthasdef,
55 (SELECT substring(pg_get_expr(d.adbin, d.adrelid) for 128)
56 FROM pg_attrdef d
57 WHERE d.adrelid = a.attrelid
58 AND d.adnum = a.attnum
59 AND a.atthasdef
60 ) as default
61 FROM pg_attribute a,
62 pg_class c,
63 pg_type t
64 WHERE c.relname = " . $db->quote($table, 'text') . "
65 AND a.atttypid = t.oid
66 AND c.oid = a.attrelid
67 AND NOT a.attisdropped
68 AND a.attnum > 0
69 AND a.attname = " . $db->quote($field_name, 'text') . "
70 ORDER BY a.attnum";
71 $column = $db->queryRow($query, null, MDB2_FETCHMODE_ASSOC);
72
73 if (empty($column)) {
74 throw new ilDatabaseException('it was not specified an existing table column');
75 }
76
77 $column = array_change_key_case($column, CASE_LOWER);
78 $mapped_datatype = $db->getFieldDefinition()->mapNativeDatatype($column);
79
80 list($types, $length, $unsigned, $fixed) = $mapped_datatype;
81 $notnull = false;
82 if (!empty($column['attnotnull']) && $column['attnotnull'] == 't') {
83 $notnull = true;
84 }
85 $default = null;
86 if ($column['atthasdef'] === 't'
87 && !preg_match("/nextval\‍('([^']+)'/", $column['default'])
88 ) {
89 $default = $column['default'];#substr($column['adsrc'], 1, -1);
90 if (is_null($default) && $notnull) {
91 $default = '';
92 }
93 }
94 $autoincrement = false;
95 if (preg_match("/nextval\‍('([^']+)'/", $column['default'], $nextvals)) {
96 $autoincrement = true;
97 }
98 $definition[0] = array( 'notnull' => $notnull, 'nativetype' => $column['type'] );
99 if (!is_null($length)) {
100 $definition[0]['length'] = $length;
101 }
102 if (!is_null($unsigned)) {
103 $definition[0]['unsigned'] = $unsigned;
104 }
105 if (!is_null($fixed)) {
106 $definition[0]['fixed'] = $fixed;
107 }
108 if ($default !== false) {
109 $definition[0]['default'] = $default;
110 }
111 if ($autoincrement !== false) {
112 $definition[0]['autoincrement'] = $autoincrement;
113 }
114 foreach ($types as $key => $type) {
115 $definition[$key] = $definition[0];
116 if ($type == 'clob' || $type == 'blob') {
117 unset($definition[$key]['default']);
118 }
119 $definition[$key]['type'] = $type;
120 $definition[$key]['mdb2type'] = $type;
121 }
122
123 return $definition;
124 }
125
126
133 public function getTableIndexDefinition($table, $index_name)
134 {
135 $db = $this->db_instance;
136 $manager = $db->loadModule(ilDBConstants::MODULE_MANAGER);
141 $query = 'SELECT relname, indkey FROM pg_index, pg_class';
142 $query .= ' WHERE pg_class.oid = pg_index.indexrelid';
143 $query .= " AND indisunique != 't' AND indisprimary != 't'";
144 $query .= ' AND pg_class.relname = %s';
145 $index_name_mdb2 = $db->getIndexName($index_name);
146 $failed = false;
147 try {
148 $row = $db->queryRow(sprintf($query, $db->quote($index_name_mdb2, 'text')), null, ilDBConstants::FETCHMODE_DEFAULT);
149 } catch (Exception $e) {
150 $failed = true;
151 }
152 if ($failed || empty($row)) {
153 $row = $db->queryRow(sprintf($query, $db->quote($index_name, 'text')), null, ilDBConstants::FETCHMODE_DEFAULT);
154 }
155
156 if (empty($row)) {
157 throw new ilDatabaseException('it was not specified an existing table index');
158 }
159
160 $row = array_change_key_case($row, CASE_LOWER);
161
162 $columns = $manager->listTableFields($table);
163
164 $definition = array();
165
166 $index_column_numbers = explode(' ', $row['indkey']);
167
168 $colpos = 1;
169 foreach ($index_column_numbers as $number) {
170 $definition['fields'][$columns[($number - 1)]] = array(
171 'position' => $colpos++,
172 'sorting' => 'ascending',
173 );
174 }
175
176 return $definition;
177 }
178
179 // }}}
180 // {{{ getTableConstraintDefinition()
189 public function getTableConstraintDefinition($table, $constraint_name)
190 {
191 $db = $this->db_instance;
192
193 $query = 'SELECT relname, indisunique, indisprimary, indkey FROM pg_index, pg_class';
194 $query .= ' WHERE pg_class.oid = pg_index.indexrelid';
195 $query .= " AND (indisunique = 't' OR indisprimary = 't')";
196 $query .= ' AND pg_class.relname = %s';
197 $constraint_name_mdb2 = $db->getIndexName($constraint_name);
198 try {
199 $row = $db->queryRow(sprintf($query, $db->quote($constraint_name_mdb2, 'text')), null, ilDBConstants::FETCHMODE_ASSOC);
200 } catch (Exception $e) {
201 }
202
203 if ($e instanceof PDOException || empty($row)) {
204 // fallback to the given $index_name, without transformation
205 $row = $db->queryRow(sprintf($query, $db->quote($constraint_name, 'text')), null, ilDBConstants::FETCHMODE_ASSOC);
206 }
207
208 if (empty($row)) {
209 throw new ilDatabaseException($constraint_name . ' is not an existing table constraint');
210 }
211
212 $row = array_change_key_case($row, CASE_LOWER);
213 $columns = $db->loadModule(ilDBConstants::MODULE_MANAGER)->listTableFields($table);
214
215 $definition = array();
216 if ($row['indisprimary'] == 't') {
217 $definition['primary'] = true;
218 } elseif ($row['indisunique'] == 't') {
219 $definition['unique'] = true;
220 }
221
222 $index_column_numbers = explode(' ', $row['indkey']);
223
224 $colpos = 1;
225 foreach ($index_column_numbers as $number) {
226 $definition['fields'][$columns[($number - 1)]] = array(
227 'position' => $colpos++,
228 'sorting' => 'ascending',
229 );
230 }
231
232 return $definition;
233 }
234
235 // }}}
236 // {{{ getTriggerDefinition()
237
252 public function getTriggerDefinition($trigger)
253 {
254 $db = $this->db_instance;
255
256 $query = "SELECT trg.tgname AS trigger_name,
257 tbl.relname AS table_name,
258 CASE
259 WHEN p.proname IS NOT NULL THEN 'EXECUTE PROCEDURE ' || p.proname || '();'
260 ELSE ''
261 END AS trigger_body,
262 CASE trg.tgtype & cast(2 as int2)
263 WHEN 0 THEN 'AFTER'
264 ELSE 'BEFORE'
265 END AS trigger_type,
266 CASE trg.tgtype & cast(28 as int2)
267 WHEN 16 THEN 'UPDATE'
268 WHEN 8 THEN 'DELETE'
269 WHEN 4 THEN 'INSERT'
270 WHEN 20 THEN 'INSERT, UPDATE'
271 WHEN 28 THEN 'INSERT, UPDATE, DELETE'
272 WHEN 24 THEN 'UPDATE, DELETE'
273 WHEN 12 THEN 'INSERT, DELETE'
274 END AS trigger_event,
275 trg.tgenabled AS trigger_enabled,
276 obj_description(trg.oid, 'pg_trigger') AS trigger_comment
277 FROM pg_trigger trg,
278 pg_class tbl,
279 pg_proc p
280 WHERE trg.tgrelid = tbl.oid
281 AND trg.tgfoid = p.oid
282 AND trg.tgname = " . $db->quote($trigger, 'text');
283 $types = array(
284 'trigger_name' => 'text',
285 'table_name' => 'text',
286 'trigger_body' => 'text',
287 'trigger_type' => 'text',
288 'trigger_event' => 'text',
289 'trigger_comment' => 'text',
290 'trigger_enabled' => 'boolean',
291 );
292
293 return $db->queryRow($query, $types, MDB2_FETCHMODE_ASSOC);
294 }
295
296 // }}}
297 // {{{ tableInfo()
298
317 public function tableInfo($result, $mode = null)
318 {
319 if (is_string($result)) {
320 return parent::tableInfo($result, $mode);
321 }
322
323 $db = $this->db_instance;
324
325 $resource = MDB2::isResultCommon($result) ? $result->getResource() : $result;
326 if (!is_resource($resource)) {
327 return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null, 'Could not generate result resource', __FUNCTION__);
328 }
329
330 if ($db->options['portability']) {
331 if ($db->options['field_case'] == CASE_LOWER) {
332 $case_func = 'strtolower';
333 } else {
334 $case_func = 'strtoupper';
335 }
336 } else {
337 $case_func = 'strval';
338 }
339
340 $count = @pg_num_fields($resource);
341 $res = array();
342
343 if ($mode) {
344 $res['num_fields'] = $count;
345 }
346
347 $db->loadModule('Datatype', null, true);
348 for ($i = 0; $i < $count; $i++) {
349 $res[$i] = array(
350 'table' => function_exists('pg_field_table') ? @pg_field_table($resource, $i) : '',
351 'name' => $case_func(@pg_field_name($resource, $i)),
352 'type' => @pg_field_type($resource, $i),
353 'length' => @pg_field_size($resource, $i),
354 'flags' => '',
355 );
356 $mdb2type_info = $db->datatype->mapNativeDatatype($res[$i]);
357 if (PEAR::isError($mdb2type_info)) {
358 return $mdb2type_info;
359 }
360 $res[$i]['mdb2type'] = $mdb2type_info[0][0];
361 if ($mode & MDB2_TABLEINFO_ORDER) {
362 $res['order'][$res[$i]['name']] = $i;
363 }
364 if ($mode & MDB2_TABLEINFO_ORDERTABLE) {
365 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
366 }
367 }
368
369 return $res;
370 }
371}
sprintf('%.4f', $callTime)
$column
Definition: 39dropdown.php:62
$result
const MDB2_ERROR_NEED_MORE_DATA
Definition: MDB2.php:92
const MDB2_FETCHMODE_ASSOC
Column data indexed by column names.
Definition: MDB2.php:134
const MDB2_TABLEINFO_ORDERTABLE
Definition: Common.php:60
const MDB2_TABLEINFO_ORDER
These are constants for the tableInfo-function they are bitwised or'ed.
Definition: Common.php:59
$failed
Definition: Utf8Test.php:85
if(! $in) $columns
Definition: Utf8Test.php:45
An exception for terminatinating execution or to throw for unit testing.
isResultCommon($value)
Tell whether a value is a MDB2 result implementing the common interface.
Definition: MDB2.php:660
isError($data, $code=null)
Tell whether a value is a PEAR error.
Definition: PEAR.php:280
tableInfo($result, $mode=null)
Returns information about a table or a result set.
getTriggerDefinition($trigger)
Get the structure of a trigger into an array.
getTableConstraintDefinition($table, $constraint_name)
Get the structure of a constraint into an array.
Class ilDBPdoReverse.
getTableIndexDefinition($table, $index_name)
getTableFieldDefinition($table_name, $field_name)
Class ilDatabaseException.
$key
Definition: croninfo.php:18
$i
Definition: disco.tpl.php:19
$query
$type
if(empty($password)) $table
Definition: pwgen.php:24
foreach($_POST as $key=> $value) $res