Digest for jooq-user@googlegroups.com - 3 updates in 2 topics

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Digest for jooq-user@googlegroups.com - 3 updates in 2 topics

http://groups.google.com/group/jooq-user/topics mailing list
Lukas Eder <[hidden email]>: Aug 29 07:33AM -0700

Version 3.12.0 - August 29, 2019
================================================================================
 
In this release, we've focused on a lot of minor infrastructure tasks,
greatly
improving the overall quality of jOOQ. We've reworked some of our automated
integration tests, which has helped us fix a big number of not yet
discovered
issues, including a much better coverage of our 26 supported RDBMS dialects.
 
We're excited about these internal changes, as they will help us implement
a lot
of features that have been requested by many for a long time, including an
immutable query object model, with all the secondary benefits like caching
of
generated SQL, and much more powerful dynamic SQL construction and
transformation in the future.
 
Major new features include the new procedural language API shipped with our
commercial distributions, new data types including native JSON support,
MemSQL
support, formal Java 11+ support, a much better parser, and reactive stream
API
support.
 
 
Procedural languages
--------------------
 
Following up on jOOQ 3.11's support for anonymous blocks, the jOOQ 3.12
Professional and Enterprise Editions now include support for a variety of
procedural language features, including
 
- Variable declarations
- Variable assignments
- Loops (WHILE, REPEAT, FOR, LOOP)
- If Then Else
- Labels
- Exit, Continue, Goto
- Execute
 
This feature set is part of our ongoing efforts to continue supporting more
advanced vendor specific functionality, including our planned definition and
translation of stored procedures, triggers, and other, ad-hoc procedural
logic
that helps move data processing logic into the database server.
 
 
New Databases Supported
-----------------------
 
The jOOQ Professional Edition now supports the MemSQL dialect. MemSQL is
derived
from MySQL, although our integration tests have shown that there are
numerous
differences, such that supporting MemSQL formally will add a lot of value
to our
customers by providing much increased syntactic correctness.
 
 
Reactive streams
----------------
 
The reactive programming model is gaining traction in some environments as
new,
useful streaming APIs emerge, such as e.g. Reactor. These APIs have agreed
to
work with a common SPI: reactive streams, and since JDK 9 the new
java.util.concurrent.Flow SPI. jOOQ 3.12 now implements these paradigms on
an
API level, such that an integration with APIs like Reactor becomes much more
easy. The implementation still binds to JDBC, and is thus blocking. Future
versions of jOOQ will abstract over JDBC to allow for running queries
against
ADBA (by Oracle) or R2DBC (by Spring)
 
 
New data types
--------------
 
We've introduced native support for a few new data types, which are often
very
useful in specific situations. These include:
 
- JSON / JSONB: A native string wrapper for textual and binary JSON data.
While
users will still want to bind more specific JSON to maps and lists using
custom data type Bindings, in a lot of cases, being able to just
serialise and
deserialise JSON content as strings is sufficient. jOOQ now provides out
of
the box support for this approach for various SQL dialects.
- INSTANT: RDBMS do not agree on the meaning of the SQL standard TIMESTAMP
WITH
TIME ZONE. PostgreSQL, for example, interprets it as a unix timestamp,
just
like java.time.Instant. For an optimal PostgreSQL experience, this new
INSTANT
type will be much more useful than the standard JDBC
java.time.OffsetDateTime
binding.
- ROWID: Most RDBMS have a native ROWID / OID / CTID / physloc identity
value that physically identifies a row on the underlying storage system,
irrespective of any logical primary key. These ROWIDs can be leveraged to
run
more performant, vendor specific queries. Supporting this type allows for
easily using this feature in arbitrary queries.
 
 
Parser
------
 
Our parser is seeing a lot of continued improvements over the releases as we
gather feedback from our users. Our main drivers for feedback are:
 
- The DDLDatabase which allows for generating code from DDL scripts rather
than
live JDBC connections to your database
- The https://www.jooq.org/translate website, which translates any kind of
SQL
between database dialects.
 
SQL dialect translation will evolve into an independent product in the
future.
DDL parsing is already very powerful, and a lot of customers rely on it for
their production systems.
 
In the next versions, we will be able to simulate DDL on our own, without
H2,
which will open up a variety of possible use cases, including better schema
management.
 
Specific jOOQ 3.12 parser improvements include:
 
- Being able to access schema meta information (column types, constraints)
to
better emulate SQL features / translate SQL syntax between dialects
- A parse search path, similar to PostgreSQL's search_path, or other
dialects'
current_schema, allowing support for unqualified object references.
- The DDL simulation from the DDLDatabase is now moved into the core
library,
supporting it also out of the box as a DDL script based meta data source
- A new special comment syntax that helps ignoring SQL fragments in the jOOQ
parser only, while executing it in your ordinary SQL execution.
- A new interactive mode in the ParserCLI
- Support for nested block comments
- And much more
 
 
Formal Java 11 Support
----------------------
 
While we have been supporting Java 11 for a while through our integration
tests,
jOOQ 3.12 now fully supports Java 11 to help improve the experience around
the
transitive JAXB dependency, which we now removed entirely out of jOOQ.
 
The commercial editions ship with a Java 11+ supporting distribution, which
includes more optimal API usage, depending on new Java 9-11 APIs. All
editions,
including the jOOQ Open Source Edition, have a Java 8+ distribution that
supports any Java version starting from Java 8.
 
 
Commercial Editions
-------------------
 
Dual licensing is at the core of our business, helping us to provide
continued
value to our customers.
 
In the past, the main distinction between the different jOOQ editions was
the
number of database products each edition supported. In the future, we want
to
provide even more value to our customers with commercial subscriptions.
This is
why, starting from jOOQ 3.12, we are now offering some new, advanced
features
only in our commercial distributions. Such features include:
 
- The procedural language API, which is available with the jOOQ Professional
and Enterprise Editions
- While the jOOQ 3.12 Open Source Edition supports Java 8+, the jOOQ 3.12
Professional Edition also ships with a Java 11+ distribution, leveraging
some
newer JDK APIs, and the jOOQ 3.12 Enterprise Edition continues supporting
Java 6 and 7.
- Since Java 8 still sees very substantial market adoption, compared to
Java 11,
we still support Java 8 in the jOOQ 3.12 Open Source Edition.
- Starting from jOOQ 3.12, formal support for older RDBMS dialect versions
in
the runtime libraries is reserved to the jOOQ Professional and Enterprise
Editions. The jOOQ Open Source Edition will ship with support for the
latest
version of an RDBMS dialect, only. The code generator is not affected by
this
change.
 
By offering more value to our paying customers, we believe that we can
continue
our successful business model, which in turn allows us to continue the free
jOOQ Open Source Edition for free. Our strategy is:
 
- To implement new, advanced, commercial only features.
- To offer legacy support (legacy Java versions, legacy database versions)
to
paying customers only.
- To continue supporting a rich set of features to Open Source Edition
users.
 
 
H2 and SQLite integration
-------------------------
 
Over the past year, both H2 and SQLite have seen a lot of improvements,
which we
have now supported in jOOQ as well. Specifically, H2 is moving at a very
fast
pace, and our traditional close cooperation got even better as we're helping
the H2 team with our insights into the SQL standards, while the H2 team is
helping us with our own implementations.
 
 
Other improvements
------------------
 
The complete list of changes can be found on our website:
https://www.jooq.org/notes
 
A few improvements are worth summarising here explicitly
 
