RE: Defect ID: TRQS278 - mysql innodb tables and order of index creation from schema

[Thomas Fischer <fi...@seitenbau.net>]

Hi Panagiotis,

I have added your files to the scarab issue TRQS278. I did a quick shot at
the velocity templates for the table generation, but my current velocity
knowledge is not enough to resolve this issue. I believe your particular
issue could be resolved if the following would work (though I would not
consider it a very clean solution)
table.vm first parses foreignkey.vm then index.vm

old foreignkey.vm:

#foreach ($fk in $table.ForeignKeys)
    FOREIGN KEY ($fk.LocalColumnNames) REFERENCES $fk.ForeignTableName
($fk.ForeignColumnNames)
  #if ($fk.hasOnUpdate())
    ON UPDATE $fk.OnUpdate
  #end
  #if ($fk.hasOnDelete())
    ON DELETE $fk.OnDelete
  #end
,
#end

old index.vm:

#foreach ($index in $table.Indices)
    INDEX $index.Name ($index.ColumnList),
#end


The issue could be resolved if the following would work

foreignkey.vm

#foreach ($fk in $table.ForeignKeys)
    FOREIGN KEY ($fk.LocalColumnNames) REFERENCES $fk.ForeignTableName
($fk.ForeignColumnNames)
  #if ($fk.hasOnUpdate())
    ON UPDATE $fk.OnUpdate
  #end
  #if ($fk.hasOnDelete())
    ON DELETE $fk.OnDelete
  #end
,
  #if ($table.Indices.contains($fk.LocalColumnNames)) {
    INDEX $index.Name ($index.ColumnList),
  #end
#end

index.vm:

#foreach ($index in $table.Indices)
    #if (!$table.ForeignKeys.contains($index.ColumnList))
        INDEX $index.Name ($index.ColumnList),
    #end
#end

I am sending this also to the dev list, maybe some velocity guru knows a
solution there.

   Thomas

Panagiotis Spiliotis <p....@vierex.com> schrieb am 16.02.2005
22:16:27:

> Hi Thomas,
> The order of the foreign keys and the indexes is an issue only when
> creating InnoDB tables.
> As you can see in the xml I changed the order to the desired effect
> but the creation of the sql code did not follow the order the in the
> xml and this is basically the problem. I will look into the matter
> of circular references. Regarding the act of issuing ALTER commands
> after the creation of the tables: my attempts to create the tables
> were unsuccessfull unless I issued the commands exactly as I wrote
> it in the changed sql files. MySQL requires the INDEX and FOREIGN
> KEY declarations to be in the correct order and in the same command
> in order to create the table of type InnoDB.
>
> As for appending the files to the scarab issue. I don't mind at
all.Please do.
>
> Panagiotis
>
> Thomas Fischer wrote:
>
>
>
> Hi,
>
> I believe creation of foreign key references is still an issue. But I do
> not believe that changing the order of table creation would help in all
> circumstances suchas  circular references. It would be better to first
> create the tables and then issue an alter table to create the foreign
keys.
> I am not sure, but perhaps you can change the order in your xml to get
the
> correct creation order in your case (unfortunately, the correct drop
order
> is the other way round...)
>
> Do you mind if I append your files to the scarab issue so other people
can
> see them, too ?
>
>    Thomas
>
> Panagiotis Spiliotis <p....@vierex.com> schrieb am 14.02.2005
> 13:39:20:
>
>
> First of all I would like to thank you for your interest.
>
> The attached "bookstore-schema.xml" contains the schema of the example
> given in the torque turorial.
> As you can see I have added two <index> elements just before stating the
> foreign keys.
> In addition, I have changed the order the tables are declared as this is
> also an issue when creating innodb tables.
> To be more specific the tables that are referenced by means of foreign
> keys should be created before those containing references to fields in
> the aforementioned tables.
>
> After running "maven torque" the generated sql is in the file
> bookstore-schema.sql. As you can see the order in which the tables are
> created is correct but at the point where the "book" table is created,
> the INDEX declarations are not before each FOREIGN KEY declarations as
> they should. Thus mysql produces "errno:150" unless I manually change
> the code to the desired form indicated in the attached file
> "bookstore-schema-changed.sql".
>
> Thank you in advance for your time and interest.
> Panagiotis Spiliotis
>  [...]


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org