Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <14704569.1155299894295.JavaMail.jira@brutus>
Date: Fri, 11 Aug 2006 05:38:14 -0700 (PDT)
From: "Kathey Marsden (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-1577) DatabaseMetaData.getIndexInfo()
 returns internal names
In-Reply-To: <27896868.1153678693831.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

    [ http://issues.apache.org/jira/browse/DERBY-1577?page=comments#action_12427514 ] 
            
Kathey Marsden commented on DERBY-1577:
---------------------------------------

Yes, I like DERBY-1669 better as it states the core problem that  there is not a way to differentiate between a normal index, a primary key or foreign key.    It sounds like we need to discuss the best technical solution on the derby-dev alias.  We can leave this one open too until that is decided, but I think it is better to branch the discussion off of DERBY-1669 as it is  states the problem to be solved most clearly.

(Just for clarification since folks on this list don't know Jason, Jason is one of the frontline Cloudscape support folks at IBM. )


> 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