66 lines
1.9 KiB
MySQL
66 lines
1.9 KiB
MySQL
|
-- HYAPI Server Database Initialization Script
|
||
|
|
-- This script runs when PostgreSQL container starts for the first time
|
||
|
|
|
||
|
|
-- Create development database if it doesn't exist
|
||
|
|
-- Note: hyapi_dev is already created by POSTGRES_DB environment variable
|
||
|
|
|
||
|
|
-- Create test database for running tests
|
||
|
|
-- Note: Skip database creation in init script, handle in application if needed
|
||
|
|
|
||
|
|
-- Create production database (for reference)
|
||
|
|
-- CREATE DATABASE hyapi_prod;
|
||
|
|
|
||
|
|
-- Connect to development database
|
||
|
|
\c hyapi_dev;
|
||
|
|
|
||
|
|
-- Enable necessary extensions
|
||
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
||
|
|
|
||
|
|
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
|
||
|
|
|
||
|
|
CREATE EXTENSION IF NOT EXISTS "btree_gin";
|
||
|
|
|
||
|
|
-- Create schemas for better organization
|
||
|
|
CREATE SCHEMA IF NOT EXISTS public;
|
||
|
|
|
||
|
|
CREATE SCHEMA IF NOT EXISTS logs;
|
||
|
|
|
||
|
|
CREATE SCHEMA IF NOT EXISTS metrics;
|
||
|
|
|
||
|
|
-- Set search path
|
||
|
|
SET search_path TO public, logs, metrics;
|
||
|
|
|
||
|
|
-- Test database setup will be handled by application migrations
|
||
|
|
-- when needed, since we don't create it in this init script
|
||
|
|
|
||
|
|
-- Continue with development database setup
|
||
|
|
-- (already connected to hyapi_dev)
|
||
|
|
|
||
|
|
-- Create application-specific roles (optional)
|
||
|
|
-- CREATE ROLE hyapi_app WITH LOGIN PASSWORD 'app_password';
|
||
|
|
-- CREATE ROLE hyapi_readonly WITH LOGIN PASSWORD 'readonly_password';
|
||
|
|
|
||
|
|
-- Grant permissions
|
||
|
|
-- GRANT CONNECT ON DATABASE hyapi_dev TO hyapi_app;
|
||
|
|
-- GRANT USAGE ON SCHEMA public TO hyapi_app;
|
||
|
|
-- GRANT CREATE ON SCHEMA public TO hyapi_app;
|
||
|
|
|
||
|
|
-- Initial seed data can be added here
|
||
|
|
-- This will be replaced by proper migrations in the application
|
||
|
|
|
||
|
|
-- Log the initialization
|
||
|
|
-- Note: pg_stat_statements extension may not be available, skip this insert
|
||
|
|
|
||
|
|
-- Create a simple health check function
|
||
|
|
CREATE OR REPLACE FUNCTION health_check()
|
||
|
|
RETURNS json AS $$
|
||
|
|
BEGIN
|
||
|
|
RETURN json_build_object(
|
||
|
|
'status', 'healthy',
|
||
|
|
'database', current_database(),
|
||
|
|
'timestamp', now(),
|
||
|
|
'version', version()
|
||
|
|
);
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|