Pull Request for new issue.

Only relevant for mysql databases.

Summary of Changes

When implementation of utf8mb4 conversion started a few months ago, we (all who worked together on that) tried to make it work also when using Extension Installer to update Joomla! Core. So we impemented functions to check utf8mb4 support and downgrade "create table" or "alter table" not only in the db driver but also at several places in case of the update is running while still being connected to the old db driver.

Meanwhile this update method is not supported anymore, and with using the Joomla! Update component we can rely on the new, updated db driver being used when running schema updates, and so the duplicate functions are not necessary anymore.

This PR removes these duplicate function impementations and replaces calls to them by calling the corresponding db driver functions.

After this change, the 2 functions to perform the utf8mb4 conversion in script.php and the database schema manager (administrator/components/com_installer/models/database.php) are identical beside a message to check the db fixer in case if some error occurred, which makes sense to be shown on update but not when using the db fixer and so already being in this view. Because the schema manager already includes script.php and uses functions of it just a few lines above the conversion function is called, it is a logical step to make the schema manager use the conversion function from script.php, too.

So this PR also replaces the call to the local conversion function in the schema manager by a call to the one of scrip.php, and remove the duplicate local code. The function in script.php is extended by a parameter telling if the special message to check the db fixer is shown or not.

Finally, this PR corrects following 2 issues with the statement downgrade function for which I once created PR #9504 , which I will close soon in favour of this PR here:

1. If in a "CREATE TABLE" or "ALTER TABLE" statement there is more than 1 space or a tab between the words "CREATE" or "ALTER" and "TABLE", or the keywords are not in uppercase, which is all still valid SQL, the statement will not be detected to be a "CREATE TABLE" or "ALTER TABLE" statement and so not be processed. This will lead to an "unknown character set" SQL error on a db not supporting utf8mb4 e.g. when installing extensions already using utf8mb4 in their sql for creating tables.
2. If a "CREATE TABLE" or "ALTER TABLE" has been detected, the procedure is to simply replace "utf8mb4" by "utf8" in the statement, regardless if it occurs withint quoted text or name quotes. This makes it impossible to use the pattern "utf8mb4" within table or column names, table or column comments or column default values.

This PR fixes both issues.

Testing Instructions

Pre-requisites

This PR is only relevant for mysql databases.

It needs tests with database sever/client combinations which do not support utf8mb4, i.e. either the mysql server version is lower than 5.5.3, or a msqlnd client API with version lower than 5.0.9 is used, or another mysql-related client API with version lower than 5.5.3 is used.

Testers who have only database sever/client combinations which do support utf8mb4 are welcome to test that updating Joomla! core with any valid method or installing extensions already using utf8mb4 (e.g. Patchtester) still works the same as before. Please report in this case that you tested with utf8mb4 support.

Test 1: Joomla! Update component

Step 1: Update a Joomla! version 3.5.1 or lower to a 3.5.2-dev patched by this PR using following custom URL: http://test5.richard-fath.de/list_test10.xml.

If you want to continue later with Test 2, you should export your database before doing the update, so you can later use the database dump for Test 2.

Result: Update ends with success.

Step 2: Check "Extensions -> Manage -> Database".

Result:

Database table structure is up to date.
Other Information

• Database schema version (in #__schemas): 3.5.1-2016-03-29.
• Update version (in #__extensions): 3.5.2-dev.
• Database driver: mysqli.
• 94 database changes were checked successfully.
• 145 database changes did not alter table structure and were skipped.

Step 3: Check database table character sets and collations with phpMyAdmin.

Result: All core tables have utf8mb4 or utf8 charset, depending on utf8mb4 support. All core table have unicode_ci collations, except of those for com_finder, which have general_ci.

Step 4: Check with phpMyAdmin the index idx_name of table #__users.

Result: The index idx_name of table #__users includes the name column with 100 chars.

Test 2: Database Schema Manager aka "DB fixer"

Step 1: Prepare to update a Joomla! version 3.5.1 or lower to a 3.5.2-dev patched by this PR with the "copy files and run db fixer" method.

If you have done Test 1 before and have exported the database before updating, just import back the exported database.

Otherwise use a clean 3.5.1 or lower and copy the files of this PR'S branch to the joomla folder. A zip of this branch can be found here: https://github.com/richard67/joomla-cms/archive/utf8mb4-refactoring-1.zip.

Step 2: Go to "Extensions -> Manage -> Database".

Result: Database problems shown for schema update files according to the pre-update schema version, last problem is that utf8mb4 (or utf8) conversion has not been done yet.

Step 3: Use the "Fix" button.

Result: See result of Test 1 Step 2.

Check database as described with Steps 3 and 4 of Test 1.

Test 3: Install extensions

Step 1: On current staging patched by this PR, or on the updated 3.5.2-dev patched by this PR which you have after Test 1 or Test 2, install some extension which already uses utf8mb4, e.g. the latest com_patchtester 2.0.0-rc.

Step 2: Check with phpMyAdmin that the database tables for this component have the correct character sets and collations depending on utf8mb4 support, e.g. for patchtester: utf8mb4_unicode_ci or utf8_unicode_ci.

Result: No SQL errors related to character sets or collations, database tables look as expected.

Test 4: Statement downgrade bug fix

This test only makes sense on a database server/client combination which does not support utf8mb4.

Step 1: Download following dummy extension and then install it, using "Extensions -> Manage -> Install", "Upload & Install Package": http://test5.richard-fath.de/test_package_utf8mb4.zip.

Result: The installation succeeds without any SQL errors.

Step 2: Check with phpMyAdmin that the package has created a new table #__test_package_utf8mb4 with default character set=utf8 and default collation = utf8_unicode_ci.

Step 3: Check with phpMyAdmin the structure of this new table.

Result: The table has following 2 columns:

• utf8mb4_test_1 varchar(40) NOT NULL DEFAULT 'Do not replace utf8mb4 in quoted text.' COMMENT 'Do not replace utf8mb4 in quoted text.',
• utf8mb4_test_2 varchar(7) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '#fff' COMMENT 'Do not replace utf8mb4 in quoted text.'

Step 4: Uninstall the package to clean up if you need your test system for other tests.

Step 5: Repeat Steps 1 to 4 on an unpatched staging or 3.5.1 or 3.5.0.

Result: Table names, column names, column default values and column comments are wrong because "utf8mb4" has been replaced everywhere by "utf8".