DatabaseMetaData.getIndexInfo() returns internal names

[Jorg Janke <jj...@compiere.org>]

Problem: (Version: 10_1_3_1)
----------------------------
We inquire the meta data of the database and then dynamically update the
database to its target date (e.g. add/modify tables, columns, indexes,
constraints, ...) via (standard) DDL.
When requesting the indexes for a table, we get the internal name, not
the index name.

When (re-) the submitting the DDL
ALTER TABLE AD_ACCESSLOG ADD CONSTRAINT AD_ACCESSLOG_KEY PRIMARY KEY
(AD_ACCESSLOG_ID)

I get the error message
Constraints 'AD_ACCESSLOG_KEY' and 'AD_ACCESSLOG_KEY' have the same set
of columns, which is not allowed.


Technical Description
---------------------
Problem is that the Derby implementation of
	DatabaseMetaData.getIndexInfo()
returns the internal (conglomerate) name rather then the "real" name of
the index.
I checked - in
org.apache.derby.client.am.DatabaseMetaData.getIndexInfoX(..) you call
the function SYSIBM.SQLSTATISTICS(?,?,?,?,?,?) - which returns the wrong
data.

Results from getIndexInfo(..)
  0: TABLE_CAT=, TABLE_SCHEM=COMPIERE, TABLE_NAME=AD_ACCESSLOG,
NON_UNIQUE=0, INDEX_QUALIFIER=, INDEX_NAME=SQL060709062929330, TYPE=3,
ORDINAL_POSITION=1, COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A,
CARDINALITY=null, PAGES=null, FILTER_CONDITION=null
  1: TABLE_CAT=, TABLE_SCHEM=COMPIERE, TABLE_NAME=AD_ACCESSLOG,
NON_UNIQUE=1, INDEX_QUALIFIER=, INDEX_NAME=SQL060716064852400, TYPE=3,
ORDINAL_POSITION=1, COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A,
CARDINALITY=null, PAGES=null, FILTER_CONDITION=null

Results from getImportedKeys(..)
  0: PKTABLE_CAT=, PKTABLE_SCHEM=COMPIERE, PKTABLE_NAME=AD_COLUMN,
PKCOLUMN_NAME=AD_COLUMN_ID, FKTABLE_CAT=, FKTABLE_SCHEM=COMPIERE,
FKTABLE_NAME=AD_ACCESSLOG, FKCOLUMN_NAME=AD_COLUMN_ID, KEY_SEQ=1,
UPDATE_RULE=3, DELETE_RULE=0, FK_NAME=ADCOLUMN_ADACCESSLOG,
PK_NAME=AD_COLUMN_KEY, DEFERRABILITY=7

The problem would be solved, if in addition to the (internal type 3)
index info you would provide the index type 1/2 info with the resuly of

  0: .. INDEX_NAME=AD_ACCESSLOG_KEY, TYPE=1, ORDINAL_POSITION=1,
COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A, CARDINALITY=null,
PAGES=null, FILTER_CONDITION=null
  1: .. INDEX_NAME=ADCOLUMN_ADACCESSLOG, TYPE=3, ORDINAL_POSITION=1,
COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null,
FILTER_CONDITION=null

The original table definition is:

CREATE TABLE AD_ACCESSLOG
(
    AD_ACCESSLOG_ID DECIMAL(10,0)      NOT NULL,
    AD_CLIENT_ID    DECIMAL(10,0)      NOT NULL,
    AD_ORG_ID       DECIMAL(10,0)      NOT NULL,
    ISACTIVE        CHAR(1)            DEFAULT 'Y' NOT NULL,
    CREATED         TIMESTAMP          DEFAULT CURRENT_TIMESTAMP NOT
NULL,
    CREATEDBY       DECIMAL(10,0)      NOT NULL,
    UPDATED         TIMESTAMP          DEFAULT CURRENT_TIMESTAMP NOT
NULL,
    UPDATEDBY       DECIMAL(10,0)      NOT NULL,
    AD_TABLE_ID     DECIMAL(10,0)          NULL,
    AD_COLUMN_ID    DECIMAL(10,0)          NULL,
    RECORD_ID       DECIMAL(10,0)          NULL,

    CONSTRAINT AD_ACCESSLOG_KEY
	PRIMARY KEY (AD_ACCESSLOG_ID),
    CONSTRAINT ADCOLUMN_ADACCESSLOG
	FOREIGN KEY (AD_COLUMN_ID)
	REFERENCES AD_COLUMN (AD_COLUMN_ID)
)

---
Note that you create an index for a constraint - that is fine, but it
would be helpful to again not get the internal name, but the "external".

Index 'SQL060716064852400' was created to enforce constraint
'ADCOLUMN_ADACCESSLOG'.  It can only be dropped by dropping the
constraint. - DROP INDEX SQL060716064852400
---

Help requested:
---------------
If you please could fix it 
and tell me if I could find/update/fix the function
SYSIBM.SQLSTATISTICS.