BDS Database Model Synchronization

This is part of a series of articles on the Business Data Storage. Please, refer to the main article Business Data Storage (BDS) for complete information.

The Business Data Storage provides the FNZ Studio user with the ability to synchronize a set of selected FNZ Studio Data Classes to a Relational Database. This means that the data model designed through Data Classes in FNZ Studio Composition is converted to a relational format (tables, columns, primary keys, etc) that can be stored in the Database.

This article explains in detail how the "conversion" from the FNZ Studio model to the Database model is done and how the Database (from now on, DB) synchronization is executed.

When synchronizing a given Data Class to the DB, all its properties are converted to a corresponding DB representation. In the context of the Business Data Storage (from now on, BDS), Data Class properties can be classified in 4 categories:

Simple Value (SV): property whose type is one of the supported primitive types, e.g. String.
Collection of Simple Values (CSV): property whose type is Indexed or Named Collection of one of the supported primitive types, e.g. Indexed String.
Complex Value (CV): property whose type is another Data Class e.g. Client.
Collection of Complex Values (CCV): property whose type is Indexed or Named Collection of another Data Class e.g. Indexed Client. Properties of the last two types (Complex Value and Collection of Complex Values) represent "relationships" between two Data Classes and are modeled as such in the DB.

FNZ Studio-to-Database Model Conversion

Main Table

Each Data Class that is stored with the BDS corresponds to a DB table. Tables of this type are referred to as main tables. The main table of class MyClass contains one row for each instance of the Data Class MyClass that is saved with the BDS.

By default, each main table has a column called persistence_id which contains the BDS-specific persistence ID generated by the BDS for each saved Data Class instance. This column is the primary key (PK) of the main table.

Consider the following information:

The persistence ID is a number (BIGINT JDBC type).
The persistence ID is unique across all BDS tables, meaning that there cannot be two Data Class instances with the same persistence ID, even if they are of different types.

Note: This is obtained by defining a Sequence object on the DB and then using the next value of the sequence as default value of the persistence_id column in all main tables.

ExamplePerson Data Class (no properties)

person table (main table):

persistence_id (PK)

Simple Value (SV) Properties

Each Simple Value property of a stored Data Class corresponds to a column in the main table of the Data Class.

ExamplePerson Data Class

String name
Date dateOfBirth

person table (main table):

persistence_id (PK)	name	dateOfBirth

Supported Primitive Types

Only a subset of the Primitive Types available in FNZ Studio are supported by the BDS. These are:

String
Long
Integer
Float
Double
BigInteger
BigDecimal
Boolean
Date
Money
ClusterFile
LocalFile

If a Data Class contains a SV property whose type is not among supported Primitive Types, it is not possible to store the Data Class with the BDS (see the Model Verification Algorithm section).

Note that type Any is not among supported Primitive Types.

The correspondence between supported Primitive Types and Database column types is reported in the following table:

Primitive Type	JDBC Type	PostgreSQL column type	Oracle SQL column type	Microsoft SQL Server column type	Notes
BigDecimal	VARCHAR	TEXT	VARCHAR2(4000)	VARCHAR(8000)	-
BigInteger	VARCHAR	TEXT	VARCHAR2(4000)	VARCHAR(8000)	-
Boolean	BOOLEAN	BOOLEAN	CHAR(1)	BIT	-
ClusterFile	VARCHAR	TEXT	VARCHAR2(4000)	VARCHAR(8000)	The DB column contains the file path
Date	TIMESTAMP	TIMESTAMP	TIMESTAMP	DATETIME2	-
Double	DOUBLE	DOUBLE PRECISION	FLOAT	FLOAT	-
Float	REAL	REAL	FLOAT(23)	REAL	-
Integer	INTEGER	INT	NUMBER(10,0)	INT	-
LocalFile	VARCHAR	TEXT	VARCHAR2(4000)	VARCHAR(8000)	The DB column contains the file path
Long	BIGINT	BIGINT	NUMBER(19,0)	BIGINT	-
Money	VARCHAR	TEXT	VARCHAR2(4000)	VARCHAR(8000)	The DB column contains the value in the format currency + " " + amount e.g. CHF 6000
String	VARCHAR	TEXT	VARCHAR2(4000)	VARCHAR(8000)	Different behavior for Oracle and Microsoft SQL Server (see note below*).

For Oracle: if a given String SV property can contain text longer than 4000 characters, the Database administrator can alter the column type directly on the DB to a size up to 32000 characters or to a CLOB (please refer to Oracle documentation). For Microsoft SQL Server: if a given String SV property can contain text longer than 8000 characters, the Database administrator can alter the column type directly on the DB and set it to VARCHAR(max) (please refer to the Microsoft SQL Server documentation).

Collection of Simple Values (CSV) Properties

Each Collection of Simple Values property in a stored Data Class corresponds to a dedicated table in the DB. Tables of this type are referred to as collection tables.

The collection table corresponding to the property MyProperty of Data Class MyDataClass contains all values found inside MyProperty in each of the stored instances of MyDataClass.

As for SV properties, only a subset of Primitive Types is supported. See the Supported Primitive Types section for a full list.

Each collection table contains exactly 4 columns:

source_id: the persistence ID of the Data Class instance that contains the collection ("source" Data Class instance). The JDBC type of the column is BIGINT.
source_tbl: the name of the table where the "source" Data Class instance is located. This is required due to inheritance (see the Inheritance section for more details).The JDBC type of the column is VARCHAR.
indexed_key (for Indexed Collections) / named_key (for Named Collections): the index or key of the element inside the collection. The JDBC type of the column is INT for indexed_key and VARCHAR for named_key.
value: the value of the element of the collection. The JDBC type of the column depends on the type of the collection, according to the table in the Supported Primitive Types section. For example, for a collection of type Indexed Date, the JDBC type of the column is TIMESTAMP.

The primary key of collection tables is composed by the columns source_id and indexed_key/named_key. An Index is defined on the source_id column to speed up data loading.

Note that no foreign key is defined on this table. This is due to inheritance (see the Inheritance section for more details).

Additionally, each collection of Simple Values in a stored Data Class corresponds to a column in the Data Class main table.

This column contains a boolean flag which indicates whether the collection is null or not. This allows distinguishing null collections from empty collections (no elements).

The JDBC type of the column is BOOLEAN. The name of this column is always prefixed with is_null_. For more details about the name, please see the Table and Column Names section.

Example

Person Data Class

String name
Indexed String countries

person table (main table):

persistence_id (PK)	name	is_null_countries
123456	Brian	false

person_countries table (collection table):

source_id (PK)	source_tbl	indexed_key (PK)	value
123456	person	1	US
123456	person	2	null
123456	person	3	Switzerland

Complex Value (CV) Properties

A property of a complex type establishes a relationship between the Data Class where the property is defined (also called "source" of the relationship) and the Data Class used as type of the property ("target" of the relationship).

The cardinality of this relationship is 'one to many', where the 'many' side of the relationship is on the source. As an example, let's consider a Data Class Client with a property address of type Address. Client is the source of the relationship, while Address is the target. For a given Data Class instance of type Client, there is only one Address, while the same Address Data Class instance can be put in relationship with multiple Client Data Class instances. For this reason, this type of property does not require a dedicated table to contain instances of this relationship. Each CV property corresponds to two columns in the main table of the Data Class:

a first one that contains the persistence ID of the Data Class instance target of the relationship. For the name of this column, see the Table and Column Names section. The JDBC type of the column is BIGINT.
a second one that contains the name of the table where the target Data Class instance is located. This is required due to inheritance (see the Inheritance section for more details). The name of this column is always suffixed with _tbl. For more details about the name, please see the Table and Column Names section. The JDBC type of the column is VARCHAR.

Note that no foreign key is defined. This is due to inheritance (see the Inheritance section for more details).

ExamplePerson Data Class

String name
Address address

Address Data Class

String city

person table (main table):

persistence_id (PK)	name	address	address_tbl
123456	Brian	789012	address

address table (main table):

persistence_id (PK)	city
789012	New York

Collection of Complex Values (CCV) Properties

As for CV properties, Collection of Complex Values properties establish a relationship between the Data Class where the property is defined (also called "source" of the relationship) and the Data Class used as the property type ("target" of the relationship).

The cardinality of this relationship is many to many.

As an example, let's consider a Data Class Client with a property addresses of type Indexed Address. Client is the source of the relationship, while Address is the target. For a given Data Class instance of type Client, there can be multiple addresses (stored inside the addresses collection). Also the same Address Data Class instance can be put in relationship with multiple Client Data Class instances (added to their addresses collection). For this reason, this type of property requires a dedicated table that contains instances of this relationship. Tables of this type are referred to as bridge tables.

The bridge table corresponding to the property MyProperty of Data Class MyDataClass contains all the relationships found inside the property MyProperty in each of the stored Data Class instances of type MyDataClass.

Each bridge table contains exactly 5 columns:

source_id: the persistence ID of the Data Class instance that contains the collection ("source" Data Class instance). The JDBC type of the column is BIGINT.
source_tbl: the name of the table where the "source" Data Class instance is located. This is required due to inheritance (see the Inheritance section for more details).The JDBC type of the column is VARCHAR.
target_id: the persistence ID of the Data Class instance that is contained in the collection ("target" Data Class instance). The JDBC type of the column is BIGINT.
target_tbl: the name of the table where the "target" Data Class instance is located. This is required due to inheritance (see the Inheritance section for more details).The JDBC type of the column is VARCHAR.
indexed_key (for Indexed collections) / named_key (for Named collections): the index or key of the element inside the collection. The JDBC type of the column is INT for indexed_key and VARCHAR for named_key.

