Six free databases with commercial-quality features

How effective are free and open source data bases? For answers, we reviewed 6:: Microsoft SQL Server Express, PostgreSQL, Oracle's MySQL, MariaDB, Apache Derby and Firebird SQL.

1 2 Page 2
Page 2 of 2

Apache Derby started as Cloudscape in the mid-1990s, then it was acquired by Informix before being re-branded as IBM Cloudscape when IBM took over the assets from Informix. In 2004 IBM contributed the Cloudscape code to the Apache project and in 2005 the first Apache Derby version was released as Version 10.1.1. Apache Derby can be run in two modes, either as a fully functional embedded database engine (for instance as used in Apache Tomcat and Red Hat application server) or as a traditional client-server database using the Derby Network Server.

We installed Version 10.9.1 on a Windows Server 2008 running the Oracle Java run-time Virtual Environment (JRE) Version 1.7. It is important that the JRE is installed prior to installing Apache Derby and the install does require a few commands issued from the DOS prompt. Apache Derby ships with an interactive scripting tool named "ij." It works with the embedded Derby JDBC driver or a client JDBC driver. Using the command prompt we were able to create a test database and empty table from the command prompt using just a few short commands.

After creating our test table, we decided to utilize the built-in bulk insert procedure to insert our million-plus test rows. The bulk insert worked fine except we ran into a similar problem we had with PostgreSQL with uppercase vs. lowercase in the execute syntax. Our table was named test1, all lower case, but the parser (and documentation, after some online research) insisted we enter it in all upper case like this:

CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'TEST1','c:\temp\SampleData.txt',',',null,null,0);

After fixing the case problem, the data import took just a few seconds and a select statement to retrieve the first 100,000 records ran in less than five seconds. Just as we observed with the other database servers, working with individual rows of data proved to be quite efficient and our select, update and delete statements completed in fractions of a second. Although Apache Derby did not deliver quite the same speed as MariaDB and MySQL in our tests, but the differences were small, ranging from one-tenth to one-hundredth of a second slower.

Security features include user authentication and authorization allowing for object-level access restrictions. Apache Derby databases can also be encrypted either when they are created or encryption can be added to existing databases.

The small footprint of Apache Derby makes it an excellent choice for embedding in Java applications such as those running on PDAs or cell phones. The Apache website provides comprehensive and current documentation and there is also a fairly active community of blogs and forums dedicated in whole or in part to Apache Derby.

Firebird

Firebird has been in existence in some form or another since 1981 and is currently based on the highly regarded InterBase code released by Borland in 2000. The current modules are released under the Initial Developer's Public License. Firebird runs on Linux, Unix and Windows. We reviewed the latest stable release, Version 2.5.1, on a Windows Server 2008 R2. Installation is very straightforward and completed in a few minutes with few options to decide during installation. In addition we decided to install FlameRobin, a cross-platform management tool for Firebird.

FlameRobin is not as feature-rich as a pgAdmin, SQL Server Management Studio or HeidiSQL, but it does offer some basic features such as the ability to create databases, tables and other objects. Objects are created by providing sample T-SQL code as opposed to providing an interface where you can type in field names and such. However, once you have created an object such as a table, it can be edited using a GUI. As it turned out, the more we worked with FlameRobin, the more useful features we happened upon.

After completing the installation, we created a test database with a table to hold the test data. As there is no real bulk insert option nor an import feature, we ended up creating a "temporary" table to hold the external data and used this to insert into our real table. This process was not exactly elegant, but it did get the job done after some back-and-forth with permissions issues. As is often the case with free and open source software, the documentation is not always up-to-date or presented as well as with commercial software, but a well-formulated search engine phrase eventually yields results for most tasks.

Once the test data was loaded, we conducted our usual test, but ran into problems when attempting to select large numbers of rows (100,000-plus) using SELECT statements both from the Firebird ISQL tool and also using FlameRobin. The ISQL tool simply crashed whereas FlameRobin did return the records, but since these were returned just a few hundred at a time it was hard to get a feel for performance. While working with smaller subsets we found Firebird to work efficiently and we were able to select, update and delete single rows in fractions of a second.

Some of the security features of Firebird include object-level access control and the ability to remove source code for triggers, procedures and views. Firebird does not currently offer native encryption, but there are third-party tools that do. As for documentation, Firebird has an excellent FAQ website along with a variety of other documentation available on the Firebird website.

Overall we were impressed with Firebird SQL as the vendor has added additional features to an already excellent database, InterBase. For example, the most recent releases of Firebird SQL include recursive queries, procedural triggers to remote DB access and multi-threading. A new major release, Version 3.0, is in the works, with an alpha release expected to be available later this year. This will include features such as the ability to write stored procedures in Java and C++.

Conclusion

Bottom line, all the products we reviewed are very capable database servers and selecting one will depend on a variety of factors such as existing infrastructure and scripting language, size of the data to be stored/retrieved, whether the database is to be embedded, and how much vendor support is needed. Once you've selected a candidate, be sure to follow the best practices recommended by the vendor to secure the product, together with the host OS, before it is used in a production environment.

Perschke is CSO for Arc Seven Technology. She is also an experienced technical writer, and has written numerous white papers for a number of organizations, including Fortune 500 companies. Susan can be reached at susan@arcseven.com.

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.

Copyright © 2012 IDG Communications, Inc.

1 2 Page 2
Page 2 of 2
IT Salary Survey: The results are in