ubuntuusers.de

Hallo zusammen!

Wie hier bereits angedroht versuche ich mich an der Entwicklung einer QML-Anwendung (Ziel: Plasmoid und App). Derzeit ist da noch vieles von Theorie, die Datenbankfunktionen sind aber nahezu fertig. Hatte mir dafür etwas "leichtes" ausgesucht, was aber doch irgendwie größere Dimensionen angenommen hat 😀

Verwendungszweck:

• Einbuchen von Items ( Zigaretten, Kaffees, Kaugummis, gefrühstückte Kinder, whatever. ) zur Erfassung von Verbrauch und Kosten.

Ablauf:

• Bei jedem Klick auf ein Item wird eine Buchung in die DB (SQLite) geschrieben.

• Die Übersicht zeigt einem eine Liste mit Gesamtverbräuchen (Jahr, Monat, Tag, letzter Zeitstempel)

  • Zur Zeit ist die Übersicht realisiert über temporäre Tabellen

Problem:

• Bei jeder Buchung müssen alle (statistisch relevanten) Werte angepasst werden.

Frage: Wie setze ich das am sinnvollsten um? An sich ist die DB-Klasse so konzipiert, dass sie sich nach X Sekunden selbständig schließt (mittels Timer), da man ja nicht ununterbrochen irgendwas einträgt. Vorteil ist klar, die Anwendung verbrät nicht unnötig Ressourcen und beschränkt sich auf das Anzeigen bereits vorhandener Daten. Der Nachteil wäre, dass man bei jedem Klick auf eine Buchung die Datenbank öffnen, die temporären Tabellen erstellen, den Eintrag tätigen, die temporären Tabellen aktualisieren und neu über die c++-Models ans QML weiterreichen muss. Mir erscheint das massiv aufwändig.

Die erste Alternative wäre bei der Initialisierung der App die Werte auszulesen und anschließend bei Buchungen alle vorhandenen Werte in den Modellen separat anzupassen.

Die zweite Alternative wäre die DB einfach offen zu halten, so dass lediglich die Aktualisierten Modelle geladen und übertragen werden müssen.

Meine Vermutung geht in dem Fall in Richtung der zweiten Alternative, da salopp gesagt, eine solche SQLite-DB auch nur eine offen gehaltene Datei von ein paar kilobyte ist. Vermutlich ist diese komplett im Speicher hinterlegt noch kleiner, als die ganzen Funktionen, die zur Anpassung nötig wären...Hochgerechnet sind das für ein Jahr ein halbes MB Dateigröße.

Da ich das ja aber auch hauptsächlich aus Lernzwecken mache, wüsste ich gerne, wie man sowas "richtig denkt". Danke!

ChickenLipsRfun2eat schrieb:

Ablauf:

• Bei jedem Klick auf ein Item wird eine Buchung in die DB (SQLite) geschrieben.

• Die Übersicht zeigt einem eine Liste mit Gesamtverbräuchen (Jahr, Monat, Tag, letzter Zeitstempel)

  • Zur Zeit ist die Übersicht realisiert über temporäre Tabellen

Wozu das? Da reicht doch ein oder mehrere Views.

Problem:

• Bei jeder Buchung müssen alle (statistisch relevanten) Werte angepasst werden.

Wieso denn das? Du trägst jede Buchung einzeln ein und machst einfach eine Ad-Hoc-Abfrage, wenn Du die entsprechende aggregierte Daten anzeigen willst (siehe "GROUP BY"). Bei Deinem geringen Datenvolumen ist überhaupt nicht zu vertreten, dass Du die Daten redundant hältst.

Frage: Wie setze ich das am sinnvollsten um? An sich ist die DB-Klasse so konzipiert, dass sie sich nach X Sekunden selbständig schließt (mittels Timer), da man ja nicht ununterbrochen irgendwas einträgt.

Nicht machen - schon gar nicht in der DBKlasse (was auch immer die genau macht).

Vorteil ist klar, die Anwendung verbrät nicht unnötig Ressourcen und beschränkt sich auf das Anzeigen bereits vorhandener Daten. Der Nachteil wäre, dass man bei jedem Klick auf eine Buchung die Datenbank öffnen, die temporären Tabellen erstellen, den Eintrag tätigen, die temporären Tabellen aktualisieren und neu über die c++-Models ans QML weiterreichen muss. Mir erscheint das massiv aufwändig.

Die Ressourcen, von denen Du sprichst, dürften minimale Mengen an Speicher sein. Die Variante, die ständig die DB wieder öffnet, braucht sicherlich mehr CPU und IO und dürfte auch langsameres Antwortverhalten zeigen. Um die Konsistenz der DB brauchst Du Dir keine Sorgen zu machen: wenn die Transaktion abgeschlossen ist, sind die Daten auch persistent - selbst, wenn danach Dein Programm abstürzt.

Die erste Alternative wäre bei der Initialisierung der App die Werte auszulesen und anschließend bei Buchungen alle vorhandenen Werte in den Modellen separat anzupassen.

Die zweite Alternative wäre die DB einfach offen zu halten, so dass lediglich die Aktualisierten Modelle geladen und übertragen werden müssen.

Besser. Aber s.o.