The primary key of bridge tables is composed by the columns source_id and indexed_key/named_key.

Note that no foreign key is defined. This is due to inheritance (see the Inheritance section for more details).

Additionally, each collection of complex values in a stored Data Class corresponds to a column in the Data Class main table. This column contains a boolean flag that indicates whether the collection is null or not. This allows distinguishing null collections from empty collections (no elements). The JDBC type of the column is BOOLEAN. The name of this column is always prefixed with is_null_. For more details about column names, please see the Table and Column Names section.

ExamplePerson DataClass

String name
Indexed Address addresses

Address DataClass

String city

person table (main table):

persistence_id (PK)	name	is_null_addresses
123456	Brian	false

address table (main table):

persistence_id (PK)	city
789012	New York
135789	Zurich

person_addresses table (bridge table)

source_id (PK)	source_tbl	target_id	target_tbl	indexed_key (PK)
123456	person	789012	address	1
123456	person	135789	address	2
123456	person	null	null	3

Note that a bridge table is added for each CCV property in a stored Data Class.

For example, if two Data Classes point at each other and each of them contains a property, two bridge tables are needed. This is required since there is no guarantee that the two properties represent the same relationship.

Example

Person DataClass

String name
Indexed Address workAddresses

Address DataClass

String city
Indexed Person inhabitants

person table (main table)

persistence_id (PK)	name	is_null_workaddresses
12	Brian	false
34	Mark	false

address table (main table)

persistence_id (PK)	city	is_null_inhabitants
56	San Francisco	false
78	New York	false

person_workaddresses table (bridge table)

source_id (PK)	source_tbl	target_id	target_tbl	indexed_key (PK)
12	person	56	address	1

address_inhabitants table (bridge table)

source_id (PK)	source_tbl	target_id	target_tbl	indexed_key (PK)
56	address	34	person	1

In this example Person 'Brian' has 'San Francisco' as his work address. However, the 'San Francisco' address has Person 'Mark' as an inhabitant.

Two bridge tables are also needed if a stored Data Class has two collections pointing to the same target Data Class.

Inheritance

In FNZ Studio, it is possible to define inheritance between Data Classes, so that a child Data Class inherits properties (and methods) from one or more parent Data Classes.

However, in Relational Databases there is no concept of inheritance between tables, therefore different approaches have been theorized to represent this type of model. Among the different possible approaches to represent inheritance in relational DBs, the BDS uses the table-per-class approach.

Every Data Class in the hierarchy has its own separate table. A Data Class instance is stored in exactly one row in the specific table for its type. That specific table contains columns for all the properties of the Data Class, including any inherited property. This implies that each sibling in an inheritance hierarchy has its own copy of the properties it inherits from its parent Data Class(es).

This choice is due to the fact that FNZ Studio supports multiple inheritance in the Data Class model (one child Data Class can have multiple parent Data Classes). One implication of the choice of the table-per-class approach is that it is not possible to use foreign keys in case of relationships, since foreign keys can only reference one specific table.

The following paragraphs explain this approach in more detail for the different property types.

Note: it is only possible to store one given Data Class to the BDS if all the parents of that Data Class are also stored.

Main Table, Simple Value (SV) Properties and Inheritance

Given a Data Class hierarchy consisting of an arbitrary number of Data Classes and inheritance levels, the DB contains a dedicated main table for each Data Class in the hierarchy.

Each Data Class instance is saved only in the main table of its specific runtime type. In turn, each SV property that is inherited from a parent Data Class has a corresponding column in both the parent and the child Data Class.

Therefore, the main table contains columns representing all the SV properties of the Data Class, including the inherited ones, at any level. The name of the column corresponding to a given SV property is always exactly the same in the parent and the child Data Class.

ExamplePerson DataClass

String name
String surname

Employee Data Class inherits from Person Data Class

String department

person table (main table)

persistence_id (PK)	name	surname
123456	Brian	May

employee table (main table)

persistence_id (PK)	name	surname	department
789012	Mark	Green	logistics

In this example, 'Mark' is an instance of the Data Class Employee, therefore it is saved in the table employee. He is also a Person, but the data is not saved in the table person as well.

'Brian' instead is an instance of the Data Class Person, so it is saved in the table person.

Collection of Simple Values (CSV) Properties and Inheritance

When a property of CSV type is inherited from a parent to a child Data Class, the child Data Class uses the same collection table as the parent. In this case, therefore, there is not a dedicated collection table that represents the CSV property on the child. All data goes into the collection table of the parent.

