Code Editor for app.py

import streamlit as st
import re

def convert_greenplum_to_teradata(greenplum_code, output_type):
    """
    Convert Greenplum function to Teradata Macro or Stored Procedure
    """
    # Common conversions
    # Replace PostgreSQL/Greenplum specific syntax with Teradata equivalents
    converted_code = greenplum_code
    
    # Replace CREATE OR REPLACE FUNCTION with appropriate Teradata syntax
    if output_type == "Macro":
        # Extract function name and parameters
        function_match = re.search(r'CREATE\s+(?:OR\s+REPLACE\s+)?FUNCTION\s+(\w+)\s*\((.*?)\)', 
                                  converted_code, re.IGNORECASE | re.DOTALL)
        
        if function_match:
            function_name = function_match.group(1)
            params = function_match.group(2)
            
            # Convert parameters format if needed
            params_converted = params.replace(' IN ', ' ')
            
            # Replace function declaration
            converted_code = re.sub(r'CREATE\s+(?:OR\s+REPLACE\s+)?FUNCTION\s+\w+\s*\(.*?\)',
                                   f'REPLACE MACRO {function_name}({params_converted})',
                                   converted_code, 
                                   flags=re.IGNORECASE | re.DOTALL)
        
        # Replace RETURNS with appropriate Teradata syntax
        converted_code = re.sub(r'RETURNS\s+(\w+(?:\s*\(\s*\d+\s*\))?)', 
                               r'RETURNS (\1)', 
                               converted_code, 
                               flags=re.IGNORECASE)
        
        # Replace AS $$ with AS (
        converted_code = re.sub(r'AS\s*\$\$', 'AS (', converted_code, flags=re.IGNORECASE)
        
        # Replace $$ at the end with );
        converted_code = re.sub(r'\$\$\s*;?\s*$', ');', converted_code, flags=re.IGNORECASE)
        
    elif output_type == "Store Proc":
        # Extract function name and parameters
        function_match = re.search(r'CREATE\s+(?:OR\s+REPLACE\s+)?FUNCTION\s+(\w+)\s*\((.*?)\)', 
                                  converted_code, re.IGNORECASE | re.DOTALL)
        
        if function_match:
            function_name = function_match.group(1)
            params = function_match.group(2)
            
            # Convert parameters format if needed
            params_converted = params.replace(' IN ', ' IN ')  # Keep IN parameters
            
            # Replace function declaration
            converted_code = re.sub(r'CREATE\s+(?:OR\s+REPLACE\s+)?FUNCTION\s+\w+\s*\(.*?\)',
                                   f'REPLACE PROCEDURE {function_name}({params_converted})',
                                   converted_code, 
                                   flags=re.IGNORECASE | re.DOTALL)
        
        # Replace RETURNS with appropriate Teradata syntax for OUT parameters
        returns_match = re.search(r'RETURNS\s+(\w+(?:\s*\(\s*\d+\s*\))?)', 
                                 converted_code, 
                                 flags=re.IGNORECASE)
        
        if returns_match:
            return_type = returns_match.group(1)
            converted_code = re.sub(r'RETURNS\s+\w+(?:\s*\(\s*\d+\s*\))?', 
                                   f'OUT result_value {return_type}', 
                                   converted_code, 
                                   flags=re.IGNORECASE)
        
        # Replace AS $$ with BEGIN
        converted_code = re.sub(r'AS\s*\$\$', 'BEGIN', converted_code, flags=re.IGNORECASE)
        
        # Replace $$ at the end with END;
        converted_code = re.sub(r'\$\$\s*;?\s*$', 'END;', converted_code, flags=re.IGNORECASE)
    
    # Common transformations for both output types
    
    # Replace DECLARE with DECLARE LOCAL TEMPORARY TABLE
    converted_code = re.sub(r'DECLARE\s+(\w+)\s+(\w+)',
                           r'DECLARE \1 \2',
                           converted_code,
                           flags=re.IGNORECASE)
    
    # Replace Greenplum's NOW() with Teradata's CURRENT_TIMESTAMP
    converted_code = re.sub(r'NOW\(\)', 'CURRENT_TIMESTAMP', converted_code, flags=re.IGNORECASE)
    
    # Replace double quotes for identifiers with square brackets
    # This is more complex and might need refinement based on actual code patterns
    converted_code = re.sub(r'"([^"]+)"', r'[\1]', converted_code)
    
    return converted_code

def main():
    st.title("Greenplum function to Teradata SQL Converter")
    
    col1, col2 = st.columns([3, 2])
    
    with col1:
        uploaded_file = st.file_uploader("Upload Greenplum function file", type=["sql", "txt"])
    
    with col2:
        output_type = st.selectbox("Output", ["Macro", "Store Proc"])
    
    if uploaded_file is not None:
        greenplum_code = uploaded_file.getvalue().decode("utf-8")
        st.text_area("Greenplum Function Code", greenplum_code, height=200)
    else:
        greenplum_code = ""
    
    col_btn1, col_btn2, col_btn3 = st.columns([3, 2, 3])
    
    with col_btn2:
        submit_button = st.button("Convert", use_container_width=True)
    
    if submit_button and greenplum_code:
        teradata_code = convert_greenplum_to_teradata(greenplum_code, output_type)
        st.subheader(f"Converted Teradata {output_type} Code")
        st.code(teradata_code, language="sql")
        
        st.download_button(
            label=f"Download Teradata {output_type}",
            data=teradata_code,
            file_name=f"teradata_{output_type.lower().replace(' ', '_')}.sql",
            mime="text/plain"
        )
    
    st.markdown("---")
    st.markdown("""
    ### Conversion Notes
    - This tool attempts to convert basic syntax patterns from Greenplum to Teradata
    - Some advanced features or specific syntax might require manual adjustment
    - Always review the converted code before using in production
    """)

if __name__ == "__main__":
    main()