Zápis do databáze máme ošetřený. Ale data jsou uložená v navzájem provázaných tabulkách, v nichž obyčejný uživatel nedokáže rozeznat žádnou užitečnou informaci. Dnes tedy zpřístupníme data i těmto obyčejným uživatelům a tím naše povídání zakončíme.
Obyčejným uživatelem můžete chápat třeba ředitele, který chce mít ve svém PC na stole vždy aktuální hlavní knihu a nic jiného než MS Excel nezvládá. Proto je dobré vytvořit v databázi několik dalších pohledů na data.
Nejzajímavější pro vás bude asi hlavní kniha. Vytvoříme proto view, který doplní k účtům v hlavní knize popis a vypočítá zůstatek. Zde se uplatní konečně i položka „ei_osnova.aktivni“ – opticky upraví zůstatky u účtů tam, kde by jinak vycházely záporně (u pasivních účtů). Takový pohled na hlavní knihu můžete dát směle k dispozici managementu přes ODBC a zároveň máte pojistku, že změníte-li view v databázi, nemusíte zjišťovat, kde všude v aplikacích a uživatelských dotazech (zase přes ODBC v MS Excelu) se přístup do hlavní knihy používá.
create view ei_hlkniha_vw as select ei_osnova.ucet, ei_osnova.anal, ei_osnova.popis, ei_hlkniha.poc_zustatek_md, ei_hlkniha.poc_zustatek_dal, ei_hlkniha.zustatek_md, ei_hlkniha.zustatek_dal, (ei_hlkniha.poc_zustatek_md - ei_hlkniha.poc_zustatek_dal + ei_hlkniha.zustatek_md - ei_hlkniha.zustatek_dal) * ei_osnova.aktivni as zustatek from ei_osnova, ei_hlkniha where ei_osnova.ucet=ei_hlkniha.ucet and ei_osnova.anal=ei_hlkniha.anal;
Další hodně frekventovaný je pohled do deníku. Informace v našich tabulkách jsou hodně rozházené – každá strana účtu má v deníku vlastní záznam. Jednodušší je proto přístup do deníku pomocí view. Předpokládáme-li, že v deníku je na každé straně účtu pouze jeden záznam, je view hodně jednoduché:
create view ei_denik_vw
select distinct on (dp.cislo)
dp.cislo, dm.datum, dp.popis, dm.castka,
dm.ucet as ucetm, dm.anal as analm,
om.popis as popism, dm.doklad as dokladm,
dd.ucet as ucetd, dd.anal as anald,
od.popis as popisd, dd.doklad as dokladd
from ei_popis dp, ei_denik dm, ei_denik dd,
ei_osnova om, ei_osnova od
where dp.cislo = dm.cislo
and dp.cislo = dd.cislo
and dm.ucet=om.ucet and dm.anal=om.anal
and dd.ucet=od.ucet and dd.anal=od.anal;
Mnohem nepříjemnější situace nastane, pokud počítáme s tím, že na každé straně transakce může být obecný počet řádků. Zatímco s výše uvedeným view jsem se příliš netrápil, zobecnění mě stálo několik subselectů a neobešlo se bez podmíněných výrazů:
create view ei_denik_vw2 as
select po.cislo, po.popis,
sm.pocet as pocetm,
case when sm.pocet>1 then null else dm.ucet end as ucetm,
case when sm.pocet>1 then null else dm.anal end as analm,
case when sm.pocet>1 then null else om.popis end as popism,
case when sd.pocet>1 then null else dd.doklad end as dokladm,
sd.pocet as pocetd,
case when sd.pocet>1 then null else dd.ucet end as ucetd,
case when sd.pocet>1 then null else dd.anal end as anald,
case when sm.pocet>1 then null else od.popis end as popisd,
case when sd.pocet>1 then null else dd.doklad end as dokladd,
case when sm.castka!=sd.castka then 'NaN'::float8
else sm.castka end as castka
from ei_popis po
left join (select cislo,
count(*) as pocet,
sum(castka) as castka
from ei_denik
where mdd='M'
group by cislo) as sm using (cislo)
left join (select cislo,
count(*) as pocet,
sum(castka) as castka
from ei_denik
where mdd='D'
group by cislo) as sd using (cislo)
left join (select distinct on (cislo)
cislo, ucet, anal, doklad
from ei_denik
where mdd='M') as dm using (cislo)
left join (select distinct on (cislo)
cislo, ucet, anal, doklad
from ei_denik
where mdd='D') as dd using (cislo)
left join ei_osnova om on (om.ucet=dm.ucet and om.anal=dm.anal)
left join ei_osnova od on (od.ucet=dd.ucet and od.anal=dd.anal)
;
Proti jednoduššímu pohledu na deník má toto view navíc dva sloupce, ve kterých je uvedený počet záznamů na příslušné straně účetního případu. Pokud je na každé straně pouze jeden záznam, jsou uvedeny i ostatní údaje (syntetická a analytická část účtu a další). Má-li účetní případ více řádků, je na příslušné straně uveden počet záznamů a vícenásobné (z principu různé) hodnoty mají hodnotu null – pro aplikačního programátora neklamný příznak, že transakce byla složitejší a je třeba ji vypsat jinak.
Zároveň se toto view dá použít pro zběžnou kontrolu dat – v databázi zdaleka nejsou ošetřené všechny potenciální problémy. Částka musí být na obou stranách (má dáti a dal) stejná. Doufejme, že chyba nikdy nenastane, ale kdyby přece, ve view bude v takovém řádku místo částky hodnota-nehodnota ‚NaN‘ – Not a Number – česky nečíslo.
Protože view už je dost komplikované, bylo by dobré se zamyslet nad jednoduchou optimalizací dat (jistě by šlo napsat lépe i samotný dotaz). Hlavní brzdou ve výkonu databáze bývají sekvenční přístupy. Příkazem explain zjistíte, že sekvenčně se prohledávají hned dvě tabulky, tabulka ei_denik dokonce čtyřikrát. Hned na začátku se prohledává tabulka ei_popis. Zajímají-li nás všechny věty, nedá se tabulka ani jinak prohledávat. Zkuste vložit do databáze dostatečný počet záznamů (desettisíckrát si zopakujte insert do tabulky ei_zapis) – plánovač v PostgreSQL potřebuje dostatečný počet vět, aby uznal, že režie s indexovým vyhledáváním je menší než režie sekvenčního přístupu. A potom můžete položit dotaz znovu, tentokrát ale s omezením (…where cislo=666). Sekvenční prohledávání tabulky ei_popis zmizelo (nad tabulkou je vytvořený jedinečný index podle položky cislo), ale zůstalo nám sekvenční prohledávání deníku. Zkusíme tedy vytvořit index:
create index ei_denik_index_cislo on ei_denik(cislo);
Ale po vytvoření indexu zažijeme drobné zklamání – dotaz stále prohledává databázi sekvenčně a index se nepoužívá. (Pro mě to byl šok. Hodinu tady vymýšlím krkolomné konstrukce a ověřuji, jestli nepíšu bludy, a teď taková zrada!) Těšit nás může alespoň zrychlení zápisu – jestli si pamatujete, při update tabulky ei_poradi se prohledává deník a kontrolují se částky na straně má dáti a dal. Tady index zabere. Proč nezabere v našem view? Problém je v omezení podle položky mdd. Naštěstí lze index vytvořit jen nad částí tabulky:
create index ei_denik_index_cislo_m on ei_denik(cislo)
where mdd='M';
create index ei_denik_index_cislo_d on ei_denik(cislo)
where mdd='D';
Výpis příkazu explain nás tentokrát potěší. Přesně tohle jsme chtěli. Při několika tisícovkách vět pochopitelně žádné zrychlení nepoznáte, ale jakmile se tabulky rozrostou na stovky tisíc vět, bude zrychlení velmi výrazné.
Vytvoření pohledu na hlavní knihu
Vytvoření pohledu na deník
Co říci na závěr? Cílem seriálu nebylo naprogramovat nic, co by byť i jen vzdáleně připomínalo hotovou aplikaci. Pouze jsme si ukázali, co je potřeba v podvojném účetnictví sledovat, jakým způsobem uložit data a jak údaje zabezpečit alespoň proti těm nejobvyklejším chybám. Zároveň jsme si díky funkcím v PostgreSQL zjednodušili přístup k datům, jak jen to šlo. Doufám, že v praxi, kdyby jinak museli podesáté přepisovat deset modulů v aplikaci třeba kvůli novým tabulkám a kvůli vazbám na ně, by takový návrh (a neříkám, že by nešel ještě vylepšit) ocenili i ti, kteří tvrdí, že podobnou funkčnost by dokázali naprogramovat v aplikaci taky.
Jakékoliv účetnictví pochopitelně není pouze deník a hlavní kniha. Ke skutečnému účetnictví se váže množství dalších pomocných evidencí (faktury, sklady a podobně). Postupy, naznačené v našem seriálu, mohou být použity i dále – například při vytvoření faktury a uložení do tabulky faktur se triggerem automaticky vyvolá funkce pro zaúčtování faktury na příslušné účty.
ČLÁNKY DO MAILU