Bug in displaying macronised characters

[emms]

Hi, There is a problem with the display of macronised characters. Entered into the CMS they appear fine, but then render as ?? on the live site, and once the page is reloaded in the CMS they have changed to ?? in the CMS also.

Have done extensive testing to make sure that there is no problem with my webserver/database/website sending in the wrong charset - have eliminated all of this so it must be something in SS? :(

Please see this thread here for our attempts at deciphering the problem. http://www.silverstripe.com/site-builders-forum/flat/50517?start=0

This is important as we are creating a website for a Maori organisation who *need* these to display correctly.

[sminnee]

Can you please give us a link to your test server? For privacy you can email it to sam at silverstripe dot com.

[sminnee]

Okay - so I've worked out a solution to this. The other benefit of this change is that you can see your macrons properly inside phpMyAdmin.

In essence, we need to tell MySQL to recognise the character set we're using.

sapphire/core/model/MySQLDatabase.php

@@ -39 +39 @@
         */
        public function __construct($parameters) {
                $this->dbConn = mysql_connect($parameters['server'], $parameters['username'], $parameters['password']);
+               $this->query("SET CHARACTER SET 'utf8'");
+               $this->query("SET NAMES 'utf8'");
                $this->active = mysql_select_db($parameters['database'], $this->dbConn);
                $this->database = $parameters['database'];
                if(!$this->dbConn) {

But there's a big problem with this: if we blindly apply it to the mainline trunk, we will corrupt everyone's data!

We need to have a way of:

• Signalling whether we have been using latin1 to save data (the current behaviour for ss)
• Extract all text content using SET CHARACTER SET 'latin1'.
• Save that content back using SET NAMES 'utf8'.
• Telling the database that all the content has been saved as utf8, so we don't do this again in the future.

Perhaps we should add a DatabaseConfig? table or something, where we can store key-value pairs with information such as this? The other thing that DatabaseConfig? could be used for is storing the schema version of a database, if we were to improve our schema migration stuff.