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)
	@inheritDoc More...
	supportsCollationMigration ()
	@inheritDoc More...
	nextId (string $table_name)
	doesCollationSupportMB4Strings ()
	@inheritDoc More...
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)
	@description $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)
	@inheritDoc More...
	supportsCollationMigration ()
	@inheritDoc More...
	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 ()
	@inheritDoc More...
	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
	doesCollationSupportMB4Strings ()
	sanitizeMB4StringIfNotSupported (string $query)
	initFromIniFile (?ilIniFile $ini=null)
	connect (bool $return_false_on_error=false)
	nextId (string $table_name)
	createTable (string $table_name, array $fields, bool $drop_table=false, bool $ignore_erros=false)
	addPrimaryKey (string $table_name, array $primary_keys)
	createSequence (string $table_name, int $start=1)
	getSequenceName (string $table_name)
	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)
	renameTable (string $old_name, string $new_name)
	query (string $query)
	Run a (read-only) Query on the database. More...
	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 $values, array $where)
	@description $where MUST contain existing columns only. More...
	manipulate (string $query)
	Run a (write) Query on the database. More...
	fetchAssoc (ilDBStatement $statement)
	numRows (ilDBStatement $statement)
	quote ($value, string $type)
	addIndex (string $table_name, array $fields, string $index_name='', bool $fulltext=false)
	indexExistsByFields (string $table_name, array $fields)
	getDSN ()
	Get DSN. More...
	getDBType ()
	Get DSN. More...
	lockTables (array $tables)
	Abstraction of lock table. More...
	unlockTables ()
	Unlock tables locked by previous lock table calls. More...
	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)
	setLimit (int $limit, int $offset=0)
	like (string $column, string $type, string $value="?", bool $case_insensitive=true)
	Generate a like subquery. More...
	now ()
	replace (string $table, array $primary_keys, array $other_columns)
	Replace into method. More...
	equals (string $columns, $value, string $type, bool $emptyOrNull=false)
	setDBUser (string $user)
	setDBPort (int $port)
	setDBPassword (string $password)
	setDBHost (string $host)
	upper (string $expression)
	lower (string $expression)
	substr (string $expression)
	prepare (string $a_query, ?array $a_types=null, ?array $a_result_types=null)
	Prepare a query (SELECT) statement to be used with execute. More...
	prepareManip (string $a_query, ?array $a_types=null)
	enableResultBuffering (bool $a_status)
	execute (ilDBStatement $stmt, array $data=[])
	sequenceExists (string $sequence)
	listSequences ()
	supports (string $feature)
	supportsFulltext ()
	supportsSlave ()
	supportsTransactions ()
	listTables ()
	loadModule (string $module)
	getAllowedAttributes ()
	concat (array $values, bool $allow_null=true)
	locate (string $needle, string $string, int $start_pos=1)
	quoteIdentifier (string $identifier, bool $check_option=false)
	modifyTableColumn (string $table, string $column, array $attributes)
	free (ilDBStatement $a_st)
	checkTableName (string $a_name)
	beginTransaction ()
	commit ()
	rollback ()
	constraintName (string $a_table, string $a_constraint)
	dropIndex (string $a_table, string $a_name="i1")
	createDatabase (string $a_name, string $a_charset="utf8", string $a_collation="")
	dropIndexByFields (string $table_name, array $afields)
	getPrimaryKeyIdentifier ()
	addFulltextIndex (string $table_name, array $afields, string $a_name='in')
	dropFulltextIndex (string $a_table, string $a_name)
	isFulltextIndex (string $a_table, string $a_name)
	setStorageEngine (string $storage_engine)
	getStorageEngine ()
	buildAtomQuery ()
	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)
	getServerVersion (bool $native=false)
	queryCol (string $query, int $type=ilDBConstants::FETCHMODE_DEFAULT, int $colnum=0)
	queryRow (string $query, ?array $types=null, int $fetchmode=ilDBConstants::FETCHMODE_DEFAULT)
	escape (string $value, bool $escape_wildcards=false)
	escapePattern (string $text)
	migrateTableToEngine (string $table_name, string $engine=ilDBConstants::MYSQL_ENGINE_INNODB)
	migrateAllTablesToEngine (string $engine=ilDBConstants::MYSQL_ENGINE_INNODB)
	supportsEngineMigration ()
	migrateTableCollation (string $table_name, string $collation=ilDBConstants::MYSQL_COLLATION_UTF8MB4)
	migrateAllTablesToCollation (string $collation=ilDBConstants::MYSQL_COLLATION_UTF8MB4)
	supportsCollationMigration ()
	addUniqueConstraint (string $table, array $fields, string $name="con")
	dropUniqueConstraint (string $table, string $name="con")
	dropUniqueConstraintByFields (string $table, array $fields)
	checkIndexName (string $name)
	getLastInsertId ()
	uniqueConstraintExists (string $table, array $fields)
	dropPrimaryKey (string $table_name)
	executeMultiple (ilDBStatement $stmt, array $data)
	fromUnixtime (string $expr, bool $to_text=true)
	unixTimestamp ()
	getDBVersion ()
	returns the Version of the Database (e.g. More...

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
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...
static	isReservedWord (string $a_word)
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

(

)

@inheritDoc

Reimplemented from ilDBPdo.

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>

Reimplemented from ilDBPdo.

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

(

)

Reimplemented from ilDBPdo.

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

Reimplemented from ilDBPdo.

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

)

@inheritDoc

Reimplemented from ilDBPdo.

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

                                                                                                         : 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;
    }

References ilDBConstants\MODULE_MANAGER.

migrateAllTablesToEngine()

ilDBPdoMySQL::migrateAllTablesToEngine

(

string

$engine = ilDBConstants::MYSQL_ENGINE_INNODB

)

Returns

array<int|string, string>

Reimplemented from ilDBPdo.

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

                                                                                               : 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;
    }

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

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.

     : 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;
    }

References $q.

migrateTableToEngine()

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

Implements ilDBPdoInterface.

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

                                                                                                               : 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);
        }
    }

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

Here is the call graph for this function:

nextId()

ilDBPdoMySQL::nextId

(

string

$table_name

)

Parameters

$table_name

string

Reimplemented from ilDBPdo.

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

                                              : 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;
    }

supportsCollationMigration()

ilDBPdoMySQL::supportsCollationMigration

(

)

@inheritDoc

Reimplemented from ilDBPdo.

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

                                                : bool
    {
        return true;
    }

supportsEngineMigration()

ilDBPdoMySQL::supportsEngineMigration

(

)

Reimplemented from ilDBPdo.

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

                                             : bool
    {
        return true;
    }

supportsTransactions()

ilDBPdoMySQL::supportsTransactions

(

)

Reimplemented from ilDBPdo.

Reimplemented in ilDBPdoMySQLGalera, and ilDBPdoMySQLInnoDB.

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