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>
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
-- Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers