Как проверить “составной” объект на уникальность?

#база_данных #sql_server

Стоит задача организовать создание и хранение уникальных сборок инструментов. Под
сборкой инструмента подразумевается набор уникальных инструментов(фрезы, сверла и т.п.).

Структуру бд я представляю как то так:


Справочник Инструментов;
Справочник Сборок;
Таблица для связи двух вышеуказанных справочников(многие ко многим: ToolId & AssemblyId)


Сборка считается уникальной на основании инструментов в неё входящих, т.е. не должно
быть несколько сборок состоящих из одинаковых позиций.

Например, сборка №1 состоит из следующих позиций, Патрон №1, Фреза №5. Необходимо
запретить создание сборок состоящих только из Патрон №1 и Фреза №5 при наличии существующей
сборки, но можно создать сборку №2 состоящую из Патрон №1, Фреза №5, Удлинитель №2.

Можно ли как то это сделать на стороне бд?
    

Ответы

Ответ 1

Сборки можно хранить в виде дерева, где у каждой сборки будет указан последний инструмент
и ссылка на сборку без этого инструмента:

Сборка1 = Сборка2 + Инструмент


Если нет двух одинаковых пар (Сборка, Инструмент) в БД - то все сборки будут уникальными.

create table assemblies (
  id bigint identity(0,1) primary key,
  tool bigint not null foreign key references tools(id),
  parent bigint null foreign key references assemblies(id),

  unique (parent, tool)
)


Но тут есть проблема - данное представление будет считать сборки "Инструмент1 + Инструмент2"
и "Инструмент2 + Инструмент1" различными. Для того, чтобы проверялась уникальность
без учета порядка - надо принудительно упорядочить инструменты внутри сборки по номеру.

Для того, чтобы не пропустить неверно упорядоченные сборки - скопируем поле tool
родительской сборки в дочернюю, проверив корректность при помощи составного внешнего ключа:

create table assemblies (
  id bigint identity(0,1) primary key,
  tool bigint not null foreign key references tools(id),

  parent bigint null,
  parenttool bigint null,

  -- Две строки ниже нужны чтобы нельзя было установить некорректное значение parenttool 
  foreign key (parent, parenttool) references assemblies(id, tool),
  check (parent is null and parenttool is null or parent is not null and parenttool
is not null),

  -- Собственно, проверка уникальности сборок
  check (tool > parenttool or parenttool is null),
  unique (parent, tool)
)


Ответ 2

Предлагаю сделать сериализацию, т.е. собрать строку вида "ToolId1,ToolId2,ToolId3"
для каждого значения AssemblyId. 

Эти строки уже можно проверять на уникальность. Важно, чтобы ID в списке был отсортированы.


Ответ 3

Я немного усложню постановку задачи, относительно приведённой в вопросе, с целью
обобщения.

Итак, пусть есть некая составная сущность, информация о которой может храниться в
нескольких таблицах.

В моём примере это будут таблицы:


Assemblies - сборки


CREATE TABLE [Assemblies]
(
    Id int IDENTITY(1, 1) NOT NULL,
    Name nvarchar(100) NOT NULL,
    YearAssembled smallint NOT NULL,
    CONSTRAINT PK_Assemblies PRIMARY KEY (Id)
);



AssemblyTools - инструменты, содержащиеся в сборке


CREATE TABLE [AssemblyTools]
(
    AssemblyId int NOT NULL,
    ToolId int NOT NULL,
    CONSTRAINT PK_AssemblyTools PRIMARY KEY (AssemblyId, ToolId),
    CONSTRAINT FK_AssemblyTools_Assemblies FOREIGN KEY (AssemblyId)
        REFERENCES [Assemblies] (Id) ON DELETE CASCADE
);



я добавлю ещё таблицу AssemblyControllers - системы управления, содержащиеся в сборке


CREATE TABLE [AssemblyControllers]
(
    AssemblyId int NOT NULL,
    ControllerId int NOT NULL,
    CONSTRAINT PK_AssemblyControllers PRIMARY KEY (AssemblyId, ControllerId),
    CONSTRAINT FK_AssemblyControllers_Assemblies FOREIGN KEY (AssemblyId)
        REFERENCES [Assemblies] (Id) ON DELETE CASCADE
);


