PHP Doctrine - Best practice to set application-wide default table charset

Question

At the moment, I am setting my table charset and collation like this:

class Model extends Doctrine_Record
{

  public function setTableDefinition()
  {
    //...

    $this->option('collate', 'utf8_unicode_ci');
    $this->option('charset', 'utf8');
  }

}

I am setting this in all my table definitions. Is there a way to set a default value? In my bootstrap I am setting other default values like this:

Doctrine_Manager::getInstance()->setAttribute(
    Doctrine::ATTR_DEFAULT_IDENTIFIER_OPTIONS,
    array('name' => 'id', 'type' => 'integer', 'length' => 4));

Would be nice, if there was a way to do the same for collate and charset. I found constants for it, but couldn't find where/if they were ever used somewhere.

Thanks

Answer 1

It's best not to define this as default in doctrine as it needs to be compatible with multiple databases types (Mysql, MSSql, SQLite, ...). But I will give you some options how you can do this without doctrine and one with doctrine:

Option 1:

I mostly set the default for my whole database server in my.cf file. This file is located in /etc/mysql/my.cnf on Debian (Ubuntu) (this can be different if you use an other os).

[mysqld]
default-character-set = utf8
default-character-collate = utf8_unicode_ci

After editing the file restart mysql server. That should do the trick.

Option 2: When you create your database:

CREATE DATABASE `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

You can also alter your database:

ALTER DATABASE `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Option 3: If you want to alter a table:

ALTER TABLE `tablename` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Option 4: You can do something like this:

public function configureDoctrine(Doctrine_Manager $manager)
{
    $manager->setCollate('utf8_unicode_ci');
    $manager->setCharset('utf8');
}

Answer 2

Doctrine_Manager::connection('mysql://user:pass@host/schema')
    // This must be the charset of your HTTP header 
    // header('Content-Type: text/html;charset=utf-8');
    // or your HTML Head tag
    // <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    ->setCharset('UTF8')
    ;

This is all you need (even with latin1 tables, accentued e for example will be saved correctly).

But to be honest, it is best to stick to the same charset throughout your system (DB, HTML) as you can loose some data (e.g. chinese characters inputed on your site won't be saved in your latin1 tables)