public void TestAddNotification()
{
NotificationList notificationList = new NotificationList();
int expectedIllegalCount = 0;
Assert.AreEqual(expectedIllegalCount, notificationList.GetIllegalList().Count);
expectedIllegalCount = 1;
notificationList.Add(NotificationLevel.Illegal, IllegalType.NotDeclaredTableName, "hello", "hello");
Assert.AreEqual(expectedIllegalCount, notificationList.GetIllegalList().Count);
notificationList.Add(NotificationLevel.Warning, IllegalType.NotDeclaredTableName, "hello", "hello");
Assert.AreEqual(expectedIllegalCount, notificationList.GetIllegalList().Count);
}
public void TestConstructor()
{
NotificationList actualList = new NotificationList();
int expectedListCount = 0;
Assert.AreEqual(expectedListCount, actualList.GetIllegalList().Count);
}
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付き)したが、テーブル名を宣言しなかった時の違反要素が違う"
);
}