Fórum Root.cz

Hlavní témata => Vývoj => Téma založeno: Vláďa J 12. 12. 2011, 17:03:20

Název: Pomoc se strukturou databáze
Přispěvatel: Vláďa J 12. 12. 2011, 17:03:20
Ahoj,

Vyvíjím v PySide/QML multimediální přehrávač. Zatím zvládá pouze hudbu, ale do budoucna počítám i s videem. To ale není podstatné.

Jeden z hlavních důvodů, proč jsem se rozhodl vytvořit vlastní přehrávač je to, že jsem potřeboval takový, který půjde ovládat prstem na dotykovém displeji a bude mít dobrou správu knihovny (dynamické playlisty, třídění podle žánru, umělce, alba atd.) Žádný takový přehrávač není (nebo jsem na něj zatím nenarazil), tak jsem si napsal svůj vlastní.

Jeden z problémů, který řeším, a jako naprostý laik v této oblasti neumím posoudit, je struktura databáze pro knihovnu. Momentálně mám pouze jednu databázi, ve které je na každém řádku asi 20 údajů ke každé písničce. V přehrávači potom s každou písničkou pracuji jako s objektem můžu se odkazovat na tyto vlastnosti. Takhle to funguje a je to i relativně rychlé.

Problém nastává, jako když si třeba teď vzpomenu, že u každé písničky chci mít nově přístupný nějaký nový údaj z tagů (teď je to BPM, příště to bude třeba počet přehrání). Jak tohle efektivně vyřešit? V mém případě to znamená přepsat kód na několika místech, smazat databázi a vytvořit novou s jiným počtem řádků. Samozřejmě po prvním spuštění na mě vypadne spousta chyb, protože aktuální databáze bude mít jiný počet řádků.

Jaké je správné a efektivní řešení mého problému? Fascinuje mě rychlost a funkčnost přehrávače foobar2000. Chtěl bych dosáhnout něčeho podobného. V podstatě celý kód, který pracuje s daty a databází je napsaný v Pythonu 2.x s SQLite pluginem, v tom by snad problém být neměl, doufám. Qt/QML je použité pouze pro uživatelské rozhraní.

Práce s databází (například vyfiltrování všech skladeb jednoho žánru) musí být dostatečně rychlá (do 0,1s). Navíc přehrávač používám na UMPC s Atomem, do budoucna ho možná portuju i na telefony s Androidem (až bude PySide pro Android) a další zařízení s podporou Pythonu a Qt. Každopádně mi na výkonu poměrně dost záleží a bojím se, že pokud udělám tabulku třeba se 100 sloupci, bude to mít negativní vliv na rychlost.

Předem díky za rady.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Ivan 12. 12. 2011, 17:17:05
Na tohle mozna ani nepotrebujes SQL databazi. Koukni se na Berkeley DB. Pokud ti jde o rychlost a nenarocnost tak tohle je velice dobre reseni.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Kit 12. 12. 2011, 17:59:33
Myslím si, že SQLite se pro daný účel hodí velmi dobře. 100 polí je docela dost, možná by bylo vhodné databázi normalizovat. Není to však nezbytné. Mohlo by se totiž stát, že po důkladné normalizaci by se databáze výrazně zpomalila a bylo by nutné ji denormalizovat. Ovšem kdyby výsledkem normalizace byly číselníky interpretů, žánrů, alb, apod., mohlo by to hledání i zrychlit.

SQLite umožňuje i "ALTER TABLE". Tabulky tedy mazat nemusíš, stačí doplnit sloupec. Pokud děláš SELECTy s výčtem sloupců, problémy by nastat neměly.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Pavouk106 12. 12. 2011, 18:48:06
Já se předem omlouvám, pokud sem teď napíšu blbost...

Zkus si do knihovny nasypat hromady songů (tisíce až desetitisíce) a pak zkus listování (výběr podle např, žánru). Odhaduju, že pak bude cítit pomalost SQLite. Ale fakt to jen odhaduju, bez praktickýho testu je to k ničemu. Podložené to nijak nemám...
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Pavouk106 12. 12. 2011, 18:49:50
A teď se omlouvám za double-post :-) Chtěl jsem jen dodat, že smekám. Sám bych chtěl taky umět napsat si právě třeba přehrávač, nebo klidně jinou aplikaci, ale nemám základy ani odhodlání. Tak hodně štěstí s vlastním přehrávačem ;-)
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Kit 12. 12. 2011, 19:18:24
Zkus si do knihovny nasypat hromady songů (tisíce až desetitisíce) a pak zkus listování (výběr podle např, žánru). Odhaduju, že pak bude cítit pomalost SQLite.
SQLite patří mezi velmi rychlé databáze. Testoval jsem SELECT, který vypsal 300000 záznamů o délce 80 znaků jako jeden řetězec. Na mém Atomu to trvalo jen necelé 2 sekundy.

Při výběru podle žánru se dá s výhodou využít fulltextové vyhledávání, které je na SQLite velmi rychlé.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Logik 12. 12. 2011, 20:29:58
IMHO správná struktura tady je písnička + tabulka tagů písnička:název:hodnota, připojená k databázi písniček 1:n. Otázka je, jestli ještě neudělat tabulku interpretů a alb )podle funkčnosti, kteoru požaduješ).
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Kit 12. 12. 2011, 20:58:07
IMHO správná struktura tady je písnička + tabulka tagů písnička:název:hodnota, připojená k databázi písniček 1:n. Otázka je, jestli ještě neudělat tabulku interpretů a alb )podle funkčnosti, kteoru požaduješ).
To už můžeš rovnou udělat tabulku id:attr->value a nad value udělat fulltext. Vystačíš si s jednou třísloupcovou tabulkou na celou aplikaci.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Logik 12. 12. 2011, 21:45:00
Jo, a jak jednoduše nad toudle tabulkou např. spočítáš počet písniček? Popř. Prostá key-value tabulka je jeden extrém, plochá tabulka s řádkem pro jednu písničku je druhej extrém. Extrémy zpravidla nejsou vhodným řešením, většinou je nejlepší zlatá střední cesta.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Kit 12. 12. 2011, 22:25:55
Jo, a jak jednoduše nad toudle tabulkou např. spočítáš počet písniček?
Spočítáním unikátních ID?
Popř. Prostá key-value tabulka je jeden extrém, plochá tabulka s řádkem pro jednu písničku je druhej extrém. Extrémy zpravidla nejsou vhodným řešením, většinou je nejlepší zlatá střední cesta.
Já si s EAV nezačal :-)

Těch řešení je spousta, dalo by se použít XML, JSON, CSV, YAML,... Dokonce se dá využít i holý filesystém. Každé řešení je dobré na něco jiného. V tuto chvíli vůbec netušíme, jaký objem písniček vlastně tazatel potřebuje řešit. Dokonce i správných řešení je velmi mnoho.

Naše vnitřní potřeba normalizace říká, že 100 sloupců v tabulce není v pořádku. Je to ale nejjednodušší funkční řešení, které je dokonce i rychlé. Samozřejmě má své vady, jako např. že se smazáním písničky zmizí i interpret. Vadí to koncovému uživateli? Spoustě z nich by mohlo vadit, že tam toho zpěváka ještě mají, i když smazali všechny jeho písničky.

BTW: Udělal jsem si lehký test SQLite na Atomu. 256K záznamů, každý 30 sloupců. Každý záznam cca 1 KB, soubor má 300 MB. Vyhledávání přes LIKE '%word%' cca 1.2 sekundy. Ještě má někdo pocit, že je to pomalá databáze? A to jsem nepoužil fulltext, který by se na tuto aplikaci hodil.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: nou 12. 12. 2011, 23:10:40
BTW: Udělal jsem si lehký test SQLite na Atomu. 256K záznamů, každý 30 sloupců. Každý záznam cca 1 KB, soubor má 300 MB. Vyhledávání přes LIKE '%word%' cca 1.2 sekundy. Ještě má někdo pocit, že je to pomalá databáze? A to jsem nepoužil fulltext, který by se na tuto aplikaci hodil.
no este by sa zisiel podobny test s MySQL nech to mame s cim porovnat.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Pavouk106 13. 12. 2011, 11:33:38
BTW: Udělal jsem si lehký test SQLite na Atomu. 256K záznamů, každý 30 sloupců. Každý záznam cca 1 KB, soubor má 300 MB. Vyhledávání přes LIKE '%word%' cca 1.2 sekundy. Ještě má někdo pocit, že je to pomalá databáze? A to jsem nepoužil fulltext, který by se na tuto aplikaci hodil.
Proto jsem se hned omlouval, neměl jsem to doteď ničím podložené a odteď už pro změnu vím, že to není tak, jak jsem si já myslel. Díky za konkrétní čísla. Nevadí, že je nemám s čím porovnat, 1,2 sekundy mi jako výsledek bohatě stačí.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Nemo7 13. 12. 2011, 11:48:45
Můžete si také nainstalovat Banshee, přidat do něj pár písniček a prohlédnout si jeho DB strukturu v SQLite. Mají celkem normální návrh. Poučné, proč se namáhat když nemusím. Základní vlastnost programátora je lenost, ne ?  :)
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Vláďa J 13. 12. 2011, 16:31:27
Díky všem za užitečné reakce.

