[jira] Created: (DDLUTILS-212) org.apache.ddlutils.model.ModelException: The index in table references the undefined column

org.apache.ddlutils.model.ModelException: The index in table references the undefined column
--------------------------------------------------------------------------------------------

                 Key: DDLUTILS-212
                 URL: https://issues.apache.org/jira/browse/DDLUTILS-212
             Project: DdlUtils
          Issue Type: Bug
          Components: Core - PostgreSql
    Affects Versions: 1.0
         Environment: RHEL 4 Linux
            Reporter: prasanna
            Assignee: Thomas Dudziak


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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DDLUTILS-212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12607476#action_12607476 ]

Thomas Dudziak commented on DDLUTILS-212:
-----------------------------------------

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

> org.apache.ddlutils.model.ModelException: The index in table references the undefined column
> --------------------------------------------------------------------------------------------
>
>                 Key: DDLUTILS-212
>                 URL: https://issues.apache.org/jira/browse/DDLUTILS-212
>             Project: DdlUtils
>          Issue Type: Bug
>          Components: Core - PostgreSql
>    Affects Versions: 1.0
>         Environment: RHEL 4 Linux
>            Reporter: prasanna
>            Assignee: Thomas Dudziak
>
> 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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DDLUTILS-212?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Thomas Dudziak updated DDLUTILS-212:
------------------------------------

    Issue Type: New Feature  (was: Bug)
       Summary: Support for indexes that use functions  (was: org.apache.ddlutils.model.ModelException: The index in table references the undefined column)

> Support for indexes that use functions
> --------------------------------------
>
>                 Key: DDLUTILS-212
>                 URL: https://issues.apache.org/jira/browse/DDLUTILS-212
>             Project: DdlUtils
>          Issue Type: New Feature
>          Components: Core - PostgreSql
>    Affects Versions: 1.0
>         Environment: RHEL 4 Linux
>            Reporter: prasanna
>            Assignee: Thomas Dudziak
>
> 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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DDLUTILS-212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12607493#action_12607493 ]

prasanna commented on DDLUTILS-212:
-----------------------------------

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?

> Support for indexes that use functions
> --------------------------------------
>
>                 Key: DDLUTILS-212
>                 URL: https://issues.apache.org/jira/browse/DDLUTILS-212
>             Project: DdlUtils
>          Issue Type: New Feature
>          Components: Core - PostgreSql
>    Affects Versions: 1.0
>         Environment: RHEL 4 Linux
>            Reporter: prasanna
>            Assignee: Thomas Dudziak
>
> 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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DDLUTILS-212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12608253#action_12608253 ]

Thomas Dudziak commented on DDLUTILS-212:
-----------------------------------------

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.

> Support for indexes that use functions
> --------------------------------------
>
>                 Key: DDLUTILS-212
>                 URL: https://issues.apache.org/jira/browse/DDLUTILS-212
>             Project: DdlUtils
>          Issue Type: New Feature
>          Components: Core - PostgreSql
>    Affects Versions: 1.0
>         Environment: RHEL 4 Linux
>            Reporter: prasanna
>            Assignee: Thomas Dudziak
>
> 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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.