H2 database is better than Derby

[Former user]

Derby is the slowest embedded database... H2 is the evolution of HSQLDB. It offers fast and reliable multi-threaded DB, small footprint, few small files on disk, phpmyadmin-like manager etc...

See comparison : http://www.h2database.com/html/performance.html

I've tested with Serviio really simply :

put jar in classpath

Change 1 line in serviio.properties :

db_schema_url=jdbc:h2:file:_h2/serviioDB

[Petr Nejedly]

that is interesting. the problem is the installer would have to drop everyone's current DB's instead of just updating their current library.

[Former user]

Exactly... That's the drawback. The new DB can be in another directory (in my sample : _h2), so deleting would be easy. Furthermore the new DB will be more compact.

[Former user]

New installation for v0.6b3 :

1. add H2 jar in classpath.
2. Remove system property "derby.system.home" and Derby jar.
3. Remove/Comment the line : DriverManager.getConnection("jdbc:derby:;shutdown=true"); in DatabaseManager.stopDatabase() (H2 automatically close db when the last connection is closed).
4. Change dbUrl from jdbc:derby:db;create=true to jdbc:h2:file:./library2/serviio :
   1. Either "db_schema_url" in "serviio.properties"
   2. Or system property "dbUrl"
5. Update enclose H2-compliant "script-0.6.sql". Be careful, it will not work with Derby anymore...

Note1 : "jdbc:h2:file:./library2/serviio" means a db "serviio" inside relative dir "library2".

Note2 : there is a web console to administrate any JDBC db. Try these ;-) :

java -cp H2.jar org.h2.tools.Console -url "jdbc:derby:./library/db" 
java -cp H2.jar org.h2.tools.Console -url "jdbc:h2:file:./library2/serviio"

[Former user]

V1.0 is probably the time to update to H2 database

[Jerzy Kołosowski]

Serviio 1.4.1.2 sql update scripts patch for H2 database

[Jerzy Kołosowski]

Serviio 1.4.1.2 start script patch

[Former user]

I know this is on hold and can understand why but was just wondering why dropping everyones current DB was much of an issue? Surely a disclaimer stating that the initial scan will take longer and this being reason would be enough to sort that issue or am I missing something?

[Petr Nejedly]

It's also losing all the settings in the Console.

[Former user]

Ah okay makes sense. Does complicate things a bit more. The way I've seen other software do this before in the case of an underlying file system change was to phase it. For example using the new database for clean / fresh installs and offering an 'upgrade' that keeps all settings intact using the old database with some option in the installer. Obviously means more to maintain however.

I must admit i'm not an expert on databases but I think a way round it might be to convert the derby database to sql format and then convert the sql to H2 using a script of some sort. I remember reading sometime ago (found the link http://stackoverflow.com/questions/11359813/how-to-convert-h2-h2-db-file-database-file-to-mysql-database-sql-file-file) H2 having a SCRIPT statement that allows the second part.

I guess the other option would be to allow exporting and importing of settings but then again I guess there is the worry of compatibility between the 2 file formats.

Anyway just my two cents. I can see the benefits of using the H2 database format but then also the issues in changing over. Hope you can figure it out!

[Former user]

If the migration to a different database is indeed worth doing, I have some thoughts on this:

• There can't be that many console settings, right? Maybe these can be exported to a text file and then re-imported as part of the upgrade process? Or maybe a possible new feature might be an export/import console settings to/from text file backup. This idea might be worth looking at prior to any database migration as an intermediate step?

• There could be a warning that version x.x has major changes and that upgrading from versions prior to x.x will require an entire library rescan, which might take a long time.