Trošku upřesním, co tedy vlastně potřebuji. Mám kolekci písniček (řádově tisíce, možná i přes 10k). Přehrávač vypadá a funguje tak, že má 5 sloupců. V prvním jsou playlisty (dynamické neboli chytré, i obyčejné) v dalším je žánr, potom autor (nebo autor alba), album a písnička. Dynamický playlist je jakékoliv SQL query. Každý sloupec filtruje ty za sebou. To znamená, že pokud vyberu dynamický playlist, který omezí výběr na písničky s hodnocením vyšším než 3, a v autorech vyberu třeba Beatles, budou už v dalším sloupci pouze alba obsahující písničky od Beatles s hodnocením vyšším než 3.

Podobně funguje například iTunes (aspoň myslím, nepoužívám ho) nebo Songbird.

Zatím to funguje tak, že všechny písničky jsou v tabulce s cca 30 sloupci. Pokud označím nějaký výběr ve sloupci x, aktualizuji všechny sloupce od x+1 do 5. To znamená, že pokud ve 3. sloupci vyberu Beatles, query pro 4. sloupec bude vypadat následovně: SELECT DISTINCT album, album_art FROM Library WHERE rating > 3 AND (album_artist='The Beatles' or artist ='The Beatles').

Pokud přidávám skladby z knihovny do playlistu, použiju stejný query, ale nasypu tam všechny sloupce. Cesta k souboru je v tabulce použitá jako unikátní klíč.

Je tohle rozumné nebo ne? Šlo by to udělat jinak a líp?

