ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
class.ilDBPdoManagerPostgres.php
Go to the documentation of this file.
1<?php
2require_once('class.ilDBPdoManager.php');
3require_once('./Services/Database/classes/QueryUtils/class.ilPostgresQueryUtils.php');
4
11{
12
16 public function getQueryUtils()
17 {
18 if (!$this->query_utils) {
19 $this->query_utils = new ilPostgresQueryUtils($this->db_instance);
20 }
21
22 return $this->query_utils;
23 }
24
25
33 public function getTableCreationQuery($name, $fields, $options = array())
34 {
35 return $this->getQueryUtils()->createTable($name, $fields, $options);
36 }
37
38
45 public function createTable($name, $fields, $options = array())
46 {
47 return $this->pdo->exec($this->getQueryUtils()->createTable($name, $fields, $options));
48 }
49
50
55 public function listTables($database = null)
56 {
58
59 // gratuitously stolen from PEAR DB _getSpecialQuery in pgsql.php
60 $query = 'SELECT c.relname AS "Name"' . ' FROM pg_class c, pg_user u' . ' WHERE c.relowner = u.usesysid' . " AND c.relkind = 'r'"
61 . ' AND NOT EXISTS' . ' (SELECT 1 FROM pg_views' . ' WHERE viewname = c.relname)' . " AND c.relname !~ '^(pg_|sql_)'" . ' UNION'
62 . ' SELECT c.relname AS "Name"' . ' FROM pg_class c' . " WHERE c.relkind = 'r'" . ' AND NOT EXISTS' . ' (SELECT 1 FROM pg_views'
63 . ' WHERE viewname = c.relname)' . ' AND NOT EXISTS' . ' (SELECT 1 FROM pg_user' . ' WHERE usesysid = c.relowner)'
64 . " AND c.relname !~ '^pg_'";
66
67 if ($db->options['portability']) {
68 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
69 }
70 sort($result); // FSX Patch
71 return $result;
72 }
73
74
79 public function createDatabase($name)
80 {
82 $name = $db->quoteIdentifier($name, true);
83
84 return $db->manipulate("CREATE DATABASE $name");
85 }
86
87
92 public function dropDatabase($name)
93 {
95
96 $name = $db->quoteIdentifier($name, true);
97
98 return $db->manipulate("DROP DATABASE $name");
99 }
100
101
108 public function alterTable($name, $changes, $check)
109 {
110 $db = $this->db_instance;
111 $reverse = $db->loadModule(ilDBConstants::MODULE_REVERSE);
116 foreach ($changes as $change_name => $change) {
117 switch ($change_name) {
118 case 'add':
119 case 'remove':
120 case 'change':
121 case 'name':
122 case 'rename':
123 break;
124 default:
125 throw new ilDatabaseException('change type "' . $change_name . '\" not yet supported');
126 }
127 }
128
129 if ($check) {
130 return true;
131 }
132
133 if (!empty($changes['add']) && is_array($changes['add'])) {
134 foreach ($changes['add'] as $field_name => $field) {
135 $query = 'ADD ' . $db->getFieldDefinition()->getDeclaration($field['type'], $field_name, $field);
136 $result = $db->manipulate("ALTER TABLE $name $query");
137 }
138 }
139
140 if (!empty($changes['remove']) && is_array($changes['remove'])) {
141 foreach ($changes['remove'] as $field_name => $field) {
142 $field_name = $db->quoteIdentifier($field_name, true);
143 $query = 'DROP ' . $field_name;
144 $result = $db->manipulate("ALTER TABLE $name $query");
145 }
146 }
147
148 if (!empty($changes['change']) && is_array($changes['change'])) {
149 foreach ($changes['change'] as $field_name => $field) {
150 $field_name = $db->quoteIdentifier($field_name, true);
151 if (!empty($field['type'])) {
152 $server_info = $db->getServerVersion();
153
154 if (is_array($server_info) && $server_info['major'] < 8) {
155 throw new ilDatabaseException('changing column type for "' . $change_name . '\" requires PostgreSQL 8.0 or above');
156 }
157
158 $query = "ALTER $field_name TYPE " . $db->getFieldDefinition()->getTypeDeclaration($field);
159 $result = $db->manipulate("ALTER TABLE $name $query");
160 }
161 if (array_key_exists('default', $field)) {
162 $query = "ALTER $field_name SET DEFAULT " . $db->quote($field['definition']['default'], $field['definition']['type']);
163 $result = $db->manipulate("ALTER TABLE $name $query");
164 }
165 if (!empty($field['notnull'])) {
166 $query = "ALTER $field_name " . ($field['definition']['notnull'] ? "SET" : "DROP") . ' NOT NULL';
167 $result = $db->manipulate("ALTER TABLE $name $query");
168 }
169 }
170 }
171
172 if (!empty($changes['rename']) && is_array($changes['rename'])) {
173 foreach ($changes['rename'] as $field_name => $field) {
174 $field_name = $db->quoteIdentifier($field_name, true);
175 $result = $db->manipulate("ALTER TABLE $name RENAME COLUMN $field_name TO " . $db->quoteIdentifier($field['name'], true));
176 }
177 }
178
179 if (!empty($changes['name'])) {
180 $result = $db->manipulate("ALTER TABLE " . $db->quoteIdentifier($name, true) . " RENAME TO " . $db->quoteIdentifier($changes['name']));
181
182 $idx = array_merge($this->listTableIndexes($changes['name']), $this->listTableConstraints($changes['name']));
183 foreach ($idx as $index_name) {
184 $index_newname = preg_replace("/^$name/", $changes['name'], $index_name);
185 $result = $db->manipulate("ALTER INDEX " . $this->getIndexName($index_name) . " RENAME TO " . $this->getIndexName($index_newname));
186 }
187 }
188
189 return true;
190 }
191
192
197 public function listTableFields($table)
198 {
199 $db = $this->db_instance;
200
201 $table = $db->quoteIdentifier($table, true);
202 $res = $this->pdo->query("select * from $table");
203 for ($i = 0; $i < $res->columnCount(); $i++) {
204 $data[] = $res->getColumnMeta($i)["name"];
205 }
206 return $data;
207 }
208
209
214 public function listTableIndexes($table)
215 {
216 $db = $this->db_instance;
217
218 $table = $db->quote($table, 'text');
219 $subquery = "SELECT indexrelid FROM pg_index, pg_class";
220 $subquery .= " WHERE pg_class.relname=$table AND pg_class.oid=pg_index.indrelid AND indisunique != 't' AND indisprimary != 't'";
221 $query = "SELECT relname FROM pg_class WHERE oid IN ($subquery)";
222 $indexes = $db->queryCol($query, 'text');
223
224 $result = array();
225 foreach ($indexes as $index) {
226 $index = $this->fixIndexName($index);
227 if (!empty($index)) {
228 $result[$index] = true;
229 }
230 }
231
232 if ($db->options['portability']) {
233 $result = array_change_key_case($result, $db->options['field_case']);
234 }
235
236 return array_keys($result);
237 }
238
239
245 {
246 $db = $this->db_instance;
247
248 $table = $db->quote($table, 'text');
249 $subquery = "SELECT indexrelid FROM pg_index, pg_class";
250 $subquery .= " WHERE pg_class.relname=$table AND pg_class.oid=pg_index.indrelid AND (indisunique = 't' OR indisprimary = 't')";
251 $query = "SELECT relname FROM pg_class WHERE oid IN ($subquery)";
252 $constraints = $db->queryCol($query);
253
254 $result = array();
255 foreach ($constraints as $constraint) {
256 $constraint = $this->fixIndexName($constraint);
257 if (!empty($constraint)) {
258 $result[$constraint] = true;
259 }
260 }
261
262 if ($db->options['portability']
263 && $db->options['field_case'] == CASE_LOWER
264 ) {
265 $result = array_change_key_case($result, $db->options['field_case']);
266 }
267
268 return array_keys($result);
269 }
270
271
278 public function createSequence($seq_name, $start = 1, $options = array())
279 {
280 $db = $this->db_instance;
281
282 $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
283
284 return $db->manipulate("CREATE SEQUENCE $sequence_name INCREMENT 1" . ($start < 1 ? " MINVALUE $start" : '') . " START $start");
285 }
286
287
292 public function dropSequence($seq_name)
293 {
294 $db = $this->db_instance;
295
296 $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
297
298 return $db->manipulate("DROP SEQUENCE $sequence_name");
299 }
300
301
307 public function dropIndex($table, $name)
308 {
309 $db = $this->db_instance;
310
311 $name = $this->getIndexName($name);
312 $name = $db->quoteIdentifier($this->getDBInstance()->constraintName($table, $name), true);
313
314 return $db->manipulate("DROP INDEX $name");
315 }
316
317
322 protected function fixIndexName($idx)
323 {
324 $idx_pattern = '/^' . preg_replace('/%s/', '([a-z0-9_]+)', ilDBPdoFieldDefinition::INDEX_FORMAT) . '$/i';
325 $idx_name = preg_replace($idx_pattern, '\\1', $idx);
326 if ($idx_name && !strcasecmp($idx, $this->db_instance->getIndexName($idx_name))) {
327 return $idx_name;
328 }
329
330 return $idx;
331 }
332
333
338 public function listSequences($database = null)
339 {
340 $db = $this->db_instance;
341
342 $query = "SELECT relname FROM pg_class WHERE relkind = 'S' AND relnamespace IN";
343 $query .= "(SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema')";
344 $table_names = $db->queryCol($query);
345
346 $result = array();
347 foreach ($table_names as $table_name) {
348 $result[] = $this->fixSequenceName($table_name);
349 }
350 if ($db->options['portability']) {
351 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
352 }
353 sort($result); // FSX patch
354
355 return $result;
356 }
357
358
365 public function dropConstraint($table, $name, $primary = false)
366 {
367 $table_quoted = $this->getDBInstance()->quoteIdentifier($table, true);
368 $name = $this->getDBInstance()->quoteIdentifier($table . '_' . $this->getDBInstance()->getIndexName($name), true);
369
370 return $this->pdo->exec("ALTER TABLE $table_quoted DROP CONSTRAINT $name");
371 }
372}
$result
if(!isset( $_REQUEST[ 'ReturnTo'])) if(!isset($_REQUEST['AuthId'])) $options
Definition: as_login.php:20
An exception for terminatinating execution or to throw for unit testing.
getTableCreationQuery($name, $fields, $options=array())
dropConstraint($table, $name, $primary=false)
createTable($name, $fields, $options=array())
createSequence($seq_name, $start=1, $options=array())
Class ilDBPdoManager.
fixSequenceName($sqn, $check=false)
alterTable($name, $changes, $check)
Class ilDatabaseException.
Class ilPostgresQueryUtils.
$i
Definition: disco.tpl.php:19
if($format !==null) $name
Definition: metadata.php:146
$index
Definition: metadata.php:60
$query
if(empty($password)) $table
Definition: pwgen.php:24
foreach($_POST as $key=> $value) $res