This documentation is part of the "Projects with Books" initiative at zenOSmosis.

The source code for this project is available on GitHub.

Test Organization Structure

Relevant source files

Purpose and Scope

This document describes the organizational structure of the SQL Logic Test corpus extracted from SQLite's Fossil repository. It explains how test files are categorized, the directory hierarchy used for storage, and the taxonomy that governs test placement within the test/ directory.

For information about how these tests are extracted from the upstream repository, see Corpus Extraction Pipeline. For details on the file format and directives used within individual test files, see Test File Format Specification.

Test Taxonomy

The corpus employs a two-tier categorization system that separates tests based on their validation purpose:

Category	Directory	Purpose	Focus Area
Evidence Tests	`test/evidence/`	Validate SQL language specification compliance	DDL, DML, DQL correctness
Index Tests	`test/index/`	Validate query optimization behavior	Index usage, query planning

Evidence tests ensure that SQL statements execute according to specification, verifying the semantic correctness of database operations. Index tests focus on performance-related behaviors, particularly how the query optimizer utilizes indexes to execute queries efficiently.

Sources: Based on system architecture analysis and repository structure.

graph TB
    subgraph "test/ Root Directory"
        ROOT["test/"]
subgraph "Evidence Directory"
            EVIDENCE["test/evidence/"]
subgraph "DDL Tests"
                CREATE_TRIGGER["slt_lang_createtrigger.test"]
CREATE_VIEW["slt_lang_createview.test"]
DROP_INDEX["slt_lang_dropindex.test"]
DROP_TABLE["slt_lang_droptable.test"]
DROP_TRIGGER["slt_lang_droptrigger.test"]
DROP_VIEW["slt_lang_dropview.test"]
REINDEX["slt_lang_reindex.test"]
end
            
            subgraph "DML Tests"
                REPLACE["slt_lang_replace.test"]
UPDATE["slt_lang_update.test"]
end
            
            subgraph "DQL Tests"
                IN1["in1.test"]
IN2["in2.test"]
AGGFUNC["slt_lang_aggfunc.test"]
end
        end
        
        subgraph "Index Directory"
            INDEX["test/index/"]
subgraph "BETWEEN Tests"
                BETWEEN["between/"]
BETWEEN_1["between/1/"]
BETWEEN_10["between/10/"]
BETWEEN_100["between/100/"]
BETWEEN_1_FILE["slt_good_0.test"]
BETWEEN_10_FILES["slt_good_0.test through slt_good_5.test"]
BETWEEN_100_FILE["slt_good_0.test"]
end
        end
    end
    
 
   ROOT --> EVIDENCE
 
   ROOT --> INDEX
    
 
   EVIDENCE --> CREATE_TRIGGER
 
   EVIDENCE --> CREATE_VIEW
 
   EVIDENCE --> DROP_INDEX
 
   EVIDENCE --> DROP_TABLE
 
   EVIDENCE --> DROP_TRIGGER
 
   EVIDENCE --> DROP_VIEW
 
   EVIDENCE --> REINDEX
 
   EVIDENCE --> REPLACE
 
   EVIDENCE --> UPDATE
 
   EVIDENCE --> IN1
 
   EVIDENCE --> IN2
 
   EVIDENCE --> AGGFUNC
    
 
   INDEX --> BETWEEN
 
   BETWEEN --> BETWEEN_1
 
   BETWEEN --> BETWEEN_10
 
   BETWEEN --> BETWEEN_100
    
 
   BETWEEN_1 --> BETWEEN_1_FILE
 
   BETWEEN_10 --> BETWEEN_10_FILES
 
   BETWEEN_100 --> BETWEEN_100_FILE

Directory Structure Overview

Diagram: Test Corpus Directory Hierarchy

This diagram maps the physical directory structure to actual file paths in the repository. The test/evidence/ directory contains flat files named by SQL language feature, while test/index/ uses nested directories to organize tests by operator and configuration parameters.

Sources:

Evidence Tests: SQL Language Specification

Evidence tests validate that SQL statements behave according to documented specifications. These tests are stored as individual files in test/evidence/, with filenames following the pattern slt_lang_<feature>.test for language features or descriptive names for operator tests.

Diagram: Evidence Test File Organization by SQL Category

graph LR
    subgraph "Evidence Test Categories"
        DDL["Data Definition Language"]
DML["Data Manipulation Language"]
DQL["Data Query Language"]
end
    
    subgraph "DDL Test Files"
 
       DDL --> CT["slt_lang_createtrigger.test"]
DDL --> CV["slt_lang_createview.test"]
DDL --> DI["slt_lang_dropindex.test"]
DDL --> DT["slt_lang_droptable.test"]
DDL --> DTR["slt_lang_droptrigger.test"]
DDL --> DV["slt_lang_dropview.test"]
DDL --> RI["slt_lang_reindex.test"]
end
    
    subgraph "DML Test Files"
 
       DML --> REP["slt_lang_replace.test"]
DML --> UPD["slt_lang_update.test"]
end
    
    subgraph "DQL Test Files"
 
       DQL --> IN1_F["in1.test"]
DQL --> IN2_F["in2.test"]
DQL --> AGG["slt_lang_aggfunc.test"]
end

File Naming Convention

Evidence test files follow these naming patterns:

Pattern	Description	Example
`slt_lang_<operation>.test`	Tests for specific SQL language operations	`slt_lang_createtrigger.test`
`<operator><number>.test`	Tests for SQL operators with variations	`in1.test`, `in2.test`
`slt_lang_<function_category>.test`	Tests for groups of related functions	`slt_lang_aggfunc.test`

The slt_lang_ prefix indicates tests that validate language specification compliance, often including references to specific evidence markers in the SQLite documentation (e.g., EVIDENCE-OF: R-10346-40046).

Sources:

Index Tests: Query Optimization Validation

Index tests are organized hierarchically to support parameterized test generation across different configurations. Unlike evidence tests, index tests use nested directories to represent test variations.

Diagram: Index Test Directory Hierarchy for BETWEEN Operator

graph TB
    INDEX_ROOT["test/index/"]
subgraph "Operator Directories"
        BETWEEN_DIR["between/"]
end
    
    subgraph "Configuration Directories"
        CONFIG_1["1/"]
CONFIG_10["10/"]
CONFIG_100["100/"]
end
    
    subgraph "Test Files"
        FILE_1["slt_good_0.test"]
FILES_10_0["slt_good_0.test"]
FILES_10_1["slt_good_1.test"]
FILES_10_2["slt_good_2.test"]
FILES_10_3["slt_good_3.test"]
FILES_10_4["slt_good_4.test"]
FILES_10_5["slt_good_5.test"]
FILE_100["slt_good_0.test"]
end
    
 
   INDEX_ROOT --> BETWEEN_DIR
    
 
   BETWEEN_DIR --> CONFIG_1
 
   BETWEEN_DIR --> CONFIG_10
 
   BETWEEN_DIR --> CONFIG_100
    
 
   CONFIG_1 --> FILE_1
    
 
   CONFIG_10 --> FILES_10_0
 
   CONFIG_10 --> FILES_10_1
 
   CONFIG_10 --> FILES_10_2
 
   CONFIG_10 --> FILES_10_3
 
   CONFIG_10 --> FILES_10_4
 
   CONFIG_10 --> FILES_10_5
    
 
   CONFIG_100 --> FILE_100
    
    note1["Path: test/index/between/1/slt_good_0.test"]
note2["Path: test/index/between/10/slt_good_0.test"]
note3["Path: test/index/between/100/slt_good_0.test"]
CONFIG_1 -.-> note1
 
   CONFIG_10 -.-> note2
 
   CONFIG_100 -.-> note3

Directory Structure Pattern

Index tests follow a three-level hierarchy:

Operator Level (test/index/<operator>/): Top-level directory named after the SQL operator being tested
Configuration Level (test/index/<operator>/<config>/): Numeric directories representing test configuration parameters (e.g., data volume, index cardinality)
Test Files (test/index/<operator>/<config>/slt_good_<n>.test): Individual test files numbered sequentially

The numeric configuration directories (1, 10, 100) typically represent scaling factors or data set sizes, allowing the same test logic to be validated across different performance scenarios.

Sources: Based on repository structure analysis from high-level architecture diagrams.

Cross-Database Compatibility Markers

Test files in both evidence/ and index/ directories use conditional execution directives to control test execution based on the target database platform. These directives are embedded directly in test files:

Diagram: Conditional Execution Flow in Test Files

flowchart TD
    TEST_START["Test File Execution Begins"]
READ_LINE["Read Next Line"]
CHECK_DIRECTIVE{"Line Type?"}
ONLYIF["onlyif <database>"]
SKIPIF["skipif <database>"]
HALT["halt"]
SQL["SQL Statement or Query"]
EVAL_ONLYIF{"Database\nMatches?"}
EVAL_SKIPIF{"Database\nMatches?"}
EXECUTE["Execute Statement/Query"]
SKIP["Skip Block"]
HALT_EXEC["Stop File Execution"]
TEST_START --> READ_LINE
 
   READ_LINE --> CHECK_DIRECTIVE
    
 
   CHECK_DIRECTIVE -->|Directive| ONLYIF
 
   CHECK_DIRECTIVE -->|Directive| SKIPIF
 
   CHECK_DIRECTIVE -->|Directive| HALT
 
   CHECK_DIRECTIVE -->|Content| SQL
    
 
   ONLYIF --> EVAL_ONLYIF
 
   SKIPIF --> EVAL_SKIPIF
    
 
   EVAL_ONLYIF -->|Yes| READ_LINE
 
   EVAL_ONLYIF -->|No| SKIP
    
 
   EVAL_SKIPIF -->|Yes| SKIP
 
   EVAL_SKIPIF -->|No| READ_LINE
    
 
   HALT --> EVAL_ONLYIF
 
   EVAL_ONLYIF -->|Yes| HALT_EXEC
    
 
   SQL --> EXECUTE
 
   EXECUTE --> READ_LINE
 
   SKIP --> READ_LINE
    
 
   HALT_EXEC -.-> END_FILE["End of File Processing"]
READ_LINE -.->|EOF| END_FILE

Example directives from evidence tests:

Platform exclusion: test/evidence/in1.test:4-5 demonstrates halting execution for MSSQL
Platform-specific tests: test/evidence/in1.test:22-26 shows SQLite-only tests for empty RHS in IN operator
SQLite extensions: test/evidence/in1.test:69-73 demonstrates table-as-operand syntax unique to SQLite

These directives enable a single corpus to serve multiple database platforms while accommodating platform-specific SQL dialects and feature sets.

Sources:

Evidence Documentation Markers

Evidence tests include special comment markers that reference specific sections of SQLite's documentation. These markers follow the pattern EVIDENCE-OF: R-<number>-<number> and serve as bidirectional traceability links between test assertions and specification requirements:

Diagram: Evidence Marker Traceability

graph LR
    subgraph "Test File Structure"
        COMMENT["# Comment with EVIDENCE-OF marker"]
DIRECTIVE["Conditional execution directive"]
STATEMENT["statement ok / query directive"]
SQL_CODE["SQL code to execute"]
EXPECTED["Expected result"]
end
    
    subgraph "SQLite Documentation"
        SPEC["Specification Requirement"]
REF_ID["Reference ID: R-xxxxx-xxxxx"]
end
    
 
   COMMENT -->|References| REF_ID
 
   REF_ID -->|Documents| SPEC
 
   SPEC -->|Validated by| STATEMENT
 
   STATEMENT --> SQL_CODE
 
   SQL_CODE --> EXPECTED

Example evidence markers from test files:

Reference ID	Location	Specification Topic
`R-52275-55503`	test/evidence/in1.test:11-14	Empty set behavior for IN operator
`R-64309-54027`	test/evidence/in1.test:16-20	SQLite-specific empty list syntax
`R-10346-40046`	test/evidence/slt_lang_createtrigger.test:21-22	CREATE TRIGGER statement purpose
`R-63660-13730`	test/evidence/slt_lang_createtrigger.test:65-68	OLD/NEW reference validity rules

These markers enable developers to trace test failures back to specific requirements and understand the intended behavior being validated.

Sources:

graph LR
    subgraph "slt_lang Prefix Pattern"
        PREFIX["slt_lang_"]
OPERATION["<operation>"]
EXTENSION[".test"]
PREFIX -->|Concatenate| OPERATION
 
       OPERATION -->|Concatenate| EXTENSION
    end
    
    subgraph "Examples"
        EX1["slt_lang_createtrigger.test"]
EX2["slt_lang_dropindex.test"]
EX3["slt_lang_reindex.test"]
end
    
    subgraph "Operator Pattern"
        OP_NAME["<operator>"]
VARIANT["<variant_number>"]
OP_EXT[".test"]
OP_NAME -->|Concatenate| VARIANT
 
       VARIANT -->|Concatenate| OP_EXT
    end
    
    subgraph "Operator Examples"
        OP1["in1.test"]
OP2["in2.test"]
end

Automated generation: Sequential numbering allows test generators to create multiple test files programmatically
Parallel execution: Numbered files can be distributed across multiple test runners
Configuration grouping: All files in a configuration directory share the same test parameters

The slt_good_ prefix distinguishes these as "good" (expected to pass) test cases, allowing for future expansion with slt_bad_ tests for error condition validation.

Sources: Based on repository structure analysis and naming pattern observations.

Summary

The test organization structure implements a dual taxonomy:

Evidence tests (test/evidence/) provide flat-file organization for SQL specification validation, with filenames directly indicating the feature under test
Index tests (test/index/) use hierarchical directories to parameterize optimization tests across different configurations

This structure supports:

Discoverability: Developers can locate tests by SQL feature name or operator
Maintainability: Related tests are grouped together in predictable locations
Cross-platform testing: Conditional directives allow platform-specific test execution
Traceability: Evidence markers link tests to specification requirements
Scalability: Hierarchical index tests support configuration variations without file proliferation

For details on how to execute these tests, see Usage Guide. For information on the test file format syntax, see Test File Format Specification.

Sources:

sqlite-sqllogictest-corpus Documentation

Test Organization Structure

Purpose and Scope

Test Taxonomy

Directory Structure Overview

Evidence Tests: SQL Language Specification

File Naming Convention

Index Tests: Query Optimization Validation

Directory Structure Pattern

Cross-Database Compatibility Markers

Evidence Documentation Markers

Test File Naming and Identification

Evidence Test Naming

Index Test Naming

Summary

Keyboard shortcuts

sqlite-sqllogictest-corpus Documentation