anpera.net
http://anpera.homeip.net/phpbb3/

[Ressource][PHP5] Trigger-Factory
http://anpera.homeip.net/phpbb3/viewtopic.php?f=43&t=4980
Seite 1 von 1

Autor:  Eliwood [ So 23 Aug, 2009 14:31 ]
Betreff des Beitrags:  [Ressource][PHP5] Trigger-Factory

Ich habe mich in letzter Zeit etwas mehr mit SQL an sich beschäftigt und bin dabei auf einige intressante Features gestossen, die nirgendwo in LoGD verwendet werden aber durchaus nützlich sein können. Und zwar sind das die sogenannten Trigger - Datenbankevents, die bei bestimmten Momenten ausgelöst werden und SQL-Code ausführt.

Man kann so die ganzen Account-Lösch-Aufräumarbeiten in die Datenbank auslagern. Die Vorteile liegen auf der Hand: Da die Latenz PHP<=>MySQL wegfällt sparrt man damit Zeit. Und man kann sich darauf verlassen, dass die Datenintegrität immer gegeben ist - wenn man zwei verschiedene PHP-Löschroutinen hat und die nicht synchron hält, können durchaus noch Reste übrig bleiben.

Leider hat das ganze auch einige Nachteile: Zuerst einmal braucht man für Trigger mindestens MySQL in der Version 5. Und dann ist es auch nicht garantiert, dass das Trigger-Erstellen mit jeder PHP-Version funktioniert (mysql_query funktioniert mit PHP 5.2.6, vermutlich nicht mit PHP < 5.). Das heisst, ich empfehle das, was ich hier vorstelle nur Leuten, die auch wissen, was sie tun. ;)

Die Triggerfactory, die ich hier vorstelle und aus YaGD portiert wurde, kümmert sich um das erstellen der Trigger und das löschen der selben, um das aktualisieren. Die Informationen werden systematisch in einer Tabelle gespeichert. Der Vorteil der ganzen Geschichte ist, dass die Triggerfactory theoretisch Module unterstützt. ;)

$this->bbcode_second_pass_code('', 'CREATE TABLE IF NOT EXISTS `triggers` (
triggertable varchar(128) NOT NULL,
triggertype varchar(7) NOT NULL,
triggerposition varchar(6) NOT NULL,
triggeridentifier varchar(64) NOT NULL,
triggercode text NOT NULL,
PRIMARY KEY (triggertable,triggertype,triggerposition,triggeridentifier)
);')

$this->bbcode_second_pass_code('', '/*
* Trigger: A factory which builds mysql triggers automaticly.
*
* Dependencies:
* - LoGD-0.9.7+jt ext GER 3
* - function db_query_secure()
*
* Copyright 2009 Basilius Sauter <basilius.sauter@hispeed.ch>
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
* MA 02110-1301, USA.
*/

class Trigger {
protected $type = array(
'INSERT' => 'INSERT',
'UPDATE' => 'UPDATE',
'DELETE' => 'DELETE'
);

protected $position = array(
'AFTER' => 'AFTER',
'BEFORE' => 'BEFORE',
);

# creates a trigger-event.
# $id: A Identifier, like a modulename. Must be Unique with $table, $position and $type.
# $table: The table that should be surveilled by MySQL.
# $position: AFTER or BEFORE an Event.
# $type: INSERT, UPDATE or DELETE. Not the same as the MySQL-Commands INSERT INTO, UPDATE or DELETE FROM.
# $code: MySQL-Code which should be executed. Can contents multiple queries.
public static function add($id, $table, $position, $type, $code) {
$position = strToUpper($position);
$type = strToUpper($type);

$sql = 'INSERT INTO triggers (triggertable, triggertype, triggerposition, triggeridentifier, triggercode)
VALUES (?,?,?,?,?)
ON DUPLICATE KEY UPDATE triggercode = VALUES(triggercode)';
$args = array($table, $type, $position, $id, $code);

db_query_secure($sql, $args);

self::rebuildTrigger($table, $position, $type);
}

# Drops a trigger-event
# $id: A Identifier, like a modulename. Must be Unique with $table, $position and $type.
# $table: The table that should be surveilled by MySQL.
# $position: AFTER or BEFORE an Event.
# $type: INSERT, UPDATE or DELETE. Not the same as the MySQL-Commands INSERT INTO, UPDATE or DELETE FROM.
public static function drop($id, $table, $position, $type) {
$position = strToUpper($position);
$type = strToUpper($type);

$sql = 'DELETE FROM triggers WHERE triggertable = ? AND triggerposition = ? AND triggertype = ? AND triggeridentifier = ?';
$args = array($table, $position, $type, $id);

db_query_secure($sql, $args);

self::rebuildTrigger($table, $position, $type);
}

# Rebuilds the triggers $table-$position-$type.
protected static function rebuildTrigger($table, $position, $type) {
$triggername = self::getTriggerName($table, $position, $type);

$sql = 'SELECT triggercode FROM triggers WHERE triggertable = ? AND triggerposition = ? AND triggertype = ?';
$args = array($table, $position, $type);

$res= db_query_secure($sql, $args);

if(db_num_rows($res) > 0) {
// Killing Trigger
$triggerSQL = sprintf('DROP TRIGGER IF EXISTS %s', $triggername);
db_query($triggerSQL);
unset($triggerSQL);

$code = array();
# Fix by Harthas. Replaces YaGD-Code.
while( $row = db_fetch_assoc( $res ) ) {
array_push($code, $row['triggercode']);
unset($row);
}

$triggerSQL = sprintf('CREATE TRIGGER %s
%s %s ON %s
FOR EACH ROW
BEGIN
%s
END;
', $triggername, $position, $type, $table, implode("\n", $code));
}
else {
$triggerSQL = sprintf('DROP TRIGGER IF EXISTS %s', $triggername);
}

db_query($triggerSQL);
}

# Returns the systematic trigger name from $table, $position and $type.
protected static function getTriggerName($table, $position, $type) {
return strtolower(sprintf('%s_%s_%s', $table, $position, $type));
}
}')

Autor:  Eliwood [ So 23 Aug, 2009 15:09 ]
Betreff des Beitrags:  Re: [Ressource][PHP5] Trigger-Factory

Und jetzt noch n’paar Beispiele, wie solche Trigger hinzugefügt werden können.

Das erste Beispiel erledigt die Aufräumarbeiten beim Löschen eines Accounts. Haus und Items werden enteignet, PVP-Duelle werden gelöscht und der Spieler wird geschieden:
$this->bbcode_second_pass_code('', '$sql = <<<SQL
UPDATE houses SET owner=0, status=3 WHERE owner = OLD.acctid AND status = 1;
UPDATE houses SET owner=0, status=4 WHERE owner = OLD.acctid AND status = 0;
UPDATE items SET owner=0 WHERE owner = OLD.acctid;
DELETE FROM pvp WHERE acctid2 = OLD.acctid OR acctid1 = OLD.acctid;
UPDATE accounts SET charisma = 0, marriedto = 0 WHERE marriedto = OLD.acctid;
SQL;

Trigger::add('accounts_cleanup', 'accounts', 'AFTER', 'DELETE', $sql);')

Das zweite Beispiel übernimmt den Drachenkill: Sobald Drachenkill hochgezählt wird, werden die Items gelöscht und der PVP ausgesetzt:
$this->bbcode_second_pass_code('', '$sql = <<<SQL
IF NEW.dk > OLD.dk THEN
DELETE FROM pvp WHERE acctid1 = OLD.acctid OR acctid2 = OLD.acctid;
DELETE FROM items WHERE owner = OLD.acctid AND class IN ('Beute', 'Fluch', 'Geschenk', 'Schmuck', 'Waffe', 'Rüstung', 'Zauber');
END IF;
SQL;

Trigger::add('accounts_dragonkill', 'accounts', 'AFTER', 'UPDATE', $sql);')

Das letzte Beispiel fügt die Wilkommens-News ein beim Erstellen eines Accounts. Hier könnte man auch automatische Yoms verteilen lassen und so.
$this->bbcode_second_pass_code('', '$sql = <<<SQL
INSERT INTO news (newstext, newsdate, accountid) VALUES (CONCAT(NEW.name, '`# hat unsere Welt betreten. Willkommen!'), NOW(), NEW.acctid);
SQL;

Trigger::add('accounts_newaccount', 'accounts', 'AFTER', 'INSERT', $sql);')

Autor:  Linus [ So 23 Aug, 2009 23:35 ]
Betreff des Beitrags:  Re: [Ressource][PHP5] Trigger-Factory

Eliwood hat geschrieben:
Das letzte Beispiel fügt die Wilkommens-News ein beim Erstellen eines Accounts. Hier könnte man auch automatische Yoms verteilen lassen und so.
$this->bbcode_second_pass_code('', '$sql = <<<SQL
INSERT INTO news (newstext, newsdate, accountid) VALUES (CONCAT(NEW.name, '`# hat unsere Welt betreten. Willkommen!'), NOW(), NEW.acctid);
SQL;

Trigger::add('accounts_newaccount', 'accounts', 'AFTER', 'INSERT', $sql);')


Hört sich ja mächtig gut an. Doch durchschaue ich nicht woher der MySQL-Dienst denn jetzt weiß das ein Event ausgelöst wurde, und der entsprechende Trigger aktiv werden soll. Ist da nun bei 'NEW.acctid', das 'NEW.' vor acctid ein Schlüsselwort für die Datenbank? Oder wie funktioniert das Ganze? :???:

Autor:  Garlant [ Mo 24 Aug, 2009 08:54 ]
Betreff des Beitrags:  Re: [Ressource][PHP5] Trigger-Factory

Hallo Linus,

Ja, das NEW ist ein Datenbank-Schlüsselwort in MySQL (wie auch in PgSQL, MsSQL hat da was anderes).
NEW, wie auch OLD sind temporäre Tabellen in welcher sich Datensätze vor dem insert und nach dem delete (noch) kurzzeitig befinden. Wird ein Trigger auf ein Delete-Query abgefeuert (gehen wir davon aus, dass es ein 'after'-Trigger ist), so kann man z.B. den eben gelöschen Datensatz nutzen um abhängige Daten zu löschen. ...

Autor:  Eliwood [ Mo 24 Aug, 2009 13:04 ]
Betreff des Beitrags:  Re: [Ressource][PHP5] Trigger-Factory

Linus hat geschrieben:
Hört sich ja mächtig gut an. Doch durchschaue ich nicht woher der MySQL-Dienst denn jetzt weiß das ein Event ausgelöst wurde, und der entsprechende Trigger aktiv werden soll. Ist da nun bei 'NEW.acctid', das 'NEW.' vor acctid ein Schlüsselwort für die Datenbank? Oder wie funktioniert das Ganze? :???:


Meine Triggerfactory erstellt nur die Trigger für die Datenbank (Mit CREATE TRIGGER), damit sie leichter zu verwalten sind im Code. Die Events löst MySQL selbstverständlich selbst aus und weiss deshalb auch, was es auslösen soll.

Wenn es dich intressiert: Wikipedia weiss auch dazu was.

Autor:  Linus [ Mo 24 Aug, 2009 16:47 ]
Betreff des Beitrags:  Re: [Ressource][PHP5] Trigger-Factory

Aha! Danke euch beiden für die Aufklärung. Ich werde das mal auf einem Test-Webspace versuchen umzusetzen, und natürlich ausgiebig testen bevor es eventuell Einzug in mein eigentliches Projekt hält! :)

Autor:  Garlant [ Di 25 Aug, 2009 08:43 ]
Betreff des Beitrags:  Re: [Ressource][PHP5] Trigger-Factory

Es bieten sich durchaus neue Möglichkeiten, wenn man die Nutzung von Views in betracht zieht. ;)
(Auch wenn das jetzt in diesem Thema nichts mit Triggern zu tun hat. Vielleicht hat sich ja Eli schon in dem Bereich ein wenig umgesehen und zeigt etwas neues.)

Autor:  Harthas [ Sa 05 Sep, 2009 00:20 ]
Betreff des Beitrags:  Re: [Ressource][PHP5] Trigger-Factory

Mir sind da noch 2 kleine Flüchtigkeitsfehler aufgefallen:

1. Fehlerhafter MySQL-Feldtyp
$this->bbcode_second_pass_code('', ' triggercode ext NOT NULL,')
sollte wohl
$this->bbcode_second_pass_code('', ' triggercode text NOT NULL,')
heissen.

2. Falsch durchlaufene Query-Resultate
$this->bbcode_second_pass_code('', ' $code = array();

foreach($q as $row) {
array_push($code, $row['triggercode']);
unset($row);
}')
Hab' ich bei mir nun so korrigiert. ;-)
$this->bbcode_second_pass_code('', '
$code = array();
while( $row = db_fetch_assoc( $res ) ) {
array_push($code, $row['triggercode']);
unset($row);
}')

Hat der Verfasser dies auch ungefähr so gedacht - Oder hab ich was falsch interpretiert?

Schönen Abend wünsche ich.

Autor:  Eliwood [ Sa 05 Sep, 2009 13:57 ]
Betreff des Beitrags:  Re: [Ressource][PHP5] Trigger-Factory

Ja, das ext war ein Flüchtigkeitsfehler. Und das foreach eine nicht-angepasste Codestelle. Werds anpassen, danke Harthas.

Seite 1 von 1 Alle Zeiten sind UTC + 1 Stunde
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/