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 Ян Программист <we...@gmail.com> on 2010/04/09 17:38:41 UTC
Table describing
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