private void Dispose(bool disposing)
{
AccessSemaphore?.Dispose();
InsertQuery?.Dispose();
UpdateQuery?.Dispose();
DeleteQuery?.Dispose();
SelectHashQuery?.Dispose();
}
public async Task WithAccess(Func <Task> taskFunc)
{
await AccessSemaphore.WaitAsync();
try
{
await taskFunc();
}
finally
{
AccessSemaphore.Release();
}
}
public async Task WithAccess(Func <MySqlConnection, Task> taskFunc)
{
await AccessSemaphore.WaitAsync();
try
{
using (var rentedConnection = await ConnectionPool.RentConnectionAsync())
await taskFunc(rentedConnection.Object);
}
finally
{
AccessSemaphore.Release();
}
}
public async override Task <Message> HandleMessage(Message msg)
{
MessageContext context = new MessageContext(msg, this);
string dbName = Database != null ? await Database.SelectStringAsync(context) : null;
string dsTable = Table != null ? await Table?.SelectStringAsync(context) : null;
string dsColumn = Column != null ? await Column?.SelectStringAsync(context) : null;
string dsFrom = From != null ? await From?.SelectStringAsync(context) : null;
string dsQueryName = Query != null ? await Query?.SelectStringAsync(context) : null;
string sfSql = null;
bool hasRows = false;
Semaphore accessSemaphore = null;
if (AccessSemaphore.ContainsKey(dbName))
{
accessSemaphore = AccessSemaphore[dbName];
}
try
{
string[] paramValues;
var namedQuery = dsQueryName != null?Queries?.Where(x => x.Name.Equals(dsQueryName)).FirstOrDefault() : null;
if (namedQuery != null)
{
string[] parameterised = namedQuery.Content.Value.ToSmartParameterised("@arg{0}");
sfSql = parameterised[0];
var selectors = (from p in parameterised.Skip(1)
select new DataSelector(p)).ToArray();
paramValues = new string[parameterised.Length - 1];
for (int i = 0; i < paramValues.Length; i++)
{
paramValues[i] = await selectors[i].SelectStringAsync(context);
}
}
else
{
sfSql = Smart.Format("SELECT 1 FROM {0} WHERE {1} = @arg1",
dsTable,
dsColumn);
paramValues = new string[1];
paramValues[0] = dsFrom;
}
accessSemaphore?.WaitOne();
using (DbConnection connection = await NewConnection(context))
{
connection.Open();
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = sfSql;
cmd.CommandType = CommandType.Text;
for (int i = 0; i < paramValues.Length; i++)
{
var param = cmd.CreateParameter();
param.ParameterName = String.Format("arg{0}", i + 1);
param.Value = paramValues[i];
cmd.Parameters.Add(param);
}
var result = await cmd.ExecuteReaderAsync();
hasRows = result.HasRows;
result.Close();
}
}
catch (Exception ex)
{
logger.Error(ex, $"{ex.GetType().Name} thrown for \"{this.Name}\": {ex.Message}");
return(null);
}
finally
{
accessSemaphore?.Release();
}
// Wait to get out of the try/using/finally block for lock sensitive databases like SQLite
if (hasRows)
{
return(ThisPassedTheTest(msg));
}
else
{
return(ThisFailedTheTest(msg));
}
}
public override async Task <Message> HandleMessage(Message msg)
{
// Handle a mix of message types by rebatching them into groups.
// We'll only let the database and table vary by the type, not
// individual messages.
IEnumerable <Message> messages = null;
if (Children)
{
messages = msg.AsEnumerable();
}
else
{
messages = new List <Message> {
msg
}
};
var byType = messages.GroupBy(x => x.GetType());
foreach (var group in byType)
{
var sample = group.First();
MessageContext context = new MessageContext(sample, this);
string dbName = await Database.SelectStringAsync(context);
string tableName = await Table.SelectStringAsync(context);
string[] parameterised = $"UPDATE {tableName} SET {Set} WHERE {Where}".ToSmartParameterised();
if (String.IsNullOrWhiteSpace(tableName))
{
tableName = sample.TableName();
}
Semaphore accessSemaphore = null;
if (AccessSemaphore.ContainsKey(dbName))
{
accessSemaphore = AccessSemaphore[dbName];
}
try
{
accessSemaphore?.WaitOne();
using (DbConnection connection = await NewConnection(context))
{
connection.Open();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = parameterised[0];
foreach (var m in group)
{
cmd.Parameters.Clear();
MessageContext mContext = new MessageContext(m, this);
for (int i = 1; i <= parameterised.Length - 1; i++)
{
var sqlParam = cmd.CreateParameter();
sqlParam.ParameterName = String.Format("arg{0}", i);
sqlParam.Value = Smart.Format(parameterised[i], mContext).ToBestType();
cmd.Parameters.Add(sqlParam);
}
int written = await cmd.ExecuteNonQueryAsync();
}
}
}
}
catch (Exception ex)
{
logger.Error(ex, "Error updating DB");
}
finally
{
accessSemaphore?.Release();
}
}
return(msg);
}
}
public override async Task <Message> HandleMessage(Message msg)
{
// Handle a mix of message types by rebatching them into groups.
// We'll only let the database and table vary by the type, not
// individual messages.
IEnumerable <Message> messages = null;
if (Children)
{
messages = msg.AsEnumerable();
}
else
{
messages = new List <Message> {
msg
}
};
var byType = messages.GroupBy(x => x.GetType());
foreach (var group in byType)
{
var sample = group.First();
MessageContext context = new MessageContext(sample, this);
string connectionString = Connection != null ? await Connection.SelectStringAsync(context) : null;
string dbName = Database != null ? await Database.SelectStringAsync(context) : null;
string tableName = Table != null ? await Table.SelectStringAsync(context) : null;
if (String.IsNullOrWhiteSpace(connectionString))
{
// Default to a SQLite database if no connection string given
string dbPath = Path.Combine(AHostProxy.Current.BaseDirectory, "Databases");
if (!Directory.Exists(dbPath))
{
Directory.CreateDirectory(dbPath);
}
string dbFile = Path.Combine(dbPath, dbName);
connectionString = String.Format(_defaultConnection, dbFile);
}
if (String.IsNullOrWhiteSpace(tableName))
{
tableName = sample.TableName();
}
Semaphore accessSemaphore = null;
if (AccessSemaphore.ContainsKey(dbName))
{
accessSemaphore = AccessSemaphore[dbName];
}
try
{
Config.Table table = ByName <Config.Table>(tableName);
if (table is null)
{
table = sample.ToTableDef(tableName);
}
accessSemaphore?.WaitOne();
using (DbConnection connection = connectionString.ToConnection())
{
connection.Open();
var tables = connection.GetSchema("Tables");
bool tableExists = false;
foreach (DataRow t in tables.Rows)
{
if (t.ItemArray[2].ToString() == tableName)
{
tableExists = true;
break;
}
}
if (!tableExists)
{
var ctcmd = connection.CreateCommand();
ctcmd.CommandText = String.Format("BEGIN;\n{0}\nCOMMIT;", await table.ToCreateTable(context));
ctcmd.ExecuteScalar();
}
using (DbCommand cmd = table.ToInsertCmd(connection, Extensions.InsertOrReplaceTemplate))
foreach (var m in group)
{
cmd.Parameters.Clear();
MessageContext mContext = new MessageContext(m, this);
cmd.SetParameters(table, mContext);
int written = await cmd.ExecuteNonQueryAsync();
if (written == 0)
{
logger.Warn("Failed to insert to {0}", dbName);
}
}
}
}
catch (Exception ex)
{
logger.Error(ex, "Error saving to DB");
}
finally
{
accessSemaphore?.Release();
}
}
return(msg);
}
}