[DBCleaner] SQL for dropping columns

Forge Component
(53)
Published on 13 Aug by João Pêgas
53 votes
Published on 13 Aug by João Pêgas

Hi guys!

I am trying to automate some functions of DBCleaner, and was wondering if you could share the SQL used to drop columns? Specifically, do the columns marked as ossys_Entity_Attr.IS_ACTIVE = False correspond directly to the Database Integrity Suggestion warnings, and would be any consequences to dropping them, other than not being able to roll back the entity to a previous version? Do I need to drop the ossys_Entity_Attr record as well after dropping the table? 

I tried to describe my problem in this post

Hello Chris, the columns that DBCleaner indicates are no longer used, they are the columns that have already been deleted from the entity. Removing them through DBCleaner will effectively exclude them physically from the entity, without the possibility of reversing the data.

When a column is excluded from the entity inside the platform, the data remains physically in the column in the database, until it is permanently deleted by DBCleaner, or directly in SQL (not recommended). Hope this helps.

Hi Chris,

DBCleaner uses the DbCleaner OutSystems API to perform its cleanup, so there's no public SQL instruction available to share. (btw, since the default database runtime user that the platform uses does not have privileges to change the structure of the database, such SQL would never work as expected).

Jorge Martins wrote:

Hi Chris,

DBCleaner uses the DbCleaner OutSystems API to perform its cleanup, so there's no public SQL instruction available to share. (btw, since the default database runtime user that the platform uses does not have privileges to change the structure of the database, such SQL would never work as expected).

Thanks Jorge! 

I would not even have thought about looking at the database runtime user (it is the os_runtime user, right?) if you had not pointed it out...

I was trying to do the SQL myself because I have some problems with DBCleaner (it says that it dropped the column but does not actually do so, no oppurtunity to automate cleanup, etc.), and my client is still on OS10, so I can't try the DBCleaner on Steroids.

This leaves me with only the option of doing the cleanup manually in SSMS. I have a stored proc set up to drop the constraints and then drop the columns, but I think I should be removing the Outsystems Entity_Attr record for that attribute as well. Does that make sense? Any advice on how to do this (reasonably) safely?