The goal is to organize the creation and storage of unique assemblies of tools . Under the assembly tool refers to a set of unique tools (cutters, drills, etc.).

The structure of the database, I imagine something like this:

  1. Tools Directory;
  2. Directory of Assemblies;
  3. A table for the connection of the two directories mentioned above (many to many: ToolId & AssemblyId )

An assembly is considered unique based on the tools it contains. there should not be several assemblies consisting of the same positions.

For example, assembly number 1 consists of the following positions, chuck number 1 , cutter number 5 . It is necessary to prohibit the creation of assemblies consisting only of Chuck No. 1 and Mill No. 5 in the presence of an existing assembly, but it is possible to create an assembly No. 2 consisting of Chuck No. 1 , Mill No. 5 , Extension No. 2 .

Is it possible to do this on the side of the database?

  • Comments are not intended for extended discussion; conversation moved to chat . - Nicolas Chabanovsky ♦

4 answers 4

Assemblies can be stored as a tree, where each assembly will have the latest tool and a link to the assembly without this tool:

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

If there are no two identical pairs (Assembly, Tool) in the database - then all assemblies will be unique.

 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) )

But there is a problem - this presentation will consider the assemblies "Tool1 + Tool2" and "Tool2 + Tool1" different. In order to check the uniqueness without taking into account the order - it is necessary to force the tools inside the assembly by number.

In order not to miss incorrectly ordered assemblies, copy the toolbox field of the parent assembly to the child assembly, checking the correctness using the composite foreign key:

 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) )
  • at the assembly / tool as a number auto-increment field - Bald
  • @Bald and what's the problem? I also wrote. - Pavel Mayorov

I propose to do serialization, i.e. assemble a string like "ToolId1, ToolId2, ToolId3" for each AssemblyId value.

