带测试的数据库脚本执行

引言

测试是检查代码行为和帮助编写无错误代码的非常有用的工具。在本文中，我将尝试解释在生产环境中维护应用程序数据库和通过脚本执行来处理数据时，测试将如何从中受益。实际上，我想通过我的真实生活经验来分享我的知识。

背景

测试是我们确保代码按预期工作的过程。大多数时候，我们编写针对应用程序代码（UI、业务、数据访问层）的测试，但我们对数据库脚本执行级别不太熟悉。但是，对于数据库脚本执行的目的，我们可以使用它并获得明确的结果。<u1:p>

为什么需要脚本执行

我们熟悉创建/部署数据库及其对象、修改数据库模式、插入查找数据的场景。在所有这些场景中，我们都会在数据库中执行脚本。设想一个场景，当客户从其应用程序报告一个 bug 时。您需要修复它。调查后，您发现您的代码有问题，并且您已部署该代码。但您还需要更新生产数据库中的数据。再设想一个场景，客户要求您更新一些生产数据，而您没有任何用户界面来更新这些数据。因此，您别无选择，只能执行可以修改生产数据的脚本。如果我想列出需要修改数据的脚本的原因，原因如下：

• 查找已修改某些数据的 bug。现在需要修复该数据。
• 需要满足应用程序不支持的业务需求。
• 数据迁移。
• 绕过应用程序级别安全直接修改数据。
• 需要处理数据，但应用程序没有任何用户界面。
• 暂时绕过业务规则，直接修改数据。
• 可能还有更多...

要求

需要在生产数据库中更改标准（一个实体）的状态，其编号为 #1000。假设有许多表的数需要修改，并且没有应用程序 UI 可以解决这个问题。因此，我们需要直接在生产数据库中修改数据。

那么该怎么办？

步骤如下：

• 更新 Standard 表。
• 向 Standard Version 表插入新记录，
• 更新 Last Endorsed version，
• 向 StatusChangeTable 插入新记录，
• 向 NoteTable 插入新记录，
• 向 NoteVersionTable 插入新记录。

解决方案

我们需要准备脚本文件并编写 SQL 来解决问题。SQL 如下：

DECLARE @SId INT;

