path: root/src/dbi.c

/*
 * Copyright (C) 2020-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; either version 3 of the License, or
 * (at your option) any later version.
 */

#include <syslog.h>
#include <netinet/in.h>
#include <arpa/inet.h>
#include <string.h>
#include "dbi.h"
#include <syslog.h>
#include <string.h>
#include <stdio.h>

struct og_dbi *og_dbi_open(struct og_dbi_config *config)
{
	struct og_dbi *dbi;

	dbi = (struct og_dbi *)malloc(sizeof(struct og_dbi));
	if (!dbi)
		return NULL;

	dbi_initialize_r(NULL, &dbi->inst);
	dbi->conn = dbi_conn_new_r("mysql", dbi->inst);
	if (!dbi->conn) {
		free(dbi);
		return NULL;
	}

	dbi_conn_set_option(dbi->conn, "host", config->ip);
	dbi_conn_set_option(dbi->conn, "port", config->port);
	dbi_conn_set_option(dbi->conn, "username", config->user);
	dbi_conn_set_option(dbi->conn, "password", config->pass);
	dbi_conn_set_option(dbi->conn, "dbname", config->name);
	dbi_conn_set_option(dbi->conn, "encoding", "UTF-8");

	if (dbi_conn_connect(dbi->conn) < 0) {
		dbi_shutdown_r(dbi->inst);
		free(dbi);
		return NULL;
	}

	return dbi;
}

void og_dbi_close(struct og_dbi *dbi)
{
	dbi_conn_close(dbi->conn);
	dbi_shutdown_r(dbi->inst);
	free(dbi);
}

int og_dbi_get_computer_info(struct og_dbi *dbi, struct og_computer *computer,
			     struct in_addr addr)
{
	const char *msglog;
	dbi_result result;

	result = dbi_conn_queryf(dbi->conn,
				 "SELECT ordenadores.idordenador,"
				 "       ordenadores.nombreordenador,"
				 "       ordenadores.numserie,"
				 "       ordenadores.ip,"
				 "       ordenadores.mac,"
				 "       ordenadores.idaula,"
				 "       ordenadores.idperfilhard,"
				 "       ordenadores.idrepositorio,"
				 "       ordenadores.arranque,"
				 "       ordenadores.netiface,"
				 "       ordenadores.netdriver,"
				 "       ordenadores.idproautoexec,"
				 "       ordenadores.oglivedir,"
				 "       ordenadores.inremotepc,"
				 "       ordenadores.maintenance,"
				 "	 aulas.netmask,"
				 "       centros.idcentro "
				 "FROM ordenadores "
				 "INNER JOIN aulas ON aulas.idaula=ordenadores.idaula "
				 "INNER JOIN centros ON centros.idcentro=aulas.idcentro "
				 "WHERE ordenadores.ip='%s'", inet_ntoa(addr));
	if (!result) {
		dbi_conn_error(dbi->conn, &msglog);
		syslog(LOG_ERR, "failed to query database (%s:%d) %s\n",
		       __func__, __LINE__, msglog);
		return -1;
	}
	if (!dbi_result_next_row(result)) {
		syslog(LOG_ERR, "client does not exist in database (%s:%d)\n",
		       __func__, __LINE__);
		dbi_result_free(result);
		return -1;
	}

	computer->id = dbi_result_get_uint(result, "idordenador");
	snprintf(computer->name, sizeof(computer->name), "%s",
		 dbi_result_get_string(result, "nombreordenador"));
	snprintf(computer->serial_number, sizeof(computer->serial_number), "%s",
		 dbi_result_get_string(result, "numserie"));
	snprintf(computer->ip, sizeof(computer->ip), "%s",
		 dbi_result_get_string(result, "ip"));
	snprintf(computer->mac, sizeof(computer->mac), "%s",
		 dbi_result_get_string(result, "mac"));
	computer->room = dbi_result_get_uint(result, "idaula");
	computer->center = dbi_result_get_uint(result, "idcentro");
	computer->hardware_id = dbi_result_get_uint(result, "idperfilhard");
	computer->repo_id = dbi_result_get_uint(result, "idrepositorio");
	snprintf(computer->netmask, sizeof(computer->netmask), "%s",
		 dbi_result_get_string(result, "netmask"));
	snprintf(computer->boot, sizeof(computer->boot), "%s",
		 dbi_result_get_string(result, "arranque"));
	snprintf(computer->netiface, sizeof(computer->netiface), "%s",
		 dbi_result_get_string(result, "netiface"));
	snprintf(computer->netdriver, sizeof(computer->netdriver), "%s",
		 dbi_result_get_string(result, "netdriver"));
	computer->procedure_id = dbi_result_get_uint(result, "idproautoexec");
	snprintf(computer->livedir, sizeof(computer->livedir), "%s",
		 dbi_result_get_string(result, "oglivedir"));
	computer->remote = dbi_result_get_uint(result, "inremotepc") != 0;
	computer->maintenance = dbi_result_get_uint(result, "maintenance") != 0;

	dbi_result_free(result);

	return 0;
}

int og_dbi_get_center_info(struct og_dbi *dbi, struct og_center *center)
{
	const char *msglog;
	dbi_result result;

	result = dbi_conn_queryf(dbi->conn,
				 "SELECT nombrecentro, idcentro, comentarios "
				 "FROM centros WHERE idcentro=%d",
				 center->id);
	if (!result) {
		dbi_conn_error(dbi->conn, &msglog);
		syslog(LOG_ERR, "failed to query database (%s:%d) %s\n",
		       __func__, __LINE__, msglog);
		return -1;
	}

	if (!dbi_result_next_row(result)) {
		syslog(LOG_ERR, "center does not exist in database (%s:%d)\n",
		       __func__, __LINE__);
		dbi_result_free(result);
		return -1;
	}

	snprintf(center->name, sizeof(center->name), "%s",
		 dbi_result_get_string(result, "nombrecentro"));
	snprintf(center->comment, sizeof(center->comment), "%s",
		 dbi_result_get_string(result, "comentarios"));

	dbi_result_free(result);

	return 0;
}

int og_dbi_get_room_info(struct og_dbi *dbi, struct og_room *room,
			 uint32_t room_id)
{
	const char *msglog;
	dbi_result result;

	result = dbi_conn_queryf(dbi->conn,
				 "SELECT nombreaula, router, netmask "
				 "FROM aulas WHERE idaula=%d",
				 room_id);
	if (!result) {
		dbi_conn_error(dbi->conn, &msglog);
		syslog(LOG_ERR, "failed to query database (%s:%d) %s\n",
		       __func__, __LINE__, msglog);
		return -1;
	}

	if (!dbi_result_next_row(result)) {
		syslog(LOG_ERR, "room does not exist in database (%s:%d)\n",
		       __func__, __LINE__);
		dbi_result_free(result);
		return -1;
	}

	room->id = room_id;
	snprintf(room->name, sizeof(room->name), "%s",
		 dbi_result_get_string(result, "nombreaula"));
	snprintf(room->gateway, sizeof(room->gateway), "%s",
		 dbi_result_get_string(result, "router"));
	snprintf(room->netmask, sizeof(room->netmask), "%s",
		 dbi_result_get_string(result, "netmask"));

	dbi_result_free(result);

	return 0;
}

#define OG_UNASSIGNED_SW_ID 0
#define OG_IMAGE_DEFAULT_TYPE 1 /* monolithic */

int og_dbi_add_image(struct og_dbi *dbi, struct og_image *image)
{
	const char *msglog;
	dbi_result result;


	result = dbi_conn_queryf(dbi->conn,
				 "INSERT INTO imagenes (nombreca, "
				 "descripcion, "
				 "idperfilsoft, "
				 "idcentro, "
				 "comentarios, "
				 "grupoid, "
				 "idrepositorio, "
				 "tipo, "
				 "ruta) "
				 "VALUES ('%s', '%s', %u, %lu, '', %u, %lu, %u, '')",
				 image->name, image->description,
				 OG_UNASSIGNED_SW_ID, image->center_id,
				 image->group_id, image->repo_id,
				 OG_IMAGE_DEFAULT_TYPE);

	if (!result) {
		dbi_conn_error(dbi->conn, &msglog);
		syslog(LOG_ERR, "failed to add client to database (%s:%d) %s\n",
		       __func__, __LINE__, msglog);
		return -1;
	}

	dbi_result_free(result);
	image->id = dbi_conn_sequence_last(dbi->conn, NULL);

	return 0;
}

