public inbox for [email protected]help / color / mirror / Atom feed
[pgAdmin4]: Initial patch for Import table 3+ messages / 2 participants [nested] [flat]
* [pgAdmin4]: Initial patch for Import table @ 2016-05-05 11:13 Neel Patel <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Neel Patel @ 2016-05-05 11:13 UTC (permalink / raw) To: pgadmin-hackers Hi, Please find attached patch file which contains the table Import functionality. Attached patch file is depend on the following patch which is not committed yet. - File Manager patch - Table node patch Do review it and let us know for comments. Thanks, Neel Patel -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [application/octet-stream] import_v1.patch (22.1K, 3-import_v1.patch) download | inline diff: diff --git a/web/pgadmin/static/css/overrides.css b/web/pgadmin/static/css/overrides.css index 1927ff1..4a98069 100755 --- a/web/pgadmin/static/css/overrides.css +++ b/web/pgadmin/static/css/overrides.css @@ -1101,3 +1101,11 @@ button.pg-alertify-button { div.backform_control_notes label.control-label { min-width: 0px; } + +.alertify_tools_dialog_properties { + bottom: 0 !important; + left: 0 !important; + position: absolute !important; + right: 0 !important; + top: 35px !important; +} diff --git a/web/pgadmin/tools/import/__init__.py b/web/pgadmin/tools/import/__init__.py new file mode 100644 index 0000000..a53e8ec --- /dev/null +++ b/web/pgadmin/tools/import/__init__.py @@ -0,0 +1,230 @@ +########################################################################## +# +# 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 import functionality""" + +MODULE_NAME = 'import' + +import json +from random import randint +from flask import url_for, Response, render_template, request, make_response, jsonify, current_app +from pgadmin.utils import PgAdminModule +from pgadmin.utils.ajax import bad_request +from flask.ext.babel import gettext +from flask.ext.security import login_required +from pgadmin.utils.driver import get_driver +from config import PG_DEFAULT_DRIVER +from pgadmin.utils.ajax import make_json_response, \ + make_response as ajax_response, internal_server_error +from pgadmin.model import db, Server, Jobs +from pgadmin.utils.preferences import Preferences + +class ImportModule(PgAdminModule): + """ + class ImportModule(PgAdminModule) + + A module class for import which is derived from PgAdminModule. + + Methods: + ------- + * get_own_javascripts(self) + - Method is used to load the required javascript files for debugger module + + * get_pg_utility_dir_preference(self) + - Method is used to get the PG utilities path set in preferences. + + * get_edb_utility_dir_preference(self) + - Method is used to get the EDB utilities path set in preferences. + + * register_preferences(self) + - Register the storage dir, PG and EDB utilities preferences. + + """ + + LABEL = gettext('Utilities') + + def get_own_javascripts(self): + scripts = list() + for name, script in [ + ['pgadmin.tools.import', 'js/import'] + ]: + scripts.append({ + 'name': name, + 'path': url_for('import.index') + script, + 'when': None + }) + + return scripts + + def get_pg_utility_dir_preference(self): + return self.pg_utility_dir + + def get_edb_utility_dir_preference(self): + return self.edb_utility_dir + + def register_preferences(self): + """ + Get storage directory preference + """ + self.storage_directory = Preferences.module('file_manager') + self.storage_dir = self.storage_directory.preference( + 'storage_dir' + ) + + # Register 'PG specific utility binary directory' preference + self.pg_utility_dir = self.preference.register( + 'options', 'pg_utilities_bin_dir', + gettext("PG bin path"), 'text', '/', + category_label=gettext('BIN path') + ) + + # Register 'EDB specific utility binary directory' preference + self.edb_utility_dir = self.preference.register( + 'options', 'edb_utilities_bin_dir', + gettext("EDB bin path"), 'text', '/', + category_label=gettext('BIN path') + ) + + +blueprint = ImportModule(MODULE_NAME, __name__) + + [email protected]("/") +@login_required +def index(): + return bad_request(errormsg=gettext("This URL can not be called directly!")) + + [email protected]("/js/import.js") +@login_required +def script(): + """render the import javascript file""" + return Response(response=render_template("import/js/import.js", _=gettext), + status=200, + mimetype="application/javascript") + + [email protected]('/create_job/<int:sid>', methods=['POST']) +@login_required +def create_import_job(sid): + """ + Args: + sid: Server ID + + Creates a new job for import + + Returns: + None + """ + if request.form: + # Convert ImmutableDict to dict + data = dict(request.form) + data = json.loads(data['data'][0]) + else: + data = json.loads(request.data.decode()) + + # Fetch the server details like hostname, port, roles etc + server = Server.query.filter_by( + id=sid).first() + + if server is None: + return make_json_response( + success=0, + errormsg=gettext("Couldn't find the given server") + ) + + # To fetch MetaData for the server + manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(server.id) + conn = manager.connection() + connected = conn.connected() + + if not connected: + return make_json_response( + success=0, + errormsg=gettext("Please connect to the server first...") + ) + + # Set psql binary path from preference + if manager.server_type == 'ppas': + # EDB specific backup tool + pgsql_utility_dir = blueprint.get_edb_utility_dir_preference().get() + else: + pgsql_utility_dir = blueprint.get_pg_utility_dir_preference().get() + + pgsql_utility_dir += 'psql' + + # Get the storage path from preference + str_dir = blueprint.storage_dir.get() + if 'filename' in data: + data['filename'] = str_dir + data['filename']; + else: + return make_json_response( + data={'status': False, 'info': 'Please specify a valid file'} + ) + + ignore_column_list = '' + column_list_import = '' + + if data['ignore_column']: + new_ignore_col_list = json.loads(data['ignore_column']) + + # format the ignore column list required as per copy command requirement + if new_ignore_col_list: + ignore_column_list = '(' + ignore_col_length = len(new_ignore_col_list) + for i in range(ignore_col_length): + ignore_column_list += new_ignore_col_list[i] + if i != (ignore_col_length - 1): + ignore_column_list += ',' + ignore_column_list += ')' + + # format the column import list required as per copy command requirement + if data['column_import']: + column_list_import = '(' + import_col_length = len(data['column_import']) + for indexCnt in range(import_col_length): + column_list_import += data['column_import'][indexCnt] + if indexCnt != (import_col_length - 1): + column_list_import += ',' + column_list_import += ')' + + # Fetch arguments from template + arguments = render_template( + 'import/arguments/import.args', + conn = conn, + server=server, + data=data, + column_list_import = column_list_import, + ignore_column_list = ignore_column_list + ) + + try: + # Generate random job id + jid = randint(1001, 9999) + create_job = Jobs( + job_id=jid, + command=pgsql_utility_dir, + arguments=arguments + ) + # Create the job execution string and save to database + db.session.add(create_job) + db.session.commit() + + except Exception as e: + current_app.logger.exception(e) + return make_json_response( + status=410, + success=0, + errormsg=str(e) + ) + + # Return response + return make_json_response( + data={'job_id': jid, 'status': True, 'info': 'Import job created'} + ) diff --git a/web/pgadmin/tools/import/templates/import/arguments/import.args b/web/pgadmin/tools/import/templates/import/arguments/import.args new file mode 100644 index 0000000..1d68887 --- /dev/null +++ b/web/pgadmin/tools/import/templates/import/arguments/import.args @@ -0,0 +1 @@ +-h {{ conn|qtIdent(server.host) }} -p {{ server.port }} -U {{ conn|qtIdent(server.username) }} {{ conn|qtIdent(data.database) }} -c "COPY {{ conn|qtIdent(data.schema) }}.{{ conn|qtIdent(data.table) }} {% if column_list_import %} {{ column_list_import }} {% endif %} FROM {{ data.filename|qtLiteral }} {% if data.oid %} OIDS {% endif %}{% if data.delimiter and data.delimiter == '[tab]' %} WITH DELIMITER E'\\t' {% elif data.delimiter %} WITH DELIMITER {{ data.delimiter|qtLiteral }}{% endif %}{% if data.format == 'csv' %} CSV HEADER {% endif %}{% if data.encoding %} ENCODING {{ data.encoding|qtLiteral }}{% endif %}{% if data.quote %} QUOTE {{ data.quote|qtLiteral }}{% endif %}{% if data.null_string %} NULL {{ data.null_string|qtLiteral }}{% endif %}{% if data.escape %} ESCAPE {{ data.escape|qtLiteral }}{% endif %}{% if data.format == 'csv' and ignore_column_list %} FORCE_NOT_NULL {{ ignore_column_list }} {% endif %};" \ No newline at end of file diff --git a/web/pgadmin/tools/import/templates/import/js/import.js b/web/pgadmin/tools/import/templates/import/js/import.js new file mode 100644 index 0000000..67d516f --- /dev/null +++ b/web/pgadmin/tools/import/templates/import/js/import.js @@ -0,0 +1,355 @@ +define( + ['jquery', 'underscore', 'underscore.string', 'alertify', 'pgadmin', + 'pgadmin.browser', 'backbone', 'backgrid', 'backform', + 'pgadmin.backform', 'pgadmin.backgrid', 'pgadmin.browser.node.ui'], + function($, _, S, Alertify, pgAdmin, pgBrowser, Backbone, Backgrid, Backform) { + + pgAdmin = pgAdmin || window.pgAdmin || {}; + + var pgTools = pgAdmin.Tools = pgAdmin.Tools || {}; + + // Return back, this has been called more than once + if (pgAdmin.Tools.import_utility) + return pgAdmin.Tools.import_utility; + + // Main model for Import functionality + var ImportModel = Backbone.Model.extend({ + defaults: { + filename: undefined, + format: 'text', + encoding: undefined, + oid: undefined, + header: undefined, + delimiter: undefined, + quote: undefined, + escape: undefined, + null_string: undefined, + column_import: [], + ignore_column: [], + database: undefined, + schema: undefined, + table: undefined + }, + schema: [ + { + id: 'filename', label: '{{ _('Filename')}}', + type: 'text', control: Backform.FileControl, group: '{{ _('Files')}}', + dialog_type: 'select_file', supp_types: ['*', 'txt'] + }, + { + id: 'format', label: '{{ _("Format") }}', cell: 'string', + control: 'select2', group: '{{ _('Files')}}', + options:[ + {'label': 'text', 'value': 'text'}, + {'label': 'csv', 'value': 'csv'}, + {'label': 'binary', 'value': 'binary'}, + ], + disabled: 'isDisabled', select2: {allowClear: false, width: "100%" }, + }, + { + id: 'encoding', label: '{{ _("Encoding") }}', cell: 'string', + control: 'node-ajax-options', node: 'database', url: 'get_encodings', first_empty: true, + group: '{{ _('Files')}}' + }, + { + id: 'column_import', label: '{{ _("Columns to import") }}', cell: 'string', + type: 'array', control: Backform.MultiSelectAjaxControl.extend({ + // By default, all the import columns should be selected + initialize: function() { + Backform.MultiSelectAjaxControl.prototype.initialize.apply(this, arguments); + var self = this, + options = self.field.get('options'), + op_vals = []; + if (_.isFunction(options)) { + try { + options = options.apply(self) + } catch(e) { + // Do nothing + options = []; + } + } + _.each(options, function(op){ + op_vals.push(op['value']); + }); + + self.model.set(self.field.get('name'),op_vals); + } + }), + node: 'column', url: 'nodes', group: '{{ _('Columns')}}', + transform: function(rows) { + var self = this, + node = self.field.get('schema_node'), + res = []; + + _.each(rows, function(r) { + var l = (_.isFunction(node['node_label']) ? + (node['node_label']).apply(node, [r, self.model, self]) : + r.label), + image = (_.isFunction(node['node_image']) ? + (node['node_image']).apply( + node, [r, self.model, self] + ) : + (node['node_image'] || ('icon-' + node.type))); + res.push({ + 'value': r.label, + 'image': image, + 'label': l + }); + }); + + return res; + }, + select2: { multiple: true, allowClear: true, placeholder: '{{ _('Select columns to import...') }}'}, + }, + { + id: 'null_string', label: '{{ _("NULL Strings") }}', cell: 'string', + type: 'text', group: '{{ _('Columns')}}', disabled: 'isDisabled', deps: ['format'] + }, + { + id: 'ignore_column', label: '{{ _("Ignore Columns") }}', cell: 'string', + control: 'node-list-by-name', node: 'column', + group: '{{ _('Columns')}}', deps: ['format'], disabled: 'isDisabled', + select2: { multiple: true, allowClear: true, placeholder: '{{ _('Select columns to ignore...') }}'}, + }, + { + type: 'nested', control: 'fieldset', label: '{{ _('Miscellaneous') }}', + group: '{{ _('Options') }}', + schema:[{ + id: 'oid', label:'{{ _('OID') }}', cell: 'string', + type: 'switch', group: '{{ _('Miscellaneous') }}' + },{ + id: 'header', label:'{{ _('Header') }}', cell: 'string', + type: 'switch', group: '{{ _('Miscellaneous') }}', deps: ['format'], disabled: 'isDisabled' + },{ + id: 'delimiter', label:'{{ _('Delimiter') }}', cell: 'string', first_empty: true, deps: ['format'], + type: 'text', control: 'node-ajax-options', group: '{{ _('Miscellaneous') }}', disabled: 'isDisabled', + options:[ + + {'label': ';', 'value': ';'}, + {'label': ',', 'value': ','}, + {'label': '|', 'value': '|'}, + {'label': '[tab]', 'value': '[tab]'}, + + ], + select2: { + allowClear: false, + width: "100%", + placeholder: '{{ _('Select from list...') }}' + }, + }] + }, + { + type: 'nested', control: 'fieldset', label: '{{ _('Quote') }}', + group: '{{ _('Options') }}', + schema:[{ + id: 'quote', label:'{{ _('Quote') }}', cell: 'string', + type: 'text', control: 'node-ajax-options', group: '{{ _('Quote') }}', + disabled: 'isDisabled', deps: ['format'], first_empty: true, + options:[ + {'label': '\"', 'value': '\"'}, + {'label': '\'', 'value': '\''}, + ], + select2: { + allowClear: false, + width: "100%", + placeholder: '{{ _('Select from list...') }}' + }, + },{ + id: 'escape', label:'{{ _('Escape') }}', cell: 'string', + type: 'text', control: 'node-ajax-options', group: '{{ _('Quote') }}', + disabled: 'isDisabled', deps: ['format'], first_empty: true, + options:[ + {'label': '\"', 'value': '\"'}, + {'label': '\'', 'value': '\''}, + ], + select2: { + allowClear: false, + width: "100%", + placeholder: '{{ _('Select from list...') }}' + }, + }] + } + ], + + // Enable/Disable the items based on the user file format selection + isDisabled: function(m) { + name = this.name; + switch(name) { + case 'quote': + case 'escape': + case 'header': + case 'ignore_column': + if (m.get('format') != 'csv') { + return true; + } + else { + return false; + } + break; + case 'null_string': + case 'delimiter': + if (m.get('format') == 'binary') { + return true; + } + else { + return false; + } + break; + default: + return false; + } + return false; + }, + }); + + pgTools.import_utility = { + init: function() { + // We do not want to initialize the module multiple times. + if (this.initialized) + return; + + this.initialized = true; + + /** + Enable/disable import menu in tools based on node selected + Import menu will be enabled only when user select table node. + */ + menu_enabled = function(itemData, item, data) { + var t = pgBrowser.tree, i = item, d = itemData; + var parent_item = t.hasParent(i) ? t.parent(i): null, + parent_data = parent_item ? t.itemData(parent_item) : null; + if(!_.isUndefined(d) && !_.isNull(d) && !_.isNull(parent_data)) + return ( + (_.indexOf(['table'], d._type) !== -1 && + parent_data._type != 'catalog') ? true: false + ); + else + return false; + }; + + // Initialize the context menu to display the import options when user open the context menu for table + pgBrowser.add_menus([{ + name: 'import', node: 'table', module: this, + applies: ['tools', 'context'], callback: 'callback_import', + category: 'import', priority: 10, label: '{{ _('Import...') }}', + data: {object: 'table'}, icon: 'fa fa-sign-in', enable: menu_enabled + } + ]); + }, + + /* + Open the dialog for the import functionality + */ + callback_import: function(args, item) { + var self = this; + var input = args || {}, + t = pgBrowser.tree, + i = item || t.selected(), + d = i && i.length == 1 ? t.itemData(i) : undefined, + node = d && pgBrowser.Nodes[d._type]; + + if (!d) + return; + + var objName = d.label; + var treeInfo = node.getTreeNodeHierarchy.apply(node, [i]); + + if (!Alertify.ImportDialog) { + Alertify.dialog('ImportDialog', function factory() { + + return { + main:function(title) { + this.set('title', title); + }, + setup:function() { + return { + buttons:[{ text: "{{ _('Import') }}", key: 27, className: "btn btn-primary fa fa-lg fa-save pg-alertify-button" }, + { text: "{{ _('Cancel') }}", key: 27, className: "btn btn-danger fa fa-lg fa-times pg-alertify-button" }], + options: { modal: 0} + }; + }, + // Callback functions when click on the buttons of the Alertify dialogs + callback: function(e) { + if (e.button.text === "{{ _('Import') }}") { + + this.view.model.set({'database': treeInfo.database.label, + 'schema': treeInfo.schema.label, + 'table': treeInfo.table.label}) + var self = this, + baseUrl = "{{ url_for('import.index') }}" + + "create_job/" + treeInfo.server._id, + args = this.view.model.toJSON(); + + $.ajax({ + url: baseUrl, + method: 'POST', + data:{ 'data': JSON.stringify(args) }, + success: function(res) { + if (res.data.status) { + //Do nothing as we are creating the job and exiting from the main dialog + Alertify.success(res.data.info); + } + else { + Alertify.error(res.data.info); + } + }, + error: function(e) { + Alertify.alert( + "{{ _('Import job creation failed') }}" + ); + } + }); + } + }, + build:function() { + + }, + hooks: { + onclose: function() { + if (this.view) { + this.view.remove({data: true, internal: true, silent: true}); + } + } + }, + prepare:function() { + // Main import module container + var $container = $("<div class='import_dlg'></div>"); + + var t = pgBrowser.tree, + i = t.selected(), + d = i && i.length == 1 ? t.itemData(i) : undefined, + node = d && pgBrowser.Nodes[d._type]; + + if (!d) + return; + + var treeInfo = node.getTreeNodeHierarchy.apply(node, [i]); + + var newModel = new ImportModel ( + {}, {node_info: treeInfo} + ), + fields = Backform.generateViewSchema( + treeInfo, newModel, 'create', node, treeInfo.server, true + ); + + var view = this.view = new Backform.Dialog({ + el: $container, model: newModel, schema: fields + }); + + $(this.elements.body.childNodes[0]).addClass('alertify_tools_dialog_properties obj_properties'); + + view.render(); + + this.elements.content.appendChild($container.get(0)); + } + }; + }); + } + + // Open the Alertify dialog for the import module + Alertify.ImportDialog('Import data from file into ' + treeInfo.table.label).set('resizable',true).resizeTo('60%','70%'); + }, + }; + + return pgAdmin.Tools.import_utility; + }); ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [pgAdmin4]: Initial patch for Import table @ 2016-05-16 07:18 Ashesh Vashi <[email protected]> parent: Neel Patel <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Ashesh Vashi @ 2016-05-16 07:18 UTC (permalink / raw) To: Neel Patel <[email protected]>; +Cc: pgadmin-hackers Hi Neel, As discussed, I am sending you the updated path for based on your new patch (sent personally, which included the background process integration). Also, the COPY Statement expects the file to be present on the server OS, hence - we should be using the '\copy' command of psql to do the job. I have modified the patch accordingly, but - I did not get a chance to test it properly, also - with some cosmetic changes. Can you please review, and test it? -- Thanks & Regards, Ashesh Vashi EnterpriseDB INDIA: Enterprise PostgreSQL Company <http://www.enterprisedb.com; *http://www.linkedin.com/in/asheshvashi* <http://www.linkedin.com/in/asheshvashi; On Thu, May 5, 2016 at 4:43 PM, Neel Patel <[email protected]> wrote: > Hi, > > Please find attached patch file which contains the table Import > functionality. > Attached patch file is depend on the following patch which is not > committed yet. > > - File Manager patch > - Table node patch > > Do review it and let us know for comments. > > Thanks, > Neel Patel > > > -- > Sent via pgadmin-hackers mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgadmin-hackers > > -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [application/octet-stream] import_latest_16May2016.patch (22.5K, 3-import_latest_16May2016.patch) download | inline diff: diff --git a/web/pgadmin/tools/import/__init__.py b/web/pgadmin/tools/import/__init__.py new file mode 100644 index 0000000..347274b --- /dev/null +++ b/web/pgadmin/tools/import/__init__.py @@ -0,0 +1,277 @@ +########################################################################## +# +# 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 import functionality""" + +import json +import os + +from flask import url_for, Response, render_template, request, current_app +from flask.ext.babel import gettext as _ +from flask.ext.security import login_required, current_user + +from config import PG_DEFAULT_DRIVER +from pgadmin.utils import PgAdminModule, get_storage_directory, html +from pgadmin.utils.ajax import make_json_response, bad_request +from pgadmin.model import Server +from pgadmin.misc.bgprocess.processes import BatchProcess, IProcessDesc + +MODULE_NAME = 'import' + + +class ImportModule(PgAdminModule): + """ + class ImportModule(PgAdminModule) + + A module class for import which is derived from PgAdminModule. + + Methods: + ------- + * get_own_javascripts(self) + - Method is used to load the required javascript files for import module + """ + + LABEL = _('Import') + + def get_own_javascripts(self): + scripts = list() + for name, script in [ + ['pgadmin.tools.import', 'js/import'] + ]: + scripts.append({ + 'name': name, + 'path': url_for('import.index') + script, + 'when': None + }) + + return scripts + + +blueprint = ImportModule(MODULE_NAME, __name__) + + +class ImportMessage(IProcessDesc): + """ + ImportMessage(IProcessDesc) + + Defines the message shown for the ImportMessage operation. + """ + def __init__(self, _sid, _schema, _tbl, _storage): + self.sid = _sid + self.schema = _schema + self.table = _tbl + self.storage = _storage + + @property + def message(self): + # Fetch the server details like hostname, port, roles etc + s = Server.query.filter_by( + id=self.sid, user_id=current_user.id + ).first() + + return _( + "Importing table - '{0}.{1}' on database '{2}' and server ({3}{4})..." + ).format( + self.schema, self.table, self.database, s.host, s.port + ) + + def details(self, cmd, args): + # Fetch the server details like hostname, port, roles etc + s = Server.query.filter_by( + id=self.sid, user_id=current_user.id + ).first() + + res = '<div class="h5">' + res += html.safe_str( + _( + "Importing data for the table '{0}.{1}' on database '{2}' for the server - '{3}'" + ).format( + self.schema, self.table, self.database, + "{0} ({1}:{2})".format(s.name, s.host, s.port) + ) + ) + + res += '</div><div class="h5">' + res += html.safe_str( + _("Running command:") + ) + res += '</b><br><i>' + res += html.safe_str(cmd) + + replace_next = False + + def cmdArg(x): + if x: + x = x.replace('\\', '\\\\') + x = x.replace('"', '\\"') + x = x.replace('""', '\\"') + + return ' "' + html.safe_str(x) + '"' + + return '' + + for arg in args: + if arg and len(arg) >= 2 and arg[:2] == '--': + res += ' ' + arg + elif replace_next: + if self.storage: + arg = arg.replace(self.storage, '') + res += ' "' + html.safe_str(arg) + '"' + else: + if arg == '--command': + replace_next = True + res += cmdArg(arg) + res += '</i></div>' + + return res + + [email protected]("/") +@login_required +def index(): + return bad_request(errormsg=_("This URL can not be called directly!")) + + [email protected]("/js/import.js") +@login_required +def script(): + """render the import javascript file""" + return Response(response=render_template("import/js/import.js", _=_), + status=200, + mimetype="application/javascript") + + [email protected]('/create_job/<int:sid>', methods=['POST']) +@login_required +def create_import_job(sid): + """ + Args: + sid: Server ID + + Creates a new job for import + + Returns: + None + """ + if request.form: + # Convert ImmutableDict to dict + data = dict(request.form) + data = json.loads(data['data'][0]) + else: + data = json.loads(request.data.decode()) + + # Fetch the server details like hostname, port, roles etc + server = Server.query.filter_by( + id=sid).first() + + if server is None: + return make_json_response( + success=0, + errormsg=_("Couldn't find the given server") + ) + + # To fetch MetaData for the server + from pgadmin.utils.driver import get_driver + driver = get_driver(PG_DEFAULT_DRIVER) + manager = driver.connection_manager(server.id) + conn = manager.connection() + connected = conn.connected() + + if not connected: + return make_json_response( + success=0, + errormsg=_("Please connect to the server first...") + ) + + utility = manager.utility('sql') + + # Get the storage path from preference + storage_dir = get_storage_directory() + if 'filename' in data: + if os.name == 'nt': + data['filename'] = data['filename'].replace('/', '\\') + if storage_dir: + storage_dir = storage_dir.replace('/', '\\') + data['filename'] = data['filename'].replace('\\', '\\\\') + data['filename'] = os.path.join(storage_dir, data['filename']) + else: + data['filename'] = os.path.join(storage_dir, data['filename']) + else: + return make_json_response( + data={'status': False, 'info': 'Please specify a valid file'} + ) + + ignore_column_list = '' + column_list_import = '' + + if data['ignore_column']: + new_ignore_col_list = json.loads(data['ignore_column']) + + # format the ignore column list required as per copy command + # requirement + if new_ignore_col_list: + ignore_column_list = '(' + ignore_col_length = len(new_ignore_col_list) + for i in range(ignore_col_length): + ignore_column_list += new_ignore_col_list[i] + if i != (ignore_col_length - 1): + ignore_column_list += ',' + ignore_column_list += ')' + + # format the column import list required as per copy command requirement + if data['column_import']: + column_list_import = '(' + import_col_length = len(data['column_import']) + for indexCnt in range(import_col_length): + column_list_import += data['column_import'][indexCnt] + if indexCnt != (import_col_length - 1): + column_list_import += ',' + column_list_import += ')' + + # Fetch arguments from template + arguments = render_template( + 'import/arguments/import.args', + conn=conn, + data=data, + column_list_import=column_list_import, + ignore_column_list=ignore_column_list + ) + + args = [ + '--host', server.host, '--port', str(server.port), + '--username', server.username, '--dbname', + driver.qtIdent(conn, data['database']), + '--command', arguments + ] + + try: + p = BatchProcess( + desc=ImportMessage( + sid, + data['schema'], + data['table'], + storage_dir + ), + cmd=utility, args=args + ) + manager.export_password_env(sid, str(p.id)) + p.start() + jid = p.id + except Exception as e: + current_app.logger.exception(e) + return make_json_response( + status=410, + success=0, + errormsg=str(e) + ) + + # Return response + return make_json_response( + data={'job_id': jid, 'success': 1} + ) diff --git a/web/pgadmin/tools/import/templates/import/arguments/import.args b/web/pgadmin/tools/import/templates/import/arguments/import.args new file mode 100644 index 0000000..6cb809a --- /dev/null +++ b/web/pgadmin/tools/import/templates/import/arguments/import.args @@ -0,0 +1 @@ +\copy {{ conn|qtIdent(data.schema, data.table) }} {% if column_list_import %} {{ column_list_import }} {% endif %} FROM E{{ data.filename|qtLiteral }} {% if data.oid %} OIDS {% endif %}{% if data.delimiter and data.delimiter == '[tab]' %} DELIMITER E'\\t' {% elif data.delimiter %} DELIMITER {{ data.delimiter|qtLiteral }}{% endif %}{% if data.format == 'csv' %} CSV HEADER {% endif %}{% if data.encoding %} ENCODING {{ data.encoding|qtLiteral }}{% endif %}{% if data.quote %} QUOTE {{ data.quote|qtLiteral }}{% endif %}{% if data.null_string %} NULL {{ data.null_string|qtLiteral }}{% endif %}{% if data.escape %} ESCAPE {{ data.escape|qtLiteral }}{% endif %}{% if data.format == 'csv' and ignore_column_list %} FORCE_NOT_NULL {{ ignore_column_list }} {% endif %};" diff --git a/web/pgadmin/tools/import/templates/import/js/import.js b/web/pgadmin/tools/import/templates/import/js/import.js new file mode 100644 index 0000000..84b5aa5 --- /dev/null +++ b/web/pgadmin/tools/import/templates/import/js/import.js @@ -0,0 +1,356 @@ +define( + ['jquery', 'underscore', 'underscore.string', 'alertify', 'pgadmin', + 'pgadmin.browser', 'backbone', 'backgrid', 'backform', + 'pgadmin.backform', 'pgadmin.backgrid', 'pgadmin.browser.node.ui'], + function($, _, S, Alertify, pgAdmin, pgBrowser, Backbone, Backgrid, Backform) { + + pgAdmin = pgAdmin || window.pgAdmin || {}; + + var pgTools = pgAdmin.Tools = pgAdmin.Tools || {}; + + // Return back, this has been called more than once + if (pgAdmin.Tools.import_utility) + return pgAdmin.Tools.import_utility; + + // Main model for Import functionality + var ImportModel = Backbone.Model.extend({ + defaults: { + filename: undefined, + format: 'text', + encoding: undefined, + oid: undefined, + header: undefined, + delimiter: undefined, + quote: undefined, + escape: undefined, + null_string: undefined, + column_import: [], + ignore_column: [], + database: undefined, + schema: undefined, + table: undefined + }, + schema: [ + { + id: 'filename', label: '{{ _('Filename')}}', + type: 'text', control: Backform.FileControl, group: '{{ _('Files')}}', + dialog_type: 'select_file', supp_types: ['*', 'txt'] + }, + { + id: 'format', label: '{{ _("Format") }}', cell: 'string', + control: 'select2', group: '{{ _('Files')}}', + options:[ + {'label': 'text', 'value': 'text'}, + {'label': 'csv', 'value': 'csv'}, + {'label': 'binary', 'value': 'binary'}, + ], + disabled: 'isDisabled', select2: {allowClear: false, width: "100%" }, + }, + { + id: 'encoding', label: '{{ _("Encoding") }}', cell: 'string', + control: 'node-ajax-options', node: 'database', url: 'get_encodings', first_empty: true, + group: '{{ _('Files')}}' + }, + { + id: 'column_import', label: '{{ _("Columns to import") }}', cell: 'string', + type: 'array', control: Backform.MultiSelectAjaxControl.extend({ + // By default, all the import columns should be selected + initialize: function() { + Backform.MultiSelectAjaxControl.prototype.initialize.apply(this, arguments); + var self = this, + options = self.field.get('options'), + op_vals = []; + if (_.isFunction(options)) { + try { + options = options.apply(self) + } catch(e) { + // Do nothing + options = []; + } + } + _.each(options, function(op){ + op_vals.push(op['value']); + }); + + self.model.set(self.field.get('name'),op_vals); + } + }), + node: 'column', url: 'nodes', group: '{{ _('Columns')}}', + transform: function(rows) { + var self = this, + node = self.field.get('schema_node'), + res = []; + + _.each(rows, function(r) { + var l = (_.isFunction(node['node_label']) ? + (node['node_label']).apply(node, [r, self.model, self]) : + r.label), + image = (_.isFunction(node['node_image']) ? + (node['node_image']).apply( + node, [r, self.model, self] + ) : + (node['node_image'] || ('icon-' + node.type))); + res.push({ + 'value': r.label, + 'image': image, + 'label': l + }); + }); + + return res; + }, + select2: { multiple: true, allowClear: true, placeholder: '{{ _('Select columns to import...') }}'}, + }, + { + id: 'null_string', label: '{{ _("NULL Strings") }}', cell: 'string', + type: 'text', group: '{{ _('Columns')}}', disabled: 'isDisabled', deps: ['format'] + }, + { + id: 'ignore_column', label: '{{ _("Ignore Columns") }}', cell: 'string', + control: 'node-list-by-name', node: 'column', + group: '{{ _('Columns')}}', deps: ['format'], disabled: 'isDisabled', + select2: { multiple: true, allowClear: true, placeholder: '{{ _('Select columns to ignore...') }}'}, + }, + { + type: 'nested', control: 'fieldset', label: '{{ _('Miscellaneous') }}', + group: '{{ _('Options') }}', + schema:[{ + id: 'oid', label:'{{ _('OID') }}', cell: 'string', + type: 'switch', group: '{{ _('Miscellaneous') }}' + },{ + id: 'header', label:'{{ _('Header') }}', cell: 'string', + type: 'switch', group: '{{ _('Miscellaneous') }}', deps: ['format'], disabled: 'isDisabled' + },{ + id: 'delimiter', label:'{{ _('Delimiter') }}', cell: 'string', first_empty: true, deps: ['format'], + type: 'text', control: 'node-ajax-options', group: '{{ _('Miscellaneous') }}', disabled: 'isDisabled', + options:[ + + {'label': ';', 'value': ';'}, + {'label': ',', 'value': ','}, + {'label': '|', 'value': '|'}, + {'label': '[tab]', 'value': '[tab]'}, + + ], + select2: { + allowClear: false, + width: "100%", + placeholder: '{{ _('Select from list...') }}' + }, + }] + }, + { + type: 'nested', control: 'fieldset', label: '{{ _('Quote') }}', + group: '{{ _('Options') }}', + schema:[{ + id: 'quote', label:'{{ _('Quote') }}', cell: 'string', + type: 'text', control: 'node-ajax-options', group: '{{ _('Quote') }}', + disabled: 'isDisabled', deps: ['format'], first_empty: true, + options:[ + {'label': '\"', 'value': '\"'}, + {'label': '\'', 'value': '\''}, + ], + select2: { + allowClear: false, + width: "100%", + placeholder: '{{ _('Select from list...') }}' + }, + },{ + id: 'escape', label:'{{ _('Escape') }}', cell: 'string', + type: 'text', control: 'node-ajax-options', group: '{{ _('Quote') }}', + disabled: 'isDisabled', deps: ['format'], first_empty: true, + options:[ + {'label': '\"', 'value': '\"'}, + {'label': '\'', 'value': '\''}, + ], + select2: { + allowClear: false, + width: "100%", + placeholder: '{{ _('Select from list...') }}' + }, + }] + } + ], + + // Enable/Disable the items based on the user file format selection + isDisabled: function(m) { + name = this.name; + switch(name) { + case 'quote': + case 'escape': + case 'header': + case 'ignore_column': + if (m.get('format') != 'csv') { + return true; + } + else { + return false; + } + break; + case 'null_string': + case 'delimiter': + if (m.get('format') == 'binary') { + return true; + } + else { + return false; + } + break; + default: + return false; + } + return false; + }, + }); + + pgTools.import_utility = { + init: function() { + // We do not want to initialize the module multiple times. + if (this.initialized) + return; + + this.initialized = true; + + /** + Enable/disable import menu in tools based on node selected + Import menu will be enabled only when user select table node. + */ + menu_enabled = function(itemData, item, data) { + var t = pgBrowser.tree, i = item, d = itemData; + var parent_item = t.hasParent(i) ? t.parent(i): null, + parent_data = parent_item ? t.itemData(parent_item) : null; + if(!_.isUndefined(d) && !_.isNull(d) && !_.isNull(parent_data)) + return ( + (_.indexOf(['table'], d._type) !== -1 && + parent_data._type != 'catalog') ? true: false + ); + else + return false; + }; + + // Initialize the context menu to display the import options when user open the context menu for table + pgBrowser.add_menus([{ + name: 'import', node: 'table', module: this, + applies: ['tools', 'context'], callback: 'callback_import', + category: 'import', priority: 10, label: '{{ _('Import...') }}', + data: {object: 'table'}, icon: 'fa fa-sign-in', enable: menu_enabled + } + ]); + }, + + /* + Open the dialog for the import functionality + */ + callback_import: function(args, item) { + var self = this; + var input = args || {}, + t = pgBrowser.tree, + i = item || t.selected(), + d = i && i.length == 1 ? t.itemData(i) : undefined, + node = d && pgBrowser.Nodes[d._type]; + + if (!d) + return; + + var objName = d.label; + var treeInfo = node.getTreeNodeHierarchy.apply(node, [i]); + + if (!Alertify.ImportDialog) { + Alertify.dialog('ImportDialog', function factory() { + + return { + main:function(title) { + this.set('title', title); + }, + setup:function() { + return { + buttons:[{ text: "{{ _('Import') }}", key: 27, className: "btn btn-primary fa fa-lg fa-save pg-alertify-button" }, + { text: "{{ _('Cancel') }}", key: 27, className: "btn btn-danger fa fa-lg fa-times pg-alertify-button" }], + options: { modal: 0} + }; + }, + // Callback functions when click on the buttons of the Alertify dialogs + callback: function(e) { + if (e.button.text === "{{ _('Import') }}") { + + this.view.model.set({'database': treeInfo.database.label, + 'schema': treeInfo.schema.label, + 'table': treeInfo.table.label}) + var self = this, + baseUrl = "{{ url_for('import.index') }}" + + "create_job/" + treeInfo.server._id, + args = this.view.model.toJSON(); + + $.ajax({ + url: baseUrl, + method: 'POST', + data:{ 'data': JSON.stringify(args) }, + success: function(res) { + if (res.success) { + Alertify.message('{{ _('Background process for taking import has been created!') }}', 1); + pgBrowser.Events.trigger('pgadmin-bgprocess:created', self); + } + }, + error: function(xhr, status, error) { + try { + var err = $.parseJSON(xhr.responseText); + Alertify.alert( + '{{ _('Import failed...') }}', + err.errormsg + ); + } catch (e) {} + } + }); + } + }, + build:function() { + + }, + hooks: { + onclose: function() { + if (this.view) { + this.view.remove({data: true, internal: true, silent: true}); + } + } + }, + prepare:function() { + // Main import module container + var $container = $("<div class='import_dlg'></div>"); + + var t = pgBrowser.tree, + i = t.selected(), + d = i && i.length == 1 ? t.itemData(i) : undefined, + node = d && pgBrowser.Nodes[d._type]; + + if (!d) + return; + + var treeInfo = node.getTreeNodeHierarchy.apply(node, [i]); + + var newModel = new ImportModel ( + {}, {node_info: treeInfo} + ), + fields = Backform.generateViewSchema( + treeInfo, newModel, 'create', node, treeInfo.server, true + ); + + var view = this.view = new Backform.Dialog({ + el: $container, model: newModel, schema: fields + }); + + $(this.elements.body.childNodes[0]).addClass('alertify_tools_dialog_properties obj_properties'); + + view.render(); + + this.elements.content.appendChild($container.get(0)); + } + }; + }); + } + + // Open the Alertify dialog for the import module + Alertify.ImportDialog('Import data from file into ' + treeInfo.table.label).set('resizable',true).resizeTo('60%','70%'); + }, + }; + + return pgAdmin.Tools.import_utility; + }); ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [pgAdmin4]: Initial patch for Import table @ 2016-05-17 07:11 Neel Patel <[email protected]> parent: Ashesh Vashi <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Neel Patel @ 2016-05-17 07:11 UTC (permalink / raw) To: Ashesh Vashi <[email protected]>; +Cc: pgadmin-hackers Hi, I have fixed some of the issues observed during testing and as per your feedback added "Export" functionality as well. I have tested it on both linux and windows and it is working. Please find attached updated patch. Do review it and let me know for any modifications. Thanks, Neel Patel On Mon, May 16, 2016 at 12:48 PM, Ashesh Vashi < [email protected]> wrote: > Hi Neel, > > As discussed, I am sending you the updated path for based on your new > patch (sent personally, which included the background process integration). > Also, the COPY Statement expects the file to be present on the server OS, > hence - we should be using the '\copy' command of psql to do the job. > I have modified the patch accordingly, but - I did not get a chance to > test it properly, also - with some cosmetic changes. > > Can you please review, and test it? > > > -- > > Thanks & Regards, > > Ashesh Vashi > EnterpriseDB INDIA: Enterprise PostgreSQL Company > <http://www.enterprisedb.com; > > > *http://www.linkedin.com/in/asheshvashi* > <http://www.linkedin.com/in/asheshvashi; > > On Thu, May 5, 2016 at 4:43 PM, Neel Patel <[email protected]> > wrote: > >> Hi, >> >> Please find attached patch file which contains the table Import >> functionality. >> Attached patch file is depend on the following patch which is not >> committed yet. >> >> - File Manager patch >> - Table node patch >> >> Do review it and let us know for comments. >> >> Thanks, >> Neel Patel >> >> >> -- >> Sent via pgadmin-hackers mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgadmin-hackers >> >> > -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [application/octet-stream] import_v3.patch (25.2K, 3-import_v3.patch) download | inline diff: diff --git a/web/pgadmin/tools/import/__init__.py b/web/pgadmin/tools/import/__init__.py new file mode 100644 index 0000000..318f56f --- /dev/null +++ b/web/pgadmin/tools/import/__init__.py @@ -0,0 +1,280 @@ +########################################################################## +# +# 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 import and export functionality""" + +import json +import os + +from flask import url_for, Response, render_template, request, current_app +from flask.ext.babel import gettext as _ +from flask.ext.security import login_required, current_user + +from config import PG_DEFAULT_DRIVER +from pgadmin.utils import PgAdminModule, get_storage_directory, html +from pgadmin.utils.ajax import make_json_response, bad_request +from pgadmin.model import Server +from pgadmin.misc.bgprocess.processes import BatchProcess, IProcessDesc + +MODULE_NAME = 'import' + + +class ImportExportModule(PgAdminModule): + """ + class ImportExportModule(PgAdminModule) + + A module class for import which is derived from PgAdminModule. + + Methods: + ------- + * get_own_javascripts(self) + - Method is used to load the required javascript files for import module + """ + + LABEL = _('Import') + + def get_own_javascripts(self): + scripts = list() + for name, script in [ + ['pgadmin.tools.import', 'js/import'] + ]: + scripts.append({ + 'name': name, + 'path': url_for('import.index') + script, + 'when': None + }) + + return scripts + + +blueprint = ImportExportModule(MODULE_NAME, __name__) + + +class Message(IProcessDesc): + """ + Message(IProcessDesc) + + Defines the message shown for the Message operation. + """ + def __init__(self, _sid, _schema, _tbl, _database, _storage): + self.sid = _sid + self.schema = _schema + self.table = _tbl + self.database = _database + self.storage = _storage + + @property + def message(self): + # Fetch the server details like hostname, port, roles etc + s = Server.query.filter_by( + id=self.sid, user_id=current_user.id + ).first() + + return _( + "Copying table data - '{0}.{1}' on database '{2}' and server ({3}{4})..." + ).format( + self.schema, self.table, self.database, s.host, s.port + ) + + def details(self, cmd, args): + # Fetch the server details like hostname, port, roles etc + s = Server.query.filter_by( + id=self.sid, user_id=current_user.id + ).first() + + res = '<div class="h5">' + res += html.safe_str( + _( + "Copying table data '{0}.{1}' on database '{2}' for the server - '{3}'" + ).format( + self.schema, self.table, self.database, + "{0} ({1}:{2})".format(s.name, s.host, s.port) + ) + ) + + res += '</div><div class="h5">' + res += html.safe_str( + _("Running command:") + ) + res += '</b><br><i>' + res += html.safe_str(cmd) + + replace_next = False + + def cmdArg(x): + if x: + x = x.replace('\\', '\\\\') + x = x.replace('"', '\\"') + x = x.replace('""', '\\"') + + return ' "' + html.safe_str(x) + '"' + + return '' + + for arg in args: + if arg and len(arg) >= 2 and arg[:2] == '--': + res += ' ' + arg + elif replace_next: + if self.storage: + arg = arg.replace(self.storage, '') + res += ' "' + html.safe_str(arg) + '"' + else: + if arg == '--command': + replace_next = True + res += cmdArg(arg) + res += '</i></div>' + + return res + + [email protected]("/") +@login_required +def index(): + return bad_request(errormsg=_("This URL can not be called directly!")) + + [email protected]("/js/import.js") +@login_required +def script(): + """render the import javascript file""" + return Response(response=render_template("import/js/import.js", _=_), + status=200, + mimetype="application/javascript") + + [email protected]('/create_job/<int:sid>', methods=['POST']) +@login_required +def create_import_job(sid): + """ + Args: + sid: Server ID + + Creates a new job for import and export table data functionality + + Returns: + None + """ + if request.form: + # Convert ImmutableDict to dict + data = dict(request.form) + data = json.loads(data['data'][0]) + else: + data = json.loads(request.data.decode()) + + # Fetch the server details like hostname, port, roles etc + server = Server.query.filter_by( + id=sid).first() + + if server is None: + return make_json_response( + success=0, + errormsg=_("Couldn't find the given server") + ) + + # To fetch MetaData for the server + from pgadmin.utils.driver import get_driver + driver = get_driver(PG_DEFAULT_DRIVER) + manager = driver.connection_manager(server.id) + conn = manager.connection() + connected = conn.connected() + + if not connected: + return make_json_response( + success=0, + errormsg=_("Please connect to the server first...") + ) + + # Get the utility path from the connection manager + utility = manager.utility('sql') + + # Get the storage path from preference + storage_dir = get_storage_directory() + if 'filename' in data: + if os.name == 'nt': + data['filename'] = data['filename'].replace('/', '\\') + if storage_dir: + storage_dir = storage_dir.replace('/', '\\') + data['filename'] = data['filename'].replace('\\', '\\\\') + data['filename'] = os.path.join(storage_dir, data['filename']) + else: + data['filename'] = os.path.join(storage_dir, data['filename']) + else: + return make_json_response( + data={'status': False, 'info': 'Please specify a valid file'} + ) + + ignore_column_list = '' + column_list_import = '' + + if data['ignore_column']: + new_ignore_col_list = json.loads(data['ignore_column']) + + # format the ignore column list required as per copy command + # requirement + if new_ignore_col_list: + ignore_column_list = '(' + ignore_col_length = len(new_ignore_col_list) + for i in range(ignore_col_length): + ignore_column_list += new_ignore_col_list[i] + if i != (ignore_col_length - 1): + ignore_column_list += ',' + ignore_column_list += ')' + + # format the column import list required as per copy command requirement + if data['column_import']: + column_list_import = '(' + import_col_length = len(data['column_import']) + for indexCnt in range(import_col_length): + column_list_import += data['column_import'][indexCnt] + if indexCnt != (import_col_length - 1): + column_list_import += ',' + column_list_import += ')' + + # Fetch arguments from template + arguments = render_template( + 'import/arguments/import.args', + conn=conn, + data=data, + column_list_import=column_list_import, + ignore_column_list=ignore_column_list + ) + + args = [ + '--host', server.host, '--port', str(server.port), + '--username', server.username, '--dbname', + driver.qtIdent(conn, data['database']), + '--command', arguments + ] + + try: + p = BatchProcess( + desc=Message( + sid, + data['schema'], + data['table'], + data['database'], + storage_dir + ), + cmd=utility, args=args + ) + manager.export_password_env(p.id) + p.start() + jid = p.id + except Exception as e: + current_app.logger.exception(e) + return make_json_response( + status=410, + success=0, + errormsg=str(e) + ) + + # Return response + return make_json_response( + data={'job_id': jid, 'success': 1} + ) diff --git a/web/pgadmin/tools/import/templates/import/arguments/import.args b/web/pgadmin/tools/import/templates/import/arguments/import.args new file mode 100644 index 0000000..355b019 --- /dev/null +++ b/web/pgadmin/tools/import/templates/import/arguments/import.args @@ -0,0 +1 @@ +\copy {{ conn|qtIdent(data.schema, data.table) }} {% if column_list_import %} {{ column_list_import }} {% endif %} {% if data.is_import %}FROM{% else %}TO{% endif %} {{ data.filename|qtLiteral }} {% if data.oid %} OIDS {% endif %}{% if data.delimiter and data.delimiter == '[tab]' %} DELIMITER E'\\t' {% elif data.delimiter %} DELIMITER {{ data.delimiter|qtLiteral }}{% endif %}{% if data.format == 'csv' %} CSV HEADER {% endif %}{% if data.encoding %} ENCODING {{ data.encoding|qtLiteral }}{% endif %}{% if data.quote %} QUOTE {{ data.quote|qtLiteral }}{% endif %}{% if data.null_string %} NULL {{ data.null_string|qtLiteral }}{% endif %}{% if data.escape %} ESCAPE {{ data.escape|qtLiteral }}{% endif %}{% if data.format == 'csv' and ignore_column_list %} FORCE_NOT_NULL {{ ignore_column_list }} {% endif %}; diff --git a/web/pgadmin/tools/import/templates/import/js/import.js b/web/pgadmin/tools/import/templates/import/js/import.js new file mode 100644 index 0000000..2d288f2 --- /dev/null +++ b/web/pgadmin/tools/import/templates/import/js/import.js @@ -0,0 +1,418 @@ +define( + ['jquery', 'underscore', 'underscore.string', 'alertify', 'pgadmin', + 'pgadmin.browser', 'backbone', 'backgrid', 'backform', + 'pgadmin.backform', 'pgadmin.backgrid', 'pgadmin.browser.node.ui'], + function($, _, S, Alertify, pgAdmin, pgBrowser, Backbone, Backgrid, Backform) { + + pgAdmin = pgAdmin || window.pgAdmin || {}; + + var pgTools = pgAdmin.Tools = pgAdmin.Tools || {}; + + // Return back, this has been called more than once + if (pgAdmin.Tools.import_utility) + return pgAdmin.Tools.import_utility; + + // Main model for Import/Export functionality + var ImportExportModel = Backbone.Model.extend({ + defaults: { + is_import: true, /* false for Export */ + filename: undefined, + format: 'text', + encoding: undefined, + oid: undefined, + header: undefined, + delimiter: undefined, + quote: undefined, + escape: undefined, + null_string: undefined, + column_import: [], + ignore_column: [], + database: undefined, + schema: undefined, + table: undefined + }, + schema: [{ + id: 'is_import', label:'{{ _('Import/Export') }}', cell: 'switch', + type: 'switch', group: '{{ _('Files')}}', + options: { + 'onText': '{{ _('Import') }}', 'offText': '{{ _('Export') }}', + 'onColor': 'success', 'offColor': 'primary' + } + }, { /* select file control for import */ + id: 'filename', label: '{{ _('Filename')}}', deps: ['is_import'], + type: 'text', control: Backform.FileControl, group: '{{ _('Files')}}', + dialog_type: 'select_file', supp_types: ['csv', 'txt', '*'], + visible: 'isSelectVisible' + }, { /* create file control for export */ + id: 'filename', label: '{{ _('Filename')}}', deps: ['is_import'], + type: 'text', control: Backform.FileControl, group: '{{ _('Files')}}', + dialog_type: 'create_file', supp_types: ['csv', 'txt', '*'], + visible: 'isCreateVisible' + }, + { + id: 'format', label: '{{ _("Format") }}', cell: 'string', + control: 'select2', group: '{{ _('Files')}}', + options:[ + {'label': 'text', 'value': 'text'}, + {'label': 'csv', 'value': 'csv'}, + {'label': 'binary', 'value': 'binary'}, + ], + disabled: 'isDisabled', select2: {allowClear: false, width: "100%" }, + }, + { + id: 'encoding', label: '{{ _("Encoding") }}', cell: 'string', + control: 'node-ajax-options', node: 'database', url: 'get_encodings', first_empty: true, + group: '{{ _('Files')}}' + }, + { + id: 'column_import', label: '{{ _("Columns to/for import/export") }}', cell: 'string', + type: 'array', control: Backform.MultiSelectAjaxControl.extend({ + // By default, all the import columns should be selected + initialize: function() { + Backform.MultiSelectAjaxControl.prototype.initialize.apply(this, arguments); + var self = this, + options = self.field.get('options'), + op_vals = []; + if (_.isFunction(options)) { + try { + options = options.apply(self) + } catch(e) { + // Do nothing + options = []; + } + } + _.each(options, function(op){ + op_vals.push(op['value']); + }); + + self.model.set(self.field.get('name'),op_vals); + } + }), + node: 'column', url: 'nodes', group: '{{ _('Columns')}}', + transform: function(rows) { + var self = this, + node = self.field.get('schema_node'), + res = []; + + _.each(rows, function(r) { + var l = (_.isFunction(node['node_label']) ? + (node['node_label']).apply(node, [r, self.model, self]) : + r.label), + image = (_.isFunction(node['node_image']) ? + (node['node_image']).apply( + node, [r, self.model, self] + ) : + (node['node_image'] || ('icon-' + node.type))); + res.push({ + 'value': r.label, + 'image': image, + 'label': l + }); + }); + + return res; + }, + select2: { multiple: true, allowClear: true, placeholder: '{{ _('Select columns to import...') }}'}, + }, + { + id: 'null_string', label: '{{ _("NULL Strings") }}', cell: 'string', + type: 'text', group: '{{ _('Columns')}}', disabled: 'isDisabled', deps: ['format'] + }, + { + id: 'ignore_column', label: '{{ _("Ignore Columns") }}', cell: 'string', + control: 'node-list-by-name', node: 'column', + group: '{{ _('Columns')}}', deps: ['format'], disabled: 'isDisabled', + select2: { multiple: true, allowClear: true, placeholder: '{{ _('Select columns to ignore...') }}'}, + }, + { + type: 'nested', control: 'fieldset', label: '{{ _('Miscellaneous') }}', + group: '{{ _('Options') }}', + schema:[{ + id: 'oid', label:'{{ _('OID') }}', cell: 'string', + type: 'switch', group: '{{ _('Miscellaneous') }}' + },{ + id: 'header', label:'{{ _('Header') }}', cell: 'string', + type: 'switch', group: '{{ _('Miscellaneous') }}', deps: ['format'], disabled: 'isDisabled' + },{ + id: 'delimiter', label:'{{ _('Delimiter') }}', cell: 'string', first_empty: true, deps: ['format'], + type: 'text', control: 'node-ajax-options', group: '{{ _('Miscellaneous') }}', disabled: 'isDisabled', + options:[ + + {'label': ';', 'value': ';'}, + {'label': ',', 'value': ','}, + {'label': '|', 'value': '|'}, + {'label': '[tab]', 'value': '[tab]'}, + + ], + select2: { + allowClear: false, + width: "100%", + placeholder: '{{ _('Select from list...') }}' + }, + }] + }, + { + type: 'nested', control: 'fieldset', label: '{{ _('Quote') }}', + group: '{{ _('Options') }}', + schema:[{ + id: 'quote', label:'{{ _('Quote') }}', cell: 'string', + type: 'text', control: 'node-ajax-options', group: '{{ _('Quote') }}', + disabled: 'isDisabled', deps: ['format'], first_empty: true, + options:[ + {'label': '\"', 'value': '\"'}, + {'label': '\'', 'value': '\''}, + ], + select2: { + allowClear: false, + width: "100%", + placeholder: '{{ _('Select from list...') }}' + }, + },{ + id: 'escape', label:'{{ _('Escape') }}', cell: 'string', + type: 'text', control: 'node-ajax-options', group: '{{ _('Quote') }}', + disabled: 'isDisabled', deps: ['format'], first_empty: true, + options:[ + {'label': '\"', 'value': '\"'}, + {'label': '\'', 'value': '\''}, + ], + select2: { + allowClear: false, + width: "100%", + placeholder: '{{ _('Select from list...') }}' + }, + }] + } + ], + + // Enable/Disable the items based on the user file format selection + isDisabled: function(m) { + name = this.name; + switch(name) { + case 'quote': + case 'escape': + case 'header': + case 'ignore_column': + if (m.get('format') != 'csv') { + return true; + } + else { + return false; + } + break; + case 'null_string': + case 'delimiter': + if (m.get('format') == 'binary') { + return true; + } + else { + return false; + } + break; + default: + return false; + } + return false; + }, + isSelectVisible: function(m) { + name = this.name; + switch(name) { + case 'filename': + if (m.get('is_import')) { + return true; + } + else { + return false; + } + break; + default: + return false; + } + return false; + }, + isCreateVisible: function(m) { + name = this.name; + switch(name) { + case 'filename': + if (m.get('is_import')) { + return false; + } + else { + return true; + } + default: + return false; + } + return false; + }, + }); + + pgTools.import_utility = { + init: function() { + // We do not want to initialize the module multiple times. + if (this.initialized) + return; + + this.initialized = true; + + /** + Enable/disable import menu in tools based on node selected + Import menu will be enabled only when user select table node. + */ + menu_enabled = function(itemData, item, data) { + var t = pgBrowser.tree, i = item, d = itemData; + var parent_item = t.hasParent(i) ? t.parent(i): null, + parent_data = parent_item ? t.itemData(parent_item) : null; + if(!_.isUndefined(d) && !_.isNull(d) && !_.isNull(parent_data)) + return ( + (_.indexOf(['table'], d._type) !== -1 && + parent_data._type != 'catalog') ? true: false + ); + else + return false; + }; + + // Initialize the context menu to display the import options when user open the context menu for table + pgBrowser.add_menus([{ + name: 'import', node: 'table', module: this, + applies: ['tools', 'context'], callback: 'callback_import', + category: 'import', priority: 10, label: '{{ _('Import...') }}', + data: {object: 'table', type: 'import'}, icon: 'fa fa-sign-in', enable: menu_enabled + },{ + name: 'export', node: 'table', module: this, + applies: ['tools', 'context'], callback: 'callback_import', + category: 'export', priority: 11, label: '{{ _('Export...') }}', + data: {object: 'table', type: 'export'}, icon: 'fa fa-sign-out', enable: menu_enabled + } + ]); + }, + + /* + Open the dialog for the import functionality + */ + callback_import: function(args, item) { + var self = this; + var input = args || {}, + t = pgBrowser.tree, + i = item || t.selected(), + d = i && i.length == 1 ? t.itemData(i) : undefined, + node = d && pgBrowser.Nodes[d._type]; + + if (!d) + return; + + var objName = d.label; + var treeInfo = node.getTreeNodeHierarchy.apply(node, [i]); + var isImport = (args.type == "import") ? true: false; + + if (!Alertify.ImportDialog) { + Alertify.dialog('ImportDialog', function factory() { + + return { + main:function(title, pg_import, node, item, data) { + this.set('title', title); + this.setting('pg_import', pg_import); + this.setting('pg_node', node); + this.setting('pg_item', item); + this.setting('pg_item_data', data); + }, + setup:function() { + return { + buttons:[{ text: "{{ _('OK') }}", key: 27, className: "btn btn-primary fa fa-lg fa-save pg-alertify-button" }, + { text: "{{ _('Cancel') }}", key: 27, className: "btn btn-danger fa fa-lg fa-times pg-alertify-button" }], + options: { modal: 0} + }; + }, + settings: { + pg_import: true, + pg_node: null, + pg_item: null, + pg_item_data: null + }, + // Callback functions when click on the buttons of the Alertify dialogs + callback: function(e) { + if (e.button.text === "{{ _('OK') }}") { + + var n = this.settings['pg_node'], + i = this.settings['pg_item'], + treeInfo = n.getTreeNodeHierarchy.apply(n, [i]) + + this.view.model.set({ + 'database': treeInfo.database.label, + 'schema': treeInfo.schema.label, + 'table': treeInfo.table.label + }); + var self = this, + baseUrl = "{{ url_for('import.index') }}" + + "create_job/" + treeInfo.server._id, + args = this.view.model.toJSON(); + + $.ajax({ + url: baseUrl, + method: 'POST', + data:{ 'data': JSON.stringify(args) }, + success: function(res) { + if (res.success) { + Alertify.message('{{ _('Background process for taking import/export has been created!') }}', 1); + pgBrowser.Events.trigger('pgadmin-bgprocess:created', self); + } + }, + error: function(xhr, status, error) { + try { + var err = $.parseJSON(xhr.responseText); + Alertify.alert( + '{{ _('Import failed...') }}', + err.errormsg + ); + } catch (e) {} + } + }); + } + }, + hooks: { + onclose: function() { + if (this.view) { + this.view.remove({data: true, internal: true, silent: true}); + } + } + }, + prepare:function() { + // Main import module container + var self = this, + $container = $("<div class='import_dlg'></div>"), + n = this.settings.pg_node, + i = this.settings.pg_item, + treeInfo = n.getTreeNodeHierarchy.apply(n, [i]), + newModel = new ImportExportModel ({ + 'is_import': this.settings['pg_import'] + }, { + node_info: treeInfo + }), + fields = Backform.generateViewSchema( + treeInfo, newModel, 'create', node, treeInfo.server, true + ), + view = this.view = new Backform.Dialog({ + el: $container, model: newModel, schema: fields + }); + + $(this.elements.body.childNodes[0]).addClass( + 'alertify_tools_dialog_properties obj_properties' + ); + view.render(); + + this.elements.content.appendChild($container.get(0)); + } + }; + }); + } + + // Open the Alertify dialog for the import/export module + Alertify.ImportDialog( + S( + "{{ _("Import/Export data to/from file/table - '%%s'") }}" + ).sprintf(treeInfo.table.label).value(), isImport, node, i, d + ).set('resizable',true).resizeTo('60%','70%'); + } + }; + + return pgAdmin.Tools.import_utility; + }); ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2016-05-17 07:11 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2016-05-05 11:13 [pgAdmin4]: Initial patch for Import table Neel Patel <[email protected]> 2016-05-16 07:18 ` Ashesh Vashi <[email protected]> 2016-05-17 07:11 ` Neel Patel <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox