Hi, Solution is good only for dates and only in small range but considering date with time or numbers you see that solution is realy limited. As Tomasz say will be good to see „systematic solution”
And to check overlaping your sample is quite ok but is ineficient |---R1---| |-----------R2---------| |---R1---| |-----------R2---------| |---R1---| |-----------R2---------| |--------R1-------| |--R2--| Better is check when dates do not overlap and do negation – only 2 possibilities 😉 NOT (DATE_TO1<DATE_FROM2 OR DATE_TO2<DATE_FROM1) DATE_FROM1|--------------------|DATE_TO1 …………………………DATE_FROM2|--------------------|DATE_TO2 …………………………DATE_FROM1|--------------------|DATE_TO1 DATE_FROM2|--------------------|DATE_TO2 Pozdrawiam, Karol Bieniaszewski Od: 'Louis van Alphen' lo...@nucleo.co.za [firebird-support] Wysłano: środa, 12 września 2018 15:18 Do: firebird-support@yahoogroups.com Temat: RE: [firebird-support] Prevent overlaping dates in concurent environment The practicality of entering each date in the range into a table depends on the application. For short date ranges this should be fine, but very wide ranges will have a bit of a performance impact on inserts. Let’s say you want to book a resource for a date range. Then you could create tables like this: create table RESOURCE ( ID integer, NAME varchar(64) ); alter table RESOURCE add constraint RESOURCE_PK primary key (ID) using index RESOURCE_PK_IDX; create table RESOURCE_BOOKING ( ID integer, RESOURCE_ID integer, FROM_DAT DATE, TO_DAT DATE, ); alter table RESOURCE_BOOKING add constraint RESOURCE_BOOKING_PK primary key (ID) using index RESOURCE_BOOKING_PK_IDX; create index RESOURCE_BOOKING_RESOURCE_IDX on RESOURCE_BOOKING(RESOURCE_ID); create table RESOURCE_BOOKING_DETAIL ( BOOKING_ID integer, RESOURCE_ID integer, BOOKED_DAT DATE ); alter table RESOURCE_BOOKING_DETAIL add constraint RESOURCE_BOOKING_DETAIL_UNQ unique (RESOURCE_ID,BOOKED_DAT) using index RESOURCE_BOOKING_DETAIL_UNQ_IDX; create index RESOURCE_BOOKING_DETAIL_IDX on RESOURCE_BOOKING_DETAIL(BOOKING_ID); The RESOURCE_BOOKING_DETAIL_UNQ unique constraint will ensure that a RESOURCE cannot be booked more than once for the same day. The associated index is also useful for selecting the days a RESOURCE is booked for. The RESOURCE_BOOKING_DETAIL_IDX is useful for when you want to delete a BOOKING by its ID. Two ranges may overlap in 4 ways and you have to check all 4 scenarios to detect an overlap. There is no way to utilise standard DB constraints to validate this. I have written some PSQL SP to validate the 4 cases, but I normally do this in my domain layer. This means there is no guarantee that overlap does not exist in a concurrent environment. The range overlap cases are as follows (not sure if the email spacing will be preserved): |---R1---| |-----------R2---------| |---R1---| |-----------R2---------| |---R1---| |-----------R2---------| |--------R1-------| |--R2--| From: firebird-support@yahoogroups.com [mailto:firebird-supp...@yahoogroups...com] Sent: Wednesday, 12 September, 2018 14:41 To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] Prevent overlaping dates in concurent environment Hi Tomasz! Tested on 2.5.8, dialect 1: CREATE TABLE RESERVED_DATE ( RES_DATE DATE NOT NULL ); ALTER TABLE RESERVED_DATE ADD CONSTRAINT RESERVED_DATE_PK PRIMARY KEY (RES_DATE); CREATE TABLE MEETINGS ( DATE_FROM DATE NOT NULL, DATE_TO DATE NOT NULL ); CREATE OR ALTER TRIGGER MEETINGS_BI FOR MEETINGS ACTIVE BEFORE INSERT POSITION 0 as declare variable curr_date date; begin curr_date = new.date_from; while (curr_date <= new.date_to) do begin insert into reserved_date (res_date) values (:curr_date); curr_date = dateadd(1 day to curr_date); end end run on first transaction: insert into MEETINGS (DATE_FROM, DATE_TO) values ('2018.09.01', '2018.09.10') run parallel on secound transaction: insert into MEETINGS (DATE_FROM, DATE_TO) values ('2018.09.08', '2018.09.15') violation of PRIMARY or UNIQUE KEY constraint "RESERVED_DATE_PK" on table "RESERVED_DATE". Problematic key value is ("RES_DATE" = '8-SEP-2018'). At trigger 'MEETINGS_BI' line: 9, col: 7. András From: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com> Sent: Wednesday, September 12, 2018 1:59 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Prevent overlaping dates in concurent environment On 12.09.2018 at 13:29, Omacht András aoma...@mve.hu [firebird-support] wrote: > Create a (reserved_dates) table with date field, and make that field unique.. > When a user inserts a date into the reservation table a trigger immadiate > inserts this date to the reserved_dates table too. Then the unique key will > stop secound insert instead of the first transaction is not commited. This won't work. All dates may be different and the intervals may still overlap. Karol, that's an interesting issue and I'm really curious if there's a clever solution. So far I've checked the check (pun intended): create table TST1 ( d1 timestamp, d2 timestamp, constraint no_overlap check ( not exists ( select * from TST1 t1 where exists ( select * from TST1 t2 where t1.d1 between t2.d1 and t2.d2 or t1.d2 between t2.d1 and t2.d2 ) ) ) ); insert into TST1 values('01.01.2018', '30.09.2018'); -- in transaction A insert into TST1 values('01.02.2018', '30.10.2018'); -- in transaction B -- commit A (no errors) -- commit B (no errors) and it doesn't work. You can still insert two overlapping pairs and both transactions get committed without errors, resulting in overlapping intervals being inserted. So, unless you change the transaction isolation level (I always use read committed), I don't have more ideas at the moment. have a good one Tomasz -- __--==============================--__ __--== Tomasz Tyrakowski ==--__ __--== SOL-SYSTEM ==--__ __--== http://www.sol-system.pl ==--__ __--==============================--__ __________ Information from ESET Mail Security, version of virus signature database 18037 (20180912) __________ The message was checked by ESET Mail Security. http://www.eset.com [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
