Сохранение вложенных сущностей с помощью SQL Server

#sql #sql_server

Допустим есть класс со свойством того же типа (опишу на C#, но язык описания классов
здесь неважен)

class Exception
{
    public string Message { get; set; }

    public string StackTrace { get; set; }

    public Exception InnerException { get; set; }
}


Как видно из приведённого выше кода есть класс исключения и у него может быть вложено
исключение и так неограниченно.

Создадим таблицу для того, чтобы хранить эти исключения

CREATE TABLE Exceptions
(
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Message] NVARCHAR(MAX) NOT NULL,
    StackTrace NVARCHAR(MAX) NULL,
    InnerExceptionId INT NULL,
    CONSTRAINT FK__Exceptions_Id__Exceptions_InnerExceptionId
        FOREIGN KEY(InnerExceptionId) REFERENCES Exceptions (Id)
);


Конечно, я могу написать код, который хоть как-то выполняется: к примеру, можно написать
хранимую процедуру, которая принимает аргументом TVP, затем пройтись циклом по строкам
TVP и вставлять записи одну за другой в таблицу Exceptions.

Но есть ли действительно изящный код SQL, который будет делать вставку исключений
с вложениями?
    

Ответы

Ответ 1

Создайте коллекцию для временного хранения строк. Добавте уникальный сурогатный ключ,
например, id/rowno. На основе этого ключа будет строится иерархическая зависимость
между отдельными строчками. Значение ключа может каждый раз начинаться с 1, это не
имеет особого значения.    
Заполните коллекцию данными.
Вставте все строчки в постоянную таблицу Exceptions с новым сгенерированным значением
IDENTITY сохраняя связь со значением сурогатного ключа id/rowno из коллекции.  
Конвертируйте ранее созданую иерархическую зависимость на основе сурогатного ключа
id/rowno из коллекции в зависимости основанные на значениях IDENTITY. Обновите таблицу. 


Итак, предположим @original_list будет входной аргумент. Теперь нужено соединить
src.id с dst.id после вставки - MERGE справится с этим лучше всего. Потом останется
только сконвертировать старые значения ключей в новые.
Все команды set-based, никаких циклов: 

DECLARE @original_list TABLE (
  surrogate_no int not null,
  msg varchar(100) not null,
  inner_surrogate_no int null
);

insert into @original_list (surrogate_no, msg, inner_surrogate_no)
values
  (1000, 'err 1000', null),
  (1010, 'err 1010->1000', 1000),
  (1020, 'err 1020', null),
  (1030, 'err 1030->1010', 1010)

-- args prepared, starting migration

DECLARE @migration TABLE (
  src_id int not null,
  dst_id int not null
)

merge Exceptions t
using @original_list s
on 1=0 --<< we are not looking for updates
when not matched by target then
  insert (message) 
  values (s.msg)
output s.surrogate_no, inserted.id ---<<< here is the main trick: src.id and matching
dst.id
into @migration(src_id, dst_id)
;

-- now all error messages are inserted, but none of them have InnerExceptionId

update e set
  InnerExceptionId = mp.dst_id
from Exceptions e
inner join @migration m  --<< get original surrogate_no 
  on m.dst_id = e.id
inner join @original_list o --<< grab original row
  on o.surrogate_no = m.src_id
inner join @migration mp  --<< locate dst.id for inner_surrogate_no
  on mp.src_id = o.inner_surrogate_no


Это общее решение для подобных задач. Полностью на sqlfiddle

Вывод:

| Id |        Message | StackTrace | InnerExceptionId |
|----|----------------|------------|------------------|
|  1 |       err 1000 |     (null) |           (null) |
|  2 | err 1010->1000 |     (null) |                1 |
|  3 |       err 1020 |     (null) |           (null) |
|  4 | err 1030->1010 |     (null) |                2 |


Вывод дерева зависимостей рекурсивным CTE:

|                                        Message | Lvl | id | InnerExceptionID |
|------------------------------------------------|-----|----|------------------|
|                                       err 1000 |   1 |  1 |           (null) |
|                                       err 1020 |   1 |  3 |           (null) |
|                    err 1010->1000>>>(err 1000) |   2 |  2 |                1 |
| err 1030->1010>>>(err 1010->1000>>>(err 1000)) |   3 |  4 |                2 |


PS Заметьте, sqlfiddle не позволяет выполнить MERGE внутри большого скрипта (каждый
раз возникала ошибка о недостающем ;), пришлось переделать @ таблицы в постоянные таблицы,
а также вполнять MERGE в динамическом SQL. На реальном БД сервере в этом не будет необходимости.

Перевод ответа originally by @IvanStarostin


Ответ 2

В итоге создал хранимую процедуру с TVP и циклы через курсор.

Вот определение табличного параметра (table-valued parameter или TVP):

CREATE TYPE ExceptionTableType AS TABLE
(
    [Message] NVARCHAR(MAX) NOT NULL,
    StackTrace NVARCHAR(MAX) NULL
);


Вот моя хранимая процедура с циклом через курсор

CREATE PROCEDURE LogException
    @exceptions ExceptionTableType READONLY
AS
BEGIN
    DECLARE @cursor CURSOR;

    DECLARE @message NVARCHAR(MAX);
    DECLARE @stackTrace NVARCHAR(MAX);

    DECLARE @innerExceptionId INT = NULL;

    DECLARE @outputTable TABLE (Id INT);

    BEGIN
        SET @cursor = CURSOR FOR
        SELECT [Message], StackTrace
        FROM @exceptions;

        OPEN @cursor
        FETCH NEXT FROM @cursor
        INTO @message, @stackTrace;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            INSERT INTO Exceptions
            ([Message], StackTrace, InnerExceptionId)
            OUTPUT INSERTED.Id INTO @outputTable (Id)
            VALUES
            (@message, @stackTrace, @innerExceptionId);

            SELECT @innerExceptionId = Id
            FROM @outputTable;

            FETCH NEXT FROM @cursor
            INTO @message, @stackTrace;
        END;

        CLOSE @cursor;
        DEALLOCATE @cursor;
    END
END


Пример вызова хранимой процедуры через SQL:

DECLARE @exceptions AS ExceptionTableType;

INSERT INTO @exceptions
([Message], [StackTrace])
VALUES
('My exception', 'Some stack trace here'),
('My inner exception', 'Dummy data'),
('My inner exception 2', 'Dummy data 2');

EXEC LogException @exceptions;