SQL example simplified.
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 b175f48..6f5b288 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 @@
| 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 @@
| 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 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 @@
* - 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)
- schemaTbls.foreach(t ⇒ g.addVertex(t.getTable))
- schemaJoins.foreach(j ⇒ g.addEdge(j.getFromTable, j.getToTable, Edge(j.getFromTable, j.getToTable)))
+ private case class Key(table: String, column: String)
- new DijkstraShortestPath(g)
+ 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
+
+ while (queue.nonEmpty && !found) {
+ val parent = queue.dequeue
+ val children = allNeighbors(parent)
+
+ 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 @@
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 _ ⇒ 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 @@
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 @@
if (refs.length != names.length - 1)
throw new RuntimeException(s"Tables cannot be joined: ${names.mkString(", ")}")
- refs.mkString(" ")
+ refs.mkString(" ")
}
}
@@ -203,7 +240,7 @@
* @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 @@
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 @@
).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 _ ⇒
// 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 @@
else
2
)
-
+
/**
* Tries to find date sql column.
*
@@ -331,7 +364,7 @@
None
}
-
+
/**
* Extends conditions list by given free data (if defined) and converts conditions to
* SQL text and parameters list.
@@ -409,7 +442,7 @@
* @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 c341046..a7c2582 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>
