Give and Take

Have you ever looked at the source code of MySQL Workbench? There are quite some hidden gems there, e.g. the OpenGL canvas and our latest addtion: a new UI port of the famous open source edit control Scintilla. This port allows us to use this fantastic editor control natively on Mac OS X Leopard as a Cocoa framework.

Scintilla, native Cocoa port on OS X In fact, we are using Scintilla for a long time already, mainly on Windows. It allows us to give you a powerful editor interface for SQL code. This includes features like syntax highlighting, text markers (e.g. for marking syntax errors), code folding, code completion and many more. However, so far we could not use it on OS X. There is a Carbon port available, but that did not work well (particularly, if the rest of the UI is using Cocoa) so we decided to write our own port for Cocoa, taking the Carbon code as the starting point. This new framework is by far not complete but works already so well that we have it included in our recently published 5.2. alpha release. But we thought there should be more than that. Since we are committed to open source software (even though we have some productivity features in our SE version of MySQL Workbench, which are not available in source code, however the vast majority of the code is open) we decided to publish our port as a separate project, so that also users who do not use MySQL Workbench, but Scintilla, can make use of it. In the good tradition of publishing MySQL code on Launchpad we created a new project called Scintilla-Cocoa, where everybody can see what changed and can download the new code. Currently you have to use a Bazaar client to create a local branch of the Launchpad project in order to actually get the code.

Additionally, we sent the author of the Scintilla project Neil Hodgson a patch so that he can incorporate our code into the main distribution. If anybody of you is interested in helping to complete the Cocoa port let us know. We can give you write access to the Launchpad project where you can directly work on the actual code.

Join the MySQL Workbench Team!

If you are a Mac developer, interested in a new professional challenge and like what we have done with MySQL Workbench so far, please consider applying for our open position in the Workbench team.We are looking for an Objective-C / UI coding wunderkind who is also not easily scared by C++.

You can be located anywhere around the world, only hard requirements are that you are comfortable with working from home, have a decent internet connection and are not too far from an airport.We would be happy to have you on the team.

More details about the job below. To speed up the hire process please send your CV to workbench@sun.com. We are preferring people with open source background so please do not forget to include links to your open source work.

Job Summary:

Sun Microsystems is looking for a strong Mac UI developer with in-depth knowledge of Objective-C, C++, X-Code and the Mac OS X 10.5 operating system. Responsibilities will include ownership of upcoming Mac versions of the MySQL Workbench product, user interface design and integration with C++ backend as well as extensive unit- and UI testing.

Job Description

Sun Microsystems, the developer of the world’s most popular open source database MySQL, is looking for a Software Engineer to join a growing team of professionals.  The ideal candidate must have excellent experience in Mac application- and user interface development with X-Code using Objective-C as well as good C++ knowledge.Join the #1 open-source software company, with great benefits, competitive salary, the flexibility to work from home, and the experience of interacting with a worldwide, multi-cultural workforce!

Responsibilities

  • Take responsibility for Mac versions of the MySQL Workbench product
  • Design and develop sophisticated Mac user interfaces
  • Work on the integration of C++ back-end implementation and Objective-C UI code
  • Develop and maintain automated unit-/UI- tests on the Mac platform

Qualifications

  • Minimum 6+ years hands-on Software Development on the Mac platform with strong focus on UI development work
  • Strong background in database technologies and previous experience in maintaining/administrating or developing for DBMS systems (MySQL, SQLite, Oracle, MS SQL Server, DB2 and others)
  • Strong background in SQL (experience developing applications using complex SQL structures), scripting languages (Lua / Python)
  • Ability to define, create, document and verify testing methods and results
  • Strong knowledge of the Mac operating system with in-depth experience of the current APIs found in OS X 10.5
  • Strong knowledge in Database concepts
  • Ability to work independently and remotely with limited supervision
  • Fluent written and spoken English
Desired:
  • Experience with C#/.Net and extensive knowledge of scripting languages Lua, Python, Ruby
  • Experience on the Linux platform
  • Background in QA methodologies and experience using UI test automation tools

Python Scripting in Workbench

Updated: 2010-8-11 updated sample code to reflect changes in plugin registration API

Python support has been added to the latest version of MySQL Workbench.

In addition to Lua, you can now write scripts and modules or interact with the GRT shell using the Python language. The integration allows you to use GRT objects and modules mostly in the same way you would do with normal Python objects. The built-in grt module contains everything related to the GRT that’s exposed to Python, including:

  • custom types for GRT lists, dicts and objects
  • wrappers for GRT classes, that can be used and instantiated as a normal Python class;
  • wrappers for registered GRT modules, that can be used like normal modules;
  • a reference to the root node of the GRT globals tree;

You can inspect these objects with the standard dir() command and in some cases with help(), to access the built-in documentation (although still incomplete).

Below is a sample shell session demonstrating the Python support:

>>> import grt

>>> dir(grt)
[‘DICT’, ‘DOUBLE’, ‘Dict’, ‘Function’, ‘INT’, ‘LIST’, ‘List’, ‘Method’, ‘Module’, ‘OBJECT’, ‘Object’, ‘STRING’, ‘__GRT__’, ‘__doc__’, ‘__name__’, ‘classes’, ‘get’, ‘modules’, ‘root’, ‘send_error’, ‘send_info’, ‘send_output’, ‘send_warning’, ‘softspace’, ‘write’]

>>> grt.classes
<module ‘grt.classes’ (built-in)>

>>> dir(grt.classes)