(предположим также, что у нас есть таблицы Tools и Controllers, но их определения
и внешние ключи в AssemblyTools и AssemblyControllers на них ссылающиеся я не привожу,
т.к. для примера это несущественно).

И пусть есть некий признак уникальности, который также может быть составным и может
быть распределён по нескольким таблицам.

В моём примере это будет совокупность следующих свойств:


год сборки (столбец YearAssembled в таблице Assemblies)
набор инструментов в сборке (подмножество записей в AssemblyTools)
набор систем управления в сборке (подмножество записей в AssemblyControllers)


Создание составной сущности логично инкапсулировать в хранимую процедуру, которая,
затем, и должна использоваться приложениями для их создания.

У меня будет единая процедура для создания и изменения сборки со следующей сигнатурой

CREATE PROCEDURE [dbo].[SetAssemblyInfo]
(
    @id int = NULL,
    @assemblyInfo xml
)
AS
...


где


@id - идентификатор сборки, которую нужно изменить (NULL, если нужно создать новую
сборку)
@assemblyInfo - информация о сборке в XML-формате следующего вида



  
    
    
    
  
  
    
    
  



Решить проблему конкуренции при создании или изменении составной сущности можно,
например, следующими способами:


блокировкой приложения (с помощью sp_getapplock)
блокировкой таблиц с помощью табличных указаний (table hints) на время операции
используя свойство блокировки диапазона ключей сериализуемых транзакций, блокировать
таблицы не полностью, а лишь небольшую их часть




Перейдём к деталям.

Добавим в таблицы данных:

SET IDENTITY_INSERT [Assemblies] ON;
INSERT INTO [Assemblies] (ID, Name, YearAssembled) VALUES
    (1, N'Assembly 1', 2011), (2, N'Assembly 2', 2012), (3, N'Assembly 3', 2013);
SET IDENTITY_INSERT [Assemblies] OFF;

INSERT INTO [AssemblyTools] VALUES
    (1, 11), (2, 22), (3, 11), (3, 22);

INSERT INTO [AssemblyControllers] VALUES
    (1, 101), (2, 202), (3, 101), (3, 202);


Нам также понадобятся несколько вспомогательных вещей.

Во-первых. Блокировать таблицы для проверки признака уникальности будем с помощью
пары табличных указаний TABLOCK и UPDLOCK:

SELECT ...
FROM [Table] WITH (TABLOCK, UPDLOCK)
WHERE /* проверяем признак по таблице */


SqlServer, к сожалению, преобразует пару TABLOCK + UPDLOCK в TABLOCKX (монопольная
блокировка таблицы). Если бы он этого не делал, то наша блокировка не мешала бы тем,
кто просто читает из таблицы, не изменяя данные.

Во-вторых, предположим, что таблица #tools (ToolId int) содержит набор инструментов
в количестве @cntT int, найти сборки с точно таким же набором инструментов мы можем
с помощью запроса

SELECT asmT.AssemblyId
FROM [AssemblyTools] asmT
    LEFT JOIN #tools t ON t.ToolId = asmT.ToolId
HAVING COUNT(1) = @cntT AND COUNT(t.ToolId) = @cntT;


(т.е. ищем сборки с тем же количеством инструментов, при этом количество совпадающих
инструментов не должно отличаться от полного количества инструментов)

Собираем всё в процедуру:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER PROCEDURE [dbo].[SetAssemblyInfo]
(
    @id int = NULL,
    @assemblyInfo xml
)
AS
BEGIN TRY
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    CREATE TABLE #tools (ToolId int PRIMARY KEY);
    CREATE TABLE #controllers (ControllerId int PRIMARY KEY);

    DECLARE @name nvarchar(100), @yearAssembled smallint;
    SET @name = @assemblyInfo.value('(/assembly/@name)[1]', 'nvarchar(100)');
    SET @yearAssembled = @assemblyInfo.value('(/assembly/@yearAssembled)[1]', 'smallint');

    -- tools
    DECLARE @cntT int;

    INSERT INTO #tools (ToolId)
    SELECT t.x.value('@id', 'int')
    FROM @assemblyInfo.nodes('/assembly[1]/tools/tool') t(x);

    SET @cntT = @@ROWCOUNT;

    -- controllers
    DECLARE @cntC int;

    INSERT INTO #controllers (ControllerId)
    SELECT t.x.value('@id', 'int')
    FROM @assemblyInfo.nodes('/assembly[1]/controllers/controller') t(x);

    SET @cntC = @@ROWCOUNT;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;

    DECLARE @existingId int;
    SELECT TOP (1)
        @existingId = a.Id
    FROM
        [Assemblies] a WITH (TABLOCK, UPDLOCK)
    WHERE
        (@id IS NULL OR a.Id != @id)
        -- проверяем признаки
        AND a.YearAssembled = @yearAssembled
        AND EXISTS (
            SELECT 1
            FROM [AssemblyTools] asmT WITH (TABLOCK, UPDLOCK)
                LEFT JOIN #tools t ON t.ToolId = asmT.ToolId
            WHERE asmT.AssemblyId = a.Id
            HAVING COUNT(1) = @cntT AND COUNT(t.ToolId) = @cntT
        )
        AND EXISTS (
            SELECT 1
            FROM [AssemblyControllers] asmC WITH (TABLOCK, UPDLOCK)
                LEFT JOIN #controllers c ON c.ControllerId = asmC.ControllerId
            WHERE asmC.AssemblyId = a.Id
            HAVING COUNT(1) = @cntC AND COUNT(c.ControllerId) = @cntC
        );

    IF @@ROWCOUNT > 0
        RAISERROR('Another assembly with same key parameters exists.', 16, 1);

    CREATE TABLE #id (Id int);

    MERGE INTO [Assemblies] t
    USING (
        VALUES (@id, @name, @yearAssembled)
    ) S (Id, Name, YearAssembled)
    ON T.Id = S.Id
    WHEN MATCHED THEN
        UPDATE
        SET
            t.Name = s.Name,
            t.YearAssembled = s.YearAssembled
    WHEN NOT MATCHED THEN
        INSERT (Name, YearAssembled)
        VALUES (s.Name, s.YearAssembled)
    OUTPUT INSERTED.Id INTO #id(Id);

    SELECT @id = Id from #id;

    WITH asmT AS (
        SELECT AssemblyId, ToolId
        FROM [AssemblyTools]
        WHERE AssemblyId = @id
    )
    MERGE INTO asmT
    USING #tools t
    ON asmT.ToolId = t.ToolId
    WHEN NOT MATCHED BY SOURCE AND asmT.AssemblyId = @id THEN
        DELETE
    WHEN NOT MATCHED THEN
        INSERT (AssemblyId, ToolId)
        VALUES (@id, t.ToolId);

    WITH asmC AS (
        SELECT AssemblyId, ControllerId
        FROM [AssemblyControllers]
        WHERE AssemblyId = @id
    )
    MERGE INTO asmC
    USING #controllers c
    ON asmC.ControllerId = c.ControllerId
    WHEN NOT MATCHED BY SOURCE AND asmC.AssemblyId = @id THEN
        DELETE
    WHEN NOT MATCHED THEN
        INSERT (AssemblyId, ControllerId)
        VALUES (@id, c.ControllerId);

    COMMIT TRANSACTION;

END TRY
BEGIN CATCH
    DECLARE @errMsg nvarchar(4000);
    SET @errMsg = ERROR_MESSAGE();

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    RAISERROR(@errMsg, 16, 1);
END CATCH




В системах с высокой конкуренцией полная блокировка таблиц, как правило, нежелательна.
Для увеличения возможного числа конкурентных транзакций за единицу времени лучше использовать
способ с частичной блокировкой.

Для этого добавим к сущности хэш от совокупности уникальных признаков:

BEGIN TRAN
GO
ALTER TABLE [Assemblies]
    ADD [Hash] binary(20) NULL;
GO
WITH asmH AS (
    SELECT
        a.[Hash],
        assemblyKeyInfo = (
            SELECT
                a.YearAssembled as '@yearAssembled',
                (
                    SELECT
                        asmT.ToolId as '@id'
                    FROM [AssemblyTools] asmT
                    WHERE asmT.AssemblyId = a.Id
                    ORDER BY asmT.ToolId
                    FOR XML PATH('tool'), ROOT('tools'), TYPE
                ),
                (
                    SELECT
                        asmC.ControllerId as '@id'
                    FROM [AssemblyControllers] asmC
                    WHERE asmC.AssemblyId = a.Id
                    ORDER BY asmC.ControllerId
                    FOR XML PATH('controller'), ROOT('controllers'), TYPE
                )
            FOR XML PATH('assembly'), TYPE
        )
    FROM [Assemblies] a
)
UPDATE asmH
SET [Hash] = HASHBYTES('SHA1', CONVERT(varbinary(max), assemblyKeyInfo));
GO
ALTER TABLE [Assemblies]
    ALTER COLUMN [Hash] binary(20) NOT NULL;
GO
COMMIT
GO


В версиях SqlServer до 2014 включительно размер данных, которые функция HASHBYTES
может принять, ограничен 8000 байтами. Если вас это как-то затрагивает, то придётся
что-то придумывать.

Также добавим индекс по столбцу Hash, который нам потребуется при блокировке

CREATE INDEX IX_Assemblies_Hash ON [Assemblies] ([Hash]) INCLUDE (Id, YearAssembled);
GO


Ещё пара вспомогательных вещей, которые нам потребуются в этом случае.

Во-первых. Внутри процедуры при помощи FLWOR выделим из @assemblyInfo набор ключевых
признаков:

DECLARE @assemblyKeyInfo xml;
SET @assemblyKeyInfo = @assemblyInfo.query('
    
        { /assembly[1]/@yearAssembled }
        {
            if (/assembly[1]/tools/tool)
            then
                
                {
                    for $t in /assembly[1]/tools/tool
                    order by $t/@id ascending
                    return 
                }
                
            else ()
        }
        {
            if (/assembly[1]/controllers/controller)
            then
                
                {
                    for $c in /assembly[1]/controllers/controller
                    order by $c/@id ascending
                    return 
                }
                
            else ()
        }
    ');


что обеспечит нам также устойчивость при хэшировании (чтобы, например, сборки с инструментами
(инструмент 1, инструмент 2) и (инструмент 2, инструмент 1) не воспринимались как разные),
и вычислим хэш набора ключевых признаков:

DECLARE @hash binary(20);
SET @hash = HASHBYTES('SHA1', CONVERT(varbinary(max), @assemblyKeyInfo));


Во-вторых. Блокировать теперь будем диапазон строк в таблице с одинаковым значением
хэша ключевых признаков, вот так:

SELECT ...
FROM [Table] WITH (INDEX(IX_Assemblies_Hash), UPDLOCK)
WHERE [Hash] = @hash AND /*проверяем признаки*/


указание INDEX(IX_Assemblies_Hash) нужно для того, чтобы оптимизатор не соскочил
на план выполнения запроса без использования нужного нам индекса, что не обеспечит
блокировку диапазона строк с заданным хэш-значением.

Собираем всё в процедуру:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER PROCEDURE [dbo].[SetAssemblyInfo]
(
    @id int = NULL,
    @assemblyInfo xml
)
AS
BEGIN TRY
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    CREATE TABLE #tools (ToolId int PRIMARY KEY);
    CREATE TABLE #controllers (ControllerId int PRIMARY KEY);

    DECLARE @name nvarchar(100), @yearAssembled smallint;
    SET @name = @assemblyInfo.value('(/assembly/@name)[1]', 'nvarchar(100)');
    SET @yearAssembled = @assemblyInfo.value('(/assembly/@yearAssembled)[1]', 'smallint');

    -- tools
    DECLARE @cntT int;

    INSERT INTO #tools (ToolId)
    SELECT t.x.value('@id', 'int')
    FROM @assemblyInfo.nodes('/assembly[1]/tools/tool') t(x);

    SET @cntT = @@ROWCOUNT;

    -- controllers
    DECLARE @cntC int;

    INSERT INTO #controllers (ControllerId)
    SELECT t.x.value('@id', 'int')
    FROM @assemblyInfo.nodes('/assembly[1]/controllers/controller') t(x);

    SET @cntC = @@ROWCOUNT;

    DECLARE @assemblyKeyInfo xml;
    SET @assemblyKeyInfo = @assemblyInfo.query('
        
            { /assembly[1]/@yearAssembled }
            {
                if (/assembly[1]/tools/tool)
                then
                    
                    {
                        for $t in /assembly[1]/tools/tool
                        order by $t/@id ascending
                        return 
                    }
                    
                else ()
            }
            {
                if (/assembly[1]/controllers/controller)
                then
                    
                    {
                        for $c in /assembly[1]/controllers/controller
                        order by $c/@id ascending
                        return 
                    }
                    
                else ()
            }
        ');

    DECLARE @hash binary(20);
    SET @hash = HASHBYTES('SHA1', CONVERT(varbinary(max), @assemblyKeyInfo));

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;

    DECLARE @existingId int;
    SELECT TOP (1)
        @existingId = a.Id
    FROM
        [Assemblies] a WITH (UPDLOCK, INDEX(IX_Assemblies_Hash))
    WHERE
        a.[Hash] = @hash
        AND (@id IS NULL OR a.Id != @id)
        -- проверяем признаки
        AND a.YearAssembled = @yearAssembled
        AND EXISTS (
            SELECT 1
            FROM [AssemblyTools] asmT WITH (UPDLOCK)
                LEFT JOIN #tools t ON t.ToolId = asmT.ToolId
            WHERE asmT.AssemblyId = a.Id
            HAVING COUNT(1) = @cntT AND COUNT(t.ToolId) = @cntT
        )
        AND EXISTS (
            SELECT 1
            FROM [AssemblyControllers] asmC WITH (UPDLOCK)
                LEFT JOIN #controllers c ON c.ControllerId = asmC.ControllerId
            WHERE asmC.AssemblyId = a.Id
            HAVING COUNT(1) = @cntC AND COUNT(c.ControllerId) = @cntC
        );

    IF @@ROWCOUNT > 0
        RAISERROR('Another assembly with same key parameters exists.', 16, 1);

    CREATE TABLE #id (Id int);

    MERGE INTO [Assemblies] t
    USING (
        VALUES (@id, @name, @yearAssembled, @hash)
    ) S (Id, Name, YearAssembled, [Hash])
    ON T.Id = S.Id
    WHEN MATCHED THEN
        UPDATE
        SET
            t.Name = s.Name,
            t.YearAssembled = s.YearAssembled,
            t.[Hash] = s.[Hash]
    WHEN NOT MATCHED THEN
        INSERT (Name, YearAssembled, [Hash])
        VALUES (s.Name, s.YearAssembled, s.[Hash])
    OUTPUT INSERTED.Id INTO #id(Id);

    SELECT @id = Id from #id;

    WITH asmT AS (
        SELECT AssemblyId, ToolId
        FROM [AssemblyTools]
        WHERE AssemblyId = @id
    )
    MERGE INTO asmT
    USING #tools t
    ON asmT.ToolId = t.ToolId
    WHEN NOT MATCHED BY SOURCE AND asmT.AssemblyId = @id THEN
        DELETE
    WHEN NOT MATCHED THEN
        INSERT (AssemblyId, ToolId)
        VALUES (@id, t.ToolId);

    WITH asmC AS (
        SELECT AssemblyId, ControllerId
        FROM [AssemblyControllers]
        WHERE AssemblyId = @id
    )
    MERGE INTO asmC
    USING #controllers c
    ON asmC.ControllerId = c.ControllerId
    WHEN NOT MATCHED BY SOURCE AND asmC.AssemblyId = @id THEN
        DELETE
    WHEN NOT MATCHED THEN
        INSERT (AssemblyId, ControllerId)
        VALUES (@id, c.ControllerId);

    COMMIT TRANSACTION;

END TRY
BEGIN CATCH
    DECLARE @errMsg nvarchar(4000);
    SET @errMsg = ERROR_MESSAGE();

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    RAISERROR(@errMsg, 16, 1);
END CATCH




Пример вызова процедуры

DECLARE @assemblyInfo xml;
SET @assemblyInfo = N'

  
    
    
  
  
     
    
  
';

EXEC SetAssemblyInfo NULL, @assemblyInfo;


Ответ 4

Можно придумать какую-нибудь хеш-функцию.

Например:

Добавь во вторую сущность первичный ключ, который будет равен Сумме ID всех инструментов,
которые входят в сборку.

Тогда БД сама будет проверять уникальность.

По идее, это должно работать. Однако, если вы будите менять ручками ID инструментов,
то могут возникнуть проблемы.

UPD

Упустил коллизию...

Что бы от нее избавится можно умножать на Row_Number.