/// <summary>
/// <para>Compare two dictionaries (of the same types) and creates SQL inserts
/// or updates accordingly.</para>
/// <remarks>Second dictionary can be null (only inserts queries will be produced)</remarks>
/// <remarks>Use DBTableName and DBFieldName attributes to specify table and field names, in TK</remarks>
/// </summary>
/// <typeparam name="T">Type of the first primary key</typeparam>
/// /// <typeparam name="TG">Type of the second primary key</typeparam>
/// <typeparam name="TK">Type of the WDB struct (field names and types must match DB field name and types)</typeparam>
/// <typeparam name="TH"></typeparam>
/// <param name="dict1">Dictionary retrieved from parser</param>
/// <param name="dict2">Dictionary retrieved from DB</param>
/// <param name="storeType1">(T) Are we dealing with Spells, Quests, Units, ...?</param>
/// <param name="storeType2">(TG) Are we dealing with Spells, Quests, Units, ...?</param>
/// <param name="storeType3">(TH) Are we dealing with Spells, Quests, Units, ...?</param>
/// <param name="primaryKeyName1">The name of the first primary key</param>
/// <param name="primaryKeyName2">The name of the second primary key</param>
/// <param name="primaryKeyName3">The name of the third primary key</param>
/// <returns>A string containing full SQL queries</returns>
public static string CompareDicts <T, TG, TH, TK>(StoreDictionary <Tuple <T, TG, TH>, TK> dict1, StoreDictionary <Tuple <T, TG, TH>, TK> dict2, StoreNameType storeType1, StoreNameType storeType2, StoreNameType storeType3, string primaryKeyName1, string primaryKeyName2, string primaryKeyName3)
{
var tableAttrs = (DBTableNameAttribute[])typeof(TK).GetCustomAttributes(typeof(DBTableNameAttribute), false);
if (tableAttrs.Length <= 0)
{
return(string.Empty);
}
var tableName = tableAttrs[0].Name;
var fields = Utilities.GetFieldsAndAttribute <TK, DBFieldNameAttribute>();
if (fields == null)
{
return(string.Empty);
}
fields.RemoveAll(field => field.Item2.Name == null);
var rowsIns = new List <QueryBuilder.SQLInsertRow>();
var rowsUpd = new List <QueryBuilder.SQLUpdateRow>();
foreach (var elem1 in Settings.SQLOrderByKey ? dict1.OrderBy(blub => blub.Key).ToList() : dict1.ToList())
{
if (dict2 != null && dict2.ContainsKey(elem1.Key)) // update
{
var row = new QueryBuilder.SQLUpdateRow();
foreach (var field in fields)
{
var elem2 = dict2[elem1.Key];
var val1 = field.Item1.GetValue(elem1.Value.Item1);
var val2 = field.Item1.GetValue(elem2.Item1);
var arr1 = val1 as Array;
if (arr1 != null)
{
var arr2 = (Array)val2;
var isString = arr1.GetType().GetElementType() == typeof(string);
for (var i = 0; i < field.Item2.Count; i++)
{
var value1 = i >= arr1.Length ? (isString ? (object)string.Empty : 0) : arr1.GetValue(i);
var value2 = i >= arr2.Length ? (isString ? (object)string.Empty : 0) : arr2.GetValue(i);
if (!Utilities.EqualValues(value1, value2))
{
row.AddValue(field.Item2.Name + (field.Item2.StartAtZero ? i : i + 1), value1);
}
}
continue;
}
if (!Utilities.EqualValues(val1, val2))
{
row.AddValue(field.Item2.Name, val1);
}
}
var key1 = Convert.ToUInt32(elem1.Key.Item1);
var key2 = Convert.ToUInt32(elem1.Key.Item2);
var key3 = Convert.ToUInt32(elem1.Key.Item3);
row.AddWhere(primaryKeyName1, key1);
row.AddWhere(primaryKeyName2, key2);
row.AddWhere(primaryKeyName3, key3);
var key1Name = storeType1 != StoreNameType.None ?
StoreGetters.GetName(storeType1, (int)key1, false) :
elem1.Key.Item1.ToString();
var key2Name = storeType2 != StoreNameType.None ?
StoreGetters.GetName(storeType2, (int)key2, false) :
elem1.Key.Item2.ToString();
var key3Name = storeType3 != StoreNameType.None ?
StoreGetters.GetName(storeType3, (int)key3, false) :
elem1.Key.Item2.ToString();
row.Comment = key1Name + " - " + key2Name + " - " + key3Name;
row.Table = tableName;
if (row.ValueCount == 0)
{
continue;
}
var lastField = fields[fields.Count - 1];
if (lastField.Item2.Name == "VerifiedBuild")
{
var buildvSniff = (int)lastField.Item1.GetValue(elem1.Value.Item1);
var buildvDB = (int)lastField.Item1.GetValue(dict2[elem1.Key].Item1);
if (buildvDB > buildvSniff) // skip update if DB already has a VerifiedBuild higher than this one
{
continue;
}
}
rowsUpd.Add(row);
}
else // insert new
{
var row = new QueryBuilder.SQLInsertRow();
row.AddValue(primaryKeyName1, elem1.Key.Item1);
row.AddValue(primaryKeyName2, elem1.Key.Item2);
row.AddValue(primaryKeyName3, elem1.Key.Item3);
var key1 = Convert.ToUInt32(elem1.Key.Item1);
var key2 = Convert.ToUInt32(elem1.Key.Item2);
var key3 = Convert.ToUInt32(elem1.Key.Item3);
var key1Name = storeType1 != StoreNameType.None ?
StoreGetters.GetName(storeType1, (int)key1, false) :
elem1.Key.Item1.ToString();
var key2Name = storeType2 != StoreNameType.None ?
StoreGetters.GetName(storeType2, (int)key2, false) :
elem1.Key.Item2.ToString();
var key3Name = storeType3 != StoreNameType.None ?
StoreGetters.GetName(storeType3, (int)key3, false) :
elem1.Key.Item2.ToString();
row.Comment = key1Name + " - " + key2Name + " - " + key3Name;
foreach (var field in fields)
{
if (field.Item1.FieldType.BaseType == typeof(Array))
{
var arr = (Array)field.Item1.GetValue(elem1.Value.Item1);
if (arr == null)
{
continue;
}
for (var i = 0; i < arr.Length; i++)
{
row.AddValue(field.Item2.Name + (field.Item2.StartAtZero ? i : i + 1), arr.GetValue(i));
}
continue;
}
var val = field.Item1.GetValue(elem1.Value.Item1);
if (val == null && field.Item1.FieldType == typeof(string))
{
val = string.Empty;
}
row.AddValue(field.Item2.Name, val);
}
rowsIns.Add(row);
}
}
var result = new QueryBuilder.SQLInsert(tableName, rowsIns, deleteDuplicates: false, primaryKeyNumber: 3).Build() +
new QueryBuilder.SQLUpdate(rowsUpd).Build();
return(result);
}
/// <summary>
/// <para>Compare two dictionaries (of the same types) and creates SQL inserts
/// or updates accordingly.</para>
/// <remarks>Second dictionary can be null (only inserts queries will be produced)</remarks>
/// <remarks>Use DBTableName and DBFieldName attributes to specify table and field names, in TK</remarks>
/// </summary>
/// <typeparam name="T">Type of the primary key (uint)</typeparam>
/// <typeparam name="TK">Type of the WDB struct (field types must match DB field)</typeparam>
/// <param name="dict1">Dictionary retrieved from parser</param>
/// <param name="dict2">Dictionary retrieved from DB</param>
/// <param name="storeType">Are we dealing with Spells, Quests, Units, ...?</param>
/// <param name="primaryKeyName">The name of the primary key, usually "entry"</param>
/// <returns>A string containing full SQL queries</returns>
public static string CompareDicts <T, TK>(StoreDictionary <T, TK> dict1, StoreDictionary <T, TK> dict2, StoreNameType storeType, string primaryKeyName = "entry")
{
var tableAttrs = (DBTableNameAttribute[])typeof(TK).GetCustomAttributes(typeof(DBTableNameAttribute), false);
if (tableAttrs.Length <= 0)
{
return(string.Empty);
}
var tableName = tableAttrs[0].Name;
var fields = Utilities.GetFieldsAndAttribute <TK, DBFieldNameAttribute>();
if (fields == null)
{
return(string.Empty);
}
var rowsIns = new List <QueryBuilder.SQLInsertRow>();
var rowsUpd = new List <QueryBuilder.SQLUpdateRow>();
foreach (var elem1 in dict1)
{
if (dict2 != null && dict2.ContainsKey(elem1.Key)) // update
{
var row = new QueryBuilder.SQLUpdateRow();
foreach (var field in fields)
{
var elem2 = dict2[elem1.Key];
var val1 = field.Item1.GetValue(elem1.Value.Item1);
var val2 = field.Item1.GetValue(elem2.Item1);
var arr1 = val1 as Array;
if (arr1 != null)
{
var arr2 = (Array)val2;
var isString = arr1.GetType().GetElementType() == typeof(string);
for (var i = 0; i < field.Item2.Count; i++)
{
var value1 = i >= arr1.Length ? (isString ? (object)string.Empty : 0) : arr1.GetValue(i);
var value2 = i >= arr2.Length ? (isString ? (object)string.Empty : 0) : arr2.GetValue(i);
if (!Utilities.EqualValues(value1, value2))
{
row.AddValue(field.Item2.Name + (field.Item2.StartAtZero ? i : i + 1), value1);
}
}
continue;
}
if ((val2 is Array) && val1 == null)
{
continue;
}
if (!Utilities.EqualValues(val1, val2))
{
row.AddValue(field.Item2.Name, val1);
}
}
var key = Convert.ToUInt32(elem1.Key);
row.AddWhere(primaryKeyName, key);
row.Comment = StoreGetters.GetName(storeType, (int)key, false);
row.Table = tableName;
if (row.ValueCount == 0)
{
continue;
}
var lastField = fields[fields.Count - 1];
if (lastField.Item2.Name == "WDBVerified")
{
var wdbvSniff = (int)lastField.Item1.GetValue(elem1.Value.Item1);
var wdbvDB = (int)lastField.Item1.GetValue(dict2[elem1.Key].Item1);
if (wdbvDB > wdbvSniff) // skip update if DB already has a WDBVerified higher than this one
{
continue;
}
}
rowsUpd.Add(row);
}
else // insert new
{
var row = new QueryBuilder.SQLInsertRow();
row.AddValue(primaryKeyName, elem1.Key);
row.Comment = StoreGetters.GetName(storeType, Convert.ToInt32(elem1.Key), false);
foreach (var field in fields)
{
if (field.Item1.FieldType.BaseType == typeof(Array))
{
var arr = (Array)field.Item1.GetValue(elem1.Value.Item1);
if (arr == null)
{
continue;
}
for (var i = 0; i < arr.Length; i++)
{
row.AddValue(field.Item2.Name + (field.Item2.StartAtZero ? i : i + 1), arr.GetValue(i));
}
continue;
}
row.AddValue(field.Item2.Name, field.Item1.GetValue(elem1.Value.Item1));
}
rowsIns.Add(row);
}
}
var result = new QueryBuilder.SQLInsert(tableName, rowsIns, deleteDuplicates: false).Build() +
new QueryBuilder.SQLUpdate(rowsUpd).Build();
return(result);
}