public inbox for [email protected]
help / color / mirror / Atom feedFrom: Khushboo Vashi <[email protected]>
To: Murtuza Zabuawala <[email protected]>
Cc: Dave Page <[email protected]>
Cc: Ashesh Vashi <[email protected]>
Cc: Neel Patel <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: [pgAdmin4] [Patch]: Foreign Table Module
Date: Sun, 15 May 2016 13:07:22 +0530
Message-ID: <CAFOhELceEWd_o0t8GgBTToc3R_6Or2-eEMzQD=XTbQ37_TRPDQ@mail.gmail.com> (raw)
In-Reply-To: <CAKKotZS4xxwn9o=Pw_T1Sk+WP7b+gcmhjvgLJ+RFKnBQRTKX6Q@mail.gmail.com>
References: <CAFOhELeqEBG3qd+Fe8nLfpc1SuJpkJzuzPmy1-jogD0220WiLw@mail.gmail.com>
<CAFOhELfm9y9jQLLzvnQpKey5TX=yZkL+qaF=6g+CsvuWKFDh2w@mail.gmail.com>
<CAFOhELekGKVKaMQt9L_itJw2WGdG69yVdiJDfq=1BPz3OjRJiw@mail.gmail.com>
<CACCA4P0Q_9DBt43asNze9OjkJ+xya_m_cmf3DMoZB3HKWLKidw@mail.gmail.com>
<CAFOhELf010FXe8_1AsBcc_GxrLYD5QzpQft+URS6zwf=pxtCiA@mail.gmail.com>
<[email protected]>
<CAFOhELeXyKfYoR4TAA2h8XErhQ7f1X9xkykpmN+Q4C52WMkG=Q@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAFOhELfqvoDVTJG7SE6aWnA-itzCUg91xWMknTin7NDfkRGHkA@mail.gmail.com>
<CAG7mmozo8+OFaYXx_GX81Y2ow=y=3EEuzb=w9coRJAEZbZ3cGw@mail.gmail.com>
<CA+OCxoz26u=CXOHzZkS=x_nTBjQFRzhUa3Yhb0J1hqhutxV_ow@mail.gmail.com>
<CAFOhELeUGNkk=gy4+kAYrbsL3gDg7DH5m7UU9dKPOPSUtOby3g@mail.gmail.com>
<CAKKotZQAxEppv8eMJPCcE9qi0=Nu49su=QjdMzdS+5Hk2sYGxA@mail.gmail.com>
<CAFOhELcKCCAMQYCrSj0Wz17DozYC+RrFGsVfTy8P6gjjVQ3Evw@mail.gmail.com>
<CAKKotZS4xxwn9o=Pw_T1Sk+WP7b+gcmhjvgLJ+RFKnBQRTKX6Q@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
Hi,
Please find the updated patch for the foreign table.
Thanks,
Khushboo
On Sat, May 14, 2016 at 10:36 PM, Murtuza Zabuawala <
[email protected]> wrote:
> Hi,
>
> One last otherwise rest of functionality looks good.
>
> - We are not able to drop/delete any constraints, not even empty blank row
> once added.
>
> Fixed
>
> Regards,
> Murtuza
>
>
> --
> Regards,
> Murtuza Zabuawala
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> On Fri, May 13, 2016 at 3:25 PM, Khushboo Vashi <
> [email protected]> wrote:
>
>> Hi,
>>
>> Please find the attached updated patch for the foreign table module.
>>
>> Thanks,
>> Khushboo
>>
>> On Fri, May 13, 2016 at 10:50 AM, Murtuza Zabuawala <
>> [email protected]> wrote:
>>
>>> *1)* If we delete one inherited table and add another one
>>> It generated drop columns sql for that inherited table which throws
>>> error,
>>> for example "Test123"."TEST" has col1, col2 in it, once we remove table
>>> see the wrong drop column sql.
>>>
>>> SQL:
>>> ----
>>> ALTER FOREIGN TABLE pem.asdas
>>> DROP COLUMN col1;
>>>
>>> ALTER FOREIGN TABLE pem.asdas
>>> DROP COLUMN col2;
>>>
>>> ALTER FOREIGN TABLE pem.asdas INHERIT pemdata.number_of_wal_files;
>>>
>>> ALTER FOREIGN TABLE pem.asdas NO INHERIT "Test123"."TEST";
>>>
>>> Expected:
>>> ---------
>>> ALTER FOREIGN TABLE pem.asdas INHERIT pemdata.number_of_wal_files;
>>>
>>> ALTER FOREIGN TABLE pem.asdas NO INHERIT "Test123"."TEST";
>>>
>>> Done
>>
>>>
>>> *2)* In EDIT mode, Changing just column name of existing column first
>>> drops the entire column
>>> and adds it again, instead of just renaming column name
>>>
>>> (*** Consider a case when user have data in the table & just want to
>>> rename column col1 to col2
>>> or any other attribute like default value/not null etc, why do we need
>>> to drop entire column in that case? ***)
>>>
>>> SQL:
>>> ----
>>> ALTER FOREIGN TABLE test.test_ft
>>> DROP COLUMN c1;
>>>
>>> ALTER FOREIGN TABLE test.test_ft
>>> ADD COLUMN c12 bigint NOT NULL;
>>>
>>> Expected:
>>> ---------
>>> ALTER FOREIGN TABLE test.test_ft
>>> RENAME c1 TO c12;
>>>
>>>
>>> Done
>>
>>> *3)* We should not allow user to delete columns inherited from
>>> other table in edit mode from UI
>>>
>>>
>>> Done
>>
>>> *4)* We should not allow user to delete constraint inherited from
>>> other table in edit mode from UI
>>>
>>> Done
>>
>>>
>>> *5)* Issue from my last email still exists, changing type of column
>>> creates wrong sql if it has Length/Precision.
>>>
>>> eg: col12 was numeric(12) which I changed to aclitem
>>> See the wrong sql
>>>
>>> ALTER FOREIGN TABLE pem."test-2"
>>> DROP COLUMN col12;
>>>
>>> ALTER FOREIGN TABLE pem."test-2"
>>> ADD COLUMN col12 aclitem(12) NOT NULL COLLATE pg_catalog."default";
>>>
>>>
>>> Expected:
>>> ---------
>>> ALTER FOREIGN TABLE pem."test-2"
>>> ADD COLUMN col12 aclitem;
>>>
>>> Done
>>
>>>
>>> *6)* EDIT mode, clearing Data Type from column throws Error on console
>>>
>>> TypeError: this.dataAdapter is null
>>>
>>> Couldn't reproduce.
>>
>>>
>>> *7)* EDIT mode, If i have blank column row and clicks on SQL tab throws
>>> error
>>>
>>> app_iter = app(environ, start_response)
>>> TypeError: 'KeyError' object is not callable
>>>
>>> Done
>>
>>>
>>> *8)* User can clear Owner which we should not allow to clear OR we have
>>> to generate SQL for that opration
>>>
>>>
>>> Done
>>
>>> *9)* I do not have option to provide Statistics attribute for column
>>>
>>> Done
>>
>>>
>>> --
>>> Regards,
>>> Murtuza Zabuawala
>>> EnterpriseDB: http://www.enterprisedb.com
>>> The Enterprise PostgreSQL Company
>>>
>>> On Wed, May 11, 2016 at 3:17 PM, Khushboo Vashi <
>>> [email protected]> wrote:
>>>
>>>> Hi,
>>>>
>>>> Please find the attached updated patch for the Foreign Table module.
>>>>
>>>> Thanks,
>>>> Khushboo
>>>>
>>>> On Tue, May 10, 2016 at 4:33 PM, Dave Page <[email protected]> wrote:
>>>>
>>>>>
>>>>>
>>>>> On Mon, May 9, 2016 at 1:23 PM, Ashesh Vashi <
>>>>> [email protected]> wrote:
>>>>>
>>>>>> On Mon, May 9, 2016 at 5:50 PM, Khushboo Vashi <
>>>>>> [email protected]> wrote:
>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> Please find the attached updated patch for the Foreign Tables module.
>>>>>>>
>>>>>>
>>>>> Feedback from my initial testing:
>>>>>
>>>>> - Owner is missing from the New dialogue.
>>>>>
>>>>> Done
>>>>
>>>>> - The Properties list shows "Select from the list" in a combo box for
>>>>> Owner and
>>>>> Inherits.
>>>>>
>>>>
>>>>
>>>>>
>>>>>
>>>> This is a generalize issue, I will create a new Kanban card for the
>>>> same.
>>>>
>>>>> - s/Oid/OID
>>>>>
>>>>> Done
>>>>
>>>>> - s/Foreign Server/Foreign server
>>>>>
>>>>> Done
>>>>
>>>>> - SQL Help filenames are not defined.
>>>>>
>>>>> Done
>>>>
>>>>> - SQL formatting is weird, e.g.
>>>>>
>>>>> CREATE FOREIGN TABLE public.redis_table
>>>>> (id text NOT NULL COLLATE pg_catalog."default",
>>>>> value text NULL COLLATE pg_catalog."default")
>>>>> SERVER redis_server1
>>>>> OPTIONS (database '0');
>>>>>
>>>>> instead of:
>>>>>
>>>>> CREATE FOREIGN TABLE public.redis_table (
>>>>> id text NOT NULL COLLATE pg_catalog."default",
>>>>> value text NULL COLLATE pg_catalog."default"
>>>>> )
>>>>> SERVER redis_server1
>>>>> OPTIONS (database '0');
>>>>>
>>>>>
>>>> Done
>>>>
>>>>> - In the validation error messages, please replace ! with . and "can
>>>>> not" with
>>>>> "cannot" for consistency with recently reviewed strings.
>>>>>
>>>>>
>>>> Done
>>>>
>>>>> - s/mulitple/multiple (in the js).
>>>>>
>>>>> Done
>>>>
>>>>> - How do I manage the ACL?
>>>>>
>>>>> Done. I missed this as I was following pgAdmin-3 as usual.
>>>>
>>>>> Thanks.
>>>>>
>>>>>
>>>>> --
>>>>> Dave Page
>>>>> Blog: http://pgsnake.blogspot.com
>>>>> Twitter: @pgsnake
>>>>>
>>>>> EnterpriseDB UK: http://www.enterprisedb.com
>>>>> The Enterprise PostgreSQL Company
>>>>>
>>>>
>>>>
>>>
>>
>
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
Attachments:
[text/x-patch] pgAdmin4_foreign_tables_ver6.patch (120.3K, 3-pgAdmin4_foreign_tables_ver6.patch)
download | inline diff:
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..1fb6d3c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py
@@ -0,0 +1,1121 @@
+##########################################################################
+#
+# 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, gone
+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
+from pgadmin.browser.server_groups.servers.utils import parse_priv_from_db, \
+ parse_priv_to_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', 'acl']
+ 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] = [] if\
+ type(req[key]) == list and len(req[key]) == 0 else \
+ 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 >= 90200:
+ self.template_path = 'foreign_tables/sql/9.2_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)
+
+ if not data:
+ return gone(gettext("""
+Could not find the foreign table in the database.
+It may have been removed by another user or
+shifted to the another schema.
+"""))
+
+ 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']), foid=foid)
+ 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)
+
+ if not old_data:
+ return gone(gettext("""
+ Could not find the foreign table in the database.
+ It may have been removed by another user or
+ shifted to the another schema.
+ """))
+
+ # 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
+
+ if 'columns' in data and 'added' in data['columns']:
+ data['columns']['added'] = self._format_columns(
+ data['columns']['added'])
+
+ if 'columns' in data and 'changed' in data['columns']:
+ data['columns']['changed'] = self._format_columns(
+ data['columns']['changed'])
+
+ # Parse Privileges
+ if 'acl' in data and 'added' in data['acl']:
+ data['acl']['added'] = parse_priv_to_db(data['acl']['added'],
+ ["a", "r", "w", "x"])
+ if 'acl' in data and 'changed' in data['acl']:
+ data['acl']['changed'] = parse_priv_to_db(
+ data['acl']['changed'],["a", "r", "w", "x"])
+ if 'acl' in data and 'deleted' in data['acl']:
+ data['acl']['deleted'] = parse_priv_to_db(
+ data['acl']['deleted'],["a", "r", "w", "x"])
+
+ SQL = render_template(
+ "/".join([self.template_path, 'update.sql']),
+ data=data, o_data=old_data
+ )
+ else:
+ data['columns'] = self._format_columns(data['columns'])
+
+ # Parse Privileges
+ if 'acl' in data:
+ data['acl'] = parse_priv_to_db(data['acl'],
+ ["a", "r", "w", "x"])
+
+ 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 _format_columns(self, columns):
+ """
+ Format Table Columns.
+ """
+ cols = []
+ for c in columns:
+ if len(c) > 0:
+ if '[]' in c['datatype']:
+ c['datatype'] = c['datatype'].replace('[]', '')
+ c['isArrayType'] = True
+ else:
+ c['isArrayType'] = False
+ cols.append(c)
+
+ return cols
+
+
+ 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)
+
+ if len(res['rows']) == 0:
+ return False
+
+ data = res['rows'][0]
+
+ if self.manager.version >= 90200:
+ # Fetch privileges
+ SQL = render_template("/".join([self.template_path, 'acl.sql']),
+ foid=foid)
+ status, aclres = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=aclres)
+
+ # Get Formatted Privileges
+ data.update(self._format_proacl_from_db(aclres['rows']))
+
+ # 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:c['fulltype'].find(")")]
+ typlen = substr.split(",")
+ if len(typlen) > 1:
+ c['typlen'] = int(typlen[0])
+ c['precision'] = int(typlen[1])
+ else:
+ c['typlen'] = int(typlen[0])
+ c['precision'] = None
+
+ 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 _format_proacl_from_db(self, proacl):
+ """
+ Returns privileges.
+ Args:
+ proacl: Privileges Dict
+ """
+ privileges = []
+ for row in proacl:
+ priv = parse_priv_from_db(row)
+ privileges.append(priv)
+
+ return {"acl": privileges}
+
+ 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..e6ed9f6
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/js/foreign_tables.js
@@ -0,0 +1,751 @@
+/* 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.security_label) ||
+ _.isNull(data.security_label) ||
+ String(data.security_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);
+ },
+ dependentChanged: 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,
+ attstattarget: undefined
+ },
+ type_options: undefined,
+ schema: [{
+ id: 'attname', label:'{{ _('Name') }}', cell: 'string', type: 'text',
+ editable: 'is_editable_column', cellHeaderClasses: 'width_percent_20'
+ },{
+ id: 'datatype', label:'{{ _('Data Type') }}', cell: 'node-ajax-options',
+ control: 'node-ajax-options', type: 'text', url: 'get_types',
+ editable: 'is_editable_column', 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
+ if(!(_.isUndefined(m.get('inheritedid'))
+ || _.isNull(m.get('inheritedid'))
+ || _.isUndefined(m.get('inheritedfrom'))
+ || _.isNull(m.get('inheritedfrom')))) { return false; }
+
+ 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 ) {
+ m.set('typlen', undefined);
+ // 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) {
+ if(!(_.isUndefined(m.get('inheritedid'))
+ || _.isNull(m.get('inheritedid'))
+ || _.isUndefined(m.get('inheritedfrom'))
+ || _.isNull(m.get('inheritedfrom')))) { return false; }
+
+ 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 ) {
+ m.set('precision', undefined);
+ // 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(!(_.isUndefined(m.get('inheritedid'))
+ || _.isNull(m.get('inheritedid'))
+ || _.isUndefined(m.get('inheritedfrom'))
+ || _.isNull(m.get('inheritedfrom')))) { return false; }
+ if (this.get('node_info').server.version < 90300){
+ return false;
+ }
+ return true;
+ }
+ },{
+ id: 'attnotnull', label:'{{ _('Not Null') }}',
+ cell: 'boolean',type: 'switch', editable: 'is_editable_column',
+ cellHeaderClasses: 'width_percent_10'
+ },{
+ id: 'attstattarget', label:'{{ _('Statistics') }}', min_version: 90200,
+ cell: 'integer', type: 'int', editable: function(m) {
+ if (_.isUndefined(m.isNew) || m.isNew()) { return false; }
+ if (this.get('node_info').server.version < 90200){
+ return false;
+ }
+ return (_.isUndefined(m.get('inheritedid')) || _.isNull(m.get('inheritedid'))
+ || _.isUndefined(m.get('inheritedfrom')) || _.isNull(m.get('inheritedfrom'))) ? true : false
+ }, 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: function(m) {
+ if (!(_.isUndefined(m.isNew)) && !m.isNew()) { return false; }
+ return (_.isUndefined(m.get('inheritedid')) || _.isNull(m.get('inheritedid'))
+ || _.isUndefined(m.get('inheritedfrom')) || _.isNull(m.get('inheritedfrom'))) ? true : false
+ },
+ 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', 'edit'],
+ cellHeaderClasses: 'width_percent_10'
+ }],
+ validate: function() {
+ var err = {},
+ errmsg;
+
+ if (_.isUndefined(this.get('attname')) || String(this.get('attname')).replace(/^\s+|\s+$/g, '') == '') {
+ err['name'] = '{{ _('Column Name can not be empty!') }}';
+ errmsg = errmsg || err['attname'];
+ }
+
+ if (_.isUndefined(this.get('datatype')) || String(this.get('datatype'))
+ .replace(/^\s+|\s+$/g, '') == '') {
+ err['basensp'] = '{{ _('Column Datatype can not be empty!') }}';
+ errmsg = errmsg || err['datatype'];
+ }
+
+ this.errorModel.clear().set(err);
+
+ return errmsg;
+ },
+ is_editable_column: function(m) {
+ return (_.isUndefined(m.get('inheritedid')) || _.isNull(m.get('inheritedid'))
+ || _.isUndefined(m.get('inheritedfrom')) || _.isNull(m.get('inheritedfrom'))) ? true : false
+ },
+ 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', self.model, 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
+ var fnd_columns = columns.where({inheritedid: parseInt(i)});
+ if (fnd_columns && fnd_columns.length <= 0) {
+ 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,
+ conislocal: undefined
+ },
+ 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() {
+ var err = {},
+ errmsg;
+
+ if (_.isUndefined(this.get('conname')) || String(this.get('conname')).replace(/^\s+|\s+$/g, '') == '') {
+ err['conname'] = '{{ _('Constraint Name can not be empty!') }}';
+ errmsg = errmsg || err['conname'];
+ }
+
+ if (_.isUndefined(this.get('consrc')) || String(this.get('consrc'))
+ .replace(/^\s+|\s+$/g, '') == '') {
+ err['consrc'] = '{{ _('Constraint Check can not be empty!') }}';
+ errmsg = errmsg || err['consrc'];
+ }
+
+ this.errorModel.clear().set(err);
+
+ return errmsg;
+ },
+ 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',
+ sqlAlterHelp: 'sql-alterforeigntable.html',
+ sqlCreateHelp: 'sql-createforeigntable.html',
+ label: '{{ _('Foreign Table') }}',
+ collection_type: 'coll-foreign-table',
+ hasSQL: true,
+ hasDepends: true,
+ parent_type: ['schema'],
+ Init: function() {
+ /* Avoid multiple 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});
+
+ // Set Current User
+ var userInfo = pgBrowser.serverInfo[args.node_info.server._id].user;
+ this.set({'owner': userInfo.name}, {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: [],
+ relacl: [],
+ stracl: [],
+ 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', select2: { allowClear: false }
+ },{
+ 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',
+ transform: function(d, self){
+ if (this.field.get('mode') == 'edit') {
+ oid = this.model.get('oid');
+ s = _.findWhere(d, {'id': oid});
+ if (s) {
+ d = _.reject(d, s);
+ }
+ }
+ return d;
+ }
+ },{
+ 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', 'attstattarget', 'collname', 'inheritedfrom'],
+ canDeleteRow: function(m) {
+ return (_.isUndefined(m.get('inheritedid')) || _.isNull(m.get('inheritedid'))
+ || _.isUndefined(m.get('inheritedfrom')) || _.isNull(m.get('inheritedfrom'))) ? true : false
+ }
+ },
+ {
+ 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, canDeleteRow: function(m) {
+ return (m.get('conislocal') == true || _.isUndefined(m.get('conislocal'))) ? true : false
+ }
+ },{
+ id: 'strftoptions', label:'{{ _('Options') }}', cell: 'string',
+ type: 'text', group: 'Definition', mode: ['properties']
+ },{
+ id: 'ftoptions', label:'{{ _('Options') }}', cell: 'string',
+ type: 'collection', group: 'Options', mode: ['edit', 'create'],
+ model: OptionsModel, canAdd: true, canDelete: true, canEdit: false,
+ control: 'unique-col-collection', uniqueCol : ['option']
+ },{
+ id: 'relacl', label: '{{ _('Privileges') }}', cell: 'string',
+ type: 'text', group: '{{ _('Security') }}',
+ mode: ['properties'], min_version: 90200
+ },{
+ id: 'acl', label: '{{ _('Privileges') }}', model: pgAdmin
+ .Browser.Node.PrivilegeRoleModel.extend(
+ {privileges: ['a','r','w','x']}), uniqueCol : ['grantee', 'grantor'],
+ editable: false, type: 'collection', group: '{{ _('Security') }}',
+ mode: ['edit', 'create'],
+ canAdd: true, canDelete: true, control: 'unique-col-collection',
+ min_version: 90200
+ },{
+ 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 cannot be empty.') }}';
+ errmsg = errmsg || err['name'];
+ }
+
+ if (_.isUndefined(this.get('basensp')) || String(this.get('basensp'))
+ .replace(/^\s+|\s+$/g, '') == '') {
+ err['basensp'] = '{{ _('Schema cannot be empty.') }}';
+ errmsg = errmsg || err['basensp'];
+ }
+
+ if (_.isUndefined(this.get('ftsrvname')) || String(this.get('ftsrvname')).replace(/^\s+|\s+$/g, '') == '') {
+ err['ftsrvname'] = '{{ _('Foreign server cannot 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..dcd6027
--- /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,36 @@
+{% 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 %}
+ {{conn|qtIdent(c.attname)}} {{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}} {% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% 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.owner %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}
+ OWNER TO {{ data.owner }};
+{% 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.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..bc731c5
--- /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,14 @@
+SELECT
+ c.oid, c.relname AS name, pg_get_userbyid(relowner) AS owner,
+ ftoptions, nspname as basensp, description
+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)
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=c.oid AND des.classoid='pg_class'::regclass)
+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..d34b2d0
--- /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,109 @@
+{% 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.isArrayType %}[]{% 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 %}
+{% set col_name = o_data['columns'][c.attnum]['attname'] %}
+{% if c.attname != o_data['columns'][c.attnum]['attname'] %}
+{% set col_name = c.attname %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ RENAME COLUMN {{conn|qtIdent(o_data['columns'][c.attnum]['attname'])}} TO {{conn|qtIdent(c.attname)}};
+{% endif %}
+{% if c.attnotnull != o_data['columns'][c.attnum]['attnotnull'] %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}}{% if c.attnotnull %} SET{% else %} DROP{% endif %} NOT NULL;
+{% endif %}
+{% if c.datatype != o_data['columns'][c.attnum]['datatype'] or c.typlen != o_data['columns'][c.attnum]['typlen'] or
+c.precision != o_data['columns'][c.attnum]['precision'] %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}} TYPE {{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %};
+{% endif %}
+{% if c.typdefault != o_data['columns'][c.attnum]['typdefault'] %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}}{% if c.typdefault %} SET DEFAULT {{c.typdefault}}{% else %} DROP DEFAULT{% endif %};
+{% 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}});
+{% 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.security_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.security_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.2_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/acl.sql
new file mode 100644
index 0000000..121a035
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/acl.sql
@@ -0,0 +1,35 @@
+SELECT
+ COALESCE(gt.rolname, 'public') AS grantee,
+ g.rolname AS grantor, array_agg(privilege_type) AS privileges,
+ array_agg(is_grantable) AS grantable
+FROM
+ (SELECT
+ d.grantee, d.grantor, d.is_grantable,
+ CASE d.privilege_type
+ WHEN 'CONNECT' THEN 'c'
+ WHEN 'CREATE' THEN 'C'
+ WHEN 'DELETE' THEN 'd'
+ WHEN 'EXECUTE' THEN 'X'
+ WHEN 'INSERT' THEN 'a'
+ WHEN 'REFERENCES' THEN 'x'
+ WHEN 'SELECT' THEN 'r'
+ WHEN 'TEMPORARY' THEN 'T'
+ WHEN 'TRIGGER' THEN 't'
+ WHEN 'TRUNCATE' THEN 'D'
+ WHEN 'UPDATE' THEN 'w'
+ WHEN 'USAGE' THEN 'U'
+ ELSE 'UNKNOWN'
+ END AS privilege_type
+ FROM
+ (SELECT
+ (d).grantee AS grantee, (d).grantor AS grantor,
+ (d).is_grantable AS is_grantable,
+ (d).privilege_type AS privilege_type
+ FROM
+ (SELECT aclexplode(db.relacl) AS d FROM pg_class db
+ WHERE db.oid = {{foid}}::OID) a
+ ) d
+ ) d
+ LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid)
+ LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid)
+GROUP BY g.rolname, gt.rolname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/create.sql
new file mode 100644
index 0000000..497b124
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/create.sql
@@ -0,0 +1,44 @@
+{% import 'macros/schemas/security.macros' as SECLABLE %}
+{% import 'macros/schemas/privilege.macros' as PRIVILEGE %}
+{% if data %}
+CREATE FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}(
+{% if data.columns %}
+{% for c in data.columns %}
+ {{conn|qtIdent(c.attname)}} {{conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% 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.owner %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}
+ OWNER TO {{ data.owner }};
+{% endif -%}
+{% if data.description %}
+
+COMMENT ON FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}
+ IS '{{ data.description }}';
+{% endif -%}
+{% if data.acl %}
+
+{% for priv in data.acl %}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.basensp) }}
+{% endfor -%}
+{% 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.2_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_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.2_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.2_plus/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_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.2_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.2_plus/get_columns.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/get_columns.sql
new file mode 100644
index 0000000..4c03ad7
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/get_columns.sql
@@ -0,0 +1,34 @@
+SELECT
+ attname, attndims, atttypmod, format_type(t.oid,NULL) AS datatype,
+ attnotnull, attstattarget, 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.2_plus/get_constraints.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/get_constraints.sql
new file mode 100644
index 0000000..43a8298
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/get_constraints.sql
@@ -0,0 +1,7 @@
+SELECT
+ conname, contype, consrc, conislocal
+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.2_plus/get_foreign_servers.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_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.2_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.2_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_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.2_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.2_plus/node.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/node.sql
new file mode 100644
index 0000000..bc731c5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/node.sql
@@ -0,0 +1,14 @@
+SELECT
+ c.oid, c.relname AS name, pg_get_userbyid(relowner) AS owner,
+ ftoptions, nspname as basensp, description
+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)
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=c.oid AND des.classoid='pg_class'::regclass)
+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.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/properties.sql
new file mode 100644
index 0000000..c557c87
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/properties.sql
@@ -0,0 +1,27 @@
+SELECT
+ c.oid, c.relname AS name, c.relacl, 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.2_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/update.sql
new file mode 100644
index 0000000..44fb433
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/update.sql
@@ -0,0 +1,148 @@
+{% import 'macros/schemas/security.macros' as SECLABLE %}
+{% import 'macros/schemas/privilege.macros' as PRIVILEGE %}
+{% 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.isArrayType %}[]{% 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 %}
+{% set col_name = o_data['columns'][c.attnum]['attname'] %}
+{% if c.attname != o_data['columns'][c.attnum]['attname'] %}
+{% set col_name = c.attname %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ RENAME COLUMN {{conn|qtIdent(o_data['columns'][c.attnum]['attname'])}} TO {{conn|qtIdent(c.attname)}};
+{% endif %}
+{% if c.attnotnull != o_data['columns'][c.attnum]['attnotnull'] %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}}{% if c.attnotnull %} SET{% else %} DROP{% endif %} NOT NULL;
+{% endif %}
+{% if c.datatype != o_data['columns'][c.attnum]['datatype'] or c.typlen != o_data['columns'][c.attnum]['typlen'] or
+c.precision != o_data['columns'][c.attnum]['precision'] %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}} TYPE {{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %};
+{% endif %}
+{% if c.typdefault != o_data['columns'][c.attnum]['typdefault'] %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}}{% if c.typdefault %} SET DEFAULT {{c.typdefault}}{% else %} DROP DEFAULT{% endif %};
+{% endif %}
+{% if c.attstattarget != o_data['columns'][c.attnum]['attstattarget'] %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}} SET STATISTICS {% if c.attstattarget %}{{c.attstattarget}}{% else %}-1{% 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)}} {% 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}});
+{% 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 -%}
+{% if data.acl %}
+{% if 'deleted' in data.acl %}
+{% for priv in data.acl.deleted %}
+
+{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, name, o_data.basensp) }}
+{% endfor %}
+{% endif -%}
+{% if 'changed' in data.acl %}
+{% for priv in data.acl.changed %}
+
+{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, name, o_data.basensp) }}
+
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, name, priv.without_grant, priv.with_grant, o_data.basensp) }}
+{% endfor %}
+{% endif -%}
+{% if 'added' in data.acl %}
+{% for priv in data.acl.added %}
+
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, name, priv.without_grant, priv.with_grant, o_data.basensp) }}
+{% endfor %}
+{% endif %}
+{% 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.security_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.security_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/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/acl.sql
new file mode 100644
index 0000000..121a035
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/acl.sql
@@ -0,0 +1,35 @@
+SELECT
+ COALESCE(gt.rolname, 'public') AS grantee,
+ g.rolname AS grantor, array_agg(privilege_type) AS privileges,
+ array_agg(is_grantable) AS grantable
+FROM
+ (SELECT
+ d.grantee, d.grantor, d.is_grantable,
+ CASE d.privilege_type
+ WHEN 'CONNECT' THEN 'c'
+ WHEN 'CREATE' THEN 'C'
+ WHEN 'DELETE' THEN 'd'
+ WHEN 'EXECUTE' THEN 'X'
+ WHEN 'INSERT' THEN 'a'
+ WHEN 'REFERENCES' THEN 'x'
+ WHEN 'SELECT' THEN 'r'
+ WHEN 'TEMPORARY' THEN 'T'
+ WHEN 'TRIGGER' THEN 't'
+ WHEN 'TRUNCATE' THEN 'D'
+ WHEN 'UPDATE' THEN 'w'
+ WHEN 'USAGE' THEN 'U'
+ ELSE 'UNKNOWN'
+ END AS privilege_type
+ FROM
+ (SELECT
+ (d).grantee AS grantee, (d).grantor AS grantor,
+ (d).is_grantable AS is_grantable,
+ (d).privilege_type AS privilege_type
+ FROM
+ (SELECT aclexplode(db.relacl) AS d FROM pg_class db
+ WHERE db.oid = {{foid}}::OID) a
+ ) d
+ ) d
+ LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid)
+ LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid)
+GROUP BY g.rolname, gt.rolname;
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..ef57008
--- /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,61 @@
+{% import 'macros/schemas/security.macros' as SECLABLE %}
+{% import 'macros/schemas/privilege.macros' as PRIVILEGE %}
+{% 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.isArrayType %}[]{% 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.owner %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}
+ OWNER TO {{ data.owner }};
+{% 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.acl %}
+
+{% for priv in data.acl %}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.basensp) }}
+{% endfor -%}
+{% 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.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..18f70e8
--- /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,53 @@
+WITH INH_TABLES AS
+ (SELECT
+ distinct on (at.attname) attname, ph.inhparent AS inheritedid, ph.inhseqno,
+ 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
+ GROUP BY at.attname, ph.inhparent, ph.inhseqno, inheritedfrom
+ ORDER BY at.attname, ph.inhparent, ph.inhseqno, inheritedfrom
+ )
+SELECT INH.inheritedfrom, INH.inheritedid,
+ att.attname, att.attndims, att.atttypmod, format_type(t.oid,NULL) AS datatype,
+ att.attnotnull, att.attstattarget, 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,
+ (
+ att.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 COUNT(1) from pg_type t2 WHERE t2.typname=t.typname) > 1 AS isdup
+FROM
+ pg_attribute att
+LEFT JOIN
+ INH_TABLES as INH ON att.attname = INH.attname
+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.attname;
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..f8333dc
--- /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, conislocal
+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..83445a2
--- /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,22 @@
+{% 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')
+{% if foid %}
+ AND c.oid <> {{foid}}::oid
+{% endif %}
+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..bc731c5
--- /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,14 @@
+SELECT
+ c.oid, c.relname AS name, pg_get_userbyid(relowner) AS owner,
+ ftoptions, nspname as basensp, description
+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)
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=c.oid AND des.classoid='pg_class'::regclass)
+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..3976a07
--- /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, c.relacl, 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..1aed700
--- /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,182 @@
+{% import 'macros/schemas/security.macros' as SECLABLE %}
+{% import 'macros/schemas/privilege.macros' as PRIVILEGE %}
+{% 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 %}
+{% if (not c.inheritedfrom or c.inheritedfrom =='' or c.inheritedfrom == None or c.inheritedfrom == 'None' ) %}
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ DROP COLUMN {{conn|qtIdent(c.attname)}};
+{% endif %}
+{% 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.isArrayType %}[]{% 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 %}
+{% set col_name = o_data['columns'][c.attnum]['attname'] %}
+{% if c.attname != o_data['columns'][c.attnum]['attname'] %}
+{% set col_name = c.attname %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ RENAME COLUMN {{conn|qtIdent(o_data['columns'][c.attnum]['attname'])}} TO {{conn|qtIdent(c.attname)}};
+{% endif %}
+{% if c.attnotnull != o_data['columns'][c.attnum]['attnotnull'] %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}}{% if c.attnotnull %} SET{% else %} DROP{% endif %} NOT NULL;
+{% endif %}
+{% if c.datatype != o_data['columns'][c.attnum]['datatype'] or c.typlen != o_data['columns'][c.attnum]['typlen'] or
+c.precision != o_data['columns'][c.attnum]['precision'] %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}} TYPE {{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %};
+{% endif %}
+{% if c.typdefault != o_data['columns'][c.attnum]['typdefault'] %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}}{% if c.typdefault %} SET DEFAULT {{c.typdefault}}{% else %} DROP DEFAULT{% endif %};
+{% endif %}
+{% if c.attstattarget != o_data['columns'][c.attnum]['attstattarget'] %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}} SET STATISTICS {% if c.attstattarget %}{{c.attstattarget}}{% else %}-1{% endif %};
+{% 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}});
+{% 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.security_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.security_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.acl %}
+{% if 'deleted' in data.acl %}
+{% for priv in data.acl.deleted %}
+
+{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, name, o_data.basensp) }}
+{% endfor %}
+{% endif -%}
+{% if 'changed' in data.acl %}
+{% for priv in data.acl.changed %}
+
+{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, name, o_data.basensp) }}
+
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, name, priv.without_grant, priv.with_grant, o_data.basensp) }}
+{% endfor %}
+{% endif -%}
+{% if 'added' in data.acl %}
+{% for priv in data.acl.added %}
+
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, name, priv.without_grant, priv.with_grant, o_data.basensp) }}
+{% endfor %}
+{% endif %}
+{% endif -%}
+{% if data.basensp %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ SET SCHEMA {{ conn|qtIdent(data.basensp) }};
+{% endif %}
+{% endif %}
view thread (21+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: [pgAdmin4] [Patch]: Foreign Table Module
In-Reply-To: <CAFOhELceEWd_o0t8GgBTToc3R_6Or2-eEMzQD=XTbQ37_TRPDQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox