Volltext
Werbung
Ein Elefant vergisst nicht Erweiterte Funktionen in PostgreSQL am Beispiel der CSN Datenbank Daniel Schreiber Chemnitzer StudentenNetz https://www.csn.tu-chemnitz.de Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 1 Überblick ● Überblick CSN Inhalt: n SQL PostgreSQL n PostgreSQL im CSN n Referenzen n Kurzer Blick ins CSN Relationales Datenmodell und SQL PostgreSQL als (Objekt-)relationales Datenbanksystem PostgreSQL im CSN Nicht behandelt wird: n n n Daniel Schreiber, 28. April 2004 Installation eines Servers Zugriff aus Programmiersystemen (Perl, PHP, ...) Objektrelationale Fähigkeiten von PostgreSQL PostgreSQL im CSN - p. 2 ● Überblick CSN ● Szenario ● Umgebung der Datenbank ● Anforderungen SQL PostgreSQL Einblick ins CSN PostgreSQL im CSN Referenzen Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 3 Szenario ● Überblick n CSN n ● Szenario ● Umgebung der Datenbank ● Anforderungen n SQL n ca. 1500 Hosts im CSN ca. 80 aktive Netzgeräte Trafficaccounting Verwaltungsdaten für Routinearbeiten PostgreSQL PostgreSQL im CSN Referenzen Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 4 Umgebung der Datenbank ● Überblick n CSN ● Szenario ● Umgebung der Datenbank ● Anforderungen n SQL PostgreSQL n PostgreSQL im CSN Referenzen Daniel Schreiber, 28. April 2004 n Alle Informationen werden zentral in PostgreSQL Datenbank gespeichert Netzwerkhardware wird automatisch gemanaged (Port security, static ARP) Cronjobs erledigen Routineaufgaben (IPs beantragen, Mail verschicken, Netzconfiguration, Firewall, DCHP etc.) Dateneingabe über Webinterface PostgreSQL im CSN - p. 5 Anforderungen ● Überblick n CSN ● Szenario ● Umgebung der Datenbank ● Anforderungen SQL PostgreSQL n n PostgreSQL im CSN Referenzen Daniel Schreiber, 28. April 2004 n Ohne Datenbank ist das CSN nicht arbeitsfähig u ACID (Atomicity, Consistency, Isolation, Durability) u die Datenbank hat immer Recht Änderungen werden in Logtabellen protokolliert Komplizierte Aufgaben werden von stored Procedures erledigt → API Strukturdump “wiegt” ca. 320 kBytes PostgreSQL im CSN - p. 6 ● Überblick CSN SQL ● Relationen ● Beispiel ● Beispiel ● Verknüpfungen (JOINS) ● Gruppieren ● Daten bearbeiten Wiederholung: Relationales Datenmodell und SQL PostgreSQL PostgreSQL im CSN Referenzen Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 7 Relationen ● Überblick n CSN n SQL ● Relationen ● Beispiel ● Beispiel ● Verknüpfungen (JOINS) ● Gruppieren ● Daten bearbeiten n n Relationale Datenbanken speichern Werte in Tabellen Tabellen haben festgelegte Anzahl Spalten beliebig viele Zeilen (Tupel) über Anfragesprache können Einträge in Spalten ausgewählt, gruppiert, sortiert und verknüpft werden PostgreSQL PostgreSQL im CSN Referenzen Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 8 Beispiel ● Überblick CSN SQL ● Relationen ● Beispiel ● Beispiel ● Verknüpfungen (JOINS) ● Gruppieren ● Daten bearbeiten PostgreSQL PostgreSQL im CSN Referenzen n n Daniel Schreiber, 28. April 2004 ID Vorname Nachname Geschlecht 1 2 3 Ismael Vinzenz Edithe Blanckenhagen Hillebrandt Follenius m m f Alter 23 42 65 Kontonummer Eigentümer Kontostand 1234 1235 1236 1238 1 2 1 3 29338 9812347 253 124723 ID und Kontonummer sind Primärschlüssel Eigentümer ist Fremdschlüssel PostgreSQL im CSN - p. 9 Beispiel ● Überblick CSN SQL ● Relationen ● Beispiel ● Beispiel ● Verknüpfungen (JOINS) ● Gruppieren ● Daten bearbeiten PostgreSQL PostgreSQL im CSN Referenzen Daniel Schreiber, 28. April 2004 ID Vorname Nachname Geschlecht 1 2 3 Ismael Vinzenz Edithe Blanckenhagen Hillebrandt Follenius m m f Kontonummer Kontostand 1234 1235 1236 1238 29338 9812347 253 124723 Alter 23 42 65 Kontoinhaber Konto 1 2 1 3 3 1234 1235 1236 1238 1236 PostgreSQL im CSN - p. 10 Verknüpfungen (JOINS) ● Überblick CSN SQL ● Relationen ● Beispiel ● Beispiel ● Verknüpfungen (JOINS) ● Gruppieren ● Daten bearbeiten PostgreSQL PostgreSQL im CSN Referenzen erzeugen aus mehreren Tabellen neue (virtuelle) Tabelle n Verknüfungskriterium (fast immer Gleichheit von Primär- und Fremdschüssel) SELECT Vorname, Nachname, Kontostand, Kontonummer FROM person JOIN konto ON person.ID=konto.Eigentuemer; n Vorname Nachname Ismael Ismael Vinzenz Edithe Blanckenhagen Blanckenhagen Hillebrandt Follenius Kontostand Kontonummer 29338 253 9812347 124723 1234 1236 1235 1238 Wieviel Geld besitzt Ismael Blanckenhagen? Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 11 Gruppieren ● Überblick CSN SQL ● Relationen ● Beispiel ● Beispiel ● Verknüpfungen (JOINS) ● Gruppieren ● Daten bearbeiten PostgreSQL PostgreSQL im CSN Referenzen durch Gruppierung können mehrere Zeilen (Tupel) miteinander verrechnet werden n Verrechnung geschieht durch Aggregatfunktion n in PostgreSQL kann man eigene Aggregatfunktionen definieren SELECT Vorname, Nachname, sum(Kontostand) FROM person JOIN konto ON person.ID=konto.Eigentuemer GROUP BY Vorname, Nachname; Vorname Nachname Kontostand n Ismael Vinzenz Edithe Daniel Schreiber, 28. April 2004 Blanckenhagen Hillebrandt Follenius 29591 9812347 124723 PostgreSQL im CSN - p. 12 Daten bearbeiten ● Überblick n CSN SQL ● Relationen ● Beispiel ● Beispiel ● Verknüpfungen (JOINS) n n INSERT INTO tabelle (spalte1, spalte2) VALUES (wert1, wert2); UPDATE tabelle set spalte1=wert1 WHERE bedingung; DELETE FROM tabelle WHERE bedingung; ● Gruppieren ● Daten bearbeiten PostgreSQL PostgreSQL im CSN Referenzen Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 13 ● Überblick CSN SQL PostgreSQL ● Was ist PostgreSQL ● Transaktionen ● Transaktionsisolierung ● Views Der Elefant betritt die Bühne ● Rules ● Stored Procedures ● Trigger PostgreSQL im CSN Referenzen Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 14 Was ist PostgreSQL ● Überblick n CSN n SQL PostgreSQL ● Was ist PostgreSQL ● Transaktionen ● Transaktionsisolierung ● Views ● Rules ● Stored Procedures ● Trigger PostgreSQL im CSN OID Verwaltungssystem Objektrelationale Datenbank u Referentielle Integrität u Transaktionen u Views u Rules u Stored Procedures u Trigger Referenzen Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 15 Transaktionen ● Überblick CSN SQL PostgreSQL ● Was ist PostgreSQL ● Transaktionen ● Transaktionsisolierung ● Views ● Rules ● Stored Procedures ● Trigger PostgreSQL im CSN Referenzen Daniel Schreiber, 28. April 2004 Szenario: Banküberweisung von Konto A → Konto B u Subtrahiere Betrag c von Konto A u Addiere Betrag c zu Konto B BEGIN; UPDATE konto set betrag=betrag-c WHERE kontoid=’A’; UPDATE konto set betrag=betrag+c WHERE kontoid=’B’; COMMIT; n Alle Befehle zwischen BEGIN und COMMIT werden komplett oder gar nicht ausgeführt n Transaktion kann durch Fehler oder durch ROLLBACK abgebrochen werden. n PostgreSQL im CSN - p. 16 Transaktionsisolierung ● Überblick CSN SQL PostgreSQL ● Was ist PostgreSQL ● Transaktionen ● Transaktionsisolierung ● Views ● Rules ● Stored Procedures ● Trigger Eine Transaktion liest Daten einer anderen Transaktion, die nicht beendet wurde dirty read Eine Transaktion liest Daten, die sie schon einmal gelesen hat und stellt fest, dass die Daten sich geändert haben nonrepeatable read Die Ergebnismenge derselben Anfrage ändert sich während einer Transaktion phantom read PostgreSQL im CSN Referenzen Isolierung dirty read nonrep. read phantom read read uncommited möglich möglich möglich read commited nicht möglich möglich möglich repeatable read nicht möglich nicht möglich möglich serializable nicht möglich nicht möglich nicht möglich PostgreSQL unterstützt Isolationslevel read commited und serializable Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 17 Views ● Überblick CSN SQL PostgreSQL ● Was ist PostgreSQL ● Transaktionen ● Transaktionsisolierung ● Views ● Rules ● Stored Procedures ● Trigger PostgreSQL im CSN Referenzen Views sind Vereinfachung für den Benutzer n die Datenbank kann Zugriffe auf Views beschleunigen n über Views können Zugriffsrechte beschränkt werden CREATE VIEW millionaere AS SELECT Vorname, Nachname, sum(Kontostand) as Kontostand FROM konto JOIN person ON person.ID=konto.eigentuemer WHERE kontostand > 1000000; SELECT * FROM millionaere; Vorname Nachname Kontostand n Vinzenz Daniel Schreiber, 28. April 2004 Hillebrandt 9812347 PostgreSQL im CSN - p. 18 Rules ● Überblick n CSN SQL PostgreSQL ● Was ist PostgreSQL ● Transaktionen ● Transaktionsisolierung ● Views ● Rules ● Stored Procedures ● Trigger PostgreSQL im CSN Referenzen n Rules schreiben den Abfragebaum um u Anfrage wird an Datenbank geschickt u Rules werden angewendet → neue Anfrage entsteht u neue Anfrage wird in Abfragebaum übersetzt Views werden in PostgreSQL intern über Rules realisiert CREATE VIEW millionaere AS SELECT Vorname, Nachname, sum(Kontostand) as Kontostand· FROM konto JOIN person ON person.ID=konto.eigentuemer· WHERE kontostand > 1000000; CREATE table millionaere AS (Vorname VARCHAR, Nachname VARCHAR, Kontostand INTEGER); CREATE RULE "_RETURN" AS ON SELECT TO millionaere DO INSTEAD SELECT Vorname, Nachname, sum(Kontostand) as Kontostand· FROM konto JOIN person ON person.ID=konto.eigentuemer· WHERE kontostand > 1000000; Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 19 Stored Procedures ● Überblick CSN SQL PostgreSQL ● Was ist PostgreSQL ● Transaktionen ● Transaktionsisolierung ● Views ● Rules ● Stored Procedures ● Trigger PostgreSQL im CSN Referenzen Datenbankfunktionen können hinzugefügt werden n Implementierung in SQL, pgSQL, C, Scriptsprachen CREATE FUNCTION cowsay (varchar) RETURNS varchar AS ’ DECLARE _saying alias for $1; BEGIN return _saying || ’’Muh.’’; END; ’ AS LANGUAGE ’plpgsql’; n SELECT cowsay(’Hello World’); n Eventuell problematisch: Last, Sicherheit, Stabilität (bei Implementierung in C) Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 20 Trigger ● Überblick n CSN SQL PostgreSQL ● Was ist PostgreSQL ● Transaktionen ● Transaktionsisolierung ● Views n n n ● Rules ● Stored Procedures ● Trigger PostgreSQL im CSN n Code, der bei einem bestimmten Ereignis ausgeführt wird: INSERT UPDATE DELETE Ausführung pro Statement oder für jedes Tupel Schreibt nicht den Abfragebaum um üblicherweise werden selbst programmierte Funktionen aufgerufen Funktion muß Datentyp trigger zurückliefern (opaque vor PostgreSQL 7.3) Referenzen Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 21 ● Überblick CSN SQL PostgreSQL PostgreSQL im CSN ● Zahlen und Fakten ● Modellierung PostgreSQL im CSN ● Erfahrungen im Einsatz ● Kritik ● TF freizugebende_ip_tf ● TF check_mac_tf ● Aggregatfunktion ● Fremdschlüssel Referenzen Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 22 Zahlen und Fakten ● Überblick n CSN n SQL n PostgreSQL PostgreSQL im CSN n 110 Relationen 64 Views 119 Stored Procedures (+ Systemfunktionen) Tendenz: steigend ● Zahlen und Fakten ● Modellierung ● Erfahrungen im Einsatz ● Kritik ● TF freizugebende_ip_tf ● TF check_mac_tf ● Aggregatfunktion ● Fremdschlüssel Referenzen Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 23 Modellierung ● Überblick n CSN n SQL n PostgreSQL PostgreSQL im CSN n ● Zahlen und Fakten ● Modellierung n ● Erfahrungen im Einsatz ● Kritik n ● TF freizugebende_ip_tf ● TF check_mac_tf ● Aggregatfunktion n Nutzer Rechner Statusfelder (m : n) Standorte (Nutzer, Rechner), Dosen, Ports Rechtemanagement (Aufgabenbereiche) Inventarverwaltung Finanzverwaltung ● Fremdschlüssel Referenzen Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 24 Erfahrungen im Einsatz ● Überblick n CSN n SQL PostgreSQL PostgreSQL im CSN ● Zahlen und Fakten ● Modellierung n ● Erfahrungen im Einsatz ● Kritik ● TF freizugebende_ip_tf ● TF check_mac_tf ● Aggregatfunktion ● Fremdschlüssel Referenzen n n n n Daniel Schreiber, 28. April 2004 Einsatz seit 1999 Seit dem wesentliche Erweiterungen der Funktionalität in PostgreSQL: Referentielle Integrität, bessere Unterstützung von Programmiersprachen spürbare Geschwindigkeitsverbesserungen seit PostgreSQL 7.0 kein Datenverlust wenig Bugs (außer Serie 7.2.x) sehr schnelle und kompetente Hilfe im IRC (irc.freenode.org, #postgresql) sehr gute ausführliche Dokumentation PostgreSQL im CSN - p. 25 Kritik ● Überblick n CSN SQL PostgreSQL PostgreSQL im CSN ● Zahlen und Fakten ● Modellierung ● Erfahrungen im Einsatz ● Kritik n n Fehlende Features u Verteilung der Datenbank u Replikationssoftware nicht im Standardlieferumfang enthalten (kommerziell, ähnlich GhostScript) regelmäßiges Vacuum notwendig (im CSN: Cronjob) regelmäßiges reindizieren notwendig (bis 7.3) ● TF freizugebende_ip_tf ● TF check_mac_tf ● Aggregatfunktion ● Fremdschlüssel Referenzen Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 26 Triggerfunktion: freizugebende_ip_tf ● Überblick CSN SQL PostgreSQL PostgreSQL im CSN ● Zahlen und Fakten ● Modellierung ● Erfahrungen im Einsatz ● Kritik ● TF freizugebende_ip_tf ● TF check_mac_tf ● Aggregatfunktion ● Fremdschlüssel Referenzen Daniel Schreiber, 28. April 2004 CREATE OR REPLACE FUNCTION freizugebende_ip_tf () RETURNS "trigger" AS BEGIN IF TG_OP = ’’UPDATE’’ THEN IF (NEW.ip_adr IS NULL) AND (OLD.ip_adr IS NOT NULL) THEN INSERT INTO freizugebende_ip(host_id,hostname,ip_adr) VALUES (OLD.host_id,OLD.hostname,OLD.ip_adr); END IF; END IF; IF TG_OP = ’’DELETE’’ THEN IF OLD.ip_adr IS NOT NULL THEN INSERT INTO freizugebende_ip(host_id,hostname,ip_adr) VALUES (OLD.host_id,OLD.hostname,OLD.ip_adr); END IF; END IF; RETURN NULL; END; ’ LANGUAGE ’plpgsql’; PostgreSQL im CSN - p. 27 Triggerfunktion: check_mac_tf ● Überblick CSN SQL PostgreSQL PostgreSQL im CSN ● Zahlen und Fakten ● Modellierung ● Erfahrungen im Einsatz ● Kritik ● TF freizugebende_ip_tf ● TF check_mac_tf ● Aggregatfunktion ● Fremdschlüssel Referenzen Daniel Schreiber, 28. April 2004 CREATE OR REPLACE FUNCTION check_mac_tf () RETURNS "trigger" AS ’ BEGIN IF TG_OP=’’UPDATE’’ THEN IF NEW.ether_adr=OLD.ether_adr THEN RETURN NEW; END IF; END IF; IF ismacused(NEW.ether_adr) THEN RAISE EXCEPTION ’’MAC % ist bereits in Benutzung’’, NEW.ether_adr; END IF; RETURN NEW; END; ’ LANGUAGE plpgsql; PostgreSQL im CSN - p. 28 Aggregatfunktion ● Überblick CSN SQL PostgreSQL PostgreSQL im CSN ● Zahlen und Fakten ● Modellierung ● Erfahrungen im Einsatz ● Kritik ● TF freizugebende_ip_tf ● TF check_mac_tf ● Aggregatfunktion ● Fremdschlüssel Referenzen create or replace function str_concat_by_newline(text, text) returns text as ’ BEGIN IF $1 = ’’’’ then return $2; ELSE return $1 || ’’\n’’ || $2; END IF; END; ’ LANGUAGE ’plpgsql’; create AGGREGATE agg_text (sfunc=str_concat_by_newline, basetype=text,stype=text, initcond=’’); Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 29 Fremdschlüssel einfügen ● Überblick CSN SQL PostgreSQL PostgreSQL im CSN ● Zahlen und Fakten ● Modellierung ● Erfahrungen im Einsatz ● Kritik ● TF freizugebende_ip_tf ● TF check_mac_tf ● Aggregatfunktion ● Fremdschlüssel Referenzen Daniel Schreiber, 28. April 2004 CREATE OR REPLACE FUNCTION erteile_sperre_mit_grund (character varying, character varying, character varying, timestamp with time zone, timestamp with time zone) RETURNS boolean AS ’ DECLARE _urz_login ALIAS FOR $1; _sperrender ALIAS FOR $2; _grund ALIAS FOR $3; _gueltig_von ALIAS FOR $4; _gueltig_bis ALIAS FOR $5; _person_id integer; _oid oid; BEGIN SELECT person_id INTO _person_id FROM csn_nutzer WHERE urz_login=_urz_login; INSERT INTO csn_nutzer_hat_status (person_id, status_id, gueltig_von, gueltig_bis) VALUES (_person_id, 3, _gueltig_von, _gueltig_bis); GET DIAGNOSTICS _oid = RESULT_OID; INSERT INTO nutzersperre (id, sperrgrund) VALUES ( (SELECT id FROM csn_nutzer_hat_status WHERE oid=_oid), _grund); RETURN ’’t’’::BOOLEAN; END; ’ LANGUAGE plpgsql; PostgreSQL im CSN - p. 30 ● Überblick CSN SQL PostgreSQL PostgreSQL im CSN Referenzen Referenzen ● Quellen ● Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 31 Quellen ● Überblick n CSN n SQL n PostgreSQL PostgreSQL im CSN Referenzen n PostgreSQL Webseite: http://www.postgresql.org PostgreSQL Dokumentation: http://www.postgresql.org/docs/ PostgreSQL, Jens Hartwig, Addison Wesley 2001, ISBN 3-8273-1860-2 CSN Datenbank, Lutz Neugebauer ● Quellen ● Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 32 ● Überblick Vielen Dank für Ihre Aufmerksamkeit! CSN SQL PostgreSQL PostgreSQL im CSN Referenzen ● Quellen ● Fragen? Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 33
