DML – update -aktualizacja danych w bazie

Ręczna aktualizacja danych w bazie to najniebezpieczniejsza operacja w RDBMS moim zdaniem. Należy tą operację przeprowadzić z szczególną starannością. Wynika to z faktu iż podmieniamy najczęściej wartości na takie które spełniają zależności w bazie. Gdy napiszemy źle pytanie aktualizujące możemy zmodyfikować dużą ilość wierszy co będzie później trudne do wykrycia. Powinniśmy raczej trzymać się zasady iż nie updatetuje się pól kluczy.
Składnia polecenia jest podobna do polecenia insert

update tabela set kolumna = nowa_wartosc, kolumna_2 = nowa_wartosc;

Powyższe zapytanie zmodyfikuje nam wszystkie wiersze tabeli. Podobnie jak w przypadku delete i select możemy korzystać z warunków:

update tabela set kolumna=1 where kolumna_2 = 'jakas wartosc';

np:

update przyjaciele set wynagrodzenie = wynagrodzenie * 1,1 where plec = 1;

czyli każdy z naszych męskich przyjaciół dostał 10% podwyżki.
Tak więc podobnie jak w przypadku delete filtry możemy stosować bez ograniczeń.

Czasem zachodzi potrzeba aktualizacji danych na podstawie jakiegoś zapytania. Niestety implementacje nie są jednorodne. Najlepiej to wygląda na Oracle.

update przyjaciele p set wynagrodzenie = (select k.wynagrodzenie from przyjaciele_kopia k where k.id = p.id)

chcąc na Oraclu w ten sposób zaktualizować kilka kolumn za jednym zamachem:

update przyjaciele p set (wynagrodzenie,kolor) = (select k.wynagrodzenie,k.kolor from przyjaciele_kopia k where k.id = p.id)

Wygląda to dość podobnie do zapytania skorelowanego.
Jednak związany jest z tym mały żarcik:

update przyjaciele p set (wynagrodzenie,kolor) = (select k.wynagrodzenie,k.kolor from przyjaciele_kopia k where k.id = p.id) where p.kolor=0

aktualizacja wynagrodzenia dla przyjaciol koloru 0. Teraz załóżmy że tabela przyjaciele_kopia jest nie kompletna i zawiera tylko połowe przyjaciół z kolorem = 0. To co zrobi Oracle jest dość nie miłe, dla reszty przyjaciół koloru 0 przypisze null do wynagrodzenia. W zapytaniu głównym po za zapytaniem skorelowanym należy umieścić odpowiednie warunki. Działa to tak jakby w zapytaniu skorelowanym było ukryte wiązanie a w głównym filtr. Dla niedopasowanych wartości ustawiane są nulle.

W mssq taki update można przeprowadzić za pomocą inner join’a

UPDATE przyjaciele
SET kolor=10
FROM przyjaciele p
INNER JOIN przyjaciele_kopia k
ON p.id = k.id

Tutaj już szczęśliwie nie ma niespodzianek związanych z korelacją.

Teraz trochę o tym jak taka operacja się odbywa. Tak naprawdę update to delete połączony z insertem.
Więc możemy załapać się na triggery reagujące zarówno na insert i delete. Dlatego też aktualizacja wierszy powinna skupiać w sobie wiedzę odnośnie insertowania i usuwania danych. Nie mniej trzeba sprawdzić (w dokumentacji) jak reaguje dany RDBMS na aktualizacje.

Teraz mała kwestia optymalizacyjna:
zakładając iż mamy tabele 130k wierszy nie poindeksowaną:

update przyjaciele set wynagrodzenie = 0 where kolor is null or plec is null or wiek is null

lepiej zapisać jako:
update przyjaciele set wynagrodzenie = 0 where kolor is null;
update przyjaciele set wynagrodzenie = 0 where plec is null;
update przyjaciele set wynagrodzenie = 0 where wiek is null;

