Change the schema

Support
#1

How to change the table schema at runtime

We have a case where the schema used is one at development time

but at runtime it is another.

How can I make Jmix recognize this information?

In this case it would be the SCHEMA tag. Is there any way to change this information in the application.properties file?

@DdlGeneration(value = DdlGeneration.DbScriptGenerationMode.DISABLED)
@JmixEntity
@Store(name = “bdOra”)
@Table(name = “RMPEDIDO”, schema = “TESTE”, indexes = {
@Index(name = “IDX_RMPEDIDO_CODFILILAL”, columnList = “codfilial”)
})
@Entity(name = “prm_Rmpedido”)
public class Rmpedido {

#2

Hello,
Yes, there is a solution to this: Spring profiles.
It allows you to have several different .properties files, in the same directory as application.properties, with different configurations that you specify when the application is started, like this:
java -jar application-1.7.17-SNAPSHOT.jar --spring.profiles.active=abc
while having several profiles like this:

application.properties  
application-dev.properties 
application-abc.properties

When you specify abc, it will read the properties from there, and if the property is not specified, it will read it from application.properties.

I use this to connect to different databases and have different email and various other settings for development, test, and production.

Kind regards,
Mladen

#3

OK, I already use these property files, but what line should be added or what parameter should be added to the connection so that in classes that use the “STORE” object related to this connection, it respects the SCHEMA defined in the connection and not the schema that is inside the class?

as in the example below?

Connection

redemais.datasource.url=jdbc:oracle:thin:@[IPSERVIDOR]:1521:[BANCO]
redemais.datasource.username=usuario
redemais.datasource.password=senhadobanco

Class

@DdlGeneration(value = DdlGeneration.DbScriptGenerationMode.DISABLED)
@JmixEntity
@Store(name = “redemais”)
@Table(name = “RMPEDIDO”, schema = “REDEMAIS”, indexes = {
@Index(name = “IDX_RMPEDIDO_CODFILILAL”, columnList = “codfilial”)
})
@Entity(name = “prm_Rmpedido”)
public class Rmpedido {

#4

Hello, I understand now what you want.

Why do you need to have schema defined in the @Table annotation in your entity class, can you not have it defined in your redemais.datasource.url connection parameters in the application.settings?
And then have several application.settings to use with spring profiles? (dev, test, prod1, prod2 …)

#5

Good morning everyone

This is the first time I’ve worked with an Oracle database, for a specific client, and I came across this issue with the schema. My question is precisely about this point. Can I remove the schema from the table annotation and inform it via connection?

Or by another means, such as a variable within the properties file?

In other words, eliminating the need for fixed information within the table. Is it possible? If so, how?

#6

Hi
There are some discussions about this with relation to MS SQL. It seems there IS a property, which is not correctly supported by Jmix ( Liquibase setSchemaName - #16 by gaslov ).
Are we talking about the same ?

#7

Good morning, I understand that this is not the same topic,
in the post you are only referring to updating the database, in my case it is simpler,
I need to change the SCHEMA in some tables, I believe there must be a property that can define the schema at runtime, like this, or is this so far-fetched?

redemais.datasource.schema=ADEFINIR

#8

I don’t work with Oracle database atm, but I checked how one would make a JDBC connection.
Turns out, with Oracle, each user has their own schema and that is their default schema.
You can not put that into JDBC connection string.
One user owns the schema, and when logging in, uses the schema name as the user and then the password. Other users can use objects in that schema, but they need grant, and they do not own it.

Some workarounds are mentioned, such as executing SQL statement to change the default schema after the user is logged in:

ALTER SESSION SET CURRENT_SCHEMA=anotherschema

and that it can be done with a logon trigger on the user and/or database. Huh ?!

create or replace trigger SET_SCHEMA_AFTER_LOGON
    after logon on database
begin
     execute immediate 'alter session set CURRENT_SCHEMA=anotherschema';
end SET_SCHEMA_AFTER_LOGON;

Try this one too, USERNAME[SCHEMA_NAME]

redemais.datasource.url=jdbc:oracle:thin:@[IPSERVIDOR]:1521:[BANCO]
redemais.datasource.username=usuario[anotherschema]
redemais.datasource.password=senhadobanco

Some people say that with [] after the username one can specify the schema.

I also know that some annotations in Spring can be parametrized by the values from application.settings , but let’s first see if [] can solve your problem.

Kind regards,
Mladen

#9

Hi

There is a property
redemais.liquibase.default-schema=ADEFINIR
which is for the liquibase files.

@i.gavrilov did mention
We use main.liquibase prefix instead of spring.liquibase to handle multiple data stores (instead of main there can be another data store name).
It’s mentioned here - Database Schema Migration :: Jmix Documentation.

Me too, I need a place where I can define a schema which is used when accessing databases ( not only the 2 liquibase tables … ).

Perhaps we should make a request for this.