from re import compile, VERBOSE, IGNORECASE, MULTILINE

# The T-SQL SET commands and their equivalents in Postgres
# These regular expressions are compiled later on with the re.X (== re.VERBOSE) flag set,
# so the whitespace and text after the # sign are ignored
subs = {
    # Keeping the regex version of these substitutions in case the logic gets more complicated
    #
    # r"""               # The outermost parentheses capture the entire line
    #     ((?:nocount    # NOCOUNT - do not output the number of affected rows
    #     |ansi_nulls    # ANSI_NULLS - only allow comparing to NULL using `is` and `is not` (not comparison operators)
    #     |quoted_       # QUOTED_IDENTIFIER - allow using double quotes around identifiers
    #     identifier)    #
    #     \s+o(?:n|ff))  # ON|OFF
    # """:
    #     # The above flags have no corresponding flags in Postgres (basically, they are all ON in Postgres),
    #     # so we comment out the line adding the note that it came from T-SQL
    #     r'-- (from T-SQL) SET \1;',
    #
    #
    # r"""
    #     (identity_insert\s+  # IDENTITY_INSERT
    #     (?:\[(\w+)\]\.)?     # [db].     (optional)
    #     (?:\[(\w+)\]\.)?     # [schema]. (optional)
    #     \[(\w+)\]            # [table]
    #     \s+off)              # OFF
    # """:
    #     # This flag doesn't have a corresponding flag in Postgres,
    #     # so we comment out the line adding the note that it came from T-SQL
    #     r'-- (from T-SQL) SET \1;',


    r"""
        (identity_insert\s+  # IDENTITY_INSERT - allow manually inserting explicit values into the ID (`serial`)
                             #                   column of the table (i.e., inserting records with ID not equal to
                             #                   max(ID) + 1)
                             #                   Postgres always allows using explicit values for the ID column,
                             #                   but doing so changes the ID column's counter
                             #                   (it's the last inserted ID value).
                             #                   To make sure the implicit values are correct,
                             #                   after finishing inserting explicit values the ID counter's value needs
                             #                   to be reset to max(ID).
        (?:\[(\w+)\]\.)?     # [db].     (optional)
        (?:\[(\w+)\]\.)?     # [schema]. (optional)
        \[(\w+)\]            # [table]
        \s+on)               # ON
    """:
        lambda match: replace_identity_insert(match)
}


def replace_identity_insert(match):
    """
        Generates a string for the `SET IDENTITY_INSERT <table> ON` command

        :param match: the match object generated by the regex that matched the `SET` command
        :return: the generated string to insert into Postgres
        :rtype: str
    """
    groups = match.groups()

    qualified_table = '"' + groups[-1] + '"'
    if len(groups) > 1 and groups[-2]:
        qualified_table = '"' + groups[-2] + '".' + qualified_table
    if len(groups) > 2 and groups[-3]:
        qualified_table = '"' + groups[-3] + '".' + qualified_table

    return \
        '-- Add the following after explicitly inserting values into the serial column of ' + qualified_table + '\n' + \
        '-- (replacing <id_column_name> with the name of the serial column):\n' + \
        '-- SELECT setval(\n' + \
        '--    pg_get_serial_sequence(' + qualified_table + ', <id_column_name>),\n' + \
        '--    (SELECT MAX(<id_column_name>) FROM ' + qualified_table + '\n' + \
        '-- );'


# The same substitutions as pairs (compiled regex, replacement)
# The regular expressions are compiled with the following flags:
#   re.I - ignore case
#   re.X - verbose - ignore whitespace and comments
compiled_subs = [(compile(pattern, IGNORECASE | VERBOSE | MULTILINE), subs[pattern]) for pattern in subs]


def set_psql(tsql):
    """
        Translates the `SET` statement from T-SQL to PostgresQL.

        :param tsql: The `SET` statement in T-SQL (i.e., SQL Server) syntax
        :type tsql: str
        :return: The equivalent statement in PostgresQL syntax if one exists
        :rtype: str
    """
    global compiled_subs

    # Part of the string following the `SET` keyword
    parameters = tsql.split(maxsplit=1)[1]

    # See if there's a pattern that matches the parameters
    for regex, replacement in compiled_subs:
        # If so, apply the corresponding substitution
        if regex.match(parameters):
            return regex.sub(replacement, parameters)

    # By default, just comment out the line adding the note that it came from T-SQL
    return '-- (from T-SQL) SET ' + parameters.replace('\n', '\n--              ') + ';'

1cdd46bbfcd4fe830570f2a0412e2d07292d6fcf
<mask token>


def replace_identity_insert(match):
    """
        Generates a string for the `SET IDENTITY_INSERT <table> ON` command

        :param match: the match object generated by the regex that matched the `SET` command
        :return: the generated string to insert into Postgres
        :rtype: str
    """
    groups = match.groups()
    qualified_table = '"' + groups[-1] + '"'
    if len(groups) > 1 and groups[-2]:
        qualified_table = '"' + groups[-2] + '".' + qualified_table
    if len(groups) > 2 and groups[-3]:
        qualified_table = '"' + groups[-3] + '".' + qualified_table
    return (
        '-- Add the following after explicitly inserting values into the serial column of '
         + qualified_table + '\n' +
        """-- (replacing <id_column_name> with the name of the serial column):
"""
         + '-- SELECT setval(\n' + '--    pg_get_serial_sequence(' +
        qualified_table + """, <id_column_name>),
""" +
        '--    (SELECT MAX(<id_column_name>) FROM ' + qualified_table +
        '\n' + '-- );')


<mask token>

<mask token>


def replace_identity_insert(match):
    """
        Generates a string for the `SET IDENTITY_INSERT <table> ON` command

        :param match: the match object generated by the regex that matched the `SET` command
        :return: the generated string to insert into Postgres
        :rtype: str
    """
    groups = match.groups()
    qualified_table = '"' + groups[-1] + '"'
    if len(groups) > 1 and groups[-2]:
        qualified_table = '"' + groups[-2] + '".' + qualified_table
    if len(groups) > 2 and groups[-3]:
        qualified_table = '"' + groups[-3] + '".' + qualified_table
    return (
        '-- Add the following after explicitly inserting values into the serial column of '
         + qualified_table + '\n' +
        """-- (replacing <id_column_name> with the name of the serial column):
"""
         + '-- SELECT setval(\n' + '--    pg_get_serial_sequence(' +
        qualified_table + """, <id_column_name>),
""" +
        '--    (SELECT MAX(<id_column_name>) FROM ' + qualified_table +
        '\n' + '-- );')


<mask token>


def set_psql(tsql):
    """
        Translates the `SET` statement from T-SQL to PostgresQL.

        :param tsql: The `SET` statement in T-SQL (i.e., SQL Server) syntax
        :type tsql: str
        :return: The equivalent statement in PostgresQL syntax if one exists
        :rtype: str
    """
    global compiled_subs
    parameters = tsql.split(maxsplit=1)[1]
    for regex, replacement in compiled_subs:
        if regex.match(parameters):
            return regex.sub(replacement, parameters)
    return '-- (from T-SQL) SET ' + parameters.replace('\n',
        '\n--              ') + ';'

<mask token>
subs = {
    """
        (identity_insert\\s+  # IDENTITY_INSERT - allow manually inserting explicit values into the ID (`serial`)
                             #                   column of the table (i.e., inserting records with ID not equal to
                             #                   max(ID) + 1)
                             #                   Postgres always allows using explicit values for the ID column,
                             #                   but doing so changes the ID column's counter
                             #                   (it's the last inserted ID value).
                             #                   To make sure the implicit values are correct,
                             #                   after finishing inserting explicit values the ID counter's value needs
                             #                   to be reset to max(ID).
        (?:\\[(\\w+)\\]\\.)?     # [db].     (optional)
        (?:\\[(\\w+)\\]\\.)?     # [schema]. (optional)
        \\[(\\w+)\\]            # [table]
        \\s+on)               # ON
    """
    : lambda match: replace_identity_insert(match)}


def replace_identity_insert(match):
    """
        Generates a string for the `SET IDENTITY_INSERT <table> ON` command

        :param match: the match object generated by the regex that matched the `SET` command
        :return: the generated string to insert into Postgres
        :rtype: str
    """
    groups = match.groups()
    qualified_table = '"' + groups[-1] + '"'
    if len(groups) > 1 and groups[-2]:
        qualified_table = '"' + groups[-2] + '".' + qualified_table
    if len(groups) > 2 and groups[-3]:
        qualified_table = '"' + groups[-3] + '".' + qualified_table
    return (
        '-- Add the following after explicitly inserting values into the serial column of '
         + qualified_table + '\n' +
        """-- (replacing <id_column_name> with the name of the serial column):
"""
         + '-- SELECT setval(\n' + '--    pg_get_serial_sequence(' +
        qualified_table + """, <id_column_name>),
""" +
        '--    (SELECT MAX(<id_column_name>) FROM ' + qualified_table +
        '\n' + '-- );')


compiled_subs = [(compile(pattern, IGNORECASE | VERBOSE | MULTILINE), subs[
    pattern]) for pattern in subs]


def set_psql(tsql):
    """
        Translates the `SET` statement from T-SQL to PostgresQL.

        :param tsql: The `SET` statement in T-SQL (i.e., SQL Server) syntax
        :type tsql: str
        :return: The equivalent statement in PostgresQL syntax if one exists
        :rtype: str
    """
    global compiled_subs
    parameters = tsql.split(maxsplit=1)[1]
    for regex, replacement in compiled_subs:
        if regex.match(parameters):
            return regex.sub(replacement, parameters)
    return '-- (from T-SQL) SET ' + parameters.replace('\n',
        '\n--              ') + ';'

from re import compile, VERBOSE, IGNORECASE, MULTILINE
subs = {
    """
        (identity_insert\\s+  # IDENTITY_INSERT - allow manually inserting explicit values into the ID (`serial`)
                             #                   column of the table (i.e., inserting records with ID not equal to
                             #                   max(ID) + 1)
                             #                   Postgres always allows using explicit values for the ID column,
                             #                   but doing so changes the ID column's counter
                             #                   (it's the last inserted ID value).
                             #                   To make sure the implicit values are correct,
                             #                   after finishing inserting explicit values the ID counter's value needs
                             #                   to be reset to max(ID).
        (?:\\[(\\w+)\\]\\.)?     # [db].     (optional)
        (?:\\[(\\w+)\\]\\.)?     # [schema]. (optional)
        \\[(\\w+)\\]            # [table]
        \\s+on)               # ON
    """
    : lambda match: replace_identity_insert(match)}


def replace_identity_insert(match):
    """
        Generates a string for the `SET IDENTITY_INSERT <table> ON` command

        :param match: the match object generated by the regex that matched the `SET` command
        :return: the generated string to insert into Postgres
        :rtype: str
    """
    groups = match.groups()
    qualified_table = '"' + groups[-1] + '"'
    if len(groups) > 1 and groups[-2]:
        qualified_table = '"' + groups[-2] + '".' + qualified_table
    if len(groups) > 2 and groups[-3]:
        qualified_table = '"' + groups[-3] + '".' + qualified_table
    return (
        '-- Add the following after explicitly inserting values into the serial column of '
         + qualified_table + '\n' +
        """-- (replacing <id_column_name> with the name of the serial column):
"""
         + '-- SELECT setval(\n' + '--    pg_get_serial_sequence(' +
        qualified_table + """, <id_column_name>),
""" +
        '--    (SELECT MAX(<id_column_name>) FROM ' + qualified_table +
        '\n' + '-- );')


compiled_subs = [(compile(pattern, IGNORECASE | VERBOSE | MULTILINE), subs[
    pattern]) for pattern in subs]


def set_psql(tsql):
    """
        Translates the `SET` statement from T-SQL to PostgresQL.

        :param tsql: The `SET` statement in T-SQL (i.e., SQL Server) syntax
        :type tsql: str
        :return: The equivalent statement in PostgresQL syntax if one exists
        :rtype: str
    """
    global compiled_subs
    parameters = tsql.split(maxsplit=1)[1]
    for regex, replacement in compiled_subs:
        if regex.match(parameters):
            return regex.sub(replacement, parameters)
    return '-- (from T-SQL) SET ' + parameters.replace('\n',
        '\n--              ') + ';'

from re import compile, VERBOSE, IGNORECASE, MULTILINE

# The T-SQL SET commands and their equivalents in Postgres
# These regular expressions are compiled later on with the re.X (== re.VERBOSE) flag set,
# so the whitespace and text after the # sign are ignored
subs = {
    # Keeping the regex version of these substitutions in case the logic gets more complicated
    #
    # r"""               # The outermost parentheses capture the entire line
    #     ((?:nocount    # NOCOUNT - do not output the number of affected rows
    #     |ansi_nulls    # ANSI_NULLS - only allow comparing to NULL using `is` and `is not` (not comparison operators)
    #     |quoted_       # QUOTED_IDENTIFIER - allow using double quotes around identifiers
    #     identifier)    #
    #     \s+o(?:n|ff))  # ON|OFF
    # """:
    #     # The above flags have no corresponding flags in Postgres (basically, they are all ON in Postgres),
    #     # so we comment out the line adding the note that it came from T-SQL
    #     r'-- (from T-SQL) SET \1;',
    #
    #
    # r"""
    #     (identity_insert\s+  # IDENTITY_INSERT
    #     (?:\[(\w+)\]\.)?     # [db].     (optional)
    #     (?:\[(\w+)\]\.)?     # [schema]. (optional)
    #     \[(\w+)\]            # [table]
    #     \s+off)              # OFF
    # """:
    #     # This flag doesn't have a corresponding flag in Postgres,
    #     # so we comment out the line adding the note that it came from T-SQL
    #     r'-- (from T-SQL) SET \1;',


    r"""
        (identity_insert\s+  # IDENTITY_INSERT - allow manually inserting explicit values into the ID (`serial`)
                             #                   column of the table (i.e., inserting records with ID not equal to
                             #                   max(ID) + 1)
                             #                   Postgres always allows using explicit values for the ID column,
                             #                   but doing so changes the ID column's counter
                             #                   (it's the last inserted ID value).
                             #                   To make sure the implicit values are correct,
                             #                   after finishing inserting explicit values the ID counter's value needs
                             #                   to be reset to max(ID).
        (?:\[(\w+)\]\.)?     # [db].     (optional)
        (?:\[(\w+)\]\.)?     # [schema]. (optional)
        \[(\w+)\]            # [table]
        \s+on)               # ON
    """:
        lambda match: replace_identity_insert(match)
}


def replace_identity_insert(match):
    """
        Generates a string for the `SET IDENTITY_INSERT <table> ON` command

        :param match: the match object generated by the regex that matched the `SET` command
        :return: the generated string to insert into Postgres
        :rtype: str
    """
    groups = match.groups()

    qualified_table = '"' + groups[-1] + '"'
    if len(groups) > 1 and groups[-2]:
        qualified_table = '"' + groups[-2] + '".' + qualified_table
    if len(groups) > 2 and groups[-3]:
        qualified_table = '"' + groups[-3] + '".' + qualified_table

    return \
        '-- Add the following after explicitly inserting values into the serial column of ' + qualified_table + '\n' + \
        '-- (replacing <id_column_name> with the name of the serial column):\n' + \
        '-- SELECT setval(\n' + \
        '--    pg_get_serial_sequence(' + qualified_table + ', <id_column_name>),\n' + \
        '--    (SELECT MAX(<id_column_name>) FROM ' + qualified_table + '\n' + \
        '-- );'


# The same substitutions as pairs (compiled regex, replacement)
# The regular expressions are compiled with the following flags:
#   re.I - ignore case
#   re.X - verbose - ignore whitespace and comments
compiled_subs = [(compile(pattern, IGNORECASE | VERBOSE | MULTILINE), subs[pattern]) for pattern in subs]


def set_psql(tsql):
    """
        Translates the `SET` statement from T-SQL to PostgresQL.

        :param tsql: The `SET` statement in T-SQL (i.e., SQL Server) syntax
        :type tsql: str
        :return: The equivalent statement in PostgresQL syntax if one exists
        :rtype: str
    """
    global compiled_subs

    # Part of the string following the `SET` keyword
    parameters = tsql.split(maxsplit=1)[1]

    # See if there's a pattern that matches the parameters
    for regex, replacement in compiled_subs:
        # If so, apply the corresponding substitution
        if regex.match(parameters):
            return regex.sub(replacement, parameters)

    # By default, just comment out the line adding the note that it came from T-SQL
    return '-- (from T-SQL) SET ' + parameters.replace('\n', '\n--              ') + ';'
