lds-scriptures postgresql update snapshot

I have not worked on my LDS Scriptures project in a very long time.  In fact, annual realeases are becoming an embarrassing reality.  However, I’ve been itching to update the project for a while now, since I’ve been learning quite a lot more about databases since my last release.

I had an interesting idea for doing some custom RSS feeds of pulling down scriptures (which I’ll get into at a later time), but I quickly realized the current db schema wasn’t quite up to par.  I started poking around the postgres one, and it was much easier than I imagined to fix it up quite a bit.  I dropped all the tables and recreated them, this time with primary and foreign keys, and I got rid of some columns that were completely unnecessary.  I renamed some too, along with the other general cleanup.

I tarballed the snapshot once I was finished.  This isn’t what I’m going to release as the final 3.0 version, since I still need to add things like views and more indexes, but this is the direction I’m going to be heading.  You can download the snapshot tarball here.  Feedback is welcome, as always. :)

About these ads

1 Comment

Filed under Computers, Religion

One response to “lds-scriptures postgresql update snapshot

  1. Speaking as a programmer, those table names are hideous. I’d go nuts writing a program that accessed those tables and even some of the fields:

    lds_scriptures_verses.verse_scripture

    would be a pain in any language. Even if I wrote a wrapper for it (which I would) I would still have to keep in mind what I had aliased to what.

    Might I suggest the following?

    lds_scriptures_volumes => volumes
    lds_scriptures_books => books
    lds_scriptures_verses => verses

    lds_scriptures_verses.verse_scripture => verses.vtext

    (I’d rather just do text, but that’s a reserved word.)

    Also, the following view in its various forms (long title lookup, short title lookup, lds_org lookup, etc.):

    CREATE VIEW verse_lookup AS
    SELECT vol.title, book.title, v.chapter, v.verse, v.verse_scripture
    FROM lds_scriptures_volumes vol, lds_scriptures_books book, lds_scriptures_verses v
    WHERE v.book = book.id
    AND book.volume = vol.id

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s