Iraiana Mariotte
Rank: #56268
17Views
5Comments
Solved
Create and Update tables - Integration between Outsystems and Sql Server
Question
SQL

Hi guys!


I would very much like to understand how linking OutSystems' own database to SQL Server works.


Example: I create a table called Test, with columns A, B and C. When you delete this table in OutSystems and after the exclusion is published, it does not end up being deleted in the Sql Server. If I create it again and publish it, Sql creates it with a new version (named Test1). But the two remain at the base of the Sql (there is a Test and another Test1, only in Sql Server). Another example is if I delete column B and when I publish it, this column in SQL does not end up being deleted, only on the Outsystmes platform.


Do you have material tips where I can study this integration further? I'm new around here.


Thank you all very much and have a great weekend.


0
0
on 2020-10-23
Copy link to comment
Rui Barradas
Rank: #94
Solution

Hi Iraiana,

Hope you're doing well.

That is an interesting point to discuss :)


You are indeed correct. There is a difference between the Logical Database (the one you see in Service Studio) and the Physical Database (the one you see using for example SQL Server).


About the columns (meaning attributes), please check this documentation:

https://success.outsystems.com/Documentation/11/Reference/OutSystems_Language/Data/Database_Reference/How_Data_Model_Changes_are_Handled

I believe you will understand how things work and why. It explains how OutSystems platform handles deleting, adding or renaming attributes, as well as changing their properties.


About the tables (meaning entities), the process is exactly how you described. If you deleted a table in the application model (Logical Database), it won't be deleted in the Physical Database. The main reason why this happens is because if you rollback to a previous version, both the table and the data are still preserved and stored in the database.

That said, if you end up creating a new table with the same logical name, its physical name will be created with a sufix (Table1, Table2, etc.).


An exception to these rules are indexes. If you create an index using Service Studio, it will be created in the Physical Database. On the other hand, it will be deleted from the Physical Database if you delete it in Service Studio.


Overall, the main reason why OutSystems preserve this information (tables and columns) is because of versioning and to preserve data. Of course, you can end up having unnecessary tables and attributes in your physical database. My suggestion is that you keep your physical database as clean as possible :)

Like Agno Silveira posted, there are already some tools and components that may help you removing these unused elements from your database.


Personally, I suggest this one:

https://www.outsystems.com/forge/component-overview/5018/db-cleaner-on-steroids


Hope that this helps you!


Kind regards,

Rui Barradas

See solution in context
1
0
on 2020-10-23
Copy link to comment
Raphael Ranieri
Rank: #170
Solution

I found another article that explains very well how the Platform handle tables.

It is not an official documentation from OutSystems but I believe it is a very good article.

Take a look here.


Hope it helps,

Cheers and Regards,

RR :)

See solution in context
0
0
on 2020-10-23
Copy link to comment
Agno Silveira
Rank: #171
Solution

Hi Iraiana,

After delete the fileds and publish, install this this application and delete your fields (or table) deleted, before creating again.

https://www.outsystems.com/forge/component-overview/423/dbcleaner


Regards.

0
0
on 2020-10-23
Copy link to comment
Rui Barradas
Rank: #94
Solution

Hi Iraiana,

Hope you're doing well.

That is an interesting point to discuss :)


You are indeed correct. There is a difference between the Logical Database (the one you see in Service Studio) and the Physical Database (the one you see using for example SQL Server).


About the columns (meaning attributes), please check this documentation:

https://success.outsystems.com/Documentation/11/Reference/OutSystems_Language/Data/Database_Reference/How_Data_Model_Changes_are_Handled

I believe you will understand how things work and why. It explains how OutSystems platform handles deleting, adding or renaming attributes, as well as changing their properties.


About the tables (meaning entities), the process is exactly how you described. If you deleted a table in the application model (Logical Database), it won't be deleted in the Physical Database. The main reason why this happens is because if you rollback to a previous version, both the table and the data are still preserved and stored in the database.

That said, if you end up creating a new table with the same logical name, its physical name will be created with a sufix (Table1, Table2, etc.).


An exception to these rules are indexes. If you create an index using Service Studio, it will be created in the Physical Database. On the other hand, it will be deleted from the Physical Database if you delete it in Service Studio.


Overall, the main reason why OutSystems preserve this information (tables and columns) is because of versioning and to preserve data. Of course, you can end up having unnecessary tables and attributes in your physical database. My suggestion is that you keep your physical database as clean as possible :)

Like Agno Silveira posted, there are already some tools and components that may help you removing these unused elements from your database.


Personally, I suggest this one:

https://www.outsystems.com/forge/component-overview/5018/db-cleaner-on-steroids


Hope that this helps you!


Kind regards,

Rui Barradas

1
0
on 2020-10-23
Copy link to comment
Raphael Ranieri
Rank: #170
Solution

I found another article that explains very well how the Platform handle tables.

It is not an official documentation from OutSystems but I believe it is a very good article.

Take a look here.


Hope it helps,

Cheers and Regards,

RR :)

0
0
on 2020-10-23
Copy link to comment