private string BuildSQLQueryForSingleRow(DatabaseTableDefinitionJson tableDefinitionJson, string?customWhere, long?offset, int?limit)
{
var tableName = tableDefinitionJson.TableName;
var columns = tableDefinitionJson.Groups
.SelectMany(x => x.Fields)
.Where(x => !x.IsConditionColumn && !x.IsMetaColumn)
.Select(x =>
{
var column = $"`{x.ForeignTable ?? tableName}`.`{x.DbColumnName}`";
if (x.IsUnixTimestamp)
{
column = $"UNIX_TIMESTAMP({column}) AS `{x.DbColumnName}`";
}
return(column);
})
.Distinct();
var names = string.Join(",", columns);
var joins = "";
if (tableDefinitionJson.ForeignTable != null)
{
joins += string.Join(" ", tableDefinitionJson.ForeignTable.Select(table =>
{
var where = table.ForeignKeys.Zip(tableDefinitionJson.PrimaryKey !)
.Select(pair => $"`{table.TableName}`.`{pair.First}` = `{tableName}`.`{pair.Second}`");
return($"LEFT JOIN `{table.TableName}` ON " + string.Join(" AND ", where));
}));
}
var where = string.IsNullOrEmpty(customWhere) ? "" : $"WHERE ({customWhere})";
return($"SELECT {names} FROM `{tableDefinitionJson.TableName}` {joins} {where} ORDER BY {string.Join(", ", tableDefinitionJson.PrimaryKey.Select(x => $"`{x}`"))} ASC LIMIT {limit ?? 300} OFFSET {offset ?? 0}");
}
public IQuery GenerateDeleteQuery(DatabaseTableDefinitionJson table, IReadOnlyList <DatabaseKey> keys)
{
var query = Queries.BeginTransaction();
GeneratePrimaryKeyDeletion(table, keys.Distinct().ToList(), query);
return(query.Close());
}
public IQuery GenerateDeleteQuery(DatabaseTableDefinitionJson table, ICollection <uint> keys)
{
if (keys.Count == 1)
{
return(GenerateDeleteQuery(table, keys.First()));
}
var query = Queries.BeginTransaction();
if (table.ForeignTable != null)
{
foreach (var foreign in table.ForeignTable)
{
query
.Table(foreign.TableName)
.WhereIn(foreign.ForeignKeys[0], keys.Distinct())
.Delete();
}
}
query
.Table(table.TableName)
.WhereIn(table.TablePrimaryKeyColumnName, keys.Distinct())
.Delete();
return(query.Close());
}
private string BuildSQLQueryFromTableDefinition(DatabaseTableDefinitionJson tableDefinitionJson, DatabaseKey[] entries)
{
var tableName = tableDefinitionJson.TableName;
var tablePrimaryKey = tableDefinitionJson.TablePrimaryKeyColumnName;
var columns = tableDefinitionJson.Groups
.SelectMany(x => x.Fields)
.Where(x => !x.IsConditionColumn && !x.IsMetaColumn)
.Select(x =>
{
var column = $"`{x.ForeignTable ?? tableName}`.`{x.DbColumnName}`";
if (x.IsUnixTimestamp)
{
column = $"UNIX_TIMESTAMP({column}) AS `{x.DbColumnName}`";
}
return(column);
})
.Distinct();
var names = string.Join(",", columns);
var joins = "";
if (tableDefinitionJson.ForeignTable != null)
{
joins += string.Join(" ", tableDefinitionJson.ForeignTable.Select(table =>
{
var where = table.ForeignKeys.Zip(tableDefinitionJson.PrimaryKey !)
.Select(pair => $"`{table.TableName}`.`{pair.First}` = `{tableName}`.`{pair.Second}`");
return($"LEFT JOIN `{table.TableName}` ON " + string.Join(" AND ", where));
}));
}
return
($"SELECT {names} FROM {tableDefinitionJson.TableName} {joins} WHERE `{tableName}`.`{tablePrimaryKey}` IN ({string.Join(", ", entries)});");
}
public IQuery GenerateDeleteQuery(DatabaseTableDefinitionJson table, DatabaseEntity entity)
{
return(Queries
.Table(table.TableName)
.Where(r => r.Column <uint>(table.TablePrimaryKeyColumnName) == entity.Key)
.Delete());
}
public DatabaseEntity CreateEmptyEntity(DatabaseTableDefinitionJson definition, DatabaseKey key, bool phantomEntity)
{
Dictionary <string, IDatabaseField> columns = new(StringComparer.InvariantCultureIgnoreCase);
foreach (var column in definition.Groups.SelectMany(t => t.Fields)
.Distinct(
EqualityComparerFactory.Create <DatabaseColumnJson>(
f => f.DbColumnName.GetHashCode(),
(a, b) => a !.DbColumnName.Equals(b !.DbColumnName))))
{
IValueHolder valueHolder;
var type = column.ValueType;
if (parameterFactory.IsRegisteredLong(type))
{
type = "uint";
}
else if (parameterFactory.IsRegisteredString(type))
{
type = "string";
}
else if (type.EndsWith("Parameter"))
{
type = "uint";
}
if (type == "float")
{
valueHolder = new ValueHolder <float>(column.Default is float f ? f : 0.0f, column.CanBeNull && column.Default == null);
}
else if (type is "int" or "uint" or "long")
{
valueHolder = new ValueHolder <long>(column.Default is long f ? f : 0, column.CanBeNull && column.Default == null);
}
private string BuildWhereFromKeys(DatabaseTableDefinitionJson definition, DatabaseKey[] keys)
{
if (keys.Length == 0)
{
return("false");
}
if (definition.GroupByKeys.Count == 1)
{
return($"`{definition.TableName}`.`{definition.GroupByKeys[0]}` IN ({string.Join(", ", keys.Select(k => k[0]))})");
}
else
{
Debug.Assert(keys[0].Count == definition.GroupByKeys.Count);
return(string.Join(" OR ", keys.Select(key =>
{
StringBuilder sb = new();
for (int i = 0; i < definition.GroupByKeys.Count; ++i)
{
if (i != 0)
{
sb.Append(" AND ");
}
sb.Append($"(`{definition.TableName}`.`{definition.GroupByKeys[i]}` = {key[i]})");
}
return sb.ToString();
})));
}
}
public ExpressionVisitor(ICreatureStatCalculatorService statCalculatorService,
IParameterFactory parameterFactory,
DatabaseTableDefinitionJson definition)
{
this.statCalculatorService = statCalculatorService;
this.parameterFactory = parameterFactory;
this.definition = definition;
}
public EntityKey(DatabaseEntity entity, DatabaseTableDefinitionJson table)
{
fields = table.PrimaryKey !.Select(key => entity.GetCell(key) !).ToList();
hash = 0;
foreach (var field in fields)
{
hash = HashCode.Combine(hash, field.GetHashCode());
}
}
public DatabaseEntity CreateEmptyEntity(DatabaseTableDefinitionJson definition, uint key)
{
Dictionary <string, IDatabaseField> columns = new();
foreach (var column in definition.Groups.SelectMany(t => t.Fields)
.Distinct(
EqualityComparerFactory.Create <DatabaseColumnJson>(
f => f.DbColumnName.GetHashCode(),
(a, b) => a !.DbColumnName.Equals(b !.DbColumnName))))
{
IValueHolder valueHolder;
var type = column.ValueType;
if (parameterFactory.IsRegisteredLong(type))
{
type = "uint";
}
else if (parameterFactory.IsRegisteredString(type))
{
type = "string";
}
else if (type.EndsWith("Parameter"))
{
type = "uint";
}
if (type == "float")
{
valueHolder = new ValueHolder <float>(column.Default is float f ? f : 0.0f, column.CanBeNull && column.Default == null);
}
else if (type == "int" || type == "uint")
{
if (column.DbColumnName == definition.TablePrimaryKeyColumnName)
{
valueHolder = new ValueHolder <long>(key, false);
}
else
{
valueHolder = new ValueHolder <long>(column.Default is long f ? f : 0, column.CanBeNull && column.Default == null);
}
}
else
{
valueHolder = new ValueHolder <string>(column.Default is string f ? f : "", column.CanBeNull && column.Default == null);
}
columns[column.DbColumnName] = databaseFieldFactory.CreateField(column.DbColumnName, valueHolder);
}
return(new DatabaseEntity(false, key, columns, null));
}
internal DatabaseTableSolutionItemProvider(DatabaseTableDefinitionJson definition,
IDatabaseTableDataProvider tableDataProvider,
IItemFromListProvider itemFromListProvider,
IMessageBoxService messageBoxService,
IParameterFactory parameterFactory)
{
this.tableDataProvider = tableDataProvider;
this.itemFromListProvider = itemFromListProvider;
this.messageBoxService = messageBoxService;
this.parameterFactory = parameterFactory;
this.definition = definition;
this.itemIcon = new ImageUri($"Icons/document_big.png");
}
internal DatabaseTableSolutionItemProvider(DatabaseTableDefinitionJson definition,
IDatabaseProvider databaseProvider,
ITableOpenService tableOpenService,
bool isCompatible,
bool byDefaultIsHiddenInQuickLoad)
{
this.databaseProvider = databaseProvider;
this.tableOpenService = tableOpenService;
this.definition = definition;
this.itemIcon = new ImageUri($"Icons/document_big.png");
this.isCompatible = isCompatible;
this.ByDefaultHideFromQuickStart = byDefaultIsHiddenInQuickLoad;
}
internal DatabaseTableSolutionItemProvider(DatabaseTableDefinitionJson definition,
IDatabaseTableDataProvider tableDataProvider,
IItemFromListProvider itemFromListProvider,
IMessageBoxService messageBoxService,
IParameterFactory parameterFactory)
{
this.tableDataProvider = tableDataProvider;
this.itemFromListProvider = itemFromListProvider;
this.messageBoxService = messageBoxService;
this.parameterFactory = parameterFactory;
this.definition = definition;
this.itemIcon = new ImageUri($"Resources/SmartScriptGeneric.png");
}
public DatabaseExpressionEvaluator(ICreatureStatCalculatorService statCalculatorService,
IParameterFactory parameterFactory,
DatabaseTableDefinitionJson definition, string expression)
{
this.parameterFactory = parameterFactory;
lexer = new DatabaseEditorExpressionLexer(new AntlrInputStream(expression));
tokens = new CommonTokenStream(lexer);
parser = new DatabaseEditorExpressionParser(tokens);
parser.BuildParseTree = true;
parser.RemoveErrorListeners();
visitor = new ExpressionVisitor(statCalculatorService, parameterFactory, definition);
}
public string GenerateUpdateFieldQuery(DatabaseTableDefinitionJson table, DatabaseEntity entity,
IDatabaseField field)
{
var column = table.TableColumns[field.FieldName];
string primaryKeyColumn = table.TablePrimaryKeyColumnName;
if (column.ForeignTable != null)
{
primaryKeyColumn = table.ForeignTableByName[column.ForeignTable].ForeignKey;
}
return
($"UPDATE {table.TableName} SET `{field.FieldName}` = {field.ToQueryString()} WHERE `{primaryKeyColumn}` = {entity.Key};");
}
private static void GeneratePrimaryKeyDeletion(DatabaseTableDefinitionJson definition, IReadOnlyList <DatabaseKey>?keys,
IMultiQuery query)
{
if (keys == null || keys.Count == 0)
{
return;
}
ITable table;
if (definition.ForeignTable != null)
{
foreach (var foreign in definition.ForeignTable)
{
table = query.Table(foreign.TableName);
if (keys.Count > 1 && keys[0].Count == 1)
{
foreach (var chunk in keys.Select(k => k[0]).Chunk(128))
{
table.WhereIn(foreign.ForeignKeys[0], chunk).Delete();
}
}
else
{
foreach (var key in keys)
{
GenerateWherePrimaryKey(foreign.ForeignKeys, table, key).Delete();
}
}
}
}
table = query.Table(definition.TableName);
if (keys.Count > 1 && keys[0].Count == 1)
{
foreach (var chunk in keys.Select(k => k[0]).Chunk(128))
{
table.WhereIn(definition.TablePrimaryKeyColumnName, chunk).Delete();
}
}
else
{
foreach (var key in keys)
{
GenerateWherePrimaryKey(definition, table, key).Delete();
}
}
}
public IQuery GenerateDeleteQuery(DatabaseTableDefinitionJson table, DatabaseKey key)
{
var query = Queries.BeginTransaction();
if (table.ForeignTable != null)
{
foreach (var foreign in table.ForeignTable)
{
var tableKey = foreign.ForeignKeys[0];
GenerateWherePrimaryKey(foreign.ForeignKeys.Take(table.GroupByKeys.Count).ToList(), query
.Table(foreign.TableName), key).Delete();
}
}
GenerateWherePrimaryKey(table, query.Table(table.TableName), key).Delete();
return(query.Close());
}
public IQuery GenerateUpdateFieldQuery(DatabaseTableDefinitionJson table, DatabaseEntity entity,
IDatabaseField field)
{
var column = table.TableColumns[field.FieldName];
string primaryKeyColumn = table.TablePrimaryKeyColumnName;
if (column.ForeignTable != null)
{
primaryKeyColumn = table.ForeignTableByName[column.ForeignTable].ForeignKey;
}
return(Queries
.Table(table.TableName)
.Where(row => row.Column <uint>(primaryKeyColumn) == entity.Key)
.Set(field.FieldName, field.Object)
.Update());
}
public async Task <ISolutionItem?> TryCreate(DatabaseTableDefinitionJson definition)
{
if (definition.RecordMode == RecordMode.SingleRow)
{
return(await Create(definition, default));
}
Debug.Assert(definition.GroupByKeys.Count == 1);
var parameter = parameterFactory.Factory(definition.Picker);
var key = await itemFromListProvider.GetItemFromList(parameter.HasItems?parameter.Items !: new Dictionary <long, SelectOption>(), false);
if (key.HasValue)
{
return(await Create(definition, new DatabaseKey(key.Value)));
}
return(null);
}
public async Task <ISolutionItem?> Create(DatabaseTableDefinitionJson definition, DatabaseKey key)
{
if (definition.RecordMode == RecordMode.MultiRecord)
{
return(new DatabaseTableSolutionItem(key, false, definition.Id, definition.IgnoreEquality));
}
if (definition.RecordMode == RecordMode.SingleRow)
{
return(new DatabaseTableSolutionItem(definition.Id, definition.IgnoreEquality));
}
else
{
var data = await tableDataProvider.Load(definition.Id, null, null, null, new [] { key });
if (data == null)
{
return(null);
}
if (data.Entities.Count == 0)
{
return(null);
}
if (!data.Entities[0].ExistInDatabase)
{
if (!await messageBoxService.ShowDialog(new MessageBoxFactory <bool>()
.SetTitle("Entity doesn't exist in database")
.SetMainInstruction($"Entity {data.Entities[0].Key} doesn't exist in the database")
.SetContent(
"WoW Database Editor will be generating DELETE/INSERT query instead of UPDATE. Do you want to continue?")
.WithYesButton(true)
.WithNoButton(false).Build()))
{
return(null);
}
}
return(new DatabaseTableSolutionItem(data.Entities[0].Key, data.Entities[0].ExistInDatabase, definition.Id, definition.IgnoreEquality));
}
}
public IQuery GenerateDeleteQuery(DatabaseTableDefinitionJson table, uint key)
{
var query = Queries.BeginTransaction();
if (table.ForeignTable != null)
{
foreach (var foreign in table.ForeignTable)
{
var tableKey = foreign.ForeignKeys[0];
query
.Table(foreign.TableName)
.Where(row => row.Column <uint>(tableKey) == key)
.Delete();
}
}
query
.Table(table.TableName)
.Where(r => r.Column <uint>(table.TablePrimaryKeyColumnName) == key)
.Delete();
return(query.Close());
}
private async Task <bool> CheckIfItemIsOpened(DatabaseTableSolutionItem fakeSolutionItem,
DatabaseTableDefinitionJson definition)
{
bool openIsNoSaveMode = false;
if (IsItemAlreadyOpened(fakeSolutionItem, out var openedDocument))
{
var result = await messageBoxService.ShowDialog(new MessageBoxFactory <bool>()
.SetTitle("Document is already opened")
.SetMainInstruction($"{definition.Id} is already opened")
.SetContent(
"This table is already being edited and you have an active session.\n Editing the same table in a new window would cause a session data loss.\n\nTherefore you can either close the current document or open the table without save feature enabled (you can still generate sql).")
.WithButton("Close document", true)
.WithButton("Open table without save", false)
.Build());
if (result)
{
await openedDocument.CloseCommand !.ExecuteAsync();
openIsNoSaveMode = documentManager.Value.OpenedDocuments.Contains(openedDocument);
if (openIsNoSaveMode)
{
await messageBoxService.ShowDialog(new MessageBoxFactory <Unit>()
.SetTitle("Document is still opened")
.SetMainInstruction("Document is still opened")
.SetContent("You didn't close the document. Opening the table without the save feature.")
.Build());
}
}
else
{
openIsNoSaveMode = true;
}
}
return(openIsNoSaveMode);
}
public TableItemViewModel(string tableName, DatabaseTableDefinitionJson definition)
{
TableName = tableName;
Definition = definition;
Icon = new ImageUri(definition.IconPath ?? "Icons/document.png");
}
public IQuery GenerateDeleteQuery(DatabaseTableDefinitionJson table, DatabaseEntity entity) => GenerateDeleteQuery(table, entity.Key);
internal DatabaseTableSolutionItemNumberedProvider(DatabaseTableDefinitionJson definition, IDatabaseProvider databaseProvider, ITableOpenService tableOpenService, bool isCompatible, bool byDefaultIsHiddenInQuickLoad) : base(definition, databaseProvider, tableOpenService, isCompatible, byDefaultIsHiddenInQuickLoad)
{
}
public EntityComparer(DatabaseTableDefinitionJson definition)
{
this.definition = definition;
}
public string GenerateDeleteQuery(DatabaseTableDefinitionJson table, DatabaseEntity entity)
{
return($"DELETE FROM {table.TableName} WHERE {table.TablePrimaryKeyColumnName} = {entity.Key};");
}
public DatabaseTableData(DatabaseTableDefinitionJson definitionJson, IReadOnlyList <DatabaseEntity> entities)
{
TableDefinition = definitionJson;
Entities = entities;
}
public IDatabaseTableData?CreateDatabaseTable(DatabaseTableDefinitionJson tableDefinition,
uint[] keys,
IList <Dictionary <string, (System.Type type, object value)> > fieldsFromDb)
private static IWhere GenerateWherePrimaryKey(DatabaseTableDefinitionJson definition, ITable table, DatabaseKey key)
{
return(GenerateWherePrimaryKey(definition.GroupByKeys, table, key));
}
