CData JDBC Driver for Google Calendar2019の動作確認メモ

1年前くらいに残したメモですが、もったいないなと思ったので公開します。

CData JDBC Driver for Google Calendarとは

CData社が作ったGoogleカレンダーにアクセスするJDBCドライバーです。

JDBCインターフェースを通して、他のユーザーの予定一覧を取得できるだけでなく、予定を入れたり削除したりできてしまいます。

機械的にGoogleカレンダー利用者の予定を操作できるので、管理職系の業務で役立ちそうなことが多そうです。またBIツールのようなデータ分析アプリケーションであれば他データとの関連づけも期待できます。

前提

GCPアカウント https://console.cloud.google.com/
- OAuth2.0クライアントの情報(クライアントID,クライアントシークレットとか)
- Google Calendar APIのサービスが有効になっていること
CDataのライセンス(RTK)
JDBC Driverとその実行環境
- cdata.jdbc.googlecalendar.jar
- Java/Scala等のJVM環境

やること

JDBCのストアドプロシージャで接続の確認
予定表を操作するサンプルクエリの実行

ストアドプロシージャで接続確認

まず、CData社が用意したストアドプロシージャを使ってOAuth2認証の手順を踏んでいきます。

参考にしたリファレンスの場所はここら辺です。

カスタムクレデンシャル: http://cdn.cdata.com/help/EGE/jp/jdbc/pg_oauthcustomapp.htm
ストアドプロシージャ: http://cdn.cdata.com/help/EGE/jp/jdbc/pg_allsps.htm

OAuth認証フローで以下2つのストアドプロシージャを利用します。

認可コードの取得：GetOAuthAuthorizationURL
アクセストークンの取得：GetOAuthAccessToken

アクセストークン取得まで

簡略化のために例外処理やtry-with-resourcesの処理は省きます。手続きをわかりやすくすしたいので、べた書きします。また、OAuth2なので、あらかじめリダイレクトサーバーを立てておきます。

一応ですがインポート文を載せときます。

import java.awt.Desktop
import java.net.URI
import java.sql.ResultSet
import scala.collection.mutable
import java.sql.Driver
import java.sql.Connection
import java.{util => ju}
import java.sql.CallableStatement

それと、あらかじめJDBCのResultSetを関数型チックに(コレクション操作ができるように)デコレートしておきます。

implicit class RichResultSet(self: ResultSet) {
  def map[T](f: ResultSet => T): Seq[T] = {
    val buf = mutable.ListBuffer.empty[T]
    while (self.next()) {
      buf += f(self)
    }
    buf
  }
}

では、まず認証情報とJDBC Driverの用意です。

// GCPアカウントで作成したOAuthクライアントの情報
val clientId = "{クライアント ID}"
val clientSecret = "{クライアントシークレット}"
val redirectUrl = "{リダイレクトURL}"
// ライセンス
val RTK = "{RKT}"
// CDataのJDBC Driver
val driver: Driver = Class.forName("cdata.jdbc.googlecalendar.GoogleCalendarDriver").newInstance().asInstanceOf[java.sql.Driver]

Connection生成します。

val url = "jdbc:googlecalendar:"
val prop: ju.Properties = new ju.Properties
prop.setProperty("OAuthClientId",clientId)
prop.setProperty("OAuthClientSecret",clientSecret)
prop.setProperty("RTK",RTK)
val connection:Connection = driver.connect(url,prop)

認可URLを生成して認可コードを取得します。

// 認可URLを取得
val getOAuthAuthorizationURL: CallableStatement = connection.prepareCall("{call GetOAuthAuthorizationURL}")
getOAuthAuthorizationURL.setString("CallbackURL",redirectUrl)
val authUrl = getOAuthAuthorizationURL.executeQuery().map(_.getString("URL")).head

// ブラウザ起動
val desktop = Desktop.getDesktop()
val uri = new URI(authUrl)
print("input auth code:")
desktop.browse(uri)

// 起動したブラウザのULRからcode="認可コード"をコピペして入力
val authCode = io.StdIn.readLine()

最後に、アクセストークンの取得です。

val getOAuthAccessToken: CallableStatement  = connection.prepareCall("{call GetOAuthAccessToken}")
getOAuthAccessToken.setString("Verifier",authCode) // 認可コード
getOAuthAccessToken.setString("CallbackURL",redirectUrl)
val token = getOAuthAccessToken.executeQuery().map(_.getString("OAuthAccessToken")).head
println(token) // これがアクセストークン!

試しにクエリを叩いてみる

ここでも簡略化のために例外処理やtry-with-resourcesの処理は省きます。

val token = "{取得したアクセストークン}"
val RTK = "{RKT}"
val redirectUri = "{リダイレクトURL}"
val prop = new ju.Properties
prop.setProperty("OAuth Access Token",token)
prop.setProperty("RTK",RTK)
val url = "jdbc:googlecalendar:"
val driver: Driver = Class.forName("cdata.jdbc.googlecalendar.GoogleCalendarDriver").newInstance().asInstanceOf[java.sql.Driver]
val connection = driver.connect(url,prop)
val statement = connection.createStatement()
val sql = """ select * from "hogehoge@gmail.com" """
val res: ResultSet = statement.executeQuery(sql)
res.map(rs => 
  (rs.getString("Summary"),       // タイトル
   rs.getString("Description"),   // メモ
   rs.getString("StartDateTime"), // 開始日時
   rs.getString("EndDateTime"),   // 終了日時
   rs.getString("Location"))      // 場所
).foreach(println)

実行結果はこんな感じです。

(Takeshima event,hoge,2020-02-24T14:00:00.000+09:00,2020-03-03T17:00:00.000+09:00,イスタンブル, トルコ イスタンブール県 イスタンブル)
(Over drive!!,null,2020-02-21T12:00:00.000+09:00,2020-02-21T15:15:00.000+09:00,null)
(hey!!!!,null,2020-02-19T16:30:00.000+09:00,2020-02-19T21:00:00.000+09:00,null)
(new Event!!!!!,null,2020-02-23T07:00:00.000+09:00,2020-02-23T08:00:00.000+09:00,null)
(Hello MB,null,2020-02-23T12:00:00.000+09:00,2020-02-24T12:00:00.000+09:00,null)

予定表を操作するサンプルクエリの実行

簡単なSQL文を書いてみます。

予定一覧を取得

ユーザーhogehoge@gmail.comの予定一覧を見ます。

select * from "hogehoge@gmail.com"

特定のユーザーのカレンダー情報が格納されるテーブルは、メールアドレスがそのままテーブル名になる場合がある(Googleカレンダーのユーザー名はデフォルトではメールアドレスがそのままユーザー名になる)ので、テーブル名をダブルクォーテーションで囲まないと構文エラーになりがちです。

予定を入れる

ユーザーhogehoge@gmail.comに対して予定を新規作成します。

insert into "hogehoge@gmail.com" (
  Summary, StartDateTime, EndDateTime
) 
values 
  (
    '会議1', '2020-02-23T12:00:00.000+09:00', 
    '2020-02-24T12:00:00.000+09:00'
  )

StartDateTimeとEndDateTimeは必須パラメータです。

特定の予定を更新

update 
  "hogehoge@gmail.com" 
set 
  StartDateTime = '2020-02-23T10:00:00.000+12:00', 
  EndDateTime = '2020-02-23T11:00:00.000+12:00' 
where 
  Summary = '会議1'

予定を削除する

delete "hogehoge@gmail.com" where Summary = '会議1'

繰り返し予定を入れる

insert into "hogehoge@gmail.com" (
  Summary,
  StartDateTime,
  StartDateTimeZone, 
  EndDateTime,
  EndDateTimeZone,
  Recurrences
) 
values 
  (
    '繰り返し予定',
    '2020-02-28T10:00:00.000+09:00', 
    'Asia/Tokyo',
    '2020-02-28T12:00:00.000+09:00', 
    'Asia/Tokyo',
    'RRULE:FREQ=WEEKLY;BYDAY=FR'
  )

繰り返し予定を作成する際は結構面倒です。
以下必要なパラメーターです。

列名	概要	例
Recurrences	繰り返しのルール	RRULE:FREQ=WEEKLY;BYDAY=FR → 毎週金曜日になる
StartDateTime	始まり時刻	2020-02-28T10:00:00.000+09:00
StartDateTimeZone	始まり時刻のタイムゾーン	Asia/Tokyo
EndDateTime	終わり時刻	2020-02-28T12:00:00.000+09:00
EndDateTimeZone	終わり時刻のタイムゾーン	Asia/Tokyo

Recurrencesについて

繰り返しのルールを指定することでそのルールに従った繰り返しイベントが作成されます。例えば「RRULE:FREQ=WEEKLY;COUNT=3」をinsertするとその曜日を3週繰り返すことになります。

公式リファレンス：https://developers.google.com/calendar/v3/reference/events/insert
Tatsuya Nakanoさんという方のブログがわかりやすかったです: http://howdy.hatenablog.com/entry/20120908/1347115729

StartDateTimeZoneとEndDateTimeZoneを指定しないときに起きること

WebAPIのPOSTメソッドを直接叩く場合は400がレスポンスとして帰ってきましたが、本記事で使用したDriverではINSERT文を発行してもエラーは帰ってきませんでした。そして、INSERT文でRecurrencesの値を指定してもnullになってしまうため、繰り返し予定が作成されないという動作になっています。

終わり

以上です。

本記事は1年前のメモをQiitaに書き起こしたということでしたが、当時の自分はJDBCインターフェースでOAuth認証ができることに感動してた記憶があります。CDataすごい。

では、ありがとうございました。

参考文献

CData JDBC Driver for Google Calendar2019 リファレンス:　http://cdn.cdata.com/help/EGE/jp/jdbc/default.htm
Google Calendar API リファレンス:https://developers.google.com/calendar/v3/reference/
Tatsuya Nakanoさんの繰り返し予定についての記事: http://howdy.hatenablog.com/entry/20120908/1347115729