Tag Archive | jooq

Introducing CQLC – a Query DSL for Cassandra’s CQL in Go, Inspired by jOOQ


This morning, we’ve had very nice feedback about our work on the jOOQ User Group by Ben Hood, who has been a long-time jOOQ user and ideas/bug report contributor. He has taken inspiration from our software to build CQLC, a fluent API / query DSL for Cassandra’s CQL written in Go. Citing Ben:

I think JOOQ is the best thing since sliced bread when it comes to dealing with SQL databases on the JVM. I’ve been writing some CQL based apps in Go, and I’ve really missed JOOQ. So I decided to build a JOOQ for Go/Cassandra.

cqlc generates Go code from your Cassandra schema so that you can write type safe CQL statements in Go with a natural query syntax.

It’s aimed at people using CQL in Golang apps who are looking to reduce boilerplate code. [...] I think it reinforces the pragmatism of the underlying concept behind JOOQ.

The project is available here:

http://relops.com/cqlc/

I’ve written a blog post to explain what motivated me to do this here:

http://relops.com/blog/2014/01/25/cqlc/

That’s great news for Cassandra and CQL! NoSQL databases will not profit from jOOQ directly any time soon – unless they implement actual SQL. We have studied Cassandra’s CQL syntax in the past and we’ve come to the conclusion that formal jOOQ support would be overkill, similar to JCR-SQL2 support or CMIS SQL support. 90% of the jOOQ API wouldn’t work.

So if your a Go developer using Cassandra, do have a look at CQLC!

Lesser-Known SQL Features: DEFAULT VALUES


A lesser-known SQL feature is the DEFAULT keyword, which can be used in INSERT and UPDATE statements. Consider the following table, created using standard SQL syntax:

CREATE TABLE timestamps (
  id INTEGER   GENERATED BY DEFAULT 
               AS IDENTITY(START WITH 1),
  t  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT pk_values PRIMARY KEY (id)
)

Now, in order to generate a new record in this table, you could either explicitly set a timestamp as such:

INSERT INTO timestamps (t) 
  VALUES (CURRENT_TIMESTAMP);

Or, you just use the handy DEFAULT VALUES syntax:

-- Short syntax
INSERT INTO timestamps DEFAULT VALUES;

-- Explicit syntax
INSERT INTO timestamps (t) 
  VALUES (DEFAULT);
INSERT INTO timestamps (id, t) 
  VALUES (DEFAULT, DEFAULT);

The same can be done in an UPDATE statement:

-- Set all timestamps to CURRENT_TIMESTAMP
UPDATE timestamps SET t = DEFAULT;

SQL Compatibility for DEFAULT VALUES

As always with SQL, things aren’t as bright as the SQL-92 standard specifies. According to the standard, all of the above must be supported. In reality, this can be said:

Standards-compliant databases

These databases support the standard, fully

Almost compliant databases

These databases support the DEFAULT keyword, but not the DEFAULT VALUES clause for insert statements:

  • Access
  • DB2
  • Derby (we have created DERBY-6444 for this)
  • MariaDB
  • MySQL
  • Oracle

Support for DEFAULT VALUES in jOOQ 3.3

jOOQ 3.3 will support the DEFAULT VALUES syntax and also a very useful variant of it, when combining inserting DEFAULT values with returning them after the insert:

DSL.using(configuration)
   .insertInto(TIMESTAMPS)
   .defaultValues()
   .returning(TIMESTAMPS.ID, TIMESTAMPS.T)
   .fetch();

The above query will not only insert a new record with default values, but also return those values to your Java program for further processing. As with most jOOQ API elements, the above statement will transparently work with all databases, either through native syntax:

  • DB2: SELECT .. FROM FINAL TABLE (INSERT ..)
  • PostgreSQL: INSERT .. RETURNING

… or through JDBC’s Statement.getGeneratedKeys().

Why Did SQLJ Die?


Every now and then, SQLJ pops up somewhere, mostly in a very dusty/enterprisey or in an academic context.

If you give SQLJ some thought, though, it isn’t such a bad idea. It is:

  • An ANSI and ISO standard
  • Part of the SQL standard
  • Quite easy to understand
  • Quite a powerful extension to JDBC

So why did it die (or rather, why did it never really take off)? This question was asked on Stack Overflow, and we gave an answer.

Let’s assume that you have already decided to embed your SQL (as opposed to externalising it through a templating mechanism, hiding it with an ORM, or with stored procedures). Here are a couple of reasons why SQLJ is not an optimal solution for embedding SQL:

IDE support

While Pro*C worked well for C and C++ in the 90s, Java really took of in the early 2000′s. With Java, there were also an increasing number of powerful IDEs such as Eclipse, NetBeans, JBuilder, and others. Java preprocessors and IDEs have never become friends, though, as parsing one language is hard enough. Parsing (and providing tooling) for two languages is much harder.

In fact, SQLJ made the surrounding Java code type-unsafe as IDEs and compilers couldn’t process .sqlj files before they had been pre-processed.

SQL popularity

There was a time when people started thinking that SQL itself was going to be dead. First, they did so with the advent of ORMs, then they did so with the advent of NoSQL. People thought that the DBA is dead. Again.

Well, this has been proven to be wrong a couple of times, but certainly not because of SQLJ.

Typesafety

In the late 2000′s, there had now been typesafe alternatives to SQLJ, such as jOOQ in Java, or LINQ-to.SQL in .NET, which leverage IDE features such as syntax autocompletion. By being internal domain-specific languages / query DSLs, these APIs not only bring typesafety to embedded SQL, but they also allow for dynamic SQL building, which SQLJ doesn’t support.

Predictions

While embedding SQL into other languages is a useful thing, SQLJ never solved this problem adequately. Hence, R.I.P., SQLJ

Typesafe’s Slick is Not About SQL


We have stumbled upon an interesting thread on the Typesafe SLICK user group where Slick was compared to jOOQ. In that thread, Christopher Vogt has made a couple of interesting statements.

But let us have a look at the broader context, first.

Unifying Stuff

Ever since the proclamation of UDDI or RUP, we may think that the U for Unified is a clear and unmistakable indicator for what Joel Spolsky would call architecture astronautitis. In case you’ve missed those hilarious posts, here they are:

Today, many software vendors are again trying to unify database query languages. Erik Meijer’s LINQ was the most successful attempt at doing so, so far. But even LINQ doesn’t compare to Codd’s visions, which were about replacing the whole data model first by a rock-solid mathematical theory, and only then, thinking about appropriate languages to query such data models.

Flexible vs. rigid abstractions

We believe that unifying query languages to query RDBMS, XML, Objects, and NoSQL is a bad idea because such a unification is subject to either:

  • being a flexible abstraction
  • being a rigid abstraction

If an abstraction is flexible, then the heterogeneous implementation details of the abstracted data stores will inevitably leak into the query language and into your application. You don’t gain too much, for the price of adding more layers and boiler-plate.

If an abstraction is rigid, then the unified query language (LINQ, JPQL, etc.) may be concise, but it will inevitably abstract away 80% of all useful features of the underlying data store. LINQ cannot meet the expressivity of SQL. Neither can it match the power of XPath/XQuery/XSLT/XProc, which is the most appropriate tool chain for XML. Maybe, it cannot even match what Java 8 calls the Streams API, which is very likely to become the most appropriate tool chain for objects and collections in Java.

Typesafe’s SLICK is Not About SQL

We’ve already compared Slick with jOOQ in our manual’s preface. Now, Christopher Vogt has made a clear statement about what SLICK is supposed to be and what SQL is:

There are understandable mistakes when your mind is (still) set on SQL. [...]

Good luck with jooq and check back if you are ever annoyed by SQL semantics and want Scala back :).

That is only an extract of what Christopher said, of course, and there’s certainly quite a bit of goodness in SLICK. SLICK’s mission is to provide Scala collection semantics when querying databases. That might be a desireable thing to have in the Scala platform, specifically when comparing SLICK with LINQ.

But we’ve mentioned it before, on our blog. SQL is not an undesirable language or technology. Like any legacy technology, SQL has its ways. We’ve blogged about that, too, lots of times. SQL is a standard that is constantly evolving and that is here to stay. In our opinion, any technology operating on RDBMS but at the same time aiming for hiding SQL or abstracting it away completely is against the inevitable trend imposed by the big elephants who will not let go of their best-selling technologies.

SQL is about 10 years ahead of alternative RDBMS querying methods – most specifically Java, Scala, C# collection-based ones. T-SQL has now entered the TIOBE Top 10 and is considered by TIOBE to be the language of the year 2013, PL/SQL isn’t too far behind. Don’t fight SQL any longer, embrace it. Or in Christopher Vogt’s words:

