This is an automated email from the ASF dual-hosted git repository.
sergeykamov pushed a commit to branch NLPCRAFT-30
in repository https://gitbox.apache.org/repos/asf/incubator-nlpcraft.git
The following commit(s) were added to refs/heads/NLPCRAFT-30 by this push:
new fb807ae WIP.
fb807ae is described below
commit fb807aed7786c8df718158c9d0d19236f3dfef7a
Author: Sergey Kamov <[email protected]>
AuthorDate: Wed Apr 8 16:55:50 2020 +0300
WIP.
---
.../nlpcraft/examples/sql/db/SqlBuilder.scala | 45 ++++++-
.../nlpcraft/model/tools/sqlgen/NCSqlSort.java | 2 +-
.../model/tools/sqlgen/impl/NCSqlBeans.scala | 32 ++++-
.../tools/sqlgen/impl/NCSqlExtractorImpl.scala | 144 +++++++++++----------
.../tools/sqlgen/impl/NCSqlSchemaBuilderImpl.scala | 87 +++++++------
5 files changed, 186 insertions(+), 124 deletions(-)
diff --git
a/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlBuilder.scala
b/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlBuilder.scala
index 44d32d8..1fea753 100644
--- a/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlBuilder.scala
+++ b/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlBuilder.scala
@@ -72,7 +72,23 @@ case class SqlBuilder(schema: NCSqlSchema) extends
LazyLogging {
private def sql(clause: NCSqlTable): String = clause.getTable
private def sql(clause: NCSqlColumn): String =
s"${clause.getTable}.${clause.getColumn}"
- private def sql(clause: NCSqlSort): String = s"${sql(clause.getColumn)}
${if (clause.isAscending) "ASC" else "DESC"}"
+ private def sql(clause: NCSqlSort): String = {
+ val cols: Seq[NCSqlColumn] =
+ if (clause.getBy.isEmpty) {
+ require(!clause.getSubj.isEmpty)
+
+ clause.getSubj.asScala.flatMap(tab ⇒ {
+ val cols = tab.getColumns.asScala
+ val pkCols = cols.filter(_.isPk)
+
+ if (pkCols.nonEmpty) pkCols else cols.take(1)
+ })
+ }
+ else
+ clause.getBy.asScala
+
+ cols.map(col ⇒ s"${sql(col)} ${if (clause.isAscending) "ASC" else
"DESC"}").mkString(", ")
+ }
// TODO: implement based on join type.
private def sql(clause: NCSqlJoin): String =
@@ -125,12 +141,20 @@ case class SqlBuilder(schema: NCSqlSchema) extends
LazyLogging {
sorts.size match {
case 0 ⇒
limit.flatMap(l ⇒ if (cols.contains(l.getColumn))
Some(Seq(limit2Sort(l))) else None).
- getOrElse({
+ getOrElse(
tables.flatMap(_.getDefaultSort.asScala.
- // 'Sort' can contains columns from select list
only (some databases restriction)
- flatMap(sort ⇒ if (cols.contains(sort.getColumn))
Some(sort) else None)
+ // 'Sort' can contain columns from select list
only (some databases restriction)
+ flatMap(sort ⇒
+ if (
+
sort.getSubj.asScala.toSet.subsetOf(tables.toSet) &&
+ (sort.getBy.isEmpty ||
sort.getBy.asScala.toSet.subsetOf(cols.toSet))
+ )
+ Some(sort)
+ else
+ None
+ )
)
- }).distinct
+ ).distinct
case _ ⇒ sorts.distinct
}
@@ -159,7 +183,11 @@ case class SqlBuilder(schema: NCSqlSchema) extends
LazyLogging {
}
}
- private def limit2Sort(l: NCSqlLimit): NCSqlSort =
NCSqlSortImpl(l.getColumn, l.isAscending)
+ private def limit2Sort(l: NCSqlLimit): NCSqlSort = NCSqlSortImpl(
+ Seq(schemaTabsByNames(l.getColumn.getTable)),
+ Seq(l.getColumn),
+ l.isAscending
+ )
private def sort(cols2Sort: Seq[NCSqlColumn], cols: Seq[NCSqlColumn],
tabs: Seq[NCSqlTable]): Seq[NCSqlColumn] =
cols2Sort.sortBy(col ⇒
@@ -277,7 +305,10 @@ case class SqlBuilder(schema: NCSqlSchema) extends
LazyLogging {
colsNorm.foreach(col ⇒ tabsNorm += schemaTabsByNames(col.getTable))
condsNorm.foreach(cond ⇒ { tabsNorm +=
schemaTabsByNames(cond.column.getTable); colsNorm += cond.column })
- sortsNorm.foreach(sort ⇒ { tabsNorm +=
schemaTabsByNames(sort.getColumn.getTable); colsNorm += sort.getColumn })
+ sortsNorm.foreach(sort ⇒ {
+ tabsNorm ++= sort.getSubj.asScala ++ sort.getBy.asScala.map(col ⇒
schemaTabsByNames(col.getTable))
+ colsNorm ++= sort.getBy.asScala
+ })
val freeDateColOpt = findDateColumn(tabsNorm)
diff --git
a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/NCSqlSort.java
b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/NCSqlSort.java
index dc92395..74c0a4e 100644
--- a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/NCSqlSort.java
+++ b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/NCSqlSort.java
@@ -31,7 +31,7 @@ public interface NCSqlSort {
/**
* TODO:
*/
- List<NCSqlColumn> getSubj();
+ List<NCSqlTable> getSubj();
/**
* TODO:
diff --git
a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlBeans.scala
b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlBeans.scala
index adc77e4..ecd34a4 100644
---
a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlBeans.scala
+++
b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlBeans.scala
@@ -35,7 +35,13 @@ import scala.compat.java8.OptionConverters._
* @param isPk
* @param isNullable
*/
-case class NCSqlColumnImpl(table: String, column: String, dataType: Int, isPk:
Boolean, isNullable: Boolean) extends NCSqlColumn {
+case class NCSqlColumnImpl(
+ table: String,
+ column: String,
+ dataType: Int,
+ isPk: Boolean,
+ isNullable: Boolean
+) extends NCSqlColumn {
override def getTable: String = table
override def getColumn: String = column
override def getDataType: Int = dataType
@@ -59,7 +65,13 @@ case class NCSqlDateRangeImpl(from: Timestamp, to:
Timestamp) extends NCSqlDateR
* @param toColumns
* @param typ
*/
-case class NCSqlJoinImpl(fromTable: String, toTable: String, fromColumns:
Seq[String], toColumns: Seq[String], typ: NCSqlJoinType) extends NCSqlJoin {
+case class NCSqlJoinImpl(
+ fromTable: String,
+ toTable: String,
+ fromColumns: Seq[String],
+ toColumns: Seq[String],
+ typ: NCSqlJoinType
+) extends NCSqlJoin {
override def getFromTable: String = fromTable
override def getToTable: String = toTable
override def getFromColumns: util.List[String] = fromColumns.asJava
@@ -73,8 +85,8 @@ case class NCSqlJoinImpl(fromTable: String, toTable: String,
fromColumns: Seq[St
* @param by
* @param asc
*/
-case class NCSqlSortImpl(subj: Seq[NCSqlColumn], by: Seq[NCSqlColumn], asc:
Boolean) extends NCSqlSort {
- override def getSubj: util.List[NCSqlColumn] = subj.asJava
+case class NCSqlSortImpl(subj: Seq[NCSqlTable], by: Seq[NCSqlColumn], asc:
Boolean) extends NCSqlSort {
+ override def getSubj: util.List[NCSqlTable] = subj.asJava
override def getBy: util.List[NCSqlColumn] = by.asJava
override def isAscending: Boolean = asc
}
@@ -88,13 +100,23 @@ case class NCSqlSortImpl(subj: Seq[NCSqlColumn], by:
Seq[NCSqlColumn], asc: Bool
* @param extraTables
* @param defaultDate
*/
-case class NCSqlTableImpl(table: String, columns: Seq[NCSqlColumn], sort:
Seq[NCSqlSort], select: Seq[String], extraTables: Seq[String], defaultDate:
Option[NCSqlColumn]) extends NCSqlTable {
+case class NCSqlTableImpl(
+ table: String,
+ columns: Seq[NCSqlColumn],
+ sort: mutable.ArrayBuffer[NCSqlSort] = mutable.ArrayBuffer.empty,
+ select: Seq[String],
+ extraTables: Seq[String],
+ defaultDate: Option[NCSqlColumn]
+) extends NCSqlTable {
override def getTable: String = table
override def getColumns: util.List[NCSqlColumn] = columns.asJava
override def getDefaultSort: util.List[NCSqlSort] = sort.asJava
override def getDefaultSelect: util.List[String] = select.asJava
override def getExtraTables: util.List[String] = extraTables.asJava
override def getDefaultDate: Optional[NCSqlColumn] = defaultDate.asJava
+
+ // TODO: what about another fields for symmetry.
+ def addSort(sort: NCSqlSort): Unit = this.sort += sort
}
/**
diff --git
a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlExtractorImpl.scala
b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlExtractorImpl.scala
index e29648a..299b373 100644
---
a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlExtractorImpl.scala
+++
b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlExtractorImpl.scala
@@ -21,8 +21,8 @@ import java.sql.Timestamp
import java.util
import org.apache.nlpcraft.common.NCException
-import org.apache.nlpcraft.model.tools.sqlgen._
import org.apache.nlpcraft.model._
+import org.apache.nlpcraft.model.tools.sqlgen._
import scala.collection.JavaConverters._
import scala.compat.java8.OptionConverters._
@@ -110,32 +110,80 @@ class NCSqlExtractorImpl(schema: NCSqlSchema, variant:
NCVariant) extends NCSqlE
}
/**
- *
- * @param refTok
- * @return
- */
- private def getReference(refTok: NCToken): Option[NCSqlColumn] = {
- val tok = getLinks(variant, refTok)
-
- findAnyColumnTokenOpt(tok) match {
+ *
+ * @param link
+ * @param typ
+ * @return
+ */
+ private def findColumn(link: NCToken, typ: String): NCSqlColumn =
+ findAnyColumnTokenOpt(link) match {
// If reference is column - sort by column.
- case Some(t) ⇒ Some(extractColumn(t))
+ case Some(t) ⇒ extractColumn(t)
case None ⇒
// If reference is table - sort by any PK column of table.
- findAnyTableTokenOpt(tok) match {
- case Some(tab) ⇒ Some(tab.getColumns.asScala.minBy(col ⇒
if (col.isPk) 0 else 1))
- case None ⇒ None
+ findAnyTableTokenOpt(link) match {
+ case Some(tab) ⇒ tab.getColumns.asScala.minBy(col ⇒ if
(col.isPk) 0 else 1)
+ case None ⇒ throw new NCException(s"Unexpected $typ link:
$link")
}
}
+
+ /**
+ *
+ * @param link
+ * @param typ
+ * @return
+ */
+ private def findTable(link: NCToken, typ: String): NCSqlTable =
+ findAnyTableTokenOpt(link) match {
+ case Some(t) ⇒ t
+ case None ⇒
+ findAnyColumnTokenOpt(link) match {
+ case Some(col) ⇒
findSchemaTable(extractColumn(col).getTable)
+ case None ⇒ throw new NCException(s"Unexpected $typ link:
$link")
+ }
+ }
+
+ /**
+ *
+ * @param variant
+ * @param tok
+ * @return
+ */
+ private def getLinks(
+ variant: NCVariant, tok: NCToken, idxField: String, noteField: String,
canBeEmpty: Boolean
+ ): Seq[NCToken] = {
+ val idxsOpt: Option[util.List[Integer]] =
tok.metaOpt(s"${tok.getId}:$idxField").asScala
+
+ if (idxsOpt.isEmpty && !canBeEmpty)
+ throw new NCException(s"Empty indexes for: $tok")
+
+ idxsOpt.get.asScala.map(idx ⇒ {
+ if (idx < variant.size) {
+ val note: String = tok.metax(s"${tok.getId}:$noteField")
+
+ val link = variant.get(idx)
+
+ if (link.getId != note)
+ throw new NCException(s"Unexpected token with index: $idx,
type: $note")
+
+ link
+ }
+ else
+ throw new NCException(s"Token not found with index: $idx")
+ })
}
/**
*
- * @param refToks
+ * @param tok
+ * @param metaName
+ * @param dflt
* @return
*/
- private def getReferences(refToks: Seq[NCToken]): Seq[NCSqlColumn] = {
- refToks
+ private def getAsc(tok: NCToken, metaName: String, dflt: Boolean): Boolean
= {
+ val ascOpt: Option[Boolean] = tok.meta(metaName)
+
+ ascOpt.getOrElse(dflt)
}
/**
@@ -146,34 +194,18 @@ class NCSqlExtractorImpl(schema: NCSqlSchema, variant:
NCVariant) extends NCSqlE
override def extractLimit(limitTok: NCToken): NCSqlLimit = {
checkTokenId(limitTok, "nlpcraft:limit")
- // Skips indexes to simplify.
- val limit: Double = limitTok.metax("nlpcraft:limit:limit")
-
val links = getLinks(variant, limitTok, "indexes", "note", false)
- val size = links.size
-
- size match {
+ links.size match {
case 1 ⇒
- val link = links.head
-
- val res = findAnyColumnTokenOpt(link) match {
- // If reference is column - sort by column.
- case Some(t) ⇒ extractColumn(t)
- case None ⇒
- // If reference is table - sort by any PK column of
table.
- findAnyTableTokenOpt(link) match {
- case Some(tab) ⇒ tab.getColumns.asScala.minBy(col
⇒ if (col.isPk) 0 else 1)
- case None ⇒ throw new NCException("TODO:")
- }
- }
+ val limit: Double = limitTok.metax("nlpcraft:limit:limit")
NCSqlLimitImpl(
- res,
+ findColumn(links.head, "LIMIT"),
limit.intValue(),
- limitTok.metax("nlpcraft:limit:asc")
+ getAsc(limitTok, "nlpcraft:limit:asc", true)
)
- case _ ⇒ throw new NCException(s"Unexpected LIMIT links count:
$size")
+ case n ⇒ throw new NCException(s"Unexpected LIMIT links count: $n")
}
}
@@ -184,10 +216,13 @@ class NCSqlExtractorImpl(schema: NCSqlSchema, variant:
NCVariant) extends NCSqlE
*/
override def extractSort(sortTok: NCToken): NCSqlSort = {
checkTokenId(sortTok, "nlpcraft:sort")
-
+
NCSqlSortImpl(
- getReference(sortTok).getOrElse(throw new NCException(s"Sort not
found for: $sortTok")),
- sortTok.metax("nlpcraft:sort:asc")
+ getLinks(variant, sortTok, "subjindexes", "subjnotes", false).
+ map(link ⇒ findTable(link, "SORT")),
+ getLinks(variant, sortTok, "byindexes", "bynotes", true).
+ map(link ⇒ findColumn(link, "SORT BY")),
+ getAsc(sortTok, "nlpcraft:sort:asc", true)
)
}
@@ -229,33 +264,4 @@ class NCSqlExtractorImpl(schema: NCSqlSchema, variant:
NCVariant) extends NCSqlE
new Timestamp(tok.metax("nlpcraft:date:to"))
)
}
-
- /**
- *
- * @param variant
- * @param tok
- * @return
- */
- private def getLinks(variant: NCVariant, tok: NCToken, idxField: String,
noteField: String, canBeEmpty: Boolean): Seq[NCToken] = {
- val idxsOpt: Option[util.List[Integer]] =
tok.metaOpt(s"${tok.getId}:$idxField").asScala
-
- if (idxsOpt.isEmpty && !canBeEmpty)
- throw new NCException(s"Empty indexes for: $tok")
-
- idxsOpt.get.asScala.map(idx ⇒ {
- if (idx < variant.size) {
- val note: String = tok.metax(s"${tok.getId}:$noteField")
-
- val link = variant.get(idx)
-
- if (link.getId != note)
- throw new NCException(s"Unexpected token with index: $idx,
type: $note")
-
- link
- }
- else
- throw new NCException(s"Token not found with index: $idx")
- })
-
- }
}
diff --git
a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlSchemaBuilderImpl.scala
b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlSchemaBuilderImpl.scala
index 7899d00..b30a4e1 100644
---
a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlSchemaBuilderImpl.scala
+++
b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlSchemaBuilderImpl.scala
@@ -81,49 +81,14 @@ object NCSqlSchemaBuilderImpl {
val cols = tabCols(tab).toSeq.sortBy(p ⇒ (if (p.isPk) 0
else 1, p.getColumn)).asJava
- val table: NCSqlTable = NCSqlTableImpl(
- tab,
+ val table: NCSqlTableImpl = NCSqlTableImpl(
+ table = tab,
// TODO: columns should be list, but elements are set.
How should we order them?
// TODO: Seems elements should be seq too in model.
- cols.asScala,
- dfltSort.
- map(s ⇒ {
- def error() = throw new NCException(s"Invalid
default sort declaration in: $s")
-
- var pair = s.split("\\.")
-
- val t =
- pair.length match {
- case 1 ⇒
- pair = s.split("#")
-
- // By default, same table name.
- tab
- case 2 ⇒
- val t = pair.head
-
- pair = pair.last.split("#")
-
- t
- case _ ⇒ error()
- }
-
- if (pair.length != 2)
- error()
-
- val col = pair.head
- val asc = pair.last.toLowerCase
-
- if (asc != "asc" && asc != "desc")
- error()
-
- val sort: NCSqlSort =
NCSqlSortImpl(findSchemaColumn(cols, t, col), asc == "asc")
-
- sort
- }),
- dfltSelect,
- extra,
- defDateOpt match {
+ columns = cols.asScala,
+ select = dfltSelect,
+ extraTables = extra,
+ defaultDate = defDateOpt match {
case Some(defDate) ⇒
def error() = throw new NCException(s"Invalid
default date declaration in: $defDate")
@@ -139,7 +104,45 @@ object NCSqlSchemaBuilderImpl {
case None ⇒ None
}
)
-
+
+ dfltSort.
+ foreach(s ⇒ {
+ def error() = throw new NCException(s"Invalid
default sort declaration in: $s")
+
+ var pair = s.split("\\.")
+
+ val t =
+ pair.length match {
+ case 1 ⇒
+ pair = s.split("#")
+
+ // By default, same table name.
+ tab
+ case 2 ⇒
+ val t = pair.head
+
+ pair = pair.last.split("#")
+
+ t
+ case _ ⇒ error()
+ }
+
+ if (pair.length != 2)
+ error()
+
+ val col = pair.head
+ val asc = pair.last.toLowerCase
+
+ if (asc != "asc" && asc != "desc")
+ error()
+
+ table.addSort(NCSqlSortImpl(
+ Seq(table),
+ Seq(findSchemaColumn(cols, t, col)),
+ asc == "asc"
+ ))
+ })
+
table
}).toSeq
