To add a new model use the Model desktop. Define the model first and then save it so all the other elements can be subsequently added. Certain details are mandatory (see the InfoBox entry for each field) such as Tag and Catalog Name. Also make sure to set Realise? if the final physical database is actually to be installable. Use the Clusters box to add the basic (mandatory) details of clusters in the model (if any) - the other details must be set using the Cluster panel.

To add a new cluster use the Cluster panel. Certain details are mandatory (see the InfoBox entry for each field) such as Tag. If the cluster represents a scheme (particular slice or module of the model) then set scheme and for all child clusters of the scheme set the their parent cluster via the drop-down to be the scheme cluster. Cluster hierarchies are otherwise not supported - a cluster can belong to one and only one scheme, a cluster can not be a child of a cluster which is not a scheme cluster, a scheme cluster can not be a child of another scheme cluster. The other boxes on this panel can be used to associate model elements with one or more clusters. These elements must all have been defined via other panels first.

Using the desktops to manually create/maintain database tables.

To add a new table use the Dictionary desktop. On the Model panel first query for the specific model to update. By default there would only be one following the basic create and import/reload steps described earlier so an explicit re-query would not be necessary. Then choose the Entity panel and add a new record. Certain details are mandatory (see the InfoBox entry for each field) such as Tag and Catalog Name. For simplicity at this stage do not add any columns, keys, constraints or triggers (do not add records to the Attributes, Keys, Relationships or Events boxes). Since this is a table make sure to set the entity type to Table. Also make sure to set Realise? if the table definition is to be included in the final physical database. Save changes.

Now re-query for the record just added. To define the table columns you can stay on the Entity panel and add a record for each to the box, or you can choose the Attribute panel and add records on that. All the mandatory values can be entered via the box on the Entity panel, but optional values (such as full column description and value sets) need to be done on the Attribute panel. Or enter the basic details quickly in the tabular box view, save the changes, and then use the Attribute panel to add any other information. Make sure to set the type as Column and set Realise?. Save changes.

To add primary (or unique) keys to the table use either the Keys box on the Entity panel (to enter mandatory data only) or use the Key panel. You do need to ultimately use the Key panel in order to actually define the columns associated with a key however. Make sure to set the key type as appropriate and set Realise?. On the Key panel use the Constituent Attributes box to add a record for each column that forms the key. Choose the relevant column from the table using the drop-down. Save changes.

To add foreign key constraints to the table use either the Relationships box on the Entity panel (to enter mandatory data only) or use the Relationship panel. It is necessary to use the Relationship panel to actually define the columns associated with any foreign key constraint. Make sure to set the primary table and key using the drop-downs and set Realise?. On the Relationship panel use the Constituent Attributes box to add a record for each column pairing that forms the foreign key constraint. Choose the relevant column from the current table and from the primary table using the drop-downs. Save changes.

To add triggers to the table use either the Events box on the Entity panel (to enter mandatory data only) or use the Event panel. It is necessary to use the Event panel to define specific update columns associated with a trigger (if any). Make sure to set the process entity (function) using the drop-down and set Realise?. On the Event panel use the Updated Attributes box to add a record for each column that when changed will cause an update trigger. Choose the relevant column from the current table using the drop-down. Save changes.

To create other entity types proceed in the same manner. Note though that keys and relationships only apply to the Table and View entity types. With the View entity type they are not realised in the physical database - they provide instead additional "virtual" links between tables and/or views that can be used by TheonUI but would be redundant (or illegal) if actually created. Events only apply to the Table entity type.

Views are created much like tables except that the View type is used and a view definition is required in the container. This will be a suitable SQL SELECT statement. Attributes must be defined for the view as references to the columns returned by the query statement. View definitions can be also be maintained outside of the XSDDB database and loaded using TheonCoupler (see later). Views are actually realised as inline SQL functions with a wrapper view (this is not done here and is part of the transformation process, only the original view as defined can be referenced). To suppress this behaviour use the RawView type instead or containerisation (see below). Use the Requires panel to enter all the other entities the view depends on (other tables and views).

Types are created just like tables except using the Type type.

Enumerates are normally defined as part of a table column definition (attribute values) but are not by default realised so that they are only constrained for use in that specific table. It is not possible in the current structure to define completely table independent enumerates, other than via containerisation (see below).

Non-unique indexes are created automatically for all foreign constraint columns. If these are not wanted the Realise? flag should be unset for them. Indexes are held the Key panel (or Keys box of the Entity panel) with type Index. Additional indexes can also be added for performance if necessary. Indexes can be created with an appropriate key entry (using the Index type) and relevant attribute entrys to define the constitute table columns.

Sequences created with an appropriate entity entry (Sequence type) and named attribute properties. Create these by adding an attribute for each non-default value sequence option (e.g. max cycle, step size, etc). The attribute type should be Property and the name of the attribute should match the relevant SQL SEQUENCE option name (ignoring case). The attribute value can then be used to set the corresponding sequence option value.

SQL and PL/PGSQL functions (normal or trigger) are created with an appropriate entity entry (Function, EventFunction or TableFunction type) and content type setting. A container definition must be included to define the function (the content between DECLARE, BEGIN and END only). Other function languages are partially supported - the content type must be set to External and the definition must be the full function including CREATE statement. Often a function is not defined directly via TheonUI like this but is loaded via TheonCoupler from an external source (either as its easier to edit that way or because it is automatically built from templating). See later for details. The Attribute panel can be used to define each parameter and optionally the return type. To define the triggering events for trigger functions query for the triggering table on the Entity panel and use the Events box to add the trigger definition, selecting the function defined here from the Process Entity drop-down. Further optional details can be added on the Events panel as necessary.

The Container entity type is used to wrap one or more unsupported SQL DDL statements. Anything can be used in the container definition. A realised container will be written out into the generated SQL DDL statements as-is. It can be only ordered within the output by adding a dependency on another entity (which will then preceed it) using the Requires box.

Existing tables or other entity types can all be updated in much the same fashion as above by first querying then making the necessary changes.

The documentation for all objects should be added here as well so that it is self contained as much as possible. The database schema documentation is automatically built from the data maintained here, so the more detail the better. The same data is also used in the InfoBox in TheonUI.

To add permissions for tables (currently the only supported entity type to which permissions can be applied) use the Role panel (after first selecting a model on the Model panel if not already done and necessary). Query for a suitable role to add the permission to, or create a role first. Save changes first if added a new role and then re-query for it. Next use the Entitys box to add each entity (via drop-down) and types of permission to be granted. You can also use the Attributes box to select a specific entity and attribute via drop-downs for column only permissions.

Permissions are defined as follows. The entry for the entity sets the default. The entry for an attribute sets an exception. This allows the default to be mostly allow or mostly deny depending which is easiest to maintain. For example, if an entity is added with read access checked then the default will be that all columns are readable. In this case any entry for an attribute for that entity with Swap Read access checked will "deny" read access for that specific column only. Conversely if the read access is not checked for the entity then the default will be that all columns are not readable. In this case any entry for an attribute for that entity with Swap Read access checked will "allow" read access for that specific column only. Choose the most suitable approach based on whether the majority columns should be readable or the majority of columns should not be readable. The attributes box does not apply for the "delete" permission which can only be set for the entity as a whole (any attribute entries in this case will be ignored).

Note that roles need to also be assigned to individual users in PostgreSQL which is a process done outside of Theon as it is site dependent.

See the section on TheonCoupler for an overview.

Most of the management of incoming streams and coupling sync processes can be configured here. The actual source data for a stream however (if not internal) must be fetched by another process so as to be locally accessable to TheonCoupler and matching the description configured for it. How external data is fetched is a process done outside of Theon as it is stream and site dependent.

Configuration of streams automatically creates addtional table columns for target tables that are used for couple management metadata. It also creates the sync function for processing the couple. The new columns and function will be immediately visible via the Entity panel and Attribute panel. While changes could be made to these automatically generated entries this should not be done - any changes will be subsequently reverted on the next update.

Note that although the sync process function is added (so it can be referenced by other entities) the body definition is not initially available within the XSDDB management database, nor the exported SchemaTree, as it is only created as part of the DDLCoupler transform which also creates additional hidden couple processing support functions and table triggers. These support functions and triggers will not be added into XSDDB via an import from the physical database either as they are specifically excluded from that stream (as they are held in the theon physical database schema). The definition for the couple processing sync function and added table columns will however be imported and exported (into the SchemaTree), but they are ignored in the normal DDL transform (as they are tagged as being maintained by the relevant TheonCoupler stream), and the DDLCoupler transform will ignore the sync function body definition.

Use the Stream panel to create a new stream or query for an existing stream. Certain data is mandatory when creating a new stream. Once done save and re-query for it so couples can be added. A stream may or may not have a specific common data table for all related couples. It certainly will if the stream is handling external data. The data table must first have been separately defined as an entity. If the stream has an upstream external provider source definition that needs to be maintained separately but consistently with the stream definition then a serial number (identity string) can be set to tie the two together. If this is done the stream will fail if the source data does not have in its local filename a matching serial number. This is generally used to correctly map external data coming from reports in BIS and handle changes to those reports cleanly.

Now use the Couple panel to add each distinct synchronisation process driven from the stream data. Some basic details are mandatory such as the couple tag and name. A couple must also have a source and target table (entity rather as the source at least can be a table, view or table function for example). These must be entities that have been separately defined already. The source table may simply be the same as the stream common data table in many cases. Save the changes as presently the data in the other boxes for bonds and pairs cannot be added until the couple has been saved to the database.

Using the Bond box add a Handle bond type for each couple. This is mandatory. It is the columns in the source and target that tie the records together so the content can be syncronised. Then add any number of Column bonds for each couple. Each of these form sets of columns in the source and target the values of which are kept synchronised. Optionally for a bond a separate entity and relationship can be chosen by drop-down. In this case the bond is formed between the source columns and the columns defined in the separate entity, the value then synchronised is that as defined by the relationship to the target. This is for creating foreign key value lookups (where the source table has a column with a unique value but which is not the actual key value required in the target table which must therefore be retrieved by lookup). Note that a Handle bond can be comprised of multiple instances of that type, so that some elements can be from a lookup via intermediate entity and relationship for example. Save changes so the actual pairs can be added to each bond.

Using the Pair box add each source/target column pairing relevant to the bond. Instead of a column from the source entity a literal SQL expression can be used as the left half of the pair (either directly or as a value to map via a lookup relationship). A literal SQL expression can also be used in addition to a column from the source entity, in which case the literal expression will be used but within it any @ character will be expanded as a reference to the source column.

See the section on TheonUI for main documentation but here is a rough structural overview.

To add and maintain the desktops and panels used on TheonUI use the Reflection desktop. Mapping the above to the content in that desktop gives us below.

First on the Model panel query for the relevant model the desktops and panels belong to. This is often not necessary as only one model is normally installed into the XSDDB management database.

Use the Desktop panel to create a new desktop or query for an existing desktop. Certain data is mandatory when creating a new desktop such as tag and name. Once done save and re-query for it so panels can be added. The desktop panel has a box for defining the panels that are included on the desktop. It will not be possible to populate this until the actual panels are defined (or if a new desktop is using panels already available). So first define each panel. A desktop must always have at least one panel.

Using the Panel panel add an entry for the panel. Some basic details are mandatory such as the panel tag and name. Choose the base table for the panel using the entity drop-down. A table or view entity type must be chosen here to be the base table for the panel.

The base table of a Panel has already been defined (above). However, a panel can consist of any number of nested tables. When the panel has other tables embedded these are all defined (by relationship) by adding entries in the Connectors box. Add an entry for each table that is joined directly off the base table only. Use the relevant drop-down to set the associated relationship and its type.

The connector type defines how the child table is embedded on the panel. A Forward relationship is used for a "1:1" or "1:N" type association where the child records are tied to the parent (possibly creating multiple instances of the parent). A ForwardNormal connector is used where a "1:1" or "1:N" type association should be folded into the parent record (only one instance of the parent will ever be shown). A ForwardNormal connector is referred to internally as a Grid and commonly presented with a subsidiary "selector" (for example as tabbed layers or in a tabular format, use Selector to set this). With a ForwardNormal connector it is also possible to set whether the selector type will allow addition or deletion of entries. Forward types follow relationships from a primary table to a foreign table. Reverse types on the other hand follow relationships from a foreign table to a primary table. A Reverse relationship is used for a "1:1" or "N:1" type association where the child records are tied to the parent. There will only ever be one instance of the parent. A ReverseLookup relationship is the same except that the parent table is embedded on the panel as a "dynamic drop down" (or lookup field) to allow immediate selection of any record from the parent and "bring it into" the child.

All connector types except ReverseLookup are presented in a visible box on the panel. The connector entry can define the label and position of the box, features of the box (such as default visibility and displayed layout width) and also whether the box should be removed and the connector content simply embedded with its surrounding context in the panel. The ReverseLookup connector type is always embedded and so none of this applies to that. The indexing value if set will put a row N of M statement in the box label.

Once done save changes and then the next level of connectors can be added. While the connectors box supports representing a hierarchy currently with TheonUI each level of the hierarchy must be done and saved before anything on the next (lower) level can be added. Save changes so the connector data is available for fields.

Any number of tables can be connected off the base table. Nor is there any maximum nested depth down from the panel base table imposed. Connectors can be extended breadth wise at any table point in the tree, not just the base table, and again with no specific imposed limit. The only practical constraint in depth or breadth is usability and performance. There is some constraint on nesting connector types. A ReverseLookup connector type cannot be followed by a ForwardNormal connector type or by another ReverseLookup. Although in principle any number of ForwardNormal connector types can be nested, presentation constraints will generally limit actual depth to two (three at the very most).

Next define any presentation elements using the Presenters box. This is necessary when the panel needs to contain nested grid boxes or tabbed layers for representing some or all fields on connected tables (or the base table). However per-field presenters can also be added if needed. Save changes so the presenter data is available for fields. The use of some types of presenter can artificially constrain the types of nested connector, for example fields from a ForwardNormal connector can be shown in a Grid presenter but it is not possible for that presenter to then contain any fields from a further nested ForwardNormal connector (the fields can still be included outside of the presenter but usability will quickly be lost).

Now define the fields using the Fields box. These are the columns from the base table and from each connected table that need to be available on the panel (although potentially hidden from view, such as key columns). Each field entry represents a single table column to be shown, independently by default. Choose the associated table column (entity attribute) via the drop-down - the choice is constrained by the chosen connector or leave connector blank if the column is to be taken from the panel base table. Other facets of fields can be defined, such as position on the panel, visibility (hidden fields can be used for "live" constraints), whether the field is readonly and a title to override the default (the name of the associated attribute).