ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
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
4require_once("./Services/Database/classes/PDO/class.ilPDOStatement.php");
5require_once("./Services/Database/classes/QueryUtils/class.ilMySQLQueryUtils.php");
6require_once('./Services/Database/classes/PDO/Manager/class.ilDBPdoManager.php');
7require_once('./Services/Database/classes/PDO/Reverse/class.ilDBPdoReverse.php');
8require_once('./Services/Database/interfaces/interface.ilDBInterface.php');
9require_once('./Services/Database/classes/class.ilDBConstants.php');
10require_once('./Services/Database/interfaces/interface.ilDBLegacyInterface.php');
11
18abstract class ilDBPdo implements ilDBInterface, ilDBPdoInterface
19{
20 const FEATURE_TRANSACTIONS = 'transactions';
21 const FEATURE_FULLTEXT = 'fulltext';
22 const FEATURE_SLAVE = 'slave';
26 protected $host = '';
30 protected $dbname = '';
34 protected $charset = 'utf8';
38 protected $username = '';
42 protected $password = '';
46 protected $port = 3306;
50 protected $pdo;
54 protected $manager;
58 protected $reverse;
62 protected $limit = null;
66 protected $offset = null;
70 protected $storage_engine = 'MyISAM';
74 protected $dsn = '';
78 protected $attributes = array(
79 // PDO::ATTR_EMULATE_PREPARES => true,
80 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
81 );
85 protected $db_type = '';
89 protected $error_code = 0;
94
95
101 public function connect($return_false_for_error = false)
102 {
103 $this->generateDSN();
104 try {
105 $options = $this->getAttributes();
106 $this->pdo = new PDO($this->getDSN(), $this->getUsername(), $this->getPassword(), $options);
107 $this->initHelpers();
108 $this->initSQLMode();
109 } catch (Exception $e) {
110 $this->error_code = $e->getCode();
111 if ($return_false_for_error) {
112 return false;
113 }
114 throw $e;
115 }
116
117 return ($this->pdo->errorCode() == PDO::ERR_NONE);
118 }
119
120
121 abstract public function initHelpers();
122
123
124 protected function initSQLMode()
125 {
126 }
127
128
132 protected function getAttributes()
133 {
135 foreach ($this->getAdditionalAttributes() as $k => $v) {
136 $options[$k] = $v;
137 }
138
139 return $options;
140 }
141
142
146 protected function getAdditionalAttributes()
147 {
148 return array();
149 }
150
151
155 public function getFieldDefinition()
156 {
158 }
159
160
165 {
166 $this->field_definition = $field_definition;
167 }
168
169
177 public function createDatabase($a_name, $a_charset = "utf8", $a_collation = "")
178 {
179 $this->setDbname(null);
180 $this->generateDSN();
181 $this->connect(true);
182 try {
183 return $this->query($this->manager->getQueryUtils()->createDatabase($a_name, $a_charset, $a_collation));
184 } catch (PDOException $e) {
185 return false;
186 }
187 }
188
189
193 public function getLastErrorCode()
194 {
195 if ($this->pdo instanceof PDO) {
196 return $this->pdo->errorCode();
197 }
198
199 return $this->error_code;
200 }
201
202
206 public function initFromIniFile($tmpClientIniFile = null)
207 {
208 global $ilClientIniFile;
209 if ($tmpClientIniFile instanceof ilIniFile) {
210 $clientIniFile = $tmpClientIniFile;
211 } else {
212 $clientIniFile = $ilClientIniFile;
213 }
214
215 $this->setUsername($clientIniFile->readVariable("db", "user"));
216 $this->setHost($clientIniFile->readVariable("db", "host"));
217 $this->setPort((int) $clientIniFile->readVariable("db", "port"));
218 $this->setPassword($clientIniFile->readVariable("db", "pass"));
219 $this->setDbname($clientIniFile->readVariable("db", "name"));
220 $this->setDBType($clientIniFile->readVariable("db", "type"));
221
222 $this->generateDSN();
223 }
224
225
226 public function generateDSN()
227 {
228 $port = $this->getPort() ? ";port=" . $this->getPort() : "";
229 $dbname = $this->getDbname() ? ';dbname=' . $this->getDbname() : '';
230 $host = $this->getHost();
231 $charset = ';charset=' . $this->getCharset();
232 $this->dsn = 'mysql:host=' . $host . $port . $dbname . $charset;
233 }
234
235
240 public function quoteIdentifier($identifier, $check_option = false)
241 {
242 return '`' . $identifier . '`';
243 }
244
245
251 public function nextId($table_name)
252 {
253 $sequence_table_name = $table_name . '_seq';
254
255 $last_insert_id = $this->pdo->lastInsertId($table_name);
256 if ($last_insert_id) {
257 // return $last_insert_id;
258 }
259
260 if ($this->tableExists($sequence_table_name)) {
261 $stmt = $this->pdo->prepare("SELECT sequence FROM $sequence_table_name");
262 $stmt->execute();
263 $rows = $stmt->fetch(PDO::FETCH_ASSOC);
264 $stmt->closeCursor();
265 $next_id = $rows['sequence'] + 1;
266 $stmt = $this->pdo->prepare("DELETE FROM $sequence_table_name");
267 $stmt->execute(array("next_id" => $next_id));
268 $stmt = $this->pdo->prepare("INSERT INTO $sequence_table_name (sequence) VALUES (:next_id)");
269 $stmt->execute(array("next_id" => $next_id));
270
271 return $next_id;
272 }
273
274 return 1;
275 }
276
277
286 public function createTable($table_name, $fields, $drop_table = false, $ignore_erros = false)
287 {
288 // check table name
289 if (!$this->checkTableName($table_name) && !$ignore_erros) {
290 throw new ilDatabaseException("ilDB Error: createTable(" . $table_name . ")");
291 }
292
293 // check definition array
294 if (!$this->checkTableColumns($fields) && !$ignore_erros) {
295 throw new ilDatabaseException("ilDB Error: createTable(" . $table_name . ")");
296 }
297
298 if ($drop_table) {
299 $this->dropTable($table_name, false);
300 }
301
302 return $this->manager->createTable($table_name, $fields, array());
303 }
304
305
310 protected function checkTableColumns($a_cols)
311 {
312 foreach ($a_cols as $col => $def) {
313 if (!$this->checkColumn($col, $def)) {
314 return false;
315 }
316 }
317
318 return true;
319 }
320
321
327 protected function checkColumn($a_col, $a_def)
328 {
329 if (!$this->checkColumnName($a_col)) {
330 return false;
331 }
332
333 if (!$this->checkColumnDefinition($a_def)) {
334 return false;
335 }
336
337 return true;
338 }
339
340
346 protected function checkColumnDefinition($a_def, $a_modify_mode = false)
347 {
348 return $this->field_definition->checkColumnDefinition($a_def);
349 }
350
351
356 public function checkColumnName($a_name)
357 {
358 return $this->field_definition->checkColumnName($a_name);
359 }
360
361
368 public function addPrimaryKey($table_name, $primary_keys)
369 {
370 assert(is_array($primary_keys));
371
372 $fields = array();
373 foreach ($primary_keys as $f) {
374 $fields[$f] = array();
375 }
376 $definition = array(
377 'primary' => true,
378 'fields' => $fields,
379 );
380 $this->manager->createConstraint($table_name, $this->constraintName($table_name, $this->getPrimaryKeyIdentifier()), $definition);
381
382 return true;
383 }
384
385
392 public function dropIndexByFields($table_name, $fields)
393 {
394 foreach ($this->manager->listTableIndexes($table_name) as $idx_name) {
395 $def = $this->reverse->getTableIndexDefinition($table_name, $idx_name);
396 $idx_fields = array_keys((array) $def['fields']);
397
398 if ($idx_fields === $fields) {
399 return $this->dropIndex($table_name, $idx_name);
400 }
401 }
402
403 return false;
404 }
405
406
410 public function getPrimaryKeyIdentifier()
411 {
412 return "PRIMARY";
413 }
414
415
420 public function createSequence($table_name, $start = 1)
421 {
422 $this->manager->createSequence($table_name, $start);
423 }
424
425
431 public function tableExists($table_name)
432 {
433 $result = $this->pdo->prepare("SHOW TABLES LIKE :table_name");
434 $result->execute(array('table_name' => $table_name));
435 $return = $result->rowCount();
436 $result->closeCursor();
437
438 return $return > 0;
439 }
440
441
448 public function tableColumnExists($table_name, $column_name)
449 {
450 $fields = $this->loadModule(ilDBConstants::MODULE_MANAGER)->listTableFields($table_name);
451
452 $in_array = in_array($column_name, $fields);
453
454 return $in_array;
455 }
456
457
465 public function addTableColumn($table_name, $column_name, $attributes)
466 {
467 if (!$this->checkColumnName($column_name)) {
468 throw new ilDatabaseException("ilDB Error: addTableColumn(" . $table_name . ", " . $column_name . ")");
469 }
470 if (!$this->checkColumnDefinition($attributes)) {
471 throw new ilDatabaseException("ilDB Error: addTableColumn(" . $table_name . ", " . $column_name . ")");
472 }
473
474 $changes = array(
475 "add" => array(
476 $column_name => $attributes,
477 ),
478 );
479
480 return $this->manager->alterTable($table_name, $changes, false);
481 }
482
483
490 public function dropTable($table_name, $error_if_not_existing = true)
491 {
492 $ilDBPdoManager = $this->loadModule(ilDBConstants::MODULE_MANAGER);
493 $tables = $ilDBPdoManager->listTables();
494 $table_exists = in_array($table_name, $tables);
495 if (!$table_exists && $error_if_not_existing) {
496 throw new ilDatabaseException("Table {$table_name} does not exist");
497 }
498
499 // drop sequence
500 $sequences = $ilDBPdoManager->listSequences();
501 if (in_array($table_name, $sequences)) {
502 $ilDBPdoManager->dropSequence($table_name);
503 }
504
505 // drop table
506 if ($table_exists) {
507 $ilDBPdoManager->dropTable($table_name);
508 }
509
510 return true;
511 }
512
513
520 public function query($query)
521 {
522 global $DIC;
523 $ilBench = $DIC['ilBench'];
524
525 $query = $this->appendLimit($query);
526
527 try {
528 if ($ilBench instanceof ilBenchmark) {
529 $ilBench->startDbBench($query);
530 }
531 $res = $this->pdo->query($query);
532 if ($ilBench instanceof ilBenchmark) {
533 $ilBench->stopDbBench();
534 }
535 } catch (PDOException $e) {
536 throw new ilDatabaseException($e->getMessage() . ' QUERY: ' . $query);
537 }
538
539 $err = $this->pdo->errorCode();
540 if ($err != PDO::ERR_NONE) {
541 $info = $this->pdo->errorInfo();
542 $info_message = $info[2];
543 throw new ilDatabaseException($info_message . ' QUERY: ' . $query);
544 }
545
546 return new ilPDOStatement($res);
547 }
548
549
555 public function fetchAll($query_result, $fetch_mode = ilDBConstants::FETCHMODE_ASSOC)
556 {
560 $return = array();
561 while ($data = $query_result->fetch($fetch_mode)) {
562 $return[] = $data;
563 }
564
565 return $return;
566 }
567
568
572 public function dropSequence($table_name)
573 {
574 $this->manager->dropSequence($table_name);
575 }
576
577
584 public function dropTableColumn($table_name, $column_name)
585 {
586 $changes = array(
587 "remove" => array(
588 $column_name => array(),
589 ),
590 );
591
592 return $this->manager->alterTable($table_name, $changes, false);
593 }
594
595
603 public function renameTableColumn($table_name, $column_old_name, $column_new_name)
604 {
605 // check table name
606 if (!$this->checkColumnName($column_new_name)) {
607 throw new ilDatabaseException("ilDB Error: renameTableColumn(" . $table_name . "," . $column_old_name . "," . $column_new_name . ")");
608 }
609
610 $def = $this->reverse->getTableFieldDefinition($table_name, $column_old_name);
611
612 $analyzer = new ilDBAnalyzer($this);
613 $best_alt = $analyzer->getBestDefinitionAlternative($def);
614 $def = $def[$best_alt];
615 unset($def["nativetype"]);
616 unset($def["mdb2type"]);
617
618 $f["definition"] = $def;
619 $f["name"] = $column_new_name;
620
621 $changes = array(
622 "rename" => array(
623 $column_old_name => $f,
624 ),
625 );
626
627 return $this->manager->alterTable($table_name, $changes, false);
628 }
629
630
636 public function insert($table_name, $values)
637 {
638 $real = array();
639 $fields = array();
640 foreach ($values as $key => $val) {
641 $real[] = $this->quote($val[1], $val[0]);
642 $fields[] = $this->quoteIdentifier($key);
643 }
644 $values = implode(",", $real);
645 $fields = implode(",", $fields);
646 $query = "INSERT INTO " . $table_name . " (" . $fields . ") VALUES (" . $values . ")";
647
649
650 return $this->pdo->exec($query);
651 }
652
653
659 public function fetchObject($query_result)
660 {
661 $res = $query_result->fetchObject();
662 if ($res == null) {
663 $query_result->closeCursor();
664
665 return null;
666 }
667
668 return $res;
669 }
670
671
678 public function update($table_name, $columns, $where)
679 {
680 $fields = array();
681 $field_values = array();
682 $placeholders = array();
683 $placeholders_full = array();
684 $types = array();
685 $values = array();
686 $lobs = false;
687 $lob = array();
688 foreach ($columns as $k => $col) {
689 $field_value = $col[1];
690 $fields[] = $k;
691 $placeholders[] = "%s";
692 $placeholders_full[] = ":$k";
693 $types[] = $col[0];
694
695 if ($col[0] == "blob" || $col[0] == "clob" || $col[0] == 'text') {
696 $field_value = $this->sanitizeMB4StringIfNotSupported($field_value);
697 }
698
699 // integer auto-typecast (this casts bool values to integer)
700 if ($col[0] == 'integer' && !is_null($field_value)) {
701 $field_value = (int) $field_value;
702 }
703
704 $values[] = $field_value;
705 $field_values[$k] = $field_value;
706 if ($col[0] == "blob" || $col[0] == "clob") {
707 $lobs = true;
708 $lob[$k] = $k;
709 }
710 }
711
712 if ($lobs) {
713 $q = "UPDATE " . $table_name . " SET ";
714 $lim = "";
715 foreach ($fields as $k => $field) {
716 $q .= $lim . $field . " = " . $placeholders_full[$k];
717 $lim = ", ";
718 }
719 $q .= " WHERE ";
720 $lim = "";
721 foreach ($where as $k => $col) {
722 $q .= $lim . $k . " = " . $this->quote($col[1], $col[0]);
723 $lim = " AND ";
724 }
725
726 $r = $this->prepareManip($q, $types);
727 $this->execute($r, $field_values);
728 $this->free($r);
729 } else {
730 foreach ($where as $k => $col) {
731 $types[] = $col[0];
732 $values[] = $col[1];
733 $field_values[$k] = $col;
734 }
735 $q = "UPDATE " . $table_name . " SET ";
736 $lim = "";
737 foreach ($fields as $k => $field) {
738 $q .= $lim . $this->quoteIdentifier($field) . " = " . $placeholders[$k];
739 $lim = ", ";
740 }
741 $q .= " WHERE ";
742 $lim = "";
743 foreach ($where as $k => $col) {
744 $q .= $lim . $k . " = %s";
745 $lim = " AND ";
746 }
747
748 $r = $this->manipulateF($q, $types, $values);
749 }
750
751 return $r;
752 }
753
754
755
761 public function manipulate($query)
762 {
763 global $DIC;
764 $ilBench = $DIC['ilBench'];
765 try {
767 if ($ilBench instanceof ilBenchmark) {
768 $ilBench->startDbBench($query);
769 }
770 $r = $this->pdo->exec($query);
771 if ($ilBench instanceof ilBenchmark) {
772 $ilBench->stopDbBench();
773 }
774 } catch (PDOException $e) {
775 throw new ilDatabaseException($e->getMessage() . ' QUERY: ' . $query);
776 }
777
778 return $r;
779 }
780
781
787 public function fetchAssoc($query_result)
788 {
789 $res = $query_result->fetch(PDO::FETCH_ASSOC);
790 if ($res == null) {
791 $query_result->closeCursor();
792
793 return null;
794 }
795
796 return $res;
797 }
798
799
805 public function numRows($query_result)
806 {
807 return $query_result->rowCount();
808 }
809
810
817 public function quote($value, $type = null)
818 {
819 if ($value === null) {
820 return 'NULL';
821 }
822
823 $pdo_type = PDO::PARAM_STR;
824 switch ($type) {
828 if ($value === '') {
829 return 'NULL';
830 }
831 break;
833 $value = (int) $value;
834
835 return $value;
836 break;
838 $pdo_type = PDO::PARAM_INT;
839 break;
841 default:
842 $pdo_type = PDO::PARAM_STR;
843 break;
844 }
845
846 return $this->pdo->quote($value, $pdo_type);
847 }
848
849
856 public function indexExistsByFields($table_name, $fields)
857 {
858 foreach ($this->manager->listTableIndexes($table_name) as $idx_name) {
859 $def = $this->reverse->getTableIndexDefinition($table_name, $idx_name);
860 $idx_fields = array_keys((array) $def['fields']);
861
862 if ($idx_fields === $fields) {
863 return true;
864 }
865 }
866
867 return false;
868 }
869
870
877 public function addIndex($table_name, $fields, $index_name = '', $fulltext = false)
878 {
879 assert(is_array($fields));
880 $this->field_definition->checkIndexName($index_name);
881
882 $definition_fields = array();
883 foreach ($fields as $f) {
884 $definition_fields[$f] = array();
885 }
886 $definition = array(
887 'fields' => $definition_fields,
888 );
889
890 if (!$fulltext) {
891 $this->manager->createIndex($table_name, $this->constraintName($table_name, $index_name), $definition);
892 } else {
893 if ($this->supportsFulltext()) {
894 $this->addFulltextIndex($table_name, $fields, $index_name); // TODO
895 }
896 }
897
898 return true;
899 }
900
901
909 public function addFulltextIndex($a_table, $a_fields, $a_name = "in")
910 {
911 $i_name = $this->constraintName($a_table, $a_name) . "_idx";
912 $f_str = implode($a_fields, ",");
913 $q = "ALTER TABLE $a_table ADD FULLTEXT $i_name ($f_str)";
914 $this->query($q);
915 }
916
917
921 public function dropFulltextIndex($a_table, $a_name)
922 {
923 $i_name = $this->constraintName($a_table, $a_name) . "_idx";
924 $this->query("ALTER TABLE $a_table DROP FULLTEXT $i_name");
925 }
926
927
931 public function isFulltextIndex($a_table, $a_name)
932 {
933 $set = $this->query("SHOW INDEX FROM " . $a_table);
934 while ($rec = $this->fetchAssoc($set)) {
935 if ($rec["Key_name"] == $a_name && $rec["Index_type"] == "FULLTEXT") {
936 return true;
937 }
938 }
939
940 return false;
941 }
942
943
948 public function getIndexName($index_name_base)
949 {
950 return sprintf(ilDBPdoFieldDefinition::INDEX_FORMAT, preg_replace('/[^a-z0-9_\$]/i', '_', $index_name_base));
951 }
952
953
958 public function getSequenceName($table_name)
959 {
960 return sprintf(ilDBPdoFieldDefinition::SEQUENCE_FORMAT, preg_replace('/[^a-z0-9_\$.]/i', '_', $table_name));
961 }
962
963
969 public function constraintName($a_table, $a_constraint)
970 {
971 return $a_constraint;
972 }
973
974
978 public function getDSN()
979 {
980 return $this->dsn;
981 }
982
983
987 public function getDBType()
988 {
989 return $this->db_type;
990 }
991
992
997 public function setDBType($type)
998 {
999 $this->db_type = $type;
1000 }
1001
1002
1007 public static function getReservedWords()
1008 {
1009 global $ilDB;
1010
1014 return $ilDB->getFieldDefinition()->getReservedMysql();
1015 }
1016
1017
1022 public function lockTables($tables)
1023 {
1024 assert(is_array($tables));
1025
1026 $lock = $this->manager->getQueryUtils()->lock($tables);
1027 global $ilLog;
1028 if ($ilLog instanceof ilLog) {
1029 $ilLog->write('ilDB::lockTables(): ' . $lock);
1030 }
1031
1032 $this->pdo->exec($lock);
1033 }
1034
1035
1040 public function unlockTables()
1041 {
1042 $this->pdo->exec($this->manager->getQueryUtils()->unlock());
1043 }
1044
1045
1053 public function in($field, $values, $negate = false, $type = "")
1054 {
1055 return $this->manager->getQueryUtils()->in($field, $values, $negate, $type);
1056 }
1057
1058
1066 public function queryF($query, $types, $values)
1067 {
1068 if (!is_array($types) || !is_array($values) || count($types) != count($values)) {
1069 throw new ilDatabaseException("ilDB::queryF: Types and values must be arrays of same size. ($query)");
1070 }
1071 $quoted_values = array();
1072 foreach ($types as $k => $t) {
1073 $quoted_values[] = $this->quote($values[$k], $t);
1074 }
1075 $query = vsprintf($query, $quoted_values);
1076
1077 return $this->query($query);
1078 }
1079
1080
1088 public function manipulateF($query, $types, $values)
1089 {
1090 if (!is_array($types) || !is_array($values) || count($types) != count($values)) {
1091 throw new ilDatabaseException("ilDB::manipulateF: types and values must be arrays of same size. ($query)");
1092 }
1093 $quoted_values = array();
1094 foreach ($types as $k => $t) {
1095 $quoted_values[] = $this->quote($values[$k], $t);
1096 }
1097 $query = vsprintf($query, $quoted_values);
1098
1099 return $this->manipulate($query);
1100 }
1101
1102
1109 public function useSlave($bool)
1110 {
1111 return false;
1112 }
1113
1114
1121 public function setLimit($limit, $offset = 0)
1122 {
1123 $this->limit = $limit;
1124 $this->offset = $offset;
1125 }
1126
1127
1136 public function like($column, $type, $value = "?", $case_insensitive = true)
1137 {
1138 return $this->manager->getQueryUtils()->like($column, $type, $value, $case_insensitive);
1139 }
1140
1141
1145 public function now()
1146 {
1147 return $this->manager->getQueryUtils()->now();
1148 }
1149
1150
1159 public function replace($table, $primaryKeys, $otherColumns)
1160 {
1161 $a_columns = array_merge($primaryKeys, $otherColumns);
1162 $fields = array();
1163 $field_values = array();
1164 $placeholders = array();
1165 $types = array();
1166 $values = array();
1167
1168 foreach ($a_columns as $k => $col) {
1169 $fields[] = $k;
1170 $placeholders[] = "%s";
1171 $placeholders2[] = ":$k";
1172 $types[] = $col[0];
1173
1174 // integer auto-typecast (this casts bool values to integer)
1175 if ($col[0] == 'integer' && !is_null($col[1])) {
1176 $col[1] = (int) $col[1];
1177 }
1178
1179 $values[] = $col[1];
1180 $field_values[$k] = $col[1];
1181 }
1182
1183 $q = "REPLACE INTO " . $table . " (" . implode($fields, ",") . ") VALUES (" . implode($placeholders, ",") . ")";
1184
1185 $r = $this->manipulateF($q, $types, $values);
1186
1187 return $r;
1188 }
1189
1190
1198 public function equals($columns, $value, $type, $emptyOrNull = false)
1199 {
1200 if (!$emptyOrNull || $value != "") {
1201 return $columns . " = " . $this->quote($value, $type);
1202 } else {
1203 return "(" . $columns . " = '' OR $columns IS NULL)";
1204 }
1205 }
1206
1207
1211 public function getHost()
1212 {
1213 return $this->host;
1214 }
1215
1216
1220 public function setHost($host)
1221 {
1222 $this->host = $host;
1223 }
1224
1225
1229 public function getDbname()
1230 {
1231 return $this->dbname;
1232 }
1233
1234
1238 public function setDbname($dbname)
1239 {
1240 $this->dbname = $dbname;
1241 }
1242
1243
1247 public function getCharset()
1248 {
1249 return $this->charset;
1250 }
1251
1252
1256 public function setCharset($charset)
1257 {
1258 $this->charset = $charset;
1259 }
1260
1261
1265 public function getUsername()
1266 {
1267 return $this->username;
1268 }
1269
1270
1274 public function setUsername($username)
1275 {
1276 $this->username = $username;
1277 }
1278
1279
1283 public function getPassword()
1284 {
1285 return $this->password;
1286 }
1287
1288
1292 public function setPassword($password)
1293 {
1294 $this->password = $password;
1295 }
1296
1297
1301 public function getPort()
1302 {
1303 return $this->port;
1304 }
1305
1306
1310 public function setPort($port)
1311 {
1312 $this->port = $port;
1313 }
1314
1315
1319 public function setDBUser($user)
1320 {
1321 $this->setUsername($user);
1322 }
1323
1324
1328 public function setDBPort($port)
1329 {
1330 $this->setPort($port);
1331 }
1332
1333
1337 public function setDBPassword($password)
1338 {
1339 $this->setPassword($password);
1340 }
1341
1342
1346 public function setDBHost($host)
1347 {
1348 $this->setHost($host);
1349 }
1350
1351
1356 public function upper($a_exp)
1357 {
1358 return " UPPER(" . $a_exp . ") ";
1359 }
1360
1361
1366 public function lower($a_exp)
1367 {
1368 return " LOWER(" . $a_exp . ") ";
1369 }
1370
1371
1378 public function substr($a_exp, $a_pos = 1, $a_len = -1)
1379 {
1380 $lenstr = "";
1381 if ($a_len > -1) {
1382 $lenstr = ", " . $a_len;
1383 }
1384
1385 return " SUBSTR(" . $a_exp . ", " . $a_pos . $lenstr . ") ";
1386 }
1387
1388
1394 public function prepareManip($query, $types = null)
1395 {
1396 return new ilPDOStatement($this->pdo->prepare($query));
1397 }
1398
1399
1406 public function prepare($query, $types = null, $result_types = null)
1407 {
1408 return new ilPDOStatement($this->pdo->prepare($query));
1409 }
1410
1411
1415 public function enableResultBuffering($a_status)
1416 {
1417 $this->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, $a_status);
1418 }
1419
1420
1427 public function execute($stmt, $data = array())
1428 {
1432 $result = $stmt->execute($data);
1433 if ($result === false) {
1434 throw new ilDatabaseException(implode(', ', $stmt->errorInfo()), $stmt->errorCode());
1435 }
1436 return $stmt;
1437 }
1438
1439
1443 public function supportsSlave()
1444 {
1445 return false;
1446 }
1447
1448
1452 public function supportsFulltext()
1453 {
1454 return false;
1455 }
1456
1457
1461 public function supportsTransactions()
1462 {
1463 return false;
1464 }
1465
1466
1471 public function supports($feature)
1472 {
1473 switch ($feature) {
1475 return $this->supportsTransactions();
1477 return $this->supportsFulltext();
1479 return $this->supportsSlave();
1480 default:
1481 return false;
1482 }
1483 }
1484
1485
1489 public function listTables()
1490 {
1491 return $this->manager->listTables();
1492 }
1493
1494
1499 public function loadModule($module)
1500 {
1501 switch ($module) {
1503 return $this->manager;
1505 return $this->reverse;
1506 }
1507 }
1508
1509
1513 public function getAllowedAttributes()
1514 {
1515 return $this->field_definition->getAllowedAttributes();
1516 }
1517
1518
1523 public function sequenceExists($sequence)
1524 {
1525 return in_array($sequence, $this->listSequences());
1526 }
1527
1528
1532 public function listSequences()
1533 {
1534 return $this->manager->listSequences();
1535 }
1536
1537
1543 public function concat(array $values, $allow_null = true)
1544 {
1545 return $this->manager->getQueryUtils()->concat($values, $allow_null);
1546 }
1547
1548
1553 protected function appendLimit($query)
1554 {
1555 if ($this->limit !== null && $this->offset !== null) {
1556 $query .= ' LIMIT ' . (int) $this->offset . ', ' . (int) $this->limit;
1557 $this->limit = null;
1558 $this->offset = null;
1559
1560 return $query;
1561 }
1562
1563 return $query;
1564 }
1565
1566
1573 public function locate($a_needle, $a_string, $a_start_pos = 1)
1574 {
1575 return $this->manager->getQueryUtils()->locate($a_needle, $a_string, $a_start_pos);
1576 }
1577
1578
1586 public function modifyTableColumn($table, $a_column, $a_attributes)
1587 {
1588 $def = $this->reverse->getTableFieldDefinition($table, $a_column);
1589
1590 $analyzer = new ilDBAnalyzer($this);
1591 $best_alt = $analyzer->getBestDefinitionAlternative($def);
1592 $def = $def[$best_alt];
1593 unset($def["nativetype"]);
1594 unset($def["mdb2type"]);
1595
1596 // check attributes
1597 $ilDBPdoFieldDefinition = $this->field_definition;
1598
1599 $type = ($a_attributes["type"] != "") ? $a_attributes["type"] : $def["type"];
1600 foreach ($def as $k => $v) {
1601 if ($k != "type" && !$ilDBPdoFieldDefinition->isAllowedAttribute($k, $type)) {
1602 unset($def[$k]);
1603 }
1604 }
1605 $check_array = $def;
1606 foreach ($a_attributes as $k => $v) {
1607 $check_array[$k] = $v;
1608 }
1609 if (!$this->checkColumnDefinition($check_array, true)) {
1610 throw new ilDatabaseException("ilDB Error: modifyTableColumn(" . $table . ", " . $a_column . ")");
1611 }
1612
1613 foreach ($a_attributes as $a => $v) {
1614 $def[$a] = $v;
1615 }
1616
1617 $a_attributes["definition"] = $def;
1618
1619 $changes = array(
1620 "change" => array(
1621 $a_column => $a_attributes,
1622 ),
1623 );
1624
1625 return $this->manager->alterTable($table, $changes, false);
1626 }
1627
1628
1633 public function free($a_st)
1634 {
1638 return $a_st->closeCursor();
1639 }
1640
1641
1648 public function renameTable($a_name, $a_new_name)
1649 {
1650 // check table name
1651 try {
1652 $this->checkTableName($a_new_name);
1653 } catch (ilDatabaseException $e) {
1654 throw new ilDatabaseException("ilDB Error: renameTable(" . $a_name . "," . $a_new_name . ")<br />" . $e->getMessage());
1655 }
1656
1657 $this->manager->alterTable($a_name, array("name" => $a_new_name), false);
1658
1659 // The abstraction_progress is no longer used in ILIAS, see http://www.ilias.de/mantis/view.php?id=19513
1660 // $query = "UPDATE abstraction_progress " . "SET table_name = " . $this->quote($a_new_name, 'text') . " " . "WHERE table_name = "
1661 // . $this->quote($a_name, 'text');
1662 // $this->pdo->query($query);
1663
1664 return true;
1665 }
1666
1667
1673 public function checkTableName($a_name)
1674 {
1675 return $this->field_definition->checkTableName($a_name);
1676 }
1677
1678
1683 public static function isReservedWord($a_word)
1684 {
1685 require_once('./Services/Database/classes/PDO/FieldDefinition/class.ilDBPdoMySQLFieldDefinition.php');
1686 global $DIC;
1687 $ilDBPdoMySQLFieldDefinition = new ilDBPdoMySQLFieldDefinition($DIC['ilDB']);
1688
1689 return $ilDBPdoMySQLFieldDefinition->isReserved($a_word);
1690 }
1691
1692
1697 public function beginTransaction()
1698 {
1699 if (!$this->supports(self::FEATURE_TRANSACTIONS)) {
1700 throw new ilDatabaseException("ilDB::beginTransaction: Transactions are not supported.");
1701 }
1702
1703 return $this->pdo->beginTransaction();
1704 }
1705
1706
1711 public function commit()
1712 {
1713 if (!$this->supports(self::FEATURE_TRANSACTIONS)) {
1714 throw new ilDatabaseException("ilDB::beginTransaction: Transactions are not supported.");
1715 }
1716
1717 return $this->pdo->commit();
1718 }
1719
1720
1725 public function rollback()
1726 {
1727 if (!$this->supports(self::FEATURE_TRANSACTIONS)) {
1728 throw new ilDatabaseException("ilDB::beginTransaction: Transactions are not supported.");
1729 }
1730
1731 return $this->pdo->rollBack();
1732 }
1733
1734
1740 public function dropIndex($a_table, $a_name = "i1")
1741 {
1742 return $this->manager->dropIndex($a_table, $a_name);
1743 }
1744
1745
1750 {
1751 $this->storage_engine = $storage_engine;
1752 }
1753
1754
1758 public function getStorageEngine()
1759 {
1760 return $this->storage_engine;
1761 }
1762
1763
1770 public function queryCol($query, $type = PDO::FETCH_ASSOC, $colnum = 0)
1771 {
1772 switch ($type) {
1774 $type = PDO::FETCH_ASSOC;
1775 break;
1777 $type = PDO::FETCH_OBJ;
1778 break;
1779 default:
1780 $type = PDO::FETCH_ASSOC;
1781 break;
1782 }
1783
1784 return $this->pdo->query($query, PDO::FETCH_ASSOC)->fetchAll(PDO::FETCH_COLUMN, $colnum);
1785 }
1786
1787
1794 public function queryRow($query, $types = null, $fetchmode = ilDBConstants::FETCHMODE_DEFAULT)
1795 {
1796 switch ($fetchmode) {
1798 $type = PDO::FETCH_ASSOC;
1799 break;
1801 $type = PDO::FETCH_OBJ;
1802 break;
1803 default:
1804 $type = PDO::FETCH_ASSOC;
1805 break;
1806 }
1807
1808 return $this->pdo->query($query, $type)->fetch();
1809 }
1810
1811
1816 public function getServerVersion($native = false)
1817 {
1818 return $this->pdo->query('SELECT VERSION()')->fetchColumn();
1819 }
1820
1821
1827 public function escape($value, $escape_wildcards = false)
1828 {
1829 return $value;
1830 }
1831
1832
1837 public function escapePattern($text)
1838 {
1839 return $text;
1840 }
1841
1842
1848 {
1849 return array();
1850 }
1851
1852
1857 {
1858 return array();
1859 }
1860
1861
1866 {
1867 return false;
1868 }
1869
1870
1874 public function supportsEngineMigration()
1875 {
1876 return false;
1877 }
1878
1879
1885 public function checkIndexName($name)
1886 {
1887 return $this->getFieldDefinition()->checkIndexName($name);
1888 }
1889
1890
1898 public function addUniqueConstraint($table, $fields, $name = "con")
1899 {
1900 assert(is_array($fields));
1902
1903 // check index name
1904 if (!$this->checkIndexName($name)) {
1905 throw new ilDatabaseException("ilDB Error: addUniqueConstraint(" . $table . "," . $name . ")");
1906 }
1907
1908 $fields_corrected = array();
1909 foreach ($fields as $f) {
1910 $fields_corrected[$f] = array();
1911 }
1912 $definition = array(
1913 'unique' => true,
1914 'fields' => $fields_corrected,
1915 );
1916
1917 return $manager->createConstraint($table, $this->constraintName($table, $name), $definition);
1918 }
1919
1920
1926 public function dropUniqueConstraint($a_table, $a_name = "con")
1927 {
1928 return $this->manager->dropConstraint($a_table, $this->constraintName($a_table, $a_name), false);
1929 }
1930
1931
1937 public function dropUniqueConstraintByFields($a_table, $a_fields)
1938 {
1939 $analyzer = new ilDBAnalyzer();
1940 $cons = $analyzer->getConstraintsInformation($a_table);
1941 foreach ($cons as $c) {
1942 if ($c["type"] == "unique" && count($a_fields) == count($c["fields"])) {
1943 $all_in = true;
1944 foreach ($a_fields as $f) {
1945 if (!isset($c["fields"][$f])) {
1946 $all_in = false;
1947 }
1948 }
1949 if ($all_in) {
1950 return $this->dropUniqueConstraint($a_table, $c['name']);
1951 }
1952 }
1953 }
1954
1955 return false;
1956 }
1957
1958
1962 public function getLastInsertId()
1963 {
1964 return $this->pdo->lastInsertId();
1965 }
1966
1967
1971 public function buildAtomQuery()
1972 {
1973 require_once('./Services/Database/classes/Atom/class.ilAtomQueryLock.php');
1974
1975 return new ilAtomQueryLock($this);
1976 }
1977
1978
1984 public function uniqueConstraintExists($table, array $fields)
1985 {
1986 require_once('./Services/Database/classes/class.ilDBAnalyzer.php');
1987 $analyzer = new ilDBAnalyzer();
1988 $cons = $analyzer->getConstraintsInformation($table);
1989 foreach ($cons as $c) {
1990 if ($c["type"] == "unique" && count($fields) == count($c["fields"])) {
1991 $all_in = true;
1992 foreach ($fields as $f) {
1993 if (!isset($c["fields"][$f])) {
1994 $all_in = false;
1995 }
1996 }
1997 if ($all_in) {
1998 return true;
1999 }
2000 }
2001 }
2002
2003 return false;
2004 }
2005
2006
2011 public function dropPrimaryKey($table_name)
2012 {
2013 return $this->manager->dropConstraint($table_name, "PRIMARY", true);
2014 }
2015
2016
2021 public function executeMultiple($stmt, $a_data)
2022 {
2023 for ($i = 0, $j = count($a_data); $i < $j; $i++) {
2024 $stmt->execute($a_data[$i]);
2025 }
2026 }
2027
2028
2034 public function fromUnixtime($a_expr, $a_to_text = true)
2035 {
2036 return "FROM_UNIXTIME(" . $a_expr . ")";
2037 }
2038
2039
2043 public function unixTimestamp()
2044 {
2045 return "UNIX_TIMESTAMP()";
2046 }
2047
2048
2064 public function autoExecute($tablename, $fields, $mode = ilDBConstants::MDB2_AUTOQUERY_INSERT, $where = false)
2065 {
2066 $fields_values = (array) $fields;
2068 if (!empty($fields_values)) {
2069 $keys = $fields_values;
2070 } else {
2071 $keys = array();
2072 }
2073 } else {
2074 $keys = array_keys($fields_values);
2075 }
2076 $params = array_values($fields_values);
2077 if (empty($params)) {
2078 $query = $this->buildManipSQL($tablename, $keys, $mode, $where);
2079 $result = $this->pdo->query($query);
2080 } else {
2081 $stmt = $this->autoPrepare($tablename, $keys, $mode, $where, $types, $result_types);
2082 $this->execute($stmt);
2083 $this->free($stmt);
2084 $result = $stmt;
2085 }
2086
2087 return $result;
2088 }
2089
2090
2100 protected function autoPrepare($table, $table_fields, $mode = ilDBConstants::MDB2_AUTOQUERY_INSERT, $where = false, $types = null, $result_types = ilDBConstants::MDB2_PREPARE_MANIP)
2101 {
2102 $query = $this->buildManipSQL($table, $table_fields, $mode, $where);
2103
2104 return $this->prepare($query, $types, $result_types);
2105 }
2106
2107
2116 protected function buildManipSQL($table, $table_fields, $mode, $where = false)
2117 {
2118 if ($this->options['quote_identifier']) {
2119 $table = $this->quoteIdentifier($table);
2120 }
2121
2122 if (!empty($table_fields) && $this->options['quote_identifier']) {
2123 foreach ($table_fields as $key => $field) {
2124 $table_fields[$key] = $this->quoteIdentifier($field);
2125 }
2126 }
2127
2128 if ($where !== false && !is_null($where)) {
2129 if (is_array($where)) {
2130 $where = implode(' AND ', $where);
2131 }
2132 $where = ' WHERE ' . $where;
2133 }
2134
2135 switch ($mode) {
2137 if (empty($table_fields)) {
2138 throw new ilDatabaseException('Insert requires table fields');
2139 }
2140 $cols = implode(', ', $table_fields);
2141 $values = '?' . str_repeat(', ?', (count($table_fields) - 1));
2142
2143 return 'INSERT INTO ' . $table . ' (' . $cols . ') VALUES (' . $values . ')';
2144 break;
2146 if (empty($table_fields)) {
2147 throw new ilDatabaseException('Update requires table fields');
2148 }
2149 $set = implode(' = ?, ', $table_fields) . ' = ?';
2150 $sql = 'UPDATE ' . $table . ' SET ' . $set . $where;
2151
2152 return $sql;
2153 break;
2155 $sql = 'DELETE FROM ' . $table . $where;
2156
2157 return $sql;
2158 break;
2160 $cols = !empty($table_fields) ? implode(', ', $table_fields) : '*';
2161 $sql = 'SELECT ' . $cols . ' FROM ' . $table . $where;
2162
2163 return $sql;
2164 break;
2165 }
2166
2167 throw new ilDatabaseException('Syntax error');
2168 }
2169
2170
2175 public function getDBVersion()
2176 {
2177 $d = $this->fetchObject($this->query("SELECT VERSION() AS version"));
2178
2179 return ($d->version ? $d->version : 'Unknown');
2180 }
2181
2182
2187 {
2188 if (!$this->doesCollationSupportMB4Strings()) {
2189 $query_replaced = preg_replace(
2190 '/[\x{10000}-\x{10FFFF}]/u',
2192 $query
2193 );
2194 if (!empty($query_replaced)) {
2195 return $query_replaced;
2196 }
2197 }
2198
2199 return $query;
2200 }
2201
2206 {
2207 return false;
2208 }
2209
2210
2214 public function groupConcat($a_field_name, $a_seperator = ",", $a_order = null)
2215 {
2216 return $this->manager->getQueryUtils()->groupConcat($a_field_name, $a_seperator, $a_order);
2217 }
2218
2222 public function cast($a_field_name, $a_dest_type)
2223 {
2224 return $this->manager->getQueryUtils()->cast($a_field_name, $a_dest_type);
2225 }
2226}
sprintf('%.4f', $callTime)
$column
Definition: 39dropdown.php:62
$result
if(! $in) $columns
Definition: Utf8Test.php:45
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 ilAtomQueryLock.
performance measurement class
This class gives all kind of DB information using the MDB2 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)
autoExecute($tablename, $fields, $mode=ilDBConstants::MDB2_AUTOQUERY_INSERT, $where=false)
Generate an insert, update or delete query and call prepare() and execute() on it.
initFromIniFile($tmpClientIniFile=null)
addUniqueConstraint($table, $fields, $name="con")
getAdditionalAttributes()
supports($feature)
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)
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)
autoPrepare($table, $table_fields, $mode=ilDBConstants::MDB2_AUTOQUERY_INSERT, $where=false, $types=null, $result_types=ilDBConstants::MDB2_PREPARE_MANIP)
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.
logging
Definition: class.ilLog.php:19
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.
if($format !==null) $name
Definition: metadata.php:146
$info
Definition: index.php:5
$keys
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
$engine
Definition: workflow.php:89
$params
Definition: disable.php:11
$text
Definition: errorreport.php:18
$cols
Definition: xhr_table.php:11
$rows
Definition: xhr_table.php:10