From 582c03eacbc73b851aa232c404be71b2cf77295a Mon Sep 17 00:00:00 2001 From: Joris Date: Fri, 7 Feb 2025 10:35:20 +0100 Subject: Use strict mode for tables --- src/db/migrations/01-init.sql | 13 ++++++++ src/db/migrations/02-categories.sql | 11 +++++++ src/db/migrations/03-event-color.sql | 5 +++ src/db/migrations/04-strict-tables.sql | 57 ++++++++++++++++++++++++++++++++++ src/db/migrations/1-init.sql | 13 -------- src/db/migrations/2-categories.sql | 11 ------- src/db/migrations/3-event-color.sql | 5 --- 7 files changed, 86 insertions(+), 29 deletions(-) create mode 100644 src/db/migrations/01-init.sql create mode 100644 src/db/migrations/02-categories.sql create mode 100644 src/db/migrations/03-event-color.sql create mode 100644 src/db/migrations/04-strict-tables.sql delete mode 100644 src/db/migrations/1-init.sql delete mode 100644 src/db/migrations/2-categories.sql delete mode 100644 src/db/migrations/3-event-color.sql (limited to 'src/db/migrations') diff --git a/src/db/migrations/01-init.sql b/src/db/migrations/01-init.sql new file mode 100644 index 0000000..467e481 --- /dev/null +++ b/src/db/migrations/01-init.sql @@ -0,0 +1,13 @@ +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 */ +); + +CREATE INDEX events_date_index on events (date); +CREATE INDEX events_repetition_index on events (repetition); diff --git a/src/db/migrations/02-categories.sql b/src/db/migrations/02-categories.sql new file mode 100644 index 0000000..0b373d0 --- /dev/null +++ b/src/db/migrations/02-categories.sql @@ -0,0 +1,11 @@ +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 */ +); + +ALTER TABLE "events" ADD COLUMN "category" TEXT REFERENCES "categories" ("id"); /* UUID */ + +PRAGMA foreign_keys = ON diff --git a/src/db/migrations/03-event-color.sql b/src/db/migrations/03-event-color.sql new file mode 100644 index 0000000..ec589ea --- /dev/null +++ b/src/db/migrations/03-event-color.sql @@ -0,0 +1,5 @@ +CREATE TABLE IF NOT EXISTS "event_color" ( + "color" TEXT NOT NULL /* COLOR */ +); + +INSERT INTO "event_color" ("color") VALUES ("#a8c2e0"); 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); diff --git a/src/db/migrations/1-init.sql b/src/db/migrations/1-init.sql deleted file mode 100644 index 467e481..0000000 --- a/src/db/migrations/1-init.sql +++ /dev/null @@ -1,13 +0,0 @@ -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 */ -); - -CREATE INDEX events_date_index on events (date); -CREATE INDEX events_repetition_index on events (repetition); diff --git a/src/db/migrations/2-categories.sql b/src/db/migrations/2-categories.sql deleted file mode 100644 index 0b373d0..0000000 --- a/src/db/migrations/2-categories.sql +++ /dev/null @@ -1,11 +0,0 @@ -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 */ -); - -ALTER TABLE "events" ADD COLUMN "category" TEXT REFERENCES "categories" ("id"); /* UUID */ - -PRAGMA foreign_keys = ON diff --git a/src/db/migrations/3-event-color.sql b/src/db/migrations/3-event-color.sql deleted file mode 100644 index ec589ea..0000000 --- a/src/db/migrations/3-event-color.sql +++ /dev/null @@ -1,5 +0,0 @@ -CREATE TABLE IF NOT EXISTS "event_color" ( - "color" TEXT NOT NULL /* COLOR */ -); - -INSERT INTO "event_color" ("color") VALUES ("#a8c2e0"); -- cgit v1.2.3