DavidS/zusammenfassungen

Fork 0

David Schirrmeister ecc7ab358e update

2025-06-15 15:31:44 +02:00

16 KiB

Raw Permalink Blame History

Anwendungsentwicklung

Grundprinzipien

Fall 1: Ergebnis der Abfrage ist (max) ein einzelner Tupel
- bspw. Ausgabe von Preis und Namen des Produkts mit der Nr. 203
Fall 2: Ergebnis der Abfrage sind mehrere Tupel
- bspw. Ausgabe aller Produkte mit einem Preis größer 100 Euro
- Problem: Unterschiedliche Datenstrukturen
  - (imperative) Programmiersprachen: Tupel
  - SQL: Relation (Menge von Tupeln)
  - → Impedance mismatch

Cursor-Konzept

Abstrakte Sicht auf eine Relation, realisiert als Liste
Anfrageergebnisse werden sequenziell abgearbeitet

Prozedurale SQL-Erweiterungen

Nachteile von SQL in höheren Programmiersprachen
- Anwendungsprogramm wechselt häufig zwischen Host-Sprache und SQL-Anweisung
- Optimizer (Kern von DBMS) kann nur Bereich einzelner SQL-Anweisungen überschauen
- Daten müssen zwischen DB und Anwendung transportiert werden
Lösungen
- Erweiterung von SQL um konzepte prozeduraler Sprachen

Aufbau und Kontrollstrukturen

Kleinstes Element ist ein Block

[ DECLARE
  declarations ]
BEGIN
  statements
END;

Kontrollstrukturen

IF bedingung THEN
  statements
[ ELSE
  statements ]
END IF;

WHILE bedingung LOOP
  statements
END LOOP;

FOR name IN [reverse] expression ... expression [BY expression] LOOP
  statements
END LOOP;

können direkt in SQL-Client ausgeführt werden
- Ausführung startet nach ;
- $$ ... $$ sind Stringbegrenzer (ähnlich zu {...} in C++)
- ```
user=> DO $$
user=> BEGIN
user=> RAISE NOTICE 'Mein erster Test';
user=> END $$;
NOTICE: Mein erster Test
DO
user=>
```

Stored Procedures

entspricht Funktion, die in der Sprache des jeweiligen DBMS geschrieben wird
werden in Tabellen des Systemkatalogs der DB abgelegt
Spezielle Anwendung:
- Kombination mit Triggern

Beispiel:

CREATE OR REPLACE FUNCTION square(integer) returns integer as $SQUARE$
DECLARE
  x integer := $1;
BEGIN
  RAISE NOTICE 'Meine erste Funktion';
  x := x*x;
  RAISE NOTICE 'Ergebnis: %', x;
  RETURN x;
END;
$SQUARE$ LANGUAGE plpgsql;

erzeugt Funktion mit dem Namen square, die einen int als Parameter akzeptiert und zurückgibt
CREATE OR REPLACE erlaubt Änderung eines Stored Procedure

Auslesen einzerlner Tupel

Mithilfe eines Cursors

CREATE OR REPLACE FUNCTION countairports() returns integer as $countairports$
DECLARE
  anzahl integer;
  c1 cursor for select count(*) from flughafen;
BEGIN
  open c1;
  fetch c1 into anzahl;
  close c1;
  RETURN anzahl;
END;
$countairports$ LANGUAGE plpgsql;

Ausgabe:

user => select countairports();
countairports
-------------
         12

Mit Hilfe eines SELECT INTO

CREATE OR REPLACE FUNCTION countairports2() returns integer as $countairports2$
DECLARE
  anzahl integer;
BEGIN
  select count(*) INTO anzahl from flughafen;
    RETURN anzahl;
END;
$countairports2$ LANGUAGE plpgsql;

Ausgabe:

user => select countairports2();
countairports2
-------------
         12

Cursor in PL/pgSQL

CREATE OR REPLACE FUNCTION listflights() returns void AS $listflights$
DECLARE
    c1 CURSOR for SELECT * FROM flughafen;
    rowl RECORD;
BEGIN
    for rowl IN c1 LOOP
      raise notice 'Flugnummer: %', rowl.flugnr;
    end loop;
    return;
