public inbox for [email protected]help / color / mirror / Atom feed
[pgAdmin4][Patch] - RM 1402 - Macro support like pgAdmin3 4+ messages / 2 participants [nested] [flat]
* [pgAdmin4][Patch] - RM 1402 - Macro support like pgAdmin3 @ 2020-09-21 08:49 Khushboo Vashi <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Khushboo Vashi @ 2020-09-21 08:49 UTC (permalink / raw) To: pgadmin-hackers Hi, Please find the attached patch for RM 1402 - Macro support like pgAdmin3. Thanks, Khushboo Attachments: [application/octet-stream] RM_1402.patch (58.8K, 3-RM_1402.patch) download | inline diff: diff --git a/web/migrations/versions/398697dc9550_.py b/web/migrations/versions/398697dc9550_.py new file mode 100644 index 000000000..c9651f546 --- /dev/null +++ b/web/migrations/versions/398697dc9550_.py @@ -0,0 +1,56 @@ + +"""empty message + +Revision ID: 398697dc9550 +Revises: a091c9611d20 +Create Date: 2020-09-07 15:17:59.473879 + +""" +from pgadmin.model import db + +# revision identifiers, used by Alembic. +revision = '398697dc9550' +down_revision = 'a091c9611d20' +branch_labels = None +depends_on = None + + +def upgrade(): + db.engine.execute(""" + CREATE TABLE macros ( + id INTEGER NOT NULL, + alt BOOLEAN NOT NULL, + control BOOLEAN NOT NULL, + key VARCHAR(128) NOT NULL, + key_code INTEGER NOT NULL, + PRIMARY KEY(id) + ); + """) + + db.engine.execute(""" + CREATE TABLE user_macros ( + mid INTEGER NOT NULL, + uid INTEGER NOT NULL, + name VARCHAR(1024) NOT NULL, + sql TEXT NOT NULL, + PRIMARY KEY(mid, uid), + FOREIGN KEY(mid) REFERENCES macros (id), + FOREIGN KEY(uid) REFERENCES user (id) + ); + """) + + db.engine.execute(""" + INSERT INTO macros (id, alt, control, key, key_code) VALUES (1, false, true, '1', 49), + (2, false, true, '2', 50), (3, false, true, '3', 51), (4, false, true, '4', 52), + (5, false, true, '5', 53), (6, false, true, '6', 54), (7, false, true, '7', 55), + (8, false, true, '8', 56), (9, false, true, '9', 57), (10, false, true, '0', 48), + (11, true, false, 'F1', 112), (12, true, false, 'F2', 113), (13, true, false, 'F3', 114), + (14, true, false, 'F4', 115), (15, true, false, 'F5', 116), (16, true, false, 'F6', 117), + (17, true, false, 'F7', 118), (18, true, false, 'F8', 119), (19, true, false, 'F9', 120), + (20, true, false, 'F10', 121), (21, true, false, 'F11', 122), (22, true, false, 'F12', 123); + """) + + +def downgrade(): + # pgAdmin only upgrades, downgrade not implemented. + pass diff --git a/web/pgadmin/model/__init__.py b/web/pgadmin/model/__init__.py index b33adc062..425c9e649 100644 --- a/web/pgadmin/model/__init__.py +++ b/web/pgadmin/model/__init__.py @@ -29,7 +29,7 @@ from flask_sqlalchemy import SQLAlchemy # ########################################################################## -SCHEMA_VERSION = 26 +SCHEMA_VERSION = 27 ########################################################################## # @@ -391,3 +391,26 @@ class SharedServer(db.Model): tunnel_identity_file = db.Column(db.String(64), nullable=True) tunnel_password = db.Column(db.String(64), nullable=True) shared = db.Column(db.Boolean(), nullable=False) + + +class Macros(db.Model): + """Define a particular macro.""" + __tablename__ = 'macros' + id = db.Column(db.Integer, primary_key=True) + alt = db.Column(db.Boolean(), nullable=False) + control = db.Column(db.Boolean(), nullable=False) + key = db.Column(db.String(32), nullable=False) + key_code = db.Column(db.Integer, nullable=False) + + +class UserMacros(db.Model): + """Define the macro for a particular user.""" + __tablename__ = 'user_macros' + mid = db.Column( + db.Integer, db.ForeignKey('macros.id'), primary_key=True + ) + uid = db.Column( + db.Integer, db.ForeignKey('user.id'), primary_key=True + ) + name = db.Column(db.String(1024), nullable=False) + sql = db.Column(db.Text(), nullable=False) diff --git a/web/pgadmin/static/js/backgrid.pgadmin.js b/web/pgadmin/static/js/backgrid.pgadmin.js index e417dc78d..6394c1794 100644 --- a/web/pgadmin/static/js/backgrid.pgadmin.js +++ b/web/pgadmin/static/js/backgrid.pgadmin.js @@ -9,10 +9,10 @@ define([ 'sources/gettext', 'underscore', 'jquery', 'backbone', 'backform', 'backgrid', 'alertify', - 'moment', 'bignumber', 'sources/utils', 'sources/keyboard_shortcuts', 'sources/select2/configure_show_on_scroll', + 'moment', 'bignumber', 'codemirror', 'sources/utils', 'sources/keyboard_shortcuts', 'sources/select2/configure_show_on_scroll', 'bootstrap.datetimepicker', 'backgrid.filter', 'bootstrap.toggle', ], function( - gettext, _, $, Backbone, Backform, Backgrid, Alertify, moment, BigNumber, + gettext, _, $, Backbone, Backform, Backgrid, Alertify, moment, BigNumber, CodeMirror, commonUtils, keyboardShortcuts, configure_show_on_scroll ) { /* @@ -44,7 +44,7 @@ define([ _.extend(Backgrid.InputCellEditor.prototype.events, { 'keydown': function(e) { let preferences = pgBrowser.get_preferences_for_module('browser'); - if(keyboardShortcuts.validateShortcutKeys(preferences.add_grid_row,e)) { + if(preferences && keyboardShortcuts.validateShortcutKeys(preferences.add_grid_row,e)) { pgBrowser.keyboardNavigation.bindAddGridRow(); } else { Backgrid.InputCellEditor.prototype.saveOrCancel.apply(this, arguments); @@ -324,7 +324,7 @@ define([ events: { 'keydown': function (event) { let preferences = pgBrowser.get_preferences_for_module('browser'); - if(keyboardShortcuts.validateShortcutKeys(preferences.add_grid_row,event)) { + if(preferences && keyboardShortcuts.validateShortcutKeys(preferences.add_grid_row,event)) { pgBrowser.keyboardNavigation.bindAddGridRow(); } }, @@ -606,6 +606,95 @@ define([ }, }); + + Backgrid.Extension.ClearCell = Backgrid.Cell.extend({ + defaults: _.defaults({ + defaultClearMsg: gettext('Are you sure you wish to clear this row?'), + defaultClearTitle: gettext('Clear Row'), + }, Backgrid.Cell.prototype.defaults), + + /** @property */ + className: 'clear-cell', + events: { + 'click': 'clearRow', + }, + clearRow: function(e) { + e.preventDefault(); + var that = this; + // We will check if row is deletable or not + + var clear_msg = !_.isUndefined(this.column.get('customClearMsg')) ? + this.column.get('customClearMsg') : that.defaults.defaultClearMsg; + var clear_title = !_.isUndefined(this.column.get('customClearTitle')) ? + this.column.get('customClearTitle') : that.defaults.defaultClearTitle; + Alertify.confirm( + clear_title, + clear_msg, + function() { + that.model.set('name', null); + that.model.set('sql', null); + }, + function() { + return true; + } + ); + + }, + exitEditMode: function() { + this.$el.removeClass('editor'); + }, + initialize: function() { + Backgrid.Cell.prototype.initialize.apply(this, arguments); + }, + render: function() { + var self = this; + this.$el.empty(); + $(this.$el).attr('tabindex', 0); + if (this.model.get('name') !== null && this.model.get('sql') !== null) + this.$el.html('<i aria-label="' + gettext('Clear row') + '" class=\'fa fa-eraser\' title=\'' + gettext('Clear row') + '\'></i>'); + // Listen for Tab/Shift-Tab key + this.$el.on('keydown', function(e) { + // with keyboard navigation on space key, mark row for deletion + if (e.keyCode == 32) { + self.$el.click(); + } + var gotoCell; + if (e.keyCode == 9 || e.keyCode == 16) { + // go to Next Cell & if Shift is also pressed go to Previous Cell + gotoCell = e.shiftKey ? self.$el.prev() : self.$el.next(); + } + + if (gotoCell) { + let command = new Backgrid.Command({ + key: 'Tab', + keyCode: 9, + which: 9, + shiftKey: e.shiftKey, + }); + setTimeout(function() { + // When we have Editable Cell + if (gotoCell.hasClass('editable')) { + e.preventDefault(); + e.stopPropagation(); + self.model.trigger('backgrid:edited', self.model, + self.column, command); + } + else { + // When we have Non-Editable Cell + self.model.trigger('backgrid:edited', self.model, + self.column, command); + } + }, 20); + } + }); + + + this.delegateEvents(); + return this; + }, + }); + + Backgrid.Extension.CustomHeaderCell = Backgrid.HeaderCell.extend({ initialize: function() { // Here, we will add custom classes to header cell @@ -2081,6 +2170,84 @@ define([ }, }); + Backgrid.Extension.SqlCell = Backgrid.Extension.TextareaCell.extend({ + className: 'sql-cell', + defaults: { + lineWrapping: true, + }, + template: _.template([ + '<div data-toggle="tooltip" data-placement="top" data-html="true" title="<%- val %>"><textarea aria-label="' + gettext('SQL') +'" + style="display: none;"><%- val %></textarea><div>', + ].join('\n')), + + render: function() { + let self = this, + col = _.defaults(this.column.toJSON(), this.defaults), + model = this.model, + column = this.column, + columnName = this.column.get('name'), + editable = Backgrid.callByNeed(col.editable, column, model); + + if (this.sqlCell) { + this.sqlCell.toTextArea(); + delete this.sqlCell; + this.sqlCell = null; + } + + this.$el.empty(); + this.$el.append(this.template({ + val:this.formatter.fromRaw(model.get(columnName), model), + }) + ); + this.$el.addClass(columnName); + this.updateStateClassesMaybe(); + this.delegateEvents(); + + setTimeout(function() { + self.sqlCell = CodeMirror.fromTextArea( + (self.$el.find('textarea')[0]), { + mode: 'text/x-pgsql', + readOnly: !editable, + lineWrapping: col.lineWrapping, + screenReaderLabel: columnName, + }); + }); + + return this; + }, + enterEditMode: function () { + if (!this.$el.hasClass('editor')) this.$el.addClass('editor'); + this.sqlCell.focus(); + this.sqlCell.on('blur', this.exitEditMode.bind(this)); + }, + exitEditMode: function () { + this.$el.removeClass('editor'); + this.saveOrCancel.apply(this, arguments); + }, + saveOrCancel: function() { + var model = this.model; + var column = this.column; + if (this.sqlCell) { + var val = this.sqlCell.getTextArea().value; + var newValue = this.sqlCell.getValue(); + if (_.isUndefined(newValue)) { + model.trigger('backgrid:error', model, column, val); + } + else { + model.set(column.get('name'), newValue); + } + } + }, + remove: function() { + if (this.sqlCell) { + $(this.$el.find('[data-toggle="tooltip"]')).tooltip('dispose'); + this.sqlCell.toTextArea(); + delete this.sqlCell; + this.sqlCell = null; + } + return Backgrid.Extension.TextareaCell.prototype.remove.apply(this, arguments); + }, + }); + return Backgrid; }); diff --git a/web/pgadmin/static/js/keyboard_shortcuts.js b/web/pgadmin/static/js/keyboard_shortcuts.js index f02851720..abda8d8f2 100644 --- a/web/pgadmin/static/js/keyboard_shortcuts.js +++ b/web/pgadmin/static/js/keyboard_shortcuts.js @@ -324,15 +324,38 @@ function keyboardShortcutsQueryTool( } } } + } else { + // Macros + let macroId = this.validateMacros(sqlEditorController, event); + + if (macroId !== false) { + this._stopEventPropagation(event); + queryToolActions.executeMacro(sqlEditorController, macroId); + } } return panel_type; } +function validateMacros(sqlEditorController, event) { + let keyCode = event.which || event.keyCode; + + let macro = sqlEditorController.macros.filter(mc => + mc.alt == event.altKey && + mc.control == event.ctrlKey && + mc.key_code == keyCode); + + if (macro.length == 1) { + return macro[0].id; + } + + return false; +} + export { keyboardShortcutsDebugger as processEventDebugger, keyboardShortcutsQueryTool as processEventQueryTool, - focusDockerPanel, validateShortcutKeys, + focusDockerPanel, validateShortcutKeys, validateMacros, _stopEventPropagation, isMac, isKeyCtrlAlt, isKeyAltShift, isKeyCtrlShift, isKeyCtrlAltShift, isAltShiftBoth, isCtrlShiftBoth, isCtrlAltBoth, shortcut_key, shortcut_title, shortcut_accesskey_title, diff --git a/web/pgadmin/static/js/sqleditor/macro.js b/web/pgadmin/static/js/sqleditor/macro.js new file mode 100644 index 000000000..b761002fc --- /dev/null +++ b/web/pgadmin/static/js/sqleditor/macro.js @@ -0,0 +1,325 @@ +///////////////////////////////////////////////////////////// +// +// pgAdmin 4 - PostgreSQL Tools +// +// Copyright (C) 2013 - 2020, The pgAdmin Development Team +// This software is released under the PostgreSQL Licence +// +////////////////////////////////////////////////////////////// + +import gettext from 'sources/gettext'; +import url_for from 'sources/url_for'; +import $ from 'jquery'; +import Alertify from 'pgadmin.alertifyjs'; +import pgAdmin from 'sources/pgadmin'; +import Backform from 'pgadmin.backform'; +import macroModel from 'sources/sqleditor/macro_model'; +import axios from 'axios'; + +let MacroDialog = { + 'dialog': function(handler) { + let title = gettext('Manage Macros'); + + // Check the alertify dialog already loaded then delete it to clear + // the cache + if (Alertify.macroDialog) { + delete Alertify.macroDialog; + } + + // Create Dialog + Alertify.dialog('macroDialog', function factory() { + let $container = $('<div class=\'macro_dialog\'></div>'); + return { + main: function() { + this.set('title', '<i class="fa fa-scroll sql-icon-lg" aria-hidden="true" role="img"></i> ' + gettext('Manage Macros')); + }, + build: function() { + this.elements.content.appendChild($container.get(0)); + Alertify.pgDialogBuild.apply(this); + }, + setup: function() { + return { + buttons: [{ + text: '', + key: 112, + className: 'btn btn-primary-icon pull-left fa fa-question pg-alertify-icon-button', + attrs: { + name: 'dialog_help', + type: 'button', + label: gettext('Help'), + 'aria-label': gettext('Help'), + url: url_for('help.static', { + 'filename': 'querytool.html', + }), + }, + }, { + text: gettext('Cancel'), + key: 27, + className: 'btn btn-secondary fa fa-times pg-alertify-button', + 'data-btn-name': 'cancel', + }, { + text: gettext('Save'), + className: 'btn btn-primary fa fa-save pg-alertify-button', + 'data-btn-name': 'ok', + }], + // Set options for dialog + options: { + title: title, + //disable both padding and overflow control. + padding: !1, + overflow: !1, + model: 0, + resizable: true, + maximizable: true, + pinnable: false, + closableByDimmer: false, + modal: false, + autoReset: false, + }, + }; + }, + hooks: { + // triggered when the dialog is closed + onclose: function() { + if (this.view) { + this.macroCollectionModel.stopSession(); + this.view.model.stopSession(); + this.view.remove({ + data: true, + internal: true, + silent: true, + }); + } + }, + }, + prepare: function() { + let self = this; + $container.html(''); + // Status bar + this.statusBar = $( + '<div class=\'pg-prop-status-bar pg-el-xs-12 d-none\'>' + + ' <div class="error-in-footer"> ' + + ' <div class="d-flex px-2 py-1"> ' + + ' <div class="pr-2"> ' + + ' <i class="fa fa-exclamation-triangle text-danger" aria-hidden="true"></i> ' + + ' </div> ' + + ' <div class="alert-text" role="alert"></div> ' + + ' </div> ' + + ' </div> ' + + '</div>').appendTo($container); + + // To show progress on filter Saving/Updating on AJAX + this.showFilterProgress = $( + `<div id="show_filter_progress" class="pg-sp-container sql-editor-busy-fetching d-none"> + <div class="pg-sp-content"> + <div class="row"><div class="col-12 pg-sp-icon sql-editor-busy-icon"></div></div> + <div class="row"><div class="col-12 pg-sp-text sql-editor-busy-text">` + gettext('Loading data...') + `</div></div> + </div> + </div>` + ).appendTo($container); + + self.macroCollectionModel = macroModel(handler.transId); + + let fields = Backform.generateViewSchema( + null, self.macroCollectionModel, 'edit', null, null, true + ); + + let ManageMacroDialog = Backform.Dialog.extend({ + template: { + 'panel': _.template( + '<div role="tabpanel" tabindex="-1" class="tab-pane <%=label%> <%=tabPanelCodeClass%> pg-el-sm-12 pg-el-md-12 pg-el-lg-12 pg-el-12 fade" id="<%=cId%>"></div>' + ), + }, + render: function() { + this.cleanup(); + + var m = this.model, + controls = this.controls, + tmpls = this.template, + dialog_obj = this, + idx = (this.tabIndex * 100), + evalF = function(f, d, model) { + return (_.isFunction(f) ? !!f.apply(d, [model]) : !!f); + }; + + this.$el + .empty() + .attr('role', 'tabpanel') + .attr('class', _.result(this, 'tabPanelClassName')); + m.panelEl = this.$el; + + var tabContent = $('<div class="tab-content pg-el-sm-12 pg-el-md-12 pg-el-lg-12 pg-el-12 macro-tab"></div>') + .appendTo(this.$el); + + _.each(this.schema, function(o) { + idx++; + if (!o.version_compatible || !evalF(o.visible, o, m)) { + return; + } + var el = $((tmpls['panel'])(_.extend(o, { + 'tabIndex': idx, + 'tabPanelCodeClass': o.tabPanelCodeClass ? o.tabPanelCodeClass : '', + }))) + .appendTo(tabContent) + .removeClass('collapse').addClass('collapse'); + + o.fields.each(function(f) { + var cntr = new(f.get('control'))({ + field: f, + model: m, + dialog: dialog_obj, + tabIndex: idx, + }); + el.append(cntr.render().$el); + controls.push(cntr); + }); + }); + + tabContent.find('.tab-pane').first().addClass('active show'); + + return this; + }, + }); + + let view = self.view = new ManageMacroDialog({ + el: '<div></div>', + model: self.macroCollectionModel, + schema: fields, + }); + + self.macroCollectionModel.fetch({ + success: function() { + + // We got the latest attributes of the object. Render the view + // now. + $container.append(self.view.render().$el); + + // Enable/disable save button and show/hide statusbar based on session + self.view.listenTo(self.view.model, 'pgadmin-session:start', function() { + self.view.listenTo(self.view.model, 'pgadmin-session:invalid', function(msg) { + self.statusBar.removeClass('d-none'); + $(self.statusBar.find('.alert-text')).html(msg); + // Disable Okay button + self.__internal.buttons[2].element.disabled = true; + }); + + view.listenTo(self.view.model, 'pgadmin-session:valid', function() { + self.statusBar.addClass('d-none'); + $(self.statusBar.find('.alert-text')).html(''); + // Enable Okay button + self.__internal.buttons[2].element.disabled = false; + }); + }); + + view.listenTo(self.view.model, 'pgadmin-session:stop', function() { + view.stopListening(self.view.model, 'pgadmin-session:invalid'); + view.stopListening(self.view.model, 'pgadmin-session:valid'); + }); + + // Starts monitoring changes to model + self.view.model.startNewSession(); + + }}); + + $(this.elements.body.childNodes[0]).addClass( + 'alertify_tools_dialog_properties obj_properties' + ); + + + + }, + // Callback functions when click on the buttons of the Alertify dialogs + callback: function(e) { + let self = this; + + if (e.button.element.name == 'dialog_help') { + e.cancel = true; + pgAdmin.Browser.showHelp(e.button.element.name, e.button.element.getAttribute('url'), + null, null); + return; + } else if (e.button['data-btn-name'] === 'ok') { + e.cancel = true; // Do not close dialog + + let data = self.view.model.get('macro').toJSON(true); + + if (data == undefined || data == null) { + self.close(); + return; + } + + axios.put( + url_for('sqleditor.set_macros', { + 'trans_id': handler.transId, + }), + data + ).then(function (result) { + // Hide Progress ... + $( + self.showFilterProgress[0] + ).addClass('d-none'); + + let macroResponse = result; + + if (macroResponse.status) { + setTimeout( + function() { + // Update Macro Menu + let macros = self.view.model.get('macro').toJSON().filter(m => m.name !== undefined || m.name !== null); + handler.macros = macros; + var str = ` + <li> + <a class="dropdown-item" id="btn-manage-macros" href="#" tabindex="0"> + <span> Manage Macros... </span> + </a> + </li> + <li class="dropdown-divider"></li>`; + _.each(macros, function(m) { + if (m.name) { + str += `<li> + <a class="dropdown-item btn-macro" data-macro-id="`+ m.id +`" href="#" tabindex="0"> + <span>` + m.name + `</span> + <span> (` + m.key_label + `) </span> + </a> + </li>`; + } + }); + + $($.find('div.btn-group.mr-1.user_macros ul.dropdown-menu')).html($(str)); + + self.close(); // Close the dialog now + Alertify.success(gettext('Macro updated successfully')); + }, 10 + ); + } else { + Alertify.alert( + gettext('Validation Error'), + macroResponse.result + ); + } + + }).catch(function (error) { + // Hide Progress ... + $( + self.showFilterProgress[0] + ).addClass('d-none'); + + setTimeout( + function() { + Alertify.error(error.response.data.errormsg); + }, 10 + ); + }); + + } else { + self.close(); + } + }, + }; + }); + + Alertify.macroDialog(title).resizeTo(pgAdmin.Browser.stdW.calc(pgAdmin.Browser.stdW.lg), + pgAdmin.Browser.stdH.calc(pgAdmin.Browser.stdH.lg)); + }, +}; + +module.exports = MacroDialog; diff --git a/web/pgadmin/static/js/sqleditor/macro_model.js b/web/pgadmin/static/js/sqleditor/macro_model.js new file mode 100644 index 000000000..c66d85c6e --- /dev/null +++ b/web/pgadmin/static/js/sqleditor/macro_model.js @@ -0,0 +1,213 @@ +///////////////////////////////////////////////////////////// +// +// pgAdmin 4 - PostgreSQL Tools +// +// Copyright (C) 2013 - 2020, The pgAdmin Development Team +// This software is released under the PostgreSQL Licence +// +////////////////////////////////////////////////////////////// + +import gettext from 'sources/gettext'; +import pgAdmin from 'sources/pgadmin'; +import Backform from 'pgadmin.backform'; +import Backgrid from 'pgadmin.backgrid'; +import url_for from 'sources/url_for'; +import $ from 'jquery'; +import _ from 'underscore'; +import Alertify from 'pgadmin.alertifyjs'; + +export default function macroModel(transId) { + + let MacroModel = pgAdmin.Browser.DataModel.extend({ + idAttribute: 'id', + defaults: { + id: undefined, + key: undefined, + name: undefined, + sql: undefined, + key_label: undefined, + }, + schema: [{ + id: 'key_label', + name: 'key_label', + label: gettext('Key'), + type: 'text', + cell: 'string', + editable: false, + cellHeaderClasses: 'width_percent_10', + headerCell: Backgrid.Extension.CustomHeaderCell, + disabled: false, + }, { + id: 'name', + name: 'name', + label: gettext('Name'), + cell: 'string', + type: 'text', + editable: true, + cellHeaderClasses: 'width_percent_20', + headerCell: Backgrid.Extension.CustomHeaderCell, + disabled: false, + }, { + id: 'sql', + name: 'sql', + label: gettext('SQL'), + cell: Backgrid.Extension.SqlCell, + type: 'multiline', + control: Backform.SqlCodeControl, + editable: true, + cellHeaderClasses: 'width_percent_70', + headerCell: Backgrid.Extension.CustomHeaderCell, + disabled: false, + }, + ], + validate: function() { + return null; + }, + }); + + let MacroCollectionModel = pgAdmin.Browser.DataModel.extend({ + defaults: { + macro: undefined, + }, + urlRoot: url_for('sqleditor.get_macros', {'trans_id': transId}), + schema: [{ + id: 'macro', + name: 'macro', + label: gettext('Macros'), + model: MacroModel, + editable: true, + type: 'collection', + control: Backform.SubNodeCollectionControl.extend({ + showGridControl: function(data) { + var self = this, + gridBody = $('<div class=\'pgadmin-control-group backgrid form-group pg-el-12 object subnode\'></div>'); + + var subnode = data.subnode.schema ? data.subnode : data.subnode.prototype, + gridSchema = Backform.generateGridColumnsFromModel( + data.node_info, subnode, this.field.get('mode'), data.columns, data.schema_node + ); + + // Clean up existing grid if any (in case of re-render) + if (self.grid) { + self.grid.remove(); + } + + // Set visibility of Add button + if (data.disabled || data.canAdd == false) { + $(gridBody).find('button.add').remove(); + } + + // Insert Clear Cell into Grid + gridSchema.columns.unshift({ + name: 'pg-backform-clear', + label: '<i aria-label="' + gettext('Clear row') + '" class="fa fa-eraser" title="' + gettext('Clear row') + '"></i>', + cell: Backgrid.Extension.ClearCell, + editable: false, + cell_priority: -1, + sortable: false, + headerCell: Backgrid.Extension.CustomHeaderCell.extend({ + className: 'header-icon-cell', + events: { + 'click': 'clearrAll', + }, + clearrAll: function(e) { + e.preventDefault(); + var that = this; + // We will check if row is deletable or not + + Alertify.confirm( + gettext('Clear All Rows'), + gettext('Are you sure you wish to clear all rows?'), + function() { + _.each(that.collection.toJSON(), function(m) { + that.collection.get(m.id).set({'name': null, 'sql': null}); + }); + }, + function() { + return true; + } + ); + }, + render: function() { + this.$el.empty(); + var column = this.column; + var label = $('<button type="button" title="' + gettext('Clear row') + '" aria-label="Clear row" aria-expanded="false" tabindex="0">').html(column.get('label')).append('<span class=\'sort-caret\' aria-hidden=\'true\'></span>'); + + this.$el.append(label); + this.$el.addClass(column.get('name')); + this.$el.addClass(column.get('direction')); + this.$el.attr('role', 'columnheader'); + this.$el.attr('aria-label', 'columnheader'); + this.$el.attr('alt', 'columnheader'); + this.delegateEvents(); + return this; + + }, + }), + }); + + + var collection = self.model.get(data.name); + + if (!collection) { + collection = new(pgAdmin.Browser.Node.Collection)(null, { + handler: self.model.handler || self.model, + model: data.model, + top: self.model.top || self.model, + silent: true, + }); + self.model.set(data.name, collection, { + silent: true, + }); + } + + var cellEditing = function(args) { + var ctx = this, + cell = args[0]; + // Search for any other rows which are open. + this.each(function(m) { + // Check if row which we are about to close is not current row. + if (cell.model != m) { + var idx = ctx.indexOf(m); + if (idx > -1) { + var row = grid.body.rows[idx], + rowEditCell = row.$el.find('.subnode-edit-in-process').parent(); + // Only close row if it's open. + if (rowEditCell.length > 0) { + var event = new Event('click'); + rowEditCell[0].dispatchEvent(event); + } + } + } + }); + }; + // Listen for any row which is about to enter in edit mode. + collection.on('enteringEditMode', cellEditing, collection); + + // Initialize a new Grid instance + var grid = self.grid = new Backgrid.Grid({ + columns: gridSchema.columns, + collection: collection, + row: this.row, + className: 'backgrid table presentation table-bordered table-noouter-border table-hover', + }); + + // Render subNode grid + var subNodeGrid = grid.render().$el; + + var $dialog = gridBody.append(subNodeGrid); + + return $dialog; + }, + }), + columns: ['key_label', 'name', 'sql'], + visible: true, + }], + validate: function() { + return null; + }, + }); + + let model = new MacroCollectionModel(); + return model; +} diff --git a/web/pgadmin/static/js/sqleditor/query_tool_actions.js b/web/pgadmin/static/js/sqleditor/query_tool_actions.js index 98297cf18..81937058c 100644 --- a/web/pgadmin/static/js/sqleditor/query_tool_actions.js +++ b/web/pgadmin/static/js/sqleditor/query_tool_actions.js @@ -39,6 +39,11 @@ let queryToolActions = { $('.sql-editor-message').html(''); }, + executeMacro: function (sqlEditorController, MacroId) { + this._clearMessageTab(); + sqlEditorController.check_data_changes_to_execute_query(null, false, MacroId); + }, + executeQuery: function (sqlEditorController) { this._clearMessageTab(); sqlEditorController.check_data_changes_to_execute_query(); diff --git a/web/pgadmin/static/scss/_backgrid.overrides.scss b/web/pgadmin/static/scss/_backgrid.overrides.scss index 4fc1f5f19..35840cfae 100644 --- a/web/pgadmin/static/scss/_backgrid.overrides.scss +++ b/web/pgadmin/static/scss/_backgrid.overrides.scss @@ -359,3 +359,31 @@ table tr th button { } } } + +.backgrid .sql-cell .CodeMirror-scroll { + overflow: hidden !important; +} + +.backgrid .sql-cell .cm-s-default { + height: 50px !important; +} + +.backgrid .sql-cell .CodeMirror-hscrollbar > div { + width: 100% !important; +} + +.backgrid .sql-cell .CodeMirror-hscrollbar { + overflow-x: hidden !important; +} + +.backgrid .sql-cell .CodeMirror-vscrollbar { + overflow-y: hidden !important; +} + +.backgrid .sql-cell .CodeMirror-sizer { + padding-bottom: 0 !important; +} + +.backgrid .sql-cell .CodeMirror-scrollbar-filler { + display: none !important; +} diff --git a/web/pgadmin/tools/datagrid/__init__.py b/web/pgadmin/tools/datagrid/__init__.py index f5fc78c4f..1bd841f20 100644 --- a/web/pgadmin/tools/datagrid/__init__.py +++ b/web/pgadmin/tools/datagrid/__init__.py @@ -33,6 +33,7 @@ from pgadmin.settings import get_setting from pgadmin.browser.utils import underscore_unescape from pgadmin.utils.exception import ObjectGone from pgadmin.utils.constants import MIMETYPE_APP_JS +from pgadmin.tools.sqleditor.utils.macros import get_user_macros MODULE_NAME = 'datagrid' @@ -274,6 +275,8 @@ def panel(trans_id): layout = get_setting('SQLEditor/Layout') + macros = get_user_macros() + return render_template( "datagrid/index.html", _=gettext, @@ -286,6 +289,7 @@ def panel(trans_id): bgcolor=bgcolor, fgcolor=fgcolor, layout=layout, + macros=macros ) diff --git a/web/pgadmin/tools/datagrid/templates/datagrid/index.html b/web/pgadmin/tools/datagrid/templates/datagrid/index.html index 097a42e91..a0eebc8cf 100644 --- a/web/pgadmin/tools/datagrid/templates/datagrid/index.html +++ b/web/pgadmin/tools/datagrid/templates/datagrid/index.html @@ -377,6 +377,31 @@ <i class="fa fa-download sql-icon-lg" aria-hidden="true" role="img"></i> </button> </div> + <div class="btn-group mr-1 user_macros" role="group" aria-label=""> + <button id="btn-macro-dropdown" type="button" class="btn btn-sm btn-primary-icon dropdown-toggle" + data-toggle="dropdown" aria-haspopup="true" aria-expanded="false" + aria-label="{{ _('Macros') }}" title="{{ _('Macros') }}" tabindex="0"> + <i class="fa fa-scroll sql-icon-lg" aria-hidden="true" role="img"></i> + </button> + <ul class="dropdown-menu"> + <li> + <a class="dropdown-item" id="btn-manage-macros" href="#" tabindex="0"> + <span> {{ _('Manage Macros...') }} </span> + </a> + </li> + {% if macros|length > 0 %} + <li class="dropdown-divider"></li> + {% endif %} + {% for i in macros %} + <li> + <a class="dropdown-item btn-macro" data-macro-id="{{ i.id }}" href="#" tabindex="0"> + <span> {{ _(i.name) }} </span> + <span> ({{ i.key_label }}) </span> + </a> + </li> + {% endfor %} + </ul> + </div> </div> <div class="connection_status_wrapper d-flex"> @@ -459,7 +484,8 @@ require(['sources/generated/browser_nodes', 'sources/generated/codemirror', 'sou sqlEditorController.start( {{ uniqueId }}, {{ url_params|safe}}, - '{{ layout|safe }}' + '{{ layout|safe }}', + {{ macros|safe }} ); // If opening from schema diff, set the generated script to the SQL Editor diff --git a/web/pgadmin/tools/sqleditor/__init__.py b/web/pgadmin/tools/sqleditor/__init__.py index c62bc7cbc..9d0903b25 100644 --- a/web/pgadmin/tools/sqleditor/__init__.py +++ b/web/pgadmin/tools/sqleditor/__init__.py @@ -32,7 +32,7 @@ from pgadmin.tools.sqleditor.utils.update_session_grid_transaction import \ from pgadmin.utils import PgAdminModule from pgadmin.utils import get_storage_directory from pgadmin.utils.ajax import make_json_response, bad_request, \ - success_return, internal_server_error + success_return, internal_server_error, make_response as ajax_response from pgadmin.utils.driver import get_driver from pgadmin.utils.menu import MenuItem from pgadmin.utils.exception import ConnectionLost, SSHTunnelConnectionLost,\ @@ -46,6 +46,8 @@ from pgadmin.tools.sqleditor.utils.filter_dialog import FilterDialog from pgadmin.tools.sqleditor.utils.query_history import QueryHistory from pgadmin.utils.constants import MIMETYPE_APP_JS, SERVER_CONNECTION_CLOSED,\ ERROR_MSG_TRANS_ID_NOT_FOUND +from pgadmin.tools.sqleditor.utils.macros import get_macros,\ + get_user_macros, set_macros MODULE_NAME = 'sqleditor' @@ -109,6 +111,9 @@ class SqlEditorModule(PgAdminModule): 'sqleditor.get_query_history', 'sqleditor.add_query_history', 'sqleditor.clear_query_history', + 'sqleditor.get_macro', + 'sqleditor.get_macros', + 'sqleditor.set_macros' ] def register_preferences(self): @@ -1547,3 +1552,46 @@ def get_query_history(trans_id): check_transaction_status(trans_id) return QueryHistory.get(current_user.id, trans_obj.sid, conn.db) + + [email protected]( + '/get_macros/<int:trans_id>', + methods=["GET"], endpoint='get_macros' +) [email protected]( + '/get_macros/<int:macro_id>/<int:trans_id>', + methods=["GET"], endpoint='get_macro' +) +@login_required +def macros(trans_id, macro_id=None, json_resp=True): + """ + This method is used to get all the columns for data sorting dialog. + + Args: + trans_id: unique transaction id + macro_id: Macro id + """ + + status, error_msg, conn, trans_obj, session_ob = \ + check_transaction_status(trans_id) + + return get_macros(macro_id, json_resp) + + [email protected]( + '/set_macros/<int:trans_id>', + methods=["PUT"], endpoint='set_macros' +) +@login_required +def update_macros(trans_id): + """ + This method is used to get all the columns for data sorting dialog. + + Args: + trans_id: unique transaction id + """ + + status, error_msg, conn, trans_obj, session_ob = \ + check_transaction_status(trans_id) + + return set_macros() diff --git a/web/pgadmin/tools/sqleditor/static/css/sqleditor.css b/web/pgadmin/tools/sqleditor/static/css/sqleditor.css index 00543ff69..5f673b04d 100644 --- a/web/pgadmin/tools/sqleditor/static/css/sqleditor.css +++ b/web/pgadmin/tools/sqleditor/static/css/sqleditor.css @@ -395,3 +395,24 @@ input.editor-checkbox:focus { .hide-vertical-scrollbar { overflow-y: hidden; } + + +/* Macros */ + +.macro-tab { + top: 0px !important; +} + +.macro-tab .tab-pane { + padding: 0px !important; +} + +.CodeMirror { + overflow-y: auto; + resize: vertical; +} +.sql-cell > div { + overflow-y: auto; + resize: vertical; +} + diff --git a/web/pgadmin/tools/sqleditor/static/js/sqleditor.js b/web/pgadmin/tools/sqleditor/static/js/sqleditor.js index ddd9ad701..203e3a573 100644 --- a/web/pgadmin/tools/sqleditor/static/js/sqleditor.js +++ b/web/pgadmin/tools/sqleditor/static/js/sqleditor.js @@ -43,6 +43,7 @@ define('tools.querytool', [ 'tools/datagrid/static/js/datagrid_panel_title', 'sources/window', 'sources/is_native', + 'sources/sqleditor/macro', 'sources/../bundle/slickgrid', 'pgadmin.file_manager', 'slick.pgadmin.formatters', @@ -57,7 +58,7 @@ define('tools.querytool', [ GeometryViewer, historyColl, queryHist, querySources, keyboardShortcuts, queryToolActions, queryToolNotifications, Datagrid, modifyAnimation, calculateQueryRunTime, callRenderAfterPoll, queryToolPref, queryTxnStatus, csrfToken, panelTitleFunc, - pgWindow, isNative) { + pgWindow, isNative, MacroHandler) { /* Return back, this has been called more than once */ if (pgAdmin.SqlEditor) return pgAdmin.SqlEditor; @@ -149,6 +150,9 @@ define('tools.querytool', [ // Transaction control 'click #btn-commit': 'on_commit_transaction', 'click #btn-rollback': 'on_rollback_transaction', + // Manage Macros + 'click #btn-manage-macros': 'on_manage_macros', + 'click .btn-macro': 'on_execute_macro', }, reflectPreferences: function() { @@ -2038,8 +2042,30 @@ define('tools.querytool', [ queryToolActions.executeRollback(this.handler); }, + + // Callback function for manage macros button click. + on_manage_macros: function() { + var self = this; + + // Trigger the show_filter signal to the SqlEditorController class + self.handler.trigger( + 'pgadmin-sqleditor:button:manage_macros', + self, + self.handler + ); + }, + + // Callback function for manage macros button click. + on_execute_macro: function(e) { + let macroId = $(e.currentTarget).data('macro-id'); + this.handler.history_query_source = QuerySources.EXECUTE; + queryToolActions.executeMacro(this.handler, macroId); + }, + }); + + /* Defining controller class for data grid, which actually * perform the operations like executing the sql query, poll the result, * render the data in the grid, Save/Refresh the data etc... @@ -2308,7 +2334,7 @@ define('tools.querytool', [ * call the render method of the grid view to render the slickgrid * header and loading icon and start execution of the sql query. */ - start: function(transId, url_params, layout) { + start: function(transId, url_params, layout, macros) { var self = this; self.is_query_tool = url_params.is_query_tool==='true'?true:false; @@ -2333,6 +2359,7 @@ define('tools.querytool', [ layout: layout, }); self.transId = self.gridView.transId = transId; + self.macros = self.gridView.macros = macros; self.gridView.current_file = undefined; @@ -2474,12 +2501,14 @@ define('tools.querytool', [ self.on('pgadmin-sqleditor:unindent_selected_code', self._unindent_selected_code, self); // Format self.on('pgadmin-sqleditor:format_sql', self._format_sql, self); + self.on('pgadmin-sqleditor:button:manage_macros', self._manage_macros, self); + self.on('pgadmin-sqleditor:button:execute_macro', self._execute_macro, self); window.parent.$(window.parent.document).on('pgadmin-sqleditor:rows-copied', self._copied_in_other_session); }, // Checks if there is any dirty data in the grid before executing a query - check_data_changes_to_execute_query: function(explain_prefix=null, shouldReconnect=false) { + check_data_changes_to_execute_query: function(explain_prefix=null, shouldReconnect=false, macroId=undefined) { var self = this; // Check if the data grid has any changes before running query @@ -2492,7 +2521,10 @@ define('tools.querytool', [ gettext('The data has been modified, but not saved. Are you sure you wish to discard the changes?'), function() { // The user does not want to save, just continue - if(self.is_query_tool) { + if (macroId !== undefined) { + self._execute_macro_query(explain_prefix, shouldReconnect, macroId); + } + else if(self.is_query_tool) { self._execute_sql_query(explain_prefix, shouldReconnect); } else { @@ -2508,7 +2540,10 @@ define('tools.querytool', [ cancel: gettext('No'), }); } else { - if(self.is_query_tool) { + if (macroId !== undefined) { + self._execute_macro_query(explain_prefix, shouldReconnect, macroId); + } + else if(self.is_query_tool) { self._execute_sql_query(explain_prefix, shouldReconnect); } else { @@ -2602,6 +2637,37 @@ define('tools.querytool', [ }); }, + // Executes sql query for macroin the editor in Query Tool mode + _execute_macro_query: function(explain_prefix, shouldReconnect, macroId) { + var self = this; + + self.has_more_rows = false; + self.fetching_rows = false; + + $.ajax({ + url: url_for('sqleditor.get_macro', {'macro_id': macroId, 'trans_id': self.transId}), + method: 'GET', + contentType: 'application/json', + dataType: 'json', + }) + .done(function(res) { + if (res) { + // Replace the place holder + let query = res.sql.replaceAll('$SELECTION$', self.gridView.query_tool_obj.getSelection()); + + const executeQuery = new ExecuteQuery.ExecuteQuery(self, pgAdmin.Browser.UserManagement); + executeQuery.poll = pgBrowser.override_activity_event_decorator(executeQuery.poll).bind(executeQuery); + executeQuery.execute(query, explain_prefix, shouldReconnect); + } else { + // Let it be for now + } + }) + .fail(function() { + /* failure should not be ignored */ + }); + + }, + // Executes sql query in the editor in Query Tool mode _execute_sql_query: function(explain_prefix, shouldReconnect) { var self = this, sql = ''; @@ -3968,6 +4034,7 @@ define('tools.querytool', [ $('#btn-file-menu-dropdown').prop('disabled', mode_disabled); $('#btn-find').prop('disabled', mode_disabled); $('#btn-find-menu-dropdown').prop('disabled', mode_disabled); + $('#btn-macro-dropdown').prop('disabled', mode_disabled); if (this.is_query_tool) { @@ -4375,6 +4442,24 @@ define('tools.querytool', [ }); }, + // This function will open the manage macro dialog + _manage_macros: function() { + let self = this; + + /* When server is disconnected and connected, connection is lost, + * To reconnect pass true + */ + MacroHandler.dialog(self); + }, + + // This function will open the manage macro dialog + _execute_macro: function() { + + queryToolActions.executeMacro(this.handler); + + }, + + isQueryRunning: function() { return is_query_running; }, diff --git a/web/pgadmin/tools/sqleditor/tests/test_macros.py b/web/pgadmin/tools/sqleditor/tests/test_macros.py new file mode 100644 index 000000000..0375f01a2 --- /dev/null +++ b/web/pgadmin/tools/sqleditor/tests/test_macros.py @@ -0,0 +1,121 @@ +########################################################################## +# +# pgAdmin 4 - PostgreSQL Tools +# +# Copyright (C) 2013 - 2020, 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 +import random + + +class TestMacros(BaseTestGenerator): + """ This class will test the query tool polling. """ + scenarios = [ + ('Get all macros', + dict( + url='get_macros', + method='get' + )), + ('Set Macros', + dict( + url='set_macros', + method='put', + operation='update', + data={ + 'changed': [ + {'id': 1, + 'name': 'Test Macro 1', + 'sql': 'SELECT 1;' + }, + {'id': 2, + 'name': 'Test Macro 2', + 'sql': 'SELECT 2;' + }, + {'id': 3, + 'name': 'Test Macro 3', + 'sql': 'SELECT 3;' + }, + ] + } + )), + ('Clear Macros', + dict( + url='set_macros', + method='put', + operation='clear', + data={ + 'changed': [ + {'id': 1, + 'name': '', + 'sql': '' + }, + {'id': 3, + 'name': '', + 'sql': '' + }, + ] + } + )) + ] + + def setUp(self): + """ This function will check messages return by query tool polling. """ + 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.") + + # Initialize query tool + self.trans_id = str(random.randint(1, 9999999)) + url = '/datagrid/initialize/query_tool/{0}/{1}/{2}/{3}'.format( + self.trans_id, utils.SERVER_GROUP, self.server_id, self.db_id) + response = self.tester.post(url) + self.assertEqual(response.status_code, 200) + + def runTest(self): + url = '/sqleditor/{0}/{1}'.format(self.url, self.trans_id) + + if self.method == 'get': + response = self.tester.get(url) + self.assertEqual(response.status_code, 200) + + response_data = json.loads(response.data.decode('utf-8')) + self.assertEqual(len(response_data['macro']), 22) + else: + response = self.tester.put(url, + data=json.dumps(self.data), + follow_redirects=True) + self.assertEqual(response.status_code, 200) + + for m in self.data['changed']: + url = '/sqleditor/get_macros/{0}/{1}'.format(m['id'], + self.trans_id) + response = self.tester.get(url) + + if self.operation == 'clear': + self.assertEqual(response.status_code, 410) + else: + self.assertEqual(response.status_code, 200) + + response_data = json.loads(response.data.decode('utf-8')) + self.assertEqual(response_data['name'], m['name']) + self.assertEqual(response_data['sql'], m['sql']) + + def tearDown(self): + # Disconnect the database + database_utils.disconnect_database(self, self.server_id, self.db_id) diff --git a/web/pgadmin/tools/sqleditor/utils/macros.py b/web/pgadmin/tools/sqleditor/utils/macros.py new file mode 100644 index 000000000..5dd183c30 --- /dev/null +++ b/web/pgadmin/tools/sqleditor/utils/macros.py @@ -0,0 +1,165 @@ +########################################################################## +# +# pgAdmin 4 - PostgreSQL Tools +# +# Copyright (C) 2013 - 2020, The pgAdmin Development Team +# This software is released under the PostgreSQL Licence +# +########################################################################## + +"""Handle Macros for SQL Editor.""" + +import simplejson as json +from flask_babelex import gettext +from flask import current_app, request +from flask_security import login_required, current_user +from pgadmin.utils.ajax import make_response as ajax_response,\ + make_json_response +from pgadmin.model import db, Macros, UserMacros + + +def get_macros(macro_id, json_resp): + """ + This method is used to get all the macros/specific macro. + :param macro_id: Macro ID + :param json_resp: Set True to return json response + """ + if macro_id: + macro = UserMacros.query.filter_by(mid=macro_id, + uid=current_user.id).first() + if macro is None: + return make_json_response( + status=410, + success=0, + errormsg=gettext("Macro not found.") + ) + else: + return ajax_response( + response={'id': macro.mid, + 'name': macro.name, + 'sql': macro.sql}, + status=200 + ) + else: + macros = db.session.query(Macros.id, Macros.alt, Macros.control, + Macros.key, Macros.key_code, + UserMacros.name, UserMacros.sql + ).outerjoin( + UserMacros, Macros.id == UserMacros.mid).all() + + data = [] + + for m in macros: + key_label = 'Ctrl + ' + m[3] if m[2] is True else 'Alt + ' + m[3] + data.append({'id': m[0], 'alt': m[1], + 'control': m[2], 'key': m[3], + 'key_code': m[4], 'name': m[5], + 'sql': m[6], + 'key_label': key_label}) + + if not json_resp: + return data + + return ajax_response( + response={'macro': data}, + status=200 + ) + + +def get_user_macros(): + """ + This method is used to get all the user macros. + """ + + macros = db.session.query(UserMacros.name, Macros.id, + Macros.alt, Macros.control, + Macros.key, Macros.key_code + ).outerjoin( + Macros, UserMacros.mid == Macros.id).all() + + data = [] + + for m in macros: + key_label = 'Ctrl + ' + m[4] if m[3] is True else 'Alt + ' + m[4] + data.append({'name': m[0], 'id': m[1], 'key': m[4], + 'key_label': key_label, 'alt': 1 if m[2] else 0, + 'control': 1 if m[3] else 0, 'key_code': m[5]}) + + return data + + +def set_macros(): + """ + This method is used to update the user defined macros. + """ + + data = request.form if request.form else json.loads( + request.data, encoding='utf-8' + ) + + if 'changed' not in data: + return make_json_response( + success=1, + info=gettext('Nothing to update.') + ) + + for m in data['changed']: + if m['id']: + macro = UserMacros.query.filter_by( + uid=current_user.id, + mid=m['id']).first() + if macro: + status, msg = update_macro(m, macro) + else: + status, msg = create_macro(m) + + if not status: + return make_json_response( + status=410, success=0, errormsg=msg + ) + return ajax_response(status=200) + + +def create_macro(macro): + """ + This method is used to create the user defined macros. + :param macro: macro + """ + try: + new_macro = UserMacros( + uid=current_user.id, + mid=macro['id'], + name=macro['name'], + sql=macro['sql'] + ) + db.session.add(new_macro) + db.session.commit() + except Exception as e: + db.session.rollback() + return False, str(e) + + return True, None + + +def update_macro(data, macro): + """ + This method is used to clear/update the user defined macros. + :param data: updated macro data + :param macro: macro + """ + name = data['name'] if 'name' in data else None + sql = data['sql'] if 'sql' in data else None + try: + if (name is None and sql is None) or (name == '' and sql == ''): + db.session.delete(macro) + else: + if name: + macro.name = name + if sql: + macro.sql = sql + db.session.commit() + except Exception as e: + db.session.rollback() + return False, str(e) + + return True, None diff --git a/web/regression/javascript/sqleditor/keyboard_shortcuts_spec.js b/web/regression/javascript/sqleditor/keyboard_shortcuts_spec.js index c8415a089..04365bf48 100644 --- a/web/regression/javascript/sqleditor/keyboard_shortcuts_spec.js +++ b/web/regression/javascript/sqleditor/keyboard_shortcuts_spec.js @@ -18,7 +18,8 @@ describe('the keyboard shortcuts', () => { F7_KEY = 118, F8_KEY = 119, PERIOD_KEY = 190, - FWD_SLASH_KEY = 191; + FWD_SLASH_KEY = 191, + C1_KEY = 49; let sqlEditorControllerSpy, event, queryToolActionsSpy; beforeEach(() => { @@ -120,6 +121,19 @@ describe('the keyboard shortcuts', () => { }, }; + sqlEditorControllerSpy.macros = [ + { + alt: false, + control: true, + id: 1, + key: '1', + key_code: C1_KEY, + key_label: 'Ctrl + 1', + name: 'C1', + sql: 'Select 1;', + }, + ]; + queryToolActionsSpy = jasmine.createSpyObj(queryToolActions, [ 'explainAnalyze', 'explain', @@ -131,6 +145,7 @@ describe('the keyboard shortcuts', () => { 'executeCommit', 'executeRollback', 'saveDataChanges', + 'executeMacro', ]); }); @@ -667,6 +682,47 @@ describe('the keyboard shortcuts', () => { }); }); + describe('Macro Ctrl + 1', () => { + describe('when there is not a query already running', () => { + beforeEach(() => { + event.which = C1_KEY; + event.altKey = false; + event.shiftKey = false; + event.ctrlKey = true; + + keyboardShortcuts.processEventQueryTool( + sqlEditorControllerSpy, queryToolActionsSpy, event + ); + }); + + it('should execute the macro', () => { + expect(queryToolActionsSpy.executeMacro).toHaveBeenCalledWith(sqlEditorControllerSpy, + sqlEditorControllerSpy.macros[0].id); + }); + + it('should stop event propagation', () => { + expect(event.preventDefault).toHaveBeenCalled(); + }); + }); + + describe('when the query is already running', () => { + it('does nothing', () => { + event.keyCode = C1_KEY; + event.altKey = false; + event.shiftKey = false; + event.ctrlKey = true; + + sqlEditorControllerSpy.isQueryRunning.and.returnValue(true); + + keyboardShortcuts.processEventQueryTool( + sqlEditorControllerSpy, queryToolActionsSpy, event + ); + + expect(queryToolActionsSpy.executeMacro).not.toHaveBeenCalled(); + }); + }); + }); + function expectEventPropagationToStop() { describe('stops all event propogation', () => { ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgAdmin4][Patch] - RM 1402 - Macro support like pgAdmin3 @ 2020-09-23 12:12 Akshay Joshi <[email protected]> parent: Khushboo Vashi <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Akshay Joshi @ 2020-09-23 12:12 UTC (permalink / raw) To: Khushboo Vashi <[email protected]>; +Cc: pgadmin-hackers Hi Khushboo Following are the GUI review comments: - If we provide anyone value either Name or SQL it throws Alertify error message with 'name' or 'SQL'. The error message should be correct. [image: Name_Error.png] - We should not enable the 'Save' button if the value of Name or SQL is not entered by the user. Either both values should be there or both should be blank. - Click on anywhere inside the empty cell where the clear row icon is not visible, it pops up the message to clear the row which is already empty. [image: Clear_Row.png] - Save any macro and then open the "Manage Macros..." dialog again click on the SQL control for the existing macro, cursor is not at the appropriate place. [image: CodeMirror_Error.png] - Macros are not working for multiple users. Create some macro from User1 and then log in using User2, macros created by User1 are visible to User2 and when I try to update it is not updating. On Mon, Sep 21, 2020 at 2:19 PM Khushboo Vashi < [email protected]> wrote: > Hi, > > Please find the attached patch for RM 1402 - Macro support like pgAdmin3. > > Thanks, > Khushboo > > -- *Thanks & Regards* *Akshay Joshi* *pgAdmin Hacker | Sr. Software Architect* *EDB Postgres <http://edbpostgres.com>* *Mobile: +91 976-788-8246* Attachments: [image/png] Name_Error.png (8.0K, 3-Name_Error.png) download | view image [image/png] Clear_Row.png (19.4K, 4-Clear_Row.png) download | view image [image/png] CodeMirror_Error.png (35.1K, 5-CodeMirror_Error.png) download | view image ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgAdmin4][Patch] - RM 1402 - Macro support like pgAdmin3 @ 2020-09-28 08:47 Khushboo Vashi <[email protected]> parent: Akshay Joshi <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Khushboo Vashi @ 2020-09-28 08:47 UTC (permalink / raw) To: Akshay Joshi <[email protected]>; +Cc: pgadmin-hackers Hi, Please find the attached updated patch. Thanks, Khushboo On Wed, Sep 23, 2020 at 5:42 PM Akshay Joshi <[email protected]> wrote: > Hi Khushboo > > Following are the GUI review comments: > > - If we provide anyone value either Name or SQL it throws Alertify > error message with 'name' or 'SQL'. The error message should be correct. > > [image: Name_Error.png] > Fixed. > > - We should not enable the 'Save' button if the value of Name or SQL > is not entered by the user. Either both values should be there or both > should be blank. > > Fixed. > > - Click on anywhere inside the empty cell where the clear row icon is > not visible, it pops up the message to clear the row which is already > empty. > > [image: Clear_Row.png] > Fixed. > > - Save any macro and then open the "Manage Macros..." dialog again > click on the SQL control for the existing macro, cursor is not at the > appropriate place. > > [image: CodeMirror_Error.png] > Fixed. > > - Macros are not working for multiple users. Create some macro from > User1 and then log in using User2, macros created by User1 are visible to > User2 and when I try to update it is not updating. > > Fixed. > > On Mon, Sep 21, 2020 at 2:19 PM Khushboo Vashi < > [email protected]> wrote: > >> Hi, >> >> Please find the attached patch for RM 1402 - Macro support like pgAdmin3. >> >> Thanks, >> Khushboo >> >> > > -- > *Thanks & Regards* > *Akshay Joshi* > *pgAdmin Hacker | Sr. Software Architect* > *EDB Postgres <http://edbpostgres.com>* > > *Mobile: +91 976-788-8246* > Attachments: [image/png] Name_Error.png (8.0K, 3-Name_Error.png) download | view image [image/png] Clear_Row.png (19.4K, 4-Clear_Row.png) download | view image [image/png] CodeMirror_Error.png (35.1K, 5-CodeMirror_Error.png) download | view image [application/octet-stream] RM_1402_v1.patch (60.3K, 6-RM_1402_v1.patch) download | inline diff: diff --git a/web/migrations/versions/398697dc9550_.py b/web/migrations/versions/398697dc9550_.py new file mode 100644 index 000000000..c9651f546 --- /dev/null +++ b/web/migrations/versions/398697dc9550_.py @@ -0,0 +1,56 @@ + +"""empty message + +Revision ID: 398697dc9550 +Revises: a091c9611d20 +Create Date: 2020-09-07 15:17:59.473879 + +""" +from pgadmin.model import db + +# revision identifiers, used by Alembic. +revision = '398697dc9550' +down_revision = 'a091c9611d20' +branch_labels = None +depends_on = None + + +def upgrade(): + db.engine.execute(""" + CREATE TABLE macros ( + id INTEGER NOT NULL, + alt BOOLEAN NOT NULL, + control BOOLEAN NOT NULL, + key VARCHAR(128) NOT NULL, + key_code INTEGER NOT NULL, + PRIMARY KEY(id) + ); + """) + + db.engine.execute(""" + CREATE TABLE user_macros ( + mid INTEGER NOT NULL, + uid INTEGER NOT NULL, + name VARCHAR(1024) NOT NULL, + sql TEXT NOT NULL, + PRIMARY KEY(mid, uid), + FOREIGN KEY(mid) REFERENCES macros (id), + FOREIGN KEY(uid) REFERENCES user (id) + ); + """) + + db.engine.execute(""" + INSERT INTO macros (id, alt, control, key, key_code) VALUES (1, false, true, '1', 49), + (2, false, true, '2', 50), (3, false, true, '3', 51), (4, false, true, '4', 52), + (5, false, true, '5', 53), (6, false, true, '6', 54), (7, false, true, '7', 55), + (8, false, true, '8', 56), (9, false, true, '9', 57), (10, false, true, '0', 48), + (11, true, false, 'F1', 112), (12, true, false, 'F2', 113), (13, true, false, 'F3', 114), + (14, true, false, 'F4', 115), (15, true, false, 'F5', 116), (16, true, false, 'F6', 117), + (17, true, false, 'F7', 118), (18, true, false, 'F8', 119), (19, true, false, 'F9', 120), + (20, true, false, 'F10', 121), (21, true, false, 'F11', 122), (22, true, false, 'F12', 123); + """) + + +def downgrade(): + # pgAdmin only upgrades, downgrade not implemented. + pass diff --git a/web/pgadmin/model/__init__.py b/web/pgadmin/model/__init__.py index b33adc062..425c9e649 100644 --- a/web/pgadmin/model/__init__.py +++ b/web/pgadmin/model/__init__.py @@ -29,7 +29,7 @@ from flask_sqlalchemy import SQLAlchemy # ########################################################################## -SCHEMA_VERSION = 26 +SCHEMA_VERSION = 27 ########################################################################## # @@ -391,3 +391,26 @@ class SharedServer(db.Model): tunnel_identity_file = db.Column(db.String(64), nullable=True) tunnel_password = db.Column(db.String(64), nullable=True) shared = db.Column(db.Boolean(), nullable=False) + + +class Macros(db.Model): + """Define a particular macro.""" + __tablename__ = 'macros' + id = db.Column(db.Integer, primary_key=True) + alt = db.Column(db.Boolean(), nullable=False) + control = db.Column(db.Boolean(), nullable=False) + key = db.Column(db.String(32), nullable=False) + key_code = db.Column(db.Integer, nullable=False) + + +class UserMacros(db.Model): + """Define the macro for a particular user.""" + __tablename__ = 'user_macros' + mid = db.Column( + db.Integer, db.ForeignKey('macros.id'), primary_key=True + ) + uid = db.Column( + db.Integer, db.ForeignKey('user.id'), primary_key=True + ) + name = db.Column(db.String(1024), nullable=False) + sql = db.Column(db.Text(), nullable=False) diff --git a/web/pgadmin/static/js/backgrid.pgadmin.js b/web/pgadmin/static/js/backgrid.pgadmin.js index e417dc78d..def9c18f6 100644 --- a/web/pgadmin/static/js/backgrid.pgadmin.js +++ b/web/pgadmin/static/js/backgrid.pgadmin.js @@ -9,10 +9,10 @@ define([ 'sources/gettext', 'underscore', 'jquery', 'backbone', 'backform', 'backgrid', 'alertify', - 'moment', 'bignumber', 'sources/utils', 'sources/keyboard_shortcuts', 'sources/select2/configure_show_on_scroll', + 'moment', 'bignumber', 'codemirror', 'sources/utils', 'sources/keyboard_shortcuts', 'sources/select2/configure_show_on_scroll', 'bootstrap.datetimepicker', 'backgrid.filter', 'bootstrap.toggle', ], function( - gettext, _, $, Backbone, Backform, Backgrid, Alertify, moment, BigNumber, + gettext, _, $, Backbone, Backform, Backgrid, Alertify, moment, BigNumber, CodeMirror, commonUtils, keyboardShortcuts, configure_show_on_scroll ) { /* @@ -44,7 +44,7 @@ define([ _.extend(Backgrid.InputCellEditor.prototype.events, { 'keydown': function(e) { let preferences = pgBrowser.get_preferences_for_module('browser'); - if(keyboardShortcuts.validateShortcutKeys(preferences.add_grid_row,e)) { + if(preferences && keyboardShortcuts.validateShortcutKeys(preferences.add_grid_row,e)) { pgBrowser.keyboardNavigation.bindAddGridRow(); } else { Backgrid.InputCellEditor.prototype.saveOrCancel.apply(this, arguments); @@ -324,7 +324,7 @@ define([ events: { 'keydown': function (event) { let preferences = pgBrowser.get_preferences_for_module('browser'); - if(keyboardShortcuts.validateShortcutKeys(preferences.add_grid_row,event)) { + if(preferences && keyboardShortcuts.validateShortcutKeys(preferences.add_grid_row,event)) { pgBrowser.keyboardNavigation.bindAddGridRow(); } }, @@ -606,6 +606,96 @@ define([ }, }); + + Backgrid.Extension.ClearCell = Backgrid.Cell.extend({ + defaults: _.defaults({ + defaultClearMsg: gettext('Are you sure you wish to clear this row?'), + defaultClearTitle: gettext('Clear Row'), + }, Backgrid.Cell.prototype.defaults), + + /** @property */ + className: 'clear-cell', + events: { + 'click': 'clearRow', + }, + clearRow: function(e) { + e.preventDefault(); + if (_.isEmpty(e.currentTarget.innerHTML)) return false; + var that = this; + // We will check if row is deletable or not + + var clear_msg = !_.isUndefined(this.column.get('customClearMsg')) ? + this.column.get('customClearMsg') : that.defaults.defaultClearMsg; + var clear_title = !_.isUndefined(this.column.get('customClearTitle')) ? + this.column.get('customClearTitle') : that.defaults.defaultClearTitle; + Alertify.confirm( + clear_title, + clear_msg, + function() { + that.model.set('name', null); + that.model.set('sql', null); + }, + function() { + return true; + } + ); + + }, + exitEditMode: function() { + this.$el.removeClass('editor'); + }, + initialize: function() { + Backgrid.Cell.prototype.initialize.apply(this, arguments); + }, + render: function() { + var self = this; + this.$el.empty(); + $(this.$el).attr('tabindex', 0); + if (this.model.get('name') !== null && this.model.get('sql') !== null) + this.$el.html('<i aria-label="' + gettext('Clear row') + '" class=\'fa fa-eraser\' title=\'' + gettext('Clear row') + '\'></i>'); + // Listen for Tab/Shift-Tab key + this.$el.on('keydown', function(e) { + // with keyboard navigation on space key, mark row for deletion + if (e.keyCode == 32) { + self.$el.click(); + } + var gotoCell; + if (e.keyCode == 9 || e.keyCode == 16) { + // go to Next Cell & if Shift is also pressed go to Previous Cell + gotoCell = e.shiftKey ? self.$el.prev() : self.$el.next(); + } + + if (gotoCell) { + let command = new Backgrid.Command({ + key: 'Tab', + keyCode: 9, + which: 9, + shiftKey: e.shiftKey, + }); + setTimeout(function() { + // When we have Editable Cell + if (gotoCell.hasClass('editable')) { + e.preventDefault(); + e.stopPropagation(); + self.model.trigger('backgrid:edited', self.model, + self.column, command); + } + else { + // When we have Non-Editable Cell + self.model.trigger('backgrid:edited', self.model, + self.column, command); + } + }, 20); + } + }); + + + this.delegateEvents(); + return this; + }, + }); + + Backgrid.Extension.CustomHeaderCell = Backgrid.HeaderCell.extend({ initialize: function() { // Here, we will add custom classes to header cell @@ -2081,6 +2171,84 @@ define([ }, }); + Backgrid.Extension.SqlCell = Backgrid.Extension.TextareaCell.extend({ + className: 'sql-cell', + defaults: { + lineWrapping: true, + }, + template: _.template([ + '<div data-toggle="tooltip" data-placement="top" data-html="true" title="<%- val %>"><textarea aria-label="' + gettext('SQL') +'" + style="display: none;"><%- val %></textarea><div>', + ].join('\n')), + + render: function() { + let self = this, + col = _.defaults(this.column.toJSON(), this.defaults), + model = this.model, + column = this.column, + columnName = this.column.get('name'), + editable = Backgrid.callByNeed(col.editable, column, model); + + if (this.sqlCell) { + this.sqlCell.toTextArea(); + delete this.sqlCell; + this.sqlCell = null; + } + + this.$el.empty(); + this.$el.append(this.template({ + val:this.formatter.fromRaw(model.get(columnName), model), + }) + ); + this.$el.addClass(columnName); + this.updateStateClassesMaybe(); + this.delegateEvents(); + + setTimeout(function() { + self.sqlCell = CodeMirror.fromTextArea( + (self.$el.find('textarea')[0]), { + mode: 'text/x-pgsql', + readOnly: !editable, + singleCursorHeightPerLine: true, + screenReaderLabel: columnName, + }); + }); + + return this; + }, + enterEditMode: function () { + if (!this.$el.hasClass('editor')) this.$el.addClass('editor'); + this.sqlCell.focus(); + this.sqlCell.on('blur', this.exitEditMode.bind(this)); + }, + exitEditMode: function () { + this.$el.removeClass('editor'); + this.saveOrCancel.apply(this, arguments); + }, + saveOrCancel: function() { + var model = this.model; + var column = this.column; + if (this.sqlCell) { + var val = this.sqlCell.getTextArea().value; + var newValue = this.sqlCell.getValue(); + if (_.isUndefined(newValue)) { + model.trigger('backgrid:error', model, column, val); + } + else { + model.set(column.get('name'), newValue); + } + } + }, + remove: function() { + if (this.sqlCell) { + $(this.$el.find('[data-toggle="tooltip"]')).tooltip('dispose'); + this.sqlCell.toTextArea(); + delete this.sqlCell; + this.sqlCell = null; + } + return Backgrid.Extension.TextareaCell.prototype.remove.apply(this, arguments); + }, + }); + return Backgrid; }); diff --git a/web/pgadmin/static/js/keyboard_shortcuts.js b/web/pgadmin/static/js/keyboard_shortcuts.js index f02851720..abda8d8f2 100644 --- a/web/pgadmin/static/js/keyboard_shortcuts.js +++ b/web/pgadmin/static/js/keyboard_shortcuts.js @@ -324,15 +324,38 @@ function keyboardShortcutsQueryTool( } } } + } else { + // Macros + let macroId = this.validateMacros(sqlEditorController, event); + + if (macroId !== false) { + this._stopEventPropagation(event); + queryToolActions.executeMacro(sqlEditorController, macroId); + } } return panel_type; } +function validateMacros(sqlEditorController, event) { + let keyCode = event.which || event.keyCode; + + let macro = sqlEditorController.macros.filter(mc => + mc.alt == event.altKey && + mc.control == event.ctrlKey && + mc.key_code == keyCode); + + if (macro.length == 1) { + return macro[0].id; + } + + return false; +} + export { keyboardShortcutsDebugger as processEventDebugger, keyboardShortcutsQueryTool as processEventQueryTool, - focusDockerPanel, validateShortcutKeys, + focusDockerPanel, validateShortcutKeys, validateMacros, _stopEventPropagation, isMac, isKeyCtrlAlt, isKeyAltShift, isKeyCtrlShift, isKeyCtrlAltShift, isAltShiftBoth, isCtrlShiftBoth, isCtrlAltBoth, shortcut_key, shortcut_title, shortcut_accesskey_title, diff --git a/web/pgadmin/static/js/sqleditor/macro.js b/web/pgadmin/static/js/sqleditor/macro.js new file mode 100644 index 000000000..b761002fc --- /dev/null +++ b/web/pgadmin/static/js/sqleditor/macro.js @@ -0,0 +1,325 @@ +///////////////////////////////////////////////////////////// +// +// pgAdmin 4 - PostgreSQL Tools +// +// Copyright (C) 2013 - 2020, The pgAdmin Development Team +// This software is released under the PostgreSQL Licence +// +////////////////////////////////////////////////////////////// + +import gettext from 'sources/gettext'; +import url_for from 'sources/url_for'; +import $ from 'jquery'; +import Alertify from 'pgadmin.alertifyjs'; +import pgAdmin from 'sources/pgadmin'; +import Backform from 'pgadmin.backform'; +import macroModel from 'sources/sqleditor/macro_model'; +import axios from 'axios'; + +let MacroDialog = { + 'dialog': function(handler) { + let title = gettext('Manage Macros'); + + // Check the alertify dialog already loaded then delete it to clear + // the cache + if (Alertify.macroDialog) { + delete Alertify.macroDialog; + } + + // Create Dialog + Alertify.dialog('macroDialog', function factory() { + let $container = $('<div class=\'macro_dialog\'></div>'); + return { + main: function() { + this.set('title', '<i class="fa fa-scroll sql-icon-lg" aria-hidden="true" role="img"></i> ' + gettext('Manage Macros')); + }, + build: function() { + this.elements.content.appendChild($container.get(0)); + Alertify.pgDialogBuild.apply(this); + }, + setup: function() { + return { + buttons: [{ + text: '', + key: 112, + className: 'btn btn-primary-icon pull-left fa fa-question pg-alertify-icon-button', + attrs: { + name: 'dialog_help', + type: 'button', + label: gettext('Help'), + 'aria-label': gettext('Help'), + url: url_for('help.static', { + 'filename': 'querytool.html', + }), + }, + }, { + text: gettext('Cancel'), + key: 27, + className: 'btn btn-secondary fa fa-times pg-alertify-button', + 'data-btn-name': 'cancel', + }, { + text: gettext('Save'), + className: 'btn btn-primary fa fa-save pg-alertify-button', + 'data-btn-name': 'ok', + }], + // Set options for dialog + options: { + title: title, + //disable both padding and overflow control. + padding: !1, + overflow: !1, + model: 0, + resizable: true, + maximizable: true, + pinnable: false, + closableByDimmer: false, + modal: false, + autoReset: false, + }, + }; + }, + hooks: { + // triggered when the dialog is closed + onclose: function() { + if (this.view) { + this.macroCollectionModel.stopSession(); + this.view.model.stopSession(); + this.view.remove({ + data: true, + internal: true, + silent: true, + }); + } + }, + }, + prepare: function() { + let self = this; + $container.html(''); + // Status bar + this.statusBar = $( + '<div class=\'pg-prop-status-bar pg-el-xs-12 d-none\'>' + + ' <div class="error-in-footer"> ' + + ' <div class="d-flex px-2 py-1"> ' + + ' <div class="pr-2"> ' + + ' <i class="fa fa-exclamation-triangle text-danger" aria-hidden="true"></i> ' + + ' </div> ' + + ' <div class="alert-text" role="alert"></div> ' + + ' </div> ' + + ' </div> ' + + '</div>').appendTo($container); + + // To show progress on filter Saving/Updating on AJAX + this.showFilterProgress = $( + `<div id="show_filter_progress" class="pg-sp-container sql-editor-busy-fetching d-none"> + <div class="pg-sp-content"> + <div class="row"><div class="col-12 pg-sp-icon sql-editor-busy-icon"></div></div> + <div class="row"><div class="col-12 pg-sp-text sql-editor-busy-text">` + gettext('Loading data...') + `</div></div> + </div> + </div>` + ).appendTo($container); + + self.macroCollectionModel = macroModel(handler.transId); + + let fields = Backform.generateViewSchema( + null, self.macroCollectionModel, 'edit', null, null, true + ); + + let ManageMacroDialog = Backform.Dialog.extend({ + template: { + 'panel': _.template( + '<div role="tabpanel" tabindex="-1" class="tab-pane <%=label%> <%=tabPanelCodeClass%> pg-el-sm-12 pg-el-md-12 pg-el-lg-12 pg-el-12 fade" id="<%=cId%>"></div>' + ), + }, + render: function() { + this.cleanup(); + + var m = this.model, + controls = this.controls, + tmpls = this.template, + dialog_obj = this, + idx = (this.tabIndex * 100), + evalF = function(f, d, model) { + return (_.isFunction(f) ? !!f.apply(d, [model]) : !!f); + }; + + this.$el + .empty() + .attr('role', 'tabpanel') + .attr('class', _.result(this, 'tabPanelClassName')); + m.panelEl = this.$el; + + var tabContent = $('<div class="tab-content pg-el-sm-12 pg-el-md-12 pg-el-lg-12 pg-el-12 macro-tab"></div>') + .appendTo(this.$el); + + _.each(this.schema, function(o) { + idx++; + if (!o.version_compatible || !evalF(o.visible, o, m)) { + return; + } + var el = $((tmpls['panel'])(_.extend(o, { + 'tabIndex': idx, + 'tabPanelCodeClass': o.tabPanelCodeClass ? o.tabPanelCodeClass : '', + }))) + .appendTo(tabContent) + .removeClass('collapse').addClass('collapse'); + + o.fields.each(function(f) { + var cntr = new(f.get('control'))({ + field: f, + model: m, + dialog: dialog_obj, + tabIndex: idx, + }); + el.append(cntr.render().$el); + controls.push(cntr); + }); + }); + + tabContent.find('.tab-pane').first().addClass('active show'); + + return this; + }, + }); + + let view = self.view = new ManageMacroDialog({ + el: '<div></div>', + model: self.macroCollectionModel, + schema: fields, + }); + + self.macroCollectionModel.fetch({ + success: function() { + + // We got the latest attributes of the object. Render the view + // now. + $container.append(self.view.render().$el); + + // Enable/disable save button and show/hide statusbar based on session + self.view.listenTo(self.view.model, 'pgadmin-session:start', function() { + self.view.listenTo(self.view.model, 'pgadmin-session:invalid', function(msg) { + self.statusBar.removeClass('d-none'); + $(self.statusBar.find('.alert-text')).html(msg); + // Disable Okay button + self.__internal.buttons[2].element.disabled = true; + }); + + view.listenTo(self.view.model, 'pgadmin-session:valid', function() { + self.statusBar.addClass('d-none'); + $(self.statusBar.find('.alert-text')).html(''); + // Enable Okay button + self.__internal.buttons[2].element.disabled = false; + }); + }); + + view.listenTo(self.view.model, 'pgadmin-session:stop', function() { + view.stopListening(self.view.model, 'pgadmin-session:invalid'); + view.stopListening(self.view.model, 'pgadmin-session:valid'); + }); + + // Starts monitoring changes to model + self.view.model.startNewSession(); + + }}); + + $(this.elements.body.childNodes[0]).addClass( + 'alertify_tools_dialog_properties obj_properties' + ); + + + + }, + // Callback functions when click on the buttons of the Alertify dialogs + callback: function(e) { + let self = this; + + if (e.button.element.name == 'dialog_help') { + e.cancel = true; + pgAdmin.Browser.showHelp(e.button.element.name, e.button.element.getAttribute('url'), + null, null); + return; + } else if (e.button['data-btn-name'] === 'ok') { + e.cancel = true; // Do not close dialog + + let data = self.view.model.get('macro').toJSON(true); + + if (data == undefined || data == null) { + self.close(); + return; + } + + axios.put( + url_for('sqleditor.set_macros', { + 'trans_id': handler.transId, + }), + data + ).then(function (result) { + // Hide Progress ... + $( + self.showFilterProgress[0] + ).addClass('d-none'); + + let macroResponse = result; + + if (macroResponse.status) { + setTimeout( + function() { + // Update Macro Menu + let macros = self.view.model.get('macro').toJSON().filter(m => m.name !== undefined || m.name !== null); + handler.macros = macros; + var str = ` + <li> + <a class="dropdown-item" id="btn-manage-macros" href="#" tabindex="0"> + <span> Manage Macros... </span> + </a> + </li> + <li class="dropdown-divider"></li>`; + _.each(macros, function(m) { + if (m.name) { + str += `<li> + <a class="dropdown-item btn-macro" data-macro-id="`+ m.id +`" href="#" tabindex="0"> + <span>` + m.name + `</span> + <span> (` + m.key_label + `) </span> + </a> + </li>`; + } + }); + + $($.find('div.btn-group.mr-1.user_macros ul.dropdown-menu')).html($(str)); + + self.close(); // Close the dialog now + Alertify.success(gettext('Macro updated successfully')); + }, 10 + ); + } else { + Alertify.alert( + gettext('Validation Error'), + macroResponse.result + ); + } + + }).catch(function (error) { + // Hide Progress ... + $( + self.showFilterProgress[0] + ).addClass('d-none'); + + setTimeout( + function() { + Alertify.error(error.response.data.errormsg); + }, 10 + ); + }); + + } else { + self.close(); + } + }, + }; + }); + + Alertify.macroDialog(title).resizeTo(pgAdmin.Browser.stdW.calc(pgAdmin.Browser.stdW.lg), + pgAdmin.Browser.stdH.calc(pgAdmin.Browser.stdH.lg)); + }, +}; + +module.exports = MacroDialog; diff --git a/web/pgadmin/static/js/sqleditor/macro_model.js b/web/pgadmin/static/js/sqleditor/macro_model.js new file mode 100644 index 000000000..c874ded4d --- /dev/null +++ b/web/pgadmin/static/js/sqleditor/macro_model.js @@ -0,0 +1,224 @@ +///////////////////////////////////////////////////////////// +// +// pgAdmin 4 - PostgreSQL Tools +// +// Copyright (C) 2013 - 2020, The pgAdmin Development Team +// This software is released under the PostgreSQL Licence +// +////////////////////////////////////////////////////////////// + +import gettext from 'sources/gettext'; +import pgAdmin from 'sources/pgadmin'; +import Backform from 'pgadmin.backform'; +import Backgrid from 'pgadmin.backgrid'; +import url_for from 'sources/url_for'; +import $ from 'jquery'; +import _ from 'underscore'; +import Alertify from 'pgadmin.alertifyjs'; + +export default function macroModel(transId) { + + let MacroModel = pgAdmin.Browser.DataModel.extend({ + idAttribute: 'id', + defaults: { + id: undefined, + key: undefined, + name: undefined, + sql: undefined, + key_label: undefined, + }, + schema: [{ + id: 'key_label', + name: 'key_label', + label: gettext('Key'), + type: 'text', + cell: 'string', + editable: false, + cellHeaderClasses: 'width_percent_10', + headerCell: Backgrid.Extension.CustomHeaderCell, + disabled: false, + }, { + id: 'name', + name: 'name', + label: gettext('Name'), + cell: 'string', + type: 'text', + editable: true, + cellHeaderClasses: 'width_percent_20', + headerCell: Backgrid.Extension.CustomHeaderCell, + disabled: false, + }, { + id: 'sql', + name: 'sql', + label: gettext('SQL'), + cell: Backgrid.Extension.SqlCell, + type: 'multiline', + control: Backform.SqlCodeControl, + editable: true, + cellHeaderClasses: 'width_percent_70', + headerCell: Backgrid.Extension.CustomHeaderCell, + disabled: false, + }, + ], + validate: function() { + let msg = null; + this.errorModel.clear(); + if (_.isEmpty(this.get('name')) && !(_.isEmpty(this.get('sql')))) { + msg = gettext('Please enter macro name.'); + this.errorModel.set('name', msg); + return msg; + } else if (_.isEmpty(this.get('sql')) && !(_.isEmpty(this.get('name')))) { + msg = gettext('Please enter macro sql.'); + this.errorModel.set('sql', msg); + return msg; + } + return null; + }, + }); + + let MacroCollectionModel = pgAdmin.Browser.DataModel.extend({ + defaults: { + macro: undefined, + }, + urlRoot: url_for('sqleditor.get_macros', {'trans_id': transId}), + schema: [{ + id: 'macro', + name: 'macro', + label: gettext('Macros'), + model: MacroModel, + editable: true, + type: 'collection', + control: Backform.SubNodeCollectionControl.extend({ + showGridControl: function(data) { + var self = this, + gridBody = $('<div class=\'pgadmin-control-group backgrid form-group pg-el-12 object subnode\'></div>'); + + var subnode = data.subnode.schema ? data.subnode : data.subnode.prototype, + gridSchema = Backform.generateGridColumnsFromModel( + data.node_info, subnode, this.field.get('mode'), data.columns, data.schema_node + ); + + // Clean up existing grid if any (in case of re-render) + if (self.grid) { + self.grid.remove(); + } + + // Set visibility of Add button + if (data.disabled || data.canAdd == false) { + $(gridBody).find('button.add').remove(); + } + + // Insert Clear Cell into Grid + gridSchema.columns.unshift({ + name: 'pg-backform-clear', + label: '<i aria-label="' + gettext('Clear row') + '" class="fa fa-eraser" title="' + gettext('Clear row') + '"></i>', + cell: Backgrid.Extension.ClearCell, + editable: false, + cell_priority: -1, + sortable: false, + headerCell: Backgrid.Extension.CustomHeaderCell.extend({ + className: 'header-icon-cell', + events: { + 'click': 'clearrAll', + }, + clearrAll: function(e) { + e.preventDefault(); + var that = this; + // We will check if row is deletable or not + + Alertify.confirm( + gettext('Clear All Rows'), + gettext('Are you sure you wish to clear all rows?'), + function() { + _.each(that.collection.toJSON(), function(m) { + that.collection.get(m.id).set({'name': null, 'sql': null}); + }); + }, + function() { + return true; + } + ); + }, + render: function() { + this.$el.empty(); + var column = this.column; + var label = $('<button type="button" title="' + gettext('Clear row') + '" aria-label="Clear row" aria-expanded="false" tabindex="0">').html(column.get('label')).append('<span class=\'sort-caret\' aria-hidden=\'true\'></span>'); + + this.$el.append(label); + this.$el.addClass(column.get('name')); + this.$el.addClass(column.get('direction')); + this.$el.attr('role', 'columnheader'); + this.$el.attr('aria-label', 'columnheader'); + this.$el.attr('alt', 'columnheader'); + this.delegateEvents(); + return this; + + }, + }), + }); + + + var collection = self.model.get(data.name); + + if (!collection) { + collection = new(pgAdmin.Browser.Node.Collection)(null, { + handler: self.model.handler || self.model, + model: data.model, + top: self.model.top || self.model, + silent: true, + }); + self.model.set(data.name, collection, { + silent: true, + }); + } + + var cellEditing = function(args) { + var ctx = this, + cell = args[0]; + // Search for any other rows which are open. + this.each(function(m) { + // Check if row which we are about to close is not current row. + if (cell.model != m) { + var idx = ctx.indexOf(m); + if (idx > -1) { + var row = grid.body.rows[idx], + rowEditCell = row.$el.find('.subnode-edit-in-process').parent(); + // Only close row if it's open. + if (rowEditCell.length > 0) { + var event = new Event('click'); + rowEditCell[0].dispatchEvent(event); + } + } + } + }); + }; + // Listen for any row which is about to enter in edit mode. + collection.on('enteringEditMode', cellEditing, collection); + + // Initialize a new Grid instance + var grid = self.grid = new Backgrid.Grid({ + columns: gridSchema.columns, + collection: collection, + row: this.row, + className: 'backgrid table presentation table-bordered table-noouter-border table-hover', + }); + + // Render subNode grid + var subNodeGrid = grid.render().$el; + + var $dialog = gridBody.append(subNodeGrid); + + return $dialog; + }, + }), + columns: ['key_label', 'name', 'sql'], + visible: true, + }], + validate: function() { + return null; + }, + }); + + let model = new MacroCollectionModel(); + return model; +} diff --git a/web/pgadmin/static/js/sqleditor/query_tool_actions.js b/web/pgadmin/static/js/sqleditor/query_tool_actions.js index 98297cf18..81937058c 100644 --- a/web/pgadmin/static/js/sqleditor/query_tool_actions.js +++ b/web/pgadmin/static/js/sqleditor/query_tool_actions.js @@ -39,6 +39,11 @@ let queryToolActions = { $('.sql-editor-message').html(''); }, + executeMacro: function (sqlEditorController, MacroId) { + this._clearMessageTab(); + sqlEditorController.check_data_changes_to_execute_query(null, false, MacroId); + }, + executeQuery: function (sqlEditorController) { this._clearMessageTab(); sqlEditorController.check_data_changes_to_execute_query(); diff --git a/web/pgadmin/static/scss/_backgrid.overrides.scss b/web/pgadmin/static/scss/_backgrid.overrides.scss index 4fc1f5f19..35840cfae 100644 --- a/web/pgadmin/static/scss/_backgrid.overrides.scss +++ b/web/pgadmin/static/scss/_backgrid.overrides.scss @@ -359,3 +359,31 @@ table tr th button { } } } + +.backgrid .sql-cell .CodeMirror-scroll { + overflow: hidden !important; +} + +.backgrid .sql-cell .cm-s-default { + height: 50px !important; +} + +.backgrid .sql-cell .CodeMirror-hscrollbar > div { + width: 100% !important; +} + +.backgrid .sql-cell .CodeMirror-hscrollbar { + overflow-x: hidden !important; +} + +.backgrid .sql-cell .CodeMirror-vscrollbar { + overflow-y: hidden !important; +} + +.backgrid .sql-cell .CodeMirror-sizer { + padding-bottom: 0 !important; +} + +.backgrid .sql-cell .CodeMirror-scrollbar-filler { + display: none !important; +} diff --git a/web/pgadmin/tools/datagrid/__init__.py b/web/pgadmin/tools/datagrid/__init__.py index f5fc78c4f..1bd841f20 100644 --- a/web/pgadmin/tools/datagrid/__init__.py +++ b/web/pgadmin/tools/datagrid/__init__.py @@ -33,6 +33,7 @@ from pgadmin.settings import get_setting from pgadmin.browser.utils import underscore_unescape from pgadmin.utils.exception import ObjectGone from pgadmin.utils.constants import MIMETYPE_APP_JS +from pgadmin.tools.sqleditor.utils.macros import get_user_macros MODULE_NAME = 'datagrid' @@ -274,6 +275,8 @@ def panel(trans_id): layout = get_setting('SQLEditor/Layout') + macros = get_user_macros() + return render_template( "datagrid/index.html", _=gettext, @@ -286,6 +289,7 @@ def panel(trans_id): bgcolor=bgcolor, fgcolor=fgcolor, layout=layout, + macros=macros ) diff --git a/web/pgadmin/tools/datagrid/templates/datagrid/index.html b/web/pgadmin/tools/datagrid/templates/datagrid/index.html index 097a42e91..a0eebc8cf 100644 --- a/web/pgadmin/tools/datagrid/templates/datagrid/index.html +++ b/web/pgadmin/tools/datagrid/templates/datagrid/index.html @@ -377,6 +377,31 @@ <i class="fa fa-download sql-icon-lg" aria-hidden="true" role="img"></i> </button> </div> + <div class="btn-group mr-1 user_macros" role="group" aria-label=""> + <button id="btn-macro-dropdown" type="button" class="btn btn-sm btn-primary-icon dropdown-toggle" + data-toggle="dropdown" aria-haspopup="true" aria-expanded="false" + aria-label="{{ _('Macros') }}" title="{{ _('Macros') }}" tabindex="0"> + <i class="fa fa-scroll sql-icon-lg" aria-hidden="true" role="img"></i> + </button> + <ul class="dropdown-menu"> + <li> + <a class="dropdown-item" id="btn-manage-macros" href="#" tabindex="0"> + <span> {{ _('Manage Macros...') }} </span> + </a> + </li> + {% if macros|length > 0 %} + <li class="dropdown-divider"></li> + {% endif %} + {% for i in macros %} + <li> + <a class="dropdown-item btn-macro" data-macro-id="{{ i.id }}" href="#" tabindex="0"> + <span> {{ _(i.name) }} </span> + <span> ({{ i.key_label }}) </span> + </a> + </li> + {% endfor %} + </ul> + </div> </div> <div class="connection_status_wrapper d-flex"> @@ -459,7 +484,8 @@ require(['sources/generated/browser_nodes', 'sources/generated/codemirror', 'sou sqlEditorController.start( {{ uniqueId }}, {{ url_params|safe}}, - '{{ layout|safe }}' + '{{ layout|safe }}', + {{ macros|safe }} ); // If opening from schema diff, set the generated script to the SQL Editor diff --git a/web/pgadmin/tools/sqleditor/__init__.py b/web/pgadmin/tools/sqleditor/__init__.py index c62bc7cbc..9d0903b25 100644 --- a/web/pgadmin/tools/sqleditor/__init__.py +++ b/web/pgadmin/tools/sqleditor/__init__.py @@ -32,7 +32,7 @@ from pgadmin.tools.sqleditor.utils.update_session_grid_transaction import \ from pgadmin.utils import PgAdminModule from pgadmin.utils import get_storage_directory from pgadmin.utils.ajax import make_json_response, bad_request, \ - success_return, internal_server_error + success_return, internal_server_error, make_response as ajax_response from pgadmin.utils.driver import get_driver from pgadmin.utils.menu import MenuItem from pgadmin.utils.exception import ConnectionLost, SSHTunnelConnectionLost,\ @@ -46,6 +46,8 @@ from pgadmin.tools.sqleditor.utils.filter_dialog import FilterDialog from pgadmin.tools.sqleditor.utils.query_history import QueryHistory from pgadmin.utils.constants import MIMETYPE_APP_JS, SERVER_CONNECTION_CLOSED,\ ERROR_MSG_TRANS_ID_NOT_FOUND +from pgadmin.tools.sqleditor.utils.macros import get_macros,\ + get_user_macros, set_macros MODULE_NAME = 'sqleditor' @@ -109,6 +111,9 @@ class SqlEditorModule(PgAdminModule): 'sqleditor.get_query_history', 'sqleditor.add_query_history', 'sqleditor.clear_query_history', + 'sqleditor.get_macro', + 'sqleditor.get_macros', + 'sqleditor.set_macros' ] def register_preferences(self): @@ -1547,3 +1552,46 @@ def get_query_history(trans_id): check_transaction_status(trans_id) return QueryHistory.get(current_user.id, trans_obj.sid, conn.db) + + [email protected]( + '/get_macros/<int:trans_id>', + methods=["GET"], endpoint='get_macros' +) [email protected]( + '/get_macros/<int:macro_id>/<int:trans_id>', + methods=["GET"], endpoint='get_macro' +) +@login_required +def macros(trans_id, macro_id=None, json_resp=True): + """ + This method is used to get all the columns for data sorting dialog. + + Args: + trans_id: unique transaction id + macro_id: Macro id + """ + + status, error_msg, conn, trans_obj, session_ob = \ + check_transaction_status(trans_id) + + return get_macros(macro_id, json_resp) + + [email protected]( + '/set_macros/<int:trans_id>', + methods=["PUT"], endpoint='set_macros' +) +@login_required +def update_macros(trans_id): + """ + This method is used to get all the columns for data sorting dialog. + + Args: + trans_id: unique transaction id + """ + + status, error_msg, conn, trans_obj, session_ob = \ + check_transaction_status(trans_id) + + return set_macros() diff --git a/web/pgadmin/tools/sqleditor/static/css/sqleditor.css b/web/pgadmin/tools/sqleditor/static/css/sqleditor.css index 00543ff69..c281d537e 100644 --- a/web/pgadmin/tools/sqleditor/static/css/sqleditor.css +++ b/web/pgadmin/tools/sqleditor/static/css/sqleditor.css @@ -395,3 +395,32 @@ input.editor-checkbox:focus { .hide-vertical-scrollbar { overflow-y: hidden; } + + +/* Macros */ + +.macro-tab { + top: 0px !important; +} + +.macro-tab .tab-pane { + padding: 0px !important; +} + +.macro_dialog .CodeMirror { + overflow-y: auto; + resize: vertical; +} +.macro_dialog .sql-cell > div { + overflow-y: auto; + resize: vertical; +} + +.macro_dialog .CodeMirror-cursor { + width: 1px !important; + height: 18px !important; +} + +.macro_dialog .pg-prop-status-bar { + z-index: 1; +} diff --git a/web/pgadmin/tools/sqleditor/static/js/sqleditor.js b/web/pgadmin/tools/sqleditor/static/js/sqleditor.js index ddd9ad701..203e3a573 100644 --- a/web/pgadmin/tools/sqleditor/static/js/sqleditor.js +++ b/web/pgadmin/tools/sqleditor/static/js/sqleditor.js @@ -43,6 +43,7 @@ define('tools.querytool', [ 'tools/datagrid/static/js/datagrid_panel_title', 'sources/window', 'sources/is_native', + 'sources/sqleditor/macro', 'sources/../bundle/slickgrid', 'pgadmin.file_manager', 'slick.pgadmin.formatters', @@ -57,7 +58,7 @@ define('tools.querytool', [ GeometryViewer, historyColl, queryHist, querySources, keyboardShortcuts, queryToolActions, queryToolNotifications, Datagrid, modifyAnimation, calculateQueryRunTime, callRenderAfterPoll, queryToolPref, queryTxnStatus, csrfToken, panelTitleFunc, - pgWindow, isNative) { + pgWindow, isNative, MacroHandler) { /* Return back, this has been called more than once */ if (pgAdmin.SqlEditor) return pgAdmin.SqlEditor; @@ -149,6 +150,9 @@ define('tools.querytool', [ // Transaction control 'click #btn-commit': 'on_commit_transaction', 'click #btn-rollback': 'on_rollback_transaction', + // Manage Macros + 'click #btn-manage-macros': 'on_manage_macros', + 'click .btn-macro': 'on_execute_macro', }, reflectPreferences: function() { @@ -2038,8 +2042,30 @@ define('tools.querytool', [ queryToolActions.executeRollback(this.handler); }, + + // Callback function for manage macros button click. + on_manage_macros: function() { + var self = this; + + // Trigger the show_filter signal to the SqlEditorController class + self.handler.trigger( + 'pgadmin-sqleditor:button:manage_macros', + self, + self.handler + ); + }, + + // Callback function for manage macros button click. + on_execute_macro: function(e) { + let macroId = $(e.currentTarget).data('macro-id'); + this.handler.history_query_source = QuerySources.EXECUTE; + queryToolActions.executeMacro(this.handler, macroId); + }, + }); + + /* Defining controller class for data grid, which actually * perform the operations like executing the sql query, poll the result, * render the data in the grid, Save/Refresh the data etc... @@ -2308,7 +2334,7 @@ define('tools.querytool', [ * call the render method of the grid view to render the slickgrid * header and loading icon and start execution of the sql query. */ - start: function(transId, url_params, layout) { + start: function(transId, url_params, layout, macros) { var self = this; self.is_query_tool = url_params.is_query_tool==='true'?true:false; @@ -2333,6 +2359,7 @@ define('tools.querytool', [ layout: layout, }); self.transId = self.gridView.transId = transId; + self.macros = self.gridView.macros = macros; self.gridView.current_file = undefined; @@ -2474,12 +2501,14 @@ define('tools.querytool', [ self.on('pgadmin-sqleditor:unindent_selected_code', self._unindent_selected_code, self); // Format self.on('pgadmin-sqleditor:format_sql', self._format_sql, self); + self.on('pgadmin-sqleditor:button:manage_macros', self._manage_macros, self); + self.on('pgadmin-sqleditor:button:execute_macro', self._execute_macro, self); window.parent.$(window.parent.document).on('pgadmin-sqleditor:rows-copied', self._copied_in_other_session); }, // Checks if there is any dirty data in the grid before executing a query - check_data_changes_to_execute_query: function(explain_prefix=null, shouldReconnect=false) { + check_data_changes_to_execute_query: function(explain_prefix=null, shouldReconnect=false, macroId=undefined) { var self = this; // Check if the data grid has any changes before running query @@ -2492,7 +2521,10 @@ define('tools.querytool', [ gettext('The data has been modified, but not saved. Are you sure you wish to discard the changes?'), function() { // The user does not want to save, just continue - if(self.is_query_tool) { + if (macroId !== undefined) { + self._execute_macro_query(explain_prefix, shouldReconnect, macroId); + } + else if(self.is_query_tool) { self._execute_sql_query(explain_prefix, shouldReconnect); } else { @@ -2508,7 +2540,10 @@ define('tools.querytool', [ cancel: gettext('No'), }); } else { - if(self.is_query_tool) { + if (macroId !== undefined) { + self._execute_macro_query(explain_prefix, shouldReconnect, macroId); + } + else if(self.is_query_tool) { self._execute_sql_query(explain_prefix, shouldReconnect); } else { @@ -2602,6 +2637,37 @@ define('tools.querytool', [ }); }, + // Executes sql query for macroin the editor in Query Tool mode + _execute_macro_query: function(explain_prefix, shouldReconnect, macroId) { + var self = this; + + self.has_more_rows = false; + self.fetching_rows = false; + + $.ajax({ + url: url_for('sqleditor.get_macro', {'macro_id': macroId, 'trans_id': self.transId}), + method: 'GET', + contentType: 'application/json', + dataType: 'json', + }) + .done(function(res) { + if (res) { + // Replace the place holder + let query = res.sql.replaceAll('$SELECTION$', self.gridView.query_tool_obj.getSelection()); + + const executeQuery = new ExecuteQuery.ExecuteQuery(self, pgAdmin.Browser.UserManagement); + executeQuery.poll = pgBrowser.override_activity_event_decorator(executeQuery.poll).bind(executeQuery); + executeQuery.execute(query, explain_prefix, shouldReconnect); + } else { + // Let it be for now + } + }) + .fail(function() { + /* failure should not be ignored */ + }); + + }, + // Executes sql query in the editor in Query Tool mode _execute_sql_query: function(explain_prefix, shouldReconnect) { var self = this, sql = ''; @@ -3968,6 +4034,7 @@ define('tools.querytool', [ $('#btn-file-menu-dropdown').prop('disabled', mode_disabled); $('#btn-find').prop('disabled', mode_disabled); $('#btn-find-menu-dropdown').prop('disabled', mode_disabled); + $('#btn-macro-dropdown').prop('disabled', mode_disabled); if (this.is_query_tool) { @@ -4375,6 +4442,24 @@ define('tools.querytool', [ }); }, + // This function will open the manage macro dialog + _manage_macros: function() { + let self = this; + + /* When server is disconnected and connected, connection is lost, + * To reconnect pass true + */ + MacroHandler.dialog(self); + }, + + // This function will open the manage macro dialog + _execute_macro: function() { + + queryToolActions.executeMacro(this.handler); + + }, + + isQueryRunning: function() { return is_query_running; }, diff --git a/web/pgadmin/tools/sqleditor/tests/test_macros.py b/web/pgadmin/tools/sqleditor/tests/test_macros.py new file mode 100644 index 000000000..cbc43edec --- /dev/null +++ b/web/pgadmin/tools/sqleditor/tests/test_macros.py @@ -0,0 +1,125 @@ +########################################################################## +# +# pgAdmin 4 - PostgreSQL Tools +# +# Copyright (C) 2013 - 2020, 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 +import random + + +class TestMacros(BaseTestGenerator): + """ This class will test the query tool polling. """ + scenarios = [ + ('Get all macros', + dict( + url='get_macros', + method='get' + )), + ('Set Macros', + dict( + url='set_macros', + method='put', + operation='update', + data={ + 'changed': [ + {'id': 1, + 'name': 'Test Macro 1', + 'sql': 'SELECT 1;' + }, + {'id': 2, + 'name': 'Test Macro 2', + 'sql': 'SELECT 2;' + }, + {'id': 3, + 'name': 'Test Macro 3', + 'sql': 'SELECT 3;' + }, + ] + } + )), + ('Clear Macros', + dict( + url='set_macros', + method='put', + operation='clear', + data={ + 'changed': [ + {'id': 1, + 'name': '', + 'sql': '' + }, + {'id': 2, + 'name': '', + 'sql': '' + }, + {'id': 3, + 'name': '', + 'sql': '' + }, + ] + } + )) + ] + + def setUp(self): + """ This function will check messages return by query tool polling. """ + 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.") + + # Initialize query tool + self.trans_id = str(random.randint(1, 9999999)) + url = '/datagrid/initialize/query_tool/{0}/{1}/{2}/{3}'.format( + self.trans_id, utils.SERVER_GROUP, self.server_id, self.db_id) + response = self.tester.post(url) + self.assertEqual(response.status_code, 200) + + def runTest(self): + url = '/sqleditor/{0}/{1}'.format(self.url, self.trans_id) + + if self.method == 'get': + response = self.tester.get(url) + self.assertEqual(response.status_code, 200) + + response_data = json.loads(response.data.decode('utf-8')) + self.assertEqual(len(response_data['macro']), 22) + else: + response = self.tester.put(url, + data=json.dumps(self.data), + follow_redirects=True) + self.assertEqual(response.status_code, 200) + + for m in self.data['changed']: + url = '/sqleditor/get_macros/{0}/{1}'.format(m['id'], + self.trans_id) + response = self.tester.get(url) + + if self.operation == 'clear': + self.assertEqual(response.status_code, 410) + else: + self.assertEqual(response.status_code, 200) + + response_data = json.loads(response.data.decode('utf-8')) + self.assertEqual(response_data['name'], m['name']) + self.assertEqual(response_data['sql'], m['sql']) + + def tearDown(self): + # Disconnect the database + database_utils.disconnect_database(self, self.server_id, self.db_id) diff --git a/web/pgadmin/tools/sqleditor/utils/macros.py b/web/pgadmin/tools/sqleditor/utils/macros.py new file mode 100644 index 000000000..597d5f996 --- /dev/null +++ b/web/pgadmin/tools/sqleditor/utils/macros.py @@ -0,0 +1,189 @@ +########################################################################## +# +# pgAdmin 4 - PostgreSQL Tools +# +# Copyright (C) 2013 - 2020, The pgAdmin Development Team +# This software is released under the PostgreSQL Licence +# +########################################################################## + +"""Handle Macros for SQL Editor.""" + +import simplejson as json +from flask_babelex import gettext +from flask import current_app, request +from flask_security import login_required, current_user +from pgadmin.utils.ajax import make_response as ajax_response,\ + make_json_response +from pgadmin.model import db, Macros, UserMacros +from sqlalchemy import and_ + + +def get_macros(macro_id, json_resp): + """ + This method is used to get all the macros/specific macro. + :param macro_id: Macro ID + :param json_resp: Set True to return json response + """ + if macro_id: + macro = UserMacros.query.filter_by(mid=macro_id, + uid=current_user.id).first() + if macro is None: + return make_json_response( + status=410, + success=0, + errormsg=gettext("Macro not found.") + ) + else: + return ajax_response( + response={'id': macro.mid, + 'name': macro.name, + 'sql': macro.sql}, + status=200 + ) + else: + macros = db.session.query(Macros.id, Macros.alt, Macros.control, + Macros.key, Macros.key_code, + UserMacros.name, UserMacros.sql + ).outerjoin( + UserMacros, and_(Macros.id == UserMacros.mid, + UserMacros.uid == current_user.id)).all() + + data = [] + + for m in macros: + key_label = 'Ctrl + ' + m[3] if m[2] is True else 'Alt + ' + m[3] + data.append({'id': m[0], 'alt': m[1], + 'control': m[2], 'key': m[3], + 'key_code': m[4], 'name': m[5], + 'sql': m[6], + 'key_label': key_label}) + + if not json_resp: + return data + + return ajax_response( + response={'macro': data}, + status=200 + ) + + +def get_user_macros(): + """ + This method is used to get all the user macros. + """ + + macros = db.session.query(UserMacros.name, + Macros.id, + Macros.alt, Macros.control, + Macros.key, Macros.key_code + ).outerjoin( + Macros, UserMacros.mid == Macros.id).filter( + UserMacros.uid == current_user.id).order_by(UserMacros.name).all() + + data = [] + + for m in macros: + key_label = 'Ctrl + ' + m[4] if m[3] is True else 'Alt + ' + m[4] + data.append({'name': m[0], 'id': m[1], 'key': m[4], + 'key_label': key_label, 'alt': 1 if m[2] else 0, + 'control': 1 if m[3] else 0, 'key_code': m[5]}) + + return data + + +def set_macros(): + """ + This method is used to update the user defined macros. + """ + + data = request.form if request.form else json.loads( + request.data, encoding='utf-8' + ) + + if 'changed' not in data: + return make_json_response( + success=1, + info=gettext('Nothing to update.') + ) + + for m in data['changed']: + if m['id']: + macro = UserMacros.query.filter_by( + uid=current_user.id, + mid=m['id']).first() + if macro: + status, msg = update_macro(m, macro) + else: + status, msg = create_macro(m) + + if not status: + return make_json_response( + status=410, success=0, errormsg=msg + ) + return ajax_response(status=200) + + +def create_macro(macro): + """ + This method is used to create the user defined macros. + :param macro: macro + """ + + required_args = [ + 'name', + 'sql' + ] + for arg in required_args: + if arg not in macro: + return False, gettext( + "Could not find the required parameter ({}).").format(arg) + + try: + new_macro = UserMacros( + uid=current_user.id, + mid=macro['id'], + name=macro['name'], + sql=macro['sql'] + ) + db.session.add(new_macro) + db.session.commit() + except Exception as e: + db.session.rollback() + return False, str(e) + + return True, None + + +def update_macro(data, macro): + """ + This method is used to clear/update the user defined macros. + :param data: updated macro data + :param macro: macro + """ + + name = getattr(data, 'name', None) + sql = getattr(data, 'sql', None) + + if name or sql and macro.sql and name is None: + return False, gettext( + "Could not find the required parameter (name).") + elif name or sql and macro.name and sql is None: + return False, gettext( + "Could not find the required parameter (sql).") + + try: + if name or sql: + if name: + macro.name = name + if sql: + macro.sql = sql + else: + db.session.delete(macro) + + db.session.commit() + except Exception as e: + db.session.rollback() + return False, str(e) + + return True, None diff --git a/web/regression/javascript/sqleditor/keyboard_shortcuts_spec.js b/web/regression/javascript/sqleditor/keyboard_shortcuts_spec.js index c8415a089..04365bf48 100644 --- a/web/regression/javascript/sqleditor/keyboard_shortcuts_spec.js +++ b/web/regression/javascript/sqleditor/keyboard_shortcuts_spec.js @@ -18,7 +18,8 @@ describe('the keyboard shortcuts', () => { F7_KEY = 118, F8_KEY = 119, PERIOD_KEY = 190, - FWD_SLASH_KEY = 191; + FWD_SLASH_KEY = 191, + C1_KEY = 49; let sqlEditorControllerSpy, event, queryToolActionsSpy; beforeEach(() => { @@ -120,6 +121,19 @@ describe('the keyboard shortcuts', () => { }, }; + sqlEditorControllerSpy.macros = [ + { + alt: false, + control: true, + id: 1, + key: '1', + key_code: C1_KEY, + key_label: 'Ctrl + 1', + name: 'C1', + sql: 'Select 1;', + }, + ]; + queryToolActionsSpy = jasmine.createSpyObj(queryToolActions, [ 'explainAnalyze', 'explain', @@ -131,6 +145,7 @@ describe('the keyboard shortcuts', () => { 'executeCommit', 'executeRollback', 'saveDataChanges', + 'executeMacro', ]); }); @@ -667,6 +682,47 @@ describe('the keyboard shortcuts', () => { }); }); + describe('Macro Ctrl + 1', () => { + describe('when there is not a query already running', () => { + beforeEach(() => { + event.which = C1_KEY; + event.altKey = false; + event.shiftKey = false; + event.ctrlKey = true; + + keyboardShortcuts.processEventQueryTool( + sqlEditorControllerSpy, queryToolActionsSpy, event + ); + }); + + it('should execute the macro', () => { + expect(queryToolActionsSpy.executeMacro).toHaveBeenCalledWith(sqlEditorControllerSpy, + sqlEditorControllerSpy.macros[0].id); + }); + + it('should stop event propagation', () => { + expect(event.preventDefault).toHaveBeenCalled(); + }); + }); + + describe('when the query is already running', () => { + it('does nothing', () => { + event.keyCode = C1_KEY; + event.altKey = false; + event.shiftKey = false; + event.ctrlKey = true; + + sqlEditorControllerSpy.isQueryRunning.and.returnValue(true); + + keyboardShortcuts.processEventQueryTool( + sqlEditorControllerSpy, queryToolActionsSpy, event + ); + + expect(queryToolActionsSpy.executeMacro).not.toHaveBeenCalled(); + }); + }); + }); + function expectEventPropagationToStop() { describe('stops all event propogation', () => { ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgAdmin4][Patch] - RM 1402 - Macro support like pgAdmin3 @ 2020-09-28 12:26 Akshay Joshi <[email protected]> parent: Khushboo Vashi <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Akshay Joshi @ 2020-09-28 12:26 UTC (permalink / raw) To: Khushboo Vashi <[email protected]>; +Cc: pgadmin-hackers Thanks, patch applied. On Mon, Sep 28, 2020 at 2:18 PM Khushboo Vashi < [email protected]> wrote: > Hi, > > Please find the attached updated patch. > > Thanks, > Khushboo > > On Wed, Sep 23, 2020 at 5:42 PM Akshay Joshi < > [email protected]> wrote: > >> Hi Khushboo >> >> Following are the GUI review comments: >> >> - If we provide anyone value either Name or SQL it throws Alertify >> error message with 'name' or 'SQL'. The error message should be correct. >> >> [image: Name_Error.png] >> > Fixed. > >> >> - We should not enable the 'Save' button if the value of Name or SQL >> is not entered by the user. Either both values should be there or both >> should be blank. >> >> Fixed. > >> >> - Click on anywhere inside the empty cell where the clear row icon is >> not visible, it pops up the message to clear the row which is already >> empty. >> >> [image: Clear_Row.png] >> > Fixed. > >> >> - Save any macro and then open the "Manage Macros..." dialog again >> click on the SQL control for the existing macro, cursor is not at the >> appropriate place. >> >> [image: CodeMirror_Error.png] >> > Fixed. > >> >> - Macros are not working for multiple users. Create some macro from >> User1 and then log in using User2, macros created by User1 are visible to >> User2 and when I try to update it is not updating. >> >> Fixed. > >> >> On Mon, Sep 21, 2020 at 2:19 PM Khushboo Vashi < >> [email protected]> wrote: >> >>> Hi, >>> >>> Please find the attached patch for RM 1402 - Macro support like pgAdmin3. >>> >>> Thanks, >>> Khushboo >>> >>> >> >> -- >> *Thanks & Regards* >> *Akshay Joshi* >> *pgAdmin Hacker | Sr. Software Architect* >> *EDB Postgres <http://edbpostgres.com>* >> >> *Mobile: +91 976-788-8246* >> > -- *Thanks & Regards* *Akshay Joshi* *pgAdmin Hacker | Sr. Software Architect* *EDB Postgres <http://edbpostgres.com>* *Mobile: +91 976-788-8246* Attachments: [image/png] Name_Error.png (8.0K, 3-Name_Error.png) download | view image [image/png] Clear_Row.png (19.4K, 4-Clear_Row.png) download | view image [image/png] CodeMirror_Error.png (35.1K, 5-CodeMirror_Error.png) download | view image ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-09-28 12:26 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-09-21 08:49 [pgAdmin4][Patch] - RM 1402 - Macro support like pgAdmin3 Khushboo Vashi <[email protected]> 2020-09-23 12:12 ` Akshay Joshi <[email protected]> 2020-09-28 08:47 ` Khushboo Vashi <[email protected]> 2020-09-28 12:26 ` Akshay Joshi <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox