public inbox for [email protected]
help / color / mirror / Atom feedFrom: Akshay Joshi <[email protected]>
To: Dave Page <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: [pgAdmin4] [Patch] Implementation of the Data Grid and Query Tool
Date: Thu, 14 Apr 2016 18:28:37 +0530
Message-ID: <CANxoLDe-pk8=s0JkW=9ccr5LBx-Fyh-vrPQ-hqLpYYjqfoUycw@mail.gmail.com> (raw)
In-Reply-To: <CA+OCxowg-0gM-SkmfFmWZDeHAJvhYtuyWdRWHtxrn_sp0rjAPA@mail.gmail.com>
References: <CANxoLDe8fRZ=m8NGPc0=YQLS0vTc4-L5xmjyn2J2V1jUt937Mg@mail.gmail.com>
<CA+OCxoz4ejTML6cjwMvOLd7vg4Dcd1koz30StnH_huND61gdzg@mail.gmail.com>
<CANxoLDemUxkGL_z6=OPkA=3E-+ZDDmA3oht2wgDoCmurVv3JYQ@mail.gmail.com>
<CA+OCxoxOOW4j0o+_NsV7JoW+-3aQ_As_xC90U3ShGw_DgWgLvQ@mail.gmail.com>
<CANxoLDcp=sjd=P8U7qUP3tOQThAXDx+GUFyoE6F9xNDKwa5MnA@mail.gmail.com>
<CA+OCxozK661Ha4RD51FtxmEdTzBd2h0nVgemAhfh0opbYOTerA@mail.gmail.com>
<CANxoLDcxkQ+T8XwpVabDo2=E5P0x3e27Gz4Ob3NAsi_9C=m14w@mail.gmail.com>
<CA+OCxowdBvsnin91F2chyWLaH1ivEZmD_N_oY2DEJ11jEe3OjQ@mail.gmail.com>
<CANxoLDd0Ljz7Oq6uaJo181uiAixXHsEpDQr3nFi8VyisYx-9sQ@mail.gmail.com>
<CA+OCxoz0Y-So4gOLVG2UVUEKBu-f9wAh0iEyMPfXH8ao-4Ai6w@mail.gmail.com>
<CANxoLDcmtT5zUyXAU_HvLcoJjFeYWfRx2egLWp_3rdwOsKXV_w@mail.gmail.com>
<CA+OCxowg-0gM-SkmfFmWZDeHAJvhYtuyWdRWHtxrn_sp0rjAPA@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
Hi All
I have fixed review comments given by Dave and couple of them are remaining
*Fixed Review Comments:*
- The View Data menu option should be on the Object menu, which should
mirror the Context menu, except options should be disabled when not
applicable instead of hidden.
- The Query Tool menu icon should be a glyphicon, to match the others.
- Please merge the functionality of the Refresh and Execute buttons into
one button. We shouldn't have two buttons that do essentially the same
thing.
- In Edit Grid mode, the History panel should log all queries (SELECTs,
UPDATEs, DELETEs etc) as it would in the Query Tool.
- In Edit Grid mode, the Messages panel should display any messages from
the most recent action as it would in the Query Tool.
- In Edit Grid mode, that textbox should be read-only, but should
display the SQL used (including any LIMIT/FILTER clauses)
- Please remove the border from the SQL box, such that it fills all
available space.
- The Filter box should be in a modal overlay over the top of the SQL
box/Results tabs as required. Those elements should be grayed whilst it is
open.
- Please adjust the height of the Delete icon in the Edit Grid, such
that it doesn't force the row height to be higher than it should be.
- I think the names of the tabs are far too long. Can we change them to
"Query 1", "Query 2" etc, then rename them to the filename if the user
saves/loads a file?
- We should add an "Edit" button, which opens a drop-down menu. This
would eventually include options as found on the Edit menu in the pgAdmin3
query tool, such as the "Clear SQL" option.
- Ashesh and I discussed changing the History tab to be a grid, showing:
Date/Time, Query (first line only), Rows affected, Runtime and Status,
in a row per query executed. Ashesh suggested using a sub-form that can
be expanded for each row, which could show the full query and error details
(SQL State etc). New rows should be added to the top of the list.
- Errors should be highlighted in the SQL box - a marker in the margin
to note the line, and spellcheck-style underlining for the error word.
- Query results should have spaces converted to " ", so that proper
indenting is maintained (for example, on EXPLAIN queries).
- on shutdown pgAdmin server , appropriate message should be displayed.
*Remaining review comments*:
- Please add an SQL button. This should show/hide the SQL panel in
*both* Query Tool and Edit Grid modes.
- If a field has been edited, but not saved, can we highlight it
somehow? Maybe make the text dark blue?
- The "Add Row" button only works if you're on the last page of the
resultset.
- Can the "Copy Row" button also populate the clipboard with CSV data
for the row?
- In Edit mode, we need to be able to represent/set values to NULL.
- The layout of the result tabs should be maintained if new Query Tool
or Edit Grid tabs are opened.
*TODO's* (apart from above)
- Open/Save SQL file.
- Cut, Copy, Paste functionality.
Attached is the patch file which contains complete DataGrid and Query Tool
code with fixed review comments. Can you please review the patch and let me
know the review comments if any else can you please commit the code if it
looks good to you.
On Tue, Apr 12, 2016 at 6:10 PM, Dave Page <[email protected]> wrote:
> Very nice!
>
>
> On Tue, Apr 12, 2016 at 1:11 PM, Akshay Joshi <
> [email protected]> wrote:
>
>> Hi Dave
>>
>> As per suggestion, I have changed History tab to be a grid. Attached is
>> the two screenshot one for Query Tool and other is for the Data Grid where
>> some of the transaction gets rollback while saving the data due to one of
>> the error condition. Please review the screenshots and let me know the
>> comments if any.
>>
>> On Fri, Apr 8, 2016 at 9:49 PM, Dave Page <[email protected]> wrote:
>>
>>>
>>>
>>> On Fri, Apr 8, 2016 at 3:39 PM, Akshay Joshi <
>>> [email protected]> wrote:
>>>
>>>> Hi Dave
>>>>
>>>> On Fri, Apr 8, 2016 at 2:32 PM, Dave Page <[email protected]> wrote:
>>>>
>>>>>
>>>>>
>>>>> On Fri, Apr 8, 2016 at 7:43 AM, Akshay Joshi <
>>>>> [email protected]> wrote:
>>>>>>
>>>>>>
>>>>>>>>>>> - The Query Tool menu icon should be a glyphicon, to match the
>>>>>>>>>>> others.
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> There is no glyphicon available which match the Query Tool
>>>>>>>>>> icon. I have found one like below which is "database-search" or can you
>>>>>>>>>> please suggest some other icon.
>>>>>>>>>> [image: Inline image 1]
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> That one looks perfect.
>>>>>>>>>
>>>>>>>>
>>>>>> We can't use this icon because it's not come with
>>>>>> Bootstrap , I have picked this from "http://glyphicons.com/"; and I
>>>>>> am not sure we can use it as per the Licence.
>>>>>>
>>>>>
>>>>> At the risk of annoying everyone immensely, on reflection I'm thinking
>>>>> we should use Font Awesome as our default generic icons, and fall back to
>>>>> Bootstrap's Glyphicons. I really hadn't realised how much larger the FA set
>>>>> is.
>>>>>
>>>>> For this particular issue, could we use FA's stacking? e.g. something
>>>>> like: https://jsfiddle.net/pa8x6nt3/
>>>>>
>>>>> If not, how about using the execute icon we discussed, e.g. fa-bolt?
>>>>>
>>>>
>>>> I have used fa-bolt. Apart from that I have completed below review
>>>> comments
>>>>
>>>
>>> OK.
>>>
>>>
>>>>
>>>>
>>>> - The View Data menu option should be on the Object menu, which
>>>> should mirror the Context menu, except options should be disabled when not
>>>> applicable instead of hidden. *Note*: - With current implementation
>>>> "Object" menu is re-created dynamically depending on the node clicked, so
>>>> we will have to re-create the "View Data" menu as well and it require
>>>> change in the generic code. For the time being "View Data" menu is visible
>>>> in "Object" menu when appropriate node is selected.
>>>> - Please merge the functionality of the Refresh and Execute buttons
>>>> into one button. We shouldn't have two buttons that do essentially the same
>>>> thing.
>>>> - In Edit Grid mode, that textbox should be read-only, but should
>>>> display the SQL used (including any LIMIT/FILTER clauses). Please
>>>> refer the attached screenshot (Modified-Data-Grid).
>>>> - Please adjust the height of the Delete icon in the Edit Grid,
>>>> such that it doesn't force the row height to be higher than it should be.
>>>> - I think the names of the tabs are far too long. Can we change
>>>> them to "Query 1", "Query 2" etc, then rename them to the filename
>>>> if the user saves/loads a file? *Note*: - As discussed I have added
>>>> one more container to show the title. Please refer the attached screenshots
>>>> (Modified-Query-Tool)
>>>> - Query results should have spaces converted to " ", so that
>>>> proper indenting is maintained (for example, on EXPLAIN queries)
>>>> - To fix this we have added css style "*white-space: pre-wrap;*",
>>>> but it changes the backgrid cell size. Please refer the
>>>> screenshot (Modified-Data-Grid).
>>>>
>>>> Hmmm. I'd rather that the heights didn't change (or at least the rows
>>> were resizeable like in pga3. Can we set the column to hide vertical
>>> overflow, unless it gets focus or something?
>>>
>>>
>>>> Please review the screenshots and please let me know will it looks
>>>> good.
>>>>
>>>
>>> Bar the cell size, certainly it's good :-)
>>>
>>> --
>>> Dave Page
>>> Blog: http://pgsnake.blogspot.com
>>> Twitter: @pgsnake
>>>
>>> EnterpriseDB UK: http://www.enterprisedb.com
>>> The Enterprise PostgreSQL Company
>>>
>>
>>
>>
>> --
>> *Akshay Joshi*
>> *Principal Software Engineer *
>>
>>
>>
>> *Phone: +91 20-3058-9517 <%2B91%2020-3058-9517>Mobile: +91 976-788-8246*
>>
>
>
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
--
*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:
[image/png] image.png (2.4K, 3-image.png)
download | view image
[application/octet-stream] SQLEditor_v2.patch (170.4K, 4-SQLEditor_v2.patch)
download | inline diff:
diff --git a/web/pgadmin/static/css/overrides.css b/web/pgadmin/static/css/overrides.css
index 3843734..a6f3994 100755
--- a/web/pgadmin/static/css/overrides.css
+++ b/web/pgadmin/static/css/overrides.css
@@ -528,6 +528,10 @@ fieldset[disabled] .form-control {
background-color: #AAA;
}
+.backgrid th.renderable, .backgrid td.renderable {
+ white-space: pre-wrap;
+}
+
.subnode-header {
background-color: #2C76B4;
height: 35px;
@@ -952,3 +956,8 @@ ul.nav.nav-tabs {
.pgadmin-controls.SQL>.CodeMirror {
height: 500px!important;
}
+
+.wcPanelTab > div .wcTabIcon.fa {
+ padding-left: 0px !important;
+ color: black;
+}
diff --git a/web/pgadmin/tools/datagrid/__init__.py b/web/pgadmin/tools/datagrid/__init__.py
new file mode 100644
index 0000000..2f4d82c
--- /dev/null
+++ b/web/pgadmin/tools/datagrid/__init__.py
@@ -0,0 +1,266 @@
+##########################################################################
+#
+# 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 []
+
+
+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>/<editor_title>', methods=["GET"])
+def panel(trans_id, is_query_tool, editor_title):
+ """
+ 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.
+ editor_title: Title of the editor
+ """
+
+ return render_template("datagrid/index.html", _=gettext, uniqueId=trans_id,
+ is_query_tool=is_query_tool, editor_title=editor_title)
+
+
[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)
+
+ # if connection id is None then no need to release the connection
+ if cmd_obj.conn_id is not None:
+ 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/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..31ade7d
--- /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="main-editor_panel">
+ <div id="fetching_data" class="sql-editor-busy-fetching hide">',
+ <span class="sql-editor-busy-icon"><img src="{{ url_for('browser.static', filename='css/aciTree/image/load-root.gif') }}"></span>
+ <span class="sql-editor-busy-text"></span>
+ </div>
+ <div class="sql-editor" data-trans-id="{{ uniqueId }}"></div>
+ </div>
+{% endblock %}
+
+{% block init_script %}
+try {
+ require(
+ ['jquery', 'pgadmin', 'pgadmin.sqleditor'],
+ function($, pgAdmin) {
+
+ var editorPanel = $('.sql-editor'),
+ loadingDiv = $('#fetching_data'),
+ msgDiv = loadingDiv.find('.sql-editor-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 }}, "{{ editor_title }}");
+ });
+} 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..dda607d
--- /dev/null
+++ b/web/pgadmin/tools/datagrid/templates/datagrid/js/datagrid.js
@@ -0,0 +1,428 @@
+define(
+ ['jquery','alertify', 'pgadmin', 'pgadmin.browser', 'wcdocker'],
+ function($, alertify, pgAdmin) {
+ // 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;
+ this.title_index = 1;
+
+ // 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: 'query_tool', module: this, applies: ['tools'],
+ callback: 'show_query_tool', enable: query_tool_menu_enabled,
+ priority: 1, label: '{{ _('Query tool') }}',
+ icon: 'fa fa-bolt'
+ }];
+
+ // 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', 'object'], callback: 'show_data_grid', enable: view_menu_enabled,
+ category: 'view_data', priority: 101, 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', 'object'], callback: 'show_data_grid', enable: view_menu_enabled,
+ category: 'view_data', priority: 102, label: '{{ _('View Last 100 Rows') }}'
+ },{
+ name: 'view_all_rows_context_' + supported_nodes[idx],
+ node: supported_nodes[idx], module: this, data: {mnuid: 3},
+ applies: ['context', 'object'], callback: 'show_data_grid', enable: view_menu_enabled,
+ category: 'view_data', priority: 103, label: '{{ _('View All Rows') }}'
+ },{
+ name: 'view_filtered_rows_context_' + supported_nodes[idx],
+ node: supported_nodes[idx], module: this, data: {mnuid: 4},
+ applies: ['context', 'object'], callback: 'show_filtered_row', enable: view_menu_enabled,
+ category: 'view_data', priority: 104, label: '{{ _('View Filtered Rows...') }}'
+ });
+ }
+
+ pgAdmin.Browser.add_menu_category('view_data', '{{ _('View Data') }}', 100, '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) {
+ var self = this;
+
+ /* 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 panel_title = ' Query-' + self.title_index;
+ self.title_index += 1;
+
+ var dashboardPanel = pgBrowser.docker.findPanels('dashboard');
+ dataGridPanel = pgBrowser.docker.addPanel('frm_datagrid', wcDocker.DOCK.STACKED, dashboardPanel[0]);
+ dataGridPanel.title(panel_title);
+ dataGridPanel.icon('fa fa-bolt');
+ 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/" + grid_title;
+ var openDataGridURL = function(j) {
+ setTimeout(function() {
+ var frameInitialized = j.data('frameInitialized');
+ if (frameInitialized) {
+ var frame = j.data('embeddedFrame');
+ 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 ;
+
+ var panel_title = ' Query-' + self.title_index;
+ self.title_index += 1;
+
+ $.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(panel_title);
+ queryToolPanel.icon('fa fa-bolt');
+ 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/" + grid_title;
+ var openQueryToolURL = function(j) {
+ setTimeout(function() {
+ var frameInitialized = j.data('frameInitialized');
+ if (frameInitialized) {
+ var frame = j.data('embeddedFrame');
+ 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..32ac6e4
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/__init__.py
@@ -0,0 +1,951 @@
+##########################################################################
+#
+# 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
+from tools.sqleditor.command import QueryToolCommand
+
+
+# Async Constants
+ASYNC_OK = 1
+ASYNC_READ_TIMEOUT = 2
+ASYNC_WRITE_TIMEOUT = 3
+ASYNC_NOT_CONNECTED = 4
+ASYNC_EXECUTION_ABORTED = 5
+
+# Transaction status constants
+TX_STATUS_IDLE = 0
+TX_STATUS__ACTIVE = 1
+TX_STATUS_INTRANS = 2
+TX_STATUS_INERROR = 3
+
+
+class SqlEditorModule(PgAdminModule):
+ """
+ class SqlEditorModule(PgAdminModule)
+
+ A module class for SQL Grid derived from PgAdminModule.
+ """
+
+ LABEL = "SQL Editor"
+
+ 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 []
+
+ 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 = 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
+ sql = None
+
+ return make_json_response(
+ data={
+ 'status': status, 'result': result,
+ 'filter_applied': filter_applied,
+ 'limit': limit, 'can_edit': can_edit,
+ 'can_filter': can_filter, 'sql': sql,
+ 'items_per_page': blueprint.items_per_page.get()
+ }
+ )
+
+
[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)
+
+ # If auto commit is False and transaction status is Idle
+ # then call is_begin_not_required() function to check BEGIN
+ # is required or not.
+
+ if not trans_obj.auto_commit \
+ and conn.transaction_status() == TX_STATUS_IDLE \
+ and is_begin_required(sql):
+ conn.execute_void("BEGIN;")
+
+ # Execute sql asynchronously with params is None
+ # and formatted_error is True.
+ status, result = conn.execute_async(sql)
+
+ # If the transaction aborted for some reason and
+ # Auto RollBack is True then issue a rollback to cleanup.
+ trans_status = conn.transaction_status()
+ if trans_status == TX_STATUS_INERROR and trans_obj.auto_rollback:
+ conn.execute_void("ROLLBACK;")
+ 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,
+ 'items_per_page': blueprint.items_per_page.get()
+ }
+ )
+
+
[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
+ rows_affected = 0
+
+ # Check the transaction and connection status
+ status, error_msg, conn, trans_obj, session_obj = check_transaction_status(trans_id)
+ if status and conn is not None and session_obj is not None:
+ status, result, col_info = conn.poll()
+ if status == ASYNC_OK:
+ status = 'Success'
+ if 'primary_keys' in session_obj:
+ primary_keys = session_obj['primary_keys']
+
+ # if transaction object is instance of QueryToolCommand
+ # and transaction aborted for some reason then issue a
+ # rollback to cleanup
+ if isinstance(trans_obj, QueryToolCommand):
+ trans_status = conn.transaction_status()
+ if trans_status == TX_STATUS_INERROR and trans_obj.auto_rollback:
+ conn.execute_void("ROLLBACK;")
+ 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()
+ rows_affected = conn.rows_affected()
+
+ 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()
+ rows_affected = conn.rows_affected()
+
+ return make_json_response(data={'status': status, 'result': result,
+ 'colinfo': col_info, 'primary_keys': primary_keys,
+ 'rows_affected': rows_affected})
+
+
[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, query_res = trans_obj.save(changed_data)
+ else:
+ status = False
+ res = error_msg
+ query_res = None
+
+ return make_json_response(data={'status': status, 'result': res, 'query_result': query_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]('/auto_commit/<int:trans_id>', methods=["PUT", "POST"])
+@login_required
+def set_auto_commit(trans_id):
+ """
+ This method is used to set the value for auto commit .
+
+ Args:
+ trans_id: unique transaction id
+ """
+ if request.data:
+ auto_commit = json.loads(request.data.decode())
+ else:
+ auto_commit = 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_auto_commit method of transaction object
+ trans_obj.set_auto_commit(auto_commit)
+
+ # 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]('/auto_rollback/<int:trans_id>', methods=["PUT", "POST"])
+@login_required
+def set_auto_rollback(trans_id):
+ """
+ This method is used to set the value for auto commit .
+
+ Args:
+ trans_id: unique transaction id
+ """
+ if request.data:
+ auto_rollback = json.loads(request.data.decode())
+ else:
+ auto_rollback = 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_auto_rollback method of transaction object
+ trans_obj.set_auto_rollback(auto_rollback)
+
+ # 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]("/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")
+
+
+def is_begin_required(query):
+ word_len = 0
+ query = query.strip()
+ query_len = len(query)
+
+ # Check word length (since "beginx" is not "begin").
+ while (word_len < query_len) and query[word_len].isalpha():
+ word_len += 1
+
+ # Transaction control commands. These should include every keyword that
+ # gives rise to a TransactionStmt in the backend grammar, except for the
+ # savepoint-related commands.
+ #
+ # (We assume that START must be START TRANSACTION, since there is
+ # presently no other "START foo" command.)
+
+ keyword = query[0:word_len]
+
+ if word_len == 5 and keyword.lower() == "abort":
+ return False
+ if word_len == 5 and keyword.lower() == "begin":
+ return False
+ if word_len == 5 and keyword.lower() == "start":
+ return False
+ if word_len == 6 and keyword.lower() == "commit":
+ return False
+ if word_len == 3 and keyword.lower() == "end":
+ return False
+ if word_len == 8 and keyword.lower() == "rollback":
+ return False
+ if word_len == 7 and keyword.lower() == "prepare":
+ # PREPARE TRANSACTION is a TC command, PREPARE foo is not
+ query = query[word_len:query_len]
+ query = query.strip()
+ query_len = len(query)
+ word_len = 0
+
+ while (word_len < query_len) and query[word_len].isalpha():
+ word_len += 1
+
+ keyword = query[0:word_len]
+ if word_len == 11 and keyword.lower() == "transaction":
+ return False
+ return True
+
+ # Commands not allowed within transactions. The statements checked for
+ # here should be exactly those that call PreventTransactionChain() in the
+ # backend.
+ if word_len == 6 and keyword.lower() == "vacuum":
+ return False
+
+ if word_len == 7 and keyword.lower() == "cluster":
+ # CLUSTER with any arguments is allowed in transactions
+ query = query[word_len:query_len]
+ query = query.strip()
+
+ if query[0].isalpha():
+ return True # has additional words
+ return False # it's CLUSTER without arguments
+
+ if word_len == 6 and keyword.lower() == "create":
+ query = query[word_len:query_len]
+ query = query.strip()
+ query_len = len(query)
+ word_len = 0
+
+ while (word_len < query_len) and query[word_len].isalpha():
+ word_len += 1
+
+ keyword = query[0:word_len]
+ if word_len == 8 and keyword.lower() == "database":
+ return False
+ if word_len == 10 and keyword.lower() == "tablespace":
+ return False
+
+ # CREATE [UNIQUE] INDEX CONCURRENTLY isn't allowed in xacts
+ if word_len == 7 and keyword.lower() == "cluster":
+ query = query[word_len:query_len]
+ query = query.strip()
+ query_len = len(query)
+ word_len = 0
+
+ while (word_len < query_len) and query[word_len].isalpha():
+ word_len += 1
+
+ keyword = query[0:word_len]
+
+ if word_len == 5 and keyword.lower() == "index":
+ query = query[word_len:query_len]
+ query = query.strip()
+ query_len = len(query)
+ word_len = 0
+
+ while (word_len < query_len) and query[word_len].isalpha():
+ word_len += 1
+
+ keyword = query[0:word_len]
+ if word_len == 12 and keyword.lower() == "concurrently":
+ return False
+ return True
+
+ if word_len == 5 and keyword.lower() == "alter":
+ query = query[word_len:query_len]
+ query = query.strip()
+ query_len = len(query)
+ word_len = 0
+
+ while (word_len < query_len) and query[word_len].isalpha():
+ word_len += 1
+
+ keyword = query[0:word_len]
+
+ # ALTER SYSTEM isn't allowed in xacts
+ if word_len == 6 and keyword.lower() == "system":
+ return False
+ return True
+
+ # Note: these tests will match DROP SYSTEM and REINDEX TABLESPACE, which
+ # aren't really valid commands so we don't care much. The other four
+ # possible matches are correct.
+ if word_len == 4 and keyword.lower() == "drop" \
+ or word_len == 7 and keyword.lower() == "reindex":
+ query = query[word_len:query_len]
+ query = query.strip()
+ query_len = len(query)
+ word_len = 0
+
+ while (word_len < query_len) and query[word_len].isalpha():
+ word_len += 1
+
+ keyword = query[0:word_len]
+ if word_len == 8 and keyword.lower() == "database":
+ return False
+ if word_len == 6 and keyword.lower() == "system":
+ return False
+ if word_len == 10 and keyword.lower() == "tablespace":
+ return False
+ return True
+
+ # DISCARD ALL isn't allowed in xacts, but other variants are allowed.
+ if word_len == 7 and keyword.lower() == "discard":
+ query = query[word_len:query_len]
+ query = query.strip()
+ query_len = len(query)
+ word_len = 0
+
+ while (word_len < query_len) and query[word_len].isalpha():
+ word_len += 1
+
+ keyword = query[0:word_len]
+ if word_len == 3 and keyword.lower() == "all":
+ return False
+ return True
+
+ return True
diff --git a/web/pgadmin/tools/sqleditor/command.py b/web/pgadmin/tools/sqleditor/command.py
new file mode 100644
index 0000000..88259bc
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/command.py
@@ -0,0 +1,638 @@
+##########################################################################
+#
+# 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
+ query_res = dict()
+ count = 0
+
+ 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)
+ rows_affected = conn.rows_affected()
+
+ # store the result of each query in dictionary
+ query_res[count] = {'status': status, 'result': res,
+ 'sql': sql, 'rows_affected': rows_affected}
+ count += 1
+
+ if not status:
+ conn.execute_void('ROLLBACK;')
+ # If we roll backed every thing then update the message for
+ # each sql query.
+ for val in query_res:
+ if query_res[val]['status']:
+ query_res[val]['result'] = 'Transaction ROLLBACK'
+
+ return status, res, query_res
+
+ # Commit the transaction if there is no error found
+ conn.execute_void('COMMIT;')
+
+ return status, res, query_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
+ self.auto_rollback = False
+ self.auto_commit = True
+
+ 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
+
+ def set_auto_rollback(self, auto_rollback):
+ self.auto_rollback = auto_rollback
+
+ def set_auto_commit(self, auto_commit):
+ self.auto_commit = auto_commit
+
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..998aa68
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/static/css/sqleditor.css
@@ -0,0 +1,250 @@
+#main-editor_panel {
+ height: 100%;
+ width: 100%;
+}
+
+.sql-editor {
+ position: absolute;
+ left: 0;
+ right: 0;
+ top : 0;
+ bottom: 0;
+}
+
+.sql-editor-busy-fetching {
+ position:absolute;
+ left: 0;
+ top: 41px;
+ bottom: 0;
+ right: 0;
+ margin:0;
+ padding: 0;
+ background: black;
+ opacity: 0.4;
+ z-index: 100;
+}
+
+.sql-editor-busy-icon {
+ position:absolute;
+ left: 45%;
+ top: 40%;
+}
+
+.sql-editor-busy-text {
+ position:absolute;
+ left: 42%;
+ top: 50%;
+ font-size: 20px;
+}
+
+#editor-panel {
+ position: absolute;
+ left: 0;
+ right: 0;
+ top : 65px;
+ bottom: 0;
+ z-index: 0;
+}
+
+.editor-title {
+ background-color: rgb(33, 81, 128);
+ padding: 2px;
+ text-color: white;
+ color: white;
+ font-weight: bold;
+}
+
+.sql-editor-btn-group {
+ background-color: #D2D2D2;
+ border: 2px solid #A9A9A9;
+ left: 0px;
+ right: 0px;
+ padding: 2px;
+}
+
+.sql-editor-btn-group button {
+ padding: 5px;
+}
+
+.sql-editor-btn-group button.dropdown-toggle {
+ padding-left: 5px !important;
+ padding-right: 5px !important;
+}
+
+.sql-editor-btn-group .dropdown-menu {
+ min-width: initial;
+}
+
+.sql-editor-btn-group .backgrid-filter.form-search {
+ position: relative;
+ width: 248px;
+ height: 30px;
+ float: right;
+ margin-top: 2px;
+ margin-right: 10px;
+}
+
+#output-panel .wcDocker {
+ top: 0px;
+ bottom: 0px;
+ height: auto;
+}
+
+#output-panel .wcFrameCenter {
+ overflow: hidden;
+}
+
+.sql-editor-grid-container {
+ height: calc(100% - 45px);
+ overflow: auto;
+}
+
+#datagrid-paginator {
+ 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: small;
+ font-weight: normal;
+ display: inline-block;
+ min-width: 28px;
+}
+
+/* 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;
+}
+
+.filter-container {
+ position: relative;
+ background-color: white;
+ border: 1px solid black;
+ box-shadow: 0.5px 0.5px 5px #000;
+ padding-bottom: 30px;
+ z-index: 1;
+ margin: auto;
+ width: 60%;
+}
+
+.filter-container .CodeMirror-scroll {
+ min-height: 120px;
+ max-height: 120px;
+}
+
+.filter-container .sql-textarea{
+ box-shadow: 0.1px 0.1px 3px #000;
+ margin-bottom: 5px;
+}
+
+.filter-title {
+ background-color: rgb(33, 81, 128);
+ padding: 2px;
+ text-color: white;
+ color: white;
+ font-weight: bold;
+}
+
+#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 {
+ white-space:pre-wrap;
+ font-family: monospace;
+ padding-top: 5px;
+ padding-left: 10px;
+}
+
+.limit-enabled {
+ background-color: white;
+}
+
+.sql-editor-history-container {
+ height: 100%;
+ overflow: auto;
+}
+
+.sql-status-cell {
+ max-width: 30px;
+}
+
+.btn-circle {
+ width: 20px;
+ height: 20px;
+ text-align: center;
+ padding: 0;
+ font-size: 10px;
+ line-height: 1.428571429;
+ border-radius: 10px;
+}
+
+.visibility-hidden {
+ visibility: hidden;
+}
+
+.sql-editor-mark {
+ border-bottom: 2px dotted red;
+}
+
+#editor-panel .CodeMirror-activeline-background {
+ background: #5B9CEF;
+ color: white;
+}
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..3366ee2
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/js/sqleditor.js
@@ -0,0 +1,2164 @@
+define(
+ ['jquery', 'underscore', 'alertify', 'pgadmin', 'backbone', 'backgrid', 'codemirror',
+ 'codemirror/mode/sql', 'codemirror/addon/selection/mark-selection', 'codemirror/addon/selection/active-line',
+ '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-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-flash": "on_flash",
+ "click #btn-cancel-query": "on_cancel_query",
+ "click #btn-download": "on_download",
+ "click #btn-clear": "on_clear",
+ "click #btn-auto-commit": "on_auto_commit",
+ "click #btn-auto-rollback": "on_auto_rollback",
+ "click #btn-clear-history": "on_clear_history",
+ "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="sql-editor-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>',
+ '<i class="fa fa-floppy-o" aria-hidden="true"></i>',
+ '</button>',
+ '</div>',
+ '<div class="btn-group" role="group" aria-label="">',
+ '<button id="btn-copy" type="button" class="btn btn-default" title="{{ _('Copy Row') }}" disabled>',
+ '<i class="fa fa-files-o" aria-hidden="true"></i>',
+ '</button>',
+ '<button id="btn-paste" type="button" class="btn btn-default" title="{{ _('Paste Row') }}" disabled>',
+ '<i class="fa fa-clipboard" aria-hidden="true"></i>',
+ '</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>',
+ '<i class="fa fa-plus" aria-hidden="true"></i>',
+ '</button>',
+ '</div>',
+ '<div class="btn-group" role="group" aria-label="">',
+ '<button id="btn-filter" type="button" class="btn btn-default" title="{{ _('Filter') }}" disabled>',
+ '<i class="fa fa-filter" aria-hidden="true"></i>',
+ '</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="#">{{ _('Remove') }}</a>',
+ '<a id="btn-include-filter" href="#">{{ _('By selection') }}</a>',
+ '<a id="btn-exclude-filter" href="#">{{ _('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-flash" type="button" class="btn btn-default" title="{{ _('Execute/Refresh') }}">',
+ '<i class="fa fa-bolt" aria-hidden="true"></i>',
+ '</button>',
+ '<button id="btn-query-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>',
+ '<ul class="dropdown-menu dropdown-menu">',
+ '<li>',
+ '<a id="btn-auto-commit" href="#">',
+ '<i class="auto-commit fa fa-check" aria-hidden="true"></i>',
+ '<span> {{ _('Auto-Commit') }} </span>',
+ '</a>',
+ '<a id="btn-auto-rollback" href="#">',
+ '<i class="auto-rollback fa fa-check visibility-hidden" aria-hidden="true"></i>',
+ '<span> {{ _('Auto-Rollback') }} </span>',
+ '</a>',
+ '</li>',
+ '</ul>',
+ '<button id="btn-cancel-query" type="button" class="btn btn-default" title="{{ _('Cancel query') }}" disabled>',
+ '<i class="fa fa-stop" aria-hidden="true"></i>',
+ '</button>',
+ '</div>',
+ '<div class="btn-group" role="group" aria-label="">',
+ '<button id="btn-edit" type="button" class="btn btn-default" title="{{ _('Edit') }}">',
+ '<i class="fa fa-pencil-square-o" aria-hidden="true"></i>',
+ '</button>',
+ '<button id="btn-edit-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>',
+ '<ul class="dropdown-menu dropdown-menu">',
+ '<li>',
+ '<a id="btn-clear" href="#">',
+ '<i class="fa fa-eraser" aria-hidden="true"></i>',
+ '<span> {{ _('Clear query window') }} </span>',
+ '</a>',
+ '<a id="btn-clear-history" href="#">',
+ '<i class="fa fa-eraser" aria-hidden="true"></i>',
+ '<span> {{ _('Clear history') }} </span>',
+ '</a>',
+ '</li>',
+ '</ul>',
+ '</div>',
+ '<div class="btn-group" role="group" aria-label="">',
+ '<button id="btn-download" type="button" class="btn btn-default" title="{{ _('Download as CSV') }}">',
+ '<i class="fa fa-download" aria-hidden="true"></i>',
+ '</button>',
+ '</div>',
+ '</div>',
+ '<div class="editor-title"></div>',
+ '<div id="filter" class="filter-container hidden">',
+ '<div class="filter-title">Filter</div>',
+ '<div class="sql-textarea">',
+ '<textarea id="sql_filter" row="5"></textarea>',
+ '</div>',
+ '<div class="btn-group">',
+ '<button id="btn-cancel" type="button" class="btn btn-danger" title="{{ _('Cancel') }}">',
+ '<i class="fa fa-times" aria-hidden="true"></i> {{ _('Cancel') }}',
+ '</button>',
+ '</div>',
+ '<div class="btn-group">',
+ '<button id="btn-apply" type="button" class="btn btn-primary" title="{{ _('Apply') }}">',
+ '<i class="fa fa-check" aria-hidden="true"></i> {{ _('Apply') }}',
+ '</button>',
+ '</div>',
+ '</div>',
+ '<div id="editor-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());
+
+ $('.editor-title').text(self.editor_title);
+
+ 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"
+ });
+
+ // Create main wcDocker instance
+ var main_docker = new wcDocker(
+ '#editor-panel', {
+ allowContextMenu: false,
+ allowCollapse: false,
+ themePath: '{{ url_for('static', filename='css/wcDocker/Themes') }}',
+ theme: 'pgadmin'
+ });
+
+ var sub_panel = new pgAdmin.Browser.Panel({
+ name: 'sub_panel',
+ title: false,
+ width: '100%',
+ height:'100%',
+ isCloseable: false,
+ isPrivate: true
+ });
+
+ sub_panel.load(main_docker);
+ panel = main_docker.addPanel('sub_panel', wcDocker.DOCK.LEFT);
+
+ // Create a Splitter to divide sql code and data output
+ var hSplitter = new wcSplitter(
+ "#editor-panel", panel,
+ wcDocker.ORIENTATION.VERTICAL
+ );
+ hSplitter.scrollable(0, false, false);
+ hSplitter.scrollable(1, true, true);
+
+ // Initialize this splitter with a layout in each pane.
+ hSplitter.initLayouts(wcDocker.LAYOUT.SIMPLE, wcDocker.LAYOUT.SIMPLE);
+
+ // By default, the splitter splits down the middle, we split the main panel by 80%.
+ hSplitter.pos(0.25);
+
+ var text_container = $('<textarea id="sql_query_tool" row="5"></textarea>');
+
+ // Add text_container at the top half of the splitter
+ hSplitter.left().addItem(text_container);
+
+ // Add data output panel at the bottom half of the splitter
+ var output_container = $('<div id="output-panel"></div>');
+ hSplitter.right().addItem(output_container);
+
+ self.query_tool_obj = CodeMirror.fromTextArea(text_container.get(0), {
+ lineNumbers: true,
+ lineWrapping: true,
+ matchBrackets: true,
+ indentUnit: 4,
+ styleSelectedText: true,
+ mode: "text/x-sql"
+ });
+
+ // Create wcDocker for tab set.
+ var docker = new wcDocker(
+ '#output-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 id ="history_grid" class="sql-editor-history-container"></div>'
+ })
+
+ // Load all the created panels
+ data_output.load(docker);
+ explain.load(docker);
+ messages.load(docker);
+ history.load(docker);
+
+ // Add all the panels to the docker
+ self.data_output_panel = docker.addPanel('data_output', wcDocker.DOCK.LEFT);
+ self.explain_panel = docker.addPanel('explain', wcDocker.DOCK.STACKED, self.data_output_panel);
+ self.messages_panel = docker.addPanel('messages', wcDocker.DOCK.STACKED, self.data_output_panel);
+ self.history_panel = docker.addPanel('history', wcDocker.DOCK.STACKED, self.data_output_panel);
+
+ self.render_history_grid();
+ },
+
+ /* 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('.sql-editor-btn-group').append(clientSideFilter.render().el);
+
+ // Forcefully sorting by the first column.
+ if (columns.length > 1) {
+ collection.setSorting(columns[1].name);
+ collection.fullCollection.sort();
+ }
+ },
+
+ /* This function is responsible to create and render the
+ * new backgrid for the history tab.
+ */
+ render_history_grid: function() {
+ var self = this;
+
+ // Remove any existing grid first
+ if (self.history_grid) {
+ self.history_grid.remove();
+ }
+
+ var history_model = Backbone.Model.extend({
+ defaults: {
+ status: undefined,
+ start_time: undefined,
+ query: undefined,
+ row_affected: 0,
+ row_retrieved: 0,
+ total_time: undefined,
+ message: ''
+ }
+ });
+
+ var history_collection = self.history_collection = new (Backbone.Collection.extend({
+ model: history_model,
+ // comparator to sort the history in reverse order of the start_time
+ comparator: function(a, b) {
+ return -a.get('start_time').localeCompare(b.get('start_time'));
+ }
+ }));
+ var columns = [{
+ name: "status",
+ label: "",
+ cell: Backgrid.Cell.extend({
+ class: 'sql-status-cell',
+ render: function() {
+ this.$el.empty();
+ var $btn = $('<button></button>', {
+ class: 'btn btn-circle'
+ }).appendTo(this.$el);
+ var $circleDiv = $('<i></i>', {class: 'fa'}).appendTo($btn);
+ if (this.model.get('status')) {
+ $btn.addClass('btn-success');
+ $circleDiv.addClass('fa-check');
+ } else {
+ $btn.addClass('btn-danger');
+ $circleDiv.addClass('fa-times');
+ }
+
+ return this;
+ },
+ editable: false
+ }),
+ editable: false
+ }, {
+ name: "start_time",
+ label: "Date",
+ cell: "string",
+ editable: false
+ }, {
+ name: "query",
+ label: "Query",
+ cell: "string",
+ editable: false
+ }, {
+ name: "row_affected",
+ label: "Rows affected",
+ cell: "integer",
+ editable: false
+ }, {
+ name: "total_time",
+ label: "Total Time",
+ cell: "string",
+ editable: false
+ }, {
+ name: "message",
+ label: "Message",
+ cell: "string",
+ editable: false
+ }];
+
+ var grid = self.history_grid = new Backgrid.Grid({
+ columns: columns,
+ collection: history_collection,
+ className: "backgrid table-bordered"
+ });
+
+ // Render the grid
+ self.$el.find('#history_grid').append(self.history_grid.render().$el);
+ },
+
+ // 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 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() {
+ $('#filter').addClass('hidden');
+ $('#editor-panel').removeClass('sql-editor-busy-fetching');
+ },
+
+ // 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 flash button click.
+ on_flash: function() {
+ var self = this;
+
+ // Trigger the flash signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:flash',
+ 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('');
+ },
+
+ // Callback function for the clear history button click.
+ on_clear_history: function() {
+ var self = this;
+
+ // Remove any existing grid first
+ if (self.history_grid) {
+ self.history_collection.reset();
+ }
+ },
+
+ // Callback function for the auto commit button click.
+ on_auto_commit: function() {
+ var self = this;
+
+ // Trigger the auto-commit signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:auto_commit',
+ self,
+ self.handler
+ );
+ },
+
+ // Callback function for the auto rollback button click.
+ on_auto_rollback: function() {
+ var self = this;
+
+ // Trigger the download signal to the SqlEditorController class
+ self.handler.trigger(
+ 'pgadmin-sqleditor:button:auto_rollback',
+ self,
+ self.handler
+ );
+ }
+ });
+
+ /* 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, editor_title) {
+ var self = this;
+
+ self.is_query_tool = is_query_tool;
+ self.items_per_page = 25;
+ self.rows_affected = 0;
+ self.marked_line_no = 0;
+
+ // 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');
+
+ self.gridView.editor_title = editor_title;
+
+ // 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: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:flash', self._refresh, self);
+ self.on('pgadmin-sqleditor:button:cancel-query', self._cancel_query, self);
+ self.on('pgadmin-sqleditor:button:download', self._download, self);
+ self.on('pgadmin-sqleditor:button:auto_rollback', self._auto_rollback, self);
+ self.on('pgadmin-sqleditor:button:auto_commit', self._auto_commit, self);
+
+ if (self.is_query_tool) {
+ self.gridView.query_tool_obj.refresh();
+ }
+ else {
+ self.gridView.query_tool_obj.setOption("readOnly",true);
+ 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.rows_affected = 0;
+
+ 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;
+ self.items_per_page = res.data.items_per_page;
+
+ // Set the sql query to the SQL panel
+ self.gridView.query_tool_obj.setValue(res.data.sql);
+ self.query = res.data.sql;
+
+ /* 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.update_msg_history(false, res.data.result);
+ }
+ },
+ error: function(e) {
+ if (e.readyState == 0) {
+ self.update_msg_history(false,
+ '{{ _('Not connected to server Or connection with the server has been closed.') }}'
+ );
+ return;
+ }
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ self.update_msg_history(false, 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();
+ self.rows_affected = res.data.rows_affected;
+
+ /* 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
+ self.total_time = self.get_query_run_time(self.query_start_time, self.query_end_time);
+ self.update_msg_history(true, res.data.result);
+ }
+
+ // 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.update_msg_history(false, res.data.result);
+ }
+ else if (res.data.status === 'Cancel') {
+ self.update_msg_history(false, "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);
+ }
+
+ if (e.readyState == 0) {
+ self.update_msg_history(false,
+ '{{ _('Not connected to server Or connection with the server has been closed.') }}'
+ );
+ return;
+ }
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ self.update_msg_history(false, 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);
+ $(".limit").addClass('limit-enabled');
+ $("#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
+ self.total_time = self.get_query_run_time(self.query_start_time, self.query_end_time);
+ self.update_msg_history(true, "", false);
+ var message = 'Total query runtime: ' + self.total_time + '\n' + self.rows_affected + ' rows retrieved.';
+ $('.sql-editor-message').text(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 {
+ alertify.alert('Fetching Type Error', res.data.result);
+ }
+ }
+ });
+
+ return columns;
+ },
+
+ // This function is used to raise appropriate message.
+ update_msg_history: function(status, msg, clear_grid) {
+ var self = this;
+
+ if (clear_grid === undefined)
+ clear_grid = true;
+
+ self.trigger('pgadmin-sqleditor:loading-icon:hide');
+ $('.sql-editor-message').text(msg);
+ self.gridView.messages_panel.focus();
+
+ if (self.is_query_tool && clear_grid) {
+ // 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();
+ }
+
+ self.gridView.history_collection.add(
+ {'status' : status, 'start_time': self.query_start_time.toString(),
+ 'query': self.query, 'row_affected': self.rows_affected,
+ 'total_time': self.total_time, 'message':msg
+ });
+
+ self.gridView.history_collection.sort();
+ },
+
+ // 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) {
+ alertify.alert(
+ '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.trigger('pgadmin-sqleditor:loading-icon:hide');
+ $('.sql-editor-message').text(res.data.result);
+ self.gridView.messages_panel.focus();
+ }
+
+ // Update the sql results in history tab
+ _.each(res.data.query_result, function(r) {
+
+ self.gridView.history_collection.add(
+ {'status' : r.status, 'start_time': self.query_start_time.toString(),
+ 'query': r.sql, 'row_affected': r.rows_affected,
+ 'total_time': self.total_time, 'message': r.result
+ });
+ });
+ },
+ error: function(e) {
+ if (e.readyState == 0) {
+ self.update_msg_history(false,
+ '{{ _('Not connected to server Or connection with the server has been closed.') }}'
+ );
+ return;
+ }
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ self.update_msg_history(false, 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.
+ if (self.is_query_tool)
+ self._execute();
+ else
+ self._execute_data_query();
+ },
+
+ // This function will show the filter in the text area.
+ _show_filter: function() {
+ var self = this;
+
+ $.ajax({
+ url: "{{ url_for('sqleditor.index') }}" + "filter/get/" + self.transId,
+ method: 'GET',
+ success: function(res) {
+ if (res.data.status) {
+ $('#filter').removeClass('hidden');
+ $('#editor-panel').addClass('sql-editor-busy-fetching');
+ 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 {
+ alertify.alert('Get Filter Error', res.data.result);
+ }
+ },
+ error: function(e) {
+ if (e.readyState == 0) {
+ alertify.alert('Get Filter Error',
+ '{{ _('Not connected to server Or connection with the server has been closed.') }}'
+ );
+ return;
+ }
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ alertify.alert('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 {
+ alertify.alert('Filter By Selection Error', res.data.result);
+ }
+ },
+ error: function(e) {
+ if (e.readyState == 0) {
+ alertify.alert('Filter By Selection Error',
+ '{{ _('Not connected to server Or connection with the server has been closed.') }}'
+ );
+ return;
+ }
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ alertify.alert('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 {
+ alertify.alert('Filter Exclude Selection Error', res.data.result);
+ }
+ },
+ error: function(e) {
+ if (e.readyState == 0) {
+ alertify.alert('Filter Exclude Selection Error',
+ '{{ _('Not connected to server Or connection with the server has been closed.') }}'
+ );
+ return;
+ }
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ alertify.alert('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 {
+ alertify.alert('Remove Filter Error', res.data.result);
+ }
+ },
+ error: function(e) {
+ if (e.readyState == 0) {
+ alertify.alert('Remove Filter Error',
+ '{{ _('Not connected to server Or connection with the server has been closed.') }}'
+ );
+ return;
+ }
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ alertify.alert('Remove Filter Error', msg);
+ }
+ });
+ },
+
+ // This function will apply the filter.
+ _apply_filter: function() {
+ var self = this;
+ sql = self.gridView.filter_obj.getValue();
+
+ // 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');
+ $('#editor-panel').removeClass('sql-editor-busy-fetching');
+ // Refresh the sql grid
+ self._refresh();
+ }
+ else {
+ alertify.alert('Apply Filter Error',res.data.result);
+ }
+ },
+ error: function(e) {
+ if (e.readyState == 0) {
+ alertify.alert('Apply Filter Error',
+ '{{ _('Not connected to server Or connection with the server has been closed.') }}'
+ );
+ return;
+ }
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ alertify.alert('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) {
+ // Refresh the sql grid
+ self._refresh();
+ }
+ else
+ alertify.alert('Change limit Error', res.data.result);
+ },
+ error: function(e) {
+ if (e.readyState == 0) {
+ alertify.alert('Change limit Error',
+ '{{ _('Not connected to server Or connection with the server has been closed.') }}'
+ );
+ return;
+ }
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ alertify.alert('Change limit Error', msg);
+ }
+ });
+ },
+
+ // This function is used to enable/disable buttons
+ disable_tool_buttons: function(disabled) {
+ $("#btn-clear").prop('disabled', disabled);
+ $("#btn-query-dropdown").prop('disabled', disabled);
+ $("#btn-edit-dropdown").prop('disabled', disabled);
+ $("#btn-edit").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();
+
+ self.query_start_time = new Date();
+ self.query = sql;
+ self.rows_affected = 0;
+
+ 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) {
+ // Remove marker
+ if (self.gridView.marker) {
+ self.gridView.marker.clear();
+ delete self.gridView.marker;
+ self.gridView.marker = null;
+
+ // Remove already existing marker
+ self.gridView.query_tool_obj.removeLineClass(self.marked_line_no, 'wrap', 'CodeMirror-activeline-background');
+ }
+
+ 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;
+ self.items_per_page = res.data.items_per_page;
+
+ // If status is True then poll the result.
+ self._poll();
+ }
+ else {
+ self.disable_tool_buttons(false);
+ $("#btn-cancel-query").prop('disabled', true);
+ self.update_msg_history(false, res.data.result);
+
+ // Highlight the error in the sql panel
+ self._highlight_error(res.data.result);
+ }
+ },
+ error: function(e) {
+ self.disable_tool_buttons(false);
+ $("#btn-cancel-query").prop('disabled', true);
+
+ if (e.readyState == 0) {
+ self.update_msg_history(false,
+ '{{ _('Not connected to server Or connection with the server has been closed.') }}'
+ );
+ return;
+ }
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ self.update_msg_history(false, msg);
+ }
+ });
+ },
+
+ /* This function is used to highlight the error line and
+ * underlining for the error word.
+ */
+ _highlight_error: function(result) {
+ var self = this,
+ error_line_no = 0,
+ start_marker = 0,
+ end_marker = 0,
+ selected_line_no = 0;
+
+ // Remove already existing marker
+ self.gridView.query_tool_obj.removeLineClass(self.marked_line_no, 'wrap', 'CodeMirror-activeline-background');
+
+ // In case of selection we need to find the actual line no
+ if (self.gridView.query_tool_obj.getSelection().length > 0)
+ selected_line_no = self.gridView.query_tool_obj.getCursor(true).line;
+
+ // Fetch the LINE string using regex from the result
+ var line = /LINE (\d+)/.exec(result),
+ // Fetch the Character string using regex from the result
+ char = /Character: (\d+)/.exec(result);
+
+ // If line and character is null then no need to mark
+ if (line != null && char != null) {
+ error_line_no = self.marked_line_no = (parseInt(line[1]) - 1) + selected_line_no;
+ var error_char_no = (parseInt(char[1]) - 1);
+
+ /* We need to loop through each line till the error line and
+ * count the total no of character to figure out the actual
+ * starting/ending marker point for the individual line. We
+ * have also added 1 per line for the "\n" character.
+ */
+ var prev_line_chars = 0;
+ var loop_index = selected_line_no > 0 ? selected_line_no : 0;
+ for (var i = loop_index; i < error_line_no; i++)
+ prev_line_chars += self.gridView.query_tool_obj.getLine(i).length + 1;
+
+ /* Marker starting point for the individual line is
+ * equal to error character index minus total no of
+ * character till the error line starts.
+ */
+ start_marker = error_char_no - prev_line_chars;
+
+ // Find the next space from the character or end of line
+ var error_line = self.gridView.query_tool_obj.getLine(error_line_no);
+ end_marker = error_line.indexOf(' ', start_marker);
+ if (end_marker < 0)
+ end_marker = error_line.length;
+
+ // Mark the error text
+ self.gridView.marker = self.gridView.query_tool_obj.markText(
+ {line: error_line_no, ch: start_marker},
+ {line: error_line_no, ch: end_marker},
+ {className: "sql-editor-mark"}
+ );
+
+ self.gridView.query_tool_obj.addLineClass(self.marked_line_no, 'wrap', 'CodeMirror-activeline-background');
+ }
+ },
+
+ // 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);
+ alertify.alert('Cancel Query Error', res.data.result);
+ }
+ },
+ error: function(e) {
+ self.disable_tool_buttons(false);
+
+ if (e.readyState == 0) {
+ alertify.alert('Cancel Query Error',
+ '{{ _('Not connected to server Or connection with the server has been closed.') }}'
+ );
+ return;
+ }
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ alertify.alert('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) {
+ if (e.readyState == 0) {
+ alertify.alert('Get Object Name Error',
+ '{{ _('Not connected to server Or connection with the server has been closed.') }}'
+ );
+ return;
+ }
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ alertify.alert('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');
+ }
+ },
+
+ _auto_rollback: function() {
+ var self = this;
+ auto_rollback = true;
+
+ if ($('.auto-rollback').hasClass('visibility-hidden') === true)
+ $('.auto-rollback').removeClass('visibility-hidden');
+ else {
+ $('.auto-rollback').addClass('visibility-hidden');
+ auto_rollback = false;
+ }
+
+ // Make ajax call to change the limit
+ $.ajax({
+ url: "{{ url_for('sqleditor.index') }}" + "auto_rollback/" + self.transId,
+ method: 'POST',
+ async: false,
+ contentType: "application/json",
+ data: JSON.stringify(auto_rollback),
+ success: function(res) {
+ if (!res.data.status)
+ alertify.alert('Auto Rollback Error', res.data.result);
+ },
+ error: function(e) {
+ if (e.readyState == 0) {
+ alertify.alert('Auto Rollback Error',
+ '{{ _('Not connected to server Or connection with the server has been closed.') }}'
+ );
+ return;
+ }
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ alertify.alert('Auto Rollback Error', msg);
+ }
+ });
+ },
+
+ _auto_commit: function() {
+ var self = this,
+ auto_commit = true;
+
+ if ($('.auto-commit').hasClass('visibility-hidden') === true)
+ $('.auto-commit').removeClass('visibility-hidden');
+ else {
+ $('.auto-commit').addClass('visibility-hidden');
+ auto_commit = false;
+ }
+
+ // Make ajax call to change the limit
+ $.ajax({
+ url: "{{ url_for('sqleditor.index') }}" + "auto_commit/" + self.transId,
+ method: 'POST',
+ async: false,
+ contentType: "application/json",
+ data: JSON.stringify(auto_commit),
+ success: function(res) {
+ if (!res.data.status)
+ alertify.alert('Auto Commit Error', res.data.result);
+ },
+ error: function(e) {
+ if (e.readyState == 0) {
+ alertify.alert('Auto Commit Error',
+ '{{ _('Not connected to server Or connection with the server has been closed.') }}'
+ );
+ return;
+ }
+
+ var msg = e.responseText;
+ if (e.responseJSON != undefined &&
+ e.responseJSON.errormsg != undefined)
+ msg = e.responseJSON.errormsg;
+
+ alertify.alert('Auto Commit Error', msg);
+ }
+ });
+ }
+ }
+ );
+
+ 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..7c17616
--- /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..16ad5b8
--- /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..8b96576
--- /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..8ee3895
--- /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
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], [email protected]
Subject: Re: [pgAdmin4] [Patch] Implementation of the Data Grid and Query Tool
In-Reply-To: <CANxoLDe-pk8=s0JkW=9ccr5LBx-Fyh-vrPQ-hqLpYYjqfoUycw@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