You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/07/03 13:10:40 UTC
[GitHub] [shardingsphere] RaigorJiang opened a new issue, #18811: Improve parameter type specification of DistSQL
RaigorJiang opened a new issue, #18811:
URL: https://github.com/apache/shardingsphere/issues/18811
Hi community,
Since the release of version 5.0.0, DistSQL has provided users with powerful management capabilities.
However, DistSQL lacks something compared to standard SQL statements: data type specifications.
What does that mean?
Let's look at this DistSQL:
```sql
ADD RESOURCE resource_0 (
HOST=127.0.0.1,
PORT=3306,
DB=db0,
USER=root,
PASSWORD=root,
PROPERTIES("maximumPoolSize"=10,"idleTimeout"="30000")
)
````
- `127.0.0.1` should be a string, why don't we need quotes?
- `root` for USER and PASSWORD are both strings, so why don't quotes are needed?
- Why is there no quotes for `10` in PROPERTIES and quotes for `"30000"`?
Look at the next DistSQL:
```sql
CREATE SHARDING ALGORITHM table_inline (
TYPE(NAME=inline,PROPERTIES("algorithm-expression"="t_order_${order_id % 2}"))
);
```
- `NAME=inline`, where inline is a string without quotes
- `t_order_${order_id % 2}` is also a string, but must have quotes
As can be seen from the above, in order to reduce user input, DistSQL ignores some quotes requirements, but this creates confusion for users because they are not sure where quotes are required and where they can be omitted.
Therefore, we need to sort out a specification to make users more clear about the principles of DistSQL syntax.
For reference, MySQL provides some [Literal Values](https://dev.mysql.com/doc/refman/8.0/en/literals.html), and PostgreSQL also provides a description of the [Parameter Names and Values](https://www.postgresql.org/docs/14/config-setting.html) .
I'll sort through this part and add more information.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #18811: Improve parameter type specification of DistSQL
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #18811:
URL: https://github.com/apache/shardingsphere/issues/18811#issuecomment-1200396756
## Update:
I reviewed the syntax of all DistSQL statements, combined with the previous references to MySQL and PostgreSQL, I suggest to distinguish DistSQL parameters into two types: **identifier** and **literal**.
### identifier
The identifier represents an object in the SQL statement. The identifier does not require single quotes (') or double quotes ("). When reserved keywords or special characters appear in the identifier, use backticks (`) to wrap. The following apply to identifiers:
- database name
- table name
- column name
- index name
- resource name
- rule name
- algorithm name
### literals
Types of literals include:
- string, enclosed in single quotes (') or double quotes (")
- int
- boolean, containing only true & false.
If there are other types, use string representation.
### Example
#### 1. ADD RESOURCE
- ##### Before
```sql
ADD RESOURCE resource_0 (
HOST=127.0.0.1,
PORT=3306,
DB=db0,
USER=root,
PASSWORD=root
)
```
- ##### After
```sql
ADD RESOURCE resource_0 (
HOST='127.0.0.1', # string
PORT=3306, # int
DB='db0', # string
USER='root', # string
PASSWORD="root", # string
PROPERTIES("maximumPoolSize"="10") # string
)
```
#### 2. SET VARIABLE
- ##### Before
```sql
SET VARIABLE sql_show = true;
SET VARIABLE transaction_type = XA;
```
- ##### After
```sql
SET VARIABLE sql_show = true; # boolean
SET VARIABLE transaction_type = 'XA'; # string
```
#### 3. CREATE SHARDING TABLE RULE
- ##### Before
```sql
CREATE SHARDING ALGORITHM database_inline (
TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="ds_${user_id % 2}"))
);
CREATE SHARDING KEY GENERATOR snowflake_key_generator (
TYPE(NAME=SNOWFLAKE)
);
CREATE SHARDING TABLE RULE t_order (
DATANODES("ds_${0..1}.t_order_${0..2}"),
DATABASE_STRATEGY(TYPE=STANDARD,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=database_inline),
KEY_GENERATE_STRATEGY(COLUMN=id,KEY_GENERATOR=snowflake_key_generator)
);
```
- ##### After
```sql
CREATE SHARDING ALGORITHM database_inline (
TYPE(NAME="INLINE",PROPERTIES("algorithm-expression"="ds_${user_id % 2}"))
);
CREATE SHARDING KEY GENERATOR snowflake_key_generator (
TYPE(NAME="SNOWFLAKE")
);
CREATE SHARDING TABLE RULE t_order (
DATANODES("ds_${0..1}.t_order_${0..2}"),
DATABASE_STRATEGY(TYPE="STANDARD",SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=database_inline),
KEY_GENERATE_STRATEGY(COLUMN=id,KEY_GENERATOR=snowflake_key_generator)
);
```
#### 3. CREATE SHARDING TABLE RULE (autoTable)
- ##### Before
```sql
CREATE SHARDING TABLE RULE t_order (
RESOURCES(resource_0,resource_1),
SHARDING_COLUMN=order_id,TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=4)),
KEY_GENERATE_STRATEGY(COLUMN=id,TYPE(NAME=snowflake))
);
```
- ##### After
```sql
CREATE SHARDING TABLE RULE t_order (
RESOURCES(resource_0,resource_1),
SHARDING_COLUMN=order_id,TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="4")),
KEY_GENERATE_STRATEGY(COLUMN=id,TYPE(NAME="snowflake"))
);
```
### Special Note
- key and value in all properties in DistSQL should be string
### Tasks
We need to do:
1. Redefine identifier and literals in g4 grammar of DistSQL
2. Change type definitions in g4 of RDL, RQL, RAL, RUL
3. Adjust the way to get parameters in `visitor`, for example, need to handle quotes in strings
4. Update the syntax and usage documentation
5. Update parser test case (in shardingsphere-parser-test)
Since there are many modules affected, I will create task issues for them separately.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang closed issue #18811: Improve parameter type specification of DistSQL
Posted by GitBox <gi...@apache.org>.
RaigorJiang closed issue #18811: Improve parameter type specification of DistSQL
URL: https://github.com/apache/shardingsphere/issues/18811
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #18811: Improve parameter type specification of DistSQL
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #18811:
URL: https://github.com/apache/shardingsphere/issues/18811#issuecomment-1223622807
Completed.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org