SQLite

SQLite ist eine einfache dateibasierte Datenbank. Die Dateien sind zwar binär, können aber auch der Kommandozeile bearbeitet werden. Für die unsigen Anwendungen ist SQLite mehr als performant genug.

Links:

Empfehlung: legen Sie die Datei ~/.sqliterc auf dem Server an (oder auch lokal, wenn Sie lokal mit Sqlite arbeiten):

.headers on
.mode column
PRAGMA foreign_keys = ON;

Die Datenbank kann auf der Kommandozeile abgefragt und manipuliert werden (dazu verbinden Sie sich am besten auf den OFI-Server).

sqlite3 scores.sqlite
create table scores(name text not null default "noname", score integer not null default 0);
insert into scores values ("Hans Wurst", 42);
insert into scores values ("Max", 23);
insert into scores values ("Moritaz", 23);
select ROWID,* from scores;
select * from scores order by score desc;
update scores set name="Moritz" where name="Moritaz";
select * from scores order by score desc;
.quit

https://gist.github.com/bladeSk/6294d3266370868601a7d2e50285dbf5

Dump der Datenbank im Text-Format:

echo -e ".dump\n.quit" | sqlite3 scores.sqlite > scores.dump

Wiederherstellung:

sqlite3 copy.sqlite < scores.dump

Idee: PHP-Datei macht die Verbindung zwischen JS im Browser und SQLite auf dem Server. Die PHP-Datei liefert immer JSON, wenn es ein JSON-String ist, ist es eine Fehlermeldung. Ansonsten werden Objects für einzelne Werte oder Arrays mit Objects zurückgegeben.

PHP-Code: scores.php
HTML/JS auf https://ofi.tech-lab.ch/2023/teach/76f5054a/sqlite/

SQL-Datenbanken sind in Tabellen organisiert, mit genau definierten Kolonnen. Jede Zeile ist ein Eintrag in einer Tabelle. Jede Zeile hat (entweder explizit definiert oder automatisch) einen Index, normalerweise eine Ganzzahl. In SQLite heisst die ROWID, kann aber auch selbst gesetzt werden, z.B. mit:

create table scores(id int primary key, name text not null default "noname", score integer not null default 0);

Daten sollten nicht mehrfach in einer Datenbank gespeichert sein. Ein typisches Beispiel könnte sein die Kombination von PLZ und Ort, wenn man Adressen speichert.

Sprachen

id INT	sprache TEXT
1	Deutsch
2	Französisch

Wortpaare

id INT	wort1 TEXT	langid1	wort2 TEXT	langid2
42	ein Haus	1	une maison	2
23	eine Maus	1	une souris	2
63	ein Lächeln	1	un sourire	2

Autoren

id INT	name TEXT		…
12	Hans Wurst	…

Wortlisten

id INT	name TEXT	idauthor INT
3	Franz Voki	12

Zuordnungstabelle

idliste INT	idpair INT
3	42
3	63

Anlegen der Datenbank

PRAGMA foreign_keys=ON;
CREATE TABLE langs(id INTEGER PRIMARY KEY, lang text);
CREATE TABLE pairs(id INTEGER PRIMARY KEY, word1 text NOT NULL,idlang1 INT, word2 text NOT NULL, idlang2 INT, FOREIGN KEY(idlang1) REFERENCES langs(id), FOREIGN KEY(idlang2) REFERENCES langs(id));
CREATE TABLE authors(id INTEGER PRIMARY KEY, name text NOT NULL);
CREATE TABLE wordlists(id INTEGER PRIMARY KEY, name text NOT NULL, idauthor INTEGER, FOREIGN KEY(idauthor) REFERENCES authors(id));
CREATE TABLE pairlist(idlist INT, idpair INT, FOREIGN KEY(idlist) REFERENCES wordlists(id), FOREIGN KEY(idpair) REFERENCES pairs(id), PRIMARY KEY (idlist, idpair));

Befüllen mit Werten

INSERT INTO langs (lang) VALUES ("Deutsch"), ("Französisch");
INSERT INTO pairs (word1, idlang1, word2, idlang2) VALUES ("ein Haus", 1, "une maison", 2), ("eine Maus", 1, "une souris", 2), ("ein Lächeln", 1, "un sourire", 2);
INSERT INTO authors (name) VALUES ("Hans Wurst"), ("K. Asper");
INSERT INTO wordlists (name, idauthor) VALUES ("Hans-Voki", 1), ("Jörgs Voki", 2);
INSERT INTO pairlist (idlist, idpair) VALUES (1,1), (1,2), (2,2), (2,3);

Beachten Sie, dass die Abfrage

INSERT INTO pairs (word1, idlang1, word2, idlang2) VALUES ("Fremdschlüssel Fehler", 77, "erreur de clé étrangère", 78);

mit der Fehlermeldung Error: FOREIGN KEY constraint failed scheitert, weil es die Spachen mit ID 77 und 78 in der Datenbank nicht gibt (und PRAGMA foreign_keys=ON; gewählt wurde!).

Auch ist es nicht möglich, z.B. eine Sprache zu löschen, so lange diese referenziert wird:

DELETE FROM langs WHERE id==2;

Was aber natürlich funktioniert ist folgendes:

INSERT INTO langs (lang) VALUES ("St. Galler Deutsch");
SELECT * FROM langs;
DELETE FROM langs WHERE id==3;

Es wäre möglich, beim Anlegen der Tables bei den foreign keys die Option ON DELETE CASCADE anzugeben, dann werden alle Einträge, die diesen referenzieren auch gelöscht.

Abfragen:

.headers ON
.mode COLUMN
SELECT * FROM pairs;
SELECT word1, word2 FROM pairs;
SELECT word1, word2 FROM pairs INNER JOIN pairlist ON pairs.id==pairlist.idpair WHERE pairlist.idlist==2;
SELECT authors.name, wordlists.name FROM authors INNER JOIN wordlists ON authors.id==wordlists.idauthor;
SELECT name FROM wordlists WHERE idauthor==1;