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

[ajs]

But the progress bar in that skin is visible only for the item that has the focus.

Depend from skin:

 

[ajs]

I would be prepared to trial your new implementation, but although I am a "tester", I use none of the extensions that @ajs has been concerned about, and have only a single-seat installation, so my testing would be very "one-dimensional".

If there is no regression, it is also a necessary test.

 

[doskabouter]

Did a quick read of this thread.

Couple of questions/remark:

1: You want to have 1 central database for all mediaportal clients in your house?
Am I understanding it correctly that you created some kind of service managing a sqlite database?
If that's the case, I would go with just a regular Mysql or something...

2: https://forum.team-mediaportal.com/...base-for-multi-seat-usage.140621/post-1286235
did you think of movies consisting of multiple files? ajs already asked that

3: I've added some try/catches All errors are properly logged I assume?
4: percentage watched I agree with ajs, as soon as you've watched more than x % increase numberoftimeswatched and set watched to true (that matches our usecase)
5: lost knowledge due to leaving devs: Ideally all design decisions should be documented somewhere (at te very least in the jira ticket or findable through that)
I always try to trace back how things evolved by doing git-blame but too often that leads to a deadend...
6: incompatible databases can probably have a view in them for backwards compatibility?
7: any other discussions about database-things: you can also ask me I've got some experience in those as well (right @ajs? )

 

[Anthony Vaughan]

I think you've done the right thing by moving this conversation to a different thread.

I have 35 years of database design experience and I finished my career as a database architect at Imperial College. I've been very lucky to get into SQL databases very early on and I have lots of inside knowledge that is a bit hidden now. I started developing for Windows in 1991, 2 months before Windows 3 came out .

The bulk of my work, in the last 10 years has been with SQL Server and SQLite using WPF, LINQ and Entity Framework. I also worked at Oracle houses where I gained my best knowledge about database design.

I understand the point about the original reasons for design decisions being lost as developers leave and I am happy to reconsider why we have certain features and whether they are still relevant.

The upgrade I am proposing is significant, but I believe we can manage it. There is no rush, because we need to get it right.

Testing will be key and, thank you, CyberSimian, for your offer to help. I have been implementing this change as an upgrade to the latest version: i.e. I install the latest release, in this case 1.29, and then copy files over that installation. I think this approach would be good to start before moving to a full release. The issue for me is that I tend not to use special settings and that is where people like CyberSimian will be of great help because he uses, or is familiar with, so many of the features I don't touch.

Can you tell me where I can deposit files for others to pick up? I'll try to make it easy to install by sending the files and a batch file to do the install - to keep it really simple. Alternatively, as you're in the UK, CyberSimian, I am happy to communicate by email to avoid cluttering up the forum should that work better - using the forum to get views from people generally.

Tony

 

[Anthony Vaughan]

1: You want to have 1 central database for all mediaportal clients in your house?

The reason for my upgrade for MP1 was to make MP1 work with the client SQLite databases (Folder, Music, Picture and Video) without locking the databases for the entirety of a user session - doing away with any need to synchronization applications. My design works with a single user or a multi-client household. For a multi-user household, you just need to place the databases in a location where all of the clients can see them and configure the documents folder MediaPortalDirs.xml to point to that location.

All of the database handing is performed in the databases class of MP1. I have incorporated Entity Framework into this design. To achieve a multi-user setup in a multi-threaded application I have removed auto-increment from the primary keys of the tables in the Music and Video databases, except for VideoThumbBList (because of the multi-threading), and have rationalized the video database to remove redundancy. I have also made GUIVideoFiles work more efficiently when building and manipulating folders to add new videos to the database and remove existing database records for videos that no longer exist.

The code handles stacked files though I don't fully understand that and the reasons behind them as I don't use multi file videos myself, so it would be good for me to get a better understanding of that because I am sure that processing could be much for efficient (less duplication of processing).

I see this as an excellent opportunity to streamline the database processing and I have employed object-orientation throughout and am caching all of the data so it is processed much more quickly behind the scenes - to allow the front-end to proceed with as little interference from the database processing as possible.

I will produce documentation that explains the rational behind the design.

Since introducing this upgrade in my household, I have not had any synchronization issues and the product has been stable. Having said that, I am sure we need to do more testing to cater for all of the features I don't use or know about.

Tony

 

[doskabouter]

Ok, you obviously beat me when it comes to databases