In other words, the collection table is only created at the level where the CSV property is defined.

Since the collection table is 'shared' (it can contain collection entries of multiple Data Classes) there cannot be a foreign key constraint on the source_id column.

This also explains the purpose of the source_tbl column in the collection table. Since the table can contain entries sourced from multiple Data Classes, it is necessary to distinguish them to be able to query information correctly.

The is_null_ column is instead added to all main tables at all levels in the hierarchy.

Example

Person Data Class

String name
String surname
Indexed String countries

Employee Data Class inherits from Person Data Class

String department

person table (main table):

persistence_id (PK)	name	surnme	is_null_countries
123456	Brian	May	false

employee table (main table):

persistence_id (PK)	name	surnme	is_null_countries	department
789012	Mark	Green	false	logistics

person_countries table (collection table, used by both Person and Employee)

source_id (PK)	source_tbl	indexed_key (PK)	value
123456	person	1	US
789012	employee	1	Canada

Complex Value (CV) Properties and Inheritance

Each inherited CV property corresponds to two columns (as described in the FNZ Studio-to-Database Model Conversion) in the main tables of both the parent and child Data Class.

The name of the two columns is always exactly the same in the parent and the child Data Class.

If the target Data Class of a CV property has children Data Classes, the two columns in the main table of the source Data Class can point to either the parent or the child Data Class based on the type of the target Data Class instance.

Since the columns can point to different tables, there cannot be a foreign key constraint on them.

This also explains the purpose of the _tbl column in the main table. Since the columns can contain references to multiple Data Classes, it is necessary to distinguish them in order to be able to query the information correctly.

ExamplePerson DataClass

String name
Document identification

Employee Data Class inherits from Person Data Class

String department

Document Data Class

String title

OnlineDocument Data Class inherits from Document Data Class

String link

person table (main table):

persistence_id (PK)	name	document	document_tbl
12	Brian	56	document

employee table (main table)

persistence_id (PK)	name	document	document_tbl	department
34	Mark	78	onlinedocument	logistics

document table (main table)

persistence_id (PK)	title
56	Passport

onlinedocument table (main table)

persistence_id (PK)	title	link
78	Driver license	www.google.com

Collection of Complex Values (CCV) Properties and Inheritance

When a property of CCV type is inherited from a parent to a child Data Class, the child Data Class uses the same bridge table as the parent.

In this case, therefore, there is not a dedicated bridge table that represents the CCV property on the child. All data goes into the bridge table of the parent. In other words, the bridge table is only created at the level where the CCV property is defined.

Since the bridge table is 'shared' (it can contain relationship entries of multiple Data Classes), there cannot be a foreign key constraint on the source_id column. This also explains the purpose of the source_tbl column in the bridge table. Since the table can contain entries sourced from multiple Data Classes, it is necessary to distinguish them in order to be able to query information correctly.

If the target Data Class of a CCV property has children Data Classes, the bridge table can contain references to target instances of either the parent target type or any of its children. Therefore there cannot be a foreign key constraint on the target_id column. This also explains the purpose of the target_tbl column in the bridge table. Since the table can contain references to multiple target Data Classes, it is necessary to distinguish them to be able to query the information correctly.

The is_null_ column is added to all the main tables of source Data Classes at all levels in the hierarchy.

ExamplePerson Data Class

String name
Indexed Document documents

Employee Data Class inherits from Person Data Class

String department

Document Data Class

String title

OnlineDocument Data Class inherits from Document Data Class

String link

person table (main table)

persistence_id (PK)	name	is_null_documents
12	Brian	false

employee table (main table)

persistence_id (PK)	name	is_null_documents	department
34	Mark	false	logistics

document table (main table)

persistence_id (PK)	title
56	Passport

onlinedocument table (main table)

persistence_id (PK)	title	link
78	Drivers license	www.google.com

person_documents table (bridge table)

source_id (PK)	source_tbl	target_id	target_tbl	indexed_key (PK)
12	person	56	document	1
34	employee	78	onlinedocument	1

Table and Column Names

This section describes the logic used by the BDS to generate names for all Database tables and columns. In general, table and column names are derived from Data Class IDs and property IDs.

This derivation is not always straightforward due to the following challenges:

the restrictions on table and column names in the DB are in general stricter than the ones on Data Class IDs in FNZ Studio:
- the maximum allowed length of table and column names in the DB is usually shorter than the length of FNZ Studio Data Class and property IDs.
- there are reserved words and special characters in the DB that cannot be used as table and column names but that are instead allowed in FNZ Studio as Data Class and property IDs.
in FNZ Studio it is possible to refactor the ID of a Data Class, but changing table names 'on the fly' on the DB might result in breaking compatibility with existing scripts and processes that refer to the old table name.

