canonical-ubuntu-qa team mailing list archive

[Skia <mp+475520@xxxxxxxxxxxxxxxxxx>]

Skia has proposed merging ~hyask/autopkgtest-cloud:skia/publish_db_copy_url_last_checked into autopkgtest-cloud:master.

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

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

Quick win bringing nice performance improvement to `publish-db`.
-- 
Your team Canonical's Ubuntu QA is requested to review the proposed merge of ~hyask/autopkgtest-cloud:skia/publish_db_copy_url_last_checked into autopkgtest-cloud:master.

diff --git a/charms/focal/autopkgtest-web/webcontrol/publish-db b/charms/focal/autopkgtest-web/webcontrol/publish-db
index 4149637..78b4b92 100755
--- a/charms/focal/autopkgtest-web/webcontrol/publish-db
+++ b/charms/focal/autopkgtest-web/webcontrol/publish-db
@@ -78,7 +78,7 @@ def init_db(path, path_current, path_rw):
         except sqlite3.OperationalError as e2:
             if "duplicate column name" not in str(e2):
                 raise
-        logging.debug("table already exists")
+        logging.debug("table 'current_version' already exists")
     db.execute(
         "CREATE INDEX IF NOT EXISTS current_version_pocket_ix "
         "ON current_version(pocket, component)"
@@ -110,21 +110,22 @@ def init_db(path, path_current, path_rw):
             "  PRIMARY KEY(url))"
         )
         logging.debug("database table url_last_checked created")
-        try:
-            if current_version_copied:
-                db.execute(
-                    "INSERT INTO url_last_checked (url, timestamp) "
-                    "SELECT url, timestamp FROM current.url_last_checked"
-                )
-                logging.debug("Old url_last_checked copied over")
-        except sqlite3.OperationalError as e:
-            if "no such table" not in str(e):
-                raise
-            logging.debug("no url_last_checked yet, first run probably")
     except sqlite3.OperationalError as e:
         if "already exists" not in str(e):
             raise
-        logging.debug("table already exists")
+        logging.debug("table 'url_last_checked' already exists")
+
+    try:
+        if current_version_copied:
+            db.execute(
+                "INSERT OR REPLACE INTO url_last_checked (url, timestamp) "
+                "SELECT url, timestamp FROM current.url_last_checked"
+            )
+            logging.debug("Old url_last_checked copied over")
+    except sqlite3.OperationalError as e:
+        if "no such table" not in str(e):
+            raise
+        logging.debug("no url_last_checked yet, first run probably")
 
     db.commit()
     db.execute("DETACH DATABASE current")