MySQL Forums - Workbench - Plugins & Scripts

MySQL Workbench: Running Python Scripts (no replies)

Edwin DeSouza — Thu, 08 Oct 2009 19:02:23 +0000

MySQL Workbench: Running Python Scripts
http://blog.some-abstract-type.com/2009/10/running-python-scripts-within-mysql.html

MySQL Workbench: Enabling Python in the GRT shell (no replies)

Edwin DeSouza — Thu, 08 Oct 2009 16:29:11 +0000

MySQL Workbench: Enabling Python in the GRT shell
http://blog.some-abstract-type.com/2009/10/mysql-workbench-enabling-python-in-grt.html

Lua script for batch commenting BOOL columns (no replies)

Gabriele Tozzi — Tue, 29 Sep 2009 20:55:25 +0000

Hi there,

in the new version of Workbench, BOOLEAN columns are rendered as TINYINT(1) when generating SQL.

This is very annoying to me, because I often manually edit the SQL code and I can no longer distinguish between booleans and very small integers.

I wrote a small lua script to add a *BOOL* comment on every BOOLEAN column, so I can now easily distinguish them.

Hope this will be useful, just my 2c.

Script:

--
-- Inspired from MySQLFKRenaming
--
-- this function is called first by MySQL Workbench core to determine number of plugins in this module and basic plugin info
-- see the comments in the function body and adjust the parameters as appropriate
--

function getModuleInfo()
return {
name= "MySQLBoolCommenter",
author= "Gabriele Tozzi ",
version= "1.0",
implements= "PluginInterface",
functions= {
"getPluginInfo:l:",
"addBoolComments:i:o@db.Catalog"
}
}
end

function objectPluginInput(type)
return grtV.newObj("app.PluginObjectInput", {objectStructName= type})
end

function getPluginInfo()
local l
local plugin

-- create the list of plugins that this module exports
l= grtV.newList("object", "app.Plugin")

-- create a new app.Plugin object for every plugin
plugin= grtV.newObj("app.Plugin", {
name= "wb.catalog.util.addBoolComments",
caption= "Add comments to BOOL columns",
moduleName= "MySQLBoolCommenter",
pluginType= "normal",
moduleFunctionName= "addBoolComments",
inputValues= {objectPluginInput("db.Catalog")},
rating= 100,
showProgress= 0,
groups= {"Catalog/Utilities", "Menu/Utilities"}
})

-- fixup owner
plugin.inputValues[1].owner= plugin

-- add to the list of plugins
grtV.insert(l, plugin)

return l
end

-- function to go through all schemata in catalog and add a comment to all BOOL columns
function addBoolComments(obj)

local i, j, k, schema, tbl, renames, idxrenames, newName, newNameBase, newNameSfx

-- loop over all catalogs in schema
for i = 1, grtV.getn(obj.schemata) do
local table_map = {}
schema = obj.schemata[i]

commented= 0
skipped= 0

print("-- start renaming tables in schema `" .. schema.name .. "` --")

-- loop through all tables
for j = 1, grtV.getn(schema.tables) do

tbl = schema.tables[j]

-- print("-- table `" .. tbl.name .. "` --")

for k = 1, grtV.getn(tbl.columns) do

col = tbl.columns[k]

if string.sub(col.formattedRawType,0,4) == "BOOL" then
-- print("-- column `" .. col.name .. "` IS BOOLEAN")
if string.sub(col.comment,0,6) ~= '*BOOL*' then
print("-- commenting `" .. tbl.name .."`.`" .. col.name .."` --")
newcomment = "*BOOL* " .. col.comment
col.comment = newcomment
commented = commented + 1
else
skipped = skipped + 1
end
end

end
end
print("-- finished commenting. " .. commented .. " done, " .. skipped .. " skipped in schema `" .. schema.name .. "` --")
end

return 0
end

Script to Update Column Names to Standard Abbreviations (no replies)

Julian Bishop — Fri, 18 Sep 2009 03:50:07 +0000

Hi,

Does anyone have a script that will run against the entire schema and update column names to a standard set of abbreviations. For instance, if my table contains a column called ACCOUNT_NUMBER the script would rename it to ACCT_NO based on a dictionary of abbreviations.

The script could contain the dictionary of abbreviations as an array (ACCOUNT = ACCT, NUMBER = NO, etc. etc.). There are probably no more than 300 abbreviations of interest.

Looks like this would be a fairly easy plugin to code - but I have no experience in coding GRT scripts. If someone could get me started in the right direction it would be greatly appreciated.

Thanks.

Conditional Not Working in Script (2 replies)

Eric Salsbury — Mon, 21 Sep 2009 06:24:49 +0000

I have written an update script that is supposed to check individual revision numbers and apply the revisions to a database if they do not exist. The conditional statements are not working, however. I can't seem to figure out what I am doing wrong with it. Even a simple IF 1 = 1 THEN select * from revisions; END IF; statement fails on running. Here is the script:

SET @cur_rev_num = 0;

SET @description = NULL;

-- UPDATE 1 - (PLACE DESCRIPTION HERE) COPY AND PASTE BELOW CODE TO PRIOR TO EDITTING CURRENT REVISION

SET @cur_rev_num := 1; -- REPLACE THIS VALUE WITH CURRENT UPDATE NUMBER

SET @description := "REPLACE THIS WITH DESCRIPTION";

IF (SELECT COUNT(revision_number) FROM revisions WHERE revision_number = @cur_rev_num) = 0 THEN

--PLACE UPDATE CODE HERE
INSERT INTO users (username, password) values ('joe', 'blow');

INSERT INTO revisions (revision_number, description, committed_on) VALUES(@cur_rev_num, @description, NOW());

END IF;

The error I am getting when I run this script is:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (SELECT COUNT(revision_number) FROM revisions WHERE revision_number = @cur_re' at line 1

Can anyone shed some light on why no conditional statement seems to be working currently?

Thanks

Custom python plugins not being installed (1 reply)

Ryan Taylor — Wed, 30 Sep 2009 03:02:41 +0000

I am having difficulty getting a custom python module/plug-in to display in the Plugins menu. I have followed http://dev.mysql.com/workbench/?p=197 to the best of my ability but the plugin menu will not reflect my plugin.

I am running MySQL Workbench 5.1.17 SE and Windows XP SP3. Any help would be appreciated.

Running LUA script in Workbench (no replies)

John Critchley — Mon, 31 Aug 2009 20:14:27 +0000

Hi - this is a pretty newbie question, but I haven't been able to resolve this any other way.

I haven't been able to run an LUA script (plain text file, with .lua extension) from the Tools > Run Workbench Script menu item. I can see the file, but cannot select it in the file browsing window.

I am using MySQL Workbench 5.1.16, rev. 4210 on Mac OS X 10.5.8

I have been trying to run a file containing the script described at this location:
http://dev.mysql.com/workbench/?p=271

by copying the script contents, pasting into a plain text file and saving as a '*.lua' file.

Thanks for any help.

Regards,
-John

Local variables in scripts (no replies)

Andrew Chapman — Tue, 11 Aug 2009 16:25:21 +0000

I'm trying to adjust my database creation scripts to cope with development, test and live environments. In development I use InnoDB and in live/test I use a ndb. I thought I could something like this:

set @ENGINE_NAME='ndb';
set @DISK_STORAGE=' STORAGE DISK ';

select @ENGINE_NAME, @DISK_STORAGE;

create table wibble (
row_id int not null auto_increment,
name varchar(20) not null,
image mediumblob null
) @DISK_STORAGE engine=@ENGINE_NAME;

and then change the variables depending on system.

The select works fine but the create table doesn't. Can anyone tell me why?

script to change PK name (1 reply)

Wladimir Coka — Tue, 04 Aug 2009 14:13:51 +0000

I need to make a simple script to change the name of the PK of all tables, but I dont know how to get the PK column. I know how to loop through all the tables and columns but how can I know which column if PK?

MySQL Workbench: Lua Script to auto-create relationships for MyISAM tables (3 replies)

Edwin DeSouza — Mon, 14 Sep 2009 07:52:59 +0000

MySQL Workbench: Lua Script to auto-create relationships for MyISAM tables
http://dev.mysql.com/workbench/?p=271

SQLite export plugin for MySQL Workbench (no replies)

Thomas Henlich — Wed, 13 May 2009 14:52:54 +0000

Hi everyone,

I'd like to announce the release of my SQLite export plugin for MySQL Workbench. The plugin is available under GPL, just like the Community Edition (OSS) of Workbench for which it was developed.

With this plugin, one can combine the visual database design capabilities of MySQL Workbench together with the high-performance, stand-alone SQLite database.

The plugin creates a SQL create script from a Workbench model. It already supports some advanced features like:
- Creates INTEGER PRIMARY KEY where possible (as a fast alias for rowid)
- Uses the .genfkey feature of sqlite3 command-line client for foreign keys (>=3.6.14 required)
- Exports project metainformation, schema, table and column comments
- Table and column comments are stored right in the SQLite file for persistent documentation

Since I just started this project, room for improvement is surely there.
I'm always open for suggestions, bug reports or feature wishes.

Links:

SQLite export plugin for MySQL Workbench:
http://www.henlich.de/software/sqlite-export-plugin-for-mysql-workbench/

MySQL Workbench:
http://dev.mysql.com/workbench/

parseSqlScriptFile? (no replies)

Guy markey — Wed, 13 May 2009 06:31:49 +0000

G'day,
I'm trying to write a plugin script that will import a mySql script file (Actually it creates a script file and then I want to import it). I have no problem with the createing part and can import the created file using the File>Import>Reverse Engineer MySql Create Script.. menu option, that works fine.
In my script I am trying to use WbMysqlImport:parseSqlScriptFile(db_Catalog, s) to import it, since this seems to be the function that should do it. The problem is it doesn't seem to do anything and there is precious little documentation to tell me wether I'm barking up the wrong tree, so to speak.
When I run my script I get the following output...
Started parsing MySQL SQL script.
Finished parsing MySQL SQL script. Totally processed statements:successful(0), errors(0),warnings(0).

Any hints?

Setting collation and character set defaults (2 replies)

Karsten Wutzke — Tue, 28 Apr 2009 16:23:14 +0000

Where do you set the schema's collation and character set defaults in the WB GUI?

I mean the two settings that are accessible by plugins internally by

schema.defaultCollationName
schema.defaultCharacterSetName

...

When editing tables, the collation dropdown "Collation" has "Schema Default" as the top entry. Where do you change this?

Karsten

PS: is there a reason why the collation is settable on the table and the character set isn't?

Default schema storage engine field? (3 replies)

Karsten Wutzke — Tue, 28 Apr 2009 17:07:07 +0000

I have the following code:

-- set basic options
yaml = yaml .. "options:\n"
yaml = yaml .. " collation: " .. schema.defaultCollationName .. "\n"
yaml = yaml .. " charset: " .. schema.defaultCharacterSetName .. "\n"
yaml = yaml .. " type: " .. "InnoDB" .. "\n\n"

This just generates a piece of YAML code (string) by accessing MySQL Workbench schema attrributes/fiels

schema.defaultCollationName
schema.defaultCharacterSetName

Missing:
schema.defaultStorageEngineName or else

Is there such a property? What's its name?

I mean the one which is settable via "Tools" -> "Options..." -> "MySQL" (tab), the dropdown box calles "Default Storage Engine"

Karsten

Plugin system changed? (11 replies)

Karsten Wutzke — Fri, 24 Apr 2009 16:45:43 +0000

Hello,

I know two object relational mapping export plugins that work under WB 5.0 but not in WB 5.1. What changed?

Where can I find info on how to fix this?

I mean the Propel and Doctrine plugins mentioned here:
http://forums.mysql.com/read.php?153,208229,208229

Karsten

MySQL Workbench: Scripting in Python (no replies)

Edwin DeSouza — Mon, 09 Mar 2009 03:24:32 +0000

MySQL Workbench: Scripting in Python
http://dev.mysql.com/workbench/?p=197

MySQL Workbench: Enabling Python in the GRT shell
http://blog.some-abstract-type.com/2009/10/mysql-workbench-enabling-python-in-grt.html

MySQL Workbench: Running Python Scripts
http://blog.some-abstract-type.com/2009/10/running-python-scripts-within-mysql.html

[REQ] Calculate Table Size (no replies)

Rodrigo Olivares — Wed, 18 Feb 2009 12:29:02 +0000

Have anybody a some script that calculate the table size in order to estimate the size average for the DB?

The idea is that the script list the tables and theirs max row sizes and based in this calculate the entire DB size.

Thanks

LUA-Script For Batch Renaming Of Foreign Keys (no replies)

Edwin DeSouza — Tue, 03 Feb 2009 15:28:50 +0000

LUA-Script For Batch Renaming Of Foreign Keys
http://dev.mysql.com/workbench/?p=190

Use LUA to customize SQL export (3 replies)

Gabriele Tozzi — Wed, 04 Feb 2009 15:56:04 +0000

Hello,

Workbenck "Forward Engineer SQL Create script" doesn't fits my needs.

I would like to write a plugin to accomplish a few simple optimizations:

1. Remove empty queries from the generated script (see bug #39929)
2. Sort tables by Layer and then Name
3. Sort Routines by Layer, Group and then Name

Is this possible? May someone point me to some related documentation/examples to start working on?

I'm new on LUA scripting.

Thank you

Timestamp format (1 reply)

Kalpana Kanthasamy — Tue, 30 Dec 2008 18:56:36 +0000

Hi, how do I alter the timestamp code so that only the year as in YY is displayed in the mysql column, as in if its 2008, only 08 should appear

MySQL Workbench Plugin Tutorial: Load INSERTs from a File (no replies)

Edwin DeSouza — Sat, 13 Dec 2008 00:10:36 +0000

MySQL Workbench Plugin Tutorial: Load INSERTs from a File
http://dev.mysql.com/workbench/?p=176

MySQL Workbench: Lua Library for writing Custom Plugins (no replies)

Edwin DeSouza — Fri, 07 Nov 2008 21:48:44 +0000

MySQL Workbench: Lua Library for writing Custom Plugins
http://dev.mysql.com/workbench/?p=166

MySQL Workbench: Lua Plugin Support
http://dev.mysql.com/workbench/?p=60

Pimp My Workbench (writing Lua plugins)
http://dev.mysql.com/workbench/?p=148

Workbench (2 replies)

Julian Santander — Wed, 05 Nov 2008 07:43:39 +0000

What can I do with Workbench?

So far I've seen:

Workbench:input("Text") that shows an input dialog with "Text" title and returns the text entered into the dialog

Workbench:confirm("text","text") that shows a confirmation dialog.

Workbench:copyToClipboard("text") that copies text to the clipboard.

However I failed to find any document about other capabilities.

Thanks in advance

Julian

XML (2 replies)

Julian Santander — Fri, 31 Oct 2008 19:39:04 +0000

How can I read XML (any XML document) and write XML (any XML) from a plugin/script?

To give more details. I have a proprietary schema design tool that stores data in XML files. I'd like to go to/from the MySQL Workbench.

I guess writing is the easy part... just printing appropriately formated strings.

I tried adding the LuaExpat library to the Lua environment but I got an MSF error (but I only get the error from the workbench or from the MySQLGrtCppShell.exe... but not from the MySQLGrtShell.exe).

I noticed that libxml2.dll is present... so I wonder if it is accessible in any way from the plugins/scripts.

As you can imagine this is on Windows environment. And I'm using 5.0.26 OSS

Thanks in advance

Julian

Use LUA to merge tables into layers (3 replies)

Andrew Johnstone — Thu, 30 Oct 2008 10:24:44 +0000

How can I create a layer/and or group existing tables with a prefix into a particular layer? I have a large database and grouping by layers would be an extremely useful.

Also is there any particular reason that reverse engineering schemas is disabled or has this not yet been implemented?

Thanks

Andrew

Module information (1 reply)

Hans Stevens — Mon, 06 Oct 2008 16:40:44 +0000

Hi

is there already a possibility to see some module information?
Like a small description and how and which arguments it modules expects?

Thanks in advance!

Kind regards
Hans Stevens

Symfony Export Plugin (1 reply)

Johannes Mueller — Fri, 12 Sep 2008 20:59:36 +0000

on
http://trac.symfony-project.org/wiki/SymfonyYamlMyqlWorkbenchPlugin

you'll find a workbench symfony export plugin coded by jason rowe.

MySQL Workbench - Plugins, Scripts, Modules: Articles, Blogs, FAQs (no replies)

Edwin DeSouza — Fri, 05 Sep 2008 20:51:33 +0000

MySQL Workbench: Plugins
http://dev.mysql.com/workbench/?cat=4

MySQL Workbench: Scripting in Python
http://dev.mysql.com/workbench/?p=197

MySQL Workbench: Lua Library for writing Custom Plugins
http://dev.mysql.com/workbench/?p=166

Pimp My Workbench (writing Lua plugins)
http://dev.mysql.com/workbench/?p=148

MySQL Workbench Plugin Tutorial: Load INSERTs from a File
http://dev.mysql.com/workbench/?p=176

MySQL Workbench: LUA-Script For Batch Renaming Of Foreign Keys
http://dev.mysql.com/workbench/?p=190

MySQL Workbench: Automate Figure Arrangement
http://dev.mysql.com/workbench/?p=164

MySQL Workbench: Lua Script to auto-create relationships for MyISAM tables
http://dev.mysql.com/workbench/?p=271

Workbench DoctrineExport plugin (1 reply)

Johannes Mueller — Tue, 05 Aug 2008 19:31:58 +0000

accoring to PropelExport plugin i release an early alpha version of a Doctrine Export Plugin for YAML schemes.

feel free to download:
http://www.tahat.de/downloads/DoctrineExport.grt.lua

If you have questions, suggestions or want to fix a bug. you can contact me via email, which is written in the plugin-code.

db.Column.flags doesn't work properly?? (2 replies)

Johannes Mueller — Tue, 05 Aug 2008 15:52:55 +0000

hi, i've tried to program a plugin in LUA, but it seems to me like there is a problem with db.Column.flags. I expected it to return a list of strings, instead it returns a list of userdata. Which means i can not compare the flags with strings like i want:

local i, flag
for i=1, getV.getn(catalog.schemata[x].tables[y].columns[z].flags) do
flag = catalog.schemata[x].tables[y].columns[z].flags[i]
if ( flag == "UNSIGNED" ) do
-- do something
end
end

Am i correct, or do i have to convert it (but how)? I think in the documentation it's mentioned to be a list of strings. But i'm unsure - so i didn't open a Bug report.