Subject: Re: CAP: Badly formed SQL

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

>> Meanwhile, we should probably disallow joins in CAP, because they just 
>> don't make sense--what does it mean to combine a VEVENT and a VTODO 
into a 
>> single component? Because that's what the join means.

>Simple: "Give me all the Events and Todos on my calendar that have
>alarms that will trigger in the given time range".  Thats totally
>logical.

No, it isn't, because that's nothing like what a join is.  You're thinking 
of a union.  A join is, in essence, a cross product of two tables.

Say you have two tables, A and B.  A has three columns, (A1, A2, A3), and 
B has two, (B1, B2).  Then the join of A and B has five columns, (A1, A2, 
A3, B1, B2).  It's assembled by putting together all the possible 
combinations of rows in A and B; if there are 10 rows in A, and 13 rows in 
B, then there are 130 rows in the join.

Each row in the join of the VEVENT table and the VTODO table would be a 
component (bracketed with BEGIN/END) containing some properties from a 
VEVENT and some from a VTODO.  That's obviously not useful.

Now, if you use a UNION, then what you get is any component from either 
table that matches the constraint.

>We can break it down and mandate multiple queries but thats increased
>CS load, response time, etc...

Er...excuse me, but it does not appear that you read the message you 
replied to.  The problem you raise is solved by using UNION, rather than 
join, as I pointed out at the start of my message.

/===============================================================\
|John Stracke                   |Principal Engineer             |
[email-masked]  |Incentive Systems, Inc.        |
|http://www.incentivesystems.com|My opinions are my own.        |
|===============================================================|
|"If it failed then, there's no reason to think we can't make it|
|fail now." -- Colin Benson                                     |
\===============================================================/

---

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

> of a union.  A join is, in essence, a cross product of two tables.

Your debating SQL, not CAP. The question we need to answer
is "If I had this ideal virtual cap schema", could I get
the data out specified by SQL-MIN? Or SQL-92?

If NO - why?
If YES - done?

---

From: Alan Davies <[email-masked]>
Date: Thu, 10 Jan 2002 18:59:47 -0500

>the data out specified by SQL-MIN? Or SQL-92?

I don't think our current schema can be modelled effectively
in SQL.

We have to either
     a) Change the schema to make it look like a relational DB
     b) Invent some new SQL syntax
     c) Forget about using SQL altogether

--Alan

---

From: Doug Royer <[email-masked]>
Date: Thu, 10 Jan 2002 17:23:08 -0700

>      c) Forget about using SQL altogether

I think that (c) is out of the question at this point.

I think that if we had to we could live with (b), and we
would have to mandate that CAP-SQL-92 also MUST have those extensions
so any SQL-92 CS could be talked to by a SQL-MIN CS.

Do you have a specific list of what it would take to do (a) ?
Does anyone?

-Doug

---

From: Alan Davies <[email-masked]>
Date: Fri, 11 Jan 2002 10:43:25 -0500

>I think that (c) is out of the question at this point.

You were more or less suggesting (c) with your stored query
ideas.

One possibility would be to restrict the 'FROM' clauses to
specifiec pre-defined values, to prevent complex joins from
being constructed. This way the CUA would specify the
properties to return (in the 'SELECT' clause), and the
properties to filter on (in the 'WHERE' clause).

>so any SQL-92 CS could be talked to by a SQL-MIN CS.

I'm not sure how our extensions would be able to live alongside
SQL-92, it could become so complex that it would be unusable, and
we would be better off forgetting all about SQL-92.

>Do you have a specific list of what it would take to do (a) ?

The iCalendar schema would have to be representable in an
relational database. The best way to prove this would be to
get a copy of MySQL or similar, and try creating the appropriate
tables and columns so that CAP's SQL-MIN queries can
be made on this DB.

--Alan

---

From: Doug Royer <[email-masked]>
Date: Fri, 11 Jan 2002 12:11:40 -0700

> You were more or less suggesting (c) with your stored query

NO, that was not my intent
Stored SQL queries - they already exist in CAP, using QUERYNAME.

> properties to filter on (in the 'WHERE' clause).

Yes - I think we agree.

> tables and columns so that CAP's SQL-MIN queries can

I had started work on that a year or so ago.