views:

98

answers:

2

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

+1  A: 

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');
}
Skelton
Hi, thanks for your answer, could you be a bit more specific? Where does my.cf go? Where is it being called? I don't have that file ;) Sorry
tilman
I updated my post.
Skelton
Ohhh, now I get you. Thank you. Unfortunately, this might not be an option with hosted web servers, so would like to see the doctrine way.
tilman
I did update my answer again. You can't do this in doctrine as I stated in my answer. But I gave some more options how to achieve your problem.
Skelton
>As I know there is no way to set a default with doctrine. Actually, there is. Read my first post again. You can set the default charset/collate per table. And you can specify defaults for other table-based options, so surely there must be a way to do it for charset/collate as well. Maybe you misunderstood my question? How could I improve the question so it's clearer?
tilman
I did mean that I don't know how to set it for the whole database. But I found a new option It's added to my post.
Skelton
+1  A: 
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)

Julien