Build Automation & MySQL Workbench Scripting: Forward Engineer SQL CREATE SCRIPT

Question

I'm currently looking into automating a software build process that includes a database schema defined in MySQL Workbench.

Using Workbench's scripting capabilities, I'd like to open a Workbench document and export its schema as an SQL CREATE script.

What I'd like to know is if there is a function that exports the entire schema in one step as Workbench's File | Export | Forward Engineer SQL CREATE Script, automatically handling any dependencies between tables.

I've found some candidates in the DbMySQL module that might do that (generateSQL(GrtNamedObject, dict, string) and makeSQLExportScript(GrtNamedObject, dict, dict, dict)), however I'm confused about the parameters they expect – the first one could be the schema object, but what are the other arguments ?

Could anyone tell me if my assumption is correct and/or provide me with usage examples ?

So far, I've come up with a manual solution (note that this currently does not sort the tables according to their FK relations):

local o = assert(io.open("/tmp/create.sql", "wb"));
foreach_table_all(function (t)
    o:write(DbMySQL:makeCreateScriptForObject(t) .. ";\n\n")
end)
o:close()

The question is related to How to generate SQL Script from MySQL Workbench using Command Line?, however the answer found there is really abstract and tells nothing about actually using the scripting features of MySQL Workbench.

Lone

Answer 1

Seems that other linked question got answered in Dec 2013, courtesy of madhead, albeit with minor trivial code glitches, and in Python rather than Lua, so here the Python version that is working for me:

# -*- coding: utf-8 -*-
# MySQL Workbench Python script
# <description>
# Written in MySQL Workbench 6.0.8

import os
import grt
from grt.modules import DbMySQLFE

c = grt.root.wb.doc.physicalModels[0].catalog
DbMySQLFE.generateSQLCreateStatements(c, c.version, {
    'GenerateDrops' : 1,
    'GenerateSchemaDrops' : 1,
    'OmitSchemata' : 1,
    'GenerateUse' : 1
})
DbMySQLFE.generateSQLCreateStatements(c, c.version, {})
DbMySQLFE.createScriptForCatalogObjects(os.path.dirname(grt.root.wb.docPath) + '/ddl.sql', c, {})

Looks rather big compared to the loop variant but might bring some benefits (haven't tested, but I could imagine Workbench being able to figure out the proper order to create tables etc.).

Also I am unsure about whether this has existed when I was asking the question, but anyway, this works on a recent version.

Lone

Any hint on what option to use for excluding certain tables from the export, as it is possible in the GUI? – Year

See my question. It uses foreach_table_all(), and the callback function can check for the table and just decide not to write it. Simple as that. – Lone

Any way to drop the schema identifier in this approach? – Year

Haven't looked at it, so I'd refer you to the docs/help. But you probably could just hack and use string replace in most cases, I guess. – Lone

I can't find any docs for the options, but looks like they are mainly processed here. Looks like there's both TableFilterList and OmitSchemas – Wyatt