ilDBPdoMySQL Class Reference

Class ilDBPdoMySQL. More...

Inheritance diagram for ilDBPdoMySQL:

Collaboration diagram for ilDBPdoMySQL:

Public Member Functions
	supportsTransactions ()
	initHelpers ()
	supportsEngineMigration ()
	migrateTableToEngine (string $table_name, string $engine=ilDBConstants::MYSQL_ENGINE_INNODB)
	migrateAllTablesToEngine (string $engine=ilDBConstants::MYSQL_ENGINE_INNODB)
	migrateTableCollation (string $table_name, string $collation=ilDBConstants::MYSQL_COLLATION_UTF8MB4)
	migrateAllTablesToCollation (string $collation=ilDBConstants::MYSQL_COLLATION_UTF8MB4)
	supportsCollationMigration ()
	nextId (string $table_name)
	doesCollationSupportMB4Strings ()
Public Member Functions inherited from ilDBPdo
	connect (bool $return_false_for_error=false)
	initHelpers ()
	getFieldDefinition ()
	setFieldDefinition (\ilDBPdoFieldDefinition $field_definition)
	createDatabase (string $a_name, string $a_charset="utf8", string $a_collation="")
	getLastErrorCode ()
	initFromIniFile (?ilIniFile $ini=null)
	generateDSN ()
	quoteIdentifier (string $identifier, bool $check_option=false)
	nextId (string $table_name)
	createTable (string $table_name, array $fields, bool $drop_table=false, bool $ignore_erros=false)
	checkColumnName (string $a_name)
	addPrimaryKey (string $table_name, array $primary_keys)
	dropIndexByFields (string $table_name, array $fields)
	getPrimaryKeyIdentifier ()
	createSequence (string $table_name, int $start=1)
	tableExists (string $table_name)
	tableColumnExists (string $table_name, string $column_name)
	addTableColumn (string $table_name, string $column_name, array $attributes)
	dropTable (string $table_name, bool $error_if_not_existing=true)
	query (string $query)
	fetchAll (ilDBStatement $statement, int $fetch_mode=ilDBConstants::FETCHMODE_ASSOC)
	dropSequence (string $table_name)
	dropTableColumn (string $table_name, string $column_name)
	renameTableColumn (string $table_name, string $column_old_name, string $column_new_name)
	insert (string $table_name, array $values)
	fetchObject (ilDBStatement $query_result)
	update (string $table_name, array $columns, array $where)
	$where MUST contain existing columns only. More...
	manipulate (string $query)
	fetchAssoc (ilDBStatement $statement)
	numRows (ilDBStatement $statement)
	quote ($value, ?string $type=null)
	indexExistsByFields (string $table_name, array $fields)
	addIndex (string $table_name, array $fields, string $index_name='', bool $fulltext=false)
	addFulltextIndex (string $table, array $fields, string $a_name="in")
	dropFulltextIndex (string $a_table, string $a_name)
	Drop fulltext index. More...
	isFulltextIndex (string $a_table, string $a_name)
	Is index a fulltext index? More...
	getIndexName (string $index_name_base)
	getSequenceName (string $table_name)
	constraintName (string $a_table, string $a_constraint)
	Determine contraint name by table name and constraint name. More...
	getDSN ()
	Get DSN. More...
	getDBType ()
	Get DSN. More...
	setDBType (string $type)
	lockTables (array $tables)
	unlockTables ()
	in (string $field, array $values, bool $negate=false, string $type="")
	queryF (string $query, array $types, array $values)
	manipulateF (string $query, array $types, array $values)
	useSlave (bool $bool)
	TODO. More...
	setLimit (int $limit, int $offset=0)
	Set the Limit for the next Query. More...
	like (string $column, string $type, string $value="?", bool $case_insensitive=true)
	now ()
	replace (string $table, array $primary_keys, array $other_columns)
	Replace into method. More...
	equals (string $columns, $value, string $type, bool $emptyOrNull=false)
	getHost ()
	setHost (string $host)
	getDbname ()
	setDbname (string $dbname)
	getCharset ()
	setCharset (string $charset)
	getUsername ()
	setUsername (string $username)
	getPassword ()
	setPassword (string $password)
	getPort ()
	setPort (int $port)
	setDBUser (string $user)
	setDBPort (int $port)
	setDBPassword (string $password)
	setDBHost (string $host)
	upper (string $expression)
	lower (string $expression)
	substr (string $a_exp, int $a_pos=1, int $a_len=-1)
	prepareManip (string $query, ?array $types=null)
	prepare (string $query, ?array $types=null, ?array $result_types=null)
	Prepare a query (SELECT) statement to be used with execute. More...
	enableResultBuffering (bool $a_status)
	supportsSlave ()
	supportsFulltext ()
	supportsTransactions ()
	supports (string $feature)
	listTables ()
	loadModule (string $module)
	getAllowedAttributes ()
	Returns string[] More...
	sequenceExists (string $sequence)
	listSequences ()
	concat (array $values, bool $allow_null=true)
	locate (string $needle, string $string, int $start_pos=1)
	modifyTableColumn (string $table, string $column, array $attributes)
	free (ilDBStatement $a_st)
	renameTable (string $name, string $new_name)
	checkTableName (string $a_name)
	beginTransaction ()
	commit ()
	rollback ()
	dropIndex (string $a_table, string $a_name="i1")
	setStorageEngine (string $storage_engine)
	getStorageEngine ()
	queryCol (string $query, int $type=PDO::FETCH_ASSOC, int $colnum=0)
	queryRow (string $query, ?array $types=null, int $fetchmode=ilDBConstants::FETCHMODE_DEFAULT)
	getServerVersion (bool $native=false)
	escape (string $value, bool $escape_wildcards=false)
	escapePattern (string $text)
	migrateAllTablesToEngine (string $engine=ilDBConstants::MYSQL_ENGINE_INNODB)
	migrateAllTablesToCollation (string $collation=ilDBConstants::MYSQL_COLLATION_UTF8MB4)
	supportsCollationMigration ()
	supportsEngineMigration ()
	checkIndexName (string $name)
	addUniqueConstraint (string $table, array $fields, string $name="con")
	dropUniqueConstraint (string $table, string $name="con")
	dropUniqueConstraintByFields (string $table, array $fields)
	getLastInsertId ()
	buildAtomQuery ()
	uniqueConstraintExists (string $table, array $fields)
	dropPrimaryKey (string $table_name)
	executeMultiple (ilDBStatement $stmt, array $data)
	fromUnixtime (string $expr, bool $to_text=true)
	unixTimestamp ()
	getDBVersion ()
	sanitizeMB4StringIfNotSupported (string $query)
	Parameters $query string to sanitize, all MB4-Characters like emojis will re replaced with ??? Returns string sanitized query More...
	doesCollationSupportMB4Strings ()
	groupConcat (string $a_field_name, string $a_seperator=",", ?string $a_order=null)
	cast (string $a_field_name, string $a_dest_type)
	addForeignKey (string $foreign_key_name, array $field_names, string $table_name, array $reference_field_names, string $reference_table, ?ForeignKeyConstraints $on_update=null, ?ForeignKeyConstraints $on_delete=null)
	dropForeignKey (string $foreign_key_name, string $table_name)
	foreignKeyExists (string $foreign_key_name, string $table_name)
	buildIntegrityAnalyser ()
	primaryExistsByFields (string $table_name, array $fields)
