MySQL - podmíněný SELECT přes dvě tabulky

[Miroslav Šilhavý]

Ovšem když chcete někoho pustit ke své službě, neznamená to, že nejlepší je zpřístupnit mu databázi.

Ano, na to uděláte nějaký middleware, ale funkce by měla obstarávat databáze.

[Reklama]

[Kit]

Nejlepším databázovým API je SQL.

Ovšem když chcete někoho pustit ke své službě, neznamená to, že nejlepší je zpřístupnit mu databázi.

Když správce databáze umí používat GRANT, vložené procedury a triggery, tak se nemusí bát k ní někoho pustit.

[Miroslav Šilhavý]

Když správce databáze umí používat GRANT, vložené procedury a triggery, tak se nemusí bát k ní někoho pustit.

Na MySQL bude problém s oprávněním k datům uvnitř aplikace, tam asi nebude možné nic vymyslet kromě middlewaru, který provede ověření oprávnění k datům a filtrování řádků, které se nesmějí ven dostat.

Na PostgreSQL by to šlo poměrně bez problémů pomocí row-level-security a pomocí views s WITH secuirty_barrier.

[Kit]

Když správce databáze umí používat GRANT, vložené procedury a triggery, tak se nemusí bát k ní někoho pustit.

Na MySQL bude problém s oprávněním k datům uvnitř aplikace, tam asi nebude možné nic vymyslet kromě middlewaru, který provede ověření oprávnění k datům a filtrování řádků, které se nesmějí ven dostat.

Na PostgreSQL by to šlo poměrně bez problémů pomocí row-level-security a pomocí views s WITH secuirty_barrier.

Jde to i v MySQL. Stačí udělit přístupy jen k pohledům a vloženým procedurám. Odpadnou tím potíže se špinavými kešemi a zbytečně dlouhým zamykáním transakcí.

Celý middleware může být přímo v databázi. Spousta běžných potíží tím sama odpadne.

[Miroslav Šilhavý]

Jde to i v MySQL. Stačí udělit přístupy jen k pohledům a vloženým procedurám. Odpadnou tím potíže se špinavými kešemi a zbytečně dlouhým zamykáním transakcí.

Aha, já si nebyl jistý, jestli MySQL umí dostatečně bezpečně vystavit VIEW. Pak je to jasné, přístup DB je určitě dobrý způsob.

[Reklama]

[Filip Jirsák]

Když správce databáze umí používat GRANT, vložené procedury a triggery, tak se nemusí bát k ní někoho pustit.

Jistě, ale tady jsme se bavili o případu, kdy jeden diskutující triggery používat neumí. Navíc aplikační logiku je možné psát v databázi, ale má to své meze použitelnosti a složitější logiku je lepší psát jinde, než v databázi. To, že někdo umí jenom SELECT * FROM tabulka a vše ostatní – řazení, filtrování, spojování – řeší (typicky) v PHP, je jeden extrém. Ale komplikovaná logika nacpaná v triggerech a uložených procedurách je druhý extrém.

[Miroslav Šilhavý]

Navíc aplikační logiku je možné psát v databázi, ale má to své meze použitelnosti a složitější logiku je lepší psát jinde, než v databázi. To, že někdo umí jenom SELECT * FROM tabulka a vše ostatní – řazení, filtrování, spojování – řeší (typicky) v PHP, je jeden extrém.

Podle toho, čemu říkáte "aplikační logika". Typicky přístup a pohledy na data jsou databázová záležitost, stejně jako zajištění konzistence dat. Zejména v případech, kdy do databáze přistupuje víc aplikací (přes API - včetně přímého vstupu do databáze). Pak se Vám sakra moc nehodí mít logiku v aplikaci (PHP), protože tu samou logiku pak musíte znovu a znovu zavádět do každého dalšího API, které nad data postavíte. To se dřív nebo později stane neudržitelné.

Typicky se nad určitou oblastí staví velké view, které podchycuje vazby. Nad toto view se pak staví další konkrétní pohledy - které mohou přidávat filtrování, ošetření bezpečnosti atd. atd.

Ale komplikovaná logika nacpaná v triggerech a uložených procedurách je druhý extrém.

