Re: A neutral challenge.
Date: 28 Jun 2003 12:39:11 -0700
Message-ID: <cd3b3cf.0306281139.63f9b7ed_at_posting.google.com>
Lauri Pietarinen <lauri.pietarinen_at_atbusiness.com> wrote in message news:<3EFD52A3.C699C075_at_atbusiness.com>...
> Bob Badour wrote:
>
> > > I think using types for this purpose is a slight overkill. It would be
> > > easy to add a 'day of week' and
> > > a 'day of week in month' column into materialised calendar and just use
> > > them in selection criterias.
> >
> > This would complicate queries too much. If one wants to see the events on
> > thursday, one would have to query three or more columns instead of querying
> > a single interval column.
>
> OK, maybe I am a bit to used to thinking in SQL. How would your solution
> look like, using types?
>
> Lauri
I won't be able to provide a complete solution right away--partly because I have more pressing tasks to complete and partly because I have not fully developed my ideas regarding recasting intervals from one type to another.
As a broad overview, however, the tuple describing a calendar event would have a single interval attribute describing when the event occurs. Since intervals are sets of points that need not be contiguous, a single interval can describe an event occuring over multiple days at the same time of day or even at different times of day.
If one wants to query all of the events that occur on some day, one selects an appropriate interval representing the day and returns all events whose interval overlaps. In fact, the interval of interest need not represent a day; it could just as easily represent a minute or a year.
So, if we have an EVENT relation that has an OCCURS interval describing when the event occurs and one wants to derive a relation of those events occuring during an interval, i1, as well as when the event occurs in the interval:
WITH ( EVENT WHERE OCCURS OVERLAPS i1 ) AS T1,
( EXTEND T1 ADD ( OCCURS INTERSECT i1 ) AS OCCURS_IN_INTERVAL ) AS
T2:
PACK T2 ON OCCURS_IN_INTERVAL
As for types, consider an ordered supertype P with an ordered subtype
C. (I chose P for parent and C for child.)
While C is a point type, it seems to me that any value of C defines at least three intervals of P. Let's consider a value of C and call it C0. Because C is a subtype of P, C0 is also a value of P. If one considers the longest continuous interval of P that begins at C0 and contains no other values of C, C0 defines one interval of P. Similarly, C0 defines a longest continuous interval of P ending at C0. Finally, because C0 is a value of P, it defines an interval beginning and ending at C0.
It might make sense to have generic operations that cast a value of a subtype to an interval of the supertype. Are the intervals defined above equally important? Does C0 define any other important intervals of P? What would we call these generic cast operations? Then again, it might not make sense in the first place.
Assuming it does make sense, since INTERVAL_C selects a type comprising a set of C values, if we have generic operations to cast C values to INTERVAL_P values, it is possible to define operations to cast INTERVAL_C values to INTERVAL_P values by taking the union of the INTERVAL_P values derived by casting each of the C values in the INTERVAL_C value to an INTERVAL_P value.
So, for instance, if we have a TIME type with a DAY subtype, one could cast a DAY value to an interval of TIME representing the DAY. Likewise, one could cast an interval of DAY to an interval of TIME representing the days in the interval.
Then again, I might be wrong to consider DAY a subtype of TIME in the first place. Perhaps DAY is a different type entirely that simply defines an operation resulting in an interval of TIME representing the DAY. Regardless whether the operations are generic or explicit, one might precede the query above with any of the following:
i1 := INTERVAL_TIME(DAY('2003/06/26'));
i1 := INTERVAL_TIME(YEAR(2003));
i1 := INTERVAL_TIME(MINUTE('2003/06/28 15:25'));
i1 := INTERVAL_TIME(INTERVAL_MINUTE(['2003/06/28 15:30','2003/06/28
15:45']));
i1 := INTERVAL_TIME(INTERVAL_THIRD_THURSDAY(['2003/06/19','2003/12/18']); i1 := INTERVAL_TIME(['2003/06/28 15:30:12.000','2003/06/28 15:32:16.500']);
(I assume above that TIME resolves to milliseconds.)
Regards,
Bob
Received on Sat Jun 28 2003 - 21:39:11 CEST