This forum is in READ-ONLY mode.
You can look around, but if you want to ask a new question, please use Stack Overflow.

[PATCH] Migrating relationships Between Different Databases

This is for discussion, mainly on 1.x but there's some 2.x topics here too.

[PATCH] Migrating relationships Between Different Databases

by xtrm » Wed Oct 03, 2012 3:05 pm

I have made some testing and changes to the core file Export.php in order to be able to automatically migrate and add the proper changes when we have some relations between different tables in different databases.
C:\php5\PEAR\symfony\plugins\sfDoctrinePlugin\lib\vendor\doctrine\Doctrine\Export.php
Its a very common scenario to have shared data between a lot of databases, so it could be a simple fix to add this feature.
The changes will probably only work with MySQL, but if you are an advanced programmer, you will find really easy to fix it for your Database enviroment.

In order to fix it, we just need to add the prefix of the database before the referenced table to which we want to create the relationship with.
database2.table1(id) //FIXED


Code: Select all
    /**
     * getForeignKeyBaseDeclaration
     * Obtain DBMS specific SQL code portion needed to set the FOREIGN KEY constraint
     * of a field declaration to be used in statements like CREATE TABLE.
     *
     * @param array $definition
     * @return string
     */
    public function getForeignKeyBaseDeclaration(array $definition)
    {
        $sql = '';
        //BD Origin
        $slocalkey = $definition['local'];                   
        //$sTableOrigen = $definition['tboriginname'];       
        $sConnectionOrigen = $this->conn->getOption('dsn');
        $dpos = (stripos($sConnectionOrigen, "dbname=")) + 7;
        $sBDOrigen = substr($sConnectionOrigen, $dpos);      //??? -- db001
       
        //BD Final
        $sfinalkey = $definition['foreign'];                 //id
        $sTableFinal = $definition['foreignTable'];
        //Guess the name of the second database (of the second table) that we are going to do our relationship.
        $scomandoSQL = "SELECT DISTINCT t.CONSTRAINT_SCHEMA " .
        "FROM `information_schema`.`TABLE_CONSTRAINTS` t " .
        "JOIN `information_schema`.`KEY_COLUMN_USAGE` k " .
        "USING (`TABLE_NAME`) " .
        "WHERE (t.`TABLE_NAME`='" . $sTableFinal . "') AND (k.`COLUMN_NAME`='" . $sfinalkey ."') " .
        "AND (t.CONSTRAINT_SCHEMA NOT LIKE '%backup%') " . //optional, to avoid getting other database names, also it will be nice to add limit 1.
        ";";
        $results = $this->conn->fetchAssoc($scomandoSQL);
        $sBD2 = $results[0]['CONSTRAINT_SCHEMA'];
        $sBDFinal = $sBD2;                                   //??? -- database2

        if (isset($definition['name'])) {
            $sql .= 'CONSTRAINT ' . $this->conn->quoteIdentifier($this->conn->formatter->getForeignKeyName($definition['name'])) . ' ';
        }
        $sql .= 'FOREIGN KEY (';
        //$sql .= $sBDOrigen . '.' . $sTableOrigen . '.';//do not add it, gives error in mysql, just for reference.

        if ( ! isset($definition['local'])) {
            throw new Doctrine_Export_Exception('Local reference field missing from definition.');
        }
        if ( ! isset($definition['foreign'])) {
            throw new Doctrine_Export_Exception('Foreign reference field missing from definition.');
        }
        if ( ! isset($definition['foreignTable'])) {
            throw new Doctrine_Export_Exception('Foreign reference table missing from definition.');
        }

        if ( ! is_array($definition['local'])) {
            $definition['local'] = array($definition['local']);
        }
        if ( ! is_array($definition['foreign'])) {
            $definition['foreign'] = array($definition['foreign']);
        }

        $sql .= implode(', ', array_map(array($this->conn, 'quoteIdentifier'), $definition['local']))
              . ') REFERENCES '
              . $sBDFinal . '.' //FIXED
              . $this->conn->quoteIdentifier($definition['foreignTable']) . '('
              . implode(', ', array_map(array($this->conn, 'quoteIdentifier'), $definition['foreign'])) . ')';

        return $sql;
    }


Just get the name of the database and you will be able to fix it. I dont know how to get the name using symfony, but probably we should pass the name in the functions so this fix could work in all systems.
xtrm
Junior Member
 
Posts: 10
Joined: Thu Apr 28, 2011 4:24 pm

Re: [PATCH] Migrating relationships Between Different Databa

by Primavera888 » Mon Nov 04, 2013 12:21 pm

I still don't know how to to this...
Primavera888
Junior Member
 
Posts: 3
Joined: Mon Nov 04, 2013 12:06 pm