DdlUtils
  1. DdlUtils
  2. DDLUTILS-212

Support for indexes that use functions

    Details

    • Type: New Feature New Feature
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.0
    • Fix Version/s: None
    • Component/s: Core - PostgreSql
    • Labels:
      None
    • Environment:
      RHEL 4 Linux

      Description

      I created a postgres database and ran this SQL:

      /create LANGUAGE plpgsql;/
      CREATE OR REPLACE FUNCTION FUNC (/id/ INTEGER, /status/ INTEGER)
      RETURNS NUMERIC AS'
      BEGIN
      RETURN 0;
      END;

      'LANGUAGE plpgsql IMMUTABLE;

      create table test_list
      (
      test_list_id integer not null, --pk
      test_id integer not null, --fk
      status_id integer not null --fk
      );

      create unique index uq_test_list on test_list(test_list_id, test_id, func(test_list_id, status_id));

      When trying to load the database schema in Jasper Server it fails with this exception:

      Caused by: org.apache.ddlutils.model.ModelException: The index uq_test_list in table test_list references the undefined column func(test_list_id, status_id)
      at org.apache.ddlutils.model.Database.initialize(Database.java:393)
      at org.apache.ddlutils.platform.JdbcModelReader.getDatabase(JdbcModelReader.java:484)
      at org.apache.ddlutils.platform.PlatformImplBase.readModelFromDatabase(PlatformImplBase.java:1920)
      at com.jaspersoft.commons.semantic.metaapi.impl.jdbc.BaseJdbcMetaDataFactoryImpl.getMetaData(BaseJdbcMetaDataFactoryImpl.java:82)

      I applied the fix mentioned here:
      https://issues.apache.org/jira/browse/DDLUTILS-192

      Still it fails with the same exception.

        Activity

        Hide
        Thomas Dudziak added a comment -

        DdlUtils currently does not support reading/writing indexes that use anything other than simple columns, such as in your case, functions.

        Show
        Thomas Dudziak added a comment - DdlUtils currently does not support reading/writing indexes that use anything other than simple columns, such as in your case, functions.
        Hide
        prasanna added a comment -

        Thanks Thomas for the quick response.

        Is there any workaround for this issue?

        We use functions in defining indexes through out our schema so changing the schema is difficult.

        Do you have any suggestions on how this can be fixed in Ddlutil code?

        Show
        prasanna added a comment - Thanks Thomas for the quick response. Is there any workaround for this issue? We use functions in defining indexes through out our schema so changing the schema is difficult. Do you have any suggestions on how this can be fixed in Ddlutil code?
        Hide
        Thomas Dudziak added a comment -

        Your particular problem requires DdlUtils to understand functions and to be able to read and recreate them across all databases that support them, which is not a simple thing to implement.
        One workaround would be to give DdlUtils the ability to execute arbitrary SQL at a given step in the creation or alteration of a database (DDLUTILS-213).
        For now, unfortunately you'll have to create the indexes manually (e.g. use the sql task in Ant) as DdlUtils has no way of doing so.

        Show
        Thomas Dudziak added a comment - Your particular problem requires DdlUtils to understand functions and to be able to read and recreate them across all databases that support them, which is not a simple thing to implement. One workaround would be to give DdlUtils the ability to execute arbitrary SQL at a given step in the creation or alteration of a database ( DDLUTILS-213 ). For now, unfortunately you'll have to create the indexes manually (e.g. use the sql task in Ant) as DdlUtils has no way of doing so.
        Hide
        Ilja Pavkovic added a comment -

        this problem also occurs on oracle 10 with a slightly different error message:

        "The index IDX_CNT_RGHT in table CONTAINER references the undefined column SYS_NC00015$"

        the index was created with:

        CREATE INDEX IDX_CNT_RGHT ON CONTAINER (SUBSTR("CONTAINER_RIGHTS",9,1))

        Show
        Ilja Pavkovic added a comment - this problem also occurs on oracle 10 with a slightly different error message: "The index IDX_CNT_RGHT in table CONTAINER references the undefined column SYS_NC00015$" the index was created with: CREATE INDEX IDX_CNT_RGHT ON CONTAINER (SUBSTR("CONTAINER_RIGHTS",9,1))
        Hide
        teo sarca added a comment -

        Hi,

        Yes, i agree that is complicated to recreate an function based index but i think that, until this feature is not supported, the DDLUTILS should not export function based indexes when we export schema.
        What do you think ?

        Best regards,
        Teo Sarca

        Show
        teo sarca added a comment - Hi, Yes, i agree that is complicated to recreate an function based index but i think that, until this feature is not supported, the DDLUTILS should not export function based indexes when we export schema. What do you think ? Best regards, Teo Sarca
        Hide
        Tomislav Nakic-Alfirevic added a comment -

        I have the same problem because is PostgreSQL, you can create an index on e.g. "tableoid" which is a "hidden" column so it doesn't show up in select * from... and ddlutils doesn't pick up on it. It does, however, prevent me from completeing the ant export task. For me, commenting out one line below would be a much better solution than halting the whole export:

        Index: src/main/java/org/apache/ddlutils/model/Database.java
        ===================================================================
        — src/main/java/org/apache/ddlutils/model/Database.java (revision 753277)
        +++ src/main/java/org/apache/ddlutils/model/Database.java (working copy)
        @@ -451,7 +451,7 @@

        if (column == null)

        { - throw new ModelException("The index "+indexDesc+" in table "+curTable.getName()+" references the undefined column "+indexColumn.getName()); + //throw new ModelException("The index "+indexDesc+" in table "+curTable.getName()+" references the undefined column "+indexColumn.getName()); }

        else
        {

        Show
        Tomislav Nakic-Alfirevic added a comment - I have the same problem because is PostgreSQL, you can create an index on e.g. "tableoid" which is a "hidden" column so it doesn't show up in select * from... and ddlutils doesn't pick up on it. It does, however, prevent me from completeing the ant export task. For me, commenting out one line below would be a much better solution than halting the whole export: Index: src/main/java/org/apache/ddlutils/model/Database.java =================================================================== — src/main/java/org/apache/ddlutils/model/Database.java (revision 753277) +++ src/main/java/org/apache/ddlutils/model/Database.java (working copy) @@ -451,7 +451,7 @@ if (column == null) { - throw new ModelException("The index "+indexDesc+" in table "+curTable.getName()+" references the undefined column "+indexColumn.getName()); + //throw new ModelException("The index "+indexDesc+" in table "+curTable.getName()+" references the undefined column "+indexColumn.getName()); } else {
        Hide
        Tomislav Nakic-Alfirevic added a comment -

        I ran into this issue as well.
        In my use cases, it would be much better if the export proceeded ignoring the functional index than break the export altogether, i.e. all that would have to be done is to comment out one line of code, Database.java:451 or provide some kind of parameter "ignoreMissingIndexColumns" to allow users to choose.

        Show
        Tomislav Nakic-Alfirevic added a comment - I ran into this issue as well. In my use cases, it would be much better if the export proceeded ignoring the functional index than break the export altogether, i.e. all that would have to be done is to comment out one line of code, Database.java:451 or provide some kind of parameter "ignoreMissingIndexColumns" to allow users to choose.

          People

          • Assignee:
            Thomas Dudziak
            Reporter:
            prasanna
          • Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:

              Development