Rename entity attributes without loosing the database content

On our radar
Physical table attributes are not renamed when the entity attributes are renamed, requiring manual upgrade scripts.

Created on 10 May 2010
Comments (9)
Here's some things in this general area that are causing issues for me.

1) An entity with the name Person may appear as Person1, 2 etc. in the database schema. It makes things tougher, especially if you allow ODBC access to the database for reporting.

2) When an entity attribute is renamed it leaves the old field in the database and creates a new field.  It should just create the appropriate Alter script after warning the user that it is being used somplace, if it is.  (Basiclly do an F2 first.)  An you get a warning and the only way to fix is to delete the old field from the database manager.

3) When an entity is created that is the same as one of the system entities, such as Application, it automatically calls it Application2.  System components should have prefixes on their components (sysApplication) to prevent this.  This is what SQL Server does.

4) ID values are created as int fields that have a maximum value of 2,147,483,647.  I could see cases where this could be exceeded so I'm surprised that bigint isn't used.  Maybe a platform option (like the date format) to decide whether int or bigint should be used for IDs?

Curt -

A couple of items here...

1. Direct DB access is not a good idea in general to the OutSystems tables. Instead, write a Web service to access the data, and report against that.

2. That's to support rollbacks. Imagine this scenario: the column is type int, then you make it type text. You add data to it,find a critical bug, then need to roll back the app. You can't change the text to an int, and if there is data in there, it is even worse.

3. All of your tables get prefixed with OSUSR_XYZ_, where XYZ is a random combination assignd on a per-eSpace basis to separate them. If you see one with a 2 on it, that means that you had one, deleted it, and re-created it.

4. I agree 100% with you here, and I know it's been raised by others in the past as well. You *can* change an ID column to be type text, but I think we all agree that having a bigint is a better choice.

Thanks again for all your help.  For number 1, I don't have any choice.  My potential client will want ODBC access to the database so they can use Crystal Reports.  If I get to a point where I can get Crystal to use a web service as its data source, then I can go that way. In order to provide that access via ODBC, the table names described in 3 need to be as I defined them so they can be documented and referenced over time without breaking the reports.  Fortunately, in another thread I found the solution - an Outsystems setting that forces table name to be set to entity name.
Curt -

If you MUST do that, then your best bet is to write a timer that runs "on publish" to use the system entities that describe the relationship between entities & physical tables to construct a view.

Crystal can consume Web services just fine, I've done some proof of concepts of it.

"4. I agree 100% with you here, and I know it's been raised by others in the past as well. You *can* change an ID column to be type text, but I think we all agree that having a bigint is a better choice."

Bigint is coming soon, this was one of the first issues I reported back in 2009.
Outsystems is aware of this issue, and it is a wide spread issue, evenutally outsystems will fix this.

This puzzled me a long time.

changing type of attributes and losing content, sure.

renaming a column from "PersonID" to "PersonNumber" should not cause losing data

The rationale with regards to why a new column is created in the table is clear and makes sense.

In my view the defined approach should be followed in the following scenarios:

  • Deleting an Attribute;
  • Adding an Attribute;
  • Changing the data type of an Attribute;
  • Changing the Length property of a Text Attribute.

Where it does not make sense is the following scenario:

  • Renaming an Attribute;

When renaming the attribute the underlying table and column definition should not be impacted as the Attribute name is defined at the metamodel system level.

Yes, this will impact any external ODBS access but this is requiring manual intervention and management today.

An OutSystems best practice is to use "Meaningful names". 

Consider the following question in the context of the below statements; 

Why should a new column be created in the database table?

A change to the name needs to be made if the name best practice was not followed correctly.

A change to the name needs to be made so that it is more meaningful.

The underlying data type andthe  business intent of column do not change.

Merged this idea with 'Simply change attribute name' (created on 28 May 2019 16:51:21 by Kit Lam)

In OutSystems you can change the Entity Name without consequences, but when you change an Attribute Name, you loose the data in that column.

This is because  it's creating another attribute and the old attribute is still there but hidden. Renaming this back to the original attribute name will show the data again. Is this because there is another technical key to identify the Entity, but there is none for the attribute?

I would like to change an attribute name without consequences.

Now, if I want to change an attribute name, I would create a new attribute, then copy the data of the old attribute to the new attribute with an sql statement and delete the old attribute.

I would like to change the attribute name without loosing the data. Functionally OutSystems should know what I want, when I change the name. Don't ask me to do all those manual steps for such a simple action. Please do this all automatically behind the screen.

This comment was:
- originally posted on idea 'Simply change attribute name' (created on 28 May 2019 by Kit Lam)
- merged to idea 'Rename entity attributes without loosing the database content' on 28 May 2019 16:54:17 by Vasco Pessanha