public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ashesh Vashi <[email protected]>
To: Neel Patel <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: [pgAdmin4]: Initial patch for Import table
Date: Mon, 16 May 2016 12:48:57 +0530
Message-ID: <CAG7mmoxHOr9qdGD6XfmpcvZygbY0+LFrXbnM04mv489qg4D5tg@mail.gmail.com> (raw)
In-Reply-To: <CACCA4P2fR_RscNCxz6Y=kz6ZKZ=3bUAOqKtDsY36jwuitu=Ssg@mail.gmail.com>
References: <CACCA4P2fR_RscNCxz6Y=kz6ZKZ=3bUAOqKtDsY36jwuitu=Ssg@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-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;
+ });
view thread (3+ 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]: Initial patch for Import table
In-Reply-To: <CAG7mmoxHOr9qdGD6XfmpcvZygbY0+LFrXbnM04mv489qg4D5tg@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