Ocena atestu w T-SQL: model tylko czyta, procedura liczy

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

OPENJSON i porównanie z limitami w T-SQL pozwalają odebrać modelowi ocenę zgodności atestu i powierzyć ją procedurze, która daje powtarzalny wynik. Artykuł opisuje architekturę referencyjną dla portalu awizacji Studio VSS.net, czyli projekt do zbudowania.

To nie jest opis wdrożenia ani procedura, którą ma klient. Kod jest przykładem z danymi wymyślonymi i opieramy go na skrypcie sprawdzonym na SQL Server 2022.

W tej architekturze model tylko czyta atest i zwraca fakty w JSON, bez oceniania. Procedura T-SQL zamienia je funkcją OPENJSON w tabelę i porównuje z tabelą limitów, a dla każdej cechy wylicza wynik. Werdykt wynika z dwóch zapytań EXISTS, a ostatnie słowo ma człowiek z działu jakości.

Dlaczego ocenę warto oddzielić od odczytu dokumentu

W opisywanym wdrożeniu model otrzymuje atest i zwraca JSON z oceną każdej cechy, a kod aplikacji przelicza z tych wyników tylko werdykt końcowy. Wartości liczbowe z atestu porównuje z wymaganiami sam model, a kod liczb nie sprawdza. Według dokumentacji wdrożenia wymagania są częścią instrukcji po stronie usługi modelu, więc aplikacja nie dokleja do polecenia treści wzorców. Przy ponowieniu po uszkodzonym JSON polecenie dostaje krótką uwagę o poprawnej składni.

Przy rosnącej liczbie wzorców wymagań wygodniej oddzielić odczyt dokumentu przez model od oceny zgodności liczonej w procedurze. Instrukcja z wymaganiami rośnie z każdym nowym wzorcem, a porównanie liczby z limitem da się wykonać zwykłym zapytaniem. Dalsza część artykułu to architektura referencyjna, czyli projekt do zbudowania, a nie opis wdrożenia klienta.

Odczyt atestu i raport w opisywanym wdrożeniu opisuje artykuł o sztucznej inteligencji, która czyta atest. Wzorzec wymagań jako dokument, który rozumie model, omawia artykuł o wzorcu wymagań materiałowych.

Projekt zakłada dalsze elementy, których dziś nie ma. Wzorce byłyby danymi z rewizjami, a dział jakości dostałby formularz, w którym zakład sam wprowadza nowe rewizje. Wzorzec łączyłaby z materiałem reguła przy grupie kartotek, a przetwarzanie odbywałoby się w tle w kolejce. Wynik zapisywano by przy załączniku, a poprawki działu jakości tworzyłyby zestaw porównawczy.

Model zwraca fakty w JSON i nic więcej

W architekturze referencyjnej model tylko czyta atest i zwraca fakty, bez oceniania. Zwracane pola tworzą pięć grup, które zestawiamy w tabeli.

Zestawienie faktów, które model odczytuje z atestu i zwraca bez oceny.

Dobór wzorca odbywa się w kodzie, a nie w modelu. Najpierw brany jest numer wzorca z atestu, a gdy go brak, wybór użytkownika albo wzorzec z kartoteki. Na końcu działają reguły oparte na cechach materiału, takich jak postać i wymiar. Przy niejednoznaczności system pyta użytkownika, zamiast zgadywać.

Odpowiedź ma w nagłówku kod wzorca i rewizję. Procedura czyta je funkcją JSON_VALUE, a tablicę cech funkcją OPENJSON. Odpowiedź uszkodzona, której ISJSON nie uzna za JSON, kończy się wyjątkiem, zanim cokolwiek zostanie porównane. Podobne wzorce odczytu i zapisu JSON w procedurach pokazuje artykuł o API na procedurach SQL i JSON.

Limity leżą w tabeli, a nie w tekście polecenia

Tabela limitów zna pięć typów cech: liczbę z minimum i maksimum, wymagane, zwolnienie, jedno z listy oraz wariant. Przykład realizuje pierwsze cztery typy. Wariant jest elementem propozycji, którego w przykładzie nie ma.

Pięć typów cech z propozycji. Przykład kodu implementuje cztery pierwsze.

Klucz główny tabeli ma trzy kolumny: wzorzec, rewizję i cechę, więc nowa rewizja to nowe wiersze, a nie zmiana kodu. Pole dozwolone trzyma listę po przecinku dla typu jedno_z. Puste wartosc_min oznacza brak dolnej granicy. Działa to dzięki logice trójwartościowej: porównanie z NULL daje wynik nieznany, więc sama górna granica rozstrzyga o niezgodności.

CREATE TABLE dbo.limity_wzorca (
    wzorzec     varchar(20)   NOT NULL,
    rewizja     int           NOT NULL,
    cecha       varchar(50)   NOT NULL,
    typ         varchar(12)   NOT NULL,
    wartosc_min decimal(18,4) NULL,
    wartosc_max decimal(18,4) NULL,
    dozwolone   nvarchar(200) NULL,
    CONSTRAINT pk_limity_wzorca PRIMARY KEY (wzorzec, rewizja, cecha)
);
GO
INSERT dbo.limity_wzorca (wzorzec, rewizja, cecha, typ, wartosc_min, wartosc_max, dozwolone) VALUES
 ('WZ-01', 2, 'Cecha A', 'liczba', NULL, 2.5, NULL),
 ('WZ-01', 2, 'Cecha B', 'liczba', 40, 60, NULL),
 ('WZ-01', 2, 'Typ dokumentu', 'jedno_z', NULL, NULL, '3.1,3.2'),
 ('WZ-01', 2, 'Certyfikat', 'wymagane', NULL, NULL, NULL),
 ('WZ-01', 2, 'Badanie dodatkowe', 'zwolnienie', NULL, NULL, NULL);
GO

Przykład ilustracyjny, dane wymyślone.

Zmiany limitów wdraża się tak jak każdą zmianę treści w tabeli konfiguracji, czyli z kontrolą skrótu i trybem próbnym, które opisuje artykuł o zmianie zapytań zapisanych w bazie.

Procedura porównuje fakty z limitami

Ocenę wykonuje procedura T-SQL, która parsuje JSON i łączy go z tabelą limitów. Jedno zapytanie obsługuje wszystkie wzorce, bez rozgałęzień na wzorzec. Poniżej cała procedura.

CREATE PROCEDURE dbo.ocen_atest @json nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    IF ISJSON(@json) = 0 THROW 50001, 'Odpowiedz modelu nie jest poprawnym JSON.', 1;

    DECLARE @wzorzec varchar(20) = JSON_VALUE(@json, '$.wzorzec');
    DECLARE @rewizja int         = TRY_CONVERT(int, JSON_VALUE(@json, '$.rewizja'));
    IF NOT EXISTS (SELECT 1 FROM dbo.limity_wzorca WHERE wzorzec = @wzorzec AND rewizja = @rewizja)
        THROW 50002, 'Brak limitow dla podanego wzorca i rewizji.', 1;

    ;WITH odczyt AS (
        SELECT j.cecha, j.wartosc,
               TRY_CONVERT(decimal(18,4), REPLACE(j.wartosc, ',', '.')) AS liczba
        FROM OPENJSON(@json, '$.cechy')
             WITH (cecha varchar(50) '$.cecha', wartosc nvarchar(100) '$.wartosc') AS j
    )
    SELECT l.cecha, l.typ, o.wartosc,
           CASE
               WHEN l.typ = 'zwolnienie'                          THEN 'nie dotyczy'
               WHEN NULLIF(o.wartosc, '') IS NULL                 THEN 'brak danych'
               WHEN l.typ = 'wymagane'                            THEN 'zgodny'
               WHEN l.typ = 'liczba' AND o.liczba IS NULL         THEN 'do weryfikacji'
               WHEN l.typ = 'liczba'
                    AND (o.liczba < l.wartosc_min OR o.liczba > l.wartosc_max) THEN 'niezgodny'
               WHEN l.typ = 'liczba'                              THEN 'zgodny'
               WHEN l.typ = 'jedno_z'
                    AND o.wartosc IN (SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(l.dozwolone, ',')) THEN 'zgodny'
               ELSE 'niezgodny'
           END AS wynik
    INTO #wynik
    FROM dbo.limity_wzorca AS l
    LEFT JOIN odczyt AS o ON o.cecha = l.cecha
    WHERE l.wzorzec = @wzorzec AND l.rewizja = @rewizja;

    SELECT cecha, typ, wartosc, wynik FROM #wynik ORDER BY cecha;

    SELECT CASE
             WHEN EXISTS (SELECT 1 FROM #wynik WHERE wynik = 'niezgodny')                        THEN 'niezgodny'
             WHEN EXISTS (SELECT 1 FROM #wynik WHERE wynik IN ('brak danych', 'do weryfikacji')) THEN 'czesciowo zgodny'
             ELSE 'zgodny'
           END AS werdykt;
END
GO

Przykład ilustracyjny, dane wymyślone.

Procedura zaczyna od dwóch kontroli. Pierwsza odrzuca uszkodzoną odpowiedź modelu wyjątkiem 50001. Druga sprawdza, czy tabela limitów ma w ogóle wiersze dla wzorca i rewizji z odpowiedzi, i zgłasza THROW 50002. Bez niej zapytanie nie miałoby czego porównać i zwróciłoby werdykt zgodny, bo pusty zbiór nie zawiera żadnej niezgodności.

Wyrażenie OPENJSON ... WITH zamienia tablicę cech w tabelę o typowanych kolumnach. Łączy się ją zwykłym LEFT JOIN z tabelą limitów, od strony limitów. Liczba z przecinkiem dziesiętnym, na przykład 2,1, daje po TRY_CONVERT do decimal wartość NULL, więc przecinek trzeba najpierw zamienić na kropkę przez REPLACE. Wartość nieliczbowa też daje NULL i kończy się wynikiem do weryfikacji.

Cecha, której model nie zwrócił, daje wynik brak danych, a typ zwolnienie daje nie dotyczy. Cecha zwrócona przez model, której nie ma w tabeli limitów, jest pomijana. Nazwy cech z modelu muszą więc zgadzać się z nazwami w tabeli limitów. Nieznany typ cechy w tabeli trafia do gałęzi ELSE i dostaje wynik niezgodny. Można dopisać osobną gałąź do weryfikacji.

Typ jedno_z porównuje wartość z listą rozbitą przez STRING_SPLIT. OPENJSON oraz STRING_SPLIT użyte w przykładzie wymagają poziomu zgodności bazy co najmniej 130, a ISJSON i JSON_VALUE działają także na poziomie 120. Po ustawieniu poziomu 120 na SQL Server 2022 obie funkcje dały błąd Invalid object name. Poziomy zgodności opisuje dokumentacja ALTER DATABASE i artykuł o SQL Server 2025 i poziomie zgodności bazy.

Werdykt wynika z jednej reguły zliczania

Werdykt wynika z dwóch zapytań EXISTS. Jeśli występuje wynik niezgodny, werdykt jest niezgodny. W przeciwnym razie, gdy występuje brak danych albo do weryfikacji, werdykt jest częściowo zgodny, a w pozostałych przypadkach zgodny. Wynik nie dotyczy nie obniża oceny.

Model wyciąga fakty z dokumentu, a o zgodności z limitem rozstrzyga zapytanie, które daje ten sam wynik za każdym razem.

Ta sama odpowiedź modelu daje za każdym razem ten sam wynik, bo porównanie jest zwykłym zapytaniem, a nie kolejnym wywołaniem modelu. Wywołanie na danych przykładowych zwraca pięć wierszy, po jednym na wiersz limitów, choć odpowiedź modelu ma trzy cechy. Cecha Certyfikat nie wystąpiła w odpowiedzi, a zwolnienie z badania dodatkowego nie jest porównywane.

EXEC dbo.ocen_atest N'{"wzorzec":"WZ-01","rewizja":2,"cechy":[
  {"cecha":"Cecha A","wartosc":"2,1"},
  {"cecha":"Cecha B","wartosc":"62,4"},
  {"cecha":"Typ dokumentu","wartosc":"3.1"}]}';

Przykład ilustracyjny, dane wymyślone.

Oczekiwany wynik pierwszego zapytania procedury. Werdykt to niezgodny.

Scenariusz planowanej architektury: model odczytuje atest przykładowej dostawy i zwraca JSON z kodem wzorca WZ-01 i rewizją 2 oraz trzema wartościami cech. Aplikacja wywołuje procedurę z tym JSON i dostaje pięć wierszy wyników oraz werdykt. Cecha B (62,4) leży poza zakresem, więc wynik jest niezgodny, a Certyfikat nie wystąpił w odpowiedzi, więc ma wynik brak danych. Człowiek z działu jakości widzi wynik w raporcie i po poprawieniu atestu dostaje ten sam wynik przy każdym wywołaniu.

Drugi blok sprawdza dwa kolejne przypadki. Przy wartości 55,2 dla Cechy B i dopisanym Certyfikacie werdykt to zgodny, a nieznany wzorzec kończy się błędem 50002.

EXEC dbo.ocen_atest N'{"wzorzec":"WZ-01","rewizja":2,"cechy":[
  {"cecha":"Cecha A","wartosc":"2,1"},
  {"cecha":"Cecha B","wartosc":"55,2"},
  {"cecha":"Typ dokumentu","wartosc":"3.1"},
  {"cecha":"Certyfikat","wartosc":"jest"}]}';

EXEC dbo.ocen_atest N'{"wzorzec":"WZ-99","rewizja":1,"cechy":[]}';

Przykład ilustracyjny, dane wymyślone.

Co zostaje modelowi, a co przejmuje baza

Model zostaje przy cechach opisowych, takich jak oznakowanie i metoda badania. Procedura ich nie ocenia, bo da się je opisać słowami, a nie liczbami i listami w tabeli limitów. Ekran tylko wyświetla wynik.

Wynik oceny nie jest gwarancją zgodności materiału. Raport przypomina, że ocena wymaga potwierdzenia przez dział jakości, a procedura zmienia tylko to, kto liczy porównanie, a nie to, kto za nie odpowiada.

Architektura jest projektem do wyceny, a nie opisem istniejącej procedury. Nie podajemy dla niej terminów ani kolejności prac. Podobny wzorzec z unikalnym kluczem zdarzenia opisujemy w artykule o jednym mailu na zdarzenie, a wzorzec, w którym zapytania filtruje konto z sesji, w artykule o funkcji tożsamości.

Cztery pytania przed budową własnej oceny w T-SQL

Przed zbudowaniem podobnej oceny odpowiedz na cztery pytania.

• Cechy: które cechy da się opisać liczbami i listami w tabeli limitów, a które wymagają czytania opisu przez model.
• Wzorzec: jak i kiedy dobierać wzorzec do atestu, aby niejednoznaczność kończyła się pytaniem do użytkownika, a nie zgadywaniem.
• Rewizje: kto prowadzi tabelę limitów i rewizje, i jak sprawdzić nowy wzorzec względem wcześniej ocenionych dokumentów, zanim trafi do użycia.
• Wynik: czy wynik oceny ma być zapisywany razem z dokumentem i jak wykorzystać poprawki działu jakości jako zestaw porównawczy.