Zmiana zapytań zapisanych w bazie: skrót treści oraz tryb próbny z wycofaniem

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

Zmiana zapytań zapisanych w tabeli konfiguracji SQL Server wymaga takiej samej dyscypliny jak wdrożenie kodu, bo aplikacja pobiera ich treść z bazy. Opisujemy technikę wdrożeniową z portalu awizacji Studio VSS.net w zakładzie produkcyjnym.

To przypadek jednego wdrożenia, a nie opis standardu produktu. Kod napisaliśmy od nowa na podstawie skryptu sprawdzonego na SQL Server 2022, z nazwami przykładowymi.

Bezpieczna zmiana zapytań w tabeli konfiguracji to skrypt, który przed podmianą liczy skrót SHA2_256 każdej treści i zatrzymuje się przy niezgodności. Działa domyślnie w trybie próbnym, który wykonuje całą zmianę i cofa ją na końcu. Osobny skrypt wycofujący też sprawdza skrót, zanim cokolwiek przywróci.

Dlaczego zmiana zapytania w tabeli konfiguracji to wdrożenie kodu

W opisywanym wdrożeniu aplikacja nie zawiera treści zapytań. Wywołuje je po identyfikatorze, a treść pobiera z tabeli konfiguracji, którą opisuje strona o repozytorium zapytań. Zmiana treści w tabeli zmienia działanie aplikacji bez kompilacji, więc wymaga takiej samej dyscypliny jak wdrożenie kodu.

Skrypt zmiany odpowiada na dwa pytania: czy w bazie jest dokładnie ta treść, którą analizowano, i co zrobić, gdy zmianę trzeba cofnąć. Pierwsze rozstrzyga kontrola skrótu przed podmianą, drugie osobny skrypt wycofujący. Sama zmiana najpierw działa w trybie próbnym.

Technika przydaje się, gdy do wielu zapytań trzeba dopisać wspólny element, jak funkcja z artykułu o funkcji tożsamości, i przy procedurach z harmonogramu, takich jak te z artykułu o jednym mailu na zdarzenie. Krótszy opis wdrożenia zmiany zapytań, z innym zestawem kontroli, znajdziesz w artykule o TOP 1 bez ORDER BY.

Skrót treści sprawdzany przed każdą podmianą

Skrypt liczy SHA2_256 każdej treści po usunięciu znaków CR i porównuje wynik z zapisanym w skrypcie skrótem stanu przed zmianą. Usunięcie CR sprawia, że różnice w końcach linii nie psują porównania. Przy niezgodności skrypt wypisuje identyfikator i powód, czyli brak identyfikatora w bazie albo treść zmieniona, i kończy się przez THROW 50001 z komunikatem, że nic nie zmieniono.

Skrypt wykrywa zmianę dokonaną po analizie i przed uruchomieniem. Nie chroni przed zmianą równoległą w trakcie jego działania i sprawdza wyłącznie treść wskazanych wierszy, a nie obiekty zależne, czyli procedury i widoki wywoływane przez zapytania.

Przykład używa tabeli konfiguracji z dwoma zapytaniami, którą tworzy się przed skryptem zmiany. Skróty do skryptu liczy zapytanie na stanie początkowym i na treściach po zmianie.

CREATE TABLE dbo.tabela_konfiguracji
(
    uid       varchar(50)  NOT NULL PRIMARY KEY,
    zapytanie varchar(max) NOT NULL,
    aktywne   bit          NOT NULL DEFAULT 1
);
INSERT dbo.tabela_konfiguracji (uid, zapytanie) VALUES
    ('Q-LISTA',  'SELECT numer FROM dbo.tabela_zamowien WHERE id_kontrahenta = {0}'),
    ('Q-LICZBA', 'SELECT COUNT(*) FROM dbo.tabela_zamowien');
GO

Przykład ilustracyjny, nazwy obiektów wymyślone.

SELECT uid,
       CONVERT(varchar(80), HASHBYTES('SHA2_256', REPLACE(zapytanie, CHAR(13), '')), 1) AS skrot
