一种生成SQL Server的“标准"消息的简单方法.表达形式? [英] a simple way to generate SQL Server's "standard" form of an expression?

问题描述

这与SQL Server 2005(或更高版本)中的计算列和默认约束(可能还有其他表达式)有关.两者都使用任意表达式来生成一个值，例如(year+1)代表下一年"的计算列(这显然是一个简单而愚蠢的示例).

This relates to computed columns and default constraints (and possibly other expressions) in SQL Server 2005 (or above). Both of these use an arbitrary expression to generate a value, e.g. (year+1) for a computed column representing "next year" (this is obviously a simple and stupid example).

我要执行的操作:我希望能够确定某些表中现有的计算列(或默认约束)是否与预期的定义相匹配，而后者是在用于创建表的软件生成的架构.我可以使用sp_helptext获取计算列的定义，并从sys.default_constraints目录视图获取默认约束的定义.

What I'm trying to do: I want to be able to determine whether an existing computed column (or default constraint) in some table matches the intended definition, where the latter is defined in a software-generated schema that was used to create the table. I can get the definition of a computed column using sp_helptext, and the definition of a default constraint from the sys.default_constraints catalog view.

我遇到的问题:我从上述来源获得的表达式是归一化/标准格式，与用于创建列/约束的格式不匹配.在上面的示例中，SQL将表达式归一化为([year]+(1)).因此，在此表单和原始表单之间进行简单的字符串比较将无法可靠地确定它们是否相同.

What I'm having trouble with: The expressions I get back from the above sources are in a normalized/standard form that doesn't match the form used to create the column/constraint. In the example above, SQL normalizes the expression to ([year]+(1)). Therefore, a simple string comparison between this form and the original form will not reliably determine whether they are the same.

我已经想到的解决方案:

• 生成原始表达式，使其与SQL的格式匹配.这需要了解SQL用于生成其表单的规则，这些规则尚未记录，因此这不是一个很好的解决方案.
• 将这两种形式解析为AST并进行比较.我已经为原始格式准备了AST，但是我没有解析器，宁愿不编写它.
• 创建一个临时表，并使用原始表达式添加计算列，然后读回规范化表达式.这将是非常可靠的，但感觉很脏，因为从理论上讲，此比较应该是只读操作.

有人能想到另一个解决此问题的好方法吗?我的希望是，也许有人知道一些调试/诊断工具，它将以规范化/标准形式吐出输入表达式.

Can anyone think of another good option for handling this? My hope is that maybe someone knows of some debugging/diagnostic tool that will spit back the input expression in normalized/standard form.

推荐答案

我还将补充一点，表达式的规范化"有时不仅会添加方括号，而且还会以非常不同的方式更改某些表达式！

I will also add, that "normalization" of expression sometimes do not only adds brackets, but it also changes some expressions in a very different way!

例如，在MS SQL 2008 Express中，我使用以下表达式创建了默认值:

For example in MS SQL 2008 Express I created a default with the following expression:

year(getdate()) + 1

但是SQL Server将其更改为

but SQL Server changes it to be

(datepart(year,getdate())+(1))

所以，我不认为任何规则或正则表达式都能在100％的情况下解决您的问题，因此，我建议您结合几种方法

So, I don't believe that any kind of rules or regular expressions will solve your problem for 100% of cases, so I do recommend you to combine several methods

1)首先，我认为在您的情况下，通常在大多数数据库中都存在有限的典型约束.通常，有getdate()和常数数值表达式(0)，(1).您可以具有这些典型约束的表格，这些表格将帮助您匹配期望的表达式和实际的表达式.

1) First of all I think that in your case there is a limited number of typical constraints, that usually exists in most databases. As a rule, there are getdate(), and constant numeric expressions (0), (1). You can have a table of those typical constraints that will help you to match expected and real expressions.

2)然后，您可以尝试使用非常简单的规则，将所有字段名称都包含在[]括号中，并将所有常量和数学运算包含在()中，这样您就可以将 year + 1 转换为 ([年] +(1)).我想这可以用正则表达式来完成.

2) Then you can try very simple rule to include all fields names in [] brackets and all constants and math operations in (), so you will have year + 1 transformed into ([year] + (1)). I suppose this could be done with a Regular Expressions.

3)对于无法使用第一种或第二种方法比较预期结果和实际结果的所有情况，您将按照建议的方式进行操作-创建一个临时表并比较结果.

3) for all cases where you was not able to compare expected and actual results using 1th or 2nd method, you will do what you suggested - create a temp table and compare results.

编辑8月04日:

我发现，当您创建数据库级别的默认值时，将不会对其进行规范化.奇怪吧?但是可能您可以使用此事实并创建绑定到列的数据库级默认值，而不用为列创建默认约束(尽管我认为这将在设计上发生很大的变化，并且需要对现有数据库进行大量更新)

I found that when you create a database-level defaults, they will not be normalized. Strange, eh? But probably, you can use this fact and create database-level defaults that you bind to columns instead of creating default constraints for columns (though, I suppose this will be a very big change in design and will require a huge update of existing databases)

关于列默认约束，以及动态创建/删除默认值以获得规范化形式的方法，这是使用Microsoft.SqlServer.Management.Smo库的简单C#代码.我建议创建一个带有IntTest int，VarcharTest varchar(1)，DateTimeTest datetime等列的test_table-即每种类型仅包含一列.在这种情况下，您将创建/删除默认值，但不必创建删除表和列，这将提高性能.

As for columns default constraints, and the approach to create/drop defaults dynamically in order to get their normalized form, here is a simple C# code using Microsoft.SqlServer.Management.Smo library. I will suggest to create one test_table with columns IntTest int, VarcharTest varchar(1), DateTimeTest datetime and so on - i.e. only one column for each type. In this case you will create/drop defaults but will not have to create drop table and columns and this will increase the performance.

将遵循C#代码(包括使用Microsoft.SqlServer.Management.Smo;)

C# code will follow (include using Microsoft.SqlServer.Management.Smo;)

        Server server = new Server("localhost\\SQLEXPRESS");
        Database db = server.Databases["test"];
        Table t = db.Tables["test_defaults"];
        //here should be some logic to select column name depending on default data type
        //for example for DateTime defaults we will use "DateTimeTest" column
        Column c = t.Columns["DateTimeTest"];

        //clean up previous results if they exist
        DefaultConstraint constr = c.DefaultConstraint;
        if (constr != null) constr.Drop();

        //create new constraint
        constr = c.AddDefaultConstraint();
        constr.Text = "getdate()";
        constr.Create();
        //after refresh we will have a new text
        constr.Refresh();
        string result = constr.Text;

        //drop it if we don't need it
        constr.Drop();

这篇关于一种生成SQL Server的“标准"消息的简单方法.表达形式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！