Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- praktikum_informationssysteme [2014/12/09 08:46]
web1423 [Aufgabe]
+++ praktikum_informationssysteme [2015/02/23 09:29] (aktuell)
@@ Zeile 1: / Zeile 1: @@
 ====== Praktikum Informationssysteme ======
 Anmeldedaten Gowron:
   * Nachname auf 8 Stellen
   * Passwort: Matrikelnummer
 Kommandos:
   * psql: ruft SQL Interpreter auf
   * \q: beendet den SQL Interpreter
@@ Zeile 10: / Zeile 13: @@
   * \c [nutername]: zu Benutzer wechseln
   * \z: Tabellen eines Nutzers anzeigen
 Derjenige, der die Datenbank angelegt hat, hat das Recht, Rechte zu vergeben. Aber es können natürlich auch die Rechte auf die Datenbank vergeben werden. Kann ebenfalls auf Views angewendet werden.
-<code sql>grant select on WS14 to public;</code>
+<code sql>
+GRANT SELECT ON WS14 TO public;
+</code>
 Sichten sind wichtig, da die Daten immer aktuell sind, da sie keine echten Tabellen sind. So können andere Nutzer nur auf Teildaten einer Tabelle einfach zugreifen.
-<code sql>grant select on Dienstags to altaner;</code>
+<code sql>
+GRANT SELECT ON Dienstags TO altaner;
+</code>
 ===== Regelsystem =====
-Vorbereitungen:
-<code sql>create table Gegenstand(ID int, Bezeichnung varchar(30));
+Vorbereitungen:
-create table LogAnz(Anz int);
-insert into LogAnz values(0);</code>
+<code sql>
+CREATE TABLE Gegenstand(ID INT, Bezeichnung VARCHAR(30));
+CREATE TABLE LogAnz(Anz INT);
+INSERT INTO LogAnz VALUES(0);
+</code>
 Jedes mal soll bei insert automatisch eine Anzahl in LogAnz gelogged werden.
-<code sql>create rule protokoll
-as on insert to Gegenstand
+<code sql>
-do also update LogAnz set Anz = Anz+1;</code>
+CREATE rule protokoll
-also kann weggelassen werden, da also Standard ist.\\
+AS ON INSERT TO Gegenstand
-Test:
+do also UPDATE LogAnz SET Anz = Anz+1;
-<code sql> insert into Gegenstand values(1, 'Computer');
+</code>
-insert into Gegenstand values(2, 'Drucker');
-select * from LogAnz;</code>
+also kann weggelassen werden, da also Standard ist. \\ Test:
-Nun soll als Regel die ID aus Gegenstand, die bei insert erstellt wird auch in LogAnz erstellt werden. Dazu brauchen wir virtuelle Tabellen (new, old). New enthält die Werte und Spalten, die neu beim Abarbeiten der Regel hinzugekommen sind. Old sammelt die Werte, die von insert und delete betroffen waren. Bei update lassen sich beide Regeln benutzen. Zuerst wird die zuvor erstellte Regel entfernt und eine neue Tabelle Log erstellt:
-<code sql>drop rule protokoll on Gegenstand;
+<code sql>
-create table Log(ID int);</code>
+ INSERT INTO Gegenstand VALUES(1, 'Computer');
-<code sql>create rule protokoll
+INSERT INTO Gegenstand VALUES(2, 'Drucker');
-as on insert to Gegenstand
+SELECT * FROM LogAnz;
-do also insert into Log values(new.ID);</code>
+</code>
+Nun soll als Regel die ID aus Gegenstand, die bei insert erstellt wird auch in LogAnz erstellt werden. Dazu brauchen wir virtuelle Tabellen (new, old). **New**  enthält die Werte und Spalten, die neu beim Abarbeiten der Regel hinzugekommen sind. **Old**  sammelt die Werte, die von insert und delete betroffen waren. Bei update lassen sich beide Regeln benutzen. Zuerst wird die zuvor erstellte Regel entfernt und eine neue Tabelle Log erstellt:
+<code sql>
+DROP rule protokoll ON Gegenstand;
+CREATE TABLE Log(ID INT);
+</code>
+<code sql>
+CREATE rule protokoll
+AS ON INSERT TO Gegenstand
+do also INSERT INTO Log VALUES(NEW.ID);
+</code>
 Man kann bei Sichten kein insert anwenden, da es ja nur die (Teil-)Darstellung einer Tabelle ist. Möglich ist es, indem man via Regel in die eigentliche Tabelle einfügt:
-<code sql>create rule insertRegel as
-on insert to Dienstags do instead
+<code sql>
-insert into WS14 values(new.Wochentag, new.Beginn, new.Ende, new.Nr, new.Name, new.Dozent);</code>
+CREATE rule insertRegel AS
-Delete Regel lautet (bei der Where Bedingungen bietet sich immer der Primary Key an. Eigentlich müssten via and Bedingung alle Spalten definiert werden (old.Wochentag and ...):
+ON INSERT TO Dienstags do instead
-<code sql>create rule deleteRegel as
+INSERT INTO WS14 VALUES(NEW.Wochentag, NEW.Beginn, NEW.Ende, NEW.Nr, NEW.Name, NEW.Dozent);
-on delete to Dienstags do instead
+</code>
-delete from WS14 where Nr = old.Nr;</code>
+Delete Regel lautet (bei der Where Bedingungen bietet sich immer der Primary Key an. Eigentlich müssten via and Bedingung alle Spalten definiert werden (old.Wochentag and …):
+<code sql>
+CREATE rule deleteRegel AS
+ON DELETE TO Dienstags do instead
+DELETE FROM WS14 WHERE Nr = OLD.Nr;
+</code>
 Update:
-<code sql>create rule updateRegel as
-on update to Dienstags do instead
+<code sql>
-update WS14 set Wochentag=new.Wochentag,
+CREATE rule updateRegel AS
-Beginn=new.Beginn,
+ON UPDATE TO Dienstags do instead
-Ende=new.Ende,
+UPDATE WS14 SET Wochentag=NEW.Wochentag,
-Nr=new.Nr,
+Beginn=NEW.Beginn,
-Name=new.Name,
+Ende=NEW.Ende,
-Dozent=new.Dozent
+Nr=NEW.Nr,
-where Nr=old.Nr;</code>
+Name=NEW.Name,
+Dozent=NEW.Dozent
+WHERE Nr=OLD.Nr;
+</code>
 Test:
-<code sql>update Dienstags set Beginn = 11 where Dozent = 'Siegmund';
-select * from Dienstags;</code>
+<code sql>
-<code sql>delete from Dienstags where Name='Foo';</code>
+UPDATE Dienstags SET Beginn = 11 WHERE Dozent = 'Siegmund';
+SELECT * FROM Dienstags;
+</code>
+<code sql>
+DELETE FROM Dienstags WHERE Name='Foo';
+</code>
 Was macht folgende Regel:
-<code sql>create rule ReK as
-on insert to Doppelt do
+<code sql>
-insert into Doppelt values(new.A+1, new.B);</code>
+CREATE rule ReK AS
-Bei jedem Einfügen wird eine weitere Zeile einfügen und so weiter... D.h. diese Regel ist rekursiv. Mit do instead wird die Tabelle nicht überlaufen, da nur der zweite Teil der Regel ausgeführt wird. Instead wird das Problem allerdings vertagen, da gewartet wird.
+ON INSERT TO Doppelt do
+INSERT INTO Doppelt VALUES(NEW.A+1, NEW.B);
+</code>
+Bei jedem Einfügen wird eine weitere Zeile einfügen und so weiter… D.h. diese Regel ist rekursiv. Mit do instead wird die Tabelle nicht überlaufen, da nur der zweite Teil der Regel ausgeführt wird. Instead wird das Problem allerdings vertagen, da gewartet wird.
 ===== Schlüssel =====
-Wenn zwei Tabellen mittels primary und foreign key miteinander verbunden sind, kann es beim Update von Werten des Schlüssel beider Tabellen zu Problemen führen, auch wenn die Bedingung zwischen primary und foreign key beim Update inhaltlich nicht verletzt wird.\\
-<note tip>constraint dient dazu einen Namen vergeben zu können. So können z.B. Schlüssel ganz einfach wieder gelöscht werden.</note>
+Wenn zwei Tabellen mittels primary und foreign key miteinander verbunden sind, kann es beim Update von Werten des Schlüssel beider Tabellen zu Problemen führen, auch wenn die Bedingung zwischen primary und foreign key beim Update inhaltlich nicht verletzt wird. \\ <note tip>constraint dient dazu einen Namen vergeben zu können. So können z.B. Schlüssel ganz einfach wieder gelöscht werden.</note> Erstellen einer Tabelle und Verknüpfung mittels keys - Beispiel:
-Erstellen einer Tabelle und Verknüpfung mittels keys - Beispiel:
 <code sql>
-alter table WS14 add primary key(Nr);
+ALTER TABLE WS14 ADD PRIMARY KEY(Nr);
-create table HS13(Wochentag char(2), Uhrzeit int, Vorlesung varchar(6),
+CREATE TABLE HS13(Wochentag CHAR(2), Uhrzeit INT, Vorlesung VARCHAR(6),
-primary key(Wochentag, Uhrzeit),
+PRIMARY KEY(Wochentag, Uhrzeit),
-constraint Fremdschluessel
+CONSTRAINT Fremdschluessel
-foreign key(Vorlesung) references WS14);
+FOREIGN KEY(Vorlesung) REFERENCES WS14);
+INSERT INTO HS13 VALUES('Di', 10, '5102V');
+INSERT INTO HS13 VALUES('Di', 11, '5102V');
+</code>
-insert into HS13 values('Di', 10, '5102V');
-insert into HS13 values('Di', 11, '5102V');</code>
 Update beider Tabellen führt zu Problem, da Integritätsprüfung erst nach kurz vor dem commit ausgeführt werden soll:
 <code sql>
-begin transaction;
+BEGIN TRANSACTION;
-update WS14 set Nr = '5103V' where Nr = '5102V';
+UPDATE WS14 SET Nr = '5103V' WHERE Nr = '5102V';
-update HS13 set Vorlesung = '5103V' where Vorlesung = '5102V';
+UPDATE HS13 SET Vorlesung = '5103V' WHERE Vorlesung = '5102V';
-commit;</code>
+commit;
+</code>
 Daher muss bei der Definition des foreign key die Bedingung deferrable initially deferred gesetzt werden:
 <code sql>
-alter table HS13 drop constraint Fremdschluessel;
+ALTER TABLE HS13 DROP CONSTRAINT Fremdschluessel;
-alter table HS13 add constraint Fremdschluessel
+ALTER TABLE HS13 ADD CONSTRAINT Fremdschluessel
-foreign key(Vorlesung) references WS14 deferrable initially deferred;</code>
+FOREIGN KEY(Vorlesung) REFERENCES WS14 deferrable initially deferred;
+</code>
 Zweite Variante: Definieren der Fremschlüsselbedingung als verzögerbar, aber sofort standardmäßig als sofort-ausführend, um dannbeim Ausführen einer Transaktion zu definieren, dass die Bedingung verzögert werden soll.
 <code sql>
-alter table HS13 add constraint Fremdschluessel
+ALTER TABLE HS13 ADD CONSTRAINT Fremdschluessel
-foreign key(Vorlesung) references WS14 deferrable initially immediate;
+FOREIGN KEY(Vorlesung) REFERENCES WS14 deferrable initially immediate;
-begin transaction;
+BEGIN TRANSACTION;
-set constraints Fremdschluessel deferred;
+SET constraints Fremdschluessel deferred;
-update WS14 set Nr = '5102V' where Nr = '5103V';
+UPDATE WS14 SET Nr = '5102V' WHERE Nr = '5103V';
-update HS13 set Vorlesung = '5102V' where Vorlesung = '5103V';
+UPDATE HS13 SET Vorlesung = '5102V' WHERE Vorlesung = '5103V';
-commit;</code>
+commit;
+</code>
 <note tip>deferrable definiert, dass eine Aktion verzögert ausgeführt werden darf. initially meint, was beim Start der Aktion ausgeführt werden soll. Dazu dienen die Parameter immediate (sofort) und deferred (verzögert).</note>
 ===== Transaktionen =====
-Zwei Insert-Aktionen werden parallel mit Transaktionen gestartet.\\ Beispiel 1. Fenster:
+Zwei Insert-Aktionen werden parallel mit Transaktionen gestartet. \\ Beispiel 1. Fenster:
 <code sql>
-begin;
+BEGIN;
-insert into WS14 values('Fr',10,12,'5555V','Java','Mueller');
+INSERT INTO WS14 VALUES('Fr',10,12,'5555V','Java','Mueller');
 commit;
 </code>
 Beispiel 2. Fenster (parallel):
 <code sql>
-begin;
+BEGIN;
-insert into WS14 values('Fr',10,12,'5555V','SQL','Mueller');
+INSERT INTO WS14 VALUES('Fr',10,12,'5555V','SQL','Mueller');
 </code>
-Mit jeweils select * from WS14 sehen wir bereits die eingefügten Werte. Was passiert nun, sobald commit; eingegeben wird? Per Voreinstellung wissen andere Transaktionen, was gerade passiert.\\
-Es werden zwei Transaktionen geöffnet und jeweils werden bei einem Insert die gleichen Primary Keys eingegeben. Ein Insert funktioniert, beim zweiten wird das Prozedere angehalten, da die eine Transaktion wartet, ob die andere committed oder aborted. Je nach dem, was nun passiert, meldet die andere Transaktion einen Fehler oder einen Erfolg.\\
+Mit jeweils select * from WS14 sehen wir bereits die eingefügten Werte. Was passiert nun, sobald commit; eingegeben wird? Per Voreinstellung wissen andere Transaktionen, was gerade passiert. \\ Es werden zwei Transaktionen geöffnet und jeweils werden bei einem Insert die gleichen Primary Keys eingegeben. Ein Insert funktioniert, beim zweiten wird das Prozedere angehalten, da die eine Transaktion wartet, ob die andere committed oder aborted. Je nach dem, was nun passiert, meldet die andere Transaktion einen Fehler oder einen Erfolg.
 ==== Isolationsgrade ====
-Voreinstellung für Transaktionen ist read committed .
-<note tip>Isolationsgrade bestimmen, was andere Transaktionen von dem was um sie herum passiert, sehen können.</note>
+Voreinstellung für Transaktionen ist read committed . <note tip>Isolationsgrade bestimmen, was andere Transaktionen von dem was um sie herum passiert, sehen können.</note>
-  * read committed: andere Transaktionen können Lesen, was passiert.
-  * serializable: Abschotten der Transaktionen von der Außenwelt, so als wären sie hintereinander ausgeführt worden, aber lost update problem.
+  * read committed: andere Transaktionen können Lesen, was passiert, aber lost update problem.
+  * serializable: Abschotten der Transaktionen von der Außenwelt, so als wären sie hintereinander ausgeführt worden.
 <code sql>
-begin;
+BEGIN;
-set transaction isolation level serializable;
+SET TRANSACTION isolation level serializable;
-insert into WS14 values('Di', 16,18,'5500V','Datenbanken','Schulze');
+INSERT INTO WS14 VALUES('Di', 16,18,'5500V','Datenbanken','Schulze');
 commit;
 </code>
-Obwohl eine Transaktion mit commit; abgeschlossen wird, sieht die andere Transaktion bei serializable das nicht. Was passiert nun bei einem update-Befehl in zwei Transaktionen, die das gleiche updaten wollen?
-Mit serializable wartet die andere Transaktion, was die erste macht, d.h. hier kommt auch ein Error, wenn die erste Transaktion committed.\\
+Obwohl eine Transaktion mit commit; abgeschlossen wird, sieht die andere Transaktion bei serializable das nicht. Was passiert nun bei einem update-Befehl in zwei Transaktionen, die das gleiche updaten wollen? Mit serializable wartet die andere Transaktion, was die erste macht, d.h. hier kommt auch ein Error, wenn die erste Transaktion committed. \\ Bei read committed kommt kein Fehler, sondern es wird ein update ausgeführt, aber die erste Transaktion hat das update erfolgreich ausgeführt (lost update Problem).
-Bei read committed kommt kein Fehler, sondern es wird ein update ausgeführt, aber die erste Transaktion hat das update erfolgreich ausgeführt (lost update Problem).
 ==== Sperren ====
   * Es gibt 8 verschiedene Sperren: Tabellen- und Zeilensperren
   * Sperren bleiben bis zum Ende der Transaktion erhalten und können nicht entsperrt werden (2-Phasen-Sperr-Protokoll)
   * Sperren sind im Grunde nur Namen, die auf eine Tabelle geklebt wird. Es wird also nur definiert, welche Sperre mit welcher in Konflikt steht
   * Bestimmte SQL-Befehle richten automatisch bestimmte Sperren ein, aber Tabellensperren können auch individuell festgelegt werden
 === Übersicht möglicher Sperren ===
-{{::sperren-sql.jpg?nolink|}}
+{{:sperren-sql.jpg?nolink&}}
 === Automatische Sperren ===
-  * ACCESS SHARE:  SELECT
-  * ROW SHARE:	SELECT FOR UPDATE
+  * ACCESS SHARE: SELECT
-  * ROW EXCLUSIVE:	UPDATE, DELETE, INSERT
+  * ROW SHARE: SELECT FOR UPDATE
-  * SHARE UPDATE EXCLUSIVE:	VACUUM
+  * ROW EXCLUSIVE: UPDATE, DELETE, INSERT
-  * SHARE:	CREATE INDEX
+  * SHARE UPDATE EXCLUSIVE: VACUUM
-  * SHARE ROW EXCLUSIVE:	-
+  * SHARE: CREATE INDEX
-  * EXCLUSIVE:	-
+  * SHARE ROW EXCLUSIVE: -
-  * ACCESS EXCLUSIVE:	ALTER TABLE, DROP TABLE
+  * EXCLUSIVE: -
+  * ACCESS EXCLUSIVE: ALTER TABLE, DROP TABLE
 === Anwendung ===
 Beispiel für Sperren anfordern:
 <code sql>
-lock WS14 in row exclusive mode;
+LOCK WS14 IN ROW exclusive mode;
 /*Struktur: lock [tabellenname] in [modusname] mode */
 </code>
   * sukzessiv können mehrere Sperren angefordert werden
   * Wenn durch Einsatz von Sperren bei Transaktionen in Konflikt stehen, wartet die andere Transaktion
@@ Zeile 151: / Zeile 234: @@
   * Sperren innerhalb derselben Transaktion stehen nie in Konflikt
   * Wenn sich Sperren gegenseitig blockieren, wird eine Transaktion beendet (dead lock)
 === Beispiel für einen Dead Lock ===
 Transaktion 1
 <code sql>
-begin;
+BEGIN;
-select * from WS14;
+SELECT * FROM WS14;
-alter table WS14 add Zusatz int;
+ALTER TABLE WS14 ADD Zusatz INT;
 /* wartet... */
 </code>
 Transaktion 2
 <code sql>
-begin;
+BEGIN;
-select * from WS14;
+SELECT * FROM WS14;
-alter table WS14 add bla int;
+ALTER TABLE WS14 ADD bla INT;
 /* abgebrochen wegen Deadlock */
 </code>
-=> Dead lock kann durch starke Sperren verhindert werden.
+⇒ Dead lock kann durch starke Sperren verhindert werden.
 ==== Vererbung ====
 Wiederholung: 3 Tabellen erstellen
 <code sql>
-create table Uni_Bedienstet(PersNr int, Vorname varchar(30), Name varchar(30), ZiNr int, TelNr int);
+CREATE TABLE Uni_Bedienstet(PersNr INT, Vorname VARCHAR(30), Name VARCHAR(30), ZiNr INT, TelNr INT);
-create table Professor(Lehrgebiet varchar(30), Status char(2)) inherits(Uni_Bedienstet);
+CREATE TABLE Professor(Lehrgebiet VARCHAR(30), STATUS CHAR(2)) inherits(Uni_Bedienstet);
 /* inherits sagt, dass alle Attribute von Professor an Uni_Bedienstet vererbt werden */
-create table Assistent(Projekt varchar(30), Vertragsende date) inherits(Uni_Bedienstet);
+CREATE TABLE Assistent(Projekt VARCHAR(30), Vertragsende DATE) inherits(Uni_Bedienstet);
-insert into Uni_Bedienstet values (12345, 'Klaus', 'Maier', 210, 2016);
+INSERT INTO Uni_Bedienstet VALUES (12345, 'Klaus', 'Maier', 210, 2016);
-insert into Professor values(12346, 'Helga', 'Huber' 314, 2020, 'Politik', 'W2');
+INSERT INTO Professor VALUES(12346, 'Helga', 'Huber' 314, 2020, 'Politik', 'W2');
-insert into Assistent values(12347, 'Uwe', 'Schulze',221,2160, 'SQL-Projekt', '31.03.2015');
+INSERT INTO Assistent VALUES(12347, 'Uwe', 'Schulze',221,2160, 'SQL-Projekt', '31.03.2015');
 </code>
 <note tip>Reihenfolge bei Insert in table mit Vererbung: erst die Generalisierung, dann die eigenen Attribute.</note>
 === Select mit vererbten Tabellen ===
-Mit select * from Uni_Bedienstet sind alle Personen aufgelistet, da diese ja vererbt werden. Bei den jeweiligen anderen Tabellen sind nur die einzelnen Personeneinträge gelistet. D.h. bei insert werden die Spezialisierungen in der Generalisierung angezeigt, auch wenn diese nicht Teil davon sind.\\
-Eine Variante des select Befehls: only. Dann werden keine Vererbungen bei select ausgegeben, sondern nur Einträge in dieser Tabelle.
+Mit select * from Uni_Bedienstet sind alle Personen aufgelistet, da diese ja vererbt werden. Bei den jeweiligen anderen Tabellen sind nur die einzelnen Personeneinträge gelistet. D.h. bei insert werden die Spezialisierungen in der Generalisierung angezeigt, auch wenn diese nicht Teil davon sind. \\ Eine Variante des select Befehls: only. Dann werden keine Vererbungen bei select ausgegeben, sondern nur Einträge in dieser Tabelle.
 <code sql>
-select * from only Uni_Bedienstet;
+SELECT * FROM ONLY Uni_Bedienstet;
 </code>
 === Update / Delete mit vererbten Tabellen ===
-Wenn aus vererbten Tabellen Einträge gelöscht werden, sind diese auch in der generalisierten Tabelle gelöscht.\\
-Sollte ein Wert mit Update aktualisiert werden, wird dieser auch in der Generalisierung sichtbar.
+Wenn aus vererbten Tabellen Einträge gelöscht werden, sind diese auch in der generalisierten Tabelle gelöscht. \\ Sollte ein Wert mit Update aktualisiert werden, wird dieser auch in der Generalisierung sichtbar.
 === Beispiel: eine Spezialisierung und 2 Generalisierungen ===
-{{::vererbung.png?nolink|}}\\
-Mögliche Probleme: Wenn 2 Datentypen gleich sind bei der Generalisierung, denn in der Spezialisierung werden nur Werte angezeigt, während sie ja lediglich in den Generalisierungen fest geschrieben werden. Dort besteht ja dann kein Problem. \\
+{{:vererbung.png?nolink&}} \\ Mögliche Probleme: Wenn 2 Datentypen gleich sind bei der Generalisierung, denn in der Spezialisierung werden nur Werte angezeigt, während sie ja lediglich in den Generalisierungen fest geschrieben werden. Dort besteht ja dann kein Problem. \\ Mehrfache Vererbung ist möglich. Es geht so lange, wie alle gemeinsamen Datentypen in den Generalisierungen gleiche Datentypen haben. Sollte diese allerdings unterschiedlich sein, gibt es ein Problem.
-Mehrfache Vererbung ist möglich. Es geht so lange, wie alle gemeinsamen Datentypen in den Generalisierungen gleiche Datentypen haben. Sollte diese allerdings unterschiedlich sein, gibt es ein Problem.
 === Zusatzspalte: Woher kommen die Werte ===
-OIDs: Object Identifier. Identifieren jedes Objekt in der Datenbank und vergeben eine eindeutige Nummer.
-Beispiel für eine OID: INSERT 39654 [1(=Anzahl der Zeilen)].
+OIDs: Object Identifier. Identifieren jedes Objekt in der Datenbank und vergeben eine eindeutige Nummer. Beispiel für eine OID: INSERT 39654 [1(=Anzahl der Zeilen)].
 <code sql>
-select oid, * from Uni_Bedienstet;
+SELECT oid, * FROM Uni_Bedienstet;
 </code>
 Table-OID: Eindeutige Nummer für Tabellen.
 <code sql>
-select oid, tableoid, * from Uni_Bedienstet;
+SELECT oid, tableoid, * FROM Uni_Bedienstet;
 </code>
 Aber Zahlen sind wirr, d.h. es gibt auch Systemtabellen, die die Namen der jeweiligen Tabellen ausgeben.
 <code sql>
-select relname as Tabelle, Uni_Bedienstet, * from Uni_Bedienstet, pg_class
+SELECT relname AS Tabelle, Uni_Bedienstet. * FROM Uni_Bedienstet, pg_class
-where Uni_Bedienstet.tableoid = pg_class.oid;
+WHERE Uni_Bedienstet.tableoid = pg_class.oid;
 </code>
 ===== Funktionen SQL =====
 Beispiele:
 <code sql>
-select 3+4:
+SELECT 3+4:
 /* ergibt eine Spalte mit Ergebnis 7*/
-select sqrt(2);
+SELECT SQRT(2);
 </code>
   * Funkionen: Haben ein Argument, eine Zahl und ein Ergebnis.
   * Aggregatfunktionen: Nehmen mehrere Werte und verdichten diese zu einem Wert, bsp. die Summer einer Spalte.
 ==== Aufbau einer Funktion ====
-Eine Artikeltabelle soll bei Verkaufspreis 19% Mwst. erhalten.\\
-Möglichkeit 1:
+Eine Artikeltabelle soll bei Verkaufspreis 19% Mwst. erhalten. \\ Möglichkeit 1:
 <code sql>
-select Bezeichnung, Verkaufspreis as Netto,
+SELECT Bezeichnung, Verkaufspreis AS Netto,
-  Verkaufspreis * 0.19 as Mwst,
+  Verkaufspreis * 0.19 AS Mwst,
-  Verkaufspreis + Verkaufspreis * 0.19 as Brutto from Artikel;
+  Verkaufspreis + Verkaufspreis * 0.19 AS Brutto FROM Artikel;
 </code>
 Möglichkeit 2:
 <code sql>
-create function mwst(numeric) returns numeric as $$
+CREATE FUNCTION mwst(NUMERIC) RETURNS NUMERIC AS $$
-    select round($1 * 0.19, 2);
+    SELECT round($1 * 0.19, 2);
-$$ language sql;
+$$ LANGUAGE SQL;
 </code>
   * Die eingesetzten Datentypen müssen nicht gleich sein, d.h. es kann ein anderer Datentyp returned werden, als ursprünglich eingegeben wird.
   * Dollar 1: Platzhalter für das erste Argument, das übergeben wird.
   * language muss definiert werden, da mehrere Sprachen möglich sind.
-  * Dollar Dollar: eine Art von Anführungszeichen, die die Funktion definieren.\\
+  * Dollar Dollar: eine Art von Anführungszeichen, die die Funktion definieren.
 <code sql>
-select
+SELECT
     Artikelnr,
     Bezeichnung,
-    Verkaufspreis as Netto,
+    Verkaufspreis AS Netto,
-    mwst(Verkaufspreis) as MwSt,
+    mwst(Verkaufspreis) AS MwSt,
-    Verkaufspreis + mwst(Verkaufspreis) as Brutto
+    Verkaufspreis + mwst(Verkaufspreis) AS Brutto
-from Artikel;
+FROM Artikel;
 </code>
 Erweiterung round:
 <code sql>
-select ... as Netto, round(mwst(Verkaufspreis),2) as mwst, round(...,2) as Brutto from Artikel;
+SELECT ... AS Netto, round(mwst(Verkaufspreis),2) AS mwst, round(...,2) AS Brutto FROM Artikel;
 </code>
 ==== SQL Procedural Language ====
 Aufgabe 1: String mit jeweils einem Leerzeichen zwischen den Buchstaben ausgeben.
 <code sql>
-create function spaceout(varchar) returns varchar as $$
+CREATE FUNCTION spaceout(VARCHAR) RETURNS VARCHAR AS $$
-declare
+DECLARE
-    str varchar;
+    str VARCHAR;
-    ret varchar;
+    ret VARCHAR;
-    len int;
+    len INT;
-begin
+BEGIN
-    str:= upper($1); /* Alles in Großbuchstaben */
+    str:= UPPER($1); /* Alles in Großbuchstaben */
     ret:= '';
-    len:= length(str);
+    len:= LENGTH(str);
-    for i in 1..len loop
+    FOR i IN 1..len loop
        ret:= ret || substr(str, i, 1) || ' ';
-    end loop;
+    END loop;
-    return ret;
+    RETURN ret;
-end;
+END;
-$$ language plpgsql;
+$$ LANGUAGE plpgsql;
 </code>
-Aufgabe 2:
-Eine Funktion soll 2 String auf Ähnlichkeit überprüfen und die unterschiedlichen Stellen sollen als Anzahl ausgegeben werden. => akin
+Aufgabe 2: Eine Funktion soll 2 String auf Ähnlichkeit überprüfen und die unterschiedlichen Stellen sollen als Anzahl ausgegeben werden. ⇒ akin
   * abs: absolute Zahl
   * <>: ungleich
 <code sql>
-create function diff(varchar, varchar) returns int as $$
+CREATE FUNCTION diff(VARCHAR, VARCHAR) RETURNS INT AS $$
-declare
+DECLARE
-    len1 int;
+    len1 INT;
-    len2 int;
+    len2 INT;
-    minLen int;
+    minLen INT;
-    diffLen int;
+    diffLen INT;
-    diffCount int;
+    diffCount INT;
-begin
+BEGIN
-    len1:= length($1);
+    len1:= LENGTH($1);
-    len2:= length($2);
+    len2:= LENGTH($2);
-    if len1 < len2 then
+    IF len1 < len2 THEN
         minLen:= len1;
-    else
+    ELSE
         minLen:= len2;
-    end if;
+    END IF;
     diffLen:= abs(len1 - len2);
     diffCount:= 0;
-    for i in 1..minLen loop
+    FOR i IN 1..minLen loop
-       if substr($1, i, 1) <> substr($2, i, 1) then
+       IF substr($1, i, 1) <> substr($2, i, 1) THEN
            diffCount:= diffCount + 1;
-       end if;
+       END IF;
-    end loop;
+    END loop;
     diffCount:= diffCount + diffLen;
-    return diffCount;
+    RETURN diffCount;
-end;
+END;
-$$ language plpgsql;
+$$ LANGUAGE plpgsql;
-create function akin(varchar, varchar) returns boolean as $$
+CREATE FUNCTION akin(VARCHAR, VARCHAR) RETURNS BOOLEAN AS $$
-    select diff($1, $2) <= 1;
+    SELECT diff($1, $2) <= 1;
-$$ language sql;
+$$ LANGUAGE SQL;
 </code>
 ==== Operatoren definieren ====
 <code sql>
-create operator ~= (
+CREATE operator ~= (
-    procedure = akin,
+    PROCEDURE = akin,
-    leftarg = varchar,
+    leftarg = VARCHAR,
-    rightarg = varchar);
+    rightarg = VARCHAR);
 </code>
-Möglich sind dann Abfragen der Art
-select * from Artikel where Hersteller ~= 'Maierhofer';
+Möglich sind dann Abfragen der Art select * from Artikel where Hersteller ~= 'Maierhofer';
 ===== Trigger =====
-Sind Ereignisse, die etwas auslösen.\\
-Erst muss eine Funktion erstellt werden.
+Sind Ereignisse, die etwas auslösen. \\ Erst muss eine Funktion erstellt werden.
 <code sql>
-create function capitalize() returns trigger as $$
+CREATE FUNCTION capitalize() RETURNS TRIGGER AS $$
 /* keine Parameter müssen eingegeben werden */
-begin
+BEGIN
-  new.bezeichnung = initcap(new.bezeichnung);
+  NEW.bezeichnung = initcap(NEW.bezeichnung);
   /* Auf new Tabellen können Änderungen vorgenommen werden */
-  return new;
+  RETURN NEW;
-end $$ language plpgsql;
+END $$ LANGUAGE plpgsql;
 </code>
 Daraus wird nun der Trigger erstellt:
 <code sql>
-create trigger trigger_cap
+CREATE TRIGGER trigger_cap
-before insert or update
+BEFORE INSERT OR UPDATE
-on aritkel
+ON aritkel
-for each row
+FOR each ROW
 /*standardmäßig*/
-execute procedure capitalize();
+EXECUTE PROCEDURE capitalize();
 </code>
-==== Aufgabe ====
+==== Aufgabe 1 ====
 Tabelle mit Terminen erstellen und bei insert oder update sollen die Wochentage auch in englisch verstanden werden, aber automatisch auf deutsche Abkürzungen übertragen werden.
-Ein Befehl in SQL wird ganz oder gar nicht ausgeführt.
+<code sql>
+CREATE TABLE Termine(Wochentag VARCHAR(3), Uhrzeit INT, Notiz VARCHAR(100));
+INSERT INTO Termine VALUES
+('Mo', 10, 'Besprechung'),
+('Di', 8, 'Wichtig: Abgabeschluss Report'),
+('Di', 14, 'Mit dem Chef wichtige Details zum Projekt bereden'),
+('Di', 15, 'Maier kontaktieren'),
+('Fr', 17, 'Dringend: Geschenke besorgen');
+</code>
+Erstellen der Funktion und des Triggers:
+<code sql>
+CREATE FUNCTION tage_en2de() RETURNS TRIGGER AS $$
+DECLARE
+    tag VARCHAR;
+BEGIN
+    NEW.wochentag = initcap(NEW.wochentag);
+    tag = NEW.wochentag;
+    IF tag ='Mon' THEN
+        tag = 'Mo';
+    elsif tag = 'Tue' THEN
+        tag = 'Di';
+    elsif tag = 'Wed' THEN
+        tag = 'Mi';
+    elsif tag = 'Thu' THEN
+        tag = 'Do';
+    elsif tag = 'Fri' THEN
+        tag = 'Fr';
+    elsif tag = 'Sat' THEN
+        tag = 'Sa';
+    elsif tag = 'Sun' THEN
+        tag = 'So';
+    elsif (tag = 'Mo') OR (tag = 'Di') OR (tag = 'Mi') OR
+          (tag = 'Do') OR (tag = 'Fr') OR (tag = 'Sa') OR (tag = 'So') THEN
+        -- nichts zu tun
+    ELSE
+        raise exception 'Fehlerhafter Wochentag';
+    END IF;
+    NEW.wochentag = tag;
+    RETURN NEW;
+END $$ LANGUAGE plpgsql;
+CREATE TRIGGER trigger_wochentage
+BEFORE INSERT OR UPDATE
+ON termine
+FOR each ROW
+EXECUTE PROCEDURE tage_en2de();
+</code>
+==== Aufgabe 2 ====
+Sorgen Sie jetzt noch dafür, dass Termine, in deren Notiz die Zeichenfolge "wichtig" vorkommt, nicht mehr gelöscht werden können!
+<code sql>
+CREATE FUNCTION wichtig_schuetzen() RETURNS TRIGGER AS $$
+BEGIN
+    IF OLD.notiz ~* 'wichtig' THEN
+    /* ~* bedeutet, dass wichtig in der Zeichenkette vorkommen muss */
+        raise exception 'Kann wichtige Termine nicht loeschen!';
+    END IF;
+    RETURN OLD;
+END $$ LANGUAGE plpgsql;
+CREATE TRIGGER trigger_wichtig
+BEFORE DELETE
+ON termine
+FOR each ROW
+EXECUTE PROCEDURE wichtig_schuetzen();
+</code>
+Test mit Löschen eines 14 Uhr Termins, wobei 2 14 Uhr Termine existieren, wovon nur einer mit wichtig markiert ist: Kein Termin wird gelöscht, da ein Befehl in SQL ganz oder gar nicht ausgeführt wird.
+===== PHP und Datenbanken =====
+In einer Datei auf dem Server, die auf html endet, kann nur html Code eingefügt werden, d.h. .php als Dateiendung notwendig.
+==== PHP Grundlagen ====
+  * PHP aufrufen: <?php , PHP   beenden: ?>
+  * Textausgabe: echo "String". In der Textausgabe mit echo kann sowohl ein normaler String als auch HTML oder Javascript Code stehen. Um in einem solchen String ein Hochkommata oder andere Programmiercodes einzufügen, muss davor ein \ stehen. **Einfache Hochkommata** meinen: Nimm alles wörtlich, d.h. \n wird kein Zeilenumbruch ausgeben. Aber mittels Konkatenation kann \n hinzugefügt werden: echo '<sdfsdf>' . "\n";
+  * Zeilenumbruch: \n
+  * Variablen definieren: $[Variablenname]
+  * Konkatenation: Was in Java ein + ist, ist in php ein Punkt
+Funktion definieren:
+<code php>
+function verdoppeln($wert)
+{ return $wert * 2;
+ }
+</code>
+==== Formular erstellen ====
+Jedes Formular braucht ein action und method Attribut. input type sorgt für Eingabefelder. Der Absendebutton wird über input type="submit" hinzugefügt.
+Bei action in Form ist angegeben: <?php echo $_SERVER['PHP_SELF']; ?>
+  * $_SERVER: Ein Array, in dem viele Servereigenschaften gespeichert sind
+  * ['PHP_SELF']: Array an der Position PHP_SELF wird aufgerufen (Array-Zeile ist mit Namen gekennzeichnet). PHP_SELF beschreibt die derzeitige PHP-Datei, also wird die gerade aufgerufene PHP-Datei aufgerufen.
+  * $_POST: Ebenfalls ein Arraystandard, in dem Zeilen aus dem Array ausgegeben werden, die via Post Methode bei Forms übergeben wurden. Diese Zeilen heissen wie die input Elemente.
+==== Verbindung aufbauen und Anfragen ====
+  * Wichtig: Encoding der Datenbank muss das selbe Encoding der PHP-Ausgabe haben.
+  * Verbindung herstellen: [$conn=]pg_connect("host=gowron.fim.uni-passau.de dbname=roeder user=roeder password=0815");
+  * Anfrage an Datenbank: pg_query($conn. "select * from Wein;");
+  * Tabellenbesonderheiten von php: pg_num_rows (zeilenanzahl), pg_num_fields (spaltenanzahl)
+  * Ergebnis einer Tabellenzelle: pg_fetch_result($variable, $i, $j);
+  * Überschrift einer Spalte: pg_field_name

Doku

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Unterschiede

Seiten-Werkzeuge