Context Navigation

schema.c

Last change on this file was a0a3470, checked in by Jose M. Guisado <jguisado@…>, 2 years ago

#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@…>

Property mode set to 100644

File size: 7.8 KB

Line
1	/*
2	* Copyright (C) 2020-2021 Soleta Networks <info@soleta.eu>
3	*
4	* This program is free software: you can redistribute it and/or modify it under
5	* the terms of the GNU Affero General Public License as published by the
6	* Free Software Foundation; either version 3 of the License, or
7	* (at your option) any later version.
8	*/
9
10	#include <syslog.h>
11	#include <netinet/in.h>
12	#include <arpa/inet.h>
13	#include <string.h>
14	#include "dbi.h"
15	#include "cfg.h"
16	#include <syslog.h>
17	#include <string.h>
18	#include <stdio.h>
19
20	#define OG_SCHEMA_STMTS_V2 7
21
22	static const char *stmts_v2[OG_SCHEMA_STMTS_V2] = {
23	[0] = "ALTER TABLE `aulas` "
24	"ADD CONSTRAINT FK_centros "
25	"FOREIGN KEY (`idcentro`) "
26	"REFERENCES `centros` (`idcentro`) ON DELETE CASCADE",
27	[1] = "ALTER TABLE `ordenadores` "
28	"ADD CONSTRAINT FK_aulas "
29	"FOREIGN KEY (`idaula`) "
30	"REFERENCES `aulas` (`idaula`) ON DELETE CASCADE",
31	[2] = "ALTER TABLE `ordenadores_particiones` "
32	"ADD CONSTRAINT FK_ordenadores "
33	"FOREIGN KEY (`idordenador`) "
34	"REFERENCES `ordenadores` (`idordenador`) ON DELETE CASCADE",
35	[3] = "DELETE PS FROM perfilessoft_softwares AS PS "
36	"WHERE NOT EXISTS ("
37	"SELECT null FROM softwares AS S "
38	"WHERE S.idsoftware = PS.idsoftware)",
39	[4] = "ALTER TABLE `perfilessoft_softwares` "
40	"ADD CONSTRAINT FK_softwares "
41	"FOREIGN KEY (`idsoftware`) "
42	"REFERENCES `softwares` (`idsoftware`) ON DELETE CASCADE",
43	[5] = "ALTER TABLE `perfilessoft_softwares` "
44	"ADD CONSTRAINT FK_perfilessoft "
45	"FOREIGN KEY (`idperfilsoft`) "
46	"REFERENCES `perfilessoft` (`idperfilsoft`) ON DELETE CASCADE",
47	[6] = "UPDATE version SET version = 2",
48	};
49
50	static int og_dbi_create_version(struct og_dbi *dbi)
51	{
52	const char *msglog;
53	dbi_result result;
54
55	result = dbi_conn_queryf(dbi->conn, "CREATE TABLE `version` "
56	"(`version` smallint unsigned NOT NULL) "
57	"ENGINE='MyISAM' COLLATE 'utf8_general_ci'");
58	if (!result) {
59	dbi_conn_error(dbi->conn, &msglog);
60	syslog(LOG_INFO, "Could not create schema version table (%s:%d) %s\n",
61	__func__, __LINE__, msglog);
62	return -1;
63	}
64	dbi_result_free(result);
65
66	result = dbi_conn_queryf(dbi->conn, "INSERT INTO `version` (`version`) VALUES ('0')");
67	if (!result) {
68	dbi_conn_error(dbi->conn, &msglog);
69	syslog(LOG_INFO, "Could not insert into schema version table (%s:%d) %s\n",
70	__func__, __LINE__, msglog);
71	return -1;
72	}
73	dbi_result_free(result);
74
75	return 0;
76	}
77	static int og_dbi_schema_version(struct og_dbi *dbi)
78	{
79	const char *msglog;
80	dbi_result result;
81	uint32_t version;
82
83	result = dbi_conn_queryf(dbi->conn, "SELECT * from version");
84	if (!result) {
85	dbi_conn_error(dbi->conn, &msglog);
86	syslog(LOG_INFO, "no version table found (%s:%d) %s\n",
87	__func__, __LINE__, msglog);
88	return -1;
89	}
90
91	if (!dbi_result_last_row(result)) {
92	dbi_conn_error(dbi->conn, &msglog);
93	syslog(LOG_ERR, "cannot get last row from version table (%s:%d) %s\n",
94	__func__, __LINE__, msglog);
95	return -1;
96	}
97
98	version = dbi_result_get_uint(result, "version");
99
100	dbi_result_free(result);
101
102	return version;
103	}
104
105	static int og_dbi_schema_v1(struct og_dbi *dbi)
106	{
107	const char msglog, command;
108	dbi_result result, result_alter;
109
110	result = dbi_conn_queryf(dbi->conn, "SELECT concat('alter table `',TABLE_SCHEMA,'`.`',TABLE_NAME,'` engine=innodb;')"
111	"AS cmd FROM information_schema.TABLES WHERE TABLE_SCHEMA='%s'",
112	ogconfig.db.name);
113
114	while (dbi_result_next_row(result)) {
115	command = dbi_result_get_string(result, "cmd");
116
117	syslog(LOG_DEBUG, "Upgrading to innoDB: %s\n", command);
118	result_alter = dbi_conn_query(dbi->conn, command);
119	if (!result_alter) {
120	dbi_conn_error(dbi->conn, &msglog);
121	syslog(LOG_INFO, "Error when upgrading engine to innoDB (%s:%d) %s\n",
122	__func__, __LINE__, msglog);
123	return -1;
124	}
125	dbi_result_free(result_alter);
126	}
127	dbi_result_free(result);
128
129	result = dbi_conn_query(dbi->conn, "UPDATE version SET version = 1");
130	if (!result) {
131	dbi_conn_error(dbi->conn, &msglog);
132	syslog(LOG_INFO, "Could not update version row (%s:%d) %s\n",
133	__func__, __LINE__, msglog);
134	return -1;
135	}
136	dbi_result_free(result);
137
138	return 0;
139	}
140
141	static int og_dbi_schema_v2(struct og_dbi *dbi)
142	{
143	const char *msglog;
144	dbi_result result;
145	int ret, i;
146
147	ret = dbi_conn_transaction_begin(dbi->conn);
148	if (ret) {
149	syslog(LOG_DEBUG, "could not begin a transaction (%s:%d)\n",
150	__func__, __LINE__);
151	goto err_no_trans;
152	}
153
154	for (i = 0; i < OG_SCHEMA_STMTS_V2; i++) {
155	result = dbi_conn_query(dbi->conn, stmts_v2[i]);
156	if (!result) {
157	dbi_conn_error(dbi->conn, &msglog);
158	syslog(LOG_ERR, "Statement number %d failed (%s:%d): %s\n",
159	i, __func__, __LINE__, msglog);
160	goto err_trans;
161	}
162	dbi_result_free(result);
163	}
164
165	ret = dbi_conn_transaction_commit(dbi->conn);
166	if (ret) {
167	syslog(LOG_DEBUG, "could not commit a transaction (%s:%d)\n",
168	__func__, __LINE__);
169	goto err_trans;
170	}
171	return 0;
172
173	err_trans:
174	dbi_conn_transaction_rollback(dbi->conn);
175	err_no_trans:
176	return -1;
177	}
178
179	static int og_dbi_schema_v3(struct og_dbi *dbi)
180	{
181	const char *msglog;
182	dbi_result result;
183
184	syslog(LOG_DEBUG, "Adding disk type to ordenadores_particiones\n");
185	result = dbi_conn_query(dbi->conn,
186	"ALTER TABLE ordenadores_particiones "
187	"ADD disk_type VARCHAR(32) DEFAULT NULL "
188	"AFTER numdisk;");
189	if (!result) {
190	dbi_conn_error(dbi->conn, &msglog);
191	syslog(LOG_INFO, "Error when adding disk type (%s:%d) %s\n",
192	__func__, __LINE__, msglog);
193	return -1;
194	}
195	dbi_result_free(result);
196
197	result = dbi_conn_query(dbi->conn, "UPDATE version SET version = 3");
198	if (!result) {
199	dbi_conn_error(dbi->conn, &msglog);
200	syslog(LOG_INFO, "Could not update version row (%s:%d) %s\n",
201	__func__, __LINE__, msglog);
202	return -1;
203	}
204	dbi_result_free(result);
205
206	return 0;
207	}
208
209	static int og_dbi_schema_v4(struct og_dbi *dbi)
210	{
211	const char *msglog;
212	dbi_result result;
213
214	syslog(LOG_DEBUG, "Adding identorno to ordenadores\n");
215	result = dbi_conn_query(dbi->conn,
216	"ALTER TABLE `ordenadores` "
217	"ADD `identorno` int(11) NOT NULL DEFAULT '1' "
218	"AFTER `idordenador`, "
219	"ADD CONSTRAINT `FK_entornos` "
220	"FOREIGN KEY (`identorno`) "
221	"REFERENCES `entornos` (`identorno`) "
222	"ON DELETE RESTRICT;");
223	if (!result) {
224	dbi_conn_error(dbi->conn, &msglog);
225	syslog(LOG_INFO, "Error when adding identorno (%s:%d) %s\n",
226	__func__, __LINE__, msglog);
227	return -1;
228	}
229	dbi_result_free(result);
230
231	result = dbi_conn_query(dbi->conn, "UPDATE version SET version = 4");
232	if (!result) {
233	dbi_conn_error(dbi->conn, &msglog);
234	syslog(LOG_INFO, "Could not update version row (%s:%d) %s\n",
235	__func__, __LINE__, msglog);
236	return -1;
237	}
238	dbi_result_free(result);
239
240	return 0;
241	}
242
243	static struct og_schema_version {
244	int version;
245	int (update)(struct og_dbi dbi);
246	} schema_version[] = {
247	{ .version = 1, .update = og_dbi_schema_v1 },
248	{ .version = 2, .update = og_dbi_schema_v2 },
249	{ .version = 3, .update = og_dbi_schema_v3 },
250	{ .version = 4, .update = og_dbi_schema_v4 },
251	{ 0, NULL },
252	};
253
254	int og_dbi_schema_update(void)
255	{
256	int version, i, err;
257	struct og_dbi *dbi;
258	const char *msglog;
259
260	dbi = og_dbi_open(&ogconfig.db);
261	if (!dbi) {
262	dbi_conn_error(dbi->conn, &msglog);
263	syslog(LOG_ERR, "failed to query database (%s:%d) %s\n",
264	__func__, __LINE__, msglog);
265	return -1;
266	}
267
268	version = og_dbi_schema_version(dbi);
269
270	if (version < 0) {
271	syslog(LOG_INFO, "creating table version in schema\n");
272	og_dbi_create_version(dbi);
273	} else {
274	syslog(LOG_INFO, "database schema version %d\n", version);
275	}
276
277	for (i = 0; schema_version[i].version; i++) {
278	if (version >= schema_version[i].version)
279	continue;
280
281	syslog(LOG_INFO, "upgrading to schema version %d\n", schema_version[i].version);
282
283	err = schema_version[i].update(dbi);
284	if (err < 0) {
285	syslog(LOG_ERR, "failed to update schema!\n");
286	og_dbi_close(dbi);
287	return -1;
288	}
289	}
290
291	og_dbi_close(dbi);
292
293	return 0;
294	}

Note: See TracBrowser for help on using the repository browser.

Context Navigation

source: ogServer-Git/src/schema.c

Download in other formats: