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 6f05508 WIP.
6f05508 is described below
commit 6f055088b80a7346bbfa8b7be686bf64127a07bf
Author: Sergey Kamov <[email protected]>
AuthorDate: Thu Apr 16 22:08:21 2020 +0300
WIP.
---
pom.xml | 14 ++--
.../apache/nlpcraft/examples/sql/SqlModel.scala | 31 +++-----
.../nlpcraft/examples/sql/SqlModelTest.scala | 85 ++++++++++------------
.../nlpcraft/examples/sql/db/SqlBuilder.scala | 69 +++++++-----------
4 files changed, 83 insertions(+), 116 deletions(-)
diff --git a/pom.xml b/pom.xml
index f5df355..d00bfab 100644
--- a/pom.xml
+++ b/pom.xml
@@ -82,6 +82,7 @@
<commons-lang3.ver>3.9</commons-lang3.ver>
<commons.validator.ver>1.6</commons.validator.ver>
<apache.commons.text.ver>1.7</apache.commons.text.ver>
+ <jgrapht.ver>1.4.0</jgrapht.ver>
<jsoup.ver>1.12.1</jsoup.ver>
<slf4j.ver>1.7.26</slf4j.ver>
<scala.logging.ver>3.9.2</scala.logging.ver>
@@ -110,7 +111,6 @@
<jackson.yaml.ver>2.10.1</jackson.yaml.ver>
<apache.math.ver>3.6.1</apache.math.ver>
<apache.common.lang.ver>3.8.1</apache.common.lang.ver>
- <apache.common.graph.ver>0.8.1</apache.common.graph.ver>
<apache.httpcomponents.ver>4.5.7</apache.httpcomponents.ver>
<apache.opennlp.ver>1.9.1</apache.opennlp.ver>
<maven.javadoc.plugin.ver>3.0.0-M1</maven.javadoc.plugin.ver>
@@ -218,6 +218,12 @@
<artifactId>antlr4-runtime</artifactId>
<version>${org.antlr4.ver}</version>
</dependency>
+
+ <dependency>
+ <groupId>org.jgrapht</groupId>
+ <artifactId>jgrapht-core</artifactId>
+ <version>${jgrapht.ver}</version>
+ </dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-text</artifactId>
@@ -249,12 +255,6 @@
<version>${apache.opennlp.ver}</version>
</dependency>
<dependency>
- <groupId>commons-graph</groupId>
- <artifactId>commons-graph</artifactId>
- <version>${apache.common.graph.ver}</version>
- </dependency>
-
- <dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>${gson.ver}</version>
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 ebefb38..4308078 100644
--- a/src/main/scala/org/apache/nlpcraft/examples/sql/SqlModel.scala
+++ b/src/main/scala/org/apache/nlpcraft/examples/sql/SqlModel.scala
@@ -52,17 +52,9 @@ class SqlModel extends
NCModelFileAdapter("org/apache/nlpcraft/examples/sql/sql_
GSON.toJson(m)
}
- private def findColumnToken(tok: NCToken): NCToken = {
- val cols = (Seq(tok) ++ tok.findPartTokens().asScala).
- flatMap(p ⇒ if (p.getGroups.contains("column")) Some(p) else None)
-
- cols.size match {
- case 1 ⇒ cols.head
- case 0 ⇒ throw new Exception(s"No columns found for token: $tok")
- case _ ⇒ throw new Exception("Too many columns found for token:
$tok")
- }
- }
-
+ 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.
*
@@ -81,22 +73,21 @@ class SqlModel extends
NCModelFileAdapter("org/apache/nlpcraft/examples/sql/sql_
Condition(colTok, condTok)
}
- private def getWithGroup(tok: NCToken, group: String): Seq[NCToken] =
- (Seq(tok) ++ tok.findPartTokens().asScala).flatMap(p ⇒ if
(p.getGroups.contains(group)) Some(p) else None)
-
private def findAnyColumnTokenOpt(tok: NCToken): Option[NCToken] = {
- val cols = getWithGroup(tok, "column")
+ val cols =
+ (Seq(tok) ++ tok.findPartTokens().asScala).
+ flatMap(p ⇒ if (p.getGroups.contains("column")) Some(p) else
None)
cols.size match {
case 1 ⇒ Some(cols.head)
case 0 ⇒ None
- case _ ⇒ throw new IllegalArgumentException(s"Too many columns
found for token: $tok")
+ case _ ⇒ throw new RuntimeException(s"Too many columns found for
token: $tok")
}
}
private def findAnyColumnToken(tok: NCToken): NCToken =
- findAnyColumnTokenOpt(tok).getOrElse(throw new
IllegalArgumentException(s"No columns found for token: $tok"))
+ findAnyColumnTokenOpt(tok).getOrElse(throw new RuntimeException(s"No
columns found for token: $tok"))
private def extractNumConditions(ext: NCSqlExtractor, colTok: NCToken,
numTok: NCToken): Seq[SqlSimpleCondition] = {
val col = ext.extractColumn(colTok)
@@ -146,8 +137,8 @@ class SqlModel extends
NCModelFileAdapter("org/apache/nlpcraft/examples/sql/sql_
valToks.size match {
case 1 ⇒ valToks.head
- case 0 ⇒ throw new IllegalStateException(s"Values column
not found for token: $tok")
- case _ ⇒ throw new IllegalStateException(s"Too many values
columns found token: $tok")
+ case 0 ⇒ throw new RuntimeException(s"Values column not
found for token: $tok")
+ case _ ⇒ throw new RuntimeException(s"Too many values
columns found token: $tok")
}
ext.extractColumn(valTok) → valTok.getValue
@@ -208,7 +199,7 @@ class SqlModel extends
NCModelFileAdapter("org/apache/nlpcraft/examples/sql/sql_
withFreeDateRange(freeDateOpt.flatMap(freeDate ⇒
Some(ext.extractDateRange(freeDate))).orNull).
build()
- NCResult.json(toJson(SqlAccess.select(query, true), query.sql,
query.parameters))
+ NCResult.json(toJson(SqlAccess.select(query, logResult = true),
query.sql, query.parameters))
}
catch {
case e: Exception ⇒
diff --git a/src/main/scala/org/apache/nlpcraft/examples/sql/SqlModelTest.scala
b/src/main/scala/org/apache/nlpcraft/examples/sql/SqlModelTest.scala
index 5d85405..bb2178e 100644
--- a/src/main/scala/org/apache/nlpcraft/examples/sql/SqlModelTest.scala
+++ b/src/main/scala/org/apache/nlpcraft/examples/sql/SqlModelTest.scala
@@ -26,7 +26,7 @@ import com.google.gson.Gson
import com.google.gson.reflect.TypeToken
import com.jakewharton.fliptables.FlipTable
import org.apache.nlpcraft.model.tools.test.{NCTestClient, NCTestClientBuilder}
-import org.scalatest.{BeforeAndAfterAll, FlatSpec}
+import org.junit.jupiter.api.{AfterEach, BeforeEach, Test}
import scala.collection.JavaConverters._
import scala.compat.java8.OptionConverters._
@@ -34,7 +34,7 @@ import scala.compat.java8.OptionConverters._
/**
*
*/
-class SqlModelTest extends FlatSpec with BeforeAndAfterAll {
+class SqlModelTest {
private val GSON = new Gson
private val TYPE_RESP = new TypeToken[util.Map[String, Object]]()
{}.getType
private val NORM = Seq("\n", "\r", "\t")
@@ -54,6 +54,18 @@ class SqlModelTest extends FlatSpec with BeforeAndAfterAll {
case class Case(texts: Seq[String], sql: String)
+ @BeforeEach
+ def setUp(): Unit = {
+ client = new
NCTestClientBuilder().newBuilder.setResponseLog(false).build
+
+ client.open("sql.model.id")
+ }
+
+ @AfterEach
+ def tearDown(): Unit =
+ if (client != null)
+ client.close()
+
private def normalize(s: String): String =
NORM.
foldLeft(s) { (res, s) ⇒ res.replaceAll(s, " ") }.
@@ -63,17 +75,7 @@ class SqlModelTest extends FlatSpec with BeforeAndAfterAll {
mkString(" ")
private def toPretty(s: String): util.List[String] =
SqlFormatter.format(s).split("\n").toSeq.asJava
-
- override protected def beforeAll(): Unit = {
- client = new
NCTestClientBuilder().newBuilder.setResponseLog(false).build
-
- client.open("sql.model.id")
- }
-
- override protected def afterAll(): Unit =
- if (client != null)
- client.close()
-
+
private def check(multiLineOut: Boolean, cases: Case*): Unit = {
val errs = collection.mutable.LinkedHashMap.empty[String, String]
@@ -143,7 +145,8 @@ class SqlModelTest extends FlatSpec with BeforeAndAfterAll {
println("Passed")
}
- it should "work fine" in {
+ @Test
+ def test() {
check(
true,
Case(
@@ -279,30 +282,25 @@ class SqlModelTest extends FlatSpec with
BeforeAndAfterAll {
""".stripMargin
),
Case(
- // TODO:
Seq(
"employees territories"
),
"""SELECT
- | employee_territories.employee_id,
- | employee_territories.territory_id,
| employees.employee_id,
| employees.last_name,
| employees.first_name,
- | region.region_id,
- | region.region_description,
| territories.territory_id,
| territories.territory_description,
- | territories.region_id
+ | territories.region_id,
+ | employee_territories.employee_id,
+ | employee_territories.territory_id,
+ | region.region_id,
+ | region.region_description
|FROM
- | employee_territories,
- | employees,
- | territories,
- | region
- |WHERE
- | employee_territories.employee_id = employees.employee_id
- | AND employee_territories.territory_id =
territories.territory_id
- | AND territories.region_id = region.region_id
+ | employee_territories
+ | INNER JOIN employees ON employee_territories.employee_id
= employees.employee_id
+ | INNER JOIN territories ON
employee_territories.territory_id = territories.territory_id
+ | INNER JOIN region ON territories.region_id =
region.region_id
|ORDER BY
| employees.employee_id DESC,
| territories.territory_id DESC
@@ -327,13 +325,16 @@ class SqlModelTest extends FlatSpec with
BeforeAndAfterAll {
""".stripMargin
),
Case(
- // TODO:
Seq(
"last year Exotic Liquids orders"
),
"""SELECT
| suppliers.company_name,
| orders.order_date,
+ | orders.order_id,
+ | orders.required_date,
+ | suppliers.supplier_id,
+ | suppliers.contact_name,
| customers.customer_id,
| customers.company_name,
| customers.contact_name,
@@ -343,34 +344,24 @@ class SqlModelTest extends FlatSpec with
BeforeAndAfterAll {
| order_details.unit_price,
| order_details.quantity,
| order_details.discount,
- | orders.order_id,
- | orders.required_date,
| products.product_id,
| products.product_name,
| products.quantity_per_unit,
| shippers.shipper_id,
| shippers.company_name,
- | shippers.phone,
- | suppliers.supplier_id,
- | suppliers.contact_name
+ | shippers.phone
|FROM
- | order_details,
- | orders,
- | products,
- | suppliers,
- | customers,
- | shippers,
- | employees
+ | order_details
+ | INNER JOIN orders ON order_details.order_id =
orders.order_id
+ | INNER JOIN products ON order_details.product_id =
products.product_id
+ | LEFT JOIN customers ON orders.customer_id =
customers.customer_id
+ | LEFT JOIN shippers ON orders.ship_via =
shippers.shipper_id
+ | LEFT JOIN employees ON orders.employee_id =
employees.employee_id
+ | LEFT JOIN suppliers ON products.supplier_id =
suppliers.supplier_id
|WHERE
| suppliers.company_name IN (?)
| AND orders.order_date >= ?
| AND orders.order_date <= ?
- | AND order_details.order_id = orders.order_id
- | AND order_details.product_id = products.product_id
- | AND orders.customer_id = customers.customer_id
- | AND orders.ship_via = shippers.shipper_id
- | AND orders.employee_id = employees.employee_id
- | AND products.supplier_id = suppliers.supplier_id
|ORDER BY
| orders.order_id DESC,
| suppliers.supplier_id DESC
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 2d5fd82..5cf34bd 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
@@ -20,12 +20,12 @@ package org.apache.nlpcraft.examples.sql.db
import java.sql.Types
import com.typesafe.scalalogging.LazyLogging
-import org.apache.commons.graph.algorithm.path.AllPairsShortestPath
-import org.apache.commons.graph.domain.basic.DirectedGraphImpl
-import org.apache.commons.graph.exception.GraphException
+import org.apache.nlpcraft.model.tools.sqlgen.NCSqlJoinType._
import org.apache.nlpcraft.model.tools.sqlgen._
import org.apache.nlpcraft.model.tools.sqlgen.impl.NCSqlSortImpl
-import org.apache.nlpcraft.model.tools.sqlgen.NCSqlJoinType._
+import org.jgrapht.alg.shortestpath.DijkstraShortestPath
+import org.jgrapht.graph.{DefaultEdge, SimpleGraph}
+
import scala.collection.JavaConverters._
import scala.collection.{Seq, mutable}
import scala.compat.java8.OptionConverters._
@@ -46,8 +46,7 @@ import scala.compat.java8.OptionConverters._
case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
private final val DFLT_LIMIT = 1000
- private case class Edge(from: String, to: String) extends
org.apache.commons.graph.Edge
- private case class Vertex(name: String) extends
org.apache.commons.graph.Vertex
+ private case class Edge(from: String, to: String) extends DefaultEdge
private case class Key(table: String, column: String)
private val schemaTabs = schema.getTables.asScala.toSeq.sortBy(_.getTable)
@@ -56,13 +55,13 @@ case class SqlBuilder(schema: NCSqlSchema) extends
LazyLogging {
schemaTabs.flatMap(p ⇒ p.getColumns.asScala.map(col ⇒
Key(col.getTable, col.getColumn) → col)).toMap
private val schemaJoins = schema.getJoins.asScala
- private val PATHS = {
- val g = new DirectedGraphImpl()
+ private val PATH = {
+ val g = new SimpleGraph[String, Edge](classOf[Edge])
- schemaTabs.foreach(t ⇒ g.addVertex(Vertex(t.getTable)))
- schemaJoins.foreach(j ⇒ g.addEdge(Edge(j.getFromTable, j.getToTable),
Vertex(j.getFromTable), Vertex(j.getToTable)))
+ schemaTabs.foreach(t ⇒ g.addVertex(t.getTable))
+ schemaJoins.foreach(j ⇒ g.addEdge(j.getFromTable, j.getToTable,
Edge(j.getFromTable, j.getToTable)))
- new AllPairsShortestPath(g)
+ new DijkstraShortestPath(g)
}
private var tabs: Seq[NCSqlTable] = Seq.empty
@@ -113,7 +112,7 @@ case class SqlBuilder(schema: NCSqlSchema) extends
LazyLogging {
}
if (refs.length != names.length - 1)
- throw new IllegalArgumentException(s"Tables cannot be
joined: ${names.mkString(", ")}")
+ throw new RuntimeException(s"Tables cannot be joined:
${names.mkString(", ")}")
refs.mkString(" ")
}
@@ -137,16 +136,6 @@ case class SqlBuilder(schema: NCSqlSchema) extends
LazyLogging {
private def isDate(col: NCSqlColumn): Boolean = col.getDataType ==
Types.DATE
private def isString(col: NCSqlColumn): Boolean = col.getDataType ==
Types.VARCHAR
- private def getImportant(tabs: Seq[NCSqlTable], cols: Seq[NCSqlColumn]):
Seq[NCSqlColumn] =
- if (cols.nonEmpty)
- cols
- else {
- // val res = tabs.flatMap(_.getColumns.asScala.filter(_.isPk))
- val res = tabs.flatMap(_.getColumns.asScala.filter(_.isPk))
-
- if (res.isEmpty) tabs.map(_.getColumns.asScala.head) else res
- }
-
private def extendColumns(
cols: Seq[NCSqlColumn], extTabs: Seq[NCSqlTable], freeDateColOpt:
Option[NCSqlColumn]
): Seq[NCSqlColumn] = {
@@ -183,25 +172,21 @@ case class SqlBuilder(schema: NCSqlSchema) extends
LazyLogging {
case 1 ⇒ ext
case _ ⇒
// The simple algorithm, which takes into account only FKs
between tables.
- @throws[GraphException]
- def getPath(t1: NCSqlTable, t2: NCSqlTable): Seq[String] = {
- val path = PATHS.getShortestPath(Vertex(t1.getTable),
Vertex(t2.getTable))
-
- Seq(path.getStart.asInstanceOf[Vertex].name,
path.getEnd.asInstanceOf[Vertex].name)
- }
-
- ext.combinations(2).flatMap(pair ⇒
- try
- getPath(pair.head, pair.last)
- catch {
- case _ : GraphException ⇒
- try
- getPath(pair.last, pair.head)
- catch {
- case _ : GraphException ⇒ Seq.empty
- }
- }
- ).toSeq.distinct.map(schemaTabsByNames)
+ val extra =
+ ext.combinations(2).flatMap(pair ⇒
+ PATH.getPath(pair.head.getTable, pair.last.getTable)
match {
+ case null ⇒ Seq.empty
+ case list ⇒ list.getEdgeList.asScala.flatMap(e ⇒
Seq(e.from, e.to))
+ }
+ ).toSeq.distinct.map(schemaTabsByNames)
+
+ if (ext.exists(t ⇒ !extra.contains(t)))
+ throw new RuntimeException(
+ s"Select clause cannot be prepared with given tables
set: " +
+ s"${ext.map(_.getTable).mkString(", ")}"
+ )
+
+ extra
}
}
@@ -211,7 +196,7 @@ case class SqlBuilder(schema: NCSqlSchema) extends
LazyLogging {
cols2Sort.sortBy(col ⇒
if (cols.contains(col))
0
- else if (tabs.contains(col.getTable))
+ else if (tabs.contains(schemaTabsByNames(col.getTable)))
1
else
2
