Aktualizacja nagłówka, która nie zeruje numerów na pozycjach

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

Zapis nagłówka dokumentu, który kopiuje jedną wartość do wszystkich pozycji, kasuje numery wpisane osobno na pozycjach. Programista T-SQL widzi wtedy dokument, w którym numer zamówienia klienta zniknął z pozycji po zwykłym zapisie nagłówka. Wzorzec z COALESCE i priorytetem źródeł zachowuje numery z pozycji, a brakujący uzupełnia z linii zlecenia. Nazwy tabel i kolumn w kodzie są przykładowe.

Aktualizacja nagłówka, która nie zeruje numerów na pozycjach, nie używa numeru z nagłówka w ogóle. Wzorzec z COALESCE bierze pierwszą wartość niepustą z kolejki źródeł: najpierw numer z pozycji, potem numer z linii zlecenia. Przykład z trzema pozycjami dał te same wyniki na poziomach zgodności 130 i 160.

Stary wzorzec aktualizuje wszystkie pozycje dokumentu jednym poleceniem UPDATE i wpisuje do nich wartość z nagłówka, nawet gdy ta wartość jest pusta. W przykładzie stary zapis wyczyścił wszystkie numery, a wzorzec z COALESCE zachował numery z pozycji i uzupełnił brakujący.

Kolumna zapisu nazywa się w przykładzie numer_zam, a leży w niej numer zamówienia klienta, więc w tekście używamy nazwy numer zamówienia klienta. Przykład opiera się na dwóch tabelach pomocniczych (linia_przyklad, pozycja_przyklad) i na jednym pustym polu nagłówka.

Stary wzorzec wpisuje jedną wartość do wszystkich pozycji dokumentu

Stary zapis nagłówka to jedno polecenie UPDATE, które ustawia status i numer zamówienia na wszystkich pozycjach dokumentu wybranego po numerze. Wartość numeru pochodzi z pola nagłówka. Gdy pole jest puste, wszystkie pozycje dostają pusty tekst, także te, w których wpisano osobny numer.

W przykładzie dokument ma trzy pozycje. Dwie mają numery ZK-1 i ZK-9, a trzecia jest bez numeru. Pole nagłówka jest puste. Po starym zapisie wszystkie trzy pozycje mają numer pusty, a nie NULL, i status 1. Zapytanie niczego nie ostrzega, bo z punktu widzenia bazy wykonało dokładnie to, co zapisano w kodzie.

Stan przed zapisem; numery w liniach zlecenia pochodzą z kodu przykładu.

Blok kodu zaczyna się od tabel i danych, a za nimi stoją trzy instrukcje UPDATE i dwa zapytania SELECT. Każda z instrukcji UPDATE ma własną rolę.

CREATE TABLE dbo.linia_przyklad (linia int PRIMARY KEY, numer_zam varchar(20) NULL);
CREATE TABLE dbo.pozycja_przyklad (id int IDENTITY PRIMARY KEY, numer varchar(20) NOT NULL,
    linia int NOT NULL, numer_zam varchar(20) NULL, status char(1) NOT NULL DEFAULT '0');
INSERT INTO dbo.linia_przyklad VALUES (1, 'ZK-1'), (2, 'ZK-2'), (3, 'ZK-3');
INSERT INTO dbo.pozycja_przyklad (numer, linia, numer_zam)
VALUES ('DOK-1', 1, 'ZK-1'), ('DOK-1', 2, 'ZK-9'), ('DOK-1', 3, NULL);
DECLARE @numer varchar(20) = 'DOK-1', @numer_zam varchar(20) = '';
UPDATE dbo.pozycja_przyklad SET status = '1', numer_zam = @numer_zam WHERE numer = @numer;
SELECT linia, numer_zam, status FROM dbo.pozycja_przyklad ORDER BY linia;
UPDATE dbo.pozycja_przyklad SET status = '0',
    numer_zam = CASE linia WHEN 1 THEN 'ZK-1' WHEN 2 THEN 'ZK-9' END;
UPDATE p SET status = '1',
    numer_zam = COALESCE(NULLIF(LTRIM(RTRIM(p.numer_zam)), ''),
        (SELECT TOP (1) NULLIF(LTRIM(RTRIM(z.numer_zam)), '') FROM dbo.linia_przyklad AS z
         WHERE z.linia = p.linia))
FROM dbo.pozycja_przyklad AS p WHERE p.numer = @numer;
SELECT linia, numer_zam, status FROM dbo.pozycja_przyklad ORDER BY linia;

Przykład ilustracyjny, nazwy wymyślone. Stary zapis nagłówka, przywrócenie stanu wyjściowego i zapis z COALESCE.

1. Pierwszy UPDATE zapisuje nagłówek: ustawia status 1 i wpisuje do wszystkich pozycji pustą wartość z pola nagłówka. Następny SELECT pokazuje wynik.
2. Drugi UPDATE przywraca stan wyjściowy: status 0, numery ZK-1 i ZK-9, a trzecia pozycja znów bez numeru.
3. Trzeci UPDATE to wzorzec z COALESCE, a ostatni SELECT pokazuje numery po nim.

Najprostszy sposób usuwa kolumnę numeru z UPDATE nagłówka

Pierwszy sposób naprawy polega na usunięciu kolumny numeru z polecenia UPDATE, które zapisuje nagłówek. Wywołanie może dalej przekazywać wartość numeru, ale zapytanie jej nie używa, więc pozycje zachowują własne numery. Rozwiązanie jest najtańsze, bo nie wymaga zmiany układu wywołania ani kodu po stronie aplikacji.

To rozwiązanie ma jednak granicę. Usuwa tylko skutek uboczny zapisu nagłówka i niczego nie uzupełnia. Pozycja, która nie dostała numeru przy dodaniu, zostaje bez numeru, nawet gdy jej linia zlecenia ma numer. Dlatego drugi wzorzec dokłada do zapisu uzupełnianie braków z linii zlecenia.

• Zaleta: najtańsza zmiana, bo wywołanie zostaje bez zmian.
• Granica: pozycja bez numeru zostaje bez numeru.

COALESCE decyduje, które źródło numeru wygrywa

Funkcja COALESCE zwraca pierwszą wartość różną od NULL z listy argumentów, więc kolejność argumentów rozstrzyga o pierwszeństwie źródeł. Obie wartości są najpierw oczyszczane wyrażeniem NULLIF(LTRIM(RTRIM(...)), ''), które traktuje spacje i pusty tekst jak brak wartości. Bez tego pusty tekst wygrywałby jako wartość niepusta i COALESCE nie przeszedłby do kolejnego źródła.

W przykładzie pierwszym argumentem jest numer z pozycji, a drugim podzapytanie, które bierze numer z linii zlecenia o tym samym identyfikatorze linii. Pierwsza pozycja zachowuje ZK-1, druga zachowuje ZK-9, a trzecia, która numeru nie miała, dostaje ZK-3 z linii.

Numer z pozycji zostaje, puste miejsce uzupełnia linia zlecenia.

Schemat zestawia oba wzorce na tych samych trzech pozycjach. Stary zapis zostawia puste numery, a COALESCE zachowuje numery z pozycji i dopisuje brakujący numer z linii zlecenia.

Pierwszeństwo ma numer z pozycji, a linia zlecenia uzupełnia braki

We wzorcu z COALESCE numer z nagłówka nie jest używany w ogóle. Pierwsze źródło to numer wpisany na pozycji, drugie to numer z linii zlecenia. Taka kolejność chroni numer poprawiony ręcznie: gdyby pierwsza była linia zlecenia, numer wpisany na pozycji mógłby zostać zastąpiony numerem z linii.

Linia zlecenia pełni więc rolę zapasu, a nie nadrzędnego źródła. Dla procesu, w którym osoba zakładająca dokument poprawia numer na pozycji, jest to ważna różnica. Osobny UPDATE może dodatkowo uzupełniać numer z linii zlecenia tylko tam, gdzie pole pozycji jest puste, a linia ma numer.

• Pierwsze źródło: numer wpisany na pozycji.
• Drugie źródło: numer z linii zlecenia.

Zapis nagłówka w tym wzorcu nie dotyka pozycji, które mają własny numer, i uzupełnia wyłącznie puste. Kolejność źródeł jest jedyną regułą, którą trzeba pamiętać. Rozstrzyga ją kolejność argumentów COALESCE.

Pusta wartość w nagłówku nie zeruje pozycji

W przykładzie pole nagłówka jest puste, a mimo to po zapisie z COALESCE linia 1 ma numer ZK-1, linia 2 numer ZK-9, a linia 3 numer ZK-3, wszystkie ze statusem 1. Pusta wartość w nagłówku nie wchodzi w ogóle do wyniku, bo wzorzec nie używa jej jako źródła.

Tabela zestawia numery na trzech pozycjach przed zapisem, po starym zapisie i po zapisie z COALESCE.

Numery po obu zapisach przy pustym polu nagłówka.

Przykład (dane wymyślone): dokument DOK-1 ma trzy pozycje, a osoba zakładająca dokument wpisała na dwóch z nich numer zamówienia klienta, odpowiednio ZK-1 i ZK-9. Trzecia pozycja jest pusta, a jej linia zlecenia ma numer ZK-3. Pole nagłówka jest puste. Stary zapis nagłówka ustawia pusty numer na wszystkich trzech pozycjach. Wzorzec z COALESCE zostawia ZK-1 i ZK-9 i uzupełnia trzecią pozycję numerem ZK-3 z linii.

NULLIF odbiera możliwość świadomego wyczyszczenia numeru

Wzorzec ma dwie granice. Pierwsza dotyczy NULLIF: skoro pusty numer jest traktowany jak brak, pozycji nie da się świadomie wyczyścić tym zapisem. Pusty numer zawsze wraca do wartości z linii zlecenia. Jeśli proces wymaga czyszczenia numeru, potrzebny jest osobny zapis, który omija ten wzorzec.

Druga granica dotyczy filtra statusu. Podzapytanie z linią zlecenia bierze pierwszy wiersz o tym samym identyfikatorze linii, bez ORDER BY, a sam UPDATE nie ma filtra statusu pozycji. TOP (1) bez ORDER BY jest bezpieczne tylko przy jednoznacznym kluczu linii, bo przy dwóch pasujących wierszach wynik nie jest określony. Zapis obejmuje więc wszystkie pozycje dokumentu, także te o statusie usuniętym, jeśli tabela takie ma.

• Brak świadomego wyczyszczenia: pusty numer wraca z linii zlecenia.
• Brak filtra statusu: UPDATE obejmuje wszystkie pozycje dokumentu.

Sposób sprawdzenia jest prosty: dwa zapytania z przykładu uruchamia się w jednorazowej bazie na poziomach zgodności 130 i 160 i porównuje wyniki z tabelą. Po teście usuwa się bazę. Numer zamówienia po stronie zlecenia wydania opisujemy w artykule o zleceniu wydania z numerem zamówienia. Dwa pokrewne wzorce T-SQL opisują artykuły o sortowaniu liczbowym kodów zapisanych jako tekst i o QUOTED_IDENTIFIER w procedurach.

Pytania do analizy

Przed wdrożeniem takiego zapisu odpowiedz na cztery pytania.

1. Kiedy pusty parametr nagłówka powinien wyczyścić numer, a kiedy nie, i co przesądza o kolejności źródeł w COALESCE?
2. Czy NULLIF, który nie pozwala wyczyścić numeru, jest akceptowalnym kosztem?
3. Jak zabezpieczyć UPDATE przed pozycjami o statusie usuniętym, skoro wzorzec nie filtruje po statusie?
4. Czy podzapytanie TOP (1) bez ORDER BY jest bezpieczne przy jednoznacznym kluczu linii?