From 0fc769e8e2141451da34247e7733fcf0a3396b9c Mon Sep 17 00:00:00 2001 From: Kseniya Tomskikh Date: Thu, 17 Aug 2017 16:34:06 +0700 Subject: Created SlickQueryBuilder --- build.sbt | 3 +- .../pdsuicommon/db/SlickPostgresQueryBuilder.scala | 103 +++++++ .../driver/pdsuicommon/db/SlickQueryBuilder.scala | 341 +++++++++++++++++++++ 3 files changed, 446 insertions(+), 1 deletion(-) create mode 100644 src/main/scala/xyz/driver/pdsuicommon/db/SlickPostgresQueryBuilder.scala create mode 100644 src/main/scala/xyz/driver/pdsuicommon/db/SlickQueryBuilder.scala diff --git a/build.sbt b/build.sbt index 8801de7..7778e02 100644 --- a/build.sbt +++ b/build.sbt @@ -28,5 +28,6 @@ lazy val core = (project in file(".")) "org.asynchttpclient" % "async-http-client" % "2.0.24", "org.slf4j" % "slf4j-api" % "1.7.21", "ai.x" %% "diff" % "1.2.0-get-simple-name-fix" % "test", - "org.scalatest" %% "scalatest" % "3.0.0" % "test" + "org.scalatest" %% "scalatest" % "3.0.0" % "test", + "xyz.driver" %% "core" % "0.16.3" excludeAll (ExclusionRule(organization = "io.netty")) )) diff --git a/src/main/scala/xyz/driver/pdsuicommon/db/SlickPostgresQueryBuilder.scala b/src/main/scala/xyz/driver/pdsuicommon/db/SlickPostgresQueryBuilder.scala new file mode 100644 index 0000000..66434f0 --- /dev/null +++ b/src/main/scala/xyz/driver/pdsuicommon/db/SlickPostgresQueryBuilder.scala @@ -0,0 +1,103 @@ +package xyz.driver.pdsuicommon.db + +import java.time.{LocalDateTime, ZoneOffset} + +import slick.driver.JdbcProfile +import slick.jdbc.GetResult +import xyz.driver.core.database.SlickDal + +import scala.collection.breakOut +import scala.concurrent.ExecutionContext + +object SlickPostgresQueryBuilder { + + import xyz.driver.pdsuicommon.db.SlickQueryBuilder._ + + def apply[T](tableName: String, + lastUpdateFieldName: Option[String], + nullableFields: Set[String], + links: Set[SlickTableLink], + runner: Runner[T], + countRunner: CountRunner)(implicit sqlContext: SlickDal, + profile: JdbcProfile, + getResult: GetResult[T], + ec: ExecutionContext): SlickPostgresQueryBuilder[T] = { + val parameters = SlickPostgresQueryBuilderParameters( + tableData = TableData(tableName, lastUpdateFieldName, nullableFields), + links = links.map(x => x.foreignTableName -> x)(breakOut) + ) + new SlickPostgresQueryBuilder[T](parameters)(runner, countRunner) + } + + def apply[T](tableName: String, + lastUpdateFieldName: Option[String], + nullableFields: Set[String], + links: Set[SlickTableLink])(implicit sqlContext: SlickDal, + profile: JdbcProfile, + getResult: GetResult[T], + ec: ExecutionContext): SlickPostgresQueryBuilder[T] = { + apply(tableName, SlickQueryBuilderParameters.AllFields, lastUpdateFieldName, nullableFields, links) + } + + def apply[T](tableName: String, + fields: Set[String], + lastUpdateFieldName: Option[String], + nullableFields: Set[String], + links: Set[SlickTableLink])(implicit sqlContext: SlickDal, + profile: JdbcProfile, + getResult: GetResult[T], + ec: ExecutionContext): SlickPostgresQueryBuilder[T] = { + + val runner: Runner[T] = { parameters => + val sql = parameters.toSql(countQuery = false, fields = fields).as[T] + sqlContext.execute(sql) + } + + val countRunner: CountRunner = { parameters => + implicit val getCountResult: GetResult[(Int, Option[LocalDateTime])] = GetResult({ r => + val count = r.rs.getInt(1) + val lastUpdate = if (parameters.tableData.lastUpdateFieldName.isDefined) { + Option(r.rs.getTimestamp(2)).map(timestampToLocalDateTime) + } else None + (count, lastUpdate) + }) + val sql = parameters.toSql(countQuery = true).as[(Int, Option[LocalDateTime])] + sqlContext.execute(sql).map(_.head) + } + + apply[T]( + tableName = tableName, + lastUpdateFieldName = lastUpdateFieldName, + nullableFields = nullableFields, + links = links, + runner = runner, + countRunner = countRunner + ) + } + + def timestampToLocalDateTime(timestamp: java.sql.Timestamp): LocalDateTime = { + LocalDateTime.ofInstant(timestamp.toInstant, ZoneOffset.UTC) + } +} + +class SlickPostgresQueryBuilder[T](parameters: SlickPostgresQueryBuilderParameters)( + implicit runner: SlickQueryBuilder.Runner[T], + countRunner: SlickQueryBuilder.CountRunner) + extends SlickQueryBuilder[T](parameters) { + + def withFilter(newFilter: SearchFilterExpr): SlickQueryBuilder[T] = { + new SlickPostgresQueryBuilder[T](parameters.copy(filter = newFilter)) + } + + def withSorting(newSorting: Sorting): SlickQueryBuilder[T] = { + new SlickPostgresQueryBuilder[T](parameters.copy(sorting = newSorting)) + } + + def withPagination(newPagination: Pagination): SlickQueryBuilder[T] = { + new SlickPostgresQueryBuilder[T](parameters.copy(pagination = Some(newPagination))) + } + + def resetPagination: SlickQueryBuilder[T] = { + new SlickPostgresQueryBuilder[T](parameters.copy(pagination = None)) + } +} diff --git a/src/main/scala/xyz/driver/pdsuicommon/db/SlickQueryBuilder.scala b/src/main/scala/xyz/driver/pdsuicommon/db/SlickQueryBuilder.scala new file mode 100644 index 0000000..e45ff87 --- /dev/null +++ b/src/main/scala/xyz/driver/pdsuicommon/db/SlickQueryBuilder.scala @@ -0,0 +1,341 @@ +package xyz.driver.pdsuicommon.db + +import java.sql.PreparedStatement +import java.time.LocalDateTime + +import slick.driver.JdbcProfile +import slick.jdbc.{PositionedParameters, SQLActionBuilder, SetParameter} +import xyz.driver.pdsuicommon.db.Sorting.{Dimension, Sequential} +import xyz.driver.pdsuicommon.db.SortingOrder.{Ascending, Descending} + +import scala.concurrent.{ExecutionContext, Future} + +object SlickQueryBuilder { + + type Runner[T] = SlickQueryBuilderParameters => Future[Seq[T]] + + type CountResult = Future[(Int, Option[LocalDateTime])] + + type CountRunner = SlickQueryBuilderParameters => CountResult + + /** + * Binder for PreparedStatement + */ + type Binder = PreparedStatement => PreparedStatement + + final case class TableData(tableName: String, + lastUpdateFieldName: Option[String] = None, + nullableFields: Set[String] = Set.empty) + + val AllFields = Set("*") + + implicit class SQLActionBuilderConcat(a: SQLActionBuilder) { + def concat(b: SQLActionBuilder): SQLActionBuilder = { + SQLActionBuilder(a.queryParts ++ b.queryParts, new SetParameter[Unit] { + def apply(p: Unit, pp: PositionedParameters): Unit = { + a.unitPConv.apply(p, pp) + b.unitPConv.apply(p, pp) + } + }) + } + } +} + +final case class SlickTableLink(keyColumnName: String, foreignTableName: String, foreignKeyColumnName: String) + +object SlickQueryBuilderParameters { + val AllFields = Set("*") +} + +sealed trait SlickQueryBuilderParameters { + import SlickQueryBuilder._ + + def tableData: SlickQueryBuilder.TableData + def links: Map[String, SlickTableLink] + def filter: SearchFilterExpr + def sorting: Sorting + def pagination: Option[Pagination] + + def findLink(tableName: String): SlickTableLink = links.get(tableName) match { + case None => throw new IllegalArgumentException(s"Cannot find a link for `$tableName`") + case Some(link) => link + } + + def toSql(countQuery: Boolean = false)(implicit profile: JdbcProfile): SQLActionBuilder = { + toSql(countQuery, QueryBuilderParameters.AllFields) + } + + def toSql(countQuery: Boolean, fields: Set[String])(implicit profile: JdbcProfile): SQLActionBuilder = { + import profile.api._ + val escapedTableName = tableData.tableName + val fieldsSql: String = if (countQuery) { + val suffix: String = tableData.lastUpdateFieldName match { + case Some(lastUpdateField) => s", max($escapedTableName.$lastUpdateField)" + case None => "" + } + "count(*)" + suffix + } else { + if (fields == SlickQueryBuilderParameters.AllFields) { + s"$escapedTableName.*" + } else { + fields + .map { field => + s"$escapedTableName.$field" + } + .mkString(", ") + } + } + val where = filterToSql(escapedTableName, filter) + val orderBy = sortingToSql(escapedTableName, sorting) + + val limitSql = limitToSql() + + val sql = sql"select #$fieldsSql from #$escapedTableName" + + val filtersTableLinks: Seq[SlickTableLink] = { + import SearchFilterExpr._ + def aux(expr: SearchFilterExpr): Seq[SlickTableLink] = expr match { + case Atom.TableName(tableName) => List(findLink(tableName)) + case Intersection(xs) => xs.flatMap(aux) + case Union(xs) => xs.flatMap(aux) + case _ => Nil + } + aux(filter) + } + + val sortingTableLinks: Seq[SlickTableLink] = Sorting.collect(sorting) { + case Dimension(Some(foreignTableName), _, _) => findLink(foreignTableName) + } + + // Combine links from sorting and filter without duplicates + val foreignTableLinks = (filtersTableLinks ++ sortingTableLinks).distinct + + foreignTableLinks.foreach { + case SlickTableLink(keyColumnName, foreignTableName, foreignKeyColumnName) => + sql = sql concat sql"""inner join #$foreignTableName + on #$escapedTableName.#$keyColumnName = #$foreignTableName.#$foreignKeyColumnName""" + } + + if (where.toString.nonEmpty) { + sql = sql concat sql"where #$where" + } + + if (orderBy.toString.nonEmpty && !countQuery) { + sql = sql concat sql"order by #$orderBy" + } + + if (limitSql.toString.nonEmpty && !countQuery) { + sql = sql concat sql"#$limitSql" + } + + sql + } + + /** + * Converts filter expression to SQL expression. + * + * @return Returns SQL string and list of values for binding in prepared statement. + */ + protected def filterToSql(escapedTableName: String, filter: SearchFilterExpr)( + implicit profile: JdbcProfile): SQLActionBuilder = { + import SearchFilterBinaryOperation._ + import SearchFilterExpr._ + import profile.api._ + + def isNull(string: AnyRef) = Option(string).isEmpty || string.toString.toLowerCase == "null" + + def escapeDimension(dimension: SearchFilterExpr.Dimension) = { + val tableName = escapedTableName + s"$tableName.$dimension.name" + } + + def filterToSqlMultiple(operands: Seq[SearchFilterExpr]) = operands.collect { + case x if !SearchFilterExpr.isEmpty(x) => filterToSql(escapedTableName, x) + } + + def multipleSqlToAction(op: String, conditions: Seq[SQLActionBuilder]): SQLActionBuilder = { + var first = true + var filterSql = sql"(" + for (condition <- conditions) { + if (first) { + filterSql = filterSql concat condition + first = false + } else { + filterSql = filterSql concat sql" #$op " concat condition + } + } + filterSql concat sql")" + } + + filter match { + case x if isEmpty(x) => + sql"" + + case AllowAll => + sql"1" + + case DenyAll => + sql"0" + + case Atom.Binary(dimension, Eq, value) if isNull(value) => + sql"#${escapeDimension(dimension)} is NULL" + + case Atom.Binary(dimension, NotEq, value) if isNull(value) => + sql"#${escapeDimension(dimension)} is not NULL" + + case Atom.Binary(dimension, NotEq, value) if tableData.nullableFields.contains(dimension.name) => + // In MySQL NULL <> Any === NULL + // So, to handle NotEq for nullable fields we need to use more complex SQL expression. + // http://dev.mysql.com/doc/refman/5.7/en/working-with-null.html + val escapedColumn = escapeDimension(dimension) + sql"(#$escapedColumn is null or #$escapedColumn != #$value)" + + case Atom.Binary(dimension, op, value) => + val operator = op match { + case Eq => sql"=" + case NotEq => sql"!=" + case Like => sql"like" + case Gt => sql">" + case GtEq => sql">=" + case Lt => sql"<" + case LtEq => sql"<=" + } + sql"#${escapeDimension(dimension)}" concat operator concat sql"#$value" + + case Atom.NAry(dimension, op, values) => + val sqlOp = op match { + case SearchFilterNAryOperation.In => sql"in" + case SearchFilterNAryOperation.NotIn => sql"not in" + } + + val formattedValues = if (values.nonEmpty) { + sql"#$values" + } else sql"NULL" + sql"#${escapeDimension(dimension)}" concat sqlOp concat formattedValues + + case Intersection(operands) => + multipleSqlToAction("and", filterToSqlMultiple(operands)) + + case Union(operands) => + multipleSqlToAction("or", filterToSqlMultiple(operands)) + } + } + + protected def limitToSql()(implicit profile: JdbcProfile): SQLActionBuilder + + /** + * @param escapedMainTableName Should be escaped + */ + protected def sortingToSql(escapedMainTableName: String, sorting: Sorting)( + implicit profile: JdbcProfile): SQLActionBuilder = { + import profile.api._ + sorting match { + case Dimension(optSortingTableName, field, order) => + val sortingTableName = optSortingTableName.getOrElse(escapedMainTableName) + val fullName = s"$sortingTableName.$field" + + sql"#$fullName #${orderToSql(order)}" + + case Sequential(xs) => + sql"#${xs.map(sortingToSql(escapedMainTableName, _)).mkString(", ")}" + } + } + + protected def orderToSql(x: SortingOrder): String = x match { + case Ascending => "asc" + case Descending => "desc" + } + + protected def binder(bindings: List[AnyRef])(bind: PreparedStatement): PreparedStatement = { + bindings.zipWithIndex.foreach { + case (binding, index) => + bind.setObject(index + 1, binding) + } + + bind + } + +} + +final case class SlickPostgresQueryBuilderParameters(tableData: SlickQueryBuilder.TableData, + links: Map[String, SlickTableLink] = Map.empty, + filter: SearchFilterExpr = SearchFilterExpr.Empty, + sorting: Sorting = Sorting.Empty, + pagination: Option[Pagination] = None) + extends SlickQueryBuilderParameters { + + def limitToSql()(implicit profile: JdbcProfile): SQLActionBuilder = { + import profile.api._ + pagination.map { pagination => + val startFrom = (pagination.pageNumber - 1) * pagination.pageSize + sql"limit #${pagination.pageSize} OFFSET #$startFrom" + } getOrElse (sql"") + } + +} + +/** + * @param links Links to another tables grouped by foreignTableName + */ +final case class SlickMysqlQueryBuilderParameters(tableData: SlickQueryBuilder.TableData, + links: Map[String, SlickTableLink] = Map.empty, + filter: SearchFilterExpr = SearchFilterExpr.Empty, + sorting: Sorting = Sorting.Empty, + pagination: Option[Pagination] = None) + extends SlickQueryBuilderParameters { + + def limitToSql()(implicit profile: JdbcProfile): SQLActionBuilder = { + import profile.api._ + pagination + .map { pagination => + val startFrom = (pagination.pageNumber - 1) * pagination.pageSize + sql"limit #$startFrom, #${pagination.pageSize}" + } + .getOrElse(sql"") + } + +} + +abstract class SlickQueryBuilder[T](val parameters: SlickQueryBuilderParameters)( + implicit runner: SlickQueryBuilder.Runner[T], + countRunner: SlickQueryBuilder.CountRunner) { + + def run()(implicit ec: ExecutionContext): Future[Seq[T]] = runner(parameters) + + def runCount()(implicit ec: ExecutionContext): SlickQueryBuilder.CountResult = countRunner(parameters) + + /** + * Runs the query and returns total found rows without considering of pagination. + */ + def runWithCount()(implicit ec: ExecutionContext): Future[(Seq[T], Int, Option[LocalDateTime])] = { + for { + all <- run + (total, lastUpdate) <- runCount + } yield (all, total, lastUpdate) + } + + def withFilter(newFilter: SearchFilterExpr): SlickQueryBuilder[T] + + def withFilter(filter: Option[SearchFilterExpr]): SlickQueryBuilder[T] = { + filter.fold(this)(withFilter) + } + + def resetFilter: SlickQueryBuilder[T] = withFilter(SearchFilterExpr.Empty) + + def withSorting(newSorting: Sorting): SlickQueryBuilder[T] + + def withSorting(sorting: Option[Sorting]): SlickQueryBuilder[T] = { + sorting.fold(this)(withSorting) + } + + def resetSorting: SlickQueryBuilder[T] = withSorting(Sorting.Empty) + + def withPagination(newPagination: Pagination): SlickQueryBuilder[T] + + def withPagination(pagination: Option[Pagination]): SlickQueryBuilder[T] = { + pagination.fold(this)(withPagination) + } + + def resetPagination: SlickQueryBuilder[T] + +} -- cgit v1.2.3 From 2c17e8696ea1e5cbe3f557dfc62b1ca9c66a1135 Mon Sep 17 00:00:00 2001 From: Kseniya Tomskikh Date: Fri, 18 Aug 2017 16:17:05 +0700 Subject: Fixed compile errors --- .../pdsuicommon/db/SlickPostgresQueryBuilder.scala | 7 +- .../driver/pdsuicommon/db/SlickQueryBuilder.scala | 76 ++++++++++++---------- 2 files changed, 48 insertions(+), 35 deletions(-) diff --git a/src/main/scala/xyz/driver/pdsuicommon/db/SlickPostgresQueryBuilder.scala b/src/main/scala/xyz/driver/pdsuicommon/db/SlickPostgresQueryBuilder.scala index 66434f0..3ff1688 100644 --- a/src/main/scala/xyz/driver/pdsuicommon/db/SlickPostgresQueryBuilder.scala +++ b/src/main/scala/xyz/driver/pdsuicommon/db/SlickPostgresQueryBuilder.scala @@ -5,11 +5,12 @@ import java.time.{LocalDateTime, ZoneOffset} import slick.driver.JdbcProfile import slick.jdbc.GetResult import xyz.driver.core.database.SlickDal +import xyz.driver.pdsuicommon.logging._ import scala.collection.breakOut import scala.concurrent.ExecutionContext -object SlickPostgresQueryBuilder { +object SlickPostgresQueryBuilder extends PhiLogging { import xyz.driver.pdsuicommon.db.SlickQueryBuilder._ @@ -36,7 +37,7 @@ object SlickPostgresQueryBuilder { profile: JdbcProfile, getResult: GetResult[T], ec: ExecutionContext): SlickPostgresQueryBuilder[T] = { - apply(tableName, SlickQueryBuilderParameters.AllFields, lastUpdateFieldName, nullableFields, links) + apply[T](tableName, SlickQueryBuilderParameters.AllFields, lastUpdateFieldName, nullableFields, links) } def apply[T](tableName: String, @@ -50,6 +51,7 @@ object SlickPostgresQueryBuilder { val runner: Runner[T] = { parameters => val sql = parameters.toSql(countQuery = false, fields = fields).as[T] + logger.debug(phi"${Unsafe(sql)}") sqlContext.execute(sql) } @@ -62,6 +64,7 @@ object SlickPostgresQueryBuilder { (count, lastUpdate) }) val sql = parameters.toSql(countQuery = true).as[(Int, Option[LocalDateTime])] + logger.debug(phi"${Unsafe(sql)}") sqlContext.execute(sql).map(_.head) } diff --git a/src/main/scala/xyz/driver/pdsuicommon/db/SlickQueryBuilder.scala b/src/main/scala/xyz/driver/pdsuicommon/db/SlickQueryBuilder.scala index e45ff87..2eb8b9b 100644 --- a/src/main/scala/xyz/driver/pdsuicommon/db/SlickQueryBuilder.scala +++ b/src/main/scala/xyz/driver/pdsuicommon/db/SlickQueryBuilder.scala @@ -110,25 +110,33 @@ sealed trait SlickQueryBuilderParameters { // Combine links from sorting and filter without duplicates val foreignTableLinks = (filtersTableLinks ++ sortingTableLinks).distinct - foreignTableLinks.foreach { - case SlickTableLink(keyColumnName, foreignTableName, foreignKeyColumnName) => - sql = sql concat sql"""inner join #$foreignTableName - on #$escapedTableName.#$keyColumnName = #$foreignTableName.#$foreignKeyColumnName""" + def fkSql(fkLinksSql: SQLActionBuilder, tableLinks: Seq[SlickTableLink]): SQLActionBuilder = { + if (tableLinks.nonEmpty) { + tableLinks.head match { + case SlickTableLink(keyColumnName, foreignTableName, foreignKeyColumnName) => + fkSql( + fkLinksSql concat sql""" inner join #$foreignTableName + on #$escapedTableName.#$keyColumnName = #$foreignTableName.#$foreignKeyColumnName""", + tableLinks.tail + ) + } + } else fkLinksSql } + val foreignTableLinksSql = fkSql(sql"", foreignTableLinks) - if (where.toString.nonEmpty) { - sql = sql concat sql"where #$where" - } + val whereSql = if (where.toString.nonEmpty) { + sql" where " concat where + } else sql"" - if (orderBy.toString.nonEmpty && !countQuery) { - sql = sql concat sql"order by #$orderBy" - } + val orderSql = if (orderBy.toString.nonEmpty && !countQuery) { + sql" order by" concat orderBy + } else sql"" - if (limitSql.toString.nonEmpty && !countQuery) { - sql = sql concat sql"#$limitSql" - } + val limSql = if (limitSql.toString.nonEmpty && !countQuery) { + limitSql + } else sql"" - sql + sql concat foreignTableLinksSql concat whereSql concat orderSql concat limSql } /** @@ -146,25 +154,25 @@ sealed trait SlickQueryBuilderParameters { def escapeDimension(dimension: SearchFilterExpr.Dimension) = { val tableName = escapedTableName - s"$tableName.$dimension.name" + s"$tableName.${dimension.name}" } def filterToSqlMultiple(operands: Seq[SearchFilterExpr]) = operands.collect { case x if !SearchFilterExpr.isEmpty(x) => filterToSql(escapedTableName, x) } - def multipleSqlToAction(op: String, conditions: Seq[SQLActionBuilder]): SQLActionBuilder = { - var first = true - var filterSql = sql"(" - for (condition <- conditions) { + def multipleSqlToAction(first: Boolean, + op: String, + conditions: Seq[SQLActionBuilder], + sql: SQLActionBuilder): SQLActionBuilder = { + if (conditions.nonEmpty) { + val condition = conditions.head if (first) { - filterSql = filterSql concat condition - first = false + multipleSqlToAction(false, op, conditions.tail, condition) } else { - filterSql = filterSql concat sql" #$op " concat condition + multipleSqlToAction(false, op, conditions.tail, sql concat sql" #${op} " concat condition) } - } - filterSql concat sql")" + } else sql } filter match { @@ -188,7 +196,7 @@ sealed trait SlickQueryBuilderParameters { // So, to handle NotEq for nullable fields we need to use more complex SQL expression. // http://dev.mysql.com/doc/refman/5.7/en/working-with-null.html val escapedColumn = escapeDimension(dimension) - sql"(#$escapedColumn is null or #$escapedColumn != #$value)" + sql"(#${escapedColumn} is null or #${escapedColumn} != ${value.toString})" case Atom.Binary(dimension, op, value) => val operator = op match { @@ -200,7 +208,7 @@ sealed trait SlickQueryBuilderParameters { case Lt => sql"<" case LtEq => sql"<=" } - sql"#${escapeDimension(dimension)}" concat operator concat sql"#$value" + sql"#${escapeDimension(dimension)}" concat operator concat sql"""${value.toString}""" case Atom.NAry(dimension, op, values) => val sqlOp = op match { @@ -209,15 +217,17 @@ sealed trait SlickQueryBuilderParameters { } val formattedValues = if (values.nonEmpty) { - sql"#$values" + sql"${values.mkString(",")}" } else sql"NULL" sql"#${escapeDimension(dimension)}" concat sqlOp concat formattedValues case Intersection(operands) => - multipleSqlToAction("and", filterToSqlMultiple(operands)) + val filter = multipleSqlToAction(true, "and", filterToSqlMultiple(operands), sql"") + sql"(" concat filter concat sql")" case Union(operands) => - multipleSqlToAction("or", filterToSqlMultiple(operands)) + val filter = multipleSqlToAction(true, "or", filterToSqlMultiple(operands), sql"") + sql"(" concat filter concat sql")" } } @@ -234,10 +244,10 @@ sealed trait SlickQueryBuilderParameters { val sortingTableName = optSortingTableName.getOrElse(escapedMainTableName) val fullName = s"$sortingTableName.$field" - sql"#$fullName #${orderToSql(order)}" + sql"#$fullName ${orderToSql(order)}" case Sequential(xs) => - sql"#${xs.map(sortingToSql(escapedMainTableName, _)).mkString(", ")}" + sql"${xs.map(sortingToSql(escapedMainTableName, _)).mkString(", ")}" } } @@ -268,7 +278,7 @@ final case class SlickPostgresQueryBuilderParameters(tableData: SlickQueryBuilde import profile.api._ pagination.map { pagination => val startFrom = (pagination.pageNumber - 1) * pagination.pageSize - sql"limit #${pagination.pageSize} OFFSET #$startFrom" + sql"limit ${pagination.pageSize} OFFSET $startFrom" } getOrElse (sql"") } @@ -289,7 +299,7 @@ final case class SlickMysqlQueryBuilderParameters(tableData: SlickQueryBuilder.T pagination .map { pagination => val startFrom = (pagination.pageNumber - 1) * pagination.pageSize - sql"limit #$startFrom, #${pagination.pageSize}" + sql"limit $startFrom, ${pagination.pageSize}" } .getOrElse(sql"") } -- cgit v1.2.3 From d5ecec043a3d70dd09bda8a79fcd188f411b47df Mon Sep 17 00:00:00 2001 From: Kseniya Tomskikh Date: Mon, 21 Aug 2017 15:53:05 +0700 Subject: Fixed builder errors --- .../pdsuicommon/db/SlickPostgresQueryBuilder.scala | 18 +++++-- .../driver/pdsuicommon/db/SlickQueryBuilder.scala | 62 ++++++++++++---------- 2 files changed, 48 insertions(+), 32 deletions(-) diff --git a/src/main/scala/xyz/driver/pdsuicommon/db/SlickPostgresQueryBuilder.scala b/src/main/scala/xyz/driver/pdsuicommon/db/SlickPostgresQueryBuilder.scala index 3ff1688..f882441 100644 --- a/src/main/scala/xyz/driver/pdsuicommon/db/SlickPostgresQueryBuilder.scala +++ b/src/main/scala/xyz/driver/pdsuicommon/db/SlickPostgresQueryBuilder.scala @@ -14,7 +14,8 @@ object SlickPostgresQueryBuilder extends PhiLogging { import xyz.driver.pdsuicommon.db.SlickQueryBuilder._ - def apply[T](tableName: String, + def apply[T](databaseName: String, + tableName: String, lastUpdateFieldName: Option[String], nullableFields: Set[String], links: Set[SlickTableLink], @@ -24,23 +25,31 @@ object SlickPostgresQueryBuilder extends PhiLogging { getResult: GetResult[T], ec: ExecutionContext): SlickPostgresQueryBuilder[T] = { val parameters = SlickPostgresQueryBuilderParameters( + databaseName = databaseName, tableData = TableData(tableName, lastUpdateFieldName, nullableFields), links = links.map(x => x.foreignTableName -> x)(breakOut) ) new SlickPostgresQueryBuilder[T](parameters)(runner, countRunner) } - def apply[T](tableName: String, + def apply[T](databaseName: String, + tableName: String, lastUpdateFieldName: Option[String], nullableFields: Set[String], links: Set[SlickTableLink])(implicit sqlContext: SlickDal, profile: JdbcProfile, getResult: GetResult[T], ec: ExecutionContext): SlickPostgresQueryBuilder[T] = { - apply[T](tableName, SlickQueryBuilderParameters.AllFields, lastUpdateFieldName, nullableFields, links) + apply[T](databaseName, + tableName, + SlickQueryBuilderParameters.AllFields, + lastUpdateFieldName, + nullableFields, + links) } - def apply[T](tableName: String, + def apply[T](databaseName: String, + tableName: String, fields: Set[String], lastUpdateFieldName: Option[String], nullableFields: Set[String], @@ -69,6 +78,7 @@ object SlickPostgresQueryBuilder extends PhiLogging { } apply[T]( + databaseName = databaseName, tableName = tableName, lastUpdateFieldName = lastUpdateFieldName, nullableFields = nullableFields, diff --git a/src/main/scala/xyz/driver/pdsuicommon/db/SlickQueryBuilder.scala b/src/main/scala/xyz/driver/pdsuicommon/db/SlickQueryBuilder.scala index 2eb8b9b..79cb114 100644 --- a/src/main/scala/xyz/driver/pdsuicommon/db/SlickQueryBuilder.scala +++ b/src/main/scala/xyz/driver/pdsuicommon/db/SlickQueryBuilder.scala @@ -50,12 +50,15 @@ object SlickQueryBuilderParameters { sealed trait SlickQueryBuilderParameters { import SlickQueryBuilder._ + def databaseName: String def tableData: SlickQueryBuilder.TableData def links: Map[String, SlickTableLink] def filter: SearchFilterExpr def sorting: Sorting def pagination: Option[Pagination] + def qs: String + def findLink(tableName: String): SlickTableLink = links.get(tableName) match { case None => throw new IllegalArgumentException(s"Cannot find a link for `$tableName`") case Some(link) => link @@ -67,20 +70,20 @@ sealed trait SlickQueryBuilderParameters { def toSql(countQuery: Boolean, fields: Set[String])(implicit profile: JdbcProfile): SQLActionBuilder = { import profile.api._ - val escapedTableName = tableData.tableName + val escapedTableName = s"""$qs$databaseName$qs.$qs${tableData.tableName}$qs""" val fieldsSql: String = if (countQuery) { val suffix: String = tableData.lastUpdateFieldName match { - case Some(lastUpdateField) => s", max($escapedTableName.$lastUpdateField)" + case Some(lastUpdateField) => s", max($escapedTableName.$qs$lastUpdateField$qs)" case None => "" } - "count(*)" + suffix + s"count(*) $suffix" } else { if (fields == SlickQueryBuilderParameters.AllFields) { s"$escapedTableName.*" } else { fields .map { field => - s"$escapedTableName.$field" + s"$escapedTableName.$qs$field$qs" } .mkString(", ") } @@ -90,7 +93,7 @@ sealed trait SlickQueryBuilderParameters { val limitSql = limitToSql() - val sql = sql"select #$fieldsSql from #$escapedTableName" + val sql = sql"""select #$fieldsSql from #$escapedTableName""" val filtersTableLinks: Seq[SlickTableLink] = { import SearchFilterExpr._ @@ -114,26 +117,25 @@ sealed trait SlickQueryBuilderParameters { if (tableLinks.nonEmpty) { tableLinks.head match { case SlickTableLink(keyColumnName, foreignTableName, foreignKeyColumnName) => - fkSql( - fkLinksSql concat sql""" inner join #$foreignTableName - on #$escapedTableName.#$keyColumnName = #$foreignTableName.#$foreignKeyColumnName""", - tableLinks.tail - ) + val escapedForeignTableName = s"$qs$databaseName$qs.$qs$foreignTableName$qs" + val join = sql""" inner join #$escapedForeignTableName + on #$escapedTableName.#$qs#$keyColumnName#$qs=#$escapedForeignTableName.#$qs#$foreignKeyColumnName#$qs""" + fkSql(fkLinksSql concat join, tableLinks.tail) } } else fkLinksSql } val foreignTableLinksSql = fkSql(sql"", foreignTableLinks) - val whereSql = if (where.toString.nonEmpty) { + val whereSql = if (where.queryParts.size > 1) { sql" where " concat where } else sql"" - val orderSql = if (orderBy.toString.nonEmpty && !countQuery) { - sql" order by" concat orderBy + val orderSql = if (orderBy.nonEmpty && !countQuery) { + sql" order by #$orderBy" } else sql"" - val limSql = if (limitSql.toString.nonEmpty && !countQuery) { - limitSql + val limSql = if (limitSql.queryParts.size > 1 && !countQuery) { + sql" " concat limitSql } else sql"" sql concat foreignTableLinksSql concat whereSql concat orderSql concat limSql @@ -153,8 +155,7 @@ sealed trait SlickQueryBuilderParameters { def isNull(string: AnyRef) = Option(string).isEmpty || string.toString.toLowerCase == "null" def escapeDimension(dimension: SearchFilterExpr.Dimension) = { - val tableName = escapedTableName - s"$tableName.${dimension.name}" + s"$escapedTableName.$qs${dimension.name}$qs" } def filterToSqlMultiple(operands: Seq[SearchFilterExpr]) = operands.collect { @@ -236,18 +237,17 @@ sealed trait SlickQueryBuilderParameters { /** * @param escapedMainTableName Should be escaped */ - protected def sortingToSql(escapedMainTableName: String, sorting: Sorting)( - implicit profile: JdbcProfile): SQLActionBuilder = { - import profile.api._ + protected def sortingToSql(escapedMainTableName: String, sorting: Sorting)(implicit profile: JdbcProfile): String = { sorting match { case Dimension(optSortingTableName, field, order) => - val sortingTableName = optSortingTableName.getOrElse(escapedMainTableName) - val fullName = s"$sortingTableName.$field" + val sortingTableName = + optSortingTableName.map(x => s"$qs$databaseName$qs.$qs$x$qs").getOrElse(escapedMainTableName) + val fullName = s"$sortingTableName.$qs$field$qs" - sql"#$fullName ${orderToSql(order)}" + s"$fullName ${orderToSql(order)}" case Sequential(xs) => - sql"${xs.map(sortingToSql(escapedMainTableName, _)).mkString(", ")}" + xs.map(sortingToSql(escapedMainTableName, _)).mkString(", ") } } @@ -267,7 +267,8 @@ sealed trait SlickQueryBuilderParameters { } -final case class SlickPostgresQueryBuilderParameters(tableData: SlickQueryBuilder.TableData, +final case class SlickPostgresQueryBuilderParameters(databaseName: String, + tableData: SlickQueryBuilder.TableData, links: Map[String, SlickTableLink] = Map.empty, filter: SearchFilterExpr = SearchFilterExpr.Empty, sorting: Sorting = Sorting.Empty, @@ -278,16 +279,19 @@ final case class SlickPostgresQueryBuilderParameters(tableData: SlickQueryBuilde import profile.api._ pagination.map { pagination => val startFrom = (pagination.pageNumber - 1) * pagination.pageSize - sql"limit ${pagination.pageSize} OFFSET $startFrom" + sql"limit #${pagination.pageSize} OFFSET #$startFrom" } getOrElse (sql"") } + val qs = """"""" + } /** * @param links Links to another tables grouped by foreignTableName */ -final case class SlickMysqlQueryBuilderParameters(tableData: SlickQueryBuilder.TableData, +final case class SlickMysqlQueryBuilderParameters(databaseName: String, + tableData: SlickQueryBuilder.TableData, links: Map[String, SlickTableLink] = Map.empty, filter: SearchFilterExpr = SearchFilterExpr.Empty, sorting: Sorting = Sorting.Empty, @@ -299,11 +303,13 @@ final case class SlickMysqlQueryBuilderParameters(tableData: SlickQueryBuilder.T pagination .map { pagination => val startFrom = (pagination.pageNumber - 1) * pagination.pageSize - sql"limit $startFrom, ${pagination.pageSize}" + sql"limit #$startFrom, #${pagination.pageSize}" } .getOrElse(sql"") } + val qs = """`""" + } abstract class SlickQueryBuilder[T](val parameters: SlickQueryBuilderParameters)( -- cgit v1.2.3