Walidacja pliku w tabeli roboczej - LEFT JOIN zamiast INNER JOIN i blokada częściowego zapisu

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

Walidacja importu w tabeli roboczej ma w T-SQL jeden cel: żaden wiersz pliku nie może zniknąć bez śladu, a do dokumentu nie może trafić tylko część danych. W opisywanym wdrożeniu import zlecenia z pliku ma dwa etapy: wiersze pliku trafiają najpierw do tabeli roboczej z kontrolą, a do dokumentów dopiero wtedy, gdy kontrola nie zwróci błędu. Wiersz tabeli roboczej ma dwie dodatkowe kolumny kontroli: opis błędu i znacznik. Sam import opisuje strona o imporcie palet do zlecenia wydania, a ten artykuł pokazuje wzorzec do przeniesienia do dowolnej bazy.

Złączenie wsadu ze stanem przez INNER JOIN gubi bez śladu każdy wiersz, którego nie ma w stanie. LEFT JOIN zachowuje wszystkie wiersze i pozwala ustawić flagę błędu, a funkcja okna wykrywa duplikaty. INSERT z warunkiem NOT EXISTS przenosi dane do dokumentu tylko wtedy, gdy żaden wiersz nie ma błędu, a porównanie liczby wierszy pliku i zapisanych zamyka kontrolę.

Podobne zachowanie LEFT JOIN, który zostawia linię bez dopasowania, opisuje strona o zamykaniu zlecenia w T-SQL. Wynik jest identyczny na poziomach zgodności 130 i 160. Przykład w pliku z pięcioma wierszami pokazuje różnicę: INNER JOIN zwraca cztery wiersze, LEFT JOIN pięć, a zapis dokumentu po kontroli zero albo wszystkie poprawne wiersze.

Wsad zapisany w tabeli roboczej wymaga trzech decyzji, na które odpowiadają kolejno trzy mechanizmy.

1. Jak nie zgubić wiersza: LEFT JOIN zachowuje wszystkie wiersze wsadu.
2. Jak go oznaczyć: flaga błędu z funkcją okna opisuje problem w samym wierszu.
3. Kiedy pozwolić na zapis: INSERT z warunkiem NOT EXISTS przepuszcza dane tylko bez żadnego błędu.

Plik trafia najpierw do tabeli roboczej, a do dokumentu po kontroli

Tabela robocza jest pośrednim miejscem, w którym wiersze pliku czekają na kontrolę przed zapisem do dokumentu. W przykładzie są trzy tabele. Stan ma trzy kolumny i klucz na parze paleta i partia. Wsad ma numer wiersza. Do tabeli dokumentu zapis idzie dopiero po kontroli.

Plik ma pięć wierszy: wiersz 3 ma literówkę w numerze palety, a wiersze 4 i 5 mają tę samą paletę. Według dokumentacji wdrożenia import składa się z zapisu w tabeli roboczej z kontrolą i zapisu zlecenia, gdy dla żadnej pozycji nie zapisano błędu. Wzorzec odtwarza te dwa etapy na przykładowych tabelach.

INNER JOIN gubi wiersze, których nie ma w stanie

INNER JOIN zwraca tylko wiersze z dopasowaniem po obu stronach, więc literówka w numerze palety znika z wyniku bez śladu. W przykładzie złączenie wsadu ze stanem zwraca cztery wiersze z pięciu. Nikt nie zobaczy, że piąty wiersz istniał, bo wynik nie ma pola, które mogłoby go oznaczyć. Opisujemy INNER JOIN jako wzorzec, który tak działa, a nie jako zachowanie opisywanego wdrożenia.

Pierwsza tabela zestawia liczniki: INNER JOIN zwraca cztery wiersze, LEFT JOIN pięć, a trzy wiersze mają opis błędu.

Liczniki z pierwszego zapytania po bloku kodu.

LEFT JOIN zachowuje każdy wiersz pliku i pozwala ustawić flagę

Widok kontrola_przyklad łączy wsad ze stanem przez LEFT JOIN po parze paleta i partia. Wiersz bez dopasowania zostaje w wyniku z pustym polem stanu, a to puste pole służy do ustawienia flagi „brak w stanie”. Skrypt ma dwa separatory partii GO. GO jest separatorem narzędzia, a nie instrukcją T-SQL.

SET NOCOUNT ON;
CREATE TABLE dbo.stan_przyklad (paleta varchar(10), partia varchar(10), ilosc int NOT NULL, PRIMARY KEY (paleta, partia));
CREATE TABLE dbo.wsad_przyklad (wiersz int PRIMARY KEY, paleta varchar(10) NOT NULL, partia varchar(10) NOT NULL);
CREATE TABLE dbo.zapis_przyklad (paleta varchar(10), partia varchar(10), ilosc int NOT NULL);
INSERT dbo.stan_przyklad VALUES ('P-001','A1',24), ('P-002','A1',24), ('P-003','B2',12), ('P-004','B2',12), ('P-005','C3',6);
INSERT dbo.wsad_przyklad VALUES (1,'P-001','A1'), (2,'P-002','A1'), (3,'P-0O3','B2'), (4,'P-004','B2'), (5,'P-004','B2');
GO
CREATE VIEW dbo.kontrola_przyklad AS
SELECT w.wiersz, w.paleta, w.partia, s.ilosc,
       CASE WHEN s.paleta IS NULL THEN 'brak w stanie'
            WHEN COUNT(*) OVER (PARTITION BY w.paleta) > 1 THEN 'powtorzona paleta' END AS blad
FROM dbo.wsad_przyklad AS w
LEFT JOIN dbo.stan_przyklad AS s ON s.paleta = w.paleta AND s.partia = w.partia;
GO
SELECT (SELECT COUNT(*) FROM dbo.wsad_przyklad AS w JOIN dbo.stan_przyklad AS s ON s.paleta = w.paleta AND s.partia = w.partia) AS wiersze_inner,
       COUNT(*) AS wiersze_left, COUNT(blad) AS z_bledem FROM dbo.kontrola_przyklad;
SELECT wiersz, paleta, partia, blad FROM dbo.kontrola_przyklad ORDER BY wiersz;
INSERT dbo.zapis_przyklad (paleta, partia, ilosc)
SELECT paleta, partia, -ilosc FROM dbo.kontrola_przyklad
WHERE NOT EXISTS (SELECT 1 FROM dbo.kontrola_przyklad WHERE blad IS NOT NULL);
SELECT (SELECT COUNT(*) FROM dbo.wsad_przyklad) AS w_pliku, (SELECT COUNT(*) FROM dbo.zapis_przyklad) AS zapisane, N'pierwszy plik' AS etap;
DELETE dbo.wsad_przyklad WHERE wiersz = 3 OR wiersz = 5;
INSERT dbo.wsad_przyklad VALUES (6,'P-005','C3');
INSERT dbo.zapis_przyklad (paleta, partia, ilosc)
SELECT paleta, partia, -ilosc FROM dbo.kontrola_przyklad
WHERE NOT EXISTS (SELECT 1 FROM dbo.kontrola_przyklad WHERE blad IS NOT NULL);
SELECT (SELECT COUNT(*) FROM dbo.wsad_przyklad) AS w_pliku, (SELECT COUNT(*) FROM dbo.zapis_przyklad) AS zapisane, N'poprawiony plik' AS etap;

Przykład ilustracyjny, nazwy wymyślone. Skrypt buduje widok z flagą błędu i wykonuje dwa etapy zapisu.

Druga tabela pokazuje wynik widoku dla wszystkich pięciu wierszy. Wiersz 3 dostaje opis „brak w stanie”, a wiersze 4 i 5 opis „powtorzona paleta”. Wiersze 1 i 2 mają w kolumnie błędu wartość NULL, czyli są poprawne.

Wynik drugiego zapytania z bloku kodu.

Duplikaty numerów wykrywa funkcja okna

Drugi warunek flagi używa funkcji okna COUNT(*) OVER (PARTITION BY paleta). Dzieli ona wiersze wsadu na grupy o tym samym numerze palety i liczy wiersze w każdej grupie. Gdy grupa ma więcej niż jeden wiersz, wszystkie jej wiersze dostają opis „powtorzona paleta”. Dlatego w przykładzie flagę mają oba wiersze z tą samą paletą, a nie jeden z nich.

