Esempio n. 1
0
        protected virtual TableSchema GetSchema(string table)
        {
            var columns = new List <TableSchema.ColumnDefinition>();

            using (var cmd = SqlServerAdhocCommand.UsingSmartConnection(_config.ConnectionString)
                             .WithSql(SqlServerStatement.Create("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", table)))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var def = new TableSchema.ColumnDefinition
                        {
                            Name       = reader["COLUMN_NAME"].ToString(),
                            Type       = reader["DATA_TYPE"].ToString(),
                            IsNullable =
                                (string.Compare(reader["IS_NULLABLE"].ToString(), "yes", true) == 0)
                        };
                        columns.Add(def);
                    }
                }
            }

            return(new TableSchema
            {
                Columns = columns.AsReadOnly()
            });
        }
Esempio n. 2
0
        protected virtual bool AddColumnIfMissing(string column, string datatype, bool nullable)
        {
            if (GetSchema("AgentData").HasColumn(column))
            {
                return(false);
            }

            using (var cmd = SqlServerAdhocCommand.UsingSmartConnection(_config.ConnectionString)
                             .WithSql(SqlServerStatement.Create("ALTER TABLE [{3}].[AgentData] ADD {0} {1} {2} NULL",
                                                                column,
                                                                datatype,
                                                                nullable ? string.Empty : "NOT",
                                                                _config.SchemaName)))
            {
                cmd.ExecuteNonQuery();
                Logger.Debug("\tAdded column {0} [{1}]", column, datatype);
            }
            return(true);
        }
Esempio n. 3
0
        public override void Initialise()
        {
            try
            {
                Logger.Debug("\tCreating {0} schema...", _config.SchemaName);
                using (var cmd = SqlServerAdhocCommand.UsingSmartConnection(_config.ConnectionString)
                                 .WithSql(SqlServerStatement.Create("IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'{0}') SELECT CAST(1 as bit) ELSE Select CAST(0 as bit)", _config.SchemaName)))
                {
                    bool exists = (bool)cmd.ExecuteScalar();
                    if (exists)
                    {
                        using (var createCmd = SqlServerAdhocCommand.UsingSmartConnection(_config.ConnectionString)
                                               .WithSql(SqlServerStatement.Create("CREATE SCHEMA {0}", _config.SchemaName)))
                        {
                            createCmd.ExecuteNonQuery();
                            Logger.Debug("\tDone");
                        }
                    }
                }

                Logger.Debug("\tCreating AgentData table...");
                using (var cmd = SqlServerAdhocCommand.UsingSmartConnection(_config.ConnectionString)
                                 .WithSql(SqlServerStatement.Create("IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}].[AgentData]') AND type in (N'U')) BEGIN", _config.SchemaName)
                                          .Append("CREATE TABLE [{0}].[AgentData](", _config.SchemaName)
                                          .Append("[TypeId] [uniqueidentifier] NOT NULL,")
                                          .Append("[EventType] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,")
                                          .Append("[SiteId] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,")
                                          .Append("[AgentId] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,")
                                          .Append("[CheckId] [varchar](50) COLLATE Latin1_General_CI_AS NULL,")
                                          .Append("[Result] [bit] NULL,")
                                          .Append("[GeneratedOnUtc] [datetime] NOT NULL,")
                                          .Append("[ReceivedOnUtc] [datetime] NOT NULL,")
                                          .Append("[Data] [xml] NOT NULL,")
                                          .Append("[Version] [uniqueidentifier] NOT NULL) END")))
                {
                    cmd.ExecuteNonQuery();
                    Logger.Debug("\tDone");
                }

                Logger.Debug("\tApplying schema updates (ResultCount) to AgentData table...");
                using (var cmd = SqlServerAdhocCommand.UsingSmartConnection(_config.ConnectionString)
                                 .WithSql(SqlServerStatement.Create("IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AgentData' AND COLUMN_NAME = 'ResultCount') BEGIN")
                                          .Append("ALTER TABLE {0}.AgentData ADD ResultCount DECIMAL(20,4) NULL", _config.SchemaName)
                                          .Append("END ELSE BEGIN")
                                          .Append("IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AgentData' AND COLUMN_NAME = 'ResultCount' AND DATA_TYPE = 'bigint') BEGIN")
                                          .Append("ALTER TABLE {0}.AgentData ALTER COLUMN ResultCount DECIMAL(20,4)", _config.SchemaName)
                                          .Append("END")
                                          .Append("END")))
                {
                    cmd.ExecuteNonQuery();
                    Logger.Debug("\tDone");
                }

                Logger.Debug("\tApplying schema updates to AgentData table...");
                AddColumnIfMissing("Tags", "VARCHAR(200)", true);
                AddColumnIfMissing("MinuteBucket", "INT", true);
                AddColumnIfMissing("HourBucket", "INT", true);
                AddColumnIfMissing("DayBucket", "INT", true);
                // Geo - point
                AddColumnIfMissing("Latitude", "VARCHAR(12)", true);
                AddColumnIfMissing("Longitude", "VARCHAR(12)", true);

                Logger.Debug("\tSuccess, AgentData table established");
            }
            catch (Exception)
            {
                Logger.Debug("\tFailed to create AgentData table");
                throw;
            }
        }