Technical columns of database
| Columns Added in Version 7 | Internal Columns Added in Version 7 | Technical Columns Used in Version 6 and Version 7 | Technical Columns Used in Version 6 and Still Used in Version 7 but Deprecated |
This document describes the different technical fields that can be added or that are automatically added as columns in every database table created by the Sage X3 dictionary validation.
In version 7, new columns have been added for better performance in entities management.
Columns Added in Version 7
When a table is created, the following columns are automatically added and displayed at the end of the list of columns:
| Column name | Data type | Definition | Comments |
|---|---|---|---|
| CREDATTIM | ADATIM (Datetime) | This column stores the creation date and time. It is automatically filled by the supervisor when an insert method is used on a class. | Replaces CREDAT and CRETIM (used in version 6, still available, but deprecated). |
| UPDDATTIM | ADATIM (Datetime) | This column stores the update date and time. It is automatically filled by the supervisor when an update method is used on a class. | Replaces UPDDAT and UPDTIM (used in version 6, still available, but deprecated). |
| AUUID | AUUID (Uuid) | This columns stores a [unique identifier](../4gl/glossary-uuid.md) assigned by the supervisor layer when an insertion is done. | When a table is upgraded to version 7 from version 6, this column is inserted, but the existing lines will lack a UUID assignment. Although a UUID can be a unique key, this column should not be used as a key for external references. It is primarily used to identify child instances when a CRUD operation is performed on nested classes instances. |
Internal Columns Added in Version 7
When a table is created, a technical internal column called Updtick is also added to the table, and a trigger is created to feed this field. The principles are the following:
- When a line is created in the table,
Updtickis set to 1. - When an update is done on a line, its
Updtickis increased by 1. - In any persistent class, there is also a property called
Updtick.
Updtick is like a revision number for the line. When CRUD operation based on the supervisor layers is performed, two instructions provided by the engine, called DeleteByKey and RewriteByKey, are used in delete and update operations. They check that the Updtick value did not change between the read operation and the update or delete operation.
Technical Columns Used in Version 6 and Version 7
The following columns already exist in version 6 and are still used in version 7:
| Column name | Data type | Definition | Comments |
|---|---|---|---|
| CREUSR | AUS (user code) | User created the line | Column added by default to the list of fields when a table is created. Not mandatory, but recommended. Assigned automatically by the supervisor layer in Creation. |
| UPDUSR | AUS (user code) | User performed the latest modification | Column added by default to the list of fields when a table is created. Not mandatory, but recommended. Assigned automatically by the supervisor layer in Creation. |
| EXPNUM | L (long integer) | Export time stamp used for sequential exports | Filled by [C]EXPORT variable if present on the table. Not added automatically in the dictionary. |
| ENAFLG | M 1 (local menu 1 : no / yes) | Enabled flag | Flag used for default filtering of active instances in a class. Not added automatically in the dictionary. |
Technical Columns Used in Version 6 and Still Used in Version 7 but Deprecated
The following columns exist in version 6 and are still used in version 7, but their use is strongly discouraged in newly created tables:
| Column name | Data type | Definition | Comments |
|---|---|---|---|
| CRETIM | L (long integer) | Creation time | Time where the creation was finished. |
| UPDTIM | L (long integer) | Update time | Time where the latest update was finished. |