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