A Rust application to showcase badge awards in the AT Protocol ecosystem.
smokesignal.events
1-- init-db.sql
2-- Database initialization script for PostgreSQL development and testing
3
4-- Set timezone to UTC for consistent testing
5SET timezone TO 'UTC';
6
7-- Create useful extensions for badge storage and testing
8CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
9CREATE EXTENSION IF NOT EXISTS "pgcrypto";
10
11-- Create additional test database for parallel testing
12CREATE DATABASE showcase_test_parallel;
13GRANT ALL PRIVILEGES ON DATABASE showcase_test_parallel TO showcase;
14
15-- Create database for integration tests
16CREATE DATABASE showcase_integration;
17GRANT ALL PRIVILEGES ON DATABASE showcase_integration TO showcase;
18
19-- Set up proper encoding and collation
20UPDATE pg_database SET datcollate = 'C', datctype = 'C' WHERE datname = 'showcase_test';
21UPDATE pg_database SET datcollate = 'C', datctype = 'C' WHERE datname = 'showcase_test_parallel';
22UPDATE pg_database SET datcollate = 'C', datctype = 'C' WHERE datname = 'showcase_integration';
23
24-- Connect to main test database to set up initial configuration
25\c showcase_test;
26
27-- Set up session parameters for optimal JSONB performance
28SET default_statistics_target = 100;
29SET random_page_cost = 1.1;
30SET effective_cache_size = '256MB';
31
32-- Create a function to reset test data (useful for integration tests)
33CREATE OR REPLACE FUNCTION reset_test_data()
34RETURNS void AS $$
35BEGIN
36 -- Drop all tables if they exist (for clean test runs)
37 DROP TABLE IF EXISTS awards CASCADE;
38 DROP TABLE IF EXISTS badges CASCADE;
39 DROP TABLE IF EXISTS identities CASCADE;
40
41 RAISE NOTICE 'Test data reset completed';
42END;
43$$ LANGUAGE plpgsql;
44
45-- Create a function to get database statistics (useful for debugging)
46CREATE OR REPLACE FUNCTION get_table_stats()
47RETURNS TABLE(
48 table_name text,
49 row_count bigint,
50 table_size text,
51 index_size text
52) AS $$
53BEGIN
54 RETURN QUERY
55 SELECT
56 schemaname||'.'||tablename as table_name,
57 n_tup_ins - n_tup_del as row_count,
58 pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as table_size,
59 pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) as index_size
60 FROM pg_stat_user_tables
61 WHERE schemaname = 'public'
62 ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
63END;
64$$ LANGUAGE plpgsql;
65
66-- Insert some test data for development (will be ignored if tables don't exist)
67DO $$
68BEGIN
69 -- This block will only execute if we're in a development environment
70 -- Production migrations should be handled by the Rust application
71 IF current_setting('server_version_num')::int >= 170000 THEN
72 RAISE NOTICE 'PostgreSQL 17+ detected - ready for JSONB optimizations';
73 END IF;
74
75 RAISE NOTICE 'Database initialization completed successfully';
76 RAISE NOTICE 'Available databases: showcase_test, showcase_test_parallel, showcase_integration';
77 RAISE NOTICE 'Available functions: reset_test_data(), get_table_stats()';
78END;
79$$;