To optimize the database schema for model elements, specialized database tables can be defined. A specialized database mapping can then be performed instead of the automatic generic database mapping. Without a custom schema configuration, TopLogic uses generic storage for application data.

Schema configuration

The database schema can either be done directly via XML configuration files in a TL-Studio environment, or created in the application itself in the schema editor. This section covers the underlying XML declarations. If a table is created interactively in the schema editor, the files described here are generated automatically.

A schema configuration is created in the folder WEB-INF/kbase and has the name schema <MySchema>Meta.xml, for example, com.top_logic.demo/webapp/WEB-INF/kbase/DemoTypesMeta.xml.

The configuration of the database schema of the finished application is modular. That is, the finished schema is composed of many individual files located in different modules. TopLogic already provides a number of such schema definitions, which can be inherited from the concrete application and enriched with your own declarations. Each individual schema definition file must therefore be included in the application configuration.

For example, the above mentioned configurationDemoTypesMeta.xml from the demo application is referenced in the following way from the application configuration of the demo application, see com.top_logic.demo/webapp/WEB-INF/conf/DemoConf.config.xml:

<application xmlns:config="http://www.top-logic.com/ns/config/6.0">
   <configs>
      <config config:interface="com.top_logic.basic.db.schema.setup.config.ApplicationTypes">
         <type-systems>
            <type-system name="Default">
               <declarations>
                  <declaration resource="webinf://kbase/DemoTypesMeta.xml" />
               </declarations>
            </type-system>
         </type-systems>
      </config>
   </configs>
</application>

Table definition

A normal object table is defined using the tag metaobject. The table name is specified using object_name.

<objectlist xmlns="http://www.top-logic.com/ns/meta/5.8">
  <metaobjects>

    <metaobject abstract="false" object_name="MyTable" super_class="TLObject">
      <attributes>
        ...
      </attributes>
    </metaobject>

  </metaobjects>
</objectlist>

Table definitions in TopLogic can be "abstract". An abstract table is not created in the database, but only serves as a template for other tables. A table T can use another table S as a template by referencing it via super_class="S". In this case, the table T receives all columns from S as preceding columns and additionally its own columns (defined in T ) added at the end.

It is important to note that the name specified in object_name is only the so-called platform-independent table name. From the platform-independent table name used for internal referencing of the table, the database abstraction layer automatically generates a concrete table name that is used in SQL statements. The SQL table name is usually written in uppercase letters and uses underscores as word separators. Depending on the database, the name can also be shortened to meet special restrictions of the database system. The concrete table MY_TABLE is created in the database from the table MyTable defined above. In continuous text, this documentation always uses the platform-independent table name as found in the corresponding schema declaration files. Only when SQL statements are given as examples, the SQL name of the table is used.

Column definition

A value column (with primitive type) is defined using the mo_attribute tag. With mandatory one specifies whether it should be excluded on database level that the empty value can be stored (NOT NULL in SQL). For string columns, db_size specifies the maximum number of characters that the column can hold.

<mo_attribute att_name="myColumn" att_type="String" mandatory="false" db_size="256"/>

A platform-independent description also exists for the value types of table columns. The platform-independent column type is then converted into a concrete SQL type depending on the database system used. This ensures that an application with the same schema definition can run on all database systems supported by TopLogic.

Possible value types for primitive columns are:

• String
• Boolean
• Integer
• Long
• Byte
• Short
• Character
• Float
• Double
• Date
• Time
• Timestamp
• Clob
• Blob

Foreign key columns

A foreign key column (a column whose value is a reference to another object) is defined using the reference tag. The target type is specified via target-type. The foreign key defined in this way can be used to reference all objects stored in the table specified in target-type. It is permitted that an abstract table is specified as the target type. In this case, all those objects can be referenced that are stored in tables that use the specified abstract table as a template (either directly or as a template of the template, etc.).

The following definition creates a foreign key column keyCol, which can be used to reference objects stored in the table OtherTable:

<reference 
   att_name="keyCol"
   target-type="OtherTable"
   mandatory="false" 
   monomorphic="true" 
   deletion-policy="clear-reference" 
   is-container="false" 
   history-type="current" 
   branch-global="false" 
   initial="false"
   by-value="false"
/>

Since the reference is not mandatory, the reference can be empty (point to no other object).

With monomorphic='true', it is specified that all objects that are referenced are in the same table (exactly the specified table OtherTable), even if the specified table was used as a template for other tables. For monomorphic='false', a referenced object can also be in a table that uses target-type (here OtherTable) as a template.

Declaring a reference in a table type creates one or more columns in concrete SQL schema, depending on how the reference is parameterized. In any case, a column is created that can contain the technical identifier of the referenced object, vlg. Technical Columns. The name of this ID column is formed from the specified name of the reference with the suffix _ID.

Deletion strategies

You use deletion-policy to determine what should happen when the referenced target object is deleted. Possible values are

• clear-reference: The reference is set to "empty" (deleted).
• delete-referer: The referencing source object is also deleted.
• stabilise-reference: The reference is retained and points to the last valid version of the referenced target object. For this, history-type must be set to mixed.
• veto: Deletion of the referenced target object is prevented.

With is-container one can determine that the referenced target object is "part of" the referencing source object and therefore should be deleted together with the referencing source object. Thus, a deletion continues through a is-container reference to the target object. This strategy currently only works with history-type=current on the referenced target object.

Scope of the Reference

Whether a reference can point to historical versions of an object is specified via history-type, see Versioning. Possible values are:

• current: The reference can only point to current versions of objects. If the referenced object is deleted, deletion-policy is taken into account.
• historic: The reference only points to stable versions of objects. If it is filled with a current object, after the commit it points to the version of the referenced object that is valid in that commit (the reference is "stabilized").
• mixed: Both current states and stable versions of objects can be referenced in such a reference. In such a reference, stabilise-reference can be used as deletion-policy, see above.

Association tables

There is a shorthand notation for typical association tables with only two foreign keys (source and target).

The following definition creates a link table named myLinkTable, which can be used to store a set or list reference in the model. The template hasWrapperAttValueBaseAssociation must be used for this purpose.

<association super_class="hasWrapperAttValueBaseAssociation" object_name="myLinkTable" />

The table thus defined has two foreign keys source and dest. If the type of reference or the options like mandatory etc. are to be customized, then these special foreign keys can also be declared using <source/> and <dest/>:

<association super_class="hasWrapperAttValueBaseAssociation" object_name="myLinkTable">
   <attributes>
      <source
         override="true"
         target-type="SpecialSourceTable"
      />
      <destination
         override="true"
         target-type="SpecialDestTable"
      />
   </attributes>
</association>

So the above definition is equivalent to:

<association super_class="hasWrapperAttValueBaseAssociation" object_name="myLinkTable">
   <attributes>
      <reference 
         att_name="source"
         override="true"
         target-type="SpecialSourceTable"
      />
      <reference 
         att_name="dest"
         override="true"
         target-type="SpecialDestTable"
      />
   </attributes>
</association>

Indexes

User-defined indexes for object tables can be defined using the mo_index tag. The list index_parts enumerates the attributes and references over which the index is to be created:

<metaobject abstract="false" object_name="MyTable" super_class="TLObject">
    <attributes>
        ...
    </attributes>

    <index>
        <mo_index name="myIndex" unique="false">
            <index-parts>
	        <index_part name="myAttr1"/>
	        <index_part name="myAttr2"/>
            </index-parts>
        </mo_index>
    </index>
</metaobject>

The attribute unique controls whether the database should enforce uniqueness of index entries.

Branches

TopLogic is in principle multi-client capable (see also Multi-client capability). For this purpose, an additional column "BRANCH" is created in the database in each compartment table. If an application does not require this, it can be switched off. Depending on the data volume, the database can be optimized in this way.

To switch off the multi-client capability and to prevent the unnecessary "BRANCH" column in the database, the configuration file top-logic.singleBranch.config.xml can be integrated into the application.