SQLite - IDs aus zwei Tabellen anhand von anderen Spaltenwerte in dritte Tabelle eintragen (sonstige Sprachen, auch PHP)

[thread]20293[/thread]

SQLite - IDs aus zwei Tabellen anhand von anderen Spaltenwerte in dritte Tabelle eintragen

Tags: perl5 SQLite Ähnliche Threads

Leser: 10

Articles: hide open all | hide show old branches

+10 replies
Linuxer

2017-05-18 19:30
User since
2006-01-27
3890 Artikel
HausmeisterIn
Hallo,

sorry für den Titel, wusste es gerade nicht besser zu formulieren.

Sagen wir mal, ich habe drei Tabellen (hier vereinfacht):
Code: (dl )

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE TABLE Addresses ( id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL ); CREATE TABLE Groups ( id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL ); CREATE TABLE Group_Members ( grp_id INTEGER REFERENCES Groups(id), adr_id INTEGER REFERENCES Addresses(id) );
"Addresses" soll Rechner-Namen oder IP-Adressen enthalten.
Manche der angelegten Adressen können in Gruppen zusammengefasst sein; "Groups" enthält die Namen der Gruppen.
"Group_Members" soll dann die Zuordnung zwischen Gruppe und Adressen enthalten.

Nehmen wir an, ich habe nun einen (bereits registrierten) Gruppen-Namen und ebenso eine entsprechende Adresse.
Wie muss ich den INSERT-Befehl zusammenstellen, damit ich anhand des Namens und der Addresse die jeweiligen IDs in die dritte Tabelle "Group_Members" eintragen kann?

Ich werkel schon eine Weile an dem ganzen (SQL) rum (noch wegen anderer Dinge) und momentan raucht der Kopf zu viel...

...

Wie ich das hier so geschrieben habe, hatte ich eine "doofe Idee" und diese ausprobiert. Funktioniert ~~(jedenfalls mit dem Test-SQL-Code)~~ (wenn man die Daten korrekt aufbereitet, kommt auch ein besseres Ergebnis im produktiven Code raus)...

Aber geht das auch kürzer und "schöner" oder ist das die Art wie man das üblicherweise so macht (also mehrere SELECTs für die gesuchten Werte aus den verschiedenen Quell-Tabellen)? Hier mal mein auf dieses "Problem" reduzierte SQLite Test-Code:

reduced.sql (2.0kb)
Testlauf mit sqlite3 test.db < reduced.sql

Also, lassen sich die INSERT-Statements in Zeile 25+26 vereinfachen oder ist das so gut und OK?
Last edited: 2017-05-18 19:41:33 +0200 (CEST)
meine Beiträge: I.d.R. alle Angaben ohne Gewähr und auf Linux abgestimmt!
Die Sprache heisst Perl, nicht PERL. - Bitte Crossposts als solche kenntlich machen!
- +3 replies
- Linuxer
  
  2017-05-18 19:58
  
  User since
  2006-01-27
  3890 Artikel
  HausmeisterIn
  
  Da ich relativ viele Daten haben werde, überlege ich gerade, die SELECTs im Perl-Code vorher zu machen und die Paare im Perl zu bilden und dann die ID-Paare als Reihe von VALUES mit einem INSERT einzutragen.
  Muss ich mir morgen mal anschauen.
  Die Frage gilt dann aber weiterhin, weil es mich nun interessiert, ob das auch anders in SQL geht. Danke.
  meine Beiträge: I.d.R. alle Angaben ohne Gewähr und auf Linux abgestimmt!
  Die Sprache heisst Perl, nicht PERL. - Bitte Crossposts als solche kenntlich machen!
  - +2 replies
  - Gast payx
    
    2017-05-21 11:09
    Hallo Linuxer,
    
    ich hoffe, ich habe die Aufgabe richtig verstanden.
    
    Zunächst würde ich (off topic) sicherstellen, dass die Mapping-Tabelle keine Dubletten enthalten kann:
    
    Code: (dl )
    
    1 2 3 4 5
    
    CREATE TABLE IF NOT EXISTS Addr_Groups_Members ( grp_id INTEGER REFERENCES Address_Groups(id), obj_id INTEGER REFERENCES Addresses(id), constraint unique_mapping unique (grp_id, obj_id) );
    
    Einfachere Inserts könnten so aussehen:
    
    Code: (dl )
    
    1 2
    
    INSERT INTO Addr_Groups_Members SELECT g.id as grp_id, a.id as obj_id from Address_Groups as g, Addresses as a where g.name = "dummy" and a.name = "any"; INSERT INTO Addr_Groups_Members SELECT g.id as grp_id, a.id as obj_id from Address_Groups as g, Addresses as a where g.name = "dummy" and a.name = "localhost";
    
    Die Erzeugung Deiner SQLs wird dadurch aber nicht einfacher, denn Du musst ja bei diesem wie bei Deinem SQL die beiden Parameter einfügen.
    
    Dasselbe Ergebnis wie mit obigen beiden Inserts bekommst Du bei Deinem Beispiel freilich so (ohne Parameter, kartesisches Produkt):
    
    Code: (dl )
    
    INSERT INTO Addr_Groups_Members SELECT g.id as grp_id, a.id as obj_id from Address_Groups as g, Addresses as a;
    
    Aber vermutlich möchtest Du künftig nicht immer alle Adressen in allen Gruppen haben.
    
    Nachdem wir ganz oben die Mappings unique gemacht haben, sollten wir jetzt noch (wieder off topic) sicherstellen, dass gar nicht erst versucht wird, Dubletten einzufügen:
    
    Code: (dl )
    
    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
    
    INSERT INTO Addr_Groups_Members SELECT g.id as grp_id , a.id as obj_id from Address_Groups as g join Addresses as a on g.name = "dummy" and a.name = "any" left outer join Addr_Groups_Members as ag on g.id = ag.grp_id and a.ID = ag.obj_id where ag.obj_id is null ;
    
    HTH
    Viele Grüße mal wieder
    payx
    Last edited: 2017-05-21 11:14:20 +0200 (CEST)
    - Linuxer
      
      2017-05-22 15:06
      
      User since
      2006-01-27
      3890 Artikel
      HausmeisterIn
      
      Danke für Deine Antwort; sowohl für die "direkte" als auch für die "offtopic" Antworten.
      
      Sind schöne Ideen enthalten, das "unique constraint" werde ich gleich mal übernehmen.
      
      Den Rest muss ich mir noch mal in Ruhe zu Gemüte führen; vor allem, da mir beim Grübeln andere "Probleme" aufgefallen sind, die ich noch berücksichtigen muss; evtl. muss ich Teile des Designs überarbeiten.
      meine Beiträge: I.d.R. alle Angaben ohne Gewähr und auf Linux abgestimmt!
      Die Sprache heisst Perl, nicht PERL. - Bitte Crossposts als solche kenntlich machen!
- +4 replies
- rosti
  
  2017-05-20 06:05
  
  User since
  2011-03-19
  3492 Artikel
  BenutzerIn
  
  Ich würde die Members (Addresses) als Objekte speichern wobei Gruppenname und Name Eigenschaften sind. Mit der IP-Addr kriegt da jedes Objekt eine eindeutige ID (Entity).
  - +3 replies
  - Linuxer
    
    2017-05-22 15:02
    
    User since
    2006-01-27
    3890 Artikel
    HausmeisterIn
    
    Danke für Deine Antwort.
    
    Die IP-Adresse ist leider nicht eindeutig, da private Adressen mehrmals vergeben sein können und sind.
    Die Unterscheidung muss daher an etwas anderem als an der IP gemacht werden.
    
    Ein Adress-Objekt kann leider auch in mehr als einer Gruppe enthalten sein; daher ist der Gruppennamen als Eigenschaft einen Adress-Objekts meiner Meinung nach nicht wirklich brauchbar.
    meine Beiträge: I.d.R. alle Angaben ohne Gewähr und auf Linux abgestimmt!
    Die Sprache heisst Perl, nicht PERL. - Bitte Crossposts als solche kenntlich machen!
    - +2 replies
    - rosti
      
      2017-05-22 15:20
      
      User since
      2011-03-19
      3492 Artikel
      BenutzerIn
      
      Nun, die Objekt-ID muss ja nicht die IP-Adresse sein, die kann davon völlig unabhängig vergeben werden, z.B. eine fortlaufende Nummer. Und wenn ein Objekt zu mehreren Gruppen gehört, dann ist diese Eigenschaft eben ein Array bzw. eine Arrayreferenz. Wie willst Du denn gerade sowas in einer relationalen Datenbank abbilden?
      
      Und selbst wenn die Datenhaltung relational wäre, eine Addressverwaltung macht sich programmiertechnisch besser objektorientiert, so meine Erfahrung.
      
      MfG
      
      PS: Wenn's unbedingt eine Datenbank sein muss, die gibt's auch objektorientiert bzw. ORM.
      Last edited: 2017-05-22 15:37:34 +0200 (CEST)
      - Linuxer
        
        2017-05-22 15:40
        
        User since
        2006-01-27
        3890 Artikel
        HausmeisterIn
        
        2017-05-22T13:20:07 rosti
        Nun, die Objekt-ID muss ja nicht die IP-Adresse sein, die kann davon völlig unabhängig vergeben werden, z.B. eine fortlaufende Nummer.
        
        Naja, ich habe Deinen Satz so verstanden, dass es genau Dein Tipp wäre, die IP als eindeutige ID zu verwenden. OK, abhaken; Punkt geklärt ;-)
        
        2017-05-22T13:20:07 rosti
        Und wenn ein Objek zu mehreren Gruppen gehört, dann ist diese Eigenschaft eben ein Array bzw. eine Arrayreferenz. Wie willst Du denn gerade sowas in einer relationalen Datenbank abbilden?
        
        Gruppen bekommen eine ID, Adressen bekommen eine ID. Und dann wird das über eine weitere Tabelle abgebildet (s.o. Tabelle Groups_Members, bzw. Addr_Groups_Members im gezeigten "reduced.sql" ).
        
        PS:
        
        Quote
        PS: Wenn's unbedingt eine Datenbank sein muss, die gibt's auch objektorientiert bzw. ORM.
        
        Guter Hinweis. Gutes Stichwort. Danke.
        Last edited: 2017-05-22 15:43:22 +0200 (CEST)
        meine Beiträge: I.d.R. alle Angaben ohne Gewähr und auf Linux abgestimmt!
        Die Sprache heisst Perl, nicht PERL. - Bitte Crossposts als solche kenntlich machen!
- Linuxer
  
  2017-08-15 16:45
  
  User since
  2006-01-27
  3890 Artikel
  HausmeisterIn
  
  Was mir gerade auffällt: Bitte mal verschieben ins Forum "Sonstige Sprachen". Danke.
  meine Beiträge: I.d.R. alle Angaben ohne Gewähr und auf Linux abgestimmt!
  Die Sprache heisst Perl, nicht PERL. - Bitte Crossposts als solche kenntlich machen!
- GwenDragon
  
  2017-08-15 16:59
  
  User since
  2005-01-17
  14758 Artikel
  Admin1
  
  Verschoben von Datenbanken und Verzeichnisdienste nach sonstige Sprachen, auch PHP
  die Drachin Gwen
  
  Meine Perl-Artikel · perldev – verschiedene Perl-Versionen unter Windows starten

View all threads created 2017-05-18 19:30.