Subject: Re: CAP: Badly formed SQL

From: "John Stracke" <[email-masked]>
Date: Thu, 10 Jan 2002 11:11:59 -0500

>I'm also not sure about selecting from both VEVENT
>and VTODO in a single statement like this, without
>worring about some complex joins or qualifying the
>properties with the component that they belong to.

Yeah...it's obviously something that'd be nice to be able to do, 
though--reduce round trips, and all that.  If we want CUAs to be able to 
do it, then it'd be useful to have an example in the spec, to make it 
clear that implementors need to support it.

/=======================================================\
|John Stracke                   |Principal Engineer     |
[email-masked]  |Incentive Systems, Inc.|
|http://www.incentivesystems.com|My opinions are my own.|
|=======================================================|
|"Fate just isn't what it used to be." --Hobbes         |
\=======================================================/

---

From: "John Stracke" <[email-masked]>
Date: Thu, 10 Jan 2002 13:17:17 -0500

>If we model iCalendar components as tables for our SQL syntax,
>how should we express sub-components, as I can't see how
>we can model a 'sub-table' in SQL92?

I think you're right; we can't.  We probably need to accept that the 
iCalendar data model is too rich to be expressed this way.  Either we 
change how we model the components (so that subcomponents live in separate 
tables), or we do an extended SQL that can cope with nested tables.  The 
former requires more work from CUA developers; the latter requires more 
work from CS developers.  Offhand, I'd favor the latter, since there are 
likely to be more CUAs than CSes.

/=================================================================\
|John Stracke                   |Principal Engineer               |
[email-masked]  |Incentive Systems, Inc.          |
|http://www.incentivesystems.com|My opinions are my own.          |
|=================================================================|
|Two words that inspire the same feeling of dread are synominous. |
\=================================================================/

---

From: "John Stracke" <[email-masked]>
Date: Thu, 10 Jan 2002 17:03:14 -0500

>John wrote on 01/10/2002 02:43:23 PM:
>> No, but we are mandating (I think) that it behave like a SQL engine at 
the 
>> CAP layer; and, at that layer, the query is asking for a join ("FROM 
>> VEVENT,VTODO").

>I guess if you say that the query format MUST behave like SQL then
>I'd buy the FROM bit.  However as we are not mandating it IS SQL then
>Im not sure you can mandate that it ACT LIKE SQL.

You're getting layers confused.

There are two layers in this system, the backend and the CAP interpreter. 
I don't care how your backend acts.  What is vital is that we standardize 
how the CAP interpreter handles SQL queries.  Either we mandate that it 
acts like SQL, or we make up our own rules.

>I get queasy when I have to consider mapping SQL idiosyncrasies onto
>other products (ie: dB2, etc) just because of "Heck, thats what it
>means to SQL."

Well, that's what you're going to have do, if CAP uses SQL as its query 
language.  Your CS will contain a SQL engine, which will map onto your 
backend store.  People who use SQL-based backends won't necessarily have 
an advantage (except that they'll be more likely to know SQL), because 
SQL's data model is much more basic than iCalendar's.

>Dont start mixing in stuff like "columns" as that even
>further implys an SQL engine

Layer confusion again.  If the query in the CAP request is written in SQL, 
then it is legitimate to talk about the columns in that query, because 
that's SQL terminology.  It is never legitimate to talk about the columns 
in the backend, because they're not exposed to CAP.

>> Whether or not this means doing a join (the Cartesian product of the 
two 
>> tables) behind the scenes is irrelevant; it's only required that the CS 

>> return results as if it had done a join. 

>Most of our users typically call this "combined results".  When we
>(the WG folk) start using terms like JOIN, UNION, INTERSECTION,
>etc. at the same time as SQL (or any other dB) then its risking
>making the improper assumption that some implied behaviour is going
>on.

Not at all.  It simply indicates what results the client can expect to 
get.

>>                                         If you ask your database to do 
a 
>> join on two million-row tables, it doesn't first construct a 
trillion-row 
>> table and then select from it; it just pretends.

>Sounds like you've been reliving the 31 billion instance recurrence
>rule nightmare again...

The horror, the horror...

/=======================================================\
|John Stracke                   |Principal Engineer     |
[email-masked]  |Incentive Systems, Inc.|
|http://www.incentivesystems.com|My opinions are my own.|
|=======================================================|
|I'm not a bibliophile, I'm a bibliophiliac. Put me in a|
|bookstore, & my wallet bleeds.                         |
\=======================================================/

---

From: "John Stracke" <[email-masked]>
Date: Fri, 11 Jan 2002 10:14:25 -0500

>Your debating SQL, not CAP.

The problem is that, as defined, CAP includes SQL.  That doesn't mean it 
includes a language that looks kind of like SQL; it includes real SQL, 
with all the semantics that implies.  Subsetting the syntax to produce 
SQL-MIN does subset the semantics; but it doesn't *change* the semantics. 
So a join really is a cross product, and really doesn't make sense in CAP.

---

From: "John Stracke" <[email-masked]>
Date: Fri, 11 Jan 2002 10:10:31 -0500

>                SELECT * FROM VTODO,VEVENT
[...]
>It DID NOT produce one big blob inside of a single BEGIN/END.

Right.  That means it's not a join, and so the query syntax is wrong. It'd 
have to be

SELECT * FROM TABLE(VTODO) UNION TABLE(VEVENT)

---

From: "John Stracke" <[email-masked]>
Date: Fri, 11 Jan 2002 10:17:57 -0500

>> I'm not knocking SQL-MIN; the problems pointed out by John, Patrice,
>> and myself such as parameters, sub-components, multiple properties
>> etc. are just as present in SQL-92.
>
>Would PARAM(prop-name, param-name) solve the problem if we
>specify no joins?

It wouldn't solve the problems of subcomponents and multiple properties.

/=============================================================\
|John Stracke                   |Principal Engineer           |
[email-masked]  |Incentive Systems, Inc.      |
|http://www.incentivesystems.com|My opinions are my own.      |
|=============================================================|
|"I think we'd get fewer bug reports if we stopped selling our|
|software off planet."                                        |
\=============================================================/

---

From: "John Stracke" <[email-masked]>
Date: Fri, 11 Jan 2002 10:17:16 -0500

>>      c) Forget about using SQL altogether
>
>I think that (c) is out of the question at this point.

Mmm, I'm not so sure.  Developing a new, high-powered query language would 
probably slow us down too much; but we could go for Just Enough.  Your 
suggestion of canned queries, for example, could suffice to get us out the 
door.

|Seen in computer peripheral ad: "User-friendly dip switches!"|

---

From: [email-masked]
Date: Thu, 10 Jan 2002 11:27:42 -0500

Doug replied:
>> 6.2.4.5 "search" Command
>> [...]
>> C: QUERY:SELECT DTSTART,DTEND,SUMMARY,UID,VALARM.*
>> C:    FROM VEVENT,VTODO
>> C:    WHERE VALARM.TRIGGER >= '19990310T080000Z'
>> C:    AND VALARM.TRIGGER <= '19990310T190000Z'
>> C:    AND METHOD IS BOOKED
>> 
>Good point, lets remove VTODO from the example or
>split it into two examples.

That wont resolve the issue of what would come back if someone actually 
did make that query (since it appears to be legally formatted to me). 
Granted the prose for _this_ example says "Find alarms within a range of time for booked VEVENTs." which means that the VTODOs acutally dont belong in the example. HOWEVER, 
if its possible to do then we should show a sample for it.  (Anyone care 
for some multipart/* rehashing and interpretations in iCalendar??)

Having forgotten most of the SQL I knew from my dB days I dont see that 
any joins, etc are really going to happen (or should happen).  Just 
because we use SQL-MIN does not mean we are mandating that the CS actually 
use SQL as its engine, right?? 

So, for the cited example query why wouldnt the CS just return all the 
data that match the parameters.  That is, something like:

...
S: Content-Type: text/calendar
S: Content-ID: [email-masked]
S:
S: BEGIN:VCALENDAR
S: VERSION:2.0
S: BEGIN:VEVENT
S: DTSTART:19990310T130000Z
S: DTEND:19990310T133000Z
S: UID:abcxyz8999
S: SUMMARY:Meet with brave Sir Robin
S: BEGIN:VALARM
S: TRIGGER:19990310T132500Z
S: SUMMARY:Almost time...
S: ACTION:DISPLAY
S: END:VALARM
S: END:VEVENT
S: BEGIN:VTODO
S: DTSTART:19990310T124500Z
S: DTEND:19990310T125000Z
S: UID:qwerty1234
S: SUMMARY:Change my armor before meeting with brave Sir Robin
S: BEGIN:VALARM
S: TRIGGER:19990310T124500Z
S: SUMMARY:Go and change your armor...
S: ACTION:DISPLAY
S: END:VALARM
S: END:VEVENT
S: END:VCALENDAR
S: --boundary-435fe--
S: END
S: NUL 1 15 . 16671 0
S: END

Bruce
===========================================================================
Bruce Kahn
INet: [email-masked]
Still recoiling from the implants...

---

From: Alan Davies <[email-masked]>
Date: Fri, 11 Jan 2002 12:07:11 -0500

At 11:45 AM 11/01/2002 -0500, John Stracke wrote:

> >> >      a) Change the schema to make it look like a relational DB
> >>Do you have a specific list of what it would take to do (a) ?
> >>Does anyone?
> >
> >The iCalendar schema would have to be representable in an
> >relational database.
>
>That could certainly be done; but it would require CUA developers to know
>much more SQL than the current draft envisions.

What about using a set of pre-defined functions to overcome the
complexities of iCalendar's schema, in a similar way to Patrice's
suggestion for referencing parameters.

The VEVENT virtual table could be viewed as a table like this,
with the VALARM column being viewed as the whole VALARM blob,
which is only allowed to be accessed through a set of predefined
functions:

+-------------+
|VEVENT       |
+-------------+
|DTSTART      |
|DTEND        |
|UID          |
|VALARM       | <- sub-component
|[...]        |
+-------------+

A subcomponent can only be referenced outside of the 'SELECT'
clause by using a predefined function:

     SELECT DTSTART,DTEND,SUMMARY,UID,VALARM FROM VEVENT
         WHERE CAP_SUBCOMP_PROP(VALARM, 'TRIGGER') >= '19990310T080000Z'
         AND CAP_SUBCOMP_PROP(VALARM, 'TRIGGER') <= '19990310T190000Z'
         AND METHOD = 'BOOKED'

The following would be disallowed, as sub-components cannot be
referenced directly outside of the 'SELECT' clause:

     SELECT DTSTART,DTEND,SUMMARY,UID,VALARM FROM VEVENT
         WHERE VALARM = [...]

Does this make any sense, and would there be a clean way
of coping with multivalues like this?

--Alan

---

From: "John Stracke" <[email-masked]>
Date: Fri, 11 Jan 2002 14:57:06 -0500

>We were going to be SQL like.

What is the value in being SQL-like? It's misleading; it leads developers 
to think they understand the query language when they don't.  If we're 
going to make up new semantics, we should make up new syntax, too.

/===============================================================\
|John Stracke                   |Principal Engineer             |
[email-masked]  |Incentive Systems, Inc.        |
|http://www.incentivesystems.com|My opinions are my own.        |
|===============================================================|
|"She gets kidnapped. He gets killed. But it turns out okay." --|
|_Princess Bride_ poster                                        |
\===============================================================/

---

From: "John Stracke" <[email-masked]>
Date: Mon, 14 Jan 2002 17:28:56 -0500

>This would mean we take all SQL out of CAP and replace it
>with a MINIMUM set of canned queries - by NAME. - As an option

I think that'd be a better option than using SQL directly.  If we try to 
shoehorn SQL into CAP, then we'll either (a) spend the next six months 
ironing out the implications of the more obscure features or (b) spend the 
next six years ironing out the interop problems when people try to use SQL 
features that the CS doesn't quite grok.  If CAP can get by with a 
minimalist set of capabilities, then we can put off the problem of a real 
query language.

/==============================================================\
|John Stracke                   |Principal Engineer            |
[email-masked]  |Incentive Systems, Inc.       |
|http://www.incentivesystems.com|My opinions are my own.       |
|==============================================================|
|A ship is safe in a harbor, but that's not what a ship is for.|
\==============================================================/

---

From: "John Stracke" <[email-masked]>
Date: Mon, 14 Jan 2002 17:25:28 -0500

>And I am hoping that we can continue to use valid SQL. So far
>it looks as if it will work.

Except that nobody has yet come up with a proposal for how to solve the 
problem of querying on multivalued properties.

I'm still working on my constraint-based proposal (I would have had it 
today, but my wife & I spent most of the day at a prenatal appointment).