Bugzilla – Bug 8753
Add "updated" functionality to CLI database queries
Last modified: 2009-09-08 09:29:15 UTC
For all CLI commands returning a view or selection of the database I would like to be able to request updates, that is request changes since a certain state. This would, of course, require some kind of state or transaction model, yet it would make development of client apps a lot simpler and I image in could be uses for Jive as well. Here's an example: albums requests all albums in the database. I would like to add a "timestamp" or "transaction count" value to the return parameters and then albums changessince:timestamp would give you a list of all albums added sice "timestamp" and all albums removed since "timestamp". This could alternatively be done as two commands. If the database was completely flushed, all albums would be returned plus a code indicating that the database had beed flushed. I would like to see this on: alarms albums artists favorites genres musicfolder players playlist (this does already have the timestamp) playlists radios music_services titles years may have forgotten a few... A notification does NOT do the job since that implies you will have to have the HTTP context open and you cannot ask for changes a while later.
This seems hard to me. Andy, what are your thoughts?
Sorry but no way this would happen for 7.2.
Oh, and yes, hard.
Ok, thanks. Joerg: Can you describe the problem you are trying to solve?
I understand this is a bigger change and I thought this is probably something for the new database scheme in 7.3 (don't think it was me adding the 7.2 target). I will start with loading everything on demand anyway for the iPeng native. But I think this is a very useful feature. I can live with a 7.3 target, as long as I have a bit of confidence that it will actually come then. If not, I would like to know that, too, since then I will try to invent my own update scheme, which will be more complex, but it should be doable. BTW, Erland did sound like he had an idea how to do this in the thread I opened, maybe he can contribute (will add him to the list on CC. Erland, hope you don't mind) - Will add the link later, the forum seems to be down -
I'm not convinced we'll do this yet, it doesn't seem that useful. If there's a performance reason why you need a local cache, we should address that instead.
The base functionality for this, if we decide to do it, is that we have a timestamp column in each database table which is updated when the particular row is created or updated. I think a timestamp field could be useful also during incremental rescans to detect what has been updated, but I'm not completely sure yet. A timestamp field should be fairly simple to implement for all the objects that are stored in the database. If we have that support from the database, it should be possible to do the CLI/query implementation as separate plugin if we don't want to include it in the standard distribution. Of course, it would mean that the plugin needs to implement all the queries which already is in place in the SqueezeCenter core code, so it would probably result in a lot of almost duplicated code in the plugin. Joerg can describe this better than me, but I think the issues he have are: 1. Requesting a list of objects takes too long time, for example a list of albums. The Controller can do this incrementally, but as I've understand it is hard to do this in the iPhone because the list controls are provided by Apple and you can't optimize them in the same way as we can do in the Controller interface. 2. Even if it would be extremely fast to request a list of all albums, the client application would have to look through the whole list every time because it can't be sure which items that is the same as before and which has been updated. As I've understand it the purpose with this enhancement request is to be able to keep an updated cache of these data lists directly on the iPhone so they don't have to be retrieved every time the user enters a menu. The client application just have to check if something has changed by calling SqueezeCenter with a timestamp. I'm still not sure this timestamp handling is the best solution to the problem, because I have a feeling there will be complicated cases where a timestamp isn't enough. As an example, deleting an item won't modify any timestamp of the items that remain and my guess is that we don't want to keep track of deleted items. Anyway, Joerg can describe the problem a lot better than me since he have better knowlege of how he likes to cache the information.
Erland, actually your description is correct. I do NOT plan to re-implement the UITableView control on the iPhone so I have to live with what's there. That means: no deferred loading. And I feel a list of deleted items should be maintained, too. If you do a full database rescan I can live with saying so and flushing the cache one but besides that I would suggest tracking deletions. I fail to see why this should be so dramatically complex. Andy, improving performance is fine. Give me a list of Albums, including Artists, no coverart, for an average database (say, mine, ~1.000 Albums) in 500ms and any database, no matter how big within 1.000ms and I'm fine. Otherwise I will cache. I'll have to process the stuff on the phone as well and would expect at least another 500-1000ms so I'll end up at two seconds waiting time. That's fine. Anything else is too slow, IMHO. Today, with 75 items per page the first Album page loads 10-14s on iPeng, subsequent random pages take 5-9s and returning to a previously loaded page takes <5s on iPeng. Descending into an Album takes 3-5s. And there are still people telling me it's too slow to be usable at all...
Maybe this is something we can think about for new schema.
The current plan for the new_schema stuff re: updates was basically to track a single last-scan-time for the whole library, which is used for the mtime checks for rescanning. We could easily add another column which is basically that same scan-time, set to whenever the track/album was inserted or last updated, and allow queries to make use of it. Detecting deletions doesn't sound worth it though, it would mean keeping deleted tracks in the database with a deleted flag just to support it, which can get out of hand.
Brandon, that's pretty much what I would need. One more thing, though: Not tracking deletions is fine as long as they don't get "filled in", that is, an ID is not being reused or at least not being reused for a long time. Maybe not reusing it at all makes more sense since it will always work and I don't think you should run into trouble too soon with today's integer sizes.