public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dave Page <[email protected]>
To: Surinder Kumar <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: [pgAdmin4] PATCH: View and Role Node
Date: Tue, 1 Mar 2016 17:12:45 +0000
Message-ID: <CA+OCxowX+RDV5mocfb8T=JKD-FQ6SbjKnOsqG28MYdZnh4V84g@mail.gmail.com> (raw)
In-Reply-To: <CAM5-9D8Dz0n2CHQvFsokgkifb146jAJb42P7ExaUL5tqbWXp2A@mail.gmail.com>
References: <CAM5-9D_qRCubJ8CE+7XsNyHKDF=JixqiOCZcZQK+Gr1ATM-OAg@mail.gmail.com>
<CAM5-9D_08pXJ_G4QpN89TuZR3bDShVk4sFG5Ee8rJ8o0JW1b9w@mail.gmail.com>
<CA+OCxowWwR70-w=674UQXfZkAiCnmzdnqj7mCSwGFfT4DymWzw@mail.gmail.com>
<CAM5-9D8Dz0n2CHQvFsokgkifb146jAJb42P7ExaUL5tqbWXp2A@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
Sorry - I believe this is it (I had to rescue it from the trash).
On Tue, Mar 1, 2016 at 5:16 AM, Surinder Kumar
<[email protected]> wrote:
> Dave you forgot to attach patch. Can you please attach it ?
>
> On Thu, Feb 25, 2016 at 4:33 PM, Dave Page <[email protected]> wrote:
>>
>> Hi
>>
>> On Wed, Feb 24, 2016 at 1:31 PM, Surinder Kumar
>> <[email protected]> wrote:
>>>
>>> Hi,
>>>
>>> Please find the updated patch with following changes:
>>>
>>> 1. Integrated dependents and dependency tabs with view and rule node.
>>> 2. Added "Create Rule" option for Table node.
>>> 3. Added node_inode property for rule node as it has no child node.
>>> 4. Improved javascript code commenting and added pydoc in python files.
>>> 5. Name column under dependents tab showing wrong name for rule node
>>> due to typo mistake, It is fixed.
>>>
>>> Please review it and Let me know for any comments.
>>
>>
>> Please find an updated patch attached, in which I tweaked some comments
>> and fixed a few minor issues as I reviewed the code. The following issues
>> need to be addressed please, then I'll take another look:
>>
>> - Don't show the definition SQL in the main properties tab.
>>
>> - Check Options is missing from the properties tab.
>>
>> - Views were made function in PostgreSQL v6.4! Why only load the module
>> for 9.1+?
>>
>> - The TRUNCATE ACL flag is not recognised, leading to ACLs being shown as:
>> arwdUNKNOWNxt
>>
>> - 4 character indents are not applied consistently to SQL templates
>>
>> - Why do servers pre-9.3 have VACUUM options in the SQL templates?
>>
>> - There should be no special casing of Slony schemas (backend_support.sql)
>>
>> - Don't include \'s on line ends of multiline comments or docstrings.
>>
>> - I can't create a Materialised View
>>
>> On the dialogs:
>>
>> - The "Definition" input box should have a border.
>>
>> - The "Definition" input box fill the vertical space assigned to it,
>> such that the border on the left runs top to bottom (see the main SQL tab).
>>
>> - The "Definition" input box should be displayed below the label at all
>> times (unlike other controls), to maximise available space
>>
>> - The SQL tab view is broken for views (tested on PG 9.4), likely due to
>> the TRUNCATE ACL issue:
>>
>> 2016-02-25 10:59:00,118: INFO werkzeug: 127.0.0.1 - - [25/Feb/2016
>> 10:59:00] "GET /browser/view/sql/1/1/24587/27424/27555 HTTP/1.1" 500 -
>> Traceback (most recent call last):
>> File
>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>> line 1836, in __call__
>> return self.wsgi_app(environ, start_response)
>> File
>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>> line 1820, in wsgi_app
>> response = self.make_response(self.handle_exception(e))
>> File
>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>> line 1403, in handle_exception
>> reraise(exc_type, exc_value, tb)
>> File
>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>> line 1817, in wsgi_app
>> response = self.full_dispatch_request()
>> File
>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>> line 1477, in full_dispatch_request
>> rv = self.handle_user_exception(e)
>> File
>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>> line 1381, in handle_user_exception
>> reraise(exc_type, exc_value, tb)
>> File
>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>> line 1475, in full_dispatch_request
>> rv = self.dispatch_request()
>> File
>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>> line 1461, in dispatch_request
>> return self.view_functions[rule.endpoint](**req.view_args)
>> File
>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/views.py",
>> line 84, in view
>> return self.dispatch_request(*args, **kwargs)
>> File "/Users/dpage/git/pgadmin4/web/pgadmin/browser/utils.py", line 248,
>> in dispatch_request
>> return method(*args, **kwargs)
>> File
>> "/Users/dpage/git/pgadmin4/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py",
>> line 242, in wrap
>> return f(*args, **kwargs)
>> File
>> "/Users/dpage/git/pgadmin4/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py",
>> line 646, in sql
>> result['datacl'] = self.parse_privileges(result['datacl'])
>> File
>> "/Users/dpage/git/pgadmin4/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py",
>> line 540, in parse_privileges
>> db_privileges[privilege['privilege_type']])
>> KeyError: 'UNKNOWN'
>>
>>
>> Thanks!
>>
>> --
>> Dave Page
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>
>
--
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:
[application/octet-stream] view_and_role_node_v2-dave.patch (164.8K, 2-view_and_role_node_v2-dave.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py
new file mode 100644
index 0000000..aa2605c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py
@@ -0,0 +1,723 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+"""Implements View Node"""
+
+import json
+from flask import render_template, make_response, request, jsonify
+from flask.ext.babel import gettext
+from pgadmin.utils.ajax import make_json_response, \
+ make_response as ajax_response, internal_server_error
+from pgadmin.browser.utils import PGChildNodeView
+from pgadmin.browser.collection import CollectionNodeModule
+import pgadmin.browser.server_groups.servers.databases.schemas as schemas
+from pgadmin.utils.ajax import precondition_required
+from pgadmin.utils.driver import get_driver
+from config import PG_DEFAULT_DRIVER
+from functools import wraps
+from .parse_definition import parse_definition
+
+
+try:
+ unicode = unicode
+except NameError:
+ # 'unicode' is undefined, must be Python 3
+ str = str
+ unicode = str
+ bytes = bytes
+ basestring = (str, bytes)
+else:
+ # 'unicode' exists, must be Python 2
+ str = str
+ unicode = unicode
+ bytes = str
+ basestring = basestring
+
+
+class ViewModule(CollectionNodeModule):
+ """
+ class ViewModule(Object):
+
+ A view collection Node which inherits CollectionNodeModule
+ class and define methods:
+ get_nodes - To generate collection node.
+ script_load - tells when to load js file.
+ csssnppets - add css to page
+ """
+ NODE_TYPE = 'view'
+ COLLECTION_LABEL = gettext("Views")
+
+ def __init__(self, *args, **kwargs):
+ self.min_ver = None
+ self.max_ver = None
+
+ super(ViewModule, self).__init__(*args, **kwargs)
+
+ # Before loading this module we need to make sure that scid is catalog \
+ # and schema object and catalog name is 'sys', 'dbo',
+ # 'information_schema' then only we load this module
+ def BackendSupported(self, manager, **kwargs):
+ """
+ This function will validate schema name & scid against catalogs \
+ then allow us to decide whether we want to load this module or \
+ not for that schema
+ """
+ if super(ViewModule, self).BackendSupported(manager, **kwargs):
+ conn = manager.connection()
+ # If DB is not connected then return error to browser
+ if not conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+ ver = manager.version
+ server_type = manager.server_type
+ # we will set template path for sql scripts
+ if server_type == 'ppas' and ver >= 90100:
+ template_path = 'templates/views/sql/edb_9.1_plus'
+ elif ver >= 90100:
+ template_path = 'templates/views/sql/9.4_plus'
+ else:
+ # Note: View is not supported below postgres version 9.1
+ # Hence we will not load this module
+ return False
+
+ SQL = render_template("/".join([
+ template_path, 'backend_support.sql']), scid=kwargs['scid'])
+ status, res = conn.execute_scalar(SQL)
+ # check if any errors
+ if not status:
+ return internal_server_error(errormsg=res)
+ # Check scid is catalog and from 'sys', 'dbo', \
+ # 'information_schema',then False (Do not load this module), \
+ # othewise True
+ if res is True:
+ return False
+ else:
+ return True
+
+ def get_nodes(self, gid, sid, did, scid):
+ """
+ Generate the collection node
+ """
+ yield self.generate_browser_collection_node(scid)
+
+ @property
+ def script_load(self):
+ """
+ Load the module script for views, when any database node is
+ initialized, as views are under catalogs and schemas .
+ """
+ return schemas.SchemaModule.NODE_TYPE
+
+ @property
+ def csssnippets(self):
+ """
+ Returns a snippet of css to include in the page
+ """
+ snippets = [
+ render_template(
+ "browser/css/collection.css",
+ node_type=self.node_type,
+ _=gettext
+ ),
+ render_template(
+ "templates/views/css/view.css",
+ node_type=self.node_type,
+ _=gettext
+ )
+ ]
+
+ for submodule in self.submodules:
+ snippets.extend(submodule.csssnippets)
+
+ return snippets
+
+# Set template_folder to views,as view also uses rules sql templates.
+blueprint = ViewModule(__name__, template_folder="../views")
+
+
+class ViewNode(PGChildNodeView):
+ """
+ This is a class for view node which inherits the
+ properties and methods from PGChildNodeView class and define
+ various methods to list, create, update and delete view.
+
+ Variables:
+ ---------
+ * node_type - tells which type of node it is
+ * parent_ids - id with its type and name of parent nodes
+ * ids - id with type and name of extension module being used.
+ * operations - function routes mappings defined.
+ """
+ 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': 'vid'}
+ ]
+
+ operations = dict({
+ 'obj': [
+ {'get': 'properties', 'delete': 'delete', 'put': 'update'},
+ {'get': 'list', 'post': 'create'}
+ ],
+ 'children': [{
+ 'get': 'children'
+ }],
+ 'delete': [{'delete': 'delete'}],
+ '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'}],
+ 'configs': [{'get': 'configs'}]
+ })
+
+ def module_js(self):
+ """
+ This property defines whether Javascript exists for this node.
+ """
+ return make_response(
+ render_template(
+ "templates/views/js/views.js",
+ _=gettext
+ ),
+ 200, {'Content-Type': 'application/x-javascript'}
+ )
+
+ def check_precondition(f):
+ """
+ This function will behave as a decorator which will check the
+ database connection before running the view. It will also attach
+ manager, conn & template_path properties to self
+ """
+ @wraps(f)
+ def wrap(*args, **kwargs):
+
+ # Here args[0] will hold self & kwargs will hold gid,sid,did
+ self = args[0]
+ self.manager = get_driver(
+ PG_DEFAULT_DRIVER).connection_manager(kwargs['sid'])
+ self.conn = self.manager.connection(did=kwargs['did'])
+
+ # If DB not connected then return error to browser
+ if not self.conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+
+ self.datlastsysoid = self.manager.db_info[
+ kwargs['did']]['datlastsysoid']
+ ver = self.manager.version
+
+ # we will set template path for sql scripts
+ if ver >= 90400:
+ self.template_path = 'templates/views/sql/9.4_plus'
+ elif ver >= 90300:
+ self.template_path = 'templates/views/sql/9.3_plus'
+ elif ver >= 90200:
+ self.template_path = 'templates/views/sql/9.2_plus'
+ elif ver >= 90100:
+ self.template_path = 'templates/views/sql/9.1_plus'
+ else:
+ self.template_path = 'templates/views/sql/pre_9.1'
+
+ return f(*args, **kwargs)
+
+ return wrap
+
+ @check_precondition
+ def list(self, gid, sid, did, scid):
+ """
+ Fetches all views properties and render into properties tab
+ """
+ SQL = render_template("/".join(
+ [self.template_path, 'properties.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):
+ """
+ Lists all views under the Views Collection node
+ """
+ res = []
+ SQL = render_template("/".join(
+ [self.template_path, 'properties.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-view"
+ ))
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ def parse_views_privileges(self, db_privileges):
+ """
+ This function forms a separate list of grantable
+ and non grantable privileges.
+ """
+ acl = {'grantor': db_privileges['grantor'],
+ 'grantee': db_privileges['grantee'],
+ 'privileges': []
+ }
+
+ privileges = []
+ for idx, priv in enumerate(db_privileges['privileges']):
+ if db_privileges['grantable'][idx]:
+ privileges.append({"privilege_type": priv,
+ "privilege": True,
+ "with_grant": True
+ })
+ else:
+ privileges.append({"privilege_type": priv,
+ "privilege": True,
+ "with_grant": False
+ })
+
+ acl['privileges'] = privileges
+
+ return acl
+
+ @check_precondition
+ def properties(self, gid, sid, did, scid, vid):
+ """
+ Fetches the properties of an individual view
+ and render in the properties tab
+ """
+ SQL = render_template("/".join(
+ [self.template_path, 'properties.sql']
+ ), vid=vid, datlastsysoid=self.datlastsysoid)
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+ SQL = render_template("/".join(
+ [self.template_path, 'acl.sql']), vid=vid)
+ status, dataclres = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in dataclres['rows']:
+ priv = self.parse_views_privileges(row)
+ if row['deftype'] in res['rows'][0]:
+ res['rows'][0][row['deftype']].append(priv)
+ else:
+ res['rows'][0][row['deftype']] = [priv]
+
+ result = res['rows'][0]
+
+ # sending result to formtter
+ frmtd_reslt = self.formatter(result)
+
+ # merging formated result with main result again
+ result.update(frmtd_reslt)
+ return ajax_response(
+ response=result,
+ status=200
+ )
+
+ @staticmethod
+ def formatter(result):
+ """ This function formats output for security label & variables"""
+ frmtd_result = dict()
+ sec_lbls = []
+ if 'seclabels' in result and result['seclabels'] is not None:
+ for sec in result['seclabels']:
+ import re
+ sec = re.search(r'([^=]+)=(.*$)', sec)
+ sec_lbls.append({
+ 'provider': sec.group(1),
+ 'security_label': sec.group(2)
+ })
+
+ frmtd_result.update({"seclabels": sec_lbls})
+ return frmtd_result
+
+ @check_precondition
+ def create(self, gid, sid, did, scid):
+ """
+ This function will create a new view object
+ """
+ required_args = [
+ 'name',
+ 'definition'
+ ]
+
+ data = request.form if request.form else \
+ json.loads(request.data.decode())
+ for arg in required_args:
+ if arg not in data:
+ return make_json_response(
+ status=410,
+ success=0,
+ errormsg=gettext(
+ "Couldn't find the required parameter (%s)." % arg
+ )
+ )
+ try:
+ SQL = render_template("/".join(
+ [self.template_path, 'create.sql']), data=data)
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+ SQL = render_template("/".join(
+ [self.template_path, 'view_id.sql']), data=data)
+ status, view_id = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=view_id)
+ return jsonify(
+ node=self.blueprint.generate_browser_node(
+ view_id,
+ scid,
+ data['name'],
+ icon="icon-view"
+ )
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def update(self, gid, sid, did, scid, vid):
+ """
+ This function will update a view object
+ """
+ data = request.form if request.form else \
+ json.loads(request.data.decode())
+ SQL = self.getSQL(gid, sid, data, vid)
+ try:
+ if SQL and isinstance(SQL, basestring) and \
+ SQL.strip('\n') and SQL.strip(' '):
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info=gettext("View updated"),
+ data={
+ 'id': vid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+ else:
+ return make_json_response(
+ success=1,
+ info=gettext("Nothing to update"),
+ data={
+ 'id': vid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def delete(self, gid, sid, did, scid, vid):
+ """
+ This function will drop a view object
+ """
+
+ # Below will decide if it's simple drop or drop with cascade call
+ cascade = True if self.cmd == 'delete' else False
+
+ try:
+
+ # Get name for view from did
+ SQL = render_template("/".join(
+ [self.template_path, 'delete.sql']), vid=vid)
+ status, res_data = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res_data)
+
+ # drop view
+ SQL = render_template("/".join(
+ [self.template_path, 'delete.sql']),
+ nspname=res_data['rows'][0]['nspname'],
+ name=res_data['rows'][0]['name'], 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("View dropped"),
+ data={
+ 'id': vid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def msql(self, gid, sid, did, scid, vid=None):
+ """
+ This function returns modified SQL
+ """
+ data = {}
+ for k, v in request.args.items():
+ try:
+ data[k] = json.loads(v)
+ except ValueError:
+ data[k] = v
+
+ SQL = self.getSQL(gid, sid, data, vid)
+ if SQL and isinstance(SQL, basestring) and \
+ SQL.strip('\n') and SQL.strip(' '):
+ return make_json_response(
+ data=SQL,
+ status=200
+ )
+ else:
+ return make_json_response(
+ data=gettext("-- Nothing changed"),
+ status=200
+ )
+
+ @staticmethod
+ def parse_privileges(str_privileges, object_type='VIEW'):
+ """Parse Privileges."""
+ db_privileges = {
+ 'a': 'INSERT',
+ 'r': 'SELECT',
+ 'w': 'UPDATE',
+ 'd': 'DELETE',
+ 'x': 'REFERENCE',
+ 't': 'TRIGGER'
+ }
+ privileges = []
+ for priv in str_privileges:
+ priv_with_grant = []
+ priv_without_grant = []
+ for privilege in priv['privileges']:
+ if privilege['with_grant']:
+ priv_with_grant.append(
+ db_privileges[privilege['privilege_type']])
+ elif privilege['privilege']:
+ priv_without_grant.append(
+ db_privileges[privilege['privilege_type']])
+ priv_with_grant = ", ".join(priv_with_grant)
+ priv_without_grant = ", ".join(priv_without_grant)
+
+ # Server Level validation
+ if 'grantee' in priv:
+ privileges.append(
+ {
+ 'grantee': priv['grantee'] if 'grantee' in priv else '',
+ 'with_grant': priv_with_grant,
+ 'without_grant': priv_without_grant
+ }
+ )
+ else:
+ return ''
+ return privileges
+
+ def getSQL(self, gid, sid, data, vid=None):
+ """
+ This function will genrate sql from model data
+ """
+ try:
+ if vid is not None:
+ SQL = render_template("/".join(
+ [self.template_path, 'properties.sql']),
+ vid=vid,
+ datlastsysoid=self.datlastsysoid
+ )
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+ old_data = res['rows'][0]
+
+ if 'name' not in data:
+ data['name'] = res['rows'][0]['name']
+ if 'schema' not in data:
+ data['schema'] = res['rows'][0]['schema']
+
+ key = 'datacl'
+ if key in data and data[key] is not None:
+ if 'added' in data[key]:
+ data[key]['added'] = self.parse_privileges(
+ data[key]['added'])
+ if 'changed' in data[key]:
+ data[key]['changed'] = self.parse_privileges(
+ data[key]['changed'])
+ if 'deleted' in data[key]:
+ data[key]['deleted'] = self.parse_privileges(
+ data[key]['deleted'])
+ SQL = render_template("/".join(
+ [self.template_path, 'update.sql']), data=data,
+ o_data=old_data, conn=self.conn)
+ else:
+ required_args = [
+ 'name',
+ 'schema',
+ 'definition'
+ ]
+ for arg in required_args:
+ if arg not in data:
+ return " -- definition incomplete"
+
+ if 'datacl' in data and data['datacl'] is not None:
+ data['datacl'] = self.parse_privileges(data['datacl'])
+ SQL = render_template("/".join(
+ [self.template_path, 'create.sql']), data=data)
+ if data['definition']:
+ SQL += "\n"
+ SQL += render_template("/".join(
+ [self.template_path, 'grant.sql']), data=data)
+ return SQL
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def sql(self, gid, sid, did, scid, vid):
+ """
+ This function will generate sql to render into the sql panel
+ """
+ SQL_data = ''
+ SQL = render_template("/".join(
+ [self.template_path, 'properties.sql']),
+ vid=vid,
+ datlastsysoid=self.datlastsysoid
+ )
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ # Fetch all privileges for view
+ SQL = render_template("/".join(
+ [self.template_path, 'acl.sql']), vid=vid)
+ status, dataclres = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in dataclres['rows']:
+ priv = self.parse_views_privileges(row)
+ if row['deftype'] in res['rows'][0]:
+ res['rows'][0]['datacl'].append(priv)
+ else:
+ res['rows'][0]['datacl'] = [priv]
+
+ result = res['rows'][0]
+ if 'datacl' in result:
+ result['datacl'] = self.parse_privileges(result['datacl'])
+ SQL = render_template("/".join(
+ [self.template_path, 'create.sql']),
+ data=result,
+ conn=self.conn,
+ display_comments=True
+ )
+ SQL += "\n"
+ SQL += render_template("/".join(
+ [self.template_path, 'grant.sql']), data=result)
+
+ SQL_data += SQL
+
+ """Get all non system rules of view node, generate their sql and render
+ into sql tab"""
+ self.temp_path = 'rules/templates/rules/sql'
+ SQL = render_template("/".join(
+ [self.temp_path, 'properties.sql']), vid=vid)
+ status, data_prop = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for rule in data_prop['rows']:
+
+ # Generate SQL only for non system rule
+ if rule['name'] != '_RETURN':
+ res = []
+ SQL = render_template("/".join(
+ [self.temp_path, 'properties.sql']),
+ rid=rule['oid']
+ )
+ status, res = self.conn.execute_dict(SQL)
+ res_data = parse_definition(res)
+ SQL = render_template("/".join(
+ [self.temp_path, 'create.sql']),
+ data=res_data, display_comments=True)
+ SQL_data += '\n'
+ SQL_data += SQL
+
+ return ajax_response(response=SQL_data)
+
+ @check_precondition
+ def dependents(self, gid, sid, did, scid, vid):
+ """
+ This function gets the dependents and returns an ajax response
+ for the view node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ vid: View ID
+ """
+ dependents_result = self.get_dependents(self.conn, vid)
+ return ajax_response(
+ response=dependents_result,
+ status=200
+ )
+
+ @check_precondition
+ def dependencies(self, gid, sid, did, scid, vid):
+ """
+ This function gets the dependencies and returns an ajax response
+ for the view node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ vid: View ID
+ """
+ dependencies_result = self.get_dependencies(self.conn, vid)
+ return ajax_response(
+ response=dependencies_result,
+ status=200
+ )
+
+ViewNode.register_node_view(blueprint)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/parse_definition.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/parse_definition.py
new file mode 100644
index 0000000..2abb0af
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/parse_definition.py
@@ -0,0 +1,58 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+""" Parse definition field data """
+
+from pgadmin.utils.ajax import internal_server_error
+
+
+def parse_definition(res):
+ """
+ This function extracts:
+ - events
+ - do_instead
+ - statements
+ - condition
+ from the defintion row, forms an array with fields and returns it.
+ """
+ res_data = []
+ try:
+ res_data = res['rows'][0]
+ data_def = res_data['definition']
+ import re
+ # Parse data for event
+ e_match = re.search(r"ON\s+(.*)\s+TO", data_def)
+ event_data = e_match.group(1) if e_match is not None else None
+ event = event_data if event_data is not None else ''
+
+ # Parse data for do instead
+ inst_match = re.search(r"\s+(INSTEAD)\s+", data_def)
+ instead_data = inst_match.group(1) if inst_match is not None else None
+ instead = True if instead_data is not None else False
+
+ # Parse data for condition
+ condition_match = re.search(r"(?:WHERE)\s+(.*)\s+(?:DO)", data_def)
+ condition_data = condition_match.group(1) \
+ if condition_match is not None else None
+ condition = condition_data if condition_data is not None else ''
+
+ # Parse data for statements
+ statement_match = re.search(
+ r"(?:DO\s+)(?:INSTEAD\s+)?((.|\n)*)", data_def)
+ statement_data = statement_match.group(1) if statement_match else None
+ statement = statement_data if statement_data is not None else ''
+
+ # set columns parse data
+ res_data['event'] = event.lower().capitalize()
+ res_data['do_instead'] = instead
+ res_data['statements'] = statement
+ res_data['condition'] = condition
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+ return res_data
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/__init__.py
new file mode 100644
index 0000000..d37f471
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/__init__.py
@@ -0,0 +1,522 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+""" Implements Rule Node """
+
+import json
+from flask import render_template, make_response, request, jsonify
+from flask.ext.babel import gettext
+from pgadmin.utils.ajax import make_json_response, \
+ make_response as ajax_response, internal_server_error
+from pgadmin.browser.utils import PGChildNodeView
+from pgadmin.browser.collection import CollectionNodeModule
+import pgadmin.browser.server_groups.servers.databases.schemas.views as views
+from pgadmin.utils.ajax import precondition_required
+from pgadmin.utils.driver import get_driver
+from config import PG_DEFAULT_DRIVER
+from functools import wraps
+from ..parse_definition import parse_definition
+
+
+try:
+ unicode = unicode
+except NameError:
+ # 'unicode' is undefined, must be Python 3
+ str = str
+ unicode = str
+ bytes = bytes
+ basestring = (str, bytes)
+else:
+ # 'unicode' exists, must be Python 2
+ str = str
+ unicode = unicode
+ bytes = str
+ basestring = basestring
+
+
+class RuleModule(CollectionNodeModule):
+ """
+ class RuleModule(Object):
+
+ A rule collection Node which inherits CollectionNodeModule
+ class and define methods:
+ get_nodes - To generate collection node.
+ script_load - tells when to load js file.
+ csssnppets - add css to page
+ """
+ NODE_TYPE = 'rule'
+ COLLECTION_LABEL = gettext("Rules")
+
+ def __init__(self, *args, **kwargs):
+ self.min_ver = None
+ self.max_ver = None
+
+ super(RuleModule, self).__init__(*args, **kwargs)
+
+ # Before loading this module we need to make sure that if scid is a catalog
+ # and catalog name is not 'sys', 'dbo' or 'information_schema' only then
+ # do we load this module
+ def BackendSupported(self, manager, **kwargs):
+ """
+ This function will validate schema name & scid against catalogs
+ then allow us to decide if we want to load this module or
+ not for that schema
+ """
+ if super(RuleModule, self).BackendSupported(manager, **kwargs):
+ conn = manager.connection()
+ # If DB is not connected then return error to browser
+ if not conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+ ver = manager.version
+ server_type = manager.server_type
+ template_path = 'rules/sql'
+ SQL = render_template("/".join(
+ [template_path, 'backend_support.sql']
+ ), scid=kwargs['scid'])
+ status, res = conn.execute_scalar(SQL)
+ # check if any errors
+ if not status:
+ return internal_server_error(errormsg=res)
+ # Check scid is catalog and from 'sys', 'dbo', \
+ # 'information_schema', then False (Do not load this module), \
+ # othewise True
+ if res is True:
+ return False
+ else:
+ return True
+
+ def get_nodes(self, gid, sid, did, scid, vid):
+ """
+ Generate the collection node
+ """
+ yield self.generate_browser_collection_node(vid)
+
+ @property
+ def node_inode(self):
+ """
+ If a node has children return True otherwise False
+ """
+ return False
+
+ @property
+ def script_load(self):
+ """
+ Load the module script for rule, when any of the database nodes are
+ initialized.
+ """
+ return views.ViewModule.NODE_TYPE
+
+ @property
+ def csssnippets(self):
+ """
+ Returns a snippet of css to include in the page
+ """
+ snippets = [
+ render_template(
+ "browser/css/collection.css",
+ node_type=self.node_type,
+ _=gettext
+ ),
+ render_template(
+ "rules/css/rule.css",
+ node_type=self.node_type,
+ _=gettext
+ )
+ ]
+
+ for submodule in self.submodules:
+ snippets.extend(submodule.csssnippets)
+
+ return snippets
+
+
+# Create blueprint of RuleModule.
+blueprint = RuleModule(__name__)
+
+
+class RuleView(PGChildNodeView):
+ """
+ This is a class for rule node which inherits the
+ properties and methods from PGChildNodeView class and define
+ various methods to list, create, update and delete rule.
+
+ Variables:
+ ---------
+ * node_type - tells which type of node it is
+ * parent_ids - id with its type and name of parent nodes
+ * ids - id with type and name of extension module being used.
+ * operations - function routes mappings defined.
+ """
+ node_type = blueprint.node_type
+
+ parent_ids = [
+ {'type': 'int', 'id': 'gid'},
+ {'type': 'int', 'id': 'sid'},
+ {'type': 'int', 'id': 'did'},
+ {'type': 'int', 'id': 'scid'},
+ {'type': 'int', 'id': 'vid'}
+ ]
+ ids = [
+ {'type': 'int', 'id': 'rid'}
+ ]
+
+ operations = dict({
+ 'obj': [
+ {'get': 'properties', 'delete': 'delete', 'put': 'update'},
+ {'get': 'list', 'post': 'create'}
+ ],
+ 'children': [{
+ 'get': 'children'
+ }],
+ 'delete': [{'delete': 'delete'}],
+ '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'}],
+ 'configs': [{'get': 'configs'}]
+ })
+
+ def module_js(self):
+ """
+ This property defines whether Javascript exists for this node.
+ """
+ return make_response(
+ render_template(
+ "rules/js/rules.js",
+ _=gettext
+ ),
+ 200, {'Content-Type': 'application/x-javascript'}
+ )
+
+ def check_precondition(f):
+ """
+ This function will behave as a decorator which will check the
+ database connection before running a view. It will also attach
+ manager, conn & template_path properties to self
+ """
+ @wraps(f)
+ def wrap(*args, **kwargs):
+
+ # Here args[0] will hold self & kwargs will hold gid,sid,did
+ self = args[0]
+ self.manager = get_driver(
+ PG_DEFAULT_DRIVER).connection_manager(kwargs['sid'])
+ self.conn = self.manager.connection(did=kwargs['did'])
+
+ # If DB not connected then return error to browser
+ if not self.conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+
+ self.datlastsysoid = self.manager.db_info[kwargs['did']]['datlastsysoid']
+ ver = self.manager.version
+ self.template_path = 'rules/sql'
+ return f(*args, **kwargs)
+
+ return wrap
+
+ @check_precondition
+ def list(self, gid, sid, did, scid, vid):
+ """
+ Fetch all rule properties and render into properties tab
+ """
+
+ # fetch schema name by schema id
+ SQL = render_template("/".join(
+ [self.template_path, 'properties.sql']), vid=vid)
+ 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, vid):
+ """
+ List all the rules under the Rules Collection node
+ """
+ res = []
+ SQL = render_template("/".join(
+ [self.template_path, 'properties.sql']), vid=vid)
+ 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'],
+ vid,
+ row['name'],
+ icon="icon-rule"
+ ))
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ @check_precondition
+ def properties(self, gid, sid, did, scid, vid, rid):
+ """
+ Fetch the properties of an individual rule and render in properties tab
+
+ """
+ SQL = render_template("/".join(
+ [self.template_path, 'properties.sql']
+ ), rid=rid, datlastsysoid=self.datlastsysoid)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return ajax_response(
+ response=parse_definition(res),
+ status=200
+ )
+
+ @check_precondition
+ def create(self, gid, sid, did, scid, vid):
+ """
+ This function will create a new rule object
+ """
+ required_args = [
+ 'name',
+ ]
+
+ data = request.form if request.form else \
+ json.loads(request.data.decode())
+ for arg in required_args:
+ if arg not in data:
+ return make_json_response(
+ status=410,
+ success=0,
+ errormsg=gettext(
+ "Couldn't find the required parameter (%s)." % arg
+ )
+ )
+ try:
+ SQL = render_template("/".join(
+ [self.template_path, 'create.sql']), data=data)
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ """ Fetch the rule id against rule name to display node in tree browser"""
+ SQL = render_template("/".join(
+ [self.template_path, 'rule_id.sql']), rule_name=data['name'])
+ status, rule_id = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=rule_id)
+ return jsonify(
+ node=self.blueprint.generate_browser_node(
+ rule_id,
+ vid,
+ data['name'],
+ icon="icon-rule"
+ )
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def update(self, gid, sid, did, scid, vid, rid):
+ """
+ This function will update a rule object
+ """
+ data = request.form if request.form else \
+ json.loads(request.data.decode())
+ SQL = self.getSQL(gid, sid, data, vid, rid)
+ try:
+ if SQL and isinstance(SQL, basestring) and \
+ SQL.strip('\n') and SQL.strip(' '):
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info=gettext("Rule updated"),
+ data={
+ 'id': vid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+ else:
+ return make_json_response(
+ success=1,
+ info=gettext("Nothing to update"),
+ data={
+ 'id': vid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def delete(self, gid, sid, did, scid, vid, rid):
+ """
+ This function will drop a rule object
+ """
+ # Below will decide if it's simple drop or drop with cascade call
+ cascade = True if self.cmd == 'delete' else False
+
+ try:
+ # Get name for rule from did
+ SQL = render_template("/".join(
+ [self.template_path, 'delete.sql']), rid=rid)
+ status, res_data = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res_data)
+ # drop rule
+ rset = res_data['rows'][0]
+ SQL = render_template("/".join(
+ [self.template_path, 'delete.sql']),
+ rulename=rset['rulename'],
+ relname=rset['relname'],
+ nspname=rset['nspname'],
+ 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("Rule dropped"),
+ data={
+ 'id': vid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def msql(self, gid, sid, did, scid, vid, rid=None):
+ """
+ This function returns modified SQL
+ """
+ data = request.args
+ SQL = self.getSQL(gid, sid, data, vid, rid)
+ if SQL and isinstance(SQL, basestring) and \
+ SQL.strip('\n') and SQL.strip(' '):
+ return make_json_response(
+ data=SQL,
+ status=200
+ )
+ else:
+ return make_json_response(
+ data=gettext("--modified SQL"),
+ status=200
+ )
+
+ @check_precondition
+ def sql(self, gid, sid, did, scid, vid, rid):
+ """
+ This function will generate sql to render into the sql panel
+ """
+ SQL = render_template("/".join(
+ [self.template_path, 'properties.sql']), rid=rid)
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+ res_data = parse_definition(res)
+ SQL = render_template("/".join(
+ [self.template_path, 'create.sql']),
+ data=res_data, display_comments=True)
+
+ return ajax_response(response=SQL)
+
+ def getSQL(self, gid, sid, data, vid, rid):
+ """
+ This function will genrate sql from model data
+ """
+ try:
+ if rid is not None:
+ SQL = render_template("/".join(
+ [self.template_path, 'properties.sql']), rid=rid)
+ status, res = self.conn.execute_dict(SQL)
+ res_data = []
+ res_data = parse_definition(res)
+ if not status:
+ return internal_server_error(errormsg=res)
+ old_data = res_data
+ SQL = render_template(
+ "/".join([self.template_path, 'update.sql']),
+ data=data, o_data=old_data
+ )
+ else:
+ SQL = render_template("/".join(
+ [self.template_path, 'create.sql']), data=data)
+ return SQL
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def dependents(self, gid, sid, did, scid, vid, rid):
+ """
+ This function gets the dependents and returns an ajax response
+ for the rule node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ vid: View ID
+ rid: Rule ID
+ """
+ dependents_result = self.get_dependents(self.conn, rid)
+ return ajax_response(
+ response=dependents_result,
+ status=200
+ )
+
+ @check_precondition
+ def dependencies(self, gid, sid, did, scid, vid, rid):
+ """
+ This function gets the dependencies and returns sn ajax response
+ for the rule node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ vid: View ID
+ rid: Rule ID
+ """
+ dependencies_result = self.get_dependencies(self.conn, rid)
+ return ajax_response(
+ response=dependencies_result,
+ status=200
+ )
+
+RuleView.register_node_view(blueprint)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/static/img/coll-rule.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/static/img/coll-rule.png
new file mode 100644
index 0000000..cfe6ed2
Binary files /dev/null and b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/static/img/coll-rule.png differ
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/static/img/rule.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/static/img/rule.png
new file mode 100644
index 0000000..8b49780
Binary files /dev/null and b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/static/img/rule.png differ
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/css/rule.css b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/css/rule.css
new file mode 100644
index 0000000..d2b036a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/css/rule.css
@@ -0,0 +1,8 @@
+.icon-rule{
+ background-image: url('{{ url_for('NODE-rule.static', filename='img/rule.png') }}') !important;
+ border-radius: 10px;
+ background-repeat: no-repeat;
+ align-content: center;
+ vertical-align: middle;
+ height: 1.3em;
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/js/rules.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/js/rules.js
new file mode 100644
index 0000000..652c80d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/js/rules.js
@@ -0,0 +1,252 @@
+define(
+ ['jquery', 'underscore', 'underscore.string', 'pgadmin',
+ 'pgadmin.browser', 'codemirror'],
+
+function($, _, S, pgAdmin, pgBrowser, CodeMirror) {
+
+
+ /**
+ Create and add a rule collection into nodes
+ @param {variable} label - Label for Node
+ @param {variable} type - Type of Node
+ @param {variable} columns - List of columns to
+ display under under properties.
+ */
+ if (!pgBrowser.Nodes['coll-rule']) {
+ var rules = pgAdmin.Browser.Nodes['coll-rule'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'rule',
+ label: '{{ _('Rules') }}',
+ type: 'coll-rule',
+ columns: ['name', 'owner', 'comment']
+ });
+ };
+
+
+ /**
+ Create and Add an View Node into nodes
+ @param {variable} parent_type - The list of nodes
+ under which this node to display
+ @param {variable} type - Type of Node
+ @param {variable} hasSQL - To show SQL tab
+ @param {variable} canDrop - Adds drop view option
+ in the context menu
+ @param {variable} canDropCascade - Adds drop Cascade
+ view option in the context menu
+ */
+ if (!pgBrowser.Nodes['rule']) {
+ pgAdmin.Browser.Nodes['rule'] = pgAdmin.Browser.Node.extend({
+ parent_type: ['view'],
+ type: 'rule',
+ label: '{{ _('rule') }}',
+ collection_type: 'coll-rule',
+ hasSQL: true,
+ hasDepends: true,
+ canDrop: pgBrowser.Nodes['schema'].canChildDrop,
+ canDropCascade: pgBrowser.Nodes['schema'].canChildDrop,
+ Init: function() {
+
+ /* Avoid mulitple registration of menus */
+ if (this.initialized)
+ return;
+
+ this.initialized = true;
+
+ /**
+ Add "create rule" menu option into context and object menu
+ for the following nodes:
+ coll-rule, rule and view and table.
+ @property {data} - Allow create rule option on schema node or
+ system rules node.
+ */
+ pgBrowser.add_menus([{
+ name: 'create_rule_on_coll', node: 'coll-rule', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Rule...') }}',
+ icon: 'wcTabIcon icon-rule', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_rule', node: 'view', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Rule...') }}',
+ icon: 'wcTabIcon icon-rule', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_rule', node: 'rule', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Rule...') }}',
+ icon: 'wcTabIcon icon-rule', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_rule', node: 'table', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Rule...') }}',
+ icon: 'wcTabIcon icon-rule', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ }
+ ]);
+ },
+
+ /**
+ Define model for the rule node and specify the node
+ properties of the model in schema.
+ */
+ model: pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ },
+ schema: [{
+ id: 'name', label: '{{ _('Name') }}',
+ type: 'text', disabled: function(m){
+ return !m.isNew();
+ }
+ },
+ {
+ id: 'oid', label:'{{ _('OID') }}',
+ type: 'text', disabled: true
+ },
+ {
+ id: 'schema', label:'{{ _('') }}',
+ type: 'text', visible: false, disabled: function(m){
+
+ // It is used while generating sql
+ m.set('schema', m.node_info.schema.label);
+ }
+ },
+ {
+ id: 'view', label:'{{ _('') }}',
+ type: 'text', visible: false, disabled: function(m){
+
+ // It is used while generating sql
+ m.set('view', m.node_info.view.label);
+ }
+ },
+ {
+ id: 'comment', label:'{{ _('Comment') }}', cell: 'string', type: 'multiline'
+ },
+ {
+ id: 'event', label:'{{ _('Event') }}', control: 'select2',
+ group: 'Definition', type: 'text',
+ select2: {
+ width: '100%'
+ },
+ options:[
+ {label: 'Select', value: 'Select'},
+ {label: 'Insert', value: 'Insert'},
+ {label: 'Update', value: 'Update'},
+ {label: 'Delete', value: 'Delete'}
+ ]
+ },
+ {
+ id: 'do_instead', label:'{{ _('Do Instead') }}', group: 'Definition',
+ type: 'switch', 'options': {
+ 'onText': 'Yes', 'offText': 'No', 'onColor': 'success',
+ 'offColor': 'default', 'size': 'small'
+ }
+ },
+ {
+ id: 'condition', label:'{{ _('Condition') }}',
+ type: 'text', group: 'Definition',
+ control: 'sql-field'
+ },
+ {
+ id: 'statements', label:'{{ _('Definition') }}',
+ type: 'text', group: 'Statements', control: 'sql-field'
+ },
+ {
+ id: 'system_rule', label:'{{ _('System rule?') }}',
+ type: 'switch', mode: ['properties'], 'options': {
+ 'onText': 'Yes', 'offText': 'No', 'onColor': 'success',
+ 'offColor': 'default', 'size': 'small'
+ }
+ },
+ {
+ id: 'enabled', label:'{{ _('Enabled?') }}',
+ type: 'switch', mode: ['properties'], 'options': {
+ 'onText': 'Yes', 'offText': 'No', 'onColor': 'success',
+ 'offColor': 'default', 'size': 'small'
+ }
+ }
+ ],
+ validate: function() {
+
+ // Triggers specific error messages for fields
+ var err = {},
+ errmsg,
+ field_name = this.get('name');
+ if (_.isUndefined(field_name) || _.isNull(field_name) ||
+ String(field_name).replace(/^\s+|\s+$/g, '') == '')
+ {
+ err['name'] = '{{ _("Please specify name.") }}';
+ errmsg = errmsg || err['name'];
+ this.errorModel.set('name', errmsg);
+ return errmsg;
+ }
+ else
+ {
+ this.errorModel.unset('name');
+ }
+ return null;
+ }
+ }),
+
+ // Show or hide create rule menu option on parent node
+ 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 rule
+ if (_.indexOf(['schema'], d._type) > -1)
+ return true;
+
+ if ('coll-rule' == d._type) {
+
+ //Check if we are not child of rule
+ prev_i = t.hasParent(i) ? t.parent(i) : null;
+ prev_d = prev_i ? t.itemData(prev_i) : null;
+ prev_j = t.hasParent(prev_i) ? t.parent(prev_i) : null;
+ prev_e = prev_j ? t.itemData(prev_j) : null;
+ prev_k = t.hasParent(prev_j) ? t.parent(prev_j) : null;
+ prev_f = prev_k ? t.itemData(prev_k) : null;
+ if( prev_f._type == 'catalog') {
+ return false;
+ } else {
+ return true;
+ }
+ }
+
+ /**
+ Check if it is view and its parent node is schema
+ then allow to create Rule
+ */
+ else if('view' == d._type){
+ prev_i = t.hasParent(i) ? t.parent(i) : null;
+ prev_d = prev_i ? t.itemData(prev_i) : null;
+ prev_j = t.hasParent(prev_i) ? t.parent(prev_i) : null;
+ prev_e = prev_j ? t.itemData(prev_j) : null;
+ if(prev_e._type == 'schema') {
+ return true;
+ }else{
+ return false;
+ }
+ }
+ 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['coll-rule'];
+});
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/backend_support.sql
new file mode 100644
index 0000000..e3f8f4f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/backend_support.sql
@@ -0,0 +1,20 @@
+SELECT
+ CASE WHEN nsp.nspname IN ('sys', 'dbo', 'information_schema') THEN true ELSE false END AS dbSupport
+FROM
+ pg_namespace nsp
+WHERE
+ nsp.oid={{scid}}::int
+AND (
+ (nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+)
+AND
+ nspname NOT LIKE E'pg\\temp\\%'
+AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/create.sql
new file mode 100644
index 0000000..3c7436f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/create.sql
@@ -0,0 +1,26 @@
+{#============Create Rule=============#}
+{% if display_comments %}
+-- Rule: {{ data.name }} ON {{ conn|qtIdent(data.schema, data.name) }}
+
+-- DROP Rule {{ data.name }} ON {{ conn|qtIdent(data.schema, data.name) }};
+
+{% endif %}
+{% if data.name and data.schema and data.view %}
+CREATE OR REPLACE RULE {{ conn|qtIdent(data.name) }} AS
+ ON {{ data.event|upper if data.event else 'SELECT' }} TO {{ conn|qtIdent(data.schema, data.view) }}
+{% if data.condition %}
+ WHERE {{ data.condition }}
+{% endif %}
+ DO{% if data.is_instead == True %}
+{{ ' INSTEAD' }}
+ {% else %}
+{{ ' ' }}
+ {% endif %}
+{% if data.statements %}
+{{ data.statements.rstrip(';') }};
+{% else %}
+NOTHING;
+{% endif %}
+{% if data.comment %}
+COMMENT ON RULE {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.view) }} IS {{ data.comment|qtLiteral }};{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/delete.sql
new file mode 100644
index 0000000..7b8318b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/delete.sql
@@ -0,0 +1,16 @@
+{#======== Drop/Cascade Rule =========#}
+{% if rid %}
+SELECT
+ rw.rulename, cl.relname, nsp.nspname
+FROM
+ pg_rewrite rw
+JOIN
+ pg_class cl ON cl.oid=rw.ev_class
+JOIN
+ pg_namespace nsp ON nsp.oid=cl.relnamespace
+WHERE
+ rw.oid={{ rid }};
+{% endif %}
+{% if rulename and relname and nspname %}
+DROP RULE {{ conn|qtIdent(rulename) }} ON {{ conn|qtIdent(nspname, relname) }} {% if cascade %} CASCADE {% endif %};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/properties.sql
new file mode 100644
index 0000000..eda1025
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/properties.sql
@@ -0,0 +1,27 @@
+{#===================Fetch Rules====================#}
+{% if vid or rid %}
+SELECT
+ rw.oid AS oid, rw.rulename AS name, rw.*, relname AS view,
+ CASE WHEN relkind = 'r' THEN TRUE ELSE FALSE END AS parentistable,
+ nspname AS schema, description AS comment,
+ {#==Checks whether it is system rule or not===#}
+ CASE WHEN rw.rulename = '_RETURN' THEN True ELSE False END AS system_rule,
+ CASE WHEN rw.ev_enabled != 'D' THEN True ELSE False END AS enabled,
+ pg_get_ruledef(rw.oid, true) AS definition
+FROM
+ pg_rewrite rw
+JOIN
+ pg_class cl ON cl.oid=rw.ev_class
+JOIN
+ pg_namespace nsp ON nsp.oid=cl.relnamespace
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=rw.oid AND des.classoid='pg_rewrite'::regclass)
+WHERE
+ {% if vid %}
+ ev_class = {{ vid }}
+ {% elif rid %}
+ rw.oid = {{ rid }}
+ {% endif %}
+ORDER BY
+ rw.rulename
+ {% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/rule_id.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/rule_id.sql
new file mode 100644
index 0000000..3dc6dba
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/rule_id.sql
@@ -0,0 +1,9 @@
+{#========Below will provide rule id for last created rule========#}
+{% if rule_name %}
+SELECT
+ rw.oid
+FROM
+ pg_rewrite rw
+WHERE
+ rw.rulename={{ rule_name|qtLiteral }}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/update.sql
new file mode 100644
index 0000000..b50369a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/rules/templates/rules/sql/update.sql
@@ -0,0 +1,22 @@
+{#============Update Rule=============#}
+{% if data.event or data.do_instead or data.condition %}
+CREATE OR REPLACE RULE {{ conn|qtIdent(o_data.name) }} AS
+ ON {% if data.event and data.event != o_data.event %}{{ data.event|upper }}{% else %}{{ o_data.event|upper }}{% endif %}
+ TO {{ conn|qtIdent(o_data.schema, o_data.view) }}
+{% if data.condition and o_data.condition != data.condition %}
+ WHERE {{ data.condition }}
+{% elif data.condition is not defined and o_data.condition %}
+ WHERE {{ o_data.condition }}
+{% endif %}
+ DO{% if data.do_instead in ['true', True] %}{{ ' INSTEAD' }}{% else %}{{ ' ' }}
+{% endif %}
+{% if data.statements and data.statements != o_data.statements %}
+ {{ data.statements.rstrip(';') }};
+ {% elif data.statements is not defined and o_data.statements %}
+ {{ o_data.statements.rstrip(';') }};
+{% else %}
+ NOTHING;
+{% endif %}
+{% endif %}
+{% if data.comment and data.comment != o_data.comment %}
+COMMENT ON RULE {{ conn|qtIdent(o_data.name) }} ON {{ conn|qtIdent(o_data.schema, o_data.view) }} IS {{ data.comment|qtLiteral }};{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/static/img/coll-view.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/static/img/coll-view.png
new file mode 100644
index 0000000..078d533
Binary files /dev/null and b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/static/img/coll-view.png differ
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/static/img/view.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/static/img/view.png
new file mode 100644
index 0000000..44116db
Binary files /dev/null and b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/static/img/view.png differ
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/css/view.css b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/css/view.css
new file mode 100644
index 0000000..88a2f43
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/css/view.css
@@ -0,0 +1,8 @@
+.icon-view{
+ background-image: url('{{ url_for('NODE-view.static', filename='img/view.png') }}') !important;
+ border-radius: 10px;
+ background-repeat: no-repeat;
+ align-content: center;
+ vertical-align: middle;
+ height: 1.3em;
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/js/views.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/js/views.js
new file mode 100644
index 0000000..d3cea78
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/js/views.js
@@ -0,0 +1,310 @@
+define(
+ ['jquery', 'underscore', 'underscore.string', 'pgadmin',
+ 'pgadmin.browser', 'codemirror', 'pgadmin.browser.server.privilege'],
+
+function($, _, S, pgAdmin, pgBrowser, CodeMirror) {
+
+ // Define Security Model with fields and validation
+ var SecurityModel = pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ provider: undefined,
+ securitylabel: undefined
+ },
+ schema: [{
+ id: 'provider', label: '{{ _('Provider') }}',
+ type: 'text', editable: true
+ },{
+ id: 'security_label', label: '{{ _('Security Label') }}',
+ type: 'text', editable: true
+ }],
+
+ validate: function() {
+ var err = {},
+ errmsg = null,
+ field_name = this.get('provider'),
+ field_security_label = this.get('security_label');
+
+ if (_.isUndefined(field_name) || _.isNull(field_name) ||
+ String(field_name).replace(/^\s+|\s+$/g, '') == '') {
+ err['provider'] = '{{ _("Provider cannot be empty.") }}';
+ errmsg = errmsg || err['provider'];
+ this.errorModel.set('provider', errmsg);
+ return errmsg;
+ }else{
+ this.errorModel.unset('provider');
+ }
+
+ if (_.isUndefined(field_security_label) || _.isNull(field_security_label) ||
+ String(field_security_label).replace(/^\s+|\s+$/g, '') == '') {
+ err['security_label'] = '{{ _("Security Label cannot be empty.") }}';
+ errmsg = errmsg || err['security_label'];
+ this.errorModel.set('security_label', errmsg);
+ return errmsg;
+ }else{
+ this.errorModel.unset('security_label');
+ }
+ return null;
+ }
+ });
+
+
+ /**
+ Create and add a view collection into nodes
+ @param {variable} label - Label for Node
+ @param {variable} type - Type of Node
+ @param {variable} columns - List of columns to
+ display under under properties.
+ */
+ if (!pgBrowser.Nodes['coll-view']) {
+ var views= pgAdmin.Browser.Nodes['coll-view'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'view',
+ label: '{{ _('Views') }}',
+ type: 'coll-view',
+ columns: ['name', 'owner']
+ });
+ };
+
+ /**
+ Create and Add a View Node into nodes
+ @param {variable} parent_type - The list of nodes
+ under which this node to display
+ @param {variable} type - Type of Node
+ @param {variable} hasSQL - To show SQL tab
+ @param {variable} canDrop - Adds drop view option
+ in the context menu
+ @param {variable} canDropCascade - Adds drop Cascade
+ view option in the context menu
+ */
+ if (!pgBrowser.Nodes['view']) {
+ pgAdmin.Browser.Nodes['view'] = pgAdmin.Browser.Node.extend({
+ parent_type: ['schema', 'catalog'],
+ type: 'view',
+ label: '{{ _('View') }}',
+ hasSQL: true,
+ hasDepends: true,
+ collection_type: 'coll-view',
+ canDrop: pgBrowser.Nodes['schema'].canChildDrop,
+ canDropCascade: pgBrowser.Nodes['schema'].canChildDrop,
+ Init: function() {
+
+ // Avoid mulitple registration of menus
+ if (this.initialized)
+ return;
+
+ this.initialized = true;
+
+ /**
+ Add "create view" menu option into context and object menu
+ for the following nodes:
+ coll-view, view and schema.
+ @property {data} - Allow create view option on schema node or
+ system view nodes.
+ */
+ pgBrowser.add_menus([{
+ name: 'create_view_on_coll', node: 'coll-view', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('View...') }}',
+ icon: 'wcTabIcon icon-view', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_view', node: 'view', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('View...') }}',
+ icon: 'wcTabIcon icon-view', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_view', node: 'schema', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('View...') }}',
+ icon: 'wcTabIcon icon-view', data: {action: 'create', check: false},
+ enable: 'canCreate'
+ }
+ ]);
+ },
+
+ /**
+ Define model for the view node and specify the
+ properties of the model in schema.
+ */
+ model: pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ },
+ schema: [{
+ id: 'name', label: '{{ _('Name') }}', cell: 'string',
+ type: 'text'
+ },
+ {
+ id: 'oid', label:'{{ _('Oid') }}', cell: 'string',
+ type: 'text', disabled: true
+ },
+ {
+ id: 'owner', label:'{{ _('Owner') }}', cell: 'string', control: 'node-list-by-name',
+ node: 'role'
+ },
+ {
+ id: 'schema', label:'{{ _('Schema') }}', cell: 'string', first_empty: false,
+ type: 'options', node: 'schema',
+ control: Backform.NodeListByNameControl.extend({
+ render: function(){
+
+ // Initialize parent's render method
+ Backform.NodeListByNameControl.prototype.render.apply(this, arguments);
+
+ // Set schema default value to its parent Schema
+ if(this.model.isNew()){
+ this.model.set({'schema': this.model.node_info.schema.label});
+ }
+ return this;
+ }
+ }),
+
+ // Don't show schema starts with "pg_"
+ filter: function(d){
+ if(d.label && d.label.match(/^pg_/)){
+ return false;
+ }
+ return true;
+ }
+ },
+ {
+ id: 'comment', label:'{{ _('Comment') }}', cell: 'string',
+ type: 'multiline'
+ },
+ {
+ id: 'system_view', label:'{{ _('System view?') }}', cell: 'string',
+ type: 'switch', disabled: true, mode: ['properties'], 'options': {
+ 'onText': 'Yes', 'offText': 'No', 'onColor': 'success',
+ 'offColor': 'default', 'size': 'small'
+ }
+ },
+ {
+ id: 'security_barrier', label:'{{ _('Security Barrier') }}', cell: 'string',
+ type: 'switch', min_version: '90200',
+ group: 'Definition', options: {
+ 'onText': 'Yes', 'offText': 'No', 'onColor': 'success',
+ 'offColor': 'default', 'size': 'small'
+ }
+ },
+ {
+ id: 'check_option', label:'{{ _('Check Options') }}',
+ control: 'select2', group: 'Definition', type: 'text',
+ min_version: '90400', mode:['create', 'edit'],
+ select2: {
+
+ // set select2 option width to 100%
+ width: '100%'
+ },
+ options:[
+ {label: "No", value: "no"},
+ {label: "Local", value: "local"},
+ {label: "Cascaded", value: "cascaded"}
+ ]
+ },
+ {
+ id: 'definition', label:'{{ _('Definition') }}', cell: 'string',
+ type: 'text', mode: ['properties', 'create', 'edit'], group: 'Definition',
+ control: 'sql-field'
+ },
+ {
+ id: 'm_view', label:'{{ _('Materialized view?') }}', cell: 'string',
+ type: 'switch', mode: ['properties'], min_version: '90300',
+ group: 'Materialization', options: {
+ 'onText': 'Yes', 'offText': 'No', 'onColor': 'success',
+ 'offColor': 'default', 'size': 'small'
+ }
+ },
+
+ // Add Privilege Control
+ {
+ id: 'datacl', label: '{{ _('Privileges') }}',
+ model: pgAdmin.Browser.Node.PrivilegeRoleModel.extend(
+ {privileges: ['a', 'r', 'w', 'd', 'x', 't']}), uniqueCol : ['grantee'],
+ editable: false, type: 'collection', group: '{{ _('Security') }}',
+ mode: ['properties', 'edit', 'create'], canAdd: true, canDelete: true,
+ control: 'unique-col-collection',
+ },
+
+ // Add Security Labels Control
+ {
+ id: 'seclabels', label: '{{ _('Security Labels') }}',
+ model: SecurityModel, editable: false, type: 'collection',
+ canEdit: false, group: '{{ _('Security') }}', canDelete: true,
+ mode: ['edit', 'create'], canAdd: true,
+ control: 'unique-col-collection', uniqueCol : ['provider']
+ }
+ ],
+ validate: function() {
+
+ // Triggers specific error messages for fields
+ var err = {},
+ errmsg,
+ field_name = this.get('name'),
+ field_def = this.get('definition');
+ if (_.isUndefined(field_name) || _.isNull(field_name) ||
+ String(field_name).replace(/^\s+|\s+$/g, '') == '') {
+ err['name'] = '{{ _("Please specify name.") }}';
+ errmsg = errmsg || err['name'];
+ this.errorModel.set('name', errmsg);
+ return errmsg;
+ }else{
+ this.errorModel.unset('name');
+ }
+ if(this.isNew()){
+ if (_.isUndefined(field_def) || _.isNull(field_def) ||
+ String(field_def).replace(/^\s+|\s+$/g, '') == '') {
+ err['definition'] = '{{ _("Please enter function definition.") }}';
+ errmsg = errmsg || err['definition'];
+ this.errorModel.set('definition', errmsg);
+ return errmsg;
+ }else{
+ this.errorModel.unset('definition');
+ }
+ }
+ return null;
+ }
+ }),
+
+ /**
+ Show or hide create view menu option on parent node
+ and hide for system view in catalogs.
+ */
+ 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 view
+ if (_.indexOf(['schema'], d._type) > -1)
+ return true;
+
+ if ('coll-view' == d._type) {
+
+ // Check if we are not child of view
+ 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['coll-view'];
+});
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/acl.sql
new file mode 100644
index 0000000..807fb04
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/acl.sql
@@ -0,0 +1,35 @@
+{#============================Get ACLs=========================#}
+{% if vid %}
+SELECT
+ 'datacl' as deftype,
+ COALESCE(gt.rolname, 'public') grantee,
+ g.rolname 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 'DELETE' THEN 'd'
+ WHEN 'INSERT' THEN 'a'
+ WHEN 'REFERENCES' THEN 'x'
+ WHEN 'SELECT' THEN 'r'
+ WHEN 'TRIGGER' THEN 't'
+ WHEN 'UPDATE' THEN 'w'
+ ELSE 'UNKNOWN'
+ END AS privilege_type
+ FROM
+ (SELECT relacl FROM pg_class cl
+ LEFT OUTER JOIN pg_shdescription descr ON (
+ cl.oid=descr.objoid AND descr.classoid='pg_class'::regclass)
+ WHERE cl.oid = {{ vid }}::OID AND relkind = 'v'
+ ) acl,
+ (SELECT (d).grantee AS grantee, (d).grantor AS grantor, (d).is_grantable AS is_grantable,
+ (d).privilege_type AS privilege_type
+ FROM (SELECT aclexplode(relacl) as d FROM pg_class cl1 WHERE cl1.oid = {{vid}}) 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
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/backend_support.sql
new file mode 100644
index 0000000..e3f8f4f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/backend_support.sql
@@ -0,0 +1,20 @@
+SELECT
+ CASE WHEN nsp.nspname IN ('sys', 'dbo', 'information_schema') THEN true ELSE false END AS dbSupport
+FROM
+ pg_namespace nsp
+WHERE
+ nsp.oid={{scid}}::int
+AND (
+ (nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+)
+AND
+ nspname NOT LIKE E'pg\\temp\\%'
+AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/create.sql
new file mode 100644
index 0000000..2ac1e3a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/create.sql
@@ -0,0 +1,25 @@
+{#============================Create new view=========================#}
+{% if display_comments %}
+-- View: {{ conn|qtIdent(data.schema, data.name) }}
+
+-- DROP VIEW {{ conn|qtIdent(data.schema, data.name) }};
+
+{% endif %}
+{% if data.name and data.schema and data.definition %}
+CREATE OR REPLACE VIEW {{ conn|qtIdent(data.schema, data.name) }}
+{% if ((data.check_option and data.check_option.lower() != 'no') or data.security_barrier) %}
+WITH ({% if data.check_option and data.check_option.lower() != 'no' %}
+check_option={{ data.check_option }}{% endif %}{{ ', ' if data.check_option and
+data.check_option.lower() != 'no' and data.security_barrier else '' }}{% if data.security_barrier %}
+security_barrier={{ data.security_barrier }}{% endif %}){% endif %} AS
+{{ data.definition.rstrip(';') }};
+{% if data.owner %}
+
+ALTER TABLE {{ conn|qtIdent(data.schema, data.name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{% if data.comment %}
+COMMENT ON VIEW {{ conn|qtIdent(data.schema, data.name) }}
+ IS {{ data.comment|qtLiteral }};
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/delete.sql
new file mode 100644
index 0000000..4e290a2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/delete.sql
@@ -0,0 +1,12 @@
+{#============================Drop/Cascade view by name=========================#}
+{% if vid %}
+SELECT
+ c.relname As name, nsp.nspname
+FROM
+ pg_class c
+ LEFT JOIN pg_namespace nsp on c.relnamespace = nsp.oid
+WHERE
+ c.relfilenode = {{ vid }};
+{% elif (name and nspname) %}
+DROP VIEW {{nspname}}.{{ conn|qtIdent(name) }} {% if cascade %} CASCADE {% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/grant.sql
new file mode 100644
index 0000000..9fa7181
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/grant.sql
@@ -0,0 +1,14 @@
+{#=====Grant Permissions to User Role on Views/Tables====#}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{# We will generate Security Label SQL using macro #}
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% if data.datacl %}
+{% for priv in data.datacl %}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/properties.sql
new file mode 100644
index 0000000..9487458
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/properties.sql
@@ -0,0 +1,29 @@
+{% if (vid and datlastsysoid) or scid %}
+SELECT c.oid, c.xmin, c.relname AS name, c.reltablespace AS spcoid, spc.spcname,
+ pg_get_userbyid(c.relowner) AS owner, description AS comment,
+ pg_get_viewdef(c.oid, true) AS definition, nsp.nspname AS schema,
+ (SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=c.oid AND sl1.objsubid=0) AS labels,
+ (SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=c.oid AND sl2.objsubid=0) AS providers
+FROM pg_class c
+ LEFT OUTER JOIN pg_namespace nsp on nsp.oid = c.relnamespace
+ LEFT OUTER JOIN pg_tablespace spc on spc.oid=c.reltablespace
+ LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0 AND des.classoid='pg_class'::regclass)
+ WHERE ((c.relhasrules AND (EXISTS (
+ SELECT r.rulename FROM pg_rewrite r
+ WHERE ((r.ev_class = c.oid)
+ AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char))
+{% if (vid and datlastsysoid) %}
+ AND c.oid = {{vid}}::oid
+{% elif scid %}
+ AND c.relnamespace = {{scid}}::oid ORDER BY c.relname
+{% endif %}
+
+{% elif type == 'roles' %}
+SELECT pr.rolname FROM pg_roles pr WHERE pr.rolcanlogin ORDER BY pr.rolname
+
+{% elif type == 'schemas' %}
+SELECT nsp.nspname
+ FROM pg_namespace nsp
+WHERE (nsp.nspname NOT LIKE E'pg\\_%'
+ AND nsp.nspname != 'information_schema')
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/update.sql
new file mode 100644
index 0000000..d258632
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/update.sql
@@ -0,0 +1,81 @@
+{#============================Update view=========================#}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{% if data %}
+{% set view_name = data.name if data.name else o_data.name %}
+{% set view_schema = data.schema if data.schema else o_data.schema %}
+{% set def = data.definition.rstrip(';') if data.definition %}
+{% if data.name and data.name != o_data.name %}
+ALTER VIEW {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ RENAME TO {{ conn|qtIdent(data.name) }};
+{% endif %}
+{% if data.schema and data.schema != o_data.schema %}
+ALTER VIEW {{ conn|qtIdent(o_data.schema, view_name ) }}
+ SET SCHEMA {{ data.schema }};
+{% endif %}
+{% if data.owner and data.owner != o_data.owner %}
+ALTER TABLE {{ conn|qtIdent(view_schema, view_name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{% if def and def != o_data.definition.rstrip(';') %}
+CREATE OR REPLACE VIEW {{ conn|qtIdent(view_schema, view_name) }}
+{% if (data.check_option and data.check_option != o_data.check_option)
+ or (data.security_barrier and data.security_barrier != o_data.security_barrier) %}
+ WITH ({% if (data.check_option and data.check_option != 'no') %}check_option={{ data.check_option }}{% endif %}{{', ' if data.check_option and data.check_option != 'no' and data.security_barrier }}{% if data.security_barrier %}security_barrier={{ data.security_barrier }}{% endif %})
+{% endif %}
+ AS{{ def }};
+{% else %}
+{% if (data.security_barrier and data.security_barrier != o_data.security_barrier) %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET (security_barrier={{ data.security_barrier }});
+{% endif %}
+{% if (data.check_option and data.check_option != o_data.check_option and data.check_option != 'no') %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET (check_option={{ data.check_option }});
+{% elif (data.check_option and data.check_option != o_data.check_option and data.check_option == 'no') %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }} RESET (check_option);
+{% endif %}
+{% endif %}
+{% if data.comment and data.comment != o_data.comment %}
+COMMENT ON VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ IS {{ data.comment|qtLiteral }};
+{% endif %}
+{# The SQL generated below will change privileges #}
+{% if data.datacl %}
+{% if 'deleted' in data.datacl %}
+{% for priv in data.datacl.deleted %}
+{{ PRIVILEGE.RESETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.datacl %}
+{% for priv in data.datacl.changed %}
+{{ PRIVILEGE.RESETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.datacl %}
+{% for priv in data.datacl.added %}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{# The SQL generated below will change Security Label #}
+{% if data.seclabels is not none and data.seclabels|length > 0 %}
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+{{ SECLABLE.DROP(conn, 'VIEW', data.name, r.provider) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/view_id.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/view_id.sql
new file mode 100644
index 0000000..6dff04e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.1_plus/view_id.sql
@@ -0,0 +1,4 @@
+{# Below will provide view id for newly created view #}
+{% if data %}
+SELECT c.oid FROM pg_class c WHERE c.relname = '{{ data.name }}';
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/acl.sql
new file mode 100644
index 0000000..2a3fbca
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/acl.sql
@@ -0,0 +1,35 @@
+{#============================Get ACLs=========================#}
+{% if vid %}
+SELECT
+ 'datacl' as deftype,
+ COALESCE(gt.rolname, 'public') grantee,
+ g.rolname 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 'DELETE' THEN 'd'
+ WHEN 'INSERT' THEN 'a'
+ WHEN 'REFERENCES' THEN 'x'
+ WHEN 'SELECT' THEN 'r'
+ WHEN 'TRIGGER' THEN 't'
+ WHEN 'UPDATE' THEN 'w'
+ ELSE 'UNKNOWN'
+ END AS privilege_type
+ FROM
+ (SELECT relacl FROM pg_class cl
+ LEFT OUTER JOIN pg_shdescription descr ON (
+ cl.oid=descr.objoid AND descr.classoid='pg_class'::regclass)
+ WHERE cl.oid = {{ vid }}::OID AND relkind = 'v'
+ ) acl,
+ (SELECT (d).grantee AS grantee, (d).grantor AS grantor, (d).is_grantable AS is_grantable,
+ (d).privilege_type AS privilege_type
+ FROM (SELECT aclexplode(relacl) as d FROM pg_class cl1 WHERE cl1.oid = {{ vid }}::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
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/backend_support.sql
new file mode 100644
index 0000000..7d19b68
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/backend_support.sql
@@ -0,0 +1,18 @@
+SELECT
+ CASE WHEN nsp.nspname IN ('sys', 'dbo', 'information_schema') THEN true ELSE false END AS dbSupport
+FROM pg_namespace nsp
+WHERE nsp.oid={{scid}}::int
+AND (
+ (nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'pgagent' AND EXIST
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+)
+AND
+ nspname NOT LIKE E'pg\\temp\\%'
+AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/create.sql
new file mode 100644
index 0000000..a345e15
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/create.sql
@@ -0,0 +1,25 @@
+{#============================Create new view=========================#}
+{% if display_comments %}
+-- View: {{ conn|qtIdent(data.schema, data.name) }}
+
+-- DROP VIEW {{ conn|qtIdent(data.schema, data.name) }};
+
+{% endif %}
+{% if data.name and data.schema and data.definition %}
+CREATE OR REPLACE VIEW {{ conn|qtIdent(data.schema, data.name) }}
+{% if ((data.check_option and data.check_option.lower() != 'no') or data.security_barrier) %}
+WITH ({% if data.check_option and data.check_option.lower() != 'no' %}
+check_option={{ data.check_option }}{% endif %}{{ ', ' if data.check_option and
+data.check_option.lower() != 'no' and data.security_barrier else '' }}{% if data.security_barrier %}
+security_barrier={{ data.security_barrier }}{% endif %}){% endif %} AS
+{{ data.definition.rstrip(';') }};
+{% if data.owner %}
+
+ALTER TABLE {{ conn|qtIdent(data.schema, data.name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{% if data.comment %}
+COMMENT ON VIEW {{ conn|qtIdent(data.schema, data.name) }}
+ IS {{ data.comment|qtLiteral }};
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/delete.sql
new file mode 100644
index 0000000..4e290a2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/delete.sql
@@ -0,0 +1,12 @@
+{#============================Drop/Cascade view by name=========================#}
+{% if vid %}
+SELECT
+ c.relname As name, nsp.nspname
+FROM
+ pg_class c
+ LEFT JOIN pg_namespace nsp on c.relnamespace = nsp.oid
+WHERE
+ c.relfilenode = {{ vid }};
+{% elif (name and nspname) %}
+DROP VIEW {{nspname}}.{{ conn|qtIdent(name) }} {% if cascade %} CASCADE {% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/grant.sql
new file mode 100644
index 0000000..9fa7181
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/grant.sql
@@ -0,0 +1,14 @@
+{#=====Grant Permissions to User Role on Views/Tables====#}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{# We will generate Security Label SQL using macro #}
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% if data.datacl %}
+{% for priv in data.datacl %}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/properties.sql
new file mode 100644
index 0000000..3da7801
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/properties.sql
@@ -0,0 +1,39 @@
+{% if (vid and datlastsysoid) or scid %}
+SELECT c.oid, c.xmin, c.relname AS name, c.reltablespace AS spcoid, spc.spcname,
+ pg_get_userbyid(c.relowner) AS owner, c.relacl, description As comment,
+ pg_get_viewdef(c.oid, true) AS definition, nsp.nspname AS schema,
+ {#=============Checks if it is system view================#}
+ {% if vid and datlastsysoid %}
+ CASE WHEN {{vid}} <= {{datlastsysoid}} THEN True ELSE False END AS system_view,
+ {% endif %}
+ {#=============Checks if it is materialized view========#}
+ CASE WHEN c.relkind = 'm' THEN True ELSE False END As m_view,
+ (SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=c.oid AND sl1.objsubid=0) AS labels,
+ (SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=c.oid AND sl2.objsubid=0) AS providers
+ , substring(array_to_string(c.reloptions, ',') FROM 'security_barrier=([a-z|0-9]*)') AS security_barrier
+FROM pg_class c
+ LEFT OUTER JOIN pg_namespace nsp on nsp.oid = c.relnamespace
+ LEFT OUTER JOIN pg_tablespace spc on spc.oid=c.reltablespace
+ LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0 AND des.classoid='pg_class'::regclass)
+ WHERE ((c.relhasrules AND (EXISTS (
+ SELECT r.rulename FROM pg_rewrite r
+ WHERE ((r.ev_class = c.oid)
+ AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char))
+{% if (vid and datlastsysoid) %}
+ AND c.oid = {{vid}}::oid
+{% elif scid %}
+ AND c.relnamespace = {{scid}}::oid ORDER BY c.relname
+{% endif %}
+
+{% elif type == 'roles' %}
+SELECT pr.rolname FROM pg_roles pr WHERE pr.rolcanlogin ORDER BY pr.rolname
+
+{% elif type == 'schemas' %}
+SELECT
+ nsp.nspname
+FROM
+ pg_namespace nsp
+WHERE
+ (nsp.nspname NOT LIKE E'pg\\_%'
+ AND nsp.nspname != 'information_schema')
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/update.sql
new file mode 100644
index 0000000..d258632
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/update.sql
@@ -0,0 +1,81 @@
+{#============================Update view=========================#}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{% if data %}
+{% set view_name = data.name if data.name else o_data.name %}
+{% set view_schema = data.schema if data.schema else o_data.schema %}
+{% set def = data.definition.rstrip(';') if data.definition %}
+{% if data.name and data.name != o_data.name %}
+ALTER VIEW {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ RENAME TO {{ conn|qtIdent(data.name) }};
+{% endif %}
+{% if data.schema and data.schema != o_data.schema %}
+ALTER VIEW {{ conn|qtIdent(o_data.schema, view_name ) }}
+ SET SCHEMA {{ data.schema }};
+{% endif %}
+{% if data.owner and data.owner != o_data.owner %}
+ALTER TABLE {{ conn|qtIdent(view_schema, view_name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{% if def and def != o_data.definition.rstrip(';') %}
+CREATE OR REPLACE VIEW {{ conn|qtIdent(view_schema, view_name) }}
+{% if (data.check_option and data.check_option != o_data.check_option)
+ or (data.security_barrier and data.security_barrier != o_data.security_barrier) %}
+ WITH ({% if (data.check_option and data.check_option != 'no') %}check_option={{ data.check_option }}{% endif %}{{', ' if data.check_option and data.check_option != 'no' and data.security_barrier }}{% if data.security_barrier %}security_barrier={{ data.security_barrier }}{% endif %})
+{% endif %}
+ AS{{ def }};
+{% else %}
+{% if (data.security_barrier and data.security_barrier != o_data.security_barrier) %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET (security_barrier={{ data.security_barrier }});
+{% endif %}
+{% if (data.check_option and data.check_option != o_data.check_option and data.check_option != 'no') %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET (check_option={{ data.check_option }});
+{% elif (data.check_option and data.check_option != o_data.check_option and data.check_option == 'no') %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }} RESET (check_option);
+{% endif %}
+{% endif %}
+{% if data.comment and data.comment != o_data.comment %}
+COMMENT ON VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ IS {{ data.comment|qtLiteral }};
+{% endif %}
+{# The SQL generated below will change privileges #}
+{% if data.datacl %}
+{% if 'deleted' in data.datacl %}
+{% for priv in data.datacl.deleted %}
+{{ PRIVILEGE.RESETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.datacl %}
+{% for priv in data.datacl.changed %}
+{{ PRIVILEGE.RESETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.datacl %}
+{% for priv in data.datacl.added %}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{# The SQL generated below will change Security Label #}
+{% if data.seclabels is not none and data.seclabels|length > 0 %}
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+{{ SECLABLE.DROP(conn, 'VIEW', data.name, r.provider) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/view_id.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/view_id.sql
new file mode 100644
index 0000000..6dff04e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.2_plus/view_id.sql
@@ -0,0 +1,4 @@
+{# Below will provide view id for newly created view #}
+{% if data %}
+SELECT c.oid FROM pg_class c WHERE c.relname = '{{ data.name }}';
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/acl.sql
new file mode 100644
index 0000000..b60bfa8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/acl.sql
@@ -0,0 +1,30 @@
+{#============================Get ACLs=========================#}
+{% if vid %}
+SELECT
+ 'datacl' as deftype, COALESCE(gt.rolname, 'public') grantee,
+ g.rolname 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 'DELETE' THEN 'd'
+ WHEN 'INSERT' THEN 'a'
+ WHEN 'REFERENCES' THEN 'x'
+ WHEN 'SELECT' THEN 'r'
+ WHEN 'TRIGGER' THEN 't'
+ WHEN 'UPDATE' THEN 'w'
+ ELSE 'UNKNOWN'
+ END AS privilege_type
+ FROM
+ (SELECT relacl FROM pg_class cl
+ LEFT OUTER JOIN pg_shdescription descr ON (
+ cl.oid=descr.objoid AND descr.classoid='pg_class'::regclass)
+ WHERE cl.oid = {{ vid }}::OID AND relkind = 'v'
+ ) acl,
+ aclexplode(relacl) 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
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/backend_support.sql
new file mode 100644
index 0000000..ab39cac
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/backend_support.sql
@@ -0,0 +1,18 @@
+SELECT
+ CASE WHEN nsp.nspname IN ('sys', 'dbo', 'information_schema') THEN true ELSE false END AS dbSupport
+FROM pg_namespace nsp
+WHERE nsp.oid={{scid}}::int
+AND (
+ (nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+)
+AND
+ nspname NOT LIKE E'pg\\temp\\%'
+AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/create.sql
new file mode 100644
index 0000000..a345e15
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/create.sql
@@ -0,0 +1,25 @@
+{#============================Create new view=========================#}
+{% if display_comments %}
+-- View: {{ conn|qtIdent(data.schema, data.name) }}
+
+-- DROP VIEW {{ conn|qtIdent(data.schema, data.name) }};
+
+{% endif %}
+{% if data.name and data.schema and data.definition %}
+CREATE OR REPLACE VIEW {{ conn|qtIdent(data.schema, data.name) }}
+{% if ((data.check_option and data.check_option.lower() != 'no') or data.security_barrier) %}
+WITH ({% if data.check_option and data.check_option.lower() != 'no' %}
+check_option={{ data.check_option }}{% endif %}{{ ', ' if data.check_option and
+data.check_option.lower() != 'no' and data.security_barrier else '' }}{% if data.security_barrier %}
+security_barrier={{ data.security_barrier }}{% endif %}){% endif %} AS
+{{ data.definition.rstrip(';') }};
+{% if data.owner %}
+
+ALTER TABLE {{ conn|qtIdent(data.schema, data.name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{% if data.comment %}
+COMMENT ON VIEW {{ conn|qtIdent(data.schema, data.name) }}
+ IS {{ data.comment|qtLiteral }};
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/delete.sql
new file mode 100644
index 0000000..044ff71
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/delete.sql
@@ -0,0 +1,12 @@
+{#============================Drop/Cascade view by name=========================#}
+{% if vid %}
+SELECT
+ c.relname AS name, nsp.nspname
+FROM
+ pg_class c
+ LEFT JOIN pg_namespace nsp on c.relnamespace = nsp.oid
+WHERE
+ c.relfilenode = {{ vid }};
+{% elif (name and nspname) %}
+DROP VIEW {{nspname}}.{{ conn|qtIdent(name) }} {% if cascade %} CASCADE {% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/grant.sql
new file mode 100644
index 0000000..9fa7181
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/grant.sql
@@ -0,0 +1,14 @@
+{#=====Grant Permissions to User Role on Views/Tables====#}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{# We will generate Security Label SQL using macro #}
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% if data.datacl %}
+{% for priv in data.datacl %}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/properties.sql
new file mode 100644
index 0000000..f4e3498
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/properties.sql
@@ -0,0 +1,60 @@
+{% if (vid and datlastsysoid) or scid %}
+SELECT c.oid, c.xmin, c.relname AS name, c.reltablespace AS spcoid, c.relkind, nsp.nspname AS schema,
+ c.relispopulated AS ispopulated, spc.spcname, pg_get_userbyid(c.relowner)
+ AS owner, c.relacl, description, pg_get_viewdef(c.oid, true) AS definition,
+ {#=============Checks if it is system view================#}
+ {% if vid and datlastsysoid %}
+ CASE WHEN {{vid}} <= {{datlastsysoid}} THEN True ELSE False END AS system_view,
+ {% endif %}
+ {#=============Checks if it is materialized view========#}
+ CASE WHEN c.relkind = 'm' THEN True ELSE False END As m_view,
+ (SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=c.oid AND sl1.objsubid=0) AS labels,
+ (SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=c.oid AND sl2.objsubid=0) AS providers
+ , substring(array_to_string(c.reloptions, ',') FROM 'security_barrier=([a-z|0-9]*)') AS security_barrier
+ , substring(array_to_string(c.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') AS autovacuum_enabled
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS autovacuum_vacuum_scale_factor
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS autovacuum_analyze_scale_factor
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') AS toast_autovacuum_enabled
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS toast_autovacuum_vacuum_scale_factor
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS toast_autovacuum_analyze_scale_factor
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age
+ , c.reloptions AS reloptions, tst.reloptions AS toast_reloptions
+ , (CASE WHEN c.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable
+FROM pg_class c
+ LEFT OUTER JOIN pg_namespace nsp on nsp.oid = c.relnamespace
+ LEFT OUTER JOIN pg_tablespace spc on spc.oid=c.reltablespace
+ LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0 AND des.classoid='pg_class'::regclass)
+ LEFT OUTER JOIN pg_class tst ON tst.oid = c.reltoastrelid
+ WHERE ((c.relhasrules AND (EXISTS (
+ SELECT r.rulename FROM pg_rewrite r
+ WHERE ((r.ev_class = c.oid)
+ AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char))
+{% if (vid and datlastsysoid) %}
+ AND c.oid = {{vid}}::oid
+{% elif scid %}
+ AND c.relnamespace = {{scid}}::oid ORDER BY c.relname
+{% endif %}
+
+{% elif type == 'roles' %}
+SELECT pr.rolname FROM pg_roles pr WHERE pr.rolcanlogin ORDER BY pr.rolname
+
+{% elif type == 'schemas' %}
+SELECT nsp.nspname
+ FROM pg_namespace nsp
+WHERE (nsp.nspname NOT LIKE E'pg\\_%'
+ AND nsp.nspname != 'information_schema')
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/update.sql
new file mode 100644
index 0000000..d258632
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/update.sql
@@ -0,0 +1,81 @@
+{#============================Update view=========================#}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{% if data %}
+{% set view_name = data.name if data.name else o_data.name %}
+{% set view_schema = data.schema if data.schema else o_data.schema %}
+{% set def = data.definition.rstrip(';') if data.definition %}
+{% if data.name and data.name != o_data.name %}
+ALTER VIEW {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ RENAME TO {{ conn|qtIdent(data.name) }};
+{% endif %}
+{% if data.schema and data.schema != o_data.schema %}
+ALTER VIEW {{ conn|qtIdent(o_data.schema, view_name ) }}
+ SET SCHEMA {{ data.schema }};
+{% endif %}
+{% if data.owner and data.owner != o_data.owner %}
+ALTER TABLE {{ conn|qtIdent(view_schema, view_name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{% if def and def != o_data.definition.rstrip(';') %}
+CREATE OR REPLACE VIEW {{ conn|qtIdent(view_schema, view_name) }}
+{% if (data.check_option and data.check_option != o_data.check_option)
+ or (data.security_barrier and data.security_barrier != o_data.security_barrier) %}
+ WITH ({% if (data.check_option and data.check_option != 'no') %}check_option={{ data.check_option }}{% endif %}{{', ' if data.check_option and data.check_option != 'no' and data.security_barrier }}{% if data.security_barrier %}security_barrier={{ data.security_barrier }}{% endif %})
+{% endif %}
+ AS{{ def }};
+{% else %}
+{% if (data.security_barrier and data.security_barrier != o_data.security_barrier) %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET (security_barrier={{ data.security_barrier }});
+{% endif %}
+{% if (data.check_option and data.check_option != o_data.check_option and data.check_option != 'no') %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET (check_option={{ data.check_option }});
+{% elif (data.check_option and data.check_option != o_data.check_option and data.check_option == 'no') %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }} RESET (check_option);
+{% endif %}
+{% endif %}
+{% if data.comment and data.comment != o_data.comment %}
+COMMENT ON VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ IS {{ data.comment|qtLiteral }};
+{% endif %}
+{# The SQL generated below will change privileges #}
+{% if data.datacl %}
+{% if 'deleted' in data.datacl %}
+{% for priv in data.datacl.deleted %}
+{{ PRIVILEGE.RESETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.datacl %}
+{% for priv in data.datacl.changed %}
+{{ PRIVILEGE.RESETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.datacl %}
+{% for priv in data.datacl.added %}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{# The SQL generated below will change Security Label #}
+{% if data.seclabels is not none and data.seclabels|length > 0 %}
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+{{ SECLABLE.DROP(conn, 'VIEW', data.name, r.provider) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/view_id.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/view_id.sql
new file mode 100644
index 0000000..6dff04e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.3_plus/view_id.sql
@@ -0,0 +1,4 @@
+{# Below will provide view id for newly created view #}
+{% if data %}
+SELECT c.oid FROM pg_class c WHERE c.relname = '{{ data.name }}';
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/acl.sql
new file mode 100644
index 0000000..f0c4c01
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/acl.sql
@@ -0,0 +1,35 @@
+{#============================Get ACLs=========================#}
+{% if vid %}
+SELECT
+ 'datacl' as deftype, COALESCE(gt.rolname, 'public') grantee,
+ g.rolname 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 'DELETE' THEN 'd'
+ WHEN 'INSERT' THEN 'a'
+ WHEN 'REFERENCES' THEN 'x'
+ WHEN 'SELECT' THEN 'r'
+ WHEN 'TRIGGER' THEN 't'
+ WHEN 'UPDATE' THEN 'w'
+ ELSE 'UNKNOWN'
+ END AS privilege_type
+ FROM
+ (SELECT
+ relacl FROM pg_class cl
+ LEFT OUTER JOIN
+ pg_shdescription descr
+ ON
+ (cl.oid=descr.objoid AND descr.classoid='pg_class'::regclass)
+ WHERE
+ cl.oid = {{ vid }}::OID AND relkind = 'v'
+ ) acl,
+ aclexplode(relacl) 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
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/backend_support.sql
new file mode 100644
index 0000000..e3f8f4f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/backend_support.sql
@@ -0,0 +1,20 @@
+SELECT
+ CASE WHEN nsp.nspname IN ('sys', 'dbo', 'information_schema') THEN true ELSE false END AS dbSupport
+FROM
+ pg_namespace nsp
+WHERE
+ nsp.oid={{scid}}::int
+AND (
+ (nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+)
+AND
+ nspname NOT LIKE E'pg\\temp\\%'
+AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/create.sql
new file mode 100644
index 0000000..2ac1e3a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/create.sql
@@ -0,0 +1,25 @@
+{#============================Create new view=========================#}
+{% if display_comments %}
+-- View: {{ conn|qtIdent(data.schema, data.name) }}
+
+-- DROP VIEW {{ conn|qtIdent(data.schema, data.name) }};
+
+{% endif %}
+{% if data.name and data.schema and data.definition %}
+CREATE OR REPLACE VIEW {{ conn|qtIdent(data.schema, data.name) }}
+{% if ((data.check_option and data.check_option.lower() != 'no') or data.security_barrier) %}
+WITH ({% if data.check_option and data.check_option.lower() != 'no' %}
+check_option={{ data.check_option }}{% endif %}{{ ', ' if data.check_option and
+data.check_option.lower() != 'no' and data.security_barrier else '' }}{% if data.security_barrier %}
+security_barrier={{ data.security_barrier }}{% endif %}){% endif %} AS
+{{ data.definition.rstrip(';') }};
+{% if data.owner %}
+
+ALTER TABLE {{ conn|qtIdent(data.schema, data.name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{% if data.comment %}
+COMMENT ON VIEW {{ conn|qtIdent(data.schema, data.name) }}
+ IS {{ data.comment|qtLiteral }};
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/delete.sql
new file mode 100644
index 0000000..53a9bdb
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/delete.sql
@@ -0,0 +1,13 @@
+{#============================Drop/Cascade view by name=========================#}
+{% if vid %}
+SELECT
+ c.relname As name, nsp.nspname
+FROM
+ pg_class c
+LEFT JOIN
+ pg_namespace nsp ON c.relnamespace = nsp.oid
+WHERE
+ c.relfilenode = {{ vid }};
+{% elif (name and nspname) %}
+DROP VIEW {{ conn|qtIdent(nspname, name) }} {% if cascade %} CASCADE {% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/grant.sql
new file mode 100644
index 0000000..9fa7181
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/grant.sql
@@ -0,0 +1,14 @@
+{#=====Grant Permissions to User Role on Views/Tables====#}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{# We will generate Security Label SQL using macro #}
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% if data.datacl %}
+{% for priv in data.datacl %}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/properties.sql
new file mode 100644
index 0000000..fa4dcf6
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/properties.sql
@@ -0,0 +1,65 @@
+{#============================Fetch View Properties=========================#}
+{% if (vid and datlastsysoid) or scid %}
+SELECT c.oid, c.xmin, c.relname AS name, c.reltablespace AS spcoid,
+ c.relispopulated AS ispopulated, spc.spcname, nsp.nspname as schema,
+ pg_get_userbyid(c.relowner) AS owner, description AS comment,
+ pg_get_viewdef(c.oid, true) AS definition,
+ {#=============Checks if it is system view================#}
+ {% if vid and datlastsysoid %}
+ CASE WHEN {{vid}} <= {{datlastsysoid}} THEN True ELSE False END AS system_view,
+ {% endif %}
+ {#=============Checks if it is materialized view========#}
+ CASE WHEN c.relkind = 'm' THEN True ELSE False END As m_view,
+ (SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE sl1.objoid=c.oid AND sl1.objsubid=0) AS seclabels
+ , substring(array_to_string(c.reloptions, ',') FROM 'check_option=([a-z]*)') AS check_option
+ , substring(array_to_string(c.reloptions, ',') FROM 'security_barrier=([a-z|0-9]*)') AS security_barrier
+ , substring(array_to_string(c.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') AS autovacuum_enabled
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS autovacuum_vacuum_scale_factor
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS autovacuum_analyze_scale_factor
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') AS toast_autovacuum_enabled
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS toast_autovacuum_vacuum_scale_factor
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS toast_autovacuum_analyze_scale_factor
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age
+ , c.reloptions AS reloptions, tst.reloptions AS toast_reloptions
+ , (CASE WHEN c.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable
+FROM pg_class c
+ LEFT OUTER JOIN pg_namespace nsp on nsp.oid = c.relnamespace
+ LEFT OUTER JOIN pg_tablespace spc on spc.oid=c.reltablespace
+ LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0 AND des.classoid='pg_class'::regclass)
+ LEFT OUTER JOIN pg_class tst ON tst.oid = c.reltoastrelid
+ WHERE ((c.relhasrules AND (EXISTS (
+ SELECT r.rulename FROM pg_rewrite r
+ WHERE ((r.ev_class = c.oid)
+ AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char))
+{% if (vid and datlastsysoid) %}
+ AND c.oid = {{vid}}::oid
+{% elif scid %}
+ AND c.relnamespace = {{scid}}::oid ORDER BY c.relname
+{% endif %}
+
+{% elif type == 'roles' %}
+SELECT pr.rolname FROM pg_roles pr WHERE pr.rolcanlogin ORDER BY pr.rolname
+
+{% elif type == 'schemas' %}
+SELECT
+ nsp.nspname
+FROM
+ pg_namespace nsp
+WHERE
+ (nsp.nspname NOT LIKE E'pg\\_%'
+ AND nsp.nspname != 'information_schema')
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/update.sql
new file mode 100644
index 0000000..1867038
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/update.sql
@@ -0,0 +1,81 @@
+{#============================Update View=========================#}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{% if data %}
+{% set view_name = data.name if data.name else o_data.name %}
+{% set view_schema = data.schema if data.schema else o_data.schema %}
+{% set def = data.definition.rstrip(';') if data.definition %}
+{% if data.name and data.name != o_data.name %}
+ALTER VIEW {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ RENAME TO {{ conn|qtIdent(data.name) }};
+{% endif %}
+{% if data.schema and data.schema != o_data.schema %}
+ALTER VIEW {{ conn|qtIdent(o_data.schema, view_name ) }}
+ SET SCHEMA {{ data.schema }};
+{% endif %}
+{% if data.owner and data.owner != o_data.owner %}
+ALTER TABLE {{ conn|qtIdent(view_schema, view_name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{% if def and def != o_data.definition.rstrip(';') %}
+CREATE OR REPLACE VIEW {{ conn|qtIdent(view_schema, view_name) }}
+{% if (data.check_option and data.check_option != o_data.check_option)
+ or (data.security_barrier and data.security_barrier != o_data.security_barrier) %}
+ WITH ({% if (data.check_option and data.check_option != 'no') %}check_option={{ data.check_option }}{% endif %}{{', ' if data.check_option and data.check_option != 'no' and data.security_barrier }}{% if data.security_barrier %}security_barrier={{ data.security_barrier }}{% endif %})
+{% endif %}
+ AS{{ def }};
+{% else %}
+{% if (data.security_barrier and data.security_barrier != o_data.security_barrier) %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET (security_barrier={{ data.security_barrier }});
+{% endif %}
+{% if (data.check_option and data.check_option != o_data.check_option and data.check_option != 'no') %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET (check_option={{ data.check_option }});
+{% elif (data.check_option and data.check_option != o_data.check_option and data.check_option == 'no') %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }} RESET (check_option);
+{% endif %}
+{% endif %}
+{% if data.comment and data.comment != o_data.comment %}
+COMMENT ON VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ IS {{ data.comment|qtLiteral }};
+{% endif %}
+{# The SQL generated below will change privileges #}
+{% if data.datacl %}
+{% if 'deleted' in data.datacl %}
+{% for priv in data.datacl.deleted %}
+{{ PRIVILEGE.RESETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.datacl %}
+{% for priv in data.datacl.changed %}
+{{ PRIVILEGE.RESETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.datacl %}
+{% for priv in data.datacl.added %}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{# The SQL generated below will change Security Label #}
+{% if data.seclabels is not none and data.seclabels|length > 0 %}
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+{{ SECLABLE.DROP(conn, 'VIEW', data.name, r.provider) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/view_id.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/view_id.sql
new file mode 100644
index 0000000..65648c0
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/9.4_plus/view_id.sql
@@ -0,0 +1,4 @@
+{#==Below will provide view id for last created view==#}
+{% if data %}
+SELECT c.oid FROM pg_class c WHERE c.relname = '{{ data.name }}';
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/acl.sql
new file mode 100644
index 0000000..b5d2225
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/acl.sql
@@ -0,0 +1,33 @@
+{#============================Get ACLs=========================#}
+{% if vid %}
+SELECT
+ 'datacl' as deftype,
+ COALESCE(gt.rolname, 'public') grantee,
+ g.rolname 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 'DELETE' THEN 'd'
+ WHEN 'INSERT' THEN 'a'
+ WHEN 'REFERENCES' THEN 'x'
+ WHEN 'SELECT' THEN 'r'
+ WHEN 'TRIGGER' THEN 't'
+ WHEN 'UPDATE' THEN 'w'
+ ELSE 'UNKNOWN'
+ END AS privilege_type
+ FROM
+ (SELECT relacl FROM pg_class cl
+ LEFT OUTER JOIN pg_shdescription descr ON (
+ cl.oid=descr.objoid AND descr.classoid='pg_class'::regclass)
+ WHERE cl.oid = {{ vid }}::OID AND relkind = 'v'
+ ) acl,
+ aclexplode(relacl) 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
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/backend_support.sql
new file mode 100644
index 0000000..5c0648d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/backend_support.sql
@@ -0,0 +1,20 @@
+SELECT
+ CASE WHEN nsp.nspname IN ('sys', 'dbo', 'information_schema') THEN true ELSE false END AS dbSupport
+FROM
+ pg_namespace nsp
+WHERE
+ nsp.oid={{scid}}::int
+AND (
+ (nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+)
+AND
+ nspname NOT LIKE E'pg\\temp\\%'
+AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/create.sql
new file mode 100644
index 0000000..2ac1e3a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/create.sql
@@ -0,0 +1,25 @@
+{#============================Create new view=========================#}
+{% if display_comments %}
+-- View: {{ conn|qtIdent(data.schema, data.name) }}
+
+-- DROP VIEW {{ conn|qtIdent(data.schema, data.name) }};
+
+{% endif %}
+{% if data.name and data.schema and data.definition %}
+CREATE OR REPLACE VIEW {{ conn|qtIdent(data.schema, data.name) }}
+{% if ((data.check_option and data.check_option.lower() != 'no') or data.security_barrier) %}
+WITH ({% if data.check_option and data.check_option.lower() != 'no' %}
+check_option={{ data.check_option }}{% endif %}{{ ', ' if data.check_option and
+data.check_option.lower() != 'no' and data.security_barrier else '' }}{% if data.security_barrier %}
+security_barrier={{ data.security_barrier }}{% endif %}){% endif %} AS
+{{ data.definition.rstrip(';') }};
+{% if data.owner %}
+
+ALTER TABLE {{ conn|qtIdent(data.schema, data.name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{% if data.comment %}
+COMMENT ON VIEW {{ conn|qtIdent(data.schema, data.name) }}
+ IS {{ data.comment|qtLiteral }};
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/delete.sql
new file mode 100644
index 0000000..8fc4e01
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/delete.sql
@@ -0,0 +1,9 @@
+{#============================Drop/Cascade view by name=========================#}
+{% if vid %}
+SELECT c.relname As name, nsp.nspname
+ FROM pg_class c
+ LEFT JOIN pg_namespace nsp on c.relnamespace = nsp.oid
+ WHERE c.relfilenode = {{ vid }};
+{% elif (name and nspname) %}
+DROP VIEW {{ conn|qtIdent(nspname, name) }} {% if cascade %} CASCADE {% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/grant.sql
new file mode 100644
index 0000000..9fa7181
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/grant.sql
@@ -0,0 +1,14 @@
+{#=====Grant Permissions to User Role on Views/Tables====#}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{# We will generate Security Label SQL using macro #}
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% if data.datacl %}
+{% for priv in data.datacl %}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/properties.sql
new file mode 100644
index 0000000..f926027
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/properties.sql
@@ -0,0 +1,63 @@
+{#============================Fetch View Properties=========================#}
+{% if (vid and datlastsysoid) or scid %}
+SELECT c.oid, c.xmin, c.relname AS name, c.reltablespace AS spcoid,
+ c.relispopulated AS ispopulated, spc.spcname, nsp.nspname as schema,
+ pg_get_userbyid(c.relowner) AS owner, description AS comment,
+ pg_get_viewdef(c.oid, true) AS definition,
+ {#=============Checks if it is system view================#}
+ {% if vid and datlastsysoid %}
+ CASE WHEN {{vid}} <= {{datlastsysoid}} THEN True ELSE False END AS system_view,
+ {% endif %}
+ {#=============Checks if it is materialized view========#}
+ CASE WHEN c.relkind = 'm' THEN True ELSE False END As m_view,
+ (SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE sl1.objoid=c.oid AND sl1.objsubid=0) AS seclabels
+ , substring(array_to_string(c.reloptions, ',') FROM 'check_option=([a-z]*)') AS check_option
+ , substring(array_to_string(c.reloptions, ',') FROM 'security_barrier=([a-z|0-9]*)') AS security_barrier
+ , substring(array_to_string(c.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') AS autovacuum_enabled
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS autovacuum_vacuum_scale_factor
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS autovacuum_analyze_scale_factor
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') AS toast_autovacuum_enabled
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS toast_autovacuum_vacuum_scale_factor
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS toast_autovacuum_analyze_scale_factor
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age
+ , c.reloptions AS reloptions, tst.reloptions AS toast_reloptions
+ , (CASE WHEN c.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable
+FROM pg_class c
+ LEFT OUTER JOIN pg_namespace nsp on nsp.oid = c.relnamespace
+ LEFT OUTER JOIN pg_tablespace spc on spc.oid=c.reltablespace
+ LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0 AND des.classoid='pg_class'::regclass)
+ LEFT OUTER JOIN pg_class tst ON tst.oid = c.reltoastrelid
+ WHERE ((c.relhasrules AND (EXISTS (
+ SELECT r.rulename FROM pg_rewrite r
+ WHERE ((r.ev_class = c.oid)
+ AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char))
+{% if (vid and datlastsysoid) %}
+ AND c.oid = {{vid}}::oid
+{% elif scid %}
+ AND c.relnamespace = {{scid}}::oid ORDER BY c.relname
+{% endif %}
+
+{% elif type == 'roles' %}
+SELECT pr.rolname FROM pg_roles pr WHERE pr.rolcanlogin ORDER BY pr.rolname
+
+{% elif type == 'schemas' %}
+SELECT nsp.nspname
+ FROM pg_namespace nsp
+WHERE
+ (nsp.nspname NOT LIKE E'pg\\_%'
+ AND nsp.nspname != 'information_schema')
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/update.sql
new file mode 100644
index 0000000..1867038
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/update.sql
@@ -0,0 +1,81 @@
+{#============================Update View=========================#}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{% if data %}
+{% set view_name = data.name if data.name else o_data.name %}
+{% set view_schema = data.schema if data.schema else o_data.schema %}
+{% set def = data.definition.rstrip(';') if data.definition %}
+{% if data.name and data.name != o_data.name %}
+ALTER VIEW {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ RENAME TO {{ conn|qtIdent(data.name) }};
+{% endif %}
+{% if data.schema and data.schema != o_data.schema %}
+ALTER VIEW {{ conn|qtIdent(o_data.schema, view_name ) }}
+ SET SCHEMA {{ data.schema }};
+{% endif %}
+{% if data.owner and data.owner != o_data.owner %}
+ALTER TABLE {{ conn|qtIdent(view_schema, view_name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{% if def and def != o_data.definition.rstrip(';') %}
+CREATE OR REPLACE VIEW {{ conn|qtIdent(view_schema, view_name) }}
+{% if (data.check_option and data.check_option != o_data.check_option)
+ or (data.security_barrier and data.security_barrier != o_data.security_barrier) %}
+ WITH ({% if (data.check_option and data.check_option != 'no') %}check_option={{ data.check_option }}{% endif %}{{', ' if data.check_option and data.check_option != 'no' and data.security_barrier }}{% if data.security_barrier %}security_barrier={{ data.security_barrier }}{% endif %})
+{% endif %}
+ AS{{ def }};
+{% else %}
+{% if (data.security_barrier and data.security_barrier != o_data.security_barrier) %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET (security_barrier={{ data.security_barrier }});
+{% endif %}
+{% if (data.check_option and data.check_option != o_data.check_option and data.check_option != 'no') %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET (check_option={{ data.check_option }});
+{% elif (data.check_option and data.check_option != o_data.check_option and data.check_option == 'no') %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }} RESET (check_option);
+{% endif %}
+{% endif %}
+{% if data.comment and data.comment != o_data.comment %}
+COMMENT ON VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ IS {{ data.comment|qtLiteral }};
+{% endif %}
+{# The SQL generated below will change privileges #}
+{% if data.datacl %}
+{% if 'deleted' in data.datacl %}
+{% for priv in data.datacl.deleted %}
+{{ PRIVILEGE.RESETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.datacl %}
+{% for priv in data.datacl.changed %}
+{{ PRIVILEGE.RESETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.datacl %}
+{% for priv in data.datacl.added %}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{# The SQL generated below will change Security Label #}
+{% if data.seclabels is not none and data.seclabels|length > 0 %}
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+{{ SECLABLE.DROP(conn, 'VIEW', data.name, r.provider) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/view_id.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/view_id.sql
new file mode 100644
index 0000000..65648c0
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/edb_9.1_plus/view_id.sql
@@ -0,0 +1,4 @@
+{#==Below will provide view id for last created view==#}
+{% if data %}
+SELECT c.oid FROM pg_class c WHERE c.relname = '{{ data.name }}';
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/acl.sql
new file mode 100644
index 0000000..46c5e5f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/acl.sql
@@ -0,0 +1,32 @@
+{#============================Get ACLs=========================#}
+{% if vid %}
+SELECT
+ 'datacl' as deftype, COALESCE(gt.rolname, 'public') grantee,
+ g.rolname 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 'DELETE' THEN 'd'
+ WHEN 'INSERT' THEN 'a'
+ WHEN 'REFERENCES' THEN 'x'
+ WHEN 'SELECT' THEN 'r'
+ WHEN 'TRIGGER' THEN 't'
+ WHEN 'UPDATE' THEN 'w'
+ ELSE 'UNKNOWN'
+ END AS privilege_type
+ FROM
+ (SELECT relacl FROM pg_class cl
+ LEFT OUTER JOIN pg_shdescription descr ON (
+ cl.oid=descr.objoid AND descr.classoid='pg_class'::regclass)
+ WHERE cl.oid = {{ vid }}::OID AND relkind = 'v'
+ ) acl,
+ (SELECT (d).grantee AS grantee, (d).grantor AS grantor, (d).is_grantable AS is_grantable,
+ (d).privilege_type AS privilege_type
+ FROM (SELECT aclexplode(relacl) as d FROM pg_class cl1 WHERE cl1.oid = {{ vid }}::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
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/backend_support.sql
new file mode 100644
index 0000000..e3f8f4f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/backend_support.sql
@@ -0,0 +1,20 @@
+SELECT
+ CASE WHEN nsp.nspname IN ('sys', 'dbo', 'information_schema') THEN true ELSE false END AS dbSupport
+FROM
+ pg_namespace nsp
+WHERE
+ nsp.oid={{scid}}::int
+AND (
+ (nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+)
+AND
+ nspname NOT LIKE E'pg\\temp\\%'
+AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/create.sql
new file mode 100644
index 0000000..2ac1e3a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/create.sql
@@ -0,0 +1,25 @@
+{#============================Create new view=========================#}
+{% if display_comments %}
+-- View: {{ conn|qtIdent(data.schema, data.name) }}
+
+-- DROP VIEW {{ conn|qtIdent(data.schema, data.name) }};
+
+{% endif %}
+{% if data.name and data.schema and data.definition %}
+CREATE OR REPLACE VIEW {{ conn|qtIdent(data.schema, data.name) }}
+{% if ((data.check_option and data.check_option.lower() != 'no') or data.security_barrier) %}
+WITH ({% if data.check_option and data.check_option.lower() != 'no' %}
+check_option={{ data.check_option }}{% endif %}{{ ', ' if data.check_option and
+data.check_option.lower() != 'no' and data.security_barrier else '' }}{% if data.security_barrier %}
+security_barrier={{ data.security_barrier }}{% endif %}){% endif %} AS
+{{ data.definition.rstrip(';') }};
+{% if data.owner %}
+
+ALTER TABLE {{ conn|qtIdent(data.schema, data.name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{% if data.comment %}
+COMMENT ON VIEW {{ conn|qtIdent(data.schema, data.name) }}
+ IS {{ data.comment|qtLiteral }};
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/delete.sql
new file mode 100644
index 0000000..8fc4e01
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/delete.sql
@@ -0,0 +1,9 @@
+{#============================Drop/Cascade view by name=========================#}
+{% if vid %}
+SELECT c.relname As name, nsp.nspname
+ FROM pg_class c
+ LEFT JOIN pg_namespace nsp on c.relnamespace = nsp.oid
+ WHERE c.relfilenode = {{ vid }};
+{% elif (name and nspname) %}
+DROP VIEW {{ conn|qtIdent(nspname, name) }} {% if cascade %} CASCADE {% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/grant.sql
new file mode 100644
index 0000000..9fa7181
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/grant.sql
@@ -0,0 +1,14 @@
+{#=====Grant Permissions to User Role on Views/Tables====#}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{# We will generate Security Label SQL using macro #}
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% if data.datacl %}
+{% for priv in data.datacl %}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/properties.sql
new file mode 100644
index 0000000..61c4a0e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/properties.sql
@@ -0,0 +1,65 @@
+{#============================Fetch View Properties=========================#}
+{% if (vid and datlastsysoid) or scid %}
+SELECT c.oid, c.xmin, c.relname AS name, c.reltablespace AS spcoid,
+ c.relispopulated AS ispopulated, spc.spcname, nsp.nspname as schema,
+ pg_get_userbyid(c.relowner) AS owner, description AS comment,
+ pg_get_viewdef(c.oid, true) AS definition,
+ {#=============Checks if it is system view================#}
+ {% if vid and datlastsysoid %}
+ CASE WHEN {{vid}} <= {{datlastsysoid}} THEN True ELSE False END AS system_view,
+ {% endif %}
+ {#=============Checks if it is materialized view========#}
+ CASE WHEN c.relkind = 'm' THEN True ELSE False END As m_view,
+ (SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE sl1.objoid=c.oid AND sl1.objsubid=0) AS seclabels
+ , substring(array_to_string(c.reloptions, ',') FROM 'check_option=([a-z]*)') AS check_option
+ , substring(array_to_string(c.reloptions, ',') FROM 'security_barrier=([a-z|0-9]*)') AS security_barrier
+ , substring(array_to_string(c.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') AS autovacuum_enabled
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS autovacuum_vacuum_scale_factor
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS autovacuum_analyze_scale_factor
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age
+ , substring(array_to_string(c.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') AS toast_autovacuum_enabled
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS toast_autovacuum_vacuum_scale_factor
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS toast_autovacuum_analyze_scale_factor
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age
+ , substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age
+ , c.reloptions AS reloptions, tst.reloptions AS toast_reloptions
+ , (CASE WHEN c.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable
+FROM pg_class c
+ LEFT OUTER JOIN pg_namespace nsp on nsp.oid = c.relnamespace
+ LEFT OUTER JOIN pg_tablespace spc on spc.oid=c.reltablespace
+ LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0 AND des.classoid='pg_class'::regclass)
+ LEFT OUTER JOIN pg_class tst ON tst.oid = c.reltoastrelid
+ WHERE ((c.relhasrules AND (EXISTS (
+ SELECT r.rulename FROM pg_rewrite r
+ WHERE ((r.ev_class = c.oid)
+ AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char))
+{% if (vid and datlastsysoid) %}
+ AND c.oid = {{vid}}::oid
+{% elif scid %}
+ AND c.relnamespace = {{scid}}::oid ORDER BY c.relname
+{% endif %}
+
+{% elif type == 'roles' %}
+SELECT pr.rolname FROM pg_roles pr WHERE pr.rolcanlogin ORDER BY pr.rolname
+
+{% elif type == 'schemas' %}
+SELECT
+ nsp.nspname
+FROM
+ pg_namespace nsp
+WHERE
+ (nsp.nspname NOT LIKE E'pg\\_%'
+ AND nsp.nspname != 'information_schema')
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/update.sql
new file mode 100644
index 0000000..1867038
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/update.sql
@@ -0,0 +1,81 @@
+{#============================Update View=========================#}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{% if data %}
+{% set view_name = data.name if data.name else o_data.name %}
+{% set view_schema = data.schema if data.schema else o_data.schema %}
+{% set def = data.definition.rstrip(';') if data.definition %}
+{% if data.name and data.name != o_data.name %}
+ALTER VIEW {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ RENAME TO {{ conn|qtIdent(data.name) }};
+{% endif %}
+{% if data.schema and data.schema != o_data.schema %}
+ALTER VIEW {{ conn|qtIdent(o_data.schema, view_name ) }}
+ SET SCHEMA {{ data.schema }};
+{% endif %}
+{% if data.owner and data.owner != o_data.owner %}
+ALTER TABLE {{ conn|qtIdent(view_schema, view_name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{% if def and def != o_data.definition.rstrip(';') %}
+CREATE OR REPLACE VIEW {{ conn|qtIdent(view_schema, view_name) }}
+{% if (data.check_option and data.check_option != o_data.check_option)
+ or (data.security_barrier and data.security_barrier != o_data.security_barrier) %}
+ WITH ({% if (data.check_option and data.check_option != 'no') %}check_option={{ data.check_option }}{% endif %}{{', ' if data.check_option and data.check_option != 'no' and data.security_barrier }}{% if data.security_barrier %}security_barrier={{ data.security_barrier }}{% endif %})
+{% endif %}
+ AS{{ def }};
+{% else %}
+{% if (data.security_barrier and data.security_barrier != o_data.security_barrier) %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET (security_barrier={{ data.security_barrier }});
+{% endif %}
+{% if (data.check_option and data.check_option != o_data.check_option and data.check_option != 'no') %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET (check_option={{ data.check_option }});
+{% elif (data.check_option and data.check_option != o_data.check_option and data.check_option == 'no') %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }} RESET (check_option);
+{% endif %}
+{% endif %}
+{% if data.comment and data.comment != o_data.comment %}
+COMMENT ON VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ IS {{ data.comment|qtLiteral }};
+{% endif %}
+{# The SQL generated below will change privileges #}
+{% if data.datacl %}
+{% if 'deleted' in data.datacl %}
+{% for priv in data.datacl.deleted %}
+{{ PRIVILEGE.RESETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.datacl %}
+{% for priv in data.datacl.changed %}
+{{ PRIVILEGE.RESETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.datacl %}
+{% for priv in data.datacl.added %}
+{{ PRIVILEGE.APPLY(conn, 'TABLE', priv.grantee, data.name, data.schema, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{# The SQL generated below will change Security Label #}
+{% if data.seclabels is not none and data.seclabels|length > 0 %}
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+{{ SECLABLE.DROP(conn, 'VIEW', data.name, r.provider) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+{{ SECLABLE.APPLY(conn, 'VIEW', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/view_id.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/view_id.sql
new file mode 100644
index 0000000..65648c0
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/sql/pre_9.1/view_id.sql
@@ -0,0 +1,4 @@
+{#==Below will provide view id for last created view==#}
+{% if data %}
+SELECT c.oid FROM pg_class c WHERE c.relname = '{{ data.name }}';
+{% endif %}
view thread (7+ 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]
Subject: Re: [pgAdmin4] PATCH: View and Role Node
In-Reply-To: <CA+OCxowX+RDV5mocfb8T=JKD-FQ6SbjKnOsqG28MYdZnh4V84g@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