#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 <jguisado@soleta.eu>

1 files changed, 35 insertions, 0 deletions

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				},
 };