DBvolution / Blog: Recent posts

TLDNR: Standard SQL Intervals are broken so I’ve implemented DBDateRepeat instead. Date subtraction has been replaced with secondsFrom(otherDate) and getDateRepeatFrom(otherDate).

Intervals are included in the SQL standard as representations of time durations. That is they are meant to represent the difference between a day and a date. A day is the concept of 24 contiguous hours while a date is a particular 24 hours such as 1/March/2013.... read more

Currently I'm doing a big migration project and a part of it is inserting parsed files from one DB to another. DBvolution is playing a big part in the project but not in the way you might expect.

I did try inserting all the rows using the DBV API but DBV is designed for programmer performance not database performance and it took too long. So I switched to using the bulk load facility available in the database.... read more

Currently I'm implementing recursive queries in DBvolution.

I'm still not finished with the design but I'm creating a method within DBQuery to follow a self-referencing foreign key to the root or leaves of a tree structure.

The primary use case is showing the path or hierarchy above the current record, like the directory path to a folder, or the chapter, section, and sub-sections in a document.

It's an interesting area of relational database design and throws up intriguing problems for implementing recursion automatically.... read more

Expressions can also be used to create new columns from the database values automagically, allowing you to change the data type of column, retrieve dynamic values, or transform the data before it reaches your Java code.

For instance we had a database that used integers to store dates in a non-standard way. To get the correct dates automatically we created the field normally then used an expression column to get the value as a date. To simplify the process of transforming dozens of dates, we placed the expression in a custom QDT.... read more

Oracle and NuoDB are now almost completely supported. They run fast and clear and I'm impressed with the both of them.

EXCEPT: Oracle and NuoDB both treat an empty string as a NULL. This means they don't pass some tests because they disagree with the other databases fundamentally.

I have been developing an attitude that any query from DBvolution, given an identical data set, should produce the same result on every database. This does mean that I can run the test set without any annoying if/then/else protecting dainty little DBs from the nasty tests. It also means that DBV-based applications can move seamlessly from one database backend to another. Possibly even spanning across heterogenous databases.... read more

Junit is awesome and makes it possible to change DBvolution without endangering existing functionality. However I didn't start the project with enough knowledge of Junit or discipline to cover all of the functionality. So there might be lots of code that might not be tested. Subsequently I started getting better at writing tests, sometimes even doing Test Driven Development. Backfilling in the dark is a haphazard process though and daunting.... read more

I spent my flight over Java writing DBJavaObject in Java making DBJavaObject the most Java thing I've done.

I was meant to be writing the JavaDocs on DBNumber, but I noticed some problems when I was documenting DBLargeObject.

In particular there was only one subclass of DBLargeObject when I was certain that there should be 2. The second should have been DBJavaObject, the one that stores actual objects in the database, but it directly sub-classed QueryableDatatype. Also it looked too terse to actually be working.... read more

Grouping your results is very important functionality for database queries and it is supported in several ways in DBvolution.

Firstly, it's supported by trivial Java so even if DBvolution's built-in support isn't sufficient you can still loop over the data and get the results you need. That's expensive for your middleware tier or UI though so DBV has better ways to do it: distinct methods, and DBReport classes.... read more

SQLite is now completely support by DBvolution. Anything you can do with any other database, you can do with SQLite.

It was very surprising just how much work was required for SQLite. The developers claim it is the world's most installed database, yet it seems to be the least standard. Also, as far as I could tell, it has the worst JDBC support.

The large install base is due to the number of common applications that use SQLite as a storage system. Its major advantage is that it's easy to create a database programatically. ... read more

One thing that has bugged me for ages in database programming is getting primary keys.

Primary keys are vital to database programming and at some point every DBA just starts adding a "pk_uid integer not null primary key" like statement to their tables without even noticing. Unfortunately this leads to conflicts with developers about exactly how they make a unique integer for the primary key. The simplest solution these days is to add an auto increment statement, or a sequence and a default. ... read more

Release 0.9.29 brings support for PostgresSQL up-to-date with all the current functionality.

Umm, that's about it really. Pretty big news, but nothing much else to mention.

The biggest problem was handling comparing a DBBoolean and an issue with TRUNC.

But it's all fixed now.

Oh and I'm now testing against 3 databases on 5 profiles and a total of 1200+ tests.

A big bug has been squashed and I'm pleased to have found it.

JDBC has the concept of a Connection and a Statement. DBvolution has the concept of a statement and adds concepts for a database and a transaction, but nothing like a Connection.

This is because the difficulty of maintaining a connection while worrying about multiple threads wasn't worth it. However that meant Connection has been quietly ignored.... read more

With a little help my friends, a lot of internet searching, and some hard thinking, MySQL is now up-to-date.

This is very pleasing as it's fixed several bugs in DBvolution as well as in DBvolution's support for MySQL. For instance DBBoolean is now a little more likely to be automatically generated.

Of course it's also exciting because MySQL is the one of the big 3 databases that I need to support. Only 2 more to go :)

One of the strange things about this project is that it's taught me just how important and awesome JUnit testing is, while misusing JUnit horribly.

Still I'm up to 400+ tests and I'm very impressed with how they help the process. I'm also horrified both that I've written so many tests and at how few I have!

Theoretically my tests are mostly wrong too: unit tests are supposed to test tiny deterministic functions and certainly shouldn't depend on anything external like a database.... read more

Some schemas seem pathological, everything has a bizarre name and when there is a sensible name the data is crazy.

Say, for instance, that your database has a column called REG_DATE but the values are integers around the 40,000 mark. After much searching you work out that the values are the number of days since the 1st of January 1900.

So you have a date that is represented by an integer and all your Java is going to be converting from the integer to the more appropriate Date. And then you realise all the other tables have a similar column...... read more

DBRow is the representation of a table and its structure.

A fundamental difference between Object Oriented Programming and Relational Databases is that DBs are based on persistent tables that store information. OOP however generates a process that creates transient information.

In Java the only persistent concept is a class, so DBvolution represents each table as a class. Each column of the table is represented as a Java field. Connecting the class and fields to the table and columns are annotations: DBTableName & DBColumn.... read more

Java programmers have a hierarchical view of the world. Which is fine, even good according to the kings and presidents of the world, but it does clash with SQL's egalitarian attitude.

Java is inherently hierarchical, from the need for a Main method to the IsA/HasA paradigm. Relational databases, on the other hand, deliberately have no hierarchy and you can randomly access data from any table. Even when you try to impose a hierarchy using a query the database will happily invert your hierarchy if it prefers it that way.... read more

I use DBvolution for a lot of scripting work and usually I use DBDatabase's update, insert, and delete methods to make changes. They're is quick and easy to use.

But what if I need to check what's going to happening before executing?

This use case is covered by static methods in DBUpdate, DBInsert, and DBDelete.

In particular DBUpdate provides the getUpdates(DBRow...) method which works out how to do the updates and then creates a DBActionList with the required actions.... read more

Toaomalkster managed to find a funny little bug caused by MS SQL Server's interpretation of "get the date". All fixed now tho.

More importantly, or not if you use SQL Server, I've added the ability to add an expression to a QueryableDatatype during the initialisation of a DBRow class.

This is similar to adding a column expression to a query but it's permanently attached to the DBRow class like any other field. This makes it easy to perform calculations, do transformations, or just trim strings.... read more

0.9.9 Is out, and it's a big deal, but you might not notice.

There are 2 major changes: QueryGraph now streamlines your queries; and DBTable has been completely re-written.

A complete graph of the tables and joins is now drawn internally with QueryGraph and used to add tables to the SQL query in a better order. Previously it was possible to create a highly inefficient outer join by adding DBRows in the "wrong" order. Now DBV adds the tables in an efficient order when possible. You can gleefully add examples any which way and know that DBV will tidy up after you.... read more

0.9.8 is out and a good thing it is too.

The permittedPattern() method of QueryableDatatype has managed to get thru innumerable versions without a problem. But strangely didn't function correctly within my JSP page at work. After much angst and worry I managed to find and fix the bug in a, supposedly, core part of DBV.

For at least 70 revisions there has been a bug in QueryableDatatype where the permitted pattern was set to the QDT itself and not the actual pattern. Obviously this is wrong and any amount of testing would have spotted it.... read more

DBRow is probably the most important class in DBvolution, and you will use it a lot.

So it's important to get it right. Here are the best practices line by line.

The simple solution is to use DBTableClassGenerator as it automatically generates good code. However DBV started with the idea that DBRow would be done by hand like a normal class, and it was only after I saw 900+ views and tables that DBTableClassGenerator was invented.... read more

After much too-ing and fro-ing DBEnums have been finalised and added to the available datatypes.

Previously I talked about using sub-subclasses of DBRow and the example used a field called 'typeNumber' to automatically limit the results. Enumerations would help turn the database's inscrutable values into nice Java objects.... read more

0.9.6 is out and it includes the final pieces for expressions.

Expressions are designed to cover the harder comparisons that QDT.permittedValues methods don't cover.

They are inspired by the Java 8 stream framework so every expression method produces a new expression. This sounds complicated but it's a well known technique and easy to write code with.

To start an expression use the DBRow column( ) method or the appropriate Expressions value( ) method. After that the methods you can use will be available to you:... read more

I've recently discovered what my JavaDocs look like and I'm tempted to halt all development until they're fixed.

That's not going to happen tho, because fixing them will be another endless task. So I'm doing them as I find them, in quiet moments say or will chatting to friends etc.

However the real question is what constitutes a good JavaDoc?

Is it short and pithy, or long and detailed? With lots of references or self-contained?... read more