[SQLite3] Wenn man SQLite3 erlernt II

[Joopi]

SQLite3 könnte im Laufe der Zeit für mich wichtig werden. Ich möchte ja nicht unbedingt mit MySQL oder MariaDB arbeiten. SQLite3 ist schlank und kann, wenn gewünscht, mit anderen Programmen verarbeitet werden.

Auch kann ich mir vorstellen, dass ich mit Shellscripten eine Verbindung zu SQLite3-Datenbanken herstellen kann.

• Eine Dokumentation zu SQLite3 liegt hier](file:///usr/share/doc/sqlite3-doc/index.html)
  
• Eine Dokumentation von SQL liegt hier](file:///usr/share/doc/sqlite3-doc/lang.html)
  
• Eine Dokumentation der Client-Befehle, für mich die wichtigen Angaben, liegt hier](file:///usr/share/doc/sqlite3-doc/cli.html)
  
• Eine weitere Dokumentation ist hier zu finden](https://sqlite.org/cli.html)
  
• <https://sqliteonline.com/>
  
• <https://www.php.net/manual/de/ref.sqlite.php>
  
• <https://riptutorial.com/de/php/example/27461/sqlite3-schnellstartanleitung> - sehr gut, hat geholfen die Anzahl auszuwerten
  
• <http://www.hackingwithphp.com/9/7/3/getting-started-with-sqlite-3> - in englischer Sprache, aber sehr, sehr gut!!!!!!!
  

In der Handhabung von SQLite gibt es zwischen den verschiedenen Plattformen doch einige Unterschiede.

Um über den Export-Befehl eine Tabelle als CSV-Datei zu exportieren, sind folgende Zeilen notwendig:

Code:

sqlite> .header on 

Code:

sqlite> .mode csv 

Code:

sqlite> .once /home/django/help/help3/dataout.csv 

Code:

sqlite> select * from users; 

Code:

sqlite> .system xdg-open /home/django/help/help3/dataout.csv

Der letzte Befehl öffnet im Grunde genommen ein Importfenster aus LibreOfficeCals, legt aber auch die entsprechende Datei an (in Mac heißt das open, unter Windows ist nichts anzugeben).

Um die ganze Datenbank zu sichern, ist folgender Befehl außerhalb von SQLite, also auf der Konsole am Prompt, einzugeben (im Beispiel ist das aktuelle Verzeichnis auch das Datenbankverzeichnis):

Code:

sqlite3 users.sqlite .dump | gzip -c >/home/django/help/help3/users.dump.gz

Eine Datenbank ist recht einfach auf der Konsole zu erstellen. Es ist jedoch wichtig zu wissen, dass eine gänzlich leere Datenbank sofort wieder gelöscht wird. Daher ist es ratsam, sofort im Anschluss, wenn man die Datenbank halten will, eine Tabelle anzulegen. Damit ist gewährleistet, dass die Datenbank aktiv bleibt und vorhanden ist.

Die Definition von Tabellen ist im Grunde genommen relativ einfach, da es keine expliziten Datentypen wie bei MySQL gibt. Eine Datumsangabe wird als String erfasst und sollte die Syntax JJJJ-MM-TT haben.

Eine Ausnahme bildet die Möglichkeit bzw. die Notwendigkeit, einen Primärindex zu schaffen. Dieser wird wie folgt definiert: integer primary key

Wenn ich also, wie ich es gewohnt bin, eine ID als Autowert in dieser Tabelle haben, so muss ich bei der Anlage der Tabelle, am besten wie immer zu Beginn, diese definieren: **id integer primary key**

Soweit ich das verstanden habe, muss die ID nicht mit angegeben werden, sondern bei der Erfassung eines Datensatzes zählt SQLite3 den Autowert selber hoch.

CSV-Daten importieren

Der Import über eine CSV-Datei ist relativ einfach.

Der Aufbau der CSV-Datei ist wie der Export einer Tabellenabfrage und könnte wie folgt aussehen:

6,mailadresse@beispiel.com,Vorname,"Beliebiger Text."

Innerhalb von SQLite3, also in der aktiven Datenbank, ist dann folgendes einzugeben:

Code:

sqlite> .mode csv 

Code:

sqlite> .import datain.csv mails

In der CSV-Datei sollte die erste Zeile, sofern sie die Feldnamen beinhaltet, gelöscht werden.

fetchAssoc() und fetchArray()

Als weitere wichtige Information habe ich folgendes gefunden:

Finally, note that there is no fetchAssoc() method at this time, so SQLite's fetchArray() method is used, specifying SQLITE3_ASSOC as parameter two. If you do not do this, fetchArray() will return each field of data twice - once with its numeric index, and again with its field name string index.

Das bedeutet für mich, dass SQLite3 nur mit fetchArray() arbeitet, aber kein fetchAssoc() kann.

Abfrageresultate zeigen

Die Ausgabe einer Select-Anweisung ist immer noch das spannedste Thema und bringt mich immer wieder in Bedrängnis.

Das nachfolgende Beispiel für die Ausgabe einer Auswertung ist aus einem anderen Erklärungsanlass, zeigt aber die grundelegende Bedeutung des Ganzen:

PHP-Code:

<?php
    $db->createFunction("EXISTS_IN_BOTH", "ExistsInBoth");

    $result = $db->query("SELECT Name FROM employees WHERE EXISTS_IN_BOTH(Name)");

    while($row = $result->fetchArray($query, SQLITE_ASSOC)) {
        extract($row);
        print "$Name is in both databases\n";
        }
?>

Es geht nur um den Teil nach der Query-Auswertung, wo mit while die Ausgabe angestoßen wird. Hier ist zu bedenken, dass scheinbar die Syntax für SQLite genutzt wurde. Ich arbeite aber mit SQLite3.

Ich habe den Code also versucht an SQLite3 anzupassen und folgenden funktionierenden Programmcode erhalten:

PHP-Code:

<?php
    /* Datenbankdatei öffnen bzw. erzeugen */
    $db = new SQLite3("mailbox.db");

    /* Drei Datensätze eintragen */
    $res = $db->query("SELECT * FROM mails");

    /* Ausgabe der Daten in einer Schleife */
    while($row = $res->fetchArray(SQLITE3_ASSOC)) {
        extract($row);
        //print "$name als Test\n";
        echo "<p>Das ist der Name: " . $id . "</p>";
        echo "<p>Das ist der Name: " . $mailaddi . "</p>";
        echo "<p>Das ist der Name: " . $name . "</p>";
        echo "<hr>";
    }

    /* Verbindung zur Datenbank wieder lösen */
    $db->close();
?>

Im Parameter von fetchArray darf nicht das Wort query stehen und es ist naürlich SQLITE**3**_ASSOC.

Das Datum ist auch so eine Sache für sich

Bei der Erfassung von Datensätzen ist es angebracht, Zeitstempel zu setzen. Bei MySQL ist das mit now() recht einfach.

Diese Funktion gibt es bei SQLite anscheinend nicht, aber es gibt eine Variante, die funktioniert:

Code:

insert into test (partner,erfasst, geaendert) values ("Margot", datetime('now'), datetime('now'));

Die Ausgabe über SELECT ist dann wie folgt:

2,Margot,"2020-04-02 15:42:20","2020-04-02 15:42:20"

Leider wird die UTC-Zeit gesetzt und nicht die lokale Zeit. Das Zeitformat datetime('localtime') hat nicht funktioniert.

Die beiden Datumsfelder müssen nicht zwingend als solche definiert werden. Das ist bei SQLite3 sowieso eine Sache, die ich noch nicht so ganz verstehe, da ich im Internet Artikel finde, die eine Deklaration von Datentypen bei der Erstellung von Tabellen zeigen.

• DATETIME() zeigt später das Datum und die Uhrzeit.
  
• DATE() hingegen zeigt nur das Datum.
  
• TIME() zeigt nur die Uhrzeit, die wohl immer als UTC bzw. GMT gezeigt wird. Man soll später bei der Auswertung entsprechend umstellen.
  

Eine mögliche Lösung

Um das Problem zu beheben, da scheinbar nicht über localtime erfasst werden kann, ist es, bei der Ausgabe der Datensätze das Feld bzw. die Felder, die Zeitangaben beinhalten, auf die lokale Zeit umzurechnen.

Das kann so erfolgen:

Code:

select datetime(erfasst, 'localtime') from test1;

Die Angabe der Zeitzone CET funktioniert in diesem Zusammenhang leider nicht.

Die Lösung!!!

Entgegen der Annahme, dass es mit localtime nicht geht, habe ich es geschafft.

Datensatzerfassung:

Code:

insert into test1(name, erfasst, geaendert) values ("Vera", datetime('now'), time('now', 'localtime'));

Resultat bei der Abfrage:

3|Vera|2020-04-02 16:10:58|18:10:58

Der erste Eintrag ist die normale Zeit, also UTC, der zweite Eintrag ist im Grunde genommen die CET-Zeit, also mein lokale Zeit. Damit weiß ich, wie ich Zeitangaben regeln kann.

Im Internet gibt es Stimmen, die sagen, dass man das nicht machen soll, sondern eben bei der Datenausgab, wie vorher beschrieben, eine Umrechnungen machen soll.

Das Einfügen von Werte über Variablen birgt Fallen und Tücken.

Ich hatte das Problem, dass zwar Datensätze in der Tabelle enthalten waren, aber diese bei der Auswertung über ein PHP-Script nicht angezeigt wurden.

Nach langem Suchen habe ich festgestellt, dass ich die Werte mit einer Variablen in die Tabelle schreiben, die in Hochkommata gesetzt war. Somit sind die Werte als Strings erfasst worden und die als Integer-Werte. Nachdem ich die Werte via UPDATE mehr oder weniger neu erfasst habe, über die Konsole habe ich das gemacht, wurden die Datensätze bei der Selektion auch angezeigt.

Somit muss das INSERT-Statement wie folgt aussehen:

PHP-Code:

<?php
    $db->query($sqlstr. "('$n1', $n2, $n3, '$n4', datetime('now', 'localtime'), datetime('now', 'localtime'))");
?>

Die Variablen $n2 und $n3 werden ohne Hochkommata eingebaut und sollten somit Zahlenwerte beinhalten.

Update am 27.05.2020**

Will man in einer SQLite3-Tabelle nachträglich einen primary key einfügen, so ist das nicht möglich. Es geht einfach nicht.

Stattdessen muss man die betroffene Tabelle umbenennen, eine neue Tabelle mit dem primary key erstellen (mit dem alten Namen) und dann die Daten von der umbenannten Tabelle in die neue Tabelle zu kopieren.

Das geht wie folgt:

Tabelle ohne primary key umbenennen:

Code:

ALTER TABLE datentabelle RENAME TO datentabelle_alt;

Neue Tabelle mit pirmary key erstellen:

Code:

CREATE TABLE datentabelle (id primary key, vorname, nachname);

Daten der umbenannten Tabelle in die neu erstelle Tabelle kopieren:

Code:

INSERT INTO datentabelle (id, vorname, nachname) SELECT id, vorname, nachname from datentabelle_alt;

Die alte, also umbenannte Tabelle nach Prüfung löschen:

Code:

DROP TABLE datentabelle_alt;

Damit ist das Feld **id** mit einem primary key belegt.

Für den normalen Gebrauch

Folgende Vorgänge muss ich auf der Konsole lernen und beherrschen:

• Datenbank anlegen
  
• Tabelle anlegen
  
• Daten erfassen
  
• Tabellenstruktur ansehen (Welche Felder gibt es?)
  
• Tabelle ändern (ALTER)
  
• Tabelle auswerten (SELECT)
  
• Datensatz löschen (DELETE)
  
• Datensatz ändern (UPDATE)
  

Update am 12.06.2020

Für die Darstellung der im Jahr gefahrenen Kilometer hole ich die Daten aus einer Datenbank. Leider geht das nicht mehr so wie in MySQL, sondern ich musste nun andere Wege finden.

Um Jahressummen der gefahrenen Kilometer zu erhalten, musste ich wiederum das Jahr aus dem Tag der Tour ermitteln. Das geht so:

Code:

strftime('%Y', tag)

Das ist das SQL-Statement zur Abfrage:

PHP-Code:

<?php
    $data = $db->query("select strftime('%Y', tag) as Jahr, sum(km) as KM from $tblname109 group by strftime('%Y', tag) order by strftime('%Y', tag) desc");
?>

%Y steht für eine vierstellig formatierte Jahreszahl. tag ist das Feld in der Tabelle für das Datum der Radtour. strftime ist die Funktion, um die Jahreszahl aus dem Datum heraus zu holen.

Die Syntax für die Summierung der Kilometer je Jahr wurde so in der Form anstandslos akzeptiert.

Aber auch die Ausgabe der Ergebnisse musste umgebaut und an SQLite3 angepasst werden:

<?php
    while($r = $data->fetchArray(SQLITE3_ASSOC))
    {
        echo var_dump($r);
        echo "<tr>";
        echo "<td>" . $r["Jahr"] . "</td><td>" . $r["KM"] . "</td>";
        echo "</tr>";
    }
?>

Zuvor hatte ich die Variable $r durch einen var_dump ausgeben lassen und somit wusste ich, welche Feldnamen ich aus dem Array über übernehmen hatte - nämlich die über as neu definierten Feldnamen.

Die Radtourliste als auch das Erfassungsformular sind damit wieder einsatzbereit.