This is a description of the project completed by me, Yosry Ahmed, during GSoC 2019 for PostgreSQL under the mentorship of Dave Page.
The official project page can be found here: https://summerofcode.withgoogle.com/archive/2019/projects/4883641988022272/
pgAdmin 4 is the most popular and feature rich open source administration and development platform for PostgreSQL, the most advanced open source database management system.
In pgAdmin 4, the Query Tool currently works in 2 different modes:
- View/Edit Data mode allows modifications to the SQL query only through the UI, but allows editing of data when used with a table with an appropriate primary key.
- Query Tool mode allows arbitrary SQL queries to be written and executed, but the user cannot edit the results.
The core aim of the project is automatically detecting whether executed SELECT queries in the Query Tool produce updatable result-sets and enable editing of the data directly without switching to View/Edit Data mode. This allows for performing modifications on more complex result-sets. In addition, result-sets that are partially editable (in terms of columns) can also be edited, with other columns being locked as read-only.
Furthermore, other tweaks/enhancements are made to the Query Tool, such as:
- Enhancing the Query History panel to optionally include queries generated by pgAdmin internally (e.g. in saving data changes made through the datagrid). In addition to showing the source of each query in the query history.
- Keeping track of whether the current transaction is committed to prompt the user when exiting with an uncommitted transaction.
- Integrating the edits done through the datagrid with any ongoing active transaction.
pgAdmin 4 is implemented as a local web application. Flask (Python) is used in the backend while JQuery, Backbone.js and other JavaScript libraries are used in the frontend.
pgAdmin 4 maintains a regression testing suite. Consequently, all my work was accompanied with Javascript tests - Karma, Python/API tests, and feature tests (end-to-end tests in the browser using Selenium Webdriver).
Some of my work aslo included fixing bugs that are not directly related to the project.
Throughout the project, the workflow involved creating code patches and sending them to the pgAdmin mailing list to be reviewed (and commited if appropriate).
All the commits authored by me can be found here: https://git.postgresql.org/gitweb/?p=pgadmin4.git;a=search;s=Yosry+Muhammad;st=author
Or on the GitHub mirror of pgAdmin 4: https://github.com/postgres/pgadmin4/commits?author=yosrym93
Here is a list of the patches I created throughout the project. All of the patches are committed to the master branch. As of pgAdmin 4.13, all my work is already released to the users. All patches included adequate updates/additions to regression tests and user documentation.
Some patches include new features added to the Query Tool while others include bug fixes or refactoring.
Links are provided for the commits in the GitHub mirror repositry.
- Add support for editing of resultsets in the Query Tool, if the data is identified as editable.
This is the core patch of the project. When a query is run in the Query Tool, check if the source of the columns can be identified as being from a single table, and that we have all columns that make up the primary key. If so, consider the resultset to be editable and allow the user to edit data and add/remove rows in the datagrid. Changes to data are saved using SAVEPOINTs as part of any transaction that's in progress, and rolled back if there are integrity violations, without otherwise affecting the ongoing transaction. This patch also included a few tweaks to the Query Tool including:
- Appropriate notifications to let the user know when their changes need to be commited.
- Keeping track of whether the current transaction is committed and prompt the user to commit or rollback if exiting with an uncommitted transaction.
- Highlighting modified and new cells in the data grid.
- Separate the Save File and Save Data Changes functionalities into 2 separate buttons (previously a single button).
https://github.com/postgres/pgadmin4/commit/710d520631b1b4649eb4a8d924e3141ff74af449
- Randomise table names for tests.
This patch includes randomising table names in python tests so that they can run in parallel.
https://github.com/postgres/pgadmin4/commit/f5b927b9254beafd1e99027a1d22fe532615af57
- Added support for editing of result sets from tables with OIDs in query tool.
This patch added support to edit result-sets from tables with OIDs (only tables with primary keys were supported).
https://github.com/postgres/pgadmin4/commit/82d209946f6967a45238364659fe2d20313fcaae
- Ensure the query tool will work with older versions of psycopg2 than we officially support, albeit without updateable resultsets.
Fixes a bug that occurred as a regression of the core patch with users having older versions of psycopg2 than officially supported.
https://github.com/postgres/pgadmin4/commit/7b65507533ac1e505786d04378f709451fbddfdf
- Ensure command tags are shown in the messages tab of the Query Tool.
Fixes a bug where the command tags and number of affected rows were not shown in the messages tab of the results. This bug was not related to the project.
https://github.com/postgres/pgadmin4/commit/76e658ee129a49afc74152b57330cf2c2e653e2e
- Ensure the Query Tool doesn't throw an error when viewing the contents of a table with no columns.
Fixes a bug that occurred when viewing the contents of a table with no columns. This bug was not related to the project.
https://github.com/postgres/pgadmin4/commit/97e39699ead1005a4a5ef8e986d980df8f743db9
- Fix load on demand in View/Edit data mode.
Fixes a bug with fetching more rows while scrolling the results in View/Edit Data mode. This bug was a regression of the core patch.
https://github.com/postgres/pgadmin4/commit/687204771ce936697508997fb7988ad8551b9b81
- Add support in query history to show internal queries generated by pgAdmin during save data operations.
This patch introduces new features to the Query History tab. It allows queries that are generated internally by pgAdmin (e.g. during saving data changes) to optionally be shown in the history panel. It also shows the source of each query in the history panel (Execute, Explain, Save Data, etc.).
https://github.com/postgres/pgadmin4/commit/687204771ce936697508997fb7988ad8551b9b81
- 1) Added new python test that checks the transaction status after executing queries and saving data when auto-commit is on/off. 2) Some refactoring of previously written python tests.
This patch added a new python test and refactored previously written ones.
https://github.com/postgres/pgadmin4/commit/663e8382f3971487fc2b006392af285c274b04a1
- Some refactoring of sqleditor.js.
This patch included refactoring of some JavaScript code.
https://github.com/postgres/pgadmin4/commit/5887fb38153e94307dfc8be47085353b9b9acf81
- Add support for editable and read-only columns in Query Tool, they are also identified by icons and tooltips in the column header.
This patch adds support for updatable result-sets that include read-only columns. This allows for a wider variety of updatable result-sets to be supported. For example, results including renamed columns or columns that are computed based on other columns.
https://github.com/postgres/pgadmin4/commit/f8f7d5ac6fd1625ec8d27300b9a1c5319fc22446
I would like to thank my mentor, Dave Page, for providing guidance, positive criticism and encouragement throughout the project. I would also like to thank a few developers working on pgAdmin 4 who helped me throughtout the project: Aditya Toshniwal, Akshay Joshi, and Khushboo Vashi.