Delphi - MS SQL adatbázis kezelés Delphi-ből

Logikai komponensek

Egy SQL Server adatbázist egyszerre több különböző adatbázis objektum reprezentál. Ezek a következők: Table, Constraint, Default, Trigger, Index, View, Stored procedure.

Table. Az adatbázis adatai táblákban tárolódnak. Minden tábla az adatok valamilyen szempontból hasonló halmazát tárolja. Például egy iskolát leíró adatbázisban bizonyára találunk olyan táblákat, mint tanárok táblája, diákok táblája, osztályok táblája, stb. Az SQL Server táblái sorokból és oszlopokból állnak. Az oszlopok tulajdonság-típusoknak megfelelő tulajdonságokat tartalmaznak, a sorok pedig egyed-előfordulásokat tartalmaznak. Például a tanárokat (egyed-típus) nyilvántartó tábla egyik sorában Torna József (egyed-előfordulás) szerepel. Ha kíváncsiak vagyunk a tanárok hajszínére (tulajdonság-típus), akkor tárolnunk kell egy ennek megfelelő mezőt, aminek Torna József esetén legyen az értéke barna (tulajdonság).

Az egyes objektumok tulajdonságai nagyon sokfélék és különbözőek lehetnek Az oszlopok egyik fontos jellemzője az adat típus, ami meghatározza, hogy az oszlopban milyen típusú adatok tárolhatók. A legfontosabb adat típusok a következők: binary, bit, char, datetime, float, int, varchar. Ha a meglevő adat típusok nem elegendőek, saját adat típust hozhatunk létre.
Egy kicsit előreszaladva készítsünk például egy birthday nevű adat típust, ami olyan, mint a datetime típus, és megengedi a Null értéket:

EXEC sp_addtype birthday, datetime, 'NULL'
GO

Készítsünk egy olyan táblát, ahol az egyik oszlop ilyen típusú:

CREATE TABLE employee(
  emp_id  char(5),
  emp_first_name  char(30),
  emp_last_name  char(40),
  emp_birthday  birthday)

Tekintsük át a legfontosabb adat típusokat kicsit részletesebben.
Egész érték tárolására 4 típus áll rendelkezésre. A bit típusú adat lehet 1, 0 vagy Null. Az int típus 4 byte-os előjeles egész, vagyis -2^31-től (-2,147,483,648) 2^31-1-ig (2,147,483,647) vehet fel értékeket. Kisebb testvére a smallint, ez csak két byte-on tárolódik, tehát -32,768 és 32,767 közötti értékeket vehet fel. A család legkisebb tagja a tinyint (1 byte, 0-255).

Két lebegőpontos típust használhatunk, a float és a real típusokat. Ezek úgynevezett pontatlan számtípusok, vagyis pontosságuk korlátozott. A float típus ábrázolási tartománya -1.79E+308-tól 1.79E+308-ig terjed. 4 vagy 8 byte-on tárolódik egy opcionális n paramétertől függően. (float [n]), és pontossága 7 vagy 15 jegy. A real típus 4 byte-os, ábrázolási tartománya -3.40E+38-tól 3.40E+38-ig terjed.

Két nagyobb pontosságú adat típus a numeric és a decimal. A két típus ugyanazt valósítja meg, az ANSI kompatibilitás miatt lettek beépítve. Megadásuknál meg kell határoznunk a pontosságot és az ábrázolandó nagyságrendet. A tárolás mérete ezektől a paraméterektől függ. Példa: numeric(20,6) olyan szám típusa, amelyikben 20 számjegyet szeretnénk tárolni, és ebből 6 tizedes jegy.

Karakteres (nem Unicode) adatok tárolására 3 típus áll rendelkezésre. A char típus egy fix hosszúságú karakterláncot tárol, maximális mérete 8000 karakter. A varchar típusú változó ugyanezt teszi, de csak annyi memóriát foglal le, amilyen hosszú az aktuális karakterfüzér. A 8000 karakternél hosszabb adatok kezelésére szolgál a text típus. Ebben legfeljebb 2 147 483 647 karaktert tárolhatunk.

A dátumok kezelésére két típus áll rendelkezésre. A 8 byte-os datetime, és a 4 byte-os smalldatetime. Utóbbi 1900-tól 2079-ig használható, nagyobb testvére 1753-tól 9999-ig. Még egy különbség van a két típus között, az első sokkal pontosabb, millimásodperces pontossággal tárolja az időt, utóbbi csak perces pontossággal.

View. A view nem más, mint egy virtuális tábla. Egy view-ban látott adathalmaz nem feltétlenül tárolódik egyetlen objektumban. Legtöbbször több tábla adataiból áll, amit egy SELECT utasítással válogatunk le. A view-kat el lehet nevezni, és ugyanúgy viselkednek, mint a táblák. Mire jó hát akkor egy view?

- Egy view-n keresztül a táblának csak bizonyos sorait mutatjuk meg a felhasználónak. Például egy alkalmazott a tábla csak rá vonatkozó részét nézheti meg.
- Egy view-n keresztül a táblának csak bizonyos oszlopait mutatjuk meg a felhasználónak. Például egy alkalmazottnak meg szeretnénk mutatni a többi alkalmazott nevét, osztályát, beosztását, stb., de a fizetési információkat nem.
- Logikailag összetartozó adatokat gyűjthetünk össze, és úgy mutathatjuk meg őket, mintha egyetlen táblában lennének.

Stored Procedure. A tárolt eljárás SQL utasítások egy előre lefordított halmaza. A tárolt eljárások az alkalmazásban megvalósítandó logika leképezésének legfőbb eszközei. A későbbiekben bővebben lesz róla szó.

Constraint. A constraint-ek segítségével szabályokat definiálhatunk arra vonatkozólag, hogy az egyes oszlopokba az adatokat milyen megszorításokkal írhatjuk csak be. Ötféle constraint van:

- NOT NULL: Az oszlopban nem fordulhat elő Null érték.

- CHECK: Egy logikai kifejezéssel megszűrhetjük az oszlopba írható adatokat. A következő
példában a tábla cust_id oszlopába csak 0 és 10000 közti értékeket engedünk meg.

CREATE TABLE cust_sample 
(
cust_id            int PRIMARY KEY, 
cust_name          char(50), 
cust_address       char(50), 
cust_credit_limit  money, 
CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000 ) 
)

- UNIQUE: Ez a constraint biztosítja, hogy egy oszlopban a nem Null értékek közül biztosan nem lesz két megegyező. Ezt a tulajdonságot a Primary key is megköveteli, de ő a Null értékeket sem engedi meg.

- PRIMARY KEY: Ez a constraint jelöli ki azt az oszlopot vagy oszlopcsoportot, ami az egyes rekordokat megkülönbözteti egymástól. Minden táblában kell primary key-t deklarálni.

- FOREIGN KEY: Ez a constraint azonosítja a táblák közti kapcsolatokat. A Foreign key egy másik tábla egyedi azonosítójára kell hogy mutasson, ami lehet abban a táblában akár primary key vagy unique constraint-tel ellátott oszlop.

Default. A default-ok határozzák meg egy mező értékét, ha beszúráskor annak mi nem adunk értéket. A default bármi lehet, ami egy értéket ad eredményül: lehet konstans, függvény vagy matematikai kifejezés. Egy példa default létrehozására:

CREATE TABLE test_defaults
  (keycol      smallint,
  process_id   smallint DEFAULT @@SPID,
  date_ins     datetime DEFAULT getdate(),
  mathcol      smallint DEFAULT 10 * 2,
  char1        char(3),
  char2        char(3) DEFAULT 'xyz')
GO

Trigger. A trigger egy speciális tárolt eljárás, ami akkor hajtódik végre automatikusan, ha egy táblára kiadunk egy Update, egy Insert vagy egy Delete utasítást. A triggerek arra jók, hogy az adatok módosításával kapcsolatos szabályokat automatizálni lehessen. A későbbiekben részletesen lesz róla szó.

Index: Az indexek olyan táblához kapcsolt struktúrák, amik az adatok gyors visszakeresését hivatottak meggyorsítani. A rekordokra kulcsokkal hivatkozunk, ezeket a kulcsokat tároljuk az indexben olyan módon, hogy az SQL Server azt a lehető legrövidebb időn belül megtalálja. Szintén részletes tárgyalásra kerül később.

Fizikai komponensek

Az alapvető adattárolási egység az SQL Serverben a lap (page). Egy lap mérete 8 KB, vagyis 1 megabájtnyi adat 128 lapon tárolódik. Minden lap egy 96 byte hosszú fejléccel kezdődik, itt tárolódik például a lap típusa. Hat különböző típusú lap van: Data (ilyen lapokon tárolódik minden adat, kivétel a text, ntext és image típusúak), Index (index bejegyzések), Text/image (text, ntext, image), és még három speciális típusú lap, amin rendszerinformációk tárolódnak. Az adatsorok közvetlenül a fejléc után helyezkednek el. Másik lapra soha nem terjednek át, így maximális méretük 8060 byte.

A táblák és indexek tárolásának alapegységei az extent-ek. Egy extent 8 egymás után következő lapból áll (64kB). Az SQL Server soha nem foglal le egyetlen táblának egy extent-et, így kisebb méretű táblák és indexek megosztozhatnak egy-egy extent-en.
A log (napló) állományok más struktúrában tárolódnak.

Az SQL Server az adatbázisokat fájlokban tárolja. Az adat és log információk sohasem kerülnek ugyanabba a fájlba, és a különböző adatbázisok is különböző fájlokban tárolódnak. Háromféle fájltípus van:
- Elsődleges adatfájl. Minden adatbázisnak van. Kiterjesztése .mdf.
- Másodlagos adatfájl. Az adatbázis azon része tárolódik itt, ami belső szervezési okokból nem kerül az elsődleges fájlba. Lehet, hogy egy adatbázisban nincs ilyen, de az is lehet, hogy az elsődleges adatfájl mellett több is van. Kiterjesztése: .ndf.
- Naplófájl (log): A tranzakciós naplót tartalmazza. Több is lehet belőle, kiterjesztése: .ldf.

Tehát minden adatbázis rendelkezik egy elsődleges adatfájllal és egy, esetleg több naplófájllal. Az adatbázisban a lapok szekvenciálisan sorszámozva helyezkednek el, az első lap száma 0. Minden adatfájlnak egyedi azonosítója van, ezzel a két számmal az SQL Server minden lapot azonosítani tud.

A tranzakciós napló tárolja az Insert, Delete és Update utasítások végrehajtásakor történt módosításokat. Ha valamilyen hiba meghiúsítja a tranzakciót, a napló alapján a befejezetlen tranzakciókat vissza lehet görgetni.

MS SQL adatbázis kezelés Delphi-ből

3. rész