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 offer 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/MariaDB Type	MS SQL Server Type	Oracle Type	PostgreSQL Type	DB2 Type
Text & Encrypted Text	varchar(255)	nvarchar(255)	varchar2(255 char)	varchar(255)	nvarchar(255)
Number (Integer)	integer	int	number(10,0)	int / int4	integer
Number (Decimal)	double precision	double precision	float (126)	float8	double
Boolean	tinyint(1)	tinyint	number(1,0)	bool	smallint
Date	date	datetime	date	date	date
Time	datetime (Since 6.6.1)	datetime	datetime (Since 6.6.1)	timestamp (no timezone)	datetime (Since 6.6.1)
Date and Time	datetime	datetime	timestamp	timestamp (no timezone)	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/MariaDB Type	MS SQL Server Type	Oracle Type	PostgreSQl Type	DB2 Type
string	Text	varchar(255)	nvarchar(255)	varchar2(255 char)	varchar(255)	nvarchar(255)
appian:EncryptedText*	Encrypted Text	varchar(255)	nvarchar(255)	varchar2(255 char)	varchar(255)	nvarchar(255)
boolean	Boolean	tinyint(1)	tinyint	number(1,0)	bool	smallint
decimal	Number (Decimal)	decimal(19,2)	numeric(19,2)	number(19,2)	numeric(19,2)	decimal(19,2)
float	Number (Decimal)	float	float	float	float4	double
double	Number (Decimal)	double precision	double precision	float (126)	float8	double
duration	Text	varchar(255)	nvarchar(255)	varchar2(255 char)	vachar(255)	nvarchar(255)
dateTime	Date and Time	datetime	datetime	timestamp	timestamp	timestamp
time	Time	datetime (Since 6.6.1)	datetime	datetime (Since 6.6.1)	timestamp	datetime (Since 6.6.1)
date	Date	date	datetime	date	date	date
gYearMonth	Text	date	datetime	date	date	date
gYear	Text	date	datetime	date	date	date
gMonthDay	Text	date	datetime	date	date	date
gDay	Text	date	datetime	date	date	date
gMonth	Text	date	datetime	date	date	date
hexBinary	Text	array table (tinyint)	array table (tinyint)	array table (number(3,0))	array table(int2)	array table (smallint)
base64Binary	Text	array table (tinyint)	array table (tinyint)	array table (number(3,0))	array table(int2)	array table (smallint)
anyURI	Text	varchar(255)	nvarchar(255)	varchar2(255 char)	varchar(255)	nvarchar(255)
QName	Text	varchar(255)	nvarchar(255)	varchar2(255 char)	vachar(255)	nvarchar(255)
NOTATION	Text	tinyblob	varbinary(255)	raw(255)	bytea	varchar(255)
normalizedString	Text	varchar(255)	nvarchar(255)	varchar2(255 char)	varchar(255)	nvarchar(255)
token	Text	varchar(255)	nvarchar(255)	varchar2(255 char)	varchar(255)	nvarchar(255)
language	Text	varchar(255)	nvarchar(255)	varchar2(255 char)	varchar(255)	nvarchar(255)
NMTOKEN	Text	varchar(255)	nvarchar(255)	varchar2(255 char)	varchar(255)	nvarchar(255)
NMTOKENS	Text(Multiple)	array table (varchar(255))	array table (nvarchar(255))	array table (varchar2(255 char))	array table (varchar(255))	array table (nvarchar(255))
Name	Text	varchar(255)	nvarchar(255)	varchar2(255 char)	varchar(255)	nvarchar(255)
NCName	Text	varchar(255)	nvarchar(255)	varchar2(255 char)	varchar(255)	nvarchar(255)
ID	Text	varchar(255) not null primary key	nvarchar(255) not null primary key	varchar2(255 char) not null primary key	varchar(255) not null primary key	nvarchar(255) not null primary key
IDREF	Text	varchar(255)	nvarchar(255)	varchar2(255 char)	varchar(255)	nvarchar(255)
IDREFS	Text(Multiple)	array table (varchar(255))	array table (nvarchar(255))	array table (varchar2(255 char))	array table (varchar(255))	array table (nvarchar(255))
ENTITY	Text	varchar(255)	nvarchar(255)	varchar2(255 char)	varchar(255)	nvarchar(255)
ENTITIES	Text(Multiple)	array table (varchar(255))	array table (nvarchar(255))	array table (varchar2(255 char))	array table (varchar(255))	array table nvarchar(255)
int	Number (Integer)	integer	int	number(10,0)	int4	integer
integer	Number (Integer)	decimal(19,2)	numeric(19,2)	number(19,2)	numeric(19,2)	decimal(19,2)
nonNegativeInteger	Number (Integer)	decimal(19,2)	numeric(19,2)	number(19,2)	numeric(19,2)	decimal(19,2)
nonPositiveInteger	Number (Integer)	decimal(19,2)	numeric(19,2)	number(19,2)	numeric(19,2)	decimal(19,2)
negativeInteger	Number (Integer)	decimal(19,2)	numeric(19,2)	number(19,2)	numeric(19,2)	decimal(19,2)
long	Number (Integer)	bigint	numeric(19,0)	number(19,0)	int8	bigint
short	Number (Integer)	smallint	smallint	number(5,0)	int2	smallint
byte	Number (Integer)	tinyint(4)	tinyint	number(3,0)	int2	smallint
unsignedLong	Number (Integer)	decimal(19,2)	numeric(19,2)	number(19,2)	numeric(19,2)	decimal(19,2)
unsignedInt	Number (Integer)	bigint	numeric(19,0)	number(19,0)	int8	bigint
unsignedShort	Number (Integer)	integer	int	number(10,0)	int4	integer
unsignedByte	Number (Integer)	smallint	smallint	number(5,0)	int2	smallint
positiveInteger	Number (Integer)	decimal(19,2)	numeric(19,2)	number(19,2)	numeric(19,2)	decimal(19,2)

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.
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.
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 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.

Update the CDT

To update the CDT in Appian:

1. In the Build view, click the CDT to open it.
2. Configure the CDT properties:
   • In the CDT object view, open the settings menu .
   • Click Properties.
   • Edit the Description.
   • Enable or disable Hide this data from auto-complete and pickers.
   • Click OK.
3. Configure the CDT fields:
   • In the CDT object view, click any of the existing field parameters to edit.
   • Reorder the fields by clicking the order arrows.
   • Remove existing fields by clicking next to the field you want to remove.
   • Click New Field and configure the parameters for the new field.
4. Click Save Changes.

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.
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.
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