How-to Guides‎ > ‎

Inspecting the schema of a Polyhedra database

There are three simple ways of looking at the structure of a Polyhedra database: by direct inspection of special tables in the database (the schema tables) that hold information about the structure, by using third-party tools that make use of the ODBC and JDBC schema inspection functions, and by using a tool written in CL that will produce an HTML file documenting the structure of a database. These approaches are discussed in the following sections, and there is zip file containing a copy of the tool attached to the the end of this page.


The schema tables

Polyhedra is a family of relational database management systems, and obeys one of 'Codd's commandments' that insists that true relational database systems should ensure that information about the schema of a database should be available for inspection in the same way as contents of the database. For this reason, there are special tables in each Polyhedra database that can be inspected to see the tables, views and attributes that are available in the database. These special tables are:

table name
description attributes
tables This table has a record for each table, domain, view or built-in datatype in the database name, depth, type, derived_from, islocal, pk_index, persistence, system, owner
views This table contains a record for each view defined in the database. It is derived from the tables table This has the same attributes as the tables table, plus an additional attribute called value which contains the text of the query defining the view
attributes This table contains a record for each attribute in a table, domain or view. table_name, name, type, length, isexternal, not_null, persistence, isarray, ishidden, isprimary, default_value, islocal, isshared, isvirtual
indexes This table contains a record for each index in the database. The table contains user-defined indexes and also indexes on primary keys (prefixed by PK-) or foreign keys (prefixed by FK-) name, table_name, type is_unique, foreign_table, constraint_type
indexattrs This table contains a record for each attribute included in an index. index_name, index_order, attr_table, attr_name

A detailed description of these tables can be found in section 8 of the rtrdb reference manual. If the user-based security has been enabled, there will be a table called users, and some additional special tables describing the current set of access privileges.

A client may investigate the database structure using standard SQL to query the schema tables (but they cannot be directly modified). Thus, a query such as

select * from tables where type=0 order by name

will give you all information about the tables in the database (omitting entries for domains, views and datatypes).

Third party tools

Polyhedra has developed ODBC drivers for use on Windows and Linux/x86, and a JDBC driver (that is written in Java and thus platform-independent). This means that third party inspection and modelling tools  can use ODBC and JDBC schema inspection calls to look at the structure of the database, and then use this information to browse the contents of the database. Please see the Third Party Tools page for more information. Most of these tools assume Polyhedra is a 'standard' relational database, but SQLSpec is different in that it knows that Polyhedra supports table inheritance and can handle tables that make use of this feature.

A CL tool to inspect a Polyhedra database

Attached to the bottom of this web page you will find a ZIP file containing a directory with a Polyhedra CL file,  a Polyhedra configuration file, an SQL file containing some table definitions, and a readme file. The tables.sql file contains the following definitions:

-- create some tables, domains, views and indexes to test the application.

CREATE SCHEMA

-- a simple domain

CREATE DOMAIN d1
( v1 INTEGER NOT NULL
, v2 INTEGER default 3
)

-- a derived domain with a refence to a table that is defined further below
-- (hence the use of CREATE SCHEMA)


CREATE DOMAIN d2
( derived from d1
, v3 INTEGER references one
)

-- a simple table to test basic operation; it has a mixture of attribute qualifiers

CREATE TABLE one
( PERSISTENT
, id INTEGER PRIMARY KEY
, arg2 INTEGER NOT NULL
, arg3 INTEGER TRANSIENT
, arg4 INTEGER TRANSIENT LOCAL
, arg5 INTEGER HIDDEN
, arg6_shared INTEGER SHARED
, arg7_virtual INTEGER VIRTUAL
)

-- now a table to test handling of different types, and defaults

CREATE TABLE two
( PERSISTENT
, id INTEGER PRIMARY KEY
, arg2 LARGE VARCHAR DEFAULT 'arg2'
, arg3 CHAR(1) DEFAULT 'arg3'
, arg4 VARCHAR(20) DEFAULT 'arg4'
, arg5 FLOAT DEFAULT 5.12
, arg6 float32 DEFAULT 6.0
, arg7 integer16 DEFAULT 16
, arg8 integer8 DEFAULT 8
, arg9 DATETIME DEFAULT '01-Jan-1973 11:12:13'
, arg10 BOOL DEFAULT TRUE
, arg11 BINARY(50) DEFAULT x'0102030405'
, arg12 binary large object
, arg13 ARRAY OF one TRANSIENT
, arg14 large VARCHAR(20)
, arg15 large VARCHAR
, arg16 INTEGER64
)

