Geekly Articles each Day
📜 ⬆️ ⬇️

"Real" enums for MS SQL Server


As you know, MSSQL does not provide the ability to create / use enums, which often entails implicit, unreliable, ugly, and difficult to maintain code. One can argue that in a relational database, enumerations as such cannot have such a place, but many times in my (and not only mine) practice, similar lines were born:
select * from Process where ProcessType = 1 /* Suspended */

or
 declare @processSuspended int = 1; select * from Process where ProcessType = @processSuspended; ------------------------------------------------------------------------------------------------------------ DECLARE @processSuspended INT; SELECT @processSuspended = Value FROM ProcessEnum WHERE Name = 'Suspended'; SELECT * FROM Process WHERE ProcessType = @processSuspended; ------------------------------------------------------------------------------------------------------------ CREATE FUNCTION ProcessEnum_Suspended() RETURNS INT AS BEGIN RETURN 1; END; SELECT * FROM Process WHERE ProcessType = ProcessEnum_Suspended();


And similar crutches of different levels of complexity.
And you want -
 SELECT * FROM Process WHERE ProcessType = EnumProcess.Suspended;


The decision, in general, is known - we do CLR type. But who wants to write all the time, publish the assembly and take care of their relevance?
')
Objectives of this article


Disclaimer : Everything was written further using Visual Studio 2012, MS SQL Server 2012 SP1, .Net Framework 3.5, in a short time and is just an example of how.

Let's try without further ado


(UPD: All together in one script )

1. Import the assembly (at the end of the article - a link to the source code), (remember about ALTER DATABASE SET TRUSTWORTHY ON)
CREATE ASSEMBLY SQLAutoEnums ...
 CREATE ASSEMBLY [SQLAutoEnums] FROM WITH PERMISSION_SET = UNSAFE GO CREATE FUNCTION [dbo].[SqlAutoEnums.EnumMembers_Current](@enumName [nvarchar](4000)) RETURNS TABLE ( [ID] [int] NULL, [Name] [nvarchar](4000) NULL ) WITH EXECUTE AS N'dbo' AS EXTERNAL NAME [SQLAutoEnums].[UserDefinedFunctions].[EnumMembersCurrent] GO CREATE FUNCTION [dbo].[SqlAutoEnumsCompile](@code [nvarchar](max)) RETURNS [varbinary](max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLAutoEnums].[UserDefinedFunctions].[SqlAutoEnumsCompile] GO CREATE FUNCTION [dbo].[SqlAutoEnumsGenerate](@tableName [nvarchar](4000), @columnPrefix [nvarchar](4000), @columnName [nvarchar](4000), @columnMember [nvarchar](4000), @columnValue [nvarchar](4000)) RETURNS [nvarchar](max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLAutoEnums].[UserDefinedFunctions].[SqlAutoEnumsGenerate] GO CREATE FUNCTION [dbo].[SqlAutoEnumsTryCompile](@code [nvarchar](max)) RETURNS [nvarchar](max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLAutoEnums].[UserDefinedFunctions].[SqlAutoEnumsTryCompile] GO



2. Create a test table SqlAutoEnums.Data, in which we will have a description of the enumerations and view looking at it (later you can preserve the structure to another table, where you have enums, then the SqlAutoEnums.Data table is not needed):
CREATE TABLE dbo. [SqlAutoEnums.Data] ...
 CREATE TABLE [dbo].[SqlAutoEnums.Data]( [ID] [int] IDENTITY(1,1) NOT NULL, [Prefix] [nvarchar](50) NOT NULL, [Name] [nvarchar](50) NOT NULL, [MemberName] [nvarchar](50) NOT NULL, [MemberValue] [int] NOT NULL, CONSTRAINT [PK_SqlAutoEnums.Data] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE UNIQUE NONCLUSTERED INDEX [IX_SqlAutoEnums.Data] ON [dbo].[SqlAutoEnums.Data] ( [Prefix] ASC, [Name] ASC, [MemberName] ASC, [MemberValue] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE UNIQUE NONCLUSTERED INDEX [IX_SqlAutoEnums.Data.A] ON [dbo].[SqlAutoEnums.Data] ( [Name] ASC, [MemberValue] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE VIEW [dbo].[SqlAutoEnums.Data.View] AS SELECT ID , Prefix , Name , MemberName , MemberValue FROM dbo.[SqlAutoEnums.Data]


3. A bunch of utilitarian auxiliary functions:
CREATE FUNCTION ...
 CREATE FUNCTION [dbo].[SqlAutoEnums.Enum_Equals] ( @enumName NVARCHAR(100) ) RETURNS bit AS BEGIN DECLARE @res BIT = 0; SET @res = CASE WHEN EXISTS ( SELECT Name AS MemberName, ID AS MemberValue FROM dbo.[SqlAutoEnums.EnumMembers_Current](@enumName) except select MemberName, MemberValue FROM dbo.[SqlAutoEnums.EnumMembers_New](@enumName) ) OR EXISTS ( SELECT MemberName, MemberValue FROM dbo.[SqlAutoEnums.EnumMembers_New](@enumName) EXCEPT SELECT Name AS MemberName, ID AS MemberValue FROM dbo.[SqlAutoEnums.EnumMembers_Current](@enumName) ) THEN 0 ELSE 1 END; RETURN @res; END GO --================================================================================================================= CREATE FUNCTION [dbo].[SqlAutoEnums.Enum_HasDependencies] ( @schemaName NVARCHAR(100), @typeName NVARCHAR(100), @onColumns BIT, @oncomputedColumns BIT, @onParameters BIT, @onCheckConstraints BIT, @onCode BIT ) RETURNS bit AS BEGIN DECLARE @res BIT = 0; DECLARE @typeidname NVARCHAR(255) = '[' + @schemaName + '].[' + @typeName + ']'; IF (@onColumns = 1) BEGIN SET @res = CASE WHEN EXISTS ( SELECT 1 --OBJECT_NAME(object_id) AS object_name ,c.name AS column_name ,SCHEMA_NAME(t.schema_id) AS schema_name,TYPE_NAME(c.user_type_id) AS user_type_name,c.max_length,c.precision,c.scale,c.is_nullable,c.is_computed FROM sys.columns AS c INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id WHERE c.user_type_id = TYPE_ID(@typeidname) ) THEN 1 END; IF (1 = @res) RETURN @res; END; IF (@oncomputedColumns = 1) BEGIN SET @res = CASE WHEN EXISTS ( SELECT 1 --OBJECT_NAME(object_id) AS OBJECT_NAME ,COL_NAME(object_id, column_id) AS column_name FROM sys.sql_dependencies WHERE referenced_major_id = TYPE_ID(@typeidname) AND class = 2 AND -- schema-bound references to type OBJECTPROPERTY(object_id, 'IsTable') = 1 ) THEN 1 END; IF (1 = @res) RETURN @res; END; IF (@onParameters = 1) BEGIN SET @res = CASE WHEN EXISTS ( SELECT 1 -- OBJECT_NAME(object_id) AS object_name ,NULL AS procedure_number ,name AS param_name ,parameter_id AS param_num ,TYPE_NAME(p.user_TYPE_ID) AS type_name FROM sys.parameters AS p WHERE p.user_TYPE_ID = TYPE_ID(@typeidname) UNION SELECT 1 -- OBJECT_NAME(object_id) AS object_name ,procedure_number ,name AS param_name ,parameter_id AS param_num ,TYPE_NAME(p.user_TYPE_ID) AS type_name FROM sys.numbered_procedure_parameters AS p WHERE p.user_TYPE_ID = TYPE_ID(@typeidname) ) THEN 1 END; IF (1 = @res) RETURN @res; END; IF (@onCheckConstraints = 1) BEGIN SET @res = CASE WHEN EXISTS ( SELECT 1 -- SCHEMA_NAME(o.schema_id) AS schema_name ,OBJECT_NAME(o.parent_object_id) AS table_name ,OBJECT_NAME(o.object_id) AS constraint_name FROM sys.sql_dependencies AS d JOIN sys.objects AS o ON o.object_id = d.object_id WHERE referenced_major_id = TYPE_ID(@typeidname) AND class = 2 AND -- schema-bound references to type OBJECTPROPERTY(o.object_id, 'IsCheckCnst') = 1 -- exclude non-CHECK dependencies ) THEN 1 END; IF (1 = @res) RETURN @res; END; IF (@onCode = 1) BEGIN SET @res = CASE WHEN EXISTS ( SELECT 1 -- SCHEMA_NAME(o.schema_id) AS dependent_object_schema ,OBJECT_NAME(o.object_id) AS dependent_object_name ,o.type_desc AS dependent_object_type ,d.class_desc AS kind_of_dependency ,TYPE_NAME (d.referenced_major_id) AS type_name FROM sys.sql_dependencies AS d JOIN sys.objects AS o ON d.object_id = o.object_id AND o.type IN ('FN','IF','TF', 'V', 'P') WHERE d.class = 2 AND -- dependencies on types d.referenced_major_id = TYPE_ID(@typeidname) ) THEN 1 END; IF (1 = @res) RETURN @res; END; RETURN 0; END GO --================================================================================================================= CREATE FUNCTION [dbo].[SqlAutoEnums.EnumMembers_New] ( @enumName NVARCHAR(100) ) RETURNS TABLE AS RETURN ( -- select * from [SqlAutoEnums.NewEnumVals]() SELECT Prefix+Name AS EnumName, MemberName AS MemberName, MemberValue AS MemberValue FROM dbo.[SqlAutoEnums.Data.View] WHERE Prefix+Name = @enumName ) GO --================================================================================================================= CREATE FUNCTION [dbo].[SqlAutoEnums.Enums_Current]() RETURNS TABLE AS RETURN ( SELECT atold.assembly_id AS AssemblyId, asm.name AS AssemblyName, atold.user_type_id AS EnumId, atold.name AS EnumName FROM sys.assembly_types atold INNER JOIN sys.assemblies asm on asm.name LIKE 'SQLAutoEnums.Generated%' AND atold.assembly_id = asm.assembly_id ) GO --====================================================================================================================== CREATE FUNCTION [dbo].[SqlAutoEnums.Enums_New]() RETURNS TABLE AS RETURN ( SELECT DISTINCT Prefix+Name AS EnumName FROM dbo.[SqlAutoEnums.Data.View] ) GO --================================================================================================================= CREATE FUNCTION [dbo].[SqlAutoEnums.EnumsMembers_Current] ( ) RETURNS TABLE AS RETURN ( SELECT e.AssemblyId, e.AssemblyName, e.EnumId, e.EnumName, v.Name AS MemberName, v.ID AS MemberValue FROM ( SELECT AssemblyId, AssemblyName, EnumId, CAST(EnumName AS NVARCHAR(100)) AS EnumName FROM dbo.[SqlAutoEnums.Enums_Current]() ) e CROSS APPLY dbo.[SqlAutoEnums.EnumMembers_Current](e.EnumName) AS v ) GO --================================================================================================================= CREATE FUNCTION [dbo].[SqlAutoEnums.EnumsMembers_New] ( ) RETURNS TABLE AS RETURN ( SELECT Prefix+Name AS EnumName, MemberName AS MemberName, MemberValue AS MemberValue FROM dbo.[SqlAutoEnums.Data.View] ) GO


4. We do the procedure for creating / updating transfers
CREATE PROCEDURE dbo. [SqlAutoEnums.Renew] ...
 CREATE PROCEDURE [dbo].[SqlAutoEnums.Renew] WITH EXECUTE AS SELF AS BEGIN DECLARE @msg NVARCHAR(MAX); BEGIN TRY BEGIN TRAN --============================================================================================================ -- dropping current enums PRINT 'Current enums: clearing...'; IF (EXISTS ( SELECT 1 FROM dbo.[SqlAutoEnums.Enums_Current]() ec WHERE dbo.[SqlAutoEnums.Enum_Equals](ec.EnumName) = 0 AND dbo.[SqlAutoEnums.Enum_HasDependencies]('', ec.EnumName, 1, 1, 1, 1, 1) = 1 )) BEGIN SET @msg = 'Cannot modify or drop enums cause of dependencies: '; SELECT @msg += ec.EnumName + ', ' FROM dbo.[SqlAutoEnums.Enums_Current]() ec WHERE dbo.[SqlAutoEnums.Enum_Equals](ec.EnumName) = 0 AND dbo.[SqlAutoEnums.Enum_HasDependencies]('', ec.EnumName, 1, 1, 1, 1, 1) = 1 SET @msg = SUBSTRING(@msg, 1, LEN(@msg)-2); RAISERROR(@msg, 16, 2); END; --assembly list to drop DECLARE @asstodrop TABLE (Name NVARCHAR(MAX)); INSERT INTO @asstodrop (Name) SELECT ec.AssemblyName FROM dbo.[SqlAutoEnums.Enums_Current]() ec GROUP BY ec.AssemblyName HAVING SUM(CAST(dbo.[SqlAutoEnums.Enum_HasDependencies]('', ec.EnumName, 1, 1, 1, 1, 1) AS INT)) = 0 -- dropping enums DECLARE @qryDropEnum NVARCHAR(MAX); DECLARE @qryDropEnumToList NVARCHAR(MAX); DECLARE @oldEnumName NVARCHAR(MAX); DECLARE enumCursor CURSOR FOR SELECT ec.EnumName FROM dbo.[SqlAutoEnums.Enums_Current]() ec WHERE dbo.[SqlAutoEnums.Enum_HasDependencies]('', ec.EnumName, 1, 1, 1, 1, 1) = 0 OPEN enumCursor; FETCH NEXT FROM enumCursor INTO @oldEnumName; WHILE @@FETCH_STATUS = 0 BEGIN PRINT ' Dropping enum ' + @oldEnumName; SET @qryDropEnum = 'DROP TYPE [dbo].[' + @oldEnumName + ']'; SET @qryDropEnumToList = 'DROP FUNCTION [dbo].[' + @oldEnumName + '.ToList]'; IF (EXISTS (SELECT 1 FROM Information_schema.Routines WHERE SPECIFIC_SCHEMA = 'dbo' AND SPECIFIC_NAME = @oldEnumName + '.ToList')) EXEC sp_executesql @qryDropEnumToList; EXEC sp_executesql @qryDropEnum; FETCH NEXT FROM enumCursor INTO @oldEnumName; END; CLOSE enumCursor; DEALLOCATE enumCursor; -- dropping assemblies DECLARE @qryDropAss NVARCHAR(MAX); DECLARE @oldAssName NVARCHAR(MAX); DECLARE assCursor CURSOR FOR SELECT Name FROM @asstodrop; OPEN assCursor; FETCH NEXT FROM assCursor INTO @oldAssName; WHILE @@FETCH_STATUS = 0 BEGIN PRINT ' Dropping assembly ' + @oldAssName; SET @qryDropAss = 'DROP ASSEMBLY [' + @oldAssName + ']' EXEC sp_executesql @qryDropAss; FETCH NEXT FROM assCursor INTO @oldAssName; END; CLOSE assCursor; DEALLOCATE assCursor; PRINT 'Current enums: clear.'; --============================================================================================================ -- creating new assembly PRINT 'New assembly: generating...'; DECLARE @newAsmName NVARCHAR(255) = 'SQLAutoEnums.Generated.' + CAST(NEWID() AS NVARCHAR(100)); DECLARE @newAsmId BIGINT; PRINT ' Generated assembly name = ' + @newAsmName; DECLARE @code NVARCHAR(MAX) = dbo.SqlAutoEnumsGenerate('[SqlAutoEnums.Data.View]', 'Prefix', 'Name', 'MemberName', 'MemberValue'); DECLARE @compilemsg NVARCHAR(MAX) = dbo.SqlAutoEnumsTryCompile(@code); DECLARE @bin VARBINARY(MAX) = dbo.SqlAutoEnumsCompile(@code); IF (@bin IS NULL) BEGIN SET @msg = 'Cannot compile generated code:' + @compilemsg; RAISERROR(@msg, 16, 2); END; DECLARE @qryCreateAssembly NVARCHAR(MAX) = 'CREATE ASSEMBLY [' + @newAsmName + '] FROM ' + master.dbo.fn_varbintohexstr(@bin) + ' WITH PERMISSION_SET = SAFE;'; EXEC sp_executesql @qryCreateAssembly; SELECT @newAsmId = asm.assembly_id FROM sys.assemblies asm WHERE asm.name = @newAsmName; PRINT 'New assembly: done.'; --=========================================================================================================== -- registering new enums PRINT 'New enums: registering...' DECLARE @qryCreateEnum NVARCHAR(MAX); DECLARE @qryCreateEnumToList NVARCHAR(MAX); DECLARE @newEnumName NVARCHAR(MAX); DECLARE newEnumCursor CURSOR FOR SELECT EnumName FROM dbo.[SqlAutoEnums.Enums_New]() en WHERE en.EnumName NOT IN (SELECT EnumName FROM dbo.[SqlAutoEnums.Enums_Current]()); OPEN newEnumCursor; FETCH NEXT FROM newEnumCursor INTO @newEnumName; WHILE @@FETCH_STATUS = 0 BEGIN PRINT ' Registering enum ' + @newEnumName; SET @qryCreateEnum = 'CREATE TYPE [dbo].[' + @newEnumName + '] EXTERNAL NAME [' + @newAsmName + '].[SqlAutoEnumsGenerated.' + @newEnumName + ']'; EXEC sp_executesql @qryCreateEnum; SET @qryCreateEnumToList = 'CREATE FUNCTION [' + @newEnumName + '.ToList]() RETURNS TABLE (ID INT, Name NVARCHAR(4000)) EXTERNAL NAME [' + @newAsmName + '].[SqlAutoEnumsGenerated.' + @newEnumName + '].ToList;'; EXEC sp_executesql @qryCreateEnumToList; FETCH NEXT FROM newEnumCursor INTO @newEnumName; END; CLOSE newEnumCursor; DEALLOCATE newEnumCursor; PRINT 'New enums: done.' COMMIT TRANSACTION; END TRY BEGIN CATCH IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION; THROW; END CATCH; END GO


4. We hang a trigger on the table to automatically update transfers
CREATE TRIGGER dbo. [SqlAutoEnums.Renew.Trigger] ...
 CREATE TRIGGER dbo.[SqlAutoEnums.Renew.Trigger] ON [dbo].[SqlAutoEnums.Data] AFTER INSERT, DELETE, UPDATE AS BEGIN EXEC dbo.[SqlAutoEnums.Renew]; END


What to do with it now?



Generate test listings

INSERT INTO dbo. [SqlAutoEnums.Data] ...
 INSERT INTO dbo.[SqlAutoEnums.Data] (Prefix, Name, MemberName, MemberValue) VALUES ('Enum', 'Lolly', 'C', 14 ), ('Enum', 'Lolly', 'A', 1 ), ('Enum', 'Lolly', 'B', 2 ), ('Enum', 'Process', 'Running', 1 ), ('Enum', 'Process', 'Suspended', 2 ), ('Enum', 'Process', 'Terminated', 3 )


Let's see what we have now:
 --  SELECT * FROM [SqlAutoEnums.Enums_Current]() --    SELECT * FROM [SqlAutoEnums.EnumsMembers_Current]() --   EnumProcess SELECT * FROM [EnumProcess.ToList]() --  DECLARE @processState EnumProcess; SET @processState = EnumProcess::Running; PRINT @processState.ToString(); --   DECLARE @process TABLE (ID INT, Comment NVARCHAR(100), ProcessState EnumProcess); INSERT INTO @process (ID, Comment, ProcessState) VALUES (0, ' : EnumProcess::Suspended', EnumProcess::Suspended), (0, ' : "Running" ', EnumProcess::Parse('Running')), (0, ' : 3', EnumProcess::Parse(3)) -- where SELECT ID, Comment, ProcessState, ProcessState.ToInt(), ProcessState.ToString() FROM @process WHERE ProcessState = EnumProcess::Suspended OR ProcessState = @processState; -- group SELECT ProcessState, ProcessState.ToInt(), ProcessState.ToString(), COUNT(*) AS [Count] FROM @process GROUP BY ProcessState;


A little about the insides



The SqlAutoEnums assembly deals, in fact, with the fact that it generates and registers the SqlAutoEnums.Generated. * Assemblies from the data in the table, in which the enums are located. Due to limitations CLR Hosted Enviroment ( tynts , tynts ), generates clumsily, string.Format and jumped, compiles by calling csc.exe along the path Environment.GetEnvironmentVariable ("windir") + "\\ Microsoft.NET \\ Framework \\ v3.5 \\ csc.exe "(make sure that the .NET 3.5 path of the installation of the version of .NET used is installed on the server . Make sure that there and to Environment.GetEnvironmentVariable (" TEMP ")) there is access. One day we will replace something less clumsy.

Due to the fact that SQLServer does not know and does not understand enum, structures are formed with fields named after the enumeration members:

 public enum FooEnum{A = 1, B = 2} public struct MyFooEnum { public MyFooEnum(FooEnum value) { _value = value; } public static MyFooEnum A { get { return new MyFooEnum(FooEnum.A);} } public static MyFooEnum B { get { return new MyFooEnum(FooEnum.B);} } private FooEnum _value; }

There are still many technical wrappers around it (for more information on creating CLR types, you can read, for example, here , MSDN, Google ...

It should be remembered that as soon as you used the enumeration in the work (fields and calculated fields in the table, parameters of procedures / functions, etc.), then, like any other user-defined data type, change (due to the lack of ALTER TYPE - DROP is done , then CREATE) or delete does not work, you first need to remove all references to it. However, if the type is used only within the code of procedures / functions, then it is possible, but, understandably, the procedure / function can then fall out with an exception, if there is no enumeration or part of it.
Therefore, when generating a new assembly, all enumerations that do not have dependencies are transferred to it, in the "old" assembly there are enumerations that do not need to be changed and which have dependencies.

Promised SqlAutoEnums.dll assembly sources

Thank you for your attention, I hope someone the above described will come in handy or will push a more advanced version.

Source: https://habr.com/ru/post/187966/

More articles:


All Articles