bool og_dbi_get_image(struct og_dbi *dbi, struct og_image *image)
{
	const char *msglog;
	dbi_result result;

	/* old front-end needs this... */
	if (image->repo_id) {
		result = dbi_conn_queryf(dbi->conn,
					 "SELECT nombreca, idimagen FROM imagenes WHERE nombreca = '%s'",
					 image->name);
	} else {
		result = dbi_conn_queryf(dbi->conn,
					 "SELECT nombreca, idimagen FROM imagenes WHERE nombreca = '%s' AND idrepositorio = '%s'",
					 image->name, image->repo_id);
	}
	if (!result) {
		dbi_conn_error(dbi->conn, &msglog);
		syslog(LOG_ERR, "failed to query database (%s:%d) %s\n",
		       __func__, __LINE__, msglog);
		return false;
	}

	if (dbi_result_next_row(result)) {
		image->id = dbi_result_get_uint(result, "idimagen");
		dbi_result_free(result);
		return true;
	}
	dbi_result_free(result);

	return false;
}

int og_dbi_get_server_ip(const struct og_dbi *dbi, const char *client_ip,
			 char *res_server_ip)
{
	const char *msglog, *netmask_ip, *server_ip, *first_server_ip = NULL;
	struct in_addr client, netmask, server;
	bool found = false;
	dbi_result result;

	if (!inet_aton(client_ip, &client)) {
		syslog(LOG_ERR, "failed to parse client IP (%s:%d)\n",
		       __func__, __LINE__);
		return -1;
	}

	result = dbi_conn_queryf(dbi->conn,
				 "SELECT aulas.netmask FROM aulas "
				 "INNER JOIN ordenadores ON ordenadores.idaula = aulas.idaula "
				 "WHERE ordenadores.ip = '%s'", client_ip);
	if (!result) {
		dbi_conn_error(dbi->conn, &msglog);
		syslog(LOG_ERR, "failed to get netmask (%s:%d) %s\n",
		       __func__, __LINE__, msglog);
		return -1;
	}

	if (!dbi_result_next_row(result)) {
		syslog(LOG_ERR, "netmask does not exist in database (%s:%d)\n",
		       __func__, __LINE__);
		dbi_result_free(result);
		return -1;
	}

	netmask_ip = dbi_result_get_string(result, "netmask");
	if (!inet_aton(netmask_ip, &netmask)) {
		syslog(LOG_ERR, "failed to parse netmask (%s:%d)\n",
		       __func__, __LINE__);
		dbi_result_free(result);
		return -1;
	}

	dbi_result_free(result);

	result = dbi_conn_queryf(dbi->conn,
				 "SELECT ipserveradm FROM entornos");
	if (!result) {
		dbi_conn_error(dbi->conn, &msglog);
		syslog(LOG_ERR, "failed to query database (%s:%d) %s\n",
		       __func__, __LINE__, msglog);
		return -1;
	}

	while (dbi_result_next_row(result) > 0) {
		server_ip = dbi_result_get_string(result, "ipserveradm");

		if (server_ip[0] && !first_server_ip)
			first_server_ip = server_ip;

		if (!inet_aton(server_ip, &server)) {
			syslog(LOG_ERR, "failed to get repository IP (%s:%d)\n",
			       __func__, __LINE__);
			dbi_result_free(result);
			return -1;
		}
		client.s_addr &= netmask.s_addr;

		if (client.s_addr != (server.s_addr & netmask.s_addr))
			continue;

		found = true;
		break;
	}

	if (!first_server_ip) {
		syslog(LOG_ERR,
		       "cannot find any server IP for client %s (%s:%d)\n",
		       client_ip, __func__, __LINE__);
		dbi_result_free(result);
		return -1;
	}

	if (found) {
		syslog(LOG_INFO, "using server IP %s for client %s\n",
		       server_ip, client_ip);
	} else {
		server_ip = first_server_ip;
		syslog(LOG_INFO, "falling back to server %s for client %s\n",
		       server_ip, client_ip);
	}

	snprintf(res_server_ip, OG_DB_IP_MAXLEN + 1, "%s", server_ip);
	dbi_result_free(result);

	return 0;
}
int og_dbi_get_repository_ip(const struct og_dbi *dbi, const uint32_t repo_id,
			     const char *client_ip, char *repository_ip)
{
	const char *msglog, *netmask_ip, *repo_ip, *first_repo_ip = NULL;
	struct in_addr client, netmask, repo;
	bool found = false;
	dbi_result result;

	if (!inet_aton(client_ip, &client)) {
		syslog(LOG_ERR, "failed to parse client IP (%s:%d)\n",
		       __func__, __LINE__);
		return -1;
	}

	result = dbi_conn_queryf(dbi->conn,
				 "SELECT aulas.netmask FROM aulas "
				 "INNER JOIN ordenadores ON ordenadores.idaula = aulas.idaula "
				 "WHERE ordenadores.ip = '%s'", client_ip);
	if (!result) {
		dbi_conn_error(dbi->conn, &msglog);
		syslog(LOG_ERR, "failed to get netmask (%s:%d) %s\n",
		       __func__, __LINE__, msglog);
		return -1;
	}

	if (!dbi_result_next_row(result)) {
		syslog(LOG_ERR, "netmask does not exist in database (%s:%d)\n",
		       __func__, __LINE__);
		dbi_result_free(result);
		return -1;
	}

	netmask_ip = dbi_result_get_string(result, "netmask");
	if (!inet_aton(netmask_ip, &netmask)) {
		syslog(LOG_ERR, "failed to parse netmask (%s:%d)\n",
		       __func__, __LINE__);
		dbi_result_free(result);
		return -1;
	}

	dbi_result_free(result);

	result = dbi_conn_queryf(dbi->conn,
				 "SELECT ip FROM repositorios WHERE idrepositorio = %u OR alias = %u",
				 repo_id, repo_id);
	if (!result) {
		dbi_conn_error(dbi->conn, &msglog);
		syslog(LOG_ERR, "failed to query database (%s:%d) %s\n",
		       __func__, __LINE__, msglog);
		return -1;
	}

	while (dbi_result_next_row(result) > 0) {
		repo_ip = dbi_result_get_string(result, "ip");

		if (repo_ip[0] && !first_repo_ip)
			first_repo_ip = repo_ip;

		if (!inet_aton(repo_ip, &repo)) {
			syslog(LOG_ERR, "failed to get repository IP (%s:%d)\n",
			       __func__, __LINE__);
			dbi_result_free(result);
			return -1;
		}
		client.s_addr &= netmask.s_addr;

		if (client.s_addr != (repo.s_addr & netmask.s_addr))
			continue;

		found = true;
		break;
	}

	if (!first_repo_ip) {
		syslog(LOG_ERR,
		       "cannot find any repository with ID %u for client %s (%s:%d)\n",
		       repo_id, client_ip, __func__, __LINE__);
		dbi_result_free(result);
		return -1;
	}

	if (found) {
		syslog(LOG_INFO, "using repository with ID %u and IP %s for client %s\n",
		       repo_id, repo_ip, client_ip);
	} else {
		repo_ip = first_repo_ip;
		syslog(LOG_INFO, "falling back to repository wth ID %u and IP %s for client %s\n",
		       repo_id, repo_ip, client_ip);
	}

	snprintf(repository_ip, OG_DB_IP_MAXLEN + 1, "%s", repo_ip);
	dbi_result_free(result);

	return 0;
}

int og_dbi_get_os_id(const struct og_dbi *dbi, const char *os_name, uint32_t *os_id)
{
	const char *msglog;
	dbi_result result;

	if (strlen(os_name) == 0)
		return 0;

	result = dbi_conn_queryf(dbi->conn,
				 "SELECT idnombreso FROM nombresos "
				 "WHERE nombreso = '%s'", os_name);
	if (!result) {
		dbi_conn_error(dbi->conn, &msglog);
		syslog(LOG_ERR, "failed to query OS id (%s:%d) %s\n",
		       __func__, __LINE__, msglog);
		return -1;
	}

	if (dbi_result_next_row(result)) {
		*os_id = dbi_result_get_uint(result, "idnombreso");
		dbi_result_free(result);
		return 0;
	}

	dbi_result_free(result);

	result = dbi_conn_queryf(dbi->conn,
				 "INSERT INTO nombresos (nombreso) "
				 "VALUES ('%s') ", os_name);
	if (!result) {
		dbi_conn_error(dbi->conn, &msglog);
		syslog(LOG_ERR, "failed to insert OS id (%s:%d) %s\n",
		       __func__, __LINE__, msglog);
		return -1;
	}

	dbi_result_free(result);

	result = dbi_conn_queryf(dbi->conn,
				 "SELECT idnombreso FROM nombresos "
				 "WHERE nombreso = '%s'", os_name);
	if (!result) {
		dbi_conn_error(dbi->conn, &msglog);
		syslog(LOG_ERR, "failed to query OS id (%s:%d) %s\n",
		       __func__, __LINE__, msglog);
		return -1;
	}

	if (!dbi_result_next_row(result)) {
		dbi_result_free(result);
		syslog(LOG_ERR, "failed to query OS id (%s:%d) %s\n",
				__func__, __LINE__, msglog);
		return -1;
	}

	*os_id = dbi_result_get_uint(result, "idnombreso");
	dbi_result_free(result);

	return 0;
}