はじめに
前回までにGoogleCalendarのAPIの仕様を学びました。今回は実際にTBLの作成、データ挿入して理解を深めていきたいと思います。
Google Calendarシリーズ
Google CalendarAPIを学ぶ(Step1:APIの仕様、リソースを理解する)
GoogleCalendar APIを学ぶ(Step2:TBL作成、データ挿入)
GoogleCalendar APIを学ぶ(Step3:カレンダーの取得APIを実装する)
GoogleCalendar APIを学ぶ(Step4:カレンダーに紐づくイベントの一覧取得APIを実装する)
TBL設計
dbdiagram
dbdiagramのコードです。興味のある方は自分で編集して遊んでみてくださいTable users {
id bigint [pk, increment]
email varchar [unique, not null]
display_name varchar
locale varchar [default: 'Asia/Tokyo']
created_at timestamptz
}
Table user_settings {
id bigint [pk, increment]
user_id bigint [pk, ref: > users.id]
default_reminders jsonb
working_hours jsonb
color_theme varchar
notification_settings jsonb
updated_at timestamptz
}
Table calendars {
id bigint [pk, increment]
owner_id bigint [not null, ref: > users.id]
summary varchar
description text
color varchar
visibility varchar
created_at timestamptz
updated_at timestamptz
}
Table calendar_memberships {
id bigint [pk, increment]
calendar_id uuid [ref: > calendars.id]
user_id bigint [ref: > users.id]
role varchar
Primary Key (calendar_id, user_id)
}
Table events {
id bigint [pk, increment]
calendar_id uuid [not null, ref: > calendars.id]
ical_uid varchar [unique]
status varchar
sequence int
title varchar
description text
location text
color varchar
organizer_id bigint [ref: > users.id]
all_day boolean
end_time_unspecified boolean
time_range tstzrange
recurrence_rule text
recurrence text[]
recurrence_exceptions tsmultirange
recurring_event_id uuid [ref: > events.id]
original_start_time timestamptz
transparency varchar
visibility varchar
extended_private jsonb
extended_shared jsonb
created_at timestamptz
updated_at timestamptz
}
Table event_attendees {
id bigint [pk, increment]
event_id uuid [ref: > events.id]
email varchar
user_id bigint [ref: > users.id]
display_name varchar
organizer boolean
optional boolean
response_status varchar
comment text
additional_guests int
Primary Key (event_id, email)
}
Table event_reminders {
id bigint [pk, increment]
event_id uuid [ref: > events.id]
method varchar
minutes_before int
Primary Key (event_id, method, minutes_before)
}
Table calendar_acl {
id varchar [pk]
calendar_id uuid [ref: > calendars.id]
scope_type varchar
scope_value varchar
role varchar
}
TBLの作成
今回はdocker上にPostgreSQL16を立て、TBLを作成します。
docker-compose.yml
version: "3.9"
services:
db:
image: postgres:16
container_name: calendar_db
restart: always
environment:
POSTGRES_DB: calendar
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
ports:
- "5436:5432"
volumes:
- ./pgdata:/var/lib/postgresql/data
- ./init:/docker-entrypoint-initdb.d
ddl
-- ユーザー情報
CREATE TABLE "users" (
"id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- ユーザーID
"email" varchar UNIQUE NOT NULL, -- メールアドレス
"display_name" varchar, -- 表示名
"locale" varchar DEFAULT 'Asia/Tokyo', -- ロケール（デフォルト：Asia/Tokyo）
"created_at" timestamptz -- 登録日時
);
-- ユーザーの個別設定
CREATE TABLE "user_settings" (
"id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 設定ID
"user_id" bigint, -- ユーザーID
"default_reminders" jsonb, -- デフォルトリマインダー
"working_hours" jsonb, -- 勤務時間
"color_theme" varchar, -- カラーテーマ
"notification_settings" jsonb, -- 通知設定
"updated_at" timestamptz -- 更新日時
);
-- カレンダー
CREATE TABLE "calendars" (
"id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- カレンダーID
"owner_id" bigint NOT NULL, -- 所有者ユーザーID
"summary" varchar, -- カレンダー名
"description" text, -- 説明
"color" varchar, -- カラー
"visibility" varchar, -- 公開設定
"created_at" timestamptz, -- 作成日時
"updated_at" timestamptz -- 更新日時
);
-- カレンダーの参加者情報
CREATE TABLE "calendar_memberships" (
"id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- レコードID
"calendar_id" bigint, -- カレンダーID
"user_id" bigint, -- ユーザーID
"role" varchar -- 権限（owner, readerなど）
);
-- イベント
CREATE TABLE "events" (
"id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- イベントID
"calendar_id" bigint NOT NULL, -- カレンダーID
"ical_uid" varchar UNIQUE, -- iCal UID
"status" varchar, -- ステータス
"sequence" int, -- バージョン番号
"title" varchar, -- タイトル
"description" text, -- 内容
"location" text, -- 場所
"color" varchar, -- 色
"organizer_id" bigint, -- 主催ユーザーID
"all_day" boolean, -- 終日かどうか
"end_time_unspecified" boolean, -- 終了時間が不明か
"time_range" tstzrange, -- 時間範囲
"recurrence_rule" text, -- 繰り返しルール
"recurrence" text[], -- 繰り返しの詳細
"recurrence_exceptions" tsmultirange, -- 繰り返し例外
"recurring_event_id" bigint, -- 繰り返し元イベントID
"original_start_time" timestamptz, -- オリジナル開始時間
"transparency" varchar, -- 予定の透明性
"visibility" varchar, -- 公開設定
"extended_private" jsonb, -- 拡張（非公開）
"extended_shared" jsonb, -- 拡張（共有）
"created_at" timestamptz,
"updated_at" timestamptz
);
-- イベント出席者
CREATE TABLE "event_attendees" (
"id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 出席者ID
"event_id" bigint, -- イベントID
"email" varchar, -- メール
"user_id" bigint, -- ユーザーID
"display_name" varchar, -- 表示名
"organizer" boolean, -- 主催者フラグ
"optional" boolean, -- 任意か
"response_status" varchar, -- 出席ステータス
"comment" text, -- コメント
"additional_guests" int -- 同伴者数
);
-- リマインダー
CREATE TABLE "event_reminders" (
"id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- リマインダーID
"event_id" bigint, -- イベントID
"method" varchar, -- 通知方法
"minutes_before" int -- 何分前に通知するか
);
-- カレンダーのアクセス制御
CREATE TABLE "calendar_acl" (
"id" varchar PRIMARY KEY, -- ACL ID（文字列型）
"calendar_id" bigint, -- カレンダーID
"scope_type" varchar, -- スコープ種別（user等）
"scope_value" varchar, -- 対象値（emailなど）
"role" varchar -- 権限（reader, writer, owner）
);
-- 外部キー制約
ALTER TABLE "user_settings" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");
ALTER TABLE "calendars" ADD FOREIGN KEY ("owner_id") REFERENCES "users" ("id");
ALTER TABLE "calendar_memberships" ADD FOREIGN KEY ("calendar_id") REFERENCES "calendars" ("id");
ALTER TABLE "calendar_memberships" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");
ALTER TABLE "events" ADD FOREIGN KEY ("calendar_id") REFERENCES "calendars" ("id");
ALTER TABLE "events" ADD FOREIGN KEY ("organizer_id") REFERENCES "users" ("id");
ALTER TABLE "events" ADD FOREIGN KEY ("recurring_event_id") REFERENCES "events" ("id");
ALTER TABLE "event_attendees" ADD FOREIGN KEY ("event_id") REFERENCES "events" ("id");
ALTER TABLE "event_attendees" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");
ALTER TABLE "event_reminders" ADD FOREIGN KEY ("event_id") REFERENCES "events" ("id");
ALTER TABLE "calendar_acl" ADD FOREIGN KEY ("calendar_id") REFERENCES "calendars" ("id");
データのinsert
-- users
INSERT INTO users (email, display_name, created_at)
VALUES
('alice@example.com', 'Alice', now()),
('bob@example.com', 'Bob', now());
-- user_settings
INSERT INTO user_settings (user_id, default_reminders, working_hours, color_theme, notification_settings, updated_at)
VALUES
(1, '{"popup": 10}', '{"start": "09:00", "end": "18:00"}', 'blue', '{"email": true}', now()),
(2, '{"popup": 15}', '{"start": "10:00", "end": "17:00"}', 'green', '{"email": false}', now());
-- calendars
INSERT INTO calendars (owner_id, summary, description, color, visibility, created_at, updated_at)
VALUES
(1, 'Alice Work', 'Work calendar for Alice', 'blue', 'default', now(), now()),
(2, 'Bob Personal', 'Personal calendar for Bob', 'green', 'private', now(), now());
-- calendar_memberships
INSERT INTO calendar_memberships (calendar_id, user_id, role)
VALUES
(1, 1, 'owner'),
(1, 2, 'reader'),
(2, 2, 'owner');
-- events
INSERT INTO events (calendar_id, ical_uid, status, sequence, title, description, location, color, organizer_id,
all_day, end_time_unspecified, time_range, created_at, updated_at)
VALUES
(1, 'evt-1@google.com', 'confirmed', 0, 'Project Kickoff', 'Initial project meeting', 'Zoom', 'blue', 1,
false, false, tstzrange(now(), now() + interval '1 hour'), now(), now()),
(2, 'evt-2@google.com', 'tentative', 0, 'Dentist Appointment', 'Routine check-up', 'Clinic', 'green', 2,
false, false, tstzrange(now() + interval '1 day', now() + interval '1 day 1 hour'), now(), now());
-- event_attendees
INSERT INTO event_attendees (event_id, email, user_id, display_name, organizer, optional, response_status, comment, additional_guests)
VALUES
(1, 'bob@example.com', 2, 'Bob', false, false, 'accepted', 'Looking forward to it!', 0),
(2, 'alice@example.com', 1, 'Alice', false, false, 'tentative', '', 0);
-- event_reminders
INSERT INTO event_reminders (event_id, method, minutes_before)
VALUES
(1, 'popup', 10),
(2, 'email', 30);
-- calendar_acl
INSERT INTO calendar_acl (id, calendar_id, scope_type, scope_value, role)
VALUES
('acl-1', 1, 'user', 'bob@example.com', 'reader'),
('acl-2', 2, 'user', 'alice@example.com', 'writer');
ユースケースに応じたSQLの実行
カレンダー関連のSQL
1. カレンダーを作成する
ログインユーザーが新しいカレンダーを作成する（例：「チームプロジェクト用」など）
calendar=# INSERT INTO "calendars" (owner_id, summary, description, color, visibility, created_at, updated_at)
VALUES (1, 'Team Project', 'カンファレンスや締切用', 'blue', 'default', now(), now());
INSERT 0 1
2. ユーザーが所属するカレンダー一覧を取得する
calendar=# SELECT c.*
FROM calendars c
JOIN calendar_memberships cm ON c.id = cm.calendar_id
WHERE cm.user_id = 1;
id | owner_id | summary | description | color | visibility | created_at | updated_at
----+----------+------------+-------------------------+-------+------------+-------------------------------+-------------------------------
1 | 1 | Alice Work | Work calendar for Alice | blue | default | 2025-05-17 05:57:04.619288+00 | 2025-05-17 05:57:04.619288+00
(1 row)
3. 特定のカレンダーの詳細を取得する
calendar=# SELECT * FROM calendars WHERE id = 1;
id | owner_id | summary | description | color | visibility | created_at | updated_at
----+----------+------------+-------------------------+-------+------------+-------------------------------+-------------------------------
1 | 1 | Alice Work | Work calendar for Alice | blue | default | 2025-05-17 05:57:04.619288+00 | 2025-05-17 05:57:04.619288+00
(1 row)
4.カレンダーへの参加メンバー一覧を取得する
calendar=# SELECT u.id, u.display_name, cm.role
FROM users u
JOIN calendar_memberships cm ON u.id = cm.user_id
WHERE cm.calendar_id = 1;
id | display_name | role
----+--------------+--------
1 | Alice | owner
2 | Bob | reader
(2 rows)
イベント関連
1. カレンダーにイベントを登録する
calendar=# INSERT INTO events (
calendar_id, ical_uid, status, sequence, title, description, location, color, organizer_id,
all_day, end_time_unspecified, time_range, created_at, updated_at
)
VALUES (
1, 'evt-3@google.com', 'confirmed', 0, 'Team Sync', 'Weekly sync meeting', 'Google Meet', 'blue', 1,
false, false, tstzrange('2025-05-20 10:00:00+09', '2025-05-20 11:00:00+09'), now(), now()
);
INSERT 0 1
開区間、閉区間のお話が大事です！一般的に範囲の指定の場合は、[start, end)（右端排他）です。今回、間違って開区間で入れています。最初は気づかないんですが、後々痛い目を見るので、気をつけましょう！
開区間、閉区間の話はここで記事にしています。
2. 指定期間中のイベントを一覧取得する
calendar=# SELECT * FROM events
WHERE calendar_id = 1
AND time_range && tstzrange('2025-05-01 00:00:00+09', '2025-05-31 23:59:59+09');
id | calendar_id | ical_uid | status | sequence | title | description | location | color | organizer_id | all_day | end_time_unspecified | time_range | recurrence_rule | recurrence | recurrence_exceptions | recurring_event_id | original_start_time | transparency | visibility | extended_private | extended_shared | created_at | updated_at
----+-------------+------------------+-----------+----------+-----------------+-------------------------+-------------+-------+--------------+---------+----------------------+-------------------------------------------------------------------+-----------------+------------+-----------------------+--------------------+---------------------+--------------+------------+------------------+-----------------+-------------------------------+-------------------------------
1 | 1 | evt-1@google.com | confirmed | 0 | Project Kickoff | Initial project meeting | Zoom | blue | 1 | f | f | ["2025-05-17 05:57:09.915029+00","2025-05-17 06:57:09.915029+00") | | | | | | | | | | 2025-05-17 05:57:09.915029+00 | 2025-05-17 05:57:09.915029+00
3 | 1 | evt-3@google.com | confirmed | 0 | Team Sync | Weekly sync meeting | Google Meet | blue | 1 | f | f | ["2025-05-20 01:00:00+00","2025-05-20 02:00:00+00") | | | | | | | | | | 2025-05-17 06:09:05.172634+00 | 2025-05-17 06:09:05.172634+00
(2 rows)
これは開区間/開区間の絞り込みですね。一般的に、開区間/閉区間
3.繰り返しイベントを登録する（毎週・毎月など）
calendar=# INSERT INTO events (
calendar_id, ical_uid, status, sequence, title, recurrence_rule, time_range, created_at, updated_at
)
VALUES (
1, 'evt-4@google.com', 'confirmed', 0, 'Weekly Check-In',
'RRULE:FREQ=WEEKLY;BYDAY=MO',
tstzrange('2025-05-05 10:00:00+09', '2025-05-05 10:30:00+09'),
now(), now()
);
INSERT 0 1
繰り返しはRRuleの話ですね。ちょっと難しいです。また、どこかで記事にして理解を深めたいと思います。
4.イベントに通知リマインダーを設定する
calendar=# INSERT INTO event_reminders (event_id, method, minutes_before)
VALUES (3, 'email', 30);
INSERT 0 1
アクセス制御関連
1.他ユーザーにカレンダーの「閲覧・編集」権限を付与する
calendar=# INSERT INTO calendar_acl (id, calendar_id, scope_type, scope_value, role)
VALUES ('acl-3', 1, 'user', 'bob@example.com', 'writer');
INSERT 0 1
2.カレンダーへのアクセス制御リスト（ACL）を取得する
calendar=# SELECT * FROM calendar_acl WHERE calendar_id = 1;
id | calendar_id | scope_type | scope_value | role
-------+-------------+------------+-----------------+--------
acl-1 | 1 | user | bob@example.com | reader
acl-3 | 1 | user | bob@example.com | writer
(2 rows)