Oct 23, 2014

Change default naming strategy to underscore in Doctrine

I just realized that using default (CamelCase) naming strategy for database is not a good thing. There are some inconsistencies, sometimes WTFs and sometimes it’s just really stupid. Especially with Doctrine and MySQL….

If you’re using Doctrine and you aren’t blind, you noticed that all tables at your database has got exactly same names as their entities (I’m using entities’ annotations). Perfect, everything looks good…. Well.. Until you create some many to many association.

Then you will end up with for example these tables:

  • User
  • UserRole
  • user_userrole

OMG… I just wanted to have many users with many user roles. Instead of that I’ve got nice User and UserRole tables and then some sh**y….. thing.

But you know, I can live with that.

Some time later my coworker needs to check her database and I will do that for her because she is designer and coder and I don’t want her to break her database.

Lets see what Windows will do (sorry, forgot to mention that I’m using Linux):

  • user
  • userrole
  • user_userrole

Ok, this is even more stupid but again, you know, this is not my computer, so I don’t care.. Almost…

You’ll be working with that for some time but in some corner of your mind, you’ll hear this annoying little voice telling you, that your database looks like some circus tent.. Without struts.. Without clowns.. Long forgotten and scary…

Last straw is when you need to change your database from MySQL to PostgreSQL. That’s problem because Doctrine will not automatically escape tables’ names and all unescaped names at all SQL queries are lowercased by Postgres.

So this will not work:

SELECT * FROM User

But this will:

SELECT * FROM `User`

So now, lets just rewrite all database names to underscored names. You can try to write some magic disposable script, but I think that it’s unnecessary and because we’re using Doctrine migrations I’ll write just two migration classes.

<?php

namespace App\Model\Migrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;

/**
 *
 * @author David Kudera
 */
class Version20141022093454 extends AbstractMigration
{


	/** @var array */
	private $tables = [
		'User' => 'user',
		'UserRole' => 'user_role',
		'user_userrole' => 'user_user_role',
	];


	/**
	* @param \Doctrine\DBAL\Schema\Schema $schema
	*/
	public function up(Schema $schema)
	{
		foreach ($this->tables as $oldName => $newName) {
			$this->addSql("RENAME TABLE $oldName TO $newName");
		}
	}


	/**
	* @param \Doctrine\DBAL\Schema\Schema $schema
	*/
	public function down(Schema $schema)
	{
		foreach ($this->tables as $oldName => $newName) {
			$this->addSql("RENAME TABLE $newName TO $oldName");
		}
	}

}

I’m using standard MySQL query for renaming tables. Doctrine has got renameTable method but if you don’t want to lose all your data, just don’t use it. That’s because doctrine will drop table and create a new one (link).

The second migration class will be for renaming columns and foreign keys. The reason why I didn’t put these two migrations together is because we need to ensure that we set all foreign keys to already renamed tables. Also it is little bit more readable.

<?php

namespace Aulinks\Model\Migrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;

/**
 *
 * @author David Kudera
 */
class Version20141022125848 extends AbstractMigration
{


	/** @var array */
	private $tables = [
		'user' => [
			[
				'oldName' => 'nameFirst',
				'newName' => 'name_first',
				'definition' => 'VARCHAR(50) NOT NULL',
			],
			[
				'oldName' => 'nameLast',
				'newName' => 'name_last',
				'definition' => 'VARCHAR(50) NOT NULL',
			],
			[
				'oldName' => 'publicProfile',
				'newName' => 'public_profile',
				'definition' => "TINYINT(1) NOT NULL COMMENT '(DC2Type:boolean)'",
			],
		],
		'user_user_role' => [
			[
				'oldName' => 'userrole_id',
				'newName' => 'user_role_id',
				'definition' => 'INT NOT NULL',
				'foreignKey' => [
					'constraint' => 'FK_1199161B4A62DE12',
					'target' => 'user_role(id) ON DELETE CASCADE',
				],
			],
		],
	];


	/**
	* @param \Doctrine\DBAL\Schema\Schema $schema
	*/
	public function up(Schema $schema)
	{
		foreach ($this->tables as $table => $columns) {
			foreach ($columns as $column) {
				if (isset($column['foreignKey'])) {
					$this->addSql("ALTER TABLE $table DROP FOREIGN KEY {$column['foreignKey']['constraint']}");
				}
				
				$this->addSql("ALTER TABLE $table CHANGE $column[oldName] $column[newName] $column[definition]");
				
				if (isset($column['foreignKey'])) {
					$this->addSql("ALTER TABLE $table ADD CONSTRAINT {$column['foreignKey']['constraint']} FOREIGN KEY ($column[newName]) REFERENCES {$column['foreignKey']['target']}");
				}
			}
		}
	}


	/**
	* @param \Doctrine\DBAL\Schema\Schema $schema
	*/
	public function down(Schema $schema)
	{
		foreach ($this->tables as $table => $columns) {
			foreach ($columns as $column) {
				if (isset($column['foreignKey'])) {
					$this->addSql("ALTER TABLE $table DROP FOREIGN KEY {$column['foreignKey']['constraint']}");
				}
				
				$this->addSql("ALTER TABLE $table CHANGE $column[newName] $column[oldName] $column[definition]");
				
				if (isset($column['foreignKey'])) {
					$this->addSql("ALTER TABLE $table ADD CONSTRAINT {$column['foreignKey']['constraint']} FOREIGN KEY ($column[oldName]) REFERENCES {$column['foreignKey']['target']}");
				}
			}
		}
	}

}

This is again really simple. Unfortunately there is no RENAME query so we need to basically change whole columns and that means that we need to set again their settings even with comments from doctrine (eg. boolean columns).

Also we can’t just rename columns with foreign keys, so we need to remove these foreign keys first, update column and recreate foreign key for column.

What left is run migrate command (replace with command for your system):

php index.php migrations:migrate