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

            using (var cmd = SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
                             .WithSql(SQLiteStatement.Create("PRAGMA table_info('{0}')", table)))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var def = new TableSchema.ColumnDefinition
                        {
                            Name       = reader["name"].ToString(),
                            Type       = reader["type"].ToString(),
                            IsNullable = Convert.ToBoolean(reader["notnull"])
                        };
                        columns.Add(def);
                    }
                }
            }

            return(new TableSchema
            {
                Columns = columns.AsReadOnly()
            });
        }
Esempio n. 2
0
        public override void Initialise()
        {
            var dbFile = ExtractFilename(SmartConnectionString.For(myConfig.ConnectionString));

            try
            {
                if (!File.Exists(dbFile))
                {
                    Logger.Debug("\tCreating Wolfpack SQLite datafile at {0}...", dbFile);
                    SQLiteConnection.CreateFile(dbFile);
                }

                var schema = GetSchema("AgentData");

                if (schema.Columns.Count == 0)
                {
                    Logger.Debug("\tCreating AgentData table...");
                    using (var cmd = SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
                                     .WithSql(SQLiteStatement.Create("CREATE TABLE AgentData (")
                                              .Append("[TypeId] UNIQUEIDENTIFIER NOT NULL,")
                                              .Append("[EventType] TEXT NOT NULL,")
                                              .Append("[SiteId] TEXT NOT NULL,")
                                              .Append("[AgentId] TEXT NOT NULL,")
                                              .Append("[CheckId] TEXT NULL,")
                                              .Append("[Result] BOOL NULL,")
                                              .Append("[GeneratedOnUtc] DATETIME NOT NULL,")
                                              .Append("[ReceivedOnUtc] DATETIME NOT NULL,")
                                              .Append("[Data] TEXT NOT NULL,")
                                              .Append("[Tags] TEXT NULL,")
                                              .Append("[Version] UNIQUEIDENTIFIER NOT NULL,")
                                              .Append("[ResultCount] REAL NULL,")
                                              .Append("[MinuteBucket] INTEGER NULL,")
                                              .Append("[HourBucket] INTEGER NULL,")
                                              .Append("[DayBucket] INTEGER NULL)")))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }

                AddColumnIfMissing("MinuteBucket", "INTEGER", true);
                AddColumnIfMissing("HourBucket", "INTEGER", true);
                AddColumnIfMissing("DayBucket", "INTEGER", true);
                // Geo - point
                AddColumnIfMissing("Latitude", "TEXT", true);
                AddColumnIfMissing("Longitude", "TEXT", true);

                Logger.Debug("\tSuccess, AgentData table established");
            }
            catch (Exception)
            {
                Logger.Debug("\tError during SQLite datafile/database creation...");
                throw;
            }
        }
Esempio n. 3
0
        protected virtual bool AddColumnIfMissing(string column, string datatype, bool nullable)
        {
            if (GetSchema("AgentData").HasColumn(column))
            {
                return(false);
            }

            using (var cmd = SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
                             .WithSql(SQLiteStatement.Create("ALTER TABLE AgentData ADD {0} {1} {2} NULL",
                                                             column,
                                                             datatype,
                                                             nullable ? string.Empty : "NOT")))
            {
                cmd.ExecuteNonQuery();
                Logger.Debug("\tAdded column {0} [{1}]", column, datatype);
            }
            return(true);
        }