====== Technische Details zur Implementierung der SQL-IDE ======
===== Last auf das System =====
Die SQL-IDE verwaltet derzeit (Stand Dezember 2025) ca. 33500 Datenbanken von ca. 10000 Usern. Zu Spitzenzeiten (d.h. wochentags am Vormittag) gibt es ca. 150 User, die gleichzeitig online sind. Bei der Speicherung der Datenbanken kommt Deduplizierung zum Einsatz, daher ist die serverseitig notwendige Speicherkapazität relativ gering (derzeit ca. 500 MB).

===== Überlegungen zur Architektur =====
Da die Entwicklung der SQL-IDE erst zwei Jahre nach dem Start der Online-IDE begann, war die Größenordnung der zu erwartenden Zahl an Usern (und damit auch Datenbanken) schon abschätzbar. 
==== Einfacher Ansatz (nicht umgesetzt) ====
Es läge nahe, serverseitig eine Instanz von MariaDB oder Postgres zu installieren und in dieser - je nach Bedarf - die Datenbanken der User anzulegen. Alle an den Clients eingegebenen SQL-Statements der User werden per http-Request zum Server übermittelt, dort in der entsprechenden User-Datenbank ausgeführt, die Ergebnistabelle wird zurückgeliefert und am Client angezeigt. \\ \\ 
Diese Architektur erschien aus folgenden Gründen problematisch:
  * a) Es war keine Dokumentation darüber zu finden, wie sich MySQL oder Postgres verhalten, wenn man in einer Instanz zehntausende Datenbanken anlegt.
  * b) Das System sollte damit umgehen können, dass mehrere hundert User gleichzeitig online sind und SQL-Statements eingeben. (Insbesondere auch Anfänger-Statements, die durch unbedarfte Joins sehr große Ergebnistabellen erzeugen!)
  * c) Das System sollte die Größe der einzelnen Datenbanken überwachen und ggf. beschränken.
  * d) Mein Wunsch war es, die SQL-IDE den Schulen ohne Aufpreis zusätzlich zur Online-IDE zur Verfügung zu stellen.
Grobe Abschätzungen und kurze Testläufe ergaben, dass die Miete eines deutlich stärkeren Servers notwendig gewesen wäre, um mit dem aufgrund von b) zu erwartenden Traffic umzugehen. Damit wäre d) nicht machbar gewesen. Zudem stellte sich heraus, dass c) schwer umsetzbar war und die Unsicherheit a) konnte nicht abschließend beseitigt werden. \\ \\ 
Der Ansatz wurde daher verworfen.
==== Ansatz mit clientseitigem Datenbank-System ====
Auf der Suche nach einem Datenbanksystem, das sich innerhalb der Client-Browser ausführen ließ, stieß ich auf [[https://github.com/sql-js/sql.js|SQL.js]], eine Implementierung von [[https://sqlite.org/about.html|SQLite]], die im Browser läuft und sich per Javascript-API steuern lässt. Der Grundgedanke war, die User-Datenbanken serverseitig als Binärdatei im SQLite-Format zu speichern, beim Selektieren der Datenbank an die Clients auszuliefern, alle SQL-Statements dort auszuführen und - im Falle datenverändernder SQL-Statements - die veränderte Datenbankdatei als Ganzes wieder zurück zum Server zu senden und dort zu speichern. Dem standen folgende Schwierigkeiten im Weg:
  * a) SQLite weicht vom SQL-Standard ab, indem es die Datensätze untypisiert speichert. 
  * b) Die Größenbeschränkung der User-Datenbanken sollte bei mindestens 10 MB liegen. Es ist ineffizient, diese 10 MB bei jedem datenverändernden SQL-Statement vom Client zum Server zu schicken.
  * c) User sollen Datenbanken auch gleichzeitig gemeinsam nutzen können.

===== Endgültige Architektur =====
==== Speicherung der User-Datenbanken ====
Der Server speichert User-Datenbanken immer in zwei Teilen:
  * Die vom User hochgeladene oder von der Lehrkraft an die Schüler/-innen ausgeteilte "Grund-Datenbank" wird in einer ersten Datei im SQLite-Binärformat gespeichert.
  * Alle vom User auf dieser Datenbank ausgeführten datenverändernden SQL-Statements werden in einer zweiten Datei als Text gespeichert.
==== Selektion einer Datenbank durch einen User ====
Wählt ein User die Datenbank in der SQL-IDE aus, so passiert folgendes:
  * 1. Der Client holt die SQLite-Binärdatei vom Server (oder aus dem Browser-Cache!) und initialisiert damit SQLite.js.
  * 2. Der Client holt die datenverändernden SQL-Statements vom Server und führt sie "in einem Rutsch" aus. \\ Jetzt ist die Datenbank clientseitig bereit für weitere SQL-Anweisungen.
  * 3. Der Server "merkt" sich, dass der Client jetzt mit dieser Datenbank verbunden ist und teilt ihm fortan alle Änderungen mit, die andere - evtl. gleichzeitig mit dieser Datenbank verbundene - Clients an der Datenbank vornehmen (s.u.).

Die getrennte Speicherung der "Grunddatenbank" und aller durch den User ausgeführten datenverändernden Statements ermöglicht es u.a., ausgeführte Statements auch nach Tagen wieder rückgängig zu machen (Rollback). Erreicht wird das dadurch, dass 
  * das letzte SQL-Statement aus der Liste der Statements gelöscht wird,
  * die Datenbank in SQL.js gelöscht wird, 
  * die "Grunnddatenbank" neu geladen wird und zuletzt
  * alle SQL-Statements mit Ausnahme des letzten Statements ausgeführt werden.

Sowohl die Selektion einer Datenbank als auch das Rollback erfolgen selbst bei hunderten von SQL-Statements so schnell, dass sie von den Usern i.d.R. nicht als langsam empfunden werden.
==== Ausführung einer SQL-Anweisung ====
Gibt ein User eine SQL-Anweisung ein und klickt auf den Ausführen-Button, so passiert folgendes:
  * **Lesende** Statements werden einfach clientseitig ausgeführt. Der Server bekommt davon gar nichts mit.
  * **Schreibende** Statements werden in drei Schritten ausgeführt:
    * 1. Der Client schickt das Statement und die Anzahl der bisher ausgeführten datenverändernden Statements zum Server. 
    * 2. Der Server überprüft, ob beim Client die komplette Liste der datenverändernden Statements zur aktuellen Datenbank vorliegt (ein anderer User könnte die Datenbank wenige Millisekunden zuvor verändert haben, was zu einer Race-Condition führen würde). Er speichert das Statement in der SQL-Textdatei, benachrichtigt alle anderen aktuell mit der Datenbank verbundenen Clients über das neue Statement (die führen es clientseitig gleich aus) und schickt dem Client, der das Statement gesendet hat, das "OK" zur Ausführung zusammen mit der Liste der noch fehlenden Statements.
    * 3. Der Client führt ggf. die noch fehlenden Statements aus und anschließend das vom User eingegebene Statement.

==== Bearbeiten von SQL-Statements durch den User ====
Als Editor wird der [[https://microsoft.github.io/monaco-editor/|Monaco Editor]] von Microsoft verwendet, der auch in Visual Studio Code zum Einsatz kommt. Die semantischen Informationen, die nötig sind, um bspw. kontextsensitive Codevervollständigung anzubieten oder Fehler schon bei der Texteingabe zu unterringeln, erhält der Editor von einem eigens für die SQL-IDE implementiertn SQL-Compiler. Im Gegensatz zu SQLite arbeitet dieser typisiert und nah am SQL-Standard, sodass beim User der Eindruck entsteht, Statements für einen voll SQL-konforme Datenbank zu schreiben. Der Compiler nutzt als Eingabe nicht nur das aktuell vom User bearbeitete SQL-Statement, sondern auch die Strukturdaten der aktuell selektierten Datenbank. \\ \\ 
Klickt der user auf den Button "Statement ausführen",  wird das gerade im Cursorbereich befindliche Statement mithilfe des vom Compiler generierten Abstract Syntax Tree aus der Menge der im Editor befindlichen Statements herausgeschält und - im Falle datenverändernder Statements - so verändert, dass es der Syntax von SQLite entspricht. Zudem werden ''CREATE TABLE''-Statements um SQLite-spezifische ''check''-Anweisungen erweitert, die sicherstellen, dass die gespeicherten Datensätze den definierten Spaltentypen entsprechen.
==== Weitere Optimierungen ====
  * Beim Veröffentlichen einer Datenbank oder beim Austeilen durch eine Lehrkraft werden alle bisher ausgeführten schreibenden SQL-Statements in die SQLite-Datenbankdatei integriert und diese dann weitergegeben.
  * Serverseitig kommt zur Speicherung der SQLite-Datenbankdateien Deduplizierung zum Einsatz. 
  * Der Server cached, die zuletzt verwendeten SQLite-Datenbankdateien, die zuletzt verwendeten Textdateien mit SQL-Statements und alle Benutzerdaten im Arbeitsspeicher. Dadurch kann er einen großen Teil der http-Anfragen ohne Zugriff auf den Massenspeicher erledigen.