Meine Vermutung geht in dem Fall in Richtung der zweiten Alternative, da salopp gesagt, eine solche SQLite-DB auch nur eine offen gehaltene Datei von ein paar kilobyte ist. Vermutlich ist diese komplett im Speicher hinterlegt noch kleiner, als die ganzen Funktionen, die zur Anpassung nötig wären...Hochgerechnet sind das für ein Jahr ein halbes MB Dateigröße.

Das ist ja lächerlich wenig.

Hallo,

Zur Zeit ist die Übersicht realisiert über temporäre Tabellen

Warum temporär? Bzw. warum kein normaler Query gegen die normale Tabelle? DBs betreiben Optimierung, Caching etc normalerweise intern - da muss man sich als Nutzer nicht reinmischen und unnötige temporäre Tabellen bauen.

Frage: Wie setze ich das am sinnvollsten um?

QML arbeitet doch auch Event-basiert, oder? Dann wäre es doch IMHO am sinnvollsten, nach jedem DB-Write ein Event auszulösen (bzw. vielleicht generiert der "successful wrtite" so wie so eins?) und die Aktualisierung der Statistik wird durch dieses Event getriggert.

Gruß, noisefloor

rklm schrieb:

Wozu das? Da reicht doch ein oder mehrere Views.

👍 ReadOnly reicht! Das kannte ich noch gar nicht ☺

Problem:

• Bei jeder Buchung müssen alle (statistisch relevanten) Werte angepasst werden.

Wieso denn das? Du trägst jede Buchung einzeln ein und machst einfach eine Ad-Hoc-Abfrage, wenn Du die entsprechende aggregierte Daten anzeigen willst (siehe "GROUP BY"). Bei Deinem geringen Datenvolumen ist überhaupt nicht zu vertreten, dass Du die Daten redundant hältst.

Wieso? Weil die Übersicht ja alle Mengen und Werte in Summe enthält. Füge ich eine Buchung hinzu, muss ich alle anpassen. Bspw. für die Mengen: Gesamtmenge, Jahresmenge, Monatsmenge, Tagesmenge erhöht sich um jeweils 1. Dies geschieht dann ja mittels einer erneuten Abfrage, die mir die Übersichtstabelle wieder ausspuckt.

Bei offen bleibender Datenbank, wäre das wenig aufwand, da noisefloor schrieb:

Frage: Wie setze ich das am sinnvollsten um?

QML arbeitet doch auch Event-basiert, oder? Dann wäre es doch IMHO am sinnvollsten, nach jedem DB-Write ein Event auszulösen (bzw. vielleicht generiert der "successful wrtite" so wie so eins?) und die Aktualisierung der Statistik wird durch dieses Event getriggert.

sich genau dadurch das verwendete Modell selbst aktualisiert, bzw. per Signal&Slot-Konzept QML die Werte aktualisiert. Ich emitte ein "itemUpdate" oder "entryUpdate", wenn sich was an der jeweiligen Tabelle getan hat, so dass automagisch durch den entsprechenden SLOT die Daten wieder aktuell abgefragt werden. Die Modelle (hab derzeit QSQLTableModel und eine eigene Version mittels QList<eigenes struct> versucht) müssen ja eh bereitgestellt bleiben, da QML diese anzeigt.

Als Beispiel würde ich ein einmaliges Modell abfragen, welches letzten Endes eine kleine Gesamttabelle enthält (mittels GROUP BY). Dies passiert beim Start und ist das Ergebnis der temporären Tabelle.

Diese Tabelle wird als c++-Model für QML vorbehalten und entsprechend der delegates aufbereitet durch eine view (Liste, Tabelle,etc.) auf den Schirm gebracht. Dazu muss aber die DB logischerweise offen bleiben.

Nicht machen - schon gar nicht in der DBKlasse (was auch immer die genau macht).

Die DB-Klasse öffnet die DB, richtet ggf. die Tabellen ein und ist für jeden Eintrag und jedes Auslesen zuständig. Quasi alles was einen SQL-Befehl enthält steckt da drin. Falls ich mal ein nutzbares Erstergebnis habe, lade ich das irgendwo hoch und lasse mich dann anhand echten codes beraten ☺

Die Ressourcen, von denen Du sprichst, dürften minimale Mengen an Speicher sein. Die Variante, die ständig die DB wieder öffnet, braucht sicherlich mehr CPU und IO und dürfte auch langsameres Antwortverhalten zeigen. Um die Konsistenz der DB brauchst Du Dir keine Sorgen zu machen: wenn die Transaktion abgeschlossen ist, sind die Daten auch persistent - selbst, wenn danach Dein Programm abstürzt.

Wahrscheinlich hast du Recht. Wobei ich bei ggf. einer oder zwei Buchungen pro Stunde schon einen Sinn drin sehe die DB zuzumachen. Andererseits ist das aufm Rechner auch wieder völlig egal und als App macht man das Ding eh zu und lässt es nicht im Hintergrund laufen...selbst wenn...passiert ja "nix", wenn man nix macht.

...Hochgerechnet sind das für ein Jahr ein halbes MB Dateigröße.

Das ist ja lächerlich wenig.

Ja. Das stimmt. Vllt habe ich mich auch verschätzt und es wächst enorm auf das Doppelte an Größe an... wer weiß ☺

noisefloor schrieb:

Zur Zeit ist die Übersicht realisiert über temporäre Tabellen

Warum temporär? Bzw. warum kein normaler Query gegen die normale Tabelle? DBs betreiben Optimierung, Caching etc normalerweise intern - da muss man sich als Nutzer nicht reinmischen und unnötige temporäre Tabellen bauen.

Weil ich zu doof bin einen Query zu schreiben, der mehrere Zeiträume abdeckt. Ich hatte bei sqlite das Problem, dass ich nicht zu viele verschachtelte SELECT hinbekommen habe und ich brauche ja ein wenig Kalkulation. Vielleicht verstehe ich es auch nur grundlegend falsch ☺

Die Beispieltabellen erstelle ich so: (teilweise ungetestet)

CREATE TABLE IF NOT EXISTS items(name TEXT, value REAL NOT NULL DEFAULT 0.0, iconPath TEXT DEFAULT '', groupID INTEGER DEFAULT 0,updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP, deleted INTEGER DEFAULT 0 );
CREATE TABLE IF NOT EXISTS booking(itemRow INTEGER, value REAL NOT NULL, updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP, tim TIMESTAMP DEFAULT CURRENT_TIMESTAMP, deleted INTEGER DEFAULT 0);
CREATE TABLE IF NOT EXISTS groups(name TEXT DEFAULT 'Group', updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP, deleted INTEGER DEFAULT 0);
CREATE TRIGGER IF NOT EXISTS update_items_trigger AFTER UPDATE ON items BEGIN UPDATE items SET updated=datetime('now') WHERE id=NEW.id;END;
CREATE TRIGGER IF NOT EXISTS update_entry_trigger AFTER UPDATE ON items BEGIN UPDATE bookings SET updated=datetime('now') WHERE id=NEW.id;END;
INSERT INTO groups(name) VALUES('Coffee');
INSERT INTO items(name, value, groupID) VALUES('Coffee', 2.00, 1);
INSERT INTO items(name, value, groupID) VALUES('Coffee(dispenser)', 0.70, 1);
INSERT INTO items(name, value) VALUES('Cigarette', 0.2857);

Mittels einiger Test-Buchungen kam ich dann auf folgende Abfragen für die Übersicht:

#Jahresübersicht:
SELECT (SELECT name FROM items WHERE ROWID=itemRow) as Name, COUNT(itemRow) as FullAmount, SUM(value) as FullTotal, MAX(tim) as LastOne FROM booking GROUP BY itemRow;
#...
#Monatsübersicht
SELECT (SELECT name FROM items WHERE ROWID=itemRow) as Name, COUNT(itemRow) as MonthAmount, SUM(value) as MonthTotal FROM booking WHERE tim>=(SELECT(datetime('now','start of month'))) GROUP BY itemRow;
#...

Eine weitere Verschachtelung mittels direktem JOIN habe ich nicht geschrieben bekommen, daher bin ich auf die temporären Tabellen ausgewichen.

Auch hier sind Verbesserungsvorschläge gern gesehen ☺

Vielen Dank zunächst für die Antworten!

Hallo,

in deinem DB-Entwurf fehlen doch dir Relationen? Wenn ich das richtig verstehe, sollen doch bei items.name nur Werte zulässig sein, die in groups stehen (übrigens nimmt man üblicherweise Namen in der Einzahl, weil jede Zeile _ein_ Item bzw. eine Group ist). Dann fällt auch das 1. verschachtelte SELECT im Query weg.

Weil ich zu doof bin einen Query zu schreiben, der mehrere Zeiträume abdeckt.

Bei SQL kennst die bei WHERE die Konditonen mit OR und / oder AND verknüpfen.

Kannst man bei Qt bzw. QML SQL-Queries nicht parametrisieren? Wahrscheinlich schon, oder? Also dass du z.B. im Query einen Platzhalter hast, denn du vor der eigentlichen Wert befüllst. Deine beiden gezeigten Queries sind ja letztendlich sehr ähnlich.

Gruß, noisefloor

Hallo!

Ja, die Tabelle group(s) macht noch keinen Sinn. Derzeit könnte man ein weiteres Textfeld in item(s) eintragen und dort steht entweder eine Gruppe drin, oder eben nicht. War eine spontane Idee das mit einzubauen und dementsprechend sind Gruppen noch reine Textinformationen. So ist es aber leichter erweiterbar ☺

Bei Qt kann man Platzhalter setzen, klar. Im einfachen Query bspw mittels

QSQLQuery q;
q.prepare("INSERT INTO table SET field=:content WHERE id=:id");
q.bindValue(":id:",99);
q.bindValue(":content", "Feldinhalt");
if(!q.exec())...

Bei Models geht das über "Filter", in etwa so:

SQLDBTableModel *model = new SQLDBTableModel;
model->setTable("items");
if(!showDeleted) model->setFilter("deleted=0");
model->select();

Filter sind dabei theoretisch alles, was nach WHERE kommt/kommen kann. Also ein "1=1 AND deleted=0 GROUP BY whatever" wäre auch ein gültiger Filter.

Ich werde meine Tabellennamen auf jeden Fall in die Einzahl bringen. Macht ja durchaus Sinn.

Ich habe aber leider nicht verstanden, wie ich damit die Verschachtelung einsparen soll. Diese ist ja an sich nur da, damit die Namen zurückgegeben werden anstatt der ID des items. Fehlt noch etwas an Erklärungen, wie ich gedacht habe um da durchzusteigen? Dann kann ich die Idee dahinter gerne ausarbeiten.

Hallo,

das erste verschachtelte SELECT eliminierst du, indem du a) die Tabellen untereinander in Beziehung setzt ("Foreign Key") und b) dann einen JOIN bei der Abfrage machst.

Die Relation kannst du auch im Model machen (http://doc.qt.io/qt-5/qsqlrelationaltablemodel.html) - keine Ahnung, ob Qt dann beim Query so schlau ist, die Relation automatisch aufzulösen.

Gruß, noisefloor

ChickenLipsRfun2eat schrieb:

rklm schrieb:

Problem:

• Bei jeder Buchung müssen alle (statistisch relevanten) Werte angepasst werden.

Wieso denn das? Du trägst jede Buchung einzeln ein und machst einfach eine Ad-Hoc-Abfrage, wenn Du die entsprechende aggregierte Daten anzeigen willst (siehe "GROUP BY"). Bei Deinem geringen Datenvolumen ist überhaupt nicht zu vertreten, dass Du die Daten redundant hältst.

Wieso? Weil die Übersicht ja alle Mengen und Werte in Summe enthält. Füge ich eine Buchung hinzu, muss ich alle anpassen. Bspw. für die Mengen: Gesamtmenge, Jahresmenge, Monatsmenge, Tagesmenge erhöht sich um jeweils 1. Dies geschieht dann ja mittels einer erneuten Abfrage, die mir die Übersichtstabelle wieder ausspuckt.

Du musst nichts anpassen! Du musst nur die Abfragen passend formulieren. Wenn Du alle einzelnen Buchungen mit Zeitstempel in der DB hast, kannst Du aggregierte Werte pro Tag, Monat, Jahr und gesamt abfragen. Ich glaube, Du solltest mal ein SQL-Tutorial durcharbeiten.

Nicht machen - schon gar nicht in der DBKlasse (was auch immer die genau macht).

Die DB-Klasse öffnet die DB, richtet ggf. die Tabellen ein und ist für jeden Eintrag und jedes Auslesen zuständig. Quasi alles was einen SQL-Befehl enthält steckt da drin. Falls ich mal ein nutzbares Erstergebnis habe, lade ich das irgendwo hoch und lasse mich dann anhand echten codes beraten ☺

Was uU mehr Aufwand für Dich bedeutet. ☺

...Hochgerechnet sind das für ein Jahr ein halbes MB Dateigröße.

Das ist ja lächerlich wenig.

Ja. Das stimmt. Vllt habe ich mich auch verschätzt und es wächst enorm auf das Doppelte an Größe an... wer weiß ☺

Immer noch lächerlich wenig.

Auch hier sind Verbesserungsvorschläge gern gesehen ☺

Da muss ich mangels Zeit gerade passen.

Hallo,

im folgenden Mail ein kleines Beispiel:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

sqlite> PRAGMA foreign_keys=1;
sqlite> CREATE TABLE consumable (
   ...> consumable_id INTEGER PRIMARY KEY,
   ...> consumable_text TEXT);
sqlite> INSERT INTO consumable(consumable_text) VALUES ('coffee');
sqlite> INSERT INTO consumable(consumable_text) VALUES ('fast-food');
sqlite> SELECT * FROM consumable;
1|coffee
2|fast-food
sqlite> CREATE TABLE consumption (
   ...> consumption_id INTEGER PRIMARY KEY,
   ...> location TEXT,
   ...> consumption_date DATETIME DEFAULT CURRENT_TIMESTAMP,
   ...> consumable_id NOT NULL REFERENCES consumable);
sqlite> INSERT INTO consumption(location, consumable_id) VALUES ('vending machine', 1);
sqlite> INSERT INTO consumption(location, consumable_id) VALUES ('Currywurst Palace', 2);
sqlite> INSERT INTO consumption(location, consumption_date, consumable_id) VALUES ('bakery', '2015-09-10 10:11:12:13' , 1);
sqlite> SELECT * FROM consumption;
sqlite> INSERT INTO consumption(location, consumable_id) VALUES('Zur Gabel', 4);
Error: FOREIGN KEY constraint failed
1|vending machine|2016-09-14 14:32:53|1
2|Currywurst Palace|2016-09-14 14:33:32|2
3|bakery|2015-09-10 10:11:12:13|1
sqlite> SELECT location, consumption_date FROM consumption JOIN consumable USING(consumable_id) WHERE consumable_id=1;
vending machine|2016-09-14 14:32:53
bakery|2015-09-10 10:11:12:13
sqlite> SELECT location, consumption_date FROM consumption JOIN consumable USING(consumable_id) WHERE consumable_id=1 AND strftime('%Y', consumption_date)='2016';
vending machine|2016-09-14 14:32:53
sqlite> SELECT consumable.consumable_text, consumption.location, consumption.consumption_date FROM consumption JOIN consumable USING(consumable_id) WHERE consumable_id=1;
coffee|vending machine|2016-09-14 14:32:53
coffee|bakery|2015-09-10 10:11:12:13
sqlite> SELECT consumable.consumable_text, consumption.location, consumption.consumption_date FROM consumption JOIN consumable USING(consumable_id) WHERE consumable_text='coffee';
coffee|vending machine|2016-09-14 14:32:53
coffee|bakery|2015-09-10 10:11:12:13

Das mit den One-to-Many Relations und JOIN ... USING ist im Prinzip ganz einfach.

Gruß, noisefloor

rklm schrieb:

Du musst nichts anpassen! Du musst nur die Abfragen passend formulieren. Wenn Du alle einzelnen Buchungen mit Zeitstempel in der DB hast, kannst Du aggregierte Werte pro Tag, Monat, Jahr und gesamt abfragen. Ich glaube, Du solltest mal ein SQL-Tutorial durcharbeiten.

Naja, ich passe nicht manuell an, sondern ich muss eben die Anzeige aktualisieren (lassen). Das funktioniert ja dann automatisch, bedeutet aber einen Zugriff auf die DB.

Was uU mehr Aufwand für Dich bedeutet. ☺

..mir aber Fehler aufzeigt, die ich alleine nicht finden würde. Auch wenn es funktioniert, gibt es was zu verbessern und wenn man mir dann ein paar Brocken an den Kopf wirft, kann ich mich gezielt mit dem Thema im Hinterkopf in Neues einarbeiten. Alles in Theorie mal zu lesen mag einigen reichen, ich muss das anwenden, damit es haften bleibt.

Auch hier sind Verbesserungsvorschläge gern gesehen ☺

Da muss ich mangels Zeit gerade passen.

Schade, aber kann ich verstehen. Es eilt ja nicht. Ich lerne das ja "nebenbei" und brauche es für nix.

noisefloor schrieb:

das erste verschachtelte SELECT eliminierst du, indem du a) die Tabellen untereinander in Beziehung setzt ("Foreign Key") und b) dann einen JOIN bei der Abfrage machst.

