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

Anthony Vaughan

MP Donator
  • Premium Supporter
  • June 25, 2015
    520
    289
    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

    • MediaPortalRefactor1.29.014.zip
      7.3 MB
    • MediaPortal Refactor - How it Works.docx
      101.9 KB
    • MediaPortal Refactor - How to use it.docx
      318.5 KB
    Last edited:

    ajs

    Development Group
  • Team MediaPortal
  • February 29, 2008
    14,248
    8,853
    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
    520
    289
    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,248
    8,853
    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,248
    8,853
    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
    520
    289
    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
    520
    289
    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,248
    8,853
    Kyiv
    Home Country
    Ukraine Ukraine

    Anthony Vaughan

    MP Donator
  • Premium Supporter
  • June 25, 2015
    520
    289
    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,248
    8,853
    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

    Top Bottom