regression index generation SQL

[jaugustin]

Hi,

@marcj your commit a3cb1b6#generator/lib/model/Table.php

introduce a regression with index.

With this schema

<table name="notification_type">
    <column name="module_unique_name" primaryKey="true" required="true" size="255" type="VARCHAR" />
    <column name="unique_name" primaryKey="true" required="true" size="255" type="VARCHAR" />
    <column name="is_correction" defaultValue="0" required="true" type="BOOLEAN" />
    <column name="disabled_engine" size="255" type="VARCHAR" />
    <foreign-key foreignTable="module" name="FK_TYPENOTIFICATION_MODULE0" onDelete="CASCADE" onUpdate="CASCADE">
      <reference foreign="unique_name" local="module_unique_name" />
    </foreign-key>
    <index name="FK_TYPENOTIFICATION_MODULE">
      <index-column name="module_unique_name" />
    </index>
  </table>

Before

CREATE TABLE `notification_type`
(
    `module_unique_name` VARCHAR(255) NOT NULL,
    `unique_name` VARCHAR(255) NOT NULL,
    `is_correction` TINYINT(1) DEFAULT 0 NOT NULL,
    `disabled_engine` VARCHAR(255),
    PRIMARY KEY (`module_unique_name`,`unique_name`),
    INDEX `FK_TYPENOTIFICATION_MODULE` (`module_unique_name`),
    INDEX `I_referenced_FK_NOTIFICATION_TYPENOTIFICATION0_1` (`unique_name`),
    CONSTRAINT `FK_TYPENOTIFICATION_MODULE0`
        FOREIGN KEY (`module_unique_name`)
        REFERENCES `module` (`unique_name`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB;

After

CREATE TABLE `notification_type`
(
    `module_unique_name` VARCHAR(255) NOT NULL,
    `unique_name` VARCHAR(255) NOT NULL,
    `is_correction` TINYINT(1) DEFAULT 0 NOT NULL,
    `disabled_engine` VARCHAR(255),
    PRIMARY KEY (`module_unique_name`,`unique_name`),
    INDEX `FK_TYPENOTIFICATION_MODULE` (`module_unique_name`),
    CONSTRAINT `FK_TYPENOTIFICATION_MODULE0`
        FOREIGN KEY (`module_unique_name`)
        REFERENCES `module` (`unique_name`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB;

This prevent the sql from being executed :

SQLSTATE[HY000]: General error: 1005 Can't create table 'notification_type' (errno: 150)

The index INDEX I_referenced_FK_NOTIFICATION_TYPENOTIFICATION0_1 (unique_name), is not generated anymore.

[marcj]

I've tried the 1.6.7 tag (where my commit isn't) and there is no I_referenced_* index as well. I've also reverted my commit in the current master branch and still no referenced index. Thus I think that bug comes from somewhere else.

My test gist: https://gist.github.com/marcj/5350549 - output ist based on the 1.6.7 tag.

In which tree-ish does your schema work?

[jaugustin]

@marcj sorry this is only a part of the schema ;) I think the index are created by related FK

It work on 1.6.7 and not on 1.6.9-dev and the only think that change in the index handling is your commit ;)

a more complete schema

<table name="notification">
    <column autoIncrement="true" name="id" primaryKey="true" required="true" type="INTEGER" />
    <column name="target_user_id" required="true" type="INTEGER" />
    <column name="notification_type_unique_name" required="true" size="255" type="VARCHAR" />
    <column name="group_id" type="INTEGER" />
    <column name="date" required="true" type="TIMESTAMP" />
    <column name="objects" type="LONGVARCHAR" />
    <column name="is_new" defaultValue="1" required="true" type="BOOLEAN" />
    <foreign-key foreignTable="user" name="FK_NOTIFICATION_TARGET_USER0" onDelete="CASCADE" onUpdate="CASCADE">
      <reference foreign="id" local="target_user_id" />
    </foreign-key>
    <foreign-key foreignTable="notification_type" name="FK_NOTIFICATION_TYPENOTIFICATION0">
      <reference foreign="unique_name" local="notification_type_unique_name" />
    </foreign-key>
    <index name="FK_NOTIFICATION_TARGET_USER">
      <index-column name="target_user_id" />
    </index>
    <index name="FK_NOTIFICATION_TYPENOTIFICATION">
      <index-column name="notification_type_unique_name" />
    </index>
  </table>
<table name="notification_type">
    <column name="module_unique_name" primaryKey="true" required="true" size="255" type="VARCHAR" />
    <column name="unique_name" primaryKey="true" required="true" size="255" type="VARCHAR" />
    <column name="is_correction" defaultValue="0" required="true" type="BOOLEAN" />
    <column name="disabled_engine" size="255" type="VARCHAR" />
    <foreign-key foreignTable="module" name="FK_TYPENOTIFICATION_MODULE0" onDelete="CASCADE" onUpdate="CASCADE">
      <reference foreign="unique_name" local="module_unique_name" />
    </foreign-key>
    <index name="FK_TYPENOTIFICATION_MODULE">
      <index-column name="module_unique_name" />
    </index>
  </table>
<table name="module">
    <column autoIncrement="true" name="id" primaryKey="true" required="true" type="INTEGER" />
    <column name="unique_name" required="true" size="255" type="VARCHAR" />
    <column name="label" primaryString="true" required="true" size="255" type="VARCHAR" />
    <column name="description" required="true" size="255" type="VARCHAR" />
    <unique name="UNIQUE_NAME_IS_UNIQUE">
      <unique-column name="unique_name" />
    </unique>
  </table>

If you can't reproduce the issue, I will work on a test case ;)

[marcj]

Was a really stupid copy&paste bug. :/