SQLiteDatabase Plugin for MP2 (2 Viewers)

[MJGraf]

Edit 28.09.2013:
As of now, the source code is available on Github and the latest version of the binaries is available in our new MP2 Plugin Repository. There are no more source or binary files attached to this post.
---------------------------------------------

Dear MP2 community,

this thread is about the SQLiteDatabase plugin for MP2. I'm already using it on a daily basis and I have not found any bug since quite some time. If you have problems using the inbuilt SQLCEDatabase (in particular, if you hit the database size limit of SQLCE of 2GB, which can easily be the case if you have a big music collection tagged with a lot of covers), I encourage you to test the attached SQLiteDatabase. And please don't forget to post test results in this thread - even if the result is "no errors found, it just works"

cheers,
Michael

The SQLiteDatabase plugin is intended to replace the SQLCEDatabase, which is shipped as standard database for MP2. The reason for providing this database plugin is

• to have an inbuilt database system, which does not require to install an external database system (such as MySQL)
• and at the same time not to suffer from restriction imposed by the MS SQLCE database (such as the maximum database size limite of 2GB).

It uses the system.data.sqlite wrapper (system.data.sqlite.org) to provide an SQLite database (www.sqlite.org) for the MP2 server.

Note 1: This plugin has two big advantages over the standard SQLCEDatabase plugin:

• It does not have a size limit for the database.
• It is MUCH faster than the standard SQLCEDatabase plugin.

Note 2: But it also has one big disadvantage: This database plugin does not work with SlimTVNative (yet). So if you want to use SlimTVNative, do not use this database plugin!

Installation:
On your MP2-Server computer

• Stop your MP2 server
• In your MP2 server's plugin directory delete the "SQLCEDatabase" directory
• Instead copy the content of the SQLiteDatabase_BIN_v0.x.zip file attached to this thread in the plugin directory of your MP2 server
• To be on the save side also delete your [SystemDrive]:\ProgramData\Team MediaPortal\MP2-Server directory
• Start your MP2 server

On your MP2-Client computer(s):

• Quit MP2 client
• Delete your [SystemDrive]:\ProgramData\Team MediaPortal\MP2-client directory
• Restart MP2 client
• (Re)attach your MP2 client to the MP2 server
• (Re)add your shares

Please note that by the procedure described above your will lose all your MP2-settings and all your MP2-shares have to be reimported. For those of you, who know what you do, it may not be necessary to delete the ProgramData\[...] directories. Just want to make sure that tests are made on clean systems...

To do:

• Test, test and again test this database...
• Make sure that it is compatible with TVE3.5

ChangeLog:
v0.5

• This version is compatible with database files generated by v0.4, so for those of you already using SQLite, just replace the SQLiteDatabase directory in your MP2 server's plugin directory
• This version is compiled as debug binary (same as the previous versions)
• Attached separate ZIP files for compiled files (SQLiteDatabase_BIN_v0.x.zip) and source code (SQLiteDatabase_SRC_v0.x.zip) to make installation easier
• Removed system.data.sqlite binaries from source code and used NuGet instead
• Upgraded system.data.sqlite from version 1.0.85.0 to version 1.0.86.0
• Added lots of comments in the source code (in particular regarding the connection string parameters) to explain why the respective settings are necessary in the context of SQLite

v0.6

• Execute "PRAGMA foreign_key = on" in every SQLiteDatabaseConnection to ensure that SQLite enforces foreign key constraints, which are used by MP2's database backend to enforce referential integrity (Default for SQLite is to just ignore the respective SQL commands)
• Added a reference to system.database.sqlite.linq as well as the respective dll (via NuGet). MP2-Server does not need this, but TVE3.5 uses the Entity Framework, which (at least I suppose) needs linq support.

v0.7

• Updated System.Data.SQLite to 1.0.88.0
• Code Cleanups

-----------------------------------------
Original Thread:

Hi everybody,

Well, the title says it all - but unfortunately, it doesn't work completely, yet. I thought I could provide you with something, that works, but I'm running out of time. Will not have time to play with it for the next two or three weeks, but maybe someone wants to give it a try in the meantime.

The idea was born, because MSSQLCE has a database size limit of 2GB. MySQL does not have this size limit, but requires a database system being installed. SQLite does not have any of these limitations. It is completely embedded and allows for database sizes in the terabyte range.

Attached is the source code and a compiled binary. As mentioned, it doesn't really work, yet. As far as I can see, the import works correctly (tried with music files so far). After the import, the database looks perfectly to me. Unfortunately, the music files are not displayed in "audio" afterward. So something must be wrong with reading from the database.
So if someone feels like digging into some SQL debugging, please feel free
If not, I'll try to continue when I'm back with some more time...

Have fun!
Michael

 

[MJGraf]

I can't get my Hands off it

Then Problem seems to be that I get multiple tables in the database for the same MIA.

After freshly starting the MP2-Server, I only have two "double" tables:
V_GENRES
NM_GENRES
and
V_GENRES_0
NM_GENRES_0
That seems to be correct, because the first two are for the Genres from the Audio Aspect, the latter two for the Genres from the Video aspect.

Then I add a share with only one subdirectory with one MP3 Music file in it. The importer runs and suddenly, I have lots of double tables in my database.

In particular i have
M_AUDIOITEM
and
M_AUDIOITEM_0

While the data from my single MP3-file is stored in M_AUDIOITEM, the query, which later on tries to find the Audio items in the MediaLibrary seraches in M_AUDIOITEM_0 and therefore doesn't find anything.

It somehow seems like MP2-server thinks that the table for a certain MIA is still missing although it is already there and as a result, creates a further table for the same MIA.
Does anyone know how and where MP2-server checks, whether the database table for a MIA is already in the database? I thought it would use the TableExists-Method from ISQLDatabase, but this method is only called once in the whole MP2-project, which is in line 67 of the DatabaseManager:
if (!database.TableExists(MediaPortal_Basis_Schema.MEDIAPORTAL_BASIS_TABLE_NAME))
and this has nothing to do with MIAs. So the detection, whether a table for a certain MIA already exists, must work differently.
Any help appreciated..

Michael

 

[MJGraf]

Forget about the above.
After a complete rebuild and deleting all the ProgramData\Team Mediaportal Folders, it now works
Will make a test with my whole Audio library, which didn't work with SQLCE because of the size Limit. If it works with SQLite, I'll provide new binaries and source code...
Michael

 

[MJGraf]

Here we go!

Attached is a working version of the SQLiteDatabase - source code and debug binaries.
If you want to test, please remove your former database plugin (MSSQLCE or MySQL) and instead copy the attached SQLiteDatabase plugin in your MP2-Server's plugin directory.
No configuration needed at all. There will just be one database file in your ProgramData\Team Mediaportal\MP2-Server directory.

The database seems more efficient than the MSSQLCE database as regards database file size. My music collection consists of about 24k files, thoroughly tagged, each file with a (relatively) high res embedded cover. While the MSSQLCE database was not able to Import my whole Music collection (due to the 2GB size Limit), SQLite imported all the files and the database file size was still below 2GB.

I would therefore be grateful If someone with a REALLY big Music collection (@Lehmden ) could try if the Import works correctly with this database plugin, even when the database file size goes beyond 2GB.

I haven't done a speed test, yet, in comparison to MSSQLCE and MySQL. So this is just my feeling, but the feeling is that SQLite is really fast. Speed comparison tests are also welcome.

