1 files changed, 174 insertions, 58 deletions

diff --git a/database/sqlite/sqlite_db_migration.c b/database/sqlite/sqlite_db_migration.c
index 548b7de07..a011d0fef 100644
--- a/database/sqlite/sqlite_db_migration.c
+++ b/database/sqlite/sqlite_db_migration.c
@@ -11,7 +11,40 @@ static int return_int_cb(void *data, int argc, char **argv, char **column)
     return 0;
 }
 
-int table_exists_in_database(const char *table)
+static int get_auto_vaccum(sqlite3 *database)
+{
+    char *err_msg = NULL;
+    char sql[128];
+
+    int exists = 0;
+
+    snprintf(sql, 127, "PRAGMA auto_vacuum");
+
+    int rc = sqlite3_exec_monitored(database, sql, return_int_cb, (void *) &exists, &err_msg);
+    if (rc != SQLITE_OK) {
+        netdata_log_info("Error checking database auto vacuum setting; %s", err_msg);
+        sqlite3_free(err_msg);
+    }
+
+    return exists;
+}
+
+int db_table_count(sqlite3 *database)
+{
+    char *err_msg = NULL;
+    char sql[128];
+
+    int count = 0;
+    snprintf(sql, 127, "select count(1) from sqlite_schema where type = 'table'");
+    int rc = sqlite3_exec_monitored(database, sql, return_int_cb, (void *) &count, &err_msg);
+    if (rc != SQLITE_OK) {
+        netdata_log_info("Error checking database table count; %s", err_msg);
+        sqlite3_free(err_msg);
+    }
+    return count;
+}
+
+int table_exists_in_database(sqlite3 *database, const char *table)
 {
     char *err_msg = NULL;
     char sql[128];
@@ -20,7 +53,7 @@ int table_exists_in_database(const char *table)
 
     snprintf(sql, 127, "select 1 from sqlite_schema where type = 'table' and name = '%s';", table);
 
-    int rc = sqlite3_exec_monitored(db_meta, sql, return_int_cb, (void *) &exists, &err_msg);
+    int rc = sqlite3_exec_monitored(database, sql, return_int_cb, (void *) &exists, &err_msg);
     if (rc != SQLITE_OK) {
         netdata_log_info("Error checking table existence; %s", err_msg);
         sqlite3_free(err_msg);
@@ -29,7 +62,7 @@ int table_exists_in_database(const char *table)
     return exists;
 }
 
-static int column_exists_in_table(const char *table, const char *column)
+static int column_exists_in_table(sqlite3 *database, const char *table, const char *column)
 {
     char *err_msg = NULL;
     char sql[128];
@@ -38,7 +71,7 @@ static int column_exists_in_table(const char *table, const char *column)
 
     snprintf(sql, 127, "SELECT 1 FROM pragma_table_info('%s') where name = '%s';", table, column);
 
-    int rc = sqlite3_exec_monitored(db_meta, sql, return_int_cb, (void *) &exists, &err_msg);
+    int rc = sqlite3_exec_monitored(database, sql, return_int_cb, (void *) &exists, &err_msg);
     if (rc != SQLITE_OK) {
         netdata_log_info("Error checking column existence; %s", err_msg);
         sqlite3_free(err_msg);
@@ -47,6 +80,17 @@ static int column_exists_in_table(const char *table, const char *column)
     return exists;
 }
 
+static int get_database_user_version(sqlite3 *database)
+{
+    int user_version = 0;
+
+    int rc = sqlite3_exec_monitored(database, "PRAGMA user_version", return_int_cb, (void *)&user_version, NULL);
+    if (rc != SQLITE_OK)
+        netdata_log_error("Failed to get user version for database");
+
+    return user_version;
+}
+
 const char *database_migrate_v1_v2[] = {
     "ALTER TABLE host ADD hops INTEGER NOT NULL DEFAULT 0;",
     NULL
@@ -88,31 +132,43 @@ const char *database_migrate_v10_v11[] = {
     NULL
 };
 
-static int do_migration_v1_v2(sqlite3 *database, const char *name)
-{
-    UNUSED(name);
-    netdata_log_info("Running \"%s\" database migration", name);
+const char *database_migrate_v11_v12[] = {
+    "ALTER TABLE health_log_detail ADD summary TEXT;",
+    "ALTER TABLE alert_hash ADD summary TEXT;",
+    NULL
+};
 
-    if (table_exists_in_database("host") && !column_exists_in_table("host", "hops"))
-        return init_database_batch(database, DB_CHECK_NONE, 0, &database_migrate_v1_v2[0]);
+const char *database_migrate_v12_v13_detail[] = {
+    "ALTER TABLE health_log_detail ADD summary TEXT;",
+    NULL
+};
+
+const char *database_migrate_v12_v13_hash[] = {
+    "ALTER TABLE alert_hash ADD summary TEXT;",
+    NULL
+};
+
+const char *database_migrate_v13_v14[] = {
+    "ALTER TABLE host ADD last_connected INT NOT NULL DEFAULT 0;",
+    NULL
+};
+
+static int do_migration_v1_v2(sqlite3 *database)
+{
+    if (table_exists_in_database(database, "host") && !column_exists_in_table(database, "host", "hops"))
+        return init_database_batch(database, &database_migrate_v1_v2[0]);
     return 0;
 }
 
-static int do_migration_v2_v3(sqlite3 *database, const char *name)
+static int do_migration_v2_v3(sqlite3 *database)
 {
-    UNUSED(name);
-    netdata_log_info("Running \"%s\" database migration", name);
-
-    if (table_exists_in_database("host") && !column_exists_in_table("host", "memory_mode"))
-        return init_database_batch(database, DB_CHECK_NONE, 0, &database_migrate_v2_v3[0]);
+    if (table_exists_in_database(database, "host") && !column_exists_in_table(database, "host", "memory_mode"))
+        return init_database_batch(database, &database_migrate_v2_v3[0]);
     return 0;
 }
 
-static int do_migration_v3_v4(sqlite3 *database, const char *name)
+static int do_migration_v3_v4(sqlite3 *database)
 {
-    UNUSED(name);
-    netdata_log_info("Running database migration %s", name);
-
     char sql[256];
 
     int rc;
@@ -126,7 +182,7 @@ static int do_migration_v3_v4(sqlite3 *database, const char *name)
 
     while (sqlite3_step_monitored(res) == SQLITE_ROW) {
          char *table = strdupz((char *) sqlite3_column_text(res, 0));
-         if (!column_exists_in_table(table, "chart_context")) {
+         if (!column_exists_in_table(database, table, "chart_context")) {
              snprintfz(sql, 255, "ALTER TABLE %s ADD chart_context text", table);
              sqlite3_exec_monitored(database, sql, 0, 0, NULL);
          }
@@ -140,27 +196,18 @@ static int do_migration_v3_v4(sqlite3 *database, const char *name)
     return 0;
 }
 
-static int do_migration_v4_v5(sqlite3 *database, const char *name)
+static int do_migration_v4_v5(sqlite3 *database)
 {
-    UNUSED(name);
-    netdata_log_info("Running \"%s\" database migration", name);
-
-    return init_database_batch(database, DB_CHECK_NONE, 0, &database_migrate_v4_v5[0]);
+    return init_database_batch(database, &database_migrate_v4_v5[0]);
 }
 
-static int do_migration_v5_v6(sqlite3 *database, const char *name)
+static int do_migration_v5_v6(sqlite3 *database)
 {
-    UNUSED(name);
-    netdata_log_info("Running \"%s\" database migration", name);
-
-    return init_database_batch(database, DB_CHECK_NONE, 0, &database_migrate_v5_v6[0]);
+    return init_database_batch(database, &database_migrate_v5_v6[0]);
 }
 
-static int do_migration_v6_v7(sqlite3 *database, const char *name)
+static int do_migration_v6_v7(sqlite3 *database)
 {
-    UNUSED(name);
-    netdata_log_info("Running \"%s\" database migration", name);
-
     char sql[256];
 
     int rc;
@@ -174,7 +221,7 @@ static int do_migration_v6_v7(sqlite3 *database, const char *name)
 
     while (sqlite3_step_monitored(res) == SQLITE_ROW) {
          char *table = strdupz((char *) sqlite3_column_text(res, 0));
-         if (!column_exists_in_table(table, "filtered_alert_unique_id")) {
+         if (!column_exists_in_table(database, table, "filtered_alert_unique_id")) {
              snprintfz(sql, 255, "ALTER TABLE %s ADD filtered_alert_unique_id", table);
              sqlite3_exec_monitored(database, sql, 0, 0, NULL);
              snprintfz(sql, 255, "UPDATE %s SET filtered_alert_unique_id = alert_unique_id", table);
@@ -190,11 +237,8 @@ static int do_migration_v6_v7(sqlite3 *database, const char *name)
     return 0;
 }
 
-static int do_migration_v7_v8(sqlite3 *database, const char *name)
+static int do_migration_v7_v8(sqlite3 *database)
 {
-    UNUSED(name);
-    netdata_log_info("Running database migration %s", name);
-
     char sql[256];
 
     int rc;
@@ -208,7 +252,7 @@ static int do_migration_v7_v8(sqlite3 *database, const char *name)
 
     while (sqlite3_step_monitored(res) == SQLITE_ROW) {
          char *table = strdupz((char *) sqlite3_column_text(res, 0));
-         if (!column_exists_in_table(table, "transition_id")) {
+         if (!column_exists_in_table(database, table, "transition_id")) {
              snprintfz(sql, 255, "ALTER TABLE %s ADD transition_id blob", table);
              sqlite3_exec_monitored(database, sql, 0, 0, NULL);
          }
@@ -222,10 +266,8 @@ static int do_migration_v7_v8(sqlite3 *database, const char *name)
     return 0;
 }
 
-static int do_migration_v8_v9(sqlite3 *database, const char *name)
+static int do_migration_v8_v9(sqlite3 *database)
 {
-    netdata_log_info("Running database migration %s", name);
-
     char sql[2048];
     int rc;
     sqlite3_stmt *res = NULL;
@@ -244,7 +286,7 @@ static int do_migration_v8_v9(sqlite3 *database, const char *name)
               "updated_by_id int, updates_id int, when_key int, duration int, non_clear_duration int, " \
               "flags int, exec_run_timestamp int, delay_up_to_timestamp int, " \
               "info text, exec_code int, new_status real, old_status real, delay int, " \
-              "new_value double, old_value double, last_repeat int, transition_id blob, global_id int, host_id blob);");
+              "new_value double, old_value double, last_repeat int, transition_id blob, global_id int, summary text, host_id blob);");
     sqlite3_exec_monitored(database, sql, 0, 0, NULL);
 
     snprintfz(sql, 2047, "CREATE INDEX IF NOT EXISTS health_log_d_ind_1 ON health_log_detail (unique_id);");
@@ -296,36 +338,85 @@ static int do_migration_v8_v9(sqlite3 *database, const char *name)
     return 0;
 }
 
-static int do_migration_v9_v10(sqlite3 *database, const char *name)
+static int do_migration_v9_v10(sqlite3 *database)
 {
-    netdata_log_info("Running \"%s\" database migration", name);
+    if (table_exists_in_database(database, "alert_hash") && !column_exists_in_table(database, "alert_hash", "chart_labels"))
+        return init_database_batch(database, &database_migrate_v9_v10[0]);
+    return 0;
+}
+
+static int do_migration_v10_v11(sqlite3 *database)
+{
+    if (table_exists_in_database(database, "health_log") && !column_exists_in_table(database, "health_log", "chart_name"))
+        return init_database_batch(database, &database_migrate_v10_v11[0]);
 
-    if (table_exists_in_database("alert_hash") && !column_exists_in_table("alert_hash", "chart_labels"))
-        return init_database_batch(database, DB_CHECK_NONE, 0, &database_migrate_v9_v10[0]);
     return 0;
 }
 
-static int do_migration_v10_v11(sqlite3 *database, const char *name)
+#define MIGR_11_12_UPD_HEALTH_LOG_DETAIL "UPDATE health_log_detail SET summary = (select name from health_log where health_log_id = health_log_detail.health_log_id);"
+static int do_migration_v11_v12(sqlite3 *database)
+{
+    int rc = 0;
+
+    if (table_exists_in_database(database, "health_log_detail") && !column_exists_in_table(database, "health_log_detail", "summary") &&
+        table_exists_in_database(database, "alert_hash") && !column_exists_in_table(database, "alert_hash", "summary"))
+        rc = init_database_batch(database, &database_migrate_v11_v12[0]);
+
+    if (!rc)
+        sqlite3_exec_monitored(database, MIGR_11_12_UPD_HEALTH_LOG_DETAIL, 0, 0, NULL);
+
+    return rc;
+}
+
+static int do_migration_v12_v13(sqlite3 *database)
 {
-    netdata_log_info("Running \"%s\" database migration", name);
+    int rc = 0;
 
-    if (table_exists_in_database("health_log") && !column_exists_in_table("health_log", "chart_name"))
-        return init_database_batch(database, DB_CHECK_NONE, 0, &database_migrate_v10_v11[0]);
+    if (table_exists_in_database(database, "health_log_detail") && !column_exists_in_table(database, "health_log_detail", "summary")) {
+        rc = init_database_batch(database, &database_migrate_v12_v13_detail[0]);
+        sqlite3_exec_monitored(database, MIGR_11_12_UPD_HEALTH_LOG_DETAIL, 0, 0, NULL);
+    }
+
+    if (table_exists_in_database(database, "alert_hash") && !column_exists_in_table(database, "alert_hash", "summary"))
+        rc = init_database_batch(database, &database_migrate_v12_v13_hash[0]);
+
+    return rc;
+}
+
+static int do_migration_v13_v14(sqlite3 *database)
+{
+    if (table_exists_in_database(database, "host") && !column_exists_in_table(database, "host", "last_connected"))
+        return init_database_batch(database, &database_migrate_v13_v14[0]);
 
     return 0;
 }
 
-static int do_migration_noop(sqlite3 *database, const char *name)
+
+// Actions for ML migration
+const char *database_ml_migrate_v1_v2[] = {
+    "PRAGMA journal_mode=delete",
+    "PRAGMA journal_mode=WAL",
+    "PRAGMA auto_vacuum=2",
+    "VACUUM",
+    NULL
+};
+
+static int do_ml_migration_v1_v2(sqlite3 *database)
+{
+    if (get_auto_vaccum(database) != 2)
+        return init_database_batch(database, &database_ml_migrate_v1_v2[0]);
+    return 0;
+}
+
+static int do_migration_noop(sqlite3 *database)
 {
     UNUSED(database);
-    UNUSED(name);
-    netdata_log_info("Running database migration %s", name);
     return 0;
 }
 
 typedef struct database_func_migration_list {
     char *name;
-    int (*func)(sqlite3 *database, const char *name);
+    int (*func)(sqlite3 *database);
 } DATABASE_FUNC_MIGRATION_LIST;
 
 
@@ -347,7 +438,8 @@ static int migrate_database(sqlite3 *database, int target_version, char *db_name
 
     netdata_log_info("Database version is %d, current version is %d. Running migration for %s ...", user_version, target_version, db_name);
     for (int i = user_version; i < target_version && migration_list[i].func; i++) {
-        rc = (migration_list[i].func)(database, migration_list[i].name);
+        netdata_log_info("Running database \"%s\" migration %s", db_name, migration_list[i].name);
+        rc = (migration_list[i].func)(database);
         if (unlikely(rc)) {
             error_report("Database %s migration from version %d to version %d failed", db_name, i, i + 1);
             return i;
@@ -369,6 +461,9 @@ DATABASE_FUNC_MIGRATION_LIST migration_action[] = {
     {.name = "v8 to v9",  .func = do_migration_v8_v9},
     {.name = "v9 to v10",  .func = do_migration_v9_v10},
     {.name = "v10 to v11",  .func = do_migration_v10_v11},
+    {.name = "v11 to v12",  .func = do_migration_v11_v12},
+    {.name = "v12 to v13",  .func = do_migration_v12_v13},
+    {.name = "v13 to v14",  .func = do_migration_v13_v14},
     // the terminator of this array
     {.name = NULL, .func = NULL}
 };
@@ -379,13 +474,34 @@ DATABASE_FUNC_MIGRATION_LIST context_migration_action[] = {
     {.name = NULL, .func = NULL}
 };
 
+DATABASE_FUNC_MIGRATION_LIST ml_migration_action[] = {
+    {.name = "v0 to v1",  .func = do_migration_noop},
+    {.name = "v1 to v2",  .func = do_ml_migration_v1_v2},
+    // the terminator of this array
+    {.name = NULL, .func = NULL}
+};
 
 int perform_database_migration(sqlite3 *database, int target_version)
 {
+    int user_version = get_database_user_version(database);
+
+    if (!user_version && !db_table_count(database))
+        return target_version;
+
     return migrate_database(database, target_version, "metadata", migration_action);
 }
 
 int perform_context_database_migration(sqlite3 *database, int target_version)
 {
+    int user_version = get_database_user_version(database);
+
+    if (!user_version && !table_exists_in_database(database, "context"))
+        return target_version;
+
     return migrate_database(database, target_version, "context", context_migration_action);
 }
+
+int perform_ml_database_migration(sqlite3 *database, int target_version)
+{
+    return migrate_database(database, target_version, "ml", ml_migration_action);
+}