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/event_color.rs | 14 --------- src/db/event_colors.rs | 14 +++++++++ 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 --- src/db/mod.rs | 24 ++++++++++---- src/gui/app.rs | 2 +- 11 files changed, 119 insertions(+), 50 deletions(-) delete mode 100644 src/db/event_color.rs create mode 100644 src/db/event_colors.rs 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 diff --git a/src/db/event_color.rs b/src/db/event_color.rs deleted file mode 100644 index 33d350b..0000000 --- a/src/db/event_color.rs +++ /dev/null @@ -1,14 +0,0 @@ -use anyhow::Result; -use rusqlite::Connection; - -pub fn get_default_color(conn: &Connection) -> Result { - let mut stmt = conn.prepare("SELECT * FROM event_color LIMIT 1")?; - - let iter = stmt.query_map([], |row| row.get(0))?; - - let mut res = vec![]; - for color in iter { - res.push(color?) - } - Ok(res.first().unwrap_or(&"blue".to_string()).clone()) -} diff --git a/src/db/event_colors.rs b/src/db/event_colors.rs new file mode 100644 index 0000000..62b6146 --- /dev/null +++ b/src/db/event_colors.rs @@ -0,0 +1,14 @@ +use anyhow::Result; +use rusqlite::Connection; + +pub fn get_default_color(conn: &Connection) -> Result { + let mut stmt = conn.prepare("SELECT * FROM event_colors LIMIT 1")?; + + let iter = stmt.query_map([], |row| row.get(0))?; + + let mut res = vec![]; + for color in iter { + res.push(color?) + } + Ok(res.first().unwrap_or(&"blue".to_string()).clone()) +} 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"); diff --git a/src/db/mod.rs b/src/db/mod.rs index 20e7f81..0e73f30 100644 --- a/src/db/mod.rs +++ b/src/db/mod.rs @@ -1,5 +1,5 @@ pub mod categories; -pub mod event_color; +pub mod event_colors; pub mod events; use anyhow::Result; @@ -8,11 +8,23 @@ use rusqlite_migration::{Migrations, M}; pub fn init(db_path: &str) -> Result { let mut conn = Connection::open(db_path)?; + apply_migrations(&mut conn)?; + set_pragma(&conn, "foreign_keys", "ON")?; + set_pragma(&conn, "journal_mode", "wal")?; + Ok(conn) +} + +fn apply_migrations(conn: &mut Connection) -> Result<()> { let migrations = Migrations::new(vec![ - M::up(include_str!("migrations/1-init.sql")), - M::up(include_str!("migrations/2-categories.sql")), - M::up(include_str!("migrations/3-event-color.sql")), + M::up(include_str!("migrations/01-init.sql")), + M::up(include_str!("migrations/02-categories.sql")), + M::up(include_str!("migrations/03-event-color.sql")), + M::up(include_str!("migrations/04-strict-tables.sql")), ]); - migrations.to_latest(&mut conn)?; - Ok(conn) + migrations.to_latest(conn)?; + Ok(()) +} + +fn set_pragma(conn: &Connection, key: &str, value: &str) -> Result<()> { + Ok(conn.pragma_update(None, key, value)?) } diff --git a/src/gui/app.rs b/src/gui/app.rs index 76dff71..826f051 100644 --- a/src/gui/app.rs +++ b/src/gui/app.rs @@ -36,7 +36,7 @@ impl App { let events = db::events::list_non_recurring_between(&conn, start_date, end_date)?; let recurring_events = db::events::list_recurring(&conn)?; let categories = db::categories::list(&conn)?; - let default_color = db::event_color::get_default_color(&conn)?; + let default_color = db::event_colors::get_default_color(&conn)?; let calendar = calendar::create( tx.clone(), -- cgit v1.2.3