Merge ~cjwatson/launchpad:fix-translations-opening-unapprove into launchpad:master

[Colin Watson]

fix-translations-opening: Unapprove existing imports

`scripts/fix-translations-opening.py` is run manually in the case where opening translations for a new series is interrupted (usually due to long transactions making it difficult to continue) and leaves translations in an incomplete state.

The last time we did this, in September 2016, we found that we additionally had to set the associated translation import queue entries back from Approved to Needs Review so that `cronscripts/rosetta-approve-imports.py` could process them again (see https://pastebin.canonical.com/164459/). Rather than keeping this solely in folk memory, let's make the `fix-translations-opening` script do that as well.

I'm afraid I don't have a good way to test this short of the real-world run that we're going to need to perform shortly.

[Ines Almeida (ines-almeida)]

Looks good to me

review: Approve

[William Grant (wgrant)]

Could I convince you to use the enums rather than hardcoding their integer values? That's a big potential footgun and grep-breaker otherwise.

review: Approve (code)

diff --git a/scripts/fix-translations-opening.py b/scripts/fix-translations-opening.py
index ad30977..10a9741 100755
--- a/scripts/fix-translations-opening.py
+++ b/scripts/fix-translations-opening.py
@@ -1,6 +1,6 @@
 #!/usr/bin/python3 -S
 #
-# Copyright 2012-2013 Canonical Ltd.  This software is licensed under the
+# Copyright 2012-2023 Canonical Ltd.  This software is licensed under the
 # GNU Affero General Public License version 3 (see the file LICENSE).
 
 import _pythonpath  # noqa: F401
@@ -13,6 +13,7 @@ from lp.registry.model.distroseries import DistroSeries
 from lp.services.database.interfaces import IPrimaryStore
 from lp.services.looptuner import DBLoopTuner, ITunableLoop
 from lp.services.scripts.base import LaunchpadScript
+from lp.translations.enums import RosettaImportStatus
 
 delete_pofiletranslator = """\
 DELETE FROM POFileTranslator
@@ -87,6 +88,22 @@ DELETE FROM POTemplate
      LIMIT ?)
 """
 
+# Reset status from Approved to Needs Review; we've just unset the target,
+# so these entries will have to be gardened again.
+unapprove_translationimportqueueentry = """\
+UPDATE TranslationImportQueueEntry
+   SET status = %(needs_review)d
+ WHERE TranslationImportQueueEntry.id IN (
+    SELECT TranslationImportQueueEntry.id
+      FROM TranslationImportQueueEntry
+     WHERE TranslationImportQueueEntry.status = %(approved)d
+       AND TranslationImportQueueEntry.distroseries = ?
+     LIMIT ?)
+""" % {
+    "needs_review": RosettaImportStatus.NEEDS_REVIEW.value,
+    "approved": RosettaImportStatus.APPROVED.value,
+}
+
 statements = [
     delete_pofiletranslator,
     null_translationimportqueueentry_pofile,
@@ -95,6 +112,7 @@ statements = [
     delete_packagingjob,
     null_translationimportqueueentry_potemplate,
     delete_potemplate,
+    unapprove_translationimportqueueentry,
 ]