diff options
author | Jose M. Guisado <jguisado@soleta.eu> | 2021-04-19 09:26:31 +0000 |
---|---|---|
committer | OpenGnSys Support Team <soporte-og@soleta.eu> | 2021-04-20 11:27:21 +0200 |
commit | 7d74d42c79fb2747e42b4d9e2743abbc86a5354c (patch) | |
tree | f8840f79dc05a2862168231d7d6df6390462acc1 /src | |
parent | c2c6ce93b1feecbcc79b4d52cf68823f14e88410 (diff) |
#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.
Diffstat (limited to 'src')
-rw-r--r-- | src/dbi.h | 2 | ||||
-rw-r--r-- | src/main.c | 3 | ||||
-rw-r--r-- | src/schema.c | 159 |
3 files changed, 164 insertions, 0 deletions
@@ -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 @@ -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 <info@soleta.eu> + * + * 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 <syslog.h> +#include <netinet/in.h> +#include <arpa/inet.h> +#include <string.h> +#include "dbi.h" +#include "cfg.h" +#include <syslog.h> +#include <string.h> +#include <stdio.h> + +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; +} |