Základy SQL, část 2.

Tento článek navazuje na Základy SQL, část 1., kde jsem psal především o SELECTech a JOINech na MySQL databázi.

V této části se budu věnovat tvorbě VIEW a TRIGGER. Na uvedenou logiku je potřeba minimálně MySQL 5.0 a vyšší (popřípadě libovolná 15 roků stará Oracle databáze).

CREATE VIEW

Představte si, že pracujete na projektu, který má za úkol sjednotit dva velice rozdílné projekty pod jedním uživatelským účtem a společným přihlášením. Třeba BLOG:CMS pro publikaci článků a IP.Board jako fórum.

BLOG:CMS používá pro autorizaci tabulku members, IP.Board pro stejný účel používá hned 3 tabulky, a to ipb_members (přihlašovací jméno), ipb_member_extra (email) a ipb_member_converge (hash hesla). Naštěstí jsou veškeré údaje, které BLOG:CMS potřebuje, obsažené i v IP.Board tabulkách. Toto celé svádí k tomu zjednodušit si práci tím, že tabulku members v BLOG:CMS dropnu a místo ní udělám pohled (VIEW) na 3 tabulky IP.Boardu.

Například takto:

DROP TABLE `member`;

CREATE VIEW `member` AS 
SELECT 
	`m`.`id` AS `mnumber`,
	`m`.`name` AS `mname`,
	`m`.`members_display_name` AS `mrealname`,
	`m`.`email` AS `memail`,
	`e`.`website` AS `murl`,
	`e`.`notes` AS `mnotes`,
	`c`.`converge_pass_hash` AS `mpassword`,
	`c`.`converge_pass_salt` AS `mcookiehash`,
	`m`.`member_login_key` AS `mcookiekey`,
	`e`.`mcanlogin` AS `mcanlogin`,
	`e`.`deflang` AS `deflang`,
	`e`.`madmin` AS `madmin` 
FROM (
	(`ipb_members` `m` 
	JOIN `ipb_member_extra` `e` ON ((`m`.`id` = `e`.`id`))) 
	JOIN `ipb_members_converge` `c` ON 
		((`m`.`id` = `c`.`converge_id`)));

Pro PHP kód bude celá operace zcela transparentní, nepozná, zda se autorizuje proti TABLE member či nově proti VIEW member. Tímto jednoduchým způsobem je možné docílit toho, že dvě zcela různé aplikace sdílejí během pár sekund jednu databázi uživatelů (a protože IP.Board má skvělé prostředky pro správu uživatelů, byla zvolena jeho databáze).

Podobně je VIEW možné používat kdekoliv, kde jinde použijete tabulku s WHERE podmímkou. V redakčním systému je dobré si vytvořit VIEW, které bude filtrovat zatím nepublikované články (drafts) a články, které jsou určeny k publikaci k pozdějšímu datu. Tvorbou tohoto jednoho VIEW ušetříte programátorský čas a zpřehledníte aplikaci (nehovoře o snazším uzpůsobení), nebudete totiž muset na desítkách míst opakovat ty samé WHERE podmínky. Také je to dobrá prevence chyb, jedno VIEW se ladí lépe než desítky WHERE podmínek v PHP kódu.

Používat VIEW místo natvrdo psaných WHERE podmínek je základ dobrého programátorského stylu. Bohužel, mimořádně primitivní MySQL databáze (nazývám ji spíše filesystém) tuto triviální funkčnost umí až od verze 5.0.

CREATE TRIGGER

Nyní tedy BLOG:CMS umí používat databázi uživatelů z IP.Boardu. To ale není celé. Existují v něm i tabulky team a další, které říkají, zda a kam daný uživatel může publikovat článek.

Snadné napojení na tyto tabulky, a plně automatické, je možné implementovat pomocí triggerů. TRIGGER je akce, která se automaticky provede, pokud je to tabulky vložen nový řádek (INSERT), aktualizován řádek (UPDATE) či smazán řádek (DELETE).

V mém případě mám skupinu moderátorů s číslem mgroup=7, které chci vždy povolit i publikování článků v BLOG:CMS. Protože správa uživatelů je v IP.Boardu na několika místech, a upravovat PHP kód je pošetilé pro další upgrade, dá se to vyřešit snadno a koncepčně pomocí TRIGGERu:

CREATE TRIGGER `newmember_to_team` 
BEFORE UPDATE ON `ipb_members` 
FOR EACH ROW 
BEGIN
	IF (NEW.mgroup <> OLD.mgroup) THEN
		DELETE FROM team WHERE tmember = NEW.id;
		IF (NEW.mgroup=7) THEN
			INSERT INTO team (tmember, tblog, tadmin) 
				VALUES (NEW.id, 24, 0);
			DELETE FROM plugin_option 
				WHERE oid=289 AND ocd=NEW.id;
			INSERT INTO plugin_option (ovalue,oid,ocd) 
				VALUES ('xstandard', 289, NEW.id);
		END IF;
	END IF;
END;

Tento TRIGGER udělá před aktualizací (BEFORE UPDATE) tabulky ipb_members to, že se podívá, zda nová (NEW) hodnota skupiny je odlišná od té staré, zda se rovná moderátorům (NEW.mgroup=7), a pokud ano, zapíše do tabulky BLOG:CMS team práva pro tohoto uživatele pro publikaci článků (v opačném případě práva odebere).

Vše se děje zcela automaticky a na úrovni databáze. V PHP se o nic neumíte starat. Kromě nastavení práv TRIGGER upravuje i hodnotu v pluginu WYSIWYG editoru na možnost "xstandard".

Následně si napíšeme ještě ON DELETE TRIGGER, který odebere práva v BLOG:CMS v případě, že je uživatel z databáze IP.Boardu vymazán:

CREATE TRIGGER `delete_member_from_team` 
BEFORE DELETE ON `ipb_members` 
FOR EACH ROW 
BEGIN
	DELETE FROM team WHERE tmember = OLD.id;
	DELETE FROM plugin_option 
		WHERE oid=289 AND ocd=OLD.id;
END;

V TRIGGERrech můžete používat NEW. a OLD. předpony, které značí staré a nové hodnoty dané položky. V případě UPDATE jsou povolené obě, v případě INSERT pochopitelně jen NEW. a v případě DELETE logicky jen OLD.

Závěr

Pomocí používání VIEW a TRIGGER můžete napsat robustnější aplikaci, která zajišťuje za všech okolností konzistenci dat, a její provoz, návrh a správa je mnohem jednodušší. Více dokumentace na dev.MySQL.com.