diff options
Diffstat (limited to 'src/db/migrations')
-rw-r--r-- | src/db/migrations/01-init.sql (renamed from src/db/migrations/1-init.sql) | 0 | ||||
-rw-r--r-- | src/db/migrations/02-categories.sql (renamed from src/db/migrations/2-categories.sql) | 0 | ||||
-rw-r--r-- | src/db/migrations/03-event-color.sql (renamed from src/db/migrations/3-event-color.sql) | 0 | ||||
-rw-r--r-- | src/db/migrations/04-strict-tables.sql | 57 |
4 files changed, 57 insertions, 0 deletions
diff --git a/src/db/migrations/1-init.sql b/src/db/migrations/01-init.sql index 467e481..467e481 100644 --- a/src/db/migrations/1-init.sql +++ b/src/db/migrations/01-init.sql diff --git a/src/db/migrations/2-categories.sql b/src/db/migrations/02-categories.sql index 0b373d0..0b373d0 100644 --- a/src/db/migrations/2-categories.sql +++ b/src/db/migrations/02-categories.sql diff --git a/src/db/migrations/3-event-color.sql b/src/db/migrations/03-event-color.sql index ec589ea..ec589ea 100644 --- a/src/db/migrations/3-event-color.sql +++ b/src/db/migrations/03-event-color.sql 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); |