Database Utility: Uniform Collations & Engine

[Chen Zhen]

SMF Database -> Uniform collation, character set & engine type settings utility

This tool will allow you to opt your default database settings for common configurations.
The new settings options will be available in your Admin Maintenance section.
It will adjust all tables & columns to match.

This Simple Machines Forum Database Tool c/o Chen Zhen @ https://web-develop.ca
Copyright 2023 Chen Zhen
SMF DB Uniform Collation & Engine is distributed under the terms of the GNU General Public License

Please remember to backup your database prior to using this tool.
UTF8 or UTF8MB4 collation and character sets are available.

The TEXT & VARCHAR conversion is available to convert the actual text in those type of database fields to UTF-8.
This will likely be necessary for either UTF8 OR UTF8MB4.
There is also an option to convert any UTF8MB4 emojis to their UTF8 equivalents if opting a UTF8 database setting.

Note regarding databases converted to UTF8MB4: TEXT and MEDIUMTEXT fields will remain and will not be downgraded when converting to UTF8.


Disclaimer:

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.


Version 2.5
! fixed changed class name of "Encoding" to "ForceEncoding"
! fixed added UniformCollations_fix_serialized_columns() function
! fixed removed innodb query causing error

Version 2.4
! fixed login cookie for the SMF 2.1 branch

Version 2.3
! fixed utf8 character conversion sub-routine
! fixed namespace declaration to beginning of file
! fixed login cookie will not timeout amidst database changes

Version 2.2
+ added option to convert utf8mb4 emoji to utf8 character codes
+& admin button names altered to shorter format
+& utf8mb4 optional conversion added
+ all tables are repaired & optimized after changes

Version 2.1
+ added option to convert TEXT & BLOB to UTF-8
! removed single problematic collation
! fixed save button in Uniform Collations admin

Version 2.0
+& added settings template to SMF maintenance admin section
+& added drop-down to opt collation setting
+ restricted setting to utf8 or utf8mb4


Version 1.2
+ changes made to use more native/inherent SMF database query syntax

Version 1.1
! collation & character set now based on overall database setting
? previous query did not work for database designations containing hyphens

Version 1.0
+ initial release
+ sets collation & character set for all tables to be uniform

CLICK HERE TO DOWNLOAD UNIFORM COLLATIONS

[Skhilled]

Hmmm, interesting. Great for people who move from one hosting to another and the hosting has different collations.

[Chen Zhen]

If their mixed collations are all utf8 or all utf8mb4 then it should work just fine.
I've not tested mixed iso & utf8 tables as of yet.

One can always make backups first and then try it out.
Another option is to first try the SMF built in function to convert to utf8.
After that, run this utility to convert all collations to utf8.

[Skhilled]

There is another utility that is on my site somewhere that will do the same. Haven't tried it in a long time tho...

https://www.docskillz.com/docs/index.php?topic=334.0

[Skhilled]

It's missing instructions for where to upload and how to run, etc. I'm sure someone will be asking sooner or later.

[Chen Zhen]

I'll provide that after I finish the next update.

At the moment the download link only provides v2.0 because I discovered some issues with v2.2 that I am working on.

Large tables ie. messages for some forums which contains posts, needs the script to refresh the page and continue after X number of queries else PHP will time out. I am working on this so that it will work with a large amount of queries.

[Skhilled]

Okey, dokey.

[Chen Zhen]

Version 2.3 is now available from the download link.
The mod will occasionally refresh the page during database changes to thwart PHP timeouts.

Also for the more thorough character conversion option it now only edits field columns that need editing and ignores those that don't need any changes.

[Chen Zhen]

Version 2.5 is now available in the download section which includes some important fixes.
View the changelog for more details.

[Skhilled]

Cool! I forgot about this and was looking for something like it. LOL Luckily, I stopped by.