Consider supporting PostgreSQL/openGauss schema statements

[tuichenchuxin]

Background

In the SharedingSphere community, we have heard a lot of exception feedback about PostgreSQL/openGauss schema, such as #15579, #15145, #14004 and #13638. In order to improve experience of PostgreSQL/openGauss users, we need to support PostgreSQL/openGauss schema now.

How to support PostgreSQL/openGauss schema

Currently in the config-xxx.xml file, the actual meaning of schemaName is database, so we decided to adjust the schemaName configuration to databaseName.

In order to be compatible with the original schemaName configuration, users can still configure schemaName: sharding_db in the yaml file, which has the same effect as configuring databaseName: sharding_db.

databaseName: sharding_db

dataSources:
  ds_0:
    url: jdbc:postgresql://127.0.0.1:5432/demo_ds_0?currentSchema=schema_0959,public
    username: postgres
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  ds_1:
    url: jdbc:postgresql://127.0.0.1:5432/demo_ds_1?currentSchema=schema_0959,public
    username: postgres
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

Users can specify the search_path of PostgreSQL/openGauss by configuring currentSchema=schema_0959,public. When the user executes the SQL without the schema qualified name, the search will be performed according to the search_path.

When users execute CREATE SCHEMA or DROP SCHEMA statements, we will route these SQL statements to all PostgreSQL/openGauss databases for execution, and add schema information to the metadata of ShardingSphere.

In addition, we also need to support the SET SEARCH_PATH statement on the ShardingSphere-Proxy adaptor to allow users to switch search_path.

Tasks

• adaptors supports databaseName configuration and is compatible with schemaName
• adjust metadata loading logic to load tables under all user schemas(now just load tables under public schema)
• adjust shardingsphere kernel logic to support PostgreSQL schema bases SQL
• adjust mode logic to support databaseName configuration @ZjcNB
• add more test case for PostgreSQL schema

[cheese8]

👍 I would enhance EncryptPropertiesBuilder if this supported.

[RaigorJiang]

Related issue: #13638

[strongduanmu]

Since this task is too big, I will split the implementation of search_path into other issues. This issue is responsible for supporting SQL execution with the schema modifier.