| --- |
| active_crumb: SQL Model Generator |
| layout: documentation |
| id: sql_model_gen |
| fa_icon: fa-tools |
| --- |
| |
| <!-- |
| 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. |
| --> |
| |
| <div class="col-md-8 second-column"> |
| <section id="overview"> |
| <h2 class="section-title">Overview <a href="#"><i class="top-link fas fa-fw fa-angle-double-up"></i></a></h2> |
| <p> |
| When using NLPCraft to develop natural language interface to the existing SQL RDBMS one of the |
| routine tasks is to develop a data model that mirrors the SQL schema for the given |
| database. Doing it manually can be time consuming and error prone. |
| </p> |
| <p> |
| SQL model generator automates this task. This is a Java-based utility that takes JDBC configuration, |
| reads database schema using it and creates initial JSON or YAML stub for the data model. This stub then |
| can be further extended and modified. |
| </p> |
| </section> |
| <section id="usage"> |
| <h2 class="section-title">Usage <a href="#"><i class="top-link fas fa-fw fa-angle-double-up"></i></a></h2> |
| <p> |
| This utility can be run in several ways: |
| </p> |
| <nav> |
| <div class="nav nav-tabs" role="tablist"> |
| <a class="nav-item nav-link active" data-toggle="tab" href="#nav-script" role="tab">NLPCraft CLI</a> |
| <a class="nav-item nav-link" data-toggle="tab" href="#nav-class" role="tab">Java Class <img src="/images/java2-h20.png" alt=""></a> |
| </div> |
| </nav> |
| <div class="tab-content"> |
| <div class="tab-pane fade show active" id="nav-script" role="tabpanel"> |
| <pre class="brush: bash"> |
| $ bin/nlpcraft.sh gen-sql --url=jdbc:postgresql://localhost:5432/mydb --driver=org.postgresql.Driver --schema=public --out=model.json |
| </pre> |
| <p> |
| <b>NOTES:</b> |
| </p> |
| <ul> |
| <li> |
| <a href="/tools/script.html">NLPCraft CLI</a> is available as <code>nlpcraft.sh</code> for |
| <i class="fab fa-fw fa-linux"></i> and <code>nlpcraft.cmd</code> |
| for <i class="fab fa-fw fa-windows"></i>. |
| </li> |
| <li> |
| Run <code class="script">bin/nlpcraft.sh help --cmd=gen-sql</code> to get a full help on this command. |
| </li> |
| </ul> |
| </div> |
| <div class="tab-pane fade show" id="nav-class" role="tabpanel"> |
| <pre class="brush: bash"> |
| $ java -cp apache-nlpcraft-incubating-{{site.latest_version}}-all-deps.jar org.apache.nlpcraft.model.tools.sqlgen.NCSqlModelGenerator |
| </pre> |
| <p> |
| <b>NOTES:</b> |
| </p> |
| <ul> |
| <li> |
| Run this class without arguments to get a full help. |
| </li> |
| <li> |
| Use <code>org.apache.nlpcraft.model.tools.sqlgen.NCSqlModelGenerator</code> class directly to execute |
| it from IDE or programmatically. |
| </li> |
| </ul> |
| </div> |
| </div> |
| <h2 class="section-sub-title">Parameters <a href="#"><i class="top-link fas fa-fw fa-angle-double-up"></i></a></h2> |
| <p> |
| If you run this tool with <code>--help</code> parameter you'll get the full help information for each |
| parameter: |
| </p> |
| <pre class="brush: text"> |
| NAME: |
| NCSqlModelGenerator -- NLPCraft model generator from SQL databases. |
| |
| SYNOPSIS: |
| java -cp apache-nlpcraft-incubating-{{site.latest_version}}-all-deps.jar org.apache.nlpcraft.model.tools.sqlgen.NCSqlModelGenerator [PARAMETERS] |
| |
| DESCRIPTION: |
| This utility generates NLPCraft model stub from a given SQL database schema. You |
| can choose database schema, set of tables and columns for which you |
| want to generate NLPCraft model. After the model is generated you can |
| further configure and customize it for your specific needs. |
| |
| This Java class can be run from the command line or from an IDE like any other |
| Java application. Note that required JDBC driver class must be available on the |
| classpath and therefore its JAR should be added to the classpath when running |
| this application. |
| |
| PARAMETERS: |
| --url|-r=url |
| Mandatory database JDBC URL. |
| |
| --driver|-d=class |
| Mandatory JDBC driver class. Note that 'class' must be a |
| fully qualified class name. It should also be available on |
| the classpath. |
| |
| --schema|-s=schema |
| Mandatory database schema to scan. |
| |
| --out|-o=filename |
| Mandatory name of the output JSON or YAML model file. It should |
| have one of the following extensions: .js, .json, .yml, or .yaml |
| File extension determines the output file format. |
| |
| --user|-u=username |
| Optional database user name. |
| |
| --password|-w=password |
| Optional database user password. |
| |
| --mdlId|-m=id |
| Optional generated model ID. By default, the model ID will be 'sql.model.id'. |
| |
| --mdlVer|-v=version |
| Optional generated model version. By default, the model version will be '1.0.0-timestamp'. |
| |
| --mdlName|-n=name |
| Optional generated model name. By default, the model name will be 'SQL-based model'. |
| |
| --exclude|-e=list |
| Optional semicolon-separate list of tables and/or columns to exclude. By |
| default, none of the tables and columns in the schema are excluded. See below |
| for more information. |
| |
| --prefix|-f=list |
| Optional comma-separate list of table or column name prefixes to remove. |
| These prefixes will be removed when name is used for model elements |
| synonyms. By default, no prefixes will be removed. |
| |
| --suffix|-q=list |
| Optional comma-separate list of table or column name suffixes to remove. |
| These suffixes will be removed when name is used for model elements |
| synonyms. By default, no suffixes will be removed. |
| |
| --include|-i=list |
| Optional semicolon-separate list of tables and/or columns to include. By |
| default, all tables and columns in the schema are included. See below |
| for more information. |
| |
| --synonyms|-y=true|false |
| Optional flag on whether to generated auto synonyms for the model elements. |
| Default is true. |
| |
| --override|-z=true|false |
| Optional flag to determine whether to override output file if it already exist. |
| If override is disabled (default) and output file exists - a unique file name will |
| be used instead. |
| Default is false. |
| |
| --parent|-p=true|false |
| Optional flag on whether to use element's parent relationship for |
| defining SQL columns and their containing (i.e. parent) tables. |
| Default is false. |
| |
| --help|-h|-? |
| Prints this usage information. |
| |
| DETAILS: |
| -r, -d, -s, and -o are mandatory parameters, everything else is optional. |
| |
| Parameter values can be placed in double (") or single (') quotes which will be |
| automatically discarded. Use it to pass strings containing spaces in the command line. |
| |
| Each -i or -e parameter is a semicolon (;) separated list of table or columns names. |
| Each table or column name can be one of following forms: |
| - table -- to filter on table names only. |
| - table#column -- to filter on both table and column names. |
| - #column -- to filter on columns only (regardless of the table). |
| |
| Table and column names are treated as standard Java regular expressions. Note that |
| both '#' and ';' cannot be used inside of the regular expression: |
| |
| -e="#_.+" -- excludes any columns starting with '_'. |
| -e="tmp.+" -- excludes all tables starting with 'tmp'. |
| -i="Order.*;#[^_].+" -- includes only tables starting with 'Order' and columns that |
| do not start with '_'. |
| |
| EXAMPLES: |
| java -cp apache-nlpcraft-incubating-{{site.latest_version}}-all-deps.jar org.apache.nlpcraft.model.tools.sqlgen.NCSqlModelGenerator |
| -r=jdbc:postgresql://localhost:5432/mydb |
| -d=org.postgresql.Driver |
| -f="tbl_, col_" |
| -q="_tmp, _old, _unused" |
| -s=public |
| -e="#_.+" |
| -o=model.json |
| </pre> |
| <p> |
| After the data model stub is generated: |
| </p> |
| <ul> |
| <li> |
| Load generated YAML/JSON-based model using <a target="javadoc" href="https://javadoc.io/static/org.apache.nlpcraft/nlpcraft/0.5.0/org/apache/nlpcraft/model/NCModelFileAdapter.html">NCModelFileAdapter</a> |
| class to instantiate model from this file. |
| </li> |
| <li> |
| Modify and extend generated model stub to your own needs. In most cases, you'll need |
| to add, remove or modify auto-generated synonyms, add intents, etc. Note, however, that generated model |
| is valid and minimally complete and can be used as is. |
| </li> |
| <li> |
| Use <a target="javadoc" href="/apis/latest/org/apache/nlpcraft/model/tools/sqlgen/NCSqlExtractorBuilder.html">NCSqlSchemaBuilder</a> class to get an object representation of the |
| SQL data schema for the model. You can use this object representation along with many utility |
| methods in <a target="javadoc" href="/apis/latest/org/apache/nlpcraft/model/tools/sqlgen/NCSqlExtractor.html">NCSqlExtractor</a> |
| class to efficiently auto-generate SQL queries against the source RDBMS. |
| </li> |
| </ul> |
| <p> |
| SQL model generator comes with a several utility classes that can be used to programmatically explore |
| generated data model, its elements and their metadata. You should start with the following builders to learn more |
| about these utility classes: |
| </p> |
| <ul> |
| <li> |
| <a target="javadoc" href="/apis/latest/org/apache/nlpcraft/model/tools/sqlgen/NCSqlExtractorBuilder.html">NCSqlExtractorBuilder</a> |
| </li> |
| <li> |
| <a target="javadoc" href="/apis/latest/org/apache/nlpcraft/model/tools/sqlgen/NCSqlSchemaBuilder.html">NCSqlSchemaBuilder</a> |
| </li> |
| </ul> |
| </section> |
| <section id="examples"> |
| <h2 class="section-title">Examples <a href="#"><i class="top-link fas fa-fw fa-angle-double-up"></i></a></h2> |
| <p> |
| SQL model generator is used by <a target="github" href="https://github.com/apache/incubator-nlpcraft/tree/master/nlpcraft-examples/sql">SQL Model</a> example. This example comes with two YAML models: |
| </p> |
| <ul> |
| <li><code>sql_model_init.yaml</code> - original generated model straight out of the SQL model generator.</li> |
| <li><code>sql_model.yaml</code> - modified and updated model ultimately used by the example.</li> |
| </ul> |
| <p> |
| It is recommended to run a diff between these two files to see what was actually changed and how. |
| </p> |
| </section> |
| </div> |
| <div class="col-md-2 third-column"> |
| <ul class="side-nav"> |
| <li class="side-nav-title">On This Page</li> |
| <li><a href="#overview">Overview</a></li> |
| <li><a href="#usage">Usage</a></li> |
| <li><a href="#examples">Examples</a></li> |
| {% include quick-links.html %} |
| </ul> |
| </div> |
| |
| |
| |
| |
| |
| |
