TSQL.APP Knowledge: Metadata Management and Card Processing

Internal Architecture: Card Management

TSQL.APP uses a sophisticated metadata management system to handle card definitions, validations, and organization. By examining the database triggers on the api_card table, we can gain valuable insights into how the platform processes and validates card definitions behind the scenes.

Key Triggers on api_card Table

1. AIUD_api_card (After Insert, Update, Delete)

This trigger handles the validation and preprocessing of card definitions:

ALTER TRIGGER [dbo].[AIUD_api_card]
   ON [dbo].[api_card]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    IF TRY_CAST(SESSION_CONTEXT(N'api_save_confirmed') as INT) = 0 RETURN
    
    /* Variable declarations omitted for brevity */
    
    IF UPDATE(unparsed_reducer) OR UPDATE(unparsed_crud_create) OR UPDATE(unparsed_crud_read) 
       OR UPDATE(unparsed_crud_update) OR UPDATE(unparsed_crud_delete) OR UPDATE(unparsed_header_sql)
    BEGIN
        /* Processing code */
    END
END

Key Functions of AIUD_api_card:

Variable Substitution:
- Processes fields containing curly braces ({}) using sp_api_card_curly_replace
- Enables dynamic content in cards based on context variables
SQL Validation:
- Combines various query components (reducer, CRUD operations)
- Tests the resulting SQL for validity
- Updates the card with any error messages or clears previous errors
Synonym Creation:
- Automatically creates database synonyms if needed
- Ensures tables referenced in card definitions are accessible
Error Handling:
- Displays toast messages for validation results
- Stores error information in the card metadata

2. AI_api_card (After Insert)

This trigger handles the automatic organization of newly created cards:

ALTER TRIGGER [dbo].[AI_api_card]
   ON  [dbo].[api_card]
   AFTER  INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @menu_group_id int = (SELECT id FROM [api_menu_group] WHERE name = 'New cards')
    IF @menu_group_id IS NULL
    BEGIN
        INSERT INTO [api_menu_group](name) VALUES('New cards')
        SET @menu_group_id = SCOPE_IDENTITY()
    END
    UPDATE ac SET menu_group_id = @menu_group_id 
    FROM api_card ac JOIN inserted i ON i.id = ac.id 
    WHERE i.menu_group_id IS NULL
END

Key Functions of AI_api_card:

Automatic Menu Organization:
- Ensures a "New cards" menu group exists
- Places all newly inserted cards into this group if no group is specified
- Provides automatic organization of the application navigation

Card Performance Optimization: The Caching System

One of TSQL.APP's key performance features is its sophisticated caching system. The sp_api_card_create_cache stored procedure reveals how TSQL.APP optimizes card performance through pre-generating SQL and metadata:

ALTER PROCEDURE [dbo].[sp_api_card_create_cache]
    @card_id INT
    ,@toast bit = 1
AS
BEGIN
    /* Procedure code omitted for brevity */
END

Caching System Components

The procedure creates and stores several types of optimized content:

Declaration Cache (declare_cache):
- Pre-generates variable declarations for all fields
- Handles special data types like text, ntext, and image
- Provides the foundation for efficient field access
Selection Cache (select_cache):
- Generates optimized SQL to retrieve record details
- Handles calculated fields and special column types
- Creates efficient variable assignments
List View Cache (list_select_cache):
- Pre-builds SQL for list displays
- Includes picklist handling for foreign key relationships
- Incorporates defined sorting order

Performance Benefits

This caching system provides several benefits:

Reduced Query Generation Overhead:
- SQL is generated once and stored
- Subsequent access uses cached queries
- Eliminates repetitive SQL generation
Optimized Query Structure:
- Generated SQL includes NOLOCK hints for read operations
- Field ordering is pre-determined
- Special field types are properly handled
Validation at Cache Time:
- SQL is tested for validity during generation
- Errors are caught before runtime
- Invalid configurations are rejected early

Cache Generation Process

The procedure follows a sophisticated process:

Metadata Retrieval:
- Gets identity column, table name from card definition
- Retrieves field configuration from card fields
SQL Generation:
- Builds optimized selection queries
- Creates declaration blocks
- Generates list view SQL
Validation:
- Tests generated SQL for validity
- Handles errors with detailed reporting
- Sends notifications for issues
Cache Storage:
- Updates card definition with cached SQL
- Stores optimized queries for later use

Dynamic SQL Handling

The caching system employs several techniques for generating reliable dynamic SQL:

Name Quoting:
- Uses QUOTENAME to properly handle special characters in identifiers
- Prevents SQL injection and syntax errors
Parameter Replacement:
- Replaces parameters with their values
- Handles context variables for dynamic content
Type Handling:
- Properly manages data type conversions
- Special handling for LOB data types

This caching system is a key part of what makes TSQL.APP perform well even with complex data models and large datasets.

Rapid Application Development Tools

TSQL.APP provides multiple approaches to rapidly create applications from database structures - from SQL scripts to simplified text notation to interactive wizards. These tools exemplify the platform's commitment to efficient, database-driven development.

SQL DDL Import: Complex Database Structures

The sp_sys_modal_sql_import procedure transforms standard SQL DDL into application components:

ALTER PROCEDURE [dbo].[sp_sys_modal_sql_import]
AS
BEGIN
    SET NOCOUNT ON;
    /* Procedure code omitted for brevity */
END

This procedure executes SQL scripts and automatically generates cards from newly created tables, establishing proper relationships and navigation paths.

Table Builder: Simplified Text Notation

For even faster development, the sp_sys_modal_table_builder procedure provides a text-based approach to table and relationship definition:

ALTER PROCEDURE [dbo].[sp_sys_modal_table_builder]
AS
BEGIN
    SET NOCOUNT ON;
    /* Procedure code omitted for brevity */
END

This innovative tool:

Simplified Schema Definition:
- Uses an intuitive, (TAB) indentation-based syntax
- Defines tables and columns in plain text
- Specifies relationships with simple ">" notation
- Minimizes typing and formal SQL syntax
Example Syntax:

company
	code 32
	name 256
	address_id > address
address
	street
	number
	city_id > city
city
	name

Interactive Table Wizard

For users who prefer a guided approach, TSQL.APP provides an interactive table wizard via the sp_api_add_table procedure:

ALTER PROCEDURE [dbo].[sp_api_add_table]
AS
BEGIN
    SET NOCOUNT ON;
    /* Procedure code omitted for brevity */
END

This wizard provides:

Form-Based Table Creation:
- Step-by-step guided interface
- Field validation and error handling
- Auto-correction of common issues (spaces in names, etc.)
Intelligent Type Selection:
- Pre-defined data type options
- Common defaults for various use cases
- Custom type entry for advanced scenarios
Relationship Builder:
- Guided foreign key creation
- Table selection from existing databases
- Automatic constraint generation
SQL Preview:
- Shows SQL before execution
- Enables review and learning
- Tests SQL for validity
One-Click Application Generation:
- Creates database table
- Automatically generates card
- Redirects to new card interface

This wizard makes database and application development accessible to users with minimal SQL knowledge, while still following best practices for table design.

Development Approach Comparison

TSQL.APP offers a spectrum of development approaches:

Traditional SQL Scripts via sp_sys_modal_sql_import:
- Full SQL DDL control
- Complex constraints and indexes
- Detailed column specifications
- Integration with existing SQL scripts
Text-Based Schema via sp_sys_modal_table_builder:
- Simplified syntax
- Rapid prototyping
- Visual structure through indentation
- Quick relationship definition
Interactive Wizard via sp_api_add_table:
- No SQL knowledge required
- Guided step-by-step process
- Form validation and error prevention
- Immediate visual feedback
GUI-Based Design via other system tools:
- Interactive field configuration
- Visual relationship mapping
- Style and behavior customization
- Developer-friendly interfaces

These complementary approaches allow developers to choose the most efficient method for their specific needs, skill levels, and project requirements.

SQL-First Philosophy in Practice

These tools demonstrate how TSQL.APP's SQL-first philosophy manifests in practice:

Database as Foundation:
- All application components derive from database structures
- Database relationships define application navigation
- Schema changes automatically reflect in the UI
Multiple Entry Points:
- SQL experts can use familiar DDL
- Non-SQL experts can use simplified notation or interactive wizards
- All paths lead to the same integrated result
Unified Metadata Model:
- All approaches update the same metadata tables
- Consistent application behavior regardless of creation method
- Seamless integration between different development approaches

This flexibility enables diverse team members to contribute effectively to application development while maintaining a consistent, database-driven architecture.

Developer Tools Suite

TSQL.APP provides a comprehensive suite of developer tools to accelerate application development and enhance productivity. These integrated tools help developers quickly generate code, insert snippets, and create reports without leaving the platform.

1. Code Generation Tool

The sp_sys_modal_code_generator procedure creates a sophisticated code generation interface for working with database objects:

ALTER PROCEDURE [dbo].[sp_sys_modal_code_generator]
AS
BEGIN
    SET NOCOUNT ON;
    /* Procedure code omitted for brevity */
END

2. Code Insertion Tool

The sp_sys_modal_code_insert procedure provides a code snippet library and insertion tool:

ALTER PROCEDURE [dbo].[sp_sys_modal_code_insert]
    @card_field_id int
AS
BEGIN
    SET NOCOUNT ON;
    /* Procedure code omitted for brevity */
END

3. Report Generation Tool

The sp_sys_modal_report_from_view procedure enables rapid report creation from database views:

