Software Database

[mts7]

So here it is. I'm wondering what databases are used by the worship song presenter software that is on the market. I know MediaShout uses Microsoft Access, but what does everything else use? I would love to get a program that doesn't use Access, but Oracle, or some other SQL database.

For example, what databases do Easy Worship and Song Screen Liquid use?

--Mike

[waynehoskins]

I don't know about the others, but Lyricue uses MySQL or SQLlite (either one of them). Opensong uses individual XML files .. we converted from Opensong to Lyricue at Christmas, and I wrote a little PHP command-line program to do the conversion.

[[Director] Joseph B]

Why does it matter what database the songs are stored in? They are usually locked anyway so you can't tweak them (assuming you had the knowledge to do so - I certainly don't) so I'm just kind of curious really as to why the database would be important.

[waynehoskins]

SQL database systems are really better than a front-end to Access .. Access is kinda klunky when it comes to serious database work. I'm gonna guess MySQL/Postgres/Oracle/SQLlite are faster and more standards-compliant than Access, so it's a valid question.

And in my case, doing a system conversion took all of probably two or three hours to write the program, and about two seconds to actually execute the conversion of some 200-plus songs. The slow part was echoing progress indications out to the console; it probably would have run in half the time if I didn't do that.

[shayward]

Why does it matter what database the songs are stored in? They are usually locked anyway so you can't tweak them (assuming you had the knowledge to do so - I certainly don't) so I'm just kind of curious really as to why the database would be important.

Mike's a programmer and you know programmers ...

Easy Worship uses Paradox. I hacked it a few years back to fire CCLI text files into the database. Worked well.

As absolutely cool as I think it would be, you're not liable to see one based on SQL Server because it's overkill and it would be a support nightmare.

"Okay, joe user, can you check and see if the SQL Server service is running..."

I'm not a big MS Access fan ... way too many painful years. I'm soooo much happier developing in SQL Server.

Truth be told, if I were writing a worship program today I'd use plain old XML Files.

[mts7]

I am curious just to be curious, for the most part. I've seen programs that are just covers for Access, but I'm looking to see what else is out there. Access databases are much larger than necessary because they have a lot of M$ code that makes it Access.

Another reason I'm curious is because the technical director, my supervisor, is concerned about the database. I think he has in his mind that if I have songs in an mdb, that same mdb will work just fine in another program that uses mdb files. He was also saying things like we should have songs in File Maker Pro because that's how the previous worship pastor had them.

I personally love MySQL. It is so simple and effective. If I was to write song presentation software, I think I'd use MySQL, SQLite, or something of that sort.

[shayward]

I am curious just to be curious, for the most part.

Awesome!

I think he has in his mind that if I have songs in an mdb, that same mdb will work just fine in another program that uses mdb files.

Just ask him why he can't string his guitar with Cello strings... or stick a Motorola 68000 CPU in his Intel motherboard... or stick a Hemi in a Pinto. Things that are the same in concept are generally not the same in practical compatibility.

He was also saying things like we should have songs in File Maker Pro because that's how the previous worship pastor had them.

Interesting... but are you getting any value for it? That needs to be the primary question in any technical field... does my ROI exceed the investment itself? Or is it just doing something for the sake of doing something?

Access databases are much larger than necessary because they have a lot of M$ code that makes it Access.

Access has it's merrits... it makes building a rudimentry database and data entry forms very easy. It's an okay end-user tool and belongs nicely in the MS Office family.

MySQL has its pros and cons. I use Drupal (which uses MySQL) and I'm considering MySQL for some future projects because of it's price tag.

Where it initially fell down for me was the lack of Views and Stored Procs (two vital features) but that was a few years ago and I'm sure that it now supports them.

Why aren't we seeing new worship software written in FoxPro? Or dBase? or db400?

[[Admin] osborn4]

SongShow Plus stores the songs as seperate files in the Songs directory and then has an index to point to them.

But it has a module that allows you to create your own MDB file (and we all know what that means ) and an XML import module, so that if you have enough geekiness to format up another song database into XML, you can import it into SSP's database.

[Live4the1]

Shayward, I know this is from a few years back but I see that you are familiar with the EW database format. I am looking for a way to use the database to create a list of all of the songs that are in the database and then use them to create an electronic master list in another database software or excel spreadsheet. How can I get the info out of the EW database in a usable format for me to work with? Also, what you recommend for a free database software that would be easy for people to use?

[VideoPsalm]

Databases were invented because, at that time, computers had tiny memory.
People also needed to work at the same time on the same data, which only a database system would allow securely.

However, todays computers can store several hundred of Bibles right in their memory. The minimum memory amount one can buy a computer with today, is around 4 Gigabytes. One Bible contains around 2 million characters. Usually, a Bible structure stored into the computer memory occupies more than its amount of characters, maybe up to around 20 Megabytes. 100 Bibles would then occupy 2 Gigabytes. I can compare this to the Asus Windows 8 laptop I use right now, which has 16 Gigabytes of memory.

