In the previous post the transfer of data happened using a list of selects, where each select needed to be ordered. This post expands on the concept by explaining why the ordering is needed and how a builder can be used to make it easier to use.
Read the rest of the series:
- Data cloning - Part 1: Moving data between environments
- Data cloning - Part 2: Moving multiple tables
- Data cloning - Part 3: Ordering inserts with a builder (current)
When inserting into a database it is important to match the ordering of the database’s foreign keys. For example if you have two tables:
table1 <– table2
where table2 contains a foreign key to table1. It is important to insert into table1 before table2 to ensure the entity exists in table1.
Effectively this limitation means that the cloner will not support a scenario where the tables both contain foreign keys pointed at each other: table1 <–> table2 This is also considered bad design, so is not expected to be relevant.
PostgreSQL also supports disabling the foreign key checks for a certain transaction. However, this would require elevated privileges, and a goal of the project is to not require any additional privileges. A user of the cloner could activate this setting in the transaction the cloning happens, if there is a scenario where it is needed.
Selecting nested data
Let’s say you have three different tables:
table1 <– table2 <–* table3
In this scenario you would want to select from table2 and table3 based on the selected rows of table1.
This can be done by first selecting everything from table1:
select1: select * from table1 where id = :id
This select statement can then be reused to find all rows in table2:
select2: select * from table2 where table1_id in (select id from table1 where id = :id)
You can see the in contains select1 with id selected. This can be continued for the last select:
select3: select * from table3 where table2_id in (select id from table2 where table1_id in (select id from table1 where id = :id))
This can be continued further going down.
This list could be written in code directly, which is very doable with a few helper functions. This was the approach used in the original project that inspired this one. This project aims to improve upon it with a builder.
Builder
Using a builder to select all foreign keys that are supported can be set up with a recursive builder with foreign key joins:
- main table: Just needs a base
where-statement - join by key on current table: Join to another table based on the current table
- join by key on another table: Join to another table based on a key referencing the current table.
This can be set up in a builder pattern:
final var config = builder("orders", "id", Long.class)
.joinByJoinTableForeignKey("order_items", "order_id", items -> items
.joinByMainTableForeignKey("products", "product_id", products -> products
.joinByMatchingColumns("product_audit", "id", "entity_id")))
.joinByMatchingColumns("order_audit", "id", "entity_id", audit -> audit
.joinByJoinTableForeignKey("audit_tags", "audit_id"))
.build();
This builder pattern encodes the information required to select all the sub tables. The main issue is that if you join by a key on the current table, when inserting that key has to be reversed. In order to do that the translator between the builder config and a list of selects needs to select in the correct order. This can be done recursively by putting all tables that join by the main table in front of the main table configuration.
The builder pattern is then simply set up using an interface to specify the configuration:
public interface CloneConfigurationBuilder {
interface SubTableBuilder {
SubTableBuilder joinByJoinTableForeignKey(
String tableName,
String foreignKey,
Consumer<SubTableBuilder> subTables
);
SubTableBuilder joinByMainTableForeignKey(
String tableName,
String foreignKey,
Consumer<SubTableBuilder> subTables
);
SubTableBuilder joinByMatchingColumns(
String tableName,
String mainTableColumn,
String joinedTableColumn,
Consumer<SubTableBuilder> subTables
);
}
static <T> CloneConfigurationBuilderImpl<T> builder(
String tableName,
String columnName,
Class<T> columnClass
) {
return new CloneConfigurationBuilderImpl<>(tableName, columnName, columnClass);
}
CloneConfigurationBuilder schema(final String schema);
CloneConfiguration build();
}
Using this builder the configuration can be set up in an easy to use way.
Conclusion
The builder encodes the foreign key relationships between tables, removing the need to write nested SQL by hand and ensuring inserts are always ordered correctly. The next post covers how to verify that a given configuration is consistent with the actual database schema.
View the source on github.com/HHMagnus/PostgresqlJavaEntityCloner