0

I have problem with setting up schema for my app. In Postgres I have two schemas: public and portal. I want this app to use "portal" schema (that is how the second schema is named), but unfortunately I cant achieve that with known techniques (from another issues on stack and google).

This is my DataSourceConfig:

@Configuration
public class DataSourceConfig {

    @Value("${PORTAL_DB_URL}")
    private String databaseUrl;
    @Value("${PORTAL_DB_USER_NAME}")
    private String userName;
    @Value("${PORTAL_DB_PASSWORD}")
    private String password;
    @Value("${PORTAL_DB_DRIVER}")
    private String driver;
    @Value("${PORTAL_DB_SCHEMA}")
    private String schema;

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.tpp-portal")
    public DataSource dataSource(@Value("${PORTAL_DB_SCHEMA}") String schema){
        this.schema = schema;

        DataSource dataSource = DataSourceBuilder
                .create()
                .username(userName)
                .password(password)
                .url(databaseUrl)
                .driverClassName(driver)
                .build();

/*        if(!schema.isEmpty() && dataSource instanceof org.apache.tomcat.jdbc.pool.DataSource){
            ((org.apache.tomcat.jdbc.pool.DataSource) dataSource).setInitSQL("ALTER SESSION SET CURRENT_SCHEMA=" + schema);
        }*/

        return dataSource;
    }
}

MainApplication.java:

@Configuration
@SpringBootApplication
@EnableAutoConfiguration
public class MainApplication  {

    public static void main(String[] args) {
        SpringApplication.run(MainApplication.class, args);


    }

}

application.properties:

server.port=8081

liquibase.changeLog=classpath:db/changelog/db.changelog-master.xml

spring.mvc.view.suffix=.html

spring.datasource.portal.test-on-borrow=true
spring.datasource.portal.validation-query=SELECT 1;
spring.datasource.portal.validation-interval=3000
spring.datasource.portal.remove-abandoned=true

PORTAL_DB_DRIVER=org.postgresql.Driver
PORTAL_DB_URL=jdbc:postgresql://localhost/db_name?public=portal
PORTAL_DB_USER_NAME=db_name
PORTAL_DB_PASSWORD=db_password
PORTAL_DB_SCHEMA=portal

logging.level.root=INFO

Does anyone knows how it is possible to do that according to those files up here? Or should I change it inside my DB??

db.changelog-master.xml

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <include file="db/dev/changelog/1.0/1.0-init-tables.xml"/>
    <include file="db/dev/changelog/1.0/1.0-init-data.xml"/>
</databaseChangeLog>

and init-tables.xml

<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <changeSet id="1.0-init-tables" author="portal">

        <createTable tableName="service">
            <column name="id"
                    type="INTEGER">
                <constraints primaryKey="true"
                             primaryKeyName="PK_service_id"/>
            </column>

            <column name="status"
                    type="VARCHAR(32)"/>
            <column name="name"
                    type="VARCHAR(64)"/>
            <column name="created_By"
                    type="VARCHAR(64)"/>
            <column name="created_Date"
                    type="DATETIME"/>
            <column name="modified_By"
                    type="VARCHAR(64)"/>
            <column name="modified_Date"
                    type="DATETIME"/>
        </createTable>
    </changeSet>

I have tried several methods on those xml files but it also didn't work...

Assume that, I have a correct connection with DB, and my schema already exists (but its empty ofc.).

Any help will be appreciated!

xross
  • 597
  • 4
  • 9
  • 25
  • What do you mean by "want to use"? At which point? The default schema is `public` if you don't specify anything else, if you want to use another schema, you'll need to specify it whereever it is that you want to use it. For example in your changeset you're not specifying the schema, so `public` is used. – Kayaman Oct 04 '17 at 14:14
  • I thought that it can be only specified in application properties and dataSource. If there is not, then I would like somehow specify it in changeset. If I remember correctly I used schemaName="portal". Neither in createTable nor in changeset it worked. – xross Oct 04 '17 at 14:22
  • 1
    Well if it didn't work, you did something wrong. Look at the XML schema and check where you need to specify it. Also I'd recommend not using `VARCHAR` but only `TEXT` with Postgres, unless you have a *very good* reason not to. – Kayaman Oct 04 '17 at 16:30
  • yes, that is true, that the limitations on the live DB is problematic, but varchar is a little bit faster. It is considered especially in a moment later, when DB is filled with data and I have to make for example the views – xross Oct 05 '17 at 06:50
  • Varchar isn't faster on Postgres. On other databases there may be differences, but there's [no reason](https://stackoverflow.com/questions/4848964/postgresql-difference-between-text-and-varchar-character-varying) to use anything else than `TEXT` for textual data in Postgres. – Kayaman Oct 05 '17 at 06:59

0 Answers0