Songbooks are much smaller than one Bible. You can very very very easily have tens of thousands of songs in your memory, letting your program have instant access to all of it (to the contrary of a database, which would access songs sequentially, eq. slower).

Using a database like MS Access, MySQL, Progress, MSSQL, Oracle, etc. to store songs or Bibles could be good for concurrency = several people working on editing songs in the same songbook at the same time, however this is not used most of the time, if ever, by worship software.
The EasyWorship team is currently working on such a software, but I don't know many churches who really need this function.
The "work in parallel" on the same song or songbook is the only positive point of using a database for songs and Bibles nowadays. But there are big drawbacks.

VideoPsalm uses plain text files, one file per songbook or per Bible, in a json dialect format. Therefore, it is possible to use a simple text editor like Notepad++ to do some global editing work on a songbook, which would be most of the time impossible to do via sql, even with the best database browser.

Some more, the fact that songbooks are all in memory, enables VideoPsalm to search for songs in a world language aware way.
For example, VideoPsalm would find occurrences of the word "lumière" ("light" in French) even if you had typed "lumiere" (note the "é" and the "e" without accent), or the opposite way around.
A sql database backend cannot easily do that, if at all.
VideoPsalm can also take advantage of some of the high level string search functions, which enables for example to search for phrases: "King of kings" can be searched as a phrase with the text "kingofkings".
Many search combinations can be made possible in memory, which are impossible via sql, even with a database with full text search enabled.

As a side note, VideoPsalm can import songs and Bibles from several know sources, for example OpenLP, OpenSong, EasyWorship, EasySlides, Psalmo, text format, OpenLyrics format, Zefania, UnboundBible, WebAsaph, MediaShout, etc.

[voyager529]

Rant warning.......

Originally Posted by VideoPsalm

Databases were invented because, at that time, computers had tiny memory.

No, databases exist because there is a need to store data in such a way that the data and its relation to other data can be represented and used. Yes, computers today have significantly greater capacity than they did in years past, but the amount of data that is stored has also increased dramatically. Perhaps not for worship software, but there isn't a laptop in existence that can hold Facebook's main databases in their RAM. There is still utility within them; I am unclear as to the use of the past tense here.

People also needed to work at the same time on the same data, which only a database system would allow securely.

This is still a very common use case for databases.

However, todays computers can store several hundred of Bibles right in their memory. The minimum memory amount one can buy a computer with today, is around 4 Gigabytes.

This is true. However, there is significant OS and application overhead involved, too. Additionally, many MANY churches represented here at CMN do not have the resources to get a shiny new presentation computer, which is why many titles available continue to keep their system requirements loosely to "computers of a medium-tier or better from the past five years".

One Bible contains around 2 million characters. Usually, a Bible structure stored into the computer memory occupies more than its amount of characters, maybe up to around 20 Megabytes. 100 Bibles would then occupy 2 Gigabytes.

That's wonderful. However, there's also the relational data involved that allows immediate "queries" for chapter and verse that is also in use. Additionally, it's not simply the Bibles themselves, but the media in use, prerendered frames, and other things that make modern worship software very complex.

I can compare this to the Asus Windows 8 laptop I use right now, which has 16 Gigabytes of memory.

I have three friends, all of whom have needed upgraded laptops. Their *upgrade* was a Thinkpad T61 I built out for them here at work. Each of them has 2GB of RAM. Your 16GB figure is atypical for laptops in the sub-$700 category.

Songbooks are much smaller than one Bible.

Our song library, spanning nearly a decade of additions and multiple-song-versions, is larger than most three versions of the Bible using the same Access database format.

You can very very very easily have tens of thousands of songs in your memory, letting your program have instant access to all of it (to the contrary of a database, which would access songs sequentially, eq. slower).

On the contrary, relational database models work wonderfully for these types of tasks, given that they can do searches and joins based on relational data, allowing for instant searching of basically any song database or Bible I've come into contact with. Similarly, I don't understand the notion of "sequential" access to a database.

Using a database like MS Access, MySQL, Progress, MSSQL, Oracle, etc. to store songs or Bibles could be good for concurrency = several people working on editing songs in the same songbook at the same time, however this is not used most of the time, if ever, by worship software.

Perhaps not - but searching and metadata is. I cringe at the thought of a piece of worship software running on Oracle though - now THAT is a crime against RAM usage, and I doubt there are many churches around that would want to pay the licensing fees for it lol.

The EasyWorship team is currently working on such a software, but I don't know many churches who really need this function.

There must be some market for it. Additionally, even if it's not concurrent, the thought of running queries against a central SQL server has proven appealing in order to ensure that the youth group and the main sanctuary have databases in lockstep. Ultimately we opted for a network share of the Mediashout Access database instead, but the principle is there.

The "work in parallel" on the same song or songbook is the only positive point of using a database for songs and Bibles nowadays. But there are big drawbacks.

Most of this has been covered already - there is utility in the metadata and relational database model for use cases such as worship software.

VideoPsalm uses plain text files, one file per songbook or per Bible, in a json dialect format.

This is similarly a good thing, for a few of the reasons you specify, and some others. However, one drawback of this method is that putting all the entries into a single folder will make most file systems slow down as they attempt to enumerate the entire contents of a given folder. The most obvious workaround for this is to split it up, either by alphabet, every X files, etc., but it's a part of the one-file-per-object model that must also be addressed.

Therefore, it is possible to use a simple text editor like Notepad++ to do some global editing work on a songbook, which would be most of the time impossible to do via sql, even with the best database browser.

In my experience, if you're finding yourself in a place where you'll need to edit the song data directly within its native format - be it a text file, MS Access, an SQLite editor, orr anything else...there's likely a deeper problem.

Sometimes, this problem is corruption of the database file itself (which is sometimes unavoidable). For the most part though, the only need to edit in this fashion is if the main program does not allow for the proper type of edit that needs to be made. This problem is significantly larger than what it takes to edit the raw files - they really shouldn't require direct editing.

Some more, the fact that songbooks are all in memory, enables VideoPsalm to search for songs in a world language aware way.
For example, VideoPsalm would find occurrences of the word "lumière" ("light" in French) even if you had typed "lumiere" (note the "é" and the "e" without accent), or the opposite way around.

To the best of my knowledge, the only way to do what you're talking about is some sort of character replacement table, such that "e" is substituted for "é" in the query, and spaces are generally ignored on both ends. This is a variant of the concept known as sanitizing one's inputs, which many databases accommodate. This is not something that is possible for one but not for the other. I will concede, however, that such things are, in my opinion, a foundational requirement for anything that performs searches such as song lyrics or Bible verses, regardless of what format the data is stored in.

A sql database backend cannot easily do that, if at all.

Google does it, and I'm certain they don't sift through text files to do so.

VideoPsalm can also take advantage of some of the high level string search functions, which enables for example to search for phrases: "King of kings" can be searched as a phrase with the text "kingofkings".

I can most definitely appreciate this feature. I am genuinely glad it is there, and do certainly agree that it is a core requirement. I will counter the argument in saying that Mixmeister 3 did this ten years ago. Crucial, but not exactly patent-worthy in my opinion.

Many search combinations can be made possible in memory, which are impossible via sql, even with a database with full text search enabled.

Again, somehow Google manages to do this.

As a side note, VideoPsalm can import songs and Bibles from several know sources, for example OpenLP, OpenSong, EasyWorship, EasySlides, Psalmo, text format, OpenLyrics format, Zefania, UnboundBible, WebAsaph, MediaShout, etc.

Good to know for anyone that wants your product. I'm not sure whether this constitutes self promotion though...

Joey

[VideoPsalm]

Hi Joey,

Nice post, thanks for taking the time to answer!

English doesn't make use of diacritics, however many of the world languages use diacritics like ß, ñ, é, ö, è, ü, à, ä, ô, ç, â, etc.
For example: French, German, Spanish, nordic languages, etc.

There are cases where it is OK to spell words differently when it comes to diacritics.
For example, in German, it is permitted to write "Wasser" or "Waßer" (Water in English). Spelling differences are common, also due to the fact that German has spelling variations between Switzerland, Austria, Germany, etc.

We humans often misspell diacritics, this is the first source of spelling mistakes. But computers are usually strict, even on such "small" mistakes:
If you store the word "Mühle" (Mill in English) in a database like MSAccess, Oracle, MSSql, MySQL, Postgress, Paradox, etc., there is no way to find this word if you search for "Muhle" (note the absence of " on the ü).
The same problem happens with "Wasser" and "Waßer".

When searching for words, a software shouldn't expect users to spell every word perfectly right.
So when a user types in a few keywords to search for a song, the software has to allow for some spelling "flexibility", especially concerning diacritics (accents).
Performing this flexibility at the database level is impossible without some tricks (impossible).
Most presentation worship software are flawed in this regard. To my knowledge, only VideoPsalm does take diacritics and punctuation with enough flexibility.

Yes, Google Search does perform pretty well.
But one is sure: they do not perform a plain vanilly "LIKE" SQL command.

This is howerver what most worship software do: Connect to the song database, and perform a LIKE SQL command to find words in song titles or lyrics or some other fields.
Note that OpenSong doesn't use a database, and is still unable to perform a diacritic safe search. This is inherent to the technology that OpenSong uses, which doen't provide world language specific support.
OpenSong might work OK in English, but will fail in languages that have diacritics.

This was a discussion about "search and diacritics".
But there are other "search" related problems that cannot be directly, i.e. without tricks, solved at the database level.
Punctuation also has bad influence on search.
And how do you implement phrase search?

I will answer to your other points in another post.