/// <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));
}
