Project of the Month, January 2012: HyperSQL

HyperSQL

We’re pleased to announce that the January 2012 Project Of The Month is the HyperSQL Database Engine project. (See previous Project Of The Month winners)

Below is a transcript of a conversation I had with two of the lead developers on this project. You can also listen to the conversation below.

If the embedded player doesn’t work for you, you can also download the audio in
mp3 and ogg formats.

Rich: Today I’m speaking with Fred Toussi and Blaine Simpson of HyperSQL.


Blaine: Hi Rich.

Fred: Hello Rich.

R: HyperSQL DB is a database engine written in Java that can be embedded in Java applications, or it can be run as a standalone and connected to over JDBC. It also contains some tools for making JDBC connections to other databases.

So, without further introduction, here’s my conversation with the two lead developers of this project. Let’s jump right in with the first question. How long have you been doing this project?

F: Eleven years.

R: And have the two of you been involved that whole time?

F: I’ve been involved since the beginning. Blaine joined after about a year.

B: Long timers.

R: How big is the overall developer community that you see patches and commits from?

F: At the moment there are three of us. In the past, there have been others. People join in, do some work, finish that work …

R: How does this database compare to other databases that people might be more familiar with, like PostgreSQL?

F: In general, the two differences: Our database can use memory very well…

R: So it’s really fast!

F: It can run completely in memory without any files.

B: And on portable devices too.

F: Exactly. Another aspect – our database compares very well with those well-known ones for small and medium-sized data, in terms of speed and so on. Now, the definition of small and medium is just going up as hardware gets faster, and more disk space, etc. So at the moment, several hundred megabytes I would say, it’s extremely fast, and compares extremely well to Postgres and MySQL.

B: And there are huge advantages to all of the java databases to integrate with Java applications. Not just HyperSQL, but JavaDB and Derby – those are a lot more efficient, and easier to integrate with Java applications.

R: So you sort of embed this in with your Java application? Is that how this works?

B: You can, and it’s very easy to either embed it in an application, or use a JDBC driver. All the popular databases have a JDBC driver. Even if it’s a completely C database, if it’s a popular database, it probably has a JDBC driver. It’s just a tiny jar file. So it’s very easy for any user, either a developer or someone just running a client, like running a spreadsheet, for them to get the JDBC driver. So we have a JDBC driver. They just get a jar, and they can use it with their Java app.

R: If somebody was looking to get involved in your project, what sort of openings do you have for someone has the right skill set.

F: Every area is open to development. More compatibility with other databases – commercial databases, so that people can port applications more easily. There are lots and lots of possibilities. But it takes a lot of knowledge of databases and SQL to participate in some of the areas of the database. But if there are some other areas where they probably need knowledge of some particular API. And a good knowledge of Java, of course. But there are openings and in the past we have had a few other core developers who have contributed to the project.

R: If there’s something you’re particularly passionate about on this project, what would that be?

F: Quality. Resilience. Basically quality explains it all. Resilience means it’s on 24/7. Nothing goes down, and it’s reliable.

B: Fred’s a tireless worker. If you look through the forum history, we’re on top of every problem. And as Fred says, he works very hard to keep everything reliable. He works on this full time, constantly. It’s a very solid product. We put a lot of effort in to make it very standalone. Most Java products that people want to integrate or embed a database into their product – it’s a lot of work, and a hassle dealing with all the interdependencies with other products. For example, if you just want to use a database for your Java product, so you pull down the jar for that database, usually you have to deal with all these interdependencies. It depends on these 12 libraries. They depend on another 100 libraries. And you have to … it’s a constant chore to make sure you’re at the right version. You don’t have that issue with hsqldb. It’s a single stand-alone jar. We don’t depend on anything else.

It means that some things are extra work. For example, for a tool, I have to generate some HTML. We have to do that from scratch, because we don’t want to depend on anything else. We don’t want to make that an extra chore for people who use the product.

F: I suggest Blaine describe the tool he’s working on, because it is a significant tool in its own right. It can be used with all other databases. So, Blaine, could you go ahead and describe that.