Public Member Functions inherited from ilDBInterface
	quote ($value, string $type)
	substr (string $expression)
	execute (ilDBStatement $stmt, array $data=[])

Protected Member Functions
	initSQLMode ()
	getAdditionalAttributes ()
Protected Member Functions inherited from ilDBPdo
	initSQLMode ()
	getAttributes ()
	getAdditionalAttributes ()
	checkTableColumns (array $a_cols)
	checkColumn (string $a_col, array $a_def)
	checkColumnDefinition (array $a_def, bool $a_modify_mode=false)
	appendLimit (string $query)

Protected Attributes
array	$modes
Protected Attributes inherited from ilDBPdo
string	$host = ''
string	$dbname = ''
string	$charset = 'utf8'
string	$username = ''
string	$password = ''
int	$port = 3306
PDO	$pdo = null
ilDBPdoManager	$manager
ilDBPdoReverse	$reverse
int	$limit = null
int	$offset = null
string	$storage_engine = 'InnoDB'
string	$dsn = ''
array	$attributes
array string	$db_type = ''
int	$error_code = 0
ilDBPdoFieldDefinition	$field_definition = null

Additional Inherited Members
Static Public Member Functions inherited from ilDBPdo
static	isReservedWord (string $a_word)
Static Public Member Functions inherited from ilDBInterface
static	getReservedWords ()
	Get reserved words. More...