ALTER PROCEDURE [dbo].[sp_sys_modal_report_from_view]
AS
BEGIN
    SET NOCOUNT ON;
    /* Procedure code omitted for brevity */
END

This tool provides a guided workflow for report creation:

Intelligent View Analysis:
- Displays all available views in the database
- Analyzes view structure and data characteristics
- Identifies potential identifier columns
Data Sampling and Analysis:
- Identifies representative data for analysis
- Calculates distinct value counts for each column
- Aggregates sample values for preview
Report Configuration:
- Automatically highlights columns with reporting value
- Focuses on columns with multiple distinct values
- Allows selective inclusion of columns
Preview Capabilities:
- Displays representative data in formatted tables
- Shows data distributions
- Provides immediate feedback on report design

Developer Workflow Integration

These tools create a comprehensive development environment that supports the entire application lifecycle:

Analysis Phase:
- View analysis and data profiling
- Schema exploration
- Metadata inspection
Design Phase:
- Code generation from tables and views
- Report template creation
- Interface design
Implementation Phase:
- Code snippet insertion
- Component customization
- Parameter configuration
Testing Phase:
- Live previews of generated components
- Immediate visual feedback
- Sample data exploration

Benefits of Integrated Tools

This integrated toolset provides numerous benefits:

Reduced Development Time:
- Eliminates repetitive coding tasks
- Automates common development patterns
- Streamlines report creation
Increased Quality:
- Enforces consistent patterns
- Reduces manual coding errors
- Ensures proper framework usage
Enhanced Exploration:
- Facilitates data discovery
- Promotes understanding of data relationships
- Enables rapid prototyping
Improved Learning:
- Provides built-in examples
- Demonstrates best practices
- Contextual help and documentation

These developer tools exemplify TSQL.APP's integrated development approach, where analytical, design, and implementation tools work together within a unified environment.

Card Validation and Processing

When working with card definitions and their associated SQL components, TSQL.APP employs sophisticated validation and processing techniques:

SQL Validation:
- Tests dynamically generated SQL for errors
- Validates SQL before execution
- Reports detailed error information
Parameter Substitution:
- Replaces parameters with context-specific values
- Handles session context and user information
- Processes curly brace syntax for dynamic values
Name Quoting:
- Uses QUOTENAME for proper identifier quoting
- Prevents SQL injection vulnerabilities
- Handles special characters in names
Error Handling:
- Captures and reports detailed error messages
- Alerts developers to configuration issues
- Provides context for troubleshooting

These processing techniques ensure that cards operate reliably and securely within the TSQL.APP environment.

Understanding the Card Processing Workflow

Based on these triggers, we can understand how TSQL.APP processes card definitions:

Creation Phase:
- Card is created (either through automatic generation or manual configuration)
- AI_api_card trigger assigns it to a menu group
- Initial metadata is established
Validation Phase:
- AIUD_api_card processes SQL components and variable substitutions
- SQL is tested for validity
- Errors are captured and displayed
Runtime Phase:
- Processed SQL components are used in actual application execution
- Validated SQL ensures reliable operation

Metadata Structure

The api_card table contains several important fields:

Basic Metadata: id, name, tablename, basetable
CRUD Operations: crud_create, crud_read, crud_update, crud_delete
Query Filtering: reducer
Advanced Display: header_sql
Validation: error (stores any validation errors)

Each of these components works together to define how cards behave in the application.

Variable Substitution System

A key part of TSQL.APP's flexibility is its variable substitution system. The sp_api_card_curly_replace procedure processes text containing curly braces, replacing them with context-specific values.

Example patterns might include:

{this.FieldName} - A field from the current record
{parent.FieldName} - A field from the parent record
{user.Name} - A property of the current user
{date.Today} - Date-related values

This substitution system enables dynamic content and context-aware behavior in card definitions.

Implications for Developers

Understanding these internal processes helps developers work more effectively with TSQL.APP:

Validation Awareness:
- Cards are automatically validated
- SQL errors will be captured and displayed
- Testing complex card definitions incrementally helps isolate issues
Variable Usage:
- Taking advantage of the variable substitution system enhances flexibility
- Documentation of available variables helps develop effective cards
Menu Organization:
- Cards are automatically placed in the "New cards" group
- Organizing cards into appropriate groups improves navigation
Synonym Management:
- The system automatically creates synonyms when needed
- This reduces the need for manual synonym creation

Conclusion

The triggers on the api_card table reveal a sophisticated metadata management system that handles validation, preprocessing, and organization of card definitions. This system ensures that cards are properly validated before use, automatically organized in the application navigation, and capable of dynamic content through variable substitution.

This infrastructure allows developers to focus on the business logic and data structure while TSQL.APP handles much of the technical validation and organization behind the scenes.