Relationships : User table vs External tables

Relationships : User table vs External tables

  

Hi,

Can someone please give me a hand with this problem??. 

I have struggled to figure out how to create Entities in outsystems and have them to reflect on my external databases such as oracle or SQL server. 


Since i could not get that to work, i gave up and opted to do the invers, i create my table son my external RDMS such as sql or oracle and them use integration studio to create a database extension. aThis extension in turn will be imported into my project to give me access to my data.

Now i'm facing the following trouble: 

1 - The users table is not accessible inside the oracle or sqlserver so i cannot make references to it to create relationships with other tables on my DB. 

2 - The tables imported as part of an extension are not editable so i cannot create this relationship after i have imported my tables into outsystems

3 - If i try to create a Users table inside my database, generate the relationships  and then import it into ousystems. Then i end up with 2 users table, one with name users (the ousystems one), and other with the name users2 (the external one), so it still does not solve my problem.


I would like to know if there is a way to related the outsysetms tables with the external ones; or to create al  the databases tables on outsystems and then connect or export these tables (with data) to the external RDMS.


Thanks in advance for your help. 

Hi Ferox,

The OutSystems Entities are created in the database that is selected during Platform installation. The are names OSSYS_xxx for system tables (where xxx is the Entity name, including the User Entity), and OSUSR_xxx_yyy for "user" tables (i.e. Entities you defined yourself, where xxx is a three-character prefix that's derived from the eSpace name, and yyy is the Entity name*).

There's a system Entity, called Entity (what's in a name) and reflected in the database as ossys_Entity that maps the Entity name and the physical table name. You can reference the Entity from the Manage Dependencies pop-up, Producer "(System)":

If you have external tools that need to access a certain table, you can use ossys_Entity to find out what the physical table name is, and use it accordingly.

*If you have a very old installation, it may be an abbreviation of the Entity, since older versions of MSSQL (and possibly Oracle) didn't allow for long names.

Hi Killian, 


Thanks a lot for your assistance.

I think i understand your suggestion but im still somehow stuck.

Lets say im building a bank app, and i want app to have its own schema, i create a user bankadmin and all the tables for the app on the schema associated to this user.


Now i wan to to create a relationship between one of my tables  with the OSUSR_Users. So you suggest that i go to  OSSYS_Entity and check the physical table name, but my bankadmin users seems not have access to the  OSUSR or OSSYS schemas.


So the new questions that arise are:

  1.  - Should my tables (for each new application) be created on the OSUSR schema ?? 
    1. a) If not then how do the tables i create externally get somehow reflected there? 
  2.  - Should i grant permissions for my bankadmin user  on those schemas or should i create my tables using a different user (a system user) such as hubadmin.




Hi Ferox,

Appologies for the late reply, somehow the notifications where turned off. Unfortunately I can't help you with your additional questions, I'm unfortunately no DBA.

Hi Neto:

1. create an external user table

create table extUser

id bigint(20)   

name varchar2

2. relates it  one-to-one implicitly to internal user table by having the same id:

- call createextUser in outsystesms and the returned ID used as ID for internal user entity or the other way around: internal user first then external one


3. Other external tables relates indirectly to the internal user table through the external user table

regards,

G

Hello again,

@Killian  :  thanks anyways.


Gede wrote:

2. relates it  one-to-one implicitly to internal user table by having the same id:

- call createextUser in outsystesms and the returned ID used as ID for internal user entity or the other way around: internal user first then external one


3. Other external tables relates indirectly to the internal user table through the external user table

regards,

G

@ Gede : By relates it one-to-one implicitly to internal user table by having the same id.

Do you mean

  1. Having the same Id and Name on the  system.user table  and extuser table; or
  2. having a foreign key to create the one-to-one relationship?

    Because creating a foreign key  is exactly the problem im having, but yeah the first option seems like a nice solution. 


hi, 

I meant both ids have the same values, no need to relate them using foreign key.

regards,

G