Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rxfed-00Ak8s-DQ for pgadmin-hackers@arkaria.postgresql.org; Fri, 19 Apr 2024 04:15:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rxfec-00EekH-2I for pgadmin-hackers@arkaria.postgresql.org; Fri, 19 Apr 2024 04:15:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rxfeb-00Eek9-IY for pgadmin-hackers@lists.postgresql.org; Fri, 19 Apr 2024 04:15:13 +0000 Received: from mail-oa1-x30.google.com ([2001:4860:4864:20::30]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rxfeX-003Yfg-Sc for pgadmin-hackers@postgresql.org; Fri, 19 Apr 2024 04:15:11 +0000 Received: by mail-oa1-x30.google.com with SMTP id 586e51a60fabf-23319017c4cso1084711fac.2 for ; Thu, 18 Apr 2024 21:15:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1713500109; x=1714104909; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=SDEokvFbE8O+sxKS4G8gtBabPMlh9ZoqqD6SvkNK0Ts=; b=lerflGEnuZwm+Jxts4JVP522MQq9ykAawfA04e1dC68P+tyKORwBYqotJN0uVTYZg+ iAQzw8u3xanEK1/iQCiGK3+0uXGeoISsPBm+Yc4QKQo017TJl9z33VU1UneWXSrHrIUh FN/Fb3RgxlGYs2DiQ33XRY4pugrPW33E17qcAztONPhQrXBs6SmXbL7z4a20C+G5o8AN fpaTUeqTiWyNj88qTpXbfHmxz0FTFXh+BihyC8GzbrjAQMlOIMrRIz/Vx+HsszhWDarq 6oQ+FXcIleWrPv5LHsjKq7Rb65O+kbAKgaawosU7YepzpYxoYna5JvlvxPd+XCN5ECai c3Jw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713500109; x=1714104909; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=SDEokvFbE8O+sxKS4G8gtBabPMlh9ZoqqD6SvkNK0Ts=; b=Wpds9d3EzZ/+HTCXnVlq5Z+59jZ0OukWl6y1OoSmplLj+d8cypAdY+X4bMIYBFHCmw lTH9D7XdtLnSFb83QhIBI9aSrm/a+LatFb4+NiqrUtcQekW1n0B2cIOq9jiK3wD7TwLk oVzI54go8KSrW6LiB/mZISFnw1aR9xvt8O5MXXaUOvWMu89+R9DhN6dW3NKXYu2mfF3l dIxUxjrnVBgdAMclMJd6mDnNZzSmz8KYvkeVppRxf03IpURsPp18khaARlxh3zXssYgE tMKNF/lD2xfEUwo372KKCKQU5BvzN+TxMH+jumAw240ndTa5TItVC2XjUvj9az/+SlNv hSdg== X-Forwarded-Encrypted: i=1; AJvYcCXZt7vsOADXhuu3F47AwkyGz/WC+WVOT7vm9j4qoRCPkD2kJ/NIW1GVGqLrvb+OLH2q/2aMsoyFPZHdhZId8F5w403FMrz+aVBJoqyd6O4= X-Gm-Message-State: AOJu0YzOMqFd3xfJtVqVdTncZAkaN5UDqElkKlLpQm2Qd//eHC/+yKTE F7YqhR8n7w7N/CHD1QTK9EEO1ktS104X574KSN59BCYmTFam6dArXaCfNuGnSihKTUsTRVnGsR1 tgDLHzqr3EvQT6roxvy9Nyn+LAyTnqdhdr9q/ X-Google-Smtp-Source: AGHT+IGK9FZr0iB5zhIX2aNHThWuGn6IYE8Kg5TnjiKx77TzjvebxZD11JP1p6clSOGyixL2zy/jAgI8NB/v69cgS38= X-Received: by 2002:a05:6870:7999:b0:234:e49:d264 with SMTP id he25-20020a056870799900b002340e49d264mr1209987oab.11.1713500108925; Thu, 18 Apr 2024 21:15:08 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Aditya Toshniwal Date: Fri, 19 Apr 2024 09:44:32 +0530 Message-ID: Subject: Re: Regarding feature #6841 To: Dave Page Cc: Anil Sahoo , pgadmin-hackers@postgresql.org Content-Type: multipart/alternative; boundary="00000000000050cbb206166b5508" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000050cbb206166b5508 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Dave, On Thu, Apr 18, 2024 at 8:07=E2=80=AFPM Dave Page wrote= : > Hi > > On Thu, 18 Apr 2024 at 15:26, Anil Sahoo > 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 t= he >> 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 sing= le >> 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 comm= ent >> 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 t= he >> 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 =3D 'DELETE FROM foo; SELECT * FROM foo;' > > or > > my_sql =3D """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=3D.... > > (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 ge= t > 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. > > >> >> Regards >> Anil >> -- >> >> >> >> *Anil Sahoo* >> >> Software Engineer >> >> www.enterprisedb.com >> >> Power to Postgres >> >> >> >> >> >> >> >> On Thu, Apr 18, 2024 at 2:24=E2=80=AFPM Dave Page wr= ote: >> >>> Hi >>> >>> On Wed, 17 Apr 2024 at 15:08, Anil Sahoo >>> wrote: >>> >>>> Hi Hackers, >>>> >>>> This feature is about executing a query at the cursor position. And >>>> that query can be a one line or multiline. I have assigned a play icon >>>> button and F5 as the keyboard shortcut for the Execute Query feature, = and >>>> for Execute Script, Playlist icon button and Alt+F5(Others),Option+F5(= Mac) >>>> as keyboard shortcut. >>>> >>>> As now the query can run at cursor position, so for user convenience I >>>> am showing the current query just beside the Data Output toolbar. And = on >>>> hover of the text, it will show the whole query as a tooltip. This que= ry >>>> text will be available for both Execute Script and Execute Query. >>>> >>>> I have made the UI change for the feature #6841 >>>> . >>>> >>>> Please provide your suggestions and feedback if these changes look oka= y >>>> to you. >>>> >>> >>> How is this parsing the query to figure out the correct text to send to >>> the server? For example, I notice you have no semi-colons on many of th= e >>> queries in your test; is it breaking on newlines? What if there's a new= line >>> (or multiple of them) in the query string? How does it cope with an >>> anonymous block containing multiple queries, or a pl/whatever function >>> definition that might contain queries within its text? Or a view defini= tion? >>> >>> -- >>> Dave Page >>> pgAdmin: https://www.pgadmin.org >>> PostgreSQL: https://www.postgresql.org >>> EDB: https://www.enterprisedb.com >>> >>> > > -- > Dave Page > pgAdmin: https://www.pgadmin.org > PostgreSQL: https://www.postgresql.org > EDB: https://www.enterprisedb.com > > --=20 Thanks, Aditya Toshniwal pgAdmin Hacker | Sr. Software Architect | *enterprisedb.com* "Don't Complain about Heat, Plant a TREE" --00000000000050cbb206166b5508 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi=C2=A0Dave,

On Thu, Apr 18, 2024 at 8:0= 7=E2=80=AFPM Dave Page <dpage@pgadm= in.org> wrote:
Hi

On Thu, 18 Apr 2024 at 15:26, Ani= l Sahoo <anil.sahoo@enterprisedb.com> wrote:
Hi Dave,
We took help fro= m 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 statemen= ts 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 ch= ecked where our cursor is in editor and checked=C2=A0with the query=C2=A0an= d through this we got the actual query at cursor position.

For example,=C2=A0
  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 extra= cted.
  2. if the cursor is at starting or ending position or anywhe= re in between a comment that can be single line or multi line then the comm= ent gets extracted.
  3. if the cursor is at a position where the previo= us line has a query then that query gets extracted.=C2=A0
For the anonymous block containing multiple queries, code mirror gives t= he statements differently. That is an incomplete query we can say, so the q= uery tool gives error. We can say some limitations are there with Code Mirr= or.

Please let me know if you have any questions o= n this.

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

DELE= TE FROM foo; SELECT * FROM foo;
It will depe= nd where the cursor is and will pick one of the query, not both.=C2= =A0

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

<= /div>
my_sql =3D 'DELETE FROM foo; SELECT * FROM foo;'

or=C2=A0

my_sql =3D ""&= quot;DELETE FROM foo;=C2=A0
SELECT * FROM foo;
&quo= t;""
Since it is a part of the str= ing, it will not run the string part. It will execute along with my_sql=3D.= ...=C2=A0

(those are jus= t simple examples from the top of my head).=C2=A0

= It could be extremely dangerous if we or CodeMirror mis-parses something, w= hich seems quite possible unless it has access to the actual parser that Po= stgreSQL uses. Which makes me think... what of EPAS? It has an extended par= ser to handle some of the Oracle compatible syntax. Will CodeMirror get tha= t right?
CodeMirror parser only provides par= sing for standard SQL grammar. It doesn't even understand pl/pgsql. It = detects the query based on semicolons very effectively.=C2=A0We have add= ed our own logic to take that query provided by CM and separate it by new l= ine.
Instead of making it as the main execute=C2=A0button, I r= ealise we should make it as the second execute, and keep the main execute u= ntouched.
=C2=A0

Regar= ds
Anil
--

Anil Sahoo

Software Engineer

www.enterprisedb.com<= /p>

Power = to Postgres

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0



On Thu, Apr 18, 2024 at 2:24=E2=80=AFPM Dave Page <dpage@pgadmin.org> wrote:
Hi

On Wed, 17 Apr 2024 at 15:08, Anil Sahoo <anil.sahoo@enterprisedb.com> w= rote:
Hi Hackers,

This feature is about executing a q= uery=C2=A0at the=C2=A0cursor position. And that query can be a one=C2=A0lin= e or multiline. I have assigned a play icon button and F5 as the keyboard s= hortcut for the Execute Query feature, and for Execute Script, Playlist ico= n button and Alt+F5(Others),Option+F5(Mac) as keyboard shortcut.
=
As now the query can run at cursor position, so for user con= venience I am showing the current query just beside the Data=C2=A0Output to= olbar. And on hover of the text, it will show the whole query as a tooltip.= This query text will be available for both Execute Script and Execute Quer= y.

I have made the UI change for the feature #6841.=C2=A0

Please provide your suggestions= and feedback if these changes look okay to you.

How is this parsing the query to figure out the correct t= ext to send to the server? For example, I notice you have no semi-colons=C2= =A0on many of the queries in your test; is it breaking on newlines? What if= there's a newline (or multiple of them) in the query string? How does = it cope with an anonymous block containing multiple queries, or a pl/whatev= er function definition that might contain queries within its text? Or a vie= w definition?
=C2=A0
--


--
Dave Page


--
Thanks,
Aditya Toshniwal
pgAdmin Hacker=C2=A0| Sr. Software Architect=C2=A0| enterprisedb.com