Data Definition Language (DDL) Tests

Relevant source files

• test/evidence/slt_lang_createtrigger.test
• test/evidence/slt_lang_createview.test
• test/evidence/slt_lang_dropindex.test
• test/evidence/slt_lang_droptable.test
• test/evidence/slt_lang_droptrigger.test
• test/evidence/slt_lang_dropview.test

Purpose and Scope

This page documents the Data Definition Language (DDL) test suite within the SQL Language Evidence Tests category. These tests validate the correctness of CREATE and DROP statements for database objects including tables, views, indexes, and triggers. The tests verify compliance with SQL language specifications and SQLite-specific DDL behaviors.

For detailed coverage of individual CREATE statement types, see CREATE Statement Tests. For detailed coverage of individual DROP statement types, see DROP Statement Tests. For data manipulation tests, see Data Manipulation Language (DML) Tests-tests).

Overview

DDL tests verify the schema manipulation capabilities of SQL implementations. These tests ensure that database objects can be created, exist with the correct properties, and can be removed from the schema. Each test file focuses on a specific DDL command and validates both successful operations and expected error conditions.

The test suite follows a consistent pattern:

1. Setup : Create a base table with sample data
2. Operation : Execute the DDL statement being tested
3. Verification : Confirm the operation succeeded or failed as expected
4. Cleanup : Remove created objects

Test File Locations

All DDL test files are located in the test/evidence/ directory:

Sources: test/evidence/slt_lang_createtrigger.test:1-220 test/evidence/slt_lang_createview.test:1-129 test/evidence/slt_lang_dropindex.test:1-35 test/evidence/slt_lang_droptable.test:1-56 test/evidence/slt_lang_droptrigger.test:1-54 test/evidence/slt_lang_dropview.test:1-60

Test File Structure and Evidence Mapping

Evidence-Based Testing Model

Diagram: Evidence-Based Test Validation Model

Each test file contains inline comments that reference specific evidence identifiers from SQLite's documentation. These identifiers (format: R-XXXXX-XXXXX) trace each test back to the precise specification it validates.

Sources: test/evidence/slt_lang_createtrigger.test:21-22 test/evidence/slt_lang_createview.test:19-20 test/evidence/slt_lang_dropindex.test:19-20 test/evidence/slt_lang_droptable.test:19-20 test/evidence/slt_lang_droptrigger.test:21-22 test/evidence/slt_lang_dropview.test:19-20

DDL Test Coverage Matrix

Database Object Lifecycle Testing

Diagram: Database Object State Transitions and Test Coverage

Sources: test/evidence/slt_lang_createtrigger.test:25-29 test/evidence/slt_lang_createview.test:23-28 test/evidence/slt_lang_droptrigger.test:28-36 test/evidence/slt_lang_dropview.test:31-39

DDL Command Test Matrix

Sources: All six test files in test/evidence/

Common Test Setup Pattern

All DDL test files share a consistent initialization sequence that establishes a baseline table for testing:

CREATE TABLE t1( x INTEGER, y VARCHAR(8) )
INSERT INTO t1 VALUES(1,'true')
INSERT INTO t1 VALUES(0,'false')
INSERT INTO t1 VALUES(NULL,'NULL')
CREATE INDEX t1i1 ON t1(x)

This pattern appears at slt_lang_createtrigger.test:4-16 slt_lang_createview.test:4-16 slt_lang_dropindex.test:4-16 slt_lang_droptable.test:4-16 slt_lang_droptrigger.test:4-16 and slt_lang_dropview.test:4-16

The setup provides:

• A table (t1) with integer and varchar columns
• Sample data including NULL values for comprehensive testing
• An index (t1i1) for testing cascading deletion behavior

Cross-Database Compatibility Handling

Platform-Specific Test Execution

Diagram: Cross-Database Test Execution Control Flow

Sources: test/evidence/slt_lang_createtrigger.test:18-19 test/evidence/slt_lang_dropindex.test:22-24 test/evidence/slt_lang_createview.test:47-53

Platform-Specific Behavior Differences

The test files use three primary conditional directives:

• onlyif <database>: Execute statement only on specified database
• skipif <database>: Skip statement on specified database
• halt: Stop test execution immediately (used when entire test file is incompatible)

Sources: test/evidence/slt_lang_createtrigger.test:18-19 test/evidence/slt_lang_createview.test:47-85 test/evidence/slt_lang_dropindex.test:22-34 test/evidence/slt_lang_droptable.test:49-55

Cascading Deletion and Object Dependencies

Dependency Relationships

Diagram: Object Dependency and Cascading Deletion Behavior

Cascading Deletion Tests

Table Removal Cascades :

• When a table is dropped, all associated indexes are automatically removed (slt_lang_droptable.test:36-41)
• When a table is dropped, all associated triggers are automatically removed (slt_lang_droptrigger.test:42-52)

View Removal Independence :

• When a view is dropped, underlying base tables are not modified (slt_lang_dropview.test:44-59)
• Base table data remains accessible after view deletion (slt_lang_dropview.test:56-59)

Test Verification Examples :

1. Index cascade verification at slt_lang_droptable.test:39-41:

   # this should error, as was dropped with table
   statement error
   DROP INDEX t1i1;
   

2. Trigger cascade verification at slt_lang_droptrigger.test:44-52:

   statement ok
   CREATE TRIGGER t1r1 UPDATE ON t1 BEGIN SELECT 1; END;
   
   statement ok
   DROP TABLE t1
   
   # already deleted when table dropped
   statement error
   DROP TRIGGER t1r1
   

3. View independence verification at slt_lang_dropview.test:48-59:

   query I rowsort label-0
   SELECT x FROM view2
   ----
   0
   
   statement ok
   DROP VIEW view2
   
   query I rowsort label-0
   SELECT x FROM t1 WHERE x=0
   ----
   0
   

Sources: test/evidence/slt_lang_droptable.test:36-41 test/evidence/slt_lang_droptrigger.test:42-52 test/evidence/slt_lang_dropview.test:44-59

Trigger Timing and Event Combinations

The CREATE TRIGGER test validates all combinations of trigger timing (BEFORE/AFTER) and trigger events (INSERT/UPDATE/DELETE):

All triggers are subsequently dropped at slt_lang_createtrigger.test:191-219 to verify cleanup functionality.

Sources: test/evidence/slt_lang_createtrigger.test:25-96 test/evidence/slt_lang_createtrigger.test:191-219

View Mutability Testing

Read-Only Enforcement

Views in SQLite are read-only, meaning INSERT, UPDATE, and DELETE operations are not permitted. The test file validates this behavior at slt_lang_createview.test:65-103:

SQLite-specific read-only enforcement :

onlyif sqlite
statement error
DELETE FROM view1 WHERE x>0

onlyif sqlite
statement error
INSERT INTO view1 VALUES(2,'unknown')

onlyif sqlite
statement error
UPDATE view1 SET x=2

MSSQL exception : MSSQL allows certain DELETE and UPDATE operations on views (slt_lang_createview.test:68-85), demonstrating platform-specific SQL implementation differences.

Sources: test/evidence/slt_lang_createview.test:65-103

Evidence Reference System

Evidence Identifier Format

Test files contain evidence comments that link test cases to specific sections of SQLite's documentation. The format follows the pattern:

# EVIDENCE-OF: R-XXXXX-XXXXX <description>
# TBD-EVIDENCE-OF: R-XXXXX-XXXXX <description>
# PARTIAL-EVIDENCE-OF: R-XXXXX-XXXXX <description>

Where:

• EVIDENCE-OF: Fully implemented and tested
• TBD-EVIDENCE-OF: Evidence identified but test not yet implemented
• PARTIAL-EVIDENCE-OF: Partially tested (some aspects covered)

Key Evidence Points Tested

Sources: All evidence comments across the six DDL test files

Test Execution Workflow

Standard Test Sequence

Each DDL test file follows this execution pattern:

1. Hash threshold declaration : hash-threshold 8 ([Line 1 in all files](https://github.com/jzombie/sqlite-sqllogictest-corpus/blob/8a29188b/Line 1 in all files))
2. Base table creation : Standard t1 table setup ([Lines 3-16](https://github.com/jzombie/sqlite-sqllogictest-corpus/blob/8a29188b/Lines 3-16))
3. Primary DDL operation : Execute the CREATE or DROP statement being tested
4. Duplicate/missing error validation : Verify appropriate errors for invalid operations
5. Cascading behavior validation : Confirm dependent objects are handled correctly
6. Cleanup : Remove all created objects

Error Validation Pattern

Tests verify both successful operations and expected failures:

Success validation :

statement ok
CREATE TRIGGER t1r1 UPDATE ON t1 BEGIN SELECT 1; END;

Duplicate creation error :

# already exists
statement error
CREATE TRIGGER t1r1 UPDATE ON t1 BEGIN SELECT 1; END;

Missing object error :

# never existed
statement error
DROP TRIGGER tXrX

This pattern appears consistently across all DDL test files, establishing a comprehensive validation matrix for each DDL command.

Sources: test/evidence/slt_lang_createtrigger.test:25-29 test/evidence/slt_lang_droptrigger.test:28-36

Related Test Categories

For comprehensive coverage of SQL testing:

• CREATE statement details : See CREATE Statement Tests for in-depth coverage of CREATE TRIGGER and CREATE VIEW syntax variations
• DROP statement details : See DROP Statement Tests for in-depth coverage of all DROP statement types
• DML operations : See Data Manipulation Language (DML) Tests-tests) for INSERT, UPDATE, DELETE, and REPLACE testing
• Query operations : See Query Operator Tests for SELECT statement validation
• Test format specification : See Test File Format Specification for complete DSL syntax reference