UPDATE/INSERT bei verbundenen Tabellen in SQLITE

wisher

Benutzer
Beiträge
6
Hallo, ich bin Anfänger/Autodidakt und habe mit C#/Dapper ein funktionierendes Frontend für eine SQLite-DB mit einer nicht normalisierten Tabelle erstellt. Um Redundanzen zu vermeiden habe ich die Datenbank normalisiert mit 3 Tabellen:
personen -(1:n)-> op -(1:n)-> us (Untersuchungen)
mit folgenden Indizes;
personen.ID op,IDOP, op.FKPAT us.FKOP, (us.IDUS)

SELECT-Statemant ohne Fehlermeldung (allerdings ist die Tabelle derzeit noch leer)

Code:
                SELECT * FROM personen
               LEFT JOIN op ON op.FKPAT=personen.ID
               LEFT JOIN us ON us.FKOP=op.IDOP

Nach sehr vielem herumprobieren habe ich irgendwo im Internet gelesen dass SQLite keine Updates/Inserts bei verbundenen Tabellen unterstützt und hatte es dann (als letzten Versuch) mit einem verschachtelten Statement versucht, also quasi eine virtuelle Tabelle die alle Daten aufnehmen kann. Hierfür habe ich den o. g. Code in einen string joinedTable gepackt, so dass mein SQL-Kommando jetzt so aussieht:
Code:
               INSERT  INTO (joinedTable) (wert1, wert2, wert3, usw.) 
              VALUES (@wert1, @wert2, @wert3, usw.)
bzw.
Code:
              UPDATE (joinedTable) 
              SET wert1=@wert1, wert2=@wert2, wert3=@wert3, usw.
              WHERE ID=@ID

Leider meckert der SQL-Mapper über einen Fehler im SQL-Statement bei "(". Wo immer das auch sein mag ist mir völlig unklar. Den Update-Befehl habe ich mangels vorhandener Daten noch nicht probiert, schätze aber dass es ähnlich sein wird.

Sollten noch Infos fehlen bin ich für jeden Hinweis dankbar und werde diese gerne nachreichen.
Schon mal jetzt vielen Dank für die Mühe.
 
Werbung:
OK, versuche es mal zu rekonstruieren, da ich das meiste wieder gelöscht hatte nach entsprechenden Fehlermeldungen. Angefangen hatte ich ganz naiv mit
Code:
SELECT * FROM personen, op, us
so nicht möglich, gemäß Internetrecherche. Analog dazu auch die UPDATE bzw. INSERT-Statements. Danach:
Code:
SELECT * FROM personen LEFT JOIN (op, us)
ON (op.FKPAT=personen.ID AND us.FKOP=op.IDOP)
dann auch noch
Code:
SELECT * FROM personen
LEFT JOIN op ON op.FKPAT=personen.ID
us LEFT JOIN ON us.FKOP=op.IDOP
...und einiges andere was ich aber jetzt nicht mehr zusammen kriege. Wie gesagt alles wieder verworfen. Das Programm selbst funktioniert mit einer nicht normalisierten Tabelle völlig problemlos, sodass ich das Problem in der SQL-Abfrage vermute.
Der Aufbau der Datenbank ist wie folgt:
Code:
CREATE TABLE IF NOT EXISTS op (
   IDOP   INTEGER   NOT NULL
                    PRIMARY KEY AUTOINCREMENT
                    UNIQUE,
   FKPAT  INTEGER   NOT NULL
                    REFERENCES pat (ID) ON UPDATE CASCADE,
   OP     INTEGER   NOT NULL,
   Seite  INTEGER   NOT NULL,
   OPDate DATE (10) NOT NULL,
   FOREIGN KEY (FKPAT) REFERENCES pat (ID) 
);

CREATE TABLE IF NOT EXISTS pat (
   ID      INTEGER   NOT NULL
                     PRIMARY KEY AUTOINCREMENT
                     UNIQUE,
   Name    TEXT      NOT NULL,
   Vorname TEXT      NOT NULL,
   GDate   DATE (10) NOT NULL,
   Strasse TEXT      NOT NULL,
   HNummer TEXT      NOT NULL,
   PLZ     INTEGER   NOT NULL,
   Ort     TEXT      NOT NULL
);

CREATE TABLE sqlite_sequence (
   name,
   seq
);
INSERT INTO sqlite_sequence (
                               name,
                               seq
                            )
                            VALUES (
                               'pat',
                               0
                            );
INSERT INTO sqlite_sequence (
                               name,
                               seq
                            )
                            VALUES (
                               'op',
                               0
                            );
INSERT INTO sqlite_sequence (
                               name,
                               seq
                            )
                            VALUES (
                               'us',
                               0
                            );

CREATE TABLE IF NOT EXISTS us (
   IDUS            INTEGER   NOT NULL
                             PRIMARY KEY AUTOINCREMENT
                             UNIQUE,
   FKOP            INTEGER   NOT NULL
                             REFERENCES op (IDOP) ON UPDATE CASCADE,
   UDate           DATE (10) NOT NULL,
   Schmerz         INTEGER   NOT NULL
                             DEFAULT 0,
   Treppen         INTEGER   NOT NULL
                             DEFAULT 0,
   Schuhe          INTEGER   NOT NULL
                             DEFAULT 0,
   Gehhilfen       INTEGER   NOT NULL
                             DEFAULT 0,
   Gehstrecke      INTEGER   NOT NULL
                             DEFAULT 0,
   Anlaufschmerz   INTEGER   NOT NULL
                             DEFAULT 0,
   Hinken          INTEGER   NOT NULL
                             DEFAULT 0,
   Pflege          INTEGER   NOT NULL
                             DEFAULT 0,
   Aufstehen       INTEGER   NOT NULL
                             DEFAULT 0,
   Verkehrsmittel  INTEGER   NOT NULL
                             DEFAULT 0,
   Flektion        INTEGER   NOT NULL
                             DEFAULT 0,
   ExtHip          INTEGER   NOT NULL
                             DEFAULT 0,
   Abduktion       INTEGER   NOT NULL
                             DEFAULT 0,
   Glutkraft       INTEGER   NOT NULL
                             DEFAULT 0,
   ExtKnee         INTEGER   NOT NULL
                             DEFAULT 0,
   Weichteilbefund INTEGER   NOT NULL
                             DEFAULT 0,
   Quadkraft       INTEGER   NOT NULL
                             DEFAULT 0,
   Score           INTEGER   NOT NULL
                             DEFAULT 0,
   FOREIGN KEY (FKOP) REFERENCES op (IDOP) 
);

CREATE INDEX FK_OP_IDX ON us (FKOP);

CREATE INDEX FK_PAT_IDX ON op (FKPAT);
In dem Code heißt die Tabelle 'personen' allerdings 'pat'. Hatte den Namen 'personen' nur beispielhaft zum einfacheren Verständnis benutzt.
 
ich nenne mal paar Dinge, die mir auffallen:

  • select ... from a, b, c wird so nicht funktionieren bzw. mündet in einem cross join, was Du wohl eher nicht willst
  • insert into geht nicht in mehrere Tabellen gleichzeitig. (abgesehen von writeable Common Table Expressions in PostgreSQL)
  • PLZ INTEGER ist natürlich Bullshit, Dresden hat 0xxxx, mit führender 0. Diese würde verschluckt werden. Willst Du Postleitzahlen addieren oder einen Durchschnitt bilden?
  • was bezweckst Du mit sqlite_sequence ? Das ist schon syntaktisch falsch
  • was sollen die Werte da drin?
  • was sind das für Spalten in der letzten Tabelle, also z.B. Treppen, Schuhe, Gehhilfen? Kommen da später noch Tragetaschen, Mülleimer und Mistgabeln dazu? Das sieht zumindest nach nicht verstandener Normalisierung aus.

Was noch immer unklar ist: was ist dein eigentliches Problem? Ein Fall von XY Problem – Wikipedia ?
 
Zu Deinem ersten Punkt: Ist mir schon aufgefallen (hab' ich nach Fehlermeldung dann auch im Internet gefunden), war mein erster Versuch, aber Du hattest ja gefragt was ich so alles versucht habe, und das war das Erste.
Zu dem zweiten Punkt: Das genau ist es ja was ich brauche. Ich muss ja die Tabellen irgendwie updaten bzw. was einfügen. Und irgendwie kann ich mir nicht vorstellen, dass das bei SQLite nicht gehen soll. Schließlich würde man gerne mit einer normalisierten Datenbank arbeiten und die zerfällt dann naturgemäß in verschiedene Tabellen, oder?
Zu dem dritten Punkt: Super Hinweis! Danke. Hatte bisher noch keinen aus Dresden ;-). War natürlich ein blöder Fehler von mir.
Vierter Punkt: Hat SQL-Studio beim Export in SQL automatisch fabriziert. Um ehrlich zu sein hab' ich auch keine Ahnung was das soll.
Fünfter Punkt: Meinst Du die Werte bei sqlite_sequence? Keine Ahnung ist ein automatisierter Export bzw. interne (Verwaltungs-?)Anlage in SQL-Studio.
Sechster Punkt: ???? Das sind relevante Datenspalten. Da geht's um Abfragen aus dem medizinischen Bereich, also nix mit Güllefässern, Mistgabeln oder so. Die Spalten sind der Einfachheit halber kurz bezeichnet (-> weniger Tipparbeit. Ja, und ich weiß schon das man Tabellen in der Abfrage mit SELECT * FROM langerTabellenname AS t abkürzen könnte, aber gearbeitet wird mit dem Frontend, nicht direkt mit der Tabelle)

Um's klar zu machen: Punkt 2 ist der relevante Punkt. Also wie kann man UPDATE oder INSERT bei mehreren verbundenen Tabellen in SQLite erreichen?

Vielen Dank
 
Zuletzt bearbeitet:
Also wie kann man UPDATE oder INSERT bei mehreren verbundenen Tabellen

Dafür gibt es (sqllite weiß ich nicht) in Datenbanke Transaktionen. Man faßt zusammengehörige Dinge einfach zusammen. Beispiel, Überweisung: in einer Transaktion wird von Konto A was abgebucht und in Konto B gutgeschrieben. Eine Transaktion (TX) stellt sicher, daß entweder alles oder nichts gemacht wird.

Bei Deinen verbundenen Tabellen passiert dies ähnlich:

Code:
test=# create table bla(id int generated always as identity primary key, val text);
CREATE TABLE
test=*# create table blubb(bla_id int references bla, val text);
CREATE TABLE
test=*# commit;
COMMIT
test=#
--
-- neue TX
--
test=# begin;
BEGIN
test=*# insert into bla (val) values ('test bla');
INSERT 0 1
--
-- jetzt wurde eine ID generiert, die wir nicht kennen, aber mit currval() abfragen können
--
test=*# insert into blubb (bla_id, val) select currval('bla_id_seq'), 'test blubb';
INSERT 0 1
test=*# commit;
COMMIT
--
-- TX ist commited
-- Kontrolle
--
test=# select * from bla;
 id |   val   
----+----------
  1 | test bla
(1 row)

test=*# select * from blubb;
 bla_id |    val     
--------+------------
      1 | test blubb
(1 row)

test=*#
test=*#
test=*# commit;
COMMIT
--
-- was oben 2 Befehle (2 Inserts) waren, geht auch in einem Befehl
--
test=# with id as (insert into bla(val) values ('test 2 bla') returning id) insert into blubb select id, 'test 2 blubb' from id;
INSERT 0 1
--
-- das war 1 atomarer Datenbankbefehl, der in 2 Tabellen geschrieben hat, das returning des ersten liefert die vergebene id
-- sorry für die Namenswahl, die virtuelle Tabelle 'id' wurde erzeugt, mit einer Spalte namens 'id', hab ich bissl blöd gemacht, egal
--

test=*# select * from bla;
 id |    val     
----+------------
  1 | test bla
  2 | test 2 bla
(2 rows)

test=*# select * from blubb;
 bla_id |     val     
--------+--------------
      1 | test blubb
      2 | test 2 blubb
(2 rows)

test=*#

Letzteres kann wohl nur PostgreSQL.

tl;dr

wenn Du in mehreren Tabellen Inserts/Updates/Deletes machen willst, fasse diese Aktionen in einer TX zusammen. Manchmal muß man dazu auch Constraints auf Deferred setzen, aber das führt jetzt vielleicht erst mal zu weit - und vermutlich auch weit über sqllite hinaus.
 
Zunächst mal vielen Dank für die sehr ausführliche Antwort. Leider bin ich kein Datenbankprofi und mit meinen wenigen Kenntnissen kann ich bestenfalls erahnen was da passieren soll. Letztlich bin ich ehrlich gesagt bereits bei 'test=#' überfordert (ist das SQL?). Wenn ich das richtig verstehe werden 2 Tabellen erzeugt, Werte eingetragen, wohl auch Indizes, aber den Rest kann ich mit meinem bisschen Verständnis nicht nachvollziehen. Es erscheint mir aber wenig automatisch und sehr viel "zu Fuß" programmiert, insbesondere bei ca. 30 einzelnen Werten, also reichlich Aufwand um eine (redundante) Tabelle, die bereits funktioniert, durch die normalisierte Form (3 Tabellen) zu ersetzen und nutzen zu können. Von Seiten der Performanz ist die nicht normalisierte Form aufgrund der geringen Datenmenge auch kein Problem, zumal mit Dapper im Programm/Speicher eh eine Struktur erzeugt wird, die einem Array kompletter Datensätze entspricht und bei Bedarf in die Tabelle übertragen wird. Ich hatte mir ganz naiv gedacht 2 oder 3 Codezeilen umzuschreiben und das wär's. Aber dieser Aufwand lohnt dann die Mühe doch nicht.
Trotzdem nochmal ganz lieben Dank.
 