Naopak komplikovaná logika patří právě sem. Databázi obvykle řeší mnohem kvalifikovanější pracovník, než ten, který staví aplikaci. Tomu se dávají k dispozici jen data, která potřebuje. Triggery pak zajišťují ověření, nebo např. tvoření historie záznamu atd. Pokud to někdo dělá v aplikaci (PHP), je to nešťastné.

[Kit]

Když správce databáze umí používat GRANT, vložené procedury a triggery, tak se nemusí bát k ní někoho pustit.

Jistě, ale tady jsme se bavili o případu, kdy jeden diskutující triggery používat neumí. Navíc aplikační logiku je možné psát v databázi, ale má to své meze použitelnosti a složitější logiku je lepší psát jinde, než v databázi. To, že někdo umí jenom SELECT * FROM tabulka a vše ostatní – řazení, filtrování, spojování – řeší (typicky) v PHP, je jeden extrém. Ale komplikovaná logika nacpaná v triggerech a uložených procedurách je druhý extrém.

Nemusíme jít ani do jednoho extrému. Často úplně stačí, když si databáze udrží konzistenci, referenční integritu. Generování výstupního XML/HTML je sice v databázi možné, ale je to hloupé. Opačně jsem viděl ve výstupní šabloně volat SQL dotazy, což je zpravidla také špatně.

[Kit]

Ale komplikovaná logika nacpaná v triggerech a uložených procedurách je druhý extrém.

Naopak komplikovaná logika patří právě sem. Databázi obvykle řeší mnohem kvalifikovanější pracovník, než ten, který staví aplikaci. Tomu se dávají k dispozici jen data, která potřebuje. Triggery pak zajišťují ověření, nebo např. tvoření historie záznamu atd. Pokud to někdo dělá v aplikaci (PHP), je to nešťastné.

Bohužel mnoho firem nemá prostředky na kvalifikovaného DB specialistu a nechávají to na polovzdělaných PHP programátorech, kteří jen umí spoléhat na ORM frameworky, ale o databázovém modelování nemají ani ponětí.

[Filip Jirsák]

Nemusíme jít ani do jednoho extrému. Často úplně stačí, když si databáze udrží konzistenci, referenční integritu. Generování výstupního XML/HTML je sice v databázi možné, ale je to hloupé. Opačně jsem viděl ve výstupní šabloně volat SQL dotazy, což je zpravidla také špatně.

Je tu jeden diskutující, který si nevhodným návrhem databáze zkomplikoval situaci (místo konzistence dané definicí potřeboval nějak udržovat konzistenci), aby následně prohlásil, že pomocí cizích klíčů a check constraints tu konzistenci zaručit neumí. Proto jsem psal, že je možné tu kontrolu dělat i v aplikaci. V tomto případě to sice není vhodné, ale jsou jiné případy, kdy jsou kontroly tak komplexní, že nemá smysl je provádět v databázi.

[Vilith]

Už toho nech  - to se nedá číst.

Co může kontrolovat DB, má kontrolovat DB - jedině tak zajistíš konzistentní a správná data.

Každá kontrola dat v aplikaci je jen zdrojem možných problémů (stavíte novou aplikaci, další API, jiný programátor)

Pokud tedy neděláš aplikaci podobnou evidenci knížek v domácí knihovně...

Nemusíme jít ani do jednoho extrému. Často úplně stačí, když si databáze udrží konzistenci, referenční integritu. Generování výstupního XML/HTML je sice v databázi možné, ale je to hloupé. Opačně jsem viděl ve výstupní šabloně volat SQL dotazy, což je zpravidla také špatně.

Je tu jeden diskutující, který si nevhodným návrhem databáze zkomplikoval situaci (místo konzistence dané definicí potřeboval nějak udržovat konzistenci), aby následně prohlásil, že pomocí cizích klíčů a check constraints tu konzistenci zaručit neumí. Proto jsem psal, že je možné tu kontrolu dělat i v aplikaci. V tomto případě to sice není vhodné, ale jsou jiné případy, kdy jsou kontroly tak komplexní, že nemá smysl je provádět v databázi.

[Logik]

Zadání je funkční.

Na konkrétních příkladech - dokonce i Tvých - jsem ukázal, že takové zadání v praxi nebude fungovat dobře. Tyto příklady jsi nijak nevyvrátil. Budeme diskutovat způsobem, že kdo víckrát zařve svoji pravdu, tak vyhrál? Nebo budeme argumentovat?

