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. --- README.md | 12 +++++++++++- .../auth/20221103_02_sGrIs-create-user-credentials-table.py | 1 + migrations/auth/20221110_01_WtZ1I-create-resources-table.py | 7 +++++-- .../auth/20221110_07_7WGa1-create-role-privileges-table.py | 4 +++- ...20221114_01_n8gsF-create-generic-role-privileges-table.py | 4 +++- .../auth/20221114_02_DKKjn-drop-generic-role-tables.py | 4 +++- .../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 ++++++-- .../auth/20221219_01_CI3tN-create-oauth2-clients-table.py | 1 + .../auth/20221219_02_buSEU-create-oauth2-tokens-table.py | 4 +++- .../20221219_03_PcTrb-create-authorisation-code-table.py | 4 +++- 12 files changed, 45 insertions(+), 12 deletions(-) diff --git a/README.md b/README.md index 79657e3..aeb2db8 100644 --- a/README.md +++ b/README.md @@ -214,7 +214,17 @@ curl -X GET -H "Authorization: Bearer L3Q5mvehQeSUNQQbFLfrcUEdEyoknyblXWxlpKkvdl ``` to get all the members of a group with the ID -`8f8d7640-5d51-4445-ad68-7ab217439804` where +`8f8d7640-5d51-4445-ad68-7ab217439804` + +or: + +```sh +curl -X POST "http://localhost:8080/api/oauth2/user/register" \ + -F "email=a_new@users.email" -F "password=apasswd" \ + -F "confirm_password=apasswd" +``` + +where `L3Q5mvehQeSUNQQbFLfrcUEdEyoknyblXWxlpKkvdl` is the token you got in the **Request Token** section above. 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