Kód v databázi RNDr. Ondřej Zýka
1
Obsah • • • • • • • •
Jazyk Skripty Funkce Procedury Triggery Správa chyb Java, CLR, apod.. Dobré a špatné praktiky 2
Jazyk • PL/SQL • • • • •
Oracle Inspirovaný jazykem ADA „Objektový“ … Samostatné jednotky pro PL/SQL a SQL Typy vlastní jenom PL/SQL
• Transact SQL • Microsoft, Sybase • Rozšíření SQL 3
PL/SQL - příklad declare l_au_id author.au_id%type; l_fullname varchar2(300); begin l_au_id := '8915'; select L_NAME||','||F_NAME into l_fullname from author where AU_ID = l_au_id; DBMS_OUTPUT.PUT_LINE(l_fullname); end; / 4
PL/SQL - Příklad • • • • • • •
Deklarace před blokem Definice typu podle struktury databáze Středník za příkazy Oddělený select Výstup jako dataset neexistuje Použití textového výstupu / za příkazem
5
PL/SQL příklad DBMS_OUTPUT.PUT_LINE('výstup');
begin DBMS_OUTPUT.PUT_LINE('výstup'); end; / call DBMS_OUTPUT.PUT_LINE('výstup'); 6
Transact SQL - příklad declare @l_au_id int set @l_au_id = 8915 select @l_au_id = 8915 select L_NAME + ',' + F_NAME from author where au_id = @l_au_id;
• • • •
Deklarace kdekoliv Přísnější kontrola typů Výstup společně s kódem Středníky jsou nepovinné 7
Skripty • Základní artefakty dodávek • Problematické oblasti • • • •
Dokumentovatelnost Posloupnost nahrávání Zjištění existence objektu Nastavení výstupu • Proměnné • Programovací jazyk skriptů •&
8
Skripty • Autor, verze, historie změn, popis • Skript x Batch x Statement • Opakovatelnost spuštění • Strategie drop x create, alter • Strategie create or replace
• Jak zapsat do databáze, že skript byl nahrán (v jaké verzi)
9
Práce s katalogem • Informace o existenci objektů • Oracle • select * from USER_TABLES • USER_TABLES • ALL_TABLES • DBA_TABLES
• Microsoft • select * from dbo.sysobjects • select * from INFORMATION_SCHEMA.TABLES
10
Opakovatelnost declare l_count int; begin select count(*) into l_count from USER_TAB_COLUMNS where TABLE_NAME = 'T_PROFILE' and COLUMN_NAME = 'PARK_ID'; if l_count = 0 then execute immediate 'alter table T_PROFILE add PARK_ID VARCHAR2(255)'; DBMS_OUTPUT.PUT_LINE('Column T_PROFILE.PARK_ID added.'); else DBMS_OUTPUT.PUT_LINE('The column T_PROFILE.PARK_ID exists.'); end if; /
11
Opakovatelnost if not exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='author' and COLUMN_NAME='note' ) begin alter table author add note varchar(255) print 'Column note to table author added...' end else print 'Column note into table author exists...' 12
Funkce • Skalární funkce • Vrací jednu hodnotu
• Dataset funkce • Vrací dataset (tabulku)
• Použití v select klauzuli • Použití ve where klauzuli
13
Funkce • Nezávislé na datech (deterministické) create function weekago return timestamp as begin return systimestamp - 7; end weekago;
14
Funkce • Závislé na datech (nedeterministické) • Velice náchylné k psaní neefektivního kódu create function avg_sale return NUMBER as ret_val number; begin select avg(sale_qty) into ret_val from (select sum(qty) as sale_qty from sales_detail group by store_id,ord_num) aux_table; return ret_val; end avg_sale; / select * from (select store_id,ord_num,sum(qty) as sale_qty from sales_detail group by store_id,ord_num) aux_table where sale_qty < avg_sale
15
Procedury • Package x jmenné konvence • Podpora ladění • • • •
Debug parametr Ladící výpisy Design Logování průběhu – nutno logovat i data
• Systémové procedury • Vytváření objektů v procedurách
16
Procedury • Parametry procedur • Návratové hodnoty • Parametry • Error code • Dataset
• Verze procedur v serveru • Invalidní stav procedur • Procedury psané v jiném jazyce (CRL, Java, …) 17
DML Triggery • V okamžiku DML příkazu • Použití • Údržba denormalizovaných údajů • Správa statistiky • Počítané hodnoty
• • • • •
Zabránění chybným transakcím Logování změn – auditing, Vynucení složitých autorizačních schémat Vynucení referenční integrity Vynucení složitých business pravidel 18
DML Triggery • Typy • • • • •
Befor After Statement Rows Instead of
• Problematické oblasti • Zřetězení triggerů • Přístup k datům • Řízení transakcí 19
Triggery Oracle • Pořadí • • • •
Before statement Before row After row After statement
• Přístup k datům pouze row trigerrech create trigger triu_publisher after insert or update on publisher for each row begin DBMS_output.put_line('Stare jmeno: '|| :OLD.NAME|| ',Nove jmeno: '|| :NEW.NAME); end; 20
Triggery Oracle • Transakce • Rollback, commit nelze použít
• Jak zapsat počet updatovaných řádků? • Možno více trigerrů stejného typu a definovat pořadí • Mutating Triggers – trigger chce použít data, která se měnila
21
Triggery Microsoft • Pouze after trigger • Vždy přístup k datům pomocí tabulek inserted, deleted CREATE TRIGGER triu_publisher ON publisher AFTER INSERT,UPDATE AS BEGIN select inserted.name, deleted.name from inserted left join deleted on inserted.pub_id = deleted.pub_id END 22
Triggery Microsoft • Možnost řídit transakce v triggerech (rollback) • Pouze after triggery • Neexistuje problém Mutating Triggers • Možnost zacyklení – trigger se podruhé nevolá
23
Triggery na úrovni databáze • Schopnost reagovat na události v databázi • • • • •
Přihlášení uživatele Vytvoření/úprava objektu Přidání práv Výskyt chyby …
• Velmi rozdílné v jednotlivých verzích • Využití spíše pro administraci než pro vývoj aplikací • Neprogramujte existující funkcionalitu 24
Triggery Shrnutí • Komplikovaná problematika s mnoha proprietárními specialitami • Efektivní využití vyžaduje podrobnou znalost možností daného serveru a konkrétní verze • Extrémně špatné možnosti ladění
25
Správa chyb • Požadavek na celkovou architekturu řešení • Kdo generuje jaké chyby, kdo na jaké chyby reaguje a jak • • • •
které chyby až do GUI které chyby do logu které chyby jsou překryté funkcionalitou gui které chyby jsou překryté funkcionalitou aplikace
• Jednotný přístup ke správě chyb v sql i aplikaci
26
Možné výsledky chyby v serveru • Chyby na úrovni • • • •
Hardware Software Aplikace Kódu
• Výsledky chyby • • • •
Kód je ukončen, transakce je ukončena Kód je ukončen, transakce pokračuje Kód pokračuje, transakce ukončena Kód pokračuje, transakce pokračuje 27
SQL server – možné chyby při vkládání řádku Error Duplicate primary key. NOT NULL violation. Violation of CHECK or FOREIGN KEY constraint.
Aborts Statement Statement Statement
Most conversion errors, for instance conversion of non-numeric string to a numeric BATCH value. Attempt to execute non-existing stored procedure.
Statement
Missing or superfluous parameter to stored procedure to a procedure with parameters.
Statement
Superfluous parameter to a parameterless stored procedure.
BATCH
Exceeding the maximum nesting-level of stored procedures, triggers and functions. BATCH Being selected as a deadlock victim. Permission denied to table or stored procedure.
BATCH Statement
ROLLBACK or COMMIT without any active transaction.
Statement
Mismatch in number of columns in INSERT-EXEC. Declaration of an existing cursor
BATCH Statement
Column mismatch between cursor declaration and FETCH statement.
Statement.
Running out of space for data file or transaction log.
BATCH 28
Java • Možnosti psát procedury v java • Možnost využívat funkcionality standardních java package • Možnost ukládání java objektů v databázi • Využití zejména pro práci s XML • Nutnost konverze datových typů • Neefektivní integrace s SQL enginem 29
CLR • Common Language Rutines • MS SQL server 2008 • Možnost psát procedury, triggery a funkce v .NET • Možnost definovat uživatelské typy na úrovni .NET • Integrace CLR funkcí do Transact SQL – agregační funkce • Podpora ve Visual Studiu 30
Dobré praktiky - návrh • Zapouzdření přístupu k datům • Není nutné zapouzdřovat vše • Není vhodné používat zapouzdření v procedurách
• Oddělení vlastníka schématu od uživatelů • Používání jmenných konvencí, standardů, guidelines • Definování templates pro standardní části kódu • Provádějte revize kódu, ptejte se, pracujte společně
31
Template SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.
ON <Schema_Name, sysname, Schema_Name>. AFTER AS -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here END GO 32
Template
Procedure template.sql
33
Dobré praktiky - kód • Podpora kompilátoru • • • •
Přímé předávání parametrů Konstanty jako proměnné Transakční uzávěr Přebrání rozhodování při selektech typu serch do kódu • Rozumná hierarchie view
• • • • •
Porovnávat/dosazovat vždy stejné typy Používání exists vždy, když to jde Set nocount on na začátku každé procedury Nezapomínat na hodnotu null Omezovat zbytečné testy pro řízení toku zpracování 34
Dobré praktiky - kód if @au_id < 7000 and @type = 'A' <do something> if @au_id = 7000 <do something> if @au_id > 7000 and @state = 'CA‘ <do something else>
if @au_id < 7000 and @type = 'A' <do something> else if @au_id = 7000 <do something> else if @au_id > 7000 and @state = 'CA‘ <do something else>
35
Dobré praktiky - kód if @au_id = 7000 <do something> else <do something else>
if @au_id is null <do something special> else if @au_id = 7000 <do something> else <do something else>
36
Špatné praktiky - kód • Ukládání ROWID pro pozdější reference • Použití sekvencí jako počítadla • Nahrazení null hodnotami jako „N/A“ nebo „Unknown“ • Použití select * from • Použití výrazů v join klausuli • Použití identifikátorů vyžadující uvozovky
37
Špatné praktiky - kód declare "a + b" varchar(30); begin "a + b" := 'a + b'; DBMS_OUTPUT.PUT_LINE ("a + b" || ' ' || 'a + b'); end; /
38
Dobré praktiky - SQL • • • •
Použití UNION ALL místo UNION Nepoužívat zbytečně SELECT DISTINCT Nepoužívat zbytečně ORDER BY Používat plně kvalifikované jména objektů
39
Dobré praktiky - výkon • • • • • • •
View zachovává datové typy Jen odůvodněné použití kurzorů Použití Bulk operací Jen odůvodněné použití smyček Neukládat prázdné BLOB hodnoty Rozumná implementace práce s transakcemi Používání pouze krátkých transakcí • Nikdy nepřipusťte komunikaci s uživatelem během transakce • Nikdy nepřipusťte komunikaci s aplikací během transakce
40
Dobré praktiky - výkon • Podpora výkonu • • • •
Sdružování příkazů select Sdružování deklarací Nepoužívat exception pro řízení toku zpracování Nepoužívat nedeterministiké funkce ve where klausuli • Používat optimalizator hints s rozvahou – vždy uveďte důvod
• Nechte si otestovat výkonnost kódu někým jiným (někde jinde) 41
Dobré praktiky - údržba • Minimalizovat hard-coding SQL • Zapouzdření do views • Údržba kódu x údržba schématu
• Nepoužívat zbytečně Hard-coding datových typů • Dokumentovat změny v kódu • Dokumentovat každý složitější SQL příkaz a konstrukci • Psát vícenásobně spustitelné skripty • Generovat kód pokud to je možné • Formátovat • Neexistuje dobrý standard • 5+4+1
42
Doporučená literatura
43
