Rename entity attributes without loosing the database content

By Lúcio Ferrão on 10 May 2010
Physical table attributes are not renamed when the entity attributes are renamed, requiring manual upgrade scripts.

Curt Raddatz9 Dec 2013
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?

Justin James23 Dec 2013
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.

Curt Raddatz30 Dec 2013
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.
Justin James31 Dec 2013
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.

J.15 Apr 2015
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