You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Mamta Satoor <ms...@gmail.com> on 2006/01/30 19:59:34 UTC
System tables in Derby
Hi,
In my work on Derby-655(getImportedKeys return duplicate rows), I need to
make sure that the metadata.properties's getImportedKeys query has been
written correctly. There are many system tables that are joined in this
query and it will help to understand how the system tables are related to
each other. Is there anyone out there who can provide little more
information than what we have in the docs on the system tables? The tables
referenced by this query in particular are SYS.SYSTABLES, SYS.SYSSCHEMAS,
SYS.SYSCONSTRAINTS, SYS.SYSFOREIGNKEYS, SYS.SYSCONGLOMERATES, SYS.SYSCOLUMNS,
SYS.SYSKEYS. The query in reference is as follows
SELECT CAST ('' AS VARCHAR(128)) AS PKTABLE_CAT, \
S.SCHEMANAME AS PKTABLE_SCHEM, \
TABLENAME AS PKTABLE_NAME, \
COLS.COLUMNNAME AS PKCOLUMN_NAME, \
CAST ('' AS VARCHAR(128)) AS FKTABLE_CAT, \
FKTABLE_SCHEM, \
FKTABLE_NAME, \
FKCOLUMN_NAME, \
CAST (CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \
COLS.COLUMNNUMBER) \
AS SMALLINT) AS KEY_SEQ, \
CAST ((CASE WHEN FK_UPDATERULE='S' \
THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE \
(CASE WHEN FK_UPDATERULE='R' \
THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE \
java.sql.DatabaseMetaData::importedKeyNoAction END) END) \
AS SMALLINT) AS UPDATE_RULE, \
CAST ((CASE WHEN FK_DELETERULE='S' \
THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE \
(CASE WHEN FK_DELETERULE='R' \
THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE \
(CASE WHEN FK_DELETERULE='C' \
THEN java.sql.DatabaseMetaData::importedKeyCascade ELSE \
(CASE WHEN FK_DELETERULE='U' \
THEN java.sql.DatabaseMetaData::importedKeySetNull ELSE \
java.sql.DatabaseMetaData::importedKeyNoAction END) END) END) END) \
AS SMALLINT) AS DELETE_RULE, \
FK_NAME, \
CONSTRAINTNAME AS PK_NAME, \
CAST (java.sql.DatabaseMetaData::importedKeyNotDeferrable \
AS SMALLINT) AS DEFERRABILITY \
FROM --DERBY-PROPERTIES joinOrder=FIXED \n\
(SELECT F2.keyCONSTRAINTID AS FK_ID, \
FKTB_SCHEMA AS FKTABLE_SCHEM, \
FKTB_NAME AS FKTABLE_NAME, \
COLS2.COLUMNNAME AS FKCOLUMN_NAME, \
CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \
COLS2.COLUMNNUMBER) AS KEY_SEQ, \
C2.CONSTRAINTNAME AS FK_NAME, \
F2.DELETERULE AS FK_UPDATERULE, \
F2.DELETERULE AS FK_DELETERULE \
FROM --DERBY-PROPERTIES joinOrder=FIXED \n\
(SELECT T2.TABLEID AS FKTB_ID, \
S2.SCHEMANAME AS FKTB_SCHEMA, \
T2.TABLENAME AS FKTB_NAME \
FROM \
SYS.SYSTABLES T2 --DERBY-PROPERTIES index = 'SYSTABLES_INDEX1' \n\
, SYS.SYSSCHEMAS S2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index
= 'SYSSCHEMAS_INDEX1' \n\
WHERE \
((1=1) OR ? IS NOT NULL) \
AND S2.SCHEMANAME LIKE ? \
AND T2.TABLENAME LIKE ? \
AND S2.SCHEMAID = T2.SCHEMAID \
) AS FKTB (FKTB_ID, FKTB_SCHEMA, FKTB_NAME), \
SYS.SYSCONSTRAINTS c2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP,
index = 'SYSCONSTRAINTS_INDEX3' \n\
, SYS.SYSFOREIGNKEYS F2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP,
index = 'SYSFOREIGNKEYS_INDEX1' \n\
, SYS.SYSCONGLOMERATES CONGLOMS2 --DERBY-PROPERTIES
joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\
, SYS.SYSCOLUMNS COLS2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP,
index = 'SYSCOLUMNS_INDEX1' \n\
WHERE \
FKTB.FKTB_ID = C2.TABLEID \
AND F2.CONSTRAINTID = C2.CONSTRAINTID \
AND FKTB.FKTB_ID = COLS2.REFERENCEID \
AND (CASE WHEN CONGLOMS2.DESCRIPTOR IS NOT NULL THEN \
CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \
COLS2.COLUMNNUMBER) ELSE \
0 END) <> 0 \
AND F2.CONGLOMERATEID = CONGLOMS2.CONGLOMERATEID \
) AS FKINFO(FK_ID, \
FKTABLE_SCHEM, \
FKTABLE_NAME, \
FKCOLUMN_NAME, \
KEY_SEQ, \
FK_NAME, \
FK_UPDATERULE, \
FK_DELETERULE), \
SYS.SYSCONSTRAINTS c --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index =
'SYSCONSTRAINTS_INDEX1' \n\
, SYS.SYSTABLES T --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index =
'SYSTABLES_INDEX2' \n\
, SYS.SYSSCHEMAS S --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index =
'SYSSCHEMAS_INDEX2' \n\
, SYS.SYSKEYS K --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index =
'SYSKEYS_INDEX1' \n\
, SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy =
NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\
, SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index =
'SYSCOLUMNS_INDEX1' \n\
WHERE T.TABLEID = C.TABLEID \
AND C.CONSTRAINTID = FKINFO.FK_ID \
AND FKINFO.KEY_SEQ = CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \
COLS.COLUMNNUMBER) \
AND S.SCHEMAID = T.SCHEMAID \
AND K.CONSTRAINTID = C.CONSTRAINTID \
AND (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \
CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE 0 END) <>
0 \
AND K.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \
AND C.TABLEID = COLS.REFERENCEID \
ORDER BY PKTABLE_CAT, \
PKTABLE_SCHEM, \
PKTABLE_NAME, \
PK_NAME, \
KEY_SEQ
thanks,
Mamta
Re: System tables in Derby
Posted by Francois Orsini <fr...@gmail.com>.
Would be great to capture Jeff's info on System catalogs in a new
entry off http://db.apache.org/derby/papers/index.html#Derby+Engine
Then one can add onto it for additional details or catalogs that are
missing...having a dedicated entry about derby's system catalog
(metadata) would be neat indeed...Just some thoughts...
On 1/30/06, Jeffrey Lichtman <sw...@rcn.com> wrote:
>
> >The tables referenced by this query in particular are SYS.SYSTABLES,
> >SYS.SYSSCHEMAS, SYS.SYSCONSTRAINTS, SYS.SYSFOREIGNKEYS,
> >SYS.SYSCONGLOMERATES, SYS.SYSCOLUMNS, SYS.SYSKEYS.
>
> I'm not sure what you need, but I'll try to give an idea of the
> relationships between these tables.
>
> SYSTABLES has one row for each table in the database. Its primary key
> is TABLEID, which contains system-generated values. The SCHEMAID is a
> foreign key column, which references SYSSCHEMAS.
>
> SYSSCHEMAS has one row for each schema in the database. Its primary
> key is SCHEMAID.
>
> SYSCOLUMNS has one row for each column of every table in the
> database. Its primary key is (REFERENCEID, COLUMNNUMBER). REFERENCEID
> is also a foreign key, which refers to TABLEID in SYSTABLES.
>
> SYSCONSTRAINTS has one row for each constraint in the database
> (primary, unique, foreign and check constraints). Its primary key is
> CONSTRAINTID, which is a system-generated value. The TABLEID column
> is a foreign key referring to SYSTABLES.TABLEID. The SCHEMAID column
> is a foreign key referring to SYSSCHEMAS.SCHEMAID.
>
> SYSCONGLOMERATES has one row for each heap (base table) and index in
> the database. Its primary key is CONGLOMERATEID, which is a
> system-generated value. The TABLEID column is a foreign key referring
> to SYSTABLES.TABLEID. The SCHEMAID column is a foreign key referring
> to SYSSCHEMAS.SCHEMAID. The ISCONSTRAINT column tells whether the row
> represents the backing index for a primary key, foreign key or unique
> constraint.
>
> SYSKEYS has one row for each primary key or unique constraint in the
> database. Its primary key is CONSTRAINTID, which is also a foreign
> key to SYSCONSTRAINTS.CONSTRAINTID. The CONGLOMERATEID column is a
> foreign key referring to SYSCONGLOMERATES.CONGLOMERATEID.
>
> SYSFOREIGNKEYS has one row for each foreign key in the database. Its
> primary key is (CONSTRAINTID, KEYCONSTRAINTID). The CONSTRAINTID
> column is a foreign key refererring to SYSCONSTRAINTS.CONSTRAINTID,
> and it contains the constraint id of the foreign key itself. The
> KEYCONSTRAINTID is a foreign key referring to
> SYSCONSTRAINTS.CONSTRAINTID, and it contains the constraint id of the
> primary key that the foreign key refers to. The CONGLOMERATEID column
> is a foreign key that refers to SYSCONGLOMERATES.CONGLOMERATEID, and
> it contains the id of the backing index for the foreign key.
>
> By the way, last night I did a manual run-through of the query with
> Mamta's example, and I didn't find anything wrong. I would suggest to
> anyone working on this problem to work from the outside in - figure
> out the results of the two table subqueries (FKTB and FKINFO) before
> looking at the outer query.
>
> It's possible that the bug isn't in the query itself. That is, the
> bug could be in query compilation or execution. It might be a good
> idea to compare the query plans and runtime statistics for Derby and
> Cloudscape 5.1.60 (since Kathey says it works in Cloudscape).
>
>
> - Jeff Lichtman
> swazoo@rcn.com
> Check out Swazoo Koolak's Web Jukebox at
> http://swazoo.com/
>
>
Re: System tables in Derby
Posted by Jeffrey Lichtman <sw...@rcn.com>.
>The tables referenced by this query in particular are SYS.SYSTABLES,
>SYS.SYSSCHEMAS, SYS.SYSCONSTRAINTS, SYS.SYSFOREIGNKEYS,
>SYS.SYSCONGLOMERATES, SYS.SYSCOLUMNS, SYS.SYSKEYS.
I'm not sure what you need, but I'll try to give an idea of the
relationships between these tables.
SYSTABLES has one row for each table in the database. Its primary key
is TABLEID, which contains system-generated values. The SCHEMAID is a
foreign key column, which references SYSSCHEMAS.
SYSSCHEMAS has one row for each schema in the database. Its primary
key is SCHEMAID.
SYSCOLUMNS has one row for each column of every table in the
database. Its primary key is (REFERENCEID, COLUMNNUMBER). REFERENCEID
is also a foreign key, which refers to TABLEID in SYSTABLES.
SYSCONSTRAINTS has one row for each constraint in the database
(primary, unique, foreign and check constraints). Its primary key is
CONSTRAINTID, which is a system-generated value. The TABLEID column
is a foreign key referring to SYSTABLES.TABLEID. The SCHEMAID column
is a foreign key referring to SYSSCHEMAS.SCHEMAID.
SYSCONGLOMERATES has one row for each heap (base table) and index in
the database. Its primary key is CONGLOMERATEID, which is a
system-generated value. The TABLEID column is a foreign key referring
to SYSTABLES.TABLEID. The SCHEMAID column is a foreign key referring
to SYSSCHEMAS.SCHEMAID. The ISCONSTRAINT column tells whether the row
represents the backing index for a primary key, foreign key or unique
constraint.
SYSKEYS has one row for each primary key or unique constraint in the
database. Its primary key is CONSTRAINTID, which is also a foreign
key to SYSCONSTRAINTS.CONSTRAINTID. The CONGLOMERATEID column is a
foreign key referring to SYSCONGLOMERATES.CONGLOMERATEID.
SYSFOREIGNKEYS has one row for each foreign key in the database. Its
primary key is (CONSTRAINTID, KEYCONSTRAINTID). The CONSTRAINTID
column is a foreign key refererring to SYSCONSTRAINTS.CONSTRAINTID,
and it contains the constraint id of the foreign key itself. The
KEYCONSTRAINTID is a foreign key referring to
SYSCONSTRAINTS.CONSTRAINTID, and it contains the constraint id of the
primary key that the foreign key refers to. The CONGLOMERATEID column
is a foreign key that refers to SYSCONGLOMERATES.CONGLOMERATEID, and
it contains the id of the backing index for the foreign key.
By the way, last night I did a manual run-through of the query with
Mamta's example, and I didn't find anything wrong. I would suggest to
anyone working on this problem to work from the outside in - figure
out the results of the two table subqueries (FKTB and FKINFO) before
looking at the outer query.
It's possible that the bug isn't in the query itself. That is, the
bug could be in query compilation or execution. It might be a good
idea to compare the query plans and runtime statistics for Derby and
Cloudscape 5.1.60 (since Kathey says it works in Cloudscape).
- Jeff Lichtman
swazoo@rcn.com
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/