SDDM Physical Model for MySQL

As an example, I’ve imported the Relational Design for the HR schema into SDDM from my Oracle 11g XE Database. When you import from a data dictionary, SDDM creates a Relational Model, but it also creates a Physical Model for the database from which you imported. If you import from an unsupported database like MySQL, it will default to Oracle, but you can set the target to one of the other supported databases if you like. But I recommend that you choose from the Tools menu to run the RDBMS Site Editor, and create a new site for your MySQL database. By creating a new Site, you can distinguish your physical models that have been customized for your non-standard database.
This will require you to choose a supported database as the database type. So we have to choose the one that is as close as we can get. I did a lot of research and while there is no perfect solution, as far as I can determine, SQL Server was the best choice. There are some major differences that we will need to deal with, but at least the datatypes are fairly close, because they support the ANSI SQL standard a little better than Oracle. For instance, a text field is VARCHAR, not VARCHAR2. So here is my new RDBMS Site as shown in the Site Editor:

So, now I can create a new Physical Model for my MySQL database. The browser shows my Relational Model, the original Physical Model for my Oracle 11g XE database, and the new Physical Model.

DDL Differences for MySQL

What other adaptations do we need? For my purposes, the main change that I need is to change the DDL for generating surrogate primary keys from a non-repeating sequence of numbers. MySQL does this with the AUTO_INCREMENT attribute of the primary key column. So whenever I mark a column in the Relational Design as an auto-increment column, I want the CREATE TABLE command that is generated to have AUTO_INCREMENT, not the IDENTITY attribute that is typical of SQL Server and Oracle 12 and later, or the generated SEQUENCE and trigger that is typical of Oracle versions before 12c. And I want to use the “Start with” and “Increment” values that I put into the Relational Design, in case I don’t want to use the defaults – 1. Here’s EMPLOYEES.EMPLOYEE_ID marked as Auto Increment:



Table and column comment syntax is different too. In SQL Server, you have to call a built-in procedure to add the comment to the table or column. In Oracle, you use the “COMMENT ON” syntax. But in MySQL, there is a COMMENT attribute of the column or of the table. So I want “Comments in RDBMS” generated with the proper syntax.

Syntax for check constraint is pretty similar to syntax in Oracle or SQL Server, but they aren’t enforced in MySQL before version 8, so I want a SQL comment to warn the developer. There are a few CREATE TABLE clauses that are unique to MySQL, like ENGINE, but the defaults are reasonable, so I leave them out. If you need them, you may have to add some user-defined properties for them to SDDM and then the transformation script needs to use those properties in DDL generation – I may do that in a future version, but right now my script doesn’t have them.
In my next post, I’ll share my SDDM Transformation script, and explain how it works.

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.