PL/SQL programátor už NEbloguje – vývoj aplikací pod databází Oracle

Musím říct, že jsem několikrát ve svém životě potřeboval z DB vygenerovat nějaký report a pokaždé když došlo na potřebu grafické reprezentace, snažil jsem se tomu zuby nehty vyhnout. Jenže obrázky jsou sexy, ne?  Takže můj cíl je nyní generovat obrázky z databáze. Zatím pouze obrázky a pouze *.BMP. Mě by se totiž mnohem více hodila komprimovaná videa, nicméně je třebas dekomponovat. Ostatně generování  *.BMP bude mít jednu krásnou vlastnost – bude možné vidět případný sub-výsledek až bych třebas psal JPEG komprimaci v PL/SQL. Mnohem rychlejší by bylo napsat to v Javě a javu si provolat či jí přímo loadnout do Oracle – funkční, rychlé, efektivní – ale nudné.

Vím o jednom problému, který mi čeká a o kterém ještě nemám úplnou představu jak ho řešit – je třeba do obrázku mimo kreslení psát, což znamená mít představu jako takové písmeno (“A”) bude v bitmapě vypadat a zatímco v Javě to stačí drawStringnout na Canvas (či Canvas2d) tady to bude vyžadovat mnohem více. Což třebas povede na nějaké čtení fontů(?) a loadování do DB a o to to bude zajímavějšejší, inu, uvidíme.

Takže hurá do hébičkování !

Bitmapa (jako BMP) vypadá jako soubor pixelů a nic víc. Nicméně i tak má BMP soubor nějakou hlavičku, kterou je třebas zapsat: Při čtení článku na wiki jsem dokonce zjistil, že BMP umožňuje Huffamanovo kódování a tedy bezztrátovou komprimaci v jisté omezené podobě, což jsem do dneška netušil.

http://en.wikipedia.org/wiki/File:BMPfileFormat.png

Což znamená, že bílý jednopixelový (1×1) obrázek nejsou 3 bajty (24 bitů – 8×3 barvy), ale 58 bajtů:

Pro pochopení jsem si vyzkoušel nějakou úpravu (modře) – konkrétně jsem z 01 přepsal pixely na 02 (2x 2x) a přidal 12 bajtů:

Fajn, to by zafungovalo, takže společně s obrázkem z wikipedie už je asi pomalu možné nějaké BMP začít tvořit.  Nejprve nastřelení nějakého minimálního kódu, že je to správný směr:

1) Založení adresáře, kam se budou cpát BMP soubory:
CREATE DIRECTORY output_bmp AS 'F:\bmp_output';

2) Úplně minimální kód na zkoušku:
declare
bmp_file utl_file.file_type;
un_used_1 RAW(4):=hextoraw('42')||hextoraw('4d');
begin
bmp_file:=utl_file.fopen('OUTPUT_BMP','test.bmp','wb');
utl_file.put_raw(bmp_file,un_used_1,true);
if utl_file.is_open(bmp_file) then
utl_file.fclose_all;
end if;
end;

Je to opravdu minimální kód, který do souboru test.bmp zapíše pouze 42 a 4D (hex), což odpovídá ascii textu ‘BM‘ a je to první část hlavičky bitmapového souboru. Což v hexaeditoru (použivám WinHex 17.0 a vřele doporučuji – umí přímý zápis na disk i do RAM), vypadá takto:

Fajn, to by bylo a nyní minimální kód, pro vygenerování nejjednoduššího BMP – hlavičku, jsem sprostě opráskl z wikipedie:

