SQLite and Derby

I was interested to stumble across the web document: The Design Of SQLite4.

SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is not going away. SQLite3 and SQLite4 will be supported in parallel. The SQLite3 legacy will not be abandoned. SQLite3 will continue to be maintained and improved. But designers of new systems will now have the option to select SQLite4 instead of SQLite3 if desired.

SQLite4 strives to keep the best features of SQLite3 while addressing issues with SQLite3 that can not be fixed without breaking compatibility.

It surprised me to learn that "internally, SQLite3 simply treats that PRIMARY KEY as a UNIQUE constraint. The actual key used for storage in SQLite is the rowid associated with each row." I think it is good that SQLite 4 will amend that choice, and treat PRIMARY KEY more as a DBA would expect it to behave.

I like the fact that, overall, SQLite is continuing to move toward a more standard and "correct" implementation, by doing things like requiring that PRIMARY KEY columns be non-null, and turning foreign key constraints on by default.

Overall, it looks like SQLite is heading in a good direction and I'm pleased to hear that.

The overall goals of SQLite are very similar to Derby, which I am considerably more familiar with.

The Derby community, too, continues to remain active. Here's the plan for the next major Derby release, which will be coming out this summer: 10.11.1 Release Summary:

• MERGE statement
• Deferrable constraints
• WHEN clause in CREATE TRIGGER
• Rolling log file
• Experimental Lucene support
• Simple case expression
• New SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY function
• Use sequence generators to implement identity columns
• add HoldForConnection ij command to match NoHoldForConnection

Although some of those features are pretty small, a few of them are large, dramatic steps forward (MERGE, CREATE TRIGGER WHEN, deferrable constraints, the Lucene integration)

In my own professional and personal life, I haven't been spending as much time with Derby recently. I no longer write code in Java for 50 hours every week, so it's hard for me to find either time or excuses to be intimately involved with Derby.

However, I try to follow along as best I can, monitoring the email lists, spending time in the Derby communities in places like Stack Overflow, and generally keeping in contact with that team, because there's a superb community of brilliant engineers working on Derby, and I don't want to lose touch with them.

So: way to go, SQLite, and way to go: Derby!