Differences

This shows you the differences between two versions of the page.

--- lehrkraefte:blc:informatik:ffprg2-2023:sqlite [2023/09/01 14:41] – [Mögliches Datenbankschema für ein Wörter-Lernprogramm] Ivo Blöchliger
+++ lehrkraefte:blc:informatik:ffprg2-2023:sqlite [2023/09/27 07:40] (current) – [Kommandozeile] Ivo Blöchliger
@@ Line 1: / Line 1: @@
+====== 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:
+  * https://zetcode.com/php/sqlite3/
+  * https://www.sqlitetutorial.net/sqlite-php/
+  * https://www.tutorialspoint.com/sqlite/sqlite_php.htm
+===== Kommandozeile =====
+Empfehlung: legen Sie die Datei ''~/.sqliterc'' auf dem Server an (oder auch lokal, wenn Sie lokal mit Sqlite arbeiten):
+<code text>
+.headers on
+.mode column
+PRAGMA foreign_keys = ON;
+</code>
+Die Datenbank kann auf der Kommandozeile abgefragt und manipuliert werden (dazu verbinden Sie sich am besten auf den OFI-Server).
+<code txt>
+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
+</code>
+https://gist.github.com/bladeSk/6294d3266370868601a7d2e50285dbf5
+Dump der Datenbank im Text-Format:
+<code bash>
+echo -e ".dump\n.quit" | sqlite3 scores.sqlite > scores.dump
+</code>
+Wiederherstellung:
+<code bash>
+sqlite3 copy.sqlite < scores.dump
+</code>
+===== High-Score-Datenbank =====
+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: {{lehrkraefte:blc:informatik:ffprg2-2023:scores.php}}
+  * HTML/JS auf https://ofi.tech-lab.ch/2023/teach/76f5054a/sqlite/
+===== Datenbank-Design =====
+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:
+<code bash>
+create table scores(id int primary key, name text not null default "noname", score integer not null default 0);
+</code>
+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.
+==== Mögliches Datenbankschema für ein Wörter-Lernprogramm ====
+**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|
+==== SQL-Code ====
+Anlegen der Datenbank
+<code sql>
+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));
+</code>
+Befüllen mit Werten
+<code sql>
+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);
+</code>
+Beachten Sie, dass die Abfrage
+<code sql>
+insert into pairs (word1, idlang1, word2, idlang2) values ("Fremdschlüssel Fehler", 77, "erreur de clé étrangère", 78);
+</code>
+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:
+<code sql>
+delete from langs where id==2;
+</code>
+Was aber natürlich funktioniert ist folgendes:
+<code sql>
+insert into langs (lang) values ("St. Galler Deutsch");
+select * from langs;
+delete from langs where id==3;
+</code>
+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:
+<code sql>
+.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;
+</code>
+Siehe auch: https://www.sqlitetutorial.net/sqlite-join/
+==== php-Code ====
+{{lehrkraefte:blc:informatik:ffprg2-2023:voki.php}}