'test=#' überfordert (ist das SQL?)

das ist der Prompt des Kommandozeilen-Clients. Ähnlich wie C:\ in der DOS-Box...

Es erscheint mir aber wenig automatisch und sehr viel "zu Fuß" programmiert, insbesondere bei ca. 30 einzelnen Werten,

Nein, das ist pures SQL, was ich eingebe, und die Antworten der Datenbank.

Ich hatte mir ganz naiv gedacht 2 oder 3 Codezeilen umzuschreiben und das wär's.

Code:
with id as (insert into bla(val) values ('test 2 bla') returning id) insert into blubb select id, 'test 2 blubb' from id;

das ist in Worten EINE Zeile Code, um in 2 Tabellen was einzutragen, wobei ein Auto-generierter Wert aus der ersten Tabelle in der zweiten eingetragen wird. Das ist hocheffizient, kürzer geht nicht.
 
Nachtrag: ich verwende PostgreSQL, SQLlite kann das möglicherweise nicht. Dann die zuerst gezeigte Lösung, die sollte analog machbar sein.
 
OK, ich glaube ich verstehe, zumindest teilweise. Für mich war der Prompt ungewöhnlich. Bei MySQL steht zumindest 'mysql>'. Da ich nicht direkt mit den Datenbanken arbeite bin ich das nicht so gewohnt. Bzgl. der ID's hatte ich gedacht, dass die Datenbank das von sich aus, eben automatisch, kann da ja die Referenzen/Beziehungen der Tabellen zueinander bei der Tabellendefinition bereits angegeben werden, heißt, wenn ich die ID (PK) der ersten Tabelle habe habe ich somit automatisch den Foreign Key in der zweiten Tabelle, die wiederum ihre eigene ID erstellt und somit den Foreign Key für die dritte Tabelle stellt, die wiederum ihre eigene ID-Verwaltung besitzt etc. etc.
Soweit mir bekannt ist unterstützt SQLite Transaktionen mit BEGIN TRANSACTION. Bzgl. der Syntax müsste ich nochmal schauen. Könnte es sein, dass Transaktionen hauptsächlich dann sinnvoll sind wenn einzelne Werte zu übertragen sind? Bei mir ist es nämlich so, dass immer ein kompletter Datensatz eingefügt oder upgedated wird, d. h. es müssen nur die FK zum PK der entsprechenden Elterntabelle passen.
Bei Deinem Codebeispiel heißen, wenn ich das richtig einschätze, sowohl der PK von 'bla' also auch der FK von blubb jeweils 'id', oder setzt Du voraus, dass beide Tabellen immer die gleichen 'id' (PK) haben? Das wäre ja dann eine 1:1-Beziehung.
 
Bzgl. der ID's hatte ich gedacht, dass die Datenbank das von sich aus, eben automatisch, kann da ja die Referenzen/Beziehungen der Tabellen zueinander bei der Tabellendefinition bereits angegeben werden, heißt, wenn ich die ID (PK) der ersten Tabelle habe habe ich somit automatisch den Foreign Key in der zweiten Tabelle,

nein, weil, es kann ja sein, ich will in der einen Tabelle einen Datensatz für Maier eintragen und in der zweiten Tabelle einen Datensatz für Schuster.

Könnte es sein, dass Transaktionen hauptsächlich dann sinnvoll sind wenn einzelne Werte zu übertragen sind?

Nein, nicht nur da. Transaktionen auf DML (Data Manipulation Language, also Insert/Update/Delete) wie auch DDL (Data Definition Language, also CREATE TABLE, ALTER TABLE, ...) können gern auch für viele Datensätze und Tabellen/Objekte auftreten. Stelle Dir ein Script vor, welches die DB auf eine neue Version ändert. Da können schnell mal Millionen und mehr Datensätze geändert und hunderte Tabelle geändert werden. Stelle Dir vor, das passiert NICHT in einer Transaktion - und nach 40% der Änderungen fällt der Strom aus ...

Und: mein Beispiel ist eine 1:N - Beziehung.
 
Meine Anmerkung zu 'id' bezog sich auf den Spaltennamen. Bin etwas verwirrt über die vielen 'id', glaube aber ich krieg' langsam eine Vorstellung von dem ganzen Vorgang. Da muss ich allerdings noch einiges lesen, um das ordentlich nachvollziehen zu können und schaue mir mal die Transaktionen genauer an. Bis dahin vielen Dank. Sollte ich eine gute Lösung finden, werd' ich die hier posten.
 
Werbung:
Zurück
Oben