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