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] Commented: (DERBY-1577) DatabaseMetaData.getIndexInfo() returns internal names
Date Fri, 11 Aug 2006 02:30:15 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1577?page=comments#action_12427392 ] 
            
Jorg Janke commented on DERBY-1577:
-----------------------------------

A work-around is to use DatabaseMetaData.getPrimaryKeys().
The cause is that in most database systems, the constraint name is also used for the supporting
index.
The follow up issue is that Cloudscape does not allow to differentiate between real indexes
and indexes created to support a constraint.
https://issues.apache.org/jira/browse/DERBY-1669

> 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