Bug #84390 Python scripting API doesn't pass options to forward-engineering
Submitted: 3 Jan 2017 13:46 Modified: 14 Jun 2018 19:52
Reporter: Neil S Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: Migration Severity:S3 (Non-critical)
Version:6.3.10 OS:Any
Assigned to: CPU Architecture:Any
Tags: DbMySQLFE, forward-engineering, python

[3 Jan 2017 13:46] Neil S
Description:
DbMySQLFE's generateSQLCreateStatements() takes a Dict of options and looks like it tries to pass it to the .cpp layer but doesn't seem to make it as setting them has no effect.

These options are available as a list of tickboxes on the first page of the 'Forward Engineer SQL Script' wizard.

Here is the list of options in .cpp:
https://github.com/mysql/mysql-workbench/blob/406ec7d6900a806c16e42f9fd15f0d3f6111c18a/plu...

How to repeat:
Execute a forward-engineering export from the commandline using the script here:

https://github.com/tomoemon/mwb2sql/blob/master/mwb2sql.sh

Set an option in the dictionary at the end of line 19, for example 'OmitSchemata' (see link in the description for options used by the cpp layer) - it will have no effect.
[15 Feb 2018 10:57] MySQL Verification Team
Hello Neil,

Thank you for the report and feedback.
I discussed internally with the WB developer and confirmed that it works as expected. Could you please try latest WB and confirm if you are still seeing the issue? Thank you! 

Also, this is the file : https://github.com/mysql/mysql-workbench/blob/6.3/modules/db.mysql/db_mysql_fe_grt.py  line 228, first arg is path and then line 244 file is created from passed path

-- WB 6.3.10