For these reasons, there cannot always be an exact correspondence between Data Class/property IDs and table/column names. The BDS provides a Script Function to perform the 'translation' between the two (see the Script Functions article for more details).

Table Names

Table names are obtained by concatenating the package ID, Data Class ID and property name (for bridge and collection tables): packageId_dataClassId[_propertyName].

Example

KYC:Client DataClass

Indexed KYC:Address addresses
Indexed Integer numbers

results in the following table names:

main table: kyc_client
bridge table for addresses property: kyc_client_addresses
collection table for numbers property: kyc_client_numbers

The following rules are applied to generate table names:

The Package separator ":" is replaced by "_"
The character "_" is also used to separate the Data Class ID from the property name (for bridge and collection tables)
Any special character that is not allowed by the DB is removed (for example "." is allowed in Data Class IDs but not as a table name in PostgreSQL)
The table name is always lowercase
If the resulting table name exceeds the maximum table name length allowed by the DB, the following table name shortening algorithm is applied
- a fixed part of the maximum table name length is assigned to each part of the table name (see the Table name shortening in PostgreSQL and Table Name Shortening in Oracle and Microsoft SQL Server sections below for more details).
  - 1/5 for the Package name, 2/5 for the Data Class name and 2/5 (+ any rounding) for the property name
- for the part(s) to be shortened, at least 3 characters are preserved for each word, where a word is defined as an uppercase letter followed by lowercase letters or numbers
- if the resulting table name exceeds the maximum table name length allowed and every word has already been shortened, the name is shortened by removing characters starting from the right
If the resulting table name is a 'reserved' keyword in the DB or it is the same as an already existing table name, a "_" followed by a progressive number is added at the end of the name
- if the added "" plus progressive number combination exceeds the maximum table name length allowed, the name is shortened by removing characters starting from the right before adding the "" plus progressive number.

Table Name Shortening in PostgreSQL

For PostgreSQL, the default maximum length allowed for a table name is 63 characters. Out of the 63 characters:

2 are reserved for the two "_" separators that could be included in the table name according to the Table Name rules above.
61 are available for the name.

Out of the 61 characters for the name:

1/5 of the 61 characters (12) are assigned to the Package ID
2/5 of the 61 characters (24) are assigned to the Data Class ID
2/5 of the 61 characters (25, including rounding) are assigned to the property name

Therefore, any Package ID longer than 12 characters, any Data Class ID longer than 36 characters and any property name longer than 25 characters is shortened according to algorithm described in the Table Name section above.

Example

CustomPackageName:OneVeryLongDataclassNameToBeShortened DataClass

Indexed String thisPropertyHasANameWhichIsTooLong

Shortening algorithm:

CustomPackageName has 17 characters but only 12 are available for the Package ID. Therefore, it is shortened by keeping only 3 characters for each word starting from the last one until it fits: custompacnam
OneVeryLongDataclassNameToBeShortened has 37 characters but only 24 are available for the Data Class ID. Therefore, it is shortened by keeping only 3 characters for each word starting from the last one until it fits: oneverylongdatnamtobesho
thisPropertyHasANameWhichIsTooLong has 34 characters but only 25 are available for the property name. Therefore, it is shortened by keeping only 3 characters for each word starting from the last one until it fits: thisprohasanamwhiistoolon

Resulting table names:

main table: custompacnam_oneverylongdatnamtobesho
collection table: custompacnam_oneverylongdatnamtobesho_thisprohasanamwhiistoolon

Table Name Shortening in Oracle and Microsoft SQL Server

For Oracle and Microsoft SQL Server, the default maximum length allowed for a table name is 128 characters. Out of the 128 characters:

2 are reserved for the two "_" separators that could be included in the table name according to the Table Names section above.
126 are available for the name.

Out of the 126 characters for the name:

1/5 of the 126 characters (25) are assigned to the Package ID.
2/5 of the 126 characters (50) are assigned to the Data Class ID.
2/5 of the 126 characters (51, including rounding) are assigned to the property name.

Therefore, any Package ID longer than 25 characters, any Data Class ID longer than 50 characters, and any property name longer than 51 characters are shortened according to the algorithm described in the Table Names section above.

Note that defining a fixed amount of characters available for Package ID, Data Class ID and property name ensures consistency in the way Package IDs and Data Class IDs are shortened (assuming the same Database Management System with the same configuration is always used):
In other words, all main tables, collections tables and bridge tables belonging to the same Package have the same Package prefix in the name.
Moreover, the main table and all bridge tables and collection tables belonging to the same Data Class have the same Data Class prefix in the name.

Table Names and Data Class ID Refactoring

