From b324803fe11b52b28ac2dc459504f904a48a79d4 Mon Sep 17 00:00:00 2001 From: Joris Date: Fri, 7 Feb 2025 11:45:22 +0100 Subject: Use strict tables --- src/db/migrations/01-init.sql | 10 ++++++++++ .../02-primary-key-question-responses.sql | 20 ++++++++++++++++++++ src/db/migrations/03-drop-deck-read.sql | 1 + src/db/migrations/04-strict-tables.sql | 18 ++++++++++++++++++ src/db/mod.rs | 22 +++++++++++++++++----- src/db/sql/1-init.sql | 10 ---------- src/db/sql/2-primary-key-question-responses.sql | 20 -------------------- src/db/sql/3-drop-deck-read.sql | 1 - 8 files changed, 66 insertions(+), 36 deletions(-) create mode 100644 src/db/migrations/01-init.sql create mode 100644 src/db/migrations/02-primary-key-question-responses.sql create mode 100644 src/db/migrations/03-drop-deck-read.sql create mode 100644 src/db/migrations/04-strict-tables.sql delete mode 100644 src/db/sql/1-init.sql delete mode 100644 src/db/sql/2-primary-key-question-responses.sql delete mode 100644 src/db/sql/3-drop-deck-read.sql (limited to 'src') diff --git a/src/db/migrations/01-init.sql b/src/db/migrations/01-init.sql new file mode 100644 index 0000000..29d70ed --- /dev/null +++ b/src/db/migrations/01-init.sql @@ -0,0 +1,10 @@ +CREATE TABLE IF NOT EXISTS cards ( + question VARCHAR PRIMARY KEY, + responses VARCHAR NOT NULL, + state VARCHAR NOT NULL, + created TIMESTAMP NOT NULL, + updated TIMESTAMP NULL, + deleted TIMESTAMP NULL, + deck_read TIMESTAMP NOT NULL, + ready TIMESTAMP NOT NULL +) diff --git a/src/db/migrations/02-primary-key-question-responses.sql b/src/db/migrations/02-primary-key-question-responses.sql new file mode 100644 index 0000000..cb7df21 --- /dev/null +++ b/src/db/migrations/02-primary-key-question-responses.sql @@ -0,0 +1,20 @@ +/* Allows to use ON CONFLICT on (question, responses) when inserting a card. */ + +CREATE TABLE IF NOT EXISTS cards_copy ( + question VARCHAR NOT NULL, + responses VARCHAR NOT NULL, + state VARCHAR NOT NULL, + created TIMESTAMP NOT NULL, + updated TIMESTAMP NULL, + deleted TIMESTAMP NULL, + deck_read TIMESTAMP NOT NULL, + ready TIMESTAMP NOT NULL, + PRIMARY KEY (question, responses) +); + +INSERT INTO cards_copy (question, responses, state, created, updated, deleted, deck_read, ready) + SELECT question, responses, state, created, updated, deleted, deck_read, ready FROM cards; + +DROP TABLE cards; + +ALTER TABLE cards_copy RENAME TO cards; diff --git a/src/db/migrations/03-drop-deck-read.sql b/src/db/migrations/03-drop-deck-read.sql new file mode 100644 index 0000000..1ca23d1 --- /dev/null +++ b/src/db/migrations/03-drop-deck-read.sql @@ -0,0 +1 @@ +ALTER TABLE cards DROP COLUMN deck_read; diff --git a/src/db/migrations/04-strict-tables.sql b/src/db/migrations/04-strict-tables.sql new file mode 100644 index 0000000..9ce4ce9 --- /dev/null +++ b/src/db/migrations/04-strict-tables.sql @@ -0,0 +1,18 @@ +ALTER TABLE "cards" RENAME TO "cards_non_strict"; + +CREATE TABLE IF NOT EXISTS "cards" ( + question TEXT NOT NULL, + responses TEXT NOT NULL, + state TEXT NOT NULL, + created INTEGER NOT NULL, /* TIMESTAMP */ + updated INTEGER NULL, /* TIMESTAMP */ + deleted INTEGER NULL, /* TIMESTAMP */ + ready INTEGER NOT NULL, /* TIMESTAMP */ + PRIMARY KEY (question, responses) +) STRICT; + +INSERT INTO cards (question, responses, state, created, updated, deleted, ready) + SELECT question, responses, state, created, updated, deleted, ready + FROM cards_non_strict; + +DROP TABLE cards_non_strict; diff --git a/src/db/mod.rs b/src/db/mod.rs index 54bf90f..fb9f823 100644 --- a/src/db/mod.rs +++ b/src/db/mod.rs @@ -6,11 +6,23 @@ pub mod cards; pub fn init(database: String) -> Result { let mut conn = Connection::open(database)?; + 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!("sql/1-init.sql")), - M::up(include_str!("sql/2-primary-key-question-responses.sql")), - M::up(include_str!("sql/3-drop-deck-read.sql")), + M::up(include_str!("migrations/01-init.sql")), + M::up(include_str!("migrations/02-primary-key-question-responses.sql")), + M::up(include_str!("migrations/03-drop-deck-read.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/db/sql/1-init.sql b/src/db/sql/1-init.sql deleted file mode 100644 index 29d70ed..0000000 --- a/src/db/sql/1-init.sql +++ /dev/null @@ -1,10 +0,0 @@ -CREATE TABLE IF NOT EXISTS cards ( - question VARCHAR PRIMARY KEY, - responses VARCHAR NOT NULL, - state VARCHAR NOT NULL, - created TIMESTAMP NOT NULL, - updated TIMESTAMP NULL, - deleted TIMESTAMP NULL, - deck_read TIMESTAMP NOT NULL, - ready TIMESTAMP NOT NULL -) diff --git a/src/db/sql/2-primary-key-question-responses.sql b/src/db/sql/2-primary-key-question-responses.sql deleted file mode 100644 index cb7df21..0000000 --- a/src/db/sql/2-primary-key-question-responses.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* Allows to use ON CONFLICT on (question, responses) when inserting a card. */ - -CREATE TABLE IF NOT EXISTS cards_copy ( - question VARCHAR NOT NULL, - responses VARCHAR NOT NULL, - state VARCHAR NOT NULL, - created TIMESTAMP NOT NULL, - updated TIMESTAMP NULL, - deleted TIMESTAMP NULL, - deck_read TIMESTAMP NOT NULL, - ready TIMESTAMP NOT NULL, - PRIMARY KEY (question, responses) -); - -INSERT INTO cards_copy (question, responses, state, created, updated, deleted, deck_read, ready) - SELECT question, responses, state, created, updated, deleted, deck_read, ready FROM cards; - -DROP TABLE cards; - -ALTER TABLE cards_copy RENAME TO cards; diff --git a/src/db/sql/3-drop-deck-read.sql b/src/db/sql/3-drop-deck-read.sql deleted file mode 100644 index 1ca23d1..0000000 --- a/src/db/sql/3-drop-deck-read.sql +++ /dev/null @@ -1 +0,0 @@ -ALTER TABLE cards DROP COLUMN deck_read; -- cgit v1.2.3