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 2
Plugin Development
SQLiteDatabase Plugin for MP2
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="MJGraf" data-source="post: 1004954" data-attributes="member: 17886"><p>Hi everybody,</p><p> </p><p>I found a bug in v0.5, which is now solved in v0.6:</p><p>When I read through the code of the MediaLibrary and in particular MIA_Management, I realized that the DatabaseBackend uses foreign key constraints to ensure referential integrity (such as "ON DELETE CASCADE"). This is supported by SQLite, but by default switched off, so that the respective commands do not throw an error, but are just ignored.</p><ul> <li data-xf-list-type="ul">The result of this bug could be observed directly in MP2. Steps to reproduce with v0.5:</li> <li data-xf-list-type="ul">Add a share and wait for the share to be imported</li> <li data-xf-list-type="ul">Check your mediafiles to be present in Audio/Video/Movies/Pictures or whatever type of share you added</li> <li data-xf-list-type="ul">Remove the share</li> <li data-xf-list-type="ul">Go back and realize that the mediaitems are still present and have not been deleted from the database</li> </ul><p>This was caused by the share being deleted in the database, but the mediaitems of this share have not been deleted, because this should be taked care of by the database itself via foreign key constraints.</p><p>If you do the same with v0.6, your mediaitems of the deleted share should be gone after deleting the share (at least this is what happened for me in v0.6).</p><p> </p><p>As to the speed, I was a little be scared, because oviously we need this setting in order to SQLite function properly and keeping track of referential integrity may not be the easiest task. However, SQLite seems to be extremely efficient in this respect. Importing my reference music collection now took about 35 minutes instead of 32 minutes with v0.5. So there is a little bit of impact on the speed, but it is by far not as much as I feared. We are still nearly 3x as fast as SQLCE (about 96 minutes).</p><p> </p><p>v0.6 should be compatible with a database generated by v0.5 for those of you who do not want to reimport your shares too often (@<a href="https://forum.team-mediaportal.com/members/lehmden.109222/" target="_blank">Lehmden</a> <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite2" alt=";)" title="Wink ;)" loading="lazy" data-shortname=";)" /> I have not tested this, but I see no reason why it should not work and at least it will not harm the database). But please note that v0.6 will not take care of removing the leftovers of v0.5 from the database resulting from the missing support of foreign key constraints, i.e. if you have previously removed a share and the respective mediaitems have not been deleted in the database, they will still be in your database when using v0.6. I fear getting rid of those would require a fresh import of your shares. Using v0.6 only makes sure that referential integrity will be ensured for anything you do in the future.</p><p> </p><p>The second change of v0.6 is that I added a reference to system.data.sqlite.linq and of course also the file syste.data.sqlite.linq.dll (only in binaries, source code is still using NuGet) to the SQLiteDatabase project. I suppose this is necessary to make SQLiteDatabase compatible with TVE3.5, which uses the Entity Framework (which in turn, as I suppose, uses Linq).</p><p>@<a href="https://forum.team-mediaportal.com/members/morpheus_xx.48495/" target="_blank">morpheus_xx</a>: I saw your other thread re TVE3.5 and SQLite and also @<a href="https://forum.team-mediaportal.com/members/gibman.25430/" target="_blank">gibman</a>'s reply. Thanks a lot to both of you for pushing this forward. I hope you did not have to make changes to my source code already. But if so, the change above regarding the foreign key constraints is just adding 3 lines of code in SQLiteTransaction.cs plus 3 lines of comments.</p><p> </p><p>Please keep on testing <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite8" alt=":D" title="Big Grin :D" loading="lazy" data-shortname=":D" /></p><p>Thanks,</p><p>Michael</p></blockquote><p></p>
[QUOTE="MJGraf, post: 1004954, member: 17886"] Hi everybody, I found a bug in v0.5, which is now solved in v0.6: When I read through the code of the MediaLibrary and in particular MIA_Management, I realized that the DatabaseBackend uses foreign key constraints to ensure referential integrity (such as "ON DELETE CASCADE"). This is supported by SQLite, but by default switched off, so that the respective commands do not throw an error, but are just ignored. [LIST] [*]The result of this bug could be observed directly in MP2. Steps to reproduce with v0.5: [*]Add a share and wait for the share to be imported [*]Check your mediafiles to be present in Audio/Video/Movies/Pictures or whatever type of share you added [*]Remove the share [*]Go back and realize that the mediaitems are still present and have not been deleted from the database [/LIST] This was caused by the share being deleted in the database, but the mediaitems of this share have not been deleted, because this should be taked care of by the database itself via foreign key constraints. If you do the same with v0.6, your mediaitems of the deleted share should be gone after deleting the share (at least this is what happened for me in v0.6). As to the speed, I was a little be scared, because oviously we need this setting in order to SQLite function properly and keeping track of referential integrity may not be the easiest task. However, SQLite seems to be extremely efficient in this respect. Importing my reference music collection now took about 35 minutes instead of 32 minutes with v0.5. So there is a little bit of impact on the speed, but it is by far not as much as I feared. We are still nearly 3x as fast as SQLCE (about 96 minutes). v0.6 should be compatible with a database generated by v0.5 for those of you who do not want to reimport your shares too often (@[URL='https://forum.team-mediaportal.com/members/lehmden.109222/']Lehmden[/URL] ;) I have not tested this, but I see no reason why it should not work and at least it will not harm the database). But please note that v0.6 will not take care of removing the leftovers of v0.5 from the database resulting from the missing support of foreign key constraints, i.e. if you have previously removed a share and the respective mediaitems have not been deleted in the database, they will still be in your database when using v0.6. I fear getting rid of those would require a fresh import of your shares. Using v0.6 only makes sure that referential integrity will be ensured for anything you do in the future. The second change of v0.6 is that I added a reference to system.data.sqlite.linq and of course also the file syste.data.sqlite.linq.dll (only in binaries, source code is still using NuGet) to the SQLiteDatabase project. I suppose this is necessary to make SQLiteDatabase compatible with TVE3.5, which uses the Entity Framework (which in turn, as I suppose, uses Linq). @[URL='https://forum.team-mediaportal.com/members/morpheus_xx.48495/']morpheus_xx[/URL]: I saw your other thread re TVE3.5 and SQLite and also @[URL='https://forum.team-mediaportal.com/members/gibman.25430/']gibman[/URL]'s reply. Thanks a lot to both of you for pushing this forward. I hope you did not have to make changes to my source code already. But if so, the change above regarding the foreign key constraints is just adding 3 lines of code in SQLiteTransaction.cs plus 3 lines of comments. Please keep on testing :D Thanks, Michael [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 2
Plugin Development
SQLiteDatabase Plugin for MP2
Contact us
RSS
Top
Bottom
