canonical-ubuntu-qa team mailing list archive

[Tim Andersson <mp+472678@xxxxxxxxxxxxxxxxxx>]

Tim Andersson has proposed merging ~andersson123/autopkgtest-cloud:charm-fixes into autopkgtest-cloud:master.

Requested reviews:
  Skia (hyask)
  Canonical's Ubuntu QA (canonical-ubuntu-qa)

For more details, see:
https://code.launchpad.net/~andersson123/autopkgtest-cloud/+git/autopkgtest-cloud/+merge/472678

web charm bugfixes to ease the re-deployability of the juju units
-- 
Your team Canonical's Ubuntu QA is requested to review the proposed merge of ~andersson123/autopkgtest-cloud:charm-fixes into autopkgtest-cloud:master.

diff --git a/charms/focal/autopkgtest-web/reactive/autopkgtest_web.py b/charms/focal/autopkgtest-web/reactive/autopkgtest_web.py
index fe3cae7..8c43edb 100644
--- a/charms/focal/autopkgtest-web/reactive/autopkgtest_web.py
+++ b/charms/focal/autopkgtest-web/reactive/autopkgtest_web.py
@@ -40,9 +40,11 @@ SWIFT_WEB_CREDENTIALS_PATH = os.path.expanduser(
 )
 API_KEYS_PATH = "/home/ubuntu/external-web-requests-api-keys.json"
 CONFIG_DIR = pathlib.Path("/home/ubuntu/.config/autopkgtest-web/")
+if not CONFIG_DIR.exists():
+    set_flag("autopkgtest-web.config-needs-writing")
 for parent in reversed(CONFIG_DIR.parents):
-    parent.mkdir(mode=0o770, exist_ok=True)
-CONFIG_DIR.mkdir(mode=0o770, exist_ok=True)
+    parent.mkdir(mode=0o775, exist_ok=True)
+CONFIG_DIR.mkdir(mode=0o775, exist_ok=True)
 ALLOWED_REQUESTOR_TEAMS_PATH = CONFIG_DIR / "allowed-requestor-teams"
 
 PUBLIC_SWIFT_CREDS_PATH = os.path.expanduser("~ubuntu/public-swift-creds")
@@ -187,6 +189,7 @@ def clone_autopkgtest_cloud():
 def set_up_systemd_units():
     status.maintenance("Setting up systemd units")
     any_changed = False
+    new_units = False
     for unit in glob.glob(
         os.path.join(
             AUTOPKGTEST_CLOUD_GIT_LOCATION,
@@ -203,6 +206,7 @@ def set_up_systemd_units():
                 unit,
                 os.path.join(os.path.sep, "etc", "systemd", "system", base),
             )
+            new_units = True
         except FileExistsError:
             pass
         p = subprocess.run(
@@ -219,7 +223,7 @@ def set_up_systemd_units():
             subprocess.check_call(["systemctl", "enable", base])
 
     status.active("systemd units installed")
-    if any_changed:
+    if any_changed or new_units:
         set_flag("autopkgtest-web.autopkgtest-web-target-needs-restart")
 
 
@@ -340,9 +344,10 @@ def set_up_web_config(apache):
     apache.send_enabled()
 
 
-@when_all(
+@when_any(
     "config.changed.allowed-requestor-teams",
     "config.set.allowed-requestor-teams",
+    "autopkgtest-web.config-needs-writing",
 )
 def write_allowed_teams():
     allowed_requestor_teams = config().get("allowed-requestor-teams")
@@ -350,7 +355,10 @@ def write_allowed_teams():
     allowed_teams_path.write_text(allowed_requestor_teams, encoding="utf-8")
 
 
-@when_all("config.changed.github-secrets", "config.set.github-secrets")
+@when_any(
+    "config.changed.github-secrets",
+    "autopkgtest-web.config-needs-writing",
+)
 def write_github_secrets():
     status.maintenance("Writing github secrets")
     github_secrets = config().get("github-secrets")
@@ -368,9 +376,8 @@ def write_github_secrets():
     status.maintenance("Done writing github secrets")
 
 
-@when_all(
+@when_any(
     "config.changed.external-web-requests-api-keys",
-    "config.set.external-web-requests-api-keys",
 )
 def write_api_keys():
     status.maintenance("Writing api keys")
@@ -585,9 +592,12 @@ def symlink_public_db():
                 ),
             )
             set_flag(symlink_flag)
-            status.maintenance(f"Done creating symlink for {symlink_file}")
+            status.active(f"Done creating symlink for {symlink_file}")
         except FileExistsError:
-            pass
+            clear_flag(symlink_flag)
+            status.active(
+                "symlinking public db and sha256 checksum already done"
+            )
 
 
 @when("leadership.is_leader")
diff --git a/charms/focal/autopkgtest-web/units/download-all-results.service b/charms/focal/autopkgtest-web/units/download-all-results.service
deleted file mode 100644
index 464d2db..0000000
--- a/charms/focal/autopkgtest-web/units/download-all-results.service
+++ /dev/null
@@ -1,10 +0,0 @@
-[Unit]
-Description=Download all results
-
-[Service]
-User=ubuntu
-Type=oneshot
-ExecStart=/home/ubuntu/webcontrol/download-all-results
-
-[Install]
-WantedBy=autopkgtest-web.target
diff --git a/charms/focal/autopkgtest-web/units/sqlite-writer.service b/charms/focal/autopkgtest-web/units/sqlite-writer.service
index 3a47c08..cf3b48e 100644
--- a/charms/focal/autopkgtest-web/units/sqlite-writer.service
+++ b/charms/focal/autopkgtest-web/units/sqlite-writer.service
@@ -5,6 +5,7 @@ StartLimitBurst=60
 
 [Service]
 User=ubuntu
+EnvironmentFile=/home/ubuntu/public-swift-creds
 ExecStart=/home/ubuntu/webcontrol/sqlite-writer
 Restart=on-failure
 RestartSec=1s
diff --git a/charms/focal/autopkgtest-web/webcontrol/cache-amqp b/charms/focal/autopkgtest-web/webcontrol/cache-amqp
index 124d1b4..e953c9d 100755
--- a/charms/focal/autopkgtest-web/webcontrol/cache-amqp
+++ b/charms/focal/autopkgtest-web/webcontrol/cache-amqp
@@ -12,7 +12,7 @@ import urllib.parse
 
 import amqplib.client_0_8 as amqp
 from amqplib.client_0_8.exceptions import AMQPChannelException
-from helpers.utils import get_autopkgtest_cloud_conf
+from helpers.utils import get_autopkgtest_cloud_conf, is_db_empty
 
 AMQP_CONTEXTS = ["ubuntu", "huge", "ppa", "upstream"]
 
@@ -85,6 +85,11 @@ class AutopkgtestQueueContents:
         """
 
         db_con = sqlite3.connect("file:%s?mode=ro" % self.database, uri=True)
+        if is_db_empty(db_con=db_con):
+            logging.warning(
+                "Database is currently empty - waiting for it to be populated, exiting cache-amqp"
+            )
+            sys.exit(0)
 
         release_arches = {}
         releases = []
diff --git a/charms/focal/autopkgtest-web/webcontrol/db-backup b/charms/focal/autopkgtest-web/webcontrol/db-backup
index b03100b..435ad11 100755
--- a/charms/focal/autopkgtest-web/webcontrol/db-backup
+++ b/charms/focal/autopkgtest-web/webcontrol/db-backup
@@ -6,17 +6,20 @@ and clears up old backups
 
 import atexit
 import datetime
-import gzip
 import hashlib
 import logging
 import os
-import shutil
 import sqlite3
 import sys
 from pathlib import Path
 
 import swiftclient
-from helpers.utils import get_autopkgtest_cloud_conf, init_db
+from helpers.utils import (
+    SimpleZstdInterface,
+    get_autopkgtest_cloud_conf,
+    init_db,
+    init_swift_con,
+)
 
 DB_PATH = ""
 DB_NAME = ""
@@ -39,7 +42,9 @@ def db_connect() -> sqlite3.Connection:
     DB_PATH = Path(cp["web"]["database"])
     DB_NAME = DB_PATH.name
     DB_BACKUP_NAME = "%s.bak" % DB_NAME
-    DB_BACKUP_PATH = Path("/tmp") / (DB_PATH.name + ".bak")
+    DB_BACKUP_PATH = Path("/tmp") / (
+        DB_PATH.name + ".zst"
+    )  # using zst extension as we will compress with zstd
 
     db_con = init_db(cp["web"]["database"])
 
@@ -47,40 +52,13 @@ def db_connect() -> sqlite3.Connection:
 
 
 def backup_db(db_con: sqlite3.Connection):
-    db_backup_con = sqlite3.connect(DB_BACKUP_PATH)
-    with db_backup_con:
-        db_con.backup(db_backup_con, pages=1)
-    db_backup_con.close()
-
-
-def compress_db():
-    """
-    use gzip to compress database
-    """
-    with open(DB_BACKUP_PATH, "rb") as f_in, gzip.open(
-        "%s.gz" % DB_BACKUP_PATH, "wb"
-    ) as f_out:
-        shutil.copyfileobj(f_in, f_out)
-
-
-def init_swift_con() -> swiftclient.Connection:
-    """
-    Establish connection to swift storage
-    """
-    swift_creds = {
-        "authurl": os.environ["OS_AUTH_URL"],
-        "user": os.environ["OS_USERNAME"],
-        "key": os.environ["OS_PASSWORD"],
-        "os_options": {
-            "region_name": os.environ["OS_REGION_NAME"],
-            "project_domain_name": os.environ["OS_PROJECT_DOMAIN_NAME"],
-            "project_name": os.environ["OS_PROJECT_NAME"],
-            "user_domain_name": os.environ["OS_USER_DOMAIN_NAME"],
-        },
-        "auth_version": 3,
-    }
-    swift_conn = swiftclient.Connection(**swift_creds)
-    return swift_conn
+    zstd = SimpleZstdInterface()
+    sql_backup = []
+    for line in db_con.iterdump():
+        sql_backup.append(line)
+    compressed_backup = zstd.compress("\n".join(sql_backup).encode())
+    with open(DB_BACKUP_PATH, "wb") as bkp_file:
+        bkp_file.write(compressed_backup)
 
 
 def create_container_if_it_doesnt_exist(swift_conn: swiftclient.Connection):
@@ -96,12 +74,12 @@ def create_container_if_it_doesnt_exist(swift_conn: swiftclient.Connection):
 
 
 def get_db_backup_checksum():
-    with open("%s.gz" % DB_BACKUP_PATH, "rb") as bkp_f:
+    with open(DB_BACKUP_PATH, "rb") as bkp_f:
         md5 = hashlib.md5(bkp_f.read()).hexdigest()
     return md5
 
 
-def upload_backup_to_db(
+def upload_backup_to_swift(
     swift_conn: swiftclient.Connection,
 ) -> swiftclient.Connection:
     """
@@ -111,18 +89,17 @@ def upload_backup_to_db(
     checksum = get_db_backup_checksum()
     object_path = "%s/%s-%s.%s" % (
         now,
-        DB_PATH.name.split(".")[0],
+        DB_BACKUP_NAME.split(".")[0],
         checksum,
-        "db.gz",
+        ".db.zst",
     )
+    db_backup_contents = Path(DB_BACKUP_PATH).read_bytes()
     for retry in range(SWIFT_RETRIES):
         try:
             swift_conn.put_object(
-                CONTAINER_NAME,
-                object_path,
-                "%s.gz" % DB_BACKUP_PATH,
-                content_type="text/plain; charset=UTF-8",
-                headers={"Content-Encoding": "gzip"},
+                container=CONTAINER_NAME,
+                obj=object_path,
+                contents=db_backup_contents,
             )
             break
         except swiftclient.exceptions.ClientException as e:
@@ -183,15 +160,13 @@ if __name__ == "__main__":
     db_con = db_connect()
     logging.info("Creating a backup of the db...")
     backup_db(db_con)
-    logging.info("Compressing db")
-    compress_db()
     logging.info("Registering cleanup function")
     atexit.register(cleanup)
     logging.info("Setting up swift connection")
     swift_conn = init_swift_con()
     create_container_if_it_doesnt_exist(swift_conn)
     logging.info("Uploading db to swift!")
-    swift_conn = upload_backup_to_db(swift_conn)
+    swift_conn = upload_backup_to_swift(swift_conn)
     logging.info("Pruning old database backups")
     swift_conn = delete_old_backups(swift_conn)
     cleanup()
diff --git a/charms/focal/autopkgtest-web/webcontrol/helpers/utils.py b/charms/focal/autopkgtest-web/webcontrol/helpers/utils.py
index 89ea672..bea9a46 100644
--- a/charms/focal/autopkgtest-web/webcontrol/helpers/utils.py
+++ b/charms/focal/autopkgtest-web/webcontrol/helpers/utils.py
@@ -11,6 +11,7 @@ import pathlib
 import random
 import signal
 import sqlite3
+import subprocess
 import time
 import typing
 
@@ -18,10 +19,29 @@ import typing
 from dataclasses import dataclass
 
 import distro_info
+import swiftclient
 
 sqlite3.paramstyle = "named"
 
 
+class SimpleZstdInterface:
+    def __init__(self):
+        self.COMPRESS_COMMAND = ["zstd", "--compress"]
+        self.DECOMPRESS_COMMAND = ["zstd", "--compress"]
+
+    def zync_command(self, input: bytes, command: typing.List[str]):
+        p = subprocess.run(
+            command, input=input, capture_output=True, check=True
+        )
+        return p.stdout
+
+    def compress(self, data: bytes) -> bytes:
+        return self.zync_command(data, self.COMPRESS_COMMAND)
+
+    def decompress(self, data: bytes) -> bytes:
+        return self.zync_command(data, self.DECOMPRESS_COMMAND)
+
+
 @dataclass
 class SqliteWriterConfig:
     writer_exchange_name = "sqlite-write-me.fanout"
@@ -253,4 +273,44 @@ def get_test_id(db_con, release, arch, src):
         return test_id
 
 
+def init_swift_con() -> swiftclient.Connection:
+    """
+    Establish connection to swift storage
+    """
+    swift_creds = {
+        "authurl": os.environ["OS_AUTH_URL"],
+        "user": os.environ["OS_USERNAME"],
+        "key": os.environ["OS_PASSWORD"],
+        "os_options": {
+            "region_name": os.environ["OS_REGION_NAME"],
+            "project_domain_name": os.environ["OS_PROJECT_DOMAIN_NAME"],
+            "project_name": os.environ["OS_PROJECT_NAME"],
+            "user_domain_name": os.environ["OS_USER_DOMAIN_NAME"],
+        },
+        "auth_version": 3,
+    }
+    swift_conn = swiftclient.Connection(**swift_creds)
+    return swift_conn
+
+
+def is_db_empty(db_con):
+    cursor = db_con.cursor()
+    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
+    tables = cursor.fetchall()
+    if len(tables) == 0:
+        return True
+    for table in tables:
+        cursor.execute(f"SELECT * FROM {table[0]};")
+        entries = cursor.fetchall()
+        if len(entries) > 0:
+            return False
+    return True
+
+
+def get_db_path():
+    cp = configparser.ConfigParser()
+    cp.read(os.path.expanduser("~ubuntu/autopkgtest-cloud.conf"))
+    return cp["web"]["database"]
+
+
 get_test_id._cache = {}
diff --git a/charms/focal/autopkgtest-web/webcontrol/publish-db b/charms/focal/autopkgtest-web/webcontrol/publish-db
index a621f8a..4149637 100755
--- a/charms/focal/autopkgtest-web/webcontrol/publish-db
+++ b/charms/focal/autopkgtest-web/webcontrol/publish-db
@@ -11,11 +11,12 @@ import hashlib
 import logging
 import os
 import sqlite3
+import sys
 import tempfile
 import urllib.request
 
 import apt_pkg
-from helpers.utils import get_autopkgtest_cloud_conf
+from helpers.utils import get_autopkgtest_cloud_conf, is_db_empty
 
 sqlite3.paramstyle = "named"
 
@@ -28,11 +29,24 @@ components = ["main", "restricted", "universe", "multiverse"]
 
 def init_db(path, path_current, path_rw):
     """Create DB if it does not exist, and connect to it"""
-
     db = sqlite3.connect(path)
     db_rw = sqlite3.connect("file:%s?mode=ro" % path_rw, uri=True)
 
-    # Copy r/w database over
+    # checks if db is empty
+    if is_db_empty(db_rw):
+        logging.warning(f"Database at {path_rw} is empty")
+        sys.exit(0)
+
+    # if no db, we need to copy /home/ubuntu/autopkgtest.db to /home/ubuntu/public/autopkgtest.db
+    if not os.path.exists(path_current):
+        logging.warning(
+            f"Looks like there's no pre-existing db at {path_current}, copying..."
+        )
+        public_db_con = sqlite3.connect(path_current)
+        db_rw.backup(public_db_con)
+        public_db_con.close()
+
+    logging.info(f"backing up {path_rw} to {path}")
     with db:
         db_rw.backup(db)
     db_rw.close()
diff --git a/charms/focal/autopkgtest-web/webcontrol/sqlite-writer b/charms/focal/autopkgtest-web/webcontrol/sqlite-writer
index d0ec23a..d309311 100755
--- a/charms/focal/autopkgtest-web/webcontrol/sqlite-writer
+++ b/charms/focal/autopkgtest-web/webcontrol/sqlite-writer
@@ -9,11 +9,21 @@ import os
 import socket
 import sqlite3
 
+import swiftclient
+
 sqlite3.paramstyle = "named"
 import urllib.parse
 
 import amqplib.client_0_8 as amqp
-from helpers.utils import SqliteWriterConfig, get_test_id, init_db
+from helpers.utils import (
+    SimpleZstdInterface,
+    SqliteWriterConfig,
+    get_db_path,
+    get_test_id,
+    init_db,
+    init_swift_con,
+    is_db_empty,
+)
 
 LAST_CHECKPOINT = datetime.datetime.now()
 
@@ -38,16 +48,6 @@ def amqp_connect():
     return amqp_con
 
 
-def db_connect():
-    """Connect to SQLite DB"""
-    cp = configparser.ConfigParser()
-    cp.read(os.path.expanduser("~ubuntu/autopkgtest-cloud.conf"))
-
-    db_con = init_db(cp["web"]["database"])
-
-    return db_con
-
-
 def check_msg(queue_msg):
     queue_keys = set(queue_msg.keys())
     if set(SqliteWriterConfig.amqp_entry_fields) == queue_keys:
@@ -109,9 +109,55 @@ def msg_callback(msg, db_con):
     checkpoint_db_if_necessary(db_con)
 
 
+def restore_db_from_backup(db_con: sqlite3.Connection):
+    backups_container = "db-backups"
+    db_con.execute("PRAGMA wal_checkpoint(TRUNCATE);")
+    logging.info("Connecting to swift")
+    try:
+        swift_conn = init_swift_con()
+    except swiftclient.ClientException as e:
+        logging.warning(
+            (
+                f"Initialising swift connection failed with {e} - "
+                "continuing without restoring db from backup"
+            )
+        )
+        return
+    logging.info(
+        f"Connected to swift! Getting backups from container: {backups_container}"
+    )
+    _, objects = swift_conn.get_container(container=backups_container)
+    latest = objects[-1]
+    _, compressed_db_dump = swift_conn.get_object(
+        container=backups_container, obj=latest["name"]
+    )
+    zstd = SimpleZstdInterface()
+    db_dump = zstd.decompress(compressed_db_dump)
+    logging.info(
+        (
+            "Restoring db from swift - "
+            f"container: {backups_container} - object: {latest['name']}"
+        )
+    )
+    for line in db_dump.splitlines():
+        try:
+            db_con.execute(line.decode("utf-8"))
+        except sqlite3.OperationalError as e:
+            logging.warning(
+                f"Running sql command: `{line.decode('utf-8')}` failed with {e}"
+            )
+    logging.info("db restored from backup!")
+
+
 def main():
     logging.basicConfig(level=logging.INFO)
-    db_con = db_connect()
+    db_con = init_db(get_db_path())
+    if is_db_empty(db_con):
+        logging.info(
+            "DB is empty, indicating this unit has been recently deployed."
+        )
+        logging.info("Restoring database from a swift backup")
+        restore_db_from_backup(db_con)
     amqp_con = amqp_connect()
     status_ch = amqp_con.channel()
     status_ch.access_request("/complete", active=True, read=True, write=False)