Need the ability to remove a user/publisher and all data

[kineticsquid]

trafficstars

We need the ability to, on request, delete a user/publisher and all of their data. This can be in the form of a script or an entry on the admin panel. @amvanbaren FYI

[amvanbaren]

@kineticsquid Script to manually delete user/publisher and all data:

BEGIN TRANSACTION;

-- delete file resources published by user
DELETE FROM file_resource WHERE extension_id IN (
  SELECT ev.id 
  FROM extension_version ev
  JOIN user_data u ON u.id = ev.published_with_id
  WHERE u.login_name = 'doamatto'
);

CREATE TEMPORARY TABLE entities_to_delete (
  id BIGINT NOT NULL,
  type CHARACTER VARYING(32) NOT NULL
);

INSERT INTO entities_to_delete(id, type)
SELECT id, 'extension_version' FROM extension_version WHERE published_with_id = (
  SELECT id FROM user_data WHERE login_name = 'doamatto'
);

INSERT INTO entities_to_delete(id, type)
SELECT extension_id, 'extension' FROM extension_version WHERE published_with_id = (
  SELECT id FROM user_data WHERE login_name = 'doamatto'
);

-- decouple extension and extension_version to prevent foreign key violation
UPDATE extension SET latest_id = NULL WHERE latest_id IN (
  SELECT id FROM entities_to_delete WHERE type = 'extension_version'
);

-- decouple extension and extension_version to prevent foreign key violation
UPDATE extension SET preview_id = NULL WHERE preview_id IN (
  SELECT id FROM entities_to_delete WHERE type = 'extension_version'
);

-- delete extension versions published by user
DELETE FROM extension_version WHERE id IN (
  SELECT id FROM entities_to_delete WHERE type = 'extension_version'
);

-- delete extension reviews posted by user
DELETE FROM extension_review WHERE user_id = (
  SELECT id FROM user_data WHERE login_name = 'doamatto'
);

-- delete extensions published by user
DELETE FROM extension WHERE id IN (
  SELECT id FROM entities_to_delete WHERE type = 'extension'
);

DROP TABLE entities_to_delete;

-- delete user's namespace memberships
-- namespaces will be deleted or re-assigned by OrphanNamespaceMigration
DELETE FROM namespace_membership WHERE user_data = (
  SELECT id FROM user_data WHERE login_name = 'doamatto'
);

-- delete user's personal access tokens
DELETE FROM personal_access_token WHERE user_data = (
  SELECT id FROM user_data WHERE login_name = 'doamatto'
);

-- delete user
DELETE FROM user_data WHERE login_name = 'doamatto';

END TRANSACTION;

[kineticsquid]

Awesome! Thanks!

[amvanbaren]

@kineticsquid Can we close this issue?

[kineticsquid]

Yes.