Index Maintenance Tests

Relevant source files

• test/evidence/slt_lang_reindex.test

Purpose and Scope

This document covers the test suite for index maintenance operations in SQLite, specifically focusing on the REINDEX command. These tests validate the functionality of rebuilding database indices from scratch, including error handling and cross-database compatibility considerations.

For general query operator tests, see Query Operator Tests. For comprehensive index optimization tests including BETWEEN operators, see Index and Optimization Tests.

Overview

The index maintenance test suite validates the REINDEX SQL command, which is a SQLite-specific extension not included in the SQL-92 standard. The primary test file test/evidence/slt_lang_reindex.test:1-59 contains evidence points that verify the command's specification and behavior.

The REINDEX command serves a single purpose: to delete and recreate indices from scratch. This functionality is critical for:

• Repairing potentially corrupted indices
• Updating indices after collation sequence changes
• Optimizing index structure after bulk data operations

Sources: test/evidence/slt_lang_reindex.test:1-59

Test Environment Setup

The test file establishes a minimal testing environment to validate REINDEX behavior:

Test Setup Sequence:

graph TB
    subgraph "Test Database Schema"
        T1["Table: t1\nColumns: x INTEGER, y VARCHAR(8)"]
I1["Index: t1i1\nON t1(x)"]
end
    
    subgraph "Test Data"
        R1["Row 1: (1, 'true')"]
R2["Row 2: (0, 'false')"]
R3["Row 3: (NULL, 'NULL')"]
end
    
 
   T1 -->|indexed by| I1
 
   T1 -->|contains| R1
 
   T1 -->|contains| R2
 
   T1 -->|contains| R3

Sources: test/evidence/slt_lang_reindex.test:3-16

Cross-Database Compatibility

Platform Support Matrix

The REINDEX command is not universally supported across database platforms. The test file uses conditional execution directives to handle platform-specific behavior:

Conditional Execution Flow

Halt Directives:

• test/evidence/slt_lang_reindex.test:22-24 - Halts execution for MSSQL
• test/evidence/slt_lang_reindex.test:26-28 - Halts execution for Oracle
• test/evidence/slt_lang_reindex.test:30-32 - Halts execution for MySQL

Sources: test/evidence/slt_lang_reindex.test:18-32

graph LR
    CMD["REINDEX t1i1"]
IDX["Index: t1i1"]
DELETE["Delete Index"]
RECREATE["Recreate Index"]
RESULT["statement ok"]
CMD --> IDX
 
   IDX --> DELETE
 
   DELETE --> RECREATE
 
   RECREATE --> RESULT

Command Syntax Validation

The test suite validates both successful and error cases for the REINDEX command:

Valid REINDEX Command

The test at test/evidence/slt_lang_reindex.test:37-38 executes REINDEX t1i1 and expects successful completion (statement ok). This validates that an existing index can be rebuilt.

Error Handling

The test at test/evidence/slt_lang_reindex.test:40-41 validates error handling by attempting to reindex a non-existent index tXiX. The expected behavior is statement error, confirming that the database properly rejects invalid index names.

Sources: test/evidence/slt_lang_reindex.test:37-41

Evidence Points Coverage

The test file documents specific evidence points from SQLite's specification, though some remain unimplemented (marked as TBD):

graph TB
    EV1["R-52173-44778:\nREINDEX deletes and\nrecreates indices"]
T1["Test: REINDEX t1i1\n(line 38)"]
T2["Test: REINDEX tXiX\n(line 40-41)"]
EV1 --> T1
 
   EV1 --> T2
    
 
   T1 --> V1["Validates successful\nindex rebuild"]
T2 --> V2["Validates error handling\nfor invalid index"]

Implemented Evidence

Pending Test Coverage

Four evidence points are marked as "TBD" (To Be Determined), indicating planned but not yet implemented test cases:

1. Collation-based reindexing - Testing REINDEX with a collation sequence name to rebuild all indices using that collation
2. Table-based reindexing - Testing REINDEX with a table name to rebuild all indices on that table
3. Specific index reindexing - Already partially covered by existing tests, but may need additional validation
4. Name resolution - Testing disambiguation when a name matches both a collation sequence and a table/index

Sources: test/evidence/slt_lang_reindex.test:34-57

Test File Metadata

The test file begins with a hash-threshold configuration:

hash-threshold 8

This directive at test/evidence/slt_lang_reindex.test1 sets the hash threshold parameter for the test execution environment. This controls when the query engine switches between different algorithms for query processing, though the specific implications are runtime-dependent.

Sources: test/evidence/slt_lang_reindex.test1

graph TB
    subgraph "SQL Language Evidence Tests (3.1)"
        DDL["DDL Tests (3.1.1)"]
DML["DML Tests (3.1.4)"]
QUERY["Query Tests (3.1.5)"]
REINDEX["Index Maintenance (3.1.8)"]
end
    
    subgraph "REINDEX Test Components"
        SETUP["Table and Index Creation\n(DDL operations)"]
DATA["Data Insertion\n(DML operations)"]
MAINT["Index Rebuilding\n(REINDEX operation)"]
end
    
 
   DDL -.->|uses| SETUP
 
   DML -.->|uses| DATA
 
   REINDEX --> SETUP
 
   REINDEX --> DATA
 
   REINDEX --> MAINT

Integration with Test Corpus

This test file is part of the broader evidence test suite that validates SQL language compliance:

The REINDEX tests leverage DDL operations (table and index creation) and DML operations (data insertion) to establish a testable environment, then validate the index maintenance functionality.

Sources: test/evidence/slt_lang_reindex.test:1-59

Summary

The index maintenance test suite provides focused validation of the REINDEX command with the following characteristics:

The test suite demonstrates the corpus's approach to handling non-standard SQL extensions: documenting platform compatibility constraints using conditional directives, validating core functionality where supported, and marking future test coverage areas for comprehensive specification validation.

For information on how conditional execution directives work across the entire corpus, see Conditional Execution Directives. For platform-specific behavior documentation, see Platform-Specific Behaviors.

Sources: test/evidence/slt_lang_reindex.test:1-59