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

[Stuart Bishop]

[Stuart Bishop (stub)]

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.

[Paul Hummer (rockstar)]

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

review: Approve (code)

=== added file 'cronscripts/garbo-daily.py'
--- cronscripts/garbo-daily.py	1970-01-01 00:00:00 +0000
+++ cronscripts/garbo-daily.py	2009-03-25 11:41:03 +0000
@@ -0,0 +1,19 @@
+#!/usr/bin/python2.4
+# Copyright 2009 Canonical Ltd.  All rights reserved.
+
+"""Database garbage collector.
+
+Remove or archive unwanted data. Detect, warn and possibly repair data
+corruption.
+"""
+
+__metaclass__ = type
+__all__ = []
+
+import _pythonpath
+from canonical.launchpad.scripts.garbo import DailyDatabaseGarbageCollector
+
+if __name__ == '__main__':
+    script = DailyDatabaseGarbageCollector()
+    script.lock_and_run()
+
 
=== added file 'cronscripts/garbo-hourly.py'
--- cronscripts/garbo-hourly.py	1970-01-01 00:00:00 +0000
+++ cronscripts/garbo-hourly.py	2009-03-25 11:41:03 +0000
@@ -0,0 +1,19 @@
+#!/usr/bin/python2.4
+# Copyright 2009 Canonical Ltd.  All rights reserved.
+
+"""Database garbage collector.
+
+Remove or archive unwanted data. Detect, warn and possibly repair data
+corruption.
+"""
+
+__metaclass__ = type
+__all__ = []
+
+import _pythonpath
+from canonical.launchpad.scripts.garbo import HourlyDatabaseGarbageCollector
+
+if __name__ == '__main__':
+    script = HourlyDatabaseGarbageCollector()
+    script.lock_and_run()
+
 
=== added file 'database/schema/patch-2109-42-0.sql'
--- database/schema/patch-2109-42-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2109-42-0.sql	2009-03-25 11:41:03 +0000
@@ -0,0 +1,8 @@
+SET client_min_messages=ERROR;
+
+ALTER TABLE OAuthNonce
+    ADD CONSTRAINT oauthnonce__access_token__fk
+        FOREIGN KEY (access_token) REFERENCES OAuthAccessToken;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2109, 42, 0);
+
 
=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2009-03-24 22:08:20 +0000
+++ database/schema/security.cfg	2009-03-25 11:41:03 +0000
@@ -95,7 +95,7 @@
 [launchpad_main]
 # lpmain replication set access from the main Z3 application.
 type=user
-groups=write
+groups=write,script
 public.account                          = SELECT
 public.announcement                     = SELECT, INSERT, UPDATE, DELETE
 public.answercontact                    = SELECT, INSERT, UPDATE, DELETE
@@ -232,7 +232,7 @@
 public.revisionauthor                   = SELECT, INSERT, UPDATE
 public.revisionnumber                   = SELECT, INSERT
 public.revisionparent                   = SELECT, INSERT
-public.scriptactivity                   = SELECT, INSERT
+public.scriptactivity                   = SELECT
 public.shipitreport                     = SELECT, INSERT
 public.shipitsurvey                     = SELECT, INSERT, UPDATE
 public.shipitsurveyquestion             = SELECT, INSERT
@@ -278,8 +278,14 @@
 type=user
 groups=launchpad_main
 
+[script]
+# Permissions required by all scripts.
+type=group
+public.scriptactivity                   = SELECT, INSERT
+
 [statistician]
 type=user
+groups=script
 public.archive                          = SELECT, UPDATE
 public.archivearch                      = SELECT, UPDATE
 public.binarypackagename                = SELECT
@@ -308,7 +314,6 @@
 public.product                          = SELECT
 public.productseries                    = SELECT
 public.question                         = SELECT
-public.scriptactivity                   = SELECT, INSERT
 public.sourcepackagename                = SELECT
 public.sourcepackagepublishinghistory   = SELECT
 public.sourcepackagerelease             = SELECT
@@ -329,6 +334,7 @@
 
 [librariangc]
 type=user
+groups=script
 public.libraryfilealias                 = SELECT, UPDATE, DELETE
 public.libraryfilecontent               = SELECT, UPDATE, DELETE
 # This user needs select on every table that references LibraryFileAlias
@@ -358,7 +364,6 @@
 public.product                          = SELECT
 public.productreleasefile               = SELECT
 public.project                          = SELECT
-public.scriptactivity                   = SELECT, INSERT
 public.shipitreport                     = SELECT
 public.shippingrun                      = SELECT
 public.sprint                           = SELECT
@@ -369,6 +374,7 @@
 [productreleasefinder]
 # Dyson release import script
 type=user
+groups=script
 public.product                          = SELECT
 public.productseries                    = SELECT
 public.productrelease                   = SELECT, INSERT, UPDATE
@@ -378,26 +384,24 @@
 # Needed to write to the librarian
 public.libraryfilealias                 = SELECT, INSERT
 public.libraryfilecontent               = SELECT, INSERT
-public.scriptactivity                   = SELECT, INSERT
 
 [pofilestats]
 # Translations POFile statistics verification/update script
 type=user
+groups=script
 public.language                         = SELECT
 public.pofile                           = SELECT, UPDATE
 public.potemplate                       = SELECT
 public.potmsgset                        = SELECT
-public.scriptactivity                   = SELECT, INSERT
 public.translationmessage               = SELECT
 public.translationtemplateitem          = SELECT
 
 [poimport]
 # Rosetta import script
 type=user
-groups=write
+groups=write,script
 public.account                          = SELECT, INSERT
 public.customlanguagecode               = SELECT
