SQL Developer Data Modeler and MySQL

While my current job title is Senior Java Full-Stack Developer, my manager has taken note of the fact that I have considerable experience as a database architect, developer, and all-around database guy. So I'm doing the job of a Database Architect, and preparing to migrate an Oracle on-premises database to a MySQL database in the cloud. And of course, this is my first experience with MySQL, under the assumption that “If you know one Relational Database Management System (RDBMS), you know them all.” Well, I know Oracle very well, and three years ago, I learned a little PostgreSQL, and one and a half years ago, I learned SQL Server, so it was back to the docs to learn something about MySQL. If you are interested in my adventure in the MS SQL Server world, check out my posts:

From Oracle Expert to SQL Server Newbie - First Impressions

More on my Oracle to SQL Server Transition

About Indexing Character Fields

Databases vs. Instances

Data Conversion in Oracle vs. SQL Server

Oracle SQL Developer Data Modeler and Microsoft SQL Server

Designing the Target Database

Some of my work on the new project entailed some redesign to add new tables to the existing MySQL database. So I pulled out my favorite database design tool, Oracle SQL Developer Data Modeler (SDDM), and imported from the source Oracle database to a relational model, reverse engineered to the logical model and went to work making some changes. The biggest problem was that the original designers of the Oracle database had made extensive use of Oracle Object-Relational features. In many cases, there were columns with nested tables of objects, where a more conventional relational design would have had child tables. The target database would need to be implemented with the child tables instead.
I'll skip past several weeks of redesign work, to the point where I had my new relational design ready to go. SDDM has a very useful separation of the relational model from the physical model, and you can have more than one physical models mapped to your relational model. The relational model is simply tables, columns, keys and views, etc. - items that are common to all RDBMSs. But the physical model is specific to a particular RDBMS implementation. One physical model might be for an Oracle 11g Express Edition database, which is what I have on my laptop for experiments, testing and basic fooling around with Oracle. One might be for Oracle 12c Release 2, which is what the original database uses. And in my previous job, I had a physical model for MS SQL Server 2012. We were actually using 2017, but 2012 is the latest that SDDM supports.
Unfortunately, SDDM does not have any support for MySQL. This seems strange to me, since when Oracle bought Sun, they also obtained InnoDB, which is the most used storage engine for MySQL. And while MySQL itself is open source, and there is a community edition that is open source and free, Oracle also has a supported edition. And Oracle provides a good deal of developer support for the MySQL project. So whenever you search for MySQL information, you are going to wind up on an Oracle web site. I have seen requests for MySQL support in the SQL Developer Exchange from way back, but there is no sign that Oracle's SDDM developers will be implementing it any time soon.

SDDM DDL Transformation Scripting to the Rescue

Fortunately, what the developers of SDDM DID give us is a very powerful and flexible scripting capability. I've already talked about a script that I wrote for my SQL Server project:

Scripting SQL Developer Data Modeler with Groovy

SDDM Script to Create SQL Server Journal Tables

You can write scripts to add DDL to the DDL that SDDM generates, as I did for Journal tables, but you can also write scripts to run instead of the SDDM native processes for generating DDL. That means that you can add the missing MySQL functionality yourself. That is what I'll be talking about in the next post.