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 1rxSuJ-008urv-9a for pgadmin-hackers@arkaria.postgresql.org; Thu, 18 Apr 2024 14:38:36 +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 1rxStJ-005aCk-Jv for pgadmin-hackers@arkaria.postgresql.org; Thu, 18 Apr 2024 14:37:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rxStJ-005aCc-AF for pgadmin-hackers@lists.postgresql.org; Thu, 18 Apr 2024 14:37:33 +0000 Received: from mail-lj1-x236.google.com ([2a00:1450:4864:20::236]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rxStF-001doR-Jw for pgadmin-hackers@postgresql.org; Thu, 18 Apr 2024 14:37:32 +0000 Received: by mail-lj1-x236.google.com with SMTP id 38308e7fff4ca-2db17e8767cso15458411fa.3 for ; Thu, 18 Apr 2024 07:37:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgadmin.org; s=google; t=1713451048; x=1714055848; 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=ducaBLj/ZyBuJlwNzvz1W0AAfUqeZMA9tj9IsAYOwn8=; b=RelURgGY4C1iG0cAhKY2/1CNzBkr4m5+BhKsiCOdtoNw48FwGwyyUb9CgVdWz1y5fl Xtd0tavBM/AM5WJCQYefGjsCYw/RIy1Vaj4iVvF1weP5bS10BIbVe4bCmzvcc3+JPicr evrUbcINJ2q8ooPCHDRkvpSceQKgivmzRXU8HGlBZf/F/eVN20Ut6oVm5ywzEth06o8V X9Cm8IUnN9ER3vcIgnzm9k/Nr2USwEmiljfaEmf/Zul5XFQjwHrHllz51YmNC7U6j4co c164xh4Id9Eb8nlKsPt2CdsbGGtRgT+1SsdTV8nT7KeKEc6jMnwginBLgv4GkcksiO8H HWGA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713451048; x=1714055848; 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=ducaBLj/ZyBuJlwNzvz1W0AAfUqeZMA9tj9IsAYOwn8=; b=pTj2C1I4TRS12XKAlu83EISUmxLOr2SJgRJIbrMdgW4lL8NXVPuNWW213BoryepmzU d6l/R7TjI2LUzt8PLYes2TwmfEivoCKXE4rwbXcrRJuJ8n6nbz4B3PDZ86i0/8weTt3T +IH5TwBAjGCoXPWwUkqvPA909qd2tjdY9MfbxVFQ56ita5NJjCi/JjDktO6OBrxO4Xxf eUoQwIWg2lKdElkIOhZwnSFo1vDka8XHNqXCWThb8APwciaemA6kPm4D1P3QeoWfKUuh ujm7+t/kM0fTXrfnokcer44x7/afCxIwS4oMQJ1a9SnhLzgz1F4rzAmTSrsfLfLBVboI cdUw== X-Gm-Message-State: AOJu0YxEaWZ8nIh6xHiA0tRw3yVLWMsuQW9DF2gOIWP5o/WJ7rosn1FO Ai9xCWEFr219in/hoyT8koMOUIDMq8KzpeJkGGjhl21gCDDNqiW55dCJBjLxY8Cri0ptdOgN5wT vkxKT5TKczU8kA5OETYJHIVHE8Ezeciqy0BXB X-Google-Smtp-Source: AGHT+IECHt6nAasIdEYoh3cznmOHL79TDPrPov9aivdcSx0b8TEZrS4SFsP/JN0mcXqzGBinx06odz2Y466b8qgZMxY= X-Received: by 2002:a05:6512:2511:b0:516:d2b9:20c3 with SMTP id be17-20020a056512251100b00516d2b920c3mr2418671lfb.10.1713451048094; Thu, 18 Apr 2024 07:37:28 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Dave Page Date: Thu, 18 Apr 2024 15:37:17 +0100 Message-ID: Subject: Re: Regarding feature #6841 To: Anil Sahoo Cc: pgadmin-hackers@postgresql.org Content-Type: multipart/alternative; boundary="0000000000000feb0206165fe9d0" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000feb0206165fe9d0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 th= e > 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 singl= e > 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 comme= nt > 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 th= e > 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; 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; """ (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? > > 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 wro= te: > >> 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 tha= t >>> query can be a one line or multiline. I have assigned a play icon butto= n >>> 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 o= n >>> hover of the text, it will show the whole query as a tooltip. This quer= y >>> 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 okay >>> 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 the >> queries in your test; is it breaking on newlines? What if there's a newl= ine >> (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 definit= ion? >> >> -- >> Dave Page >> pgAdmin: https://www.pgadmin.org >> PostgreSQL: https://www.postgresql.org >> EDB: https://www.enterprisedb.com >> >> --=20 Dave Page pgAdmin: https://www.pgadmin.org PostgreSQL: https://www.postgresql.org EDB: https://www.enterprisedb.com --0000000000000feb0206165fe9d0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

On Thu, 18 Apr 2024 at 15:26, Anil Saho= o <anil.sahoo@enterprised= b.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 extr= acted the statements which have semicolon in it and also added some extra l= ogic to break the whole query on next of next line as empty or if comments = are there.

Using all this logic we got the individ= ual queries and checked where our cursor is in editor and checked=C2=A0with= the query=C2=A0and through this we got the actual query at cursor position= .

For example,=C2=A0
  1. if the curs= or is at starting or ending position or anywhere in between a query with se= micolon or without semicolon, that can be single line or multi line then th= e 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 positio= n where the previous line has a query then that query gets extracted.=C2=A0=
For the anonymous block containing multiple queries, c= ode 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 th= ere with Code Mirror.

Please let me know if you ha= ve any questions on this.

My ma= in concern is that it doesn't get it wrong. Ever. Consider:
<= br>
DELETE FROM foo; SELECT * FROM foo;

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

my_sql =3D 'DELETE FROM foo; SELEC= T * FROM foo;'

or=C2=A0

my_sql =3D """DELETE FROM foo;=C2=A0
SELECT * F= ROM foo;
"""

(those a= re just simple examples from the top of my head).=C2=A0

It could be extremely dangerous if we or CodeMirror mis-parses someth= ing, which seems quite possible unless it has access to the actual parser t= hat PostgreSQL uses. Which makes me think... what of EPAS? It has an extend= ed parser to handle some of the Oracle compatible syntax. Will CodeMirror g= et that right?
=C2=A0

Regards
Anil
--

=

= Anil Sahoo<= /span>

Software Engineer

www.enterpr= isedb.com

Power to Postgres

<= span style=3D"font-size:11pt;font-family:Roboto,sans-serif;color:rgb(0,0,0)= ;vertical-align:baseline;white-space:pre-wrap">=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.o= rg> wrote:
Hi
<= br>
On Wed,= 17 Apr 2024 at 15:08, Anil Sahoo <anil.sahoo@enterprisedb.com> wrote:
<= /div>
Hi Hackers,

This = feature is about executing a query=C2=A0at the=C2=A0cursor position. And th= at query can be a one=C2=A0line or multiline. I have assigned a play icon b= utton and F5 as the keyboard shortcut for the Execute Query feature, and fo= r Execute Script, Playlist icon button and Alt+F5(Others),Option+F5(Mac) as= keyboard shortcut.

As now the query can run at cu= rsor position, so for user convenience I am showing the current query just = beside the Data=C2=A0Output toolbar. And on hover of the text, it will show= the whole query as a tooltip. This query text will be available for both E= xecute Script and Execute Query.

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

P= lease provide your suggestions and feedback if these changes look okay to y= ou.

How is this parsing the que= ry to figure out the correct text to send to the server? For example, I not= ice 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 mu= ltiple queries, or a pl/whatever function definition that might contain que= ries within its text? Or a view definition?
=C2=A0
--


--
Dave Page
--0000000000000feb0206165fe9d0--