Nemo7> Bohužel nevím, jak prostudovat strukturu databáze jiného programu. Jinak bych to udělal u foobaru, což je podle mě nejlepší desktopový přehrávač s velkým náskokem. Banshee je, pokud vím, psaný v .NET a v tom bych se asi dost topil, takže zdroják mi taky nepomůže. :-(

Logik> Mohl bys to trochu rozvést, jak jsem psal, v databázích se moc nevyznám.

Na závěr ještě doplním pár konkrétních problémů, s jejichž řešením se malinko trápím. Například v sloupci interpretů bych chtěl mít i autory alba. Zcela konkrétně mám například v databázi album "Jako kotě si příst" od Jiřího Grossmanna. Na něm je písnička, kterou zpívá Jiří Schelinger. Chtěl bych mít v jednom sloupci jak Jiřího Grossmana tak i Jiří Schelingera. Zároveň by se ale neměly duplikovat. Momentálně to řeším tak, že si vyjedu sloupce "artist" a "album_artist", které potom projedu, promažu a spojím do jednoho, což není zrovna nejrychlejší. Jde to vyřešit nějak elegantněji?

Druhý konkrétní problém je to, že například mám v seznamu album a písničky z něj jsou ve více adresářích (podle interpretů). V každém adresáři je potom obrázek alba, který je ale stejný. Pokud si vyjedu alba příkazem SELECT DISTINCT album, album_art FROM Library WHERE nějaká podmínka, tak položka album_art (cesta k obalu alba) je různá a album tam mám několikrát duplicitně. Dá se nějak udělat (jinak než manuálně promazat), aby se DISTINCT týkal pouze prvního sloupce?

Ještě jednou díky všem za podnětné rady a nápady.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Logik 13. 12. 2011, 17:20:02
No pokud chceš todle, tak Tě to přímo na strukturu vede, ne?
Takže máš žánr, autor, album, písnička, playlist. Pro každou tu entitu si udělám tabulku.
U všech je vazba M:N na písničku.
- Vyhledávání a filtrování pak bude jednoduchej join nad těma tabulkama.
- V sloupcích se bude zobrazovat obsah patřičný tabulky.

A pro vyhledávání podle dalších kritérií (tagů písniček) bych přidal tabulku 1:N k písničkám, kam bych dával další custom tagy

Odlišný budou jen dynamický playlisty, kde místo join nad danou tabulkou budeš mít v apikaci předepsanej patřičnej filtr.


Plochou strukturu můžeš použít defakto taky, ale není to hezký a některý operace (např. vypsání autorů) znamená zbytečný projití celý tabulky, místo daleko menší tabulky seznamu autorů, a u netriviálních operací (např LIKE '%opice%') se nevyhneš sekvenčnímu scanu,  tabulky (sqllite je sice rychlá, ale zbytečně smažit procesor kvůli tomu, že to neumim napsat pořádně...).


Název: Re:Pomoc se strukturou databáze
Přispěvatel: Vláďa J 13. 12. 2011, 18:00:05
Logik> Díky, tohle zní rozumně. Jdu se učit databázové relace. Nemáš někde nějaký odkaz na vhodný studijní materiál?

Co jsem ale zatím tak nastudoval, odpovídala by ale mému případu spíš relace 1:N. Předpokládám, že každá písnička má pouze jednoho autora, jedno album, jeden žánr atd. V praxi to tak být samozřejmě nemusí, ale to asi teď řešit nebudu.

Ještě akorát nevím, zda je rozumné používat jako primární klíč pro písničku cestu k souboru. Není lepší místo toho použít třeba číslo (kvůli rychlosti) nebo to je fuk?

Ještě jednou díky. Jsem v databázích opravdu naprostý laik a říkal jsem si, že to musí jít udělat nějak líp.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Kit 13. 12. 2011, 19:16:12
Co jsem ale zatím tak nastudoval, odpovídala by ale mému případu spíš relace 1:N. Předpokládám, že každá písnička má pouze jednoho autora, jedno album, jeden žánr atd. V praxi to tak být samozřejmě nemusí, ale to asi teď řešit nebudu.
Dá se to obejít, že ve speciálních případech je možné uvést seznam interpretů, třeba "Suchý + Šlitr" a zůstaneš u 1:N.
Ještě akorát nevím, zda je rozumné používat jako primární klíč pro písničku cestu k souboru. Není lepší místo toho použít třeba číslo (kvůli rychlosti) nebo to je fuk?
Primárním klíčem může být klidně i řetězec, ale cesta k souboru se mi nejeví jako ideální primární klíč. U primárního klíče je podstatné, že musí být unikátní. Cesta k souboru to nesplňuje.

Místo vyhledávání přes LIKE je výhodnější použít MATCH, viz http://www.sqlite.org/fts3.html (http://www.sqlite.org/fts3.html), vyhledávání se tím výrazně urychlí a klidně můžeš zůstat u své ploché struktury.

Jenom mi stále není jasné, jak jsi to myslel s těmi 30 sloupci.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Vláďa J 13. 12. 2011, 19:50:24
Kit> Cesta k souboru je unikátní. Každý soubor je jedna písnička. Pokud by šlo o CUE sheet nebo třeba MKA, tak přidám nakonec dvojtečku a číslo stopy. Takže pokud tam není jiný problém, nechal bych to asi tak.

S 30 sloupci to myslím tak. Že zatím používám pouze jednu tabulku, která má jeden sloupec pro každou informaci o písničce (název, autor, album, žánr, hodnocení, počet přehrání, formát, bitrate, čas, BPM atd.)
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Kit 13. 12. 2011, 20:36:12
Kit> Cesta k souboru je unikátní. Každý soubor je jedna písnička. Pokud by šlo o CUE sheet nebo třeba MKA, tak přidám nakonec dvojtečku a číslo stopy. Takže pokud tam není jiný problém, nechal bych to asi tak.
Jedna písnička může být ve více albech a na disku jen 1x, takže zas tak unikátní být nemusí.

S 30 sloupci to myslím tak. Že zatím používám pouze jednu tabulku, která má jeden sloupec pro každou informaci o písničce (název, autor, album, žánr, hodnocení, počet přehrání, formát, bitrate, čas, BPM atd.)
Tomu rozumím, ale nepochopil jsem tohle:
Zatím to funguje tak, že všechny písničky jsou v tabulce s cca 30 sloupci. Pokud označím nějaký výběr ve sloupci x, aktualizuji všechny sloupce od x+1 do 5. To znamená, že pokud ve 3. sloupci vyberu Beatles, query pro 4. sloupec bude vypadat následovně: SELECT DISTINCT album, album_art FROM Library WHERE rating > 3 AND (album_artist='The Beatles' or artist ='The Beatles').
Logik ti radí dobře, ale podle mne jde s kanónem na vrabce. Argumentace výkonem je v daném případě lichá, protože normalizací tak malé databáze se naopak její výkon sníží. Použij FTS3 nebo FTS4, získáš tím mnohem lepší zrychlení než sebelepší normalizací.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Nemo7 13. 12. 2011, 20:37:22
Nemo7> Bohužel nevím, jak prostudovat strukturu databáze jiného programu. Jinak bych to udělal u foobaru, což je podle mě nejlepší desktopový přehrávač s velkým náskokem. Banshee je, pokud vím, psaný v .NET a v tom bych se asi dost topil, takže zdroják mi taky nepomůže. :-(
Stačí si jenom nainstalovat třeba SQLite Database Browser(žádná sláva, ale na prohlédnutí stačí) a otevřít ~/.config/banshee-1/banshee.db a je všechno pěkně vidět.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Logik 13. 12. 2011, 21:18:42
Ad výkon:  normalizací se rychlost rozhodně nesníží. Snížila by se, kdyby primárně byl  požadavek na plochá data, jenže tady naopak když se budou číst velké dávky dat, tak to právě budou data z těch "normalizovaných" tabulek. Např.
  * operace, která se po startu vždy musí provést: dej mi všechny autory (žánry, playlisty...) místo sekvenčn procházení celých 10000 záznamů a vyřazování duplicit prostě vyplivneš obsah tabulky. Dej   
  * vyhledávání dle autora: prohledám místo 10000 záznamů pár stovek záznamů s autory. Join přes pár integerů je pak levná záležitost přes klíč.
Dražší budou (a to ještě možná) operace zahrnující většinu dat, např. vyplyvni všechny písničky a u nich uveď autora. Nebo dej všechny písničky od autorů, který začínaj od Z atd.

Ad 1:n ku M:N. Jo jde to i 1:N. Obejít pomocí Šlitr+Suchý také. Pokud to děláš pro sebe, klidně u toho zůstaň. Pokud máš vážnější plány, dát tam jednu mezitabulkutakovej problém není. Naopak je to spíše jednodušší, protože nejprve zapíšeš písničku "jak je".
Navíc u něčeho (např. playlisty) to M:N stejně potřebuješ IMHO nutně, a vzhledem k tomu, že když to napíšeš chytře, tak Ti bude jedno, jestli zrovna řešíš playlist, autora nebo žánr, takže se to vlastně tím, že to uděláš M:N, zjednoduší.

Ad databáze: no já ani nevím, odkud je umím, ze školy to není. Takže tam moc neporadím. Jen doporučím obecně, neupadnout ani do jednoho extrému: tvrdá teorie (např. relační algebry) pro začátečníka není. Na druhou jen lepení dotazů bez nahlédnutí do toho, jak to db dělá taky nic moc. Takže nějaká zlatá střední cesta. Z teorie bych pro začátek končil někde u normálních forem, s tím, že normalizace je hezká teorie, ale v praxi někdy selhává: ale porušovat pravidla se mají tepr až když jim člověk rozumí.

Ad kanón na vrabce: jak se to vezme. Pokud to postavíš na textu, tak v životě nedovolíš např. vyfiltrovat pouze Beatles od Beatles revival.  Samozřejmě, napsaný to bude rychlejc a pokud chce člověk jen jednoduchý "blbátko" tak to není špatný řešení. Ale když to budeš chtít rozšířit, tak zjistíš, že to napíšeš celé znova.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Logik 13. 12. 2011, 21:21:43
Jo a ještě k primárnímu klíči. Z poměrně dost důvodů je vhodné, aby byl PK umělý, tzn. automaticky vygenerovaný integer. Např. kvůli velikosti dat (8b ku třeba 100b řetězci), možnosti změny záznamu (při přesunu souboru bys musel měnit PK všude, byť to jde automatizovat) atd...
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Kit 13. 12. 2011, 22:38:29
Jo a ještě k primárnímu klíči. Z poměrně dost důvodů je vhodné, aby byl PK umělý, tzn. automaticky vygenerovaný integer. Např. kvůli velikosti dat (8b ku třeba 100b řetězci), možnosti změny záznamu (při přesunu souboru bys musel měnit PK všude, byť to jde automatizovat) atd...
V SQLite je ten umělý integer jako primární klíč vždy. Jmenuje se rowid a je tam i v případech, kdy není v tabulce deklarován. Všechny ostatní klíče jsou de facto vedlejší, tedy i odkaz na soubor. A teď mě určitě budeš kamenovat za používání skrytých proměnných závislých na konkrétní databázi.

A jako vždy: Výkonostní testy mi potvrdily, že u malých databází není rozhodující, zda je primárním klíčem integer či string. I v případě velkých tabulek jsou časové rozdíly tak malé, že ušetření jednoho syntetického sloupce tuto časovou ztrátu nahradí. Pokud tedy někdo jako primární klíč použije třeba řetězec 'RA01234567' jako identifikaci například OP, ušetří se tím jeden sloupec a jeden sekundární index, pokud se bude podle tohoto sloupce vyhledávat. Navíc takový cizí klíč OP:'RA01234567' vypadá v tabulce mnohem lépe než ID_OP:25.

Aby nedošlo k omylu: Umělé PK typu integer používám často, pouze se nesnažím je všude dávat za každou cenu, pokud mám vhodný přirozený klíč s potřebnými vlastnostmi. Například rodné číslo mezi takové PK nepatří, i když se často používá. Normalizaci dělám také, rovněž však ne za každou cenu. Tohle není kritická aplikace, u které se musí provést pečlivá analýza. Je to jen playlist.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Logik 14. 12. 2011, 00:54:44
Umělej primární klíč se vyplatí používat vždy, už proto, že pak člověk nemusí přemejšlet, jak se ten primární klíč v týdle tabulce jmenuje a jestli je umělej nebo ne, spousta databázovejch nástrojů a knihoven automaticky čeká, že tam je sloupec id (popř. pokud tam je, tak je používání jednodušší) atd....
Další problém dokumentuje právě ten OP - ano, je to na první pohled lepší. Ale v dobrym db násroji máš to id sterjně prolinkovaný, takže to nevadí. A až se jednou dostaneš do situace, že budeš chtít zaevidovat občanskej průkaz, od kterýho neznáš číslo (to už vůbec nemluvim, že spíš se tímdle číslem budou evidovat lidi, a ty nemusej mít OP, můžou mít dva OP atd...).

Vzhledem k tomu, že umělej PK tě nic nestojí a žádnej problém nemá (nebo se pletu? jakej? - napsání o jednoho řídku víc v SQL mi fakt problém nedělá), naprosto nevidim důvod, proč ztrácet čas přemejšlenim nad tim, jestli náhodou todle není speciální případ, kdy to problém není. Navíc, když se velmi často mění zadání za pochodu a to, co problém nebyl...

----

Ad normalizace: ano, je to jen playlist. Dá se to ošulit - a když budeš mít štěstí a zrovna tam nebude někde zakopanej pes, tak na tom i (časově) vyděláš. A právě, pokud chceš vyloučit, že tam někde nevznikne nějakej průšvih, tak je nejjednodušší cesta normalizace. Protože ta Ti to zaručuje automaticky a defakto bez práce (normalizovat umí i cvičená opice). Bez ní je riziko, že to nebude umět něco, co chceš. Namátkou když chceš přehrát všechny Beatles, tak Ti to opravdu přehrálo Beatles, včetně společné desky Beatles a Moravanky, ale už ne kapelu Beatles Revival, tak to s plochou tabulkou uděláš dosti těžko: fulltextem tydle případy nerozlišíš.
 
 

Název: Re:Pomoc se strukturou databáze
Přispěvatel: Kit 14. 12. 2011, 08:52:13
Umělej primární klíč se vyplatí používat vždy, už proto, že pak člověk nemusí přemejšlet, jak se ten primární klíč v týdle tabulce jmenuje a jestli je umělej nebo ne, spousta databázovejch nástrojů a knihoven automaticky čeká, že tam je sloupec id (popř. pokud tam je, tak je používání jednodušší) atd....
Další problém dokumentuje právě ten OP - ano, je to na první pohled lepší. Ale v dobrym db násroji máš to id sterjně prolinkovaný, takže to nevadí. A až se jednou dostaneš do situace, že budeš chtít zaevidovat občanskej průkaz, od kterýho neznáš číslo (to už vůbec nemluvim, že spíš se tímdle číslem budou evidovat lidi, a ty nemusej mít OP, můžou mít dva OP atd...).

Samozřejmě. Podle Logika i logiky je nutné všechny databáze důsledně normalizovat a v každé tabulce je nezbytně nutné mít syntetické primární klíče typu integer. Jenom je otázkou, jestli se pro drobná udělátka tato časová investice dotyčnému vyplatí. Z dlouhodobého hlediska určitě ano, ale byla by velká škoda, kdyby jeho projekt zkolaboval jenom na tom, že by se myšlenkově zasekl na nutnosti normalizace a používání syntetických PK. Refaktorovat může kdykoli později. Mezitím si určitě vybere nějakou zlatou střední cestu.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Logik 14. 12. 2011, 09:02:51
Proč ten uraženej tón?

Jinak já netvrdím, že je to nutné bezpodmínečně vždy. Ale když to člověk dodržuje, neudělá chybu. Což je pro začátečníka obzvlášt vhodné. Porušovat pravidla se má až v okamžiku, kdy člověk dobře rozumí tomu, proč je porušil. A zrovna u umělých klíčů je opravdu jednodušší je mít všude - hodně "přitažlivých přirozených PK" ve skutečnosti jako PK rozumně použít nejde (např. rodné číslo) a umět vybrat data pro PK opravdu vhodná chce nějakou zkušenost. Tak nechápu, proč na jednu stranu tady obhajuješ plochej model, protože je začátečník a na druhou stranu ho cpeš do použití "nebezpečnýho postupu", kterej mu v nejlepším případě přinese pár bytů na disku a možná ani to ne.

A co se týče normalize: pokud by se mu to zaseklo opravdu proto, že by nebyl schopnej rozdělit jednu tabulku do tří, popř. pracovat s víc tabulkama než s jednou, tak nemá smysl, aby se učil programovat. A pokud to zvládne, tak se to aspoň naučí. Doporučovat začátečníkovi, ať "prasí", že se to kdyžtak přepíše je ten nejlepší způsob, jak z něj vychovat "programátorské prase".

Obzvlášť, jestli je to začátečník, tak to "refaktorizovat je možno kdykoli" se pro něj rovná "přepsat je to možné kdykoli" - a to už je to lepší začít psát rovnou dobře.

Název: Re:Pomoc se strukturou databáze
Přispěvatel: Kit 14. 12. 2011, 10:17:49
Proč ten uraženej tón?

Jinak já netvrdím, že je to nutné bezpodmínečně vždy. Ale když to člověk dodržuje, neudělá chybu. Což je pro začátečníka obzvlášt vhodné. Porušovat pravidla se má až v okamžiku, kdy člověk dobře rozumí tomu, proč je porušil. A zrovna u umělých klíčů je opravdu jednodušší je mít všude - hodně "přitažlivých přirozených PK" ve skutečnosti jako PK rozumně použít nejde (např. rodné číslo) a umět vybrat data pro PK opravdu vhodná chce nějakou zkušenost. Tak nechápu, proč na jednu stranu tady obhajuješ plochej model, protože je začátečník a na druhou stranu ho cpeš do použití "nebezpečnýho postupu", kterej mu v nejlepším případě přinese pár bytů na disku a možná ani to ne.

A co se týče normalize: pokud by se mu to zaseklo opravdu proto, že by nebyl schopnej rozdělit jednu tabulku do tří, popř. pracovat s víc tabulkama než s jednou, tak nemá smysl, aby se učil programovat. A pokud to zvládne, tak se to aspoň naučí. Doporučovat začátečníkovi, ať "prasí", že se to kdyžtak přepíše je ten nejlepší způsob, jak z něj vychovat "programátorské prase".

Obzvlášť, jestli je to začátečník, tak to "refaktorizovat je možno kdykoli" se pro něj rovná "přepsat je to možné kdykoli" - a to už je to lepší začít psát rovnou dobře.

Uraženej tón? Ne. Jen jsem chtěl dát najevo, že je možné použít i jiné přístupy, než logické. Pokud očekáváš logické argumenty proti logickému řešení, tak je logicky nemohu mít. To ale neznamená, že by mě to uráželo.

Možná příliš mnoho SQL dotazů píši interaktivně přímo do databáze, nechce se mi je psát dlouhé a tak si zjednodušuji i návrh tabulek. Jsou to většinou takové drobnosti, které jiní uživatelé patlají v Excelu. Tabulkové administrace nepoužívám, ale už při rozdělení do 3 tabulek se takový administrátor používá mnohem hůř, než když je vše v jedné tabulce. Nejlépe je nepoužívat.

Pokud například vkládám data do ploché tabulky, stačí mi jeden jednoduchý insert. Při vkládání do dobře normalizované databáze musím podmíněně uložit třeba interpreta do jedné tabulky a teprve druhým insertem s vnořeným selectem vložit hlavní záznam. Bude určitě dobře, když si jako začátečník vyzkouší oba přístupy na tak jednoduché aplikaci.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Logik 14. 12. 2011, 12:13:51
- Vzhledem k tomu, že to má být na deset tisíc písniček, tak je tam asi těžko bude vkládat věci ručně. A i kdyby, tak pokud to bude dělat často, tak tady jsou view a stored procedury, kterejma se to dá snadno nahradit. Ano, je to složitější než přímý zápis do tabulky. Rozdíl mezi těma řešeníma je ale ten: když budu chtít s normalizovanejma tabulkama realizovat "plain pohledy", je to rozumně a vcelku rychle řešitelné. Když budu chtít s plochou tabulkou realizovat ty featury, které neumožňuje, musíš to v podstatě celé přepsat.

- Ano - do ploché tabulky se lépe vkládá. Ale jak jsem ukazoval na příkladu s beatles, plochá tabulka prostě nevyhovuje vlastnostem, které jsou na řešení požadovány. Je to klasické přizpůsobení aplikace programátorovi, místo toho, co by se programátor přizpůsobil aplikaci.

- Jinak souhlasím: na "primitivní" rychlé řešení věcí, do kterých nebudu muset nikdy znova sáhnout se to řešení hodí. V případě, že se musí takové řešení udržovat, tak člověk ale brzo zjistí, že počáteční úspora času se hodně prodraží. A když si přeteč první post, tak tazatel právě do tohoto stádia došel. Zaflikovat to tím, že  jen použiju rychlejší technologii na vyhledávání mi prostě nepřijde jako dobrej nápad: ani z hlediska vývojářskýho, ani didaktickýho.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Vláďa J 14. 12. 2011, 17:41:53
Tak jsem si zkoušel na internetu něco najít, ale nastudoval jsem pouze spousty teorie, ale praktický ukázky jsem nikde nenašel. :-(

Každopádně minimálně na to, abych zobrazil všechny žánry, interprety a alba, bude výhodné mít samostatnou tabulku. Ta ještě bude obsahovat cestu k obrázku (interpreta nebo alba). Akorát nevím, jak to správně provázat. V Excelu jsem namaloval takový graf, jak by to asi mělo vypadat:

(http://i43.tinypic.com/a242lz.png)

Filtrovat se bude vždy pouze zleva doprava. Čili nikdy asi nebudu chtít vědět, kdo všechno vydal album s názvem "Best of".

Samozřejmě ta poslední tabulka bude mít mnohem víc sloupců a údajů. Je logické, že sloupce žánr a autor nemusí mít syntetický primární klíč, naopak album ho mít musí.

Potřeboval bych pomoc s tím, jak tuto strukturu prakticky vytvořit (přímo SQL příkazy). Stačí nějaký nástřel, zbytek už si odvodím a domyslím. A nebo alespoň odkaz s praktickými ukázkami, jak spojovat tabulky.

Potom také nevím, jak v tomto případě vyhledám následující věci:
1) Všechny alba a písničky od Elvise s hodnocením vyšším než 3.
2) Všechny interprety, alba a písničky ze 60. let
3) Všechno kromě Country

Předpokládám, že pro vyhledání písniček samotných mi vytvoření dalších tabulek nic nepřinese, ale urychlí se tím výrazně výpis ostatních sloupců. U prvních 2 výpisů z mých příkladů se asi neurychlí vůbec nic, předpokládám.

Ještě závěrem podotýkám, že do databáze se údaje ukládají automaticky proskenováním adresáře s písničkami. Každá písnička tedy pouze jednoho interpreta, jedno album atd. Pokud je jedna písnička na dvou albech, není to pro mě ta samá písnička (bude to jiný soubor). Písničky by mohly mít teoreticky víc žánrů, ale tím bych to asi nechtěl komplikovat, takže i ten bude pouze jeden.

Jak tedy prakticky na to?
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Logik 14. 12. 2011, 20:15:03
Hmm, dobrej SQL tutoriál je těžký, většinou stojej za houby: buďto moc teorie, nebo blbiny.
zkus třeba todle:
http://www.sallyx.org/sally/psql/
ale vybírej si z toho, co potřebuješ (např. ignoruj úpravy již existujících tabulek). Pokud to bude moc, tak se ozvy, pokusím se najít něco jednoduššího, co by mělo cenu...

Název: Re:Pomoc se strukturou databáze
Přispěvatel: Logik 14. 12. 2011, 20:17:48
Ještě upřesním, zaměř se na kapitoly
- První tabulka
- Vytváření relací
- Select I (jestli nevíš nic o selektu), ale tady přeskoč spojování tabulek
- Select III (tady je to spojování tabulek pořádně).
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Ivan 15. 12. 2011, 10:58:46
Tohle není kritická aplikace, u které se musí provést pečlivá analýza. Je to jen playlist.
Presne. Na neco takoveho by melo stacit EAV.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Kit 15. 12. 2011, 11:58:05
Tohle není kritická aplikace, u které se musí provést pečlivá analýza. Je to jen playlist.
Presne. Na neco takoveho by melo stacit EAV.
Až na to, že jsi špatně napsal citaci. Tohle jsem napsal já a Logik s tím nesouhlasí.

EAV přesouvá databázovou logiku do aplikace. V některých případech to může vyhovovat.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Vláďa J 16. 12. 2011, 01:45:47
Tak díky vašim radám už se mi to tak trochu rýsuje, ale mám ještě dva problémy, které neumím nějak vhodně vyřešit.

První je jednoduchá otázka - je lepší použít DISTINCT nebo GROUP BY? Jaký je v tom vlastně rozdíl?

Druhá věc je trošku složitější na vysvětlení, ale snad to zvládnu. Chtěl bych mít tabulku, kde budou sloupce album, autor alba, cesta k obalu a id alba (syntetický primární klíč). Album a interpret alba ho jednoznačně určují.

Problém nastává, jak takovou tabulku vytvořit. Když prohledávám písničky, tak nevím, jestli album, ze kterého písnička je, už v databázi není a už vůbec netuším id toho alba. Napadá mě tedy několik možností, jak to řešit, ale všechny se mi zdají dost krkolomné. Jediný aspoň trošku rozumný postup mě napadá tohle:
1) Zjistím, jestli už album je v tabulce s alby
2) Pokud ne, přidám ho s novým id, pokud už existuje, zjistím jeho id
3) Do tabulky s detaily o písničkách zapíšu id alba

Zdá se mi trochu zbytečné, abych s každou písničkou zjišťoval, jestli už daná kombinace alba a interpreta existuje. Nejde to nějak zjednodušit? Mně nějak došly nápady...
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Kit 16. 12. 2011, 10:00:16
První je jednoduchá otázka - je lepší použít DISTINCT nebo GROUP BY? Jaký je v tom vlastně rozdíl?

GROUP BY se používá, pokud potřebuješ agregovat některé sloupce. Jinak stačí DISTINCT.

Druhá věc je trošku složitější na vysvětlení, ale snad to zvládnu. Chtěl bych mít tabulku, kde budou sloupce album, autor alba, cesta k obalu a id alba (syntetický primární klíč). Album a interpret alba ho jednoznačně určují.

Problém nastává, jak takovou tabulku vytvořit. Když prohledávám písničky, tak nevím, jestli album, ze kterého písnička je, už v databázi není a už vůbec netuším id toho alba. Napadá mě tedy několik možností, jak to řešit, ale všechny se mi zdají dost krkolomné. Jediný aspoň trošku rozumný postup mě napadá tohle:
1) Zjistím, jestli už album je v tabulce s alby
2) Pokud ne, přidám ho s novým id, pokud už existuje, zjistím jeho id
3) Do tabulky s detaily o písničkách zapíšu id alba

