UUID migration (postgres)

TIdToUuidConverter.php

<?php

namespace App\Model\Migrations;

use Doctrine\DBAL\Connection;
use Ramsey\Uuid\Uuid;

/**
 * @author David Kudera <kudera.d@gmail.com>
 */
trait TIdToUuidConverter
{


	/**
	 * @param string $sql
	 * @param array $params
	 * @param array $types
	 */
	abstract protected function addSql($sql, array $params = [], array $types = []);


	/**
	 * @param \Doctrine\DBAL\Connection $connection
	 * @param string $tableName
	 * @param array $associations
	 * @return array
	 */
	protected function addIdToUuidConvertSQL(Connection $connection, $tableName, array $associations = [])
	{
		return $this->addConvertSQL($connection, $tableName, $associations);
	}


	/**
	 * @param \Doctrine\DBAL\Connection $connection
	 * @param string $tableName
	 * @param array $associations
	 * @return array
	 */
	protected function addUuidToIdConvertSQL(Connection $connection, $tableName, array $associations = [])
	{
		return $this->addConvertSQL($connection, $tableName, $associations, true);
	}


	/**
	 * $associations = [
	 * 		table_name => [
	 * 			fk_constraint_name => join_column_name
	 * 		]
	 * ]
	 *
	 * @param \Doctrine\DBAL\Connection $connection
	 * @param string $tableName
	 * @param array $associations
	 * @param bool $revert
	 * @return array
	 */
	private function addConvertSQL(Connection $connection, $tableName, array $associations = [], $revert = false)
	{
		$data = $connection->query('SELECT id AS old_id FROM "'. $tableName. '"')->fetchAll();

		if (!$revert) {
			$this->addSql('DROP SEQUENCE '. $tableName. '_id_seq CASCADE');
		}

		foreach ($associations as $joinedTableName => $association) {
			foreach ($association as $constraint => $columnName) {
				$this->addSql('ALTER TABLE "'. $joinedTableName. '" DROP CONSTRAINT '. $constraint);
				$this->addSql('ALTER TABLE "'. $joinedTableName. '" ALTER '. $columnName. ' TYPE VARCHAR(255)');
				$this->addSql('ALTER TABLE "'. $joinedTableName. '" ALTER '. $columnName. ' DROP DEFAULT');
			}
		}

		$this->addSql('ALTER TABLE "'. $tableName. '" ALTER id TYPE VARCHAR(255)');
		$this->addSql('ALTER TABLE "'. $tableName. '" ALTER id DROP DEFAULT');

		$current = 1;
		$ids = [];
		foreach ($data as $line) {
			$line['new_id'] = $revert ? $current : Uuid::uuid4();
			$ids[$line['old_id']] = $line['new_id'];

			$this->addSql('UPDATE "'. $tableName. '" SET id = ? WHERE id = ?', [$line['new_id'], $line['old_id']]);

			foreach ($associations as $joinedTableName => $association) {
				foreach ($association as $constraint => $columnName) {
					$this->addSql('UPDATE "'. $joinedTableName. '" SET '. $columnName. ' = ? WHERE '. $columnName. ' = ?', [$line['new_id'], $line['old_id']]);
				}
			}

			$current++;
		}

		$castType = $revert ? 'integer' : 'uuid';

		$this->addSql('ALTER TABLE "'. $tableName. '" ALTER id TYPE '. $castType. ' USING id::'. $castType);
		$this->addSql('ALTER TABLE "'. $tableName. '" ALTER id DROP DEFAULT');

		foreach ($associations as $joinedTableName => $association) {
			foreach ($association as $constraint => $columnName) {
				$this->addSql('ALTER TABLE "'. $joinedTableName. '" ALTER '. $columnName. ' TYPE '. $castType. ' USING '. $columnName. '::'. $castType);
				$this->addSql('ALTER TABLE "'. $joinedTableName. '" ALTER '. $columnName. ' DROP DEFAULT');
				$this->addSql('ALTER TABLE "'. $joinedTableName. '" ADD CONSTRAINT '. $constraint. ' FOREIGN KEY ('. $columnName. ') REFERENCES "'. $tableName. '" (id) NOT DEFERRABLE INITIALLY IMMEDIATE');
			}
		}

		if ($revert) {
			$this->addSql('CREATE SEQUENCE '. $tableName. '_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
			$this->addSql('SELECT setval(\''. $tableName. '_id_seq\', (SELECT MAX(id) FROM "'. $tableName. '"))');
		}

		return $ids;
	}

}

Version20151011132354.php

<?php

namespace App\Model\Migrations;

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

/**
 * @author David Kudera <kudera.d@gmail.com>
 */
class Version20151011132354 extends AbstractMigration
{


	use TIdToUuidConverter;


	/** @var array */
	private $config = [
		'table_without_associations' => [],
		'table_with_associations' => [
			'associated_table' => [
				'fk_sequence_name' => 'table_with_association_id',
			],
			'another_associated_table' => [
				'fk_sequence_name' => 'table_with_association_id',
			],
		],
	];


	/**
	 * @param \Doctrine\DBAL\Schema\Schema $schema
	 */
	public function up(Schema $schema)
	{
		foreach ($this->config as $table => $associations) {
			$this->addIdToUuidConvertSQL($this->connection, $table, $associations);
		}
	}


	/**
	 * @param \Doctrine\DBAL\Schema\Schema $schema
	 */
	public function down(Schema $schema)
	{
		foreach ($this->config as $table => $associations) {
			$this->addUuidToIdConvertSQL($this->connection, $table, $associations);
		}
	}

}