컴퓨토피아

저장 프로시저의 테스트 자동화

James McCaffrey _ Volt Information Sciences, Inc.의 직원
testrun@microsoft.com.

많은 Windows 기반 응용 프로그램에는 저장 프로시저가 포함된 SQL Server 백 엔드 구성 요소가 있습니다. 프런트 엔드 코드로 기능을 자동으로 테스트하는 기법은 잘 알려져 있지만 저장 프로시저의 테스트 자동화를 작성하는 기법은 별로 알려져 있지 않습니다. SQL Server 2005는 크게 향상된 Microsoft .NET Framework과의 통합 능력을 비롯하여 C# 및 기타 .NET 대상 언어로 저장 프로시저를 작성하는 기능을 제공합니다. 이로 인하여 확실히 저장 프로시저의 사용과 저장 프로시저에 대한 철저한 테스트의 중요성이 증가하였습니다.

이 문서에 사용된 코드 다운로드: TestRun0409.exe (124KB)

이 칼럼에서는 SQL Server 2005를 처음 사용할 때 미리 준비할 수 있도록 단순하지만 대표적인 SQL 저장 프로시저를 만드는 방법을 안내한 다음 그 기능을 검증하는 강력한 테스트 자동화를 빠르게 작성하는 방법을 알려줍니다. 또한 SQL 저장 프로시저에 대한 테스트 자동화를 작성하는 주요 원칙을 설명합니다.

데이터베이스에 액세스하는 모든 응용 프로그램 또는 서비스는 저장 프로시저를 사용할 수 있습니다. 이러한 경우 SQL 저장 프로시저를 응용 프로그램의 보조 기능으로 생각할 수 있습니다. 응용 프로그램의 개별 기능에 대한 테스트 자동화 작성을 보통API 테스트라고 하며 이러한 테스트를 작성하기 위한 단위 테스트 방법은 잘 알려져 있습니다. 그러나 앞에서 언급한 것처럼 SQL 저장 프로시저에 대한 테스트 자동화의 작성은 그다지 잘 알려져 있지 않습니다.


그림 1 직원 데이터베이스 액세스

학습 내용을 가장 잘 이해할 수 있도록 두 그림을 이용하여 설명하겠습니다. 직원 정보 데이터베이스에 액세스하는 응용 프로그램을 개발한다고 가정합시다. 그림 1은 단순하지만 대표적인 그러한 데이터베이스의 예제입니다. List Employees라는 단추를 누르면 포함된 SQL 문을 이용하여 백 엔드 SQL 데이터에 액세스하지만 Employee Status Code는 usp_empStatusCode라는 저장 프로시저를 이용하여 검색됩니다. 이것이 테스트할 저장 프로시저입니다. 그림 2는 그 테스트를 수행하는 테스트 실행을 보여줍니다.


그림 2 저장 프로시저 테스트 결과

이 예제에서 테스트 자동화는 프로그래밍 방식으로 직원 ID를 저장 프로시저에 공급하고 실제 상태 코드 반환 값을 포착하고 예상 값과 비교하여 테스트가 성공인지 실패인지를 결정합니다. 결과는 테스트 파일에 쓰여집니다. 여기에서는 6개의 테스트 사례만 사용합니다. 그러나 실제 환경에서는 물론 수천 개의 사례가 있을 것입니다.

이면에는 dbProseware라고 하는 SQL 데이터베이스에 tblEmployees라는 직원 정보 테이블이 있습니다. 다음 코드 조각에 표시된 바와 같이 예제 응용 프로그램은 먼저 데이터베이스에 연결한 다음 저장 프로시저 호출을 준비하여 usp_empStatusCode 저장 프로시저를 호출합니다.

string connString =
"server=(local);database=dbProseware;Integrated Security=SSPI";
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlCommand cmd = new SqlCommand("usp_empStatusCode", conn);
cmd.CommandType = CommandType.StoredProcedure;