There is still a lot of room for improvement. In particular with respect to databse locking and connection handling (currently, I just set the timeout to 30s for the database connection. That's the same way that this is done in the MSSQLCE database plugin. This works, because no wuery should take longer than 30s - but there are surely better and even faster solutions.

Any input is welcome!

Michael

[Attachment removed - please use version from first post]

 

[MJGraf]

BTW: Import of my whole music collection (more than 24k files) took 42 minutes. That was with a debug build (i.e. there was also a 250MB SQLDebug.log written at the same time when importing). Database file size after the import was 1.8GB. Will try to find some more shares to Import.
No, wait, I have to grow my music collection for testing purposes

 

[morpheus_xx]

Great progress! One thing we need to make sure in future is, that SqlLite will work for TvLibrary as well. Currently there is a feature that creates a similar connection for TvLibrary, based on MP2 database provider.

We need to create a model file and connect string for SqlLite inside TvLibrary project and add support for connect string mappings.

 

[Lehmden]

Hi.
I will test it asap I'm done with the other test (Network Share)

 

[MJGraf]

TVLibrary Integration shouldn't be a problem - SQLite seems to be easy to handle.

What I noticed is that while there is a big import running, I was once not able to add another share. The reason was that the thread, which tried to insert the new share into the database, was not able to acquire an exclusive share necessary for the write operation within the specified timeout of 30 sec. I'm not entirely sure where this comes from. I read somewhere that SQLite, if it is not able to acquire a lock from a different connection than the one holding the current lock, waits 1 sec until it tries again. When there is another thread (here the importer) with another connection, making heavily use of locks, the first thread only has 30 tries until the timeout is reached and a "database locked" exception is thrown. What I also read is that it is not recommended to have too many different connections to the same database. Maybe a solution is to have only one connection within the database object, which is then used for different transactions. As far as I understand, in this case the 1 s wait rule does not apply and locking is handled "internally" within the single connection. That would on the other hand require a lock within the database object with respect to the access of the single connection object.
Will try as soon as I have some more time.

 

[MJGraf]

Hi everybody,
ok, I still have to time to code, but I do it anyway - it's just too much fun
New version attached.

Changes:
I now use SQLiteConnectionStringBuilder instead of concatenating the connection string manually. Makes the code more readyble and is said to keep compatibility if we want to upgrade syste.data.sqlite.dll later while they changed something in the connection string.
Besides that, I didn't change the SQLiteDatabase class behaviour, I just changed some things in the connection string to improve the behaviour of the SQLite database.
Most important changes are as follows:

BinaryGUID=true
GUIDs are now stored as binaries, not as string. Saves some space in the database and is said to make search queries on GUIDs faster.

PageSize=4096
I read that on NTFS this gives some speed improvements because the standard NTFS cluster size is also 4096kb.
Furthermore, I remember we had some discussion about storing BLOBs (in our case mostly pictures such as covers, etc.) in the database or separately in a file system structure. I found an interesting article here: http://www.sqlite.org/intern-v-extern-blob.html which also has a speed comparison. According to this article, with a pagesize of 4096kb, it is faster to store BLOBs of up to 50kb in the database (With a pagesize of 8192 this even applies to BLOBs up to 100kb). I assume that most of our BLOBs, such as covers, are of a size up to 50 or 100kb. Maybe we should also try pagesize 8192 to see whether this gives further speed improvements.

JournalMode=WAL
WAL means "write ahead log". This is probably the most importand change in this build. You can read more about it here: http://www.sqlite.org/wal.html
The improvement with this mode is mainly that write locks do not block reads anymore. As a result, there are much fewer situations, in which one connection to the database is blocked by another connection. This means that we have to rely less often on the timeout of 30 sec. mentioned above. Anyway, I didn't have the situation anymore, which I mentioned above and in which I got timeout exceptions when adding a share while a big import was running. The whole system seemed to be even more fluid.
The only disadvantage I can see is that we may have three database files instead of one (1. the database itself, 2. the journal log file and 3. an index file for the journal log).

The overall result is that these new settings are already an improvement for me. I did the same import as above (24k music files) which now only took 36 minutes instead of 42 minutes and the database file was about 50MB smaller (ok the latter one is not that big of an improvement...).

What I still would like to try is to use just a single connection to the database, which is then opened on MP2-Server start and remains open until the server is shut down. All the transactions later on are then based on this single connection. I assume that opening and closing the connection again and again takes a lot of time and we may see further speed improvements by this.

Tests are still welcome
Michael

[Attachment removed - please use version from first post]

 

[Lehmden]

Hi.
I've imported my whole music collection using V0.1. My first impressions are, it's fast on import (about 500 Albums per minute) but it needs way too much CPU. While this import is running I got 100% CPU Load. After Import was finished I got about 70 to 80% CPU Load when in Audio Section of MP2. This is leading MP2 to behave like MP1 (slow responding, stuttering GUI, lots of freezes...) During Import MP2 isn't really usable at all. Maybe this is something special on my system as I think most performance issues I have with MP1 are also SQLite related. But I know other software (you know, the "bad boy starting with X" ) can handle this without issues here...

Is it possible to lower the priority of the whole process, so GUI is reacting as usual? Or what else could we do to decrease the CPU Load?.

BTW. also I have a lot more audio tracks than you I don't have such a huge db file. It's only about 400 MB in size. Manly because I don't have much Artwork tagged inside my mp3 files...

Is the db file compatible between V0.1 and V0.2?