You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@nlpcraft.apache.org by se...@apache.org on 2020/05/10 16:50:18 UTC
[incubator-nlpcraft] 01/02: Licenses list updated.
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
commit 0698f9f6a6878a0d3d4c00fae814cfff13afc1be
Author: Sergey Kamov <se...@apache.org>
AuthorDate: Sun May 10 11:40:46 2020 +0300
Licenses list updated.
---
.../apache/nlpcraft/examples/sql/SqlModel.scala | 142 ++++++++++++++++-----
.../nlpcraft/examples/sql/db/SqlBuilder.scala | 17 +++
.../nlpcraft/examples/sql/db/sample_questions.txt | 13 ++
.../apache/nlpcraft/examples/sql/sql_model.yaml | 14 +-
4 files changed, 142 insertions(+), 44 deletions(-)
diff --git a/src/main/scala/org/apache/nlpcraft/examples/sql/SqlModel.scala b/src/main/scala/org/apache/nlpcraft/examples/sql/SqlModel.scala
index 1fd7837..5c79878 100644
--- a/src/main/scala/org/apache/nlpcraft/examples/sql/SqlModel.scala
+++ b/src/main/scala/org/apache/nlpcraft/examples/sql/SqlModel.scala
@@ -52,9 +52,6 @@ class SqlModel extends NCModelFileAdapter("org/apache/nlpcraft/examples/sql/sql_
GSON.toJson(m)
}
- private def findColumnToken(tok: NCToken): NCToken =
- findAnyColumnTokenOpt(tok).getOrElse(throw new RuntimeException(s"No columns found for token: $tok"))
-
/**
* Complex element contains 2 tokens: column + date ot numeric condition.
*
@@ -68,7 +65,9 @@ class SqlModel extends NCModelFileAdapter("org/apache/nlpcraft/examples/sql/sql_
require(parts.size == 2)
val condTok = parts.find(_.getId == condTokId).get
- val colTok = findColumnToken(parts.filter(_ != condTok).head)
+
+ val pt = parts.filter(_ != condTok).head
+ val colTok = findAnyColumnTokenOpt(pt).getOrElse(throw new RuntimeException(s"No columns found for token: $pt"))
Condition(colTok, condTok)
}
@@ -146,30 +145,17 @@ class SqlModel extends NCModelFileAdapter("org/apache/nlpcraft/examples/sql/sql_
groupBy { case (col, _) ⇒ col }.
map { case (col, seq) ⇒ SqlInCondition(col, seq.map { case (_, value) ⇒ value})}.toSeq
- @NCIntent(
- "intent=commonReport conv=true " +
- "term(tabs)={groups @@ 'table'}[0,7] " +
- "term(cols)={id == 'col:date' || id == 'col:num' || id == 'col:varchar'}[0,7] " +
- "term(condNums)={id == 'condition:num'}[0,7] " +
- "term(condVals)={id == 'condition:value'}[0,7] " +
- "term(condDates)={id == 'condition:date'}[0,7] " +
- "term(condFreeDate)={id == 'nlpcraft:date'}? " +
- "term(sort)={id == 'nlpcraft:sort'}? " +
- "term(limit)={id == 'nlpcraft:limit'}?"
- )
- def onCommonReport(
- ctx: NCIntentMatch,
- @NCIntentTerm("tabs") tabs: Seq[NCToken],
- @NCIntentTerm("cols") cols: Seq[NCToken],
- @NCIntentTerm("condNums") condNums: Seq[NCToken],
- @NCIntentTerm("condVals") condVals: Seq[NCToken],
- @NCIntentTerm("condDates") condDates: Seq[NCToken],
- @NCIntentTerm("condFreeDate") freeDateOpt: Option[NCToken],
- @NCIntentTerm("sort") sortTokOpt: Option[NCToken],
- @NCIntentTerm("limit") limitTokOpt: Option[NCToken]
+ private def select0(
+ ext: NCSqlExtractor,
+ tabs: Seq[NCToken],
+ cols: Seq[NCToken],
+ condNums: Seq[NCToken],
+ condVals: Seq[NCToken],
+ condDates: Seq[NCToken],
+ freeDateOpt: Option[NCToken],
+ limitTokOpt: Option[NCToken],
+ sorts: Seq[NCSqlSort]
): NCResult = {
- val ext: NCSqlExtractor = NCSqlExtractorBuilder.build(SCHEMA, ctx.getVariant)
-
var query: SqlQuery = null
try {
@@ -188,12 +174,7 @@ class SqlModel extends NCModelFileAdapter("org/apache/nlpcraft/examples/sql/sql_
extractNumConditions(ext, h.column, h.condition)
): _*
).
- withSorts((
- sortTokOpt match {
- case Some(sortTok) ⇒ ext.extractSort(sortTok).asScala
- case None ⇒ Seq.empty
- }
- ): _*).
+ withSorts(sorts: _*).
withLimit(limitTokOpt.flatMap(limitTok ⇒ Some(ext.extractLimit(limitTok))).orNull).
withFreeDateRange(freeDateOpt.flatMap(freeDate ⇒ Some(ext.extractDateRange(freeDate))).orNull).
build()
@@ -202,7 +183,7 @@ class SqlModel extends NCModelFileAdapter("org/apache/nlpcraft/examples/sql/sql_
}
catch {
case e: Exception ⇒
- System.err.println(if (query == null) "Query cannot be prepared" else "Query execution error")
+ System.err.println(if (query == null) "Query cannot be prepared" else "Query execution error")
e.printStackTrace()
@@ -210,6 +191,99 @@ class SqlModel extends NCModelFileAdapter("org/apache/nlpcraft/examples/sql/sql_
}
}
+
+ @NCIntent(
+ "intent=commonReport conv=true " +
+ "term(tabs)={groups @@ 'table'}[0,7] " +
+ "term(cols)={id == 'col:date' || id == 'col:num' || id == 'col:varchar'}[0,7] " +
+ "term(condNums)={id == 'condition:num'}[0,7] " +
+ "term(condVals)={id == 'condition:value'}[0,7] " +
+ "term(condDates)={id == 'condition:date'}[0,7] " +
+ "term(condFreeDate)={id == 'nlpcraft:date'}? " +
+ "term(sort)={id == 'nlpcraft:sort'}? " +
+ "term(limit)={id == 'nlpcraft:limit'}?"
+ )
+ def onCommonReport(
+ ctx: NCIntentMatch,
+ @NCIntentTerm("tabs") tabs: Seq[NCToken],
+ @NCIntentTerm("cols") cols: Seq[NCToken],
+ @NCIntentTerm("condNums") condNums: Seq[NCToken],
+ @NCIntentTerm("condVals") condVals: Seq[NCToken],
+ @NCIntentTerm("condDates") condDates: Seq[NCToken],
+ @NCIntentTerm("condFreeDate") freeDateOpt: Option[NCToken],
+ @NCIntentTerm("sort") sortTokOpt: Option[NCToken],
+ @NCIntentTerm("limit") limitTokOpt: Option[NCToken]
+ ): NCResult = {
+ val ext: NCSqlExtractor = NCSqlExtractorBuilder.build(SCHEMA, ctx.getVariant)
+
+ select0(
+ NCSqlExtractorBuilder.build(SCHEMA, ctx.getVariant),
+ tabs,
+ cols,
+ condNums,
+ condVals,
+ condDates,
+ freeDateOpt,
+ limitTokOpt,
+ sortTokOpt match {
+ case Some(sortTok) ⇒ ext.extractSort(sortTok).asScala
+ case None ⇒ Seq.empty
+ }
+ )
+ }
+
+ @NCIntent(
+ "intent=customSortReport conv=true " +
+ "term(sort)={id == 'sort:best' || id == 'sort:worst'} " +
+ "term(tabs)={groups @@ 'table'}[0,7] " +
+ "term(cols)={id == 'col:date' || id == 'col:num' || id == 'col:varchar'}[0,7] " +
+ "term(condNums)={id == 'condition:num'}[0,7] " +
+ "term(condVals)={id == 'condition:value'}[0,7] " +
+ "term(condDates)={id == 'condition:date'}[0,7] " +
+ "term(condFreeDate)={id == 'nlpcraft:date'}? " +
+ "term(limit)={id == 'nlpcraft:limit'}?"
+ )
+ def onCustomSortReport(
+ ctx: NCIntentMatch,
+ @NCIntentTerm("sort") sortTok: NCToken,
+ @NCIntentTerm("tabs") tabs: Seq[NCToken],
+ @NCIntentTerm("cols") cols: Seq[NCToken],
+ @NCIntentTerm("condNums") condNums: Seq[NCToken],
+ @NCIntentTerm("condVals") condVals: Seq[NCToken],
+ @NCIntentTerm("condDates") condDates: Seq[NCToken],
+ @NCIntentTerm("condFreeDate") freeDateOpt: Option[NCToken],
+ @NCIntentTerm("limit") limitTokOpt: Option[NCToken]
+ ): NCResult = {
+ val ordersFreightColSort: NCSqlSort =
+ new NCSqlSort {
+ override def getColumn: NCSqlColumn = SCHEMA.getTables.asScala.find(_.getTable == "orders").
+ getOrElse(throw new RuntimeException(s"Table `orders` not found")).
+ getColumns.asScala.find(_.getColumn == "freight").
+ getOrElse(throw new RuntimeException(s"Column `orders.freight` not found"))
+ override def isAscending: Boolean =
+ sortTok.getId match {
+ case "sort:best" ⇒ false
+ case "sort:worst" ⇒ true
+
+ case _ ⇒ throw new AssertionError(s"Unexpected ID: ${sortTok.getId}")
+ }
+
+ if (sortTok.getId == "sort:best") false else true
+ }
+
+ select0(
+ NCSqlExtractorBuilder.build(SCHEMA, ctx.getVariant),
+ tabs,
+ cols,
+ condNums,
+ condVals,
+ condDates,
+ freeDateOpt,
+ limitTokOpt,
+ Seq(ordersFreightColSort)
+ )
+ }
+
override def onMatchedIntent(m: NCIntentMatch): Boolean = {
val toks = m.getVariant.getMatchedTokens.asScala
val intentConvToks = m.getIntentTokens.asScala.flatMap(_.asScala) -- toks
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 09a3849..46970c3 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
@@ -85,6 +85,16 @@ case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
private def sql(tabs: Seq[NCSqlTable]): String = {
val names = tabs.map(_.getTable)
+ println("names-" + names.mkString("|"))
+ println("joins=\n" + schemaJoins.map(p ⇒ s"${p.getFromTable} - ${p.getToTable}").mkString("\n"))
+ println()
+
+ val refs = names.
+ flatMap(t ⇒ schemaJoins.filter(j ⇒ j.getFromTable == t && names.contains(j.getToTable))).
+ sortBy(_.getFromTable).
+ zipWithIndex.map { case (join, idx) ⇒
+ }
+
names.size match {
case 0 ⇒ throw new AssertionError(s"Unexpected empty tables")
case 1 ⇒ names.head
@@ -103,6 +113,8 @@ case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
val fromTab = join.getFromTable
val toTab = join.getToTable
+ println(s"fromTab=$fromTab, toTab=$toTab")
+
val onCondition = fromCols.zip(toCols).
map { case (fromCol, toCol) ⇒ s"$fromTab.$fromCol = $toTab.$toCol" }.mkString(" AND ")
@@ -315,7 +327,12 @@ case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
condsNorm = condsNorm.distinct
sortsNorm = sortsNorm.distinct
+ println("tabsNorm="+tabsNorm.map(_.getTable).mkString("|"))
+
val extTabs = extendTables(tabsNorm)
+
+ println("extTabs="+extTabs.map(_.getTable).mkString("|"))
+
val extCols = extendColumns(colsNorm, extTabs, freeDateColOpt)
val (extConds, extParams) = extendConditions(condsNorm, extTabs, tabsNorm, freeDateColOpt)
diff --git a/src/main/scala/org/apache/nlpcraft/examples/sql/db/sample_questions.txt b/src/main/scala/org/apache/nlpcraft/examples/sql/db/sample_questions.txt
index 69983cc..cb807be 100644
--- a/src/main/scala/org/apache/nlpcraft/examples/sql/db/sample_questions.txt
+++ b/src/main/scala/org/apache/nlpcraft/examples/sql/db/sample_questions.txt
@@ -1,5 +1,18 @@
What are the top orders for the last 2 weeks sorted by order quantity?
What are the best/least performing products/categories/employee/territory/region for the last quarter?
+ What are the best performing products for the last quarter?
+ What are the best performing categories for the last quarter?
+ What are the best performing employee for the last quarter?
+ What are the best performing territory for the last quarter?
+ What are the best performing region for the last quarter?
+
+ What are the least performing products for the last quarter?
+ What are the least performing categories for the last quarter?
+ What are the least performing employee for the last quarter?
+ What are the least performing territory for the last quarter?
+ What are the least performing region for the last quarter?
+
What are the top 5 best/worst customers in CA for the last 90 days?
+
What are the busiest shipper/supplier in CA for the last 90 days?
What is the average/maximum/minimum unit price/discount/quantity in CA for the last 90 days?
\ No newline at end of file
diff --git a/src/main/scala/org/apache/nlpcraft/examples/sql/sql_model.yaml b/src/main/scala/org/apache/nlpcraft/examples/sql/sql_model.yaml
index 26e8ae1..f0daadc 100644
--- a/src/main/scala/org/apache/nlpcraft/examples/sql/sql_model.yaml
+++ b/src/main/scala/org/apache/nlpcraft/examples/sql/sql_model.yaml
@@ -1463,22 +1463,16 @@ elements:
- "condition"
synonyms:
- "^^id == 'col:date'^^ ^^id == 'nlpcraft:date'^^ "
-- id: "desc:best"
+- id: "sort:best"
groups:
- - "description"
+ - "Sort criteria"
synonyms:
- "best {performing|*}"
-- id: "desc:worst"
+- id: "sort:worst"
groups:
- - "description"
+ - "Sort criteria"
synonyms:
- "worst {performing|*}"
-- id: "desc:busiest"
- groups:
- - "description"
- synonyms:
- - "{busiest|busiest}"
-
enabledBuiltInTokens:
- "nlpcraft:date"
- "nlpcraft:num"