DatabaseMetaData.getIndexInfo() returns internal names
------------------------------------------------------
Key: DERBY-1577
URL: http://issues.apache.org/jira/browse/DERBY-1577
Project: Derby
Issue Type: Bug
Components: JDBC
Affects Versions: 10.1.3.1
Environment: Windows 2003 Server
Reporter: Jorg Janke
Problem:
-------------
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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
