Datab´aze ˇcajov´ych s´aˇck˚ u Martina M´alkov´a∗ Z´apadoˇcesk´a univerzita v Plzni Katedra informatiky a v´ ypoˇcetn´ı techniky Datab´azov´e syst´emy 2 9. ˇcervna 2007
∗
[email protected]
1
1
Datov´ a anal´ yza
V p˚ uvodn´ım datov´em modelu bylo provedeno nˇekolik zmˇen - pˇrev´aˇznˇe ˇslo o pˇrid´an´ı dalˇs´ıch sloupc˚ u do tabulek (foto ˇcajovn´ıka, logo firmy atp.). Pˇribyla tak´e jedna tabulka pro urˇcov´an´ı typ˚ u ˇcaj˚ u. V´ ysledn´ y datov´ y model je zobrazen na n´asleduj´ıc´ım obr´azku.
Obr´azek 1: ERA model V dalˇs´ıch kapitol´ach budou pops´any jednotliv´e tabulky.
Caje Hlavn´ı tabulka datab´aze. Jednotliv´e poloˇzky jsou n´asleduj´ıc´ı: • Obr´ azek ˇ caje nen´ı ukl´ad´an jako Image, jak bylo pl´anov´ano v p˚ uvodn´ım modelu, ale nakonec jsem se rozhodla pro v´ yhodnˇejˇs´ı ukl´ad´an´ı jako nvarchar(100), a tak umoˇznila obr´azky ˇcaj˚ u jednoduˇse fyzicky oddˇelit od vlastn´ı datab´aze. • hodnoceni je urˇcen´a pro budouc´ı moˇznost hodnocen´ı ˇcaje od ˇcajovn´ık˚ u. Pro to ale bude muset b´ yt zavedena tabulka Hodnoceni, kde budou jednotliv´a hodnocen´ı uloˇzena. Toto v r´amci t´eto semestr´aln´ı pr´ace nebylo zpracov´ano, poloˇzka hodnocen´ı prozat´ım slouˇz´ı pouze pro hodnocen´ı administr´atorem. • pocet kusu urˇcuje poˇcet kus˚ u ˇcaje, kter´ y je implicitnˇe 1. 2
• datum vlozeni je typu DateTime a je do n´ı automaticky vkl´ad´ano datum pˇri vloˇzen´ı nov´eho ˇcaje (ˇr´adku do tabulky). • datum vymeny je implicitnˇe null, je nastavena triggerem, pokud se poˇcet kus˚ u ˇcaje zv´ yˇs´ı nad 1 (a opˇet vynulov´ana pokud se vr´at´ı na 1).
TypCaje, Staty Tabulky pouze se seznamem typ˚ u ˇcaje a st´at˚ u pro lepˇs´ı pr´aci s typy.
Cajovnici Druh´a nejd˚ uleˇzitˇejˇs´ı tabulka v datab´azi. Reprezentuje jednotliv´e ˇcajovn´ıky, kteˇr´ı si mohou zamlouvat jednotliv´e ˇcaje. N´asleduj´ı popisy jednotliv´ ych poloˇzek: • bf adresa je rozdˇelena do poloˇzek ulice, cp, mesto, psc • bf jmeno, prijmeni, www, email, fotoUrl jsou standartn´ı poloˇzky typu nvarchar(50). Foto ˇcajovn´ıka je ˇreˇseno stejnˇe jako u tabulky Caje. ymi uˇzivateli, kde je uˇzivatelsk´e • user id je kl´ıˇc do syst´emov´e tabulky s registrovan´ jm´eno a heslo. Pouˇz´ıv´a se pouze pˇri pˇrihl´aˇsen´ı, jinak se pracuje s poloˇzkou id cajovnika. • admin je poloˇzka typu bit, kter´a pouze urˇcuje zda je uˇzivatel admin. Tato poloˇzka je nastavena ruˇcnˇe na jednoho administr´atora a nen´ı umoˇznˇena jej´ı programov´a zmˇena (moˇzn´a pouze prozat´ım).
Firmy, Obchody Pˇribliˇznˇe stejn´e sloˇzen´ı jako je u tabulky Cajovnici.
Sehnane, Zamluvene Pomocn´e tabulky pro realizaci propojen´ı typu M:N, neobsahuj´ı ˇz´adn´e informace nav´ıc (pouze dvojice kl´ıˇc˚ u)
Logy Tabulka pouze pro logov´an´ı zmˇen v ostatn´ıch tabulk´ach. Do n´ı zapisuje pouze trigger, nen´ı umoˇznˇeno prov´adˇet jin´e zmˇeny dat. Povinn´ ymi parametry pˇri vkl´ad´an´ı do tabulky je jmeno tabulky a typ zmeny urˇcuj´ıc´ı jak´a operace byla nad tabulkou provedena. id zaznamu urˇcuje id zmˇenˇen´eho z´aznamu v tabulce, polozka reprezentuje n´azev sloupce, kter´ y se zmˇenil, a nove, stare novou a starou hodnotu poloˇzky.
3
2
Funkˇ cn´ı anal´ yza
V t´eto kapitole budou pops´any jednotliv´e procedury a triggery.
Procedury pro vkl´ ad´ an´ı, maz´ an´ı, update Tyto procedury jsou vˇsechny podobn´e, proto zde budou vybr´any pouze uk´azky k´odu pro kaˇzd´ y typ akce. Vkl´ ad´ an´ı do tabulky Cajovnici CREATE PROCEDURE dbo.VlozCajovnika ( @user_id uniqueidentifier, @jmeno nvarchar(50), @prijmeni nvarchar(50) = NULL, @www nvarchar(50) = NULL, @datum_narozeni datetime = NULL, @id_statu int = 0, @ulice nvarchar(50) = NULL, @cp nvarchar(50) = NULL, @mesto nvarchar(50) = NULL, @psc nvarchar(50) = NULL, @email nvarchar(50) = NULL, @fotoUrl nvarchar(100)=NULL ) AS BEGIN TRY INSERT INTO Cajovnici(user_id, jmeno, prijmeni, www, datum_narozeni, id_statu, ulice, cp, mesto, psc,email,fotoUrl) VALUES (@user_id, @jmeno, @prijmeni, @www, @datum_narozeni, @id_statu, @ulice, @cp, @mesto, @psc,@email,@fotoUrl) END TRY BEGIN CATCH RETURN -1 END CATCH RETURN @@IDENTITY
Maz´ an´ı z tabulky Caje Integrita dat pˇri maz´an´ı je zajiˇstˇena ve vˇsech tabulk´ach - pˇri maz´an´ı uˇzivatele se vymaˇzou jeho zamluven´e ˇcaje, pˇri maz´an´ı firmy jsou smaz´any jej´ı ˇcaje, pˇri maz´an´ı obchodu smaz´any poloˇzky z tabulky Sehnane. CREATE PROCEDURE dbo.SmazCaj ( @id_caje int ) AS DELETE FROM Caje WHERE id_caje = @id_caje; RETURN
4
Update Firmy Vˇsechny poloˇzky jsou povinne, protoˇze update prob´ıh´a v klientovi pouze pˇrepisov´an´ım naˇcten´ ych dat. Vˇzdy tedy bude co ukl´adat (i pr´azdn´a poloˇzka v pˇr´ıpadˇe smaz´an´ı nˇekter´eho u ´daje). CREATE PROCEDURE dbo.UpdateFirmy ( @id_firmy integer, @jmeno nvarchar(50), @www nvarchar(50), @ulice nvarchar(50), @cp nvarchar(50), @mesto nvarchar(20), @psc nvarchar(50) ) AS UPDATE Firmy SET jmeno = @jmeno, www = @www, ulice = @ulice, cp = @cp, mesto = @mesto, psc = @psc WHERE id_firmy = @id_firmy; RETURN
Jednoduch´ e procedury typu SELECT V datab´azi je tak´e ˇrada jednoduch´ ych procedur pro vracen´ı jedn´e hodnoty - napˇr. VratIDCajovnika(@user id), VratIDStatu(@jmeno),...,VratJmenoStatu(@id statu), JeSpravce(@user id). Zaj´ımavˇejˇs´ı je pouze procedura VratIDStatu, kter´a je vytvoˇrena tak, aby v pˇr´ıpadˇe neexistuj´ıc´ıho jm´ena st´atu st´at tohoto jm´ena vytvoˇrila a vr´atila jeho nov´e ID. CREATE PROCEDURE dbo.VratIDStatu ( @stat varchar(20) ) AS declare @id integer; set @id = -1; SELECT @id = id_statu FROM Staty WHERE jmeno = @stat IF(@id = -1) BEGIN INSERT INTO Staty ([jmeno]) VALUES (@stat) SET @id = SCOPE_IDENTITY(); END SELECT @id AS [result];
5
Dalˇ s´ı procedury PocetCaju Byla vytvoˇrena pro realizaci agregovan´eho dotazu. Vr´at´ı celkov´ y poˇcet ˇcaj˚ u v datab´azi. CREATE PROCEDURE dbo.PocetCaju ( @id_firmy int = NULL ) AS SELECT COUNT(*) FROM Caje c WHERE @id_firmy is NULL or @id_firmy = c.id_firmy; RETURN
ZrusZamluveni Zruˇs´ı zamluven´ y ˇcaj nˇekter´eho z ˇcajovn´ık˚ u (n´aslednˇe pˇriˇcte poˇcet kus˚ u v tabulce ˇcaje, pˇrestoˇze by toto ˇslo ˇreˇsit tak´e triggerem nad tabulkou Zamluvene). Je zde uvedena hlavnˇe jako pˇr´ıpad vol´an´ı uloˇzen´e procedury z jin´e. CREATE PROCEDURE dbo.ZrusZamluveni ( @id_caje int, @id_cajovnika int ) AS DELETE FROM Zamluvene WHERE (id_caje = @id_caje) AND (id_cajovnika = @id_cajovnika); EXECUTE PrictiCaj @id_caje; RETURN
TopCaje Vyp´ıˇse ˇcaje s hodnocen´ım vyˇsˇs´ım nebo rovn´ ym neˇz dan´e ohodnocen´ı, implicitnˇe jen ˇcaje s nejvyˇsˇs´ım ohodnocen´ım. CREATE PROCEDURE [dbo].[TopCaje] @do_urovne int = 5 /* implicitne 5 */ AS SELECT c.jmeno, f.jmeno as firma, c.typ, c.datum_vlozeni, c.hodnoceni, c.obrazek FROM Caje c, Firmy f WHERE c.id_firmy = f.id_firmy AND c.hodnoceni >= @do_urovne; RETURN
6
Triggery Zmˇ ena poloˇ zky Datum vymeny v tabulce Cajovnici Pˇri updatu tabulky jsou nov´e hodnoty zmˇenˇen´ ych poloˇzek uloˇzeny v syst´emov´e tabulce INSERTED a star´e hodnoty v tabulce DELETED. CREATE TRIGGER logy_caje_pocet ON dbo.Caje FOR UPDATE AS BEGIN declare @old_pocet INTEGER SELECT @old_pocet = pocet_kusu FROM deleted declare @new_pocet INTEGER SELECT @new_pocet = pocet_kusu FROM inserted declare @id INTEGER select @id = id_caje from inserted
IF(@old_pocet < 2 AND @new_pocet >= 2) BEGIN DECLARE @datum DATETIME set @datum = (SELECT GETDATE()) select @datum as [dnesni datum a cas] UPDATE Caje SET datum_vymeny = @datum WHERE id_caje = @id END ELSE IF (@old_pocet >= 2 AND @new_pocet < 2) UPDATE Caje SET datum_vymeny = NULL WHERE id_caje = @id END
Odeˇ cten´ı kus˚ uˇ caje pˇ ri zamluven´ı Toto je realizov´ano triggerem na insert v tabulce Zamluvene. Je vol´ana procedura, kter´a odeˇcte jeden kus ˇcaje dan´eho id. ALTER TRIGGER Zamluveni ON dbo.Zamluvene FOR INSERT AS declare @id_caje int select @id_caje = id_caje FROM Inserted; execute OdectiCaj @id_caje;
7
Logov´ an´ı Zde byla snaha o neps´an´ı pˇr´ıliˇs k´odu v´ıckr´at, proto vzniklo nˇekolik procedur. V kaˇzd´em triggeru vypadal k´od podobnˇe, proto zde budou uvedeny triggery pouze z tabulky Caje: CREATE TRIGGER logy_caje_del ON dbo.Caje AFTER DELETE AS BEGIN declare @id_zaznamu INT select @id_zaznamu = $identity FROM DELETED; EXECUTE Loguj ’Caje’, ’D’, @id_zaznamu END; CREATE TRIGGER [dbo].[logy_caje_ins] ON dbo.Caje AFTER INSERT AS BEGIN declare @id_zaznamu INT select @id_zaznamu = $identity FROM INSERTED; EXECUTE Loguj ’Caje’, ’I’, @id_zaznamu END; CREATE TRIGGER logy_caje_up ON dbo.Caje AFTER UPDATE AS BEGIN select * INTO ##Tmp_ins from INSERTED select * into ##Tmp_del from DELETED EXECUTE VytvorLog ’Caje’ DROP TABLE ##Tmp_ins DROP TABLE ##Tmp_del END;
Pˇri updatu bylo potˇreba zjistit tak´e zmˇenˇen´ y sloupec a jeho starou a novou hodnotu, coˇz bylo dosaˇzeno pomoc´ı kurzor˚ u. Trigger pro update vol´a tedy nejprve proceduru VytvorLog, kter´a poˇzadovan´e hodnoty zjist´ı a pak aˇz zavol´a proceduru Loguj, kter´a uˇz pouze zapisuje do tabulky Logy. ALTER PROCEDURE dbo.VytvorLog ( @jmeno_tabulky NVARCHAR(50) ) AS declare @id INTEGER declare @nazev_sloupce NVARCHAR(255) declare @query NVARCHAR(255) declare @nova_hodnota NVARCHAR(100) declare @stara_hodnota NVARCHAR(100) declare @new_id_zaznamu INTEGER select @id = object_id
8
from sys.all_objects where name = @jmeno_tabulky declare sloupce cursor for select [name] from sys.all_columns where object_id = @id select @new_id_zaznamu = $identity FROM ##Tmp_ins; create table #Tmp_var( klic VARCHAR(50), hodnota VARCHAR(100) ) open sloupce fetch next from sloupce into @nazev_sloupce WHILE @@FETCH_STATUS = 0 BEGIN SET @query = ’INSERT INTO #Tmp_var(klic, hodnota) SELECT ’’1’’, ’+@nazev_sloupce+’ FROM ##Tmp_ins’ EXEC(@query) SET @query = ’INSERT INTO #Tmp_var(klic, hodnota) SELECT ’’2’’, ’+@nazev_sloupce+’ FROM ##Tmp_del’ EXEC(@query) SELECT @stara_hodnota = hodnota FROM #Tmp_var WHERE klic = ’1’ SELECT @nova_hodnota = hodnota FROM #Tmp_var WHERE klic = ’2’ IF(@stara_hodnota <> @nova_hodnota) EXECUTE Loguj @jmeno_tabulky = ’Caje’, @typ_zmeny = ’U’, @id_zaznamu = @new_id_zaznamu, @polozka = @nazev_sloupce, @nove = @nova_hodnota, @stare = @stara_hodnota fetch next from sloupce into @nazev_sloupce END close sloupce deallocate sloupce RETURN CREATE PROCEDURE dbo.Loguj ( @jmeno_tabulky NVARCHAR(20), @typ_zmeny CHAR(1), @id_zaznamu INTEGER, @polozka NVARCHAR(20) = NULL, @nove NVARCHAR(20) = NULL, @stare NVARCHAR(20) = NULL ) AS INSERT INTO Logy(jmeno_tabulky, typ_zmeny, uzivatel, id_zaznamu, polozka, nove, stare) VALUES (@jmeno_tabulky, @typ_zmeny, system_user, @id_zaznamu, @polozka, @nove, @stare); RETURN
9