-- a table with multi-column primary keys

CREATE TABLE three
( persistent
, id1 INTEGER, id2 INTEGER, PRIMARY KEY (id1, id2)
, arg3 INTEGER
)

-- a table with references, including multi-key references and cascade conditions

CREATE TABLE four
( TRANSIENT
, id   INTEGER PRIMARY KEY REFERENCES one on delete restrict on update cascade
, arg2 INTEGER NOT NULL    REFERENCES one on update cascade  on delete cascade
, arg3 INTEGER
, arg4 INTEGER
, FOREIGN KEY (arg3, arg4) REFERENCES three (id1, id2) on delete set null
, arg5 INTEGER
, arg6 INTEGER
, FOREIGN KEY (arg5, arg6) REFERENCES three (id2, id1) on delete cascade on update restrict

)

-- a derived table

CREATE TABLE five
( TRANSIENT, DERIVED FROM two
, extra1 INTEGER TRANSIENT
, extra2 INTEGER PERSISTENT
, extra3 INTEGER references five
)

-- another derived table

CREATE TABLE six
( TRANSIENT, DERIVED FROM five
, extra4 INTEGER TRANSIENT
, extra5 INTEGER PERSISTENT
, extra6 INTEGER references five
)

-- yet another derived table; note that the added attributes have
-- the same names as in another table derived from the same base
-- table - this is perfectly legitimate!
-- Also, one of the attributes has a name that is a reserved word.

CREATE TABLE seven
( TRANSIENT,   DERIVED FROM five
, extra4       INTEGER TRANSIENT
, extra5       INTEGER PERSISTENT
, "References" INTEGER references four
)

-- add some more derived tables, so we can see how the application
-- handles trees.


CREATE TABLE eight
( TRANSIENT, DERIVED FROM two
, extra1   INTEGER TRANSIENT
, extra2   INTEGER PERSISTENT
, "Extra3" INTEGER references eight
)

CREATE TABLE nine
( TRANSIENT, DERIVED FROM six
, extra7 INTEGER TRANSIENT
, extra8 INTEGER PERSISTENT
, extra9 INTEGER references two
)

-- recent versions of Polyhedra support 64-bit signed integers

CREATE TABLE ten
( id integer primary key
, data integer64
)

-- add a table with some domain attributes

CREATE TABLE eleven
( id integer PRIMARY KEY
, x d1
, y d2
)
;

-- finally, some indexes and views

CREATE INDEX i1 ON one (arg3);
CREATE INDEX i2 ON two (id, arg2);
CREATE ordered INDEX i3_o ON three (arg3);
CREATE UNIQUE INDEX i4_u ON three (id1);
CREATE UNIQUE ordered INDEX i5_uo ON four (id);
CREATE INDEX i6 ON one (arg4);

CREATE VIEW view_one AS SELECT name, depth, derived_from FROM TABLES WHERE TYPE = 0;


If you were to populate an empty database with the above definitions, and then run the command...

clc inspect

..the application would generate an HTML file that would produce output similar to that given below. In addition, it will produce a file containing the SQL needed to reconstruct the database schema (but without attempting to establish the user privileges). The readme file in the package gives more detail about how to run the application, and as the application is supplied in source form it is easy to alter the HTML or SQL output to suit your preferences! The application will also check the database to see if any of the table, attribute, view, domain or index names can be found in any of the lists of special words. As well as the words reserved by the Polyhedra SQL engine, it also checks against the words reserved by the CL engine, the SQL 2003 standard, and also the C and C++ standards; where a hit is found, an appropriate warning is produced. In the case of Polyhedra SQL, if hit is against a recently-introduced reserved word, then the tool will tell you the version in which it was introduced; it even checks against the words we are expecting to introduce into Polyhedra 8.9 to enable new functionality! (It also allows you to check against your own list of words, and even to provide a list of approved words that are not checked against any of the other lists.)

 

Database Schema Contents:

Table summary

System tables

(these read-only tables allow the schema to be inspected via SQL)
name persistence derived from columns
attributes transient   table_name, name, type, length, isexternal, not_null, persistence, isarray, ishidden, isprimary, default_value, islocal, isshared, isvirtual
indexattrs transient   index_name, index_order, attr_table, attr_name
indexes transient   name, table_name, type, constraint_type, is_unique, foreign_table, update_rule, delete_rule
tables transient   name, depth, type, derived_from, pk_index, persistence, system, owner, islocal
views transient
tables views
value

(top)

Special tables

(tables used by submodules of the rtrdb - the CL engine, the DVI, Historian, etc)
name persistence derived from columns
clcontrol transient, local   id, disable
dataservice persistent   name, isopen, ftmode
dbcontrol persistent   id, checksum_interval, restart
exceptionhandler transient, local   id
journalcontrol transient, local   id, filename, created, mode, replication_depth, disable, first_transaction, last_transaction, current_transaction, file_size, file_size_high
sqlprocedure persistent   name, text
tcpconnection persistent   id
tcpserver persistent   id
timer persistent   id, interval, ticktime, counter, active, continuous
udpmsg persistent   id
udpport persistent   id

(top)

Application tables

(in alphabetical order)
name persistence derived from columns
eight transient
two eight
extra1, extra2, Extra3
eleven persistent   id, x, y
five transient
two five
extra1, extra2, extra3
four transient   id, arg2, arg3, arg4, arg5, arg6
nine transient
two five six nine
extra7, extra8, extra9
one persistent   id, arg2, arg3, arg4, arg5, arg6_shared, arg7_virtual
seven transient
two five seven
extra4, extra5, References
six transient
two five six
extra4, extra5, extra6
ten persistent   id, data
three persistent   id1, id2, arg3
two persistent   id, arg2, arg3, arg4, arg5, arg6, arg7, arg8, arg9, arg10, arg11, arg12, arg13, arg14, arg15, arg16

(top)

Table hierarchy

attributes
indexattrs
indexes
tables views
clcontrol
dataservice
dbcontrol
exceptionhandler
journalcontrol
sqlprocedure
tcpconnection
tcpserver
timer
udpmsg
udpport
eleven
four
one
ten
three
two eight
five seven
six nine

(top)


Domains

name persistence derived from columns
exception transient, local   code
runtimeexception transient, local
exception runtimeexception
text
d1 persistent   v1, v2
d2 persistent
d1 d2
v3

(top)


Views

view_one SELECT name, depth, derived_from FROM TABLES WHERE TYPE = 0;
the view appears as though it had been set up by the table definition:
    create view view_one
    ( transient
    , name         large  varchar primary key
    , depth        integer
    , derived_from large  varchar references tables 
    )
view_two SELECT count (*) tablecount FROM TABLES;
the view appears as though it had been set up by the table definition:
    create view view_two
    ( transient
    , tablecount integer 
    )

(top)


Indexes

name table columns ordered? unique?
i1 one arg3    
i2 two id, arg2    
i3_o three arg3
yes
 
i4_u three id1  
yes
i5_uo four id
yes
yes
i6 one arg4    

(top)


Table Details

Domains are listed in order of definition, and then similarly for tables - so derived tables will come somewhere after the tables from which the are derived. Each table description is laid out in a tabular fashion, with the "banner row" containing a list of superclasses (if any), then the name of the table. In the case of derived tables, the description will indicate not just the columns added in the table definition, but also the inherited columns.
d1 
derived tables
d1 d2
persistence persistent
columns column name type null? persistence hidden? references default
v1 integer not null persistent      
v2 integer   persistent     3
used by eleven (x)
definition
create domain d1
( persistent
, v1 integer not null
, v2 integer default 3 
)

(top)

d2 
derivation chain
d1 d2
persistence persistent
columns inherited from d1 column name type null? persistence hidden? references default
v1 integer not null persistent      
v2 integer   persistent     3
added columns v3 integer   persistent   one  
used by eleven (y)
definition
create domain d2
( persistent 
, derived from d1
, v3 integer references one 
)

(top)

one 
persistence persistent
columns column name type null? persistence hidden? references default
id integer primary key persistent      
arg2 integer not null persistent      
arg3 integer   transient      
arg4 integer   transient local      
arg5 integer   persistent hidden    
arg6_shared integer   persistent shared    
arg7_virtual integer   persistent virtual    
primary key id
referenced by d2 (v3)
four (id)
four (arg2)
definition
create table one
( persistent
, id           integer primary key
, arg2         integer not null
, arg3         integer transient
, arg4         integer transient local
, arg5         integer hidden
, arg6_shared  integer shared
, arg7_virtual integer virtual 
)

(top)