FROM dbo.tabela_konfiguracji;

Przykład ilustracyjny, nazwy obiektów wymyślone.

Skrót HASHBYTES zależy od typu danych: ta sama treść daje inny wynik na varchar i na nvarchar. Skrót do skryptu liczy się więc na tym typie, jaki ma kolumna z treścią, a w przykładzie jest to varchar ze znakami ASCII. W SQL Server 2014 i starszych wejście funkcji jest ograniczone do 8000 bajtów, więc dłuższe treści wymagają wersji 2016 lub nowszej.

Sześć kontroli skryptu zmiany w kolejności wykonania.

Skrypt nie podmienia treści, której skrót różni się od zapisanego, i wycofuje wyłącznie treść, która jest dokładnie tą po zmianie.

Poprawkę pojedynczego fragmentu robi się przez REPLACE, ale tylko gdy skrót całej treści zgadza się ze stanem oczekiwanym. Po zmianie skrypt sprawdza, że stary fragment zniknął, nowy jest obecny, a UPDATE zmienił jeden wiersz (błędy 50031 do 50033).

Tryb próbny z sp_set_session_context i ROLLBACK

Tryb próbny jest domyślny. O zatwierdzeniu decyduje wartość zapisana przez sp_set_session_context: zero oznacza ROLLBACK, jeden oznacza COMMIT. Żeby zapisać zmianę, ustawiasz wartość jeden i uruchamiasz skrypt jeszcze raz. Kontekst sesji przeżywa kolejne partie GO, ale tylko w tej samej sesji.

Skrypt składa się z partii rozdzielonych GO, pokazanych w kolejności wykonania. Pierwsza sprawdza bazę, druga ustawia tryb, a trzecia otwiera transakcję.

IF DB_NAME() <> N'nazwa_bazy_docelowej'
BEGIN
    RAISERROR(N'Zła baza - skrypt zatrzymany.', 16, 1);
    SET NOEXEC ON;
END
GO

EXEC sys.sp_set_session_context @key = N'ZATWIERDZ_ZMIANY', @value = 0;
GO

SET XACT_ABORT ON;
SET NOCOUNT ON;
BEGIN TRANSACTION;
GO

Przykład ilustracyjny, nazwy obiektów wymyślone.

Dyrektywa SET XACT_ABORT ON sprawia, że błąd wykonania przerywa bieżącą partię i wycofuje transakcję. Transakcja z trzeciej partii obejmuje jednak wszystkie następne aż do decyzji. Czwarta partia to właściwa zmiana.

DECLARE @zmiany TABLE
(
    uid        varchar(50)   NOT NULL PRIMARY KEY,
    hash_przed varbinary(32) NOT NULL,
    hash_po    varbinary(32) NOT NULL,
    nowa       varchar(max)  NOT NULL
);
INSERT @zmiany (uid, hash_przed, hash_po, nowa) VALUES
 ('Q-LISTA',
  0xB9D426ADE0770EC61E96A813B1AE0AE5732F7498F4CE480FB362F458902B98E6,
  0xF3619AFC6C4175F622D96E71D939B86E57360722E196E4F80BDD5EAA85635992,
  'SELECT numer FROM dbo.tabela_zamowien WHERE id_kontrahenta = {0} ORDER BY numer'),
 ('Q-LICZBA',
  0xAD0ED363097E9872D9AB48592E4B648B288DC9BA74AB9676440180D1CA0D1174,
  0x012CF8C98E964D6EF9DA386E5DAD61A39571F5C5106315884D3A0EB5F4973020,
  'SELECT COUNT_BIG(*) AS liczba FROM dbo.tabela_zamowien');

IF EXISTS (SELECT 1
           FROM @zmiany AS z
           LEFT JOIN dbo.tabela_konfiguracji AS c ON c.uid = z.uid
           WHERE c.uid IS NULL
              OR HASHBYTES('SHA2_256', REPLACE(c.zapytanie, CHAR(13), '')) <> z.hash_przed)
