Subject: Re: RELATED-TO vs CHILD/PARENT

From: "John Stracke" <[email-masked]>
Date: Thu, 10 Jan 2002 16:42:58 -0500

>"Type of properties"? There is no type of property that
>can not be stored into SQL that we have defined.
>I do not know what you mean.

Well, obviously, all the iCalendar properties can be stored as strings 
(since we represent them as strings in a text/calendar message).  However, 
the string form may not be queryable in a useful way.  For example, if you 
have a property of type PERIOD, stored as a string, there's no way to make 
a SQL query that says "if this period includes that date".  To make that 
work, you'd have to either (a) represent the PERIOD as two separate 
columns, (b) extend CAP-SQL to support columns containing structured data, 
and then represent the PERIOD as a structure with two fields (start and 
end), or (c) extend CAP-SQL with operators that know about PERIODs.

At a higher level, SQL cannot represent iCalendar properties, such as 
ATTENDEE, which may appear more than once in the same component.  If I do:

SELECT ATTENDEE FROM VEVENT WHERE ATTENDEE.PARTSTAT='ACCEPTED'

then it's unclear what I should get.  By SQL rules, if VEVENT is a table, 
then the WHERE clause is selecting whole rows (VEVENT components) where 
ATTENDEE.PARTSTAT='ACCEPTED'.  If there are N ATTENDEE properties, I guess 
we can take it that this means "where at least one ATTENDEE has accepted". 
 But, then, the SELECT ATTENDEE part sees every row (event) that contains 
at least one such ATTENDEE.  So you'd get back a set of VEVENT components, 
each containing ATTENDEE properties and nothing else--and some of those 
ATTENDEE properties would *not* have PARTSTAT=ACCEPTED, because the WHERE 
applies only on a per-event basis.  But that's highly counterintuitive; I 
can easily see an implementor thinking it meant "return just the ATTENDEE 
properties that match".

Worse, what does this mean:

SELECT * FROM VEVENT WHERE ATTENDEE.PARTSTAT='ACCEPTED' AND 
ATTENDEE=[email-masked]'

? This looks like it's meant to find all events I've accepted; but I don't 
think it will work.  If we take the same "at least one" approach we did 
above, then this means "where John has been invited and where at least one 
person has accepted".  Moreover, I don't think I *can* come up with a way 
to write a query that means "all the events John has accepted", without 
extending SQL to cope with multivalues.

I'm starting to think that SQL might be both too powerful (joins) and too 
crude (limited data types) to serve as a query language for CAP.

/===========================================================\
|John Stracke                   |Principal Engineer         |
[email-masked]  |Incentive Systems, Inc.    |
|http://www.incentivesystems.com|My opinions are my own.    |
|===========================================================|
|Anyone who has a conditioned response immediately thinks of|
|Pavlov.                                                    |
\===========================================================/

---

From: Doug Royer <[email-masked]>
Date: Thu, 10 Jan 2002 16:35:41 -0700

John Stracke wrote:
> Well, obviously, all the iCalendar properties can be stored as
> strings...

True, but we are not specifying that you use an SQL engine for
the backend store. So that becomes implementation specific.

---

From: Patrice Lapierre <[email-masked]>
Date: 10 Jan 2002 19:06:56 -0500

>   ATTENDEE=[email-masked]'

...
> crude (limited data types) to serve as a query language for CAP.

  That is very good point. 

  Another related, but simpler limitation of SQL-MIN is the inability to
query on a property with multiple values such as CATEGORIES (beside an
exact match of the list).  

Should SQL-MIN be extended to support this? 

How would this be done in SQL-92 (array, string & LIKE operator)?

A property with multiple values could be viewed as multiple single-value
occurrences of the property (assuming ordering is irrelevant). Should
the same solution be used to solve the two problems?

---

From: Doug Royer <[email-masked]>
Date: Thu, 10 Jan 2002 18:46:22 -0700

> Patrice Lapierre wrote:
>   Another related, but simpler limitation of SQL-MIN is the inability
> to query on a property with multiple values such as CATEGORIES (beside
> an exact match of the list).
> 

I say NO.

You can query for the days (or whatever) events, and the CUA can
look at the CATEGORIES or it can use SQL-92. A CUA can
get its job done without it - that makes it a no for
being in SQL-MIN.

> How would this be done in SQL-92 (array, string & LIKE operator)?

Not an array.
We would have to assume that a CS could store the data
in a text string "as is" for all TEXT values.

> A property with multiple values could be viewed as multiple
> single-value occurrences of the property (assuming ordering is
> irrelevant). Should the same solution be used to solve the two
> problems?

Some properties are specified as single instance.
If it is true that all multi value properties could also
be multi instance properties - that would work.
If it is not true, then I don't think so.

---

From: Patrice Lapierre <[email-masked]>
Date: 11 Jan 2002 08:30:28 -0500

> get its job done without it - that makes it a no for

  I suspect that searches on CATEGORIES to be often used by a CUA.

For example: move all my VEVENTs in the category "BUSINESS" to
a particular VAGENDA. 

 Yes it could be done by the CUA (1. search, 2. filter, 3. move using a
list of UIDs), but it should be done the server side with a single
"move" command.

  And it's not event clear to me how this should be done in SQL-92.

> in a text string "as is" for all TEXT values.

  The LIKE operator of SQL-92 may not do what we need.
CATAGORIES are a list delimited by ','. 

Using a query such as:

  SELECT * FROM VEVENT WHERE CATEGORIES LIKE '%Holiday%'

  Can return unwanted VEVENTs that belongs in unwanted 
categories (e.g. "Holiday Cards").

note: The categories "Holiday" and "Holiday Cards" both appears 
in the default category list of a well known CUA.