You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "TherChenYang (via GitHub)" <gi...@apache.org> on 2024/02/01 07:48:29 UTC
[PR] Adjust Literal Antlr file to support SQL Server escape [shardingsphere]
TherChenYang opened a new pull request, #29948:
URL: https://github.com/apache/shardingsphere/pull/29948
Fixes #29156.
Changes proposed in this pull request:
- Add parsing for the table name of User
- Adjust string parsing in SQLServer
+ In SQLServer,Support the following syntax, `select '\'`
+ In Mysql, this syntax is not supported. `select '\'`
+ So I adjusted the syntax of string to the following rules,You can focus on case1 and case2 in the SQL list.
* 
### The supported SQL is as follows
#### case1
```sql
SELECT 'DECLARE @serverName NVARCHAR(512) = N''' + value + ''''
FROM sys.dm_hadr_fabric_config_parameters
WHERE parameter_name = 'DnsRecordName'
```
#### case2
```sql
SELECT 'DECLARE @node NVARCHAR(512) = N''' + NodeName + '.' + Cluster + ''''
FROM (
SELECT SUBSTRING(replica_address, 0, CHARINDEX('\\', replica_address)) AS NodeName,
RIGHT(service_name, CHARINDEX('/', REVERSE(service_name)) - 1) AppName,
JoinCol = 1
FROM sys.dm_hadr_fabric_partitions fp
INNER JOIN sys.dm_hadr_fabric_replicas fr
ON fp.partition_id = fr.partition_id
INNER JOIN sys.dm_hadr_fabric_nodes fn
ON fr.node_name = fn.node_name
WHERE service_name LIKE '%ManagedServer%'
AND replica_role = 2
) t1
LEFT JOIN (
SELECT value AS Cluster,
JoinCol = 1
FROM sys.dm_hadr_fabric_config_parameters
WHERE parameter_name = 'ClusterName'
) t2
ON (t1.JoinCol = t2.JoinCol)
INNER JOIN (
SELECT [value] AS AppName
FROM sys.dm_hadr_fabric_config_parameters
WHERE section_name = 'SQL'
AND parameter_name = 'InstanceName'
) t3
ON (t1.AppName = t3.AppName)
```
#### case other
```sql
SELECT DISTINCT user.FirstName, user.LastName
INTO ms_user
FROM user INNER JOIN (
SELECT * FROM ClickStream WHERE cs.url = 'www.microsoft.com'
) AS ms
ON user.user_ip = ms.user_ip
```
```sql
SELECT
req.session_id
, req.total_elapsed_time AS duration_ms
, req.cpu_time AS cpu_time_ms
, req.total_elapsed_time - req.cpu_time AS wait_time
, req.logical_reads
, SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1
THEN DATALENGTH(ST.text)
ELSE req.statement_end_offset
END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '),
1, 512) AS statement_text
FROM sys.dm_exec_requests AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC
```
```sql
SELECT t.text,
(qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
(qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
qs.total_logical_writes / qs.execution_count AS avg_writes,
(qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions
FROM sys.dm_exec_query_stats qs
CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE t.text like '<Your Query>%'
-- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped.
ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC
```
---
Before committing this PR, I'm sure that I have checked the following options:
- [ ] My code follows the [code of conduct](https://shardingsphere.apache.org/community/en/involved/conduct/code/) of this project.
- [ ] I have self-reviewed the commit code.
- [ ] I have (or in comment I request) added corresponding labels for the pull request.
- [ ] I have passed maven check locally : `./mvnw clean install -B -T1C -Dmaven.javadoc.skip -Dmaven.jacoco.skip -e`.
- [ ] I have made corresponding changes to the documentation.
- [ ] I have added corresponding unit tests for my changes.
--
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
Re: [PR] Adjust Literal Antlr file to support SQL Server escape [shardingsphere]
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu merged PR #29948:
URL: https://github.com/apache/shardingsphere/pull/29948
--
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