Re: Regarding feature #6841

Hi Dave,

On Thu, Apr 18, 2024 at 8:07 PM Dave Page <dpage@pgadmin.org> wrote:

Hi

On Thu, 18 Apr 2024 at 15:26, Anil Sahoo <anil.sahoo@enterprisedb.com> wrote:

Hi Dave,

We took help from Code Mirror, i.e Code Mirror gives the parsed SQL from the editor through a tree called syntaxTree and by using some logic we extracted the statements which have semicolon in it and also added some extra logic to break the whole query on next of next line as empty or if comments are there.

Using all this logic we got the individual queries and checked where our cursor is in editor and checked with the query and through this we got the actual query at cursor position.

For example, 

1. if the cursor is at starting or ending position or anywhere in between a query with semicolon or without semicolon, that can be single line or multi line then the query gets extracted.
   
2. if the cursor is at starting or ending position or anywhere in between a comment that can be single line or multi line then the comment gets extracted.
3. if the cursor is at a position where the previous line has a query then that query gets extracted. 

For the anonymous block containing multiple queries, code mirror gives the statements differently. That is an incomplete query we can say, so the query tool gives error. We can say some limitations are there with Code Mirror.

Please let me know if you have any questions on this.

My main concern is that it doesn't get it wrong. Ever. Consider:

DELETE FROM foo; SELECT * FROM foo;

It will depend where the cursor is and will pick one of the query, not both. 

Is that one statement or two? What if it's in the middle of a pl/python3 function:

my_sql = 'DELETE FROM foo; SELECT * FROM foo;'

or 

my_sql = """DELETE FROM foo; 

SELECT * FROM foo;

"""

Since it is a part of the string, it will not run the string part. It will execute along with my_sql=.... 

(those are just simple examples from the top of my head). 

It could be extremely dangerous if we or CodeMirror mis-parses something, which seems quite possible unless it has access to the actual parser that PostgreSQL uses. Which makes me think... what of EPAS? It has an extended parser to handle some of the Oracle compatible syntax. Will CodeMirror get that right?

CodeMirror parser only provides parsing for standard SQL grammar. It doesn't even understand pl/pgsql. It detects the query based on semicolons very effectively. We have added our own logic to take that query provided by CM and separate it by new line.

Instead of making it as the main execute button, I realise we should make it as the second execute, and keep the main execute untouched.