Ich zitiere hier lediglich zwei interessante Artikel zu dem oben genannten Thema:
VON: http://www.fractalcenter.de/?p=11
Bevor ich bei meiner jetzigen Firma angestellt war, habe ich mir eingebildet etwas über SQL (MySQL) zu wissen. Dann habe ich jedoch den Microsoft-SQL-Server kennen gelernt. Ich bin generell kein Microsoft Fan, der SQL-Server ist aber schon ein sehr leistungsfähiger Server. Durch die bereits vorhandenen Strukturen musste ich mich erst einmal durchfitzen unter anderem auch durch Sichten und Prozeduren. Aus heutiger Sicht weiß ich nicht, wie ich ohne diese SQL Werkzeuge auskommen konnte.
Kommen wir als erstes zu den Sichten (Views):
Um den Sinn einer Sicht zu verstehen, versuche ich es einmal mit einem Beispiel. (dies ist ein MySQL-Code in anderen SQL-Sprachen weicht dieser aber nicht allzu sehr ab)
CREATE VIEW terms AS SELECT term.term_id, name, slug, term_group, taxonomy, description, parent, count FROM wp_terms AS term INNER JOIN wp_term_taxonomy AS tax ON term.term_id = tax.term_id
Mit CREATE VIEW * AS wird eine Sicht eingeleitet. Ich habe hier mal als Beispiel die Wordpress Datenbank genommen um einen JOIN über die Tabellen wp_term_taxonomy und wp_terms zu erstellen. Die Frage die ich mir am Anfang gestellt habe war: “Wozu soll ich das so umständlich machen?”. Man kann die SQL ja auch direkt in den PHP-Quellcode schreiben. Das mag zwar fürs erste stimmen, wenn man das Ganze aber etwas weiter spinnt, wird es vielleicht deutlicher. In Wordpress ist es prinzipiell möglich den Tabellenpräfix zu ändern. Wenn ich das im Nachhinein ändere, wird das in der Wordpress-Programmierung automatisch erledigt, nehmen wir jetzt aber weiter an, dass ich mir ein Plugin oder eine eigene Seite mit SQL-Abfragen geschrieben habe. Ich müsste jetzt manuell durch alle Abfragen durchgehen und die Tabellen umbenennen. Wenn ich aber Sichten einsetzte muss ich dies nur einmalig zentral ändern und alle Scripte die auf die Sicht zugreifen funktionieren weiterhin. Sichten helfen Redundanz zu vermeiden. Ich muss den SQL-Code nicht mehrmals hinterlegen, sondern kann ihn an einer Stelle ändern. Für Webmaster, die sich bis jetzt nur mit “kleinen” Seiten auseinander gesetzt haben, ist der Sinn vielleicht nicht offensichtlich, wenn man jedoch eine Webanwendung mit dutzenden von Tabellen hat, lernt man Views sehr schnell zu schätzen.
Als zweites möchte ich Prozeduren vorstellen.
Eine Prozedur ist ein universelle Werkzeug in der täglichen Arbeit mit SQL. Eine Prozedur ermöglicht es viele SQL-Anweisungen in einer Anweisung zusammen zu fassen. Nehmen wir an, dass ich den Umsatz pro Kunde und Monat ermitteln will. Das geht sicherlich recht einfach mit einigen JOINS und einer GROUP BY Klausel. Jedoch müsste ich diese Werte bei jeder Abfrage erneut generieren. Bei 10.000 Kunden mag das vielleicht noch gehen. Bei 1.000.00 Kunden wird es schon recht aufwendig für den SQL-Server. Die Last ist auf jeden Fall enorm hoch. Am einfachsten wäre es doch, diese Zahlen einmal in der Woche (oder täglich) in eine gesonderte Tabelle weg zu schreiben. Genau dass kann man mit Hilfe einer Prozedur und eines Cronjobs machen.
DELIMITER $$ DROP PROCEDURE IF EXISTS `proc_umsatz_monatlich`$$ CREATE PROCEDURE `proc_umsatz_monatlich` () BEGIN INSERT INTO umsatz_monatlich SELECT kunde_id, umsatz FROM rechnungen GROUP BY YEAR(datum), MONTH(datum), kunde_id END$$ DELIMITER ;
Der Vorteil von Prozeduren liegt auf der Hand. Man kann sie mit Variablen füttern und somit das Ergebnis beeinflussen (In einer Prozedur kann man z.B. auch IF Abfragen verwenden). Die Prozedur selbst kann beliebig verwandelt und an neue Gegebenheiten angepasst werden, ohne dass sämtlicher SQL-Code umgeschrieben werden muss. Das hat vor allem Vorteile für z.B. die Suche einer Seite. Diese wir normalerweise mit wenigen Parametern angesprochen (Suchbegriff und ggf. Einschränkungen wie Zeitraum o.ä.) und gibt als Ergebnis eine Tabelle zurück. Ich musste in unsere Suche eine Tabelle zusätzlich einbinden. Das ging ohne eine Zeile im PHP-Quellcode zu verändern, nur indem ich die Prozedur verändert habe. Es gibt Programmierer, vor allem in der professionellen Programmierung, die der Meinung sind, dass ein Client (das Clientprogramm) keinen direkten Zugriff auf die Datenbank haben darf. Das ist ein recht radikaler Ansatz, dem ich mich nicht anschließen möchte. Sinnvoll wäre es aber z.B. dass man dem Client, im Falle der Webprogrammierung dürfte das zumeist “www-data” sein, nur SELECT oder UPDATE Befehle erlaubt. DROP und DELETE sind nur Prozeduren vorbehalten. Das würde dem Schutz vor SQL-Injection einigen Vorschub leisten. Wenn man sogar soweit geht, die Einschränkung auf UPDATE Befehle auszuweiten, wäre das ein großer Sicherheitsgewinn.
Eine weitere wichtige Funktion von Prozeduren soll das nachfolgende Beispiel verdeutlichen:
CREATE PROCEDURE curdemo() BEGIN DECLARE done INT DEFAULT 0; DECLARE a CHAR(16); DECLARE b,c INT; DECLARE cur CURSOR FOR SELECT id,DATA FROM test.t1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO a, b; IF NOT done THEN IF b < c THEN INSERT INTO test.t3 VALUES (a,b); ELSE INSERT INTO test.t3 VALUES (a,c); END IF; END IF; UNTIL done END REPEAT; CLOSE cur; END
Man kann in einer Prozedur durch ein Ergebnis einer SQL, Zeile für Zeile durchgehen und die Datenwerte für Bedingungen, INSERT, DELETE oder UPDATE Befehle nutzen. Man kann einen CURSOR ähnlich einer while Schleife benutzen. Das zu den Vorteilen. Der Entscheidende Nachteil ist, die Geschwindigkeit. Wenn ich ein UPDATE Befehle ausführe, selbst wenn es zehntausende Zeilen betrifft, ist der Server in meist weniger als einer Sekunde fertig (Abhängig von der Tabelle, dem Index und weiterer Faktoren). Mit einer CURSOR Operation kann ich prinzipiell die gleiche Funktion ausführen, wie mit dem UPDATE Befehl, diese würde jedoch erheblich länger Dauern. Deshalb ein Wort der Warnung. CURSOR Operationen nur mit Bedacht einsetzen und alle Alternativen vorher ausschöpfen. Als bewährtes Mittel um diesen SQL Befehl zu vermeiden, haben sich für mich temporäre Tabellen erwiesen. Man kann das Ergebnis einer SELECT Anweisung in eine Tabelle speichern und diese dann für weiter gehende Aktionen benutzen.
Dazu fällt mir wieder meine Suche ein. Als erstes erstelle ich eine Temporäre Tabelle mit bestimmten IDs die ich aus den Einschränkungen Zeitraum bzw. Thema gewinne. Diese IDs benutze ich dann als Grundlage für die weitere Recherche nach dem Suchwort. Anfänglich hat die Ausführung ca. 30 Sekunden gedauert.
Das ist auch noch ein wichtiger Punkt, den ich ansprechen möchte, obwohl es die meisten Programmierer wissen sollten. Man sollte erst seinen Code schreiben, so dass er einfach zu warten ist und den Anforderungen entspricht. Erst dann sollte man sich auf den Weg von Flaschenhälsen machen, sprich optimieren. Ein gutes Beispiel ist eine Webseite, die mehrere Zehntausend Zugriffe pro Stunde hat. Wenn man diese versucht von Anfang an zu optimieren ohne eine entsprechende Serverlast zum Testen zu haben, ist das bestenfalls verschwendete Zeit, schlimmsten Falls hat man sich auf eine falsche Annahme verlassen und die Optimierung kostet mehr Ausführungszeit als der eigentlich Code.
Zurück zur SQL. Durch schrittweises optimieren, z.B. einen zusätzlichen Index auf die durchsuchten Tabellen und dem Einsatz von temporären Tabellen konnte ich die Ausführung auf, je nach Aufruf, 0.7 - 1.5 Sekunden herunterschrauben. Die Suche geht dabei über 14 Tabellen, von denen 7 durchsucht und 7 für die Ausgabe (z.B. Bilder, Titel, Sortierung etc.) ausgewertet werden.
VON: http://www.strassenprogrammierer.de/mysql-stored-procedures-trigger_tipp_469.html
Mit der Version 5 ist MySQL den großen Datenbanken wie MS-SQL oder Oracle wieder ein Stück näher gerückt. Die wichtigsten neuen Fähigkeiten sollten Sie kennen, denn sie machen Ihre Programme sicherer und effizienter.
Mit den Stored Procedures von MySQL können Sie Programmcode innerhalb des Datenbankservers ausführen lassen. Das macht MySQL um einiges mächtiger. Ein typisches Beispiel: Bislang erforderte der Umgang mit hierarchischen Datenstrukturen, etwa dem Seitenbaum einer Website, einigen Aufwand in der verwendeten Skriptsprache.
So ist etwa die Abfrage “gib mir den Pfad von einer bestimmten Inhaltsseite hoch bis zur Startseite” in SQL nicht auszudrücken. Denn zur Beantwortung müsste eine variable Anzahl von Joins eingesetzt werden - je nachdem, wie viele Ebenen über der gewünschten Seite existieren.
Mit der Hilfe der Stored Procedures kann man ein kleines Programm schreiben, das das Problem elegant löst. Es geht von der angegebenen Seite aus immer einen Schritt nach oben, bis der Wurzelknoten erreicht ist. Die Id-Nummern der dabei durchlaufenen Seiten werden als String zurückgeliefert. Das aufrufende Programm kann diese Information dann für weitere Auswertungen nutzen, etwa für die Anzeige des Seitenpfades (”Breadcrumbs”) in der Navigation.
Angenommen, der Seitenbaum liegt in der Tabelle pages vor. Alle Seiten besitzen im Feld uid eine eindeutige Id und im Feld pid ist die Id ihrer übergeordneten Seite vermerkt. Die Wurzelseite hat die pid 0. Dann erledigt folgende Funktion die gestellte Aufgabe:
DELIMITER '$'; CREATE FUNCTION f_rootpath (param_id INT) RETURNS CHAR(255) BEGIN DECLARE retval CHAR(255); DECLARE father_id INT; SET retval=param_id; SET father_id=param_id; WHILE father_id<>0 DO SELECT pid INTO father_id FROM pages WHERE uid=father_id; SET retval = CONCAT(retval,'-',father_id); END WHILE; RETURN retval; END$ DELIMITER ;
Die Funktion führt in einer Schleife solange die Ermittlung der übergeordneten Seite durch, bis sie an der Wurzel angekommen ist und baut dabei in der Variablen retval nach und nach das Ergebnis auf. Das Ganze ließe sich eleganter über eine Rekursion lösen, aber die unterstützt MySQL in der aktuellen Version noch nicht. Ein Trost: Diese iterative Variante ist schneller als eine Rekursion.
Die Änderung des Delimiters vom standardmäßigen Semikolon zu einem Dollarzeichen ist notwendig, sonst würden die trennenden Strichpunkte innerhalb der Funktion von MySQL als Endemarke für den aktuellen Befehl angesehen. Der letzte Befehl stellt wieder den normalen Zustand her.
Um nun beispielsweise den Pfad für die Seite mit der id 11 zu ermitteln, geben Sie ein:
SELECT f_rootpath(11);
Ein Ergebnis könnte dann so aussehen:
+-----------------+ | f_rootpath(11) | +-----------------+ | 11-2-1-0 | +-----------------+ 1 row IN SET (0.00 sec)
Trigger arbeiten automatisch
Wie Stored Procedures sind auch Trigger sind serverseitige Routinen, werden aber nicht vom Programmierer aufgerufen, sondern von einem Event angestoßen, etwa dem Löschen oder Ändern eines Datensatzes.
Ein Beispiel für den Einsatzzweck: Bei einer Kundenverwaltung möchte der Anwender jede Änderung im Datenbestand nachvollziehen können. Sobald also jemand in der Tabelle kunden ein Feld ändert, sollen die vorherigen Feldwerte in der Tabelle kunden_changes vermerkt werden, die denselben Satz an Feldern aufweist und zusätzlich ein Feld für den Zeitpunkt der Änderung besitzt.
Beschränken wir uns bei den Kundendaten auf die Felder id, strasse und
name, dann würde der dazu notwendige Befehl so aussehen:
CREATE TRIGGER kunden_change AFTER UPDATE ON kunden FOR EACH ROW INSERT INTO kunden_changes ( id, name, strasse, datechanged) VALUES (old.id, old.name, old.strasse, NOW()) ;
Sobald nun bestehende Kunden-Einträge geändert werden, startet die Trigger-Prozedur. Für jeden geänderten Satz wird in der Tabelle kunden_changes der Zustand vor der Änderung als neuer Datensatz abgespeichert. Um die Werte vor dem UPDATE zu referenzieren, dient der vordefinierte Präfix old bei den Feldbezeichnern.
Sie könnten sich mit dieser Methode zu jedem Kunden eine Änderungshistorie ausgeben lassen, die auflistet, welche Überarbeitungen in seinen Daten stattgefunden haben.
Andere Einsatzgebiete für Trigger sind das Behandeln von in einer anderen Tabelle gespeicherten Detaildaten. So könnten Sie damit beispielsweise beim Deaktivieren eines Lieferanten automatisch alle seine Artikel im eigenen Sortiment ausblenden lassen.
Views vereinfachen und schotten ab
Views sind eine weitere Neuerung in MySQL 5. Ein View benimmt sich wie ein Tabelle, tatsächlich steckt dahinter aber eine von Ihnen definierte Abfrage, die ihre Ergebnisse nur wie eine neue Tabelle darstellt.
Ein einfaches Beispiel bei einem Content-Management-System wäre ein
View, der alle derzeit gültigen Artikel ausgibt, also gelöschte oder zeitlich eingeschränkte Artikel unterdrückt. Er könnte so aussehen:
CREATE VIEW v_akt_artikel AS SELECT * FROM artikel WHERE deleted=0 AND publish_until < NOW();
Weil der View sich wie eine Tabelle einsetzen lässt, könnten Sie beispielsweise schreiben:
SELECT * FROM v_akt_artikel WHERE stichwort='Heimwerken';
Auch das Rechtesystem von MySQL behandelt Views wie Tabellen, und erlaubt deshalb eine Freigabe auf View-Ebene. Das ermöglicht zum Beispiel folgendes Szenario: Sie haben geschäftliche Kundendaten in Ihrer Datenbank und möchten die einem Geschäftspartner zugänglich machen. Allerdings soll er manche Felder nicht sehen dürfen, etwa die Umsatzdaten.
Um den eingeschränkten Zugriff zu ermöglichen, definieren Sie einen View, der nur die notwendigen Felder ausgibt und erlauben dem Partner lediglich darauf den Zugriff:
CREATE VIEW v_kunden_restr AS SELECT kundennr,name,plz,strasse,ort FROM kunden; GRANT SELECT ON v_kunden TO 'kunde'@'%' IDENTIFIED BY 'geheim';
Stored Procedures als Abstraktionsebene
Bei Applikationen im großen Stil sichern Stored Procedures die Integrität von Daten. Betrachten Sie beispielsweise das Verarbeiten von Bestellungen in einem Online-Shop. Der Kunde soll eine Bestellung stornieren dürfen, solange sie noch nicht ausgeführt ist. Ein Weg wäre, die Freigabe der bestellten Artikel und die Deaktivierung des Datensatzes in der BestellungsTabelle einzeln in der ShopApplikation zu lösen.
Würde man diese Aktionen aber in einer Stored Procedure zusammenfassen, die alle Validierungen durchführt und die Teilaspekte des Stornos erledigt, ergäben sich einige Vorteile. Ein Zugriff von einer anderen Plattform als dem Webshop könnte sich auch dieser existierenden Prozedur bedienen. Man würde sich also ersparen, alle Tabellenoperationen nochmals in einem anderen Kontext zu programmieren.
Varianten serverseitiger Routinen
Genau genommen handelt es sich bei der im Artikel gezeigten Routine f_rootpath() gar nicht um eine Stored Procedure, sondern um eine Stored Function. Diese Variante wurde verwendet, weil für die Aufgabenstellung ein Rückgabewert benötigt wird.
Die echten Procedures werden im Gegensatz dazu mit CREATE PROCEDURE definiert und haben einen leicht abweichenden Aufbau. So verzichten sie beispielsweise auf das RETURN, das bei den Funktionen den Rücksprung zum Aufrufer und die Übermittlung des Funktionswerts bewirkt.
Ein weiterer wichtiger Unterschied: Stored Procedures werden nicht per SELECT sondern über CALL aufgerufen
Rückgabewerte sind hier prinzipiell auch vorgesehen. Dann allerdings müssen Sie SQL-Variablen als Rückgabeparameter einsetzen. Die Verwendung einer als Stored Procedure realisierten sp_rootpath() mit einem zusätzlichen Parameter zur Rückgabe würde dann so aussehen:
CALL sp_rootpath(11,@pfad);
SELECT @pfad; Stored
Procedures mit phpMyAdmin
Mit phpMyAdmin klappt die Definition einer Stored Procedure in der Grundkonfiguration nicht. Erst, wenn Sie vom aktiviertem mysql-Interface auf die neuere Variante mysqli umschalten, lässt sich phpMyAdmin einsetzen.
Dazu ändern Sie in der Datei config.inc.php die Zeile, die die “Server-Extension” definiert. Fügen Sie dort ein “i” hinzu, so dass dort steht:
$cfg['Servers'][$i] ['extension']='mysqli'
Noch besser geeignet ist der Query-Browser von MySQL, der Stored Procedures mit Funktionen wie Syntax-Highlighting unterstützt. Dieses kostenlose Programm können Sie einfach von der MySQL-Homepage als Teil des Pakets “GUI-Tools” herunterladen.
Write a Comment