end;
$listflights$ language plpgsql

Mit jedem Schleifendurchlauf
- Cursor wird eine Position weitergeschaltet
- record passt sich auf Zeile des Cursors an
- mittels . kann auf Attribute zugegriffen werden
- Cursor wird durch Schleife automatisch geöffnet, geschlossen

Ändern von Tabelleninhalten

create or replace function wartun(varchar(8)) returns void as $wartung$
declare
    knz varchar(8) := $1;
    heute date := current_date;
begin
  if not exists(select * from protokoll where kennzeichen = knz and datum = heute)
  then
      insert into protokoll values (knz, heute, true);
  else
      update protokoll set freigabe = true where kennzeichen = knz and datum = heute;
  end if;
end;
$wartung$ language plpgsql

NOT EXISTS (bzw EXISTS) tested, ob überhaupt Zeilen existieren
INSERT und UPDATE können einfach so genutzt werden

Ändern über Cursor

DO $tarifrunde$
DECLARE
  c1 cursor for select * from angestellter;
  row1 record;
  proz1 float8 = 1.02;
  proz2 float8 = 1.05;
BEGIN
    open c1;
    loop
        fetch c1 into row1;
        exit when not found;
            if row1.gehalt > 10000
            then
                update angestellter set gehalt = gehalt * proz1 where current of c1;
            else
                update angestellter set gehalt * proz2 where current of c1;
            end if;
    end loop;
END; 
$tarifrunde$ language plpgsql

EXIT WHEN NOT FOUND verlässt Schleife, wenn kein Datensatz (mehr) gefunden wird
WHERE CURRENT OF c1 beschränkt Update auf den Tupel an Position des Cursors

Semantische Integritätsbedingungen

Trigger

Folge von benutzerdefinierten Anweisungen, die automatisch beim Vorliegen bestimmter Bedingungen ausgeführt werden
- bspw. bei Einfügen, Update, Löschen

Aufbau:

  CREATE TRIGGER name { BEFORE | AFTER |INSTEAD OF } { event [OR...]}
  ON table_name
  [FROM referenced_table_name ]
  [NOT DEFERRABLE | DEFERRABLE ][INITIALLY IMMEDIATE | INITIALLY DEFERRED]
  [FOR [EACH] {ROW | STATEMENT}]
  [WHEN (condition)]
  EXECUTE FUNCTION function_name (arguments)

  ----------------

  where event can be one of:
  INSERT
  UPDATE [ OF column_name [, ...]]
  DELETE
  TRUNCATE

Bsp Keine Preissenkung gestatten bei Änderung eines Preises:

CREATE OR REPLACE FUNCTION nosale() returns trigger as $$
BEGIN
    IF NEW.ProdPreis < OLD.ProdPrice then
    NEW.ProdPreis = OLD.ProdPrice;
    RAISE NOTICE 'Preissenkung nicht gestattet!';
    END IF;
    RETURN NEW;
END;
$$ language plpgsql;

CREATE TRIGGER nosaletrigger BEFORE UPDATE ON Produkt
    FOR EACH ROW EXECUTE FUNCTION nosale();

Zugriff auf relationale DB über APIs

SQLALCHEMY

ermöglicht DB-Zugriffe aus Python
Installation
- pip install sqlalchemy
- pip install psycopg2
Begriffe:
- Driver
  - Treiber, der zur Verbindung zur DB benötigt wird
- Engine
  - Verbindungsdaten zu einem DB-Server
  - Von ihr aus werden Connections erzeugt
- Connection
  - konkrete Verbindung zur DB
  - Kann SQL Befehle ausführen
- Result
  - Ergebnisse werden in einem Result gespeichert

Beispiel:

from sqlalchemy import *

engine = create_engine("postgresql+psycopg2://user:pass@141.100.70.93/dbname", echo=False)

with engine.connect() as con: # ensures closing the connection at the end of the block
  s = text("Select * from buch") # Creates Query object
  result = con.execute(s)
  for row in result:
    print(row)

  s = text("Select isbn, title from buch")
  result = con.execute(s)
  print(result.fetchall()) # fetches all rows as list and prints it

  s = text("Select * from buch where verlegt_name = 'Verglag 4'")
  result = con.execute(s)
  for row in result:
    print(row.isbn) # prints only isbn

