Creating utf-8 tables in symfony 1.4 /doctrine 1.2
by rp
Recently I had an issue on one of my work websites where the database schema was setup in Latin-1 encoding, this was fine till the encoding of the website was also set to iso-8859-1 and nothing fancy was going on. However later in the last few months, I had been doing some development which required me to change the encoding to utf-8 and that resulted in a lot of issues. Some I am still resolving. One of the two solutions that we came up was assign it a custom function for ‘escaping_strategy’, which however wasn’t the silver bullet to fix as there was more crazy code that was getting raw value from the model and hence our function would get by passed. I am still looking for better ways of fixing it (seems very likely a case of massive re-factoring required) however, this one tip was handy.
If you want to create an individual table to be utf-8 then in the schema.yml you can add.
User:
options:
type: MyISAM
collate: utf8_unicode_ci
charset: utf8
columns:
username: string(255)
password: string(255)
However this can be a painful repetitive task, especially if you have loads and loads of tables to work with. In case you are like me and prefer all your tables to be utf-8 by default, you can add this function to your ProjectConfiguration.class.php file.
public function configureDoctrine(Doctrine_Manager $manager)
{
$manager->setCollate('utf8_unicode_ci');
$manager->setCharset('utf8');
}
and then rebuild all the models (and possibly load your fixtures if you have any.
# symfony doctrine:build --all --and-load --env=dev --no-confirmation
Obviously bare in mind that this will drop your database and re-create the tables with utf-8 encoding.
If anyone has any suggestions for my problem, do leave a comment.
Not sure this is related to your issue, but another thing is ensure that the db connection is utf-8 because all kinds of horrors can happen with utf8 tables and a latin1 connection.
The worst thing is that the issue is not obvious until you look at the data via a client with a utf8 connection.
yea! thanks for that pointer, i suppose its best to use the superset, will be doing more debugging in the following week and possibly write about the findings.
You can put the main options for all tables at the top of your schema.yml file
options:
collate: utf8_unicode_ci
charset: utf8
User:
columns:
username: string(255)
password: string(255)
yes that would be another way of doing that! thanks for pointing that out!
[...] Rajat Pandit configure, database, doctrine, utf8 This entry was posted on Ene 21st, 2010 at 06:45 and is [...]
Hi,
you could also directly create a utf8-database:
config/databases.yml
all:
doctrine:
class: sfDoctrineDatabase
param:
dsn: mysql:host=localhost;dbname=
username: root
password:
attributes:
default_table_collate: utf8_general_ci
default_table_charset: utf8
$ ./symfony doctrine:build-db
[...] via Creating utf-8 tables in symfony 1.4 /doctrine 1.2 | Connecting the dots…. [...]
Hi,
can you tell me how I can configure partitions thru Doctrine YML? similar to defining indexes, is there a syntax? If not, can raw SQL be added to the set-up function?