Data Management optimize

Importing existing database schemas as Agile Platform entities

Justin James describes the straightforward process of turning an existing database schema into OutSystems Agile Platform entities.

I recently had to do some work with OutSystems Agile Platform where there was an existing database to use as a model, but I wanted to bring in that database as true Agile Platform entities. There is a lot of value in working with Agile Platform entities directly, like being able to easily manipulate them, versioning, and so on. So the question was, "how do I turn the existing database schema into Agile Platform entities?" The solution turned out to be pretty straightforward.

The first thing I did was generate a DDL of the database, so that I could easily recreate it on demand. This is database specific, and in this particular case, I was given a DDL from an Oracle database. Then I created the database from the DDL on the same SQL Server instance that my Agile Platform server is connected to. It took me a bit of tweaking to get an Oracle DDL working on SQL Server, but nothing too major.

Next, I fired up Integration Studio, and I created a new extension. Once the base of the extension was made (nothing to do except give it a name, really), I went to the File menu, chose Import, and from there I was given the choice of importing entities from the database. It listed the new database I made, and it quickly imported the tables as extensions. The next step in Integration Studio was to publish the new extension to the server.

The final major step was to go to my eSpace and use Add/Remove References to import the entities from the new extension. Once they came in, I selected all of them, did a Copy, and then pasted them into the Entities section of my eSpace. Service Studio will append a "2" to each one to differentiate it from the original. Remove the reference to the extension, and then rename the entities to not have the "2" suffix.

At this point, Agile Platform will probably be filled with warnings and errors, especially around how entities with lots of attributes perform updates; work through those warnings and errors. The last thing I did was put in a new ID column, move it to the top, set it to be the ID, and then re-establish the relationships between the entities. It wasn't difficult, but it was rather time consuming.

I would love to see a tool that quickly imports an existing database in as Agile Platform entities, but this is not the worst path in the world to have to follow. There is a facility to import an Excel spreadsheet as entities, but it is not a particular time saver compared to this method, and you would still need to do the cleanup work afterwards (if not more, for where the data type was not clear).

J.Ja

Read my other TechRepublic posts about Agile Platform

About

Justin James is the Lead Architect for Conigent.

2 comments
Miguel Melo
Miguel Melo

Justin, after you Ctrl+C the entities if you delete the referenced ones prior to pasting back with Ctrl+V you won't need to waste time renaming the trailing 2's. :)

Justin James
Justin James

Miguel - I *suspected* that would be the case, but I didn't try it, thanks! Funny story here, I was working through the warnings in the data model from the import process, and I said to myself, "I wish there was an easy way to click a button and have the warning taken care of for me." Then I right-clicked the warning and I saw that there was indeed a way for many warning to be resolved instantly. :D J.Ja