To use ShardingProxy for sharding the databases based on the order type without sharding the tables?

[tts2]

Question

For English only, other languages will not accept.

Before asking a question, make sure you have:

• Googled your question.
• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will close it.

Table Name: orderinfo

Column Name	Data Type	Description
order_id	INT	Unique identifier for the order
order_date	DATE	Date when the order was placed
amount	DECIMAL(10,2)	Total amount of the order
order_type	VARCHAR(20)	Type of the order (e.g., sc_2017112116450001, sc_2017112116450002, etc.)

config-sharding.yaml

databaseName: restaurant_db

dataSources:
common_db:
url: jdbc:mysql://localhost:3306/restaurant?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
pm_sc_2017112116450001_db:
url: jdbc:mysql://localhost:3306/pm_sc_2017112116450001_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
pm_sc_2017112116450002_db:
url: jdbc:mysql://localhost:3306/pm_sc_2017112116450002_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
pm_sc_2017112116460003_db:
url: jdbc:mysql://localhost:3306/pm_sc_2017112116460003_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1

rules:

• !SHARDING
  tables:
  orderinfo:
  actualDataNodes: $->{order_type=='sc_2017112116450002'?'pm_sc_2017112116450002_db':'common_db'}.orderinfo
  tableStrategy:
  none:

  defaultDatabaseStrategy:
  none:

  defaultTableStrategy:
  none:

Error:
Exception in thread "main" groovy.lang.MissingPropertyException: No such property: order_type for class: Script1 at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:67) at org.codehaus.groovy.vmplugin.v8.IndyGuardsFiltersAndSignatures.unwrap(IndyGuardsFiltersAndSignatures.java:161)

[RaigorJiang]

Hi @tts2,
Welcome to Apache ShardingSpehre.

From the provided configuration, you don’t understand the meaning of each item, please read the configuration manual and examples first to help you complete the task.
https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/yaml-config/rules/sharding/

[tts2]

For example: In SaaS mode, each user (id=SC_202307311826340001) has their own database, and the data needs to be isolated. I want to know if Sharding Proxy can achieve this?

[RaigorJiang]

For example: In SaaS mode, each user (id=SC_202307311826340001) has their own database, and the data needs to be isolated. I want to know if Sharding Proxy can achieve this?

Yes this is common usage, you can use databaseStrategy or defaultDatabaseStrategy

[tts2]

Can you achieve storing the database data into your own database based on the tenant ID?
Can the data be stored from the user's perspective rather than the server's perspective based on the tenant ID?

[tts2]

Who should we contact for business consultation? We might need custom development.