Pomoc se strukturou databáze

[Vláďa J]

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.

[Reklama]

[Kit]

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, 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.

[Vláďa J]

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.)

[Kit]

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í.

[Nemo7]

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.

[Reklama]

[Logik]

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.

[Logik]

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...

[Kit]

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.

[Logik]

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šíš.
 
 

[Kit]

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.

[Logik]

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.

[Kit]

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.

[Logik]

- 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.

[Vláďa J]

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:



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?

[Logik]

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...