public async Task Process(DatabaseCommandDefinitionJson definition, IDatabaseTableData tableData) { if (definition.Parameters == null || definition.Parameters.Length != 2) { throw new Exception("Invalid command definition"); } var textColumn = definition.Parameters[0]; var broadcastTextColumn = definition.Parameters[1]; foreach (var entity in tableData.Entities) { var broadcastTextIdField = entity.GetCell(broadcastTextColumn) as DatabaseField <long>; var textField = entity.GetCell(textColumn) as DatabaseField <string>; if (textField == null || broadcastTextIdField == null) { throw new Exception("Invalid command parameters"); } if (textField.Current.Value == null) { continue; } var broadcastText = await databaseProvider.GetBroadcastTextByTextAsync(textField.Current.Value); if (broadcastText != null) { broadcastTextIdField.Current.Value = broadcastText.Id; } } }
private string BuildConditionsDeleteQuery(ICollection <uint> keys, IDatabaseTableData tableData) { if (tableData.TableDefinition.Condition == null) { return(""); } string?columnKey = null; if (tableData.TableDefinition.Condition.SourceEntryColumn == tableData.TableDefinition.TablePrimaryKeyColumnName) { columnKey = "SourceEntry"; } else if (tableData.TableDefinition.Condition.SourceGroupColumn == tableData.TableDefinition.TablePrimaryKeyColumnName) { columnKey = "SourceGroup"; } else if (tableData.TableDefinition.Condition.SourceIdColumn == tableData.TableDefinition.TablePrimaryKeyColumnName) { columnKey = "SourceId"; } if (columnKey == null) { throw new Exception("No condition source group/entry/id is table primary key. Unable to generate SQL."); } string c = string.Join(", ", keys.Distinct()); return($"DELETE FROM `conditions` WHERE `SourceTypeOrReferenceId` = {tableData.TableDefinition.Condition.SourceType} AND `{columnKey}` IN ({c});"); }
private IQuery BuildConditionsDeleteQuery(ICollection <uint> keys, IDatabaseTableData tableData) { if (tableData.TableDefinition.Condition == null) { return(Queries.Empty()); } string?columnKey = null; if (tableData.TableDefinition.Condition.SourceEntryColumn == tableData.TableDefinition.TablePrimaryKeyColumnName) { columnKey = "SourceEntry"; } else if (tableData.TableDefinition.Condition.SourceGroupColumn == tableData.TableDefinition.TablePrimaryKeyColumnName) { columnKey = "SourceGroup"; } else if (tableData.TableDefinition.Condition.SourceIdColumn == tableData.TableDefinition.TablePrimaryKeyColumnName) { columnKey = "SourceId"; } if (columnKey == null) { throw new Exception("No condition source group/entry/id is table primary key. Unable to generate SQL."); } return(Queries.Table("conditions") .Where(r => r.Column <int>("SourceTypeOrReferenceId") == tableData.TableDefinition.Condition.SourceType) .WhereIn(columnKey, keys.Distinct()) .Delete()); }
public string GenerateQuery(ICollection <uint> keys, IDatabaseTableData tableData) { if (tableData.TableDefinition.IsMultiRecord) { return(GenerateInsertQuery(keys, tableData)); } return(GenerateUpdateQuery(tableData)); }
private IQuery GenerateInsertQuery(ICollection <uint> keys, IDatabaseTableData tableData) { if (keys.Count == 0) { return(Queries.Empty()); } IMultiQuery query = Queries.BeginTransaction(); query.Add(GenerateDeleteQuery(tableData.TableDefinition, keys)); if (tableData.Entities.Count == 0) { return(query.Close()); } var columns = tableData.TableDefinition.TableColumns .Select(c => c.Value) .Where(col => !col.IsMetaColumn && !col.IsConditionColumn) .GroupBy(columns => columns.ForeignTable ?? tableData.TableDefinition.TableName) .ToDictionary(g => g.Key, g => g.ToList()); HashSet <EntityKey> entityKeys = new(); Dictionary <string, List <Dictionary <string, object?> > > inserts = new(tableData.Entities.Count); List <string> duplicates = new List <string>(); var comparer = new EntityComparer(tableData.TableDefinition); foreach (var entity in tableData.Entities.OrderBy(t => t, comparer)) { bool duplicate = tableData.TableDefinition.PrimaryKey != null && !entityKeys.Add(new EntityKey(entity, tableData.TableDefinition)); foreach (var table in columns) { var cells = table.Value.ToDictionary(c => c.DbColumnName, c => { var cell = entity.GetCell(c.DbColumnName) !; if (c.AutogenerateComment != null && cell is DatabaseField <string> sField) { var evaluator = new DatabaseExpressionEvaluator(calculatorService, parameterFactory, tableData.TableDefinition, c.AutogenerateComment !); var comment = evaluator.Evaluate(entity); if (comment is string s) { return(s.AddComment(sField.Current.Value)); } } return(cell.Object); }); if (table.Key != tableData.TableDefinition.TableName) { if (cells.All(c => c.Value == null)) { continue; } var foreignKeys = tableData.TableDefinition.ForeignTableByName[table.Key].ForeignKeys; for (int i = 0; i < foreignKeys.Length; ++i) { cells[foreignKeys[i]] = entity.GetTypedValueOrThrow <long>(tableData.TableDefinition.PrimaryKey ![i]);
private string BuildConditions(ICollection <uint> keys, IDatabaseTableData tableData) { if (tableData.TableDefinition.Condition == null) { return(""); } StringBuilder query = new(); query.AppendLine(BuildConditionsDeleteQuery(keys, tableData)); List <IConditionLine> conditions = new(); int sourceType = tableData.TableDefinition.Condition.SourceType; foreach (var entity in tableData.Entities) { if (entity.Conditions == null) { continue; } int sourceGroup = 0; int sourceEntry = 0; int sourceId = 0; if (tableData.TableDefinition.Condition.SourceEntryColumn != null && entity.GetCell(tableData.TableDefinition.Condition.SourceEntryColumn) is DatabaseField <long> entryCell) { sourceEntry = (int)entryCell.Current.Value; } if (tableData.TableDefinition.Condition.SourceGroupColumn != null && entity.GetCell(tableData.TableDefinition.Condition.SourceGroupColumn) is DatabaseField <long> groupCell) { sourceGroup = (int)groupCell.Current.Value; } if (tableData.TableDefinition.Condition.SourceIdColumn != null && entity.GetCell(tableData.TableDefinition.Condition.SourceIdColumn) is DatabaseField <long> idCell) { sourceId = (int)idCell.Current.Value; } foreach (var condition in entity.Conditions) { conditions.Add(new AbstractConditionLine(sourceType, sourceGroup, sourceEntry, sourceId, condition)); } } query.AppendLine(conditionQueryGenerator.BuildInsertQuery(conditions)); return(query.ToString()); }
public IQuery GenerateQuery(ICollection <uint> keys, IDatabaseTableData tableData) { if (tableData.TableDefinition.IsOnlyConditionsTable) { return(BuildConditions(keys, tableData)); } if (tableData.TableDefinition.IsMultiRecord) { return(GenerateInsertQuery(keys, tableData)); } return(GenerateUpdateQuery(tableData)); }
public IQuery GenerateSingleRecordQuery(IReadOnlyList <DatabaseKey> keys, IReadOnlyList <DatabaseKey>?deletedKeys, IDatabaseTableData tableData) { var query = Queries.BeginTransaction(); // delete GeneratePrimaryKeyDeletion(tableData.TableDefinition, deletedKeys, query); //insert query.Add(GenerateInsertQuery(tableData.Entities.Where(e => !e.ExistInDatabase).Select(e => e.GenerateKey(tableData.TableDefinition)).ToList(), tableData)); //update foreach (var entity in tableData.Entities) { if (entity.ExistInDatabase) { query.Add(GenerateUpdateQuery(tableData.TableDefinition, entity)); } } return(query.Close()); }
public IQuery GenerateQuery(IReadOnlyList <DatabaseKey> keys, IReadOnlyList <DatabaseKey>?deletedKeys, IDatabaseTableData tableData) { if (tableData.TableDefinition.IsOnlyConditionsTable) { return(BuildConditions(keys, tableData)); } if (tableData.TableDefinition.RecordMode == RecordMode.MultiRecord) { return(GenerateInsertQuery(keys, tableData)); } if (tableData.TableDefinition.RecordMode == RecordMode.SingleRow) { return(GenerateSingleRecordQuery(keys, deletedKeys, tableData)); } return(GenerateUpdateQuery(tableData)); }
public IQuery GenerateInsertQuery(IReadOnlyList <DatabaseKey> keys, IDatabaseTableData tableData) { if (keys.Count == 0) { return(Queries.Empty()); } IMultiQuery query = Queries.BeginTransaction(); query.Add(GenerateDeleteQuery(tableData.TableDefinition, keys)); if (tableData.Entities.Count == 0) { return(query.Close()); } var columns = tableData.TableDefinition.TableColumns .Select(c => c.Value) .Where(col => !col.IsMetaColumn && !col.IsConditionColumn) .GroupBy(columns => columns.ForeignTable ?? tableData.TableDefinition.TableName) .ToDictionary(g => g.Key, g => g.ToList()); HashSet <EntityKey> entityKeys = new(); Dictionary <string, List <Dictionary <string, object?> > > inserts = new(tableData.Entities.Count); List <string> duplicates = new List <string>(); var comparer = new EntityComparer(tableData.TableDefinition); foreach (var entity in tableData.Entities.OrderBy(t => t, comparer)) { if (!entity.Phantom && !keys.Contains(entity.Key)) { continue; } bool duplicate = tableData.TableDefinition.PrimaryKey != null && !entityKeys.Add(new EntityKey(entity, tableData.TableDefinition)); foreach (var table in columns) { bool isDefault = true; bool isMainTable = table.Key == tableData.TableDefinition.TableName; var cells = table.Value.ToDictionary(c => c.DbColumnName, c => { var cell = entity.GetCell(c.DbColumnName) !; if (c.AutogenerateComment != null && cell is DatabaseField <string> sField) { var evaluator = new DatabaseExpressionEvaluator(calculatorService, parameterFactory, tableData.TableDefinition, c.AutogenerateComment !); var comment = evaluator.Evaluate(entity); if (comment is string s) { return(s.AddComment(sField.Current.Value)); } } var columnDefinition = tableData.TableDefinition.TableColumns[cell.FieldName]; if (!columnDefinition.CanBeNull && cell.Object is null) { return(columnDefinition.Default ?? 0L); } if (columnDefinition.CanBeNull && cell.Object is null) { return(null); } if (!isMainTable && columnDefinition.IsTypeFloat && ((cell.Object == null && columnDefinition.Default != null) || cell.Object != null && !cell.Object.Equals(columnDefinition.Default ?? 0f))) { isDefault = false; } if (!isMainTable && columnDefinition.IsTypeLong && ((cell.Object == null && columnDefinition.Default != null) || cell.Object != null && !cell.Object.Equals(columnDefinition.Default ?? 0L))) { isDefault = false; } return(FixUnixTimestampAndNullability(columnDefinition, cell.Object)); }); if (!isMainTable) { if (isDefault) { continue; } var newCells = new Dictionary <string, object?>(); var foreignKeys = tableData.TableDefinition.ForeignTableByName ![table.Key].ForeignKeys;
private string GenerateInsertQuery(ICollection <uint> keys, IDatabaseTableData tableData) { if (keys.Count == 0) { return(""); } StringBuilder query = new(); var keysString = string.Join(", ", keys.Distinct()); query.AppendLine( $"DELETE FROM {tableData.TableDefinition.TableName} WHERE {tableData.TableDefinition.TablePrimaryKeyColumnName} IN ({keysString});"); if (tableData.Entities.Count == 0) { return(query.ToString()); } var columns = tableData.Entities[0].Fields.Select(f => $"`{f.FieldName}`"); var columnsString = string.Join(", ", columns); query.AppendLine($"INSERT INTO {tableData.TableDefinition.TableName} ({columnsString}) VALUES"); HashSet <EntityKey> entityKeys = new(); List <string> inserts = new List <string>(tableData.Entities.Count); List <string> duplicates = new List <string>(); foreach (var entity in tableData.Entities) { bool duplicate = tableData.TableDefinition.PrimaryKey != null && !entityKeys.Add(new EntityKey(entity, tableData.TableDefinition)); var cells = entity.Fields.Select(f => f.ToQueryString()); var cellStrings = string.Join(", ", cells); if (duplicate) { duplicates.Add($"({cellStrings})"); } else { inserts.Add($"({cellStrings})"); } } query.Append(string.Join(",\n", inserts)); query.AppendLine(";"); if (duplicates.Count > 0) { query.AppendLine(" -- duplicates, cannot insert:"); foreach (var line in duplicates) { query.AppendLine(" -- " + line); } } if (tableData.TableDefinition.Condition != null) { query.AppendLine(BuildConditionsDeleteQuery(keys, tableData)); List <IConditionLine> conditions = new(); int sourceType = tableData.TableDefinition.Condition.SourceType; foreach (var entity in tableData.Entities) { if (entity.Conditions == null) { continue; } int sourceGroup = 0; int sourceEntry = 0; int sourceId = 0; if (tableData.TableDefinition.Condition.SourceEntryColumn != null && entity.GetCell(tableData.TableDefinition.Condition.SourceEntryColumn) is DatabaseField <long> entryCell) { sourceEntry = (int)entryCell.Current.Value; } if (tableData.TableDefinition.Condition.SourceGroupColumn != null && entity.GetCell(tableData.TableDefinition.Condition.SourceGroupColumn) is DatabaseField <long> groupCell) { sourceGroup = (int)groupCell.Current.Value; } if (tableData.TableDefinition.Condition.SourceIdColumn != null && entity.GetCell(tableData.TableDefinition.Condition.SourceIdColumn) is DatabaseField <long> idCell) { sourceId = (int)idCell.Current.Value; } foreach (var condition in entity.Conditions) { conditions.Add(new AbstractConditionLine(sourceType, sourceGroup, sourceEntry, sourceId, condition)); } } query.AppendLine(conditionQueryGenerator.BuildInsertQuery(conditions)); } return(query.ToString()); }
private string GenerateUpdateQuery(IDatabaseTableData tableData) { StringBuilder query = new(); foreach (var entity in tableData.Entities) { Dictionary <string, List <IDatabaseField> > fieldsByTable = null !; if (tableData.TableDefinition.ForeignTable != null) { fieldsByTable = entity.Fields .GroupBy(f => tableData.TableDefinition.TableColumns[f.FieldName].ForeignTable ?? tableData.TableDefinition.TableName) .ToDictionary(g => g.Key, g => g.ToList()); foreach (var foreign in tableData.TableDefinition.ForeignTable) { fieldsByTable[foreign.TableName].Insert(0, new DatabaseField <long>(foreign.ForeignKey, new ValueHolder <long>(entity.Key, false))); } } else { fieldsByTable = new(); fieldsByTable[tableData.TableDefinition.TableName] = entity.Fields.ToList(); } if (entity.ExistInDatabase) { foreach (var table in fieldsByTable) { var updates = string.Join(", ", table.Value .Where(f => f.IsModified) .Select(f => $"`{f.FieldName}` = {f.ToQueryString()}")); if (string.IsNullOrEmpty(updates)) { continue; } string primaryKeyColumn = tableData.TableDefinition.TablePrimaryKeyColumnName; if (table.Key != tableData.TableDefinition.TableName) { primaryKeyColumn = tableData.TableDefinition.ForeignTableByName[table.Key].ForeignKey; query.AppendLine( $"INSERT IGNORE INTO {table.Key} (`{primaryKeyColumn}`) VALUES ({entity.Key});"); } var updateQuery = $"UPDATE `{table.Key}` SET {updates} WHERE `{primaryKeyColumn}`= {entity.Key};"; query.AppendLine(updateQuery); } } else { foreach (var table in fieldsByTable) { string primaryKeyColumn = tableData.TableDefinition.TablePrimaryKeyColumnName; if (table.Key != tableData.TableDefinition.TableName) { primaryKeyColumn = tableData.TableDefinition.ForeignTableByName[table.Key].ForeignKey; } query.AppendLine( $"DELETE FROM {table.Key} WHERE `{primaryKeyColumn}` = {entity.Key};"); var columns = string.Join(", ", table.Value.Select(f => $"`{f.FieldName}`")); query.AppendLine($"INSERT INTO {table.Key} ({columns}) VALUES"); var values = string.Join(", ", table.Value.Select(f => f.ToQueryString())); query.AppendLine($"({values});"); } } } return(query.ToString()); }
private string GenerateInsertQuery(ICollection <uint> keys, IDatabaseTableData tableData) { if (keys.Count == 0) { return(""); } StringBuilder query = new(); var keysString = string.Join(", ", keys.Distinct()); query.AppendLine( $"DELETE FROM {tableData.TableDefinition.TableName} WHERE {tableData.TableDefinition.TablePrimaryKeyColumnName} IN ({keysString});"); if (tableData.Entities.Count == 0) { return(query.ToString()); } var columns = tableData.TableDefinition.TableColumns .Select(c => c.Value) .Where(col => !col.IsMetaColumn && !col.IsConditionColumn) .ToList(); var columnsString = string.Join(", ", columns.Select(f => $"`{f.DbColumnName}`")); query.AppendLine($"INSERT INTO {tableData.TableDefinition.TableName} ({columnsString}) VALUES"); HashSet <EntityKey> entityKeys = new(); List <string> inserts = new List <string>(tableData.Entities.Count); List <string> duplicates = new List <string>(); foreach (var entity in tableData.Entities) { bool duplicate = tableData.TableDefinition.PrimaryKey != null && !entityKeys.Add(new EntityKey(entity, tableData.TableDefinition)); var cells = columns.Select(c => { var cell = entity.GetCell(c.DbColumnName) !; if (c.AutogenerateComment != null && cell is DatabaseField <string> sField) { var evaluator = new DatabaseExpressionEvaluator(calculatorService, parameterFactory, tableData.TableDefinition, c.AutogenerateComment !); var comment = evaluator.Evaluate(entity); if (comment is string s) { return(s.AddComment(sField.Current.Value).ToSqlEscapeString()); } } return(cell.ToQueryString()); }); var cellStrings = string.Join(", ", cells); if (duplicate) { duplicates.Add($"({cellStrings})"); } else { inserts.Add($"({cellStrings})"); } } query.Append(string.Join(",\n", inserts)); query.AppendLine(";"); if (duplicates.Count > 0) { query.AppendLine(" -- duplicates, cannot insert:"); foreach (var line in duplicates) { query.AppendLine(" -- " + line); } } query.AppendLine(BuildConditions(keys, tableData)); return(query.ToString()); }
private IQuery GenerateUpdateQuery(IDatabaseTableData tableData) { IMultiQuery query = Queries.BeginTransaction(); foreach (var entity in tableData.Entities) { Dictionary <string, List <IDatabaseField> > fieldsByTable = entity.Fields .Select(ef => (ef, tableData.TableDefinition.TableColumns[ef.FieldName])) .Where(pair => !pair.Item2.IsMetaColumn && !pair.Item2.IsConditionColumn) .GroupBy(pair => pair.Item2.ForeignTable ?? tableData.TableDefinition.TableName) .ToDictionary(g => g.Key, g => g.Select(f => f.ef).ToList()); if (tableData.TableDefinition.ForeignTable != null) { foreach (var foreign in tableData.TableDefinition.ForeignTable) { fieldsByTable[foreign.TableName].Insert(0, new DatabaseField <long>(foreign.ForeignKey, new ValueHolder <long>(entity.Key, false))); } } if (entity.ExistInDatabase) { foreach (var table in fieldsByTable) { if (!table.Value.Any(f => f.IsModified)) { continue; } var updates = table.Value .Where(f => f.IsModified) .ToList(); string primaryKeyColumn = tableData.TableDefinition.TablePrimaryKeyColumnName; if (table.Key != tableData.TableDefinition.TableName) { primaryKeyColumn = tableData.TableDefinition.ForeignTableByName[table.Key].ForeignKey; query.Table(table.Key) .InsertIgnore(new Dictionary <string, object?>() { { primaryKeyColumn, entity.Key } }); } IUpdateQuery update = query.Table(table.Key) .Where(row => row.Column <uint>(primaryKeyColumn) == entity.Key) .Set(updates[0].FieldName, updates[0].Object); for (int i = 1; i < updates.Count; ++i) { update = update.Set(updates[i].FieldName, updates[i].Object); } update.Update(); } } else { foreach (var table in fieldsByTable) { string primaryKeyColumn = tableData.TableDefinition.TablePrimaryKeyColumnName; if (table.Key != tableData.TableDefinition.TableName) { primaryKeyColumn = tableData.TableDefinition.ForeignTableByName[table.Key].ForeignKey; } query.Table(table.Key) .Where(row => row.Column <uint>(primaryKeyColumn) == entity.Key) .Delete(); query.Table(table.Key) .Insert(table.Value.ToDictionary(t => t.FieldName, t => t.Object)); } } } return(query.Close()); }
private IQuery GenerateInsertQuery(ICollection <uint> keys, IDatabaseTableData tableData) { if (keys.Count == 0) { return(Queries.Empty()); } IMultiQuery query = Queries.BeginTransaction(); query .Table(tableData.TableDefinition.TableName) .WhereIn(tableData.TableDefinition.TablePrimaryKeyColumnName, keys.Distinct()) .Delete(); if (tableData.Entities.Count == 0) { return(query.Close()); } var columns = tableData.TableDefinition.TableColumns .Select(c => c.Value) .Where(col => !col.IsMetaColumn && !col.IsConditionColumn) .ToList(); HashSet <EntityKey> entityKeys = new(); List <Dictionary <string, object?> > inserts = new(tableData.Entities.Count); List <string> duplicates = new List <string>(); var comparer = new EntityComparer(tableData.TableDefinition); foreach (var entity in tableData.Entities.OrderBy(t => t, comparer)) { bool duplicate = tableData.TableDefinition.PrimaryKey != null && !entityKeys.Add(new EntityKey(entity, tableData.TableDefinition)); var cells = columns.ToDictionary(c => c.DbColumnName, c => { var cell = entity.GetCell(c.DbColumnName) !; if (c.AutogenerateComment != null && cell is DatabaseField <string> sField) { var evaluator = new DatabaseExpressionEvaluator(calculatorService, parameterFactory, tableData.TableDefinition, c.AutogenerateComment !); var comment = evaluator.Evaluate(entity); if (comment is string s) { return(s.AddComment(sField.Current.Value)); } } return(cell.Object); }); if (duplicate) { duplicates.Add("(" + string.Join(", ", cells.Values) + ")"); } else { inserts.Add(cells); } } query.Table(tableData.TableDefinition.TableName) .BulkInsert(inserts); if (duplicates.Count > 0) { query.Comment("duplicates, cannot insert:"); foreach (var line in duplicates) { query.Comment(line); } } query.Add(BuildConditions(keys, tableData)); return(query.Close()); }