PowerShell 使用SqlScriptDOM对T-SQL做规则校验

        对于数据项目来说,编写Sql是一项基本任务同时也是数量最多的代码。为了统一项目代码规范同时降低Code Review的成本,因此需要通过自动化的方式来进行规则校验。由于本人所在的项目以SQL Server数据库为基础,于是本人决定通过使用SqlScriptDom类库来做T-SQL的规则校验。如果是其他数据库项目,则可采用ANTLR库做规则校验,其实现的方式大体一致。

        SqlScriptDom是针对SQL Server的.Net的类库,由微软公司开发并开源,源码地址 。有兴趣的朋友可以去研究一下。其次项目采用Powershell来开发,有以下原因

  1. 使用脚本开发比较灵活,不用编译,开发即可部署。
  2. Powershell可以直接使用.Net类库,并且具有高级语言的一些特点方便开发。

项目使用VS Code作为开发调试工具,需要安装Powershell相关的插件。由于要使用到PowerShell的自定义类来开发,所以需要提前将类库加载到Powershell中,因此需要配置Powershell的环境。如何配置环境可以参考这篇文章,介绍如何创建和使用 PowerShell 配置文件。并通过Add-Type这个命令来加载它。

以下是具体代码

  

using namespace Microsoft.SqlServer.TransactSql.ScriptDom
using namespace System
using namespace System.Collections.Generic
using namespace System.IO
using namespace Management.Automation
using namespace System.Reflectionenum Severity {Information = 1Warning = 2Exception = 3Fault = 4
}enum ResponseCode {Success = 0Exception = 10001ParseError = 10002
}

     

using namespace Microsoft.SqlServer.TransactSql.ScriptDom

         这句是使用了命名空间,后面在使用相关对象时候无需采用完全限定名,从而简化代码。随后定义了两个枚举,Severity定义规则的严重程度,ResponseCode定义在程序处理过程中的各种状态。


        下面定义CustomerParser类,该类的功能是接收输入的Sql代码,通过语法和词法分析后生成相关语法树,再对语法树进行分析,从而判断代码中哪些片段是违反了项目的编码规则,从而达到Code Review的作用。

class CustomParser {hidden [TSqlParser] $TSqlParserhidden [TSqlFragment]$Treehidden $AnalysisCodeSummary = [PSCustomObject]([ordered]@{ResponseCode      = [ResponseCode]::Success;ResponseMessage   = "Success";FileName          = $null;DocumentName      = $null;Code              = $null;IsDocument        = $true;ParseErrors       = [List[ParseError]]::new();ValidationResults = [List[psobject]]::new();})hidden [bool] $IsDocumenthidden [string] $FileNamehidden [string] $Codehidden CustomParser([SqlVersion]$version, [SqlEngineType]$engineType) {switch ($version) {[SqlVersion]::Sql120 { $this.TSqlParser = [TSql120Parser]::new($true) }[SqlVersion]::Sql130 { $this.TSqlParser = [TSql130Parser]::new($true, $engineType) }[SqlVersion]::Sql140 { $this.TSqlParser = [TSql140Parser]::new($true, $engineType) }[SqlVersion]::Sql150 { $this.TSqlParser = [TSql150Parser]::new($true, $engineType) }Default { $this.TSqlParser = [TSql160Parser]::new($true, $engineType) }}}hidden [void] Parse() {$this.AnalysisCodeSummary.FileName = $this.FileName$this.AnalysisCodeSummary.IsDocument = $this.IsDocument $this.AnalysisCodeSummary.DocumentName = [Path]::GetFileName($this.FileName)[StringReader]$reader = $null[ParseError[]]$errors = @()      try {if ($this.IsDocument) { $this.Code = [File]::ReadAllText($this.FileName) }$this.AnalysisCodeSummary.Code = $this.Code$reader = [StringReader]::new($this.Code) $this.Tree = $this.TSqlParser.Parse($reader, [ref] $errors)}catch {$this.AnalysisCodeSummary.ResponseCode = [ResponseCode]::Exception$this.AnalysisCodeSummary.ResponseMessage = $_.Exception.Message            return}finally {if ($null -ne $reader) { $reader.Close() }}if ($errors.Count -ne 0) {$this.AnalysisCodeSummary.ResponseCode = [ResponseCode]::ParseError$this.AnalysisCodeSummary.ResponseMessage = "An error occurred while parsing the code."$this.AnalysisCodeSummary.ParseErrors = $errors}}hidden [void]Validate([BaseRule] $rule, [bool]$lockRule) {[psobject]$validationResult = [PSCustomObject]([ordered]@{ResponseCode        = [ResponseCode]::Success;ResponseMessage     = "Success";RuleName            = $rule.RuleName;Descrtiption        = $rule.Descrtiption;Severity            = $rule.Severity;Validated           = $true;AnalysisCodeResults = @();})$lockTaken = $falsetry {if ($lockRule) { [Threading.Monitor]::Enter($rule.AnalysisCodeResults, [ref] $lockTaken) }$rule.AnalysisCodeResults = @()$this.Tree.Accept($rule)$validationResult.AnalysisCodeResults += $rule.AnalysisCodeResults}catch {$validationResult.ResponseCode = [ResponseCode]::Exception$validationResult.ResponseMessage = $_.Exception.Messagereturn}finally {if ($lockTaken) { [Threading.Monitor]::Exit($rule.AnalysisCodeResults) }$validationResult.Validated = $validationResult.ResponseCode -eq [ResponseCode]::Success `-and (( $validationResult.AnalysisCodeResults | Where-Object { -not $_.Validated } ).Count -eq 0)if (-not $validationResult.Validated) {$this.AnalysisCodeSummary.ValidationResults += $validationResult}        }}static [psobject] Analysis([string]$codeOrFile, [bool]$isDocumnet, [BaseRule[]]$rules) {[CustomParser]$parser = [CustomParser]::new([SqlVersion]::Sql130, [SqlEngineType]::All)if (-not $isDocumnet) { $parser.Code = $codeOrFile }else { $parser.FileName = $codeOrFile }$parser.IsDocument = $isDocumnet$parser.Parse()if ($parser.AnalysisCodeSummary.ResponseCode -eq [ResponseCode]::Success) {foreach ($rule in $rules) {$parser.Validate($rule, $false)}}return $parser.AnalysisCodeSummary}static [psobject[]] Analysis([string[]]$files, [BaseRule[]]$rules) {$result = @()foreach ($file in $files) { $result += [CustomParser]::Analysis($file, $true, $rules) }return $result}
}
hidden [TSqlParser] $TSqlParser

该变量是T-SQL的分析器,通过该变量的Parse方法将SQL解析成语法树,hidden表示该变量仅在类内部使用。

 hidden [TSqlFragment]$Tree

该变量则存储分析后的语法树。

hidden $AnalysisCodeSummary = [PSCustomObject]([ordered]@{ResponseCode      = [ResponseCode]::Success;ResponseMessage   = "Success";FileName          = $null;DocumentName      = $null;Code              = $null;IsDocument        = $true;ParseErrors       = [List[ParseError]]::new();ValidationResults = [List[psobject]]::new();})

该变量是存储语法分析和规则分析的结果。

ParseErrors列表存储的是当语法分析出错时的错误结果。ValidationResults列表则存储的是每条规则校验后的结果。

hidden CustomParser([SqlVersion]$version, [SqlEngineType]$engineType) {switch ($version) {[SqlVersion]::Sql120 { $this.TSqlParser = [TSql120Parser]::new($true) }[SqlVersion]::Sql130 { $this.TSqlParser = [TSql130Parser]::new($true, $engineType) }[SqlVersion]::Sql140 { $this.TSqlParser = [TSql140Parser]::new($true, $engineType) }[SqlVersion]::Sql150 { $this.TSqlParser = [TSql150Parser]::new($true, $engineType) }Default { $this.TSqlParser = [TSql160Parser]::new($true, $engineType) }}}

CustomParser类的构造函数,$version定的时使用那个版本的分析器,比如Sql130就对应Sql Server2016,$engineType参数定义了使用哪种引擎,是Sql Server还是Azure亦或两者都采用。

CustomParser类中的Parse方法是做语法分析的。Validate方法则是做规则校验,该方法的$rule参数是传入的各种验证规则,均继承自BaseRule类。$lockRule是当采用多线程执行时是否加锁来保证结果完整。

下面则是BaseRule的代码。

class BaseRule:TSqlFragmentVisitor {[string]$Descrtiption[Severity]$Severity = [Severity]::Information$AnalysisCodeResults = @()[string]$RuleName = $this.GetType().Namehidden [string] $Additionalhidden [void] Validate([TSqlFragment] $node, [bool] $validated , [string] $addtional) {$this.AnalysisCodeResults += [BaseRule]::GetAnalysisResult($node, $validated, $addtional)}static  [BaseRule[]] GetAllRules() {return [Assembly]::GetAssembly([BaseRule]).GetTypes() `| Where-Object { $_ -ne [BaseRule] -and $_.BaseType -eq [BaseRule] } `| ForEach-Object { New-Object $_ }}static [psobject] GetAnalysisResult([TSqlFragment] $node, [bool] $validated , [string] $addtional) {return [PSCustomObject]([ordered]@{StartLine   = $node.StartLine;EndLine     = if ($node.LastTokenIndex -gt 0) { $node.ScriptTokenStream[$node.LastTokenIndex].Line } else { $node.LastTokenIndex }StartColumn = $node.StartColumn;Validated   = $validated;Text        = if ($node.FragmentLength -gt 0) `{ $node.ScriptTokenStream[$node.FirstTokenIndex..$node.LastTokenIndex].Text -join [string]::Empty } `else { $null }Additional  = $addtional     })}
}

它继承自TSqlFragmentVisitor,Validate方法用来解析被规则命中的语法节点,并记录该节点在代码中的详情,如该节点在代码中的开始行,结束行,命中的文本等,方便修改相关的SQL代码。同时将这些记录添加到AnalysisCodeResults列表,并将该列表的数据添加到CustomParser类中的ValidationResults列表中。具体规则通过重写基类的Visit方法来实现代码分析。此外还定义了一个静态方法GetAllRules用以获取项目中所有的规则。以上便是整个项目的核心代码,下面将介绍一些具体样例。

我们先做一个简单的例子,比如我们规定在Select中不能包含星号(*)。代码如下:

class PDE001: BaseRule {PDE001() {$this.Descrtiption = "Asterisk in select list."$this.Severity = [Severity]::Warning}[void] Visit([SelectStarExpression] $node) {$this.Validate($node, $false, $null)}
}

够简单了吧,首先继承自BaseRule类,然后重写Visit方法。由于Visit被重载了很多,我们选择参数类型为SelectStarExpression的方法,当语法树中存在这个节点的时候,我们调用基类的$this.Validate($node, $false, $null)方法,并记录了该节点的详情,这样就代表Sql代码没能通过该条规则。

比如我们写下这样一条Sql,Select * from test;然后通过调用来看下执行结果。

接下来我再讲一条比较复杂的规则。比如我们在做数据操作的时,为了降低对资源的占用时间。我们不能直接插入,删除或者更新大批量数据,这是就需要将数据分成小批量,然后通过循环的方式来处理。为了防止这样的代码,我们需要制定该规则。当然该规则也会有一些特例,如被处理的对象是表变量或者临时表,则可以忽略该规则。以下是该规则的代码实现


class PDE003:BaseRule {PDE003() {$this.Descrtiption = "You should use batch operations in statements."$this.Severity = [Severity]::Exception}hidden [int]$start = 0hidden [int]$end = 0[void] Visit([UpdateDeleteSpecificationBase]$node) {$target = $node.Targetif ($target -is [VariableTableReference]) { return }if ($this.CheckWhile($node)) { return }[NamedTableReference] $namedTableReference = $target -as [NamedTableReference]$targetTable = $namedTableReference.SchemaObject.BaseIdentifier.Valueif ($targetTable -imatch "^#{1,2}") { return }$fromClause = $node.FromClauseif ($null -ne $fromClause) {[TemporaryTableVisitor]$tempVisitor = [TemporaryTableVisitor]::new($fromClause, $targetTable)$fromClause.AcceptChildren($tempVisitor)if ($tempVisitor.Validated) { return }}$this.Validate($node, $false, $null)}[void] Visit([InsertSpecification]$node) {$target = $node.Targetif ($target -is [VariableTableReference]) { return }if ($this.CheckWhile($node)) { return }$namedTableReference = $target -as [NamedTableReference]if ($namedTableReference.SchemaObject.BaseIdentifier.Value -imatch "^#{1,2}") { return }$valuesInsertSource = $node.InsertSource -as [ValuesInsertSource]if ($null -ne $valuesInsertSource) { return }$this.Validate($node, $false, $null)}[void] Visit([MergeSpecification]$node) {$target = $node.Targetif ( $this.CheckWhile($node)) { return }if ($target -is [VariableTableReference]) { return }$namedTableReference = $target -as [NamedTableReference]if ($namedTableReference.SchemaObject.BaseIdentifier.Value -imatch "^#{1,2}") { return }$this.Validate($node, $false, $null)}[void] Visit([WhileStatement]$node) {$this.start = $node.StartLine$this.end = $node.ScriptTokenStream[$node.LastTokenIndex].Line}hidden [bool] CheckWhile([TSqlFragment] $node) {return $node.StartLine -ge $this.start -and $node.ScriptTokenStream[$node.LastTokenIndex].Line -le $this.end}
}class TemporaryTableVisitor:TSqlFragmentVisitor {[bool]$Validated = $falsehidden [string] $pattern = "^(@|#{1,2})"hidden [FromClause]$fromClausehidden [string]$targetTemporaryTableVisitor([FromClause]$fromClause, [string]$target) {$this.fromClause = $fromClause$this.target = $targetif ($null -eq $fromClause) { $this.Validated = $true }}[void] Visit([NamedTableReference]$node) {$tableName = $node.SchemaObject.BaseIdentifier.Value$alias = $node.Alias.Valueif ($this.target -in $alias, $tableName) {$this.Validated = $this.Validated -or ($tableName -imatch $this.pattern)}  }[void] Visit([VariableTableReference]$node) {$tableName = $node.Variable.Name$alias = $node.Alias.Valueif ($this.target -in $alias, $tableName) {$this.Validated = $this.Validated -or ($tableName -imatch $this.pattern)}  }
}

 该类还引用了另外一个辅助类,辅助类是处理当前节点为Insert、Update、Delete语句的时候,获取该语句中的FROM节点中的表对象,并判断该表是否属于临时表或者表变量,如果是则忽略该规则。

当直接输入 DELETE A FROM TEST1 A INNER JOIN TEST2 B ON A.ID=B.ID,我们可以看到规则阻挡了该语句,这时Validated属性为false。

当我们代码变成 DELETE A FROM #TEST1 A INNER JOIN TEST2 B ON A.ID=B.ID,我们看到规则通过了该段代码,且Validated属性为true。

当我们在DELETE A FROM TEST1 A INNER JOIN TEST2 B ON A.ID=B.ID 语句加上WHILE再看下呢,恭喜通过了该规则的验证。

以下是客户端调用的代码

using module '.\Code Analysis\Rule.psm1'$files = Get-ChildItem -Path "E:\BackupE\QueryFile" -Filter "*.sql" -File
$rules = [BaseRule]::GetAllRules()
$result = [CustomParser]::Analysis($files.FullName, $rules)
$result.Where({ $_.ResponseCode -eq [ResponseCode]::Success -and $_.ValidationResults.Where({ -not $_.Validated }).Count -gt 0 }) |`Select-Object -Property FileName, DocumentName -ExpandProperty ValidationResults |`Select-Object -ExpandProperty AnalysisCodeResults -ExcludeProperty Validated , AnalysisCodeResults

自此,整个代码就介绍完了,如果需要代码的话可以到以转到以下地址(下载地址)。前文提到的用ANTLR去做Code Analysis的话,需要自己去维护语法文档(文档地址),此外还需相关的工具将语法文件生成语法分析库然后调用即可。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/87235.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

2022年06月 C/C++(一级)真题解析#中国电子学会#全国青少年软件编程等级考试

第1题&#xff1a;倒序输出 依次输入4个整数a、b、c、d,将他们倒序输出&#xff0c;即依次输出d、c、b、a这4个数。 时间限制&#xff1a;1000 内存限制&#xff1a;65536 输入 一行4个整数a、b、c、d&#xff0c;以空格分隔。 0 < a,b,c,d < 108 输出 一行4个整数d、c、…

快速上手React:从概述到组件与事件处理

前言 「作者主页」&#xff1a;雪碧有白泡泡 「个人网站」&#xff1a;雪碧的个人网站 「推荐专栏」&#xff1a; ★java一站式服务 ★ ★ React从入门到精通★ ★前端炫酷代码分享 ★ ★ 从0到英雄&#xff0c;vue成神之路★ ★ uniapp-从构建到提升★ ★ 从0到英雄&#xff…

ElasticSearch学习

一&#xff0c;简介 ES&#xff08;elaticsearch简写&#xff09;&#xff0c; Elasticsearch是一个开源的高扩展的分布式全文检索引擎&#xff0c;它可以近乎实时的存储、检索数据&#xff1b;本身扩展性很好&#xff0c;可以扩展到上百台服务器&#xff0c;处理PB级别的数据…

使用 AndroidX 增强 WebView 的能力

在App开发过程中&#xff0c;为了在多个平台上保持一致的用户体验和提高开发效率&#xff0c;许多应用程序选择使用 H5 技术。在 Android 平台上&#xff0c;通常使用 WebView 组件来承载 H5 内容以供展示。 一.WebView 存在的问题 自 Android Lollipop 起&#xff0c;WebVie…

lab1 utilities

测试和运行 参考大佬 修改grade-lab-util文件中的python为python3xv6.out这个文件的所有者可能是root&#xff0c;需要修改为用户&#xff0c;sudo chown woaixiaoxiao xv6.out 每完成一个函数&#xff0c;执行下面的步骤在Makefile中加入新增的程序$U/_sleep\make qemu&…

linux 命令- systemctl

systemctl 参数说明 1、使用语法 用法&#xff1a;systemctl [OPTIONS…] {COMMAND} … 2 、参数说明 参数参数说明start立刻启动后面接的unitstop立刻关闭后面接的unitrestart立刻关闭后启动后面接的unit&#xff0c;亦即执行stop再start的意思reload不关闭后面接的unit的…

PyTorch深度学习环境安装(Anaconda、CUDA、cuDNN)及关联PyCharm

1. 关系讲解 Tytorch&#xff1a;Python机器学习库&#xff0c;基于Torch&#xff0c;用于自然语言处理等应用程序 Anaconda&#xff1a;是默认的python包和环境管理工具&#xff0c;安装了anaconda&#xff0c;就默认安装了conda CUDA&#xff1a;CUDA是一种由显卡厂商NVIDI…

取个对象值导致系统崩溃

取个对象值导致系统崩溃 前言 想必各位小伙经常在项目中遇到一些错误&#xff0c;取对象值的时候&#xff0c;经常报错,又或者某些项目突然就挂经常都是出现在一些对象取值上面&#xff0c;然后就被领导一顿训斥 报错分析 例如&#xff1a; 下面这个报错大家想必不会陌生&am…

后端开发8.品牌模块

概述 简介 效果图 数据库设计 DROP TABLE IF EXISTS `goods_brand`;CREATE TABLE `goods_brand` ( `goodsBrandId` int(11) NOT NULL AUTO_IN

浅谈机器人流程自动化(RPA)

1.什么是RPA RPA代表机器人流程自动化&#xff08;Robotic Process Automation&#xff09;&#xff0c;是一种利用软件机器人或机器人工作流程来执行重复性、规范性和高度可预测性的业务流程的技术。这些流程通常涉及许多繁琐的、重复的任务&#xff0c;例如数据输入、数据处…

系统架构设计专业技能 · 网络规划与设计(三)【系统架构设计师】

系列文章目录 系统架构设计专业技能 网络规划与设计&#xff08;三&#xff09;【系统架构设计师】 系统架构设计专业技能 系统安全分析与设计&#xff08;四&#xff09;【系统架构设计师】 系统架构设计高级技能 软件架构设计&#xff08;一&#xff09;【系统架构设计师…

0基础学习VR全景平台篇 第79篇:全景相机-泰科易如何直播推流

泰科易科技是中国的一家研发全景相机的高科技公司&#xff0c;前不久&#xff0c;在2020世界VR产业大会上发布了新一代5G VR直播影像采集终端--360starlight。以其出色的夜景成像效果和一“部”到位的直播方案重新定义了VR慢直播相机&#xff0c;对行业具有高度借鉴意义。 本文…

Uniapp使用腾讯地图并进行标点创建和设置保姆教程

使用Uniapp内置地图 首先我们需要创建一个uniapp项目 首先我们需要创建一个uniapp项目 我们在HBuilder左上角点击文件新建创建一个项目 然后下面这张图的话就是uniapp创建项目过程当中需要注意的一些点和具体的操作 然后我们创建完项目之后进入到项目pages文件夹下&#xff…

【学习FreeRTOS】第2章——FreeRTOS基础知识

1.任务调度 1.1.任务调度简介 调度器就是使用相关的调度算法来决定当前需要执行的哪个任务FreeRTOS 一共支持三种任务调度方式&#xff1a; 抢占式调度&#xff1a;针对优先级不同的任务&#xff0c;每个任务都有一个优先级&#xff0c;优先级高的任务可以抢占优先级低的任务…

Webstorm + Egg.js 进行断点调试

Webstorm Egg.js 进行断点调试 1、在工具栏找到编辑配置&#xff0c;创建已运行Node.js 应用程序的调试配置 2、debug调试配置 3、调试 4、查看断点是否起效

python爬虫相关

目录 初识爬虫 爬虫分类 网络爬虫原理 爬虫基本工作流程 搜索引擎获取新网站的url robots.txt HTHP协议 Resquests模块 前言&#xff1a; 安装 普通请求 会话请求 response的常用方法 简单案例 aiohttp模块 使用前安装模块 具体案例 数据解析 re解析 bs4…

计算机竞赛 opencv python 深度学习垃圾图像分类系统

0 前言 &#x1f525; 优质竞赛项目系列&#xff0c;今天要分享的是 &#x1f6a9; opencv python 深度学习垃圾分类系统 &#x1f947;学长这里给一个题目综合评分(每项满分5分) 难度系数&#xff1a;3分工作量&#xff1a;3分创新点&#xff1a;4分 这是一个较为新颖的竞…

WebStorm

WebStorm 介绍下载安装Activation 介绍 WebStorm是由JetBrains公司开发的一款集成开发环境&#xff08;IDE&#xff09;&#xff0c;主要专注于前端开发和Web开发。它旨在提供一套强大的工具和功能&#xff0c;以支持开发者在前端项目中编写、调试和维护代码。 JetBrains官网: …

keil下载程序具体过程2:硬件链路

引言 本篇博客将介绍keil下载程序的过程中&#xff0c;镜像文件将经过哪些硬件&#xff0c;以及简单的介绍他们之间的协议。 一、硬件连接 图1 硬件连接 将PC、jlink、芯片使用ubs线、swd线连接好之后&#xff0c;在PC上的keil软件中&#xff0c;我们选择对应的仿真器&#xf…

【算法题】螺旋矩阵II (求解n阶Z形矩阵)

一、问题的提出 n阶Z形矩阵的特点是按照之(Z)字形的方式排列元素。n阶Z形矩阵是指矩阵的大小为nn&#xff0c;其中n为正整数。 题目描述 一个 n 行 n 列的螺旋(Z形)矩阵如图1所示&#xff0c;观察并找出填数规律。 图1 7行7列和8行8列的螺旋(Z形)矩阵 现在给出矩阵大小 n&…