/// <summary>
/// Builds a script that will insert data from the specified <see cref="LogTableRecord" />.
/// </summary>
/// <param name="table">The <see cref="LogTableDefinition" /> for the database table.</param>
/// <param name="record">The <see cref="LogTableRecord" /> containing the data to insert.</param>
/// <returns>A <see cref="SqlCommand" /> for an INSERT script that inserts the data from the specified <see cref="LogTableRecord" />.</returns>
/// <exception cref="ArgumentNullException">
/// <paramref name="table" /> is null.
/// <para>or</para>
/// <paramref name="record" /> is null.
/// </exception>
public static SqlCommand BuildInsert(LogTableDefinition table, LogTableRecord record)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
SqlParameterHelper helper = new SqlParameterHelper(table, record);
StringBuilder script = new StringBuilder();
script.Append($"INSERT INTO [dbo].[{table.Name}](");
script.Append(string.Join(", ", helper.GetColumnNames()));
script.Append(") VALUES (");
script.Append(string.Join(", ", helper.GetColumnParams()));
script.Append(")");
// Add parameters for all values
SqlCommand command = new SqlCommand(script.ToString());
command.Parameters.AddRange(helper.GetSqlParameters().ToArray());
return(command);
}
/// <summary>
/// Builds a script that will update data from the specified <see cref="LogTableRecord" />.
/// </summary>
/// <param name="table">The <see cref="LogTableDefinition" /> for the database table.</param>
/// <param name="record">The <see cref="LogTableRecord" /> containing the data to update.</param>
/// <returns>A <see cref="SqlCommand" /> for an UPDATE script that updates the data from the specified <see cref="LogTableRecord" />.</returns>
/// <exception cref="ArgumentNullException">
/// <paramref name="table" /> is null.
/// <para>or</para>
/// <paramref name="record" /> is null.
/// </exception>
public static SqlCommand BuildUpdate(LogTableDefinition table, LogTableRecord record)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
SqlParameterHelper helper = new SqlParameterHelper(table, record);
StringBuilder script = new StringBuilder();
script.Append($"UPDATE [dbo].[{table.Name}] SET ");
script.Append(string.Join(", ", helper.GetUpdateAssignments()));
script.Append(" WHERE ");
script.Append(helper.GetPrimaryKeyWhereClause());
// Add parameters for all values
SqlCommand command = new SqlCommand(script.ToString());
command.Parameters.AddRange(helper.GetSqlParameters().ToArray());
return(command);
}
//public static void UpdateGeneralLog(LogTableRecord record)
//{
// //TO DO: get connection string details from file
// string connString = "server=localhost; database=mysql; uid=root; password='';";
// using (MySqlConnection conn = new MySqlConnection(connString))
// {
// string query = "SET GLOBAL general_log = 'OFF'; RENAME TABLE general_log TO general_log_temp;";
// string time = record.EventTime.ToString("yyyy-MM-dd HH:mm:ss");
// query += "update mysql.general_log_temp set uploaded=true where event_time = '" + time + "' and thread_id = '" + record.ThreadID + "' and server_id = '" + record.ServerID + "';";
// query += "RENAME TABLE general_log_temp TO general_log; SET GLOBAL general_log = 'ON';";
// Console.WriteLine("**********************************");
// Console.WriteLine("running query to update log: " + query);
// Console.WriteLine("**********************************");
// MySqlCommand cmd = new MySqlCommand(query, conn);
// conn.Open();
// cmd.ExecuteNonQuery();
// conn.Close();
// }
//}
public static void UpdateGeneralLog(LogTableRecord record)
{
string connString = "server=" + Server + "; database= mysql; uid=" + Uid + "; password= "******";";
MySqlConnection conn = new MySqlConnection(connString);
string query = "SET GLOBAL general_log = 'OFF'; RENAME TABLE general_log TO general_log_temp;";
string time = record.EventTime.ToString("yyyy-MM-dd HH:mm:ss");
query += "update mysql.general_log_temp set copied=true where event_time = @time and thread_id = @threadID and server_id = @serverID and user_host=@user;";
query += "RENAME TABLE general_log_temp TO general_log; SET GLOBAL general_log = 'ON';";
//Console.WriteLine("**********************************");
//Console.WriteLine("running query to update log: " + query);
//Console.WriteLine("**********************************");
MySqlCommand cmd = new MySqlCommand(query, conn);
//cmd.Parameters.AddWithValue("@arg", record.Argument);
cmd.Parameters.AddWithValue("@threadID", record.ThreadID);
cmd.Parameters.AddWithValue("@serverID", record.ServerID);
cmd.Parameters.AddWithValue("@time", record.EventTime);
cmd.Parameters.AddWithValue("@user", record.UserHost);
conn.Open();
try
{
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
conn.Close();
throw ex;
}
}
public static void InsertQueryIntoClientLog(LogTableRecord record)
{
string connString = "server=" + Server + "; database= client_db; uid=" + Uid + "; password= "******";";
MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand cmd = new MySqlCommand("insert into client_db (event_time, user_host, thread_id, server_id, argument) values (@event_time, @user_host, @thread_id, @server_id, @argument);", conn);
cmd.Parameters.AddWithValue("@event_time", record.EventTime);
cmd.Parameters.AddWithValue("@user_host", record.UserHost);
cmd.Parameters.AddWithValue("@thread_id", record.ThreadID);
cmd.Parameters.AddWithValue("@server_id", record.ServerID);
cmd.Parameters.AddWithValue("@argument", record.Argument);
conn.Open();
try
{
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
conn.Close();
throw ex;
}
}
/// <summary>
/// Updates data from the specified <see cref="LogTableRecord" /> into the table designated by the <see cref="LogTableDefinition" />.
/// </summary>
/// <param name="table">The <see cref="LogTableDefinition" />.</param>
/// <param name="record">The <see cref="LogTableRecord" />.</param>
/// <returns><c>true</c> if submission was successful, <c>false</c> otherwise.</returns>
/// <exception cref="ArgumentNullException">
/// <paramref name="table" /> is null.
/// <para>or</para>
/// <paramref name="record" /> is null.
/// </exception>
public bool Update(LogTableDefinition table, LogTableRecord record)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
LogTrace($"UPDATE request received for {table.Name} (primary key = {record[table.PrimaryKey]})");
DataLogDatabaseResult result = _writer.Update(table, record);
bool updateSuccessful = result.Success;
// If the operation failed, try the alternate (if one exists)
if (!updateSuccessful && _alternateWriter != null)
{
LogTrace($"UPDATE failed. Attempting update to alternate database.");
DataLogDatabaseResult alternateResult = _alternateWriter.Update(table, record);
updateSuccessful = alternateResult.Success;
}
// If the operation hasn't succeeded, check to see if we should add this to the cache.
if (!updateSuccessful)
{
_cache?.Add(table, record, false);
}
return(updateSuccessful);
}
public DataLogCacheData(LogTableDefinition table, LogTableRecord record, bool isInsert)
{
Table = table;
Record = record;
IsInsert = isInsert;
Retries = 0;
}
public DataLogDatabaseResult Insert(LogTableDefinition table, LogTableRecord record)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
LogTrace($"INSERT {table.Name} (primary key = {record[table.PrimaryKey]})");
DataLogDatabaseResult result;
using (SqlCommand insert = DataLogSqlBuilder.BuildInsert(table, record))
{
result = new DataLogDatabaseResult(table.Name, insert);
try
{
using (SqlConnection connection = new SqlConnection(_connectionString.ToString()))
{
connection.Open();
insert.Connection = connection;
try
{
insert.ExecuteNonQuery();
}
catch (SqlException ex) when(ex.Message.Contains("Invalid object"))
{
// Table doesn't exist - create it and try again
LogDebug($"Generating new table {table.Name}");
if (CreateTable(connection, table))
{
LogDebug($"Table {table.Name} created.");
LogTrace($"Retrying INSERT {table.Name}");
insert.ExecuteNonQuery();
}
}
}
}
catch (Exception ex)
{
result.Error = ex.Message;
}
}
if (!result.Success)
{
LogWarn($"INSERT {table.Name} failed: {result.Error}");
LogTrace($"SQL command: " + result.Command);
LogTrace($"SQL parameters: {string.Join("; ", result.Parameters.Select(n => $"{n.Key}={n.Value}"))}");
}
return(result);
}
public SqlParameterHelper(LogTableDefinition table, LogTableRecord record)
{
_primaryKey = table.PrimaryKey;
_record = record;
// Ignore any columns that have no associated value in the LogTableRecord
foreach (LogTableColumn column in table.Columns)
{
if (_record.ContainsKey(column.Name))
{
_columns.Add(column);
}
}
}
/// <summary>
/// Updates data from the specified <see cref="LogTableRecord" /> into the table designated by the <see cref="LogTableDefinition" />.
/// </summary>
/// <param name="table">The <see cref="LogTableDefinition" />.</param>
/// <param name="record">The <see cref="LogTableRecord" />.</param>
/// <returns><c>true</c> if submission was successful, <c>false</c> otherwise.</returns>
/// <exception cref="ArgumentNullException">
/// <paramref name="table" /> is null.
/// <para>or</para>
/// <paramref name="record" /> is null.
/// </exception>
public bool Update(LogTableDefinition table, LogTableRecord record)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
return(Channel.Update(table, record));
}
public DataLogDatabaseResult Update(LogTableDefinition table, LogTableRecord record)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
LogTrace($"UPDATE {table.Name} (primary key = {record[table.PrimaryKey]})");
DataLogDatabaseResult result;
using (SqlCommand update = DataLogSqlBuilder.BuildUpdate(table, record))
{
result = new DataLogDatabaseResult(table.Name, update);
try
{
using (SqlConnection connection = new SqlConnection(_connectionString.ToString()))
{
connection.Open();
update.Connection = connection;
int affectedRows = update.ExecuteNonQuery();
if (affectedRows == 0)
{
result.Error = "No rows were affected.";
}
}
}
catch (Exception ex)
{
result.Error = ex.Message;
}
}
if (!result.Success)
{
LogWarn($"UPDATE {table.Name} failed: {result.Error}");
LogTrace($"SQL command: " + result.Command);
LogTrace($"SQL parameters: {string.Join("; ", result.Parameters.Select(n => $"{n.Key}={n.Value}"))}");
}
return(result);
}
/// <summary>
/// Inserts data from the specified <see cref="LogTableRecord" /> into the table designated by the <see cref="LogTableDefinition" />.
/// </summary>
/// <param name="table">The <see cref="LogTableDefinition" />.</param>
/// <param name="record">The <see cref="LogTableRecord" />.</param>
/// <returns><c>true</c> if submission was successful, <c>false</c> otherwise.</returns>
/// <exception cref="ArgumentNullException">
/// <paramref name="table" /> is null.
/// <para>or</para>
/// <paramref name="record" /> is null.
/// </exception>
public bool Insert(LogTableDefinition table, LogTableRecord record)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
LogTrace($"INSERT request received for {table.Name} (primary key = {record[table.PrimaryKey]})");
DataLogDatabaseResult result = _writer.Insert(table, record);
bool insertSuccessful = result.Success;
// If the operation failed, try the alternate (if one exists)
if (!insertSuccessful && _alternateWriter != null)
{
LogTrace($"INSERT failed. Attempting insert to alternate database.");
DataLogDatabaseResult alternateResult = _alternateWriter.Insert(table, record);
insertSuccessful = alternateResult.Success;
}
// If the operation hasn't succeeded, check to see if we should add this to the cache.
if (!insertSuccessful && _cache != null)
{
if (result.Error.Contains("Violation of PRIMARY KEY constraint", StringComparison.OrdinalIgnoreCase))
{
// Bypass the cache for primary key violations
result.Retries = -1;
CacheOperationsRetried?.Invoke(this, new DataLogCacheEventArgs(new[] { result }));
}
else
{
_cache.Add(table, record, true);
}
}
return(insertSuccessful);
}
/// <summary>
/// Adds the specified log table data to the cache.
/// </summary>
/// <param name="table">The <see cref="LogTableDefinition" />.</param>
/// <param name="record">The <see cref="LogTableRecord" />.</param>
/// <param name="isInsert">if set to <c>true</c> this data should be processed as an insert.</param>
/// <exception cref="ArgumentNullException">
/// <paramref name="table" /> is null.
/// <para>or</para>
/// <paramref name="record" /> is null.
/// </exception>
public void Add(LogTableDefinition table, LogTableRecord record, bool isInsert)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
string operation = isInsert ? "INSERT" : "UPDATE";
string fileName = $"{table.Name} {operation} {record[table.PrimaryKey]}.xml";
FileInfo file = new FileInfo(Path.Combine(_cacheLocation.FullName, fileName));
LogTrace($"Adding cache file {file.Name}");
DataLogCacheData cacheData = new DataLogCacheData(table, record, isInsert);
WriteCacheData(file, cacheData);
}