Zdá se mi trochu zbytečné, abych s každou písničkou zjišťoval, jestli už daná kombinace alba a interpreta existuje. Nejde to nějak zjednodušit? Mně nějak došly nápady...
1. Přidej album příkazem INSERT OR IGNORE. Název alba musí mít atribut "UNIQUE". Pokud už existuje, nevloží se.
2. Přidej písničku a ID alba doplň vnořeným SELECTem.

Oba příkazy vlož do jedné transakce, bude to rychlejší.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Logik 17. 12. 2011, 23:56:58
Kdy chceš použít distinct/group by? IMHO ho tady vůbec potřebovat nebudeš. (Pokud chceš vypsat všechny autory od píšniček, tak přehlednější je psát
SELECT * FROM autori WHERE id IN (SELECT autor_id FROM pisnicka WHERE .... 
než to spojovat joinem a jinde mě nenapadá, kde by se to mohlo použít.
Jinak, jak říkal předřečník, GROUP BY se používá, pokud tě zajímá např. počet "stejnejch" řádek, součet nějakejch polí (např. celková délka alb jako součet délek jednotlivejch písniček GROUP BY album_id) apod.
-
Pokud na začátku chceš jednorázově vytvořit novou tabulku alb a chceš to fakt rychle, tak tady bych se vykašlal na databázi a udělal si vedle phpkovský pole s nima a kouknul se tam. Ale nejlepší je IMHO udělat defaultní rutinu na přidávání písniček, kterou budeš používat i jindy, a vykašlat se na to, že se bude pokaždý písnička hledat znova - dělá se to jen jednou. Navíc pokud si uděláš metodu na vyhledání alba, tak tam můžeš udělat cache: pak Ti stačí záznamy zpracovávat seřazené dle abla a máš to defakto bez výkonnostní ztráty.
Takovýdle věci se optimalizujou, teprv až když je to hotový a je to moc pomalý.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: mimi.vx 18. 12. 2011, 01:37:51
trochu offtopic , proc vse delas v pythonu kdyz v QT je vrstva pro SQL + Vrstva pro multimedia ...takze se da celkem rychle a na par radcich stvorit prehravac cehokoliv
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Logik 18. 12. 2011, 18:46:35
Jo, omlouvám se za to phpkovský pole, zapoměl jsem na zadání, tak pythonovskej dictionary, ten má taky (snad) dobrou implementaci.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Vláďa J 19. 12. 2011, 09:38:11
mimi.vx> No podle mě to je celkem fuk, jestli použiju SQLite z Pythonu nebo Qt. Jelikož celá logika programu je v Pythonu a v Qt (respektive QML) je pouze UI, tak mi to přišlo takhle logičtější. Na přehrávání používám Phonon z Qt. Ale možná přejdu GStresmer nebo QtMultimediaKit.

Logik> Díky za perfektní rady. Zkusím to nějak zpracovat. Já už to mám v podstatě všechno funkční (s pomocí jedný tabulky), akorát některý věci prostě trvají dýl než by se mi líbilo a taky začíná váznout udržovatelnost. Proto jsem to začal řešit.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Vláďa J 26. 12. 2011, 20:04:03
Rád bych se ještě vrátil k tomuto tématu a požádal o pár rad.

Něco jsem dal dohromady, ale nejsem s tím spokojený. Respektive jsem to celý zprasil, protože mi některý věci nedošly.

Zásadní problém je v tom, že tak jak jsem tabulky poskládal, tak by ke každému umělci musel příslušet jeden žánr, ke každému albu jeden umělec atd. Předpokládám, že jediný rozumný způsob, jak tohle vyřešit, je vytvoření porovnávací tabulky mezi všema tabulkama. To už jich ale bude šílená spousta, nebude to nakonec kontraproduktivní?

Vypadalo by to asi takhle:

ŽánrŽánr_id
Folk-rock1
Country2
Rock & Roll3

InterpretInterpret_id
Elvis Presley1
Pavel Bobek2

Žánr_idInterpret_id
12
21
22
31
32

Podobné porovnávací tabulky by potom byly mezi všema třídícíma tabulkama. Dává to smysl? Je to takhle správně?

A jak se potom udělá JOIN takových tabulek?

Taky mě trápí, jak implementovat autora alba. Chtěl bych, aby v UI v seznamu "Interpret" bylo obojí. To znamená, že tam bude jak Country Beat Jiřího Brabce, jakožto autor alba, tak i Nadě Urbánková, Jiří Grossman nebo Ladislav Vodička, jakožto interperti jednotlivých písniček.

Jediná rozumná věc, která mě napadla, je přidat autora alba do tabulky alb, protože každé album má logicky pouze jednoho autora. Pokud vytvářím seznam interpretů/autorů, vyjedu si s "DISTINCT" autory alb a celou tabulku "Interpret". Potom oba seznamy spojím a vymažu duplicity. Zdá se mi to ale dost krkolomné. Bohužel jsem na nic lepšího nepřišel. Položka "Autor alba" do toho vnáší spoustu různých nesystematičností a výjimek, kterým bych se rád vyhnul, ale nevím jak. Každopádně tohle je spíš podružný problém, protože to funguje, jenom se mi to nelíbí. Důležité pro mě je, jak udělat ty tabulky.

Díky všem za rady.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: urso2 26. 12. 2011, 21:38:10
Rád bych se ještě vrátil k tomuto tématu a požádal o pár rad.

Něco jsem dal dohromady, ale nejsem s tím spokojený. Respektive jsem to celý zprasil, protože mi některý věci nedošly.

Zásadní problém je v tom, že tak jak jsem tabulky poskládal, tak by ke každému umělci musel příslušet jeden žánr, ke každému albu jeden umělec atd. Předpokládám, že jediný rozumný způsob, jak tohle vyřešit, je vytvoření porovnávací tabulky mezi všema tabulkama. To už jich ale bude šílená spousta, nebude to nakonec kontraproduktivní?

Vypadalo by to asi takhle:

ŽánrŽánr_id
Folk-rock1
Country2
Rock & Roll3

InterpretInterpret_id
Elvis Presley1
Pavel Bobek2

Žánr_idInterpret_id
12
21
22
31
32

Podobné porovnávací tabulky by potom byly mezi všema třídícíma tabulkama. Dává to smysl? Je to takhle správně?

A jak se potom udělá JOIN takových tabulek?

Taky mě trápí, jak implementovat autora alba. Chtěl bych, aby v UI v seznamu "Interpret" bylo obojí. To znamená, že tam bude jak Country Beat Jiřího Brabce, jakožto autor alba, tak i Nadě Urbánková, Jiří Grossman nebo Ladislav Vodička, jakožto interperti jednotlivých písniček.

Jediná rozumná věc, která mě napadla, je přidat autora alba do tabulky alb, protože každé album má logicky pouze jednoho autora. Pokud vytvářím seznam interpretů/autorů, vyjedu si s "DISTINCT" autory alb a celou tabulku "Interpret". Potom oba seznamy spojím a vymažu duplicity. Zdá se mi to ale dost krkolomné. Bohužel jsem na nic lepšího nepřišel. Položka "Autor alba" do toho vnáší spoustu různých nesystematičností a výjimek, kterým bych se rád vyhnul, ale nevím jak. Každopádně tohle je spíš podružný problém, protože to funguje, jenom se mi to nelíbí. Důležité pro mě je, jak udělat ty tabulky.

Díky všem za rady.
Tomu, co zde řešíš, se říká KARDINALITA. Je zde vztah (kardinalita) více ku více, takže se to řeší takto (pozor na kartezký součin).
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Vláďa J 26. 12. 2011, 23:27:16
Tak to jsem se trochu unáhlil, v případě 4 sloupců (žánr, interpret, album, skladba) to budou pouze 3 porovnávací tabulky, protože tabulka se skladbama žádnou porovnávací s ostatníma nepotřebuje (vše je unikátní). Zatím na malém počtu písniček to funguje k mé spokojenosti, akorát z toho lezou příšerné SQL dotazy typu:
Kód: [Vybrat]
SELECT title, track FROM genre_table JOIN genre_artist ON genre_table.genre_id = genre_artist.genre_ref JOIN artist_table ON genre_artist.artist_ref = artist_table.artist_id JOIN artist_album ON artist_table.artist_id = artist_album.artist_ref JOIN album_table ON artist_album.album_ref = album_table.album_id JOIN Library ON genre_table.genre_id = Library.genre_ref AND artist_table.artist_id = Library.artist_ref AND album_table.album_id = Library.album_ref WHERE genre='Soundtrack' AND (album_artist='Wings' OR artist='Wings') AND album='James Bond Themes' ORDER BY track ASC
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Vláďa J 27. 12. 2011, 00:21:54
Tak jsem se opět unáhlil. Nefunguje to. Sice tuším proč, ale nevím, co s tím.

Například když kliknu na dynamický playlist, který se jmenuje "Hudba 3+" a vyjedu si znovu sloupce "žánr", "interpret", "album", tak se všechny položky vyjedou několikrát.

konkrétně u žánru se použije tento příkaz:
Kód: [Vybrat]
SELECT genre, genre_id FROM genre_table JOIN Library ON genre_table.genre_id = Library.genre_ref WHERE rating >= 3 AND genre <> "Speech" ORDER BY genre ASC
Skladeb, které tomu odpovídají, je v knihovně 6 a tak se žánr objeví 6x stejný. Samozřejmě to můžu jednoduše ošetřit přidáním "DISTINCT", jenže se obávám, že tím zase všechno zpomalím.

Ještě hůř to dopadne u alba. V knihovně je zatím pouze jedno s 28 stopami. Použije se tento dotaz:
Kód: [Vybrat]
SELECT album, album_picture, album_id FROM genre_table JOIN genre_album ON genre_table.genre_id = genre_album.genre_ref JOIN album_table ON genre_album.album_ref = album_table.album_id JOIN Library ON genre_table.genre_id = Library.genre_ref AND album_table.album_id = Library.album_ref WHERE rating >= 3 AND genre <> "Speech" ORDER BY album ASC

A na ten mi vypadne to samé album 168x. Nechápu proč a už vůbec nevím, co s tím dělat.

Dokážete mi někdo poradit, co jsem zvoral?
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Kit 27. 12. 2011, 00:24:41
Zásadní problém je v tom, že tak jak jsem tabulky poskládal, tak by ke každému umělci musel příslušet jeden žánr, ke každému albu jeden umělec atd.
To je otázka, zda žánr patří k umělci, albu nebo ke konkrétní skladbě. Případně zda připustíme více žánrů k jedné skladbě/albu/umělci.

Pro sjednocení a vyřazení duplicit slouží UNION.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Kit 27. 12. 2011, 00:28:50
...
A na ten mi vypadne to samé album 168x. Nechápu proč a už vůbec nevím, co s tím dělat.

Však to už psal Logik, jak to máš udělat. Stačí se podívat do historie této diskuze, hledej vnořený dotaz. Bude to s ním i rychlejší.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Vláďa J 27. 12. 2011, 13:42:37
Kit> Vnořený dotaz vím, co je, ale nevím, jak ho použít. Mohl bys mi prosím poradit?

Pro zjednodušení uvedu následující příklad. Mám 3 tabulky - žánr, interpret a skladby. Tabulka žánr má sloupce žánr, žánr_id analogicky tabulka interpret má sloupce interpret a interpret_id. Mezi nima je asociační tabulka. Tedy stejně jako jsem to psal v mém včerejším příspěvku.

Na to je ale ještě napojená tabulka se skladbami, která má sloupce název, hodnocení, žánr_ref a interpret_ref. Poslední dva sloupce jsou cizí primární klíče odkazující na první dvě tabulky.

Jak z těchto tří tabulek co nejrychleji zjistím všechny interprety, kteří hrají žánr "Rock" a z tohoto žánru mají alespoň jednu skladbu s hodnocením vyšším než 3?

Když jsem měl vše pouze v jedné tabulce, stačil naprosto triviální dotaz
Kód: [Vybrat]
SELECT DISTINCT artist FROM Library WHERE genre = "Rock" AND rating > 3
Takový dotaz byl i na UMPC s Atomem provedený nejpozději do vteřiny. Pokud použiju nyní s více tabulkami analogický dotaz, trvá na hodně rychlém počítači více než 10s. Takže místo urychlení jsem to mnohonásobně zpomalil. :-(

Celý problém je o to komplikovanější, že SQL dotazy se musí generovat automaticky podle toho, co si uživatel přeje vyfiltrovat.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Kit 27. 12. 2011, 14:49:05
http://forum.root.cz/index.php?topic=3418.msg28782#msg28782

Něco takového netestovaného:
SELECT DISTINCT artist FROM Library WHERE genre_id IN (SELECT genre_id FROM genre_tab WHERE genre="Rock");

Nejprve uděláš SELECT žánru a teprve potom ho JOINem spojíš s ostatními tabulkami. Abys zvýšil rychlost, budeš asi muset indexovat i cizí klíč, tedy genre_id v tabulce artist.

Normalizace databází je hezká věc, na takové úloze se to můžeš dobře naučit. V tom má Logik pravdu. Otázkou je, zda se to v takové aplikaci vyplatí, zda její složitost nepřekročí únosnou míru pro další udržování kódu. Dotazy jsou totiž komplikovanější. Vím, že fulltext popírá normalizaci a má své nevýhody, ale v uvedeném případě bych ho použil. A už mlčím, abych to od Logika zase neschytal.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Vláďa J 27. 12. 2011, 16:14:17
Díky za nakopnutí. Už mi to do sebe trochu zapadá. V mém případě je to trochu složitější. Nakonec tenhle ód vypadá slibně:
Kód: [Vybrat]
SELECT artist FROM artist_table WHERE artist_id IN
    (SELECT DISTINCT artist_ref FROM Library WHERE rating >= 3 AND genre_ref IN
        (SELECT genre_id FROM genre_table WHERE genre <> 'Speech'))

je v podstatě stejně rychlý jako původní
Kód: [Vybrat]
SELECT DISTINCT artist_ref FROM Library WHERE rating >= 3 AND genre_ref <> 0
Oba na mém stolním počítači zaberou 0,015s při 22 000 záznamech, což je OK. Teď jenom vymyslet, jak takový kód vygenerovat automaticky. To bude, obávám se, solidní oříšek.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: BoneFlute 27. 12. 2011, 16:28:24
Nezaznělo to tady. Ale když použiješ distinc, tak se tlačí všechna data, a pak se zahodí. Když použiješ agregaci, tak se jede po indexech. Tedy toto, jak jsem to upravil by mělo být rychlejší. Tak zní teorie. Zkus to, a napiš, jaký je v tom rozdíl.

Kód: [Vybrat]
SELECT artist FROM artist_table WHERE artist_id IN
    (SELECT artist_ref FROM Library WHERE rating >= 3 AND genre_ref IN
        (SELECT genre_id FROM genre_table WHERE genre <> 'Speech')
    GROUP BY artist_ref
    )


Další optimalizace by mohla být ta, že nepoužiješ IN, ale JOINování. Asi to nebude vypadat tak hezky, ale některé databáze mají s optimalizací IN problémy.

Kód: [Vybrat]
SELECT artist
FROM artist_table AS c
LEFT JOIN (
  SELECT a.artist_ref
  FROM library AS a
  LEFT JOIN genre_table AS b ON b.genre_id = a.genre_ref AND b.genre <> 'Speech'
  GROUP BY a.artist_ref
) d ON d.artist_ref=c.artist_id
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Vláďa J 27. 12. 2011, 17:13:24
Použití GROUP BY místo DISTINCT nemá na rychlost vůbec žádný vliv. Pokud použiju JOIN tak, jak je to v druhém případě (a ještě doplním AND rating >= 3, aby to odpovídalo), tak se výpi naopak cca 3x zpomalí.

Takže asi zůstanu u použití "IN".

Jenom mě ještě napadla jedna věc. Jak co nejrychleji vypíšu všechny stopy (tedy celou tabulku Library) tak, aby ve výpisu byly všechny sloupce tabulky Library, ale například artist_ref se nahradilo sloupci artist a artist_picture a analogicky se sloupci genre_ref a album_ref.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Logik 28. 12. 2011, 17:17:53
- to s tim rozdílem mezi distinct a group by bych se neučil, sice to asi byla alespoň v nějaký verzi mysql pravda (dělali tam nějaký optimalizace ohledně toho), ale obecně je to čunačina - dobře napsanej optimalizátor by měl umět užít index i u distinct - a naopak optimalizátory často selhávaj i u group by.

- pokud děláte vnořenej dotaz, tak žádný distinct (a už vůbec ne group by) nepotřebujete! Umělec je přeci v tabulce umělců pouze jednou! To, že je jeho číslo ve vnořenym dotazu víckrát je irelevantní.

- s tím, že JOIN je někdy rychlejší souhlasím, ale tuším, že všechny databáze co s nima mam větší zkušenosti z hlediska výkonu (postgresql, mysql, firebird) to už zvládaj dobře. Mysql 4 s tím měla velký problémy a Firebird 1 taky, tam jsem joinoval jak divej.

- osobně bych udělal tabulku lidí a ty pak přiřazoval k albu (nebo ještě lépe písničce, ale k albu je to přijatelný zjednodušení) buď jako interprety, nebo jako autory.  tzn udělat tabulku:
 album_id, osoba_id, (autor|interpret)
popř. v jednoduššim provedení dát do písničky pole autor a interpret (ale to nedovolí dva interprety apod).

- Stejnětak žánr podle mne musíš přiřazovat k albům a ne lidem. Jinak Ti vypadne např. mezi folkrokem bethowenova devátá, protože tu hrála česká filharmonie, která taky hrála s čechomorem (viz Rok Ďábla). A takovejdle "multižánrovejch" je spousta.

- automaticky generovaný dotazy alá IN nejsou zas takovej problém: představ si každej poddotaz jako jednu podmínku ve where. Prostě místo
žánr = něco
tam je podmínka
žánr IN (něco).
A to něco postavíš stejně, jako ten celej dotaz.
Každej filtr ve vyhledávači pak přidá jednu podmínku za WHERE, pospojovanou AND. To, že to chceš mít v GUI hierarchický neznamená, že musí bejt hierarchická struktura DB ani že musíš do sebe nořit deset dotazů.

- Co se týče udržování kódu, tak samozřejmě něco jde v plochý struktuře lépe, něco hůře. Ale zrovna tydlety vnořený dotazy, když má člověk dobrou knihovnu, moc nestojí. Doporučuju si napsat nějakej objekt na postupný skládání dotazů, z pythonu např. jsem za pět sekund našel sqlpuzzle3k. Ale nevím, jak se to používá, já používám bazmek vlastní výroby, je to pár řádek

result=new SQLurey('pisnika')
a každej filtr by vracel nějakej "filtr",
result.addWhere(filtr)
a ten filtr bych třeba na všechny písničky od Evy a Vaška udělal takhle:
filtr=new SQLInQuery('id', 'SELECT pisnicka_id FROM pisnickyautori pa INNER JOIN autori a ON (pa.autor_id = autor.id) WHERE autor.jmeno = "Eva a Vašek")
až na to, že tydle písničky bych spíš nechtěl, takže bych udělal
filtr=filtr.not()
a pak bych chtěl třeba ještě jen nový písničky
result.addWhere("rok > 2009")

takovádle knihovna ušetří hodně práce a zároveň zpřehlední to automatický generování SQL - jakože většinou se generuje automaticky.





Název: Re:Pomoc se strukturou databáze
Přispěvatel: Vláďa J 28. 12. 2011, 21:52:19
Logik> Všechno tohle jsem zkoušel. Funkci na automatický generování SQL dotazu tam samozřejmě mám už teď k plochý tabulce.
Zkoušel jsem jí předělat na použití se normalizovanou databází. Jednoduchý dotazy fungují dobře, jenže to začíná být značně komplikovaný v okamžiku, kdy použiju dynamický playlisty. Jako příklad jednoho běžnýho playlistu jsem uvedl hudba s hodnocením 3 a vyšším. Tedy dynamický playlist má podobu rating >= 3 AND genre <> "Speech". Tohle je samozřejmě nepoužitelné. Sloupec rating je v tabulce se všemi písničkami, genre je naopak v samostatné tabulce.

Takže by asi bylo nutné ten dynamický playlist vytvořit už tak, aby s tímhle počítal. To by asi nějak šlo.

Vztah jednotlivých sloupců je samozřejmě M:N. Každé album může mít víc autorů i žánrů, ke každému interpretovi patří několik žánrů atd. Udělal jsem tedy tabulky žánr, interpret a album a další 3 asociační tabulky mezi každou z nich. V hlavní tabulce s názvem "Library" jsou potom reference do všech 3 předchozích tabulek.

Ale zasekl jsem se na jiné věci. Pořád se trochu motám v tom, jak použít JOIN. Zkusím lehce shrnout mé myšlenkové pochody:

Dejme tomu, že použiju můj již zmíněný dynamický filtr a formuluji jej jako podmínku
Kód: [Vybrat]
SELECT artist FROM artist_table WHERE artist_id IN
    (SELECT artist_ref FROM Library WHERE rating >= 3 AND genre_ref IN
        (SELECT genre_id FROM genre_table WHERE genre <> 'Speech')
    )

Jenže tohle mi vyhodí opakující se autory, jak tento dotaz přepíšu tak, aby vypadly pouze unikátní záznamy bez použití DISTINCT / GROUP BY?

Dál potom mohu chtít vyjet seznam písniček pro playlist. To bude dvourozměrné pole se všemi dostupnými informacemi ke každé písničce. Můžu analogicky s předchozím dotazem získat toto:
Kód: [Vybrat]
SELECT * FROM Library WHERE rating >= 3 AND genre_ref IN
        (SELECT genre_id FROM genre_table WHERE genre <> 'Speech')
    )

Jenže místo autora budu mít autor_id, místo alba bude album_id atd. Jak upravím dotaz tak, abych získal i sloupce autor, obrázek_autora, album, autor_alba atd.? Zkoušel jsem to a výsledkem byla vždycky nějaká hrůza se spoustou JOINů a podmínek ON a výpis trval nekonečně dlouho. Přitom by mělo jít o triviální věc (z hlediska logiky databáze). Na tohmle jsem se zasekl a nevím, jak dál. Všude jsem našel pouze příliš jednoduché příklady, které obsahovaly maximálně 2 tabulky.

Zatím jsem po vzoru Járy Cimrmana průkopníkem slepých uliček. Až si někdy říkám, jestli mi za to ta vteřina, o kterou to urychlím, stojí. Jenže já blbec jsem prefekcionista a pokud to jde udělat líp, tak to tak udělat prostě chci.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Mareg 29. 12. 2011, 08:32:20
Přiznám se, že nevím jaký to bude mít dopad na výkon a jestli tam nebudou ty záznamy vícekrát (nechce se mi dělat ty TB), takže je ta správná cesta použítí INů, ale pro MySQL bych to za pomoci JOINtů napsal takto:

Kód: [Vybrat]
SELECT artist FROM artist_table
  LEFT JOIN Library ON artist_id = artist_ref
  LEFT JOIN genre_table ON genre_ref = genre_id
WHERE rating >= 3
  AND genre <> 'Speech'
Případně tam můžete přidat GROUP BY na potřebné sloupce.

A mimochodem, přijdem mi zbytečné pojmenovávat tabulky "tabulky", takže podle mě by to vypadalo následovně:
artist_table  - artists
artist_table.artist_id - artists.id
Library.artist_ref - library.artist_id

Zkuste si zavést pravidla pro pojmenovávání TB i sloupců, nemusíte pak přemýšlet nad tím jestli se ten klíč jmenuje artist_id nebo artist_ref.
Primární klíč je vždy id, vazba na PK je <nazev_tb>_id, atd.

Kód: [Vybrat]
SELECT artist FROM artists a
  LEFT JOIN library l ON a.id = l.artist_id
  LEFT JOIN genres g ON l.genre_id = g.id
WHERE g.rating >= 3
  AND g.type <> 'Speech'

Citace
Jenže místo autora budu mít autor_id, místo alba bude album_id atd. Jak upravím dotaz tak, abych získal i sloupce autor, obrázek_autora, album, autor_alba atd.? Zkoušel jsem to a výsledkem byla vždycky nějaká hrůza se spoustou JOINů a podmínek O
No to je právě ta "krása" normalizace DB, že pak pro každý údaj musíte sáhnout do správné TB, což se dá udělat buď v kódu programu, nebo tím JOINtem  :). Ale i přesto si myslím, že normalizace a syntetické PK typu int jsou tou správnou cestou, tak ja radí Logik.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Kit 29. 12. 2011, 09:41:33
A mimochodem, přijdem mi zbytečné pojmenovávat tabulky "tabulky", takže podle mě by to vypadalo následovně:
artist_table  - artists
artist_table.artist_id - artists.id
Library.artist_ref - library.artist_id

Zkuste si zavést pravidla pro pojmenovávání TB i sloupců, nemusíte pak přemýšlet nad tím jestli se ten klíč jmenuje artist_id nebo artist_ref.
Primární klíč je vždy id, vazba na PK je <nazev_tb>_id, atd.

S tím mohu souhlasit a sám to tak používám.

Je ještě jedna alternativa, která je hojně používána a prosazována. Aby názvy cizího klíče v jedné a příslušného primárního klíče v druhé tabulce byly shodné. Tedy tabulka artist, v ní primární klíč artist_id. V ostatních tabulkách cizí klíč artist_id. Má to tu výhodu, že je možné v JOINech používat USING a jmenovat ten sloupec pouze jednou. Dotazy se tím zpřehlední.

Dále bych si dal velký pozor, aby nebyl někde stejný název tabulky a nějakého sloupce třeba i v jiné tabulce. Některé chyby v SQL dotazech se pak špatně hledají. Třeba sloupec `artist` v tabulce `artist` vypadá hodně divně. Přitom v tom sloupci je _jméno_ zpěváka, proto by se ten sloupec měl jmenovat třeba `jmeno` nebo `name`.
Název: Re:Pomoc se strukturou databáze
Přispěvatel: Logik 29. 12. 2011, 17:37:19
- ad pojmenování:
doporučuju tabulku v jednotném čísle. Název cizího klíče <table>_id. Název primárního klíče buďto shodný s cizím klíčem, nebo jen id, obé má své výhody a nevýhody. A cíleně se vyhýbat duplicitám v jménech sloupců spíš nedoporučuju: ono to ve větší db ani nejde. Spíše je lepší si zvyknout vždy psát plně kvalifikované názvy sloupců, tzn tabulka.sloupec.

- ad GROUP BY - pokud někde musíte použít GROUP BY, kde přímo nepotřebujete agregaci, asi je dotaz špatně

- jak dělat filtry
píšeš, že všechny tabulky máš M:N. Tak to je skvělé, stačí Ti jeden mustr, kterej půjde aplikovat všude. Představ si teď filtr jako blackbox , kterej Ti hodí všechny možný čísla písniček. Takovej filtr poskládáš snadno, třeba u autora:

Kód: [Vybrat]
(SELECT pisnicka_id FROM pisnicka_autor WHERE autor_id IN (SELECT id FROM autor WHERE jmeno = "Cimrman")
Podmínka ve where může bejt různá, např. WHERE autor_id = 1 nebo autor_id IN (1,2,3,4), podle toho, jak autory vybíráš.
Úplně stejnou podmínku můžeš udělat pro žánry, alba, co tě napadne.
No a teď vem tendle kus dotazu jakko blackbox kdekoli potřebuješ vybrat písničky:

Citace
SELECT * FROM pisnicka WHERE id IN <blackbox>

Nebo kde potřebuješ vybrat <entitu> (např. žánr, autora, atd...) z daných <entit>, které jsou možné pro již vybrané písničky

Citace
SELECT * FROM <entita> WHERE id IN (SELECT <entita>_id FROM <entita>_<pisnicka> WHERE <pisnicka_id> IN <blackbox>

V podstatě todle jsou všechny dotazy, které budeš potřebovat.

- ad opakující autoři:
Pokud Ti ten dotaz
Kód: [Vybrat]
SELECT artist FROM artist_table WHERE artist_id IN (...subselect...)vyhodí opakující se autory, je to čistě proto, že v té tabulce opakující autoři prostě jsou :-). Řešení tedy není expost group by, ale už při plnění tabulky se duplicit vyvarovat. Nejlépe tak, že hodíš na sloupce, které by neměli být duplicitní omezení (constraint) UNIQUE (zároveň slouží jako index) a tabulku znova naplníš. Samo Ti to zařve v okamžiku, kdy tam budeš chtít vložit druhého stejného autora a budeš moct to opravit tak, aby to místo toho použilo již toho co tam je.

-ad jak dostat autora, album atd....
No jsou dvě možnosti: buďto opravdu přidat pro každou subtabulku, nebo si ty záznamy ze subtabulek vypsat vedle, strčit do nějakýho hashpole (v pythonu asi dictionary) a tahat je v případě potřeby. První řešení se vyplatí, pokud se tahá relativně málo písniček, druhý řešení se hodí, pokud se tahá hodně písniček a počet písniček značně převyšuje počet autorů/alb atd...

Pokud jsi tam Ty joiny dal a bylo to pomalý, pak je to možná tím problémem z předchozího odstavce, popř. je také možné, že Ti někde schází indexy... anebo tam např. někde schází nějaká potřebná podmínka. Zkus sem až ten předchozí problém vyřešíš dát ten dotaz, co je pomalej, a zároveň ještě jeho EXPLAIN (spusť ten samý dotaz, ale před něj napiš EXPLAIN, tzn. EXPLAIN SELECT ...........

- Jinak Ti doporučuju na to nezanevřít - mkožná to je opravdu overkill, ale na to, abys pro příště poznal, co už je overkill a co ne bys měl ten "overkill" zvládat a to se nenaučíš líp, než tak, že to prostě napíšeš.






Název: Re:Pomoc se strukturou databáze
Přispěvatel: Vláďa J 17. 09. 2012, 10:02:05
Po dlouhé době jsem se konečně dostal k tomu, abych začal pracovat na optimalizaci mé databáze. Začal jsem komplet od nuly a tentokrát to, zdá se, funguje. Akorát některé dotazy trvají dle mého názoru neúměrně dlouho a já netuším proč. Je něco špatně na tomto dotazu? Dal by se nějak urychlit?

Kód: [Vybrat]
SELECT title, id, track FROM Library WHERE id IN (SELECT title_ref FROM genre__title WHERE genre_ref = 3) AND id IN (SELECT title_ref FROM artist_album_artist__title WHERE artist_album_artist_ref = 366) AND id IN (SELECT title_ref FROM album__title WHERE album_ref = 236) ORDER BY track ASC
Ono je to z toho celkem vidět, ale vybírám skladby podle žánru, autora a alba. Které skladby to splňují vyhledávám přes asociativní tabulky. Výsledkem jsou pouze 2 skladby, přesto dotaz zabere na pomalejším počítači až desetiny sekund. Nerozumím tomu proč. Očekával bych, že databáze nejprve vyfiltruje vše podle první podmínky, kdy odpadne přes 90% záznamů a potom už porovnává jenom malý množství řádek a další filtry budou mnohem rychlejší. Jenže podle mých pokusů to tak nefunguje. Kde může být problém?