aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorFrederick Muriuki Muriithi2024-06-06 15:36:11 -0500
committerFrederick Muriuki Muriithi2024-06-06 15:38:44 -0500
commitf691603a8e7a1700783b2be6f855f30d30f645f1 (patch)
tree7fca0121271f2a7a1e6fc9723371f455b6f53f65
parent5d34332f356164ce539044f538ed74b983fcc706 (diff)
downloadgn-auth-f691603a8e7a1700783b2be6f855f30d30f645f1.tar.gz
migration: Create `resource_roles` db table
-rw-r--r--migrations/auth/20240606_02_ubZri-create-resource-roles-table.py36
1 files changed, 36 insertions, 0 deletions
diff --git a/migrations/auth/20240606_02_ubZri-create-resource-roles-table.py b/migrations/auth/20240606_02_ubZri-create-resource-roles-table.py
new file mode 100644
index 0000000..0695c0e
--- /dev/null
+++ b/migrations/auth/20240606_02_ubZri-create-resource-roles-table.py
@@ -0,0 +1,36 @@
+"""
+Create 'resource_roles' table.
+"""
+
+from yoyo import step
+
+__depends__ = {'20240606_01_xQDwL-move-role-manipulation-privileges-from-group-to-resources'}
+
+steps = [
+ step(
+ """
+ CREATE TABLE IF NOT EXISTS resource_roles(
+ resource_id TEXT NOT NULL,
+ role_created_by TEXT NOT NULL,
+ role_id TEXT NOT NULL,
+ PRIMARY KEY (resource_id, role_created_by, role_id),
+ FOREIGN KEY(resource_id) REFERENCES resources(resource_id)
+ ON UPDATE CASCADE ON DELETE RESTRICT,
+ FOREIGN KEY(role_created_by) 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 resource_roles"),
+ step(
+ """
+ CREATE INDEX IF NOT EXISTS
+ tbl_resource_roles_cols_resource_id_role_created_by
+ ON resource_roles(resource_id, role_created_by)
+ """,
+ """
+ DROP INDEX IF EXISTS
+ tbl_resource_roles_cols_resource_id_role_created_by
+ """)
+]