Beispiel #1
0
        /// <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);
        }
Beispiel #2
0
        /// <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);
        }