Re : Configuring the physical table name format for Entities

Gonçalo Gaiolas

Question

Edited by OutSystems on March 21, 2014
The feature described in this topic is not formally supported by OutSystems.
OutSystems advises against using this feature, as it may lead to unexpected results.
Customers should not expect any official support from use of this feature.

Hi everyone,

Since version 3.2, it's possible in the OutSystems Platform to configure the physical table name used to support Entities on the database.

Up until now, physical table names were in the form of OSUSR_<ESPACE_ID>_ENTITYNAME, where <ESPACE_ID> stands for an internal eSpace identifier. For example, an Entity named Client contained inside an eSpace with identifier 112 would have a physical table name of OSUSR_112_CLIENT.

This format presented some challenges :

- Because the eSpace identifier changed between environments (development, testing, production, etc), it was difficult to make direct connections to tables. This made external integrations difficult to achieve;

- It could be a difficult format to read, because long entity names would be truncated;

To alleviate this problem, version 3.2 now provides a configuration setting that can be used to specify one of three table name formats. This setting is called Compiler.DefaultPhysicalTableName and can be found in the file OSHEComp.exe.config, on your Hub Server installation folder root. Valid entries for this setting are :

- PREFIX_ESPACENAMEHASH_ENTITYNAME - This format uses the OutSystems prefix (OSUSR) followed by a hash of the eSpace name. The hash is unique for each eSpace name and will remain constant in different environments so it is safe to assume that the physical table name will also be equal. This is now the the default Hub Server mode.

- PREFIX_ESPACEID_ENTITYNAME - Equivalent to the old format, where the OutSystems prefix is followed by the internal eSpace identifier and the entity name.

- ENTITYNAME - This format was designed to be used in very particular situations where the name of the database tables is relevant outside the OutSystems Hub Server environment. For example, it can be useful if you wish to establish a Data Dictionary that is also available to third party tools and direct integrations. Also, it is easier to read and understand when accessing the database from external query tools (for example Query Analyzer). However, please note that this setting can pose security issues, because the OutSystems Hub Server will not be able to make run-time checks on table access. This means that, for example, Advanced Queries will be able to access tables from other eSpaces directly by just referring to it's physical table name.

You will need to restart OutSystems Services in order to apply the new settings.

As a final note, please remember that changes made to this setting will only affect Entities created from that moment on. This means that if you publish an already existent eSpace any Entities that already existed will keep their original table names. However, if you create new entities, these will be affected by the new value for this setting.

Stay well!

Gonçalo

10 May 2006

Diogo Cordeiro

Hi, I question how is that hashname created, so i can identify each eSpace more quickly, without having to "look for a specific new table"....

Best Regards,

Diogo C S Cordeiro

30 Oct 2007

2 replies

Last reply 31 Oct 2007

Show thread

Hide thread

André Vieira

Staff

Hi Diogo,

Look into this post
https://www.outsystems.com/NetworkForums/viewtopic.aspx?TopicId=2943
Cheers,
André

30 Oct 2007

João Mendes

Hi,
I have another question.

When I create an entity with the name "Entity_of_JM" I the table created is OSS_XXX_Entity.
If I create an entity "Entity_of_JJJ" I the table created is OSS_XXX_Entity1.

Can I change this behavior?
By the way I can't find Compiler.DefaultPhysicalTableName in OSHEComp.exe.config in the Express Edition.
Thanks
João Mendes

31 Oct 2007

Paulo Tavares

Staff

Hi.

I am in the same situation, there seems to be no such setting in the OSHEComp.exe.config file from 4.0 onward. Is this still valid?

Best regards,

Paulo Tavares

16 Jan 2008

3 replies

Last reply 18 Feb 2008

Show thread

Hide thread

Rui Eugénio

Staff

This change in the default config file is due to .NET 2.0 changes.
However, I think the setting is still valid. Try adding the Compiler.DefaultPhysicalTableName="PREFIX_ESPACENAMEHASH_ENTITYNAME" attribute to the ServiceSettings element, that by default is empty.
Remember that this setting applies only to entities created after its change.

Best Regards,
Rui Eugénio

16 Jan 2008

Lúcio Ferrão

Hi Paulo,

In version 4.1 most configuration parameters were moved from the config file to the OSSYS_PARAMETER system table. You have to manually place an entry "Compiler.DefaultPhysicalTableName" in the system table with your option.
Cheers,
Lúcio

16 Jan 2008

Fernando Sousa

Hello,

Is it possible to change the maximum length of the physical table name in version 4.0?

Only 8 characters seems too short for me, we are ending up with a lot of tables with the same name (the first 8 chars) and then the suffix 1, 2, etc.

This naming rule does not help when you have to do queries directly on the database (using Query Analyzer, for instance) when you are trying to analyze a problem in support tasks.

18 Feb 2008

Kurt Vandevelde

MVP

In version 6, Oracle table names are still only 18 characters long (whereas Oracle supports 30 ...) and the default is that the OS_USR and hash is used. I added the record in the ossys_parameter table and now I get tablenames without prefix.
Now, I still have to find if there is a way to use names longer than 18 characters.

27 Oct 2011

João Rosado

Staff

Hi Vandevelde,

Yes the maximum limit of the table names even without prefixes in Oracle is still 18.
The other 12 are reserved for indexes/constraints/views suffixes.

The table names should not be relevant for the applications, and those settings should not be used unless extremelly necessary.

And since you wokeup a very old thread: In 5.0+ the maximum in SQL Server is longer (around 233).

Regards,
João Rosado

27 Oct 2011

Shraim

Hello everyone and sorry for waking up this thread yet again...
As a new comer to AP, I am trying to get my head around how AP handles catalogs and entities in SQL server. The following is still not clear.

Typically and for a new .NET application, I would create a new SQL DB/Catalog. In AP, unless I remeber to use the "Configuration Tool" to change the Catalog to a new DB/Catalog I pre-created for the new application then I would end up with the entitities of several applications in the same catalog.

1- is this by design that entities of different applications developed on the platform live in the same catalog?

2- if that is the case and the developer's machine could have entitties of so many applications at different phases of development, how to move only the entities of a specific application from the development SQL server to a deployment server instance?

3- is it advisable to change the database catalog in the Configuration Tool before creating a new application

4- is there a way to control which catalog the application's entities live in to the point that some entitties live in DB1 while other entitties in the same application live in DB2?

29 Oct 2012

1 reply

30 Oct 2012

Show thread

Hide thread

André Vieira

Staff

Shraim wrote:

Hello Shraim,

Let me try to answer some of those questions for you.

By design and for simplicity sake the Agile Platform operates with a single catalog where all entities are store, including the ones from the Agile Platform meta-model and the ones you create in Service Studio.
Developers shouldn't work with a local installation! The Agile Platform works as a distributed environment, not only for production use but also for development. In this sense the developer connects to a development installation of the Agile Platform that is connected to a catalog in SQL Server or Oracle.
It is absolutely unadvisable that you change the catalog in the configuration tool since this is where the meta-model will be created. If you mess with the meta-model of the Agile Platform the outcome is unpredictable!
Yes there is. The Agile Platform has a feature called Multiple database catalogs. What this means is that you can configure other catalogs in Service Center for you to use for your applications. This way you can separate the data of your applications. If you want to know about this feature you can check this tech note.

Cheers,
André

30 Oct 2012

Shraim

Thank you André. The reason for my asking is that I am coming from the traditional developer background where a developer has everything he needs to develop the application on his development machine and use subversion to check-in code for later building the build machine.

In the AP distributed approach you are explaining, the developer needs to connect to a development installation of AP (which can be local or remote or in the cloud) and this development platform is connected to a single catalog (in SQL or ORACLE).

I understand and appreciate the centralization of AP’s metadata in a one catalog but to have entities of all applications also living in one catalog makes it difficult to use query tools or reporting tools to query the application’s tables directly from the server. What would help for such scenarios is to have entities of each application live in their own catalog.

Another issue is the naming of the physical tables of entities related to this thread. The approach explained for configuring the physical table name is a great option but why also not add one more. In addition to creating a physical table for each entity why not also creates ONE view (select * from NameOfEntityTable) for each entity and the name of the view is the real name of the entity and the view refers to the latest version of the entity’s physical table. The plus of this approach is that DB query and reporting tools can easily and directly have access to the tables (views) without worrying about some obscure naming and versioning that only serves the entities of the AgilePlatform.

Apologies for the long post but I thought this thread best deals with both issues.

30 Oct 2012

1 reply

30 Oct 2012

Show thread

Hide thread

André Vieira

Staff

Shraim wrote:

I don't see how having all entities in the same catalog creates a problem for reporting tools or query tools... What leads to that issue is the naming convention using the hash. You may still want to have separate catalogs for other reasons though...

Shraim wrote:

One of the reasons why the platform has the hash in the table name is because two different eSpaces can have one Entity with the same name and this would result in name clashing in the database unless different catalogues would be used. Great thing about the Agile Platform is that you can use its meta-model. You can create a query that uses this metamodel to create a view for each entity in your eSpaces assuring that you give a unique name, e.g.: MyeSpace_MyEntity.
Though this could be one approach and probably the simplest one, I would assess creating denormalized views or even tables for the reports. IMHO you should keep business logic out of the reporting tools, they should be used just for that, rendering a report.

Shraim wrote:

Apologies for the long post but I thought this thread best deals with both issues.

No worries ;)

Cheers,
André

30 Oct 2012

MVP

@Shraim:

I think you should read this post about integrating with an external-db: https://www.outsystems.com/NetworkForums/ViewTopic.aspx?TopicId=7362

Lots of your possible issues will be solved and you keep the benefit of the Outsystems Platform.
(except the extremely easy and fast way of altering tables)

30 Oct 2012

4 replies

Last reply 05 Nov 2012

Show thread

Hide thread

Shraim

Apologies for the repeated post. I thought my previous reply was not accepted so I did it again trying to more address the riased points

Joost Landgraf wrote:

@Shraim:

I think you should read this post about integrating with an external-db:https://www.outsystems.com/NetworkForums/ViewTopic.aspx?TopicId=7362

Lots of your possible issues will be solved and you keep the benefit of the Outsystems Platform.
(except the extremely easy and fast way of altering tables)

Thank you Joost. The workflow discussed in this thread is for tables manually created in a SQL database and later changes in the tables are manually maintained (by the DBA or the developer) and then use the integration studio to refresh these changes back into the entities used by the AP's application. This method does not show that changes to the entities in the ServiceStudio can be reflected to their original tables in the SQL database they were migrated from. Meaning the refresh is really one way rather than being two ways.

What you describe as “extremely easy and fast way of altering tables” I actually find it in more in AP rather than in the management studio. It’s the ease and convenience of querying and inspecting the data during the development process is what mostly will be lost with the AgilePlatform.

Andre wrote:

I don't see how having all entities in the same catalog creates a problem for reporting tools or query tools...

Actually the problem here is two folds. First, query and reporting tools need to cope with the obscure naming of the entities amongst thousands of entities for the many applications in this database. Obscure naming becomes even more of a problem considering the versioning of tables that runs the risk having these qyery and reporting tools use older versions of some tables with more recent versions of other tables.
The second problem for query and reporting tools is change management. These tools need to cope with changes made to entity tables in the service studio. Reports and queries will always need to be changed to reflect any changes made to their tables by the service studio resulting in a true maintenance nightmare.

Andre wrote:

Great thing about the Agile Platform is that you can use its meta-model. You can create a query that uses this metamodel to create a view for each entity in your eSpaces assuring that you give a unique name, e.g.: MyeSpace_MyEntity.

Thats exactly what I am suggesting. Having Service Studio add this step into its code generation pass, will generate /overwrite for each entity on SQL view named MySpace_MyEntity that uses the latest version of the entity table. I am sure having the code-generator perform this is a natural fit rather than us having to refresh the SQL views manually each time entities are changed.

