This is a patch fixing a problem with the above patch that happened when:
- primary key columns are renamed.
- other columns are renamed to be like primary key columns.
This problem happened mainly because the primary keys are identified in the
front-end by their names. This can be handled in a better way in a future
update where columns that are primary keys are identified by the backend
and sent to the frontend instead.
Also, renamed columns can be handled better by making them read-only in a
future update (now they are editable but they cannot be updated as a column
with the new name does not exist - it produces an error message to the
user).
Waiting for your feedback. Thanks !
On Sat, Jun 15, 2019 at 8:48 AM Yosry Muhammad <[email protected]> wrote:
> Dear all,
>
> This is my first patch of my GSoC project, query tool automatic mode
> detection.
>
> In this patch, the initial (basic) version of the project is implemented.
> In this version, query resultsets are updatable if and only if:
> - All the columns belong to a single table
> - No duplicate columns are available
> - All the primary keys of the table are available
>
> Inserts, updates and deletes work automatically when the resultset is
> updatable.
>
> The 'save' button in the query tool works automatically to save the
> changes in the resultset if the query is the updatable, and saves the query
> to a file otherwise. The 'save as' button stays as is.
>
> I will work on improving and adding features to this version throughout my
> work during the summer according to what has the highest priorities
> (supporting duplicate columns or columns produced by functions or
> aggregations as read-only columns in the results seems like a good next
> move).
>
> Please give me your feedback of the changes I made, and any hints or
> comments that will improve my code in any aspect.
>
> I also have a couple of questions,
> - Should the save button in the query tool work the way I am using it now?
> or should there be a new dedicated button for saving the query to a file?
>
> - What documentations or unit tests should I write? any guidelines here
> would be appreciated.
>
> Thanks a lot!
>
>
> --
> *Yosry Muhammad Yosry*
>
> Computer Engineering student,
> The Faculty of Engineering,
> Cairo University (2021).
> Class representative of CMP 2021.
> https://www.linkedin.com/in/yosrym93/
>
--
*Yosry Muhammad Yosry*
Computer Engineering student,
The Faculty of Engineering,
Cairo University (2021).
Class representative of CMP 2021.
https://www.linkedin.com/in/yosrym93/
diff --git a/web/pgadmin/tools/sqleditor/utils/is_query_resultset_updatable.py b/web/pgadmin/tools/sqleditor/utils/is_query_resultset_updatable.py
index ed60f1e9..2ff18d83 100644
--- a/web/pgadmin/tools/sqleditor/utils/is_query_resultset_updatable.py
+++ b/web/pgadmin/tools/sqleditor/utils/is_query_resultset_updatable.py
@@ -37,20 +37,25 @@ def is_query_resultset_updatable(conn, sql_path):
# First check that all the columns belong to a single table
table_oid = columns_info[0]['table_oid']
- column_numbers = []
+ columns = []
for column in columns_info:
if column['table_oid'] != table_oid:
return False, None, None, None
else:
- column_numbers.append(column['table_column'])
+ columns.append({
+ 'display_name': column['display_name'],
+ 'column_number': column['table_column']
+ })
# Check for duplicate columns
+ column_numbers = [col['column_number'] for col in columns]
is_duplicate_columns = len(column_numbers) != len(set(column_numbers))
if is_duplicate_columns:
return False, None, None, None
if conn.connected():
# Then check that all the primary keys of the table are present
+ # and no primary keys are renamed (or other columns renamed to be like primary keys)
query = render_template(
"/".join([sql_path, 'primary_keys.sql']),
obj_id=table_oid
@@ -59,21 +64,36 @@ def is_query_resultset_updatable(conn, sql_path):
if not status:
return False, None, None, None
- primary_keys_column_numbers = []
+ primary_keys_columns = []
primary_keys = OrderedDict()
pk_names = []
for row in result['rows']:
primary_keys[row['attname']] = row['typname']
- primary_keys_column_numbers.append(row['attnum'])
+ primary_keys_columns.append({
+ 'name': row['attname'],
+ 'column_number': row['attnum']
+ })
pk_names.append(row['attname'])
- all_primary_keys_exist = all(elem in column_numbers
- for elem in primary_keys_column_numbers)
- else:
- return False, None, None, None
+ # Check that all primary keys exist and that all of them are not renamed
+ # and other columns are not renamed to primary key names
+ for pk in primary_keys_columns:
+ pk_exists = False
+ for col in columns:
+ if col['column_number'] == pk['column_number']:
+ pk_exists = True
+ if col['display_name'] != pk['name']: # If the primary key column is renamed
+ return False, None, None, None
+ # If the column is not the primary key but it is renamed to its name
+ elif col['display_name'] == pk['name']:
+ return False, None, None, None
+
+ if not pk_exists:
+ return False, None, None, None
- if all_primary_keys_exist:
+ # If the for loop exited without returning from the function then
+ # all primary keys exist without being renamed
return True, primary_keys, pk_names, table_oid
else:
return False, None, None, None
