dump_proclaim.py

dump_proclaim.py

import sqlite3
from pathlib import Path
from collections import defaultdict
import json

# NOTE: This will need to be changed to your data path
proclaim_data = Path('~/Library/Application Support/Proclaim/Data/5sos6hqf.xyd/').expanduser()
presentations_db = proclaim_data / 'PresentationManager' / 'PresentationManager.db'

conn = sqlite3.connect(presentations_db)
service_items = conn.execute(
    '''
    SELECT
        Presentations.DateGiven, ServiceItems.Title, ServiceItems.Content
        FROM ServiceItems
        LEFT JOIN Presentations ON Presentations.PresentationId == ServiceItems.PresentationId
        WHERE ServiceItemKind == "SongLyrics"
        AND DateGiven > "2024-01-01"
    ;''')

dates_offered = defaultdict(list) # title to list of dates
latest_content = {}
for service_item in service_items:
    date, title, content = service_item
    dates_offered[title].append(date)
    if title not in latest_content or date > latest_content[title][0]:
        content_json = json.loads(content)
        latest_content[title] = (date, content_json)

for title, dates in dates_offered.items():
    dates.sort()
    print(f'{title}: {dates[-1]}')

conn.close()

date, example_content = latest_content["Wonderful Merciful Savior"]
print(f'Example content for "Wonderful Merciful Savior" from {date}:')
print(example_content.keys())

# _richtextfield:Lyrics <Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I love You Lord" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="Oh Your mercy never fails me" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my days" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I’ve been held in Your hands" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="From the moment that I wake up" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="Until I lay my head" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I will sing of the goodness of God" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my life You have been faithful" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my life You have been so, so good" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="With every breath that I am able" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I will sing of the goodness of God" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I love Your voice" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="You have led me through the fire" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="In darkest night" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="You are close like no other" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I’ve known You as a father" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I’ve known You as a friend" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I have lived in the goodness of God" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my life You have been faithful" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my life You have been so, so good" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="With every breath that I am able" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I will sing of the goodness of God" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="Your goodness is running after" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="It’s running after me" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="Your goodness is running after" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="It’s running after me" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="With my life laid down" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I’m surrendered now" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I give You everything" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="Your goodness is running after" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="It’s running after me" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="Your goodness is running after" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="It’s running after me" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="Your goodness is running after" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="It’s running after me" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="With my life laid down" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I’m surrendered now" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I give You everything" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="Your goodness is running after" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="It’s running after me" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my life You have been faithful" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my life You have been so, so good" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="With every breath that I am able" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I will sing of the goodness of God" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my life You have been faithful" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my life You have been so, so good" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="With every breath that I am able" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I will sing of the goodness of God" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I will sing of the goodness of God" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" />

# parse that XML: each Paragraph Run Text is a line

from lxml import etree

def decode_richtextXML(xml):
    result = ''

    root = etree.fromstring('<Song>' + xml + '</Song>')
    for paragraph in root:
        runs = paragraph.findall('Run')
        for run in runs:
            result += run.attrib['Text'] + ' '
        result += '\n'
    return result


output_path = Path('~/Desktop/all_lyrics').expanduser()
output_path.mkdir(exist_ok=True)
all_songs_joined = []
for title, (date, content) in latest_content.items():
    if '_richtextfield:Lyrics' not in content:
        print(f"Skipping {title}")
        continue
    title_clean = title.replace('/', ' - ').replace('?', '').replace(':', ' -').replace('!', '')
    # replace strings of spaces with single space
    title_clean = ' '.join(title_clean.split()).strip()
    lyrics_path = output_path / f'{title_clean}.md'
    with lyrics_path.open('w') as f:
        f.write(f"# {title}\n\n")
        f.write(f"Last done: {date}\n\n")
        f.write(decode_richtextXML(content['_richtextfield:Lyrics']))

        all_songs_joined.append(f"# {title}\n\n")
        all_songs_joined.append(f"Last done: {date}\n\n")
        all_songs_joined.append(decode_richtextXML(content['_richtextfield:Lyrics']))

        if False:
            for key, value in content.items():
                if key.startswith("slideOutput") and key.endswith("RichTextXml"):
                    f.write(f"\n\n\n\n# Translation\n\n")
                    f.write(decode_richtextXML(value))

    if False:
        # Convert to docx using pandoc
        docx_path = lyrics_path.with_suffix('.docx')
        import subprocess
        subprocess.run(['pandoc', '-o', docx_path, lyrics_path])

with (output_path / 'all_lyrics.md').open('w') as f:
    f.write('\n'.join(all_songs_joined))