Andre wrote:

Though this could be one approach and probably the simplest one, I would assess creating denormalized views or even tables for the reports. IMHO you should keep business logic out of the reporting tools, they should be used just for that, rendering a report

Creating specialized denormalized views will be definitely used for reports and comlex queries but even such denomalized views can be build on top of the generated views from AgilePlatform. Depending on the logic of the generated entity views (select * from entityTable or select column1, column2, ... from entityTable) there could be a need to for change management for these denormalized views. Even then it is much more managable and easier than having to do the same directly querying the generated entity tables.
Having these entity SQL views is like having the best of both worlds; versioned tables for front-end application entities and SQL views for back-end database and reporting tools.

Pedro wrote:

One thing I easily find useful in having views for your espace entity, is the ability to query them directly using a SQL client like SQL Server Management Tool.

During my development process, I quite often find myself querying the database to see how my entity data looks like, or to test some options for some advanced queries. Maybe this is also because I come from a "traditional development environment" like you.

In order to facilitate the creation of the views you ask for, I've developed a simple espace that enables you to download a DDL file with the code needed to create such views. I didn't finished it yet, but I'm more than happy to share it with you and the rest of the community. If you find it useful and wish to provide some enhancements to it, it would be great.

I'm creating the project in Forge and I will let you know shortly.

Its exactly what I am trying to explain here. I am really excited about AgilePlatform because it does provide a paradigm shift for developers. Still, if the platform can do that without taking away ALL our traditional habits that we have grown accustomed to use in our work, it really can be a dream come true for many of us.
IMHO, the SQL views mapped directly to entities would help in coming closer to that dream :)

Pedro wrote:

In order to facilitate the creation of the views you ask for, I've developed a simple espace that enables you to download a DDL file with the code needed to create such views. I didn't finished it yet, but I'm more than happy to share it with you and the rest of the community. If you find it useful and wish to provide some enhancements to it, it would be great.

I'm creating the project in Forge and I will let you know shortly.

I am still learning my way around AgilePlatform and I am not sure how automatic it will be to run this DDL file. My hope is that code of the DDL file to be generated part of the code-generation process of the service studio (at least when there is a change in the entities) and then the same code-generator can run the DDL file on the DB server as part of implementing the changes back to the DB server.

31 Oct 2012

André Vieira

Staff

Hi Shraim!

What @Joost pointed you to is how to expose external entities to the Agile Platform. You'll have to use Integration Studio and database connection or linked server to do it, basically Integration Studio sees the catalogs that the platform sees plus the ones reachable through the database connections configured in service center.
If you change these tables you'll need to refresh the definitions in Integration Studio and then in Service Studio. Service Studio can't and won't change these entities!

Back to the entities defined in Service Studio and querying them outside let's see what we can do but I'll separate things between query tools and reporting tools.

Query Tools:

Use Service Studio :) - you can actually create a query and test it inside Service Studio.
Use SQL Map
You can use SQL Map available here to transform queries using the Service Studio notation to their counterpart in SQL.
Use other query tool (e.g.: SQL Server Management Console, Toad,...)
Determine the physical table name using metamodel:

select ossys_entity.physical_table_name

from ossys_entity

inner join ossys_espace on ossys_entity.espace_id = ossys_espace.id

where ossys_entity.name = @entityName

and ossys_entity.is_active = 1

and ossys_espace.is_active = 1

Reporting Tools:

Use the attached SQL script (tested in SQL Server) to create a store procedure that creates the views for a specific eSpace, usage:
// First run the script in attachment and afterwards call the store procedure
// first argument is the name of the eSpace, second one is the prefix to avoid name clashing
CreateESpaceViews 'Documents', 'DOC'

This will create the views:
DOC_Action, DOC_Document, ...

Important! The script grants PUBLIC access to the views
Create your own denormalized views to be used in the Reporting tools.

I don't understand your issue regarding updating the data model and needing to update the Reporting tool, this would always be the case no mather what technology you use, unless you have a specific model for the reports (either with views, cubes, ...)

I don't really think that the AgilePlatform will anytime automatically generate views for the entities...

As for Pedro's solution I don't know what it is.

createViews.sql

31 Oct 2012

Shraim

I am so impressed with this followup and activity on the AgilePlatform. This vibrant forum adds so much value to an already unique platform...

Andre wrote:

What @Joost pointed you to is how to expose external entities to the Agile Platform. You'll have to use Integration Studio and database connection or linked server to do it, basically Integration Studio sees the catalogs that the platform sees plus the ones reachable through the database connections configured in service center.
If you change these tables you'll need to refresh the definitions in Integration Studio and then in Service Studio. Service Studio can't and won't change these entities!

Thank you for the explanation. Basically in this scenario, I will lose the ability to change Entities in the service studio and have to do it in the DB back end.

Andre wrote:

Query Tools:

Use Service Studio :) - you can actually create a query and test it inside Service Studio.
Use SQL Map
You can use SQL Map available here to transform queries using the Service Studio notation to their counterpart in SQL.
Use other query tool (e.g.: SQL Server Management Console, Toad,...)
Determine the physical table name using metamodel:

select ossys_entity.physical_table_name

from ossys_entity

inner join ossys_espace on ossys_entity.espace_id = ossys_espace.id

where ossys_entity.name = @entityName

and ossys_entity.is_active = 1

and ossys_espace.is_active = 1

Great outline of the options.

Option 1 is very valid but my only gripe with service studio is that its data query performance is not particularly fast compared to specialized query tools and also its UI is not really optimized for heavy data query work like in Management Studio. Still it is a very valid option otherwise.

Option 2 I do not fully understand. I will have to do my homework on understanding OML packages inside service studio and how this SQL map can help

Option 3 is a great SLQ query to have a reference of the mapping between the physical table names and the logical entity names

Andre wrote:

Reporting Tools:

Use the attached SQL script (tested in SQL Server) to create a store procedure that creates the views for a specific eSpace, usage:
// First run the script in attachment and afterwards call the store procedure
// first argument is the name of the eSpace, second one is the prefix to avoid name clashing
CreateESpaceViews 'Documents', 'DOC'

This will create the views:
DOC_Action, DOC_Document, ...

Important! The script grants PUBLIC access to the views
Create your own denormalized views to be used in the Reporting tools.

Option 1 I used and works GREAT. Now I have my SQL views that have names same as the logical names of my entities. My only gripe with the script is that it still includes deleted entity attributes. Is there a way to modify the metadata query so to only include the active attributes (ignor deleted attributes in the service studio) in each entity???

Option 2 is exactly what I will do BUT I will create these denormalized views using the entity views from option 1 rather than directly from the entity physical tables

Andre wrote:

I don't really think that the AgilePlatform will anytime automatically generate views for the entities...

I respect that any framework or platform cannot satisfy all wishes or dream-features otherwise it would become deformed. Still, I don't see generating an SQL view for each entity to have such an effect. In addition, such a feature can be even included as another configuration option in the platform for the naming of the entities' physical tables.

Pedro wrote:

I've managed to take some minutes and created the project in Forge.

Find it here

Thank you Pedro. I will give it a try and get back to you. Appreciate your efforts and sharing.

02 Nov 2012

André Vieira

Staff

Hi Shraim!

I took a few minutes to update the script to include only active attributes of the entities. Find it in attach.

Cheers,
André

createViews.sql

05 Nov 2012

Pedro Cardoso

Hello Shraim.

One thing I easily find useful in having views for your espace entity, is the ability to query them directly using a SQL client like SQL Server Management Tool.

During my development process, I quite often find myself querying the database to see how my entity data looks like, or to test some options for some advanced queries. Maybe this is also because I come from a "traditional development environment" like you.

