How to display user-friendly entity history

Most systems need data audit/versioning. This usually means that for every audited table FOO there is table FOO_AUD, which contains the current and all previous values of records from FOO. FOO_AUD can be populated using Hibernate Envers or simply by triggers on table FOO.

The question is, how to present historical/audit data to the user? Suppose we have a bug tracking system with tables ISSUE and ISSUE_AUD. Obviously we could display the results of the following query:
select * from issue_aud where issue_id = 10;

 rev  | revtype | issue_id |    issue_title    | issue_status | issue_assignee
 1000 |       0 |       10 | Bar always fails! | Open         |
 1011 |       1 |       10 | Bar always fails! | In Progress  | Developer A
 1037 |       1 |       10 | Bar always fails! | Resolved     | Reporter B
 1057 |       1 |       10 | Bar always fails! | Closed       |
but that's not readable and user-friendly. Ideally, we would like to present data in the following format:
 rev  |    property    | prev_value  |    curr_value
 1000 | issue_status   |             | Open
 1000 | issue_title    |             | Bar always fails!
 .... | ...........    | ..........  | .................
In this post I would like to show you how this can be done using SQL query only.


7 tips to improve data quality using database constraints

When it comes to validating application's data, most Java developers think about frontend/backend validation, JSR 303 etc. Sadly, many ignore the database, even though it allows some easy to use, declarative validations.


The number one mistake when using Hibernate

Hibernate is one of the most popular ORM solutions for Java, yet many developers are surprised when they find out that it is state based not SQL based framework.

This means that when you call Hibernate methods like session.save(entity), session.update(entity) etc. it does not go directly to the DB to issue an INSERT/UPDATE etc. but only associates the object passed as an argument with its session and assigns some internal state to it.

At a later point in time (typically before transaction commits) Hibernate performs a flush - it synchronizes the state of objects held in memory with the database, executing any number of INSERT/UPDATE/DELETE statements.

Hibernate is very easy to use. IMHO it is even too easy to use, because it makes an impression, that it is a simple framework. It's definitely not, there is a lot of complexity under the hood and if you ignore it, you can easily shoot yourself in the foot.


Writing more testable code - a simple trick

While writing tests for service layer you can:
  • create unit tests with mocked dependencies
  • create integration tests with real dependencies (typically - DB)
Both solutions have their pros and cons and neither is 100% convenient. This post shows an alternative approach.


Virtual Private Database and how to integrate it with a Java application

Virtual Private Database (VPD) allows you to implement row and column level security in your database by "hiding" data in tables, so that only a subset of all table data is accessible. The cool things is that the "hiding" can be done based on your application user's identity or roles, so that privileged users can access more data than normal users - and it is all managed at database level!


How to draw a tree using window functions and recursive query in PostgreSQL

In this post I would like to show you how to draw a nice looking tree (similar to output of unix tree command) using select statement in PostgreSQL.

Suppose we have a view v_deps, which contains information about dependencies between views, materialized views and tables (the definition of this view is at the end of the post). Every row in this view means that dependent object dep_obj depends on referenced object ref_obj:


PostgreSQL: How to handle table and view dependencies

PostgreSQL is very restrictive when it comes to modyfing existing objects. Very often when you try to ALTER TABLE or REPLACE VIEW it tells you that you cannot do it, because there's another object (typically a view or materialized view), which depends on the one you want to modify. It seems that the only solution is to DROP dependent objects, make desired changes to the target object and then recreate dropped objects.