Doctrine 1
  1. Doctrine 1
  2. DC-231

PostgreSQL problem when Searchable behavior is child of I18n behavior

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Can't Fix
    • Affects Version/s: 1.2.0-BETA2
    • Fix Version/s: None
    • Component/s: Behaviors, I18n, Searchable
    • Labels:
      None
    • Environment:
      symfony 1.3.0 beta 2, php 5.2.8 (php-fpm), nginx, MySQL 5.1.40, PostgreSQL 8.3.8

      Description


      Hello,

      Some time ago i have been reporting a bug "Combination of Searchable and I18n behaviors" - DC-199.

      Somewhere this bug report crosses with my previous one (DC-199).

      I got a simple schema.yml, where is "Post" table specification.

      schema.yml
      Unable to find source-code formatter for language: yml. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
      Post:
        tableName: post
        actAs:
          Timestampable: ~
          I18n:
            fields: [title, blurb]
            actAs:
              Searchable:
                fields: [title, blurb]
        columns:
          id:
            type: integer
            primary: true
            autoincrement: true
            unsigned: true
          slug: { type: string(127), notnull: true }
          is_visible: boolean(false)
          # i18n.columns
          title: string(255)
          blurb: clob
        indexes:
          sc_is_visible: { fields: [is_visible] }
          sc_slug: { fields: [slug], type: unique }
      

      How can You see, "actAs" section have the I18n behavior, which, in turn, have Searchable behavior.

      I experimented with MySQL and now finished my experiments with PostgreSQL.

      Running "doctrine:build --all" with MySQL - there are no errors, SQLs are correct.

      When I`m switching to the PostgreSQL and run the same command, I got a error:

      SQLSTATE[42830]: Invalid foreign key: 7 ERROR: there is no unique constraint matching given keys for referenced table "post_translation". Failing Query: "ALTER TABLE post_translation_index ADD CONSTRAINT post_translation_index_id_post_translation_id FOREIGN KEY (id) REFERENCES post_translation(id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE". Failing Query: ALTER TABLE post_translation_index ADD CONSTRAINT post_translation_index_id_post_translation_id FOREIGN KEY (id) REFERENCES post_translation(id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE

      Here is Doctrine automatically generated SQLs (MySQL and PostgreSQL)



      (adapter MySQL) - schema.sql
      CREATE TABLE post_translation_index (id BIGINT UNSIGNED, lang CHAR(2), keyword VARCHAR(200), field VARCHAR(50), position BIGINT, PRIMARY KEY(id, lang, keyword, field, position)) ENGINE = INNODB;
      CREATE TABLE post_translation (id BIGINT UNSIGNED, title VARCHAR(255), blurb LONGTEXT, lang CHAR(2), PRIMARY KEY(id, lang)) ENGINE = INNODB;
      CREATE TABLE post (id BIGINT UNSIGNED AUTO_INCREMENT, slug VARCHAR(127) NOT NULL, is_visible TINYINT(1), created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX sc_is_visible_idx (is_visible), UNIQUE INDEX sc_slug_idx (slug), PRIMARY KEY(id)) ENGINE = INNODB;
      ALTER TABLE post_translation_index ADD CONSTRAINT post_translation_index_id_post_translation_id FOREIGN KEY (id) REFERENCES post_translation(id) ON UPDATE CASCADE ON DELETE CASCADE;
      ALTER TABLE post_translation ADD CONSTRAINT post_translation_id_post_id FOREIGN KEY (id) REFERENCES post(id) ON UPDATE CASCADE ON DELETE CASCADE;
      
      (adapter PostgreSQL) - schema.sql
      CREATE TABLE post_translation_index (id BIGINT, lang CHAR(2), keyword VARCHAR(200), field VARCHAR(50), position BIGINT, PRIMARY KEY(id, lang, keyword, field, position));
      CREATE TABLE post_translation (id BIGINT, title VARCHAR(255), blurb TEXT, lang CHAR(2), PRIMARY KEY(id, lang));
      CREATE TABLE post (id BIGSERIAL, slug VARCHAR(127) NOT NULL, is_visible BOOLEAN, created_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL, PRIMARY KEY(id));
      CREATE UNIQUE INDEX sc_slug ON post (slug);
      CREATE INDEX sc_is_visible ON post (is_visible);
      ALTER TABLE post_translation_index ADD CONSTRAINT post_translation_index_id_post_translation_id FOREIGN KEY (id) REFERENCES post_translation(id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;
      ALTER TABLE post_translation ADD CONSTRAINT post_translation_id_post_id FOREIGN KEY (id) REFERENCES post(id) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;
      

        Activity

        Hide
        Jonathan H. Wage added a comment -

        Sorry this is a known issue with nesting behaviors and it just doesn't work properly and can't be fixed without being re-implemented and completing breaking BC. We don't have another 1.x release to properly address the issue in.

        Show
        Jonathan H. Wage added a comment - Sorry this is a known issue with nesting behaviors and it just doesn't work properly and can't be fixed without being re-implemented and completing breaking BC. We don't have another 1.x release to properly address the issue in.

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Ilya Sabelnikov
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: