gerrit1_import/link_accounts.sql

-- Transfers all account data from one account to another.
--
-- Create /tmp/link_accounts.csv as "junk,from_email,to_email"
-- chmod a+r /tmp/link_accounts.csv
-- (must be on the PostgreSQL server)

DROP TABLE links;
DROP TABLE to_del;

CREATE TEMP TABLE links
(date_junk VARCHAR(255),
 from_email VARCHAR(255),
 to_email VARCHAR(255));

COPY links FROM '/tmp/link_accounts.csv' DELIMITER ',';
ALTER TABLE links DROP date_junk;

ALTER TABLE links ADD from_id INT;
ALTER TABLE links ADD to_id INT;

UPDATE links
SET from_id = (SELECT account_id FROM accounts
               WHERE preferred_email = links.from_email)
,   to_id   = (SELECT account_id FROM accounts
               WHERE preferred_email = links.to_email);
DELETE FROM links
WHERE (from_email = to_email)
   OR (from_id IS NULL AND to_id IS NULL);

CREATE TEMP TABLE to_del (old_id INT);

BEGIN TRANSACTION;

INSERT INTO account_external_ids
(email_address, account_id, external_id)
SELECT
 l.from_email
,l.to_id
,'Google Account ' || l.from_email
FROM links l
WHERE l.to_id IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM account_external_ids e
  WHERE e.email_address = l.from_email
  AND e.account_id = l.to_id
  AND e.external_id = 'Google Account ' || l.from_email);

INSERT INTO account_external_ids
(email_address, account_id, external_id)
SELECT
 l.to_email
,l.from_id
,'Google Account ' || l.to_email
FROM links l
WHERE l.from_id IS NOT NULL AND l.to_id IS NULL
AND NOT EXISTS (SELECT 1 FROM account_external_ids e
  WHERE e.email_address = l.to_email
  AND e.account_id = l.from_id
  AND e.external_id = 'Google Account ' || l.to_email);

INSERT INTO starred_changes
(account_id, change_id)
SELECT l.to_id, s.change_id
FROM links l, starred_changes s
WHERE l.from_id IS NOT NULL
  AND l.to_id IS NOT NULL
  AND s.account_id = l.from_id
  AND NOT EXISTS (SELECT 1 FROM starred_changes e
                  WHERE e.account_id = l.to_id
                  AND e.change_id = s.change_id);

INSERT INTO account_project_watches
(account_id, project_id)
SELECT l.to_id, s.project_id
FROM links l, account_project_watches s
WHERE l.from_id IS NOT NULL
  AND l.to_id IS NOT NULL
  AND s.account_id = l.from_id
  AND NOT EXISTS (SELECT 1 FROM account_project_watches e
                  WHERE e.account_id = l.to_id
                  AND e.project_id = s.project_id);

INSERT INTO account_group_members
(account_id, group_id)
SELECT l.to_id, s.group_id
FROM links l, account_group_members s
WHERE l.from_id IS NOT NULL
  AND l.to_id IS NOT NULL
  AND s.account_id = l.from_id
  AND NOT EXISTS (SELECT 1 FROM account_group_members e
                  WHERE e.account_id = l.to_id
                  AND e.group_id = s.group_id);

UPDATE changes
SET owner_account_id = (SELECT l.to_id
                        FROM links l
                        WHERE l.from_id = owner_account_id)
WHERE EXISTS (SELECT 1 FROM links l
              WHERE l.to_id IS NOT NULL
                AND l.from_id IS NOT NULL
                AND l.from_id = owner_account_id);

UPDATE change_approvals
SET account_id = (SELECT l.to_id
                  FROM links l
                  WHERE l.from_id = account_id)
WHERE EXISTS (SELECT 1 FROM links l
              WHERE l.to_id IS NOT NULL
                AND l.from_id IS NOT NULL
                AND l.from_id = account_id)
 AND NOT EXISTS (SELECT 1 FROM change_approvals e, links l
                 WHERE e.change_id = change_approvals.change_id
                   AND e.account_id = l.to_id
                   AND e.category_id = change_approvals.category_id
                   AND l.from_id = change_approvals.account_id);

UPDATE change_messages
SET author_id = (SELECT l.to_id
                 FROM links l
                 WHERE l.from_id = author_id)
WHERE EXISTS (SELECT 1 FROM links l
              WHERE l.to_id IS NOT NULL
                AND l.from_id IS NOT NULL
                AND l.from_id = author_id);

UPDATE patch_comments
SET author_id = (SELECT l.to_id
                 FROM links l
                 WHERE l.from_id = author_id)
WHERE EXISTS (SELECT 1 FROM links l
              WHERE l.to_id IS NOT NULL
                AND l.from_id IS NOT NULL
                AND l.from_id = author_id);


-- Destroy the from account
--
INSERT INTO to_del
SELECT from_id FROM links
WHERE to_id IS NOT NULL
AND from_id IS NOT NULL;

DELETE FROM account_agreements WHERE account_id IN (SELECT old_id FROM to_del);
DELETE FROM account_external_ids WHERE account_id IN (SELECT old_id FROM to_del);
DELETE FROM account_group_members WHERE account_id IN (SELECT old_id FROM to_del);
DELETE FROM account_project_watches WHERE account_id IN (SELECT old_id FROM to_del);
DELETE FROM account_ssh_keys WHERE account_id IN (SELECT old_id FROM to_del);
DELETE FROM accounts WHERE account_id IN (SELECT old_id FROM to_del);
DELETE FROM starred_changes WHERE account_id IN (SELECT old_id FROM to_del);
DELETE FROM change_approvals WHERE account_id IN (SELECT old_id FROM to_del);

COMMIT;