-public.scriptactivity                   = SELECT, INSERT
 public.translationgroup                 = SELECT
 public.translationimportqueueentry      = SELECT, DELETE
 public.translationmessage               = SELECT, INSERT, UPDATE
@@ -409,6 +413,7 @@
 [poexport]
 # Rosetta export script
 type=user
+groups=script
 public.distribution                     = SELECT
 public.distroseries                     = SELECT
 public.emailaddress                     = SELECT
@@ -427,7 +432,6 @@
 public.potranslation                    = SELECT
 public.product                          = SELECT
 public.productseries                    = SELECT
-public.scriptactivity                   = SELECT, INSERT
 public.sourcepackagename                = SELECT
 public.translationgroup                 = SELECT
 public.translationmessage               = SELECT
@@ -439,6 +443,7 @@
 [langpack]
 # Language pack exporter script
 type=user
+groups=script
 public.distribution                     = SELECT
 public.distroseries                     = SELECT, UPDATE
 public.emailaddress                     = SELECT
@@ -457,7 +462,6 @@
 public.potranslation                    = SELECT
 public.product                          = SELECT
 public.productseries                    = SELECT
-public.scriptactivity                   = SELECT, INSERT
 public.sourcepackagename                = SELECT
 public.translationgroup                 = SELECT
 public.translationmessage               = SELECT
@@ -469,6 +473,7 @@
 [checkwatches]
 # Malone bug watch script
 type=user
+groups=script
 public.account                          = SELECT, INSERT
 public.accountpassword                  = SELECT, INSERT
 public.answercontact                    = SELECT
@@ -506,7 +511,6 @@
 public.questionbug                      = SELECT
 public.question                         = SELECT
 public.questionsubscription             = SELECT
-public.scriptactivity                   = SELECT, INSERT
 public.sourcepackagename                = SELECT
 public.structuralsubscription           = SELECT
 public.teammembership                   = SELECT
@@ -532,7 +536,7 @@
 
 [branchscanner]
 type=user
-groups=write
+groups=write, script
 public.account                          = SELECT, INSERT
 public.accountpassword                  = SELECT, INSERT
 public.branch                           = SELECT, UPDATE
@@ -556,7 +560,6 @@
 public.revisionauthor                   = SELECT, INSERT, UPDATE
 public.revisionparent                   = SELECT, INSERT
 public.revisionproperty                 = SELECT, INSERT
-public.scriptactivity                   = SELECT, INSERT
 public.sourcepackagename                = SELECT
 public.staticdiff                       = SELECT, INSERT, DELETE
 public.validpersoncache                 = SELECT
@@ -564,6 +567,7 @@
 
 [targetnamecacheupdater]
 type=user
+groups=script
 public.bugtask                                  = SELECT, UPDATE
 public.product                                  = SELECT
 public.productseries                            = SELECT
@@ -572,10 +576,10 @@
 public.sourcepackagename                        = SELECT
 public.binarypackagename                        = SELECT
 public.potemplate                               = SELECT, UPDATE
-public.scriptactivity                           = SELECT, INSERT
 
 [distributionmirror]
 type=user
+groups=script
 public.archive                                  = SELECT
 public.archivearch                              = SELECT
 public.binarypackagefile                        = SELECT
@@ -597,7 +601,6 @@
 public.mirrorproberecord                        = SELECT, INSERT
 public.person                                   = SELECT
 public.processorfamily                          = SELECT
-public.scriptactivity                           = SELECT, INSERT
 public.securesourcepackagepublishinghistory     = SELECT
 public.securebinarypackagepublishinghistory     = SELECT
 public.sourcepackagerelease                     = SELECT
@@ -608,15 +611,16 @@
 [teammembership]
 # Update the TeamMembership table setting expired members
 type=user
+groups=script
 public.teammembership                           = SELECT, UPDATE
 public.teamparticipation                        = SELECT, DELETE
 public.person                                   = SELECT
 public.emailaddress                             = SELECT
-public.scriptactivity                           = SELECT, INSERT
 
 [karma]
 # Update the KarmaCache table
 type=user
+groups=script
 public.karmacache                               = SELECT, INSERT, UPDATE, DELETE
 public.karma                                    = SELECT
 public.karmacategory                            = SELECT
@@ -627,11 +631,11 @@
 public.product                                  = SELECT
 public.validpersoncache                         = SELECT
 public.validpersonorteamcache                   = SELECT
-public.scriptactivity                           = SELECT, INSERT
 
 [revisionkarma]
 # Allocate karma for revisions.
 type=user
+groups=script
 public.branch                                   = SELECT
 public.branchrevision                           = SELECT
 public.karma                                    = SELECT, INSERT
@@ -642,20 +646,19 @@
 public.productseries                            = SELECT
 public.revision                                 = SELECT, UPDATE
 public.revisionauthor                           = SELECT
-public.scriptactivity                           = SELECT, INSERT
 public.validpersoncache                         = SELECT
 
 [cve]
 type=user
+groups=script
 public.cve                                      = SELECT, INSERT, UPDATE
 public.cvereference                             = SELECT, INSERT, UPDATE, DELETE
-public.scriptactivity                           = SELECT, INSERT
 
 
 [gina]
 # Unpack sourcepackages and extract metadata
 type=user
-groups=write
+groups=write,script
 public.account                                  = SELECT, INSERT
 public.accountpassword                          = SELECT, INSERT
 public.archive                                  = SELECT, UPDATE
@@ -663,14 +666,13 @@
 public.distribution                             = SELECT
 public.openidrpsummary                          = SELECT
 public.packagediff                              = SELECT, INSERT, UPDATE
-public.scriptactivity                           = SELECT, INSERT
 public.securebinarypackagepublishinghistory     = SELECT, INSERT, UPDATE, DELETE
 public.securesourcepackagepublishinghistory     = SELECT, INSERT, UPDATE, DELETE
 
 [lucille]
 # Soyuz archive publisher.
 type=user
-groups=write
+groups=write,script
 public.archive                                  = SELECT, UPDATE
 public.archivearch                              = SELECT
 public.archiveauthtoken                         = SELECT, UPDATE
@@ -679,7 +681,6 @@
 public.gpgkey                                   = SELECT, INSERT, UPDATE
 public.packagecopyrequest                       = SELECT, INSERT, UPDATE
 public.packagediff                              = SELECT, INSERT, UPDATE
-public.scriptactivity                           = SELECT, INSERT
 public.securebinarypackagepublishinghistory     = SELECT, INSERT, UPDATE, DELETE
 public.securesourcepackagepublishinghistory     = SELECT, INSERT, UPDATE, DELETE
 public.sourcepackagepublishinghistory           = SELECT
@@ -719,6 +720,7 @@
 
 [fiera]
 type=user
+groups=script
 public.account                                  = SELECT
 public.archive                                  = SELECT, UPDATE
 public.archivearch                              = SELECT, UPDATE
@@ -750,11 +752,11 @@
 public.person                                   = SELECT
 public.emailaddress                             = SELECT
 public.teammembership                           = SELECT
-public.scriptactivity                           = SELECT, INSERT
 public.gpgkey                                   = SELECT
 
 [sourcerer]
 type=user
+groups=script
 public.archive                                  = SELECT
 public.archivearch                              = SELECT
 public.branch                                   = SELECT, INSERT, UPDATE
@@ -779,7 +781,6 @@
 # To get stuff from the librarian
 public.libraryfilealias                         = SELECT
 public.libraryfilecontent                       = SELECT
-public.scriptactivity                           = SELECT, INSERT
 
 [write]
 type=group
@@ -884,6 +885,7 @@
 
 [shipit]
 type=user
+groups=script
 public.account                          = SELECT
 public.continent                        = SELECT
 public.country                          = SELECT
@@ -893,7 +895,6 @@
 public.libraryfilecontent               = SELECT, INSERT
 public.person                           = SELECT
 public.requestedcds                     = SELECT, INSERT, UPDATE
-public.scriptactivity                   = SELECT, INSERT
 public.shipitreport                     = SELECT, INSERT
 public.shipment                         = SELECT, INSERT
 public.shippingrequest                  = SELECT, UPDATE
@@ -905,17 +906,18 @@
 [standingupdater]
 # For the personal standing updater cron script.
 type=user
+groups=script
 public.emailaddress                     = SELECT
 public.mailinglist                      = SELECT
 public.message                          = SELECT
 public.messageapproval                  = SELECT
 public.person                           = SELECT, UPDATE
-public.scriptactivity                   = SELECT, INSERT
 public.teamparticipation                = SELECT
 
 [answertracker]
 # User running expire-questions.py
 type=user
+groups=script
 public.account                          = SELECT, INSERT
 public.accountpassword                  = SELECT, INSERT
 public.answercontact                    = SELECT
@@ -935,7 +937,6 @@
 public.questionbug                      = SELECT
 public.questionmessage                  = SELECT, INSERT
 public.questionsubscription             = SELECT
-public.scriptactivity                   = SELECT, INSERT
 public.sourcepackagename                = SELECT
 public.teammembership                   = SELECT
 public.validpersoncache                 = SELECT
@@ -943,6 +944,7 @@
 
 [uploader]
 type=user
+groups=script
 # Everything is keyed off an archive
 public.archive                          = SELECT, INSERT, UPDATE
 public.archivearch                      = SELECT, INSERT, UPDATE
@@ -1002,8 +1004,6 @@
 public.packageuploadbuild               = SELECT, INSERT
 public.packageuploadcustom              = SELECT, INSERT
 
-public.scriptactivity                   = SELECT, INSERT
-
 # For premature source-only publication
 public.securesourcepackagepublishinghistory = SELECT, INSERT
 
@@ -1045,6 +1045,7 @@
 
 [queued]
 type=user
+groups=script
 # Announce handling
 public.account                          = SELECT, INSERT
 public.person                           = SELECT, INSERT
@@ -1097,8 +1098,6 @@
 # rosetta auto imports
 public.translationimportqueueentry      = SELECT, INSERT, UPDATE
 
-public.scriptactivity                   = SELECT, INSERT
-
 # Closing bugs.
 public.bug                              = SELECT, UPDATE
 public.bugactivity                      = SELECT, INSERT
@@ -1135,10 +1134,10 @@
 
 [ppad]
 type=user
+groups=script
 public.archive                          = SELECT
 public.archivearch                      = SELECT
 public.person                           = SELECT
-public.scriptactivity                   = SELECT, INSERT
 
 [session]
 # This user doesn't have access to any tables in the main launchpad
@@ -1155,6 +1154,7 @@
 #       send-bug-notifications.py needs them. They should be removed
 #       when bug 37456 is fixed.
 type=user
+groups=script
 public.account                          = SELECT
 public.archive                          = SELECT
 public.archivearch                      = SELECT
@@ -1187,7 +1187,6 @@
 public.message                          = SELECT, INSERT
 public.messagechunk                     = SELECT, INSERT
 public.milestone                        = SELECT
-public.scriptactivity                   = SELECT, INSERT
 public.structuralsubscription           = SELECT
 public.teammembership                   = SELECT
 public.teamparticipation                = SELECT
@@ -1196,6 +1195,7 @@
 
 [personnotification]
 type=user
+groups=script
 public.personnotification               = SELECT, UPDATE, DELETE
 public.person                           = SELECT
 public.emailaddress                     = SELECT
@@ -1203,7 +1203,6 @@
 public.libraryfilecontent               = SELECT
 public.message                          = SELECT
 public.messagechunk                     = SELECT
-public.scriptactivity                   = SELECT, INSERT
 public.teammembership                   = SELECT
 public.teamparticipation                = SELECT
 public.validpersoncache                 = SELECT
@@ -1211,6 +1210,7 @@
 
 [rosettaadmin]
 type=user
+groups=script
 public.customlanguagecode               = SELECT, INSERT, UPDATE, DELETE
 public.distribution                     = SELECT
 public.distroseries                     = SELECT
@@ -1225,7 +1225,6 @@
 public.potranslation                    = SELECT
 public.product                          = SELECT
 public.productseries                    = SELECT
-public.scriptactivity                   = SELECT, INSERT
 public.sourcepackagename                = SELECT
 public.translationimportqueueentry      = SELECT, INSERT, UPDATE, DELETE
 public.translationmessage               = SELECT, INSERT, UPDATE, DELETE
@@ -1234,12 +1233,12 @@
 
 [oopsprune]
 type=user
+groups=script
 public.bug                              = SELECT
 public.bugtask                          = SELECT
 public.message                          = SELECT
 public.messagechunk                     = SELECT
 public.question                         = SELECT
-public.scriptactivity                   = SELECT, INSERT
 
 [listteammembers]
 type=user
@@ -1260,7 +1259,7 @@
 
 [processmail]
 type=user
-public.scriptactivity                   = SELECT, INSERT
+groups=script
 
 # Incoming emails are stored in the librarian
 public.libraryfilealias                 = SELECT, INSERT
@@ -1361,10 +1360,10 @@
 [mlist-sync]
 # The mailing list sync user
 type=user
+groups=script
 public.mailinglist                      = SELECT
 public.person                           = SELECT
 public.emailaddress                     = SELECT, UPDATE
-public.scriptactivity                   = SELECT, INSERT
 
 [mlist-import]
 # The mailing list import user
@@ -1379,6 +1378,7 @@
 [hwdb-submission-processor]
 # The user that updates the HWDB with data from new submissions
 type=user
+groups=script
 public.person                           = SELECT
 public.hwdevicedriverlink               = SELECT, INSERT
 public.hwdevicenamevariant              = SELECT, INSERT
@@ -1397,7 +1397,6 @@
 public.hwvendorname                     = SELECT, INSERT
 public.libraryfilealias                 = SELECT
 public.libraryfilecontent               = SELECT
-public.scriptactivity                   = SELECT, INSERT
 public.teamparticipation                = SELECT
 
 [builddcontroller]
@@ -1409,6 +1408,7 @@
 [binaryfile-expire]
 # The user that expires binary files from the librarian.
 type=user
+groups=script
 public.archive                          = SELECT
 public.binarypackagefile                = SELECT
 public.binarypackagepublishinghistory   = SELECT
@@ -1417,10 +1417,10 @@
 public.person                           = SELECT
 public.libraryfilealias                 = SELECT, UPDATE
 public.securebinarypackagepublishinghistory = SELECT
-public.scriptactivity                   = SELECT, UPDATE, INSERT, DELETE
 
 [create-merge-proposals]
 type=user
+groups=script
 public.account                          = SELECT
 public.accountpassword                  = SELECT
 public.branch                           = SELECT, INSERT, UPDATE
@@ -1446,13 +1446,13 @@
 public.product                          = SELECT
 public.productseries                    = SELECT
 public.project                          = SELECT
-public.scriptactivity                   = SELECT, INSERT
 public.staticdiff                       = SELECT, INSERT
 public.teamparticipation                = SELECT
 public.validpersoncache                 = SELECT
 
 [mp-creation-job]
 type=user
+groups=script
 public.account                          = SELECT
 public.accountpassword                  = SELECT
 public.branch                           = SELECT
@@ -1475,7 +1475,6 @@
 public.person                           = SELECT
 public.product                          = SELECT
 public.productseries                    = SELECT
-public.scriptactivity                   = SELECT, INSERT
 public.staticdiff                       = SELECT, INSERT
 public.teammembership                   = SELECT
 public.teamparticipation                = SELECT
@@ -1483,6 +1482,7 @@
 
 [send-branch-mail]
 type=user
+groups=script
 public.account                          = SELECT
 public.accountpassword                  = SELECT
 public.branch                           = SELECT
@@ -1507,7 +1507,6 @@
 public.product                          = SELECT
 public.productseries                    = SELECT
 public.revision                         = SELECT
-public.scriptactivity                   = SELECT, INSERT
 public.staticdiff                       = SELECT, INSERT
 public.teammembership                   = SELECT
 public.teamparticipation                = SELECT
@@ -1516,6 +1515,7 @@
 [updateremoteproduct]
 # Updates Product.remote_product using bug watch information.
 type=user
+groups=script
 public.account                          = SELECT, INSERT, UPDATE
 public.person                           = SELECT, INSERT
 public.product                          = SELECT, INSERT, UPDATE
@@ -1537,14 +1537,13 @@
 public.bugsubscription                  = SELECT, INSERT
 public.bugmessage                       = SELECT, INSERT
 public.sourcepackagename                = SELECT
-public.scriptactivity                   = SELECT, INSERT
 
 [updatesourceforgeremoteproduct]
 # Updates Product.remote_product using SourceForge project data.
 type=user
+groups=script
 public.product                          = SELECT, UPDATE
 public.bugtracker                       = SELECT
-public.scriptactivity                   = SELECT, INSERT
 
 [weblogstats]
 # For the script that parses our Apache/Squid logfiles and updates statistics
@@ -1552,6 +1551,25 @@
 public.libraryfilealias                 = SELECT
 public.libraryfiledownloadcount         = SELECT, INSERT, UPDATE, DELETE
 
+[garbo]
+# garbo-hourly and garbo-daily script permissions. We define the
+# permissions here in this group instead of in the users, so tasks can
+# be shuffled around between the daily and hourly sections without
+# changing DB permissions.
+type=user
+groups=script
+public.codeimportresult                 = SELECT, DELETE
+public.oauthnonce                       = SELECT, DELETE
+public.openidnonce                      = SELECT, DELETE
+
+[garbo-daily]
+type=user
+groups=garbo
+
+[garbo-hourly]
+type=user
+groups=garbo
+
 [generateppahtaccess]
 # For the generate_ppa_htaccess.py cronscript.
 type=user
 
=== modified file 'database/schema/security.py'
--- database/schema/security.py	2009-03-23 19:32:14 +0000
+++ database/schema/security.py	2009-03-25 11:41:03 +0000
@@ -255,7 +255,8 @@
                 obj.fullname, quote_identifier(options.owner)
                 ))
 
-    # Revoke all privs
+    # Revoke all privs from known groups. Don't revoke anything for
+    # users or groups not defined in our security.cfg.
     for section_name in config.sections():
         for obj in schema.values():
             if obj.type == 'function':
@@ -263,13 +264,16 @@
             else:
                 t = 'TABLE'
 
-            cur.execute('REVOKE ALL ON %s %s FROM %s%s' % (
-                t, obj.fullname, g, quote_identifier(section_name)
-                ))
-            if schema.has_key(obj.seqname):
-                cur.execute('REVOKE ALL ON SEQUENCE %s FROM %s%s' % (
-                    obj.seqname, g, quote_identifier(section_name),
-                    ))
+            roles = [quote_identifier(section_name)]
+            if section_name != 'public':
+                roles.append(quote_identifier(section_name + '_ro'))
+            for role in roles:
+                cur.execute(
+                    'REVOKE ALL ON %s %s FROM %s' % (t, obj.fullname, role))
+                if schema.has_key(obj.seqname):
+                    cur.execute(
+                        'REVOKE ALL ON SEQUENCE %s FROM %s'
+                        % (obj.seqname, role))
 
     # Set of all tables we have granted permissions on. After we have assigned
     # permissions, we can use this to determine what tables have been
 
=== modified file 'importfascist.py'
--- importfascist.py	2009-03-23 19:32:14 +0000
+++ importfascist.py	2009-03-25 11:41:03 +0000
@@ -21,20 +21,20 @@
 
 # zope.testing.doctest: called as part of creating a DocTestSuite.
 permitted_database_imports = text_lines_to_set("""
-    zope.testing.doctest
-    canonical.librarian.db
-    canonical.doap.fileimporter
+    canonical.archivepublisher.deathrow
+    canonical.archivepublisher.domination
     canonical.archivepublisher.ftparchive
     canonical.archivepublisher.publishing
-    canonical.archivepublisher.domination
-    canonical.archivepublisher.deathrow
     canonical.codehosting.inmemory
     canonical.launchpad.browser.branchlisting
     canonical.launchpad.feed.branch
+    canonical.launchpad.scripts.garbo
+    canonical.launchpad.scripts.librarian_apache_log_parser
+    canonical.launchpad.validators.person
     canonical.launchpad.vocabularies.dbobjects
-    canonical.launchpad.validators.person
     canonical.librarian.client
-    canonical.launchpad.scripts.librarian_apache_log_parser
+    canonical.librarian.db
+    zope.testing.doctest
     """)
 # It's not worth creating a *Set utility for ParsedApacheLog, to be used only
 # in librarian_apache_log_parser, so instead we allow that module to import
 
=== added file 'lib/canonical/launchpad/database/openidconsumer.py'
--- lib/canonical/launchpad/database/openidconsumer.py	1970-01-01 00:00:00 +0000
+++ lib/canonical/launchpad/database/openidconsumer.py	2009-03-25 11:41:03 +0000
@@ -0,0 +1,23 @@
+# Copyright 2009 Canonical Ltd.  All rights reserved.
+
+"""OpenID Consumer related database classes."""
+
+__metaclass__ = type
+__all__ = ['OpenIDNonce']
+
+from storm.locals import DateTime, Int, Storm, Unicode
+
+class OpenIDNonce(Storm):
+    """An OpenIDNonce.
+
+    The table definition matches that required by the openid library,
+    so doesn't follow our standards. In particular, it doesn't have an
+    id column and the timestamp is an epoch time integer rather than a
+    datetime.
+    """
+    __storm_table__ = "OpenIDNonce"
+    __storm_primary__ = "server_url", "timestamp", "salt"
+
+    server_url = Unicode()
+    timestamp = Int()
+    salt = Unicode()
 
=== modified file 'lib/canonical/launchpad/doc/script-monitoring.txt'
--- lib/canonical/launchpad/doc/script-monitoring.txt	2008-12-01 18:05:42 +0000
+++ lib/canonical/launchpad/doc/script-monitoring.txt	2009-03-25 11:41:03 +0000
@@ -22,12 +22,13 @@
   >>> import sys
   >>> import tempfile
   >>> import pytz
+  >>> import transaction
   >>> from zope.component import getUtility
-  >>> from canonical.database.sqlbase import ZopelessTransactionManager
   >>> from canonical.launchpad.interfaces import IScriptActivitySet
