53 lines
1.9 KiB
SQL
53 lines
1.9 KiB
SQL
-- =============================================================================
|
|
-- DATABASE OPERATIONS TEST SUITE
|
|
-- =============================================================================
|
|
|
|
\echo '================================================================='
|
|
\echo 'Testing Basic Operations'
|
|
\echo '================================================================='
|
|
|
|
-- Set tenant context
|
|
SELECT set_tenant_context('10000000-0000-0000-0000-000000000001'::UUID);
|
|
|
|
-- Test 1: Query contracts (should see only ACME contracts)
|
|
\echo '\n[Test 1] Query contracts for tenant...'
|
|
SELECT COUNT(*) as contract_count FROM contracts;
|
|
-- Expected: 2
|
|
|
|
-- Test 2: Query with joins
|
|
\echo '\n[Test 2] Query contracts with vendor/consumer...'
|
|
SELECT
|
|
c.title,
|
|
v.name as vendor_name,
|
|
con.name as consumer_name
|
|
FROM contracts c
|
|
JOIN users v ON c.vendor_id = v.id
|
|
JOIN users con ON c.consumer_id = con.id;
|
|
-- Expected: 2 rows
|
|
|
|
-- Test 3: Full-text search
|
|
\echo '\n[Test 3] Search contracts...'
|
|
SELECT title
|
|
FROM contracts
|
|
WHERE to_tsvector('english', title || ' ' || COALESCE(description, ''))
|
|
@@ to_tsquery('english', 'website');
|
|
-- Expected: 1 row (Website Redesign)
|
|
|
|
-- Test 4: Test RLS isolation
|
|
\echo '\n[Test 4] Switch to different tenant...'
|
|
SELECT set_tenant_context('20000000-0000-0000-0000-000000000002'::UUID);
|
|
SELECT COUNT(*) FROM contracts;
|
|
-- Expected: 0 (different tenant)
|
|
|
|
-- Test 5: Trigger test (updated_at)
|
|
\echo '\n[Test 5] Test updated_at trigger...'
|
|
SELECT set_tenant_context('10000000-0000-0000-0000-000000000001'::UUID);
|
|
UPDATE contracts
|
|
SET description = description || ' (updated)'
|
|
WHERE id = 'c1000000-0000-0000-0000-000000000001'
|
|
RETURNING title, updated_at > created_at as was_updated;
|
|
-- Expected: was_updated = true
|
|
|
|
\echo '\n================================================================='
|
|
\echo 'All Tests Complete!'
|
|
\echo '=================================================================' |