From a0a3470682852d5f72967553d5debe41e86eca78 Mon Sep 17 00:00:00 2001 From: Javier Sánchez Parra Date: Tue, 31 May 2022 12:08:57 +0200 Subject: #1074 rest: set_mode: add support for different ogserver addresses Add foreign key "identornos" from table "entornos" to table "ordenadores". A row in table "entornos" represent a valid ogServer address. Multiple ogServer valid addresses can exist when running several instances or a single ogServer instance is exposed to different networks. Can't delete rows in "entornos" table nor update their id (primary key) if the row has any associated clients ({ON UPDATE/ON DELETE} RESTRICT). Allows assigning different but valid ogServer IPs to clients. Enabling support for multiple instances of ogServer (e.g: load balancing) or exposing a single ogServer instance to different networks (e.g: VLAN). Look up for the valid ogServer IP of a given client when changing a client's mode (og_set_client_mode). Determines valid ogServer IP using a JOIN statement. JOIN entornos USING(identorno) Reuses the fetched ip using a statement variable. @serverip:=entornos.ipserveradm For example, for a two VLAN setup: vlan1 ogserver: 192.168.56.10 vlan2 ogserver: 192.168.57.10 The "entornos" table should look like: identorno ipserveradm ... --------- ----------- ... 1 192.168.56.10 ... 2 192.168.57.10 ... And computers in the "ordenadores" table might look like: idordenador identorno ... ---------- --------- ... 1 1 ... 2 1 ... 3 2 ... 4 2 ... ... ... ... Additionally, splits the SQL query for better readability. Co-authored-by: Jose Guisado --- src/schema.c | 35 +++++++++++++++++++++++++++++++++++ 1 file changed, 35 insertions(+) (limited to 'src/schema.c') diff --git a/src/schema.c b/src/schema.c index 1d935b9..56d52ba 100644 --- a/src/schema.c +++ b/src/schema.c @@ -206,6 +206,40 @@ static int og_dbi_schema_v3(struct og_dbi *dbi) return 0; } +static int og_dbi_schema_v4(struct og_dbi *dbi) +{ + const char *msglog; + dbi_result result; + + syslog(LOG_DEBUG, "Adding identorno to ordenadores\n"); + result = dbi_conn_query(dbi->conn, + "ALTER TABLE `ordenadores` " + "ADD `identorno` int(11) NOT NULL DEFAULT '1' " + "AFTER `idordenador`, " + "ADD CONSTRAINT `FK_entornos` " + "FOREIGN KEY (`identorno`) " + "REFERENCES `entornos` (`identorno`) " + "ON DELETE RESTRICT;"); + if (!result) { + dbi_conn_error(dbi->conn, &msglog); + syslog(LOG_INFO, "Error when adding identorno (%s:%d) %s\n", + __func__, __LINE__, msglog); + return -1; + } + dbi_result_free(result); + + result = dbi_conn_query(dbi->conn, "UPDATE version SET version = 4"); + 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); @@ -213,6 +247,7 @@ static struct og_schema_version { { .version = 1, .update = og_dbi_schema_v1 }, { .version = 2, .update = og_dbi_schema_v2 }, { .version = 3, .update = og_dbi_schema_v3 }, + { .version = 4, .update = og_dbi_schema_v4 }, { 0, NULL }, }; -- cgit v1.2.3-18-g5258