Data Fields inherited from ilDBPdo
array	$options = []
const	FEATURE_TRANSACTIONS = 'transactions'
const	FEATURE_FULLTEXT = 'fulltext'
const	FEATURE_SLAVE = 'slave'

Detailed Description

Class ilDBPdoMySQL.

Author

Fabian Schmid fs@st.nosp@m.uder.nosp@m.-raim.nosp@m.ann..nosp@m.ch

Definition at line 24 of file class.ilDBPdoMySQL.php.

Member Function Documentation

doesCollationSupportMB4Strings()

ilDBPdoMySQL::doesCollationSupportMB4Strings

(

)

Implements ilDBInterface.

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

                                                    : bool
    {
        // Currently ILIAS does not support utf8mb4, after that ilDB could check like this:
        //              static $supported;
        //              if (!isset($supported)) {
        //                      $q = "SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = %s;";
        //                      $res = $this->queryF($q, ['text'], [$this->getDbname()]);
        //                      $data = $this->fetchObject($res);
        //                      $supported = ($data->default_character_set_name === 'utf8mb4');
        //              }

        return false;
    }

getAdditionalAttributes()

ilDBPdoMySQL::getAdditionalAttributes ( )

protected

Returns

array<int, int|bool>

Definition at line 67 of file class.ilDBPdoMySQL.php.

                                                : array
    {
        return [
            PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
            PDO::ATTR_TIMEOUT => 300 * 60,
        ];
    }

initHelpers()

ilDBPdoMySQL::initHelpers

(

)

Definition at line 45 of file class.ilDBPdoMySQL.php.

                                 : void
    {
        $this->manager = new ilDBPdoManager($this->pdo, $this);
        $this->reverse = new ilDBPdoReverse($this->pdo, $this);
        $this->field_definition = new ilDBPdoMySQLFieldDefinition($this);
    }

initSQLMode()

ilDBPdoMySQL::initSQLMode ( )

protected

Definition at line 52 of file class.ilDBPdoMySQL.php.

                                    : void
    {
        $this->pdo->exec("SET SESSION sql_mode = '" . implode(",", $this->modes) . "';");
    }

migrateAllTablesToCollation()

ilDBPdoMySQL::migrateAllTablesToCollation

(

string

$collation = ilDBConstants::MYSQL_COLLATION_UTF8MB4

)

Implements ilDBPdoInterface.

Definition at line 133 of file class.ilDBPdoMySQL.php.

References ilDBPdo\$manager, ilDBPdoManager\listTables(), ilDBPdo\loadModule(), migrateTableCollation(), and ilDBConstants\MODULE_MANAGER.

                                                                                                         : array
    {
        $manager = $this->loadModule(ilDBConstants::MODULE_MANAGER);
        $errors = [];
        foreach ($manager->listTables() as $table_name) {
            if (!$this->migrateTableCollation($table_name, $collation)) {
                $errors[] = $table_name;
            }
        }

        return $errors;
    }

Here is the call graph for this function:

migrateAllTablesToEngine()

ilDBPdoMySQL::migrateAllTablesToEngine

(

string

$engine = ilDBConstants::MYSQL_ENGINE_INNODB

)

Returns

array<int|string, string>

Implements ilDBPdoInterface.

Definition at line 91 of file class.ilDBPdoMySQL.php.

References Vendor\Package\$e, ilDBPdo\listTables(), ilDBPdo\queryCol(), and ilDBPdo\sequenceExists().

                                                                                               : array
    {
        $engines = $this->queryCol('SHOW ENGINES');
        if (!in_array($engine, $engines, true)) {
            return [];
        }
        $errors = [];
        $tables = $this->listTables();
        array_walk($tables, function (string $table_name) use (&$errors, $engine): void {
            try {
                $this->pdo->exec("ALTER TABLE $table_name ENGINE=$engine");
                if ($this->sequenceExists($table_name)) {
                    $this->pdo->exec("ALTER TABLE {$table_name}_seq ENGINE=$engine");
                }
            } catch (Exception $e) {
                $errors[$table_name] = $e->getMessage();
            }
        });

        return $errors;
    }

Here is the call graph for this function:

migrateTableCollation()

ilDBPdoMySQL::migrateTableCollation	(	string	$table_name,
		string	$collation = ilDBConstants::MYSQL_COLLATION_UTF8MB4
	)

Implements ilDBPdoInterface.

Definition at line 113 of file class.ilDBPdoMySQL.php.

References $q.

Referenced by migrateAllTablesToCollation().

     : bool {
        $collation_split = explode("_", $collation);
        $character = $collation_split[0] ?? 'utf8mb4';
        $collate = $collation;
        $q = "ALTER TABLE {$this->quoteIdentifier($table_name)} CONVERT TO CHARACTER SET {$character} COLLATE {$collate};";
        try {
            $this->pdo->exec($q);
        } catch (PDOException) {
            return false;
        }
        return true;
    }

Here is the caller graph for this function:

migrateTableToEngine()

ilDBPdoMySQL::migrateTableToEngine	(	string	$table_name,
		string	$engine = ilDBConstants::MYSQL_ENGINE_INNODB
	)

Implements ilDBPdoInterface.

Definition at line 75 of file class.ilDBPdoMySQL.php.

References Vendor\Package\$e, and ilDBPdo\sequenceExists().

                                                                                                               : void
    {
        try {
            $this->pdo->exec("ALTER TABLE {$table_name} ENGINE={$engine}");
            if ($this->sequenceExists($table_name)) {
                $this->pdo->exec("ALTER TABLE {$table_name}_seq ENGINE={$engine}");
            }
        } catch (PDOException $e) {
            throw new ilDatabaseException($e->getMessage(), $e->getCode(), $e);
        }
    }

Here is the call graph for this function:

nextId()

ilDBPdoMySQL::nextId

(

string

$table_name

)

Implements ilDBInterface.

Definition at line 155 of file class.ilDBPdoMySQL.php.

References ilDBPdo\getSequenceName(), ilDBPdo\query(), and ilDBPdo\quoteIdentifier().

                                              : int
    {
        $sequence_name = $this->quoteIdentifier($this->getSequenceName($table_name), true);
        $seqcol_name = $this->quoteIdentifier('sequence');
        $query = "INSERT INTO $sequence_name ($seqcol_name) VALUES (NULL)";
        try {
            $this->pdo->exec($query);
        } catch (PDOException) {
            // no such table check
        }

        $result = $this->query('SELECT LAST_INSERT_ID() AS next');
        $value = $result->fetchObject()->next;

        if (is_numeric($value)) {
            $query = "DELETE FROM $sequence_name WHERE $seqcol_name < $value";
            $this->pdo->exec($query);
        }

        return (int) $value;
    }

Here is the call graph for this function:

supportsCollationMigration()

ilDBPdoMySQL::supportsCollationMigration

(

)

Implements ilDBPdoInterface.

Definition at line 150 of file class.ilDBPdoMySQL.php.

                                                : bool
    {
        return true;
    }

supportsEngineMigration()

ilDBPdoMySQL::supportsEngineMigration

(

)

Implements ilDBPdoInterface.

Definition at line 58 of file class.ilDBPdoMySQL.php.

                                             : bool
    {
        return true;
    }

supportsTransactions()

ilDBPdoMySQL::supportsTransactions

(

)

Implements ilDBInterface.

Definition at line 40 of file class.ilDBPdoMySQL.php.

                                          : bool
    {
        return false;
    }

Field Documentation

$modes

array ilDBPdoMySQL::$modes

protected

Initial value:

= [
        'STRICT_TRANS_TABLES',
        'STRICT_ALL_TABLES',
        'IGNORE_SPACE',
        'NO_ZERO_IN_DATE',
        'NO_ZERO_DATE',
        'ERROR_FOR_DIVISION_BY_ZERO',
        'NO_ENGINE_SUBSTITUTION',
    ]

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

---

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

• components/ILIAS/Database/classes/PDO/class.ilDBPdoMySQL.php