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)