批量处理Cache表代码

最近碰到需要把数据库所有表字符串列加上Exact特性的事。有Exact特性的字段在索引节点是不带空格的。M可以通过代码导出类代码和导入xml类文件，借助此功能实现批量处理表代码。还可以通过导入导出来用SVN管理每个M代码额，解决M代码没有版本管理的痛点。

Cache导出的表的xml如下图，xml描述了表结构，那么可以用代码分析这种xml，得到表索引信息、列信息、列类型等信息。那么就能分析和批量处理表结构代码。比如给所有String类型加Exact特性。

查询和导出所有M的后台

Query QryAllDbo(Path, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12, P13, Sessions, Output RowCount As %String) As %Query(ROWSPEC = "OutName,Type,NameSpace")
{
}

/// 查询导出所有M
/// Path：文件夹路径
/// 
///                   
/// d ##Class(%ResultSet).RunQuery("TT.LISUpGrade","QryAllDbo","D:\OUT")
ClassMethod QryAllDboExecute(ByRef qHandle As %Binary, Path, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12, P13, Sessions, Output RowCount As %String) As %Status
{
 	Set repid=$I(^CacheTemp)
	If $g(ind)="" Set ind=1
	s Path=$g(Path)
	k ^TMPLIS($zn,repid)
	s index=0
	s rset1 = ##class(%ResultSet).%New()
	d rset1.Prepare("select CLASSNAME FROM information_schema.Tables where TABLE_SCHEMA='dbo'")
	s exeret1=rset1.Execute()
	s colCount1=rset1.GetColumnCount()
	s dealNum1=0
    While(rset1.Next())
    {
        s colField=rset1.GetColumnName(1)
        s ColValue=rset1.GetDataByName(colField)
        s OutName=ColValue_".xml"
        s Type=$p(ColValue,".",1)
        s NameSpace="DHC-LISDATA"
        i $l(Path) d
        .i Path["\" d
        ..s OutPath=Path_"\"_OutName
        .e  d
        ..s OutPath=Path_"/"_OutName
        .s ret=$system.OBJ.Export(ColValue_".cls",OutPath)
        .i ret=1 d
        ..i (OutName'["dbo.") d
        ...s ^TMPLIS($zn,repid,"OUT",index)=$lb(OutName,Type,NameSpace)
        ...s index=index+1
        ..e  d
        ...d OutputData
        e  d 
        .i OutName'["dbo." d
        ..s ^TMPLIS($zn,repid,"OUT",index)=$lb(OutName,Type,NameSpace)
        ..s index=index+1
        .e  d
        ..d OutputData
    }

	
	k ^TMPLIS($zn,repid)
    Set qHandle=$lb(0,repid,0)
    Quit $$$OK
    
OutputData
	i $d(^TMPLIS($zn,repid,OutName,NameSpace)) q
	s ^TMPLIS($zn,repid,OutName,NameSpace)=""
 	s TypeI=" "
 	i $l(Type) s TypeI=Type
 	s NameSpaceI=" "
 	i $l(NameSpace) s NameSpaceI=NameSpace
 	set Data=$lb(OutName,Type,NameSpace)
 	Set ColFields = "OutName,Type,NameSpace"
 	Set ^CacheTemp(repid,ind)=##Class(LIS.Util.Common).TransListNull(Data,ColFields)
 	Set ind=ind+1
	Quit
}

ClassMethod QryAllDboClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = QryAllDboExecute ]
{
	Set repid=$LIST(qHandle,2)
 	Kill ^CacheTemp(repid)
	Quit $$$OK
}

ClassMethod QryAllDboFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = QryAllDboExecute ]
{
 	Set AtEnd=$LIST(qHandle,1)
 	Set repid=$LIST(qHandle,2)
 	Set ind=$LIST(qHandle,3)
	//
 	Set ind=$o(^CacheTemp(repid,ind))
 	If ind="" {				// if there are no more rows, finish fetching
 		Set AtEnd=1
 		Set Row=""
 	}
 	Else      {				// fetch row
 		Set Row=^CacheTemp(repid,ind)
 	}
 	// Save QHandle
 	s qHandle=$lb(AtEnd,repid,ind)
	Quit $$$OK
}

