Default database/table charset

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
georgeoc
Forum Contributor
Posts: 166
Joined: Wed Aug 09, 2006 4:21 pm
Location: London, UK

Default database/table charset

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
georgeoc
Forum Contributor
Posts: 166
Joined: Wed Aug 09, 2006 4:21 pm
Location: London, UK

Post 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.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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 :-(
georgeoc
Forum Contributor
Posts: 166
Joined: Wed Aug 09, 2006 4:21 pm
Location: London, UK

Post by georgeoc »

I went with this in the end:

Code: Select all

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