OpenJPA
  1. OpenJPA
  2. OPENJPA-605

Informix will throw an exception when a unique index is explictily created on the primary key columns

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.0.4, 1.2.0
    • Component/s: None
    • Labels:
      None

      Description

      When an entity is defined as following (unique index annotation on the primary key field. In this situation):

      @Entity
      @Table(name="AddressBean")
      public class AddressBeanAno implements IAddressBean, Serializable {
      @EmbeddedId
      @Index(name="street_index",unique=true)
      @Column(length=30)
      private AddressPK street;

      Informix will issue an error:

      Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: Index already exists on column.

      {stmnt 1440372186 CREATE UNIQUE INDEX street_index ON AddressBean (street)}

      [code=-350, state=S0011]
      at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:192)
      at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$3(LoggingConnectionDecorator.java:189)
      at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingStatement.executeUpdate(LoggingConnectionDecorator.java:762)
      at org.apache.openjpa.lib.jdbc.DelegatingStatement.executeUpdate(DelegatingStatement.java:114)
      at org.apache.openjpa.jdbc.schema.SchemaTool.executeSQL(SchemaTool.java:1204)
      at org.apache.openjpa.jdbc.schema.SchemaTool.createIndex(SchemaTool.java:1019)
      at org.apache.openjpa.jdbc.schema.SchemaTool.add(SchemaTool.java:554)
      at org.apache.openjpa.jdbc.schema.SchemaTool.add(SchemaTool.java:344)
      at org.apache.openjpa.jdbc.schema.SchemaTool.run(SchemaTool.java:321)
      at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:501)

      Per Informaix SQL syntax guide (Create Index section):
      You can not add a unique index to a column or column list that a primary -key constraint on it. The reason is that defining the column or column list as the primary key causes the database server to create a unique internal index on the column or column list. So you cannot create another unique index on this column or column list with CREATE INDEX statement.

      1. openjpa.patch
        1 kB
        Fay Wang
      2. openjpa.patch
        1 kB
        Fay Wang
      3. openjpa.patch
        1.0 kB
        Fay Wang
      4. openjpa.patch
        3 kB
        Fay Wang

        Issue Links

          Activity

          Fay Wang created issue -
          Hide
          Fay Wang added a comment -

          The attached patch fixes this problem.

          Show
          Fay Wang added a comment - The attached patch fixes this problem.
          Fay Wang made changes -
          Field Original Value New Value
          Attachment openjpa.patch [ 12382407 ]
          Hide
          Fay Wang added a comment -

          The patch fixes the NPE introduced from the earlier one.

          Show
          Fay Wang added a comment - The patch fixes the NPE introduced from the earlier one.
          Fay Wang made changes -
          Attachment openjpa.patch [ 12382409 ]
          Hide
          Albert Lee added a comment -

          Fay,

          Can the proposed solution encapsulate in the dictionary? I.e.

          public boolean createIndex(Index idx, Table table)
          throws SQLException {
          if ( !_dict.needsToCreateIndex(idx,table))

          { return true; }

          .......

          public class InformixDictionary .... {
          public boolean needsToCreateIndex(Index idx, Table table) {
          Column[] cols = idx.getColumns();
          Column[] pkCols = table.getPrimaryKey().getColumns();
          if (cols.length == pkCols.length)

          { String[] colNames = new String[cols.length]; String[] pkColNames = new String[cols.length]; for (int i = 0; i < cols.length; i++) colNames[i] = cols[i].getName(); for (int i = 0; i < pkCols.length; i++) pkColNames[i] = pkCols[i].getName(); if (java.util.Arrays.equals(colNames, pkColNames)) return false; }

          return true;
          }
          }

          public class DBDictionary ....{
          public boolean needsToCreateIndex(Index idx, Table table)

          { return true; }

          }

          Thanks,
          Albert Lee.

          Show
          Albert Lee added a comment - Fay, Can the proposed solution encapsulate in the dictionary? I.e. public boolean createIndex(Index idx, Table table) throws SQLException { if ( !_dict.needsToCreateIndex(idx,table)) { return true; } ....... public class InformixDictionary .... { public boolean needsToCreateIndex(Index idx, Table table) { Column[] cols = idx.getColumns(); Column[] pkCols = table.getPrimaryKey().getColumns(); if (cols.length == pkCols.length) { String[] colNames = new String[cols.length]; String[] pkColNames = new String[cols.length]; for (int i = 0; i < cols.length; i++) colNames[i] = cols[i].getName(); for (int i = 0; i < pkCols.length; i++) pkColNames[i] = pkCols[i].getName(); if (java.util.Arrays.equals(colNames, pkColNames)) return false; } return true; } } public class DBDictionary ....{ public boolean needsToCreateIndex(Index idx, Table table) { return true; } } Thanks, Albert Lee.
          Hide
          Pinaki Poddar added a comment -

          Agree with Albert's suggestion.
          An extra method may not be required though.

          The method implementation in InformixDBDictionary could be:
          public String[] getCreateIndexSQL(Index index)

          { return (isIndexOnThe PrimaryKey(index)) ? new String[0] ? super.getCreateIndexSQL(index); }

          where isIndexOnThePrimaryKey(index) can encapsulate the logic presented in the patch.

          Show
          Pinaki Poddar added a comment - Agree with Albert's suggestion. An extra method may not be required though. The method implementation in InformixDBDictionary could be: public String[] getCreateIndexSQL(Index index) { return (isIndexOnThe PrimaryKey(index)) ? new String[0] ? super.getCreateIndexSQL(index); } where isIndexOnThePrimaryKey(index) can encapsulate the logic presented in the patch.
          Hide
          Pinaki Poddar added a comment -

          The method
          public boolean columnsMatch(Column[] ocols)

          in LocalConstraint.java may be useful for the logic expressed in the patch.

          Show
          Pinaki Poddar added a comment - The method public boolean columnsMatch(Column[] ocols) in LocalConstraint.java may be useful for the logic expressed in the patch.
          Hide
          Fay Wang added a comment -

          This patch is per Pinaki's comments.

          Show
          Fay Wang added a comment - This patch is per Pinaki's comments.
          Fay Wang made changes -
          Attachment openjpa.patch [ 12382429 ]
          Hide
          Fay Wang added a comment -

          Sorry, I did not notice Albert's comments. This patch is based on his comments.

          Show
          Fay Wang added a comment - Sorry, I did not notice Albert's comments. This patch is based on his comments.
          Fay Wang made changes -
          Attachment openjpa.patch [ 12382433 ]
          Michael Dick made changes -
          Assignee Michael Dick [ mikedd ]
          Michael Dick made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Fix Version/s 1.2.0 [ 12313102 ]
          Michael Dick made changes -
          Assignee Michael Dick [ mikedd ] Catalina Wei [ fancy ]
          Michael Dick made changes -
          Link This issue is part of OPENJPA-1243 [ OPENJPA-1243 ]
          Michael Dick made changes -
          Fix Version/s 1.0.4 [ 12313301 ]
          Donald Woods made changes -
          Status Resolved [ 5 ] Closed [ 6 ]

            People

            • Assignee:
              Catalina Wei
              Reporter:
              Fay Wang
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development