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: 988646" data-attributes="member: 17886"><p><u>Edit 28.09.2013:</u></p><p>As of now, the source code is available on <a href="https://github.com/MediaPortal/MediaPortal-2/commits/FEAT_SQLiteDatabase" target="_blank">Github </a>and the latest version of the binaries is available in our new <a href="https://www.team-mediaportal.com/extensions/mp2-plugins" target="_blank">MP2 Plugin Repository</a>. There are no more source or binary files attached to this post.</p><p>---------------------------------------------</p><p></p><p>Dear MP2 community,</p><p></p><p>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" <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite2" alt=";)" title="Wink ;)" loading="lazy" data-shortname=";)" /></p><p></p><p>cheers,</p><p>Michael</p><p></p><p>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</p><ul> <li data-xf-list-type="ul">to have an inbuilt database system, which does not require to install an external database system (such as MySQL)</li> <li data-xf-list-type="ul">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).</li> </ul><p>It uses the system.data.sqlite wrapper (system.data.sqlite.org) to provide an SQLite database (<a href="http://www.sqlite.org" target="_blank">www.sqlite.org</a>) for the MP2 server.</p><p></p><p><strong>Note 1: </strong>This plugin has two big advantages over the standard SQLCEDatabase plugin:</p><ul> <li data-xf-list-type="ul">It does not have a size limit for the database.</li> <li data-xf-list-type="ul">It is <u><strong>MUCH </strong></u>faster than the standard SQLCEDatabase plugin.</li> </ul><p><strong>Note 2:</strong> But it also has one big disadvantage: This database plugin does not work with SlimTVNative (yet). <strong>So if you want to use SlimTVNative, do not use this database plugin!</strong></p><p></p><p><u><strong>Installation:</strong></u></p><p>On your MP2-Server computer</p><ul> <li data-xf-list-type="ul">Stop your MP2 server</li> <li data-xf-list-type="ul">In your MP2 server's plugin directory delete the "SQLCEDatabase" directory</li> <li data-xf-list-type="ul">Instead copy the content of the SQLiteDatabase_BIN_v0.x.zip file attached to this thread in the plugin directory of your MP2 server</li> <li data-xf-list-type="ul">To be on the save side also delete your [SystemDrive]:\ProgramData\Team MediaPortal\MP2-Server directory</li> <li data-xf-list-type="ul">Start your MP2 server</li> </ul><p>On your MP2-Client computer(s):</p><ul> <li data-xf-list-type="ul">Quit MP2 client</li> <li data-xf-list-type="ul">Delete your [SystemDrive]:\ProgramData\Team MediaPortal\MP2-client directory</li> <li data-xf-list-type="ul">Restart MP2 client</li> <li data-xf-list-type="ul">(Re)attach your MP2 client to the MP2 server</li> <li data-xf-list-type="ul">(Re)add your shares</li> </ul><p>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...</p><p></p><p><u><strong>To do:</strong></u></p><ul> <li data-xf-list-type="ul">Test, test and again test this database...</li> <li data-xf-list-type="ul">Make sure that it is compatible with TVE3.5</li> </ul><p><u><strong>ChangeLog:</strong></u></p><p>v0.5</p><ul> <li data-xf-list-type="ul">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</li> <li data-xf-list-type="ul">This version is compiled as debug binary (same as the previous versions)</li> <li data-xf-list-type="ul">Attached separate ZIP files for compiled files (SQLiteDatabase_BIN_v0.x.zip) and source code (SQLiteDatabase_SRC_v0.x.zip) to make installation easier</li> <li data-xf-list-type="ul">Removed system.data.sqlite binaries from source code and used NuGet instead</li> <li data-xf-list-type="ul">Upgraded system.data.sqlite from version 1.0.85.0 to version 1.0.86.0</li> <li data-xf-list-type="ul">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</li> </ul><p>v0.6</p><ul> <li data-xf-list-type="ul">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)</li> <li data-xf-list-type="ul">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.</li> </ul><p>v0.7</p><ul> <li data-xf-list-type="ul">Updated System.Data.SQLite to 1.0.88.0</li> <li data-xf-list-type="ul">Code Cleanups</li> </ul><p></p><p>-----------------------------------------</p><p>Original Thread:</p><p></p><p>Hi everybody,</p><p></p><p>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.</p><p></p><p>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.</p><p></p><p>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.</p><p>So if someone feels like digging into some SQL debugging, please feel free <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite2" alt=";)" title="Wink ;)" loading="lazy" data-shortname=";)" /></p><p>If not, I'll try to continue when I'm back with some more time...</p><p></p><p>Have fun!</p><p>Michael</p></blockquote><p></p>
[QUOTE="MJGraf, post: 988646, member: 17886"] [U]Edit 28.09.2013:[/U] As of now, the source code is available on [URL='https://github.com/MediaPortal/MediaPortal-2/commits/FEAT_SQLiteDatabase']Github [/URL]and the latest version of the binaries is available in our new [URL='https://www.team-mediaportal.com/extensions/mp2-plugins']MP2 Plugin Repository[/URL]. 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 [LIST] [*]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). [/LIST] It uses the system.data.sqlite wrapper (system.data.sqlite.org) to provide an SQLite database ([URL='http://www.sqlite.org']www.sqlite.org[/URL]) for the MP2 server. [B]Note 1: [/B]This plugin has two big advantages over the standard SQLCEDatabase plugin: [LIST] [*]It does not have a size limit for the database. [*]It is [U][B]MUCH [/B][/U]faster than the standard SQLCEDatabase plugin. [/LIST] [B]Note 2:[/B] But it also has one big disadvantage: This database plugin does not work with SlimTVNative (yet). [B]So if you want to use SlimTVNative, do not use this database plugin![/B] [U][B]Installation:[/B][/U] On your MP2-Server computer [LIST] [*]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 [/LIST] On your MP2-Client computer(s): [LIST] [*]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 [/LIST] 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... [U][B]To do:[/B][/U] [LIST] [*]Test, test and again test this database... [*]Make sure that it is compatible with TVE3.5 [/LIST] [U][B]ChangeLog:[/B][/U] v0.5 [LIST] [*]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 [/LIST] v0.6 [LIST] [*]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. [/LIST] v0.7 [LIST] [*]Updated System.Data.SQLite to 1.0.88.0 [*]Code Cleanups [/LIST] ----------------------------------------- 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 [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 2
Plugin Development
SQLiteDatabase Plugin for MP2
Contact us
RSS
Top
Bottom