Блог

Проблема

Мне нужно ежедневно синхронизировать наши последние внутренние данные с базой данных внешнего аудита, которая не предлагает интерфейс обновления. Чтобы обновить некоторые записи, мне нужно сначала сгенерировать и отправить файл удаления, чтобы удалить эти записи, а затем добавить файл вставки с теми же, но обновленными записями в нем.

Важной деталью является то, что все записи в файлах удаления должны дословно совпадать с внешними записями, чтобы быть удаленными.

Предлагаемый подход

В настоящее время я использую две отдельные таблицы SQL для контроля версий того, что я вставил/удалил.

Допустим, что прямо сейчас inserted_records таблица выглядит так:

     id | file_version | contract_id | customer_name | start_year
    9  | 6            | 1           | Alice         | 2015
    10 | 6            | 2           | Bob           | 2015
    11 | 6            | 3           | Charlie       | 2015
 

Сопровождается отдельной и пустой deleted_records таблицей с одинаковыми столбцами.

Теперь, если я захочу

• измените идентификатор customer_name строки с Алисы на Дейва 9
• измените start_year для Боба с 2015 по 2020 год на идентификатор строки 10

inserted_records Будут сгенерированы две новые строки, строки 12 и 13, в свою очередь создающие новый файл вставки 7.

     id | file_version | contract_id | customer_name | start_year
    9  | 6            | 1           | Alice         | 2015
    10 | 6            | 2           | Bob           | 2015
    11 | 6            | 3           | Charlie       | 2015
    12 | 7            | 1           | Dave          | 2015
    13 | 7            | 2           | Bob           | 2020
 

Затем их исходные значения столбцов в строках 9 и 10 затем копируются в ранее пустые deleted_records , в свою очередь создавая новый файл удаления 1.

     id | file_version | contract_id | customer_name | start_year
    1  | 1            | 1           | Alice         | 2015
    2  | 1            | 2           | Bob           | 2015
 

Теперь, если бы я сначала отправил файл удаления 1, а затем файл вставки 7, я бы получил желаемый результат.

Вопрос

Как я могу запросить текущий набор записей, учитывая все произошедшие вставки и удаления? Предполагая, что все записи в deleted_records всегда имеют совпадения, inserted_records а если их несколько, мы всегда сначала удаляем записи с меньшими номерами версий файлов.

Я попытался сначала написать один, чтобы запросить inserted_records последние записи, сгруппированные по contract_id .

 select top 1 with ties *
from insertion_record
order by row_number() over (partition by contract_id order by file_version desc)
 

Это дало бы мне строки 11, 12 и 13, что я и хотел в данном конкретном примере. Но если бы мы также хотели удалить строку записи 11 с Чарли, то мой запрос больше не работал бы, так как он не deleted_records учитывает, и я понятия не имею, как это сделать в SQL.

Кроме того, моя гайка говорит мне, что этот подход не является надежным, поскольку есть две отдельные и движущиеся части, возможно, есть лучший подход для решения этой проблемы?

Как я могу запросить текущий набор записей

Я не понимаю вашего вопроса. Каждый SQL — запрос сопоставляется с текущим набором записей, если под этим вы подразумеваете данные, находящиеся в настоящее время в базе данных.

Я действительно вижу пару проблем.

1. Если в таблице, из которой вы удаляете, не определен ключ, даже точное совпадение в каждом столбце может привести к удалению более одной строки.
2. Вы выполняете специальное обновление с гарантией транзакции ОБНОВЛЕНИЯ. Я полагаю, что таблица, которую вы обновляете, в противном случае простаивает, и на практике вам не нужно беспокоиться о том, что кто-то другой (или вы) повторно вставит удаленные строки до того, как ваши вставки поступят. Но это проблема, которая ждет своего часа.

Если вы пытаетесь создать набор строк, которые будут результатом серии вставок и удалений, вы не предоставили достаточно информации, чтобы сказать, как это можно сделать или даже возможно ли это. Должен быть какой-то способ уникальной идентификации строк, чтобы можно было сопоставлять удаления и вставки. (В конце концов, они не совпадают во всех столбцах.) И вам понадобится некоторое указание порядка работы, потому что имеет значение, следует ли ВСТАВКА за УДАЛЕНИЕМ или предшествует УДАЛЕНИЮ.