- We've added support for a few new SQL predicates, such as the standard
UNIQUE and SIMILAR TO predicates, as well as the synthetic, but very
useful
LIKE ANY predicate.
- The JAXB implementation dependency has been removed and replaced by our
own
simplified implementation for a better Java 9+ experience.
- The historic log4j (1.x) dependency has been removed. We're now logging
only
via the optional slf4j dependency (which supports log4j bridges), or
java.util.logging, if slf4j cannot be found on the classpath.
- The shaded jOOR dependency has been upgraded to 0.9.12.
- We've greatly improved our @Support annotation usage for better use with
jOOQ-checker.
- jOOQ-checker can now run with ErrorProne as well as with the checker
framework
as the latter still does not support Java 9+.
- We've added support for a lot of new DDL statements and clauses.
- There is now a synthetic PRODUCT() aggregate and window function.
- We added support for the very useful window functions GROUPS mode.
- Formatting CSV, JSON, XML now supports nested formatting.
- UPDATE / DELETE statements now support (and emulate) ORDER BY and LIMIT.
- When constructing advanced code generation configuration, users had to
resort
to using programmatic configuration. It is now possible to use SQL
statements
to dynamically construct regular expression matching tables, columns, etc.
- Configuration has a new UnwrapperProvider SPI.
- MockFileDatabase can now handle regular expressions and update statements.
- Settings can cleanly separate the configuration of name case and
quotation.
- MySQL DDL character sets are now supported, just like collations.
- A new Table.where() API simplifies the construction of simple derived
tables.
This feature will be very useful in the future, for improved row level
security support.
- A nice BigQuery and H2 feature is the "* EXCEPT (...)" syntax, which
allows
for removing columns from an asterisked expression. We now have
Asterisk.except() and QualifiedAsterisk.except().
- A lot of improvements in date time arithmetic were added, including
support
for vendor specific DateParts, like WEEK.
 
 
 
 
Features and Improvements
-------------------------
 
#714 - Add support for non-standard ORDER BY .. LIMIT clauses in UPDATE,
DELETE statements
#1699 - Add support for the SQL standard UNIQUE predicate
#1725 - Add support for SQL standard SIMILAR TO predicate
#2026 - Add a new @Internal annotation to annotate API parts that are not
supposed to be used
#2059 - Add support for the MemSQL database for the jOOQ Professional
Edition
#2132 - Add support for non-standard date-time fields in the EXTRACT()
function
#2208 - Implement a MockFileDatabase
#2233 - Add Result<?> DSLContext.fetchFromXML() to allow for loading
results that were exported using Result.formatXML()
#2731 - Add some sections to the manual helping users migrate from other
popular ORMs
#3606 - Emulate INSERT/UPDATE/DELETE (SELECT ...) by WITH v AS (SELECT ...)
INSERT/UPDATE/DELETE v on SQL Server
#3607 - Allow for emitting common table expression declarations to
RenderContext
#4371 - Let ResultQuery<R> extend Publisher<R>
#4473 - Add ResultQuery<R1>.coerce(Field<?>[]), coerce(Table<R>), that
coerce a new Record type on a ResultQuery
#4498 - Emulate INSERT .. RETURNING via SQL Server OUTPUT clause
#5110 - Add support for Derby's MERGE statement
#5576 - Add support for SQLite 3.15's row value expression support
#5601 - IN list padding setting should also apply to row IN predicates
#5640 - MockFileDatabase should support all jOOQ import formats, including
JSON, XML
#5700 - Add support for Oracle's STATS_MODE() aggregate function
#5781 - Add support for ALTER TABLE .. RENAME COLUMN in MySQL 8
#5895 - Add support for TIME[STAMP] WITH TIME ZONE in HSQLDB
#5899 - jOOQ-checker should provide both checker-framework and ErrorProne
checks
#5909 - Deprecate RenderNameStyle.QUOTED and replace feature by
renderQuotedNames
#5939 - Add PRODUCT() aggregate and window function
#5966 - Add support for NATURAL FULL [ OUTER ] JOIN
#5969 - Add <includeInvisibleColumns/> to code generator configuration to
allow for hiding Oracle's and H2's INVISIBLE columns
#6234 - Add newline configuration to code generator
#6260 - Support loading multiple files in XMLDatabase
#6450 - Javadoc should reference JDK 11 Javadoc
#6475 - Add procedural language abstraction API
#6486 - Add a JDK 11 build
#6548 - Support multiple external configuration files through
<configurationFiles/>
#6583 - Work around MySQL's self-reference-in-DML-subquery restriction
#6709 - Support custom Hibernate properties in JPADatabase
#6778 - Support parsing PostgreSQL's dollar quoted string constants
#6944 - Add a way to specify what object type a <forcedType/> should match
#6971 - Allow Maven plugin to succeed in absence of valid DB connection if
schema is up to date
#7005 - Add support for MariaDB INTERSECT and EXCEPT
#7048 - Document the FILTER clause
#7143 - Support merging external <configurationFile/> into Maven
configuration
#7153 - Allow for configuring a locale to be used for upper/lower casing
inside of jOOQ
#7163 - Add Settings.parseWithMetaLookups
#7216 - Add RecordN<T1, ..., TN> fetchSingle(Field<T1>, ..., Field<TN>)
#7235 - DDLDatabase fails with ParserException on JSON field
#7268 - Add support for MySQL's UNIQUE [ index_name ] syntax
#7297 - Support deriving a WindowDefinition from another WindowDefinition
#7305 - Add support for DECLARE statements inside of statement blocks
#7306 - Add support for the SET command to assign values to variables
#7331 - Add reference to <forcedType/> configuration in DataType,
Converter, Binding
#7336 - Add new flags to Settings to govern parser behaviour
#7337 - Add Settings.parseDialect
#7361 - Improve formatting of nested joins
#7397 - Add a section to the manual about SELECT *
#7426 - Add support for Teradata QUALIFY
#7445 - Support parsing PostgreSQL-specific interval literals
#7470 - Add indexOf() methods to all TableLike types
#7485 - Document jOOQ's auto-conversion utility
#7490 - Upgrade jOOQ-meta-extensions dependency to Hibernate 5.4
#7545 - ForcedType configurations should have include / exclude expressions
#7570 - Add support for CREATE TYPE .. AS ENUM
#7587 - Use --release flag in JDK 9+ based builds
#7591 - Add new ResourceManagingScope which allows for Binding
implementations to register resources which are freed after execution
#7595 - Add support for Java 11
#7598 - Add search box to Javadocs
#7599 - Print row count in LoggerListener for ResultQuery
#7602 - Upgrade jOOR dependency's Java 9 functionality
#7609 - Add Setting to turn off ORDER BY clause in emulated OFFSET .. FETCH
pagination
#7612 - Add configuration to DDLDatabase to indicate whether unquoted names
are reported in lower case, upper case or as-is
#7619 - Don't ship old XSDs in binaries
#7628 - Add support for PostgreSQL 11
#7633 - ALTER TABLE .. ADD FOREIGN KEY .. REFERENCES references unqualified
table name
#7646 - Support for window functions GROUPS mode
#7647 - Support window function EXCLUDE clause
#7678 - Mention more ways to inline parameters in manual
#7705 - Add support for PostgreSQL FOR NO KEY UPDATE and FOR KEY SHARE
#7712 - Add support for Catalogs in JDBCDatabase
#7719 - Add code generation flag
Matthias Kurz <[hidden email]>: Aug 29 12:50PM -0700

