test.sql

-- Test script for pg_bleve extension
-- This script tests index creation and search functionality

-- Connect to test database
\c test_db;

-- Create the extension
CREATE EXTENSION pg_bleve;

-- Create a test table
CREATE TABLE mock_items (
    id SERIAL PRIMARY KEY,
    description TEXT,
    category TEXT,
    rating INTEGER,
    in_stock BOOLEAN,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    metadata JSONB
);

-- Insert test data
INSERT INTO mock_items (description, category, rating, in_stock, metadata) VALUES
('Wireless Bluetooth Headphones', 'Electronics', 4, true, '{"brand": "Sony", "color": "black"}'),
('Running Shoes Nike Air Max', 'Sports', 5, true, '{"brand": "Nike", "size": "10"}'),
('Coffee Maker Programmable', 'Home', 3, false, '{"brand": "Breville", "capacity": "12 cups"}'),
('Smartphone iPhone 15', 'Electronics', 5, true, '{"brand": "Apple", "storage": "128GB"}'),
('Yoga Mat Non-slip', 'Sports', 4, true, '{"brand": "Lululemon", "thickness": "5mm"}'),
('Blender High Speed', 'Home', 4, true, '{"brand": "Vitamix", "power": "1400W"}'),
('Gaming Laptop RTX 4080', 'Electronics', 5, false, '{"brand": "ASUS", "ram": "32GB"}'),
('Tennis Racket Professional', 'Sports', 4, true, '{"brand": "Wilson", "weight": "300g"}'),
('Microwave Oven Countertop', 'Home', 3, true, '{"brand": "Panasonic", "capacity": "1.2 cu ft"}'),
('Tablet iPad Pro 12.9', 'Electronics', 5, true, '{"brand": "Apple", "storage": "256GB"}');

-- Create Bleve index (similar to ParadeDB syntax)
SELECT bleve_create_index('mock_items', '{"key_field": "id"}');

-- Index documents
SELECT bleve_index_document('mock_items', '1', '{"id": "1", "description": "Wireless Bluetooth Headphones", "category": "Electronics", "rating": 4, "in_stock": true, "metadata": {"brand": "Sony", "color": "black"}}');
SELECT bleve_index_document('mock_items', '2', '{"id": "2", "description": "Running Shoes Nike Air Max", "category": "Sports", "rating": 5, "in_stock": true, "metadata": {"brand": "Nike", "size": "10"}}');
SELECT bleve_index_document('mock_items', '3', '{"id": "3", "description": "Coffee Maker Programmable", "category": "Home", "rating": 3, "in_stock": false, "metadata": {"brand": "Breville", "capacity": "12 cups"}}');
SELECT bleve_index_document('mock_items', '4', '{"id": "4", "description": "Smartphone iPhone 15", "category": "Electronics", "rating": 5, "in_stock": true, "metadata": {"brand": "Apple", "storage": "128GB"}}');
SELECT bleve_index_document('mock_items', '5', '{"id": "5", "description": "Yoga Mat Non-slip", "category": "Sports", "rating": 4, "in_stock": true, "metadata": {"brand": "Lululemon", "thickness": "5mm"}}');

-- Test search functionality
-- Search for electronics
SELECT 'Search for electronics:' as test_case;
SELECT bleve_search('mock_items', 'category:Electronics');

-- Search for Apple products
SELECT 'Search for Apple products:' as test_case;
SELECT bleve_search('mock_items', 'metadata.brand:Apple');

-- Search for high-rated items
SELECT 'Search for high-rated items:' as test_case;
SELECT bleve_search('mock_items', 'rating:5');

-- Search for wireless products
SELECT 'Search for wireless products:' as test_case;
SELECT bleve_search('mock_items', 'description:wireless');

-- Test the @@@ operator (similar to ParadeDB)
SELECT 'Testing @@@ operator:' as test_case;
SELECT 'mock_items' @@@ 'category:Electronics' as has_electronics;
SELECT 'mock_items' @@@ 'metadata.brand:Apple' as has_apple_products;
SELECT 'mock_items' @@@ 'description:wireless' as has_wireless_products;

-- Test complex queries
SELECT 'Complex search query:' as test_case;
SELECT bleve_search('mock_items', 'category:Electronics AND rating:5');

-- Show all indexed documents
SELECT 'All indexed documents:' as test_case;
SELECT bleve_search('mock_items', '*');

-- Performance test
SELECT 'Performance test - searching all documents:' as test_case;
\timing on
SELECT bleve_search('mock_items', 'category:Electronics');
\timing off