Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Data testing in data Migration

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Internet Technology >

Share

Shulou(Shulou.com)06/03 Report--

I haven't written my own blog in a long time. After the automation test project ended, I participated in some new projects, some unrelated to test automation. At present, we are doing data migration testing, that is, migrating business data from an old system to a new system. Most of the tests focus on data testing, primarily based on BMD test data field correspondence. Test cases are written in SQL scripts. In order to invoke these test cases, a simple test framework was also written, along the following lines:

Main.sql

This document has two parts,

1) Generate a TestResult table to record the results of each test case, such as:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [TestResult]') AND type in (N'U')) DROP TABLE [dbo]. [TestResult]BEGIN CREATE TABLE [dbo]. [TestResult]( [ResultId] int identity(1,1) NOT NULL, [TestCaseId] nvarchar(255) NOT NULL, [ProcedureName] nvarchar(255) NOT NULL, [TestCaseResult] nvarchar(255) NOT NULL, [TestLog] nvarchar(max) NULL, [ExecutionTimeStamp] datetime NULL ) INSERT INTO [dbo]. [TestResult] VALUES ('C-301','AccountID','NOT RUN','',CURRENT_TIMESTAMP), ...... END

2) Call the stored procedure corresponding to each test case.

EXECUTE C_AccountID

2. Each piece of data has its own file, such as Account.sql. In this file, the stored procedures for automating test cases are included. Example:

--C-301--------------Start of AccountID Procedure-------------------USE TestDatabaseGOIF OBJECT_ID ('dbo.AccountID', 'P') IS NOT NULL DROP PROCEDURE dbo.AccountIDGOCREATE PROCEDURE dbo.AccountIDASBEGIN DECLARE @AccountIDCount int DECLARE @DupCount int DECLARE @LegalCount int DECLARE @Result nvarchar(10) DECLARE @Comments nvarchar(255) /* Test the account id's mapping */ SET @AccountIDCount = 0 SET @AccountIDCount = (SELECT COUNT(*) FROM [MasterData] T LEFT JOIN [TargetList] S ON CAST( T. [iAccountID] as nvarchar) = CAST( S. [PARTYID] as nvarchar) collate Latin1_General_CI_AS WHERE T. [iAccountID] IS NULL ) /* Check there is no duplicate account id */ SET @DupCount = 0 SET @DupCount = (SELECT COUNT(*) FROM [MasterData] WHERE (iAccountID in (select iAccountID from [MasterData] group by iAccountID having count(*)>1) )) /* check the account id is 10 digits */ SET @LegalCount = (SELECT * FROM [Rex_DCL5_LDS_Customer_STG_MasterData] WHERE iAccountID not like '00000000%') IF @AccountIDCount + @DupCount + @LegalCount= 0 BEGIN SET @Result = 'PASS' SET @Comments = '[iAccountID] are correct. ' END ELSE BEGIN SET @Result = 'FAIL' SET @Comments = 'There are: ' + CAST(@AccountIDCount as nvarchar) + 'records of T data are not contained in S data. '+ CAST(@DupCount as nvarchar) + ' records are duplicated' + CAST(@LegalCount as nvarchar) + ' records are illegal' END IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [TestResult]') AND type in (N'U')) BEGIN UPDATE [dbo]. [TestResult] SET TestCaseResult = @Result, TestLog = @Comments, ExecutionTimeStamp = CURRENT_TIMESTAMP WHERE ProcedureName = 'AccountID' END ENDGO--C-301------------End of dbo.AccountID Procedure-------------------

Executing tests is to first Account.sql, produce stored procedures for each test case, and then open Main.sql to invoke these stored procedures.

The framework itself is very simple, easy to extend and maintain, and a great starting point for data migration testers.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Internet Technology

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report