SQLite und Calculated Columns

Für Themen zu Datenbanken und Zugriff auf diese. Auch für Datenbankkomponenten.
Antworten
Benutzeravatar
Maddias
Lazarusforum e. V.
Beiträge: 32
Registriert: Mo 29. Apr 2019, 09:28
OS, Lazarus, FPC: Windows 10, Lazarus 3.2, FPC 3.2.2
Wohnort: Randwick, NSW, Australien
Kontaktdaten:

SQLite und Calculated Columns

Beitrag von Maddias »

Wir haben ein Problem bei einem Delphi Projekt, in dem wir MS Access als Datenbank durch SQLite ersetzen, damit die Replikation mit PowerSync nach PostgreSQL funktioniert. Da SQLite häufig in der Lazarus Community benutzt wird, dachte ich ich frage hier mal nach.

Abstrahiert ist dies unser Problem:
Folgende Table in SQLite:

Code: Alles auswählen

CREATE TABLE t1(a int, b int);
INSERT INTO t1 values(1,1);
INSERT INTO t1 values(1,0);
Wenn wir folgende Abfrage ausführen...

Code: Alles auswählen

SELECT IIF(b > 0, a / b, NULL) AS c
FROM t1
ORDER BY b DESC
.. ist die Spalte c ein Integer Feld. Ändern wir die Sortierung zu...

Code: Alles auswählen

SELECT IIF(b > 0, a / b, NULL) AS c
FROM t1
ORDER BY b ASC
...ist der Wert für c NULL und der Datentyp der Spalte TWideString.

Auch ein Type Cast hilft nicht weiter:

Code: Alles auswählen

SELECT IIF(b > 0, a / b, Cast(NULL As Float)) AS c
FROM t1
ORDER BY b ASC
Gibt es in SQLite eine Möglichkeit den Datentyp einer berechneten Spalte im Voraus festzulegen?

Recht herzlichen Dank für eine Tipp im Voraus.

Salut,
Mathias

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6661
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: SQLite und Calculated Columns

Beitrag von af0815 »

Versuchs mit einem Subselect, da kann man den Datentyp leichter festlegen. Kann es aktuell am Mobile nur nicht testen.

Edit: NULL kann man nicht casten. Das ist typenlos.
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

Benutzeravatar
Zvoni
Beiträge: 268
Registriert: Fr 5. Jul 2024, 08:26
OS, Lazarus, FPC: Windoof 10 Pro (Laz 2.2.2 FPC 3.2.2)
CPU-Target: 32Bit
Wohnort: BW

Re: SQLite und Calculated Columns

Beitrag von Zvoni »

Was kommt hierbei raus?

Code: Alles auswählen

SELECT 
CAST(IIF(b > 0, CAST(a AS FLOAT)/ b, NULL) AS FLOAT) AS c
FROM t1
ORDER BY b ASC
EDIT: Btw: Wie bewandert bist du mit SQLite resp. SQL im allgemeinen?
Gezeigte Abfrage sieht mir nach nem Kandidaten für "generated Columns" aus (HINWEIS HINWEIS HINWEIS!! :D)

Code: Alles auswählen

CREATE TABLE T1 (
	a int, 
	b int, 
	c float generated always as (iif(b>0,cast(a as float)/b,NULL)) virtual
	)
statt "virtual" kann man auch "stored" nehmen (wäre vielleicht sogar sinnvoller).
Unterschied:
mit "virtual" wird kein Speicher in Anspruch genommen, da c jedesmal bei Aufruf neu berechnet wird.
"stored" braucht Speicher, da die Berechnung von Spalte c bei INSERT/UPDATE statt findet.
Ist am Ende auch eine Performance-Frage (welche am Ende auch nur ihr selbst beantworten könnt).
Macht ihr ein SELECT-Statement, was jedesmal 1 Million Zeilen zurückgibt, wird bei "virtual" auch 1 Million mal Spalte c neu berechnet.
habt ihr "stored" wird c eben nur einmal beim INSERT/jedesmal bei UPDATE neu berechnet.
Und bei "stored" ist dann c wie ne "handelsübliche" Spalte

