Hi, Here is patch for initial implementation of on demand loading of result set for query tool and datagrid.
-- *Harshal Dhumal* *Sr. Software Engineer* EnterpriseDB India: http://www.enterprisedb.com The Enterprise PostgreSQL Company On Tue, Apr 25, 2017 at 5:21 PM, Dave Page <dp...@pgadmin.org> wrote: > Hi > > On Tue, Apr 25, 2017 at 8:41 AM, Harshal Dhumal < > harshal.dhu...@enterprisedb.com> wrote: > >> Hi Dave, >> >> To implement feature #2137 <https://redmine.postgresql.org/issues/2137> we'll >> need to use server cursor. However server cursor has some >> limitation. >> For eg. >> 1. It cannot execute BEGIN; query (basically user cannot start new >> database transaction) >> 2. In case if Auto commit is true then we try to execute user queries >> inside BEGIN and END when ever it's possible even though user has not put >> BEGIN and END in his query. >> >> Also not all queries executed using Query tool produces records as >> result. So can we assume only >> queries started with SELECT should be executed using server cursor to >> support on demand loading. >> Or should we give user an option to use on demand loading like we have >> options for Auto commit? and Auto rollback? >> In case of on demand loading option user will be responsible to execute >> correct queries (queries which can be executed using server cursor) >> >> Let me know your opinion on this. >> > > Hmm, those are good points. > > So, as a first step, there's no absolute requirement to use a server side > cursor here. The results can be materialised in libpq/psycopg2 (perhaps > using an async query), then transferred to the client in batches as > described in the ticket. > > I think this would be a significant improvemet - we can re-visit the > possibility of using server side cursors in the future when we have more > ability to parse the query string before executing it (something we will > want to do when we merge query tool/edit grid functionality). > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
diff --git a/web/config.py b/web/config.py index 5ff9748..f89dc89 100644 --- a/web/config.py +++ b/web/config.py @@ -324,6 +324,12 @@ THREADED_MODE = True SQLALCHEMY_TRACK_MODIFICATIONS = False ########################################################################## +# Number of records to fetch in one batch in query tool when query result +# set is large. +########################################################################## +ON_DEMAND_RECORD_COUNT = 1000 + +########################################################################## # Local config settings ########################################################################## diff --git a/web/pgadmin/static/js/selection/copy_data.js b/web/pgadmin/static/js/selection/copy_data.js index 018efea..c549563 100644 --- a/web/pgadmin/static/js/selection/copy_data.js +++ b/web/pgadmin/static/js/selection/copy_data.js @@ -11,13 +11,13 @@ define([ var grid = self.slickgrid; var columnDefinitions = grid.getColumns(); var selectedRanges = grid.getSelectionModel().getSelectedRanges(); - var data = grid.getData(); + var dataView = grid.getData(); var rows = grid.getSelectedRows(); if (allTheRangesAreFullRows(selectedRanges, columnDefinitions)) { self.copied_rows = rows.map(function (rowIndex) { - return data[rowIndex]; + return grid.getDataItem(rowIndex); }); setPasteRowButtonEnablement(self.can_edit, true); } else { @@ -25,7 +25,7 @@ define([ setPasteRowButtonEnablement(self.can_edit, false); } - var csvText = rangeBoundaryNavigator.rangesToCsv(data, columnDefinitions, selectedRanges); + var csvText = rangeBoundaryNavigator.rangesToCsv(dataView.getItems(), columnDefinitions, selectedRanges); if (csvText) { clipboard.copyTextToClipboard(csvText); } diff --git a/web/pgadmin/static/js/selection/row_selector.js b/web/pgadmin/static/js/selection/row_selector.js index 76a8c1a..b166edc 100644 --- a/web/pgadmin/static/js/selection/row_selector.js +++ b/web/pgadmin/static/js/selection/row_selector.js @@ -15,9 +15,11 @@ define(['jquery', 'sources/selection/range_selection_helper', 'slickgrid'], func if (grid.getColumns()[args.cell].id === 'row-header-column') { if (event.target.type != "checkbox") { var checkbox = $(event.target).find('input[type="checkbox"]'); - toggleCheckbox($(checkbox)); + if (checkbox.length > 0) { + toggleCheckbox($(checkbox)); + updateRanges(grid, args.row); + } } - updateRanges(grid, args.row); } } diff --git a/web/pgadmin/static/js/slickgrid/slick.pgadmin.editors.js b/web/pgadmin/static/js/slickgrid/slick.pgadmin.editors.js index cdfba4d..dd10fc1 100644 --- a/web/pgadmin/static/js/slickgrid/slick.pgadmin.editors.js +++ b/web/pgadmin/static/js/slickgrid/slick.pgadmin.editors.js @@ -110,11 +110,11 @@ // When text editor opens this.loadValue = function (item) { - if (item[args.column.pos] === "") { + if (item[args.column.field] === "") { $input.val("''"); } else { - $input.val(defaultValue = item[args.column.pos]); + $input.val(defaultValue = item[args.column.field]); $input.select(); } }; @@ -141,7 +141,7 @@ }; this.applyValue = function (item, state) { - item[args.column.pos] = state; + item[args.column.field] = state; }; this.isValueChanged = function () { @@ -252,7 +252,7 @@ }; this.loadValue = function (item) { - var data = defaultValue = item[args.column.pos]; + var data = defaultValue = item[args.column.field]; if (typeof data === "object" && !Array.isArray(data)) { data = JSON.stringify(data); } else if (Array.isArray(data)) { @@ -278,7 +278,7 @@ }; this.applyValue = function (item, state) { - item[args.column.pos] = state; + item[args.column.field] = state; }; this.isValueChanged = function () { @@ -385,7 +385,7 @@ }; this.loadValue = function (item) { - $input.val(defaultValue = item[args.column.pos]); + $input.val(defaultValue = item[args.column.field]); $input.select(); }; @@ -394,7 +394,7 @@ }; this.applyValue = function (item, state) { - item[args.column.pos] = state; + item[args.column.field] = state; }; this.isValueChanged = function () { @@ -468,12 +468,12 @@ }; this.loadValue = function (item) { - defaultValue = item[args.column.pos]; + defaultValue = item[args.column.field]; if (_.isNull(defaultValue)||_.isUndefined(defaultValue)) { $select.prop('indeterminate', true); } else { - defaultValue = !!item[args.column.pos]; + defaultValue = !!item[args.column.field]; if (defaultValue) { $select.prop('checked', true); } else { @@ -490,7 +490,7 @@ }; this.applyValue = function (item, state) { - item[args.column.pos] = state; + item[args.column.field] = state; }; this.isValueChanged = function () { @@ -590,7 +590,7 @@ }; this.loadValue = function (item) { - var data = defaultValue = item[args.column.pos]; + var data = defaultValue = item[args.column.field]; if (typeof data === "object" && !Array.isArray(data)) { data = JSON.stringify(data); } else if (Array.isArray(data)) { @@ -613,7 +613,7 @@ }; this.applyValue = function (item, state) { - item[args.column.pos] = state; + item[args.column.field] = state; }; this.isValueChanged = function () { @@ -667,7 +667,7 @@ }; this.loadValue = function (item) { - var value = item[args.column.pos]; + var value = item[args.column.field]; // Check if value is null or undefined if (value === undefined && typeof value === "undefined") { @@ -819,7 +819,7 @@ }; this.loadValue = function (item) { - defaultValue = item[args.column.pos]; + defaultValue = item[args.column.field]; $input.val(defaultValue); $input[0].defaultValue = defaultValue; $input.select(); @@ -833,7 +833,7 @@ }; this.applyValue = function (item, state) { - item[args.column.pos] = state; + item[args.column.field] = state; }; this.isValueChanged = function () { diff --git a/web/pgadmin/templates/base.html b/web/pgadmin/templates/base.html index 375b39d..8be0205 100755 --- a/web/pgadmin/templates/base.html +++ b/web/pgadmin/templates/base.html @@ -144,6 +144,12 @@ ], "exports": 'Slick.Grid' }, + "slickgrid/slick.dataview": { + "deps": [ + "slickgrid" + ], + "exports": 'Slick.Data.DataView' + }, "flotr2": { deps: ['bean'], exports: function(bean) { diff --git a/web/pgadmin/tools/sqleditor/__init__.py b/web/pgadmin/tools/sqleditor/__init__.py index d114988..fb484b9 100644 --- a/web/pgadmin/tools/sqleditor/__init__.py +++ b/web/pgadmin/tools/sqleditor/__init__.py @@ -26,7 +26,7 @@ from pgadmin.utils.sqlautocomplete.autocomplete import SQLAutoComplete from pgadmin.misc.file_manager import Filemanager -from config import PG_DEFAULT_DRIVER +from config import PG_DEFAULT_DRIVER, ON_DEMAND_RECORD_COUNT MODULE_NAME = 'sqleditor' @@ -228,13 +228,32 @@ def start_view_data(trans_id): # Check the transaction and connection status status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id) + + # get the default connection as current connection which is attached to + # trans id holds the cursor which has query result so we cannot use that + # connection to execute another query otherwise we'll lose query result. + + manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(trans_obj.sid) + default_conn = manager.connection(did=trans_obj.did) + + # Connect to the Server if not connected. + if not default_conn.connected(): + status, msg = default_conn.connect() + if not status: + return make_json_response( + data={'status': status, 'result': u"{}".format(msg)} + ) + if status and conn is not None \ and trans_obj is not None and session_obj is not None: try: + # set fetched row count to 0 as we are executing query again. + trans_obj.update_fetched_row_cnt(0) + session_obj['command_obj'] = pickle.dumps(trans_obj, -1) # Fetch the sql and primary_keys from the object sql = trans_obj.get_sql() - pk_names, primary_keys = trans_obj.get_primary_keys() + pk_names, primary_keys = trans_obj.get_primary_keys(default_conn) # Fetch the applied filter. filter_applied = trans_obj.is_filter_applied() @@ -302,6 +321,8 @@ def start_query_tool(trans_id): # Use pickle.loads function to get the command object session_obj = grid_data[str(trans_id)] trans_obj = pickle.loads(session_obj['command_obj']) + # set fetched row count to 0 as we are executing query again. + trans_obj.update_fetched_row_cnt(0) can_edit = False can_filter = False @@ -428,43 +449,6 @@ def preferences(trans_id): return success_return() -@blueprint.route('/columns/<int:trans_id>', methods=["GET"]) -@login_required -def get_columns(trans_id): - """ - This method will returns list of columns of last async query. - - Args: - trans_id: unique transaction id - """ - columns = dict() - columns_info = None - primary_keys = None - status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id) - if status and conn is not None and session_obj is not None: - # Check PK column info is available or not - if 'primary_keys' in session_obj: - primary_keys = session_obj['primary_keys'] - - # Fetch column information - columns_info = conn.get_column_info() - if columns_info is not None: - for col in columns_info: - col_type = dict() - col_type['type_code'] = col['type_code'] - col_type['type_name'] = None - columns[col['name']] = col_type - - # As we changed the transaction object we need to - # restore it and update the session variable. - session_obj['columns_info'] = columns - update_session_grid_transaction(trans_id, session_obj) - - return make_json_response(data={'status': True, - 'columns': columns_info, - 'primary_keys': primary_keys}) - - @blueprint.route('/poll/<int:trans_id>', methods=["GET"]) @login_required def poll(trans_id): @@ -476,12 +460,19 @@ def poll(trans_id): """ result = None rows_affected = 0 + rows_fetched_from = 0 + rows_fetched_to = 0 + has_more_rows = False additional_result = [] + columns = dict() + columns_info = None + primary_keys = None + types = {} # Check the transaction and connection status status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id) if status and conn is not None and session_obj is not None: - status, result = conn.poll(formatted_exception_msg=True) + status, result = conn.poll(formatted_exception_msg=True, no_result=True) if not status: return internal_server_error(result) elif status == ASYNC_OK: @@ -496,6 +487,45 @@ def poll(trans_id): if (trans_status == TX_STATUS_INERROR and trans_obj.auto_rollback): conn.execute_void("ROLLBACK;") + + st, result = conn.async_fetchmany_2darray(ON_DEMAND_RECORD_COUNT) + if st: + if 'primary_keys' in session_obj: + primary_keys = session_obj['primary_keys'] + + # Fetch column information + columns_info = conn.get_column_info() + if columns_info is not None: + for col in columns_info: + col_type = dict() + col_type['type_code'] = col['type_code'] + col_type['type_name'] = None + columns[col['name']] = col_type + + if columns: + session_obj['columns_info'] = columns + st, types = fetch_pg_types(columns, trans_obj) + + if not st: + return internal_server_error(types) + # status of async_fetchmany_2darray is True and result is none + # means nothing to fetch + if result and rows_affected > -1: + res_len = len(result) + if res_len == ON_DEMAND_RECORD_COUNT: + has_more_rows = True + + if res_len > 0: + rows_fetched_from = trans_obj.get_fetched_row_cnt() + trans_obj.update_fetched_row_cnt(rows_fetched_from + res_len) + rows_fetched_from += 1 + rows_fetched_to = trans_obj.get_fetched_row_cnt() + session_obj['command_obj'] = pickle.dumps(trans_obj, -1) + + # As we changed the transaction object we need to + # restore it and update the session variable. + update_session_grid_transaction(trans_id, session_obj) + elif status == ASYNC_EXECUTION_ABORTED: status = 'Cancel' else: @@ -536,51 +566,94 @@ def poll(trans_id): data={ 'status': status, 'result': result, 'rows_affected': rows_affected, - 'additional_messages': additional_messages + 'rows_fetched_from': rows_fetched_from, + 'rows_fetched_to': rows_fetched_to, + 'additional_messages': additional_messages, + 'has_more_rows': has_more_rows, + 'colinfo': columns_info, + 'primary_keys': primary_keys, + 'types': types } ) -@blueprint.route('/fetch/types/<int:trans_id>', methods=["GET"]) +@blueprint.route('/fetch/<int:trans_id>', methods=["GET"]) @login_required -def fetch_pg_types(trans_id): +def fetch(trans_id): + result = None + has_more_rows = False + rows_fetched_from = 0 + rows_fetched_to = 0 + + # Check the transaction and connection status + status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id) + if status and conn is not None and session_obj is not None: + status, result = conn.async_fetchmany_2darray(ON_DEMAND_RECORD_COUNT) + if not status: + status = 'Error' + else: + status = 'Success' + res_len = len(result) + if res_len == ON_DEMAND_RECORD_COUNT: + has_more_rows = True + + if res_len: + rows_fetched_from = trans_obj.get_fetched_row_cnt() + trans_obj.update_fetched_row_cnt(rows_fetched_from + res_len) + rows_fetched_from += 1 + rows_fetched_to = trans_obj.get_fetched_row_cnt() + session_obj['command_obj'] = pickle.dumps(trans_obj, -1) + update_session_grid_transaction(trans_id, session_obj) + else: + status = 'NotConnected' + result = error_msg + + return make_json_response( + data={ + 'status': status, 'result': result, + 'has_more_rows': has_more_rows, + 'rows_fetched_from': rows_fetched_from, + 'rows_fetched_to': rows_fetched_to + } + ) + + +def fetch_pg_types(columns_info, trans_obj): """ This method is used to fetch the pg types, which is required to map the data type comes as a result of the query. Args: - trans_id: unique transaction id + columns_info: """ - # Check the transaction and connection status - status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id) - if status and conn is not None \ - and trans_obj is not None and session_obj is not None: - res = {} - if 'columns_info' in session_obj \ - and session_obj['columns_info'] is not None: + # get the default connection as current connection attached to trans id + # holds the cursor which has query result so we cannot use that connection + # to execute another query otherwise we'll lose query result. - oids = [session_obj['columns_info'][col]['type_code'] for col in session_obj['columns_info']] + manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(trans_obj.sid) + default_conn = manager.connection(did=trans_obj.did) - if oids: - status, res = conn.execute_dict( - u"""SELECT oid, format_type(oid,null) as typname FROM pg_type WHERE oid IN %s ORDER BY oid; + # Connect to the Server if not connected. + res = [] + if not default_conn.connected(): + status, msg = default_conn.connect() + if not status: + return status, msg + + oids = [columns_info[col]['type_code'] for col in columns_info] + + if oids: + status, res = default_conn.execute_dict( + u"""SELECT oid, format_type(oid,null) as typname FROM pg_type WHERE oid IN %s ORDER BY oid; """, [tuple(oids)]) - if status: - # iterate through pg_types and update the type name in session object - for record in res['rows']: - for col in session_obj['columns_info']: - type_obj = session_obj['columns_info'][col] - if type_obj['type_code'] == record['oid']: - type_obj['type_name'] = record['typname'] + if not status: + return False, res - update_session_grid_transaction(trans_id, session_obj) + return status, res['rows'] else: - status = False - res = error_msg - - return make_json_response(data={'status': status, 'result': res}) + return True, [] @blueprint.route('/save/<int:trans_id>', methods=["PUT", "POST"]) diff --git a/web/pgadmin/tools/sqleditor/command.py b/web/pgadmin/tools/sqleditor/command.py index 1795155..89621c8 100644 --- a/web/pgadmin/tools/sqleditor/command.py +++ b/web/pgadmin/tools/sqleditor/command.py @@ -255,7 +255,21 @@ class SQLFilter(object): return status, result -class GridCommand(BaseCommand, SQLFilter): +class FetchedRowTracker(object): + """ + Keeps track of fetched row count. + """ + def __init__(self, **kwargs): + self.fetched_rows = 0 + + def get_fetched_row_cnt(self): + return self.fetched_rows + + def update_fetched_row_cnt(self, rows_cnt): + self.fetched_rows = rows_cnt + + +class GridCommand(BaseCommand, SQLFilter, FetchedRowTracker): """ class GridCommand(object) @@ -287,6 +301,7 @@ class GridCommand(BaseCommand, SQLFilter): """ BaseCommand.__init__(self, **kwargs) SQLFilter.__init__(self, **kwargs) + FetchedRowTracker.__init__(self, **kwargs) # Save the connection id, command type self.conn_id = kwargs['conn_id'] if 'conn_id' in kwargs else None @@ -296,10 +311,10 @@ class GridCommand(BaseCommand, SQLFilter): if self.cmd_type == VIEW_FIRST_100_ROWS or self.cmd_type == VIEW_LAST_100_ROWS: self.limit = 100 - def get_primary_keys(self): + def get_primary_keys(self, *args, **kwargs): return None, None - def save(self, changed_data): + def save(self, changed_data, default_conn=None): return forbidden(errmsg=gettext("Data cannot be saved for the current object.")) def get_limit(self): @@ -337,14 +352,14 @@ class TableCommand(GridCommand): # call base class init to fetch the table name super(TableCommand, self).__init__(**kwargs) - def get_sql(self): + def get_sql(self, default_conn=None): """ This method is used to create a proper SQL query to fetch the data for the specified table """ # Fetch the primary keys for the table - pk_names, primary_keys = self.get_primary_keys() + pk_names, primary_keys = self.get_primary_keys(default_conn) sql_filter = self.get_filter() @@ -359,13 +374,16 @@ class TableCommand(GridCommand): return sql - def get_primary_keys(self): + def get_primary_keys(self, default_conn=None): """ This function is used to fetch the primary key columns. """ driver = get_driver(PG_DEFAULT_DRIVER) - manager = driver.connection_manager(self.sid) - conn = manager.connection(did=self.did, conn_id=self.conn_id) + if default_conn is None: + manager = driver.connection_manager(self.sid) + conn = manager.connection(did=self.did, conn_id=self.conn_id) + else: + conn = default_conn pk_names = '' primary_keys = dict() @@ -397,7 +415,7 @@ class TableCommand(GridCommand): def can_filter(self): return True - def save(self, changed_data): + def save(self, changed_data, default_conn=None): """ This function is used to save the data into the database. Depending on condition it will either update or insert the @@ -406,9 +424,12 @@ class TableCommand(GridCommand): Args: changed_data: Contains data to be saved """ - - manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(self.sid) - conn = manager.connection(did=self.did, conn_id=self.conn_id) + driver = get_driver(PG_DEFAULT_DRIVER) + if default_conn is None: + manager = driver.connection_manager(self.sid) + conn = manager.connection(did=self.did, conn_id=self.conn_id) + else: + conn = default_conn status = False res = None @@ -418,14 +439,6 @@ class TableCommand(GridCommand): list_of_sql = [] _rowid = None - # Replace column positions with names - def set_column_names(data): - new_data = {} - for key in data: - new_data[changed_data['columns'][int(key)]['name']] = data[key] - - return new_data - if conn.connected(): # Start the transaction @@ -446,8 +459,7 @@ class TableCommand(GridCommand): data = changed_data[of_type][each_row]['data'] # Remove our unique tracking key data.pop('__temp_PK', None) - data = set_column_names(data) - data_type = set_column_names(changed_data[of_type][each_row]['data_type']) + data_type = changed_data[of_type][each_row]['data_type'] list_of_rowid.append(data.get('__temp_PK')) sql = render_template("/".join([self.sql_path, 'insert.sql']), @@ -461,9 +473,9 @@ class TableCommand(GridCommand): # For updated rows elif of_type == 'updated': for each_row in changed_data[of_type]: - data = set_column_names(changed_data[of_type][each_row]['data']) - pk = set_column_names(changed_data[of_type][each_row]['primary_keys']) - data_type = set_column_names(changed_data[of_type][each_row]['data_type']) + data = changed_data[of_type][each_row]['data'] + pk = changed_data[of_type][each_row]['primary_keys'] + data_type = changed_data[of_type][each_row]['data_type'] sql = render_template("/".join([self.sql_path, 'update.sql']), data_to_be_saved=data, primary_keys=pk, @@ -483,18 +495,19 @@ class TableCommand(GridCommand): rows_to_delete.append(changed_data[of_type][each_row]) # Fetch the keys for SQL generation if is_first: - # We need to covert dict_keys to normal list in Python3 - # In Python2, it's already a list & We will also fetch column names using index - keys = [ - changed_data['columns'][int(k)]['name'] - for k in list(changed_data[of_type][each_row].keys()) - ] + # We need to covert dict_keys to normal list in + # Python3 + # In Python2, it's already a list & We will also + # fetch column names using index + keys = list(changed_data[of_type][each_row].keys()) + no_of_keys = len(keys) is_first = False # Map index with column name for each row for row in rows_to_delete: for k, v in row.items(): - # Set primary key with label & delete index based mapped key + # Set primary key with label & delete index based + # mapped key try: row[changed_data['columns'][int(k)]['name']] = v except ValueError: @@ -558,7 +571,7 @@ class ViewCommand(GridCommand): # call base class init to fetch the table name super(ViewCommand, self).__init__(**kwargs) - def get_sql(self): + def get_sql(self, default_conn=None): """ This method is used to create a proper SQL query to fetch the data for the specified view @@ -613,7 +626,7 @@ class ForeignTableCommand(GridCommand): # call base class init to fetch the table name super(ForeignTableCommand, self).__init__(**kwargs) - def get_sql(self): + def get_sql(self, default_conn=None): """ This method is used to create a proper SQL query to fetch the data for the specified foreign table @@ -658,7 +671,7 @@ class CatalogCommand(GridCommand): # call base class init to fetch the table name super(CatalogCommand, self).__init__(**kwargs) - def get_sql(self): + def get_sql(self, default_conn=None): """ This method is used to create a proper SQL query to fetch the data for the specified catalog object @@ -683,7 +696,7 @@ class CatalogCommand(GridCommand): return True -class QueryToolCommand(BaseCommand): +class QueryToolCommand(BaseCommand, FetchedRowTracker): """ class QueryToolCommand(BaseCommand) @@ -693,13 +706,15 @@ class QueryToolCommand(BaseCommand): def __init__(self, **kwargs): # call base class init to fetch the table name - super(QueryToolCommand, self).__init__(**kwargs) + + BaseCommand.__init__(self, **kwargs) + FetchedRowTracker.__init__(self, **kwargs) self.conn_id = None self.auto_rollback = False self.auto_commit = True - def get_sql(self): + def get_sql(self, default_conn=None): return None def can_edit(self): diff --git a/web/pgadmin/tools/sqleditor/templates/sqleditor/js/sqleditor.js b/web/pgadmin/tools/sqleditor/templates/sqleditor/js/sqleditor.js index 2062aa2..10b3960 100644 --- a/web/pgadmin/tools/sqleditor/templates/sqleditor/js/sqleditor.js +++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/js/sqleditor.js @@ -24,7 +24,8 @@ define( 'slickgrid/plugins/slick.cellselectionmodel', 'slickgrid/plugins/slick.cellcopymanager', 'slickgrid/plugins/slick.rowselectionmodel', - 'slickgrid/slick.grid' + 'slickgrid/slick.grid', + 'slickgrid/slick.dataview' ], function( $, _, S, alertify, pgAdmin, Backbone, Backgrid, CodeMirror, pgExplain, GridSelector, clipboard, copyData @@ -497,7 +498,7 @@ define( - staged_rows: This will hold all the data which user copies/pastes/deletes in grid - deleted: - This will hold all the data which user delets in grid + This will hold all the data which user deletes in grid Events handling: ---------------- @@ -513,15 +514,6 @@ define( - We are using this event for Copy operation on grid */ - // Get the item column value using a custom 'fieldIdx' column param - get_item_column_value: function (item, column) { - if (column.pos !== undefined) { - return item[column.pos]; - } else { - return null; - } - }, - // This function is responsible to create and render the SlickGrid. render_grid: function(collection, columns, is_editable) { var self = this; @@ -586,7 +578,7 @@ define( }); var gridSelector = new GridSelector(); - grid_columns = gridSelector.getColumnDefinitionsWithCheckboxes(grid_columns); + grid_columns = self.grid_columns = gridSelector.getColumnDefinitionsWithCheckboxes(grid_columns); var grid_options = { editable: true, @@ -594,8 +586,7 @@ define( enableCellNavigation: true, enableColumnReorder: false, asyncEditorLoading: false, - autoEdit: false, - dataItemColumnValueExtractor: this.get_item_column_value + autoEdit: false }; var $data_grid = self.$el.find('#datagrid'); @@ -603,14 +594,13 @@ define( var grid_height = $($('#editor-panel').find('.wcFrame')[1]).height() - 35; $data_grid.height(grid_height); - // Add our own custom primary key to keep track of changes - _.each(collection, function(row){ - row['__temp_PK'] = epicRandomString(15); - }); + var dataView = self.dataView = new Slick.Data.DataView(), + grid = self.grid = new Slick.Grid($data_grid, dataView, grid_columns, grid_options); + // Add-on function which allow us to identify the faulty row after insert/update // and apply css accordingly - collection.getItemMetadata = function(i) { + dataView.getItemMetadata = function(i) { var res = {}, cssClass = ''; if (_.has(self.handler, 'data_store')) { if (i in self.handler.data_store.added_index) { @@ -626,9 +616,8 @@ define( } } return {'cssClasses': cssClass}; - } + }; - var grid = new Slick.Grid($data_grid, collection, grid_columns, grid_options); grid.registerPlugin( new Slick.AutoTooltips({ enableForHeaderCells: false }) ); grid.setSelectionModel(new Slick.RowSelectionModel({selectActiveRow: false})); grid.registerPlugin(gridSelector); @@ -646,57 +635,70 @@ define( // Listener function to watch selected rows from grid if (editor_data.selection) { - editor_data.selection.onSelectedRangesChanged.subscribe(function(e, args) { - var collection = this.grid.getData(), - primary_key_list = _.keys(this.keys), - _tmp_keys = [], - _columns = this.columns, - rows_for_stage = {}, selected_rows_list = []; - - // Only if entire row(s) are selected via check box - if(_.has(this.selection, 'getSelectedRows')) { - selected_rows_list = this.selection.getSelectedRows(); - // We will map selected row primary key name with position - // For each Primary key - _.each(primary_key_list, function(p) { - // For each columns search primary key position - _.each(_columns, function(c) { - if(c.name == p) { - _tmp_keys.push(c.pos); - } - }); - }); - // Now assign mapped temp PK to PK - primary_key_list = _tmp_keys; - } - - // If any row(s) selected ? - if(selected_rows_list.length) { - if(this.editor.handler.can_edit) - // Enable delete rows button - $("#btn-delete-row").prop('disabled', false); - - // Enable copy rows button - $("#btn-copy-row").prop('disabled', false); - // Collect primary key data from collection as needed for stage row - _.each(selected_rows_list, function(row_index) { - var row_data = collection[row_index]; - // Store Primary key data for selected rows - rows_for_stage[row_data.__temp_PK] = _.pick(row_data, primary_key_list); - }); - } else { - // Clear the object as no rows to delete - rows_for_stage = {}; - // Disable delete/copy rows button - $("#btn-delete-row").prop('disabled', true); - $("#btn-copy-row").prop('disabled', true); - } + editor_data.selection.onSelectedRangesChanged.subscribe(function(e, args) { + var dataView = this.grid.getData(), + collection = dataView.getItems(), + primary_key_list = _.keys(this.keys), + _tmp_keys = [], + _columns = this.columns, + rows_for_stage = {}, + selected_rows_list = []; + + // Only if entire row(s) are selected via check box + if(_.has(this.selection, 'getSelectedRows')) { + selected_rows_list = this.selection.getSelectedRows(); + // We will map selected row primary key name with position + // For each Primary key + _.each(primary_key_list, function(p) { + // For each columns search primary key position + _.each(_columns, function(c) { + if(c.name == p) { + _tmp_keys.push(c.name); + } + }); + }); + // Now assign mapped temp PK to PK + primary_key_list = _tmp_keys; + } - // Update main data store - this.editor.handler.data_store.staged_rows = rows_for_stage; - }.bind(editor_data)); + // If any row(s) selected ? + if(selected_rows_list.length) { + if(this.editor.handler.can_edit) + // Enable delete rows button + $("#btn-delete-row").prop('disabled', false); + + // Enable copy rows button + $("#btn-copy-row").prop('disabled', false); + // Collect primary key data from collection as needed for stage row + _.each(selected_rows_list, function(row_index) { + var row_data = collection[row_index]; + // Store Primary key data for selected rows + rows_for_stage[row_data.__temp_PK] = _.pick(row_data, primary_key_list); + }); + } else { + // Clear the object as no rows to delete + rows_for_stage = {}; + // Disable delete/copy rows button + $("#btn-delete-row").prop('disabled', true); + $("#btn-copy-row").prop('disabled', true); + } + + // Update main data store + this.editor.handler.data_store.staged_rows = rows_for_stage; + }.bind(editor_data)); } + // listen for row count change. + dataView.onRowCountChanged.subscribe(function (e, args) { + grid.updateRowCount(); + grid.render(); + }); + + // listen for row change. + dataView.onRowsChanged.subscribe(function (e, args) { + grid.invalidateRows(args.rows); + grid.render(); + }); // Listener function which will be called before user updates existing cell // This will be used to collect primary key for that row @@ -715,8 +717,7 @@ define( // Fetch primary keys for the row before they gets modified var _columns = self.handler.columns; _.each(_keys, function(value, key) { - pos = _.where(_columns, {name: key})[0]['pos'] - current_pk[pos] = before_data[pos]; + current_pk[key] = before_data[key]; }); // Place it in main variable for later use self.handler.primary_keys_data[_pk] = current_pk @@ -748,7 +749,7 @@ define( // Fetch current row data from grid column_values = grid.getDataItem(row, cell) // Get the value from cell - value = column_values[column_info.pos] || ''; + value = column_values[column_info.field] || ''; // Copy this value to Clipboard if(value) clipboard.copyTextToClipboard(value); @@ -762,7 +763,7 @@ define( // Listener function which will be called when user updates existing rows grid.onCellChange.subscribe(function (e, args) { // self.handler.data_store.updated will holds all the updated data - var changed_column = args.grid.getColumns()[args.cell].pos, // Current field pos + var changed_column = args.grid.getColumns()[args.cell].field, // Current field pos updated_data = args.item[changed_column], // New value for current field _pk = args.item.__temp_PK || null, // Unique key to identify row column_data = {}, @@ -778,7 +779,7 @@ define( column_data); //Find type for current column self.handler.data_store.added[_pk]['err'] = false - self.handler.data_store.added[_pk]['data_type'][changed_column] = _.where(this.columns, {pos: changed_column})[0]['type']; + self.handler.data_store.added[_pk]['data_type'][changed_column] = _.where(this.columns, {name: changed_column})[0]['type']; // Check if it is updated data from existing rows? } else if(_pk in self.handler.data_store.updated) { _.extend( @@ -788,7 +789,7 @@ define( self.handler.data_store.updated[_pk]['err'] = false //Find type for current column - self.handler.data_store.updated[_pk]['data_type'][changed_column] = _.where(this.columns, {pos: changed_column})[0]['type']; + self.handler.data_store.updated[_pk]['data_type'][changed_column] = _.where(this.columns, {name: changed_column})[0]['type']; } else { // First updated data for this primary key self.handler.data_store.updated[_pk] = { @@ -798,7 +799,7 @@ define( self.handler.data_store.updated_index[args.row] = _pk; // Find & add column data type for current changed column var temp = {}; - temp[changed_column] = _.where(this.columns, {pos: changed_column})[0]['type']; + temp[changed_column] = _.where(this.columns, {name: changed_column})[0]['type']; self.handler.data_store.updated[_pk]['data_type'] = temp; } } @@ -811,25 +812,37 @@ define( // self.handler.data_store.added will holds all the newly added rows/data var _key = epicRandomString(10), column = args.column, - item = args.item, data_length = this.grid.getDataLength(); + item = args.item, data_length = this.grid.getDataLength(), + dataView = this.grid.getData(); if(item) { item.__temp_PK = _key; } - collection.push(item); + + dataView.addItem(item); self.handler.data_store.added[_key] = {'err': false, 'data': item}; self.handler.data_store.added_index[data_length] = _key; // Fetch data type & add it for the column var temp = {}; - temp[column.pos] = _.where(this.columns, {pos: column.pos})[0]['type']; + temp[column.field] = _.where(this.columns, {pos: column.pos})[0]['type']; self.handler.data_store.added[_key]['data_type'] = temp; - grid.invalidateRows([collection.length - 1]); + grid.invalidateRows([data_length - 1]); grid.updateRowCount(); grid.render(); // Enable save button $("#btn-save").prop('disabled', false); }.bind(editor_data)); + // Listen grid viewportChanged event to load next chunk of data. + grid.onViewportChanged.subscribe(function(e, args) { + var rendered_range = args.grid.getRenderedRange(), + data_len = args.grid.getDataLength(); + // start fetching next batch of records before reaching to bottom. + if (self.handler.has_more_rows && !self.handler.fetching_rows && rendered_range.bottom > data_len - 100) { + // fetch asynchronous + setTimeout(self.fetch_next.bind(self)); + } + }) // Resize SlickGrid when window resize $( window ).resize( function() { // Resize grid only when 'Data Output' panel is visible. @@ -852,6 +865,65 @@ define( if(self.data_output_panel.isVisible()) self.grid_resize(grid); }); + + for (var i = 0; i < collection.length; i++) { + // Convert to dict from 2darray + var item = {}; + for (var j = 1; j < grid_columns.length; j++) { + item[grid_columns[j]['field']] = collection[i][grid_columns[j]['pos']] + } + + item['__temp_PK'] = i.toString(); + collection[i] = item; + } + dataView.setItems(collection, '__temp_PK'); + }, + + fetch_next: function() { + var self = this; + // This will prevent fetch operation if previous fetch operation is + // already in progress. + self.fetching_rows = true; + + $.ajax({ + url: "{{ url_for('sqleditor.index') }}" + "fetch/" + self.transId, + method: 'GET', + success: function(res) { + self.handler.has_more_rows = res.data.has_more_rows; + self.update_grid_data(res.data.result); + }, + error: function(e) { + self.handler.has_more_rows = false; + self.handler.fetching_rows = false; + + if (e.readyState == 0) { + self.update_msg_history(false, + "{{ _('Not connected to the server or the connection to the server has been closed.') }}" + ); + return; + } + } + }); + }, + + update_grid_data: function(data) { + var data_len = this.grid.getDataLength(); + + this.dataView.beginUpdate(); + + for (var i = 0; i < data.length; i++) { + // Convert 2darray to dict. + var item = {}; + for (var j = 1; j < this.grid_columns.length; j++) { + item[this.grid_columns[j]['field']] = data[i][this.grid_columns[j]['pos']] + } + + item['__temp_PK'] = (data_len + i).toString(); + this.dataView.addItem(item); + } + + this.dataView.endUpdate(); + this.handler.fetching_rows = false; }, /* This function is responsible to render output grid */ @@ -1526,6 +1598,8 @@ define( self.explain_buffers = false; self.explain_timing = false; self.is_new_browser_tab = is_new_browser_tab; + self.has_more_rows = false; + self.fetching_rows = false; // We do not allow to call the start multiple times. if (self.gridView) @@ -1629,6 +1703,8 @@ define( self.query_start_time = new Date(); self.rows_affected = 0; self._init_polling_flags(); + self.has_more_rows = false; + self.fetching_rows = false; self.trigger( 'pgadmin-sqleditor:loading-icon:show', @@ -1707,45 +1783,16 @@ define( }); }, - // This function makes the ajax call to fetch columns for last async query, - get_columns: function(poll_result) { - var self = this; - // Check the flag and decide if we need to fetch columns from server - // or use the columns data stored locally from previous call? - if (self.FETCH_COLUMNS_FROM_SERVER) { - $.ajax({ - url: "{{ url_for('sqleditor.index') }}" + "columns/" + self.transId, - method: 'GET', - success: function(res) { - poll_result.colinfo = res.data.columns; - poll_result.primary_keys = res.data.primary_keys; - self.call_render_after_poll(poll_result); - // Set a flag to get columns to false & set the value for future use - self.FETCH_COLUMNS_FROM_SERVER = false; - self.COLUMNS_DATA = res; - }, - error: function(e) { - var msg = e.responseText; - if (e.responseJSON != undefined && e.responseJSON.errormsg != undefined) - msg = e.responseJSON.errormsg; - alertify.error(msg, 5); - } - }); - } else { - // Use the previously saved columns data - poll_result.colinfo = self.COLUMNS_DATA.data.columns; - poll_result.primary_keys = self.COLUMNS_DATA.data.primary_keys; - self.call_render_after_poll(poll_result); - } - }, - // This is a wrapper to call _render function // We need this because we have separated columns route & result route // We need to combine both result here in wrapper before rendering grid call_render_after_poll: function(res) { var self = this; self.query_end_time = new Date(); - self.rows_affected = res.rows_affected; + self.rows_affected = res.rows_affected, + self.rows_fetched_from = res.rows_fetched_from, + self.rows_fetched_to = res.rows_fetched_to, + self.has_more_rows = res.has_more_rows; /* If no column information is available it means query runs successfully with no result to display. In this @@ -1794,7 +1841,8 @@ define( 'pgadmin-sqleditor:loading-icon:message', "{{ _('Loading data from the database server and rendering...') }}" ); - self.get_columns(res.data); + + self.call_render_after_poll(res.data); } else if (res.data.status === 'Busy') { // If status is Busy then poll the result by recursive call to the poll function @@ -1966,132 +2014,113 @@ define( // Hide the loading icon self.trigger('pgadmin-sqleditor:loading-icon:hide'); $("#btn-flash").prop('disabled', false); - }.bind(self), - function() { - this.trigger('pgadmin-sqleditor:loading-icon:hide'); - $("#btn-flash").prop('disabled', false); }.bind(self) ); }, // This function creates the columns as required by the backgrid - _fetch_column_metadata: function(data, cb, _fail) { + _fetch_column_metadata: function(data, cb) { var colinfo = data.colinfo, primary_keys = data.primary_keys, result = data.result, columns = [], self = this; - self.trigger( - 'pgadmin-sqleditor:loading-icon:message', - "{{ _('Retrieving information about the columns returned...') }}" - ); + // Store pg_types in an array + var pg_types = new Array(); + _.each(data.types, function(r) { + pg_types[r.oid] = [r.typname]; + }); - // Make ajax call to fetch the pg types to map numeric data type - $.ajax({ - url: "{{ url_for('sqleditor.index') }}" + "fetch/types/" + self.transId, - method: 'GET', - success: function(res) { - if (res.data.status) { - // Store pg_types in an array - var pg_types = new Array(); - _.each(res.data.result.rows, function(r) { - pg_types[r.oid] = [r.typname]; - }); + // Create columns required by slick grid to render + _.each(colinfo, function(c) { + var is_primary_key = false; - // Create columns required by backgrid to render - _.each(colinfo, function(c) { - var is_primary_key = false; + // Check whether table have primary key + if (_.size(primary_keys) > 0) { + _.each(primary_keys, function (value, key) { + if (key === c.name) + is_primary_key = true; + }); + } - // Check whether table have primary key - if (_.size(primary_keys) > 0) { - _.each(primary_keys, function (value, key) { - if (key === c.name) - is_primary_key = true; - }); - } + // To show column label and data type in multiline, + // The elements should be put inside the div. + // Create column label and type. + var col_type = column_label = ''; + var type = pg_types[c.type_code] ? + pg_types[c.type_code][0] : + // This is the case where user might + // have use casting so we will use type + // returned by cast function + pg_types[pg_types.length - 1][0] ? + pg_types[pg_types.length - 1][0] : 'unknown'; + + if (!is_primary_key) + col_type += ' ' + type; + else + col_type += ' [PK] ' + type; - // To show column label and data type in multiline, - // The elements should be put inside the div. - // Create column label and type. - var col_type = column_label = ''; - var type = pg_types[c.type_code] ? - pg_types[c.type_code][0] : - // This is the case where user might - // have use casting so we will use type - // returned by cast function - pg_types[pg_types.length - 1][0] ? - pg_types[pg_types.length - 1][0] : 'unknown'; - - if (!is_primary_key) - col_type += ' ' + type; - else - col_type += ' [PK] ' + type; + if (c.precision && c.precision >= 0 && c.precision != 65535) { + col_type += ' (' + c.precision; + col_type += c.scale && c.scale != 65535 ? + ',' + c.scale + ')': + ')'; + } - if (c.precision && c.precision >= 0 && c.precision != 65535) { - col_type += ' (' + c.precision; - col_type += c.scale && c.scale != 65535 ? - ',' + c.scale + ')': - ')'; - } + // Identify cell type of column. + switch(type) { + case "json": + case "json[]": + case "jsonb": + case "jsonb[]": + col_cell = 'Json'; + break; + case "smallint": + case "integer": + case "bigint": + case "decimal": + case "numeric": + case "real": + case "double precision": + col_cell = 'number'; + break; + case "boolean": + col_cell = 'boolean'; + break; + case "character": + case "character[]": + case "character varying": + case "character varying[]": + if (c.internal_size && c.internal_size >= 0 && c.internal_size != 65535) { + // Update column type to display length on column header + col_type += ' (' + c.internal_size + ')'; + } + col_cell = 'string'; + break; + default: + col_cell = 'string'; + } - // Identify cell type of column. - switch(type) { - case "json": - case "json[]": - case "jsonb": - case "jsonb[]": - col_cell = 'Json'; - break; - case "smallint": - case "integer": - case "bigint": - case "decimal": - case "numeric": - case "real": - case "double precision": - col_cell = 'number'; - break; - case "boolean": - col_cell = 'boolean'; - break; - case "character": - case "character[]": - case "character varying": - case "character varying[]": - if (c.internal_size && c.internal_size >= 0 && c.internal_size != 65535) { - // Update column type to display length on column header - col_type += ' (' + c.internal_size + ')'; - } - col_cell = 'string'; - break; - default: - col_cell = 'string'; - } + column_label = c.display_name + '<br>' + col_type; - column_label = c.display_name + '<br>' + col_type; - - var col = { - 'name': c.name, - 'pos': c.pos, - 'label': column_label, - 'cell': col_cell, - 'can_edit': self.can_edit, - 'type': type - }; - columns.push(col); - }); - } - else { - alertify.alert('Fetching Type Error', res.data.result); - } - self.columns = columns; - if (cb && typeof(cb) == 'function') { - cb(); - } - }, - fail: _fail + var col = { + 'name': c.name, + 'pos': c.pos, + 'label': column_label, + 'cell': col_cell, + 'can_edit': self.can_edit, + 'type': type + }; + columns.push(col); }); + + self.columns = columns; + if (cb && typeof(cb) == 'function') { + cb(); + } + + }, // This function is used to raise appropriate message. @@ -2102,22 +2131,21 @@ define( self.gridView.messages_panel.focus(); - if (self.is_query_tool) { - if (clear_grid) { - // Delete grid - if (self.gridView.handler.slickgrid) { - self.gridView.handler.slickgrid.destroy(); - - } - // Misc cleaning - self.columns = undefined; - self.collection = undefined; + if (clear_grid) { + // Delete grid + if (self.gridView.handler.slickgrid) { + self.gridView.handler.slickgrid.destroy(); - $('.sql-editor-message').text(msg); - } else { - $('.sql-editor-message').append(msg); } + // Misc cleaning + self.columns = undefined; + self.collection = undefined; + + $('.sql-editor-message').text(msg); + } else { + $('.sql-editor-message').append(msg); } + // Scroll automatically when msgs appends to element setTimeout(function(){ $(".sql-editor-message").scrollTop($(".sql-editor-message")[0].scrollHeight);; @@ -2174,67 +2202,71 @@ define( is_added = _.size(self.data_store.added), is_updated = _.size(self.data_store.updated); - // Remove newly added rows from staged rows as we don't want to send them on server - if(is_added) { - _.each(self.data_store.added, function(val, key) { - if(key in self.data_store.staged_rows) { - // Remove the row from data store so that we do not send it on server - deleted_keys.push(key); - delete self.data_store.staged_rows[key]; - delete self.data_store.added[key] - } - }); + // Remove newly added rows from staged rows as we don't want to send them on server + if(is_added) { + _.each(self.data_store.added, function(val, key) { + if(key in self.data_store.staged_rows) { + // Remove the row from data store so that we do not send it on server + deleted_keys.push(key); + delete self.data_store.staged_rows[key]; + delete self.data_store.added[key] } + }); + } - // If only newly rows to delete and no data is there to send on server - // then just re-render the grid - if(_.size(self.data_store.staged_rows) == 0) { - var grid = self.slickgrid, data = grid.getData(), idx = 0; - if(deleted_keys.length){ - // Remove new rows from grid data using deleted keys - data = _.reject(data, function(d){ - return (d && _.indexOf(deleted_keys, d.__temp_PK) > -1) - }); - } - grid.resetActiveCell(); - grid.setData(data, true); - grid.setSelectedRows([]); - grid.invalidate(); - // Nothing to copy or delete here - $("#btn-delete-row").prop('disabled', true); - $("#btn-copy-row").prop('disabled', true); - if(_.size(self.data_store.added) || is_updated) { - // Do not disable save button if there are - // any other changes present in grid data - $("#btn-save").prop('disabled', false); - } else { - $("#btn-save").prop('disabled', true); - } - alertify.success("{{ _('Row(s) deleted') }}"); - } else { - // There are other data to needs to be updated on server - if(is_updated) { - alertify.alert("{{ _('Operation failed') }}", - "{{ _('There are unsaved changes in grid, Please save them first to avoid inconsistency in data') }}" - ); - return; - } - alertify.confirm("{{ _('Delete Row(s)') }}", - "{{ _('Are you sure you wish to delete selected row(s)?') }}", - function() { - $("#btn-delete-row").prop('disabled', true); - $("#btn-copy-row").prop('disabled', true); - // Change the state - self.data_store.deleted = self.data_store.staged_rows; - self.data_store.staged_rows = {}; - // Save the changes on server - self._save(); - }, - function() { - // Do nothing as user canceled the operation. - } - ).set('labels', {ok:'Yes', cancel:'No'}); + // If only newly rows to delete and no data is there to send on server + // then just re-render the grid + if(_.size(self.data_store.staged_rows) == 0) { + var grid = self.slickgrid, + dataView = grid.getData(), + data = dataView.getItems(), + idx = 0; + + if(deleted_keys.length){ + // Remove new rows from grid data using deleted keys + data = _.reject(data, function(d){ + return (d && _.indexOf(deleted_keys, d.__temp_PK) > -1) + }); + } + grid.resetActiveCell(); + grid.setData(data, true); + grid.setSelectedRows([]); + grid.invalidate(); + // Nothing to copy or delete here + $("#btn-delete-row").prop('disabled', true); + $("#btn-copy-row").prop('disabled', true); + if(_.size(self.data_store.added) || is_updated) { + // Do not disable save button if there are + // any other changes present in grid data + $("#btn-save").prop('disabled', false); + } else { + $("#btn-save").prop('disabled', true); + } + alertify.success("{{ _('Row(s) deleted') }}"); + } else { + // There are other data to needs to be updated on server + if(is_updated) { + alertify.alert("{{ _('Operation failed') }}", + "{{ _('There are unsaved changes in grid, Please save them first to avoid inconsistency in data') }}" + ); + return; + } + alertify.confirm("{{ _('Delete Row(s)') }}", + "{{ _('Are you sure you wish to delete selected row(s)?') }}", + function() { + $("#btn-delete-row").prop('disabled', true); + $("#btn-copy-row").prop('disabled', true); + // Change the state + self.data_store.deleted = self.data_store.staged_rows; + self.data_store.staged_rows = {}; + // Save the changes on server + self._save(); + }, + function() { + // Do nothing as user canceled the operation. } + ).set('labels', {ok:'Yes', cancel:'No'}); + } }, @@ -2297,26 +2329,24 @@ define( data: JSON.stringify(req_data), success: function(res) { var grid = self.slickgrid, - data = grid.getData(); + dataView = grid.getData(), + data = dataView.getItems(); if (res.data.status) { // Remove deleted rows from client as well if(is_deleted) { var rows = grid.getSelectedRows(); - /* In JavaScript sorting by default is lexical, - * To make sorting numerical we need to pass function - * After that we will Reverse the order of sorted array - * so that when we remove it does not affect array index - */ if(data.length == rows.length) { // This means all the rows are selected, clear all data data = []; + dataView.setItems(data, '__temp_PK'); } else { - rows = rows.sort(function(a,b){return a - b}).reverse(); - rows.forEach(function(idx) { - data.splice(idx, 1); - }); + dataView.beginUpdate(); + for (var i = 0; i < rows.length; i++) { + item = grid.getDataItem(rows[i]); + dataView.deleteItem(item['__temp_PK']); + } + dataView.endUpdate(); } - grid.setData(data, true); grid.setSelectedRows([]); } @@ -2387,9 +2417,13 @@ define( // Find index of row at fault from grid data _find_rowindex: function(rowid) { - var self = this; - var grid = self.slickgrid, - data = grid.getData(), _rowid, count = 0, _idx = -1; + var self = this, + grid = self.slickgrid, + dataView = grid.getData(), + data = dataView.getItems(), + _rowid, + count = 0, + _idx = -1; // If _rowid is object then it's update/delete operation if(_.isObject(rowid)) { _rowid = rowid; @@ -2616,11 +2650,6 @@ define( // This function will set the required flag for polling response data _init_polling_flags: function() { var self = this; - // Set a flag to get columns - self.FETCH_COLUMNS_FROM_SERVER = true; - // We will set columns data in this variable for future use once we fetch it - // from server - self.COLUMNS_DATA = {}; // To get a timeout for polling fallback timer in seconds in // regards to elapsed time @@ -2716,7 +2745,7 @@ define( return; // Add column position and it's value to data - data[column_info.field] = _values[column_info.pos] || ''; + data[column_info.field] = _values[column_info.field] || ''; self.trigger( 'pgadmin-sqleditor:loading-icon:show', @@ -2786,7 +2815,7 @@ define( return; // Add column position and it's value to data - data[column_info.field] = _values[column_info.pos] || ''; + data[column_info.field] = _values[column_info.field] || ''; self.trigger( 'pgadmin-sqleditor:loading-icon:show', @@ -2947,60 +2976,63 @@ define( // This function will paste the selected row. _paste_row: function() { - var self = this, col_info = {}, - grid = self.slickgrid, - data = grid.getData(); - // Deep copy - var copied_rows = $.extend(true, [], self.copied_rows), - _tmp_copied_row = {}; + var self = this, + col_info = {}, + grid = self.slickgrid, + dataView = grid.getData(), + data = dataView.getItems(), + // Deep copy + copied_rows = $.extend(true, [], self.copied_rows), + _tmp_copied_row = {}; // If there are rows to paste? - if(copied_rows.length > 0) { - // Enable save button so that user can - // save newly pasted rows on server - $("#btn-save").prop('disabled', false); - // Generate Unique key for each pasted row(s) - _.each(copied_rows, function(row) { - var _pk = epicRandomString(8); - row.__temp_PK = _pk; - }); - data = data.concat(copied_rows); - grid.setData(data, true); - grid.updateRowCount(); - grid.setSelectedRows([]); - grid.invalidateAllRows(); - grid.render(); - - // Fetch column name & its data type - _.each(self.columns, function(c) { - col_info[String(c.pos)] = c.type; - }); + if(copied_rows.length > 0) { + // Enable save button so that user can + // save newly pasted rows on server + $("#btn-save").prop('disabled', false); + // Generate Unique key for each pasted row(s) + _.each(copied_rows, function(row) { + var _pk = epicRandomString(8); + row.__temp_PK = _pk; + }); - // insert these data in data_store as well to save them on server - for (var j = 0; j < copied_rows.length; j += 1) { - self.data_store.added[copied_rows[j].__temp_PK] = { - 'data_type': {}, - 'data': {} - }; - self.data_store.added[copied_rows[j].__temp_PK]['data_type'] = col_info; - // We need to convert it from array to dict so that server can - // understand the data properly - _.each(copied_rows[j], function(val, key) { - // If value is array then convert it to string - if(_.isArray(val)) { - _tmp_copied_row[String(key)] = val.toString(); - // If value is object then stringify it - } else if(_.isObject(val)) { - _tmp_copied_row[j][String(key)] = JSON.stringify(val); - } else { - _tmp_copied_row[String(key)] = val; - } - }); - self.data_store.added[copied_rows[j].__temp_PK]['data'] = _tmp_copied_row; - // reset the variable - _tmp_copied_row = {}; - } + dataView.beginUpdate(); + for (var i = 0; i < copied_rows.length; i++) { + dataView.addItem(copied_rows[i]); } + dataView.endUpdate(); + grid.setSelectedRows([]); + + // Fetch column name & its data type + _.each(self.columns, function(c) { + col_info[String(c.name)] = c.type; + }); + + // insert these data in data_store as well to save them on server + for (var j = 0; j < copied_rows.length; j += 1) { + self.data_store.added[copied_rows[j].__temp_PK] = { + 'data_type': {}, + 'data': {} + }; + self.data_store.added[copied_rows[j].__temp_PK]['data_type'] = col_info; + // We need to convert it from array to dict so that server can + // understand the data properly + _.each(copied_rows[j], function(val, key) { + // If value is array then convert it to string + if(_.isArray(val)) { + _tmp_copied_row[String(key)] = val.toString(); + // If value is object then stringify it + } else if(_.isObject(val)) { + _tmp_copied_row[j][String(key)] = JSON.stringify(val); + } else { + _tmp_copied_row[String(key)] = val; + } + }); + self.data_store.added[copied_rows[j].__temp_PK]['data'] = _tmp_copied_row; + // reset the variable + _tmp_copied_row = {}; + } + } }, // This function will set the limit for SQL query @@ -3070,6 +3102,9 @@ define( sql = '', history_msg = ''; + self.has_more_rows = false; + self.fetching_rows = false; + /* If code is selected in the code mirror then execute * the selected part else execute the complete code. */ diff --git a/web/pgadmin/utils/driver/abstract.py b/web/pgadmin/utils/driver/abstract.py index 9b2363c..7db3e37 100644 --- a/web/pgadmin/utils/driver/abstract.py +++ b/web/pgadmin/utils/driver/abstract.py @@ -101,6 +101,12 @@ class BaseConnection(object): - Implement this method to execute the given query and returns the result as an array of dict (column name -> value) format. + * def async_fetchmany_2darray(records=-1, formatted_exception_msg=False): + - Implement this method to retrieve result of asynchronous connection and + polling with no_result flag set to True. + This returns the result as a 2 dimensional array. + If records is -1 then fetchmany will behave as fetchall. + * connected() - Implement this method to get the status of the connection. It should return True for connected, otherwise False @@ -133,7 +139,7 @@ class BaseConnection(object): - Implement this method to wait for asynchronous connection with timeout. This must be a non blocking call. - * poll(formatted_exception_msg) + * poll(formatted_exception_msg, no_result) - Implement this method to poll the data of query running on asynchronous connection. @@ -180,6 +186,10 @@ class BaseConnection(object): pass @abstractmethod + def async_fetchmany_2darray(self, records=-1, formatted_exception_msg=False): + pass + + @abstractmethod def connected(self): pass @@ -208,7 +218,7 @@ class BaseConnection(object): pass @abstractmethod - def poll(self, formatted_exception_msg=True): + def poll(self, formatted_exception_msg=True, no_result=False): pass @abstractmethod diff --git a/web/pgadmin/utils/driver/psycopg2/__init__.py b/web/pgadmin/utils/driver/psycopg2/__init__.py index e474817..c48ab43 100644 --- a/web/pgadmin/utils/driver/psycopg2/__init__.py +++ b/web/pgadmin/utils/driver/psycopg2/__init__.py @@ -973,6 +973,55 @@ Failed to execute query (execute_void) for the server #{server_id} - {conn_id} return True, {'columns': columns, 'rows': rows} + def async_fetchmany_2darray(self, records=2000, formatted_exception_msg=False): + """ + User should poll and check if status is ASYNC_OK before calling this + function + Args: + records: no of records to fetch. use -1 to fetchall. + formatted_exception_msg: + + Returns: + + """ + cur = self.__async_cursor + if not cur: + return False, gettext( + "Cursor could not be found for the async connection." + ) + + if self.conn.isexecuting(): + return False, gettext( + "Asynchronous query execution/operation underway." + ) + + if self.row_count > 0: + result = [] + # For DDL operation, we may not have result. + # + # Because - there is not direct way to differentiate DML and + # DDL operations, we need to rely on exception to figure + # that out at the moment. + try: + if records == -1: + res = cur.fetchall() + else: + res = cur.fetchmany(records) + for row in res: + new_row = [] + for col in self.column_info: + new_row.append(row[col['name']]) + result.append(new_row) + except psycopg2.ProgrammingError as e: + result = None + else: + # User performed operation which dose not produce record/s as + # result. + # for eg. DDL operations. + return True, None + + return True, result + def connected(self): if self.conn: if not self.conn.closed: @@ -1119,7 +1168,7 @@ Failed to reset the connection to the server due to following error: "poll() returned %s from _wait_timeout function" % state ) - def poll(self, formatted_exception_msg=False): + def poll(self, formatted_exception_msg=False, no_result=False): """ This function is a wrapper around connection's poll function. It internally uses the _wait_timeout method to poll the @@ -1129,6 +1178,7 @@ Failed to reset the connection to the server due to following error: Args: formatted_exception_msg: if True then function return the formatted exception message, otherwise error string. + no_result: If True then only poll status will be returned. """ cur = self.__async_cursor @@ -1184,23 +1234,23 @@ Failed to reset the connection to the server due to following error: pos += 1 self.row_count = cur.rowcount - - if cur.rowcount > 0: - result = [] - # For DDL operation, we may not have result. - # - # Because - there is not direct way to differentiate DML and - # DDL operations, we need to rely on exception to figure that - # out at the moment. - try: - for row in cur: - new_row = [] - for col in self.column_info: - new_row.append(row[col['name']]) - result.append(new_row) - - except psycopg2.ProgrammingError: - result = None + if not no_result: + if cur.rowcount > 0: + result = [] + # For DDL operation, we may not have result. + # + # Because - there is not direct way to differentiate DML and + # DDL operations, we need to rely on exception to figure + # that out at the moment. + try: + for row in cur: + new_row = [] + for col in self.column_info: + new_row.append(row[col['name']]) + result.append(new_row) + + except psycopg2.ProgrammingError: + result = None return status, result
-- Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers