Subject: Proposal for non-SQL query language

From: "John Stracke" <[email-masked]>
Date: Tue, 15 Jan 2002 17:48:28 -0500

This is my counterproposal to get us free of the SQL swamp.

The Problem:

SQL is an inappropriate query language for CAP.  The major problem is
that the SQL relational model does not mesh well with the iCalendar
data model.  For example, in the relational model, there is no such
thing as structured data; all structure must be encoded explicitly in
the data.  Similarly, there is no multivalued data; multiple values
must be stored in separate tables, and the relationships encoded
explicitly.  Since iCalendar makes heavy use of structure and multiple
values, using standard SQL would require the CUA developer to write
complex queries spanning multiple tables.  Proposals have been made to
use some form of extended SQL, but any such extension will require
some hard work to analyze how it interacts with all of SQL's various
capabilities.

The Alternative:

We should abandon SQL and develop a simple query syntax which matches
the iCalendar data model.  The structure of the query would reflect
the structure of the iCalendar data.

The Proposal:

(Notes: (a) I am aware that this proposal is not fully rigorous; that
will come if people are interested.  (b) The use of XML syntax here is
purely a convenient way to get the point across; I don't insist on
it.)

I'll call this query language iQL, iCalendar Query Language.  I don't
insist on the name, of course.

An iQL query consists of three specifications: the source(s) (what
sorts of components to search for), the constraint (the test that a
component must match to be returned), and the targets (which
properties to return from each component).  I'll write them like this:

<query>
 <sources>
  <source name="VEVENT"/>
  <source name="VTODO"/>
 </sources>

 <constraint>
 ...details below...
 </constraint>

 <targets>
  <target name="DTSTART"/>
  <target name="DTEND"/>
  <target name="UID"/>
 </targets>
</query>

The <targets/> element may instead contain an <all/> element,
specifying that all properties should be returned.

The <constraint> element, of course, is the hardest part.  A DTD
fragment:

<!ELEMENT constraint (property | subcomponent)*>

<!ELEMENT property (parameter | value)*>
<!ATTLIST property name CDATA #REQUIRED>

<!ELEMENT parameter (value)*>
<!ATTLIST parameter name CDATA #REQUIRED>

<!ELEMENT value (is | contains | starts-with | ends-with
 | is-after | is-before | is-within)>
<!ELEMENT is (#PCDATA)>
<!ELEMENT contains (#PCDATA)>
<!ELEMENT starts-with (#PCDATA)>
<!ELEMENT ends-with (#PCDATA)>
<!ELEMENT is-after (#PCDATA)>
<!ELEMENT is-before (#PCDATA)>
<!ELEMENT is-within (#PCDATA)>

<!ELEMENT subcomponent (property)*>
<!ATTLIST subcomponent type CDATA #REQUIRED>

So, now we have tospecify what all this means.  The general approach
here is recursive descent: to see if a component or property matches a
constraint, check to see whether the component or property's children
match the constraint's children.

A component V matches a <constraint> C if (a) for each <property>
child element E of C, there exists at least one property P that
matches E; and (b) for each <subcomponent> child element E of C, there
exists at least one subcomponent S that matches E.  (Note that it is
legal for there to be no <property> or <subcomponent> elements; in
that case, the CUA is asking for all components.)

A property P matches a <property> element E if (a) the name of P is
equal to the name attribute of E; (b) the value of P matches every
<value> child element of E (if any); and (c) for every <parameter>
child element E1 of E, P has at least one parameter P1 that matches
E1.

A subcomponent S matches a <subcomponent> element E if (a) the type of
S (e.g., VALARM) is equal to the type attribute of E; and (b) for
every <property> child element E1 of E, there exists at least one
property P that matches E1.

A string (the value of a property or parameter) matches a <value>
element if it matches the element's single child: <is>, <contains>
<starts-with>, <ends-with>, <is-after>, <is-before>, or <is-within>.

A string S matches an <is> element E if S is exactly equal to E's
CDATA content.

A string S matches a <contains> element E if S contains a substring
which is exactly equal to E's CDATA content.

A string S matches a <starts-with> element E if S starts with a
substring which is exactly equal to E's CDATA content.

A string S matches a <ends-with> element E if S ends with a substring
which is exactly equal to E's CDATA content.

A string S matches an <is-before> element E if (a) S is a date or
date-time value; (b) E's CDATA content (call it X) is also a date or
date-time value (must be the same type as S); and (c) S's value is
earlier than X's.

A string S matches an <is-after> element E if (a) S is a date or
date-time value; (b) E's CDATA content (call it X) is also a date or
date-time value (must be the same type as S); and (c) S's value is
later than X's.

A string S matches an <is-within> element E if (a) S is a date or
date-time value; (b) E's CDATA content (call it X) is a period value;
and (c) S's value is within X's.

(In the case of <is-before>, <is-after>, and <is-within>, I expect
that the specified target value will have to be in GMT.)

We need some rules to match the iCalendar semantics, too:

 * When a <property> element's name attribute is DTSTART or DTEND, and
the event has multiple instances, the event matches if any instance
matches.

 * If a component has a DTSTART and a DTEND, but no DURATION, all
queries which reference the DURATION property consider the component
to have a DURATION property equal to the difference between DTSTART
and DTEND.

 * Similarly, if a component has a DTSTART and a DURATION, but no
DTEND, all queries which reference the DTEND property consider the
component to have a DTEND property equal to DTSTART plus DURATION.

 * As far as iQL is concerned, the TRIGGER property of a VALARM is
always of date-time type; if it's specified as a duration, iQL queries
see the computed date-time.

Examples:

A query find the UIDs of all VEVENTs and VTODOs which start sometime
on 15 January 2002:

<query>
 <sources>
  <source name="VEVENT"/>
  <source name="VTODO"/>
 </sources>

 <targets>
  <target name="UID"/>
 </targets>

 <constraint>
  <property name="DTSTART">
   <value><is-within>20020115T000000Z/P24H</is-within></value>
  </property>
 </constraint>
</query>

A query to find the SUMMARY and the UID of any VEVENT for which
[email-masked]'s PARTSTAT is NEEDS-ACTION:

<query>
 <sources>
  <source name="VEVENT"/>
 </sources>

 <targets>
  <target name="UID"/>
  <target name="SUMMARY"/>
 </targets>

 <constraint>
  <property name="ATTENDEE">
   <value><is>[email-masked]</is></value>
   <parameter 
name="PARTSTAT"><value><is>NEEDS-ACTION</is></value></parameter>
  </property>
 </constraint>
</query>

A query to get all the properties for a particular VEVENT, specified
by UID:

<query>
 <sources>
  <source name="VEVENT"/>
 </sources>

 <targets>
  <all/>
 </targets>

 <constraint>
  <property name="UID">
   <value><is>[email-masked]</is></value>
  </property>
 </constraint>
</query>

A query to get the UID and the VALARM subcomponent of any VEVENT or
VTODO which has an alarm set in the next 60 minutes (assuming the
current time is 5:30 PM GMT on 15 January 2002):

<query>
 <sources>
  <source name="VEVENT"/>
  <source name="VTODO"/>
 </sources>

 <targets>
  <target name="UID"/>
  <target name="VALARM"/>
 </targets>

 <constraint>
  <subcomponent type="VALARM">
   <property name="TRIGGER">
    <value><is-within>20020115T173000Z/P1H</is-within></value>
   </property>
 </constraint>
</query>

Further Refinements:

It might be useful for <targets> to be able to specify just certain
properties of a subcomponent; for example, one might write
<target name="VALARM.TRIGGER"/>.

We'll probably want to be able to specify OR and NOT conditions; those
should be fairly simple to add.

Conclusion:

This approach is simple to implement (whether or not one's backend is
SQL-based), simple for a CUA to use (it doesn't have to understand a
SQL-compatible virtual data model; it just has to understand
iCalendar), and has no hidden baggage buried in an ISO standard most
of the working group hasn't read.

/==============================================================\
|John Stracke                   |Principal Engineer            |
[email-masked]  |Incentive Systems, Inc.       |
|http://www.incentivesystems.com|My opinions are my own.       |
|==============================================================|
|All I ask is a chance to prove that money can't make me happy.|
\==============================================================/

---

From: Doug Royer <[email-masked]>
Date: Tue, 15 Jan 2002 16:18:34 -0700

> This is my counterproposal to get us free of the SQL swamp.

Great idea starter for the xml-ical proposal. But it does not conform
to the CAP-requiements that it be a iCalendar data object.

And I think the latest proposal will work from Patrice, Alan,
you, and others without throwing the entire CAP
query issue open for a LONG debate. This one has produced
what I think will work. And although it is not a FULL query
language, I think it does address the issues that have been
brought up over the last few months. I think it meets the
CAP requirements, and I think that it is implementable.

I think the REAL issue has been a lack of debate. So far
everyone seemed to just accept what I proposed for the
last 2 years. Patrice and Alan seem to be trying to use it.

It is amazing how you can write code and test it, but if
you never see it as others are going to use it, it does not
look broken. I think that we can go forward and address
the next in the sequence of almost never  looked at text :-)

---

From: Patrice Lapierre <[email-masked]>
Date: Tue, 15 Jan 2002 22:50:13 -0500

First, I want to mention that I'm impress by the quality of 
John proposition, particularly by the problem description.  
I wish someone had mentioned these issues at the beginning.

> Great idea starter for the xml-ical proposal. But it does not 
> conform to the CAP-requiements that it be a iCalendar data object.

  Does SQL conform to this requirement?  I think that SkiCal already 
use XML in an property (I'm not saying this is a good practice).

  Anyway this is not the real issue, John mentioned using XML as 
a mean to get his point across.

> CAP requirements, and I think that it is implementable.

  At this point, I agree with Doug. The latest proposal of 
SQL-MIN seems to address the issues recently posted. It's not
perfect. However, unless we discover a fundamental problem with
it, I don't think that it should abandoned.