Generating Database Tables from Custom Data Types (CDTs)

Overview

One of the most common uses for custom data types (CDTs) is for reading from and writing to a database table or view. CDTs are connected to the database through data stores. The CDT defines the data structure within Appian and how it maps to the database table, while the data store defines how Appian connects with the database.

If you don't have an existing database table, you can use Appian to generate a table for you. While this can be useful if you don't have strict requirements about the table structure, it also makes it very easy to generate an initial version of the table and then make further tweaks in the database. Appian can also update existing tables for you when you update the CDT.

If instead you have an existing database table or view that you need to connect to, see the Mapping CDTs to Pre-defined Database Tables page.

Primary Keys

All data types that map to a database table must have a primary key defined. Without a primary key, the data store will fail to publish and Appian will offter to generate one for you. We do not recommend allowing Appian to generate the primary key column because it cannot be queried or referenced by processes, so it is impossible to update any existing value in the database table. Additionally, your data type must have an explicit primary key field in order to be used as the source for a record type.

Primary key values can be either explicitly set or autogenerated by the database. This will determine the behavior when you are attempting to write values to the database:

  • If the value is autogenerated, writing a CDT value with a null primary key will create a new row in the table and assign a value
  • If the value is not autogenerated, writing a CDT with a null primary key will fail

To set the primary key in your database table, click the primary key icon in the data type designer.

Generated Table

The following sections describe what the generated table and columns will look like based on the column configurations. This will give you an idea of what changes you may need to make to the database table after it has been generated.

Table and Column Names

The table name is generated based on the CDT name and each column name is generated based on the name of the corresponding field. The names of database tables and columns are truncated if they exceed 27 characters in length in order to maintain compatibility with all supported databases.

Long names (longer than 27 characters) are truncated in the following ways:

  • Vowels are removed from the name, in the order of u, o, a, e, then i. Names that begin with a vowel retain that vowel only.
  • The name is split using underscores.
  • The longest segment of the name is then trimmed, one character at a time, until the name is shortened to 27 characters.

If you have two similar long names for columns, it is possible for the two column names to be truncated to the same value. Appian recommends keeping CDT and field names to less than 27 characters when letting Appian generate the table for you. You may also use the @Column(name="columnName") annotation to specify a particular column name before the table is generated.

The casing of table and column names can also differ from your CDT based on your database settings.

Column Types

When Appian generates a database table for you, the column type is determined based on the type of the field. The column type that is used may differ based on whether you configure your CDT using the data type designer or have modified the XSD to use a different field type or to use JPA annotations.

Default Column Types Based on Appian Types

If you're using the data type designer to create your CDT, the column types will correspond to the Appian types listed in the table below. For fields that are CDT types with a One-to-One, One-to-Many, or Many-to-One relationship defined, the type of the column corresponds to the type of the primary key of the nested CDT.

Appian Type MySQL Type MS SQL Server Type Oracle Type DB2 Type
Text varchar(255) nvarchar(255) varchar2(255 char) nvarchar(255)
Encrypted Text varchar(255) nvarchar(255) varchar2(255 char) nvarchar(255)
Number (Integer) integer int number(10,0) integer
Number (Decimal) double precision double precision float (126) double
Boolean tinyint(1) tinyint number(1,0) smallint
Date date datetime date date
Time datetime
(Since 6.6.1)
datetime datetime
(Since 6.6.1)
datetime
(Since 6.6.1)
Date and Time datetime datetime timestamp timestamp

Default Column Types Based on XSD Types

When you create your CDT using the data type designer, each CDT field uses the default XSD type based on the Appian type of the field. If you use an XSD type other than the default, the type of your column will correspond to that XSD type, as described in the table below.

If you want Appian to create a column of a type other than those listed below, you will need to use the @Column(columnDefinition="DATABASE_COLUMN_TYPE") annotation to specify the database column type.

XSD Type Appian Type MySQL Type MS SQL Server Type Oracle Type DB2 Type
string Text varchar(255) nvarchar(255) varchar2(255 char) nvarchar(255)
appian:EncryptedText* Encrypted Text varchar(255) nvarchar(255) varchar2(255 char) nvarchar(255)
boolean Boolean tinyint(1) tinyint number(1,0) smallint
decimal Number (Decimal) decimal(19,2) numeric(19,2) number(19,2) decimal(19,2)
float Number (Decimal) float float float double
double Number (Decimal) double precision double precision float (126) double
duration Text varchar(255) nvarchar(255) varchar2(255 char) nvarchar(255)
dateTime Date and Time datetime datetime timestamp timestamp
time Time datetime
(Since 6.6.1)
datetime datetime
(Since 6.6.1)
datetime
(Since 6.6.1)
date Date date datetime date date
gYearMonth Text date datetime date date
gYear Text date datetime date date
gMonthDay Text date datetime date date
gDay Text date datetime date date
gMonth Text date datetime date date
hexBinary Text array table (tinyint) array table (tinyint) array table (number(3,0)) array table (smallint)
base64Binary Text array table (tinyint) array table (tinyint) array table (number(3,0)) array table (smallint)
anyURI Text varchar(255) nvarchar(255) varchar2(255 char) nvarchar(255)
QName Text varchar(255) nvarchar(255) varchar2(255 char) nvarchar(255)
NOTATION Text tinyblob varbinary(255) raw(255) varchar(255)
normalizedString Text varchar(255) nvarchar(255) varchar2(255 char) nvarchar(255)
token Text varchar(255) nvarchar(255) varchar2(255 char) nvarchar(255)
language Text varchar(255) nvarchar(255) varchar2(255 char) nvarchar(255)
NMTOKEN Text varchar(255) nvarchar(255) varchar2(255 char) nvarchar(255)
NMTOKENS Text(Multiple) array table (varchar(255)) array table (nvarchar(255)) array table (varchar2(255 char)) array table (nvarchar(255))
Name Text varchar(255) nvarchar(255) varchar2(255 char) nvarchar(255)
NCName Text varchar(255) nvarchar(255) varchar2(255 char) nvarchar(255)
ID Text varchar(255) not null primary key nvarchar(255) not null primary key varchar2(255 char) not null primary key nvarchar(255) not null primary key
IDREF Text varchar(255) nvarchar(255) varchar2(255 char) nvarchar(255)
IDREFS Text(Multiple) array table (varchar(255)) array table (nvarchar(255)) array table (varchar2(255 char)) array table (nvarchar(255))
ENTITY Text varchar(255) nvarchar(255) varchar2(255 char) nvarchar(255)
ENTITIES Text(Multiple) array table (varchar(255)) array table (nvarchar(255)) array table (varchar2(255 char)) array table nvarchar(255)
int Number (Integer) integer int number(10,0) integer
integer Number (Integer) decimal(19,2) numeric(19,2) number(19,2) decimal(19,2)
nonNegativeInteger Number (Integer) decimal(19,2) numeric(19,2) number(19,2) decimal(19,2)
nonPositiveInteger Number (Integer) decimal(19,2) numeric(19,2) number(19,2) decimal(19,2)
negativeInteger Number (Integer) decimal(19,2) numeric(19,2) number(19,2) decimal(19,2)
long Number (Integer) bigint numeric(19,0) number(19,0) bigint
short Number (Integer) smallint smallint number(5,0) smallint
byte Number (Integer) tinyint(4) tinyint number(3,0) smallint
unsignedLong Number (Integer) decimal(19,2) numeric(19,2) number(19,2) decimal(19,2)
unsignedInt Number (Integer) bigint numeric(19,0) number(19,0) bigint
unsignedShort Number (Integer) integer int number(10,0) integer
unsignedByte Number (Integer) smallint smallint number(5,0) smallint
positiveInteger Number (Integer) decimal(19,2) numeric(19,2) number(19,2) decimal(19,2)

appian:EncryptedText indicates the Appian Encrypted Text data type, which does not have a correpsonding XSD primitive type, but maps to the same column types as Text.

Column Configurations