Sprawdziłem ostatnio to empirycznie. W pierwszym przypadku czas wykonania wzrósł ponad 10 krotnie w stosunku do kilku zapytań z jednym filtrem.

DML – insert, wstawianie danych

Aby wstawić dane do bazy należy skorzystać z następującej składni:

insert into tabela (nazwy_kolumn... ) values (wartosc);

Na chwilę obecną załóżmy że działamy na przykładowej tabeli przyjaciele i tylko my mamy do niej dostęp. Kwestia współdzielonego dostępu i konsekwencji transakcji zostaną omówione później. W tej części chciałbym się skupić na samej technice ‘wkładania danych’.

tak więc załóżmy że chcemy dodać przyjaciela do naszej tabeli.

insert into przyjaciele (imie,zarobki,plec,kolor) values ('janek','4500','1','5');

Jeśli pracujemy na bazach Mysql,Mssql to wystarczy by wstawić dane. W przypadku bazy Oracle należy wykonać commit;

Commit jest potwierdzeniem zmodyfikowania danych, jeśli coś nam się nie spodoba możemy użyć polecenia Rollback;Te dwa zagadnienia zostaną dokładnie omówione później.

Możemy dane wstawiać także za pomocą zapytań pobierających dane.

insert into przyjaciele (imie,zarobki,plec,kolor)
select 'janek','4500',1,5 from dual

dual jest tabelą oraclową, wyimaginowanym Tworem do zabaw związanych z pobieraniem danych nie z tabel.
W mssql i mysql nie ma tego tworu więc wystarczy:

insert into przyjaciele (imie,zarobki,plec,kolor)
select 'janek','4500',1,5;

można też z innej tabeli:

insert into przyjaciele (imie,zarobki,plec,kolor)
select imie,wynagrodzenie,plec,kolor from starz_przyjaciele;

We wstępie wspomniałem o konieczności wykorzystania kopii zapasowej. Jak najszybciej stworzyc kopie zapasowa tabeli z przyjaciółmi?

select * into kopia_przyjaciol from przyjaciele
na bazie mssql

lub na oracle:

create table kopia_przyjaciol as select * from przyjaciele;

W poprzednich rozdziałach korzystaliśmy z tabeli przyjaciele o takiej definicji:

id int, — unikalny id – pole numeryczne
imie varchar2(255), — pole tekstowe
plec int, — 0 dziewczyna 1 facet — wartosc numeryczna
wiek int, — wiek w latach — wartosc liczbowa
zarobki int, — zarobki w tysiacach — wartosc liczbowa
kolor int); — kolor skory 0 jasny 10 ciemny – skala — wartosc liczbowa

poprzednie zapytania pomijały część kolumn. Tak Jeśli wartość w kolumnie nie jest wymagana w pomienięciu kolumny w insercie powstanie wartość null. O wymagalności wartości w kolumnie napiszę w rozdziale o tworzeniu baz danych.

oczywiście w trakcie insertów możemy korzystać z funkcji podzapytań itd. Wszystkie zasady logiki z pobierania danych muszą zostać spełnione przy insertowaniu.

Istnieje też coś takiego jak wartość domyślna, u nas polem do takiego opisu może być kolumna id.
W większości baz jest to pole które zostanie automatycznie wypełnione wartością przy czym
W zależności od bazy możemy spotkać się z różnymi zachowaniami i nazwami:
Dla mssql słowem wytrych jest autoidentity – przypisanie w insercie wartosci do takiej kolumny zwraca błąd istnieje parametr dzięki któremu możemy chwilowo wyłączyć autoidentity i zainsertować dane. Jest to przydatne przy większych operacjach kopiowania fragmentów baz danych… Autoidentity jest raczej gap free, znaczy się nie powstają tam dziury w numeracji (do momentu usunięcia wiersza).

W mysql – słowem klucz jest Autoincrement. Tutaj w zależności od silnika jaki wykorzystujemy zachowania mogą być różne. Nie zajmuje się Mysql na codzień więc nie będę sie tutaj bawił w eksperta ale mogę odesłać do archiwum grup dyskusyjnych o bazach danych i o języku php słowa kluczowe to Innodb i MyISAM

