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