Howto sync song databases?

edited November -1 in General Discussion


the sqlite song db format is quite handy for searching, but cumbersome when comparing two dbs on a per song basis.

Our scenario: We'd like to have an online song repository. Several contributors can add or delete songs, fix typos and upload the modifications to this repository. In return they should be able to update their lokal working copy of the song db. This would be easy to implement, if the songs were stored in plain text files (with rsync, unison or even svn or alike). It seems, however,  rather tricky to manage that with the openlp db format, since the db scheme doesn't keep track of the modification dates of the songs.

Any ideas? Thanks, Thomas



  • mmjmmj
    edited December 2010

    Hi Thomas,

    at the moment there is no simple nice solution for your issue. From alpha4 on there will be a OpenLyrics export. This way you could store the songs in a SVN repository. Still will you loose possible theme associations.

    In theory it should be quite easy to use a network backended database for the songs. But I think it is no good idea to rely completely on the network.

    It seems to be a good idea to check for duplicates at song import time. This could do the job as well.

  • edited December 2010

    Hi Thomas, you may want to consider Spideroak or Dropbox. My personal preference is Spideroak and you get 2 gig free online storage. You can set up openlp data folder to sync when there are changes. They are both cross platform running on Windows, Mac and Linux. There is also memopal or sugarsync, neither of these two I am familiar but offer more storage.

    Hope this helps.

  • edited February 2011
  • edited March 2011

    Perhaps in a future release the user can select were to store the song, theme, and other databases.  This could include a network location.  A problem could be working offline.

  • edited March 2011

    It's a feature request we are quite aware of, and one that is requested fairly frequently, so it won't be forgotten.

  • edited March 2011
    I don't envy you that task the sync logic can get quite hairy
    Easy enough when there are only a couple of users, and the different songs are in use. But when there are are several users , and the same songs may be edited (differently) at more or less the same time the headache begins. Just a thought - are the songs flagged, in the database, with something akin to "last modified date"? - having that would make simple syncing an easy task - and would help to identify songs that had been modified since the last sync operation...
  • edited March 2011

    Best solution, IMHO, is to get it working where you can connect to a remote database.  MySQL support is in the app, but not 100% functional it seems (I've yet to actually get the program to launch with it configured).  Once that is working then things are almost there.

    It would also be helpful to me personaly to use MySQL because I can write a web script to do entry/updates and not make people have to have OpenLP installed to work on the stuff. :)

  • edited June 2011

    I have this working quite well between the Ubuntu church computer and several Windows 7 computers by using the Ubuntu One sync tool (which is integrated in Ubuntu, and available for download for Windows). Since the Windows beta version currently only synchronizes the "My Documents\Ubuntu One" folder, I moved the OpenLP data folder inside the Ubuntu One folder, and replaced its original location with a symbolic link on all computers ("ln -s <target> <link>" on Linux, "mklink /d <link> <target>" on Windows 7/Vista).

    Once Ubuntu One for Windows allows synchronizing folders other than "My Documents\Ubuntu One", using symlinks won't be necessary.

  • Hello Guys, I think I have the beginnings of a clean solution, albeit one that requires some expertise to setup: You can use Pentaho Data Integration to set up an ETL job (Extract, Transform, Load) which reads the sqlite tables of OpenLP and compares them with a master DB on a central server. All the records OpenLP creates have modification timestamps, so it is always possible to figure out what to transfer and what to skip. 
    This way a contributor can work at home and for example import a new song from songselect and then push the change to the central repository. Then whoever wherever compiles a new song set can just run an update job (called a kettle job in pentaho parlance) to pull in whatever is newer than the newest songs already on the device and then can work with all the newest contributions. This way locally added songs do not get lost when syncing to the central repo. In this case the could be uploaded as well. 
    I just started playing around with this, but am pretty sure will develop this into a complete solution. I used PDI at work extensively, so doing this is a piece of cake. 
    You can get Pentaho DI from here:

    This is how the Job looks by now:

Sign In or Register to comment.