ushastry@ubuntu1604lts:~$ cat /etc/*release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=16.04
DISTRIB_CODENAME=xenial
DISTRIB_DESCRIPTION="Ubuntu 16.04.2 LTS"
NAME="Ubuntu"
VERSION="16.04.2 LTS (Xenial Xerus)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 16.04.2 LTS"
VERSION_ID="16.04"
HOME_URL="http://www.ubuntu.com/"
SUPPORT_URL="http://help.ubuntu.com/"
BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/"
VERSION_CODENAME=xenial
UBUNTU_CODENAME=xenial
ushastry@ubuntu1604lts:~$ dpkg -l|grep mysql-workbench
ii  mysql-workbench-community                  6.3.10-1ubuntu16.04                                 amd64        MySQL Workbench
ushastry@ubuntu1604lts:~$ 

ushastry@ubuntu1604lts:~$ ls -l mwb2sql.sh test*
-rwxr-xr-x 1 ushastry ushastry  591 Feb 15 05:42 mwb2sql.sh
-rw-rw-r-- 1 ushastry ushastry 4863 Feb 15 05:45 test.mwb

ushastry@ubuntu1604lts:~$ sh mwb2sql.sh /home/ushastry/test.mwb /home/ushastry/test.sql
Found /usr/lib/x86_64-linux-gnu/libproj.so.9
Note: the "open" parameter is deprecated and will be removed in a future version of MySQL Workbench
Ready.
ushastry@ubuntu1604lts:~$ ls -l mwb2sql.sh test*
-rwxr-xr-x 1 ushastry ushastry  591 Feb 15 05:42 mwb2sql.sh
-rw-rw-r-- 1 ushastry ushastry 4863 Feb 15 05:45 test.mwb
-rw-rw-r-- 1 ushastry ushastry  962 Feb 15 05:56 test.sql
ushastry@ubuntu1604lts:~$ cat test.sql 
-- ----------------------------------------------------------------------------
-- MySQL Workbench Migration
-- Migrated Schemata: mydb
-- Source Schemata: 
-- Created: Thu Feb 15 05:56:19 2018
-- Workbench Version: 6.3.10
-- ----------------------------------------------------------------------------

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------------------------
-- Schema mydb
-- ----------------------------------------------------------------------------
DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;

-- ----------------------------------------------------------------------------
-- Table mydb.table1
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`table1` (
  `idtable1` INT NOT NULL,
  `table1col` VARCHAR(45) NULL,
  PRIMARY KEY (`idtable1`))
ENGINE = InnoDB;
SET FOREIGN_KEY_CHECKS = 1;

Thanks,
Umesh
[15 Feb 2018 16:32] Neil S
Thanks for looking at this but, no, the options still seem to be ignored.

If you edit lines 19 and 20 of mwb2sql.sh so that they pass the OmitSchemata option:

fe.generateSQLCreateStatements(c, c.version, {'OmitSchemata':1})
fe.createScriptForCatalogObjects(os.getenv('OUTPUT'), c, {'OmitSchemata':1})" \

the option has no effect, the schema name is in every 'CREATE TABLE' statement in the created script.
But if you use the GUI to do an export and tick 'Omit Schema Qualifer in Object Names' the schema name is not included in the generated 'CREATE TABLE' statement.

The other options also seem to have no effect.

This is using MySQL Workbench CE (GPL) 6.3.10 CE build 12092614
[16 Feb 2018 10:45] MySQL Verification Team
Thank you for the feedback.

### 
ushastry@ubuntu1604lts:~$ cat mwb2sql.sh 
# generate sql from mwb
# usage: sh mwb2sql.sh {mwb file} {output file}
# prepare: set env MYSQL_WORKBENCH

if [ "$MYSQL_WORKBENCH" = "" ]; then
  export MYSQL_WORKBENCH="mysql-workbench"
fi

export INPUT=$(cd $(dirname $1);pwd)/$(basename $1)
export OUTPUT=$(cd $(dirname $2);pwd)/$(basename $2)

"$MYSQL_WORKBENCH" \
  --open $INPUT \
  --run-python "
import os
import grt
from grt.modules import DbMySQLFE as fe
c = grt.root.wb.doc.physicalModels[0].catalog
fe.generateSQLCreateStatements(c, c.version, {'OmitSchemata':1})
fe.createScriptForCatalogObjects(os.getenv('OUTPUT'), c, {'OmitSchemata':1})" \
--quit-when-done

ushastry@ubuntu1604lts:~$ sh mwb2sql.sh /home/ushastry/test.mwb /home/ushastry/test.sql
Found /usr/lib/x86_64-linux-gnu/libproj.so.9
Note: the "open" parameter is deprecated and will be removed in a future version of MySQL Workbench
Ready.

ushastry@ubuntu1604lts:~$ cat test.sql 
-- ----------------------------------------------------------------------------
-- MySQL Workbench Migration
-- Migrated Schemata: mydb
-- Source Schemata: 
-- Created: Fri Feb 16 05:45:43 2018
-- Workbench Version: 6.3.10
-- ----------------------------------------------------------------------------

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------------------------
-- Schema mydb
-- ----------------------------------------------------------------------------
DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;

-- ----------------------------------------------------------------------------
-- Table mydb.table1
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`table1` (
  `idtable1` INT NOT NULL,
  `table1col` VARCHAR(45) NULL,
  PRIMARY KEY (`idtable1`))
ENGINE = InnoDB;
SET FOREIGN_KEY_CHECKS = 1;
[6 Apr 2018 5:43] MySQL Verification Team
Bug #90313 marked as duplicate of this one
[14 Jun 2018 17:11] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 8.0.12 release, and here's the changelog entry:

When using the 'OmitSchemata':1 parameter in a Python script to generate a
schema from an .mwb file automatically, the resulting SQL script still
included the schema in each table name. This fix introduces the
OmitSchemas option, which replaces both the UseShortNames and OmitSchemata
options, to eliminate the schema name as expected.

Thank you for the bug report.
[14 Jun 2018 19:52] Neil S
Hi, thanks very much!

Is it just `OmitSchemas` or should all the parameters listed in the .cpp linked above work? 

It looks like the intent was to match the checkbox options offered in the export dialog in the GUI.