Launchpad itself

Merge lp:~allenap/launchpad/wipe-precise-translations into lp:launchpad

  1. wipe-precise-translations
  2. Merge into devel
Proposed by Gavin Panella
Status: Rejected
Rejected by: Gavin Panella
Proposed branch: lp:~allenap/launchpad/wipe-precise-translations
Merge into: lp:launchpad
Diff against target: 268 lines (+259/-0)
2 files modified
findpreciserefs.sql (+102/-0)
wipeprecisetrans.py (+157/-0)
To merge this branch: bzr merge lp:~allenap/launchpad/wipe-precise-translations
Reviewer Review Type Date Requested Status
Gavin Panella (community) Approve
Review via email: mp+82422@code.launchpad.net

Description of the change

Script to wipe Precise's translations in production so that we can reattempt the opening (see https://wiki.canonical.com/Launchpad/Translations/UbuntuOpenings).

This will just be run once and not landed.

To post a comment you must log in.
Revision history for this message
Gavin Panella (allenap) wrote :

Aaron sanity checked this yesterday.

review: Approve
lp:~allenap/launchpad/wipe-precise-translations updated
14305. By Gavin Panella

Log statements and row counts.

14306. By Gavin Panella

Null out TranslationImportQueueEntry.pofile and .potemplate.

14307. By Gavin Panella

Fix nulling.

14308. By Gavin Panella

Script to find references to POFile and POTemplate records related to Precise.

14309. By Gavin Panella

Rename.

14310. By Gavin Panella

Delete PackagingJob records too.

14311. By Gavin Panella

Join into self tables so that deletes/updates will always find something until there's nothing left.

Revision history for this message
Gavin Panella (allenap) wrote :

This was a one-time script, put here for review only.

Unmerged revisions

14311. By Gavin Panella

Join into self tables so that deletes/updates will always find something until there's nothing left.

14310. By Gavin Panella

Delete PackagingJob records too.

14309. By Gavin Panella

Rename.

14308. By Gavin Panella

Script to find references to POFile and POTemplate records related to Precise.

14307. By Gavin Panella

Fix nulling.

14306. By Gavin Panella

Null out TranslationImportQueueEntry.pofile and .potemplate.

14305. By Gavin Panella

Log statements and row counts.

14304. By Gavin Panella

Script to wipe Precise's translations.

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== added file 'findpreciserefs.sql'
2--- findpreciserefs.sql 1970-01-01 00:00:00 +0000
3+++ findpreciserefs.sql 2011-11-25 11:40:35 +0000
4@@ -0,0 +1,102 @@
5+\a
6+\t on
7+
8+\set precise 'SELECT DistroSeries.id FROM DistroSeries JOIN Distribution ON Distribution.id = DistroSeries.distribution WHERE Distribution.name = ''ubuntu'' AND DistroSeries.name = ''precise'''
9+
10+\qecho POFile referenced by:
11+
12+\qecho TABLE "poexportrequest" CONSTRAINT "poexportrequest_pofile_fk" FOREIGN KEY (pofile) REFERENCES pofile(id)
13+SELECT COUNT(*)
14+ FROM poexportrequest, pofile, potemplate
15+ WHERE poexportrequest.pofile = pofile.id
16+ AND pofile.potemplate = potemplate.id
17+ AND potemplate.distroseries = (:precise)
18+;
19+
20+\qecho TABLE "pofiletranslator" CONSTRAINT "pofiletranslator__pofile__fk" FOREIGN KEY (pofile) REFERENCES pofile(id)
21+SELECT COUNT(*)
22+ FROM pofiletranslator, pofile, potemplate
23+ WHERE pofiletranslator.pofile = pofile.id
24+ AND pofile.potemplate = potemplate.id
25+ AND potemplate.distroseries = (:precise)
26+;
27+
28+\qecho TABLE "translationimportqueueentry" CONSTRAINT "translationimportqueueentry__pofile__fk" FOREIGN KEY (pofile) REFERENCES pofile(id)
29+SELECT COUNT(*)
30+ FROM translationimportqueueentry, pofile, potemplate
31+ WHERE translationimportqueueentry.pofile = pofile.id
32+ AND pofile.potemplate = potemplate.id
33+ AND potemplate.distroseries = (:precise)
34+;
35+
36+
37+\qecho POTemplate referenced by:
38+\qecho TABLE "todrop.pocomment" CONSTRAINT "$1" FOREIGN KEY (potemplate) REFERENCES potemplate(id)
39+SELECT COUNT(*)
40+ FROM todrop.pocomment, potemplate
41+ WHERE todrop.pocomment.potemplate = potemplate.id
42+ AND potemplate.distroseries = (:precise)
43+;
44+
45+\qecho TABLE "todrop.posubscription" CONSTRAINT "$2" FOREIGN KEY (potemplate) REFERENCES potemplate(id)
46+SELECT COUNT(*)
47+ FROM todrop.posubscription, potemplate
48+ WHERE todrop.posubscription.potemplate = potemplate.id
49+ AND potemplate.distroseries = (:precise)
50+;
51+
52+\qecho TABLE "poexportrequest" CONSTRAINT "poeportrequest_potemplate_fk" FOREIGN KEY (potemplate) REFERENCES potemplate(id)
53+SELECT COUNT(*)
54+ FROM poexportrequest, potemplate
55+ WHERE poexportrequest.potemplate = potemplate.id
56+ AND potemplate.distroseries = (:precise)
57+;
58+
59+\qecho TABLE "pofile" CONSTRAINT "pofile_potemplate_fk" FOREIGN KEY (potemplate) REFERENCES potemplate(id)
60+SELECT COUNT(*)
61+ FROM pofile, potemplate
62+ WHERE pofile.potemplate = potemplate.id
63+ AND potemplate.distroseries = (:precise)
64+;
65+
66+\qecho TABLE "packagingjob" CONSTRAINT "potemplate_fk" FOREIGN KEY (potemplate) REFERENCES potemplate(id)
67+SELECT COUNT(*)
68+ FROM pofile, potemplate
69+ WHERE pofile.potemplate = potemplate.id
70+ AND potemplate.distroseries = (:precise)
71+;
72+
73+\qecho TABLE "potmsgset" CONSTRAINT "potmsgset_potemplate_fk" FOREIGN KEY (potemplate) REFERENCES potemplate(id)
74+SELECT COUNT(*)
75+ FROM potmsgset, potemplate
76+ WHERE potmsgset.potemplate = potemplate.id
77+ AND potemplate.distroseries = (:precise)
78+;
79+
80+\qecho TABLE "suggestivepotemplate" CONSTRAINT "suggestivepotemplate__potemplate__fk" FOREIGN KEY (potemplate) REFERENCES potemplate(id) ON DELETE CASCADE
81+SELECT COUNT(*)
82+ FROM suggestivepotemplate, potemplate
83+ WHERE suggestivepotemplate.potemplate = potemplate.id
84+ AND potemplate.distroseries = (:precise)
85+;
86+
87+\qecho TABLE "translationimportqueueentry" CONSTRAINT "translationimportqueueentry__potemplate__fk" FOREIGN KEY (potemplate) REFERENCES potemplate(id)
88+SELECT COUNT(*)
89+ FROM translationimportqueueentry, potemplate
90+ WHERE translationimportqueueentry.potemplate = potemplate.id
91+ AND potemplate.distroseries = (:precise)
92+;
93+
94+\qecho TABLE "translationmessage" CONSTRAINT "translationmessage_potemplate_fkey" FOREIGN KEY (potemplate) REFERENCES potemplate(id)
95+SELECT COUNT(*)
96+ FROM translationmessage, potemplate
97+ WHERE translationmessage.potemplate = potemplate.id
98+ AND potemplate.distroseries = (:precise)
99+;
100+
101+\qecho TABLE "translationtemplateitem" CONSTRAINT "translationtemplateitem_potemplate_fkey" FOREIGN KEY (potemplate) REFERENCES potemplate(id)
102+SELECT COUNT(*)
103+ FROM translationtemplateitem, potemplate
104+ WHERE translationtemplateitem.potemplate = potemplate.id
105+ AND potemplate.distroseries = (:precise)
106+;
107
108=== added file 'wipeprecisetrans.py'
109--- wipeprecisetrans.py 1970-01-01 00:00:00 +0000
110+++ wipeprecisetrans.py 2011-11-25 11:40:35 +0000
111@@ -0,0 +1,157 @@
112+#!/usr/bin/python2.6 -S
113+
114+__metaclass__ = type
115+
116+import _pythonpath
117+
118+from zope.component import getUtility
119+from zope.interface import implements
120+
121+from canonical.launchpad.interfaces.looptuner import ITunableLoop
122+from canonical.launchpad.utilities.looptuner import DBLoopTuner
123+from canonical.launchpad.webapp.interfaces import (
124+ IStoreSelector,
125+ MAIN_STORE,
126+ MASTER_FLAVOR,
127+ )
128+from lp.services.scripts.base import LaunchpadScript
129+
130+
131+select_precise = """\
132+SELECT DistroSeries.id
133+ FROM DistroSeries
134+ JOIN Distribution ON
135+ Distribution.id = DistroSeries.distribution
136+ WHERE Distribution.name = 'ubuntu'
137+ AND DistroSeries.name = 'precise'
138+"""
139+
140+delete_pofiletranslator = """\
141+DELETE FROM POFileTranslator
142+ WHERE POFileTranslator.id IN (
143+ SELECT POFileTranslator.id
144+ FROM POFileTranslator, POFile, POTemplate
145+ WHERE POFileTranslator.pofile = POFile.id
146+ AND POFile.potemplate = POTemplate.id
147+ AND POTemplate.distroseries = (%s)
148+ LIMIT ?)
149+""" % select_precise
150+
151+null_translationimportqueueentry_pofile = """\
152+UPDATE TranslationImportQueueEntry
153+ SET pofile = NULL
154+ WHERE TranslationImportQueueEntry.id IN (
155+ SELECT TranslationImportQueueEntry.id
156+ FROM TranslationImportQueueEntry, POFile, POTemplate
157+ WHERE TranslationImportQueueEntry.pofile = POFile.id
158+ AND POFile.potemplate = POTemplate.id
159+ AND POTemplate.distroseries = (%s)
160+ LIMIT ?)
161+""" % select_precise
162+
163+delete_pofile = """\
164+DELETE FROM POFile
165+ WHERE POFile.id IN (
166+ SELECT POFile.id
167+ FROM POFile, POTemplate
168+ WHERE POFile.potemplate = POTemplate.id
169+ AND POTemplate.distroseries = (%s)
170+ LIMIT ?)
171+""" % select_precise
172+
173+delete_translationtemplateitem = """\
174+DELETE FROM TranslationTemplateItem
175+ WHERE TranslationTemplateItem.id IN (
176+ SELECT TranslationTemplateItem.id
177+ FROM TranslationTemplateItem, POTemplate
178+ WHERE TranslationTemplateItem.potemplate = POTemplate.id
179+ AND POTemplate.distroseries = (%s)
180+ LIMIT ?)
181+""" % select_precise
182+
183+delete_packagingjob = """\
184+DELETE FROM PackagingJob
185+ WHERE PackagingJob.id IN (
186+ SELECT PackagingJob.id
187+ FROM PackagingJob, POTemplate
188+ WHERE PackagingJob.potemplate = POTemplate.id
189+ AND POTemplate.distroseries = (%s)
190+ LIMIT ?)
191+""" % select_precise
192+
193+null_translationimportqueueentry_potemplate = """\
194+UPDATE TranslationImportQueueEntry
195+ SET potemplate = NULL
196+ WHERE TranslationImportQueueEntry.id IN (
197+ SELECT TranslationImportQueueEntry.id
198+ FROM TranslationImportQueueEntry, POTemplate
199+ WHERE TranslationImportQueueEntry.potemplate = POTemplate.id
200+ AND POTemplate.distroseries = (%s)
201+ LIMIT ?)
202+""" % select_precise
203+
204+delete_potemplate = """\
205+DELETE FROM POTemplate
206+ WHERE POTemplate.id IN (
207+ SELECT POTemplate.id
208+ FROM POTemplate
209+ WHERE POTemplate.distroseries = (%s)
210+ LIMIT ?)
211+""" % select_precise
212+
213+statements = [
214+ delete_pofiletranslator,
215+ null_translationimportqueueentry_pofile,
216+ delete_pofile,
217+ delete_translationtemplateitem,
218+ delete_packagingjob,
219+ null_translationimportqueueentry_potemplate,
220+ delete_potemplate,
221+ ]
222+
223+
224+class ExecuteLoop:
225+
226+ implements(ITunableLoop)
227+
228+ def __init__(self, statement, logger):
229+ self.statement = statement
230+ self.logger = logger
231+ self.done = False
232+
233+ def isDone(self):
234+ return self.done
235+
236+ def __call__(self, chunk_size):
237+ self.logger.info(
238+ "%s (limited to %d rows)",
239+ self.statement.splitlines()[0],
240+ chunk_size)
241+ store = getUtility(IStoreSelector).get(MAIN_STORE, MASTER_FLAVOR)
242+ result = store.execute(self.statement, (chunk_size,))
243+ self.done = (result.rowcount == 0)
244+ self.logger.info(
245+ "%d rows deleted (%s)", result.rowcount,
246+ ("done" if self.done else "not done"))
247+ store.commit()
248+
249+
250+class WipePreciseTranslationsScript(LaunchpadScript):
251+
252+ description = "Wipe Ubuntu Precise's translations."
253+
254+ def add_my_options(self):
255+ self.parser.epilog = (
256+ "Before running this script you must `GRANT DELETE ON TABLE "
257+ "PackagingJob TO rosettaadmin` and afterwards you ought to "
258+ "`REVOKE DELETE ON PackagingJob FROM rosettaadmin`.")
259+
260+ def main(self):
261+ for statement in statements:
262+ delete = ExecuteLoop(statement, self.logger)
263+ tuner = DBLoopTuner(delete, 2.0, maximum_chunk_size=5000)
264+ tuner.run()
265+
266+
267+if __name__ == '__main__':
268+ WipePreciseTranslationsScript(dbuser='rosettaadmin').run()