diff options
Diffstat (limited to 'src/db/migrations/04-strict-tables.sql')
-rw-r--r-- | src/db/migrations/04-strict-tables.sql | 57 |
1 files changed, 57 insertions, 0 deletions
diff --git a/src/db/migrations/04-strict-tables.sql b/src/db/migrations/04-strict-tables.sql new file mode 100644 index 0000000..7a10b31 --- /dev/null +++ b/src/db/migrations/04-strict-tables.sql @@ -0,0 +1,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); |