"""Tests for the deterministic SQL renderer.""" import sys from pathlib import Path sys.path.insert(0, str(Path(__file__).parent.parent)) from models import ( MantaraSchema, Menu, Submenu, Table, Column, ForeignKey, EnumType, ) from renderer import render_sql class TestRenderSQLStructure: """Test that rendered SQL contains all required structural elements.""" def test_create_schema(self, ams_schema): sql = render_sql(ams_schema) assert "CREATE SCHEMA ams;" in sql def test_menu_table(self, ams_schema): sql = render_sql(ams_schema) assert "CREATE TABLE ams.menu" in sql assert "menu_id INT PRIMARY KEY" in sql assert "menu_name VARCHAR(255) NOT NULL" in sql assert "COMMENT ON TABLE ams.menu" in sql def test_submenu_table(self, ams_schema): sql = render_sql(ams_schema) assert "CREATE TABLE ams.submenu" in sql assert "submenu_id INT PRIMARY KEY" in sql assert "REFERENCES ams.menu(menu_id)" in sql assert "COMMENT ON TABLE ams.submenu" in sql def test_menu_insert_data(self, ams_schema): sql = render_sql(ams_schema) assert "INSERT INTO ams.menu" in sql assert "'Dashboard'" in sql assert "'Crop Management'" in sql assert "'Settings'" in sql def test_submenu_insert_data(self, ams_schema): sql = render_sql(ams_schema) assert "INSERT INTO ams.submenu" in sql assert "(101," in sql assert "(201," in sql assert "(301," in sql def test_enum_types(self, ams_schema): sql = render_sql(ams_schema) assert "CREATE TYPE ams.season_enum AS ENUM" in sql assert "CREATE TYPE ams.crop_category_enum AS ENUM" in sql assert "CREATE TYPE ams.activity_status_enum AS ENUM" in sql assert "'kharif'" in sql def test_business_tables(self, ams_schema): sql = render_sql(ams_schema) assert "CREATE TABLE ams.farm_overview" in sql assert "CREATE TABLE ams.crops" in sql assert "CREATE TABLE ams.planting_plans" in sql assert "CREATE TABLE ams.farms" in sql def test_comments_on_business_tables(self, ams_schema): sql = render_sql(ams_schema) assert "COMMENT ON TABLE ams.farm_overview" in sql assert "COMMENT ON TABLE ams.crops" in sql assert "COMMENT ON TABLE ams.planting_plans" in sql assert "COMMENT ON TABLE ams.farms" in sql def test_section_separators(self, ams_schema): sql = render_sql(ams_schema) assert "MENU 1: Dashboard" in sql assert "MENU 2: Crop Management" in sql assert "END OF SCHEMA" in sql def test_header(self, ams_schema): sql = render_sql(ams_schema) assert "MANTARA SCHEMA" in sql assert "Agricultural Management System" in sql class TestDeferredForeignKeys: """Test forward-reference FK handling.""" def test_forward_ref_deferred(self, ams_schema): """farm_overview.farm_id references farms, but farms is created later.""" sql = render_sql(ams_schema) assert "ADDITIONAL FOREIGN KEY CONSTRAINTS" in sql assert "ALTER TABLE ams.farm_overview" in sql assert "REFERENCES ams.farms(id)" in sql def test_inline_fk_not_deferred(self, ams_schema): """planting_plans.crop_id references crops, which is created before it.""" sql = render_sql(ams_schema) # crop_id FK should be inline, not deferred lines = sql.split("\n") in_planting = False for line in lines: if "CREATE TABLE ams.planting_plans" in line: in_planting = True if in_planting and "crop_id" in line: assert "REFERENCES ams.crops(id)" in line break class TestInlineColumnComments: """Test that column comments render as inline SQL comments.""" def test_column_comment_rendered(self): schema = MantaraSchema( system_name="Test", schema_name="tst", description="Test schema.", menus=[ Menu( menu_id=1, menu_name="Main", sequence_number=1, description="Main.", submenus=[ Submenu( submenu_id=101, submenu_name="Sub", sequence_number=1, description="Sub.", tables=[ Table( table_name="items", comment="Items table for testing column comments.", columns=[ Column(name="id", type="SERIAL", constraints="PRIMARY KEY"), Column(name="submenu_id", type="INT", constraints="DEFAULT 101 NOT NULL REFERENCES tst.submenu(submenu_id)"), Column(name="weight_kg", type="FLOAT", comment="Weight in kilograms"), Column(name="price", type="FLOAT", constraints="NOT NULL", comment="Price in USD"), ], ), ], ), ], ), ], ) sql = render_sql(schema) assert "-- Weight in kilograms" in sql assert "-- Price in USD" in sql def test_comma_before_comment_not_after(self): """CRITICAL: comma must come BEFORE -- comment, not after. Bad: weight_kg FLOAT -- in kg, (comma is commented out!) Good: weight_kg FLOAT, -- in kg (comma is real SQL) """ schema = MantaraSchema( system_name="Test", schema_name="tst", description="Test schema.", menus=[ Menu( menu_id=1, menu_name="Main", sequence_number=1, description="Main.", submenus=[ Submenu( submenu_id=101, submenu_name="Sub", sequence_number=1, description="Sub.", tables=[ Table( table_name="items", comment="Items table for testing comma placement.", columns=[ Column(name="id", type="SERIAL", constraints="PRIMARY KEY"), Column(name="submenu_id", type="INT", constraints="DEFAULT 101 NOT NULL REFERENCES tst.submenu(submenu_id)"), Column(name="weight_kg", type="FLOAT", comment="Weight in kilograms"), Column(name="name", type="VARCHAR(255)", constraints="NOT NULL"), ], ), ], ), ], ), ], ) sql = render_sql(schema) # Find the weight_kg line — comma must be BEFORE the comment for line in sql.split("\n"): if "weight_kg" in line and "--" in line: comment_pos = line.index("--") comma_pos = line.rindex(",", 0, comment_pos) if "," in line[:comment_pos] else -1 assert comma_pos > 0, f"Comma must appear before -- comment: {line}" assert comma_pos < comment_pos, f"Comma must be before comment: {line}" break else: raise AssertionError("weight_kg line with comment not found") def test_no_comment_no_dash(self, ams_schema): """Columns without comments should not have trailing --.""" sql = render_sql(ams_schema) lines = sql.split("\n") for line in lines: if "farm_name" in line and "CREATE" not in line: assert "-- " not in line or "COMMENT" in line class TestMySQLSanitization: """Test that MySQL-specific syntax is stripped from rendered SQL.""" def test_on_update_current_timestamp_stripped(self): schema = MantaraSchema( system_name="Test", schema_name="tst", description="Test.", menus=[ Menu( menu_id=1, menu_name="Main", sequence_number=1, description="Main.", submenus=[ Submenu( submenu_id=101, submenu_name="Sub", sequence_number=1, description="Sub.", tables=[ Table( table_name="items", comment="Items table with MySQL syntax for testing.", columns=[ Column(name="id", type="SERIAL", constraints="PRIMARY KEY"), Column(name="submenu_id", type="INT", constraints="DEFAULT 101 NOT NULL REFERENCES tst.submenu(submenu_id)"), Column(name="name", type="VARCHAR(255)"), Column(name="updated_at", type="TIMESTAMP", constraints="DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"), ], ), ], ), ], ), ], ) sql = render_sql(schema) assert "ON UPDATE CURRENT_TIMESTAMP" not in sql assert "DEFAULT CURRENT_TIMESTAMP" in sql class TestAssumptionsOpenQuestions: """Test assumptions and open_questions rendering in SQL.""" def test_assumptions_rendered(self): schema = MantaraSchema( system_name="Test", schema_name="tst", description="Test.", menus=[ Menu( menu_id=1, menu_name="Main", sequence_number=1, description="Main.", submenus=[ Submenu(submenu_id=101, submenu_name="Sub", sequence_number=1, description="Sub."), ], ), ], assumptions=["Single-tenant system", "All dates in UTC"], ) sql = render_sql(schema) assert "ASSUMPTIONS:" in sql assert "Single-tenant system" in sql assert "All dates in UTC" in sql def test_open_questions_rendered(self): schema = MantaraSchema( system_name="Test", schema_name="tst", description="Test.", menus=[ Menu( menu_id=1, menu_name="Main", sequence_number=1, description="Main.", submenus=[ Submenu(submenu_id=101, submenu_name="Sub", sequence_number=1, description="Sub."), ], ), ], open_questions=["What authentication method?"], ) sql = render_sql(schema) assert "OPEN QUESTIONS:" in sql assert "What authentication method?" in sql def test_no_assumptions_no_section(self, ams_schema): """Schema without assumptions should not render that section.""" sql = render_sql(ams_schema) assert "ASSUMPTIONS:" not in sql assert "OPEN QUESTIONS:" not in sql class TestAutoInjectCheckConstraints: """Test renderer auto-injection of CHECK constraints.""" def test_non_negative_check_injected_on_price(self): """price column without CHECK should get CHECK (price >= 0) auto-injected.""" schema = MantaraSchema( system_name="Test", schema_name="tst", description="Test.", menus=[ Menu( menu_id=1, menu_name="Main", sequence_number=1, description="Main.", submenus=[ Submenu( submenu_id=101, submenu_name="Sub", sequence_number=1, description="Sub.", tables=[ Table( table_name="products", comment="Products table for testing CHECK auto-injection.", columns=[ Column(name="id", type="SERIAL", constraints="PRIMARY KEY"), Column(name="submenu_id", type="INT", constraints="DEFAULT 101 NOT NULL REFERENCES tst.submenu(submenu_id)"), Column(name="price", type="NUMERIC(12,2)", constraints="NOT NULL"), Column(name="quantity", type="INT", constraints="NOT NULL"), Column(name="name", type="VARCHAR(255)"), ], ), ], ), ], ), ], ) sql = render_sql(schema) assert "CHECK (price >= 0)" in sql assert "CHECK (quantity >= 0)" in sql def test_no_double_check_if_already_present(self): """Column that already has CHECK should NOT get a second one.""" schema = MantaraSchema( system_name="Test", schema_name="tst", description="Test.", menus=[ Menu( menu_id=1, menu_name="Main", sequence_number=1, description="Main.", submenus=[ Submenu( submenu_id=101, submenu_name="Sub", sequence_number=1, description="Sub.", tables=[ Table( table_name="orders", comment="Orders table with existing CHECK for testing.", columns=[ Column(name="id", type="SERIAL", constraints="PRIMARY KEY"), Column(name="submenu_id", type="INT", constraints="DEFAULT 101 NOT NULL REFERENCES tst.submenu(submenu_id)"), Column(name="amount", type="NUMERIC(12,2)", constraints="NOT NULL CHECK (amount > 0)"), ], ), ], ), ], ), ], ) sql = render_sql(schema) # Should have the original CHECK but NOT a duplicate assert "CHECK (amount > 0)" in sql assert sql.count("CHECK (amount") == 1 def test_date_range_check_injected(self): """Tables with start_date + end_date should get CHECK (end_date >= start_date).""" schema = MantaraSchema( system_name="Test", schema_name="tst", description="Test.", menus=[ Menu( menu_id=1, menu_name="Main", sequence_number=1, description="Main.", submenus=[ Submenu( submenu_id=101, submenu_name="Sub", sequence_number=1, description="Sub.", tables=[ Table( table_name="reservations", comment="Reservations with date range for testing.", columns=[ Column(name="id", type="SERIAL", constraints="PRIMARY KEY"), Column(name="submenu_id", type="INT", constraints="DEFAULT 101 NOT NULL REFERENCES tst.submenu(submenu_id)"), Column(name="start_date", type="DATE", constraints="NOT NULL"), Column(name="end_date", type="DATE", constraints="NOT NULL"), ], ), ], ), ], ), ], ) sql = render_sql(schema) assert "CHECK (end_date >= start_date)" in sql def test_checkin_checkout_date_range(self): """check_in_date + check_out_date should get date range CHECK.""" schema = MantaraSchema( system_name="Test", schema_name="tst", description="Test.", menus=[ Menu( menu_id=1, menu_name="Main", sequence_number=1, description="Main.", submenus=[ Submenu( submenu_id=101, submenu_name="Sub", sequence_number=1, description="Sub.", tables=[ Table( table_name="bookings", comment="Bookings with check-in/out for testing.", columns=[ Column(name="id", type="SERIAL", constraints="PRIMARY KEY"), Column(name="submenu_id", type="INT", constraints="DEFAULT 101 NOT NULL REFERENCES tst.submenu(submenu_id)"), Column(name="check_in_date", type="DATE", constraints="NOT NULL"), Column(name="check_out_date", type="DATE", constraints="NOT NULL"), ], ), ], ), ], ), ], ) sql = render_sql(schema) assert "CHECK (check_out_date >= check_in_date)" in sql def test_no_check_on_non_matching_columns(self): """Columns like 'name', 'description' should NOT get CHECK constraints.""" schema = MantaraSchema( system_name="Test", schema_name="tst", description="Test.", menus=[ Menu( menu_id=1, menu_name="Main", sequence_number=1, description="Main.", submenus=[ Submenu( submenu_id=101, submenu_name="Sub", sequence_number=1, description="Sub.", tables=[ Table( table_name="items", comment="Items table without money columns.", columns=[ Column(name="id", type="SERIAL", constraints="PRIMARY KEY"), Column(name="submenu_id", type="INT", constraints="DEFAULT 101 NOT NULL REFERENCES tst.submenu(submenu_id)"), Column(name="name", type="VARCHAR(255)", constraints="NOT NULL"), Column(name="description", type="TEXT"), ], ), ], ), ], ), ], ) sql = render_sql(schema) assert "CHECK" not in sql class TestEdgeCases: def test_empty_tables_submenu(self): """Submenu with no tables should render a comment, not crash.""" schema = MantaraSchema( system_name="Test", schema_name="tst", description="Test schema.", menus=[ Menu( menu_id=1, menu_name="Main", sequence_number=1, description="Main menu.", submenus=[ Submenu( submenu_id=101, submenu_name="Dashboard", sequence_number=1, description="Overview screen.", tables=None, ), ], ), ], ) sql = render_sql(schema) assert "CREATE SCHEMA tst;" in sql assert "No dedicated table" in sql or "UI-only screen" in sql def test_no_enums(self): """Schema with no enum_types should render without errors.""" schema = MantaraSchema( system_name="Simple", schema_name="sim", description="Simple schema.", menus=[ Menu( menu_id=1, menu_name="Tasks", sequence_number=1, description="Task management.", submenus=[ Submenu( submenu_id=101, submenu_name="Task List", sequence_number=1, description="All tasks.", tables=[ Table( table_name="tasks", comment="Simple task list for tracking work items.", columns=[ Column(name="id", type="SERIAL", constraints="PRIMARY KEY"), Column(name="submenu_id", type="INT", constraints="DEFAULT 101 NOT NULL REFERENCES sim.submenu(submenu_id)"), Column(name="title", type="VARCHAR(255)", constraints="NOT NULL"), ], ), ], ), ], ), ], ) sql = render_sql(schema) assert "CREATE SCHEMA sim;" in sql assert "CREATE TABLE sim.tasks" in sql assert "ENUM" not in sql def test_special_chars_escaped(self): """Single quotes in comments/descriptions should be escaped.""" schema = MantaraSchema( system_name="O'Brien's System", schema_name="obs", description="A system for O'Brien.", menus=[ Menu( menu_id=1, menu_name="Main", sequence_number=1, description="The user's main menu.", submenus=[ Submenu( submenu_id=101, submenu_name="Overview", sequence_number=1, description="The system's overview.", tables=[ Table( table_name="items", comment="Items managed by O'Brien's system for tracking.", columns=[ Column(name="id", type="SERIAL", constraints="PRIMARY KEY"), Column(name="submenu_id", type="INT", constraints="DEFAULT 101 NOT NULL REFERENCES obs.submenu(submenu_id)"), Column(name="name", type="VARCHAR(255)"), ], ), ], ), ], ), ], ) sql = render_sql(schema) # Single quotes must be escaped as '' in SQL assert "O''Brien" in sql def test_table_no_foreign_keys(self): """Table with columns but no foreign_keys list should render cleanly.""" schema = MantaraSchema( system_name="Standalone", schema_name="sta", description="Standalone tables.", menus=[ Menu( menu_id=1, menu_name="Data", sequence_number=1, description="Data module.", submenus=[ Submenu( submenu_id=101, submenu_name="Logs", sequence_number=1, description="System logs.", tables=[ Table( table_name="logs", comment="System event logs with timestamps and severity levels.", columns=[ Column(name="id", type="SERIAL", constraints="PRIMARY KEY"), Column(name="submenu_id", type="INT", constraints="DEFAULT 101 NOT NULL REFERENCES sta.submenu(submenu_id)"), Column(name="message", type="TEXT", constraints="NOT NULL"), Column(name="severity", type="VARCHAR(20)"), Column(name="created_at", type="TIMESTAMP", constraints="DEFAULT CURRENT_TIMESTAMP"), ], ), ], ), ], ), ], ) sql = render_sql(schema) assert "CREATE TABLE sta.logs" in sql assert "ADDITIONAL FOREIGN KEY CONSTRAINTS" not in sql