See count of songs in database
Hi,
We just recently moved from OpenSong to OpenLP. I imported over 3000 songs from OpenSong and didn't see any errors, but now we keep on finding songs that are missing.
I've looked in my backup of OpenSong, and the missing songs are there.
How can I see how many songs there are in OpenLP?
I need to figure out if I'm going try and fix this or just start from scratch again, and the song count will help me determine that.
Comments
I think for that you will have to take a look at the sqlite file where all the songs are stored. In OpenLP there is a menu item called "Tools ‣ Open Data Folder". In the data folder you have a folder songs and in there the songs.sqlite file. You need an app for reading sqlite though.
Thank you @fiveofsix that's turned out to be the most reliable way of doing things.
I thought I could get an accurate count of how many lyrics there are by exporting them, but in my case less lyrics get exported than are in the database.
For future reference, the SQL command I used on that DB was:
select count(*) from songs;
@RobertSpencer The SQL command you used will give you the count of records in the songs table in the database. Two things to keep in mind....
First you could have temporary records and they will NOT be shown within OpenLP. Run the SQL command 'SELECT sum(temporary) from songs;`. IF you get a number more than 0 you have songs that will not show up in OpenLP....You can generally fix this by changing the field to 0, but you may end up with duplicate songs.
Second you may have "damaged" songs. This is harder to find and fix. Find a missing song title. What is the title before and after the missing song in the original database? Find the song title before the missing song in the OpenLP database. Sometimes the missing song title is in the database, but the rest of the record is not complete....depending on what is wrong it may be easier to try reloading that one song.
I know you have a large number of songs that don't show up after being loaded into OpenLP and I am not suggesting you reload each by hand! but knowing a bit more may help us help you.
@JohnM Thank you for that information.
Searching for for temporary songs retuned 0.
I searched for songs in the DB that failed to export, but I can't see anything wrong with them. I'm assuming I don't know what to look for.
@RobertSpencer copy your songs.sqlite file and place it here. I have the time to look into it right now. Also if you know one of the songs that is not being displayed, let me know what it is! Sometimes the reason for the song not to be shown is very hard to see!
@JohnM Thank you for your help.
I don't know how to answer you though. I suspect the songs aren't displaying simply because they are not in the DB.
I originally imported just over 3000 OpenSong files, there's 1278 records. It would take me a long time to find a song that's there, and not displaying. Unfortunately I don't have the time to do that.
I can though give you the name of a song that's there, but not exporting. For what it's worth.
id=37, "Eternal Father, strong to save"
I suspect part of the issue with exporting may be that songs with duplicate titles are being clobbered on export. I'll upload the first backup I made, so you can see the state of the DB before I started making changes. You'll be able to see for yourself that it's a bit of a mess, with a lot of duplicates (I have no idea why the original OpenSong collection was such a mess).
I'll also include a text file with the titles of the 1003 songs that successfully exported on Kubuntu.
@RobertSpencer I have both files and have started looking at them. Nothing to report yet, it can be hard to see the issues sometimes!
@RobertSpencer I have tested song 37 and have not found any reason why it was not exported. It is a duplicate of a song at song 595. Still researching!