+  >>> from canonical.testing.layers import LaunchpadZopelessLayer
 
   >>> UTC = pytz.timezone('UTC')
-  >>> ztm = ZopelessTransactionManager._installed
+  >>> LaunchpadZopelessLayer.switchDbUser('garbo-daily') # A script db user
 
   >>> activity = getUtility(IScriptActivitySet).recordSuccess(
   ...     name='script-name',
@@ -88,21 +89,21 @@
   ...             raise RuntimeError('Some failure')
   ...
   ... if __name__ == '__main__':
-  ...     script = TestScript('test-script')
+  ...     script = TestScript('test-script', 'garbo-daily')
   ...     script.run()
   ... """)
   >>> script_file.flush()
 
 We'll now run this script, telling it to fail:
 
-  >>> ztm.commit()
+  >>> transaction.commit()
   >>> env = dict(os.environ)
   >>> env['LPCONFIG'] = 'testrunner'
   >>> proc = subprocess.Popen([sys.executable, script_file.name, 'fail'],
   ...                         env=env, stdin=subprocess.PIPE,
   ...                         stdout=subprocess.PIPE, stderr=subprocess.PIPE)
   >>> (out, err) = proc.communicate()
-  >>> ztm.abort()
+  >>> transaction.abort()
 
 The process failed:
 
@@ -120,7 +121,7 @@
   ...                         env=env, stdin=subprocess.PIPE,
   ...                         stdout=subprocess.PIPE, stderr=subprocess.PIPE)
   >>> (out, err) = proc.communicate()
-  >>> ztm.abort()
+  >>> transaction.abort()
 
   >>> print proc.returncode
   0
 
=== modified file 'lib/canonical/launchpad/scripts/base.py'
--- lib/canonical/launchpad/scripts/base.py	2008-10-21 11:30:50 +0000
+++ lib/canonical/launchpad/scripts/base.py	2009-03-25 11:41:03 +0000
@@ -70,7 +70,8 @@
     What you get:
         - self.logger
         - self.txn
-        - self.options
+        - self.parser (the OptionParser)
+        - self.options (the parsed options)
 
     "Give me convenience or give me death."
     """
 
=== added file 'lib/canonical/launchpad/scripts/garbo.py'
--- lib/canonical/launchpad/scripts/garbo.py	1970-01-01 00:00:00 +0000
+++ lib/canonical/launchpad/scripts/garbo.py	2009-03-25 11:41:03 +0000
@@ -0,0 +1,182 @@
+# Copyright 2009 Canonical Ltd.  All rights reserved.
+
+"""Database garbage collection."""
+
+__metaclass__ = type
+__all__ = ['DailyDatabaseGarbageCollector', 'HourlyDatabaseGarbageCollector']
+
+import time
+
+import transaction
+from zope.component import getUtility
+from zope.interface import implements
+from storm.locals import SQL, Max, Min
+
+from canonical.database.sqlbase import sqlvalues
+from canonical.launchpad.database.codeimportresult import CodeImportResult
+from canonical.launchpad.database.oauth import OAuthNonce
+from canonical.launchpad.database.openidconsumer import OpenIDNonce
+from canonical.launchpad.interfaces import IMasterStore
+from canonical.launchpad.interfaces.looptuner import ITunableLoop
+from canonical.launchpad.scripts.base import LaunchpadCronScript
+from canonical.launchpad.utilities.looptuner import LoopTuner
+from canonical.launchpad.webapp.interfaces import (
+    IStoreSelector, MAIN_STORE, MASTER_FLAVOR)
+
+
+ONE_DAY_IN_SECONDS = 24*60*60
+
+
+class TunableLoop:
+    implements(ITunableLoop)
+
+    goal_seconds = 4
+    minimum_chunk_size = 1
+    maximum_chunk_size = None # Override
+    cooldown_time = 0
+
+    def run(self):
+        assert self.maximum_chunk_size is not None, "Did not override."
+        LoopTuner(
+            self, self.goal_seconds,
+            minimum_chunk_size = self.minimum_chunk_size,
+            maximum_chunk_size = self.maximum_chunk_size,
+            cooldown_time = self.cooldown_time).run()
+
+
+class OAuthNoncePruner(TunableLoop):
+    """An ITunableLoop to prune old OAuthNonce records.
+
+    We remove all OAuthNonce records older than 1 day.
+    """
+    maximum_chunk_size = 6*60*60 # 6 hours in seconds.
+
+    def __init__(self):
+        self.store = IMasterStore(OAuthNonce)
+        self.oldest_age = self.store.execute("""
+            SELECT COALESCE(EXTRACT(EPOCH FROM
+                CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
+                - MIN(request_timestamp)), 0)
+            FROM OAuthNonce
+            """).get_one()[0]
+
+    def isDone(self):
+        return self.oldest_age <= ONE_DAY_IN_SECONDS
+
+    def __call__(self, chunk_size):
+        self.oldest_age = max(ONE_DAY_IN_SECONDS, self.oldest_age - chunk_size)
+
+        self.store.find(
+            OAuthNonce,
+            OAuthNonce.request_timestamp < SQL(
+                "CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - interval '%d seconds'"
+                % self.oldest_age)).remove()
+        transaction.commit()
+
+
+class OpenIDNoncePruner(TunableLoop):
+    """An ITunableLoop to prune old OpenIDNonce records.
+
+    We remove all OpenIDNonce records older than 1 day.
+    """
+    maximum_chunk_size = 6*60*60 # 6 hours in seconds.
+
+    def __init__(self):
+        self.store = getUtility(IStoreSelector).get(MAIN_STORE, MASTER_FLAVOR)
+        self.earliest_timestamp = self.store.find(
+            Min(OpenIDNonce.timestamp)).one()
+        utc_now = int(time.mktime(time.gmtime()))
+        self.earliest_wanted_timestamp = utc_now - ONE_DAY_IN_SECONDS
+
+    def isDone(self):
+        return (
+            self.earliest_timestamp is None
+            or self.earliest_timestamp >= self.earliest_wanted_timestamp)
+
+    def __call__(self, chunk_size):
+        self.earliest_timestamp = min(
+            self.earliest_wanted_timestamp,
+            self.earliest_timestamp + chunk_size)
+
+        self.store.find(
+            OpenIDNonce,
+            OpenIDNonce.timestamp < self.earliest_timestamp).remove()
+        transaction.commit()
+
+
+class CodeImportResultPruner(TunableLoop):
+    """A TunableLoop to prune unwanted CodeImportResult rows.
+
+    Removes CodeImportResult rows if they are older than 30 days
+    and they are not one of the 4 most recent results for that
+    CodeImport.
+    """
+    maximum_chunk_size = 100
+    def __init__(self):
+        self.store = IMasterStore(CodeImportResult)
+
+        self.min_code_import = self.store.find(
+            Min(CodeImportResult.code_importID)).one()
+        self.max_code_import = self.store.find(
+            Max(CodeImportResult.code_importID)).one()
+
+        self.next_code_import_id = self.min_code_import
+
+    def isDone(self):
+        return (
+            self.min_code_import is None
+            or self.next_code_import_id > self.max_code_import)
+
+    def __call__(self, chunk_size):
+        self.store.execute("""
+            DELETE FROM CodeImportResult
+            WHERE
+                CodeImportResult.date_created
+                    < CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
+                        - interval '30 days'
+                AND CodeImportResult.code_import >= %s
+                AND CodeImportResult.code_import < %s + %s
+                AND CodeImportResult.id NOT IN (
+                    SELECT LatestResult.id
+                    FROM CodeImportResult AS LatestResult
+                    WHERE
+                        LatestResult.code_import
+                            = CodeImportResult.code_import
+                    ORDER BY LatestResult.date_created DESC
+                    LIMIT 4)
+            """ % sqlvalues(
+                self.next_code_import_id,
+                self.next_code_import_id,
+                chunk_size))
+        self.next_code_import_id += chunk_size
+        transaction.commit()
+
+
+class BaseDatabaseGarbageCollector(LaunchpadCronScript):
+    """Abstract base class to run a collection of TunableLoops."""
+    script_name = None # Script name for locking and database user. Override.
+    tunable_loops = None # Collection of TunableLoops. Override.
+
+    def __init__(self, test_args=None):
+        super(BaseDatabaseGarbageCollector, self).__init__(
+            self.script_name, dbuser=self.script_name, test_args=test_args)
+
+    def main(self):
+        for tunable_loop in self.tunable_loops:
+            self.logger.info("Running %s" % tunable_loop.__name__)
+            tunable_loop().run()
+
+
+class HourlyDatabaseGarbageCollector(BaseDatabaseGarbageCollector):
+    script_name = 'garbo-hourly'
+    tunable_loops = [
+        OAuthNoncePruner,
+        OpenIDNoncePruner,
+        ]
+
+class DailyDatabaseGarbageCollector(BaseDatabaseGarbageCollector):
+    script_name = 'garbo-daily'
+    tunable_loops = [
+        CodeImportResultPruner,
+        ]
+
 
=== added file 'lib/canonical/launchpad/scripts/tests/test_garbo.py'
--- lib/canonical/launchpad/scripts/tests/test_garbo.py	1970-01-01 00:00:00 +0000
+++ lib/canonical/launchpad/scripts/tests/test_garbo.py	2009-03-25 11:41:03 +0000
@@ -0,0 +1,192 @@
+# Copyright 2009 Canonical Ltd.  All rights reserved.
+
+"""Test the database garbage collector."""
+
+__metaclass__ = type
+__all__ = []
+
+from datetime import datetime, timedelta
+import time
+import unittest
+
+from pytz import UTC
+from storm.locals import Min
+import transaction
+
+from canonical.launchpad.database.codeimportresult import CodeImportResult
+from canonical.launchpad.database.oauth import OAuthNonce
+from canonical.launchpad.database.openidconsumer import OpenIDNonce
+from canonical.launchpad.interfaces import IMasterStore
+from canonical.launchpad.interfaces.codeimportresult import (
+    CodeImportResultStatus)
+from canonical.launchpad.testing import TestCase
+from canonical.launchpad.scripts.garbo import (
+    DailyDatabaseGarbageCollector, HourlyDatabaseGarbageCollector)
+from canonical.launchpad.scripts.tests import run_script
+from canonical.launchpad.scripts.logger import QuietFakeLogger
+from canonical.testing.layers import (
+    DatabaseLayer, LaunchpadScriptLayer, LaunchpadZopelessLayer)
+
+
+class TestGarboScript(TestCase):
+    layer = LaunchpadScriptLayer
+
+    def test_daily_script(self):
+        """Ensure garbo-daily.py actually runs."""
+        rv, out, err = run_script(
+            "cronscripts/garbo-daily.py", ["-q"], expect_returncode=0)
+        self.failIf(out.strip(), "Output to stdout: %s" % out)
+        self.failIf(err.strip(), "Output to stderr: %s" % err)
+        DatabaseLayer.force_dirty_database()
+
+    def test_hourly_script(self):
+        """Ensure garbo-hourly.py actually runs."""
+        rv, out, err = run_script(
+            "cronscripts/garbo-hourly.py", ["-q"], expect_returncode=0)
+        self.failIf(out.strip(), "Output to stdout: %s" % out)
+        self.failIf(err.strip(), "Output to stderr: %s" % err)
+
+
+class TestGarbo(TestCase):
+    layer = LaunchpadZopelessLayer
+
+    def setUp(self):
+        super(TestGarbo, self).setUp()
+        # Run the garbage collectors to remove any existing garbage,
+        # starting us in a known state.
+        self.runDaily()
+        self.runHourly()
+
+    def runDaily(self):
+        LaunchpadZopelessLayer.switchDbUser('garbo-daily')
+        collector = DailyDatabaseGarbageCollector(test_args=[])
+        collector.logger = QuietFakeLogger()
+        collector.main()
+
+    def runHourly(self):
+        LaunchpadZopelessLayer.switchDbUser('garbo-hourly')
+        collector = HourlyDatabaseGarbageCollector(test_args=[])
+        collector.logger = QuietFakeLogger()
+        collector.main()
+
+    def test_OAuthNoncePruner(self):
+        store = IMasterStore(OAuthNonce)
+        now = datetime.utcnow().replace(tzinfo=UTC)
+        timestamps = [
+            now - timedelta(days=2), # Garbage
+            now - timedelta(days=1) - timedelta(seconds=60), # Garbage
+            now - timedelta(days=1) + timedelta(seconds=60), # Not garbage
+            now, # Not garbage
+            ]
+        LaunchpadZopelessLayer.switchDbUser('testadmin')
+
+        # Make sure we start with 0 nonces.
+        self.failUnlessEqual(store.find(OAuthNonce).count(), 0)
+
+        for timestamp in timestamps:
+            OAuthNonce(
+                access_tokenID=1,
+                request_timestamp = timestamp,
+                nonce = str(timestamp))
+        transaction.commit()
+
+        # Make sure we have 4 nonces now.
+        self.failUnlessEqual(store.find(OAuthNonce).count(), 4)
+
+        self.runHourly()
+
+        # Now back to two, having removed the two garbage entries.
+        self.failUnlessEqual(store.find(OAuthNonce).count(), 2)
+
+        # And none of them are older than a day.
+        # Hmm... why is it I'm putting tz aware datetimes in and getting
+        # naive datetimes back? Bug in the SQLObject compatibility layer?
+        # Test is still fine as we know the timezone.
+        self.failUnless(
+            store.find(
+                Min(OAuthNonce.request_timestamp)).one().replace(tzinfo=UTC)
+            >= now - timedelta(days=1))
+
+    def test_OpenIDNoncePruner(self):
+        now = int(time.mktime(time.gmtime()))
+        MINUTES = 60
+        HOURS = 60 * 60
+        DAYS = 24 * HOURS
+        timestamps = [
+            now - 2 * DAYS, # Garbage
+            now - 1 * DAYS - 1 * MINUTES, # Garbage
+            now - 1 * DAYS + 1 * MINUTES, # Not garbage
+            now, # Not garbage
+            ]
+        LaunchpadZopelessLayer.switchDbUser('testadmin')
+
+        store = IMasterStore(OpenIDNonce)
+
+        # Make sure we start with 0 nonces.
+        self.failUnlessEqual(store.find(OpenIDNonce).count(), 0)
+
+        for timestamp in timestamps:
+            nonce = store.add(OpenIDNonce())
+            nonce.server_url = unicode(timestamp)
+            nonce.timestamp = timestamp
+            nonce.salt = u'aa'
+            store.add(nonce)
+        transaction.commit()
+
+        # Make sure we have 4 nonces now.
+        self.failUnlessEqual(store.find(OpenIDNonce).count(), 4)
+
+        # Run the garbage collector.
+        self.runHourly()
+
+        # We should now have 2 nonces.
+        self.failUnlessEqual(store.find(OpenIDNonce).count(), 2)
+
+        # And none of them are older than 1 day
+        earliest = store.find(Min(OpenIDNonce.timestamp)).one()
+        self.failUnless(earliest >= now - 24*60*60, 'Still have old nonces')
+
+    def test_CodeImportResultPruner(self):
+        now = datetime.utcnow().replace(tzinfo=UTC)
+        store = IMasterStore(CodeImportResult)
+
+        def new_code_import_result(timestamp):
+            LaunchpadZopelessLayer.switchDbUser('testadmin')
+            CodeImportResult(
+                date_created=timestamp,
+                code_importID=1, machineID=1, requesting_userID=1,
+                status=CodeImportResultStatus.FAILURE,
+                date_job_started=timestamp)
+            transaction.commit()
+
+        new_code_import_result(now - timedelta(days=60))
+        new_code_import_result(now - timedelta(days=19))
+        new_code_import_result(now - timedelta(days=20))
+        new_code_import_result(now - timedelta(days=21))
+
+        # Run the garbage collector
+        self.runDaily()
+
+        # Nothing is removed, because we always keep the 4 latest.
+        self.failUnlessEqual(
+            store.find(CodeImportResult).count(), 4)
+
+        new_code_import_result(now - timedelta(days=31))
+        self.runDaily()
+        self.failUnlessEqual(
+            store.find(CodeImportResult).count(), 4)
+
+        new_code_import_result(now - timedelta(days=29))
+        self.runDaily()
+        self.failUnlessEqual(
+            store.find(CodeImportResult).count(), 4)
+
+        # We now have no CodeImportResults older than 30 days
+        self.failUnless(
+            store.find(
+                Min(CodeImportResult.date_created)).one().replace(tzinfo=UTC)
+            >= now - timedelta(days=30))
+
+
+def test_suite():
+    return unittest.TestLoader().loadTestsFromName(__name__)