ist mMn nen test wert, weil wenn ich mich recht entsinne, legt FPC den DatenTyp eines Fields anhand der empfangenen Meta-Daten fest (irgendwas bei direkten CASTS in SELECT-Statements lauft nicht richtig)
Ein System sie alle zu knechten, ein Code sie alle zu finden,
Eine IDE sie ins Dunkel zu treiben, und an das Framework ewig zu binden,
Im Lande Redmond, wo die Windows drohn.

Benutzeravatar
Maddias
Lazarusforum e. V.
Beiträge: 32
Registriert: Mo 29. Apr 2019, 09:28
OS, Lazarus, FPC: Windows 10, Lazarus 3.2, FPC 3.2.2
Wohnort: Randwick, NSW, Australien
Kontaktdaten:

Re: SQLite und Calculated Columns

Beitrag von Maddias »

Danke für die Rückmeldung.

Wir sind hier auf eine sehr elegante Lösung gestoßen:

Code: Alles auswählen

SELECT count(*) as "cnt::INT" FROM mytab
Damit können wir jeder Spalte einen bestimmten Datentyp zuordnen. Ist aber leider ein FireDAC Feature, nicht SQLite.

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6661
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: SQLite und Calculated Columns

Beitrag von af0815 »

Maddias hat geschrieben: Mo 3. Feb 2025, 21:48 Damit können wir jeder Spalte einen bestimmten Datentyp zuordnen. Ist aber leider ein FireDAC Feature, nicht SQLite.
Das ist somit ein Feature das es in SQLdb nicht gibt. Es ist ein Problem wie es die Treiberschicht implementiert - bei embacadreo halt FireDAC. Bei Lazarus hast du die Wahl zwischen SQLdb und ZEOS als Treiberanbindung. Wobei bei solch speziellen Fällen oft ZEOS die Nase vorn hat. Auch weil es sowohl Delphi als auch Lazarus unterstützt und schon sehr lange Erfahrung gesammelt hat.
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

Benutzeravatar
Zvoni
Beiträge: 268
Registriert: Fr 5. Jul 2024, 08:26
OS, Lazarus, FPC: Windoof 10 Pro (Laz 2.2.2 FPC 3.2.2)
CPU-Target: 32Bit
Wohnort: BW

Re: SQLite und Calculated Columns

Beitrag von Zvoni »

Also, ich hab nochmal ein wenig geforscht.

Bei Tabellen, welche Generated Columns enthalten (Siehe mein Beispiel oben),
wirft das PRAGMA table_xinfo(Tabellenname) wie folgt aus (Beispielhaft)

Code: Alles auswählen

cid	name	type	notnull	dflt_value	pk	hidden
0	a	INT	0		0	0
1	b	INT	0		0	0
2	c	float	0		0	2
An deiner Stelle würde ich es einfach mal testen, ob du nen Datentyp für "c" zurückbekommst.
Ich hab mal in den Quellcode von sqlite3conn geschaut (AddFieldDefs-Methode),
und für alle "unbekannten" Datentypen für Spalten wird default "String" zugewiesen
Ein System sie alle zu knechten, ein Code sie alle zu finden,
Eine IDE sie ins Dunkel zu treiben, und an das Framework ewig zu binden,
Im Lande Redmond, wo die Windows drohn.

Benutzeravatar
Zvoni
Beiträge: 268
Registriert: Fr 5. Jul 2024, 08:26
OS, Lazarus, FPC: Windoof 10 Pro (Laz 2.2.2 FPC 3.2.2)
CPU-Target: 32Bit
Wohnort: BW

Re: SQLite und Calculated Columns

Beitrag von Zvoni »

Gibts hierzu ein Feedback?
Dürfte auch für andere interessant sein
Ein System sie alle zu knechten, ein Code sie alle zu finden,
Eine IDE sie ins Dunkel zu treiben, und an das Framework ewig zu binden,
Im Lande Redmond, wo die Windows drohn.

Antworten