Code Editor for app.py

import streamlit as st
import pandas as pd
import re

st.set_page_config(page_title="Greenplum to Teradata SQL Converter", layout="wide")

st.header("Greenplum function to Teradata SQL Converter")

col1, col2 = st.columns(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"])

def convert_to_teradata(greenplum_content, output_type):
    """
    Convert Greenplum function to Teradata Macro or Stored Procedure
    """
    # Remove comments
    content = re.sub(r'--.*?\n', '\n', greenplum_content)
    
    # Check if it's a function
    if not re.search(r'CREATE\s+(?:OR\s+REPLACE\s+)?FUNCTION', content, re.IGNORECASE):
        return "Error: The uploaded file does not contain a Greenplum function."
    
    # Extract function name
    function_name_match = re.search(r'FUNCTION\s+(\w+)', content, re.IGNORECASE)
    function_name = function_name_match.group(1) if function_name_match else "unknown_function"
    
    # Extract parameters
    params_match = re.search(r'\((.*?)\)', content, re.DOTALL)
    params = params_match.group(1) if params_match else ""
    
    # Extract return type
    return_type_match = re.search(r'RETURNS\s+([\w\s]+)', content, re.IGNORECASE)
    return_type = return_type_match.group(1) if return_type_match else "UNKNOWN"
    
    # Extract function body
    body_match = re.search(r'AS\s+\$\$\s+(.*?)\$\$', content, re.DOTALL | re.IGNORECASE)
    if not body_match:
        body_match = re.search(r'AS\s+\'(.*?)\'', content, re.DOTALL | re.IGNORECASE)
    body = body_match.group(1) if body_match else ""
    
    # Convert SQL syntax from Greenplum to Teradata
    body = body.replace('EXECUTE', 'EXEC')
    body = re.sub(r'SELECT\s+INTO', 'SEL', body, flags=re.IGNORECASE)
    body = body.replace('RETURNING', 'RETURNING INTO')
    
    if output_type == "Macro":
        # Convert parameters for Macro
        param_list = []
        if params:
            for param in params.split(','):
                param = param.strip()
                param_parts = param.split()
                if len(param_parts) >= 2:
                    param_name = param_parts[0]
                    param_list.append(param_name)
        
        macro_params = ', '.join(param_list)
        
        # Create Teradata Macro
        teradata_sql = f"""REPLACE MACRO {function_name}({macro_params})
AS (
{body}
);"""
        
    else:  # Store Proc
        # Convert parameters for Stored Procedure
        proc_params = []
        if params:
            for param in params.split(','):
                param = param.strip()
                param_parts = param.split()
                if len(param_parts) >= 2:
                    param_name = param_parts[0]
                    param_type = ' '.join(param_parts[1:])
                    
                    # Convert Greenplum types to Teradata types
                    if 'varchar' in param_type.lower():
                        td_type = param_type.lower().replace('varchar', 'VARCHAR')
                    elif 'integer' in param_type.lower():
                        td_type = 'INTEGER'
                    elif 'numeric' in param_type.lower():
                        td_type = 'DECIMAL'
                    elif 'boolean' in param_type.lower():
                        td_type = 'BYTEINT'
                    elif 'date' in param_type.lower():
                        td_type = 'DATE'
                    elif 'timestamp' in param_type.lower():
                        td_type = 'TIMESTAMP'
                    else:
                        td_type = param_type
                    
                    # Determine IN/OUT based on parameter name prefix
                    direction = "IN"
                    if param_name.startswith("p_out") or param_name.startswith("out_"):
                        direction = "OUT"
                    elif param_name.startswith("p_inout") or param_name.startswith("inout_"):
                        direction = "INOUT"
                    
                    proc_params.append(f"{direction} {param_name} {td_type}")
                    
        proc_param_str = ', '.join(proc_params)
        
        # Convert return type
        td_return_type = return_type
        if 'varchar' in return_type.lower():
            td_return_type = return_type.lower().replace('varchar', 'VARCHAR')
        elif 'integer' in return_type.lower():
            td_return_type = 'INTEGER'
        elif 'numeric' in return_type.lower():
            td_return_type = 'DECIMAL'
        elif 'boolean' in return_type.lower():
            td_return_type = 'BYTEINT'
        
        # Create Teradata Stored Procedure
        teradata_sql = f"""REPLACE PROCEDURE {function_name}({proc_param_str})
RETURNS {td_return_type}
LANGUAGE SQL
BEGIN
{body}
END;"""
    
    return teradata_sql

# Center the submit button
col1, col2, col3 = st.columns([1, 1, 1])

with col2:
    submit_button = st.button("Convert", type="primary")

if submit_button and uploaded_file is not None:
    # Read file content
    content = uploaded_file.read().decode("utf-8")
    
    # Convert to Teradata SQL
    teradata_sql = convert_to_teradata(content, output_type)
    
    st.subheader(f"Converted Teradata {output_type}")
    st.code(teradata_sql, language="sql")
    
    # Add download button for the converted SQL
    st.download_button(
        label=f"Download Teradata {output_type}",
        data=teradata_sql,
        file_name=f"converted_teradata_{output_type.lower()}.sql",
        mime="text/plain",
    )
elif submit_button and uploaded_file is None:
    st.error("Please upload a Greenplum function file.")

# Add a section with conversion notes
with st.expander("Conversion Notes"):
    st.markdown("""
    ### Greenplum to Teradata Conversion Notes
    
    This tool performs basic conversion from Greenplum functions to Teradata macros or stored procedures.
    
    #### Key Differences:
    
    - **Syntax**: Teradata uses different SQL syntax for many operations
    - **Parameters**: Parameter handling differs between platforms
    - **Data Types**: Some data types have different names or behavior
    - **Functions**: Built-in functions may have different names or parameters
    
    #### Limitations:
    
    - Complex functions may require manual adjustments after conversion
    - Some Greenplum-specific features might not have direct Teradata equivalents
    - Error handling mechanisms differ between platforms
    - The converter makes best-effort transformations but may not handle all edge cases
    
    Always review and test the converted code before using in a production environment.
    """)