Pokud se vám na něm něco nezdá, můžete zadání
rozporovat.

Jestli jsi to nepochopil, tak právě v těch případech bylo to rozporování implicitně obsaženo: upozorňovali jsme Tě na případ, který Tvé zadání neumí pochytit, ač je potřeba. Jen jsme prostě z Tebe nechtěli hned dělat blba a mysleli jsme, že tu chybu svého zadání pochopíš sám a nebudem Ti ji muset otlouct o hlavu - to je jaksi součást normálního vstřícného postoje v diskusi.

A co jsi se vyjádřil, že tu koninu myslíš vážně, že to nebylo jen nepřesné vyjádření, jak jsme předpokládali, ale že chybu toho zadání nevidíš, tak jsme Ti ji rozporovat začali i explicitně. Tak kde máš problém? Že jsme Ti nedali formální změnový požadavek vyhotovený na formuláři XB-457 ve třech kopiích a opatřených kolkem? Jsme v diskusi, ne v korporátu.

ale navrhnete
řešení, které je v rozporu se zadáním, je to vaše chyba.

Pokolikáté budu muset zopakovat, že ŘEŠENÍ NEBYLO V ROZPORU S TVÝM ZADÁNÍM, do navržené datové struktury šlo uložit i data požadovaná dle tvého chybného zadání. Ovšem naše řešení UMOŽŇOVALO snadnou opravu toho zadání. A zároveň Tě jemně upozorňovalo na chybu zadání.
Uvědom si, k ČEMU MĚL TEN TVŮJ PŘÍKLAD SLOUŽIT: K DEMONSTRACI BEST PRACTICIES při návrhu databáze. Tedy jsou dvě možnosti. Buďto na svém zadání trváš, pak ale je to totální OT, protože demonstrovat best practicies na chybném zadání je nonsens. Anebo chceš diskutovat k tématu - pak by mělo být automatické, že se zadání opraví tak, aby vyhovovalo běžné situaci v praxi.
A i když budeš na tom chybném návrhu trvat - i to se občas u klienta nebo třeba šéfa v práci stane - i tak by v praxi bylo Tvé řešení chybné, protože dřív či pozdějc by se na omezení toho návrhu narazilo tak jako tak. To je jedna z dalších věcí, která dělá dobrého programátora: když už šéf/klient neuhne a chce blbost, tak SW navrhnout aspoň tak, aby ta blbost vadila co nejméně. A ne to naimplementovat pitomně a umejt si ruce, že to bylo v zadání.....

[Filip Jirsák]

Na konkrétních příkladech - dokonce i Tvých - jsem ukázal, že takové zadání v praxi nebude fungovat dobře. Tyto příklady jsi nijak nevyvrátil. Budeme diskutovat způsobem, že kdo víckrát zařve svoji pravdu, tak vyhrál? Nebo budeme argumentovat?

Ukázal jste, že jsou možná i jiná řešení. Že nebude fungovat můj příklad jste neukázal. Pokud je např. jediná funkce vedoucího, že schvaluje dovolenou, bude můj příklad fungovat perfektně.

Jestli jsi to nepochopil, tak právě v těch případech bylo to rozporování implicitně obsaženo: upozorňovali jsme Tě na případ, který Tvé zadání neumí pochytit, ač je potřeba.

Já jsem reagoval především na Miroslava Šilhavého, který zadání prostě ignoroval a řešil něco, co bylo v rozporu se zadáním. To, že vy jste si vymyslel svůj vlastní příklad, který není kompatibilní s mým zadáním, je váš problém – o zadání neříká vůbec nic. Když já potřebuji odvézt tři lidi autem a vy potřebujete odvézt metrák uhlí, nedokážete nefunkčnost mého řešení tím, že je nefunkční pro vás.

že chybu toho zadání nevidíš

Zatím nikdo chybu v zadání neukázal. Akorát jste ukázal jiná zadání.

Pokolikáté budu muset zopakovat, že ŘEŠENÍ NEBYLO V ROZPORU S TVÝM ZADÁNÍM, do navržené datové struktury šlo uložit i data požadovaná dle tvého chybného zadání.

