SQL Server ScriptDom 解析 [英] SQL Server ScriptDom Parsing

问题描述

与我合作的开发团队正在使用 SQL 数据项目来完成我们必须针对现有数据库执行的大量工作.几周后，我们遇到了一些问题，但总体而言体验还是不错的.

The team of developers I work with are using SQL Data Projects for a large piece of work we have to do against an existing database. We are a few weeks in and there have been a few gotchas, but the experience has been generally good.

但是，当我们开始部署到生产环境时，dba 团队拒绝接受 DACPAC 作为部署方法.相反，他们希望看到每个 DML 或 DDL 语句的传统脚本.

However, when we get to deploy to production, the dba team have refused to accept DACPACs as a deployment method. Instead, they are want to see a traditional script per DML or DDL statement.

目前的想法是在完成的 SQL 项目和生产环境之间创建一个差异脚本，然后将其解析为单独的脚本.不太好，我知道.

The current thinking is to create a difference script between the finished SQL project and the production environment, and then parse that into individual scripts. Not nice I know.

要解析差异脚本，似乎有两个选项:

To parse the difference script there seems to be two options:

1. 根据批处理分隔符命令 GO 解析脚本.一个相当基本的解决方案，但显示出希望.
2. 或者，使用 Microsoft.SqlServer.TransactSql.ScriptDom.这看起来更具前瞻性，但似乎要复杂得多.

我目前正在试用 ScriptDom，但无法理解它.我目前的问题(但不仅限于此)如下.

I'm trialling the ScriptDom at the moment but am having trouble understanding it. My current, but not only issues, is as follows.

我正在尝试使用 C# 中的 ScriptDOM 解析以下 SQL:

I'm trying to parse the following SQL using the ScriptDOM in C#:

CREATE TABLE dbo.MyTable
(
    MyColumn VARCHAR(255)
)

但看不到如何访问 VARCHAR 大小，在本例中为 255.

But cannot see how to access the VARCHAR size, in this case, 255.

我使用的代码如下:

TSqlFragment sqlFragment = parser.Parse(textReader, out errors);

SQLVisitor myVisitor = new SQLVisitor();
sqlFragment.Accept(myVisitor);

public override void ExplicitVisit(CreateTableStatement node)
{
    // node.SchemaObjectName.Identifiers to access the table name
    // node.Definition.ColumnDefinitions to access the column attributes
}

我希望从每个列定义中找到长度属性或类似属性.但是，我也暗自怀疑您可以使用访问者模式来重新解析每个列定义，我对此很困惑.有什么想法吗?

From each column definition I expected to find a length property or similar. However, I also have a sneaking suspicion that you can use the Visitor Pattern, which I struggle with, to reparse each column definition. Any ideas?

推荐答案

很高兴您使用了 ssdt！

Great that you are using ssdt!

当您的 DBA 不想使用 dacpacs 时，处理此问题的最简单方法是使用 sqlpackage.exe 预先生成部署脚本.

The easiest way to handle this when you have DBA's who don't want to work with dacpacs is to pre-generate the deloyment script using sqlpackage.exe.

我的做法是...

• 将 t-sql 代码签入项目
• 构建服务器构建 ssdt 项目
• 在 ci 服务器上部署和运行测试
• 使用 sqlpackage.exe/action:script 将 dacpac 与 QA、PROD 等进行比较并生成部署脚本.

然后 DBA 获取该脚本(或者当我们准备好时，我们告诉他们要获取的内部版本号)- 他们可以仔细阅读并部署该脚本.

The DBA's then take that script (or when we are ready we tell them the build number to grab) - they can the peruse and deploy that script.

注意事项:

• 您将需要访问 prod db 或您可以使用的镜像副本，您不需要 dbo 或任何 (https://the.agilesql.club/Blogs/Ed-Elliott/What-Permissions-Do-I-Need-To-Generate-A-Deploy-Script-With-SSDT)
• 这些脚本仅在 prod db 中的架构更改之前有效 - 因此，如果您生成 4 个脚本并运行脚本 1，则其他三个无效，您将需要重新运行构建以生成脚本.

• You will need access to the prod db or a mirror copy you can use, you do not need dbo or anything just permissions in (https://the.agilesql.club/Blogs/Ed-Elliott/What-Permissions-Do-I-Need-To-Generate-A-Deploy-Script-With-SSDT)
• The scripts are only valid until the schema in the prod db changes - so if you generate 4 scripts the and run script 1, the other three are invalid and you will need to re-run a build to generate the script.

如果你没有 CI 设置，你可以只使用 sqlpackage.exe 来生成没有自动位的脚本:)

If you don't have CI setup you can just use sqlpackage.exe to generate the script without the automatic bits :)

希望有帮助！

编辑

这篇关于SQL Server ScriptDom 解析的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！