正则表达式学习之调用CLR函数执行正则查询-蒲公英云

正则表达式在文本查询方面，不管是速度还是功能，都十分强大。虽然SQL Server数据库可以执行模糊查询（像like子句）和全文查询（Fulltext search），但是这两个子句只能查询简单的模式，无法应对复杂的查询需求。

在之前的公司，我们前端使用webpack构建项目，项目构建完成后，我们会使用ftp或linux的一些命令工具上传我们的文件到服务器上，这种方式虽然是可以，但是最近面试的时候，人家会问我前端如何部署项目，我就说我们公司目前是这样操作的，最后人家会感觉很low，最后总之总之没有面试上。因此今天来给大家介绍下使用Jenkins基于github来实现前端自动化集成打包部署前端资源文件。

Jenkins基本介绍: Jenkins是一个开源软件项目，它是基于java开发的一种持续集成工具，它用于监控持续重复的工作。
它最大的优点是：在开发环境或测试环境代码部署都不需要运维介入，而是相关的开发人员，测试人员登录jenkins构建需要部署的tag或分支代码即可。整个过程不需要运维参与。因此我们现在想使用jenkins的话，我们首先需要安装java开发环境。

这是因为SQL Server没有执行正则表达式的内置函数，无法直接执行正则查找。我们可以创建CLR标量函数，在函数中调用正则表达式，把CLR函数发布到SQL Server数据库中，这样，就可以通过TSQL脚本调用CLR函数来执行复杂的正则查询和匹配。

一，Regex类

Regex类用于表示一个正则表达式，执行匹配、替换和拆分操作，Regex类有五大方法：

IsMatch()：是否匹配到正则
Match()：返回正则的第一个匹配
Matches()：返回正则的全部匹配
Replace()：把匹配正则表达式的文本替换掉
Split()：把输入文本拆分，拆分的边界是匹配正则表达式的文本

1，创建Regex 对象

创建Regex对象，并指定正则选项（忽略大小写）：

Regex re = new Regex("(?<=\"UserID\":\").*?(?=\")", RegexOptions.IgnoreCase);
string mat = re.Match(input_text).Value;

也可以直接使用静态方法，直接获取到第一个匹配的值：

string mat = Regex.Match(input_txt,"(?<=\"UserID\":\").*?(?=\")", RegexOptions.IgnoreCase).Value;

2，查找匹配

按照正则来查看匹配的文本是正则表达式最常用的功能，

Regex re = new Regex("(?<=\"UserID\":\").*?(?=\")", RegexOptions.IgnoreCase);
MatchCollection mc = re.Matches(text_input);
foreach(Match mt in mc)
{
    //mt.Value
}

二，创建CLR工程

我使用的IDE版本是VS2017 Enterprise，要创建CLR工程，首先需要创建SQL Server 类型的 Project。

1，新建CLR函数

在已创建的SQL Server Project中添加新项目（Add -> New Item)，选项SQL CLR C# User Defined Function，这里把文件命名为UserDefinedFunctions.cs。

format_png

2，编写CLR代码

完整的CLR标量函数示例代码如下，Build 该文件，生成DLL文件，用该DLL文件创建程序集。

为了使用正则表达式，需要在文件中添加引用： using System.Text.RegularExpressions;

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Text.RegularExpressions;
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString Match(string input, string pattern)
    {
        string str = Regex.Match(input, pattern, RegexOptions.IgnoreCase).Value;
        return new SqlString (str);
    }
    public static SqlBoolean IsMatch(string input, string pattern)
    {
        bool match = Regex.IsMatch(input, pattern, RegexOptions.IgnoreCase);
        return new SqlBoolean(match);
    }
    public static SqlString Matches(string input, string pattern)
    {
        MatchCollection mc = Regex.Matches(input, pattern, RegexOptions.IgnoreCase);
        StringBuilder strList = new StringBuilder();
        int idx = 0;
        foreach(Match m in mc)
        {
            strList.Append(string.Format("\"idx{0}\":\"{1}\",", idx, m.Value));
            idx = idx + 1;
        }
        return new SqlString(strList.ToString());
    }
    public static SqlString SplitItem(string input, string separator, int idx)
    {
        string[] str = input.Split(new string[] { separator }, StringSplitOptions.RemoveEmptyEntries);
        return str.Length> idx ? str[idx] : "";
    }
    public static string GetJsonItem(string input, string key)
    {
        string pattern = string.Format("(?<=\"{0}\":\").*?(?=\")", key);
        return Regex.Match(input, pattern, RegexOptions.IgnoreCase).Value;
    }
}

三，在SQL Server中创建CLR函数

要在SQL Server数据库中创建CLR函数，必须配置SQL Server的选项，然后使用DLL文件创建Assembly，并从Assembly创建SQL 函数。

实现的目标是：我们的本地项目发起一个git提交后，剩下的单元测试, 打包构建，代码部署，邮件提醒等，我们会全部自动化完成部署。

3.1. 准备

首先我们随便准备一个项目(我这边是使用webpack搭建的vue项目了)，在git仓库中新建一个项目，然后把该本地项目提交到github上去。

比如我这边项目如下所示：

1，配置SQL Server的选项

为了把CLR工程部署到SQL Server数据库中，需要配置数据库的高级选项，主要是禁用clr strict security 和启用clr enabled选项。

$ sudo vim /var/lib/jenkins/secrets/initialAdminPassword

然后复制密码后填写上去，继续执行下面的步骤即可。

这个过程可能会加载很慢，我们稍等一下后，我们按照默认配置安装插件即可，如下所示:

format_png 1

2，创建程序集

引用CLR Project生成的DLL文件，用该DLL文件来创建SQL Server程序集：

CREATE ASSEMBLY [SQLServerDatabase]
FROM 'E:\clr_project_path.dll'
WITH PERMISSION_SET = SAFE
GO

3，从程序集中创建SQL函数

把SQL Server Database Project中的创建的函数，逐个创建为SQL函数。

CREATE FUNCTION [dbo].[Match](@input [nvarchar](max), @pattern [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SQLServerDatabase].[UserDefinedFunctions].[Match]
GO
CREATE FUNCTION [dbo].[IsMatch](@input [nvarchar](max), @pattern [nvarchar](max))
RETURNS bit WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SQLServerDatabase].[UserDefinedFunctions].[IsMatch]
GO
CREATE FUNCTION [dbo].[Matches](@input [nvarchar](max), @pattern [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SQLServerDatabase].[UserDefinedFunctions].[Matches]
GO
CREATE FUNCTION [dbo].[SplitItem](@input [nvarchar](max), @separator [nvarchar](max), @idx int)
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SQLServerDatabase].[UserDefinedFunctions].[SplitItem]
GO
CREATE FUNCTION [dbo].[GetJsonItem](@input [nvarchar](max), @key [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SQLServerDatabase].[UserDefinedFunctions].[GetJsonItem]
GO

在SQL函数创建之后，就可以像调用普通函数那样来调用CLR函数。

update [dbo].[DimProductPath]
set  ProductPath_ProductFamily=dbo.SplitItem(ProductPath,'/',0)
    ,ProductPath_ProductName=dbo.SplitItem(ProductPath,'/',1)
    ,ProductPath_ProductVersion=dbo.SplitItem(ProductPath,'/',2)
    ,ProductPath_SupportTopic=dbo.SplitItem(ProductPath,'/',3)
    ,ProductPath_SupportSubtopic=dbo.SplitItem(ProductPath,'/',4)

$ wget -q -O - https://pkg.jenkins.io/debian/jenkins.io.key | sudo apt-key add -
sudo sh -c ‘echo deb http://pkg.jenkins.io/debian-stable binary/ > /etc/apt/sources.list.d/jenkins.list’

$ sudo apt-get update

$ sudo apt-get install jenkins

我们现在要实现这么一个功能，当我们在本地项目往github远程仓库push我们的代码的时候，jenkins就能知道我们提交了代码，要实现这么一个功能的基本原理是：在远程仓库上需要配置一个Jenkins服务的接口地址，当本地向远程仓库发起push时候，远程仓库会向配置的Jenkins服务器的接口地址发起一个带参数的请求，当jenkins收到后开始工作。

配置步骤如下：

Github配置

1) 在Github上获取访问token的值，需要一个对项目有写权限的账户。

如果要实现自动构建的话，Jenkins需要获得远程代码仓库Github的读取权。

点击右上角的 Github —> setting —> Developer settting —> Personal access tokens -> 点击Generate new token 填写如下所示的内容：