Doctrine 1
  1. Doctrine 1
  2. DC-135

Nested i18n versionable behaviour

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Can't Fix
    • Affects Version/s: 1.2.0-ALPHA3
    • Fix Version/s: 1.2.0-BETA1
    • Component/s: Behaviors
    • Labels:
      None

      Description

      Sorry to bring this up again (http://trac.doctrine-project.org/ticket/1708), but the current ALPHA 3 works perfectly on MySQL and fails on PostgreSQL. I tried the simple wiki example on a fresh project. I posted it already on google groups, but it seems this is the right place for that: http://groups.google.com/group/doctrine-user/browse_thread/thread/32208d921dffb8b3/63eb7337015f4415?show_docid=63eb7337015f4415

      This is the generated postgresql sql file:

      CREATE TABLE wiki_translation_version (id BIGINT, lang CHAR(2), title VARCHAR(255), content TEXT, version BIGINT, PRIMARY KEY(id, lang, version));
      CREATE TABLE wiki_translation_index (id BIGINT, lang CHAR(2), keyword VARCHAR(200), field VARCHAR(50), position BIGINT, PRIMARY KEY(id, lang, keyword, field, position));
      CREATE TABLE wiki_translation (id BIGINT, title VARCHAR(255), content TEXT, lang CHAR(2), version BIGINT, slug VARCHAR(255), PRIMARY KEY(id, lang));
      CREATE TABLE wiki (id BIGSERIAL, created_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL, PRIMARY KEY(id));
      CREATE UNIQUE INDEX sluggable ON wiki_translation (slug);
      ALTER TABLE wiki_translation_version ADD CONSTRAINT wiki_translation_version_id_wiki_translation_id FOREIGN KEY (id) REFERENCES wiki_translation(id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;
      ALTER TABLE wiki_translation_index ADD CONSTRAINT wiki_translation_index_id_wiki_translation_id FOREIGN KEY (id) REFERENCES wiki_translation(id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;
      ALTER TABLE wiki_translation ADD CONSTRAINT wiki_translation_id_wiki_id FOREIGN KEY (id) REFERENCES wiki(id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;

      The error is:

      SQLSTATE[42830]: Invalid foreign key: 7 FEHLER: in Tabelle »wiki_translation«, auf die verwiesen wird, gibt es keinen Unique Constraint, der auf die angegebenen Schlüssel passt. Failing Query: ALTER TABLE wiki_translation_version ADD CONSTRAINT wiki_translation_version_id_wiki_translation_id FOREIGN KEY (id) REFERENCES wiki_translation(id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE

      It is in german but it means something like: there is no unique constraint in table 'wiki_translation' matching the given key.

      Michael

        Activity

        Hide
        Jonathan H. Wage added a comment -

        hmm...I am not really sure what is wrong with that SQL on pgsql. Any ideas?

        Show
        Jonathan H. Wage added a comment - hmm...I am not really sure what is wrong with that SQL on pgsql. Any ideas?
        Hide
        Michael Piecko added a comment - - edited

        Ok, i spend some days on this, but i don't know if this is the real issue or how to solve it:

        The table wiki_translation has a two column primary key (constraint) with (id, lang), so all ALTER commands which references this table (1st and 2nd in my post above) need to reference BOTH of them, because just (id) is not unique in the translation table (that makes sense). So what i did is to change the first two ALTER commands (which references the translation table) to:

        ... FOREIGN KEY (id, lang) REFERENCES wiki_translation(id, lang) ...

        This works on pgsql. But i don't know why MySQL obviously has no problems with it ... :o(

        Michael

        Show
        Michael Piecko added a comment - - edited Ok, i spend some days on this, but i don't know if this is the real issue or how to solve it: The table wiki_translation has a two column primary key (constraint) with (id, lang), so all ALTER commands which references this table (1st and 2nd in my post above) need to reference BOTH of them, because just (id) is not unique in the translation table (that makes sense). So what i did is to change the first two ALTER commands (which references the translation table) to: ... FOREIGN KEY (id, lang) REFERENCES wiki_translation(id, lang) ... This works on pgsql. But i don't know why MySQL obviously has no problems with it ... :o( Michael
        Hide
        Jonathan H. Wage added a comment -

        Ok I think I know the issue. I will try and commit a patch for it this week.

        Show
        Jonathan H. Wage added a comment - Ok I think I know the issue. I will try and commit a patch for it this week.
        Hide
        Michael Piecko added a comment -

        Great to hear that. I'll be there to test it ;o)

        Thx a lot,
        Michael

        Show
        Michael Piecko added a comment - Great to hear that. I'll be there to test it ;o) Thx a lot, Michael
        Hide
        Jonathan H. Wage added a comment -

        Ok I was wrong. This can't be fixed. I gave it a good solid try but I can't fix it without completely breaking BC. Users would not be able to upgrade easily and it is a huge change. It will have to remain broken until someone can figure out something that works and is BC.

        Show
        Jonathan H. Wage added a comment - Ok I was wrong. This can't be fixed. I gave it a good solid try but I can't fix it without completely breaking BC. Users would not be able to upgrade easily and it is a huge change. It will have to remain broken until someone can figure out something that works and is BC.
        Hide
        Michael Piecko added a comment -

        Well, that's the way life goes ... :o(
        But it should be mentioned, that nesting these behaviours WORKS on mysql, but in this case not on pgsql.

        Thanks a lot,
        Michael

        Show
        Michael Piecko added a comment - Well, that's the way life goes ... :o( But it should be mentioned, that nesting these behaviours WORKS on mysql, but in this case not on pgsql. Thanks a lot, Michael
        Hide
        thibault duplessis added a comment -

        Hello

        This really shall appear on the documentation.

        Right here : http://www.doctrine-project.org/documentation/manual/1_2/en/behaviors#nesting-behaviors

        The documentation suggests it is possible, but it's not.

        Show
        thibault duplessis added a comment - Hello This really shall appear on the documentation. Right here : http://www.doctrine-project.org/documentation/manual/1_2/en/behaviors#nesting-behaviors The documentation suggests it is possible, but it's not.
        Hide
        Chris Miller added a comment -

        So will this be fixed in 2.0?

        Versioning with i18n is a MUST HAVE for a project I'm currently working on and MySQL isn't an option.

        Show
        Chris Miller added a comment - So will this be fixed in 2.0? Versioning with i18n is a MUST HAVE for a project I'm currently working on and MySQL isn't an option.

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Michael Piecko
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: