The SYNC database therefore acts as a push/pop queue for the synchronisation operations.
Through its own application, stored procedure or trigger, internal functions may be developed that manage data alignment between the Cloud system and the internal database infrastructure via the SYNC database.
The SYRFC, SYROP and SYRID fields
Certain essential fields to understand what kind of operation was carried out on the record, where it comes from (Cloud or internal infrastructure) and in what order, are found on each table of the system, visible only on the SYNC database.
SYRFC (System Record From Cloud):
this is a bit field (0/1) that indicates whether the record in the table has been inserted from the Cloud system or internally. If the bit is 1, the record in question is to be read and processed as it is inserted from the Cloud – if the bit is 0, the record is to be synchronised in the Cloud.
SYROP (System Record Operation):
this is a varchar(10) field which indicates the type of operation carried out on the record. The values in this field can be INSERT, UPDATE, DELETE.
You will therefore always perform an input SQL operation on the table in question for all INSERT, UPDATE and DELETE operations.
INSERT operations: The SYRFC field is set to "0" and the SYROP to "INSERT".
UPDATE operations: The SYRFC field is set to "0" and the SYROP to "UPDATE".
DELETE operations: The SYRFC field is set to "0" and the SYROP to "DELETE".
To read the records that have been altered from the Cloud, check for one or more records on the relevant tables with SYRFC bit set to "1". Again, the SYROP field indicates the operation that was performed on the record in question. The records must be read, sorting them by the SYRID field (System Record ID) that represents the record input identity (counter field).
In case of reading, once the relevant row is read, it must be deleted from the table on the SYNC database.
Key management logic (RECORD_ID and SYKEY)
There is a RECORD_ID key field on each table of the Goose system.
The value of the RECORD_ID field automatically consists of [ID_SESSIONE_UTENTE]-[INDICE_INCREMENTALE] (USER_SESSION_ID)-(INCREMENTAL_INDEX).
The [ID_SESSIONE_UTENTE] (USER_SESSION_ID) element is an incremental integer value that varies with each login session. If the same user should log in several times, they will be assigned different ID_SESSIONE.
The [INDICE_INCREMENTALE] (INCREMENTAL_INDEX) element is an incremental session value.
This mode allows you to generate always unique keys especially in concurrent situations.
You can use other fields to manage the referencing of records in relation to one’s internal infrastructure.
The first and most important is the varchar(50) type SYKEY field.
The SYKEY field is never written from Cloud, but can be used internally to link the key value relative to one’s database to a specific record.
If you perform an INSERT on the SYNC system, by compiling this field, the value will be linked to the record in question and will always be visible in all subsequent operations, both UPDATE and DELETE.
All tables also indicate a specific code field to manage any internal codes.
A few examples:
- PRO_DOT.CDPRO: varchar(100) type Product Code
- CON_TRO.CDCON: varchar(50) type Product Code
- AGE_NTI.CDAGE: varchar(50) type Product Code
Usually these fields are varchar(50) or varchar(100) to allow even complex codes to be managed, however, they are simple descriptive fields.
Do you want to ask us something?
Contact us at firstname.lastname@example.org