public void InsertOnDuplicateUpdate(DB_Table table, Dictionary <string, object> columnsValues, MySqlTransaction transaction = null)
{
#region Contracts
if (columnsValues == null)
{
throw new System.ArgumentNullException(nameof(columnsValues));
}
if (columnsValues.Count == 0)
{
throw new System.ArgumentException("Словарь с именами и значениями столбцов должен содержать хотя бы один элемент.", nameof(columnsValues));
}
#endregion
MySqlCommand cmd = transaction != null ? new MySqlCommand("", _Connection, transaction) : new MySqlCommand("", _Connection);
string columns = "", values = "", update = "";
byte count = 1;
foreach (var cv in columnsValues)
{
columns += cv.Key + ", ";
string paramName = "@p" + count;
values += paramName + ", ";
update += cv.Key + " = " + paramName + ", ";
cmd.Parameters.AddWithValue(paramName, cv.Value);
count++;
}
columns = columns.Remove(columns.Length - 2);
values = values.Remove(values.Length - 2);
update = update.Remove(update.Length - 2);
cmd.CommandText = "INSERT INTO " + GetTableName(table) + " (" + columns + ") VALUES (" + values + ") ON DUPLICATE KEY UPDATE " + update + ";";
cmd.ExecuteNonQuery();
}
public void Delete(DB_Table table, Dictionary <string, object> whereColumnValues, MySqlTransaction transaction = null)
{
#region Contracts
if (whereColumnValues == null)
{
throw new System.ArgumentNullException(nameof(whereColumnValues));
}
if (whereColumnValues.Count == 0)
{
throw new System.ArgumentException("Словарь с параметрами фильтрации должен содержать хотя бы один элемент.", nameof(whereColumnValues));
}
#endregion
MySqlCommand cmd = transaction != null ? new MySqlCommand("", _Connection, transaction) : new MySqlCommand("", _Connection);
byte count = 1;
string where = "";
foreach (var cv in whereColumnValues)
{
string paramName = "@p" + count;
where += cv.Key + " = " + paramName + " AND ";
cmd.Parameters.AddWithValue(paramName, cv.Value);
count++;
}
where = where.Remove(where.Length - 5);
cmd.CommandText = "DELETE FROM " + GetTableName(table) + " WHERE " + where + ";";
cmd.ExecuteNonQuery();
}
public void Update(DB_Table table, Dictionary <string, object> columnsValues, Dictionary <string, object> whereColumnValues, MySqlTransaction transaction = null)
{
#region Contracts
if (columnsValues == null)
{
throw new System.ArgumentNullException(nameof(columnsValues));
}
if (whereColumnValues == null)
{
throw new System.ArgumentNullException(nameof(whereColumnValues));
}
if (columnsValues.Count == 0)
{
throw new System.ArgumentException("Словарь с именами и значениями столбцов должен содержать хотя бы один элемент.", nameof(columnsValues));
}
if (whereColumnValues.Count == 0)
{
throw new System.ArgumentException("Список с параметрами фильтрации должен содержать хотя бы один элемент.", nameof(whereColumnValues));
}
#endregion
MySqlCommand cmd = transaction != null ? new MySqlCommand("", _Connection, transaction) : new MySqlCommand("", _Connection);
string set = "";
byte count = 1;
foreach (var cv in columnsValues)
{
string paramName = "@sp" + count;
set += cv.Key + " = " + paramName + ", ";
cmd.Parameters.AddWithValue(paramName, cv.Value);
count++;
}
string where = "";
count = 1;
foreach (var cv in whereColumnValues)
{
string paramName = "@wp" + count;
where += cv.Key + " = " + paramName + " AND ";
cmd.Parameters.AddWithValue(paramName, cv.Value);
count++;
}
set = set.Remove(set.Length - 2);
where = where.Remove(where.Length - 5);
cmd.CommandText = "UPDATE " + GetTableName(table) + " SET " + set + " WHERE " + where + ";";
cmd.ExecuteNonQuery();
}
public List <object[]> Select(DB_Table table, params string[] fields)
{
#region Contracts
if (fields == null)
{
throw new System.ArgumentNullException(nameof(fields));
}
#endregion
MySqlCommand cmd = new MySqlCommand("", _Connection);
if (fields.Length == 0)
{
cmd.CommandText = GetTableName(table);
cmd.CommandType = System.Data.CommandType.TableDirect;
}
else
{
cmd.CommandText = "SELECT " + string.Join(", ", fields) + " FROM " + GetTableName(table) + ";";
}
return(ExecuteSelect(cmd));
}
public void UpdateData(
DB_Table table,
IEnumerable <object[]> oldDataList,
IEnumerable <object[]> newDataList,
string[] fieldsNames,
string[] keyFieldsNames,
MySql.Data.MySqlClient.MySqlTransaction transaction)
{
#region Contracts
if (oldDataList == null)
{
throw new System.ArgumentNullException(nameof(oldDataList));
}
if (newDataList == null)
{
throw new System.ArgumentNullException(nameof(newDataList));
}
if (fieldsNames == null)
{
throw new System.ArgumentNullException(nameof(fieldsNames));
}
if (keyFieldsNames == null)
{
throw new System.ArgumentNullException(nameof(keyFieldsNames));
}
if (transaction == null)
{
throw new System.ArgumentNullException(nameof(transaction));
}
if (fieldsNames.Length == 0)
{
throw new System.ArgumentException("Массив с именами полей должен содержать хотя бы один элемент.", nameof(fieldsNames));
}
if (keyFieldsNames.Length == 0)
{
throw new System.ArgumentException("Массив с именами ключевых полей должен содержать хотя бы один элемент.", nameof(keyFieldsNames));
}
#endregion
Dictionary <string, object> whereColumns = keyFieldsNames.ToDictionary(k => k, v => (object)null);
foreach (object[] oldItem in oldDataList)
{
bool contains = false;
foreach (object[] newItem in newDataList)
{
bool allEqual = true;
for (int i = 0; i < fieldsNames.Length; ++i)
{
if (keyFieldsNames.Contains(fieldsNames[i]) && oldItem[i].ToString() != newItem[i].ToString())
{
allEqual = false;
break;
}
}
if (allEqual)
{
contains = true;
break;
}
}
if (!contains)
{
for (byte k = 0; k < keyFieldsNames.Length; ++k)
{
whereColumns[keyFieldsNames[k]] = oldItem[k];
}
_DB_Connection.Delete(table, whereColumns, transaction);
}
}
Dictionary <string, object> columnsValues = fieldsNames.ToDictionary(k => k, v => (object)null);
foreach (object[] newItem in newDataList)
{
for (byte i = 0; i < fieldsNames.Length; ++i)
{
columnsValues[fieldsNames[i]] = newItem[i];
}
_DB_Connection.InsertOnDuplicateUpdate(table, columnsValues, transaction);
}
}
/// <summary>
/// Funzione per registrare un nuovo utente.
/// </summary>
/// <param name="nome">Nome utente</param>
/// <param name="password">Password per l'utente (cleartext)</param>
/// <param name="path_monitorato">Path Locale dell'utente che verra monitorato</param>
public static User RegistraUtente(string nome,string password)
{
if (!NomeUtenteValido(nome))
{
throw new DatabaseException("Nome utente non va bene.", DatabaseErrorCode.FormatError);
}
if (!PasswordValida(password))
{
throw new DatabaseException("Password non va bene.", DatabaseErrorCode.FormatError);
}
password = TrasformaPassword(password);
//provo a mettere il nuovo utente: se ricevo un'eccezione particolare il nome utente e duplicato
DB_Table db = new DB_Table();
string[][] parameters = new string[2][];
parameters[0] = new string[2] { "@nome", nome };
parameters[1] = new string[2] { "@password", password };
try{
db.ExecuteQuery(sql_insert_user, parameters);
}
catch (DatabaseException e) when (e.ErrorCode == DatabaseErrorCode.Constraint)
{
throw new DatabaseException("L'utente che si cerca di inserire esiste già.", DatabaseErrorCode.UserGiaEsistente);
}
return new User(nome, password);
}
//Metodi Statici
/// <summary>
/// Distrugge gli snapshot di un file.
/// </summary>
/// <param name="nome_utente"></param>
/// <param name="id_file"></param>
public static void RimuoviSnapshotsDiFile(string nome_utente, int id_file)
{
DB_Table db = new DB_Table();
Log l = Log.getLog();
string sql = "SELECT nome_locale_s FROM snapshots WHERE id_file = @id_file;";
string[][] parameters = new string[1][];
string local_file = "";
string local_path = Properties.ApplicationSettings.Default.base_path + Path.DirectorySeparatorChar + "users_files"+Path.DirectorySeparatorChar+nome_utente;
parameters[0] = new string[2]{ "@id_file", id_file.ToString()};
db.ExecuteQuery(sql, parameters);
foreach (int i in db.GetResults())
{
local_file = (string)db.ResultGetValue("nome_locale_s");
try
{
File.Delete(local_path + Path.DirectorySeparatorChar + local_file);
}
catch(Exception e)
{
l.log("Errore nell'eliminare i file da disco. " + e.Message);
throw;
}
}
}
private void UpdateData(DB_Table table, List <object[]> oldList, List <object[]> newList, bool autoGeneratedKey, int keysCount, string[] fieldNames)
{
using (MySql.Data.MySqlClient.MySqlTransaction transaction = _DB_Connection.BeginTransaction())
{
foreach (var oldItem in oldList)
{
if (newList.Count == 0)
{
Dictionary <string, object> keyAndValues = new Dictionary <string, object>();
for (int i = 0; i < keysCount; i++)
{
keyAndValues.Add(fieldNames[i], oldItem[i]);
}
_DB_Connection.Delete(table, keyAndValues, transaction);
}
else
{
bool keysMatch = true;
bool valuesMatch = true;
int index = -1;
do
{
index++;
object[] newItem = newList[index];
for (int i = 0; i < fieldNames.Length; i++)
{
if (i < keysCount)
{
if ((keysMatch) && (newItem[i].ToString() != oldItem[i].ToString()))
{
keysMatch = false;
}
}
else
if ((valuesMatch) && (newItem[i].ToString() != oldItem[i].ToString()))
{
valuesMatch = false;
}
}
}while ((index < newList.Count - 1) && (!((keysMatch) && (index == keysCount - 1))));
if (keysMatch && valuesMatch)
{
newList.RemoveAt(index);
}
else if (keysMatch && !valuesMatch)
{
Dictionary <string, object> columnsAndValues = new Dictionary <string, object>();
for (int i = keysCount; i < fieldNames.Length; i++)
{
columnsAndValues.Add(fieldNames[i], newList[index][i]);
}
Dictionary <string, object> keyAndValues = new Dictionary <string, object>();
for (int i = 0; i < keysCount; i++)
{
keyAndValues.Add(fieldNames[i], newList[index][i]);
}
_DB_Connection.Update(table, columnsAndValues, keyAndValues, transaction);
newList.RemoveAt(index);
}
else
{
Dictionary <string, object> keyAndValues = new Dictionary <string, object>();
for (int i = 0; i < keysCount; i++)
{
keyAndValues.Add(fieldNames[i], oldItem[i]);
}
_DB_Connection.Delete(table, keyAndValues, transaction);
}
}
}
if (newList.Count != 0)
{
foreach (var newItem in newList)
{
Dictionary <string, object> columnsAndValues = new Dictionary <string, object>();
if (autoGeneratedKey)
{
for (int i = keysCount; i < fieldNames.Length; i++)
{
columnsAndValues.Add(fieldNames[i], newItem[i]);
}
}
else
{
for (int i = 0; i < keysCount; i++)
{
columnsAndValues.Add(fieldNames[i], newItem[i]);
}
for (int i = keysCount; i < fieldNames.Length; i++)
{
columnsAndValues.Add(fieldNames[i], newItem[i]);
}
}
_DB_Connection.Insert(table, columnsAndValues, transaction);
}
}
transaction.Commit();
}
}
public FileUtente nuovoFile(string nome_file, string path_relativo,DateTime t_creazione= new DateTime())
{
//Se non c'è spazio, cerco un capro espiatorio da buttare per far posto a quello nuovo,
//Altrimenti lancio un'eccezione
string[][] parameters = new string[1][];
if (this.__list_ids_files.Count >= this.__max_file)
{
int id_da_sacrificare=-1;
parameters[0] = new string[2] { "@nome_utente", __nome_utente};
this.ExecuteQuery(Properties.SQLquery.sqlCercaFileDaDistruggere, parameters);
foreach(int i in this.GetResults())
{
id_da_sacrificare = Int32.Parse(this.ResultGetValue("id").ToString());
break;
}
if (id_da_sacrificare >= 0)
{
for(int i = 0; i < this.Length; i++)
{
if(this[i].Id == id_da_sacrificare)
{
this[i].Distruggi();
}
}
}
else
{
this.l.log("Non c'è più posto per l'utente " + __nome_utente, Level.INFO);
throw new DatabaseException("Non è più possibile inserire nuovi file. Limite superato.", DatabaseErrorCode.LimiteFileSuperato);
}
}
if(t_creazione == DateTime.MinValue)
{
t_creazione = DateTime.Now;
}
parameters = new string[4][];
parameters[0] = new string[2] { "@t_creazione", t_creazione.ToString("u") };
parameters[1] = new string[2] { "@path_relativo_c", path_relativo };
parameters[2] = new string[2] { "@nome_file_c", nome_file };
parameters[3] = new string[2] { "@nome_utente", this.__nome_utente };
DB_Table db = new DB_Table();
Log l = Log.getLog();
db.ExecuteQuery(Properties.SQLquery.sqlNuovoFile, parameters);
long id = db.getLastInsertedId();
FileUtente file = new FileUtente(this.__nome_utente, (int)id);
this.__list_ids_files.Add(file.Id);
this.__file_list[this.__list_ids_files.Count - 1] = file;
return file;
}
public List <object[]> Select(DB_Table table, string[] fields, List <System.Tuple <string, Relation, object> > whereExpressions)
{
#region Contracts
if (fields == null)
{
throw new System.ArgumentNullException(nameof(fields));
}
if (whereExpressions == null)
{
throw new System.ArgumentNullException(nameof(whereExpressions));
}
if (fields.Length == 0)
{
throw new System.ArgumentException("Массив с именами столбцов должен содержать хотя бы один элемент.", nameof(fields));
}
if (whereExpressions.Count == 0)
{
throw new System.ArgumentException("Список с параметрами фильтрации должен содержать хотя бы один элемент.", nameof(whereExpressions));
}
#endregion
MySqlCommand cmd = new MySqlCommand("", _Connection);
string whereClause = "";
byte count = 1;
foreach (var expr in whereExpressions)
{
whereClause += expr.Item1;
if (expr.Item2 == Relation.NOT_EQUAL && expr.Item3 == null)
{
whereClause += " IS NOT NULL AND ";
}
else
{
switch (expr.Item2)
{
case Relation.EQUAL:
whereClause += " <=> ";
break;
case Relation.NOT_EQUAL:
whereClause += " != ";
break;
case Relation.LESS:
whereClause += " < ";
break;
case Relation.GREATER:
whereClause += " > ";
break;
case Relation.LESS_EQUAL:
whereClause += " <= ";
break;
case Relation.GREATER_EQUAL:
whereClause += " >= ";
break;
default:
throw new System.Exception("Reached unreachable.");
}
string paramName = "@p" + count;
whereClause += paramName + " AND ";
cmd.Parameters.AddWithValue(paramName, expr.Item3);
count++;
}
}
whereClause = whereClause.Remove(whereClause.Length - 5);
cmd.CommandText = "SELECT " + string.Join(", ", fields) + " FROM " + GetTableName(table) + " WHERE " + whereClause + ";";
return(ExecuteSelect(cmd));
}
private static string GetTableName(DB_Table table) => System.Enum.GetName(typeof(DB_Table), table).ToLower();
