check_sql_project.py - main - dcantuta/sql-project-checker

check_sql_project.py
204 строки7 KB
worddragon
Initial SQL project checker
19 май 2026, 21:19
19 май 2026, 21:198758dc9
# check_sql_project.py
# SQL Project Checker CLI Tool
# Analyzes SQL files and reports structure of student database projects.

import argparse
import re
import sys
from pathlib import Path


def analyze_sql_file(file_path: Path) -> dict:
    """
    Analyze SQL file and count key structural elements.
    Returns a dictionary with counts and statuses.
    """
    # Initialize results
    report = {
        'create_table': 0,
        'primary_key': 0,
        'foreign_key': 0,
        'insert': 0,
        'select': 0,
        'function': 0,
        'trigger': 0,
        'view': 0
    }

    try:
        content = file_path.read_text(encoding='utf-8')
    except Exception as e:
        print(f"Error reading file {file_path}: {e}", file=sys.stderr)
        sys.exit(1)

    # If file is empty
    if not content.strip():
        print(f"Error: File {file_path} is empty.", file=sys.stderr)
        sys.exit(1)

    # Remove single-line comments (--)
    content = re.sub(r'--.*$', '', content, flags=re.MULTILINE)
    # Remove multi-line comments (/* ... */)
    content = re.sub(r'/\*.*?\*/', '', content, flags=re.DOTALL | re.IGNORECASE)

    # Convert to uppercase for case-insensitive matching
    content_upper = content.upper()

    # Count CREATE TABLE
    report['create_table'] = len(re.findall(r'CREATE\s+TABLE\s+', content_upper))

    # Count PRIMARY KEY (both inline and table-level)
    report['primary_key'] = len(re.findall(r'PRIMARY\s+KEY\s*(?:\([^)]+\)|\b)', content_upper))

    # Count FOREIGN KEY
    report['foreign_key'] = len(re.findall(r'FOREIGN\s+KEY\s*', content_upper))

    # Count INSERT
    report['insert'] = len(re.findall(r'INSERT\s+INTO\s+', content_upper))

    # Count CREATE VIEW
    report['view'] = len(re.findall(r'CREATE\s+VIEW\s+', content_upper))

    # Remove CREATE VIEW blocks to avoid counting internal SELECTs
    view_pattern = r'CREATE\s+VIEW\s+[\w_"]+\s*(\([^)]*\))?\s+AS\s+[^;]*;'
    content_no_view = re.sub(view_pattern, '', content, flags=re.IGNORECASE | re.DOTALL)

    # Convert cleaned content to uppercase
    content_no_view_upper = content_no_view.upper()

    # Count SELECT statements (now only outside of views)
    report['select'] = len(re.findall(r'\bSELECT\s+', content_no_view_upper))

    # Count CREATE FUNCTION
    report['function'] = len(re.findall(r'CREATE\s+FUNCTION\s+', content_upper))

    # Count CREATE TRIGGER
    report['trigger'] = len(re.findall(r'CREATE\s+TRIGGER\s+', content_upper))

    return report


def generate_report(report: dict, output: bool = False, sql_path: Path = None) -> str:
    """Generate formatted text report from analysis results."""
    lines = [
        "---------------------------------",
        "SQL Project Structure Report",
        "---------------------------------\n"
    ]

    # Status mapping: (status, message)
    status_map = []

    # Check each component
    if report['create_table'] > 0:
        status_map.append(("OK", f"CREATE TABLE found: {report['create_table']}", ""))
    else:
        status_map.append(("WARNING", "CREATE TABLE not found", "This is a critical component."))

    if report['primary_key'] > 0:
        status_map.append(("OK", f"PRIMARY KEY found: {report['primary_key']}", ""))
    else:
        status_map.append(("WARNING", "PRIMARY KEY not found", "Each table should have a primary key."))

    if report['foreign_key'] > 0:
        status_map.append(("OK", f"FOREIGN KEY found: {report['foreign_key']}", ""))
    else:
        status_map.append(("WARNING", "FOREIGN KEY not found", "Consider adding relationships between tables."))

    if report['insert'] > 0:
        status_map.append(("OK", f"INSERT statements found: {report['insert']}", ""))
    else:
        status_map.append(("WARNING", "INSERT statements not found", "No test data provided."))

    if report['select'] > 0:
        status_map.append(("OK", f"SELECT queries found: {report['select']}", ""))
    else:
        status_map.append(("WARNING", "SELECT queries not found", "No example queries provided."))

    if report['function'] > 0:
        status_map.append(("OK", f"Functions found: {report['function']}", ""))
    else:
        status_map.append(("INFO", "Functions not found", "Optional for basic projects."))

    if report['trigger'] > 0:
        status_map.append(("OK", f"Triggers found: {report['trigger']}", ""))
    else:
        status_map.append(("INFO", "Triggers not found", "Optional for basic projects."))

    if report['view'] > 0:
        status_map.append(("OK", f"Views found: {report['view']}", ""))
    else:
        status_map.append(("WARNING", "Views not found", "Optional, but recommended for complex queries."))

    # Add status lines
    for status, message, hint in status_map:
        lines.append(f"[{status}] {message}")
        if hint:
            lines.append(f"    Hint: {hint}")

    # Overall status
    lines.append("\nOverall status:")
    critical_missing = report['create_table'] == 0
    if critical_missing:
        lines.append("Project structure is incomplete. Missing essential components.")
    else:
        lines.append("Project structure is acceptable.")

    # Final report text
    report_text = "\n".join(lines)

    # Output to console
    print(report_text)

    # Save to file if requested
    if output and sql_path:
        # Ensure reports directory exists
        reports_dir = Path("reports")
        reports_dir.mkdir(exist_ok=True)
        # Generate filename: e.g., good_project.sql -> good_project_report.txt
        report_name = f"{sql_path.stem}_report.txt"
        report_path = reports_dir / report_name
        try:
            report_path.write_text(report_text, encoding='utf-8')
            print(f"\nReport saved to {report_path.resolve()}")
        except Exception as e:
            print(f"Error saving report: {e}", file=sys.stderr)

    return report_text


def main():
    parser = argparse.ArgumentParser(
        description="Analyze student SQL project files and generate structure reports."
    )
    parser.add_argument(
        "sql_file",
        type=str,
        help="Path to the SQL file to analyze"
    )
    parser.add_argument(
        "-o", "--output",
        action="store_true",
        help="Save report automatically to reports/ folder with auto-generated name (optional)"
    )

    args = parser.parse_args()

    sql_path = Path(args.sql_file)

    # Validate file
    if not sql_path.exists():
        print(f"Error: File '{sql_path}' does not exist.", file=sys.stderr)
        sys.exit(1)

    if not sql_path.suffix.lower() == '.sql':
        print(f"Error: File '{sql_path}' is not a .sql file.", file=sys.stderr)
        sys.exit(1)

    # Analyze and report
    report_data = analyze_sql_file(sql_path)
    generate_report(report_data, output=args.output, sql_path=sql_path)


if __name__ == "__main__":
    main()