SET @SId = (SELECT MId FROM Standards WHERE MNumber = '100';

--alter status in Standard Table
UPDATE Standards SET Stutatus = 99, Version = Version + 1, DTS = DTS + 1 WHERE MId = @Mid;

--add new version record for specific standard
INSERT StandardVersions SELECT * FROM Standards WHERE Id = @MId

--alter last 50 status version record from version table
UPDATE StandardVersions SET Published = 0 WHERE Id = (SELECT Max(Id) FROM Standards WHERE SId = @SId
AND Status = 50)
 
--add new note
INSERT Notes(Title, VersionId) VALUES('Status changed by user', SCOPE_IDENTITY());

--Version that note
INSERT NotesVersion SELECT * FROM Notes WHERE Id = SCOPE_IDENTITY();  

在现实生活中，我们通常会先在暂存服务器上执行该脚本。我们的 QA 团队会对其进行测试，如果测试通过，然后我们直接在生产数据库服务器上执行它。但您应该始终牢记，直接从生产数据库修改任何数据，其测试范围将非常大，QA 团队很难覆盖整个范围。这只是一个标准的单个状态更改脚本。有时我们得到 10-110 个标准的重命名、删除请求以及更多。设想一下您编写了那样的脚本，想象一下脚本的大小和复杂性，如果出现任何错误，将会产生什么后果。

风险因素

我们发现直接在生产环境中执行数据库脚本时存在风险因素。它们如下：

• 如果在脚本中输入了错误的值（表名、列名、参数值）。
• 如果漏掉了必需的表。
• 如果 *join* 操作产生交叉连接并影响比预期更多的行。
• 如果测试人员未覆盖所有场景。
• 如果测试人员跳过了某些表/列检查。
• 如果暂存服务器和生产服务器不同步。
• 可能还有更多...

降低风险因素

因此，我们可以通过添加测试块来降低脚本的风险。那么我的脚本会是什么样的？示例脚本如下：

 --Start Script 
TRY
BEGIN TRANSACTION 

DECLARE @SId INT, @NewVersionId INT, @NewNoteId INT, @Affected INT;

SET @Affected = 0;  
SET @SId = (SELECT MId FROM Standards WHERE MNumber = '100';

UPDATE Standards SET Stutatus = 99, Version = Version + 1, DTS = DTS + 1 WHERE MId =@Mid;
SET @Affected = @Affected + @@RowCount; 

INSERT StandardVersions SELECT * FROM Standards WHERE Id = @MId; 

SET @Affected = @Affected + @@RowCount; 
SET @NewVersionId = SCOPE_IDENTITY();

UPDATE StandardVersions SET Published = 0 WHERE Id = (SELECT Max(Id) FROM Standards WHERE  SId = @SId AND Status = 50); 
SET @Affected = @Affected + @@RowCount; 

INSERT Notes(Title, VersionId) VALUES('Status changed by user', NewVersionId);

SET @Affected = @Affected + @@RowCount;
SET @NewNoteId = SCOPE_IDENTITY(); 

INSERT NotesVersion SELECT * FROM Notes WHERE Id = @NewNoteId;
SET @Affected = @Affected + @@RowCount; 



--TEST BLOCK
DECLARE @StatusMain INT, @VersionMain INT, @StatusVersion INT, @VersionVersion INT;

SELECT @StatusMain = S.Status, @VersionMain = S.Version FROM Standards S WHERE S.MNumber = '100';
SELECT @StatusVersion = S.Status, @VersionVersion = S.Version FROM Standards S WHERE S.MNumber = '100';
 
IF @StatusMain = 99 AND @VersionMain = 3 AND @Statusmain = @StatusVersion AND @VersionMain = VersionVersion AND @Affected = 5  
BEGIN  
--when all my expected result will be matched then commit that transaction 
     COMMIT TRANSACTION;
    PRINT '!!!SUCCESS!!!' 
ELSE 
BEGIN 
      ROLLBACK TRNSACTION;
      PRINT @StatusMain;
      PRINT @VersionMain;
      PRINT @StatusMain ;
      PRINT @StatusVersion; 
      PRINT @Affected
      PRINT '???FAILED. PLS CORRECT SCRIPT AND TRY AGAIN????'
  END
  END
  BEGIN CATCH
  ROLLBACK TRANSACTION;
  PRINT ERROR_MESSAGE();
  END CATCH 
--End Script  

分析测试脚本块

分析上述脚本后，我们可以轻松理解测试块的目的。它将确保我们的脚本满足我们对数据处理的预期，如果发生任何灾难（原因可能是交叉/错误连接、输入错误值、数据库中存在重复数据），则 @Affected 变量将包含错误值，脚本测试将失败并回滚所有更改，并通知开发人员有错误发生，然后开发人员开始调查、识别并修复问题，从而确保我们的生产/暂存数据库服务器和数据准确无误。测试块和预期标准可能因需求而异。当执行任何存储过程来修改数据库中的数据时（绕过应用程序），可以使用相同的技术。

有时我们发现 QA 团队由于某些限制而无法从应用程序中测试每个方面。他们需要查看数据库表以确保任何新功能/修复程序都能 100% 正常工作。在这种情况下，QA 团队也会使用这种类型的测试。QA/开发人员可以存储一些预定义的测试脚本，以确保在新的实现/重构/部署完成后，任何操作都完全正常。

编写测试块的技巧

• 不要从主脚本中复制筛选条件。相反，您需要一遍又一遍地输入这些条件。因为第二次犯相同错误的几率非常小。
• 编写测试时，独立思考成功标准。也就是说，您期望的表修改类型是什么，而不是您编写的查询。
• 如果测试失败，则将 commit transaction 替换为 rollback transaction。然后尝试使用 print 语句来分析测试失败的原因，该语句会在执行窗口中显示实际值和预期值。
• 完成脚本后，当您阅读执行代码时，您的心态应该是您试图插入/更新/删除的数据，而当您阅读测试代码时，您期望的是已更改的数据。
• 如果有人比您更了解需求和表结构，那么您可以分享根据测试修改了哪些表数据，即使有遗漏，他/她也能轻松地告诉您。
• 如果测试代码越来越长，则根据优先级削减测试代码。您可以根据复杂性（连接数、子查询、复杂数据类型（XML 等））设置优先级。
• 有时我们需要在生产服务器上运行 1-2 行非常简单的数据处理查询。在这种情况下，您可以忽略它。但我的建议是，即使在那时，也要为了养成习惯而编写该测试。
• 有时您会发现很难找到可以用来测试的测试标准。在这种情况下，您需要跳出思维定势来寻找标准。
• 切勿仓促编写测试。花时间，思考，再思考，然后设定您的成功标准。
• 有时您可能会觉得脚本测试花费的时间比实际脚本本身还长。请保持耐心，在这种情况下也请坚持编写测试。
• 在估算任何脚本执行的时间时，您必须加上测试时间。即使您的上级对您的时间安排不满意，我也建议您考虑测试。否则，您的工作将面临风险。

脚本模板

您可以创建一个名为 *Template.sql* 的脚本文件，其中可能包含以下脚本代码，并在需要时仅添加您的脚本代码和带有测试条件的测试代码。

BEGIN TRY

BEGIN TRANSACTION

--Added script logic...
--TEST BLOCK
--Added test logic...
COMMIT TRANSACTION;
PRINT '!!!SUCCESS!!'

END TRY
BEGIN CATCH
   ROLLBACK TRANSACTION;
   PRINT ERROR_MESSAGE();
   PRINT '???FAILED.PLS FIX AND TRY AGAIN???'
END CATCH   

关注点

在数据库脚本执行层面应用测试技术将有所帮助，并能降低在生产数据库中需要修改数据时的风险因素。在实际操作中，当在生产数据库中执行任何数据库脚本时，需要格外小心。额外的谨慎是为了确保不会意外修改任何数据，也不会产生不一致的数据。测试技术可以作为额外的谨慎措施，以保护生产数据免遭意外修改。