BEGIN
    SELECT z.uid,
           CASE WHEN c.uid IS NULL THEN N'BRAK UID W BAZIE' ELSE N'TREŚĆ ZMIENIONA' END AS problem
    FROM @zmiany AS z
    LEFT JOIN dbo.tabela_konfiguracji AS c ON c.uid = z.uid
    WHERE c.uid IS NULL
       OR HASHBYTES('SHA2_256', REPLACE(c.zapytanie, CHAR(13), '')) <> z.hash_przed;
    THROW 50001, N'Treść różni się od stanu z analizy - skrypt zatrzymany, nic nie zmieniono.', 1;
END;

UPDATE c SET c.zapytanie = z.nowa
FROM dbo.tabela_konfiguracji AS c
JOIN @zmiany AS z ON z.uid = c.uid;
IF @@ROWCOUNT <> 2
    THROW 50002, N'Liczba zmienionych wierszy różna od oczekiwanej (2).', 1;

IF EXISTS (SELECT 1
           FROM @zmiany AS z
           JOIN dbo.tabela_konfiguracji AS c ON c.uid = z.uid
           WHERE HASHBYTES('SHA2_256', REPLACE(c.zapytanie, CHAR(13), '')) <> z.hash_po)
    THROW 50003, N'Treść po zmianie różni się od oczekiwanej.', 1;
GO

Przykład ilustracyjny, nazwy obiektów wymyślone.

Zmienna tabelowa @zmiany trzyma dla każdego identyfikatora dwa skróty, sprzed zmiany i oczekiwany po niej, oraz nową treść. Po UPDATE skrypt sprawdza liczbę zmienionych wierszy, a potem dla każdej treści, czy jej skrót jest skrótem stanu po zmianie. Literały z polskimi znakami mają przedrostek N, a błędy zgłasza THROW.

IF @@TRANCOUNT = 0
BEGIN
    RAISERROR(N'Transakcja wycofana - skrypt zatrzymany.', 16, 1);
    SET NOEXEC ON;
END
GO

IF CAST(SESSION_CONTEXT(N'ZATWIERDZ_ZMIANY') AS int) = 1
BEGIN
    COMMIT TRANSACTION;
    PRINT N'ZATWIERDZONO. Teraz odśwież pamięć podręczną zapytań w aplikacji.';
END
ELSE
BEGIN
    ROLLBACK TRANSACTION;
    PRINT N'TRYB PRÓBNY - wszystko wycofane. Aby zapisać: @value = 1.';
END
GO

SET NOEXEC OFF;
GO

Przykład ilustracyjny, nazwy obiektów wymyślone.

Ostatnia partia czyta wartość z SESSION_CONTEXT i zatwierdza albo cofa transakcję. Prawdziwe jest tylko porównanie z jedynką, więc zero i brak klucza dają ROLLBACK.

Przykład: administrator poprawia dwa zapytania, w tym listę numerów zamówień ZAK-26-0042 i ZAK-26-0043 dla Dostawcy Testowego. W trybie próbnym UPDATE zmienia dwa wiersze, a ROLLBACK zostawia bazę bez zmian. Potem administrator ustawia wartość jeden i uruchamia skrypt ponownie. Gdy ktoś w międzyczasie dopisze komentarz do jednego zapytania, skrypt wypisze zmienione uid i zatrzyma się.

Strażnik transakcji i kontrola bazy docelowej

Skrypt dzielony na partie działa w jednej transakcji, ale XACT_ABORT przerywa tylko bieżącą partię. SSMS wykonuje kolejne partie dalej, już poza transakcją. Dlatego po partii ze zmianą stoi strażnik: IF @@TRANCOUNT = 0 ustawia SET NOEXEC ON, a reszta pliku jest tylko kompilowana. Bez strażnika kolejne partie wykonałyby się poza transakcją, więc to element obowiązkowy.

