Hi
PFA patch for primary key constraint. Note: This patch has dependency on Table, Column node and backform multiselect control. -- *Harshal Dhumal* *Software Engineer * EenterpriseDB <http://www.enterprisedb.com>
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/__init__.py new file mode 100644 index 0000000..64f91e8 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/__init__.py @@ -0,0 +1,129 @@ +########################################################################## +# +# pgAdmin 4 - PostgreSQL Tools +# +# Copyright (C) 2013 - 2016, The pgAdmin Development Team +# This software is released under the PostgreSQL Licence +# +########################################################################## + +"""Implements Constraint Node""" + +from flask.ext.babel import gettext +from flask import render_template, make_response +from pgadmin.browser.collection import CollectionNodeModule +import pgadmin.browser.server_groups.servers.databases.schemas.tables as table +from pgadmin.utils.ajax import make_json_response, \ + make_response as ajax_response +from .type import ConstraintRegistry + + +class ConstraintsModule(CollectionNodeModule): + """ + class ConstraintsModule(CollectionNodeModule) + + A module class for Constraint node derived from CollectionNodeModule. + + Methods: + ------- + * __init__(*args, **kwargs) + - Method is used to initialize the ConstraintsModule and it's base module. + + * get_nodes(gid, sid, did) + - Method is used to generate the browser collection node. + + * node_inode() + - Method is overridden from its base class to make the node as leaf node. + + * script_load() + - Load the module script for constraint node, when any of the database node is + initialized. + """ + + NODE_TYPE = 'constraints' + COLLECTION_LABEL = gettext("Constraints") + + def __init__(self, *args, **kwargs): + self.min_ver = None + self.max_ver = None + super(ConstraintsModule, self).__init__(*args, **kwargs) + + def get_nodes(self, gid, sid, did, scid, tid): + """ + Generate the collection node + """ + yield self.generate_browser_collection_node(tid) + + @property + def script_load(self): + """ + Load the module script for constraints, when any of the table node is + initialized. + """ + return table.TableModule.NODE_TYPE + +blueprint = ConstraintsModule(__name__) + + +@blueprint.route('/nodes/<int:gid>/<int:sid>/<int:did>/<int:scid>/<int:tid>/') +def nodes(**kwargs): + """ + Returns all constraint as a tree node. + + Args: + **kwargs: + + Returns: + + """ + + cmd = {"cmd": "nodes"} + res = [] + for name in ConstraintRegistry.registry: + module = (ConstraintRegistry.registry[name])['nodeview'] + view = module(**cmd) + res = res + view.get_nodes(**kwargs) + + return make_json_response( + data=res, + status=200 + ) + + +@blueprint.route('/obj/<int:gid>/<int:sid>/<int:did>/<int:scid>/<int:tid>/') +def proplist(**kwargs): + """ + Returns all constraint with properties. + Args: + **kwargs: + + Returns: + + """ + + cmd = {"cmd": "obj"} + res = [] + for name in ConstraintRegistry.registry: + module = (ConstraintRegistry.registry[name])['nodeview'] + view = module(**cmd) + res = res + view.get_node_list(**kwargs) + + return ajax_response( + response=res, + status=200 + ) + + +@blueprint.route('/module.js') +def module_js(): + """ + This property defines whether javascript exists for this node. + + """ + return make_response( + render_template( + "constraints/js/constraints.js", + _=gettext + ), + 200, {'Content-Type': 'application/x-javascript'} + ) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/__init__.py new file mode 100644 index 0000000..1abbe1d --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/__init__.py @@ -0,0 +1,774 @@ +########################################################################## +# +# pgAdmin 4 - PostgreSQL Tools +# +# Copyright (C) 2013 - 2016, The pgAdmin Development Team +# This software is released under the PostgreSQL Licence +# +########################################################################## + +"""Implements Primary key constraint Node""" + +import json +from flask import render_template, make_response, request, jsonify +from flask.ext.babel import gettext as _ +from pgadmin.utils.ajax import make_json_response, \ + make_response as ajax_response, internal_server_error +from pgadmin.browser.utils import PGChildNodeView +from pgadmin.utils.ajax import precondition_required +from pgadmin.utils.driver import get_driver +from config import PG_DEFAULT_DRIVER +import pgadmin.browser.server_groups.servers.databases.schemas.tables as table +from functools import wraps +from pgadmin.browser.server_groups.servers.databases.schemas.tables.constraints.type \ + import ConstraintRegistry, ConstraintTypeModule + + +class PrimaryKeyConstraintModule(ConstraintTypeModule): + """ + class PrimaryKeyConstraintModule(CollectionNodeModule) + + A module class for Primary key constraint node derived from ConstraintTypeModule. + + Methods: + ------- + * __init__(*args, **kwargs) + - Method is used to initialize the PrimaryKeyConstraintModule and it's base module. + + * get_nodes(gid, sid, did) + - Method is used to generate the browser collection node. + + * node_inode() + - Method is overridden from its base class to make the node as leaf node. + + * script_load() + - Load the module script for language, when any of the database node is + initialized. + """ + + NODE_TYPE = 'primary_key' + COLLECTION_LABEL = _("Primary Keys") + + def __init__(self, *args, **kwargs): + """ + Method is used to initialize the PrimaryKeyConstraintModule and it's base module. + + Args: + *args: + **kwargs: + + Returns: + + """ + self.min_ver = None + self.max_ver = None + super(PrimaryKeyConstraintModule, self).__init__(*args, **kwargs) + + def get_nodes(self, gid, sid, did, scid, tid): + """ + Generate the collection node + """ + pass + + @property + def node_inode(self): + """ + Override this property to make the node a leaf node. + + Returns: False as this is the leaf node + """ + return False + + @property + def script_load(self): + """ + Load the module script for primary_key, when any of the table node is + initialized. + + Returns: node type of the server module. + """ + return table.TableModule.NODE_TYPE + +blueprint = PrimaryKeyConstraintModule(__name__) + + +class PrimaryKeyConstraintView(PGChildNodeView): + """ + class PrimaryKeyConstraintView(PGChildNodeView) + + A view class for Primary key constraint node derived from PGChildNodeView. This class is + responsible for all the stuff related to view like creating, updating Primary key constraint + node, showing properties, showing sql in sql pane. + + Methods: + ------- + * __init__(**kwargs) + - Method is used to initialize the PrimaryKeyConstraintView and it's base view. + + * module_js() + - This property defines (if javascript) exists for this node. + Override this property for your own logic + + * check_precondition() + - This function will behave as a decorator which will checks + database connection before running view, it will also attaches + manager,conn & template_path properties to self + + * list() + - This function returns primary key constraint nodes within that + collection as http response. + + * get_list() + - This function is used to list all the language nodes within that collection + and return list of primary key constraint nodes. + + * nodes() + - This function returns child node within that collection. + Here return all primary key constraint node as http response. + + * get_nodes() + - returns all primary key constraint nodes' list. + + * properties() + - This function will show the properties of the selected primary key. + + * update() + - This function will update the data for the selected primary key. + + * msql() + - This function is used to return modified SQL for the selected primary key. + + * get_sql() + - This function will generate sql from model data. + + * sql(): + - This function will generate sql to show it in sql pane for the selected primary key. + + * get_indices(): + - This function returns indices for current table. + + """ + + node_type = 'primary_key' + + parent_ids = [ + {'type': 'int', 'id': 'gid'}, + {'type': 'int', 'id': 'sid'}, + {'type': 'int', 'id': 'did'}, + {'type': 'int', 'id': 'scid'}, + {'type': 'int', 'id': 'tid'} + ] + ids = [{'type': 'int', 'id': 'pkid'} + ] + + operations = dict({ + 'obj': [ + {'get': 'properties', 'delete': 'delete', 'put': 'update'}, + {'get': 'list', 'post': 'create'} + ], + 'delete': [{'delete': 'delete'}], + 'children': [{'get': 'children'}], + 'nodes': [{'get': 'node'}, {'get': 'nodes'}], + 'sql': [{'get': 'sql'}], + 'msql': [{'get': 'msql'}, {'get': 'msql'}], + 'stats': [{'get': 'statistics'}], + 'dependency': [{'get': 'dependencies'}], + 'dependent': [{'get': 'dependents'}], + 'module.js': [{}, {}, {'get': 'module_js'}], + 'indices': [{}, {'get': 'get_indices'}] + }) + + def module_js(self): + """ + This property defines (if javascript) exists for this node. + Override this property for your own logic. + """ + return make_response( + render_template( + "primary_key/js/primary_key.js", + _=_ + ), + 200, {'Content-Type': 'application/x-javascript'} + ) + + def check_precondition(f): + """ + This function will behave as a decorator which will checks + database connection before running view, it will also attaches + manager,conn & template_path properties to self + """ + @wraps(f) + def wrap(*args, **kwargs): + # Here args[0] will hold self & kwargs will hold gid,sid,did + self = args[0] + self.manager = get_driver(PG_DEFAULT_DRIVER).connection_manager( + kwargs['sid'] + ) + self.conn = self.manager.connection(did=kwargs['did']) + + # If DB not connected then return error to browser + if not self.conn.connected(): + return precondition_required( + _( + "Connection to the server has been lost!" + ) + ) + + self.template_path = 'primary_key/sql' + # We need parent's name eg table name and schema name + SQL = render_template("/".join([self.template_path, + 'get_parent.sql']), + tid=kwargs['tid']) + status, rset = self.conn.execute_2darray(SQL) + if not status: + return internal_server_error(errormsg=rset) + + for row in rset['rows']: + self.schema = row['schema'] + self.table = row['table'] + return f(*args, **kwargs) + + return wrap + + @check_precondition + def properties(self, gid, sid, did, scid, tid, pkid=None): + """ + This function is used to list all the primary key + nodes within that collection. + + Args: + gid: Server Group ID + sid: Server ID + did: Database ID + scid: Schema ID + tid: Table ID + pkid: Primary key constraint ID + + Returns: + + """ + sql = render_template("/".join([self.template_path, 'properties.sql']), tid=tid, pkid=pkid) + status, res = self.conn.execute_dict(sql) + + if not status: + return internal_server_error(errormsg=res) + + result = res['rows'][0] + + sql = render_template("/".join([self.template_path, 'get_constraint_cols.sql']), cid=pkid, colcnt=result['indnatts']) + status, res = self.conn.execute_dict(sql) + + if not status: + return internal_server_error(errormsg=res) + + columns = [] + for row in res['rows']: + columns.append({"column": row['column'].strip('"')}) + + result['columns'] = columns + + return ajax_response( + response=result, + status=200 + ) + + @check_precondition + def list(self, gid, sid, did, scid, tid, pkid=None): + """ + This function returns all primary keys + nodes within that collection as a http response. + + Args: + gid: Server Group ID + sid: Server ID + did: Database ID + scid: Schema ID + tid: Table ID + pkid: Primary key constraint ID + + Returns: + + """ + try: + res = self.get_node_list(gid, sid, did, scid, tid, pkid) + return ajax_response( + response=res, + status=200 + ) + except Exception as e: + return internal_server_error(errormsg=str(e)) + + @check_precondition + def get_node_list(self, gid, sid, did, scid, tid, pkid=None): + """ + This function returns all primary keys + nodes within that collection as a list. + + Args: + gid: Server Group ID + sid: Server ID + did: Database ID + scid: Schema ID + tid: Table ID + pkid: Primary key constraint ID + + Returns: + + """ + SQL = render_template("/".join([self.template_path, + 'properties.sql']), + tid=tid) + status, res = self.conn.execute_dict(SQL) + + return res['rows'] + + @check_precondition + def nodes(self, gid, sid, did, scid, tid, pkid=None): + """ + This function returns all event trigger nodes as a + http response. + + Args: + gid: Server Group ID + sid: Server ID + did: Database ID + scid: Schema ID + tid: Table ID + pkid: Primary key constraint ID + + Returns: + + """ + try: + res = self.get_nodes(gid, sid, did, scid, tid, pkid) + return make_json_response( + data=res, + status=200 + ) + except Exception as e: + return internal_server_error(errormsg=str(e)) + + @check_precondition + def get_nodes(self, gid, sid, did, scid, tid, pkid=None): + """ + This function returns all event trigger nodes as a list. + + Args: + gid: Server Group ID + sid: Server ID + did: Database ID + scid: Schema ID + tid: Table ID + pkid: Primary key constraint ID + + Returns: + + """ + res = [] + SQL = render_template("/".join([self.template_path, + 'nodes.sql']), + tid=tid) + status, rset = self.conn.execute_2darray(SQL) + + for row in rset['rows']: + res.append( + self.blueprint.generate_browser_node( + row['oid'], + tid, + row['name'], + icon="icon-primary_key" + )) + return res + + @check_precondition + def create(self, gid, sid, did, scid, tid, pkid=None): + """ + This function will create a primary key. + + Args: + gid: Server Group ID + sid: Server ID + did: Database ID + scid: Schema ID + tid: Table ID + pkid: Primary key constraint ID + + Returns: + + """ + required_args = [ + [u'columns', u'index'] # Either of one should be there. + ] + + data = request.form if request.form else json.loads(request.data.decode()) + + for arg in required_args: + if isinstance(arg, list): + for param in arg: + if (param in data and + (not isinstance(data[param], list) or + (isinstance(data[param], list) and + len(data[param]) > 0))): + break + else: + return make_json_response( + status=400, + success=0, + errormsg=_( + "Couldn't find at least one required parameter (%s)." % str(param) + ) + ) + + elif arg not in data: + return make_json_response( + status=400, + success=0, + errormsg=_( + "Couldn't find the required parameter (%s)." % arg + ) + ) + + data['schema'] = self.schema + data['table'] = self.table + try: + # The below SQL will execute CREATE DDL only + SQL = render_template( + "/".join([self.template_path, 'create.sql']), + data=data, conn=self.conn + ) + status, msg = self.conn.execute_scalar(SQL) + if not status: + return internal_server_error(errormsg=msg) + + sql = render_template("/".join([self.template_path, 'grant.sql']), data=data, conn=self.conn) + sql = sql.strip('\n').strip(' ') + + if sql != '': + status, res = self.conn.execute_scalar(sql) + if not status: + return internal_server_error(errormsg=res) + + sql = render_template("/".join([self.template_path, 'get_oid.sql']), tid=tid) + status, res = self.conn.execute_dict(sql) + if not status: + return internal_server_error(errormsg=res) + + return jsonify( + node=self.blueprint.generate_browser_node( + res['rows'][0]['oid'], + tid, + res['rows'][0]['name'], + icon="icon-%s" % self.node_type + ) + ) + + except Exception as e: + return make_json_response( + status=400, + success=0, + errormsg=e + ) + + @check_precondition + def update(self, gid, sid, did, scid, tid, pkid=None): + """ + This function will update the data for the selected + primary key. + + Args: + gid: Server Group ID + sid: Server ID + did: Database ID + scid: Schema ID + tid: Table ID + pkid: Primary key constraint ID + + Returns: + + """ + data = request.form if request.form else json.loads(request.data.decode()) + + try: + data['schema'] = self.schema + data['table'] = self.table + sql = self.get_sql(data, tid, pkid) + sql = sql.strip('\n').strip(' ') + if sql != "": + status, res = self.conn.execute_scalar(sql) + if not status: + return internal_server_error(errormsg=res) + + sql = render_template("/".join([self.template_path, 'get_oid.sql']), tid=tid) + status, res = self.conn.execute_dict(sql) + if not status: + return internal_server_error(errormsg=res) + + return jsonify( + node=self.blueprint.generate_browser_node( + res['rows'][0]['oid'], + tid, + res['rows'][0]['name'], + icon="icon-%s" % self.node_type + ) + ) + else: + return make_json_response( + success=1, + info="Nothing to update", + data={ + 'id': pkid, + 'tid': tid, + 'scid': scid, + 'sid': sid, + 'gid': gid, + 'did': did + } + ) + + except Exception as e: + return internal_server_error(errormsg=str(e)) + + @check_precondition + def delete(self, gid, sid, did, scid, tid, pkid=None): + """ + This function will delete an existing primary key. + + Args: + gid: Server Group ID + sid: Server ID + did: Database ID + scid: Schema ID + tid: Table ID + pkid: Primary key constraint ID + + Returns: + + """ + # Below code will decide if it's simple drop or drop with cascade call + if self.cmd == 'delete': + # This is a cascade operation + cascade = True + else: + cascade = False + try: + sql = render_template("/".join([self.template_path, 'get_oid.sql']), tid=tid) + status, res = self.conn.execute_dict(sql) + if not status: + return internal_server_error(errormsg=res) + + data = res['rows'][0] + data['schema'] = self.schema + data['table'] = self.table + + sql = render_template("/".join([self.template_path, 'delete.sql']), data=data, cascade=cascade) + status, res = self.conn.execute_scalar(sql) + if not status: + return internal_server_error(errormsg=res) + + return make_json_response( + success=1, + info=_("Primary key dropped."), + data={ + 'id': pkid, + 'sid': sid, + 'gid': gid, + 'did': did + } + ) + + except Exception as e: + return internal_server_error(errormsg=str(e)) + + @check_precondition + def msql(self, gid, sid, did, scid, tid, pkid=None): + """ + This function returns modified SQL for the selected + primary key. + + Args: + gid: Server Group ID + sid: Server ID + did: Database ID + scid: Schema ID + tid: Table ID + pkid: Primary key constraint ID + + Returns: + + """ + data = {} + for k, v in request.args.items(): + try: + data[k] = json.loads(v) + except ValueError: + data[k] = v + + data['schema'] = self.schema + data['table'] = self.table + try: + sql = self.get_sql(data, tid, pkid) + sql = sql.strip('\n').strip(' ') + + return make_json_response( + data=sql, + status=200 + ) + + except Exception as e: + return internal_server_error(errormsg=str(e)) + + def get_sql(self, data, tid, pkid=None): + """ + This function will generate sql from model data. + + Args: + data: Contains the data of the selected primary key constraint. + tid: Table ID. + pkid: Primary key constraint ID + + Returns: + + """ + if pkid is not None: + sql = render_template("/".join([self.template_path, 'properties.sql']), tid=tid, pkid=pkid) + status, res = self.conn.execute_dict(sql) + if not status: + return internal_server_error(errormsg=res) + + old_data = res['rows'][0] + required_args = [u'name'] + for arg in required_args: + if arg not in data: + data[arg] = old_data[arg] + + sql = render_template("/".join([self.template_path, 'update.sql']), + data=data, o_data=old_data) + else: + required_args = [ + [u'columns', u'index'] # Either of one should be there. + ] + + for arg in required_args: + if isinstance(arg, list): + for param in arg: + if (param in data and + ((isinstance(data[param], str) and + data[param] != "") or + (isinstance(data[param], list) and + len(data[param]) > 0))): + break + else: + return _('-- definition incomplete') + + elif arg not in data: + return _('-- definition incomplete') + + sql = render_template("/".join([self.template_path, 'create.sql']), data=data, conn=self.conn) + sql += "\n" + sql += render_template("/".join([self.template_path, 'grant.sql']), data=data, conn=self.conn) + + return sql + + @check_precondition + def sql(self, gid, sid, did, scid, tid, pkid=None): + """ + This function generates sql to show in the sql pane for the selected + primary key. + + Args: + gid: Server Group ID + sid: Server ID + did: Database ID + scid: Schema ID + tid: Table ID + pkid: Primary key constraint ID + + Returns: + + """ + try: + SQL = render_template( + "/".join([self.template_path, 'properties.sql']), + tid=tid, conn=self.conn, pkid=pkid) + status, res = self.conn.execute_dict(SQL) + if not status: + return internal_server_error(errormsg=res) + + data = res['rows'][0] + data['schema'] = self.schema + data['table'] = self.table + + sql = render_template( + "/".join([self.template_path, 'get_constraint_cols.sql']), + cid=pkid, colcnt=data['indnatts']) + + status, res = self.conn.execute_dict(sql) + + if not status: + return internal_server_error(errormsg=res) + + columns = [] + for row in res['rows']: + columns.append({"column": row['column'].strip('"')}) + + data['columns'] = columns + + SQL = render_template( + "/".join([self.template_path, 'create.sql']), data=data) + SQL += "\n" + SQL += render_template( + "/".join([self.template_path, 'grant.sql']), + data=data, conn=self.conn) + + sql_header = "-- Constraint: {0}\n\n-- ".format(data['name']) + + sql_header += render_template( + "/".join([self.template_path, 'delete.sql']), + data=data) + sql_header += "\n" + + SQL = sql_header + SQL + + return ajax_response(response=SQL) + + except Exception as e: + return internal_server_error(errormsg=str(e)) + + @check_precondition + def get_indices(self, gid, sid, did, scid, tid, pkid=None): + """ + This function returns indices for current table. + + Args: + gid: Server Group ID + sid: Server ID + did: Database ID + scid: Schema ID + tid: Table ID + pkid: Primary key constraint ID + + Returns: + + """ + res = [{'label': '', 'value': ''}] + sql = render_template("/".join([self.template_path, + 'get_indices.sql']), + tid=tid) + status, rest = self.conn.execute_2darray(sql) + + if not status: + return internal_server_error(errormsg=rest) + + for row in rest['rows']: + res.append( + {'label': row['relname'], 'value': row['relname']} + ) + return make_json_response( + data=res, + status=200 + ) + +constraint = ConstraintRegistry( + 'primary_key', PrimaryKeyConstraintModule, PrimaryKeyConstraintView + ) +PrimaryKeyConstraintView.register_node_view(blueprint) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/static/img/primary_key.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/static/img/primary_key.png new file mode 100644 index 0000000000000000000000000000000000000000..b57f59778554c3b0ee1b872a4174cbe631ee64e2 GIT binary patch literal 443 zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbMf<N%)#S0Mf1eD~=McE?xh-aAwO z?)k2F&$r(_Q+<4;&bQAOzI-_L>D`Vm@3!4NRdR5F#O>qdUq7Aw{C>xWm$N>;n)Uh3 z;xBI(e|bIY^Q(!UUNnAw(e(Yp+IP=)e*Jjj^V^Ldo=^DrV#4P)3vM4z`1*Fitz!|N zUo?Mt)qC$u?bnY-Kfl}j@%4gFujk!8nS1w4)!Qd4-ac7$_f*mGm0D_V?`Q*^#aI&L z7tG-B>_!@p!&%@FSq!8-z}W3%wjGcW@9E+gB5^r6VF6b{N>Y-`Gvm!0Hf=0cIJvo? zsPOZLPmT=s%*?{88RjpTu%Tncj2$gYMC1b;qT2k!{Nfw~JtJL3mox?X#ySUk&+eD! zaI9ft<CBfGtz|aJUirArf`Q@2TGkzB-UgQgtx_#<jVMV;EJ?LWE=mPb3`Pcq2D*ku zx(0?JMg~?U##Sb#+6Jap1_txq>8(Q1kei>9nN|tWU|<Q>090*aWnc!;aB6z!8lVOS MPgg&ebxsLQ07e(akpKVy literal 0 HcmV?d00001 diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/js/primary_key.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/js/primary_key.js new file mode 100644 index 0000000..a8c40fa --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/js/primary_key.js @@ -0,0 +1,302 @@ +define( + ['jquery', 'underscore', 'underscore.string', 'pgadmin', + 'pgadmin.browser', 'alertify', 'pgadmin.browser.collection'], +function($, _, S, pgAdmin, pgBrowser, alertify) { + + // Extend the browser's collection class for primary key collection + if (!pgBrowser.Nodes['coll-primary_key']) { + var databases = pgAdmin.Browser.Nodes['coll-primary_key'] = + pgAdmin.Browser.Collection.extend({ + node: 'primary_key', + label: '{{ _('Primary key') }}', + type: 'coll-primary_key', + }); + }; + + // Extend the browser's node class for primary key node + if (!pgBrowser.Nodes['primary_key']) { + pgAdmin.Browser.Nodes['primary_key'] = pgBrowser.Node.extend({ + type: 'primary_key', + label: '{{ _('Primary key') }}', + collection_type: 'coll-constraints', + hasSQL: true, + hasDepends: false, + parent_type: 'table', + canDrop: true, + canDropCascade: true, + Init: function() { + /* Avoid multiple registration of menus */ + if (this.initialized) + return; + + this.initialized = true; + + pgBrowser.add_menus([{ + name: 'create_primary_key_on_coll', node: 'coll-constraints', module: this, + applies: ['object', 'context'], callback: 'show_obj_properties', + category: 'create', priority: 4, label: '{{ _('Primary key...') }}', + icon: 'wcTabIcon icon-primary_key', data: {action: 'create', check: true}, + enable: 'canCreate' + } + ]); + }, + canCreate: function(node, item, data) { + // There should be only one primary key per table. + var children = pgBrowser.tree.children(arguments[1], false); + + _.each(children, function(child){ + data = pgBrowser.tree.itemData($(child)); + if (data._type == "primary_key") { + return false; + } + }); + + return true; + }, + // Define the model for primary key node + model: pgAdmin.Browser.Node.Model.extend({ + defaults: { + name: undefined, + oid: undefined, + comment: undefined, + spcname: "pg_default", + index: undefined, + fillfactor: undefined, + condeferrable: undefined, + condeferred: undefined, + columns: [] + }, + + // Define the schema for the primary key node + schema: [{ + id: 'name', label: '{{ _('Name') }}', type: 'text', + mode: ['properties', 'create', 'edit'] + },{ + id: 'oid', label:'{{ _('OID') }}', cell: 'string', + type: 'text' , mode: ['properties'] + },{ + id: 'comment', label:'{{ _('Comment') }}', cell: 'string', + type: 'multiline', mode: ['properties', 'create', 'edit'] + },{ + id: 'columns', label: '{{ _('Columns') }}', + type: 'collection', group: '{{ _('Definition') }}', editable:true, + canDelete: true, canAdd: true, + control: Backform.MultiSelectAjaxControl.extend({ + formatter: { + fromRaw: function (rawData, model) { + var res = _.isObject(rawData) ? + rawData : JSON.parse(rawData); + + return _.pluck(res, 'column'); + }, + toRaw: function (formattedData, model) { + return formattedData; + } + }, + defaults: _.extend( + {}, + Backform.NodeListByNameControl.prototype.defaults, + { + select2: { + multiple: true, + allowClear: true, + width: 'style', + placeholder: '{{ _('Select the column(s)') }}', + } + } + ), + onChange: function(e) { + var model = this.model, + $el = $(e.target), + attrArr = this.field.get("name").split('.'), + name = attrArr.shift(), + path = attrArr.join('.'), + vals = this.getValueFromDOM(), + collection = model.get(name), + removed = []; + + this.stopListening(this.model, "change:" + name, this.render); + + /* + * Iterate through all the values, and find out how many are already + * present in the collection. + */ + collection.each(function(m) { + var column = m.get('column'), + idx = _.indexOf(vals, column); + + if (idx > -1) { + vals.splice(idx, 1); + } else { + removed.push(column); + } + }); + + /* + * Adding new values + */ + _.each(vals, function(v) { + collection.add({column: v}); + }); + + /* + * Removing unwanted! + */ + _.each(removed, function(v) { + collection.remove(collection.where({column: v})); + }); + + this.listenTo(this.model, "change:" + name, this.render); + } + }), + deps: ['index'], node: 'column', + model: pgBrowser.Node.Model.extend({ + defaults: { + column: undefined + }, + validate: function() { + var columns = this.handler.get('columns'); + if ((!_.isUndefined(columns) && !_.isNull(columns) && columns.length > 0)) { + this.handler.errorModel.unset("columns"); + this.handler.errorModel.clear(); + } else { + var msg = '{{ _('Please specify column(s).') }}'; + this.handler.errorModel.set('columns', msg); + return msg; + } + return null; + } + }), + transform : function(data){ + var res = []; + if (data && _.isArray(data)) { + _.each(data, function(d) { + res.push({label: d.label, value: d.label}); + }) + } + return res; + }, + select2:{allowClear:false}, + disabled: function(m) { + // We can't update columns of existing primary key. + if (!m.isNew()) { + return true; + } + // Disable if index is selected. + var index = m.get('index'); + if(_.isUndefined(index) || index == '') { + return false; + } else { + var col = m.get('columns'); + col.reset(); + return true; + } + } + },{ + id: 'spcname', label: '{{ _('Tablespace') }}', + type: 'text', group: '{{ _('Definition') }}', + control: 'node-list-by-name', node: 'tablespace', + deps: ['index'], + select2:{allowClear:false}, + filter: function(m) { + // Don't show pg_global tablespace in selection. + if (m.label == "pg_global") return false; + else return true; + }, + disabled: function(m) { + // Disable if index is selected. + var index = m.get('index'); + if(_.isUndefined(index) || index == '') { + return false; + } else { + setTimeout(function(){ + m.set('spcname', ''); + },10); + return true; + } + } + },{ + id: 'index', label: '{{ _('Index') }}', + type: 'text', group: '{{ _('Definition') }}', + control: 'node-ajax-options', url:"indices", + select2:{allowClear:true}, + disabled: function(m) { + // We can't update index of existing primary key. + return !m.isNew(); + } + },{ + id: 'fillfactor', label: '{{ _('Fill factor') }}', deps: ['index'], + type: 'int', group: '{{ _('Definition') }}', allowNull: true, + disabled: function(m) { + // Disable if index is selected. + var index = m.get('index'); + if(_.isUndefined(index) || index == '') { + return false; + } else { + setTimeout(function(){ + m.set('fillfactor', null); + },10); + return true; + } + } + },{ + id: 'condeferrable', label: '{{ _('Deferrable') }}', + type: 'switch', group: '{{ _('Definition') }}', deps: ['index'], + disabled: function(m) { + // We can't update condeferrable of existing primary key. + if (!m.isNew()) { + return true; + } + // Disable if index is selected. + var index = m.get('index'); + if(_.isUndefined(index) || index == '') { + return false; + } else { + setTimeout(function(){ + m.set('condeferrable', false); + },10); + return true; + } + } + },{ + id: 'condeferred', label: '{{ _('Deferred') }}', + type: 'switch', group: '{{ _('Definition') }}', + deps: ['condeferrable'], + disabled: function(m) { + // We can't update condeferred of existing primary key. + if (!m.isNew()) { + return true; + } + // Disable if condeferred is false or unselected. + if(m.get('condeferrable') == true) { + return false; + } else { + setTimeout(function(){ + m.set('condeferred', false); + },10); + return true; + } + } + } + ], + validate: function() { + this.errorModel.clear(); + + var columns = this.get('columns'), + index = this.get('index'); + + if ((_.isUndefined(index) || String(index).replace(/^\s+|\s+$/g, '') == '') && + (_.isUndefined(columns) || _.isNull(columns) || columns.length < 1)) { + var msg = '{{ _('Please specify columns.') }}'; + this.errorModel.set('columns', msg); + return msg; + } + + return null; + } + }) + }); + } + + return pgBrowser.Nodes['primary_key']; +}); diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/create.sql new file mode 100644 index 0000000..2d9dfa5 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/create.sql @@ -0,0 +1,12 @@ +ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} + ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} PRIMARY KEY {% if data.index %}USING INDEX {{ conn|qtIdent(data.index) }}{% else %} +({% for columnobj in data.columns %}{% if loop.index != 1 %} +, {% endif %}{{ conn|qtIdent(columnobj.column)}}{% endfor %}){% if data.fillfactor %} + + WITH (FILLFACTOR={{data.fillfactor}}){% endif %}{% if data.spcname and data.spcname != "pg_default" %} + + USING INDEX TABLESPACE {{ conn|qtIdent(data.spcname) }}{% endif %}{% endif %}{% if data.condeferrable %} + + DEFERRABLE{% if data.condeferred %} + INITIALLY DEFERRED{% endif%} +{% endif%}; \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/delete.sql new file mode 100644 index 0000000..2096795 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/delete.sql @@ -0,0 +1,3 @@ +{% if data %} +ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} DROP CONSTRAINT {{ conn|qtIdent(data.name) }}{% if cascade%} CASCADE{% endif %}; +{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/get_constraint_cols.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/get_constraint_cols.sql new file mode 100644 index 0000000..4b2fee2 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/get_constraint_cols.sql @@ -0,0 +1,7 @@ +{% for n in range(colcnt|int) %} +{% if loop.index != 1 %} +UNION SELECT pg_get_indexdef({{ cid|string }}, {{ loop.index|string }}, true) AS column +{% else %} +SELECT pg_get_indexdef({{ cid|string }} , {{ loop.index|string }} , true) AS column +{% endif %} +{% endfor %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/get_indices.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/get_indices.sql new file mode 100644 index 0000000..b9cab21 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/get_indices.sql @@ -0,0 +1,3 @@ +SELECT relname FROM pg_class, pg_index +WHERE pg_class.oid=indexrelid +AND indrelid={{ tid }} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/get_oid.sql new file mode 100644 index 0000000..081dc0a --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/get_oid.sql @@ -0,0 +1,7 @@ +SELECT cls.oid, cls.relname as name +FROM pg_index idx +JOIN pg_class cls ON cls.oid=indexrelid +LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_class WHERE relname='pg_constraint') AND dep.deptype='i') +LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid) +WHERE indrelid = {{tid}}::oid +AND contype='p' \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/get_parent.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/get_parent.sql new file mode 100644 index 0000000..a652857 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/get_parent.sql @@ -0,0 +1,7 @@ +SELECT nsp.nspname AS schema, + rel.relname AS table +FROM + pg_class rel +JOIN pg_namespace nsp +ON rel.relnamespace = nsp.oid::int +WHERE rel.oid = {{tid}}::int \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/grant.sql new file mode 100644 index 0000000..0fb0ea5 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/grant.sql @@ -0,0 +1,4 @@ +{% if data.comment %} +COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} + IS {{ data.comment|qtLiteral }}; +{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/nodes.sql new file mode 100644 index 0000000..081dc0a --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/nodes.sql @@ -0,0 +1,7 @@ +SELECT cls.oid, cls.relname as name +FROM pg_index idx +JOIN pg_class cls ON cls.oid=indexrelid +LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_class WHERE relname='pg_constraint') AND dep.deptype='i') +LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid) +WHERE indrelid = {{tid}}::oid +AND contype='p' \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/properties.sql new file mode 100644 index 0000000..dc4b3b8 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/properties.sql @@ -0,0 +1,29 @@ +SELECT DISTINCT ON(cls.relname) cls.oid, + cls.relname as name, + indnatts, + COALESCE(spcname, 'pg_default') as spcname, + CASE contype + WHEN 'p' THEN desp.description + WHEN 'u' THEN desp.description + WHEN 'x' THEN desp.description + ELSE des.description + END AS comment, + condeferrable, + condeferred, + substring(array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor +FROM pg_index idx +JOIN pg_class cls ON cls.oid=indexrelid +JOIN pg_class tab ON tab.oid=indrelid +LEFT OUTER JOIN pg_tablespace ta on ta.oid=cls.reltablespace +JOIN pg_namespace n ON n.oid=tab.relnamespace +JOIN pg_am am ON am.oid=cls.relam +LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_class WHERE relname='pg_constraint') AND dep.deptype='i') +LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid) +LEFT OUTER JOIN pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass) +LEFT OUTER JOIN pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass) +WHERE indrelid = {{tid}}::oid +{% if pkid %} +AND cls.oid = {{pkid}}::oid +{% endif %} +AND contype='p' +ORDER BY cls.relname \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/update.sql new file mode 100644 index 0000000..1cb36b3 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/primary_key/templates/primary_key/sql/update.sql @@ -0,0 +1,22 @@ +{### SQL to update primary key object ###} +{% if data %} +{# ==== To update primary key name ==== #} +{% if data.name != o_data.name %} +ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} + RENAME CONSTRAINT {{ conn|qtIdent(o_data.name) }} TO {{ conn|qtIdent(data.name) }}; +{% endif %} +{# ==== To update primary key tablespace ==== #} +{% if data.spcname and data.spcname != o_data.spcname %} +ALTER INDEX {{ conn|qtIdent(data.schema, data.name) }} + SET TABLESPACE {{ conn|qtIdent(data.spcname) }}; +{% endif %} +{% if data.fillfactor and data.fillfactor != o_data.fillfactor %} +ALTER INDEX {{ conn|qtIdent(data.schema, data.name) }} + SET (FILLFACTOR={{ data.fillfactor }}); +{% endif %} +{# ==== To update primary key comments ==== #} +{% if data.comment and data.comment != o_data.comment %} +COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} + IS {{ data.comment|qtLiteral }}; +{% endif %} +{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/static/img/coll-constraints.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/static/img/coll-constraints.png new file mode 100644 index 0000000000000000000000000000000000000000..d62e13705c50e6c0cf8f19d680053e8643e28751 GIT binary patch literal 314 zcmeAS@N?(olHy`uVBq!ia0vp^0wB!73?$#)eFPFv3GfMV1=2TrO847{Jl`|t`Qpas zn<hS+l=rMY>RGPqvlydizRJ&>B`Om#V}R-yOM?7@862M7NCR>>3p^r=fwTu0yPeFo z12TL)T^vI=t|uoPU||ZF<tgaHG*QsQ!?m&Tq=?3mCu}J#Dx3x@mM}}^iE=5NIWXnk zkpnC4ai%a>@;Gg7=uums=9bIK=Egd~(us-3g@Iv02gfsK^JP^)gH=mhBT7;dOH!?p zi&B9UgOP!ufv%yEu7P2Qk%5(ov6YF5wt=aYfq}(LRXG$5x%nxXX_XKS29{tAAk|g| XW)KahriZQpYGCkm^>bP0l+XkKyyRU} literal 0 HcmV?d00001 diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/templates/constraints/js/constraints.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/templates/constraints/js/constraints.js new file mode 100644 index 0000000..c226138 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/templates/constraints/js/constraints.js @@ -0,0 +1,49 @@ +define( + ['jquery', 'underscore', 'underscore.string', 'pgadmin', + 'pgadmin.browser', 'pgadmin.browser.collection'], +function($, _, S, pgAdmin, pgBrowser) { + + if (!pgBrowser.Nodes['coll-constraints']) { + var databases = pgAdmin.Browser.Nodes['coll-constraints'] = + pgAdmin.Browser.Collection.extend({ + node: 'constraints', + label: '{{ _('Constraints') }}', + type: 'coll-constraints' + }); + }; + + if (!pgBrowser.Nodes['constraints']) { + pgAdmin.Browser.Nodes['constraints'] = pgBrowser.Node.extend({ + type: 'constraints', + label: '{{ _('Constraints') }}', + collection_type: 'coll-constraints', + parent_type: ['table'], + Init: function() { + /* Avoid mulitple registration of menus */ + if (this.initialized) + return; + + this.initialized = true; + + pgBrowser.add_menus([]); + }, + model: pgAdmin.Browser.Node.Model.extend({ + defaults: { + name: undefined, + oid: undefined + }, + schema: [{ + id: 'name', label: '{{ _('Name') }}', type: 'text', + mode: ['properties', 'create', 'edit'] + },{ + id: 'oid', label:'{{ _('Oid') }}', cell: 'string', + type: 'text' , mode: ['properties'] + },{ + id: 'comment', label:'{{ _('Comment') }}', cell: 'string', + type: 'multiline', mode: ['properties', 'create', 'edit'] + }] + }) + }); + } + return pgBrowser.Nodes['constraints']; +}); \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/type.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/type.py new file mode 100644 index 0000000..d75bf2b --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/type.py @@ -0,0 +1,42 @@ +########################################################################## +# +# pgAdmin 4 - PostgreSQL Tools +# +# Copyright (C) 2013 - 2016, The pgAdmin Development Team +# This software is released under the PostgreSQL Licence +# +########################################################################## + +from pgadmin.browser.collection import CollectionNodeModule +from flask import Blueprint + + +class ConstraintRegistry(object): + """ + ConstraintTypeRegistry + + It is more of a registry for difference type of constraints for the tables. + Its job is to initialize to different type of constraint blueprint and + register it with its respective NodeView. + """ + registry = dict() + + def __init__(self, name, con_blueprint, con_nodeview): + if name not in ConstraintRegistry.registry: + + blueprint = con_blueprint(name) + + # TODO:: register the view with the blueprint + con_nodeview.register_node_view(blueprint) + + ConstraintRegistry.registry[name] = { + 'blueprint': blueprint, + 'nodeview': con_nodeview + } + + +class ConstraintTypeModule(CollectionNodeModule): + register = Blueprint.register + + def __init__(self, *args, **kwargs): + super(ConstraintTypeModule, self).__init__(*args, **kwargs)
-- Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers