ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
class.ilDBPdo.php
Go to the documentation of this file.
1<?php
2/* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */
3
10abstract class ilDBPdo implements ilDBInterface, ilDBPdoInterface
11{
12 const FEATURE_TRANSACTIONS = 'transactions';
13 const FEATURE_FULLTEXT = 'fulltext';
14 const FEATURE_SLAVE = 'slave';
18 protected $host = '';
22 protected $dbname = '';
26 protected $charset = 'utf8';
30 protected $username = '';
34 protected $password = '';
38 protected $port = 3306;
42 protected $pdo;
46 protected $manager;
50 protected $reverse;
54 protected $limit = null;
58 protected $offset = null;
62 protected $storage_engine = 'MyISAM';
66 protected $dsn = '';
70 protected $attributes = array(
71 // PDO::ATTR_EMULATE_PREPARES => true,
72 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
73 );
77 protected $db_type = '';
81 protected $error_code = 0;
86
87
93 public function connect($return_false_for_error = false)
94 {
95 $this->generateDSN();
96 try {
97 $options = $this->getAttributes();
98 $this->pdo = new PDO($this->getDSN(), $this->getUsername(), $this->getPassword(), $options);
99 $this->initHelpers();
100 $this->initSQLMode();
101 } catch (Exception $e) {
102 $this->error_code = $e->getCode();
103 if ($return_false_for_error) {
104 return false;
105 }
106 throw $e;
107 }
108
109 return ($this->pdo->errorCode() == PDO::ERR_NONE);
110 }
111
112
113 abstract public function initHelpers();
114
115
116 protected function initSQLMode()
117 {
118 }
119
120
124 protected function getAttributes()
125 {
127 foreach ($this->getAdditionalAttributes() as $k => $v) {
128 $options[$k] = $v;
129 }
130
131 return $options;
132 }
133
134
138 protected function getAdditionalAttributes()
139 {
140 return array();
141 }
142
143
147 public function getFieldDefinition()
148 {
150 }
151
152
157 {
158 $this->field_definition = $field_definition;
159 }
160
161
169 public function createDatabase($a_name, $a_charset = "utf8", $a_collation = "")
170 {
171 $this->setDbname(null);
172 $this->generateDSN();
173 $this->connect(true);
174 try {
175 return $this->query($this->manager->getQueryUtils()->createDatabase($a_name, $a_charset, $a_collation));
176 } catch (PDOException $e) {
177 return false;
178 }
179 }
180
181
185 public function getLastErrorCode()
186 {
187 if ($this->pdo instanceof PDO) {
188 return $this->pdo->errorCode();
189 }
190
191 return $this->error_code;
192 }
193
194
198 public function initFromIniFile($tmpClientIniFile = null)
199 {
200 global $DIC;
201
202 if ($tmpClientIniFile instanceof ilIniFile) {
203 $clientIniFile = $tmpClientIniFile;
204 } else {
205 $ilClientIniFile = null;
206 if ($DIC->offsetExists('ilClientIniFile')) {
207 $clientIniFile = $DIC['ilClientIniFile'];
208 } else {
209 throw new InvalidArgumentException('$tmpClientIniFile is not an instance of ilIniFile');
210 }
211 }
212
213 $this->setUsername($clientIniFile->readVariable("db", "user"));
214 $this->setHost($clientIniFile->readVariable("db", "host"));
215 $this->setPort((int) $clientIniFile->readVariable("db", "port"));
216 $this->setPassword($clientIniFile->readVariable("db", "pass"));
217 $this->setDbname($clientIniFile->readVariable("db", "name"));
218 $this->setDBType($clientIniFile->readVariable("db", "type"));
219
220 $this->generateDSN();
221 }
222
223
224 public function generateDSN()
225 {
226 $port = $this->getPort() ? ";port=" . $this->getPort() : "";
227 $dbname = $this->getDbname() ? ';dbname=' . $this->getDbname() : '';
228 $host = $this->getHost();
229 $charset = ';charset=' . $this->getCharset();
230 $this->dsn = 'mysql:host=' . $host . $port . $dbname . $charset;
231 }
232
233
238 public function quoteIdentifier($identifier, $check_option = false)
239 {
240 return '`' . $identifier . '`';
241 }
242
243
249 public function nextId($table_name)
250 {
251 $sequence_table_name = $table_name . '_seq';
252
253 $last_insert_id = $this->pdo->lastInsertId($table_name);
254 if ($last_insert_id) {
255 // return $last_insert_id;
256 }
257
258 if ($this->tableExists($sequence_table_name)) {
259 $stmt = $this->pdo->prepare("SELECT sequence FROM $sequence_table_name");
260 $stmt->execute();
261 $rows = $stmt->fetch(PDO::FETCH_ASSOC);
262 $stmt->closeCursor();
263 $next_id = $rows['sequence'] + 1;
264 $stmt = $this->pdo->prepare("DELETE FROM $sequence_table_name");
265 $stmt->execute(array("next_id" => $next_id));
266 $stmt = $this->pdo->prepare("INSERT INTO $sequence_table_name (sequence) VALUES (:next_id)");
267 $stmt->execute(array("next_id" => $next_id));
268
269 return $next_id;
270 }
271
272 return 1;
273 }
274
275
284 public function createTable($table_name, $fields, $drop_table = false, $ignore_erros = false)
285 {
286 // check table name
287 if (!$this->checkTableName($table_name) && !$ignore_erros) {
288 throw new ilDatabaseException("ilDB Error: createTable(" . $table_name . ")");
289 }
290
291 // check definition array
292 if (!$this->checkTableColumns($fields) && !$ignore_erros) {
293 throw new ilDatabaseException("ilDB Error: createTable(" . $table_name . ")");
294 }
295
296 if ($drop_table) {
297 $this->dropTable($table_name, false);
298 }
299
300 return $this->manager->createTable($table_name, $fields, array());
301 }
302
303
308 protected function checkTableColumns($a_cols)
309 {
310 foreach ($a_cols as $col => $def) {
311 if (!$this->checkColumn($col, $def)) {
312 return false;
313 }
314 }
315
316 return true;
317 }
318
319
325 protected function checkColumn($a_col, $a_def)
326 {
327 if (!$this->checkColumnName($a_col)) {
328 return false;
329 }
330
331 if (!$this->checkColumnDefinition($a_def)) {
332 return false;
333 }
334
335 return true;
336 }
337
338
344 protected function checkColumnDefinition($a_def, $a_modify_mode = false)
345 {
346 return $this->field_definition->checkColumnDefinition($a_def);
347 }
348
349
354 public function checkColumnName($a_name)
355 {
356 return $this->field_definition->checkColumnName($a_name);
357 }
358
359
366 public function addPrimaryKey($table_name, $primary_keys)
367 {
368 assert(is_array($primary_keys));
369
370 $fields = array();
371 foreach ($primary_keys as $f) {
372 $fields[$f] = array();
373 }
374 $definition = array(
375 'primary' => true,
376 'fields' => $fields,
377 );
378 $this->manager->createConstraint($table_name, $this->constraintName($table_name, $this->getPrimaryKeyIdentifier()), $definition);
379
380 return true;
381 }
382
383
390 public function dropIndexByFields($table_name, $fields)
391 {
392 foreach ($this->manager->listTableIndexes($table_name) as $idx_name) {
393 $def = $this->reverse->getTableIndexDefinition($table_name, $idx_name);
394 $idx_fields = array_keys((array) $def['fields']);
395
396 if ($idx_fields === $fields) {
397 return $this->dropIndex($table_name, $idx_name);
398 }
399 }
400
401 return false;
402 }
403
404
408 public function getPrimaryKeyIdentifier()
409 {
410 return "PRIMARY";
411 }
412
413
418 public function createSequence($table_name, $start = 1)
419 {
420 $this->manager->createSequence($table_name, $start);
421 }
422
423
429 public function tableExists($table_name)
430 {
431 $result = $this->pdo->prepare("SHOW TABLES LIKE :table_name");
432 $result->execute(array('table_name' => $table_name));
433 $return = $result->rowCount();
434 $result->closeCursor();
435
436 return $return > 0;
437 }
438
439
446 public function tableColumnExists($table_name, $column_name)
447 {
448 $fields = $this->loadModule(ilDBConstants::MODULE_MANAGER)->listTableFields($table_name);
449
450 $in_array = in_array($column_name, $fields);
451
452 return $in_array;
453 }
454
455
463 public function addTableColumn($table_name, $column_name, $attributes)
464 {
465 if (!$this->checkColumnName($column_name)) {
466 throw new ilDatabaseException("ilDB Error: addTableColumn(" . $table_name . ", " . $column_name . ")");
467 }
468 if (!$this->checkColumnDefinition($attributes)) {
469 throw new ilDatabaseException("ilDB Error: addTableColumn(" . $table_name . ", " . $column_name . ")");
470 }
471
472 $changes = array(
473 "add" => array(
474 $column_name => $attributes,
475 ),
476 );
477
478 return $this->manager->alterTable($table_name, $changes, false);
479 }
480
481
488 public function dropTable($table_name, $error_if_not_existing = true)
489 {
490 $ilDBPdoManager = $this->loadModule(ilDBConstants::MODULE_MANAGER);
491 $tables = $ilDBPdoManager->listTables();
492 $table_exists = in_array($table_name, $tables);
493 if (!$table_exists && $error_if_not_existing) {
494 throw new ilDatabaseException("Table {$table_name} does not exist");
495 }
496
497 // drop sequence
498 $sequences = $ilDBPdoManager->listSequences();
499 if (in_array($table_name, $sequences)) {
500 $ilDBPdoManager->dropSequence($table_name);
501 }
502
503 // drop table
504 if ($table_exists) {
505 $ilDBPdoManager->dropTable($table_name);
506 }
507
508 return true;
509 }
510
511
518 public function query($query)
519 {
520 global $DIC;
521 $ilBench = $DIC['ilBench'];
522
523 $query = $this->appendLimit($query);
524
525 try {
526 if ($ilBench instanceof ilBenchmark) {
527 $ilBench->startDbBench($query);
528 }
529 $res = $this->pdo->query($query);
530 if ($ilBench instanceof ilBenchmark) {
531 $ilBench->stopDbBench();
532 }
533 } catch (PDOException $e) {
534 throw new ilDatabaseException($e->getMessage() . ' QUERY: ' . $query);
535 }
536
537 $err = $this->pdo->errorCode();
538 if ($err != PDO::ERR_NONE) {
539 $info = $this->pdo->errorInfo();
540 $info_message = $info[2];
541 throw new ilDatabaseException($info_message . ' QUERY: ' . $query);
542 }
543
544 return new ilPDOStatement($res);
545 }
546
547
553 public function fetchAll($query_result, $fetch_mode = ilDBConstants::FETCHMODE_ASSOC)
554 {
558 $return = array();
559 while ($data = $query_result->fetch($fetch_mode)) {
560 $return[] = $data;
561 }
562
563 return $return;
564 }
565
566
570 public function dropSequence($table_name)
571 {
572 $this->manager->dropSequence($table_name);
573 }
574
575
582 public function dropTableColumn($table_name, $column_name)
583 {
584 $changes = array(
585 "remove" => array(
586 $column_name => array(),
587 ),
588 );
589
590 return $this->manager->alterTable($table_name, $changes, false);
591 }
592
593
601 public function renameTableColumn($table_name, $column_old_name, $column_new_name)
602 {
603 // check table name
604 if (!$this->checkColumnName($column_new_name)) {
605 throw new ilDatabaseException("ilDB Error: renameTableColumn(" . $table_name . "," . $column_old_name . "," . $column_new_name . ")");
606 }
607
608 $def = $this->reverse->getTableFieldDefinition($table_name, $column_old_name);
609
610 $analyzer = new ilDBAnalyzer($this);
611 $best_alt = $analyzer->getBestDefinitionAlternative($def);
612 $def = $def[$best_alt];
613 unset($def["nativetype"]);
614 unset($def["mdb2type"]);
615
616 $f["definition"] = $def;
617 $f["name"] = $column_new_name;
618
619 $changes = array(
620 "rename" => array(
621 $column_old_name => $f,
622 ),
623 );
624
625 return $this->manager->alterTable($table_name, $changes, false);
626 }
627
628
634 public function insert($table_name, $values)
635 {
636 $real = array();
637 $fields = array();
638 foreach ($values as $key => $val) {
639 $real[] = $this->quote($val[1], $val[0]);
640 $fields[] = $this->quoteIdentifier($key);
641 }
642 $values = implode(",", $real);
643 $fields = implode(",", $fields);
644 $query = "INSERT INTO " . $table_name . " (" . $fields . ") VALUES (" . $values . ")";
645
647
648 return $this->pdo->exec($query);
649 }
650
651
657 public function fetchObject($query_result)
658 {
659 $res = $query_result->fetchObject();
660 if ($res == null) {
661 $query_result->closeCursor();
662
663 return null;
664 }
665
666 return $res;
667 }
668
669
676 public function update($table_name, $columns, $where)
677 {
678 $fields = array();
679 $field_values = array();
680 $placeholders = array();
681 $placeholders_full = array();
682 $types = array();
683 $values = array();
684 $lobs = false;
685 $lob = array();
686 foreach ($columns as $k => $col) {
687 $field_value = $col[1];
688 $fields[] = $k;
689 $placeholders[] = "%s";
690 $placeholders_full[] = ":$k";
691 $types[] = $col[0];
692
693 if ($col[0] == "blob" || $col[0] == "clob" || $col[0] == 'text') {
694 $field_value = $this->sanitizeMB4StringIfNotSupported($field_value);
695 }
696
697 // integer auto-typecast (this casts bool values to integer)
698 if ($col[0] == 'integer' && !is_null($field_value)) {
699 $field_value = (int) $field_value;
700 }
701
702 $values[] = $field_value;
703 $field_values[$k] = $field_value;
704 if ($col[0] == "blob" || $col[0] == "clob") {
705 $lobs = true;
706 $lob[$k] = $k;
707 }
708 }
709
710 if ($lobs) {
711 $q = "UPDATE " . $table_name . " SET ";
712 $lim = "";
713 foreach ($fields as $k => $field) {
714 $q .= $lim . $field . " = " . $placeholders_full[$k];
715 $lim = ", ";
716 }
717 $q .= " WHERE ";
718 $lim = "";
719 foreach ($where as $k => $col) {
720 $q .= $lim . $k . " = " . $this->quote($col[1], $col[0]);
721 $lim = " AND ";
722 }
723
724 $r = $this->prepareManip($q, $types);
725 $this->execute($r, $field_values);
726 $this->free($r);
727 } else {
728 foreach ($where as $k => $col) {
729 $types[] = $col[0];
730 $values[] = $col[1];
731 $field_values[$k] = $col;
732 }
733 $q = "UPDATE " . $table_name . " SET ";
734 $lim = "";
735 foreach ($fields as $k => $field) {
736 $q .= $lim . $this->quoteIdentifier($field) . " = " . $placeholders[$k];
737 $lim = ", ";
738 }
739 $q .= " WHERE ";
740 $lim = "";
741 foreach ($where as $k => $col) {
742 $q .= $lim . $k . " = %s";
743 $lim = " AND ";
744 }
745
746 $r = $this->manipulateF($q, $types, $values);
747 }
748
749 return $r;
750 }
751
752
753
759 public function manipulate($query)
760 {
761 global $DIC;
762 $ilBench = $DIC['ilBench'];
763 try {
765 if ($ilBench instanceof ilBenchmark) {
766 $ilBench->startDbBench($query);
767 }
768 $r = $this->pdo->exec($query);
769 if ($ilBench instanceof ilBenchmark) {
770 $ilBench->stopDbBench();
771 }
772 } catch (PDOException $e) {
773 throw new ilDatabaseException($e->getMessage() . ' QUERY: ' . $query);
774 }
775
776 return $r;
777 }
778
779
785 public function fetchAssoc($query_result)
786 {
787 $res = $query_result->fetch(PDO::FETCH_ASSOC);
788 if ($res == null) {
789 $query_result->closeCursor();
790
791 return null;
792 }
793
794 return $res;
795 }
796
797
803 public function numRows($query_result)
804 {
805 return $query_result->rowCount();
806 }
807
808
815 public function quote($value, $type = null)
816 {
817 if ($value === null) {
818 return 'NULL';
819 }
820
821 $pdo_type = PDO::PARAM_STR;
822 switch ($type) {
826 if ($value === '') {
827 return 'NULL';
828 }
829 break;
831 $value = (int) $value;
832
833 return $value;
834 break;
836 $pdo_type = PDO::PARAM_INT;
837 break;
839 default:
840 $pdo_type = PDO::PARAM_STR;
841 break;
842 }
843
844 return $this->pdo->quote($value, $pdo_type);
845 }
846
847
854 public function indexExistsByFields($table_name, $fields)
855 {
856 foreach ($this->manager->listTableIndexes($table_name) as $idx_name) {
857 $def = $this->reverse->getTableIndexDefinition($table_name, $idx_name);
858 $idx_fields = array_keys((array) $def['fields']);
859
860 if ($idx_fields === $fields) {
861 return true;
862 }
863 }
864
865 return false;
866 }
867
868
875 public function addIndex($table_name, $fields, $index_name = '', $fulltext = false)
876 {
877 assert(is_array($fields));
878 $this->field_definition->checkIndexName($index_name);
879
880 $definition_fields = array();
881 foreach ($fields as $f) {
882 $definition_fields[$f] = array();
883 }
884 $definition = array(
885 'fields' => $definition_fields,
886 );
887
888 if (!$fulltext) {
889 $this->manager->createIndex($table_name, $this->constraintName($table_name, $index_name), $definition);
890 } else {
891 if ($this->supportsFulltext()) {
892 $this->addFulltextIndex($table_name, $fields, $index_name); // TODO
893 }
894 }
895
896 return true;
897 }
898
899
907 public function addFulltextIndex($a_table, $a_fields, $a_name = "in")
908 {
909 $i_name = $this->constraintName($a_table, $a_name) . "_idx";
910 $f_str = implode(",", $a_fields);
911 $q = "ALTER TABLE $a_table ADD FULLTEXT $i_name ($f_str)";
912 $this->query($q);
913 }
914
915
919 public function dropFulltextIndex($a_table, $a_name)
920 {
921 $i_name = $this->constraintName($a_table, $a_name) . "_idx";
922 $this->query("ALTER TABLE $a_table DROP FULLTEXT $i_name");
923 }
924
925
929 public function isFulltextIndex($a_table, $a_name)
930 {
931 $set = $this->query("SHOW INDEX FROM " . $a_table);
932 while ($rec = $this->fetchAssoc($set)) {
933 if ($rec["Key_name"] == $a_name && $rec["Index_type"] == "FULLTEXT") {
934 return true;
935 }
936 }
937
938 return false;
939 }
940
941
946 public function getIndexName($index_name_base)
947 {
948 return sprintf(ilDBPdoFieldDefinition::INDEX_FORMAT, preg_replace('/[^a-z0-9_\$]/i', '_', $index_name_base));
949 }
950
951
956 public function getSequenceName($table_name)
957 {
958 return sprintf(ilDBPdoFieldDefinition::SEQUENCE_FORMAT, preg_replace('/[^a-z0-9_\$.]/i', '_', $table_name));
959 }
960
961
966 public function constraintName($a_table, $a_constraint)
967 {
968 return $a_constraint;
969 }
970
971
975 public function getDSN()
976 {
977 return $this->dsn;
978 }
979
980
984 public function getDBType()
985 {
986 return $this->db_type;
987 }
988
989
994 public function setDBType($type)
995 {
996 $this->db_type = $type;
997 }
998
999
1004 public static function getReservedWords()
1005 {
1006 global $DIC;
1007 $ilDB = $DIC->database();
1008
1012 return $ilDB->getFieldDefinition()->getReservedMysql();
1013 }
1014
1015
1020 public function lockTables($tables)
1021 {
1022 assert(is_array($tables));
1023
1024 $lock = $this->manager->getQueryUtils()->lock($tables);
1025 global $DIC;
1026 $ilLogger = $DIC->logger()->root();
1027 if ($ilLogger instanceof ilLogger) {
1028 $ilLogger->log('ilDB::lockTables(): ' . $lock);
1029 }
1030
1031 $this->pdo->exec($lock);
1032 }
1033
1034
1039 public function unlockTables()
1040 {
1041 $this->pdo->exec($this->manager->getQueryUtils()->unlock());
1042 }
1043
1044
1052 public function in($field, $values, $negate = false, $type = "")
1053 {
1054 return $this->manager->getQueryUtils()->in($field, $values, $negate, $type);
1055 }
1056
1057
1065 public function queryF($query, $types, $values)
1066 {
1067 if (!is_array($types) || !is_array($values) || count($types) != count($values)) {
1068 throw new ilDatabaseException("ilDB::queryF: Types and values must be arrays of same size. ($query)");
1069 }
1070 $quoted_values = array();
1071 foreach ($types as $k => $t) {
1072 $quoted_values[] = $this->quote($values[$k], $t);
1073 }
1074 $query = vsprintf($query, $quoted_values);
1075
1076 return $this->query($query);
1077 }
1078
1079
1087 public function manipulateF($query, $types, $values)
1088 {
1089 if (!is_array($types) || !is_array($values) || count($types) != count($values)) {
1090 throw new ilDatabaseException("ilDB::manipulateF: types and values must be arrays of same size. ($query)");
1091 }
1092 $quoted_values = array();
1093 foreach ($types as $k => $t) {
1094 $quoted_values[] = $this->quote($values[$k], $t);
1095 }
1096 $query = vsprintf($query, $quoted_values);
1097
1098 return $this->manipulate($query);
1099 }
1100
1101
1108 public function useSlave($bool)
1109 {
1110 return false;
1111 }
1112
1113
1120 public function setLimit($limit, $offset = 0)
1121 {
1122 $this->limit = $limit;
1123 $this->offset = $offset;
1124 }
1125
1126
1135 public function like($column, $type, $value = "?", $case_insensitive = true)
1136 {
1137 return $this->manager->getQueryUtils()->like($column, $type, $value, $case_insensitive);
1138 }
1139
1140
1144 public function now()
1145 {
1146 return $this->manager->getQueryUtils()->now();
1147 }
1148
1149
1158 public function replace($table, $primaryKeys, $otherColumns)
1159 {
1160 $a_columns = array_merge($primaryKeys, $otherColumns);
1161 $fields = array();
1162 $field_values = array();
1163 $placeholders = array();
1164 $types = array();
1165 $values = array();
1166
1167 foreach ($a_columns as $k => $col) {
1168 $fields[] = $k;
1169 $placeholders[] = "%s";
1170 $placeholders2[] = ":$k";
1171 $types[] = $col[0];
1172
1173 // integer auto-typecast (this casts bool values to integer)
1174 if ($col[0] == 'integer' && !is_null($col[1])) {
1175 $col[1] = (int) $col[1];
1176 }
1177
1178 $values[] = $col[1];
1179 $field_values[$k] = $col[1];
1180 }
1181
1182 $q = "REPLACE INTO " . $table . " (" . implode(",", $fields) . ") VALUES (" . implode(",", $placeholders) . ")";
1183
1184 $r = $this->manipulateF($q, $types, $values);
1185
1186 return $r;
1187 }
1188
1189
1197 public function equals($columns, $value, $type, $emptyOrNull = false)
1198 {
1199 if (!$emptyOrNull || $value != "") {
1200 return $columns . " = " . $this->quote($value, $type);
1201 } else {
1202 return "(" . $columns . " = '' OR $columns IS NULL)";
1203 }
1204 }
1205
1206
1210 public function getHost()
1211 {
1212 return $this->host;
1213 }
1214
1215
1219 public function setHost($host)
1220 {
1221 $this->host = $host;
1222 }
1223
1224
1228 public function getDbname()
1229 {
1230 return $this->dbname;
1231 }
1232
1233
1237 public function setDbname($dbname)
1238 {
1239 $this->dbname = $dbname;
1240 }
1241
1242
1246 public function getCharset()
1247 {
1248 return $this->charset;
1249 }
1250
1251
1255 public function setCharset($charset)
1256 {
1257 $this->charset = $charset;
1258 }
1259
1260
1264 public function getUsername()
1265 {
1266 return $this->username;
1267 }
1268
1269
1273 public function setUsername($username)
1274 {
1275 $this->username = $username;
1276 }
1277
1278
1282 public function getPassword()
1283 {
1284 return $this->password;
1285 }
1286
1287
1291 public function setPassword($password)
1292 {
1293 $this->password = $password;
1294 }
1295
1296
1300 public function getPort()
1301 {
1302 return $this->port;
1303 }
1304
1305
1309 public function setPort($port)
1310 {
1311 $this->port = $port;
1312 }
1313
1314
1318 public function setDBUser($user)
1319 {
1320 $this->setUsername($user);
1321 }
1322
1323
1327 public function setDBPort($port)
1328 {
1329 $this->setPort($port);
1330 }
1331
1332
1336 public function setDBPassword($password)
1337 {
1338 $this->setPassword($password);
1339 }
1340
1341
1345 public function setDBHost($host)
1346 {
1347 $this->setHost($host);
1348 }
1349
1350
1355 public function upper($a_exp)
1356 {
1357 return " UPPER(" . $a_exp . ") ";
1358 }
1359
1360
1365 public function lower($a_exp)
1366 {
1367 return " LOWER(" . $a_exp . ") ";
1368 }
1369
1370
1377 public function substr($a_exp, $a_pos = 1, $a_len = -1)
1378 {
1379 $lenstr = "";
1380 if ($a_len > -1) {
1381 $lenstr = ", " . $a_len;
1382 }
1383
1384 return " SUBSTR(" . $a_exp . ", " . $a_pos . $lenstr . ") ";
1385 }
1386
1387
1393 public function prepareManip($query, $types = null)
1394 {
1395 return new ilPDOStatement($this->pdo->prepare($query));
1396 }
1397
1398
1405 public function prepare($query, $types = null, $result_types = null)
1406 {
1407 return new ilPDOStatement($this->pdo->prepare($query));
1408 }
1409
1410
1414 public function enableResultBuffering($a_status)
1415 {
1416 $this->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, $a_status);
1417 }
1418
1419
1426 public function execute($stmt, $data = array())
1427 {
1431 $result = $stmt->execute($data);
1432 if ($result === false) {
1433 throw new ilDatabaseException(implode(', ', $stmt->errorInfo()), $stmt->errorCode());
1434 }
1435 return $stmt;
1436 }
1437
1438
1442 public function supportsSlave()
1443 {
1444 return false;
1445 }
1446
1447
1451 public function supportsFulltext()
1452 {
1453 return false;
1454 }
1455
1456
1460 public function supportsTransactions()
1461 {
1462 return false;
1463 }
1464
1465
1470 public function supports($feature)
1471 {
1472 switch ($feature) {
1474 return $this->supportsTransactions();
1476 return $this->supportsFulltext();
1478 return $this->supportsSlave();
1479 default:
1480 return false;
1481 }
1482 }
1483
1484
1488 public function listTables()
1489 {
1490 return $this->manager->listTables();
1491 }
1492
1493
1498 public function loadModule($module)
1499 {
1500 switch ($module) {
1502 return $this->manager;
1504 return $this->reverse;
1505 }
1506 }
1507
1508
1512 public function getAllowedAttributes()
1513 {
1514 return $this->field_definition->getAllowedAttributes();
1515 }
1516
1517
1522 public function sequenceExists($sequence)
1523 {
1524 return in_array($sequence, $this->listSequences());
1525 }
1526
1527
1531 public function listSequences()
1532 {
1533 return $this->manager->listSequences();
1534 }
1535
1536
1542 public function concat(array $values, $allow_null = true)
1543 {
1544 return $this->manager->getQueryUtils()->concat($values, $allow_null);
1545 }
1546
1547
1552 protected function appendLimit($query)
1553 {
1554 if ($this->limit !== null && $this->offset !== null) {
1555 $query .= ' LIMIT ' . (int) $this->offset . ', ' . (int) $this->limit;
1556 $this->limit = null;
1557 $this->offset = null;
1558
1559 return $query;
1560 }
1561
1562 return $query;
1563 }
1564
1565
1572 public function locate($a_needle, $a_string, $a_start_pos = 1)
1573 {
1574 return $this->manager->getQueryUtils()->locate($a_needle, $a_string, $a_start_pos);
1575 }
1576
1577
1585 public function modifyTableColumn($table, $a_column, $a_attributes)
1586 {
1587 $def = $this->reverse->getTableFieldDefinition($table, $a_column);
1588
1589 $analyzer = new ilDBAnalyzer($this);
1590 $best_alt = $analyzer->getBestDefinitionAlternative($def);
1591 $def = $def[$best_alt];
1592 unset($def["nativetype"]);
1593 unset($def["mdb2type"]);
1594
1595 // check attributes
1596 $ilDBPdoFieldDefinition = $this->field_definition;
1597
1598 $type = ($a_attributes["type"] != "") ? $a_attributes["type"] : $def["type"];
1599 foreach ($def as $k => $v) {
1600 if ($k != "type" && !$ilDBPdoFieldDefinition->isAllowedAttribute($k, $type)) {
1601 unset($def[$k]);
1602 }
1603 }
1604 $check_array = $def;
1605 foreach ($a_attributes as $k => $v) {
1606 $check_array[$k] = $v;
1607 }
1608 if (!$this->checkColumnDefinition($check_array, true)) {
1609 throw new ilDatabaseException("ilDB Error: modifyTableColumn(" . $table . ", " . $a_column . ")");
1610 }
1611
1612 foreach ($a_attributes as $a => $v) {
1613 $def[$a] = $v;
1614 }
1615
1616 $a_attributes["definition"] = $def;
1617
1618 $changes = array(
1619 "change" => array(
1620 $a_column => $a_attributes,
1621 ),
1622 );
1623
1624 return $this->manager->alterTable($table, $changes, false);
1625 }
1626
1627
1632 public function free($a_st)
1633 {
1637 return $a_st->closeCursor();
1638 }
1639
1640
1647 public function renameTable($a_name, $a_new_name)
1648 {
1649 // check table name
1650 try {
1651 $this->checkTableName($a_new_name);
1652 } catch (ilDatabaseException $e) {
1653 throw new ilDatabaseException("ilDB Error: renameTable(" . $a_name . "," . $a_new_name . ")<br />" . $e->getMessage());
1654 }
1655
1656 $this->manager->alterTable($a_name, array("name" => $a_new_name), false);
1657
1658 // The abstraction_progress is no longer used in ILIAS, see http://www.ilias.de/mantis/view.php?id=19513
1659 // $query = "UPDATE abstraction_progress " . "SET table_name = " . $this->quote($a_new_name, 'text') . " " . "WHERE table_name = "
1660 // . $this->quote($a_name, 'text');
1661 // $this->pdo->query($query);
1662
1663 return true;
1664 }
1665
1666
1672 public function checkTableName($a_name)
1673 {
1674 return $this->field_definition->checkTableName($a_name);
1675 }
1676
1677
1682 public static function isReservedWord($a_word)
1683 {
1684 require_once('./Services/Database/classes/PDO/FieldDefinition/class.ilDBPdoMySQLFieldDefinition.php');
1685 global $DIC;
1686 $ilDBPdoMySQLFieldDefinition = new ilDBPdoMySQLFieldDefinition($DIC->database());
1687
1688 return $ilDBPdoMySQLFieldDefinition->isReserved($a_word);
1689 }
1690
1691
1696 public function beginTransaction()
1697 {
1698 if (!$this->supports(self::FEATURE_TRANSACTIONS)) {
1699 throw new ilDatabaseException("ilDB::beginTransaction: Transactions are not supported.");
1700 }
1701
1702 return $this->pdo->beginTransaction();
1703 }
1704
1705
1710 public function commit()
1711 {
1712 if (!$this->supports(self::FEATURE_TRANSACTIONS)) {
1713 throw new ilDatabaseException("ilDB::beginTransaction: Transactions are not supported.");
1714 }
1715
1716 return $this->pdo->commit();
1717 }
1718
1719
1724 public function rollback()
1725 {
1726 if (!$this->supports(self::FEATURE_TRANSACTIONS)) {
1727 throw new ilDatabaseException("ilDB::beginTransaction: Transactions are not supported.");
1728 }
1729
1730 return $this->pdo->rollBack();
1731 }
1732
1733
1739 public function dropIndex($a_table, $a_name = "i1")
1740 {
1741 return $this->manager->dropIndex($a_table, $a_name);
1742 }
1743
1744
1749 {
1750 $this->storage_engine = $storage_engine;
1751 }
1752
1753
1757 public function getStorageEngine()
1758 {
1759 return $this->storage_engine;
1760 }
1761
1762
1769 public function queryCol($query, $type = PDO::FETCH_ASSOC, $colnum = 0)
1770 {
1771 switch ($type) {
1773 $type = PDO::FETCH_ASSOC;
1774 break;
1776 $type = PDO::FETCH_OBJ;
1777 break;
1778 default:
1779 $type = PDO::FETCH_ASSOC;
1780 break;
1781 }
1782
1783 return $this->pdo->query($query, PDO::FETCH_ASSOC)->fetchAll(PDO::FETCH_COLUMN, $colnum);
1784 }
1785
1786
1793 public function queryRow($query, $types = null, $fetchmode = ilDBConstants::FETCHMODE_DEFAULT)
1794 {
1795 switch ($fetchmode) {
1797 $type = PDO::FETCH_ASSOC;
1798 break;
1800 $type = PDO::FETCH_OBJ;
1801 break;
1802 default:
1803 $type = PDO::FETCH_ASSOC;
1804 break;
1805 }
1806
1807 return $this->pdo->query($query, $type)->fetch();
1808 }
1809
1810
1815 public function getServerVersion($native = false)
1816 {
1817 return $this->pdo->query('SELECT VERSION()')->fetchColumn();
1818 }
1819
1820
1826 public function escape($value, $escape_wildcards = false)
1827 {
1828 return $value;
1829 }
1830
1831
1836 public function escapePattern($text)
1837 {
1838 return $text;
1839 }
1840
1841
1847 {
1848 return array();
1849 }
1850
1851
1856 {
1857 return array();
1858 }
1859
1860
1865 {
1866 return false;
1867 }
1868
1869
1873 public function supportsEngineMigration()
1874 {
1875 return false;
1876 }
1877
1878
1884 public function checkIndexName($name)
1885 {
1886 return $this->getFieldDefinition()->checkIndexName($name);
1887 }
1888
1889
1897 public function addUniqueConstraint($table, $fields, $name = "con")
1898 {
1899 assert(is_array($fields));
1901
1902 // check index name
1903 if (!$this->checkIndexName($name)) {
1904 throw new ilDatabaseException("ilDB Error: addUniqueConstraint(" . $table . "," . $name . ")");
1905 }
1906
1907 $fields_corrected = array();
1908 foreach ($fields as $f) {
1909 $fields_corrected[$f] = array();
1910 }
1911 $definition = array(
1912 'unique' => true,
1913 'fields' => $fields_corrected,
1914 );
1915
1916 return $manager->createConstraint($table, $this->constraintName($table, $name), $definition);
1917 }
1918
1919
1925 public function dropUniqueConstraint($a_table, $a_name = "con")
1926 {
1927 return $this->manager->dropConstraint($a_table, $this->constraintName($a_table, $a_name), false);
1928 }
1929
1930
1936 public function dropUniqueConstraintByFields($a_table, $a_fields)
1937 {
1938 $analyzer = new ilDBAnalyzer();
1939 $cons = $analyzer->getConstraintsInformation($a_table);
1940 foreach ($cons as $c) {
1941 if ($c["type"] == "unique" && count($a_fields) == count($c["fields"])) {
1942 $all_in = true;
1943 foreach ($a_fields as $f) {
1944 if (!isset($c["fields"][$f])) {
1945 $all_in = false;
1946 }
1947 }
1948 if ($all_in) {
1949 return $this->dropUniqueConstraint($a_table, $c['name']);
1950 }
1951 }
1952 }
1953
1954 return false;
1955 }
1956
1957
1961 public function getLastInsertId()
1962 {
1963 return $this->pdo->lastInsertId();
1964 }
1965
1966
1970 public function buildAtomQuery()
1971 {
1972 require_once('./Services/Database/classes/Atom/class.ilAtomQueryLock.php');
1973
1974 return new ilAtomQueryLock($this);
1975 }
1976
1977
1983 public function uniqueConstraintExists($table, array $fields)
1984 {
1985 require_once('./Services/Database/classes/class.ilDBAnalyzer.php');
1986 $analyzer = new ilDBAnalyzer();
1987 $cons = $analyzer->getConstraintsInformation($table);
1988 foreach ($cons as $c) {
1989 if ($c["type"] == "unique" && count($fields) == count($c["fields"])) {
1990 $all_in = true;
1991 foreach ($fields as $f) {
1992 if (!isset($c["fields"][$f])) {
1993 $all_in = false;
1994 }
1995 }
1996 if ($all_in) {
1997 return true;
1998 }
1999 }
2000 }
2001
2002 return false;
2003 }
2004
2005
2010 public function dropPrimaryKey($table_name)
2011 {
2012 return $this->manager->dropConstraint($table_name, "PRIMARY", true);
2013 }
2014
2015
2020 public function executeMultiple($stmt, $a_data)
2021 {
2022 for ($i = 0, $j = count($a_data); $i < $j; $i++) {
2023 $stmt->execute($a_data[$i]);
2024 }
2025 }
2026
2027
2033 public function fromUnixtime($a_expr, $a_to_text = true)
2034 {
2035 return "FROM_UNIXTIME(" . $a_expr . ")";
2036 }
2037
2038
2042 public function unixTimestamp()
2043 {
2044 return "UNIX_TIMESTAMP()";
2045 }
2046
2047
2063 public function autoExecute($tablename, $fields, $mode = ilDBConstants::AUTOQUERY_INSERT, $where = false)
2064 {
2065 $fields_values = (array) $fields;
2066 if ($mode == ilDBConstants::AUTOQUERY_INSERT) {
2067 if (!empty($fields_values)) {
2068 $keys = $fields_values;
2069 } else {
2070 $keys = array();
2071 }
2072 } else {
2073 $keys = array_keys($fields_values);
2074 }
2075 $params = array_values($fields_values);
2076 if (empty($params)) {
2077 $query = $this->buildManipSQL($tablename, $keys, $mode, $where);
2078 $result = $this->pdo->query($query);
2079 } else {
2080 $stmt = $this->autoPrepare($tablename, $keys, $mode, $where, $types, $result_types);
2081 $this->execute($stmt);
2082 $this->free($stmt);
2083 $result = $stmt;
2084 }
2085
2086 return $result;
2087 }
2088
2089
2099 protected function autoPrepare($table, $table_fields, $mode = ilDBConstants::AUTOQUERY_INSERT, $where = false, $types = null, $result_types = ilDBConstants::PREPARE_MANIP)
2100 {
2101 $query = $this->buildManipSQL($table, $table_fields, $mode, $where);
2102
2103 return $this->prepare($query, $types, $result_types);
2104 }
2105
2106
2115 protected function buildManipSQL($table, $table_fields, $mode, $where = false)
2116 {
2117 if ($this->options['quote_identifier']) {
2118 $table = $this->quoteIdentifier($table);
2119 }
2120
2121 if (!empty($table_fields) && $this->options['quote_identifier']) {
2122 foreach ($table_fields as $key => $field) {
2123 $table_fields[$key] = $this->quoteIdentifier($field);
2124 }
2125 }
2126
2127 if ($where !== false && !is_null($where)) {
2128 if (is_array($where)) {
2129 $where = implode(' AND ', $where);
2130 }
2131 $where = ' WHERE ' . $where;
2132 }
2133
2134 switch ($mode) {
2136 if (empty($table_fields)) {
2137 throw new ilDatabaseException('Insert requires table fields');
2138 }
2139 $cols = implode(', ', $table_fields);
2140 $values = '?' . str_repeat(', ?', (count($table_fields) - 1));
2141
2142 return 'INSERT INTO ' . $table . ' (' . $cols . ') VALUES (' . $values . ')';
2143 break;
2145 if (empty($table_fields)) {
2146 throw new ilDatabaseException('Update requires table fields');
2147 }
2148 $set = implode(' = ?, ', $table_fields) . ' = ?';
2149 $sql = 'UPDATE ' . $table . ' SET ' . $set . $where;
2150
2151 return $sql;
2152 break;
2154 $sql = 'DELETE FROM ' . $table . $where;
2155
2156 return $sql;
2157 break;
2159 $cols = !empty($table_fields) ? implode(', ', $table_fields) : '*';
2160 $sql = 'SELECT ' . $cols . ' FROM ' . $table . $where;
2161
2162 return $sql;
2163 break;
2164 }
2165
2166 throw new ilDatabaseException('Syntax error');
2167 }
2168
2169
2174 public function getDBVersion()
2175 {
2176 $d = $this->fetchObject($this->query("SELECT VERSION() AS version"));
2177
2178 return ($d->version ? $d->version : 'Unknown');
2179 }
2180
2181
2186 {
2187 if (!$this->doesCollationSupportMB4Strings()) {
2188 $query_replaced = preg_replace(
2189 '/[\x{10000}-\x{10FFFF}]/u',
2191 $query
2192 );
2193 if (!empty($query_replaced)) {
2194 return $query_replaced;
2195 }
2196 }
2197
2198 return $query;
2199 }
2200
2205 {
2206 return false;
2207 }
2208
2209
2213 public function groupConcat($a_field_name, $a_seperator = ",", $a_order = null)
2214 {
2215 return $this->manager->getQueryUtils()->groupConcat($a_field_name, $a_seperator, $a_order);
2216 }
2217
2221 public function cast($a_field_name, $a_dest_type)
2222 {
2223 return $this->manager->getQueryUtils()->cast($a_field_name, $a_dest_type);
2224 }
2225}
$result
if(! $in) $columns
Definition: Utf8Test.php:45
An exception for terminatinating execution or to throw for unit testing.
Class ilAtomQueryLock.
performance measurement class
This class gives all kind of DB information using the database manager and reverse module.
Class ilDBPdoMySQLFieldDefinition.
Class pdoDB.
upper($a_exp)
supportsTransactions()
update($table_name, $columns, $where)
setUsername($username)
connect($return_false_for_error=false)
renameTable($a_name, $a_new_name)
dropUniqueConstraintByFields($a_table, $a_fields)
concat(array $values, $allow_null=true)
dropPrimaryKey($table_name)
queryRow($query, $types=null, $fetchmode=ilDBConstants::FETCHMODE_DEFAULT)
setDBType($type)
loadModule($module)
sanitizeMB4StringIfNotSupported($query)
string sanitized query
createSequence($table_name, $start=1)
indexExistsByFields($table_name, $fields)
getFieldDefinition()
equals($columns, $value, $type, $emptyOrNull=false)
escape($value, $escape_wildcards=false)
lockTables($tables)
query($query)
quote($value, $type=null)
setStorageEngine($storage_engine)
like($column, $type, $value="?", $case_insensitive=true)
setDBHost($host)
setFieldDefinition($field_definition)
in($field, $values, $negate=false, $type="")
setDBPassword($password)
groupConcat($a_field_name, $a_seperator=",", $a_order=null)
string
prepare($query, $types=null, $result_types=null)
prepareManip($query, $types=null)
addIndex($table_name, $fields, $index_name='', $fulltext=false)
setDBUser($user)
dropIndex($a_table, $a_name="i1")
getLastErrorCode()
setPort($port)
supportsEngineMigration()
checkIndexName($name)
dropUniqueConstraint($a_table, $a_name="con")
getPrimaryKeyIdentifier()
getAllowedAttributes()
array
setPassword($password)
checkTableName($a_name)
executeMultiple($stmt, $a_data)
isFulltextIndex($a_table, $a_name)
Is index a fulltext index?
queryF($query, $types, $values)
getSequenceName($table_name)
tableExists($table_name)
createTable($table_name, $fields, $drop_table=false, $ignore_erros=false)
initFromIniFile($tmpClientIniFile=null)
addUniqueConstraint($table, $fields, $name="con")
getAdditionalAttributes()
supports($feature)
autoExecute($tablename, $fields, $mode=ilDBConstants::AUTOQUERY_INSERT, $where=false)
Generate an insert, update or delete query and call prepare() and execute() on it.
checkColumnName($a_name)
appendLimit($query)
const FEATURE_SLAVE
supportsCollationMigration()
@inheritDoc
manipulateF($query, $types, $values)
checkTableColumns($a_cols)
replace($table, $primaryKeys, $otherColumns)
Replace into method.
static isReservedWord($a_word)
manipulate($query)
numRows($query_result)
checkColumn($a_col, $a_def)
addFulltextIndex($a_table, $a_fields, $a_name="in")
buildManipSQL($table, $table_fields, $mode, $where=false)
uniqueConstraintExists($table, array $fields)
locate($a_needle, $a_string, $a_start_pos=1)
setDBPort($port)
autoPrepare($table, $table_fields, $mode=ilDBConstants::AUTOQUERY_INSERT, $where=false, $types=null, $result_types=ilDBConstants::PREPARE_MANIP)
constraintName($a_table, $a_constraint)
Determine contraint name by table name and constraint name.
escapePattern($text)
dropTableColumn($table_name, $column_name)
migrateAllTablesToEngine($engine=ilDBConstants::MYSQL_ENGINE_INNODB)
fetchAssoc($query_result)
fromUnixtime($a_expr, $a_to_text=true)
substr($a_exp, $a_pos=1, $a_len=-1)
getIndexName($index_name_base)
addPrimaryKey($table_name, $primary_keys)
dropTable($table_name, $error_if_not_existing=true)
doesCollationSupportMB4Strings()
@inheritDoc
createDatabase($a_name, $a_charset="utf8", $a_collation="")
const FEATURE_TRANSACTIONS
sequenceExists($sequence)
renameTableColumn($table_name, $column_old_name, $column_new_name)
getServerVersion($native=false)
dropSequence($table_name)
insert($table_name, $values)
tableColumnExists($table_name, $column_name)
cast($a_field_name, $a_dest_type)
string;
lower($a_exp)
const FEATURE_FULLTEXT
setDbname($dbname)
quoteIdentifier($identifier, $check_option=false)
checkColumnDefinition($a_def, $a_modify_mode=false)
setCharset($charset)
dropFulltextIndex($a_table, $a_name)
Drop fulltext index.
nextId($table_name)
enableResultBuffering($a_status)
setLimit($limit, $offset=0)
Set the Limit for the next Query.
modifyTableColumn($table, $a_column, $a_attributes)
addTableColumn($table_name, $column_name, $attributes)
initHelpers()
migrateAllTablesToCollation($collation=ilDBConstants::MYSQL_COLLATION_UTF8MB4)
@inheritDoc
fetchObject($query_result)
setHost($host)
dropIndexByFields($table_name, $fields)
queryCol($query, $type=PDO::FETCH_ASSOC, $colnum=0)
useSlave($bool)
Class ilDatabaseException.
INIFile Parser.
Component logger with individual log levels by component id.
Class ilPDOStatement is a Wrapper Class for PDOStatement.
$def
Definition: croninfo.php:21
$key
Definition: croninfo.php:18
for( $i=6;$i< 13;$i++) for($i=1; $i< 13; $i++) $d
Definition: date.php:296
$i
Definition: disco.tpl.php:19
$r
Definition: example_031.php:79
global $ilBench
Definition: ilias.php:18
Interface ilDBInterface.
static getReservedWords()
Get reserved words.
execute($stmt, $data=array())
fetchAll($query_result, $fetch_mode=ilDBConstants::FETCHMODE_ASSOC)
Interface ilDBPdoInterface.
$info
Definition: index.php:5
$keys
$user
Definition: migrateto20.php:57
$stmt
if($modEnd===false) $module
Definition: module.php:59
$query
$type
if(empty($password)) $table
Definition: pwgen.php:24
global $DIC
Definition: saml.php:7
foreach($_POST as $key=> $value) $res
global $ilDB
$values
$start
Definition: bench.php:8
$data
Definition: bench.php:6
$engine
Definition: workflow.php:89
$text
Definition: errorreport.php:18
$cols
Definition: xhr_table.php:11
$rows
Definition: xhr_table.php:10