UseCasesForMySQLWorkBenchIntegration

Use Cases for MySQL Workbench Integration

MySQL Workbench provides powerful E/R diagramming and schema change management features, including a powerful scripting language that lets you build plugins which work with database metadata to do interesting things like schema refactoring.

It would be great to take advantage of these features to provide a more powerful set of database features in NetBeans. In particular, what can we do to take advantage of strong database tooling in Workbench integrated into a complete application development environment like NetBeans?

Here are some ideas, more to come

Basic Bundling

Bundle MySQL Workbench with NB and make it possible to launch it from within NB (e.g. select a MySQL server and launch it, passing in the connection information for the server)

Schema Refactoring

Use the Workbench APIs to enable the ability to refactor a schema, e.g change a table name or a column name, extract a table, combine tables, etc.

Migration Support

Use the Workbench APIs to enable building and running migration scripts, similar to Ruby on Rails. This comes up a lot as something users really want.

I've thought about this some more, and here is how I see it working at a fairly high level. I need to learn more about how Ruby on Rails does it, because they've been doing this a a long time and have worked out the kinks. But here's the general idea.

A NB project has a source directory called

migrations

The user chooses what database they want to use when creating migrations.

User uses whatever tool/approach she wants to create a schema in their 'dev' database.

The users chooses 'save migration', and the schema definition is saved as the revision 0 migration, e.g.

myschema-0.xml

. This is the initial schema definition. We use a human-readable/modifiable database-independent format (I am betting Workbench has such a format)

The user gets a connection to a 'dev' database and applies the migration.

User then modifies the schema, and then saves this as a new revision of their schema. In the

migrations

folder we save {myschema-1-up.xml} (to go from revision 0 to 1) and myschema-1-down.xml (to go from revision 1 to 0)

User says 'Save Migrations as SQL', chooses a folder and a target database vendor/version, and the appropriate SQL files are generated.

User applies migrations to the 'dev' database and

myschema-1-up

is run.

User applies migrations to the a new 'test' database and

myschema-0

and {myschema-1-up} is run

User decides to revert back to revision 0 in the 'test' database and

myschema-1-down

is run.[1]

When the user deploys the app, there is a way to automatically apply the migrations when the app first starts up or is first deployed. Need to think about this further.

Migrations are checked in to SCM and managed as any other source file in the project.

1 Note we have a version table as part of the schema which tells us the version of the schema in the database. This does preclude the user making manual changes to the schema - perhaps we can detect that.

Include Workbench project(s) inside an NB project

Make it possible to add Workbench project files to a NB project so a development team can keep it all together as a project in an SCM system and collaborate with it.

Workbench Scripting Editor

Provide a Workbench scripting editor with completion and highlighting. But this could just as easily be done inside Workbench.

NB API for Database Metadata

Build a metadata API on top of Workbench schema metadata that other modules within NB can use to provide intelligent database support.

Generate PHP Data Objects from Database Metadata

Generate PHP Data Objects (PDO) from database tables (very hand-wavy here, I know nothing about PDO right now, or even if it's important to PHP developers)

Integrate Workbench Natively Into NetBeans

Provide a more integrated experience by having Workbench run *inside* NB with a consistent look-and-feel. This would also allow us to provide a smoother sharing between the NetBeans DB Explorer and Workbench functionality. One potential idea is to build a Java GUI on top of the Workbench C API using Java Native Access (JNA) to communicate with the API. We need to understand the technical (complexity, performance) feasibility of such an approach.