W oracle sytuacja jest najbardziej zakręcona – Słowo klucz to sekwencja. Jednakże sekwencja musi być powiązana z triggerem(wyzwalaczem). Każdy wyzwalacz jest pisany indiwidualnie więc może się różnie zachowywać. Ja moje wyzwalacze piszę tak że podmieniają bezwzględnie wartość pola id na numer podany przez sekwencję. Omawiane twory nie są gap free (tworzą dziury w numeracji). Plusem jest ich uniwersalność i miła dostępność z poziomu kodu
do właściwości.

Kilka słów odnośnie wyzwalaczy które są już obecne w najpopularniejszych bazach (Mysql > 5.0,Mssql,Oracle).

Wyzwalacz -> Trigger – jest to kod który reaguje na operacje DML (zmiany danych). Triggery nie działają dla selectów). Głównie triggery się wykorzystuje do kontrolowania poprawności i integralności danych, Zapisywania nadpisanych danych oraz w celu rozszerzenia zabezpieczeń.

Triggery mogą w locie nadpisywać wartości które insertujemy, więc insertując np wynagrodzenie 4500
w bazie może zapisać się 2250.

Przed wykonaniem insertów powinniśmy mieć dobrze rozpoznaną bazę na której pracujemy.
Dość miła informacją jest fakt iż jeśli podamy coś źle w zapytaniu mamy nikłe szanse na destrukcje w pryzpadku inserta. Dobrze ustawiona baza nie pozwoli zapisać danych które są niespójne. Przy insertach mamy dość niską szansę na zepsucie czegokolwiek. Oczywiście do momentu jak nie zmieniamy ustawień constraintów czyli wiezów integralności.

Teraz kilka słów o transakcjach. Jest to generalnie temat rzeka, ale kilka podstawowych rzeczy należy wiedzieć. Niestety w każdej bazie jest to realizowane w nieco inny sposób ale poniżej opiszę główne założenia a w szczegóły zagłębię sie przy innej okazji.

Załóżmy że mamy dwóch użytkowników. którzy jednocześnie chcą uzyskać dostęp do danych. Wszystko jest ok
ale jeśli jeden z użytkowników zaczyna modyfikować dane co się dzieje? Wtedy w mniej lub bardziej świadomy sposób otwiera się transakcja. I w zależności od bazy albo użytkownik który chce tylko zapytać o dane dostanie dane niezaktualizowane lub dostanie czerwone światło i będzie musiał poczekać na zakończenie transakcji.

Wstawianie spójnych danych do wielu tabel powinno być świadomie wsadzone w jedną transakcję. O ile jedno zapytanie w większości przypadków tworzy spójną transakcję o tyle 2 zapytania już takiego tworu nie tworzą (przynajmniej w MSSQL i MYSQL). Tak więc insertując np fakturę jako nagłówek i pozycję koniecznie musimy zamknąć to w jedną transakcję(w przypadku gdy mamy gotowe dane). Kolejną sprawą jest minimalizowanie czasu trwania transakcji. Nie można dopuścić do sytuacji gdy transakcja zostaje otwarta i oczekuje na jakiś wybór użytkownika. W takim wypadku należy sprawę rozbić na bardziej atomowe transakcje i zaimplementować mechanizmy zachowujące spójność danych.

Modyfikacja danych w locie

Każdy kto pisze zapytania prędzej czy później stanie przed problemem zmiany jakiegoś typu danych na inny.
Flagowy przykład numer PESEL. W większości baz zapisany jest w polu varchar (jako tekst). Chociażby sortując po numerze PESEL chcąc uzyskać sortowanie po dacie urodzenia można się sfrustować bo pole tekstowe zostanie posortowane jako napis (1,11,12,2,21,3) . Na szczęście istnieje konstrukt cast

cast(kolumna as typ)

