877712 - Validate database cleanup: log cleanup automation to database and display in /admin; refresh stage from prod.

Status: RESOLVED INVALID

(Participation Infrastructure :: Phonebook, defect)

Description

[Justin Crawford [:hoosteeno] [:jcrawford]]

We need to validate that the database cleanup code we wrote for Mozillians.org [0] is doing what we expect it to do. Specifically, we need to get before/after counts of empty/not-empty groups and complete/incomplete profiles. We want to do it on stage before we run them on production.

So we need to 
a) refresh staging from production
b) run two sql queries [1]
c) wait a day for the automated cleanup code to run
d) and then run the same two queries again.

[0]
https://bugzilla.mozilla.org/show_bug.cgi?id=859943
https://bugzilla.mozilla.org/show_bug.cgi?id=859945

[1]
-- count groups with/without members
SELECT z.*, nz.* FROM (SELECT COUNT(*) AS groups_without_members FROM (SELECT COUNT(pg.userprofile_id) AS member_count FROM profile_groups pg GROUP BY pg.group_id) iz WHERE member_count = 0) z, (SELECT COUNT(*) AS groups_with_members FROM (SELECT COUNT(pg.userprofile_id) AS member_count FROM profile_groups pg GROUP BY pg.group_id) AS inz WHERE member_count != 0) nz;
     
-- count complete/incomplete profiles
SELECT c.*, i.* FROM (SELECT COUNT(*) AS complete_profiles FROM profile WHERE full_name IS NOT NULL AND full_name != '') c, (SELECT COUNT(*) AS incomplete_profiles FROM profile WHERE full_name IS NULL OR full_name = '') i;

Comment 1

[Justin Crawford [:hoosteeno] [:jcrawford]]

[Modifying the original spec]

Instead of asking for DBA assistance to run these queries one time, let's build a new table and log the before/after counts to the table after every automated cleanup. Let's also display the contents of that table in /admin. A revised punchlist:

a) modify cleanup automation to log before/after counts of complete/incomplete profiles, empty/non-empty groups to a table in the database, and display that table in /admin
b) deploy it to stage
c) refresh stage data from prod
d) validate at will

Comment 2

[Pierros Papadeas [:pierros]]

Invalid as we are not refreshing anymore.