Thread: diary constraints

Hi folks

I know this has been discussed in the past, but no amount of keywords 
has returned anything from the archives.

I want to create a courtesy car diary diary system where I have a table 
containing all of the cortesy cars in the pool, and then an allocation 
table which has two timestamps, one for the start date/time and one for 
the return date/time.

How do I go about creating constraints on inserts and updates to ensure 
that

a) the finish is after the start
b) two allocations for a single vehicle don't overlap.
-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     

Hi Gary,
I've actually just done the same thing - but for renting property. I've implemented the constraint as a trigger (Before
insert/update,for each row), that first checks if the start_date is < end_date, and then performs a select on the
bookingstable using the OVERLAPS function. If there are more than 0 records returned, an exception is raised. 
I've included the code below. You shouldn't need too many changes to adapt it to your needs!

/* This trigger function is responsible for ensuring temporal integrity
within the calendar_entries table (And it's children). It ensures that
only entries with no overlapping entries. */


BEGIN

/* First, check that the start_date > end_date */

IF NEW.start_date > NEW.end_date THEN
RAISE EXCEPTION 'ERROR: start_date must not be greater than end_date';
END IF;

IF EXISTS (

SELECT 1
FROM calendar_entries
WHERE ((start_date,end_date) OVERLAPS (NEW.start_date, NEW.end_date))
AND property_id = NEW.property_id LIMIT 1
)

THEN
RAISE EXCEPTION 'cannot add booking - overlapping calendar entries detected';
END IF;

RETURN NEW;

END;



This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private
information. If you have received it in error, please notify the sender immediately and delete the original.  Any other
useof the email by you is prohibited. 

On Aug 23, 2005, at 5:33 PM, Gary Stainburn wrote:

> I want to create a courtesy car diary diary system where I have a  
> table
> containing all of the cortesy cars in the pool, and then an allocation
> table which has two timestamps, one for the start date/time and one  
> for
> the return date/time.
>
> How do I go about creating constraints on inserts and updates to  
> ensure
> that
>
> a) the finish is after the start
> b) two allocations for a single vehicle don't overlap.

This is an interesting problem. You might want to take a look at this  
book, previously mentioned on the one of the lists (by George Essig,  
I believe):

> Developing Time-Oriented Database Applications in SQL
> by Richard T. Snodgrass
>
> The book is out of print, but the author has made the PDF available  
> on his website at:
> http://www.cs.arizona.edu/people/rts/tdbbook.pdf

Hope this helps!

Michael Glaesemann
grzm myrealbox com

> a) the finish is after the start

well, finish > start

> b) two allocations for a single vehicle don't overlap.

this one is a bit tricky !

- Check that there is no allocation in the table whose time period start,  
end includes either the start of the end of the reservation to insert, and  
that the time period of the reservation to insert does not contain either  
the start or end of any reservation in the table.

This is 4 selects, playing with order by limit 1 and indexes, it will be  
fast.

am  23.08.2005, um  9:33:58 +0100 mailte Gary Stainburn folgendes:
> Hi folks
> 
> I know this has been discussed in the past, but no amount of keywords 
> has returned anything from the archives.
> 
> I want to create a courtesy car diary diary system where I have a table 
> containing all of the cortesy cars in the pool, and then an allocation 
> table which has two timestamps, one for the start date/time and one for 
> the return date/time.
> 
> How do I go about creating constraints on inserts and updates to ensure 
> that
> 
> a) the finish is after the start

with a check-constraint like this:
create table foobar (t1 timestamp, t2 timestamp check (t2>t1));


> b) two allocations for a single vehicle don't overlap.

possibly with a trigger.


Regards, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net===    Schollglas Unternehmensgruppe    ===