DdlUtils
  1. DdlUtils
  2. DDLUTILS-268

Detect foreign keys on MySQL MyISAM showing up as indexes

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Core - MySql
    • Labels:
      None

      Description

      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:

      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));
                              }
                          }
                      }
                  }
              }
          }
      

      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:

      <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>
      
      1. DDLUTILS-268.diff
        5 kB
        Colin Ritchie

        Activity

        Eirik Bjorsnos created issue -
        Colin Ritchie made changes -
        Field Original Value New Value
        Attachment DDLUTILS-268.diff [ 12475291 ]

          People

          • Assignee:
            Thomas Dudziak
            Reporter:
            Eirik Bjorsnos
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development