Opakovat to můžete kolikrát chcete, ale pravda to není. Řeč není o datové struktuře. Řeč je o zadání „vedoucí je definován tím, že má podřízené“ a reakcí „co když vedoucí nemá podřízené“. Kolikrát ještě budu muset zopakovat, že není možné, aby vedoucí zároveň měl i neměl podřízené?

Uvědom si, k ČEMU MĚL TEN TVŮJ PŘÍKLAD SLOUŽIT: K DEMONSTRACI BEST PRACTICIES při návrhu databáze.

Nikoli. Příklad měl jen ukázat, že struktura databáze neurčuje, jak se budou v dotazech spojovat tabulky. Nad stejnou strukturou tabulek můžete dělat INNER i OUTER JOIN – výstup pak má jiný význam.

tak by v praxi bylo Tvé řešení chybné, protože dřív či pozdějc by se na omezení toho návrhu narazilo tak jako tak. To je jedna z dalších věcí, která dělá dobrého programátora: když už šéf/klient neuhne a chce blbost, tak SW navrhnout aspoň tak, aby ta blbost vadila co nejméně.

Tohle je častá chyba začínajících programátorů – implementují bez znalosti problematiky jakési obecné řešení, jehož výhoda se má ukázat někdy v budoucnosti. Jenže v budoucnosti se ukáže, že potřeby uživatele jsou úplně jiné, program na potřebné změny připravený není, zato je velmi komplikuje to „obecné řešení“.

Vždyť to bylo vidět i na zdejší diskusi. V zadání bylo, že vedoucí je definován tím, že má podřízené. Zadavatel na to může spoléhat – že mu aplikace ohlídá, že nemá vedoucího bez podřízených. Jenže pak jste společnými silami přišli s tím, že to má být v databázi uložené jako příznak, takže je nutná explicitní kontrola. Jenže pomocí cizích klíčů to nejde, pomocí triggerů to neumíte a v aplikaci to kontrolovat nechcete. Takže tam tu kontrolu mít nebudete. Takže jste se přes „vylepšování pro budoucí použití“, několik hloupých pravidel a neznalost dostali k tomu, že vaše aplikace je sice perfektně připravená na budoucí změny, akorát že nesplňuje současné zadání, protože umožní mít vedoucího, který nemá podřízené.

A je úplně jedno, že se vám nelíbí use-case „vedoucí je definován tím, že má podřízené“ – stejné vztahy mohou existovat v jiných případech, třeba „štítek je definován tím, že existují objekty, které mají daný štítek přiřazen“.

[Miroslav Šilhavý]

Já jsem reagoval především na Miroslava Šilhavého, který zadání prostě ignoroval a řešil něco, co bylo v rozporu se zadáním. To, že vy jste si vymyslel svůj vlastní příklad, který není kompatibilní s mým zadáním, je váš problém – o zadání neříká vůbec nic. Když já potřebuji odvézt tři lidi autem a vy potřebujete odvézt metrák uhlí, nedokážete nefunkčnost mého řešení tím, že je nefunkční pro vás.

Ale to je úplně ten samý případ. Racchek jasně definoval, že v návrhu má i neexistence záznamu v tbl2 svůj význam.

Dále pak specifikoval jeden konkrétní dotaz, ve kterém nemají význam řádky bez existence záznamu v tbl2.

Je však zřejmé, pokud pracuje s premisou, že v tbl2 záznam existovat může a nemusí, že v jiných dotazech bude nutně pracovat i s touto situací. V tu chvíli je to na OUTER JOIN situace jak vyšitá - spojení tabulek vyjadřuje strukturou jejich význam (sémantiku), pomocí WHERE pak definujete jen to, co Vás zrovna zajímá.

Racchekovi jste poradil přemýšlení klasické programátorské lopaty, ale bylo zcela na místě ho naučit přemýšlet v kontextu.

[Logik]

Že nebude fungovat můj příklad jste neukázal. Pokud je např. jediná funkce vedoucího, že schvaluje dovolenou, bude můj příklad fungovat perfektně.

