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

Anthony Vaughan

MP Donator
  • Premium Supporter
  • June 25, 2015
    474
    234
    Home Country
    United Kingdom United Kingdom
    While we are chatting, now get mad, you know that I said I would refactor the database to get rid of any redundancies.

    I've done that and have been using the amended system for quite a while now.

    Then I got to thinking that it would be great if the client databases could be truly multi-user: i.e. a single database, for each media component, available to many clients.

    So, I went through the process of changing the database connections so that 'using' is employed for all database connections. But that still left me having locks on the client databases that prevented other apps, like synchronization, form processing the client databases as changes occurred. The next step was to close database connections whenever the home screen was displayed (unless player was active). This really helped to make my sync app work better for my four clients.

    MediaPortal has been developed with so much care to make sure the front-end is pretty much unaffected by the back end that it seemed to me that the best way to achieve a truly multi-user setup was to separate the front-end and back ends as much as possible. However, SQLite.NET is quite old and there are so many advances in database accessing that I thought it might be better to start from the ground up and, since I had already done much of the work developing the sync app, to employ EntityFramework, Linq, Lambda and collections making the database accessing fully object based. To keep the front-end light, I have connected the front-end to lists of each entity in each of the client databases and the front-end uses the lists to keep the data current while the session is running.

    But we still have the issue of trying to keep database changes current while there are multiple threads mediating between the front and back ends, without locking the database to other clients. It seems to me that the only real solution was to queue the database changes to a single thread responsible for database updates only (all of the other processing being performed in memory).

    This would be okay for a single client with the queue processing being part of the MediaPortal app. It seemed to me that, in order to make the queuing truly multi-user, we would need to queuing system that writes queue entries to a queuing database that is processed by a windows service whose sole job is to write queued updates to the database, across the whole household (a bit like TVService). This would avoid any locking and allow the MediaPortal app to perform optimally with no back-end interruptions.

    I am happy just to do this for my own system but want to know whether you would be interested in what comes out of this. I am very nearly there in that the bulk of the donkey work has been coded and I am into detail (and getting the queuing to work). The system is working but I have a lot more testing and debugging to do yet

    Are you interested in using what I come up with. If you are, I will undoubtedly have a few questions about why certain aspects of the system are there so I understand what they were intended for (stacking comes to mind). I appreciate that they have real purpose but I don't use them so I may miss something.

    I'll understand if you feel this is too risky, but I am seeing the benefit of a truly data synchronized system, within a household, as being worth the effort.

    For me there is nothing out there as user-friendly as MediaPortal and hope that it can be made even better over time.

    Tony

    Here's the first release of the refactored application. Upzip the file to a folder and run RefactorMediaPortal.bat. Your MediaPortal application will be overwritten (well, parts of it). When you run Configuration or MP for the first time it will upgrade two of your client databases. Please check that they have successfully copied your existing data to the new versions. The existing versions will remain. To revert to the standard version of MP, reinstall MP 1.29 in the usual way.
    Please let me know if you have any issues. Just because it works for me doesn't mean I haven't missed something about your setups.
     

    Attachments

    • TvEngine3 refactor.zip
      3.2 KB
    • mediaportal-fanart-handler-master refactor.zip
      114 KB
    • mediaportal refactor.zip
      3.2 MB
    • MediaPortal Refactor - How it Works.docx
      100.8 KB
    • MediaPortal Refactor - How to use it.docx
      317.8 KB
    • MediaPortalRefactor1.29.012.zip
      6.1 MB
    Last edited:

    ajs

    Development Group
  • Team MediaPortal
  • February 29, 2008
    14,138
    8,690
    Kyiv
    Home Country
    Ukraine Ukraine
    That interests a lot of people, including me. I am currently using the Database Synchronization plugin and the Trakt plugin. If you change the database model in Mediaportal itself, maybe Mediaportal will be able to work on several computers in parallel. But Mediaportal is only the visible part of the iceberg, the non-visible part of the iceberg is plugins, they all need to be changed for the new model of working with the database. And this is a huge piece of work, but it is not a problem, the problem is that some of the plugins do not have the source code.
     

    Anthony Vaughan

    MP Donator
  • Premium Supporter
  • June 25, 2015
    474
    234
    Home Country
    United Kingdom United Kingdom
    So far, I've only had one error message from something accessing the image table (I assume from Fanart database processing - I think it's the Fanart Handler). Everything still works though.

    I am viewing this as a long-term project (up to six months to get it water tight). It would be great if you could live with that.

    My main aim is to get it right rather than quick.

    Is it okay if I ask you questions from time to time - there may be a few at first? But they will be targeted at specific issues.

    Tony
     

    ajs

    Development Group
  • Team MediaPortal
  • February 29, 2008
    14,138
    8,690
    Kyiv
    Home Country
    Ukraine Ukraine
    So far, I've only had one error message from something accessing the image table (I assume from Fanart database processing - I think it's the Fanart Handler). Everything still works though.
    I have about 10 plugins installed, @catavolt has about 70 ... So not everything is so simple ...
     

    ajs

    Development Group
  • Team MediaPortal
  • February 29, 2008
    14,138
    8,690
    Kyiv
    Home Country
    Ukraine Ukraine
    Is it okay if I ask you questions from time to time - there may be a few at first? But they will be targeted at specific issues.
    No problem, but I am not a guru in the source code Mediaportal, as well as do not always understand the logic of operation, interaction, etc. Well, and I do not know C#. But I will help you with what I can. :)
     

    Anthony Vaughan

    MP Donator
  • Premium Supporter
  • June 25, 2015
    474
    234
    Home Country
    United Kingdom United Kingdom
    Okay. Thanks.

    BTW, although I have converted almost all of the database processing to object based queries ans updates, there are some bits where the SqlLite.NET approach has to be used and I've kept them.

    I'll be in touch.

    Tony
     

    Anthony Vaughan

    MP Donator
  • Premium Supporter
  • June 25, 2015
    474
    234
    Home Country
    United Kingdom United Kingdom
    I thought I should let you know exactly what changes I have made to VideoDatabase5.db3. They are as follows:
    1. Move strFilename from files to movie;
    2. Remove files table;
    3. Remove duration;
    4. Remove hasSubtitles from filesmediainfo.
    Both duration and hasSubtitles were being stored in two places and I have rationalized that.

    I have amended all processing related to these changes throughout the application.

    These changes mean that duration, watched, times watched and watched percent are all available straight from movie (which is used to identify a video throughout, anyway). So there is no need to handle duration separately from the core processing (which it needs to do currently).

    Getting rid of the files table, which was superfluous, and removing the duplication of hasSubtitles and duration, has simplified processing considerably.

    I hope this helps you to understand why I felt it was necessary to change the database layout before handling the multi-user issue. I think it'll really be worth it in the end.

    Tony
     

    ajs

    Development Group
  • Team MediaPortal
  • February 29, 2008
    14,138
    8,690
    Kyiv
    Home Country
    Ukraine Ukraine

    Anthony Vaughan

    MP Donator
  • Premium Supporter
  • June 25, 2015
    474
    234
    Home Country
    United Kingdom United Kingdom
    Understood. This is all some time off, but, yes, if you want to take up this upgrade, then it will be necessary to check those other plugins.

    I guess it's a question of whether you think it will be worth it :).

    As I say, it will be in the next few months and there is no rush.

    Tony
     

    ajs

    Development Group
  • Team MediaPortal
  • February 29, 2008
    14,138
    8,690
    Kyiv
    Home Country
    Ukraine Ukraine
    Understood. This is all some time off, but, yes, if you want to take up this upgrade, then it will be necessary to check those other plugins.
    I don't have that many plugins. And there is no way to check them all.
     

    Users who are viewing this thread

    Similar threads

    Hi, must be really a while, cuz I can not remember any post from you ;) But might be also that I joined the team too late. Yes, there will be many things. Main reason is simply, that MP2 is not developed based on MP1. I for example never used MP1. I tried it lately to test voice control and was shocked about many inconsistancies in...
    Hi, must be really a while, cuz I can not remember any post from you ;) But might be also that I joined the team too late. Yes...
    It´s been a while... :) Well, simply the reason is cause MP1 was working so well... :) Now I decided to upgrade my old servers and...
    Replies
    1
    Views
    240
    Ok, the importer also remembers the queue of items that are still needed to be processed in settings file. If there is something stuck, it will try again on next server start and probably stuck again. But removing the media source also removes the associated queue. The server's TV plugin automatically creates the media sources for...
    Ok, the importer also remembers the queue of items that are still needed to be processed in settings file. If there is something...
    Hi, I’ve been struggling with MP 2 now for several weeks. Initially all was fine. Then in the client, the listings of recorded TV...
    Replies
    8
    Views
    911
    It is looking and working very well now and I am almost at the point of switching my HTPC to MP2, madvr would definitely push me over the edge for sure.;)
    It is looking and working very well now and I am almost at the point of switching my HTPC to MP2, madvr would definitely push me...
    We are proud to present MediaPortal 2.3 MediaPortal 2.3 is a full-blown media center software that addresses the most common user...
    Replies
    4
    Views
    3K
    MP1 MP2 MP2 DLNA status? DE
    What I meant is that we have a MP2 plugin called UPnP Render that can play media from DLNA servers if I remember correctly. I don't know the current state of the plugin though. That's why MP2 probably never will be certified. ;) Yes, that was also the idea, but it is a big job as all TV's, software etc. seems to not do everything...
    What I meant is that we have a MP2 plugin called UPnP Render that can play media from DLNA servers if I remember correctly. I don't...
    So I'm wondering what the state of play is with MP2 and DLNA functionality? When I started this post, I did see some posts...
    Replies
    10
    Views
    1K
    Thank you joecrow! It seems to resolve the problem I got since 1.22.
    Thank you joecrow! It seems to resolve the problem I got since 1.22.
    Hi Guys, Most times I have had an issue with MP1 it has been a platform/OS/finger-trouble issue that a re-install etc fixes...
    Replies
    9
    Views
    984
    Top Bottom