Gut. Nach ein wenig rumsuchen kam ich auf folgende Lösung:

SELECT itemRow, name, tim FROM booking LEFT OUTER JOIN items ON booking.itemRow=items.ROWID;

als Beispiel für eine Vermeidung des zusätzlichen SELECT.

Das FOREIGN KEY...REFERENCES erschien mir etwas zu komplex für die Abfrage, muss ich mir aber noch durchlesen.

Was ich noch nicht geschafft habe, ist es alle Abfragen mit den unterschiedlichen WHERE-Klauseln in eine zu bekommen, was aber vermutlich an den CONT/SUM-Funktionen liegt.

Die Relation kannst du auch im Model machen (http://doc.qt.io/qt-5/qsqlrelationaltablemodel.html) - keine Ahnung, ob Qt dann beim Query so schlau ist, die Relation automatisch aufzulösen.

Das verwende ich auch und ja, es ist so schlau oder meine Tabellen einfach genug, bzw. man übergibt dem Model ja ein QRelation(x,y,z) um das aufzulösen.

Aber weg vom Qt. Ich denke ich benötige erstmal optimierte Queries für die Datenbank. Diese funktionieren ja unabhängig von Qt.

Hallo,

Das FOREIGN KEY...REFERENCES erschien mir etwas zu komplex für die Abfrage, muss ich mir aber noch durchlesen.

Nope - das ist Standard bei SQL beim Erstellen von Tabellen und Relationen. Das hat mit komplex nichts zu tun.

Aber weg vom Qt. Ich denke ich benötige erstmal optimierte Queries für die Datenbank. Diese funktionieren ja unabhängig von Qt.

Kommt drauf an. Ich weiß nicht, was Qt so bietet, aber wenn du so was wie ein ORM oder irgendwelche "higher-level Functions" bzw. Klassen nutzen kann - dann mach es.

Ich selber nutzt auch SQLite für diverse (Python Web-) Projekt - aber greife darauf nur über den ORM von Django zu. Ist deutlich komfortabler als nacktes SQL.

Gruß, noisefloor

Danke. Dann spiele ich damit etwas herum, bis mir das nackte SQL besser liegt. Das Verpacken in Qt ist danach ja recht einfach. Danke schonmal bis hier! Ich werde mich am Wochenende dran setzen können ☺

Kleines Update: Abfrage als "Einzelstück" ohne temporäre Tabellen hinbekommen. Jetzt gucke ich mir die foreign keys an ☺

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

SELECT  name,
        SUM(b1.value) AS Alltime,
        IfNull( (SELECT sum(b2.value)
                 FROM booking AS b2
                 WHERE tim>=datetime('now','start of year')
                 AND b2.itemRow=b1.itemRow
                 GROUP BY itemRow
                 ),
                0) AS YearTotal,

        IfNull( (SELECT sum(b2.value)
                 FROM booking AS b2
                 WHERE tim>=datetime('now','start of month')
                 AND b2.itemRow=b1.itemRow
                 GROUP BY itemRow
                 ),
                0) AS MonthTotal,

        IfNull( (SELECT sum(b2.value)
                 FROM booking AS b2
                 WHERE tim>=datetime('now', 'weekday 1', '-7 days')
                 AND b2.itemRow=b1.itemRow
                 GROUP BY itemRow
                 ),
                0) AS WeekTotal,

        IfNull( (SELECT sum(b2.value)
                 FROM booking AS b2
                 WHERE tim>=datetime('now','weekday 1', '-14 days')
                 AND tim<datetime('now','weekday 1', '-7 days')
                 AND b2.itemRow=b1.itemRow
                 GROUP BY itemRow
                 ),
                0) AS LastWeekTotal,

        IfNull( (SELECT sum(b2.value)
                 FROM booking AS b2
                 WHERE tim>=datetime('now','start of day')
                 AND b2.itemRow=b1.itemRow
                 GROUP BY itemRow
                 ),
                0) AS DayTotal

FROM booking AS b1
JOIN items AS i ON i.rowid=b1.itemRow
GROUP BY name

ChickenLipsRfun2eat schrieb:

Kleines Update: Abfrage als "Einzelstück" ohne temporäre Tabellen hinbekommen. Jetzt gucke ich mir die foreign keys an ☺

Die helfen auf jeden Fall, die Konsistenz zu sichern und das Schema zu dokumentieren. Apropos: poste doch mal das DDL-Skript, damit wir sehen können, wie genau Dein Schema aussieht.

1 2	SELECT name, ...

Ich finde das nicht so schön, weil potentiell die geschachtelten Abfragen für jeden Wert von "name" einzeln ausgeführt werden. Das muss allerdings nicht heißen, dass das langsam ist. Ich würde das entweder mit Inline-Views oder Common Table Expressions machen und dann über "name" joinen.

Auch erscheint mit "IfNull" überflüssig, da Du ja einen Self-Join machst und damit davon ausgehen kannst, dass gerade nicht NULL herauskommt.

Mit Inline Views:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

SELECT	b1.name,
	SUM(b1.value) AS Alltime,
	b2.value AS AllYear,
	b3.value AS MonthTotal,
	b4.value AS WeekTotal,
	b5.value AS DayTotal
FROM booking AS b1
JOIN (
	SELECT name, sum(value) AS value
	FROM booking
	WHERE tim >= datetime('now', 'start of year')
	GROUP BY name
) AS b2 ON b1.name = b2.name
JOIN (
	SELECT name, sum(value) AS value
	FROM booking
	WHERE tim >= datetime('now', 'start of month')
	GROUP BY name
) AS b3 ON b1.name = b3.name
JOIN (
	SELECT name, sum(value) AS value
	FROM booking
	WHERE tim >= datetime('now', 'weekday 1', '-7 days')
	GROUP BY name
) AS b4 ON b1.name = b4.name
JOIN (
	SELECT name, sum(value) AS value
	FROM booking
	WHERE tim >= datetime('now', 'start of day')
	GROUP BY name
) AS b5 ON b1.name = b5.name
GROUP BY b1.name

PS: Ausprobiert mit diesem Schema:

1

CREATE TABLE booking ( name varchar(100) not null, value integer not null, tim timestamp not null );

Das Schema ist derzeit noch so wie oben beschrieben das der Testdatenbank, da ich mit dieser gerade am rumspielen bin. Ich habe das eigentliche Projekt erstmal liegen lassen, bis ich die SQL-Teile so weit habe, dass ich zufrieden bin, daher noch nichts geändert. Da sqlite leider keine Funktionen unterstützt, so wie ich gelesen habe, werde ich dieses Schema erst anpassen, wenn ich meine alles soweit verstanden zu haben und mir dann eine Funktion zum befüllen der DB mit zufälligen Werten ins Programm schreiben.

Also derzeit noch gültig:

1
2
3
4
5
6
7

BEGIN TRANSACTION;
CREATE TABLE items(name TEXT, value REAL NOT NULL DEFAULT 0.0, iconPath TEXT DEFAULT '', groupID INTEGER DEFAULT 0,updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP, deleted INTEGER DEFAULT 0 );
CREATE TABLE groups(name TEXT DEFAULT 'Group', updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP, deleted INTEGER DEFAULT 0);
CREATE TABLE booking(itemRow INTEGER, value REAL NOT NULL, updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP, tim TIMESTAMP DEFAULT CURRENT_TIMESTAMP, deleted INTEGER DEFAULT 0);
CREATE TRIGGER update_items_trigger AFTER UPDATE ON items BEGIN UPDATE items SET updated=datetime('now') WHERE id=NEW.id;END;
CREATE TRIGGER update_entry_trigger AFTER UPDATE ON items BEGIN UPDATE bookings SET updated=datetime('now') WHERE id=NEW.id;END;
COMMIT;

Da ich kein NOT NULL verwendet habe, brauche ich wohl die IfNull-Abfrage. Sonst endet das gruppieren in einem Einzeiler oder einer leeren Tabelle, wenn eine der Abfragen keinen Wert zurückgibt, weil z.B. am aktuellen Tag noch keine Buchung in einem der items stattfand. Da müsste ich dann wohl mit LEFT OUTER JOIN ran, vermutlich oder besser bei dem neuen Schema einfach das NOT NULL verwenden ☺

rklm schrieb:

Ich finde das nicht so schön, weil potentiell die geschachtelten Abfragen für jeden Wert von "name" einzeln ausgeführt werden. Das muss allerdings nicht heißen, dass das langsam ist. Ich würde das entweder mit Inline-Views oder Common Table Expressions machen und dann über "name" joinen.

Geschwindigkeit kann ich wohl erst mit einer befüllten DB testen. Derzeit spielt sich alles im Bereich von maximal 4ms ab.

Das mit den Inline-Views habe ich (hoffentlich) verstanden und hilft mir auf jeden Fall den Fehler des "only a single result allowed" bei mehrdimensionalen Ergebnissen zu vermeiden und die Daten sinnvoll in eine Tabelle zu bekommen. Vereinfacht es mir auch unheimlich das Modell dann später weiterzuverwenden, weil ich eben nicht durch x Ebenen loopen muss ☺ Danke!

Deine Version speichert den Namen direkt in der Buchungstabelle. Ist sicherlich in dem Fall wieder mal irrelevant, da sich die Größe ja in Grenzen hält. Aber würdest du das auch bei einer großen Datenbank so machen? Namensänderungen kann man in beiden Versionen gut umsetzen, falls fällig, nur die Namen abfragen geht ja auch mittels SELECT name FROM booking GROUP BY name;, um bspw. eine ComboBox zu befüllen. Ich habe dies halt ausgelagert, da ich zu den items weitere Infos wie ein Icon oder eben die Gruppenzugehörigkeit speichern will. Das würde die eigentliche Buchungstabelle ja unnötig aufblähen, oder regelt sqlite das intern so gut, dass es keinen Unterschied machen würde die Daten hundertfach zu speichern? Alternativ: Sollte ich anstatt mit der ID mit einem eindeutigen Namen als "primary key" arbeiten?

Als ich damals mal mit MySQL gespielt habe, hieß es bei den Tutorials immer, man müsse die Daten "atomisieren". Daher bin ich immer versucht alles in extra Tabellen auszulagern, was nicht unmittelbar in der "Haupttabelle" benötigt wird.

• items enthält somit das für den Menschen relevante. Einen lesbaren Namen, ggf. ein Icon, eine Gruppenzugehörigkeit für statistische Zwecke.

• groups enthält den lesbaren Namen der Gruppe, (zukünftig) ein Gruppensymbol und ggf. noch weitere Nicklichkeiten

• booking ist die eigentliche Datentabelle. Diese enthält nur das, was sie muss, weil dort ja der Bärenanteil an Informationen liegt.

  • value wird gespeichert, da sich die Preise ja mit der Zeit anpassen werden

  • tim benötige ich als Zeitstempel. Jeder Zeitstempel, bzw. jede Zeile bedeutet ja eine Buchung, welche wiederum mit COUNT(itemRow) eine Gesamtmenge ergibt und nur durch das Speichern der Zeit auswertbar wird. Sonst gäbe es nur eine Gesamtmenge seit Startdatum.

Die Felder updated und deleted sind der in ferner Zukunft geplanten Synchronisation mit anderen Datenbanken geschuldet, wie du dich sicher erinnerst 😉 Bei deleted bin ich mir allerdings auch noch nicht wirklich sicher, ob ich das so mache oder nicht doch etwas komplexer (deleted als weiteren Zeitstempel, bei "Jahresabschluss" oder so endgültig löschen), damit Einträge auch wirklich gelöscht werden können, wenn die Daten in allen DB's auf gelöscht stehen. Das ist allerdings ein extra Thema und gehört zur noch nicht geplanten Sync.

Danke dir schonmal für deine Zeit! Falls am Konzept ein Denkfehler ist oder ich grundlegend was falsch verstanden habe, weise mich gern darauf hin. Anders lern ich ja nix mit meinem Hasenhirn 😀

ChickenLipsRfun2eat schrieb:

Das Schema ist derzeit noch so wie oben beschrieben das der Testdatenbank, da ich mit dieser gerade am rumspielen bin.

Ah, OK.

Ich habe das eigentliche Projekt erstmal liegen lassen, bis ich die SQL-Teile so weit habe, dass ich zufrieden bin, daher noch nichts geändert.

Das ist sinnvoll: erst einmal über die Domäne und das Datenmodell klar werden.

Da sqlite leider keine Funktionen unterstützt, so wie ich gelesen habe, werde ich dieses Schema erst anpassen, wenn ich meine alles soweit verstanden zu haben und mir dann eine Funktion zum befüllen der DB mit zufälligen Werten ins Programm schreiben.

So etwas kannst Du ja auch mit einem Shell-Skript und dem Kommandozeilenprogramm sqlite3 machen oder in einer Skriptsprache mit SQLite-Unterstützung. Das würde ich nicht ins Programm einbauen.

Da ich kein NOT NULL verwendet habe, brauche ich wohl die IfNull-Abfrage.

Die Frage ist, sind NULL-Werte an den Stellen sinnvoll? Insbesondere bei "booking.itemRow" erscheint mir das nicht so, denn das ist ja - wenn auch nicht als solches deklariert - ein Foreign Key.

rklm schrieb:

Ich finde das nicht so schön, weil potentiell die geschachtelten Abfragen für jeden Wert von "name" einzeln ausgeführt werden. Das muss allerdings nicht heißen, dass das langsam ist. Ich würde das entweder mit Inline-Views oder Common Table Expressions machen und dann über "name" joinen.

Das mit den Inline-Views habe ich (hoffentlich) verstanden und hilft mir auf jeden Fall den Fehler des "only a single result allowed" bei mehrdimensionalen Ergebnissen zu vermeiden und die Daten sinnvoll in eine Tabelle zu bekommen. Vereinfacht es mir auch unheimlich das Modell dann später weiterzuverwenden, weil ich eben nicht durch x Ebenen loopen muss ☺ Danke!

Bitte!

Deine Version speichert den Namen direkt in der Buchungstabelle.

Das war nur eine Abkürzung, um mir eine Tabelle zu sparen. Das hätte ich noch erwähnen sollen.

Ist sicherlich in dem Fall wieder mal irrelevant, da sich die Größe ja in Grenzen hält. Aber würdest du das auch bei einer großen Datenbank so machen? Namensänderungen kann man in beiden Versionen gut umsetzen, falls fällig, nur die Namen abfragen geht ja auch mittels SELECT name FROM booking GROUP BY name;, um bspw. eine ComboBox zu befüllen. Ich habe dies halt ausgelagert, da ich zu den items weitere Infos wie ein Icon oder eben die Gruppenzugehörigkeit speichern will. Das würde die eigentliche Buchungstabelle ja unnötig aufblähen, oder regelt sqlite das intern so gut, dass es keinen Unterschied machen würde die Daten hundertfach zu speichern? Alternativ: Sollte ich anstatt mit der ID mit einem eindeutigen Namen als "primary key" arbeiten?

Nein, wenn Du für die Entität weitere Attribute speichern willst wie das Icon, dann sollte man das unbedingt auslagern. Ansonsten musst Du das ja redundant speichern, und das kostet Platz; viel schlimmer noch, Redundanz birgt immer die Gefahr von Inkonsistenzen. Insofern: genau richtig!

Als ich damals mal mit MySQL gespielt habe, hieß es bei den Tutorials immer, man müsse die Daten "atomisieren". Daher bin ich immer versucht alles in extra Tabellen auszulagern, was nicht unmittelbar in der "Haupttabelle" benötigt wird.

Das nennt man eigentlich "Normalisierung".

• items enthält somit das für den Menschen relevante. Einen lesbaren Namen, ggf. ein Icon, eine Gruppenzugehörigkeit für statistische Zwecke.

• groups enthält den lesbaren Namen der Gruppe, (zukünftig) ein Gruppensymbol und ggf. noch weitere Nicklichkeiten

• booking ist die eigentliche Datentabelle. Diese enthält nur das, was sie muss, weil dort ja der Bärenanteil an Informationen liegt.

  • value wird gespeichert, da sich die Preise ja mit der Zeit anpassen werden

  • tim benötige ich als Zeitstempel. Jeder Zeitstempel, bzw. jede Zeile bedeutet ja eine Buchung, welche wiederum mit COUNT(itemRow) eine Gesamtmenge ergibt und nur durch das Speichern der Zeit auswertbar wird. Sonst gäbe es nur eine Gesamtmenge seit Startdatum.

Klingt soweit erst mal plausibel.

Die Felder updated und deleted sind der in ferner Zukunft geplanten Synchronisation mit anderen Datenbanken geschuldet, wie du dich sicher erinnerst 😉 Bei deleted bin ich mir allerdings auch noch nicht wirklich sicher, ob ich das so mache oder nicht doch etwas komplexer (deleted als weiteren Zeitstempel, bei "Jahresabschluss" oder so endgültig löschen), damit Einträge auch wirklich gelöscht werden können, wenn die Daten in allen DB's auf gelöscht stehen. Das ist allerdings ein extra Thema und gehört zur noch nicht geplanten Sync.

Das musst Du dann natürlich in den Abfragen berücksichtigen, weil die Einträge, die gelöscht sind, ja nicht in die Summen eingehen sollen, oder?

Danke dir schonmal für deine Zeit! Falls am Konzept ein Denkfehler ist oder ich grundlegend was falsch verstanden habe, weise mich gern darauf hin. Anders lern ich ja nix mit meinem Hasenhirn 😀

Was mir noch aufgefallen ist:

• Die Tabellennamen sind uneinheitlich Mehrzahl und Einzahl. Die Konvention sagt, dass man die Einzahl benutzen soll, soweit ich mich erinnere.

• Die Tabelle "groups" (die also "group" heißen sollte) enthält keinen PK, den Du aber brauchst, weil Du ja mit "items.groupID" darauf referenzieren willst.

• Pack erst mal die PKs rein und deklariere dann die FKs.

• "items.iconPath" hat als Vorgabewert eine leere Zeichenkette. Das würde ich ohne deklarieren, aber NULL zulassen. Auf die Weise erkennst Du sicherer, dass da kein Wert vergeben ist.

• Generell gehe noch mal durch und schaue, welche Werte NULL sein können und welche nicht NULL sein dürfen.

• Der DEFAULT bei "groups.name" ergibt überhaupt keinen Sinn. Das ist definitiv etwas, das Du explizit setzen willst.

• Ebenso der DEFAULT bei "item.groupID".

• Kläre für Dich: Kann ein Item in mehreren Gruppen sein? Ist es immer definitiv in exakt einer Gruppe? Falls nicht, ergibt es u.U. Sinn, eine Gruppe "Ungrouped" einzuführen, damit man immer alle Items über irgendeine Gruppe erfassen kann. Dann kann man sie uniform verarbeiten und muss nicht separat die Items erfassen, die "groupID is NULL" haben.