Launchpad itself

Merge lp:~stub/launchpad/garbo into lp:launchpad/db-devel

  1. garbo
  2. Merge into db-devel
Proposed by Stuart Bishop
Status: Merged
Approved by: Stuart Bishop
Approved revision: no longer in the source branch.
Merged at revision: not available
Proposed branch: lp:~stub/launchpad/garbo
Merge into: lp:launchpad/db-devel
Diff against target: 1206 lines
To merge this branch: bzr merge lp:~stub/launchpad/garbo
Reviewer Review Type Date Requested Status
Paul Hummer (community) code Approve
Review via email: mp+4834@code.launchpad.net
To post a comment you must log in.
Revision history for this message
Stuart Bishop (stub) wrote :

The first cut of the database garbage collector. This is Oscar the Grounch, originally conceived when Launchpad was just an ER diagram.

I expect the framework will grow over time. This first version just prunes our Nonce tables and CodeImportResult entries.

There are some other cronscripts that could be folded in to this framework.

Some DB tweaks snuck in during the work.

Revision history for this message
Paul Hummer (rockstar) wrote :

Obviously, I can't comment on the database stuff, but the scripts themselves appear sound and well tested.

review: Approve (code)

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== added file 'cronscripts/garbo-daily.py'
2--- cronscripts/garbo-daily.py 1970-01-01 00:00:00 +0000
3+++ cronscripts/garbo-daily.py 2009-03-25 11:41:03 +0000
4@@ -0,0 +1,19 @@
5+#!/usr/bin/python2.4
6+# Copyright 2009 Canonical Ltd. All rights reserved.
7+
8+"""Database garbage collector.
9+
10+Remove or archive unwanted data. Detect, warn and possibly repair data
11+corruption.
12+"""
13+
14+__metaclass__ = type
15+__all__ = []
16+
17+import _pythonpath
18+from canonical.launchpad.scripts.garbo import DailyDatabaseGarbageCollector
19+
20+if __name__ == '__main__':
21+ script = DailyDatabaseGarbageCollector()
22+ script.lock_and_run()
23+
24
25=== added file 'cronscripts/garbo-hourly.py'
26--- cronscripts/garbo-hourly.py 1970-01-01 00:00:00 +0000
27+++ cronscripts/garbo-hourly.py 2009-03-25 11:41:03 +0000
28@@ -0,0 +1,19 @@
29+#!/usr/bin/python2.4
30+# Copyright 2009 Canonical Ltd. All rights reserved.
31+
32+"""Database garbage collector.
33+
34+Remove or archive unwanted data. Detect, warn and possibly repair data
35+corruption.
36+"""
37+
38+__metaclass__ = type
39+__all__ = []
40+
41+import _pythonpath
42+from canonical.launchpad.scripts.garbo import HourlyDatabaseGarbageCollector
43+
44+if __name__ == '__main__':
45+ script = HourlyDatabaseGarbageCollector()
46+ script.lock_and_run()
47+
48
49=== added file 'database/schema/patch-2109-42-0.sql'
50--- database/schema/patch-2109-42-0.sql 1970-01-01 00:00:00 +0000
51+++ database/schema/patch-2109-42-0.sql 2009-03-25 11:41:03 +0000
52@@ -0,0 +1,8 @@
53+SET client_min_messages=ERROR;
54+
55+ALTER TABLE OAuthNonce
56+ ADD CONSTRAINT oauthnonce__access_token__fk
57+ FOREIGN KEY (access_token) REFERENCES OAuthAccessToken;
58+
59+INSERT INTO LaunchpadDatabaseRevision VALUES (2109, 42, 0);
60+
61
62=== modified file 'database/schema/security.cfg'
63--- database/schema/security.cfg 2009-03-24 22:08:20 +0000
64+++ database/schema/security.cfg 2009-03-25 11:41:03 +0000
65@@ -95,7 +95,7 @@
66 [launchpad_main]
67 # lpmain replication set access from the main Z3 application.
68 type=user
69-groups=write
70+groups=write,script
71 public.account = SELECT
72 public.announcement = SELECT, INSERT, UPDATE, DELETE
73 public.answercontact = SELECT, INSERT, UPDATE, DELETE
74@@ -232,7 +232,7 @@
75 public.revisionauthor = SELECT, INSERT, UPDATE
76 public.revisionnumber = SELECT, INSERT
77 public.revisionparent = SELECT, INSERT
78-public.scriptactivity = SELECT, INSERT
79+public.scriptactivity = SELECT
80 public.shipitreport = SELECT, INSERT
81 public.shipitsurvey = SELECT, INSERT, UPDATE
82 public.shipitsurveyquestion = SELECT, INSERT
83@@ -278,8 +278,14 @@
84 type=user
85 groups=launchpad_main
86
87+[script]
88+# Permissions required by all scripts.
89+type=group
90+public.scriptactivity = SELECT, INSERT
91+
92 [statistician]
93 type=user
94+groups=script
95 public.archive = SELECT, UPDATE
96 public.archivearch = SELECT, UPDATE
97 public.binarypackagename = SELECT
98@@ -308,7 +314,6 @@
99 public.product = SELECT
100 public.productseries = SELECT
101 public.question = SELECT
102-public.scriptactivity = SELECT, INSERT
103 public.sourcepackagename = SELECT
104 public.sourcepackagepublishinghistory = SELECT
105 public.sourcepackagerelease = SELECT
106@@ -329,6 +334,7 @@
107
108 [librariangc]
109 type=user
110+groups=script
111 public.libraryfilealias = SELECT, UPDATE, DELETE
112 public.libraryfilecontent = SELECT, UPDATE, DELETE
113 # This user needs select on every table that references LibraryFileAlias
114@@ -358,7 +364,6 @@
115 public.product = SELECT
116 public.productreleasefile = SELECT
117 public.project = SELECT
118-public.scriptactivity = SELECT, INSERT
119 public.shipitreport = SELECT
120 public.shippingrun = SELECT
121 public.sprint = SELECT
122@@ -369,6 +374,7 @@
123 [productreleasefinder]
124 # Dyson release import script
125 type=user
126+groups=script
127 public.product = SELECT
128 public.productseries = SELECT
129 public.productrelease = SELECT, INSERT, UPDATE
130@@ -378,26 +384,24 @@
131 # Needed to write to the librarian
132 public.libraryfilealias = SELECT, INSERT
133 public.libraryfilecontent = SELECT, INSERT
134-public.scriptactivity = SELECT, INSERT
135
136 [pofilestats]
137 # Translations POFile statistics verification/update script
138 type=user
139+groups=script
140 public.language = SELECT
141 public.pofile = SELECT, UPDATE
142 public.potemplate = SELECT
143 public.potmsgset = SELECT
144-public.scriptactivity = SELECT, INSERT
145 public.translationmessage = SELECT
146 public.translationtemplateitem = SELECT
147
148 [poimport]
149 # Rosetta import script
150 type=user
151-groups=write
152+groups=write,script
153 public.account = SELECT, INSERT
154 public.customlanguagecode = SELECT
155-public.scriptactivity = SELECT, INSERT
156 public.translationgroup = SELECT
157 public.translationimportqueueentry = SELECT, DELETE
158 public.translationmessage = SELECT, INSERT, UPDATE
159@@ -409,6 +413,7 @@
160 [poexport]
161 # Rosetta export script
162 type=user
163+groups=script
164 public.distribution = SELECT
165 public.distroseries = SELECT
166 public.emailaddress = SELECT
167@@ -427,7 +432,6 @@
168 public.potranslation = SELECT
169 public.product = SELECT
170 public.productseries = SELECT
171-public.scriptactivity = SELECT, INSERT
172 public.sourcepackagename = SELECT
173 public.translationgroup = SELECT
174 public.translationmessage = SELECT
175@@ -439,6 +443,7 @@
176 [langpack]
177 # Language pack exporter script
178 type=user
179+groups=script
180 public.distribution = SELECT
181 public.distroseries = SELECT, UPDATE
182 public.emailaddress = SELECT
183@@ -457,7 +462,6 @@
184 public.potranslation = SELECT
185 public.product = SELECT
186 public.productseries = SELECT
187-public.scriptactivity = SELECT, INSERT
188 public.sourcepackagename = SELECT
189 public.translationgroup = SELECT
190 public.translationmessage = SELECT
191@@ -469,6 +473,7 @@
192 [checkwatches]
193 # Malone bug watch script
194 type=user
195+groups=script
196 public.account = SELECT, INSERT
197 public.accountpassword = SELECT, INSERT
198 public.answercontact = SELECT
199@@ -506,7 +511,6 @@
200 public.questionbug = SELECT
201 public.question = SELECT
202 public.questionsubscription = SELECT
203-public.scriptactivity = SELECT, INSERT
204 public.sourcepackagename = SELECT
205 public.structuralsubscription = SELECT
206 public.teammembership = SELECT
207@@ -532,7 +536,7 @@
208
209 [branchscanner]
210 type=user
211-groups=write
212+groups=write, script
213 public.account = SELECT, INSERT
214 public.accountpassword = SELECT, INSERT
215 public.branch = SELECT, UPDATE
216@@ -556,7 +560,6 @@
217 public.revisionauthor = SELECT, INSERT, UPDATE
218 public.revisionparent = SELECT, INSERT
219 public.revisionproperty = SELECT, INSERT
220-public.scriptactivity = SELECT, INSERT
221 public.sourcepackagename = SELECT
222 public.staticdiff = SELECT, INSERT, DELETE
223 public.validpersoncache = SELECT
224@@ -564,6 +567,7 @@
225
226 [targetnamecacheupdater]
227 type=user
228+groups=script
229 public.bugtask = SELECT, UPDATE
230 public.product = SELECT
231 public.productseries = SELECT
232@@ -572,10 +576,10 @@
233 public.sourcepackagename = SELECT
234 public.binarypackagename = SELECT
235 public.potemplate = SELECT, UPDATE
236-public.scriptactivity = SELECT, INSERT
237
238 [distributionmirror]
239 type=user
240+groups=script
241 public.archive = SELECT
242 public.archivearch = SELECT
243 public.binarypackagefile = SELECT
244@@ -597,7 +601,6 @@
245 public.mirrorproberecord = SELECT, INSERT
246 public.person = SELECT
247 public.processorfamily = SELECT
248-public.scriptactivity = SELECT, INSERT
249 public.securesourcepackagepublishinghistory = SELECT
250 public.securebinarypackagepublishinghistory = SELECT
251 public.sourcepackagerelease = SELECT
252@@ -608,15 +611,16 @@
253 [teammembership]
254 # Update the TeamMembership table setting expired members
255 type=user
256+groups=script
257 public.teammembership = SELECT, UPDATE
258 public.teamparticipation = SELECT, DELETE
259 public.person = SELECT
260 public.emailaddress = SELECT
261-public.scriptactivity = SELECT, INSERT
262
263 [karma]
264 # Update the KarmaCache table
265 type=user
266+groups=script
267 public.karmacache = SELECT, INSERT, UPDATE, DELETE
268 public.karma = SELECT
269 public.karmacategory = SELECT
270@@ -627,11 +631,11 @@
271 public.product = SELECT
272 public.validpersoncache = SELECT
273 public.validpersonorteamcache = SELECT
274-public.scriptactivity = SELECT, INSERT
275
276 [revisionkarma]
277 # Allocate karma for revisions.
278 type=user
279+groups=script
280 public.branch = SELECT
281 public.branchrevision = SELECT
282 public.karma = SELECT, INSERT
283@@ -642,20 +646,19 @@
284 public.productseries = SELECT
285 public.revision = SELECT, UPDATE
286 public.revisionauthor = SELECT
287-public.scriptactivity = SELECT, INSERT
288 public.validpersoncache = SELECT
289
290 [cve]
291 type=user
292+groups=script
293 public.cve = SELECT, INSERT, UPDATE
294 public.cvereference = SELECT, INSERT, UPDATE, DELETE
295-public.scriptactivity = SELECT, INSERT
296
297
298 [gina]
299 # Unpack sourcepackages and extract metadata
300 type=user
301-groups=write
302+groups=write,script
303 public.account = SELECT, INSERT
304 public.accountpassword = SELECT, INSERT
305 public.archive = SELECT, UPDATE
306@@ -663,14 +666,13 @@
307 public.distribution = SELECT
308 public.openidrpsummary = SELECT
309 public.packagediff = SELECT, INSERT, UPDATE
310-public.scriptactivity = SELECT, INSERT
311 public.securebinarypackagepublishinghistory = SELECT, INSERT, UPDATE, DELETE
312 public.securesourcepackagepublishinghistory = SELECT, INSERT, UPDATE, DELETE
313
314 [lucille]
315 # Soyuz archive publisher.
316 type=user
317-groups=write
318+groups=write,script
319 public.archive = SELECT, UPDATE
320 public.archivearch = SELECT
321 public.archiveauthtoken = SELECT, UPDATE
322@@ -679,7 +681,6 @@
323 public.gpgkey = SELECT, INSERT, UPDATE
324 public.packagecopyrequest = SELECT, INSERT, UPDATE
325 public.packagediff = SELECT, INSERT, UPDATE
326-public.scriptactivity = SELECT, INSERT
327 public.securebinarypackagepublishinghistory = SELECT, INSERT, UPDATE, DELETE
328 public.securesourcepackagepublishinghistory = SELECT, INSERT, UPDATE, DELETE
329 public.sourcepackagepublishinghistory = SELECT
330@@ -719,6 +720,7 @@
331
332 [fiera]
333 type=user
334+groups=script
335 public.account = SELECT
336 public.archive = SELECT, UPDATE
337 public.archivearch = SELECT, UPDATE
338@@ -750,11 +752,11 @@
339 public.person = SELECT
340 public.emailaddress = SELECT
341 public.teammembership = SELECT
342-public.scriptactivity = SELECT, INSERT
343 public.gpgkey = SELECT
344
345 [sourcerer]
346 type=user
347+groups=script
348 public.archive = SELECT
349 public.archivearch = SELECT
350 public.branch = SELECT, INSERT, UPDATE
351@@ -779,7 +781,6 @@
352 # To get stuff from the librarian
353 public.libraryfilealias = SELECT
354 public.libraryfilecontent = SELECT
355-public.scriptactivity = SELECT, INSERT
356
357 [write]
358 type=group
359@@ -884,6 +885,7 @@
360
361 [shipit]
362 type=user
363+groups=script
364 public.account = SELECT
365 public.continent = SELECT
366 public.country = SELECT
367@@ -893,7 +895,6 @@
368 public.libraryfilecontent = SELECT, INSERT
369 public.person = SELECT
370 public.requestedcds = SELECT, INSERT, UPDATE
371-public.scriptactivity = SELECT, INSERT
372 public.shipitreport = SELECT, INSERT
373 public.shipment = SELECT, INSERT
374 public.shippingrequest = SELECT, UPDATE
375@@ -905,17 +906,18 @@
376 [standingupdater]
377 # For the personal standing updater cron script.
378 type=user
379+groups=script
380 public.emailaddress = SELECT
381 public.mailinglist = SELECT
382 public.message = SELECT
383 public.messageapproval = SELECT
384 public.person = SELECT, UPDATE
385-public.scriptactivity = SELECT, INSERT
386 public.teamparticipation = SELECT
387
388 [answertracker]
389 # User running expire-questions.py
390 type=user
391+groups=script
392 public.account = SELECT, INSERT
393 public.accountpassword = SELECT, INSERT
394 public.answercontact = SELECT
395@@ -935,7 +937,6 @@
396 public.questionbug = SELECT
397 public.questionmessage = SELECT, INSERT
398 public.questionsubscription = SELECT
399-public.scriptactivity = SELECT, INSERT
400 public.sourcepackagename = SELECT
401 public.teammembership = SELECT
402 public.validpersoncache = SELECT
403@@ -943,6 +944,7 @@
404
405 [uploader]
406 type=user
407+groups=script
408 # Everything is keyed off an archive
409 public.archive = SELECT, INSERT, UPDATE
410 public.archivearch = SELECT, INSERT, UPDATE
411@@ -1002,8 +1004,6 @@
412 public.packageuploadbuild = SELECT, INSERT
413 public.packageuploadcustom = SELECT, INSERT
414
415-public.scriptactivity = SELECT, INSERT
416-
417 # For premature source-only publication
418 public.securesourcepackagepublishinghistory = SELECT, INSERT
419
420@@ -1045,6 +1045,7 @@
421
422 [queued]
423 type=user
424+groups=script
425 # Announce handling
426 public.account = SELECT, INSERT
427 public.person = SELECT, INSERT
428@@ -1097,8 +1098,6 @@
429 # rosetta auto imports
430 public.translationimportqueueentry = SELECT, INSERT, UPDATE
431
432-public.scriptactivity = SELECT, INSERT
433-
434 # Closing bugs.
435 public.bug = SELECT, UPDATE
436 public.bugactivity = SELECT, INSERT
437@@ -1135,10 +1134,10 @@
438
439 [ppad]
440 type=user
441+groups=script
442 public.archive = SELECT
443 public.archivearch = SELECT
444 public.person = SELECT
445-public.scriptactivity = SELECT, INSERT
446
447 [session]
448 # This user doesn't have access to any tables in the main launchpad
449@@ -1155,6 +1154,7 @@
450 # send-bug-notifications.py needs them. They should be removed
451 # when bug 37456 is fixed.
452 type=user
453+groups=script
454 public.account = SELECT
455 public.archive = SELECT
456 public.archivearch = SELECT
457@@ -1187,7 +1187,6 @@
458 public.message = SELECT, INSERT
459 public.messagechunk = SELECT, INSERT
460 public.milestone = SELECT
461-public.scriptactivity = SELECT, INSERT
462 public.structuralsubscription = SELECT
463 public.teammembership = SELECT
464 public.teamparticipation = SELECT
465@@ -1196,6 +1195,7 @@
466
467 [personnotification]
468 type=user
469+groups=script
470 public.personnotification = SELECT, UPDATE, DELETE
471 public.person = SELECT
472 public.emailaddress = SELECT
473@@ -1203,7 +1203,6 @@
474 public.libraryfilecontent = SELECT
475 public.message = SELECT
476 public.messagechunk = SELECT
477-public.scriptactivity = SELECT, INSERT
478 public.teammembership = SELECT
479 public.teamparticipation = SELECT
480 public.validpersoncache = SELECT
481@@ -1211,6 +1210,7 @@
482
483 [rosettaadmin]
484 type=user
485+groups=script
486 public.customlanguagecode = SELECT, INSERT, UPDATE, DELETE
487 public.distribution = SELECT
488 public.distroseries = SELECT
489@@ -1225,7 +1225,6 @@
490 public.potranslation = SELECT
491 public.product = SELECT
492 public.productseries = SELECT
493-public.scriptactivity = SELECT, INSERT
494 public.sourcepackagename = SELECT
495 public.translationimportqueueentry = SELECT, INSERT, UPDATE, DELETE
496 public.translationmessage = SELECT, INSERT, UPDATE, DELETE
497@@ -1234,12 +1233,12 @@
498
499 [oopsprune]
500 type=user
501+groups=script
502 public.bug = SELECT
503 public.bugtask = SELECT
504 public.message = SELECT
505 public.messagechunk = SELECT
506 public.question = SELECT
507-public.scriptactivity = SELECT, INSERT
508
509 [listteammembers]
510 type=user
511@@ -1260,7 +1259,7 @@
512
513 [processmail]
514 type=user
515-public.scriptactivity = SELECT, INSERT
516+groups=script
517
518 # Incoming emails are stored in the librarian
519 public.libraryfilealias = SELECT, INSERT
520@@ -1361,10 +1360,10 @@
521 [mlist-sync]
522 # The mailing list sync user
523 type=user
524+groups=script
525 public.mailinglist = SELECT
526 public.person = SELECT
527 public.emailaddress = SELECT, UPDATE
528-public.scriptactivity = SELECT, INSERT
529
530 [mlist-import]
531 # The mailing list import user
532@@ -1379,6 +1378,7 @@
533 [hwdb-submission-processor]
534 # The user that updates the HWDB with data from new submissions
535 type=user
536+groups=script
537 public.person = SELECT
538 public.hwdevicedriverlink = SELECT, INSERT
539 public.hwdevicenamevariant = SELECT, INSERT
540@@ -1397,7 +1397,6 @@
541 public.hwvendorname = SELECT, INSERT
542 public.libraryfilealias = SELECT
543 public.libraryfilecontent = SELECT
544-public.scriptactivity = SELECT, INSERT
545 public.teamparticipation = SELECT
546
547 [builddcontroller]
548@@ -1409,6 +1408,7 @@
549 [binaryfile-expire]
550 # The user that expires binary files from the librarian.
551 type=user
552+groups=script
553 public.archive = SELECT
554 public.binarypackagefile = SELECT
555 public.binarypackagepublishinghistory = SELECT
556@@ -1417,10 +1417,10 @@
557 public.person = SELECT
558 public.libraryfilealias = SELECT, UPDATE
559 public.securebinarypackagepublishinghistory = SELECT
560-public.scriptactivity = SELECT, UPDATE, INSERT, DELETE
561
562 [create-merge-proposals]
563 type=user
564+groups=script
565 public.account = SELECT
566 public.accountpassword = SELECT
567 public.branch = SELECT, INSERT, UPDATE
568@@ -1446,13 +1446,13 @@
569 public.product = SELECT
570 public.productseries = SELECT
571 public.project = SELECT
572-public.scriptactivity = SELECT, INSERT
573 public.staticdiff = SELECT, INSERT
574 public.teamparticipation = SELECT
575 public.validpersoncache = SELECT
576
577 [mp-creation-job]
578 type=user
579+groups=script
580 public.account = SELECT
581 public.accountpassword = SELECT
582 public.branch = SELECT
583@@ -1475,7 +1475,6 @@
584 public.person = SELECT
585 public.product = SELECT
586 public.productseries = SELECT
587-public.scriptactivity = SELECT, INSERT
588 public.staticdiff = SELECT, INSERT
589 public.teammembership = SELECT
590 public.teamparticipation = SELECT
591@@ -1483,6 +1482,7 @@
592
593 [send-branch-mail]
594 type=user
595+groups=script
596 public.account = SELECT
597 public.accountpassword = SELECT
598 public.branch = SELECT
599@@ -1507,7 +1507,6 @@
600 public.product = SELECT
601 public.productseries = SELECT
602 public.revision = SELECT
603-public.scriptactivity = SELECT, INSERT
604 public.staticdiff = SELECT, INSERT
605 public.teammembership = SELECT
606 public.teamparticipation = SELECT
607@@ -1516,6 +1515,7 @@
608 [updateremoteproduct]
609 # Updates Product.remote_product using bug watch information.
610 type=user
611+groups=script
612 public.account = SELECT, INSERT, UPDATE
613 public.person = SELECT, INSERT
614 public.product = SELECT, INSERT, UPDATE
615@@ -1537,14 +1537,13 @@
616 public.bugsubscription = SELECT, INSERT
617 public.bugmessage = SELECT, INSERT
618 public.sourcepackagename = SELECT
619-public.scriptactivity = SELECT, INSERT
620
621 [updatesourceforgeremoteproduct]
622 # Updates Product.remote_product using SourceForge project data.
623 type=user
624+groups=script
625 public.product = SELECT, UPDATE
626 public.bugtracker = SELECT
627-public.scriptactivity = SELECT, INSERT
628
629 [weblogstats]
630 # For the script that parses our Apache/Squid logfiles and updates statistics
631@@ -1552,6 +1551,25 @@
632 public.libraryfilealias = SELECT
633 public.libraryfiledownloadcount = SELECT, INSERT, UPDATE, DELETE
634
635+[garbo]
636+# garbo-hourly and garbo-daily script permissions. We define the
637+# permissions here in this group instead of in the users, so tasks can
638+# be shuffled around between the daily and hourly sections without
639+# changing DB permissions.
640+type=user
641+groups=script
642+public.codeimportresult = SELECT, DELETE
643+public.oauthnonce = SELECT, DELETE
644+public.openidnonce = SELECT, DELETE
645+
646+[garbo-daily]
647+type=user
648+groups=garbo
649+
650+[garbo-hourly]
651+type=user
652+groups=garbo
653+
654 [generateppahtaccess]
655 # For the generate_ppa_htaccess.py cronscript.
656 type=user
657
658=== modified file 'database/schema/security.py'
659--- database/schema/security.py 2009-03-23 19:32:14 +0000
660+++ database/schema/security.py 2009-03-25 11:41:03 +0000
661@@ -255,7 +255,8 @@
662 obj.fullname, quote_identifier(options.owner)
663 ))
664
665- # Revoke all privs
666+ # Revoke all privs from known groups. Don't revoke anything for
667+ # users or groups not defined in our security.cfg.
668 for section_name in config.sections():
669 for obj in schema.values():
670 if obj.type == 'function':
671@@ -263,13 +264,16 @@
672 else:
673 t = 'TABLE'
674
675- cur.execute('REVOKE ALL ON %s %s FROM %s%s' % (
676- t, obj.fullname, g, quote_identifier(section_name)
677- ))
678- if schema.has_key(obj.seqname):
679- cur.execute('REVOKE ALL ON SEQUENCE %s FROM %s%s' % (
680- obj.seqname, g, quote_identifier(section_name),
681- ))
682+ roles = [quote_identifier(section_name)]
683+ if section_name != 'public':
684+ roles.append(quote_identifier(section_name + '_ro'))
685+ for role in roles:
686+ cur.execute(
687+ 'REVOKE ALL ON %s %s FROM %s' % (t, obj.fullname, role))
688+ if schema.has_key(obj.seqname):
689+ cur.execute(
690+ 'REVOKE ALL ON SEQUENCE %s FROM %s'
691+ % (obj.seqname, role))
692
693 # Set of all tables we have granted permissions on. After we have assigned
694 # permissions, we can use this to determine what tables have been
695
696=== modified file 'importfascist.py'
697--- importfascist.py 2009-03-23 19:32:14 +0000
698+++ importfascist.py 2009-03-25 11:41:03 +0000
699@@ -21,20 +21,20 @@
700
701 # zope.testing.doctest: called as part of creating a DocTestSuite.
702 permitted_database_imports = text_lines_to_set("""
703- zope.testing.doctest
704- canonical.librarian.db
705- canonical.doap.fileimporter
706+ canonical.archivepublisher.deathrow
707+ canonical.archivepublisher.domination
708 canonical.archivepublisher.ftparchive
709 canonical.archivepublisher.publishing
710- canonical.archivepublisher.domination
711- canonical.archivepublisher.deathrow
712 canonical.codehosting.inmemory
713 canonical.launchpad.browser.branchlisting
714 canonical.launchpad.feed.branch
715+ canonical.launchpad.scripts.garbo
716+ canonical.launchpad.scripts.librarian_apache_log_parser
717+ canonical.launchpad.validators.person
718 canonical.launchpad.vocabularies.dbobjects
719- canonical.launchpad.validators.person
720 canonical.librarian.client
721- canonical.launchpad.scripts.librarian_apache_log_parser
722+ canonical.librarian.db
723+ zope.testing.doctest
724 """)
725 # It's not worth creating a *Set utility for ParsedApacheLog, to be used only
726 # in librarian_apache_log_parser, so instead we allow that module to import
727
728=== added file 'lib/canonical/launchpad/database/openidconsumer.py'
729--- lib/canonical/launchpad/database/openidconsumer.py 1970-01-01 00:00:00 +0000
730+++ lib/canonical/launchpad/database/openidconsumer.py 2009-03-25 11:41:03 +0000
731@@ -0,0 +1,23 @@
732+# Copyright 2009 Canonical Ltd. All rights reserved.
733+
734+"""OpenID Consumer related database classes."""
735+
736+__metaclass__ = type
737+__all__ = ['OpenIDNonce']
738+
739+from storm.locals import DateTime, Int, Storm, Unicode
740+
741+class OpenIDNonce(Storm):
742+ """An OpenIDNonce.
743+
744+ The table definition matches that required by the openid library,
745+ so doesn't follow our standards. In particular, it doesn't have an
746+ id column and the timestamp is an epoch time integer rather than a
747+ datetime.
748+ """
749+ __storm_table__ = "OpenIDNonce"
750+ __storm_primary__ = "server_url", "timestamp", "salt"
751+
752+ server_url = Unicode()
753+ timestamp = Int()
754+ salt = Unicode()
755
756=== modified file 'lib/canonical/launchpad/doc/script-monitoring.txt'
757--- lib/canonical/launchpad/doc/script-monitoring.txt 2008-12-01 18:05:42 +0000
758+++ lib/canonical/launchpad/doc/script-monitoring.txt 2009-03-25 11:41:03 +0000
759@@ -22,12 +22,13 @@
760 >>> import sys
761 >>> import tempfile
762 >>> import pytz
763+ >>> import transaction
764 >>> from zope.component import getUtility
765- >>> from canonical.database.sqlbase import ZopelessTransactionManager
766 >>> from canonical.launchpad.interfaces import IScriptActivitySet
767+ >>> from canonical.testing.layers import LaunchpadZopelessLayer
768
769 >>> UTC = pytz.timezone('UTC')
770- >>> ztm = ZopelessTransactionManager._installed
771+ >>> LaunchpadZopelessLayer.switchDbUser('garbo-daily') # A script db user
772
773 >>> activity = getUtility(IScriptActivitySet).recordSuccess(
774 ... name='script-name',
775@@ -88,21 +89,21 @@
776 ... raise RuntimeError('Some failure')
777 ...
778 ... if __name__ == '__main__':
779- ... script = TestScript('test-script')
780+ ... script = TestScript('test-script', 'garbo-daily')
781 ... script.run()
782 ... """)
783 >>> script_file.flush()
784
785 We'll now run this script, telling it to fail:
786
787- >>> ztm.commit()
788+ >>> transaction.commit()
789 >>> env = dict(os.environ)
790 >>> env['LPCONFIG'] = 'testrunner'
791 >>> proc = subprocess.Popen([sys.executable, script_file.name, 'fail'],
792 ... env=env, stdin=subprocess.PIPE,
793 ... stdout=subprocess.PIPE, stderr=subprocess.PIPE)
794 >>> (out, err) = proc.communicate()
795- >>> ztm.abort()
796+ >>> transaction.abort()
797
798 The process failed:
799
800@@ -120,7 +121,7 @@
801 ... env=env, stdin=subprocess.PIPE,
802 ... stdout=subprocess.PIPE, stderr=subprocess.PIPE)
803 >>> (out, err) = proc.communicate()
804- >>> ztm.abort()
805+ >>> transaction.abort()
806
807 >>> print proc.returncode
808 0
809
810=== modified file 'lib/canonical/launchpad/scripts/base.py'
811--- lib/canonical/launchpad/scripts/base.py 2008-10-21 11:30:50 +0000
812+++ lib/canonical/launchpad/scripts/base.py 2009-03-25 11:41:03 +0000
813@@ -70,7 +70,8 @@
814 What you get:
815 - self.logger
816 - self.txn
817- - self.options
818+ - self.parser (the OptionParser)
819+ - self.options (the parsed options)
820
821 "Give me convenience or give me death."
822 """
823
824=== added file 'lib/canonical/launchpad/scripts/garbo.py'
825--- lib/canonical/launchpad/scripts/garbo.py 1970-01-01 00:00:00 +0000
826+++ lib/canonical/launchpad/scripts/garbo.py 2009-03-25 11:41:03 +0000
827@@ -0,0 +1,182 @@
828+# Copyright 2009 Canonical Ltd. All rights reserved.
829+
830+"""Database garbage collection."""
831+
832+__metaclass__ = type
833+__all__ = ['DailyDatabaseGarbageCollector', 'HourlyDatabaseGarbageCollector']
834+
835+import time
836+
837+import transaction
838+from zope.component import getUtility
839+from zope.interface import implements
840+from storm.locals import SQL, Max, Min
841+
842+from canonical.database.sqlbase import sqlvalues
843+from canonical.launchpad.database.codeimportresult import CodeImportResult
844+from canonical.launchpad.database.oauth import OAuthNonce
845+from canonical.launchpad.database.openidconsumer import OpenIDNonce
846+from canonical.launchpad.interfaces import IMasterStore
847+from canonical.launchpad.interfaces.looptuner import ITunableLoop
848+from canonical.launchpad.scripts.base import LaunchpadCronScript
849+from canonical.launchpad.utilities.looptuner import LoopTuner
850+from canonical.launchpad.webapp.interfaces import (
851+ IStoreSelector, MAIN_STORE, MASTER_FLAVOR)
852+
853+
854+ONE_DAY_IN_SECONDS = 24*60*60
855+
856+
857+class TunableLoop:
858+ implements(ITunableLoop)
859+
860+ goal_seconds = 4
861+ minimum_chunk_size = 1
862+ maximum_chunk_size = None # Override
863+ cooldown_time = 0
864+
865+ def run(self):
866+ assert self.maximum_chunk_size is not None, "Did not override."
867+ LoopTuner(
868+ self, self.goal_seconds,
869+ minimum_chunk_size = self.minimum_chunk_size,
870+ maximum_chunk_size = self.maximum_chunk_size,
871+ cooldown_time = self.cooldown_time).run()
872+
873+
874+class OAuthNoncePruner(TunableLoop):
875+ """An ITunableLoop to prune old OAuthNonce records.
876+
877+ We remove all OAuthNonce records older than 1 day.
878+ """
879+ maximum_chunk_size = 6*60*60 # 6 hours in seconds.
880+
881+ def __init__(self):
882+ self.store = IMasterStore(OAuthNonce)
883+ self.oldest_age = self.store.execute("""
884+ SELECT COALESCE(EXTRACT(EPOCH FROM
885+ CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
886+ - MIN(request_timestamp)), 0)
887+ FROM OAuthNonce
888+ """).get_one()[0]
889+
890+ def isDone(self):
891+ return self.oldest_age <= ONE_DAY_IN_SECONDS
892+
893+ def __call__(self, chunk_size):
894+ self.oldest_age = max(ONE_DAY_IN_SECONDS, self.oldest_age - chunk_size)
895+
896+ self.store.find(
897+ OAuthNonce,
898+ OAuthNonce.request_timestamp < SQL(
899+ "CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - interval '%d seconds'"
900+ % self.oldest_age)).remove()
901+ transaction.commit()
902+
903+
904+class OpenIDNoncePruner(TunableLoop):
905+ """An ITunableLoop to prune old OpenIDNonce records.
906+
907+ We remove all OpenIDNonce records older than 1 day.
908+ """
909+ maximum_chunk_size = 6*60*60 # 6 hours in seconds.
910+
911+ def __init__(self):
912+ self.store = getUtility(IStoreSelector).get(MAIN_STORE, MASTER_FLAVOR)
913+ self.earliest_timestamp = self.store.find(
914+ Min(OpenIDNonce.timestamp)).one()
915+ utc_now = int(time.mktime(time.gmtime()))
916+ self.earliest_wanted_timestamp = utc_now - ONE_DAY_IN_SECONDS
917+
918+ def isDone(self):
919+ return (
920+ self.earliest_timestamp is None
921+ or self.earliest_timestamp >= self.earliest_wanted_timestamp)
922+
923+ def __call__(self, chunk_size):
924+ self.earliest_timestamp = min(
925+ self.earliest_wanted_timestamp,
926+ self.earliest_timestamp + chunk_size)
927+
928+ self.store.find(
929+ OpenIDNonce,
930+ OpenIDNonce.timestamp < self.earliest_timestamp).remove()
931+ transaction.commit()
932+
933+
934+class CodeImportResultPruner(TunableLoop):
935+ """A TunableLoop to prune unwanted CodeImportResult rows.
936+
937+ Removes CodeImportResult rows if they are older than 30 days
938+ and they are not one of the 4 most recent results for that
939+ CodeImport.
940+ """
941+ maximum_chunk_size = 100
942+ def __init__(self):
943+ self.store = IMasterStore(CodeImportResult)
944+
945+ self.min_code_import = self.store.find(
946+ Min(CodeImportResult.code_importID)).one()
947+ self.max_code_import = self.store.find(
948+ Max(CodeImportResult.code_importID)).one()
949+
950+ self.next_code_import_id = self.min_code_import
951+
952+ def isDone(self):
953+ return (
954+ self.min_code_import is None
955+ or self.next_code_import_id > self.max_code_import)
956+
957+ def __call__(self, chunk_size):
958+ self.store.execute("""
959+ DELETE FROM CodeImportResult
960+ WHERE
961+ CodeImportResult.date_created
962+ < CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
963+ - interval '30 days'
964+ AND CodeImportResult.code_import >= %s
965+ AND CodeImportResult.code_import < %s + %s
966+ AND CodeImportResult.id NOT IN (
967+ SELECT LatestResult.id
968+ FROM CodeImportResult AS LatestResult
969+ WHERE
970+ LatestResult.code_import
971+ = CodeImportResult.code_import
972+ ORDER BY LatestResult.date_created DESC
973+ LIMIT 4)
974+ """ % sqlvalues(
975+ self.next_code_import_id,
976+ self.next_code_import_id,
977+ chunk_size))
978+ self.next_code_import_id += chunk_size
979+ transaction.commit()
980+
981+
982+class BaseDatabaseGarbageCollector(LaunchpadCronScript):
983+ """Abstract base class to run a collection of TunableLoops."""
984+ script_name = None # Script name for locking and database user. Override.
985+ tunable_loops = None # Collection of TunableLoops. Override.
986+
987+ def __init__(self, test_args=None):
988+ super(BaseDatabaseGarbageCollector, self).__init__(
989+ self.script_name, dbuser=self.script_name, test_args=test_args)
990+
991+ def main(self):
992+ for tunable_loop in self.tunable_loops:
993+ self.logger.info("Running %s" % tunable_loop.__name__)
994+ tunable_loop().run()
995+
996+
997+class HourlyDatabaseGarbageCollector(BaseDatabaseGarbageCollector):
998+ script_name = 'garbo-hourly'
999+ tunable_loops = [
1000+ OAuthNoncePruner,
1001+ OpenIDNoncePruner,
1002+ ]
1003+
1004+class DailyDatabaseGarbageCollector(BaseDatabaseGarbageCollector):
1005+ script_name = 'garbo-daily'
1006+ tunable_loops = [
1007+ CodeImportResultPruner,
1008+ ]
1009+
1010
1011=== added file 'lib/canonical/launchpad/scripts/tests/test_garbo.py'
1012--- lib/canonical/launchpad/scripts/tests/test_garbo.py 1970-01-01 00:00:00 +0000
1013+++ lib/canonical/launchpad/scripts/tests/test_garbo.py 2009-03-25 11:41:03 +0000
1014@@ -0,0 +1,192 @@
1015+# Copyright 2009 Canonical Ltd. All rights reserved.
1016+
1017+"""Test the database garbage collector."""
1018+
1019+__metaclass__ = type
1020+__all__ = []
1021+
1022+from datetime import datetime, timedelta
1023+import time
1024+import unittest
1025+
1026+from pytz import UTC
1027+from storm.locals import Min
1028+import transaction
1029+
1030+from canonical.launchpad.database.codeimportresult import CodeImportResult
1031+from canonical.launchpad.database.oauth import OAuthNonce
1032+from canonical.launchpad.database.openidconsumer import OpenIDNonce
1033+from canonical.launchpad.interfaces import IMasterStore
1034+from canonical.launchpad.interfaces.codeimportresult import (
1035+ CodeImportResultStatus)
1036+from canonical.launchpad.testing import TestCase
1037+from canonical.launchpad.scripts.garbo import (
1038+ DailyDatabaseGarbageCollector, HourlyDatabaseGarbageCollector)
1039+from canonical.launchpad.scripts.tests import run_script
1040+from canonical.launchpad.scripts.logger import QuietFakeLogger
1041+from canonical.testing.layers import (
1042+ DatabaseLayer, LaunchpadScriptLayer, LaunchpadZopelessLayer)
1043+
1044+
1045+class TestGarboScript(TestCase):
1046+ layer = LaunchpadScriptLayer
1047+
1048+ def test_daily_script(self):
1049+ """Ensure garbo-daily.py actually runs."""
1050+ rv, out, err = run_script(
1051+ "cronscripts/garbo-daily.py", ["-q"], expect_returncode=0)
1052+ self.failIf(out.strip(), "Output to stdout: %s" % out)
1053+ self.failIf(err.strip(), "Output to stderr: %s" % err)
1054+ DatabaseLayer.force_dirty_database()
1055+
1056+ def test_hourly_script(self):
1057+ """Ensure garbo-hourly.py actually runs."""
1058+ rv, out, err = run_script(
1059+ "cronscripts/garbo-hourly.py", ["-q"], expect_returncode=0)
1060+ self.failIf(out.strip(), "Output to stdout: %s" % out)
1061+ self.failIf(err.strip(), "Output to stderr: %s" % err)
1062+
1063+
1064+class TestGarbo(TestCase):
1065+ layer = LaunchpadZopelessLayer
1066+
1067+ def setUp(self):
1068+ super(TestGarbo, self).setUp()
1069+ # Run the garbage collectors to remove any existing garbage,
1070+ # starting us in a known state.
1071+ self.runDaily()
1072+ self.runHourly()
1073+
1074+ def runDaily(self):
1075+ LaunchpadZopelessLayer.switchDbUser('garbo-daily')
1076+ collector = DailyDatabaseGarbageCollector(test_args=[])
1077+ collector.logger = QuietFakeLogger()
1078+ collector.main()
1079+
1080+ def runHourly(self):
1081+ LaunchpadZopelessLayer.switchDbUser('garbo-hourly')
1082+ collector = HourlyDatabaseGarbageCollector(test_args=[])
1083+ collector.logger = QuietFakeLogger()
1084+ collector.main()
1085+
1086+ def test_OAuthNoncePruner(self):
1087+ store = IMasterStore(OAuthNonce)
1088+ now = datetime.utcnow().replace(tzinfo=UTC)
1089+ timestamps = [
1090+ now - timedelta(days=2), # Garbage
1091+ now - timedelta(days=1) - timedelta(seconds=60), # Garbage
1092+ now - timedelta(days=1) + timedelta(seconds=60), # Not garbage
1093+ now, # Not garbage
1094+ ]
1095+ LaunchpadZopelessLayer.switchDbUser('testadmin')
1096+
1097+ # Make sure we start with 0 nonces.
1098+ self.failUnlessEqual(store.find(OAuthNonce).count(), 0)
1099+
1100+ for timestamp in timestamps:
1101+ OAuthNonce(
1102+ access_tokenID=1,
1103+ request_timestamp = timestamp,
1104+ nonce = str(timestamp))
1105+ transaction.commit()
1106+
1107+ # Make sure we have 4 nonces now.
1108+ self.failUnlessEqual(store.find(OAuthNonce).count(), 4)
1109+
1110+ self.runHourly()
1111+
1112+ # Now back to two, having removed the two garbage entries.
1113+ self.failUnlessEqual(store.find(OAuthNonce).count(), 2)
1114+
1115+ # And none of them are older than a day.
1116+ # Hmm... why is it I'm putting tz aware datetimes in and getting
1117+ # naive datetimes back? Bug in the SQLObject compatibility layer?
1118+ # Test is still fine as we know the timezone.
1119+ self.failUnless(
1120+ store.find(
1121+ Min(OAuthNonce.request_timestamp)).one().replace(tzinfo=UTC)
1122+ >= now - timedelta(days=1))
1123+
1124+ def test_OpenIDNoncePruner(self):
1125+ now = int(time.mktime(time.gmtime()))
1126+ MINUTES = 60
1127+ HOURS = 60 * 60
1128+ DAYS = 24 * HOURS
1129+ timestamps = [
1130+ now - 2 * DAYS, # Garbage
1131+ now - 1 * DAYS - 1 * MINUTES, # Garbage
1132+ now - 1 * DAYS + 1 * MINUTES, # Not garbage
1133+ now, # Not garbage
1134+ ]
1135+ LaunchpadZopelessLayer.switchDbUser('testadmin')
1136+
1137+ store = IMasterStore(OpenIDNonce)
1138+
1139+ # Make sure we start with 0 nonces.
1140+ self.failUnlessEqual(store.find(OpenIDNonce).count(), 0)
1141+
1142+ for timestamp in timestamps:
1143+ nonce = store.add(OpenIDNonce())
1144+ nonce.server_url = unicode(timestamp)
1145+ nonce.timestamp = timestamp
1146+ nonce.salt = u'aa'
1147+ store.add(nonce)
1148+ transaction.commit()
1149+
1150+ # Make sure we have 4 nonces now.
1151+ self.failUnlessEqual(store.find(OpenIDNonce).count(), 4)
1152+
1153+ # Run the garbage collector.
1154+ self.runHourly()
1155+
1156+ # We should now have 2 nonces.
1157+ self.failUnlessEqual(store.find(OpenIDNonce).count(), 2)
1158+
1159+ # And none of them are older than 1 day
1160+ earliest = store.find(Min(OpenIDNonce.timestamp)).one()
1161+ self.failUnless(earliest >= now - 24*60*60, 'Still have old nonces')
1162+
1163+ def test_CodeImportResultPruner(self):
1164+ now = datetime.utcnow().replace(tzinfo=UTC)
1165+ store = IMasterStore(CodeImportResult)
1166+
1167+ def new_code_import_result(timestamp):
1168+ LaunchpadZopelessLayer.switchDbUser('testadmin')
1169+ CodeImportResult(
1170+ date_created=timestamp,
1171+ code_importID=1, machineID=1, requesting_userID=1,
1172+ status=CodeImportResultStatus.FAILURE,
1173+ date_job_started=timestamp)
1174+ transaction.commit()
1175+
1176+ new_code_import_result(now - timedelta(days=60))
1177+ new_code_import_result(now - timedelta(days=19))
1178+ new_code_import_result(now - timedelta(days=20))
1179+ new_code_import_result(now - timedelta(days=21))
1180+
1181+ # Run the garbage collector
1182+ self.runDaily()
1183+
1184+ # Nothing is removed, because we always keep the 4 latest.
1185+ self.failUnlessEqual(
1186+ store.find(CodeImportResult).count(), 4)
1187+
1188+ new_code_import_result(now - timedelta(days=31))
1189+ self.runDaily()
1190+ self.failUnlessEqual(
1191+ store.find(CodeImportResult).count(), 4)
1192+
1193+ new_code_import_result(now - timedelta(days=29))
1194+ self.runDaily()
1195+ self.failUnlessEqual(
1196+ store.find(CodeImportResult).count(), 4)
1197+
1198+ # We now have no CodeImportResults older than 30 days
1199+ self.failUnless(
1200+ store.find(
1201+ Min(CodeImportResult.date_created)).one().replace(tzinfo=UTC)
1202+ >= now - timedelta(days=30))
1203+
1204+
1205+def test_suite():
1206+ return unittest.TestLoader().loadTestsFromName(__name__)

Subscribers

People subscribed via source and target branches

to status/vote changes: