OneWebSQL™: Another competitor in the SQL schema generation “business”

I’ve been observing OneWebSQL™ for a year now, impatiently expecting its first public version and official go-live. I first discovered it during my regular jOOQ marketing work. I’m always closely observing competition for several reasons:

  • It shows that the market is alive and active, which helps making jOOQ even more relevant
  • jOOQ can incorporate the best ideas of competitor products
  • It’s always fun to meet people who share similar opinions

All of the above is a big motivation to make jOOQ better. For more competitor comparisons, see also the following recent articles:

OneWebSQL™

OneWebSQL™ is one of the more interesting ones, from a professional perspective. Similar to QueryDSL, OneWebSQL™ is backed by a company who has developed a SQL code generation and execution framework over the past years. Unlike jOOQ and QueryDSL, however, OneWebSQL™ is neither free nor open source. So lets have a brief look at what OneWebSQL™ is and how it compares to jOOQ and QueryDSL

Comparing jOOQ, QueryDSL, OneWebSQL™

The below list of compared features and attributes is meant to be objective and unbiased, even if it was written by the developer of jOOQ. Here goes

License

jOOQ ASL 2.0, free and open source
QueryDSL ASL 2.0, free and open source
OneWebSQL™ Proprietary license, closed source and partially obfuscated. Free runtime, somewhat expensive source code generator

Vendor and community

jOOQ Independent developer, active community
QueryDSL Mysema, active community
OneWebSQL™ e-point, newcomer with undisclosed community

Scope

Feature jOOQ QueryDSL OneWebSQL™
Source code generation [1] yes yes yes
Active Records [2] yes partially
(through JPA)
partially
(through generated DAOs)
Procedure support [3] yes no partially
(some JDBC abstraction)
UDT support [3] yes no no
DSL [4] yes
(formal BNF notation)
partially
(chained methods)
partially
(chained methods)
DML support yes yes yes
DDL support no no no
Supported RDBMS 13 9
(or more, using JPA)
5
SQL dialect abstraction [5] yes yes partially
(simulates some syntax elements)
SQL dialect simulation [6] yes yes no
Scala examples [7] Partially
Proof of concept
Partially
Proof of concept
Probably (?)

Some explanations

  • [1]: jOOQ and QueryDSL can reverse-engineer database meta data directly from a database. OneWebSQL™ expects a proprietary model file
  • [2]: “Active Records” are record classes that can persist (store, refresh, delete) themselves or be passed to an EntityManager, DAO, etc for persistence.
  • [3]: Stored procedures (and especially functions) can be used directly in SQL
  • [4]: A domain-specific language is best defined using a BNF notation. This can be translated to Java interfaces as described in a recent blog post. Simpler fluent APIs can still be constructed using “chained methods”. Strictly speaking, that is not a domain-specific language, though
  • [5]: SQL dialects vary greatly in subtle ways. Any SQL building tool should be able to distinguish dialects. This blog contains many examples, such as RDBMS bind variable casting madness
  • [6]: Many syntax elements (and more specifically functions) can be simulated in dialects that do not natively support them. This blog contains many examples, such as The simulation of TRUNC() in Derby
  • [7]: All Java libraries can be used in Scala, even without any native API.

Conclusion

The above comparison was made at a high level. Without further knowledge of the specific needs for any given project, it doesn’t make sense to go into the details of every library’s capabilities. On a high level, one can draw a clear line between the three products:

jOOQ
jOOQ’s strength is the fact that it supports a huge variety of standard and vendor-specific SQL statements, clauses and functions, and that it is able to simulate these in dialects that do not support them. This makes jOOQ a good choice for applications that want to make extensive use of SQL.

QueryDSL
QueryDSL’s strength is the fact that it supports multiple backends (most importantly JPQL and HQL). It greatly facilitates the construction of criteria queries, reducing JPA’s verbosity.

OneWebSQL™
OneWebSQL™’s strength is probably the fact that you get professional support when you buy a license. I’m looking forward to hearing experiences with this new library

ORM vs. SQL, compared to C vs. ASM

History is repeating itself. This is nothing new, but it takes wisdom (and Elephant memory) to remember when and how things had already happened in a similar way. When you feel that the whole SQL versus ORM debate is a bit boring and you may have seen it before, you’re probably right. It’s another religious war that can be compared to the war between C and Assembler some 40 years back. Think about it this way:

ORM vs. SQL

How many ORMs do you know that support:

  • Window functions
  • MERGE statement
  • Recursive queries
  • Common table expressions
  • Ordered aggregate functions
  • Statistical functions
  • Stored procedures (And I mean not just scalar ones!)
  • SQL-based XML
  • Temporary tables
  • … I could go on and on

ORMs are not just “higher-level” they are an entirely different paradigm, put on top of SQL. So, saying SQL is “lower-level” is not entirely accurate. It’s on a “different level”. ORMs are pretending that SQL is just a little “low-level” thing.

C vs. ASM

(citing reddit user henk53)

Which C compilers at the time made use of:

  • Advanced addressing modes
  • Opcode tricks
  • Special instructions (intrinsics came much later)
  • Jump tables (yeah, switch is close, but not quite it)
  • Build-in high level functions on some architectures (like string copy)
  • Page zero tricks
  • Segments (ugly things when all you have is small ones, but quite powerful when used fine-grained for ‘objects’ and structures)
  • Delay slot shadowing
  • Knowledge of fitting computations exactly in registers and L1.
  • … I could go on and on

C wasn’t just higher level, it was another paradigm, and yes, C compilers pretending that assembly is just a little “low-level” thing did raise our hackles back then. It threw away all the nice subtle intricacies of each specific CPU architecture and made them all equal by using only the common denominator of functionality that each CPU had.

It took a while, but eventually C compilers got better at optimizing than humans and could actually take advantage of advanced instructions in a CPU.

Original discussion

See an extract of the original thread here:
http://www.reddit.com/r/java/comments/sk25o/forget_hibernate_jooq_is_byfar_the_best_database/#c4f6z1w

jOOQ and Hibernate, a discussion

Starting out from a rather emotional and maybe not really objective comparison between jOOQ and Hibernate, this turned out to be quite an interesting discussion. A must-read for jOOQ and SQL aficionados:

http://www.reddit.com/r/java/comments/sk25o/forget_hibernate_jooq_is_byfar_the_best_database/

I personally like this comment here:

“You’d be hard-pressed to find a database abstraction layer that can map to SQL better than #jOOQ does, it pretty-much is #SQL.”

Suggestions for the Java, SQL and jOOQ Blog

This blog is picking up momentum and thus, relevance. With jOOQ, I have come a long way, working with Java, JDBC, and SQL. I have solved many problems that are worth discussion, and I know quite a few things about various RDBMS, and how they compare with each other. Some examples:

What are YOU interested in? Maybe there’s a topic in jOOQ, that is worth explaining from a jOOQ-independent point of view. Some aspect of JDBC, stored procedures, user defined types, etc. Feel free to comment on this post with suggestions

Cheers
Lukas