How to setup table prefix in symfony2

20

votes

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.

phpsymfonydoctrine-orm

I found solution: doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/… – canni

47

votes

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

Open up your bundle's Resources/config/services.yml
Define a table prefix parameter:
_{Be sure to change mybundle and myprefix_}
```
parameters:
    mybundle.db.table_prefix: myprefix_
```

Add a new service:

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

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;
            }
        }
    }       
}

Optional step for postgres users: do something similary for sequences
Enjoy

11

votes

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 :-));

2

votes

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.

1

votes

Also, you can use this bundle for the new version of Symfony (4) - DoctrinePrefixBundle

0

votes

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

-1

votes

@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:

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.

How to setup table prefix in symfony2

6 Answers

Instructions

Alternate answer

Doctrine2 naming strategy

Symfony2 naming strategy

Pros and cons

User class (parent)

Employee class (child)

Address class (relation with Employee)

Solution