aboutsummaryrefslogtreecommitdiff
path: root/src/db/migrations/04-strict-tables.sql
blob: 7a10b3188f679e7747622df447a26e871b6a66ba (plain)
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
-- Categories

ALTER TABLE "categories" RENAME TO "categories_non_strict";

CREATE TABLE IF NOT EXISTS "categories" (
  "id" TEXT PRIMARY KEY, /* UUID */
  "name" TEXT NOT NULL UNIQUE,
  "color" TEXT NOT NULL, /* COLOR */
  "created" TEXT NOT NULL, /* DATETIME */
  "updated" TEXT NOT NULL /* DATETIME */
) STRICT;

INSERT INTO categories (id, name, color, created, updated)
  SELECT id, name, color, created, updated
  FROM categories_non_strict;

DROP TABLE categories_non_strict;

-- Event color

CREATE TABLE IF NOT EXISTS "event_colors" (
  "color" TEXT NOT NULL /* COLOR */
) STRICT;

INSERT INTO event_colors (color)
  SELECT color
  FROM event_color;

DROP TABLE event_color;

-- Events

ALTER TABLE "events" RENAME TO "events_non_strict";

CREATE TABLE IF NOT EXISTS "events" (
  "id" TEXT PRIMARY KEY, /* UUID */
  "date" TEXT NOT NULL, /* DATE */
  "start" TEXT NULL, /* TIME */
  "end" TEXT NULL, /* TIME */
  "name" TEXT NOT NULL,
  "repetition" TEXT NULL, /* JSON */
  "created" TEXT NOT NULL, /* DATETIME */
  "updated" TEXT NOT NULL, /* DATETIME */
  "category" TEXT REFERENCES "categories" ("id")
) STRICT;

INSERT INTO events (id, date, start, end, name, repetition, created, updated, category)
  SELECT id, date, start, end, name, repetition, created, updated, category
  FROM events_non_strict;

DROP TABLE events_non_strict;

DROP INDEX IF EXISTS events_date_index;
CREATE INDEX events_date_index on events (date);

DROP INDEX IF EXISTS events_repetition_index;
CREATE INDEX events_repetition_index on events (repetition);