Fix Database Integrity Suggestion from Service Studio

By Carlos Henriques on 2 Jul 2014
When I delete an attribute from an eSpace and then do an 1-Click Publish I get a Database Integrity Suggestion:

Database Integrity Suggestion
Inconsistent database table and entity definitions: column 'OSUSR_ABC_ENTITY.ATTRIBUTE' exists in database, but there is no corresponding attribute in entity 'ENTITY'.

If I don't want to see this again, I must go to the database server and manually delete this column from the database table.
This is a very boring task that I often do. And, since I'm doing it manually there is always the  risk of deleting the wrong column.

It would be great if Service Studio had a context action on each Database Integrity Suggestion line to automatically delete the column from the database table.
And it could be even better if it had an option to do the same action for all accurences of the Database Integrity Suggestion.

This would be something like what TrueChange does with eSpace errors and warnings, like the unused parameters, where we have a context action to automaticaly delete that parameter or delete all unused parameters.

This way I can have my database clean, no more Database Integrity Suggestions on each publish, no risk of deleting the wrong column and time saved on doing this manually.
Justin James3 Jul 2014
Deleting that column is not a good idea. It means that if you need to do a rollback of a deployment to a version that includes that column, the data that was there is gone. I am not sure why you started deleting these columns manually, but that is definitely not a best practive. These messages do not hurt you, unless they are taking up a huge amount of space there is no benefit to removing them.

That said, for an error like this, the system should not show the error at all. It knows why the column is there, it should just ignore it.

If you delete an attribute from an entity you must know what you are doing.
When I delete and attribute I must know for sure that it is not really needed. It's not something that I do just because, and one week or month later I find out that I need it back again. When it's deleted it must have a good reason and I will not need it later for sure.
After some time I do a database cleanup, I don't want my database full of garbage that is not used, that does not make sense for me. And I don’t want someone that is doing reports with Outsystems database using fields that were deleted from the app but not removed in the database.

"not a best practice", for me,  is to do something I shouldn't have done, like delete a attribute that someday I need to recover with a rollback.

If Outsystems thinks that those fields should not be deleted manually, then the messages don't make any since, since they are annoying and will never disappear if you don't delete the columns.
Justin James4 Jul 2014
Carlos -

"And I don’t want someone that is doing reports with Outsystems database using fields that were deleted from the app but not removed in the database."

This is a big red flag right here!

If you are querying the OS DB directly, and you are NOT using ossys_Entity and ossys_EntityAttr to determine at run time what the right columsn are, or using them to create views, then you are putting yourself into a very bad position to be pulling incorrect/wrong data.

If you are doing things the way you should be, these report writers should not even be aware of the old columns, and the only reason to remove them is because they are taking up space in the DB.

I agree, there should be an easy way to get rid of tables/columns which cannot ever be used again (which would be, "if there are no eSpace versions still in the history that refer to them"), but some of your reasoning here is a sign that your team is doing some things that they may want to re-consider.


Len Krygsman20 Oct 2014
I aggree with Carlos. Sometimes the way you're doing something just changes and there should be a method to clean up the database to get rid of permanantly decommissioned columns.

Presumably, if you're making a change like this, you've migrated any data to keep to whatever the new solution is, but you don't want to have to rebuild the ENTIRE DATABASE just to get rid of some old columns throwing warnings and generally cluttering things up.
Mark Tassin5 Dec 2014
Justin is assuming that the reporting solution is Outsystems.
There are full blown Business Intelligence Applications such as

Microsoft SQL Server Analysis Services
Microsoft SQL Server Report Server
SAP Business Objects Web Intelligence
Crystal Reports
Hyperion Essbase

And many more Enterprise Class BI tools used by companies for reporting.  All of them have to query the tables directly and do not go through the OS Entities.  The jungle of trash that becomes your database because Outsystems never cleans it up, makes it almost impossible to use with real BI tools.

Justin James5 Dec 2014
Mark -

The standard practice in that scenario is to use Entity and EntityAttr to generate views, and report directly from the views. The best solution is to regenerate those views on deployment with a "When Published" timer to ensure that your views are always accurate and up-to-date.

Again... if you are reporting directly against the tables, you are setting yourself up for failure.

Goncalo Borrega27 Apr 2015
The new DbCleaner API was designed for this purpose. Check the documentation to understand the required privileges when using it.
As a lot of you mentioned, this is a very sensitive operation that we must ensure is only performed by the right person and under the right conditions. Therefore we will not make it available from the Studio.

This component at the Forge shows a small example of how to use the API. You can use it to adapt to your own security restrictions and policies.