Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

INPUT/UPDATE von abhängigen Tabellen

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von DarthSidious, 29 November 2013.

  1. DarthSidious

    DarthSidious Benutzer

    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.
     
  2. akretschmer

    akretschmer Datenbank-Guru

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

    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.

    Das ist eines der Grundprinzipien von Datenbanken: Shit In, Shit Out.
     
  3. DarthSidious

    DarthSidious Benutzer

    Muss ich nochmals googeln .. oder in der Doku nachschauen.

    ...oder die Städte der PLZs über einen webservice holen, falls es sowas gibt
     
  4. akretschmer

    akretschmer Datenbank-Guru


    OpenStreetMap?
     
  5. DarthSidious

    DarthSidious Benutzer

    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.
     
    Zuletzt bearbeitet: 30 November 2013
  6. akretschmer

    akretschmer Datenbank-Guru


    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
     
  7. DarthSidious

    DarthSidious Benutzer

    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!
     
  8. ukulele

    ukulele Datenbank-Guru

    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.
     
  9. akretschmer

    akretschmer Datenbank-Guru

    Meep: die Blackhole-Storage-Engine. Sau-schnell.
     
  10. akretschmer

    akretschmer Datenbank-Guru

    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
     
  11. DarthSidious

    DarthSidious Benutzer

    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: 25 Januar 2014
  12. akretschmer

    akretschmer Datenbank-Guru

    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.
     
  13. DarthSidious

    DarthSidious Benutzer

    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;
     
  14. akretschmer

    akretschmer Datenbank-Guru

    Left join von orte_temp mit orte über die PLZ. Wenn der Eintrag noch nicht in Orte ist (orte_plz is null) erfolgt der Insert, sonst nicht.
     
  15. DarthSidious

    DarthSidious Benutzer

    O.K., gar nicht so einfach, danke!!
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden