From 0000a65ab4479a2a40e2d6468036438e9705b4aa Mon Sep 17 00:00:00 2001 From: vlad Date: Tue, 13 Jun 2017 10:25:55 -0700 Subject: Initial extraction of Driver non-specific utilities --- .../common/db/QueryBuilderParametersSuite.scala | 249 +++++++++++++++++++++ .../driver/common/db/SearchFilterExprSuite.scala | 32 +++ 2 files changed, 281 insertions(+) create mode 100644 src/test/scala/xyz/driver/common/db/QueryBuilderParametersSuite.scala create mode 100644 src/test/scala/xyz/driver/common/db/SearchFilterExprSuite.scala (limited to 'src/test/scala/xyz/driver/common/db') diff --git a/src/test/scala/xyz/driver/common/db/QueryBuilderParametersSuite.scala b/src/test/scala/xyz/driver/common/db/QueryBuilderParametersSuite.scala new file mode 100644 index 0000000..e49ccd9 --- /dev/null +++ b/src/test/scala/xyz/driver/common/db/QueryBuilderParametersSuite.scala @@ -0,0 +1,249 @@ +package xyz.driver.common.db + +import java.time.LocalDateTime + +import io.getquill.MysqlEscape +import org.scalatest.FreeSpecLike +import xyz.driver.common.db.QueryBuilder.TableData +import xyz.driver.common.domain.{Email, LongId, User} + +class QueryBuilderParametersSuite extends FreeSpecLike { + + import SearchFilterBinaryOperation._ + import SearchFilterExpr.{Dimension => _, _} + import SearchFilterNAryOperation._ + import Sorting._ + import SortingOrder._ + + val tableName = "Entity" + + case class Entity(id: LongId[Entity], + name: String, + email: Email, + optionUser: Option[LongId[User]], + date: LocalDateTime, + optionDate: Option[LocalDateTime], + kindId: Long) + + def queryBuilderParameters = MysqlQueryBuilderParameters( + tableData = TableData( + tableName = tableName, + nullableFields = Set("optionUser", "optionDate") + ), + links = Map( + "Kind" -> TableLink("kindId", "Kind", "id"), + "User" -> TableLink("optionUser", "User", "id") + ) + ) + + val queryBasis = + s"""select `$tableName`.* + |from `$tableName`""".stripMargin.trim + + "toSql" - { + "should generate correct SQL query" - { + "with default parameters" in { + val (sql, _) = queryBuilderParameters.toSql(namingStrategy = MysqlEscape) + assert(sql == queryBasis) + } + + "with filtering: " - { + "single atom filter" in { + val (sql, _) = queryBuilderParameters.copy(filter = Atom.Binary("name", Eq, "x")).toSql(namingStrategy = MysqlEscape) + assert(sql == + s"""$queryBasis + |where `$tableName`.`name` = ?""".stripMargin) + } + + "single atom filter for optional field with NotEq operation" in { + val (sql, _) = queryBuilderParameters.copy(filter = Atom.Binary("optionUser", NotEq, "x")).toSql(namingStrategy = MysqlEscape) + assert(sql == + s"""$queryBasis + |where (`$tableName`.`optionUser` is null or `$tableName`.`optionUser` != ?)""".stripMargin) + } + + "single atom filter for field with IN operation" in { + val (sql, _) = queryBuilderParameters.copy(filter = Atom.NAry("date", In, Seq("x", "x", "x"))).toSql(namingStrategy = MysqlEscape) + assert(sql == + s"""$queryBasis + |where `$tableName`.`date` in (?, ?, ?)""".stripMargin) + } + + "multiple intersected filters" in { + val (sql, _) = queryBuilderParameters.copy(filter = Intersection(Seq( + Atom.Binary("name", Gt, "x"), + Atom.Binary("optionDate", GtEq, "x") + ))).toSql(namingStrategy = MysqlEscape) + assert(sql == + s"""$queryBasis + |where (`$tableName`.`name` > ? and `$tableName`.`optionDate` >= ?)""".stripMargin) + } + + "multiple intersected nested filters" in { + val (sql, _) = queryBuilderParameters.copy(filter = Intersection(Seq( + Atom.Binary("name", Gt, "x"), + Atom.Binary("optionDate", GtEq, "x"), + Intersection(Seq( + Atom.Binary("optionUser", Eq, "x"), + Atom.Binary("date", LtEq, "x") + )) + ))).toSql(namingStrategy = MysqlEscape) + assert(sql == + s"$queryBasis\nwhere (`$tableName`.`name` > ? and `$tableName`.`optionDate` >= ?" + + s" and (`$tableName`.`optionUser` = ? and `$tableName`.`date` <= ?))") + } + + "multiple unionized filters" in { + val (sql, _) = queryBuilderParameters.copy(filter = Union(Seq( + Atom.Binary("name", Gt, "x"), + Atom.Binary("optionDate", GtEq, "x") + ))).toSql(namingStrategy = MysqlEscape) + assert(sql == + s"""$queryBasis + |where (`$tableName`.`name` > ? or `$tableName`.`optionDate` >= ?)""".stripMargin.trim) + } + + "multiple unionized nested filters" in { + val (sql, _) = queryBuilderParameters.copy(filter = Union(Seq( + Atom.Binary("name", Gt, "x"), + Atom.Binary("optionDate", GtEq, "x"), + Union(Seq( + Atom.Binary("optionUser", Eq, "x"), + Atom.Binary("date", LtEq, "x") + )) + ))).toSql(namingStrategy = MysqlEscape) + assert(sql == + s"""$queryBasis + |where (`$tableName`.`name` > ? or `$tableName`.`optionDate` >= ? or (`$tableName`.`optionUser` = ? or `$tableName`.`date` <= ?))""".stripMargin) + } + + "multiple unionized and intersected nested filters" in { + val (sql, _) = queryBuilderParameters.copy(filter = Union(Seq( + Intersection(Seq( + Atom.Binary("name", Gt, "x"), + Atom.Binary("optionDate", GtEq, "x") + )), + Intersection(Seq( + Atom.Binary("optionUser", Eq, "x"), + Atom.Binary("date", LtEq, "x") + )) + ))).toSql(namingStrategy = MysqlEscape) + + assert(sql == + s"$queryBasis\nwhere ((`$tableName`.`name` > ? and `$tableName`.`optionDate` >= ?) " + + s"or (`$tableName`.`optionUser` = ? and `$tableName`.`date` <= ?))") + } + + "single field from foreign table" in { + val (sql, _) = queryBuilderParameters + .copy(filter = Atom.Binary(SearchFilterExpr.Dimension(Some("Kind"), "name"), Eq, "x")) + .toSql(namingStrategy = MysqlEscape) + val pattern = + s"""select `$tableName`.* + |from `$tableName` + |inner join `Kind` on `Entity`.`kindId` = `Kind`.`id` + |where `Kind`.`name` = ?""".stripMargin + assert(sql == pattern) + } + } + + "with sorting:" - { + "single field sorting" in { + val (sql, _) = queryBuilderParameters.copy(sorting = Dimension(None, "name", Ascending)).toSql(namingStrategy = MysqlEscape) + + assert(sql == + s"""$queryBasis + |order by `$tableName`.`name` asc""".stripMargin) + } + + "single foreign sorting field" in { + val (sql, _) = queryBuilderParameters.copy(sorting = Dimension(Some("Kind"), "name", Ascending)).toSql(namingStrategy = MysqlEscape) + + assert(sql == + s"""select `$tableName`.* + |from `$tableName` + |inner join `Kind` on `Entity`.`kindId` = `Kind`.`id` + |order by `Kind`.`name` asc""".stripMargin) + } + + "multiple fields sorting" in { + val (sql, _) = queryBuilderParameters.copy(sorting = Sequential(Seq( + Dimension(None, "name", Ascending), + Dimension(None, "date", Descending) + ))).toSql(namingStrategy = MysqlEscape) + assert(sql == + s"""$queryBasis + |order by `$tableName`.`name` asc, `$tableName`.`date` desc""".stripMargin) + } + + "multiple foreign sorting field" in { + val (sql, _) = queryBuilderParameters.copy(sorting = Sequential(Seq( + Dimension(Some("Kind"), "name", Ascending), + Dimension(Some("User"), "name", Descending) + ))).toSql(namingStrategy = MysqlEscape) + + assert(sql == + s"""select `$tableName`.* + |from `$tableName` + |inner join `Kind` on `$tableName`.`kindId` = `Kind`.`id` + |inner join `User` on `$tableName`.`optionUser` = `User`.`id` + |order by `Kind`.`name` asc, `User`.`name` desc""".stripMargin) + } + + "multiple field sorting (including foreign tables)" in { + val (sql, _) = queryBuilderParameters.copy(sorting = Sequential(Seq( + Dimension(Some("Kind"), "name", Ascending), + Dimension(None, "date", Descending) + ))).toSql(namingStrategy = MysqlEscape) + + assert(sql == + s"""select `$tableName`.* + |from `$tableName` + |inner join `Kind` on `$tableName`.`kindId` = `Kind`.`id` + |order by `Kind`.`name` asc, `$tableName`.`date` desc""".stripMargin) + } + } + + "with pagination" in { + val (sql, _) = queryBuilderParameters.copy(pagination = Some(Pagination(5, 3))).toSql(namingStrategy = MysqlEscape) + assert(sql == + s"""$queryBasis + |limit 10, 5""".stripMargin) + } + + "combined" in { + val filter = Union(Seq( + Intersection(Seq( + Atom.Binary("name", Gt, "x"), + Atom.Binary("optionDate", GtEq, "x") + )), + Intersection(Seq( + Atom.Binary("optionUser", Eq, "x"), + Atom.Binary("date", LtEq, "x") + )) + )) + val sorting = Sequential(Seq( + Dimension(Some("Kind"), "name", Ascending), + Dimension(None, "name", Ascending), + Dimension(None, "date", Descending) + )) + + val (sql, _) = queryBuilderParameters.copy( + filter = filter, + sorting = sorting, + pagination = Some(Pagination(5, 3)) + ).toSql(namingStrategy = MysqlEscape) + + assert(sql == + s"""select `$tableName`.* + |from `$tableName` + |inner join `Kind` on `$tableName`.`kindId` = `Kind`.`id` + |where ((`$tableName`.`name` > ? and `$tableName`.`optionDate` >= ?) or (`$tableName`.`optionUser` = ? and `$tableName`.`date` <= ?)) + |order by `Kind`.`name` asc, `$tableName`.`name` asc, `$tableName`.`date` desc + |limit 10, 5""".stripMargin) + } + + } + } + +} diff --git a/src/test/scala/xyz/driver/common/db/SearchFilterExprSuite.scala b/src/test/scala/xyz/driver/common/db/SearchFilterExprSuite.scala new file mode 100644 index 0000000..3073b61 --- /dev/null +++ b/src/test/scala/xyz/driver/common/db/SearchFilterExprSuite.scala @@ -0,0 +1,32 @@ +package xyz.driver.common.db + +import org.scalatest.{FreeSpecLike, MustMatchers} + +class SearchFilterExprSuite extends FreeSpecLike with MustMatchers { + + "replace" - { + "all entities are changed" in { + val ast = SearchFilterExpr.Union(Seq( + SearchFilterExpr.Intersection(Seq( + SearchFilterExpr.Atom.Binary("foo", SearchFilterBinaryOperation.Gt, "10"), + SearchFilterExpr.Atom.Binary("foo", SearchFilterBinaryOperation.Lt, "20") + )), + SearchFilterExpr.Atom.NAry("bar", SearchFilterNAryOperation.In, Seq("x", "y", "z")), + SearchFilterExpr.Atom.Binary("foo", SearchFilterBinaryOperation.Eq, "40") + )) + + val newAst = ast.replace { + case x: SearchFilterExpr.Atom.Binary if x.dimension.name == "foo" => + x.copy(dimension = x.dimension.copy(name = "bar")) + } + + val result = newAst.find { + case x: SearchFilterExpr.Atom.Binary => x.dimension.name == "foo" + case _ => false + } + + result mustBe empty + } + } + +} -- cgit v1.2.3