ILIAS  trunk Revision v11.0_alpha-3011-gc6b235a2e85
ilDBPdoManager Class Reference

Class ilDBPdoManager. More...

+ Inheritance diagram for ilDBPdoManager:
+ Collaboration diagram for ilDBPdoManager:

Public Member Functions

 __construct (protected \PDO $pdo, protected \ilDBPdo $db_instance)
 ilDBPdoManager constructor. More...
 
 getQueryUtils ()
 
 getDBInstance ()
 
 listTables (?string $database=null)
 
 listSequences (?string $database=null)
 
 createConstraint (string $table, string $name, array $definition)
 
 createSequence (string $seq_name, int $start=1, array $options=[])
 
 alterTable (string $name, array $changes, bool $check)
 
 createTable (string $name, array $fields, array $options=[])
 
 getIndexName (string $idx)
 
 getSequenceName (string $sqn)
 
 listTableFields (string $table)
 
 listTableConstraints (string $table)
 
 listTableIndexes (string $table)
 
 createIndex (string $table, string $name, array $definition)
 
 dropIndex (string $table, string $name)
 
 dropSequence (string $seq_name)
 
 getTableCreationQuery (string $name, array $fields, array $options=[])
 
 dropConstraint (string $table, string $name, bool $primary=false)
 
 dropTable (string $name)
 
 addForeignKey (string $foreign_key_name, array $field_names, string $table_name, array $reference_field_names, string $reference_table, ?ForeignKeyConstraints $on_update=null, ?ForeignKeyConstraints $on_delete=null)
 
 dropForeignKey (string $foreign_key_name, string $table_name)
 
 foreignKeyExists (string $foreign_key_name, string $table_name)
 
 listTables (?string $database=null)
 
 listSequences (?string $database=null)
 
 createConstraint (string $table, string $name, array $definition)
 
 listTableFields (string $table)
 
 listTableConstraints (string $table)
 
 createSequence (string $seq_name, int $start=1, array $options=[])
 
 listTableIndexes (string $table)
 
 alterTable (string $name, array $changes, bool $check)
 
 createIndex (string $table, string $name, array $definition)
 
 dropIndex (string $table, string $name)
 
 dropSequence (string $seq_name)
 
 dropConstraint (string $table, string $name, bool $primary=false)
 
 dropTable (string $name)
 
 getIndexName (string $idx)
 
 getSequenceName (string $sqn)
 

Protected Member Functions

 fixSequenceName (string $sqn, bool $check=false)
 
 fixIndexName (string $idx)
 

Protected Attributes

ilQueryUtils $query_utils = null
 

Detailed Description

Constructor & Destructor Documentation

◆ __construct()

ilDBPdoManager::__construct ( protected \PDO  $pdo,
protected \ilDBPdo  $db_instance 
)

ilDBPdoManager constructor.

Definition at line 34 of file class.ilDBPdoManager.php.

35 {
36 }

Member Function Documentation

◆ addForeignKey()

ilDBPdoManager::addForeignKey ( string  $foreign_key_name,
array  $field_names,
string  $table_name,
array  $reference_field_names,
string  $reference_table,
?ForeignKeyConstraints  $on_update = null,
?ForeignKeyConstraints  $on_delete = null 
)
Parameters
string[]$field_names
string[]$reference_field_names

Definition at line 478 of file class.ilDBPdoManager.php.

486 : bool {
487 $table = $this->db_instance->quoteIdentifier($table_name, true);
488 $reference_table = $this->db_instance->quoteIdentifier($reference_table, true);
489 $field_names = implode(",", $field_names);
490 $field_names = $this->db_instance->quoteIdentifier($field_names, true);
491 $reference_field_names = implode(",", $reference_field_names);
492 $reference_field_names = $this->db_instance->quoteIdentifier($reference_field_names, true);
493 $foreign_key_name = $this->db_instance->quoteIdentifier($foreign_key_name, true);
494 $update = '';
495 if ($on_update !== null) {
496 $on_update = $on_update->value;
497 $update = "ON UPDATE $on_update";
498 }
499 $delete = '';
500 if ($on_delete !== null) {
501 $on_delete = $on_delete->value;
502 $delete = "ON DELETE $on_delete";
503 }
504 $query = "ALTER TABLE
505 $table ADD CONSTRAINT
506 $foreign_key_name FOREIGN KEY ($field_names)
507 REFERENCES $reference_table ($reference_field_names)
508 $update
509 $delete
510 ";
511
512 return (bool) $this->pdo->exec($query);
513 }

◆ alterTable()

ilDBPdoManager::alterTable ( string  $name,
array  $changes,
bool  $check 
)
Exceptions

ilDatabaseException

Implements ilDBManager.

Definition at line 181 of file class.ilDBPdoManager.php.

181 : bool
182 {
183 $db = $this->db_instance;
184
185 foreach (array_keys($changes) as $change_name) {
186 switch ($change_name) {
187 case 'add':
188 case 'remove':
189 case 'change':
190 case 'rename':
191 case 'name':
192 break;
193 default:
194 throw new ilDatabaseException('change type "' . $change_name . '" not yet supported');
195 }
196 }
197
198 if ($check) {
199 return true;
200 }
201
202 $query = '';
203 if (!empty($changes['name'])) {
204 $change_name = $db->quoteIdentifier($changes['name']);
205 $query .= 'RENAME TO ' . $change_name;
206 }
207
208 if (!empty($changes['add']) && is_array($changes['add'])) {
209 foreach ($changes['add'] as $field_name => $field) {
210 if ($query !== '') {
211 $query .= ', ';
212 }
213 $fd = $db->getFieldDefinition();
214 if ($fd !== null) {
215 $query .= 'ADD ' . $fd->getDeclaration($field['type'], $field_name, $field);
216 }
217 }
218 }
219
220 if (!empty($changes['remove']) && is_array($changes['remove'])) {
221 foreach (array_keys($changes['remove']) as $field_name) {
222 if ($query !== '') {
223 $query .= ', ';
224 }
225 $field_name = $db->quoteIdentifier($field_name);
226 $query .= 'DROP ' . $field_name;
227 }
228 }
229
230 $rename = [];
231 if (!empty($changes['rename']) && is_array($changes['rename'])) {
232 foreach ($changes['rename'] as $field_name => $field) {
233 $rename[$field['name']] = $field_name;
234 }
235 }
236
237 if (!empty($changes['change']) && is_array($changes['change'])) {
238 foreach ($changes['change'] as $field_name => $field) {
239 if ($query !== '') {
240 $query .= ', ';
241 }
242 if (isset($rename[$field_name])) {
243 $old_field_name = $rename[$field_name];
244 unset($rename[$field_name]);
245 } else {
246 $old_field_name = $field_name;
247 }
248 $old_field_name = $db->quoteIdentifier($old_field_name);
249 $fd = $this->db_instance->getFieldDefinition();
250 if ($fd !== null) {
251 $query .= "CHANGE $old_field_name " . $fd
252 ->getDeclaration(
253 $field['definition']['type'],
254 $field_name,
255 $field['definition']
256 );
257 }
258 }
259 }
260
261 if (!empty($rename) && is_array($rename)) {
262 foreach ($rename as $renamed_field) {
263 if ($query !== '') {
264 $query .= ', ';
265 }
266 $field = $changes['rename'][$renamed_field];
267 $renamed_field = $db->quoteIdentifier($renamed_field);
268 $fd = $this->db_instance->getFieldDefinition();
269 if ($fd !== null) {
270 $query .= 'CHANGE ' . $renamed_field . ' ' . $fd
271 ->getDeclaration(
272 $field['definition']['type'],
273 $field['name'],
274 $field['definition']
275 );
276 }
277 }
278 }
279
280 if ($query === '') {
281 return true;
282 }
283
284 $name = $db->quoteIdentifier($name, true);
285
286 $statement = "ALTER TABLE $name $query";
287
288 return (bool) $this->pdo->exec($statement);
289 }
$check
Definition: buildRTE.php:81
Class ilDatabaseException.

References $check.

◆ createConstraint()

ilDBPdoManager::createConstraint ( string  $table,
string  $name,
array  $definition 
)
Exceptions

ilDatabaseException

Implements ilDBManager.

Definition at line 114 of file class.ilDBPdoManager.php.

114 : bool
115 {
116 $db = $this->db_instance;
117
118 $table = $db->quoteIdentifier($table, true);
119 $name = $db->quoteIdentifier($db->getIndexName($name), true);
120 $query = "ALTER TABLE $table ADD CONSTRAINT $name";
121 if (!empty($definition['primary'])) {
122 $query .= ' PRIMARY KEY';
123 } elseif (!empty($definition['unique'])) {
124 $query .= ' UNIQUE';
125 }
126 $fields = [];
127 foreach (array_keys($definition['fields']) as $field) {
128 $fields[] = $db->quoteIdentifier($field, true);
129 }
130 $query .= ' (' . implode(', ', $fields) . ')';
131
132 return (bool) $this->pdo->exec($query);
133 }

Referenced by ilDBPdo\addUniqueConstraint().

+ Here is the caller graph for this function:

◆ createIndex()

ilDBPdoManager::createIndex ( string  $table,
string  $name,
array  $definition 
)

Implements ilDBManager.

Definition at line 411 of file class.ilDBPdoManager.php.

411 : bool
412 {
413 $table = $this->db_instance->quoteIdentifier($table, true);
414 $name = $this->db_instance->quoteIdentifier($this->db_instance->getIndexName($name), true);
415 $query = "CREATE INDEX $name ON $table";
416 $fields = [];
417 foreach ($definition['fields'] as $field => $fieldinfo) {
418 if (!empty($fieldinfo['length'])) {
419 $fields[] = $this->db_instance->quoteIdentifier($field, true) . '(' . $fieldinfo['length'] . ')';
420 } else {
421 $fields[] = $this->db_instance->quoteIdentifier($field, true);
422 }
423 }
424 $query .= ' (' . implode(', ', $fields) . ')';
425
426 return (bool) $this->pdo->exec($query);
427 }

◆ createSequence()

ilDBPdoManager::createSequence ( string  $seq_name,
int  $start = 1,
array  $options = [] 
)

Implements ilDBManager.

Definition at line 135 of file class.ilDBPdoManager.php.

135 : bool
136 {
137 $sequence_name = $this->db_instance->quoteIdentifier($this->db_instance->getSequenceName($seq_name));
138 $seqcol_name = $this->db_instance->quoteIdentifier(ilDBConstants::SEQUENCE_COLUMNS_NAME);
139
140 $options_strings = [];
141
142 if (!empty($options['comment'])) {
143 $options_strings['comment'] = 'COMMENT = ' . $this->db_instance->quote($options['comment'], 'text');
144 }
145
146 if (!empty($options['charset'])) {
147 $options_strings['charset'] = 'DEFAULT CHARACTER SET ' . $options['charset'];
148 if (!empty($options['collate'])) {
149 $options_strings['charset'] .= ' COLLATE ' . $options['collate'];
150 }
151 }
152
153 $type = false;
154 if (!empty($options['type'])) {
155 $type = $options['type'];
156 }
157 if ($type) {
158 $options_strings[] = "ENGINE = $type";
159 }
160
161 $query = "CREATE TABLE $sequence_name ($seqcol_name INT NOT NULL AUTO_INCREMENT, PRIMARY KEY ($seqcol_name))";
162
163 if (!empty($options_strings)) {
164 $query .= ' ' . implode(' ', $options_strings);
165 }
166 $this->pdo->exec($query);
167
168 if ($start == 1) {
169 return true;
170 }
171
172 $query = "INSERT INTO $sequence_name ($seqcol_name) VALUES (" . ($start - 1) . ')';
173 $this->pdo->exec($query);
174
175 return true;
176 }

References ilDBConstants\SEQUENCE_COLUMNS_NAME.

◆ createTable()

ilDBPdoManager::createTable ( string  $name,
array  $fields,
array  $options = [] 
)

Definition at line 291 of file class.ilDBPdoManager.php.

291 : bool
292 {
293 $options['type'] = $this->db_instance->getStorageEngine();
294
295 return (bool) $this->pdo->exec($this->getQueryUtils()->createTable($name, $fields, $options));
296 }
createTable(string $name, array $fields, array $options=[])