Check back with SQL/jOOQ, if you are ever annoyed by the increasing amount of leaky or rigid abstraction created by modern language architects!

Further reading: “Don’t Jump the SQL Ship Just Yet”.

The Great SQL Implementation Comparison Page


Fortunately, we have SQL standards. Or do we? It’s a well-known secret (or cynical joke) that the SQL standard is yet another SQL dialect among peers.

On this blog, we have pointed out so many differences between SQL dialects, it is hard to believe that anyone would even consider writing SQL strings rather than using jOOQ or Hibernate if they ever risk migrating their application to another database. Just consider these examples:

From the perspective of the jOOQ implementation, we know how tough it can be to abstract vendor-specific SQL away. Here’s an example piece of code that you don’t want to have in your application, dealing with the NVL() function:

switch (configuration.dialect().family()) {
  case ACCESS:
    return field("{iif}({0} is null, {1}, {0})");

  case DB2:
  case INGRES:
  case ORACLE:
  case H2:
  case HSQLDB:
    return field("{nvl}({0}, {1})", ..);

  case DERBY:
  case POSTGRES:
    return field("{coalesce}({0}, {1})", ..);

  case MARIADB:
  case MYSQL:
  case SQLITE:
    return field("{ifnull}({0}, {1})", ..);

  // By default, resort to the CASE expression
  default:
    return DSL.decode()
              .when(arg1.isNotNull(), arg1)
              .otherwise(arg2);
}

Now, trust us. The above is one of the easier cases. Consider the standardisation of the LIMIT .. OFFSET clause, for instance.

For an even more frightening overview, we recommend you visit Troels Arvin’s Comparison of Different SQL Implementations. Be warned, though!

MyBatis’ Alternative Transaction Management


On the jOOQ user group, we’re often being asked how to perform transaction management with jOOQ. And we have an easy answer ready: You don’t do that with jOOQ. You choose your favourite transaction management API, be it:

And the above list is far from being exhaustive. Transaction management is something very delicate, and it certainly should not be imposed by a library whose main purpose is not transaction management, because any such library / framework will provide you with at most a very leaky abstraction of its transaction model. In other words, if you just slightly want to deviate from “the standard” model (e.g. as imposed by Hibernate), you will suffer greatly, as soon as you want to run 2-3 queries outside of Hibernate – e.g. batch or reporting statements through jOOQ.

MyBatis’ Alternative Transaction Management

MyBatis is a SQL templating engine that provides a couple of features on top of alternative templating engines, such as Velocity, or StringTemplate. One of these features built on top of templating is precisely transaction management, as can be seen in the docs.

From what we can read in the docs, it looks as though MyBatis’ transaction managers can be overriden by Spring, for instance. However, it is not easy to see how this is done. In fact, given that MyBatis also solves Connection pooling (for which there are also very viable alternatives, such as c3p0 and DBCP), and mapping (which could be solved more easily with custom transformers, such as offered by Spring’s JdbcTemplate, or jOOQ’s RecordMapper).

As many frameworks, MyBatis tries to solve problems outside its core scope, which is SQL templating. While this may be a good thing as you only rely on a single dependency, it is also quite a lock-in, in case you have a more complex model. In the case of transaction management, we believe that this was not a good idea by MyBatis.

Thoughts from MyBatis users?

Simplernate. A Query DSL for Hibernate, Inspired by jOOQ


Simplernate” ! The name is very compelling. And so is the idea – we think. Specifically, because we get most credit for this new project in the following short readme:
https://gist.github.com/thermz/eb1b12b2146168a08e68

It reads:

Simplernate is (will be) an Hibernate wrapper that help developer to query the database using Hibernate ORM. To do this, Simplernate offers a syntax inspired by jOOQ philosofy [sic].

This obviously reminds us of Torpedoquery, another typesafe query DSL that wraps Hibernate and Hibernate’s HQL. Unfortunately, there isn’t that much to see yet, but we will certainly follow up on future progress of a project with such a promising name!

Add LATERAL Joins or CROSS APPLY to Your SQL Tool Chain


