Details
-
Type:
Bug
-
Status:
Resolved
-
Priority:
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.
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)
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;
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;
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.