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/06/22 18:41:18 UTC
[incubator-nlpcraft] branch NLPCRAFT-84 created (now 3f11924)
This is an automated email from the ASF dual-hosted git repository.
sergeykamov pushed a change to branch NLPCRAFT-84
in repository https://gitbox.apache.org/repos/asf/incubator-nlpcraft.git.
at 3f11924 SQL example simplified.
This branch includes the following new commits:
new 3f11924 SQL example simplified.
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
[incubator-nlpcraft] 01/01: SQL example simplified.
Posted by se...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
sergeykamov pushed a commit to branch NLPCRAFT-84
in repository https://gitbox.apache.org/repos/asf/incubator-nlpcraft.git
commit 3f119248bac0294ce001dcd47057ca9d19619462
Author: Sergey Kamov <se...@apache.org>
AuthorDate: Mon Jun 22 21:41:09 2020 +0300
SQL example simplified.
---
bindist/LICENSE | 11 +--
nlpcraft/pom.xml | 4 -
.../org/apache/nlpcraft/examples/sql/SqlTest.scala | 10 +--
.../nlpcraft/examples/sql/db/SqlBuilder.scala | 93 +++++++++++++++-------
pom.xml | 7 --
5 files changed, 69 insertions(+), 56 deletions(-)
diff --git a/bindist/LICENSE b/bindist/LICENSE
index 9049363..91f907c 100644
--- a/bindist/LICENSE
+++ b/bindist/LICENSE
@@ -265,13 +265,4 @@
dictionaries in WordNet format.
Maven dependency: net.sf.extjwnl
- Licensed under BSD license: http://extjwnl.sourceforge.net/license.txt
-
- Jgrapht
- -------------
- a Java library of graph theory data structures and algorithms https://jgrapht.org/
-
- Maven dependency: org.jgrapht:jgrapht-core
- Licensed under EPL 2.0 License: https://github.com/jgrapht/jgrapht/wiki/Users%3A-Relicensing
-
-
+ Licensed under BSD license: http://extjwnl.sourceforge.net/license.txt
\ No newline at end of file
diff --git a/nlpcraft/pom.xml b/nlpcraft/pom.xml
index fbb039f..88d75c0 100644
--- a/nlpcraft/pom.xml
+++ b/nlpcraft/pom.xml
@@ -74,10 +74,6 @@
<artifactId>antlr4-runtime</artifactId>
</dependency>
<dependency>
- <groupId>org.jgrapht</groupId>
- <artifactId>jgrapht-core</artifactId>
- </dependency>
- <dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-text</artifactId>
</dependency>
diff --git a/nlpcraft/src/main/scala/org/apache/nlpcraft/examples/sql/SqlTest.scala b/nlpcraft/src/main/scala/org/apache/nlpcraft/examples/sql/SqlTest.scala
index 3b4bca9..329e042 100644
--- a/nlpcraft/src/main/scala/org/apache/nlpcraft/examples/sql/SqlTest.scala
+++ b/nlpcraft/src/main/scala/org/apache/nlpcraft/examples/sql/SqlTest.scala
@@ -625,11 +625,11 @@ class SqlTest {
| 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 suppliers ON products.supplier_id = suppliers.supplier_id
- | LEFT JOIN categories ON products.category_id = categories.category_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
+ | LEFT JOIN categories ON products.category_id = categories.category_id
|WHERE
| orders.order_date >= ?
| AND orders.order_date <= ?
@@ -667,13 +667,13 @@ class SqlTest {
| shippers.company_name,
| shippers.phone
|FROM
- | products
- | LEFT JOIN categories ON products.category_id = categories.category_id
- | INNER JOIN order_details ON order_details.product_id = products.product_id
+ | 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 categories ON products.category_id = categories.category_id
|WHERE
| orders.order_date >= ?
| AND orders.order_date <= ?
diff --git a/nlpcraft/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlBuilder.scala b/nlpcraft/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlBuilder.scala
index 23c0bff..f75a0b4 100644
--- a/nlpcraft/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlBuilder.scala
+++ b/nlpcraft/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlBuilder.scala
@@ -23,8 +23,6 @@ import com.typesafe.scalalogging.LazyLogging
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.jgrapht.alg.shortestpath.DijkstraShortestPath
-import org.jgrapht.graph.{DefaultEdge, SimpleGraph}
import scala.collection.JavaConverters._
import scala.collection.{Seq, mutable}
@@ -37,28 +35,67 @@ import scala.compat.java8.OptionConverters._
* - negation (SQL <> condition),
* - LIKE and another functions.
*
- * However, these capabilities can be added relatively easy to this extendable implementation.
+ * However, these capabilities can be added relatively easy to this extendable implementation.
*
* @param schema Parsed DB schema to initialize with.
*/
case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
private final val DFLT_LIMIT = 1000
- private case class Edge(from: String, to: String) extends DefaultEdge
- private case class Key(table: String, column: String)
-
private val schemaTbls = schema.getTables.asScala.toSeq.sortBy(_.getTable)
private val schemaTblsByNames = schemaTbls.map(p ⇒ p.getTable → p).toMap
private val schemaCols = schemaTbls.flatMap(p ⇒ p.getColumns.asScala.map(col ⇒ Key(col.getTable, col.getColumn) → col)).toMap
private val schemaJoins = schema.getJoins.asScala
- private val schemaPaths = {
- val g = new SimpleGraph[String, Edge](classOf[Edge])
+ private val g = Graph(schemaJoins.map(j ⇒ Edge(j.getFromTable, j.getToTable)).toSet)
+
+ private case class Key(table: String, column: String)
+
+ private case class Edge(from: String, to: String)
+
+ private case class Graph(edges: Set[Edge]) {
+ private val allNeighbors: Map[String, Set[String]] =
+ edges.
+ flatMap(e ⇒ Seq(e.from → e.to, e.to → e.from)).
+ groupBy { case (from, _) ⇒ from }.
+ map { case (from, seq) ⇒ from → seq.map { case (_, to) ⇒ to } }.
+ withDefaultValue(Set.empty)
+
+ def bfs(from: String, to: String): Seq[String] = {
+ val visited = mutable.Set[String](from)
+ val queue = mutable.Queue[String](from)
+ val parents = mutable.HashMap.empty[String, String]
+ val path = mutable.ArrayBuffer.empty[String]
+
+ var found = false
- schemaTbls.foreach(t ⇒ g.addVertex(t.getTable))
- schemaJoins.foreach(j ⇒ g.addEdge(j.getFromTable, j.getToTable, Edge(j.getFromTable, j.getToTable)))
+ while (queue.nonEmpty && !found) {
+ val parent = queue.dequeue
+ val children = allNeighbors(parent)
- new DijkstraShortestPath(g)
+ if (children.contains(to)) {
+ parents += to → parent
+
+ var n: String = to
+
+ while (n != null) {
+ path += n
+
+ n = parents.get(n).orNull
+ }
+
+ found = true
+ }
+ else
+ for (child ← children if !visited.contains(child)) {
+ parents += child → parent
+ visited += child
+ queue += child
+ }
+ }
+
+ path.reverse
+ }
}
private var tbls: Seq[NCSqlTable] = Seq.empty
@@ -67,7 +104,7 @@ case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
private var sorts: Seq[NCSqlSort] = Seq.empty
private var freeDateRangeOpt: Option[NCSqlDateRange] = None
private var limit: Option[NCSqlLimit] = None
-
+
/**
* Makes SQL text fragment for given join type.
*
@@ -83,7 +120,7 @@ case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
case _ ⇒ throw new AssertionError(s"Unexpected join type: $clause")
}
-
+
/**
* Makes SQL join fragment for given tables. It uses tables parameters and information about relations between
* tables based on schema information.
@@ -101,7 +138,7 @@ case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
val refs = names.
flatMap(t ⇒ schemaJoins.filter(j ⇒ j.getFromTable == t && names.contains(j.getToTable))).
- sortBy(j ⇒ Math.min(names.indexOf(j.getFromTable), names.indexOf(j.getToTable))).
+ sortBy(_.getFromTable).
zipWithIndex.
map { case (join, idx) ⇒
val fromCols = join.getFromColumns.asScala
@@ -145,7 +182,7 @@ case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
if (refs.length != names.length - 1)
throw new RuntimeException(s"Tables cannot be joined: ${names.mkString(", ")}")
- refs.mkString(" ")
+ refs.mkString(" ")
}
}
@@ -203,7 +240,7 @@ case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
* @param col Column element.
*/
private def isString(col: NCSqlColumn): Boolean = col.getDataType == Types.VARCHAR
-
+
/**
* Extends given columns list, if necessary, by some additional columns, based on schema model
* configuration and free date column. It can be useful if column list is poor.
@@ -226,7 +263,7 @@ case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
res
}
-
+
/**
* Tries to find sort elements if they are not detected explicitly.
* It attempts to use sort information from limit element if it is defined, or from model configuration
@@ -248,7 +285,7 @@ case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
).distinct
case _ ⇒ sorts.distinct
}
-
+
/**
* Extends given table list, if necessary, by some additional tables,
* based on information about relations between model tables.
@@ -265,30 +302,26 @@ case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
case _ ⇒
// The simple algorithm, which takes into account only FKs between tables.
val extra =
- ext.combinations(2).flatMap(pair ⇒
- schemaPaths.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(schemaTblsByNames)
+ ext.combinations(2).flatMap(pair ⇒ g.bfs(pair.head.getTable, pair.last.getTable)).
+ toSeq.distinct.map(schemaTblsByNames)
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(", ")}"
+ s"${ext.map(_.getTable).mkString(", ")}"
)
extra
}
}
-
+
/**
* Converts limit element to sort.
*
* @param l Limit element.
*/
private def limit2Sort(l: NCSqlLimit): NCSqlSort = NCSqlSortImpl(l.getColumn, l.isAscending)
-
+
/**
* Sorts given extra columns for select list, using the following criteria:
* - initially detected columns are more important.
@@ -308,7 +341,7 @@ case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
else
2
)
-
+
/**
* Tries to find date sql column.
*
@@ -331,7 +364,7 @@ case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
None
}
-
+
/**
* Extends conditions list by given free data (if defined) and converts conditions to
* SQL text and parameters list.
@@ -409,7 +442,7 @@ case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
* @param limit Limit element.
*/
def withLimit(limit: NCSqlLimit): SqlBuilder = { this.limit = Option(limit); this }
-
+
/**
* Main build method. Builds and returns newly constructed SQL query object.
*/
diff --git a/pom.xml b/pom.xml
index 8022764..1cd2158 100644
--- a/pom.xml
+++ b/pom.xml
@@ -86,7 +86,6 @@
<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>
@@ -231,12 +230,6 @@
</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>
<version>${apache.commons.text.ver}</version>