sql-cli 1.73.1

"""Test string functions: MID, UPPER, LOWER, TRIM"""
import subprocess
import json
import pytest
import csv
import os
from pathlib import Path

def run_sql_cli(csv_file, query):
    """Run sql-cli with a query and return the output"""
    cmd = [
        "./target/release/sql-cli",
        csv_file,
        "-q", query,
        "-o", "json"
    ]
    result = subprocess.run(cmd, capture_output=True, text=True)
    if result.returncode != 0:
        print(f"Error running command: {' '.join(cmd)}")
        print(f"stderr: {result.stderr}")
        print(f"stdout: {result.stdout}")
        raise Exception(f"Command failed with return code {result.returncode}")
    
    try:
        return json.loads(result.stdout)
    except json.JSONDecodeError as e:
        print(f"Failed to parse JSON output: {result.stdout}")
        raise e

def setup_test_data():
    """Create a test CSV file with sample text data"""
    test_file = "data/test_strings.csv"
    os.makedirs("data", exist_ok=True)
    
    with open(test_file, 'w', newline='') as f:
        writer = csv.writer(f)
        writer.writerow(['id', 'name', 'description'])
        writer.writerow([1, 'Alice', '  Hello World  '])
        writer.writerow([2, 'Bob', 'Testing 123'])
        writer.writerow([3, 'Charlie', 'SQL Functions'])
        writer.writerow([4, 'David', '   Spaces   '])
        writer.writerow([5, 'Eve', 'lowercase text'])
        writer.writerow([6, 'Frank', 'UPPERCASE TEXT'])
    
    return test_file

def test_mid_function():
    """Test MID function for substring extraction"""
    test_file = setup_test_data()
    
    # Test basic MID function
    query = "SELECT id, name, MID(name, 1, 3) as first_three FROM test_strings WHERE id = 1"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['first_three'] == 'Ali'
    
    # Test MID with different positions
    query = "SELECT id, name, MID(name, 2, 2) as middle_two FROM test_strings WHERE id = 2"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['middle_two'] == 'ob'
    
    # Test MID beyond string length
    # NOTE: Empty string columns behavior varies - sometimes omitted, sometimes included as empty
    query = "SELECT id, name, MID(name, 10, 5) as beyond FROM test_strings WHERE id = 1"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    # The 'beyond' column should be empty string - handle both cases
    if 'beyond' in result[0]:
        assert result[0]['beyond'] == ''  # Column present with empty string
    # else: column omitted from output (also acceptable)
    
    # Test MID with exact remaining length
    query = "SELECT id, name, MID(name, 4, 10) as rest FROM test_strings WHERE id = 1"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['rest'] == 'ce'

def test_upper_function():
    """Test UPPER function for converting to uppercase"""
    test_file = setup_test_data()
    
    # Test UPPER on lowercase text
    query = "SELECT id, name, UPPER(name) as upper_name FROM test_strings WHERE id = 5"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['upper_name'] == 'EVE'
    
    # Test UPPER on mixed case
    query = "SELECT id, description, UPPER(description) as upper_desc FROM test_strings WHERE id = 1"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['upper_desc'] == '  HELLO WORLD  '
    
    # Test UPPER on already uppercase
    query = "SELECT id, description, UPPER(description) as upper_desc FROM test_strings WHERE id = 6"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['upper_desc'] == 'UPPERCASE TEXT'

def test_lower_function():
    """Test LOWER function for converting to lowercase"""
    test_file = setup_test_data()
    
    # Test LOWER on uppercase text
    query = "SELECT id, description, LOWER(description) as lower_desc FROM test_strings WHERE id = 6"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['lower_desc'] == 'uppercase text'
    
    # Test LOWER on mixed case
    query = "SELECT id, name, LOWER(name) as lower_name FROM test_strings WHERE id = 3"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['lower_name'] == 'charlie'
    
    # Test LOWER preserves spaces
    query = "SELECT id, description, LOWER(description) as lower_desc FROM test_strings WHERE id = 1"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['lower_desc'] == '  hello world  '

def test_trim_function():
    """Test TRIM function for removing leading/trailing spaces"""
    test_file = setup_test_data()
    
    # Test TRIM with leading and trailing spaces
    query = "SELECT id, description, TRIM(description) as trimmed FROM test_strings WHERE id = 1"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['trimmed'] == 'Hello World'
    
    # Test TRIM with multiple spaces
    query = "SELECT id, description, TRIM(description) as trimmed FROM test_strings WHERE id = 4"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['trimmed'] == 'Spaces'
    
    # Test TRIM on text without spaces
    query = "SELECT id, description, TRIM(description) as trimmed FROM test_strings WHERE id = 2"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['trimmed'] == 'Testing 123'

def test_combined_string_functions():
    """Test combining multiple string functions"""
    test_file = setup_test_data()
    
    # Test UPPER(TRIM())
    query = "SELECT id, description, UPPER(TRIM(description)) as processed FROM test_strings WHERE id = 1"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['processed'] == 'HELLO WORLD'
    
    # Test MID on UPPER
    query = "SELECT id, name, MID(UPPER(name), 1, 3) as first_upper FROM test_strings WHERE id = 1"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['first_upper'] == 'ALI'
    
    # Test TRIM(LOWER())
    query = "SELECT id, description, TRIM(LOWER(description)) as processed FROM test_strings WHERE id = 4"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['processed'] == 'spaces'
    
    # Test complex combination: MID(UPPER(TRIM()))
    query = "SELECT id, description, MID(UPPER(TRIM(description)), 1, 5) as complex FROM test_strings WHERE id = 1"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['complex'] == 'HELLO'

def test_string_functions_with_expressions():
    """Test string functions with expressions and arithmetic"""
    test_file = setup_test_data()
    
    # Test MID with calculated position
    query = "SELECT id, name, MID(name, id, 2) as dynamic_mid FROM test_strings WHERE id = 2"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['dynamic_mid'] == 'ob'  # Starting at position 2
    
    # Test MID with calculated length
    query = "SELECT id, name, MID(name, 1, id + 1) as dynamic_length FROM test_strings WHERE id = 3"
    result = run_sql_cli(test_file, query)
    assert len(result) == 1
    assert result[0]['dynamic_length'] == 'Char'  # Length = 3 + 1 = 4

if __name__ == "__main__":
    # Run tests manually
    test_mid_function()
    print("✓ MID function tests passed")
    
    test_upper_function()
    print("✓ UPPER function tests passed")
    
    test_lower_function()
    print("✓ LOWER function tests passed")
    
    test_trim_function()
    print("✓ TRIM function tests passed")
    
    test_combined_string_functions()
    print("✓ Combined string function tests passed")
    
    test_string_functions_with_expressions()
    print("✓ String functions with expressions tests passed")
    
    print("\nAll string function tests passed!")