Hi,
Please find updated patch for the Foreign Table Module.
This patch is dependent on
1. Backgrid Depscell Patch, (submitted by me)
2. NodeAjaxOptionsCell Transform change patch, on which Ashesh and Murtuza
are working
Thanks,
Khushboo
On Wed, Feb 24, 2016 at 2:57 PM, Khushboo Vashi <
khushboo.va...@enterprisedb.com> wrote:
> Hi,
>
> I have updated the Foreign Table module as below:
>
> - Used 'NodeByListControl' to get schemas, in the foreign_table.js file as
> suggested by Ashesh to avoid code redundancy.
>
> - Applied *'Security Label Macro'* Patch (Implemented by Harshal).
> To test the Foreign Table patch, 'Security Label Macro' patch must be
> applied first as that is not committed yet.
>
> Please find attached Foreign Table Patch.
>
> Thanks,
> Khushboo
>
> On Tue, Feb 23, 2016 at 6:53 PM, Khushboo Vashi <
> khushboo.va...@enterprisedb.com> wrote:
>
>> Hi,
>>
>> Please find attached patch for the Foreign Table Module.
>>
>> The patch will be modified after Types module implementation as we need
>> to populate Base Type and some Type related validations from the Types
>> module.
>>
>> Please review it and let me know the feedback.
>>
>> Thanks,
>> Khushboo
>>
>
>
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py
new file mode 100644
index 0000000..04d7592
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py
@@ -0,0 +1,1034 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+"""Implements the Foreign Table Module."""
+
+import json
+from functools import wraps
+import sys
+import traceback
+from flask import render_template, make_response, request, jsonify, \
+ current_app
+from flask.ext.babel import gettext
+from config import PG_DEFAULT_DRIVER
+from pgadmin.utils.ajax import make_json_response, \
+ make_response as ajax_response, internal_server_error
+from pgadmin.browser.utils import PGChildNodeView
+from pgadmin.browser.collection import CollectionNodeModule
+import pgadmin.browser.server_groups.servers.databases.schemas as schemas
+from pgadmin.utils.ajax import precondition_required
+from pgadmin.browser.server_groups.servers.databases.schemas.utils import \
+ SchemaChildModule, DataTypeReader
+from pgadmin.utils.driver import get_driver
+from pgadmin.browser.server_groups.servers.databases.utils import \
+ parse_sec_labels_from_db
+
+
+class ForeignTableModule(SchemaChildModule):
+ """
+ class ForeignTableModule(CollectionNodeModule):
+
+ This class represents The Foreign Table Module.
+
+ Methods:
+ -------
+ * __init__(*args, **kwargs)
+ - Initialize the Foreign Table Module.
+
+ * get_nodes(gid, sid, did, scid)
+ - Generate the Foreign Table collection node.
+
+ * node_inode():
+ - Override this property to make the Foreign Table node as leaf node.
+
+ * script_load()
+ - Load the module script for Foreign Table, when schema node is
+ initialized.
+ """
+ NODE_TYPE = 'foreign-table'
+ COLLECTION_LABEL = gettext("Foreign Tables")
+
+ def __init__(self, *args, **kwargs):
+ super(ForeignTableModule, self).__init__(*args, **kwargs)
+ self.min_ver = None
+ self.max_ver = None
+
+ def get_nodes(self, gid, sid, did, scid):
+ """
+ Generate the Foreign Table collection node.
+ """
+ yield self.generate_browser_collection_node(scid)
+
+ @property
+ def node_inode(self):
+ """
+ Make the node as leaf node.
+ """
+ return False
+
+ @property
+ def script_load(self):
+ """
+ Load the module script for foreign table, when the
+ schema node is initialized.
+ """
+ return schemas.SchemaModule.NODE_TYPE
+
+
+blueprint = ForeignTableModule(__name__)
+
+
+class ForeignTableView(PGChildNodeView, DataTypeReader):
+ """
+ class ForeignTableView(PGChildNodeView)
+
+ This class inherits PGChildNodeView to get the different routes for
+ the module.
+
+ The class is responsible to Create, Read, Update and Delete operations for
+ the Foreign Table.
+
+ Methods:
+ -------
+ * validate_request(f):
+ - Works as a decorator.
+ Validating request on the request of create, update and modified SQL.
+
+ * module_js():
+ - Overrides this property to define javascript for Foreign Table node.
+
+ * check_precondition(f):
+ - Works as a decorator.
+ - Checks database connection status.
+ - Attach connection object and template path.
+
+ * list(gid, sid, did, scid):
+ - List the Foreign Table.
+
+ * nodes(gid, sid, did, scid):
+ - Returns all the Foreign Table to generate Nodes in the browser.
+
+ * properties(gid, sid, did, scid, foid):
+ - Returns the Foreign Table properties.
+
+ * get_collations(gid, sid, did, scid, foid=None):
+ - Returns Collations.
+
+ * get_types(gid, sid, did, scid, foid=None):
+ - Returns Data Types.
+
+ * get_foreign_servers(gid, sid, did, scid, foid=None):
+ - Returns the Foreign Servers.
+
+ * get_tables(gid, sid, did, scid, foid=None):
+ - Returns the Foreign Tables as well as Plain Tables.
+
+ * get_columns(gid, sid, did, scid, foid=None):
+ - Returns the Table Columns.
+
+ * create(gid, sid, did, scid):
+ - Creates a new Foreign Table object.
+
+ * update(gid, sid, did, scid, foid):
+ - Updates the Foreign Table object.
+
+ * delete(gid, sid, did, scid, foid):
+ - Drops the Foreign Table object.
+
+ * sql(gid, sid, did, scid, foid):
+ - Returns the SQL for the Foreign Table object.
+
+ * msql(gid, sid, did, scid, foid=None):
+ - Returns the modified SQL.
+
+ * get_sql(gid, sid, data, scid, foid=None):
+ - Generates the SQL statements to create/update the Foreign Table object.
+
+ * dependents(gid, sid, did, scid, foid):
+ - Returns the dependents for the Foreign Table object.
+
+ * dependencies(gid, sid, did, scid, foid):
+ - Returns the dependencies for the Foreign Table object.
+ """
+
+ node_type = blueprint.node_type
+
+ parent_ids = [
+ {'type': 'int', 'id': 'gid'},
+ {'type': 'int', 'id': 'sid'},
+ {'type': 'int', 'id': 'did'},
+ {'type': 'int', 'id': 'scid'}
+ ]
+ ids = [
+ {'type': 'int', 'id': 'foid'}
+ ]
+
+ 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'}],
+ 'get_collations': [
+ {'get': 'get_collations'},
+ {'get': 'get_collations'}
+ ],
+ 'get_types': [{'get': 'types'}, {'get': 'types'}],
+ 'get_foreign_servers': [{'get': 'get_foreign_servers'},
+ {'get': 'get_foreign_servers'}],
+ 'get_tables': [{'get': 'get_tables'}, {'get': 'get_tables'}],
+ 'get_columns': [{'get': 'get_columns'}, {'get': 'get_columns'}]
+ })
+
+ def validate_request(f):
+ """
+ Works as a decorator.
+ Validating request on the request of create, update and modified SQL.
+
+ Required Args:
+ name: Name of the Foreign Table
+ ftsrvname: Foreign Server Name
+
+ Above both the arguments will not be validated in the update action.
+ """
+ @wraps(f)
+ def wrap(self, **kwargs):
+
+ data = {}
+
+ if request.data:
+ req = json.loads(request.data.decode())
+ else:
+ req = request.args or request.form
+
+ if 'foid' not in kwargs:
+ required_args = [
+ 'name',
+ 'ftsrvname'
+ ]
+
+ for arg in required_args:
+ if arg not in req or req[arg] == '':
+ return make_json_response(
+ status=410,
+ success=0,
+ errormsg=gettext(
+ "Couldn't find the required parameter \
+ (%s)." % arg
+ )
+ )
+
+ try:
+ list_params = []
+ if request.method == 'GET':
+ list_params = ['constraints', 'columns', 'ftoptions',
+ 'seclabels', 'inherits']
+ else:
+ list_params = ['inherits']
+
+ for key in req:
+ if key in list_params and req[key] != '' \
+ and req[key] is not None:
+ # Coverts string into python list as expected.
+ data[key] = json.loads(req[key])
+
+ if key == 'inherits':
+ # Convert Table ids from unicode/string to int
+ # and make tuple for 'IN' query.
+ inherits = tuple([int(x) for x in data[key]])
+
+ if len(inherits) == 1:
+ # Python tupple has , after the first param
+ # in case of single parameter.
+ # So, we need to make it tuple explicitly.
+ inherits = "(" + str(inherits[0]) + ")"
+ if inherits:
+ # Fetch Table Names from their respective Ids,
+ # as we need Table names to generate the SQL.
+ SQL = render_template(
+ "/".join([self.template_path,
+ 'get_tables.sql']),
+ attrelid=inherits)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ if 'inherits' in res['rows'][0]:
+ data[key] = res['rows'][0]['inherits']
+ else:
+ data[key] = []
+
+ elif key == 'typnotnull':
+ data[key] = True if (req[key] == 'true' or req[key]
+ is True) else False if\
+ (req[key] == 'false' or req[key]) is False else ''
+ else:
+ data[key] = req[key]
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ self.request = data
+ return f(self, **kwargs)
+
+ return wrap
+
+ def module_js(self):
+ """
+ Load JS file (foreign_tables.js) for this module.
+ """
+ return make_response(
+ render_template(
+ "foreign_tables/js/foreign_tables.js",
+ _=gettext
+ ),
+ 200, {'Content-Type': 'application/x-javascript'}
+ )
+
+ def check_precondition(f):
+ """
+ Works as a decorator.
+ Checks the database connection status.
+ Attaches the connection object and template path to the class object.
+ """
+ @wraps(f)
+ def wrap(*args, **kwargs):
+ self = args[0]
+ driver = get_driver(PG_DEFAULT_DRIVER)
+ self.manager = driver.connection_manager(kwargs['sid'])
+
+ # Get database connection
+ self.conn = self.manager.connection(did=kwargs['did'])
+
+ self.qtIdent = driver.qtIdent
+
+ if not self.conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+
+ ver = self.manager.version
+ server_type = self.manager.server_type
+
+ # Set template path for sql scripts depending
+ # on the server version.
+
+ if ver >= 90500:
+ self.template_path = 'foreign_tables/sql/9.5_plus'
+ elif ver >= 90300:
+ self.template_path = 'foreign_tables/sql/9.3_plus'
+ else:
+ self.template_path = 'foreign_tables/sql/9.1_plus'
+
+ return f(*args, **kwargs)
+
+ return wrap
+
+ @check_precondition
+ def list(self, gid, sid, did, scid):
+ """
+ List all the Foreign Tables.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ """
+ SQL = render_template("/".join([self.template_path, 'node.sql']),
+ scid=scid)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+ return ajax_response(
+ response=res['rows'],
+ status=200
+ )
+
+ @check_precondition
+ def nodes(self, gid, sid, did, scid):
+ """
+ Returns the Foreign Tables to generate the Nodes.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ """
+
+ res = []
+ SQL = render_template("/".join([self.template_path,
+ 'node.sql']), scid=scid)
+ status, rset = self.conn.execute_2darray(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=rset)
+
+ for row in rset['rows']:
+ res.append(
+ self.blueprint.generate_browser_node(
+ row['oid'],
+ scid,
+ row['name'],
+ icon="icon-foreign-table"
+ ))
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ @check_precondition
+ def properties(self, gid, sid, did, scid, foid):
+ """
+ Returns the Foreign Table properties.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ data = self._fetch_properties(gid, sid, did, scid, foid)
+
+ return ajax_response(
+ response=data,
+ status=200
+ )
+
+ @check_precondition
+ def get_collations(self, gid, sid, did, scid, foid=None):
+ """
+ Returns the Collations.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+
+ res = [{'label': '', 'value': ''}]
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_collations.sql']))
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in rset['rows']:
+ res.append(
+ {'label': row['copy_collation'],
+ 'value': row['copy_collation']}
+ )
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def types(self, gid, sid, did, scid, foid=None):
+ """
+ Returns the Data Types.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+
+ condition = """typisdefined AND typtype IN ('b', 'c', 'd', 'e', 'r')
+AND NOT EXISTS (SELECT 1 FROM pg_class WHERE relnamespace=typnamespace
+AND relname = typname AND relkind != 'c') AND
+(typname NOT LIKE '_%' OR NOT EXISTS (SELECT 1 FROM pg_class WHERE
+relnamespace=typnamespace AND relname = substring(typname FROM 2)::name
+AND relkind != 'c'))"""
+
+ if self.blueprint.show_system_objects:
+ condition += " AND nsp.nspname != 'information_schema'"
+
+ # Get Types
+ status, types = self.get_types(self.conn, condition)
+
+ if not status:
+ return internal_server_error(errormsg=types)
+
+ return make_json_response(
+ data=types,
+ status=200
+ )
+
+ @check_precondition
+ def get_foreign_servers(self, gid, sid, did, scid, foid=None):
+ """
+ Returns the Foreign Servers.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ res = [{'label': '', 'value': ''}]
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_foreign_servers.sql']))
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in rset['rows']:
+ res.append(
+ {'label': row['srvname'], 'value': row['srvname']}
+ )
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def get_tables(self, gid, sid, did, scid, foid=None):
+ """
+ Returns the Foreign Tables as well as Plain Tables.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ res = []
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_tables.sql']))
+ status, rset = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ data=rset['rows'],
+ status=200
+ )
+
+ except:
+ exc_type, exc_value, exc_traceback = sys.exc_info()
+ current_app.logger.error(
+ traceback.print_exception(exc_type,
+ exc_value, exc_traceback, limit=2))
+
+ return internal_server_error(errormsg=str(exc_value))
+
+ @check_precondition
+ def get_columns(self, gid, sid, did, scid, foid=None):
+ """
+ Returns the Table Columns.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ attrelid: Table oid
+
+ Returns:
+ JSON Array with below parameters.
+ attname: Column Name
+ datatype: Column Data Type
+ inherited_from: Parent Table from which the related column
+ is inheritted.
+ """
+ res = []
+ data = request.args if request.args else None
+ try:
+ if data and 'attrelid' in data:
+ SQL = render_template("/".join([self.template_path,
+ 'get_table_columns.sql']),
+ attrelid=data['attrelid'])
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+ return make_json_response(
+ data=res['rows'],
+ status=200
+ )
+ except:
+ exc_type, exc_value, exc_traceback = sys.exc_info()
+ current_app.logger.error(traceback.print_exception(
+ exc_type,
+ exc_value,
+ exc_traceback,
+ limit=2
+ )
+ )
+
+ return internal_server_error(errormsg=str(exc_value))
+
+ @check_precondition
+ @validate_request
+ def create(self, gid, sid, did, scid):
+ """
+ Creates a new Foreign Table object.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ name: Foreign Table Name
+ basensp: Schema Name
+ ftsrvname: Foreign Server Name
+
+ Returns:
+ Foreign Table object in json format.
+ """
+ try:
+ # Get SQL to create Foreign Table
+ status, SQL = self.get_sql(gid, sid, did, scid, self.request)
+ if not status:
+ return internal_server_error(errormsg=SQL)
+
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ # Need oid to add object in the tree at browser.
+ basensp = self.request['basensp'] if ('basensp' in self.request) \
+ else None
+ SQL = render_template("/".join([self.template_path,
+ 'get_oid.sql']),
+ basensp=basensp,
+ name=self.request['name'])
+ status, res = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ foid, scid = res['rows'][0]
+
+ return jsonify(
+ node=self.blueprint.generate_browser_node(
+ foid,
+ scid,
+ self.request['name'],
+ icon="icon-foreign-table"
+ )
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def delete(self, gid, sid, did, scid, foid):
+ """
+ Drops the Foreign Table.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ if self.cmd == 'delete':
+ # This is a cascade operation
+ cascade = True
+ else:
+ cascade = False
+
+ try:
+ # Fetch Name and Schema Name to delete the foreign table.
+ SQL = render_template("/".join([self.template_path,
+ 'delete.sql']), scid=scid, foid=foid)
+ status, res = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ name, basensp = res['rows'][0]
+
+ SQL = render_template("/".join([self.template_path,
+ 'delete.sql']),
+ name=name,
+ basensp=basensp,
+ 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=gettext("Foreign Table dropped"),
+ data={
+ 'id': foid,
+ 'scid': scid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ @validate_request
+ def update(self, gid, sid, did, scid, foid):
+ """
+ Updates the Foreign Table.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ status, SQL = self.get_sql(gid, sid, did, scid, self.request, foid)
+
+ if not status:
+ return internal_server_error(errormsg=SQL)
+
+ try:
+ if SQL and SQL.strip('\n') and SQL.strip(' '):
+ 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']),
+ foid=foid)
+ status, res = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ scid = res['rows'][0]['scid']
+
+ return make_json_response(
+ success=1,
+ info="Foreign Table updated",
+ data={
+ 'id': foid,
+ 'scid': scid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+ else:
+ return make_json_response(
+ success=1,
+ info="Nothing to update",
+ data={
+ 'id': foid,
+ 'scid': scid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def sql(self, gid, sid, did, scid, foid=None):
+ """
+ Returns the SQL for the Foreign Table object.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ data = self._fetch_properties(gid, sid, did, scid, foid, inherits=True)
+
+ col_data = []
+ for c in data['columns']:
+ if (not 'inheritedfrom' in c) or (c['inheritedfrom'] is None):
+ col_data.append(c)
+
+ data['columns'] = col_data
+
+ SQL = render_template("/".join([self.template_path,
+ 'create.sql']), data=data)
+
+ sql_header = """-- {0}: {1}
+
+-- DROP {0} {1};
+
+""".format('FOREIGN TABLE', data['basensp'] + "." + data['name'])
+
+ SQL = sql_header + SQL
+
+ return ajax_response(response=SQL.strip('\n'))
+
+ @check_precondition
+ @validate_request
+ def msql(self, gid, sid, did, scid, foid=None):
+ """
+ Returns the modified SQL.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ name: Foreign Table Name
+ ftsrvname: Foreign Server Name
+
+ Returns:
+ SQL statements to create/update the Foreign Table.
+ """
+ status, SQL = self.get_sql(gid, sid, did, scid, self.request, foid)
+ if status:
+ return make_json_response(
+ data=SQL.strip('\n'),
+ status=200
+ )
+ else:
+ return SQL
+
+ def get_sql(self, gid, sid, did, scid, data, foid=None):
+ """
+ Genrates the SQL statements to create/update the Foreign Table.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ try:
+ if foid is not None:
+ old_data = self._fetch_properties(gid, sid, did, scid, foid,
+ inherits=True)
+
+ # Prepare dict of columns with key = column's attnum
+ # Will use this in the update template when any column is
+ # changed, to identify the columns.
+ col_data = {}
+ for c in old_data['columns']:
+ col_data[c['attnum']] = c
+
+ old_data['columns'] = col_data
+
+ SQL = render_template(
+ "/".join([self.template_path, 'update.sql']),
+ data=data, o_data=old_data
+ )
+ else:
+ SQL = render_template("/".join([self.template_path,
+ 'create.sql']), data=data)
+ return True, SQL
+
+ except Exception as e:
+ return False, e
+
+ @check_precondition
+ def dependents(self, gid, sid, did, scid, foid):
+ """
+ This function get the dependents and return ajax response
+ for the Foreign Table object.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ dependents_result = self.get_dependents(self.conn, foid)
+ return ajax_response(
+ response=dependents_result,
+ status=200
+ )
+
+ @check_precondition
+ def dependencies(self, gid, sid, did, scid, foid):
+ """
+ This function get the dependencies and return ajax response
+ for the Foreign Table object.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ dependencies_result = self.get_dependencies(self.conn, foid)
+ return ajax_response(
+ response=dependencies_result,
+ status=200
+ )
+
+ def _fetch_properties(self, gid, sid, did, scid, foid, inherits=False):
+ """
+ Returns the Foreign Table properties which will be used in
+ properties, sql and get_sql functions.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ inherits: If True then inherited table will be fetched from
+ database
+
+ Returns:
+
+ """
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']),
+ scid=scid, foid=foid)
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ data = res['rows'][0]
+
+ # Get formatted Security Labels
+ if 'seclabels' in data:
+ data.update(parse_sec_labels_from_db(data['seclabels']))
+
+ # Get formatted Variables
+ if 'ftoptions' in data:
+ data.update({'strftoptions': data['ftoptions']})
+ data.update(self._parse_variables_from_db(data['ftoptions']))
+
+ SQL = render_template("/".join([self.template_path,
+ 'get_constraints.sql']), foid=foid)
+ status, cons = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=cons)
+
+ if cons and 'rows' in cons:
+ data['constraints'] = cons['rows']
+
+ SQL = render_template("/".join([self.template_path,
+ 'get_columns.sql']), foid=foid)
+ status, cols = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=cols)
+
+ # The Length and the precision of the Datatype should be separated.
+ # The Format we getting from database is: numeric(1,1)
+ # So, we need to separate it as Length: 1, Precision: 1
+ for c in cols['rows']:
+ if c['fulltype'] != '' and c['fulltype'].find("(") > 0:
+ substr = c['fulltype'][c['fulltype'].find("(") + 1:len(
+ c['fulltype']) - 1]
+ typlen = substr.split(",")
+ if len(typlen) > 1:
+ c['typlen'] = int(typlen[0])
+ c['precision'] = int(typlen[1])
+ else:
+ c['typlen'] = int(typlen)
+ c['precision'] = ''
+
+ if cols and 'rows' in cols:
+ data['columns'] = cols['rows']
+
+ data['strcolumn'] = ''
+ str_cols = []
+ if 'columns' in data:
+ for c in data['columns']:
+ str_cols.append(c['strcolumn'])
+ data['strcolumn'] = ', '.join(str_cols)
+
+ # Get Inherited table names from their OID
+ if inherits:
+ if 'inherits' in data and data['inherits']:
+ inherits = tuple([int(x) for x in data['inherits']])
+ if len(inherits) == 1:
+ inherits = "(" + str(inherits[0]) + ")"
+
+ SQL = render_template("/".join([self.template_path,
+ 'get_tables.sql']),
+ attrelid=inherits)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ if 'inherits' in res['rows'][0]:
+ data['inherits'] = res['rows'][0]['inherits']
+
+ return data
+
+ def _parse_variables_from_db(self, db_variables):
+ """
+ Function to format the output for variables.
+
+ Args:
+ db_variables: Variable object
+
+ Expected Object Format:
+ ['option1=value1', ..]
+ where:
+ user_name and database are optional
+ Returns:
+ Variable Object in below format:
+ {
+ 'variables': [
+ {'name': 'var_name', 'value': 'var_value',
+ 'user_name': 'user_name', 'database': 'database_name'},
+ ...]
+ }
+ where:
+ user_name and database are optional
+ """
+ variables_lst = []
+
+ if db_variables is not None:
+ for row in db_variables:
+ var_name, var_value = row.split("=")
+ # Because we save as boolean string in db so it needs
+ # conversion
+ if var_value == 'false' or var_value == 'off':
+ var_value = False
+
+ var_dict = {'option': var_name, 'value': var_value}
+
+ variables_lst.append(var_dict)
+
+ return {"ftoptions": variables_lst}
+
+ForeignTableView.register_node_view(blueprint)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/static/img/coll-foreign-table.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/static/img/coll-foreign-table.png
new file mode 100644
index 0000000000000000000000000000000000000000..dc1f742bbca05980f3c4cbf3c16af0e0cf9afa41
GIT binary patch
literal 492
zcmV<I0Tcd-P)<h;3K|Lk000e1NJLTq000mG000mO0{{R3C@l|D00001b5ch_0Itp)
z=>Px#x=>71MPs@4A(iKf&i<6o`K{IR%G~PS<LBn*=jP_-#n|R{x#%#Q=!nh!Qn2!~
z)$QHg-Gj^iddB;2!1-3P^GU1lJ*Dk2p6e%>>E7PngUkI&tMEOf?I@Y)-{0Tj;^O1u
z<K^Y$hrs9H;NYjHr>v~3?Ck9B?(XU7>EYqwrlzK-sHm;2t*@`I>gwu{%Jb*v=j-e1
z=;-LFsj2Vp@A2{R?d|QVs;aQCu(GnU<mBY=@bG)a`)|PbD4FPqpT#2p0004WQchC<
zK<3zH00001VoOIv0Eh)0NB{r;32;bRa{vGf6951U69E94oEQKA00(qQO+^RW0~{47
zCVc;%cK`qYo=HSOR2b7;&%F-9Fbqb~d^Ca9F5y=Q3JhUtmQ1dM!u!7$P76ZR<IR!f
z82rYJgGFWCa#hz2@f6zizd<`Q@H2zW$2=nuHC%$4?T(m2Al7g$=E5Y|iAXfK7sEj;
zPLi`2Jc=Qy04vsM=+&^dH+YdDJCl+cEQbyUeIH!8q}L#F1ok2?4QkNnPT(qT`XOTQ
i<DH48klNUNZ+`=M(hUmmK;RYt0000<MNUMnLSTX=Tlvud
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/static/img/foreign-table.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/static/img/foreign-table.png
new file mode 100644
index 0000000000000000000000000000000000000000..53c133885a0c0c7050e4c8580adfd75f8ceaa852
GIT binary patch
literal 563
zcmV-30?hr1P)<h;3K|Lk000e1NJLTq000mG000mO0{{R3C@l|D00001b5ch_0Itp)
z=>Px#)lf`SMdak<A(iKi(EpOp`=ZnJy4dZ}-|64u=jG<-=H}+w;pME(=VP(xE}Q6!
z&;NzY{(i{(b;J5>zW8Cd_Exg<ORVrfr|vhP>@A$?CYb4k%>8S=_(7)bHlge;oa(gJ
z?%mzp-rnBd-{0Wi;Njun;^N}t<KyM!<%hrL=;-L_>FMX^=c%cwtgNi;?CkCB?eFjJ
z>gwvJr>Cf>sI9H7udlD`>+A6F@bdEV?(XiZtE;fEu(7eRva+)A@$vKX^Y!)hwY9bO
z_V)Pr__nsTxVX6Y_xDt#KRo~d00DGTPE!Ct=GbNc0004EOGiWihy@);00009a7bBm
z000XU000XU0RWnu7ytkO2XskIMF-mh92F=elJWmr0002WNkl<Z7}HhH-EM+F5C!0!
z!a`SOE!5&qON&%(w?ITOaT9H_G4uZK<Sexp&fR=DnamLa?nVd!4?!rS_))1*g4)CQ
zDb1cIFZpyfUkr)yYnr_=6z?B@P%f9%=W4zA`c`Iu+kWX0jT}HVKkWuJCI&TMg{nWa
ztu|yMBI+4@9u0*sBWsCaRo8o@03dXwbPVhEq?2+1l3Zsa2G!IMcR5VT$aNl?orRdF
z0LaLt4E6b!;~_UINb+ZJ+;+W@L)Soi@Pqk(|0_yz6w|&QQ{?~v002ovPDHLkV1nFh
BCnEp=
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/js/foreign_tables.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/js/foreign_tables.js
new file mode 100644
index 0000000..8865dc9
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/js/foreign_tables.js
@@ -0,0 +1,642 @@
+/* Create and Register Foreign Table Collection and Node. */
+define(
+ ['jquery', 'underscore', 'underscore.string', 'pgadmin', 'pgadmin.browser', 'alertify', 'pgadmin.browser.collection'],
+function($, _, S, pgAdmin, pgBrowser, alertify) {
+
+ if (!pgBrowser.Nodes['coll-foreign-table']) {
+ var foreigntable = pgAdmin.Browser.Nodes['coll-foreign-table'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'foreign-table',
+ label: '{{ _('Foreign Tables') }}',
+ type: 'coll-foreign-table',
+ columns: ['name', 'owner', 'description']
+ });
+ };
+
+ // Security Model
+ var SecurityModel = Backform.SecurityModel = pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ provider: null,
+ label: null
+ },
+ schema: [{
+ id: 'provider', label: '{{ _('Provider') }}',
+ type: 'text', editable: true, cellHeaderClasses:'width_percent_50'
+ },{
+ id: 'security_label', label: '{{ _('Security Label') }}',
+ type: 'text', editable: true, cellHeaderClasses:'width_percent_50'
+ }],
+ validate: function() {
+ var err = {},
+ errmsg = null,
+ data = this.toJSON();
+
+ if (_.isUndefined(data.label) ||
+ _.isNull(data.label) ||
+ String(data.label).replace(/^\s+|\s+$/g, '') == '') {
+ return _("Please specify the value for all the security providers.");
+ }
+ return null;
+ }
+ });
+
+ // Integer Cell for Columns Length and Precision
+ var IntegerDepCell = Backgrid.IntegerCell.extend({
+ initialize: function() {
+ Backgrid.NumberCell.prototype.initialize.apply(this, arguments);
+ Backgrid.Extension.DependentCell.prototype.initialize.apply(this, arguments);
+ },
+ updateUIDeps: function () {
+ this.$el.empty();
+ var model = this.model;
+ var column = this.column;
+ editable = this.column.get("editable");
+
+ is_editable = _.isFunction(editable) ? !!editable.apply(column, [model]) : !!editable;
+ if (is_editable){ this.$el.addClass("editable"); }
+ else { this.$el.removeClass("editable"); }
+
+ this.delegateEvents();
+ return this;
+ },
+ remove: Backgrid.Extension.DependentCell.prototype.remove
+ });
+
+
+ // Columns Model
+ var ColumnsModel = pgAdmin.Browser.Node.Model.extend({
+ idAttribute: 'attnum',
+ defaults: {
+ attname: undefined,
+ datatype: undefined,
+ typlen: undefined,
+ precision: undefined,
+ typdefault: undefined,
+ attnotnull: undefined,
+ collname: undefined,
+ attnum: undefined,
+ inheritedfrom: undefined,
+ inheritedid: undefined,
+ },
+ type_options: undefined,
+ schema: [{
+ id: 'attname', label:'{{ _('Name') }}', cell: 'string', type: 'text',
+ editable: true, cellHeaderClasses: 'width_percent_20'
+ },{
+ id: 'datatype', label:'{{ _('Data Type') }}', cell: 'node-ajax-options',
+ control: 'node-ajax-options', type: 'text', url: 'get_types',
+ editable: true, cellHeaderClasses: 'width_percent_20',
+ transform: function(d, self){
+ self.model.type_options = d;
+ return d;
+ }
+ },{
+ id: 'typlen', label:'{{ _('Length') }}',
+ cell: IntegerDepCell,
+ type: 'text', deps: ['datatype'],
+ editable: function(m) {
+ // We will store type from selected from combobox
+ //_.isUndefined(m.isNew) ? false : m.isNew();
+ var of_type = m.get('datatype');
+ if(m.type_options) {
+ m.set('is_tlength', false, {silent: true});
+
+ // iterating over all the types
+ _.each(m.type_options, function(o) {
+ // if type from selected from combobox matches in options
+ if ( of_type == o.value ) {
+ // if length is allowed for selected type
+ if(o.length)
+ {
+ // set the values in model
+ m.set('is_tlength', true, {silent: true});
+ m.set('min_val', o.min_val, {silent: true});
+ m.set('max_val', o.max_val, {silent: true});
+ }
+ }
+ });
+ return m.get('is_tlength');
+ }
+ return true;
+ },
+ cellHeaderClasses: 'width_percent_10'
+ },{
+ id: 'precision', label:'{{ _('Precision') }}',
+ type: 'text', deps: ['datatype'],
+ cell: IntegerDepCell,
+ editable: function(m) {
+ var of_type = m.get('datatype');
+ if(m.type_options) {
+ m.set('is_precision', false, {silent: true});
+ // iterating over all the types
+ _.each(m.type_options, function(o) {
+ // if type from selected from combobox matches in options
+ if ( of_type == o.value ) {
+ // if precession is allowed for selected type
+ if(o.precision)
+ {
+ // set the values in model
+ m.set('is_precision', true, {silent: true});
+ m.set('min_val', o.min_val, {silent: true});
+ m.set('max_val', o.max_val, {silent: true});
+ }
+ }
+ });
+ return m.get('is_precision');
+ }
+ return true;
+ }, cellHeaderClasses: 'width_percent_10'
+ },{
+ id: 'typdefault', label:'{{ _('Default') }}', type: 'text',
+ cell: 'string', min_version: 90300,
+ placeholder: "Enter an expression or a value.",
+ cellHeaderClasses: 'width_percent_10',
+ editable: function(m) {
+ if (this.get('node_info').server.version < 90300){
+ return false;
+ }
+ return true;
+ }
+ },{
+ id: 'attnotnull', label:'{{ _('Not Null') }}',
+ cell: 'boolean',type: 'switch', editable: true,
+ cellHeaderClasses: 'width_percent_10'
+ },{
+ id: 'collname', label:'{{ _('Collation') }}', cell: 'node-ajax-options',
+ control: 'node-ajax-options', type: 'text', url: 'get_collations',
+ min_version: 90300, editable: true,
+ cellHeaderClasses: 'width_percent_20'
+ },{
+ id: 'attnum', cell: 'string',type: 'text', visible: false
+ },{
+ id: 'inheritedfrom', label:'{{ _('Inherited From') }}', cell: 'string',
+ type: 'text', visible: false, mode: ['properties'],
+ cellHeaderClasses: 'width_percent_10'
+ }],
+ validate: function() {
+ // TODO: Add validation here
+ },
+ toJSON: Backbone.Model.prototype.toJSON
+ });
+
+ 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>'
+ );
+ }
+ };
+
+
+ /* NodeAjaxOptionsMultipleControl is for multiple selection of Combobox.
+ * This control is used to select Multiple Parent Tables to be inherited.
+ * It also populates/vacates Columns on selection/deselection of the option (i.e. table name).
+ * To populates the column, it calls the server and fetch the columns data
+ * for the selected table.
+ */
+
+ var NodeAjaxOptionsMultipleControl = Backform.NodeAjaxOptionsMultipleControl = Backform.NodeAjaxOptionsControl.extend({
+ template: _.template([
+ '<label class="<%=Backform.controlLabelClassName%>"><%=label%></label>',
+ '<div class="<%=Backform.controlsClassName%> <%=extraClasses.join(\' \')%>">',
+ ' <select class="pgadmin-node-select form-control" name="<%=name%>" style="width:100%;" value=<%-value%> <%=disabled ? "disabled" : ""%> <%=required ? "required" : ""%> >',
+ ' </select>',
+ '</div>'].join("\n")),
+ defaults: _.extend(
+ {}, Backform.NodeAjaxOptionsControl.prototype.defaults,
+ {
+ select2: {
+ allowClear: true,
+ placeholder: 'Select from the list',
+ width: 'style',
+ templateResult: formatNode,
+ templateSelection: formatNode
+ }
+ }),
+ render: function() {
+ var field = _.defaults(this.field.toJSON(), this.defaults),
+ attributes = this.model.toJSON(),
+ attrArr = field.name.split('.'),
+ name = attrArr.shift(),
+ path = attrArr.join('.'),
+ rawValue = this.keyPathAccessor(attributes[name], path),
+ data = _.extend(field, {
+ rawValue: rawValue,
+ value: this.formatter.fromRaw(rawValue, this.model),
+ attributes: attributes,
+ formatter: this.formatter
+ }),
+ evalF = function(f, d, m) {
+ return (_.isFunction(f) ? !!f.apply(d, [m]) : !!f);
+ };
+
+ // Evaluate the disabled, visible, and required option
+ _.extend(data, {
+ disabled: evalF(data.disabled, data, this.model),
+ visible: evalF(data.visible, data, this.model),
+ required: evalF(data.required, data, this.model)
+ });
+
+ if (field.node_info.server.version < field.min_version) {
+ field.version_compatible = false
+ return this;
+ }
+ else {
+ // Evaluation the options
+ if (_.isFunction(data.options)) {
+ try {
+ data.options = data.options.apply(this)
+ } catch(e) {
+ // Do nothing
+ data.options = []
+ this.model.trigger('pgadmin-view:transform:error', m, self.field, e);
+ }
+ }
+
+ // Clean up first
+ this.$el.removeClass(Backform.hiddenClassname);
+ this.$el.html(this.template(data)).addClass(field.name);
+
+ if (!data.visible) {
+ this.$el.addClass(Backform.hiddenClassname);
+ } else {
+ var opts = _.extend(
+ {}, this.defaults.select2, data.select2,
+ {
+ 'data': data.options
+ });
+ this.$el.find("select").select2(opts).val(data.rawValue).trigger("change");
+ this.updateInvalid();
+ }
+ }
+ return this;
+ },
+ onChange: function(e) {
+ var model = this.model,
+ $el = $(e.target),
+ attrArr = this.field.get("name").split('.'),
+ name = attrArr.shift(),
+ path = attrArr.join('.'),
+ value = this.getValueFromDOM(),
+ changes = {},
+ columns = model.get('columns'),
+ inherits = model.get(name);
+
+ if (this.model.errorModel instanceof Backbone.Model) {
+ if (_.isEmpty(path)) {
+ this.model.errorModel.unset(name);
+ } else {
+ var nestedError = this.model.errorModel.get(name);
+ if (nestedError) {
+ this.keyPathSetter(nestedError, path, null);
+ this.model.errorModel.set(name, nestedError);
+ }
+ }
+ }
+
+ var self = this;
+
+ if (typeof(inherits) == "string"){ inherits = JSON.parse(inherits); }
+
+ // Remove Columns if inherit option is deselected from the combobox
+ if(_.size(JSON.parse(value)) < _.size(inherits)) {
+ var dif = _.difference(inherits, JSON.parse(value));
+ var rmv_columns = columns.where({inheritedid: parseInt(dif[0])});
+ columns.remove(rmv_columns);
+ }
+ else
+ {
+ _.each(JSON.parse(value), function(i) {
+ // Fetch Columns from server
+ inhted_columns = self.fetchColumns(i);
+ columns.add(inhted_columns);
+ });
+ }
+
+ changes[name] = _.isEmpty(path) ? value : _.clone(model.get(name)) || {};
+ this.stopListening(this.model, "change:" + name, this.render);
+ model.set(changes);
+ this.listenTo(this.model, "change:" + name, this.render);
+ },
+ fetchColumns: function(table_id){
+ var self = this,
+ url = 'get_columns',
+ m = self.model.top || self.model;
+
+ if (url) {
+ 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
+ ]),
+ cache_level = this.field.get('cache_level') || node.type,
+ cache_node = this.field.get('cache_node');
+
+ cache_node = (cache_node && pgAdmin.Browser.Nodes['cache_node']) || node;
+
+ m.trigger('pgadmin:view:fetching', m, self.field);
+ data = {attrelid: table_id}
+
+ // Fetching Columns data for the selected table.
+ $.ajax({
+ async: false,
+ url: full_url,
+ data: data,
+ success: function(res) {
+ /*
+ * We will cache this data for short period of time for avoiding
+ * same calls.
+ */
+ data = cache_node.cache(url, node_info, cache_level, res.data);
+ },
+ error: function() {
+ m.trigger('pgadmin:view:fetch:error', m, self.field);
+ }
+ });
+ m.trigger('pgadmin:view:fetched', m, self.field);
+
+ // To fetch only options from cache, we do not need time from 'at'
+ // attribute but only options.
+ //
+ // It is feasible that the data may not have been fetched.
+ data = (data && data.data) || [];
+ return data;
+ }
+ },
+ });
+
+
+ // Constraints Model
+ var ConstraintModel = pgAdmin.Browser.Node.Model.extend({
+ idAttribute: 'conoid',
+ initialize: function(attrs, args) {
+ var isNew = (_.size(attrs) === 0);
+ if (!isNew) {
+ this.convalidated_default = this.get('convalidated')
+ }
+ pgAdmin.Browser.Node.Model.prototype.initialize.apply(this, arguments);
+ },
+ defaults: {
+ conoid: undefined,
+ conname: undefined,
+ consrc: undefined,
+ connoinherit: undefined,
+ convalidated: true
+ },
+ convalidated_default: true,
+ schema: [{
+ id: 'conoid', type: 'text', cell: 'string', visible: false
+ },{
+ id: 'conname', label:'{{ _('Name') }}', type: 'text', cell: 'string',
+ editable: 'is_editable', cellHeaderClasses: 'width_percent_30'
+ },{
+ id: 'consrc', label:'{{ _('Check') }}', type: 'multiline',
+ editable: 'is_editable', cell: Backgrid.Extension.TextareaCell,
+ cellHeaderClasses: 'width_percent_30'
+ },{
+ id: 'connoinherit', label:'{{ _('No Inherit') }}', type: 'switch',
+ cell: 'boolean', editable: 'is_editable',
+ cellHeaderClasses: 'width_percent_20'
+ },{
+ id: 'convalidated', label:'{{ _('Validate?') }}', type: 'switch',
+ cell: 'boolean', cellHeaderClasses: 'width_percent_20',
+ editable: function(m) {
+ var server = this.get('node_info').server;
+ if (_.isUndefined(m.isNew)) { return true; }
+ if (!m.isNew()) {
+ if(m.get('convalidated') && m.convalidated_default) {
+ return false;
+ }
+ return true;
+ }
+ return true;
+ }
+ }
+ ],
+ validate: function() {
+ // TODO: Add validation here
+ },
+ is_editable: function(m) {
+ return _.isUndefined(m.isNew) ? true : m.isNew();
+ },
+ toJSON: Backbone.Model.prototype.toJSON
+ });
+
+
+ // Options Model
+ var OptionsModel = pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ option: undefined,
+ value: undefined
+ },
+ schema: [{
+ id: 'option', label:'{{ _('Option') }}', cell: 'string', type: 'text',
+ editable: true, cellHeaderClasses:'width_percent_50'
+ },{
+ id: 'value', label:'{{ _('Value') }}', cell: 'string',type: 'text',
+ editable: true, cellHeaderClasses:'width_percent_50'
+ }
+ ],
+ validate: function() {
+ // TODO: Add validation here
+ },
+ toJSON: Backbone.Model.prototype.toJSON
+ });
+
+
+ if (!pgBrowser.Nodes['foreign-table']) {
+ pgAdmin.Browser.Nodes['foreign-table'] = pgBrowser.Node.extend({
+ type: 'foreign-table',
+ label: '{{ _('Foreign Table') }}',
+ collection_type: 'coll-foreign-table',
+ hasSQL: true,
+ hasDepends: true,
+ parent_type: ['schema'],
+ Init: function() {
+ /* Avoid mulitple registration of menus */
+ if (this.initialized)
+ return;
+
+ this.initialized = true;
+
+ pgBrowser.add_menus([{
+ name: 'create_foreign-table_on_coll', node: 'coll-foreign-table', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Foreign Table...') }}',
+ icon: 'wcTabIcon icon-foreign-table', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_foreign-table', node: 'foreign-table', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Foreign Table...') }}',
+ icon: 'wcTabIcon icon-foreign-table', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_foreign-table', node: 'schema', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Foreign Table...') }}',
+ icon: 'wcTabIcon icon-foreign-table', data: {action: 'create', check: false},
+ enable: 'canCreate'
+ }
+ ]);
+
+ },
+ canDrop: pgBrowser.Nodes['schema'].canChildDrop,
+ canDropCascade: pgBrowser.Nodes['schema'].canChildDrop,
+ model: pgAdmin.Browser.Node.Model.extend({
+ initialize: function(attrs, args) {
+ var isNew = (_.size(attrs) === 0);
+ if (isNew) {
+ // Set Selected Schema
+ schema = args.node_info.schema.label
+ this.set({'basensp': schema}, {silent: true});
+ }
+ pgAdmin.Browser.Node.Model.prototype.initialize.apply(this, arguments);
+ },
+ defaults: {
+ name: undefined,
+ oid: undefined,
+ owner: undefined,
+ basensp: undefined,
+ description: undefined,
+ ftsrvname: undefined,
+ strcolumn: undefined,
+ strftoptions: undefined,
+ inherits: [],
+ columns: [],
+ constraints: [],
+ ftoptions: [],
+ seclabels: []
+ },
+ schema: [{
+ id: 'name', label: '{{ _('Name') }}', cell: 'string',
+ type: 'text', mode: ['properties', 'create', 'edit']
+ },{
+ id: 'oid', label:'{{ _('Oid') }}', cell: 'string',
+ type: 'text' , mode: ['properties']
+ },{
+ id: 'owner', label:'{{ _('Owner') }}', cell: 'string', control: Backform.NodeListByNameControl,
+ node: 'role', type: 'text', mode: ['edit', 'properties']
+ },{
+ id: 'basensp', label:'{{ _('Schema') }}', cell: 'node-list-by-name',
+ control: 'node-list-by-name', cache_level: 'database', type: 'text',
+ node: 'schema', mode:['create', 'edit']
+ },{
+ id: 'description', label:'{{ _('Comment') }}', cell: 'string',
+ type: 'multiline'
+ },{
+ id: 'ftsrvname', label:'{{ _('Foreign Server') }}', cell: 'string', control: 'node-ajax-options',
+ type: 'text', group: 'Definition', url: 'get_foreign_servers', disabled: function(m) { return !m.isNew(); }
+ },{
+ id: 'inherits', label:'{{ _('Inherits') }}', cell: 'string', group: 'Definition',
+ type: 'list', min_version: 90500, control: 'node-ajax-options-multiple',
+ url: 'get_tables', select2: {multiple: true},
+ 'cache_level': 'database'
+ },{
+ id: 'strcolumn', label:'{{ _('Columns') }}', cell: 'string', group: 'Definition',
+ type: 'text', min_version: 90500, mode: ['properties']
+ },{
+ id: 'columns', label:'{{ _('Columns') }}', cell: 'string',
+ type: 'collection', group: 'Columns', visible: false, mode: ['edit', 'create'],
+ model: ColumnsModel, canAdd: true, canDelete: true, canEdit: false,
+ columns: ['attname', 'datatype', 'typlen', 'precision', 'typdefault', 'attnotnull', 'collname', 'inheritedfrom']
+ },
+ {
+ id: 'constraints', label:'{{ _('Constraints') }}', cell: 'string',
+ type: 'collection', group: 'Constraints', visible: false, mode: ['edit', 'create'],
+ model: ConstraintModel, canAdd: true, canDelete: true, columns: ['conname','consrc', 'connoinherit', 'convalidated'],
+ canEdit: function(o) {
+ if (o instanceof Backbone.Model) {
+ if (o instanceof ConstraintModel) {
+ return o.isNew();
+ }
+ }
+ return true;
+ }, min_version: 90500
+ },{
+ id: 'strftoptions', label:'{{ _('Options') }}', cell: 'string',
+ type: 'text', group: 'Definition', mode: ['properties']
+ },{
+ id: 'ftoptions', label:'{{ _('Options') }}', cell: 'string',
+ type: 'collection', group: 'Options', visible: false, mode: ['edit', 'create'],
+ model: OptionsModel, canAdd: true, canDelete: true, canEdit: false
+ },{
+ id: 'seclabels', label: '{{ _('Security Labels') }}',
+ model: SecurityModel, type: 'collection',
+ group: '{{ _('Security') }}', mode: ['edit', 'create'],
+ min_version: 90100, canAdd: true,
+ canEdit: false, canDelete: true,
+ control: 'unique-col-collection', uniqueCol : ['provider']
+ }
+ ],
+ validate: function()
+ {
+ var err = {},
+ errmsg,
+ seclabels = this.get('seclabels');
+
+ if (_.isUndefined(this.get('name')) || String(this.get('name')).replace(/^\s+|\s+$/g, '') == '') {
+ err['name'] = '{{ _('Name can not be empty!') }}';
+ errmsg = errmsg || err['name'];
+ }
+
+ if (_.isUndefined(this.get('basensp')) || String(this.get('basensp'))
+ .replace(/^\s+|\s+$/g, '') == '') {
+ err['basensp'] = '{{ _('Schema can not be empty!') }}';
+ errmsg = errmsg || err['basensp'];
+ }
+
+ if (_.isUndefined(this.get('ftsrvname')) || String(this.get('ftsrvname')).replace(/^\s+|\s+$/g, '') == '') {
+ err['ftsrvname'] = '{{ _('Foreign Server can not be empty!') }}';
+ errmsg = errmsg || err['ftsrvname'];
+ }
+
+ this.errorModel.clear().set(err);
+
+ return null;
+ }
+ }),
+ canCreate: function(itemData, item, data) {
+ //If check is false then , we will allow create menu
+ if (data && data.check == false)
+ return true;
+
+ var t = pgBrowser.tree, i = item, d = itemData;
+ // To iterate over tree to check parent node
+ while (i) {
+ // If it is schema then allow user to create foreign table
+ if (_.indexOf(['schema'], d._type) > -1)
+ return true;
+
+ if ('coll-foreign-table' == d._type) {
+ //Check if we are not child of catalog
+ prev_i = t.hasParent(i) ? t.parent(i) : null;
+ prev_d = prev_i ? t.itemData(prev_i) : null;
+ if( prev_d._type == 'catalog') {
+ return false;
+ } else {
+ return true;
+ }
+ }
+ i = t.hasParent(i) ? t.parent(i) : null;
+ d = i ? t.itemData(i) : null;
+ }
+ // by default we do not want to allow create menu
+ return true;
+ }
+ });
+
+ }
+
+ return pgBrowser.Nodes['foreign-table'];
+});
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/create.sql
new file mode 100644
index 0000000..8c46eca
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/create.sql
@@ -0,0 +1,28 @@
+{% import 'macros/schemas/security.macros' as SECLABLE %}
+{% if data %}
+CREATE FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }} ({% if data.columns %}
+{% for c in data.columns %}
+{% if not loop.first%} {% endif %}{{conn|qtIdent(c.attname)}} {{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}} {% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.attnotnull %}
+ NOT NULL{% else %} NULL{% endif %}
+{% if not loop.last %},
+{% endif %}{% endfor -%}{% endif %}
+)
+ SERVER {{ conn|qtIdent(data.ftsrvname) }}{% if data.ftoptions %}
+{% for o in data.ftoptions %}
+{% if o.option and o.value %}
+{% if loop.first %} OPTIONS ({% endif %}{% if not loop.first %}, {% endif %}{{o.option}} {{o.value|qtLiteral}}{% if loop.last %}){% endif %}{% endif %}
+{% endfor %}{% endif -%};
+{% if data.description %}
+
+COMMENT ON FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}
+ IS '{{ data.description }}';
+{% endif -%}
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{% if r.label and r.provider %}
+
+{{ SECLABLE.SET(conn, 'FOREIGN TABLE', data.name, r.provider, r.label, data.basensp) }}
+{% endif %}
+{% endfor %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/delete.sql
new file mode 100644
index 0000000..6243e05
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/delete.sql
@@ -0,0 +1,15 @@
+{% if scid and foid %}
+SELECT
+ c.relname AS name, nspname as basensp
+FROM
+ pg_class c
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+WHERE
+ c.relnamespace = {{scid}}::oid
+ AND c.oid = {{foid}}::oid;
+{% endif %}
+
+{% if name %}
+DROP FOREIGN TABLE {{ conn|qtIdent(basensp, name) }}{% if cascade %} CASCADE{% endif %};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_collations.sql
new file mode 100644
index 0000000..e59c17d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_collations.sql
@@ -0,0 +1,10 @@
+SELECT --nspname, collname,
+ CASE WHEN length(nspname) > 0 AND length(collname) > 0 THEN
+ concat(nspname, '."', collname,'"')
+ ELSE '' END AS copy_collation
+FROM
+ pg_collation c, pg_namespace n
+WHERE
+ c.collnamespace=n.oid
+ORDER BY
+ nspname, collname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_columns.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_columns.sql
new file mode 100644
index 0000000..4bf5e17
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_columns.sql
@@ -0,0 +1,22 @@
+SELECT
+ attname, attndims, atttypmod, format_type(t.oid,NULL) AS datatype,
+ format_type(t.oid, att.atttypmod) AS fulltype, attnotnull, attnum,
+ (SELECT COUNT(1) from pg_type t2 WHERE t2.typname=t.typname) > 1 AS isdup,
+ (
+ attname || ' ' || format_type(t.oid, att.atttypmod) || ' ' ||
+ (CASE WHEN attnotnull='true'
+ THEN 'NOT NULL' ELSE 'NULL'
+ END)
+ ) as strcolumn
+FROM
+ pg_attribute att
+JOIN
+ pg_type t ON t.oid=atttypid
+JOIN
+ pg_namespace nsp ON t.typnamespace=nsp.oid
+LEFT OUTER JOIN
+ pg_type b ON t.typelem=b.oid
+WHERE
+ att.attrelid={{foid}}::oid
+ AND attnum>0
+ORDER by attnum;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_constraints.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_constraints.sql
new file mode 100644
index 0000000..fc93549
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_constraints.sql
@@ -0,0 +1,7 @@
+SELECT
+ conname, contype, consrc
+FROM
+ pg_constraint
+WHERE
+ conrelid={{foid}}::oid
+ORDER by conname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_foreign_servers.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_foreign_servers.sql
new file mode 100644
index 0000000..1980f02
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_foreign_servers.sql
@@ -0,0 +1,5 @@
+SELECT
+ srvname
+FROM
+ pg_foreign_server
+ORDER BY srvname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_oid.sql
new file mode 100644
index 0000000..9a5f5d1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_oid.sql
@@ -0,0 +1,19 @@
+{% if basensp %}
+SELECT
+ c.oid, bn.oid as scid
+FROM
+ pg_class c
+JOIN
+ pg_namespace bn ON bn.oid=c.relnamespace
+WHERE
+ bn.nspname = {{ basensp|qtLiteral }}
+ AND c.relname={{ name|qtLiteral }};
+
+{% elif foid %}
+SELECT
+ c.relnamespace as scid
+FROM
+ pg_class c
+WHERE
+ c.oid = {{foid}}::oid;
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/node.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/node.sql
new file mode 100644
index 0000000..2d3a38b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/node.sql
@@ -0,0 +1,12 @@
+SELECT
+ c.oid, c.relname AS name, pg_get_userbyid(relowner) AS owner,
+ ftoptions, nspname as basensp
+FROM
+ pg_class c
+JOIN
+ pg_foreign_table ft ON c.oid=ft.ftrelid
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+WHERE
+ c.relnamespace = {{scid}}::oid
+ORDER BY c.relname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/properties.sql
new file mode 100644
index 0000000..25abc1b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/properties.sql
@@ -0,0 +1,25 @@
+SELECT
+ c.oid, c.relname AS name, pg_get_userbyid(relowner) AS owner,
+ ftoptions, srvname AS ftsrvname, description, nspname as basensp,
+ (SELECT
+ array_agg(provider || '=' || label)
+ FROM
+ pg_seclabel sl1
+ WHERE
+ sl1.objoid=c.oid) AS seclabels
+FROM
+ pg_class c
+JOIN
+ pg_foreign_table ft ON c.oid=ft.ftrelid
+LEFT OUTER JOIN
+ pg_foreign_server fs ON ft.ftserver=fs.oid
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=c.oid AND des.classoid='pg_class'::regclass)
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+WHERE
+ c.relnamespace = {{scid}}::oid
+ {% if foid %}
+ AND c.oid = {{foid}}::oid
+ {% endif %}
+ORDER BY c.relname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/update.sql
new file mode 100644
index 0000000..25fbcef
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/update.sql
@@ -0,0 +1,105 @@
+{% import 'macros/schemas/security.macros' as SECLABLE %}
+{% if data %}
+{% set name = o_data.name %}
+{% if data.name %}
+{% if data.name != o_data.name %}
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, o_data.name) }}
+RENAME TO {{ conn|qtIdent(data.name) }};
+{% set name = data.name %}
+{% endif %}
+{% endif -%}
+
+{% if data.owner %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OWNER TO {{ data.owner }};
+{% endif -%}
+
+{% if data.columns %}
+{% for c in data.columns.deleted %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+DROP COLUMN {{conn|qtIdent(c.attname)}};
+{% endfor -%}
+
+{% for c in data.columns.added %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ADD COLUMN {{conn|qtIdent(c.attname)}} {{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}} {% if c.precision %}, {{c.precision}}{% endif %}){% endif %}
+{% if c.attnotnull %} NOT NULL{% else %} NULL{% endif %}
+{% if c.typdefault %} DEFAULT {{c.typdefault}}{% endif %}
+{% if c.collname %} COLLATE {{c.collname}}{% endif %};
+{% endfor -%}
+
+{% for c in data.columns.changed %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+DROP COLUMN {{conn|qtIdent(o_data['columns'][c.attnum]['attname'])}};
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ADD COLUMN {{conn|qtIdent(c.attname)}} {{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}} {% if c.precision %}, {{c.precision}}{% endif %}){% endif %}
+{% if c.attnotnull %} NOT NULL{% else %} NULL{% endif %}
+{% if c.typdefault %} DEFAULT {{c.typdefault}}{% endif %}
+{% if c.collname %} COLLATE {{c.collname}}{% endif %};
+{% endfor %}
+{% endif -%}
+
+
+{% if data.ftoptions %}
+{% for o in data.ftoptions.deleted %}
+{% if o.option and o.value %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OPTIONS ( DROP {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+
+{% for o in data.ftoptions.added %}
+{% if o.option and o.value %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OPTIONS (ADD {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+
+{% for o in data.ftoptions.changed %}
+{% if o.option and o.value %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OPTIONS (SET {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+{% endif -%}
+
+
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+{{ SECLABLE.UNSET(conn, 'FOREIGN TABLE', name, r.provider, o_data.basensp) }}
+{% endfor %}
+{% endif -%}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+
+{{ SECLABLE.SET(conn, 'FOREIGN TABLE', name, r.provider, r.label, o_data.basensp) }}
+{% endfor %}
+{% endif -%}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+
+{{ SECLABLE.SET(conn, 'FOREIGN TABLE', name, r.provider, r.label, o_data.basensp) }}
+{% endfor %}
+{% endif -%}
+
+{% if data.description %}
+
+COMMENT ON FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ IS {{ data.description|qtLiteral }};
+{% endif -%}
+
+{% if data.basensp %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ SET SCHEMA {{ conn|qtIdent(data.basensp) }};
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/create.sql
new file mode 100644
index 0000000..4f970df
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/create.sql
@@ -0,0 +1,30 @@
+{% import 'macros/schemas/security.macros' as SECLABLE %}
+{% if data %}
+CREATE FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }} ({% if data.columns %}
+{% for c in data.columns %}
+{% if not loop.first%} {% endif %}{{conn|qtIdent(c.attname)}} {{conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.attnotnull %}
+ NOT NULL{% else %} NULL{% endif %}{% if c.typdefault %}
+ DEFAULT {{c.typdefault}}{% endif %}{% if c.collname %}
+ COLLATE {{c.collname}}{% endif %}
+{% if not loop.last %},
+{% endif %}{% endfor -%}{% endif %}
+)
+ SERVER {{ conn|qtIdent(data.ftsrvname) }}{% if data.ftoptions %}
+{% for o in data.ftoptions %}
+{% if o.option and o.value %}
+{% if loop.first %} OPTIONS ({% endif %}{% if not loop.first %}, {% endif %}{{o.option}} {{o.value|qtLiteral}}{% if loop.last %}){% endif %}{% endif %}
+{% endfor %}{% endif %};
+{% if data.description %}
+
+COMMENT ON FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}
+ IS '{{ data.description }}';
+{% endif -%}
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{% if r.security_label and r.provider %}
+
+{{ SECLABLE.SET(conn, 'FOREIGN TABLE', data.name, r.provider, r.security_label, data.basensp) }}
+{% endif %}
+{% endfor %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/delete.sql
new file mode 100644
index 0000000..5bb0969
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/delete.sql
@@ -0,0 +1,17 @@
+{% if scid and foid %}
+SELECT
+ c.relname AS name, nspname as basensp
+FROM
+ pg_class c
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+WHERE
+ c.relnamespace = {{scid}}::oid
+AND
+ c.oid = {{foid}}::oid;
+{% endif %}
+
+
+{% if name %}
+DROP FOREIGN TABLE {{ conn|qtIdent(basensp, name) }}{% if cascade%} CASCADE{% endif %};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_collations.sql
new file mode 100644
index 0000000..e8d274b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_collations.sql
@@ -0,0 +1,9 @@
+SELECT --nspname, collname,
+ CASE WHEN length(nspname) > 0 AND length(collname) > 0 THEN
+ concat(nspname, '."', collname,'"')
+ ELSE '' END AS copy_collation
+FROM
+ pg_collation c, pg_namespace n
+WHERE
+ c.collnamespace=n.oid
+ORDER BY nspname, collname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_columns.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_columns.sql
new file mode 100644
index 0000000..0824d3e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_columns.sql
@@ -0,0 +1,34 @@
+SELECT
+ attname, attndims, atttypmod, format_type(t.oid,NULL) AS datatype,
+ attnotnull, attnum, format_type(t.oid, att.atttypmod) AS fulltype,
+ CASE WHEN length(cn.nspname) > 0 AND length(cl.collname) > 0 THEN
+ concat(cn.nspname, '."', cl.collname,'"') ELSE '' END AS collname,
+ (SELECT COUNT(1) from pg_type t2 WHERE t2.typname=t.typname) > 1 AS isdup,
+ pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS typdefault,
+ (
+ attname || ' ' || format_type(t.oid, att.atttypmod) || ' ' ||
+ (CASE WHEN attnotnull='true'
+ THEN 'NOT NULL' ELSE 'NULL'
+ END) || ' ' ||
+ (CASE WHEN pg_catalog.pg_get_expr(def.adbin, def.adrelid)<>''
+ THEN 'DEFAULT ' || pg_catalog.pg_get_expr(def.adbin, def.adrelid)
+ ELSE '' END)
+ ) as strcolumn
+FROM
+ pg_attribute att
+JOIN
+ pg_type t ON t.oid=atttypid
+JOIN
+ pg_namespace nsp ON t.typnamespace=nsp.oid
+LEFT OUTER JOIN
+ pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
+LEFT OUTER JOIN
+ pg_type b ON t.typelem=b.oid
+LEFT OUTER JOIN
+ pg_collation cl ON t.typcollation=cl.oid
+LEFT OUTER JOIN
+ pg_namespace cn ON cl.collnamespace=cn.oid
+WHERE
+ att.attrelid={{foid}}::oid
+ AND attnum>0
+ORDER by attnum;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_constraints.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_constraints.sql
new file mode 100644
index 0000000..fc93549
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_constraints.sql
@@ -0,0 +1,7 @@
+SELECT
+ conname, contype, consrc
+FROM
+ pg_constraint
+WHERE
+ conrelid={{foid}}::oid
+ORDER by conname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_foreign_servers.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_foreign_servers.sql
new file mode 100644
index 0000000..1c95ce5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_foreign_servers.sql
@@ -0,0 +1,6 @@
+SELECT
+ srvname
+FROM
+ pg_foreign_server
+ORDER BY
+ srvname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_oid.sql
new file mode 100644
index 0000000..9a5f5d1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_oid.sql
@@ -0,0 +1,19 @@
+{% if basensp %}
+SELECT
+ c.oid, bn.oid as scid
+FROM
+ pg_class c
+JOIN
+ pg_namespace bn ON bn.oid=c.relnamespace
+WHERE
+ bn.nspname = {{ basensp|qtLiteral }}
+ AND c.relname={{ name|qtLiteral }};
+
+{% elif foid %}
+SELECT
+ c.relnamespace as scid
+FROM
+ pg_class c
+WHERE
+ c.oid = {{foid}}::oid;
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/node.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/node.sql
new file mode 100644
index 0000000..2d3a38b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/node.sql
@@ -0,0 +1,12 @@
+SELECT
+ c.oid, c.relname AS name, pg_get_userbyid(relowner) AS owner,
+ ftoptions, nspname as basensp
+FROM
+ pg_class c
+JOIN
+ pg_foreign_table ft ON c.oid=ft.ftrelid
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+WHERE
+ c.relnamespace = {{scid}}::oid
+ORDER BY c.relname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/properties.sql
new file mode 100644
index 0000000..3ff088f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/properties.sql
@@ -0,0 +1,27 @@
+SELECT
+ c.oid, c.relname AS name, pg_get_userbyid(relowner) AS owner,
+ ftoptions, srvname AS ftsrvname, description, nspname as basensp, consrc,
+ (SELECT
+ array_agg(provider || '=' || label)
+ FROM
+ pg_shseclabel sl1
+ WHERE
+ sl1.objoid=c.oid) AS seclabels
+FROM
+ pg_class c
+JOIN
+ pg_foreign_table ft ON c.oid=ft.ftrelid
+LEFT OUTER JOIN
+ pg_foreign_server fs ON ft.ftserver=fs.oid
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=c.oid AND des.classoid='pg_class'::regclass)
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+LEFT OUTER JOIN
+ pg_constraint cn ON (cn.conrelid=c.oid)
+WHERE
+ c.relnamespace = {{scid}}::oid
+ {% if foid %}
+ AND c.oid = {{foid}}::oid
+ {% endif %}
+ORDER BY c.relname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/update.sql
new file mode 100644
index 0000000..73b0508
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/update.sql
@@ -0,0 +1,113 @@
+{% import 'macros/schemas/security.macros' as SECLABLE %}
+{% if data %}
+{% set name = o_data.name %}
+{% if data.name %}
+{% if data.name != o_data.name %}
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, o_data.name) }}
+RENAME TO {{ conn|qtIdent(data.name) }};
+{% set name = data.name %}
+{% endif %}
+{% endif -%}
+{% if data.owner %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OWNER TO {{ data.owner }};
+{% endif -%}
+{% if data.columns %}
+{% for c in data.columns.deleted %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+DROP COLUMN {{conn|qtIdent(c.attname)}};
+{% endfor -%}
+{% for c in data.columns.added %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ADD COLUMN {{conn|qtIdent(c.attname)}} {{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}} {% if c.precision %}, {{c.precision}}{% endif %}){% endif %}
+{% if c.attnotnull %} NOT NULL{% else %} NULL{% endif %}
+{% if c.typdefault %} DEFAULT {{c.typdefault}}{% endif %}
+{% if c.collname %} COLLATE {{c.collname}}{% endif %};
+{% endfor -%}
+{% for c in data.columns.changed %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+DROP COLUMN {{conn|qtIdent(o_data['columns'][c.attnum]['attname'])}};
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ADD COLUMN {{conn|qtIdent(c.attname)}} {{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}} {% if c.precision %}, {{c.precision}}{% endif %}){% endif %}
+{% if c.attnotnull %} NOT NULL{% else %} NULL{% endif %}
+{% if c.typdefault %} DEFAULT {{c.typdefault}}{% endif %}
+{% if c.collname %} COLLATE {{c.collname}}{% endif %};
+{% endfor -%}
+{% endif -%}
+{% if data.constraints %}
+{% for c in data.constraints.deleted %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+DROP CONSTRAINT {{conn|qtIdent(c.conname)}};
+{% endfor %}
+
+{% for c in data.constraints.added %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ADD CONSTRAINT {{conn|qtIdent(c.conname)}} {% if c.consrc %} CHECK ({{c.consrc}}){% endif %} {% if c.connoinherit %} NO INHERIT{% endif %};
+{% endfor %}
+{% endif -%}
+
+{% if data.ftoptions %}
+{% for o in data.ftoptions.deleted %}
+{% if o.option and o.value %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OPTIONS ( DROP {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+
+{% for o in data.ftoptions.added %}
+{% if o.option and o.value %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OPTIONS (ADD {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+
+{% for o in data.ftoptions.changed %}
+{% if o.option and o.value %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OPTIONS (SET {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+{% endif -%}
+
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+
+{{ SECLABLE.UNSET(conn, 'FOREIGN TABLE', name, r.provider, o_data.basensp) }}
+{% endfor %}
+{% endif -%}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+
+{{ SECLABLE.SET(conn, 'FOREIGN TABLE', name, r.provider, r.label, o_data.basensp) }}
+{% endfor %}
+{% endif -%}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+
+{{ SECLABLE.SET(conn, 'FOREIGN TABLE', name, r.provider, r.label, o_data.basensp) }}
+{% endfor %}
+{% endif -%}
+
+{% if data.description %}
+
+COMMENT ON FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ IS {{ data.description|qtLiteral }};
+{% endif -%}
+
+{% if data.basensp %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ SET SCHEMA {{ conn|qtIdent(data.basensp) }};
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/create.sql
new file mode 100644
index 0000000..409b294
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/create.sql
@@ -0,0 +1,46 @@
+{% import 'macros/schemas/security.macros' as SECLABLE %}
+{% set is_columns = [] %}
+{% if data %}
+CREATE FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}
+({% if data.columns %}
+{% for c in data.columns %}
+{% if (not c.inheritedfrom or c.inheritedfrom =='' or c.inheritedfrom == None or c.inheritedfrom == 'None' ) %}
+{% if is_columns.append('1') %}{% endif %}
+{{conn|qtIdent(c.attname)}} {{conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}
+{% if c.attnotnull %} NOT NULL{% else %} NULL{% endif %}
+{% if c.typdefault %} DEFAULT{{c.typdefault}}{% endif %}
+{% if c.collname %} COLLATE{{c.collname}}{% endif %}
+{% if not loop.last %},
+{% endif %}{% endif %}{% endfor -%}
+{% endif -%}
+)
+{% if data.inherits %}
+ INHERITS ({% for i in data.inherits %}{% if i %}{{i}}{% if not loop.last %}, {% endif %}{% endif %}{% endfor %})
+{% endif %}
+ SERVER {{ conn|qtIdent(data.ftsrvname) }}{% if data.ftoptions %}
+{% for o in data.ftoptions %}
+
+{% if o.option and o.value %}{% if loop.first %} OPTIONS ({% endif %}{% if not loop.first %}, {% endif %}{{o.option}} {{o.value|qtLiteral}}{% if loop.last %}){% endif %}{% endif %}
+{% endfor %}{% endif %};
+{% if data.constraints %}
+{% for c in data.constraints %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}
+ ADD CONSTRAINT {{ conn|qtIdent(c.conname) }} CHECK ({{ c.consrc }}){% if not c.convalidated %} NOT VALID{% endif %}{% if c.connoinherit %} NO INHERIT{% endif %};
+{% endfor %}
+{% endif %}
+{% if data.description %}
+
+COMMENT ON FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}
+ IS '{{ data.description }}';
+{% endif -%}
+
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{% if r.label and r.provider %}
+
+{{ SECLABLE.SET(conn, 'FOREIGN TABLE', data.name, r.provider, r.label, data.basensp) }}
+{% endif %}
+{% endfor %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/delete.sql
new file mode 100644
index 0000000..5bb0969
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/delete.sql
@@ -0,0 +1,17 @@
+{% if scid and foid %}
+SELECT
+ c.relname AS name, nspname as basensp
+FROM
+ pg_class c
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+WHERE
+ c.relnamespace = {{scid}}::oid
+AND
+ c.oid = {{foid}}::oid;
+{% endif %}
+
+
+{% if name %}
+DROP FOREIGN TABLE {{ conn|qtIdent(basensp, name) }}{% if cascade%} CASCADE{% endif %};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_collations.sql
new file mode 100644
index 0000000..e8d274b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_collations.sql
@@ -0,0 +1,9 @@
+SELECT --nspname, collname,
+ CASE WHEN length(nspname) > 0 AND length(collname) > 0 THEN
+ concat(nspname, '."', collname,'"')
+ ELSE '' END AS copy_collation
+FROM
+ pg_collation c, pg_namespace n
+WHERE
+ c.collnamespace=n.oid
+ORDER BY nspname, collname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_columns.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_columns.sql
new file mode 100644
index 0000000..b0602ed
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_columns.sql
@@ -0,0 +1,46 @@
+SELECT
+ att.attname, att.attndims, att.atttypmod, format_type(t.oid,NULL) AS datatype,
+ att.attnotnull, att.attnum, format_type(t.oid, att.atttypmod) AS fulltype,
+ CASE WHEN length(cn.nspname) > 0 AND length(cl.collname) > 0 THEN
+ concat(cn.nspname, '."', cl.collname,'"')
+ ELSE '' END AS collname,
+ pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS typdefault,
+ (
+ attname || ' ' || format_type(t.oid, att.atttypmod) || ' ' ||
+ (CASE WHEN attnotnull='true'
+ THEN 'NOT NULL' ELSE 'NULL'
+ END) || ' ' ||
+ (CASE WHEN pg_catalog.pg_get_expr(def.adbin, def.adrelid)<>''
+ THEN 'DEFAULT ' || pg_catalog.pg_get_expr(def.adbin, def.adrelid)
+ ELSE '' END)
+ ) as strcolumn,
+ (SELECT concat(nmsp_parent.nspname, '.',parent.relname ) as inheritedfrom
+ FROM
+ pg_attribute at
+ JOIN
+ pg_inherits ph ON ph.inhparent = at.attrelid AND ph.inhrelid = {{foid}}::oid
+ JOIN
+ pg_class parent ON ph.inhparent = parent.oid
+ JOIN
+ pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
+ WHERE at.attname = att.attname
+ ),
+ (SELECT COUNT(1) from pg_type t2 WHERE t2.typname=t.typname) > 1 AS isdup
+FROM
+ pg_attribute att
+JOIN
+ pg_type t ON t.oid=atttypid
+JOIN
+ pg_namespace nsp ON t.typnamespace=nsp.oid
+LEFT OUTER JOIN
+ pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
+LEFT OUTER JOIN
+ pg_type b ON t.typelem=b.oid
+LEFT OUTER JOIN
+ pg_collation cl ON t.typcollation=cl.oid
+LEFT OUTER JOIN
+ pg_namespace cn ON cl.collnamespace=cn.oid
+WHERE
+ att.attrelid={{foid}}::oid
+ AND att.attnum>0
+ORDER by att.attnum;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_constraints.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_constraints.sql
new file mode 100644
index 0000000..7560c31
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_constraints.sql
@@ -0,0 +1,7 @@
+SELECT
+ oid as conoid, conname, contype, consrc, connoinherit, convalidated
+FROM
+ pg_constraint
+WHERE
+ conrelid={{foid}}::oid
+ORDER by conname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_foreign_servers.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_foreign_servers.sql
new file mode 100644
index 0000000..717bdd3
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_foreign_servers.sql
@@ -0,0 +1,6 @@
+SELECT
+ srvname
+FROM
+ pg_foreign_server
+ORDER
+ BY srvname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_oid.sql
new file mode 100644
index 0000000..9a5f5d1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_oid.sql
@@ -0,0 +1,19 @@
+{% if basensp %}
+SELECT
+ c.oid, bn.oid as scid
+FROM
+ pg_class c
+JOIN
+ pg_namespace bn ON bn.oid=c.relnamespace
+WHERE
+ bn.nspname = {{ basensp|qtLiteral }}
+ AND c.relname={{ name|qtLiteral }};
+
+{% elif foid %}
+SELECT
+ c.relnamespace as scid
+FROM
+ pg_class c
+WHERE
+ c.oid = {{foid}}::oid;
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_table_columns.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_table_columns.sql
new file mode 100644
index 0000000..e1882db
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_table_columns.sql
@@ -0,0 +1,14 @@
+{% if attrelid %}
+SELECT
+ a.attname, format_type(a.atttypid, NULL) AS datatype,
+ quote_ident(n.nspname)||'.'||quote_ident(c.relname) as inheritedfrom,
+ c.oid as inheritedid
+FROM
+ pg_class c
+JOIN
+ pg_namespace n ON c.relnamespace=n.oid
+JOIN
+ pg_attribute a ON a.attrelid = c.oid AND NOT a.attisdropped AND a.attnum>0
+WHERE
+ c.oid = {{attrelid}}::OID
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_tables.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_tables.sql
new file mode 100644
index 0000000..3085a26
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_tables.sql
@@ -0,0 +1,19 @@
+{% if attrelid %}
+SELECT
+ array_agg(quote_ident(n.nspname) || '.' || quote_ident(c.relname)) as inherits
+FROM
+ pg_class c, pg_namespace n
+WHERE
+ c.relnamespace=n.oid AND c.relkind IN ('r', 'f')
+ AND c.oid in {{attrelid}};
+
+{% else %}
+SELECT
+ c.oid AS id, quote_ident(n.nspname) || '.' || quote_ident(c.relname) as text
+FROM
+ pg_class c, pg_namespace n
+WHERE
+ c.relnamespace=n.oid AND c.relkind IN ('r', 'f')
+ORDER BY
+ n.nspname, c.relname;
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/node.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/node.sql
new file mode 100644
index 0000000..2d3a38b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/node.sql
@@ -0,0 +1,12 @@
+SELECT
+ c.oid, c.relname AS name, pg_get_userbyid(relowner) AS owner,
+ ftoptions, nspname as basensp
+FROM
+ pg_class c
+JOIN
+ pg_foreign_table ft ON c.oid=ft.ftrelid
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+WHERE
+ c.relnamespace = {{scid}}::oid
+ORDER BY c.relname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/properties.sql
new file mode 100644
index 0000000..c20b824
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/properties.sql
@@ -0,0 +1,31 @@
+SELECT
+ c.oid, c.relname AS name, pg_get_userbyid(relowner) AS owner,
+ ftoptions, srvname AS ftsrvname, description, nspname AS basensp,
+ (SELECT
+ array_agg(provider || '=' || label)
+ FROM
+ pg_shseclabel sl1
+ WHERE
+ sl1.objoid=c.oid) AS seclabels
+ {% if foid %},
+ (SELECT
+ array_agg(i.inhparent) FROM pg_inherits i
+ WHERE
+ i.inhrelid = {{foid}}::oid GROUP BY i.inhrelid) AS inherits
+ {% endif %}
+FROM
+ pg_class c
+JOIN
+ pg_foreign_table ft ON c.oid=ft.ftrelid
+LEFT OUTER JOIN
+ pg_foreign_server fs ON ft.ftserver=fs.oid
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=c.oid AND des.classoid='pg_class'::regclass)
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+WHERE
+ c.relnamespace = {{scid}}::oid
+ {% if foid %}
+ AND c.oid = {{foid}}::oid
+ {% endif %}
+ORDER BY c.relname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/update.sql
new file mode 100644
index 0000000..e7cc1a5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/update.sql
@@ -0,0 +1,139 @@
+{% import 'macros/schemas/security.macros' as SECLABLE %}
+{% if data %}
+{% set name = o_data.name %}
+{% if data.name %}{% if data.name != o_data.name %}
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, o_data.name) }}
+ RENAME TO {{ conn|qtIdent(data.name) }};
+{% set name = data.name %}
+{% endif %}{% endif %}
+{% if data.owner %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{% if data.columns %}
+{% for c in data.columns.deleted %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ DROP COLUMN {{conn|qtIdent(c.attname)}};
+{% endfor -%}
+{% for c in data.columns.added %}
+{% if (not c.inheritedfrom or c.inheritedfrom =='' or c.inheritedfrom == None or c.inheritedfrom == 'None' ) %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ADD COLUMN {{conn|qtIdent(c.attname)}} {{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}} {% if c.precision %}, {{c.precision}}{% endif %}){% endif %}
+{% if c.attnotnull %} NOT NULL{% else %} NULL{% endif %}
+{% if c.typdefault %} DEFAULT {{c.typdefault}}{% endif %}
+{% if c.collname %} COLLATE {{c.collname}}{% endif %};
+{% endif %}
+{% endfor -%}
+{% for c in data.columns.changed %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ DROP COLUMN {{conn|qtIdent(o_data['columns'][c.attnum]['attname'])}};
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ADD COLUMN {{conn|qtIdent(c.attname)}} {{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}} {% if c.precision %}, {{c.precision}}{% endif %}){% endif %}
+{% if c.attnotnull %} NOT NULL{% else %} NULL{% endif %}
+{% if c.typdefault %} DEFAULT {{c.typdefault}}{% endif %}
+{% if c.collname %} COLLATE {{c.collname}}{% endif %};
+{% endfor %}
+{% endif %}
+{% if data.inherits and data.inherits|length > 0%}
+{% if o_data.inherits == None or o_data.inherits == 'None' %}
+{% set inherits = '' %}
+{% else %}
+{% set inherits = o_data.inherits %}
+{% endif %}
+{% for i in data.inherits %}
+{% if i not in inherits %}{% if i %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }} INHERIT {{i}};
+{% endif %}
+{% endif %}
+{% endfor %}
+{% endif %}
+{% if o_data.inherits and 'inherits' in data %}
+{% if data.inherits == None or data.inherits == 'None' %}
+{% set inherits = '' %}
+{% else %}
+{% set inherits = data.inherits %}
+{% endif %}
+{% for i in o_data.inherits %}{% if i not in inherits %}{% if i %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }} NO INHERIT {{i}};{% endif %}
+{% endif %}
+{% endfor %}
+{% endif %}
+{% if data.constraints %}
+{% for c in data.constraints.deleted %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ DROP CONSTRAINT {{conn|qtIdent(c.conname)}};
+{% endfor -%}
+{% for c in data.constraints.added %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ADD CONSTRAINT {{ conn|qtIdent(c.conname) }} CHECK ({{ c.consrc }}){% if not c.convalidated %} NOT VALID{% endif %}{% if c.connoinherit %} NO INHERIT{% endif %};
+{% endfor %}
+{% for c in data.constraints.changed %}
+{% if c.convalidated %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ VALIDATE CONSTRAINT {{ conn|qtIdent(c.conname) }};
+{% endif %}
+{% endfor %}
+{% endif %}
+{% if data.ftoptions %}
+{% for o in data.ftoptions.deleted %}
+{% if o.option and o.value %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ OPTIONS ( DROP {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+{% for o in data.ftoptions.added %}
+{% if o.option and o.value %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ OPTIONS (ADD {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+{% for o in data.ftoptions.changed %}
+{% if o.option and o.value %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ OPTIONS (SET {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+{% endif -%}
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+
+{{ SECLABLE.UNSET(conn, 'FOREIGN TABLE', name, r.provider, o_data.basensp) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+
+{{ SECLABLE.SET(conn, 'FOREIGN TABLE', name, r.provider, r.label, o_data.basensp) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+
+{{ SECLABLE.SET(conn, 'FOREIGN TABLE', name, r.provider, r.label, o_data.basensp) }}
+{% endfor %}
+{% endif -%}
+{% if data.description %}
+
+COMMENT ON FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+IS {{ data.description|qtLiteral }};
+{% endif -%}
+{% if data.basensp %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ SET SCHEMA {{ conn|qtIdent(data.basensp) }};
+{% endif %}
+{% endif %}
--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
