Database Adapters
Details
The Generic JDBC Adapter can be used as a template for implementing a Kinetic Calendar Adapter for any database that supports JDBC connections. The specific database adapters all extend the Generic JDBC Adapter with the following minor modifications:
- The configurable properties displayed on the AdminConsole are changed to use the database specific terminology.
- The .jar files necessary to connect to the specified database are included within the Adapter.
Installation
Step 1: Stop the web server
Step 2: Copy the jars
Unzip kinetic-calendar-sql-adapter.zip into the lib directory (kineticCalendar\WEB-INF\lib). This zip includes the files for the Generic, DB2, MSSQL, and Oracle adapters.
Step 3: Edit adapters.json file
With a text editor, open the configuration file for Kinetic Calendar adapters (kineticCalendar\WEB-INF\configuration\adapters.json). Before you make any changes, make a copy of the file for safekeeping. Out of the box, the file looks like this:
[
"com.kineticdata.calendar.adapters.ars.ArsAdapter",
"com.kineticdata.calendar.adapters.exchange.ExchangeAdapter"
]
Add a comma after the Exchange adapter line, and add a reference to one or more of the SQL adapters. The result should look something like this:
[
"com.kineticdata.calendar.adapters.ars.ArsAdapter",
"com.kineticdata.calendar.adapters.exchange.ExchangeAdapter",
"com.kineticdata.calendar.adapters.sql.oracle.OracleAdapter"
]
Step 4: Configure Event info
You can use the configuration files in sample-sql-calendar-event-configs.zip as a guide. Download and unzip the sample configuration file into kineticCalendar\WEB-INF\configuration\calendars and modify them to your specific database schema.
Step 5: Restart the web server
Step 6: Add a SQL Connection
For specifics on how to setup the individual adapter connections (Generic, DB2, MS SQL, Oracle) please see the sub-articles for details.
Event Type Source Configuration
The event type source for a SQL Calendar Adapter defines the name of the connection for the calendar events, and two required configuration parameters.
On the event type configuration page, we saw that the source configuration property was dependent on the adapter used by the connection. The SQL Calendar Adapter requires the additional properties: "Events Query", and "Event Query"
Example Source Configuration
"source": {
"configuration": {
"Events Query": "select id, event_name, event_description, event_start, event_end FROM mytable WHERE (event_end is null AND event_start >= {{StartTime}} AND event_start <= {{EndTime}}) OR (event_start <= {{EndTime}} AND event_end >= {{StartTime}})",
"Event Query": "select id, event_name, event_description, event_start, event_end, event_location, event_creator, event_last_updated FROM mytable WHERE id = {{Id}}"
}
...
}
Events Query
This property specifies the SQL query used for getting multiple events, called when clicking on a Day, Week, Month, or List view. It can be any valid SQL query for your SQL server. In order to access the time period the users are looking at in your SQL query you can use {{StartTime}} and {{EndTime}} (no quotes around them ever!). {{StartTime}} and {{EndTime}} will be replaced with the elapsed milliseconds since midnight on January 1st, 1970 GMT up until the time of the period being viewed on the calendar. This is known as milliseconds since Epoch.
IMPORTANT NOTE ABOUT THE SQL WHERE QUALIFICATION
In order to get an event to show up on your calendar that starts on August 5th and ends on September 15th when you're viewing the month of September you need to have a qualification like the one above. If you simply put WHERE start >= {{StartTime}} AND end <= {{EndTime}} you'll miss events like this! Also if your database stores the time stamps in a format other than milliseconds since Epoch you'll need to do some conversions.
Convert A Date to Multiple Languages
A useful website for finding ways to convert a date in multiple languages to Epoch or visa versa: http://www.epochconverter.com/. All of the conversions on that website are in seconds since Epoch, not milliseconds. We also have sample calendar configuration files for MS SQL, Oracle, and DB2 which show for each database how to do this conversion on various column data types.
One more important note, when you do the conversions you should do them on {{StartTime}} and {{EndTime}}. If you do the conversion on the table columns instead then the database will have to do a scan and any indexes on those columns will then be all for naught. For making sure your query is optimized, convert {{StartTime}} and {{EndTime}} to be like the column type in the database.
SELECT
id,
event_name,
event_description,
event_start,
event_end
FROM
mytable
WHERE
(
event_end is null
AND
event_start >= {{StartTime}}
AND
event_start <= {{EndTime}}
)
OR
(
event_start <= {{EndTime}}
AND
event_end >= {{StartTime}}
)
Event Query
This property specifies the SQL query used for getting a single event, called when a user clicks on an event in the calendar view to get more details about the event. In order to access the unique instance of the event the user is clicking on in your SQL query you can use {{Id}}. No quotes around this needed in the query! {{Id}} will be replaced with the unique ID of a row in your SQL query. This must be a column returned back by your Events Query.
SELECT
id,
event_name,
event_description,
event_start,
event_end,
event_location,
event_creator,
event_last_updated
FROM
mytable
WHERE
id = {Id}}
Security Concerns
The SQL Calendar Adapter addresses SQL injection concerns by using prepared SQL statements. When prepared statements are used any substituted variables in the query ({{StartTime}}, {{EndTime}}, and {{Id}}) are replaced in such a way that any characters inside them cannot be executed as SQL code. The option to use stored procedures is available as well to further alleviate this concern.
While SQL Injection is not possible with this adapter it is always recommended to give service accounts the least amount of access they need in order to function for an application. Kinetic Calendar is a ready-only application for displaying event information and for that reason it is recommended that any account used as a connection with the application be given read-only access to the database.
Event Type Mappings
Event type mappings create a relationship between SQL column names and one of the event properties.
The syntax used to parse SQL column names must be consistent with the rules for mappings, which means that all SQL column names must be enclosed in mustaches (double braces). These rules apply to all event type mappings - core mappings, details mappings, and filter mappings.
Core Mappings
An example of the SQL Calendar core mapping:
"coreMappings": {
"Id": "{{id}}",
"Name": "{{event_name}}",
"Description": "{{event_description}}",
"Start": "{{event_start}}",
"End": "{{event_end}}"
}
Important
Core Mapping fields are case sensitive and if any of the core mapping fields are missing from the configuration you'll see an error on the Admin Console saying it was unable to verify the source.
Valid And Required Core Mappings
- Id
- Name
- Description
- Start
- End
Detail Mappings
An example of a SQL Calendar detail mapping:
"detailMappings": {
"Event Author": "{{event_created_by}}"
}
In this example, a mapping has been created to add an "Event Author" property to the event details. The value for this property will come from the SQL query column name "event_created_by"
Any Column name returned from the Event Query SQL statement is a Valid Detail Mapping
Filter Mapping Properties
An example of a filter mapping:
"filterMappings": [
{
"name": "Event Author",
"value": "{{event_created_by}}",
"values": {}
},
{
"name": "Location",
"value": "{{event_location}}",
"values": {}
}
]
In this example, a mapping has been created to add an "Event Author" filter and "Location" filter. The filter values for this property will come from the SQL query results in the column names "event_created_by" and "event_location"
Any column name returned from the Events Query SQL statement is a valid Filter Mapping
Updated over 2 years ago