Heizungssteuerung mit Linux und Raspberry Pi einrichten

Datenbank und Tabellen einrichten

Im Folgenden wird eine Verbindung zur Mysql-Datenbank als Root aufgebaut und danach ein Datenbankbenutzer inklusive Datenbank und dessen Tabellen angelegt.

mysql --user=root mysql --password

Folgender Befehl ist nun auszuführen:

CREATE DATABASE `heating` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

CREATE USER 'pi'@'localhost' IDENTIFIED BY 'raspheat';

GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , FILE ,

INDEX , ALTER , CREATE TEMPORARY TABLES , CREATE VIEW , EVENT,

TRIGGER, SHOW VIEW , CREATE ROUTINE, ALTER ROUTINE,

EXECUTE ON * . * TO 'pi'@'localhost' IDENTIFIED BY 'raspheat' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

GRANT ALL PRIVILEGES ON `pi\_%` . * TO 'pi'@'localhost';

USE heating;

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS `desired_values` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`outside_temp` int(11) NOT NULL,

`return_flow_temp` float NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `outside_temp` (`outside_temp`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Ruecklauftemperatur in abhaengigkeit der aussentemperatur.' AUTO_INCREMENT=61 ;

INSERT INTO `desired_values` (`id`, `outside_temp`, `return_flow_temp`) VALUES

(1, -35, 36), (2, -34, 36), (3, -33, 36), (4, -32, 36), (5, -31, 36), (6, -30, 36), (7, -29, 36), (8, -28, 36), (9, -27, 36), (10, -26, 36), (11, -25, 36),

(12, -24, 36), (13, -23, 36), (14, -22, 36), (15, -21, 36), (16, -20, 36), (17, -19, 36), (18, -18, 36), (19, -17, 36), (20, -16, 36), (21, -15, 36), (22, -14, 36),

(23, -13, 36), (24, -12, 36), (25, -11, 36), (26, -10, 36), (27, -9, 36), (28, -8, 36), (29, -7, 36), (30, -6, 36), (31, -5, 36), (32, -4, 36), (33, -3, 35),

(34, -2, 34), (35, -1, 32), (36, 0, 32), (37, 1, 32), (38, 2, 32), (39, 3, 32), (40, 4, 32), (41, 5, 31), (42, 6, 30), (43, 7, 30), (44, 8, 29), (45, 9, 29),

(46, 10, 28), (47, 11, 28), (48, 12, 27), (49, 13, 27), (50, 14, 26), (51, 15, 26), (52, 16, 26), (53, 17, 25), (54, 18, 25), (55, 19, 25), (56, 20, 25),

(57, 21, 11), (58, 22, 11), (59, 23, 11), (60, 24, 11);

CREATE TABLE IF NOT EXISTS `sensor_values` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(100) COLLATE utf8_bin NOT NULL,

`value` float NOT NULL,

`unit` varchar(100) COLLATE utf8_bin NOT NULL,

`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

KEY `name` (`name`),

KEY `timestamp` (`timestamp`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2657335 ;

CREATE TABLE IF NOT EXISTS `settings` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) COLLATE utf8_bin NOT NULL,

`unit` varchar(255) COLLATE utf8_bin NOT NULL,

`value` text COLLATE utf8_bin NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=6 ;

INSERT INTO `settings` (`id`, `name`, `unit`, `value`) VALUES

(1, 'heater', 'bool', '1'),

(2, 'pump_follow_up_time', 'minutes', '45'), (3, 'slider_nighly_temperature_sink_substract', 'celsius', '13'),

(4, 'slider_nighly_temperature_sink_to', 'hour', '23'), (5, 'slider_nighly_temperature_sink_from', 'hour', '6');

quit