72        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
 
   93    public function connect($return_false_for_error = 
false)
 
  101        } 
catch (Exception $e) {
 
  102            $this->error_code = $e->getCode();
 
  103            if ($return_false_for_error) {
 
  109        return ($this->pdo->errorCode() == PDO::ERR_NONE);
 
  175            return $this->
query($this->manager->getQueryUtils()->createDatabase($a_name, $a_charset, $a_collation));
 
  176        } 
catch (PDOException $e) {
 
  187        if ($this->pdo instanceof PDO) {
 
  188            return $this->pdo->errorCode();
 
  202        if ($tmpClientIniFile instanceof 
ilIniFile) {
 
  203            $clientIniFile = $tmpClientIniFile;
 
  205            $ilClientIniFile = 
null;
 
  206            if (
$DIC->offsetExists(
'ilClientIniFile')) {
 
  207                $clientIniFile = 
$DIC[
'ilClientIniFile'];
 
  209                throw new InvalidArgumentException(
'$tmpClientIniFile is not an instance of ilIniFile');
 
  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"));
 
  240        return '`' . $identifier . 
'`';
 
  251        $sequence_table_name = $table_name . 
'_seq';
 
  253        $last_insert_id = $this->pdo->lastInsertId($table_name);
 
  254        if ($last_insert_id) {
 
  259            $stmt = $this->pdo->prepare(
"SELECT sequence FROM $sequence_table_name");
 
  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));
 
  284    public function createTable($table_name, $fields, $drop_table = 
false, $ignore_erros = 
false)
 
  300        return $this->manager->createTable($table_name, $fields, array());
 
  310        foreach ($a_cols as $col => 
$def) {
 
  346        return $this->field_definition->checkColumnDefinition($a_def);
 
  356        return $this->field_definition->checkColumnName($a_name);
 
  368        assert(is_array($primary_keys));
 
  371        foreach ($primary_keys as 
$f) {
 
  372            $fields[
$f] = array();
 
  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']);
 
  396            if ($idx_fields === $fields) {
 
  397                return $this->
dropIndex($table_name, $idx_name);
 
  420        $this->manager->createSequence($table_name, 
$start);
 
  431        $result = $this->pdo->prepare(
"SHOW TABLES LIKE :table_name");
 
  432        $result->execute(array(
'table_name' => $table_name));
 
  450        $in_array = in_array($column_name, $fields);
 
  466            throw new ilDatabaseException(
"ilDB Error: addTableColumn(" . $table_name . 
", " . $column_name . 
")");
 
  469            throw new ilDatabaseException(
"ilDB Error: addTableColumn(" . $table_name . 
", " . $column_name . 
")");
 
  478        return $this->manager->alterTable($table_name, $changes, 
false);
 
  488    public function dropTable($table_name, $error_if_not_existing = 
true)
 
  491        $tables = $ilDBPdoManager->listTables();
 
  492        $table_exists = in_array($table_name, $tables);
 
  493        if (!$table_exists && $error_if_not_existing) {
 
  498        $sequences = $ilDBPdoManager->listSequences();
 
  499        if (in_array($table_name, $sequences)) {
 
  500            $ilDBPdoManager->dropSequence($table_name);
 
  505            $ilDBPdoManager->dropTable($table_name);
 
  533        } 
catch (PDOException $e) {
 
  537        $err = $this->pdo->errorCode();
 
  538        if ($err != PDO::ERR_NONE) {
 
  539            $info = $this->pdo->errorInfo();
 
  540            $info_message = 
$info[2];
 
  559        while (
$data = $query_result->fetch($fetch_mode)) {
 
  572        $this->manager->dropSequence($table_name);
 
  586                $column_name => array(),
 
  590        return $this->manager->alterTable($table_name, $changes, 
false);
 
  605            throw new ilDatabaseException(
"ilDB Error: renameTableColumn(" . $table_name . 
"," . $column_old_name . 
"," . $column_new_name . 
")");
 
  608        $def = $this->reverse->getTableFieldDefinition($table_name, $column_old_name);
 
  611        $best_alt = $analyzer->getBestDefinitionAlternative(
$def);
 
  613        unset(
$def[
"nativetype"]);
 
  614        unset(
$def[
"mdb2type"]);
 
  617        $f[
"name"] = $column_new_name;
 
  621                $column_old_name => 
$f,
 
  625        return $this->manager->alterTable($table_name, $changes, 
false);
 
  639            $real[] = $this->
quote($val[1], $val[0]);
 
  643        $fields = implode(
",", $fields);
 
  644        $query = 
"INSERT INTO " . $table_name . 
" (" . $fields . 
") VALUES (" . 
$values . 
")";
 
  648        return $this->pdo->exec(
$query);
 
  659        $res = $query_result->fetchObject();
 
  661            $query_result->closeCursor();
 
  679        $field_values = array();
 
  680        $placeholders = array();
 
  681        $placeholders_full = array();
 
  687            $field_value = $col[1];
 
  689            $placeholders[] = 
"%s";
 
  690            $placeholders_full[] = 
":$k";
 
  693            if ($col[0] == 
"blob" || $col[0] == 
"clob" || $col[0] == 
'text') {
 
  698            if ($col[0] == 
'integer' && !is_null($field_value)) {
 
  699                $field_value = (int) $field_value;
 
  703            $field_values[$k] = $field_value;
 
  704            if ($col[0] == 
"blob" || $col[0] == 
"clob") {
 
  711            $q = 
"UPDATE " . $table_name . 
" SET ";
 
  713            foreach ($fields as $k => $field) {
 
  714                $q .= $lim . $field . 
" = " . $placeholders_full[$k];
 
  719            foreach ($where as $k => $col) {
 
  720                $q .= $lim . $k . 
" = " . $this->
quote($col[1], $col[0]);
 
  728            foreach ($where as $k => $col) {
 
  731                $field_values[$k] = $col;
 
  733            $q = 
"UPDATE " . $table_name . 
" SET ";
 
  735            foreach ($fields as $k => $field) {
 
  736                $q .= $lim . $this->
quoteIdentifier($field) . 
" = " . $placeholders[$k];
 
  741            foreach ($where as $k => $col) {
 
  742                $q .= $lim . $k . 
" = %s";
 
  772        } 
catch (PDOException $e) {
 
  787        $res = $query_result->fetch(PDO::FETCH_ASSOC);
 
  789            $query_result->closeCursor();
 
  805        return $query_result->rowCount();
 
  817        if ($value === 
null) {
 
  821        $pdo_type = PDO::PARAM_STR;
 
  831                $value = (int) $value;
 
  836                $pdo_type = PDO::PARAM_INT;
 
  840                $pdo_type = PDO::PARAM_STR;
 
  844        return $this->pdo->quote($value, $pdo_type);
 
  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']);
 
  860            if ($idx_fields === $fields) {
 
  875    public function addIndex($table_name, $fields, $index_name = 
'', $fulltext = 
false)
 
  877        assert(is_array($fields));
 
  878        $this->field_definition->checkIndexName($index_name);
 
  880        $definition_fields = array();
 
  881        foreach ($fields as 
$f) {
 
  882            $definition_fields[
$f] = array();
 
  885            'fields' => $definition_fields,
 
  889            $this->manager->createIndex($table_name, $this->
constraintName($table_name, $index_name), $definition);
 
  910        $f_str = implode(
",", $a_fields);
 
  911        $q = 
"ALTER TABLE $a_table ADD FULLTEXT $i_name ($f_str)";
 
  922        $this->
query(
"ALTER TABLE $a_table DROP FULLTEXT $i_name");
 
  931        $set = $this->
query(
"SHOW INDEX FROM " . $a_table);
 
  933            if ($rec[
"Key_name"] == $a_name && $rec[
"Index_type"] == 
"FULLTEXT") {
 
  968        return $a_constraint;
 
  996        $this->db_type = 
$type;
 
 1012        return $ilDB->getFieldDefinition()->getReservedMysql();
 
 1022        assert(is_array($tables));
 
 1024        $lock = $this->manager->getQueryUtils()->lock($tables);
 
 1026        $ilLogger = 
$DIC->logger()->root();
 
 1027        if ($ilLogger instanceof 
ilLogger) {
 
 1028            $ilLogger->log(
'ilDB::lockTables(): ' . $lock);
 
 1031        $this->pdo->exec($lock);
 
 1041        $this->pdo->exec($this->manager->getQueryUtils()->unlock());
 
 1054        return $this->manager->getQueryUtils()->in($field, 
$values, $negate, 
$type);
 
 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)");
 
 1070        $quoted_values = array();
 
 1071        foreach ($types as $k => 
$t) {
 
 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)");
 
 1092        $quoted_values = array();
 
 1093        foreach ($types as $k => 
$t) {
 
 1135    public function like($column, 
$type, $value = 
"?", $case_insensitive = 
true)
 
 1137        return $this->manager->getQueryUtils()->like($column, 
$type, $value, $case_insensitive);
 
 1146        return $this->manager->getQueryUtils()->now();
 
 1160        $a_columns = array_merge($primaryKeys, $otherColumns);
 
 1162        $field_values = array();
 
 1163        $placeholders = array();
 
 1167        foreach ($a_columns as $k => $col) {
 
 1169            $placeholders[] = 
"%s";
 
 1170            $placeholders2[] = 
":$k";
 
 1174            if ($col[0] == 
'integer' && !is_null($col[1])) {
 
 1175                $col[1] = (int) $col[1];
 
 1179            $field_values[$k] = $col[1];
 
 1182        $q = 
"REPLACE INTO " . 
$table . 
" (" . implode(
",", $fields) . 
") VALUES (" . implode(
",", $placeholders) . 
")";
 
 1199        if (!$emptyOrNull || $value != 
"") {
 
 1202            return "(" . 
$columns . 
" = '' OR $columns IS NULL)";
 
 1221        $this->host = 
$host;
 
 1311        $this->port = 
$port;
 
 1357        return " UPPER(" . $a_exp . 
") ";
 
 1367        return " LOWER(" . $a_exp . 
") ";
 
 1377    public function substr($a_exp, $a_pos = 1, $a_len = -1)
 
 1381            $lenstr = 
", " . $a_len;
 
 1384        return " SUBSTR(" . $a_exp . 
", " . $a_pos . $lenstr . 
") ";
 
 1416        $this->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, $a_status);
 
 1490        return $this->manager->listTables();
 
 1514        return $this->field_definition->getAllowedAttributes();
 
 1533        return $this->manager->listSequences();
 
 1544        return $this->manager->getQueryUtils()->concat(
$values, $allow_null);
 
 1554        if ($this->limit !== 
null && $this->offset !== 
null) {
 
 1556            $this->limit = 
null;
 
 1557            $this->offset = 
null;
 
 1572    public function locate($a_needle, $a_string, $a_start_pos = 1)
 
 1574        return $this->manager->getQueryUtils()->locate($a_needle, $a_string, $a_start_pos);
 
 1587        $def = $this->reverse->getTableFieldDefinition(
$table, $a_column);
 
 1590        $best_alt = $analyzer->getBestDefinitionAlternative(
$def);
 
 1592        unset(
$def[
"nativetype"]);
 
 1593        unset(
$def[
"mdb2type"]);
 
 1598        $type = ($a_attributes[
"type"] != 
"") ? $a_attributes[
"type"] : 
$def[
"type"];
 
 1599        foreach (
$def as $k => $v) {
 
 1600            if ($k != 
"type" && !$ilDBPdoFieldDefinition->isAllowedAttribute($k, 
$type)) {
 
 1604        $check_array = 
$def;
 
 1605        foreach ($a_attributes as $k => $v) {
 
 1606            $check_array[$k] = $v;
 
 1612        foreach ($a_attributes as $a => $v) {
 
 1616        $a_attributes[
"definition"] = 
$def;
 
 1620                $a_column => $a_attributes,
 
 1624        return $this->manager->alterTable(
$table, $changes, 
false);
 
 1632    public function free($a_st)
 
 1637        return $a_st->closeCursor();
 
 1653            throw new ilDatabaseException(
"ilDB Error: renameTable(" . $a_name . 
"," . $a_new_name . 
")<br />" . $e->getMessage());
 
 1656        $this->manager->alterTable($a_name, array(
"name" => $a_new_name), 
false);
 
 1674        return $this->field_definition->checkTableName($a_name);
 
 1684        require_once(
'./Services/Database/classes/PDO/FieldDefinition/class.ilDBPdoMySQLFieldDefinition.php');
 
 1688        return $ilDBPdoMySQLFieldDefinition->isReserved($a_word);
 
 1698        if (!$this->
supports(self::FEATURE_TRANSACTIONS)) {
 
 1702        return $this->pdo->beginTransaction();
 
 1712        if (!$this->
supports(self::FEATURE_TRANSACTIONS)) {
 
 1716        return $this->pdo->commit();
 
 1726        if (!$this->
supports(self::FEATURE_TRANSACTIONS)) {
 
 1730        return $this->pdo->rollBack();
 
 1741        return $this->manager->dropIndex($a_table, $a_name);
 
 1773                $type = PDO::FETCH_ASSOC;
 
 1776                $type = PDO::FETCH_OBJ;
 
 1779                $type = PDO::FETCH_ASSOC;
 
 1783        return $this->pdo->query(
$query, PDO::FETCH_ASSOC)->fetchAll(PDO::FETCH_COLUMN, $colnum);
 
 1795        switch ($fetchmode) {
 
 1797                $type = PDO::FETCH_ASSOC;
 
 1800                $type = PDO::FETCH_OBJ;
 
 1803                $type = PDO::FETCH_ASSOC;
 
 1817        return $this->pdo->query(
'SELECT VERSION()')->fetchColumn();
 
 1826    public function escape($value, $escape_wildcards = 
false)
 
 1899        assert(is_array($fields));
 
 1907        $fields_corrected = array();
 
 1908        foreach ($fields as 
$f) {
 
 1909            $fields_corrected[
$f] = array();
 
 1911        $definition = array(
 
 1913            'fields' => $fields_corrected,
 
 1927        return $this->manager->dropConstraint($a_table, $this->
constraintName($a_table, $a_name), 
false);
 
 1939        $cons = $analyzer->getConstraintsInformation($a_table);
 
 1940        foreach ($cons as 
$c) {
 
 1941            if (
$c[
"type"] == 
"unique" && count($a_fields) == count(
$c[
"fields"])) {
 
 1943                foreach ($a_fields as 
$f) {
 
 1944                    if (!isset(
$c[
"fields"][
$f])) {
 
 1963        return $this->pdo->lastInsertId();
 
 1972        require_once(
'./Services/Database/classes/Atom/class.ilAtomQueryLock.php');
 
 1985        require_once(
'./Services/Database/classes/class.ilDBAnalyzer.php');
 
 1987        $cons = $analyzer->getConstraintsInformation(
$table);
 
 1988        foreach ($cons as 
$c) {
 
 1989            if (
$c[
"type"] == 
"unique" && count($fields) == count(
$c[
"fields"])) {
 
 1991                foreach ($fields as 
$f) {
 
 1992                    if (!isset(
$c[
"fields"][
$f])) {
 
 2012        return $this->manager->dropConstraint($table_name, 
"PRIMARY", 
true);
 
 2022        for (
$i = 0, $j = count($a_data); 
$i < $j; 
$i++) {
 
 2035        return "FROM_UNIXTIME(" . $a_expr . 
")";
 
 2044        return "UNIX_TIMESTAMP()";
 
 2065        $fields_values = (array) $fields;
 
 2067            if (!empty($fields_values)) {
 
 2068                $keys = $fields_values;
 
 2073            $keys = array_keys($fields_values);
 
 2075        $params = array_values($fields_values);
 
 2117        if ($this->options[
'quote_identifier']) {
 
 2121        if (!empty($table_fields) && $this->options[
'quote_identifier']) {
 
 2122            foreach ($table_fields as 
$key => $field) {
 
 2127        if ($where !== 
false && !is_null($where)) {
 
 2128            if (is_array($where)) {
 
 2129                $where = implode(
' AND ', $where);
 
 2131            $where = 
' WHERE ' . $where;
 
 2136                if (empty($table_fields)) {
 
 2139                $cols = implode(
', ', $table_fields);
 
 2140                $values = 
'?' . str_repeat(
', ?', (count($table_fields) - 1));
 
 2145                if (empty($table_fields)) {
 
 2148                $set = implode(
' = ?, ', $table_fields) . 
' = ?';
 
 2149                $sql = 
'UPDATE ' . 
$table . 
' SET ' . $set . $where;
 
 2154                $sql = 
'DELETE FROM ' . 
$table . $where;
 
 2159                $cols = !empty($table_fields) ? implode(
', ', $table_fields) : 
'*';
 
 2160                $sql = 
'SELECT ' . 
$cols . 
' FROM ' . 
$table . $where;
 
 2178        return (
$d->version ? 
$d->version : 
'Unknown');
 
 2188            $query_replaced = preg_replace(
 
 2189                '/[\x{10000}-\x{10FFFF}]/u',
 
 2193            if (!empty($query_replaced)) {
 
 2194                return $query_replaced;
 
 2213    public function groupConcat($a_field_name, $a_seperator = 
",", $a_order = 
null)
 
 2215        return $this->manager->getQueryUtils()->groupConcat($a_field_name, $a_seperator, $a_order);
 
 2221    public function cast($a_field_name, $a_dest_type)
 
 2223        return $this->manager->getQueryUtils()->cast($a_field_name, $a_dest_type);
 
An exception for terminatinating execution or to throw for unit testing.
performance measurement class
This class gives all kind of DB information using the database manager and reverse module.
const MYSQL_ENGINE_INNODB
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)
initFromIniFile($tmpClientIniFile=null)
addUniqueConstraint($table, $fields, $name="con")
getAdditionalAttributes()
autoExecute($tablename, $fields, $mode=ilDBConstants::AUTOQUERY_INSERT, $where=false)
Generate an insert, update or delete query and call prepare() and execute() on it.
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)
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.
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;
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.
Component logger with individual log levels by component id.
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