[GSoC] Check if a query resultset is update-able - Mailing list pgadmin-hackers

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