Suggestion: Better multilingual support for MySQL 4.1+

kdekid

Since version 4.1, MySQL supports multi-character languages natively including UTF-8. Vanilla is born internationalized and tends to support multi languages. It is way far better to implement support for UTF-8 natively instead of handling unicode strings with latin1 encoding which apparently confuses almost all of MySQL management tools, for example, MySQL's Query Browser and phpMyAdmin.

It only demands few lines of changes to the core code:

1. In installation process, CREATE TABLE with CHARACTER SET UTF8 option. It is simple but quite informative especially for extension authors. It's also applicable for Vanilla users to ALTER DATABASE vanilladb DEFAULT CHARACTER SET UTF8 for encoding safety.
2. In Framework.Class.MySQL.php, immediately after initiation of the db connection, add a query SET NAMES UTF8. This notifies the DB that it's actually a UTF8 encoded connection and please handle all characters by utf8 encoding natively.

Best regards,
Felix

Mark

This is awesome - thanks for the information.

Right now Vanilla supports MySQL 3.23 or greater. Do you know if there is any way to make that code dependant on the version of MySQL (so we can have a single set of mysql related scripts that work in all versions - even less than 4.1?)

kdekid

For 1, I think it's easy for the user to choose whether he/she has MySQL 4.1+ or MySQL 4.0-.
For 2, I just inserted

@mysql_query('SET NAMES utf8');

to the function. Warnings (eg., an invalid query) should be suppressed and it works quite well in my Vanilla installation.

After several hours' work, I managed to get my previous UTF8 phpBB2 transferred into Vanilla with winesweeper's script, and it works without any problems so far. In my experience, only 1 line of code had to be changed (the connection part, as mentioned above), and I manually altered my DB's default charset to utf8. Then it works.

It may be more elegant to set up a vanilla-wide db configuration variable to determine whether we are using a utf8 connection, say $Configuration['DATABASE_USE_UTF8'], and

if ($Configuration['DATABASE_USE_UTF8']) {
@mysql_query('SET NAMES utf8');
}

Mark

You're right on the money about the configuration setting - that's what I was thinking when you brought it up.

As for people knowing what version of mysql they have installed, you'd be surprised. I can write a script that detects the mysql version and uses the appropriate install script. I was just wondering (since you seem to know your stuff in this regard) if you knew of a way to write a sql script that would work both ways.

Max_B

Note: If the user is aware of this point, he can create the base with default encoding utf-8, before installing Vanilla. Then the former syntax stay compatible with >=3.23.

The only mandatory call in then

@mysql_query('SET NAMES utf8');

which is ok on any version.

So a simple line in the installation screen can do the trick.

Dinoboff

Could you use mysql_get_server_info?

Max_B

Could you use mysql_get_server_info?

Yes, but if one can make a code without version tweak it's better, IMO.