Data sync record key update

Right now we have two methods for detecting existing rows in a table.

Methods

Primary key

If the primary key is defined in the data sync record, this is used to identify an existing row. If the key exists, then an update is performed using the data in the data sync record. If they key does not exist, then a new row is inserted with the data sync record.

Record data

If the primary key is NOT defined in the data sync record then the entire record is used to identify an existing row. This can be used to ensure that the data sync record exists in the database table as it is defined in the data sync file.

Caveats

For primary key, if the key is defined in multiple records then there is a chance that data will be overwritten each time a sync is performed. For record data, because the entire record is used, it is possible that an entirely new record will be recreated if even a single field changes in the data sync file.

Solution

Data key specifiers. In the data sync table object we can add a new attribute called keys that will define the keys to be used to identify existing rows. This will default to the primary key if this attribute does not exist. The attribute can be defined as an array of column names used to find existing rows. This will work almost exactly as the other methods but will allow a record with say, 2 columns matching to be considered the existing row.