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/04/17 12:32:23 UTC
[incubator-nlpcraft] 02/02: WIP.
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 5dd06c3802f9b6ec1a6a2a2bf212e91de39be2a6
Author: Sergey Kamov <se...@apache.org>
AuthorDate: Fri Apr 17 15:32:11 2020 +0300
WIP.
---
.../apache/nlpcraft/examples/sql/SqlModel.scala | 1 -
.../nlpcraft/examples/sql/SqlModelTest.scala | 57 +++++++++++++++++
.../nlpcraft/examples/sql/db/SqlAccess.scala | 8 +--
.../nlpcraft/examples/sql/db/SqlBuilder.scala | 6 +-
.../nlpcraft/examples/sql/db/SqlServer.scala | 71 +++++++++++++++-------
.../nlpcraft/examples/sql/db/SqlValueLoader.scala | 2 +-
.../tools/sqlgen/impl/NCSqlExtractorImpl.scala | 10 +--
7 files changed, 119 insertions(+), 36 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 6786153..1fd7837 100644
--- a/src/main/scala/org/apache/nlpcraft/examples/sql/SqlModel.scala
+++ b/src/main/scala/org/apache/nlpcraft/examples/sql/SqlModel.scala
@@ -154,7 +154,6 @@ class SqlModel extends NCModelFileAdapter("org/apache/nlpcraft/examples/sql/sql_
"term(condVals)={id == 'condition:value'}[0,7] " +
"term(condDates)={id == 'condition:date'}[0,7] " +
"term(condFreeDate)={id == 'nlpcraft:date'}? " +
- // The simplified version of aggregation wih single function column and one optional group by column.
"term(sort)={id == 'nlpcraft:sort'}? " +
"term(limit)={id == 'nlpcraft:limit'}?"
)
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 dbf7a3c..b36169e 100644
--- a/src/main/scala/org/apache/nlpcraft/examples/sql/SqlModelTest.scala
+++ b/src/main/scala/org/apache/nlpcraft/examples/sql/SqlModelTest.scala
@@ -146,6 +146,63 @@ class SqlModelTest {
}
@Test
+ def testConversation(): Unit = {
+ check(
+ true,
+ Case(
+ Seq(
+ "last year Exotic Liquids orders",
+ // Second and third sentences are the qualifying questions for first.
+ // Second by date, third by value.
+ // See logic implemented in org.apache.nlpcraft.examples.sql.SqlModel.onMatchedIntent
+ "last month",
+ "Norske Meierier"
+ ),
+ """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,
+ | employees.employee_id,
+ | employees.last_name,
+ | employees.first_name,
+ | order_details.unit_price,
+ | order_details.quantity,
+ | order_details.discount,
+ | products.product_id,
+ | products.product_name,
+ | products.quantity_per_unit,
+ | shippers.shipper_id,
+ | shippers.company_name,
+ | shippers.phone
+ |FROM
+ | 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 <= ?
+ |ORDER BY
+ | orders.order_id DESC,
+ | suppliers.supplier_id DESC
+ |LIMIT
+ | 1000
+ """.stripMargin
+ )
+ )
+ }
+
+ @Test
def test() {
check(
true,
diff --git a/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlAccess.scala b/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlAccess.scala
index 445d196..ca8cbca 100644
--- a/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlAccess.scala
+++ b/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlAccess.scala
@@ -75,7 +75,7 @@ object SqlAccess extends LazyLogging {
var data = rows.take(LOG_ROWS).toArray.map(_.toArray)
if (rows.nonEmpty && rows.size > LOG_ROWS)
- data = data ++ Array(cols.indices.map(_ ⇒ "...").toArray)
+ data = data ++ Array(cols.indices.map(_ ⇒ "...").toArray)
logger.info(s"\n${FlipTable.of(cols.toArray, data)}")
}
@@ -90,9 +90,9 @@ object SqlAccess extends LazyLogging {
logger.warn(
s"Query executed unsuccessful [sql=" +
- s"\n${SqlFormatter.format(qry.sql)}" +
- s"\nparameters=${qry.parameters.mkString(", ")}" +
- s"\n]"
+ s"\n${SqlFormatter.format(qry.sql)}" +
+ s"\nparameters=${qry.parameters.mkString(", ")}" +
+ s"\n]"
)
throw e
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 5cf34bd..09a3849 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
@@ -55,7 +55,7 @@ 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 PATH = {
+ private val schemaPaths = {
val g = new SimpleGraph[String, Edge](classOf[Edge])
schemaTabs.foreach(t ⇒ g.addVertex(t.getTable))
@@ -174,7 +174,7 @@ case class SqlBuilder(schema: NCSqlSchema) extends LazyLogging {
// The simple algorithm, which takes into account only FKs between tables.
val extra =
ext.combinations(2).flatMap(pair ⇒
- PATH.getPath(pair.head.getTable, pair.last.getTable) match {
+ 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))
}
@@ -304,7 +304,7 @@ 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 += schemaTabsByNames(sort.getColumn.getTable); colsNorm += sort.getColumn })
val freeDateColOpt = findDateColumn(tabsNorm)
diff --git a/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlServer.scala b/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlServer.scala
index 8620002..fba0751 100644
--- a/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlServer.scala
+++ b/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlServer.scala
@@ -39,7 +39,7 @@ import org.h2.tools.Server
* This utility added just for convenience. You can start via h2 jars files - http://www.h2database.com/html/main.html
* and install data with northwind.sql before using this example.
*/
-object SqlServer extends App with LazyLogging {
+object SqlServer extends LazyLogging {
private lazy final val H2_PORT: Int = 9092
private lazy final val H2_BASEDIR = "~/nlpcraft-examples/h2"
@@ -52,36 +52,63 @@ object SqlServer extends App with LazyLogging {
lazy final val H2_URL: String = s"jdbc:h2:tcp://localhost:$H2_PORT/nlp2sql"
- private def process(): Unit = {
- val srv = Server.createTcpServer(SRV_PARAMS:_*).start
+ private final val mux = new Object()
- logger.info(s"H2 server start parameters: ${SRV_PARAMS.mkString(" ")}")
- logger.info(s"H2 server status: ${srv.getStatus}")
+ @volatile private var srv: Server = _
+ @volatile private var started: Boolean = false
- val ds = new JdbcDataSource()
+ def start(): Unit =
+ mux.synchronized {
+ if (started)
+ throw new IllegalStateException("Server already started")
- ds.setUrl(s"$H2_URL;INIT=RUNSCRIPT FROM '${new File(INIT_FILE).getAbsolutePath}'")
+ srv = Server.createTcpServer(SRV_PARAMS:_*).start
- try {
- ds.getConnection
+ logger.info(s"H2 server start parameters: ${SRV_PARAMS.mkString(" ")}")
+ logger.info(s"H2 server status: ${srv.getStatus}")
+
+ val ds = new JdbcDataSource()
+
+ ds.setUrl(s"$H2_URL;INIT=RUNSCRIPT FROM '${new File(INIT_FILE).getAbsolutePath}'")
+
+ try {
+ ds.getConnection
+
+ logger.info(s"Database schema initialized for: $H2_URL")
+ }
+ catch {
+ case e: SQLException ⇒
+ // https://www.h2database.com/javadoc/org/h2/api/ErrorCode.html
+ if (e.getErrorCode != 42101)
+ throw e
- logger.info(s"Database schema initialized for: $H2_URL")
- }
- catch {
- case e: SQLException ⇒
- // https://www.h2database.com/javadoc/org/h2/api/ErrorCode.html
- if (e.getErrorCode == 42101)
logger.info(
s"Database '$H2_URL' is NOT initialized because data already exists. " +
- s"To re-initialize - delete files in '$H2_BASEDIR' folder and start again. "
+ s"To re-initialize - delete files in '$H2_BASEDIR' folder and start again. "
)
- else
- throw e
+ }
+
+ started = true
+
+ while (started) {
+ mux.wait()
+ }
}
- // Wait indefinitely.
- Thread.currentThread().join()
- }
+ def stop(): Unit =
+ mux.synchronized {
+ if (!started)
+ throw new IllegalStateException("Server already stopped")
+
+ started = false
- process()
+ mux.notifyAll()
+ }
}
+
+/**
+ * TODO:
+ */
+object SqlServerRunner extends App {
+ SqlServer.start()
+}
\ No newline at end of file
diff --git a/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlValueLoader.scala b/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlValueLoader.scala
index 8d19123..24fe07a 100644
--- a/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlValueLoader.scala
+++ b/src/main/scala/org/apache/nlpcraft/examples/sql/db/SqlValueLoader.scala
@@ -37,7 +37,7 @@ class SqlValueLoader extends NCValueLoader with LazyLogging {
val tab: String = e.metax("sql:tablename")
val col: String = e.metax("sql:name")
- SqlAccess.select(SqlQuery(s"SELECT $col FROM $tab WHERE $col IS NOT NULL", Seq.empty), false).
+ SqlAccess.select(SqlQuery(s"SELECT $col FROM $tab WHERE $col IS NOT NULL", Seq.empty), logResult = false).
rows.
map(_.head).
map(_.toString.trim).
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 0314743..e070119 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
@@ -194,7 +194,7 @@ class NCSqlExtractorImpl(schema: NCSqlSchema, variant: NCVariant) extends NCSqlE
override def extractLimit(limitTok: NCToken): NCSqlLimit = {
checkTokenId(limitTok, "nlpcraft:limit")
- val links = getLinks(variant, limitTok, "indexes", "note", false)
+ val links = getLinks(variant, limitTok, "indexes", "note", canBeEmpty = false)
links.size match {
case 1 ⇒
@@ -203,7 +203,7 @@ class NCSqlExtractorImpl(schema: NCSqlSchema, variant: NCVariant) extends NCSqlE
NCSqlLimitImpl(
findColumn(links.head, "LIMIT"),
limit.intValue(),
- getAsc(limitTok, "nlpcraft:limit:asc", false)
+ getAsc(limitTok, "nlpcraft:limit:asc", dflt = false)
)
case n ⇒ throw new NCException(s"Unexpected LIMIT links count: $n")
}
@@ -217,13 +217,13 @@ class NCSqlExtractorImpl(schema: NCSqlSchema, variant: NCVariant) extends NCSqlE
override def extractSort(sortTok: NCToken): util.List[NCSqlSort] = {
checkTokenId(sortTok, "nlpcraft:sort")
- val tables = getLinks(variant, sortTok, "subjindexes", "subjnotes", false).
+ val tables = getLinks(variant, sortTok, "subjindexes", "subjnotes", canBeEmpty = false).
map(link ⇒ findTable(link, "SORT"))
- val cols = getLinks(variant, sortTok, "byindexes", "bynotes", true).
+ val cols = getLinks(variant, sortTok, "byindexes", "bynotes", canBeEmpty = true).
map(link ⇒ findColumn(link, "SORT BY"))
- val asc = getAsc(sortTok, "nlpcraft:sort:asc", false)
+ val asc = getAsc(sortTok, "nlpcraft:sort:asc", dflt = false)
require(tables.nonEmpty)