그런 다음 응용 프로그램은 저장 프로시저 반환 값과 단일 입력 매개 변수를 설정합니다.

SqlParameter p=cmd.Parameters.Add("ret_val", SqlDbType.Int, 1);
p.Direction = ParameterDirection.ReturnValue;

p=cmd.Parameters.Add("@empID", SqlDbType.Char, 3);
p.Direction = ParameterDirection.Input;
p.Value = textBox1.Text;

그리고 최종적으로 응용 프로그램은 저장 프로시저를 호출하여 반환 값을 얻고 다음과 같이 응용 프로그램에 반환 값을 표시합니다.

cmd.ExecuteNonQuery();
int code =
(int)cmd.Parameters["ret_val"].Value;
textBox2.Text = code.ToString();

응용 프로그램에서 저장 프로시저를 사용할 때 추가적인 프로그래밍 작업이 종종 필요하지만 저장 프로시저는 스크립트 주입 공격을 받을 가능성이 더 적고, 대개 더 우수한 성능을 제공하며, 더 정교한 다중 문 논리를 허용하고 더 효과적인 프로그램 구조를 작성할 수 있습니다.

응용 프로그램의 UI를 사용하여 이 저장 프로시저를 수동으로 테스트하는 작업은 매우 지루하고 시간이 오래 걸리며 오류가 발생하기 쉽습니다. 그리고 제품 코드가 변경될 때마다 처음부터 다시 테스트해야 합니다. 그러므로 그림 2에서와 같이 테스트 사례 데이터를 프로그래밍 방식으로 전송하고 반환 값을 검토하여 저장 프로시저를 테스트해 봅시다. 여기서는 전체 시스템을 테스트하는 것이 아니라 응용 프로그램의 저장 프로시저만을 테스트한다는 것을 알아두십시오.

테스트할 저장 프로시저

특별 생산 환경에서 엔터프라이즈 관리자 프로그램을 사용하여 지속적으로 백 엔드 데이터베이스를 만들 수 있습니다. 그러나 필요할 때마다 데이터베이스를 다시 만들 수 있도록 SQL 데이터베이스 작성 스크립트를 작성하는 것이 훨씬 좋습니다. 기존 데이터베이스의 엔터프라이즈 관리자에서 데이터베이스 작성 스크립트를 생성할 수 있지만 그 결과 코드는 대개 거추장스럽습니다. 그림 3의 스크립트는 dbProseware라는 데이터베이스를 만들고 tblEmployees라는 이름의 테이블과 usp_empStatusCode라고 하는 사용자 정의 저장 프로시저를 만듭니다.


그림 3 Database Creation Script

같은 이름의 데이터베이스가 존재하는지 않는지 확인한 다음 dbProseware라는 데이터베이스와 tblEmployees라는 테이블을 만듭니다. 그런 다음 몇몇 기본 기능 테스트를 수행할 수 있도록 일부 개발자 데이터(개발 과정에서 사용된 데이터)를 삽입합니다.

스크립트 마지막 부분에서는 테스트할 usp_empStatusCode 저장 프로시저를 만듭니다. 이 저장 프로시저는 직원 ID를 받아들여 ID가 유효하지 않을 경우 0을 반환하고, 퇴사 일자가 있을 경우(현재 직원이 아닌 경우) 1, 2000년 1월 1일 이전에 고용된 경우 2, 2000년 1월 1일 이후 고용된 경우 3을 반환합니다. 이 저장 프로시저는 좋은 코딩 기법의 본보기는 아니며 코드를 간단하게 줄였다는 것을 알려드립니다.

프로시저를 통한 프로그래밍 경력자일 경우 일반 프로그래밍 언어의 함수처럼 SQL 저장 프로시저를 생각하는 경향이 있습니다. 그러나 SQL 저장 프로시저는 대부분 테이블이나 다른 데이터베이스 개체에 대한 논리적 종속성이 있기 때문에 일반 함수와 크게 다릅니다. 이 예제에서 usp_empStatusCode 저장 프로시저에서 반환된 값은 tblEmployees 테이블의 데이터에 전적으로 종속적이라는 점을 알아두십시오. 그러므로 SQL 저장 프로시저의 테스트는 일반 함수 테스트와 다소 다릅니다.

