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 IQuery BuildConditions(ICollection <uint> keys, IDatabaseTableData tableData)
{
if (tableData.TableDefinition.Condition == null)
{
return(Queries.Empty());
}
IMultiQuery query = Queries.BeginTransaction();
query.Add(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.Add(conditionQueryGenerator.BuildInsertQuery(conditions));
return(query.Close());
}
public async Task <IQuery> GenerateSql(MetaSolutionSQL item)
{
IMultiQuery multiQuery = Queries.BeginTransaction();
foreach (var subitem in item.ItemsToGenerate)
{
multiQuery.Add(await sqlGeneratorRegistry.Value.GenerateSql(subitem));
}
return(multiQuery.Close());
}
public async Task <IQuery> GenerateSql(SolutionFolderItem item)
{
IMultiQuery query = Queries.BeginTransaction();
query.Comment(item.MyName);
foreach (ISolutionItem i in item.Items)
{
if (i.IsExportable)
{
query.Add(await registry.Value.GenerateSql(i));
}
}
return(query.Close());
}
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 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());
}
