To run on various databases, the Application Model data types are mapped to types of the different database providers. Also primary keys, foreign keys and indexes are created. Furthermore, for some databases triggers, sequences and default constraints are needed.
Object Types
The following object types are created:
- Tables
- Columns
- Primary keys
- Foreign keys (optional)
- Indexes (optional)
- Triggers (ORACLE and MySQL)
- Sequences (ORACLE)
- Default Constraints (MSSQL)
Tables
Tables are created along with the first column created, which is the M4ID for nodes of type M4Node. The name of tables is defined in the TableName property of each DataNode.
Columns
The name of columns is defined in the ColumnName property of each DataField. M4 Back Office works with a reduced data type set, that is mapped to the data types of the different database providers.
Data Type Mapping
| M4 Type | MySQL Type | Microsoft SQL Server Type | ORACLE Type |
|---|---|---|---|
| AutoIncrementData | INT AUTO_INCREMENT | INT IDENTITY(1, 1) | NUMBER(22) 1 |
| GUIDData | CHAR(36) | UNIQUEIDENTIFIER | RAW(16) 2 |
| StringData |
NVARCHAR([length]) 3 TEXT - if length > 255 MEDIUMTEXT - if length > 21844 LONGTEXT - if length > 5592404 |
NVARCHAR([length]) NVARCHAR(MAX) - if length > 4000 |
NVARCHAR2([length]) NCLOB - if length > 2000 |
| BooleanData | TINYINT(1) | BIT | NUMBER(1) |
| IntegerData 4 |
INT BIGINT - if MaxValue/MinValue not between 2147483647 and -2147483647 |
INT BIGINT - if MaxValue/MinValue not between 2147483647 and -2147483647 |
NUMBER([size]) |
| DecimalData 4 | DECIMAL([size],[decimals]) | DECIMAL([size],[decimals]) | NUMBER([size],[decimals]) |
| DateTimeData | DATETIME | DATETIME | DATE |
| DateData | DATE | DATE | DATE |
| TimeData | TIME | TIME | INTERVAL DAY(0) TO SECOND(6) |
| ImagePathData 5 | NVARCHAR(255) | NVARCHAR(255) | NVARCHAR2(255) |
1 ORACLE does not have an auto increment column type. To obtain the behavior of such a column,
a number column is created along with a trigger and a sequence to add auto incrementing values to that column.
2 Stores GUID as byte array.
3 The data type of columns created as NVARCHAR is VARCHAR with CHARACTER_SET_NAME utf8.
4 Size is the maximum number of integer digits for positive or negative values.
5 Pseudo data type. Underlying data type is StringData. Fixed length is 255.
Primary Keys, Foreign Keys And Indexes
For data nodes with DataNodeType M4Node the standard field M4ID
is always created as primary key.
Primary keys for DataNodeType AuxiliaryNode
can be set using the property IndexType.
NOTE: As for each table only one primary key is allowed, custom primary keys can only be created for AuxiliaryNode tables.
Additional indexes of type Unique and NotUnique can also be added setting the IndexType.
Indexes can help to make data access faster, especially for large tables. They are useful, if you use a field for searching or to join tables.
NOTE: The M4ID and fields of type IDDataField, used to join tables by M4 functions, are indexed by default.
Also the M4User field has got a default index.
While every index type can help to accelerate database queries, primary and unique keys will also assure, that all values are unique for that column.
Foreign keys are used to ensure that references between parent table records and child table records are never broken.
You can choose between two types of foreign keys:
-
Restricted
Deletion of parent record with references to child records causes an exception and is aborted. -
Cascade
If a parent record is deleted, all child records with references to it are also deleted.
Triggers, Sequences And Constraints
Triggers, sequences and constraints are not explicitly part of the M4 Application Model, but needed on particular databases to create the required objects.
AutoIncrementData Column On ORACLE Database
As mentioned, ORACLE does not have an auto increment column type. To obtain the behavior of such a column, a number column is created along with a trigger and a sequence to add auto incrementing values to that column.
GUID As Default Value On MySQL Database
MySQL does not support new GUID values as default value. Alternatively a trigger is defined, that adds a new GUID on insert.
Default Values On Microsoft MSSQL Server
To define a default value on MSSQL Server a default constraint object needs to be created.
Identifiers
While for the naming for tables and columns the corresponding table and column names are used,
for other database objects no explicit names are available. For these objects identifiers need to be defined, that are unique and compliant with database naming conventions.
For the Application Model tables this can be done, using table and column names.
Unfortunately this is not possible for custom tables as table and column names are not known and resulting identifiers may be too long or may contain invalid characters.
To get unique and valid identifiers for custom tables, each field has a FieldID and the data model has a DataModelID.
The FieldID is an incremental number, unique for all fields of a DataModel.
The DataModelID is a random number between 1000 and 9999 assigned on model creation.
NOTE: As it is a random number it is possible, that two Application Model, used for the same database, are created with the same DataModelID.
In this case you have to assign a new DataModelID. All objects, using the identifiers are dropped.
They will be recreated with the new DataModelID the next time you choose Create Tables(s) And Columns....
The identifiers contain a TypeKey for the different types of database objects:
- IN for indexes
- PK for primary keys
- FK for foreign keys
- DC for default constraints
- SQ for sequences
- TR for triggers
Identifiers for Application Model tables have the format:
[TableName]
[TypeKey]
[ColumnName].
For history tables the M4 prefix of the table name is changed to M4H.
Identifiers for custom tables have the format:
[M4]
[DataModelID]
[TypeKey]
[FieldID].
For history tables the M4 is changed to M4H.