public inbox for [email protected]help / color / mirror / Atom feed
[pgAdmin][RM4988] Refactoring of all the SQL files used by pgAdmin4 4+ messages / 2 participants [nested] [flat]
* [pgAdmin][RM4988] Refactoring of all the SQL files used by pgAdmin4 @ 2019-12-16 12:09 Aditya Toshniwal <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Aditya Toshniwal @ 2019-12-16 12:09 UTC (permalink / raw) To: pgadmin-hackers Hi Hackers, Attached patch which is related to RM4988, reduces the number of hits to edit_mode_types.sql (an expensive one). Currently, this is query is executed for every column one by one when table properties dialog is opened. I've added edit_mode_types_bulk.sql, which will fetch the edit types for all the distinct column types used in the table in a single DB trip and use that data for each column. Also, while generating RE-SQL for columns, edit_mode_types.sql will not be executed. This will definitely reduce time. Kindly review. -- Thanks and Regards, Aditya Toshniwal Sr. Software Engineer | EnterpriseDB India | Pune "Don't Complain about Heat, Plant a TREE" Attachments: [application/octet-stream] RM4988.edit_types.patch (5.8K, 3-RM4988.edit_types.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/__init__.py index ee082810b..c0c40e417 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/__init__.py @@ -717,7 +717,10 @@ class ColumnsView(PGChildNodeView, DataTypeReader): column_utils.type_formatter(data['cltype']) # We will add table & schema as well - data = column_utils.column_formatter(self.conn, tid, clid, data) + # Passing edit_types_list param so that it does not fetch + # edit types. It is not required here. + data = column_utils.column_formatter(self.conn, tid, clid, + data, []) SQL, name = self.get_sql(scid, tid, None, data, is_sql=True) if not isinstance(SQL, (str, unicode)): diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/utils.py index bad938142..15a3143c6 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/utils.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/utils.py @@ -64,7 +64,8 @@ def get_parent(conn, tid, template_path=None): @get_template_path -def column_formatter(conn, tid, clid, data, template_path=None): +def column_formatter(conn, tid, clid, data, edit_types_list=None, + template_path=None): """ This function will return formatted output of query result as per client model format for column node @@ -144,25 +145,17 @@ def column_formatter(conn, tid, clid, data, template_path=None): # we will send filtered types related to current type type_id = data['atttypid'] - SQL = render_template("/".join([template_path, 'is_referenced.sql']), - tid=tid, clid=clid) - - status, is_reference = conn.execute_scalar(SQL) - - edit_types_list = list() - # We will need present type in edit mode - edit_types_list.append(data['cltype']) - - if int(is_reference) == 0: + if edit_types_list is None: + edit_types_list = [] SQL = render_template("/".join([template_path, 'edit_mode_types.sql']), type_id=type_id) status, rset = conn.execute_2darray(SQL) + edit_types_list = [row['typname'] for row in rset['rows']] - for row in rset['rows']: - edit_types_list.append(row['typname']) - - data['edit_types'] = edit_types_list + # We will need present type in edit mode + edit_types_list.append(data['cltype']) + data['edit_types_list'] = edit_types_list data['cltype'] = DataTypeReader.parse_type_name(data['cltype']) @@ -191,9 +184,10 @@ def get_formatted_columns(conn, tid, data, other_columns, raise Exception(res) all_columns = res['rows'] - + edit_types = {} # Add inherited from details from other columns - type, table for col in all_columns: + edit_types[col['atttypid']] = [] for other_col in other_columns: if col['name'] == other_col['name']: col['inheritedfrom' + table_or_type] = \ @@ -202,8 +196,17 @@ def get_formatted_columns(conn, tid, data, other_columns, data['columns'] = all_columns if 'columns' in data and len(data['columns']) > 0: + SQL = render_template("/".join([template_path, + 'edit_mode_types_bulk.sql']), + type_ids=",".join(map(lambda x: str(x), + edit_types.keys()))) + status, res = conn.execute_2darray(SQL) + for row in res['rows']: + edit_types[row['main_oid']] = row['edit_types'] + for column in data['columns']: - column_formatter(conn, tid, column['attnum'], column) + column_formatter(conn, tid, column['attnum'], column, + edit_types[col['atttypid']]) return data diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/columns/sql/default/edit_mode_types_bulk.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/columns/sql/default/edit_mode_types_bulk.sql new file mode 100644 index 000000000..2ff192156 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/columns/sql/default/edit_mode_types_bulk.sql @@ -0,0 +1,13 @@ +SELECT t.main_oid, ARRAY_AGG(t.typname) as edit_types +FROM +(SELECT pc.castsource as main_oid, format_type(tt.oid,NULL) AS typname +FROM pg_type tt + JOIN pg_cast pc ON tt.oid=pc.casttarget + WHERE pc.castsource in ({{type_ids}}) + AND pc.castcontext IN ('i', 'a') +UNION +SELECT tt.typbasetype as main_oid, format_type(tt.oid,NULL) AS typname +FROM pg_type tt +WHERE tt.typbasetype IN ({{type_ids}}) +) t +GROUP BY t.main_oid; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/columns/sql/default/is_referenced.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/columns/sql/default/is_referenced.sql deleted file mode 100644 index 52a4ffa86..000000000 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/columns/sql/default/is_referenced.sql +++ /dev/null @@ -1,6 +0,0 @@ -SELECT COUNT(1) -FROM pg_depend dep - JOIN pg_class cl ON dep.classid=cl.oid AND relname='pg_rewrite' - WHERE refobjid= {{tid}}::oid - AND classid='pg_class'::regclass - AND refobjsubid= {{clid|qtLiteral}}; ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgAdmin][RM4988] Refactoring of all the SQL files used by pgAdmin4 @ 2019-12-16 13:24 Akshay Joshi <[email protected]> parent: Aditya Toshniwal <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Akshay Joshi @ 2019-12-16 13:24 UTC (permalink / raw) To: Aditya Toshniwal <[email protected]>; +Cc: pgadmin-hackers Thanks, patch applied. On Mon, Dec 16, 2019 at 5:40 PM Aditya Toshniwal < [email protected]> wrote: > Hi Hackers, > > Attached patch which is related to RM4988, reduces the number of hits to edit_mode_types.sql > (an expensive one). > Currently, this is query is executed for every column one by one when > table properties dialog is opened. I've added edit_mode_types_bulk.sql, > which will fetch the edit types for all the distinct column types used in > the table in a single DB trip and use that data for each column. > Also, while generating RE-SQL for columns, edit_mode_types.sql will not > be executed. > This will definitely reduce time. > > Kindly review. > > -- > Thanks and Regards, > Aditya Toshniwal > Sr. Software Engineer | EnterpriseDB India | Pune > "Don't Complain about Heat, Plant a TREE" > -- *Thanks & Regards* *Akshay Joshi* *Sr. Software Architect* *EnterpriseDB Software India Private Limited* *Mobile: +91 976-788-8246* ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgAdmin][RM4988] Refactoring of all the SQL files used by pgAdmin4 @ 2019-12-18 07:42 Aditya Toshniwal <[email protected]> parent: Akshay Joshi <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Aditya Toshniwal @ 2019-12-18 07:42 UTC (permalink / raw) To: pgadmin-hackers; +Cc: Akshay Joshi <[email protected]> Hi Hackers, Had couple of typos in the previous patch. Although the edit types were passed to the front end, they were not filtered and all the types were displayed in edit table dialog. Kindly find the patch to fix it. On Mon, Dec 16, 2019 at 6:54 PM Akshay Joshi <[email protected]> wrote: > Thanks, patch applied. > > On Mon, Dec 16, 2019 at 5:40 PM Aditya Toshniwal < > [email protected]> wrote: > >> Hi Hackers, >> >> Attached patch which is related to RM4988, reduces the number of hits to edit_mode_types.sql >> (an expensive one). >> Currently, this is query is executed for every column one by one when >> table properties dialog is opened. I've added edit_mode_types_bulk.sql, >> which will fetch the edit types for all the distinct column types used in >> the table in a single DB trip and use that data for each column. >> Also, while generating RE-SQL for columns, edit_mode_types.sql will not >> be executed. >> This will definitely reduce time. >> >> Kindly review. >> >> -- >> Thanks and Regards, >> Aditya Toshniwal >> Sr. Software Engineer | EnterpriseDB India | Pune >> "Don't Complain about Heat, Plant a TREE" >> > > > -- > *Thanks & Regards* > *Akshay Joshi* > > *Sr. Software Architect* > *EnterpriseDB Software India Private Limited* > *Mobile: +91 976-788-8246* > -- Thanks and Regards, Aditya Toshniwal pgAdmin Hacker | Sr. Software Engineer | EnterpriseDB India | Pune "Don't Complain about Heat, Plant a TREE" Attachments: [application/octet-stream] RM4988.edit_types.typos.patch (1.0K, 3-RM4988.edit_types.typos.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/utils.py index 7524f482c..eede2d30e 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/utils.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/utils.py @@ -155,7 +155,7 @@ def column_formatter(conn, tid, clid, data, edit_types_list=None, # We will need present type in edit mode edit_types_list.append(data['cltype']) - data['edit_types_list'] = edit_types_list + data['edit_types'] = edit_types_list data['cltype'] = DataTypeReader.parse_type_name(data['cltype']) @@ -206,7 +206,7 @@ def get_formatted_columns(conn, tid, data, other_columns, for column in data['columns']: column_formatter(conn, tid, column['attnum'], column, - edit_types[col['atttypid']]) + edit_types[column['atttypid']]) return data ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgAdmin][RM4988] Refactoring of all the SQL files used by pgAdmin4 @ 2019-12-18 08:06 Akshay Joshi <[email protected]> parent: Aditya Toshniwal <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Akshay Joshi @ 2019-12-18 08:06 UTC (permalink / raw) To: Aditya Toshniwal <[email protected]>; +Cc: pgadmin-hackers Thanks, patch applied. On Wed, Dec 18, 2019 at 1:12 PM Aditya Toshniwal < [email protected]> wrote: > Hi Hackers, > > Had couple of typos in the previous patch. Although the edit types were > passed to the front end, they were not filtered and all the types were > displayed in edit table dialog. > Kindly find the patch to fix it. > > On Mon, Dec 16, 2019 at 6:54 PM Akshay Joshi < > [email protected]> wrote: > >> Thanks, patch applied. >> >> On Mon, Dec 16, 2019 at 5:40 PM Aditya Toshniwal < >> [email protected]> wrote: >> >>> Hi Hackers, >>> >>> Attached patch which is related to RM4988, reduces the number of hits to edit_mode_types.sql >>> (an expensive one). >>> Currently, this is query is executed for every column one by one when >>> table properties dialog is opened. I've added edit_mode_types_bulk.sql, >>> which will fetch the edit types for all the distinct column types used in >>> the table in a single DB trip and use that data for each column. >>> Also, while generating RE-SQL for columns, edit_mode_types.sql will not >>> be executed. >>> This will definitely reduce time. >>> >>> Kindly review. >>> >>> -- >>> Thanks and Regards, >>> Aditya Toshniwal >>> Sr. Software Engineer | EnterpriseDB India | Pune >>> "Don't Complain about Heat, Plant a TREE" >>> >> >> >> -- >> *Thanks & Regards* >> *Akshay Joshi* >> >> *Sr. Software Architect* >> *EnterpriseDB Software India Private Limited* >> *Mobile: +91 976-788-8246* >> > > > -- > Thanks and Regards, > Aditya Toshniwal > pgAdmin Hacker | Sr. Software Engineer | EnterpriseDB India | Pune > "Don't Complain about Heat, Plant a TREE" > -- *Thanks & Regards* *Akshay Joshi* *Sr. Software Architect* *EnterpriseDB Software India Private Limited* *Mobile: +91 976-788-8246* ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-12-18 08:06 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-12-16 12:09 [pgAdmin][RM4988] Refactoring of all the SQL files used by pgAdmin4 Aditya Toshniwal <[email protected]> 2019-12-16 13:24 ` Akshay Joshi <[email protected]> 2019-12-18 07:42 ` Aditya Toshniwal <[email protected]> 2019-12-18 08:06 ` Akshay Joshi <[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