declare
/* uvodni BMP hlavička BMP */
static_magic_header RAW(2):=hextoraw('42')||hextoraw('4d');
/* velikost obrázku v B */
variable_image_size RAW(4):=hextoraw('46')||hextoraw('00')||hextoraw('00')||hextoraw('00');
/* aplication specific - takze se podepišu AZOR - AZ */
static_aplication_1 RAW(2):=hextoraw('41')||hextoraw('5A');
/* aplication specific - takze se podepišu AZOR - OR */
static_aplication_2 RAW(2):=hextoraw('4F')||hextoraw('52');
/* kde končí hlavička */
static_head_end RAW(4):=hextoraw('36')||hextoraw('00')||hextoraw('00')||hextoraw('00');
/* délka DIB hlavičky */
static_dib_size RAW(4):=hextoraw('28')||hextoraw('00')||hextoraw('00')||hextoraw('00');
/* sířka obrázku */
variable_image_width RAW(4):=hextoraw('02')||hextoraw('00')||hextoraw('00')||hextoraw('00');
/* výška obrázku */
variable_image_height RAW(4):=hextoraw('02')||hextoraw('00')||hextoraw('00')||hextoraw('00');
/* počet planes */
static_image_planes RAW(2):=hextoraw('01')||hextoraw('00');
/* počet barev - 24 bitová paleta */
static_image_bits RAW(2):=hextoraw('18')||hextoraw('00');
/* bi_rgb */
static_bi_rgb RAW(4):=hextoraw('00')||hextoraw('00')||hextoraw('00')||hextoraw('00');
/* velikost pixel array (dat) - a BEZ KOMPRSE*/
variable_raw_size RAW(4):=hextoraw('10')||hextoraw('00')||hextoraw('00')||hextoraw('00');
/* pixels/metters - horizontalne i vertikalne pouziju jednu promennou */
static_pix_metter RAW(4):=hextoraw('13')||hextoraw('0B')||hextoraw('00')||hextoraw('00');
/* nezbytné barvy - nulla */
static_colors RAW(4):=hextoraw('00')||hextoraw('00')||hextoraw('00')||hextoraw('00');
/* A KONEČNĚ JEDNOTLIVÉ PIXELY (prvně však separátor) */
static_pix_separator RAW(2):= hextoraw('00')||hextoraw('00');
data_pix_1_1 RAW(3):=hextoraw('00')||hextoraw('FF')||hextoraw('FF');
data_pix_1_2 RAW(3):=hextoraw('00')||hextoraw('00')||hextoraw('FF');
data_pix_2_1 RAW(3):=hextoraw('FF')||hextoraw('00')||hextoraw('FF');
data_pix_2_2 RAW(3):=hextoraw('FF')||hextoraw('00')||hextoraw('FF');
bmp_file utl_file.file_type;
begin
bmp_file:=utl_file.fopen('OUTPUT_BMP','test_2.bmp','wb');
/* postupné zapisování všech hlaviček */
utl_file.put_raw(bmp_file,static_magic_header,true);
utl_file.put_raw(bmp_file,variable_image_size,true);
utl_file.put_raw(bmp_file,static_aplication_1,true);
utl_file.put_raw(bmp_file,static_aplication_2,true);
utl_file.put_raw(bmp_file,static_head_end,true);
utl_file.put_raw(bmp_file,static_dib_size,true);
utl_file.put_raw(bmp_file,variable_image_width,true); -- šířka
utl_file.put_raw(bmp_file,variable_image_height,true); -- výška
utl_file.put_raw(bmp_file,static_image_planes,true);
utl_file.put_raw(bmp_file,static_image_bits,true);
utl_file.put_raw(bmp_file,static_bi_rgb,true);
utl_file.put_raw(bmp_file,variable_raw_size,true);
utl_file.put_raw(bmp_file,static_pix_metter,true);
utl_file.put_raw(bmp_file,static_pix_metter,true);
utl_file.put_raw(bmp_file,static_colors,true);
utl_file.put_raw(bmp_file,static_colors,true);
/* a konečně data */
utl_file.put_raw(bmp_file,data_pix_1_1,true);
utl_file.put_raw(bmp_file,data_pix_1_2,true);
/* separátor řádku */
utl_file.put_raw(bmp_file,static_pix_separator,true);
/* druhý řádek */
utl_file.put_raw(bmp_file,data_pix_2_1,true);
utl_file.put_raw(bmp_file,data_pix_2_2,true);
/* ukončení souboru */
utl_file.put_raw(bmp_file,static_pix_separator,true);
/* a zavřít soubor */
if utl_file.is_open(bmp_file) then
utl_file.fclose_all;
end if;
end;


A wohoho – 4pixelový výsledek uložený v souboru test_2.bmp vypadá po zvětšení takto :

Vygenerované BMP

Bohužel jsem změnil bezhlavě barvu a vypadla z toho růžová :/, zrovna taková hloupá barva. Kód rozhodně není hezký a v nějakém dalším díle ho upravím. Zatím jsem vytipoval části hlaviček, které nebudu měnit (prefix static_) a ty které se s velikostí obrázku budou muset měnit a přepočítávat (prefix variable_). Vygenerovaný obrázek vypadá pak v hexaeditoru:

První BMP v Oracle zobrazená v WinHex

Tak a to by bylo pro tento díl všechno V druhém to navrhuji vykrášlit a udělat nějakou funkci, která mi z BLOBu vrátí přímo hlavičku. A taky funkci, která vygeneruje prázdný obrázek. Ještě s tím bude spousta legrace.

Porovnat obsah dvou tabulek není nic těžkého – v té nejjednodušší podobě se udělá některá z množinových operací a výsledek je hotov. Podobně napsání scriptu na sychronizaci rozdílných tabulek není žádný challenge.  Druhá možnost jak zjistit jestli data v tabulkách jsou stejná je použít nativní funkcionalitu Oracle a package s názvem DBMS_COMAPRSION. S tímto package míří Oracle na distribuované databáze a primárním účlem je porovnávat tabulky z rozdílných databází (pochopitelně větší výkon než nejhloupější script s množinvou operací “mínus”). Pokud se však parametr databázového linku nevyplní (tzn. NULL) či se vyplní loopbackovým databázovým linkem – lze porovnávat dva objekty v téže databázi. Objekty proto, že to nezbytně nutně nemusí být tabulka, DBMS_COMPARSION porovnává:

• Tabulky
• Single-table pohledy
• Materializované pohledy
• A synonyma pro předchozí objekty, tedy tabulky, single-table pohledy a materializované pohledy.

Naopak existují také omezení, mimo logických jakým je například rozdílně nastavené NLS na lokální a cílové databázi a sloupce s informací o TIME_ZONE, jsou to také restrikce technologické, především pro porovnávané tabulky které obsahují sloupce typu: ROWID, LOBy, LONG, LONG RAW, BFILE, uživatelsky definované typy a další složité typy (XMLType apod.) Nimcméně jako při vytváření ERROR LOG tabulky je možnost přeskočit “unsupported columns” a tedy porovnat objekty s tím, že nepodporované sloupce jsou omitnuty.

Dále vždy důležité warnings: Není to transakčně konzistentní na databázích po linku – tedy pokud se po scanu první tabulky změní data, výsledek nemusí mít to co bychom čekali. Primárně je funkcionalita určena na Oracle Stremas, například na odkontrolvání složitějšího APPLY.

K čemu je to dobré, jak se to dá použít i nějaké ty konstrainy a warningy tu jsou a nyní k tomu nejzábavnějšímu. Nejprve tedy nějakou tabulku na test – zdrojová:

CREATE TABLE SOURCE_TBL (
ID NUMBER PRIMARY KEY,
ENERGY_DRINK VARCHAR(20)
);

insert into SOURCE_TBL values (1,‘Monster Energy’);
insert into SOURCE_TBL values (2,‘Rockastar’);
insert into SOURCE_TBL values (3,‘Red bull’);
insert into SOURCE_TBL values (4,‘Shock’);
commit;

A tabulka se kterou ji budeme zakládat (struktura stejná, odlišná data)

CREATE TABLE TARGET_TBL (
ID NUMBER PRIMARY KEY,
ENERGY_DRINK VARCHAR(20)
);

insert into TARGET_TBL values (1,‘Monster Energy – Ripper’);
insert into TARGET_TBL values (2,‘Rockastar – Xdurance’);
insert into TARGET_TBL values (4,‘Shock’);
insert into TARGET_TBL values (5,‘Playboy Energy Drink’);
commit;

Nyní je třeba založit objekt “COMPARSION”, podobně jako se u advisoru zakládají tasky apod. COMPARSION se vytváří pomocí procedury DBMS_COMPARSION.CREATE_COMPARSION, tedy:

BEGIN
DBMS_COMPARISON.CREATE_COMPARISON(
comparison_name=>'COMPARSION_ENERGY_DINKS',
schema_name =>'HAF',
object_name => 'SOURCE_TBL',
dblink_name =>null,
index_schema_name=>null,
index_name =>null,
remote_schema_name =>'HAF',
remote_object_name =>'TARGET_TBL',
comparison_mode =>DBMS_COMPARISON.CMP_COMPARE_MODE_OBJECT,
column_list =>'*',
scan_mode =>DBMS_COMPARISON.CMP_SCAN_MODE_FULL,
scan_percent =>null,
null_value =>DBMS_COMPARISON.CMP_NULL_VALUE_DEF,
local_converge_tag => null,
remote_converge_tag =>null,
max_num_buckets =>2,
min_rows_in_bucket =>2);
END;

První spuštení procedury v Oracle patrně potrvá poněkud déle (až minuty), databáze potřebuje založit pohledy a objekty, které v DB, které nejsou založeny by default po instalaci. A nyní k parametrům:

• comparison_name – nic překvapivého VARCHAR2, jméno comparsion
• schema_name,object_name,dblink_name,index_schema_name,index_name,remote_schema_name a remote_object_name asi není třeba kommentovat
• comparison_mode – konstanta DBMS_COMPARISON.CMP_SCAN_MODE_FULL  parametr má možnou jedinou tuto hodnotu, nové mody mohou být přidány v budoucnu zní poznámka
• column_list - sloupce na kterých se bude porovnávat, zástupný znak * pro všechny
• scan_mode – CMP_SCAN_MODE_FULL, CMP_SCAN_MODE_RANDOM, CMP_SCAN_MODE_CYCLIC nebo CMP_SCAN_MODE_CUSTOM
• scan_percent – není třeba porovnávat celou tabulku, lze například cyklicky či náhodně a nějaké procento
• null_value - pokud je uvedeno, cílový sloupec s tout hodnotou “odpovídá” hodnotě null. Hodnota nahrazující null
• local_converge_tag  a remote_converge_tag - pro Oracle Streams
• max_num_buckets – porovnává se po částech
• max_rows_in_bucket – maximální velkost části

Po spuštění se vytvoří COMPARSION s názvem : COMPARSION_ENERGY_DRINKS, lze si prohlédnout v pohledu USER_COMPARSIONS, kde není nic jiného než založený záznam pro každé COMPARSION a jeho parametry a sloupec  LAST_UPDATE_TIME typu timpestamp, jehož název mluví za vše.

Dále je tu pohled USER_COMPARSION_COLUMNS ve kterém jsou uvedeny sloupce ke každému COMPARSION a jestli je na nich INDEX.

A nyní zbývá spustit proceduru DBMS_COMPARSION.COMPARE k vlastnímu porovnání tabulek SOURCE_TBL a TARGET_TBL.

DECLARE
t_info DBMS_COMPARISON.comparison_type;
res BOOLEAN;
BEGIN
res:=DBMS_COMPARISON.COMPARE(
comparison_name=>'COMPARSION_ENERGY_DINKS',
scan_info =>t_info,
min_value =>NULL,
max_value =>NULL,
perform_row_dif=>TRUE);
dbms_output.put_line(case when res then 'TRUE' else 'FALSE' end);
dbms_output.put_line('loc_rows_merged '||t_info.scan_id);
dbms_output.put_line('loc_rows_merged '||t_info.loc_rows_merged);
dbms_output.put_line('rmt_rows_merged '||t_info.rmt_rows_merged);
dbms_output.put_line('loc_rows_deleted '||t_info.loc_rows_deleted);
dbms_output.put_line('rmt_rows_deleted '||t_info.rmt_rows_deleted);
END;

A výstup je následující:
FALSE
loc_rows_merged 1
loc_rows_merged 0
rmt_rows_merged 0
loc_rows_deleted 0
rmt_rows_deleted 0

False řiká, že tabulky nejsou stejné a výpis z typu comparison_type jaké rozdíly tam jsou. Oracle však reportuje v tomto typu pouze hodnoty dle bucketů, proto dle nastavení nemusí odpovidat (a typicky nebudou) počtu řádků.  Jaký je výsledek scanu (spuštěním COMPARE) lze vysledovat ve view USER_COMPARSION_SCAN:  select * from user_comparsion_scan:

Comparsion

Ještě detailnější pohled přímo na rozdílné sloupce poskytuje pohled USER_COMPARISON_SCAN_VALUE, select * from user_comparsion_scan_values:

Posledním zajímavým pohledem je USER_COMPARSION_ROW_DIF, který zobrazuje rozdílné řádky(šikovné samo o sobě, že?). select * from user_comparsion_row_dif;

Spuštěním COMPARE je hotový scan, nejsou však provedeny žádné DML změny nad tabulkama a data jsou taková jaká byla před scanem. Od promítnutí dat je tu procedura CONVERGE, tedy:

BEGIN
DBMS_COMPARISON.CONVERGE(
comparison_name=>'COMPARSION_ENERGY_DINKS',
scan_id=>1,
scan_info=> t_info,
converge_options => DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS,
perform_commit=>TRUE,
local_converge_tag =>NULL,
remote_converge_tag=>NULL);
dbms_output.put_line('loc_rows_merged '||t_info.scan_id);
dbms_output.put_line('loc_rows_merged '||t_info.loc_rows_merged);
dbms_output.put_line('rmt_rows_merged '||t_info.rmt_rows_merged);
dbms_output.put_line('loc_rows_deleted '||t_info.loc_rows_deleted);
dbms_output.put_line('rmt_rows_deleted '||t_info.rmt_rows_deleted);
END;


Výsledek:
loc_rows_merged 1
loc_rows_merged 0
rmt_rows_merged 3
loc_rows_deleted 0
rmt_rows_deleted 1

K parametrům DBMS_COMPARSION.CONVERGE

• comparsion_name – jméno COMPARSION
• scan_id – každý comparsion může mít více scannů, tedy scan_id podle kterého se bude “mergovat”
• scan_info OUT parametr s počty záznamů a jejich operacemi – viz výstup
• coverge_options – pokud jsou na primálním klíči rozdílné řádky v obou porovávaných tabulkách, tak která vyhraje, možné dvě hodnoty CMP_CONVERGE_LOCAL_WINS a CMP_CONVERGE_REMOTE_WINS
• perform_commit – název mluví za sebe, ovšem commit nezbytně nutně nemusí být jeden, v závislosti na nastavení (bulky)
• local_converge_tag,remote_converge_tag – pro Oracle Streams

A nyní ještě pohled na data:

Comparsion výsledek

 Paráda, ne?