Herrnhuter Losungen in MySQL / MariaDB importieren

Das Hernnhuter Losungen Widget zeigt unter WordPress die Losung des Tages in einer Seitenleiste an. Bei der Anpassung und Überarbeitung für 2019 kam mir die Idee, die Losungen nicht aus der XML Datei einzulesen, sondern in eine MySQL / MariaDB Tabelle zu improtieren. Unter Die Losungen – Gottes Wort für jeden Tag werden die Losungen für das laufende (und ab Spätherbst folgende) Jahr zum Download angeboten.

Beim Import in eine Datenbank gilt es ein paar Hürden zu überwinden. Für den Import unter Linux sind die CSV-Dateien geeigneter als die XML-Dateien.

Schritt 1: Datenbank und Tabelle erstellen

Zuerst brauchen wir eine Datenbank und eine Tabelle, in die wir die Losungen importieren können.

echo "
CREATE DATABASE IF NOT EXISTS hhlosungen;
USE hhlosungen;
CREATE TABLE IF NOT EXISTS losungen (
  Datum date DEFAULT NULL,
  Wtag varchar(16) DEFAULT NULL,
  Sonntag varchar(255) DEFAULT NULL,
  Losungsvers varchar(64) DEFAULT NULL,
  Losungstext varchar(2048) DEFAULT NULL,
  Lehrtextvers varchar(64) DEFAULT NULL,
  Lehrtext varchar(2048) DEFAULT NULL,
  PRIMARY KEY (datum),
  KEY Losungsvers (Losungsvers),
  KEY Lehrtextvers (Lehrtextvers)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ;
" | mysql -u root -p

Schritt 2: Download

Danach holen wir die Losungen aus dem Internet. Folgende Befehle laden das Archiv des laufenden Jahres herunter:

YEAR=$(date %Y)
URL="https://www.losungen.de/fileadmin/media-losungen/download/Losung_${YEAR}_CSV.zip"
wget -O /tmp/${YEAR}.zip $URL

Schritt 3: Entpacken

Nach dem Download muss das ZIP-File „Losung_YYYY_CSV.zip entpackt werden. Wir brauchen nur die CSV-Datei aus dem Archiv:

unzip  -o /tmp/${YEAR}.zip "*.csv" -d /tmp/

Schritt 4: CSV-Datei konvertieren

Die Dateien sind ISO-8859-15 und mit Zeilenende für DOS kodiert, besser wäre UTF-8 und Linux-Zeilenende. Zusätzlich ist des Datumsformat (dd.mm.yyyy) nicht für den Import geeignet. Für den MySQL / MariaDB brauchen wir es im Format yyyy-mm-dd.

Die CSV-Datei passen wir den folgenden Befehlen an:

ICSV="/tmp/Losungen Free ${YEAR}.csv"
OCSV="$(mktemp).csv"
iconv -f ISO-8859-15 "$ICSV" -t UTF-8 \
| tr -d '\r' \
| sed 's#\([0-9]\{2\}\)\.\([0-3][0-9]\)\.\([0-9]\{4\}\)#"\3-\2-\1"#' > $OCSV

Schritt 5: Importieren

Jetzt können wir die Dateien mit folgendem SQL Befehl in die Datenbank importieren.

echo "
USE hhlosungen; 

LOAD DATA INFILE '$OCSV' 
INTO TABLE losungen
FIELDS TERMINATED BY '\t' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
"  | mysql -u root -p

Alles in einem Script

Und hier alles in einem Job zusammengepackt. Der Job lädt – wenn vorhanden – die Losungen des vergangenen, des laufenden und des nächsten Jahres herunter lädt. (Ende 2018 war 2017 noch verfügbar, obwohl in der Web-Seite aufgelistet.)

#!/bin/bash

MYSQL="mysql --user=root --password=$1" # Insert your password here or on the command line; be aware ist will be visible in the history

echo "CREATE DATABASE IF NOT EXISTS hhlosungen;" | $MYSQL 

echo "
USE hhlosungen;
CREATE TABLE IF NOT EXISTS losungen (
  Datum date DEFAULT NULL,
  Wtag varchar(16) DEFAULT NULL,
  Sonntag varchar(255) DEFAULT NULL,
  Losungsvers varchar(64) DEFAULT NULL,
  Losungstext varchar(2048) DEFAULT NULL,
  Lehrtextvers varchar(64) DEFAULT NULL,
  Lehrtext varchar(2048) DEFAULT NULL,
  PRIMARY KEY (datum),
  KEY Losungsvers (Losungsvers),
  KEY Lehrtextvers (Lehrtextvers)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ;
" | $MYSQL

for YEAR in $(date +%Y --date="last year") $(date +%Y) $(date +%Y --date="next year")
do 

URL="https://www.losungen.de/fileadmin/media-losungen/download/Losung_${YEAR}_CSV.zip"

wget -O /tmp/${YEAR}.zip $URL && unzip  -o /tmp/${YEAR}.zip "*.csv" -d /tmp/

if [ "$?" = "0" ]
then
ICSV="/tmp/Losungen Free ${YEAR}.csv"
OCSV="$(mktemp).csv"

iconv -f ISO-8859-15 "$ICSV" -t UTF-8 | tr -d '\r' | sed 's#\([0-9]\{2\}\)\.\([0-3][0-9]\)\.\([0-9]\{4\}\)#"\3-\2-\1"#' > $OCSV

# We must allow read access for MySQL / Marias DB

chmod 0744 $OCSV

echo "
USE hhlosungen; 

LOAD DATA INFILE '$OCSV' 
INTO TABLE losungen
FIELDS TERMINATED BY '\t' 
ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
" | $MYSQL
fi

done
Schlagwörter