public inbox for [email protected]
help / color / mirror / Atom feedFrom: Harshal Dhumal <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: Initial foreign key constraint node patch [pgadmin4]
Date: Mon, 11 Apr 2016 18:39:14 +0530
Message-ID: <CAFiP3vyQjXubp8DNxNW1XGvmMfHfJXn5hdfABGCj9JWpMcigmA@mail.gmail.com> (raw)
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
Hi,
PFA initial patch for foreign key constraint.
Note: This patch has dependency on Table, Column node and Index constraint.
--
*Harshal Dhumal*
*Software Engineer *
EenterpriseDB <http://www.enterprisedb.com;
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
Attachments:
[text/x-patch] foreign_key_11_April_V1.patch (78.6K, 3-foreign_key_11_April_V1.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/__init__.py
new file mode 100644
index 0000000..59c6b39
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/__init__.py
@@ -0,0 +1,1032 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+"""Implements Foreign 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 ForeignKeyConstraintModule(ConstraintTypeModule):
+ """
+ class ForeignKeyConstraintModule(CollectionNodeModule)
+
+ A module class for Foreign key constraint node derived from ConstraintTypeModule.
+
+ Methods:
+ -------
+ * __init__(*args, **kwargs)
+ - Method is used to initialize the ForeignKeyConstraintModule 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 = 'foreign_key'
+ COLLECTION_LABEL = _("Foreign Keys")
+
+ def __init__(self, *args, **kwargs):
+ """
+ Method is used to initialize the ForeignKeyConstraintModule and it's base module.
+
+ Args:
+ *args:
+ **kwargs:
+
+ Returns:
+
+ """
+ self.min_ver = None
+ self.max_ver = None
+ super(ForeignKeyConstraintModule, 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 foreign_key, when any of the table node is
+ initialized.
+
+ Returns: node type of the server module.
+ """
+ return table.TableModule.NODE_TYPE
+
+ @property
+ def csssnippets(self):
+ """
+ Returns a snippet of css to include in the page
+ """
+ snippets = [
+ render_template(
+ "browser/css/collection.css",
+ node_type=self.node_type,
+ _=_
+ ),
+ render_template(
+ "foreign_key/css/foreign_key.css",
+ node_type=self.node_type,
+ _=_
+ )
+ ]
+
+ for submodule in self.submodules:
+ snippets.extend(submodule.csssnippets)
+
+ return snippets
+
+blueprint = ForeignKeyConstraintModule(__name__)
+
+
+class ForeignKeyConstraintView(PGChildNodeView):
+ """
+ class ForeignKeyConstraintView(PGChildNodeView)
+
+ A view class for Foreign key constraint node derived from PGChildNodeView. This class is
+ responsible for all the stuff related to view like creating, updating Foreign key constraint
+ node, showing properties, showing sql in sql pane.
+
+ Methods:
+ -------
+ * __init__(**kwargs)
+ - Method is used to initialize the ForeignKeyConstraintView 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 foreign 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 foreign key constraint nodes.
+
+ * nodes()
+ - This function returns child node within that collection.
+ Here return all foreign key constraint node as http response.
+
+ * get_nodes()
+ - returns all foreign key constraint nodes' list.
+
+ * properties()
+ - This function will show the properties of the selected foreign key.
+
+ * update()
+ - This function will update the data for the selected foreign key.
+
+ * msql()
+ - This function is used to return modified SQL for the selected foreign 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 foreign key.
+
+ * get_indices():
+ - This function returns indices for current table.
+
+ """
+
+ node_type = 'foreign_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': 'fkid'}
+ ]
+
+ 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'}],
+ 'validate': [{'get': 'validate_foreign_key'}],
+ 'get_coveringindex': [{'get': 'get_coveringindex'}, {'get': 'get_coveringindex'}],
+ })
+
+ 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(
+ "foreign_key/js/foreign_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 = 'foreign_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
+
+ def end_transaction(self):
+ SQL = render_template(
+ "/".join([self.template_path, 'end.sql']))
+ # End transaction if any.
+ self.conn.execute_scalar(SQL)
+
+ @check_precondition
+ def properties(self, gid, sid, did, scid, tid, fkid=None):
+ """
+ This function is used to list all the foreign key
+ nodes within that collection.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ fkid: Foreign key constraint ID
+
+ Returns:
+
+ """
+ try:
+ sql = render_template("/".join([self.template_path, 'properties.sql']),
+ tid=tid, fkid=fkid)
+
+ 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']),
+ tid=tid,
+ keys=zip(result['confkey'], result['conkey']),
+ confrelid=result['confrelid'])
+
+ status, res = self.conn.execute_dict(sql)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ columns = []
+ cols = []
+ for row in res['rows']:
+ columns.append({"local_column": row['conattname'],
+ "references": result['confrelid'],
+ "referenced": row['confattname']})
+ cols.append(row['conattname'])
+
+ result['columns'] = columns
+
+ if fkid:
+ result['coveringindex'] = self.search_coveringindex(tid, cols)
+ result['autoindex'] = False
+
+ return ajax_response(
+ response=result,
+ status=200
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def list(self, gid, sid, did, scid, tid, fkid=None):
+ """
+ This function returns all foreign 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
+ fkid: Foreign key constraint ID
+
+ Returns:
+
+ """
+ try:
+ res = self.get_node_list(gid, sid, did, scid, tid, fkid)
+ 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, fkid=None):
+ """
+ This function returns all foreign 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
+ fkid: Foreign 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, fkid=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
+ fkid: Foreign key constraint ID
+
+ Returns:
+
+ """
+ try:
+ res = self.get_nodes(gid, sid, did, scid, tid, fkid)
+ 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, fkid=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
+ fkid: Foreign 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']:
+ if row["convalidated"]:
+ icon = "icon-foreign_key_no_validate"
+ valid = False
+ else:
+ icon = "icon-foreign_key"
+ valid = True
+ res.append(
+ self.blueprint.generate_browser_node(
+ row['oid'],
+ tid,
+ row['name'],
+ icon=icon,
+ valid=valid
+ ))
+ return res
+
+ @check_precondition
+ def create(self, gid, sid, did, scid, tid, fkid=None):
+ """
+ This function will create a foreign key.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ fkid: Foreign key constraint ID
+
+ Returns:
+
+ """
+ required_args = ['columns']
+
+ data = request.form if request.form else json.loads(request.data.decode())
+
+ for arg in required_args:
+ if arg not in data:
+ return make_json_response(
+ status=400,
+ success=0,
+ errormsg=_(
+ "Couldn't find required parameter (%s)." % str(arg)
+ )
+ )
+ elif isinstance(data[arg], list) and len(data[arg]) < 1:
+ return make_json_response(
+ status=400,
+ success=0,
+ errormsg=_(
+ "Couldn't find required parameter (%s)." % str(arg)
+ )
+ )
+
+ data['schema'] = self.schema
+ data['table'] = self.table
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_parent.sql']),
+ tid=data['columns'][0]['references'])
+ status, res = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ data['remote_schema'] = res['rows'][0]['schema']
+ data['remote_table'] = res['rows'][0]['table']
+
+ if 'name' not in data or data['name'] == "":
+ SQL = render_template(
+ "/".join([self.template_path, 'begin.sql']))
+ # Start transaction.
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ self.end_transaction()
+ return internal_server_error(errormsg=res)
+
+ # The below SQL will execute CREATE DDL only
+ SQL = render_template(
+ "/".join([self.template_path, 'create.sql']),
+ data=data, conn=self.conn
+ )
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ self.end_transaction()
+ return internal_server_error(errormsg=res)
+
+ if 'name' not in data or data['name'] == "":
+ sql = render_template(
+ "/".join([self.template_path,
+ 'get_oid_with_transaction.sql']),
+ tid=tid)
+
+ status, res = self.conn.execute_dict(sql)
+ if not status:
+ self.end_transaction()
+ return internal_server_error(errormsg=res)
+
+ self.end_transaction()
+
+ data['name'] = res['rows'][0]['name']
+
+ else:
+ sql = render_template("/".join([self.template_path, 'get_oid.sql']), name=data['name'])
+ status, res = self.conn.execute_dict(sql)
+ if not status:
+ self.end_transaction()
+ return internal_server_error(errormsg=res)
+
+ if res['rows'][0]["convalidated"]:
+ icon = "icon-foreign_key_no_validate"
+ valid = False
+ else:
+ icon = "icon-foreign_key"
+ valid = True
+
+ sql = render_template("/".join([self.template_path, 'grant.sql']), data=data, conn=self.conn)
+ sql = sql.strip('\n').strip(' ')
+
+ if sql != '':
+ status, result = self.conn.execute_scalar(sql)
+ if not status:
+ self.end_transaction()
+ return internal_server_error(errormsg=result)
+
+ if data['autoindex']:
+ sql = render_template(
+ "/".join([self.template_path, 'create_index.sql']),
+ data=data, conn=self.conn)
+ sql = sql.strip('\n').strip(' ')
+
+ if sql != '':
+ status, idx_res = self.conn.execute_scalar(sql)
+ if not status:
+ self.end_transaction()
+ return internal_server_error(errormsg=idx_res)
+
+ return jsonify(
+ node=self.blueprint.generate_browser_node(
+ res['rows'][0]['oid'],
+ tid,
+ data['name'],
+ valid=valid,
+ icon=icon
+ )
+ )
+
+ except Exception as e:
+ self.end_transaction()
+ return make_json_response(
+ status=400,
+ success=0,
+ errormsg=e
+ )
+
+ @check_precondition
+ def update(self, gid, sid, did, scid, tid, fkid=None):
+ """
+ This function will update the data for the selected
+ foreign key.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ fkid: Foreign 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, fkid)
+ 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']), name=data['name'])
+ status, res = self.conn.execute_dict(sql)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ if res['rows'][0]["convalidated"]:
+ icon = "icon-foreign_key_no_validate"
+ valid = False
+ else:
+ icon = "icon-foreign_key"
+ valid = True
+
+ return make_json_response(
+ success=1,
+ info="Foreign key updated",
+ data={
+ 'id': res['rows'][0]['oid'],
+ 'tid': tid,
+ 'scid': scid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did,
+ 'icon':icon,
+ 'valid':valid
+ }
+ )
+ else:
+ return make_json_response(
+ success=1,
+ info="Nothing to update",
+ data={
+ 'id': fkid,
+ '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, fkid=None):
+ """
+ This function will delete an existing foreign key.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ fkid: Foreign 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_name.sql']), fkid=fkid)
+ 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=_("Foreign key dropped."),
+ data={
+ 'id': fkid,
+ '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, fkid=None):
+ """
+ This function returns modified SQL for the selected
+ foreign key.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ fkid: Foreign 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, fkid)
+ 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, fkid=None):
+ """
+ This function will generate sql from model data.
+
+ Args:
+ data: Contains the data of the selected foreign key constraint.
+ tid: Table ID.
+ fkid: Foreign key constraint ID
+
+ Returns:
+
+ """
+ if fkid is not None:
+ sql = render_template("/".join([self.template_path, 'properties.sql']), tid=tid, fkid=fkid)
+ status, res = self.conn.execute_dict(sql)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ old_data = res['rows'][0]
+ required_args = ['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 = ['columns']
+
+ for arg in required_args:
+ if arg not in data:
+ return _('-- definition incomplete')
+ elif isinstance(data[arg], list) and len(data[arg]) < 1:
+ return _('-- definition incomplete')
+
+ if data['autoindex'] and ('coveringindex' not in data or
+ data['coveringindex'] == ''):
+ return _('-- definition incomplete')
+
+ SQL = render_template("/".join([self.template_path,
+ 'get_parent.sql']),
+ tid=data['columns'][0]['references'])
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=rset)
+
+ data['remote_schema'] = rset['rows'][0]['schema']
+ data['remote_table'] = rset['rows'][0]['table']
+
+ 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)
+
+ if data['autoindex']:
+ sql += render_template(
+ "/".join([self.template_path, 'create_index.sql']),
+ data=data, conn=self.conn)
+ return sql
+
+ @check_precondition
+ def sql(self, gid, sid, did, scid, tid, fkid=None):
+ """
+ This function generates sql to show in the sql pane for the selected
+ foreign key.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ fkid: Foreign key constraint ID
+
+ Returns:
+
+ """
+ try:
+ SQL = render_template(
+ "/".join([self.template_path, 'properties.sql']),
+ tid=tid, conn=self.conn, fkid=fkid)
+ 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']),
+ tid=tid,
+ keys=zip(data['confkey'], data['conkey']),
+ confrelid=data['confrelid'])
+
+ status, res = self.conn.execute_dict(sql)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ columns = []
+ for row in res['rows']:
+ columns.append({"local_column": row['conattname'],
+ "references": data['confrelid'],
+ "referenced": row['confattname']})
+
+ data['columns'] = columns
+
+ SQL = render_template("/".join([self.template_path,
+ 'get_parent.sql']),
+ tid=data['columns'][0]['references'])
+ status, res = self.conn.execute_2darray(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ data['remote_schema'] = res['rows'][0]['schema']
+ data['remote_table'] = res['rows'][0]['table']
+
+ 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 dependents(self, gid, sid, did, scid, tid, fkid=None):
+ """
+ This function gets the dependents and returns an ajax response
+ for the event trigger node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ etid: Event trigger ID
+ """
+ dependents_result = self.get_dependents(self.conn, fkid)
+ return ajax_response(
+ response=dependents_result,
+ status=200
+ )
+
+ @check_precondition
+ def dependencies(self, gid, sid, did, scid, tid, fkid=None):
+ """
+ This function gets the dependencies and returns an ajax response
+ for the event trigger node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ etid: Event trigger ID
+ """
+ dependencies_result = self.get_dependencies(self.conn, fkid)
+ return ajax_response(
+ response=dependencies_result,
+ status=200
+ )
+
+ @check_precondition
+ def validate_foreign_key(self, gid, sid, did, scid, tid, fkid):
+ """
+
+ Args:
+ gid:
+ sid:
+ did:
+ scid:
+ tid:
+ fkid:
+
+ Returns:
+
+ """
+ data = {}
+ try:
+ data['schema'] = self.schema
+ data['table'] = self.table
+ sql = render_template("/".join([self.template_path, 'get_name.sql']), fkid=fkid)
+ status, res = self.conn.execute_scalar(sql)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ data['name'] = res
+ sql = render_template("/".join([self.template_path, 'validate.sql']), data=data)
+ status, res = self.conn.execute_dict(sql)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info=_("Foreign key updated."),
+ data={
+ 'id': fkid,
+ 'tid': tid,
+ 'scid': scid,
+ 'did': did
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def get_coveringindex(self, gid, sid, did, scid, tid, fkid=None):
+ """
+
+ Args:
+ gid:
+ sid:
+ did:
+ scid:
+ tid:
+ fkid:
+
+ Returns:
+
+ """
+
+ data = request.args if request.args else None
+ index = None
+ try:
+ if data and 'cols' in data:
+ cols = set(json.loads(data['cols']))
+ index = self.search_coveringindex(tid, cols)
+
+ return make_json_response(
+ data=index,
+ status=200
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ def search_coveringindex(self, tid, cols):
+ """
+
+ Args:
+ tid: Table id
+ cols: column list
+
+ Returns:
+
+ """
+
+ cols = set(cols)
+ SQL = render_template("/".join([self.template_path,
+ 'get_constraints.sql']),
+ tid=tid)
+ status, constraints = self.conn.execute_dict(SQL)
+
+ if not status:
+ raise Exception(constraints)
+
+ for costrnt in constraints['rows']:
+
+ sql = render_template(
+ "/".join([self.template_path, 'get_cols.sql']),
+ cid=costrnt['oid'],
+ colcnt=costrnt['indnatts'])
+ status, rest = self.conn.execute_dict(sql)
+
+ if not status:
+ return internal_server_error(errormsg=rest)
+
+ indexcols = set()
+ for r in rest['rows']:
+ indexcols.add(r['column'].strip('"'))
+
+ if len(cols - indexcols) == len(indexcols - cols) == 0:
+ return costrnt["idxname"]
+
+ return None
+
+constraint = ConstraintRegistry(
+ 'foreign_key', ForeignKeyConstraintModule, ForeignKeyConstraintView
+ )
+ForeignKeyConstraintView.register_node_view(blueprint)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/static/img/foreign_key.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/static/img/foreign_key.png
new file mode 100644
index 0000000000000000000000000000000000000000..b3605500439db72c2c43879abb61a24c956e1bf5
GIT binary patch
literal 299
zcmeAS@N?(olHy`uVBq!ia0vp^0wBx*Bp9q_EZ7UAm`Z~Df*BafCZDwc@shmVT^JZv
z^(q?yd7K3vk;OpT1B~5HX4?T7@t!V@ArhC96BcmYxO{&<`^@{hp1!(zcCE*!Xt{ek
z>#~zoS#lB*UhMz1;M236-R60Bchvnn_1J5|fxo9_r|aE`sZD=6Ra!x`dfT1-l|Ns#
zc?qz5Y;4QhSCW{_ZI`Xd&M@z_Ipcx{mt%ldsg}4#l%yn<q*^5xr2;7iBLhPNT|*;X
z1H%v_11l3_D-%;~15+ylgL&`tR-tIf%}>cptAuDUumo!Wsy4AQFoS3~H9d3<Py>Uf
LtDnm{r-UW|7b#?7
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/static/img/foreign_key_no_validate.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/static/img/foreign_key_no_validate.png
new file mode 100644
index 0000000000000000000000000000000000000000..975561bcf7237d5e99e9d42ff705db67f1bb2fdb
GIT binary patch
literal 618
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GXl47?}cmLR^8|J$v@__xDejFyZ2b
zi$8z-{QB`zYisM4En6<0I(I!O=_mukh49ENQx_g}_r9B%`S9VxT^qNb3Jm#HUVhl!
zdv;s@UR$S+;o(=WUVZT3K}|{3x3aQtMMYop^7dFed<+YF@9+Q4!}I?A`&DI?UvqQ6
zWMq6!O8O8Q`rZ#H?s56j<qIdyd`?XK7#;l~B;>uX-#d5r&6_u0x^(Hn`SXVl9ln*C
z`XLx7?)%QoZIzC3Utiz$4V$k4O$ZKt@8k2%)%A^?{c8)0-OjEbf`Z<Ad%tyYeQj?3
z%Fytow)Tg>z;|9=Z=GFcI(xm;(te?;`dmTbt&{T+H?Q>ONi$u%pUW#W=hVD!YnKOx
z0%J*#UoeBivm0qZ4rhT!WHFHT0Ash4*>*t2d`}n05Q)ocd!Gt51&FlhcQmc(yxVnG
z_QqY=_y4tuT#tzt)X$mu_uM%>#d^WRQWlF}KCf&v?%p7fwDq9h|E9y52U)it=ayGp
zV=v&MA>$~rv4TnDgeT|961JN$^VB39^+a5!oSK^T_Oj!Ts>L_67GHkr`19O1cITdB
zlVAFHub7p*F(z^E%~veD|1!GIIVYasa7c9fZ)Shnk3kvpt3v;?HQy7Q(A;;*1?W`O
z64!{5l*E!$tK_0oAjM#0U}&goV61Ck5n^a$WoT|?V5x0jW@TV-`Pqsj6b-rgDVb@N
WxHT-=EpQL0fx*+&&t;ucLK6V@6Aj$}
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/css/foreign_key.css b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/css/foreign_key.css
new file mode 100644
index 0000000..aeaff11
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/css/foreign_key.css
@@ -0,0 +1,12 @@
+.icon-foreign_key {
+ background-image: url('{{ url_for('NODE-foreign_key.static', filename='img/foreign_key.png') }}') !important;
+ background-repeat: no-repeat;
+ align-content: center;
+ vertical-align: middle;
+ height: 1.3em;
+}
+
+.icon-foreign_key_no_validate {
+ background-image: url('{{ url_for('NODE-foreign_key.static', filename='img/foreign_key_no_validate.png') }}') !important;
+ border-radius: 10px
+}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/js/foreign_key.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/js/foreign_key.js
new file mode 100644
index 0000000..543e7e3
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/js/foreign_key.js
@@ -0,0 +1,777 @@
+define(
+ ['jquery', 'underscore', 'underscore.string', 'pgadmin',
+ 'pgadmin.browser', 'alertify', 'pgadmin.browser.collection'],
+function($, _, S, pgAdmin, pgBrowser, Alertify) {
+
+ var formatNode = function(opt) {
+ if (!opt.id) {
+ return opt.text;
+ }
+
+ var optimage = $(opt.element).data('image');
+
+ if(!optimage){
+ return opt.text;
+ } else {
+ return $(
+ '<span><span class="wcTabIcon ' + optimage + '"/>' + opt.text + '</span>'
+ );
+ }
+ };
+
+ var ForeignKeyColumnModel = pgBrowser.Node.Model.extend({
+ defaults: {
+ local_column: undefined,
+ references: undefined,
+ referenced: undefined
+ },
+ schema: [{
+ id: 'local_column', label:'Local', type:'text', editable: false,
+ cellHeaderClasses: 'width_percent_50', cell:'string'
+ },{
+ id: 'referenced', label:'Referenced', type: 'text', editable: false,
+ cell:'string', cellHeaderClasses: 'width_percent_50'
+ }]
+ });
+
+ var ForeignKeyColumnControl = Backform.ForeignKeyColumnControl =
+ Backform.UniqueColCollectionControl.extend({
+
+ initialize: function(opts) {
+ Backform.UniqueColCollectionControl.prototype.initialize.apply(
+ this, arguments
+ );
+
+ var self = this,
+ node = self.field.get('node').type,
+ headerSchema = [{
+ id: 'local_column', label:'', type:'text',
+ node: 'column', control: Backform.NodeListByNameControl,
+ cache_level: 'server',
+ select2: {
+ allowClear: false, width: 'style',
+ placeholder: 'Select column'
+ }, first_empty: !self.model.isNew(),
+ version_compatible: self.field.get('version_compatible'),
+ disabled: function(m) {
+ return !self.model.isNew();
+ }
+ },{
+ id: 'references', label:'', type: 'text', cache_level: 'server',
+ select2: {
+ allowClear: false, width: 'style',
+ placeholder: 'Select foreign table',
+ }, first_empty: true,
+ control: Backform.NodeListByNameControl.extend({
+ formatter: Backform.ControlFormatter
+ }),
+ url: 'all_tables', node: 'table',
+ version_compatible: self.field.get('version_compatible'),
+ disabled: function(m) {
+ return !self.model.isNew();
+ },
+ transform: function(rows) {
+ var res = [];
+ _.each(rows, function(r) {
+ res.push({
+ 'value': r.value,
+ 'image': "icon-table",
+ 'label': r.label
+ });
+ });
+ return res;
+ }
+ },{
+ id: 'referenced', label:'', type: 'text', cache_level: 'server',
+ transform: function(rows) {
+ var res = [];
+ _.each(rows, function(r) {
+ res.push({
+ 'value': r.name,
+ 'image': 'icon-column',
+ 'label': r.name
+ });
+ });
+ return res;
+ },
+ control: Backform.Select2Control.extend({
+ formatter: Backform.ControlFormatter,
+
+ template: Backform.NodeAjaxOptionsControl.prototype.template,
+
+ render: function() {
+ var self = this,
+ url = self.field.get('url') || self.defaults.url,
+ m = self.model,
+ tid = m.get('references');
+
+ // Clear any existing value before setting new options.
+ m.set(self.field.get('name'), null, {silent: true});
+
+ if (url && !_.isUndefined(tid) && !_.isNull(tid) && tid != '') {
+ var node = this.field.get('schema_node'),
+ node_info = this.field.get('node_info'),
+ full_url = node.generate_url.apply(
+ node, [
+ null, url, this.field.get('node_data'),
+ this.field.get('url_with_id') || false, node_info
+ ]),
+ data = [];
+
+ if (this.field.get('version_compatible')) {
+ m.trigger('pgadmin:view:fetching', m, self.field);
+ $.ajax({
+ async: false,
+ data : {tid:tid},
+ url: full_url,
+ success: function(res) {
+ data = res.data;
+ },
+ error: function() {
+ m.trigger('pgadmin:view:fetch:error', m, self.field);
+ }
+ });
+ m.trigger('pgadmin:view:fetched', m, self.field);
+ }
+ /*
+ * Transform the data
+ */
+ transform = this.field.get('transform') || self.defaults.transform;
+ if (transform && _.isFunction(transform)) {
+ // We will transform the data later, when rendering.
+ // It will allow us to generate different data based on the
+ // dependencies.
+ self.field.set('options', transform.bind(self, data));
+ } else {
+ self.field.set('options', data);
+ }
+ } else {
+ self.field.set('options', []);
+ }
+ Backform.Select2Control.prototype.render.apply(this, arguments);
+ return this;
+ }
+ }), url: 'get_columns', first_empty: true,
+ select2: {
+ width: "style",
+ placeholder: 'Select column',
+ templateResult: formatNode,
+ templateSelection: formatNode
+ },
+ deps:['references'], node: 'table',
+ version_compatible: self.field.get('version_compatible'),
+ disabled: function(m) {
+ return !self.model.isNew();
+ }
+ }],
+ headerDefaults = {local_column: null,
+ references: null,
+ referenced:null},
+ gridCols = ['local_column', 'references', 'referenced'];
+
+ if (!self.model.isNew()) {
+ var column = self.collection.first();
+ if (column) {
+ headerDefaults["references"] = column.get("references");
+ }
+ }
+
+ self.headerData = new (Backbone.Model.extend({
+ defaults: headerDefaults,
+ schema: headerSchema
+ }))({});
+
+ var headerGroups = Backform.generateViewSchema(
+ self.field.get('node_info'), self.headerData, 'create',
+ node, self.field.get('node_data')
+ ),
+ fields = [];
+
+ _.each(headerGroups, function(o) {
+ fields = fields.concat(o.fields);
+ });
+
+ self.headerFields = new Backform.Fields(fields);
+ self.gridSchema = Backform.generateGridColumnsFromModel(
+ null, ForeignKeyColumnModel, 'edit', gridCols
+ );
+
+ self.controls = [];
+ self.listenTo(self.headerData, "change", self.headerDataChanged);
+ self.listenTo(self.headerData, "select2", self.headerDataChanged);
+ self.listenTo(self.collection, "add", self.onAddorRemoveColumns);
+ self.listenTo(self.collection, "remove", self.onAddorRemoveColumns);
+ },
+
+ generateHeader: function(data) {
+ var header = [
+ '<div class="subnode-header-form">',
+ ' <div class="container-fluid">',
+ ' <div class="row">',
+ ' <div class="col-md-4">',
+ ' <label class="control-label"><%-column_label%></label>',
+ ' </div>',
+ ' <div class="col-md-4" header="local_column"></div>',
+ ' <div class="col-md-4">',
+ ' <button class="btn-sm btn-default add" <%=canAdd ? "" : "disabled=\'disabled\'"%> ><%-add_label%></buttton>',
+ ' </div>',
+ ' </div>',
+ ' <div class="row">',
+ ' <div class="col-md-4">',
+ ' <label class="control-label"><%-references_label%></label>',
+ ' </div>',
+ ' <div class="col-md-4" header="references"></div>',
+ ' </div>',
+ ' <div class="row">',
+ ' <div class="col-md-4">',
+ ' <label class="control-label"><%-referenced_label%></label>',
+ ' </div>',
+ ' <div class="col-md-4" header="referenced"></div>',
+ ' </div>',
+ ' </div>',
+ '</div>',].join("\n")
+
+ _.extend(data, {
+ column_label: '{{ _('Local column')}}',
+ add_label: '{{ _('ADD')}}',
+ references_label: '{{ _('References')}}',
+ referenced_label: '{{ _('Referencing')}}'
+ });
+
+ var self = this,
+ headerTmpl = _.template(header),
+ $header = $(headerTmpl(data)),
+ controls = this.controls;
+
+ this.headerFields.each(function(field) {
+ var control = new (field.get("control"))({
+ field: field,
+ model: self.headerData
+ });
+
+ $header.find('div[header="' + field.get('name') + '"]').append(
+ control.render().$el
+ );
+
+ controls.push(control);
+ });
+
+ // We should not show add but in properties mode
+ if (data.mode == 'properties') {
+ $header.find("button.add").remove();
+ }
+
+ self.$header = $header;
+
+ return $header;
+ },
+
+ events: _.extend(
+ {}, Backform.UniqueColCollectionControl.prototype.events,
+ {'click button.add': 'addColumns'}
+ ),
+
+ showGridControl: function(data) {
+
+ var self = this,
+ titleTmpl = _.template([
+ "<div class='subnode-header'>",
+ "<label class='control-label'><%-label%></label>",
+ "</div>"].join("\n")),
+ $gridBody =
+ $("<div class='pgadmin-control-group backgrid form-group col-xs-12 object subnode'></div>").append(
+ titleTmpl({label: data.label})
+ );
+
+ $gridBody.append(self.generateHeader(data));
+
+ var gridSchema = _.clone(this.gridSchema);
+
+ // Insert Delete Cell into Grid
+ if (data.disabled == false && data.canDelete) {
+ gridSchema.columns.unshift({
+ name: "pg-backform-delete", label: "",
+ cell: Backgrid.Extension.DeleteCell,
+ editable: false, cell_priority: -1
+ });
+ }
+
+ // Initialize a new Grid instance
+ var grid = self.grid = new Backgrid.Grid({
+ columns: gridSchema.columns,
+ collection: self.collection,
+ className: "backgrid table-bordered"
+ });
+ self.$grid = grid.render().$el;
+
+ $gridBody.append(self.$grid);
+
+ setTimeout(function() {
+ self.headerData.set({
+ 'local_column':
+ self.$header.find(
+ 'div[header="local_column"] select option:first'
+ ).val(),
+ 'referenced':
+ self.$header.find(
+ 'div[header="referenced"] select option:first'
+ ).val(),
+ 'references':
+ self.$header.find(
+ 'div[header="references"] select option:first'
+ ).val()
+ }, {silent:true}
+ );
+ }, 10);
+
+ // Render node grid
+ return $gridBody;
+ },
+
+ headerDataChanged: function() {
+ var self = this, val,
+ data = this.headerData.toJSON(),
+ inSelected = false,
+ checkVars = ['local_column', 'referenced'];
+
+ if (!self.$header) {
+ return;
+ }
+
+ if (self.control_data.canAdd) {
+ self.collection.each(function(m) {
+ if (!inSelected) {
+ _.each(checkVars, function(v) {
+ if (!inSelected) {
+ val = m.get(v);
+ inSelected = ((
+ (_.isUndefined(val) || _.isNull(val)) &&
+ (_.isUndefined(data[v]) || _.isNull(data[v]))
+ ) ||
+ (val == data[v]));
+ }
+ });
+ }
+ });
+ }
+ else {
+ inSelected = true;
+ }
+
+ self.$header.find('button.add').prop('disabled', inSelected);
+ },
+
+ addColumns: function(ev) {
+ ev.preventDefault();
+ var self = this,
+ local_column = self.headerData.get('local_column'),
+ referenced = self.headerData.get('referenced');
+
+ if (!local_column || local_column == '' ||
+ !referenced || referenced =='') {
+ return false;
+ }
+
+ var m = new (self.field.get('model'))(
+ self.headerData.toJSON()),
+ coll = self.model.get(self.field.get('name'));
+
+ coll.add(m);
+
+ var idx = coll.indexOf(m);
+
+ // idx may not be always > -1 because our UniqueColCollection may
+ // remove 'm' if duplicate value found.
+ if (idx > -1) {
+ self.$grid.find('.new').removeClass('new');
+
+ var newRow = self.grid.body.rows[idx].$el;
+
+ newRow.addClass("new");
+ $(newRow).pgMakeVisible('backform-tab');
+ } else {
+ delete m;
+ }
+
+ return false;
+ },
+
+ onAddorRemoveColumns: function() {
+ var self = this;
+
+ // Wait for collection to be updated before checking for the button to be
+ // enabled, or not.
+ setTimeout(function() {
+ if (self.collection.length > 0) {
+ self.$header.find(
+ 'div[header="references"] select'
+ ).prop('disabled', true);
+ } else {
+ self.$header.find(
+ 'div[header="references"] select'
+ ).prop('disabled', false);
+ }
+
+ self.headerDataChanged();
+ self.getCoveringIndex();
+ }, 10);
+ },
+
+ getCoveringIndex: function() {
+
+ var self = this,
+ url = 'get_coveringindex',
+ m = self.model
+ cols = [],
+ coveringindex = null;
+
+ self.collection.each(function(m){
+ cols.push(m.get('local_column'));
+ })
+
+ if (cols.length > 0) {
+ var node = this.field.get('schema_node'),
+ node_info = this.field.get('node_info'),
+ full_url = node.generate_url.apply(
+ node, [
+ null, url, this.field.get('node_data'),
+ this.field.get('url_with_id') || false, node_info
+ ]);
+
+ if (this.field.get('version_compatible')) {
+ m.trigger('pgadmin:view:fetching', m, self.field);
+ $.ajax({
+ async: false,
+ data : {cols:JSON.stringify(cols)},
+ url: full_url,
+ success: function(res) {
+ coveringindex = res.data;
+ },
+ error: function() {
+ m.trigger('pgadmin:view:fetch:error', m, self.field);
+ }
+ });
+ m.trigger('pgadmin:view:fetched', m, self.field);
+ }
+ }
+
+ if (coveringindex) {
+ m.set('hasindex', true);
+ m.set('autoindex', false);
+ m.set('coveringindex', coveringindex);
+ } else {
+ m.set('coveringindex', null);
+ m.set('autoindex', true);
+ m.set('hasindex', false);
+ }
+ },
+
+ remove: function() {
+ /*
+ * Stop listening the events registered by this control.
+ */
+ this.stopListening(this.headerData, "change", this.headerDataChanged);
+ this.listenTo(this.headerData, "select2", this.headerDataChanged);
+ this.listenTo(this.collection, "remove", this.onRemoveVariable);
+
+ ForeignKeyColumnControl.__super__.remove.apply(this, arguments);
+
+ // Remove the header model
+ delete (this.headerData);
+
+ }
+ });
+
+ // Extend the browser's collection class for foreign key collection
+ /*if (!pgBrowser.Nodes['coll-foreign_key']) {
+ var databases = pgAdmin.Browser.Nodes['coll-foreign_key'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'foreign_key',
+ label: '{{ _('Foreign key') }}',
+ type: 'coll-constraints',
+ });
+ };*/
+
+ // Extend the browser's node class for foreign key node
+ if (!pgBrowser.Nodes['foreign_key']) {
+ pgAdmin.Browser.Nodes['foreign_key'] = pgBrowser.Node.extend({
+ type: 'foreign_key',
+ label: '{{ _('Foreign key') }}',
+ collection_type: 'coll-constraints',
+ hasSQL: true,
+ hasDepends: false,
+ parent_type: 'table',
+ canDrop: true,
+ canDropCascade: true,
+ hasDepends: true,
+ Init: function() {
+ /* Avoid multiple registration of menus */
+ if (this.initialized)
+ return;
+
+ this.initialized = true;
+
+ pgBrowser.add_menus([{
+ name: 'create_foreign_key_on_coll', node: 'coll-constraints', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Foreign key...') }}',
+ icon: 'wcTabIcon icon-foreign_key', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'validate_foreign_key', node: 'foreign_key', module: this,
+ applies: ['object', 'context'], callback: 'validate_foreign_key',
+ category: 'validate', priority: 4, label: '{{ _('Validate foreign key') }}',
+ icon: 'fa fa-link', enable : 'is_not_valid'
+ }
+ ]);
+ },
+ is_not_valid: function(node) {
+ return (node && !node.valid);
+ },
+ callbacks: {
+ validate_foreign_key: function(args) {
+ var input = args || {};
+ obj = this,
+ t = pgBrowser.tree,
+ i = input.item || t.selected(),
+ d = i && i.length == 1 ? t.itemData(i) : undefined;
+
+ if (!d) {
+ return false;
+ }
+ var data = d;
+ $.ajax({
+ url: obj.generate_url(i, 'validate', d, true),
+ type:'GET',
+ success: function(res) {
+ if (res.success == 1) {
+ Alertify.success("{{ _('" + res.info + "') }}");
+ t.removeIcon(i);
+ data.valid = true;
+ data.icon = 'icon-foreign_key';
+ t.addIcon(i, {icon: data.icon});
+ setTimeout(function() {t.deselect(i);}, 10);
+ setTimeout(function() {t.select(i);}, 100);
+ }
+ },
+ error: function(xhr, status, error) {
+ try {
+ var err = $.parseJSON(xhr.responseText);
+ if (err.success == 0) {
+ msg = S('{{ _(' + err.errormsg + ')}}').value();
+ Alertify.error("{{ _('" + err.errormsg + "') }}");
+ }
+ } catch (e) {}
+ t.unload(i);
+ }
+ });
+
+ return false;
+ }
+ },
+ // Define the model for foreign key node
+ model: pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ name: undefined,
+ oid: undefined,
+ comment: undefined,
+ condeferrable: undefined,
+ condeferred: undefined,
+ confmatchtype: undefined,
+ convalidated: undefined,
+ columns: undefined,
+ confupdtype: "a",
+ confdeltype: "a",
+ autoindex: true,
+ coveringindex: undefined,
+ hasindex:undefined
+ },
+ toJSON: function () {
+ var d = pgAdmin.Browser.Node.Model.prototype.toJSON.apply(this, arguments);
+ delete d.hasindex;
+ return d;
+ },
+ // Define the schema for the foreign 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: 'condeferrable', label: '{{ _('Deferrable') }}',
+ type: 'switch', group: '{{ _('Definition') }}',
+ disabled: function(m) {
+ // We can't update condeferrable of existing foreign key.
+ return !m.isNew();
+ }
+ },{
+ id: 'condeferred', label: '{{ _('Deferred') }}',
+ type: 'switch', group: '{{ _('Definition') }}',
+ deps: ['condeferrable'],
+ disabled: function(m) {
+ // We can't update condeferred of existing foreign 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;
+ }
+ }
+ },{
+ id: 'confmatchtype', label: '{{ _('Match type') }}',
+ type: 'switch', group: '{{ _('Definition') }}',
+ options: {
+ onText: 'FULL',
+ offText: 'SIMPLE',
+ },disabled: function(m) {
+ // We can't update condeferred of existing foreign key.
+ return !m.isNew();
+ }
+ },{
+ id: 'convalidated', label: "{{ _("Don't validate") }}",
+ type: 'switch', group: '{{ _('Definition') }}',
+ options: {
+ onText: 'Yes',
+ offText: 'No'
+ },disabled: function(m) {
+ // We can't update condeferred of existing foreign key.
+ return !(m.isNew() || m.get("convalidated"));
+ }
+ },{
+ id: 'autoindex', label: '{{ _('Auto FK index') }}',
+ type: 'switch', group: '{{ _('Definition') }}',
+ deps: ['name', 'hasindex'],
+ options: {
+ onText: 'Yes',
+ offText: 'No',
+ },disabled: function(m) {
+
+ if (!m.isNew()) {
+ return true;
+ }
+
+ var name = m.get('name'),
+ oldindex = 'fki_'+m.previous ('name'),
+ index = m.get('coveringindex');
+
+ if (m.get('hasindex')) {
+ return true;
+ } else if (m.get('autoindex') && !_.isUndefined(name) && !_.isNull(name) &&
+ name != '' && (_.isUndefined(index) || _.isNull(index) ||
+ index == '' || index == oldindex)) {
+ var newIndex = 'fki_' + name;
+ m.set('coveringindex', newIndex);
+ return false;
+ }
+ }
+ },{
+ id: 'coveringindex', label: '{{ _('Covering index') }}', type: 'text',
+ mode: ['properties', 'create', 'edit'], group: '{{ _('Definition') }}',
+ deps:['autoindex', 'hasindex'],
+ disabled: function(m) {
+ if (!m.isNew()) {
+ return true;
+ }
+
+ if (m.get('hasindex')) {
+ return true;
+ } else if (!m.get('autoindex')) {
+ setTimeout(function () {
+ m.set('coveringindex', null);
+ });
+
+ return true;
+ } else {
+ setTimeout(function () {
+ var name = m.get('name'),
+ newIndex = 'fki_' + name;
+
+ if (m.get('autoindex') && !_.isUndefined(name) && !_.isNull(name) &&
+ name != '') {
+ m.set('coveringindex', newIndex);
+ }
+ });
+
+ return false;
+ }
+ }
+ },{
+ id: 'columns', label: '{{ _('Columns') }}',
+ type: 'collection', group: '{{ _('Columns') }}', disabled: false,
+ canAdd: function(m) {
+ // We can't update columns of existing foreign key.
+ return m.isNew();
+ }, canDelete: true,
+ control: ForeignKeyColumnControl,
+ model: ForeignKeyColumnModel,
+ disabled: function(m) {
+ // We can't update columns of existing foreign key.
+ return !m.isNew();
+ }
+ },{
+ id: 'confupdtype', label:'{{ _('On update') }}',
+ type:"select2", group: '{{ _('Action') }}', mode: ['edit','create'],
+ select2:{width:"50%"},
+ options: [
+ {label: "NO ACTION", value: "a"},
+ {label: "RESTRICT", value: "r"},
+ {label: "CASCADE", value: "c"},
+ {label: "SET NULL", value: "n"},
+ {label: "SET DEFAULT", value: "d"}
+ ],disabled: function(m) {
+ // We can't update confupdtype of existing foreign key.
+ return !m.isNew();
+ }
+ },{
+ id: 'confdeltype', label:'{{ _('On delete') }}',
+ type:"select2", group: '{{ _('Action') }}', mode: ['edit','create'],
+ select2:{width:"50%"},
+ options: [
+ {label: "NO ACTION", value: "a"},
+ {label: "RESTRICT", value: "r"},
+ {label: "CASCADE", value: "c"},
+ {label: "SET NULL", value: "n"},
+ {label: "SET DEFAULT", value: "d"}
+ ],disabled: function(m) {
+ // We can't update confdeltype of existing foreign key.
+ return !m.isNew();
+ }
+ }
+ ],
+ validate: function() {
+ this.errorModel.clear();
+
+ var columns = this.get('columns');
+ if ((_.isUndefined(columns) || _.isNull(columns) || columns.length < 1)) {
+ var msg = '{{ _('Please specify columns.') }}';
+ this.errorModel.set('columns', msg);
+ return msg;
+ }
+
+ var coveringindex = this.get('coveringindex'),
+ autoindex = this.get('autoindex');
+ if (autoindex && (_.isUndefined(coveringindex) || _.isNull(coveringindex) ||
+ String(coveringindex).replace(/^\s+|\s+$/g, '') == '')) {
+ var msg = '{{ _('Please specify covering index name.') }}';
+ this.errorModel.set('coveringindex', msg);
+ return msg;
+ }
+
+ return null;
+ }
+ })
+ });
+ }
+
+ return pgBrowser.Nodes['foreign_key'];
+});
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/begin.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/begin.sql
new file mode 100644
index 0000000..58bfee1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/begin.sql
@@ -0,0 +1 @@
+BEGIN;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/create.sql
new file mode 100644
index 0000000..f374e32
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/create.sql
@@ -0,0 +1,27 @@
+ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }}
+ ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} FOREIGN KEY ({% for columnobj in data.columns %}{% if loop.index != 1 %}
+, {% endif %}{{ conn|qtIdent(columnobj.local_column)}}{% endfor %})
+ REFERENCES {{ conn|qtIdent(data.remote_schema, data.remote_table) }} ({% for columnobj in data.columns %}{% if loop.index != 1 %}
+, {% endif %}{{ conn|qtIdent(columnobj.referenced)}}{% endfor %}) {% if data.confmatchtype %}MATCH FULL{% endif%}
+
+ ON UPDATE{% if data.confupdtype == 'a' %}
+ NO ACTION{% elif data.confupdtype == 'r' %}
+ RESTRICT{% elif data.confupdtype == 'c' %}
+ CASCADE{% elif data.confupdtype == 'n' %}
+ SET NULL{% elif data.confupdtype == 'd' %}
+ SET DEFAULT{% endif %}
+
+ ON DELETE{% if data.confdeltype == 'a' %}
+ NO ACTION{% elif data.confdeltype == 'r' %}
+ RESTRICT{% elif data.confdeltype == 'c' %}
+ CASCADE{% elif data.confdeltype == 'n' %}
+ SET NULL{% elif data.confdeltype == 'd' %}
+ SET DEFAULT{% endif %}
+{% if data.condeferrable %}
+
+ DEFERRABLE{% if data.condeferred %}
+ INITIALLY DEFERRED{% endif%}
+{% endif%}
+{% if data.convalidated %}
+
+ NOT VALID{% endif%};
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/create_index.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/create_index.sql
new file mode 100644
index 0000000..f9c1e88
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/create_index.sql
@@ -0,0 +1,5 @@
+{% if data.autoindex %}
+CREATE INDEX {{ conn|qtIdent(data.coveringindex) }}
+ ON {{ conn|qtIdent(data.schema, data.table) }}({% for columnobj in data.columns %}{% if loop.index != 1 %}
+, {% endif %}{{ conn|qtIdent(columnobj.local_column)}}{% endfor %});
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/delete.sql
new file mode 100644
index 0000000..2096795
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_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/foreign_key/templates/foreign_key/sql/end.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/end.sql
new file mode 100644
index 0000000..92d09d5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/end.sql
@@ -0,0 +1 @@
+END;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_cols.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_cols.sql
new file mode 100644
index 0000000..4b2fee2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_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/foreign_key/templates/foreign_key/sql/get_constraint_cols.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_constraint_cols.sql
new file mode 100644
index 0000000..a96e575
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_constraint_cols.sql
@@ -0,0 +1,13 @@
+{% for keypair in keys %}
+{% if loop.index != 1 %}
+UNION
+{% endif %}
+SELECT a1.attname as conattname,
+ a2.attname as confattname
+FROM pg_attribute a1,
+ pg_attribute a2
+WHERE a1.attrelid={{tid}}::oid
+ AND a1.attnum={{keypair[1]}}
+ AND a2.attrelid={{confrelid}}::oid
+ AND a2.attnum={{keypair[0]}}
+{% endfor %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_constraints.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_constraints.sql
new file mode 100644
index 0000000..d2f358c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_constraints.sql
@@ -0,0 +1,37 @@
+SELECT cls.oid, cls.relname as idxname, indnatts
+ 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 idx.indrelid = {{tid}}::oid
+ AND con.contype='p'
+
+UNION
+
+SELECT cls.oid, cls.relname as idxname, indnatts
+ 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 idx.indrelid = {{tid}}::oid
+ AND con.contype='x'
+
+UNION
+
+SELECT cls.oid, cls.relname as idxname, indnatts
+ 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 idx.indrelid = {{tid}}::oid
+ AND con.contype='u'
+
+UNION
+
+SELECT cls.oid, cls.relname as idxname, indnatts
+ 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 idx.indrelid = {{tid}}::oid
+ AND conname IS NULL
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_name.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_name.sql
new file mode 100644
index 0000000..07fdae2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_name.sql
@@ -0,0 +1,3 @@
+SELECT conname as name
+FROM pg_constraint ct
+WHERE ct.oid = {{fkid}}::oid
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_oid.sql
new file mode 100644
index 0000000..576c976
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_oid.sql
@@ -0,0 +1,5 @@
+SELECT ct.oid,
+ NOT convalidated as convalidated
+FROM pg_constraint ct
+WHERE contype='f' AND
+ct.conname = {{ name|qtLiteral }};
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_oid_with_transaction.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_oid_with_transaction.sql
new file mode 100644
index 0000000..abe636a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_oid_with_transaction.sql
@@ -0,0 +1,6 @@
+SELECT ct.oid,
+ ct.conname as name,
+ NOT convalidated as convalidated
+FROM pg_constraint ct
+WHERE contype='f' AND
+ conrelid = {{tid}}::oid LIMIT 1;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/get_parent.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_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/foreign_key/templates/foreign_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/foreign_key/templates/foreign_key/sql/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/grant.sql
new file mode 100644
index 0000000..0fb0ea5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_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/foreign_key/templates/foreign_key/sql/nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/nodes.sql
new file mode 100644
index 0000000..9667588
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/nodes.sql
@@ -0,0 +1,7 @@
+SELECT ct.oid,
+ conname as name,
+ NOT convalidated as convalidated
+FROM pg_constraint ct
+WHERE contype='f' AND
+ conrelid = {{tid}}::oid
+ORDER BY conname
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/properties.sql
new file mode 100644
index 0000000..147ac87
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/properties.sql
@@ -0,0 +1,31 @@
+SELECT ct.oid,
+ conname as name,
+ condeferrable,
+ condeferred,
+ confupdtype,
+ confdeltype,
+ CASE confmatchtype
+ WHEN 's' THEN FALSE
+ WHEN 'f' THEN TRUE
+ END AS confmatchtype,
+ conkey,
+ confkey,
+ confrelid,
+ nl.nspname as fknsp,
+ cl.relname as fktab,
+ nr.nspname as refnsp,
+ cr.relname as reftab,
+ description as comment,
+ NOT convalidated as convalidated
+FROM pg_constraint ct
+JOIN pg_class cl ON cl.oid=conrelid
+JOIN pg_namespace nl ON nl.oid=cl.relnamespace
+JOIN pg_class cr ON cr.oid=confrelid
+JOIN pg_namespace nr ON nr.oid=cr.relnamespace
+LEFT OUTER JOIN pg_description des ON (des.objoid=ct.oid AND des.classoid='pg_constraint'::regclass)
+WHERE contype='f' AND
+conrelid = {{tid}}::oid
+{% if fkid %}
+AND ct.oid = {{fkid}}::oid
+{% endif %}
+ORDER BY conname
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/update.sql
new file mode 100644
index 0000000..a72f7e5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/update.sql
@@ -0,0 +1,18 @@
+{### SQL to update foreign key object ###}
+{% if data %}
+{# ==== To update foreign 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 foreign key validate ==== #}
+{% if 'convalidated' in data and o_data.convalidated != data.convalidated and not data.convalidated %}
+ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }}
+ VALIDATE CONSTRAINT {{ conn|qtIdent(data.name) }};
+{% endif %}
+{# ==== To update foreign 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 %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/validate.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/validate.sql
new file mode 100644
index 0000000..5a62c80
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/templates/foreign_key/sql/validate.sql
@@ -0,0 +1,2 @@
+ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }}
+ VALIDATE CONSTRAINT {{ conn|qtIdent(data.name) }};
\ No newline at end of file
diff --git a/web/pgadmin/static/js/backform.pgadmin.js b/web/pgadmin/static/js/backform.pgadmin.js
index 8eaed6d..4359748 100644
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: Initial foreign key constraint node patch [pgadmin4]
In-Reply-To: <CAFiP3vyQjXubp8DNxNW1XGvmMfHfJXn5hdfABGCj9JWpMcigmA@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