From 7d74d42c79fb2747e42b4d9e2743abbc86a5354c Mon Sep 17 00:00:00 2001 From: "Jose M. Guisado" Date: Mon, 19 Apr 2021 09:26:31 +0000 Subject: #1042 Update database schema automatically This patch adds database schema management capabilities to ogServer: - ogServer now tracks the version of its database schema, if no version is detected, creates a 'version' table with a single row starting at 0. - ogServer can upgrade its database schema to a newer version if detected. (ogServer ships required SQL commands to do so) If ogServer is unable to upgrade the schema at startup (if needed be) it *will not* start. Defines schema update v1 which upgrades database engine tables of ogServer database (usually named 'ogAdmBD') from myISAM to innoDB. --- Makefile.am | 1 + src/dbi.h | 2 + src/main.c | 3 ++ src/schema.c | 159 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 165 insertions(+) create mode 100644 src/schema.c diff --git a/Makefile.am b/Makefile.am index 5a38431..bf93816 100644 --- a/Makefile.am +++ b/Makefile.am @@ -8,6 +8,7 @@ ogserver_SOURCES= src/ogAdmServer.c \ src/dbi.c \ src/main.c \ src/schedule.c \ + src/schema.c \ src/utils.c \ src/rest.c \ src/client.c \ diff --git a/src/dbi.h b/src/dbi.h index 1daaeb8..798fecd 100644 --- a/src/dbi.h +++ b/src/dbi.h @@ -93,4 +93,6 @@ int og_dbi_get_computer_info(struct og_dbi *dbi, struct og_computer *computer, struct in_addr addr); int og_dbi_add_image(struct og_dbi *dbi, const struct og_image *image); +int og_dbi_schema_update(void); + #endif diff --git a/src/main.c b/src/main.c index 36b2fe0..ea04a2b 100644 --- a/src/main.c +++ b/src/main.c @@ -87,6 +87,9 @@ int main(int argc, char *argv[]) ev_io_init(&ev_io_agent_rest, og_server_accept_cb, socket_agent_rest, EV_READ); ev_io_start(og_loop, &ev_io_agent_rest); + if (og_dbi_schema_update() < 0) + exit(EXIT_FAILURE); + if (og_dbi_schedule_get() < 0) { syslog(LOG_ERR, "Cannot connect to database\n"); exit(EXIT_FAILURE); diff --git a/src/schema.c b/src/schema.c new file mode 100644 index 0000000..f7b705b --- /dev/null +++ b/src/schema.c @@ -0,0 +1,159 @@ +/* + * Copyright (C) 2021 Soleta Networks + * + * This program is free software: you can redistribute it and/or modify it under + * the terms of the GNU Affero General Public License as published by the + * Free Software Foundation, version 3. + */ + +#include +#include +#include +#include +#include "dbi.h" +#include "cfg.h" +#include +#include +#include + +struct og_server_cfg ogconfig; + +static int og_dbi_create_version(struct og_dbi *dbi) +{ + const char *msglog; + dbi_result result; + + result = dbi_conn_queryf(dbi->conn, "CREATE TABLE `version` " + "(`version` smallint unsigned NOT NULL) " + "ENGINE='MyISAM' COLLATE 'utf8_general_ci'"); + if (!result) { + dbi_conn_error(dbi->conn, &msglog); + syslog(LOG_INFO, "Could not create schema version table (%s:%d) %s\n", + __func__, __LINE__, msglog); + return -1; + } + dbi_result_free(result); + + result = dbi_conn_queryf(dbi->conn, "INSERT INTO `version` (`version`) VALUES ('0')"); + if (!result) { + dbi_conn_error(dbi->conn, &msglog); + syslog(LOG_INFO, "Could not insert into schema version table (%s:%d) %s\n", + __func__, __LINE__, msglog); + return -1; + } + dbi_result_free(result); + + return 0; +} +static int og_dbi_schema_version(struct og_dbi *dbi) +{ + const char *msglog; + dbi_result result; + uint32_t version; + + result = dbi_conn_queryf(dbi->conn, "SELECT * from version"); + if (!result) { + dbi_conn_error(dbi->conn, &msglog); + syslog(LOG_INFO, "no version table found (%s:%d) %s\n", + __func__, __LINE__, msglog); + return -1; + } + + if (!dbi_result_last_row(result)) { + dbi_conn_error(dbi->conn, &msglog); + syslog(LOG_ERR, "cannot get last row from version table (%s:%d) %s\n", + __func__, __LINE__, msglog); + return -1; + } + + version = dbi_result_get_uint(result, "version"); + + dbi_result_free(result); + + return version; +} +static int og_dbi_schema_v1(struct og_dbi *dbi) +{ + const char *msglog, *command; + dbi_result result, result_alter; + + result = dbi_conn_queryf(dbi->conn, "SELECT concat('alter table `',TABLE_SCHEMA,'`.`',TABLE_NAME,'` engine=innodb;')" + "AS cmd FROM information_schema.TABLES WHERE TABLE_SCHEMA='%s'", + ogconfig.db.name); + + while (dbi_result_next_row(result)) { + command = dbi_result_get_string(result, "cmd"); + + syslog(LOG_DEBUG, "Upgrading to innoDB: %s\n", command); + result_alter = dbi_conn_query(dbi->conn, command); + if (!result_alter) { + dbi_conn_error(dbi->conn, &msglog); + syslog(LOG_INFO, "Error when upgrading engine to innoDB (%s:%d) %s\n", + __func__, __LINE__, msglog); + return -1; + } + dbi_result_free(result_alter); + } + dbi_result_free(result); + + result = dbi_conn_query(dbi->conn, "UPDATE version SET version = 1"); + if (!result) { + dbi_conn_error(dbi->conn, &msglog); + syslog(LOG_INFO, "Could not update version row (%s:%d) %s\n", + __func__, __LINE__, msglog); + return -1; + } + dbi_result_free(result); + + return 0; +} + +static struct og_schema_version { + int version; + int (*update)(struct og_dbi *dbi); +} schema_version[] = { + { .version = 1, .update = og_dbi_schema_v1 }, + { 0, NULL }, +}; + +int og_dbi_schema_update(void) +{ + int version, i, err; + struct og_dbi *dbi; + const char *msglog; + + dbi = og_dbi_open(&ogconfig.db); + if (!dbi) { + dbi_conn_error(dbi->conn, &msglog); + syslog(LOG_ERR, "failed to query database (%s:%d) %s\n", + __func__, __LINE__, msglog); + return -1; + } + + version = og_dbi_schema_version(dbi); + + if (version < 0) { + syslog(LOG_INFO, "creating table version in schema\n"); + og_dbi_create_version(dbi); + } else { + syslog(LOG_INFO, "database schema version %d\n", version); + } + + for (i = 0; schema_version[i].version; i++) { + if (version >= schema_version[i].version) + continue; + + syslog(LOG_INFO, "upgrading to schema version %d\n", schema_version[i].version); + + err = schema_version[i].update(dbi); + if (err < 0) { + syslog(LOG_ERR, "failed to update schema!\n"); + og_dbi_close(dbi); + return -1; + } + } + + og_dbi_close(dbi); + + return 0; +} -- cgit v1.2.3-18-g5258