private TableNameDeclareCheckVisitor visitTSql(string tsql)
{
TSqlParser parser = new TSql100Parser(false);
IList <ParseError> errors;
TextReader reader = new StringReader(tsql);
TSqlFragment parsed = parser.Parse(reader, out errors);
if (errors.Count != 0)
{
string errorMessage = "";
foreach (ParseError error in errors)
{
errorMessage = errorMessage + "\n" + error.Line + " : " + error.Message;
}
throw new Exception("パース失敗\n" + errorMessage);
}
TableNameDeclareCheckVisitor visitor = new TableNameDeclareCheckVisitor();
parsed.Accept(visitor);
return(visitor);
}
public void TestSelectFromOtherSelectTableSimple()
{
string tsql = @"
SELECT
book.id
FROM
book
INNER JOIN
(
SELECT
id,
*
FROM
shelf
) AS a_shelf ON book.id_shelf = a_shelf.id
";
TableNameDeclareCheckVisitor visitor = visitTSql(tsql);
NotificationList list = visitor.getNotificationList();
int expectedNotificationCount = 0;
int actualCount = list.GetAll().Count;
Assert.AreEqual(
expectedNotificationCount,
actualCount,
"SELECTを使用して得たテーブルをJOINしたら何か規約違反を見つけてしまった"
);
}
public void TestSelectJoinWithScalarFunctionNotDeclaringSchemaName()
{
string tsql = @"
SELECT
book.id_author
FROM
book
INNER JOIN shelf ON book.id_shelf = shelf.id
LEFT OUTER JOIN home ON shelf.id_home = home.id
WHERE
book.id_authors = f_getMostLikeAuthorId()
";
TableNameDeclareCheckVisitor visitor = visitTSql(tsql);
NotificationList list = visitor.getNotificationList();
int expectedNotificationCount = 0;
int actualCount = list.GetAll().Count;
Assert.AreEqual(
expectedNotificationCount,
actualCount,
"スカラ関数の使用(スキーマ名無し)で何か規約違反を見つけてしまった"
);
}
public void TestSelectSimpleWithHaving()
{
string tsql = @"
SELECT
id_author
FROM
book
GROUP BY
id_author
HAVING
COUNT(*) > 3
";
TableNameDeclareCheckVisitor visitor = visitTSql(tsql);
NotificationList list = visitor.getNotificationList();
int expectedNotificationCount = 0;
int actualCount = list.GetAll().Count;
Assert.AreEqual(
expectedNotificationCount,
actualCount,
"テーブル一つの単純なSELECT文(HAVING付き)で何かを見つけてしまった"
);
}
public void TestInsertSimple()
{
string tsql = @"
INSERT INTO book
(
id_shelf,
name
)
VALUES
(
1,
'Software Systems Architecture'
)
";
TableNameDeclareCheckVisitor visitor = visitTSql(tsql);
NotificationList list = visitor.getNotificationList();
int expectedNotificationCount = 0;
int actualCount = list.GetAll().Count;
Assert.AreEqual(
expectedNotificationCount,
actualCount,
"テーブル一つの単純なINSERT文で何かを見つけてしまった"
);
}
public void TestSelectFromOtherSelectJoinTableWithGroup()
{
string tsql = @"
SELECT
book.id
FROM
book
INNER JOIN
(
SELECT
shelf.id,
COUNT(shelf.id_home)
FROM
shelf
INNER JOIN home ON shelf.id_home = home.id
GROUP BY
home.id_country,
id_home
) AS a_shelf ON book.id_shelf = a_shelf.id
";
TableNameDeclareCheckVisitor visitor = visitTSql(tsql);
NotificationList list = visitor.getNotificationList();
int expectedNotificationCount = 1;
int actual = list.GetAll().Count;
Assert.AreEqual(
expectedNotificationCount,
actual,
"SELECT(GROUP有り)を使用して得たテーブルをJOINしたら規約に抵触する可能性のある数を勘違い"
);
List <Notification> illegalList = list.GetIllegalList();
expectedNotificationCount = 1;
actual = illegalList.Count;
Assert.AreEqual(
expectedNotificationCount,
actual,
"SELECT(GROUP有り)を使用して得たテーブルをJOINしたら規約違反の発見数を勘違い"
);
Notification actualIllegal = illegalList[0];
Notification expectedIllegal = new Notification(
NotificationLevel.Illegal,
IllegalType.NotDeclaredTableName,
"SELECT(GROUP有り)を使用して得たテーブルをJOINするときにテーブルを宣言してない",
"id_home"
);
Assert.AreEqual(
expectedIllegal,
actualIllegal,
"SELECT(GROUP有り)を使用して得たテーブルをJOINしたが、テーブル名を宣言しなかった時の違反要素が違う"
);
}
public void TestSelectWithUnion()
{
string tsql = @"
SELECT
book.id,
shelf.id_shlef
FROM
book
INNER JOIN shelf ON book.id_shelf = shelf.id
UNION ALL
SELECT
id_book,
book.id_shelf
FROM
book
INNER JOIN shelf ON book.id_shelf = shelf.id
";
TableNameDeclareCheckVisitor visitor = visitTSql(tsql);
NotificationList list = visitor.getNotificationList();
int expectedNotificationCount = 1;
int actual = list.GetAll().Count;
Assert.AreEqual(
expectedNotificationCount,
actual,
"UNIONを行ったら規約に抵触する可能性のある数を勘違い"
);
List <Notification> illegalList = list.GetIllegalList();
expectedNotificationCount = 1;
actual = illegalList.Count;
Assert.AreEqual(
expectedNotificationCount,
actual,
"UNIONを行ったら規約違反の発見数を勘違い"
);
Notification actualIllegal = illegalList[0];
Notification expectedIllegal = new Notification(
NotificationLevel.Illegal,
IllegalType.NotDeclaredTableName,
"UNIONするときにテーブルを宣言してない",
"id_book"
);
Assert.AreEqual(
expectedIllegal,
actualIllegal,
"UNIONしたが、テーブル名を宣言しなかった時の違反要素が違う"
);
}
public void TestSelectJoinWithHaving()
{
string tsql = @"
SELECT
MIN(book.count)
FROM
book
INNER JOIN shelf ON book.id_shelf = shelf.id
WHERE
shelf.id = 2
GROUP BY
shelf.count
HAVING
COUNT(shelf.id) < 2 AND
COUNT(id_author) > 5
";
TableNameDeclareCheckVisitor visitor = visitTSql(tsql);
NotificationList list = visitor.getNotificationList();
int expectedNotificationCount = 1;
int actual = list.GetAll().Count;
Assert.AreEqual(
expectedNotificationCount,
actual,
"HAVINGで規約に抵触する可能性のある数を勘違い"
);
List <Notification> illegalList = list.GetIllegalList();
expectedNotificationCount = 1;
actual = illegalList.Count;
Assert.AreEqual(
expectedNotificationCount,
actual,
"HAVINGで規約違反の発見数を勘違い"
);
Notification actualIllegal = illegalList[0];
Notification expectedIllegal = new Notification(
NotificationLevel.Illegal,
IllegalType.NotDeclaredTableName,
"HAVINGでJOINがあるのにテーブルを宣言してない",
"id_author"
);
Assert.AreEqual(
expectedIllegal,
actualIllegal,
"SELECT文内でJOINがあるのにHAVINGでテーブル名を宣言しない規約の違反で要素が違う"
);
}
public void TestSelectJoinWithOrder()
{
string tsql = @"
SELECT
book.id,
*
FROM
book
INNER JOIN shelf ON book.id_shelf = shelf.id
ORDER BY
book.name,
id_book
";
TableNameDeclareCheckVisitor visitor = visitTSql(tsql);
NotificationList list = visitor.getNotificationList();
int expectedNotificationCount = 1;
int actual = list.GetAll().Count;
Assert.AreEqual(
expectedNotificationCount,
actual,
"ORDERで規約に抵触する可能性のある数を勘違い"
);
List <Notification> illegalList = list.GetIllegalList();
expectedNotificationCount = 1;
actual = illegalList.Count;
Assert.AreEqual(
expectedNotificationCount,
actual,
"ORDERで規約違反の発見数を勘違い"
);
Notification actualIllegal = illegalList[0];
Notification expectedIllegal = new Notification(
NotificationLevel.Illegal,
IllegalType.NotDeclaredTableName,
"ORDERでJOINがあるのにテーブルを宣言してない",
"id_book"
);
Assert.AreEqual(
expectedIllegal,
actualIllegal,
"SELECT文内でJOINがあるのにORDERでテーブル名を宣言しない規約の違反で要素が違う"
);
}
public void TestUpdateFromJoinWithWhere()
{
string tsql = @"
UPDATE book
SET
id_shelf = shelf.id
FROM
book
INNER JOIN shelf ON shelf.id = book.id_shelf
WHERE
book.id = 1 AND
id_book < 2
";
TableNameDeclareCheckVisitor visitor = visitTSql(tsql);
NotificationList list = visitor.getNotificationList();
int expectedNotificationCount = 1;
int actual = list.GetAll().Count;
Assert.AreEqual(
expectedNotificationCount,
actual,
"UPDATE文でJOIN(WHERE付き)を行ったら規約に抵触する可能性のある数を勘違い"
);
List <Notification> illegalList = list.GetIllegalList();
expectedNotificationCount = 1;
actual = illegalList.Count;
Assert.AreEqual(
expectedNotificationCount,
actual,
"UPDATE文でJOIN(WHERE付き)を行ったら規約違反の発見数を勘違い"
);
Notification actualIllegal = illegalList[0];
Notification expectedIllegal = new Notification(
NotificationLevel.Illegal,
IllegalType.NotDeclaredTableName,
"UPDATE文でJOIN(WHERE付き)するときにテーブルを宣言してない",
"id_book"
);
Assert.AreEqual(
expectedIllegal,
actualIllegal,
"UPDATE文でJOIN(WHERE付き)したが、テーブル名を宣言しなかった時の違反要素が違う"
);
}
public void TestDeleteSimple()
{
string tsql = @"
DELETE book
";
TableNameDeclareCheckVisitor visitor = visitTSql(tsql);
NotificationList list = visitor.getNotificationList();
int expectedNotificationCount = 0;
int actualCount = list.GetAll().Count;
Assert.AreEqual(
expectedNotificationCount,
actualCount,
"テーブル一つの単純なDELETE文で何かを見つけてしまった"
);
}
public void TestUpdateSimpleWithWhere()
{
string tsql = @"
UPDATE book
SET
id_shelf = 1
WHERE
id_author = 2
";
TableNameDeclareCheckVisitor visitor = visitTSql(tsql);
NotificationList list = visitor.getNotificationList();
int expectedNotificationCount = 0;
int actualCount = list.GetAll().Count;
Assert.AreEqual(
expectedNotificationCount,
actualCount,
"テーブル一つの単純なUPDATE文(WHERE付き)で何かを見つけてしまった"
);
}
public void TestSelectSimpleWithAggregateFunction()
{
string tsql = @"
SELECT
COUNT(*)
FROM
book
";
TableNameDeclareCheckVisitor visitor = visitTSql(tsql);
NotificationList list = visitor.getNotificationList();
int expectedNotificationCount = 0;
int actualCount = list.GetAll().Count;
Assert.AreEqual(
expectedNotificationCount,
actualCount,
"テーブル一つの単純なSELECT文(集計関数付き)で何かを見つけてしまった"
);
}
public void TestSelectSimpleWithOrder()
{
string tsql = @"
SELECT
*
FROM
book
ORDER BY
id_author
";
TableNameDeclareCheckVisitor visitor = visitTSql(tsql);
NotificationList list = visitor.getNotificationList();
int expectedNotificationCount = 0;
int actualCount = list.GetAll().Count;
Assert.AreEqual(
expectedNotificationCount,
actualCount,
"テーブル一つの単純なSELECT文(ORDER付き)で何かを見つけてしまった"
);
}
public void TestSelectJoinWithTableValuedFunctionNotDeclaringSchemaName()
{
string tsql = @"
SELECT
authors.id
FROM
book
INNER JOIN shelf ON book.id_shelf = shelf.id
LEFT OUTER JOIN f_getAuthros AS authors ON book.id_author = authors.id
";
TableNameDeclareCheckVisitor visitor = visitTSql(tsql);
NotificationList list = visitor.getNotificationList();
int expectedNotificationCount = 0;
int actualCount = list.GetAll().Count;
Assert.AreEqual(
expectedNotificationCount,
actualCount,
"テーブル値関数(スキーマ名無し)の使用で何か規約違反を見つけてしまった"
);
}
public void TestSelectJoinWithScalarFunction()
{
string tsql = @"
SELECT
book.id_author
FROM
book
WHERE
book.id_authors = dbo.f_getMostLikeAuthorId()
";
TableNameDeclareCheckVisitor visitor = visitTSql(tsql);
NotificationList list = visitor.getNotificationList();
int expectedNotificationCount = 0;
int actualCount = list.GetAll().Count;
Assert.AreEqual(
expectedNotificationCount,
actualCount,
"スカラ関数の使用で何か規約違反を見つけてしまった"
);
}
