楼宇自动化与MySQL Workbench脚本:正向工程师SQL创建脚本 [英] Build Automation & MySQL Workbench Scripting: Forward Engineer SQL CREATE SCRIPT

问题描述

我目前正在考虑使软件构建过程自动化，该过程包括MySQL Workbench中定义的数据库模式.

使用Workbench的脚本功能，我想打开一个Workbench文档并将其架构导出为SQL CREATE脚本.

我想知道的是，是否有一个函数可以一步将整个模式导出为Workbench的 File |. 导出 | Forward Engineer SQL CREATE脚本，可自动处理表之间的所有依赖关系.

我在DbMySQL模块中发现了一些可能 可以做的事情(generateSQL(GrtNamedObject, dict, string)和makeSQLExportScript(GrtNamedObject, dict, dict, dict))，但是我对他们期望的参数感到困惑–第一个可能是架构对象，但是其他参数是什么?

有人可以告诉我我的假设是否正确和/或向我提供用法示例吗?

到目前为止，我已经提出了一个手动解决方案(请注意，当前该解决方案尚未根据其FK关系对表进行排序):

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

该问题与>如何使用命令行从MySQL Workbench生成SQL脚本吗?，但是找到的答案确实很抽象，并且没有说明如何实际使用MySQL Workbench的脚本功能.

解决方案

似乎其他 2013年12月得到了答复，由 madhead ，尽管有一些琐碎的代码故障，并且使用的是Python而非Lua，所以这里适用于我的Python版本:

# -*- 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, {})

与循环变体相比看起来很大，但是可能带来一些好处(尚未测试，但我可以想象Workbench能够弄清楚创建表的正确顺序等). /p>

我不确定当我问这个问题时是否已经存在，但是无论如何，这适用于最新版本.

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.

解决方案

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.

这篇关于楼宇自动化与MySQL Workbench脚本:正向工程师SQL创建脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！