References createTable(), and getQueryUtils().

Referenced by createTable().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ dropConstraint()

ilDBPdoManager::dropConstraint ( string  $table,
string  $name,
bool  $primary = false 
)

Implements ilDBManager.

Definition at line 452 of file class.ilDBPdoManager.php.

452 : bool
453 {
454 $db = $this->getDBInstance();
455 $table = $db->quoteIdentifier($table, true);
456 if ($primary || strtolower($name) === 'primary') {
457 $query = "ALTER TABLE $table DROP PRIMARY KEY";
458 } else {
459 $name = $db->quoteIdentifier($db->getIndexName($name), true);
460 $query = "ALTER TABLE $table DROP INDEX $name";
461 }
462
463 return (bool) $this->pdo->exec($query);
464 }

References getDBInstance().

+ Here is the call graph for this function:

◆ dropForeignKey()

ilDBPdoManager::dropForeignKey ( string  $foreign_key_name,
string  $table_name 
)

Definition at line 515 of file class.ilDBPdoManager.php.

515 : bool
516 {
517 $table = $this->db_instance->quoteIdentifier($table_name, true);
518 $name = $this->db_instance->quoteIdentifier($foreign_key_name, true);
519 $query = "ALTER TABLE $table DROP FOREIGN KEY $name;";
520
521 return (bool) $this->pdo->exec($query);
522 }

◆ dropIndex()

ilDBPdoManager::dropIndex ( string  $table,
string  $name 
)

Implements ilDBManager.

Definition at line 429 of file class.ilDBPdoManager.php.

429 : bool
430 {
431 $table = $this->db_instance->quoteIdentifier($table, true);
432 $name = $this->db_instance->quoteIdentifier($this->db_instance->getIndexName($name), true);
433
434 return (bool) $this->pdo->exec("DROP INDEX $name ON $table");
435 }

◆ dropSequence()

ilDBPdoManager::dropSequence ( string  $seq_name)

Implements ilDBManager.

Definition at line 437 of file class.ilDBPdoManager.php.

437 : bool
438 {
439 $sequence_name = $this->db_instance->quoteIdentifier($this->db_instance->getSequenceName($seq_name));
440
441 return (bool) $this->pdo->exec("DROP TABLE $sequence_name");
442 }

◆ dropTable()

ilDBPdoManager::dropTable ( string  $name)
Parameters
$namestring

Implements ilDBManager.

Definition at line 466 of file class.ilDBPdoManager.php.

466 : bool
467 {
468 $db = $this->getDBInstance();
469 $name = $db->quoteIdentifier($name, true);
470
471 return (bool) $this->pdo->exec("DROP TABLE $name");
472 }

References getDBInstance().

+ Here is the call graph for this function:

◆ fixIndexName()

ilDBPdoManager::fixIndexName ( string  $idx)
protected

Definition at line 400 of file class.ilDBPdoManager.php.

400 : string
401 {
402 $idx_pattern = '/^' . preg_replace('/%s/', '([a-z0-9_]+)', ilDBPdoFieldDefinition::INDEX_FORMAT) . '$/i';
403 $idx_name = preg_replace($idx_pattern, '\\1', $idx);
404 if ($idx_name && !strcasecmp($idx, $this->db_instance->getIndexName($idx_name))) {
405 return $idx_name;
406 }
407
408 return $idx;
409 }

References ilDBPdoFieldDefinition\INDEX_FORMAT.

Referenced by listTableConstraints(), and listTableIndexes().

+ Here is the caller graph for this function:

◆ fixSequenceName()

ilDBPdoManager::fixSequenceName ( string  $sqn,
bool  $check = false 
)
protected

Definition at line 71 of file class.ilDBPdoManager.php.

71 : string
72 {
73 $seq_pattern = '/^' . preg_replace('/%s/', '([a-z0-9_]+)', ilDBConstants::SEQUENCE_FORMAT) . '$/i';
74 $seq_name = preg_replace($seq_pattern, '\\1', $sqn);
75 if ($seq_name && !strcasecmp($sqn, $this->db_instance->getSequenceName($seq_name))) {
76 return $seq_name;
77 }
78
79 return $sqn;
80 }

