1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
|
CREATE TABLE calendars (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
description TEXT,
is_archived INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
);
CREATE INDEX idx_calendars_archived
ON calendars (is_archived);
/* Only for nonrecurrent events */
CREATE TABLE events (
id TEXT PRIMARY KEY,
calendar_id TEXT NOT NULL,
title TEXT NOT NULL,
description TEXT,
starts_at TEXT NOT NULL,
ends_at TEXT NOT NULL,
color INTEGER NOT NULL DEFAULT 0,
is_all_day INTEGER NOT NULL DEFAULT 0,
is_cancelled INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
FOREIGN KEY (calendar_id)
REFERENCES calendars(id)
ON DELETE CASCADE,
CHECK (starts_at < ends_at),
CHECK (color BETWEEN 0 AND 255)
);
CREATE TABLE recurrences (
id TEXT PRIMARY KEY,
calendar_id TEXT NOT NULL,
title TEXT NOT NULL,
description TEXT,
starts_at TEXT NOT NULL,
ends_at TEXT NOT NULL,
frequency TEXT NOT NULL,
interval INTEGER NOT NULL,
until TEXT,
color INTEGER NOT NULL DEFAULT 0,
is_all_day INTEGER NOT NULL DEFAULT 0,
is_cancelled INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
FOREIGN KEY (calendar_id)
REFERENCES calendars(id)
ON DELETE CASCADE,
CHECK (starts_at < ends_at),
CHECK (interval > 0),
CHECK (color BETWEEN 0 AND 255)
);
CREATE TABLE recurrence_exceptions (
recurrence_id TEXT NOT NULL,
original_starts_at TEXT NOT NULL,
new_starts_at TEXT,
new_ends_at TEXT,
is_cancelled INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (recurrence_id, original_starts_at)
FOREIGN KEY (recurrence_id)
REFERENCES recurrences(id)
ON DELETE CASCADE
);
CREATE INDEX idx_events_overlap
ON events (calendar_id, ends_at, starts_at)
WHERE is_cancelled = 0;
CREATE INDEX idx_events_active
ON events (calendar_id, is_cancelled, starts_at);
CREATE INDEX idx_events_calendar_dates
ON events (calendar_id, starts_at, ends_at)
WHERE is_cancelled = 0;
CREATE INDEX idx_recurrences_calendar
ON recurrences (calendar_id);
CREATE INDEX idx_recurrences_start_end
ON recurrences (starts_at, ends_at);
CREATE INDEX idx_recurrences_active
ON recurrences (is_cancelled, until);
CREATE INDEX idx_recurrence_exceptions_recurrence
ON recurrence_exceptions (recurrence_id);
CREATE INDEX idx_recurrence_exceptions_original_date
ON recurrence_exceptions (recurrence_id, original_starts_at);
|