sp_QuickieStore - Add the ability to search for queries with square brackets
billfinch opened this issue · 6 comments
Is your feature request related to a problem? Please describe.
Our development team uses .NET Entity Framework and LLBLGenPro ORM tools for the bulk of our application stack. The queries generated by these tools use square brackets (Ex. [alias].[column] and [schema].[table]). Searching for these queries in Query Store requires escaping the bracket characters in a LIKE search. Currently, it does not appear that sp_QuickieStore is configured to allow for this escape sequence.
Example:
query_sql_text LIKE '%SELECT \[Extent1\].\[customer_id\] FROM \[dbo\].\[customers\] AS \[Extent1\]%' ESCAPE '\'
Describe the solution you'd like
Add two parameters to sp_QuickieStore and add associated code to the section building the query for @query_text_search
@escape_query_text_brackets BIT = 0, /*Set this bit to 1 to search for query text containing square brackets (common in .NET Entity Framework and other ORM queries)*/
@escape_character NCHAR(1) = '\', /*Sets the ESCAPE character for special character searches, defaults to the SQL standard backslash (\) character*/
/* If our query texts contains square brackets (common in Entity Framework queries), add a leading escape character to each bracket character */
IF @escape_query_text_brackets = 1
BEGIN
SELECT @query_text_search = REPLACE(REPLACE(@query_text_search,'[',@escape_character+'['),']',@escape_character+']')
END
/* If we are escaping bracket character in our query text search, add the ESCAPE clause and character to the LIKE subquery*/
IF @escape_query_text_brackets = 1
BEGIN
SELECT @sql = REPLACE(@sql,N'@query_text_search','@query_text_search ESCAPE '''+@escape_character+'''')
END
Describe alternatives you've considered
I have not considered alternate solutions as there does not appear to be a way to game the search in the existing sp_QuickieStore to handle brackets in query text.
Are you ready to build the code for the feature?
I have built and tested a prototype of these changes that returns queries from my production query store containing square brackets. I am ready to submit a pull request with these changes and supporting updates to the help section.
Woah, neat! Big thumbs up from me. Pull request away.
Hey! Thanks for the approval! I just need permissions to push to the repo apparently:
$ git push -u origin 630-ADD-ESCAPE-to-Query-Text-Search
info: please complete authentication in your browser...
remote: Permission to erikdarlingdata/DarlingData.git denied to billfinch.
fatal: unable to access 'https://github.com/erikdarlingdata/DarlingData.git/': The requested URL returned error: 403
I'm not very familiar with Git from the command line, so I'm not sure what you're trying to do there. But no, I don't grant write privileges to anyone wishing to push changes in.
The usual process for people to work on code is to:
- Fork the dev branch
- Work on their local copy
- Publish their branch
- Create a Pull Request for me to merge in
There's a handy link in the contributing guide with more details, too: https://github.com/erikdarlingdata/DarlingData/blob/main/CONTRIBUTING.md
Thanks!
Got it, thanks! I am used to the GitHub Enterprise workflow we use in house with our own repositories, so I just needed to shift my paradigm to Open Source, LOL.
PR built and submitted!
No problem at all! I'll have some time to kick it around tomorrow and get it tested/merged. Thank you for the submission.
Closed via #361