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)); } }