C#实现

using System;
using System.Collections.Generic;
using System.IO;
using System.Collections;
using System.Net;
using System.Net.Sockets;
using System.Text;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.DependencyInjection;
using System.Net.Http;
using System.Diagnostics;
using Microsoft.Extensions.Configuration;
using System.Xml;
using System.Runtime.InteropServices;

namespace lisupgrade
{
    /// <summary>
    /// 检验表索引处理工具
    /// </summary>
    class Program
    {
        /// <summary>
        /// 数据库地址
        /// </summary>
        static string WebServiceAddress = "";

        /// <summary>
        /// 文件编码
        /// </summary>
        private static Encoding fileEncoding = Encoding.Default;

        /// <summary>
        /// 入口
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            var builder = new ConfigurationBuilder()
            .SetBasePath(AppContext.BaseDirectory)
            .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
            .AddEnvironmentVariables();
            IConfigurationRoot configuration = builder.Build();
            WebServiceAddress = configuration.GetSection("WebServiceAddress").Value;
            Console.WriteLine("操作数据库:" + WebServiceAddress);
            string isNewModel = GetIsNewIndexMTHD();
            if (isNewModel == "1")
            {
                Console.WriteLine("当前数据库模式为新索引模式!");
            }
            else
            {
                Console.WriteLine("当前数据库模式为老索引模式!");
            }
            Console.WriteLine("请输出要处理的索引模式:1:新索引模式 0:老索引模式");
            string model = Console.ReadLine();
            if (model != "0" && model != "1")
            {
                Console.WriteLine("不支持的选项!");
                return;
            }
            //临时目录
            string path = Path.Combine(AppContext.BaseDirectory, "tmp");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            //linux下自动给工具授权
            if (RuntimeInformation.IsOSPlatform(OSPlatform.Linux))
            {
                ChMod(path);
            }
            DirectoryInfo di = new DirectoryInfo(path);
            FileInfo[] fileArr = di.GetFiles();
            //删除老文件
            foreach (var v in fileArr)
            {
                File.Delete(v.FullName);
            }
            //参数
            Parameters param = new Parameters();
            //路径，传空就不做导出
            param.P0 = path;
            //调用类名
            string ClassName = "TT.LISUpGrade";
            //调用方法名
            string FuncName = "QryAllDbo";
            //存返回的json
            string strRet = string.Empty;
            //行数
            int rowCount = 0;
            //登录信息
            string logInfo = "";
            string Err;
            Console.WriteLine("导出表代码到tmp目录,耗时较久，请耐心等待");
            //调用方法
            strRet = WebManager.GetDataJSON(WebServiceAddress, ClassName, FuncName, param, logInfo, false, out rowCount, out Err);
            di.Refresh();
            FileInfo[] fileArrNew = di.GetFiles();
            if (fileArrNew.Length == 0)
            {
                Console.WriteLine("导出表代码到tmp目录失败");
            }
            Console.WriteLine("准备处理表结构代码");
            System.Threading.Thread.Sleep(3000);
            int curNum = 0;
            Console.WriteLine("共:" + fileArr.Length + "个表");
            //处理新代码文件
            foreach (var v in fileArr)
            {
                //处理为新表结构
                if (model == "1")
                {
                    try
                    {
                        DealToNewTable(v.FullName);
                        //类名
                        string classNameUP = "TT.LISUpGrade";
                        //方法名
                        string funcNameUP = "LoadMMTHD";
                        //参数对象
                        Parameters paramUP = new Parameters();
                        //类名
                        paramUP.P0 = v.FullName;
                        paramUP.P1 = "DHC-LISDATA";
                        string sessionStr = "^^^^";
                        curNum++;
                        Console.WriteLine("完成:" + (curNum * 1.0 / fileArr.Length * 100) + "%,共:" + fileArr.Length + "个,正在处理第:" + curNum + "个");
                        //获得子版本
                        string InportRet = WebManager.GetDataJSON(WebServiceAddress, classNameUP, funcNameUP, paramUP, sessionStr, false, out rowCount, out Err);
                        Console.WriteLine("导入处理后表:" + v.FullName);
                        Console.WriteLine("返回:" + InportRet + Err);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                }
                //处理为老表结构
                else if (model == "0")
                {
                    try
                    {
                        DealToOldTable(v.FullName);
                        //类名
                        string classNameUP = "TT.LISUpGrade";
                        //方法名
                        string funcNameUP = "LoadMMTHD";
                        //参数对象
                        Parameters paramUP = new Parameters();
                        //类名
                        paramUP.P0 = v.FullName;
                        paramUP.P1 = "DHC-LISDATA";
                        string sessionStr = "^^^^";
                        curNum++;
                        Console.WriteLine("完成:" + (curNum * 1.0 / fileArr.Length * 100) + "%,共:" + fileArr.Length + "个,正在处理第:" + curNum + "个");
                        //获得子版本
                        string InportRet = WebManager.GetDataJSON(WebServiceAddress, classNameUP, funcNameUP, paramUP, sessionStr, false, out rowCount, out Err);
                        Console.WriteLine("导入处理后表:" + v.FullName);
                        Console.WriteLine("返回:" + InportRet + Err);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                }
            }
            Console.WriteLine("处理完成,按任意键结束!");
            Console.ReadLine();
        }

        /// <summary>
        /// 授权
        /// </summary>
        /// <param name="path"></param>
        private static void ChMod(string path)
        {
            if (!path.Contains("/"))
            {
                return;
            }
            var psi = new ProcessStartInfo("chmod", " -R 777 " + path)
            {
                RedirectStandardOutput = true,
                RedirectStandardInput = true
            };
            Console.WriteLine("执行:" + "chmod -R 777 " + path + "命令");
            //启动进程
            Process proc = Process.Start(psi);
        }

        /// <summary>
        /// 得到是否是新模式
        /// </summary>
        /// <param name="model"></param>
        static string GetIsNewIndexMTHD()
        {
            //参数
            Parameters param = new Parameters();

            //调用类名
            string ClassName = "TT.LISUpGrade";
            //调用方法名
            string FuncName = "GetIsNewIndexMTHD";
            //存返回的json
            string strRet = string.Empty;
            //行数
            int rowCount = 0;
            //登录信息
            string logInfo = "";
            string Err;
            //调用方法
            strRet = WebManager.GetDataJSON(WebServiceAddress, ClassName, FuncName, param, logInfo, false, out rowCount, out Err);
            return strRet;
        }


        /// <summary>
        /// 处理代码为老表结构
        /// </summary>
        /// <param name="fileFullName"></param>
        static void DealToOldTable(string fileFullName)
        {
            string localStr = ReadTxt(fileFullName);
            string newCodeStr = localStr.Replace("<Parameter name=\"COLLATION\" value=\"Exact\"/>\r\n", "");
            if (newCodeStr != localStr)
            {
                TxtUtil.WriteTxt(fileFullName, newCodeStr, true);
            }
        }

        /// <summary>
        /// 处理代码为新表结构
        /// </summary>
        /// <param name="fileFullName"></param>
        static void DealToNewTable(string fileFullName)
        {
            //处理非dbo类继承持久类代码
            if (fileFullName.Contains("dbo.") && (!fileFullName.Contains("dbo.SYSParameter")) && (!fileFullName.Contains("dbo.PT")))
            {
                XmlDocument xmldoc = new XmlDocument();
                string localStr = ReadTxt(fileFullName);
                xmldoc.LoadXml(localStr);
                XmlNodeList nodes = xmldoc.ChildNodes[1].ChildNodes[0].ChildNodes;
                //表名
                string tableName = xmldoc.ChildNodes[1].ChildNodes[0].Attributes["name"].Value;

                //遍历处理节点
                if (nodes != null && nodes.Count > 0)
                {
                    //字符串列
                    List<string> strCols = new List<string>();
                    Dictionary<string, string> dicIndex = new Dictionary<string, string>();
                    foreach (XmlNode n in nodes)
                    {
                        //索引
                        if (n.Name == "Index")
                        {
                            XmlNodeList nodePros = n.ChildNodes;
                            string indexName = n.Attributes["name"].Value;
                            if (nodePros != null && nodePros.Count > 0)
                            {
                                foreach (XmlNode np in nodePros)
                                {
                                    if (np.Name == "Properties")
                                    {
                                        dicIndex.Add(indexName, np.InnerText);
                                    }
                                }
                            }
                        }
                        //属性
                        if (n.Name == "Property")
                        {
                            XmlNodeList nodePros = n.ChildNodes;
                            string colName = n.Attributes["name"].Value;
                            if (nodePros != null && nodePros.Count > 0)
                            {
                                //是否是字符串
                                bool isStr = false;
                                //是否加了属性
                                bool hasPro = false;
                                foreach (XmlNode np in nodePros)
                                {
                                    if (np.Name == "Type")
                                    {
                                        string npVal = np.InnerText;
                                        //字符串类型
                                        if (npVal == "%Library.String")
                                        {
                                            isStr = true;
                                        }
                                    }
                                    if (np.Name == "Parameter" && np.Attributes["name"].Value == "COLLATION")
                                    {
                                        hasPro = true;
                                    }
                                }
                                if (isStr)
                                {
                                    strCols.Add(colName);
                                }
                                //添加特性
                                if (isStr == true && hasPro == false)
                                {
                                    XmlElement newElement = xmldoc.CreateElement("Parameter");
                                    newElement.SetAttribute("name", "COLLATION");
                                    newElement.SetAttribute("value", "Exact");
                                    n.AppendChild(newElement);
                                }
                            }
                        }

                    }
                }
                string standStr = ConvertXmlToString(xmldoc);
                WriteTxt(fileFullName, standStr, true);
            }
        }

        /// <summary>
        /// 写入数据到指定文件
        /// </summary>
        /// <param name="path">文件全路径</param>
        /// <param name="str">数据</param>
        /// <param name="isReplace">是否提换，默认为替换，否则为添加</param>
        /// <returns></returns>
        public static bool WriteTxt(string path, string str, bool isReplace = true, Encoding ecod = null)
        {
            if (ecod == null)
            {
                ecod = new UTF8Encoding(false);
            }
            FileStream fs = null;
            StreamWriter sw1 = null;
            try
            {
                //如果文件不存在，先创建一个
                if (!File.Exists(path))
                {
                    //创建写入文件  
                    fs = new FileStream(path, FileMode.Create, FileAccess.Write);
                    sw1 = new StreamWriter(fs, ecod);
                    //开始写入值  
                    sw1.WriteLine(str);
                }
                else
                {
                    //如果是替换，先清除之前的内容
                    if (isReplace)
                    {
                        using (StreamWriter sw = new StreamWriter(path, false, ecod))
                        {
                            sw.Write("");
                            sw.Close();
                        }
                    }
                    fs = new FileStream(path, FileMode.Append, FileAccess.Write);
                    sw1 = new StreamWriter(fs, ecod);
                    sw1.WriteLine(str);
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                if (sw1 != null)
                {
                    sw1.Close();
                }
                if (fs != null)
                {
                    fs.Close();
                }
            }
        }

        /// <summary>  
        /// 将XmlDocument转化为string  
        /// </summary>  
        /// <param name="xmlDoc">XML文件</param>  
        /// <returns></returns>  
        private static string ConvertXmlToString(XmlDocument xmlDoc)
        {
            MemoryStream stream = new MemoryStream();
            XmlTextWriter writer = new XmlTextWriter(stream, null);
            writer.Formatting = Formatting.Indented;
            xmlDoc.Save(writer);
            StreamReader sr = new StreamReader(stream, System.Text.Encoding.UTF8);
            stream.Position = 0;
            string xmlString = sr.ReadToEnd();
            sr.Close();
            stream.Close();
            return xmlString;
        }

        /// <summary>
        /// 读取文件数据
        /// </summary>
        /// <param name="path">文件全路径</param>
        /// <returns></returns>
        public static string ReadTxt(string path)
        {
            //文件不存在
            if (!File.Exists(path))
            {
                return "";
            }
            FileStream fs = null;
            try
            {

                fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
                StreamReader sr = new StreamReader(fs, new UTF8Encoding(false));
                string str = sr.ReadToEnd();
                return str;
            }
            catch (Exception ex)
            {
                return "";
            }
            finally
            {
                fs.Close();
            }
        }
    }
}