Migration to add creator and time tracking info to resources table.

Add a migration to extend the resources' table to track the user that
created the resource, and the time at which they created that
resource.

1 files changed, 183 insertions, 0 deletions

diff --git a/migrations/auth/20260402_01_Bf8nm-add-user-and-time-tracking-to-resources-table.py b/migrations/auth/20260402_01_Bf8nm-add-user-and-time-tracking-to-resources-table.py
new file mode 100644
index 0000000..fd1e617
--- /dev/null
+++ b/migrations/auth/20260402_01_Bf8nm-add-user-and-time-tracking-to-resources-table.py
@@ -0,0 +1,183 @@
+"""
+Add user and time tracking to resources table
+"""
+import random
+import contextlib
+from datetime import datetime
+
+from yoyo import step
+
+__depends__ = {'20260331_01_FV1sL-add-privileges-to-role-systemwide-data-curator'}
+
+GN_AUTH_INIT_TIMESTAMP = 1691130509.0
+__admin_id__ = ""
+
+
+def fetch_acentenos_id(conn):
+    """Fetch the default resource creator."""
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.execute("SELECT user_id FROM users WHERE email=?",
+                       (("acent" "eno@" "uthsc" "." "edu"),))
+        res = cursor.fetchone()
+        return res[0] if bool(res) else None
+
+
+def fetch_a_sysadmin_id(conn, resources_table):
+    """Fetch one ID out of all system administrator users."""
+    global __admin_id__
+
+    def __fetch__():
+        with contextlib.closing(conn.cursor()) as cursor:
+            cursor.execute(
+                f"SELECT ur.user_id FROM {resources_table} AS rsc "
+                "INNER JOIN user_roles AS ur ON rsc.resource_id=ur.resource_id "
+                "INNER JOIN roles AS r ON ur.role_id=r.role_id "
+                "WHERE resource_name='GeneNetwork System' "
+                "AND r.role_name='system-administrator'"
+            )
+            return tuple(row[0] for row in cursor.fetchall())
+
+    if not bool(__admin_id__):
+        __admin_id__ = random.choice(__fetch__())
+
+    return __admin_id__
+
+
+def add_user_and_time_tracking_columns(conn):
+    """Add user and time tracking columns."""
+    conn.execute(
+        """
+            CREATE TABLE resources_new(
+              resource_id TEXT NOT NULL,
+              resource_name TEXT NOT NULL UNIQUE,
+              resource_category_id TEXT NOT NULL,
+              public INTEGER NOT NULL DEFAULT 0 CHECK (public=0 or public=1),
+              created_by TEXT NOT NULL,
+              created_at REAL NOT NULL DEFAULT '1691130509.0',
+              PRIMARY KEY(resource_id),
+              FOREIGN KEY(resource_category_id)
+                REFERENCES resource_categories(resource_category_id)
+                ON UPDATE CASCADE ON DELETE RESTRICT,
+              FOREIGN KEY(created_by)
+                REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE RESTRICT
+            ) WITHOUT ROWID
+            """)
+
+
+def drop_user_and_time_tracking_columns(conn):
+    """Drop user and time tracking columns."""
+    conn.execute("PRAGMA foreign_keys = OFF")
+    conn.execute("DROP TABLE IF EXISTS resources")
+    conn.execute("ALTER TABLE resources_old RENAME TO resources")
+    conn.execute("PRAGMA foreign_key_check")
+    conn.execute("PRAGMA foreign_keys = ON")
+
+
+def update_data_for_new_resources_table(conn):
+    """Add creator and time to original data."""
+    __creator__ = (
+        fetch_acentenos_id(conn) or fetch_a_sysadmin_id(conn, "resources"))
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.execute("SELECT * FROM resources")
+        cursor.executemany(
+            "INSERT INTO resources_new("
+            "  resource_id,"
+            "  resource_name,"
+            "  resource_category_id,"
+            "  public,"
+            "  created_by,"
+            "  created_at"
+            ") VALUES (?, ?, ?, ?, ?, ?)",
+            tuple(
+                tuple(row) + (__creator__, GN_AUTH_INIT_TIMESTAMP)
+                for row in cursor.fetchall()))
+
+
+def restore_data_for_old_resources_table(conn):
+    """Remove creator and time from data."""
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.execute("SELECT * FROM resources")
+        cursor.executemany(
+            "INSERT INTO resources_old("
+            "  resource_id,"
+            "  resource_name,"
+            "  resource_category_id,"
+            "  public"
+            ") VALUES (?, ?, ?, ?)",
+            tuple(tuple(row)[0:4] for row in cursor.fetchall()))
+
+
+def replace_old_table_with_new_table(conn):
+    """Restore old resources table with the new resources table."""
+    conn.execute("PRAGMA foreign_keys = OFF")
+    conn.execute("DROP TABLE resources")
+    conn.execute("ALTER TABLE resources_new RENAME TO resources")
+    conn.execute("PRAGMA foreign_key_check")
+    conn.execute("PRAGMA foreign_keys = ON")
+
+
+def restore_old_table(conn):
+    """Restore old 'resources' table schema."""
+    conn.execute(
+        """
+            CREATE TABLE resources_old(
+              resource_id TEXT NOT NULL,
+              resource_name TEXT NOT NULL UNIQUE,
+              resource_category_id TEXT NOT NULL,
+              public INTEGER NOT NULL DEFAULT 0 CHECK (public=0 or public=1),
+              PRIMARY KEY(resource_id),
+              FOREIGN KEY(resource_category_id)
+                REFERENCES resource_categories(resource_category_id)
+                ON UPDATE CASCADE ON DELETE RESTRICT
+            ) WITHOUT ROWID
+            """)
+
+
+def parse_creator_and_time(cursor, row):
+    __return__ = None
+
+    __name_parts__ = row[1].split("—")
+    if len(__name_parts__) == 4:
+        __email__, __inbredsetname__, __datetimestr__, count = __name_parts__
+        cursor.execute("SELECT user_id FROM users WHERE email=?",
+                       (__email__.strip(),))
+        results = cursor.fetchone()
+        if bool(results):
+            __return__ = {
+                "resource_id": row[0],
+                "creator": results[0],
+                "created": datetime.fromisoformat(__datetimestr__).timestamp()
+            }
+
+    return __return__
+
+
+def update_creators_and_time(conn):
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.execute("SELECT resource_id, resource_name FROM resources")
+        cursor.executemany(
+            "UPDATE resources SET created_by=:creator, created_at=:created "
+            "WHERE resource_id=:resource_id",
+            tuple(item for item in
+                  (parse_creator_and_time(cursor, row)
+                   for row in cursor.fetchall())
+                  if item is not None))
+        
+
+
+def restore_default_creators_and_time(conn):
+    with contextlib.closing(conn.cursor()) as cursor:
+        __creator__ = (
+            fetch_acentenos_id(conn) or fetch_a_sysadmin_id(conn, "resources"))
+        cursor.execute("UPDATE resources SET created_by=?, created_at=?",
+                       (__creator__, GN_AUTH_INIT_TIMESTAMP))
+
+
+steps = [
+    step(add_user_and_time_tracking_columns,
+         drop_user_and_time_tracking_columns),
+    step(update_data_for_new_resources_table,
+         restore_data_for_old_resources_table),
+    step(replace_old_table_with_new_table, restore_old_table),
+    step(update_creators_and_time, restore_default_creators_and_time)
+]