Obecné praktiky práce s DB

1. Obecné praktiky práce s DB Lubomír Andrle Lubomir.andrle@unicorn.eu

2. Osnova • Datová vrstva z pohledu programátora • Typické úlohy a problémy • SQL tipy • Integrace pomocí DB

3. Přehled vrstev systému

4. Datová vrstva • Služby pro ukládání a načítání dat, dotazování • Většinou reprezentovaná relačním databázovým systémem • Oracle • MS SQL • MySQL • … • Typické objekty • Tabulky • Pohledy • Uložené procedury • Časté využití O-R mapování pro přístup k datům z business vrstvy • Mapování objektové struktury aplikace na relační strukturu databáze • Objektovédatabáze

5. Datová vrstva v N-vrstvé aplikaci I

6. Datová vrstva v N-vrstvé aplikaci II • Zajišťuje persistenci dat • Zprostředkovává jednotný přístup k datům • Typově jde obvykle o OLTP databázi rozšířenou o dávkové zpracování • Nejčastější implementace • Relační databáze • Soubory • Objektové databáze

7. Přístup k datům I. • Probíhá pomocí dotazovacích jazyků • SQL (Standard QueryLanguage) pro relační databáze • OQL (ObjectQueryLanguage) pro objektové databáze • Nativním prostředky dané platformy (např. COM objekty) • SQL • Standardizováno ANSI normami • Neustále se vyvíjí • Jednotlivé relační databáze • Nepokrývají standardy na 100% • Obsahují specifická rozšíření • Implementace jednotlivých elementů (např. transakce) nemusí být shodná

8. Přístup k datům II. • Standardizovaná rozhraní (ovladače) • Definují API mezi obchodní logikou a databází • Unifikují jednotlivá API databázových systémů • Případně softwarově simulují chybějící funkce

9. Přístup k datům III. • Nejběžnější API • ODBC (standard C) • JDBC (Java) • ADO (.NET) • OLE DB • Microsoft specifické řešení postavené na COM objektech • Podporuje i nerelační databáze (např. MS Excel) • Dosud podporováno zejména v MS SQL

10. Typické úlohy a problémy

11. Konkurenční přístup k datům I. • Změny prováděné v databázi probíhají v transakcích • Tyto transakce splňují tzv. ACID pravidla • Atomic • Consistent • Isolation • Durability • Přístup k datům je interně řízen pomocí mechanismu zámků • Implementace těchto pravidel v jednotlivých databázích není 100% shodné

12. Konkurenční přístup k datům II. • Malé srovnání Oracle a MS SQL

13. Optimalizace výkonu - indexy • Nástroj pro optimalizaci rychlosti přístupu k datům • Databáze obvykle automaticky vytvoří index • Pod primárním klíčem • Pod alternativním klíčem • Indexy pod cizími klíči • Vhodné téměř ve všech případech, kromě odkazů na číselníkové tabulky o několika málo záznamech • Je nutné definovat manuálně • Databáze nabízejí specializované typy indexů kromě standardního B-Tree • např. clustered, bitmap, full-text

14. Historická data • Jak řešit historická data v tabulkách? • Je nutné začít již u datového modelu • Historické záznamy musí být identifikovatelné (např. datum vytvoření) • Musí být možné udělat konzistentní časový řez daty • Možná řešení • Pravidelný přesun do historických tabulek • Nastavením historizačního flagu (vhodné u menších tabulek) • „Historické“ záznamy lze skrýt před BL pomocí pohledů • Implementace partitioningu • Pomůže eliminovat potřebu historických tabulek • Nezachrání chybný návrh, stále je potřeba „partitionkey“ • Oracle nyní podporuje automatické vytváření partition

15. Sql tipy

16. Databázové triggery • Kdy ano? • DB scope • Pro vytváření speciálních auditních záznamů (autonomními transakcemi) • Pro účely komplexní validace • Vylepšený check • Proč jinde ne? • Skryté před vývojáři BL • Štěpí BL do více vrstev • Obtížně debugovatelné

17. Funkce ve „where“ klauzuli SQL • Obecně jde o „špatný nápad“ • Nepředvídatelný počet provedení (závisí na prováděcím plánu). • Performance dopad • Může vést na skryté implicitní konverze datových typů • V N-vrstvé architektuře lze funkci obvykle vyhodnotit v BL • Pokud přeci jen není vyhnutí • Funkce by měla být deterministická • Neprovádí změny stavu db

18. Výčty v IN klauzuly dotazu • Jak řešit dlouhé výčty hodnot v IN operátoru? • Příklad: SelectkódUCfromtPrávawhere role in (role1, role2, role3, …, roleN) • Nevýhody • Snížený výkon (každá varianta dotazu je originál) • Vede na velmi podobné dotazy (a tím pádem i hash kódy do SQL cache) • Dotaz je nutné pokaždé dynamicky skládat • Možná řešení • Batch operace • Oracle podporuje předávání seznamů (polí) • Použití vnořeného dotazu může být efektivnějšíSelectkódUCfromtPrávawhere role in (select role fromtPřiřazeníRolíwhere uživatel=uživ1)

19. Načtení hodnoty atributu do proměnné • Oracle • Příkaz: selectattrinto var from table wherepodmínky • Podmínce vyhovuje 0 záznamů: výjimka NO_DATA_FOUND • Podmínce vyhovuje více než 1 záznam: výjimka TOO_MANY_ROWS • MS SQL • Příkaz: select @var = attrfrom table wherepodmínky • Podmínce vyhovuje 0 záznamů: vrátí hodnotu NULL • Podmínce vyhovuje více než 1 záznam: vrátí hodnotu posledního záznamu! • Počet nalezených záznamů nutné vyhodnocovat přes proměnnou @@ROWCOUNT ihned po provedení SQL dotazu. • Jde o naprosto odlišné chování při stejné operaci!!!

20. SQL dotazy – filtrování a vyhledávání • Co je špatně na tomto kódu?// načte hlášky z loguDataSetds = execute(„select * from LOG_TABLE“) // vyfiltruje jen chybové hlášky return extractErrors(ds); • Filtrujte a vyhledávejte výhradně na datové vrstvě • Ulehčíte paměti i procesoru

21. Taky nezapomeňte … • Řazení dat pomocí „order by“ je ovlivněno regionálním nastavením databáze a session, která operaci provádí • Oracle podporuje operátor „connect by“ pro procházení stromové struktury • Dávkové zpracování v jedné transakci může trvat dlouho • V Oracle pouze držíte zámky (celkem levné) • V MS SQL držíte serverové zdroje, může dojít k eskalaci zámků a deadlockům • Dynamický datový model (vytváření tabulek online) může být možným řešením, ale nejspíš narazíte na odpor DBA

22. Integrace pomocí DB

23. Integrace na databázové úrovni • Základní principy • Oddělení konzumenta od interních datových struktur • Každý konzument má obvykle svoje vlastní db objekty tvořící rozhraní • Rozhraní používají pokud možno pouze základní datové typy • Rozhraní konzument by mělo být snadno odpojitelné • Možné implementace • Pomocí databázových pohledů / snapshotů • S využitím speciálních tabulek • Za pomoci nativních prostředků databáze (např. Oracle Streams) • Nutné vyřešit synchronizace přístupů k rozhraní • Čtení vs zápis (obvykle nebude transakční) • Lze implementovat specializovanou řídící tabulkou

24. Sdílená databáze • Základní princip - přístup do „cizí“ DB pro data • Přístup přes DB link (A) nebo dedikovaný data source (B) • Dávková i online integrace • Synchronní i asynchronní

25. Integrace na DB úrovni - příklad

26. Sdílená databáze – „online“ integrace • „Online“ integrace – přístup na živá data • Volání uložené procedury/funkce • Přístup do „online“ tabulek • Na co si dát pozor • Nutná definice formálního rozhraní pro integraci • Menší závislost na změnách vnitřních tabulek systému • DB zámky na živých datech mohou blokovat online zpracování • Řízení zátěže • Jemné omezení: povoleny pouze konkrétní operace (přístup přes DB funkci) • Hrubé omezení: profily,... • Často „quickhack“ integrace • Nevyžaduje žádné složité budování infrastruktury • Problematický pattern z pohledu mj. škálovatelnosti a údržby … mnohde zakázané

27. Sdílená databáze – „offline“ integrace • „Offline“ integrace • Data jsou připravena v dedikovaných tabulkách • Přenos dat (celé nebo části) do cílového systému nebo pouze dotazování nad daty • Použití • Reporting - přímá podpora v reportovacích nástrojích (Cognos) • Přenos dat do centralizovaného úložiště (ODS) • Na co si dát pozor • Stejné jako u online integrace • Zamykání není většinou tak kritické

28. Q & A