Adding a "logical model" part for creating a new model

[Dennis Becker]

First of all, this topic is complicated to explain, so I try to do my best that everyone does understand why it is important to switch from a "physical model" to a "logical model" in WB.

First of all I want to explain the differences between a a physical model and a logical model. At the moment, WB has implement a physical, which means that your model gets directly optimized for MySQL (with storage engine InnoDB because MyISAM ignores those statements in the "SQL Create Script" you can export). So the logical model is the difference - it implements only basic SQL support which results that every RDBMS support that. With a logical model it may be possible to integrate more databases other than MySQL. As once mentioned (as far as I remember) this is a possible feature for a later version ...

I have started to think about such an implementation how it may possible to integrate multiple databases in WB. Just as short notice: I'm working on a web application which uses the Zend Framework. This framework has a good implementation that you can use different databases but always can use the same code written in your model classes. So I also want to implement multiple database support in my web-application ;)

First of all, the creating mechanisms for db schemes and tables must based on the "logical model", but here everyone should be able to change settings for a special database, so that no one has to search how to setup feature "X" of RDBMS "Y". When you connect to your live database server (maybe sometimes in the future) or when you use the "Forward engineer" to get a "SQL Create Script", you must use the database type (storage engines from MySQL should be treated as own databases - I explain later why) to get the results optimized for this database type! That means vice versa, you can create only once a model and can use it for all supported databases without manually refactoring it!

Why should storage engines be treated like another database?
I thought abount implementing each database as a plugin. these plugins implement the export routines which will be needed to create the different SQL Codes etc.

What are the advantages of such plugins?
I thought about different layers in WB. First of all, there is the "logical model" layer, which I have described above. This layer is connected to the "export layer". Thsi is just an implementation of a Factory Pattern to decide, which plugin should be used as forward engineer. Next is the "plugin layer" where are all the implemented plugins are located. Plugins should implement all methods for "forward engineering" and "reverse engineering". At the end comes a "physical model" layer - it is not an implemented layer in WB, it is just the interface to the RDBMS or the .sql file. One big advantage could be, that you can add a plugin without updating WB or MySQL decides to integrate plugins fixed (so no extendable plugins can be integrated). For example you must buy a plugin for the Oracle interface to use it (there are a lot of licencing possibilies) or it is allowed that open sopurce developers can add custom plugins and publish them on the MySQL website ...


For fast readers, here is a small advantages / disadvantages list from my point of view (haven't thought long about these points, so there are left a lot of arguments):

advantages:
- create only one model for your application and export this for multiple databases
- it will be easier to implement more databases
- improves WB while supporting only MySQL because they were possible to optimize the forward engineer on each storage engine
- the code gets separated and can be fully optimized - that also results that a bug in one parser does not block other parsers
- WB is more flexible for end-user needs


disadvantages:
- rewrite of the creation layer (physical model to logical model)
- usability will be different from actual WB versions
- possible that not every database will be supported in OSS version (my opinion)