INPUT/UPDATE von abhängigen Tabellen

D

DarthSidious

Benutzer
Beiträge
14
Hallo ich möchte gerne Tupel von der Entität <Firma> in eine Tabelle einfügen. Ich habe das Attribut Ort ausgelagert, wobei ich dann in der Tabelle <Ort> die PLZ als index verwende (PLZ ist dann Fremdschlüssel in <Firma>)

Wie füge ich jetzt neue Firmen hinzu, gibt da evtl. ein Tutorial dazu?

Die Logik stell ich mir so vor: Ich gebe die Attribute von <Firma> ein. Ein Script muss jetzt nachschauen, ob die PLZ mit zugeörigem Ort schon existiert. Falls ja wird kein neuer Datensatz in der Tabeller Ort erzeugt. Falls nein wird ein neuer Datensatz hinzugefügt, wobei natürlich nicht klar ist, ob die PLZ mit dem Ort übereinstimmt?
Falls das beim ersten mal eigeben schief gelaufen ist (z.B. 80193 Hamburg) ist natürlich gleich ein riesen Fehler in der Datenzuordnug...
Danke.
 
Werbung:
DarthSidious schrieb:
Hallo ich möchte gerne Tupel von der Entität <Firma> in eine Tabelle einfügen. Ich habe das Attribut Ort ausgelagert, wobei ich dann in der Tabelle <Ort> die PLZ als index verwende (PLZ ist dann Fremdschlüssel in <Firma>)

Wie füge ich jetzt neue Firmen hinzu, gibt da evtl. ein Tutorial dazu?
Zum Vergrößern anklicken....

Der INSERT - Befehl ist auf etlichen Seiten und sogar in der Doku erläutert.

Die Logik stell ich mir so vor: Ich gebe die Attribute von <Firma> ein. Ein Script muss jetzt nachschauen, ob die PLZ mit zugeörigem Ort schon existiert. Falls ja wird kein neuer Datensatz in der Tabeller Ort erzeugt. Falls nein wird ein neuer Datensatz hinzugefügt, wobei natürlich nicht klar ist, ob die PLZ mit dem Ort übereinstimmt?
Zum Vergrößern anklicken....

Zum Beispiel. Mann könnte sich auch eine Stored Procedure schreiben, der man Firma, PLZ und Ort übergibt. Diese läuft dann in der DB, prüft, ob der Ort schon gespeichert ist, trägt ihn ein falls nötig und dann speichert's die Firma. Eher trivial sowas.

Falls das beim ersten mal eigeben schief gelaufen ist (z.B. 80193 Hamburg) ist natürlich gleich ein riesen Fehler in der Datenzuordnug...
Danke.
Zum Vergrößern anklicken....

Das ist eines der Grundprinzipien von Datenbanken: Shit In, Shit Out.
 
DarthSidious schrieb:
Ich schau mir das an, danke!

edit:


Ja, INSERT kenn ich schon und UPDATE auch, nur ist eben bei abhängigen Tabellen einiges zu beachten. Da arbeite ich mich eben grade ein.
Zum Vergrößern anklicken....


Nun, so schlimm ist das ja nicht. Man kann es über die Applikation machen, machen viele so. Man kann es über die DB machen. Die DB ist 'näher' an den Daten. Wenn es über die Applikation läuft, kann es z.B. u.U. bei gleichzeitig laufenden Clients zu Effekten kommen, die man schwer handeln kann.
Eine Stored Proc in der DB, als quasi extra API, vermeidet sehr viele Probleme und gibt z.B. den Webentwicklern, die meist keine Ahnung von Datenbanken haben, eine genormte Schnittstelle. Ich habe kürzlich von Zalando "Schrei vor Glück" einen sehr coolen Vortrag dazu gesehen, die machen das nämlich so (gibt es auch als PDF in Internet zu finden...)

Um es mal kurz zu zeigen:

Code: 
test=# create table orte (plz text primary key, ort text);
CREATE TABLE   
Time: 8,630 ms   
test=*# create table firma (id serial primary key, plz text references orte, name text);
CREATE TABLE   
Time: 39,839 ms   
test=*# create or replace function insert(in in_plz text, in in_ort text, in in_firma text, out erfolg bool) as $$
test$# begin   
test$#  erfolg := false;   
test$#  create temporary table orte_tmp (plz text, ort text);   
test$#  insert into orte_tmp values (in_plz, in_ort);   
test$#   
test$#  lock table orte in exclusive mode;   
test$#   
test$#  update orte   
test$#  set ort = orte_tmp.ort   
test$#  from orte_tmp   
test$#  where orte.plz = orte_tmp.plz;   
test$#   
test$#  insert into orte   
test$#  select orte_tmp.plz, orte_tmp.ort   
test$#  from orte_tmp   
test$#  left outer join orte on (orte.plz=orte_tmp.plz)   
test$#  where orte.plz is null;   
test$#   
test$#  insert into firma (plz, name) values (in_plz, in_firma);   
test$#   
test$#  drop table orte_tmp;   
test$#   
test$#  erfolg := true;   
test$# end; $$language plpgsql;   
CREATE FUNCTION   
Time: 0,549 ms   
test=*# select insert('01067','Dresden','firma1');
 insert   
--------   
 t
(1 row)

Time: 39,497 ms
test=*# select insert('01067','Dresden','firma2');
 insert
--------
 t
(1 row)

Time: 2,943 ms
test=*# select insert('01067','Dresden','firma3');
 insert
--------
 t
(1 row)

Time: 2,575 ms
test=*# select insert('04711','Musterstadt','musterfirma');
 insert
--------
 t
(1 row)

Time: 3,043 ms
test=*# select * from orte;
  plz  |  ort
-------+-------------
 01067 | Dresden
 04711 | Musterstadt
(2 rows)

Time: 0,170 ms
test=*# select * from firma;
 id |  plz  |  name
----+-------+-------------
  1 | 01067 | firma1
  2 | 01067 | firma2
  3 | 01067 | firma3
  4 | 04711 | musterfirma
(4 rows)

Time: 0,223 ms
test=*#

Die API ist also die Funktion insert(plz, ort, firma), nur diese rufen die Webheinis auf. Die Logig passiert dann innerhalb der Funktion. Man kann das ganze noch geschmacklich abwürzen, indem man alle Rechte auf die Tabellen dem normalen Webuser entzieht und die Funktionen als 'security definer' definiert (und als DB-User mit den Rechten auf die Tabellen erstellt). Bringt einiges:

  • Schutz vor SQL-Injection
  • Schutz der Tabellen
  • schützt die Webentwickler vor sich selbst
  • verringert den Traffic zwischen Applikation und DB
  • einheitliche Schnittstelle. Du kannst dann auch 1000 einzelne Entwickler einstellen und dann die beste Applikation nehmen und die anderen 999 Idioten wieder kündigen
  • und so weiter
 
Danke für dass Beispiel! Ich hab an meiner Homepage gesehen was XSS anrichten kann. Wenn das mit SQL-Injections auch so ausschaut, macht es wiklich Sinn sich in die stored precedures einzuarbeiten!
 
Ohne das mit der SP in Frage stellen zu wollen, zunächst würde ich mal sagen das eine "vernünftige DB" die Referenzielle Intigrität über Foreign Key Constraints sicherstellt. Soll heißen: Wenn es die PLZ in der Ort Tabelle nicht gibt kann auch keine solche PLZ in der Haupttabelle eingetragen werden ohne das vorher ein Datensatz in der Ort Tabelle angelegt wird. Im Umkehrschluss kann auch kein Eintrag aus Ort entfernt werden wenn ein anderer Eintrag auf ihn verweist.

Natürlich kann das DBMS nicht auf Schwachsinn (oder "Shit") prüfen, aber es kann zumindest sicherstellen das dieser Shit zusammenhängend ist. Leider hats MySQL in einigen Dingen nicht so mit der Genauigkeit. Ich kann nicht sagen was bei MySQL wirklich funktioniert.
 
ukulele schrieb:
Ohne das mit der SP in Frage stellen zu wollen, zunächst würde ich mal sagen das eine "vernünftige DB" die Referenzielle Intigrität über Foreign Key Constraints sicherstellt.
Zum Vergrößern anklicken....

Btw.: MySQL kann das auch, unter bestimmten Bedingungen:

  • richtige Storage-Engine, auch wenn MyISAM nicht meckert: es kann das nicht
  • Definition darf nicht inline erfolgen, auch wenn das laut SQL-Norm erlaubt ist und kein Fehler erzeugt: es geht dann aber nicht
  • auf die richtige Konstellation von Vollmond und Gezeiten achten, sonst geht es nicht
 
Hallo,
ich habe mir das Zalando-SQL Script nochmals angeschaut.Hier eine Frage nur zur Logik:
update orte bestimmt neue PLZ -Ort Paare in orte, fall es einen Eintrag gegeben hätte: 60308 Mainz, dann könnte dieser durch den richtigen Eintrag 60308 FFM überschrieben werden.
Mir ist irgendwie nicht klar, wie der insert-Befehl zusammen mit dem joint funktioniert. Irgendie müssten ja hier die Duplikate eliminiert werden, aber wie?
Danke!

edit: join nicht joint
 
Zuletzt bearbeitet:
DarthSidious schrieb:
Hallo,
ich habe mir das Zalando-SQL Script nochmals angeschaut.Hier eine Frage nur zur Logik:
update orte bestimmt neue PLZ -Ort Paare in orte, fall es einen Eintrag gegeben hätte: 60308 Mainz, dann könnte dieser durch den richtigen Eintrag 60308 FFM überschrieben werden.
Mir ist irgendwie nicht klar, wie der insert-Befehl zusammen mit dem join funktioniert. Irgendie müssten ja hier die Duplikate eliminiert werden, aber wie?
Zum Vergrößern anklicken....

Versteh Dein Problem nicht.

Code: 
test=*# select insert('01067','Dresden-neu','firma3');
 insert
--------
 t
(1 row)

test=*# select * from orte;
  plz  |  ort
-------+-------------
 04711 | Musterstadt
 01067 | Dresden-neu
(2 rows)

Hab also aus Dresden Dresden-neu gemacht, paßt.
 
Ich nix verstehen:

test$# insert into orte
test$# select orte_tmp.plz, orte_tmp.ort
test$# from orte_tmp
test$# left outer join orte on (orte.plz=orte_tmp.plz)
test$# where orte.plz is null;
 
Werbung:
Zurück
Oben