In FNZ Studio Composition, it is possible to refactor the ID of a Data Class and move FNZ Studio Data Classes from one Package to another (which has the same effect as refactoring the Data Class ID). These operations do not result in changes to the respective tables in the DB. In fact, any such 'on the fly' change to table names could result in breaking compatibility with existing scripts and processes which refer to the old table name. Keep this in mind when considering refactoring a Data Class that is already stored with the BDS.

Example

Create class KYC:Client
Store KYC:Client in table kyc_client
Move KYC:Client to UTILS:Client
The table name for UTILS:Client is still kyc_client

Note: if any refactoring is performed while still in the development environment, no processes are known to be impacted if a table name is changed. In this situation, it is desirable to align the table name to the new Data Class name after refactoring. To achieve this, remove the Data Class from the BDS storage and then add it back. This regenerates the table name according to the table name generation algorithm described above.

Column Names

The following columns names are generated starting from the corresponding property names:

Simple Value (SV) property columns
Complex Value (CV) property columns
is_null_ column for Collection of Simple Values (CSV) properties and Collection of Complex Values (CCV) properties

Simple Value (SV) Properties Column Names

The following rules are applied to generate the SV column names starting from property names:

the column name is always lowercase.
if the resulting column name exceeds the maximum column name length allowed by the DB, it is truncated to the maximum length allowed.
if the resulting column name already exists in the target table or it is a reserved keyword in the DB, a "_" followed by a progressive number is added at the end of the name.
- if the added "" plus progressive number combination exceeds the maximum column name length allowed, the name is shortened by removing characters starting from the right side before adding the "" plus progressive number.
- the same applies if the resulting column name already exists in the tables of children of the Data Class, in order to ensure that a given property is always mapped to the same column name across the whole inheritance hierarchy.

Complex Value (CV) Properties Column Names

The following rules are applied to generate the two CV column names starting from property names:

the length of the _tbl postfix is subtracted from the maximum column name length.
the same logic as for SV column names (lowercase+shortening+duplicate check) is applied using the maximum column name length obtained in the point above.
the resulting column name is used for the column containing the ID of the target Data Class instance.
the _tbl postfix is added to the resulting column name to get the name of the column that contains the name of the table where the target Data Class instance is located.

Collection Properties is_null_ Column Name

The following rules are applied to generate the is_null_ column name starting from the property names:

the prefix is_null_ is added to the property name
the same logic as for SV column names (lowercase+shortening+duplicate check) is applied to the extended name obtained in the point above

Synchronization Algorithm

Whenever a synchronization of the Data Classes with the DB is triggered (either from the BDS User Interface or by invoking the BDSSyncAllDataClasses functions), the Synchronization Algorithm is invoked.

The algorithm controls the following 3 main tasks:

Generating (or updating) the DB model corresponding to the Data Classes to be stored according to the rules outlined in the previous chapter.
Saving the relevant DB model information (table names, column names etc.) in the Business Data Storage Configuration Business Objects mapped to each Data Class to be stored.
Synchronizing the DB according to the defined model (create tables, add columns etc.)

Here is a more detail description of the steps of the algorithm:

Lock all Data Classes involved in the operation.
Check all Data Classes by running the Model Verification algorithm and the Configuration Validation algorithm.
Update the configuration of all the Data Classes with the latest changes to settings and DB model.
Synchronize the DB (create tables and columns etc.).
Release all the locks on the Data Classes.

If the algorithm encounters any issue for a given Data Class, that class and its children are skipped and the algorithm continues with the other Data Classes. Therefore, the invocation of the Synchronization Algorithm may result in a partial success (some of the Data Classes would be synchronized, while some other would not). If the synchronization of one or more Data Classes fails, the algorithm reports the list of errors.

BDS Data Class Configuration

For each stored Data Class, the BDS needs to keep track of the Cascade settings for each property (see the BDS Operations article) and some information about the corresponding DB model.

Regarding the DB model information, consider the following example of a Data Class with two SV properties:

Person Data Class

String name
Date dateOfBirth

person table (main table)

persistence_id (PK)	name	dateOfBirth

The BDS needs to keep track of:

the name of the main table person
the name of the column for the name property name
the name of the column for the dateOfBirth property dateOfBirth

The BDS uses this information when saving/reading/deleting Data Class instances to use the correct tables and columns to access the data. It also uses it when checking whether the DB model is up to date for a given Data Class.

These configurations are stored transparently, in the Package Configuration of the Package to which the Data Classes belong.

Note that once a Data Class has been committed, any changes to the configurations are auto-committed to the Package Configuration.

Deployment Notes

In the context of staging environments, it is possible, for example, to develop a set of Data Classes in a Development (DEV) environment and store them to the BDS, and then export those Data Classes and deploy them to a Production (PROD) environment.

