예제 #1
0
        /// <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);
        }
예제 #2
0
        /// <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);
        }
예제 #3
0
        /// <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());
        }
예제 #4
0
        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);
        }
    }