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.
         * ![image](https://github.com/apache/shardingsphere/assets/124348939/f2f769c4-d8da-458e-a5b0-95a773916428)
   
          
   ### 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