To me it sounds a bit risky to remove autoincrement prim.keys (who's responsible for them than?) and have more than one instance of MP sharing the same sqlitefile... (even within mediaportal itself) but perhaps I don't have enough experience with sqlite to be sure this couldn't cause database corruption.
And as for caching... I think one of the hardest things ofcourse is cache invalidation

 

[Anthony Vaughan]

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. This all happens in the background.

It also means that CyberSimian won't have to be concerned about deleting all watched recordings because the system will only delete data for records you have chosen to delete manually.

Also, in the GetMediaInfoThread I check whether the database has any records for files that are no longer in the current folder (LoadDirectory) and remove them - so both bases are covered: i.e. disk files deleted using MP or outside of the system.

Is there anything I may have missed in doing this?

Tony

 

[doskabouter]

Well, I could think of a scenario where there are some local videos/pictures/whatever ending up in the shared database...
(F.e. when you insert a dvd or attach a usb-stick with pictures in your htpc...)

Edit: or multiple users watching the same video on different htpcs and stopping at different times.... brr...
Edit2: regarding used plugins: I use mpsync

 

[Anthony Vaughan]

To me it sounds a bit risky to remove autoincrement prim.keys (who's responsible for them than?) and have more than one instance of MP sharing the same sqlitefile... (even within mediaportal itself) but perhaps I don't have enough experience with sqlite to be sure this couldn't cause database corruption.
And as for caching... I think one of the hardest things ofcourse is cache invalidation

The pictures and folder databases already don't have auto-increment for the primary keys. I understand your concern, but MP has a very low database hit-rate compared to other products. Therefore, I estimated that, to get multi-user capability as well as multi-threading to work, we would have to have the primary keys generated up-front. It's not a new idea - Oracle uses the same technique by storing the primary keys in a sequence table. I tried that but was prevented from using that technique by the multi-threading in MP - due to database thrashing.

I am using two layers for database processing:
a) The cached layer to deliver data to the front-end;
b) A queue to process database instructions like add, delete and update.

The queue processor, which runs in its own thread, handles any anomalies like different users generating different ID values for a movie.
I haven't experienced any data corruption so far, because the queue forces other processes to wait until the database is free - meaning that only one process can access the database for add, delete or update at a time.

SQLite locks the database for the duration of any write. SQLite supports multiple reads, but only one right. This is why I have adopted the 'using' clause for every database access to ensure that data accesses, and locks, are as short as possible. Up till now, MP has locked the database for the entirety of a session - which is why we needed MPSync. I have designed the proposed system to work as a single user or multi-user system without the user having to do anything other than decide where they want the databases to reside. Since the system depends upon a PC running TVService, then I guess it would be logical to make that the location of the central databases. But my design doesn't depend upon that. The databases can be located anywhere on the network and that location is defined in MediaPortalDirs.xml.

Well, I could think of a scenario where there are some local videos/pictures/whatever ending up in the shared database...
(F.e. when you insert a dvd or attach a usb-stick with pictures in your htpc...)

Edit: or multiple users watching the same video on different htpcs and stopping at different times.... brr...
Edit2: regarding used plugins: I use mpsync

That's true, but anyone in the household can delete a file using MP at any time. MP relies on people working together to avoid doing that.
BTW, the whole point of this upgrade is to not need MPSync any more.

Assuming that people will not delete videos other people are watching, I have designed the system so that while you are in a session; i.e. you have opened MP and are using it, the data cache will be local to you. In other words, if you leave off watching something and, say, watch the news, when you go back to that video you will resume from where you left off - whether or not other users watch the same video. Once you leave MP, and go back in again, you will refresh your cache from the database and will see what everyone else sees so far as resumes are concerned. I thought this was the best way to keep everyone happy. But I can't, of course, do anything about anyone deleting stuff deliberately.
It's an interesting point about the use of temporary media, like USB sticks. I think users are responsible for using the file menu to delete a video, irrespective of where the video is held. If you simply remove the USB stick, the data will remain in the database. In my design, you would have to use the file menu to delete the movie for the data to be purged from the database. So, I think the procedure I am suggesting should stand up in that scenario.

One last thing, I have made sure that times watched, watched and percentage watched work in conjunction with the play time percentage (the percentage at which you want to see where you are in a video). I notice that, irrespective of where you are in a video, the data watched field in movieinfo is updated to DateTime.Now. Is that still what you want to happen. It seems to me to be a slight inconsistency but there may be a reason for it.

Tony

 

[Anthony Vaughan]

It occurs to me that there might be a concern about automatically deleting records for data that is on USB sticks or removable drives.

The code in LoadDirectory, which fires off the GetMediaInfoThread thread, only affects files, not directories, and the directory has to accessed by MP for the files to be checked.

Therefore, data records for plugin devices will not be deleted by the system automatically when they are not plugged in.

I hope that is reassuring.