테스트 자동화 구조

여러 방법으로 SQL 저장 프로시저에 대한 테스트 자동화를 구조화할 수 있습니다. 여기서 설명하는 특정 구조는 여러 대규모 프로젝트에서 성공적으로 사용되었으나 중요한 대체 방법이 있습니다. 이에 대해서는 앞으로 설명하도록 하겠습니다. 이 테스트 자동화 구조는 세 개의 SQL 스크립트, 즉 makeDbProseware.sql, prepTestAuto.sql, runTestAuto.sql을 중심으로 만들어집니다. 실제로 그림 2에 표시된 테스트 실행 결과를 생성한 BAT 파일은 입력으로서 이러한 스크립트가 지정된 세 개의 osql.exe 호출에 불과합니다. osql 유틸리티를 사용하여 Windows 명령 프롬프트에서 T-SQL 문, 시스템 프로시저, 스크립트 파일을 입력할 수 있습니다.

이전 단원에서는 makeDbProseware.sql을 살펴보았습니다. 이 스크립트는 테스트할 저장 프로시저 usp_empStatusCode와 관련 테이블 tblEmployees을 포함하는 dbProseware를 만듭니다.

prepTestAuto.sql 스크립트는 테스트 자동화에 필요한 주요 두 구성 요소를 dbProseware 데이터베이스에 추가합니다. 첫 번째 추가 구성 요소는 테스트할 풍부한 직원 데이터로 테이블 tblEmployees를 채워 알려진 초기 상태를 만드는 tap_ResetState라는 이름의 테스트 자동화 유틸리티 저장 프로시저입니다. 두 번째 추가 구성 요소는 테스트 사례 ID, 테스트 사례 입력, 테스트 사례 예상 결과로 구성된 tblTestCases라는 이름의 테스트 사례 데이터 테이블입니다. tblTestCases의 각 행은 정확히 하나의 테스트 사례에 해당합니다.

runTestAuto.sql 스크립트가 대부분의 작업을 수행합니다. 이 스크립트가 각 데이터 행에 대해 테스트할 저장 프로시저를 호출하고 커서를 사용하여 한 번에 한 행씩 테스트 사례 테이블 tblTestCases를 반복합니다. 반환 값이 캡처되고 예상 결과와 비교되어 합격 또는 불합격 결과를 결정합니다. 테스트 사례 결과가 나온 후 그 결과가 외부 결과 파일에 기록됩니다.


그림 4 테스트 시스템

그림 4의 도표는 테스트 자동화 시스템의 여러 요소가 작동하는 방식을 설명합니다.

테스트 자동화 준비

그림 5의 테스트 데이터베이스 준비 스크립트 prepTestAuto.sql은 데이터베이스 상태를 재설정하는 저장 프로시저를 추가하여 개발 데이터베이스를 증가시킵니다. 이것은 다음 두 가지 이유 때문에 필수적입니다. 첫째, 개발자 데이터가 저장 프로시저를 완벽하게 테스트하기에 충분히 많지 않기 때문입니다. 그래서 더 많은 데이터를 추가해야 합니다. 둘째, 저장 프로시저는 종종 데이터베이스를 변경합니다. 따라서 알려진 초기 상태로 데이터베이스를 재설정해야 합니다. 예를 들어 저장 프로시저는 테이블에 데이터를 추가하거나 테이블에서 행을 삭제할 수 있습니다. 이 예제의 usp_empStatusCode 저장 프로시저가 데이터베이스 상태를 변경하지 않는다고 해도 나중에 후회하는 것보다 안전한 것이 낫습니다.

그림 5에서 보는 바와 같이 준비 스크립트는 tblEmployees 테이블의 모든 데이터를 삭제한 다음 많은 테스트 직원으로 테이블을 채우는 tap_ResetState라는 저장 프로시저를 만듭니다. 그런 다음 준비 스크립트는 테스트 사례 데이터 테이블을 만들고 테이블을 채웁니다. 다양한 상태 데이터와 테스트 사례 데이터를 준비 스크립트에 하드코딩했습니다. 더 유연한 방법은 대량 복사 프로그램(BCP) 또는 데이터 변환 서비스(DTS) 프로그램을 사용하여 외부 파일에서 이러한 데이터 집합을 가져오는 것입니다. 또한 저장 프로시저에서 필요한 것보다 큰 테스트 입력을 허용하도록 empID 테스트 사례 필드를 char(3)가 아닌 varchar(10)으로 선언했습니다.


그림 5 Test Automation Preparation Script

테스트 자동화 실행

usp_empStatsCode 저장 프로시저의 자동 테스트를 실제로 수행하는 코드를 검토해 봅시다. 그림 6의 코드는 핵심 테스트 자동화입니다.


그림 6 Test Automation Preparation Script

테스트 데이터베이스가 있는지 확인한 후 다음과 같이 testCaseCursor라는 SQL 커서를 선언합니다.

declare testCaseCursor cursor fast_forward
for select caseID, empID, expectedResult
from tblTestCases
order by caseID

이 커서는 tblTestCases 테이블 데이터를 가리키고 한 번에 한 행씩 테이블 데이터를 반복할 수 있게 합니다. 보통 변수처럼 커서 변수 앞에 @ 문자가 오지 않는 것을 주의하여 보십시오. SQL Server 2000은 다양한 종류의 커서를 지원합니다. 여기에서 테스트 사례 데이터 간을 건너뛸 필요가 없고 고속 전달 커서가 일반적으로 성능이 가장 우수하기 때문에 고속 전달 커서를 사용합니다.

그런 다음 로컬 변수를 선언합니다. 즉 다음에서 보는 바와 같이 testCaseCursor 커서가 tblTestCase 데이터에서 추출한 테스트 사례 데이터를 보유하는 변수 세 개, usp_empStatusCode 저장 프로시저가 호출될 때 반환되는 실제 결과를 저장하는 변수 하나, 외부 텍스트 파일로 직접 테스트 결과를 쓰는 데 사용할 수 있는 변수 세 개를 선언합니다.

declare @caseID char(4), @empID varchar(10),
@expectedResult int,
@actualResult int
declare @fsoHandle int, @fileID int
declare @resultLine varchar(80)

테스트 결과를 보유하는 테이블을 만들 수 있지만 그 대신 결과를 테이블에 쓴 다음 결과 데이터를 외부 파일로 내보냅니다. SQL Server는 ActiveX 기술을 사용하여 외부 파일로 직접 결과를 쓰는 기능을 지원합니다. @fsoHandle 변수를 사용하여 파일 시스템 개체의 핸들을 저장합니다. 그런 다음 @fileID 변수가 파일 시스템 개체에서 획득한 결과 파일에 대한 정수 ID를 저장합니다. @resultLine 변수를 사용하여 테스트 사례 하나의 결과를 나타내는 텍스트 줄을 저장하고 외부 결과 파일에 그 텍스트 줄을 씁니다.

외부 결과 텍스트 파일을 준비하는 것으로 테스트 실행을 시작합니다.

exec sp_OACreate 'Scripting.FileSystemObject', @fsoHandle out
-- create object
exec sp_OAMethod @fsoHandle, 'OpenTextFile', @fileID out,
'C:\TestStoredProcs\results.txt', 8, 1