Funkcja okna nie usuwa duplikatu, tylko go oznacza. Decyzja, który z dwóch wierszy zostaje, należy do osoby poprawiającej plik. Poniższa lista podaje dwie cechy tej kontroli.

• Klucz grupy: duplikat jest liczony po samej palecie, bez partii.
• Skutek: oba wiersze z tym samym numerem dostają flagę, więc oba blokują zapis.

INSERT z warunkiem NOT EXISTS przenosi dane tylko wtedy, gdy nie ma błędów

Pierwszy INSERT do tabeli zapis_przyklad bierze wiersze z widoku, ale tylko gdy warunek NOT EXISTS nie znajdzie w widoku żadnego wiersza z opisem błędu. W pliku z pięcioma wierszami trzy wiersze mają opis błędu, więc zapisano 0 wierszy z 5. Zasada „wszystko albo nic” dotyczy tu jednej instrukcji INSERT z SELECT: albo trafiają wszystkie wiersze widoku, albo żaden. Nie dotyczy całego importu ani opisywanego wdrożenia.

Po poprawce pliku, z usuniętymi wierszami 3 i 5 i dodanym wierszem 6, ten sam INSERT zapisał 4 wiersze z 4. Warunek jest więc ten sam przed edycją pliku i po niej, a zmienia się zawartość wsadu. W opisywanym wdrożeniu import kończy się przy błędzie komunikatem, który widać poniżej.

Występują różnice pomiędzy danymi. Import WZ nie został wykonany.

Wynik zapytań kontrolnych po każdym etapie zapisu.

Porównanie liczby wierszy pliku i zapisanych zamyka kontrolę

Ostatnie zapytanie każdego etapu porównuje liczbę wierszy pliku z liczbą wierszy zapisanych w tabeli dokumentu. W pierwszym etapie jest to 5 do 0, w poprawionym 4 do 4. Flaga błędu jest pierwszą warstwą kontroli, a porównanie liczb drugą: rozbieżność przy braku flag oznaczałaby utratę wierszy.

Rozbicie ilości na palety pokazuje artykuł o rozbiciu ilości na palety w T-SQL. Obsługę wyjątków w zapisie opisuje strona o obsłudze wyjątków w procedurze zapisu.

Wzorzec ma kilka granic, które opisuje poniższa lista. Widok nie może wskazywać tabeli tymczasowej bazy, a porównanie liczb nie zastępuje flagi.

Views or functions are not allowed on temporary tables.

• Tabela tymczasowa bazy: widok na takiej tabeli kończy się komunikatem Msg 4508, na obu poziomach zgodności.
• Duplikat po samej palecie: kontrola nie odróżnia dwóch partii tej samej palety.
• Klucz stanu bez powtórzeń: para paleta i partia w stanie nie może się powtarzać, bo zdublowany stan mnoży wiersze widoku i daje fałszywe „powtorzona paleta”.
• Pusty plik: pusty plik przechodzi kontrolę, bo zero wierszy w pliku równa się zeru zapisanych.

Przykład (dane wymyślone). Plik ma pięć wierszy. Wiersz 3 ma literówkę w numerze palety, a wiersze 4 i 5 powtarzają Paletę A. Złączenie INNER JOIN zwraca 4 wiersze z 5, więc literówka znika bez śladu.

LEFT JOIN zwraca wszystkie 5 wierszy, z czego 3 mają opis błędu, a INSERT z warunkiem NOT EXISTS zapisuje 0 wierszy. Po usunięciu wierszy 3 i 5 i dodaniu wiersza 6 zapisują się 4 wiersze z 4.

Pytania do analizy

Przed zastosowaniem wzorca odpowiedz na cztery pytania.

1. Czy w imporcie wiersz, którego nie ma w stanie, ma znikać z wyniku, czy dostawać opis błędu?
2. Czy duplikat ma być liczony po samej palecie, czy po parze paleta i partia?
3. Czy do dokumentu ma trafiać część wierszy pliku, czy tylko cały poprawny plik?
4. Gdzie w procesie ma stać porównanie liczby wierszy pliku i zapisanych: w procedurze czy w teście po imporcie?