Merge member
@DSASanFrancisco/portal-members
SUMMARY
Added: POST /members/[member_id_to_keep]/merge?member_id_to_remove=[member_id_to_remove]&force=[force]
NOTE TO REVIEWER
This is my first MR. The feature is fairly substantial and contains numerous updates/deletes across many of the tables in the db. I recommend extra scrutiny!
I will be out of town 9/15-9/21, but am available for questions/comments/fixes after I get back.
Finally, this is a script I've been using to seed the database for manual testing locally (Note, it mimics the data used for the unit tests....
### Clean up dangling data. Order is import due to foreign key constraints. ###
DELETE FROM dsa.roles
WHERE id in (3333, 4444, 5555, 6666, 7777, 8888, 9999);
DELETE FROM dsa.proxy_token
WHERE id in ("7829", "9388", "8263", "7462", "5352");
DELETE FROM dsa.phone_numbers
WHERE id in (1738, 8211, 9901, 7380, 9999, 2718);
DELETE FROM dsa.memberships_usa
WHERE id in (5739, 8847, 7393, 2661);
DELETE FROM dsa.interests
WHERE id in (8271, 1937, 5627, 6291, 8272, 9389);
DELETE FROM dsa.identities
WHERE id in (329, 1827, 6382);
DELETE FROM dsa.eligible_voters
WHERE member_id in (58337, 6278, 928276);
DELETE FROM dsa.attendees
WHERE id in (62827, 92729, 52719, 6382892);
DELETE FROM dsa.members
WHERE id in (58337, 6278, 928276);
DELETE FROM dsa.meetings
WHERE id in (7291, 42618);
DELETE FROM dsa.interest_topics
WHERE id in (8392, 9187, 6452);
DELETE FROM dsa.committees
WHERE id in (2514, 1564, 6362);
DELETE FROM dsa.chapters
WHERE id in (111);
DELETE FROM dsa.elections
WHERE id in (509329, 62829, 72920, 83826);
### Some test data that won't be manipulated as part of the tests but need to exist due to foreign key constraints. ###
INSERT INTO dsa.elections (id, name, status, number_winners, voting_begins_epoch_millis, voting_ends_epoch_millis, description, description_img, author)
VALUES (509329, "vote a", "status", 1, 1010101, 10103801, "desc", "/img", "author"),
(62829, "vote b", "status", 1, 1010101, 10103801, "desc", "/img", "author"),
(72920, "vote c", "status", 1, 1010101, 10103801, "desc", "/img", "author"),
(83826, "vote d", "status", 1, 1010101, 10103801, "desc", "/img", "author");
INSERT INTO dsa.chapters (id, name)
VALUES (111, "SF");
INSERT INTO dsa.committees (id, name, provisional)
VALUES (2514, "tech", 0),
(1564, "electoral", 0),
(6362, "climate", 0);
INSERT INTO dsa.interest_topics (id, name)
VALUES (8392, "interest 1"), (9187, "interest 2"), (6452, "interest 3");
INSERT INTO dsa.meetings (id, short_id, name, committee_id, start_time, end_time, landing_url)
VALUES (7291, 7291, "meeting 1", 2514, "2019-01-01", "2019-01-01", "meeting1.com"),
(42618, 42618, "meeting 2", 1564, "2019-01-01", "2019-01-01", "meeting2.com");
### Test data that will be directly modified and queried.
### Members must be inserted first due to foreign key constraints
INSERT INTO dsa.members (id, date_created, first_name, last_name, email_address, normalized_email, do_not_call, do_not_email, biography)
VALUES (58337, "2019-01-01", "first", "last", "email1@gmail.com", "email1@gmail.com", 0, 0, "bio"),
(6278, "2019-01-01", "first", "last", "email2@gmail.com", "email2@gmail.com", 0, 0, "bio"),
(928276, "2019-01-01", "first", "last", "email3@gmail.com", "email3@gmail.com", 0, 0, "bio");
INSERT INTO dsa.attendees (id, meeting_id, member_id, eligible_to_vote)
VALUES (62827, 7291, 58337, 1),
(92729, 7291, 6278, 1),
(52719, 42618, 6278, 1),
(6382892, 7291, 928276, 1);
INSERT INTO dsa.eligible_voters (member_id, voted, election_id)
VALUES (58337, 1, 509329), (58337, 1, 62829), (58337, 1, 83826), (6278, 1, 509329), (6278, 1, 72920), (6278, 0, 83826), (928276, 1, 509329);
INSERT INTO dsa.identities (id, member_id, date_imported, provider_name, provider_id)
VALUES (329, 58337, "2019-01-01", "provider 1", 8291),
(1827, 6278, "2019-01-01", "provider 2", 3948),
(6382, 928276, "2019-01-01", "provider 3", 9271);
INSERT INTO dsa.interests (id, member_id, topic_id, created_date)
VALUES (8271, 58337, 8392, "2019-01-01"), # This should be left as is
(1937, 58337, 9187, "2019-01-05"), # This should be updated to 2019-01-01 to reflect older duplicate entry below
(5627, 6278, 8392, "2019-01-05"), # This is a duplicate and will be deleted
(6291, 6278, 9187, "2019-01-01"), # This is a duplicate, but older then above entry
(8272, 6278, 6452, "2019-01-01"), # This is not a duplicate and id will be updated to 58337
(9389, 928276, 6452, "2019-01-01"); # This should not be impacted
INSERT INTO dsa.memberships_usa (id, date_imported, active, member_id, ak_id, dsa_id, do_not_call, first_name, middle_name, last_name, address_line_1, address_line_2, city, country, zipcode, join_date, dues_paid_until)
VALUES (5739, "2019-01-01", 1, 58337, "ak_id1", "sf", 0, "first", "middle", "last", "addr 1", "addr 2", "sf", "usa", "94117", "2019-01-01", "2020-01-01"), # This should be left as is
(7393, "2019-01-01", 1, 6278, "ak_id2", "sf", 0, "first", "middle", "last", "addr 1", "addr 2", "sf", "usa", "94117", "2019-01-01", "2020-01-01"), # This is not a duplicate and member_id should be updated to 58337
(2661, "2019-01-01", 1, 928276, "ak_id3", "sf", 0, "first", "middle", "last", "addr 1", "addr 2", "sf", "usa", "94117", "2019-01-01", "2020-01-01"); # This should not be impacted
INSERT INTO dsa.phone_numbers (id, member_id, date_imported, number, name)
VALUES (1738, 58337, "2019-01-01", 1234567890, "keep"), # This should be left as is
(8211, 58337, "2019-01-05", 4567891234, "update"), # This should be update to 2019-01-01 to reflect older duplicate entry below
(9901, 6278, "2019-01-05", 1234567890, "delete"), # This is a duplicate and should be deleted because its newer
(7380, 6278, "2019-01-01", 2345678901, "keep"), # This is not a duplicate and member_id should be updated to 58337
(9999, 6278, "2019-01-01", 4567891234, "delete"), # This is a duplicate and should be deleted
(2718, 928276, "2019-01-01", 3456789012, "keep"); # This should be left as is
INSERT INTO dsa.proxy_token (id, member_id, meeting_id, receiving_member_id, state)
VALUES ("7829", 58337, 7291, 928276, "ACCEPTED"), # This should not be impacted
("9388", 6278, 7291, 928276, "ACCEPTED"), # This is a duplicate and should be deleted
("8263", 6278, 42618, 928276, "ACCEPTED"), # This is not a dup and member_id should be updated to 58337
("7462", 928276, 7291, 6278, "ACCEPTED"), # This is not involved directly in merge, but the receiving member is and should be updated
("5352", 928276, 7291, 58337, "ACCEPTED"); # This should not be affected
INSERT INTO dsa.roles (id, chapter_id, committee_id, member_id, role, date_created)
VALUES (3333, 111, 2514, 58337, "role 1", "2019-01-01"), # This role should be preserved
(4444, 111, 2514, 58337, "role 2", "2019-01-05"), # This role should have its date updated to 2019-01-01 because the dup entry has an earlier date
(5555, 111, 2514, 6278, "role 1", "2019-01-05"), # This role is a duplicate and should be deleted
(6666, 111, 2514, 6278, "role 2", "2019-01-01"), # This role is a duplicate and should be deleted
(7777, 111, 2514, 6278, "role 3", "2019-01-01"), # This role should have its member_id updated to 58337 because it's a unique role name
(8888, 111, 1564, 6278, "role 1", "2019-01-01"), # This role should have its member_id updated to 58337 because it's a unique committee_id.
(9999, 111, 1564, 928276, "role 1", "2019-01-01"); # This role should not be impacted