create table – DDL tworzenie tabel w bazie danych

Pracując z bazą danych prędzej czy później będziemy chcieli utworzyć własną tabelę, która będzie miała przechowywać dla nas jakie informacje. Warto poświęcić chwilę nad rozmyślaniem. Koniecznie trzeba odpowiedzieć sobię na pytanie do czego ma ta tabela służyć. Można powiedzieć że pod kątem użytkowym najważniejsze rodzaje tabel jakie można wyróżnić to:

tabela transakcyjna – przechowujemy w niej np nagłówki faktur, często dopisujemy tam jakieś dane i często je odczytujemy. Raczej nie usuwamy z niej danych, lub wręcz nie pozwalamy by danych nie było można usunąć.

tabela słownikowa – Tabela której zawartość niezbyt często się zmienia, ale jest często odpytywana. Przykładem takiej tabeli może być np tabela z informacją o użytkownikach systemu. System często pyta się o dane użytkowników ale rzadko je zmienia.

tabela log – do takiej tabeli wrzucamy informacje o zmianach, często taka tabela bardzo szybko rośnie. Rzadko do niej zaglądamy.

Jeszcze należy spojrzeć od strony danych na nasz przyszłościowy twór. Jakich potrzebujemy restrykcji? Czy nasza tabela będzie referencyjną czy referowaną? A może miks?

tabela referencyjna – np tabela z nagłowkiem faktur. Posiada klucz główny (Primary Key). Do niej odwołują się inne tabele, np tabela z pozycjami faktury posiada identyfikator nagłówka.
tabela referowana – Tabela która posiada klucz obcy innej tabeli. Np tabela pozycji dokumentów. Warto się zastanowić nad właściwościami klucza obcego.
tabela miks- ma swój własny klucz główny, posiada klucze obce. W sumie najczęściej spotykany przypadek. Tabela nagłówków faktur posiada najczęściej identyfikator klienta jako klucz obcy.

Następny etap to uzmysłowienie sobie w jakim kontekście będziemy wykorzystywać tą tabele?
Czy chcemy pytać się o konkretne wiersze? Jeśli tak to na jakiej podstawie?

Na przykładzie tabeli z fakturami. Często będziemy pytać się o listę faktur(np dla danego klienta, za dany okres). Najczęściej jednak będzie interesować nas konkretna faktura.

Na przykładzie tabeli pozycji, to będą nas raczej interesować wszystkie rekordy należące do określonego dokumentu. Czasem zapytamy się o to jakie towary znalazły się na fakturach w globalu.

Teraz trzeba się zastanowić nad formą tabeli. Musimy być świadomi istnienia formatu danych. Baza inaczej traktuje liczby, inaczej napisy i daty. Patrząc globalnie to takie 3 typy obsługuje praktycznie każda baza danych.

typy liczbowe reprezentują liczby (dziwne nie?), są one rozbite na podtypy. Możemy wyróżnić liczby całkowite oraz liczby zmiennoprzecinkowe. Kolejnym podziałem jest wielkość liczby. Ze względu na ograniczenia pamięci i chęć stosowania stałego rozmiaru wiersza przez bazę danych musimy określić przedział liczbowy jaki chcemy przechowywać w pamięci.
Patrząc najprościej baza udostępnia nam ileś typu podając przedziały liczb które możemy tam zapisać. Podając liczbę z przedziału mamy pewność że ona się tam zmieści. Baza dokładnie zna rozmiar każdego wiersza i może szybko przeszukiwać pliki w celu odnalezienia wiersza, który nas interesuje. Niestety różne systemy bazodanowe różnie traktują typy danych. Typy są różnie ponazywane i mają różne właściwości. W zapytaniach będę starał się korzystać z typów które działają na każdej bazie danych.

Po nudnym wstępie możemy przejść do konkretów. Zaczynając od podstaw zdefiniujmy naszą tabelę przyjaciele:

create table przyjaciele (
id int,
imie varchar2(255),
plec int,
wiek int,
zarobki int,
kolor int);

objaśniając powyższy kod:
create table – to konstrukt (słowo kluczowe)
kolejno mamy nazwe tabeli. Systemy bazodanowe określają normy nazewnictwa. Ogólnie przyjeło się iż nazwy tabel nie zawierają polskich znaków, spacji i innych udziwnień.
Następnie mamy nawias w którym po przecinku wymieniamy listę kolumn i ich właściwości.

Zaczynamy od nazwy kolumny, zaleca się stosowanie podobnych zasad jak w przypadku nazw tabel. O sposobach nazewnictwa pisałem między innymi we wpisie odnośnie klucza głównego bądź modelu relacyjnego.

Następnie podajemy typ kolumny czyli format danych. Format liczby całkowitej nazywa się w większości systemów ‘int’. Z typami zmiennoprzecinkowymi jest różnie niektóre systemy akceptują zapis number(x,y) gdzie x to maksymalna ilość znaków przed przecinkiem, a y to ilość znaków po przecinku. Analogiczny zapis (w MSSQL) to decimal(x,y).

W przypadku zapisu ciągów znaków nazywanych ‘string’ w bazach danych operujemy nazwą varchar, jest ona raczej powszechnie akceptowalna. W nawiasie podajemy maksymalna ilość znaków jaką chcemy zapisać w kolumnie. Przyjęło się nie podawać wartości większej niż 255. Kiedyś się po prostu nie dało, w chwili obecnej można podawać w zależności od systemu wartości idące w tysiące, tylko po co?

Załóżmy że chcielibyśmy aby nasza tabela przechowywała datę urodzenia przyjaciela. Jeśli wykonaliśmy poprzednie polecenie staniemy przed pewnym problemem. Otóż albo zmodyfikujemy tabele albo po prostu ją usuniemy. W chwili obecnej założymy że usuwamy tabele ( nie mamy w niej jeszcze danych)

drop table przyjaciele;

Pisałem o tym w innych wpisach. Tworzenie tabel i ich usuwanie, tak samo jak ich czyszczenie to operacje typu ddl.
Więc są to operacje nie obsługiwane przez transakcje. Cofnięcie takiej operacji jest czasem nie możliwe. Więc należy być ostrożnym.

Wracając do tematu daty. W różnych systemach jest to różnie realizowane ale najczęściej są dostępne typy date, datetime. Należy zajrzeć do dokumentacji i zobaczyć jak są one realizowane. Generalnie data jest zapisywana jako ilość sekund jakie upłyneły od jakiegoś tam momentu. By zapisać datę do bazy najczęściej będziemy potrzebowali jakiejś funkcji. Niektóre RDBMS umożliwiają zapisanie daty bezpośrednio ze stringa(napisu) ale jest to zły nawyk. Różne kraje stosują różny zapis daty RRRR-MM-DD, DD-MM-RRRR itd. MSSQL potrafi na źle wpisanej dacie wykonać dziwne operacje, więc lepiej uważać.

create table przyjaciele (
id int,
imie varchar2(255),
plec int,
wiek int,
zarobki int,
kolor int,
data_urodzin date);

Rozważmy też opcję iż chcemy zapisywać informację o tym kiedy kogoś uznaliśmy za przyjaciela. Powinniśmy skorzystać z typu datetime lub timestamp (Znacznik czasu). W różnych systemach znów mamy różne implementacje. Jednak najbardziej uniwersalne jest stworzenie triggera(wyzwalacza). Narazie tylko utwórzmy pole, które w przyszłości będzie przechowywać datę utworzenia:

create table przyjaciele (
id int,
imie varchar2(255),
plec int,
wiek int,
zarobki int,
kolor int,
data_urodzin date,
data_wstawienia datetime);

Teraz trochę o atrybutach. Nie chcemy by nasze ID mogło być puste. Istnieje atrybut not null

create table przyjaciele (
id int not null,
imie varchar2(255),
plec int,
wiek int,
zarobki int,
kolor int,
data_urodzin date,
data_wstawienia datetime);

wymusza on przy insercie sprawdzenie czy kolumna ma przypisaną jakąś wartość. Sprawdzenie następuje after insert na moment przed zakończeniem transakcji. Tak więc w triggerze before insert można dodać taką wartość.

możemy także rozważyć sytuację w której chcemy przypisać jakąś wartość do wiersza, jest to tak zwane default value. Czyli wartość która zostanie przypisana w przypadku gdy pominiemy ją w insercie.

create table przyjaciele (
id int not null,
imie varchar2(255),
plec int default 1,
wiek int,
zarobki int,
kolor int,
data_urodzin date,
data_wstawienia datetime);

Do płci przypiszemy wartość 1 w każdym insercie.

Warto także utworzyć klucz główny czyli jednoznaczny identyfikator wiersza

create table przyjaciele (
id int not null Primary Key,
imie varchar2(255),
plec int default 1,
wiek int,
zarobki int,
kolor int,
data_urodzin date,
data_wstawienia datetime);

Więcej szczegółów o primary key można przeczytać tutaj

Primary Key – Czyli słów kilka o id

Według modelu relacyjnego każdy wiersz w tabeli posiada swój jednoznaczny identyfikator. Tym identyfikatorem w warstwie użytkownika jest najczęściej kolumna, która posiada w nazwie id. Nazewnictwo kolumn oczywiście jest kwestią dowolną, ale dobrze trzymać się jakiś schematów. Jeśli by spojrzeć na tabele przyjaciele dobrze znaną z pozostałych wpisów na tym blogu, mamy tam kolumnę ID. Przeglądając różne gotowe systemy bazodanowe najczęściej spotkamy się z takim zapisem bądź zapisem poprzedzonym przedrostkiem. Przedrostek jest definiowany dla danej tabeli najczęściej jest pewnego rodzaju akronimem/skrótem. Jeśli mamy tabele przyjaciele i chcielibyśmy ją zapisać w konwencji z przedrostkiem kolumny by wyglądały np tak: przy_id,przy_plec,przy_wynagrodzenie. Stosowanie przedrostków na dłuższą metę jest bardzo korzystne. Wracając do tematu id. W naszej tabeli mamy kolumnę o takiej właśnie nazwie. Zapisujemy tam identyfikator każdego wiersza. ID mamy jedno dla każdego przyjaciela. Mimo iż możemy mieć wielu przyjaciół co nazywają się Jan Kowalski. Tak więc id jest unikalne. Takich numerów w naszym świecie obowiązuje całe mnóstwo. Numer gg,telefonu,pesel,nip itd.

Takie pole musi spełniać kilka właściwości.

Po pierwsze każdy wiersz musi mieć określoną wartość – czyli kolumna musi mieć właściwość not null,
po drugie każda wartość musi być unikalna czyli kolumna musi mieć właściwość unique.

Klucz główny może składać się z kilku kolumn. Wszystkie kolumny razem muszą tworzyć unikalny identyfikator. W praktyce jednak rzadko się stosuje takie rozwiązania. Należy pamiętać iż tworząc klucz dla wielu kolumn, klucz obcy musi posiadać taką samą ilość kolumn.

Nadanie klucza głównego praktycznie zawsze skojarzone jest z utworzeniem indeksu.

Indeks jest swojego rodzaju spisem treści dla danej tabeli. Pozwala on systemowi bazodanowemu na szybkie poruszanie się po tabeli i pobieranie tylko interesujących nas wierszy.

Patrząc z przykładu tabeli przyjaciele, zakładając że pol id jest właśnie PK (primary key)

select * from przyjaciele where id = 454

staje się zadaniem bajecznie prostym. Otwieramy spis treści i wchodzimy w miejsce gdzie jest przyjaciel o takim id.
W przypadku braku indeksu, musimy przeglądać tabelę rekord po rekordzie (full scan table). W najgorszym przypadku szukany przyjaciel może być ostatnim rekordem jaki znajdziemy.

jak działają transakcje – systemy OLTP

By zapewnić dostępność danych w systemie transakcyjnym (czyli takim w którym użytkownicy modyfikuj dane), należy porozmyślać trochę nad samym systemem. Załóżmy że pracujemy na systemie przy którym równolegle na różnych stanowiskach pracuje 5 fakturzystek. Jest to system do wypisywania faktur i obsługi stanów magazynowych. Dla pełni obrazu ustalmy model iż klient najpierw przychodzi składa zamówienie na podstawie którego powstaje faktura. Klient z fakturą udaje się na magazyn gdzie towar jest kompletowany i wydawany. Model ten jest dużym uproszczeniem, ale pozwoli przedstawić idee transakcji.

Patrząc atomowo na proces wypisywania faktury:
musimy wystawić nagłówek i zacząć dodawać pozycje.
Dodanie pozycji to wpisanie jej na fakturę plus zmniejszenie stanów magazynowych + sprawdzenie linii kredytowej klienta (przekroczenie linii powoduje niemożność dodania kolejnej pozycji do faktury).

Na dodaniu pozycji się skupimy. Wyraźnie widać że musimy przeprowadzić kilka operacji za jednym zamachem.
Musimy sprawdzić stan, jeśli jest wystarczający pobrać go na fakturę i zmniejszyć na magazynie itd…

Kilka operacji na bazie które stają się integralnym procesem powinno przeprowadzić się w transakcji. Transakcja jest rozliczana zero jedynkowo (sukces / porażka), w przypadku porażki baza powraca do stanu sprzed uruchomienia transakcji(mówimy o poziomie modyfikowanych danych). Dodatkowo transakcja alokuje nam zasoby a w wielu przypadkach wręcz je blokuje (o czym dokładniej się rozpiszę za chwilę). Najważniejsze jest jednak że transakcja nas chroni. Ochrona ta polega na tym iż wykonując krokowo te czynności może w trakcie dojść do styuacji gdy pójdzie coś nie tak. Zdejmiemy stan ale nigdzie go nie wstawimy. Patrząc z perspektywy banku i przelewu zdejmiemy pieniądze z jednego konta ale ich nigdzie nie przelejemy, lub na odwrót wstawimy pieniądze na konto i ich gdzieś nie zdejmiemy. Takie sytuacje są bardzo niebezpieczne. Dlatego też jeśli w jednej operacji korzystamy z więcej niż jednego zapytania musimy to objąć w transakcje.
W większości systemów wygląda to tak:

begin transaction
update tabela1 set pole1=23 where id=23;
insert into tabela2 set pol2=34 where id=324435;
commit;
end transaction;

Np w Oracle zaczynając pierwszą operację DML ustanawiamy transakcję i będzie ona trwać do momentu
wydania polecenia commit/rollback. Dla nowych użytkowników jest to dość zaskakujące i może się skończyć zlokowaniem tabel. W momencie gdy w Oracle aktualizujemy dane (otwieramy transakcje) pozostałe transakcje dla tej tabeli są wstrzymane (czekają w kolejce). Pozostali użytkownicy mogą w tym czasie pobierać dane (robić selecty) jednak ujrzą dane sprzed otwarcia transakcji. Objawem otwartje transakcji jest np dodanie pozycji do faktury które zawiesza program, albo wykonanie zapytania które wykonuje się w ułamek sekundy (normalnie), a tym razem się wykonuje bez końca. W Oracle są odpowiednie widoki, które pozwalają przejrzeć locki na tabelach. W przypadku MSSQL sprawa wygląda zupełnie inaczej. Musimy napisać sami że chcemy coś zapiąć w transakcję. Jeśli tego nie zrobimy, czekają nas 2 poważne konsekwencje. Z jednej strony brak gwarancji iż całość przebiegnie poprawnie. Z drugiej strony nie możliwości cofnięcia transakcji. gdy zmiany się dokonają i efekt nas nie zadowala to musimy napisać zapytania które przywrócą stann poprzedni co nie zawsze jest możliwe. Kolejną różnicą jest fakt iż w MSSQL abele są blokowane już przy pobieraniu danych. Więc tworząc raport który wykonuje np 10 min. Na te 10 min blokujemy tabele z których tworzy się raport. Na szczęście czasem MSSQL wykorzystuje bazę tymczasową gdzie wrzuca kolejne kroki zapytań zwalniając zasoby. Tak więc operując na wielu tabelach możemy skrócić czas blokowania tabel. Dodatkowym bonusem od Billa jest składnia with (nolock).

select * from tabela with (nolock)

Pozwala to na wykonanie zapytania które nie zablokuje tabeli. Niestety musimy liczyć się z tym iż w wyniku dostaniemy nie aktualne dane. Jednak np dla raportów za minione okresy jest to stosunkowo dobre rozwiązanie.

Ew. Gdy zależy nam na jakiś konkretnych danych mozemy tylko część tabel zrobić z with nolock, a najważniejsze dane pobać bez tego parametru:

select * from tabela1 t
inner join tabela t2 with(nolock) on
t1.id = t2.id;

Jak się ma sprawa w MYSQL? To już zupełnie inna historia. W mysql mamy do wyboru różne silniki. Istnieją silniki które nie wspierają transakcji. W przypadku wsparcia dla transakcji nie wiem jaki jest ich wpływ na locki na tabelach.

Istnieje także pojęcie transakcji rozproszonych na różnych maszynach. W przypadku linku 2 mssqli lub nawet 2 baz na jednej instancji, jesteśmy w stanie przeprowadzić sensowną transakcję. Wymaga to pewnej prekonifguracji ale jest to osiągalne. W przypadku Oracle na zdalnym serwerze (linkowanym) Nie ma możliwości przeprowadzenia DML.
Jeśli chodzi o środowiska heterogeniczne (MSSQL i ORACLE). W przypadku korzystania z OpenQuery nie jesteśmy w stanie przeprowadzić sensownej transakcji. Openquery wymusza commit po każdym zapytaniu. Można skorzystać z dodatkowych pól w tabelach do oznaczania statusów ale to już zabawa.

W oracle mamy jeszcze transakcje autonomiczne. Jest to transakcja w transakcji. Przydatne zwłaszcza do logowanai błędów. Cofamy główną transakcję a pod spodem zapisujemy informacje o błędzie. Bardzo przydatna sprawa!

Istnieje jeszcze pojęcie stopnia izolacji transakcji ale to już zagłębianie się w szczegóły.

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 – delete – SQL – usuwanie danych z bazdy danych

Usuwanie danych z bazy to w systemach transakcyjnych stosunkowo najrzadziej wykonywana operacja. Przez wiele osób uznawana za niebezpieczną. Jednak dobrze zaprojektowana baza nie pozwoli nam usunąć rzeczy, które mogą zakłócić jej działanie. Nie mniej przed usuwaniem danych warto zapisać tabele lub co najmniej wiersze które chcemy usunąć. Dobrym zwyczajem może być napisanie selecta z takimi warunkami z jakimi chcemy usuwać by sprawdzić czy wybraliśmy tylko te dane, które rzeczywiście chcemy usunąć. W systemach Oraclowych mamy automatyczną transakcję więc możemy podejrzeć zmiany które dokonaliśmy przed skomitowaniem. W innych systemach ręczne otwarcie transakcji może być dodatkowym zabezpieczeniem przed nie przewidywaną utratą danych.

Jeśli chcemy usunąć wszystkie wiersze z tabeli należy rozważyć operację TRUNCATE. Truncate wycina wszystko z tabeli. Dodatkowym atutem jest cofnięcie HWM, High Water Mark – czyli wskaźnik końca pliku (wskaźnik wysokiej wody). Jest przesuwany w przód w momencie dodawania danych do tabeli. Jednak nigdy nie jest cofany. Mimo usuwania danych tabela wciąż rośnie. Dlatego projektując np tabele interfejsowe warto pomyśleć o okresowym cofnięciu tego znacznika. W różnych RDBMS jest różnie realizowane(bądź w ogóle nie jest w inny sposób niż truncate).

Tak więc składnia truncate: truncate table nazwa tabeli;

truncate table przyjaciele;

spowoduje usunięcie wszystkich danych z tabeli przyjaciele.

Ważna uwaga odnośnie truncate: To nie jest DML – transakcja!. Tak więc nie będzie możliwy rollback! Jeśli zrobiliśmy kopie tabeli przed usuwaniem bądź modyfikowaniem danych po zakończonej operacji należy posprzątać. Tabele można usunąć poleceniem DROP, które jest częścią DDL SQL.

Nie mniej składnia operacja usuwania tabeli wygląda następująco:

drop table nazwa;

czyli:

drop table przyjaciele_kopia;

usunie nam tabele przyjaciele_kopia.Podobnie jak Truncate DROP nie jest opracją DML więc nie będzie możliwe usuwanie danych. Przejdźmy do meritum. Polecenie delete pozwala nam usuwać dane, wiersze z tabeli. Możemy usuwać tym poleceniem tylko całe wiersze więc nie podajemy nazw kolumn do usunięcia. składnia polecenia delete wygląda następująco:

delete from tabela;

Powyższe polecenie wyczyści całą tabele i było by równoznaczne z truncate gdyby nie fakt iż nie cofa HWM.

delete from tabele where kolumna_id =1;

spowoduje usunięcie wierszy spełniających warunek. Więcej o warunkach można przeczytać w części poświęconej filtrowaniu danych. Na realnym przykładzie, chcąc usunąć z naszej przykładowej tabeli przyjaciół wszystkie kobiety należy wykonać polecenie:

delete from przyjaciele where plec=0;

Jeśli chcemy usunąć przyjaciela o id=34

delete from przyjaciele where id=34;

W warunkach do usuwania możemy posługiwać się wszystkimi możliwościami jakie dają filtry (funkcje,podzapytania).

Teraz trochę o niespodziankach jakie mogą nas spotkać. Jeśli tabela jest relacją do innej tabeli, weźmy pod uwagę przykład nagłówka i pozycji faktury. Niedopuszczalną sytuacją jest posiadanie pozycji faktury do których nie ma nagłówka. Więc często podczas zestawiania klucza głównego i obcego ustawia się usuwanie kaskadowe, czyli wszystkie podrzędne klucze zostaną też usunięte. Usuwając jeden wiersz z tabeli nagłówków możemy usunąć kilka tysięcy wierszy z tabeli pozycji z jednym razem zupełnie nie świadomie.

Czasem dla mniej ważnych relacji ustawia się w momencie usuwania klucza głównego wartości null dla klucz obcych. W przykładzie o fakturach możemy np mieć w nagłówku referencje do klucza głównego klienta. Usuwamy dane klienta i traci się powiązanie. Jest bardziej optymistycznie niż w poprzednim przykładzie bo faktura zostanie tylko znikną nam dane klienta. Przykład i tak jest niezgodny z literą prawa. W przypadku większych systemów warto sprawdzić constrainty tabeli z której chcemy usuwać dane. Jest to tym bardziej potrzebne jeśli operujemy na systemach w których istotne jest księgowanie albo inne procesy wymagane prawem. Jak widać kopia tabeli może nie zawsze się przydać ze względu na kaskadowe usuwanie.

Jest też trzecia możliwość związana z kluczami. To po prostu brak akcji na usunięcie klucza głównego. W Oracle nic nie szkodzi, bo sekwencja się nie cofnie, i powstałe dziury nie są uzupełniane. Jednak w MYSQL w jednym z silników po  restarcie bazy następuje cofnięcie wartości do maksymalnej +1 z tabeli. Wyobraźmy sobie sytuacje w której usuwamy ostatnio dodanego klienta i następuje restart bazy, mija trochę czasu i ktoś dodaje innego klienta. I w ten sposób możemy mieć złego klienta przypisanego do faktury. Numer id został przypisany innej osobie.

Kolejna niespodzianką przy usuwaniu to tak jak przy innych DML’ach mogą być triggery. Czyli wyzwalacze o których wspomniałem przy okazji insertowania danych. Wyzwalacze mogą nie pozwolić na usunięcie danych lub zmodyfikować nasze zapytanie( w sensie wynik delete może być inny od oczekiwanego).

Jeszcze informacja dla osób początkujących skopiowanie tabeli przez select into tudzież create as nie kopiuje constraintów i triggerów więc wynik przetestowanego zapytania DML na kopii może być zupełnie inny niż na tabeli produkcyjnej.

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.

wstęp do DML – czyli trwała zmiana danych w bazie.

DML czyli data modyfication language, jest kolejnym tematem rzeką związanym z SQL. Większość osób do DML zalicza także zapytania typu SELECT, ale osobsićie uważam że trwałe modyfikacje danych należy omówić jako osobny rozdział. Umiejętność modyfikowania danych jest potrzebna zdecydowanie mniejszej grupie osób niż konieczność ich pobierania. Nie mniej wiele osób stanie przed koniecznością zmian na bazie z pominięciem interfejsu użytkownika. Są to najbardziej niebezpieczne operacje wymagające największej dozy wyobraźni i wiedzy o bazie. W wielu systemach zmiany mogą okazać się nie odwracalne i tylko kopia zapasowa może nas uratować.

Pracując przy bazach danych powinno się należeć do grupy osób, które robią kopie zapasowe. Jeśli tak nie jest prędzej czy później się to zemści.

Ze względu na krzyżowanie się tego tematu z językiem tworzenia struktury baz danych kilka rzeczy zostanie w tej części kursu omówionych na zasadach komentarza a ich głębsze wyjaśnienie znajdzie się w części do tego przeznaczonej.

Tak naprawdę baza rozróżnia dwie operacje modyfikacji danych wstawianie i usuwanie. Przy czym aktualizacja danych jest zoptymalizowanym procesem usuwania i wstawiania. W takiej właśnie kolejności zostaną omówione te zagadnienia w poniższym kursie.

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;-)