public inbox for [email protected]
help / color / mirror / Atom feedFrom: Yosry Muhammad <[email protected]>
To: [email protected]
To: Dave Page <[email protected]>
Subject: [GSoC] Finalized First Patch
Date: Mon, 1 Jul 2019 21:19:36 +0200
Message-ID: <CAFSMqn8yLA329GFHSm0Bn4cEqvd+kVoYpCAB5mRDbVF6NNaxxA@mail.gmail.com> (raw)
Dear all,
This is a final version of the patch I have been working on since the
beginning of the GSoC project with tests and documentation.
This patch includes:
Features:
- Implementeing the first version detecting updatable resultsets. Saving
edited data works the same way as View Data mode (with small changes). A
resultset is updatable if:
- All columns belong to the same table.
- All primary keys are selected.
- No duplicate columns.
- Adding the new Save Data icon and its shortcut in preferences. The old
Save icon is used exclusively for Saving files now.
- Integrating saving data changes into the ongoing transaction (if any).
The user is notified that they need to commit the changes if auto-commit is
off.
- A failed save of data changes rolls back the data changes only, does not
rollback previous queries in the same transaction.
- Alerting the user when Execute/Explain is clicked with unsaved changes in
the grid.
- Modified/New cells are now highlighted.
- Alerting the user when exiting with uncommited transactions.
- Re-implementing the on tab close event of the query tool as multiple
dialogs may be required for: unsaved data changes, unsaved file changes &
uncommited transactions (they can all be enabled/disabled in preferences).
- Hiding queries generated by pgAdmin from query history (until they are
substituted by a mogrified version and have a checkbox added to
enable/disable them).
Bug fixes:
- Fixed a bug where exit on save would exit even if the save was not
successful.
- Fixed a bug where alertify confirm dialogs had midword break wrapping.
Tests:
- Python tests:
- test_is_query_resultset_updatable: Tests that updatable resultsets
are detected correctly.
- test_save_changed_data: Tests that additions, deletions & updates are
performed correctly on updatable resultsets.
- Feature tests:
- query_tool_journey_test (existing - extended): Test that when the
query resultset is updatable the user can modify cells and add new rows
(and vice versa).
- Updated other feature tests to match the new icon.
- JS tests:
- Updated call_render_after_poll_specs.js &
keyboard_shortcuts_specs.js to test updates in related parts of the code.
I could not add JS tests to test that the new Save Data button is enabled
when the user edits a cell as I cannot mimic the actions of editing the
grid. However, the new button is now used in View/Edit data feature tests
and it works correctly.
I also could not add JS tests to check that when the resultset is updatable
the grid should be editable as the current code in sqleditor.js is not
testable and it will required a lot of refactoring of this file, but again,
this is covered by feature tests.
Documentation:
- Updated editgrid.rst, query_tool.rst, query_tool_shortcuts.rst,
preferences.rst & keyboard_shortcuts.rst to match the new changes.
- Updated the following screenshots: query_output_data.png, query_tool.png
& query_toolbar.png
The patch passes all tests performed by "make check" command.
Please review, looking forward to any comments or feedback.
Thanks !
--
*Yosry Muhammad Yosry*
Computer Engineering student,
The Faculty of Engineering,
Cairo University (2021).
Class representative of CMP 2021.
https://www.linkedin.com/in/yosrym93/
Attachments:
[text/x-patch] query_tool_automatic_mode_switch_v1.5.patch (148.1K, 3-query_tool_automatic_mode_switch_v1.5.patch)
download | inline diff:
diff --git a/docs/en_US/editgrid.rst b/docs/en_US/editgrid.rst
index 1854274d..b697449d 100644
--- a/docs/en_US/editgrid.rst
+++ b/docs/en_US/editgrid.rst
@@ -42,13 +42,15 @@ The top row of the data grid displays the name of each column, the data type,
and if applicable, the number of characters allowed. A column that is part of
the primary key will additionally be marked with [PK].
+.. _modifying-data-grid:
+
To modify the displayed data:
* To change a numeric value within the grid, double-click the value to select
the field. Modify the content in the square in which it is displayed.
* To change a non-numeric value within the grid, double-click the content to
access the edit bubble. After modifying the contentof the edit bubble, click
- the *Save* button to display your changes in the data grid, or *Cancel* to
+ the *Ok* button to display your changes in the data grid, or *Cancel* to
exit the edit bubble without saving.
To enter a newline character, click Ctrl-Enter or Shift-Enter. Newline
@@ -70,9 +72,7 @@ quotes to the table, you need to escape these quotes, by typing \'\'
To delete a row, press the *Delete* toolbar button. A popup will open, asking
you to confirm the deletion.
-To commit the changes to the server, select the *Save* toolbar button.
-Modifications to a row are written to the server automatically when you select
-a different row.
+To commit the changes to the server, select the *Save Data* toolbar button.
**Geometry Data Viewer**
diff --git a/docs/en_US/images/query_output_data.png b/docs/en_US/images/query_output_data.png
old mode 100755
new mode 100644
index 8eec7087..6f6f0dc1
Binary files a/docs/en_US/images/query_output_data.png and b/docs/en_US/images/query_output_data.png differ
diff --git a/docs/en_US/images/query_tool.png b/docs/en_US/images/query_tool.png
old mode 100755
new mode 100644
index b9d165ef..6f6f0dc1
Binary files a/docs/en_US/images/query_tool.png and b/docs/en_US/images/query_tool.png differ
diff --git a/docs/en_US/images/query_toolbar.png b/docs/en_US/images/query_toolbar.png
index 9ae9cef1..b87c0d72 100644
Binary files a/docs/en_US/images/query_toolbar.png and b/docs/en_US/images/query_toolbar.png differ
diff --git a/docs/en_US/keyboard_shortcuts.rst b/docs/en_US/keyboard_shortcuts.rst
index 360efb32..42aee2aa 100644
--- a/docs/en_US/keyboard_shortcuts.rst
+++ b/docs/en_US/keyboard_shortcuts.rst
@@ -131,6 +131,8 @@ When using the Query Tool, the following shortcuts are available:
+==========================+====================+===================================+
| F5 | F5 | Execute query |
+--------------------------+--------------------+-----------------------------------+
+ | F6 | F6 | Save data changes |
+ +--------------------------+--------------------+-----------------------------------+
| F7 | F7 | EXPLAIN query |
+--------------------------+--------------------+-----------------------------------+
| Shift + F7 | Shift + F7 | EXPLAIN ANALYZE query |
diff --git a/docs/en_US/preferences.rst b/docs/en_US/preferences.rst
index 28ecb6ab..0e07ebc5 100644
--- a/docs/en_US/preferences.rst
+++ b/docs/en_US/preferences.rst
@@ -294,6 +294,10 @@ a graphical EXPLAIN.
Use the fields on the *Options* panel to manage editor preferences.
+* When the *Alert on uncommited transactions?* switch is set to *True*, the
+ Query Tool will alert the user when exiting while the current transaction
+ is not commited.
+
* When the *Auto-Commit?* switch is set to *True*, each successful query is
committed after execution.
diff --git a/docs/en_US/query_tool.rst b/docs/en_US/query_tool.rst
index 2f7000e3..07fcaa62 100644
--- a/docs/en_US/query_tool.rst
+++ b/docs/en_US/query_tool.rst
@@ -12,11 +12,13 @@ allows you to:
* Issue ad-hoc SQL queries.
* Execute arbitrary SQL commands.
+* Edit the result set of a SELECT query if it is :ref:`updatable <updatable-result-set>`.
* Displays current connection and transaction status as configured by the user.
* Save the data displayed in the output panel to a CSV file.
* Review the execution plan of a SQL statement in either a text or a graphical format.
* View analytical information about a SQL statement.
+
.. image:: images/query_tool.png
:alt: Query tool window
:align: center
@@ -120,6 +122,25 @@ You can:
set query execution options.
* Use the *Download as CSV* icon to download the content of the *Data Output*
tab as a comma-delimited file.
+* Edit the data in the result set of a SELECT query if it is updatable.
+
+.. _updatable-result-set:
+
+A result set is updatable if:
+
+* All the columns belong to the same table.
+* All the primary keys of the table are selected.
+* No columns are duplicated.
+
+An updatable result set can be modified just like in :ref:`View/Edit Data <modifying-data-grid>` mode.
+
+If Auto-commit is off, the data changes are made as part of the ongoing transaction, if
+no transaction is ongoing a new one is initiated. The data changes are not commited to
+the database unless the transaction is commited.
+If any errors occur during saving (for example, trying to save NULL into a column
+with NOT NULL constraint) the data changes are rolled back, however, any previously executed
+queries in the ongoing transaction are not rolled back.
+
All rowsets from previous queries or commands that are displayed in the *Data
Output* panel will be discarded when you invoke another query; open another
diff --git a/docs/en_US/query_tool_toolbar.rst b/docs/en_US/query_tool_toolbar.rst
index 3ce9deeb..2a2b5bea 100644
--- a/docs/en_US/query_tool_toolbar.rst
+++ b/docs/en_US/query_tool_toolbar.rst
@@ -31,7 +31,7 @@ File Options
+======================+===================================================================================================+================+
| *Open File* | Click the *Open File* icon to display a previously saved query in the SQL Editor. | Accesskey + O |
+----------------------+---------------------------------------------------------------------------------------------------+----------------+
- | *Save* | Click the *Save* icon to perform a quick-save of a previously saved query, or to access the | Accesskey + S |
+ | *Save File* | Click the *Save* icon to perform a quick-save of a previously saved query, or to access the | Accesskey + S |
| | *Save* menu: | |
| | | |
| | * Select *Save* to save the selected content of the SQL Editor panel in a file. | |
@@ -50,6 +50,8 @@ Editing Options
+----------------------+---------------------------------------------------------------------------------------------------+----------------+
| Icon | Behavior | Shortcut |
+======================+===================================================================================================+================+
+ | *Save Data* | Click the *Save Data* icon to save data changes in the Data Output Panel to the server. | F6 |
+ +----------------------+---------------------------------------------------------------------------------------------------+----------------+
| *Find* | Use the *Find* menu to search, replace, or navigate the code displayed in the SQL Editor: | |
| +---------------------------------------------------------------------------------------------------+----------------+
| | Select *Find* to provide a search target, and search the SQL Editor contents. | Cmd+F |
@@ -67,11 +69,10 @@ Editing Options
| | Select *Jump* to navigate to the next occurrence of the search target. | Alt+G |
+----------------------+---------------------------------------------------------------------------------------------------+----------------+
| *Copy* | Click the *Copy* icon to copy the content that is currently highlighted in the Data Output panel. | Accesskey + C |
- | | when in View/Edit data mode. | |
+----------------------+---------------------------------------------------------------------------------------------------+----------------+
- | *Paste* | Click the *Paste* icon to paste a previously row into a new row when in View/Edit data mode. | Accesskey + P |
+ | *Paste* | Click the *Paste* icon to paste a previously row into a new row. | Accesskey + P |
+----------------------+---------------------------------------------------------------------------------------------------+----------------+
- | *Delete* | Click the *Delete* icon to delete the selected rows when in View/Edit data mode. | Accesskey + D |
+ | *Delete* | Click the *Delete* icon to delete the selected rows. | Accesskey + D |
+----------------------+---------------------------------------------------------------------------------------------------+----------------+
| *Edit* | Use options on the *Edit* menu to access text editing tools; the options operate on the text | |
| | displayed in the SQL Editor panel when in Query Tool mode: | |
diff --git a/web/pgadmin/feature_tests/file_manager_test.py b/web/pgadmin/feature_tests/file_manager_test.py
index 1c74d40b..b6b35c04 100644
--- a/web/pgadmin/feature_tests/file_manager_test.py
+++ b/web/pgadmin/feature_tests/file_manager_test.py
@@ -65,7 +65,8 @@ class CheckFileManagerFeatureTest(BaseFeatureTest):
self.page.open_query_tool()
def _create_new_file(self):
- self.page.find_by_css_selector(QueryToolLocatorsCss.btn_save).click()
+ self.page.find_by_css_selector(QueryToolLocatorsCss.btn_save_file)\
+ .click()
# Set the XSS value in input
self.page.find_by_css_selector('.change_file_types')
self.page.fill_input_by_css_selector("input#file-input-path",
diff --git a/web/pgadmin/feature_tests/locators.py b/web/pgadmin/feature_tests/locators.py
index 46673f10..30ec1efb 100644
--- a/web/pgadmin/feature_tests/locators.py
+++ b/web/pgadmin/feature_tests/locators.py
@@ -1,5 +1,5 @@
class QueryToolLocatorsCss:
- btn_save = "#btn-save"
+ btn_save_file = "#btn-save-file"
btn_execute_query = "#btn-flash"
btn_query_dropdown = "#btn-query-dropdown"
btn_auto_rollback = "#btn-auto-rollback"
diff --git a/web/pgadmin/feature_tests/query_tool_journey_test.py b/web/pgadmin/feature_tests/query_tool_journey_test.py
index 79f6b7db..3cbf6f48 100644
--- a/web/pgadmin/feature_tests/query_tool_journey_test.py
+++ b/web/pgadmin/feature_tests/query_tool_journey_test.py
@@ -7,6 +7,7 @@
#
##########################################################################
+import sys
import pyperclip
import random
@@ -28,11 +29,24 @@ class QueryToolJourneyTest(BaseFeatureTest):
]
test_table_name = ""
+ test_editable_table_name = ""
def before(self):
self.test_table_name = "test_table" + str(random.randint(1000, 3000))
test_utils.create_table(
self.server, self.test_db, self.test_table_name)
+
+ self.test_editable_table_name = "test_editable_table" + \
+ str(random.randint(1000, 3000))
+ create_sql = '''
+ CREATE TABLE "%s" (
+ pk_column NUMERIC PRIMARY KEY,
+ normal_column NUMERIC
+ );
+ ''' % self.test_editable_table_name
+ test_utils.create_table_with_query(
+ self.server, self.test_db, create_sql)
+
self.page.add_server(self.server)
def runTest(self):
@@ -40,9 +54,21 @@ class QueryToolJourneyTest(BaseFeatureTest):
self._execute_query(
"SELECT * FROM %s ORDER BY value " % self.test_table_name)
+ print("Copy rows...", file=sys.stderr, end="")
self._test_copies_rows()
+ print(" OK.", file=sys.stderr)
+
+ print("Copy columns...", file=sys.stderr, end="")
self._test_copies_columns()
+ print(" OK.", file=sys.stderr)
+
+ print("History tab...", file=sys.stderr, end="")
self._test_history_tab()
+ print(" OK.", file=sys.stderr)
+
+ print("Updatable resultsets...", file=sys.stderr, end="")
+ self._test_editable_resultset()
+ print(" OK.", file=sys.stderr)
def _test_copies_rows(self):
pyperclip.copy("old clipboard contents")
@@ -162,6 +188,27 @@ class QueryToolJourneyTest(BaseFeatureTest):
.perform()
self._assert_clickable(query_we_need_to_scroll_to)
+ def _test_editable_resultset(self):
+ self.page.click_tab("Query Editor")
+
+ # Insert data into test table
+ self.__clear_query_tool()
+ self._execute_query(
+ "INSERT INTO %s VALUES (1, 1), (2, 2);"
+ % self.test_editable_table_name
+ )
+
+ # Select all data (contains the primary key -> should be editable)
+ self.__clear_query_tool()
+ query = "SELECT pk_column, normal_column FROM %s" \
+ % self.test_editable_table_name
+ self._check_query_results_editable(query, True)
+
+ # Select data without primary keys -> should not be editable
+ self.__clear_query_tool()
+ query = "SELECT normal_column FROM %s" % self.test_editable_table_name
+ self._check_query_results_editable(query, False)
+
def __clear_query_tool(self):
self.page.click_element(
self.page.find_by_xpath("//*[@id='btn-clear-dropdown']")
@@ -188,6 +235,31 @@ class QueryToolJourneyTest(BaseFeatureTest):
def _assert_clickable(self, element):
self.page.click_element(element)
+ def _check_query_results_editable(self, query, should_be_editable):
+ self._execute_query(query)
+ self.page.wait_for_spinner_to_disappear()
+
+ # Check if the first cell in the first row is editable
+ is_editable = self._check_cell_editable(1)
+ self.assertEqual(is_editable, should_be_editable)
+ # Check that new rows cannot be added
+ can_add_rows = self._check_can_add_row()
+ self.assertEqual(can_add_rows, should_be_editable)
+
+ def _check_cell_editable(self, cell_index):
+ xpath = '//div[contains(@class, "slick-cell") and ' \
+ 'contains(@class, "r' + str(cell_index) + '")]'
+ cell_el = self.page.find_by_xpath(xpath)
+ ActionChains(self.driver).double_click(cell_el).perform()
+ xpath = '//div[contains(@class, "slick-cell") and ' \
+ 'contains(@class, "r' + str(cell_index) + '") ' \
+ 'and contains(@class, "editable")]'
+ return self.page.check_if_element_exist_by_xpath(xpath)
+
+ def _check_can_add_row(self):
+ return self.page.check_if_element_exist_by_xpath(
+ '//div[contains(@class, "new-row")]')
+
def after(self):
self.page.close_query_tool()
self.page.remove_server(self.server)
diff --git a/web/pgadmin/feature_tests/view_data_dml_queries.py b/web/pgadmin/feature_tests/view_data_dml_queries.py
index 8e69ffc5..888cf716 100644
--- a/web/pgadmin/feature_tests/view_data_dml_queries.py
+++ b/web/pgadmin/feature_tests/view_data_dml_queries.py
@@ -262,7 +262,7 @@ CREATE TABLE public.defaults_{0}
# reset cell value to previous one
self._update_cell(row1_cell2_xpath, ["1", "", "int"])
- self.page.find_by_id("btn-save").click() # Save data
+ self.page.find_by_id("btn-save-data").click() # Save data
# There should be some delay after save button is clicked, as it
# takes some time to complete save ajax call otherwise discard unsaved
# changes dialog will appear if we try to execute query before previous
@@ -279,7 +279,7 @@ CREATE TABLE public.defaults_{0}
)
time.sleep(0.2)
self._update_cell(cell_xpath, config_data[str(idx)])
- self.page.find_by_id("btn-save").click() # Save data
+ self.page.find_by_id("btn-save-data").click() # Save data
# There should be some delay after save button is clicked, as it
# takes some time to complete save ajax call otherwise discard unsaved
# changes dialog will appear if we try to execute query before previous
diff --git a/web/pgadmin/static/js/keyboard_shortcuts.js b/web/pgadmin/static/js/keyboard_shortcuts.js
index c565b862..5dd2384b 100644
--- a/web/pgadmin/static/js/keyboard_shortcuts.js
+++ b/web/pgadmin/static/js/keyboard_shortcuts.js
@@ -205,6 +205,7 @@ function keyboardShortcutsQueryTool(
let toggleCaseKeys = sqlEditorController.preferences.toggle_case;
let commitKeys = sqlEditorController.preferences.commit_transaction;
let rollbackKeys = sqlEditorController.preferences.rollback_transaction;
+ let saveDataKeys = sqlEditorController.preferences.save_data;
if (this.validateShortcutKeys(executeKeys, event)) {
this._stopEventPropagation(event);
@@ -233,6 +234,9 @@ function keyboardShortcutsQueryTool(
this._stopEventPropagation(event);
queryToolActions.executeRollback(sqlEditorController);
}
+ } else if (this.validateShortcutKeys(saveDataKeys, event)) {
+ this._stopEventPropagation(event);
+ queryToolActions.saveDataChanges(sqlEditorController);
} else if ((
(this.isMac() && event.metaKey) ||
(!this.isMac() && event.ctrlKey)
diff --git a/web/pgadmin/static/js/sqleditor/call_render_after_poll.js b/web/pgadmin/static/js/sqleditor/call_render_after_poll.js
index 3f32d571..57d60537 100644
--- a/web/pgadmin/static/js/sqleditor/call_render_after_poll.js
+++ b/web/pgadmin/static/js/sqleditor/call_render_after_poll.js
@@ -37,7 +37,8 @@ export function callRenderAfterPoll(sqlEditor, alertify, res) {
const msg = sprintf(
gettext('Query returned successfully in %s.'), sqlEditor.total_time);
res.result += '\n\n' + msg;
- sqlEditor.update_msg_history(true, res.result, false);
+ sqlEditor.update_msg_history(true, res.result, true);
+ sqlEditor.reset_data_store();
if (isNotificationEnabled(sqlEditor)) {
alertify.success(msg, sqlEditor.info_notifier_timeout);
}
diff --git a/web/pgadmin/static/js/sqleditor/query_tool_actions.js b/web/pgadmin/static/js/sqleditor/query_tool_actions.js
index 7739e9b5..e5cf8a36 100644
--- a/web/pgadmin/static/js/sqleditor/query_tool_actions.js
+++ b/web/pgadmin/static/js/sqleditor/query_tool_actions.js
@@ -153,6 +153,11 @@ let queryToolActions = {
sqlEditorController.special_sql = 'ROLLBACK;';
self.executeQuery(sqlEditorController);
},
+
+ saveDataChanges: function (sqlEditorController) {
+ sqlEditorController.close_on_save = false;
+ sqlEditorController.save_data();
+ },
};
module.exports = queryToolActions;
diff --git a/web/pgadmin/static/js/sqleditor/query_tool_preferences.js b/web/pgadmin/static/js/sqleditor/query_tool_preferences.js
index c3906aaa..abceb099 100644
--- a/web/pgadmin/static/js/sqleditor/query_tool_preferences.js
+++ b/web/pgadmin/static/js/sqleditor/query_tool_preferences.js
@@ -29,7 +29,7 @@ function updateUIPreferences(sqlEditor) {
.attr('title', shortcut_accesskey_title('Open File',preferences.btn_open_file))
.attr('accesskey', shortcut_key(preferences.btn_open_file));
- $el.find('#btn-save')
+ $el.find('#btn-save-file')
.attr('title', shortcut_accesskey_title('Save File',preferences.btn_save_file))
.attr('accesskey', shortcut_key(preferences.btn_save_file));
@@ -97,6 +97,10 @@ function updateUIPreferences(sqlEditor) {
.attr('title',
shortcut_title('Download as CSV',preferences.download_csv));
+ $el.find('#btn-save-data')
+ .attr('title',
+ shortcut_title('Save Data Changes',preferences.save_data));
+
$el.find('#btn-commit')
.attr('title',
shortcut_title('Commit',preferences.commit_transaction));
diff --git a/web/pgadmin/static/scss/_alertify.overrides.scss b/web/pgadmin/static/scss/_alertify.overrides.scss
index 413e09e7..d43becd5 100644
--- a/web/pgadmin/static/scss/_alertify.overrides.scss
+++ b/web/pgadmin/static/scss/_alertify.overrides.scss
@@ -56,7 +56,7 @@
bottom: $footer-height-calc !important;
}
.ajs-wrap-text {
- word-break: break-all;
+ word-break: normal;
word-wrap: break-word;
}
/* Removes padding from alertify footer */
diff --git a/web/pgadmin/tools/datagrid/templates/datagrid/index.html b/web/pgadmin/tools/datagrid/templates/datagrid/index.html
index f138b9a4..9468eb3e 100644
--- a/web/pgadmin/tools/datagrid/templates/datagrid/index.html
+++ b/web/pgadmin/tools/datagrid/templates/datagrid/index.html
@@ -21,7 +21,7 @@
tabindex="0">
<i class="fa fa-folder-open-o sql-icon-lg" aria-hidden="true"></i>
</button>
- <button id="btn-save" type="button" class="btn btn-sm btn-secondary"
+ <button id="btn-save-file" type="button" class="btn btn-sm btn-secondary"
title=""
accesskey=""
disabled>
@@ -44,6 +44,14 @@
</li>
</ul>
</div>
+ <div class="btn-group mr-1" role="group" aria-label="">
+ <button id="btn-save-data" type="button" class="btn btn-sm btn-secondary"
+ title=""
+ accesskey=""
+ tabindex="0" disabled>
+ <i class="icon-save-data-changes sql-icon-lg" aria-hidden="true"></i>
+ </button>
+ </div>
<div class="btn-group mr-1" role="group" aria-label="">
<button id="btn-find" type="button" class="btn btn-sm btn-secondary" title="{{ _('Find (Ctrl/Cmd+F)') }}">
<i class="fa fa-search sql-icon-lg" aria-hidden="true" tabindex="0"></i>
diff --git a/web/pgadmin/tools/sqleditor/__init__.py b/web/pgadmin/tools/sqleditor/__init__.py
index ac923a55..067bff7b 100644
--- a/web/pgadmin/tools/sqleditor/__init__.py
+++ b/web/pgadmin/tools/sqleditor/__init__.py
@@ -385,6 +385,8 @@ def poll(trans_id):
rset = None
has_oids = False
oids = None
+ additional_messages = None
+ notifies = None
# Check the transaction and connection status
status, error_msg, conn, trans_obj, session_obj = \
@@ -423,6 +425,22 @@ def poll(trans_id):
st, result = conn.async_fetchmany_2darray(ON_DEMAND_RECORD_COUNT)
+ # There may be additional messages even if result is present
+ # eg: Function can provide result as well as RAISE messages
+ messages = conn.messages()
+ if messages:
+ additional_messages = ''.join(messages)
+ notifies = conn.get_notifies()
+
+ # Procedure/Function output may comes in the form of Notices
+ # from the database server, so we need to append those outputs
+ # with the original result.
+ if result is None:
+ result = conn.status_message()
+ if (result != 'SELECT 1' or result != 'SELECT 0') and \
+ result is not None and additional_messages:
+ result = additional_messages + result
+
if st:
if 'primary_keys' in session_obj:
primary_keys = session_obj['primary_keys']
@@ -439,10 +457,22 @@ def poll(trans_id):
)
session_obj['client_primary_key'] = client_primary_key
- if columns_info is not None:
+ # If trans_obj is a QueryToolCommand then check for updatable
+ # resultsets and primary keys
+ if isinstance(trans_obj, QueryToolCommand):
+ trans_obj.check_updatable_results_pkeys()
+ pk_names, primary_keys = trans_obj.get_primary_keys()
+ # If primary_keys exist, add them to the session_obj to
+ # allow for saving any changes to the data
+ if primary_keys is not None:
+ session_obj['primary_keys'] = primary_keys
- command_obj = pickle.loads(session_obj['command_obj'])
- if hasattr(command_obj, 'obj_id'):
+ if columns_info is not None:
+ # If it is a QueryToolCommand that has obj_id attribute
+ # then it should also be editable
+ if hasattr(trans_obj, 'obj_id') and \
+ (not isinstance(trans_obj, QueryToolCommand) or
+ trans_obj.can_edit()):
# Get the template path for the column
template_path = 'columns/sql/#{0}#'.format(
conn.manager.version
@@ -450,7 +480,7 @@ def poll(trans_id):
SQL = render_template(
"/".join([template_path, 'nodes.sql']),
- tid=command_obj.obj_id,
+ tid=trans_obj.obj_id,
has_oids=True
)
# rows with attribute not_null
@@ -525,26 +555,8 @@ def poll(trans_id):
status = 'NotConnected'
result = error_msg
- # There may be additional messages even if result is present
- # eg: Function can provide result as well as RAISE messages
- additional_messages = None
- notifies = None
- if status == 'Success':
- messages = conn.messages()
- if messages:
- additional_messages = ''.join(messages)
- notifies = conn.get_notifies()
-
- # Procedure/Function output may comes in the form of Notices from the
- # database server, so we need to append those outputs with the
- # original result.
- if status == 'Success' and result is None:
- result = conn.status_message()
- if (result != 'SELECT 1' or result != 'SELECT 0') and \
- result is not None and additional_messages:
- result = additional_messages + result
-
transaction_status = conn.transaction_status()
+
return make_json_response(
data={
'status': status, 'result': result,
@@ -733,7 +745,8 @@ def save(trans_id):
trans_obj is not None and session_obj is not None:
# If there is no primary key found then return from the function.
- if (len(session_obj['primary_keys']) <= 0 or
+ if ('primary_keys' not in session_obj or
+ len(session_obj['primary_keys']) <= 0 or
len(changed_data) <= 0) and \
'has_oids' not in session_obj:
return make_json_response(
@@ -746,32 +759,38 @@ def save(trans_id):
manager = get_driver(
PG_DEFAULT_DRIVER).connection_manager(trans_obj.sid)
- default_conn = manager.connection(did=trans_obj.did)
+ if hasattr(trans_obj, 'conn_id'):
+ conn = manager.connection(did=trans_obj.did,
+ conn_id=trans_obj.conn_id)
+ else:
+ conn = manager.connection(did=trans_obj.did) # default connection
# Connect to the Server if not connected.
- if not default_conn.connected():
- status, msg = default_conn.connect()
+ if not conn.connected():
+ status, msg = conn.connect()
if not status:
return make_json_response(
data={'status': status, 'result': u"{}".format(msg)}
)
-
- status, res, query_res, _rowid = trans_obj.save(
+ status, res, query_res, _rowid, is_commit_required = trans_obj.save(
changed_data,
session_obj['columns_info'],
session_obj['client_primary_key'],
- default_conn)
+ conn)
else:
status = False
res = error_msg
query_res = None
+ _rowid = None
+ is_commit_required = None
return make_json_response(
data={
'status': status,
'result': res,
'query_result': query_res,
- '_rowid': _rowid
+ '_rowid': _rowid,
+ 'is_commit_required': is_commit_required
}
)
diff --git a/web/pgadmin/tools/sqleditor/command.py b/web/pgadmin/tools/sqleditor/command.py
index a06c2c7f..08f02310 100644
--- a/web/pgadmin/tools/sqleditor/command.py
+++ b/web/pgadmin/tools/sqleditor/command.py
@@ -19,6 +19,10 @@ from flask import render_template
from flask_babelex import gettext
from pgadmin.utils.ajax import forbidden
from pgadmin.utils.driver import get_driver
+from pgadmin.tools.sqleditor.utils.constant_definition import ASYNC_OK
+from pgadmin.tools.sqleditor.utils.is_query_resultset_updatable \
+ import is_query_resultset_updatable
+from pgadmin.tools.sqleditor.utils.save_changed_data import save_changed_data
from config import PG_DEFAULT_DRIVER
@@ -668,269 +672,11 @@ class TableCommand(GridCommand):
else:
conn = default_conn
- status = False
- res = None
- query_res = dict()
- count = 0
- list_of_rowid = []
- operations = ('added', 'updated', 'deleted')
- list_of_sql = {}
- _rowid = None
-
- pgadmin_alias = {
- col_name: col_info['pgadmin_alias']
- for col_name, col_info in columns_info
- .items()
- }
- if conn.connected():
-
- # Start the transaction
- conn.execute_void('BEGIN;')
-
- # Iterate total number of records to be updated/inserted
- for of_type in changed_data:
- # No need to go further if its not add/update/delete operation
- if of_type not in operations:
- continue
- # if no data to be save then continue
- if len(changed_data[of_type]) < 1:
- continue
-
- column_type = {}
- column_data = {}
- for each_col in columns_info:
- if (
- columns_info[each_col]['not_null'] and
- not columns_info[each_col]['has_default_val']
- ):
- column_data[each_col] = None
- column_type[each_col] =\
- columns_info[each_col]['type_name']
- else:
- column_type[each_col] = \
- columns_info[each_col]['type_name']
-
- # For newly added rows
- if of_type == 'added':
- # Python dict does not honour the inserted item order
- # So to insert data in the order, we need to make ordered
- # list of added index We don't need this mechanism in
- # updated/deleted rows as it does not matter in
- # those operations
- added_index = OrderedDict(
- sorted(
- changed_data['added_index'].items(),
- key=lambda x: int(x[0])
- )
- )
- list_of_sql[of_type] = []
-
- # When new rows are added, only changed columns data is
- # sent from client side. But if column is not_null and has
- # no_default_value, set column to blank, instead
- # of not null which is set by default.
- column_data = {}
- pk_names, primary_keys = self.get_primary_keys()
- has_oids = 'oid' in column_type
-
- for each_row in added_index:
- # Get the row index to match with the added rows
- # dict key
- tmp_row_index = added_index[each_row]
- data = changed_data[of_type][tmp_row_index]['data']
- # Remove our unique tracking key
- data.pop(client_primary_key, None)
- data.pop('is_row_copied', None)
- list_of_rowid.append(data.get(client_primary_key))
-
- # Update columns value with columns having
- # not_null=False and has no default value
- column_data.update(data)
-
- sql = render_template(
- "/".join([self.sql_path, 'insert.sql']),
- data_to_be_saved=column_data,
- pgadmin_alias=pgadmin_alias,
- primary_keys=None,
- object_name=self.object_name,
- nsp_name=self.nsp_name,
- data_type=column_type,
- pk_names=pk_names,
- has_oids=has_oids
- )
-
- select_sql = render_template(
- "/".join([self.sql_path, 'select.sql']),
- object_name=self.object_name,
- nsp_name=self.nsp_name,
- primary_keys=primary_keys,
- has_oids=has_oids
- )
-
- list_of_sql[of_type].append({
- 'sql': sql, 'data': data,
- 'client_row': tmp_row_index,
- 'select_sql': select_sql
- })
- # Reset column data
- column_data = {}
-
- # For updated rows
- elif of_type == 'updated':
- list_of_sql[of_type] = []
- for each_row in changed_data[of_type]:
- data = changed_data[of_type][each_row]['data']
- pk_escaped = {
- pk: pk_val.replace('%', '%%') if hasattr(
- pk_val, 'replace') else pk_val
- for pk, pk_val in
- changed_data[of_type][each_row]['primary_keys']
- .items()
- }
- sql = render_template(
- "/".join([self.sql_path, 'update.sql']),
- data_to_be_saved=data,
- pgadmin_alias=pgadmin_alias,
- primary_keys=pk_escaped,
- object_name=self.object_name,
- nsp_name=self.nsp_name,
- data_type=column_type
- )
- list_of_sql[of_type].append({'sql': sql, 'data': data})
- list_of_rowid.append(data.get(client_primary_key))
-
- # For deleted rows
- elif of_type == 'deleted':
- list_of_sql[of_type] = []
- is_first = True
- rows_to_delete = []
- keys = None
- no_of_keys = None
- for each_row in changed_data[of_type]:
- 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 = 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
- try:
- row[changed_data['columns']
- [int(k)]['name']] = v
- except ValueError:
- continue
- del row[k]
-
- sql = render_template(
- "/".join([self.sql_path, 'delete.sql']),
- data=rows_to_delete,
- primary_key_labels=keys,
- no_of_keys=no_of_keys,
- object_name=self.object_name,
- nsp_name=self.nsp_name
- )
- list_of_sql[of_type].append({'sql': sql, 'data': {}})
-
- for opr, sqls in list_of_sql.items():
- for item in sqls:
- if item['sql']:
- item['data'] = {
- pgadmin_alias[k] if k in pgadmin_alias else k: v
- for k, v in item['data'].items()
- }
-
- row_added = None
-
- def failure_handle():
- conn.execute_void('ROLLBACK;')
- # If we roll backed every thing then update the
- # message for each sql query.
- for val in query_res:
- if query_res[val]['status']:
- query_res[val]['result'] = \
- 'Transaction ROLLBACK'
-
- # If list is empty set rowid to 1
- try:
- if list_of_rowid:
- _rowid = list_of_rowid[count]
- else:
- _rowid = 1
- except Exception:
- _rowid = 0
-
- return status, res, query_res, _rowid
-
- try:
- # Fetch oids/primary keys
- if 'select_sql' in item and item['select_sql']:
- status, res = conn.execute_dict(
- item['sql'], item['data'])
- else:
- status, res = conn.execute_void(
- item['sql'], item['data'])
- except Exception as _:
- failure_handle()
- raise
-
- if not status:
- return failure_handle()
-
- # Select added row from the table
- if 'select_sql' in item:
- status, sel_res = conn.execute_dict(
- item['select_sql'], res['rows'][0])
-
- if not status:
- conn.execute_void('ROLLBACK;')
- # If we roll backed every thing then update
- # the message for each sql query.
- for val in query_res:
- if query_res[val]['status']:
- query_res[val]['result'] = \
- 'Transaction ROLLBACK'
-
- # If list is empty set rowid to 1
- try:
- if list_of_rowid:
- _rowid = list_of_rowid[count]
- else:
- _rowid = 1
- except Exception:
- _rowid = 0
-
- return status, sel_res, query_res, _rowid
-
- if 'rows' in sel_res and len(sel_res['rows']) > 0:
- row_added = {
- item['client_row']: sel_res['rows'][0]}
-
- rows_affected = conn.rows_affected()
-
- # store the result of each query in dictionary
- query_res[count] = {
- 'status': status,
- 'result': None if row_added else res,
- 'sql': sql, 'rows_affected': rows_affected,
- 'row_added': row_added
- }
-
- count += 1
-
- # Commit the transaction if there is no error found
- conn.execute_void('COMMIT;')
-
- return status, res, query_res, _rowid
+ return save_changed_data(changed_data=changed_data,
+ columns_info=columns_info,
+ command_obj=self,
+ client_primary_key=client_primary_key,
+ conn=conn)
class ViewCommand(GridCommand):
@@ -1114,18 +860,88 @@ class QueryToolCommand(BaseCommand, FetchedRowTracker):
self.auto_rollback = False
self.auto_commit = True
+ # Attributes needed to be able to edit updatable resultselts
+ self.is_updatable_resultset = False
+ self.primary_keys = None
+ self.pk_names = None
+
def get_sql(self, default_conn=None):
return None
def get_all_columns_with_order(self, default_conn=None):
return None
+ def get_primary_keys(self):
+ return self.pk_names, self.primary_keys
+
def can_edit(self):
- return False
+ return self.is_updatable_resultset
def can_filter(self):
return False
+ def check_updatable_results_pkeys(self):
+ """
+ This function is used to check whether the last successful query
+ produced updatable results and sets the necessary flags and
+ attributes accordingly
+ """
+ # Fetch the connection object
+ driver = get_driver(PG_DEFAULT_DRIVER)
+ manager = driver.connection_manager(self.sid)
+ conn = manager.connection(did=self.did, conn_id=self.conn_id)
+
+ # Check that the query results are ready first
+ status, result = conn.poll(
+ formatted_exception_msg=True, no_result=True)
+ if status != ASYNC_OK:
+ return
+
+ # Get the path to the sql templates
+ sql_path = 'sqleditor/sql/#{0}#'.format(manager.version)
+
+ self.is_updatable_resultset, self.primary_keys, pk_names, table_oid = \
+ is_query_resultset_updatable(conn, sql_path)
+
+ # Create pk_names attribute in the required format
+ if pk_names is not None:
+ self.pk_names = ''
+
+ for pk_name in pk_names:
+ self.pk_names += driver.qtIdent(conn, pk_name) + ','
+
+ if self.pk_names != '':
+ # Remove last character from the string
+ self.pk_names = self.pk_names[:-1]
+
+ # Add attributes required to be able to update table data
+ if self.is_updatable_resultset:
+ self.__set_updatable_results_attrs(sql_path=sql_path,
+ table_oid=table_oid,
+ conn=conn)
+
+ def save(self,
+ changed_data,
+ columns_info,
+ client_primary_key='__temp_PK',
+ default_conn=None):
+ if not self.is_updatable_resultset:
+ return False, gettext('Resultset is not updatable.'), None, None
+ else:
+ 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
+
+ return save_changed_data(changed_data=changed_data,
+ columns_info=columns_info,
+ conn=conn,
+ command_obj=self,
+ client_primary_key=client_primary_key,
+ auto_commit=self.auto_commit)
+
def set_connection_id(self, conn_id):
self.conn_id = conn_id
@@ -1134,3 +950,28 @@ class QueryToolCommand(BaseCommand, FetchedRowTracker):
def set_auto_commit(self, auto_commit):
self.auto_commit = auto_commit
+
+ def __set_updatable_results_attrs(self, sql_path,
+ table_oid, conn):
+ # Set template path for sql scripts and the table object id
+ self.sql_path = sql_path
+ self.obj_id = table_oid
+
+ if conn.connected():
+ # Fetch the Namespace Name and object Name
+ query = render_template(
+ "/".join([self.sql_path, 'objectname.sql']),
+ obj_id=self.obj_id
+ )
+
+ status, result = conn.execute_dict(query)
+ if not status:
+ raise Exception(result)
+
+ self.nsp_name = result['rows'][0]['nspname']
+ self.object_name = result['rows'][0]['relname']
+ else:
+ raise Exception(gettext(
+ 'Not connected to server or connection with the server '
+ 'has been closed.')
+ )
diff --git a/web/pgadmin/tools/sqleditor/static/css/sqleditor.css b/web/pgadmin/tools/sqleditor/static/css/sqleditor.css
index 86d3defc..21448327 100644
--- a/web/pgadmin/tools/sqleditor/static/css/sqleditor.css
+++ b/web/pgadmin/tools/sqleditor/static/css/sqleditor.css
@@ -291,7 +291,7 @@ input.editor-checkbox:focus {
background-image: url('../img/disconnect.svg');
}
-.icon-commit, .icon-rollback {
+.icon-commit, .icon-rollback, .icon-save-data-changes {
display: inline-block;
align-content: center;
vertical-align: middle;
@@ -311,6 +311,10 @@ input.editor-checkbox:focus {
background-image: url('../img/rollback.svg') !important;
}
+.icon-save-data-changes {
+ background-image: url('../img/save_data_changes.svg') !important;
+}
+
.ajs-body .warn-header {
font-size: 13px;
font-weight: bold;
diff --git a/web/pgadmin/tools/sqleditor/static/img/save_data_changes.svg b/web/pgadmin/tools/sqleditor/static/img/save_data_changes.svg
new file mode 100644
index 00000000..09ead928
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/static/img/save_data_changes.svg
@@ -0,0 +1,12 @@
+<svg version="1.1" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" style="enable-background:new 0 0 1792 1792;" xml:space="preserve" width="1792" height="1792"><rect id="backgroundrect" width="100%" height="100%" x="0" y="0" fill="none" stroke="none"/>
+<style type="text/css">
+ .st0{fill:#222222;}
+ .st1{fill:#FFFFFF;}
+</style>
+<title>save_data_changes</title>
+
+
+<g class="currentLayer" style=""><title>Layer 1</title><path class="st0 selected" d="M614.0211181640625,1381.039882183075 v-170.18182373046875 c0,-8.243182182312012 -2.6703295707702637,-15.068181991577148 -8.01098918914795,-20.386363983154297 s-12.19450569152832,-7.9772725105285645 -20.4725284576416,-7.9772725105285645 H300.70343017578125 c-8.278021812438965,0 -15.131868362426758,2.659090995788574 -20.4725284576416,7.9772725105285645 s-8.01098918914795,12.143181800842285 -8.01098918914795,20.386363983154297 v170.18182373046875 c0,8.243182182312012 2.6703295707702637,15.068181991577148 8.01098918914795,20.386363983154297 s12.19450569152832,7.9772725105285645 20.4725284576416,7.9772725105285645 h284.8351745605469 c8.278021812438965,0 15.131868362426758,-2.659090995788574 20.4725284576416,-7.9772725105285645 S614.0211181640625,1389.2831683158875 614.0211181640625,1381.039882183075 zM614.0211181640625,1040.6748919487 V870.49276304245 c0,-8.243182182312012 -2.6703295707702637,-15.068181991577148 -8.01098918914795,-20.386363983154297 s-12.19450569152832,-7.9772725105285645 -20.4725284576416,-7.9772725105285645 H300.70343017578125 c-8.278021812438965,0 -15.131868362426758,2.659090995788574 -20.4725284576416,7.9772725105285645 s-8.01098918914795,12.143181800842285 -8.01098918914795,20.386363983154297 v170.18182373046875 c0,8.243182182312012 2.6703295707702637,15.068181991577148 8.01098918914795,20.386363983154297 s12.19450569152832,7.9772725105285645 20.4725284576416,7.9772725105285645 h284.8351745605469 c8.278021812438965,0 15.131868362426758,-2.659090995788574 20.4725284576416,-7.9772725105285645 S614.0211181640625,1048.918300151825 614.0211181640625,1040.6748919487 zM1069.7584228515625,1381.039882183075 v-170.18182373046875 c0,-8.243182182312012 -2.6703295707702637,-15.068181991577148 -8.01098918914795,-20.386363983154297 s-12.19450569152832,-7.9772725105285645 -20.4725284576416,-7.9772725105285645 H756.438720703125 c-8.278021812438965,0 -15.131868362426758,2.659090995788574 -20.4725284576416,7.9772725105285645 s-8.01098918914795,12.143181800842285 -8.01098918914795,20.386363983154297 v170.18182373046875 c0,8.243182182312012 2.6703295707702637,15.068181991577148 8.01098918914795,20.386363983154297 s12.19450569152832,7.9772725105285645 20.4725284576416,7.9772725105285645 h284.8351745605469 c8.278021812438965,0 15.131868362426758,-2.659090995788574 20.4725284576416,-7.9772725105285645 S1069.7584228515625,1389.2831683158875 1069.7584228515625,1381.039882183075 zM614.0211181640625,700.3109393119812 V530.1298480033875 c0,-8.243182182312012 -2.6703295707702637,-15.068181991577148 -8.01098918914795,-20.386363983154297 s-12.19450569152832,-7.9772725105285645 -20.4725284576416,-7.9772725105285645 H300.70343017578125 c-8.278021812438965,0 -15.131868362426758,2.659090995788574 -20.4725284576416,7.9772725105285645 s-8.01098918914795,12.143181800842285 -8.01098918914795,20.386363983154297 v170.18182373046875 c0,8.243182182312012 2.6703295707702637,15.068181991577148 8.01098918914795,20.386363983154297 s12.19450569152832,7.9772725105285645 20.4725284576416,7.9772725105285645 h284.8351745605469 c8.278021812438965,0 15.131868362426758,-2.659090995788574 20.4725284576416,-7.9772725105285645 S614.0211181640625,708.5541033744812 614.0211181640625,700.3109393119812 zM1069.7584228515625,1040.6748919487 V870.49276304245 c0,-8.243182182312012 -2.6703295707702637,-15.068181991577148 -8.01098918914795,-20.386363983154297 s-12.19450569152832,-7.9772725105285645 -20.4725284576416,-7.9772725105285645 H756.438720703125 c-8.278021812438965,0 -15.131868362426758,2.659090995788574 -20.4725284576416,7.9772725105285645 s-8.01098918914795,12.143181800842285 -8.01098918914795,20.386363983154297 v170.18182373046875 c0,8.243182182312012 2.6703295707702637,15.068181991577148 8.01098918914795,20.386363983154297 s12.19450569152832,7.9772725105285645 20.4725284576416,7.9772725105285645 h284.8351745605469 c8.278021812438965,0 15.131868362426758,-2.659090995788574 20.4725284576416,-7.9772725105285645 S1069.7584228515625,1048.918300151825 1069.7584228515625,1040.6748919487 zM1525.49560546875,1381.039882183075 v-170.18182373046875 c0,-8.243182182312012 -2.6703295707702637,-15.068181991577148 -8.01098918914795,-20.386363983154297 s-12.19450569152832,-7.9772725105285645 -20.4725284576416,-7.9772725105285645 h-284.8351745605469 c-8.278021812438965,0 -15.131868362426758,2.659090995788574 -20.4725284576416,7.9772725105285645 s-8.01098918914795,12.143181800842285 -8.01098918914795,20.386363983154297 v170.18182373046875 c0,8.243182182312012 2.6703295707702637,15.068181991577148 8.01098918914795,20.386363983154297 s12.19450569152832,7.9772725105285645 20.4725284576416,7.9772725105285645 h284.8351745605469 c8.278021812438965,0 15.131868362426758,-2.659090995788574 20.4725284576416,-7.9772725105285645 S1525.49560546875,1389.2831683158875 1525.49560546875,1381.039882183075 zM1069.7584228515625,700.3109393119812 V530.1298480033875 c0,-8.243182182312012 -2.6703295707702637,-15.068181991577148 -8.01098918914795,-20.386363983154297 s-12.19450569152832,-7.9772725105285645 -20.4725284576416,-7.9772725105285645 H756.438720703125 c-8.278021812438965,0 -15.131868362426758,2.659090995788574 -20.4725284576416,7.9772725105285645 s-8.01098918914795,12.143181800842285 -8.01098918914795,20.386363983154297 v170.18182373046875 c0,8.243182182312012 2.6703295707702637,15.068181991577148 8.01098918914795,20.386363983154297 s12.19450569152832,7.9772725105285645 20.4725284576416,7.9772725105285645 h284.8351745605469 c8.278021812438965,0 15.131868362426758,-2.659090995788574 20.4725284576416,-7.9772725105285645 S1069.7584228515625,708.5541033744812 1069.7584228515625,700.3109393119812 zM1525.49560546875,1040.6748919487 V870.49276304245 c0,-8.243182182312012 -2.6703295707702637,-15.068181991577148 -8.01098918914795,-20.386363983154297 s-12.19450569152832,-7.9772725105285645 -20.4725284576416,-7.9772725105285645 h-284.8351745605469 c-8.278021812438965,0 -15.131868362426758,2.659090995788574 -20.4725284576416,7.9772725105285645 s-8.01098918914795,12.143181800842285 -8.01098918914795,20.386363983154297 v170.18182373046875 c0,8.243182182312012 2.6703295707702637,15.068181991577148 8.01098918914795,20.386363983154297 s12.19450569152832,7.9772725105285645 20.4725284576416,7.9772725105285645 h284.8351745605469 c8.278021812438965,0 15.131868362426758,-2.659090995788574 20.4725284576416,-7.9772725105285645 S1525.49560546875,1048.918300151825 1525.49560546875,1040.6748919487 zM1525.49560546875,700.3109393119812 V530.1298480033875 c0,-8.243182182312012 -2.6703295707702637,-15.068181991577148 -8.01098918914795,-20.386363983154297 s-12.19450569152832,-7.9772725105285645 -20.4725284576416,-7.9772725105285645 h-284.8351745605469 c-8.278021812438965,0 -15.131868362426758,2.659090995788574 -20.4725284576416,7.9772725105285645 s-8.01098918914795,12.143181800842285 -8.01098918914795,20.386363983154297 v170.18182373046875 c0,8.243182182312012 2.6703295707702637,15.068181991577148 8.01098918914795,20.386363983154297 s12.19450569152832,7.9772725105285645 20.4725284576416,7.9772725105285645 h284.8351745605469 c8.278021812438965,0 15.131868362426758,-2.659090995788574 20.4725284576416,-7.9772725105285645 S1525.49560546875,708.5541033744812 1525.49560546875,700.3109393119812 zM1639.4296875,416.6754412651062 v964.3636474609375 c0,39 -13.974724769592285,72.41590881347656 -41.835166931152344,100.15908813476562 s-61.41758346557617,41.65909194946289 -100.5824203491211,41.65909194946289 H300.70343017578125 c-39.16483688354492,0 -72.72197723388672,-13.915908813476562 -100.5824203491211,-41.65909194946289 s-41.835166931152344,-61.15909194946289 -41.835166931152344,-100.15908813476562 V416.6754412651062 c0,-39 13.974724769592285,-72.41590881347656 41.835166931152344,-100.15908813476562 s61.41758346557617,-41.65909194946289 100.5824203491211,-41.65909194946289 h1196.3077392578125 c39.16483688354492,0 72.72197723388672,13.915908813476562 100.5824203491211,41.65909194946289 S1639.4296875,377.6754412651062 1639.4296875,416.6754412651062 z" id="svg_1"/><g id="svg_2" class="selected">
+ <path class="st0" d="M898.8562622070312,1342.7491106987 c-22.69780158996582,0 -43.081321716308594,-8.331817626953125 -58.92527389526367,-24.19772720336914 l-289.4637451171875,-288.6886291503906 c-16.378021240234375,-15.51136302947998 -25.012088775634766,-35.897727966308594 -25.012088775634766,-58.943180084228516 c0,-22.868181228637695 8.278021812438965,-42.72272872924805 24.656044006347656,-59.03181457519531 l32.84505844116211,-33.150001525878906 c0.17802198231220245,-0.1772727221250534 0.44505494832992554,-0.4431818127632141 0.6230769157409668,-0.6204545497894287 c16.46703338623047,-15.51136302947998 36.761539459228516,-23.665908813476562 58.83626174926758,-23.665908813476562 c22.69780158996582,0 43.081321716308594,8.331817626953125 58.92527389526367,24.19772720336914 l85.27252960205078,84.91363525390625 V715.5564227104187 c0,-22.247726440429688 8.545055389404297,-42.54545593261719 24.745054244995117,-58.677268981933594 s36.58351516723633,-24.640907287597656 58.92527389526367,-24.640907287597656 h56.96703338623047 c22.341758728027344,0 42.72527313232422,8.5090913772583 58.92527389526367,24.640907287597656 s24.745054244995117,36.429542541503906 24.745054244995117,58.677268981933594 v247.8272705078125 l85.27252960205078,-84.91363525390625 c15.932967185974121,-15.8659086227417 36.227474212646484,-24.19772720336914 58.92527389526367,-24.19772720336914 c22.074724197387695,0 42.45824432373047,8.154544830322266 58.83626174926758,23.665908813476562 c0.17802198231220245,0.1772727221250534 0.3560439646244049,0.3545454442501068 0.5340659618377686,0.5318182110786438 l33.379119873046875,33.2386360168457 c0.17802198231220245,0.1772727221250534 0.3560439646244049,0.3545454442501068 0.5340659618377686,0.5318182110786438 c15.576923370361328,16.397727966308594 23.765933990478516,36.606815338134766 23.765933990478516,58.5886344909668 c0,22.602272033691406 -8.367033004760742,42.900001525878906 -24.299999237060547,58.677268981933594 l-289.73077392578125,288.95452880859375 c-0.17802198231220245,0.1772727221250534 -0.3560439646244049,0.3545454442501068 -0.5340659618377686,0.5318182110786438 C941.3145141601562,1334.5058245658875 920.9309692382812,1342.7491106987 898.8562622070312,1342.7491106987 z" id="svg_3"/>
+ <path class="st1" d="M927.3397827148438,658.8291277885437 c15.398900985717773,0 28.75054931640625,5.5840911865234375 40.05494689941406,16.84090805053711 s16.91208839416504,24.55227279663086 16.91208839416504,39.8863639831543 v312 l130.84616088867188,-130.2954559326172 c10.948351860046387,-10.902273178100586 24.299999237060547,-16.397727966308594 40.05494689941406,-16.397727966308594 c15.398900985717773,0 28.928571701049805,5.49545431137085 40.5,16.397727966308594 l33.379119873046875,33.2386360168457 c10.948351860046387,11.522727012634277 16.46703338623047,24.995454788208008 16.46703338623047,40.32954406738281 c0,15.688636779785156 -5.518681049346924,28.98409080505371 -16.46703338623047,39.8863639831543 l-289.73077392578125,288.95452880859375 c-11.571428298950195,10.902273178100586 -25.101099014282227,16.397727966308594 -40.5,16.397727966308594 c-15.754945755004883,0 -29.10659408569336,-5.49545431137085 -40.05494689941406,-16.397727966308594 l-289.73077392578125,-288.95452880859375 c-11.30439567565918,-10.636363983154297 -16.91208839416504,-23.93181800842285 -16.91208839416504,-39.8863639831543 c0,-15.688636779785156 5.607692718505859,-29.072725296020508 16.91208839416504,-40.32954406738281 l32.93406677246094,-33.2386360168457 c11.571428298950195,-10.902273178100586 25.101099014282227,-16.397727966308594 40.5,-16.397727966308594 c15.754945755004883,0 29.10659408569336,5.49545431137085 40.05494689941406,16.397727966308594 l130.84616088867188,130.2954559326172 v-312 c0,-15.334090232849121 5.607692718505859,-28.629545211791992 16.91208839416504,-39.8863639831543 s24.656044006347656,-16.84090805053711 40.05494689941406,-16.84090805053711 L927.3397827148438,658.8291277885437 M927.3397827148438,605.647304058075 h-56.96703338623047 c-29.64065933227539,0 -56.52198028564453,11.168182373046875 -77.79560852050781,32.4409065246582 c-21.36263656616211,21.272727966308594 -32.57802200317383,48.04090881347656 -32.57802200317383,77.46818542480469 v183.56591796875 l-39.69889831542969,-39.53181457519531 c-21.006593704223633,-20.918182373046875 -47.88791275024414,-31.997726440429688 -77.79560852050781,-31.997726440429688 c-29.017581939697266,0 -55.72087860107422,10.725000381469727 -77.17252349853516,30.934091567993164 c-0.44505494832992554,0.4431818127632141 -0.8901098966598511,0.8863636255264282 -1.3351647853851318,1.2409090995788574 l-32.84505844116211,33.150001525878906 c-21.184614181518555,21.18408966064453 -32.400001525878906,48.04090881347656 -32.400001525878906,77.8227310180664 c0,30.402271270751953 11.393406867980957,57.259090423583984 33.023075103759766,77.91136169433594 l289.28570556640625,288.5113525390625 c21.006593704223633,20.918182373046875 47.976924896240234,31.997726440429688 77.88461303710938,31.997726440429688 c29.017581939697266,0 55.72087860107422,-10.725000381469727 77.17252349853516,-30.934091567993164 c0.3560439646244049,-0.3545454442501068 0.7120879292488098,-0.7090908885002136 1.068131923675537,-1.0636364221572876 l289.73077392578125,-288.95452880859375 c21.006593704223633,-20.918182373046875 32.0439567565918,-47.686363220214844 32.0439567565918,-77.46818542480469 c0,-28.89545249938965 -10.770330429077148,-55.48636245727539 -31.064836502075195,-76.84772491455078 c-0.3560439646244049,-0.3545454442501068 -0.7120879292488098,-0.7090908885002136 -1.068131923675537,-1.0636364221572876 l-33.379119873046875,-33.2386360168457 c-0.3560439646244049,-0.3545454442501068 -0.7120879292488098,-0.7090908885002136 -1.068131923675537,-1.0636364221572876 c-21.451648712158203,-20.209089279174805 -48.154945373535156,-30.934091567993164 -77.17252349853516,-30.934091567993164 c-29.907691955566406,0 -56.78900909423828,11.079545021057129 -77.79560852050781,31.997726440429688 l-39.69889831542969,39.53181457519531 V715.5564227104187 c0,-29.515907287597656 -11.215385437011719,-56.28409194946289 -32.57802200317383,-77.46818542480469 C983.8617553710938,616.904139995575 956.98046875,605.647304058075 927.3397827148438,605.647304058075 L927.3397827148438,605.647304058075 z" id="svg_4"/>
+</g></g></svg>
\ No newline at end of file
diff --git a/web/pgadmin/tools/sqleditor/static/js/sqleditor.js b/web/pgadmin/tools/sqleditor/static/js/sqleditor.js
index 8bf041f6..f8110ed6 100644
--- a/web/pgadmin/tools/sqleditor/static/js/sqleditor.js
+++ b/web/pgadmin/tools/sqleditor/static/js/sqleditor.js
@@ -86,8 +86,8 @@ define('tools.querytool', [
// Bind all the events
events: {
'click .btn-load-file': 'on_file_load',
- 'click #btn-save': 'on_save',
- 'click #btn-file-menu-save': 'on_save',
+ 'click #btn-save-file': 'on_save_file',
+ 'click #btn-file-menu-save': 'on_save_file',
'click #btn-file-menu-save-as': 'on_save_as',
'click #btn-find': 'on_find',
'click #btn-find-menu-find': 'on_find',
@@ -98,6 +98,7 @@ define('tools.querytool', [
'click #btn-find-menu-find-persistent': 'on_find_persistent',
'click #btn-find-menu-jump': 'on_jump',
'click #btn-delete-row': 'on_delete',
+ 'click #btn-save-data': 'on_save_data',
'click #btn-filter': 'on_show_filter',
'click #btn-filter-menu': 'on_show_filter',
'click #btn-include-filter': 'on_include_filter',
@@ -375,26 +376,7 @@ define('tools.querytool', [
_.each(window.top.pgAdmin.Browser.docker.findPanels('frm_datagrid'), function(p) {
if (p.isVisible()) {
p.on(wcDocker.EVENT.CLOSING, function() {
- // Only if we can edit data then perform this check
- var notify = false,
- msg;
- if (self.handler.can_edit
- && self.preferences.prompt_save_data_changes) {
- var data_store = self.handler.data_store;
- if (data_store && (_.size(data_store.added) ||
- _.size(data_store.updated))) {
- msg = gettext('The data has changed. Do you want to save changes?');
- notify = true;
- }
- } else if (self.handler.is_query_tool && self.handler.is_query_changed
- && self.preferences.prompt_save_query_changes) {
- msg = gettext('The text has changed. Do you want to save changes?');
- notify = true;
- }
- if (notify) {
- return self.user_confirmation(p, msg);
- }
- return true;
+ return self.handler.check_needed_confirmations_before_closing_panel(true);
});
// Set focus on query tool of active panel
@@ -638,62 +620,6 @@ define('tools.querytool', [
}
},
- /* To prompt user for unsaved changes */
- user_confirmation: function(panel, msg) {
- // If there is anything to save then prompt user
- var that = this;
-
- alertify.confirmSave || alertify.dialog('confirmSave', function() {
- return {
- main: function(title, message) {
- this.setHeader(title);
- this.setContent(message);
- },
- setup: function() {
- return {
- buttons: [{
- text: gettext('Cancel'),
- key: 27, // ESC
- invokeOnClose: true,
- className: 'btn btn-secondary fa fa-lg fa-times pg-alertify-button',
- }, {
- text: gettext('Don\'t save'),
- className: 'btn btn-secondary fa fa-lg fa-trash-o pg-alertify-button',
- }, {
- text: gettext('Save'),
- className: 'btn btn-primary fa fa-lg fa-save pg-alertify-button',
- }],
- focus: {
- element: 0,
- select: false,
- },
- options: {
- maximizable: false,
- resizable: false,
- },
- };
- },
- callback: function(closeEvent) {
- switch (closeEvent.index) {
- case 0: // Cancel
- //Do nothing.
- break;
- case 1: // Don't Save
- that.handler.close_on_save = false;
- that.handler.close();
- break;
- case 2: //Save
- that.handler.close_on_save = true;
- that.handler._save(that, that.handler);
- break;
- }
- },
- };
- });
- alertify.confirmSave(gettext('Save changes?'), msg);
- return false;
- },
-
/* Regarding SlickGrid usage in render_grid function.
SlickGrid Plugins:
@@ -757,16 +683,16 @@ define('tools.querytool', [
render_grid: function(collection, columns, is_editable, client_primary_key, rows_affected) {
var self = this;
- // This will work as data store and holds all the
- // inserted/updated/deleted data from grid
- self.handler.data_store = {
- updated: {},
- added: {},
- staged_rows: {},
- deleted: {},
- updated_index: {},
- added_index: {},
- };
+ self.handler.numberOfModifiedCells = 0;
+
+ self.handler.reset_data_store();
+
+ // keep track of newly added rows
+ self.handler.rows_to_disable = new Array();
+ // Temporarily hold new rows added
+ self.handler.temp_new_rows = new Array();
+ self.handler.has_more_rows = false;
+ self.handler.fetching_rows = false;
// To store primary keys before they gets changed
self.handler.primary_keys_data = {};
@@ -873,7 +799,7 @@ define('tools.querytool', [
}
var grid_options = {
- editable: true,
+ editable: is_editable,
enableAddRow: is_editable,
enableCellNavigation: true,
enableColumnReorder: false,
@@ -1090,6 +1016,14 @@ define('tools.querytool', [
_pk = args.item[self.client_primary_key] || null, // Unique key to identify row
column_data = {};
+ // Highlight the changed cell
+ self.handler.numberOfModifiedCells++;
+ args.grid.addCellCssStyles(self.handler.numberOfModifiedCells, {
+ [args.row] : {
+ [changed_column]: 'highlighted_grid_cells',
+ },
+ });
+
// Access to row/cell value after a cell is changed.
// The purpose is to remove row_id from temp_new_row
// if new row has primary key instead of [default_value]
@@ -1145,7 +1079,7 @@ define('tools.querytool', [
}
}
// Enable save button
- $('#btn-save').prop('disabled', false);
+ $('#btn-save-data').prop('disabled', false);
}.bind(editor_data));
// Listener function which will be called when user adds new rows
@@ -1173,6 +1107,7 @@ define('tools.querytool', [
'data': item,
};
self.handler.data_store.added_index[data_length] = _key;
+
// Fetch data type & add it for the column
var temp = {};
temp[column.name] = _.where(this.columns, {
@@ -1181,8 +1116,17 @@ define('tools.querytool', [
grid.updateRowCount();
grid.render();
+ // Highlight the first added cell of the new row
+ var row = dataView.getRowByItem(item);
+ self.handler.numberOfModifiedCells++;
+ args.grid.addCellCssStyles(self.handler.numberOfModifiedCells, {
+ [row] : {
+ [column.field]: 'highlighted_grid_cells',
+ },
+ });
+
// Enable save button
- $('#btn-save').prop('disabled', false);
+ $('#btn-save-data').prop('disabled', false);
}.bind(editor_data));
// Listen grid viewportChanged event to load next chunk of data.
@@ -1231,9 +1175,11 @@ define('tools.querytool', [
}
dataView.setItems(collection, self.client_primary_key);
},
+
fetch_next_all: function(cb) {
this.fetch_next(true, cb);
},
+
fetch_next: function(fetch_all, cb) {
var self = this,
url = '';
@@ -1433,7 +1379,7 @@ define('tools.querytool', [
},
// Callback function for Save button click.
- on_save: function(ev) {
+ on_save_file: function(ev) {
var self = this;
this._stopEventPropogation(ev);
@@ -1442,9 +1388,7 @@ define('tools.querytool', [
self.handler.close_on_save = false;
// Trigger the save signal to the SqlEditorController class
self.handler.trigger(
- 'pgadmin-sqleditor:button:save',
- self,
- self.handler
+ 'pgadmin-sqleditor:button:save_file'
);
},
@@ -1458,7 +1402,7 @@ define('tools.querytool', [
self.handler.close_on_save = false;
// Trigger the save signal to the SqlEditorController class
self.handler.trigger(
- 'pgadmin-sqleditor:button:save',
+ 'pgadmin-sqleditor:button:save_file',
self,
self.handler,
true
@@ -1631,6 +1575,11 @@ define('tools.querytool', [
);
},
+ // Callback function for Save Data Changes button click.
+ on_save_data: function() {
+ queryToolActions.saveDataChanges(this.handler);
+ },
+
// Callback function for the flash button click.
on_flash: function() {
queryToolActions.executeQuery(this.handler);
@@ -2217,7 +2166,7 @@ define('tools.querytool', [
// Listen on events come from SQLEditorView for the button clicked.
self.on('pgadmin-sqleditor:button:load_file', self._load_file, self);
- self.on('pgadmin-sqleditor:button:save', self._save, self);
+ self.on('pgadmin-sqleditor:button:save_file', self._save_file, self);
self.on('pgadmin-sqleditor:button:deleterow', self._delete, self);
self.on('pgadmin-sqleditor:button:show_filter', self._show_filter, self);
self.on('pgadmin-sqleditor:button:include_filter', self._include_filter, self);
@@ -2278,12 +2227,6 @@ define('tools.querytool', [
self.query_start_time = new Date();
self.rows_affected = 0;
self._init_polling_flags();
- // keep track of newly added rows
- self.rows_to_disable = new Array();
- // Temporarily hold new rows added
- self.temp_new_rows = new Array();
- self.has_more_rows = false;
- self.fetching_rows = false;
self.trigger(
'pgadmin-sqleditor:loading-icon:show',
@@ -2332,8 +2275,7 @@ define('tools.querytool', [
$('#btn-filter').addClass('btn-secondary');
$('#btn-filter-dropdown').addClass('btn-secondary');
}
- $('#btn-save').prop('disabled', true);
- $('#btn-file-menu-dropdown').prop('disabled', true);
+
$('#btn-copy-row').prop('disabled', true);
$('#btn-paste-row').prop('disabled', true);
@@ -2409,13 +2351,12 @@ define('tools.querytool', [
$('#btn-filter-dropdown').prop('disabled', false);
}
+ // No data to save initially
+ $('#btn-save-data').prop('disabled', true);
+
// Initial settings for delete row, copy row and paste row buttons.
$('#btn-delete-row').prop('disabled', true);
- // Do not disable save button in query tool
- if (!self.is_query_tool && !self.can_edit) {
- $('#btn-save').prop('disabled', true);
- $('#btn-file-menu-dropdown').prop('disabled', true);
- }
+
if (!self.can_edit) {
$('#btn-delete-row').prop('disabled', true);
$('#btn-copy-row').prop('disabled', true);
@@ -2799,9 +2740,9 @@ define('tools.querytool', [
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);
+ $('#btn-save-data').prop('disabled', false);
} else {
- $('#btn-save').prop('disabled', true);
+ $('#btn-save-data').prop('disabled', true);
}
alertify.success(gettext('Row(s) deleted.'));
} else {
@@ -2830,41 +2771,42 @@ define('tools.querytool', [
if (_.size(self.data_store.added) || is_updated || _.size(self.data_store.deleted)) {
// Do not disable save button if there are
// any other changes present in grid data
- $('#btn-save').prop('disabled', false);
+ $('#btn-save-data').prop('disabled', false);
} else {
- $('#btn-save').prop('disabled', true);
+ $('#btn-save-data').prop('disabled', true);
}
}
},
+ /// This function will open save file dialog conditionally.
+
+ _save_file: function(save_as=false) {
+ var self = this;
+
+ var current_file = self.gridView.current_file;
+ if (!_.isUndefined(current_file) && !save_as) {
+ self._save_file_handler(current_file);
+ } else {
+ // provide custom option to save file dialog
+ var params = {
+ 'supported_types': ['*', 'sql'],
+ 'dialog_type': 'create_file',
+ 'dialog_title': 'Save File',
+ 'btn_primary': 'Save',
+ };
+ pgAdmin.FileManager.init();
+ pgAdmin.FileManager.show_dialog(params);
+ }
+ },
+
/* This function will fetch the list of changed models and make
* the ajax call to save the data into the database server.
- * and will open save file dialog conditionally.
*/
- _save: function(view, controller, save_as) {
- var self = this,
- save_data = true;
+ save_data: function() {
+ var self = this;
- // Open save file dialog if query tool
- if (self.is_query_tool) {
- var current_file = self.gridView.current_file;
- if (!_.isUndefined(current_file) && !save_as) {
- self._save_file_handler(current_file);
- } else {
- // provide custom option to save file dialog
- var params = {
- 'supported_types': ['*', 'sql'],
- 'dialog_type': 'create_file',
- 'dialog_title': 'Save File',
- 'btn_primary': 'Save',
- };
- pgAdmin.FileManager.init();
- pgAdmin.FileManager.show_dialog(params);
- }
+ if(!self.can_edit)
return;
- }
- $('#btn-save').prop('disabled', true);
- $('#btn-file-menu-dropdown').prop('disabled', true);
var is_added = _.size(self.data_store.added),
is_updated = _.size(self.data_store.updated),
@@ -2874,154 +2816,188 @@ define('tools.querytool', [
return; // Nothing to save here
}
- if (save_data) {
+ self.trigger(
+ 'pgadmin-sqleditor:loading-icon:show',
+ gettext('Saving the updated data...')
+ );
- self.trigger(
- 'pgadmin-sqleditor:loading-icon:show',
- gettext('Saving the updated data...')
- );
+ // Add the columns to the data so the server can remap the data
+ var req_data = self.data_store, view = self.gridView;
+ req_data.columns = view ? view.handler.columns : self.columns;
- // Add the columns to the data so the server can remap the data
- var req_data = self.data_store;
- req_data.columns = view ? view.handler.columns : self.columns;
+ var save_successful = false;
- // Make ajax call to save the data
- $.ajax({
- url: url_for('sqleditor.save', {
- 'trans_id': self.transId,
- }),
- method: 'POST',
- contentType: 'application/json',
- data: JSON.stringify(req_data),
- })
- .done(function(res) {
- var grid = self.slickgrid,
- dataView = grid.getData(),
- data_length = dataView.getLength(),
- data = [];
-
- if (res.data.status) {
- if(is_added) {
- // Update the rows in a grid after addition
- dataView.beginUpdate();
- _.each(res.data.query_result, function(r) {
- if (!_.isNull(r.row_added)) {
- // Fetch temp_id returned by server after addition
- var row_id = Object.keys(r.row_added)[0];
- _.each(req_data.added_index, function(v, k) {
- if (v == row_id) {
- // Fetch item data through row index
- var item = grid.getDataItem(k);
- _.extend(item, r.row_added[row_id]);
- }
- });
- }
- });
- dataView.endUpdate();
- }
- // Remove flag is_row_copied from copied rows
- _.each(data, function(row) {
- if (row.is_row_copied) {
- delete row.is_row_copied;
+ // Make ajax call to save the data
+ $.ajax({
+ url: url_for('sqleditor.save', {
+ 'trans_id': self.transId,
+ }),
+ method: 'POST',
+ contentType: 'application/json',
+ data: JSON.stringify(req_data),
+ })
+ .done(function(res) {
+ var grid = self.slickgrid,
+ dataView = grid.getData(),
+ data_length = dataView.getLength(),
+ data = [];
+
+ if (res.data.status) {
+ // Disable Save Data Changes button
+ $('#btn-save-data').prop('disabled', true);
+
+ save_successful = true;
+
+ // Remove highlighted cells styling
+ for (let i = 1; i <= self.numberOfModifiedCells; i++)
+ grid.removeCellCssStyles(i);
+
+ self.numberOfModifiedCells = 0;
+
+ if(is_added) {
+ // Update the rows in a grid after addition
+ dataView.beginUpdate();
+ _.each(res.data.query_result, function(r) {
+ if (!_.isNull(r.row_added)) {
+ // Fetch temp_id returned by server after addition
+ var row_id = Object.keys(r.row_added)[0];
+ _.each(req_data.added_index, function(v, k) {
+ if (v == row_id) {
+ // Fetch item data through row index
+ var item = grid.getDataItem(k);
+ _.extend(item, r.row_added[row_id]);
+ }
+ });
}
});
-
- // Remove 2d copied_rows array
- if (grid.copied_rows) {
- delete grid.copied_rows;
+ dataView.endUpdate();
+ }
+ // Remove flag is_row_copied from copied rows
+ _.each(data, function(row) {
+ if (row.is_row_copied) {
+ delete row.is_row_copied;
}
+ });
+
+ // Remove 2d copied_rows array
+ if (grid.copied_rows) {
+ delete grid.copied_rows;
+ }
- // Remove deleted rows from client as well
- if (is_deleted) {
- var rows = _.keys(self.data_store.deleted);
- if (data_length == rows.length) {
- // This means all the rows are selected, clear all data
- data = [];
- dataView.setItems(data, self.client_primary_key);
- } else {
- dataView.beginUpdate();
- for (var i = 0; i < rows.length; i++) {
- var item = grid.getDataItem(rows[i]);
- data.push(item);
- dataView.deleteItem(item[self.client_primary_key]);
- }
- dataView.endUpdate();
+ // Remove deleted rows from client as well
+ if (is_deleted) {
+ var rows = _.keys(self.data_store.deleted);
+ if (data_length == rows.length) {
+ // This means all the rows are selected, clear all data
+ data = [];
+ dataView.setItems(data, self.client_primary_key);
+ } else {
+ dataView.beginUpdate();
+ for (var i = 0; i < rows.length; i++) {
+ var item = grid.getDataItem(rows[i]);
+ data.push(item);
+ dataView.deleteItem(item[self.client_primary_key]);
}
- self.rows_to_delete.apply(self, [data]);
- grid.setSelectedRows([]);
+ dataView.endUpdate();
}
-
+ self.rows_to_delete.apply(self, [data]);
grid.setSelectedRows([]);
+ }
- // Reset data store
- self.data_store = {
- 'added': {},
- 'updated': {},
- 'deleted': {},
- 'added_index': {},
- 'updated_index': {},
- };
+ grid.setSelectedRows([]);
- // Reset old primary key data now
- self.primary_keys_data = {};
+ // Reset data store
+ self.reset_data_store();
- // Clear msgs after successful save
- self.set_sql_message('');
+ // Reset old primary key data now
+ self.primary_keys_data = {};
- alertify.success(gettext('Data saved successfully.'));
- } else {
- // Something went wrong while saving data on the db server
- $('#btn-flash').prop('disabled', false);
- $('#btn-download').prop('disabled', false);
- self.set_sql_message(res.data.result);
- var err_msg = S(gettext('%s.')).sprintf(res.data.result).value();
- alertify.error(err_msg, 20);
- grid.setSelectedRows([]);
- // To highlight the row at fault
- if (_.has(res.data, '_rowid') &&
- (!_.isUndefined(res.data._rowid) || !_.isNull(res.data._rowid))) {
- var _row_index = self._find_rowindex(res.data._rowid);
- if (_row_index in self.data_store.added_index) {
- // Remove new row index from temp_list if save operation
- // fails
- var index = self.handler.temp_new_rows.indexOf(res.data._rowid);
- if (index > -1) {
- self.handler.temp_new_rows.splice(index, 1);
- }
- self.data_store.added[self.data_store.added_index[_row_index]].err = true;
- } else if (_row_index in self.data_store.updated_index) {
- self.data_store.updated[self.data_store.updated_index[_row_index]].err = true;
+ // Clear msgs after successful save
+ self.set_sql_message('');
+
+ var msg, is_commit_required = _.has(res.data, 'is_commit_required') &&
+ !_.isNull(res.data.is_commit_required) &&
+ res.data.is_commit_required;
+
+ if(is_commit_required) {
+ msg = gettext('Data saved successfully, you still need to commit changes to the database.');
+ self.disable_transaction_buttons(false);
+ }
+ else
+ msg = gettext('Data saved successfully.');
+
+ alertify.success(msg);
+ } else {
+ // Something went wrong while saving data on the db server
+ self.set_sql_message(res.data.result);
+ var err_msg = S(gettext('%s.')).sprintf(res.data.result).value();
+ alertify.error(err_msg, 20);
+ grid.setSelectedRows([]);
+ // To highlight the row at fault
+ if (_.has(res.data, '_rowid') &&
+ (!_.isUndefined(res.data._rowid) || !_.isNull(res.data._rowid))) {
+ var _row_index = self._find_rowindex(res.data._rowid);
+ if (_row_index in self.data_store.added_index) {
+ // Remove new row index from temp_list if save operation
+ // fails
+ var index = self.handler.temp_new_rows.indexOf(res.data._rowid);
+ if (index > -1) {
+ self.handler.temp_new_rows.splice(index, 1);
}
+ self.data_store.added[self.data_store.added_index[_row_index]].err = true;
+ } else if (_row_index in self.data_store.updated_index) {
+ self.data_store.updated[self.data_store.updated_index[_row_index]].err = true;
}
- grid.gotoCell(_row_index, 1);
}
+ grid.gotoCell(_row_index, 1);
+ }
- // Update the sql results in history tab
- _.each(res.data.query_result, function(r) {
- self.gridView.history_collection.add({
- 'status': r.status,
- 'start_time': self.query_start_time,
- 'query': r.sql,
- 'row_affected': r.rows_affected,
- 'total_time': self.total_time,
- 'message': r.result,
- });
+ // Update the sql results in history tab
+ /*
+ _.each(res.data.query_result, function(r) {
+ self.gridView.history_collection.add({
+ 'status': r.status,
+ 'start_time': self.query_start_time,
+ 'query': r.sql,
+ 'row_affected': r.rows_affected,
+ 'total_time': self.total_time,
+ 'message': r.result,
});
- self.trigger('pgadmin-sqleditor:loading-icon:hide');
+ });
+ */
+ self.trigger('pgadmin-sqleditor:loading-icon:hide');
- grid.invalidate();
- if (self.close_on_save) {
- self.close();
+ grid.invalidate();
+ if (self.close_on_save) {
+ if(save_successful) {
+ // Check for any other needed confirmations before closing
+ self.check_needed_confirmations_before_closing_panel();
}
- })
- .fail(function(e) {
- let stateParams = [view, controller, save_as];
- let msg = httpErrorHandler.handleQueryToolAjaxError(
- pgAdmin, self, e, '_save', stateParams, true
- );
- self.update_msg_history(false, msg);
- });
- }
+ else {
+ self.close_on_save = false;
+ }
+ }
+ })
+ .fail(function(e) {
+ let stateParams = [view];
+ let msg = httpErrorHandler.handleQueryToolAjaxError(
+ pgAdmin, self, e, 'save_data', stateParams, true
+ );
+ self.update_msg_history(false, msg);
+ });
+ },
+
+ reset_data_store: function() {
+ var self = this;
+ // This holds all the inserted/updated/deleted data from grid
+ self.data_store = {
+ updated: {},
+ added: {},
+ staged_rows: {},
+ deleted: {},
+ updated_index: {},
+ added_index: {},
+ };
},
// Find index of row at fault from grid data
@@ -3065,7 +3041,7 @@ define('tools.querytool', [
// Save as
_save_as: function() {
- return this._save(true);
+ return this._save_file(true);
},
// Set panel title.
@@ -3152,7 +3128,7 @@ define('tools.querytool', [
$busy_icon_div.removeClass('show_progress');
// disable save button on file save
- $('#btn-save').prop('disabled', true);
+ $('#btn-save-file').prop('disabled', true);
$('#btn-file-menu-save').css('display', 'none');
// Update the flag as new content is just loaded.
@@ -3197,7 +3173,7 @@ define('tools.querytool', [
self.gridView.current_file = e;
self.setTitle(self.gridView.current_file.replace(/^.*[\\\/]/g, ''), true);
// disable save button on file save
- $('#btn-save').prop('disabled', true);
+ $('#btn-save-file').prop('disabled', true);
$('#btn-file-menu-save').css('display', 'none');
// Update the flag as query is already saved.
@@ -3206,7 +3182,8 @@ define('tools.querytool', [
}
self.trigger('pgadmin-sqleditor:loading-icon:hide');
if (self.close_on_save) {
- self.close();
+ // Check for any other needed confirmations before closing
+ self.check_needed_confirmations_before_closing_panel();
}
})
.fail(function(e) {
@@ -3246,7 +3223,7 @@ define('tools.querytool', [
self.setTitle(title);
}
- $('#btn-save').prop('disabled', false);
+ $('#btn-save-file').prop('disabled', false);
$('#btn-file-menu-save').css('display', 'block');
$('#btn-file-menu-dropdown').prop('disabled', false);
}
@@ -3480,7 +3457,7 @@ define('tools.querytool', [
if (copied_rows.length > 0) {
// Enable save button so that user can
// save newly pasted rows on server
- $('#btn-save').prop('disabled', false);
+ $('#btn-save-data').prop('disabled', false);
var arr_to_object = function(arr) {
var obj = {};
@@ -3583,10 +3560,11 @@ define('tools.querytool', [
$('#btn-explain-options-dropdown').prop('disabled', mode_disabled);
$('#btn-edit-dropdown').prop('disabled', mode_disabled);
$('#btn-load-file').prop('disabled', mode_disabled);
- $('#btn-save').prop('disabled', mode_disabled);
+ $('#btn-save-file').prop('disabled', mode_disabled);
$('#btn-file-menu-dropdown').prop('disabled', mode_disabled);
$('#btn-find').prop('disabled', mode_disabled);
$('#btn-find-menu-dropdown').prop('disabled', mode_disabled);
+
if (this.is_query_tool) {
// Cancel query tool needs opposite behaviour
$('#btn-cancel-query').prop('disabled', !disabled);
@@ -3612,8 +3590,36 @@ define('tools.querytool', [
// This function will fetch the sql query from the text box
// and execute the query.
execute: function(explain_prefix, shouldReconnect=false) {
- var self = this,
- sql = '';
+ var self = this;
+
+ // Check if the data grid has any changes before running query
+ // Check if the data grid has any changes before running query
+ if (self.can_edit && _.has(self, 'data_store') &&
+ (_.size(self.data_store.added) ||
+ _.size(self.data_store.updated) ||
+ _.size(self.data_store.deleted))
+ ) {
+ alertify.confirm(gettext('Unsaved changes'),
+ gettext('The data has been modified, but not saved. Are you sure you wish to discard the changes?'),
+ function() {
+ // Do nothing as user do not want to save, just continue
+ self._execute_sql_query(explain_prefix, shouldReconnect);
+ },
+ function() {
+ // Stop, User wants to save
+ return true;
+ }
+ ).set('labels', {
+ ok: gettext('Yes'),
+ cancel: gettext('No'),
+ });
+ } else {
+ self._execute_sql_query(explain_prefix, shouldReconnect);
+ }
+ },
+
+ _execute_sql_query: function(explain_prefix, shouldReconnect) {
+ var self = this, sql = '';
self.has_more_rows = false;
self.fetching_rows = false;
@@ -3622,8 +3628,8 @@ define('tools.querytool', [
sql = self.special_sql;
} else {
/* If code is selected in the code mirror then execute
- * the selected part else execute the complete code.
- */
+ * the selected part else execute the complete code.
+ */
var selected_code = self.gridView.query_tool_obj.getSelection();
if (selected_code.length > 0)
sql = selected_code;
@@ -4018,6 +4024,146 @@ define('tools.querytool', [
is_query_running = value;
},
+ /* Checks if there is any unsaved data changes, unsaved changes in the query
+ or uncommited transactions before closing a panel */
+ check_needed_confirmations_before_closing_panel: function(is_close_event_call = false) {
+ var self = this, msg;
+
+ /*
+ is_close_event_call = true only when the function is called when the
+ close panel event is triggered, otherwise it is false
+ */
+ if(!self.ignore_on_close || is_close_event_call)
+ self.ignore_on_close = {
+ unsaved_data: false,
+ unsaved_query: false,
+ uncommited_transaction: false,
+ };
+
+ var ignore_unsaved_data = self.ignore_on_close.unsaved_data,
+ ignore_unsaved_query = self.ignore_on_close.unsaved_query,
+ ignore_uncommited_transaction = self.ignore_on_close.uncommited_transaction;
+
+ // If there is unsaved data changes in the grid
+ if (!ignore_unsaved_data && self.can_edit
+ && self.preferences.prompt_save_data_changes &&
+ self.data_store &&
+ (_.size(self.data_store.added) ||
+ _.size(self.data_store.updated) ||
+ _.size(self.data_store.deleted))) {
+ msg = gettext('The data has changed. Do you want to save changes?');
+ self.unsaved_changes_user_confirmation(msg, true);
+ } // If there is unsaved query changes in the query editor
+ else if (!ignore_unsaved_query && self.is_query_tool
+ && self.is_query_changed
+ && self.preferences.prompt_save_query_changes) {
+ msg = gettext('The text has changed. Do you want to save changes?');
+ self.unsaved_changes_user_confirmation(msg, false);
+ } // If a transaction is currently ongoing
+ else if (!ignore_uncommited_transaction
+ && self.preferences.alert_uncommited_transaction
+ && SqlEditorUtils.previousStatus != 0) {
+ self.uncommited_transaction_user_confirmation();
+ }
+ else {
+ // No other function should call close() except through this function
+ // in order to perform necessary checks
+ self.ignore_on_close = undefined;
+ self.close();
+ }
+ // Return false so that the panel does not close unless close()
+ // is called explicitly (when all needed prompts are issued).
+ return false;
+ },
+
+ /* To prompt the user for uncommited transaction */
+ uncommited_transaction_user_confirmation: function() {
+ var self = this;
+
+ alertify.confirm(
+ gettext('Uncommited transaction'),
+ gettext(`The current transaction was not commited to the database.
+ Are you sure you wish to discard the current transaction?`),
+ function() {
+ // Go back to check for any other needed confirmations before closing
+ self.ignore_on_close.uncommited_transaction = true;
+ self.check_needed_confirmations_before_closing_panel();
+ },
+ function() {
+ return;
+ }
+ ).set('labels', {
+ ok: gettext('Yes'),
+ cancel: gettext('No'),
+ });
+ },
+
+ /* To prompt user for unsaved changes */
+ unsaved_changes_user_confirmation: function(msg, is_unsaved_data) {
+ // If there is anything to save then prompt user
+ var self = this;
+
+ alertify.confirmSave || alertify.dialog('confirmSave', function() {
+ return {
+ main: function(title, message, is_unsaved_data) {
+ this.is_unsaved_data = is_unsaved_data;
+ this.setHeader(title);
+ this.setContent(message);
+ },
+ setup: function() {
+ return {
+ buttons: [{
+ text: gettext('Cancel'),
+ key: 27, // ESC
+ invokeOnClose: true,
+ className: 'btn btn-secondary fa fa-lg fa-times pg-alertify-button',
+ }, {
+ text: gettext('Don\'t save'),
+ className: 'btn btn-secondary fa fa-lg fa-trash-o pg-alertify-button',
+ }, {
+ text: gettext('Save'),
+ className: 'btn btn-primary fa fa-lg fa-save pg-alertify-button',
+ }],
+ focus: {
+ element: 0,
+ select: false,
+ },
+ options: {
+ maximizable: false,
+ resizable: false,
+ },
+ };
+ },
+ callback: function(closeEvent) {
+ switch (closeEvent.index) {
+ case 0: // Cancel
+ //Do nothing.
+ break;
+ case 1: // Don't Save
+ self.close_on_save = false;
+ if(this.is_unsaved_data)
+ self.ignore_on_close.unsaved_data = true;
+ else
+ self.ignore_on_close.unsaved_query = true;
+ // Go back to check for any other needed confirmations before closing
+ self.check_needed_confirmations_before_closing_panel();
+ break;
+ case 2: //Save
+ self.close_on_save = true;
+ if(this.is_unsaved_data) {
+ self.save_data();
+ }
+ else {
+ self._save_file();
+ }
+ break;
+ }
+ },
+ };
+ });
+ alertify.confirmSave(gettext('Save changes?'), msg, is_unsaved_data);
+ },
+
close: function() {
var self = this;
diff --git a/web/pgadmin/tools/sqleditor/static/scss/_sqleditor.scss b/web/pgadmin/tools/sqleditor/static/scss/_sqleditor.scss
index ebf5b180..2742f320 100644
--- a/web/pgadmin/tools/sqleditor/static/scss/_sqleditor.scss
+++ b/web/pgadmin/tools/sqleditor/static/scss/_sqleditor.scss
@@ -209,6 +209,13 @@ li.CodeMirror-hint-active {
color: $text-muted;
}
+/* Highlighted (modified or new) cell */
+.grid-canvas .highlighted_grid_cells {
+ background: $color-gray-lighter;
+ font-weight: bold;
+}
+
+
/* Override selected row color */
#datagrid .slick-row .slick-cell.selected {
background-color: $table-bg-selected;
diff --git a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/11_plus/primary_keys.sql b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/11_plus/primary_keys.sql
index 1dfb094f..459977e9 100644
--- a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/11_plus/primary_keys.sql
+++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/11_plus/primary_keys.sql
@@ -1,6 +1,6 @@
{# ============= Fetch the primary keys for given object id ============= #}
{% if obj_id %}
-SELECT at.attname, ty.typname
+SELECT at.attname, at.attnum, ty.typname
FROM pg_attribute at LEFT JOIN pg_type ty ON (ty.oid = at.atttypid)
WHERE attrelid={{obj_id}}::oid AND attnum = ANY (
(SELECT con.conkey FROM pg_class rel LEFT OUTER JOIN pg_constraint con ON con.conrelid=rel.oid
diff --git a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/default/primary_keys.sql b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/default/primary_keys.sql
index 60d0e56f..a96c928f 100644
--- a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/default/primary_keys.sql
+++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/default/primary_keys.sql
@@ -1,8 +1,8 @@
{# ============= Fetch the primary keys for given object id ============= #}
{% if obj_id %}
-SELECT at.attname, ty.typname
+SELECT at.attname, at.attnum, ty.typname
FROM pg_attribute at LEFT JOIN pg_type ty ON (ty.oid = at.atttypid)
WHERE attrelid={{obj_id}}::oid AND attnum = ANY (
(SELECT con.conkey FROM pg_class rel LEFT OUTER JOIN pg_constraint con ON con.conrelid=rel.oid
AND con.contype='p' WHERE rel.relkind IN ('r','s','t') AND rel.oid = {{obj_id}}::oid)::oid[])
-{% endif %}
\ No newline at end of file
+{% endif %}
diff --git a/web/pgadmin/tools/sqleditor/tests/execute_query_utils.py b/web/pgadmin/tools/sqleditor/tests/execute_query_utils.py
new file mode 100644
index 00000000..c3cf89b3
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/tests/execute_query_utils.py
@@ -0,0 +1,28 @@
+import json
+
+
+def execute_query(tester, query, start_query_tool_url, poll_url):
+ # Start query tool and execute sql
+ response = tester.post(start_query_tool_url,
+ data=json.dumps({"sql": query}),
+ content_type='html/json')
+
+ if response.status_code != 200:
+ return False, None
+
+ # Poll for results
+ return poll_for_query_results(tester=tester, poll_url=poll_url)
+
+
+def poll_for_query_results(tester, poll_url):
+ # Poll for results until they are successful
+ while True:
+ response = tester.get(poll_url)
+ if response.status_code != 200:
+ return False, None
+ response_data = json.loads(response.data.decode('utf-8'))
+ status = response_data['data']['status']
+ if status == 'Success':
+ return True, response_data
+ elif status == 'NotConnected' or status == 'Cancel':
+ return False, None
diff --git a/web/pgadmin/tools/sqleditor/tests/test_is_query_resultset_updatable.py b/web/pgadmin/tools/sqleditor/tests/test_is_query_resultset_updatable.py
new file mode 100644
index 00000000..d7e75c78
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/tests/test_is_query_resultset_updatable.py
@@ -0,0 +1,125 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2019, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+import json
+
+from pgadmin.browser.server_groups.servers.databases.tests import utils as \
+ database_utils
+from pgadmin.utils.route import BaseTestGenerator
+from regression import parent_node_dict
+from regression.python_test_utils import test_utils as utils
+from .execute_query_utils import execute_query
+
+
+class TestQueryUpdatableResultset(BaseTestGenerator):
+ """ This class will test the detection of whether the query
+ result-set is updatable. """
+ scenarios = [
+ ('When selecting all columns of the table', dict(
+ sql='SELECT * FROM test_for_updatable_resultset;',
+ primary_keys={
+ 'pk_col1': 'int4',
+ 'pk_col2': 'int4'
+ }
+ )),
+ ('When selecting all primary keys of the table', dict(
+ sql='SELECT pk_col1, pk_col2 FROM test_for_updatable_resultset;',
+ primary_keys={
+ 'pk_col1': 'int4',
+ 'pk_col2': 'int4'
+ }
+ )),
+ ('When selecting some of the primary keys of the table', dict(
+ sql='SELECT pk_col2 FROM test_for_updatable_resultset;',
+ primary_keys=None
+ )),
+ ('When selecting none of the primary keys of the table', dict(
+ sql='SELECT normal_col1 FROM test_for_updatable_resultset;',
+ primary_keys=None
+ )),
+ ('When renaming a primary key', dict(
+ sql='SELECT pk_col1 as some_col, '
+ 'pk_col2 FROM test_for_updatable_resultset;',
+ primary_keys=None
+ )),
+ ('When renaming a column to a primary key name', dict(
+ sql='SELECT pk_col1, pk_col2, normal_col1 as pk_col1 '
+ 'FROM test_for_updatable_resultset;',
+ primary_keys=None
+ ))
+ ]
+
+ def setUp(self):
+ self._initialize_database_connection()
+ self._initialize_query_tool()
+ self._initialize_urls()
+ self._create_test_table()
+
+ def runTest(self):
+ is_success, response_data = \
+ execute_query(tester=self.tester,
+ query=self.sql,
+ poll_url=self.poll_url,
+ start_query_tool_url=self.start_query_tool_url)
+ self.assertEquals(is_success, True)
+
+ # Check primary keys
+ primary_keys = response_data['data']['primary_keys']
+ self.assertEquals(primary_keys, self.primary_keys)
+
+ def tearDown(self):
+ # Disconnect the database
+ database_utils.disconnect_database(self, self.server_id, self.db_id)
+
+ def _initialize_database_connection(self):
+ database_info = parent_node_dict["database"][-1]
+ self.server_id = database_info["server_id"]
+
+ self.db_id = database_info["db_id"]
+ db_con = database_utils.connect_database(self,
+ utils.SERVER_GROUP,
+ self.server_id,
+ self.db_id)
+ if not db_con["info"] == "Database connected.":
+ raise Exception("Could not connect to the database.")
+
+ def _initialize_query_tool(self):
+ url = '/datagrid/initialize/query_tool/{0}/{1}/{2}'.format(
+ utils.SERVER_GROUP, self.server_id, self.db_id)
+ response = self.tester.post(url)
+ self.assertEquals(response.status_code, 200)
+
+ response_data = json.loads(response.data.decode('utf-8'))
+ self.trans_id = response_data['data']['gridTransId']
+
+ def _initialize_urls(self):
+ self.start_query_tool_url = \
+ '/sqleditor/query_tool/start/{0}'.format(self.trans_id)
+
+ self.poll_url = '/sqleditor/poll/{0}'.format(self.trans_id)
+
+ def _create_test_table(self):
+ create_sql = """
+ DROP TABLE IF EXISTS test_for_updatable_resultset;
+
+ CREATE TABLE test_for_updatable_resultset(
+ pk_col1 SERIAL,
+ pk_col2 SERIAL,
+ normal_col1 VARCHAR,
+ normal_col2 VARCHAR,
+ PRIMARY KEY(pk_col1, pk_col2)
+ );
+ """
+
+ is_success, _ = \
+ execute_query(tester=self.tester,
+ query=create_sql,
+ start_query_tool_url=self.start_query_tool_url,
+ poll_url=self.poll_url)
+ self.assertEquals(is_success, True)
diff --git a/web/pgadmin/tools/sqleditor/tests/test_save_changed_data.py b/web/pgadmin/tools/sqleditor/tests/test_save_changed_data.py
new file mode 100644
index 00000000..01795d29
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/tests/test_save_changed_data.py
@@ -0,0 +1,347 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2019, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+import json
+
+from pgadmin.browser.server_groups.servers.databases.tests import utils as \
+ database_utils
+from pgadmin.utils.route import BaseTestGenerator
+from regression import parent_node_dict
+from regression.python_test_utils import test_utils as utils
+from .execute_query_utils import execute_query
+
+
+class TestSaveChangedData(BaseTestGenerator):
+ """ This class tests saving data changes in the grid to the database """
+ scenarios = [
+ ('When inserting new valid row', dict(
+ save_payload={
+ "updated": {},
+ "added": {
+ "2": {
+ "err": False,
+ "data": {
+ "pk_col": "3",
+ "__temp_PK": "2",
+ "normal_col": "three"
+ }
+ }
+ },
+ "staged_rows": {},
+ "deleted": {},
+ "updated_index": {},
+ "added_index": {"2": "2"},
+ "columns": [
+ {
+ "name": "pk_col",
+ "display_name": "pk_col",
+ "column_type": "[PK] integer",
+ "column_type_internal": "integer",
+ "pos": 0,
+ "label": "pk_col<br>[PK] integer",
+ "cell": "number",
+ "can_edit": True,
+ "type": "integer",
+ "not_null": True,
+ "has_default_val": False,
+ "is_array": False},
+ {"name": "normal_col",
+ "display_name": "normal_col",
+ "column_type": "character varying",
+ "column_type_internal": "character varying",
+ "pos": 1,
+ "label": "normal_col<br>character varying",
+ "cell": "string",
+ "can_edit": True,
+ "type": "character varying",
+ "not_null": False,
+ "has_default_val": False,
+ "is_array": False}
+ ]
+ },
+ save_status=True,
+ check_sql='SELECT * FROM test_for_save_data WHERE pk_col = 3',
+ check_result=[[3, "three"]]
+ )),
+ ('When inserting new invalid row', dict(
+ save_payload={
+ "updated": {},
+ "added": {
+ "2": {
+ "err": False,
+ "data": {
+ "pk_col": "1",
+ "__temp_PK": "2",
+ "normal_col": "four"
+ }
+ }
+ },
+ "staged_rows": {},
+ "deleted": {},
+ "updated_index": {},
+ "added_index": {"2": "2"},
+ "columns": [
+ {
+ "name": "pk_col",
+ "display_name": "pk_col",
+ "column_type": "[PK] integer",
+ "column_type_internal": "integer",
+ "pos": 0,
+ "label": "pk_col<br>[PK] integer",
+ "cell": "number",
+ "can_edit": True,
+ "type": "integer",
+ "not_null": True,
+ "has_default_val": False,
+ "is_array": False},
+ {"name": "normal_col",
+ "display_name": "normal_col",
+ "column_type": "character varying",
+ "column_type_internal": "character varying",
+ "pos": 1,
+ "label": "normal_col<br>character varying",
+ "cell": "string",
+ "can_edit": True,
+ "type": "character varying",
+ "not_null": False,
+ "has_default_val": False,
+ "is_array": False}
+ ]
+ },
+ save_status=False,
+ check_sql=None,
+ check_result=None
+ )),
+ ('When updating a row in a valid way', dict(
+ save_payload={
+ "updated": {
+ "1":
+ {"err": False,
+ "data": {"normal_col": "ONE"},
+ "primary_keys":
+ {"pk_col": 1}
+ }
+ },
+ "added": {},
+ "staged_rows": {},
+ "deleted": {},
+ "updated_index": {"1": "1"},
+ "added_index": {},
+ "columns": [
+ {
+ "name": "pk_col",
+ "display_name": "pk_col",
+ "column_type": "[PK] integer",
+ "column_type_internal": "integer",
+ "pos": 0,
+ "label": "pk_col<br>[PK] integer",
+ "cell": "number",
+ "can_edit": True,
+ "type": "integer",
+ "not_null": True,
+ "has_default_val": False,
+ "is_array": False},
+ {"name": "normal_col",
+ "display_name": "normal_col",
+ "column_type": "character varying",
+ "column_type_internal": "character varying",
+ "pos": 1,
+ "label": "normal_col<br>character varying",
+ "cell": "string",
+ "can_edit": True,
+ "type": "character varying",
+ "not_null": False,
+ "has_default_val": False,
+ "is_array": False}
+ ]
+ },
+ save_status=True,
+ check_sql='SELECT * FROM test_for_save_data WHERE pk_col = 1',
+ check_result=[[1, "ONE"]]
+ )),
+ ('When updating a row in an invalid way', dict(
+ save_payload={
+ "updated": {
+ "1":
+ {"err": False,
+ "data": {"pk_col": "2"},
+ "primary_keys":
+ {"pk_col": 1}
+ }
+ },
+ "added": {},
+ "staged_rows": {},
+ "deleted": {},
+ "updated_index": {"1": "1"},
+ "added_index": {},
+ "columns": [
+ {
+ "name": "pk_col",
+ "display_name": "pk_col",
+ "column_type": "[PK] integer",
+ "column_type_internal": "integer",
+ "pos": 0,
+ "label": "pk_col<br>[PK] integer",
+ "cell": "number",
+ "can_edit": True,
+ "type": "integer",
+ "not_null": True,
+ "has_default_val": False,
+ "is_array": False},
+ {"name": "normal_col",
+ "display_name": "normal_col",
+ "column_type": "character varying",
+ "column_type_internal": "character varying",
+ "pos": 1,
+ "label": "normal_col<br>character varying",
+ "cell": "string",
+ "can_edit": True,
+ "type": "character varying",
+ "not_null": False,
+ "has_default_val": False,
+ "is_array": False}
+ ]
+ },
+ save_status=False,
+ check_sql=None,
+ check_result=None
+ )),
+ ('When deleting a row', dict(
+ save_payload={
+ "updated": {},
+ "added": {},
+ "staged_rows": {"1": {"pk_col": 2}},
+ "deleted": {"1": {"pk_col": 2}},
+ "updated_index": {},
+ "added_index": {},
+ "columns": [
+ {
+ "name": "pk_col",
+ "display_name": "pk_col",
+ "column_type": "[PK] integer",
+ "column_type_internal": "integer",
+ "pos": 0,
+ "label": "pk_col<br>[PK] integer",
+ "cell": "number",
+ "can_edit": True,
+ "type": "integer",
+ "not_null": True,
+ "has_default_val": False,
+ "is_array": False},
+ {"name": "normal_col",
+ "display_name": "normal_col",
+ "column_type": "character varying",
+ "column_type_internal": "character varying",
+ "pos": 1,
+ "label": "normal_col<br>character varying",
+ "cell": "string",
+ "can_edit": True,
+ "type": "character varying",
+ "not_null": False,
+ "has_default_val": False,
+ "is_array": False}
+ ]
+ },
+ save_status=True,
+ check_sql='SELECT * FROM test_for_save_data WHERE pk_col = 2',
+ check_result='SELECT 0'
+ )),
+ ]
+
+ def setUp(self):
+ self._initialize_database_connection()
+ self._initialize_query_tool()
+ self._initialize_urls_and_select_sql()
+ self._create_test_table()
+
+ def runTest(self):
+ # Execute select sql
+ is_success, _ = \
+ execute_query(tester=self.tester,
+ query=self.select_sql,
+ start_query_tool_url=self.start_query_tool_url,
+ poll_url=self.poll_url)
+ self.assertEquals(is_success, True)
+
+ # Send a request to save changed data
+ response = self.tester.post(self.save_url,
+ data=json.dumps(self.save_payload),
+ content_type='html/json')
+
+ self.assertEquals(response.status_code, 200)
+
+ # Check that the save is successful
+ response_data = json.loads(response.data.decode('utf-8'))
+ save_status = response_data['data']['status']
+ self.assertEquals(save_status, self.save_status)
+
+ if self.check_sql:
+ # Execute check sql
+ is_success, response_data = \
+ execute_query(tester=self.tester,
+ query=self.check_sql,
+ start_query_tool_url=self.start_query_tool_url,
+ poll_url=self.poll_url)
+ self.assertEquals(is_success, True)
+
+ # Check table for updates
+ result = response_data['data']['result']
+ self.assertEquals(result, self.check_result)
+
+ def tearDown(self):
+ # Disconnect the database
+ database_utils.disconnect_database(self, self.server_id, self.db_id)
+
+ def _initialize_database_connection(self):
+ database_info = parent_node_dict["database"][-1]
+ self.server_id = database_info["server_id"]
+
+ self.db_id = database_info["db_id"]
+ db_con = database_utils.connect_database(self,
+ utils.SERVER_GROUP,
+ self.server_id,
+ self.db_id)
+ if not db_con["info"] == "Database connected.":
+ raise Exception("Could not connect to the database.")
+
+ def _initialize_query_tool(self):
+ url = '/datagrid/initialize/query_tool/{0}/{1}/{2}'.format(
+ utils.SERVER_GROUP, self.server_id, self.db_id)
+ response = self.tester.post(url)
+ self.assertEquals(response.status_code, 200)
+
+ response_data = json.loads(response.data.decode('utf-8'))
+ self.trans_id = response_data['data']['gridTransId']
+
+ def _initialize_urls_and_select_sql(self):
+ self.start_query_tool_url = \
+ '/sqleditor/query_tool/start/{0}'.format(self.trans_id)
+ self.save_url = '/sqleditor/save/{0}'.format(self.trans_id)
+ self.poll_url = '/sqleditor/poll/{0}'.format(self.trans_id)
+
+ self.select_sql = 'SELECT * FROM test_for_save_data;'
+
+ def _create_test_table(self):
+ create_sql = """
+ DROP TABLE IF EXISTS test_for_save_data;
+
+ CREATE TABLE test_for_save_data(
+ pk_col INT PRIMARY KEY,
+ normal_col VARCHAR);
+
+ INSERT INTO test_for_save_data VALUES
+ (1, 'one'),
+ (2, 'two');
+ """
+ is_success, _ = \
+ execute_query(tester=self.tester,
+ query=create_sql,
+ start_query_tool_url=self.start_query_tool_url,
+ poll_url=self.poll_url)
+ self.assertEquals(is_success, True)
diff --git a/web/pgadmin/tools/sqleditor/utils/is_query_resultset_updatable.py b/web/pgadmin/tools/sqleditor/utils/is_query_resultset_updatable.py
new file mode 100644
index 00000000..f6b453ee
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/utils/is_query_resultset_updatable.py
@@ -0,0 +1,120 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2019, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+"""
+ Check if the result-set of a query is updatable, A resultset is
+ updatable (as of this version) if:
+ - All columns belong to the same table.
+ - All the primary key columns of the table are present in the resultset
+ - No duplicate columns
+"""
+from flask import render_template
+try:
+ from collections import OrderedDict
+except ImportError:
+ from ordereddict import OrderedDict
+
+
+def is_query_resultset_updatable(conn, sql_path):
+ """
+ This function is used to check whether the last successful query
+ produced updatable results.
+
+ Args:
+ conn: Connection object.
+ sql_path: the path to the sql templates.
+ """
+ columns_info = conn.get_column_info()
+
+ if columns_info is None or len(columns_info) < 1:
+ return return_not_updatable()
+
+ table_oid = _check_single_table(columns_info)
+ if not table_oid:
+ return return_not_updatable()
+
+ if not _check_duplicate_columns(columns_info):
+ return return_not_updatable()
+
+ if conn.connected():
+ primary_keys, primary_keys_columns, pk_names = \
+ _get_primary_keys(conn=conn,
+ table_oid=table_oid,
+ sql_path=sql_path)
+
+ if not _check_primary_keys_uniquely_exist(primary_keys_columns,
+ columns_info):
+ return return_not_updatable()
+
+ return True, primary_keys, pk_names, table_oid
+ else:
+ return return_not_updatable()
+
+
+def _check_single_table(columns_info):
+ table_oid = columns_info[0]['table_oid']
+ for column in columns_info:
+ if column['table_oid'] != table_oid:
+ return None
+ return table_oid
+
+
+def _check_duplicate_columns(columns_info):
+ column_numbers = \
+ [col['table_column'] for col in columns_info]
+ is_duplicate_columns = len(column_numbers) != len(set(column_numbers))
+ if is_duplicate_columns:
+ return False
+ return True
+
+
+def _check_primary_keys_uniquely_exist(primary_keys_columns, columns_info):
+ for pk in primary_keys_columns:
+ pk_exists = False
+ for col in columns_info:
+ if col['table_column'] == pk['column_number']:
+ pk_exists = True
+ # If the primary key column is renamed
+ if col['display_name'] != pk['name']:
+ return False
+ # If a normal column is renamed to a primary key column name
+ elif col['display_name'] == pk['name']:
+ return False
+
+ if not pk_exists:
+ return False
+ return True
+
+
+def _get_primary_keys(sql_path, table_oid, conn):
+ query = render_template(
+ "/".join([sql_path, 'primary_keys.sql']),
+ obj_id=table_oid
+ )
+ status, result = conn.execute_dict(query)
+ if not status:
+ return return_not_updatable()
+
+ primary_keys_columns = []
+ primary_keys = OrderedDict()
+ pk_names = []
+
+ for row in result['rows']:
+ primary_keys[row['attname']] = row['typname']
+ primary_keys_columns.append({
+ 'name': row['attname'],
+ 'column_number': row['attnum']
+ })
+ pk_names.append(row['attname'])
+
+ return primary_keys, primary_keys_columns, pk_names
+
+
+def return_not_updatable():
+ return False, None, None, None
diff --git a/web/pgadmin/tools/sqleditor/utils/query_tool_preferences.py b/web/pgadmin/tools/sqleditor/utils/query_tool_preferences.py
index ca09eaec..501ef0ce 100644
--- a/web/pgadmin/tools/sqleditor/utils/query_tool_preferences.py
+++ b/web/pgadmin/tools/sqleditor/utils/query_tool_preferences.py
@@ -163,6 +163,16 @@ def RegisterQueryToolPreferences(self):
)
)
+ self.show_alert_uncommited_transaction = self.preference.register(
+ 'Options', 'alert_uncommited_transaction',
+ gettext("Alert on uncommited transactions?"), 'boolean', True,
+ category_label=gettext('Options'),
+ help_str=gettext(
+ 'Specifies whether or not to alert the user when a current '
+ 'transaction is uncommited on Query Tool exit.'
+ )
+ )
+
self.csv_quoting = self.preference.register(
'CSV_output', 'csv_quoting',
gettext("CSV quoting"), 'options', 'strings',
@@ -290,6 +300,24 @@ def RegisterQueryToolPreferences(self):
fields=shortcut_fields
)
+ self.preference.register(
+ 'keyboard_shortcuts',
+ 'save_data',
+ gettext('Save Data Changes'),
+ 'keyboardshortcut',
+ {
+ 'alt': False,
+ 'shift': False,
+ 'control': False,
+ 'key': {
+ 'key_code': 117,
+ 'char': 'F6'
+ }
+ },
+ category_label=gettext('Keyboard shortcuts'),
+ fields=shortcut_fields
+ )
+
self.preference.register(
'keyboard_shortcuts',
'explain_query',
diff --git a/web/pgadmin/tools/sqleditor/utils/save_changed_data.py b/web/pgadmin/tools/sqleditor/utils/save_changed_data.py
new file mode 100644
index 00000000..6275e658
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/utils/save_changed_data.py
@@ -0,0 +1,317 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2019, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+from flask import render_template
+from pgadmin.tools.sqleditor.utils.constant_definition import TX_STATUS_IDLE
+try:
+ from collections import OrderedDict
+except ImportError:
+ from ordereddict import OrderedDict
+
+
+def save_changed_data(changed_data, columns_info, conn, command_obj,
+ client_primary_key, auto_commit=True):
+ """
+ This function is used to save the data into the database.
+ Depending on condition it will either update or insert the
+ new row into the database.
+
+ Args:
+ changed_data: Contains data to be saved
+ command_obj: The transaction object (command_obj or trans_obj)
+ conn: The connection object
+ columns_info: session_obj['columns_info']
+ client_primary_key: session_obj['client_primary_key']
+ auto_commit: If the changes should be commited automatically.
+ """
+ status = False
+ res = None
+ query_res = dict()
+ count = 0
+ list_of_rowid = []
+ operations = ('added', 'updated', 'deleted')
+ list_of_sql = {}
+ _rowid = None
+ is_commit_required = False
+
+ pgadmin_alias = {
+ col_name: col_info['pgadmin_alias']
+ for col_name, col_info in columns_info.items()
+ }
+
+ if conn.connected():
+ is_savepoint = False
+ # Start the transaction if the session is idle
+ if conn.transaction_status() == TX_STATUS_IDLE:
+ conn.execute_void('BEGIN;')
+ else:
+ conn.execute_void('SAVEPOINT save_data;')
+ is_savepoint = True
+
+ # Iterate total number of records to be updated/inserted
+ for of_type in changed_data:
+ # No need to go further if its not add/update/delete operation
+ if of_type not in operations:
+ continue
+ # if no data to be save then continue
+ if len(changed_data[of_type]) < 1:
+ continue
+
+ column_type = {}
+ column_data = {}
+ for each_col in columns_info:
+ if (
+ columns_info[each_col]['not_null'] and
+ not columns_info[each_col]['has_default_val']
+ ):
+ column_data[each_col] = None
+ column_type[each_col] = \
+ columns_info[each_col]['type_name']
+ else:
+ column_type[each_col] = \
+ columns_info[each_col]['type_name']
+
+ # For newly added rows
+ if of_type == 'added':
+ # Python dict does not honour the inserted item order
+ # So to insert data in the order, we need to make ordered
+ # list of added index We don't need this mechanism in
+ # updated/deleted rows as it does not matter in
+ # those operations
+ added_index = OrderedDict(
+ sorted(
+ changed_data['added_index'].items(),
+ key=lambda x: int(x[0])
+ )
+ )
+ list_of_sql[of_type] = []
+
+ # When new rows are added, only changed columns data is
+ # sent from client side. But if column is not_null and has
+ # no_default_value, set column to blank, instead
+ # of not null which is set by default.
+ column_data = {}
+ pk_names, primary_keys = command_obj.get_primary_keys()
+ has_oids = 'oid' in column_type
+
+ for each_row in added_index:
+ # Get the row index to match with the added rows
+ # dict key
+ tmp_row_index = added_index[each_row]
+ data = changed_data[of_type][tmp_row_index]['data']
+ # Remove our unique tracking key
+ data.pop(client_primary_key, None)
+ data.pop('is_row_copied', None)
+ list_of_rowid.append(data.get(client_primary_key))
+
+ # Update columns value with columns having
+ # not_null=False and has no default value
+ column_data.update(data)
+
+ sql = render_template(
+ "/".join([command_obj.sql_path, 'insert.sql']),
+ data_to_be_saved=column_data,
+ pgadmin_alias=pgadmin_alias,
+ primary_keys=None,
+ object_name=command_obj.object_name,
+ nsp_name=command_obj.nsp_name,
+ data_type=column_type,
+ pk_names=pk_names,
+ has_oids=has_oids
+ )
+
+ select_sql = render_template(
+ "/".join([command_obj.sql_path, 'select.sql']),
+ object_name=command_obj.object_name,
+ nsp_name=command_obj.nsp_name,
+ primary_keys=primary_keys,
+ has_oids=has_oids
+ )
+
+ list_of_sql[of_type].append({
+ 'sql': sql, 'data': data,
+ 'client_row': tmp_row_index,
+ 'select_sql': select_sql
+ })
+ # Reset column data
+ column_data = {}
+
+ # For updated rows
+ elif of_type == 'updated':
+ list_of_sql[of_type] = []
+ for each_row in changed_data[of_type]:
+ data = changed_data[of_type][each_row]['data']
+ pk_escaped = {
+ pk: pk_val.replace('%', '%%') if hasattr(
+ pk_val, 'replace') else pk_val
+ for pk, pk_val in
+ changed_data[of_type][each_row]['primary_keys'].items()
+ }
+ sql = render_template(
+ "/".join([command_obj.sql_path, 'update.sql']),
+ data_to_be_saved=data,
+ pgadmin_alias=pgadmin_alias,
+ primary_keys=pk_escaped,
+ object_name=command_obj.object_name,
+ nsp_name=command_obj.nsp_name,
+ data_type=column_type
+ )
+ list_of_sql[of_type].append({'sql': sql, 'data': data})
+ list_of_rowid.append(data.get(client_primary_key))
+
+ # For deleted rows
+ elif of_type == 'deleted':
+ list_of_sql[of_type] = []
+ is_first = True
+ rows_to_delete = []
+ keys = None
+ no_of_keys = None
+ for each_row in changed_data[of_type]:
+ 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 = 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
+ try:
+ row[changed_data['columns']
+ [int(k)]['name']] = v
+ except ValueError:
+ continue
+ del row[k]
+
+ sql = render_template(
+ "/".join([command_obj.sql_path, 'delete.sql']),
+ data=rows_to_delete,
+ primary_key_labels=keys,
+ no_of_keys=no_of_keys,
+ object_name=command_obj.object_name,
+ nsp_name=command_obj.nsp_name
+ )
+ list_of_sql[of_type].append({'sql': sql, 'data': {}})
+
+ for opr, sqls in list_of_sql.items():
+ for item in sqls:
+ if item['sql']:
+ item['data'] = {
+ pgadmin_alias[k] if k in pgadmin_alias else k: v
+ for k, v in item['data'].items()
+ }
+
+ row_added = None
+
+ def failure_handle(res):
+ if is_savepoint:
+ conn.execute_void('ROLLBACK TO SAVEPOINT '
+ 'save_data;')
+ msg = 'Query ROLLBACK, but the current ' \
+ 'transaction is still ongoing.'
+ res += ' Saving ROLLBACK, but the current ' \
+ 'transaction is still ongoing'
+ else:
+ conn.execute_void('ROLLBACK;')
+ msg = 'Transaction ROLLBACK'
+ # If we roll backed every thing then update the
+ # message for each sql query.
+ for val in query_res:
+ if query_res[val]['status']:
+ query_res[val]['result'] = msg
+
+ # If list is empty set rowid to 1
+ try:
+ if list_of_rowid:
+ _rowid = list_of_rowid[count]
+ else:
+ _rowid = 1
+ except Exception:
+ _rowid = 0
+
+ return status, res, query_res, _rowid,\
+ is_commit_required
+
+ try:
+ # Fetch oids/primary keys
+ if 'select_sql' in item and item['select_sql']:
+ status, res = conn.execute_dict(
+ item['sql'], item['data'])
+ else:
+ status, res = conn.execute_void(
+ item['sql'], item['data'])
+ except Exception as _:
+ failure_handle(res)
+ raise
+
+ if not status:
+ return failure_handle(res)
+
+ # Select added row from the table
+ if 'select_sql' in item:
+ status, sel_res = conn.execute_dict(
+ item['select_sql'], res['rows'][0])
+
+ if not status:
+ if is_savepoint:
+ conn.execute_void('ROLLBACK TO SAVEPOINT'
+ ' save_data;')
+ msg = 'Query ROLLBACK, the current' \
+ ' transaction is still ongoing.'
+ else:
+ conn.execute_void('ROLLBACK;')
+ msg = 'Transaction ROLLBACK'
+ # If we roll backed every thing then update
+ # the message for each sql query.
+ for val in query_res:
+ if query_res[val]['status']:
+ query_res[val]['result'] = msg
+
+ # If list is empty set rowid to 1
+ try:
+ if list_of_rowid:
+ _rowid = list_of_rowid[count]
+ else:
+ _rowid = 1
+ except Exception:
+ _rowid = 0
+
+ return status, sel_res, query_res, _rowid,\
+ is_commit_required
+
+ if 'rows' in sel_res and len(sel_res['rows']) > 0:
+ row_added = {
+ item['client_row']: sel_res['rows'][0]}
+
+ rows_affected = conn.rows_affected()
+ # store the result of each query in dictionary
+ query_res[count] = {
+ 'status': status,
+ 'result': None if row_added else res,
+ 'sql': item['sql'], 'rows_affected': rows_affected,
+ 'row_added': row_added
+ }
+
+ count += 1
+
+ # Commit the transaction if no error is found & autocommit is activated
+ if auto_commit:
+ conn.execute_void('COMMIT;')
+ else:
+ is_commit_required = True
+
+ return status, res, query_res, _rowid, is_commit_required
diff --git a/web/pgadmin/tools/sqleditor/utils/start_running_query.py b/web/pgadmin/tools/sqleditor/utils/start_running_query.py
index a5399774..ece11f9c 100644
--- a/web/pgadmin/tools/sqleditor/utils/start_running_query.py
+++ b/web/pgadmin/tools/sqleditor/utils/start_running_query.py
@@ -45,6 +45,9 @@ class StartRunningQuery:
if type(session_obj) is Response:
return session_obj
+ # Remove any existing primary keys in session_obj
+ session_obj.pop('primary_keys', None)
+
transaction_object = pickle.loads(session_obj['command_obj'])
can_edit = False
can_filter = False
diff --git a/web/regression/javascript/sqleditor/call_render_after_poll_spec.js b/web/regression/javascript/sqleditor/call_render_after_poll_spec.js
index d68f5ee1..8f30c10c 100644
--- a/web/regression/javascript/sqleditor/call_render_after_poll_spec.js
+++ b/web/regression/javascript/sqleditor/call_render_after_poll_spec.js
@@ -23,6 +23,7 @@ describe('#callRenderAfterPoll', () => {
update_msg_history: jasmine.createSpy('SQLEditor.update_msg_history'),
disable_tool_buttons: jasmine.createSpy('SQLEditor.disable_tool_buttons'),
disable_transaction_buttons: jasmine.createSpy('SQLEditor.disable_transaction_buttons'),
+ reset_data_store: jasmine.createSpy('SQLEditor.reset_data_store'),
query_start_time: new Date(),
};
alertify = jasmine.createSpyObj('alertify', ['success']);
@@ -37,7 +38,7 @@ describe('#callRenderAfterPoll', () => {
sqlEditorSpy.is_query_tool = false;
});
- describe('query was successful but had no result to display', () => {
+ describe('query was successful and have results', () => {
beforeEach(() => {
queryResult = {
rows_affected: 10,
@@ -65,7 +66,7 @@ describe('#callRenderAfterPoll', () => {
});
});
- describe('query was successful and have results', () => {
+ describe('query was successful but had no result to display', () => {
beforeEach(() => {
queryResult = {
rows_affected: 10,
@@ -81,10 +82,16 @@ describe('#callRenderAfterPoll', () => {
expect(sqlEditorSpy.update_msg_history).toHaveBeenCalledWith(
true,
'Some result\n\nQuery returned successfully in 0 msec.',
- false
+ true
);
});
+ it('resets the changed data store', () => {
+ callRenderAfterPoll(sqlEditorSpy, alertify, queryResult);
+
+ expect(sqlEditorSpy.reset_data_store).toHaveBeenCalled();
+ });
+
it('inform sqleditor that the query stopped running', () => {
callRenderAfterPoll(sqlEditorSpy, alertify, queryResult);
@@ -116,7 +123,7 @@ describe('#callRenderAfterPoll', () => {
sqlEditorSpy.is_query_tool = true;
});
- describe('query was successful but had no result to display', () => {
+ describe('query was successful and have results', () => {
beforeEach(() => {
queryResult = {
rows_affected: 10,
@@ -150,7 +157,7 @@ describe('#callRenderAfterPoll', () => {
});
});
- describe('query was successful and have results', () => {
+ describe('query was successful but had no result to display', () => {
beforeEach(() => {
queryResult = {
rows_affected: 10,
@@ -166,10 +173,16 @@ describe('#callRenderAfterPoll', () => {
expect(sqlEditorSpy.update_msg_history).toHaveBeenCalledWith(
true,
'Some result\n\nQuery returned successfully in 0 msec.',
- false
+ true
);
});
+ it('resets the changed data store', () => {
+ callRenderAfterPoll(sqlEditorSpy, alertify, queryResult);
+
+ expect(sqlEditorSpy.reset_data_store).toHaveBeenCalled();
+ });
+
it('inform sqleditor that the query stopped running', () => {
callRenderAfterPoll(sqlEditorSpy, alertify, queryResult);
diff --git a/web/regression/javascript/sqleditor/keyboard_shortcuts_spec.js b/web/regression/javascript/sqleditor/keyboard_shortcuts_spec.js
index d0973a78..788615f2 100644
--- a/web/regression/javascript/sqleditor/keyboard_shortcuts_spec.js
+++ b/web/regression/javascript/sqleditor/keyboard_shortcuts_spec.js
@@ -14,6 +14,7 @@ import gettext from 'sources/gettext';
describe('the keyboard shortcuts', () => {
const F1_KEY = 112,
F5_KEY = 116,
+ F6_KEY = 117,
F7_KEY = 118,
F8_KEY = 119,
PERIOD_KEY = 190,
@@ -109,6 +110,14 @@ describe('the keyboard shortcuts', () => {
key_code: 'r',
},
},
+ save_data: {
+ alt : false,
+ shift: false,
+ control: false,
+ key: {
+ key_code: F6_KEY,
+ },
+ },
};
queryToolActionsSpy = jasmine.createSpyObj(queryToolActions, [
@@ -121,6 +130,7 @@ describe('the keyboard shortcuts', () => {
'executeQuery',
'executeCommit',
'executeRollback',
+ 'saveDataChanges',
]);
});
@@ -176,6 +186,42 @@ describe('the keyboard shortcuts', () => {
});
});
+ describe('F6', () => {
+ describe('when there is not a query already running', () => {
+ beforeEach(() => {
+ event.which = F6_KEY;
+ event.altKey = false;
+ event.shiftKey = false;
+ event.ctrlKey = false;
+ keyboardShortcuts.processEventQueryTool(
+ sqlEditorControllerSpy, queryToolActionsSpy, event
+ );
+ });
+
+ it('should save the changed data', () => {
+ expect(queryToolActionsSpy.saveDataChanges).toHaveBeenCalledWith(sqlEditorControllerSpy);
+ });
+
+ expectEventPropagationToStop();
+ });
+
+ describe('when the query is already running', () => {
+ it('does nothing', () => {
+ event.keyCode = F6_KEY;
+ event.altKey = false;
+ event.shiftKey = false;
+ event.ctrlKey = false;
+ sqlEditorControllerSpy.isQueryRunning.and.returnValue(true);
+
+ keyboardShortcuts.processEventQueryTool(
+ sqlEditorControllerSpy, queryToolActionsSpy, event
+ );
+
+ expect(queryToolActionsSpy.saveDataChanges).not.toHaveBeenCalled();
+ });
+ });
+ });
+
describe('F7', () => {
describe('when there is not a query already running', () => {
beforeEach(() => {
diff --git a/web/regression/runtests.py b/web/regression/runtests.py
index 1d4fa54b..bd9ffd67 100644
--- a/web/regression/runtests.py
+++ b/web/regression/runtests.py
@@ -233,6 +233,7 @@ def get_test_modules(arguments):
if test_setup.config_data['headless_chrome']:
options.add_argument("--headless")
options.add_argument("--window-size=1280,1024")
+ options.add_argument("--disable-infobars")
options.add_experimental_option('w3c', False)
driver = webdriver.Chrome(chrome_options=options)
view thread (19+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: [GSoC] Finalized First Patch
In-Reply-To: <CAFSMqn8yLA329GFHSm0Bn4cEqvd+kVoYpCAB5mRDbVF6NNaxxA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox