Subject: Propose we remove SQL-92 as a capability

From: Doug Royer <[email-masked]>
Date: Sat, 12 Jan 2002 16:58:58 -0700

I propose that we:

(1) Remove all references to SQL-92 as a query language for CAP (keep
    the references to the SQL standard at the end).

(2) Use SQL-MIN only in this release of CAP.

(3) Keep SQL-MIN as an exact named subset of SQL as define by the
    SQL 1992 standard. And that that exact named subset be
    specified in CAP.

---

From: Patrice Lapierre <[email-masked]>
Date: 14 Jan 2002 08:25:26 -0500

>     the references to the SQL standard at the end).

  I agreed.

>     SQL 1992 standard. And that that exact named subset be

SQL-MIN is not enough, it's missing fundamental features:

   1) Search on parameter.

       We already have propositions: dot delimiter and function.

   3) Regular expression on TEXT properties (e.g. LIKE operator).

      This a part of cap-requirement-04, see section 4.5.2 
      (Query Capabilities):
         
         "- Pattern-matching string comparison query (e.g., contains)"

   4) Search on properties with multiple values or occurrences.

      The following operations are basic to a CUA:

       - Find all VEVENTs with my PARTSTAT set to 'NEEDS-ACTION'
       - Show all my uncompleted VTODOs in the BUSINESS category.

       I posted a proposition to handle the first query. 
       The second query could be solved by either 3) or 4).   

  Our objective is to build a calendar server, not a minimalist generic
relational database. As such it seems only natural that 
our minimal set of requirements differs from SQL-MIN.

  If the terminology "SQL-MIN" causes confusion, then why not use
another name for the CAP query language (e.g. CQL-MIN or 
SQL-CAPMIN). 

  If the CAP query language turns out to be a super set of SQL-MIN, 
then great we can still make reference to it, and claim compliance 
to SQL-MIN. I don't see any real benefits in cutting basic 
functionality for the sole purpose of using a syntax identical to
SQL-MIN.

---

From: Patrice Lapierre <[email-masked]>
Date: 14 Jan 2002 12:17:51 -0500

In the previous post, I misinterpret (3) from the original post to
mean "keep SQL-MIN as an exact named subset defined in the SQL-92
standard"

  Obviously it's SQL (-92) and not SQL-MIN that is defined in the SQL-92
standard. 

> functionality for the sole purpose of using a syntax identical to

I should have written:

  Basic functionalities a calendar server should should not be
removed, just to maintain a syntactic compatibility with SQL. If the 
terminology "SQL-MIN" is confusing, then why not use another 
name (e.g, CQL-MIN).

---

From: Doug Royer <[email-masked]>
Date: Mon, 14 Jan 2002 11:16:08 -0700

>        We already have propositions: dot delimiter and function.

I could live with that.

I looked them up:

   The dot is SQL-92 (in "7.9 <query specification")

   SQL-92 allows for user defined functions (in "12.3 <procedure>")
   These procedures are defined by modules (language bindings).
   We simply have to define out language bindings.

>          "- Pattern-matching string comparison query (e.g., contains)"

Okay - we add LIKE - but that is also SQL-92.

	SELECT * FROM VEVENT WHERE SUMMARY LIKE '%MEETING%'

Do we want the "NOT LIKE" also?

>        - Find all VEVENTs with my PARTSTAT set to 'NEEDS-ACTION'

	SELECT * FROM VEVENT WHERE VEVENT.PARTSTAT = 'NEEDS-ACTION'

>        - Show all my uncompleted VTODOs in the BUSINESS category.

Okay, now I see why you want NULL and NOT NULL.

	SELECT * FROM VTODO WHERE VTODO.CATEGORY LIKE '%BUSINESS%'
         AND VTODO.COMPLETED NOT NULL

> Our objective is to build a calendar server, not a minimalist
> generic relational database. As such it seems only natural that

There is NO SUCH 'standard' as SQL-MIN, it is simply a CAPABILITY
string THIS WG is defining in the CAP specification that specifies
what this WG wants, so that CUA's know what QUERY language
the CS supports.

> another name for the CAP query language (e.g. CQL-MIN or

I don't care what the name in the CAP specification ABNF is,
not an issue for me.

> functionality for the sole purpose of using a syntax identical to

I think the error here is that some think that there is an SQL-MIN
specification outside of CAP?

---

From: Patrice Lapierre <[email-masked]>
Date: 14 Jan 2002 15:14:38 -0500

> Do we want the "NOT LIKE" also?

 Good idea, once the LIKE is included it is not the 
NOT that will slow us down.

> 	SELECT * FROM VEVENT WHERE VEVENT.PARTSTAT = 'NEEDS-ACTION'

 It's the query on properties with multiple occurrence (e.g. ATTENDEE)
seem to conflict with SQL.

>          AND VTODO.COMPLETED NOT NULL

LIKE is not ideal for categories, problem with a category embedded 
in another.

  	e.g., "HOLLIDAY" and "HOLLIDAY CARDS" 

  Regular expression of the LIKE seems relatively limited, but I
think that it is possible.

Introducing a specialized function might be more intuitive though:

e.g. (syntax might be wrong)
 
 SELECT * FROM VTODO WHERE 
   CATEGORIES_CONTAINS( CATEGORIES, 'BUSINESS' ) = TRUE AND
   COMPLETED IS NOT NULL
..
> what this WG wants, so that CUA's know what QUERY language
  Correct, my mistake. I already sent a mail explaining this.

---

From: Doug Royer <[email-masked]>
Date: Mon, 14 Jan 2002 14:57:07 -0700

> seem to conflict with SQL.

Why? The above SELECT could return (in the same reply):

	BEGIN:VCALENDAR:
	...
	UID:1
	ATTENDEE:PARTSTAT=NEEDS-ACTION:Doug
	ATTENDEE:PARTSTAT=NEEDS-ACTION:Patrice
	ATTENDEE:PARTSTAT=ACCEPTED:TheBoss
	...
	END:VCALENDR
	BEGIN:VCALENDAR
	...
	UID-2
	ATTENDEE:PARTSTAT=NEEDS-ACTION:Bruce
	..
	END:VCALENDAR

>    CATEGORIES_CONTAINS( CATEGORIES, 'BUSINESS' ) = TRUE AND

Or just this so that it can apply to any column for which you
want to compare contents?

     CONTAINS(CATEGORIES, 'BUSINESS')

Did you just introduce a new keyword 'TRUE' ?
How about it just returns TRUE if true, else it returns FALSE
and the same statement still works:

  SELECT * FROM VTODO WHERE
    CONTAINS( CATEGORIES, 'BUSINESS' ) AND
    COMPLETED IS NOT NULL

---

From: Alan Davies <[email-masked]>
Date: Mon, 14 Jan 2002 17:13:27 -0500

>Why? The above SELECT could return (in the same reply):

I think it's more the WHERE clause than the SELECT
clause that is a problem with multiple occurrences.

For example; a common calendaring operation that wouldn't
be currently possible (or whose syntax would appear to be
ambiguous at present) is a query that returns all events
of which I am an attendee, where my PARSTAT is NEEDS-ACTION

>     CONTAINS( CATEGORIES, 'BUSINESS' ) AND

That looks good to me too (using the general function, and removing TRUE).

--Alan

---

From: Doug Royer <[email-masked]>
Date: Mon, 14 Jan 2002 15:45:07 -0700

> of which I am an attendee, where my PARSTAT is NEEDS-ACTION

I guess I still don't understand, will this work? It is not
like we have defined any function name or its arguments in stone yet.

	SELECT * FROM VTODO WHERE
	 ATTENDEE = 'myUPN'
         AND PARAM(ATTENDEE, PARTSTAT, 'NEEDS-ACTION')

So the function can take 3 arguments:

	 ~Collumn-name~		ATTENDEE
	 PARAMATER-NAME  	PARTSTAT
	 Compare-to-value	NEEDS-ACTION

You would get the entire (*) VEVENT. Correct?

---

From: Alan Davies <[email-masked]>
Date: Mon, 14 Jan 2002 18:13:14 -0500

At 03:45 PM 14/01/2002 -0700, Doug Royer wrote:
>[...]

It would sort of work, but you have to make the assumption that
both times the ATTENDEE property was referred to, it referred to
the same ATTENDEE.

With this query, where I want to find events with two
specific attendees you'd have to assume that each reference
to ATTENDEE referred to a different ATTENDEE:

     SELECT * FROM VTODO WHERE
       ATTENDEE = 'myUPN'
       AND ATTENDEE = 'someOtherUPN'

If you wanted to search on more than 1 value/parameter of more
than 1 attendee, the syntax would be ambiguous:

     SELECT * FROM VTODO WHERE
       ATTENDEE = 'myUPN'
       AND ATTENDEE = 'someOtherUPN'
       AND PARAM(ATTENDEE, PARTSTAT) = 'NEEDS-ACTION'

Which ATTENDEE's PARTSTAT was I referring to, was it myUPN's,
somOtherUPN's, or a different ATTENDEE of the same event?

>         Compare-to-value        NEEDS-ACTION

Might be more flexible and consistent with properties
to have just the first 2 arguments:

     PARAM(ATTENDEE, PARTSTAT) = 'NEEDS-ACTION'
     PARAM(ATTENDEE, MEMBER) IS NOT NULL
     PARAM(ATTENDEE, CN) LIKE '%Alan'

--Alan

---

From: Doug Royer <[email-masked]>
Date: Mon, 14 Jan 2002 17:10:12 -0700

> both times the ATTENDEE property was referred to, it referred to

Same attendee property line? Can we define this to
be 'true' and solve this issue? Like:

	For all queries where multiple instances of a value exist.
	By multiple instances of the property, or by multiple
 	values on one property, the WHERE clase refers to
	the same instance for comparisons. (We then give
	the ATTENDEE example).

	Where the CUA wishes to perform	multiple property and
	paramater matches on multiple instance values, the CUA
	must perform one query for each unique match.

And when you want more complex queries you have to do multiple
queries and merge/test them in the CUA?

Or does someone have an idea on how we can specify the 'Nth'
property or at least uniquely identify an instance of a
multiple valued property? If not, the CUA must do multiple
queries or broaden the query to include all or more than
all of what it needs and filter the data itself?

>      PARAM(ATTENDEE, CN) LIKE '%Alan'

GREAT idea!

The idea behind SQL-MIN is that it was for simple queries.
I am certin that we can find many things you can not do with SQL-MIN.

---

From: Patrice Lapierre <[email-masked]>
Date: 15 Jan 2002 08:06:20 -0500

> queries and merge/test them in the CUA?
 
  That is always an option, but it would be extremely inefficient.

> all of what it needs and filter the data itself?

I did make a proposition last week.

 http://www.imc.org/ietf-calendar/mail-archive/msg02980.html

  It is an extension to SQL, but I don't think that it should an issue.
I'm not aware of any SQL-92 compliant database server that
don't include their own extensions to fit their particular needs.

  I modified the example from the original post, to use 
PARAM() instead of '.' to access the parameters.

---------------------
From: 	Patrice Lapierre <[email-masked]>
To: 	[email-masked] <[email-masked]>
Subject: CAP Query Language: Proposition to query properties with
multiple occurrences
Date: 	11 Jan 2002 10:03:36 -0500	

  A previous post by John Stracke raised the point that queries on
properties with multiple occurrences are ambiguous.

  I would like to propose an extension to the CAP query language, 
that is not part of SQL-92. It addresses some of the issues related to 
properties with multiple occurrences.

Here's an example to introduce the proposition:

The query: "All VEVENTs accepted by user1 and user2"

Could be written as follows:

  SELECT * FROM  VEVENT 
    USING ATTENDEE AS ATT1,
          ATTENDEE AS ATT2
    WHERE PARAM( ATT1, 'PARTSTAT' ) = 'ACCEPTED' AND 
          ATT1 = [email-masked]' AND
          PARAM( ATT2, 'PARTSTAT' ) = 'ACCEPTED' AND 
          ATT2 = [email-masked]'

The proposition adds two new keywords: "USING" and "AS".
   
The optional USING clause with elements has the form:
   
   PROPERTYNAME "AS" IDENT

The semantic could be described as follows:   

   For every element (PROPERTYNAME, IDENT) of the USING clause,
there exist at least one property PROPERTYNAME, say P, in all 
components of the FROM section that satisfies the expression
of the WHERE section after substituting all occurrences of
IDENT by the property P.

---

From: Doug Royer <[email-masked]>
Date: Tue, 15 Jan 2002 09:57:58 -0700

> PARAM() instead of '.' to access the parameters.

It was in my inbox, but somehow I missed it. I must have hit
the 'already read' button accidentally or something.

I could be happy with the proposal.

> The proposition adds two new keywords: "USING" and "AS".

I just looked up USING and AS, they are SQL-92. I would have
to do a lot more reading to see if the usage above is compliant.
I do know you can use the same column name multiple times in 
a USING clause. Ether way, I could go with this.

---

From: Patrice Lapierre <[email-masked]>
Date: 15 Jan 2002 12:28:09 -0500

> a USING clause. Ether way, I could go with this.

  I made up the keyword USING, hoping that it wasn't in SQL-92.
Another (or no keyword at all) will probably be more appropriate.

---

From: Alan Davies <[email-masked]>
Date: Tue, 15 Jan 2002 12:58:21 -0500

For multi-properties, I like the idea of inventing a new
keyword + clause which behaves in a syntactically similar
way to other clauses, I agree that it would be a bad idea
to reuse an existing keyword which has a distinct meaning.

We could define a compound keyword that specifies exactly
what we want, something like MULTI_PROPERTIES or
USING_PROPERTIES:

     SELECT *
         FROM VEVENT
         USING_PROPERTIES ATTENDEE att1, ATTENDEE att2
         WHERE att1= 'myUPN'
         AND att2= 'someOtherUPN'
         AND PARAM(att2, PARTSTAT) = 'NEEDS-ACTION'

(I don't care much about the name, so long as it's not
already an SQL keyword)

Additionally, I think we should specify that in the absence
of a USING_PROPERTIES (or whatever it's named) clause,
then all references to a multi-property refer to the same
property:

     SELECT *
         FROM VEVENT
         WHERE ATTENDEE = 'myUPN'
         AND PARAM(ATTENDEE , PARTSTAT) = 'NEEDS-ACTION'

(selects all events that a given attendee hasn't replied
to yet)

Are we getting close to proposing a revised SQL-MIN ABNF?

--Alan

---

From: Doug Royer <[email-masked]>
Date: Tue, 15 Jan 2002 12:13:12 -0700

> Are we getting close to proposing a revised SQL-MIN ABNF?

I think so!!

---

From: Patrice Lapierre <[email-masked]>
Date: 15 Jan 2002 14:33:55 -0500

> > Are we getting close to proposing a revised SQL-MIN ABNF?

Definitely, here a summary of the proposed changes:

  - 'LIKE' operator.

  - 'USING_PROPERTIES' construct (or MULTI_PROPERTIES?).

  - 'PARAM' function.
 
	Question: Should the second argument be quoted?
         e.i., PARAM( ATTENDEE, 'PARTSTAT' ) or 
               PARAM( ATTENDEE, PARSTAT )

	I would tend to use the quoted version.

  - 'CONTAINS' function.

  - Define 'colvalue' in ABNF (missing from draft).

	Is it always a quoted string?

Is anything missing?

  It's was already mentioned in another thread, and is probably just
an omission, but we should also add support for subexpression
(i.e., parenthesis) in the WHERE clause.

e.g.,

SELECT * FROM VEVENT 
         USING_PROPERTIES ATTENDEE att1, ATTENDEE att2
         WHERE ( att1 = 'calid1' AND 
                 PARAM( att1, 'PARTSTAT' ) = 'ACCEPTED' )
            OR ( att2 = 'calid2' AND
                 PARAM( att2, 'PARTSTAT' ) = 'ACCEPTED' )

---

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

>                  PARAM( att2, 'PARTSTAT' ) = 'ACCEPTED' )

I would have no objections to adding parenthesis.

Issue:
	Do we limit the number of levels?
	Unlimited?

Do we need to specify the precedence, or require groupings? :-)

---

From: Patrice Lapierre <[email-masked]>
Date: Tue, 15 Jan 2002 23:11:45 -0500

>         Do we limit the number of levels?

  I don't see any problem with unlimited. I can think 
of expensive queries with only one level.

The worst case seems the same: scan everything.

> Do we need to specify the precedence, or require groupings? :-)

Wow! That is a good one. :-)