Okay so I currently I am setting up a number of databases on my own V-Server and I thought it would be helpful to make a few notes about the current Java databases and how easy they are to run in Server mode. The two biggest databases are Derby and HSQLDB, this post is about Derby. Both databases are really geared towards the embedded space and generally focus their documentation and tasks around this. In their default setups they are both pretty insecure for running on a public network, neither for example defaults to using passwords to authenticate logins.

All the databases are getting setup in the same basic way. Each database is going to be run in the userspace of a dedicated UNIX user and that user is meant to be accessed via SSH and SSH Keys. All the datafiles will reside within the user’s space. I’m not going to go into that as it is fairly straightforward and is probably better covered by the various UNIX admin guides you can find around the internet.

So Derby first then, well I have played around with Derby before so I hoped this would be the easiest. One thing that you need to know about Derby is that its documentation is good but it is organised into several different documents… by a madman. There is no apparent logic, rhyme or reason to the way information is included in one document or another. When setting up the server I had to jump between the Developer’s Guide, the Tuning Guide, the Server Guide and more unusually the Tool Guide. If you have the same experience then don’t worry, you’re probably doing it right.

Having downloaded the Derby package unpack it to the root directory. I find it helps with upgrades to create a symbolic link ~/derby to the actual installation directory. You can also create a profile environment variable DERBY_HOME that points to $HOME/derby.

With that all setup you can simply run ~/derby/startNetworkServer and you should be in the basic network server business. This is where it gets a bit more complicated. Derby will look for its configuration in the directory where you start it not in DERBY_HOME. So you need to create a derby.properties file in $HOME to configure the behaviour of the server.

To require authorisation you need to properties created in the properties file. Remember that the format of this file is a standard Java properties file. The two properties are:

• derby.connection.requireAuthentication=true
• derby.authentication.provider=BUILTIN

You should now restart the server. Now when you want to interact with the database you should need to provide a username and password. You have to check this though to make sure it works.

Run the ij tool from the command line: try to create a new database with something like the following

connect 'jdbc:derby:test;create=true';

You should get a message saying something like ‘Invalid authentication’. If the database is actually created then the database is probably not picking up your properties file, which in turn is likely to be an installation issue.

All being well we can now add a user to the properties file. User information is also a property so to make the file manageable you probably want to use comments to divide up the file into relevant sections and try and keep all the user setup in the same place. The format of the user entry is derby.user.USERNAME=PASSWORD. So for example

derby.user.test=changeme

Okay now restart the server and login via ij again. This time use a connection URL like the one below.

connect 'jdbc:derby:test;create=true;user=test;password=changeme';

This should now create a new database under HOME and give you access to it normally. Drop out of ij and confirm that the test directory has been created.

That is pretty much that now, from here on in you can connect to your server database instance as you would any other database server.

Written like this the setup must seem very basic and quick to perform, which is good. However when I was finding my way with it, it took over an hour and most of that time was taken up with looking for things in the documentation. Nothing is logically organised with Derby, for example the configuration properties are all described in the Tuning Guide. Except the network control properties which are described in the Admin’s Guide.

Unlike code DRY is not a good idea unless you are going to very rigorously modularise the documentation. Even then why not repeat something if it means that all the information relevant to task is one place?

Derby has a great feature list but I wonder how many users are ignorant of what it can do because of the poor documentation?