Page History

Understand the datasets engine architecture.

Reference → Modules → Datasets → UI | Engine

Datasets → Tutorial | Concept | How-to Guide | Reference

Overview

The Dataset Engine orchestrates the internal mechanics of database operations through a multi-layered architecture. This reference covers the deep technical aspects of how the engine processes requests internally.

The engine orchestrates:

• Synchronous and asynchronous database operationsoperation coordination
• Thread management and request queuingConnection pooling and thread management
• Client/server domain tag mappingresolution
• Query execution through TServer servicespipeline
• Result set propagation mechanisms
• Multi-database coordination

Understanding the engine internals helps when:

• Debugging complex concurrency issues
• Optimizing database for extreme performance
• Managing concurrent operations
• Implementing client-specific data
• Troubleshooting connection issues
• scenarios
• Building custom extensions
• Troubleshooting edge cases

Concurrency & Server Domain Management

Understanding Server Domain Attributes

All Dataset Module properties exist in the server domain, creating a shared resource environment:

Example Risk Scenario:
1. Client A sets: SQLStatement = "SELECT * FROM Orders WHERE Status='Open'"
2. Client B sets: SQLStatement = "SELECT * FROM Orders WHERE Status='Closed'"
3. Execute command runs with Client B's statement (last write wins)

Preventing Concurrency Conflicts

Strategy 1: Dedicated Query Objects

• Create separate query objects for different operations
• Assign unique queries to specific tasks or displays
• Avoid sharing query objects between concurrent processes

Strategy 2: Synchronization Patterns

• Use semaphores or locks in scripts
• Implement request queuing for shared resources
• Design state machines for complex operations

Strategy 3: Client-Side Processing

• Execute queries in scripts with local variables
• Process DataTables before assignment to tags
• Minimize server domain property modifications

Advanced Data Management Strategies

Concurrency Patterns

The Dataset Module provides three primary patterns for handling concurrent access:

Pattern 1: Direct Script Processing

csharp

DataTable result = @Dataset.Query.Query1.SelectCommand();
// Process data locally without server domain impact
foreach(DataRow row in result.Rows) {
    // Local processing
}

Pattern 2: Tag Distribution

csharp

// Assign to DataTable tag for module sharing
@Tag.MyDataTable = @Dataset.Query.Query1.SelectCommand();
// Now available to displays, reports, etc.

Pattern 3: Mapped Navigation

csharp

// Configure mapping, then navigate rows
@Dataset.Query.Query1.Select();
@Dataset.Query.Query1.Next(); // Moves to next row

Backup & Recovery

SQLite Backup Strategies

Option 1: Command Line Backup

bash

sqlite3 source.db ".backup backup.db"

• Simple and reliable
• Requires database file access
• Best for scheduled maintenance

Option 2: Online Backup API

• Backup while database is active
• Support for incremental backups
• Progress monitoring capability

Option 3: File System Copy

• Only when database is offline
• Fastest for large databases
• Requires downtime

Backup Best Practices

1. Schedule: Automate backups during low-activity periods
2. Verify: Test restore procedures monthly
3. Rotate: Maintain multiple backup generations
4. Secure: Store backups in separate physical location
5. Document: Maintain restore procedure documentation

Production Deployment Considerations

Design Principles

1. Isolation: Use dedicated query objects for different operations
2. Filtering: Always limit result sets with WHERE clauses
3. Security: Use parameterized queries exclusively
4. Monitoring: Track performance metrics and errors
5. Planning: Design for concurrent access from the start

Production Checklist

•  Before deploying to production:
•  Parameterized all dynamic queries
•  Implemented error handling for all operations
•  Tested concurrent access scenarios
•  Configured appropriate timeouts
•  Established backup procedures
•  Documented recovery processes
•  Verified timezone handling
•  Optimized query performance
•  Planned for data growth
•  Secured network access

Architecture

Process Separation

The Dataset Module runs as TRunModule.exe (Dataset):

• Reads all dataset configurations
• Does NOT directly connect to databases
• Consumes TServer database services
• Manages tag mapping and updates

TServer provides:

• Actual database connections
• SQL execution services
• Connection pooling
• Transaction management

Connection Management

Single Thread per DB

• Each DB configuration creates ONE connection
• Single execution thread per database
• Sequential command execution

Parallel Execution

For concurrent operations to same database:

DB1 → Production Database (Thread 1)
DB2 → Production Database (Thread 2)
DB3 → Production Database (Thread 3)

Create multiple DB connections to same database for parallelism.

Execution Methods

Asynchronous Execution (Default)

Trigger: Property changes (Select, Insert, Update, Delete)

Flow:

1. Property triggered (screen/script)
2. Request propagated to server
3. Dataset Module receives request
4. TServer executes database operation
5. Results returned to Dataset Module
6. Tags mapped and updated
7. Execution continues in parallel

Advantages:

• Non-blocking operation
• Better performance
• Prevents UI freezing
• Allows parallel operations

Use Cases:

• Display queries
• Background updates
• Report generation
• Real-time monitoring

Synchronous Execution

Trigger: Method calls (SelectCommand, ExecuteCommand)

Flow:

1. Method called
2. Execution PAUSES
3. Dataset Module calls TServer
4. Database operation completes
5. Results returned
6. Tags mapped
7. Execution RESUMES

Advantages:

• Guaranteed completion
• Sequential logic
• Immediate results
• Transaction support

Risks:

• Can freeze UI if called from screen
• Blocks thread execution
• Performance bottlenecks

Use Cases:

• Script tasks
• Sequential operations
• Transaction requirements
• Data validation

Tag Domain Mapping

Execution Context

Tag mapping occurs in the original call domain:

Client-Initiated Calls:

• From displays or client scripts
• Mapping uses client domain
• Results visible to specific client
• Isolated from other clients

Server-Initiated Calls:

• From server scripts or tasks
• Mapping uses server domain
• Results visible project-wide
• Shared across all clients

Domain Selection Strategy

Performance Optimization

Connection Pooling

• Reuse existing connections
• Minimize connection overhead
• Configure pool size appropriately

Query Optimization

sql

-- Use parameters to prevent recompilation
SELECT * FROM Table WHERE ID = @id

-- Limit result sets
SELECT TOP 100 * FROM LargeTable

-- Use appropriate indexes
CREATE INDEX idx_timestamp ON Data(Timestamp)

Asynchronous Patterns

csharp

// Good: Asynchronous from screen
@Dataset.Table.MyTable.SelectCommand = "SELECT * FROM Data";

// Bad: Synchronous from screen (blocks UI)
@Dataset.Table.MyTable.SelectCommandWithStatus();

Batch Operations

• Group multiple operations
• Use transactions for consistency
• Minimize round trips

Threading Model

Dataset Module Thread

• Main coordination thread
• Manages request queue
• Handles tag mapping

TServer Database Threads

• One per DB connection
• Sequential execution per thread
• Connection isolation

Client Request Threads

• Asynchronous request handling
• Non-blocking UI operations
• Parallel client support

Error Handling

Connection Failures

• Automatic retry logic
• Connection pooling recovery
• Error event propagation

Query Errors

• Exception capture in TServer
• Error property population
• Tag mapping skipped on error

Timeout Management

• Configurable command timeout
• Connection timeout settings
• Automatic cleanup

Best Practices Checklist 

•  Prefer asynchronous - Use properties over methods
•  Avoid UI blocking - No synchronous calls from screens
•  Use appropriate domains - Client for user, Server for shared
•  Pool connections - Reuse database connections
•  Optimize queries - Index, parameterize, limit
•  Handle errors - Check status properties
•  Monitor performance - Track execution times

Troubleshooting

Slow queries:

• Check execution plan
• Add appropriate indexes
• Reduce result set size
• Use asynchronous execution

Connection issues:

• Verify TServer running
• Check connection string
• Review firewall rules
• Monitor connection pool

Tag mapping problems:

• Verify domain selection
• Check tag existence
• Review mapping configuration
• Confirm execution context

Thread blocking:

• Avoid synchronous in UI
• Use Script Tasks
• Implement async patterns
• Monitor thread pool

In this section...