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

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

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

## [CData JDBC Driver for Google Calendar](https://www.cdata.com/jp/drivers/googlecalendar/jdbc/)とは

[CData社](https://www.cdata.com/jp/)が作った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なので、あらかじめリダイレクトサーバーを立てておきます。

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

```scala
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を関数型チックに(コレクション操作ができるように)デコレートしておきます。

```scala
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の用意です。

```scala
// 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生成します。

```Scala
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を生成して認可コードを取得します。

```Scala
// 認可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()
```

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

```scala
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の処理は省きます。

```Scala
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`の予定一覧を見ます。

```sql
select * from "hogehoge@gmail.com"
```

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


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

```sql
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は必須パラメータです。

#### 特定の予定を更新
```sql
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'
```

#### 予定を削除する
```sql
delete "hogehoge@gmail.com" where Summary = '会議1'
```

### 繰り返し予定を入れる

```sql
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さん](https://howdy.hatenablog.com/about)という方のブログがわかりやすかったです: 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さん](https://howdy.hatenablog.com/about)の繰り返し予定についての記事: http://howdy.hatenablog.com/entry/20120908/1347115729