Repository: TobikoData/sqlmesh
Pull request #4467 (2025-05-19 22:29:22)

Chore: Get fast-test working on Windows
This extends the Windows tests to include `make fast-test` instead of just the LSP tests.

There is still more work to do in order to get `make cicd-test` fully working on Windows so that can be addressed in followup PR's as bandwidth allows



Patch:
diff --git a/.circleci/continue_config.yml b/.circleci/continue_config.yml
index 02a1b9fe..2546944c 100644
--- a/.circleci/continue_config.yml
+++ b/.circleci/continue_config.yml
@@ -99,35 +99,35 @@ jobs:
       - store_test_results:
           path: test-results
 
-  style_and_cicd_tests_windows:
+  cicd_tests_windows:
     executor:
       name: windows/default
       size: large
-    environment:
-      PYTEST_XDIST_AUTO_NUM_WORKERS: 4
     steps:
       - halt_unless_core
+      - run:
+          name: Enable symlinks in git config
+          command: git config --global core.symlinks true
       - checkout
       - run:
-          name: Install Python 3.9
+          name: Install System Dependencies
           command: |
-            choco install python --version=3.9 -y
+            choco install make which -y
             refreshenv
-      - run: 
-          name: Install make
-          command: choco install make -y
       - run:
           name: Install SQLMesh dev dependencies
-          command: make install-dev
-      - run:
-          name: Fix Git URL override
-          command: git config --global --unset url."ssh://git@github.com".insteadOf
-      # - run:
-      #     name: Run linters and code style checks
-      #     command: make py-style
+          command: |
+            python -m venv venv
+            . ./venv/Scripts/activate
+            python.exe -m pip install --upgrade pip
+            make install-dev
       - run:
-          name: Run cicd tests
-          command: pytest -n auto -m "fast" --junitxml=test-results/junit-cicd.xml tests/lsp
+          name: Run fast unit tests
+          command: |
+            . ./venv/Scripts/activate
+            which python
+            python --version
+            make fast-test
       - store_test_results:
           path: test-results
 
@@ -304,7 +304,7 @@ workflows:
                 - "3.10"
                 - "3.11"
                 - "3.12"
-      - style_and_cicd_tests_windows
+      - cicd_tests_windows
       - engine_tests_docker:
           name: engine_<< matrix.engine >>
           matrix:
diff --git a/Makefile b/Makefile
index 7d6c0f03..7c2aa11d 100644
--- a/Makefile
+++ b/Makefile
@@ -64,7 +64,7 @@ engine-up: engine-clickhouse-up engine-mssql-up engine-mysql-up engine-postgres-
 engine-down: engine-clickhouse-down engine-mssql-down engine-mysql-down engine-postgres-down engine-spark-down engine-trino-down
 
 fast-test:
-	pytest -n auto -m "fast and not cicdonly" && pytest -m "isolated"
+	pytest -n auto -m "fast and not cicdonly" --junitxml=test-results/junit-fast-test.xml && pytest -m "isolated"
 
 slow-test:
 	pytest -n auto -m "(fast or slow) and not cicdonly" && pytest -m "isolated"
diff --git a/examples/sushi/linter/__init__.py b/examples/sushi/linter/__init__.py
new file mode 100644
index 00000000..fc4d40b0
--- /dev/null
+++ b/examples/sushi/linter/__init__.py
@@ -0,0 +1,1 @@
+# this makes "linter" a package so "linter.user" is a valid module for importlib.import_module() to load
diff --git a/sqlmesh/core/engine_adapter/athena.py b/sqlmesh/core/engine_adapter/athena.py
index 5dc67d5b..8e0f3d84 100644
--- a/sqlmesh/core/engine_adapter/athena.py
+++ b/sqlmesh/core/engine_adapter/athena.py
@@ -8,7 +8,7 @@ from sqlmesh.utils.aws import validate_s3_uri, parse_s3_uri
 from sqlmesh.core.engine_adapter.mixins import PandasNativeFetchDFSupportMixin, RowDiffMixin
 from sqlmesh.core.engine_adapter.trino import TrinoEngineAdapter
 from sqlmesh.core.node import IntervalUnit
-import os
+import posixpath
 from sqlmesh.utils.errors import SQLMeshError
 from sqlmesh.core.engine_adapter.shared import (
     CatalogSupport,
@@ -403,11 +403,13 @@ class AthenaEngineAdapter(PandasNativeFetchDFSupportMixin, RowDiffMixin):
 
         elif self.s3_warehouse_location:
             # If the user has set `s3_warehouse_location` in the connection config, the base URI is <s3_warehouse_location>/<catalog>/<schema>/
-            base_uri = os.path.join(self.s3_warehouse_location, table.catalog or "", table.db or "")
+            base_uri = posixpath.join(
+                self.s3_warehouse_location, table.catalog or "", table.db or ""
+            )
         else:
             return None
 
-        full_uri = validate_s3_uri(os.path.join(base_uri, table.text("this") or ""), base=True)
+        full_uri = validate_s3_uri(posixpath.join(base_uri, table.text("this") or ""), base=True)
         return exp.LocationProperty(this=exp.Literal.string(full_uri))
 
     def _find_matching_columns(
diff --git a/sqlmesh/core/model/cache.py b/sqlmesh/core/model/cache.py
index f814c204..3357747c 100644
--- a/sqlmesh/core/model/cache.py
+++ b/sqlmesh/core/model/cache.py
@@ -15,6 +15,7 @@ from sqlmesh.core import constants as c
 from sqlmesh.core.model.definition import ExternalModel, Model, SqlModel, _Model
 from sqlmesh.utils.cache import FileCache
 from sqlmesh.utils.hashing import crc32
+from sqlmesh.utils.windows import IS_WINDOWS
 
 from dataclasses import dataclass
 
@@ -149,8 +150,10 @@ class OptimizedQueryCache:
 
 
 def optimized_query_cache_pool(optimized_query_cache: OptimizedQueryCache) -> ProcessPoolExecutor:
+    # fork doesnt work on Windows. ref: https://docs.python.org/3/library/multiprocessing.html#multiprocessing-start-methods
+    context_type = "spawn" if IS_WINDOWS else "fork"
     return ProcessPoolExecutor(
-        mp_context=mp.get_context("fork"),
+        mp_context=mp.get_context(context_type),
         initializer=_init_optimized_query_cache,
         initargs=(optimized_query_cache,),
         max_workers=c.MAX_FORK_WORKERS,
diff --git a/sqlmesh/utils/cache.py b/sqlmesh/utils/cache.py
index ab91fae9..002248f5 100644
--- a/sqlmesh/utils/cache.py
+++ b/sqlmesh/utils/cache.py
@@ -12,6 +12,7 @@ from sqlglot import __version__ as SQLGLOT_VERSION
 from sqlmesh.utils import sanitize_name
 from sqlmesh.utils.date import to_datetime
 from sqlmesh.utils.errors import SQLMeshError
+from sqlmesh.utils.windows import IS_WINDOWS, fix_windows_path
 
 logger = logging.getLogger(__name__)
 
@@ -132,4 +133,8 @@ class FileCache(t.Generic[T]):
 
     def _cache_entry_path(self, name: str, entry_id: str = "") -> Path:
         entry_file_name = "__".join(p for p in (self._cache_version, name, entry_id) if p)
-        return self._path / sanitize_name(entry_file_name)
+        full_path = self._path / sanitize_name(entry_file_name)
+        if IS_WINDOWS:
+            # handle paths longer than 260 chars
+            full_path = fix_windows_path(full_path)
+        return full_path
diff --git a/sqlmesh/utils/windows.py b/sqlmesh/utils/windows.py
new file mode 100644
index 00000000..238ed353
--- /dev/null
+++ b/sqlmesh/utils/windows.py
@@ -0,0 +1,14 @@
+import platform
+from pathlib import Path
+
+IS_WINDOWS = platform.system() == "Windows"
+
+
+def fix_windows_path(path: Path) -> Path:
+    """
+    Windows paths are limited to 260 characters: https://learn.microsoft.com/en-us/windows/win32/fileio/maximum-file-path-limitation
+    Users can change this by updating a registry entry but we cant rely on that.
+    We can quite commonly generate a cache file path that exceeds 260 characters which causes a FileNotFound error.
+    If we prefix the path with "\\?\" then we can have paths up to 32,767 characters
+    """
+    return Path("\\\\?\\" + str(path.absolute()))
diff --git a/web/server/api/endpoints/commands.py b/web/server/api/endpoints/commands.py
index 6655d3ce..5631a5d3 100644
--- a/web/server/api/endpoints/commands.py
+++ b/web/server/api/endpoints/commands.py
@@ -22,6 +22,7 @@ from web.server.utils import (
     df_to_pyarrow_bytes,
     run_in_executor,
 )
+from pathlib import Path
 
 router = APIRouter()
 
@@ -143,7 +144,7 @@ async def test(
     test_output = io.StringIO()
     try:
         result = context.test(
-            tests=[str(context.path / test)] if test else None,
+            tests=[str(context.path / Path(test))] if test else None,
             verbosity=verbosity,
             stream=test_output,
         )
@@ -160,11 +161,17 @@ async def test(
         test_output.getvalue(),
         context._test_connection_config._engine_adapter.DIALECT,
     )
+
+    def _test_path(test: ModelTest) -> t.Optional[str]:
+        if path := test.path_relative_to(context.path):
+            return path.as_posix()
+        return None
+
     return models.TestResult(
         errors=[
             models.TestErrorOrFailure(
                 name=test.test_name,
-                path=test.path_relative_to(context.path),
+                path=_test_path(test),
                 tb=tb,
             )
             for test, tb in ((t.cast(ModelTest, test), tb) for test, tb in result.errors)
@@ -172,7 +179,7 @@ async def test(
         failures=[
             models.TestErrorOrFailure(
                 name=test.test_name,
-                path=test.path_relative_to(context.path),
+                path=_test_path(test),
                 tb=tb,
             )
             for test, tb in ((t.cast(ModelTest, test), tb) for test, tb in result.failures)
@@ -180,7 +187,7 @@ async def test(
         skipped=[
             models.TestSkipped(
                 name=test.test_name,
-                path=test.path_relative_to(context.path),
+                path=_test_path(test),
                 reason=reason,
             )
             for test, reason in (
@@ -190,7 +197,7 @@ async def test(
         successes=[
             models.TestCase(
                 name=test.test_name,
-                path=test.path_relative_to(context.path),
+                path=_test_path(test),
             )
             for test in (t.cast(ModelTest, test) for test in result.successes)
         ],
diff --git a/web/server/api/endpoints/files.py b/web/server/api/endpoints/files.py
index f4265003..7e4d3652 100644
--- a/web/server/api/endpoints/files.py
+++ b/web/server/api/endpoints/files.py
@@ -145,13 +145,13 @@ def _get_directory(path: str | Path, settings: Settings) -> models.Directory:
                     directories.append(
                         models.Directory(
                             name=entry.name,
-                            path=str(relative_path),
+                            path=str(relative_path.as_posix()),
                             directories=_directories,
                             files=_files,
                         )
                     )
                 elif entry.is_file(follow_symlinks=False):
-                    files.append(models.File(name=entry.name, path=str(relative_path)))
+                    files.append(models.File(name=entry.name, path=str(relative_path.as_posix())))
         return sorted(directories, key=lambda x: x.name), sorted(files, key=lambda x: x.name)
 
     directories, files = walk_path(path)
diff --git a/web/server/api/endpoints/models.py b/web/server/api/endpoints/models.py
index ab681bf4..5d81306a 100644
--- a/web/server/api/endpoints/models.py
+++ b/web/server/api/endpoints/models.py
@@ -124,8 +124,8 @@ def serialize_model(context: Context, model: Model, render_query: bool = False)
     return models.Model(
         name=model.name,
         fqn=model.fqn,
-        path=str(model._path.absolute().relative_to(context.path)),
-        full_path=str(model._path.absolute()),
+        path=str(model._path.absolute().relative_to(context.path).as_posix()),
+        full_path=str(model._path.absolute().as_posix()),
         dialect=dialect,
         columns=columns,
         details=details,
diff --git a/web/server/models.py b/web/server/models.py
index 82e17986..cc12d036 100644
--- a/web/server/models.py
+++ b/web/server/models.py
@@ -420,7 +420,7 @@ class TableDiff(PydanticModel):
 
 class TestCase(PydanticModel):
     name: str
-    path: pathlib.Path
+    path: str
 
 
 class TestErrorOrFailure(TestCase):
diff --git a/web/server/utils.py b/web/server/utils.py
index 1c9886f4..cda6a7d9 100644
--- a/web/server/utils.py
+++ b/web/server/utils.py
@@ -16,6 +16,7 @@ from sqlmesh.core import constants as c
 from web.server.console import api_console
 from web.server.exceptions import ApiException
 from web.server.settings import Settings, get_context, get_settings
+from sqlmesh.utils.windows import IS_WINDOWS
 
 R = t.TypeVar("R")
 
@@ -75,6 +76,16 @@ def replace_file(src: Path, dst: Path) -> None:
     """Move a file or directory at src to dst."""
     if src != dst:
         try:
+            if IS_WINDOWS:
+                # os.replace() behaves differently on Windows so we have to do some extra checks
+                if dst.exists():
+                    if src.exists() and src.is_dir() and dst.is_file():
+                        raise OSError("Cant rename directory to existing file")
+                    elif src.exists() and src.is_file() and dst.is_dir():
+                        raise OSError("Cant rename file to existing directory")
+                    elif dst.is_dir() and not any(dst.iterdir()):
+                        # target dir exists but is empty, delete it so replace() succeeds
+                        dst.rmdir()
             src.replace(dst)
         except FileNotFoundError:
             raise HTTPException(status_code=HTTP_404_NOT_FOUND)


Test patch:
diff --git a/tests/conftest.py b/tests/conftest.py
index a690222c..224fba7d 100644
--- a/tests/conftest.py
+++ b/tests/conftest.py
@@ -9,6 +9,8 @@ from shutil import copytree, rmtree
 from tempfile import TemporaryDirectory
 from unittest import mock
 from unittest.mock import PropertyMock
+import os
+import shutil
 
 import duckdb
 import pandas as pd
@@ -39,6 +41,7 @@ from sqlmesh.core.snapshot import (
 )
 from sqlmesh.utils import random_id
 from sqlmesh.utils.date import TimeLike, to_date
+from sqlmesh.utils.windows import IS_WINDOWS, fix_windows_path
 from sqlmesh.core.engine_adapter.shared import CatalogSupport
 
 T = t.TypeVar("T", bound=EngineAdapter)
@@ -480,10 +483,27 @@ def copy_to_temp_path(tmp_path: Path) -> t.Callable:
         paths: t.Union[t.Union[str, Path], t.Collection[t.Union[str, Path]]],
     ) -> t.List[Path]:
         paths = ensure_list(paths)
+        all_paths = [Path(p) for p in paths]
         temp_dirs = []
-        for path in paths:
+        for path in all_paths:
             temp_dir = Path(tmp_path) / uuid.uuid4().hex
-            copytree(path, temp_dir, symlinks=True, ignore=ignore)
+
+            if IS_WINDOWS:
+                # shutil.copytree just doesnt work properly with the symlinks on Windows, regardless of the `symlinks` setting
+                src = str(path.absolute())
+                dst = str(temp_dir.absolute())
+                os.system(f"robocopy {src} {dst} /E /COPYALL")
+
+                # after copying, delete the files that would have been ignored
+                for root, dirs, _ in os.walk(temp_dir):
+                    for dir in dirs:
+                        full_dir = fix_windows_path(Path(root) / dir)
+                        for ignored in ignore(full_dir, [full_dir]):
+                            shutil.rmtree(ignored)
+
+            else:
+                copytree(path, temp_dir, symlinks=True, ignore=ignore)
+
             temp_dirs.append(temp_dir)
         return temp_dirs
 
diff --git a/tests/core/test_format.py b/tests/core/test_format.py
index a334e86a..9b51220a 100644
--- a/tests/core/test_format.py
+++ b/tests/core/test_format.py
@@ -61,10 +61,10 @@ def test_format_files(tmp_path: pathlib.Path, mocker: MockerFixture):
     assert all(
         c in context.console.log_status_update.mock_calls  # type: ignore
         for c in [
-            call(f"{tmp_path}/models/model_3.sql needs reformatting."),
-            call(f"{tmp_path}/models/model_2.sql needs reformatting."),
-            call(f"{tmp_path}/models/model_1.sql needs reformatting."),
-            call(f"{tmp_path}/audits/audit_1.sql needs reformatting."),
+            call(f"{tmp_path / 'models/model_3.sql'} needs reformatting."),
+            call(f"{tmp_path / 'models/model_2.sql'} needs reformatting."),
+            call(f"{tmp_path / 'models/model_1.sql'} needs reformatting."),
+            call(f"{tmp_path / 'audits/audit_1.sql'} needs reformatting."),
             call("\n4 file(s) need reformatting."),
         ]
     )
diff --git a/tests/core/test_model.py b/tests/core/test_model.py
index e805132b..592dd992 100644
--- a/tests/core/test_model.py
+++ b/tests/core/test_model.py
@@ -1238,7 +1238,7 @@ def test_seed_marker_substitution():
     )
 
     assert isinstance(model.kind, SeedKind)
-    assert model.kind.path == "examples/sushi/seeds/waiter_names.csv"
+    assert model.kind.path == str(Path("examples/sushi/seeds/waiter_names.csv"))
     assert model.seed is not None
     assert len(model.seed.content) > 0
 
diff --git a/tests/core/test_table_diff.py b/tests/core/test_table_diff.py
index fb58c9a5..4d7be022 100644
--- a/tests/core/test_table_diff.py
+++ b/tests/core/test_table_diff.py
@@ -13,9 +13,10 @@ from sqlmesh.core.config import AutoCategorizationMode, CategorizerConfig, DuckD
 from sqlmesh.core.model import SqlModel, load_sql_based_model
 from sqlmesh.core.table_diff import TableDiff
 import numpy as np
-
 from sqlmesh.utils.errors import SQLMeshError
 
+pytestmark = pytest.mark.slow
+
 
 def create_test_console() -> t.Tuple[StringIO, TerminalConsole]:
     """Creates a console and buffer for validating console output."""
@@ -50,7 +51,6 @@ def strip_ansi_codes(text: str) -> str:
     return ansi_escape.sub("", text).strip()
 
 
-@pytest.mark.slow
 def test_data_diff(sushi_context_fixed_date, capsys, caplog):
     model = sushi_context_fixed_date.models['"memory"."sushi"."customer_revenue_by_day"']
 
@@ -143,7 +143,6 @@ def test_data_diff(sushi_context_fixed_date, capsys, caplog):
     assert row_diff.t_sample.shape == (1, 6)
 
 
-@pytest.mark.slow
 def test_data_diff_decimals(sushi_context_fixed_date):
     engine_adapter = sushi_context_fixed_date.engine_adapter
 
@@ -230,7 +229,6 @@ Column: value
     assert stripped_output == stripped_expected
 
 
-@pytest.mark.slow
 def test_grain_check(sushi_context_fixed_date):
     expressions = d.parse(
         """
@@ -312,7 +310,6 @@ def test_grain_check(sushi_context_fixed_date):
     assert row_diff.t_sample.shape == (3, 3)
 
 
-@pytest.mark.slow
 def test_generated_sql(sushi_context_fixed_date: Context, mocker: MockerFixture):
     engine_adapter = sushi_context_fixed_date.engine_adapter
 
@@ -382,7 +379,6 @@ def test_generated_sql(sushi_context_fixed_date: Context, mocker: MockerFixture)
     spy_execute.assert_any_call(query_sql_where)
 
 
-@pytest.mark.slow
 def test_tables_and_grain_inferred_from_model(sushi_context_fixed_date: Context):
     (sushi_context_fixed_date.path / "models" / "waiter_revenue_by_day.sql").write_text("""
     MODEL (
@@ -432,7 +428,6 @@ def test_tables_and_grain_inferred_from_model(sushi_context_fixed_date: Context)
     assert col_names == ["waiter_id", "event_date"]
 
 
-@pytest.mark.slow
 def test_data_diff_array_dict(sushi_context_fixed_date):
     engine_adapter = sushi_context_fixed_date.engine_adapter
 
@@ -566,7 +561,6 @@ Column: value
     assert strip_ansi_codes(output) == expected_output.strip()
 
 
-@pytest.mark.slow
 def test_data_diff_multiple_models(sushi_context_fixed_date, capsys, caplog):
     # Create first analytics model
     expressions = d.parse(
@@ -699,7 +693,6 @@ def test_data_diff_multiple_models(sushi_context_fixed_date, capsys, caplog):
     assert len(diffs) == 0
 
 
-@pytest.mark.slow
 def test_data_diff_forward_only(sushi_context_fixed_date, capsys, caplog):
     expressions = d.parse(
         """
diff --git a/tests/integrations/jupyter/test_magics.py b/tests/integrations/jupyter/test_magics.py
index a30b9325..71e6f2d3 100644
--- a/tests/integrations/jupyter/test_magics.py
+++ b/tests/integrations/jupyter/test_magics.py
@@ -15,6 +15,7 @@ from rich.console import Console as RichConsole
 
 from sqlmesh import Context, RuntimeEnv
 from sqlmesh.magics import register_magics
+from pathlib import Path
 
 logger = logging.getLogger(__name__)
 
@@ -109,8 +110,9 @@ def test_context(notebook, convert_all_html_output_to_text, get_all_html_output,
     assert output.stdout == ""
     assert output.stderr == ""
     assert len(output.outputs) == 1
+    sushi_path = str(Path("examples/sushi"))
     assert convert_all_html_output_to_text(output) == [
-        "SQLMesh project context set to: examples/sushi"
+        f"SQLMesh project context set to: {sushi_path}"
     ]
     assert get_all_html_output(output) == [
         str(
@@ -120,7 +122,7 @@ def test_context(notebook, convert_all_html_output_to_text, get_all_html_output,
                 h(
                     "span",
                     {"style": SUCCESS_STYLE},
-                    "SQLMesh project context set to: examples/sushi",
+                    f"SQLMesh project context set to: {sushi_path}",
                     autoescape=False,
                 ),
                 autoescape=False,
diff --git a/tests/utils/test_metaprogramming.py b/tests/utils/test_metaprogramming.py
index 1bef8b7a..0de3f63b 100644
--- a/tests/utils/test_metaprogramming.py
+++ b/tests/utils/test_metaprogramming.py
@@ -48,7 +48,7 @@ def test_print_exception(mocker: MockerFixture):
     except Exception as ex:
         print_exception(ex, test_env, out_mock)
 
-    expected_message = r"""  File ".*?/tests/utils/test_metaprogramming\.py", line 47, in test_print_exception
+    expected_message = r"""  File ".*?.tests.utils.test_metaprogramming\.py", line 47, in test_print_exception
     eval\("test_fun\(\)", env\)
 
   File "<string>", line 1, in <module>
@@ -58,7 +58,13 @@ def test_print_exception(mocker: MockerFixture):
         raise RuntimeError\("error"\)
       RuntimeError: error
 """
-    assert re.match(expected_message, out_mock.write.call_args_list[0][0][0])
+    actual_message = out_mock.write.call_args_list[0][0][0]
+    assert isinstance(actual_message, str)
+
+    expected_message = "".join(expected_message.split())
+    actual_message = "".join(actual_message.split())
+
+    assert re.match(expected_message, actual_message)
 
 
 X = 1