Wednesday, December 16, 2015

Undocumented Google Calendar URL Parameter #2

While looking up information about the sprop parameter for Google Calendars, I found a Japanese page with information about adding events to Google Calendar that included the add parameter. The add parameter allows you to define additional attendees. I've tried this with email addresses. You can specify multiple email addresses by separating them with commas. Here is an example:

add=guest@gmail.com,visitor@gmail.com

I'm still looking for a parameter that allows you to specify notification times.

Undocumented Google Calendar URL Parameter #1

While doing some looking into ColdFusion components and research into RFC 5545, Google Calendars, and Yahoo! Calendars, I did some experimenting to see how much I can specify for a calendar event via URL in Google Calendars. I quickly found the action, text, desc, dates, and location parameters. Some additional looking found the sprop and trp parameters.

I opened up a new calendar event and tried to find ways to specify other portions of the form through URL parameters. I eventually found that I could use the recur parameter and specify a full RDATE or RRULE property and value string in URL-encoded format. For instance, if you wanted to create an event that repeated monthly on the second Sunday, you could add:

recur=RRULE%3AFREQ%3DMONTHLY%3BWKST=SU%3BBYDAY%3D2SU
to the URL. That's a URL-encoded form of the following.
RRULE:FREQ=MONTHLY;WKST=SU;BYDAY=2SU

I didn't find this documentation anywhere else, so I'm posting it here for someone else to find.

I'm still looking for ways to specify reminders and attendees. If you know how to do either of these, let me know.

Tuesday, February 3, 2015

Discoveries in Familiar Languages #1

I love it when I'm working in a familiar programming language and find something new, particularly if that something new gives me more power or simplifies a pattern I've been using for a while. Such a discovery happened today.

Many-to-many relationship maintenance

I am constantly setting up and maintaining many-to-many relationships. I often set up the junction table to prevent duplication of relationships by adding a unique index on the appropriate key columns. Here's an example of such a junction table in MS SQL Server.

 CREATE TABLE [dbo].[ContactsGroups](  
  [keyContactGroup] [int] IDENTITY(1,1) NOT NULL,  
  [keyContact] [int] NOT NULL,  
  [keyGroup] [int] NOT NULL,  
  CONSTRAINT [PK_ContactsGroups] PRIMARY KEY CLUSTERED ([keyContactGroup] ASC) ON [PRIMARY]  
 )  

This is a pretty typical, very simple junction table. When I need to maintain this many-to-many relationship, I've typically taken a two-step process: DELETE and INSERT. Here is an example of those two queries as they would appear in ColdFusion.

 <cfquery datasource="..." result="qry_updateContact_groups">  
 DELETE FROM [ContactsGroups]  
 WHERE ([keyContact] = <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keyContact#" />)  
 <cfif ListLen(Form.keysGroups) GT 0>  
  AND ([keyGroup] NOT IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keysGroups#" list="true" />))  
 </cfif>  
 </cfquery>  
 <cfif ListLen(Form.keysGroups) GT 0>  
  <cftry>  
   <cfquery datasource="..." result="qry_updateContact_groups">  
   INSERT INTO [ContactsGroups] ([keyContact], [keyGroup])  
   SELECT <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keyContact#" />, [Groups].[keyGroup]  
   FROM [Groups]  
   LEFT JOIN (  
    SELECT [keyGroup]  
    FROM [ContactsGroups]  
    WHERE ([keyContact] = <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keyContact#" />)  
   ) [ContactsGroups]  
    ON ([ContactsGroups].[keyGroup] = [Groups].[keyGroup])  
   WHERE (  
    [Groups].[keyGroup] IN (  
     <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keysGroups#" list="true" />  
    )  
   ) AND (COALESCE([ContactsGroups].[keyGroup], 0) = 0)  
   </cfquery>  
   <cfcatch><!--- An exception is thrown when nothing is inserted. ---></cfcatch>  
  </cftry>  
 </cfif>  

As you can see, this involved performing a LEFT JOIN to exclude items that already exist in the table. Today, while looking up a related issue (see the comments in the <cfcatch> block), I found a much easier way to write the same INSERT query.

  <cftry>  
   <cfquery datasource="#Request.ds.site#" result="qry_updateContact_groups">  
   INSERT INTO [ContactsGroups] ([keyContact], [keyGroup])  
   SELECT <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keyContact#" />, [keyGroup]  
   FROM [Groups]  
   WHERE ([keyGroup] IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keysGroups#" list="true" />))  
   EXCEPT  
   SELECT [keyContact], [keyGroup]  
   FROM [ContactsGroups]  
   WHERE ([keyContact] = <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keyContact#" />)  
   </cfquery>  
   <cfcatch><!--- An exception is thrown when nothing is inserted. ---></cfcatch>  
  </cftry>  

Note the use of the EXCEPT keyword. This operates very similarly to the UNION keyword, but performs practically the opposite operation. Instead of creating a result set with the results of the first query followed by the results of the second query, it creates a result set with the results of the first query excluding results matching those of the second query. It performs the same task I was doing before, but it's more concise and (bonus!) more readable.