Skip to content

DESIGN.md

Latest commit

 

History

History
79 lines (56 loc) · 3.62 KB

DESIGN.md

File metadata and controls

79 lines (56 loc) · 3.62 KB

Tests Design

The Tests project is the automated test suite for SQL Notebook. It uses a custom test framework and primarily tests the scripting engine through file-based SQL test scripts.

Architecture

Custom Test Framework (Program.cs)

The project implements its own lightweight test runner rather than using xUnit or NUnit:

  • Attributes: [TestClass], [TestMethod], [ClassInitialize] (defined in Program.cs)
  • Assert class: Custom implementation with AreEqual, IsTrue, IsInstanceOfType, Fail. String comparisons automatically normalize whitespace and line endings.
  • Test runner: Uses reflection to discover and execute test classes. Supports command-line filtering by test name. Returns exit code 1 on failure.

Test Generation

Test methods in ScriptTest.g.cs are auto-generated by ps1/Update-Tests.ps1, which scans the scripts/ directory and generates one [TestMethod] per .sql file. This means adding a new test is as simple as adding a .sql file and re-running the generator.

Test Script Format (scripts/*.sql)

Each .sql file contains a SQL Notebook script followed by expected output, separated by --output--:

DECLARE @x = 5;
PRINT @x;
--output--
5

Multi-script tests use --script-- separators to define named scripts that can call each other via EXECUTE.

Placeholders:

  • <TEMP> - Replaced with a temporary directory path
  • <FILES> - Replaced with the path to the files/ test data directory

Result format: Data table output uses CSV-like formatting with - row separators between columns and rows.

Test Classes

Class File Coverage
ScriptTest ScriptTest.cs, ScriptTest.g.cs 224 script-based integration tests covering the full scripting language
SqliteGrammarTest SqliteGrammarTest.cs Validates 100+ SQL statements against the SQLite grammar parser
DropStatementsTest DropStatementsTest.cs Tests DROP SCRIPT and DROP PAGE with case-insensitivity
SqlNotebookCmdTest SqlNotebookCmdTest.cs CLI integration tests (success, errors, help output)
FileVersionTest FileVersionTest.cs Backward compatibility with v1 notebook format

Test Data (files/)

Pre-built test data files used by import tests:

  • CSV: utf8.csv, utf16.csv, blank-values.csv, duplicate-header.csv, etc.
  • Excel: excel.xlsx, excel.xls, variants with blank rows/values
  • Text: utf8.txt, utf16.txt, shiftjis.txt
  • Databases: example.sqlite3, example.duckdb
  • Notebooks: cli_test.sqlnb, v1.sqlnb

Coverage Areas

The 224 script tests cover:

  • Control flow: IF/ELSE, WHILE, FOR, FOREACH, BREAK, CONTINUE, nested loops
  • Variables: DECLARE, SET, parameter passing, default values
  • Script execution: EXECUTE with parameters, return values
  • Import: CSV (24 tests), Excel (32 tests), TXT (6 tests), DATABASE (4 tests)
  • Export: CSV (9 tests), TXT (4 tests)
  • DDL: ALTER TABLE (10 tests), CREATE TABLE, DROP statements
  • Functions: Array, date, string, system, aggregate functions
  • Advanced SQL: RETURNING clauses, JOINs, NULLS FIRST/LAST, CTEs, JSON operations
  • Error handling: TRY/CATCH, THROW
  • Documentation examples: 85 tests in scripts/doc/ that validate examples from the help docs

Global Initialization (GlobalInit.cs)

Runs once before any tests:

  1. Encoding.RegisterProvider(CodePagesEncodingProvider.Instance) - Enable legacy encodings
  2. Notebook.InitSqlite() - Initialize the native SQLite engine and extensions

Dependencies

No NuGet packages. Only project references to SqlNotebook, SqlNotebookScript, and SqlNotebookCmd.