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.
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
Rui Barradas wrote:
Hello, Agno and Rui.
Cool, I loved all the tips.
I'm studying a little more on this subject.
I want to take the opportunity to take one more question:
- Is there a way to rename the tables on the Sql server itself and this change be replicated in Service Studio? Are there any applications that do this migration from Sql to Service Studio?
Thank you! :)
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.
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 :)