{"action":"created","comment":{"url":"https://api.github.com/repos/mozilla/balrog/pulls/comments/69458245","id":69458245,"diff_hunk":"@@ -13,22 +14,74 @@\n \n from auslib.db import AUSDatabase\n \n+def cleanup_releases(trans, nightly_age, dryrun=True):\n+    # This and the subsequent queries use \"%%%%%\" because we end up going\n+    # through two levels of Python string formatting. The first is here,\n+    # and the second happens at a low level of SQLAlchemy when the transaction\n+    # is being executed.\n+    query = \"\"\"\n+LEFT OUTER JOIN releases_history USING (name)\n+LEFT JOIN rules ON (name=mapping)","path":"scripts/manage-db.py","position":24,"original_position":24,"commit_id":"184d6b18e7b7e580f0ee9518fadd8490d0ef50e4","original_commit_id":"184d6b18e7b7e580f0ee9518fadd8490d0ef50e4","user":{"login":"mozbhearsum","id":49649,"avatar_url":"https://avatars.githubusercontent.com/u/49649?v=3","gravatar_id":"","url":"https://api.github.com/users/mozbhearsum","html_url":"https://github.com/mozbhearsum","followers_url":"https://api.github.com/users/mozbhearsum/followers","following_url":"https://api.github.com/users/mozbhearsum/following{/other_user}","gists_url":"https://api.github.com/users/mozbhearsum/gists{/gist_id}","starred_url":"https://api.github.com/users/mozbhearsum/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozbhearsum/subscriptions","organizations_url":"https://api.github.com/users/mozbhearsum/orgs","repos_url":"https://api.github.com/users/mozbhearsum/repos","events_url":"https://api.github.com/users/mozbhearsum/events{/privacy}","received_events_url":"https://api.github.com/users/mozbhearsum/received_events","type":"User","site_admin":false},"body":"re: LEFT JOIN vs. LEFT OUTER JOIN - I don't think there's any difference either, I just didn't tweak those parts of the queries from https://bugzilla.mozilla.org/show_bug.cgi?id=1237991#c41.\r\n\r\nIt's a good point about only needing the most recent row - I wonder if it would be faster to do subquery with LIMIT 1 instead of a JOIN?","created_at":"2016-07-04T13:32:26Z","updated_at":"2016-07-04T13:32:26Z","html_url":"https://github.com/mozilla/balrog/pull/96#discussion_r69458245","pull_request_url":"https://api.github.com/repos/mozilla/balrog/pulls/96","_links":{"self":{"href":"https://api.github.com/repos/mozilla/balrog/pulls/comments/69458245"},"html":{"href":"https://github.com/mozilla/balrog/pull/96#discussion_r69458245"},"pull_request":{"href":"https://api.github.com/repos/mozilla/balrog/pulls/96"}}},"pull_request":{"url":"https://api.github.com/repos/mozilla/balrog/pulls/96","id":75836084,"html_url":"https://github.com/mozilla/balrog/pull/96","diff_url":"https://github.com/mozilla/balrog/pull/96.diff","patch_url":"https://github.com/mozilla/balrog/pull/96.patch","issue_url":"https://api.github.com/repos/mozilla/balrog/issues/96","number":96,"state":"open","locked":false,"title":"Integrate retention policy with existing db management script","user":{"login":"mozbhearsum","id":49649,"avatar_url":"https://avatars.githubusercontent.com/u/49649?v=3","gravatar_id":"","url":"https://api.github.com/users/mozbhearsum","html_url":"https://github.com/mozbhearsum","followers_url":"https://api.github.com/users/mozbhearsum/followers","following_url":"https://api.github.com/users/mozbhearsum/following{/other_user}","gists_url":"https://api.github.com/users/mozbhearsum/gists{/gist_id}","starred_url":"https://api.github.com/users/mozbhearsum/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozbhearsum/subscriptions","organizations_url":"https://api.github.com/users/mozbhearsum/orgs","repos_url":"https://api.github.com/users/mozbhearsum/repos","events_url":"https://api.github.com/users/mozbhearsum/events{/privacy}","received_events_url":"https://api.github.com/users/mozbhearsum/received_events","type":"User","site_admin":false},"body":"I'd like to do a little bit more testing with a larger data set, but in my limited tests it's working well. The queries are based on the most recent stored procedures from https://bugzilla.mozilla.org/show_bug.cgi?id=1237991#c41. Because we intend to do this cleanup much more frequently (at least once an hour, probably more), @mostlygeek suggested that we just do plain DELETEs rather than complicating things with cursors, which seemed fine to me.\r\n\r\nI'm not 100% sure this script is the right place for the queries, but it seemed even more awkward to put them in db.py - I'm curious what you think about this, @nthomas-mozilla . At one point I had them in a .sql file, but that made it much tougher to do the SELECTs for the dryruns. Any other comments are welcome too, of course.","created_at":"2016-06-30T15:05:16Z","updated_at":"2016-07-04T13:32:26Z","closed_at":null,"merged_at":null,"merge_commit_sha":"4317b4a7dcf649acb49dfdfbf24aa07e963040ce","assignee":null,"assignees":[],"milestone":null,"commits_url":"https://api.github.com/repos/mozilla/balrog/pulls/96/commits","review_comments_url":"https://api.github.com/repos/mozilla/balrog/pulls/96/comments","review_comment_url":"https://api.github.com/repos/mozilla/balrog/pulls/comments{/number}","comments_url":"https://api.github.com/repos/mozilla/balrog/issues/96/comments","statuses_url":"https://api.github.com/repos/mozilla/balrog/statuses/184d6b18e7b7e580f0ee9518fadd8490d0ef50e4","head":{"label":"mozbhearsum:retention-script","ref":"retention-script","sha":"184d6b18e7b7e580f0ee9518fadd8490d0ef50e4","user":{"login":"mozbhearsum","id":49649,"avatar_url":"https://avatars.githubusercontent.com/u/49649?v=3","gravatar_id":"","url":"https://api.github.com/users/mozbhearsum","html_url":"https://github.com/mozbhearsum","followers_url":"https://api.github.com/users/mozbhearsum/followers","following_url":"https://api.github.com/users/mozbhearsum/following{/other_user}","gists_url":"https://api.github.com/users/mozbhearsum/gists{/gist_id}","starred_url":"https://api.github.com/users/mozbhearsum/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozbhearsum/subscriptions","organizations_url":"https://api.github.com/users/mozbhearsum/orgs","repos_url":"https://api.github.com/users/mozbhearsum/repos","events_url":"https://api.github.com/users/mozbhearsum/events{/privacy}","received_events_url":"https://api.github.com/users/mozbhearsum/received_events","type":"User","site_admin":false},"repo":{"id":2570483,"name":"balrog","full_name":"mozbhearsum/balrog","owner":{"login":"mozbhearsum","id":49649,"avatar_url":"https://avatars.githubusercontent.com/u/49649?v=3","gravatar_id":"","url":"https://api.github.com/users/mozbhearsum","html_url":"https://github.com/mozbhearsum","followers_url":"https://api.github.com/users/mozbhearsum/followers","following_url":"https://api.github.com/users/mozbhearsum/following{/other_user}","gists_url":"https://api.github.com/users/mozbhearsum/gists{/gist_id}","starred_url":"https://api.github.com/users/mozbhearsum/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozbhearsum/subscriptions","organizations_url":"https://api.github.com/users/mozbhearsum/orgs","repos_url":"https://api.github.com/users/mozbhearsum/repos","events_url":"https://api.github.com/users/mozbhearsum/events{/privacy}","received_events_url":"https://api.github.com/users/mozbhearsum/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/mozbhearsum/balrog","description":"Next generation update service for Mozilla products","fork":true,"url":"https://api.github.com/repos/mozbhearsum/balrog","forks_url":"https://api.github.com/repos/mozbhearsum/balrog/forks","keys_url":"https://api.github.com/repos/mozbhearsum/balrog/keys{/key_id}","collaborators_url":"https://api.github.com/repos/mozbhearsum/balrog/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/mozbhearsum/balrog/teams","hooks_url":"https://api.github.com/repos/mozbhearsum/balrog/hooks","issue_events_url":"https://api.github.com/repos/mozbhearsum/balrog/issues/events{/number}","events_url":"https://api.github.com/repos/mozbhearsum/balrog/events","assignees_url":"https://api.github.com/repos/mozbhearsum/balrog/assignees{/user}","branches_url":"https://api.github.com/repos/mozbhearsum/balrog/branches{/branch}","tags_url":"https://api.github.com/repos/mozbhearsum/balrog/tags","blobs_url":"https://api.github.com/repos/mozbhearsum/balrog/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/mozbhearsum/balrog/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/mozbhearsum/balrog/git/refs{/sha}","trees_url":"https://api.github.com/repos/mozbhearsum/balrog/git/trees{/sha}","statuses_url":"https://api.github.com/repos/mozbhearsum/balrog/statuses/{sha}","languages_url":"https://api.github.com/repos/mozbhearsum/balrog/languages","stargazers_url":"https://api.github.com/repos/mozbhearsum/balrog/stargazers","contributors_url":"https://api.github.com/repos/mozbhearsum/balrog/contributors","subscribers_url":"https://api.github.com/repos/mozbhearsum/balrog/subscribers","subscription_url":"https://api.github.com/repos/mozbhearsum/balrog/subscription","commits_url":"https://api.github.com/repos/mozbhearsum/balrog/commits{/sha}","git_commits_url":"https://api.github.com/repos/mozbhearsum/balrog/git/commits{/sha}","comments_url":"https://api.github.com/repos/mozbhearsum/balrog/comments{/number}","issue_comment_url":"https://api.github.com/repos/mozbhearsum/balrog/issues/comments{/number}","contents_url":"https://api.github.com/repos/mozbhearsum/balrog/contents/{+path}","compare_url":"https://api.github.com/repos/mozbhearsum/balrog/compare/{base}...{head}","merges_url":"https://api.github.com/repos/mozbhearsum/balrog/merges","archive_url":"https://api.github.com/repos/mozbhearsum/balrog/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/mozbhearsum/balrog/downloads","issues_url":"https://api.github.com/repos/mozbhearsum/balrog/issues{/number}","pulls_url":"https://api.github.com/repos/mozbhearsum/balrog/pulls{/number}","milestones_url":"https://api.github.com/repos/mozbhearsum/balrog/milestones{/number}","notifications_url":"https://api.github.com/repos/mozbhearsum/balrog/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/mozbhearsum/balrog/labels{/name}","releases_url":"https://api.github.com/repos/mozbhearsum/balrog/releases{/id}","deployments_url":"https://api.github.com/repos/mozbhearsum/balrog/deployments","created_at":"2011-10-13T15:58:54Z","updated_at":"2015-04-10T17:04:36Z","pushed_at":"2016-06-30T15:03:48Z","git_url":"git://github.com/mozbhearsum/balrog.git","ssh_url":"git@github.com:mozbhearsum/balrog.git","clone_url":"https://github.com/mozbhearsum/balrog.git","svn_url":"https://github.com/mozbhearsum/balrog","homepage":"","size":31144,"stargazers_count":2,"watchers_count":2,"language":"Python","has_issues":false,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":1,"mirror_url":null,"open_issues_count":0,"forks":1,"open_issues":0,"watchers":2,"default_branch":"master"}},"base":{"label":"mozilla:master","ref":"master","sha":"b0404ab22649c650def7e4c9c90ce2576e28d8a9","user":{"login":"mozilla","id":131524,"avatar_url":"https://avatars.githubusercontent.com/u/131524?v=3","gravatar_id":"","url":"https://api.github.com/users/mozilla","html_url":"https://github.com/mozilla","followers_url":"https://api.github.com/users/mozilla/followers","following_url":"https://api.github.com/users/mozilla/following{/other_user}","gists_url":"https://api.github.com/users/mozilla/gists{/gist_id}","starred_url":"https://api.github.com/users/mozilla/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozilla/subscriptions","organizations_url":"https://api.github.com/users/mozilla/orgs","repos_url":"https://api.github.com/users/mozilla/repos","events_url":"https://api.github.com/users/mozilla/events{/privacy}","received_events_url":"https://api.github.com/users/mozilla/received_events","type":"Organization","site_admin":false},"repo":{"id":2570444,"name":"balrog","full_name":"mozilla/balrog","owner":{"login":"mozilla","id":131524,"avatar_url":"https://avatars.githubusercontent.com/u/131524?v=3","gravatar_id":"","url":"https://api.github.com/users/mozilla","html_url":"https://github.com/mozilla","followers_url":"https://api.github.com/users/mozilla/followers","following_url":"https://api.github.com/users/mozilla/following{/other_user}","gists_url":"https://api.github.com/users/mozilla/gists{/gist_id}","starred_url":"https://api.github.com/users/mozilla/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozilla/subscriptions","organizations_url":"https://api.github.com/users/mozilla/orgs","repos_url":"https://api.github.com/users/mozilla/repos","events_url":"https://api.github.com/users/mozilla/events{/privacy}","received_events_url":"https://api.github.com/users/mozilla/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/mozilla/balrog","description":"Next generation update service for Mozilla products","fork":false,"url":"https://api.github.com/repos/mozilla/balrog","forks_url":"https://api.github.com/repos/mozilla/balrog/forks","keys_url":"https://api.github.com/repos/mozilla/balrog/keys{/key_id}","collaborators_url":"https://api.github.com/repos/mozilla/balrog/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/mozilla/balrog/teams","hooks_url":"https://api.github.com/repos/mozilla/balrog/hooks","issue_events_url":"https://api.github.com/repos/mozilla/balrog/issues/events{/number}","events_url":"https://api.github.com/repos/mozilla/balrog/events","assignees_url":"https://api.github.com/repos/mozilla/balrog/assignees{/user}","branches_url":"https://api.github.com/repos/mozilla/balrog/branches{/branch}","tags_url":"https://api.github.com/repos/mozilla/balrog/tags","blobs_url":"https://api.github.com/repos/mozilla/balrog/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/mozilla/balrog/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/mozilla/balrog/git/refs{/sha}","trees_url":"https://api.github.com/repos/mozilla/balrog/git/trees{/sha}","statuses_url":"https://api.github.com/repos/mozilla/balrog/statuses/{sha}","languages_url":"https://api.github.com/repos/mozilla/balrog/languages","stargazers_url":"https://api.github.com/repos/mozilla/balrog/stargazers","contributors_url":"https://api.github.com/repos/mozilla/balrog/contributors","subscribers_url":"https://api.github.com/repos/mozilla/balrog/subscribers","subscription_url":"https://api.github.com/repos/mozilla/balrog/subscription","commits_url":"https://api.github.com/repos/mozilla/balrog/commits{/sha}","git_commits_url":"https://api.github.com/repos/mozilla/balrog/git/commits{/sha}","comments_url":"https://api.github.com/repos/mozilla/balrog/comments{/number}","issue_comment_url":"https://api.github.com/repos/mozilla/balrog/issues/comments{/number}","contents_url":"https://api.github.com/repos/mozilla/balrog/contents/{+path}","compare_url":"https://api.github.com/repos/mozilla/balrog/compare/{base}...{head}","merges_url":"https://api.github.com/repos/mozilla/balrog/merges","archive_url":"https://api.github.com/repos/mozilla/balrog/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/mozilla/balrog/downloads","issues_url":"https://api.github.com/repos/mozilla/balrog/issues{/number}","pulls_url":"https://api.github.com/repos/mozilla/balrog/pulls{/number}","milestones_url":"https://api.github.com/repos/mozilla/balrog/milestones{/number}","notifications_url":"https://api.github.com/repos/mozilla/balrog/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/mozilla/balrog/labels{/name}","releases_url":"https://api.github.com/repos/mozilla/balrog/releases{/id}","deployments_url":"https://api.github.com/repos/mozilla/balrog/deployments","created_at":"2011-10-13T15:51:47Z","updated_at":"2016-05-18T03:09:48Z","pushed_at":"2016-06-30T20:18:36Z","git_url":"git://github.com/mozilla/balrog.git","ssh_url":"git@github.com:mozilla/balrog.git","clone_url":"https://github.com/mozilla/balrog.git","svn_url":"https://github.com/mozilla/balrog","homepage":"","size":28754,"stargazers_count":16,"watchers_count":16,"language":"Python","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":28,"mirror_url":null,"open_issues_count":7,"forks":28,"open_issues":7,"watchers":16,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/mozilla/balrog/pulls/96"},"html":{"href":"https://github.com/mozilla/balrog/pull/96"},"issue":{"href":"https://api.github.com/repos/mozilla/balrog/issues/96"},"comments":{"href":"https://api.github.com/repos/mozilla/balrog/issues/96/comments"},"review_comments":{"href":"https://api.github.com/repos/mozilla/balrog/pulls/96/comments"},"review_comment":{"href":"https://api.github.com/repos/mozilla/balrog/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/mozilla/balrog/pulls/96/commits"},"statuses":{"href":"https://api.github.com/repos/mozilla/balrog/statuses/184d6b18e7b7e580f0ee9518fadd8490d0ef50e4"}}}}
{"actor":{"display_login":"mozbhearsum"}}