Database Format / Importing Zionworx Database
I am trying to "import" a Zionworx database into v1.9.3. I found on the Wiki site a description of how to do this, http://wiki.openlp.org/Development:ZionWorx_-_Song_Data_Format , but it does not seem to match up quite right. I assume this is for the v1 database instead of the v2 database since it refereneces the songs.olp file.
Can someone tell me if the v2 database uses a different database engine or if the fields were just rearanged? Any other Help to convert a Zionworx Database over would be great. Mainly the SQL queries needed to finish the converting.
Comments
You are correct, that is aimed toward the v1 database. I think that document was written long before we had anything solid for v2. I'll see if someone can update that page.
Version 2 uses SQLite 3 (as opposed to 2.8 used by v1), and while the structure of the database is mostly the same, there are some notable differences.
If you have the command line "sqlite3" command installed, open the OpenLP database with the tool, and then type in ".schema" to see the database schema.
I came into the same problems when trying to convert the Zionworx database. I just threw it out the window and added each song individually.
Yes it took a large amount of time to do it, but now its taken care of.
Good luck with it!
I just pulled 700 songs out of a zionworx database using the "dataWeb Turbo Database Data Exchange" utility as recommended elsewhere.
I didn't like the format of the data in the example, so I used the command:
TdbDataX MainTable.dat mybase1.nyf -fmybase
After 2 minutes of searching and replacing tags with comma's, I now have a nice comma delimited file.
The format of the file is
"SongNum", "Title1", "Title2", "Lyrics", "Author", "Copyright", "Keywords"
as in the following example.
"187","THERE SHALL BE SHOWERS OF BLESSING","","There shall be showers of blessing: This is the promise of love;
.
.
.
Mercy drops round us are falling, But for the showers we plead.","By Daniel W. Whittle/James McGranahan" ,"CCLI #" ,""
I guess I just need a pointer on how I should go about loading it into the V2, and maybe I can work on the beginnings of a guide.
I've just managed to import almost 1900 songs from Zionworx into OpenLP 2.0 although via a round-about route!
On Windows (I'm running XP in a virtualbox on Ubuntu 10.04) follow the instructions on the wiki except for one line which is wrong:
http://wiki.openlp.org/Development:ZionWorx_-_Song_Data_Format
I used TurboDB Data Exchange rather than the studio version which is all in German and didn't seem to work properly for what I needed here. TurboDB Data Exchange worked fine as per the instructions.
I had to do some minor editing of the csv file using find and replace to change things like (C) signs. It depends what character sets you have.
I installed OpenLP version 1.2.9 and opened the songs.olp file using Sqlitebrowser v 1.1 as described. However, I deleted all the exisiting songs in the database before importing mine from Zionworx. This may not be critical, but I have a feeling it may have messed up the author id entries if I'd left them in. I had 95% of the songs / hymns in my Zionworx database anyway.
The line you need to change in the rest of the instructions is near the end:
INSERT INTO songauthors (authorid, songid) SELECT songid, settingsid FROM songs;
should read
INSERT INTO songauthors (authorid, songid) SELECT settingsid, songid FROM songs;
If you don't make this change the authors do not line up with the correct songs and causes all sorts of spurious errors.
You should now have all your Zionworx songs showing in OpenLP 1.
You can now open OpenLP 2.0 (I'm using 1.9.4) and import your version 1 songs database straight in.
Now if, like me, you're using Linux you can copy the songs.sqlite file to your linux drive and away you go. (I copied the file to my desktop, opened the programme and did an import. Easier than finding the file in the depths of the sibfolder structure). Works a treat.
Hi sab478,
Thanks for your correction. I've updated the wiki page accordingly.