SSDT项目中schema对象的条件编译 [英] Conditional Compilation of schema objects in SSDT Project

问题描述

我们的 SSDT 数据库项目包括一个具有计算列的表，该列可以采用多种形式中的一种，具体取决于客户的要求.我正在尝试弄清楚如何管理这个计算列，以便我们仍然可以使用发布功能，而无需将每个人的列恢复为默认值.

Our SSDT database project includes a table that has a computed column that can take one of several forms, depending on customer requirements. I'm trying to figure out how to manage this computed column so that we can still use the Publish function without reverting everyone's columns back to the default.

我想要完成的事情可以用以下无效 T-SQL 代码来解释:

What I'm trying to accomplish can be explained in the following invalid T-SQL code:

CREATE TABLE dbo.Customer
(
  Id INTEGER,
  Region INTEGER,
  Name VARCHAR(50),
  AccountNumber AS dbo.FormatAccountNumber(Id, Region)
)

CREATE FUNCTION [dbo].[FormatAccountNumber]
(
  @Id INTEGER,
  @Region INTEGER
)
RETURNS VARCHAR(20)
AS
BEGIN
  IF '$(AccountType)' = 'Regional'
    RETURN CONVERT(VARCHAR, @Region) + '-' + CONVERT(VARCHAR, @Id)

  IF '$(AccountType)' = 'Merged'
    RETURN CONVERT(VARCHAR, @Region * 100000 + @Id)

  IF '$(AccountType)' = 'Flat'
    RETURN CONVERT(VARCHAR, @Id)
END

这当然不起作用，因为 $(AccountType) SQLCMD 变量不能在函数内部使用，并且无论如何都不会在运行时正确设置.我还尝试在整个函数周围放置 SQLCMD 条件:

This, of course, doesn't work because the $(AccountType) SQLCMD variable can't be used inside of the function, and wouldn't be set properly at run-time anyway. I've also trying putting the SQLCMD conditional around the entire function:

IF '$(AccountType)' = 'Flat'
  CREATE FUNCTION ...

但这会产生CREATE FUNCTION 必须是批处理中唯一的语句"的错误.

but this produces the error that "CREATE FUNCTION must be the only statement in the batch."

有没有办法在 SSDT 项目中对架构进行任何形式的条件编译?如果没有，我有哪些选择可以在 SSDT 发布流程中维护这种可自定义的字段?

Is there any way to do any sort of conditional compilation of schema in the SSDT project? And if not, what options do I have for maintaining this sort of customizable field within the SSDT publishing process?

推荐答案

您可以使用 Post-Deploment 脚本在动态 SQL 中强制部署您的对象:

You could use a Post-Deploment script to imperatively deploy your object in dynamic SQL:

IF NOT EXISTS (SELECT * FROM sys.objects 
            WHERE object_id = OBJECT_ID(N'[dbo].[FormatAccountNumber]') 
            AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
EXEC('CREATE FUNCTION [dbo].[FormatAccountNumber] () RETURNS BIT AS BEGIN RETURN 0 END')
GO 

IF '$(AccountType)' = 'Regional'
BEGIN
    EXEC('
        ALTER FUNCTION [dbo].[FormatAccountNumber]
        (
          @Id INTEGER,
          @Region INTEGER
        )
        RETURNS VARCHAR(20)
        AS
        BEGIN
            RETURN CONVERT(VARCHAR, @Region) + ''-'' + CONVERT(VARCHAR, @Id)
        END
    ')
END

请注意，这样做后，您将无法对 SSDT 数据库项目中的 dbo.FormatAccountNumber 函数进行任何引用(除非这些对象也包含在部署后脚本中).

Note that by doing this, you won't be able to make any references to the dbo.FormatAccountNumber function in your SSDT Database Project (unless those objects also included in the Post-Deployment script).

我还尝试了另一种解决方案，该解决方案涉及 .sqlproj 文件本身(在 ItemGroup 元素内)的附加条件，但是自从 MsBuild 以来这确实变得有点混乱属性与 SQLCMD 变量并不完全相同，但如果您愿意，我可以发布此内容.

I also toyed with an alternate solution which involved addition conditionals within the .sqlproj file itself (within the ItemGroup elements), but this did get a bit messy since MsBuild Properties aren't exactly like-for-like with SQLCMD Variables, but I can post this if you like.

如果您发现自己需要经常有条件地部署数据库中的对象，您可能会考虑转向基于命令式的部署解决方案(与 SSDT 项目的声明式风格相反).命令式部署(通常称为迁移)可让您更好地控制部署时的行为.

If you find yourself needing to conditionally deploy objects in your database often, you may like to consider moving to an imperative-based deployment solution (as opposed to the declarative style of SSDT projects). Imperative deployment, often called migrations, gives you greater control of deploy-time behaviour.

免责声明:我是 ReadyRoll 的创始人，该公司为使用命令式部署的 VS 制作产品.

Disclaimer: I am the founder of ReadyRoll, which makes a product for VS that uses imperative deployment.

这篇关于SSDT项目中schema对象的条件编译的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！