Lazarus & SQLite: Datum im YYYY-MM-DD-Format

[Judas]

Moin, zusammen.

SQLite verfügt über keinen eigenen Datumstyp. Daher kann in der Datenbank das Datum in unterschiedlichen Formaten (String, Double und Integer) gehalten werden. Ich würde gerne das Datum als String im Format YYYY-MM-DD verwenden wollen. Dies, weil das Datum dann auch außerhalb der Lazarus-Anwendung lesbar bliebe.

Zu diesem Thema habe ich bereits einiges hier in diesem Forum und auch an anderen Stellen gelesen. Der Tenor dazu ging in die Richtung: ja, das geht prinzipiell, aber ...

Ich habe jetzt aus einer Beispielanwendung zu SQLite3 eine leicht modifizierte Version erstellt. Dies zusammen mit einer kleinen SQlite3-Datenbank die eine Tabelle im besagten Format erhält. *Lesen* kann ich die Daten in der Lazarus-Anwendung immerhin. Problematisch wird es nur, wenn wenn ich einen Datensatz editieren oder hinzufügen möchte. Beim Editieren sieht es aus, als funktioniere es, nur werden die Änderungen nicht dauerhaft gespeichert. Eine Fehlermeldung gibt es erstaunlicherweise nicht.

Vielleicht hat ja jemand Zeit und Lust, einen Blick auf das Ganze zu werfen und mir ein paar Hinweise zu geben, wo mögliche Fehler (im Denken und Handeln) bei mir liegen. Das Projekt befindet sich in archivierter Form im Anhang.

SQLite-Project.7z

(163.06 KiB) 136-mal heruntergeladen

Schon einmal Danke im Voraus für euer Interesse

Liebe Grüße

[af0815]

Code: Alles auswählen

procedure TForm1.CloseDataBase;
begin
  queryGuitars.ApplyUpdates;
  queryMembers.ApplyUpdates;
  queryGuitars.Active := False;
  queryMembers.Active := False;
  SQLTransaction1.Active := False;
  SQLite3Connection1.Connected := False;
end;

Mir geht bei dir das ApplyUpdates ab. ENtweder nach dem Post oder bevor du alles schliesst. Damit werden die internen Puffer erst in die DB eingepflegt.

[Judas]

Moin, af0815

Mir geht bei dir das ApplyUpdates ab. ENtweder nach dem Post oder bevor du alles schliesst. Damit werden die internen Puffer erst in die DB eingepflegt.

Nun, wie ich schon erwähnte, ist diese Beispielanwendung nicht auf meinem Mist gewachsen. Ich habe sie nur etwas modularisiert, ohne die Funktionalität zu ändern.

Deinen Vorschlag mit dem "ApplyUpdates" habe ich aufgegriffen. Jedoch setzt "ApplyUpdate" voraus, dass die Query zum Zeitpunkt aktiv ist. Der Programmierer der Ursprungsversion setzt zum Programmstart prophylaktisch erst einmal alles auf inaktiv was dann zur Folge hätte, dass "ApplyUpdates" einen Fehler produziert.

Ich habe jetzt also dein Anraten in folgender Form umgesetzt:

Code: Alles auswählen

procedure TForm1.UpdateQuery(var Query: TSQLquery);
begin
  with Query do
    if Active then
       ApplyUpdates;
end;

procedure TForm1.CloseDataBase;
begin
  UpdateQuery(queryGuitars);
  UpdateQuery(queryMembers);
  queryGuitars.Active := False;
  queryMembers.Active := False;
  SQLTransaction1.Active := False;
  SQLite3Connection1.Connected := False;
end;    

Mein bisheriger Stand ist also der: wenn ich einen Datensatz der Mitgliedertabelle editiere, wird die Änderung auch zur Laufzeit richtig angezeigt. Nach dem Programmende und erneutem Aufruf ist die Änderung perdü!

Momentan ist mir nicht klar, warum.