SQL Injections

stmt = text("select * from account where username='" + username + "' and password='" + password + "'")

result = con.execute(stmt)

# If the result contains the specified user/password combination, then that user exists
if result.fetchone() is not None:
  print("User logged in")
else:
  print("Username and/or Password wrong!")

Bei Eingabe von abc' or '1' ='1 als Passwort
- Ausführen von select * from account where username='abc' and password='abc' OR ‘1’=’1’;
- Ergbnis ist eine Zeile, auch wenn das Passwort falsch war

Lösung: Parameter Binding

stmt = text("select * from account where username=:user and password=:pass")

result = con.execute(stmt, {"user": username, "pass": password})

# If the result contains the specified user/password combination, then that user exists
if result.fetchone() is not None:
  print("User logged in")
else:
  print("Username and/or Password wrong!")

Speichern von MetaDaten

from sqlalchemy import MetaData # or from sqlalchemy import *
meta = MetaData() # create metadata object

# Informationen über Tabellen werden anschließend aus DB extrahiert
buch = Table("buch", meta, autoload_with=engine)
verlag = Table("verlag", meta, autoload_with=engine)
autor = Table("autor", meta, autoload_with=engine)
verfasst = Table("ist_autor", meta, autoload_with=engine)

Impedance Mismatch

Wenn Daten aus DB extrahiert werden und in Klassen übernommen werden sollen, müssen diese konvertiert werden
- Nennt man Impedance Mismatch
  - Unverträglichkeit der beiden Welten

Objekt-relationales Mapping (OR-Mapping)

Mapping zwischen objektorientiertem (Klassendiagramm) und Relationenmodell
Änderungen werden automatisch an DB-System weitergegeben
Strategien
- Top-Down Bottom-Up
  
  Erstellen eines Klassendiagramms und mappen auf ein relationales DB-Schema Erstellen eines relationalen DB-Schemas und Re-Engineering auf Entity-Klassen
Begriffe
- Session
  - Erweiterung der bisher bekannten Connection
  - Verwaltet Verbindung zur DB und Transaktionen
  - Wichtiger Bestandteil: identity map
    - referenziert Objekte im Persistenzkontext
    - werden anhand der PKs unterschieden
- Persistenzkontext
  - Objekte können einer Session hinzugefügt werden
  - Alle "managed" Objekte werden auf Änderungen überwacht
    - Beim Schließen oder Committen der Session auf DB übertragen
- Mapping
  - definiert wie und ob ein Objekt der Anwendung in der DB abgebildet wird

Top-Down	Bottom-Up
Erstellen eines Klassendiagramms und mappen auf ein relationales DB-Schema	Erstellen eines relationalen DB-Schemas und Re-Engineering auf Entity-Klassen

Table Annotation

Jede Klasse muss entsprechend annotiert werden
- werden auch Entity Klassen genannt
Metadaten werden in einer Klasse gespeichert, von der die Klassen der Anwendung erben
gemeinsame Basisklasse zur Speicherung bleibt leer
- wird zum Tabellen erzeugen verwendet
Benötigt
- Name der Tabelle
- PKs
- ggf. weitere Attribute

Beispiel

class Base(DeclarativeBase):
pass

class Verlag(Base):
__tablename__ = "alc_verlag"

name: Mapped[str] = mapped_column(String(50), primary_key=True)
adresse: Mapped[str] = mapped_column(String(50), nullable=True)

Deklaration der Attribute

name: Mapped[str] = mapped_column(String(50), primary_key=True)
adresse: Mapped[str] = mapped_column(String(50), nullable=True)
isbn: Mapped[str] = mapped_column(String(13), primary_key=True)
auflage: Mapped[int] = mapped_column(primary_key=True)
erscheinungsjahr: Mapped[Date] = mapped_column(Date(), nullable=True)
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=False)

Minimalbeispiel:

Adressbuch mit einer Tabelle

class Address(Base):
  __tablename__ = "addressbook"

  id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
  first_name: Mapped[str] = mapped_column(String(50))
  last_name: Mapped[str] = mapped_column(String(50))
  birthday: Mapped[Date] = mapped_column(Date())

  def __init__(self, fname, lname, bday) -> None:
    self.first_name = fname
    self.last_name = lname
    self.birthday = bday
  
  def __repr__(self) -> str:
    return f"{self.last_name}, {self.first_name} ({self.birthday})"

Tabellen aller Entity Klassen werden durch OR Mapper erzeugt
- müssen/können nicht mehr manuell erzeugt werden
- Folgender Befehl erzeugt alle Tabellen, die von Base erben
  - Base.metadata.create_all(engine)

Arbeiten mit den Tabellen:

with Session(engine) as session:
  session.add(Address("Charles", "Dickens", date(1812, 2, 7)))
  session.add(Address("Edgar Allen", "Poe", date(1809, 1, 19)))
  session.add(Address("Douglas", "Adams", date(1952, 3, 11)))
  session.add(Address("Terry", "Pratchett", date(1948, 4, 28)))
  session.add(Address("Albert", "Einstein", date(1879, 3, 14)))
  session.add(Address("Marie", "Curie", date(1867, 11, 7)))
  session.add(Address("Werner", "Heisenberg", date(1901, 12, 5)))
  session.add(Address("Pierre", "Curie", date(1859, 3, 15)))

  session.commit()

Relationships

1:N Relationships

class Parent(Base):
  __tablename__ = "parent_table"
  
  id: Mapped[int] = mapped_column(primary_key=True)
  
  # Deklariert eine Liste von Child-Referenzen als Attribut, nicht jedoch als mapped column
  # Dies hat *keine* Repräsentation in der Datenbank!
  children: Mapped[list["Child"]] = relationship(back_populates="parent")

class Child(Base):
  __tablename__ = "child_table"
  
  id: Mapped[int] = mapped_column(primary_key=True)
  
  # Deklariert einen Fremdschlüssel als mapped column. Dieser wird anwendungsseitig *nicht* verwendet!
  # nullable=True kann gesetzt werden, falls 0..N gewünscht ist
  parent_id: Mapped[int] = mapped_column(ForeignKey("parent_table.id"), nullable=True)
  
  # Deklariert eine Parent-Referenz als Attribut, nicht jedoch als mapped column
  parent: Mapped["Parent"] = relationship(back_populates="children")

Attribute parent und children sind nur auf Anwendungsseite vorhanden
- back_populates sorgt dafür, dass Attribut der gegenüberliegenden Seite automatisch gefüllt wird
Fremdschlüssel werden durch OR-Mapper automatisch gesetzt

1:1 Relationships

Gleiches wie bei 1:N
- Mapped[list["Child"]] wird zu Mapped["Child"]

M:N Relationships

Zwischentabellen können nicht automatisch erzeugt werden
Explizite Deklaration
- Parameter secondary

association_table = Table(
  "association_table",
  Base.metadata,
  Column("left_id", ForeignKey("left_table.id"), primary_key=True),
  Column("right_id", ForeignKey("right_table.id"), primary_key=True),
)

class Parent(Base):
  __tablename__ = "left_table"
  
  id: Mapped[int] = mapped_column(primary_key=True)
  children: Mapped[list[Child]] = relationship(
    secondary=association_table, back_populates="parents"
)

class Child(Base):
  __tablename__ = "right_table"
  
  id: Mapped[int] = mapped_column(primary_key=True)
  parents: Mapped[list[Parent]] = relationship(
    secondary=association_table, back_populates="children"
)

Queries

Da Ergebnisse jetzt Objekte sind, kein "raw sql" mehr

Bspw:

verlag4 = session.get(Verlag, "Verlag 4")
print("Bücher von", verlag4.name)
books = session.scalars(select(Buch).where(Buch.erscheint_bei == verlag4))
for book in books:
  print(book)

Einfacher:

verlag4 = session.get(Verlag, "Verlag 4")
for book in verlag4.buecher:
  print(book)

16 KiB Raw Permalink Blame History Unescape Escape