첫 번째 문은 FileSystemObject 개체의 인스턴스를 만들고 그에 대한 참조를 @fsoHandle 변수에 저장합니다. 다음 줄은 추가할 결과 파일을 엽니다. 여기에서는 단순하도록 결과 파일의 이름과 위치를 하드코딩하였으나 이 정보를 매개 변수화할 것입니다. 좀 이상하게 보이는 8과 1 매개 변수는 OpenTextFile을 실행하여 추가할 결과 파일을 열거나 파일이 없을 경우 파일을 만듭니다.

'WriteLine' 매개 변수와 함께 sp_OAMethod 저장 프로시저를 사용하여 결과 파일에 몇몇 예비 헤더 정보를 쓴 후에, 테스트 사례 데이터를 반복할 커서를 만들고 데이터베이스를 알려진 상태로 초기화합니다. Open 문은 변수 이 변수가 속하는 메모리 내부 결과 집합(여기에서 tblTestCases)를 가리키도록 testCaseCursor를 초기화합니다.

open testCaseCursor
exec tap_ResetState
fetch next
from testCaseCursor
into @caseID, @empID, @expectedResult

전부 정확하지는 않더라도 데이터 첫 행 전에 testCaseCursor가 tblTestCases의 맨 처음 데이터를 가리킨다고 가정하는 것이 유용합니다. Fetch next 문은 다음 데이터 행(여기에서는 첫 행)을 가져오고 위치를 기준으로 지정된 로컬 변수에 가져온 값을 저장합니다.

대량의 자동화가 while 루프를 통해 수행됩니다.

while @@fetch_status = 0
begin
-- create known state (call tap_ResetState)
-- execute stored procedure under test
-- determine pass or fail
-- write result
fetch next -- next test case
from testCaseCursor
into @caseID, @empID, @expectedResult
end

@@fetch_status는 현재 연결에서 모든 커서에서 마지막으로 시도된 반입 결과를 보유하는 전역 변수입니다. 값 0은 마지막 반입이 성공했음을 나타냅니다(값 -1 또는 -2는 실패를 나타냄). 그러므로 @@fetch_status를 사용하여 커서가 가리키는 전체 결과 집합을 반복했을 때를 확인할 수 있습니다. 루프 내에서의 처리는 실제로 다음과 같이 매우 단순합니다.

exec tap_ResetState
exec @actualResult = usp_empStatusCode @empID

도우미 저장 프로시저 tap_ResetState를 호출하여 알려진 상태를 만듭니다. 특히 테이블 tblEmployees의 모든 행을 삭제한 다음 많은 테스트 데이터로 테이블을 다시 채웁니다. 이제 테스트할 저장 프로시저를 실행하여 커서에 의해 반입된 테스트 사례 입력 데이터를 전달한 다음 실제 반환 값을 캡처할 수 있습니다. tap_ResetState 저장 프로시저의 호출 위치가 매우 중요합니다. 모든 테스트 전에, 반복적으로 호출되도록 While 루프 내에서 tap_ResetState을 호출합니다. 이 예제에서 테스트할 저장 프로시저 usp_empStatusCode는 dbProseware 데이터베이스의 상태를 변경하지 않으며 따라서 while 루프 외부에서 한 번만 호출함으로써 성능을 향상시킬 수 있었습니다. 그러나 경험상 대부분의 경우 저장 프로시저가 시스템 상태에 대해 어떠한 작업을 수행하는지에 관하여 아무런 가정을 하지 않고 주요 자동화 코드의 while 루프 내에서 상태를 항상 다시 만드는 것이 좋습니다. 각각의 모든 테스트 호출 전에 상태를 다시 만들 경우 발생하는 방문 횟수(performance hit)는 테스트 유효성 검사의 상당한 향상의 대가로 지불해야 하는 대체적으로 작은 비용입니다.

테스트 사례의 합격 또는 실패를 결정하기 위해 테스트하는 저장 프로시저를 호출하여 얻은 실제 값과 테스트 사례 테이블에서 반입된 예상 값과 비교합니다. 사실 다음 코드는 간단하게 작성할 수 있었습니다.

