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 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
