target audience

Mastering TjanSQL: A Complete Guide TjanSQL is a unique, lightweight, single-user relational database engine implemented as a native Pascal/Delphi object that relies on plain-text flat files (semi-colon or comma-separated values) for data storage. Despite its compact architecture, it processes tables and recordsets entirely in-memory to provide high-performance operations with semi-compiled expressions.

This complete guide explores how to configure, utilize, and maximize TjanSQL for your applications, embeddable systems, or testing environments. Core Features and Syntax Support

TjanSQL balances the simplicity of text-file storage with a surprisingly robust implementation of the Structured Query Language (SQL).

Data Manipulation Language (DML): Fully supports SELECT (including table joins, field aliases, and calculated fields), INSERT (both direct values and sub-select structures), UPDATE, and DELETE.

Data Definition Language (DDL): Supports standard schema modification queries like CREATE TABLE, DROP TABLE, and ALTER TABLE.

Nested Subqueries & Joins: Allows the joining of an unlimited number of tables and handles nested subqueries, IN clauses, GROUP BY, HAVING, and conditional ORDER BY configurations.

Mathematical & Text Functions: Built-in operations include statistical aggregates (COUNT, SUM, AVG, MAX, MIN, stdDev), text manipulation (UPPER, LOWER, TRIM, LEFT, MID, RIGHT, LEN, SOUNDEX), and math capabilities (SQR, SQRT). Technical Architecture

Understanding the lifecycle of a TjanSQL query execution is crucial to mastering its implementation.

+——————+ +————————–+ | Text File (.txt)| —-> | TJanSQL In-Memory Table | | (Semi-colon/CSV)| | (Complete Recordset) | +——————+ +————————–+ | v +——————+ +————————–+ | SQL Execution | —-> | Semi-Compiled Expression | | (SELECT/UPDATE) | | Parser Engine | +——————+ +————————–+ | v +————————–+ | Persistent Data Save | | (COMMIT / SAVE TABLE) | +————————–+

Because TjanSQL runs as an in-memory database, files are loaded at connection, manipulated dynamically, and only written back to disk upon specific execution instructions. Getting Started: Basic Connection and Schema Creation

The absolute first instruction you must execute when using the TjanSQL engine is the connection statement. Connecting to a Database Directory

In engines powered by TjanSQL (such as ZMSql), a “database” is simply a dedicated folder on your file system containing your flat text tables. CONNECT TO ‘C:\MyData\TjanDB\’; Use code with caution. Creating Tables

When defining columns, types are parsed seamlessly, and the file engine registers a new schema configuration mapping.

CREATE TABLE users ( userid INT, username VARCHAR(50), email VARCHAR(100), signup_date DATE ); Use code with caution. Advanced Query Techniques

TjanSQL supports sophisticated expressions and dataset management mechanisms that make it powerful despite its small footprint. Using Table Aliases, Joins, and Aggregate Functions

You can run complex multi-table analysis reporting by combining aggregates like standard deviation (stdDev) with multi-layer inner logic.

SELECT u.username, COUNT(o.orderid) AS total_orders, AVG(o.amount) AS average_spend, stdDev(o.amount) AS spend_variance FROM users u LEFT JOIN orders o ON u.userid = o.userid WHERE o.status = ‘COMPLETED’ GROUP BY u.username HAVING COUNT(o.orderid) > 5; Use code with caution. Temporary Tables and Persistence Management

To prevent performance degradation during highly complex intermediate data manipulation, TjanSQL offers specialized clauses like ASSIGN TO and SAVE TABLE to store records without hitting raw text files repeatedly.

– Step 1: Assign a complex query result to a named temporary memory table SELECT userid, username FROM users WHERE signup_date >= ‘2026-01-01’ ASSIGN TO temp_new_users; – Step 2: Use the temporary table in a sub-query update UPDATE orders SET discount = 0.15 WHERE userid IN (SELECT userid FROM temp_new_users); – Step 3: Persist the records down to the hard drive file system SAVE TABLE orders; COMMIT; Use code with caution. Performance Optimization and Best Practices

To extract maximum processing speed when mastering TjanSQL implementations, keep the following principles in mind:

Leverage the In-Memory Sandbox: All processing remains strictly in-memory until a COMMIT or SAVE TABLE command is issued. Utilize this design to experiment with massive datasets without risking file-lock issues or slow read/write times.

Format ISO Dates: Ensure date values conform strictly to ISO 8601 formatting conventions (YYYY-MM-DD) to prevent syntax evaluation faults during sort or comparison queries.

Minimize Raw Text Imports: Since engines utilizing TjanSQL (like ZMQueryDataset) parse tables sequentially out of comma-separated layouts, strip trailing spaces and unused columns from the source text files before running connections to optimize RAM consumption. If you want to dive deeper, let me know:

Are you integrating TjanSQL into a Delphi or Lazarus/Pascal development pipeline?

What is the estimated row count or file size of your flat-text dataset?

Do you need help debugging a specific join or nested subquery syntax error? TjanSQL has been ported to Lazarus