DB meteo e Backup automatici con MariaDB

Il database dei dati meteo che viene popolato dai vari sensori ha il seguente diagramma ER. In questo diatgramma IDDisp è chiave primaria nella tabella Dispositivo e chiave esterna nelle altre.

Il tipo scelto è un int (11) e non un contatore poichè domoticz assegna un numero intero ai dispositivi. Nella chiamate JSon dal sensore a domoticz si userà proprio questo numero. In tal modo si crea un ponte tra il database meteo e domoticz. Per tenere traccia anche qui di che tipo di dispositivo si tratta e di dove si trova sono state aggiunte anche le entità Posizione e Tipo. Per le chiavi primarie che non sono IDDisp  si è scelto di renderli semplicemente autoincrement.

Il DBMS scelto è  MariaDB di seguito il dump dell’SQL

-- Host:                         host IP
-- Versione server:              10.1.23-MariaDB-9+deb9u1 - Raspbian 9.0
-- S.O. server:                  debian-linux-gnueabihf
-- HeidiSQL Versione:            9.4.0.5125
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


-- Dump della struttura del database meteo
CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `meteo`;

-- Dump della struttura di tabella meteo.Dispositivo
CREATE TABLE IF NOT EXISTS `Dispositivo` (
  `IDDisp` int(11) NOT NULL,
  `Nome` varchar(50) NOT NULL,
  `IDPosizione` int(11) NOT NULL,
  `IndirizzoIP` int(11) unsigned DEFAULT NULL,
  `IDTipo` int(11) NOT NULL,
  PRIMARY KEY (`IDDisp`),
  KEY `FK_Dispositivo_Posizione` (`IDPosizione`),
  KEY `FK_Dispositivo_Tipo` (`IDTipo`),
  CONSTRAINT `FK_Dispositivo_Posizione` FOREIGN KEY (`IDPosizione`) REFERENCES `Posizione` (`IDPosizione`) ON DELETE NO ACTION,
  CONSTRAINT `FK_Dispositivo_Tipo` FOREIGN KEY (`IDTipo`) REFERENCES `Tipo` (`IDTipo`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dump dei dati della tabella meteo.Dispositivo: ~1 rows (circa)
/*!40000 ALTER TABLE `Dispositivo` DISABLE KEYS */;
INSERT INTO `Dispositivo` (`IDDisp`, `Nome`, `IDPosizione`, `IndirizzoIP`, `IDTipo`) VALUES
   (15, 'TempHum', 1, NULL, 1);
/*!40000 ALTER TABLE `Dispositivo` ENABLE KEYS */;

-- Dump della struttura di tabella meteo.Luminosita
CREATE TABLE IF NOT EXISTS `Luminosita` (
  `IDLum` int(11) NOT NULL AUTO_INCREMENT,
  `Time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `LUM` int(11) DEFAULT NULL,
  `IDDisp` int(11) NOT NULL,
  PRIMARY KEY (`IDLum`),
  KEY `FK_Luminosita_Dispositivo` (`IDDisp`),
  CONSTRAINT `FK_Luminosita_Dispositivo` FOREIGN KEY (`IDDisp`) REFERENCES `Dispositivo` (`IDDisp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dump dei dati della tabella meteo.Luminosita: ~0 rows (circa)
/*!40000 ALTER TABLE `Luminosita` DISABLE KEYS */;
/*!40000 ALTER TABLE `Luminosita` ENABLE KEYS */;

-- Dump della struttura di tabella meteo.Posizione
CREATE TABLE IF NOT EXISTS `Posizione` (
  `IDPosizione` int(11) NOT NULL,
  `Piano` int(1) DEFAULT NULL,
  `Nome` varchar(30) NOT NULL,
  `Descrizione` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`IDPosizione`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dump dei dati della tabella meteo.Posizione: ~2 rows (circa)
/*!40000 ALTER TABLE `Posizione` DISABLE KEYS */;
INSERT INTO `Posizione` (`IDPosizione`, `Piano`, `Nome`, `Descrizione`) VALUES
   (1, 1, 'Mansarda', NULL),
   (2, 0, 'Ingresso', NULL);
/*!40000 ALTER TABLE `Posizione` ENABLE KEYS */;

-- Dump della struttura di tabella meteo.Temperatura
CREATE TABLE IF NOT EXISTS `Temperatura` (
  `IDTemp` int(11) NOT NULL AUTO_INCREMENT,
  `Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Temp` double(5,2) unsigned NOT NULL,
  `IDDisp` int(11) NOT NULL,
  PRIMARY KEY (`IDTemp`),
  KEY `FK_Temperatura_Dispositivo` (`IDDisp`),
  CONSTRAINT `FK_Temperatura_Dispositivo` FOREIGN KEY (`IDDisp`) REFERENCES `Dispositivo` (`IDDisp`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4;

-- Dump dei dati della tabella meteo.Temperatura: ~0 rows (circa)
/*!40000 ALTER TABLE `Temperatura` DISABLE KEYS */;
/*!40000 ALTER TABLE `Temperatura` ENABLE KEYS */;

-- Dump della struttura di tabella meteo.Tipo
CREATE TABLE IF NOT EXISTS `Tipo` (
  `IDTipo` int(11) NOT NULL,
  `Nome` varchar(30) DEFAULT NULL,
  `Descrizione` varchar(50) NOT NULL,
  PRIMARY KEY (`IDTipo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dump dei dati della tabella meteo.Tipo: ~1 rows (circa)
/*!40000 ALTER TABLE `Tipo` DISABLE KEYS */;
INSERT INTO `Tipo` (`IDTipo`, `Nome`, `Descrizione`) VALUES
   (1, 'Sensore', '');
/*!40000 ALTER TABLE `Tipo` ENABLE KEYS */;

-- Dump della struttura di tabella meteo.Umidita
CREATE TABLE IF NOT EXISTS `Umidita` (
  `IDHum` int(11) NOT NULL AUTO_INCREMENT,
  `Time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `Hum` tinyint(3) unsigned NOT NULL,
  `IDDisp` int(11) NOT NULL,
  PRIMARY KEY (`IDHum`),
  KEY `FK_Luminosita_Dispositivo` (`IDDisp`),
  CONSTRAINT `Umidita_ibfk_1` FOREIGN KEY (`IDDisp`) REFERENCES `Dispositivo` (`IDDisp`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;

-- Dump dei dati della tabella meteo.Umidita: ~0 rows (circa)
/*!40000 ALTER TABLE `Umidita` DISABLE KEYS */;
/*!40000 ALTER TABLE `Umidita` ENABLE KEYS */;

-- Dump della struttura di tabella meteo.Vento
CREATE TABLE IF NOT EXISTS `Vento` (
  `IDVento` int(11) NOT NULL AUTO_INCREMENT,
  `Time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `IntensitaVento` int(11) DEFAULT NULL,
  `Direzione` varchar(50) DEFAULT 'Nord,Sud,Est,Ovest',
  `IDDisp` int(11) NOT NULL,
  PRIMARY KEY (`IDVento`),
  KEY `FK_Luminosita_Dispositivo` (`IDDisp`),
  CONSTRAINT `Vento_ibfk_1` FOREIGN KEY (`IDDisp`) REFERENCES `Dispositivo` (`IDDisp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;

-- Dump dei dati della tabella meteo.Vento: ~0 rows (circa)
/*!40000 ALTER TABLE `Vento` DISABLE KEYS */;
/*!40000 ALTER TABLE `Vento` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Infine il comando sudo mysqldump -u "root" --databases "meteo"> meteobackup_$(date "+%b-%d-%Y-%H-%M-%S").sql

è stato inserito in un file .sh richiamato via cronjob ogni settimana in modo da creare un backup del DB. Il backup viene poi salvato in automatico su  di un FTP server posto su un altro host.