/// <summary> /// String representation of the field or group of fields /// </summary> /// <returns>name</returns> public override string ToString() { if (Name == null) { return(null); } if (_multipleFields) { var result = new StringBuilder(); for (var i = 1; i <= Count; i++) { result.Append('`'); result.Append(Name); result.Append(StartAtZero ? i - 1 : i); result.Append('`'); if (i != Count) { result.Append(","); } } return(result.ToString()); } return(SQLUtil.AddBackQuotes(Name)); }
/// <summary> /// Adds a field-value pair to be updated /// </summary> /// <param name="field">The field name associated with the value</param> /// <param name="value">Any value (string, number, enum, ...)</param> /// <param name="isFlag">If set to true the value, "0x" will be append to value</param> /// <param name="noQuotes">If value is a string and this is set to true, value will not be 'quoted' (SQL variables)</param> public void AddValue(string field, object value, bool isFlag = false, bool noQuotes = false) { if (value != null) { _values.Add(new KeyValuePair <string, object>(SQLUtil.AddBackQuotes(field), SQLUtil.ToSQLValue(value, isFlag, noQuotes))); } }
/// <summary> /// Adds a field-value pair to be updated. If value is equal to defaultValue, value will NOT be added to this update row /// </summary> /// <param name="field">The field name associated with the value</param> /// <param name="value">Any value (string, number, enum, ...)</param> /// <param name="defaultValue">Default value (usually defined in database structure)</param> /// <param name="isFlag">If set to true the value, "0x" will be append to value</param> /// <param name="noQuotes">If value is a string and this is set to true, value will not be 'quoted' (SQL variables)</param> public void AddValue <T>(string field, T value, T defaultValue, bool isFlag = false, bool noQuotes = false) { // T used because it is compile time safe. We know that value and defaultValue got the same type // ReSharper disable CompareNonConstrainedGenericWithNull if (value == null) { // ReSharper restore CompareNonConstrainedGenericWithNull return; } if (value is float || value is double) { if (Math.Abs(Convert.ToDouble(value) - Convert.ToDouble(defaultValue)) < 0.000001) { return; } } if (value.Equals(defaultValue)) { return; } Values[SQLUtil.AddBackQuotes(field)] = SQLUtil.ToSQLValue(value, isFlag, noQuotes); }
/// <summary> /// Constructs the actual query /// </summary> /// <returns>Insert query header</returns> public string Build() { var query = new StringBuilder(); query.Append("INSERT "); query.Append(Ignore ? "IGNORE " : string.Empty); query.Append("INTO "); query.Append(SQLUtil.AddBackQuotes(Table)); if (TableStructure.Count != 0) { query.Append(" ("); var count = 0; foreach (var column in TableStructure) { count++; query.Append(SQLUtil.AddBackQuotes(column)); // Append comma if not end of fields if (TableStructure.Count != count) { query.Append(SQLUtil.CommaSeparator); } } query.Append(")"); } query.Append(" VALUES" + Environment.NewLine); return(query.ToString()); }
/// <summary> /// Constructs the actual query /// </summary> /// <returns>Full insert AND delete queries</returns> public string Build() { // If we only have rows with comment, do not print any query if (Rows.All(row => row.NoData)) // still true if row count = 0 { return("-- " + SQLUtil.AddBackQuotes(Table) + " has empty data." + Environment.NewLine); } var query = new StringBuilder(); query.Append(Delete); // Can be empty query.Append(InsertHeader); var count = 0; foreach (var row in Rows) { if (count >= MaxRowsPerInsert) { query.ReplaceLast(',', ';'); query.Append(InsertHeader); count = 0; } query.Append(row.Build()); count++; } query.Append(Environment.NewLine); return(query.ReplaceLast(',', ';').ToString()); }
/// <summary> /// Adds a field-value pair to be updated /// </summary> /// <param name="field">The field name associated with the value</param> /// <param name="value">Any value (string, number, enum, ...)</param> /// <param name="isFlag">If set to true the value, "0x" will be append to value</param> /// <param name="noQuotes">If value is a string and this is set to true, value will not be 'quoted' (SQL variables)</param> public void AddValue(string field, object value, bool isFlag = false, bool noQuotes = false) { if (value == null) { value = ""; } Values[SQLUtil.AddBackQuotes(field)] = SQLUtil.ToSQLValue(value, isFlag, noQuotes); }
/// <summary> /// Constructs the actual query /// </summary> /// <returns>Full insert AND delete queries</returns> public string Build() { // If we only have rows with comment, do not print any query if (Rows.All(row => row.NoData)) // still true if row count = 0 { return("-- " + SQLUtil.AddBackQuotes(Table) + " has empty data." + Environment.NewLine); } var query = new StringBuilder(); query.Append(Delete); // Can be empty query.Append(InsertHeader); var count = 0; foreach (var row in Rows) { if (count >= MaxRowsPerInsert && !_deleteDuplicates) { query.ReplaceLast(',', ';'); query.Append(InsertHeader); count = 0; } query.Append(row.Build()); count++; } query.Append(Environment.NewLine); // This is easier to implement that comparing raw objects in each row // and certainly faster. Imagine comparing 1k rows of <string, int, int, emote, YouGotIt> if (_deleteDuplicates) { var str = String.Join("\n", query.ToString().Split('\n').Distinct()); // Do not use Enviroment.NewLine query.Clear(); query.Append(str); } query.ReplaceLast(',', ';'); return(query.ToString()); }
/// <summary> /// String representation of the field or group of fields /// </summary> /// <returns>name</returns> public override string ToString() { if (Name == null) { return(string.Empty); } if (!_multipleFields) { return(SQLUtil.AddBackQuotes(Name)); } StringBuilder result = new StringBuilder(); for (int i = 1; i <= Count; i++) { result.Append(SQLUtil.AddBackQuotes(Name + (StartAtZero ? i - 1 : i))); if (i != Count) { result.Append(SQLUtil.CommaSeparator); } } return(result.ToString()); }
/// <summary> /// Constructs the actual query /// </summary> /// <returns>A single update query</returns> public virtual string Build() { StringBuilder query = new StringBuilder(); if (CommentOut) { query.Append("-- "); } // Return empty if there are no values or where clause or no table name set if (!WhereClause.HasConditions) { return(string.Empty); } query.Append("UPDATE "); query.Append(SQLUtil.GetTableName <T>()); query.Append(" SET "); bool hasValues = false; foreach (var field in _databaseFields) { object value = field.Item2.GetValue(_value.Data); Array arr = value as Array; if (arr != null) { for (int i = 0; i < arr.Length; i++) { object v = arr.GetValue(i); if (v == null) { continue; } query.Append(SQLUtil.AddBackQuotes(field.Item3.First().Name + (field.Item3.First().StartAtZero ? i : i + 1))); query.Append("="); query.Append(SQLUtil.ToSQLValue(v, noQuotes: field.Item3.Any(a => a.NoQuotes))); query.Append(SQLUtil.CommaSeparator); hasValues = true; } continue; } if (value == null) { continue; } if (field.Item2.Name != "VerifiedBuild" || !Settings.SkipOnlyVerifiedBuildUpdateRows) { hasValues = true; } query.Append(field.Item1); query.Append("="); query.Append(SQLUtil.ToSQLValue(value, noQuotes: field.Item3.Any(a => a.NoQuotes))); query.Append(SQLUtil.CommaSeparator); } if (!hasValues) { return(string.Empty); } query.Remove(query.Length - SQLUtil.CommaSeparator.Length, SQLUtil.CommaSeparator.Length); // remove last ", " query.Append(" WHERE "); query.Append(WhereClause.Build()); query.Append(";"); if (!string.IsNullOrWhiteSpace(_value.Comment)) { query.Append(" -- " + _value.Comment); } return(query.ToString()); }
/// <summary> /// Constructs the actual query /// </summary> /// <returns>Delete query</returns> public string Build() { var query = new StringBuilder(); if (_between) { query.Append("DELETE FROM "); query.Append(SQLUtil.AddBackQuotes(Table)); query.Append(" WHERE "); switch (_primaryKeyNumber) { case 2: query.Append(SQLUtil.AddBackQuotes(PrimaryKey)); query.Append(" BETWEEN "); query.Append(ValuesBetweenDouble.Item1); query.Append(" AND "); query.Append(ValuesBetweenDouble.Item2); query.Append(";"); break; case 3: query.Append(SQLUtil.AddBackQuotes(PrimaryKey)); query.Append(" BETWEEN "); query.Append(ValuesBetweenTriple.Item1); query.Append(" AND "); query.Append(ValuesBetweenTriple.Item2); query.Append(" AND "); query.Append(ValuesBetweenTriple.Item3); query.Append(";"); break; } } else { switch (_primaryKeyNumber) { case 2: { var counter = 0; var rowsPerDelete = 0; query.Append("DELETE FROM "); query.Append(SQLUtil.AddBackQuotes(Table)); query.Append(" WHERE "); foreach (var tuple in ValuesDouble) { counter++; rowsPerDelete++; query.Append("("); query.Append(SQLUtil.AddBackQuotes(PrimaryKeyDouble.Item1)); query.Append("="); query.Append(tuple.Item1); query.Append(" AND "); query.Append(SQLUtil.AddBackQuotes(PrimaryKeyDouble.Item2)); query.Append("="); query.Append(tuple.Item2); query.Append(")"); // Append an OR if not end of items if (rowsPerDelete < 25 && ValuesDouble.Count != counter) { query.Append(" OR "); } else if (rowsPerDelete == 25) { rowsPerDelete = 0; query.Append(";"); if (ValuesDouble.Count != counter) { query.Append(Environment.NewLine); query.Append("DELETE FROM "); query.Append(SQLUtil.AddBackQuotes(Table)); query.Append(" WHERE "); } } else if (ValuesDouble.Count == counter) { query.Append(";"); } } break; } case 3: { var counter = 0; var rowsPerDelete = 0; query.Append("DELETE FROM "); query.Append(SQLUtil.AddBackQuotes(Table)); query.Append(" WHERE "); foreach (var tuple in ValuesTriple) { counter++; rowsPerDelete++; query.Append("("); query.Append(SQLUtil.AddBackQuotes(PrimaryKeyTriple.Item1)); query.Append("="); query.Append(tuple.Item1); query.Append(" AND "); query.Append(SQLUtil.AddBackQuotes(PrimaryKeyTriple.Item2)); query.Append("="); query.Append(tuple.Item2); query.Append(" AND "); query.Append(SQLUtil.AddBackQuotes(PrimaryKeyTriple.Item3)); query.Append("="); query.Append(tuple.Item3); query.Append(")"); // Append an OR if not end of items if (rowsPerDelete < 25 && ValuesTriple.Count != counter) { query.Append(" OR "); } else if (rowsPerDelete == 25) { rowsPerDelete = 0; query.Append(";"); if (ValuesTriple.Count != counter) { query.Append(Environment.NewLine); query.Append("DELETE FROM "); query.Append(SQLUtil.AddBackQuotes(Table)); query.Append(" WHERE "); } } else if (ValuesTriple.Count == counter) { query.Append(";"); } } break; } default: { query.Append("DELETE FROM "); query.Append(SQLUtil.AddBackQuotes(Table)); query.Append(" WHERE "); query.Append(SQLUtil.AddBackQuotes(PrimaryKey)); query.Append(Values.Count == 1 ? "=" : " IN ("); var counter = 0; var rowsPerDelete = 0; foreach (var entry in Values) { counter++; rowsPerDelete++; query.Append(entry); // Append comma if not end of items if (Values.Count != counter) { query.Append(SQLUtil.CommaSeparator); } else if (Values.Count != 1 && Values.Count != counter) { query.Append(")"); } else if (rowsPerDelete == 25) { rowsPerDelete = 0; query.Append(";"); if (Values.Count != counter) { query.Append(Environment.NewLine); query.Append("DELETE FROM "); query.Append(SQLUtil.AddBackQuotes(Table)); query.Append(" WHERE "); query.Append(SQLUtil.AddBackQuotes(PrimaryKey)); query.Append(Values.Count == 1 ? "=" : " IN ("); } } else if (Values.Count == counter) { if (Values.Count != 1) { query.Append(")"); } query.Append(";"); } } break; } } } query.Append(Environment.NewLine); return(query.ToString()); }
/// <summary> /// Adds to this row what will be updated /// </summary> /// <param name="field">The field name associated with the value</param> /// <param name="value">The value used in the where clause</param> public void AddWhere(string field, object value) { WhereClause.Add(new KeyValuePair <string, object>(SQLUtil.AddBackQuotes(field), value)); }
/// <summary> /// Constructs the actual query /// </summary> /// <returns>Delete query</returns> public string Build() { var query = new StringBuilder(); query.Append("DELETE FROM "); query.Append(SQLUtil.AddBackQuotes(Table)); query.Append(" WHERE "); if (_between) { query.Append(SQLUtil.AddBackQuotes(PrimaryKey)); query.Append(" BETWEEN "); query.Append(ValuesBetween.Item1); query.Append(" AND "); query.Append(ValuesBetween.Item2); query.Append(";"); } else { if (_double) { var counter = 0; foreach (var tuple in ValuesDouble) { counter++; query.Append("("); query.Append(SQLUtil.AddBackQuotes(PrimaryKeys.Item1)); query.Append("="); query.Append(tuple.Item1); query.Append(" AND "); query.Append(SQLUtil.AddBackQuotes(PrimaryKeys.Item2)); query.Append("="); query.Append(tuple.Item2); query.Append(")"); // Append an OR if not end of items if (ValuesDouble.Count != counter) { query.Append(" OR "); } } query.Append(";"); } else { query.Append(SQLUtil.AddBackQuotes(PrimaryKey)); query.Append(Values.Count == 1 ? "=" : " IN ("); var counter = 0; foreach (var entry in Values) { counter++; query.Append(entry); // Append comma if not end of items if (Values.Count != counter) { query.Append(SQLUtil.CommaSeparator); } else if (Values.Count != 1) { query.Append(")"); } } query.Append(";"); } } query.Append(Environment.NewLine); return(query.ToString()); }
/// <summary> /// Gets from `world` database a dictionary of the given struct/class. /// Structs fields type must match the type of the DB columns. /// DB columns names are set by using DBFieldNameAttribute. /// </summary> /// <typeparam name="T">Type of the elements of the list of entries (usually uint)</typeparam> /// <typeparam name="TK">Type of the struct</typeparam> /// <param name="entries">List of entries to select from DB</param> /// <param name="primaryKeyName"></param> /// <param name="database"></param> /// <returns>Dictionary of structs of type TK</returns> public static StoreDictionary <T, TK> GetDict <T, TK>(List <T> entries, string primaryKeyName = "entry", string database = null) { if (entries.Count == 0) { return(null); } // TODO: Add new config option "Verify data against DB" if (!SQLConnector.Enabled) { return(null); } var tableAttrs = (DBTableNameAttribute[])typeof(TK).GetCustomAttributes(typeof(DBTableNameAttribute), false); if (tableAttrs.Length <= 0) { return(null); } var tableName = tableAttrs[0].Name; var fields = Utilities.GetFieldsAndAttribute <TK, DBFieldNameAttribute>(); fields.RemoveAll(field => field.Item2.Name == null); var fieldCount = 1; var fieldNames = new StringBuilder(); fieldNames.Append(SQLUtil.AddBackQuotes(primaryKeyName) + ","); foreach (var field in fields) { fieldNames.Append(field.Item2); fieldNames.Append(","); fieldCount += field.Item2.Count; } var query = string.Format("SELECT {0} FROM {1}.{2} WHERE {3} IN ({4})", fieldNames.ToString().TrimEnd(','), database ?? Settings.TDBDatabase, tableName, primaryKeyName, String.Join(",", entries)); var dict = new Dictionary <T, TK>(entries.Count); using (var reader = SQLConnector.ExecuteQuery(query)) { if (reader == null) { return(null); } while (reader.Read()) { var instance = (TK)Activator.CreateInstance(typeof(TK)); var values = new object[fieldCount]; var count = reader.GetValues(values); if (count != fieldCount) { throw new InvalidConstraintException( "Number of fields from DB is different of the number of fields with DBFieldName attribute"); } var i = 1; foreach (var field in fields) { if (values[i] is DBNull && field.Item1.FieldType == typeof(string)) { field.Item1.SetValueDirect(__makeref(instance), string.Empty); } else if (field.Item1.FieldType.BaseType == typeof(Enum)) { field.Item1.SetValueDirect(__makeref(instance), Enum.Parse(field.Item1.FieldType, values[i].ToString())); } else if (field.Item1.FieldType.BaseType == typeof(Array)) { var arr = Array.CreateInstance(field.Item1.FieldType.GetElementType(), field.Item2.Count); for (var j = 0; j < arr.Length; j++) { var elemType = arr.GetType().GetElementType(); var val = elemType.IsEnum ? Enum.Parse(elemType, values[i + j].ToString()) : Convert.ChangeType(values[i + j], elemType); arr.SetValue(val, j); } field.Item1.SetValueDirect(__makeref(instance), arr); } else if (field.Item1.FieldType == typeof(bool)) { field.Item1.SetValueDirect(__makeref(instance), Convert.ToBoolean(values[i])); } else { field.Item1.SetValueDirect(__makeref(instance), values[i]); } i += field.Item2.Count; } T key = (T)values[0]; if (!dict.ContainsKey(key)) { dict.Add(key, instance); } } } return(new StoreDictionary <T, TK>(dict)); }