How to setup table prefix in symfony2

Question

Like in question topic, how can I setup default table prefix in symfony2?

The best if it can be set by default for all entities, but with option to override for individual ones.

Answer 1

I don't when to implement a solution that involved catching event (performance concern), so I have tried the Alternate Solution but it doesn't work for me. I was adding the JMSPaymentCoreBundle and wanted to add a prefix on the payment tables. In this bundle, the definition of the tables are in the Resources\config\doctrine directory (xml format). I have finally found this solution:

1) copy doctrine directory containing the definitions on the table and paste it in my main bundle

2) modify the name of the tables in the definitions to add your prefix

3) declare it in your config.yml, in the doctrine/orm/entity manager/mapping section (the dir is the directory where you have put the modified definitions):

doctrine:
  orm:
      ...
      entity_managers:
         default:
            mappings:
               ...
               JMSPaymentCoreBundle:
                   mapping: true
                   type: xml
                   dir: "%kernel.root_dir%/Resources/JMSPayment/doctrine"
                   alias: ~
                   prefix: JMS\Payment\CoreBundle\Entity
                   is_bundle: false

Answer 2

I found solution: http://www.doctrine-project.org/docs/orm/2.0/en/cookbook/sql-table-prefixes.html

Answer 3

@simshaun answer is good, but there is a problem with Many-to-Many relationships and inheritance.

If you have a parent class User and a child class Employee, and the Employee own a Many-to-Many field $addresses, this field's table will not have a prefix. That is because of:

if ($classMetadata->isInheritanceTypeSingleTable() && !$classMetadata->isRootEntity()) {
    // if we are in an inheritance hierarchy, only apply this once
    return;
}

User class (parent)

namespace FooBundle\Bar\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * User
 *
 * @ORM\Entity()
 * @ORM\Table(name="user")
 * @ORM\InheritanceType("SINGLE_TABLE")
 * @ORM\DiscriminatorColumn(name="type", type="string")
 * @ORM\DiscriminatorMap({"user" = "User", "employee" = "\FooBundle\Bar\Entity\Employee"})
 */
class User extends User {

}

Employee class (child)

namespace FooBundle\Bar\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * User
 *
 * @ORM\Entity()
 */
class Employee extends FooBundle\Bar\Entity\User {
    /**
     * @var ArrayCollection $addresses
     * 
     * @ORM\ManyToMany(targetEntity="\FooBundle\Bar\Entity\Adress")
     * @ORM\JoinTable(name="employee_address",
     *      joinColumns={@ORM\JoinColumn(name="employee_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="address_id", referencedColumnName="id")}
     *      )
     */
    private $addresses;
}

Address class (relation with Employee)

namespace FooBundle\Bar\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * User
 *
 * @ORM\Entity()
 * @ORM\Table(name="address")
 */
class Address {

}

With the original solution, if you apply pref_ prefixe to this mapping, you will end up with tables :

• pref_user
• pref_address
• employee_address

Solution

A solution can be to modify, in the answer of @simshaun, the point 4 like this:

4. Create MyBundle\Subscriber\TablePrefixSubscriber.php

   <?php
   namespace MyBundle\Subscriber;
   
   use Doctrine\ORM\Event\LoadClassMetadataEventArgs;
   
   class TablePrefixSubscriber implements \Doctrine\Common\EventSubscriber
   {
       protected $prefix = '';
   
       public function __construct($prefix)
       {
           $this->prefix = (string) $prefix;
       }
   
       public function getSubscribedEvents()
       {
           return array('loadClassMetadata');
       }
   
       public function loadClassMetadata(LoadClassMetadataEventArgs $args)
       {
           $classMetadata = $args->getClassMetadata();
   
           // Put the Many-yo-Many verification before the "inheritance" verification. Else fields of the child entity are not taken into account
           foreach($classMetadata->getAssociationMappings() as $fieldName => $mapping) {
               if($mapping['type'] == \Doctrine\ORM\Mapping\ClassMetadataInfo::MANY_TO_MANY
                   && array_key_exists('name', $classMetadata->associationMappings[$fieldName]['joinTable'])   // Check if "joinTable" exists, it can be null if this field is the reverse side of a ManyToMany relationship
                   && $mapping['sourceEntity'] == $classMetadata->getName()        // If this is not the root entity of an inheritance mapping, but the "child" entity is owning the field, prefix the table.
               ) {
                   $mappedTableName = $classMetadata->associationMappings[$fieldName]['joinTable']['name'];
                   $classMetadata->associationMappings[$fieldName]['joinTable']['name'] = $this->prefix . $mappedTableName;
               }
           }
   
           if($classMetadata->isInheritanceTypeSingleTable() && !$classMetadata->isRootEntity()) {
               // if we are in an inheritance hierarchy, only apply this once
               return;
           }
   
           $classMetadata->setTableName($this->prefix . $classMetadata->getTableName());
       }        
   }
   

Here we handle the Many-to-Many relationship before verifying if the class is the child of an inheritance, and we add $mapping['sourceEntity'] == $classMetadata->getName() to add the prefix only one time, on the owning entity of the field.

Answer 4

Having just figured this out myself, I'd like to shed some light on exactly how to accomplish this.

Symfony 2 & Doctrine 2.1
_{Note: I use YML for config, so that's what I'll be showing.}

Instructions

1. Open up your bundle's Resources/config/services.yml

2. Define a table prefix parameter:
   _{Be sure to change mybundle and myprefix_}

   parameters:
       mybundle.db.table_prefix: myprefix_
   

3. Add a new service:

   services:
       mybundle.tblprefix_subscriber:
           class: MyBundle\Subscriber\TablePrefixSubscriber
           arguments: [%mybundle.db.table_prefix%]
           tags:
               - { name: doctrine.event_subscriber }
   

4. Create MyBundle\Subscriber\TablePrefixSubscriber.php

   <?php
   namespace MyBundle\Subscriber;
   
   use Doctrine\ORM\Event\LoadClassMetadataEventArgs;
   
   class TablePrefixSubscriber implements \Doctrine\Common\EventSubscriber
   {
       protected $prefix = '';
   
       public function __construct($prefix)
       {
           $this->prefix = (string) $prefix;
       }
   
       public function getSubscribedEvents()
       {
           return array('loadClassMetadata');
       }
   
       public function loadClassMetadata(LoadClassMetadataEventArgs $args)
       {
           $classMetadata = $args->getClassMetadata();
           if ($classMetadata->isInheritanceTypeSingleTable() && !$classMetadata->isRootEntity()) {
               // if we are in an inheritance hierarchy, only apply this once
               return;
           }
   
           $classMetadata->setTableName($this->prefix . $classMetadata->getTableName());
   
           foreach ($classMetadata->getAssociationMappings() as $fieldName => $mapping) {
               if ($mapping['type'] == \Doctrine\ORM\Mapping\ClassMetadataInfo::MANY_TO_MANY 
                       && array_key_exists('name', $classMetadata->associationMappings[$fieldName]['joinTable']) ) {     // Check if "joinTable" exists, it can be null if this field is the reverse side of a ManyToMany relationship
                   $mappedTableName = $classMetadata->associationMappings[$fieldName]['joinTable']['name'];
                   $classMetadata->associationMappings[$fieldName]['joinTable']['name'] = $this->prefix . $mappedTableName;
               }
           }
       }       
   }
   

5. Optional step for postgres users: do something similary for sequences

6. Enjoy

Answer 5

Alternate answer

This is an update taking into account the newer features available in Doctrine2.

Doctrine2 naming strategy

Doctrine2 uses NamingStrategy classes which implement the conversion from a class name to a table name or from a property name to a column name.

The DefaultNamingStrategy just finds the "short class name" (without its namespace) in order to deduce the table name.

The UnderscoreNamingStrategy does the same thing but it also lowercases and "underscorifies" the "short class name".

Your CustomNamingStrategy class could extend either one of the above (as you see fit) and override the classToTableName and joinTableName methods to allow you to specify how the table name should be constructed (with the use of a prefix).

For example my CustomNamingStrategy class extends the UnderscoreNamingStrategy and finds the bundle name based on the namespacing conventions and uses that as a prefix for all tables.

---

Symfony2 naming strategy

Using the above in Symfony2 requires declaring your CustomNamingStragery class as a service and then referencing it in your config:

doctrine:
    # ...

    orm:
        # ...
        #naming_strategy: doctrine.orm.naming_strategy.underscore
        naming_strategy: my_bundle.naming_strategy.prefixed_naming_strategy

---

Pros and cons

Pros:

• running one piece of code to do one single task -- your naming strategy class is called directly and its output is used;
• clarity of structure -- you're not using events to run code which alter things that have already been built by other code;
• better access to all aspects of the naming conventions;

Cons:

• zero access to mapping metadata -- you only have the context that was given to you as parameters (this can also be a good thing because it forces convention rather than exception);
• needs doctrine 2.3 (not that much of a con now, it might have been in 2011 when this question was asked :-));

Answer 6

Simshaun's answer works fine, but has a problem when you have a single_table inheritance, with associations on the child entity. The first if-statement returns when the entity is not the rootEntity, while this entity might still have associations that have to be prefixed.

I fixed this by adjusting the subscriber to the following:

<?php
namespace MyBundle\Subscriber;

use Doctrine\Common\EventSubscriber;
use Doctrine\ORM\Event\LoadClassMetadataEventArgs;
use Doctrine\ORM\Mapping\ClassMetadataInfo;

class TablePrefixSubscriber implements EventSubscriber
{
    protected $prefix = '';

    /**
     * Constructor
     *
     * @param string $prefix
     */
    public function __construct($prefix)
    {
        $this->prefix = (string) $prefix;
    }

    /**
     * Get subscribed events
     *
     * @return array
     */
    public function getSubscribedEvents()
    {
        return array('loadClassMetadata');
    }

    /**
     * Load class meta data event
     *
     * @param LoadClassMetadataEventArgs $args
     *
     * @return void
     */
    public function loadClassMetadata(LoadClassMetadataEventArgs $args)
    {
        $classMetadata = $args->getClassMetadata();

        // Only add the prefixes to our own entities.
        if (FALSE !== strpos($classMetadata->namespace, 'Some\Namespace\Part')) {
            // Do not re-apply the prefix when the table is already prefixed
            if (false === strpos($classMetadata->getTableName(), $this->prefix)) {
                $tableName = $this->prefix . $classMetadata->getTableName();
                $classMetadata->setPrimaryTable(['name' => $tableName]);
            }

            foreach ($classMetadata->getAssociationMappings() as $fieldName => $mapping) {
                if ($mapping['type'] == ClassMetadataInfo::MANY_TO_MANY && $mapping['isOwningSide'] == true) {
                    $mappedTableName = $classMetadata->associationMappings[$fieldName]['joinTable']['name'];

                    // Do not re-apply the prefix when the association is already prefixed
                    if (false !== strpos($mappedTableName, $this->prefix)) {
                        continue;
                    }

                    $classMetadata->associationMappings[$fieldName]['joinTable']['name'] = $this->prefix . $mappedTableName;
                }
            }
        }
    }
}

This has a drawback though; A not wisely chosen prefix might cause conflicts when it's actually already part of a table name. E.g. using prefix 'co' when theres a table called 'content' will result in a non-prefixed table, so using an underscore like 'co_' will reduce this risk.