set table schema at runtime

set table schema at runtime

Postby Mac » Thu 03 Dec 2015 14:43

I have an oracle db running our ERP system which uses the concept of shared tables (not my choice!)

I can only login with a user 'query' to a query schema. This user is given read-only permissions to other schemas and is the only means of accessing data in these other schemas.

I access various sprocs/functions etc in my query schema as it's the only schema which I can modify, but it has no tables or data. They exist in the other schemas.

Every schema apart from Query has identical sets of tables.

schemaA.Table1
schemaB.Table1
query.MySproc


A "shared table" means that the data is only in schemaA even though empty tables exist in the other schemas. I can only access the data by qualifying shared table with the schema name.

It means I often end generating queries like

Code: Select all
from table1 in context.GetData<Table1>()
join table2 in context.GetData<Table2>() on table1.col1 equals table2.col1
join table3 in context.GetData<Table3>() on table2.col2 equals table3.col2


which geneates pl/sql

Code: Select all
select (..fields)
from schemaA.table1 t1
inner join schemaB.table2 t2 on t1.col1 = t2.col1
inner join schemaA.table3 t3 on t2.col2 = t3.col2


I can never know the specific schema I am targeting for non shared data until runtime as it is based on information associated with a ERP user.

How can I set the schema per table at runtime based on some business logic per user?

Previously I modified the T4 templates to alter the
Code: Select all
[Table(Name = "SharedPrefix.TableA")]
which would work if I could set the default schema and not prefix the TableAttributes.

I now have to use three different schemas query, sharedTableSchema, nonsharedTableSchema all from the same dataContext.

Code: Select all
TableAttribute
is sealed so cannot override, so is
Code: Select all
AttributeMappingSource
. Haven't been able to inherit from
Code: Select all
MappingSource
to override the metamodel, type resolution.

Currently I'm running a static method to prefix TableAttribute names in the T4 templates and I've overloaded the datacontext constructors to accept the schema name and am running a sql command
Code: Select all
ALTER SESSION SET CURRENT_SCHEMA=
when connecting.

Looking at ways to use IoC interception also to try and inject the schema or change the mapping at runtime but its all a bit hacky and would be so much easier to have the means to map each table using runtime instead of compile time logic.

Maybe I've missing something obvious in the code but when it comes to my admittedly very specific scenario, it's is really awkward to get your product to work well.

Any ideas or thoughts on providing an extensibility point to make this possible?
Mac
 
Posts: 12
Joined: Sun 23 Aug 2009 22:33

Re: set table schema at runtime

Postby MariiaI » Mon 07 Dec 2015 11:20

I can never know the specific schema I am targeting for non shared data until runtime as it is based on information associated with a ERP user.
...
How can I set the schema per table at runtime based on some business logic per user?

You can try using fluent mapping approach for this. Please refer to this topic in the LinqConnect documentation:
http://www.devart.com/linqconnect/docs/?FluentMapping.html

If you'll encounter any difficulties with this or have any questions, feel free to contact us.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for Oracle