db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ян Программист <webautoma...@gmail.com>
Subject Table describing
Date Fri, 09 Apr 2010 15:38:41 GMT
I made a tryout using SYS.* tables. I created following DDL:

create table server_shutdown_type
(
id integer(3) not null,
name varchar(10) not null,
primary key(id)
Ru);
create table server_shutdown_log
(
record_id integer not null,
shutdown_type integer not null,
admin_records VARCHAR(30),
primary key (record_id),
foreign key (shutdown_type) references server_shutdown_type (id)
);

First, about primary and foreign keys:

select TABLEID, CONSTRAINTID, TYPE, REFERENCECOUNT from SYS.SYSCONSTRAINTS;

TABLEID                             |CONSTRAINTID
 |&|REFERENCEC&
---------------------------------------------------------------------------------------
2ba7c09b-0127-df6f-bee8-000000547e10|cbf5809d-0127-df6f-bee8-000000547e10|P|1

94bc40a2-0127-df6f-bee8-000000547e10|e50d80a4-0127-df6f-bee8-000000547e10|P|0

94bc40a2-0127-df6f-bee8-000000547e10|4d3680a5-0127-df6f-bee8-000000547e10|F|0

For general lookup:

select TABLEID, TABLENAME from SYS.SYSTABLES;

TABLEID                             |TABLENAME


---------------------------------------------------------------------------------------------------------------------------------------------------------------------
80000010-00d0-fd77-3ed8-000a0a0b1900|SYSCONGLOMERATES


80000018-00d0-fd77-3ed8-000a0a0b1900|SYSTABLES


8000001e-00d0-fd77-3ed8-000a0a0b1900|SYSCOLUMNS


80000022-00d0-fd77-3ed8-000a0a0b1900|SYSSCHEMAS


8000002f-00d0-fd77-3ed8-000a0a0b1900|SYSCONSTRAINTS


80000039-00d0-fd77-3ed8-000a0a0b1900|SYSKEYS


8000003e-00d0-fd77-3ed8-000a0a0b1900|SYSDEPENDS


c013800d-00d7-ddbd-08ce-000a0a411400|SYSALIASES


8000004d-00d0-fd77-3ed8-000a0a0b1900|SYSVIEWS


80000056-00d0-fd77-3ed8-000a0a0b1900|SYSCHECKS


8000005b-00d0-fd77-3ed8-000a0a0b1900|SYSFOREIGNKEYS


80000000-00d1-15f7-ab70-000a0a0b1500|SYSSTATEMENTS


80000000-00d3-e222-873f-000a0a0b1900|SYSFILES


c013800d-00d7-c025-4809-000a0a411200|SYSTRIGGERS


f81e0010-00e3-6612-5a96-009e3a3b5e00|SYSSTATISTICS


c013800d-00f8-5b70-bea3-00000019ed88|SYSDUMMY1


b8450018-0103-0e39-b8e7-00000010f010|SYSTABLEPERMS


286cc01e-0103-0e39-b8e7-00000010f010|SYSCOLPERMS


2057c01b-0103-0e39-b8e7-00000010f010|SYSROUTINEPERMS


e03f4017-0115-382c-08df-ffffe275b270|SYSROLES


2ba7c09b-0127-df6f-bee8-000000547e10|SERVER_SHUTDOWN_TYPE


94bc40a2-0127-df6f-bee8-000000547e10|SERVER_SHUTDOWN_LOG

Hence created earlier SERVER_SHUTDOWN_TYPE and SERVER_SHUTDOWN_LOG

For gathering relations between tables:

select SYS.SYSTABLES.TABLENAME, SYS.SYSCONSTRAINTS.TYPE,
SYS.SYSCONSTRAINTS.REFERENCECOUNT, SYS.SYSCONSTRAINTS.CONSTRAINTID from
SYS.SYSCONSTRAINTS join SYS.SYSTABLES on SYS.SYSCONSTRAINTS.TABLEID =
SYS.SYSTABLES.TABLEID;
TABLENAME

|&|REFERENCEC&|CONSTRAINTID
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SERVER_SHUTDOWN_TYPE
                                                   |P|1
 |cbf5809d-0127-df6f-bee8-000000547e10
SERVER_SHUTDOWN_LOG
                                                    |P|0
 |e50d80a4-0127-df6f-bee8-000000547e10
SERVER_SHUTDOWN_LOG
                                                    |F|0
 |4d3680a5-0127-df6f-bee8-000000547e10

Note: Number of targeted foreign keys could be figured out
via REFERENCECOUNT. If REFERENCECOUNT > 1 then their are cyclic dependencies
in dependency graph

For data compatibility examinations following SQL can be used:

select SYS.SYSTABLES.TABLENAME, SYS.SYSCOLUMNS.COLUMNNAME,
SYS.SYSCOLUMNS.COLUMNDATATYPE from SYS.SYSTABLES, SYS.SYSCOLUMNS where
SYS.SYSTABLES.TABLEID=SYS.SYSCOLUMNS.REFERENCEID and SYS.SYSTABLES.TABLENAME
like 'SERVER%';

TABLENAME
                                                    |COLUMNNAME

                             |COLUMNDATATYPE
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SERVER_SHUTDOWN_LOG
                                                    |ADMIN_RECORDS

                            |VARCHAR(30)
SERVER_SHUTDOWN_LOG
                                                    |RECORD_ID

                            |INTEGER NOT NU&
SERVER_SHUTDOWN_LOG
                                                    |SHUTDOWN_TYPE

                            |INTEGER NOT NU&
SERVER_SHUTDOWN_TYPE
                                                   |ID

                           |INTEGER NOT NU&
SERVER_SHUTDOWN_TYPE
                                                   |NAME

                           |VARCHAR(10) NO&

But what was shocking to me is the following to me:

select SYS.SYSCOLUMNS.COLUMNNAME, SYS.SYSCOLUMNS.COLUMNDATATYPE,
SYS.SYSCONSTRAINTS.TYPE, SYS.SYSCONSTRAINTS.REFERENCECOUNT from
SYS.SYSCOLUMNS inner join SYS.SYSTABLES on SYS.SYSTABLES.TABLEID =
SYS.SYSCOLUMNS.REFERENCEID INNER JOIN SYS.SYSCONSTRAINTS on
SYS.SYSCOLUMNS.REFERENCEID = SYS.SYSCONSTRAINTS.TABLEID where
SYS.SYSTABLES.TABLENAME like 'SERVER%';
COLUMNNAME
                                                   |COLUMNDATATYPE
|&|REFERENCEC&
--------------------------------------------------------------------------------------------------------------------------------------------------------------
ADMIN_RECORDS
                                                    |VARCHAR(30)    |P|0

ADMIN_RECORDS
                                                    |VARCHAR(30)    |F|0

RECORD_ID
                                                    |INTEGER NOT NU&|P|0

RECORD_ID
                                                    |INTEGER NOT NU&|F|0

SHUTDOWN_TYPE
                                                    |INTEGER NOT NU&|P|0

SHUTDOWN_TYPE
                                                    |INTEGER NOT NU&|F|0

ID
                                                   |INTEGER NOT NU&|P|1

NAME
                                                   |VARCHAR(10) NO&|P|1


Hence: columns, not mentioned in table creation as primary keys, for example
NAME column. listed as primary(?!) But those are not primary. Or they are
auto-converted? John

Mime
View raw message