Skip to content

Virtual Tables

Jump to bottom
Temp edited this page Sep 23, 2025 · 1 revision

Virtual Tables

Last Updated: September 23, 2025 1:48 PM EST

The SQLite MCP Server provides comprehensive virtual table management capabilities, supporting multiple virtual table types for specialized data access patterns and performance optimization.

🔧 Available Virtual Table Tools

Tool Description
create_rtree_table Create R-Tree virtual tables for spatial indexing
create_csv_table Create virtual tables to access CSV files
create_series_table Create generate_series virtual tables for sequences
list_virtual_tables List all virtual tables in the database
drop_virtual_table Drop virtual tables with confirmation
virtual_table_info Get detailed information about virtual tables

🌳 R-Tree Spatial Indexing

Create Spatial Indexes

// Create a 2D spatial index for geographic data
create_rtree_table({
  "table_name": "locations_spatial",
  "dimensions": 2,
  "coordinate_type": "float"
})

// Create a 3D spatial index for volumetric data
create_rtree_table({
  "table_name": "objects_3d",
  "dimensions": 3,
  "coordinate_type": "float"
})

Features:

  • Configurable dimensions (2D, 3D, multi-dimensional)
  • Float or integer coordinate types
  • Automatic column generation (id, min0, max0, min1, max1, etc.)
  • Optimized for range queries and spatial searches

Geographic Data Indexing

// Create spatial index for geographic locations
create_rtree_table({
  "table_name": "geo_locations",
  "dimensions": 2,
  "coordinate_type": "float",
  "columns": {
    "longitude": {"min": "min_lon", "max": "max_lon"},
    "latitude": {"min": "min_lat", "max": "max_lat"}
  }
})

// Insert geographic data
write_query({
  "query": "INSERT INTO geo_locations (id, min_lon, max_lon, min_lat, max_lat) VALUES (?, ?, ?, ?, ?)",
  "params": [1, -122.5, -122.4, 37.7, 37.8]  // San Francisco area
})

// Query nearby locations
read_query({
  "query": `
    SELECT id FROM geo_locations 
    WHERE min_lon <= ? AND max_lon >= ? 
    AND min_lat <= ? AND max_lat >= ?
  `,
  "params": [-122.45, -122.45, 37.75, 37.75]
})

3D Object Spatial Queries

// Create 3D spatial index for objects
create_rtree_table({
  "table_name": "spatial_objects",
  "dimensions": 3,
  "coordinate_type": "float"
})

// Insert 3D bounding boxes
write_query({
  "query": "INSERT INTO spatial_objects (id, min0, max0, min1, max1, min2, max2) VALUES (?, ?, ?, ?, ?, ?, ?)",
  "params": [1, 0.0, 10.0, 0.0, 10.0, 0.0, 5.0]  // 3D box
})

// Find intersecting objects
read_query({
  "query": `
    SELECT id FROM spatial_objects 
    WHERE min0 <= 5.0 AND max0 >= 5.0 
    AND min1 <= 5.0 AND max1 >= 5.0
    AND min2 <= 2.0 AND max2 >= 2.0
  `
})

📄 CSV File Access

Direct CSV File Querying

// Create a virtual table for a CSV file with headers
create_csv_table({
  "table_name": "sales_data",
  "csv_file_path": "./data/sales.csv",
  "has_header": true,
  "delimiter": ","
})

// Create a virtual table for a TSV file without headers
create_csv_table({
  "table_name": "log_data",
  "csv_file_path": "./logs/server.tsv",
  "has_header": false,
  "delimiter": "\t",
  "column_names": ["timestamp", "level", "message", "source"]
})

Features:

  • Direct CSV file access without importing
  • Configurable delimiters (comma, tab, pipe, etc.)
  • Header row detection and handling
  • Automatic fallback to temporary table if CSV extension unavailable

Advanced CSV Configuration

// Complex CSV with custom settings
create_csv_table({
  "table_name": "financial_data",
  "csv_file_path": "./data/financial_report.csv",
  "has_header": true,
  "delimiter": ",",
  "quote_char": '"',
  "escape_char": '\\',
  "skip_lines": 2,  // Skip metadata lines
  "encoding": "utf-8",
  "null_values": ["", "NULL", "N/A"]
})

// Query CSV data directly
read_query({
  "query": "SELECT region, SUM(revenue) FROM financial_data WHERE year = 2024 GROUP BY region"
})

Multiple CSV Files

// Create virtual tables for multiple related CSV files
const csvFiles = [
  { name: "q1_sales", path: "./data/q1_sales.csv" },
  { name: "q2_sales", path: "./data/q2_sales.csv" },
  { name: "q3_sales", path: "./data/q3_sales.csv" },
  { name: "q4_sales", path: "./data/q4_sales.csv" }
];

csvFiles.forEach(file => {
  create_csv_table({
    "table_name": file.name,
    "csv_file_path": file.path,
    "has_header": true,
    "delimiter": ","
  });
});

// Union query across all quarters
read_query({
  "query": `
    SELECT 'Q1' as quarter, * FROM q1_sales
    UNION ALL
    SELECT 'Q2' as quarter, * FROM q2_sales
    UNION ALL
    SELECT 'Q3' as quarter, * FROM q3_sales
    UNION ALL
    SELECT 'Q4' as quarter, * FROM q4_sales
  `
})

🔢 Series Generation

Numeric Sequences

// Create a simple number series
create_series_table({
  "table_name": "numbers_1_to_100",
  "start_value": 1,
  "end_value": 100,
  "step": 1
})

// Create a series with custom step
create_series_table({
  "table_name": "even_numbers",
  "start_value": 2,
  "end_value": 1000,
  "step": 2
})

Features:

  • Configurable start, end, and step values
  • Automatic fallback to regular table with recursive CTE
  • Perfect for generating test data and sequences
  • Memory-efficient virtual table implementation

Date Series Generation

// Generate date sequences using series table
create_series_table({
  "table_name": "day_sequence",
  "start_value": 0,
  "end_value": 365,
  "step": 1
})

// Query with date calculations
read_query({
  "query": `
    SELECT 
      value as day_offset,
      date('2024-01-01', '+' || value || ' days') as date,
      strftime('%w', date('2024-01-01', '+' || value || ' days')) as day_of_week
    FROM day_sequence 
    WHERE value <= 30
  `
})

Test Data Generation

// Create series for test data generation
create_series_table({
  "table_name": "test_ids",
  "start_value": 1,
  "end_value": 10000,
  "step": 1
})

// Generate test users
write_query({
  "query": `
    INSERT INTO users (id, username, email, created_at)
    SELECT 
      value,
      'user_' || value,
      'user_' || value || '@example.com',
      datetime('2024-01-01', '+' || (value % 365) || ' days')
    FROM test_ids
    WHERE value <= 1000
  `
})

🔍 Virtual Table Management

List All Virtual Tables

list_virtual_tables()

Returns:

  • Virtual table names and SQL definitions
  • Automatic type detection (rtree, fts, csv, generate_series)
  • Complete virtual table inventory
  • Structured JSON output with metadata

Get Virtual Table Information

virtual_table_info({
  "table_name": "locations_spatial"
})

Features:

  • Complete column information with types and constraints
  • Virtual table type identification
  • SQL definition display
  • Column count and metadata

Safe Virtual Table Removal

// Safe drop with confirmation
drop_virtual_table({
  "table_name": "old_spatial_index",
  "confirm": true
})

Safety Features:

  • Mandatory confirmation flag to prevent accidents
  • Virtual table verification before deletion
  • Detailed status reporting
  • Error handling for non-existent tables

💡 Real-World Use Cases

GIS and Mapping Applications

// 1. Create spatial index for points of interest
create_rtree_table({
  "table_name": "poi_spatial",
  "dimensions": 2,
  "coordinate_type": "float"
})

// 2. Insert POI data
write_query({
  "query": `
    INSERT INTO poi_spatial (id, min_lon, max_lon, min_lat, max_lat)
    SELECT 
      id, 
      longitude, longitude,  -- Point data uses same value for min/max
      latitude, latitude
    FROM points_of_interest
  `
})

// 3. Find POIs within bounding box
read_query({
  "query": `
    SELECT poi.name, poi.category, poi.longitude, poi.latitude
    FROM poi_spatial sp
    JOIN points_of_interest poi ON sp.id = poi.id
    WHERE sp.min_lon BETWEEN -122.5 AND -122.4
    AND sp.min_lat BETWEEN 37.7 AND 37.8
  `
})

Log File Analysis

// 1. Create virtual table for log files
create_csv_table({
  "table_name": "server_logs",
  "csv_file_path": "./logs/server.log",
  "has_header": false,
  "delimiter": " ",
  "column_names": ["timestamp", "level", "component", "message"]
})

// 2. Analyze error patterns
read_query({
  "query": `
    SELECT 
      component,
      COUNT(*) as error_count,
      MIN(timestamp) as first_error,
      MAX(timestamp) as last_error
    FROM server_logs 
    WHERE level = 'ERROR'
    GROUP BY component
    ORDER BY error_count DESC
  `
})

// 3. Time-based analysis
read_query({
  "query": `
    SELECT 
      strftime('%H', timestamp) as hour,
      level,
      COUNT(*) as count
    FROM server_logs
    GROUP BY hour, level
    ORDER BY hour, level
  `
})

Data Import and ETL

// 1. Create virtual tables for multiple data sources
const dataSources = [
  { table: "customers_csv", file: "./import/customers.csv" },
  { table: "orders_csv", file: "./import/orders.csv" },
  { table: "products_csv", file: "./import/products.csv" }
];

dataSources.forEach(source => {
  create_csv_table({
    "table_name": source.table,
    "csv_file_path": source.file,
    "has_header": true,
    "delimiter": ","
  });
});

// 2. Data validation and cleaning
read_query({
  "query": `
    SELECT 
      'customers' as table_name,
      COUNT(*) as total_rows,
      COUNT(DISTINCT customer_id) as unique_ids,
      COUNT(CASE WHEN email LIKE '%@%' THEN 1 END) as valid_emails
    FROM customers_csv
    
    UNION ALL
    
    SELECT 
      'orders' as table_name,
      COUNT(*) as total_rows,
      COUNT(DISTINCT order_id) as unique_ids,
      COUNT(CASE WHEN amount > 0 THEN 1 END) as valid_amounts
    FROM orders_csv
  `
})

// 3. Import clean data to permanent tables
write_query({
  "query": `
    INSERT INTO customers (customer_id, name, email, created_date)
    SELECT customer_id, name, email, date(created_date)
    FROM customers_csv
    WHERE email LIKE '%@%' AND customer_id IS NOT NULL
  `
})

⚡ Performance Benefits

R-Tree Tables

  • O(log n) spatial queries vs O(n) table scans
  • Efficient range queries for geographic data
  • Multi-dimensional indexing for complex spatial relationships

CSV Tables

  • Direct file access without storage duplication
  • No import overhead for read-only data
  • Dynamic file access - changes to CSV files reflected immediately

Series Tables

  • Memory-efficient sequence generation
  • No storage overhead for computed sequences
  • Perfect for joins with generated data

Comprehensive Management

  • Centralized lifecycle control for all virtual table types
  • Consistent API across different virtual table implementations
  • Integrated with core database tools for seamless workflows

🎯 Best Practices

1. Choose the Right Virtual Table Type

// Use R-Tree for spatial/geometric data
create_rtree_table({
  "table_name": "geographic_regions",
  "dimensions": 2,
  "coordinate_type": "float"
})

// Use CSV tables for external data files
create_csv_table({
  "table_name": "external_data",
  "csv_file_path": "./data/source.csv",
  "has_header": true
})

// Use series tables for generated sequences
create_series_table({
  "table_name": "date_range",
  "start_value": 1,
  "end_value": 365,
  "step": 1
})

2. Optimize Spatial Queries

// Create appropriate spatial indexes
create_rtree_table({
  "table_name": "spatial_index",
  "dimensions": 2,
  "coordinate_type": "float"
})

// Use bounding box queries efficiently
read_query({
  "query": `
    SELECT id FROM spatial_index
    WHERE min0 <= ? AND max0 >= ?
    AND min1 <= ? AND max1 >= ?
    LIMIT 100
  `,
  "params": [x_max, x_min, y_max, y_min]
})

3. Handle CSV Files Properly

// Validate CSV structure before creating virtual table
create_csv_table({
  "table_name": "validated_csv",
  "csv_file_path": "./data/source.csv",
  "has_header": true,
  "validate_structure": true,
  "skip_invalid_rows": true
})

// Use appropriate data types in queries
read_query({
  "query": `
    SELECT 
      CAST(numeric_column AS REAL) as number,
      date(date_column) as parsed_date
    FROM validated_csv
    WHERE numeric_column IS NOT NULL
  `
})

4. Regular Virtual Table Maintenance

// List and inspect virtual tables regularly
const virtualTables = await list_virtual_tables();

virtualTables.forEach(async (table) => {
  const info = await virtual_table_info({
    "table_name": table.name
  });
  
  console.log(`Table: ${table.name}, Type: ${info.type}, Columns: ${info.column_count}`);
});

// Clean up unused virtual tables
drop_virtual_table({
  "table_name": "temporary_analysis",
  "confirm": true
})

📚 Related Pages

🌳 Virtual Table Tip: Virtual tables provide powerful data access patterns without the overhead of importing data. Use them to access external files, generate sequences, and create spatial indexes for optimal performance.

Clone this wiki locally