In order to facilitate the creation of the views you ask for, I've developed a simple espace that enables you to download a DDL file with the code needed to create such views. I didn't finished it yet, but I'm more than happy to share it with you and the rest of the community. If you find it useful and wish to provide some enhancements to it, it would be great.

I'm creating the project in Forge and I will let you know shortly.

30 Oct 2012

Shraim

Joost said:I think you should read this post about integrating with an external-db: https://www.outsystems.com/NetworkForums/ViewTopic.aspx?TopicId=7362

Lots of your possible issues will be solved and you keep the benefit of the Outsystems Platform.
(except the extremely easy and fast way of altering tables)

What you describe as “extremely easy and fast way of altering tables” I actually find it in more in AP rather than in the management studio. It’s the ease and convenience of querying and inspecting the data during the development process is what mostly will be lost with the AgilePlatform.

André said:I would assess creating denormalized views or even tables for the reports. IMHO you should keep business logic out of the reporting tools, they should be used just for that, rendering a report.

Problem that querying and reporting tools need to cope with changes made to entity tables from service studio. If we create specialized views then just maintaining changes in the entities back to these denormalized views becomes a maintenance nightmare doubled by the fact that entity tables are not directly named against their entities and that each entity table will change its name reflecting the new version of the entity.

If during the code generation process, AgilePlatform generates a single SQL view for every entity in addition to generating the table and also updates this SQL view to always use the latest version of the entity table, then database query and reporting tools can easily do their work the same they would do in the traditional world of simple named tables. Having these SQL views is like having best of both worlds; versioned tables for front-end application entities and SQL views for back-end database and reporting tools.

Pedro said:During my development process, I quite often find myself querying the database to see how my entity data looks like, or to test some options for some advanced queries.

That’s a great reason for me asking for these SQL views that are directly mapped to AP Entities.

Pedro said:

Pedro said: In order to facilitate the creation of the views you ask for, I've developed a simple espace that enables you to download a DDL file with the code needed to create such views. I didn't finished it yet, but I'm more than happy to share it with you and the rest of the community. If you find it useful and wish to provide some enhancements to it, it would be great.

That would be great and I know for myself and many other possible adopters of AgilePlatform, such functionality automation will make it that much easier to convert to AgilePlatform as it would not deprive us “traditional developers” from the way we are used to dealing with the data of our applications’ databases and at the same time we gain the phenomenal flexibility and power of the AgilePltaform applications.

30 Oct 2012

Pedro Cardoso

Hi Shraim!

It seems that André has a great solution for the reported need, nevertheless, find attached the solution I talked about yesterday.

I will create a project in Forge, but I'm not able to do it right now.

Also, please note that my solution is very, very Beta. I developed that in a rainy Sunday ;). It does not deals with the situation where you have two entities with the same name in diferent eSpaces.

Cheers
Pedro

View4Entity.osp

31 Oct 2012

Pedro Cardoso

I've managed to take some minutes and created the project in Forge.

Find it here

31 Oct 2012

2 replies

Last reply 06 Nov 2012

Show thread

Hide thread

Shraim

Pedro Cardoso wrote:

I've managed to take some minutes and created the project in Forge.

Find it here

I downloaded the View4Entity-0.0.1.osp file from Forge. When I doubleclick on it I get the dialog with the option to Upload or 1-click-publish. When I selected 1-click-publish I received an error "invalid intellectual property" (attached screen capture of the error dialog).

Is this related to me running the community server ??

AP publish error.jpg

06 Nov 2012

Shraim

wrote:

I reposted the same to a new thread on the component's Forge page.

06 Nov 2012

Shraim

Thank you Andre. Great forum and super support.

05 Nov 2012

André Vieira

Staff

Hi Shraim! You'll need to pass the OSP throught the IPP app that you'll find here: https://www.outsystems.com/ipp Cheers, André

06 Nov 2012

Shraim

Thank you Andre. With regards to your SQL script, a small bug that gave me some grieve when generating a view with @columns larger than 128 characters (size you declared in the script for the @columns variable). Making the @columns bigger resolved the matter.

06 Nov 2012

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines