abclinuxu.cz AbcLinuxu.cz itbiz.cz ITBiz.cz HDmag.cz HDmag.cz abcprace.cz AbcPráce.cz
AbcLinuxu hledá autory!
Inzerujte na AbcPráce.cz od 950 Kč
Rozšířené hledání
×
eParkomat, startup z ČR, postoupil mezi finalisty evropského akcelerátoru ChallengeUp!
Robot na pivo mu otevřel dveře k opravdovému byznysu
Internet věcí: Propojený svět? Už se to blíží...
včera 17:02 | Pozvánky

Přijďte si popovídat o open source obecně a openSUSE konkrétně s dalšími uživateli a vývojáři. Oslava nového vydání openSUSE Leap se uskuteční 16. prosince od 17:00 v nových prostorách firmy SUSE v Praze. K dispozici bude nějaké občerstvení a DVD pro ty, kdo je sbírají nebo ještě mají mechaniku. Po párty v kanceláři se bude pokračovat v některé z hospod v okolí.

Miška | Komentářů: 8
včera 14:55 | Zajímavý software

Byla vydána verze Alpha 1.0 otevřeného operačního systému pro chytré hodinky AsteroidOS. Podporovány jsou hodinky LG G Watch, LG G Watch Urbane, Asus ZenWatch 2 a Sony Smartwatch 3. Ukázka ovládání hodinek na YouTube. Jaroslav Řezník přednášel o AsteroidOS na chytrých hodinkách (videozáznam) na letošní konferenci OpenAlt.

Ladislav Hagara | Komentářů: 0
včera 13:30 | Zajímavý software

Byly uvolněny zdrojové kódy známé rogue-like hry DoomRL. Počátky hry jsou v roce 2002. Je napsána ve FreePascalu a zdrojový kód je nyní k dispozici na GitHubu pod licencí GNU GPL 2.0. Autor pracuje na nové hře Jupiter Hell, která je moderním nástupcem DoomRL a na jejíž vývoj shání peníze prostřednictvím Kickstarteru.

Blaazen | Komentářů: 0
včera 13:15 | Pozvánky

Přijďte s námi oslavit vydání Fedory 25. Na programu budou přednášky o novinkách, diskuse, neřízený networking atd. Release Party se bude konat 16. prosince v prostorách společnosti Etnetera. Na party budou volně k dispozici také propagační materiály, nová DVD s Fedorou 25 a samozřejmě občerstvení. Přednášky budou probíhat v češtině. Pro více informací se můžete podívat na web MojeFedora.cz. Jen připomínám, že tentokrát jsme zavedli

… více »
frantisekz | Komentářů: 0
9.12. 16:38 | Komunita

Byly zveřejněny videozáznamy přednášek a workshopů z letošní konference OpenAlt konané 5. a 6. listopadu v Brně. K videozáznamům lze přistupovat ze stránky na SuperLectures nebo přes program konference, detaily o vybrané přednášce nebo workshopu a dále kliknutím na ikonku filmového pásu. Celkově bylo zpracováno 65 hodin z 89 přednášek a workshopů.

Ladislav Hagara | Komentářů: 0
9.12. 11:30 | Komunita

Bylo oznámeno, že bude proveden bezpečnostní audit zdrojových kódů open source softwaru pro implementaci virtuálních privátních sítí OpenVPN. Audit provede Matthew D. Green (blog), uznávaný kryptolog a profesor na Univerzitě Johnse Hopkinse. Auditována bude verze 2.4 (aktuálně RC 1, stabilní verze je 2.3.14). Audit bude financován společností Private Internet Access [reddit].

Ladislav Hagara | Komentářů: 4
9.12. 06:00 | Komunita

Na YouTube byl publikován Blender Institute Reel 2016, ani ne dvouminutový sestřih z filmů, které vznikly za posledních 10 let díky Blender Institutu. V institutu aktuálně pracují na novém filmu Agent 327. Dění kolem filmu lze sledovat na Blender Cloudu. Videoukázka Agenta 327 z června letošního roku na YouTube.

Ladislav Hagara | Komentářů: 0
9.12. 01:02 | Zajímavý článek

Minulý týden byly vydány verze 1.2.3 a 1.1.7 webového poštovního klienta Roundcube. V oznámení o vydání bylo zmíněno řešení bezpečnostního problému nalezeného společností RIPS a souvisejícího s voláním funkce mail() v PHP. Tento týden byly zveřejněny podrobnosti. Útočník mohl pomocí speciálně připraveného emailu spustit na serveru libovolný příkaz. Stejně, jak je popsáno v článku Exploit PHP’s mail() to get remote code execution z roku 2014.

Ladislav Hagara | Komentářů: 1
8.12. 16:00 | Nová verze

Byla vydána verze 0.98 svobodného nelineárního video editoru Pitivi. Z novinek lze zmínit například přizpůsobitelné klávesové zkratky. Videoukázka práce s nejnovější verzí Pitivi na YouTube.

Ladislav Hagara | Komentářů: 1
8.12. 15:00 | Zajímavý software

Stop motion je technika animace, při níž je reálný objekt mezi jednotlivými snímky ručně upravován a posouván o malé úseky, tak aby po spojení vyvolala animace dojem spojitosti. Jaký software lze pro stop motion použít na Linuxu? Článek na OMG! Ubuntu! představuje Heron Animation. Ten bohužel podporuje pouze webové kamery. Podpora digitálních zrcadlovek je začleněna například v programu qStopMotion.

Ladislav Hagara | Komentářů: 5
Kolik máte dat ve svém domovském adresáři na svém primárním osobním počítači?
 (32%)
 (23%)
 (29%)
 (7%)
 (5%)
 (3%)
Celkem 810 hlasů
 Komentářů: 50, poslední 29.11. 15:50
Rozcestník
Reklama

Dotaz: Makro-preprocesor pro SQL?

xkucf03 avatar 4.1. 21:42 xkucf03 | skóre: 45 | blog: xkucf03
Makro-preprocesor pro SQL?
Přečteno: 1349×

Jak řešíte situace, kdy máte v databázi číselníkové tabulky (obsahující číselné ID, jedinečný textový kód a případně další atributy), další tabulky, které se na ně odkazují přes ta číselná ID a chcete nad tím psát SELECTy (neprocedurální, čisté SQL), ve kterých potřebujete hodnoty z těch číselníků?

Jsem zvyklý dělat JOIN s číselníkovou tabulkou a v SELECTu používat ty textové kódy. Dávat tam magické konstanty v podobě těch číselných ID považuji za prasárnu a předčasnou optimalizaci – bude se to špatně číst a bude to příliš křehké – takové programy mají tendenci se rozbíjet a chovat se divně, nepředvídatelně.

Pokud byste přeci jen došli k tomu, že JOIN je nepřijatelně pomalý, jak byste to řešili jinak?

Konstanty asi na většině systémů v SQL (neprocedurálním) použít nepůjdou. Dá se udělat funkce (IMMUTABLE), která vrací číselné ID, a v rámci SELECTu ji zavolat.

Taky by k těm magickým konstantám šlo psát komentáře. To by trochu pomohlo, ale ne moc – program by byl stále moc křehký a nespolehlivý.

Kdyby ty komentáře měly nějakou pevnou strukturu a byly strojově čitelné, šlo by si k tomu napsat skripty, které by zkontrolovaly, že jsou tam správné magické konstanty, případně by aspoň usnadnily nalezení míst, kde je potřeba hodnotu změnit. Příklad:

SELECT *
FROM tabulka
WHERE stav = 384 -- xxx:tabulka_stavů:kód_stavu

Napadlo mě, že by na to šlo jít i opačně a SQL skript před provedením prohnat nějakým preprocesorem (buď v době kompilace nebo při prvním načtení v době běhu), což by umožňovalo v něm používat konstanty případně i další makra.

Zkusil jsem si, jak to vypadá v m4 a cpp.

CPP – soubor s makry:

#define KONSTANTA_1 1337

CPP – dotaz:

#include "makra.sql"

SELECT *
FROM tabulka
WHERE
  stav = KONSTANTA_1 -- tohle chceme nahradit
  AND popis <> 'KONSTANTA_1' -- tohle se nemá nahradit
  AND popis NOT LIKE '%KONSTANTA_1%' -- dtto

CPP – výsledek:

# 1 "dotaz.sql"
# 1 "<built-in>"
# 1 "<command-line>"
# 1 "/usr/include/stdc-predef.h" 1 3 4
# 1 "<command-line>" 2
# 1 "dotaz.sql"
# 1 "makra.sql" 1
# 2 "dotaz.sql" 2

SELECT *
FROM tabulka
WHERE
  stav = 1337
  AND popis <> 'KONSTANTA_1'
  AND popis NOT LIKE '%KONSTANTA_1%'

M4 – soubor s makry:

define(KONSTANTA_1, 1337)

M4 – dotaz:

include(`makra.m4')

SELECT *
FROM tabulka
WHERE
  stav = KONSTANTA_1 -- tohle chceme nahradit
  AND popis <> '`KONSTANTA_1'' -- tohle se nemá nahradit
  AND popis NOT LIKE '%`KONSTANTA_1'%' - dtto

M4 – výstup:

SELECT *
FROM tabulka
WHERE
  stav = 1337
  AND popis <> 'KONSTANTA_1'
  AND popis NOT LIKE '%KONSTANTA_1%'

Ale není to úplně ono. CPP je určené pro jazyk C a tady funguje spíš náhodou. A hlavně by to chtělo nějaká neinvazivní makra, která nenarušují syntaxi SQL – aby fungovalo zvýraznění syntaxe a případně další nástroje i nad zdrojovým souborem.

Použili byste CPP nebo M4? Případně jak? Nebo jiný preprocesor? Nebo ty funkce? Nebo byste zůstali u JOINů? (mnou preferovaná varianta – i když ty preprocesory mne trochu lákají, protože to skýtá i jiné možnosti…)

Řešili byste tuhle otázku jinak v různých DBMS? (PostgreSQL, MariaDB/MySQL, Oracle)

Mám rád, když se lidé přou, znamená to, že vědí, co dělají, a že mají směr. Frantovo.cz, SQL-Výuka.cz, Nekuřák.net

Odpovědi

4.1. 22:10 Kit | skóre: 37 | Brno
Rozbalit Rozbalit vše Re: Makro-preprocesor pro SQL?
JOIN by měl být dostatečně rychlý. Tohle asi bude výjimka.

V MySQL tohle řeším přes ENUM(). Databáze si ten klíč z dotazu přeloží na konstantu integer a pak hledá rychle. Rovněž výsledky jsou prezentovány slovně. Mně to velmi vyhovuje, protože mi na to snadno navazují výstupní vícejazykové šablony. Jako vstup i výstup lze použít integer, ale tomu se vyhýbám. Problémem může být udržení konzistence při použití jednoho číselníku ve více tabulkách.

Konstanty tedy nedefinuji v aplikaci, ale přímo v databázi. Snese to i modifikaci typu ENUM(), vše se vnitřně automaticky přečísluje - z praktických důvodu je však lepší to dělat jen výjimečně.

PostgreSQL na to umožňuje definici vlastního datového typu. Jedná se de facto o další tabulku a implicitní JOIN.
Komentáře označují místa, kde programátor udělal chybu nebo něco nedodělal.
6.1. 17:35 OldFrog {Ondra Nemecek} | skóre: 25 | blog: Žabákův notes | Praha
Rozbalit Rozbalit vše Re: Makro-preprocesor pro SQL?
Mě zaujala ta varianta funkcí. Pokud je fce IMMUTABLE tak se snad pro daný parametr i cachuje hodnota, ne? Takže by to mohlo být dost rychlé. A pohodlnější+čitelnější než subselect (který osobně používám já). View se budou asi hodit, pokud se ty stejné dotazy pořád opakují. Pak lze celý join nebo dotaz včetně subseletů elegantně schovat pod kapotu. Ale pro jednorázovky si to nedokážu představit (možná mám malou představivost).
-- OldFrog
6.1. 17:36 OldFrog {Ondra Nemecek} | skóre: 25 | blog: Žabákův notes | Praha
Rozbalit Rozbalit vše Re: Makro-preprocesor pro SQL?
Eee, chtěl jsem reagovat na původní příspěvek, ne na Kita.
-- OldFrog
rADOn avatar 6.1. 18:06 rADOn | skóre: 44 | blog: bloK | Praha
Rozbalit Rozbalit vše Re: Makro-preprocesor pro SQL?
Enum ma dalsi vyhodu ze se nemusi delat cizi klic, takze si usetrim potencialne zbytecny index. A taky celkem podstatnou nevyhodu v tom ze kazda zmena znamena udelat alter coz na velkych tabulich muze byt docela problem.
"2^24 comments ought to be enough for anyone" -- CmdrTaco
6.1. 23:32 Kit | skóre: 37 | Brno
Rozbalit Rozbalit vše Re: Makro-preprocesor pro SQL?
Není náhodou modifikace toho ENUM stejný výkonnostní problém jako modifikace číselníku připojovaného přes JOIN? Zkusil jsem přidat položku do ENUM v tabulce se čtvrt miliónem záznamů a změna byla okamžitá (0,11 s)

Horší by bylo, kdybych nějakou položku vyřadil nebo přehodil (32,56 s). Ovšem s číselníkem by to lepší určitě nebylo.

Takže pokud se udělá alter na ENUM tak, že se pouze přidá položka na konec, ani na obrovských tabulích to problém není.
Komentáře označují místa, kde programátor udělal chybu nebo něco nedodělal.
okbob avatar 5.1. 10:39 okbob | skóre: 30 | blog: systemakuv_blog | Benešov
Rozbalit Rozbalit vše Re: Makro-preprocesor pro SQL?
Makrem v SQL jsou pohledy. Číselník, který se vejde do paměti, je díky hashjoinu v podstatě zadarmo. Jedinou nevýhodou může být zhoršení odhadů, protože se nepracuje se uloženou statistikou (alespoň v Postgresu) a používá se odhad procentem - což pokud jsou některé položky příliš dominantní může působit problémy v komplexních dotazech. V jednodušších dotazech a s normálními daty se většinou nic špatného neděje.
Josef Kufner avatar 6.1. 02:18 Josef Kufner | skóre: 66
Rozbalit Rozbalit vše Re: Makro-preprocesor pro SQL?
Podívej se na EXPLAIN, ten ti řekne, zda to zdržuje nebo ne. Já bych tam dal subselect, který by vrátil to správné ID podle hodnoty v číselníku. Je menší šance, že se tím rozdrbou nějaké další joiny. Navíc subselect se dá bezpečně dosadit i tím preprocesorem, nebo lépe query builderem.

Pro složitější SQL dotazy používám query builder. Pokud bych chtěl takto pracovat s číselníky, rozšířil bych si ho o metodu, která by přidala odpovídající subselect. Takže by to mohlo vypadat takto:
$result = $flupdo->select('*')->from('tabulka')
    ->whereZCiselniku('stav =', 'KONSTANTA_1')
    ->where('popis <> ?', 'KONSTANTA_1')
    ->where('popis NOT LIKE CONCAT("%", ?, "%")', 'KONSTANTA_1');
Výsledný SQL dotaz by pak byl:
SELECT *
FROM tabulka
WHERE
  stav = (SELECT id FROM cislenik WHERE popis = ? LIMIT 1)
  AND popis <> ?
  AND popis NOT LIKE CONCAT("%", ?, "%")
(A samozřejmě také odpovídající pole s parametry.)
Hello world ! Segmentation fault (core dumped)
6.1. 23:11 Tomáš
Rozbalit Rozbalit vše Re: Makro-preprocesor pro SQL?

Na tyto věci používám program sed ( případně Perl nebo Ruby jako stream filter přepínač -p). Zejména operátor s///. Používal jsem to jako mockovací systém pro SQL, kdy jsem potřeboval nahradit jméno tabulky jinou - mockem. S trochou formátovací kultury se s tím dala napsat pravidla i pro kontextové nahrazení. Například čtení bude z tabulky původní, ale zápis jde do jiné tabulky (syntaxe regexp ala perl pro zvýšení čitelnosti):

s/my_table/for_writing/g
s/((from)|(join)\s+)for_writing/my_table/g

Předpokadem byla formátovací kultura používat explicitně join klíčové slovo a nepoužívat join skrytý v klausuli where. Je potřeba tomu preprocesingu trošku pomoci. Kompletní generické parsování SQL by nebyl vůbec triviální úkol. A rozhodně bych se nesnažil o validaci SQL vlastním kódem.

Otázka zda zabránit substituci v řetězcích je spíše otázkou: "Co když takovou substituci budete chtít?" Pokud si matně pamatuji, řešil jsem problém potlačení substituce explicitně:

where X.x = 'my_table' -- @no_substitute@ opravdu zde nechci substituci
/@no_substitute@/ b # přeskočí řádky označené @no_substitute@

Pokud budete chtít zamezit kolizi vyberte si dostatečně divoké jméno proměnné tak aby nekolidovala. Např:

stav = @@KONSTANTA_1@@

Tak divoký řetězec snad nikde mít nikdy nebudete. Ale pamatuji si že se mi substituce v řetězcích hodila. Myslím, že to bylo nějaké SQL s tabulkovými metadaty.

Pokud má být transparentní nahrazení hodnotou z číselníku tak pro Oracle by mohlo zafungovat něco takového:

s/@(\w+).(\w+)/(select ID from $1 where txt_id='$2')/g  # @ = označení substituce

V perlu/ruby by se dalo i případně zkontrolovat zda dotyčná tabulka a záznam existuje a že se do skriptu neinjektujeme nesmyslný kód.

Založit nové vláknoNahoru

Tiskni Sdílej: Linkuj Jaggni to Vybrali.sme.sk Google Del.icio.us Facebook

ISSN 1214-1267   www.czech-server.cz
© 1999-2015 Nitemedia s. r. o. Všechna práva vyhrazena.