if (@actualResult = @expectedResult)
set @resultLine = @caseID + ' Pass '
else
set @resultLine = @caseID + ' FAIL '

그런 다음 다음과 같이 결과를 결과 파일에 썼습니다.

exec sp_OAMethod @fileID, 'WriteLine', null, @resultLine

물론 여러분도 이 예제에서와 같이 결과 파일에 더 자세한 설명 정보를 표시하기를 원할 것입니다. runTestAuto.sql 스크립트는 실행할 때마다 메모리가 누수되지 않도록 할당한 자원을 정리한 후 종료됩니다.

자동화 기능의 자동화

데이터베이스 작성 스크립트, 자동화 준비 스크립트, 자동 실행 스크립트가 작성되었으므로 이제 쿼리 분석기 프로그램에서 차례로 이 스크립트를 실행할 수 있습니다. 아니면, 세 스크립트를 실행하는 osql.exe 명령을 실행하는 간단한 BAT 파일을 작성하는 방법도 있습니다.

@echo off
rem runTests.bat

osql -S(local) -E -i makeDbProseware.sql -n > nul
osql -S(local) -E -i prepTestAuto.sql -n > nul
osql -S(local) -E -i runTestAuto.sql -n > nul

echo.
echo Test run complete
echo.

-E 스위치를 지정하면 osql에서 현재 Windows 로그인을 사용하여 Windows 인증 연결을 요청합니다. 이 스위치는 SQL Server 인스턴스에 연결하는 가장 안전한 옵션입니다. -n 스위치를 지정하면 줄 번호가 출력되지 않습니다. 또한 이 예제에서는 > nul을 지정하여 출력을 재지정함으로써 기타 메시지를 표시하지 않고 출력 표시를 더 깔끔하게 만들었습니다. 물론 생산 환경에서는 얻을 수 있는 모든 정보를 표시해야 할 것입니다. 앞에서 설명한 것과 같은 BAT 파일을 만든 후에 엔터프라이즈 관리자를 사용하여 편하게 설정할 수 있는 SQL Server 에이전트의 작업 기능을 사용하거나 Windows Scheduler를 사용하여 자동으로 테스트 스크립트를 시작하도록 쉽게 예약할 수 있습니다.

결론

SQL 저장 프로시저에 대한 테스트 자동화를 작성할 때 직면하는 기술적 문제 외에 빌드 관리 문제를 해결해야 합니다. 소규모 생산 환경에서는 제품 코드 베이스의 변경이나 새 빌드의 코드 베이스가 삭제된 경우와 같은 변경을 인지할 수 있을 것입니다. 그러나 대규모 생산 환경에서는 이러한 변경을 관리하기가 매우 어려울 수 있습니다. 예전에 대규모 제품에 대한 테스트 자동화 작업을 할 때 빌드 트리의 잘못된 분기를 3일 동안 테스트하여 변경을 찾았던 기억이 아직도 생생합니다! 테스트 팀 관리는 이 칼럼의 범위를 벗어나지만 그것은 테스트 자동화 구조를 개발할 때 명심해야 할 중요한 사항입니다.

이 칼럼에서 설명한 테스트는 SQL 구성 요소가 포함된 응용 프로그램을 테스트하는 여러 방법 중 하나일 뿐이라는 사실을 기억하십시오. 철저한 테스트를 위해서는 시스템을 구성하는 모든 계층을 테스트해야 합니다. 사용자 인터페이스, 응용 프로그램-SQL 인터페이스, 시스템에 있을 수 있는 기타 모든 인터페이스(HTTP, 소켓, 명명된 파이프, 서비스 등)를 통해 응용 프로그램을 테스트해야 합니다. 또한 여기에서 설명한 기법은 SQL 저장 프로시저 기능만을 테스트합니다. 스트레스 테스트, 성능 테스트, 기타 테스트도 고려해야 합니다. 이 칼럼에서 제시된 기본 구조를 사용하여 이러한 여러 분야의 테스트를 수행해야 할 것입니다.