65.9K
CodeProject 正在变化。 阅读更多。
Home

比较 SQL 数据库模式版本的工具

Lindsey Lewis
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.53/5 (9投票s)

2009年2月17日

LGPL3

7分钟阅读

viewsIcon

111585

一篇关于比较 SQL 数据库模式版本的工具的文章。

SchemaToolUse.jpg

引言

任何大小的 SQL 数据库开发项目，在 SDLC 持续时间里，代码和数据库模式经常会发生变化或修改，这与先前交付给客户的版本不同，这是开发中面临的挑战。

除非有严格的项目流程控制，否则要确保先前交付的模式与代码保持同步并不容易。我曾参与过几个项目，每个项目都有不同的方法来确保代码和数据库模式保持同步。

我称之为 SQL Schema Tool (SST) 的这个工具，就是为了解决比较或提供更新脚本的问题而创建的，这些脚本可以处理在迁移到新版本时，同步客户数据库模式从旧版本到新版本的问题。

一种交付现有数据库模式变更的方法是创建一个具有更新模式的新数据库，然后使用某种 ETL 过程将数据从旧数据库迁移到新数据库。然而，根据我的客户经验，他们不愿意更换整个生产数据库。

我使用 SST 来解决这个问题的办法是提供一种方法，将已交付的客户模式捕获为一个描述该模式的 XML 快照。这个客户数据库模式的快照可以与当前的开发数据库模式进行比较，而无需主动连接，并提供一个 SQL 更新脚本来运行在客户数据库上。

当前特性

  • 模式 XML 快照，用于无需数据库的便携式 SQL 模式生成/比较
  • 模式生成 - 可选择对象类型
  • 模式比较 - 可选择对象类型
  • DTS 包 XML 快照，用于创建便携式 DTS 包
  • 两个命令行工具用于执行上述功能。这应该允许 ant 构建或其他自动化工具自动比较或生成模式更新。
  • 数据比较 - 目前仅限于单个表
  • 数据导出 - CSV 和 XLS，计划添加文本文件输出，用于 SQL insert 语句
  • 数据比较报告，模式；表、视图、函数和存储过程
  • XML 树浏览器，支持 XPath 搜索由工具生成的 XML 快照
  • 在选定的数据库上运行生成的 SQL，无需切换到另一个工具
  • 记录所有事件、操作和错误 - 包括 SQL 相关错误和应用程序类型错误。
  • 自定义用户定义的 XSLT 转换可以自动应用于生成的 SQL 模式和数据 XML 输出。

背景

我曾经参与过 QueryCommander SQL 编辑器项目。 **SST** 中使用的一些设计方法来自我处理代码库的经验。

SQL Schema Tool (SST) 最初是一个命令行项目，用于将 DTS 包作为 XML 从数据库中导出。后来添加了额外的代码，用于处理开发数据库中存在而 QA 数据库中不存在的模式变更，并创建更新脚本，在持续构建过程中针对 QA 数据库运行。

然后，模式工具被改造成了一组可以从命令行或 Winform GUI 应用程序调用的公共类。我曾想过销售这款软件，但似乎没有引起太大的兴趣。因此，我写了这篇文章，并根据附带的许可证以及代码中使用的库的任何许可证，免费提供我的代码。

Using the Code

代码是针对 Microsoft SQL Server 2000 和 SQL Server 2005 编写和测试的。我已经将类分离开，可以轻松地修改以支持其他供应商的数据库，如 MYSQL 或 DB2、Oracle；尽管 DTS 包类只适用于 SQL 2000。

模式模块的架构设计是为了以对象级别来处理数据库。这允许选择单个对象组进行比较或生成模式操作。它还允许应用程序用户将其注意力集中在数据库中正在变化或经常变化的具体对象上。

当您检查 XML 快照的输出时，可以通过注意 XML 节点或元素按对象类型分组并按对象名称排序来看到这种方法。

