Database: Refactor MP1 database for multi seat usage (3 Viewers)

[doskabouter]

Ok so if I understand correctly:
- Writes will be put in the queue and sent to Sqlite asap, and current cache will be updated/invalidated to reflect those changes?
I can imagine scenarios where the front-end needs something that the write returned (be it an id or something like that) didn't you encounter such cases?

• Reads will be fetched from cache if present, and if not will be read from sqlite (as blocking call), and cache will be updated?
• You will create a new sqlite connection for each and every query? Can't that be a performance-killer?

As for using mpsync: don't know why but it seems to be doing an ok job despite mediaportal locking the database for the whole session...
Times watched; fine by me.
Remove dead records: so that only works if you browse inside the directory in mediaportal? So that will keep deleted directories in the database? Not a big problem in my view but might still be a valid usecase for things like mpcleaner then...
My concern was more that if pc1 has some local files (be it on local c-disk or removable media) those are added in the database and if after that, pc2 starts mediaportal and doesn't have those files present on pc2 it might go deleting those database records.

P.S. I might be overthinking all the possible pitfalls of these changes, but that's just how I am

 

[CyberSimian]

One of the features I have placed in the code is to delete all database records related to a video when you delete the video from the hard disk. This seemed like a pretty obvious thing to do but I thought I would ask in case I am missing a reason why you might want to keep databases records for which you no longer have to original video

Yes, there is one case that I can think of.

One of the really-useful features that Windows Media Center implements is series recording with avoidance of recording the same episode twice. WMC uses unique programme ids to accomplish this. However, this still works when the user employs the record/watch/delete style of use, i.e. WMC remembers the information pertaining to episodes that have been deleted. This information is retained until the series-record request is deleted (at which point no future recordings are scheduled, and the information relating to deleted episodes is itself deleted).

MP does not currently support this. But the DVB-T/T2 system in the UK (and probably the DVB-S/S2 system in the UK) broadcast EPGs that do have unique programme ids, making it possible for MP to support this feature. I think that this is also true for some other sources of EPG (e.g. "Schedules Direct"), and for other countries in the world. There are no current plans to support this, but it might happen at some time in the future. However...

Support for this feature probably affects the recordings database, which I think is separate from the video database? If so, it does not directly affect the design or processing of the video database, unless you were anticipating using the same database structure for both databases.

-- from CyberSimian in the UK

 

[CyberSimian]

Testing will be key ... The issue for me is that I tend not to use special settings.

With a major change such as this, testing by as many users as possible would be advisable, so that all parts of MP can be covered. My use of MP is restricted mostly to the TV and radio parts, but users of the music, picture, and video parts need to become involved in the testing, plus those who use the various extensions/plugins.

I don't know whether there is a specific MP convention for this, but probably the easiest way to provide files for testing is to upload them in a zip file to the first post in this thread, and update that post whenever a revised build becomes available. In this way, any MP user who wants to get involved can download the files and install them.

As you are the author of the first post, you should be able to edit it -- look for the "Edit" button at the bottom of the post.

-- from CyberSimian in the UK

 

[Anthony Vaughan]

P.S. I might be overthinking all the possible pitfalls of these changes, but that's just how I am

You really can't overthink things, so far as I am concerned.

This is not about ego. It's about getting the best product out there. So, if you find issues let me know so I can address them.

Ok so if I understand correctly:
- Writes will be put in the queue and sent to Sqlite asap, and current cache will be updated/invalidated to reflect those changes?
I can imagine scenarios where the front-end needs something that the write returned (be it an id or something like that) didn't you encounter such cases?

• Reads will be fetched from cache if present, and if not will be read from sqlite (as blocking call), and cache will be updated?
• You will create a new sqlite connection for each and every query? Can't that be a performance-killer?

All database writes are put into a queue - after the cache has been updated. The cache always has the latest data (for that session) and the database updates catch up. The updates run in a separate thread so they happen pretty quickly after the event. The queue handles updates coming from other users so the database IDs are always correct. Well, that's the intention anyway.
I get round the need to for the updates to return something by predetermining the ID - that's what Oracle does.
It's the other way 'round:
a) The cache is updated;
b) The database updates happen as and when the system allows.

There is a common misconception that connects are expensive. The first connection is expensive. All subsequent connections come from the internal database cache. Try it out and you'll see what I mean. All relational databases work this way. You gain far more by releasing resources as soon as possible and using them for as short a time as possible. In the case of MP, I do the first connection in the Init of the plugin programs. This way, when you close down MP you can close down the machine straight away. Try that with the current version and you will lose your latest updates.

Remove dead records: so that only works if you browse inside the directory in mediaportal? So that will keep deleted directories in the database? Not a big problem in my view but might still be a valid usecase for things like mpcleaner then...
My concern was more that if pc1 has some local files (be it on local c-disk or removable media) those are added in the database and if after that, pc2 starts mediaportal and doesn't have those files present on pc2 it might go deleting those database records.

That won't happen because LoadDirectory, in GUIVideoFiles, only processes an existing folder and my extra code only processes database records in that folder. It only works on one folder at a time as you navigate through MP (it doesn't loop through sub-directories).

There are two sides to this:
a) Manually deleting a video - the data for that video is also removed;
b) Navigating through existing folders using MP - MP now removes database records for files that no longer exist in the current folder.

This is sure to work because files are held by path and file name and MP only deletes records with a matching path and file name.

I have written another application that removes orphaned records and no-existent paths, on a daily basis.

 

[Anthony Vaughan]

One of the really-useful features that Windows Media Center implements is series recording with avoidance of recording the same episode twice. WMC uses unique programme ids to accomplish this. However, this still works when the user employs the record/watch/delete style of use, i.e. WMC remembers the information pertaining to episodes that have been deleted. This information is retained until the series-record request is deleted (at which point no future recordings are scheduled, and the information relating to deleted episodes is itself deleted).

Yep. I noticed that one as well. I guess that may be something we can look at when things have settled down.

The trouble is that you are now talking about the TVService database and I'm reluctant to touch that because everything else hangs off that. It's not that I can't do it or don't know how, it's just that the risks would be greater.

 

[Anthony Vaughan]

I don't know whether there is a specific MP convention for this, but probably the easiest way to provide files for testing is to upload them in a zip file to the first post in this thread, and update that post whenever a revised build becomes available. In this way, any MP user who wants to get involved can download the files and install them.

Are. So there is no limit on the size of the files that can be posted through the forum?

I agree about using the first posting to submit files (I saw that the skins use that approach

 

[doskabouter]

There is a common misconception that connects are expensive. The first connection is expensive. All subsequent connections come from the internal database cache. Try it out and you'll see what I mean. All relational databases work this way

I have to disagree with that (or perhaps I'm misunderstanding you, do you indeed do a new SQLiteClient for each query?)
I'm using postgres at work quite extensively and if I create a new postgres connection for every query I want to do that certainly takes it's time. (don't ever see something like an internal database cache for that...)

 

[CyberSimian]

So there is no limit on the size of the files that can be posted through the forum?

I don't know.
How big do you anticipate a zip file containing the updates would be? Perhaps @high needs to comment. There may be a better way of providing the updates to all who want to get involved in testing.

-- from CyberSimian in the UK

 

[high]

Are. So there is no limit on the size of the files that can be posted through the forum?

unless we are not talking about hundreds of MB per file it should be fine

 

[framug]

Are. So there is no limit on the size of the files that can be posted through the forum?

I agree about using the first posting to submit files (I saw that the skins use that approach

Hello,
Maybe could you also create a MPEI ?
A MediaPortal Extension include all files.
Then this could be see as a "plugin" and, easy to install by users who wants to test.
But you need to take care of save/restore previous files in case of uninstall.
In fact, a bit like a NSIS install...