⟵ zur Projektübericht

Motivation

Der ADFC-Stormarn e.V. verwendet Klebeetiketten zur Codierung von Fahrrädern.

Auf diesen Etiketten ist ein EIN-Code (Eigentümer-Identifizierungs-Nummer) aufgedruckt, die es der Polizei, den Fundbüros und dem ADFC erlauben, den Eigentümer eines Fahrrads zu ermitteln. Eine über aufgeklebte Etikett angebrachte Plombierfolie schützt dieses vor dem Ablösen.

Dieses Verfahren eignet sich auch für andere Gegenstände, wie Kameras, Fahrrad-Akkus, Kinderwagen, Pferdesättel, etc.

Die Codier-Aktionen finden häufig j.w.d. statt und nicht immer gibt es dort einen verlässlichen Zugang zum Internet. Die Daten zur Generierung der EIB-Codes werden deshalb in einer lokal zur Verfügung stehenden SQLite-Datenbank abgelegt.

Diese Seite beschreibt, die Struktur der Datenbank.

---

Version	Anpassungen
1.0 (2025-06-03)	Initiale Version

 

---

Datenbankstruktur

Daten in den Tabellen sind im auf Android-Geräten üblichen UTF8-Format abgelegt.

Download der SQL-Anweisung zur Erstellung des Datenbankschemas EIN-Schema.sql.

Tabellen

Die Daten zur Schlüsselgenerierung sind in vier hierarchisch angeordneten Tabellen abgelegt, die über referentielle Integrität miteinander verknüpft sind (Land, Kreis, Gemeinde, Strasse). In der Tabelle DBVersion sind die Versionsnummern zum Datenbankschema und zu den Daten abgelegt. In DataUpdate werden hinzugefügte Straßen mitprotokolliert. Die Tabell wird über Trigger gefüllt.

Tabelle Land

Die Tabelle Land enthält die Kürzel (Feld ID) und ausgeschriebene Bezeichnung (Feld Name) der Bundesländer (BW = Baden-Württemberg .. TH = Thüringen).

Tabelle Kreis

Die Tabelle Kreis enthält Daten zu den Landkreisen der Bundesländer. Das Feld ID enthält eine lfd. Nummer (autoincrement). Das Feld Land enthält das Kürzel des zugehörigen Bundeslandes referenziert auf das Feld ID der Tabelle Land. Das Feld Name enthält die Bezeichnung und Kfz das Kraftfahrzeugkennzeichen des Kreises bzw. der Stadt. Bei kreisfreien Städten ist der Name der Stadt abgelegt.

Tabelle Gemeinde

Die Tabelle Gemeinde enthält die Daten zu den Gemeinden in den  Landkreisen bzw. kreisfreien Städte. Das Feld ID enthält eine lfd. Nummer (autoincrement). Das Feld KreisID referenziert auf das Feld ID des zugehörigen Kreises. Im Feld Name ist die Bezeichnung der Gemeinde abgelegt. Das Feld Gemeindeschluessel enthält den Schlüssel als Text, wie er für den EIN-Code benötigt wird, also für S-H dreistellig mit führenden Nullen (z.B. "006" für Bargteheide) und ein Minuszeichen ("-") für HH.

Tabelle Strasse

Die Tabelle Strasse enthält die Daten zu den Straßen in den Gemeinden. Das Feld ID enthält eine lfd. Nummer (autoincrement). Das Feld GemeindeID referenziert auf das Feld ID der zugehörigen Gemeinde. Das Feld Ortsteil ist nur dann gefüllt, wenn es in der Gemeinde mehrere Straßen mit dem gleichen Namen in verschiedenen Ortsteilen gibt (z.B. nach Eingemeindung). Die Straßen haben dann unterschiedliche Schlüssel. Das Feld Name enthält die Bezeichnung der Straße. Das Feld Strassenschluessel enthält den Schlüssel als Text, wie er für den EIN-Code benötigt wird, also für S-H fünfstellig mit führenden Nullen (z.B. "00312" für Deviller Straße). Die Schlüssel für Hamburger Straßen beginnen mit dem ersten Buchstaben des Straßennamens, gefolgt von drei Ziffern (z.B. "A006" für ABC-Straße).

Tabelle DataUpdate

In der Tabelle DataUpdate werden Informationen zu Änderungen in der Tabelle Strasse mitprotokolliert. Das Feld ID enthält eine lfd. Nummer (autoincrement). Das Feld Type hat den Wert 1, wenn es sich um eine Einfügung handelt und den Wert 2 bei einer Änderung. Die anderen Felder entsprechenden denen in den anderen Tabellen.

Die Befüllung der Tabelle erfolgt über Trigger:

CREATE TRIGGER InsertStrasse 
   AFTER INSERT 
   ON Strasse
BEGIN
 INSERT INTO DataUpdate (Type, GemeindeID, StrasseID, Ortsteil, Strasse, Strassenschluessel)
        VALUES (1, NEW.GemeindeID, NEW.ID, NEW.Ortsteil, NEW.Name, NEW.Strassenschluessel);
END

CREATE TRIGGER UpdateStrasse 
   AFTER UPDATE 
   ON Strasse
BEGIN
 INSERT INTO DataUpdate (Type, GemeindeID, StrasseID, Ortsteil, Strasse, Strassenschluessel)
        VALUES (2, NEW.GemeindeID, NEW.ID, NEW.Ortsteil, NEW.Name, NEW.Strassenschluessel);
END

Tabelle DBVersion

In der Tabelle DBVersion sind zwei Datensätze mit den Versionsnummer für das Datenbankschema und den Datenstand abgelegt:

Ansichten (View)

Die Views erleichtern den Zugriff auf bestimmte Datenkonstellationen. Beim Abruf der Views durch die Extension UrsAI2SQLiteEIN werden meist Einschränkungen durch hinzugefügte WHERE-Klauseln gemacht.

View getDataVersion

Liefert die aktuelle Version des Datenstands:

SELECT Major, Minor, Major || "." || Minor AS Name, Datum FROM "DBVersion" WHERE Typ = "Data"

View getSchemaVersion

Liefert die aktuelle Version des Datenbankschemas:

SELECT Major, Minor, Major || "." || Minor AS Name, Datum FROM "DBVersion" WHERE Typ = "Schema"

View getLand

Liefert die Ländernamen zu einem Kfz-Kennzeichen:

SELECT Land.Name AS Name, Kreis.Kfz AS Kfz FROM Land INNER JOIN Kreis ON Land.ID = Kreis.Land

View getKreis

Liefert Daten zum Kreis und den Gemeindeschlüssel anhand der Gemeinde-ID.

SELECT Kreis.Kfz, Kreis.Name AS Kreis, Land.Name AS Land, Gemeinde.ID AS GemID, Gemeinde.Gemeindeschluessel AS Schluessel
FROM Land INNER JOIN (Kreis INNER JOIN Gemeinde ON Kreis.ID = Gemeinde.KreisID) ON Land.ID = Kreis.Land

View getGemeinde

Liefert den Gemeindeschlüssel und das Kfz anhand des Gemeindenamens.

SELECT Gemeinde.Name AS Name, Gemeinde.Gemeindeschluessel AS GemSchl, Kreis.Kfz AS Kfz
FROM (Kreis INNER JOIN Gemeinde ON Kreis.ID = Gemeinde.KreisID)

View getStrasse

Liefert Daten zu einer Straße anhand des Strßennamens.

SELECT Strasse.Name || CASE WHEN Strasse.Ortsteil = "" THEN "" ELSE " (" || Ortsteil || ")" END  AS Name, 
       Strasse.Strassenschluessel AS StrSchl, Gemeinde.ID AS GemID, Gemeinde.Gemeindeschluessel AS GemSchl, Kreis.Kfz AS Kfz
FROM (Kreis INNER JOIN Gemeinde ON Kreis.ID = Gemeinde.KreisID) INNER JOIN Strasse ON Gemeinde.ID = Strasse.GemeindeID

View Gemeindeliste 

Liefert eine Liste der Gemeinden mit aufbereitetem Namen.

SELECT Gemeinde.ID AS ID, Gemeinde.Name AS Gemeinde, 
CASE
    WHEN Gemeinde.Name =  Kreis.Name THEN " (" || Kreis.Land || ")"
    ELSE " (" || Kreis.Name || ", " || Kreis.Land || ")"
END AS Text 
FROM Kreis INNER JOIN Gemeinde ON Kreis.ID = Gemeinde.KreisID ORDER BY Gemeinde

View Strassenliste

Liefert eine Liste der Straßen, ggf. mit Ortsteil.

SELECT Name AS Strasse,
      CASE WHEN Ortsteil = "" THEN "" ELSE "(" || Ortsteil || ")" END AS Ortsteil, ID, GemeindeID
	  FROM Strasse ORDER BY Strasse, Ortsteil

View Schluessel

Liefert eine Kombination aller Daten.

SELECT Land.Name AS Land, Kreis.Name AS Kreis, Kreis.Kfz AS Kfz, Gemeinde.Name AS Gemeinde, Gemeinde.Gemeindeschluessel, Strasse.Name AS Strasse, Strasse.Ortsteil AS Ortsteil, Strasse.Strassenschluessel
FROM (Land INNER JOIN (Kreis INNER JOIN Gemeinde ON Kreis.ID = Gemeinde.KreisID) ON Land.ID = Kreis.Land) INNER JOIN Strasse ON Gemeinde.ID = Strasse.GemeindeID