From 3dee24dc1e05893b49ace052ab61899e49a03bb7 Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Mon, 13 Mar 2023 06:25:13 +0300
Subject: Add 'ON UPDATE/DELETE' clauses to foreign keys.

---
 .../auth/20221103_02_sGrIs-create-user-credentials-table.py       | 1 +
 migrations/auth/20221110_01_WtZ1I-create-resources-table.py       | 7 +++++--
 migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py | 4 +++-
 .../20221114_01_n8gsF-create-generic-role-privileges-table.py     | 4 +++-
 migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py     | 4 +++-
 migrations/auth/20221114_03_PtWjc-create-group-roles-table.py     | 4 +++-
 migrations/auth/20221114_05_hQun6-create-user-roles-table.py      | 4 +++-
 .../20221117_01_RDlfx-modify-group-roles-add-group-role-id.py     | 8 ++++++--
 migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py  | 1 +
 migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py   | 4 +++-
 .../auth/20221219_03_PcTrb-create-authorisation-code-table.py     | 4 +++-
 11 files changed, 34 insertions(+), 11 deletions(-)

(limited to 'migrations')

diff --git a/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py b/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py
index 3f72f3e..48bd663 100644
--- a/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py
+++ b/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py
@@ -13,6 +13,7 @@ steps = [
             user_id TEXT PRIMARY KEY,
             password TEXT NOT NULL,
             FOREIGN KEY(user_id) REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS user_credentials")
diff --git a/migrations/auth/20221110_01_WtZ1I-create-resources-table.py b/migrations/auth/20221110_01_WtZ1I-create-resources-table.py
index 09ed891..0b769b8 100644
--- a/migrations/auth/20221110_01_WtZ1I-create-resources-table.py
+++ b/migrations/auth/20221110_01_WtZ1I-create-resources-table.py
@@ -15,8 +15,11 @@ steps = [
             resource_name TEXT NOT NULL,
             resource_category_id TEXT NOT NULL,
             PRIMARY KEY(group_id, resource_id),
-            FOREIGN KEY(group_id) REFERENCES groups(group_id),
-            FOREIGN KEY(resource_category_id) REFERENCES resource_categories(resource_category_id)
+            FOREIGN KEY(group_id) REFERENCES groups(group_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
+            FOREIGN KEY(resource_category_id)
+              REFERENCES resource_categories(resource_category_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS resources")
diff --git a/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py b/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py
index 3174231..0d0eeb9 100644
--- a/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py
+++ b/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py
@@ -13,8 +13,10 @@ steps = [
             role_id TEXT NOT NULL,
             privilege_id TEXT NOT NULL,
             PRIMARY KEY(role_id, privilege_id),
-            FOREIGN KEY(role_id) REFERENCES roles(role_id),
+            FOREIGN KEY(role_id) REFERENCES roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY(privilege_id) REFERENCES privileges(privilege_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS role_privileges"),
diff --git a/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py b/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py
index 7102a57..2048f4a 100644
--- a/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py
+++ b/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py
@@ -15,8 +15,10 @@ steps = [
             generic_role_id TEXT NOT NULL,
             privilege_id TEXT NOT NULL,
             PRIMARY KEY(generic_role_id, privilege_id),
-            FOREIGN KEY(generic_role_id) REFERENCES generic_roles(role_id),
+            FOREIGN KEY(generic_role_id) REFERENCES generic_roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY(privilege_id) REFERENCES privileges(privilege_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS generic_role_privileges"),
diff --git a/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py b/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py
index e767aeb..6bd101b 100644
--- a/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py
+++ b/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py
@@ -24,8 +24,10 @@ steps = [
             generic_role_id TEXT NOT NULL,
             privilege_id TEXT NOT NULL,
             PRIMARY KEY(generic_role_id, privilege_id),
-            FOREIGN KEY(generic_role_id) REFERENCES generic_roles(role_id),
+            FOREIGN KEY(generic_role_id) REFERENCES generic_roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY(privilege_id) REFERENCES privileges(privilege_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """),
     step(
diff --git a/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py b/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py
index f314000..a7e7b45 100644
--- a/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py
+++ b/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py
@@ -13,8 +13,10 @@ steps = [
             group_id TEXT NOT NULL,
             role_id TEXT NOT NULL,
             PRIMARY KEY(group_id, role_id),
-            FOREIGN KEY(group_id) REFERENCES groups(group_id),
+            FOREIGN KEY(group_id) REFERENCES groups(group_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY(role_id) REFERENCES roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS group_roles"),
diff --git a/migrations/auth/20221114_05_hQun6-create-user-roles-table.py b/migrations/auth/20221114_05_hQun6-create-user-roles-table.py
index d9532dc..e0de751 100644
--- a/migrations/auth/20221114_05_hQun6-create-user-roles-table.py
+++ b/migrations/auth/20221114_05_hQun6-create-user-roles-table.py
@@ -13,8 +13,10 @@ steps = [
             user_id TEXT NOT NULL,
             role_id TEXT NOT NULL,
             PRIMARY KEY(user_id, role_id),
-            FOREIGN KEY(user_id) REFERENCES users(user_id),
+            FOREIGN KEY(user_id) REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY(role_id) REFERENCES roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS user_roles"),
diff --git a/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py b/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py
index 2deff10..a4d7806 100644
--- a/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py
+++ b/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py
@@ -23,8 +23,10 @@ steps = [
             group_id TEXT NOT NULL,
             role_id TEXT NOT NULL,
             PRIMARY KEY(group_id, role_id),
-            FOREIGN KEY(group_id) REFERENCES groups(group_id),
+            FOREIGN KEY(group_id) REFERENCES groups(group_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY(role_id) REFERENCES roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """),
     step(
@@ -34,8 +36,10 @@ steps = [
             group_id TEXT NOT NULL,
             role_id TEXT NOT NULL,
             UNIQUE (group_id, role_id),
-            FOREIGN KEY(group_id) REFERENCES groups(group_id),
+            FOREIGN KEY(group_id) REFERENCES groups(group_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY(role_id) REFERENCES roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS group_roles"),
diff --git a/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py b/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py
index 8ab7be4..475be01 100644
--- a/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py
+++ b/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py
@@ -18,6 +18,7 @@ steps = [
             user_id TEXT NOT NULL,
             PRIMARY KEY(client_id),
             FOREIGN KEY(user_id) REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS oauth2_clients")
diff --git a/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py b/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py
index 81ca7df..778282b 100644
--- a/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py
+++ b/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py
@@ -21,8 +21,10 @@ steps = [
             expires_in INTEGER NOT NULL,
             user_id TEXT NOT NULL,
             PRIMARY KEY(token_id),
-            FOREIGN KEY (client_id) REFERENCES oauth2_clients(client_id),
+            FOREIGN KEY (client_id) REFERENCES oauth2_clients(client_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY (user_id) REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS oauth2_tokens")
diff --git a/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py b/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py
index 495916f..1683f87 100644
--- a/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py
+++ b/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py
@@ -21,8 +21,10 @@ steps = [
             code_challenge_method TEXT,
             user_id TEXT NOT NULL,
             PRIMARY KEY (code_id),
-            FOREIGN KEY (client_id) REFERENCES oauth2_clients(client_id),
+            FOREIGN KEY (client_id) REFERENCES oauth2_clients(client_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY (user_id) REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS authorisation_code")
-- 
cgit v1.2.3