public void BuildBooleanSqlStatement_TwoValidInputs_ValidSql()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
var sqlOutput =
"SELECT \"id\", \"name\", \"desc\"\n" +
"FROM \"table\"\n" +
"WHERE " +
"(((CONTAINS(\"name\", @field1)) AND (CONTAINS(\"name\", @field2)))) OR " +
"(((CONTAINS(\"desc\", @field3)) AND (CONTAINS(\"desc\", @field4))));";
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlStatement(
"foo AND bar", "table",
new List <string>()
{
"name", "desc"
},
new List <string>()
{
"id", "name", "desc"
});
// Assert
Assert.AreEqual(sqlOutput, parameterizedSql.Sql);
}
public void BuildBooleanQuery_FourValidInputs_ValidNumParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("(cat Or dog) And (muzzle Or leash)");
// Assert
Assert.AreEqual(4, parameterizedSql.UserInputVariables.Count);
}
public void BuildPrefixQuery_ValidInput_ValidParameter()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("abc*");
// Assert
Assert.AreEqual("ABC%", parameterizedSql.UserInputVariables["field1"]);
}
public void BuildBooleanQuery_OneValidInput_ValidSql()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("+foo");
// Assert
Assert.AreEqual("((CONTAINS({{COLUMN}}, @field1)))", parameterizedSql.Sql);
}
public void BuildTermQuery_ValidInput_ValidNumParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("abc");
// Assert
Assert.AreEqual(1, parameterizedSql.UserInputVariables.Count);
}
public void BuildBooleanQuery_OneValidInput_ValidParameter()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("+foo");
// Assert
Assert.AreEqual("FOO", parameterizedSql.UserInputVariables["field1"]);
}
public void BuildBooleanQuery_ThreeValidInputs_ValidNumParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("cat OR dog AND muzzle");
// Assert
Assert.AreEqual(2, parameterizedSql.UserInputVariables.Count);
}
public void BuildBooleanQuery_SixValidInputs_ValidNumParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("(cat Or dog) And (muzzle aNd leash (-toy +treat))");
// Assert
Assert.AreEqual(6, parameterizedSql.UserInputVariables.Count);
}
public void BuildPrefixQuery_ValidInput_ValidSql()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("abc*");
// Assert
Assert.AreEqual("CONTAINS({{COLUMN}}, \"@field1\")", parameterizedSql.Sql);
}
public void BuildWildcardPhraseQuery_ValidInput_ValidParameter()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("\"Ab?Cd* dog\"");
// Assert
Assert.AreEqual("AB_CD% DOG", parameterizedSql.UserInputVariables["field1"]);
}
public void BuildTermRangeQuery_ValidInput_ValidParameter()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("{a TO B}");
// Assert
Assert.AreEqual(null, parameterizedSql);
}
public void BuildBooleanQuery_ThreeValidInputs_ValidSql()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
var sqlOutput = "((CONTAINS({{COLUMN}}, @field1)) AND (CONTAINS({{COLUMN}}, @field2)))";
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("cat OR dog AND muzzle");
// Assert
Assert.AreEqual(sqlOutput, parameterizedSql.Sql);
}
public void BuildBooleanQuery_ThreeValidInputs_ValidParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("cat OR dog AND muzzle");
// Assert
Assert.AreEqual("DOG", parameterizedSql.UserInputVariables["field1"]);
Assert.AreEqual("MUZZLE", parameterizedSql.UserInputVariables["field2"]);
}
public void BuildSqlWhereClause2_ValidInput_ValidNumParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
var luceneQuery = "fruit AND veg OR cheese";
var fields = new[] { "name", "desc", "code" };
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause(luceneQuery, fields);
// Assert
Assert.AreEqual(3, parameterizedSql.UserInputVariables.Count);
}
public void BuildBooleanQuery_FourValidInputs_ValidSql()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
var sqlOutput = "((((CONTAINS({{COLUMN}}, @field1)) OR (CONTAINS({{COLUMN}}, @field2)))) AND "
+ "(((CONTAINS({{COLUMN}}, @field3)) OR (CONTAINS({{COLUMN}}, @field4)))))";
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("(cat Or dog) And (muzzle Or leash)");
// Assert
Assert.AreEqual(sqlOutput, parameterizedSql.Sql);
}
public void BuildQuery_SqlWildcardInput_EscapedOutput()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
var luceneQuery = "\"5% of coders \\{\\{FOO\\}\\} are cod\\[ing\\] all_night_long\"";
var sqlOutput = "5[%] OF CODERS [{][{]FOO}} ARE COD[[]ING] ALL[_]NIGHT[_]LONG";
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause(luceneQuery);
// Assert
Assert.AreEqual(sqlOutput, parameterizedSql.UserInputVariables["field1"]);
}
public void BuildBooleanQuery_FourValidInputs_ValidParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("(cat Or dog) And (muzzle Or leash)");
// Assert
Assert.AreEqual("CAT", parameterizedSql.UserInputVariables["field1"]);
Assert.AreEqual("DOG", parameterizedSql.UserInputVariables["field2"]);
Assert.AreEqual("MUZZLE", parameterizedSql.UserInputVariables["field3"]);
Assert.AreEqual("LEASH", parameterizedSql.UserInputVariables["field4"]);
}
public void BuildBooleanQuery_FiveValidInputs_ValidSql()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
var sqlOutput = "((((CONTAINS({{COLUMN}}, @field1)) OR (CONTAINS({{COLUMN}}, @field2)))) AND "
+ "(((CONTAINS({{COLUMN}}, @field3)) OR (CONTAINS({{COLUMN}}, @field4)) OR "
+ "(CONTAINS({{COLUMN}}, @field5)))))";
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("(cat Or dog) And (muzzle Or leash or toy)");
// Assert
Assert.AreEqual(sqlOutput, parameterizedSql.Sql);
}
public void BuildSqlWhereClause2_ValidInput_ValidSql()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
var luceneQuery = "fruit AND veg OR cheese";
var fields = new[] { "name", "desc", "code" };
var sqlOutput = "(((CONTAINS(name, @field1)))) OR "
+ "(((CONTAINS(desc, @field2)))) OR "
+ "(((CONTAINS(code, @field3))))";
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause(luceneQuery, fields);
// Assert
Assert.AreEqual(sqlOutput, parameterizedSql.Sql);
}
public void BuildBooleanQuery_FiveValidInputs_ValidParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("(cat Or dog) And (muzzle Or leash or toy)");
// Assert
Assert.AreEqual("CAT", parameterizedSql.UserInputVariables["field1"]);
Assert.AreEqual("DOG", parameterizedSql.UserInputVariables["field2"]);
Assert.AreEqual("MUZZLE", parameterizedSql.UserInputVariables["field3"]);
Assert.AreEqual("LEASH", parameterizedSql.UserInputVariables["field4"]);
Assert.AreEqual("TOY", parameterizedSql.UserInputVariables["field5"]);
}
public void BuildBooleanSqlStatement_TwoValidInputs_ValidNumParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlStatement(
"foo AND bar", "table",
new List <string>()
{
"name", "desc"
},
new List <string>()
{
"id", "name", "desc"
});
// Assert
Assert.AreEqual(4, parameterizedSql.UserInputVariables.Count);
}
public void BuildSqlWhereClause_ValidInput_ValidParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
var luceneQuery = "fruit AND (veg OR cheese)";
var fields = new[] { "name", "desc", "code" };
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause(luceneQuery, fields);
// Assert
Assert.AreEqual("FRUIT", parameterizedSql.UserInputVariables["field1"]);
Assert.AreEqual("VEG", parameterizedSql.UserInputVariables["field2"]);
Assert.AreEqual("CHEESE", parameterizedSql.UserInputVariables["field3"]);
Assert.AreEqual("FRUIT", parameterizedSql.UserInputVariables["field4"]);
Assert.AreEqual("VEG", parameterizedSql.UserInputVariables["field5"]);
Assert.AreEqual("CHEESE", parameterizedSql.UserInputVariables["field6"]);
Assert.AreEqual("FRUIT", parameterizedSql.UserInputVariables["field7"]);
Assert.AreEqual("VEG", parameterizedSql.UserInputVariables["field8"]);
Assert.AreEqual("CHEESE", parameterizedSql.UserInputVariables["field9"]);
}
public void BuildBooleanQuery_TwoValidInputs_ValidNumParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("foo AND bar");
// Assert
Assert.AreEqual(2, parameterizedSql.UserInputVariables.Count);
}
public void BuildBooleanSqlStatement_TwoValidInputs_ValidSql()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
var sqlOutput =
"SELECT \"id\", \"name\", \"desc\"\n" +
"FROM \"table\"\n" +
"WHERE " +
"(((CONTAINS(\"name\", @field1)) AND (CONTAINS(\"name\", @field2)))) OR " +
"(((CONTAINS(\"desc\", @field3)) AND (CONTAINS(\"desc\", @field4))));";
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlStatement(
"foo AND bar", "table",
new List<string>() { "name", "desc" },
new List<string>() { "id", "name", "desc" });
// Assert
Assert.AreEqual(sqlOutput, parameterizedSql.Sql);
}
public void BuildTermRangeQuery_ValidInput_ValidSql()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("{a TO B}");
// Assert
Assert.AreEqual(null, parameterizedSql);
}
public void BuildWildcardQuery_ValidInput_ValidParameter()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("Ab?Cd*");
// Assert
Assert.AreEqual("AB_CD%", parameterizedSql.UserInputVariables["field1"]);
}
public void BuildWildcardPhraseQuery_ValidInput_ValidNumParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("\"Ab?Cd* dog\"");
// Assert
Assert.AreEqual(1, parameterizedSql.UserInputVariables.Count);
}
public void BuildPhraseQuery_ValidInput_ValidSql()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("\"abc def\"");
// Assert
Assert.AreEqual("CONTAINS({{COLUMN}}, @field1)", parameterizedSql.Sql);
}
public void BuildPhraseQuery_OneValidInput_ValidParameter()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("\"abc def\"");
// Assert
Assert.AreEqual("ABC DEF", parameterizedSql.UserInputVariables["field1"]);
}
public void BuildWildcardQuery_ValidInput_ValidSql()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("Ab?Cd*");
// Assert
Assert.AreEqual("CONTAINS({{COLUMN}}, \"@field1\")", parameterizedSql.Sql);
}
public void BuildPrefixQuery_ValidInput_ValidNumParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("abc*");
// Assert
Assert.AreEqual(1, parameterizedSql.UserInputVariables.Count);
}
public void BuildBooleanQuery_TwoValidInputs_ValidSql()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
var sqlOutput = "((CONTAINS({{COLUMN}}, @field1)) AND (CONTAINS({{COLUMN}}, @field2)))";
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("foo AND bar");
// Assert
Assert.AreEqual(sqlOutput, parameterizedSql.Sql);
}
public void BuildBooleanSqlStatement_TwoValidInputs_ValidParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlStatement(
"foo AND bar", "table",
new List<string>() { "name", "desc" },
new List<string>() { "id", "name", "desc" });
// Assert
Assert.AreEqual("FOO", parameterizedSql.UserInputVariables["field1"]);
Assert.AreEqual("BAR", parameterizedSql.UserInputVariables["field2"]);
Assert.AreEqual("FOO", parameterizedSql.UserInputVariables["field3"]);
Assert.AreEqual("BAR", parameterizedSql.UserInputVariables["field4"]);
}
public void BuildQuery_SqlWildcardInput_EscapedOutput()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
var luceneQuery = "\"5% of coders \\{\\{FOO\\}\\} are cod\\[ing\\] all_night_long\"";
var sqlOutput = "5[%] OF CODERS [{][{]FOO}} ARE COD[[]ING] ALL[_]NIGHT[_]LONG";
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause(luceneQuery);
// Assert
Assert.AreEqual(sqlOutput, parameterizedSql.UserInputVariables["field1"]);
}
public void BuildSqlWhereClause_ValidInput_ValidNumParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
var luceneQuery = "fruit AND (veg OR cheese)";
var fields = new[] {"name", "desc", "code"};
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause(luceneQuery, fields);
// Assert
Assert.AreEqual(9, parameterizedSql.UserInputVariables.Count);
}
public void BuildSqlWhereClause_ValidInput_ValidParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
var luceneQuery = "fruit AND (veg OR cheese)";
var fields = new[] {"name", "desc", "code"};
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause(luceneQuery, fields);
// Assert
Assert.AreEqual("FRUIT", parameterizedSql.UserInputVariables["field1"]);
Assert.AreEqual("VEG", parameterizedSql.UserInputVariables["field2"]);
Assert.AreEqual("CHEESE", parameterizedSql.UserInputVariables["field3"]);
Assert.AreEqual("FRUIT", parameterizedSql.UserInputVariables["field4"]);
Assert.AreEqual("VEG", parameterizedSql.UserInputVariables["field5"]);
Assert.AreEqual("CHEESE", parameterizedSql.UserInputVariables["field6"]);
Assert.AreEqual("FRUIT", parameterizedSql.UserInputVariables["field7"]);
Assert.AreEqual("VEG", parameterizedSql.UserInputVariables["field8"]);
Assert.AreEqual("CHEESE", parameterizedSql.UserInputVariables["field9"]);
}
public void BuildBooleanQuery_ThreeValidInputs_ValidParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("cat OR dog AND muzzle");
// Assert
Assert.AreEqual("DOG", parameterizedSql.UserInputVariables["field1"]);
Assert.AreEqual("MUZZLE", parameterizedSql.UserInputVariables["field2"]);
}
public void BuildSqlWhereClause_ValidInput_ValidSql()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
var luceneQuery = "fruit AND (veg OR cheese)";
var fields = new[] {"name", "desc", "code"};
var sqlOutput = "(((CONTAINS(name, @field1)) AND (((CONTAINS(name, @field2)) OR (CONTAINS(name, @field3)))))) OR "
+ "(((CONTAINS(desc, @field4)) AND (((CONTAINS(desc, @field5)) OR (CONTAINS(desc, @field6)))))) OR "
+ "(((CONTAINS(code, @field7)) AND (((CONTAINS(code, @field8)) OR (CONTAINS(code, @field9))))))";
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause(luceneQuery, fields);
// Assert
Assert.AreEqual(sqlOutput, parameterizedSql.Sql);
}
public void BuildBooleanQuery_OneValidInput_ValidSql()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("+foo");
// Assert
Assert.AreEqual("((CONTAINS({{COLUMN}}, @field1)))", parameterizedSql.Sql);
}
public void BuildBooleanQuery_TwoValidInputs_ValidParameters()
{
// Arrange
var sqlQueryBuilder = new SqlServerFullTextQueryBuilder();
// Act
var parameterizedSql = sqlQueryBuilder.BuildSqlWhereClause("foo AND bar");
// Assert
Assert.AreEqual("FOO", parameterizedSql.UserInputVariables["field1"]);
Assert.AreEqual("BAR", parameterizedSql.UserInputVariables["field2"]);
}
