Adapter Online Help > TDV Google Calendar Adapter > Data Model > Tables
 
Tables
The adapter models the data in Google Calendar into a list of tables that can be queried using standard SQL statements.
Generally, querying Google Calendar tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.
Google Calendar Adapter Tables
Name
Description
AllCalendars
Create, update, delete, and query all calendar events in your Google Account.
Calendars
Create, update, delete, and query calendars in Google.
MyCalendar
Create, update, delete, and query events of the calendar.
AllCalendars
Create, update, delete, and query all calendar events in your Google Account.
Table-Specific Information
This is an example on how all calendar events in your account are exposed in a single table.
Select
Query events of all calendars.
Select * FROM [AllCalendars]
Insert
Create a new event in a certain calendar. At least StartDateTime, EndDateTime and CalendarId must be specified.
INSERT INTO [AllCalendars] (Summary, Description, StartDateTime, EndDateTime, CalendarId) VALUES ('Great Event', 'Description for event', '8/27/2017', '8/28/2017', 'calendarid@gmail.com')
Update
Update details of a specific event. At least the Id of the event being updated must be specified.
UPDATE AllCalendars SET Summary='Test Event' WHERE id='6bjelf33p0al4d8ei5ft5ghqjs' AND CalendarId='clanedarId@cdata.com'
Delete
Delete an event from AllCalendars by specifying at least its Id.
Delete FROM [AllCalendars] WHERE Id='8ba774m3anenroqcepfi7ka6ok' AND CalendarId='clanedarId@cdata.com'
Order Events
When you query from AllCalendars table, the events will not be ordered by the StartDate, but rather by the CalendarId. You can order the calendars by either the StartDate or StartDateTime column, depending if the event is an AllDayEvent or not. Alternatively, you can order both Event types using the example query below.
SELECT CalendarId, Id, Summary,
CASE
WHEN StartDateTime IS NULL THEN startDate
ELSE StartDateTime
END AS EventDate
FROM AllCalendars ORDER BY EventDate ASC
Columns
Name
Type
ReadOnly
Description
Id [KEY]
String
True
The Id of the event.
CalendarId [KEY]
String
True
Calendar Id
Summary
String
False
The title of the event.
Description
String
False
The description of the event.
Location
String
False
The geographic location of the event as free-form text.
AllDayEvent
Boolean
False
This value sets whether or not the event is an all-day event.
StartDate
Date
False
The start date if this is an all-day event..
StartDateTime
Datetime
False
The (inclusive) start time of the event. For a recurring event, this value is the start time of the first instance.
StartDateTimeZone
String
False
The time zone in which the start date time is specified.
EndDate
Datet
False
The end date if this is an all-day event.
EndDateTime
Datetime
False
The (exclusive) end time of the event. For a recurring event, this value is the end time of the first instance.
EndDateTimeZone
String
False
The time zone in which the end date time is specified.
OriginalStartTimeDateTime
Datetime
False
For an instance of a recurring event, this value is the time when the event would start according to the recurrence data in the recurring event identified by RecurringEventId.
SendNotification
Boolean
False
This value sets whether to send a notification when performing an insert or update.
Kind
String
True
The type of the resource, returned in the format calendar#event.
ETag
String
True
The ETag of the resource.
Status
String
False
The status of the event.
HTMLLink
String
True
The absolute link to the event in the Google Calendar Web UI.
Created
Datetime
True
The creation time of the event.
Updated
Datetime
True
The latest modification time of the event.
ColorId
Integer
False
The color of the event. This value is an Id referring to an entry in the event section of the colors definitions.
CreatorEmail
String
True
The creator's email address, if available.
CreatorDisplayName
String
True
The creator's name, if available.
OrganizerEmail
String
False
The organizer's email address, if available.
OrganizerDisplayName
String
False
The organizer's name, if available.
Recurrences
String
False
A pipe-separated list of RRULE, EXRULE, RDATE, and EXDATE lines for a recurring event. This field is omitted for single events or instances of recurring events. OriginalStartTimeDateTime must be set in order to modify this value.
RecurringEventId
String
True
For an instance of a recurring event, this value is the event Id of the recurring event itself.
Transparency
String
False
This value sets whether the event blocks time on the calendar. If set to transparent, the event does not block time on the calendar. If set to opaque, the event blocks time; this is the default value.
Visibility
String
False
The visibility of the event.
ICalUid
String
True
The event Id in the iCalendar format.
Sequence
String
False
The sequence number as per iCalendar.
AttendeesEmails
String
False
A comma-separated list of attendee's email addresses, if available.
AttendeesDisplayNames
String
False
A comma-separated list of attendee's names, if available.
AttendeesOmitted
Boolean
True
This field sets whether attendees have been omitted from the event's representation. When updating an event, this field can be used to update only the participant's response. When retrieving an event, the attendees that are returned are restricted to only the participant by the MaxAttendees query parameter.
ExtendedPropertiesPrivateKey
String
False
This field contains properties that are private to the copy of the event that appears on the calendar.
ExtendedPropertiesPrivateValue
String
False
This field contains properties that are private to the copy of the event that appears on the calendar.
ExtendedPropertiesSharedKey
String
False
This field contains properties that are shared between copies of the event on other attendees' calendars.
ExtendedPropertiesSharedValue
String
False
This field contains properties that are shared between copies of the event on other attendees' calendars.
AnyoneCanAddSelf
Boolean
True
This value sets whether anyone can invite themselves to the event.
GuestsCanInviteOthers
Boolean
False
This value sets whether attendees other than the organizer can invite others to the event.
GuestsCanSeeOtherGuests
Boolean
False
This value sets whether attendees other than the organizer can see who the event's attendees are.
GuestsCanModify
Boolean
False
Whether attendees other than the organizer can modify the event.
PrivateCopy
Boolean
True
This value sets whether this is a private event copy where changes are not shared with other copies on other calendars.
RemindersUseDefault
Boolean
False
This value sets whether the default reminders of the calendar apply to the event.
ReminderOverrideMethods
String
False
A comma-separated list of the methods used by the reminder. The possible values are EMAIL, SMS, and POPUP.
ReminderOverrideMinutes
String
False
A comma-separated list of the minutes before the start of the event when the corresponding ReminderOverrideMethod should trigger.
Pseudo-Columns
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name
Type
Description
SearchTerms
String
Free text search terms to find events that match these terms in any field, except for extended properties.
SingleEvents
Boolean
Whether to expand recurring events into instances, default value is false.
Calendars
Create, update, delete, and query calendars in Google.
Table-Specific Information
Select
When selecting calendars no fields are required. In addition an Id can be specified for filtering the result. For example:
SELECT Id, Summary, Description FROM Calendars
Insert
To insert a calendar, issue an INSERT statement and specify a value for at least the Summary column. For example:
INSERT INTO Calendars (Summary) VALUES ('My Custom Calendar')
Update
To update a calendar, the Id column must be specified. Only the Description, Location, Summary, and Timezone columns are updateable. For example:
UPDATE Calendars SET Description='Updated Description' WHERE Id='8ba774m3anenroqcepfi7ka6ok'
Delete
Delete a calendar by specifying its Id. For example:
DELETE FROM Calendars WHERE Id='123456789'
Columns
Name
Type
ReadOnly
Description
Id [KEY]
String
True
The calendar identifier.
Summary
String
False
The title of the calendar.
Description
String
False
The description of the calendar.
Kind
String
True
The type of the resource.
ETag
String
True
The ETag of the resource.
Location
String
False
The geographic location of the calendar as free-form text.
Timezone
String
False
The time zone of the calendar.
SummaryOverride
String
False
The summary that the authenticated user has set for the calendar.
ColorId
Integer
False
The color of the calendar. This is an Id referring to an entry in the 'calendar' section of the colors definition.
Hidden
Boolean
False
This field sets whether the calendar has been hidden from the list.
Selected
Boolean
False
This field sets whether the calendar content shows up in the calendar UI.
AccessRole
String
True
The effective access role that the authenticated user has on the calendar.
ReminderMethods
String
False
A semicolon-separated list of the methods used by the reminder. Possible values are: EMAIL, SMS, and POPUP.
ReminderMinutes
String
False
A semicolon-separated list of minutes before the start of the event when the corresponding ReminderOverrideMethod should trigger.
Pseudo-Columns
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name
Type
Description
ShowHidden
String
This value sets whether to show hidden calendars.
The allowed values are TRUE, FALSE.
The default value is False.
MyCalendar
Create, update, delete, and query events of the calendar.
Table-Specific Information
This is an example on how a calendar is exposed as table.
Select
Query events of the specified calendar.
Select * FROM [MyCalendar]
Insert
Create a new event in the calendar. At least StartDateTime and EndDateTime must be specified.
INSERT INTO [MyCalendar] (Summary, Description, StartDateTime, EndDateTime) VALUES ('Great Event', 'Description for event', '8/27/2017', '8/28/2017')
When inserting a new event, multiple values can be specified for AttendeesEmails and AttendeesDisplayNames.
Insert Into [MyCalendar] (StartDateTime,EndDateTime,AttendeesEmails#1,AttendeesEmails#2) VALUES ('2017-03-15 15:00','2017-03-15 20:00','test@test.com','test1@test.com')
Update
Update details of a specific event. The Id of the event being updated must be specified.
UPDATE [MyCalendar] SET Summary='Updated Summary 2' WHERE Id='8ba774m3anenroqcepfi7ka6ok'
Delete
Delete an event by specifying its Id.
Delete FROM [MyCalendar] WHERE Id='8ba774m3anenroqcepfi7ka6ok'
Columns
Name
Type
ReadOnly
Description
Id [KEY]
String
True
The Id of the event.
CalendarId
String
True
Calendar Id
Summary
String
False
The title of the event.
Description
String
False
The description of the event.
Location
String
False
The geographic location of the event as free-form text.
AllDayEvent
Boolean
False
This value sets whether or not the event is an all-day event.
StartDate
Date
False
The start date if this is an all-day event..
StartDateTime
Datetime
False
The (inclusive) start time of the event. For a recurring event, this value is the start time of the first instance.
StartDateTimeZone
String
False
The time zone in which the start date time is specified.
EndDate
Datet
False
The end date if this is an all-day event.
EndDateTime
Datetime
False
The (exclusive) end time of the event. For a recurring event, this value is the end time of the first instance.
EndDateTimeZone
String
False
The time zone in which the end date time is specified.
OriginalStartTimeDateTime
Datetime
False
For an instance of a recurring event, this value is the time when the event would start according to the recurrence data in the recurring event identified by RecurringEventId.
SendNotification
Boolean
False
This value sets whether to send a notification when performing an insert or update.
Kind
String
True
The type of the resource, returned in the format calendar#event.
ETag
String
True
The ETag of the resource.
Status
String
False
The status of the event.
HTMLLink
String
True
The absolute link to the event in the Google Calendar Web UI.
Created
Datetime
True
The creation time of the event.
Updated
Datetime
True
The latest modification time of the event.
ColorId
Integer
False
The color of the event. This value is an Id referring to an entry in the event section of the colors definitions.
CreatorEmail
String
True
The creator's email address, if available.
CreatorDisplayName
String
True
The creator's name, if available.
OrganizerEmail
String
False
The organizer's email address, if available.
OrganizerDisplayName
String
False
The organizer's name, if available.
Recurrences
String
False
A pipe-separated list of RRULE, EXRULE, RDATE, and EXDATE lines for a recurring event. This field is omitted for single events or instances of recurring events. OriginalStartTimeDateTime must be set in order to modify this value.
RecurringEventId
String
True
For an instance of a recurring event, this value is the event Id of the recurring event itself.
Transparency
String
False
This value sets whether the event blocks time on the calendar. If set to transparent, the event does not block time on the calendar. If set to opaque, the event blocks time; this is the default value.
Visibility
String
False
The visibility of the event.
ICalUid
String
True
The event Id in the iCalendar format.
Sequence
String
False
The sequence number as per iCalendar.
AttendeesEmails
String
False
A comma-separated list of attendee's email addresses, if available.
AttendeesDisplayNames
String
False
A comma-separated list of attendee's names, if available.
AttendeesOmitted
Boolean
True
This field sets whether attendees have been omitted from the event's representation. When updating an event, this field can be used to update only the participant's response. When retrieving an event, the attendees that are returned are restricted to only the participant by the MaxAttendees query parameter.
ExtendedPropertiesPrivateKey
String
False
This field contains properties that are private to the copy of the event that appears on the calendar.
ExtendedPropertiesPrivateValue
String
False
This field contains properties that are private to the copy of the event that appears on the calendar.
ExtendedPropertiesSharedKey
String
False
This field contains properties that are shared between copies of the event on other attendees' calendars.
ExtendedPropertiesSharedValue
String
False
This field contains properties that are shared between copies of the event on other attendees' calendars.
AnyoneCanAddSelf
Boolean
True
This value sets whether anyone can invite themselves to the event.
GuestsCanInviteOthers
Boolean
False
This value sets whether attendees other than the organizer can invite others to the event.
GuestsCanSeeOtherGuests
Boolean
False
This value sets whether attendees other than the organizer can see who the event's attendees are.
GuestsCanModify
Boolean
False
Whether attendees other than the organizer can modify the event.
PrivateCopy
Boolean
True
This value sets whether this is a private event copy where changes are not shared with other copies on other calendars.
RemindersUseDefault
Boolean
False
This value sets whether the default reminders of the calendar apply to the event.
ReminderOverrideMethods
String
False
A comma-separated list of the methods used by the reminder. The possible values are EMAIL, SMS, and POPUP.
ReminderOverrideMinutes
String
False
A comma-separated list of the minutes before the start of the event when the corresponding ReminderOverrideMethod should trigger.
Pseudo-Columns
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name
Type
Description
SearchTerms
String
Free text search terms to find events that match these terms in any field, except for extended properties.
SingleEvents
Boolean
Whether to expand recurring events into instances, default value is false.