Script for membership and user tables for LOTS

user_role_migration.py

#!/usr/bin/env python
# Author: Ian Des Jardins
# Date: 06/26/2018

import sys
from datetime import date

def find_line_and_values(lines, file_cursor):
    line = ''
    line_count = 1
    for insert_line in lines[file_cursor:]:
        line += insert_line
        line_count += 1
        if line.rstrip().endswith(');'):
            break

    sanitized_line = line.replace('null', '\'\'')
    sanitized_line = sanitized_line.replace('NULL', '\'\'')
    sanitized_line = ''.join(sanitized_line.splitlines())
    return (line, eval(sanitized_line[sanitized_line.find('VALUES') + 6:sanitized_line.find(';')].strip()))

def write_group_comment(dest_file, group):
    left_right_asterisks = (80 - (len(group) + 2)) / 2 * '*'
    group_asterisks = left_right_asterisks + ' ' + group + ' ' + left_right_asterisks
    asterisks = '*' * 80
    dest_file.write('\n' + '/{}/'.format(asterisks) + '\n')
    dest_file.write('/{}/'.format(group_asterisks) + '\n')
    dest_file.write('/{}/'.format(asterisks) + '\n')

def write_statement(dest_file, exists_sql, insert_sql):
    dest_file.write(exists_sql)
    dest_file.write('BEGIN\n')
    dest_file.write('\t' + insert_sql)
    dest_file.write('END\n')

counts = {
    'aspnet_applications': 0,
    'aspnet_groups': 0,
    'aspnet_roles': 0,
    'aspnet_roles_in_groups': 0,
    'aspnet_schema_versions': 0,
    'aspnet_users': 0,
    'aspnet_membership': 0,
    'aspnet_users_in_groups': 0,
    'kla_aspnet_profile': 0,
    }

