Choose database engine ?

[Cerberus]

derby is just as easy to edit as mysql ( http://www.razorsql.com/docs/derby_data ... owser.html ) and all of your examples are aimed around linux and most of our user are not using linux.

Not as easy, since those tools have a GUI. Users on headless machines (like NAS) can't use those, unless they open the database on a share or something like that. They will also have to stop Serviio, or the database will be locked. Maybe you guys know if there is a CLI version of a derby database editor? I couldn't find one.

I got the point though, and it's fine. This tiny downside doesn't weigh up against the excellent DLNA server Serviio is.

Edit: Or is 'ij' the CLI tool to use?

look on the site i posted they do alot of things for derby databases. or look at the below links.

http://db.apache.org/derby/faq.html or http://www.theregister.co.uk/2006/11/08 ... ase_derby/


FYI - yes ij is the command line editor for derby

[wagstaff]

Cerberus wrote:
derby is just as easy to edit as mysql ( http://www.razorsql.com/docs/derby_data ... owser.html )

And for those of us who prefer free software over paying for software licenses, there is SQuirreL SQL:
http://www.squirrelsql.org/

With instructions for making it work with Derby:
http://db.apache.org/derby/integrate/SQuirreL_Derby.html

As well as a few others choices:
http://wiki.apache.org/db-derby/UsesOfDerby#GUI_Tools

And of course the previously mentioned "ij" Command Line Interface bundled with all versions of Derby for those operating in a "graphically-challenged" environment.

So, generally speaking, tools abound. Are they as good/fast/feature-rich as tools available for MySQL and others? That judgment is left to the reader.

[Cerberus]

nice summary wagstaff

[kairoh]

Derby or H2 as default database seems to be more simple for common users and you don't have to take care of the OS Serviio is running in.
But installing MySQL isn't quite difficult on Windows, and comes as standard with Linux (more than H2 or Derby, or others...).

"Embeddable database" like Derby, H2 or HSQLDB aren't simpler than "classic database" like MySQL, Sybase or Oracle : all of them use SQL (more or less) and offer lot of functions.
The first ones are coded in Java and can be run within a Java application (like Serviio) - with no installation at all.
They're generally smaller, faster but not really robust, especially with a lots of users.
For an application like Serviio these are definitely the best match.
And I don't want to have to launch many service before the one I want...

PS: I don't work for H2. I have used a lot of db : all of them have pros and cons, it depends of every application need.

[kairoh]

derby is just as easy to edit as mysql ( http://www.razorsql.com/docs/derby_data ... owser.html ) and all of your examples are aimed around linux and most of our user are not using linux.

Not as easy, since those tools have a GUI. Users on headless machines (like NAS) can't use those, unless they open the database on a share or something like that. They will also have to stop Serviio, or the database will be locked. Maybe you guys know if there is a CLI version of a derby database editor? I couldn't find one.

H2 comes with a "PhpMyAdmin-like" console able to manage any JDBC database (with correct driver).
I've successfully used it to manage my Serviio's databases (I am currently switching between : Derby or H2).
Indeed the main drawback is that you have to stop Serviio for editing the db (but I think this may be fixed with some option).

[Cerberus]

nice to know now that puts H2 ahead in the comparison as it means we can edit it and maybe taylor the admin code to allow us to make a custom interface for serviio ( licences permitting )...

EDIT - hmm i can see linux and windows systems, but i dont see files for MasOSx does you know if it will work?

[kairoh]

taylor the admin code to allow us to make a custom interface for serviio ( licences permitting )...

Why would you need this?

EDIT - hmm i can see linux and windows systems, but i dont see files for MasOSx does you know if it will work?

The H2 Console is a WebUI for basic database management : it should work on any platform.

[Cerberus]

taylor the admin code to allow us to make a custom interface for serviio ( licences permitting )...

Why would you need this? <---- cause most users would not have the first clue howe to do it in that webui

EDIT - hmm i can see linux and windows systems, but i dont see files for MasOSx does you know if it will work?

The H2 Console is a WebUI for basic database management : it should work on any platform. < -- hmm im not so sure on that but ok

Indeed the main drawback is that you have to stop Serviio for editing the db (but I think this may be fixed with some option).

With Derby you can circumvent that by using the Apache Derby Network Server. I'm using that one with Serviio for now, so that the lock is done by the JVM of the Network Server. Isn't something like that available for H2? Would be nice

Edit: Already found it: http://www.h2database.com/html/features ... onnections and http://www.h2database.com/html/tutorial ... ing_server. Seems possible...

Indeed the main drawback is that you have to stop Serviio for editing the db (but I think this may be fixed with some option).

With Derby you can circumvent that by using the Apache Derby Network Server. I'm using that one with Serviio for now, so that the lock is done by the JVM of the Network Server. Isn't something like that available for H2? Would be nice

Edit: Already found it: http://www.h2database.com/html/features ... onnections and http://www.h2database.com/html/tutorial ... ing_server. Seems possible...

Kairoh, thank you. I've got it working fine! I had to enable compatibility mode with DB2 though. Compatibility mode with Derby wouldn't work. Anyway, that web editor also works amazing, and because I've set it up using tcp connections, I can edit the metabase while Serviio is running. Exactly how I wanted it. Thanks again.

[wagstaff]

r-win wrote:
I've got it working fine! I had to enable compatibility mode with DB2 though. Compatibility mode with Derby wouldn't work. Anyway, that web editor also works amazing, and because I've set it up using tcp connections, I can edit the metabase while Serviio is running.

I cannot speak for others, but I would certainly love to see a "How To" guide on this! Obviously there are some tricks to making it work (e.g. the DB2 compatibility vs. the Derby compatibility).

[kairoh]

I had to enable compatibility mode with DB2 though. Compatibility mode with Derby wouldn't work. Anyway, that web editor also works amazing, and because I've set it up using tcp connections, I can edit the metabase while Serviio is running. Exactly how I wanted it. Thanks again.

Be careful, the latest sql (script-0.6.sql) doesn't work with H2. Everything seems Ok but some index and column are missing. You may use the attached file instead.
BTW tcp connections seem really cool !

[kairoh]

I cannot speak for others, but I would certainly love to see a "How To" guide on this! Obviously there are some tricks to making it work (e.g. the DB2 compatibility vs. the Derby compatibility).

You may have look at the bitbucket ticket.
Watch out : Some step require decompiling and/or repackaging knowledge.
Most important : Zip doesn't support it (yet?)

I had to enable compatibility mode with DB2 though. Compatibility mode with Derby wouldn't work. Anyway, that web editor also works amazing, and because I've set it up using tcp connections, I can edit the metabase while Serviio is running. Exactly how I wanted it. Thanks again.

Be careful, the latest sql (script-0.6.sql) doesn't work with H2. Everything seems Ok but some index and column are missing. You may use the attached file instead.
BTW tcp connections seem really cool !

Heh, I noticed that, but only a few minutes ago (just before reading this thread, actually).

I also noticed that my Serviio installation now doesn't retrieve metadata from internet anymore, and series aren't shown as series anymore. I'll switch back to derby to see if this is a problem with the H2 database. I also noticed that with the H2 online browser I can connect with a derby database if I load the derby.jar when launching the h2 console. I think it should be possible to connect serviio to derby using the Apache Derby Network Service, and use the H2 console to connect to the same database, allowing the same features as I have now, but on a Derby database.

To be continued...

To be continued...

Alright, when I switched back to Derby, everything started working again. Makes sense, ofc
My current setup uses the Apache Network Derby Server as, well, database server. I've changed the connectionstring in serviio.properties to "jdbc:derby://localhost:1527/<path_to_serviio>/library/db" (in my case jdbc:derby://localhost:1527//opt/serviio/library/db).

I can then edit the database using the H2 Console, if I launch that one with derbyclient.jar in the classpath. The console asks for a driver class (org.apache.derby.jdbc.ClientDriver), a connectionstring (see above), a username and password. The username must be "app", the password doesn't matter. All works well using this setup. If you are interested in a complete guide, let me know. I think I can whip up something then.

[kairoh]

Heh, I noticed that, but only a few minutes ago (just before reading this thread, actually).
I also noticed that my Serviio installation now doesn't retrieve metadata from internet anymore, and series aren't shown as series anymore.
I'll switch back to derby to see if this is a problem with the H2 database.

These problems are related to the "script-0.6.sql" incompatibility. Everything is ok with the fixed version.

Heh, I noticed that, but only a few minutes ago (just before reading this thread, actually).
I also noticed that my Serviio installation now doesn't retrieve metadata from internet anymore, and series aren't shown as series anymore.

These problems are related to the "script-0.6.sql" incompatibility. Everything is ok with the fixed version.

I also had a fixed 0.6 script, but the problems remained on my setup. Might've been a corrupt database, since I've indexed it with a wrong schema to begin with.

[kairoh]

I had to enable compatibility mode with DB2 though. Compatibility mode with Derby wouldn't work.

I didn't test everything with h2 db : you're right about Derby incompatibility.
Derby supports "FETCH FIRST" sql syntax since a few weeks only and H2 will supports compat mode in the next release (see this ticket).
DB2 compatibility is needed, but creates new bugs...
We should wait for the next H2 release.

Im using H2 with Generic Derby embedded driver to:
jdbc:derby:/opt/serviio/library/db

and can select from the tables just fine...
but I cant update any rows.
An SQL data change is not permitted for a read-only connection, user or database. 25502/30000

How can I updates my broken metadata?
TIA Strafer

Nevermind, I only just had to run org.h2.tools.Console as root. big duh.
So whats the latest, is H2 or Derby faster?
Strafer