用正则提取SQL脚本关键字屏蔽常见错误
2013-04-12 18:45 AceYue 阅读(1427) 评论(0) 编辑 收藏 举报每天的批量部署SQL script难免会出现一些小差错,为了避免某些常见而又容易忽略的错误的出现,我们在执行前先将脚本过滤一遍,通过一些关键字来提示是否出现了不符合的设置。
本实例以创建表和给表怎加列不容许出现null列以及在执行脚本过程中不容许出现设置SET ANSI_PADDING OFF为例,利用正则获取脚本中的关键字,以检索是否符合要求。
完成这个任务我们的主要挑战是如何获取sql 脚本中的关键字,如创建新table时,我们如何判断出那一段脚本是字段列,这里就需要用的正则来提取出关键字。来看看通常创建表的脚本:
CREATE TABLE [dbo].[A]( [EmployerID] [int] NOT NULL, [InvoiceItemID] [int] NOT NULL ) ON [PRIMARY] GO
像这样的语句结构我们只需要提取出“CREATE TABLE [dbo].[A](” 与“) ON [PRIMARY] GO”之间的语句然后用‘,’分割出每个字段,即可判断是否有“NOT NULL”关键字。
但是事与愿违,我们穿件一个表可能面临着更多的情况,比如需要设置主键:
create table Ace ( AceID int, AceName nvarchar(20), CONSTRAINT [PK_Billing_SalesInvoice_Item] PRIMARY KEY CLUSTERED ( AceID ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
很明显这个脚本中,我们要提取的就是“Create table Ace (” 与 “CONSTRAINT” 之间的内容了。所以处理起来就需要分情况而定。下面来看我的处理:
/// <summary> /// Check exists "NOT NULL" when create table. /// </summary> /// Author: Ace Yue /// Date:2013-04-08 /// <param name="sqlScript">the sql script</param> protected void CheckNullableWhenCreate(string sqlScript) { string sCreateBegin = @"\s?create\s+table\s+((dbo.)?|(\[dbo\].)?)(\[?)([a-z]+[_,a-z0-9]*(\]?))(\s*)(\()(\s*)";//Get the script start when create new table. Regex regCheck = new Regex(sCreateBegin); if (regCheck.IsMatch(sqlScript)) { int iSplitStart = sqlScript.IndexOf(regCheck.Match(sqlScript).Value); int iSplitEnd = 0; string sSplit = sqlScript.Substring(iSplitStart + regCheck.Match(sqlScript).Value.Length);//Capture the script behind the table's name. string sEndGeneral = @"(\s*)(?<![0-9])(\){1})";// get the ")" but not in "(10)" string sEndSpecific = @"(\,\s*)(constraint)(\s+)((\[?)[a-z]+[_,a-z0-9]*(\]?))(\s+)(primary)(\s+)(key)(\s+)(clustered)"; //Get the "CONSTRAINT" when the script contain "CONSTRAINT". Regex regEndGeneral = new Regex(sEndGeneral); Regex regEndSpecific = new Regex(sEndSpecific); if (regEndSpecific.IsMatch(sSplit)) iSplitEnd = sSplit.IndexOf(regEndSpecific.Match(sSplit).Value); else if (regEndGeneral.IsMatch(sSplit)) iSplitEnd = sSplit.IndexOf(regEndGeneral.Match(sSplit).Value); else iSplitEnd = sSplit.Length; sSplit = sSplit.Substring(0, iSplitEnd); string[] arrScript = sSplit.Split(','); string sExistsNotNull = @"(\s+)(not)(\s+)(null)(\s*)"; Regex regExistsNotNull = new Regex(sExistsNotNull); foreach (string sItem in arrScript) { if (!regExistsNotNull.IsMatch(sItem)) { this.bPass = false; this.SReturn += "The SQL script not contain \"NOT NULL\" in some column when create table. Do you want continue ? \r\n"; } } } }
下面来看看Alter表的情况:
ALTER TABLE dbo.Billing_Plan_Payment_Bank_Log Add InvoiceReference1 NVarChar(100) NOT NULL CONSTRAINT DF_Billing_Plan_Payment_Bank_Log_InvoiceReference1 DEFAULT '', InvoiceReference2 NVarChar(100) NOT NULL CONSTRAINT DF_Billing_Plan_Payment_Bank_Log_InvoiceReference2 DEFAULT '', InvoiceReference3 NVarChar(100) NOT NULL CONSTRAINT DF_Billing_Plan_Payment_Bank_Log_InvoiceReference3 DEFAULT '' Go
处理的方面与上述情况类似,我们需要提取想要的东西就可以了:
/// <summary> /// Check the script exists "NOT NULL" when Alter table add columns. /// </summary> /// Author: Ace Yue /// Date:2013-04-08 /// <param name="sqlScript">the sql script</param> protected void CheckNullableWhenAlter(string sqlScript) { string sAlterBegin = @"(\s*)(alter)(\s+)(table)(\s+)((dbo.)?|(\[dbo\].)?)(\[?)([a-z]+[_,a-z0-9]*(\]?))(\s+)(add)(\s+)";// Get the script begin when alter table. like "ALTER TABLE dbo.Billing_Plan_Payment_Bank_Log Add" Regex regCheck = new Regex(sAlterBegin); if (regCheck.IsMatch(sqlScript)) { int iSplitStart = sqlScript.IndexOf(regCheck.Match(sqlScript).Value); string sSplit = sqlScript.Substring(iSplitStart + regCheck.Match(sqlScript).Value.Length); string sAlterEnd = @"(\s+)((go\s+)|(go$))"; Regex regEnd = new Regex(sAlterEnd); int iSplitEnd = 0; if (regEnd.IsMatch(sSplit)) iSplitEnd = sSplit.IndexOf(regEnd.Match(sSplit).Value); else iSplitEnd = sSplit.Length; sSplit = sSplit.Substring(0, iSplitEnd); string[] arrScript = sSplit.Split(','); string sExistsNotNull = @"(\s+)(not)(\s+)(null)(\s*)"; Regex regExistsNotNull = new Regex(sExistsNotNull); foreach (string sItem in arrScript) { if (!regExistsNotNull.IsMatch(sItem)) { this.bPass = false; this.SReturn += "The SQL script not contain \"NOT NULL\" in some column when add new column. Do you want continue ? \r\n"; } } } }
处理SET ANSI_PADDING OFF 这个就是太简单了,主需要查出就可以了:
/// <summary> /// Check the Script is contain "Set ANSI OFF" when /// </summary> /// Author: Ace Yue /// Date:2013-04-08 /// <param name="sqlScript">The SQL Script whose you want to check</param> protected void CheckANSI(string sqlScript) { Regex reg = new Regex(@"set\s+ansi_padding{1}\s+(off\s+|off$)");// Is there have "SET ANSI_PADDING OFF" if (reg.IsMatch(sqlScript)) { this.BPass = false; this.SReturn += "This SQL script contain \"SET ANSI_PADDING OFF\". Do you want to continue?\r\n"; } }
到了这里我们的任务就完成了,下面是我的完整Demo:
//============================================================== // Copyright (C) Moonplus.net Corporation . Ace Yue 2013-04-08 //============================================================== using System; using System.Collections.Generic; using System.Text; using System.Text.RegularExpressions; namespace SQLScript { /// <summary> /// Define check the SQL Script delegate. /// </summary> /// <param name="sqlScrit">The SQL Script.</param> public delegate void CheckScriptDel(string sqlScrit); public class ScriptCheckHelper { private bool bPass = true; public bool BPass { get { return bPass; } set { this.bPass = value; } } private string sReturn = ""; public string SReturn { get { return sReturn; } set { this.sReturn = value; } } /// <summary> /// Start invoke the check script function. /// </summary> /// Author: Ace Yue /// Date: 2013-04-08 /// Mantis:0033628: SIS 3.0 - Research - Build a small SQL script validation tools to Check SQL issues /// <param name="sqlScript">The SQL Script whose you want to check</param> public void StartCheck(string sqlScript) { CheckScriptDel checkDel = CheckNullable; checkDel += CheckANSI; checkDel(sqlScript.ToLower()); } /// <summary> /// Check the Script is contain the nullable field when create table or add new field. /// </summary> /// <param name="sqlScirpt">The SQL Script whose you want to check</param> protected void CheckNullable(string sqlScirpt) { CheckScriptDel checkNullable = CheckNullableWhenCreate; checkNullable += CheckNullableWhenAlter; checkNullable(sqlScirpt); } /// <summary> /// Check the Script is contain "Set ANSI OFF" when /// </summary> /// <param name="sqlScript">The SQL Script whose you want to check</param> protected void CheckANSI(string sqlScript) { Regex reg = new Regex(@"set\s+ansi_padding{1}\s+(off\s+|off$)");// Is there have "SET ANSI_PADDING OFF" if (reg.IsMatch(sqlScript)) { this.BPass = false; this.SReturn += "This SQL script contain \"SET ANSI_PADDING OFF\". Do you want to continue?\r\n"; } } /// <summary> /// Check exists "NOT NULL" when create table. /// </summary> /// <param name="sqlScript">the sql script</param> protected void CheckNullableWhenCreate(string sqlScript) { string sCreateBegin = @"\s?create\s+table\s+((dbo.)?|(\[dbo\].)?)(\[?)([a-z]+[_,a-z0-9]*(\]?))(\s*)(\()(\s*)";//Get the script start when create new table. Regex regCheck = new Regex(sCreateBegin); if (regCheck.IsMatch(sqlScript)) { int iSplitStart =sqlScript.IndexOf(regCheck.Match(sqlScript).Value); int iSplitEnd = 0; string sSplit = sqlScript.Substring(iSplitStart + regCheck.Match(sqlScript).Value.Length);//Capture the script behind the table's name. string sEndGeneral = @"(\s*)(?<![0-9])(\){1})";// get the ")" but not in "(10)" string sEndSpecific = @"(\,\s*)(constraint)(\s+)((\[?)[a-z]+[_,a-z0-9]*(\]?))(\s+)(primary)(\s+)(key)(\s+)(clustered)"; //Get the "CONSTRAINT" when the script contain "CONSTRAINT". Regex regEndGeneral = new Regex(sEndGeneral); Regex regEndSpecific = new Regex(sEndSpecific); if (regEndSpecific.IsMatch(sSplit)) iSplitEnd = sSplit.IndexOf(regEndSpecific.Match(sSplit).Value); else if (regEndGeneral.IsMatch(sSplit)) iSplitEnd = sSplit.IndexOf(regEndGeneral.Match(sSplit).Value); else iSplitEnd = sSplit.Length; sSplit = sSplit.Substring(0, iSplitEnd); string[] arrScript = sSplit.Split(','); string sExistsNotNull = @"(\s+)(not)(\s+)(null)(\s*)"; Regex regExistsNotNull =new Regex(sExistsNotNull); foreach (string sItem in arrScript) { if (!regExistsNotNull.IsMatch(sItem)) { this.bPass = false; this.SReturn += "The SQL script not contain \"NOT NULL\" in some column when create table. Do you want continue ? \r\n"; } } } } /// <summary> /// Check the script exists "NOT NULL" when Alter table add columns. /// </summary> /// <param name="sqlScript">the sql script</param> protected void CheckNullableWhenAlter(string sqlScript) { string sAlterBegin = @"(\s*)(alter)(\s+)(table)(\s+)((dbo.)?|(\[dbo\].)?)(\[?)([a-z]+[_,a-z0-9]*(\]?))(\s+)(add)(\s+)";// Get the script begin when alter table. like "ALTER TABLE dbo.Billing_Plan_Payment_Bank_Log Add" Regex regCheck = new Regex(sAlterBegin); if (regCheck.IsMatch(sqlScript)) { int iSplitStart = sqlScript.IndexOf(regCheck.Match(sqlScript).Value); string sSplit = sqlScript.Substring(iSplitStart + regCheck.Match(sqlScript).Value.Length); string sAlterEnd = @"(\s+)((go\s+)|(go$))"; Regex regEnd = new Regex(sAlterEnd); int iSplitEnd=0; if (regEnd.IsMatch(sSplit)) iSplitEnd = sSplit.IndexOf(regEnd.Match(sSplit).Value); else iSplitEnd = sSplit.Length; sSplit = sSplit.Substring(0, iSplitEnd); string[] arrScript = sSplit.Split(','); string sExistsNotNull = @"(\s+)(not)(\s+)(null)(\s*)"; Regex regExistsNotNull = new Regex(sExistsNotNull); foreach (string sItem in arrScript) { if (!regExistsNotNull.IsMatch(sItem)) { this.bPass = false; this.SReturn += "The SQL script not contain \"NOT NULL\" in some column when add new column. Do you want continue ? \r\n"; } } } } } }
看看运行结果:
由于正则表达式我是现学现卖,并不能保证对所有情况都考虑到,如果有遗漏欢迎提出来,谢谢!
本文出处:http://www.cnblogs.com/aces/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接。
请关注我的个人博客:www.afire.com.cn