5793e12df9
- Add complete hymnal API with search, themes, and responsive readings - Implement hymn title lookup for bulletins (#319 → #319 - Hymn Title) - Add Bible book abbreviation support (Matt → Matthew, etc.) - Enhance scripture processing to handle verse ranges (Matt 1:21-23) - Add hymnal database schema with SDA 1985 and 1941 hymnals support - Implement advanced hymnal search with fuzzy matching and themes - Update bulletin processing to auto-populate hymn titles from database
90 lines
4.1 KiB
SQL
90 lines
4.1 KiB
SQL
-- Create Hymnal Tables Migration
|
|
-- This migration adds support for SDA Hymnals with proper separation between versions
|
|
-- The 1941 and 1985 hymnals are completely different books with different content
|
|
|
|
-- Hymnals table to store different hymnal versions
|
|
CREATE TABLE hymnals (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(100) NOT NULL,
|
|
code VARCHAR(50) NOT NULL UNIQUE,
|
|
description TEXT,
|
|
year INTEGER,
|
|
language VARCHAR(10) DEFAULT 'en',
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Hymns table for individual hymns
|
|
-- Each hymnal has its own set of hymns, even if numbers overlap
|
|
CREATE TABLE hymns (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
hymnal_id UUID NOT NULL REFERENCES hymnals(id) ON DELETE CASCADE,
|
|
number INTEGER NOT NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
content TEXT NOT NULL, -- Preserves original verse formatting from each hymnal
|
|
is_favorite BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(hymnal_id, number)
|
|
);
|
|
|
|
-- Thematic lists for organizing hymns by themes
|
|
-- Each hymnal has its own thematic organization
|
|
CREATE TABLE thematic_lists (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
hymnal_id UUID NOT NULL REFERENCES hymnals(id) ON DELETE CASCADE,
|
|
name VARCHAR(100) NOT NULL,
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Thematic ambits (ranges of hymns within themes)
|
|
-- Define which hymn numbers belong to each theme
|
|
CREATE TABLE thematic_ambits (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
thematic_list_id UUID NOT NULL REFERENCES thematic_lists(id) ON DELETE CASCADE,
|
|
name VARCHAR(100) NOT NULL,
|
|
start_number INTEGER NOT NULL,
|
|
end_number INTEGER NOT NULL,
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Responsive readings (shared across hymnals)
|
|
CREATE TABLE responsive_readings (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
number INTEGER NOT NULL UNIQUE,
|
|
title VARCHAR(255) NOT NULL,
|
|
content TEXT NOT NULL,
|
|
is_favorite BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Create indexes for performance
|
|
CREATE INDEX idx_hymns_hymnal_id ON hymns(hymnal_id);
|
|
CREATE INDEX idx_hymns_number ON hymns(number);
|
|
CREATE INDEX idx_hymns_title ON hymns(title);
|
|
CREATE INDEX idx_hymns_content_search ON hymns USING gin(to_tsvector('english', content));
|
|
CREATE INDEX idx_hymns_favorite ON hymns(is_favorite) WHERE is_favorite = true;
|
|
CREATE INDEX idx_thematic_lists_hymnal_id ON thematic_lists(hymnal_id);
|
|
CREATE INDEX idx_thematic_lists_order ON thematic_lists(hymnal_id, sort_order);
|
|
CREATE INDEX idx_thematic_ambits_list_id ON thematic_ambits(thematic_list_id);
|
|
CREATE INDEX idx_thematic_ambits_range ON thematic_ambits(start_number, end_number);
|
|
CREATE INDEX idx_responsive_readings_number ON responsive_readings(number);
|
|
|
|
-- Insert the two hymnal versions
|
|
INSERT INTO hymnals (name, code, description, year, language) VALUES
|
|
('Seventh-day Adventist Hymnal', 'sda-1985', 'The current SDA Church Hymnal published in 1985 (en-newVersion)', 1985, 'en'),
|
|
('Church Hymnal', 'sda-1941', 'The older SDA Church Hymnal published in 1941 (en-oldVersion)', 1941, 'en');
|
|
|
|
-- Add helpful comments
|
|
COMMENT ON TABLE hymnals IS 'Different versions of hymnals (1941 vs 1985 have completely different content)';
|
|
COMMENT ON TABLE hymns IS 'Individual hymns - same numbers can have different content across hymnals';
|
|
COMMENT ON TABLE thematic_lists IS 'Categories/themes for organizing hymns (e.g., Worship, Trinity)';
|
|
COMMENT ON TABLE thematic_ambits IS 'Ranges of hymn numbers that belong to each theme';
|
|
COMMENT ON TABLE responsive_readings IS 'Responsive readings for church services';
|
|
COMMENT ON COLUMN hymns.content IS 'Original verse formatting preserved (1985 uses numbered verses, 1941 does not)'; |