Script to merge 2 Galaxy users

merge_users.py

#!/usr/bin/env python

import argparse
import os
import shlex
import subprocess
import sys

scripts_dir = os.path.dirname(__file__)
sys.path.insert(1, os.path.join(scripts_dir, os.pardir, 'lib'))

import galaxy.config
from galaxy.util.script import app_properties_from_args, populate_config_args

# Tables that need to be updated manually: group_role_association, user_address, user_preference
query_templates = [
    "UPDATE workflow_tag_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE workflow_step_tag_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE workflow_step_annotation_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE visualization_user_share_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE visualization SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE visualization_tag_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE visualization_rating_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE visualization_annotation_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE user_action SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE tool_tag_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE stored_workflow_user_share_connection SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE stored_workflow SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE stored_workflow_tag_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE stored_workflow_menu_entry SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE stored_workflow_annotation_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE request SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "DELETE FROM password_reset_token WHERE user_id={from_user_id:d}",
    "UPDATE page_user_share_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE page SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE page_tag_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE page_rating_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE page_annotation_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE oidc_user_authnz_tokens SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE library_dataset_dataset_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE library_dataset_dataset_association_tag_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE library_dataset_collection_rating_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE library_dataset_collection_annotation_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE job SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE history_user_share_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE history SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE history_tag_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE history_rating_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE history_dataset_collection_tag_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE history_dataset_collection_rating_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE history_dataset_collection_annotation_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE history_dataset_association_tag_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE history_dataset_association_rating_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE history_dataset_association_display_at_authorization SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE history_dataset_association_annotation_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE genome_index_tool_data SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE galaxy_session SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE event SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE data_manager_history_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "DELETE FROM api_keys WHERE user_id={from_user_id:d}",
    "DELETE FROM galaxy_user_openid WHERE user_id={from_user_id:d}",
    "DELETE FROM default_user_permissions WHERE role_id={from_role_id:d}",
    "UPDATE default_user_permissions SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "DELETE FROM user_role_association WHERE role_id={from_role_id:d}",
    "UPDATE user_role_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d}",
    "UPDATE request_type_permissions SET role_id={to_role_id:d} WHERE role_id={from_role_id:d}",
    "UPDATE library_permissions SET role_id={to_role_id:d} WHERE role_id={from_role_id:d}",
    "UPDATE library_folder_permissions SET role_id={to_role_id:d} WHERE role_id={from_role_id:d}",
    "UPDATE library_dataset_permissions SET role_id={to_role_id:d} WHERE role_id={from_role_id:d}",
    "UPDATE library_dataset_dataset_association_permissions SET role_id={to_role_id:d} WHERE role_id={from_role_id:d}",
    "UPDATE default_history_permissions SET role_id={to_role_id:d} WHERE role_id={from_role_id:d}",
    "UPDATE dataset_permissions SET role_id={to_role_id:d} WHERE role_id={from_role_id:d}",
    "DELETE FROM role WHERE id={from_role_id:d}",
    "DELETE FROM galaxy_user WHERE id={from_user_id:d}",
]


def execute_query_template(sa_session, query_template, format_dict=None):
    if format_dict is None:
        format_dict = dict()
    query = query_template.format(**format_dict)
    print('Executing query: ' + query)
    sa_session.execute(query)


def get_user_private_role_id(user):
    private_roles = [ura.role for ura in user.roles if ura.role.name == user.email and ura.role.type == model.Role.types.PRIVATE]
    assert len(private_roles) == 1, private_roles
    return private_roles[0].id


def get_user_group_ids(user):
    user_groups = [uga.group for uga in user.groups]
    return [_.id for _ in user_groups]


parser = argparse.ArgumentParser()
parser.add_argument('-f', '--from', dest='from_user', action='store', required=True, help='Email of the user to be merged')
parser.add_argument('-t', '--to', action='store', required=True, help='Email of the user to merge into')
parser.add_argument('--keep_from_email', action='store_true', help='Use the email of the "from" user for the merged one')
parser.add_argument('--keep_from_username', action='store_true', help='Use the username of the "from" user for the merged one')
populate_config_args(parser)
args = parser.parse_args()

app_properties = app_properties_from_args(args)
config = galaxy.config.Configuration(**app_properties)
model = galaxy.config.init_models_from_config(config)
sa_session = model.context.current

from_user_email = args.from_user
from_user = sa_session.query(model.User).filter_by(email=from_user_email).first()
from_user_id = from_user.id
from_username = from_user.username
from_role_id = get_user_private_role_id(from_user)
from_user_group_ids = get_user_group_ids(from_user)
to_user_email = args.to
to_user = sa_session.query(model.User).filter_by(email=to_user_email).first()
to_user_id = to_user.id
to_role_id = get_user_private_role_id(to_user)
to_user_group_ids = get_user_group_ids(to_user)

format_dict = {
    'from_user_id': from_user_id,
    'to_user_id': to_user_id,
    'from_role_id': from_role_id,
    'to_role_id': to_role_id
}

sa_session.begin()
try:
    for from_group_id in from_user_group_ids:
        format_dict.update({'from_group_id': from_group_id})
        if from_group_id in to_user_group_ids:
            query_template = "DELETE FROM user_group_association WHERE user_id={from_user_id:d} AND group_id={from_group_id:d}"
        else:
            query_template = "UPDATE user_group_association SET user_id={to_user_id:d} WHERE user_id={from_user_id:d} AND group_id={from_group_id:d}"
        execute_query_template(sa_session, query_template, format_dict)
    for query_template in query_templates:
        execute_query_template(sa_session, query_template, format_dict)
except Exception as e:
    sa_session.rollback()
    raise e
sa_session.commit()

if args.keep_from_email:
    to_user.email = from_user_email
if args.keep_from_username:
    to_user.username = from_username
sa_session.flush()

print(f"Merged user {from_user_email} into {to_user_email}")

cmd = [os.path.join(scripts_dir, 'set_user_disk_usage.py'), '-e', to_user.email]
if args.config_file:
    cmd.extend(['-c', args.config_file])
print('Executing: ' + ' '.join(shlex.quote(_) for _ in cmd))
subprocess.check_call(cmd)