by Horatiu Dan
There aren’t few the situations when applications are requested to be agile and versatile enough so that they can run dynamic reports for which the input comes at runtime.
This post aims to present a way of achieving it by leveraging the temporary configuration parameters supported by PostgreSQL databases.
According to the PostgreSQL documentation, starting with version 7.3, it is possible to set a configuration parameter using the set_config(name, value, is_local) function. Later, the value of the previously set parameter may be read using the current_setting(name) function, converted if needed and used. If the third parameter of the former function is true, the changed setting will only apply to the current transaction.
This is exactly what is needed here, a way of providing a runtime parameter value that can be used as part of an atomic operation.
Set-Up
The sample application is build with:
- Java 21
- Spring Boot version 3.1.15
- PostgreSQL Driver version 42.6.0.
- Liquibase 4.20.0
- Maven 3.6.3
At application level, the Maven project is configured to use Spring Data JPA and Liquibase dependencies.
The domain is represented by products, whose prices are in various currencies. For converting between currencies, a currency exchange rate exists. The goal is to be able to read all products with their prices represented in a certain currency, at the rate of a certain day.
Proof of Concept
In order to start modelling, one shall first create a new schema, once connected to the database.
create schema pgsetting;
There are three entities – Product, Currency, CurrencyExchange.
@Entity
@Table(name = "product")
public class Product {
@Id
@Column(name = "id")
private Long id;
@Column(name = "name", nullable = false)
private String name;
@Column(name = "price", nullable = false)
private Double price;
@ManyToOne
@JoinColumn(name = "currency_id")
private Currency currency;
...
}
@Entity
@Table(name = "currency")
public class Currency {
@Id
@Column(name = "id", nullable = false)
private Long id;
@Column(name = "name", nullable = false)
private String name;
...
}
@Entity
@Table(name = "currency_exchange")
public class CurrencyExchange {
@Id
@Column(name = "id", nullable = false)
private Long id;
@Column(name = "date", nullable = false)
private LocalDate date;
@ManyToOne
@JoinColumn(name = "from_currency_id", nullable = false)
private Currency from;
@ManyToOne
@JoinColumn(name = "to_currency_id", nullable = false)
private Currency to;
@Column(name = "value", nullable = false)
private Double value;
...
}
Each one has a corresponding CrudRepository.
@Repository
public interface ProductRepository extends CrudRepository<Product, Long> { }
@Repository
public interface CurrencyRepository extends CrudRepository<Currency, Long> { }
@Repository
public interface CurrencyExchangeRepository extends CrudRepository<CurrencyExchange, Long> { }
The data source is configured as usual in the application.properties file, together with the path to the Liquibase changelog file that records a few simple change sets for initializing the schema with the three tables and the relations among them.
For details, the application properties and db/changelog/schema-init.xml files may be explored.
The root changelog file is:
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
<include file="/db/changelog/schema-init.xml"/>
</databaseChangeLog>
When the application is started, the change sets are executed in the order they are declared. So far, everything is straight forward, nothing out of the ordinary – a simple Spring Boot application whose database changes are managed with Liquibase.
Creating the Dynamic Report
Let’s assume that currently the application has two currencies defined – RON and EUR and two products with their prices recorded in different currencies.
Currency
+--+----+
|id|name|
+--+----+
|1 |RON |
|2 |EUR |
+--+----+
Product
+--+-------------------+-----+-----------+
|id|name |price|currency_id|
+--+-------------------+-----+-----------+
|1 |Swatch Moonlight v1|100 |2 |
|2 |Winter Sky |1000 |1 |
+--+-------------------+-----+-----------+
CurrencyExchange rates for the 15th of November
+--+----------+----------------+--------------+-----+
|id|date |from_currency_id|to_currency_id|value|
+--+----------+----------------+--------------+-----+
|1 |2023-11-15|2 |1 |5 |
|2 |2023-11-15|2 |2 |1 |
|3 |2023-11-15|1 |2 |0.2 |
|4 |2023-11-15|1 |1 |1 |
+--+----------+----------------+--------------+-----+
The aimed result is a product report with all prices in EUR, using the exchange rate from the 15th of November 2023. This means the price of the second product needs to be converted.
To ease the design, the previously set goal is divided into smaller parts, then conquered. Conceptually, products shall be fetched and their prices converted (if needed).
- Fetch the products
- Convert the prices in the requested currency, using the exchange rate of the requested day
The former is trivial, a Spring Data Repository method would easily allow getting the products – List<Product> findAll().
The latter is achievable through a query that makes the conversions.
SELECT p.id,
p.name,
p.price * e.value price,
e.to_currency_id currency_id,
e.date
FROM product p
LEFT JOIN currency_exchange e on p.currency_id = e.from_currency_id and
e.to_currency_id = 2 and
e.date = '2023-11-15'
In order to unite the two, the following are accomplished:
- a view is defined, for the above query –
product_view
It is defined in the product-view.sql file and added as an idempotent operation in a repeatable Liquibase change set that is run whenever changed.
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
<include file="/db/changelog/schema-init.xml"/>
<changeSet id="repeatable" author="horatiucd" runOnChange="true">
<sqlFile dbms="postgresql" path="db/changelog/product-view.sql"/>
</changeSet>
</databaseChangeLog>
- a new entity –
ProductView– is defined as part of the domain, together with the corresponding repository
@Entity
@Immutable
public class ProductView {
@Id
private Long id;
private String name;
private Double price;
private LocalDate date;
@ManyToOne
@JoinColumn(name = "currency_id")
private Currency currency;
...
}
@Repository
public interface ProductViewRepository extends org.springframework.data.repository.Repository<ProductView, Long> {
List<ProductView> findAll();
}
The application is now able to construct the desired report, but only for a hardcoded currency and exchange rate.
In order to pass the two at runtime, the following are performed in the same transaction:
- the two parameter values are set as configuration parameters –
SELECT set_config(:name, :value, true) - the
ProductViewentities are fetched using the repository method
Also, the product_view is modified to read the configuration parameters set as part of the current transaction and select the data accordingly.
SELECT p.id,
p.name,
p.price * e.value price,
e.date,
e.to_currency_id currency_id
FROM product p
LEFT JOIN currency_exchange e on p.currency_id = e.from_currency_id and
e.to_currency_id = current_setting('pgsetting.CurrencyId')::int and
e.date = current_setting('pgsetting.CurrencyDate')::date;
current_setting('pgsetting.CurrencyId') and current_setting('pgsetting.CurrencyDate') calls read the previously set parameters, which are further converted and used.
The implementation needs some additional adjustments.
ProductViewRepository is enhanced with a method that allows setting the configuration parameters.
@Repository
public interface ProductViewRepository extends org.springframework.data.repository.Repository<ProductView, Long> {
List<ProductView> findAll();
@Query(value = "SELECT set_config(:name, :value, true)")
void setConfigParam(String name, String value);
}
The last parameter is always set to true, thus the value is kept only during the current transaction.
Also, a ProductService is defined to clearly mark all operations involved in the transaction.
@Service
public class ProductService {
private final ProductViewRepository productViewRepository;
public ProductService(ProductViewRepository productViewRepository) {
this.productViewRepository = productViewRepository;
}
@Transactional
public List<ProductView> getProducts(Currency currency, LocalDate date) {
productViewRepository.setConfigParam("pgsetting.CurrencyId",
String.valueOf(currency.getId()));
productViewRepository.setConfigParam("pgsetting.CurrencyDate",
DateTimeFormatter.ofPattern("yyyy-MM-dd").format(date));
return productViewRepository.findAll();
}
}
The name of the parameters are the ones used in the product_view definition.
To certify the implementation, two tests are set-up.
@SpringBootTest
class Product1Test {
@Autowired
private CurrencyRepository currencyRepository;
@Autowired
private ProductRepository productRepository;
@Autowired
private CurrencyExchangeRepository rateRepository;
@Autowired
private ProductService productService;
private Currency ron, eur;
private Product watch, painting;
private CurrencyExchange eurToRon, ronToEur;
private LocalDate date;
@BeforeEach
public void setup() {
ron = new Currency(1L, "RON");
eur = new Currency(2L, "EUR");
currencyRepository.saveAll(List.of(ron, eur));
watch = new Product(1L, "Swatch Moonlight v1", 100.0d, eur);
painting = new Product(2L, "Winter Sky", 1000.0d, ron);
productRepository.saveAll(List.of(watch, painting));
date = LocalDate.now();
eurToRon = new CurrencyExchange(1L, date, eur, ron, 5.0d);
CurrencyExchange eurToEur = new CurrencyExchange(2L, date, eur, eur, 1.0d);
ronToEur = new CurrencyExchange(3L, date, ron, eur, .2d);
CurrencyExchange ronToRon = new CurrencyExchange(4L, date, ron, ron, 1.0d);
rateRepository.saveAll(List.of(eurToRon, eurToEur, ronToEur, ronToRon));
}
}
The former fetches the products with prices in EUR, using the recorded exchange rates.
@Test
void prices_in_eur() {
List<ProductView> products = productService.getProducts(eur, date);
Assertions.assertEquals(2, products.size());
Assertions.assertTrue(products.stream()
.allMatch(product -> product.getCurrency().getId().equals(eur.getId())));
Assertions.assertTrue(products.stream()
.allMatch(product -> product.getDate().equals(date)));
Assertions.assertEquals(watch.getPrice(),
products.get(0).getPrice());
Assertions.assertEquals(painting.getPrice() * ronToEur.getValue(),
products.get(1).getPrice());
}
When called, product_view is:
+--+-------------------+-----+-----------+----------+
|id|name |price|currency_id|date |
+--+-------------------+-----+-----------+----------+
|1 |Swatch Moonlight v1|100 |2 |2023-11-15|
|2 |Winter Sky |200 |2 |2023-11-15|
+--+-------------------+-----+-----------+----------+
The latter fetches the products with prices in RON, using the same exchange rates.
@Test
void prices_in_ron() {
List<ProductView> products = productService.getProducts(ron, date);
Assertions.assertEquals(2, products.size());
Assertions.assertTrue(products.stream()
.allMatch(product -> product.getCurrency().getId().equals(ron.getId())));
Assertions.assertTrue(products.stream()
.allMatch(product -> product.getDate().equals(date)));
Assertions.assertEquals(watch.getPrice() * eurToRon.getValue(),
products.get(0).getPrice());
Assertions.assertEquals(painting.getPrice(),
products.get(1).getPrice());
}
When called, product_view is:
+--+-------------------+-----+-----------+----------+
|id|name |price|currency_id|date |
+--+-------------------+-----+-----------+----------+
|1 |Swatch Moonlight v1|500 |1 |2023-11-15|
|2 |Winter Sky |1000 |1 |2023-11-15|
+--+-------------------+-----+-----------+----------+
Sample Code
Available here.
Resources
- PostgreSQL System Admin Functions
- The picture was taken at Legoland, Germany
Kaizen Driven Development 