summaryrefslogtreecommitdiffstats
path: root/database/sqlite/sqlite_db_migration.c
blob: 9c7235fdb63424c71e6d7c81e03e1dbfbc4f819b (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
// SPDX-License-Identifier: GPL-3.0-or-later

#include "sqlite_db_migration.h"

static int return_int_cb(void *data, int argc, char **argv, char **column)
{
    int *status = data;
    UNUSED(argc);
    UNUSED(column);
    *status = str2uint32_t(argv[0], NULL);
    return 0;
}


int table_exists_in_database(const char *table)
{
    char *err_msg = NULL;
    char sql[128];

    int exists = 0;

    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);
    if (rc != SQLITE_OK) {
        info("Error checking table existence; %s", err_msg);
        sqlite3_free(err_msg);
    }

    return exists;
}

static int column_exists_in_table(const char *table, const char *column)
{
    char *err_msg = NULL;
    char sql[128];

    int exists = 0;

    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);
    if (rc != SQLITE_OK) {
        info("Error checking column existence; %s", err_msg);
        sqlite3_free(err_msg);
    }

    return exists;
}

const char *database_migrate_v1_v2[] = {
    "ALTER TABLE host ADD hops INTEGER NOT NULL DEFAULT 0;",
    NULL
};

const char *database_migrate_v2_v3[] = {
    "ALTER TABLE host ADD memory_mode INT NOT NULL DEFAULT 0;",
    "ALTER TABLE host ADD abbrev_timezone TEXT NOT NULL DEFAULT '';",
    "ALTER TABLE host ADD utc_offset INT NOT NULL DEFAULT 0;",
    "ALTER TABLE host ADD program_name TEXT NOT NULL DEFAULT 'unknown';",
    "ALTER TABLE host ADD program_version TEXT NOT NULL DEFAULT 'unknown';",
    "ALTER TABLE host ADD entries INT NOT NULL DEFAULT 0;",
    "ALTER TABLE host ADD health_enabled INT NOT NULL DEFAULT 0;",
    NULL
};

const char *database_migrate_v4_v5[] = {
    "DROP TABLE IF EXISTS chart_active;",
    "DROP TABLE IF EXISTS dimension_active;",
    "DROP TABLE IF EXISTS chart_hash;",
    "DROP TABLE IF EXISTS chart_hash_map;",
    "DROP VIEW IF EXISTS v_chart_hash;",
    NULL
};

const char *database_migrate_v5_v6[] = {
    "DROP TRIGGER IF EXISTS tr_dim_del;",
    "DROP TABLE IF EXISTS dimension_delete;",
    NULL
};


static int do_migration_v1_v2(sqlite3 *database, const char *name)
{
    UNUSED(name);
    info("Running \"%s\" database migration", name);

    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]);
    return 0;
}

static int do_migration_v2_v3(sqlite3 *database, const char *name)
{
    UNUSED(name);
    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]);
    return 0;
}

static int do_migration_v3_v4(sqlite3 *database, const char *name)
{
    UNUSED(name);
    info("Running database migration %s", name);

    char sql[256];

    int rc;
    sqlite3_stmt *res = NULL;
    snprintfz(sql, 255, "SELECT name FROM sqlite_schema WHERE type ='table' AND name LIKE 'health_log_%%';");
    rc = sqlite3_prepare_v2(database, sql, -1, &res, 0);
    if (rc != SQLITE_OK) {
        error_report("Failed to prepare statement to alter health_log tables");
        return 1;
    }

    while (sqlite3_step_monitored(res) == SQLITE_ROW) {
         char *table = strdupz((char *) sqlite3_column_text(res, 0));
         if (!column_exists_in_table(table, "chart_context")) {
             snprintfz(sql, 255, "ALTER TABLE %s ADD chart_context text", table);
             sqlite3_exec_monitored(database, sql, 0, 0, NULL);
         }
         freez(table);
    }

    rc = sqlite3_finalize(res);
    if (unlikely(rc != SQLITE_OK))
        error_report("Failed to finalize statement when altering health_log tables, rc = %d", rc);

    return 0;
}

static int do_migration_v4_v5(sqlite3 *database, const char *name)
{
    UNUSED(name);
    info("Running \"%s\" database migration", name);

    return init_database_batch(database, DB_CHECK_NONE, 0, &database_migrate_v4_v5[0]);
}

static int do_migration_v5_v6(sqlite3 *database, const char *name)
{
    UNUSED(name);
    info("Running \"%s\" database migration", name);

    return init_database_batch(database, DB_CHECK_NONE, 0, &database_migrate_v5_v6[0]);
}

static int do_migration_v6_v7(sqlite3 *database, const char *name)
{
    UNUSED(name);
    info("Running \"%s\" database migration", name);

    char sql[256];

    int rc;
    sqlite3_stmt *res = NULL;
    snprintfz(sql, 255, "SELECT name FROM sqlite_schema WHERE type ='table' AND name LIKE 'aclk_alert_%%';");
    rc = sqlite3_prepare_v2(database, sql, -1, &res, 0);
    if (rc != SQLITE_OK) {
        error_report("Failed to prepare statement to alter aclk_alert tables");
        return 1;
    }

    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")) {
             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);
             sqlite3_exec_monitored(database, sql, 0, 0, NULL);
         }
         freez(table);
    }

    rc = sqlite3_finalize(res);
    if (unlikely(rc != SQLITE_OK))
        error_report("Failed to finalize statement when altering aclk_alert tables, rc = %d", rc);

    return 0;
}

static int do_migration_v7_v8(sqlite3 *database, const char *name)
{
    UNUSED(name);
    info("Running database migration %s", name);

    char sql[256];

    int rc;
    sqlite3_stmt *res = NULL;
    snprintfz(sql, 255, "SELECT name FROM sqlite_schema WHERE type ='table' AND name LIKE 'health_log_%%';");
    rc = sqlite3_prepare_v2(database, sql, -1, &res, 0);
    if (rc != SQLITE_OK) {
        error_report("Failed to prepare statement to alter health_log tables");
        return 1;
    }

    while (sqlite3_step_monitored(res) == SQLITE_ROW) {
         char *table = strdupz((char *) sqlite3_column_text(res, 0));
         if (!column_exists_in_table(table, "transition_id")) {
             snprintfz(sql, 255, "ALTER TABLE %s ADD transition_id blob", table);
             sqlite3_exec_monitored(database, sql, 0, 0, NULL);
         }
         freez(table);
    }

    rc = sqlite3_finalize(res);
    if (unlikely(rc != SQLITE_OK))
        error_report("Failed to finalize statement when altering health_log tables, rc = %d", rc);

    return 0;
}


static int do_migration_noop(sqlite3 *database, const char *name)
{
    UNUSED(database);
    UNUSED(name);
    info("Running database migration %s", name);
    return 0;
}

typedef struct database_func_migration_list {
    char *name;
    int (*func)(sqlite3 *database, const char *name);
} DATABASE_FUNC_MIGRATION_LIST;


static int migrate_database(sqlite3 *database, int target_version, char *db_name, DATABASE_FUNC_MIGRATION_LIST *migration_list)
{
    int user_version = 0;
    char *err_msg = NULL;

    int rc = sqlite3_exec_monitored(database, "PRAGMA user_version;", return_int_cb, (void *) &user_version, &err_msg);
    if (rc != SQLITE_OK) {
        info("Error checking the %s database version; %s", db_name, err_msg);
        sqlite3_free(err_msg);
    }

    if (likely(user_version == target_version)) {
        info("%s database version is %d (no migration needed)", db_name, target_version);
        return target_version;
    }

    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);
        if (unlikely(rc)) {
            error_report("Database %s migration from version %d to version %d failed", db_name, i, i + 1);
            return i;
        }
    }
    return target_version;

}

DATABASE_FUNC_MIGRATION_LIST migration_action[] = {
    {.name = "v0 to v1",  .func = do_migration_noop},
    {.name = "v1 to v2",  .func = do_migration_v1_v2},
    {.name = "v2 to v3",  .func = do_migration_v2_v3},
    {.name = "v3 to v4",  .func = do_migration_v3_v4},
    {.name = "v4 to v5",  .func = do_migration_v4_v5},
    {.name = "v5 to v6",  .func = do_migration_v5_v6},
    {.name = "v6 to v7",  .func = do_migration_v6_v7},
    {.name = "v7 to v8",  .func = do_migration_v7_v8},
    // the terminator of this array
    {.name = NULL, .func = NULL}
};

DATABASE_FUNC_MIGRATION_LIST context_migration_action[] = {
    {.name = "v0 to v1",  .func = do_migration_noop},
    // the terminator of this array
    {.name = NULL, .func = NULL}
};


int perform_database_migration(sqlite3 *database, int target_version)
{
    return migrate_database(database, target_version, "metadata", migration_action);
}

int perform_context_database_migration(sqlite3 *database, int target_version)
{
    return migrate_database(database, target_version, "context", context_migration_action);
}