В ходе разработки одного приложения (приложение на Spring'е, для доступа к данным использовались Spring Data JPA + Hibernate), для демонстрации проекта заказчику в базу данных был вручную добавлен набор данных. Для простоты все первичные ключи были заданы вручную (идентифкаторы были заранее заданы достаточно большими, чтобы на ранних этапах не возникало проблем с их автоматической генерацией).
К сожалению, эти изначально тестовые данные пришлось оставить, из-за чего мне пришлось искать способ обновления всех первичных (и соответсвующих внешних) ключей. Так как за генерацию ключей отвечает автоматически созданный sequence 'hibernate_sequence', было принято простое решение - обновить все ключи во всех таблицах, используя функцию nextval('hibernate_sequence'). Для автоматизации этого процесса я разработал простую программу на Java, которая парсит вывод команды \d+ утилиты psql. Вывод этой команды выглядит примерно так:
Table "public.role"
Column | Type | Modifiers | Storage | Stats target | Description
---------+------------------------+-----------+----------+--------------+-------------
user_id | bigint | not null | plain | |
role | character varying(255) | | extended | |
Indexes:
"unique_role_const" UNIQUE CONSTRAINT, btree (user_id, role)
Foreign-key constraints:
"fkma2xkq5p4o7vf20em7qjfpf5s" FOREIGN KEY (user_id) REFERENCES user_account(id)
Column | Type | Modifiers | Storage | Stats target | Description
------------------------+-------------------------+-----------+----------+--------------+-------------
id | bigint | not null | plain | |
about | character varying(1000) | | extended | |
photo | bytea | | extended | |
Indexes:
"studentprofile_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"fknaad0ldkaot1io6lg4pnym28y" FOREIGN KEY (university_id) REFERENCES university(id)
Table "public.user_account"
Column | Type | Modifiers | Storage | Stats target | Description
--------------------+------------------------+-----------+----------+--------------+-------------
id | bigint | not null | plain | |
firstname | character varying(255) | | extended | |
lastname | character varying(255) | | extended | |
password | character varying(255) | not null | extended | |
patronymicname | character varying(255) | | extended | |
username | character varying(255) | not null | extended | |
Indexes:
"user_account_pkey" PRIMARY KEY, btree (id)
"uk_castjbvpeeus0r8lbpehiu0e4" UNIQUE CONSTRAINT, btree (username)
Foreign-key constraints:
"fkrcmssfkl2lmxh7it7p9qxav6n" FOREIGN KEY (student_profile_id) REFERENCES studentprofile(id)
Referenced by:
TABLE "role" CONSTRAINT "fkma2xkq5p4o7vf20em7qjfpf5s" FOREIGN KEY (user_id) REFERENCES user_account(id)
Код программы программы весьма прост. В качестве входного аргумента командной строки она принимает путь к файлу с выводом команды \d+, на стандартный поток вывода будет выведен скрипт обновления ключей.
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
public class App {
public static void main(String[] args) throws IOException {
List<Table> tables = read(Paths.get(args[0]));
for (Table table : tables) {
System.out.println(table.updateIds());
}
}
static class Table {
private final String tableName;
private final List<ForeignKey> foreignKeys;
public Table(String tableName, List<ForeignKey> foreignKeys) {
this.tableName = tableName;
this.foreignKeys = foreignKeys;
}
public String updateIds() {
StringBuilder dropAll = new StringBuilder(), createAll = new StringBuilder();
StringBuilder forLoop = new StringBuilder("FOR old_id IN SELECT id FROM ").append(tableName).append(" LOOP\n");
forLoop.append("UPDATE ").append(tableName).append("\n" +
"SET id = nextval('hibernate_sequence')\n" +
"WHERE id = old_id\n" +
"RETURNING id\n" +
"INTO new_id;\n");
for (ForeignKey foreignKey : foreignKeys) {
String drop = foreignKey.dropConstraint();
dropAll.append(drop).append("\n");
forLoop.append(String.format("UPDATE %s \nSET %s = new_id \nWHERE %s = old_id\n", foreignKey.tableName,
foreignKey.columnName, foreignKey.columnName));
String create = foreignKey.createConstraint(tableName, "id");
createAll.append(create).append("\n");
}
forLoop.append("END LOOP;\n");
return "DO $$\n" +
"DECLARE\n" +
"old_id BIGINT;\n" +
"new_id BIGINT;\n" +
"BEGIN\n" + dropAll.toString() + forLoop.toString() + createAll.toString() + "END;\n" +
"$$;";
}
}
static class ForeignKey {
private final String tableName;
private final String constraintName;
private final String columnName;
public ForeignKey(String tableName, String constraintName, String columnName) {
this.tableName = tableName;
this.constraintName = constraintName;
this.columnName = columnName;
}
public String createConstraint(String referencedTableName, String referencedColumnName) {
return String.format("ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s);",
tableName, constraintName, columnName, referencedTableName, referencedColumnName);
}
public String dropConstraint() {
return String.format("ALTER TABLE %s DROP CONSTRAINT %s;", tableName, constraintName);
}
}
public static List<Table> read(Path filePath) throws IOException {
List<Table> result = new ArrayList<>();
List<String> lines = Files.lines(filePath)
.map(String::trim).collect(Collectors.toList());
Table current;
String currentTableName = "";
List<ForeignKey> currentConstraints = new ArrayList<>();
boolean constraintsStarted = false;
for (String line : lines) {
if (line.startsWith("Table")) {
if (currentConstraints.size() > 0) {
result.add(new Table(currentTableName, currentConstraints));
}
Matcher matcher = Pattern.compile("^Table \"public\\.([^\"]*)\"$").matcher(line);
boolean matches = matcher.matches();
currentTableName = matcher.group(1);
currentConstraints = new ArrayList<>();
constraintsStarted = false;
} else if (line.startsWith("Indexes") || line.startsWith("Foreign-key") || line.startsWith("Triggers")
|| line.isEmpty()) {
constraintsStarted = false;
} else if (line.startsWith("Referenced by")) {
constraintsStarted = true;
} else if (constraintsStarted) {
Matcher matcher = Pattern.compile("^TABLE \"([^\"]+)\" CONSTRAINT \"([^\"]*)\" FOREIGN KEY " +
"\\(([^)]*)\\) REFERENCES ([^(]*)\\(([^)]*)\\)$").matcher(line);
boolean matches = matcher.matches();
String tableName = matcher.group(1);
String constraintName = matcher.group(2);
String columnName = matcher.group(3);
String referencedTableName = matcher.group(4);
String referencedColumnName = matcher.group(5);
currentConstraints.add(new ForeignKey(tableName, constraintName, columnName));
}
}
if (currentConstraints.size() > 0) {
result.add(new Table(currentTableName, currentConstraints));
}
return result;
}
}