B1NARY B1NARY - 4 months ago 49
PHP Question

How to dynamically change table schemas for multi-tenant databases in Doctrine 2/PHP 5.4

WHAT I'M TRYING TO DO:

So I'm trying to implement a multi-tenant database architecture using SQL Azure, PHP 5.4, Zend Framework 2, and Doctrine 2. I'm going with the "Shared Database, Separate Schemas" architecture as mentioned in this article: http://msdn.microsoft.com/en-us/library/aa479086.aspx

Unlike simple multi-tenant environments, my environment has certain use cases where a User from Tenant A should be able to access information from a table in Tenant B. So because of this there are "root" or "global" tables that aren't made for each tenant and are instead used by all tenants. So as an example, I could have a table called users that exists for each tenant each with a unique schema name (e.g. tenanta.users and tenantb.users). I would then also have a root schema for things like global permissions (e.g. root.user_permissions).

WHAT I'VE DONE:

In

Module.php
's
onBootstrap()
function I've set up a
loadClassMetadata
event for dynamically changing the schemas of tables, like so:

$entityManager = $serviceManager->get('doctrine.entitymanager.orm_default')->getEventManager();
$entityManager->addEventListener(array( \Doctrine\ORM\Events::loadClassMetadata ), new PrependTableEvent() );


The
PrependTableEvent
object uses session data to know which schema to use, it looks like so:

namespace Application\Model;

use Zend\Session\Container;

class PrependTableEvent {

private $session;

public function __construct() {

$this->session = new Container('base');
}

public function loadClassMetadata(\Doctrine\ORM\Event\LoadClassMetadataEventArgs $eventArgs) {

$classMetadata = $eventArgs->getClassMetadata();
$table = $classMetadata->table;
$table_name = explode('.', $table['name']);

if ( 'root' != $table_name[0] && NULL !== $this->session->queryschema ) {
$table['name'] = $this->session->queryschema . '.' . $table_name[1];
}

$classMetadata->setPrimaryTable($table);
}
}


In order for
loadClassMetadata
to be called everytime the
queryschema
changes I built a
QuerySchemaManager
that looks like so:

namespace Application\Model;

use Doctrine\ORM\Events,
Doctrine\ORM\Event\LoadClassMetadataEventArgs,
Doctrine\ORM\Mapping\ClassMetadata,
Doctrine\ORM\EntityManager,
Zend\Session\Container;

class QuerySchemaManager {

private static $session;
private static $initialized = FALSE;

private static function initialize() {

QuerySchemaManager::$session = new Container('base');
QuerySchemaManager::$initialized = TRUE;
}

public static function reload_table_name( EntityManager $em, $class, $schema) {

if ( ! QuerySchemaManager::$initialized ) {

QuerySchemaManager::initialize();
}

QuerySchemaManager::$session->queryschema = $schema;
if ($em->getEventManager()->hasListeners(Events::loadClassMetadata)) {

$eventArgs = new LoadClassMetadataEventArgs($em->getClassMetadata($class), $em);
$em->getEventManager()->dispatchEvent(Events::loadClassMetadata, $eventArgs);
}
}

public static function reload_all_table_names( EntityManager $em, $schema) {

if ( ! QuerySchemaManager::$initialized ) {

QuerySchemaManager::initialize();
}

QuerySchemaManager::$session->queryschema = $schema;
if ($em->getEventManager()->hasListeners(Events::loadClassMetadata)) {

$metadatas = $em->getMetadataFactory()->getAllMetadata();
foreach($metadatas as $metadata) {
$eventArgs = new LoadClassMetadataEventArgs($metadata, $em);
$em->getEventManager()->dispatchEvent(Events::loadClassMetadata, $eventArgs);
}
}
}
}


All that code works great and properly updates the ClassMetadata files for each entity.

THE PROBLEM:

I have an issue with Doctrine 2 where when I insert values into a table for Tenant A and then try to insert values into the same table for Tenant B, all the rows get inserted into Tenant A's table.

I spent a lot of time following break points to find the problem, but I still have no idea how to solve it.

(Note: All the following Code is from Doctrine, so it can't/shouldn't be edited by me)

The problem is that
EntityManager->unitOfWork
has a private array called
$persisters
that stores (in my case)
BasicEntityPersister
objects. Every time one of the
BasicEntityPersister
s are needed
UnitOfWork
's
getEntityPersister($entityName)
is called which looks like so:

public function getEntityPersister($entityName)
{
if ( ! isset($this->persisters[$entityName])) {
$class = $this->em->getClassMetadata($entityName);
if ($class->isInheritanceTypeNone()) {
$persister = new Persisters\BasicEntityPersister($this->em, $class);
} else if ($class->isInheritanceTypeSingleTable()) {
$persister = new Persisters\SingleTablePersister($this->em, $class);
} else if ($class->isInheritanceTypeJoined()) {
$persister = new Persisters\JoinedSubclassPersister($this->em, $class);
} else {
$persister = new Persisters\UnionSubclassPersister($this->em, $class);
}
$this->persisters[$entityName] = $persister;
}
return $this->persisters[$entityName];
}


So it will create one
BasicEntityPersister
per entity (i.e. Application\Model\User will have one
BasicEntityPersister
even though its schema name will dynamically change), which is fine.

Each
BasicEntityPersister
has a private member called
$insertSql
which stores the insert SQL statement once it has been created. When the insert statement is needed this method is called:

protected function getInsertSQL()
{
if ($this->insertSql !== null) {
return $this->insertSql;
}

$columns = $this->getInsertColumnList();
$tableName = $this->quoteStrategy->getTableName($this->class, $this->platform);

if (empty($columns)) {
$identityColumn = $this->quoteStrategy->getColumnName($this->class->identifier[0], $this->class, $this->platform);
$this->insertSql = $this->platform->getEmptyIdentityInsertSQL($tableName, $identityColumn);

return $this->insertSql;
}

$values = array();
$columns = array_unique($columns);

foreach ($columns as $column) {
$placeholder = '?';

if (isset($this->class->fieldNames[$column])
&& isset($this->columnTypes[$this->class->fieldNames[$column]])
&& isset($this->class->fieldMappings[$this->class->fieldNames[$column]]['requireSQLConversion'])) {

$type = Type::getType($this->columnTypes[$this->class->fieldNames[$column]]);
$placeholder = $type->convertToDatabaseValueSQL('?', $this->platform);
}

$values[] = $placeholder;
}

$columns = implode(', ', $columns);
$values = implode(', ', $values);

$this->insertSql = sprintf('INSERT INTO %s (%s) VALUES (%s)', $tableName, $columns, $values);

return $this->insertSql;
}


These three lines are the culprit:

if ($this->insertSql !== null) {
return $this->insertSql;
}


If those lines were commented out then it would work perfectly as the metadata it uses to create the insertSql statement updates properly. I can't find a way to delete/overwrite the insertSql variable, or to even delete/overwrite the whole
BasicEntityPersister
.

Anyone who's implemented a multi-tenant environment using Doctrine 2 I would like to know how you did it. I don't mind redoing all or large parts of my work, I just need to know what the best way to go about doing this is. Thanks in advance.

Answer

You are fighting against the ORM (which is meant to be generating the SQL for you). This should be a signal that you perhaps are going about things in the wrong way.

Rather than modify the persisters (that generate the SQL strings) you should be adding an additional EntityManager. Each entity EntityManager (and therefore UnitOfWork) are designed to persist to one schema; so your second one would simple handle the persistence to the second database - No need to change Doctrine internals!

I have not personally tried to connect to two schemas; however reading into it it seems that it should be possible with the DoctrineModule v1.0.

Comments