Dotaz: Mysql - zaručení vzájemné integrity mezi 2 tabulkami

[mlynar]

Ahoj. Mám 2 tabulky:

uzivatele: uid, nazev, typ, .... (uid je primarni klic a nazev je unikatni)
opravneni: admin_nazev, admin_cil, muze_cist, aktualizovano.... (admin_nazev je cizi klic uzivatele(nazev) a admin_cil je cizi klic uzivatele(uid)

(mimochodem 2 cizí klíče na 2 různé unikátní hodnoty v tab. oprávnění nejsou chyba návrhu, ale chyba mysql, kter8 neumožňuje odkazovat dvěma sloupci stejné tabulky na jeden v jiné (minimálně nefunguje CASCADE update))

Celé je to koncipované tak, že pokud přibude do tabulky uzivatele uživatel s typem admin nebo pokud po updatu bude uživatel admin, pak se spustí trigger a založí nebo aktualizuje potřebné řádky v tabulce opravneni tzn. vloží se páry admin_nazev a admin_cil, což budou všechna uid v tabulce, kromě uid administrátora. Toto celé krásně funguje, ale bojím se, aby se to nerozbilo, když integritu zaručují jen triggery (např. kdyby se dotaz nepouštěl v transakci a v místě po vložení uživatele a před spuštením after triggeru by došlo k výpadku). Jsou mé obavy oprávněné nebo už to má databáze vyřešené příp. jak to řešit?

Odpovědi

[l0gik]

Jsou oprávněné. s triggerem je více problémů, např. trigger se při cascade update nespustí (další chyba Mysql). Pokud chceš používat takovýdle věci, nejlepší řešení je použít postgresql. Nicméně na druhou stranu pokud někdo poleze do tabulky bez transakcí, tak konzistenci prostě nezaručíš nikdy, to se zas na MySQL házet nedá. - Postgresql by Ti ale zajistila normální chování ve všem ostatním, např. cizí klíče, správné spiouštění triggerů. Také by to v ní šlo udělat ne tak "humpolácky": místo automatickýho přidávání všech neadminích bys moh zavést "grupové uživatele" a při přidání admina mu dát práva k grupě neadmini, při přidání neadmina dát grupě neadmini práva k tomuto uživateli a pomocí rekurzivních CTE počítat aktuální práva atd...

[mlynar]

To s tím cascade update triggerem mě netrápí - nepoužívám. Takže kromě transakcí není jiná možnost, jak zaručit integritu těch dat v tabulce opravneni? Počítám s tím, že všechny kritické dotazy budu pouštět v transakci, ale nechci se dočkat toho, že chyby v aplikaci způsobí nekonzistenci celé databáze. Jako výchozí chování zapínám autocommiting, ale to asi neobsáhne after trigger nebo ano? Když spustím třeba INSERT aniž bych předtím ručně odstartoval transakci - provedou se alespoň nějaké triggery v transakci?

[mlynar]

Tak nikde jsem to nenašel, ale vyzkoušel jsem, že pokud ručně nezačnu transakci (tzn. použije se autocommit) a spustím nějaký insert/update dotaz, který skončí chybou v after triggeru, tak se provede rollback a je to konzitentní. Takže vlastně žádná nekonzistence v těch datech nemůže nastat ne?

[l0gik]

Jo, autocommit je vlastně jeden řádek jedna transakce, to máš pravdu, to zachová, pokud tam nebudeš dělat operace přes FK ignorující trigger, tak by to mělo bejt Ok. - Doporučil bych Ti ale stejně si dávat do tabulky opravnění nějaký flagy, čím byl daný řádek vygenerován. Aby kdybys tam měl nějakou chybu, tak abys moh ty automaticky generované smazat a vygenerovat znova.

[Senior Database Programmer]

MySQL umoznuje "odkazovat dvěma sloupci stejné tabulky na jeden v jiné":

CREATE TABLE `uzivatele` (
	`uid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`nazev` VARCHAR(255) NOT NULL,
	`typ` ENUM('admin','user') NOT NULL DEFAULT 'user',
	PRIMARY KEY (`uid`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

CREATE TABLE `opravneni` (
	`oid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`uid1` INT(10) UNSIGNED NOT NULL,
	`uid2` INT(10) UNSIGNED NOT NULL,
	`muze_cist` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
	PRIMARY KEY (`oid`),
	INDEX `fk2` (`uid2`),
	INDEX `fk1` (`uid1`),
	CONSTRAINT `fk2` FOREIGN KEY (`uid2`) REFERENCES `uzivatele` (`uid`) ON UPDATE CASCADE,
	CONSTRAINT `fk1` FOREIGN KEY (`uid1`) REFERENCES `uzivatele` (`uid`) ON UPDATE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

V tabulke "opravneni" mam dva stlpce (uid1, uid2) ktore obidva odkazuju cez cudzi kluc na jeden a ten isty stlpec v tabulke "uzivatele" (uid). Dokonca aj ten UPDATE CASCADE funguje...ked zmenim uid nejakeho zaznamu v tabulke "uzivatele" tak sa zmeni aj v tabulke "opravneni"...

[mlynar]

Zkuste do tabulky oprávnění vložit řádek, kde uid1=uid2, při pokusu o změnu nebo odstranění uid z tabulky uzivatele dostanete error.

[Senior Database Programmer]

Skusil som: Vlozim do tab. opravneni riadok s uid1=uid2 (3):

INSERT INTO `opravneni` (`uid1`, `uid2`, `muze_cist`) VALUES (3, 3, 1);

Zmena uid v tabulke "uzivatele" (z 3 na 4):

UPDATE `uzivatele` SET `uid`=4 WHERE `uid`=3 LIMIT 1;

Ziadna chyba, kaskada sa vykonala a v tabulke "opravneni" sa uid zmenili z 3 na 4.

To iste s mazanim:

Zmazem uzivatel z tabulky "uzivatele"

DELETE FROM `uzivatele` WHERE `uid`=4 LIMIT 1;

Ziadna chyba, kaskada sa vykonala a zmazal sa zaznam aj v tabulke "opravneni"

Nerobite niekde chybu?

[l0gik]

Já mám dojem, že mi to v mysql někdy fungovalo a někdy ne. Možná záleží na použité verzi, mysql se dosti zlepšuje v tom, co umí.

[Kit]

Podle mne jsou 2 cizí klíče odkazující do jedné tabulky chybou v návrhu. Zejména v uvedeném případě.

Dovedu si však představit situaci, kdy by to chybou být nemuselo např. v genealogii trojice (potomek, otec, matka) - na to dokonce stačí jedna tabulka.

[l0gik]

Tak zrovna genealogie lidí je daleko lepší dvěma tabulka: lidé a vztah. Když to dáš do jedný tabulky, tak se Ti budou špatně hledat libovolný příbuzenský vztahy. Vztah po meči a po přeslici patří do stejného sloupce.

Jak byste to chtěl udělat tady, když tu evidentně existuje vztah uživatel - uživatel? Jak jinak než extra tabulkou s dvěma cizími klíči na uživatele?

Elegantnější řešení by bylo zavést ještě skupiny (jako speciálního uživatele), aby se nemusely řešit triggerama práva práva "superuživatelů". Jenže bez rekurzivních CTE se s tím pak hrozně blbě pracuje.

[Kit]

Tak vida. Genealogii jsem nikdy nedělal a hned bych ji udělal špatně. Tím mi odpadá problém se dvěma cizími klíči do jedné tabulky.

Zmíněný problém bych asi řešil skupinami, ale to by byla další tabulka navíc. Jenže je to osvědčené řešení. Navíc se tím zbavíme nechtěných triggerů a umožníme vznik rolí skupinových správců.

Na autorizační záležitosti se relační databáze moc nehodí, vhodnější jsou hierarchické. U těch tolik nevadí rekurzivní algoritmy, neboť jsou lépe optimalizovány na rychlost při čtení.

[l0gik]

I v mém navrženém schématu máš dva odkazy do jedné tabulky. Genealogie je 2:n relace a jako taková se tomu nevyhne.

Implementovat skupiny zvlášť je zbytečné, daleko lepší je implementovat je jako "speciální uživatele". Už proto, že to transparentně umožní skupiny vnořovat atd.

To máte sice pravdu, ale rekurzivní CTE tento problém vcelku efektivně řeší. Navíc když mám ostatní data v normální DB, tak je to zas natolik velká programátorská obtíž, a počty uživatelů málokdy sahají do takového počtu, by byl problém výkon. V případě potřeby bych to spíš obalil nějakou stored procedure a tabulkou s předpočítanými právy (to už se dostáváme k řešení podobným jako je ten trigger).

[Kit]

2:n se dá zobecnit jako m:n, takže se dostáváme k tomu jak jsi psal: V jedné tabulce lidi, ve druhé vztahy. Tím vlastně odpadá problém dvou cizích klíčů, neboť po normalizaci se tyto cizí klíče dostanou do jednoho sloupce pod sebe.

Ano, skupiny jsou listové uzly, proto je můžeme dát do stejné tabulky jako uživatele. Musíme však vytvořit další tabulku, ve které jsou definovány vztahy mezi uživateli a skupinami, neboť se opět jedná o vztah m:n.

Je to však obecné řešení. Pokud potřebuji jen odlišit privilegované a neprivilegované uživatele, dá se to udělat jedním příznakem.

Rekurzivní CTE beru jen jako berličku pro obhájení relačních databází před hierarchickými. Na každý problém by se měl vybírat vhodný prostředek. Uznávám, že pro malý počet záznamů to může být víceméně jedno.

[l0gik]

- Ale vztah přeci má dva sloupce kdo s kým. Takže opět dva cizí klíče na jeden sloupec.

- Žádný nástroj není vhodný na vše. Přesto je často vhodnější použít na první pohled "nevhodnější nástroj". Např. proto, že rozstrkáním dat do různých úložišť dle vhodnosti člověk přijde o atomicitu transakcí, jednoduchost zálohy atd... U běžného IS je IMHO cpát uživatele do nějaké spešl hierarchické DB blbina.

- Pokud uděláme skupiny, lze pro vztah uživatel-skupina použít současnou tabulku uživatel-uživatel. I to je argument pro to, dát skupiny do stejné tabulky s uživateli. Nehledě na to, že práva se budou chtít odkazovat FK na jednu tabulku.