diff options
| author | Simon Rettberg | 2021-05-06 10:26:09 +0200 |
|---|---|---|
| committer | Simon Rettberg | 2021-05-11 14:51:13 +0200 |
| commit | 8dc2b92d667f1401ab9f1315a36add61658f899c (patch) | |
| tree | 452c5fe040055884cf9d9ee834415db84999a9a1 /install.php | |
| parent | [session] Add simple session overview table (diff) | |
| download | slx-admin-8dc2b92d667f1401ab9f1315a36add61658f899c.tar.gz slx-admin-8dc2b92d667f1401ab9f1315a36add61658f899c.tar.xz slx-admin-8dc2b92d667f1401ab9f1315a36add61658f899c.zip | |
Moderize Database handling
* Auto-convert to utf8mb4_unicode_520_ci
* Use foreach instead of while to loop over results
* Drop useless statement caching
* Keep emulated prepares, as we sometimes loop over nested queries
Diffstat (limited to 'install.php')
| -rw-r--r-- | install.php | 48 |
1 files changed, 44 insertions, 4 deletions
diff --git a/install.php b/install.php index f0bb1c72..60cf9495 100644 --- a/install.php +++ b/install.php @@ -84,7 +84,7 @@ function tableGetDescribeColumn($table, $column, $what) $table = preg_replace('/\W/', '', $table); $res = Database::simpleQuery("DESCRIBE `$table`", array(), true); if ($res !== false) { - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + foreach ($res as $row) { if ((is_array($column) && in_array($row['Field'], $column)) || (is_string($column) && $row['Field'] === $column)) return $row[$what]; } @@ -101,7 +101,7 @@ function tableGetIndex($table, $index) $res = Database::simpleQuery("SHOW INDEX FROM `$table`", array(), true); if ($res !== false) { $matches = []; - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + foreach ($res as $row) { $i = $row['Seq_in_index'] - 1; if (isset($index[$i]) && $index[$i] === $row['Column_name']) { if (!isset($matches[$row['Key_name']])) { @@ -124,7 +124,7 @@ function tableDropColumn($table, $column) $column = preg_replace('/\W/', '', $column); $res = Database::simpleQuery("DESCRIBE `$table`", array(), true); if ($res !== false) { - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + foreach ($res as $row) { if ((is_array($column) && in_array($row['Field'], $column)) || (is_string($column) && $row['Field'] === $column)) Database::exec("ALTER TABLE `$table` DROP `{$row['Field']}`"); } @@ -345,6 +345,45 @@ function runUpdateScript($module) require_once $module->getDir() . '/install.inc.php'; } +// Update collation/encoding etc +$charsetUpdate = ''; +$COLLATION = 'utf8mb4_unicode_520_ci'; +$res = Database::queryFirst("SELECT @@character_set_database, @@collation_database"); +if ($res['@@character_set_database'] !== 'utf8mb4' || $res['@@collation_database'] !== $COLLATION) { + if (!preg_match('/dbname=(\w+)/', CONFIG_SQL_DSN, $out)) { + $charsetUpdate = 'Cannot update charset: DB Name unknown'; + } else { + $db = $out[1]; + $columns = Database::simpleQuery("SELECT + TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_TYPE, EXTRA, COLUMN_COMMENT + FROM information_schema.COLUMNS + WHERE TABLE_SCHEMA = :db AND COLLATION_NAME LIKE 'utf8%' AND COLLATION_NAME <> :collation", + ['db' => $db, 'collation' => $COLLATION]); + foreach ($columns as $c) { + $args = []; + $str = $c['COLUMN_TYPE'] . ' CHARACTER SET utf8mb4 ' . $c['EXTRA']; + if ($c['IS_NULLABLE'] === 'NO') { + $str .= ' NOT NULL'; + } + if (!($c['IS_NULLABLE'] === 'NO' && $c['COLUMN_DEFAULT'] === null)) { + $str .= ' DEFAULT ' . ($c['COLUMN_DEFAULT'] ?? 'NULL'); + } + if (!empty($c['COLUMN_COMMENT'])) { + $str .= ' COMMENT :comment'; + $args['comment'] = $c['COLUMN_COMMENT']; + } + $str .= ' COLLATE ' . $COLLATION; + if (Database::exec("ALTER TABLE {$c['TABLE_NAME']} MODIFY {$c['COLUMN_NAME']} $str", $args) === false) { + $charsetUpdate .= "\n+++ {$c['TABLE_NAME']}.{$c['COLUMN_NAME']} failed: " . Database::lastError(); + $charsetUpdate .= "\nALTER TABLE {$c['TABLE_NAME']} MODIFY {$c['COLUMN_NAME']} $str"; + } + } + if (empty($charsetUpdate) && Database::exec("ALTER DATABASE $db CHARACTER SET utf8mb4 COLLATE $COLLATION") === false) { + $charsetUpdate .= "\nCannot update database charset or collation: " . Database::lastError(); + } + } +} // End utf8 stuff + // Build dependency tree Module::init(); $modules = Module::getEnabled(); @@ -411,7 +450,7 @@ if (DIRECT_MODE) { <title>Install/Update SLXadmin</title> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> - <style type="text/css"> + <style> body, html { color: #000; background: #fff; @@ -428,6 +467,7 @@ if (DIRECT_MODE) { </style> </head> <body> + <pre>$charsetUpdate</pre> <h1>Modules</h1> <button onclick="slxRunInstall()" class="install-btn">Install/Upgrade</button> <br> |