if __name__ == '__main__':
    source_file = ''
    destination_file = 'LOTS_USERS_{}.sql'.format(str(date.today()).replace('-', ''))
    if (len(sys.argv) > 1):
        source_file = sys.argv[1]
        if (len(sys.argv) > 2):
            destination_file = sys.argv[2]
    else:
        print('Please provide source file and destination file like so:\n')
        print('\t ./user_role_migration.py source_file.sql destination_file.sql\n')
        print('If a destination file is not provided, one will be created with todays timestamp for you like "{}".'.format(destination_file))
        exit(1)
    file_cursor = -1
    with open(source_file, 'r') as sql_file_src:
        lines = sql_file_src.readlines()
        with open(destination_file, 'w') as sql_file_dest:
            sql_file_dest.write('/* Date: {} */\n'.format(str(date.today())))
            for line in lines:
                file_cursor += 1
                if 'dbo.aspnet_applications' in line.lower():
                    if counts['aspnet_applications'] == 0:
                        write_group_comment(sql_file_dest, 'aspnet_Applications')
                    line_values = find_line_and_values(lines, file_cursor)
                    if_not_exists = "IF NOT EXISTS (SELECT 1 FROM dbo.aspnet_Applications WHERE ApplicationId = '{}')\n".format(line_values[1][2])
                    write_statement(sql_file_dest, if_not_exists, line_values[0])
                    counts['aspnet_applications'] += 1

                if 'dbo.aspnet_groups' in line.lower():
                    if counts['aspnet_groups'] == 0:
                        write_group_comment(sql_file_dest, 'aspnet_Groups')
                    line_values = find_line_and_values(lines, file_cursor)
                    if_not_exists = "IF NOT EXISTS (SELECT 1 FROM dbo.aspnet_Groups WHERE GroupId = '{}')\n".format(line_values[1][1])
                    write_statement(sql_file_dest, if_not_exists, line_values[0])
                    counts['aspnet_groups'] += 1

                if 'dbo.aspnet_rolesingroups' in line.lower():
                    if counts['aspnet_roles_in_groups'] == 0:
                        write_group_comment(sql_file_dest, 'aspnet_RolesInGroups')
                    line_values = find_line_and_values(lines, file_cursor)
                    if_not_exists = "IF NOT EXISTS (SELECT 1 FROM dbo.aspnet_RolesInGroups WHERE GroupId = '{}' AND RoleId = '{}')\n".format(line_values[1][0], line_values[1][1])
                    write_statement(sql_file_dest, if_not_exists, line_values[0])
                    counts['aspnet_roles_in_groups'] += 1
                    continue

                if 'dbo.aspnet_roles' in line.lower():
                    if counts['aspnet_roles'] == 0:
                        write_group_comment(sql_file_dest, 'aspnet_Roles')
                    line_values = find_line_and_values(lines, file_cursor)
                    if_not_exists = "IF NOT EXISTS (SELECT 1 FROM dbo.aspnet_Roles WHERE RoleId = '{}')\n".format(line_values[1][1])
                    write_statement(sql_file_dest, if_not_exists, line_values[0])
                    counts['aspnet_roles'] += 1

                if 'dbo.aspnet_schemaversions' in line.lower():
                    if counts['aspnet_schema_versions'] == 0:
                        write_group_comment(sql_file_dest, 'aspnet_SchemaVersions')
                    line_values = find_line_and_values(lines, file_cursor)
                    if_not_exists = "IF NOT EXISTS (SELECT 1 FROM dbo.aspnet_SchemaVersions WHERE Feature = '{}' AND CompatibleSchemaVersion = '{}' )\n".format(line_values[1][0], line_values[1][1])
                    write_statement(sql_file_dest, if_not_exists, line_values[0])
                    counts['aspnet_schema_versions'] += 1

                if 'dbo.aspnet_usersingroups' in line.lower():
                    if counts['aspnet_users_in_groups'] == 0:
                        write_group_comment(sql_file_dest, 'aspnet_UsersInGroups')
                    line_values = find_line_and_values(lines, file_cursor)
                    if_not_exists = "IF NOT EXISTS (SELECT 1 FROM dbo.aspnet_UsersInGroups WHERE GroupId = '{}' AND UserId = '{}')\n".format(line_values[1][0], line_values[1][1])
                    write_statement(sql_file_dest, if_not_exists, line_values[0])
                    counts['aspnet_users_in_groups'] += 1
                    continue

                if 'dbo.aspnet_users' in line.lower():
                    if counts['aspnet_users'] == 0:
                        write_group_comment(sql_file_dest, 'aspnet_Users')
                    line_values = find_line_and_values(lines, file_cursor)
                    if_not_exists = "IF NOT EXISTS (SELECT 1 FROM dbo.aspnet_Users WHERE UserId = '{}')\n".format(line_values[1][1])
                    write_statement(sql_file_dest, if_not_exists, line_values[0])
                    counts['aspnet_users'] += 1

                if 'dbo.aspnet_membership' in line.lower():
                    if counts['aspnet_users'] == 0:
                        write_group_comment(sql_file_dest, 'aspnet_Membership')
                    line_values = find_line_and_values(lines, file_cursor)
                    if_not_exists = "IF NOT EXISTS (SELECT 1 FROM dbo.aspnet_Membership WHERE UserId = '{}')\n".format(line_values[1][1])
                    write_statement(sql_file_dest, if_not_exists, line_values[0])
                    counts['aspnet_membership'] += 1

                if 'dbo.kla_aspnet_profile' in line.lower():
                    if counts['kla_aspnet_profile'] == 0:
                        write_group_comment(sql_file_dest, 'KLA_ASPNET_PROFILE')
                    line_values = find_line_and_values(lines, file_cursor)
                    if_not_exists = "IF NOT EXISTS (SELECT 1 FROM dbo.KLA_ASPNET_PROFILE WHERE UserId = '{}')\n".format(line_values[1][0])
                    write_statement(sql_file_dest, if_not_exists, line_values[0])
                    counts['kla_aspnet_profile'] += 1

            sql_file_dest.close()
        sql_file_src.close()

    total_inserts = 0
    for count in counts:
        total_inserts += counts[count]
        print(count + ' count: ' + str(counts[count]))

    print('\nTotal inserts = ' + str(total_inserts))
    print('Output file = ' + destination_file)