SQLiteDatabase Plugin for MP2 (1 Viewer)

[MJGraf]

You only need the client to add the share. After that its enough if the server is running and the import will continue. In your SQL log I can see that the import was running. Nothing wrong in that log. Can you also upload the corresponding server.log (and if it exists also the server error.log?

 

[rickyman1977]

if it is everything is okay, why the series section empty

 

[MJGraf]

Well, then it's definitely not ok but now I need the server log from the import. Best is probably
1. Stop MP2 server service
2. Delete the database file in your programdata\team mediaportal\mp2 server directory (all your settings will be lost)
3. Start the mp2 server service
4. Start MP2 client
5. Attach your client to the server
6. Add (only) your series share (make sure you check "video" and "series" as type of your share)
7. Wait until you read something like "importworker finished" in your server log
8. Post full set of server logs here
Without the logs from the import I just can't see what happened

 

[rickyman1977]

can u please tell me what is wrong with it? so we can fix it together.

thank you

I did what you told me to do. I will wait for it. when I look inside the server log I got this msg:

[WARN ] - ImporterWorker: Problem processing 'ImportJob '{03dd2da6-4da8-4d3e-9e55-80e3165729a3}:////TVSHOW-PC''
MediaPortal.Common.Services.MediaManagement.ImportAbortException: Error in the application.
at MediaPortal.Common.Services.MediaManagement.ImporterWorker.CheckImportStillRunning(ImportJobState state)
at MediaPortal.Common.Services.MediaManagement.ImporterWorker.Process(ImportJob importJob)

is this okay or not?

 

[MJGraf]

Hi Rickyman,
Yepp, we'll solve that - I'm sure - just needs some time and maybe some try and error...

There are some things I don't understand, yet.

1. Is "TVSHOW-PC" a computer name or the name of a network share?
2. The exception you copied above is to my understanding not normal - and most likely the reason why you'll never get something like import job finished in your logs because the import job is aborted.
3. But the server log attached to your post does not contain this exception. Could you please post the server log in which this exception is contained?
4. The log above contains an exception about network name resolution. Is there something special with your network configuration?
Thanks, Michael

 

[MJGraf]

@morpheus_xx, @chefkoch

Hey guys!

I've cleaned up this thread a little bit and attached a new version to the first post - changelog also there.
In particular, I've removed the System.data.sqlite binaries from the source and used NuGet instead as per chefkoch's wiki instructions (very cool btw...). So as soon as we have merged chefkoch's NuGet branch into dev, this plugin is ready to be included in the Incubator (any help appreciated as always, since I have no clue about Git...)
Morpheus, if there's anything I can do to help with respect to TVE3.5 compatibility, just let me know (or tell me what to do...) I've never really used TVE3.5 so far...

To all the other MP2 users / testers: Please test and report anything you don't like (in particular bugs) but also please report if it just works...

cheers,
Michael

 

[Lehmden]

Hi.
Just upgraded my MP2 Server with new version of SQLite Plugin. All seems to be working as expected. Will use it on regular basis from now up to next version. If there are hidden bugs or other unwanted things I will come back and report here. Up to now all is fine.

 

[MJGraf]

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.

• 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

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 (@Lehmden 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).
@morpheus_xx: I saw your other thread re TVE3.5 and SQLite and also @gibman'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
Thanks,
Michael

 

[MJGraf]

...and just for reference:

I also tried to add SQLite's shared cache mode (http://www.sqlite.org/sharedcache.html) and SQLite's memory mapped i/o (http://www.sqlite.org/mmap.html) via the respective PRAGMA Statements in every SQLiteConnection to see, whether these have any positive impact on the speed of the SQLiteDatabase plugin (in particular with respect to share imports).

Both settings did not have any effect on speed at least from the tests I ran (i.e. importing large shares), so I removed the respective statements again.

Fot memory mapped i/o this may result from the fact that if you set e.g. a memory size to use for mapping of 512MB (which is then actually used in RAM for this), only the first 512MB of the database are mapped. For every database access beyond 512MB in your database, the file is accessed regularly, i.e. without memory mapping. My database is 1.7GB in size, so I also tried to use 2GB of my RAM for memory mapping (which is the maximum value for SQLite), but again no speed improvements (apart from the fact that using 2GB of RAM just for speed improvements in the database is a real overkill...)

Just wanted to mention this here for documentation purposes. At least from my perspective no need to investigate this any further...

Michael

 

[morpheus_xx]

@Lehmden, I think this issue probably explains why your missing files are not cleaned up properly by the importer. For me it always worked, but I didn't use SQLite yet.