Welcome to the 4th article on our not-so-new Blog series about Oracle JET and Oracle Cloud.
Today we’ll take our Logical Data Model design from the last article and create the necessary tables directly on our cloud database instance.
Let’s start the party!
The SQL Developer data model generation tools
Launch the SQL Developer and import the design you worked on the previous article.
So here’s our Logical Data Model, in all its splendor:
Now we need to create the Relational Model, based on our logical model. Lucky for us, this means pressing a button in SQL Developer. When you open your Logical Model, a specific option called “Engineer to Relational Model” appears, allowing you to produce the Relational Model directly. And the good thing is that there’s also a button to “Engineer to Logical Model”, which means you can make your own small adjustments and changes, and reflect those back into the Logical Model.
Find the SQL Developer Data Modeler Logical Model toolbar and click on the “Engineer to Relational Model” option.
Ooops… what’s this?
You need to create the model first (or open one, if you already have one created). Go to your Data Modeler browser, find the Relational Models folder and right-click it to create a new relational model.
Now you can try again to engineer your Logical Model. This time, an Engineering dialog appears. Make sure all checkboxes on the Logical side are selected so that everything in the Logical Model is transformed into Relational Model artifacts. There are a few options that you can use to finetune this process, but we’ll leave everything in default. Click on Engineer. Et voilá! Your Relational Model is now generated.
Here’s our application Relational Data model. Although it’s closer to what we’re going to have on our application (now we talk about Tables and Columns and Primary Keys), it’s still not the actual data model implementation.
The final step to have our model running inside the cloud database is to produce the actual Physical model and run it, which is to say, generate the DDL and execute that inside our cloud database. The DDL generation is achieved also with the press of a button, based on our relational model.
As the DDL code is specific to a given RDBMS, this option asks what specific version of DB engine are we using and from which relational model do we want to use to generate it. Notice that the current SQL Developer version also features DDL generation for DB engines other than Oracle.
As we choose Oracle DB 12c as our cloud database engine, we’ll choose that option and click on “Generate”. SQL Developer will then show you the DDL generation options specific to that DB engine. Naturally, Oracle DB DDL generation have more options than other engines. For the purpose of this exercise, we’ll just use the default settings and click OK.
Our DDL generation produced some errors, but we can’t see them. SQL Developer says that we should check the Design Rules for more details, so that’s what we’ll do. Press “Generate” again to take you to the DDL generation dialog and click on “Design Rules”, in the lower left corner to check for the errors.
It shows up a Design Rules dialog where you can apply the rules and check eventual problems. Doing so shows a few warnings and a lot of errors, but only one seems to stand out: The Evaluation.value Column datatype is unknown.
We seem to have forgotten to set the Evaluation.Value datatype. It should be a Domain value list, so let’s do it, as we did before. The best way is to correct all the way up in the logical model, where the problem is. The good thing is that correcting it is easy and propagating that correction all the way down to the physical level is also quite easy: just generate an updated relational model and then re-run the DDL generation tool.
The error message still exists, but it’s just a matter of having comments associated with the tables, so we can just ignore them. Just save the data model generation code that SQL Developer produced. We’ll now apply it to our cloud database.
Executing our DDL in our Cloud Database
Do you remember the user we created in the second article of the series, OJETBLOG?
We’ll use that to execute our DDL. First, create a DB connection using OJETBLOG to our cloud database and connect to it. Then, open the DDL file you have previously saved and select the OJETBLOG connection from the connection list. Finally, execute the DDL script and commit those changes. Your data model should now be fully deployed in the cloud.
So if you do a simple
SELECT * FROM EVALUATION;
you can see that the table is already created.
And that’s it! Your data model is fully deployed and running in your Oracle DB Cloud Service.
The data model is done and implemented. Next time, we’ll start building our application, from top (UI/JET) to bottom (NodeJS). Stay tuned.
José Rodrigues, a.k.a. Maverick
Post header image by: nigelpepper