CHECK constraints are already pretty great when you want to sanitize your data. But there are some limitations to CHECK constraints, including the fact that they are applied to the table itself, when sometimes, you want to specify constraints that only apply in certain situations.
This can be done with the SQL standard WITH CHECK OPTION clause, which is implemented by at least Oracle and SQL Server. Here’s how to do that:
CREATE TABLE books (
id NUMBER(10) NOT NULL,
title VARCHAR2(100 CHAR) NOT NULL,
price NUMBER(10, 2) NOT NULL,
CONSTRAINT pk_book PRIMARY KEY (id)
CREATE VIEW expensive_books
SELECT id, title, price
WHERE price > 100
WITH CHECK OPTION;
INSERT INTO books
VALUES (1, '1984', 35.90);
INSERT INTO books
'The Answer to Life, the Universe, and Everything',
As you can see, expensive_books are all those books whose price is more than 100.00. This view will only report the second book:
SELECT * FROM expensive_books;
The above query yields:
ID TITLE PRICE
-- ----------------------------------------- -------
2 The Answer to Life, the Universe, and ... 999.9
But now, that we have that CHECK OPTION, we can also prevent users from inserting “expensive books” that aren’t really expensive. For instance, let’s run this query:
INSERT INTO expensive_books
VALUES (3, '10 Reasons why jOOQ is Awesome', 9.99);
This query won’t work now. We’re getting:
ORA-01402: view WITH CHECK OPTION where-clause violation
We also cannot update any of the “expensive books” to be non-expensive:
SET price = 9.99;
This query results in the same ORA-01402 error message.
Inline WITH CHECK OPTION
In case you need to locally prevent bogus data from being inserted into a table, you can also use inline WITH CHECK OPTION clauses like so:
INSERT INTO (
WHERE price > 1000
WITH CHECK OPTION
VALUES (3, 'Modern Enterprise Software', 999.99);
And the above query again resutls in an ORA-01402 error.
Using SQL transformation to generate ad-hoc constraints
While CHECK OPTION is very useful for stored views, which can have proper grants for those users that may not access the underlying table directly, the inline CHECK OPTION is mainly useful when you transform dynamic SQL in an intermediate SQL transformation layer in your applciation.
This can be done with jOOQ’s SQL transformation capabilities, for instance, where you can watch out for a certain table in your SQL statements, and then centrally prevent bogus DML from being executed. This is a great way to implement multi-tenancy, if your database doesn’t natively support row-level security.
Stay tuned for a future blog post explaining how to transform your SQL with jOOQ to implement row-level security for any database.
jOOQ is a great framework when you want to work with SQL in Java without having too much ORM in your way. At the same time, it can be integrated into many environments as it is offering you support for many database-specific features. One such database-specific feature is partitioning in PostgreSQL. Partitioning in PostgreSQL is mainly used for performance reasons because it can improve query performance in certain situations. jOOQ has no explicit support for this feature but it can be integrated quite easily as we will show you.
This article is brought to you by the Germany based jOOQ integration partnerUWS Software Service (UWS). UWS is specialised in custom software development, application modernisation and outsourcing with a distinct focus on the Java Enterprise ecosystem.
Partitioning in PostgreSQL
With the partitioning feature of PostgreSQL you have the possibility of splitting data that would form a huge table into multiple separate tables. Each of the partitions is a normal table which inherits its columns and constraints from a parent table. This so-called table inheritance can be used for “range partitioning” where, for example, the data from one range does not overlap the data from another range in terms of identifiers, dates or other criteria.
Like in the following example, you can have partitioning for a table “author” that shares the same foreign-key of a table “authorgroup” in all its rows.
As you can see, we set up inheritance and – in order to have a simple example – we just put one constraint checking that the partitions have the same “authorgroup_id”. Basically, this results in the “author” table only containing table and column definitions, but no data. However, when querying the “author” table, PostgreSQL will really query all the inheriting “author_n” tables returning a combined result.
A trivial approach to using jOOQ with partitioning
In order to work with the partitioning described above, jOOQ offers several options. You can use the default way which is to let jOOQ generate one class per table. In order to insert data into multiple tables, you would have to use different classes. This approach is used in the following snippet:
You can see that multiple classes generated by jOOQ need to be used, so depending on how many partitions you have, generated classes can pollute your codebase. Also, imagine that you eventually need to iterate over partitions, which would be cumbersome to do with this approach. Another approach could be that you use jOOQ to build fields and tables using string manipulation but that is error prone again and prevents support for generic type safety. Also, consider the case where you want true data separation in terms of multi-tenancy.
You see that there are some considerations to do when working with partitioning. Fortunately jOOQ offers various ways of working with partitioned tables, and in the following we’ll compare approaches, so that you can choose the one most suitable for you.
Using jOOQ with partitioning and multi-tenancy
JOOQ’s runtime-schema mapping is often used to realize database environments, such that for example during development, one database is queried but when deployed to production, the queries are going to another database. Multi-tenancy is another recommended use case for runtime-schema mapping as it allows for strict partitioning and for configuring your application to only use databases or tables being configured in the runtime-schema mapping. So running the same code would result in working with different databases or tables depending on the configuration, which allows for true separation of data in terms of multi-tenancy.
The following configuration taken from the jOOQ documentation is executed when creating the DSLContext so it can be considered a system-wide setting:
Settings settings = new Settings()
// Add the settings to the Configuration
DSLContext create = DSL.using(
connection, SQLDialect.ORACLE, settings);
// Run queries with the "mapped" configuration
// results in SQL:
// “SELECT * FROM MY_BOOK_WORLD.AUTHOR_1”
Using this approach you can map one table to one partition permanently eg. “AUTHOR” to “AUTHOR_1” for environment “DEV”. In another environment you could choose to map “AUTHOR” table to “AUTHOR_2”.
Runtime-schema mapping only allows you to map to exactly one table on a per-query basis, so you could not handle the use case where you would want to manipulate more than one table partition. If you would like to have more flexibility you might want to consider the next approach.
Using jOOQ with partitioning and without multi-tenancy
If you need to handle multiple table partitions without having multi-tenancy, you need a more flexible way of accessing partitions. The following example shows how you can do it in a dynamic and type safe way, avoiding errors and being usable in the same elegant way you are used to by jOOQ:
What you can see above is that the partition numbers are abstracted away so that you can use “AUTHOR” table instead of “AUTHOR_1”. Thus, your code won’t be polluted with many generated classes. Another thing is that the partitioner object is initialized dynamically so you can use it for example in a loop like above. Also it follows the Builder pattern so that you can operate on it like you are used to by jOOQ.
The code above is doing exactly the same as the first trivial snippet, but there are multiple benefits like type safe and reusable access to partitioned tables.
Integration of jOOQ partitioning without multi-tenancy into a Maven build process (optional)
If you are using Continuous-Integration you can integrate the solution above so that jOOQ is not generating tables for the partitioned tables. This can be achieved using a regular expression that excludes certain table names when generating Java classes. When using Maven, your integration might look something like this:
Then it’s just calling mvn install and jOOQ maven plugin will be generating the database schema in compilation time.
Integrating jOOQ with PostgreSQL: Partitioning
This article described how jOOQ in combination with the partitioning feature of PostgreSQL can be used to implement multi-tenancy and improve database performance. PostgreSQL’s documentation states that for partitioning “the benefits will normally be worthwhile only when a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server.”
Achieving support for partitioning with jOOQ is as easy as adding configuration or a small utility class, jOOQ is then able to support partitioning with or without multi-tenancy and without sacrificing type safety. Apart from Java-level integration, the described solution also smoothly integrates into your build and test process.
You may want to look at the sources of the partitioner utility class which also includes a test-class so that you can see the behavior and integration in more detail.
Please let us know if you need support for this or other jOOQ integrations within your environment. UWS Software Service (UWS) is an official jOOQ integration partner.