Skip to content

SKILL.md

Latest commit

 

History

History
284 lines (214 loc) · 10.9 KB

SKILL.md

File metadata and controls

284 lines (214 loc) · 10.9 KB
Raw
name add-feature
description Add new T-SQL features to ScriptDOM parser. Asks questions about SQL Server version and feature type, then guides through grammar changes, AST updates, script generation, and testing using existing instruction files. Handles syntax additions, new functions, data types, index types, and validation rules.
argument-hint Feature description (e.g., 'Add VECTOR data type', 'Add JSON_OBJECT function')
user-invocable true
platforms SQL Server, Azure SQL DB, Fabric, Fabric DW, VNext

Overview

This skill helps add new T-SQL features to the ScriptDOM parser by:

  1. Interviewing you about the feature and target platform (SQL Server, Azure SQL DB, Fabric, Fabric DW, VNext)
  2. Determining the latest parser version from SqlVersionFlags.cs
  3. Routing you to the appropriate parser:
    • SQL Server → version-specific parser (TSql120-180)
    • Azure SQL DB/Fabric/VNext → latest parser version
    • Fabric DW → separate TSqlFabricDW parser
  4. Classifying the change type (grammar, validation, function, data type, etc.)
  5. Guiding through implementation and testing without duplicating existing documentation

Step 1: Feature Discovery Interview

Ask the user these questions to understand the feature:

Required Questions

  1. What T-SQL feature are you adding?

    • Example: "VECTOR data type", "JSON_OBJECT function", "RESUMABLE option for ALTER TABLE"

  2. Which platform is this feature for?

    • SQL Server (on-premises / standalone)
    • Azure SQL Database (uses latest parser version)
    • Fabric (uses latest parser version)
    • Fabric DW (uses separate TSqlFabricDW parser)
    • VNext (future version, uses latest parser version unless repo instructions say otherwise)

  3. If SQL Server, which version introduced this feature?

    • SQL Server 2014 (TSql120)
    • SQL Server 2016 (TSql130)
    • SQL Server 2017 (TSql140)
    • SQL Server 2019 (TSql150)
    • SQL Server 2022 (TSql160)
    • SQL Server 2025 (TSql170)
    • SQL Server vNext (TSql180)
    • (Skip if Azure SQL DB, Fabric, or VNext - these use TSql180 by default)

  4. Do you have example T-SQL syntax or Microsoft documentation links?

    • This helps verify the exact syntax requirements

Optional Context Questions

  1. Is this a new syntax element or enabling existing syntax in a new context?

    • New syntax: Requires grammar rules, AST nodes
    • Validation fix: May only need version checks in validation code

  2. Does similar syntax already work in other contexts?

    • Example: "RESUMABLE works for ALTER INDEX but not ALTER TABLE"
    • This indicates a validation issue rather than missing grammar

Step 2: Determine Feature Type

Based on the answers, classify the feature into one of these types:

Type A: Validation-Only Fix

Indicators:

  • Similar syntax already works elsewhere in SQL
  • Error message says "Option 'X' is not valid..." or "Feature 'Y' not supported..."
  • The grammar may already parse it, but validation blocks it

→ Route to: grammar_validation.guidelines.instructions.md

Type B: New Grammar/Syntax

Indicators:

  • Completely new statement, clause, or operator
  • Error says "Incorrect syntax near..." or "Unexpected token..."
  • Parser doesn't recognize the syntax at all

→ Route to: bug_fixing.guidelines.instructions.md

Type C: New System Function

Indicators:

  • Adding a new built-in T-SQL function (e.g., JSON_OBJECT, STRING_AGG)
  • May need special handling for RETURN statement contexts

→ Route to: function.guidelines.instructions.md

Type D: New Data Type

Indicators:

  • Adding a completely new SQL Server data type (e.g., VECTOR, GEOGRAPHY)
  • Requires custom parameter syntax (e.g., VECTOR(1536, FLOAT32))

→ Route to: new_data_types.guidelines.instructions.md

Type E: New Index Type

Indicators:

  • Adding a new CREATE INDEX variant (e.g., VECTOR INDEX, JSON INDEX)
  • Requires specialized syntax different from standard indexes

→ Route to: new_index_types.guidelines.instructions.md

Type F: Parser Predicate Recognition (Parentheses)

Indicators:

  • Identifier-based predicate works without parentheses but fails with them
  • Error near closing parenthesis: WHERE (REGEXP_LIKE(...)) fails

→ Route to: parser.guidelines.instructions.md

Type G: Database Option (ALTER/CREATE DATABASE SET)

Indicators:

  • Adding a database option for ALTER DATABASE or CREATE DATABASE statements
  • Simple ON/OFF option (e.g., SET AUTOMATIC_INDEX_COMPACTION = ON)
  • Complex option with sub-options (e.g., SET CHANGE_TRACKING (AUTO_CLEANUP = ON))
  • Enum-based option (e.g., SET RECOVERY FULL)

→ Route to: database_option.guidelines.instructions.md

Step 3: Grammar Rule Development

For grammar changes (Types B, C, D, E, G), follow this workflow:

3.1 Identify Target Grammar File

First, determine the latest parser version: Check SqlScriptDom/Parser/TSql/SqlVersionFlags.cs for the highest TSql version enum value.

Then select the appropriate grammar file:

For SQL Server (on-premises):

  • SQL 2014+: TSql120.g
  • SQL 2016+: TSql130.g
  • SQL 2017+: TSql140.g
  • SQL 2019+: TSql150.g
  • SQL 2022+: TSql160.g
  • SQL 2025+: TSql170.g
  • SQL Server vNext: TSql180.g