These lines can already be checked for uniqueness. It is important that the ID in the list be sorted.

    I will slightly complicate the formulation of the problem, relative to that given in the question, for the purpose of generalization.

    So, let there be some composite entity, information about which can be stored in several tables.

    In my example, these will be the tables:

    • Assemblies - build
     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 - tools contained in the assembly
     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 );
    • I will add another AssemblyControllers table - the control systems contained in the assembly
     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 );

    (Suppose also that we have the Tools and Controllers tables, but I do not provide their definitions and foreign keys in AssemblyTools and AssemblyControllers , since for example this is irrelevant).

    And let there be a certain sign of uniqueness, which can also be composite and can be distributed across several tables.

    In my example, it will be a combination of the following properties:

    • build year (column YearAssembled in the Assemblies table)
    • toolbox in an assembly (a subset of the records in AssemblyTools )
    • set of control systems in an assembly (a subset of the records in the AssemblyControllers )

    Creating a composite entity is logical to encapsulate into a stored procedure, which then must be used by applications to create them.

    I will have a single procedure for creating and modifying an assembly with the following signature.

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

    Where

    • @id is the identifier of the assembly that needs to be changed ( NULL if you need to create a new assembly)
    • @assemblyInfo - assembly information in the following XML format
     <assembly name="Assembly Name" yearAssembled="2016"> <tools> <tool id="1"/> <tool id="2"/> <tool id="3"/> </tools> <controllers> <controller id="1"/> <controller id="2"/> </controllers> </assembly>

    To solve the problem of competition when creating or changing a composite entity, for example, you can:

    1. application locking (using sp_getapplock )
    2. locking tables using table instructions ( table hints ) at the time of operation
    3. using the blocking property of the key range of serializable transactions, block tables not completely, but only a small part of them

    We turn to the details.

    Add to the data tables:

     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);

    We will also need some auxiliary things.

    First of all. We will block the tables for checking the uniqueness attribute using a pair of table instructions TABLOCK and UPDLOCK :

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

    SqlServer, unfortunately, converts a pair of TABLOCK + UPDLOCK to TABLOCKX (exclusive table lock). If he did not do this, then our lock would not hinder those who simply read from the table without changing the data.

    Secondly, suppose that the table #tools (ToolId int) contains a toolkit in the number of @cntT int , we can find assemblies with the exact same toolbox using a query

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

    (Ie, we are looking for assemblies with the same number of tools, and the number of matching tools should not differ from the total number of tools)

    Putting it all in the procedure:

     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 txvalue('@id', 'int') FROM @assemblyInfo.nodes('/assembly[1]/tools/tool') t(x); SET @cntT = @@ROWCOUNT; -- controllers DECLARE @cntC int; INSERT INTO #controllers (ControllerId) SELECT txvalue('@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

    In highly competitive systems, full table locking is usually undesirable. To increase the possible number of competitive transactions per unit of time, it is better to use the method with partial blocking.

    To do this, add to the entity a hash from a set of unique features:

     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

    In SqlServer versions up to 2014 inclusive, the size of the data that the HASHBYTES function can accept is limited to 8000 bytes. If it somehow affects you, then you have to invent something.

    Also add an index on the Hash column, which we need when blocking

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

    A couple more auxiliary things that we need in this case.

    First of all. Inside the procedure, using FLWOR, select the set of key features from @assemblyInfo :

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

    which will also provide us with stability when hashing (for example, assemblies with tools (tool 1, tool 2) and (tool 2, tool 1) are not perceived as different), and calculate the hash of a set of key features:

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

    Secondly. Now we will block the range of rows in the table with the same hash value of the key features, like this:

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

    INDEX(IX_Assemblies_Hash) required so that the optimizer does not jump to the query execution plan without using the index we need, which does not ensure blocking of the range of lines with the specified hash value.

    Putting it all in the procedure:

     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 txvalue('@id', 'int') FROM @assemblyInfo.nodes('/assembly[1]/tools/tool') t(x); SET @cntT = @@ROWCOUNT; -- controllers DECLARE @cntC int; INSERT INTO #controllers (ControllerId) SELECT txvalue('@id', 'int') FROM @assemblyInfo.nodes('/assembly[1]/controllers/controller') t(x); SET @cntC = @@ROWCOUNT; DECLARE @assemblyKeyInfo xml; SET @assemblyKeyInfo = @assemblyInfo.query(' <assembly> { /assembly[1]/@yearAssembled } { if (/assembly[1]/tools/tool) then <tools> { for $t in /assembly[1]/tools/tool order by $t/@id ascending return <tool id="{$t/@id}" /> } </tools> else () } { if (/assembly[1]/controllers/controller) then <controllers> { for $c in /assembly[1]/controllers/controller order by $c/@id ascending return <controller id="{$c/@id}" /> } </controllers> else () } </assembly>'); 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

    Sample procedure call

     DECLARE @assemblyInfo xml; SET @assemblyInfo = N' <assembly name="Assembly 4" yearAssembled="2014"> <tools> <tool id="11"/> <tool id="33"/> </tools> <controllers> <controller id="101"/> <controller id="303"/> </controllers> </assembly>'; EXEC SetAssemblyInfo NULL, @assemblyInfo;
    • thanks, I'll try to digest ... - Bald

    You can come up with some hash function.

    For example:

    Add a primary key to the second entity, which will be equal to the Sum of ID of all tools that are included in the assembly.

    Then the DB itself will check the uniqueness.

    In theory, this should work. However, if you change the tool ID handles, problems may arise.

    UPD

    Missed a collision ...

    To get rid of it you can multiply by Row_Number.

    • and how to be with 1 + 2 + 3 == 6 ? - Bald
    • Let's say in one assembly there are id 3 and 6, and in the second there are 4 and 5. And your algorithm will decide that it is the same assembly. A classic example of a hash function collision. - KoVadim
    • I corrected. But my option will work if the base is not huge, as I can generate huge IDs. - iluxa1810
    • The question is who will be this amount and when to calculate. So you can stupidly and ID separated by commas in ascending order. I don’t like the fact that such a field will have to be calculated by the client before making a record and only then writing separate records with an ID, theoretically, there can be a discrepancy between this field and the real list (in case of errors in the client, for example). - Mike
    • @Mike on the client is not an option to check: the same collision is possible that while one client checks another one will have time to insert ... - Bald