SQL table definition
All SQL tables used by the DB Sync system need to be declared in the database schema.
An SQL table JSON object must contain the following properties:
ordinal
: An ordinal number to order the tables in the schema,name
: The SQL table name.cols
: The list of SQL column definition with data type and column constraints.pkey
: The primary key definition.autoincr
(optional): Auto-incremented number generator.uniques
(optional): Unique constraints.checks
(optional): Check constraints.references
(optional): Foreign key references to other tables.dbsync
: The synchronization type.
Below is an example of SQL Table defining the
"employee"
table: {
"ordinal": 9,
"name": "employee",
"cols": [ ... ],
"pkey": {
"name": "pk_employee",
"cols": [
"employee_id"
]
},
"autoincr": { ... },
"uniques": [ ... ],
"checks": [ ... ],
"references": [ ... ],
"dbsync": {
"sync_type": "sync"
}
}
The
"sync_type"
property defines the who data rows are transmitted between the
central database and the DB Sync client apps:- With the
"sync"
type, the DB Sync system will register all changes in the sync log, and optimize the synchronization procedure by handling only new, modified or deleted rows. Use this type of synchronization, for any SQL table where data rows are created, modified and deleted by DB Sync client apps or server-side apps. - With the
"none"
type, the DB Sync system will send all data rows to the DB Sync client app, only in case of complete db copy or when doing a full sync. The"none"
mode is typically used for SQL tables containing static data such as a"country"
table: Such SQL table is filled once when creating the central database, and never modified during the life time of the application.
In case of doubt, just use the "sync"
type.