ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
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, ilDBConstants::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, ilDBConstants::FETCHMODE_ASSOC);
294 }
295}
$result
$failed
Definition: Utf8Test.php:85
if(! $in) $columns
Definition: Utf8Test.php:45
$default
Definition: build.php:20
An exception for terminatinating execution or to throw for unit testing.
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
$row
$query
$type
if(empty($password)) $table
Definition: pwgen.php:24