The T-SQL dialect has known the powerful CROSS APPLY and OUTER APPLY JOIN syntaxes for ages. The SQL:1999 standard had introduced almost equivalent “lateral derived tables”, which are finally supported with PostgreSQL 9.3, or Oracle 12c, which has adopted both the SQL standard LATERAL syntax and the T-SQL vendor-specific CROSS APPLY and OUTER APPLY syntaxes.

But what are we even talking about?

SQL features have a unique trait that few other languages have. They are obscure to those who don’t know them, as every language feature introduces a new syntax with new keywords. In this case: APPLY and LATERAL. But it really isn’t so hard to understand. All you do with a CROSS APPLY is a CROSS JOIN between two tables where the right-hand side of the join expression can reference columns from the left-hand side of the join expression. Consider the following example by Martin Smith on Stack Overflow:

Reusing column aliases

SELECT number,
       doubled_number,
       doubled_number_plus_one
FROM master..spt_values
CROSS APPLY (
  SELECT 2 * CAST(number AS BIGINT)
) CA1(doubled_number)
CROSS APPLY (
  SELECT doubled_number + 1
) CA2(doubled_number_plus_one)

See a SQLFiddle of the above example

In this example, we’re selecting numbers from a system table and cross apply a scalar subselect multiplying each number by two. Then to the whole table product, we cross apply another scalar subselect, adding one to the last number.

This particular example could also be implemented using subqueries in the SELECT clause. But as you can see in the above example, doubled_number_plus_one can be calculated from a previously calculated column in one go. That wouldn’t be so “simple” with subqueries.

Applying table-valued functions to each record

SELECT *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)

This example may be even more useful when you want to join a table-valued function to each record of another table.

PostgreSQL’s LATERAL derived tabels

In PostgreSQL, this can be done somewhat magically by put-ting table-valued functions in the SELECT clause:

SELECT x, GENERATE_SERIES(0, x)
FROM (VALUES(0), (1), (2)) t(x)

See a SQLFiddle of the above example

The above yields

| X | GENERATE_SERIES |
|---|-----------------|
| 0 |               0 |
| 1 |               0 |
| 1 |               1 |
| 2 |               0 |
| 2 |               1 |
| 2 |               2 |

Alternatively, since PostgreSQL 9.3, you can use an explicit lateral derived table as such:

SELECT x, y
FROM (VALUES(0), (1), (2)) t(x),
LATERAL GENERATE_SERIES(0, t.x) u(y)

See a SQLFiddle of the above example

Yielding again

| X | Y |
|---|---|
| 0 | 0 |
| 1 | 0 |
| 1 | 1 |
| 2 | 0 |
| 2 | 1 |
| 2 | 2 |

CROSS APPLY and OUTER APPLY in jOOQ 3.3

The above clauses will also be supported in the upcoming editions of jOOQ 3.3 where you can write queries like this one here:

DSL.using(configuration)
   .select()
   .from(AUTHOR)
   .crossApply(
        select(count().as("c"))
       .from(BOOK)
       .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
   .fetch();

Or lateral joins:

DSL.using(configuration)
   .select()
   .from(
        values(row(0), row(1), row(2))
            .as("t", "x"),
        lateral(generateSeries(0,
                fieldByName("t", "x"))
            .as("u", "y")))
   .fetch();

No matter if you’re using jOOQ or native SQL, lateral derived tables or CROSS APPLY should definitely be part of your awesome SQL tool chain!

jOOQ, a Love Story


… according to a jOOQ user on Twitter:

jOOQ, a love story

jOOQ, a love story

 

Let’s hope this relationship will last for a very long time! Cheers, the jOOQ team.

Free Open Source with Commercial Support


Have you ever thought about “Free” Open Source with commercial support vs. commercial software? We have and we found that the following is true:

Free Open Source Software with commercial support

… is the best business model for companies selling services for very complex OSS software where customers might lack skilled personnel. This includes things like

The above systems can cause terrible pain to your productive environments when they crash. You want to have extremely skilled fire fighters ready when your system goes down. Some of the above systems have been said to be deliberately complex in order to be able to pursue this particular business model.

Commercial software or dual-licensed software

… is the best business model for companies selling very simple, easy to use software where customers do not rely on skilled personnel in the event of a crash. This is particularly true for

The above software are so easy to handle and to maintain, their vendors would hardly make any money with support. Granted, support is always included in the license fees. That’s just part of the service quality.

Follow

Get every new post delivered to your Inbox.

Join 1,236 other followers

%d bloggers like this: