SQLiteDatabase Plugin for MP2 (1 Viewer)

[morpheus_xx]

I found a bug with SQLite in the audio section: filter by "decade" (Jahrzehnt) does not work, there is a type conversion exception logged in server:

[2013-10-27 10:02:48,421] [221447 ] [14 ] [WARN ] - SOAPHandler: Error invoking UPnP action 'GetValueGroups'
System.InvalidCastException: Die angegebene Umwandlung ist ungültig.
bei System.Data.SQLite.SQLiteDataReader.VerifyType(Int32 i, DbType typ)
bei System.Data.SQLite.SQLiteDataReader.GetInt32(Int32 i)
bei MediaPortal.Backend.Services.Database.DBUtils.ReadSimpleDBValue(Type type, IDataReader reader, Int32 colIndex) in d:\Coding\MP\MP2\MP2_git\MediaPortal-2\MediaPortal\Source\Core\MediaPortal.Backend\Services\Database\DBUtils.cs:Zeile 172.
bei MediaPortal.Database.SQLite.SQLiteDatabase.ReadDBValue(Type type, IDataReader reader, Int32 colIndex)
bei MediaPortal.Backend.Services.MediaLibrary.QueryEngine.CompiledGroupedAttributeValueQuery.Execute() in d:\Coding\MP\MP2\MP2_git\MediaPortal-2\MediaPortal\Source\Core\MediaPortal.Backend\Services\MediaLibrary\QueryEngine\CompiledGroupedAttributeValueQuery.cs:Zeile 155.
bei MediaPortal.Backend.Services.MediaLibrary.MediaLibrary.GetValueGroups(AttributeSpecification attributeType, IFilter selectAttributeFilter, ProjectionFunction projectionFunction, IEnumerable`1 necessaryMIATypeIDs, IFilter filter, Boolean filterOnlyOnline) in d:\Coding\MP\MP2\MP2_git\MediaPortal-2\MediaPortal\Source\Core\MediaPortal.Backend\Services\MediaLibrary\MediaLibrary.cs:Zeile 574.
bei MediaPortal.Backend.Services.ClientCommunication.UPnPContentDirectoryServiceImpl.OnGetValueGroups(DvAction action, IList`1 inParams, IList`1& outParams, CallContext context) in d:\Coding\MP\MP2\MP2_git\MediaPortal-2\MediaPortal\Source\Core\MediaPortal.Backend\Services\ClientCommunication\UPnPContentDirectoryServiceImpl.cs:Zeile 988.
bei UPnP.Infrastructure.Dv.DeviceTree.DvAction.FireActionInvoked(IList`1 inParams, IList`1& outParams, CallContext context) in d:\Coding\MP\MP2\MP2_git\MediaPortal-2\MediaPortal\Source\Core\UPnP\Infrastructure\Dv\DeviceTree\DvAction.cs:Zeile 146.
bei UPnP.Infrastructure.Dv.DeviceTree.DvAction.InvokeAction(IList`1 inParameters, IList`1& outParameters, Boolean checkSignature, CallContext context) in d:\Coding\MP\MP2\MP2_git\MediaPortal-2\MediaPortal\Source\Core\UPnP\Infrastructure\Dv\DeviceTree\DvAction.cs:Zeile 129.
bei UPnP.Infrastructure.Dv.SOAP.SOAPHandler.HandleRequest(DvService service, Stream messageStream, Encoding streamEncoding, Boolean subscriberSupportsUPnP11, CallContext context, String& result) in d:\Coding\MP\MP2\MP2_git\MediaPortal-2\MediaPortal\Source\Core\UPnP\Infrastructure\Dv\SOAP\SOAPHandler.cs:Zeile 155.

 

[MJGraf]

Thanks! Bug confirmed and solved...
We treat the result of the DateToYearProjectionExpression as an INT and therefore try to read it with GetInt32. However, the SQLite command strftime returns a TEXT Value. SQLite refuses to convert TEXT into INTEGER automatically and so we have to make an explicit CAST in the respective SQL statement returned by SQLiteDatabase.CreateDateToYearProjectionExpression. Just a one line change already done and tested...

I somehow knew they would publish the new version of system.data.sqlite when I don't really have time... So what, I just did the update as well. Test is running. When everything goes well, I'll publish v0.08 Beta 2 within the next hour or so. Included changes:

• Resolve the conversion exception reported by Morpheus above
• Update to sytem.data.sqlite 1.0.89.0

 

[MJGraf]

There we are. Attached is SQLiteDatabase v0.08 Beta 2. Changes mentioned in last post.
Oh and I knew we would get there. The guys from SQLite did a great job with there next generation query optimizer.
Import Test: 19:17 - without any additional optimizations
Have fun and most importantly: test it until it breaks...
Michael

 

[MaxMan23]

Hi MJGraf,
did this work with native TV together?

 

[MJGraf]

Hi MaxMan23,
unfortunately not (yet). The guys from system.data.sqlite need to implement some missing methods for EntityFramework - unfortunately this doesn't seem to be really high on their priority list. I will have a look, whether we can implement that on our own as soon as v0.08 final is out.
So stay tuned...
Michael

 

[MJGraf]

To have a comparison, I did a complete run of tests as per the changed test protocol above for v0.07 and v0.08 Beta 2. Results as follows:

SQLiteDatabase (v0.07):
Import Test: 31:38
Read Test 1
Read Time: 11984 ms, 11945 ms (23929 ms)
Read Test 2
Read Time: 188 ms, 190 ms (378 ms)
Read Test 3
Read Time: 6.4ms ms, 8.2 ms (14.6 ms)
Read Test 4
Read Time: 93.0 ms, 81.1 ms (174.1 ms)

SQLiteDatabase (v0.08 Beta 2):
Import Test: 19:17
Read Test 1
Read Time: 11193 ms, 10926 ms (22119 ms)
Read Test 2
Read Time: 201 ms, 196 ms (397 ms)
Read Test 3
Read Time: 5.9ms ms, 5.1 ms (11 ms)
Read Test 4
Read Time: 31.8 ms, 79.1 ms (110.9 ms)

Comparison SQLiteDatabase (v0.07) vs. SQLiteDatabase (v0.08 Beta 2):
Import Test: 164%
Read Test 1
Read Time: 108%
Read Test 2
Read Time: 95%
Read Test 3
Read Time: 133%
Read Test 4
Read Time: 157%

This means, v0.08 Beta 2 is 1.64 times as fast as v0.07 on imports (i.e. writes).
For reads we have no significant changes for Read Test 1 and 2 (both below 10%), but significant improvements for ReadTests 3 and 4 (the ones where we actually read covers from the database).

Not really bad. But my suspicion is that it will become more and more difficult to achieve further such big improvements by tweaking the database. I think when v0.08 is out, it is really time to make SQLite compatible with SlimTV Native. Seems more important to me now than getting some percent of further speed improvements (and I finally want to use SlimTV Native ).

 

[morpheus_xx]

I created a test installer, using SQLite as default DB. I found some issues:

1. IRC bot fails to compile SQLite plugin due to missing dependencies. Local there was a missing reference and NuGet prompted to restore missing packages. After this action, references were ok. Build log is attached.
2. After manually adding SQLite to installed folder and restarting the computer, I found this error in log:

[2013-11-01 17:10:22,169] [61127  ] [Main    ] [ERROR] - SettingsManager: Error loading settings of type 'SQLiteSettings'
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException: Aufruf wurde durch Messagefilter abgebrochen. (Exception from HRESULT: 0x80010002 (RPC_E_CALL_CANCELED))
  at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
  at System.Management.ManagementScope.InitializeGuts(Object o)
  at System.Management.ManagementScope.Initialize()
  at System.Management.ManagementObjectSearcher.Initialize()
  at System.Management.ManagementObjectSearcher.Get()
  at MediaPortal.Database.SQLite.SQLiteSettings.GetRamInMegaBytes()
  at MediaPortal.Database.SQLite.SQLiteSettings..ctor()
  --- End of inner exception stack trace ---
  at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck)
  at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache)
  at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean skipCheckThis, Boolean fillCache)
  at System.Activator.CreateInstance(Type type, Boolean nonPublic)
  at MediaPortal.Common.Services.Settings.SettingsManager.LoadSettingsObject(Type settingsType)
[2013-11-01 17:10:22,181] [61139  ] [Main    ] [FATAL] - SQLiteDatabase: Error establishing database connection
System.NullReferenceException: Object reference not set to an instance of an object.
  at MediaPortal.Database.SQLite.SQLiteDatabase..ctor()
[2013-11-01 17:10:22,182] [61140  ] [Main    ] [ERROR] - PluginManager: Error building plugin item 'SQLiteDatabase' at location '/Services'
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.NullReferenceException: Object reference not set to an instance of an object.
  at MediaPortal.Database.SQLite.SQLiteDatabase..ctor()
  --- End of inner exception stack trace ---
  at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck)
  at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache)
  at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean skipCheckThis, Boolean fillCache)
  at System.Activator.CreateInstance(Type type, Boolean nonPublic)
  at MediaPortal.Common.PluginManager.PluginRuntime.InstantiatePluginObject(String typeName)
  at MediaPortal.Common.Services.PluginManager.Builders.ServiceBuilder.BuildItem(PluginItemMetadata itemData, PluginRuntime plugin)
  at MediaPortal.Common.Services.PluginManager.PluginManager.RequestItem(PluginItemRegistration itemRegistration, Type type, IPluginItemStateTracker stateTracker)

Edit:

the exception occured after reboot / autostart of server. In the 2nd attempt (manual start of server) it worked ok:

[2013-11-01 17:33:29,577] [744    ] [Main    ] [INFO ] - SQLiteDatabase: Database Filename: 'Datastore.s3db' (Default Database Filename: 'Datastore.s3db')
[2013-11-01 17:33:29,578] [745    ] [Main    ] [INFO ] - SQLiteDatabase: PageSize: 4096 Bytes (Default PageSize: 4096 Bytes)
[2013-11-01 17:33:29,635] [802    ] [Main    ] [INFO ] - SQLiteDatabase: CacheSize: 65536 pages = 262144KB (RAM: 4094MB, Default CacheSize: 262144KB)
[2013-11-01 17:33:29,635] [802    ] [Main    ] [INFO ] - SQLiteDatabase: LockTimeout: 30000ms (Default LockTimeout: 30000ms)
[2013-11-01 17:33:29,636] [803    ] [Main    ] [INFO ] - SQLiteDatabase: Initialization Command: 'PRAGMA locking_mode=EXCLUSIVE;PRAGMA wal_autocheckpoint=32768;PRAGMA temp_store=MEMORY;' (Default Initialization Command: 'PRAGMA locking_mode=EXCLUSIVE;PRAGMA wal_autocheckpoint=32768;PRAGMA temp_store=MEMORY;')
[2013-11-01 17:33:29,653] [820    ] [Main    ] [DEBUG] - SQLiteDatabase: URI used in connection string: 'file:///C:/ProgramData/Team%20MediaPortal/MP2-Server/Datastore.s3db?cache=shared'
[2013-11-01 17:33:29,691] [858    ] [Main    ] [INFO ] - SQLiteDatabase: Connection String used: 'fulluri="file:///C:/ProgramData/Team%20MediaPortal/MP2-Server/Datastore.s3db?cache=shared";version=3;binaryguid=True;default timeout=30000;cache size=65536;journal mode=Wal;pooling=False;synchronous=Normal;foreign keys=True'
[2013-11-01 17:33:30,607] [1774  ] [Main    ] [DEBUG] - SQLiteDatabase: 1 connections in use

Edit 2:
I think the Management query tried to access informations that were not yet ready on service startup time. I found another way to query available RAM:
http://www.dotnetspider.com/resources/4612-Find-Memory-usage-CPU-usage.aspx

 

[MJGraf]

Thanks, Morph!

IRC-Bot failing to build:
I have to admit that I have no idea why this happens. Sorry, I'm not familiar enough with our build process. What I see from your BuildLog, however, is this:

  "d:\github\MediaPortal-2\MediaPortal\Build\\NuGet.exe" install "d:\github\MediaPortal-2\MediaPortal\Incubator\SQLiteDatabase\packages.config"
  Restoring NuGet packages...
  To prevent NuGet from downloading packages during build, open the Visual Studio Options dialog, click on the Package Manager node and uncheck 'Allow NuGet to download missing packages'.
  All packages listed in packages.config are already installed.

The packages.config that is mentioned here looks like this:

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="System.Data.SQLite.x86" version="1.0.89.0" targetFramework="net40" />
</packages>

So my understanding is that it looked up System.Data.SQLite.x86 version 1.0.89.0 and realized that it is already there and doesn't have to be downloaded.

Later it says in the log:

Project "d:\github\MediaPortal-2\MediaPortal\Source\MP2-Server.sln" (59) is building "d:\github\MediaPortal-2\MediaPortal\Incubator\SQLiteDatabase\SQLiteDatabase.csproj" (65) on node 1 (Rebuild target(s)).
ResolveAssemblyReferences:
  Primary reference "System.Data.SQLite".
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Microsoft.Common.targets(1360,9): warning MSB3245: Could not resolve this reference. Could not locate the assembly "System.Data.SQLite". Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors. [d:\github\MediaPortal-2\MediaPortal\Incubator\SQLiteDatabase\SQLiteDatabase.csproj]
          For SearchPath "{HintPathFromItem}".
          Considered "..\..\Source\packages\System.Data.SQLite.x86.1.0.89.0\lib\net40\System.Data.SQLite.dll", but it didn't exist.

So here it complains that system.data.sqlite.dll is not there. But why the heck did it say above that it was there and didn't have to be downloaded?!?
Think I need help with this one..

Exception on boot:
This is a nasty one
I use ManagementObjectSearcher to find out the overall RAM of the system. But as I learned now, ManagementObjectSearcher needs the Windows WMI service to be started. So when MP2 Server Service - on startup - starts before the WMI service, we get this exception. When MP2 Server Service is started manually later on, WMI service is already started and it works...
I think the cleanest solution for this is to make a direct PInvoke to GlobalMemoryStatusEx (see here: http://www.emoticode.net/c-sharp/get-total-and-free-ram-on-a-computer.html). Additionally I would wrap it into a try-catch-block and if we get an exception at this point, we just use the lowest value (32MB) for the cache size of SQlite. In any case, a problem in determining the total available RAM shouldn't prevent SQLiteDatabase from starting...
Will try the PInvoke solution and report back...

 

[MJGraf]

Attached is v0.08 Beta 3.
Only change is to use another way to get the total RAM of MP2 Server in order to avoid Morpheus' exception on reboot. I cannot reproduce the exception anymore.
@morpheus_xx could you please try this one?

Unfortunately I still don't know what's wrong with the build process...

 

[breese]

@morpheus_xx What is the sequence to dulpicate, also seperate client of client/server install?