public inbox for [email protected]help / color / mirror / Atom feed
[GSoC] Check if a query resultset is update-able 4+ messages / 2 participants [nested] [flat]
* [GSoC] Check if a query resultset is update-able @ 2019-06-10 21:51 Yosry Muhammad <[email protected]> 2019-06-11 08:21 ` Re: [GSoC] Check if a query resultset is update-able Dave Page <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Yosry Muhammad @ 2019-06-10 21:51 UTC (permalink / raw) To: 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 -- *Yosry Muhammad Yosry* Computer Engineering student, The Faculty of Engineering, Cairo University (2021). Class representative of CMP 2021. https://www.linkedin.com/in/yosrym93/ ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [GSoC] Check if a query resultset is update-able 2019-06-10 21:51 [GSoC] Check if a query resultset is update-able Yosry Muhammad <[email protected]> @ 2019-06-11 08:21 ` Dave Page <[email protected]> 2019-06-11 14:05 ` Re: [GSoC] Check if a query resultset is update-able Yosry Muhammad <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Dave Page @ 2019-06-11 08:21 UTC (permalink / raw) To: Yosry Muhammad <[email protected]>; +Cc: pgadmin-hackers Hi On Mon, Jun 10, 2019 at 10:51 PM Yosry Muhammad <[email protected]> wrote: > 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). > What are all the conditions that prevent a query being updateable? Joins, aggregates, lack of pkey, computed columns, function calls....? > - 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] > Hmm, at first glance it seems to me that psycopg2.extensions.Column might make this much easier than previously expected: - Is Column.table_oid the same for all columns? - Is Column.table_column present for all columns? then: - Do we have all the primary key columns in the resultset? In fact, we could even support queries such as: SELECT col1 || col2, col1, col2 FROM table; because (assuming col1 or col2 is the pkey) we can just make the first column read-only in the grid, and only allow editing of the second and third. That requires that the table_oid matches of course. > - 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). > You mean the endpoint that is polled for the results, or the transaction status poll endpoint? I'm assuming the former. > > 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). > I don't see any obvious reason why we couldn't add rows as well. Of course, they may fail if a not null column isn't present in the query, but the user can fix that (we should probably not just make the grid read-only if we detect that, unless we can come up with a nice way to tell the user why they can't edit anything). > > 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/ > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [GSoC] Check if a query resultset is update-able 2019-06-10 21:51 [GSoC] Check if a query resultset is update-able Yosry Muhammad <[email protected]> 2019-06-11 08:21 ` Re: [GSoC] Check if a query resultset is update-able Dave Page <[email protected]> @ 2019-06-11 14:05 ` Yosry Muhammad <[email protected]> 2019-06-11 15:47 ` Re: [GSoC] Check if a query resultset is update-able Dave Page <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Yosry Muhammad @ 2019-06-11 14:05 UTC (permalink / raw) To: Dave Page <[email protected]>; pgadmin-hackers Hi, On Tue, Jun 11, 2019 at 10:21 AM Dave Page <[email protected]> wrote: > Hi > > On Mon, Jun 10, 2019 at 10:51 PM Yosry Muhammad <[email protected]> > wrote: > >> 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). >> > > What are all the conditions that prevent a query being updateable? Joins, > aggregates, lack of pkey, computed columns, function calls....? > > >> - 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] >> > > Hmm, at first glance it seems to me that psycopg2.extensions.Column might > make this much easier than previously expected: > > - Is Column.table_oid the same for all columns? > - Is Column.table_column present for all columns? > > then: > > - Do we have all the primary key columns in the resultset? > > In fact, we could even support queries such as: > > SELECT col1 || col2, col1, col2 FROM table; > > because (assuming col1 or col2 is the pkey) we can just make the first > column read-only in the grid, and only allow editing of the second and > third. That requires that the table_oid matches of course. > According to my understanding, Column.table_oid is the same for all columns belonging to the same table and is None if the column does not directly reference an original column in a table (an aggregation or a result of a mathematical operation or concatenation for example). It follows that Column.table_column is only present for the columns that have a table_oid attribute that isn't None. For the first version, the query resultset can be update-able if (and only if): - All the columns either belong to the same table or no tables (such as the concatenation case you pointed out). - All the primary keys of that column are available. columns that do not belong to the table can be made read-only. Please keep in mind that including columns that do not belong directly to the table will require more modifications in the front-end part when modifying the table (to drop any read-only columns and so). > > >> - 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). >> > > You mean the endpoint that is polled for the results, or the transaction > status poll endpoint? I'm assuming the former. > I did mean the endpoint that is polled for the results indeed. > > >> >> 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). >> > > I don't see any obvious reason why we couldn't add rows as well. Of > course, they may fail if a not null column isn't present in the query, but > the user can fix that (we should probably not just make the grid read-only > if we detect that, unless we can come up with a nice way to tell the user > why they can't edit anything). > Inserting the rows can be supported I didn't look into this part yet, was just letting you know of the upcoming steps in my point of view. For now I will work on the aforementioned implementation. It can be extended later according to what you see suitable, for example allow editing result-sets containing columns from different tables). Looking forward for your feedback to start working on that at once. > > >> >> 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/ >> > > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- *Yosry Muhammad Yosry* Computer Engineering student, The Faculty of Engineering, Cairo University (2021). Class representative of CMP 2021. https://www.linkedin.com/in/yosrym93/ ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [GSoC] Check if a query resultset is update-able 2019-06-10 21:51 [GSoC] Check if a query resultset is update-able Yosry Muhammad <[email protected]> 2019-06-11 08:21 ` Re: [GSoC] Check if a query resultset is update-able Dave Page <[email protected]> 2019-06-11 14:05 ` Re: [GSoC] Check if a query resultset is update-able Yosry Muhammad <[email protected]> @ 2019-06-11 15:47 ` Dave Page <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Dave Page @ 2019-06-11 15:47 UTC (permalink / raw) To: Yosry Muhammad <[email protected]>; +Cc: pgadmin-hackers Hi On Tue, Jun 11, 2019 at 3:06 PM Yosry Muhammad <[email protected]> wrote: > Hi, > > On Tue, Jun 11, 2019 at 10:21 AM Dave Page <[email protected]> wrote: > >> Hi >> >> On Mon, Jun 10, 2019 at 10:51 PM Yosry Muhammad <[email protected]> >> wrote: >> >>> 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). >>> >> >> What are all the conditions that prevent a query being updateable? Joins, >> aggregates, lack of pkey, computed columns, function calls....? >> >> >>> - 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] >>> >> >> Hmm, at first glance it seems to me that psycopg2.extensions.Column might >> make this much easier than previously expected: >> >> - Is Column.table_oid the same for all columns? >> - Is Column.table_column present for all columns? >> >> then: >> >> - Do we have all the primary key columns in the resultset? >> >> In fact, we could even support queries such as: >> >> SELECT col1 || col2, col1, col2 FROM table; >> >> because (assuming col1 or col2 is the pkey) we can just make the first >> column read-only in the grid, and only allow editing of the second and >> third. That requires that the table_oid matches of course. >> > > According to my understanding, Column.table_oid is the same for all > columns belonging to the same table and is None if the column does not > directly reference an original column in a table (an aggregation or a > result of a mathematical operation or concatenation for example). It > follows that Column.table_column is only present for the columns that have > a table_oid attribute that isn't None. > > For the first version, the query resultset can be update-able if (and only > if): > - All the columns either belong to the same table or no tables (such as > the concatenation case you pointed out). > - All the primary keys of that column are available. > I assume you mean table, and not column. > columns that do not belong to the table can be made read-only. > > Please keep in mind that including columns that do not belong directly to > the table will require more modifications in the front-end part when > modifying the table (to drop any read-only columns and so). > I'd start by handling the "only columns from the table" case. Handling individual read-only columns can be handled if/when there is time. > > > > >> >> >>> - 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). >>> >> >> You mean the endpoint that is polled for the results, or the transaction >> status poll endpoint? I'm assuming the former. >> > > I did mean the endpoint that is polled for the results indeed. > :-) > > >> >> >>> >>> 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). >>> >> >> I don't see any obvious reason why we couldn't add rows as well. Of >> course, they may fail if a not null column isn't present in the query, but >> the user can fix that (we should probably not just make the grid read-only >> if we detect that, unless we can come up with a nice way to tell the user >> why they can't edit anything). >> > > Inserting the rows can be supported I didn't look into this part yet, was > just letting you know of the upcoming steps in my point of view. For now I > will work on the aforementioned implementation. It can be extended later > according to what you see suitable, for example allow editing result-sets > containing columns from different tables). > > Looking forward for your feedback to start working on that at once. > I would suggest working on IUD for resultsets that are entirely from a single table. I think that insert is as important as update and delete, and less important that supporting cases like the example I gave. Otherwise, sounds good to me! > > > >> >> >>> >>> 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/ >>> >> >> >> -- >> Dave Page >> Blog: http://pgsnake.blogspot.com >> Twitter: @pgsnake >> >> EnterpriseDB UK: http://www.enterprisedb.com >> The Enterprise PostgreSQL Company >> > > > -- > *Yosry Muhammad Yosry* > > Computer Engineering student, > The Faculty of Engineering, > Cairo University (2021). > Class representative of CMP 2021. > https://www.linkedin.com/in/yosrym93/ > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-06-11 15:47 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-06-10 21:51 [GSoC] Check if a query resultset is update-able Yosry Muhammad <[email protected]> 2019-06-11 08:21 ` Dave Page <[email protected]> 2019-06-11 14:05 ` Yosry Muhammad <[email protected]> 2019-06-11 15:47 ` Dave Page <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox