Draft: Add created column

Closes #152

migrations/20240327_add_created_and_origin.py

+import re
+import csv
+import dataset
+from datetime import datetime
+import rabbit_config as rcfg
+
+
+def fromisoformat(date_string: str):
+    regex = re.compile(
+        r"(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})\.(\d{1,6})"
+    )
+    mo = re.search(regex, date_string)
+    return datetime(*[int(m) for m in mo.groups()])
+
+
+def upgrade():
+    db_path = rcfg.db_path
+    db_name = "user_reg.db"
+    csv_filename = "./migrations/account-registration-history.csv"
+
+    db = dataset.connect(f"sqlite:///{db_path}/{db_name}")
+    table = db["users"]
+
+    # Get all username and last_update
+    usernames = db.query("SELECT username, last_update FROM users")
+    users = {}
+    for u in usernames:
+        users[u["username"]] = dict(u)
+
+    # Get created from csv file
+    with open(csv_filename) as f:
+        reader = csv.reader(f, delimiter=",")
+        # Skip header
+        next(reader)
+        for row in reader:
+            if row[0] in users:
+                users[row[0]]["created"] = fromisoformat(row[1][:-3])
+                users[row[0]]["created_source"] = row[2]
+
+    # Add copy created column from last_update if they are not in csv file
+    for username in users:
+        if "created" not in users[username]:
+            users[username]["created"] = fromisoformat(
+                users[username]["last_update"]
+            )
+            users[username]["created_source"] = "last_update"
+        # No need to update this column back to the db
+        del users[username]["last_update"]
+
+    # Create new columns
+    table.create_column("created", db.types.datetime)
+    table.create_column("created_source", db.types.text)
+
+    # Update database in chunk
+    table.update_many(list(users.values()), ["username"], ensure=True)
+
+
+if __name__ == "__main__":
+    upgrade()