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