public inbox for [email protected]
help / color / mirror / Atom feedFrom: Akshay Joshi <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: [pgAdmin4] [Patch] Implementation of the Data Grid and Query Tool
Date: Tue, 5 Apr 2016 14:50:10 +0530
Message-ID: <CANxoLDe8fRZ=m8NGPc0=YQLS0vTc4-L5xmjyn2J2V1jUt937Mg@mail.gmail.com> (raw)
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
Hi All
I have implemented the Data Grid and Query Tool as one component.
*Functionality working for Data Grid*:
- Add/Update/Delete rows if object is editable.
- Copy row
- Paste row
- Refresh
- Client Filter provided by the Backgrid.
- Filter (User specified, By cell Selection, Exclude Selection
- Add Limit to the query result
- Download the data as CSV.
*Todo's for the Data Grid*:
- Find a way to select backgrid cell when rows are not editable. If
backgrid rows are not editable then Filter by selection and Exclude
selection won't work
*Functionality working for Query Tool*:
- Execute sql query.
- Execute the Highlighted sql.
- Cancel the running query.
- Download the data as CSV
*Todo's for the Query Tool*:
- Open as SQL file.
- Save to SQL file.
- Auto Commit/ Rollback.
- Output Panel should be resizable, so that user can
Attached is the patch file. Please review it and let me know the review
comments if any.
--
*Akshay Joshi*
*Principal Software Engineer *
*Phone: +91 20-3058-9517Mobile: +91 976-788-8246*
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
Attachments:
[application/octet-stream] SQLEditor.patch (146.7K, 3-SQLEditor.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/static/js/frame.js b/web/pgadmin/browser/static/js/frame.js
index 92ea5bf..9613f00 100644
--- a/web/pgadmin/browser/static/js/frame.js
+++ b/web/pgadmin/browser/static/js/frame.js
@@ -40,8 +40,12 @@ function(_, pgAdmin) {
that.panel = myPanel;
var frame = new wcIFrame($frameArea, myPanel);
$(myPanel).data('embededFrame', frame);
+ $(myPanel).data('frameInitialized', false);
- setTimeout(function() { frame.openURL(that.url); }, 500);
+ setTimeout(function() {
+ frame.openURL(that.url);
+ $(myPanel).data('frameInitialized', true);
+ }, 100);
if (that.events && _.isObject(that.events)) {
_.each(that.events, function(v, k) {
diff --git a/web/pgadmin/tools/datagrid/__init__.py b/web/pgadmin/tools/datagrid/__init__.py
new file mode 100644
index 0000000..ae33a38
--- /dev/null
+++ b/web/pgadmin/tools/datagrid/__init__.py
@@ -0,0 +1,273 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+"""A blueprint module implementing the datagrid frame."""
+MODULE_NAME = 'datagrid'
+
+import pickle
+import random
+import json
+from flask import Response, url_for, session, request, make_response
+from flask.ext.babel import gettext
+from flask.ext.security import login_required
+from tools.sqleditor.command import *
+from pgadmin.utils import PgAdminModule
+from pgadmin.utils.ajax import make_json_response, bad_request, internal_server_error
+
+
+class DataGridModule(PgAdminModule):
+ """
+ class DataGridModule(PgAdminModule)
+
+ A module class for Edit Grid derived from PgAdminModule.
+ """
+
+ LABEL = "Data Grid"
+
+ def get_own_menuitems(self):
+ return {}
+
+ def get_own_javascripts(self):
+ return [{
+ 'name': 'pgadmin.datagrid',
+ 'path': url_for('datagrid.index') + "datagrid",
+ 'when': None
+ }]
+
+ def get_panels(self):
+ return []
+
+ def get_own_stylesheets(self):
+ return [url_for('datagrid.index') + 'css/datagrid.css']
+
+ def register_preferences(self):
+ self.items_per_page = self.preference.register(
+ 'display', 'items_per_page',
+ gettext("Items per page in grid"), 'integer', 50,
+ category_label=gettext('Display')
+ )
+
+blueprint = DataGridModule(MODULE_NAME, __name__, static_url_path='/static')
+
+
[email protected]("/")
+@login_required
+def index():
+ return bad_request(errormsg=gettext('User can not call this url directly'))
+
+
[email protected]("/css/datagrid.css")
+def datagrid_css():
+ return make_response(
+ render_template('datagrid/css/datagrid.css'),
+ 200, {'Content-Type': 'text/css'}
+ )
+
+
[email protected]("/filter")
+@login_required
+def show_filter():
+ return render_template(MODULE_NAME + '/filter.html')
+
+
[email protected](
+ '/initialize/datagrid/<int:cmd_type>/<obj_type>/<int:sid>/<int:did>/<int:obj_id>',
+ methods=["PUT", "POST"]
+ )
+@login_required
+def initialize_datagrid(cmd_type, obj_type, sid, did, obj_id):
+ """
+ This method is responsible for creating an asynchronous connection.
+ After creating the connection it will instantiate and initialize
+ the object as per the object type. It will also create a unique
+ transaction id and store the information into session variable.
+
+ Args:
+ cmd_type: Contains value for which menu item is clicked.
+ obj_type: Contains type of selected object for which data grid to be render
+ sid: Server Id
+ did: Database Id
+ obj_id: Id of currently selected object
+ """
+
+ if request.data:
+ filter_sql = json.loads(request.data.decode())
+ else:
+ filter_sql = request.args or request.form
+
+ # Create asynchronous connection using random connection id.
+ conn_id = str(random.randint(1, 9999999))
+ try:
+ manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)
+ conn = manager.connection(did=did, conn_id=conn_id)
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ # Connect the Server
+ status, msg = conn.connect()
+ if not status:
+ return internal_server_error(errormsg=str(msg))
+
+ try:
+ # Get the object as per the object type
+ command_obj = ObjectRegistry.get_object(obj_type, conn_id=conn_id, sid=sid,
+ did=did, obj_id=obj_id, cmd_type=cmd_type,
+ sql_filter=filter_sql)
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ # Create a unique id for the transaction
+ trans_id = str(random.randint(1, 9999999))
+
+ if 'gridData' not in session:
+ sql_grid_data = dict()
+ else:
+ sql_grid_data = session['gridData']
+
+ # Use pickle to store the command object which will be used
+ # later by the sql grid module.
+ sql_grid_data[trans_id] = {
+ 'command_obj': pickle.dumps(command_obj, -1) # -1 specify the highest protocol version available
+ }
+
+ # Store the grid dictionary into the session variable
+ session['gridData'] = sql_grid_data
+
+ return make_json_response(data={'gridTransId': trans_id})
+
+
[email protected]('/panel/<int:trans_id>/<is_query_tool>', methods=["GET"])
+def panel(trans_id, is_query_tool):
+ """
+ This method calls index.html to render the data grid.
+
+ Args:
+ trans_id: unique transaction id
+ is_query_tool: True if panel calls when query tool menu is clicked.
+ """
+
+ return render_template("datagrid/index.html", _=gettext, uniqueId=trans_id,
+ is_query_tool=is_query_tool,
+ items_per_page=blueprint.items_per_page.get())
+
+
[email protected](
+ '/initialize/query_tool/<int:sid>/<int:did>',
+ methods=["PUT", "POST"]
+ )
+@login_required
+def initialize_query_tool(sid, did):
+ """
+ This method is responsible for instantiate and initialize
+ the query tool object. It will also create a unique
+ transaction id and store the information into session variable.
+
+ Args:
+ sid: Server Id
+ did: Database Id
+ """
+
+ try:
+ command_obj = ObjectRegistry.get_object('query_tool', sid=sid, did=did)
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ # Create a unique id for the transaction
+ trans_id = str(random.randint(1, 9999999))
+
+ if 'gridData' not in session:
+ sql_grid_data = dict()
+ else:
+ sql_grid_data = session['gridData']
+
+ # Use pickle to store the command object which will be used
+ # later by the sql grid module.
+ sql_grid_data[trans_id] = {
+ 'command_obj': pickle.dumps(command_obj, -1) # -1 specify the highest protocol version available
+ }
+
+ # Store the grid dictionary into the session variable
+ session['gridData'] = sql_grid_data
+
+ return make_json_response(data={'gridTransId': trans_id})
+
+
[email protected]('/close/<int:trans_id>', methods=["GET"])
+def close(trans_id):
+ """
+ This method is used to close the asynchronous connection
+ and remove the information of unique transaction id from
+ the session variable.
+
+ Args:
+ trans_id: unique transaction id
+ """
+
+ grid_data = session['gridData']
+ # Return from the function if transaction id not found
+ if str(trans_id) not in grid_data:
+ return make_json_response(data={'status': True})
+
+ cmd_obj_str = grid_data[str(trans_id)]['command_obj']
+ # Use pickle.loads function to get the command object
+ cmd_obj = pickle.loads(cmd_obj_str)
+
+ try:
+ manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(cmd_obj.sid)
+ conn = manager.connection(did=cmd_obj.did, conn_id=cmd_obj.conn_id)
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ # Release the connection
+ if conn.connected():
+ manager.release(did=cmd_obj.did, conn_id=cmd_obj.conn_id)
+
+ # Remove the information of unique transaction id from the session variable.
+ grid_data.pop(str(trans_id), None)
+ session['gridData'] = grid_data
+
+ return make_json_response(data={'status': True})
+
+
[email protected]('/filter/validate/<int:sid>/<int:did>/<int:obj_id>',
+ methods=["PUT", "POST"])
+@login_required
+def validate_filter(sid, did, obj_id):
+ """
+ This method is used to validate the sql filter.
+
+ Args:
+ sid: Server Id
+ did: Database Id
+ obj_id: Id of currently selected object
+ """
+ if request.data:
+ filter_sql = json.loads(request.data.decode())
+ else:
+ filter_sql = request.args or request.form
+
+ try:
+ # Create object of SQLFilter class
+ sql_filter_obj = SQLFilter(sid=sid, did=did, obj_id=obj_id)
+
+ # Call validate_filter method to validate the SQL.
+ status, res = sql_filter_obj.validate_filter(filter_sql)
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ return make_json_response(data={'status': status, 'result': res})
+
+
[email protected]("/datagrid.js")
+@login_required
+def script():
+ """render the required javascript"""
+ return Response(response=render_template("datagrid/js/datagrid.js", _=gettext),
+ status=200,
+ mimetype="application/javascript")
diff --git a/web/pgadmin/tools/datagrid/static/img/sql-16.png b/web/pgadmin/tools/datagrid/static/img/sql-16.png
new file mode 100644
index 0000000000000000000000000000000000000000..01246deb34dd2e6108cc0d8cdc617a1b043592b3
GIT binary patch
literal 1030
zcmV+h1o``kP)<h;3K|Lk000e1NJLTq000mG000mO0{{R3C@l|D0006yP)t-s0002~
z{{HLg>eSQC&C0;d%fQsq%=-HJ#l5Q5yKwc&UG~sk_10na+GF(FWAoT!)xUDaz^(W9
z_sz0+_sU%N#ar^kTid;B%e#NZyNJrZgww!p?$2NL&R)*9d-nGB#JHmO!dcw4X}Ytd
z!otJJ$;!&f%F4>h%gW2h#JSYBZ}!Dn#JZo&t9AFjSk|&>ySlj0(9ql4+~wuw+}zyN
z)z;S5*VEF|+S%FAuW;0~ZqB1|@U&L5uB64q#dC9Wf`Wp^#>Rkvfqi{_r>Cf_tgYYP
z-^awh&!T3|qj1)oSEs3|(9qGQrl)jtb&rsc)z#I9hlsVcwe$4!&(6=gp?1xqbJw3%
zq@<<L&(MpDi-CcH&d$z$etu6+Pp7A+dwYASsj0`u$G@9#&ZKejsZpk$kIl}{=H}-7
z{QUd-{QCO)_4W4M-QK;ks?Vlr)0Rw=eO%Gd(BR+S?Ck8;)z-hexY?~{*rsOr`T2uy
zTQf5?FEKK8Y+S3Hd!nSGz`?@O($m-1*wfR}#k!v1v|;zDQO>AzR#;pxF*GnSGEPJ_
z&#F@O&|l`rVb{TT;LL>N%zfv>VfMdR_peg+tWn6PfJjJ3Qc+NbX-Dj}R`$hP_0wkd
z*?0BWX!Xxt_QP8DxmNX~P}I=MZf$M3xw*!>t?{l__0(bX-fZ>YdGy|B^w?td%w6}p
zSMjS=$GoiI;o;ug+QzPj>bqd}$Xxc%WA@Hp_Qzc5x?skyhu+=V($C4?-rE(^>ec`N
z00DGTPE!Ct=GbNc000SaNLh0L002k;002k;M#*bF00034Nkl<Z7}H~500KrPW)@a9
zCPoGbVCUfE;^yJy;};NQ2l0i3MMTBKB_yS!Wn|^#fdUGOqDsmts%q*Qnp)aAKmlDn
zeFH-yV-r&|a|=r=pn$cFt)0Dtqm#3XtDCzAP{7m6+sD_>KR_igC^#e(C=eDN5g8R7
z6B`$wkeHMV6i7);OV7y6%*xKm&dtjQX(=cSC@L<=%ql4@E3aT+sI024sjaJTXl!b3
zX>Ds~VCd-V>h9_7>z^=j(&Q;qr!g>0pD}Zm-t0MZ=gnWBw{X#728Ja|mo1;YV&$sU
zYu2t?zX53GMh1pWo40J;wtdIWUAq}zpmO(~J$u1kWdH!YwM+>mmsHCD001R)MObuX
zVRU6WV{&C-bY%cCFflMKF)}SMF;p@zIy5snG&L(QH99abp6)KH0000bbVXQnWMOn=
zI&E)cX=Zr<GB7bPEigGPFgR2&F*-0aIyE^fFg7|cFlfe;3;+NC07*qoM6N<$g3`Az
A761SM
literal 0
HcmV?d00001
diff --git a/web/pgadmin/tools/datagrid/templates/datagrid/css/datagrid.css b/web/pgadmin/tools/datagrid/templates/datagrid/css/datagrid.css
new file mode 100644
index 0000000..6e9925d
--- /dev/null
+++ b/web/pgadmin/tools/datagrid/templates/datagrid/css/datagrid.css
@@ -0,0 +1,7 @@
+.icon-sql-16 {
+ background-image: url('{{ url_for('datagrid.static', filename='img/sql-16.png') }}') !important;
+ background-repeat: no-repeat;
+ align-content: center;
+ vertical-align: middle;
+ height: 20px;
+}
\ No newline at end of file
diff --git a/web/pgadmin/tools/datagrid/templates/datagrid/filter.html b/web/pgadmin/tools/datagrid/templates/datagrid/filter.html
new file mode 100644
index 0000000..34548ca
--- /dev/null
+++ b/web/pgadmin/tools/datagrid/templates/datagrid/filter.html
@@ -0,0 +1,9 @@
+<div class="filter-textarea">
+ <textarea id="sql_filter" row="5"></textarea>
+ <style>
+ .filter-textarea .CodeMirror-scroll {
+ min-height: 120px;
+ max-height: 120px;
+ }
+ </style>
+</div>
\ No newline at end of file
diff --git a/web/pgadmin/tools/datagrid/templates/datagrid/index.html b/web/pgadmin/tools/datagrid/templates/datagrid/index.html
new file mode 100644
index 0000000..55bab8e
--- /dev/null
+++ b/web/pgadmin/tools/datagrid/templates/datagrid/index.html
@@ -0,0 +1,53 @@
+{% extends "base.html" %}
+{% block title %}{{ config.APP_NAME }} - Datagrid{% endblock %}
+{% block css_link %}
+ <link type="text/css" rel="stylesheet" href="{{ url_for('sqleditor.static', filename='css/sqleditor.css') }}">
+{% endblock %}
+{% block body %}
+<style>
+body {
+ padding: 0px;
+}
+</style>
+ <div id="data_panel">
+ <div id="fetching_data" class="pg-sqleditor-busy-fetching hide">',
+ <span class="pg-sqleditor-busy-icon"><img src="{{ url_for('browser.static', filename='css/aciTree/image/load-root.gif') }}"></span>
+ <span class="pg-sqleditor-busy-text"></span>
+ </div>
+ <div class="pg-sql-editor" data-trans-id="{{ uniqueId }}"></div>
+ </div>
+{% endblock %}
+
+{% block init_script %}
+try {
+ require(
+ ['jquery', 'pgadmin', 'pgadmin.sqleditor'],
+ function($, pgAdmin) {
+
+ var editorPanel = $('.pg-sql-editor'),
+ loadingDiv = $('#fetching_data'),
+ msgDiv = loadingDiv.find('.pg-sqleditor-busy-text');
+
+ // Get the controller object from pgAdmin.SqlEditor
+ var sqlEditorController = pgAdmin.SqlEditor.create(editorPanel);
+
+ // Listen on events to show/hide loading-icon and change messages.
+ sqlEditorController.on('pgadmin-sqleditor:loading-icon:message', function(msg) {
+ msgDiv.text(msg);
+ }).on('pgadmin-sqleditor:loading-icon:show', function(msg) {
+ loadingDiv.removeClass('hide');
+ msgDiv.text(msg);
+ }).on('pgadmin-sqleditor:loading-icon:hide', function() {
+ if (!loadingDiv.hasClass('hide')) {
+ loadingDiv.addClass('hide');
+ }
+ });
+
+ // Start the query tool.
+ sqlEditorController.start({{ is_query_tool }}, {{ items_per_page }});
+ });
+} catch (err) {
+ /* Show proper error dialog */
+ console.log(err);
+}
+{% endblock %}
diff --git a/web/pgadmin/tools/datagrid/templates/datagrid/js/datagrid.js b/web/pgadmin/tools/datagrid/templates/datagrid/js/datagrid.js
new file mode 100644
index 0000000..beebc2b
--- /dev/null
+++ b/web/pgadmin/tools/datagrid/templates/datagrid/js/datagrid.js
@@ -0,0 +1,436 @@
+define(
+ ['jquery','alertify', 'pgadmin', 'codemirror', 'pgadmin.browser',
+ 'wcdocker', 'codemirror.sql'],
+ function($, alertify, pgAdmin, CodeMirror) {
+ // Some scripts do export their object in the window only.
+ // Generally the one, which do no have AMD support.
+ var wcDocker = window.wcDocker,
+ pgBrowser = pgAdmin.Browser;
+
+ /* Return back, this has been called more than once */
+ if (pgAdmin.DataGrid)
+ return pgAdmin.DataGrid;
+
+ pgAdmin.DataGrid = {
+ init: function() {
+ if (this.initialized)
+ return;
+ this.initialized = true;
+
+ // Define list of nodes on which view data option appears
+ var supported_nodes = [
+ 'table', 'view',
+ 'foreign-table', 'catalog_object'
+ ],
+
+ /* Enable/disable View data menu in tools based
+ * on node selected. if selected node is present
+ * in supported_nodes, menu will be enabled
+ * otherwise disabled.
+ */
+ view_menu_enabled = function(obj) {
+ if(!_.isUndefined(obj) && !_.isNull(obj))
+ return (_.indexOf(supported_nodes, obj._type) !== -1 ? true: false);
+ else
+ return false;
+ };
+
+ // Define list of nodes on which Query tool option doesn't appears
+ var unsupported_nodes = [
+ 'server-group', 'server', 'coll-tablespace', 'tablespace',
+ 'coll-role', 'role', 'coll-resource_group', 'resource_group',
+ 'coll-database'
+ ],
+
+ /* Enable/disable Query tool menu in tools based
+ * on node selected. if selected node is present
+ * in unsupported_nodes, menu will be disabled
+ * otherwise enabled.
+ */
+ query_tool_menu_enabled = function(obj) {
+ if(!_.isUndefined(obj) && !_.isNull(obj))
+ return (_.indexOf(unsupported_nodes, obj._type) !== -1 ? false: true);
+ else
+ return false;
+ };
+
+ // Define the nodes on which the menus to be appear
+ var menus = [{
+ name: 'view_first_100_rows', module: this, data: {mnuid: 1},
+ applies: ['tools'], callback: 'show_data_grid', enable: view_menu_enabled,
+ category: 'view_data', priority: 1, label: '{{ _('View First 100 Rows') }}'
+ },{
+ name: 'view_last_100_rows', module: this, data: {mnuid: 2},
+ applies: ['tools'], callback: 'show_data_grid', enable: view_menu_enabled,
+ category: 'view_data', priority: 2, label: '{{ _('View Last 100 Rows') }}'
+ },{
+ name: 'view_all_rows', module: this, data: {mnuid: 3},
+ applies: ['tools'], callback: 'show_data_grid', enable: view_menu_enabled,
+ category: 'view_data', priority: 3, label: '{{ _('View All Rows') }}'
+ },{
+ name: 'view_filtered_rows', module: this, data: {mnuid: 4},
+ applies: ['tools'], callback: 'show_filtered_row', enable: view_menu_enabled,
+ category: 'view_data', priority: 4, label: '{{ _('View Filtered Rows...') }}'
+ },{
+ name: 'query_tool', module: this, applies: ['tools'],
+ callback: 'show_query_tool', enable: query_tool_menu_enabled,
+ priority: 1, label: '{{ _('Query tool') }}',
+ icon: 'wcTabIcon icon-sql-16'
+ }];
+
+ // Create context menu
+ for (var idx = 0; idx < supported_nodes.length; idx++) {
+ menus.push({
+ name: 'view_first_100_rows_context_' + supported_nodes[idx],
+ node: supported_nodes[idx], module: this, data: {mnuid: 1},
+ applies: ['context'], callback: 'show_data_grid', enable: view_menu_enabled,
+ category: 'view_data', priority: 1, label: '{{ _('View First 100 Rows') }}'
+ },{
+ name: 'view_last_100_rows_context_' + supported_nodes[idx],
+ node: supported_nodes[idx], module: this, data: {mnuid: 2},
+ applies: ['context'], callback: 'show_data_grid', enable: view_menu_enabled,
+ category: 'view_data', priority: 2, label: '{{ _('View Last 100 Rows') }}'
+ },{
+ name: 'view_all_rows_context_' + supported_nodes[idx],
+ node: supported_nodes[idx], module: this, data: {mnuid: 3},
+ applies: ['context'], callback: 'show_data_grid', enable: view_menu_enabled,
+ category: 'view_data', priority: 3, label: '{{ _('View All Rows') }}'
+ },{
+ name: 'view_filtered_rows_context_' + supported_nodes[idx],
+ node: supported_nodes[idx], module: this, data: {mnuid: 4},
+ applies: ['context'], callback: 'show_filtered_row', enable: view_menu_enabled,
+ category: 'view_data', priority: 4, label: '{{ _('View Filtered Rows...') }}'
+ });
+ }
+
+ pgAdmin.Browser.add_menu_category('view_data', '{{ _('View Data') }}', 2, 'fa fa-th');
+ pgAdmin.Browser.add_menus(menus);
+
+ // Creating a new pgAdmin.Browser frame to show the data.
+ var dataGridFrameType = new pgAdmin.Browser.Frame({
+ name: 'frm_datagrid',
+ title: 'Edit Data',
+ showTitle: true,
+ isCloseable: true,
+ isPrivate: true,
+ url: 'about:blank'
+ });
+
+ // Load the newly created frame
+ dataGridFrameType.load(pgBrowser.docker);
+ },
+
+ // This is a callback function to show data when user click on menu item.
+ show_data_grid: function(data, i) {
+ var self = this,
+ d = pgAdmin.Browser.tree.itemData(i);
+ if (d === undefined) {
+ alertify.alert(
+ 'Data Grid Error',
+ 'No object selected.'
+ );
+ return;
+ }
+
+ // Get the parent data from the tree node hierarchy.
+ var node = pgBrowser.Nodes[d._type],
+ parentData = node.getTreeNodeHierarchy(i);
+
+ // If server, database or schema is undefined then return from the function.
+ if (parentData.server === undefined || parentData.database === undefined) {
+ return;
+ }
+ // If schema, view, catalog object all are undefined then return from the function.
+ if (parentData.schema === undefined && parentData.view === undefined &&
+ parentData.catalog === undefined) {
+ return;
+ }
+
+ var nsp_name = '';
+
+ if (parentData.schema != undefined) {
+ nsp_name = parentData.schema.label;
+ }
+ else if (parentData.view != undefined) {
+ nsp_name = parentData.view.label;
+ }
+ else if (parentData.catalog != undefined) {
+ nsp_name = parentData.catalog.label;
+ }
+
+ var baseUrl = "{{ url_for('datagrid.index') }}" + "initialize/datagrid/" + data.mnuid + "/" + d._type + "/" +
+ parentData.server._id + "/" + parentData.database._id + "/" + d._id;
+
+ var grid_title = parentData.server.label + '-' + parentData.database.label + '-'
+ + nsp_name + '.' + d.label;
+
+ // Initialize the data grid.
+ self.initialize_data_grid(baseUrl, grid_title, '');
+ },
+
+ // This is a callback function to show filtered data when user click on menu item.
+ show_filtered_row: function(data, i) {
+ var self = this,
+ d = pgAdmin.Browser.tree.itemData(i);
+ if (d === undefined) {
+ alertify.alert(
+ 'Data Grid Error',
+ 'No object selected.'
+ );
+ return;
+ }
+
+ // Get the parent data from the tree node hierarchy.
+ var node = pgBrowser.Nodes[d._type],
+ parentData = node.getTreeNodeHierarchy(i);
+
+ // If server or database is undefined then return from the function.
+ if (parentData.server === undefined || parentData.database === undefined) {
+ return;
+ }
+
+ // If schema, view, catalog object all are undefined then return from the function.
+ if (parentData.schema === undefined && parentData.view === undefined &&
+ parentData.catalog === undefined) {
+ return;
+ }
+
+ var nsp_name = '';
+
+ if (parentData.schema != undefined) {
+ nsp_name = parentData.schema.label;
+ }
+ else if (parentData.view != undefined) {
+ nsp_name = parentData.view.label;
+ }
+ else if (parentData.catalog != undefined) {
+ nsp_name = parentData.catalog.label;
+ }
+
+ // Create base url to initialize the edit grid
+ var baseUrl = "{{ url_for('datagrid.index') }}" + "initialize/datagrid/" + data.mnuid + "/" + d._type + "/" +
+ parentData.server._id + "/" + parentData.database._id + "/" + d._id;
+
+ // Create url to validate the SQL filter
+ var validateUrl = "{{ url_for('datagrid.index') }}" + "filter/validate/" +
+ parentData.server._id + "/" + parentData.database._id + "/" + d._id;
+
+ var grid_title = parentData.server.label + '-' + parentData.database.label + '-'
+ + nsp_name + '.' + d.label;
+
+ // Create filter dialog using alertify
+ if (!alertify.filterDialog) {
+ alertify.dialog('filterDialog', function factory() {
+ return {
+ main: function(title, message) {
+ this.set('title', title);
+ this.message = message;
+ },
+
+ setup:function() {
+ return {
+ buttons:[
+ { text: "OK", className: "btn btn-primary" },
+ { text: "Cancel", className: "btn btn-danger" }
+ ],
+ options: { modal: 0, resizable: false, maximizable: false, pinnable: false}
+ };
+ },
+
+ build:function() {},
+ prepare:function() {
+ var $content = $(this.message),
+ $sql_filter = $content.find('#sql_filter');
+
+ this.setContent($content.get(0));
+
+ // Apply CodeMirror to filter text area.
+ this.filter_obj = CodeMirror.fromTextArea($sql_filter.get(0), {
+ lineNumbers: true,
+ lineWrapping: true,
+ matchBrackets: true,
+ indentUnit: 4,
+ mode: "text/x-sql"
+ });
+ },
+
+ callback: function(closeEvent) {
+
+ if (closeEvent.button.text == "{{ _('OK') }}") {
+ var sql = this.filter_obj.getValue();
+
+ // Make ajax call to include the filter by selection
+ $.ajax({
+ url: validateUrl,
+ method: 'POST',
+ async: false,
+ contentType: "application/json",
+ data: JSON.stringify(sql),
+ success: function(res) {
+ if (res.data.status) {
+ // Initialize the data grid.
+ self.initialize_data_grid(baseUrl, grid_title, sql);
+ }
+ else {
+ alertify.alert(
+ 'Validation Error',
+ res.data.result
+ );
+ }
+ },
+ error: function(e) {
+ alertify.alert(
+ 'Validation Error',
+ e
+ );
+ }
+ });
+ }
+ }
+ };
+ });
+ }
+
+ var content = '';
+ $.get("{{ url_for('datagrid.index') }}" + "filter",
+ function(data) {
+ alertify.filterDialog('Data Filter', data).resizeTo(600, 400);
+ }
+ );
+ },
+
+ initialize_data_grid: function(baseUrl, grid_title, sql_filter) {
+ /* Ajax call to initialize the edit grid, which creates
+ * an asynchronous connection and create appropriate query
+ * for the selected node.
+ */
+ $.ajax({
+ url: baseUrl,
+ method: 'POST',
+ dataType: 'json',
+ contentType: "application/json",
+ data: JSON.stringify(sql_filter),
+ success: function(res) {
+
+ /* On successfully initialization find the dashboard panel,
+ * create new panel and add it to the dashboard panel.
+ */
+ var dashboardPanel = pgBrowser.docker.findPanels('dashboard');
+ dataGridPanel = pgBrowser.docker.addPanel('frm_datagrid', wcDocker.DOCK.STACKED, dashboardPanel[0]);
+ dataGridPanel.title(grid_title);
+ dataGridPanel.icon('icon-sql-16');
+ dataGridPanel.focus();
+
+ // Listen on the panel closed event.
+ dataGridPanel.on(wcDocker.EVENT.CLOSED, function() {
+ $.ajax({
+ url: "{{ url_for('datagrid.index') }}" + "close/" + res.data.gridTransId,
+ method: 'GET'
+ });
+ });
+
+ // Open the panel if frame is initialized
+ baseUrl = "{{ url_for('datagrid.index') }}" + "panel/" + res.data.gridTransId + "/false";
+ var openDataGridURL = function(j) {
+ setTimeout(function() {
+ var frameInitialized = j.data('frameInitialized');
+ if (frameInitialized) {
+ var frame = j.data('embededFrame');
+ if (frame) {
+ frame.openURL(baseUrl);
+ }
+ } else {
+ openDataGridURL(j);
+ }
+ }, 100);
+ };
+ openDataGridURL($(dataGridPanel));
+ },
+ error: function(e) {
+ alertify.alert(
+ 'SQL Tool Initialize Error',
+ e.responseJSON.errormsg
+ );
+ }
+ });
+ },
+
+ // This is a callback function to show query tool when user click on menu item.
+ show_query_tool: function(data, i) {
+ var self = this,
+ d = pgAdmin.Browser.tree.itemData(i);
+ if (d === undefined) {
+ alertify.alert(
+ 'Query tool Error',
+ 'No object selected.'
+ );
+ return;
+ }
+
+ // Get the parent data from the tree node hierarchy.
+ var node = pgBrowser.Nodes[d._type],
+ parentData = node.getTreeNodeHierarchy(i);
+
+ // If server, database is undefined then return from the function.
+ if (parentData.server === undefined || parentData.database === undefined) {
+ return;
+ }
+
+ var baseUrl = "{{ url_for('datagrid.index') }}" + "initialize/query_tool/" + parentData.server._id +
+ "/" + parentData.database._id;
+ var grid_title = parentData.database.label + ' on ' + parentData.server.user.name + '@' +
+ parentData.server.label ;
+
+ $.ajax({
+ url: baseUrl,
+ method: 'POST',
+ dataType: 'json',
+ contentType: "application/json",
+ success: function(res) {
+
+ /* On successfully initialization find the dashboard panel,
+ * create new panel and add it to the dashboard panel.
+ */
+ var dashboardPanel = pgBrowser.docker.findPanels('dashboard');
+ queryToolPanel = pgBrowser.docker.addPanel('frm_datagrid', wcDocker.DOCK.STACKED, dashboardPanel[0]);
+ queryToolPanel.title(grid_title);
+ queryToolPanel.icon('icon-sql-16');
+ queryToolPanel.focus();
+
+ // Listen on the panel closed event.
+ queryToolPanel.on(wcDocker.EVENT.CLOSED, function() {
+ $.ajax({
+ url: "{{ url_for('datagrid.index') }}" + "close/" + res.data.gridTransId,
+ method: 'GET'
+ });
+ });
+
+ // Open the panel if frame is initialized
+ baseUrl = "{{ url_for('datagrid.index') }}" + "panel/" + res.data.gridTransId + "/true";
+ var openQueryToolURL = function(j) {
+ setTimeout(function() {
+ var frameInitialized = j.data('frameInitialized');
+ if (frameInitialized) {
+ var frame = j.data('embededFrame');
+ if (frame) {
+ frame.openURL(baseUrl);
+ }
+ } else {
+ openQueryToolURL(j);
+ }
+ }, 100);
+ };
+ openQueryToolURL($(queryToolPanel));
+ },
+ error: function(e) {
+ alertify.alert(
+ 'Query Tool Initialize Error',
+ e.responseJSON.errormsg
+ );
+ }
+ });
+ }
+ };
+
+ return pgAdmin.DataGrid;
+ });
diff --git a/web/pgadmin/tools/sqleditor/__init__.py b/web/pgadmin/tools/sqleditor/__init__.py
new file mode 100644
index 0000000..e2cd8ad
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/__init__.py
@@ -0,0 +1,675 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+"""A blueprint module implementing the sqleditor frame."""
+MODULE_NAME = 'sqleditor'
+
+import json
+import pickle
+import random
+from flask import Response, url_for, render_template, session, request
+from flask.ext.babel import gettext
+from flask.ext.security import login_required
+from pgadmin.utils import PgAdminModule
+from pgadmin.utils.ajax import make_json_response, bad_request, success_return, internal_server_error
+from pgadmin.utils.driver import get_driver
+from config import PG_DEFAULT_DRIVER
+
+# Constants
+ASYNC_OK = 1
+ASYNC_READ_TIMEOUT = 2
+ASYNC_WRITE_TIMEOUT = 3
+ASYNC_NOT_CONNECTED = 4
+ASYNC_EXECUTION_ABORTED = 5
+
+
+class SqlEditorModule(PgAdminModule):
+ """
+ class SqlEditorModule(PgAdminModule)
+
+ A module class for SQL Grid derived from PgAdminModule.
+ """
+
+ def get_own_menuitems(self):
+ return {}
+
+ def get_own_javascripts(self):
+ return [{
+ 'name': 'pgadmin.sqleditor',
+ 'path': url_for('sqleditor.index') + "sqleditor",
+ 'when': None
+ }]
+
+ def get_panels(self):
+ return []
+
+blueprint = SqlEditorModule(MODULE_NAME, __name__, static_url_path='/static')
+
[email protected]('/')
+@login_required
+def index():
+ return bad_request(errormsg=gettext('User can not call this url directly'))
+
+
+def update_session_grid_transaction(trans_id, data):
+ grid_data = session['gridData']
+ grid_data[str(trans_id)] = data
+
+ session['gridData'] = grid_data
+
+
+def check_transaction_status(trans_id):
+ """
+ This function is used to check the transaction id
+ is available in the session object and connection
+ status.
+
+ Args:
+ trans_id:
+
+ Returns: status and connection object
+
+ """
+ grid_data = session['gridData']
+
+ # Return from the function if transaction id not found
+ if str(trans_id) not in grid_data:
+ return False, gettext('Transaction id not found in the session.'), None, None, None
+
+ # Fetch the object for the specified transaction id.
+ # Use pickle.loads function to get the command object
+ session_obj = grid_data[str(trans_id)]
+ trans_obj = pickle.loads(session_obj['command_obj'])
+
+ try:
+ manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(trans_obj.sid)
+ conn = manager.connection(did=trans_obj.did, conn_id=trans_obj.conn_id)
+ except Exception as e:
+ return False, internal_server_error(errormsg=str(e)), None, None, None
+
+ if conn.connected():
+ return True, None, conn, trans_obj, session_obj
+ else:
+ return False, gettext('Not connected to server Or connection with the server has been closed.'), \
+ None, trans_obj, session_obj
+
+
[email protected]('/view_data/start/<int:trans_id>', methods=["GET"])
+@login_required
+def start_view_data(trans_id):
+ """
+ This method is used to execute query using asynchronous connection.
+
+ Args:
+ trans_id: unique transaction id
+ """
+ limit = -1
+
+ # Check the transaction and connection status
+ status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id)
+ if status and conn is not None \
+ and trans_obj is not None and session_obj is not None:
+ try:
+
+ # Fetch the sql and primary_keys from the object
+ sql = trans_obj.get_sql()
+ pk_names, primary_keys = trans_obj.get_primary_keys()
+
+ # Fetch the applied filter.
+ filter_applied = trans_obj.is_filter_applied()
+
+ # Fetch the limit for the SQL query
+ limit = trans_obj.get_limit()
+
+ can_edit = trans_obj.can_edit()
+ can_filter = trans_obj.can_filter()
+
+ # Store the primary keys to the session object
+ session_obj['primary_keys'] = primary_keys
+ update_session_grid_transaction(trans_id, session_obj)
+
+ # Execute sql asynchronously
+ status, result = conn.execute_async(sql)
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+ else:
+ status = False
+ result = error_msg
+ filter_applied = False
+ can_edit = False
+ can_filter = False
+
+ return make_json_response(
+ data={
+ 'status': status, 'result': result,
+ 'filter_applied': filter_applied,
+ 'limit': limit, 'can_edit': can_edit,
+ 'can_filter': can_filter
+ }
+ )
+
+
[email protected]('/query_tool/start/<int:trans_id>', methods=["PUT", "POST"])
+@login_required
+def start_query_tool(trans_id):
+ """
+ This method is used to execute query using asynchronous connection.
+
+ Args:
+ trans_id: unique transaction id
+ """
+
+ if request.data:
+ sql = json.loads(request.data.decode())
+ else:
+ sql = request.args or request.form
+
+ grid_data = session['gridData']
+
+ # Return from the function if transaction id not found
+ if str(trans_id) not in grid_data:
+ return make_json_response(
+ data={
+ 'status': False, 'result': gettext('Transaction id not found in the session.'),
+ 'can_edit': False, 'can_filter': False
+ }
+ )
+
+ # Fetch the object for the specified transaction id.
+ # Use pickle.loads function to get the command object
+ session_obj = grid_data[str(trans_id)]
+ trans_obj = pickle.loads(session_obj['command_obj'])
+
+ can_edit = False
+ can_filter = False
+
+ if trans_obj is not None and session_obj is not None:
+ conn_id = trans_obj.conn_id
+
+ # if conn_id is None then we will have to create a new connection
+ if conn_id is None:
+ # Create asynchronous connection using random connection id.
+ conn_id = str(random.randint(1, 9999999))
+
+ try:
+ manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(trans_obj.sid)
+ conn = manager.connection(did=trans_obj.did, conn_id=conn_id)
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ # Connect to the Server if not connected.
+ if not conn.connected():
+ status, msg = conn.connect()
+ if not status:
+ return internal_server_error(errormsg=str(msg))
+
+ if conn.connected():
+ # on successful connection set the connection id to the
+ # transaction object
+ trans_obj.set_connection_id(conn_id)
+
+ # As we changed the transaction object we need to
+ # restore it and update the session variable.
+ session_obj['command_obj'] = pickle.dumps(trans_obj, -1)
+ update_session_grid_transaction(trans_id, session_obj)
+
+ # Execute sql asynchronously with params is None
+ # and formatted_error is True.
+ status, result = conn.execute_async(sql)
+ else:
+ status = False
+ result = gettext('Not connected to server Or connection with the server has been closed.')
+
+ can_edit = trans_obj.can_edit()
+ can_filter = trans_obj.can_filter()
+
+ else:
+ status = False
+ result = gettext('Either Transaction object or Session object not found.')
+
+ return make_json_response(
+ data={
+ 'status': status, 'result': result,
+ 'can_edit': can_edit, 'can_filter': can_filter
+ }
+ )
+
+
[email protected]('/poll/<int:trans_id>', methods=["GET"])
+@login_required
+def poll(trans_id):
+ """
+ This method polls the result of the asynchronous query and returns the result.
+
+ Args:
+ trans_id: unique transaction id
+ """
+ col_info = None
+ primary_keys = None
+
+ # Check the transaction and connection status
+ status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id)
+ if status and conn is not None and session_obj is not None:
+ status, result, col_info = conn.poll()
+ if status == ASYNC_OK:
+ status = 'Success'
+ if 'primary_keys' in session_obj:
+ primary_keys = session_obj['primary_keys']
+ elif status == ASYNC_EXECUTION_ABORTED:
+ status = 'Cancel'
+ else:
+ status = 'Busy'
+ else:
+ status = 'NotConnected'
+ result = error_msg
+
+ # Check column info is available or not
+ if col_info is not None and len(col_info) > 0:
+ columns = dict()
+
+ for col in col_info:
+ col_type = dict()
+ col_type['type_code'] = col[1]
+ col_type['type_name'] = None
+ columns[col[0]] = col_type
+
+ # As we changed the transaction object we need to
+ # restore it and update the session variable.
+ session_obj['columns_info'] = columns
+ update_session_grid_transaction(trans_id, session_obj)
+ else:
+ if result is None:
+ result = conn.status_message()
+
+ return make_json_response(data={'status': status, 'result': result,
+ 'colinfo': col_info, 'primary_keys': primary_keys})
+
+
[email protected]('/fetch/types/<int:trans_id>', methods=["GET"])
+@login_required
+def fetch_pg_types(trans_id):
+ """
+ This method is used to fetch the pg types, which is required
+ to map the data type comes as a result of the query.
+
+ Args:
+ trans_id: unique transaction id
+ """
+
+ # Check the transaction and connection status
+ status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id)
+ if status and conn is not None \
+ and trans_obj is not None and session_obj is not None:
+
+ # List of oid for which we need type name from pg_type
+ oid = ''
+ for col in session_obj['columns_info']:
+ type_obj = session_obj['columns_info'][col]
+ oid += str(type_obj['type_code']) + ','
+
+ # Remove extra comma
+ oid = oid[:-1]
+ status, res = conn.execute_dict(
+ """SELECT oid, format_type(oid,null) as typname FROM pg_type WHERE oid IN ({0}) ORDER BY oid;
+""".format(oid))
+
+ if status:
+ # iterate through pg_types and update the type name in session object
+ for record in res['rows']:
+ for col in session_obj['columns_info']:
+ type_obj = session_obj['columns_info'][col]
+ if type_obj['type_code'] == record['oid']:
+ type_obj['type_name'] = record['typname']
+
+ update_session_grid_transaction(trans_id, session_obj)
+ else:
+ status = False
+ res = error_msg
+
+ return make_json_response(data={'status': status, 'result': res})
+
+
[email protected]('/save/<int:trans_id>', methods=["PUT", "POST"])
+@login_required
+def save(trans_id):
+ """
+ This method is used to save the changes to the server
+
+ Args:
+ trans_id: unique transaction id
+ """
+
+ if request.data:
+ changed_data = json.loads(request.data.decode())
+ else:
+ changed_data = request.args or request.form
+
+ # Check the transaction and connection status
+ status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id)
+ if status and conn is not None \
+ and trans_obj is not None and session_obj is not None:
+
+ # If there is no primary key found then return from the function.
+ if len(session_obj['primary_keys']) <= 0 or len(changed_data) <= 0:
+ return make_json_response(
+ data={'status': False,
+ 'result': gettext('No primary key found for this object, so unable to save records.')}
+ )
+
+ status, res = trans_obj.save(changed_data)
+ else:
+ status = False
+ res = error_msg
+
+ return make_json_response(data={'status': status, 'result': res})
+
+
[email protected]('/filter/get/<int:trans_id>', methods=["GET"])
+@login_required
+def get_filter(trans_id):
+ """
+ This method is used to get the existing filter.
+
+ Args:
+ trans_id: unique transaction id
+ """
+
+ # Check the transaction and connection status
+ status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id)
+ if status and conn is not None \
+ and trans_obj is not None and session_obj is not None:
+
+ res = trans_obj.get_filter()
+ else:
+ status = False
+ res = error_msg
+
+ return make_json_response(data={'status': status, 'result': res})
+
+
[email protected]('/filter/apply/<int:trans_id>', methods=["PUT", "POST"])
+@login_required
+def apply_filter(trans_id):
+ """
+ This method is used to apply the filter.
+
+ Args:
+ trans_id: unique transaction id
+ """
+ if request.data:
+ filter_sql = json.loads(request.data.decode())
+ else:
+ filter_sql = request.args or request.form
+
+ # Check the transaction and connection status
+ status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id)
+ if status and conn is not None \
+ and trans_obj is not None and session_obj is not None:
+
+ status, res = trans_obj.set_filter(filter_sql)
+
+ # As we changed the transaction object we need to
+ # restore it and update the session variable.
+ session_obj['command_obj'] = pickle.dumps(trans_obj, -1)
+ update_session_grid_transaction(trans_id, session_obj)
+ else:
+ status = False
+ res = error_msg
+
+ return make_json_response(data={'status': status, 'result': res})
+
+
[email protected]('/filter/inclusive/<int:trans_id>', methods=["PUT", "POST"])
+@login_required
+def append_filter_inclusive(trans_id):
+ """
+ This method is used to append and apply the filter.
+
+ Args:
+ trans_id: unique transaction id
+ """
+ if request.data:
+ filter_data = json.loads(request.data.decode())
+ else:
+ filter_data = request.args or request.form
+
+ # Check the transaction and connection status
+ status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id)
+ if status and conn is not None \
+ and trans_obj is not None and session_obj is not None:
+
+ res = None
+ filter_sql = ''
+ driver = get_driver(PG_DEFAULT_DRIVER)
+
+ for column_name in filter_data:
+ column_value = filter_data[column_name]
+ if column_value is None:
+ filter_sql = driver.qtIdent(conn, column_name) + ' IS NULL '
+ else:
+ filter_sql = driver.qtIdent(conn, column_name) + ' = ' + driver.qtLiteral(column_value)
+
+ trans_obj.append_filter(filter_sql)
+
+ # As we changed the transaction object we need to
+ # restore it and update the session variable.
+ session_obj['command_obj'] = pickle.dumps(trans_obj, -1)
+ update_session_grid_transaction(trans_id, session_obj)
+ else:
+ status = False
+ res = error_msg
+
+ return make_json_response(data={'status': status, 'result': res})
+
+
[email protected]('/filter/exclusive/<int:trans_id>', methods=["PUT", "POST"])
+@login_required
+def append_filter_exclusive(trans_id):
+ """
+ This method is used to append and apply the filter.
+
+ Args:
+ trans_id: unique transaction id
+ """
+ if request.data:
+ filter_data = json.loads(request.data.decode())
+ else:
+ filter_data = request.args or request.form
+
+ # Check the transaction and connection status
+ status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id)
+ if status and conn is not None \
+ and trans_obj is not None and session_obj is not None:
+
+ res = None
+ filter_sql = ''
+ driver = get_driver(PG_DEFAULT_DRIVER)
+
+ for column_name in filter_data:
+ column_value = filter_data[column_name]
+ if column_value is None:
+ filter_sql = driver.qtIdent(conn, column_name) + ' IS NOT NULL '
+ else:
+ filter_sql = driver.qtIdent(conn, column_name) + ' IS DISTINCT FROM ' + driver.qtLiteral(column_value)
+
+ # Call the append_filter method of transaction object
+ trans_obj.append_filter(filter_sql)
+
+ # As we changed the transaction object we need to
+ # restore it and update the session variable.
+ session_obj['command_obj'] = pickle.dumps(trans_obj, -1)
+ update_session_grid_transaction(trans_id, session_obj)
+ else:
+ status = False
+ res = error_msg
+
+ return make_json_response(data={'status': status, 'result': res})
+
+
[email protected]('/filter/remove/<int:trans_id>', methods=["PUT", "POST"])
+@login_required
+def remove_filter(trans_id):
+ """
+ This method is used to remove the filter.
+
+ Args:
+ trans_id: unique transaction id
+ """
+
+ # Check the transaction and connection status
+ status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id)
+ if status and conn is not None \
+ and trans_obj is not None and session_obj is not None:
+
+ res = None
+
+ # Call the remove_filter method of transaction object
+ trans_obj.remove_filter()
+
+ # As we changed the transaction object we need to
+ # restore it and update the session variable.
+ session_obj['command_obj'] = pickle.dumps(trans_obj, -1)
+ update_session_grid_transaction(trans_id, session_obj)
+ else:
+ status = False
+ res = error_msg
+
+ return make_json_response(data={'status': status, 'result': res})
+
+
[email protected]('/limit/<int:trans_id>', methods=["PUT", "POST"])
+@login_required
+def set_limit(trans_id):
+ """
+ This method is used to set the limit for the SQL.
+
+ Args:
+ trans_id: unique transaction id
+ """
+ if request.data:
+ limit = json.loads(request.data.decode())
+ else:
+ limit = request.args or request.form
+
+ # Check the transaction and connection status
+ status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id)
+ if status and conn is not None \
+ and trans_obj is not None and session_obj is not None:
+
+ res = None
+
+ # Call the set_limit method of transaction object
+ trans_obj.set_limit(limit)
+
+ # As we changed the transaction object we need to
+ # restore it and update the session variable.
+ session_obj['command_obj'] = pickle.dumps(trans_obj, -1)
+ update_session_grid_transaction(trans_id, session_obj)
+ else:
+ status = False
+ res = error_msg
+
+ return make_json_response(data={'status': status, 'result': res})
+
+
[email protected]('/cancel/<int:trans_id>', methods=["PUT", "POST"])
+@login_required
+def cancel_transaction(trans_id):
+ """
+ This method is used to cancel the running transaction
+
+ Args:
+ trans_id: unique transaction id
+ """
+
+ grid_data = session['gridData']
+
+ # Return from the function if transaction id not found
+ if str(trans_id) not in grid_data:
+ return make_json_response(
+ data={
+ 'status': False, 'result': gettext('Transaction id not found in the session.')
+ }
+ )
+
+ # Fetch the object for the specified transaction id.
+ # Use pickle.loads function to get the command object
+ session_obj = grid_data[str(trans_id)]
+ trans_obj = pickle.loads(session_obj['command_obj'])
+
+ if trans_obj is not None and session_obj is not None:
+
+ # Fetch the main connection object for the database.
+ try:
+ manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(trans_obj.sid)
+ conn = manager.connection(did=trans_obj.did)
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ delete_connection = False
+
+ # Connect to the Server if not connected.
+ if not conn.connected():
+ status, msg = conn.connect()
+ if not status:
+ return internal_server_error(errormsg=str(msg))
+ delete_connection = True
+
+ if conn.connected():
+ # on successful connection cancel the running transaction
+ status, result = conn.cancel_transaction(trans_obj.conn_id, trans_obj.did)
+
+ # Delete connection if we have created it to
+ # cancel the transaction
+ if delete_connection:
+ manager.release(did=trans_obj.did)
+ else:
+ status = False
+ result = gettext('Not connected to server Or connection with the server has been closed.')
+ else:
+ status = False
+ result = gettext('Either Transaction object or Session object not found.')
+
+ return make_json_response(
+ data={
+ 'status': status, 'result': result
+ }
+ )
+
+
[email protected]('/object/get/<int:trans_id>', methods=["GET"])
+@login_required
+def get_object_name(trans_id):
+ """
+ This method is used to get the object name
+
+ Args:
+ trans_id: unique transaction id
+ """
+
+ # Check the transaction and connection status
+ status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id)
+ if status and conn is not None \
+ and trans_obj is not None and session_obj is not None:
+
+ res = trans_obj.object_name
+ else:
+ status = False
+ res = error_msg
+
+ return make_json_response(data={'status': status, 'result': res})
+
+
[email protected]("/sqleditor.js")
+@login_required
+def script():
+ """render the required javascript"""
+ return Response(response=render_template("sqleditor/js/sqleditor.js", _=gettext),
+ status=200,
+ mimetype="application/javascript")
diff --git a/web/pgadmin/tools/sqleditor/command.py b/web/pgadmin/tools/sqleditor/command.py
new file mode 100644
index 0000000..53984ca
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/command.py
@@ -0,0 +1,614 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+""" Implemented classes for the different object type used by data grid """
+
+from abc import ABCMeta, abstractmethod
+from flask import render_template
+from flask.ext.babel import gettext
+from pgadmin.utils.driver import get_driver
+from pgadmin.utils.ajax import forbidden
+from config import PG_DEFAULT_DRIVER
+import six
+
+VIEW_FIRST_100_ROWS = 1
+VIEW_LAST_100_ROWS = 2
+VIEW_ALL_ROWS = 3
+VIEW_FILTERED_ROWS = 4
+
+
+class ObjectRegistry(ABCMeta):
+ """
+ class ObjectRegistry(ABCMeta)
+ Every object will be registered automatically by its object type.
+
+ Class-level Methods:
+ ----------- -------
+ * get_object(cls, name, **kwargs)
+ - This method returns the object based on register object type
+ else return not implemented error.
+ """
+
+ registry = dict()
+
+ def __init__(cls, name, bases, d):
+ """
+ This method is used to register the objects based on object type.
+ """
+
+ if d and 'object_type' in d:
+ ObjectRegistry.registry[d['object_type']] = cls
+
+ ABCMeta.__init__(cls, name, bases, d)
+
+ @classmethod
+ def get_object(cls, name, **kwargs):
+ """
+ This method returns the object based on register object type
+ else return not implemented error
+
+ Args:
+ name: object type for which object to be returned.
+ **kwargs: N number of parameters
+ """
+
+ if name in ObjectRegistry.registry:
+ return (ObjectRegistry.registry[name])(**kwargs)
+
+ raise NotImplementedError(
+ gettext("This feature for object type - '{0}' has not been implemented!").format(name)
+ )
+
+
[email protected]_metaclass(ObjectRegistry)
+class BaseCommand(object):
+ """
+ class BaseCommand
+
+ It is a base class for SQL Tools like data grid and query tool.
+ A different sql tools must implement this to expose abstract methods.
+
+ Abstract Methods:
+ -------- -------
+ * get_sql()
+ - This method returns the proper SQL query for the object type.
+
+ * can_edit()
+ - This method returns True/False, specifying whether data is
+ editable or not.
+
+ * can_filter()
+ - This method returns True/False, specifying whether filter
+ will be applied on data or not.
+ """
+
+ def __init__(self, **kwargs):
+ """
+ This method is used to initialize the class and
+ create a proper object name which will be used
+ to fetch the data using namespace name and object name.
+
+ Args:
+ **kwargs : N number of parameters
+ """
+
+ # Save the server id and database id, namespace id, object id
+ self.sid = kwargs['sid'] if 'sid' in kwargs else None
+ self.did = kwargs['did'] if 'did' in kwargs else None
+
+ @abstractmethod
+ def get_sql(self):
+ pass
+
+ @abstractmethod
+ def can_edit(self):
+ pass
+
+ @abstractmethod
+ def can_filter(self):
+ pass
+
+
+class SQLFilter(object):
+ """
+ class SQLFilter
+
+ Implementation of filter class for sql grid.
+
+ Class-level Methods:
+ ----------- -------
+ * get_filter()
+ - This method returns the filter applied.
+ * set_filter(row_filter)
+ - This method sets the filter to be applied.
+ * append_filter(row_filter)
+ - This method is used to append the filter within existing filter
+ * remove_filter()
+ - This method removes the filter applied.
+ * validate_filter(row_filter)
+ - This method validates the given filter.
+ """
+
+ def __init__(self, **kwargs):
+ """
+ This method is used to initialize the class and
+ create a proper object name which will be used
+ to fetch the data using namespace name and object name.
+
+ Args:
+ **kwargs : N number of parameters
+ """
+ # Save the server id and database id, namespace id, object id
+ assert ('sid' in kwargs)
+ assert ('did' in kwargs)
+ assert ('obj_id' in kwargs)
+
+ self.sid = kwargs['sid']
+ self.did = kwargs['did']
+ self.obj_id = kwargs['obj_id']
+ self.__row_filter = kwargs['sql_filter'] if 'sql_filter' in kwargs else None
+
+ manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(self.sid)
+ conn = manager.connection(did=self.did)
+
+ ver = manager.version
+ # we will set template path for sql scripts
+ if ver >= 90100:
+ self.sql_path = 'sqleditor/sql/9.1_plus'
+
+ if conn.connected():
+ # Fetch the Namespace Name and object Name
+ query = render_template("/".join([self.sql_path, 'objectname.sql']), obj_id=self.obj_id)
+
+ status, result = conn.execute_dict(query)
+ if not status:
+ raise Exception(result)
+
+ self.nsp_name = result['rows'][0]['nspname']
+ self.object_name = result['rows'][0]['relname']
+ else:
+ raise Exception(gettext('Not connected to server or connection with the server has been closed.'))
+
+ def get_filter(self):
+ """
+ This function returns the filter.
+ """
+ return self.__row_filter
+
+ def set_filter(self, row_filter):
+ """
+ This function validates the filter and set the
+ given filter to member variable.
+
+ Args:
+ row_filter: sql query
+ """
+
+ status, msg = self.validate_filter(row_filter)
+
+ if status:
+ self.__row_filter = row_filter
+
+ return status, msg
+
+ def is_filter_applied(self):
+ """
+ This function returns True if filter is applied else False.
+ """
+ if self.__row_filter is None or self.__row_filter == '':
+ return False
+
+ return True
+
+ def remove_filter(self):
+ """
+ This function remove the filter by setting value to None.
+ """
+ self.__row_filter = None
+
+ def append_filter(self, row_filter):
+ """
+ This function will used to get the existing filter and append
+ the given filter.
+
+ Args:
+ row_filter: sql query to append
+ """
+
+ existing_filter = self.get_filter()
+
+ if existing_filter is None or existing_filter == '':
+ self.__row_filter = row_filter
+ else:
+ self.__row_filter = existing_filter + ' \n AND ' + row_filter
+
+ def validate_filter(self, row_filter):
+ """
+ This function validates the given filter.
+
+ Args:
+ row_filter: sql syntax to validate
+ """
+ status = True
+ result = None
+
+ if row_filter is None or row_filter == '':
+ return False, gettext('Filter string is empty!')
+
+ manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(self.sid)
+ conn = manager.connection(did=self.did)
+
+ if conn.connected():
+ sql = render_template("/".join([self.sql_path, 'validate.sql']),
+ nsp_name=self.nsp_name, object_name=self.object_name, row_filter=row_filter)
+
+ status, result = conn.execute_scalar(sql)
+ if not status:
+ result = result.partition("\n")[0]
+
+ return status, result
+
+
+class GridCommand(BaseCommand, SQLFilter):
+ """
+ class GridCommand(object)
+
+ It is a base class for different object type used by data grid.
+ A different object type must implement this to expose abstract methods.
+
+ Class-level Methods:
+ ----------- -------
+ * get_primary_keys()
+ - Derived class can implement there own logic to get the primary keys.
+
+ * save()
+ - Derived class can implement there own logic to save the data into the database.
+
+ * set_limit(limit)
+ - This method sets the limit for SQL query
+
+ * get_limit()
+ - This method returns the limit.
+ """
+
+ def __init__(self, **kwargs):
+ """
+ This method is used to call base class init to initialize
+ the data.
+
+ Args:
+ **kwargs : N number of parameters
+ """
+ BaseCommand.__init__(self, **kwargs)
+ SQLFilter.__init__(self, **kwargs)
+
+ # Save the connection id, command type
+ self.conn_id = kwargs['conn_id'] if 'conn_id' in kwargs else None
+ self.cmd_type = kwargs['cmd_type'] if 'cmd_type' in kwargs else None
+ self.limit = -1
+
+ if self.cmd_type == VIEW_FIRST_100_ROWS or self.cmd_type == VIEW_LAST_100_ROWS:
+ self.limit = 100
+
+ def get_primary_keys(self):
+ return None, None
+
+ def save(self, changed_data):
+ return forbidden(errmsg=gettext("Not allowed to save the data for the selected object!"))
+
+ def get_limit(self):
+ """
+ This function returns the limit for the SQL query.
+ """
+ return self.limit
+
+ def set_limit(self, limit):
+ """
+ This function sets the limit for the SQL query
+ Args:
+ limit: limit to be set for SQL.
+ """
+ self.limit = limit
+
+
+class TableCommand(GridCommand):
+ """
+ class TableCommand(GridCommand)
+
+ It is a derived class for Table type.
+ """
+ object_type = 'table'
+
+ def __init__(self, **kwargs):
+ """
+ This method calls the __init__ method of the base class
+ to get the proper object name.
+
+ Args:
+ **kwargs : N number of parameters
+ """
+
+ # call base class init to fetch the table name
+ super(TableCommand, self).__init__(**kwargs)
+
+ def get_sql(self):
+ """
+ This method is used to create a proper SQL query
+ to fetch the data for the specified table
+ """
+
+ # Fetch the primary keys for the table
+ pk_names, primary_keys = self.get_primary_keys()
+
+ sql_filter = self.get_filter()
+
+ if sql_filter is None:
+ sql = render_template("/".join([self.sql_path, 'objectquery.sql']), object_name=self.object_name,
+ nsp_name=self.nsp_name, pk_names=pk_names, cmd_type=self.cmd_type,
+ limit=self.limit)
+ else:
+ sql = render_template("/".join([self.sql_path, 'objectquery.sql']), object_name=self.object_name,
+ nsp_name=self.nsp_name, pk_names=pk_names, cmd_type=self.cmd_type,
+ sql_filter=sql_filter, limit=self.limit)
+
+ return sql
+
+ def get_primary_keys(self):
+ """
+ This function is used to fetch the primary key columns.
+ """
+
+ manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(self.sid)
+ conn = manager.connection(did=self.did, conn_id=self.conn_id)
+
+ pk_names = ''
+ primary_keys = dict()
+
+ if conn.connected():
+
+ # Fetch the primary key column names
+ query = render_template("/".join([self.sql_path, 'primary_keys.sql']), obj_id=self.obj_id)
+
+ status, result = conn.execute_dict(query)
+ if not status:
+ raise Exception(result)
+
+ for row in result['rows']:
+ pk_names += row['attname'] + ','
+ primary_keys[row['attname']] = row['typname']
+
+ if pk_names != '':
+ # Remove last character from the string
+ pk_names = pk_names[:-1]
+ else:
+ raise Exception(gettext('Not connected to server Or connection with the server has been closed.'))
+
+ return pk_names, primary_keys
+
+ def can_edit(self):
+ return True
+
+ def can_filter(self):
+ return True
+
+ def save(self, changed_data):
+ """
+ This function is used to save the data into the database.
+ Depending on condition it will either update or insert the
+ new row into the database.
+
+ Args:
+ changed_data: Contains data to be saved
+ """
+
+ manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(self.sid)
+ conn = manager.connection(did=self.did, conn_id=self.conn_id)
+
+ status = False
+ res = None
+
+ if conn.connected():
+
+ # Start the transaction
+ conn.execute_void('BEGIN;')
+
+ # Iterate total number of records to be updated/inserted
+ for row in changed_data:
+
+ # if no data to be saved then continue
+ if 'data' not in row:
+ continue
+
+ # if 'keys' is present in row then it is and update query
+ # else it is an insert query.
+ if 'keys' in row:
+ # if 'marked_for_deletion' is present in row and it is true then delete
+ if 'marked_for_deletion' in row and row['marked_for_deletion']:
+ sql = render_template("/".join([self.sql_path, 'delete.sql']),
+ primary_keys=row['keys'], object_name=self.object_name,
+ nsp_name=self.nsp_name)
+ else:
+ sql = render_template("/".join([self.sql_path, 'update.sql']), object_name=self.object_name,
+ data_to_be_saved=row['data'], primary_keys=row['keys'],
+ nsp_name=self.nsp_name)
+ else:
+ sql = render_template("/".join([self.sql_path, 'create.sql']), object_name=self.object_name,
+ data_to_be_saved=row['data'], nsp_name=self.nsp_name)
+
+ status, res = conn.execute_void(sql)
+ if not status:
+ conn.execute_void('ROLLBACK;')
+ return status, res
+
+ # Commit the transaction if there is no error found
+ conn.execute_void('COMMIT;')
+
+ return status, res
+
+
+class ViewCommand(GridCommand):
+ """
+ class ViewCommand(GridCommand)
+
+ It is a derived class for View type.
+ """
+ object_type = 'view'
+
+ def __init__(self, **kwargs):
+ """
+ This method calls the __init__ method of the base class
+ to get the proper object name.
+
+ Args:
+ **kwargs : N number of parameters
+ """
+
+ # call base class init to fetch the table name
+ super(ViewCommand, self).__init__(**kwargs)
+
+ def get_sql(self):
+ """
+ This method is used to create a proper SQL query
+ to fetch the data for the specified view
+ """
+ sql_filter = self.get_filter()
+
+ if sql_filter is None:
+ sql = render_template("/".join([self.sql_path, 'objectquery.sql']), object_name=self.object_name,
+ nsp_name=self.nsp_name, cmd_type=self.cmd_type,
+ limit=self.limit)
+ else:
+ sql = render_template("/".join([self.sql_path, 'objectquery.sql']), object_name=self.object_name,
+ nsp_name=self.nsp_name, cmd_type=self.cmd_type,
+ sql_filter=sql_filter, limit=self.limit)
+
+ return sql
+
+ def can_edit(self):
+ return False
+
+ def can_filter(self):
+ return True
+
+
+class ForeignTableCommand(GridCommand):
+ """
+ class ForeignTableCommand(GridCommand)
+
+ It is a derived class for ForeignTable type.
+ """
+ object_type = 'foreign-table'
+
+ def __init__(self, **kwargs):
+ """
+ This method calls the __init__ method of the base class
+ to get the proper object name.
+
+ Args:
+ **kwargs : N number of parameters
+ """
+
+ # call base class init to fetch the table name
+ super(ForeignTableCommand, self).__init__(**kwargs)
+
+ def get_sql(self):
+ """
+ This method is used to create a proper SQL query
+ to fetch the data for the specified foreign table
+ """
+ sql_filter = self.get_filter()
+
+ if sql_filter is None:
+ sql = render_template("/".join([self.sql_path, 'objectquery.sql']), object_name=self.object_name,
+ nsp_name=self.nsp_name, cmd_type=self.cmd_type,
+ limit=self.limit)
+ else:
+ sql = render_template("/".join([self.sql_path, 'objectquery.sql']), object_name=self.object_name,
+ nsp_name=self.nsp_name, cmd_type=self.cmd_type,
+ sql_filter=sql_filter, limit=self.limit)
+
+ return sql
+
+ def can_edit(self):
+ return False
+
+ def can_filter(self):
+ return True
+
+
+class CatalogCommand(GridCommand):
+ """
+ class CatalogCommand(GridCommand)
+
+ It is a derived class for CatalogObject type.
+ """
+ object_type = 'catalog_object'
+
+ def __init__(self, **kwargs):
+ """
+ This method calls the __init__ method of the base class
+ to get the proper object name.
+
+ Args:
+ **kwargs : N number of parameters
+ """
+
+ # call base class init to fetch the table name
+ super(CatalogCommand, self).__init__(**kwargs)
+
+ def get_sql(self):
+ """
+ This method is used to create a proper SQL query
+ to fetch the data for the specified catalog object
+ """
+ sql_filter = self.get_filter()
+
+ if sql_filter is None:
+ sql = render_template("/".join([self.sql_path, 'objectquery.sql']), object_name=self.object_name,
+ nsp_name=self.nsp_name, cmd_type=self.cmd_type,
+ limit=self.limit)
+ else:
+ sql = render_template("/".join([self.sql_path, 'objectquery.sql']), object_name=self.object_name,
+ nsp_name=self.nsp_name, cmd_type=self.cmd_type,
+ sql_filter=sql_filter, limit=self.limit)
+
+ return sql
+
+ def can_edit(self):
+ return False
+
+ def can_filter(self):
+ return True
+
+
+class QueryToolCommand(BaseCommand):
+ """
+ class QueryToolCommand(BaseCommand)
+
+ It is a derived class for Query Tool.
+ """
+ object_type = 'query_tool'
+
+ def __init__(self, **kwargs):
+ # call base class init to fetch the table name
+ super(QueryToolCommand, self).__init__(**kwargs)
+
+ self.conn_id = None
+
+ def get_sql(self):
+ return None
+
+ def can_edit(self):
+ return False
+
+ def can_filter(self):
+ return False
+
+ def set_connection_id(self, conn_id):
+ self.conn_id = conn_id
diff --git a/web/pgadmin/tools/sqleditor/static/css/sqleditor.css b/web/pgadmin/tools/sqleditor/static/css/sqleditor.css
new file mode 100644
index 0000000..c728894
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/static/css/sqleditor.css
@@ -0,0 +1,202 @@
+.pg-sql-editor {
+ position: absolute;
+ left: 0;
+ right: 0;
+ top : 0;
+ bottom: 0;
+}
+
+.pg-sqleditor-btn-group {
+ background-color: #D2D2D2;
+ border: 2px solid #A9A9A9;
+ left: 0px;
+ right: 0px;
+ padding: 2px;
+}
+
+.pg-sqleditor-btn-group button {
+ padding: 5px;
+}
+
+.pg-sqleditor-btn-group button.dropdown-toggle {
+ padding-left: 5px !important;
+ padding-right: 5px !important;
+}
+
+.pg-sqleditor-btn-group .dropdown-menu > li > a {
+ padding: 2px 5px;
+}
+
+.pg-sqleditor-btn-group .dropdown-menu {
+ min-width: initial;
+}
+
+.pg-sqleditor-btn-group .backgrid-filter.form-search {
+ position: relative;
+ width: 248px;
+ height: 30px;
+ float: right;
+ margin-top: 2px;
+ margin-right: 10px;
+}
+
+.pg-sqleditor-busy-fetching {
+ position:absolute;
+ left: 0;
+ top: 41px;
+ bottom: 0;
+ right: 0;
+ margin:0;
+ padding: 0;
+ background: black;
+ opacity: 0.4;
+ z-index: 100;
+}
+
+.pg-sqleditor-busy-icon {
+ position:absolute;
+ left: 45%;
+ top: 40%;
+}
+
+.pg-sqleditor-busy-text {
+ position:absolute;
+ left: 42%;
+ top: 50%;
+ font-size: 20px;
+}
+
+.tab-container {
+ position: absolute;
+ top: 0px;
+ left: 0px;
+ right: 0px;
+ bottom:0px;
+ border-top: 2px solid #A9A9A9;
+}
+
+#data_panel {
+ height: 100%;
+ width: 100%;
+}
+
+#pg-sqleditor-panel .wcDocker {
+ top: 41px;
+ bottom: 45px;
+ height: auto;
+}
+
+#pg-sqleditor-panel .wcDocker.with-query-container {
+ top: 207px;
+}
+
+.sql-editor-grid-container {
+ padding-bottom: 35px;
+}
+
+#datagrid-paginator {
+ position: fixed;
+ bottom: 0px;
+ width: 100%;
+ background-color: white;
+}
+
+/*Move he original checkbox out of the way */
+#datagrid .select-row-cell .sqleditor-checkbox {
+ position: absolute;
+ left: -9999px;
+}
+/*Align the icon and the label.deletable text to same height using tabl-cell display*/
+/*If you change the font-size of the text, you may also want to do som padding or alignhment changes here*/
+#datagrid .sqleditor-checkbox ~ label.deletable > span {
+ display: table-cell;
+ vertical-align: middle;
+ padding-left: 5px;
+}
+/*The label.deletable will contain the icon and the text, will grab the focus*/
+#datagrid .select-row-cell .sqleditor-checkbox + label.deletable {
+ cursor: pointer;
+ display: table;
+}
+/*The icon container, set it to fixed size and font size, the padding is to align the border*/
+/*If you change the font-size of this icon, be sure to adjust the min-width as well*/
+#datagrid .select-row-cell .sqleditor-checkbox + label.deletable:before {
+ font-family: 'FontAwesome';
+ font-size: medium;
+ font-weight: normal;
+ display: inline-block;
+ box-sizing: border-box;
+ border: 1px solid transparent;
+ min-width: 28px;
+ padding: 2px 0 0 3px;
+}
+
+/* toggle font awesome icon*/
+#datagrid .select-row-cell .sqleditor-checkbox:checked + label:before {
+ content: "\f014";
+}
+
+#datagrid .select-row-cell .sqleditor-checkbox:not(:checked) + label:before {
+ content: "\f014";
+}
+
+/*Do something on focus, in this case show dashed border*/
+#datagrid .select-row-cell .sqleditor-checkbox:focus + label:before {
+ border: 1px dashed #777;
+}
+
+/*Do something on hover, in this case change the image color*/
+#datagrid .select-row-cell .sqleditor-checkbox:hover + label:before {
+ color: #67afe5;
+}
+
+.pgadmin-row-deleted td {
+ color: red !important;
+ text-decoration: line-through;
+}
+
+.query-container {
+ width: 99%;
+ border: 1px solid black;
+ box-shadow: 0.5px 0.5px 5px #000;
+ margin: 0.5%;
+ padding-bottom: 30px;
+}
+
+.query-container .CodeMirror-scroll {
+ min-height: 120px;
+ max-height: 120px;
+}
+
+.query-container .sql-textarea{
+ box-shadow: 0.1px 0.1px 3px #000;
+ margin-bottom: 5px;
+}
+
+#filter .btn-group {
+ margin-right: 2px;
+ float: right;
+}
+
+#filter .btn-group > button {
+ padding: 3px;
+ font-weight: 700;
+}
+
+#filter .btn-group .btn-primary {
+ margin: auto !important;
+}
+
+.has-select-all table thead tr th:nth-child(1),
+.has-select-all table tbody tr td:nth-child(1) {
+ width: 35px !important;
+ max-width: 35px !important;
+ min-width: 35px !important;
+}
+
+.sql-editor-message,
+.sql-editor-history
+{
+ white-space:pre-wrap;
+ font-family: monospace;
+}
\ No newline at end of file
diff --git a/web/pgadmin/tools/sqleditor/templates/sqleditor/js/sqleditor.js b/web/pgadmin/tools/sqleditor/templates/sqleditor/js/sqleditor.js
new file mode 100644
index 0000000..4c2ac91
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/js/sqleditor.js
@@ -0,0 +1,1725 @@
+define(
+ ['jquery', 'underscore', 'alertify', 'pgadmin', 'backbone', 'backgrid', 'codemirror', 'codemirror.sql',
+ 'backgrid.select.all', 'backbone.paginator', 'backgrid.paginator', 'backgrid.filter', 'bootstrap',
+ 'pgadmin.browser', 'wcdocker'],
+ function($, _, alertify, pgAdmin, Backbone, Backgrid, CodeMirror) {
+
+ // Some scripts do export their object in the window only.
+ // Generally the one, which do no have AMD support.
+ var wcDocker = window.wcDocker,
+ pgBrowser = pgAdmin.Browser;
+
+ /* Return back, this has been called more than once */
+ if (pgAdmin.SqlEditor)
+ return pgAdmin.SqlEditor;
+
+ /* Get the function definition from
+ * http://stackoverflow.com/questions/1349404/generate-a-string-of-5-random-characters-in-javascript/35302975#35302975
+ */
+ function epicRandomString(b){for(var a=(Math.random()*eval("1e"+~~(50*Math.random()+50))).toString(36).split(""),c=3;c<a.length;c++)c==~~(Math.random()*c)+1&&a[c].match(/[a-z]/)&&(a[c]=a[c].toUpperCase());a=a.join("");a=a.substr(~~(Math.random()*~~(a.length/3)),~~(Math.random()*(a.length-~~(a.length/3*2)+1))+~~(a.length/3*2));if(24>b)return b?a.substr(a,b):a;a=a.substr(a,b);if(a.length==b)return a;for(;a.length<b;)a+=epicRandomString();return a.substr(0,b)};
+
+ // Defining the backbone model for the sql grid
+ var sqlEditorViewModel = Backbone.Model.extend({
+
+ /* Keep track of values for the original primary keys for later reference,
+ * to allow to change the value of primary keys in the model, which will be
+ * required to identify the value of any row in the datagrid for the relation.
+ */
+ parse: function(data) {
+ var self = this;
+ self.grid_keys = {};
+ self.marked_for_deletion = false;
+ self.changed_data = false;
+
+ if (data && 'primary_keys' in self && self.primary_keys && _.size(self.primary_keys) > 0) {
+ _.each(self.primary_keys, function (value, key) {
+ // Assumption - the data, which are coming will always have data for primary_keys
+ self.grid_keys[key] = data[key];
+ });
+ }
+
+ return data;
+ },
+ /* We also need primary key along with the original data,
+ * which is required to identify this row in the database for modification.
+ */
+ toJSON: function(overridden, keys_only) {
+ var res = Backbone.Model.prototype.toJSON.apply(this, arguments);
+ if (!overridden) {
+ return res;
+ }
+
+ if (keys_only)
+ return this.grid_keys;
+
+ return {
+ 'keys': this.grid_keys,
+ 'data': res,
+ 'marked_for_deletion': this.marked_for_deletion
+ };
+ },
+
+ // This function updates the primary key if changed.
+ update_keys: function() {
+ var self = this;
+
+ /* If 'grid_keys' present in the changed object
+ * then it is an update else insert.
+ */
+ if ('grid_keys' in self) {
+ /* Iterate through primary keys and check if the key
+ * is updated or not. If it is updated we need to update
+ * the grid_keys of the model as well.
+ */
+ _.each(self.primary_keys, function (value, key) {
+ if (self.grid_keys[key] != self.attributes[key])
+ self.grid_keys[key] = self.attributes[key]
+ });
+ }
+ else {
+ self.grid_keys = {};
+ /* Iterate through primary keys and insert
+ * the values in models grid_keys.
+ */
+ _.each(self.primary_keys, function (value, key) {
+ self.grid_keys[key] = self.attributes[key]
+ });
+ }
+ }
+ });
+
+ // Suppose you want to highlight the entire row when an editable field is focused
+ var FocusableRow = Backgrid.Row.extend({
+ highlightColor: "#D9EDF7",
+ events: {
+ focusin: "rowFocused",
+ focusout: "rowLostFocus"
+ },
+ rowFocused: function() {
+ this.model.trigger('backgrid:row:selected', this);
+ this.el.style.backgroundColor = this.highlightColor;
+ },
+ rowLostFocus: function() {
+ this.model.trigger('backgrid:row:deselected', this);
+ }
+ });
+
+ /*
+ * Extend the FocusableRow row used when user marked
+ * the row for deletion.
+ */
+ var SqlEditorCustomRow = FocusableRow.extend({
+ initialize: function() {
+ Backgrid.Row.prototype.initialize.apply(this, arguments);
+ _.bindAll(this, 'render');
+
+ // Listen for the mark for deletion event and call render method.
+ this.model.on('backgrid:row:mark:deletion', this.render);
+ },
+ remove: function() {
+ this.model.off('backgrid:row:mark:deletion', this.render);
+ Backgrid.Row.prototype.remove.apply(this, arguments);
+ },
+ render: function() {
+ var res = Backgrid.Row.prototype.render.apply(this, arguments);
+
+ if (this.model.marked_for_deletion)
+ this.$el.addClass('pgadmin-row-deleted');
+ else
+ this.$el.removeClass('pgadmin-row-deleted');
+
+ return res;
+ }
+ });
+
+ // Defining Backbone view for the sql grid.
+ var SQLEditorView = Backbone.View.extend({
+ initialize: function(opts) {
+ this.$el = opts.el;
+ this.handler = opts.handler;
+ },
+
+ // Bind all the events
+ events: {
+ "click #btn-save": "on_save",
+ "click #btn-refresh": "on_refresh",
+ "click #btn-add-row": "on_add",
+ "click #btn-filter": "on_show_filter",
+ "click #btn-include-filter": "on_include_filter",
+ "click #btn-exclude-filter": "on_exclude_filter",
+ "click #btn-remove-filter": "on_remove_filter",
+ "click #btn-apply": "on_apply",
+ "click #btn-cancel": "on_cancel",
+ "click #btn-copy": "on_copy",
+ "click #btn-paste": "on_paste",
+ "click #btn-execute": "on_execute",
+ "click #btn-cancel-query": "on_cancel_query",
+ "click #btn-download": "on_download",
+ "click #btn-clear": "on_clear",
+ "change .limit": "on_limit_change"
+ },
+
+ /* Defining the template to create buttons and div to render
+ * the backgrid inside this div.
+ */
+ template: _.template([
+ '<div id="btn-toolbar" class="pg-sqleditor-btn-group" role="toolbar" aria-label="">',
+ '<div class="btn-group" role="group" aria-label="">',
+ '<button id="btn-save" type="button" class="btn btn-default" title="{{ _('Save') }}" disabled>',
+ '<span class="glyphicon glyphicon-floppy-saved" aria-hidden="true"></span>',
+ '</button>',
+ '</div>',
+ '<div class="btn-group" role="group" aria-label="">',
+ '<button id="btn-refresh" type="button" class="btn btn-default" title="{{ _('Refresh') }}">',
+ '<span class="glyphicon glyphicon-refresh" aria-hidden="true"></span>',
+ '</button>',
+ '</div>',
+ '<div class="btn-group" role="group" aria-label="">',
+ '<button id="btn-copy" type="button" class="btn btn-default" title="{{ _('Copy Row') }}" disabled>',
+ '<span class="glyphicon glyphicon-copy" aria-hidden="true"></span>',
+ '</button>',
+ '<button id="btn-paste" type="button" class="btn btn-default" title="{{ _('Paste Row') }}" disabled>',
+ '<span class="glyphicon glyphicon-paste" aria-hidden="true"></span>',
+ '</button>',
+ '</div>',
+ '<div class="btn-group" role="group" aria-label="">',
+ '<button id="btn-add-row" type="button" class="btn btn-default" title="{{ _('Add New Row') }}" disabled>',
+ '<span class="glyphicon glyphicon-plus" aria-hidden="true"></span>',
+ '</button>',
+ '</div>',
+ '<div class="btn-group" role="group" aria-label="">',
+ '<button id="btn-filter" type="button" class="btn btn-default" title="{{ _('Filter') }}" disabled>',
+ '<span class="glyphicon glyphicon-filter" aria-hidden="true"></span>',
+ '</button>',
+ '<button id="btn-filter-dropdown" type="button" class="btn btn-default dropdown-toggle" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false" disabled>',
+ '<span class="caret"></span> <span class="sr-only">Toggle Dropdown</span>',
+ '</button>',
+ '<ul class="dropdown-menu dropdown-menu-right">',
+ '<li>',
+ '<a id="btn-remove-filter" href="#" type="button">{{ _('Remove') }}</a>',
+ '<a id="btn-include-filter" href="#" type="button">{{ _('By selection') }}</a>',
+ '<a id="btn-exclude-filter" href="#" type="button">{{ _('Exclude selection') }}</a>',
+ '</li>',
+ '</ul>',
+ '</div>',
+ '<div class="btn-group" role="group" aria-label="">',
+ '<select class="limit" style="height: 30px; width: 90px;" disabled>',
+ '<option value="-1">No limit</option>',
+ '<option value="1000">1000 rows</option>',
+ '<option value="500">500 rows</option>',
+ '<option value="100">100 rows</option>',
+ '</select>',
+ '</div>',
+ '<div class="btn-group" role="group" aria-label="">',
+ '<button id="btn-download" type="button" class="btn btn-default" title="{{ _('Download as CSV') }}">',
+ '<span class="glyphicon glyphicon-download-alt" aria-hidden="true"></span>',
+ '</button>',
+ '</div>',
+ '<div class="btn-group" role="group" aria-label="">',
+ '<button id="btn-execute" type="button" class="btn btn-default" title="{{ _('Execute query') }}">',
+ '<span class="glyphicon glyphicon-play" aria-hidden="true"></span>',
+ '</button>',
+ '<button id="btn-execute-dropdown" type="button" class="btn btn-default dropdown-toggle" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">',
+ '<span class="caret"></span> <span class="sr-only">Toggle Dropdown</span>',
+ '</button>',
+ '<button id="btn-cancel-query" type="button" class="btn btn-default" title="{{ _('Cancel query') }}" disabled>',
+ '<span class="glyphicon glyphicon-stop" aria-hidden="true"></span>',
+ '</button>',
+ '</div>',
+ '<div class="btn-group" role="group" aria-label="">',
+ '<button id="btn-clear" type="button" class="btn btn-default" title="{{ _('Clear edit window') }}">',
+ '<span class="glyphicon glyphicon-erase" aria-hidden="true"></span>',
+ '</button>',
+ '</div>',
+ '</div>',
+ '<div id="filter" class="query-container hidden">',
+ '<div class="sql-textarea">',
+ '<textarea id="sql_filter" row="5"></textarea>',
+ '</div>',
+ '<div class="btn-group">',
+ '<button id="btn-apply" type="button" class="btn btn-primary" title="{{ _('Apply') }}">',
+ '<span class="glyphicon glyphicon-ok" aria-hidden="true"></span> {{ _('Apply') }}',
+ '</button>',
+ '<button id="btn-cancel" type="button" class="btn btn-danger" title="{{ _('Cancel') }}">',
+ '<span class="glyphicon glyphicon-remove" aria-hidden="true"></span> {{ _('Cancel') }}',
+ '</button>',
+ '</div>',
+ '</div>',
+ '<div id="query_tool" class="query-container hidden">',
+ '<div class="sql-textarea">',
+ '<textarea id="sql_query_tool" row="5"></textarea>',
+ '</div>',
+ '</div>',
+ '<div id="pg-sqleditor-panel"></div>'
+ ].join("\n")),
+
+ // This function is used to render the template.
+ render: function() {
+ var self = this;
+
+ // Render header only.
+ self.$el.empty();
+ self.$el.html(self.template());
+
+ var filter = self.$el.find('#sql_filter');
+
+ self.filter_obj = CodeMirror.fromTextArea(filter.get(0), {
+ lineNumbers: true,
+ lineWrapping: true,
+ matchBrackets: true,
+ indentUnit: 4,
+ mode: "text/x-sql"
+ });
+
+ var query_tool = self.$el.find('#sql_query_tool');
+
+ self.query_tool_obj = CodeMirror.fromTextArea(query_tool.get(0), {
+ lineNumbers: true,
+ lineWrapping: true,
+ matchBrackets: true,
+ indentUnit: 4,
+ mode: "text/x-sql"
+ });
+
+ // Create wcDocker for tab set.
+ var docker = self.docker = new wcDocker(
+ '#pg-sqleditor-panel', {
+ allowContextMenu: false,
+ allowCollapse: false,
+ themePath: '{{ url_for('static', filename='css/wcDocker/Themes') }}',
+ theme: 'pgadmin'
+ });
+
+ // Create panels for 'Data Output', 'Explain', 'Messages' and 'History'
+
+ var data_output = new pgAdmin.Browser.Panel({
+ name: 'data_output',
+ title: '{{ _('Data Output') }}',
+ width: '100%',
+ height:'100%',
+ isCloseable: false,
+ isPrivate: true,
+ content: '<div id ="datagrid" class="sql-editor-grid-container"></div><div id ="datagrid-paginator"></div>'
+ })
+
+ var explain = new pgAdmin.Browser.Panel({
+ name: 'explain',
+ title: '{{ _('Explain') }}',
+ width: '100%',
+ height:'100%',
+ isCloseable: false,
+ isPrivate: true,
+ content: '<div class="sql-editor-explian"></div>'
+ })
+
+ var messages = new pgAdmin.Browser.Panel({
+ name: 'messages',
+ title: '{{ _('Messages') }}',
+ width: '100%',
+ height:'100%',
+ isCloseable: false,
+ isPrivate: true,
+ content: '<div class="sql-editor-message"></div>'
+ })
+
+ var history = new pgAdmin.Browser.Panel({
+ name: 'history',
+ title: '{{ _('History') }}',
+ width: '100%',
+ height:'100%',
+ isCloseable: false,
+ isPrivate: true,
+ content: '<div class="sql-editor-history"></div>'
+ })
+
+ // Load all the created panels
+ data_output.load(self.docker);
+ explain.load(self.docker);
+ messages.load(self.docker);
+ history.load(self.docker);
+
+ // Add all the panels to the docker
+ self.data_output_panel = self.docker.addPanel('data_output', wcDocker.DOCK.LEFT);
+ self.explain_panel = self.docker.addPanel('explain', wcDocker.DOCK.STACKED, self.data_output_panel);
+ self.messages_panel = self.docker.addPanel('messages', wcDocker.DOCK.STACKED, self.data_output_panel);
+ self.history_panel = self.docker.addPanel('history', wcDocker.DOCK.STACKED, self.data_output_panel);
+ },
+
+ /* This function is responsible to create and render the
+ * new backgrid and paginator.
+ */
+ render_grid: function(collection, columns) {
+ var self = this;
+
+ // Remove any existing grid first
+ if (self.grid) {
+ self.grid.remove();
+ }
+
+ // Remove any existing paginator
+ if (self.paginator) {
+ self.paginator.remove();
+ }
+
+ // Remove any existing client side filter
+ if (self.clientSideFilter) {
+ self.clientSideFilter.remove();
+ }
+
+ // Create an array for client filter
+ var filter_array = new Array()
+ _.each(columns, function(c) {
+ filter_array.push(c.name);
+ });
+
+ var grid = self.grid = new Backgrid.Grid({
+ columns: columns,
+ collection: collection,
+ className: "backgrid table-bordered",
+ row: SqlEditorCustomRow
+ }),
+ paginator = self.paginator = new Backgrid.Extension.Paginator({
+ goBackFirstOnSort: false,
+ collection: collection
+ }),
+ clientSideFilter = self.clientSideFilter = new Backgrid.Extension.ClientSideFilter({
+ collection: collection,
+ placeholder: _('Search'),
+ // The model fields to search for matches
+ fields: filter_array,
+ // How long to wait after typing has stopped before searching can start
+ wait: 150
+ });
+
+ // Render the grid
+ self.$el.find('#datagrid').append(self.grid.render().$el);
+
+ // Render the paginator
+ self.$el.find('#datagrid-paginator').append(paginator.render().el);
+
+ // Render the client side filter
+ self.$el.find('.pg-sqleditor-btn-group').append(clientSideFilter.render().el);
+
+ // Forcefully sorting by the first column.
+ if (columns.length > 1) {
+ collection.setSorting(columns[1].name);
+ collection.fullCollection.sort();
+ }
+ },
+
+ // Callback function for Add New Row button click.
+ on_add: function() {
+ var self = this;
+
+ // Trigger the addrow signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:addrow',
+ self,
+ self.handler
+ );
+ },
+
+ // Callback function for Save button click.
+ on_save: function() {
+ var self = this;
+
+ // Trigger the save signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:save',
+ self,
+ self.handler
+ );
+ },
+
+ // Callback function for refresh button click.
+ on_refresh: function() {
+ var self = this;
+
+ // Trigger the refresh signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:refresh',
+ self,
+ self.handler
+ );
+ },
+
+ // Callback function for filter button click.
+ on_show_filter: function() {
+ var self = this;
+
+ // Trigger the show_filter signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:show_filter',
+ self,
+ self.handler
+ );
+ },
+
+ // Callback function for include filter button click.
+ on_include_filter: function() {
+ var self = this;
+
+ // Trigger the include_filter signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:include_filter',
+ self,
+ self.handler
+ );
+ },
+
+ // Callback function for exclude filter button click.
+ on_exclude_filter: function() {
+ var self = this;
+
+ // Trigger the exclude_filter signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:exclude_filter',
+ self,
+ self.handler
+ );
+ },
+
+ // Callback function for remove filter button click.
+ on_remove_filter: function() {
+ var self = this;
+
+ // Trigger the remove_filter signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:remove_filter',
+ self,
+ self.handler
+ );
+ },
+
+ // Callback function for ok button click.
+ on_apply: function() {
+ var self = this;
+
+ // Trigger the apply_filter signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:apply_filter',
+ self,
+ self.handler
+ );
+ },
+
+ // Callback function for cancel button click.
+ on_cancel: function() {
+ this.$el.find('#pg-sqleditor-panel > .wcDocker').first().removeClass('with-query-container');
+ $('#filter').addClass('hidden');
+ },
+
+ // Callback function for copy button click.
+ on_copy: function() {
+ var self = this;
+
+ // Trigger the copy signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:copy',
+ self,
+ self.handler
+ );
+ },
+
+ // Callback function for paste button click.
+ on_paste: function() {
+ var self = this;
+
+ // Trigger the paste signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:paste',
+ self,
+ self.handler
+ );
+ },
+
+ // Callback function for the change event of combo box
+ on_limit_change: function() {
+ var self = this;
+
+ // Trigger the limit signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:limit',
+ self,
+ self.handler
+ );
+ },
+
+ // Callback function for the execute button click.
+ on_execute: function() {
+ var self = this;
+
+ // Trigger the execute signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:execute',
+ self,
+ self.handler
+ );
+ },
+
+ // Callback function for the cancel query button click.
+ on_cancel_query: function() {
+ var self = this;
+
+ // Trigger the cancel-query signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:cancel-query',
+ self,
+ self.handler
+ );
+ },
+
+ // Callback function for the download button click.
+ on_download: function() {
+ var self = this;
+
+ // Trigger the download signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:download',
+ self,
+ self.handler
+ );
+ },
+
+ // Callback function for the clear button click.
+ on_clear: function() {
+ var self = this;
+ self.query_tool_obj.setValue('');
+ }
+ });
+
+ /* 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...
+ */
+ var SqlEditorController = function(container, options) {
+ this.initialize.apply(this, arguments);
+ };
+
+ _.extend(
+ SqlEditorController.prototype,
+ Backbone.Events,
+ {
+ initialize: function(container, opts) {
+ this.container = container;
+ },
+
+ /* This function is used to create instance of SQLEditorView,
+ * call the render method of the grid view to render the backgrid
+ * header and loading icon and start execution of the sql query.
+ */
+ start: function(is_query_tool, items_per_page) {
+ var self = this;
+
+ self.is_query_tool = is_query_tool;
+ self.items_per_page = items_per_page;
+
+ // We do not allow to call the start multiple times.
+ if (self.gridView)
+ return;
+
+ self.gridView = new SQLEditorView({
+ el: self.container,
+ handler: self
+ });
+ self.transId = self.container.data('transId');
+
+ // Render the header
+ self.gridView.render();
+
+ // Listen on events come from SQLEditorView for the button clicked.
+ self.on('pgadmin-sqleditor:button:save', self._save, self);
+ self.on('pgadmin-sqleditor:button:refresh', self._refresh, self);
+ self.on('pgadmin-sqleditor:button:addrow', self._add, self);
+ self.on('pgadmin-sqleditor:button:show_filter', self._show_filter, self);
+ self.on('pgadmin-sqleditor:button:include_filter', self._include_filter, self);
+ self.on('pgadmin-sqleditor:button:exclude_filter', self._exclude_filter, self);
+ self.on('pgadmin-sqleditor:button:remove_filter', self._remove_filter, self);
+ self.on('pgadmin-sqleditor:button:apply_filter', self._apply_filter, self);
+ self.on('pgadmin-sqleditor:button:copy', self._copy, self);
+ self.on('pgadmin-sqleditor:button:paste', self._paste, self);
+ self.on('pgadmin-sqleditor:button:limit', self._set_limit, self);
+ self.on('pgadmin-sqleditor:button:execute', self._execute, self);
+ self.on('pgadmin-sqleditor:button:cancel-query', self._cancel_query, self);
+ self.on('pgadmin-sqleditor:button:download', self._download, self);
+
+ if (self.is_query_tool) {
+ $('#query_tool').removeClass('hidden');
+ self.gridView.query_tool_obj.refresh();
+ $docker = $(this.container).find('#pg-sqleditor-panel > .wcDocker').first();
+ $docker.addClass('with-query-container');
+ $("#btn-refresh").prop('disabled', true);
+ }
+ else {
+ self.disable_tool_buttons(true);
+ self._execute_data_query();
+ }
+ },
+
+ // This function makes the ajax call to execute the sql query.
+ _execute_data_query: function() {
+ var self = this;
+ self.query_start_time = new Date();
+
+ self.trigger(
+ 'pgadmin-sqleditor:loading-icon:show',
+ '{{ _('Initializing the query execution!') }}'
+ );
+
+ $.ajax({
+ url: "{{ url_for('sqleditor.index') }}" + "view_data/start/" + self.transId,
+ method: 'GET',
+ success: function(res) {
+ if (res.data.status) {
+ self.trigger(
+ 'pgadmin-sqleditor:loading-icon:message',
+ '{{ _('Waiting for the query execution to complete...') }}'
+ );
+
+ self.can_edit = res.data.can_edit;
+ self.can_filter = res.data.can_filter;
+
+ /* If filter is applied then remove class 'btn-default'
+ * and add 'btn-warning' to change the colour of the button.
+ */
+ if (self.can_filter && res.data.filter_applied) {
+ $('#btn-filter').removeClass('btn-default');
+ $('#btn-filter-dropdown').removeClass('btn-default');
+ $('#btn-filter').addClass('btn-warning');
+ $('#btn-filter-dropdown').addClass('btn-warning');
+ }
+ else {
+ $('#btn-filter').removeClass('btn-warning');
+ $('#btn-filter-dropdown').removeClass('btn-warning');
+ $('#btn-filter').addClass('btn-default');
+ $('#btn-filter-dropdown').addClass('btn-default');
+ }
+
+ // Set the combo box value
+ $(".limit").val(res.data.limit);
+
+ // If status is True then poll the result.
+ self._poll();
+ }
+ else {
+ self.raise_message('Query Execution Error', res.data.result);
+ }
+ },
+ error: function(e) {
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ self.raise_message('Query Execution Error', msg);
+ }
+ });
+ },
+
+ /* This function makes the ajax call to poll the result,
+ * if status is Busy then recursively call the poll function
+ * till the status is 'Success' or 'NotConnected'. If status is
+ * 'Success' then call the render method to render the data.
+ */
+ _poll: function() {
+ var self = this;
+ setTimeout(
+ function() {
+ $.ajax({
+ url: "{{ url_for('sqleditor.index') }}" + "poll/" + self.transId,
+ method: 'GET',
+ success: function(res) {
+ if (res.data.status === 'Success') {
+ self.trigger(
+ 'pgadmin-sqleditor:loading-icon:message',
+ '{{ _('Loading the data from the database server, and rendering into the grid...') }}'
+ );
+
+ self.query_end_time = new Date();
+
+ /* If no column information is available it means query
+ runs successfully with no result to display. In this
+ case no need to call render function.
+ */
+ if (res.data.colinfo != null)
+ self._render(res.data);
+ else {
+ // Show message in message and history tab in case of query tool
+ var total_query_run_time = self.get_query_run_time(self.query_start_time, self.query_end_time);
+ var message = res.data.result + ': ' + total_query_run_time + ' execution time.';
+ self.raise_message('Query Execution Success', message);
+ }
+
+ // Enable/Disable query tool button only if is_query_tool is true.
+ if (self.is_query_tool) {
+ self.disable_tool_buttons(false);
+ $("#btn-cancel-query").prop('disabled', true);
+ }
+ }
+ else if (res.data.status === 'Busy') {
+ // If status is Busy then poll the result by recursive call to the poll function
+ self._poll();
+ }
+ else if (res.data.status === 'NotConnected') {
+
+ // Enable/Disable query tool button only if is_query_tool is true.
+ if (self.is_query_tool) {
+ self.disable_tool_buttons(false);
+ $("#btn-cancel-query").prop('disabled', true);
+ }
+ self.raise_message('Poll Result Error', res.data.result);
+ }
+ else if (res.data.status === 'Cancel') {
+ self.raise_message('Poll Result Error', "Execution Cancelled!")
+ }
+ },
+ error: function(e) {
+ // Enable/Disable query tool button only if is_query_tool is true.
+ if (self.is_query_tool) {
+ self.disable_tool_buttons(false);
+ $("#btn-cancel-query").prop('disabled', true);
+ }
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ self.raise_message('Poll Result Error', msg);
+ }
+ });
+ }, 1);
+ },
+
+ /* This function is used to create the backgrid columns,
+ * create the Backbone PageableCollection and finally render
+ * the data in the backgrid.
+ */
+ _render: function(data) {
+ var self = this;
+ self.colinfo = data.col_info;
+ self.primary_keys = data.primary_keys;
+ self.cell_selected = false;
+ self.selected_model = null;
+ self.changedModels = [];
+
+ // Stop listening to all the events
+ if (self.collection) {
+ self.collection.off('change', self.on_model_change, self);
+ self.collection.off('backgrid:selected', self.on_backgrid_selected, self);
+ self.collection.off('backgrid:editing', self.on_cell_editing, self);
+ }
+
+ /* If object don't have primary keys then set the
+ * can_edit flag to false.
+ */
+ if (self.primary_keys === null || self.primary_keys === undefined
+ || _.size(self.primary_keys) === 0)
+ self.can_edit = false;
+ else
+ self.can_edit = true;
+
+ /* If user can edit the data then we should enabled
+ * Copy Row, Paste Row and 'Add New Row' buttons.
+ */
+ if (self.can_edit) {
+ $("#btn-add-row").prop('disabled', false);
+ $("#btn-copy").prop('disabled', false);
+ $("#btn-paste").prop('disabled', false);
+ }
+
+ /* If user can filter the data then we should enabled
+ * Filter and Limit buttons.
+ */
+ if (self.can_filter) {
+ $(".limit").prop('disabled', false);
+ $("#btn-filter").prop('disabled', false);
+ $("#btn-filter-dropdown").prop('disabled', false);
+ }
+
+ // Fetch the columns metadata
+ self.columns = self._fetch_column_metadata(data);
+
+ self.trigger(
+ 'pgadmin-sqleditor:loading-icon:message',
+ '{{ _('Loading the data from the database server, and rendering into the grid...') }}',
+ self
+ );
+
+ // Defining backbone's pageable collection.
+ self.collection = new (Backbone.PageableCollection.extend({
+ mode: "client",
+ state: {
+ pageSize: self.items_per_page,
+ order: -1
+ },
+ model: sqlEditorViewModel.extend({
+ primary_keys: self.primary_keys,
+
+ /* Change the idAttribute to random string
+ * so that it won't clash with id column of
+ * the table.
+ */
+ idAttribute: epicRandomString(10)
+ })
+ }));
+
+ // Listen on backgrid events
+ self.collection.on('change', self.on_model_change, self);
+ self.collection.on('backgrid:selected', self.on_backgrid_selected, self);
+ self.collection.on('backgrid:editing', self.on_cell_editing, self);
+ self.collection.on('backgrid:row:selected', self.on_row_selected, self);
+ self.collection.on('backgrid:row:deselected', self.on_row_deselected, self);
+
+ // Show message in message and history tab in case of query tool
+ var total_query_run_time = self.get_query_run_time(self.query_start_time, self.query_end_time);
+
+ if (self.is_query_tool) {
+ var rows = 0;
+ if (data.result && data.result.length > 0)
+ rows = data.result.length;
+
+ var message = 'Total query runtime: ' + total_query_run_time + '\n' + rows + ' rows retrieved.';
+ $('.sql-editor-message').text(message);
+ $('.sql-editor-history').append(message);
+ }
+
+ // Add the data to the collection and render the grid.
+ self.collection.add(data.result, {parse: true});
+ self.gridView.render_grid(self.collection, self.columns);
+ self.gridView.data_output_panel.focus();
+
+ // Hide the loading icon
+ self.trigger('pgadmin-sqleditor:loading-icon:hide');
+ },
+
+ // This function creates the columns as required by the backgrid
+ _fetch_column_metadata: function(data) {
+ var colinfo = data.colinfo,
+ primary_keys = data.primary_keys,
+ result = data.result,
+ self = this,
+ columns = [{
+ // name is a required parameter, but you don't really want one on a select all column
+ name: "",
+
+ /* Extend Backgrid.Extension.SelectRowCell to create
+ * a custom checkbox where we change the icon when user
+ * click on checkbox.
+ */
+ cell: Backgrid.Extension.SelectRowCell.extend({
+ render: function() {
+ var self = this,
+ id = _.uniqueId('sql_grid_deletable_');
+ Backgrid.Extension.SelectRowCell.prototype.render.apply(this, arguments);
+
+ // Find the input control and add sqleditor-checkbox class
+ this.$el.find('input').attr('id', id).addClass(
+ 'sqleditor-checkbox'
+ ).prop('checked', this.model.marked_for_deletion);
+
+ // Append the label and add class to change the icon and color using css
+ this.$el.append(
+ $('<label>', {
+ for: id, class: 'deletable'
+ }).append($('<span>')));
+
+ this.delegateEvents();
+ return this;
+ }
+ }),
+
+ // Backgrid.Extension.SelectAllHeaderCell lets you select all the row on a page
+ headerCell: "select-all"
+ }];
+
+ /* If user will not be able to edit data
+ * then no need to show 'select-all' column.
+ */
+ if (!self.can_edit) {
+ columns = [];
+ $('#datagrid').removeClass('has-select-all');
+ }
+ else {
+ if ($('#datagrid').hasClass('has-select-all') === false)
+ $('#datagrid').addClass('has-select-all');
+ }
+
+ self.trigger(
+ 'pgadmin-sqleditor:loading-icon:message',
+ '{{ _('Fetching the information about the columns returned...') }}'
+ );
+
+ // Make ajax call to fetch the pg types to map numeric data type
+ $.ajax({
+ url: "{{ url_for('sqleditor.index') }}" + "fetch/types/" + self.transId,
+ method: 'GET',
+ async: false,
+ success: function(res) {
+ if (res.data.status) {
+ // Store pg_types in an array
+ var pg_types = new Array();
+ _.each(res.data.result.rows, function(r) {
+ pg_types[r.oid] = [r.typname];
+ });
+
+ // Create columns required by backgrid to render
+ _.each(colinfo, function(c) {
+ var is_primary_key = false;
+
+ // Check whether table have primary key
+ if (_.size(primary_keys) > 0) {
+ _.each(primary_keys, function (value, key) {
+ if (key === c.name)
+ is_primary_key = true;
+ });
+ }
+
+ // Create column label.
+ var type = pg_types[c.type_code][0];
+ var col_label = c.name;
+ if (!is_primary_key)
+ col_label += ' ' + type;
+ else
+ col_label += ' [PK] ' + type;
+
+ if (c.precision == null) {
+ if (c.internal_size > 0)
+ col_label += '(' + c.internal_size + ')';
+ }
+ else
+ col_label += '(' + c.precision + ',' + c.scale + ')';
+
+ // Identify cell type of column.
+ switch(type) {
+ case "integer":
+ col_cell = 'integer';
+ break;
+ case "boolean":
+ col_cell = 'boolean';
+ break;
+ case "numeric":
+ col_cell = 'number';
+ break;
+ case "timestamp without time zone":
+ case "timestamp with time zone":
+ col_cell = 'datetime';
+ break;
+ default:
+ col_cell = 'string';
+ }
+
+ var col = {
+ name : c.name,
+ label: col_label,
+ cell: col_cell,
+ can_edit: self.can_edit,
+ editable: self.is_editable
+ };
+
+ columns.push(col);
+ });
+ }
+ else {
+ self.raise_message('Fetching Type Error', res.data.result);
+ }
+ }
+ });
+
+ return columns;
+ },
+
+ /* This function is used to raise appropriate message.
+ * If it is query tool then show it in message and history tab
+ * else show it as alertify dialog.
+ */
+ raise_message: function(title , errmsg) {
+ var self = this;
+
+ self.trigger('pgadmin-sqleditor:loading-icon:hide');
+ if (self.is_query_tool) {
+ $('.sql-editor-message').text(errmsg);
+ $('.sql-editor-history').append(errmsg);
+ self.gridView.messages_panel.focus();
+
+ // Delete grid and paginator
+ if (self.gridView.grid) {
+ self.gridView.grid.remove();
+ self.columns = undefined;
+ self.collection = undefined;
+ }
+ if (self.gridView.paginator)
+ self.gridView.paginator.remove();
+ }
+ else
+ alertify.alert( title, errmsg);
+ },
+
+ // This function will return the total query execution Time.
+ get_query_run_time: function (start_time, end_time) {
+ var self = this;
+
+ // Calculate the difference in milliseconds
+ var difference_ms = miliseconds = end_time.getTime() - start_time.getTime();
+ //take out milliseconds
+ difference_ms = difference_ms/1000;
+ var seconds = Math.floor(difference_ms % 60);
+ difference_ms = difference_ms/60;
+ var minutes = Math.floor(difference_ms % 60);
+
+ if (minutes > 0)
+ return minutes + ' min';
+ else if (seconds > 0) {
+ return seconds + ' secs';
+ }
+ else
+ return miliseconds + ' msec';
+ },
+
+ /* This function is used to check whether cell
+ * is editable or not depending on primary keys
+ * and marked_for_deletion flag
+ */
+ is_editable: function(obj) {
+ var self = this;
+ if (obj instanceof Backbone.Collection)
+ return false;
+ return (self.get('can_edit') && !obj.marked_for_deletion);
+ },
+
+ /* This is a callback function when there is any change
+ * in the model and stores the unique id (cid) to the changedModels
+ * array which will be used when Save button is clicked.
+ */
+ on_model_change: function(model) {
+ $("#btn-save").prop('disabled', false);
+ var self = this;
+ model.changed_data = true;
+ if (_.indexOf(self.changedModels, model.cid) == -1) {
+ self.changedModels.push(model.cid);
+ }
+ return;
+ },
+
+ // This is a callback function when backgrid is selected
+ on_backgrid_selected: function (model, selected) {
+ var self = this,
+ item_idx = _.indexOf(self.changedModels, model.cid);
+
+ // If data can't be edited then no need to marked for deletion.
+ if (!self.can_edit)
+ return;
+
+ if (selected) {
+ /* Check whether it is a new row, not inserted
+ * yet to the database. If yes then remove the row
+ * immediately. If not then marked the row for deletion.
+ */
+ if ('grid_keys' in model) {
+ model.marked_for_deletion = true;
+
+ // Push the model if not already exist and if exists then update the flag
+ if (item_idx == -1)
+ self.changedModels.push(model.cid);
+
+ }
+ else {
+ // Pop the model from changed model list if already exist
+ if (item_idx != -1)
+ self.changedModels.splice(item_idx, 1);
+
+ self.collection.remove(model);
+ }
+ }
+ else {
+ if (item_idx != -1) {
+ model.marked_for_deletion = false;
+ /* In case of deselected we check whether data is updated
+ * for this model, if not updated then delete it from
+ * changed list.
+ */
+ if (!model.changed_data)
+ self.changedModels.splice(item_idx, 1);
+ }
+ }
+
+ // Trigger the mark for deletion event where SqlEditorCustomRow is listening
+ model.trigger('backgrid:row:mark:deletion', model);
+
+ // Enable/Disable Save button
+ if (self.changedModels.length > 0)
+ $("#btn-save").prop('disabled', false);
+ else
+ $("#btn-save").prop('disabled', true);
+ },
+
+ /* This is a callback function when backgrid cell
+ * is selected we need this event for filter by selection
+ * or filter exclude selection.
+ */
+ on_cell_editing: function (model, column) {
+ var self = this;
+
+ self.cell_selected = true;
+ self.column_name = column.attributes.name;
+ self.column_value = model.attributes[self.column_name]
+ },
+
+ /* This is a callback function when backgrid row
+ * is selected. This function will change the color
+ * of the backgrid row and saved the selected model.
+ */
+ on_row_selected: function(row) {
+ var self = this;
+ if (self.selected_row && self.selected_row.cid != row.cid) {
+ self.selected_row.el.style.backgroundColor = '';
+ }
+ self.selected_row = row;
+ self.selected_model = row.model;
+ },
+
+ /* This is a callback function when backgrid row
+ * is deselected. This function will clear the background
+ * color and reset the selected model.
+ */
+ on_row_deselected: function(row) {
+ var self = this;
+ setTimeout(
+ function() {
+ if (self.selected_row.cid != row.cid) {
+ row.el.style.backgroundColor = '';
+ self.cell_selected = false;
+ self.selected_model = null;
+ }
+ }, 200
+ );
+ },
+
+ // This function will add a new row to the backgrid.
+ _add: function() {
+ var self = this,
+ empty_model = new (self.collection.model);
+ self.collection.add(empty_model);
+ },
+
+ /* This function will fetch the list of changed models and make
+ * the ajax call to save the data into the database server.
+ */
+ _save: function() {
+ var self = this,
+ data = [],
+ save_data = true;
+
+ $("#btn-save").prop('disabled', true);
+ if (self.changedModels.length == 0)
+ return;
+
+ for (var i = 0; i < self.changedModels.length; i++) {
+ if (self.collection) {
+ var model = self.collection.get(self.changedModels[i]);
+ /* Iterate through primary keys and check the key
+ * is not null. If it is null then raise an error
+ * and return from the function.
+ */
+ _.each(self.primary_keys, function (value, key) {
+ if (model.attributes[key] === null ||
+ model.attributes[key] === undefined) {
+ self.raise_message(
+ 'Save Error',
+ '{{ _('Primary key columns can not be null.') }}'
+ );
+ save_data = false;
+
+ return;
+ }
+ });
+
+ if (save_data)
+ data.push(model.toJSON(true, false));
+ }
+ }
+
+ if (save_data) {
+
+ // Make ajax call to save the data
+ $.ajax({
+ url: "{{ url_for('sqleditor.index') }}" + "save/" + self.transId,
+ method: 'POST',
+ async: false,
+ contentType: "application/json",
+ data: JSON.stringify(data),
+ success: function(res) {
+ if (res.data.status) {
+ // Update the primary keys if changed in the model
+ for (var i = 0; i < self.changedModels.length; i++) {
+ if (self.collection) {
+ var model = self.collection.get(self.changedModels[i]);
+
+ /* if model is marked for deletion the destroy
+ * the model else update the primary keys if changed.
+ */
+ if (model.marked_for_deletion)
+ model.destroy();
+ else
+ model.update_keys();
+ }
+ }
+
+ self.changedModels.length = 0;
+ }
+ else {
+ self.raise_message('Save Error', res.data.result);
+ }
+ },
+ error: function(e) {
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ self.raise_message('Save Error', msg);
+ }
+ });
+ }
+ },
+
+ // This function will run the SQL query and refresh the data in the backgrid.
+ _refresh: function() {
+ var self = this;
+
+ // Start execution of the query.
+ self._execute_data_query();
+ },
+
+ // This function will show the filter in the text area.
+ _show_filter: function() {
+ var self = this;
+ $docker = $(this.container).find('#pg-sqleditor-panel > .wcDocker').first();
+
+ $docker.addClass('with-query-container');
+
+ $.ajax({
+ url: "{{ url_for('sqleditor.index') }}" + "filter/get/" + self.transId,
+ method: 'GET',
+ success: function(res) {
+ if (res.data.status) {
+ $('#filter').removeClass('hidden');
+ self.gridView.filter_obj.refresh();
+
+ if (res.data.result == null)
+ self.gridView.filter_obj.setValue('');
+ else
+ self.gridView.filter_obj.setValue(res.data.result);
+ }
+ else {
+ self.raise_message('Get Filter Error', res.data.result);
+ }
+ },
+ error: function(e) {
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ self.raise_message('Get Filter Error', msg);
+ }
+ });
+ },
+
+ // This function will include the filter by selection.
+ _include_filter: function () {
+ var self = this,
+ data = {};
+
+ // If no cell is selected then return from the function
+ if (self.cell_selected === false)
+ return;
+
+ // Add column name and their value to data
+ data[self.column_name] = self.column_value;
+
+ // Make ajax call to include the filter by selection
+ $.ajax({
+ url: "{{ url_for('sqleditor.index') }}" + "filter/inclusive/" + self.transId,
+ method: 'POST',
+ async: false,
+ contentType: "application/json",
+ data: JSON.stringify(data),
+ success: function(res) {
+ if (res.data.status) {
+ // Refresh the sql grid
+ self._refresh();
+ }
+ else {
+ self.raise_message('Filter By Selection Error', res.data.result);
+ }
+ },
+ error: function(e) {
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ self.raise_message('Filter By Selection Error', msg);
+ }
+ });
+ },
+
+ // This function will exclude the filter by selection.
+ _exclude_filter: function () {
+ var self = this,
+ data = {};
+
+ // If no cell is selected then return from the function
+ if (self.cell_selected === false)
+ return;
+
+ // Add column name and their value to data
+ data[self.column_name] = self.column_value;
+
+ // Make ajax call to exclude the filter by selection.
+ $.ajax({
+ url: "{{ url_for('sqleditor.index') }}" + "filter/exclusive/" + self.transId,
+ method: 'POST',
+ async: false,
+ contentType: "application/json",
+ data: JSON.stringify(data),
+ success: function(res) {
+ if (res.data.status) {
+ // Refresh the sql grid
+ self._refresh();
+ }
+ else {
+ self.raise_message('Filter Exclude Selection Error', res.data.result);
+ }
+ },
+ error: function(e) {
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ self.raise_message('Filter Exclude Selection Error', msg);
+ }
+ });
+ },
+
+ // This function will remove the filter.
+ _remove_filter: function () {
+ var self = this;
+
+ // Make ajax call to exclude the filter by selection.
+ $.ajax({
+ url: "{{ url_for('sqleditor.index') }}" + "filter/remove/" + self.transId,
+ method: 'POST',
+ async: false,
+ success: function(res) {
+ if (res.data.status) {
+ // Refresh the sql grid
+ self._refresh();
+ }
+ else {
+ self.raise_message('Remove Filter Error', res.data.result);
+ }
+ },
+ error: function(e) {
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ self.raise_message('Remove Filter Error', msg);
+ }
+ });
+ },
+
+ // This function will apply the filter.
+ _apply_filter: function() {
+ var self = this;
+ sql = self.gridView.filter_obj.getValue(),
+ $docker = $(this.container).find('#pg-sqleditor-panel > .wcDocker').first();
+
+ // Make ajax call to include the filter by selection
+ $.ajax({
+ url: "{{ url_for('sqleditor.index') }}" + "filter/apply/" + self.transId,
+ method: 'POST',
+ async: false,
+ contentType: "application/json",
+ data: JSON.stringify(sql),
+ success: function(res) {
+ if (res.data.status) {
+ $('#filter').addClass('hidden');
+ $docker.removeClass('with-query-container');
+ // Refresh the sql grid
+ self._refresh();
+ }
+ else {
+ self.raise_message('Apply Filter Error',res.data.result);
+ }
+ },
+ error: function(e) {
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ self.raise_message('Apply Filter Error', msg);
+ }
+ });
+ },
+
+ // This function will copy the selected row.
+ _copy: function() {
+ var self = this;
+
+ // Save the selected model as copied model for future use
+ if ('selected_model' in self)
+ self.copied_model = self.selected_model;
+ },
+
+ // This function will paste the selected row.
+ _paste: function() {
+ var self = this;
+ new_model = null;
+ if ('copied_model' in self && self.copied_model != null) {
+ $("#btn-save").prop('disabled', false);
+
+ /* Find the model to be copied in the collection
+ * if found then we need to clone the object, so
+ * that it's cid/id gets changed.
+ */
+ if (self.collection.get(self.copied_model.cid) === undefined)
+ new_model = self.copied_model;
+ else
+ new_model = self.copied_model.clone();
+
+ /* Add the model to the array of changedModels which
+ * will be used when save button is clicked.
+ */
+ if (_.indexOf(self.changedModels, new_model.cid) == -1) {
+ self.changedModels.push(new_model.cid);
+ }
+
+ // Add the copied model to collection
+ self.collection.add(new_model);
+ }
+ },
+
+ // This function will set the limit for SQL query
+ _set_limit: function() {
+ var self = this,
+ limit = parseInt($(".limit").val());
+
+ // Make ajax call to change the limit
+ $.ajax({
+ url: "{{ url_for('sqleditor.index') }}" + "limit/" + self.transId,
+ method: 'POST',
+ async: false,
+ contentType: "application/json",
+ data: JSON.stringify(limit),
+ success: function(res) {
+ if (!res.data.status) {
+ self.raise_message('Change limit Error', res.data.result);
+ }
+ },
+ error: function(e) {
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ self.raise_message('Change limit Error', msg);
+ }
+ });
+ },
+
+ // This function is used to enable/disable buttons
+ disable_tool_buttons: function(disabled) {
+ $("#btn-execute").prop('disabled', disabled);
+ $("#btn-execute-dropdown").prop('disabled', disabled);
+ $("#btn-clear").prop('disabled', disabled);
+ },
+
+ // This function will fetch the sql query from the text box
+ // and execute the query.
+ _execute: function () {
+ var self = this,
+ sql = '',
+ history_msg = '';
+
+ self.trigger(
+ 'pgadmin-sqleditor:loading-icon:show',
+ '{{ _('Initializing the query execution!') }}'
+ );
+
+ /* If code is selected in the code mirror then execute
+ * the selected part else execute the complete code.
+ */
+ var selected_code = self.gridView.query_tool_obj.getSelection();
+ if (selected_code.length > 0)
+ sql = selected_code;
+ else
+ sql = self.gridView.query_tool_obj.getValue();
+
+ // Update history tab
+ if ($('.sql-editor-history').text().length > 0)
+ history_msg = '\n\n-- Executing query:\n' + sql + '\n';
+ else
+ history_msg = '-- Executing query:\n' + sql + '\n';
+
+ $('.sql-editor-history').append(history_msg);
+
+ self.query_start_time = new Date();
+
+ self.disable_tool_buttons(true);
+ $("#btn-cancel-query").prop('disabled', false);
+
+ $.ajax({
+ url: "{{ url_for('sqleditor.index') }}" + "query_tool/start/" + self.transId,
+ method: 'POST',
+ async: false,
+ contentType: "application/json",
+ data: JSON.stringify(sql),
+ success: function(res) {
+ if (res.data.status) {
+ self.trigger(
+ 'pgadmin-sqleditor:loading-icon:message',
+ '{{ _('Waiting for the query execution to complete...') }}'
+ );
+
+ self.can_edit = res.data.can_edit;
+ self.can_filter = res.data.can_filter;
+
+ // If status is True then poll the result.
+ self._poll();
+ }
+ else {
+ self.disable_tool_buttons(false);
+ $("#btn-cancel-query").prop('disabled', true);
+ self.raise_message('Query Execution Error', res.data.result);
+ }
+ },
+ error: function(e) {
+ self.disable_tool_buttons(false);
+ $("#btn-cancel-query").prop('disabled', true);
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ self.raise_message('Query Execution Error', msg);
+ }
+ });
+ },
+
+ // This function will cancel the running query.
+ _cancel_query: function() {
+ var self = this;
+
+ $("#btn-cancel-query").prop('disabled', true);
+ $.ajax({
+ url: "{{ url_for('sqleditor.index') }}" + "cancel/" + self.transId,
+ method: 'POST',
+ async: false,
+ contentType: "application/json",
+ success: function(res) {
+ if (res.data.status) {
+ self.disable_tool_buttons(false);
+ }
+ else {
+ self.disable_tool_buttons(false);
+ self.raise_message('Cancel Query Error', res.data.result);
+ }
+ },
+ error: function(e) {
+ self.disable_tool_buttons(false);
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ self.raise_message('Cancel Query Error', msg);
+ }
+ });
+ },
+
+ // This function will download the grid data as CSV file.
+ _download: function() {
+ var self = this;
+
+ if (self.columns != undefined &&
+ self.collection != undefined &&
+ self.collection.fullCollection != undefined &&
+ self.collection.fullCollection.length > 0)
+ {
+ var csv_col = _.indexBy(self.columns, 'name'),
+ labels = _.pluck(self.columns, 'label'),
+ keys = _.pluck(self.columns, 'name');
+
+ // Fetch the items from fullCollection and convert it as csv format
+ var csv = labels.join(',') + '\n';
+ csv += self.collection.fullCollection.map(function(item) {
+ return _.map(keys, function(key) {
+ var cell = csv_col [key].cell,
+ // suppose you want to preserve custom formatters
+ formatter = cell.prototype && cell.prototype.formatter;
+
+ return formatter && formatter.fromRaw ?
+ formatter.fromRaw(item.get(key), item) : item.get(key);
+ }).join(',');
+ }).join('\n');
+
+ // Download the file.
+ var encodedUri = encodeURI('data:text/csv;charset=utf-8,' + csv),
+ link = document.createElement('a');
+ link.setAttribute('href', encodedUri);
+
+ /* If download is from view data then file name should be
+ * the object name for which data is to be displayed.
+ */
+ if (!self.is_query_tool) {
+ $.ajax({
+ url: "{{ url_for('sqleditor.index') }}" + "object/get/" + self.transId,
+ method: 'GET',
+ success: function(res) {
+ if (res.data.status) {
+ filename = res.data.result + '.csv';
+ link.setAttribute('download', filename);
+ link.click();
+ }
+ },
+ error: function(e) {
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ self.raise_message('Get Object Name Error', msg);
+ }
+ });
+ }
+ else {
+ var cur_time = new Date();
+ var filename = 'data-' + cur_time.getTime() + '.csv';
+ link.setAttribute('download', filename);
+ link.click();
+ }
+ }
+ else {
+ alertify.alert('Download Data', 'No data is available to download');
+ }
+ }
+ }
+ );
+
+ pgAdmin.SqlEditor = {
+ // This function is used to create and return the object of grid controller.
+ create: function(container) {
+ return new SqlEditorController(container);
+ }
+ };
+
+ return pgAdmin.SqlEditor;
+ });
diff --git a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/create.sql b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/create.sql
new file mode 100644
index 0000000..f08ee7a
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/create.sql
@@ -0,0 +1,8 @@
+{# Insert the new row with primary keys (specified in primary_keys) #}
+ INSERT INTO {{ conn|qtIdent(nsp_name, object_name) }} (
+{% for col in data_to_be_saved %}
+{% if not loop.first %}, {% endif %}{{ conn|qtIdent(col) }}{% endfor %}
+ ) VALUES (
+{% for col in data_to_be_saved %}
+{% if not loop.first %}, {% endif %}{{ data_to_be_saved[col]|qtLiteral }}{% endfor %}
+);
\ No newline at end of file
diff --git a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/delete.sql b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/delete.sql
new file mode 100644
index 0000000..53c1c63
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/delete.sql
@@ -0,0 +1,4 @@
+{# Delete the row with primary keys (specified in primary_keys) #}
+ DELETE FROM {{ conn|qtIdent(nsp_name, object_name) }} WHERE
+{% for pk in primary_keys %}
+{% if not loop.first %} AND {% endif %}{{ conn|qtIdent(pk) }} = {{ primary_keys[pk]|qtLiteral }}{% endfor %};
\ No newline at end of file
diff --git a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/objectname.sql b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/objectname.sql
new file mode 100644
index 0000000..188e69d
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/objectname.sql
@@ -0,0 +1,7 @@
+{# ============= Fetch the schema and object name for given object id ============= #}
+{% if obj_id %}
+SELECT n.nspname, r.relname
+FROM pg_class r
+ LEFT JOIN pg_namespace n ON (r.relnamespace = n.oid)
+WHERE r.oid = {{obj_id}};
+{% endif %}
diff --git a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/objectquery.sql b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/objectquery.sql
new file mode 100644
index 0000000..26dbf58
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/objectquery.sql
@@ -0,0 +1,12 @@
+{# SQL query for objects #}
+SELECT * FROM {{ conn|qtIdent(nsp_name, object_name) }}
+{% if sql_filter %}
+WHERE {{ sql_filter }}
+{% endif %}
+{% if pk_names %}
+ORDER BY {{ pk_names }}
+{% if cmd_type == 1 or cmd_type == 3 %} ASC {% elif cmd_type == 2 %} DESC {% endif %}
+{% endif %}
+{% if limit > 0 %}
+LIMIT {{ limit }}
+{% endif %}
diff --git a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/primary_keys.sql b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/primary_keys.sql
new file mode 100644
index 0000000..b05a697
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/primary_keys.sql
@@ -0,0 +1,8 @@
+{# ============= Fetch the primary keys for given object id ============= #}
+{% if obj_id %}
+SELECT at.attname, ty.typname
+FROM pg_attribute at LEFT JOIN pg_type ty ON (ty.oid = at.atttypid)
+WHERE attrelid={{obj_id}}::oid AND attnum = ANY (
+ (SELECT con.conkey FROM pg_class rel LEFT OUTER JOIN pg_constraint con ON con.conrelid=rel.oid
+ AND con.contype='p' WHERE rel.relkind IN ('r','s','t') AND rel.oid = {{obj_id}}::oid)::integer[])
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/update.sql b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/update.sql
new file mode 100644
index 0000000..d69c196
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/update.sql
@@ -0,0 +1,7 @@
+{# Update the row with primary keys (specified in primary_keys) #}
+ UPDATE {{ conn|qtIdent(nsp_name, object_name) }} SET
+{% for col in data_to_be_saved %}
+{% if not loop.first %}, {% endif %}{{ conn|qtIdent(col) }} = {{ data_to_be_saved[col]|qtLiteral }}{% endfor %}
+ WHERE
+{% for pk in primary_keys %}
+{% if not loop.first %} AND {% endif %}{{ conn|qtIdent(pk) }} = {{ primary_keys[pk]|qtLiteral }}{% endfor %};
\ No newline at end of file
diff --git a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/validate.sql b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/validate.sql
new file mode 100644
index 0000000..b80c5e9
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/validate.sql
@@ -0,0 +1,4 @@
+{# Validation query #}
+{% if row_filter %}
+ EXPLAIN SELECT * FROM {{ conn|qtIdent(nsp_name, object_name) }} WHERE {{ row_filter }}
+{% endif %}
\ No newline at end of file
[image/png] DataGrid-Design.png (265.6K, 4-DataGrid-Design.png)
download | view image
view thread (18+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [pgAdmin4] [Patch] Implementation of the Data Grid and Query Tool
In-Reply-To: <CANxoLDe8fRZ=m8NGPc0=YQLS0vTc4-L5xmjyn2J2V1jUt937Mg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox