Delete all MyWorkspace sites from a Sakai deployment

delete-myworkspace.sql

-- Deletes all user myworkspaces we can find.
-- Matthew Buckett

BEGIN WORK;

CREATE TEMPORARY TABLE delete_ids (
  id varchar(99) UNIQUE
  );

-- If you want to be more brutal you can exlude the join to sakai_user_id_map and just look for all IDs that start with ~
INSERT INTO delete_ids SELECT site_id from SAKAI_SITE where site_id in (SELECT concat("~",user_id) FROM SAKAI_USER_ID_MAP where user_id != eid) FOR UPDATE;


DELETE s FROM SAKAI_SITE_TOOL_PROPERTY s, delete_ids WHERE site_id = delete_ids.id;
SELECT ROW_COUNT() as SAKAI_SITE_TOOL_PROPERTY FROM DUAL;
DELETE s FROM SAKAI_SITE_PAGE_PROPERTY s, delete_ids WHERE site_id = delete_ids.id;
SELECT ROW_COUNT() as SAKAI_SITE_PAGE_PROPERTY FROM DUAL;
DELETE s FROM SAKAI_SITE_GROUP_PROPERTY s, delete_ids WHERE site_id = delete_ids.id;
SELECT ROW_COUNT() as SAKAI_SITE_GROUP_PROPERTY FROM DUAL;
DELETE s FROM SAKAI_SITE_USER s, delete_ids WHERE site_id = delete_ids.id;
SELECT ROW_COUNT() as SAKAI_SITE_USER  FROM DUAL;
DELETE s FROM SAKAI_SITE_TOOL s, delete_ids WHERE site_id = delete_ids.id;
SELECT ROW_COUNT() as SAKAI_SITE_TOOL FROM DUAL;
DELETE s FROM SAKAI_SITE_PAGE s, delete_ids WHERE site_id = delete_ids.id;
SELECT ROW_COUNT() as SAKAI_SITE_PAGE FROM DUAL;
DELETE s FROM SAKAI_SITE_PROPERTY s, delete_ids WHERE site_id = delete_ids.id;
SELECT ROW_COUNT() as SAKAI_SITE_PROPERTY FROM DUAL;
DELETE s FROM SAKAI_SITE_GROUP s, delete_ids WHERE site_id = delete_ids.id;
SELECT ROW_COUNT() as SAKAI_SITE_GROUP FROM DUAL;

DELETE s FROM SAKAI_SITE s, delete_ids WHERE site_id = delete_ids.id;
SELECT ROW_COUNT() as SAKAI_SITE FROM DUAL;

DELETE s FROM SAKAI_SITE_USER s, delete_ids WHERE site_id = delete_ids.id;
SELECT ROW_COUNT() as SAKAI_SITE_USER FROM DUAL;

SELECT site_id as "Remaining User Sites" from SAKAI_SITE where site_id like '~%';


COMMIT WORK;