Pomoc se strukturou databáze

Pomoc se strukturou databáze
« kdy: 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.


Ivan

Re:Pomoc se strukturou databáze
« Odpověď #1 kdy: 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.

Kit

Re:Pomoc se strukturou databáze
« Odpověď #2 kdy: 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.

Pavouk106

  • *****
  • 2 358
    • Zobrazit profil
    • Můj blog
    • E-mail
Re:Pomoc se strukturou databáze
« Odpověď #3 kdy: 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...

Pavouk106

  • *****
  • 2 358
    • Zobrazit profil
    • Můj blog
    • E-mail
Re:Pomoc se strukturou databáze
« Odpověď #4 kdy: 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 ;-)


Kit

Re:Pomoc se strukturou databáze
« Odpověď #5 kdy: 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é.

Logik

  • *****
  • 993
    • Zobrazit profil
    • E-mail
Re:Pomoc se strukturou databáze
« Odpověď #6 kdy: 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š).

Kit

Re:Pomoc se strukturou databáze
« Odpověď #7 kdy: 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.

Logik

  • *****
  • 993
    • Zobrazit profil
    • E-mail
Re:Pomoc se strukturou databáze
« Odpověď #8 kdy: 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.

Kit

Re:Pomoc se strukturou databáze
« Odpověď #9 kdy: 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.

nou

Re:Pomoc se strukturou databáze
« Odpověď #10 kdy: 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.

Pavouk106

  • *****
  • 2 358
    • Zobrazit profil
    • Můj blog
    • E-mail
Re:Pomoc se strukturou databáze
« Odpověď #11 kdy: 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čí.

Nemo7

Re:Pomoc se strukturou databáze
« Odpověď #12 kdy: 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 ?  :)

Re:Pomoc se strukturou databáze
« Odpověď #13 kdy: 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.

Logik

  • *****
  • 993
    • Zobrazit profil
    • E-mail
Re:Pomoc se strukturou databáze
« Odpověď #14 kdy: 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ě...).