/// <summary>
/// Return a parameterised INSERT command
/// </summary>
/// <returns>DbCommand ready to populate parameters</returns>
/// <param name="table">Table definition</param>
/// <param name="template">INSERT template, defaults to a regular
/// INSERT, but could be substituted for an "INSERT OR REPLACE" or
/// UPSERT.</param>
public static DbCommand ToInsertCmd(this Config.Table table, DbConnection connection, string template = InsertTemplate)
{
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = Smart.Format(template, table.Name,
String.Join(',', table.Columns.Select(x => x.Name)),
String.Join(',', table.Columns.Select(x => $"@{x.Name}")));
cmd.Prepare();
return(cmd);
}
/// <summary>
/// Set the named parameters of a DbCommand with the values in a message
/// </summary>
/// <returns>The parameters.</returns>
/// <param name="cmd">The prepared DbCommand from something like ToInsertCmd()</param>
/// <param name="table">The table definition</param>
/// <param name="context">The message and its context wrapped in a SmartObjects</param>
public static DbCommand SetParameters(this DbCommand cmd, Config.Table table, MessageContext context)
{
cmd.Parameters.Clear();
foreach (var c in table.Columns)
{
DbParameter param = cmd.CreateParameter();
param.ParameterName = $"@{c.Name}";
param.Value = Smart.Format(c.From, context);
// Convert datetimes to ISO 8601
if (c.Type.ToLower().Equals("datetime"))
{
param.Value = DateTime.Parse((string)param.Value).ToString("yyyy-MM-dd HH:mm:ss");
}
cmd.Parameters.Add(param);
}
return(cmd);
}
/// <summary>
/// Return CREATE TABLE DDL from a table definition
/// </summary>
/// <returns>The CREATE TABLE statement.</returns>
/// <param name="table">Table definition.</param>
public static async Task <string> ToCreateTable(this Config.Table table, MessageContext context)
{
if (!(table.Create is null))
{
return(await table.Create.Content.SelectStringAsync(context));
}
List <string> definitions = new List <string>();
List <string> indexes = new List <string>();
foreach (var col in table.Columns)
{
definitions.Add(col.ToSQLColumnDefinition());
switch (col.Index)
{
case Config.IndexType.NotUnique:
indexes.Add(Smart.Format(CreateIndexTemplate, table.Name, col.Name));
break;
case Config.IndexType.PrimaryKey:
case Config.IndexType.Unique:
indexes.Add(Smart.Format(CreateUniqueTemplate, table.Name, col.Name));
break;
default:
break;
}
}
StringBuilder transaction = new StringBuilder();
transaction.AppendLine(String.Format(CreateTableTemplate, table.Name, String.Join(",\n", definitions)));
transaction.AppendLine(String.Join('\n', indexes));
return(transaction.ToString());
}
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);
}
}