For Azure SQL Database, Fabric, VNext:

  • Use the latest parser version (currently TSql180.g)
  • Check SqlVersionFlags.cs to confirm the highest version

For Fabric DW:

  • Use TSqlFabricDW.g (separate parser with Fabric-specific syntax)

3.2 Grammar Development Patterns

Reference: grammar.guidelines.instructions.md

Key patterns:

  • Use this.FragmentFactory.CreateFragment<Type>() for AST nodes
  • Use syntactic predicates (LA(1) == Token) for lookahead
  • Avoid modifying shared grammar rules (create specialized versions instead)
  • Include proper script generation in ScriptGenerator

3.3 AST Updates

If adding new AST nodes:

  1. Edit SqlScriptDom/Parser/TSql/Ast.xml
  2. Rebuild to regenerate visitor classes
  3. Implement script generation in SqlScriptDom/ScriptDom/SqlServer/ScriptGenerator

Step 4: Testing

Reference: testing.guidelines.instructions.md

4.1 Create Test Files

  1. Input Script: Test/SqlDom/TestScripts/YourFeature.sql

    • Include all syntax variations
    • Test edge cases, parameters, expressions

  2. Baseline: Test/SqlDom/Baselines<version>/YourFeature.sql

    • Expected pretty-printed output after parse → script gen → reparse

4.2 Add Test Method

For SQL Server versions (TSql120-180):

In Test/SqlDom/Only<version>SyntaxTests.cs:

[TestMethod]
public void YourFeatureTest()
{
    ParserTest<version>("YourFeature.sql");
}

For Fabric DW:

Add to the OnlyFabricDWTestInfos array in Test/SqlDom/OnlyFabricDWSyntaxTests.cs:

new ParserTestFabricDW("YourFeatureFabricDW.sql", 
    nErrors80: X, nErrors90: Y, nErrors100: Z, 
   nErrors110: A, nErrors120: B, nErrors130: C, 
   nErrors140: D, nErrors150: E, nErrors160: F, nErrors170: G, nErrors180: H),

(Set expected error counts for each parser version)

Simplified patterns available - see testing guidelines for:

  • ParserTestAllowingErrors - for scripts with expected parse errors
  • ParserTestOutput constructors - for custom output verification
  • Error message verification - exact match or fragment-based

4.3 Run Tests

# Build to regenerate parser
dotnet build SqlScriptDom/Microsoft.SqlServer.TransactSql.ScriptDom.csproj -c Debug

# Run specific test
dotnet test --filter "FullyQualifiedName~YourFeatureTest" -c Debug

# ALWAYS run full suite before committing
dotnet test Test/SqlDom/UTSqlScriptDom.csproj -c Debug

Step 5: Debugging and Troubleshooting

Reference: debugging_workflow.guidelines.instructions.md

Common issues:

  • Tests fail after grammar changes: Likely modified a shared rule - create specialized version
  • Script generation mismatch: Update ScriptGenerator for your AST nodes
  • Version errors: Check validation code in TSql80ParserBaseInternal.cs

Step 6: Implementation Checklist

Before marking the feature complete:

  • Grammar rules added/modified in correct TSql<version>.g or TSqlFabricDW.g file
  • AST nodes defined in Ast.xml (if new nodes needed)
  • Script generation implemented for new AST nodes
  • Validation rules updated (if version-gated)
  • Test script created in TestScripts/ (use appropriate suffix: .sql or FabricDW.sql)
  • Baseline created in Baselines<version>/ or BaselinesFabricDW/
  • Test method added to Only<version>SyntaxTests.cs or OnlyFabricDWSyntaxTests.cs
  • All tests pass (including full test suite)
  • No regressions in unrelated tests

Quick Reference: File Locations

Component Path
Grammar files (SQL Server) SqlScriptDom/Parser/TSql/TSql*.g
Grammar file (Fabric DW) SqlScriptDom/Parser/TSql/TSqlFabricDW.g
Version detection SqlScriptDom/Parser/TSql/SqlVersionFlags.cs
AST definition SqlScriptDom/Parser/TSql/Ast.xml
Script generator SqlScriptDom/ScriptDom/SqlServer/ScriptGenerator
Validation code SqlScriptDom/Parser/TSql/TSql80ParserBaseInternal.cs
Test scripts Test/SqlDom/TestScripts/
Baselines Test/SqlDom/Baselines<version>/ or BaselinesFabricDW/
Test classes Test/SqlDom/Only<version>SyntaxTests.cs or FabricDWSyntaxTests.cs

Agent Workflow

When invoked, the agent should:

  1. Determine the latest parser version by reading SqlScriptDom/Parser/TSql/SqlVersionFlags.cs
  2. Interview the user using the questions in Step 1
    • Ask about platform (SQL Server, Azure SQL DB, Fabric, Fabric DW, VNext)
    • For Azure/Fabric/VNext → use latest parser version
    • For Fabric DW → use TSqlFabricDW.g
    • For SQL Server → ask for specific version
  3. Classify the feature type using Step 2 criteria
  4. Load the appropriate instruction file using read_file tool
  5. Guide through implementation by referencing specific sections of the loaded instruction file
  6. Execute the development workflow:
    • Make grammar/validation changes
    • Regenerate parser (build)
    • Create test files
    • Run tests
    • Debug failures
  7. Verify completeness using Step 6 checklist

Never duplicate content from instruction files - always reference and quote specific sections.