MIME-Version: 1.0
From: Yosry Muhammad <yosrym93@gmail.com>
Date: Mon, 10 Jun 2019 23:51:04 +0200
Message-ID: 
 <CAFSMqn8uUoAM9Cc=ZngWMrAFg0m7K2fVuJMhQNYym0DkFha7sA@mail.gmail.com>
Subject: [GSoC] Check if a query resultset is update-able
To: pgadmin-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000f1a57b058aff2d8f"
Precedence: bulk

--000000000000f1a57b058aff2d8f
Content-Type: text/plain; charset="UTF-8"

Dear all,

After some research and review of the code, I have arrived at this method
to check whether a query resultset is update-able. I would like to know
your feedback before I start implementing this technique:

- Query results are not be update-able (initially at least) except if all
the primary keys are selected and all the columns belong to the same table
(no joins or aggregations).
- When the query results is ready (polling is successful) I can check the
results in the back-end using the transaction connection cursor.
- The transaction cursor description attribute includes a list of Column
objects, each of which has attributes pointing to its original table in the
system catalog table *pg_class* (if available) and its attribute number
within the table. [1]
- From this information, the system catalog tables *pg_class, pg_attribute *and
*pg_constraint *can be queried to check that all the columns belong to a
single table and that all the primary keys are available. [2][3][4]
- This can be used as an indicator to whether the resultset is updatable
(similar to the View Table mode, where tables are only editable if they
have primary keys).

I will modify the following parts in the code:
1- *web/tools/sqleditor/command.py*
QueryToolCommand class will be modified to contain an attribute indicating
whether the query results are update-able for the last successful query.

2- A new file will be added in* web/tools/sqleditor/utils/* containing the
function that will check if the query results are update-able.

3-
*web/tools/sqleditor/__init__.py *
The poll endpoint will be modified to check if the results are update-able
(in case the results are ready), then the session object primary keys and
the transaction object can_edit attribute will be updated (the primary keys
are checked in the frontend, if they exist table modifications are allowed).

This is the first step, to check if a query resultset is update-able. The
upcoming steps will include switching the mode in the frontend to allow for
editing the results and checking what options should be enabled or disabled
and any needed modifications (I think allowing for only editing and
deleting rows makes sense).

Sorry for the long email, looking forward to your feedback!

[1] http://initd.org/psycopg/docs/extensions.html#psycopg2.extensions.Column
[2] https://www.postgresql.org/docs/current/catalog-pg-class.html
[3] https://www.postgresql.org/docs/current/catalog-pg-attribute.html
[4] https://www.postgresql.org/docs/current/catalog-pg-constraint.html

-- 
*Yosry Muhammad Yosry*

Computer Engineering student,
The Faculty of Engineering,
Cairo University (2021).
Class representative of CMP 2021.
https://www.linkedin.com/in/yosrym93/

--000000000000f1a57b058aff2d8f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Dear all,</div><div><br></div><div>After some researc=
h and review of the code, I have arrived at this method to check whether a =
query resultset is update-able. I would like to know your feedback before I=
 start implementing this technique:</div><div><br></div><div>- Query result=
s are not be update-able (initially at least) except if all the primary key=
s are selected and all the columns belong to the same table (no joins or ag=
gregations).</div><div>- When the query results is ready (polling is succes=
sful) I can check the results in the back-end using the transaction connect=
ion cursor.</div><div>- The transaction cursor description attribute includ=
es a list of Column objects, each of which has attributes pointing to its o=
riginal table in the system catalog table <b>pg_class</b> (if available) an=
d its attribute number within the table. [1]</div><div>- From this informat=
ion, the system catalog tables <b>pg_class, pg_attribute </b>and <b>pg_cons=
traint </b>can be queried to check that all the columns belong to a single =
table and that all the primary keys are available. [2][3][4]<br></div><div>=
- This can be used as an indicator to whether the resultset is updatable (s=
imilar to the View Table mode, where tables are only editable if they have =
primary keys).<br></div><div><br></div><div>I will modify the following par=
ts in the code:</div><div>1- <b>web/tools/sqleditor/command.py</b><br></div=
><div>QueryToolCommand class will be modified to contain an attribute indic=
ating whether the query results are update-able for the last successful que=
ry.</div><div><br></div><div>2- A new file will be added in<b> web/tools/sq=
leditor/utils/</b> containing the function that will check if the query res=
ults are update-able.</div><div><br></div><div>3- <b>web/tools/sqleditor/__=
init__.py <br></b></div><div>The poll endpoint will be modified to check if=
 the results are update-able (in case the results are ready), then the sess=
ion object primary keys and the transaction object can_edit attribute will =
be updated (the primary keys are checked in the frontend, if they exist tab=
le modifications are allowed).</div><div><br></div><div>This is the first s=
tep, to check if a query resultset is update-able. The upcoming steps will =
include switching the mode in the frontend to allow for editing the results=
 and checking what options should be enabled or disabled and any needed mod=
ifications (I think allowing for only editing and deleting rows makes sense=
).</div><div><br></div><div>Sorry for the long email, looking forward to yo=
ur feedback!<br></div><div><br></div><div>[1] <a href=3D"http://initd.org/p=
sycopg/docs/extensions.html#psycopg2.extensions.Column">http://initd.org/ps=
ycopg/docs/extensions.html#psycopg2.extensions.Column</a><br></div><div>[2]=
 <a href=3D"https://www.postgresql.org/docs/current/catalog-pg-class.html">=
https://www.postgresql.org/docs/current/catalog-pg-class.html</a></div><div=
>[3] <a href=3D"https://www.postgresql.org/docs/current/catalog-pg-attribut=
e.html">https://www.postgresql.org/docs/current/catalog-pg-attribute.html</=
a></div><div>[4] <a href=3D"https://www.postgresql.org/docs/current/catalog=
-pg-constraint.html">https://www.postgresql.org/docs/current/catalog-pg-con=
straint.html</a></div><div><br></div>-- <br><div dir=3D"ltr" class=3D"gmail=
_signature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr"><div style=
=3D"text-align:left"><i><b>Yosry Muhammad Yosry</b></i></div><div style=3D"=
text-align:left"><i><b><br></b></i></div><div style=3D"text-align:left">Com=
puter Engineering student,</div><div style=3D"text-align:left"> The Faculty=
 of Engineering,</div><div style=3D"text-align:left">Cairo University (2021=
).</div><div style=3D"text-align:left">Class representative of CMP 2021.<br=
></div><div style=3D"text-align:left"><a href=3D"https://www.linkedin.com/i=
n/yosrym93/" target=3D"_blank">https://www.linkedin.com/in/yosrym93/</a><br=
></div></div></div></div>

--000000000000f1a57b058aff2d8f--