MySQL Workbench Scripting
This article give a short introduction to how to write scripts for the MySQL Workbench. Further it shows how functions can be turned into plugins.
Contents |
[edit] Scripting Basics
In the GRT Shell (F4) you can use ? to get help. We added a number of Lua modules to be able to work with GRT objects.
/ > ? grtV (for GRT Values) grtS (for GRT Struct definitions) grtM (for GRT Modules) ...
?grtV to get help for the grtV module ?grtV.getGlobal to get help for a specific function
GRT Objects are MetaTables in Lua, so you need to convert the GRT values to real Lua values for things like =. We will try to overload the operators in a future version, currently one has to use grtV.toLua()
First thing to understand is, that all objects the application works with are stored in the GRT globals tree.
/ > opts= grtV.getGlobal("/app/commonOptions") ! Only in Windows, not Linux or Mac !
Inspect the globals tree with the Object Tree on the right. Or print right in the shell.
/ > print(opts)
{
DefaultCodeFontCharset = "1"
DefaultCodeFontHeight = "-11"
DefaultCodeFontName = "Bitstream Vera Sans Mono"
DefaultCodeFontWidth = "7"
DefaultFontHeight = "13"
DefaultFontName = "Tahoma"
}
To change a value you do
/ > opts.DefaultFontHeight= "14"
or
/ > opts["DefaultFontHeight"]= "13"
This should change some options in realtime.
There are 5 different GRT value types.
integer string real list dict
[edit] Adding a new Schema to the Catalog
When you inspect the globals tree you will find the type to the left of the object name.
schemata list [dict: db.mysql.Schema]
Which means that this is a "typed" list, it can only hold dicts that conform to the "db.mysql.Schema" struct. The struct definitions are in the xml directory.
/ > schemata= grtV.getGlobal("/workbench/catalog/schemata")
Now, for example, insert a new schema. First create it.
/ > mySchema= grtV.newObj("db.mysql.Schema", "my_schema", "", "")
You can print it.
/ > print(mySchema)
{
_id = "{2418A083-23D0-43F2-BC39-ADB544CFCD19}"
comment = ""
commentedOut = 0
customData = NULL
defaultCharacterSetName = ""
defaultCollationName = ""
name = "my_schema"
oldName = ""
...
now, let's add it to the list of schemata.
/ > grtV.insert(schemata, mySchema)
Refresh the Object Tree with the right mouse button -> Refresh and you will see the new schema added to the schemata list.
Looping over all schemata and printing their names. You can enter multiline commands when you press Shift+Enter. You can use the cursor keys to move up and down. To get the last command, you do Ctrl+Up / Ctrl+Down.
/ > for i= 1, grtV.getn(schemata) do >> print(schemata[i].name) >> end
"test" "my_schema"
[edit] Adding a Table to the Catalog
Adding a table.
/ > myTbl= grtV.newObj("db.mysql.Table", "my_table", "", "")
/ > grtV.insert(mySchema.tables, myTbl)
Refresh the globals tree again to see the new table added to the schema.
You can also inspect the table. Enter the variable name instead of "GRT Globals Tree", in our case myTbl. Press Enter. It will display the table in the Object tree and in the Object Inspector.
Adding an element for the table on the canvas.
Get the main view.
/ > curView= grtV.getGlobal("/workbench/model").currentView
Note that currentView in the Object inspector is just a GUID. It is a reference to "/workbench/model/views/0". When accessed with obj.ref we automatically resolve the reference so curView will hold the actual object.
/ > print(curView)
Create a new table element. Note that we have to convert the GRT _id string value to a real Lua value.
/ > tblElm= grtV.newObj("db.workbench.TableElement", "myTblElement", "", grtV.toLua(curView._id))
Assign our schema table to the element.
/ > tblElm.table= myTbl
Set the position.
/ > tblElm.left= 100
/ > tblElm.top= 50
Add the element to the current view's elements.
/ > grtV.insert(curView.elements, tblElm)
Show the element.
/ > tblElm.visible= 1
Change the color.
/ > tblElm.color= "red"
Hope that gives you a basic idea how things work.
[edit] Creating functions
You can also create functions.
/ > function listSchemata()
>> local schemata= grtV.getGlobal("/workbench/catalog/schemata")
>> local i
>>
>> for i=1, grtV.getn(schemata) do
>> print(schemata[i].name)
>> end
>> end
/ > listSchemata() "test" "my_schema"
[edit] Checking for errors
If there are errors, you can use
/ > print(grtError)
to inspect the last error message.
[edit] Creating a Plugin out of a Function
To make a plugin out of that function, please create a new file in the lua subdir of the WB installation folder, e.g. WorkbenchMyPlugins.lua
I love to use the SciTE editor [1]
It has nice Lua syntax highlighting and I have extended that to also highlight our Lua extension modules. Find a lua.properties file attached, simply replace the one in SciTE installdir.
Each Lua module file needs a function with the name getModuleInfo(). It has to return a simple struct following this format.
function getModuleInfo()
local moduleInfo= {
name= "WorkbenchMyPlugins",
functions= {
"getPluginInfo::",
"listSchemata::"
},
extends= "PluginInfo"
}
return moduleInfo
end
Second, it needs a getPluginInfo() function that returns information about the plugin you write.
function getPluginInfo(args)
local pluginList= grtV.newList("dict", "base.Plugin")
local plugin
plugin= grtV.newObj("base.Plugin", "listSchemata",
"plugin://com.mysql.grt.base.Plugin.myPlugins.listSchemata", "")
plugin.caption= _("List Schemata")
plugin.description= _("Lists all schemata of the workbench's catalog.")
plugin.groupPath= "Catalog/Reporting"
plugin.moduleName= "WorkbenchMyPlugins"
plugin.moduleFunctionName= "listSchemata"
plugin.categories= {"database"}
plugin.objectStructNames= {}
plugin.singleArgument= 1
grtV.insert(pluginList, plugin)
return grt.success(pluginList)
}
And last the actual plugin function.
function listSchemata()
local schemata= grtV.getGlobal("/workbench/catalog/schemata")
local i
for i=1, grtV.getn(schemata) do
print(schemata[i].name)
end
return grt.success(1)
end
Now start the Workbench and you will see your new module WorkbenchMyPlugins is listed on the Modules tabsheet. Doubleclick it to inspect its functions.
Further, there is a new Menu Item created in the Plugins menu, Catalog -> Reporting -> List Schemata.
If you execute it, you will see the plugin's output on the Plugins tabsheet.
See, it's almost trivial ;)