Any additional configurations that are configured through the data type designer such as primary key settings, text length, and relationship definitions will be reflected in your database table when it is initially generated. These configurations will also be applied to new columns when they are added to an existing table.

Some configurations, such as text length and whether a primary key value is autogenerated, will not be updated in the database table if they are modified after the table has been generated. You may need to make the corresponding changes in the database table itself.

If you've added additional JPA annotations to your CDT that are not configurable through the data type designer, those will also be reflected on your database table when it is initially generated and when adding new columns. Some annotations may not be reflected when updating existing columns once the table has been generated, so you may need to make the corresponding changes in the database table itself.

Create

To generate a database table using Appian, you can create your CDT using the data type designer and Appian will create a table when you publish the data store. There are two main ways to do this, depending on how you plan to deploy the tables to higher environments:

Create Tables Via Database Script

If you will be creating new tables in higher environments using a script, but want the convenience of Appian generating the initial tables for you:

  1. Create the CDT using the data type designer
  2. Create the corresponding data store entity, verify, and publish the data store. Appian will create the initial database table for you based on the default behavior
  3. Make any changes you need directly to the database table itself
    • If making changes to the table name, column names, or column types, you need to modify the XSD and add the appropriate JPA annotations
  4. Before deploying the table, export the final table definition directly from the database and run that script in the higher environment before importing your application

This will make it easy to create the initial table in the database, but still allow you to control the final database structure.

Create Tables By Publishing the Data Store

If you will be letting Appian create tables for you in each of your environments:

  1. Create the CDT using the data type designer
  2. If the table will require any changes, such as specific table or column names, column types other than the defaults, or other JPA annotations that are not configurable through the data type designer, download the XSD and add the appropriate annotations
  3. Create the corresponding data store entity, verify, and publish the data store. Appian will create the database table for you, respecting any annotations that were added to the XSD
    • Consider configuring your data store to Automatically Update Database Schema to remove manual steps from your application deployment, if it's enabled for your environment

Update

When using Appian to update existing database tables, you can update your CDT using the data type designer and allow Appian to generate a table when you publish the data store. There are two main ways to do this, depending on how you plan to deploy the tables to higher environments.

Remember that each time you update a CDT, all dependent objects are updated automatically to reference the latest version of the data type, including any data stores that are used to connect the CDT to a database. If you do not want Appian to modify the database for you, make sure that the data store does not have Automatically Update Database Schema enabled. See Data Stores page for more information.

Before updating any data type, be sure you understand the impact it will have on objects that reference that data type, including running processes.

Individual fields within a CDT do not display when viewing the dependents for that CDT, so there is no way to reliably know what objects are using each field. Therefore, it can be very difficult to correctly update all objects when you change the name or type of a field, or delete a field. Be sure to thoroughly test your changes in order to ensure that you've updated all the appropriate objects.

Additionally, running processes cannot be updated to use the latest version of a CDT. Therefore, changes to a CDT, its underlying database structure, or related artifacts such as rules and sub-processes may cause the running processes to behave unexpectedly.

Update Tables Via Database Script

If you will be updating tables in higher environments using a script, use the following process:

  1. Update the CDT using the data type designer
  2. Verify the data store and download the DDL script that is generated, then publish the data store
  3. Make any changes you need directly to the database table itself, then update the DDL script to include those changes
  4. Run the DDL script in the higher environment before importing your application to higher environments

Update Tables By Publishing the Data Store

If you will be letting Appian update tables for you in higher environments, use the following process:

  1. Update the CDT using the data type designer
  2. If the table will require any changes, such as specific table or column names, column types other than the defaults, or other JPA annotations that are not configurable through the data type designer, download the XSD and add the appropriate annotations
    • Most changes to existing fields that already map to a column will not have an effect, such as adding new JPA annotations. If you need to make these types of changes, you will need to make them in the database itself.
  3. Verify and publish the data store. Appian will update the database table for you.
    • Consider configuring your data store to Automatically Update Database Schema to remove manual steps from your application deployment, if it's enabled for your environment
FEEDBACK