Kontrola bazy stoi zaraz pod nagłówkiem. Funkcja DB_NAME() daje nazwę bieżącej bazy, a przy innej skrypt zgłasza Zła baza - skrypt zatrzymany. i ustawia SET NOEXEC ON. Ostatnie linie, SET NOEXEC OFF i GO, chronią okno SSMS przed zostaniem w trybie tylko kompilacji. Nazwę nazwa_bazy_docelowej trzeba podmienić na własną, bo do tego czasu skrypt się zatrzymuje.

Z wiersza poleceń uruchamiamy skrypt przez sqlcmd. Przełącznik -I włącza QUOTED_IDENTIFIER, o czym piszemy w artykule o procedurze zapisanej z QUOTED_IDENTIFIER OFF, a -f 65001 czyta plik w UTF-8.

sqlcmd -S nazwa_serwera -d nazwa_bazy -E -b -I -f 65001 -i plik.sql

Przykład ilustracyjny, nazwy serwera i bazy wymyślone.

Bez opcji -d sqlcmd łączy się z domyślną bazą konta, często master, więc kontrola bazy zatrzymuje skrypt. Komunikat Zła baza i niezerowy kod wyjścia oznaczają, że skrypt się nie wykonał.

Skrypt wycofujący, który też sprawdza skrót

Skrypt wycofujący ma tę samą budowę co skrypt zmiany. Przywraca stare treści tylko wtedy, gdy w bazie jest dokładnie treść po zmianie, co sprawdza porównanie skrótów, a w przeciwnym razie zatrzymuje się przez THROW 50011. Po przywróceniu sprawdza liczbę wierszy (50012) i zgodność ze stanem sprzed zmiany (50013).

Wycofanie też działa domyślnie w trybie próbnym, a jego klucz kontekstu ma inną nazwę, więc zapis wymaga wartości jeden w tym skrypcie.

IF DB_NAME() <> N'nazwa_bazy_docelowej'
BEGIN
    RAISERROR(N'Zła baza - skrypt zatrzymany.', 16, 1);
    SET NOEXEC ON;
END
GO

EXEC sys.sp_set_session_context @key = N'ZATWIERDZ_WYCOFANIA', @value = 0;
GO

SET XACT_ABORT ON;
SET NOCOUNT ON;
BEGIN TRANSACTION;
GO

Przykład ilustracyjny, nazwy obiektów wymyślone.

DECLARE @zmiany TABLE
(
    uid        varchar(50)   NOT NULL PRIMARY KEY,
    hash_po    varbinary(32) NOT NULL,
    hash_przed varbinary(32) NOT NULL,
    stara      varchar(max)  NOT NULL
);
INSERT @zmiany (uid, hash_po, hash_przed, stara) VALUES
 ('Q-LISTA',
  0xF3619AFC6C4175F622D96E71D939B86E57360722E196E4F80BDD5EAA85635992,
  0xB9D426ADE0770EC61E96A813B1AE0AE5732F7498F4CE480FB362F458902B98E6,
  'SELECT numer FROM dbo.tabela_zamowien WHERE id_kontrahenta = {0}'),
 ('Q-LICZBA',
  0x012CF8C98E964D6EF9DA386E5DAD61A39571F5C5106315884D3A0EB5F4973020,
  0xAD0ED363097E9872D9AB48592E4B648B288DC9BA74AB9676440180D1CA0D1174,
  'SELECT COUNT(*) FROM dbo.tabela_zamowien');

IF EXISTS (SELECT 1
           FROM @zmiany AS z
           LEFT JOIN dbo.tabela_konfiguracji AS c ON c.uid = z.uid
           WHERE c.uid IS NULL
              OR HASHBYTES('SHA2_256', REPLACE(c.zapytanie, CHAR(13), '')) <> z.hash_po)
    THROW 50011, N'W bazie nie ma treści po zmianie - wycofanie zatrzymane, nic nie zmieniono.', 1;

UPDATE c SET c.zapytanie = z.stara
FROM dbo.tabela_konfiguracji AS c
JOIN @zmiany AS z ON z.uid = c.uid;
IF @@ROWCOUNT <> 2
    THROW 50012, N'Liczba przywróconych wierszy różna od oczekiwanej (2).', 1;

IF EXISTS (SELECT 1
           FROM @zmiany AS z
           JOIN dbo.tabela_konfiguracji AS c ON c.uid = z.uid
           WHERE HASHBYTES('SHA2_256', REPLACE(c.zapytanie, CHAR(13), '')) <> z.hash_przed)
    THROW 50013, N'Przywrócona treść różni się od stanu sprzed zmiany.', 1;
GO

Przykład ilustracyjny, nazwy obiektów wymyślone.

IF @@TRANCOUNT = 0
BEGIN
    RAISERROR(N'Transakcja wycofana - skrypt zatrzymany.', 16, 1);
    SET NOEXEC ON;
END
GO

IF CAST(SESSION_CONTEXT(N'ZATWIERDZ_WYCOFANIA') AS int) = 1
BEGIN
    COMMIT TRANSACTION;
    PRINT N'WYCOFANO. Teraz odśwież pamięć podręczną zapytań w aplikacji.';
END
ELSE
BEGIN
    ROLLBACK TRANSACTION;
    PRINT N'TRYB PRÓBNY - wszystko wycofane. Aby zapisać: @value = 1.';
END
GO

SET NOEXEC OFF;
GO

Przykład ilustracyjny, nazwy obiektów wymyślone.

Przed wdrożeniem wykonano też próbę całości w jednej transakcji z bezwarunkowym ROLLBACK: zdjęcie stanu do tabel tymczasowych, które przeżywają ROLLBACK, zmiana, wycofanie i porównanie stanu przed i po.

Pamięć podręczna oraz kolejność wycofań

Kolejność wycofywania ma znaczenie. Wycofanie wcześniejszej zmiany sprawdza skrót sprzed późniejszej, więc późniejszą trzeba wycofać pierwszą. W opisywanym wdrożeniu ostrzeżenie o tym stoi w nagłówku skryptu.

Pamięć podręczna zapytań jest osobna dla każdej instancji aplikacji, więc reset na jednej nie odświeża pozostałych. Poniższa kolejność po zapisie zmiany to przykład z konkretnego wdrożenia, a nie element mechanizmu skryptu.

1. Reset pamięci zapytań na każdej instancji zaraz po zapisie zmiany treści.
2. Wgranie pliku skryptu na serwer.
3. Podbicie wersji pliku w adresie transakcji osobnym skryptem.
4. Ponowny reset pamięci zapytań na każdej instancji.

Wersję podbija się dopiero po wgraniu pliku, bo inaczej przeglądarki zapamiętają stary plik pod nową wersją. W całym wdrożeniu są więc dwa resety pamięci na każdej instancji. Zmiany w tabeli limitów z artykułu o ocenie atestu w T-SQL wdraża się tą samą techniką.

Dziesięć scenariuszy, na których sprawdzono przykład

Skrypt, na którym oparliśmy przykład, sprawdzono wykonawczo na SQL Server 2022 w dziesięciu scenariuszach. Skróty zapisane w przykładzie zgadzają się z treściami początkowymi i po zmianie.

Dziesięć scenariuszy i oczekiwane wyniki.

Całość wymaga SQL Server 2016, bo korzysta z SESSION_CONTEXT.

Cztery pytania przed wdrożeniem zmiany zapytań

Przed zmianą zapytań zapisanych w bazie odpowiedz na cztery pytania.

• Skrót: skąd wiemy, że w bazie jest dokładnie ta treść, którą analizowano, i czy sprawdzamy to skrótem przed każdą podmianą.
• Próba: czy mamy tryb próbny, który wykonuje całą zmianę i cofa ją na końcu, a jego wynik jest raportem do przejrzenia.
• Wycofanie: czy skrypt wycofujący wypróbowano przed wdrożeniem i czy sprawdza stan po zmianie, zanim cokolwiek przywróci.
• Pamięć: które pamięci podręczne i wersje plików trzeba odświeżyć po zmianie oraz w jakiej kolejności na każdej instancji.