MariaDB installieren

sudo apt update
sudo apt upgrade
sudo apt install mariadb-server

mysql

sudo mysql_secure_installation

neuer User

CREATE USER 'caravanpi'@'localhost' IDENTIFIED BY 'caravanpi';
GRANT ALL PRIVILEGES ON CaravanPiValues.* TO 'caravanpi'@'localhost';
FLUSH PRIVILEGES;

Setzen eines Passworts für den User root bzw für einen anderen User

sudo mysql -uroot -p mysql
SET PASSWORD = PASSWORD('diesisteingeheimespasswort');

SET PASSWORD FOR 'caravanpi'@'localhost' = PASSWORD('diesisteinsehrgeheimespasswort');

Einloggen als User caravanpi in die Datenbank CaravanPiValues

sudo mysql -ucaravanpi -p CaravanPiValues

Nützliche Kommandos zur Administration von Datenbanken und Tabellen

SHOW DATABASES;
use <datenbank_name>;

SHOW TABLES FROM <datenbank_name>;
SHOW TABLES;

SHOW FIELDS FROM <tabellen_name> FROM <datenbank_name>;
SHOW COLUMNS FROM <datenbank_name>.<tabellen_name>;

neue Datenbank für den CaravanPi generieren

sudo mysql -uroot -p
CREATE DATABASE CaravanPiValues;

Erzeugen der Tabellen für den CaravanPi

create table temperaturen (sensor_id VARCHAR(20) NOT NULL, zeitstempel TIMESTAMP NOT NULL, temperatur FLOAT(10,2) NOT NULL);

create table klimawerte (sensor_id VARCHAR(20) NOT NULL, zeitstempel TIMESTAMP NOT NULL, temperatur FLOAT(10,2) NOT NULL, luftdruck FLOAT(10,2) NOT NULL, luftfeuchtigkeit FLOAT(10,2) NOT NULL);

create table gasfuellgrad (sensor_id VARCHAR(20) NOT NULL, zeitstempel TIMESTAMP NOT NULL, gewicht FLOAT(10,0) NOT NULL, fuellgrad FLOAT(10,0) NOT NULL);

create table ausrichtung (zeitstempel TIMESTAMP NOT NULL, aktuell_x FLOAT NOT NULL, aktuell_y FLOAT NOT NULL, aktuell_z FLOAT NOT NULL, toleranz_x FLOAT NOT NULL, toleranz_y FLOAT NOT NULL, letztes_x FLOAT, vorletztes_x FLOAT, differenz_hinten_links INT NOT NULL, differenz_hinten_rechts INT NOT NULL, differenz_vorne_links INT NOT NULL, differenz_vorne_rechts INT NOT NULL, differenz_zentral_links INT NOT NULL, differenz_zentral_rechts INT NOT NULL, differenz_deichsel INT NOT NULL);

create table tankfuellgrad (tank_id VARCHAR(20) NOT NULL, zeitstempel TIMESTAMP NOT NULL, fuellgrad FLOAT(10,0) NOT NULL);

create table spannung (quellen_id VARCHAR(20) NOT NULL, zeitstempel TIMESTAMP NOT NULL, spannung FLOAT(15,3) NOT NULL);




create table defaults_CaravanPi (schreibe_datenbank BOOLEAN NOT NULL, schreibe_dateien BOOLEAN NOT NULL, anzahl_waagen INT UNSIGNED NOT NULL, anzahl_tanks INT UNSIGNED NOT NULL);

create table defaults_ausrichtung (horizontal_x FLOAT(10,2) NOT NULL, horizontal_y FLOAT(10,2) NOT NULL, horizontal_z FLOAT(10,2) NOT NULL, toleranz_x FLOAT(10,2) NOT NULL, toleranz_y FLOAT(10,2) NOT NULL, annaeherung_x FLOAT(10,2) NOT NULL, annaeherung_y FLOAT(10,2) NOT NULL, sensorabstand_nach_rechts FLOAT(10,2) NOT NULL, sensorabstand_nach_vorne FLOAT(10,2) NOT NULL, sensorabstand_zur_achse FLOAT(10,2) NOT NULL);

create table defaults_abmessungen (laenge_ueber_alles FLOAT(10,2) NOT NULL, laenge_aufbau FLOAT(10,2) NOT NULL, breite FLOAT(10,2) NOT NULL);

