I understand where you are coming from now. I guess the answer to the issue of amount of debugging/development on indexes would come down to the design of the helper functions etc that support functions to get indexes.
I don't have my code here in front of me, but no "fields" are accessed directly. Everything goes through a function like jb_getsongstring(intSongID,DB_FIELDNAME) or jb_getsongint(intSongID,DB_FIELDNAME). Really comonly used fields like artist have their own functions like getartist(intSongID), but getartist(intSongID) just calls jb_getsongstring(intSongID, DB_ARTIST). In the "database", each song is just a bunch of fields in brackets, like this:
{145798TAKE}{Take A Chance On Me}{ABBA}{Greatest Hits}{2}{204}{545131}{Pop}{0}
Those are off the top of my head and may not be in the right order but they are File ID, Title, Artist, Album, Track Number, Song Length, File Timestamp, Genre, and Play Count. So DB_ARTIST evaluates as 3, and so jb_getsongstring loops through 3 sets of brackets and returns whatever is between them. There are, of course, equivalent putsongstring and putsongint functions. So to change over to SQL, all I need to do is change jb_getsongstring to
sprintf(strQuery, "SELECT %s FROM tblSongs WHERE intSongID = %d", strFieldName(intFieldID),intSongID);
sqlite3_exec(jb_db, strQuery, callback, 0, &zErrMsg);
The callback function appears to actually iterate through the data, so it is slightly more complicated.
In reality, categories such as decade/genre etc would have permanent indexes that gives benefits of speed of startup (if nothing changed), switching between categories during runtime etc. But a song search etc would probably be a temporary array of song indexes created on the fly. It is probably the performance of the latter that is most important i.e. sql v your own memory based solution.
Right. And it's unlikely I'll implement a true song search because that gets into the whole "on-screen keyboard" issue, although I could do it I suppose with a dynamic version of the T9w system that's used on phones. Essentialy, I'd have to figure out how many keys I have available and distribute the alphabet among them, then at startup create matching indexes. For example, using a 10-key keypad with letters distributed the way they are on a phone, "Take A Chance On Me" comes out to 825322426236663. So as the user starts typing, all matching songs thus far are displayed; four or five keystrokes should be sufficient to narrow down any song.
None of this is rocket science; filtering, indexes and search are Programming 101.
in terms of a single scan to check each entry for a match be it on one field match or multiple (effectively a sql query), I would have expected to be fast enough that the delay before results were displayed would not be an issue.
Well, the tricky part here is that I need to be able to do this in a way that doesn't disrupt the play of music or animation on suboptimal hardware, which is why I'd tend toward preindexing everything. It's spending memory and prep time against run time. The nice thing is that the database is rarely
changed, it's usually just added to, and additions are
always at the end of the list. So I can save the index files after they're created, and just need to insert data into the appropriate indexes when a song is added. The tricky part there becomes when a song DOES change, I need to extract its data out of the affected indexes and re-add it. But that's rare. And there's the big benefit of writing my own indexes and query code; I can optimize for the most usual case.
We use paradox files at work to maintain a list of records in a buffer so we have state if the app crashes. This is very simple, a file, a list but you would not believe the problems this has caused us over the years. But I partly blame the developers (Borland), for that.
Ironically I was just thinking about this last night: storing the jukebox state in a temporary file and offering to restore it on startup. I already track whether or not shutdown was expected in the log, but dumping the playlist and credit count and offering to reload them would be easy enough.
Sounds like I am saying avoid the SQLite approach, I'm not!! Just trying to offer my experiences.
I'm leaning toward not using it right now.
So anyway, we have probably talked this subject too much, I should leave you alone to develop
Not at all, 80% of the features in the jukebox have come from discussions like this.
Being devil's advocate, would offering those users ability to write sql, provide more complex filter arrangements not possibly cause more development than the couple of funtions you have available?
More development? No, it's simple. More technical support Very likely!
--Chris