Offline Sprachassistent mit Python

Ich habe schon immer mit einem Sprachassistenten geliebäugelt. Der teils vorhandene onlinezwang sowie die Erforderlichkeit den Sprachassistenten mit google, amazon, etc. zu verbinden, haben mich davor abgehalten einen zu ordern. Jetzt habe ich mir einen eigenen offline Sprachassistent mit Python erstellt. Als Spracherkennungsbibliothek verwende ich vosk. Vosk unterstützt mehr als 20 Sprachen, darunter auch deutsch und englisch. Die Spracherkennung funktioniert offline und sogar auf lightweight devices wie den Raspberry Pi.

Zur Einrichtung benötigen wir ein auf deutsch trainiertes Model. Von Vosk werden welche bereitgestellt, ihr könnt aber auch eigene Modelle erstellen.

Installation und Einrichtung

Die Installation von Vosk erledigen wir mit dem pip Manager. Für die Integration unseres Mikrophones ist sounddevice erforderlich. Zum Testen unserer Geräte installieren wir die alsa-utils, die eine Palette an Programme bereitstellt, unteranderem arecord und aplay.

# Abhängigkeiten installieren
sudo apt install python3-pyaudio alsa-utils libgfortran3

pip3 install vosk
pip3 install sounddevice

Wir laden uns zuerst das github von vosk herunter. Dort sind ebenfalls einige Beispiel Skripte enthalten. Geht in das Beispiel Verzeichnis und ladet ein Model eurer Wahl, um so vosk die Fähigkeit zu geben, unser Gesprochenes in Text umzuwandeln. Wichtig ist, dass das geladene und entpackte Model-Verzeichnis nach model umbenannt wird. Alle verfügbaren Models findet ihr hier: https://alphacephei.com/vosk/models

git clone https://github.com/alphacep/vosk-api

cd vosk-api/python/example
wget https://alphacephei.com/vosk/models/vosk-model-small-de-0.15.zip
unzip vosk-model-de-0.21.zip
mv vosk-model-de-0.21/ model

Falls ihr noch kein Benutzer der Gruppe audio seid, fügt euch bitte hinzu. Ansonsten fehlen euch unter umständen die Berechtigungen, Aufnahme- und Ausgabegeräte zu benutzen.

development:~# grep audio /etc/group
audio:x:29:<user>

sudo reboot

Sounddevice/Mikrofon testen

Als Sounddevice kommt ein USB Mikrofon zum Einsatz. Bei Amazon habe ich mir das Tyasoleil USB Mikrofon gekauft, und muss sagen, dass ich mehr als beeindruckt bin. Es kann mich im kompletten Raum aufnehmen. Ich habe es hier verlinkt: Mikrofone*. Mit arecord können wir die verfügbaren Aufnahmegeräte auflisten.

arecord -l
**** List of CAPTURE Hardware Devices ****
card 0: I82801AAICH [Intel 82801AA-ICH], device 0: Intel ICH [Intel 82801AA-ICH]
  Subdevices: 1/1
  Subdevice #0: subdevice #0
card 0: I82801AAICH [Intel 82801AA-ICH], device 1: Intel ICH - MIC ADC [Intel 82801AA-ICH - MIC ADC]
  Subdevices: 1/1
  Subdevice #0: subdevice #0

Ebenfalls bietet es uns die Möglichkeit eine Aufnahme zu starten und später als .wav Datei zu speichern.

arecord -f S16_LE -d 10 -r 16000 ./test-mic.wav
Recording WAVE './test-mic.wav' : Signed 16 bit Little Endian, Rate 16000 Hz, Mono

Mit aplay können wir unsere gerade erstellte .wav Datei anhören und so die Tests des Mikrofons abschließen.

aplay test-mic.wav
Playing WAVE 'test-mic.wav' : Signed 16 bit Little Endian, Rate 16000 Hz, Mono

Python3 Code

Zuerst laden wir die benötigten Python Bibliotheken. und erstellen eine Queue. Queue ist eine Art lineare Datenstruktur und arbeitet nach dem FIFO Prinzip. FIFO stammt aus dem englischen und bedeutet First-In-First-Out. Kurz gesagt, was zuerst reinkommt, geht auch als erstes wieder raus. Wir haben einen zusätzlichen Thread, der die Aktivphase managed und für 10 Sekunden offen hält. Dazu aber später mehr.

import argparse
import os
import queue
import sys
import json
import sounddevice as sd
import vosk
import threading
import time
import gpiozero

q = queue.Queue()

Activities Klasse

In der Activities Klasse sind alle Aktivitäten aufgelistet, die wir benutzen können. Zurzeit beschränken wir uns auf Licht an/aus und Tor an/aus. In beiden Fällen triggern wir eine LED, die mit unterschiedlichen GPIO gesteuert wird.

Speech Klasse

Die Speech Klasse ist die Hauptklasse. Hier wird der Startcode definiert, auf dem unsere Sprachsteuerung reagiert und nach dem wir unsere Kommandos sagen können. Der Startcode ist ähnlich wie “alexa” bei Amazon. Wurde der Startcode herausgehört, haben wir ein 10 Sekunden Fenster für die Kommandos, ehe wir unseren Startcode erneut sagen müssen. Während unser Sprachfenster offen ist, leuchtet zusätzlich noch eine grüne LED. Die 10 Sekunden Zählschleife, unsere Aktivphase, ist in einem Thread gepackt und läuft im Hintergrund. Ebenso wie das an und ausschalten der grünen LED. Das hat den Vorteil, dass unser Programm weiterhin auf das Gesprochene reagieren kann, während es die Zeit herunterzählt. Solange unser Thread läuft, können wir die Activities Methoden mit unserer Sprache steuern.

class Activities:
# Definiere die Aktivitäten, die mit deiner Sprache gesteuert werden können.

    LICHT_LED = LED_TOR = None
    
    def licht(GPIO):
        # Schalte das Licht an und aus
        print(f" Schalte das Licht an/aus mit {GPIO}")
        if Activities.LICHT_LED is None:
            Activities.LICHT_LED = gpiozero.LED(GPIO)
        try:
            Activities.LICHT_LED.toggle()
            
        # if any error occurs call exception
        except gpiozero.GPIOZeroError as err:
            print("Error occured: {0}".format(err))
    
    def tor(GPIO):
        # zur Demonstrationszwecken wird hier nun eine Ausgabe definiert. 
        print(f" Schalte das Tor an mit {GPIO}")


