Confluence migrator SQL script

confluence-migrator.sql

BEGIN;

INSERT INTO users (id, name, password, email, fullname)
  SELECT u.id, u.username, u.passwd,
    (SELECT string_val FROM os_propertyentry
      WHERE entity_id = u.id AND entity_key = 'email'),
    (SELECT string_val FROM os_propertyentry
      WHERE entity_id = u.id AND entity_key = 'fullName')
  FROM os_user u
  WHERE u.id NOT IN (
    SELECT user_id FROM os_user_group WHERE group_id = (
      SELECT id FROM os_group WHERE groupname = 'ldap-users'));

SAVEPOINT sp1;

INSERT INTO external_entities (id, name, type)
  SELECT u.id, u.username, 'EXT'
  FROM os_user u
  WHERE u.id IN (
    SELECT user_id FROM os_user_group WHERE group_id = (
      SELECT id FROM os_group WHERE groupname = 'ldap-users'));

SAVEPOINT sp2;

INSERT INTO groups (id, groupname)
  SELECT id, groupname FROM os_group;

SAVEPOINT sp3;

INSERT INTO external_members (extentityid, groupid)
  SELECT user_id, group_id FROM os_user_group
   WHERE user_id IN (SELECT id FROM external_entities);

SAVEPOINT sp4;

INSERT INTO local_members (userid, groupid)
  SELECT user_id, group_id FROM os_user_group
   WHERE user_id IN (SELECT id FROM users);

SAVEPOINT sp5;

DELETE FROM os_propertyentry
 WHERE entity_name = 'OSUser_user'
   AND entity_key IN ('fullName', 'email');

SAVEPOINT sp6;

UPDATE os_propertyentry
   SET entity_name = 'LOC_' || (SELECT name FROM users WHERE id = entity_id)
 WHERE entity_name = 'OSUser_user' 
   AND entity_id IN (SELECT id FROM users);

SAVEPOINT sp7;

UPDATE os_propertyentry
   SET entity_name = 'EXT_' || (SELECT name FROM external_entities WHERE id = entity_id)
 WHERE entity_name = 'OSUser_user' 
   AND entity_id IN (SELECT id FROM external_entities);

ROLLBACK;