模式模块的所有主要输出都是 XML 格式，然后通过使用 XSLT 和 XML 转换，将其转换为适当的输出。

SQL Schema Tool 被设计为考虑一个主数据库或源数据库，这是项目中当前或最新数据库模式。目标或目标数据库被 SST 视为旧模式，或者需要更新的模式。

主要设计考虑因素

  • 保持目标数据库中存在的任何数据完整
  • 通过保存源数据库、目标数据库或两者之一的 XML 模式快照，能够在完全或部分断开连接模式下进行模式比较
  • 使用 XSLT 将 XML 转换为适当的对象

下面是 SQLSchemaTool 类的简化序列图。

SSTSequenceDiagram.jpg

SQLSchemaTool 类使用 SQLObjects 命名空间和类：Tables、Views、Sprocs 等，它们将这些对象的模式序列化为 XML。数据库中对象的代码在适当的地方使用了泛型。

public static string SerializeDB(
    string SQLServer,
    string DBName,
    string UID,
    string PWD,
    string SQLfile,
    bool Translate,
    bool Primary,
    object threaded,
    byte objectsToSerialize,
    string CustomXSLT,
    string delimTableNames)
{
    _threaded = threaded;
    string _serverDB = SQLServer + ":" + DBName;
    string outputFile = string.Format
		(_OUTPUTFILE, SQLServer.Replace("\\", "_").Replace(":", "-"),
                DBName.Replace("\\", "_").Replace(":", "-"));
    try
    {
        // TODO:  add threads if this takes a long while
        SQLMethods.SQLConnections _connections;
        if (UID != null && PWD != null)
        {
            _connections = new SQLMethods.SQLConnections
				(SQLServer, DBName, UID, PWD, false);
        }
        else
        {
            _connections = new SQLMethods.SQLConnections(SQLServer, DBName);
        }
        if (_connections != null && _connections.Count > 0)
        {
            DataSet _ds = new DataSet("DataBase_Schema");
            _ds.EnforceConstraints = false;
            DataTable dt = _ds.Tables.Add("Database");
            dt.Columns.Add("Name");
            dt.Columns.Add("Date");
            dt.Columns.Add("Time");
            DataRow dr = dt.NewRow();
            dr.ItemArray =
                 new object[] { DBName, DateTime.Now.ToShortDateString(),
			DateTime.Now.ToShortTimeString() };
            dt.Rows.Add(dr);

            Sleep();

            // get defaults, rules and UDDTs :
		    // in this order because of dependant behavior
            if ((objectsToSerialize & Convert.ToByte(_NodeType.DEFAULT)) ==
			(int)_NodeType.DEFAULT)
            {
                SQLObjects.Defaults _defaults = new SQLObjects.Defaults(DBName);
                _defaults.GetObject<sqlobjects.defaults>(_connections[0].sqlConnection);

                _ds.Merge(_defaults);
            }
            Sleep();

            if ((objectsToSerialize & Convert.ToByte(_NodeType.RULE)) ==
			(int)_NodeType.RULE)
            {
                SQLObjects.Rules _rules = new SQLObjects.Rules(DBName);
                _rules.GetObject<sqlobjects.rules>(_connections[0].sqlConnection);

                _ds.Merge(_rules);
            }
            Sleep();

            if ((objectsToSerialize & Convert.ToByte(_NodeType.UDDT)) ==
			(int)_NodeType.UDDT)
            {
                SQLObjects.UDDTs _uddts = new SQLObjects.UDDTs(DBName);
                _uddts.GetObject<sqlobjects.uddts>(_connections[0].sqlConnection);

                _ds.Merge(_uddts);
            }
            Sleep();

            if ((objectsToSerialize & Convert.ToByte(_NodeType.TABLE)) ==
			(int)_NodeType.TABLE)
            {
                SQLObjects.Tables _tables = new SQLObjects.Tables(DBName);
                if (!string.IsNullOrEmpty(delimTableNames))
                {
                    _tables.GetObject<sqlobjects.tables>(_connections[0].sqlConnection,
						Primary, delimTableNames);
                }
                else
                {
                    _tables.GetObject<sqlobjects.tables>
			(_connections[0].sqlConnection, Primary);
                }
                // TODO:  make work with DBs attached as MDF files to SQL 2005
                _ds.Merge(_tables);
            }
            Sleep();

            if ((objectsToSerialize & Convert.ToByte(_NodeType.VIEW)) ==
			(int)_NodeType.VIEW)
            {
                SQLObjects.Views _views = new SQLObjects.Views(DBName);
                _views.GetObject<sqlobjects.views>(_connections[0].sqlConnection);

                _ds.Merge(_views);
            }
            Sleep();

            if ((objectsToSerialize & Convert.ToByte(_NodeType.SPROC)) ==
			(int)_NodeType.SPROC)
            {
                SQLObjects.Sprocs _sprocs = new SQLObjects.Sprocs(DBName);
                _sprocs.GetObject<sqlobjects.sprocs>(_connections[0].sqlConnection);

                _ds.Merge(_sprocs);
            }
            Sleep();

            if ((objectsToSerialize & Convert.ToByte(_NodeType.FUNCTION)) ==
			(int)_NodeType.FUNCTION)
            {
                SQLObjects.Funcs _funcs = new SQLObjects.Funcs(DBName);
                _funcs.GetObject<sqlobjects.funcs>(_connections[0].sqlConnection);

                _ds.Merge(_funcs);
            }
            Sleep();

            if ((objectsToSerialize & Convert.ToByte(_NodeType.TRIGGER)) ==
			(int)_NodeType.TRIGGER)
            {
                SQLObjects.Triggers _triggers = new SQLObjects.Triggers(DBName);
                _triggers.GetObject<sqlobjects.triggers>(_connections[0].sqlConnection);

                _ds.Merge(_triggers);
            }
            // TODO: add jobs, users, roles

            Sleep();

            // get rid of old files
            if (File.Exists(outputFile)) File.Delete(outputFile);

            // write out xml schema document
            XmlDataDocument xmlData = new XmlDataDocument(_ds);
            //xmlData.Save(outputFile);

            // reload to xml schema to avoid the "deleted row" error
		    // when removing the dependant child nodes
            XmlDocument xmlDoc = new XmlDocument();
            //xmlDoc.Load(outputFile);
            xmlDoc.LoadXml(xmlData.OuterXml);

            Sleep();

            // sort the dependencies for views, functions, and stored procedures
            SortDependencies(_serverDB, VIEWPATH, VIEWDEP, ref xmlDoc);
            SortDependencies(_serverDB, FUNCPATH, FUNCDEP, ref xmlDoc);
            SortDependencies(_serverDB, SPROCPATH, SPROCDEP, ref xmlDoc);

            foreach (Char c in Path.GetInvalidFileNameChars())
            {
                outputFile = outputFile.Replace(c, '_');
            }
            foreach (Char c in Path.GetInvalidPathChars())
            {
                outputFile = outputFile.Replace(c, '_');
            }
            xmlDoc.Save(outputFile);

            // perform garbage collection to free up memory
            GC.Collect();

            if (Translate && outputFile != null && outputFile.Trim().Length > 0)
            {
                string createName = outputFile.ToLower().Replace(".xml", ".sql");
                if (SQLfile != null && SQLfile.Length > 0)
                {
                    createName = SQLfile.ToLower().Replace(".xml", ".sql");
                }
                if (!createName.EndsWith(".sql")) { createName += ".sql"; }
                XsltHelper.SQLTransform(outputFile,
				XsltHelper.SQLCREATEXSLT, createName);
                outputFile += "," + createName;
                logger.Info("\nSQL Create Schema has been saved to " +
					createName + ".");
            }
            if (CustomXSLT != null && CustomXSLT.Trim().Length > 0)
            {
                FileInfo fi = new FileInfo(CustomXSLT);
                File.WriteAllText("CustomOutput.XML",
			XsltHelper.Transform(xmlDoc.OuterXml, fi));
                logger.Info("\nThe Custom XSLT {0},
			has been applied and saved as
				CustomOutput.XML.", CustomXSLT);
            }
        }
    }
    catch (Exception ex)
    {
        if (ex is System.Data.SqlClient.SqlException)
        {
            logger.Error("\nSQL Error: {0}, DB Server {1}",
						ex.Message, _serverDB);
        }
        else
        {
            logger.Error(ERRORFORMAT, ex.Message, ex.Source, ex.StackTrace);
        }
    }
    return outputFile;
}

一旦数据库对象被序列化为 XML，代码就可以对 XML 执行 XSLT 转换，生成 SQL 或其他输出。

此外，如果存在两个这样的数据库 XML 表示，则可以比较这些序列化的数据库。该比较的结果以 XML 输出，然后可以再次转换为更新目标数据库所需的 SQL。

SQLSchemaTool_Class.jpg

TablesClassDiagram.jpg

关注点

这是该工具生成的 XML 输出的屏幕截图

XMLDocument.jpg

这是应用 XSLT 转换后该工具生成的 SQL 输出的屏幕截图

SQLDocumentWindow.jpg

这是应用 XSLT 转换后由该工具创建的 HTML 差异报告的屏幕截图

sst_html_report.jpg

解决方案构建

构建过程使用 ILMerge 在 pre/post 构建命令中将项目的 DLL 合并成一个单独的程序集。请参阅 Microsoft Research 下载此工具。这使得部署安装程序更容易设计。

对于 SQLSchemaToolGUI 项目，请编辑从项目属性访问的 pre 和 post 构建事件。

在 pre 和 post 构建事件中都有一个初始行，其中包含

del "$(TargetDir)merge.log"

该行应更改为

IF EXIST "$(TargetDir)merge.log" del "$(TargetDir)merge.log"

这是必需的，因为我最初从命令行运行了 merge.bat 文件，该文件创建了 merge.log 文件。所以我就没考虑过日志文件可能不存在。

日志文件是 .NET iLMerge 工具的输出日志，批处理文件使用它将所有单独的 DLL 合并成一个 SSTassemblies.dll 文件。

待办事项

  • 这些工具需要大量测试，我确信仍然存在 bug :)
  • 改进 SQL 对象依赖性计算和性能
  • 设计 XSLT 以使用 .NET 生成基础数据访问类 - 类似于 MyGeneration 工具
  • 创建 MySql、DB2 和 Oracle 的其他数据库提供程序类
  • 增强数据比较功能

致谢

  • Weifen Luo - DockPanel Suite 旨在为 MDI 窗体实现停靠功能。可用于开发 Visual Studio .NET 风格的应用程序。
  • #develop - SharpDevelop 是一个 .NET 平台的开源 IDE。SST 使用 ICSharpCode.TextEditor。
  • QueryCommander - QueryCommander 开发团队提供了处理 SQL Server 的想法和方法。
  • NLog - NLog 是一个 .NET 日志库，设计简洁灵活。
  • WIX - Windows Installer XML (WiX) 是一个从 XML 源代码构建 Windows 安装包的工具集。该工具集支持命令行环境，开发人员可以将其集成到他们的构建过程中来构建 MSI 和 MSM 设置包。
  • XML Diff - Microsoft XML Diff Tool。

更多信息

历史

  • 2005 年 1 月 - 完成命令行工具
  • 2007 年 6 月 - 完成 GUI 版本
  • 2007 年 9 月 - 开始 Beta 测试
  • 现在 - 决定免费提供源代码
© . All rights reserved.