class speech: 
    
    STARTCODE = 'computer'
    def __init__(self,startcode):
        self.STARTCODE = startcode

    # Unsere Thread Funktion
    def thread_timer(self):
        # Aktiviere GPIO 17, um die grüne LED zum Leuchten zu bringen. 
        led = gpiozero.LED(17)
        self.power_gpio(17,led)
        # warte 10 Sekunden
        time.sleep(10)
        # Schalte die grüne LED wieder aus.
        self.close_gpio(17,led)
        
    # Definieren der Aktivierungsphase. Solange der thread gestartet ist, können Kommandos zum triggern der Methoden aus der Activities Klasse gesagt werden.
    # 
    def active(self,rec):
        print("active")
        # Thread definieren
        t = threading.Thread(target=self.thread_timer)
        # Thread starten
        t.start()
        i=0
        # solange Thread aktiv
        while t.is_alive():
            print('call a command')
            # hole die Daten aus der Queue, bzw. aus dem Stream
            data = q.get()
            if rec.AcceptWaveform(data):
                print("second record")
                # 
                res = json.loads(rec.Result())
                if 'LICHT'.upper() in res['text'].upper():
                    Activities.licht(18)

                elif 'Tor'.upper() in res['text'].upper():
                    Activities.tor(18)
                print(res['text'])


    def power_gpio(self,GPIO,led):
        print(f"Power {GPIO}")
        try:
            # switch LED on
            if not led.is_lit:
                led.on()
        # if any error occurs call exception
        except gpiozero.GPIOZeroError as err:
            print("Error occured: {0}".format(err))
    
    def close_gpio(self,GPIO,led):
        print(f"Close {GPIO}")
        try:
            # switch LED off
            if led.is_lit:
                led.off()
        # if any error occurs call exception
        except gpiozero.GPIOZeroError as err:
            print("Error occured: {0}".format(err))

Main

In der Main parsen wir die Argumente, die wir dem Programm übergeben können. Falls keine angegeben worden sind, übernimmt das Script definierte Standardwerte. Wir überprüfen, ob der Ordner model existiert und erstellen ein Objekt der Klasse speech mit unserem definierten Aktivierungswort. Unsere Sprache muss natürlich noch aufgezeichnet werden. Dies machen wir mit der Methode RawInputStream der sounddevice Klasse. Wichtig hier ist vor allem der callback Parameter vom Typ “callable”. Diesem geben wir unsere gleichnamige Funktion über, die folgenden Aufbau benötigt:

callback(indata: buffer, frames: int,
         time: CData, status: CallbackFlags) -> None

In dieser Funktion geben wir unserem Sprach-Stream, den wir zuvor mit RawInputStream eingefangen haben, als Bytes in die am Anfang definierte Queue. In unserer while Schleife entnehmen wir unserer Queue die Daten und übergeben diese der Methode AcceptWaveForm. Die gerade genannte Methode versucht die gesprochenen Sätze zu erkennen. Erst wenn es das Ende vermutet, gibt AcceptWaveForm True zurück. In rec.Result() befindet sich das Ergebnis des gesprochenen Textes. Wird erhalten ein String, den wir mit json.loads als JSON parsen und weiterverarbeiten können. Wird da Aktivierungswort herausgehört, geht es mit dem gleichen Vorgehen im Aktivierungsfenster weiter.

def callback(indata, frames, time, status):
    """This is called (from a separate thread) for each audio block."""
    if status:
        print(status, file=sys.stderr)
        pass
    q.put(bytes(indata))

if __name__ == '__main__':
    parser = argparse.ArgumentParser(add_help=True)
    parser.add_argument(
        '-m','--model', type=str, nargs='?',default='model', help='Pfad zum Model'
    )
    parser.add_argument(
        '-d','--device', type=str,nargs='?',default='1,0',help='Eingabegerät(Mikrofon als String)'
    )
    parser.add_argument(
        '-r','--samplerate',type=int,nargs='?', default=44100,help='Sample Rate'
    )

    args = parser.parse_args('')

    if not os.path.exists(args.model):
        print("Please download a model from https://alphacephei.com/vosk/models and unpack to 'model'")
        #exit(1)

    model = vosk.Model(args.model)
    # Speech Objekt erstellen und Übergabe des Aktivierungsworts
    speech = speech('computer')
    # 
    with sd.RawInputStream(samplerate=args.samplerate, blocksize=8000, device=None,dtype='int16',
                            channels=1, callback=callback):
        print('*' * 80)
        # Aktivierung der vosk Spracherkennung mit Übergabe des geladenen Models. Übersetze das Gesprochene in Text.
        rec = vosk.KaldiRecognizer(model, args.samplerate)
        while True:
            # Daten aus der Queue ziehen
            data = q.get()
            print("start to speak")
            if rec.AcceptWaveform(data):
                # erhalte das erkannte gesprochene als String zurück
                x = rec.Result()
                print(x)
                print(rec.Result())
                # wandelt den String in Json um
                res = json.loads(x)
                print(res)
                # wenn der Aktivierungscode herausgehört wurde, wird die active Methode von Speech gestartet
                if speech.STARTCODE == res['text']:
                    speech.active(rec)

            else:
                pass

Aufbau der Schaltung

Vom Prinzip her sind es zwei die gleichen Schaltungen. Nur der benutzte GPIO Pin ist ein anderer. Beide LEDs sind mit einem 330 Ohm Wiederstand geschaltet. Der Rest kann in der Schaltung begutachtet werden.

Den kompletten Code findet ihr bei github.

Update: Ich habe den Sprachassistenten nun mit einem LED-Streifen verbunden. Den Beitrag findet ihr hier: LED-Streifen mit offline Sprachassistenten steuern

Dash callbacks

Wer mit Dash arbeiten möchte, wird nicht um das Thema Dash callbacks drum rum kommen. Callbacks benutzen wir, um mit dem Dashboard zu interagieren, somit erhalten wir neue Visualisierungsmöglichkeiten. Die Installation von Dash ist in meinem Beitrag Plotly Dash installieren und einrichten beschrieben.

Abhängigkeiten laden

Um Dash lauffähig zu machen, müsst ihr sogenannte Komponenten laden. Dies sind unter anderem 

  • dash_core_components – stellt uns unter anderem Dropdowns, Sliders, Checkboxen, etc. zur Verfügung. Mit diesem Komponenten können wir mit Dash interargieren.
  • dash_html_components – bietet eine Palette an HTML Komponenten wie Div, Img, Table, etc. an. Diese Komponenten sind, wie der Name schon sagt, mit HTML gleichzusetzen.
  • Dash oder JupyterDash – Standardmäßig wird Dash über den Browser aufgerufen. Mit der Dash Komponente instanzieren wir Dash und starten die App. JupyterDash macht es der Dash Komponente gleich. Der einzige Unterschied ist, dass wir Dash mit Jupyter starteten und betrachtet können.
  • dash.dependencies – der Hauptbestandteil für die Callbacks. Output und Input sind unerlässlich für Dash callbacks.

from jupyter_dash import JupyterDash
import dash_core_components as dcc
import dash_html_components as html
from dash.dependencies import Output,Input

Dash Instanz erstellen und das Layout erzeugen

Die Instanz erzeugen wir ganz einfach mit app = JupyterDash(__name__).

Für das Layout verwenden wir dash_html_components um, wie vom Namen schon abzuleiten ist, HTML Elemente einzufügen. All den HTML Elementen können Parameter übergeben werden. Fast alle haben z.B. den ID Parameter. Weitere sind nachzulesen unter https://dash.plotly.com/dash-html-components

# app instanzieren
app = JupyterDash(__name__)

# app Layout erstellen
app.layout = html.Div(id="container",children=[
    html.H3('Dropdown'),
    dcc.Dropdown(
        id='city_dropdown',
        options=[{'label':city,'value':city} for city in['Berlin','München','Dresden','Hamburg']]        
    ),
    html.Div(id='text_output'),
    html.Br(),
    html.Hr(),
    html.H3('Slider'),
    dcc.Slider(
        id="slider",
        min=0,
        max=10,
        value=3
        ),
    html.Div(id='slider_output')
])

Zur Veranschaulichung präsentiere ich hier den groben HTML DOM. Dieser ist stark vereinfacht, der Aufbau sollte aber ähnlich sein.

<div id='container'>
   <h3>Dropdown</h3>
      <select name="city_dropdown" id="city_dropdown"> 
         <option value="Berlin">Berlin</option>
         ...
   <div id="text_output"></div>
   <br>
   <hr>
   <h3>Slider</h3>
   <div id=slider">Slider Definierung</div>
   <div id="slider_output"></div>
</div>

Python Dekorator

Kurz, ein Dekorator ist ein aufrufbares Python Objekt. Der Dekorator nimmt die zu dekorierende Funktion als Parameter an. @app.callback ist der Dekorator bei Dash. Dieser wird üblicherweise vor der Funktion geschrieben, die nachträglich ausgeführt werden soll.

Mit Output definieren wir, wo die Ausgabe der Funktion landen soll. In diesem Fall wäre es das <Div> mit der ID = text_output. Da wir hier kein weiteres Attribut haben, wählen wir children als Ausgabeattribut.

Beim Input nehmen wir vom Dropdown Namens “city_drowdown” das Value Attribut, um unseren Wert auszulesen. Die Anzahl der definierten Inputs ist auch gleichzeitig die erwartete Anzahl der Parameter der darunterliegenden Funktion. Wird nun ein Wert aus der Dropdownliste ausgewählt, z.B. Berlin, so wird dieser Wert der Funktion “City_Namen_anzeigen” übergeben. Innerhalb der Funktion ist der Wert in der Variable “city” gespeichert. Die Funktion validiert, ob city evtl. von Typ None ist. Sollte dies der Fall sein, geben wir “Keine Stadt ausgewählt” zurück, andernfalls ist es der Wert innerhalb der Variable city. In unserem Fall also Berlin, was an den Output geht. Um es nun abzuschließen, steht “Du hast Berlin” in unserem <Div> mit der ID = text_output.

@app.callback(
    Output('text_output','children'),
    Input('city_dropdown','value')
)
def City_Namen_anzeigen(city):
    if city is None:
        return 'Keine Stadt ausgewählt'
    return 'Du hast {0} gewählt'.format(city)

@app.callback(
    Output('slider_output','children'),
    Input('slider','value')
)
def Slider_Nummer_anzeigen(num):
    if num is None:
        return 'keine Nummer ausgewählt'
    return 'Du hast {0} gewählt'.format(num)

Mit all den getätigten Codesnippets können wir nun unsere App starten lassen. Dafür ist app.run_server zuständig. Der Methode können wir mehrere Parameter übergeben. Üblicherweise erlaubt Plotly nur Zugriffe vom Localhost, dies könnte z.B. mit host angepasst werden.

if __name__ == '__main__':
    app.run_server(host='192.168.188.80', mode='inline',port='8051')

Wer interesse an dem Notebook hat, kann es sich hier downloaden.

Kali Linux 2021.3 wurde veröffentlicht

Am 14.09 wurde Kali Linux 2021.3(Quartal 3) veröffentlicht. Diese Version hat ein paar Neuerungen gegenüber der 2021.2.

Änderungen seid 2021.2:

  • OpenSSL – Größere Kompatibilität per default
  • Neue Kali-Tools Seite
  • Verbesserten VM Support im Live Image
  • Neue Tools
  • Kali Nethunter smartwatch
  • KDE 5.21

OpenSSL mit größerer Kompatibilität

Die veralteten Protokolle TLS 1.0 und TLS 1.1, sowie ältere Verschlüsselungsverfahren, sind nun per Default aktiv. Dies soll Kali Linux helfen mit älteren, obsoleten System zu sprechen, sowie mit Servern, die die alten Protokolle noch immer verwenden. Somit ist der Angriffsfläche größer.

Kali Tools

Kali Linux hat die eigene Dokumentation bearbeitet. Nicht nur ein neues Layout hat die Doku erfahren, die Seite soll auch schneller sein. Ihr erreicht die neue Doku Seite über folgenden Link: https://www.kali.org/tools/

Virtualisierung: Verbesserungen im ganzen Bereich

Das Kali Live Image wurde ebenfalls bearbeitet. All diejenigen, die das Live Image in einer virtuellen Umgebung laufen lassen, sollen ein besseres Erlebnis haben. Es soll vor allem reibungsloser laufen. Copy & Paste sowie Drag & Drop zwischen dem Host und dem Gast soll out of the box funktionieren. Diese Veränderung soll für alle gängigen Virtualisierungssoftware gelten, wie VMware, VirtualBox, Hyper-V und QEMU+Spice.

Neue Tools in Kali

In dem neuen Release dürfen auch keine neuen Hacker Tools fehlen:

  • Berate_ap – Organisiere MANA rogue Wi-Fi Access Points
  • CALDERA – Skalierbarer automatische adversary emulation Platform (Tests laufen von einem System aus)
  • EAPHammer – Evil Twin Attacke gegen WPA2-Enterprise Wi-Fi Netzwerke
  • HostHunter – Aufklärungstool zum finden von Hostname mittels der OSINT Technik
  • RouterKeygenPC – generiert Standard WPA/WEP Wi-Fi Keys
  • Subjack – übernahme von Subdomänen
  • WPA_Sycophant – Übermitteln von Phase 2 Authentifizierungsversuche um auf das Unternehmens Wi-Fi zuzugreifen ohne das Password zu cracken

Kali Nethunter Smartwatch

Ganz neu ist die erste Kali NetHunter smartwatch, die TicHunter Pro. Die Uhr befindet sich weiterhin im Experimental Status, aber kann bisher beschränkte Angriffe auf USB und einige Basis Angriffe ausführen. Es gibt ebenfalls ein paar Limitierungen an der Hardware. Die Uhr darf keine kleine Batterie besitzen, da sie sonst nicht genug Spannung für den OTG Adapter liefert. In Zukunft soll Nexmon unterstützt werden, als auch der interne Bluetooth Gebrauch.

https://www.kali.org/blog/kali-linux-2021-3-release/images/NHWheader.png

Desktop und Themen Update

Die Desktopumgebung hat sich ebenfalls leicht geändert:

  • Verbesserte GTK3 Themen für Xfce’s Benachrichtigungen und dem Logout-Dialog.
  • Erneuerte GTK2 Themen für bessere Eignung von alten Programmen
  • Verbesserte Kali-Dark und Kali-Light Syntax-Hervorhebungsthemen für GNOME und Xfce.

Neben den gerade genannten Verbesserungen hat die bevorzugte Desktopumgebung, KDE plasma, eine neue Version erhalten. Die Version 5.21 bringt einen geupdateten Look, sowie einen neuen Application Launcher, sowie Themen Verbesserungen. Hier seht ihr den neue Oberfläche mit den Kali Einstellungen.

https://www.kali.org/blog/kali-linux-2021-3-release/images/KDE_5.21.png
# Quelle: https://www.kali.org/blog/kali-linux-2021-3-release/

Kontostand Dashboard

Wäre es nicht interessant den eigenen Kontostand zu beobachten? Somit zu erfahren, ob wirklich Geld im Monat übrig bleibt oder zu viel ausgegeben wurde? Diese Idee kam mir vor einiger Zeit. In der Banking-App sehe ich nur den Verlauf meiner Ausgaben und Einnahmen. Allerdings nicht, ob am Ende eines Monats Geld übrig geblieben ist. Erschwerend kommt noch hinzu, dass ich viel mit dem Geld jongliere und auf mehrere Konten verteile. Das führt dazu, dass die grafischen Anzeigen der Bank wirres Zeug anzeigen. Um diesen Problem Herr zu werden, habe ich es mir zur Aufgabe gemacht, jeden Monat zum 1. meine Salden abzufragen. Die Ergebnisse tragen ich dann in eine Datenbank-Tabelle ein.

Da das eigene Einkommen auch mit dazu beiträgt, habe ich es ebenfalls mit ins Projekt reingenommen.

Datenbank Design

In der Datenbank, ich nenne sie Balance, gibt es insgesamt 6 Tabellen, mehrere Unique Key, Primary Key, Foreign Keys und Triggers. In BAL_BANKACCOUNT, BAL_COMPANY, BAL_LOCATION, BAL_INSTITUTION werden Stammdaten eingetragen und dienen als Fremdschlüssel für andere Tabellen. BAL_INCOME und BAL_BALANCE enthalten die gewünschten Informationen, die später ausgelesen werden.

Eigentlich wollte ich z.B. in BAL_INCOME einen Primärschlüssel aus COMPANY_ID, MONTH und YEAR, um so Doppeleinträge zu verhindern. Leider sorgt Django zu einer Limitierung und ich habe ID als primären Schlüssel definieren müssen. Von Django möchte ich das Admin-Interface benutzen, wie ich es auch schon zuvor getan habe. Django erfordert genau einen Primärschlüssel und ein Verbund aus Primärschlüsseln sorgt für Fehler, daher ist ID der alleinige Primärschlüssel und MONTH, YEAR und BNR sind als unique deklariert worden.

BAL_BANKACCOUNTAlle Bank Konten werden hier eingetragen
BAL_COMPANYAlle Firmen werden hier eingetragen
BAL_LOCATIONAlle Orte werden hier eingetragen
BAL_INSTITUTIONAlle Bank Institutionen werden hier eingetragen
BAL_INCOMEDas Einkommen wird hier eingetragen
BAL_BALANCEDie Salden werden hier eingetragen

Eine Sache muss hier aber noch erwähnt werden und das ist die Prozedur. Würde ich jetzt eine Result von BAL_BALANCE zurückbekommen, so wären die Bankkonten im Result aufzufinden.

IDMONTHYEARBNRBALANCEA.._BALANCEU.._BALANCE
112021Konto1300030000
212021Konto220000150005000
312021Konto310000100000
412021Konto450000500000
522021Konto1350035000

Zur Weiterverarbeitung mit Pandas wollte ich diese allerdings als Spaltennamen einsetzen. Zusätzlich wollte ich es vermeiden, immer und immer wieder neue Bankkonten als neue Spalte hinzufügen zu müssen.

CREATE DEFINER=`username`@`%` PROCEDURE `P_BAL_BALANCE`(IN START int, IN END int)
BEGIN
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'ifnull(SUM(case when BNR = ''',
      BNR,
      ''' then BALANCE end),0) AS `',
      BNR, '`'
    )
  ) INTO @sql
FROM
  Balance.BAL_BALANCE ;
SET @sql = CONCAT('SELECT YEAR,MONTH, ', @sql, ' 
                  FROM Balance.BAL_BALANCE where YEAR >= ', START ,' and YEAR <= ', END ,'
GROUP BY YEAR,MONTH');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

Die Prozedur nimmt sich die BNRs und verwendet diese als Spaltennamen und erwartet zusätzlich noch ein Start- sowie Endjahr, von dem es die Results bereitstellen soll. Der Aufruf ist dahingehend einfach:

call Balance.P_BAL_BALANCE(2019, 2021);
MONTHYEARKONTO1KONTO2KONTO3KONTO4
120213000200001000050000
220213500

Noch zu erwähnen wäre der Trigger in BAL_INCOME, denn der berechnet die gesetzlichen Abgaben anhand der Daten, die eingegeben worden sind.

CREATE DEFINER=`username`@`%` TRIGGER `Balance`.`BAL_INCOME_BEFORE_INSERT` BEFORE INSERT ON `BAL_INCOME` FOR EACH ROW
BEGIN
set NEW.STATUTORY_LEVIES = NEW.INCOME - NEW.NET_INCOME;
END

Kontostand in Python Dash

Die Erstellung des Backends ist somit erledigt. Den Kontostand und somit die Visualisierung stelle ich mit Plotly/Dash grafisch her. Es würde den Rahmen sprengen den kompletten Code hier zu veröffentlichen, daher habe ich ihn auf github hochgeladen. Wer daran interessiert ist, findet ihn hier: https://github.com/stevieWoW/balance.

Shell Start Script mit automatischen Neustart

Falls ihr auf der Suche nach einem Shell Start Script seit, habt ihr es hier gefunden. Für das OpenSource Datawarehouse Metabase habe ich mir ein Script geschrieben, welches den Prozess startet und überwacht. Systemd als Service Manager kommt dabei ebenfalls zum Einsatz und sorgt dafür, dass das Script beim Starten des Servers ausgeführt wird. Sollte Metabase nicht mehr Laufen oder der Prozess sich beendet haben, startet das Script es einfach erneut und protokolliert dies in der Log. Die Grundbasis kann dabei für jegliche Prozesse benutzt werden, die automatisch neu gestartet werden sollen.

Shell Start Script

Vor einiger Zeit hatte ich schon etwas ähnliches für Metabase geschrieben. Den alten Artikel könnt ihr hier finden. Auch wenn es mir damals sehr geholfen hat, bin ich diesmal einen gänzlich anderen Weg gegangen. Statt des manuellen Ausführens, folgt dies nun über Systemd, wo ich weiter unten mehr zu schreibe. Wir deklarieren zwei Variablen name und waittime. Name ist der eindeutige Prozessname, um ihn in der Prozessliste mit ps ausfindig machen zu können. Die waittime indes, besagt nur das er sich am Ende kurz für 5 Sekunden schlafen legt, bevor er wieder von vorne anfängt.


Der wichtigste Part ist in der while true Schleife. In process schreiben wir die Ausgabe von ps, falls ein Prozess vorhanden ist. In diesem Fall würde rc = 0 bzw. true sein. Ist die Suche erfolglos haben wir ein rc = 1 bzw. false. Dies ist wichtig für die darauffolgende If-Abfrage, die wir negiert haben. Sollte also kein Prozess vorhanden sein, gehen wir in den If Block, andernfalls wären wir schon am Ende angekommen und würden 5 Sekunden warten. Im Block deklarieren wir die MB_DB_CONNECTION_URI, die für Metabase benötigt wird. Danach folgt der eigentliche Start über java und eine weitere while Schleife. Diese verweilt solange, bis der Server auf Port 3000 lauscht, also der Port, den Metabase benutzt. Somit stelle ich sicher, dass Metabase gestartet ist und der Prozess auffindbar ist. Wir wären auch hier nun am Ende angekommen. Wie vorher warten wir ebenfalls 5 Sekunden, bevor alles erneut anfängt. Diesmal greift der If-Block allerdings nicht, da der Prozess nun gestartet ist.

#!/bin/bash

name="java -jar /var/www/html/Metabase/metabase.jar"
waittime=5

if [ ! -d "/var/log/Metabase" ]
then
                mkdir "/var/log/Metabase"
fi

while true
do
        process=`ps aux | grep "${name}" | grep -v grep`
        rc=$?

        if [ ! $rc -eq 0 ]
        then
                echo "`date +%Y-%m-%d\ %T` INFO `basename $0` :: starting Metabase..." >> /var/log/Metabase/metabase`date +%V`.log
                export MB_DB_CONNECTION_URI="mysql://<ip>:<port>/<database>?user=<user>&password=<password>&serverRsaPublicKeyFile=/var/lib/mysql/public_key.pem"
             
                java -jar /var/www/html/Metabase/metabase.jar >> /var/log/Metabase/metabase`date +%V`.log &
                while [ ! `netstat -tapen | grep LISTEN | grep 3000 | awk '{print $9}'` ]
                do
                        sleep 1
                done
                echo "`date +%Y-%m-%d\ %T` INFO `basename $0` :: Metabase was started" >> /var/log/Metabase/metabase`date +%V`.log
        fi
        sleep ${waittime}
done

Systemd einrichten

Systemd ist ein Hintergrundprogramm in Linuxsysteme. Es ist ein Init-Prozess, somit der erste Prozess(Prozess-ID 1) und sorgt für das Starten, Beenden und Überwachen weiterer Prozesse. Weitestgehend ist systemd zum Standard in Linux-Distributionen geworden und so verwende ich es auch hier. Für Metabase richten wir in systemd eine neue Unit ein. Dies machen wir, indem wir eine neue Datei Namens metabase.service in /etc/systemd/system anlegen. Mit dem folgenden Codeblock wird unser Script nach der Netzwerkinitialisierung automatisch gestartet.

[Unit]
Description=Metabase
Documentation=https://www.metabase.com
After=network.target

[Service]
Type=simple
ExecStart=/var/www/html/Metabase/run_metabase.sh

[Install]
WantedBy=multi-user.target
Alias=metabase.service

Falls nicht schon automatisch passiert, müssen wir unsere neuen Dienst aktivieren

sudo systemctl enable metabase.service 

Das Script läuft jetzt in Dauerschleife durch. Wie bei allen üblichen systemd-Prozessen können wir unseren erstellen Dienst mit systemctl starten.

sudo systemctl start metabase.service

Aber auch stoppen und Überwachen lässt sich der Dienst über systemctl.

#stoppen
sudo systemctl stop metabase.service 

#überwachen
sudo systemctl status metabase.service 

Wir wären somit am Ende angelangt. Sollte Metabase nun nicht mehr laufen, wird es automatisch direkt neu gestartet. Dieser Vorgang ist selbstverständlich auch auf andere Dienste übertragbar und nicht nur an Metabase gekoppelt.

DVWA mit Docker

Es ist zwar schon eine Weile her, wo ich über DVWA(Damn Vulnerable Web Application) geschrieben habe, aber ich möchte die Installation von DVWA mit Docker nicht vorenthalten. Im damaligen Artikel hatte ich die Installation mit dem Package-Manager, bzw. github beschrieben. Dort hatte ich die Datenbank und weiteres händisch aufbereitet. Einfacher ist somit also die Installation über Docker. Naheliegend, dass Docker vorerst installiert ist, danach ist die Integration ein einfaches und kann mit einer Zeile Code installiert werden.

Docker Installation

Solltet ihr vorhaben, einen neuen Server zu installieren, Docker ist bei der Ubuntu-server Installation als Package bei der Installation auswählbar. Habt ihr schon einen Server und wollt Docker nachträglich integrieren, so könnt ihr den Installationshergang auf der Dockerseite finden. Die Ubuntu Installation über das Repository beschreibe ich hier weiter. Für die Installation benötigen wir vorerst das Repository von Docker. Dies kann wie folgt eingebunden werden.

sudo apt update
sudo apt install apt-transport-https ca-certificates curl gnupg lsb-release

Nach der Installation können wir den offiziellen Docker GPG Key einfügen.

curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg

Benutzte den Nachfolgende Codeblock, um das stable Repository hinzuzufügen.

echo "deb [arch=amd64 signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/ubuntu \
  $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null

Sobald das Repository hinzugefügt ist, können wir die Docker-engine installieren

 sudo apt-get update
 sudo apt-get install docker-ce docker-ce-cli containerd.io

Ihr könnt nun überprüfen, ob Docker korrekt installiert ist, indem ihr das hello-world image ausführt

sudo docker run hello-world

Folgende Ausgabe solltet ihr dabei am Ende erhalten

Unable to find image 'hello-world:latest' locally
latest: Pulling from library/hello-world
b8dfde127a29: Pull complete
Digest: sha256:9f6ad537c5132bcce57f7a0a20e317228d382c3cd61edae14650eec68b2b345c
Status: Downloaded newer image for hello-world:latest

Hello from Docker!
This message shows that your installation appears to be working correctly.

To generate this message, Docker took the following steps:
 1. The Docker client contacted the Docker daemon.
 2. The Docker daemon pulled the "hello-world" image from the Docker Hub.
    (amd64)
 3. The Docker daemon created a new container from that image which runs the
    executable that produces the output you are currently reading.
 4. The Docker daemon streamed that output to the Docker client, which sent it
    to your terminal.

To try something more ambitious, you can run an Ubuntu container with:
 $ docker run -it ubuntu bash

Share images, automate workflows, and more with a free Docker ID:
 https://hub.docker.com/

For more examples and ideas, visit:
 https://docs.docker.com/get-started/

DVWA mit Docker installieren

Wie schon oben beschrieben, ist die DVWA Installation danach nur noch ein Einzeiler.

docker run --rm -it -p 80:80 vulnerables/web-dvwa

Sollte bei euch also noch nicht der Container “vulnerables/web-dvwa” existieren, so lädt Docker diesen herunter und startet ihn. Dadurch, dass Docker in seiner eigenen Infrastruktur auf dem Server läuft und eine eigene IP inne hält, geben wir an, dass der Zugriff auf den Server auf Port 80 weiter zum Container auf Port 80 geleitet wird. Mit -rm entfernen wir den Container, falls dieser schon existiert.

Wir können nun auf DVWA zugreifen. Dafür gebt einfach die IP-Adresse eures Servers im Browser ein. Ihr solltet zur Anmeldeseite kommen. Username und Passwort sind in dem Fall admin:password. Von der Anmeldung gefolgt, kommt die Datenbank und das Setup. Ihr könnt vermeintliche Fehler fixen, ist aber nicht zwingend erforderlich. In dem Fall wären einige Funktionen in DVWA nicht gegeben.

DVWA Docker Installation

Nachdem ihr auf “Create/Reset Database” geklickt habt, wird die Einrichtung vorgenommen und ihr könnt euch erneut mit admin:password anmelden. Dieses mal werdet ihr auf die Mainpage von DVWA weitergeleitet.

Hier könnt ihr Einstellungen vornehmen und die Schwierigkeit hochschrauben. Von Brutforce bis hin zu XSS ist hier alles vertreten und ihr könnt eure Skills ausgiebig testen.

Plotly Dash installieren und einrichten

Plotly Dash ist ein Python Framework, um analytische Web-Applikationen zu erstellen. Aufgebaut auf Flask, plotly.js und react.js, können Daten visualisiert werden. Es bietet dabei Methoden, um einfache User-Interfaces zu erstellen, alles basierend auf Python. Plotly ist mehr und mehr in kommen und bietet mittlerweile eine große Community. Auch hier in Deutschland wird es immer populärer. T-Systems selbst ist mittlerweile mit plotly gepartnert. Die Applikationen werden in einem Web-Browser gerendert und sind somit direkt geeignet für mehrere Plattformen und natürlich auch mobilen Geräten. 

Plotly Dash Installation

Um Dash verwenden können, installieren wir die Anforderungen. Damit die Pakete nicht mit anderen in die Quere kommen, machen wir das ganze in einer virtuelle Umgebung.

python3 -m venv plotly
cd plotly
source bin/activate

Zusätzlich installieren wir folgende Pakete über den Package-Manager pip3.

pip3 install dash
pip3 install dash-auth
pip3 install dash_daq

Das wärs schon bezüglich der Installation. Ich möchte euch hier jetzt ein Beispiel aus der Dash Dokumentation zeigen.

import dash
import dash_core_components as dcc
import dash_html_components as html
from dash.dependencies import Input, Output
import plotly.express as px

import pandas as pd

df = pd.read_csv('https://raw.githubusercontent.com/plotly/datasets/master/gapminderDataFiveYear.csv')

external_stylesheets = ['https://codepen.io/chriddyp/pen/bWLwgP.css']

app = dash.Dash(__name__, external_stylesheets=external_stylesheets)

app.layout = html.Div([
    dcc.Graph(id='graph-with-slider'),
    dcc.Slider(
        id='year-slider',
        min=df['year'].min(),
        max=df['year'].max(),
        value=df['year'].min(),
        marks={str(year): str(year) for year in df['year'].unique()},
        step=None
    )
])


@app.callback(
    Output('graph-with-slider', 'figure'),
    Input('year-slider', 'value'))
def update_figure(selected_year):
    filtered_df = df[df.year == selected_year]

    fig = px.scatter(filtered_df, x="gdpPercap", y="lifeExp",
                     size="pop", color="continent", hover_name="country",
                     log_x=True, size_max=55)

    fig.update_layout(transition_duration=500)

    return fig


if __name__ == '__main__':
    app.run_server(debug=True)

Das Beispiel könnt ihr direkt testen, indem ihr den Code in eine Datei packt und diese mit

python3 <name>.py 

ausführt. Per Default ist die Seite nur über localhost über den Port 3000 erreichbar. Ihr könnt den Host + Port allerdings ändern. Ersetzt dafür die letzte Zeile mit:

app.run_server(debug=True,Host='192.168.100.1',Port='3333')

Wer sich für Plotly Dash interessiert und wissen möchte, was alles möglich ist, kann sich die Gallery anschauen. Hier findet ihr eine Vielzahl an Beispiele. Ebenfalls habe ich in einem zusätzlichen Beitrag beschrieben, wie ihr Plotly Dash mit Apache2 in ein Produktivsystem einsetzen könnt.

Auch ich habe schon das ein oder andere Dashboard erstellt und kann nur somit wiederholen, was ich oben schon geschrieben habe. Mit etwas Python Kenntnissen ist im nu ein Dashboard erstellt.

IP-Adressliste mit Excel

Oft ist es sinnvoll einen IP Plan zu haben, um zu dokumentieren, ob und welche IP-Adressen noch frei sind, welche Netze in Benutzung sind und eine Menge anderer Gründe. Klar, es gibt viele Tools, die das anbieten, aber was ist, wenn man solch eine Liste selber generieren möchte? Ich habe mir dies kurz zur Aufgabe gemacht und mir eine IP-Adressliste mit Excel erzeugt. Die zu scannenden Netze werden als Arbeitsblatt erzeugt. Enthalten sind dort die Adressen, die gescannt werden sollen. Zusätzlich gibt es noch ein Feld für den Ping-Status, den DNS Namen falls vorhanden und etwaige Bemerkungen. Über VBA wird die IP-Adresse ausgelesen und ein ping versucht. Ist dies erfolgreich, überprüft das Script den DNS Namen eigenhändig und schreibt ihn in eine Zelle. Falls keine DNS-Auflösung stattfindet, übernimmt er die IP-Adresse.

Private Sub update_Click()
    Dim ws As Worksheet
    Dim x As Integer
    Dim i As Integer
    Dim Result As String
    Dim ip As String
    Dim arrSplitStrings() As String
    Dim excludeString As String
    
    
    x = 10 ' helfer Variable um die Zelle für die IP Bereiche zu bestimmen
    i = 2
    t_bereich = Worksheets("Settings").Range("A1:A50").Find("IP-Bereich").Row
    excludeString = Worksheets("Settings").Cells(9, 2).Value
    arrSplitStrings = Split(excludeString, ";")
    
    
    ' Bereich clearen   
    Worksheets("Überblick").Range("B10:B200").Clear
    'Gehe über jedes Arbeitsblatt
    For Each ws In Worksheets
        'Wenn dieses Arbeitsblatt nicht excluded werden soll, schreibe das Netz in die Übersicht
        If Not IsInArray(ws.Name, arrSplitStrings) Then
            Sheets("Überblick").Cells(x, 2) = ws.Name
            ' Suche den Namen des Netzwerkes in der Settings Arbeitsmappe
            Do While Not IsEmpty(Worksheets("Settings").Cells(t_bereich, 2))
                ' Wenn das Netz gefunden wurde, schreibe es in die Zelle
                If Worksheets("Settings").Cells(t_bereich, 2) = ws.Name Then
                    Sheets("Überblick").Cells(x, 3) = Worksheets("Settings").Cells(t_bereich, 3)
                End If
                ' erhöhe t_bereich, damit die nächste Zelle überprüft wird
                t_bereich = t_bereich + 1
            Loop

            'setze t_bereich wieder zurück
            t_bereich = Worksheets("Settings").Range("A1:A50").Find("IP-Bereich").Row
            
            'Solange es fortlaufende IPs gibt, führe ein Ping durch
            Do While Not IsEmpty(ws.Cells(i, 2))
                'Lese die IP aus
                ip = ws.Cells(i, 2).Value
                'Wenn ein Ping erfolgreich war, schreibe in die erste Zelle ein "S" für Success und färbe es grün
                If Ping(ip) Then
                    ws.Cells(i, 1).Value = "S"
                    ws.Cells(i, 1).Interior.Color = RGB(50, 205, 50)
                    'Wenn in der Zelle für die Namensauflösung noch nichts steht, versuche den Namen aufzulösen
                    If IsEmpty(ws.Cells(i, 3)) Then
                        'Namensauflösung zu der IP Adresse
                        ws.Cells(i, 3) = HostName(ip)
                    End If
                Else
                    'Wenn der Ping nicht erfolgreich war, schreibe "C" in die erste Zelle für Critical und färbe es rot
                    ws.Cells(i, 1).Value = "C"
                    ws.Cells(i, 1).Interior.Color = RGB(255, 120, 120)
                End If
                'erhöhe i um 1, damit die nächste IP gezogen wird
                i = i + 1
            Loop
            'setze i wieder zurück
            i = 2
            'erhöhe x für den nächsten IP-Bereich
            x = x + 1
        End If
    Next ws
    'Wenn alles fertig, gebe eine MessageBox aus
    MsgBox "Fertig!"
End Sub

In den obigen Code habe ich die Erklärung der wichtigsten Zeilen vorgenommen. Somit sollte dieser einfach zu verstehen sein. Sollte trotzdem etwas unklar sein, so fragt in den Kommentaren nach.

Weiter geht es mit der Ping Funktion. Relativ einfach ruft diese eine Shell auf und versucht die übergebene IP zu pingen. Je nach dem ob erfolgreich oder nicht, gibt es ein True oder False zurück. Durch den Parameter -n wird nur ein Paket verschickt. Mit Parameter -m haben wir eine Timeoutzeit von 1000ms und somit 1s definiert. Selbstverständlich dürfen die ICMP Pakete nicht von der Firewall verworfen werden. In dem Fall würde das Makro nicht korrekt funktionieren.

Function Ping(strcomputer)

Dim objshell, boolcode

Set objshell = CreateObject("wscript.shell")

boolcode = objshell.Run("ping -n 1 -w 1000 " & strcomputer, 0, True)

If boolcode = 0 Then

Ping = True

Else

Ping = False

End If

End Function

Ähnliches wie für den Ping gilt auch für den Hostname. Auch hier übergeben wir wieder die IP und es wird per Shell ein nslookup auf die IP gemacht. Erhalten wir den Namen so geben wir diesen zurück, ansonsten die IP.

Function HostName(ip$)
    Dim s As String, a
    s = CreateObject("Wscript.Shell").Exec("nslookup " & ip).StdOut.ReadAll
    a = Split(s, vbCrLf)

    If UBound(a) > 2 Then
        If Not a(3) = "" Then
            HostName = Right(a(3), Len(a(3)) - 9)
        Else
            HostName = ip
        End If
    End If

IsInArray ist eine Helferfunktion. Sie überprüft, ob der Wert im Array enthalten ist und gibt dementsprechend True oder False zurück.

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Da ich aus Sicherheitsgründen keine .xlsm Dateien mit Macros hochladen kann, stelle ich die Datei als .xlsx bereit.

Die oben genannten Funktionen müssen dementsprechend eigenst in Excel eingebunden werden, damit die IP-Adressliste mit Excel funktioniert. Alles andere sollte dann direkt funktionieren. Je nach Anzahl der Netze im Arbeitsblatt kann ein vollständiger Scan lange dauern. Unter anderem aus diesem Grund habe ich ein “exclude” Feld bei den Settings eingebaut.

cifs_mount failed w/return code = -13

Als ich heute versucht hatte eine Freigabe auf einem Ubuntu 20.04 über cifs zu mounten, bin ich ständig über den Fehler

cannot mount //0.0.0.0/Programing read-only.

gestolpert. Der Befehl dmesg zeigte mir indes noch eine andere Fehlermeldung, die so leider erstmal nichts aussagend ist.

CIFS VFS: \\0.0.0.0 Send error in SessSetup = -13
CIFS VFS: cifs_mount failed w/return code = -13

Die beiden Fehlermeldungen halfen mir leider nicht weiter. Die Freigabe hatte ich schon auf anderen Maschinen eingerichtet, ohne jegliche Probleme. Ein read-only konnte ich daher ausschließen. Zumal per default read-write Zugriff beim mount gewährt werden sollte. Nach etwas googlen bin ich auf die Lösung gestoßen. Mir fehlte mount.cifs, welches Bestandteil der cifs-utils ist. mount.cifs wird benutzt, wenn im mount Befehl der Schalter “-t cifs” gesetzt ist, was bei mir der Fall war.

sudo mount -t cifs //0.0.0.0/Programing -o username=user,uid=1000 /home/user/Programing

Nach dem Installieren des Package konnte ich die Freigabe auch direkt mounten

sudo apt install cifs-utils

Unser schönes Emsland

Heute möchte ich euch ein kleines Herzens Projekt vorstellen, bei dem ich beides vereint habe. Meine Leidenschaft fürs Fotografieren, als auch die Leidenschaft für die Informatik. Wie ihr im Namen seht, geht es um den Landkreis, in dem ich wohne. Ich bin der Meinung, auch hier im Emsland, haben wir viele Sehenswürdigkeiten, auch wenn diese nicht auf den ersten Blick ersichtlich sind. Ich versuche sowohl bekannte als auch unbekannte sehenswürdige Orte mit einzubringen. Darunter die offensichtlichen wie z.B. das Schloss Clemenswerth, aber auch solche, die wahrscheinlich noch niemand abgelichtet hat. Das Emsland besteht nämlich nicht nur aus seinen bekannten Destillerien 😉

In diesem Frame könnt ihr die Marker sehen. Die Marker stehen jeweils für ein sehenswerten Ort.

Mit dem Bild und dem Standort kommen noch kleine Informationen über den Ort/Sehenswürdigkeit, falls es denn soweit welche gibt. Darunter versuche ich noch allgemeine Informationen mit einzubringen, ob z.B. der Ort barrierefrei ist, wie dieser am besten zu erreichen ist (Auto, Bus, zu Fuß), ob es dort Verpflegung gibt und Parkmöglichkeiten vorhanden sind. Die Karte kann als Vollbild über https://steviesblog.de/blog/map aufgerufen werden.

Bitte achtet drauf die Orte sauber zu verlassen, keine Gegenstände/Pflanzen zu zerstören und die Tierwelt nicht zu stören! Respektiert auch bitte den Privatbesitz.

Technische Details

Ich möchte euch auch nicht vorhalten, wie genau dies funktioniert. Zuerst, wenn ich ein Spot erreicht habe, brauche ich die GPS Koordinaten. Dafür habe ich mir eine kleine App mit Kotlin geschrieben, die mir diese in eine CSV Datei abspeichert, mit dem Namen, den ich vorher vergeben habe. Diese Informationen speichere ich dann später in der Datenbank ab. Dafür existiert eine eigenständige Tabelle.

DROP TABLE IF EXISTS `c_spot`;

CREATE TABLE `c_spot` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `NAME` varchar(45) NOT NULL,
  `LATITUDE` decimal(24,15) NOT NULL,
  `LONGITUDE` decimal(24,15) NOT NULL,
  `MARKER_ID` INT(11) NOT NULL,
  `PICTURE_ID` bigint unsigned NOT NULL,
  `TEXT` varchar(245) NOT NULL,
  `DATE` datetime NOT NULL,
  `CREATED_AT` datetime NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `SPOT_UNIQUE` (`NAME`,`LATITUDE`,`LONGITUDE`),
  KEY `PICTURE_ID` (`PICTURE_ID`),
  KEY `MARKER_ID` (`MARKER_ID`),
  CONSTRAINT `ibfk_1` FOREIGN KEY (`PICTURE_ID`) REFERENCES `posts` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT
  CONSTRAINT `ibfk_2` FOREIGN KEY (`MARKER_ID`) REFERENCES `c_spot_marker` (`id`)
)
DROP TABLE IF EXISTS `c_spot_marker`;

CREATE TABLE `c_spot_marker`(
  `ID` INT(11) NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`ID`)
)

Die Daten gelangen über ein Plugin in die Tabelle. Die Spalte “TEXT” enthält den Html Code für das Popup-Fenster mit den zusätzlichen Informationen. Leaflet wird benutzt, um die Makierer zu platzieren, während OpenStreetMap(OSM) als Karte dazu gezogen wird. Eine for-Schleife geht über alle definierten Punkte in der Tabelle und platziert diese auf der Karte.