You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-dev@db.apache.org by "Eirik Bjorsnos (JIRA)" <ji...@apache.org> on 2011/01/13 14:16:45 UTC
[jira] Created: (DDLUTILS-268) Detect foreign keys on MySQL MyISAM
showing up as indexes
Detect foreign keys on MySQL MyISAM showing up as indexes
----------------------------------------------------------
Key: DDLUTILS-268
URL: https://issues.apache.org/jira/browse/DDLUTILS-268
Project: DdlUtils
Issue Type: Improvement
Components: Core - MySql
Reporter: Eirik Bjorsnos
Assignee: Thomas Dudziak
When reading a MyISAM MySQL database model from the database, DdlUtils will read in Index instead of a ForeignKey.
As a result of this, DdlUtils suggests to remove the index and create the foreign key. After applying this change, DdlUtils will still not see the ForeignKey and thus suggests the same change all over again.
We work around this by looking for all indexes in a table. If we find an equally named foreign key in our "wanted" model, we remove the index and add a foreign key:
{code}
public void transform(Database database, Database wanted, Platform platform) {
if (platform instanceof MySqlPlatform) {
for (Table table : database.getTables()) {
for (Index index : table.getIndices()) {
Table wantedTable = wanted.findTable(table.getName());
if (wantedTable != null) {
ForeignKey key = findCandidateForeignKey(platform.getSqlBuilder(), index, wantedTable);
if (key != null) {
table.removeIndex(index);
table.addForeignKey(new CloneHelper().clone(key, table, database, true));
}
}
}
}
}
}
{code}
After this transformation, DdlUtils will not suggest removing the index and adding the foreign key.
If this something that could be added to DdlUtils proper in some way?
Here's the DB I used for testing this:
{code}
<database name="ddlutils-db" xmlns="http://db.apache.org/ddlutils/schema/1.1">
<table name="orderTable">
<column name="orderId" primaryKey="true" required="true" type="INTEGER" size="10" autoIncrement="true"/>
<column name="CustomerName" required="false" type="VARCHAR" size="64"/>
</table>
<table name="orderLine">
<column name="orderLineId" primaryKey="true" required="true" type="INTEGER" size="10" autoIncrement="true"/>
<column name="orderId" required="true" type="INTEGER" size="10"/>
<foreign-key foreignTable="orderTable">
<reference local="orderId" foreign="orderId"/>
</foreign-key>
</table>
</database>
{code}
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] [Updated] (DDLUTILS-268) Detect foreign keys on MySQL MyISAM
showing up as indexes
Posted by "Colin Ritchie (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DDLUTILS-268?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Colin Ritchie updated DDLUTILS-268:
-----------------------------------
Attachment: DDLUTILS-268.diff
Here is a fix for DDLUTILS-268. What I have done is:
Since MyISAM databases store a FK as an Index, the MySQL Modeler method findCorrespondingIndex() will look for a corresponding ForeignKey with the same name, and findCorrespondingForeignKey() will look for a corresponding Index with the same name.
> Detect foreign keys on MySQL MyISAM showing up as indexes
> ----------------------------------------------------------
>
> Key: DDLUTILS-268
> URL: https://issues.apache.org/jira/browse/DDLUTILS-268
> Project: DdlUtils
> Issue Type: Improvement
> Components: Core - MySql
> Reporter: Eirik Bjorsnos
> Assignee: Thomas Dudziak
> Attachments: DDLUTILS-268.diff
>
>
> When reading a MyISAM MySQL database model from the database, DdlUtils will read in Index instead of a ForeignKey.
> As a result of this, DdlUtils suggests to remove the index and create the foreign key. After applying this change, DdlUtils will still not see the ForeignKey and thus suggests the same change all over again.
> We work around this by looking for all indexes in a table. If we find an equally named foreign key in our "wanted" model, we remove the index and add a foreign key:
> {code}
> public void transform(Database database, Database wanted, Platform platform) {
> if (platform instanceof MySqlPlatform) {
> for (Table table : database.getTables()) {
> for (Index index : table.getIndices()) {
> Table wantedTable = wanted.findTable(table.getName());
> if (wantedTable != null) {
> ForeignKey key = findCandidateForeignKey(platform.getSqlBuilder(), index, wantedTable);
> if (key != null) {
> table.removeIndex(index);
> table.addForeignKey(new CloneHelper().clone(key, table, database, true));
> }
> }
> }
> }
> }
> }
> {code}
> After this transformation, DdlUtils will not suggest removing the index and adding the foreign key.
> If this something that could be added to DdlUtils proper in some way?
> Here's the DB I used for testing this:
> {code}
> <database name="ddlutils-db" xmlns="http://db.apache.org/ddlutils/schema/1.1">
> <table name="orderTable">
> <column name="orderId" primaryKey="true" required="true" type="INTEGER" size="10" autoIncrement="true"/>
> <column name="CustomerName" required="false" type="VARCHAR" size="64"/>
> </table>
> <table name="orderLine">
> <column name="orderLineId" primaryKey="true" required="true" type="INTEGER" size="10" autoIncrement="true"/>
> <column name="orderId" required="true" type="INTEGER" size="10"/>
> <foreign-key foreignTable="orderTable">
> <reference local="orderId" foreign="orderId"/>
> </foreign-key>
> </table>
> </database>
> {code}
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira