da06dae89d
- Remove entire utils/db_operations.rs file and all *Operations patterns - Comment out imports to dead operations - This breaks compilation temporarily but eliminates the maintenance nightmare - Next: rewrite db:: modules to use direct SQL instead of operations - Goal: Clean Handler → Service → Database pattern only
114 lines
4.2 KiB
SQL
114 lines
4.2 KiB
SQL
-- SQL script to remove manually added hymn titles from bulletins
|
|
-- This will clean up patterns like "#319 - Amazing Grace" back to just "#319"
|
|
-- Run these in order and test on a backup first!
|
|
|
|
-- STEP 1: Preview what will be changed (RUN THIS FIRST)
|
|
-- This shows what changes would be made without actually making them
|
|
SELECT
|
|
id,
|
|
title,
|
|
date,
|
|
'divine_worship' as field_name,
|
|
divine_worship as original_content,
|
|
-- Clean up various hymn title patterns
|
|
REGEXP_REPLACE(
|
|
REGEXP_REPLACE(
|
|
REGEXP_REPLACE(
|
|
REGEXP_REPLACE(divine_worship,
|
|
'#([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', -- Pattern: #123 - Title
|
|
'#\1', 'g'),
|
|
'Hymn\s+([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', -- Pattern: Hymn 123 - Title
|
|
'Hymn \1', 'g'),
|
|
'No\.\s*([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', -- Pattern: No. 123 - Title
|
|
'No. \1', 'g'),
|
|
'Number\s+([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', -- Pattern: Number 123 - Title
|
|
'Number \1', 'g'
|
|
) as cleaned_content
|
|
FROM bulletins
|
|
WHERE divine_worship IS NOT NULL
|
|
AND (divine_worship LIKE '%#[0-9]%-%' OR
|
|
divine_worship LIKE '%Hymn [0-9]%-%' OR
|
|
divine_worship LIKE '%No. [0-9]%-%' OR
|
|
divine_worship LIKE '%Number [0-9]%-%')
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
id,
|
|
title,
|
|
date,
|
|
'sabbath_school' as field_name,
|
|
sabbath_school as original_content,
|
|
-- Clean up various hymn title patterns
|
|
REGEXP_REPLACE(
|
|
REGEXP_REPLACE(
|
|
REGEXP_REPLACE(
|
|
REGEXP_REPLACE(sabbath_school,
|
|
'#([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', -- Pattern: #123 - Title
|
|
'#\1', 'g'),
|
|
'Hymn\s+([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', -- Pattern: Hymn 123 - Title
|
|
'Hymn \1', 'g'),
|
|
'No\.\s*([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', -- Pattern: No. 123 - Title
|
|
'No. \1', 'g'),
|
|
'Number\s+([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', -- Pattern: Number 123 - Title
|
|
'Number \1', 'g'
|
|
) as cleaned_content
|
|
FROM bulletins
|
|
WHERE sabbath_school IS NOT NULL
|
|
AND (sabbath_school LIKE '%#[0-9]%-%' OR
|
|
sabbath_school LIKE '%Hymn [0-9]%-%' OR
|
|
sabbath_school LIKE '%No. [0-9]%-%' OR
|
|
sabbath_school LIKE '%Number [0-9]%-%')
|
|
ORDER BY date DESC;
|
|
|
|
-- STEP 2: BACKUP YOUR DATA FIRST!
|
|
-- CREATE TABLE bulletins_backup AS SELECT * FROM bulletins;
|
|
|
|
-- STEP 3: Actually clean up divine_worship field (ONLY RUN AFTER TESTING)
|
|
/*
|
|
UPDATE bulletins
|
|
SET divine_worship = REGEXP_REPLACE(
|
|
REGEXP_REPLACE(
|
|
REGEXP_REPLACE(
|
|
REGEXP_REPLACE(divine_worship,
|
|
'#([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)',
|
|
'#\1', 'g'),
|
|
'Hymn\s+([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)',
|
|
'Hymn \1', 'g'),
|
|
'No\.\s*([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)',
|
|
'No. \1', 'g'),
|
|
'Number\s+([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)',
|
|
'Number \1', 'g'
|
|
)
|
|
WHERE divine_worship IS NOT NULL
|
|
AND (divine_worship LIKE '%#[0-9]%-%' OR
|
|
divine_worship LIKE '%Hymn [0-9]%-%' OR
|
|
divine_worship LIKE '%No. [0-9]%-%' OR
|
|
divine_worship LIKE '%Number [0-9]%-%');
|
|
*/
|
|
|
|
-- STEP 4: Actually clean up sabbath_school field (ONLY RUN AFTER TESTING)
|
|
/*
|
|
UPDATE bulletins
|
|
SET sabbath_school = REGEXP_REPLACE(
|
|
REGEXP_REPLACE(
|
|
REGEXP_REPLACE(
|
|
REGEXP_REPLACE(sabbath_school,
|
|
'#([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)',
|
|
'#\1', 'g'),
|
|
'Hymn\s+([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)',
|
|
'Hymn \1', 'g'),
|
|
'No\.\s*([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)',
|
|
'No. \1', 'g'),
|
|
'Number\s+([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)',
|
|
'Number \1', 'g'
|
|
)
|
|
WHERE sabbath_school IS NOT NULL
|
|
AND (sabbath_school LIKE '%#[0-9]%-%' OR
|
|
sabbath_school LIKE '%Hymn [0-9]%-%' OR
|
|
sabbath_school LIKE '%No. [0-9]%-%' OR
|
|
sabbath_school LIKE '%Number [0-9]%-%');
|
|
*/
|
|
|
|
-- STEP 5: Verify the cleanup worked
|
|
-- SELECT COUNT(*) FROM bulletins WHERE divine_worship LIKE '%#[0-9]%-%' OR sabbath_school LIKE '%#[0-9]%-%'; |