null

Массовое обновление ID в PostgreSQL

В ходе разработки одного приложения (приложение на 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;
    }
}