🤸🏿 ⛏️ 🐑 MS SQL Serverの「実際の」列挙 🚳 😢 👩‍🔬

ご存知のように、MSSQL は列挙を作成/使用する機能を提供していません。これは、暗黙的で信頼性が低く、く、複雑にサポートされるコードを必要とすることがよくあります。リレーショナルデータベースの転送自体には場所を持たないことができると主張することができますが、私の練習では（そして私の練習だけでなく）多くの場合、これらの行が生まれました：

select * from Process where ProcessType = 1 /* Suspended */

または

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

難易度の異なる同様の松葉杖。

そして、私はしたい-

 SELECT * FROM Process WHERE ProcessType = EnumProcess.Suspended;

一般に、解決策は知られています-CLR型を作成します。しかし、アセンブリを作成し、公開し、その関連性を常に管理したい人はいますか？

この記事の目的

この人目を引く構文にできるだけ近くなる
自動的に転送を作成します

免責事項 ：さらにすべては、Visual Studio 2012、MS SQL Server 2012 SP1、.Net Framework 3.5を使用して短時間で作成されたものであり、その方法の一例にすぎません。

言葉なしでやってみよう

（UPD： 1つのスクリプトですべて一緒に）

1.アセンブリをインポートします（記事の最後-ソースコードへのリンク）、（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.テストテーブルSqlAutoEnums.Dataを作成します。このテーブルには、列挙の説明とそれを表示するビューがあります（後で、構造を維持しながら、列挙が格納されている別のテーブルにリダイレクトできます。その後、SqlAutoEnums.Dataテーブルは不要です）。

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.多数の実用的および補助的な機能：

関数の作成...

 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.転送を作成/更新する手順を作成します

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.転送を自動的に更新するためにテーブルにトリガーを掛けます

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

今それをどうしますか？

テストリストを生成します

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 )

私たちが今持っているものを見てみましょう：

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

内部について少し

SqlAutoEnumsアセンブリは、実際には、SqlAutoEnums.Generatedを生成および登録しています*列挙が配置されているテーブル内のデータからのアセンブリ。 CLR Hosted Enviroment （ tynt 、 tynt ）の制限により、不器用なstring.Formatを生成し、 ~~パスEnvironment.GetEnvironmentVariable（ "windir"）+ "\\ Microsoft.NET \\ Framework \\ v3.5に~~沿ってcsc.exeを呼び出してコンパイルします。 ~~\\ csc.exe "（使用している.NET~~のバージョンの~~.NET 3.5~~インストールパス~~がサーバーにインストールされて~~いることを確認してください~~。Environment.GetEnvironmentVariable~~ （" TEMP "）へのアクセスがあることを確認してください）。いつの日か、私たちは不器用なものを交換します。

SQLServerはenumを認識せず、認識もしないため、列挙メンバーの名前によるフィールドを持つ構造が形成されます。

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

まだ多くの技術的なラッピングがあります（たとえば、ここ、MSDN、Googleなど、CLR型の作成に関する詳細を読むことができます...

作業で列挙型（テーブルのフィールドと計算フィールド、プロシージャ/関数のパラメーターなど）を使用するとすぐに、他のユーザーデータタイプと同様に変更されることを覚えておく必要があります（ALTER TYPEがないため-DROPが行われます）、CREATE）または削除できない場合、最初にすべてのリンクを削除する必要があります。ただし、型がプロシージャ/関数のコード内でのみ使用されている場合は可能ですが、列挙またはその一部がない場合、プロシージャ/関数は例外で失敗する可能性があります。

したがって、新しいアセンブリを生成すると、依存関係のないすべての列挙がそのアセンブリに転送されます。「古い」アセンブリには、変更する必要がなく、依存関係がある列挙が残ります。

SqlAutoEnums.dllの約束されたアセンブリソース

ご清聴ありがとうございました。誰かが上記の恩恵を受けるか、より高度なオプションを思い付くと思います。

MS SQL Serverの「実際の」列挙

言葉なしでやってみよう

今それをどうしますか？

内部について少し

More articles: