Posted by: Max Chen | in Dbfit | 1 year, 11 months ago |

Dbfit--Comparison of Database In SQL Server

使用Dbfit做Database Comparision一陣子了, 比起以前在直接下Query做, 的確加上視覺化的輔助更好管理了.

不過由於需要比較很多不同台機器, 如果照以前的做法的話, 每一次都需要改很多地方.

幸好, Fitnesse提供我們Varible的功能, 所以我就將原本需要改的地方, 都替換成Varible, 看起來是不是簡潔多了?

也更好管理了!

註:

(1). Fitnesse Varible使用方法 !define y {y-value} variable defined: y=y-value !define x {The value of y is ${y}} variable defined: x=The value of y is ${y}

${x} is expressed as: The value of y is y-value

!define y {new-y-value} variable defined: y=new-y-value

${x} is expressed as: The value of y is new-y-value

(2). Stored Query中, 需要把原本包住Script的!- -!拿掉,使Varible得以被讀取. Note that you have to use !- -! around your query, otherwise FitNesse will expand the concatenation operator to table cells. The main drawback of this manner is that you cannot use ordinary FitNesse variables (${varname}) in the query.

(3). 以下SQL Script不一樣, 但不影響比較

舊版

```
<test page>
<test page>

!contents -R2 -g -p -f -h
!define COMMAND_PATTERN {%m -r fitnesse.fitserver.FitServer,"fitsharp\fit.dll" %p}
!define TEST_RUNNER {fitsharp\Runner.exe} 
!define PATH_SEPARATOR {;} 
!path FitSharp\dbfit.sqlserver.dll
!|dbfit.SqlServer2000Test|
!|Connect|SQLServerHost1|ITS|ITS|ITS|

!| Store Query |!- 
 SELECT T.NAME collate Chinese_Taiwan_Stroke_CI_AS AS TABLE_NAME,I.NAME collate Chinese_Taiwan_Stroke_CI_AS AS INDEX_NAME,C.NAME collate Chinese_Taiwan_Stroke_CI_AS AS COLUMN_NAME, 
                    I.type INDEX_TYPE, I.type_desc collate Chinese_Taiwan_Stroke_CI_AS AS INDEX_TYPE_DESC, I.is_unique IS_UNIQUE,
                    C.is_identity IS_IDENTITY, I.is_primary_key IS_PRIMARY_KEY, IC.is_descending_key IS_DESCENDINGKEY, IC.is_included_column IS_INCLUDED_COLUMN
                    FROM [ITS].SYS.TABLES T
                    INNER JOIN [ITS].SYS.SCHEMAS S ON T.SCHEMA_ID = S.SCHEMA_ID
                    INNER JOIN [ITS].SYS.INDEXES I ON I.OBJECT_ID = T.OBJECT_ID
                    INNER JOIN [ITS].SYS.INDEX_COLUMNS IC ON IC.OBJECT_ID = T.OBJECT_ID
                    INNER JOIN [ITS].SYS.COLUMNS C ON C.OBJECT_ID = T.OBJECT_ID AND IC.INDEX_ID = I.INDEX_ID AND IC.COLUMN_ID = C.COLUMN_ID
                    WHERE T.[name] NOT LIKE '_Back%' AND T.[name] NOT LIKE '_Temp%'

-!|2006Output|

!|dbfit.SqlServer2000Test|
!|Connect|SQLServerHost2|ITS|ITS|ITS|

!|Store Query|!-

 SELECT T.NAME collate Chinese_Taiwan_Stroke_CI_AS AS TABLE_NAME,I.NAME collate Chinese_Taiwan_Stroke_CI_AS AS INDEX_NAME,C.NAME collate Chinese_Taiwan_Stroke_CI_AS AS COLUMN_NAME, 
                    I.type INDEX_TYPE, I.type_desc collate Chinese_Taiwan_Stroke_CI_AS AS INDEX_TYPE_DESC, I.is_unique IS_UNIQUE,
                    C.is_identity IS_IDENTITY, I.is_primary_key IS_PRIMARY_KEY, IC.is_descending_key IS_DESCENDINGKEY, IC.is_included_column IS_INCLUDED_COLUMN
                    FROM [ITS].SYS.TABLES T
                    INNER JOIN [ITS].SYS.SCHEMAS S ON T.SCHEMA_ID = S.SCHEMA_ID
                    INNER JOIN [ITS].SYS.INDEXES I ON I.OBJECT_ID = T.OBJECT_ID
                    INNER JOIN [ITS].SYS.INDEX_COLUMNS IC ON IC.OBJECT_ID = T.OBJECT_ID
                    INNER JOIN [ITS].SYS.COLUMNS C ON C.OBJECT_ID = T.OBJECT_ID AND IC.INDEX_ID = I.INDEX_ID AND IC.COLUMN_ID = C.COLUMN_ID
                    WHERE T.[name] NOT LIKE '_Back%' AND T.[name] NOT LIKE '_Temp%'

-!|2015Output|


!|compare stored queries|2006Output|2015Output|
|TABLE_NAME|INDEX_NAME|COLUMN_NAME|INDEX_TYPE|INDEX_TYPE_DESC|IS_UNIQUE|IS_IDENTITY|IS_PRIMARY_KEY|IS_DESCENDINGKEY|IS_INCLUDED_COLUMN|

```

新版

```
<test page>
!contents -R2 -g -p -f -h
!define COMMAND_PATTERN {%m -r fitnesse.fitserver.FitServer,"fitsharp\fit.dll" %p}
!define TEST_RUNNER {fitsharp\Runner.exe} 
!define PATH_SEPARATOR {;} 
!path FitSharp\dbfit.sqlserver.dll

!define Server1 {SQLServerHost1}
!define Server2 {SQLServerHost2}
!define Database {ITS}
!define Account {ITS}
!define Password {ITS}
!define Query {SELECT t.[name] collate Chinese_Taiwan_Stroke_CI_AS as tname,c.[name] collate Chinese_Taiwan_Stroke_CI_AS as cname, c.system_type_id,c.user_type_id,
                    c.max_length,c.[precision],c.scale,c.collation_name collate Chinese_Taiwan_Stroke_CI_AS as collation_name,c.is_nullable
                    FROM [${Database}].sys.tables t
                    INNER JOIN [${Database}].sys.columns c ON t.[object_id]=c.[object_id]
                    WHERE t.[name] NOT LIKE '_Back%' AND t.[name] NOT LIKE '_Temp%'}

!|dbfit.SqlServer2000Test|
!|Connect|${Server1}|${Database}|${Account}|${Password}|

!| Store Query|${Query}|${Server1}Output|

!|dbfit.SqlServer2000Test|
!|Connect|${Server2}|${Database}|${Account}|${Password}|

!|Store Query|${Query}|${Server2}Output|


!|compare stored queries|${Server1}Output|${Server2}Output|
|tname|cname|user_type_id|max_length|

```

參考來源：

https://stackoverflow.com/questions/19518366/fitnesse-and-dbfit-how-to-escape-colons-in-sql-queries http://fitnesse.org/FitNesse.FullReferenceGuide.UserGuide.FitNesseWiki.MarkupLanguageReference.MarkupVariables

tags: `Dbfit`

Authors

Max Chen (159)

Feeds

RSS / Atom