path: root/src/test/scala/xyz/driver/pdsuicommon/db/QueryBuilderParametersSuite.scala

package xyz.driver.pdsuicommon.db

import java.time.LocalDateTime

import io.getquill.MysqlEscape
import org.scalatest.FreeSpecLike
import xyz.driver.pdsuicommon.db.QueryBuilder.TableData
import xyz.driver.pdsuicommon.domain._

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[StringId[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)
      }

    }
  }

}