When doing so, the BDS generates all the DB model information (table and column names etc) in the DEV environment stores them in the corresponding Business Data Storage Configuration. Those settings are, therefore, exported and imported as Business Data Storage Configuration BOs together with the corresponding Packages (see Deployment details) when moving to the PROD environment.

After the deployment of the Packages to the PROD environment, the Data Classes need to be synced to the DB.

The Synchronization Algorithm reads all the DB model information from the Business Data Storage Configuration created in the DEV environment and re-creates the same DB model in PROD (same tables and columns).

Model Verification Algorithm

Before proceeding with the updates to the Data Class configuration and the DB, the Synchronization Algorithm verifies if the Data Classes to be stored comply to a set of rules. These rules verify if the Data Classes can be stored using the BDS.

It is not possible to store Data Classes that do not comply with the defined rules to the BDS. Therefore, any issue must be fixed before proceeding with the synchronization.

Here is the set of model validation rules that are verified in this step for each Data Class to be stored:

all the Data Classes that the Data Class inherits from (at all levels) are also stored.
there is no cycle in the inheritance hierarchy of the Data Class (e.g. Data Class A inherits from Data Class B which inherits from Data Class A).
there are not two properties with the same name in the inheritance hierarchy of the Data Class (e.g. Data Class A with property p1 inherits from Data Class B with property p1).
the ID of the Data Class is not the same as an existing Primitive Type.
the ID of the Data Class is not a reserved word ("Nothing", "Null", "DataPlaceholder", "Indexed", "Named", "Mapped", "unknown").
all the SV and CSV properties of the Data Class have a type which is among the supported Primitive Types.
none of the properties of the Data Class has a type which is a Primitive Type and a Data Class at the same time (e.g. a property of type String if String is also a Data Class in the Base Package)
none of the properties of the Data Class has a type whose ID is a reserved word ("Nothing", "Null", "DataPlaceholder", "Indexed", "Named", "Mapped", "unknown").

Configuration Validation Algorithm

There is another check that the Synchronization Algorithm performs before proceeding: making sure that there is no conflict in the configuration of the Data Classes to be stored. As mentioned, the configuration contains information about the DB objects corresponding to a Data Class, for example the name of the Main Table.

There are some situations where the configuration of different stored Data Classes becomes conflicting, for example when more than one Data Class corresponds to the same Main Table. This situation can generate severe issues with the persistence of data of the two Data Classes, therefore the Synchronization Algorithm does not proceed if such cases are found.

These situations may occur in case of:

importing an .AWEXPORT file that contains conflicting Data Classes.
importing an .AWDEPLOYMENT file that contains conflicting Data Classes.
renaming/moving Data Classes (results in duplicated, and therefore, conflicting configurations).
reverting to and older versions of a Data Class.

The conflicting cases are:

the same Main Table is used by two different Data Classes.
the same Bridge/Collection Table is used by two different Data Classes and it does not correspond to an inherited property between the two Data Classes.

In order to resolve the problematic situations it is recommended to:

for conflicts on Main Tables: remove one of the Data Classes from BDS storage or revert to a older non-conflicting version
for conflicts on Bridge/Collection Tables: remove one of the Data Classes from BDS storage, remove the property with the conflicting table or revert to a older non-conflicting version

If the chosen approach is to remove a Data Class from BDS storage, it is always possible to then add it back. In that case, all DB object names are regenerated by the BDS, hence getting rid of the conflicts previously encountered.

Versioning Implications

In FNZ Studio, it is possible to change the definition of one or more Data Classes at any time, for example by adding of removing a property. New Process Instances will use the new version of the Data Class(es), while old Processes will keep using the old version.

After such changes, if the Data Classes are stored with the BDS, they must be re-synced: the Synchronization Algorithm must be executed so that it can take care of updating the Package Configuration and DB model according to the changes.

The DB model however is not versioned, meaning that there is always just one version of the DB model existing at any point in time. For this reason, the changes to the DB model must be done in a way that preserves the compatibility with old running Processes, otherwise they fail when accessing the data stored with the BDS.

For example, let's consider a case in which an SV property is removed from a Data Class. If the corresponding column in the Main Table was simply dropped, running processes that use the old version of the Data Class where the property still existed would fail if they tried to access the corresponding DB column.

The following sections detail the behavior of the Synchronization Algorithm for the different types of changes that can be applied to stored Data Classes. Only those changes that impact the DB model are described (for example, changes to Data Class functions are not described since the do not t have any impact on the DB model).

Removing a Data Class

No action is taken by the Synchronization Algorithm in this situation. The Main, Bridge and Collection tables corresponding to the Data Class are left untouched in the DB. This allows old running processes which still use the Data Class to keep on reading and writing data to the DB with no issue.

Removing a Property from a Data Class

No action is taken by the Synchronization Algorithm in this situation. The column(s) or tables corresponding to the property are left untouched in the DB. This allows old running processes which still use the property to keep on reading and writing data to the DB with no issue.