Hi Lukas,
 
congratulations, nice release!
 
Regarding the commercial editions:
- The Java 11+ supporting distribution, "which includes more optimal API
usage, depending on new Java 9-11 APIs" - can you be a bit more specific?
Which newer JDK APIs are leveraged? Examples?
- Can you give examples for the procedural language API? How does that work?

Further (not related to the commercial editions):
- Native JSON/JSONB support: Can you show an example?
- You write "...immutable query object model, with all the secondary
benefits like caching of generated SQL..." Is this "immutable query object
model" in internal change? Is there a (new) user facing API? And: Will
generated SQL be cached internally now somehow (or was that the case
already)?
 
Thank you very much for this great piece of software!
 
Cheers,
Matthias
Lukas Eder <[hidden email]>: Aug 29 08:04AM -0700

Hi Marek,
 
We've just released jOOQ 3.12. With it there is experimental support for
running our DDL simulation in the core library though
DSLContext.meta(Source...). You can provide a set of DDL scripts, and we'll
run them against H2 (translated to H2 dialect), to produce the resulting
meta model, which you can use in your tooling, or in the parser.
 
Any early feedback would be greatly appreciated!
 
Cheers,
Lukas
 
On Monday, July 29, 2019 at 12:51:53 PM UTC+2, marekgregor wrote:
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].