Tetsing the Regex Lexing in SQL

import re
column_attributes = (
        'DEFAULT|'
        'IDENTITY\s*?(\(.*?\))?|'
        'ENCODE|'
        'DISTKEY|'
        'SORTKEY'
    )
column_constraints = (
        'NOT\s+NULL|NULL|'
        'UNIQUE|PRIMARY\s+KEY|'
        'REFERENCES'
    )
column_encodings = (
        'BYTEDICT|DELTA|DELTA32K|LZO|MOSTLY8|MOSTLY16|MOSTLY32|RAW|RUNLENGTH|'
        'TEXT255|TEXT32K'
    )
replace_identity =  re.compile(
            '(IDENTITY|IDENTITY\s*\(.*?\))\s*(?=(,|\)|(({}|{})+'
            '(?=(\s|,|\))+))))'.format(
                column_attributes, column_constraints), flags=re.I)
test_cases = [{
        'actual': (
                   'CREATE TABLE foo('
                   '   col1 int,'
                   '   col2 int IDENTITY(0,1)'
                   ') '),
        'expected': ('CREATE TABLE foo('
                     '   col1 int,'
                     '   col2 int SERIAL '
                     ') ')
        },{
         'actual': (
                   'CREATE TABLE foo('
                   '   col1 int,'
                   '   col2 int\n   IDENTITY(0,1) PRIMARY KEY'
                   ') '),
        'expected': ('CREATE TABLE foo('
                     '   col1 int,'
                     '   col2 int\n   SERIAL PRIMARY KEY'
                     ') ')
        },
        {
         'actual': (
                    'CREATE TABLE staging.lnrt_student_parameters (\n'
                    'user_id         CHAR(36),\n'
                    'unit_id         CHAR(36),\n'
                    'subgraph_id     CHAR(36),\n'
                    'log_time        TIMESTAMP,\n'
                    'speed_r         FLOAT8,\n'
                    'speed_q         FLOAT8,\n'
                    'quit_duration   FLOAT8,\n'
                    'tag_rq_speed    VARCHAR(8000),\n'
                    'id              INT IDENTITY(1,1)\n'
					')\n'
					'DISTKEY(unit_id)\n'
					'SORTKEY(log_time);'
                    ),
         'expected': (
                      'CREATE TABLE staging.lnrt_student_parameters (\n'
                    'user_id         CHAR(36),\n'
                    'unit_id         CHAR(36),\n'
                    'subgraph_id     CHAR(36),\n'
                    'log_time        TIMESTAMP,\n'
                    'speed_r         FLOAT8,\n'
                    'speed_q         FLOAT8,\n'
                    'quit_duration   FLOAT8,\n'
                    'tag_rq_speed    VARCHAR(8000),\n'
                    'id              INT SERIAL '
					')\n'
					'DISTKEY(unit_id)\n'
					'SORTKEY(log_time);'
                      )
         }
		]

for test_case in test_cases:
	print "*Substituted:\n", replace_identity.sub('SERIAL ', test_case['actual'])
	print "**Expected:\n", test_case['expected']
	assert replace_identity.sub('SERIAL ', test_case['actual']) == test_case['expected']
	print "\n"

Python Fiddle

Python Cloud IDE