db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jorg Janke (JIRA)" <derby-...@db.apache.org>
Subject [jira] Created: (DERBY-1577) DatabaseMetaData.getIndexInfo() returns internal names
Date Sun, 23 Jul 2006 18:18:13 GMT
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

        

Mime
View raw message