References ilDBConstants\SEQUENCE_FORMAT.

Referenced by listSequences().

+ Here is the caller graph for this function:

◆ foreignKeyExists()

ilDBPdoManager::foreignKeyExists ( string  $foreign_key_name,
string  $table_name 
)

Definition at line 524 of file class.ilDBPdoManager.php.

524 : bool
525 {
526 $query = "SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY';";
527 $result_set = $this->db_instance->query($query);
528 while ($foreign_data = $this->db_instance->fetchAssoc($result_set)) {
529 if (array_key_exists(
530 'CONSTRAINT_NAME',
531 $foreign_data
532 ) && $foreign_data['CONSTRAINT_NAME'] === $foreign_key_name) {
533 return true;
534 }
535 }
536 return false;
537 }

◆ getDBInstance()

ilDBPdoManager::getDBInstance ( )

Definition at line 47 of file class.ilDBPdoManager.php.

47 : \ilDBPdo
48 {
49 return $this->db_instance;
50 }
Class pdoDB.

Referenced by dropConstraint(), dropTable(), and listTableConstraints().

+ Here is the caller graph for this function:

◆ getIndexName()

ilDBPdoManager::getIndexName ( string  $idx)

Implements ilDBPdoManagerInterface.

Definition at line 298 of file class.ilDBPdoManager.php.

298 : string
299 {
300 return $this->db_instance->getIndexName($idx);
301 }

◆ getQueryUtils()

ilDBPdoManager::getQueryUtils ( )

Definition at line 38 of file class.ilDBPdoManager.php.

39 {
40 if ($this->query_utils === null) {
41 $this->query_utils = new ilMySQLQueryUtils($this->db_instance);
42 }
43
44 return $this->query_utils;
45 }
ilQueryUtils $query_utils
Class ilMySQLQueryUtils.
Class ilQueryUtils.

References $query_utils.

Referenced by createTable(), and getTableCreationQuery().

+ Here is the caller graph for this function:

◆ getSequenceName()

ilDBPdoManager::getSequenceName ( string  $sqn)

Implements ilDBPdoManagerInterface.

Definition at line 303 of file class.ilDBPdoManager.php.

303 : string
304 {
305 return $this->db_instance->getSequenceName($sqn);
306 }

◆ getTableCreationQuery()

ilDBPdoManager::getTableCreationQuery ( string  $name,
array  $fields,
array  $options = [] 
)
Exceptions

ilDatabaseException

Definition at line 447 of file class.ilDBPdoManager.php.

447 : string
448 {
449 return $this->getQueryUtils()->createTable($name, $fields, $options);
450 }

References getQueryUtils().

+ Here is the call graph for this function:

◆ listSequences()

ilDBPdoManager::listSequences ( ?string  $database = null)
Returns
string[]

Implements ilDBManager.

Definition at line 85 of file class.ilDBPdoManager.php.

85 : array
86 {
87 $query = "SHOW TABLES LIKE '%_seq'";
88 if (!is_null($database)) {
89 $query .= " FROM $database";
90 }
91
92 $res = $this->db_instance->query($query);
93
94 $result = [];
95 while ($table_name = $this->db_instance->fetchAssoc($res)) {
96 $sqn = $this->fixSequenceName(reset($table_name), true);
97 if ($sqn !== '' && $sqn !== '0') {
98 $result[] = $sqn;
99 }
100 }
101 if ($this->db_instance->options['portability'] ?? null) {
102 return array_map(
103 ($this->db_instance->options['field_case'] === CASE_LOWER ? 'strtolower' : 'strtoupper'),
104 $result
105 );
106 }
107
108 return $result;
109 }
fixSequenceName(string $sqn, bool $check=false)
$res
Definition: ltiservices.php:69

References $res, and fixSequenceName().

+ Here is the call graph for this function:

◆ listTableConstraints()

ilDBPdoManager::listTableConstraints ( string  $table)
Returns
string[]

Implements ilDBManager.

Definition at line 324 of file class.ilDBPdoManager.php.

