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/03/21 13:44:43 UTC
[incubator-nlpcraft] branch NLPCRAFT-23 updated: WIP.
This is an automated email from the ASF dual-hosted git repository.
sergeykamov pushed a commit to branch NLPCRAFT-23
in repository https://gitbox.apache.org/repos/asf/incubator-nlpcraft.git
The following commit(s) were added to refs/heads/NLPCRAFT-23 by this push:
new 7121aba WIP.
7121aba is described below
commit 7121abad512b6afb609ea244692fd72ad845fd49
Author: Sergey Kamov <se...@apache.org>
AuthorDate: Sat Mar 21 16:44:36 2020 +0300
WIP.
---
.../apache/nlpcraft/examples/sql/sql_model.yaml | 41 +++++++++++-------
.../nlpcraft/model/tools/sqlgen/NCSqlJoin.java | 3 ++
.../nlpcraft/model/tools/sqlgen/NCSqlJoinType.java | 26 ++++++++++++
.../model/tools/sqlgen/impl/NCSqlBeans.scala | 3 +-
.../tools/sqlgen/impl/NCSqlExtractorsImpl.scala | 23 ++++++++--
.../sqlgen/impl/NCSqlModelGeneratorImpl.scala | 49 ++++++++++++++++++----
6 files changed, 118 insertions(+), 27 deletions(-)
diff --git a/src/main/scala/org/apache/nlpcraft/examples/sql/sql_model.yaml b/src/main/scala/org/apache/nlpcraft/examples/sql/sql_model.yaml
index f5bf06c..d934131 100644
--- a/src/main/scala/org/apache/nlpcraft/examples/sql/sql_model.yaml
+++ b/src/main/scala/org/apache/nlpcraft/examples/sql/sql_model.yaml
@@ -12,62 +12,72 @@ metadata:
totable: "customers"
tocolumns:
- "customer_id"
+ jointype: "left"
- fromtable: "orders"
fromcolumns:
- "ship_via"
totable: "shippers"
tocolumns:
- "shipper_id"
+ jointype: "left"
- fromtable: "orders"
fromcolumns:
- "employee_id"
totable: "employees"
tocolumns:
- "employee_id"
+ jointype: "left"
- fromtable: "order_details"
fromcolumns:
- "order_id"
totable: "orders"
tocolumns:
- "order_id"
+ jointype: "inner"
- fromtable: "order_details"
fromcolumns:
- "product_id"
totable: "products"
tocolumns:
- "product_id"
+ jointype: "inner"
- fromtable: "territories"
fromcolumns:
- "region_id"
totable: "region"
tocolumns:
- "region_id"
+ jointype: "inner"
- fromtable: "products"
fromcolumns:
- "supplier_id"
totable: "suppliers"
tocolumns:
- "supplier_id"
+ jointype: "left"
- fromtable: "products"
fromcolumns:
- "category_id"
totable: "categories"
tocolumns:
- "category_id"
+ jointype: "left"
- fromtable: "employee_territories"
fromcolumns:
- "employee_id"
totable: "employees"
tocolumns:
- "employee_id"
+ jointype: "inner"
- fromtable: "employee_territories"
fromcolumns:
- "territory_id"
totable: "territories"
tocolumns:
- "territory_id"
- sql:output: "src/main/scala/org/apache/nlpcraft/examples/nlp2sql/nlp2sql_model.json"
- sql:timestamp: "2020-01-22T09:13:42.716Z"
+ jointype: "inner"
+ sql:output: "/Users/sergeykhisamov/apache/incubator-nlpcraft/src/main/scala/org/apache/nlpcraft/examples/sql/sql_model.yaml"
+ sql:timestamp: "2020-03-21T11:50:54.433Z"
sql:schema: "PUBLIC"
macros:
- name: "<OF>"
@@ -86,7 +96,7 @@ elements:
- "region_id"
- "region_description"
sql:defaultsort:
- - "region_id#desc"
+ - "region.region_id#desc"
description: "Auto-generated from 'region' table."
- id: "tbl:orders"
groups:
@@ -100,7 +110,7 @@ elements:
- "order_date"
- "required_date"
sql:defaultsort:
- - "order_id#desc"
+ - "orders.order_id#desc"
sql:extratables:
- "customers"
- "shippers"
@@ -119,7 +129,7 @@ elements:
- "company_name"
- "contact_name"
sql:defaultsort:
- - "supplier_id#desc"
+ - "suppliers.supplier_id#desc"
description: "Auto-generated from 'suppliers' table."
- id: "tbl:order_details"
groups:
@@ -133,8 +143,8 @@ elements:
- "quantity"
- "discount"
sql:defaultsort:
- - "order_id#desc"
- - "product_id#desc"
+ - "order_details.order_id#desc"
+ - "order_details.product_id#desc"
sql:extratables:
- "orders"
- "products"
@@ -152,7 +162,7 @@ elements:
- "territory_description"
- "region_id"
sql:defaultsort:
- - "territory_id#desc"
+ - "territories.territory_id#desc"
sql:extratables:
- "region"
description: "Auto-generated from 'territories' table."
@@ -168,7 +178,7 @@ elements:
- "company_name"
- "phone"
sql:defaultsort:
- - "shipper_id#desc"
+ - "shippers.shipper_id#desc"
description: "Auto-generated from 'shippers' table."
- id: "tbl:products"
groups:
@@ -182,7 +192,7 @@ elements:
- "product_name"
- "quantity_per_unit"
sql:defaultsort:
- - "product_id#desc"
+ - "products.product_id#desc"
sql:extratables:
- "suppliers"
- "categories"
@@ -199,8 +209,8 @@ elements:
- "employee_id"
- "territory_id"
sql:defaultsort:
- - "employee_id#desc"
- - "territory_id#desc"
+ - "employee_territories.employee_id#desc"
+ - "employee_territories.territory_id#desc"
sql:extratables:
- "employees"
- "territories"
@@ -218,7 +228,7 @@ elements:
- "company_name"
- "contact_name"
sql:defaultsort:
- - "customer_id#desc"
+ - "customers.customer_id#desc"
description: "Auto-generated from 'customers' table."
- id: "tbl:categories"
groups:
@@ -232,7 +242,7 @@ elements:
- "category_name"
- "description"
sql:defaultsort:
- - "category_id#desc"
+ - "categories.category_id#desc"
description: "Auto-generated from 'categories' table."
- id: "tbl:employees"
groups:
@@ -246,8 +256,7 @@ elements:
- "last_name"
- "first_name"
sql:defaultsort:
- - "employee_id#desc"
- sql:defaultdate: "employees.birth_date"
+ - "employees.employee_id#desc"
description: "Auto-generated from 'employees' table."
- id: "col:region_region_id"
groups:
diff --git a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/NCSqlJoin.java b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/NCSqlJoin.java
index 15eba66..9c168c6 100644
--- a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/NCSqlJoin.java
+++ b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/NCSqlJoin.java
@@ -65,4 +65,7 @@ public interface NCSqlJoin {
* @return List of columns in target table.
*/
List<String> getToColumns();
+
+ // TODO:
+ NCSqlJoinType getType();
}
diff --git a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/NCSqlJoinType.java b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/NCSqlJoinType.java
new file mode 100644
index 0000000..a725d88
--- /dev/null
+++ b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/NCSqlJoinType.java
@@ -0,0 +1,26 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.nlpcraft.model.tools.sqlgen;
+
+// TODO:
+
+public enum NCSqlJoinType {
+ INNER,
+ LEFT,
+ RIGHT
+}
diff --git a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlBeans.scala b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlBeans.scala
index d79e14e..1844ed5 100644
--- a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlBeans.scala
+++ b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlBeans.scala
@@ -59,11 +59,12 @@ case class NCSqlFunctionImpl(column: NCSqlColumn, function: String) extends NCSq
override def getFunction: String = function
}
-case class NCSqlJoinImpl(fromTable: String, toTable: String, fromColumns: Seq[String], toColumns: Seq[String]) extends NCSqlJoin {
+case class NCSqlJoinImpl(fromTable: String, toTable: String, fromColumns: Seq[String], toColumns: Seq[String], typ: NCSqlJoinType) extends NCSqlJoin {
override def getFromTable: String = fromTable
override def getToTable: String = toTable
override def getFromColumns: util.List[String] = fromColumns.asJava
override def getToColumns: util.List[String] = toColumns.asJava
+ override def getType: NCSqlJoinType = typ
}
case class NCSqlSortImpl(column: NCSqlColumn, asc: Boolean) extends NCSqlSort {
diff --git a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlExtractorsImpl.scala b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlExtractorsImpl.scala
index 1430a0f..9d4bcea 100644
--- a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlExtractorsImpl.scala
+++ b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlExtractorsImpl.scala
@@ -277,7 +277,23 @@ object NCSqlExtractorsImpl {
cols.asScala,
defSort.
map(s ⇒ {
- val pair = s.split("#")
+ var pair = defDate.split("\\.")
+
+ val t =
+ pair.length match {
+ case 1 ⇒
+ pair = s.split("#")
+
+ // By default, same table name.
+ tab
+ case 2 ⇒
+ val t = pair.head
+
+ pair = pair.last.split("#")
+
+ t
+ case _ ⇒ throw new NCSqlException(s"Invalid sort: $s")
+ }
if (pair.length != 2)
throw new NCSqlException(s"Invalid sort: $s")
@@ -288,7 +304,7 @@ object NCSqlExtractorsImpl {
if (asc != "asc" && asc != "desc")
throw new NCSqlException(s"Invalid sort: $pair")
- val sort: NCSqlSort = NCSqlSortImpl(findSchemaColumn(cols, tab, col), asc == "asc")
+ val sort: NCSqlSort = NCSqlSortImpl(findSchemaColumn(cols, t, col), asc == "asc")
sort
}),
@@ -321,7 +337,8 @@ object NCSqlExtractorsImpl {
m("fromtable").asInstanceOf[String],
m("totable").asInstanceOf[String],
m("fromcolumns").asInstanceOf[util.List[String]].asScala,
- m("tocolumns").asInstanceOf[util.List[String]].asScala
+ m("tocolumns").asInstanceOf[util.List[String]].asScala,
+ NCSqlJoinType.valueOf(m("jointype").asInstanceOf[String].toUpperCase())
)
j
diff --git a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlModelGeneratorImpl.scala b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlModelGeneratorImpl.scala
index 9af57f7..4954bea 100644
--- a/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlModelGeneratorImpl.scala
+++ b/src/main/scala/org/apache/nlpcraft/model/tools/sqlgen/impl/NCSqlModelGeneratorImpl.scala
@@ -31,6 +31,7 @@ import com.fasterxml.jackson.dataformat.yaml.YAMLGenerator.Feature
import org.apache.nlpcraft.common.ascii.NCAsciiTable
import org.apache.nlpcraft.common.nlp.core.NCNlpPorterStemmer
import org.apache.nlpcraft.model.impl.json.{NCElementJson, NCMacroJson, NCModelJson}
+import org.apache.nlpcraft.model.tools.sqlgen.NCSqlJoinType
import resource.managed
import scala.collection.JavaConverters._
@@ -257,7 +258,7 @@ object NCSqlModelGeneratorImpl {
add(meta, "sql:name", tbl.nameLc)
add(meta, "sql:defaultselect", tbl.columns.take(3).map(_.nameLc).asJava) // First 3 columns by default.
- add(meta, "sql:defaultsort", tbl.columns.filter(_.isPk).map(col ⇒ s"${col.nameLc}#desc").asJava)
+ add(meta, "sql:defaultsort", tbl.columns.filter(_.isPk).map(col ⇒ s"${tbl.nameLc}.${col.nameLc}#desc").asJava)
add(meta, "sql:extratables", tbl.joins.map(_.toTable.toLowerCase).toSet.asJava)
e.setMetadata(meta)
@@ -268,6 +269,8 @@ object NCSqlModelGeneratorImpl {
elems += e
}
+ val tablesMap = tables.map(t ⇒ t.name → t).toMap
+
for (tbl ← tables; col ← tbl.columns) {
val e = new NCElementJson
@@ -326,12 +329,38 @@ object NCSqlModelGeneratorImpl {
"sql:cmdline" → params.cmdLine,
"sql:joins" →
tables.flatMap(t ⇒ t.joins.map(j ⇒ {
+ val fromTable = t.name.toLowerCase
+ val toTable = j.toTable.toLowerCase
+ val fromCols = j.fromColumns.map(_.toLowerCase)
+ val toCols = j.toColumns.map(_.toLowerCase)
+
+ def mkNullables(t: String, cols: Seq[String]): Seq[Boolean] = {
+ val tabCols = tablesMap(t).columns
+
+ cols.map(col ⇒ tabCols.find(_.name == col).get.isNull)
+ }
+
+ val fromColsNulls = mkNullables(fromTable, fromCols)
+ val toColsNulls =mkNullables(toTable, toCols)
+
+ def forall(seq: Seq[Boolean], v: Boolean): Boolean = seq.forall(_ == v)
+
+ val typ =
+ if (forall(fromColsNulls, true) && forall(toColsNulls, false))
+ NCSqlJoinType.LEFT
+ else if (forall(fromColsNulls, false) && forall(toColsNulls, true))
+ NCSqlJoinType.RIGHT
+ else
+ // Default value.
+ NCSqlJoinType.INNER
+
val m = new util.LinkedHashMap[String, Object]()
- m.put("fromtable", t.name.toLowerCase)
- m.put("fromcolumns", j.fromColumns.map(_.toLowerCase).asJava)
- m.put("totable", j.toTable.toLowerCase)
- m.put("tocolumns", j.toColumns.map(_.toLowerCase).asJava)
+ m.put("fromtable", fromTable)
+ m.put("fromcolumns", fromCols.asJava)
+ m.put("totable", toTable)
+ m.put("tocolumns", toCols.asJava)
+ m.put("jointype", typ.toString.toLowerCase)
m
})).asJava
@@ -513,7 +542,11 @@ object NCSqlModelGeneratorImpl {
fk.fromTableSchema == null && fk.toTableSchema == null ||
fk.fromTableSchema == schNameOrigin && fk.toTableSchema == schNameOrigin
).groupBy(_.name).map { case (_, fksGrp) ⇒
- Join(fksGrp.map(_.fromColumn), fksGrp.head.toTable, fksGrp.map(_.toColumn))
+ Join(
+ fromColumns = fksGrp.map(_.fromColumn),
+ toTable = fksGrp.head.toTable,
+ toColumns = fksGrp.map(_.toColumn)
+ )
}
val t = Table(
@@ -543,7 +576,9 @@ object NCSqlModelGeneratorImpl {
}
catch {
case _: ClassNotFoundException ⇒ errorExit(s"Unknown JDBC driver class: ${params.driver}")
- case e: Exception ⇒ errorExit(s"Failed to generate model for '${params.url}': ${e.getMessage}")
+ case e: Exception ⇒
+ e.printStackTrace()
+ errorExit(s"Failed to generate model for '${params.url}': ${e.getMessage}")
}
tables.values.toSeq.filter(_.columns.nonEmpty)