B: My main area of concentration with HyperSQL is the tool SQLTool, and it’s a generic JDBC client. JDBC is just accessing SQL databases over the Java protocol, JDBC. This tool can connect to any database that has a JDBC driver. Like I said a few minutes ago, all popular databases have a JDBC driver, so you can use this tool, SQLTool, to connect to and work with pretty much any relational database. It’s a command-line tool, so that means that if you’re looking for something graphical with buttons and pictures and graphical tables, this isn’t what you want. It’s for people who want to interactively work on the command line – type in SQL. And also for automation.

In that respect there are other tools that do the same thing like Derby’s IQL and Oracle’s SQL plus. It started as comparable to those tools, but it’s a lot more powerful for two reasons.

For one, those tools can’t work with just any JDBC database. They work with the database that they come with. SQLTool can work with any. You can write scripts and you can use the tool, the same exact commands, to work with any database.

The second advantage is that a lot of work has gone in to make it very stable and suitable for automation purposes. You can go to the command line, whether you’re on MacOS or Windows, or Unix, and right on the command line you can give a single command line with SQL in it. You don’t need to enter the program. You just give it on the command line. People who work with automation and scripting can recognize right away that that’s extremely useful.

A lot of work has gone in so that it gives a meaningful error status. It always ensures that it will return a success status if everything works. The developer has the control to use the default, which is if an error occurs just exit right away, depending on whether it’s interactive or not. But the error handling is intuitive. If you run it interactively and an error occurs, it tells you on the screen but it won’t exit. If you execute an SQL script with it, it will exit by default, and you’ll get an exit status. So it’s very easy to integrate that, put it in scripts or cron jobs, or autosys, or Windows scheduling systems, and send error notification through email or logging systems. Very suitable for automation tasks.

R: This sounds like it’s almost a full-time job. How much time do you all put into this project?

F: I’m working full time. Blaine has worked full time when he’s between assignments. He works part-time. There’s a lot of work that he has to do, to do with maintaining the code repository – version control – to do with releases – he has set up releases on Maven, on SourceForge, etc, and with the documentation, with basic generation of documentation, because we use Docbook markup. There is a lot of logistics involved in releasing the new versions of the product, and Blaine has taken care of that as well as doing his SQLTools, and in the past he has also contributed code to the engine.

R: So you have commercial clients.

F: Yes, we have.

B: My time investment has been very sporadic, so even though I have been working steadily at regular daytime jobs, there have been several weeks when I have worked 40 hours on Hsqldb. I just finished up one of those marathon runs about a week ago, so there were about three weeks there where I would get back from my day job every day and crunch at Hsqldb.

R: I’m curious about something here – this is more of a philosophy question. You have a full-time job here, and you have commercial clients. What is the rationale for making this Open Source as well?

F: Because it was Open Source to start with. The commercial clients like the fact that it’s Open Source. These people contribute financially to the project to keep it going, so they want it to be as it is.

I do also have another product which is not Open Source, but it’s only used by a small percentage of the commercial users. Most of them want the Open Source product, which is good enough for their use.

B: And for the SQLTool portion, that gets a lot more use because it is Open Source. Millions of people can benefit by connecting to commercial like Oracle, and Open Source databases like MySQL, and everything in between. I put a lot of work into the product, so I get a lot satisfaction from reaching a wider audience, being Open Source.

F: We’ve been in computing a long time – all three of us. I developed one of the early WYSISYG word processors in the late 80s and early 90s. And then the platform it was on disappeared. I thought that the next time I did something, it would be completely cross-platform, and Open, and it has a very long shelf-life. That was probably the main motivation.

B: We appreciate the social mechanisms – the forums and mailing lists – on SourceForge, because for all these years it’s been nice to have that system in the background there working for us. I’ve spent minutes, adding up all the time, administering those communications systems over the years. It’s nice to have that working so that I can concentrate on the code, and when people bring up problems, I can work on those problems and your communication and help systems take care of themselves.

F: We use the SourceForge platform for bugs and for communication – the forums. We’ve used them from the beginning, and they are adequate for our needs.

R: Thank you all so much for your time. I really appreciate it.

F: You’re welcome.

B: Thanks very much, Rich.


More projects of the month

Project name: HyperSQL Database Engine

Date founded: 2001

Project page: https://sourceforge.net/projects/hsqldb/

Project Leaders


Blaine Simpson

Blaine Simpson

Occupation:Senior Consultant, BMC Software
Location:Virginia, US
Education:BS Engineering, Post-grad CS


Fred Toussi

Occupation:Software Developer
Location:London
Education:MSc