aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorFrederick Muriuki Muriithi2024-05-29 16:10:40 -0500
committerFrederick Muriuki Muriithi2024-06-03 10:02:07 -0500
commitd0150a1cee14edce64481920af539b89d5bd3cc8 (patch)
treef8107fb6d0dbdec75761e3cf98ee4e97b7af9abb
parentfa08d8592f11bcec54119e72262f82585c5a630b (diff)
downloadgn-auth-d0150a1cee14edce64481920af539b89d5bd3cc8.tar.gz
Update db schema to allow user verification.
-rw-r--r--migrations/auth/20240529_01_ALNWj-update-schema-for-user-verification.py64
1 files changed, 64 insertions, 0 deletions
diff --git a/migrations/auth/20240529_01_ALNWj-update-schema-for-user-verification.py b/migrations/auth/20240529_01_ALNWj-update-schema-for-user-verification.py
new file mode 100644
index 0000000..0cab1c3
--- /dev/null
+++ b/migrations/auth/20240529_01_ALNWj-update-schema-for-user-verification.py
@@ -0,0 +1,64 @@
+"""
+update schema for user-verification
+"""
+
+from yoyo import step
+
+__depends__ = {'20240506_01_798tW-create-jwt-refresh-tokens-table'}
+
+def add_verification_cols_to_users_table(conn):
+ "add verification columns to users table";
+ conn.execute("PRAGMA foreign_keys = OFF")
+
+ conn.execute(
+ """
+ CREATE TABLE users_new(
+ user_id TEXT PRIMARY KEY NOT NULL,
+ email TEXT UNIQUE NOT NULL,
+ name TEXT,
+ created INTEGER NOT NULL DEFAULT (unixepoch()),
+ verified INTEGER NOT NULL DEFAULT 0 CHECK (verified=0 or verified=1)
+ ) WITHOUT ROWID
+ """)
+ conn.execute(
+ """
+ INSERT INTO users_new(user_id, email, name)
+ SELECT user_id, email, name FROM users
+ """)
+ # the original table `users` has dependents, so we cannot simply do a
+ # `ALTER TABLE … RENAME TO …` since according to
+ # https://sqlite.org/lang_altertable.html#alter_table_rename
+ # from versions 3.26.0 onward, the foreign key references are **ALWAYS**
+ # changed. In this case, we create the new table first, do data transfers,
+ # drop the original and rename the new table to the same name as the
+ # original.
+ conn.execute("DROP TABLE IF EXISTS users")
+ conn.execute("ALTER TABLE users_new RENAME TO users")
+
+
+ print("turning foreign keys should back on.")
+ conn.execute("PRAGMA foreign_key_check")
+ conn.execute("PRAGMA foreign_keys = ON")
+
+def drop_verification_cols_from_users_table(conn):
+ "Drop verification columns from users table"
+ conn.execute("ALTER TABLE users DROP COLUMN created")
+ conn.execute("ALTER TABLE users DROP COLUMN verified")
+
+steps = [
+ step(add_verification_cols_to_users_table,
+ drop_verification_cols_from_users_table),
+ step(
+ """
+ CREATE TABLE IF NOT EXISTS user_verification_codes(
+ user_id TEXT NOT NULL,
+ code TEXT NOT NULL,
+ generated INTEGER NOT NULL,
+ expires INTEGER NOT NULL,
+ PRIMARY KEY(user_id),
+ FOREIGN KEY(user_id) REFERENCES users(user_id)
+ ON UPDATE CASCADE ON DELETE CASCADE
+ ) WITHOUT ROWID
+ """,
+ "DROP TABLE IF EXISTS verification_codes")
+]