home
products
contribute
download
documentation
forum
Home
Forums
New posts
Search forums
What's new
New posts
All posts
Latest activity
Members
Registered members
Current visitors
Donate
Log in
Register
What's new
Search
Search
Search titles only
By:
New posts
Search forums
Search titles only
By:
Menu
Log in
Register
Navigation
Install the app
Install
More options
Contact us
Close Menu
Forums
MediaPortal 1
Development
General Development (no feature request here!)
Database: Refactor MP1 database for multi seat usage
Contact us
RSS
JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser
.
Reply to thread
Message
<blockquote data-quote="Anthony Vaughan" data-source="post: 1287889" data-attributes="member: 153283"><p>When I started to use MP1, I realized that the client databases had some serious design issues; namely that they are fundamentally flat-file in design. It was also apparent that the client databases were locked for the duration of a client session making a multi-user design approach impossible. For several years I left things alone and put up with these deficiencies until I could stand it no longer and decided to role my sleeves up and see if I could do something about it. I did this because I truly believed in MP and wanted to make it work even better for me.</p><p>I started out by, as a proof of concept, trying to see whether it was possible to make the system work in a multi-user environment, still using SQLite as the database architecture. I started this around June last year. By September I had something that worked for me and knew this was a workable solution. But I really had no idea why some things worked the way they did and it took a bit of work to find out why, for example, the video client database was set-up the way it was. AJS pointed out that there was such a thing as stacked files for a video. I had seen references to stacked files in the code but didn't really appreciate what they were and why. I realized that this the cause of some of the issues in the database design - the tail was wagging the dog. I changed the design so that stacked files were stored as parts associated with a movie.</p><p>I am a developer that strongly believes that a system's design hangs principally on the database design and its ability to future-proof the system. In relational databases a flat design is a recipe for disaster because faults are so complex and time-consuming to fix. I couldn't help myself and I had to redesign the music, video, folder and fanart databases so that they would perform optimally and, as a welcome byproduct, be much smaller. So, I took on that task at the end of October and completed it at the end of December.</p><p>The fanart database was 17 MB and is now 1.7 MB , (yes, one tenth of the size), with exactly the same input data. The music database is now half its original size. The video database is about the same size but I have normalized it so that there are at least twice the number of tables - at 10 MB.</p><p>The key thing to understand is that the task of making the database multi-user required a complete re-write of the back-end and many, many changes throughout the system. </p><p>This was not a trivial task and I think it will not be possible to embed these changes into the current system while preserving 100% of the previous functionality.</p><p>I have been maintaining an upgrade path for the refactored system in parallel with the live system. The truth is that, in order to make the system multi-user, it is vital to control ALL database accesses through the Databases project and that all third-party products should call methods in the databases project to perform database tasks. I see this as a workable and feasible path, but it will require total commitment from the team to replace the current system with this solution and I am not convinced that that commitment is there yet.</p><p>Therefore, I will continue to test and upgrade the refactored version for my own use. If anyone would like to use that version then I think it best that they regard it like a whole new product until, and if, it is taken on-board by the team. I have developed this system as a cut-down version of the live product that will work for multiple users in real time covering Video, Music, Pictures and Fanart. The Fanart is now very responsive without the previous delays and I have removed much of the hashtable behaviour - though I haven't been able to remove it completely yet. But, for the most part, the Fanart system is now data-driven; avoiding repeated reprocessing by analyzing existing databases to identify new data for which to retrieve fanart. I did some time-trialing of the scraping procedures and discovered that, with or without multi-threading, the processing took the same time pretty much to the second. So, I have removed multi-threading from the fanart application to minimized database clashes in a multi-user environment.</p><p>All of this is why I have not been doing any posts. I will also not be writing any database upgrade code unless and until there is any demand for the refactored system. I have found that I can install this system against existing data, albeit video, music, pictures or fanart, from scratch very quickly. For me, the multi-user aspect makes it worthwhile. I appreciate that others may not feel the same way.</p><p>If there is any demand for the refactored system then I will post the latest version and it's code. Bare in mind that the way I have implemented the system, you can install the live system and overwrite certain files from my system (batch file provided) - you must ensure that all of the client databases have been deleted first. The databases will build themselves and I recommend that the configuration application is used to import existing videos, music and pictures.</p><p>If there is demand for the refactored system, then I will consider reworking the database upgrade code. However, having said that, I think that the new system approach is so different from the old system that it would be better to think of it as a different product - even though it looks exactly the same. The beauty of MP is that it works as a closed system and can be rebuilt from scratch quite easily - as I have done several times over the last few weeks.</p><p></p><p>Tony</p></blockquote><p></p>
[QUOTE="Anthony Vaughan, post: 1287889, member: 153283"] When I started to use MP1, I realized that the client databases had some serious design issues; namely that they are fundamentally flat-file in design. It was also apparent that the client databases were locked for the duration of a client session making a multi-user design approach impossible. For several years I left things alone and put up with these deficiencies until I could stand it no longer and decided to role my sleeves up and see if I could do something about it. I did this because I truly believed in MP and wanted to make it work even better for me. I started out by, as a proof of concept, trying to see whether it was possible to make the system work in a multi-user environment, still using SQLite as the database architecture. I started this around June last year. By September I had something that worked for me and knew this was a workable solution. But I really had no idea why some things worked the way they did and it took a bit of work to find out why, for example, the video client database was set-up the way it was. AJS pointed out that there was such a thing as stacked files for a video. I had seen references to stacked files in the code but didn't really appreciate what they were and why. I realized that this the cause of some of the issues in the database design - the tail was wagging the dog. I changed the design so that stacked files were stored as parts associated with a movie. I am a developer that strongly believes that a system's design hangs principally on the database design and its ability to future-proof the system. In relational databases a flat design is a recipe for disaster because faults are so complex and time-consuming to fix. I couldn't help myself and I had to redesign the music, video, folder and fanart databases so that they would perform optimally and, as a welcome byproduct, be much smaller. So, I took on that task at the end of October and completed it at the end of December. The fanart database was 17 MB and is now 1.7 MB , (yes, one tenth of the size), with exactly the same input data. The music database is now half its original size. The video database is about the same size but I have normalized it so that there are at least twice the number of tables - at 10 MB. The key thing to understand is that the task of making the database multi-user required a complete re-write of the back-end and many, many changes throughout the system. This was not a trivial task and I think it will not be possible to embed these changes into the current system while preserving 100% of the previous functionality. I have been maintaining an upgrade path for the refactored system in parallel with the live system. The truth is that, in order to make the system multi-user, it is vital to control ALL database accesses through the Databases project and that all third-party products should call methods in the databases project to perform database tasks. I see this as a workable and feasible path, but it will require total commitment from the team to replace the current system with this solution and I am not convinced that that commitment is there yet. Therefore, I will continue to test and upgrade the refactored version for my own use. If anyone would like to use that version then I think it best that they regard it like a whole new product until, and if, it is taken on-board by the team. I have developed this system as a cut-down version of the live product that will work for multiple users in real time covering Video, Music, Pictures and Fanart. The Fanart is now very responsive without the previous delays and I have removed much of the hashtable behaviour - though I haven't been able to remove it completely yet. But, for the most part, the Fanart system is now data-driven; avoiding repeated reprocessing by analyzing existing databases to identify new data for which to retrieve fanart. I did some time-trialing of the scraping procedures and discovered that, with or without multi-threading, the processing took the same time pretty much to the second. So, I have removed multi-threading from the fanart application to minimized database clashes in a multi-user environment. All of this is why I have not been doing any posts. I will also not be writing any database upgrade code unless and until there is any demand for the refactored system. I have found that I can install this system against existing data, albeit video, music, pictures or fanart, from scratch very quickly. For me, the multi-user aspect makes it worthwhile. I appreciate that others may not feel the same way. If there is any demand for the refactored system then I will post the latest version and it's code. Bare in mind that the way I have implemented the system, you can install the live system and overwrite certain files from my system (batch file provided) - you must ensure that all of the client databases have been deleted first. The databases will build themselves and I recommend that the configuration application is used to import existing videos, music and pictures. If there is demand for the refactored system, then I will consider reworking the database upgrade code. However, having said that, I think that the new system approach is so different from the old system that it would be better to think of it as a different product - even though it looks exactly the same. The beauty of MP is that it works as a closed system and can be rebuilt from scratch quite easily - as I have done several times over the last few weeks. Tony [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 1
Development
General Development (no feature request here!)
Database: Refactor MP1 database for multi seat usage
Contact us
RSS
Top
Bottom