two 
derived tables
two eight
five seven
six nine
persistence persistent
columns column name type null? persistence hidden? references default
id integer primary key persistent      
arg2 large varchar   persistent     'arg2'
arg3 large varchar   persistent     'arg3'
arg4 varchar(20)   persistent     'arg4'
arg5 float   persistent     5.12
arg6 float32   persistent     6.0
arg7 integer16   persistent     16
arg8 integer8   persistent     8
arg9 datetime   persistent     '01-JAN-1973 11:12:13'
arg10 bool   persistent     true
arg11 binary large object   persistent     x'0102030405'
arg12 binary large object   persistent      
arg13 array of one   transient      
arg14 large varchar(20)   persistent      
arg15 large varchar   persistent      
arg16 integer64   persistent      
primary key id
referenced by nine (extra9)
definition
create table two
( persistent
, id    integer primary key
, arg2  large  varchar default 'arg2'
, arg3  large  varchar default 'arg3'
, arg4  varchar(20) default 'arg4'
, arg5  float default 5.12
, arg6  float32 default 6.0
, arg7  integer16 default 16
, arg8  integer8 default 8
, arg9  datetime default '01-JAN-1973 11:12:13'
, arg10 bool default true
, arg11 binary large object default x'0102030405'
, arg12 binary large object
, arg13 array of one transient
, arg14 large  varchar(20)
, arg15 large  varchar
, arg16 integer64 
)

(top)

three 
persistence persistent
columns column name type null? persistence hidden? references default
id1 integer not null persistent      
id2 integer not null persistent      
arg3 integer   persistent      
primary key id1, id2
referenced by four (arg3, arg4)
four (arg6, arg5)
definition
create table three
( persistent
, id1  integer not null
, id2  integer not null
, arg3 integer
, primary key (id1, id2) 
)

(top)

four 
persistence transient
columns column name type null? persistence hidden? references default
id integer primary key transient   one  
arg2 integer not null transient   one  
arg3 integer   transient      
arg4 integer   transient      
arg5 integer   transient      
arg6 integer   transient      
primary key id
multi-column foreign keys (arg3, arg4) references three (id1, id2) on delete set null
(arg6, arg5) references three (id1, id2) on update restrict on delete cascade
referenced by seven (References)
definition
create table four
( transient
, id   integer primary key references one
, arg2 integer not null references one on delete cascade
, arg3 integer
, arg4 integer
, arg5 integer
, arg6 integer
, foreign key (arg3, arg4) references three (id1, id2) 
on delete set null , foreign key (arg6, arg5) references three (id1, id2)
 on update restrict on delete cascade )

(top)

five 
derivation chain,
plus derived tables
two five seven
six nine
persistence transient
columns inherited from two column name type null? persistence hidden? references default
id integer primary key persistent      
arg2 large varchar   persistent     'arg2'
arg3 large varchar   persistent     'arg3'
arg4 varchar(20)   persistent     'arg4'
arg5 float   persistent     5.12
arg6 float32   persistent     6.0
arg7 integer16   persistent     16
arg8 integer8   persistent     8
arg9 datetime   persistent     '01-JAN-1973 11:12:13'
arg10 bool   persistent     true
arg11 binary large object   persistent     x'0102030405'
arg12 binary large object   persistent      
arg13 array of one   transient      
arg14 large varchar(20)   persistent      
arg15 large varchar   persistent      
arg16 integer64   persistent      
added columns extra1 integer   transient      
extra2 integer   persistent      
extra3 integer   transient   five  
primary key id (inherited from two)
referenced by five (extra3)
six (extra6)
definition
create table five
( transient 
, derived from two
, extra1 integer
, extra2 integer persistent
, extra3 integer references five 
)

(top)

six 
derivation chain,
plus derived tables
two five six nine
persistence transient
columns inherited from two column name type null? persistence hidden? references default
id integer primary key persistent      
arg2 large varchar   persistent     'arg2'
arg3 large varchar   persistent     'arg3'
arg4 varchar(20)   persistent     'arg4'
arg5 float   persistent     5.12
arg6 float32   persistent     6.0
arg7 integer16   persistent     16
arg8 integer8   persistent     8
arg9 datetime   persistent     '01-JAN-1973 11:12:13'
arg10 bool   persistent     true
arg11 binary large object   persistent     x'0102030405'
arg12 binary large object   persistent      
arg13 array of one   transient      
arg14 large varchar(20)   persistent      
arg15 large varchar   persistent      
arg16 integer64   persistent      
columns inherited from five extra1 integer   transient      
extra2 integer   persistent      
extra3 integer   transient   five  
added columns extra4 integer   transient      
extra5 integer   persistent      
extra6 integer   transient   five  
primary key id (inherited from two)
definition
create table six
( transient 
, derived from five
, extra4 integer
, extra5 integer persistent
, extra6 integer references five 
)

(top)

seven 
derivation chain
two five seven
persistence transient
columns inherited from two column name type null? persistence hidden? references default
id integer primary key persistent      
arg2 large varchar   persistent     'arg2'
arg3 large varchar   persistent     'arg3'
arg4 varchar(20)   persistent     'arg4'
arg5 float   persistent     5.12
arg6 float32   persistent     6.0
arg7 integer16   persistent     16
arg8 integer8   persistent     8
arg9 datetime   persistent     '01-JAN-1973 11:12:13'
arg10 bool   persistent     true
arg11 binary large object   persistent     x'0102030405'
arg12 binary large object   persistent      
arg13 array of one   transient      
arg14 large varchar(20)   persistent      
arg15 large varchar   persistent      
arg16 integer64   persistent      
columns inherited from five extra1 integer   transient      
extra2 integer   persistent      
extra3 integer   transient   five  
added columns extra4 integer   transient      
extra5 integer   persistent      
References integer   transient   four  
primary key id (inherited from two)
definition
create table seven
( transient 
, derived from five
, extra4       integer
, extra5       integer persistent
, "References" integer references four 
)

(top)

eight 
derivation chain
two eight
persistence transient
columns inherited from two column name type null? persistence hidden? references default
id integer primary key persistent      
arg2 large varchar   persistent     'arg2'
arg3 large varchar   persistent     'arg3'
arg4 varchar(20)   persistent     'arg4'
arg5 float   persistent     5.12
arg6 float32   persistent     6.0
arg7 integer16   persistent     16
arg8 integer8   persistent     8
arg9 datetime   persistent     '01-JAN-1973 11:12:13'
arg10 bool   persistent     true
arg11 binary large object   persistent     x'0102030405'
arg12 binary large object   persistent      
arg13 array of one   transient      
arg14 large varchar(20)   persistent      
arg15 large varchar   persistent      
arg16 integer64   persistent      
added columns extra1 integer   transient      
extra2 integer   persistent      
Extra3 integer   transient   eight  
primary key id (inherited from two)
referenced by eight (Extra3)
definition
create table eight
( transient 
, derived from two
, extra1   integer
, extra2   integer persistent
, "Extra3" integer references eight 
)

(top)

nine 
derivation chain
two five six nine
persistence transient
columns inherited from two column name type null? persistence hidden? references default
id integer primary key persistent      
arg2 large varchar   persistent     'arg2'
arg3 large varchar   persistent     'arg3'
arg4 varchar(20)   persistent     'arg4'
arg5 float   persistent     5.12
arg6 float32   persistent     6.0
arg7 integer16   persistent     16
arg8 integer8   persistent     8
arg9 datetime   persistent     '01-JAN-1973 11:12:13'
arg10 bool   persistent     true
arg11 binary large object   persistent     x'0102030405'
arg12 binary large object   persistent      
arg13 array of one   transient      
arg14 large varchar(20)   persistent      
arg15 large varchar   persistent      
arg16 integer64   persistent      
columns inherited from five extra1 integer   transient      
extra2 integer   persistent      
extra3 integer   transient   five  
columns inherited from six extra4 integer   transient      
extra5 integer   persistent      
extra6 integer   transient   five  
added columns extra7 integer   transient      
extra8 integer   persistent      
extra9 integer   transient   two  
primary key id (inherited from two)
definition
create table nine
( transient 
, derived from six
, extra7 integer
, extra8 integer persistent
, extra9 integer references two 
)

(top)

ten 
persistence persistent
columns column name type null? persistence hidden? references default
id integer primary key persistent      
data integer64   persistent      
primary key id
definition
create table ten
( persistent
, id   integer primary key
, data integer64 
)

(top)

eleven 
persistence persistent
columns column name type null? persistence hidden? references default
id integer primary key persistent      
x large d1 not null persistent      
y large d2 not null persistent      
primary key id
definition
create table eleven
( persistent
, id integer primary key
, x  large  d1 not null
, y  large  d2 not null 
)

(top)


File db_schema.htm created on Wed, 23 JUL 2014 19:56:10 GMT using $Revision: 1.5 $ of the Polyhedra schema scanner.

PLEASE NOTE:

  • Security information not included.
  • Accuracy not warranted.
Statistics:
  • info gather time was 0.02 seconds.
  • html preparation time was 0.01 seconds.
  • schema has 27 tables, 4 domains, 2 views and 6 indexes.

ċ
inspect.zip
(40k)
Nigel Day,
23 Jul 2014, 13:38