public inbox for [email protected]
help / color / mirror / Atom feedFrom: Yosry Muhammad <[email protected]>
To: [email protected]
To: Dave Page <[email protected]>
Subject: Re: [GSoC] Finalized First Patch
Date: Wed, 3 Jul 2019 18:21:58 +0200
Message-ID: <CAFSMqn8fjFT8CbhB5FAtkN8cd7QH0+XYtSUZmD5J1F38n1189g@mail.gmail.com> (raw)
In-Reply-To: <CAFSMqn8yLA329GFHSm0Bn4cEqvd+kVoYpCAB5mRDbVF6NNaxxA@mail.gmail.com>
References: <CAFSMqn8yLA329GFHSm0Bn4cEqvd+kVoYpCAB5mRDbVF6NNaxxA@mail.gmail.com>
I updated the patch to be in sync with recent commits made (since I sent
the patch) and fix merge conflicts.
Please find the new patch attached. Waiting for review.
Thanks.
On Mon, Jul 1, 2019 at 9:19 PM Yosry Muhammad <[email protected]> wrote:
> 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/
>
--
*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.6.patch (148.0K, 3-query_tool_automatic_mode_switch_v1.6.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 d9746ddc..b89e49e0 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 3b5a549d..2bac3708 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..6d41fd1c 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_updatable_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_updatable_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']")
@@ -179,6 +226,7 @@ class QueryToolJourneyTest(BaseFeatureTest):
self.page.toggle_open_tree_item('Databases')
self.page.toggle_open_tree_item(self.test_db)
self.page.open_query_tool()
+ self.page.wait_for_spinner_to_disappear()
def _execute_query(self, query):
self.page.fill_codemirror_area_with(query)
@@ -188,6 +236,33 @@ 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)
+ cell_classes = cell_el.get_attribute('class')
+ cell_classes = cell_classes.split(" ")
+ self.assertFalse('editable' in cell_classes)
+ ActionChains(self.driver).double_click(cell_el).perform()
+ cell_classes = cell_el.get_attribute('class')
+ cell_classes = cell_classes.split(" ")
+ return 'editable' in cell_classes
+
+ 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 e47145e2..7de93792 100644
--- a/web/pgadmin/feature_tests/view_data_dml_queries.py
+++ b/web/pgadmin/feature_tests/view_data_dml_queries.py
@@ -304,7 +304,7 @@ CREATE TABLE public.nonintpkey
)
time.sleep(0.2)
self._update_cell(cell_xpath, 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 101aff5b..b0c92634 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 d3f6d9e1..18e15ecb 100644
--- a/web/pgadmin/static/js/sqleditor/query_tool_actions.js
+++ b/web/pgadmin/static/js/sqleditor/query_tool_actions.js
@@ -156,6 +156,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 7fd19b40..491f0ca9 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 65b6c4ec..b1be514d 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 1d2796ec..6d4e46b6 100644
--- a/web/pgadmin/tools/sqleditor/__init__.py
+++ b/web/pgadmin/tools/sqleditor/__init__.py
@@ -352,6 +352,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 = \
@@ -390,6 +392,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']
@@ -406,10 +424,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
@@ -417,7 +447,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
@@ -492,26 +522,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,
@@ -700,7 +712,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(
@@ -713,32 +726,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 f7f6f897..0cdf861a 100644
--- a/web/pgadmin/tools/sqleditor/static/js/sqleditor.js
+++ b/web/pgadmin/tools/sqleditor/static/js/sqleditor.js
@@ -87,8 +87,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',
@@ -99,6 +99,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',
@@ -394,26 +395,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
@@ -657,62 +639,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:
@@ -776,16 +702,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 = {};
@@ -892,7 +818,7 @@ define('tools.querytool', [
}
var grid_options = {
- editable: true,
+ editable: is_editable,
enableAddRow: is_editable,
enableCellNavigation: true,
enableColumnReorder: false,
@@ -1109,6 +1035,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]
@@ -1164,7 +1098,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
@@ -1192,6 +1126,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, {
@@ -1200,8 +1135,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.
@@ -1250,9 +1194,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 = '';
@@ -1452,7 +1398,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);
@@ -1461,9 +1407,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'
);
},
@@ -1477,7 +1421,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
@@ -1650,6 +1594,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);
@@ -2262,7 +2211,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);
@@ -2325,12 +2274,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',
@@ -2379,8 +2322,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);
@@ -2456,13 +2398,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);
@@ -2846,9 +2787,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 {
@@ -2877,41 +2818,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),
@@ -2921,154 +2863,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
@@ -3112,7 +3088,7 @@ define('tools.querytool', [
// Save as
_save_as: function() {
- return this._save(true);
+ return this._save_file(true);
},
// Set panel title.
@@ -3199,7 +3175,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.
@@ -3244,7 +3220,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.
@@ -3253,7 +3229,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) {
@@ -3293,7 +3270,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);
}
@@ -3527,7 +3504,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 = {};
@@ -3630,10 +3607,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);
@@ -3659,8 +3637,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;
@@ -3669,8 +3675,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;
@@ -3994,6 +4000,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 64c75754..2290e2bd 100644
--- a/web/pgadmin/tools/sqleditor/utils/query_tool_preferences.py
+++ b/web/pgadmin/tools/sqleditor/utils/query_tool_preferences.py
@@ -175,6 +175,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',
@@ -302,6 +312,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 cf9ab19c..906354b8 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: <CAFSMqn8fjFT8CbhB5FAtkN8cd7QH0+XYtSUZmD5J1F38n1189g@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