Dbfit--Comparison of Database In SQL Server

Posted by: bart30508 | in Dbfit | 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
Currently unrated
 or 

Subscribe

* indicates required

Recent Posts

Archive

2022
2021

Categories

Apache 1

Data Science 2

Dbfit 1

Design Pattern 1

Devops 3

DigitalOcean 1

Django 1

English 3

Excel 5

Flask 3

Git 1

HackMD 1

Heroku 1

Html/Css 1

Linux 4

Machine Learning 2

Manufacture 1

Mezzanine 18

Oracle 1

Postgresql 7

PowerBI 4

Powershell 3

Python 21

SEO 2

SQL Server 51

SQLite 1

Windows 1

database 8

work-experience 1

其他 1

自我成長 1

資料工程 1

Tags

SEO(1) Github(2) Title Tag(2) ML(1) 李宏毅(1) SQL Server(18) Tempdb(1) SSMS(1) Windows(1) 自我成長(2) Excel(1) python Flask(1) python(5) Flask(2)

Authors

bart30508 (145)

Feeds

RSS / Atom

Dbfit--Comparison of Database In SQL Server

© COPYRIGHT 2011-2022. Max的文藝復興. ALL RIGHT RESERVED.