Transcription

View metadata, citation and similar papers at core.ac.ukEin Elefant vergisst nichtErweiterte Funktionen in PostgreSQL amBeispiel der CSN DatenbankDaniel SchreiberChemnitzer StudentenNetzhttps://www.csn.tu-chemnitz.debrought to you byCOREPostgreSQL im CSN - p. 1provided by Multimedia ONline ARchiv CHemnitzDaniel Schreiber, 28. April 2004

Überblick ÜberblickCSNInhalt:nSQLPostgreSQLnPostgreSQL im CSNnReferenzennKurzer Blick ins CSNRelationales Datenmodell und SQLPostgreSQL als (Objekt-)relationales DatenbanksystemPostgreSQL im CSNNicht behandelt wird:nnnDaniel Schreiber, 28. April 2004Installation eines ServersZugriff aus Programmiersystemen (Perl, PHP, .)Objektrelationale Fähigkeiten von PostgreSQLPostgreSQL im CSN - p. 2

ÜberblickCSN Szenario Umgebung der Datenbank AnforderungenSQLPostgreSQLEinblick ins CSNPostgreSQL im CSNReferenzenDaniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 3

Szenario ÜberblicknCSNn Szenario Umgebung der Datenbank AnforderungennSQLnca. 1500 Hosts im CSNca. 80 aktive NetzgeräteTrafficaccountingVerwaltungsdaten für RoutinearbeitenPostgreSQLPostgreSQL im CSNReferenzenDaniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 4

Umgebung der Datenbank ÜberblicknCSN Szenario Umgebung der Datenbank AnforderungennSQLPostgreSQLnPostgreSQL im CSNReferenzenDaniel Schreiber, 28. April 2004nAlle Informationen werden zentral in PostgreSQL DatenbankgespeichertNetzwerkhardware wird automatisch gemanaged (Portsecurity, static ARP)Cronjobs erledigen Routineaufgaben (IPs beantragen, Mailverschicken, Netzconfiguration, Firewall, DCHP etc.)Dateneingabe über WebinterfacePostgreSQL im CSN - p. 5

Anforderungen ÜberblicknCSN Szenario Umgebung der Datenbank AnforderungenSQLPostgreSQLnnPostgreSQL im CSNReferenzenDaniel Schreiber, 28. April 2004nOhne Datenbank ist das CSN nicht arbeitsfähigu ACID (Atomicity, Consistency, Isolation, Durability)u die Datenbank hat immer RechtÄnderungen werden in Logtabellen protokolliertKomplizierte Aufgaben werden von stored Procedureserledigt APIStrukturdump “wiegt” ca. 320 kBytesPostgreSQL im CSN - p. 6

ÜberblickCSNSQL Relationen Beispiel Beispiel Verknüpfungen (JOINS) Gruppieren Daten bearbeitenWiederholung: RelationalesDatenmodell und SQLPostgreSQLPostgreSQL im CSNReferenzenDaniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 7

Relationen ÜberblicknCSNnSQL Relationen Beispiel Beispiel Verknüpfungen (JOINS) Gruppieren Daten bearbeitennnRelationale Datenbanken speichern Werte in TabellenTabellen haben festgelegte Anzahl Spaltenbeliebig viele Zeilen (Tupel)über Anfragesprache können Einträge in Spaltenausgewählt, gruppiert, sortiert und verknüpft werdenPostgreSQLPostgreSQL im CSNReferenzenDaniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 8

Beispiel ÜberblickCSNSQL Relationen Beispiel Beispiel Verknüpfungen (JOINS) Gruppieren Daten bearbeitenPostgreSQLPostgreSQL im CSNReferenzennnDaniel Schreiber, 28. April 13293389812347253124723ID und Kontonummer sind PrimärschlüsselEigentümer ist FremdschlüsselPostgreSQL im CSN - p. 9

Beispiel ÜberblickCSNSQL Relationen Beispiel Beispiel Verknüpfungen (JOINS) Gruppieren Daten bearbeitenPostgreSQLPostgreSQL im CSNReferenzenDaniel Schreiber, 28. April PostgreSQL im CSN - p. 10

Verknüpfungen (JOINS) ÜberblickCSNSQL Relationen Beispiel Beispiel Verknüpfungen (JOINS) Gruppieren Daten bearbeitenPostgreSQLPostgreSQL im CSNReferenzenerzeugen aus mehreren Tabellen neue (virtuelle) Tabellen Verknüfungskriterium (fast immer Gleichheit von Primär- undFremdschüssel)SELECT Vorname, Nachname, Kontostand, KontonummerFROM person JOIN konto ON person.ID 4123612351238Wieviel Geld besitzt Ismael Blanckenhagen?Daniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 11

Gruppieren ÜberblickCSNSQL Relationen Beispiel Beispiel Verknüpfungen (JOINS) Gruppieren Daten bearbeitenPostgreSQLPostgreSQL im CSNReferenzendurch Gruppierung können mehrere Zeilen (Tupel)miteinander verrechnet werdenn Verrechnung geschieht durch Aggregatfunktionn in PostgreSQL kann man eigene AggregatfunktionendefinierenSELECT Vorname, Nachname, sum(Kontostand)FROM person JOIN konto ON person.ID konto.EigentuemerGROUP BY Vorname, Nachname;Vorname NachnameKontostandnIsmaelVinzenzEditheDaniel Schreiber, 28. April 24723PostgreSQL im CSN - p. 12

Daten bearbeiten ÜberblicknCSNSQL Relationen Beispiel Beispiel Verknüpfungen (JOINS)nnINSERT INTO tabelle (spalte1, spalte2)VALUES (wert1, wert2);UPDATE tabelle set spalte1 wert1 WHERE bedingung;DELETE FROM tabelle WHERE bedingung; Gruppieren Daten bearbeitenPostgreSQLPostgreSQL im CSNReferenzenDaniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 13

ÜberblickCSNSQLPostgreSQL Was ist PostgreSQL Transaktionen Transaktionsisolierung ViewsDer Elefant betritt die Bühne Rules Stored Procedures TriggerPostgreSQL im CSNReferenzenDaniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 14

Was ist PostgreSQL ÜberblicknCSNnSQLPostgreSQL Was ist PostgreSQL Transaktionen Transaktionsisolierung Views Rules Stored Procedures TriggerPostgreSQL im CSNOID VerwaltungssystemObjektrelationale Datenbanku Referentielle Integritätu Transaktionenu Viewsu Rulesu Stored Proceduresu TriggerReferenzenDaniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 15

Transaktionen ÜberblickCSNSQLPostgreSQL Was ist PostgreSQL Transaktionen Transaktionsisolierung Views Rules Stored Procedures TriggerPostgreSQL im CSNReferenzenDaniel Schreiber, 28. April 2004Szenario: Banküberweisung von Konto A Konto Bu Subtrahiere Betrag c von Konto Au Addiere Betrag c zu Konto BBEGIN;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 komplettoder gar nicht ausgeführtn Transaktion kann durch Fehler oder durch ROLLBACKabgebrochen werden.nPostgreSQL im CSN - p. 16

Transaktionsisolierung ÜberblickCSNSQLPostgreSQL Was ist PostgreSQL Transaktionen Transaktionsisolierung Views Rules Stored Procedures TriggerEine Transaktion liest Daten einer anderenTransaktion, die nicht beendet wurdedirty readEine Transaktion liest Daten, die sie schoneinmal gelesen hat und stellt fest, dass die Daten sichgeändert habennonrepeatable readDie Ergebnismenge derselben Anfrage ändertsich während einer Transaktionphantom readPostgreSQL im CSNReferenzenIsolierungdirty readnonrep. readphantom readread uncommited möglichmöglichmöglichread commited nicht möglich möglichmöglichrepeatable read nicht möglich nicht möglich möglichserializable nicht möglich nicht möglich nicht möglichPostgreSQL unterstützt Isolationslevel read commited undserializableDaniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 17

Views ÜberblickCSNSQLPostgreSQL Was ist PostgreSQL Transaktionen Transaktionsisolierung Views Rules Stored Procedures TriggerPostgreSQL im CSNReferenzenViews sind Vereinfachung für den Benutzern die Datenbank kann Zugriffe auf Views beschleunigenn über Views können Zugriffsrechte beschränkt werdenCREATE VIEW millionaere ASSELECT Vorname, Nachname, sum(Kontostand) as KontostandFROM konto JOIN person ON person.ID konto.eigentuemerWHERE kontostand 1000000;SELECT * FROM millionaere;Vorname Nachname KontostandnVinzenzDaniel Schreiber, 28. April 2004Hillebrandt9812347PostgreSQL im CSN - p. 18

Rules ÜberblicknCSNSQLPostgreSQL Was ist PostgreSQL Transaktionen Transaktionsisolierung Views Rules Stored Procedures TriggerPostgreSQL im CSNReferenzennRules schreiben den Abfragebaum umu Anfrage wird an Datenbank geschicktu Rules werden angewendet neue Anfrage entstehtu neue Anfrage wird in Abfragebaum übersetztViews werden in PostgreSQL intern über Rules realisiertCREATE VIEW millionaere ASSELECT 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 INSTEADSELECT Vorname, Nachname, sum(Kontostand) as Kontostand·FROM konto JOIN person ON person.ID konto.eigentuemer·WHERE kontostand 1000000;Daniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 19

Stored Procedures ÜberblickCSNSQLPostgreSQL Was ist PostgreSQL Transaktionen Transaktionsisolierung Views Rules Stored Procedures TriggerPostgreSQL im CSNReferenzenDatenbankfunktionen können hinzugefügt werdenn Implementierung in SQL, pgSQL, C, ScriptsprachenCREATE FUNCTION cowsay (varchar) RETURNS varchar AS ’DECLAREsaying alias for 1;BEGINreturn saying ’’Muh.’’;END;’ AS LANGUAGE ’plpgsql’;nSELECT cowsay(’Hello World’);n Eventuell problematisch: Last, Sicherheit, Stabilität (beiImplementierung in C)Daniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 20

Trigger ÜberblicknCSNSQLPostgreSQL Was ist PostgreSQL Transaktionen Transaktionsisolierung Viewsnnn Rules Stored Procedures TriggerPostgreSQL im CSNnCode, der bei einem bestimmten Ereignis ausgeführt wird:INSERT UPDATE DELETEAusführung pro Statement oder für jedes TupelSchreibt nicht den Abfragebaum umüblicherweise werden selbst programmierte FunktionenaufgerufenFunktion muß Datentyp trigger zurückliefern (opaque vorPostgreSQL 7.3)ReferenzenDaniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 21

ÜberblickCSNSQLPostgreSQLPostgreSQL im CSN Zahlen und Fakten ModellierungPostgreSQL im CSN Erfahrungen im Einsatz Kritik TF freizugebende ip tf TF check mac tf Aggregatfunktion FremdschlüsselReferenzenDaniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 22

Zahlen und Fakten ÜberblicknCSNnSQLnPostgreSQLPostgreSQL im CSNn110 Relationen64 Views119 Stored Procedures ( Systemfunktionen)Tendenz: steigend Zahlen und Fakten Modellierung Erfahrungen im Einsatz Kritik TF freizugebende ip tf TF check mac tf Aggregatfunktion FremdschlüsselReferenzenDaniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 23

Modellierung ÜberblicknCSNnSQLnPostgreSQLPostgreSQL im CSNn Zahlen und Fakten Modellierungn Erfahrungen im Einsatz Kritikn TF freizugebende ip tf TF check mac tf AggregatfunktionnNutzerRechnerStatusfelder (m : n)Standorte (Nutzer, Rechner), Dosen, PortsRechtemanagement ng FremdschlüsselReferenzenDaniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 24

Erfahrungen im Einsatz ÜberblicknCSNnSQLPostgreSQLPostgreSQL im CSN Zahlen und Fakten Modellierungn Erfahrungen im Einsatz Kritik TF freizugebende ip tf TF check mac tf Aggregatfunktion FremdschlüsselReferenzennnnnDaniel Schreiber, 28. April 2004Einsatz seit 1999Seit dem wesentliche Erweiterungen der Funktionalität inPostgreSQL: Referentielle Integrität, bessere Unterstützungvon Programmiersprachenspürbare Geschwindigkeitsverbesserungen seit PostgreSQL7.0kein Datenverlustwenig Bugs (außer Serie 7.2.x)sehr schnelle und kompetente Hilfe im IRC (irc.freenode.org,#postgresql)sehr gute ausführliche DokumentationPostgreSQL im CSN - p. 25

Kritik ÜberblicknCSNSQLPostgreSQLPostgreSQL im CSN Zahlen und Fakten Modellierung Erfahrungen im Einsatz KritiknnFehlende Featuresu Verteilung der Datenbanku Replikationssoftware nicht im Standardlieferumfangenthalten (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üsselReferenzenDaniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 26

Triggerfunktion: freizugebende ip tf ÜberblickCSNSQLPostgreSQLPostgreSQL im CSN Zahlen und Fakten Modellierung Erfahrungen im Einsatz Kritik TF freizugebende ip tf TF check mac tf Aggregatfunktion FremdschlüsselReferenzenDaniel Schreiber, 28. April 2004CREATE OR REPLACE FUNCTION freizugebende ip tf () RETURNS "trigger" ASBEGINIF TG OP ’’UPDATE’’ THENIF (NEW.ip adr IS NULL) AND (OLD.ip adr IS NOT NULL) THENINSERT 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’’ THENIF OLD.ip adr IS NOT NULL THENINSERT 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 ÜberblickCSNSQLPostgreSQLPostgreSQL im CSN Zahlen und Fakten Modellierung Erfahrungen im Einsatz Kritik TF freizugebende ip tf TF check mac tf Aggregatfunktion FremdschlüsselReferenzenDaniel Schreiber, 28. April 2004CREATE OR REPLACE FUNCTION check mac tf () RETURNS "trigger" AS ’BEGINIF TG OP ’’UPDATE’’ THENIF NEW.ether adr OLD.ether adr THENRETURN NEW;END IF;END IF;IF ismacused(NEW.ether adr) THENRAISE EXCEPTION ’’MAC % ist bereits in Benutzung’’,NEW.ether adr;END IF;RETURN NEW;END;’ LANGUAGE plpgsql;PostgreSQL im CSN - p. 28

Aggregatfunktion ÜberblickCSNSQLPostgreSQLPostgreSQL im CSN Zahlen und Fakten Modellierung Erfahrungen im Einsatz Kritik TF freizugebende ip tf TF check mac tf Aggregatfunktion FremdschlüsselReferenzencreate or replace function str concat by newline(text,text) returns text as ’BEGINIF 1 ’’’’ thenreturn 2;ELSEreturn 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 2004PostgreSQL im CSN - p. 29

Fremdschlüssel einfügen ÜberblickCSNSQLPostgreSQLPostgreSQL im CSN Zahlen und Fakten Modellierung Erfahrungen im Einsatz Kritik TF freizugebende ip tf TF check mac tf Aggregatfunktion FremdschlüsselReferenzenDaniel Schreiber, 28. April 2004CREATE OR REPLACE FUNCTION erteile sperre mit grund (character varying,character varying, character varying, timestamp with time zone, timestampwith time zone) RETURNS boolean AS ’DECLAREurz 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;BEGINSELECT 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

ÜberblickCSNSQLPostgreSQLPostgreSQL im CSNReferenzenReferenzen Quellen Daniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 31

Quellen ÜberblicknCSNnSQLnPostgreSQLPostgreSQL im CSNReferenzennPostgreSQL Webseite: http://www.postgresql.orgPostgreSQL Dokumentation: http://www.postgresql.org/docs/PostgreSQL, Jens Hartwig, Addison Wesley 2001, ISBN3-8273-1860-2CSN Datenbank, Lutz Neugebauer Quellen Daniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 32

ÜberblickVielen Dank für Ihre Aufmerksamkeit!CSNSQLPostgreSQLPostgreSQL im CSNReferenzen Quellen Fragen?Daniel Schreiber, 28. April 2004PostgreSQL im CSN - p. 33

lUmgebung der Datenbank lAnforderungen SQL PostgreSQL PostgreSQL im CSN Referenzen Daniel Schreiber, 28. April 2004 PostgreSQL im CSN - p. 6 Anforderungen n Ohne Datenbank ist das CSN nicht arbeitsfähig u ACID (Atomicity, Consistency, Isolation, Durability) u die Datenbank hat imm