create table defaults_gaswaagen (sensor_id VARCHAR(20) NOT NULL PRIMARY KEY, leergewicht_gasflasche FLOAT(10,2) NOT NULL, max_gas_fuellgewicht FLOAT(10,2) NOT NULL, gpio_pin_out INT UNSIGNED NOT NULL, gpio_pin_sck INT UNSIGNED NOT NULL, hx711_channel INT UNSIGNED NOT NULL, referenz_wert INT UNSIGNED NOT NULL);

create table defaults_tanks (tank_id VARCHAR(20) NOT NULL PRIMARY KEY, level_1_liter INT UNSIGNED NOT NULL, level_2_liter INT UNSIGNED NOT NULL, level_3_liter INT UNSIGNED NOT NULL, level_4_liter INT UNSIGNED NOT NULL);

create table defaults_spannung (quellen_id VARCHAR(20) NOT NULL PRIMARY KEY, level_1_volt FLOAT(10,2) NOT NULL, level_2_volt FLOAT(10,2) NOT NULL, level_3_volt FLOAT(10,2) NOT NULL);

Ausgeben von Warnings und Errors einschalten

SHOW WARNINGS [LIMIT [offset,] row_count]
SHOW ERRORS [LIMIT row_count OFFSET offset]
SHOW COUNT(*) WARNINGS

Importieren vorhandener Werte aus bisherigen Daten-Dateien Achtung, vorher prüfen, ob keine Leerzeilen drin sind.

Kühlschrank Temperaturen

LOAD DATA LOCAL INFILE '/home/pi/CaravanPi/values/28-012032d4dd5a' INTO TABLE temperaturen FIELDS TERMINATED BY ' ';
LOAD DATA LOCAL INFILE '/home/pi/CaravanPi/values/28-012033251e15' INTO TABLE temperaturen FIELDS TERMINATED BY ' ';
LOAD DATA LOCAL INFILE '/home/pi/CaravanPi/values/28-01203392085f' INTO TABLE temperaturen FIELDS TERMINATED BY ' ';

Klimasensoren

LOAD DATA LOCAL INFILE '/home/pi/CaravanPi/values/BME280-96-118' INTO TABLE klimawerte FIELDS TERMINATED BY ' ';
LOAD DATA LOCAL INFILE '/home/pi/CaravanPi/values/BME280-96-118.1' INTO TABLE klimawerte FIELDS TERMINATED BY ' ';
LOAD DATA LOCAL INFILE '/home/pi/CaravanPi/values/BME280-96-118.2' INTO TABLE klimawerte FIELDS TERMINATED BY ' ';
LOAD DATA LOCAL INFILE '/home/pi/CaravanPi/values/BME280-96-119' INTO TABLE klimawerte FIELDS TERMINATED BY ' ';
LOAD DATA LOCAL INFILE '/home/pi/CaravanPi/values/BME280-96-119.1' INTO TABLE klimawerte FIELDS TERMINATED BY ' ';
LOAD DATA LOCAL INFILE '/home/pi/CaravanPi/values/BME280-96-119.2' INTO TABLE klimawerte FIELDS TERMINATED BY ' ';

Gaswaage

LOAD DATA LOCAL INFILE '/home/pi/CaravanPi/values/gasScale1' INTO TABLE gasfuellgrad FIELDS TERMINATED BY ' ';
LOAD DATA LOCAL INFILE '/home/pi/CaravanPi/values/gasScale1.1' INTO TABLE gasfuellgrad FIELDS TERMINATED BY ' ';
LOAD DATA LOCAL INFILE '/home/pi/CaravanPi/values/gasScale1.2' INTO TABLE gasfuellgrad  FIELDS TERMINATED BY ' ';

Tanks

LOAD DATA LOCAL INFILE '/home/pi/CaravanPi/values/tank1' INTO TABLE tankfuellgrad FIELDS TERMINATED BY ' ';
LOAD DATA LOCAL INFILE '/home/pi/CaravanPi/values/tank1.1' INTO TABLE tankfuellgrad FIELDS TERMINATED BY ' ';
LOAD DATA LOCAL INFILE '/home/pi/CaravanPi/values/tank1.2' INTO TABLE tankfuellgrad   FIELDS TERMINATED BY ' ';