ILIAS  trunk Revision v11.0_alpha-3011-gc6b235a2e85
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
$querystring 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.

181 : bool
182 {
183 // Currently ILIAS does not support utf8mb4, after that ilDB could check like this:
184 // static $supported;
185 // if (!isset($supported)) {
186 // $q = "SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = %s;";
187 // $res = $this->queryF($q, ['text'], [$this->getDbname()]);
188 // $data = $this->fetchObject($res);
189 // $supported = ($data->default_character_set_name === 'utf8mb4');
190 // }
191
192 return false;
193 }

◆ getAdditionalAttributes()

ilDBPdoMySQL::getAdditionalAttributes ( )
protected
Returns
array<int, int|bool>

Reimplemented from ilDBPdo.

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

67 : array
68 {
69 return [
70 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
71 PDO::ATTR_TIMEOUT => 300 * 60,
72 ];
73 }

◆ initHelpers()

ilDBPdoMySQL::initHelpers ( )

Reimplemented from ilDBPdo.

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

45 : void
46 {
47 $this->manager = new ilDBPdoManager($this->pdo, $this);
48 $this->reverse = new ilDBPdoReverse($this->pdo, $this);
49 $this->field_definition = new ilDBPdoMySQLFieldDefinition($this);
50 }
Class ilDBPdoManager.
Class ilDBPdoMySQLFieldDefinition.
Class ilDBPdoReverse.

◆ initSQLMode()

ilDBPdoMySQL::initSQLMode ( )
protected

Reimplemented from ilDBPdo.

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

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

◆ migrateAllTablesToCollation()

ilDBPdoMySQL::migrateAllTablesToCollation ( string  $collation = ilDBConstants::MYSQL_COLLATION_UTF8MB4)

@inheritDoc

Reimplemented from ilDBPdo.

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

133 : array
134 {
136 $errors = [];
137 foreach ($manager->listTables() as $table_name) {
138 if (!$this->migrateTableCollation($table_name, $collation)) {
139 $errors[] = $table_name;
140 }
141 }
142
143 return $errors;
144 }
listTables(?string $database=null)
migrateTableCollation(string $table_name, string $collation=ilDBConstants::MYSQL_COLLATION_UTF8MB4)
loadModule(string $module)
ilDBPdoManager $manager

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.

91 : array
92 {
93 $engines = $this->queryCol('SHOW ENGINES');
94 if (!in_array($engine, $engines, true)) {
95 return [];
96 }
97 $errors = [];
98 $tables = $this->listTables();
99 array_walk($tables, function (string $table_name) use (&$errors, $engine): void {
100 try {
101 $this->pdo->exec("ALTER TABLE $table_name ENGINE=$engine");
102 if ($this->sequenceExists($table_name)) {
103 $this->pdo->exec("ALTER TABLE {$table_name}_seq ENGINE=$engine");
104 }
105 } catch (Exception $e) {
106 $errors[$table_name] = $e->getMessage();
107 }
108 });
109
110 return $errors;
111 }
sequenceExists(string $sequence)
queryCol(string $query, int $type=PDO::FETCH_ASSOC, int $colnum=0)

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.

116 : bool {
117 $collation_split = explode("_", $collation);
118 $character = $collation_split[0] ?? 'utf8mb4';
119 $collate = $collation;
120 $q = "ALTER TABLE {$this->quoteIdentifier($table_name)} CONVERT TO CHARACTER SET {$character} COLLATE {$collate};";
121 try {
122 $this->pdo->exec($q);
123 } catch (PDOException) {
124 return false;
125 }
126 return true;
127 }
$q
Definition: shib_logout.php:23

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.

75 : void
76 {
77 try {
78 $this->pdo->exec("ALTER TABLE {$table_name} ENGINE={$engine}");
79 if ($this->sequenceExists($table_name)) {
80 $this->pdo->exec("ALTER TABLE {$table_name}_seq ENGINE={$engine}");
81 }
82 } catch (PDOException $e) {
83 throw new ilDatabaseException($e->getMessage(), $e->getCode(), $e);
84 }
85 }
Class ilDatabaseException.

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

+ Here is the call graph for this function:

◆ nextId()

ilDBPdoMySQL::nextId ( string  $table_name)
Parameters
$table_namestring

Reimplemented from ilDBPdo.

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

155 : int
156 {
157 $sequence_name = $this->quoteIdentifier($this->getSequenceName($table_name), true);
158 $seqcol_name = $this->quoteIdentifier('sequence');
159 $query = "INSERT INTO $sequence_name ($seqcol_name) VALUES (NULL)";
160 try {
161 $this->pdo->exec($query);
162 } catch (PDOException) {
163 // no such table check
164 }
165
166 $result = $this->query('SELECT LAST_INSERT_ID() AS next');
167 $value = $result->fetchObject()->next;
168
169 if (is_numeric($value)) {
170 $query = "DELETE FROM $sequence_name WHERE $seqcol_name < $value";
171 $this->pdo->exec($query);
172 }
173
174 return (int) $value;
175 }
query(string $query)
getSequenceName(string $table_name)
quoteIdentifier(string $identifier, bool $check_option=false)

◆ supportsCollationMigration()

ilDBPdoMySQL::supportsCollationMigration ( )

@inheritDoc

Reimplemented from ilDBPdo.

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

150 : bool
151 {
152 return true;
153 }

◆ supportsEngineMigration()

ilDBPdoMySQL::supportsEngineMigration ( )

Reimplemented from ilDBPdo.

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

58 : bool
59 {
60 return true;
61 }

◆ supportsTransactions()

ilDBPdoMySQL::supportsTransactions ( )

Reimplemented from ilDBPdo.

Reimplemented in ilDBPdoMySQLGalera, and ilDBPdoMySQLInnoDB.

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

40 : bool
41 {
42 return false;
43 }

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: