ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
class.ilDBPdoManager.php
Go to the documentation of this file.
1<?php
2require_once('./Services/Database/interfaces/interface.ilDBManager.php');
3
10{
11
15 protected $pdo;
19 protected $db_instance;
20
21
28 public function __construct(\PDO $pdo, ilDBPdo $db_instance)
29 {
30 $this->pdo = $pdo;
31 $this->db_instance = $db_instance;
32 }
33
34
38 protected $query_utils;
39
40
44 public function getQueryUtils()
45 {
46 if (!$this->query_utils) {
47 $this->query_utils = new ilMySQLQueryUtils($this->db_instance);
48 }
49
50 return $this->query_utils;
51 }
52
53
57 public function getDBInstance()
58 {
59 return $this->db_instance;
60 }
61
62
67 public function listTables($database = null)
68 {
69 $str = 'SHOW TABLES ' . ($database ? ' IN ' . $database : '');
70 $r = $this->pdo->query($str);
71 $tables = array();
72
73 $sequence_identifier = "_seq";
74 while ($data = $r->fetchColumn()) {
75 if (!preg_match("/{$sequence_identifier}$/um", $data)) {
76 $tables[] = $data;
77 }
78 }
79
80 return $tables;
81 }
82
83
89 protected function fixSequenceName($sqn, $check = false)
90 {
91 $seq_pattern = '/^' . preg_replace('/%s/', '([a-z0-9_]+)', ilDBConstants::SEQUENCE_FORMAT) . '$/i';
92 $seq_name = preg_replace($seq_pattern, '\\1', $sqn);
93 if ($seq_name && !strcasecmp($sqn, $this->db_instance->getSequenceName($seq_name))) {
94 return $seq_name;
95 }
96 if ($check) {
97 return false;
98 }
99
100 return $sqn;
101 }
102
103
108 public function listSequences($database = null)
109 {
110 $query = "SHOW TABLES";
111 if (!is_null($database)) {
112 $query .= " FROM $database";
113 }
114
115 $res = $this->db_instance->query($query);
116
117 $result = array();
118 while ($table_name = $this->db_instance->fetchAssoc($res)) {
119 if ($sqn = $this->fixSequenceName(reset($table_name), true)) {
120 $result[] = $sqn;
121 }
122 }
123 if ($this->db_instance->options['portability']) {
124 $result = array_map(($this->db_instance->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
125 }
126
127 return $result;
128 }
129
130
138 public function createConstraint($table, $name, $definition)
139 {
140 $db = $this->db_instance;
141
142 $table = $db->quoteIdentifier($table, true);
143 $name = $db->quoteIdentifier($db->getIndexName($name), true);
144 $query = "ALTER TABLE $table ADD CONSTRAINT $name";
145 if (!empty($definition['primary'])) {
146 $query .= ' PRIMARY KEY';
147 } elseif (!empty($definition['unique'])) {
148 $query .= ' UNIQUE';
149 }
150 $fields = array();
151 foreach (array_keys($definition['fields']) as $field) {
152 $fields[] = $db->quoteIdentifier($field, true);
153 }
154 $query .= ' (' . implode(', ', $fields) . ')';
155
156 return $this->pdo->exec($query);
157 }
158
159
166 public function createSequence($seq_name, $start = 1, $options = array())
167 {
168 $sequence_name = $this->db_instance->quoteIdentifier($this->db_instance->getSequenceName($seq_name));
169 $seqcol_name = $this->db_instance->quoteIdentifier(ilDBConstants::SEQUENCE_COLUMNS_NAME);
170
171 $options_strings = array();
172
173 if (!empty($options['comment'])) {
174 $options_strings['comment'] = 'COMMENT = ' . $this->db_instance->quote($options['comment'], 'text');
175 }
176
177 if (!empty($options['charset'])) {
178 $options_strings['charset'] = 'DEFAULT CHARACTER SET ' . $options['charset'];
179 if (!empty($options['collate'])) {
180 $options_strings['charset'] .= ' COLLATE ' . $options['collate'];
181 }
182 }
183
184 $type = false;
185 if (!empty($options['type'])) {
186 $type = $options['type'];
187 }
188 if ($type) {
189 $options_strings[] = "ENGINE = $type";
190 }
191
192 $query = "CREATE TABLE $sequence_name ($seqcol_name INT NOT NULL AUTO_INCREMENT, PRIMARY KEY ($seqcol_name))";
193
194 if (!empty($options_strings)) {
195 $query .= ' ' . implode(' ', $options_strings);
196 }
197 $this->pdo->exec($query);
198
199 if ($start == 1) {
200 return true;
201 }
202
203 $query = "INSERT INTO $sequence_name ($seqcol_name) VALUES (" . ($start - 1) . ')';
204 $this->pdo->exec($query);
205
206 return true;
207 }
208
209
217 public function alterTable($name, $changes, $check)
218 {
219 $db = $this->db_instance;
220
221 foreach ($changes as $change_name => $change) {
222 switch ($change_name) {
223 case 'add':
224 case 'remove':
225 case 'change':
226 case 'rename':
227 case 'name':
228 break;
229 default:
230 throw new ilDatabaseException('change type "' . $change_name . '" not yet supported');
231 }
232 }
233
234 if ($check) {
235 return true;
236 }
237
238 $query = '';
239 if (!empty($changes['name'])) {
240 $change_name = $db->quoteIdentifier($changes['name']);
241 $query .= 'RENAME TO ' . $change_name;
242 }
243
244 if (!empty($changes['add']) && is_array($changes['add'])) {
245 foreach ($changes['add'] as $field_name => $field) {
246 if ($query) {
247 $query .= ', ';
248 }
249 $query .= 'ADD ' . $db->getFieldDefinition()->getDeclaration($field['type'], $field_name, $field);
250 }
251 }
252
253 if (!empty($changes['remove']) && is_array($changes['remove'])) {
254 foreach ($changes['remove'] as $field_name => $field) {
255 if ($query) {
256 $query .= ', ';
257 }
258 $field_name = $db->quoteIdentifier($field_name);
259 $query .= 'DROP ' . $field_name;
260 }
261 }
262
263 $rename = array();
264 if (!empty($changes['rename']) && is_array($changes['rename'])) {
265 foreach ($changes['rename'] as $field_name => $field) {
266 $rename[$field['name']] = $field_name;
267 }
268 }
269
270 if (!empty($changes['change']) && is_array($changes['change'])) {
271 foreach ($changes['change'] as $field_name => $field) {
272 if ($query) {
273 $query .= ', ';
274 }
275 if (isset($rename[$field_name])) {
276 $old_field_name = $rename[$field_name];
277 unset($rename[$field_name]);
278 } else {
279 $old_field_name = $field_name;
280 }
281 $old_field_name = $db->quoteIdentifier($old_field_name);
282 $query .= "CHANGE $old_field_name " . $this->db_instance->getFieldDefinition()
283 ->getDeclaration($field['definition']['type'], $field_name, $field['definition']);
284 }
285 }
286
287 if (!empty($rename) && is_array($rename)) {
288 foreach ($rename as $rename_name => $renamed_field) {
289 if ($query) {
290 $query .= ', ';
291 }
292 $field = $changes['rename'][$renamed_field];
293 $renamed_field = $db->quoteIdentifier($renamed_field);
294 $query .= 'CHANGE ' . $renamed_field . ' ' . $this->db_instance->getFieldDefinition()
295 ->getDeclaration($field['definition']['type'], $field['name'], $field['definition']);
296 }
297 }
298
299 if (!$query) {
300 return true;
301 }
302
303 $name = $db->quoteIdentifier($name, true);
304
305 $statement = "ALTER TABLE $name $query";
306
307 return $this->pdo->exec($statement);
308 }
309
310
317 public function createTable($name, $fields, $options = array())
318 {
319 $options['type'] = $this->db_instance->getStorageEngine();
320
321 return $this->pdo->exec($this->getQueryUtils()->createTable($name, $fields, $options));
322 }
323
324
325
326
327
328 //
329 // ilDBPdoManagerInterface
330 //
335 public function getIndexName($idx)
336 {
337 return $this->db_instance->getIndexName($idx);
338 }
339
340
345 public function getSequenceName($sqn)
346 {
347 return $this->db_instance->getSequenceName($sqn);
348 }
349
350
356 public function listTableFields($table)
357 {
358 $table = $this->db_instance->quoteIdentifier($table);
359 $query = "SHOW COLUMNS FROM $table";
360 $result = $this->db_instance->query($query);
361 $return = array();
362 while ($data = $this->db_instance->fetchObject($result)) {
363 $return[] = $data->Field;
364 }
365
366 return $return;
367 }
368
369
376 {
377 $key_name = 'Key_name';
378 $non_unique = 'Non_unique';
379
380 $db = $this->getDBInstance();
381 if ($db->options['portability']) {
382 if ($db->options['field_case'] == CASE_LOWER) {
383 $key_name = strtolower($key_name);
384 $non_unique = strtolower($non_unique);
385 } else {
386 $key_name = strtoupper($key_name);
387 $non_unique = strtoupper($non_unique);
388 }
389 }
390
391 $table = $this->db_instance->quoteIdentifier($table);
392 $query = "SHOW INDEX FROM $table";
393 $result_set = $this->db_instance->query($query);
394
395 $result = array();
396 while ($index_data = $this->db_instance->fetchAssoc($result_set)) {
397 if (!$index_data[$non_unique]) {
398 if ($index_data[$key_name] !== 'PRIMARY') {
399 $index = $this->fixIndexName($index_data[$key_name]);
400 } else {
401 $index = 'PRIMARY';
402 }
403 if (!empty($index)) {
404 $index = strtolower($index);
405 $result[$index] = true;
406 }
407 }
408 }
409
410 if ($this->db_instance->options['portability']) {
411 $result = array_change_key_case($result, $this->db_instance->options['field_case']);
412 }
413
414 return array_keys($result);
415 }
416
417
423 public function listTableIndexes($table)
424 {
425 $key_name = 'Key_name';
426 $non_unique = 'Non_unique';
427 if ($this->db_instance->options['portability']) {
428 if ($this->db_instance->options['field_case'] == CASE_LOWER) {
429 $key_name = strtolower($key_name);
430 $non_unique = strtolower($non_unique);
431 } else {
432 $key_name = strtoupper($key_name);
433 $non_unique = strtoupper($non_unique);
434 }
435 }
436
437 $table = $this->db_instance->quoteIdentifier($table);
438 $query = "SHOW INDEX FROM $table";
439 $result_set = $this->db_instance->query($query);
440 $indexes = array();
441 while ($index_data = $this->db_instance->fetchAssoc($result_set)) {
442 $indexes[] = $index_data;
443 }
444 $result = array();
445 foreach ($indexes as $index_data) {
446 if ($index_data[$non_unique] && ($index = $this->fixIndexName($index_data[$key_name]))) {
447 $result[$index] = true;
448 }
449 }
450
451 if ($this->db_instance->options['portability']) {
452 $result = array_change_key_case($result, $this->db_instance->options['field_case']);
453 }
454
455 return array_keys($result);
456 }
457
458
463 protected function fixIndexName($idx)
464 {
465 $idx_pattern = '/^' . preg_replace('/%s/', '([a-z0-9_]+)', ilDBPdoFieldDefinition::INDEX_FORMAT) . '$/i';
466 $idx_name = preg_replace($idx_pattern, '\\1', $idx);
467 if ($idx_name && !strcasecmp($idx, $this->db_instance->getIndexName($idx_name))) {
468 return $idx_name;
469 }
470
471 return $idx;
472 }
473
474
481 public function createIndex($table, $name, $definition)
482 {
483 $table = $this->db_instance->quoteIdentifier($table, true);
484 $name = $this->db_instance->quoteIdentifier($this->db_instance->getIndexName($name), true);
485 $query = "CREATE INDEX $name ON $table";
486 $fields = array();
487 foreach ($definition['fields'] as $field => $fieldinfo) {
488 if (!empty($fieldinfo['length'])) {
489 $fields[] = $this->db_instance->quoteIdentifier($field, true) . '(' . $fieldinfo['length'] . ')';
490 } else {
491 $fields[] = $this->db_instance->quoteIdentifier($field, true);
492 }
493 }
494 $query .= ' (' . implode(', ', $fields) . ')';
495
496 return $this->pdo->exec($query);
497 }
498
499
505 public function dropIndex($table, $name)
506 {
507 $table = $this->db_instance->quoteIdentifier($table, true);
508 $name = $this->db_instance->quoteIdentifier($this->db_instance->getIndexName($name), true);
509
510 return $this->pdo->exec("DROP INDEX $name ON $table");
511 }
512
513
518 public function dropSequence($table_name)
519 {
520 $sequence_name = $this->db_instance->quoteIdentifier($this->db_instance->getSequenceName($table_name));
521
522 return $this->pdo->exec("DROP TABLE $sequence_name");
523 }
524
525
533 public function getTableCreationQuery($name, $fields, $options = array())
534 {
535 return $this->getQueryUtils()->createTable($name, $fields, $options);
536 }
537
538
545 public function dropConstraint($table, $name, $primary = false)
546 {
547 $db = $this->getDBInstance();
548 $table = $db->quoteIdentifier($table, true);
549 if ($primary || strtolower($name) == 'primary') {
550 $query = "ALTER TABLE $table DROP PRIMARY KEY";
551 } else {
552 $name = $db->quoteIdentifier($db->getIndexName($name), true);
553 $query = "ALTER TABLE $table DROP INDEX $name";
554 }
555
556 return $this->pdo->exec($query);
557 }
558
559
563 public function dropTable($name)
564 {
565 $db = $this->getDBInstance();
566
567 $name = $db->quoteIdentifier($name, true);
568
569 return $db->manipulate("DROP TABLE $name");
570 }
571}
$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.
Class ilDBPdoManager.
fixSequenceName($sqn, $check=false)
listTables($database=null)
createTable($name, $fields, $options=array())
__construct(\PDO $pdo, ilDBPdo $db_instance)
ilDBPdoManager constructor.
createConstraint($table, $name, $definition)
getTableCreationQuery($name, $fields, $options=array())
alterTable($name, $changes, $check)
dropSequence($table_name)
dropConstraint($table, $name, $primary=false)
dropIndex($table, $name)
listSequences($database=null)
createSequence($seq_name, $start=1, $options=array())
createIndex($table, $name, $definition)
Class pdoDB.
Class ilDatabaseException.
Class ilMySQLQueryUtils.
$r
Definition: example_031.php:79
Interface ilDBManager.
Interface ilDBPdoManagerInterface.
if($format !==null) $name
Definition: metadata.php:146
$index
Definition: metadata.php:60
$query
$type
if(empty($password)) $table
Definition: pwgen.php:24
foreach($_POST as $key=> $value) $res