TSQL.APP Framework LLM Training Data Generator
Introduction
This documentation describes a specialized T-SQL script developed specifically for the TSQL.APP Framework (https://www.t-sql.app). The script generates JSONL (JSON Lines) formatted data used for fine-tuning Large Language Models (LLMs) to better understand and interact with TSQL.APP's stored procedures and framework components.
Background
TSQL.APP is an innovative web application framework that enables developers to build complete business applications using primarily SQL Server and T-SQL. The framework represents a paradigm shift in application development by eliminating the traditional separation between frontend and backend, allowing developers to create full-featured applications directly from their database.
This tool serves as a bridge between TSQL.APP's stored procedures and modern AI language models, enabling better developer assistance and documentation generation.
Purpose and Integration with TSQL.APP
The primary purpose of this tool is to convert TSQL.APP Framework's stored procedures and their metadata into training data for LLMs. This integration serves several key purposes within the TSQL.APP ecosystem:
-
The tool generates comprehensive documentation for TSQL.APP's extensive library of stored procedures, focusing particularly on the framework's modal and UI-related procedures.
-
It structures the data in a way that helps LLMs understand TSQL.APP's unique approach to SQL-first application development, including its handling of modal dialogs, form generation, and data operations.
-
The generated training data helps LLMs provide more accurate and contextual assistance for developers working within the TSQL.APP Framework.
Technical Implementation in TSQL.APP Context
Data Processing Flow
The script processes TSQL.APP stored procedures through several stages:
-
Procedure Definition Extraction The system begins by extracting stored procedure definitions from TSQL.APP's framework database, focusing on procedures that follow the framework's naming conventions and patterns, such as procedures prefixed with 'sp_api_'.
-
Definition Processing The tool processes the extracted definitions according to TSQL.APP's specific requirements, including proper handling of Unicode strings and framework-specific parameter patterns.
-
Parameter Analysis Special attention is given to TSQL.APP's parameter conventions, including the framework's standard output parameters and modal interaction patterns.
-
Framework-Specific Validation The system validates parameters against TSQL.APP's mandated practices and coding standards.
TSQL.APP Integration Points
The script is specifically designed to work with TSQL.APP's architecture:
- It understands TSQL.APP's modal dialog patterns and parameter conventions
- It recognizes framework-specific data types and parameter naming patterns
- It maintains awareness of TSQL.APP's mandatory practices for stored procedures
Code Implementation
--T-SQL JSONL FineTuning Data Creator.sql
--v19
-- Maak eerste temp tabel voor procedure definitie
DROP table if exists #ProcDef
CREATE TABLE #ProcDef (
object_id int,
ProcedureName nvarchar(128),
Definition nvarchar(max)
);
-- Vul de eerste temp tabel
INSERT INTO #ProcDef
SELECT
p.object_id,
p.name AS ProcedureName,
LOWER(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
m.definition,
CHAR(160), ' '
),
CHAR(9), ' '
),
CHAR(13), ' '
),
CHAR(10), ' '
)
)
FROM sys.procedures p
JOIN sys.sql_modules m ON p.object_id = m.object_id
WHERE p.name = 'sp_api_modal_input';
-- Maak en vul tweede temp tabel voor getrimde definitie
DROP table if exists #TrimmedDef
CREATE TABLE #TrimmedDef (
object_id int,
ProcedureName nvarchar(128),
Definition nvarchar(max)
);
INSERT INTO #TrimmedDef
SELECT
object_id,
ProcedureName,
LTRIM(RTRIM(
SUBSTRING(
Definition,
CHARINDEX('procedure', Definition) + 9,
CHARINDEX(' as ', Definition + ' as ') - CHARINDEX('procedure', Definition) - 9
)
))
FROM #ProcDef;
-- Maak en vul parameter details temp tabel
DROP table if exists #ParamDetails
CREATE TABLE #ParamDetails (
object_id int,
ProcedureName nvarchar(128),
ParameterName nvarchar(128),
ParameterOrder int,
ParameterType nvarchar(128),
ParameterMaxLength smallint,
IsOutput bit,
ParamText nvarchar(max),
HasDefaultValue bit,
DefaultValue nvarchar(max)
);
INSERT INTO #ParamDetails
SELECT
td.object_id,
td.ProcedureName,
prm.name,
prm.parameter_id,
TYPE_NAME(prm.user_type_id),
prm.max_length,
prm.is_output,
LTRIM(RTRIM(SUBSTRING(
td.Definition,
CHARINDEX(prm.name, td.Definition),
CASE
WHEN CHARINDEX(',', td.Definition, CHARINDEX(prm.name, td.Definition)) > 0
THEN CHARINDEX(',', td.Definition, CHARINDEX(prm.name, td.Definition)) - CHARINDEX(prm.name, td.Definition)
ELSE LEN(td.Definition) - CHARINDEX(prm.name, td.Definition) + 1
END
))),
CASE
WHEN CHARINDEX('=', SUBSTRING(
td.Definition,
CHARINDEX(prm.name, td.Definition),
CASE
WHEN CHARINDEX(',', td.Definition, CHARINDEX(prm.name, td.Definition)) > 0
THEN CHARINDEX(',', td.Definition, CHARINDEX(prm.name, td.Definition)) - CHARINDEX(prm.name, td.Definition)
ELSE LEN(td.Definition) - CHARINDEX(prm.name, td.Definition) + 1
END
)) > 0 THEN 1
ELSE 0
END,
CASE
WHEN CHARINDEX('=', SUBSTRING(
td.Definition,
CHARINDEX(prm.name, td.Definition),
CASE
WHEN CHARINDEX(',', td.Definition, CHARINDEX(prm.name, td.Definition)) > 0
THEN CHARINDEX(',', td.Definition, CHARINDEX(prm.name, td.Definition)) - CHARINDEX(prm.name, td.Definition)
ELSE LEN(td.Definition) - CHARINDEX(prm.name, td.Definition) + 1
END
)) > 0
THEN LTRIM(RTRIM(
SUBSTRING(
SUBSTRING(
td.Definition,
CHARINDEX(prm.name, td.Definition),
CASE
WHEN CHARINDEX(',', td.Definition, CHARINDEX(prm.name, td.Definition)) > 0
THEN CHARINDEX(',', td.Definition, CHARINDEX(prm.name, td.Definition)) - CHARINDEX(prm.name, td.Definition)
ELSE LEN(td.Definition) - CHARINDEX(prm.name, td.Definition) + 1
END
),
CHARINDEX('=', SUBSTRING(
td.Definition,
CHARINDEX(prm.name, td.Definition),
CASE
WHEN CHARINDEX(',', td.Definition, CHARINDEX(prm.name, td.Definition)) > 0
THEN CHARINDEX(',', td.Definition, CHARINDEX(prm.name, td.Definition)) - CHARINDEX(prm.name, td.Definition)
ELSE LEN(td.Definition) - CHARINDEX(prm.name, td.Definition) + 1
END
)) + 1,
1000
)
))
ELSE NULL
END
FROM #TrimmedDef td
INNER JOIN sys.parameters prm ON td.object_id = prm.object_id;
-- Voeg Required kolom toe
DROP table if exists #ParamDetailsWithRequired
CREATE TABLE #ParamDetailsWithRequired (
object_id int,
ProcedureName nvarchar(128),
ParameterName nvarchar(128),
ParameterOrder int,
ParameterType nvarchar(128),
ParameterMaxLength smallint,
IsOutput bit,
ParamText nvarchar(max),
HasDefaultValue bit,
DefaultValue nvarchar(max),
Required bit
);
INSERT INTO #ParamDetailsWithRequired
SELECT
*,
CASE
WHEN HasDefaultValue = 0 THEN 1
ELSE 0
END
FROM #ParamDetails;
-- Finale output
SELECT
OutputText
FROM (
SELECT
1 AS OutputOrder,
CAST(
(SELECT
ParameterName,
ParameterType,
Required,
ParamText,
DefaultValue
FROM #ParamDetailsWithRequired
ORDER BY ParameterOrder
FOR JSON PATH) AS nvarchar(max)
) AS OutputText
UNION ALL
SELECT
2 AS OutputOrder,
CAST(
(SELECT
'JSON Mode: Enabled' AS system,
JSON_QUERY((
SELECT
JSON_QUERY((
SELECT
CONCAT('How do I use ', pd.ProcedureName, '?') AS content
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)) AS role_user,
JSON_QUERY((
SELECT
pd.ProcedureName AS storedProcedure,
JSON_QUERY((
SELECT
ParameterName AS name,
ParameterType AS [type],
ParameterMaxLength AS [maxLength],
CASE WHEN IsOutput = 1 THEN 'output' ELSE 'input' END AS direction,
Required AS [required],
NULLIF(TRIM(DefaultValue), '') AS defaultValue
FROM #ParamDetailsWithRequired
ORDER BY ParameterOrder
FOR JSON PATH
)) AS parameters,
CONCAT('EXEC ', pd.ProcedureName, ' ...') AS example
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)) AS role_assistant
FROM (SELECT DISTINCT ProcedureName FROM #ParamDetailsWithRequired) pd
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)) AS messages
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS nvarchar(max)
)
) Combined
ORDER BY OutputOrder;
-- Opruimen temp tabellen
DROP TABLE #ProcDef;
DROP TABLE #TrimmedDef;
DROP TABLE #ParamDetails;
DROP TABLE #ParamDetailsWithRequired;
Usage in TSQL.APP Environment
When deploying this script in a TSQL.APP environment, consider the following:
-
Framework Compatibility The script is designed to work with TSQL.APP's database structure and naming conventions. It expects stored procedures to follow the framework's standard patterns.
-
Documentation Integration The generated output can be integrated with TSQL.APP's documentation system and development tools.
-
Version Compatibility Ensure the script version aligns with your TSQL.APP Framework version for optimal compatibility.
Output Format
The generated output follows a specific structure designed for LLM training:
{
"system": "JSON Mode: Enabled",
"messages": {
"role_user": {
"content": "How do I use [TSQL.APP_procedure_name]?"
},
"role_assistant": {
"storedProcedure": "[TSQL.APP_procedure_name]",
"parameters": [
{
"name": "@parameter_name",
"type": "parameter_type",
"maxLength": length_value,
"direction": "input/output",
"required": boolean_value,
"defaultValue": "default_value"
}
],
"example": "EXEC [TSQL.APP_procedure_name] ..."
}
}
}
Benefits for TSQL.APP Development
This tool provides several specific advantages for TSQL.APP development:
-
Framework Understanding Enhances LLM comprehension of TSQL.APP's unique approach to application development.
-
Documentation Automation Streamlines the creation of accurate documentation for TSQL.APP's extensive procedure library.
-
Developer Assistance Improves AI-powered assistance for developers working with the TSQL.APP Framework.
-
Quality Assurance Helps maintain consistency in procedure usage across TSQL.APP applications.
Maintenance Within TSQL.APP
When maintaining this tool in a TSQL.APP environment:
- Keep the script updated with any changes to TSQL.APP's stored procedure conventions
- Validate output against TSQL.APP's mandated practices
- Monitor for compatibility with framework updates
- Consider performance impact on development databases
Future Development
Consider these potential enhancements for better TSQL.APP integration:
- Expanded coverage of TSQL.APP's specialized procedure types
- Integration with the framework's automated testing systems
- Support for TSQL.APP's multi-tenant architecture patterns
- Enhanced documentation of framework-specific features
Notes
This tool is specifically designed for use with the TSQL.APP Framework and may require modifications for use in other environments. It reflects TSQL.APP's SQL-first philosophy and integrates seamlessly with the framework's development patterns and practices.
Version History
- v19: Initial release with TSQL.APP Framework integration
- Future versions will be documented here as they are released
Support and Resources
For more information about the TSQL.APP Framework, visit https://www.t-sql.app. For technical support or questions about this tool, please consult your TSQL.APP documentation or contact your framework administrator.