324 : array
325 {
326 $key_name = 'Key_name';
327 $non_unique = 'Non_unique';
328
329 $db = $this->getDBInstance();
330 if ($db->options['portability'] ?? null) {
331 if ($db->options['field_case'] == CASE_LOWER) {
332 $key_name = strtolower($key_name);
333 $non_unique = strtolower($non_unique);
334 } else {
335 $key_name = strtoupper($key_name);
336 $non_unique = strtoupper($non_unique);
337 }
338 }
339
340 $table = $this->db_instance->quoteIdentifier($table);
341 $query = "SHOW INDEX FROM $table";
342 $result_set = $this->db_instance->query($query);
343
344 $result = [];
345 while ($index_data = $this->db_instance->fetchAssoc($result_set)) {
346 if (!$index_data[$non_unique]) {
347 $index = $index_data[$key_name] !== 'PRIMARY' ? $this->fixIndexName($index_data[$key_name]) : 'PRIMARY';
348 if (!empty($index)) {
349 $index = strtolower($index);
350 $result[$index] = true;
351 }
352 }
353 }
354
355 if ($this->db_instance->options['portability'] ?? null) {
356 $result = array_change_key_case($result, $this->db_instance->options['field_case']);
357 }
358
359 return array_keys($result);
360 }
fixIndexName(string $idx)

References fixIndexName(), and getDBInstance().

+ Here is the call graph for this function:

◆ listTableFields()

ilDBPdoManager::listTableFields ( string  $table)
Returns
string[]

Implements ilDBManager.

Definition at line 308 of file class.ilDBPdoManager.php.

308 : array
309 {
310 $table = $this->db_instance->quoteIdentifier($table);
311 $query = "SHOW COLUMNS FROM $table";
312 $result = $this->db_instance->query($query);
313 $return = [];
314 while ($data = $this->db_instance->fetchObject($result)) {
315 $return[] = $data->Field;
316 }
317
318 return $return;
319 }

References $data.

◆ listTableIndexes()

ilDBPdoManager::listTableIndexes ( string  $table)
Returns
string[]

Implements ilDBManager.

Definition at line 365 of file class.ilDBPdoManager.php.

365 : array
366 {
367 $key_name = 'Key_name';
368 $non_unique = 'Non_unique';
369 if ($this->db_instance->options['portability'] ?? null) {
370 if ($this->db_instance->options['field_case'] == CASE_LOWER) {
371 $key_name = strtolower($key_name);
372 $non_unique = strtolower($non_unique);
373 } else {
374 $key_name = strtoupper($key_name);
375 $non_unique = strtoupper($non_unique);
376 }
377 }
378
379 $table = $this->db_instance->quoteIdentifier($table);
380 $query = "SHOW INDEX FROM $table";
381 $result_set = $this->db_instance->query($query);
382 $indexes = [];
383 while ($index_data = $this->db_instance->fetchAssoc($result_set)) {
384 $indexes[] = $index_data;
385 }
386 $result = [];
387 foreach ($indexes as $index_data) {
388 if ($index_data[$non_unique] && ($index = $this->fixIndexName($index_data[$key_name]))) {
389 $result[$index] = true;
390 }
391 }
392
393 if ($this->db_instance->options['portability'] ?? null) {
394 $result = array_change_key_case($result, $this->db_instance->options['field_case']);
395 }
396
397 return array_keys($result);
398 }

References fixIndexName().

+ Here is the call graph for this function:

◆ listTables()

ilDBPdoManager::listTables ( ?string  $database = null)
Returns
int[]|string[]

Implements ilDBManager.

Definition at line 55 of file class.ilDBPdoManager.php.

55 : array
56 {
57 $str = 'SHOW TABLES ' . ($database ? ' IN ' . $database : '');
58 $r = $this->pdo->query($str);
59 $tables = [];
60
61 $sequence_identifier = "_seq";
62 while ($data = $r->fetchColumn()) {
63 if (!preg_match("/$sequence_identifier$/um", $data)) {
64 $tables[] = $data;
65 }
66 }
67
68 return $tables;
69 }

References $data.

Field Documentation

◆ $query_utils

ilQueryUtils ilDBPdoManager::$query_utils = null
protected

Definition at line 29 of file class.ilDBPdoManager.php.

Referenced by getQueryUtils().


The documentation for this class was generated from the following file: