Database Tables

By default, ADITO cloud systems come with a database of type MariaDB. Usually, each Entity will be connected to a specific database table, via a so-called Record Container.

There are multiple ways to create the required database tables and to insert data into them (e.g., demo data, keywords, etc.):

One way, which is unusual in ADITO, would be to execute the respective MariaDB SQL statements for creating new database tables and their columns or for inserting data - with an external tool or in the ADITO Designer (see button "Execute SQL", to the left of the combobox in the button bar).

Another option is to use ADITO's database editor: In the "Projects" window, navigate to "system" and double-click on the your system's name, e.g., "default" (ADITO cloud server must be running, and a tunnel connection must be established). This will display your system configuration in an editor window, usually in the upper middle of the Designer. Here, double-click on "Data_alias". Then, your database content will appear as a tree structure, and you can view tables and columns as well as add/edit/delete columns via the context menu when right-clicking on table names or column names.

important

Whenever you have modified the structure or configuration of the database by any of the above methods, you need to update the Alias Definition, in order to use the changes in your project.

Liquibase

However, the most common way of creating database tables and columns, and inserting data, is to use the tool "Liquibase": Here, you define the tables and their columns in XML files, and Liquibase can use these files to execute SQL commands that create the tables and columns.

note

Liquibase is an open source tool for database schema change management. It has not been developed by ADITO, but it is integrated into the ADITO Designer via a plugin (see option "Plugins" in the "Tools" menu). You can find a detailed documentation of Liquibase on the developer's web site, see https://www.liquibase.org/. Further information can be found in chapter "Create Liquibase files automatically" of the Designer Manual.

Content of Data_alias folder

In the structure of an ADITO project, the xml files that Liquibase needs all reside under alias > Data_alias (see "Projects" window), or - in modularized projects - in the module-specific alias folders.

Via the context menu of the project folder Data_alias, you can execute the Liquibase commands

• update
• drop all
• drop all and update

The starting point for the Liquibase logic is always a file named changelog.xml on top-level of the Data_alias folder. This file does not contain information for database access, but only references to other files with the same name changelog.xml, further below in the Data_alias folder's sub-structure or in the alias folders of modules.

Here is a generic example:

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
                   xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" 
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.4.xsd">
    <include relativeToChangelogFile="true" file="../Module1_alias/changelog.xml"/>
    <include relativeToChangelogFile="true" file="../Module2_alias/changelog.xml"/>
    <include relativeToChangelogFile="true" file="../Module3_alias/changelog.xml"/>
    <include relativeToChangelogFile="true" file="../Module4_alias/changelog.xml"/>
    <include relativeToChangelogFile="true" file="../Module5_alias/changelog.xml"/>
 </databaseChangeLog>

changelog.xml

These referenced changelog files, in turn, include references either again to changelog files (building a deeper sub-structure) or to files (of arbitrary name) including so-called "changesets", which control the actual database access of all kinds, e.g., for

• creating new tables
• creating new columns
• inserting data into existing tables
• updating existing data
• etc.

note

Don't be confused by the naming: All Liquibase XML files used in ADITO are, technically, "changelogs" - see the surrounding XML tag databaseChangeLog in every XML file. However, in ADITO's colloquial language, it is usual to call a file "changelog" if it only includes references to other XML files. (On top level, they are literally named changelog.xml, but on deeper levels, they can have self-explanatory names, like init.xml.)
In contrast, all XML files including "changeSet" tags (resulting in SQL commands, like create or insert) are called "changesets". However, they are seldomly named changeset.xml, but they have self-explanatory names like init_create_address.xml, or 4.2.0_2039650_addTimAdmin.xml.

Via the Data_alias' context menu, you can create new folders, changelog files, and changeset files:

Finally, each XXX_Data_alias project folder will have a more or less deep structure of changelogs, changesets, and folders. Here is an example from the ADITO xRM project's module "Contact Management":

Changeset examples

Here is a generic example of a changeset that creates a new table:

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
    <changeSet author="j.smith" id="23533445-0d3d-499c-aa98-cf37ca4793c1">
        <createTable tableName="MYTABLE">
            <column name="MYTABLEID" type="CHAR(36)">
                <constraints primaryKey="true" primaryKeyName="PK_MYTABLE_MYTABLEID"/>
            </column>
            <column name="MYCOLUMN1" type="VARCHAR(36)"/>     
            <column name="MYCOLUMN2" type="VARCHAR(20)"/>
            <column name="MYCOLUMN3" type="DATE"/>  
            <column name="MYCOLUMN4" type="DECIMAL(10,2)"/>
        </createTable>
    </changeSet>
</databaseChangeLog>

create_mytable.xml

And here is a generic example of a changeset that inserts datasets into an existing table:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd">
  <changeSet author="j.smith" id="29368dec-2195-48d1-9b81-b57973a833c1">
    <insert tableName="MYTABLE">
      <column name="MYTABLEID" value="1d194e2f-fa81-4b9c-95ba-a245d87de3bc" />
      <column name="MYCOLUMN1" value="MYVALUE11" />
      <column name="MYCOLUMN2" value="MYVALUE21" />
      <column name="MYCOLUMN3" value="MYVALUE31" />
      <column name="MYCOLUMN4" value="MYVALUE41" />
    </insert>
    <insert tableName="MYTABLE">
      <column name="MYTABLEID" value="37631246-0c1e-4618-a280-c1731211b9f3" />
      <column name="MYCOLUMN1" value="MYVALUE12" />
      <column name="MYCOLUMN2" value="MYVALUE22" />
      <column name="MYCOLUMN3" value="MYVALUE32" />
      <column name="MYCOLUMN4" value="MYVALUE42" />
    </insert>
  </changeSet>
</databaseChangeLog>

insert_mytable.xml

Liquibase update

If you have added or modified one or multiple XML files, you need to execute a subsequent Liquibase update, in order to get the changes into your database.

alias > Data_alias > (context menu:) Liquibase > Update... (This option is only available, if a database connection exists.)

This will open a dialog, in which you select your database connection; make sure you select the "data database", not the "system database". Confirm with "ok". If everything has been configured correctly, you will, after a few seconds, read "Update successful!" in a small message window (called "Balloon") in the lower right corner of the Designer.

warning

The Liquibase update dialog shows the flag "example", in order to decide, whether or not example data (contacts, companies, Activities, etc.) should be inserted in your database. Please be aware: Checking checkbox "example" will result in a complete loss of any productive data - even if you only choose option "Liquibase - update" (without "drop all"). Therefore, checkbox "example" should NEVER be checked in a productive system or whenever you have entered your own data that must not be deleted.

Liquibase errors

If one of the liquibase XML files contains an error (e.g., a typo), the update process stops at this file, and the following liquibase files will not be executed. There is no rollback in this case.

If you choose "Drop All & Update...", then "Drop All" and "Update" are separate commands, which are executed subsequently. If, e.g., "Drop All" has been executed, but the first "Update" XML file fails, then the database is empty.

A single changeset is always executed as database transaction, i.e., if, e.g., in a table creation file, the third column has been misconfigured, then the table will not be created at all.

Automatic XML file generation

As for Liquibase XML files that create tables or insert data, you have an alternative to writing these files from scratch: You can first create the tables via the ADITO Designer's database editor and fill them with datasets via the Client. Then select option "Generate Changelog..." (actually, it could also be named "Generate changesets") from the context menu of a database table:

This opens the following dialog:

As you can see in line "Types", you can select option

• "Structure": This means you get a changeset for creating the selected table.
• "Data": This means you get, in the same file, a changeset with insert-related tags for each dataset of this table.

Depending on the table's configuration, further changesets might be added, e.g., for creating database indices.

In the above example, the result would be something like this (abbreviated):

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd">
  <changeSet author="j.smith" id="ab77746e-3086-430b-9b33-7f71e37afa4c">
    <preConditions onFail="MARK_RAN">
      <not>
        <tableExists tableName="person" />
      </not>
    </preConditions>
    <createTable tableName="person">
      <column name="PERSONID" type="CHAR(36)">
        <constraints nullable="false" primaryKey="true" />
      </column>
      <column defaultValueComputed="NULL" name="DATEOFBIRTH" type="date" />
      <column name="FIRSTNAME" type="VARCHAR(50)" />
      <column name="MIDDLENAME" type="VARCHAR(50)" />
      <column name="LASTNAME" type="VARCHAR(50)" />
      <column name="GENDER" type="VARCHAR(36)" />
      <column name="SALUTATION" type="VARCHAR(16)" />
      <column name="TITLE" type="VARCHAR(50)" />
      <column name="TITLESUFFIX" type="VARCHAR(50)" />
      <column name="USER_NEW" type="VARCHAR(50)" />
      <column name="DATE_NEW" type="datetime">
        <constraints nullable="false" />
      </column>
      <column name="USER_EDIT" type="VARCHAR(50)" />
      <column defaultValueComputed="NULL" name="DATE_EDIT" type="datetime" />
      <column defaultValueComputed="NULL" name="PICTURE" type="LONGBLOB" />
    </createTable>
  </changeSet>
  <changeSet author="j.smith" id="cd22246e-3086-430b-9b33-7f71e37afa4c">
    <insert tableName="person">
      <column name="PERSONID" value="0061c866-98a9-49a0-8fae-040221d02c4b" />
      <column name="DATEOFBIRTH" />
      <column name="FIRSTNAME" value="Alan" />
      <column name="MIDDLENAME" />
      <column name="LASTNAME" value="Miller" />
      <column name="GENDER" />
      <column name="SALUTATION" value="Mr." />
      <column name="TITLE" />
      <column name="TITLESUFFIX" />
      <column name="USER_NEW" value="Admin" />
      <column name="DATE_NEW" valueDate="2024-09-27T09:54:22" />
      <column name="USER_EDIT" />
      <column name="DATE_EDIT" />
      <column name="PICTURE" />
    </insert>
    <insert tableName="person">
      <column name="PERSONID" value="05e16612-3015-4381-b3da-cd1dc23a0fd7" />
      <column name="DATEOFBIRTH" />
      <column name="FIRSTNAME" value="Betty" />
      <column name="MIDDLENAME" />
      <column name="LASTNAME" value="Clark" />
      <column name="GENDER" value="f" />
      <column name="SALUTATION" value="Mrs." />
      <column name="TITLE" value="Dr." />
      <column name="TITLESUFFIX" />
      <column name="USER_NEW" value="Fred Myers" />
      <column name="DATE_NEW" valueDate="2024-08-01T13:30:27" />
      <column name="USER_EDIT" />
      <column name="DATE_EDIT" />
      <column name="PICTURE" />
    </insert>
  </changeSet>
  <changeSet author="j.smith" id="ff44446e-3086-430b-9b33-7f71e37afa4c">
    <createIndex indexName="IDX_PERSON_FIRSTNAME" tableName="person">
      <column name="FIRSTNAME" />
      <column name="PERSONID" />
    </createIndex>
  </changeSet>
  <changeSet author="j.smith" id="bc55546e-3086-430b-9b33-7f71e37afa4c">
    <createIndex indexName="IDX_PERSON_LASTNAME" tableName="person">
      <column name="LASTNAME" />
      <column name="PERSONID" />
    </createIndex>
  </changeSet>
</databaseChangeLog>

note

It can happen that the id values (UUIDs) are sometimes the same (i.e., not unique). In these case, you need to rework the ids by yourself, to make them different from each other; you may generate new UUIDs via the Designer's option "Tools" > Generate UUID. This will store a new UUID in your Windows clipboard, so you can paste it in the XML file.

tip

In practice, you will work with separate files for creating tables and inserting datasets. Therefore, if you need both, you should simply execute the "Generate Changelog" dialog twice - one time with option "Structure" checked, and one time with option "Data" checked. Then you get 2 separate files.

Flexible column data types

One of Liquibase's benefits is that it can handle multiple database systems, which have different column data types.

When Liquibase is executed, Liquibase's data types (as included in the XML files) are automatically mapped to data types proper to the specific database engine connected to the ADITO project. For example, Liquibase's data type NCLOB (used for very large text fields) remains a NCLOB for Apache Derby databases, but is mapped to a LONGTEXT for MariaDB and MySQL, while in MicrosoftSQL it will be a NVARCHAR(MAX).

When customizing ADITO, you should always prefer the target data types of these Liquibase mappings, even if you do not use Liquibase itself. You can find a list of the preferable data types, according to database system, in the article "Preferable data types", available in this article of the ADITO Knowledge Base. (To read this article, you need access to the ADITO Service Client.)

Indices

As you can see in the above example, some of ADITO's database table columns have an index set. Setting appropriate indices is very important for the system's performance - find further information in the chapter Performance Optimization.

Constraints

In the database of the ADITO xRM project, constraints are usually only set for the primary key, and in very few further cases (e.g., a "not null" constraints for all columns refering to an EntityField of contentType "Boolean"). In particular, there are no foreign key constraints on database level. If you want to make sure that a specific EntityField is not empty, you usually set its property mandatory to true (rather than setting a "not null" constraint on its corresponding database column).

This has multiple reasons, e.g., it simplifies the task of dropping and re-creating database tables, and it grants more flexibility when creating interdependent datasets.

However, in special cases, it can be useful to set a database constraint - but still keep in mind the above aspects.