Doctrine 1
  1. Doctrine 1
  2. DC-502

sometimes not all the constraints are created for a many to many relation

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2.0-ALPHA1, 1.2.0-ALPHA2, 1.2.0-ALPHA3, 1.2.0-BETA1, 1.2.0-BETA2, 1.2.0-BETA3, 1.2.0-RC1, 1.2.0, 1.2.1, 1.2.2
    • Fix Version/s: None
    • Component/s: Import/Export, Relations
    • Labels:
      None
    • Environment:
      i tried both under linux / win; with doctrine 1.1 / 1.2.1
      tried with sandbox / direct creating "bootstrap" => same results...

      Description

      it seems that the naming is important:

      in a User, Group, UserGroup relation (where the UserGroup defines the many to many relation for User & Group) the constraints are defined well

      but if the tables are Order, Service, OrderService it doesn't work => just one constraint is created... please find my example:

      schema.yml
      Order:
        tableName:          orders
        columns:
          name:             string(50)
        relations:
          Services:
            class:          Service
            local:          order_id
            foreign:        service_id
            refClass:       OrderService
      
      Service:
        actAs:
          Versionable:      ~
        columns:
          name:             string(50)
        relations:
          Orders:
            class:          Order
            local:          service_id
            foreign:        order_id
            refClass:       OrderService
      
      # WTF: it takes alphbetically the constraints building  !?
      # if I used ZorderService instead of OrderService, Z is after S (O is before S) the constraints are defined fine!
      OrderService:
        columns:
          order_id:         { type: integer, primary: true }
          service_id:       { type: integer, primary: true }
      
      

      ==> the generated SQL

      sql
      CREATE TABLE orders (id BIGINT AUTO_INCREMENT, name VARCHAR(50), PRIMARY KEY(id)) ENGINE = INNODB;
      CREATE TABLE order_service (order_id BIGINT, service_id BIGINT, version BIGINT, PRIMARY KEY(order_id, service_id, 
              version)) ENGINE = INNODB;
      CREATE TABLE service_version (id BIGINT, name VARCHAR(50), version BIGINT, PRIMARY KEY(id, version)) ENGINE = 
              INNODB;
      CREATE TABLE service (id BIGINT AUTO_INCREMENT, name VARCHAR(50), version BIGINT, PRIMARY KEY(id)) ENGINE = 
              INNODB;
      ALTER TABLE order_service ADD CONSTRAINT order_service_order_id_orders_id FOREIGN KEY (order_id) REFERENCES 
              orders(id);
      ALTER TABLE service_version ADD CONSTRAINT service_version_id_service_id FOREIGN KEY (id) REFERENCES service(id) 
              ON UPDATE CASCADE ON DELETE CASCADE;
      

      => as you can see for TABLE order_service just one constraint is defined (order_service_order_id_orders_id) the other one (order_service_service_id_service_id) is missing !!!

      However if i change the names for tables (actually i think alphabetically order) and use ZorderService instead of OrderService (lik UserGroup for User & Group) the two constraints are created!

      This also affects the "getRelations()" when i tried to write a behavior (it gives back just one relation):

        foreach ($event->getInvoker()->getTable()->getRelations() as $relation)
      

      ps:
      also the same bug reported on the list: http://groups.google.com/group/doctrine-user/browse_thread/thread/ad48db71b71e043b

      (edit G.X: split too long lines in SQL code)

        Activity

        Hide
        Piotr Karaś added a comment -

        I seem to have a very similar issue. It is really annoying because no error feedback is given at any point...
        In my case, one out of three many-to-many relations actually has its constraints created, the other two not.
        However, if I take the file date/sql/schema.sql (I am working with Symfony 1.4.4) and run as an SQL query directly with my database, the constraints are created properly!

        So, with symfony, if I go:
        $ symfony14 doctrine:build-model
        $ symfony14 doctrine:build-sql
        $ symfony14 doctrine:insert-sql
        The model gets generated, the database gets generated, but several many-to-many constrains are missing.

        Then if I go:
        $ symfony14 doctrine:build-model
        $ symfony14 doctrine:build-sql
        $ mysql -u user -p database < date/sql/schema.sql
        The model gets generated, the database gets generated, and the constraints are in place.

        Hope this helps to have the problem fixed.

        Cheers,
        Piotrek

        Show
        Piotr Karaś added a comment - I seem to have a very similar issue. It is really annoying because no error feedback is given at any point... In my case, one out of three many-to-many relations actually has its constraints created, the other two not. However, if I take the file date/sql/schema.sql (I am working with Symfony 1.4.4) and run as an SQL query directly with my database, the constraints are created properly! So, with symfony, if I go: $ symfony14 doctrine:build-model $ symfony14 doctrine:build-sql $ symfony14 doctrine:insert-sql The model gets generated, the database gets generated, but several many-to-many constrains are missing. Then if I go: $ symfony14 doctrine:build-model $ symfony14 doctrine:build-sql $ mysql -u user -p database < date/sql/schema.sql The model gets generated, the database gets generated, and the constraints are in place. Hope this helps to have the problem fixed. Cheers, Piotrek
        Hide
        Guilliam X added a comment - - edited

        I do confirm this "alphabetical-order exporting" problem! (in 1.2 svn revision 7676)
        (see also #DC-655)

        *Edit:* I have gone closer through the functions calls and used a debugger, and the problem is actually logical;
        it happens whenever a refClass of a many-to-many relation is parsed (and exported) before one or both of the linked classes.

        Let's take this schema (YAML is shorter, anyways the generated PHP models are not bugged):

        schema.yml
        ---
        LinkTable:
          columns:
            table1_id:
              primary: true
              type:    integer
            table2_id:
              primary: true
              type:    integer
        Table1:
          relations:
            Table2:
              refClass: LinkTable
              local:    table1_id
              foreign:  table2_id
        Table2:
          relations:
            Table1:
              refClass: LinkTable
              local:    table2_id
              foreign:  table1_id
        

        Now if you call Doctrine_Core::createTablesFromModels() or Doctrine_Core::generateSqlFromModels(), consequent call to Doctrine_Export::exportSortedClassesSql() does a loop and calls exportClassesSql() for each model in alphabetical order; once a table has been exported, subsequent bindings of pending relations won't be "retro-parsed" for the export.

        I tried to compact the calls chain:

        php
        /* IN CLASS Doctrine_Export (or child (connection export)) */
        
        $this->exportSortedClassesSql($classes)
        {
            FOREACH ($classes as $class) {
                $connection = Doctrine_Manager::getInstance()->getConnectionForComponent($class);
                //...
                $sql =
                $connection->export->exportClassesSql($models = array($class))
                {
                    FOREACH ($models as $name) {
                        $record = new $name();
                        $table = $record->getTable();
                        //...
                        $data = $table->getExportableFormat()
                        {
                            /* IN CLASS Doctrine_Table (or child) */
                            
                            //...
                            $this->_parser->getRelations()
                            {
                                /* IN CLASS Doctrine_Relation_Parser (or child) */
                                
                                FOREACH ($this->_pending as $alias => $v) {
                                    $this->getRelation($alias)
                                    {
                                        //...
                                        /*
                                         * In the case of an "association relation" through a refTable:
                                         *   binds a *pending* (simple foreign key) relation to the refTable parser,
                                         *   binds a *pending* (simple foreign key) relation to itself,
                                         *   and "finalizes" (adds to $this->_relations) its association relation
                                         */
                                    }
                                }
                            }
                            //...
                        }
                    }
                }
            }
        }
        

        So, with our example schema,

        1. LinkTable is exported with no constraints (because has no relations at this time);
        2. Table1 gets parsed, which finalizes the pending relation with Table2 (binded when loaded), binds to itself a pending relation with LinkTable, and binds to LinkTable parser the first pending relation (with Table1); Table1 is then exported whilst it still has a pending relation;
        3. Table2 gets parsed and exported the same way.

        So now we have 2 problems:

        • each table has been exported immediately after its parsing, i.e. in an incomplete state (this is the present issue)
        • even after the whole relations parsing loop, LinkTable has still 2 pending relations, and each of both other tables has one "final" and one pending (but maybe it is supposed to be so...)

        Note that a second call to Doctrine_Core::generateSqlFromModels() just after the first one, will parse all models one more time and thus finalize those pending relations, generating all constraints (also will subsequent calls).

        For now I don't have a fix but I think we could consider:

        • loop twice in Doctrine_Export::exportSortedClassesSql() and exportClassesSql(), and construct SQL queries only the 2nd time (but... hum...)
        • improve Doctrine_Relation_Parser? maybe explicitly call getRelations() after each bind() (in the getRelation() function)...

        Anyway it seems clear that export shouldn't be done before all tables have been parsed.

        G.X

        Show
        Guilliam X added a comment - - edited I do confirm this "alphabetical-order exporting" problem! (in 1.2 svn revision 7676) (see also # DC-655 ) * Edit: * I have gone closer through the functions calls and used a debugger, and the problem is actually logical; it happens whenever a refClass of a many-to-many relation is parsed (and exported) before one or both of the linked classes. Let's take this schema (YAML is shorter, anyways the generated PHP models are not bugged): schema.yml --- LinkTable: columns: table1_id: primary: true type: integer table2_id: primary: true type: integer Table1: relations: Table2: refClass: LinkTable local: table1_id foreign: table2_id Table2: relations: Table1: refClass: LinkTable local: table2_id foreign: table1_id Now if you call Doctrine_Core::createTablesFromModels() or Doctrine_Core::generateSqlFromModels(), consequent call to Doctrine_Export::exportSortedClassesSql() does a loop and calls exportClassesSql() for each model in alphabetical order; once a table has been exported, subsequent bindings of pending relations won't be "retro-parsed" for the export. I tried to compact the calls chain: php /* IN CLASS Doctrine_Export (or child (connection export)) */ $ this ->exportSortedClassesSql($classes) { FOREACH ($classes as $class) { $connection = Doctrine_Manager::getInstance()->getConnectionForComponent($class); //... $sql = $connection->export->exportClassesSql($models = array($class)) { FOREACH ($models as $name) { $record = new $name(); $table = $record->getTable(); //... $data = $table->getExportableFormat() { /* IN CLASS Doctrine_Table (or child) */ //... $ this ->_parser->getRelations() { /* IN CLASS Doctrine_Relation_Parser (or child) */ FOREACH ($ this ->_pending as $alias => $v) { $ this ->getRelation($alias) { //... /* * In the case of an "association relation" through a refTable: * binds a *pending* (simple foreign key) relation to the refTable parser, * binds a *pending* (simple foreign key) relation to itself, * and "finalizes" (adds to $ this ->_relations) its association relation */ } } } //... } } } } } So, with our example schema, LinkTable is exported with no constraints (because has no relations at this time); Table1 gets parsed, which finalizes the pending relation with Table2 (binded when loaded), binds to itself a pending relation with LinkTable, and binds to LinkTable parser the first pending relation (with Table1); Table1 is then exported whilst it still has a pending relation; Table2 gets parsed and exported the same way. So now we have 2 problems: each table has been exported immediately after its parsing, i.e. in an incomplete state ( this is the present issue ) even after the whole relations parsing loop, LinkTable has still 2 pending relations , and each of both other tables has one "final" and one pending (but maybe it is supposed to be so...) Note that a second call to Doctrine_Core::generateSqlFromModels() just after the first one, will parse all models one more time and thus finalize those pending relations, generating all constraints (also will subsequent calls). For now I don't have a fix but I think we could consider: loop twice in Doctrine_Export::exportSortedClassesSql() and exportClassesSql(), and construct SQL queries only the 2nd time (but... hum...) improve Doctrine_Relation_Parser? maybe explicitly call getRelations() after each bind() (in the getRelation() function)... Anyway it seems clear that export shouldn't be done before all tables have been parsed. G.X
        Hide
        Guilliam X added a comment - - edited

        I still have no fix but in the waiting we can still discuss some "user-side" work-arounds (that don't need modifying Doctrine source code)...
        Here is what crosses my mind first:

        • (in the PHP working code) do a first "dummy" call to
          $conn->export->exportClassesSql(Doctrine_Core::loadModels('models'))

          just to ensure all relations are bound, then a "real" call to Doctrine_Core::createTablesFromModels('models') which will export all constraints well

        • (in the schema) rename LinkTable to something like ZzLinkTable so that it is exported last
        • "explicitize" the two (one-to-many) foreign key relations in LinkTable by completing the schema as follows:
          schema.yml
          ---
          LinkTable:
            columns:
              table1_id:
                primary: true
                type:    integer
              table2_id:
                primary: true
                type:    integer
          #(added)
            relations:
              Table1:
                local:   table1_id
                foreign: id
              Table2:
                local:   table2_id
                foreign: id
          #(/added)
          Table1:
            relations:
              Table2:
                refClass: LinkTable
                local:    table1_id
                foreign:  table2_id
          Table2:
            relations:
              Table1:
                refClass: LinkTable
                local:    table2_id
                foreign:  table1_id
          

          (I think this one would also "solve" the behavior problem of Papp Richard)

        But I'm not that happy with the idea of choosing one of these "hacks" if I had to, I would use this last one but (unless you use some "onDelete: CASCADE" or so) it looks like unnecessary duplication...
        Someone, any other idea? ^^

        G.X

        (edit: typo fix)

        Show
        Guilliam X added a comment - - edited I still have no fix but in the waiting we can still discuss some "user-side" work-arounds (that don't need modifying Doctrine source code)... Here is what crosses my mind first: (in the PHP working code) do a first "dummy" call to $conn->export->exportClassesSql(Doctrine_Core::loadModels('models')) just to ensure all relations are bound, then a "real" call to Doctrine_Core::createTablesFromModels('models') which will export all constraints well (in the schema) rename LinkTable to something like ZzLinkTable so that it is exported last "explicitize" the two (one-to-many) foreign key relations in LinkTable by completing the schema as follows: schema.yml --- LinkTable: columns: table1_id: primary: true type: integer table2_id: primary: true type: integer #(added) relations: Table1: local: table1_id foreign: id Table2: local: table2_id foreign: id #(/added) Table1: relations: Table2: refClass: LinkTable local: table1_id foreign: table2_id Table2: relations: Table1: refClass: LinkTable local: table2_id foreign: table1_id ( I think this one would also "solve" the behavior problem of Papp Richard ) But I'm not that happy with the idea of choosing one of these "hacks" if I had to, I would use this last one but (unless you use some " onDelete: CASCADE " or so) it looks like unnecessary duplication... Someone, any other idea? ^^ G.X (edit: typo fix)
        Hide
        Guilliam X added a comment - - edited

        We can try another approach too: let's say that our generated PHP models classes are incomplete.

        Indeed, for a simple foreign key relation (i.e. o2o and o2m / m2o), we can define it only once on the owning side in the YAML file, and the schema importer will generate complete PHP classes, with the "hasOne" on the owning side and the auto-completed "hasMany" on the opposite side. E.g:

        YAML schema
        Phonenumber:
          columns:
            user_id: integer
          relations:
            User: {local: user_id, foreign: id}
        User:
        # we don't precise the opposite relation here
        

        will generate the following classes (simplified a bit):

        PHP models (1)
        // models/Phonenumber.php
        class Phonenumber extends Doctrine_Record
        {
            public function setTableDefinition() {
                $this->hasColumn('user_id', 'integer');
            }
        
            public function setUp() {
                $this->hasOne('User', array('local'   => 'user_id',
                                            'foreign' => 'id'));
            }
        }
        
        // models/User.php
        class User extends Doctrine_Record
        {
            public function setTableDefinition() {
            }
        
            public function setUp() {
                // this was auto-added during the schema import:
                $this->hasMany('Phonenumber', array('local'   => 'id',
                                                    'foreign' => 'user_id'));
            }
        }
        

        and this will work well.
        BUT, now if we remove the auto-added hasMany() in User.php, then run the following (which worked well before our manual modification):

        test.php
        require_once 'bootstrap.php';
        
        $user = new User();
        Doctrine_Core::dump($user->Phonenumber);
        

        we get some Fatal error: Uncaught exception 'Doctrine_Record_UnknownPropertyException' with message 'Unknown record property / related component "Phonenumber" on "User"', which is supposed to occur. Indeed, how could Doctrine know that a User has a "link" with Phonenumber? It would be only possible by parsing the relations of all the (loaded) models first...

        So we can say that YAML schema files can be incomplete whereas PHP models classes MUST be COMPLETE.

        Now, considering it is not to change, why not apply this "principle" to (m2m) association relations?
        i.e., Doctrine documentation could warn that, if you want to have all constraints properly exported (like in the present issue) and/or use relations from an association class (having $link instance of LinkTable, you want to use $link->Table1, not just $link->table1_id), then you MUST complete your PHP models with the FK relations, like this:

        PHP models (2)
        // models/LinkTable.php
        class LinkTable extends Doctrine_Record
        {
            public function setTableDefinition() {
                $this->hasColumn('table1_id', 'integer', null, array(
                        'primary' => true));
                $this->hasColumn('table2_id', 'integer', null, array(
                        'primary' => true));
            }
        
            public function setUp() {
                // ADD:
                $this->hasOne('Table1', array('local'   => 'table1_id',
                                              'foreign' => 'id'));
                $this->hasOne('Table2', array('local'   => 'table2_id',
                                              'foreign' => 'id'));
            }
        }
        
        // models/Table1.php
        class Table1 extends Doctrine_Record
        {
            public function setTableDefinition() {
            }
        
            public function setUp() {
                $this->hasMany('Table2', array('refClass' => 'LinkTable',
                                               'local'    => 'table1_id',
                                               'foreign'  => 'table2_id'));
                // add too?:
                $this->hasMany('LinkTable', array('local'    => 'id',
                                                  'foreign'  => 'table1_id'));
            }
        }
        
        // models/Table2.php
        class Table2 extends Doctrine_Record
        {
            public function setTableDefinition() {
            }
        
            public function setUp() {
                $this->hasMany('Table1', array('refClass' => 'LinkTable',
                                               'local'    => 'table2_id',
                                               'foreign'  => 'table1_id'));
                // add too?:
                $this->hasMany('LinkTable', array('local'    => 'id',
                                                  'foreign'  => 'table2_id'));
            }
        }
        

        After all, these additional relations are bound internally (if not already defined) when exporting tables to DB...

        We also said that the YAML can be incomplete, but for now the short schema.yml I took as an example in my first comment results in PHP classes without those (2+1+1) adds.

        So to allow YAML to be incomplete even for m2m relations, Doctrine_Import_Schema#_buildRelationships() would need some "improvement" so that it creates the many-to-one relation for each many-to-many relation (and the opposite one-to-many relation, on the association class side i.e. the owning side, would be auto-completed afterwards). However, a m2m relation is allowed to be defined on one single end, thus may be created one the other end only when _autoCompleteOppositeRelations() is called, i.e. after _buildRelationships() has looped over all the existing relations. So Doctrine_Import_Schema#_autoCompleteOppositeRelations() would need the same kind of improvement...

        In fact I'm not sure of what is a "well defined" model as soon as it uses refClass, the documentation sometimes uses contradictory examples...

        Sorry I was so verbose!
        Thank you for developing Doctrine

        Show
        Guilliam X added a comment - - edited We can try another approach too: let's say that our generated PHP models classes are incomplete. Indeed, for a simple foreign key relation (i.e. o2o and o2m / m2o), we can define it only once on the owning side in the YAML file, and the schema importer will generate complete PHP classes, with the "hasOne" on the owning side and the auto-completed "hasMany" on the opposite side. E.g: YAML schema Phonenumber: columns: user_id: integer relations: User: {local: user_id, foreign: id} User: # we don't precise the opposite relation here will generate the following classes (simplified a bit): PHP models (1) // models/Phonenumber.php class Phonenumber extends Doctrine_Record { public function setTableDefinition() { $ this ->hasColumn('user_id', 'integer'); } public function setUp() { $ this ->hasOne('User', array('local' => 'user_id', 'foreign' => 'id')); } } // models/User.php class User extends Doctrine_Record { public function setTableDefinition() { } public function setUp() { // this was auto-added during the schema import : $ this ->hasMany('Phonenumber', array('local' => 'id', 'foreign' => 'user_id')); } } and this will work well. BUT, now if we remove the auto-added hasMany() in User.php, then run the following (which worked well before our manual modification): test.php require_once 'bootstrap.php'; $user = new User(); Doctrine_Core::dump($user->Phonenumber); we get some Fatal error: Uncaught exception 'Doctrine_Record_UnknownPropertyException' with message 'Unknown record property / related component "Phonenumber" on "User"' , which is supposed to occur. Indeed, how could Doctrine know that a User has a "link" with Phonenumber? It would be only possible by parsing the relations of all the (loaded) models first... So we can say that YAML schema files can be incomplete whereas PHP models classes MUST be COMPLETE. Now, considering it is not to change, why not apply this "principle" to (m2m) association relations? i.e., Doctrine documentation could warn that, if you want to have all constraints properly exported (like in the present issue) and/or use relations from an association class (having $link instance of LinkTable , you want to use $link->Table1 , not just $link->table1_id ), then you MUST complete your PHP models with the FK relations, like this: PHP models (2) // models/LinkTable.php class LinkTable extends Doctrine_Record { public function setTableDefinition() { $ this ->hasColumn('table1_id', 'integer', null , array( 'primary' => true )); $ this ->hasColumn('table2_id', 'integer', null , array( 'primary' => true )); } public function setUp() { // ADD: $ this ->hasOne('Table1', array('local' => 'table1_id', 'foreign' => 'id')); $ this ->hasOne('Table2', array('local' => 'table2_id', 'foreign' => 'id')); } } // models/Table1.php class Table1 extends Doctrine_Record { public function setTableDefinition() { } public function setUp() { $ this ->hasMany('Table2', array('refClass' => 'LinkTable', 'local' => 'table1_id', 'foreign' => 'table2_id')); // add too?: $ this ->hasMany('LinkTable', array('local' => 'id', 'foreign' => 'table1_id')); } } // models/Table2.php class Table2 extends Doctrine_Record { public function setTableDefinition() { } public function setUp() { $ this ->hasMany('Table1', array('refClass' => 'LinkTable', 'local' => 'table2_id', 'foreign' => 'table1_id')); // add too?: $ this ->hasMany('LinkTable', array('local' => 'id', 'foreign' => 'table2_id')); } } After all, these additional relations are bound internally (if not already defined) when exporting tables to DB... We also said that the YAML can be incomplete, but for now the short schema.yml I took as an example in my first comment results in PHP classes without those (2+1+1) adds. So to allow YAML to be incomplete even for m2m relations, Doctrine_Import_Schema#_buildRelationships() would need some "improvement" so that it creates the many-to-one relation for each many-to-many relation (and the opposite one-to-many relation, on the association class side i.e. the owning side, would be auto-completed afterwards). However, a m2m relation is allowed to be defined on one single end, thus may be created one the other end only when _ autoCompleteOppositeRelations() is called, i.e. after _ buildRelationships() has looped over all the existing relations. So Doctrine_Import_Schema#_autoCompleteOppositeRelations() would need the same kind of improvement... In fact I'm not sure of what is a "well defined" model as soon as it uses refClass, the documentation sometimes uses contradictory examples... Sorry I was so verbose! Thank you for developing Doctrine

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Papp Richard
          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: