diff options
| -rw-r--r-- | migrations/auth/20240606_03_BY7Us-drop-group-roles-table.py | 47 |
1 files changed, 47 insertions, 0 deletions
diff --git a/migrations/auth/20240606_03_BY7Us-drop-group-roles-table.py b/migrations/auth/20240606_03_BY7Us-drop-group-roles-table.py new file mode 100644 index 0000000..5add4d2 --- /dev/null +++ b/migrations/auth/20240606_03_BY7Us-drop-group-roles-table.py @@ -0,0 +1,47 @@ +""" +Drop 'group_roles' table. +""" +import sqlite3 +from yoyo import step + +__depends__ = {'20240606_02_ubZri-create-resource-roles-table'} + +def drop_group_roles(conn): + """Delete `group_roles` and related data.""" + conn.row_factory = sqlite3.Row + cursor = conn.cursor() + cursor.execute("DELETE FROM user_roles WHERE role_id IN " + "(SELECT role_id FROM group_roles)") + cursor.execute("DELETE FROM roles WHERE role_id IN" + "(SELECT role_id FROM group_roles)") + cursor.execute("DROP TABLE IF EXISTS group_roles") + cursor.close() + +def restore_group_roles(conn): + """Restore the `group_roles` table.""" + + conn.row_factory = sqlite3.Row + cursor = conn.cursor() + cursor.execute( + """ + CREATE TABLE group_roles( + group_role_id TEXT PRIMARY KEY, + group_id TEXT NOT NULL, + role_id TEXT NOT NULL, + UNIQUE (group_id, role_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 + """) + cursor.execute( + """ + CREATE INDEX idx_tbl_group_roles_cols_group_id + ON group_roles(group_id) + """) + cursor.close() + +steps = [ + step(drop_group_roles, restore_group_roles) +] |