[‘GrtLogEntry’, ‘GrtLogObject’, ‘GrtMessage’, ‘GrtNamedObject’, ‘GrtObject’, ‘GrtStoredNote’, ‘GrtVersion’, ‘__doc__’, ‘__name__’, ‘app_Application’, ‘app_CommandItem’, ‘app_CustomDataField’, ‘app_Document’, ‘app_DocumentInfo’, ‘app_DocumentPlugin’, ‘app_Info’, ….

>>> for schema in grt.root.wb.doc.physicalModels[0].catalog.schemata:
… print schema.name

mydb
sakila

>>> table=grt.root.wb.doc.physicalModels[0].catalog.schemata[1].tables[0]

>>> dir(table)
[‘__callmethod__’, ‘__class__’, ‘__delattr__’, ‘__dict__’, ‘__doc__’, ‘__getattribute__’, ‘__grtclassname__’, ‘__hash__’, ‘__init__’, ‘__module__’, ‘__new__’, ‘__reduce__’, ‘__reduce_ex__’, ‘__repr__’, ‘__setattr__’, ‘__str__’, ‘__weakref__’, ‘addColumn’, ‘addIndex’, ‘addPrimaryKeyColumn’, ‘avgRowLength’, ‘checksum’, ‘columns’, ‘comment’, ‘commentedOut’, ‘connection’, ‘connectionString’, ‘createDate’, ‘createForeignKey’, ‘customData’, ‘defaultCharacterSetName’, ‘defaultCollationName’, ‘delayKeyWrite’, ‘foreignKeys’, ‘indices’, ‘inserts’, ‘isDependantTable’, ‘isForeignKeyColumn’, ‘isPrimaryKeyColumn’, ‘isStub’, ‘isSystem’, ‘isTemporary’, ‘lastChangeDate’, ‘maxRows’, ‘mergeInsert’, ‘mergeUnion’, ‘minRows’, ‘modelOnly’, ‘name’, ‘nextAutoInc’, ‘oldName’, ‘owner’, ‘packKeys’, ‘partitionCount’, ‘partitionDefinitions’, ‘partitionExpression’, ‘partitionType’, ‘password’, ‘primaryKey’, ‘raidChunkSize’, ‘raidChunks’, ‘raidType’, ‘removeColumn’, ‘removeForeignKey’, ‘removeIndex’, ‘removePrimaryKeyColumn’, ‘rowFormat’, ‘subpartitionCount’, ‘subpartitionExpression’, ‘subpartitionType’, ‘tableDataDir’, ‘tableEngine’, ‘tableIndexDir’, ‘temp_sql’, ‘temporaryScope’, ‘triggers’]

>>> table.name
‘actor’

>>> table.name= table.name.upper()

>>> table.name
‘ACTOR’

>>> table.columns[1].formattedType(1)
‘VARCHAR(45)’

>>> isinstance(table, grt.classes.db_mysql_Table)
True

>>> dir(grt.modules)
[‘DbMySQL’, ‘DbUtils’, ‘MySQLDbDiffReportingModule’, ‘MySQLDbModule’, ‘MySQLEditorsModule’, ‘MySQLSchemaReportingModule’, ‘MysqlSqlFacade’, ‘PluginManager’, ‘PropelExport’, ‘PyWbUtils’, ‘WbEditorsModule’, ‘WbModel’, ‘WbModuleValidation’, ‘WbModuleValidationMySQL’, ‘WbMysqlImport’, ‘Workbench’, ‘__doc__’, ‘__name__’]

>>> from grt.modules import Workbench
>>> Workbench.copyToClipboard(“HELLO WORLD”)
1L

Modules and Plugins

Writing Workbench modules and plugins is very simple task in Python. For modules (collections of routines available to other scripts and modules, from any language); all you need to do is to import the wb module and call DefineModule() with a few arguments. Defining a function you wrote to be exported can be done using the @export decorator from the object returned by DefineModule().

To make your module also export plugins (a function that is exposed to the user in menus or others ways), the @plugin can be used in addition to @export.

Here’s a sample Python module, exporting a function which also works as a Plugin, callable from Plugins -> Catalog -> . It will simply dump a list of all columns from all tables in all schemata in your model to the Output pane.

# import the wb module, must be imported this way for the automatic module setup to work

from wb import *

# import the grt module

import grt

# define this Python module as a GRT module

ModuleInfo=DefineModule(name= “PyWbUtils”, author= “Sun Microsystems Inc.”, version=“1.0”)

# this is just a function used by the plugin, it’s not exported

def printTableLine(fields, filler= ” “):

print “|”,

for text, size in fields:

print text.ljust(size, filler), “|”,

print

# @wbexport makes this function be exported by the module and also describes the return and

# argument types of the function

# @wbplugin defines the name of the plugin to “wb.catalog.util.dumpColumns”, sets the caption to be

# shown in places like the menu, where to take input arguments from and also that it should be included

# in the Catalog submenu in Plugins.

@ModuleInfo.plugin(“wb.catalog.util.dumpColumns”, caption= “Dump All Table Columns”, input= [wbinputs.currentCatalog()], pluginMenu= “Catalog”)

@ModuleInfo.export(grt.INT, grt.classes.db_Catalog)

def printAllColumns(catalog):

lines= []

schemalen= 0

tablelen= 0

columnlen= 0

typelen= 0

for schema in catalog.schemata:

schemalen= max(schemalen, len(schema.name))

for table in schema.tables:

tablelen= max(tablelen, len(table.name))

for column in table.columns:

columnlen= max(columnlen, len(column.name))

typelen= max(typelen, len(column.formattedType(1)))

lines.append((schema.name, table.name, column.name, column.formattedType(1)))

printTableLine([(“-“, schemalen),
(“-“, tablelen),
(“-“, columnlen),
(“-“, typelen)], “-“)

printTableLine([(“Schema”, schemalen),
(“Table”, tablelen),
(“Column”, columnlen),
(“Type”, typelen)])

printTableLine([(“-“, schemalen),
(“-“, tablelen),
(“-“, columnlen),
(“-“, typelen)], “-“)

for s,t,c,dt in lines:

printTableLine([(s, schemalen), (t, tablelen), (c, columnlen), (dt, typelen)])

printTableLine([(“-“, schemalen),
(“-“, tablelen),
(“-“, columnlen),
(“-“, typelen)], “-“)

span style=”color: #cdbe70″>print len(lines), “columns printed”

return 0

Truncated output for the sakila model:

	| ------ | ------- | ----------- | ------------- |
	| Schema | Table   | Column      | Type          |
	| ------ | ------- | ----------- | ------------- |
	| sakila | actor   | actor_id    | SMALLINT      |
	| sakila | actor   | first_name  | VARCHAR(45)   |
	| sakila | actor   | last_name   | VARCHAR(45)   |
	| sakila | actor   | last_update | TIMESTAMP     |
	| sakila | address | address_id  | SMALLINT      |
	| sakila | address | address     | VARCHAR(50)   |
	| sakila | address | address2    | VARCHAR(50)   |
	| sakila | address | district    | VARCHAR(20)   |
	| sakila | address | city_id     | SMALLINT      |
	| sakila | address | postal_code | VARCHAR(10)   |
	....
	| ------ | ------- | ----------- | ------------- |
	89 columns printed

With the large amount of libraries available for Python, the possibilities you have for writing Workbench plugins and modules are now much bigger.

Also, to facilitate executing your own scripts, a new command to run script files has been added to the Tools menu.

Automate Figure Arrangement In Workbench

Initiated by a question on the forum I took some time to exercise my Workbench-Scripting-Knowledge. The question was, if it’s possible to do some figure-arrangement/organizing via scripts. To be more precise, the task I had to master was to get tables that have names starting with a given prefix moved into a new layer using scripts.

The function to create a new object – layer in this case – was an easy one. All you have to do is to use the function grtV.newObj(“workbench.model.Layer”). This function returns a handle to the created Layer. Finally, set the visual properties (color, position) and most important, the owner has to be set to the containing Diagram-object. Last step is to add the layer to the “layers” property of the Diagram-object.
The second step, which i put into a separate function, is to ask the user for the prefix to filter the desired tables/objects that should get grouped onto the new layer. Next, loop through the figures on the rootLayer, compare the names with the given prefix and if match add the object to the “figures” property of the destination layer, remove it from rootLayer.figures and adjust the figure coordinates to line them up in a cascading arrangement.

Fetch the attached .zip file and follow the instructions in the README to add the script to your copy of workbench. This is only a basic implementation so feel free to expand/improve it to perform more sophisticated operations on your models.

Download the scriptfile: organizefigures.zip

Pimp My Workbench

Some weeks ago there was a request about adding a feature that keeps (or gets) datatypes of colums, which are connected via foreign keys, in sync. While we have added this to our ToDo list, I thought of doing some simple LUA exercises to give the user at least a funtion to check if the FKs in the model all have columns with matching datatypes.
As it turned out such tasks can be done quite easily using our GRT-shell. And by writing some functions you can add features to Workbenchs interface.

To get in touch with the shell/GRT environment inside Workbench click the tiny black prompt-window-icon in the top right area of workbench (or choose ‘View->Advanced->GRT Shell’ from the main menu).  Some items in the UI are swapped/added: the sidebar on the right side gets a new tab named ‘GRT Shell’ featuring some more tool-panes (GRT Tree, Modules, Inspector) and on the bottom part of the main window – where the editors usually show up – a black shell Console will pop up.
At the top of the sidebar you can see the module-file-browser. By default the only two files in there are the ones containing our standard modules. When you want to dive further into scripting workbench, its best to look at the functions in the standard-module-files. When you’re ready to add some custom functions to workbench simply create a new user module (click at the “new”-icon at the top of the sidebar) and select the “LUA GRT Module File” Template, give the file a name and click “Add”. You’ll find the needed functions for registring your modules in workbench already set up there for you to adapt. The comments in the code will point out the parts you need to modify. So lets start extending!

As an additional example, I’m adding my approach to the check-FK-problem mentioned at the beginning of this post. Grab the zip, unpack it and put the .lua files into your Workbench-modules directory (detailed instructions can be found in the readme inside the .zip)
UserModules for checking FK-columns consistency

MySQL Workbench progress update on Linux port

As you probably know, or at least heard, we are currently porting Workbench to Linux. Generally speaking the porting process is split in several stages. The first one is to compile non-GUI Back-End which represents about 80% of the total application code. The next stage is to ensure that unit-tests are run correctly for the ported stuff. The third is to create user interface and to bind it to the back-end/core. After that we will have alpha version of Workbench for Linux.
Regarding tests, actually a portion of unit-tests are already passed. These are 121 of 122 going well. At the moment we are working on non-GUI back-end, and core part is compiled and run, so now the modules and plugins are in progress. I must admit that process of porting is pretty smooth, most of the code has already been prepared with Linux/OS X ports in mind. I will be posting our progress on the porting efforts frequently, please keep checking our blog.

Live from the Workbench Kiev Meeting

It is 23:30 here in Kiev and we are closing the first day of our team meeting.

After my Kick-Off and general discussion of the road-map Tax took over and was discussing the QA aspect of our WB 5.0 releases so far. We analyzed the rate of incoming bugs vs. the number of bug fixes and the time spans between Alpha, Beta, RCs and GA releases. That helps us to make better predictions in the future.

After the lunch break Alfredo presented his conversion of the GRT (WB core system) from the current C implementation to a cleaner C++ version and we forgot about the time discussing all the details of this complex tasks. We will squeeze in the rest of the planned sessions for today (Linux Porting Challenges, Feature Discussion – Visual Query Builder) tomorrow morning – but this was a very important discussion to have.

We went out for dinner and just as we walked outside it started raining. The center of Kiev is very beautiful and usually we just walk to find a place near by. But because of the rain we just took the subway. After returning to the apartment I checked emails and am doing this blog post. A shower and bedtime coming up.

We will continue tomorrow, a lot of interesting stuff is on the agenda!

MySQL Workbench Team Meeting Coming Up

After our MySQL Workbench 5.0 GA release and a hotfix release a week later it is time to start our efforts on the next releases. As many of you might already know, my team is highly distributed and when doing planning tasks it is always good to sit together in a single room. So we will be meeting in Kiev, Ukraine next week.We are going to discuss the progress we have already made on the Linux / OS X / Solaris ports but the main focus will be in the new features for the next main release.A big thank you to everybody who posted their ideas on the forum. If you have not posted yet you are welcome to do so. Everything that is submitted before next week will be considered.

Next MySQL Workbench 5.0.20 Release Almost Ready To Ship

As promised we are continuing our strong efforts after reaching GA and our announcement at the MySQL Users Conference (find a nice press article here).

Alfredo managed to fix a serious bug that almost seemed to be of random nature and happened on certain OnMouseDown and OnMouseUp events on the canvas.

Another thing that got improved is the drawing order of connections between table figures on the canvas. Previously the connections would be drawn on top of tables, resulting in a messy image. Now connections are always drawn behind tables. To make that work we had to remove the nesting of layers – a feature that does not really make sense for a database tool anyway.

The team will meet in the week of May 12th in Kiev where we are going to define the detailed plans for Workbench 5.1 and 5.5. Until then we will work on WB 5.0, fixing bugs and improving the scripting interface to make it more convenient to write plugins.

Please keep reporting bugs and blog about your experiences. This is the best way to support the project. (Or pony up the $99 for Standard Edition if you don’t have time for that and still want to help 🙂 )

Collecting ideas for Workbench – SQL IDE

Hi Workbench Users,

Now that we got the RC1 out, I would like to start collecting ideas of what you would like to see in the MySQL Workbench 5.1 release that will happen later this year.

Our current plan is (apart to improve the existing features and workflow) to add one major feature for WB 5.1 – and that is SQL querying functionality. Please think of it as an improved, extended MySQL Query Browser embedded right into the MySQL Workbench framework.

My vision is to turn MySQL Workbench into a fully featured SQL IDE. The single tool a MySQL database developer needs to start up to do all his database work.

A few key points that come to my mind.

  • Database schema design, creation and maintenance (already there)
  • Default data management (“CREATE INSERTS”, already there, can be improved)
  • Database SQL querying
  • Result set handling (like in QB, but improved)
  • Visual query builder (I liked the Oracle XE web-based query builder)
  • Improved SQL code editing (syntax highlighting, auto completion, …)
  • Stored Procedures debugger
  • Full scripting and plugin support
  • Exchange with Netbeans IDE

Please add your own points and also provide input how you would like to see the features implemented.

References to other tools, e.g. Oracle SQL Developer (project Raptor), MSSQL Query Analyzer, SQLyog, … are welcome. Please point out what you like about their querying interface and how we could improve upon that.

Please post your ideas on the following forum:

http://forums.mysql.com/list.php?161 

I am looking forward to your input,
Mike