Page 1 of 1

Default database/table charset

Posted: Thu Jan 11, 2007 10:05 pm
by georgeoc
Hi all.

I'm trying to get UTF-8 working in my PHP app. It's all good now except for one thing.

During the installation process, I use the AdoDB XML Schema class to automatically create the necessary tables from XML files. This is great, but has the drawback that I don't get to specify the Character Set of the new tables, as AdoDb doesn't support that yet. What I'm looking for is the best way to get the desired result: all tables using the UTF-8 charset.

From what I can understand of the MySQL manual, I can set the default charset of the database, then create the tables and they will inherit the charset. However, as my app may not be the only one using this particular database, it's important that I restore the default charset after I've finished the installation.

Does this sound like the right way to go? Basically, I run:

Code: Select all

SHOW VARIABLES LIKE 'character_set_database'
...to get the previous default, then set the default to utf8, then install the tables and finally return the default to whatever it was previously.


Any comments, or improvements?

TIA

Posted: Thu Jan 11, 2007 11:42 pm
by volka
what about
http://phplens.com/lens/adodb/docs-datadict.htm#xmlschema wrote:Finally, AXMLS allows you to include arbitrary SQL that will be applied to the database when the schema is executed.
?
You could add a ALTER TABLE query as described at http://dev.mysql.com/doc/refman/5.0/en/ ... table.html
But then your xml file has a dependency on mysql - suboptimal solution.
Or you use preg_match_all to fetch all table element from your schema and build the ALTER TABLE query. Or even a xml parser like AXMLS does.

Posted: Fri Jan 12, 2007 5:20 am
by georgeoc
I need to support as many DMBS types as possible: portability is the key here. I'll have a think about your second suggestion.

Posted: Fri Jan 12, 2007 5:40 am
by Kieran Huggins
Are all those DB's UTF-8 capable?

If not, you could store base64 encoded versions of your UTF-8 text. That would definitely be compatible, but you would lose a little space and processing time... not to mention readability :-(

Posted: Fri Jan 12, 2007 7:26 pm
by georgeoc
I went with this in the end:

Code: Select all

'ALTER TABLE ' . $this->_db_prefix . '_' . $table . ' CONVERT TO CHARACTER SET utf8'