Creating utf-8 tables in symfony 1.4 /doctrine 1.2

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.

• muffinresearch.co.uk Stuart

  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.

• rp

  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.

• dextrose.be Dextro

  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)

• rp

  yes that would be another way of doing that! thanks for pointing that out!

• Pingback: UTF8 con Doctrine en Symfony 1.4 | Symfony por David Vega

• Susan

  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

• Pingback: Creating utf-8 tables in symfony 1.4 /doctrine 1.2 » HalGatewood.com

• www.prasadgupte.com Prasad

  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?

• pasargad.cse.shirazu.ac.ir/~kazemi Alireza

  Thank you Susan, Your database.yml configuration parameters worked fine.

• pasargad.cse.shirazu.ac.ir/~kazemi Alireza

  In my case when I set my db to pgsql without adding following parameters in the database.yml it works with unicode tables and fixtures.

  attributes:
  default_table_collate: utf8_general_ci
  default_table_charset: utf8

  But for mysql db the above lines must be added in database.yml.

• www.hamsterdesign.si internetnih strani

  Thanks for this solution. I was struggling one hour with this issue before googling it