This implies that, at a given point in time, the DB model also contains all the tables and columns that were used by the properties of the Data Class in the past.

Adding a Property to a Data Class

The Synchronization Algorithm adds the necessary DB tables and columns according to the logic described in the FNZ Studio to Database Model Conversion section.

Changing a Data Class Property Type

Whenever the type of a property is changed, the Synchronization algorithm leaves the existing table and/or columns corresponding to the old property type untouched.

For the new type, it adds the necessary DB tables and columns according to the logic described in the FNZ Studio to Database Model Conversion section.

As a reference for the different combinations of property types before/after the change and the corresponding actions taken by the Synchronization Algorithm, see the following table:

From/To	SV	CV	CSV	CCV
SV	Keep old column in the Main Table.	Keep old column in the Main Table. Add new column to the Main Table.	Keep old column in the Main Table. Create new Collection Table and new is_null column in the Main Table.	Keep old column in the Main Table. Create new Bridge Table and new is_null column in the Main Table.
CV	Keep two old columns in the Main Table. Add new column to the Main Table.	Keep two old columns in the Main Table.	Keep two old columns in the Main Table. Create new Collection Table and new is_null column in the Main Table.	Keep two old columns in the Main Table. Create new Bridge Table and new is_null column in the Main Table.
CSV	Keep old Collection Table and old is_null column in the Main Table. Add new column to the Main Table.	Keep old Collection Table and old is_null column in the Main Table. Add new column to the Main Table.	Keep old Collection Table and old is_null column in the Main Table.	Keep old Collection Table and old is_null column in the Main Table. Create new Bridge Table and new is_null column in the Main Table.
CCV	Keep old Bridge Table and old is_null column in the Main Table. Add new column to the Main Table.	Keep old Bridge Table and old is_null column in the Main Table. Add two new columns to the Main Table.	Keep old Bridge Table and old is_null column in the Main Table. Create new Collection Table and new is_null column in the Main Table.	Keep old Bridge Table and old is_null column in the Main Table.

Legend: SV - Simple Value; CSV - Collection of Simple Values; CV - Complex Value; CCV - Collection of Complex Values.More info.

Therefore, at a given point in time, the DB model also contains the tables and columns of all the types that the properties of a Data Class had in the past (e.g. if a user changes the type of a property from String to Integer, the DB model contains both a column for type Integer and a column for type String)."

Example

Person Data Class

String name
String dateOfBirth

person table (main table)

persistence_id (PK)	name	dateOfBirth

Change dateOfBirth type from String to Date and resync:

Person Data Class

String name
Date dateOfBirth

person table (main table)

persistence_id (PK)	name	dateOfBirth	dateOfBirth_1

This is due to the fact that, in most situations, the sets of data before and after the change are not compatible, therefore they cannot be stored in the same column/table.

This also implies that, when the property type of a Data Class stored with the BDS is changed and the Data Class is re-synced, the data stored before the change and the data stored after the change are located in different places (different columns/tables). Therefore, it is not possible to query the old and new sets of data together, unless a manual migration of data is performed.

Example

If in the example above the dateOfBirth type is changed back to String, the DB model is the same:

person table (main table)

persistence_id (PK)	name	dateOfBirth	dateOfBirth_1

and new data of type String is saved in the dateOfBirth column.

Note: As explained in this section, the structure of the tables in the database can change during synchronization due to changes in the properties. Some Databases use statement caching, so if the synchronization of Data Classes (with consequent table structure changes) is executed and, afterwards, a statement that was created and executed before the synchronization is reused, an error might be generated by the DB. Generally speaking, this is true only for SELECT * statements. Therefore, it is recommended not to use SELECT * queries for any required custom queries to be executed on the BDS model. The list of columns to be queried should always be specified explicitly instead.++

Recap of DB Out-of-Sync Situations and Corresponding Actions Taken by the Sync Algorithm

The following table summarizes the action taken by the Sync algorithm whenever an out-of-sync situation is encountered on the DB side (e.g. missing table, missing column, column of wrong type with respect to Package Configuration etc).

Object	Sync policy if table/column missing on DB	Sync policy if wrong column type on DB
Main Table	Create	N.A.
Main Table persistence_id column	Create new table	Create new table
Main Table primary key constraint	Create new table	N.A.
SV properties column	Create	Create new column
CV properties column pair	Create new column pair	Create new column pair
is null column for Collection properties	Create	Create new column
Collection Table	Create	N.A.
Collection Table columns	Create new table	Create new table
Collection Table primary key constraint	Create new table	N.A.
Bridge Table	Create	N.A.
Bridge Table columns	Create new table	Create new table
Bridge Table primary key constraint	Create new table	N.A.