public async Task <bool> SynchronizeAsync(string tableName, Dict[] existingRecords, Dict[] newRecords, string[] keyFieldNames = null)
{
if (!this.database.TableByName.TryGetValue(tableName, out Table table))
{
throw new Exception($"Invalid table name '{tableName}'");
}
bool changed = false;
if (existingRecords.Length == 0 && newRecords.Length == 0)
{
return(changed);
}
if (keyFieldNames == null)
{
var record = existingRecords.Length > 0 ? existingRecords[0] : newRecords[0];
var uniqueIndex = table.FindUniqueIndex(record.Keys.ToArray());
if (uniqueIndex == null)
{
throw new Exception("Could not determine unique index");
}
keyFieldNames = uniqueIndex.FieldNames;
}
List <object> existingIds = existingRecords.Select(x => BaseDatabase.GetKeyValue(keyFieldNames, x)).ToList();
List <object> newIds = newRecords.Select(x => BaseDatabase.GetKeyValue(keyFieldNames, x, throwErrorIfMissingKeyField: false)).ToList();
for (int i = 0; i < existingIds.Count; i++)
{
int newIndex = newIds.IndexOf(existingIds[i]);
int count = 0;
if (newIndex == -1)
{
var vars = keyFieldNames.ToDictionary(x => x, x => existingRecords[i][x]);
count = await this.DeleteAsync(table.Name, vars);
}
else if (!newRecords[newIndex].IsSame(existingRecords[i]))
{
count = await this.UpdateAsync(table.Name, newRecords[newIndex]);
}
if (count > 0)
{
changed = true;
}
}
for (int i = 0; i < newIds.Count; i++)
{
int existingIndex = newIds[i] == null ? -1 : existingIds.IndexOf(newIds[i]);
if (existingIndex == -1)
{
await this.InsertAsync <object>(table.Name, newRecords[i]);
changed = true;
}
}
return(changed);
}
public async Task <bool> SynchronizeAsync(string tableName, Dict[] existingRecords, Dict[] newRecords, Func <Dict, dynamic> getDeleteKey, string[] keyFieldNames = null)
{
if (!this.database.TableByName.TryGetValue(tableName, out Table table))
{
throw new Exception($"Invalid table name '{tableName}'");
}
bool changed = false;
if (keyFieldNames == null)
{
keyFieldNames = table.Indexes[0].FieldNames;
}
List <object> existingIds = existingRecords.Select(x => BaseDatabase.GetKeyValue(keyFieldNames, x)).ToList();
List <object> newIds = newRecords.Select(x => BaseDatabase.GetKeyValue(keyFieldNames, x, throwErrorIfMissingKeyField: false)).ToList();
for (int i = 0; i < existingIds.Count; i++)
{
int newIndex = newIds.IndexOf(existingIds[i]);
int count = 0;
if (newIndex == -1)
{
var deleteKey = getDeleteKey(existingRecords[i]);
count = await this.DeleteAsync(table.Name, deleteKey);
}
else if (!newRecords[newIndex].IsSame(existingRecords[i]))
{
count = await this.UpdateAsync(table.Name, newRecords[newIndex]);
}
if (count > 0)
{
changed = true;
}
}
for (int i = 0; i < newIds.Count; i++)
{
int existingIndex = newIds[i] == null ? -1 : existingIds.IndexOf(newIds[i]);
if (existingIndex == -1)
{
await this.InsertAsync <object>(table.Name, newRecords[i]);
changed = true;
}
}
return(changed);
}
public async Task <object> InsertAsync(InsertStatement insertStatement, dynamic vars, bool ignoreIfDuplicate = false)
{
// Convert statementParams
Dict varsDict = insertStatement.ConvertParamsToDict(vars);
this.database.PreprocessInput(insertStatement.StatementFromRefs[0].table.Name, varsDict);
Dict varsOverrides = this.database.GetOverrideValues(insertStatement.StatementFromRefs[0].table);
varsDict.UpdateFrom(varsOverrides);
Dict varsDefaults = this.database.GetDefaultValues(insertStatement.StatementFromRefs[0].table);
// Get the executable sql and params
(string executableSql, Dict executableParams) = insertStatement.GetExecutableSqlAndParams(varsDict, varsDefaults);
// Execute insert and return getGenerateId lambda
Func <object> getGeneratedId;
try {
getGeneratedId = await this.DoInsertAsync(executableSql, executableParams, ignoreIfDuplicate);
}
catch (DuplicateKeyDatabaseException) {
if (ignoreIfDuplicate)
{
return(null);
}
throw;
}
// Determine keyValue (either keyValue is from a generated id or was included in the statement params)
object keyValue;
if (insertStatement.StatementFromRefs[0].table.AutoIncrementFieldName != null && getGeneratedId != null)
{
keyValue = getGeneratedId();
}
else
{
keyValue = BaseDatabase.GetKeyValue(insertStatement.StatementFromRefs[0].table.Indexes[0].FieldNames, executableParams);
}
// Create data event
this.dataEvents.Add(new KeyValueDataEvent(DataEventType.Insert, insertStatement.StatementFromRefs[0].table.Name, keyValue));
this.database.InsertCount++;
return(keyValue);
}
protected async Task <object> GetKeyValue(TableIndex whereIndex, Dict varsDict, Dict executableParams, StatementEqualsRef[] whereRefs, TableIndex primaryIndex, string tableName)
{
Dict fieldValues;
if (whereIndex.IndexType == TableIndexType.Primary)
{
fieldValues = BaseStatement.RemapStatementParamsToFieldValues(varsDict, whereRefs);
}
else
{
// This extra select to convert from a non-primary key index to a primary key index is unfortunate
var selectValues = whereRefs.ToDictionary(x => x.fieldName, x => executableParams[x.fieldName]);
fieldValues = await this.database.SelectRowAsync($"SELECT {string.Join(",", primaryIndex.FieldNames)} FROM {tableName}", selectValues);
}
if (fieldValues == null)
{
return(null);
}
else
{
return(BaseDatabase.GetKeyValue(primaryIndex.FieldNames, fieldValues));
}
}
