Korišćenje UDF funkcija u CHECK uslovima – rezervacija soba
Wednesday, 05.03.2008 – ZidarPošto mi se mnogo dopalo korišćenje UDF funkcija u CHECK uslovima, pokušao sam da odgovorim na pitanje, koje se često sreće na forumima: rezervacija soba bez prebukiranja. Evo kako može da se reši:
Kreiramo tabelu u koju se upisuju rezervacije soba u jednom hotelu.
Tabela ima 3 kolone (Sobe, ZauzetoOD, ZauzetoDo)
– Soba ne sme da se prebukira. Drugim rečima, interval zauzeća (ZauzetoOd, ZauzetoDo) ne sme da se preklopi ni sa jednim drugim intervalom zauzeca za posmatranu sobu.
– Na dan odlaska jednog gosta iz sobe, drugi gost može ući u sobu.
– Sobe se napuštaju do 11AM, a preuzimaju u 1PM.
– Vreme između 11 i 1 se koristi za pripremu sobe za novog gosta. Znači, preklapanje na krajevima intervala se dopušta.
CREATE TABLE Rezervacije ( Soba int NOT NULL , ZauzetaOd smalldatetime NOT NULL , ZauzetaDo smalldatetime NOT NULL PRIMARY KEY (Soba, ZauzetaOd, ZauzetaDo) ) GO
INSERT INTO Rezervacije VALUES(100,'20080115','20080125') INSERT INTO Rezervacije VALUES(100,'20080126','20080130') INSERT INTO Rezervacije VALUES(100,'20080205','20080215') GO SELECT * FROM Rezervacije
Soba Transakcija ZauzetaOd ZauzetaDo ----------- ----------- ------------------------------------------------------ -------------------- 100 1 2008-01-15 00:00:00 2008-01-25 00:00:00 100 2 2008-01-26 00:00:00 2008-01-30 00:00:00 100 3 2008-02-05 00:00:00 2008-02-15 00:00:00
(3 row(s) affected)
Šta treba proveriti da bi se sprečilo preklapanje intervala?
Dva intervale (a,b) i (x,y) mogu da se preklapaju u sledecim slučajevima:
--A------B-------------------> -----X---------Y------------->
--------A------------B-------> ----X----------Y------------->
-------A-------------B-------> -----------X---Y------------->
-----------A---B-------------> -------X--------------Y------>
Ni jedan od ovih slučajeva nije dozvoljen. Mogli bismo da pokušamo da
napišemo izraz za preklapanje ovako:
Intervali (A,B) i (X,Y) se preklapaju kada je zadovoljeno
(
B Between X AND Y
OR
A Between X AND Y
OR
X Between A AND B
)
Ako uvedemo dodatni uslov, da se soba može preuzeti istog dana kada je
prethodni gost napušta, ne možemo više da koristimo BETWEEN.
U tom slučaju izraz više nije čitljiv, pa nam treba nešto jednostavnije.
Za pisanje, jednostavniji je dvostruko negativni uslov.
Dva intervala se NE preklapaju ako (A,B) leži potpuno van (X,Y)
Može se reći “Dva intervala se preklapaju, ako ni jedan ne leži potpuno izvan drugoga”
To je samo u dva slučaja:
(A,B) se završio pre nego što je (X,Y) počeo:
--------A---B----X--------Y----------->
(A,B) počinje tek kada je (X,Y) završen
-----------------X--------Y---A---B--->
Preklapanje: (NOT (B<=X OR A>=Y)) (1)
nema preklapanja: (B<=X OR A>=Y) (2)
Ako upotrebimo uslov (2) možemo napisati upit, koji prebrojava intervale koji se preklapaju sa (a,b):
DECLARE @a smalldatetime, @b smalldatetime, @Soba int SELECT @a = '2008-01-31', @b = '2008-02-04', @Soba =100 -- X = ZauzetaOd, Y = ZauzetaDo
SELECT COUNT(*) FROM Rezervacije WHERE (NOT ( (@b <= ZauzetaOd) OR (@a >= ZauzetaDo) )) AND Soba = @Soba
----------- 1
(1 row(s) affected)
Znači, ne želimo da dopustimo novu rezervaciju ako kveri
SELECT COUNT(*) FROM Rezervacije WHERE (NOT ( (@b <= ZauzetaOd) OR (@a >= ZauzetaDo) )) AND Soba = @Soba
vraća broj veći od nule.
CHECK constraint se proverava nakon unošenja novog reda u tabelu.
Ako je ograničenje narušeno, transakcija se odbacuje.
Zbog toga će navedeni kveri uvek vratiti barem broj 1 (jer svaki red se preklapa sa samim sobom).
Da to izbegnemo, zahtevaćemo da kveri ne vraća proveravani red.
Funkcija izgleda ovako:
CREATE FUNCTION dbo.PreklapanjeIntervala ( @a smalldatetime, @b smalldatetime, @Soba int) RETURNS smallint -- 0 = nema preklapanja, 1 = ima preklapanja AS BEGIN DECLARE @Rezultat smallint
SET @Rezultat = ( SELECT COUNT(*) FROM Rezervacije WHERE (Soba = @Soba) -- sve radimo za tekucu sobu -- ovo bi trebalo da eliminise proveru samog sebe: AND NOT (Soba=@Soba AND ZauzetaOd=@a AND ZauzetaDo = @b) -- napokon provera preklapanja: AND NOT ( (@b <= ZauzetaOd) OR (@a >= ZauzetaDo) ) ) RETURN @Rezultat END GO
Sada može ovo:
SELECT * FROM Rezervacije
Soba ZauzetaOd ZauzetaDo ----------- ------------------------------------------------------ --------------------- 100 2008-01-15 00:00:00 2008-01-25 00:00:00 100 2008-01-26 00:00:00 2008-01-30 00:00:00 100 2008-02-05 00:00:00 2008-02-15 00:00:00
(3 row(s) affected)
ALTER TABLE Rezervacije DROP CONSTRAINT ck_NeSmeSePrebukiratiSoba GO -- CHECK CONSTRAINT koji sprecava prebukiranje soba : ALTER TABLE Rezervacije ADD CONSTRAINT ck_NeSmeSePrebukiratiSoba CHECK ( dbo.PreklapanjeIntervala(ZauzetaOd, ZauzetaDo, Soba) = 0 ) GO
SELECT * FROM Rezervacije Soba ZauzetaOd ZauzetaDo ----------- ------------------------------------------------------ -------------------- 100 2008-01-15 00:00:00 2008-01-25 00:00:00 100 2008-01-26 00:00:00 2008-01-30 00:00:00 100 2008-02-05 00:00:00 2008-02-15 00:00:00
(3 row(s) affected)
Pokušajmo da prebukiramo sobu: INSERT INTO Rezervacije VALUES(100,'2008-02-01','2008-02-04') (1 row(s) affected)
INSERT INTO Rezervacije VALUES(100,'2008-01-20','2008-02-04') Server: Msg 547, Level 16, State 1, Line 1 INSERT statement conflicted with TABLE CHECK constraint 'ck_NeSmeSePrebukiratiSoba'. The conflict occurred in database 'Zadaci', table 'Rezervacije'. The statement has been terminated.
INSERT INTO Rezervacije VALUES(200,'2008-01-20','2008-02-04') (1 row(s) affected)
SELECT * FROM Rezervacije Soba ZauzetaOd ZauzetaDo ----------- ------------------------------------------------------ --------------------- 100 2008-01-15 00:00:00 2008-01-25 00:00:00 100 2008-01-26 00:00:00 2008-01-30 00:00:00 100 2008-02-01 00:00:00 2008-02-04 00:00:00 100 2008-02-05 00:00:00 2008-02-15 00:00:00 200 2008-01-20 00:00:00 2008-02-04 00:00:00
(5 row(s) affected)
INSERT INTO Rezervacije VALUES(300,'2008-01-20','2008-02-04')
(1 row(s) affected)
Da probamo sa sobom 200:
INSERT INTO Rezervacije (Soba, ZauzetaOd, ZauzetaDo) VALUES (200, '2008-02-04','2008-02-20') (1 row(s) affected)
SELECT * FROM Rezervacije Soba ZauzetaOd ZauzetaDo ----------- ------------------------------------------------------ -------------------- 100 2008-01-15 00:00:00 2008-01-25 00:00:00 100 2008-01-26 00:00:00 2008-01-30 00:00:00 100 2008-02-01 00:00:00 2008-02-04 00:00:00 100 2008-02-05 00:00:00 2008-02-15 00:00:00 200 2008-01-20 00:00:00 2008-02-04 00:00:00 200 2008-02-04 00:00:00 2008-02-20 00:00:00 300 2008-01-20 00:00:00 2008-02-04 00:00:00
(7 row(s) affected)
Unesimo ponovo isti interval:
INSERT INTO Rezervacije (Soba, ZauzetaOd, ZauzetaDo) VALUES (200, '2008-02-04','2008-02-20') Server: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK__Rezervacije__239E4DCF'. Cannot insert duplicate key in object 'Rezervacije'. The statement has been terminated.
Pokušajmo ponovo približno isti interval:
INSERT INTO Rezervacije (Soba, ZauzetaOd, ZauzetaDo) VALUES (200, '2008-02-08','2008-02-20')
Server: Msg 547, Level 16, State 1, Line 1 INSERT statement conflicted with TABLE CHECK constraint 'ck_NeSmeSePrebukiratiSoba'. The conflict occurred in database 'Zadaci', table 'Rezervacije'. The statement has been terminated.
SELECT * FROM Rezervacije Soba ZauzetaOd ZauzetaDo ----------- ------------------------------------------------------ ------------------------------------------------------ 100 2008-01-15 00:00:00 2008-01-25 00:00:00 100 2008-01-26 00:00:00 2008-01-30 00:00:00 100 2008-02-01 00:00:00 2008-02-04 00:00:00 100 2008-02-05 00:00:00 2008-02-15 00:00:00 200 2008-01-20 00:00:00 2008-02-04 00:00:00 200 2008-02-04 00:00:00 2008-02-20 00:00:00 300 2008-01-20 00:00:00 2008-02-04 00:00:00
(7 row(s) affected)
Šta ako naknadno promenimo neku rezervaciju, tako da se narušava zabrana dvostrukog bukiranja?
Pokušajmo prvu rezervaciju za sobu 100 (15 januar – 25 Januar 2008) da produžimo do 1 februara 2008.
Ista soba je već bukirana od 26 Januara do 30 januara.
UPDATE Rezervacije SET ZauzetaDo = '2008-02-01' WHERE Soba = 100 AND ZauzetaOd = '2008-01-15' AND ZauzetaDo = '2008-01-25'
Server: Msg 547, Level 16, State 1, Line 1 UPDATE statement conflicted with TABLE CHECK constraint 'ck_NeSmeSePrebukiratiSoba'. The conflict occurred in database 'Zadaci', table 'Rezervacije'. The statement has been terminated.
Pokazali smo dve stvari – kako se proverava preklapanje intervala i kako se to koristi u funkciji da se obebedi zabrana prebukiranja soba.
🙂
One Response to “Korišćenje UDF funkcija u CHECK uslovima – rezervacija soba”
e ovo mi je bas trebalo!!!
Bas radim nesto sa rezervacijama i vremenskim intervalima pa ce mi tvoj primer biti od pomoci. Samo nastavi sa ovakvim primerima!!1
By Milan on Mar 11, 2008