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


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:


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


..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 Special tables Application tables Table hierarchy Domains Views Indexes Detailed table descriptions 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.