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');
80 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
101 public function connect($return_false_for_error =
false)
109 }
catch (Exception $e) {
110 $this->error_code = $e->getCode();
111 if ($return_false_for_error) {
117 return ($this->pdo->errorCode() == PDO::ERR_NONE);
183 return $this->
query($this->manager->getQueryUtils()->createDatabase($a_name, $a_charset, $a_collation));
184 }
catch (PDOException $e) {
195 if ($this->pdo instanceof PDO) {
196 return $this->pdo->errorCode();
208 global $ilClientIniFile;
209 if ($tmpClientIniFile instanceof
ilIniFile) {
210 $clientIniFile = $tmpClientIniFile;
212 $clientIniFile = $ilClientIniFile;
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"));
242 return '`' . $identifier .
'`';
253 $sequence_table_name = $table_name .
'_seq';
255 $last_insert_id = $this->pdo->lastInsertId($table_name);
256 if ($last_insert_id) {
261 $stmt = $this->pdo->prepare(
"SELECT sequence FROM $sequence_table_name");
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));
286 public function createTable($table_name, $fields, $drop_table =
false, $ignore_erros =
false)
302 return $this->manager->createTable($table_name, $fields, array());
312 foreach ($a_cols as $col =>
$def) {
348 return $this->field_definition->checkColumnDefinition($a_def);
358 return $this->field_definition->checkColumnName($a_name);
370 assert(is_array($primary_keys));
373 foreach ($primary_keys as $f) {
374 $fields[$f] = array();
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']);
398 if ($idx_fields === $fields) {
399 return $this->
dropIndex($table_name, $idx_name);
422 $this->manager->createSequence($table_name, $start);
433 $result = $this->pdo->prepare(
"SHOW TABLES LIKE :table_name");
434 $result->execute(array(
'table_name' => $table_name));
452 $in_array = in_array($column_name, $fields);
468 throw new ilDatabaseException(
"ilDB Error: addTableColumn(" . $table_name .
", " . $column_name .
")");
471 throw new ilDatabaseException(
"ilDB Error: addTableColumn(" . $table_name .
", " . $column_name .
")");
480 return $this->manager->alterTable($table_name, $changes,
false);
490 public function dropTable($table_name, $error_if_not_existing =
true)
493 $tables = $ilDBPdoManager->listTables();
494 $table_exists = in_array($table_name, $tables);
495 if (!$table_exists && $error_if_not_existing) {
500 $sequences = $ilDBPdoManager->listSequences();
501 if (in_array($table_name, $sequences)) {
502 $ilDBPdoManager->dropSequence($table_name);
507 $ilDBPdoManager->dropTable($table_name);
535 }
catch (PDOException $e) {
539 $err = $this->pdo->errorCode();
540 if ($err != PDO::ERR_NONE) {
541 $info = $this->pdo->errorInfo();
542 $info_message =
$info[2];
561 while (
$data = $query_result->fetch($fetch_mode)) {
574 $this->manager->dropSequence($table_name);
588 $column_name => array(),
592 return $this->manager->alterTable($table_name, $changes,
false);
607 throw new ilDatabaseException(
"ilDB Error: renameTableColumn(" . $table_name .
"," . $column_old_name .
"," . $column_new_name .
")");
610 $def = $this->reverse->getTableFieldDefinition($table_name, $column_old_name);
613 $best_alt = $analyzer->getBestDefinitionAlternative(
$def);
615 unset(
$def[
"nativetype"]);
616 unset(
$def[
"mdb2type"]);
618 $f[
"definition"] =
$def;
619 $f[
"name"] = $column_new_name;
623 $column_old_name => $f,
627 return $this->manager->alterTable($table_name, $changes,
false);
636 public function insert($table_name, $values)
640 foreach ($values as
$key => $val) {
641 $real[] = $this->
quote($val[1], $val[0]);
644 $values = implode(
",", $real);
645 $fields = implode(
",", $fields);
646 $query =
"INSERT INTO " . $table_name .
" (" . $fields .
") VALUES (" . $values .
")";
650 return $this->pdo->exec(
$query);
661 $res = $query_result->fetchObject();
663 $query_result->closeCursor();
681 $field_values = array();
682 $placeholders = array();
683 $placeholders_full = array();
689 $field_value = $col[1];
691 $placeholders[] =
"%s";
692 $placeholders_full[] =
":$k";
695 if ($col[0] ==
"blob" || $col[0] ==
"clob" || $col[0] ==
'text') {
700 if ($col[0] ==
'integer' && !is_null($field_value)) {
701 $field_value = (int) $field_value;
704 $values[] = $field_value;
705 $field_values[$k] = $field_value;
706 if ($col[0] ==
"blob" || $col[0] ==
"clob") {
713 $q =
"UPDATE " . $table_name .
" SET ";
715 foreach ($fields as $k => $field) {
716 $q .= $lim . $field .
" = " . $placeholders_full[$k];
721 foreach ($where as $k => $col) {
722 $q .= $lim . $k .
" = " . $this->
quote($col[1], $col[0]);
730 foreach ($where as $k => $col) {
733 $field_values[$k] = $col;
735 $q =
"UPDATE " . $table_name .
" SET ";
737 foreach ($fields as $k => $field) {
738 $q .= $lim . $this->
quoteIdentifier($field) .
" = " . $placeholders[$k];
743 foreach ($where as $k => $col) {
744 $q .= $lim . $k .
" = %s";
774 }
catch (PDOException $e) {
789 $res = $query_result->fetch(PDO::FETCH_ASSOC);
791 $query_result->closeCursor();
807 return $query_result->rowCount();
819 if ($value ===
null) {
823 $pdo_type = PDO::PARAM_STR;
833 $value = (int) $value;
838 $pdo_type = PDO::PARAM_INT;
842 $pdo_type = PDO::PARAM_STR;
846 return $this->pdo->quote($value, $pdo_type);
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']);
862 if ($idx_fields === $fields) {
877 public function addIndex($table_name, $fields, $index_name =
'', $fulltext =
false)
879 assert(is_array($fields));
880 $this->field_definition->checkIndexName($index_name);
882 $definition_fields = array();
883 foreach ($fields as $f) {
884 $definition_fields[$f] = array();
887 'fields' => $definition_fields,
891 $this->manager->createIndex($table_name, $this->
constraintName($table_name, $index_name), $definition);
912 $f_str = implode($a_fields,
",");
913 $q =
"ALTER TABLE $a_table ADD FULLTEXT $i_name ($f_str)";
924 $this->
query(
"ALTER TABLE $a_table DROP FULLTEXT $i_name");
933 $set = $this->
query(
"SHOW INDEX FROM " . $a_table);
935 if ($rec[
"Key_name"] == $a_name && $rec[
"Index_type"] ==
"FULLTEXT") {
971 return $a_constraint;
999 $this->db_type =
$type;
1014 return $ilDB->getFieldDefinition()->getReservedMysql();
1024 assert(is_array($tables));
1026 $lock = $this->manager->getQueryUtils()->lock($tables);
1029 $ilLog->write(
'ilDB::lockTables(): ' . $lock);
1032 $this->pdo->exec($lock);
1042 $this->pdo->exec($this->manager->getQueryUtils()->unlock());
1053 public function in($field, $values, $negate =
false,
$type =
"")
1055 return $this->manager->getQueryUtils()->in($field, $values, $negate,
$type);
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)");
1071 $quoted_values = array();
1072 foreach ($types as $k =>
$t) {
1073 $quoted_values[] = $this->
quote($values[$k],
$t);
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)");
1093 $quoted_values = array();
1094 foreach ($types as $k =>
$t) {
1095 $quoted_values[] = $this->
quote($values[$k],
$t);
1138 return $this->manager->getQueryUtils()->like(
$column,
$type, $value, $case_insensitive);
1147 return $this->manager->getQueryUtils()->now();
1161 $a_columns = array_merge($primaryKeys, $otherColumns);
1163 $field_values = array();
1164 $placeholders = array();
1168 foreach ($a_columns as $k => $col) {
1170 $placeholders[] =
"%s";
1171 $placeholders2[] =
":$k";
1175 if ($col[0] ==
'integer' && !is_null($col[1])) {
1176 $col[1] = (int) $col[1];
1179 $values[] = $col[1];
1180 $field_values[$k] = $col[1];
1183 $q =
"REPLACE INTO " .
$table .
" (" . implode($fields,
",") .
") VALUES (" . implode($placeholders,
",") .
")";
1200 if (!$emptyOrNull || $value !=
"") {
1203 return "(" .
$columns .
" = '' OR $columns IS NULL)";
1222 $this->host =
$host;
1312 $this->port =
$port;
1358 return " UPPER(" . $a_exp .
") ";
1368 return " LOWER(" . $a_exp .
") ";
1378 public function substr($a_exp, $a_pos = 1, $a_len = -1)
1382 $lenstr =
", " . $a_len;
1385 return " SUBSTR(" . $a_exp .
", " . $a_pos . $lenstr .
") ";
1417 $this->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, $a_status);
1491 return $this->manager->listTables();
1515 return $this->field_definition->getAllowedAttributes();
1534 return $this->manager->listSequences();
1543 public function concat(array $values, $allow_null =
true)
1545 return $this->manager->getQueryUtils()->concat($values, $allow_null);
1555 if ($this->limit !==
null && $this->offset !==
null) {
1557 $this->limit =
null;
1558 $this->offset =
null;
1573 public function locate($a_needle, $a_string, $a_start_pos = 1)
1575 return $this->manager->getQueryUtils()->locate($a_needle, $a_string, $a_start_pos);
1588 $def = $this->reverse->getTableFieldDefinition(
$table, $a_column);
1591 $best_alt = $analyzer->getBestDefinitionAlternative(
$def);
1593 unset(
$def[
"nativetype"]);
1594 unset(
$def[
"mdb2type"]);
1599 $type = ($a_attributes[
"type"] !=
"") ? $a_attributes[
"type"] :
$def[
"type"];
1600 foreach (
$def as $k => $v) {
1601 if ($k !=
"type" && !$ilDBPdoFieldDefinition->isAllowedAttribute($k,
$type)) {
1605 $check_array =
$def;
1606 foreach ($a_attributes as $k => $v) {
1607 $check_array[$k] = $v;
1613 foreach ($a_attributes as $a => $v) {
1617 $a_attributes[
"definition"] =
$def;
1621 $a_column => $a_attributes,
1625 return $this->manager->alterTable(
$table, $changes,
false);
1633 public function free($a_st)
1638 return $a_st->closeCursor();
1654 throw new ilDatabaseException(
"ilDB Error: renameTable(" . $a_name .
"," . $a_new_name .
")<br />" . $e->getMessage());
1657 $this->manager->alterTable($a_name, array(
"name" => $a_new_name),
false);
1675 return $this->field_definition->checkTableName($a_name);
1685 require_once(
'./Services/Database/classes/PDO/FieldDefinition/class.ilDBPdoMySQLFieldDefinition.php');
1689 return $ilDBPdoMySQLFieldDefinition->isReserved($a_word);
1699 if (!$this->
supports(self::FEATURE_TRANSACTIONS)) {
1703 return $this->pdo->beginTransaction();
1713 if (!$this->
supports(self::FEATURE_TRANSACTIONS)) {
1717 return $this->pdo->commit();
1727 if (!$this->
supports(self::FEATURE_TRANSACTIONS)) {
1731 return $this->pdo->rollBack();
1742 return $this->manager->dropIndex($a_table, $a_name);
1774 $type = PDO::FETCH_ASSOC;
1777 $type = PDO::FETCH_OBJ;
1780 $type = PDO::FETCH_ASSOC;
1784 return $this->pdo->query(
$query, PDO::FETCH_ASSOC)->fetchAll(PDO::FETCH_COLUMN, $colnum);
1796 switch ($fetchmode) {
1798 $type = PDO::FETCH_ASSOC;
1801 $type = PDO::FETCH_OBJ;
1804 $type = PDO::FETCH_ASSOC;
1818 return $this->pdo->query(
'SELECT VERSION()')->fetchColumn();
1827 public function escape($value, $escape_wildcards =
false)
1900 assert(is_array($fields));
1908 $fields_corrected = array();
1909 foreach ($fields as $f) {
1910 $fields_corrected[$f] = array();
1912 $definition = array(
1914 'fields' => $fields_corrected,
1928 return $this->manager->dropConstraint($a_table, $this->
constraintName($a_table, $a_name),
false);
1940 $cons = $analyzer->getConstraintsInformation($a_table);
1941 foreach ($cons as $c) {
1942 if ($c[
"type"] ==
"unique" && count($a_fields) == count($c[
"fields"])) {
1944 foreach ($a_fields as $f) {
1945 if (!isset($c[
"fields"][$f])) {
1964 return $this->pdo->lastInsertId();
1973 require_once(
'./Services/Database/classes/Atom/class.ilAtomQueryLock.php');
1986 require_once(
'./Services/Database/classes/class.ilDBAnalyzer.php');
1988 $cons = $analyzer->getConstraintsInformation(
$table);
1989 foreach ($cons as $c) {
1990 if ($c[
"type"] ==
"unique" && count($fields) == count($c[
"fields"])) {
1992 foreach ($fields as $f) {
1993 if (!isset($c[
"fields"][$f])) {
2013 return $this->manager->dropConstraint($table_name,
"PRIMARY",
true);
2023 for (
$i = 0, $j = count($a_data);
$i < $j;
$i++) {
2024 $stmt->execute($a_data[
$i]);
2036 return "FROM_UNIXTIME(" . $a_expr .
")";
2045 return "UNIX_TIMESTAMP()";
2066 $fields_values = (array) $fields;
2068 if (!empty($fields_values)) {
2069 $keys = $fields_values;
2074 $keys = array_keys($fields_values);
2076 $params = array_values($fields_values);
2081 $stmt = $this->
autoPrepare($tablename,
$keys, $mode, $where, $types, $result_types);
2118 if ($this->options[
'quote_identifier']) {
2122 if (!empty($table_fields) && $this->options[
'quote_identifier']) {
2123 foreach ($table_fields as
$key => $field) {
2128 if ($where !==
false && !is_null($where)) {
2129 if (is_array($where)) {
2130 $where = implode(
' AND ', $where);
2132 $where =
' WHERE ' . $where;
2137 if (empty($table_fields)) {
2140 $cols = implode(
', ', $table_fields);
2141 $values =
'?' . str_repeat(
', ?', (count($table_fields) - 1));
2143 return 'INSERT INTO ' .
$table .
' (' .
$cols .
') VALUES (' . $values .
')';
2146 if (empty($table_fields)) {
2149 $set = implode(
' = ?, ', $table_fields) .
' = ?';
2150 $sql =
'UPDATE ' .
$table .
' SET ' . $set . $where;
2155 $sql =
'DELETE FROM ' .
$table . $where;
2160 $cols = !empty($table_fields) ? implode(
', ', $table_fields) :
'*';
2161 $sql =
'SELECT ' .
$cols .
' FROM ' .
$table . $where;
2179 return (
$d->version ?
$d->version :
'Unknown');
2189 $query_replaced = preg_replace(
2190 '/[\x{10000}-\x{10FFFF}]/u',
2194 if (!empty($query_replaced)) {
2195 return $query_replaced;
2214 public function groupConcat($a_field_name, $a_seperator =
",", $a_order =
null)
2216 return $this->manager->getQueryUtils()->groupConcat($a_field_name, $a_seperator, $a_order);
2222 public function cast($a_field_name, $a_dest_type)
2224 return $this->manager->getQueryUtils()->cast($a_field_name, $a_dest_type);
sprintf('%.4f', $callTime)
if(!isset( $_REQUEST[ 'ReturnTo'])) if(!isset($_REQUEST['AuthId'])) $options
An exception for terminatinating execution or to throw for unit testing.
performance measurement class
This class gives all kind of DB information using the MDB2 manager and reverse module.
const MDB2_AUTOQUERY_INSERT
const MDB2_AUTOQUERY_DELETE
const MDB2_AUTOQUERY_SELECT
const MYSQL_ENGINE_INNODB
const MDB2_AUTOQUERY_UPDATE
const MYSQL_COLLATION_UTF8MB4
Class ilDBPdoMySQLFieldDefinition.
update($table_name, $columns, $where)
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)
sanitizeMB4StringIfNotSupported($query)
string sanitized query
createSequence($table_name, $start=1)
indexExistsByFields($table_name, $fields)
equals($columns, $value, $type, $emptyOrNull=false)
escape($value, $escape_wildcards=false)
quote($value, $type=null)
setStorageEngine($storage_engine)
like($column, $type, $value="?", $case_insensitive=true)
setFieldDefinition($field_definition)
in($field, $values, $negate=false, $type="")
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)
dropIndex($a_table, $a_name="i1")
supportsEngineMigration()
dropUniqueConstraint($a_table, $a_name="con")
getPrimaryKeyIdentifier()
getAllowedAttributes()
array
executeMultiple($stmt, $a_data)
isFulltextIndex($a_table, $a_name)
Is index a fulltext index?
queryF($query, $types, $values)
getSequenceName($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()
supportsCollationMigration()
@inheritDoc
manipulateF($query, $types, $values)
checkTableColumns($a_cols)
replace($table, $primaryKeys, $otherColumns)
Replace into method.
static isReservedWord($a_word)
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)
constraintName($a_table, $a_constraint)
Determine contraint name by table name and constraint name.
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;
quoteIdentifier($identifier, $check_option=false)
checkColumnDefinition($a_def, $a_modify_mode=false)
dropFulltextIndex($a_table, $a_name)
Drop fulltext index.
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)
migrateAllTablesToCollation($collation=ilDBConstants::MYSQL_COLLATION_UTF8MB4)
@inheritDoc
fetchObject($query_result)
dropIndexByFields($table_name, $fields)
queryCol($query, $type=PDO::FETCH_ASSOC, $colnum=0)
Class ilDatabaseException.
Class ilPDOStatement is a Wrapper Class for PDOStatement.
for( $i=6;$i< 13;$i++) for($i=1; $i< 13; $i++) $d
static getReservedWords()
Get reserved words.
execute($stmt, $data=array())
fetchAll($query_result, $fetch_mode=ilDBConstants::FETCHMODE_ASSOC)
Interface ilDBPdoInterface.
if($modEnd===false) $module
if(empty($password)) $table
foreach($_POST as $key=> $value) $res