Fix foreign-key refs. Merge tables.

* Link the `role_id` field to the `roles` table rather than the
  `group_roles` table.
* Merge the data in the `group_user_roles_on_resources` table in the
  `user_roles` table to have a single point-of-truth for all user
  roles on resources.

1 files changed, 227 insertions, 0 deletions

diff --git a/migrations/auth/20230912_02_hFmSn-drop-group-id-and-fix-foreign-key-references-on-group-user-roles-on-resources-table.py b/migrations/auth/20230912_02_hFmSn-drop-group-id-and-fix-foreign-key-references-on-group-user-roles-on-resources-table.py
new file mode 100644
index 0000000..1b3f0b1
--- /dev/null
+++ b/migrations/auth/20230912_02_hFmSn-drop-group-id-and-fix-foreign-key-references-on-group-user-roles-on-resources-table.py
@@ -0,0 +1,227 @@
+"""
+Drop 'group_id' and fix foreign key references on 'group_user_roles_on_resources' table
+"""
+
+import sqlite3
+from yoyo import step
+
+__depends__ = {'20230912_01_BxrhE-add-system-resource'}
+
+def drop_group_id(conn):
+    """Drop `group_id` from `group_user_roles_on_resources` table."""
+    conn.execute("PRAGMA foreign_keys = OFF")
+
+    conn.execute(
+        """
+        ALTER TABLE group_user_roles_on_resources
+        RENAME TO group_user_roles_on_resources_bkp
+        """)
+    conn.execute(
+        """
+        CREATE TABLE IF NOT EXISTS group_user_roles_on_resources (
+          user_id TEXT NOT NULL,
+          role_id TEXT NOT NULL,
+          resource_id TEXT NOT NULL,
+          PRIMARY KEY (user_id, role_id, resource_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,
+          FOREIGN KEY (resource_id)
+            REFERENCES resources(resource_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """)
+    conn.execute(
+        "INSERT INTO group_user_roles_on_resources "
+        "SELECT user_id, role_id, resource_id "
+        "FROM group_user_roles_on_resources_bkp")
+    conn.execute("DROP TABLE IF EXISTS group_user_roles_on_resources_bkp")
+
+    conn.execute("PRAGMA foreign_key_check")
+    conn.execute("PRAGMA foreign_keys = ON")
+
+def restore_group_id(conn):
+    """Restore `group_id` to `group_user_roles_on_resources` table."""
+    conn.execute("PRAGMA foreign_keys = OFF")
+
+    conn.execute(
+        """
+        ALTER TABLE group_user_roles_on_resources
+        RENAME TO group_user_roles_on_resources_bkp
+        """)
+    conn.execute(
+        """
+        CREATE TABLE IF NOT EXISTS group_user_roles_on_resources (
+          group_id TEXT NOT NULL,
+          user_id TEXT NOT NULL,
+          role_id TEXT NOT NULL,
+          resource_id TEXT NOT NULL,
+          PRIMARY KEY (group_id, user_id, role_id, resource_id),
+          FOREIGN KEY (user_id)
+            REFERENCES users(user_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT,
+          FOREIGN KEY (group_id, role_id)
+            REFERENCES group_roles(group_id, role_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT,
+          FOREIGN KEY (resource_id)
+            REFERENCES resources(resource_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """)
+
+    cursor = conn.cursor()
+    cursor.execute(
+        """
+        INSERT INTO group_user_roles_on_resources
+          SELECT
+            ro.group_id, gurorb.user_id, gurorb.role_id, gurorb.resource_id
+          FROM resource_ownership AS ro
+          INNER JOIN group_user_roles_on_resources_bkp AS gurorb
+          ON ro.resource_id=gurorb.resource_id
+        """)
+
+    conn.execute("DROP TABLE IF EXISTS group_user_roles_on_resources_bkp")
+
+    conn.execute("PRAGMA foreign_key_check")
+    conn.execute("PRAGMA foreign_keys = ON")
+
+def link_sys_admin_user_roles(conn):
+    """Link system-admins to the system resource."""
+    conn.row_factory = sqlite3.Row
+    cursor = conn.cursor()
+    cursor.execute(
+        "SELECT ur.* FROM user_roles AS ur "
+        "INNER JOIN roles AS r ON ur.role_id=r.role_id "
+        "WHERE r.role_name='system-administrator'")
+    admins = cursor.fetchall()
+    cursor.execute(
+        "SELECT r.resource_id FROM resources AS r "
+        "INNER JOIN resource_categories AS rc "
+        "ON r.resource_category_id=rc.resource_category_id "
+        "WHERE rc.resource_category_key='system'")
+    system_resource_id = cursor.fetchone()["resource_id"]
+    cursor.executemany(
+        "INSERT INTO "
+        "group_user_roles_on_resources(user_id, role_id, resource_id) "
+        "VALUES (:user_id, :role_id, :resource_id)",
+        tuple({**admin, "resource_id": system_resource_id} for admin in admins))
+
+def restore_sys_admin_user_roles(conn):
+    """Restore fields into older `user_roles` table."""
+    conn.row_factory = sqlite3.Row
+    cursor = conn.cursor()
+    cursor.execute(
+        "SELECT guror.user_id, guror.role_id "
+        "FROM group_user_roles_on_resources AS guror "
+        "INNER JOIN resources AS r "
+        "ON guror.resource_id=r.resource_id "
+        "INNER JOIN resource_categories AS rc "
+        "ON r.resource_category_id=rc.resource_category_id "
+        "WHERE rc.resource_category_key='system'")
+    user_roles = tuple(cursor.fetchall())
+    cursor.executemany(
+        "INSERT INTO user_roles(user_id, role_id) "
+        "VALUES (:user_id, :role_id)",
+        user_roles)
+
+def link_group_leader_user_roles(conn):
+    """Link group leaders to their resources."""
+    conn.execute(
+        """
+        INSERT INTO group_user_roles_on_resources(user_id, role_id, resource_id)
+         SELECT gu.user_id, r.role_id, gr.resource_id
+         FROM group_resources AS gr INNER JOIN group_users AS gu
+         ON gr.group_id=gu.group_id INNER JOIN user_roles AS ur
+         ON gu.user_id=ur.user_id INNER JOIN roles AS r
+         ON ur.role_id=r.role_id
+         WHERE r.role_name='group-leader'
+        """)
+
+def restore_group_leader_user_roles(conn):
+    """Restore group admins to older `user_roles` table."""
+    conn.execute(
+        """
+        INSERT INTO user_roles(user_id, role_id)
+         SELECT guror.user_id, guror.role_id
+         FROM group_user_roles_on_resources AS guror
+         INNER JOIN resources AS r ON guror.resource_id=r.resource_id
+         INNER JOIN resource_categories AS rc
+         ON r.resource_category_id=rc.resource_category_id
+         WHERE rc.resource_category_key='group'
+        """)
+
+def link_group_creator_user_roles(conn):
+    """Link group-creators to system."""
+    conn.row_factory = sqlite3.Row
+    cursor = conn.cursor()
+    cursor.execute(
+        "SELECT ur.* FROM user_roles AS ur "
+        "INNER JOIN roles AS r ON ur.role_id=r.role_id "
+        "WHERE r.role_name='group_creator'")
+    creators = cursor.fetchall()
+    cursor.execute(
+        "SELECT r.resource_id FROM resources AS r "
+        "INNER JOIN resource_categories AS rc "
+        "ON r.resource_category_id=rc.resource_category_id "
+        "WHERE rc.resource_category_key='system'")
+    sys_res_id = cursor.fetchone()["resource_id"]
+    cursor.executemany(
+        "INSERT INTO "
+        "group_user_roles_on_resources(user_id, role_id, resource_id) "
+        "VALUES (:user_id, :role_id, :resource_id)",
+        tuple({**creator, "resource_id": sys_res_id} for creator in creators))
+
+def restore_group_creator_user_roles(conn):
+    "Restore group-creator user roles."
+    conn.execute(
+        """
+        INSERT INTO user_roles
+         SELECT guror.user_id, guror.role_id
+         FROM group_user_roles_on_resources AS guror
+         INNER JOIN roles AS r ON guror.role_id=r.role_id
+         WHERE r.role_name='group-creator'""")
+
+def rename_table(conn):
+    "rename `group_user_roles_on_resources`, drop `user_roles`."
+    conn.execute("PRAGMA foreign_keys = OFF")
+
+    conn.execute("DROP TABLE IF EXISTS user_roles")
+    conn.execute(
+        "ALTER TABLE group_user_roles_on_resources RENAME TO user_roles")
+
+    conn.execute("PRAGMA foreign_key_check")
+    conn.execute("PRAGMA foreign_keys = ON")
+
+def restore_tables(conn):
+    "rename to `group_user_roles_on_resources`, recreate original `user_roles`."
+    conn.execute("PRAGMA foreign_keys = OFF")
+
+    conn.execute(
+        "ALTER TABLE user_roles RENAME TO group_user_roles_on_resources")
+    conn.execute(
+        """
+        CREATE TABLE user_roles(
+            user_id TEXT NOT NULL,
+            role_id TEXT NOT NULL,
+            PRIMARY KEY(user_id, role_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
+        """)
+
+    conn.execute("PRAGMA foreign_key_check")
+    conn.execute("PRAGMA foreign_keys = ON")
+
+steps = [
+    step(drop_group_id, restore_group_id),
+    step(link_sys_admin_user_roles, restore_sys_admin_user_roles),
+    step(link_group_leader_user_roles, restore_group_leader_user_roles),
+    step(link_group_creator_user_roles, restore_group_creator_user_roles),
+    step(rename_table, restore_tables)
+]
+