Ukázal jsem, že Tvůj příklad v situacích, které v praxi vcelku běžně nastávají, nebude fungovat: např. dojde k tomu, že se vedoucím pracovníků nedostanou potřebné informace, nebo že v případě návratu z mateřské po reorganizaci by člověku nezařazenému do firemní struktury neměl kdo dovolenou schválit.
Tvůj argument je špatný: to, že nějaký model funguje v jedné konkrétní situaci není důkaz toho, že je dobrý. Dobrý model funguje ve všech situacích, které s nějakou nemizivou pravděpodobností mohou nastat.

Když já potřebuji odvézt tři lidi autem a vy potřebujete odvézt metrák uhlí,

Chybný příměr. Software není věc na jedno použití. Správný příměr by byl: "Když já potřebuju (mám v zadání) odvážet lidi autem a vy TVRDÍTE, ŽE BUDU NĚKDY POTŘEBOVAT ODVÉZT I UHLÍ". A to je naprosto legitimní námitka, která podle toho, o koho jde, může či nemusí být správná. A moji arugmentaci, že česká legislativa i potřeby firemní praxe jsou takové, že si v podstatě odvážení uhlí vynucují, jste nijak nevyvrátil.

Tohle je častá chyba začínajících programátorů – implementují bez znalosti problematiky jakési obecné řešení, jehož výhoda se má ukázat někdy v budoucnosti.

Ano, to je častá chyba. Stejně jako je chyba začínajících programátorů, že slepě dodržují zadání, i když jsou v něm zjevné chyby. Zkušený programátor posoudí zadání a vyhodnotí dle své znalosti dané problematiky, kde je na místě ho nezobecňovat a kde je třeba ho zobecnit, popř. změnit (což dle procesů ve firmě někdy i může udělat sám, jindy to znamená složitý proces.)

A přesně tak zněla má argumentace: na příkladech z praxe jsem ukázal, proč dané zadání nebude dostačovat a tedy proč v tomto případě ho zobecnit na místě je. Ty jsi se to nejprve snažil vyvrátit - a ukázalo se, že o příslušné legislativě a z ní plynoucích potřebách pro praxi příliš nevíš (viz Tvůj vedoucí schvalující si sám dovolenou, nebo vůbec definice vedoucího odporující zákonu), a pak jsi toho radši nechal a vrátil ses k svému stanovisku: "toto je moje zadání a kdo ho slepě nedodrží anebo kdo ho implementuje obecněji, tak to dělá špatně.
Tak nevím, na koho to "implementuje bez znalosti problematiky" sedí spíš.... :-)

Nikoli. Příklad měl jen ukázat, že struktura databáze neurčuje, jak se budou v dotazech spojovat tabulky.

Napsal jsi: " Např. pokud je vedoucí zaměstnanec definován tím, že má podřízené, nemá smysl vytvářet si pohled s vedoucími..."Tedy jsi stanovoval "jak se to má dělat". Stejnětak jako Miroslav hájil outer join, tedy jak se to má dělat. Samozřejmě, že tím nemyslel, že se má 100% joinů dělat OUTER. Ale že to má smysl v běžných situacích. Tedy Miroslav stanovoval best practice.
Ty tvrdíš, že jeho best practice je špatná. Tedy říkáš, že best practice je jiná. Tedy ji stanovuješ - a vyvracíš ji příkladem, který je mimo praxi...

...pomocí triggerů to neumíte...

Ten, kdo se vyhýbá triggerům a bojí se databázovýho programování a radši tu logiku buďto nacpe až do aplikační vrstvy (čímž vznikne problematická situace, kdy různé constrainy jsou vynucované na různé úrovni aplikace), nebo ji v DB vynutí primitivními a neflexibilními mechanismy (přímo strukturou DB) aby se vyhnul triggerům, jsi jaksi Ty. Tak fakt nechápu, proč z toho obviňuješ nás....
Ano, když se člověk vyhýbá nástrojům, které je třeba použít k správnému návrhu databáze, tak tu databázi zkriplí.

stejné vztahy mohou existovat v jiných případech, třeba „štítek je definován tím, že existují objekty, které mají daný štítek přiřazen“.

Jo, klasika. Tak přesně vznikají ty databáze, kde je hromada šťítků typu: "Novinka", "Novinky", "novinky", "novikny", "novynky", "novikni"..... Kdybych byl vznětlivější typ, tak bych měl chuť zpřerážet hnáty člověku, co tu databázi takhle blbě navrhnul. Todle je i proti základním principům db programování (denormalizovaná struktura).