Mit ratlosen Grüßen

[af0815]

Für dieses Verhalten gibt es grundlegend 2 Möglichkeiten.

a) Eine Transaktion wurde nicht komplett beendet und damit ein Rollback ausgelöst.
b) Der interene Puffer nicht in den Datenbank persistent gemacht (Bei manchen DBs ist ApplyUpdates notwendig, das hängt auch von der Konfiguration der Connection ab).

b) sollte mit der Änderung von Dir behoben sein
Edit:
b) braucht man nicht extra ausführen, es ist unnötig ! Das wird bei der Connection bereits auf automatisch Committen gesetzt

Code: Alles auswählen

procedure TForm1.InitDataBase;
begin
  SQLite3Connection1.DatabaseName := 'guitarclub.db';
  queryMembers.Options := [sqoKeepOpenOnCommit,sqoAutoApplyUpdates,sqoAutoCommit];
  .....
  queryGuitars.Options := [sqoKeepOpenOnCommit,sqoAutoApplyUpdates,sqoAutoCommit];
  .....
end;

sqoAutoApplyUpdates : Die Änderungen werden automatisch an die DB weitergegeben, es braucht KEIN extra ApplyUpdates
sqoAutoCommit: Die Transaktionen werden automatisch durchgeführt -> somit kann es auch nicht a) sein

Ich habe den Code jetzt von dir Grundlegend am laufen (ich musste mir erst Treiber für SQLite mal suchen ). Wenn ich Änderungen mache so werden diese bei mir auch in der DB gespeichert. Das ist kein Problem. Nur ich habe jede Menge anderer Laufzeitfehler, wenn ich bei den 'Members' versuche einzufügen. Mit einem (Laufzeit-)Fehler wird natürlich auch nichts in die DB übernommen, auch wenn etwas lokal angezeigt wird. Das könnte der Grund sein. Wenn ich in 'Guitars' etwas ändere wird es übernommen.

Damit kannst du die Änderungen mit dem ApplyUpdate rauswerfen. Die machen keinen Sinn.

[af0815]

Was mir überhaupt nicht gefällt, ist die Definition der Query

Code: Alles auswählen

  queryMembers.SQL.Text := 'select FirstName, Lastname, strftime(''%d.%m.%Y'',[RegistDate]) as RegistDate, ID from members order by RegistDate';

Das ist meiner Meinung nach keine aktualisierbare Query, das Datum wird nicht im nativen Format verwendet. Weil die Querykomponente muss aus dieser Zeile, dann das Insert, Delete und Updatestatement ableiten. Formatierungen gehören meiner Meinung nach nur in UserInterface nicht in die QUery selbst. AUSSER die Query dient NUR zur Anzeige. Und bei mir gibt es definitiv jede menge Fehlermeldungen zu Laufzeit !!

Das SQLite eine eigene Geschichte mit dem Datum ist, habe ich schon in der Vergangenheit zu spüren bekommen. Ich schau mir das am Abend an.

[Judas]

Moin, af0815.

Danke, dass du dich derart in dieses Thema einbringst. Wobei ich denke, dass dieser Thread ja nicht nur mir zugute kommt, sondern auch anderen, die jetzt oder später hier mitlesen.

a) Explizite "applyupdates" sind unnötig.

Okay. Habe ich jetzt also bei mir hier wieder rückgängig gemacht. Sie haben zwar nichts verschlechtert, waren aber unnötig.

b) SQL-Statement in "queryMembers".

Das dieses nicht der Weisheit letzter Schluss ist, hatte ich auch bereits eingesehen. Es wird bewirkt, dass das Datum Anwendungsintern als Zeichenkette behandelt wird. Dies hat u.a. den unerwünschten Nebeneffekt, dass die (aufsteigende) Sortierung nicht stimmt:

01.07.2000
02.07.1999
03.07.1998

anstatt

03.07.1998
02.07.1999
01.07.2000

Grundsätzlich sollte das Verfahren wie folgt ausschauen:

1. Das Datum wird Anwendungsintern mit dem Datentyp TDateTime gehalten.
2. In der SQLite-Datenbank wird das Datum als Zeichenkette im Format YYYY-MM-DD gespeichert.

Die simple Frage die sich daraus ergibt ist: Geht das? Oder geht das nicht?

Liebe Grüße
und den gleichen sonnigen Himmel den ich hier gerade habe

[af0815]

Es ist für mich auch eine komplexe Fragestellung (in SQLite), die mehr Hintergrundswissen benötigt. Aktuell schau ich mir (wiedereinmal) SQLite genauer an um den Fragen auf den Grund zu gehen. Dank deines Beispiel ist es für mich leichter zu verstehen, wo der Schuh drückt.

Ich muss dazu sagen, das ich bereits schon einmal an den Eigenheiten von SQlite beim Datum gescheitert bin, da ich normalerweise fast nur mit dem MS-SQL (auch unter Linux) arbeite. Deswegen reizt mich das Thema.

[Winni]

Hi!

Wenn Du auf die Bindestriche verzichtest, kannst Du das Datum im Format

YYYYMMDD

auch als integer32 speichern.
Dann stimmt auch die Sortierung.

Winni

[wp_xyz]

Keine gute Idee. Denn mit Integern kann man rechnen. Angenommen, in der Datenbank steht das heutige Datum für ein Rechnungsdatum als Integer mit der vorgeschlagenen Struktur: 20200511. Wenn in einer zusätzlichen Spalte das späteste Zahlungsdatum bei 30 Tagen Zahlungsziel berechnet wird, steht da drin, dass der Kunde die Rechnung spätestens am 41.Mai 2020 zahlen muss.

[Winni]

Hallo!

Ich habe gesagt: Sortieren .

Dass der Kalender wenig mit dem Dezimalsystem zu tun hat, sollte man spätestens in der Grundschule gelernt haben. Vielleicht auch früher.

Winni

PS.: Für Deinen Vorschlag eignet sich der Dezimal-Kalender der französischen Revolution.
Den gb es aber leider nur 12 Jahre.

[af0815]

Wenn dann wird das Datum nur in einem der nativen Formate der DB gespeichert. Alles andere ist ein No-Go. SQLite hat leider keinen dezitierten Typ dazu sondern lässt eine Auswahl aus 3 Basistypen zu (String, Integer und Real), jeder mit Vor und Nachteilen. Dabei ist natürlich auch die Kunst des Treibers gefragt der zwischen der DB und Lazarus steht als auch SQLdb als Interfaceschicht in Lazarus.

Daraus ergibt sich natürlich ein Problem, wenn man in einer Query sich das Datum brauchbar umformt. Der arme Parser intern der Query muss ja dann ein Insert, Update und Delete Statement formen. Wenn ich ich also in der SQL bereits eine Umformung mache, so kann ich da genaugenommen nur verlieren oder muss auf die (oft bequeme) Automatk der Query komplett verzichten und die Statements selbst formulieren.

Bei dem Beispiel von Judas, sind noch ein paar Kleinigkeiten drinnen versteckt die das Leben erfreuen können (sarkastisch gemeint). Die kleine App ist als Beispiel sehr gut, weil genauer untersucht, alles an kleinen Schwierigkeiten IMHO drinnenstecken.

BTW: mit einer anderen SQL-Servertype tretn die Datumsprobleme gar nicht so auf, weil die einen eigene sicheren Typ dafür haben, oft sogar mehrere, die je nach benötigter Genauigkeit ausgewählt werden können.

[m.fuchs]

Wenn Du auf die Bindestriche verzichtest, kannst Du das Datum im Format
YYYYMMDD
auch als integer32 speichern.

Wenn man Datumswerte als Integer speichert und damit Sortieren und Rechnen will ist die Unixzeit das Mittel zur Wahl.
Hat auch den Vorteil, dass der Datenaustausch recht einfach gelingt, praktisch überall gibt es fertige Konvertierungsfunktionen. Unter FP wären das DateTimeToUnix und UnixToDateTime.

Hat den Nachteil, dass bei direkten Blick in die Datenbank, der Datumswert nicht sofort lesbar ist. Ist aber auch nicht so schlimm, innerhalb eines Select kann man auch konvertieren.

Code: Alles auswählen

SELECT datetime(1589264068, 'unixepoch', 'localtime');

Ausgabe:

Code: Alles auswählen

2020-05-12 08:14:28

[af0815]

Hat den Nachteil, dass bei direkten Blick in die Datenbank, der Datumswert nicht sofort lesbar ist. Ist aber auch nicht so schlimm, innerhalb eines Select kann man auch konvertieren.

Code: Alles auswählen

SELECT datetime(1589264068, 'unixepoch', 'localtime');

Ausgabe:

Code: Alles auswählen

2020-05-12 08:14:28

Genau da fangen die Probleme an, wie soll das der SQL-Parser in Insert, Update, Delete umformen ?! Das kann man nur händisch lösen oder das ganze richtig im Dataset umformen. Das geht bei einer Read-Only Query, nicht aber bei einem aktualisierbaren.

SQLite lt Doku siehe https://www.sqlite.org/datatype3.html

Date and Time Datatype
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions

Und in diesem Beispiel geht es um den Text als ISO8601 Format. Das sieht man, wenn man sich die DB des Beispiels ansieht.

Für die Funktionen siehe hier https://www.sqlite.org/lang_datefunc.html , allerdings sollte die richtig verwendet werden. S. o.

[Judas]

Moin, zusammen.

Erst einmal vielen Dank an

af0815
m.fuchs
Winni
wp_xyz

für das intensive Auseinandersetzen mit meiner Fragestellung.

Leider konnte sich niemand dazu hinreißen lassen, mögliche Lösungsansätze nicht nur theoretisch zu erörtern, sondern sie auch gleich in das von mir eingestellte Projekt zu integrieren.

Ich komme also für mich zu dem Resumée: in der von mir gewünschten Form (Beibehaltung der Datumsdarstellung in SQLite in der Form 'YYYY-MM-DD') funktioniert in Verbindung mit Lazarus nicht. Das Datum sollte mit dem Datentyp 'Double' gehalten werden. Damit kann ich leben, auch, wenn es aus meiner Sicht nur die zweitbeste Lösung ist.

@af0815

Schön, dass du ein paar Kleinigkeiten in dem von mir eingestellten Projekt gefunden hast, die das Leben erfreuen können. Das ist in diesen Corona-Zeiten nun wirklich nicht zu verachten. Du darfst mir (und damit auch der Community) anhand von Code-Schnipseln aber auch gerne aufzeigen, wo aus deiner Sicht Verstöße gegen die vielzitierte "Best practice" vorliegen.

Bleibt gesund!
Und liebe Grüße

[af0815]

Das Projekt ein klein wenig umgestellt, nur das mit dem Datum einmal geändert.

Grundlegend:
-> Native Formate der DB verwenden, wenn es anders sein soll, dann Fields auf die DB legen und dort umformen
-> ID nicht über dieselbe Query erzeugen, besser AutoINC, Generatoren oder GUIDs verwenden.

Die Begründung für den ersten Teil habe ich schon früher gegeben.
Deine ID Erzeugung funktioniert zwar für 9999 Einträge, dann wirds problematisch Auch hier gilt, mache es nicht in der DB, sondern formatiere deine Daten so spät wie möglich. Man braucht in der DB keine ID mit führenden Nullen und als String gespeichert - nur damit die blöde Sortierung von Strings funktioniert.

BTW: Ich habe mit dem Beispiel wieder gesehen, wie man sich das Leben sehr schwer machen kann, obwohl es unnötig ist. Es sind noch ein paar Kleinigkeiten drinnen