How to get Physical Column Mapping

Goodday,


After creating an entity, if we delete some columns then it happens that backend physical column is not removed but in service studio, its stopped showing it.


My question is that how service studio is identifying this mapping? From where it is being checked that which physical column was removed and it should not appear in service studio?


Why Am I asking this, because i have a requirement, where i need to create view on my table in Database.

The columns which are not visible in Service Studio, should not be included in view select list. In some cases, we have more than 50 columns and its not easy to do it manual comparison to identify removed fields.


Regards,

Bilal

Solution

Hi Bilal,

If you want to provide a view of you data model you can use an aggregate and query the below tables (available in System).

  • Entity (Entity Definition, can filter based on entity name for example)
  • Entity_Attr (Attributes Definition, should select only active columns)


Does this help?


Best regards

Solution

Paulo Jadaugy wrote:

Hi Bilal,

If you want to provide a view of you data model you can use an aggregate and query the below tables (available in System).

  • Entity (Entity Definition, can filter based on entity name for example)
  • Entity_Attr (Attributes Definition, should select only active columns)


Does this help?


Best regards

Thanks a lot Paulo, Yes, it is what i was looking for.


Bilal Iqbal wrote:

Goodday,


After creating an entity, if we delete some columns then it happens that backend physical column is not removed but in service studio, its stopped showing it.


My question is that how service studio is identifying this mapping? From where it is being checked that which physical column was removed and it should not appear in service studio?


Why Am I asking this, because i have a requirement, where i need to create view on my table in Database.

The columns which are not visible in Service Studio, should not be included in view select list. In some cases, we have more than 50 columns and its not easy to do it manual comparison to identify removed fields.


Regards,

Bilal

Hello Bilal,

You can check the generated SQL in Service Studio and see that the deleted column no longer is considered by SS, so SS ignores it.

Regarding the other question you have, when you publish a module that contains a table from which you eventually deleted some columns you get an info from Service Studio saying something like: "Database Integrity Suggestion
Inconsistent database table and entity definitions: column '<Table>.<Column>' exists in database, but there is no corresponding attribute in entity '<Table>'."

To clean this info, you can use this statement to drop a column from your database: "ALTER TABLE <Table> DROP COLUMN <Column>;"

By doing this you have 2 advantages:

  1. You clean the Inconsistent Database Table Warnings from Service Studio;
  2. You keep your Database clean;

So now your database is consistent with what you see in your modules making it more easy to make a match with everything among other things :)...

To clean the DB I would recommend using the forge component:
https://www.outsystems.com/forge/component-overview/423/dbcleaner


Instead of creating manual SQL scripts to drop columns or old tables.

Edgar and Paulo,


Thanks for your response. Anyhow, I got what i was looking for. 

For DB cleaner, i tried to install on , but it was showing that its not to safe on our environment, that's why I was focused to just get a mapping of active fields.