Dzięki tej funkcji w granicach rozsądku jesteśmy w stanie przerzucać typy danych. np

select cast(substrin(pesel,1,2) as int) from tabela
order by 1 desc

Niestety funkcja nie ma obslugi bledów
jesli podstawimy jakis napis:

select cast(substrin(‘tekst’,1,2) as int) from tabela
order by 1 desc

to się zapytanie wysypie w trakcie wykonywania.
Więc jeśli w całej bazie mamy choć jeden pesel z literką w pierwszych dwóch znakach to mamy tak zwana porażkę.

I teraz w zależności od tego z jakiej bazy akurat korzystamy mamy większe lub mniejsze pole do popisu.
W MSSQL mamy isnumeric -> dość przyjemna funkcja która ratuje honor tworu z Redmond. zwraca 1 jeśli argument jest liczba lub 0 jeśli nie. MSSQL udostępnia język T-sql w którym można definować własne funkcje i procedury. Jest tam opcja try i catch do obsługi wyjątków. Niestety Pan Bill nie przewidział możliwości obsługi wyjątków w funkcjach. Więc przy tego typu sprawach jesteśmy zdani na narzędzia wymyślone w Redmont.
Przykład wyjścia z opisywanego kłopotu na MSSQL:

select
case when isnumeric(PESEL)=1 then cast(substr(PESEL,1,2) as int) else 0 end rok from tabela

Na oralcu w standaradzie nie ma funkcji do sprawdzenia czy coś jest numerem. Plus dodatkowa trudność w zależności od ustawienia parametrów połączenia coś może być traktowane jako liczba bądź nie.
Chodzi o przecinki i kropki jako znak separacji dziesiętnej. Na szczęście obsługa wyjątków w Oracle po prostu działa więc można samemu zdefiniować funkcję w której kastujemy i w razie porażki robimy exception.

Definiowanie funkcji w PL/SQL || prawa wlasciciela i wykonawcy

Dziś trochę po za kursem.

Oracle ma dość specyficzne podejście do uprawnień, które objawia się tym iż nadając przywilej wykonywania procedur użytkownikowi procedura(funkcja) w domyśle jest wykonywana z uprawnieniami autora. Jednak to nieco friwolne tłumaczenie przemija swego rodzaju ‘i lub czasopisma’. Ta procedura jest wykonywana jakby ją wykonywał autor. O czy można brutalnie się przekonać w następujący sposób:

select * from username;

zwraca nam użytkownika na którym jesteśmy zalogowani.

napiszmy zatem taka funkcję:
create or replace function get_usrid
return int
is

Result int;
begin
select u.usr_id
into Result
from user_users
inner join usrnag u
on u.usr_login = username;

return(Result);
end get_usrid;

istnieje tam złączenie z jakąś tabelą z uzytkownikami, ale można równie dobrze zrobic select username into jakisvarchar.

wykonajmy funkcje

select get_usrid() from dual;

jest nasze id(login)

teraz udostępnijmy funkcje innemu userowi i niech on to wykona.
I też dostanie nasz id. Jest to dość irytujące bo nie będąc tego faktu świadomy napisałem ileś procedur które insertują właśnie w taki sposób id wykonującego operacje.

Oczywiście możemy skorzystać z AUTHID CURRENT_USER
Tylko wewnątrz procedur wykonywanej z prawami ownera i tak funkcja wykona sie z prawami uzytkownika będącego właścicielem procedury (innymi słowy to jest dziedziczne :-))

czyli wykonanie z uprawnieniami osoby wykonującej
co dla poniższej funkcji będzie korzystne:


create or replace function get_usrid
return int
AUTHID CURRENT_USER is

Result int;
begin
select u.usr_id
into Result
from user_users
inner join usrnag u
on u.usr_login = username;

return(Result);
end get_usrid;

Ale dla procedury już mniej. Założyłem w swojej aplikacji że nie daje dostępu do tabel tylko do procedur. No i właśnie napotkałem schody.
Chyba po prostu zrobię procedury z prawami osoby wykonującej gdzie będzie wykonywała się powyższa funkcja
która będzie jednocześnie parametrem z id użytkownika dla właściwej procedury.

Warto dodać iż pakiety mają globalnie przypisany sposób wykonywania (ownera lub wykonującego).
I tak właśnie stworzyłem pakiet z prawami invokera który wykonuje opisaną funkcje i insertuje do odpowiednich procedur argumenty.

Mały update:
Dzięki uprzejmości jednego z czytelników grupu dyskusyjnej o bazach danych
dowiedziałem się o :
sys_context(‘USERENV’,’SESSION_USER’)
Jeszcze nie przestowałem ale być może będzie to bardziej kulturalne rozwiązanie problemu;-)

Filtrowanie w zapytaniach grupujących

W poprzednim poście poruszony został wątek grupowania. Warto też pomyśleć o możliwościach filtrowania takich zapytań w celu prezentacji wyników pod użytkownika. Tak aby zapytanie wróciło tylko te wiersze, o które chodzi użytkownikowi.

Mamy kilka opcji do wyboru w zależności od oczekiwanego efektu. Postaram się najpierw logicznie opisać jak to działa i przedstawię proste przykłady.

Pierwszą możliwością filtrowania jest klauzula where, która jak w przypadku normalnego zapytania pozwoli nam na wycięcie pewnych wierszy z wyniku. Jednak należy być ostrożnym. W zapytaniu grupującym where jest filtrem, który działa przed agregacją danych. Jeśli chcemy filtrować wyniki po zgrupowaniu to w języku SQL istnieje jeszcze klauzula HAVING, jest to filtr dla agregatów.

Operując na tabeli przyjaciele z postu o podstawach grupowania możemy chcieć sprawdzić czy mamy wśród naszych przyjaciół co najmniej 10 osobowe grupy osób o tym samym imieniu:

select imie,count(imie) form przyjaciele
group by imie
having count(imie)>=10

Jeśli chcemy sprawdzić jak wyglądają zarobki najbogatszych naszych kolegów i koleżanek możemy przeprowadzić coś takiego:

select avg(zarobki) from przyjaciele
where zarobki > 10000
group by plec

w wyniku dostaniemy średnią zarobków większych od 10k z uwzględnieniem płci.

Możemy też sprawdzić czy imiennicy mogą zarabiać razem więcej:

select imie,avg(zarobki) from przyjaciele
where zarobki > 1000
group by imie
having count(imie) > 10

zapytanie najpierw odsieje osoby, które zarabiają mniej niż 10k, a później zliczy agregacje i odsieje worki z imionami, które zawierają mniej niż 11 przyjaciół.

Teraz trochę bardziej skomplikowany temat, otóż jak wyciągnąć przyjaciół, którzy zarabiają więcej niż średnia?
Można by spróbować:

select sum(zarobki) from przyjaciele
having zarobki > avg(zarobki)

ale to nam niestety nic nie zwróci (przynajmniej w oracle), pokaże się błąd agregacji.
Gdy zmienimy na:

select sum(zarobki) from przyjaciele
group by zarobki
having zarobki > avg(zarobki)

nie pokaże błędów, ale też nic nie zwróci.

Musimy niestety (lub stety) skorzystać z podzapytania:

select sum(zarobki) from przyjaciele
where zarobki > (select avg(zarobki) from przyjaciele)

ciekawostką jest wynik zapytania:

select sum(zarobki) from przyjaciele
having zarobki = avg(zarobki)

który zwraca wszystkie wiersze

Czasem zachodzi konieczność przefiltrowania wyników już zagregowanych w sposób możliwy do zrealizowania przez where.

select imie,count(imie) imiona from przyjaciele
where imiona > 5

to zapytanie również wyświetli błąd i jest to błąd popełniany dość często przez niedoświadczone osoby. Powinienem napisać o tym w sumie na początku przed opisem HAVING, ale chciałbym uniknąć takiego rozumowania.

W takim zapytaniu możemy wykorzystać wspomniane having lub zrobić z tego podzapytanie i użyć filtru where

select * from (
select imie,count(imie) imiona from przyjaciele
group by imie
) jakis_alias
where imiona>5

Takie zapytanie jest już poprawne. Pozostaje tylko kwestia dyskusji nad optymalnością i kosztem takiego rozwiązania.

Warto zwrócić także uwagę na podzapytania agregujące w selekcie:

select zarobki-srednia from (
select imie, zarobki, (select avg(zarobki) from przyjaciele p1 where p1.plec = p2.plec ) srednia from przyjaciele p2
) x

Nie jest to może zbyt szczęśliwy przykład, ale pokazuje możliwości.

W podzapytaniu grupującym, a właściwie na wynik takiego podzapytania w locie można nakładać funkcje arytmetyczne, np. zaokrąglenie

select zarobki-srednia from (
select imie, zarobki, round((select avg(zarobki) from przyjaciele p1 where p1.plec = p2.plec ),1) srednia from przyjaciele p2
) x

zagadka – zapytania skorelowane ciąg dalszy

Na jednym z blogów znalazłem ciekawą zagadkę:

zagadka o bazach

SELECT
*
FROM
tab_a
WHERE
pole_a IN ( SELECT pole_a FROM tab_b );

Co wyświetli takie zapytanie?

Jak na ironie wyświetli wyniki i to niestety na pierwszy rzut oka sensowne.

Jednak jeśli się przyjrzeć widać w tym zapytaniu duży błąd.

Dzięki mechanizmowi zapytań skorelowanych mamy możliwość łączenia wyników zapytania z podzapytaniem. Jednak w tym wypadku mamy do czynienia z czymś na wzór:

select ‘cos’ from tabela

zapytanie zwróci n wierszy z ‘cos’ gdzie n to ilość wierszy w tabela.

Jednakże bardziej roztropni zrobią coś takiego:

SELECT
*
FROM
tab_a a
WHERE
pole_a IN ( SELECT a.pole_a FROM tab_b b );

i teraz błąd już łatwo dostrzec, ale nadal zapytanie wykona się prawidłowo.

SELECT
*
FROM
tab_a a
WHERE
pole_a IN ( SELECT b.pole_a FROM tab_b b );

Dopiero teraz dostaniemy błąd (o ile pole_a nie istnieje w tab_b)

Teraz mała kwestia optymalizacyjna

spotkałem się z tym iż w podzapytaniu funkcji in () niektórzy używają distincta.

select a from tab where tab_a in (select distinct b from tab_b )

Jest to kompletnie bez sensu, w przypadku baz Oracle hasło distinct zostanie pominięte i nie wpłynie na koszt zapytania.

Ale jeśli ktoś wpadnie na pomysł

select a from tab where tab_a in (select b from tab_b
group by b)

to koszt zapytania już wzrośnie.

Left outer join vs zapytanie skorelowane

Dość ciekawym polem do optymalizacji zapytań jest możliwość wykorzystania zapytań skorelowanych.

select col_a, col_b from tab_a a
left outer join tab_b b on
a.id = b.id

vs

select col_a, ( select col_b from tab_b b where a.id=b.id) from tab_a a

W przykładzie testowanym na bazie produkcyjnej okazało się że użycie zapytań skorelowanych dało 18% zysku w stosunku do left outer joinów. W testowanym przykładzie była joinowana ta sama tabela (słownikowa) 5 razy i za każdym razem były z niej pobrane 3 kolumny.

Widać iż ilość kolumn potrzebnych w zapytaniu finalnym będzie miało wpływ na koszt zapytania, jednakże w niektórych przypadkach może to być całkiem ciekawy sposób na optymalizacje.

Jak twierdzi Dan Tow w swojej książce SQL optymalizacja, opłaca się optymalizować od zysku 20% w górę, jednak osobiście uważam że w skali całej aplikacji z ziarenek może uzbierać się znaczna różnica w wydajności.