public inbox for [email protected]
help / color / mirror / Atom feedFrom: Surinder Kumar <[email protected]>
To: Dave Page <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: [pgAdmin4] PATCH: View and Role Node
Date: Wed, 23 Mar 2016 21:14:48 +0530
Message-ID: <CAM5-9D_NCKo=J=Y7O2t-jKiW=C_bUjqeN4JE68kC6wJy3Q4fHA@mail.gmail.com> (raw)
In-Reply-To: <CA+OCxowX+RDV5mocfb8T=JKD-FQ6SbjKnOsqG28MYdZnh4V84g@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>
<CA+OCxowX+RDV5mocfb8T=JKD-FQ6SbjKnOsqG28MYdZnh4V84g@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
This patch contains following nodes:
1. View Node
2. Materialized View Node
This patch depends on couple of patches:
1. Control for displaying "auto vacuum" fields into grid
2. Tables sub node patch.
First apply these two patches then apply patch this patch.
Please review the patch.
On Tue, Mar 1, 2016 at 10:42 PM, Dave Page <[email protected]> wrote:
> 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.
>
Done
> >>
> >> - Check Options is missing from the properties tab.
>
Added
> >>
> >> - Views were made function in PostgreSQL v6.4! Why only load the module
> >> for 9.1+?
>
Fixed
> >>
> >> - The TRUNCATE ACL flag is not recognised, leading to ACLs being shown
> as:
> >> arwdUNKNOWNxt
>
Fixed
> >>
> >> - 4 character indents are not applied consistently to SQL templates
>
Done
> >>
> >> - Why do servers pre-9.3 have VACUUM options in the SQL templates?
>
Done
> >>
> >> - There should be no special casing of Slony schemas
> (backend_support.sql)
>
Done
> >>
> >> - Don't include \'s on line ends of multiline comments or docstrings.
>
Done
> >>
> >> - I can't create a Materialised View
>
This patch contains materialized view now
> >>
> >> On the dialogs:
> >>
> >> - The "Definition" input box should have a border.
>
Fixed
> >>
> >> - 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).
>
Fixed
> >>
> >> - The "Definition" input box should be displayed below the label at
> all
> >> times (unlike other controls), to maximise available space
>
Fixed
> >>
> >> - 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'
> >>
>
Done
> >>
> >> 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_material_view_node_patch_v3.patch (177.9K, 3-view_material_view_node_patch_v3.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..0ebf5c3
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py
@@ -0,0 +1,1327 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+"""Implements View and Materialized View Node"""
+
+import json
+from flask import render_template, request, jsonify
+from flask.ext.babel import gettext
+from pgadmin.utils.ajax import make_json_response, \
+ make_response as ajax_response, internal_server_error, \
+ bad_request
+from pgadmin.browser.utils import PGChildNodeView
+import pgadmin.browser.server_groups.servers as servers
+from pgadmin.browser.server_groups.servers.databases.schemas.utils import \
+ SchemaChildModule, parse_rule_definition
+from pgadmin.utils.ajax import precondition_required
+from pgadmin.utils.driver import get_driver
+from config import PG_DEFAULT_DRIVER
+from functools import wraps
+
+
+"""
+ This module is responsible for generating two nodes
+ 1) View
+ 2) Materialized View
+
+ We have created single file because view & material view has same
+ functionality. It allows us to share the same submodules for both
+ view, and material view modules.
+
+ This modules uses separate template paths for each respective node
+ - templates/view for View node
+ - templates/materialized_view for MaterializedView node
+
+ [Each path contains node specific js files as well as sql template files.]
+"""
+
+
+class ViewModule(SchemaChildModule):
+ """
+ class ViewModule(SchemaChildModule):
+
+ A module class for View node derived from SchemaChildModule.
+
+ Methods:
+ -------
+ * __init__(*args, **kwargs)
+ - Method is used to initialize the View and it's base module.
+
+ * get_nodes(gid, sid, did, scid)
+ - Method is used to generate the browser collection node.
+
+ * node_inode()
+ - Method is overridden from its base class to make the node as leaf node.
+
+ * script_load()
+ - Load the module script for View, when any of the server node is
+ initialized.
+ """
+ NODE_TYPE = 'view'
+ COLLECTION_LABEL = gettext("Views")
+
+ def __init__(self, *args, **kwargs):
+ """
+ Method is used to initialize the ViewModule and it's base module.
+
+ Args:
+ *args:
+ **kwargs:
+ """
+ super(ViewModule, self).__init__(*args, **kwargs)
+ self.min_ver = None
+ self.max_ver = None
+
+ 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 schemas.
+ """
+ return servers.ServerModule.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(
+ "view/css/view.css",
+ node_type=self.node_type,
+ _=gettext
+ ),
+ render_template(
+ "materialized_view/css/materialized_view.css",
+ node_type='materialized_view',
+ _=gettext
+ )
+ ]
+
+ for submodule in self.submodules:
+ snippets.extend(submodule.csssnippets)
+
+ return snippets
+
+
+class MaterializedViewModule(ViewModule):
+ """
+ class MaterializedViewModule(ViewModule)
+ A module class for the materialized view and view node derived
+ from ViewModule.
+ """
+
+ NODE_TYPE = 'materialized_view'
+ COLLECTION_LABEL = gettext("Materialized Views")
+
+ def __init__(self, *args, **kwargs):
+ """
+ Method is used to initialize the MaterializedViewModule and
+ it's base module.
+
+ Args:
+ *args:
+ **kwargs:
+ """
+ super(MaterializedViewModule, self).__init__(*args, **kwargs)
+ self.min_ver = 90300
+ self.max_ver = None
+
+view_blueprint = ViewModule(__name__)
+materialized_view_blueprint = MaterializedViewModule(
+ __name__)
+
+
+def check_precondition(f):
+ """
+ This function will behave as a decorator which will checks
+ database connection before running view, it will also attaches
+ manager,conn & template_path properties to instance of the method.
+
+ Assumptions:
+ This function will always be used as decorator of a class method.
+ """
+ @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']
+
+ # Set template path for sql scripts
+ self.template_path = self.template_initial + '/' + (
+ self.ppas_template_path(self.manager.version)
+ if self.manager.server_type == 'ppas' else
+ self.pg_template_path(self.manager.version)
+ )
+
+ return f(*args, **kwargs)
+
+ return wrap
+
+
+class ViewNode(PGChildNodeView):
+ """
+ This class is responsible for generating routes for view node.
+
+ Methods:
+ -------
+ * __init__(**kwargs)
+ - Method is used to initialize the ViewNode and it's base view.
+
+ * module_js()
+ - Request handler for module.js routes for the view node module
+ javascript, which returns javscript for this module.
+
+ * list()
+ - This function is used to list all the view nodes within the
+ collection.
+
+ * nodes()
+ - This function will used to create all the child node within the
+ collection, Here it will create all the view node.
+
+ * properties(gid, sid, did, scid)
+ - This function will show the properties of the selected view node.
+
+ * create(gid, sid, did, scid)
+ - This function will create the new view object.
+
+ * update(gid, sid, did, scid)
+ - This function will update the data for the selected view node.
+
+ * delete(self, gid, sid, scid):
+ - This function will drop the view object
+
+ * msql(gid, sid, did, scid)
+ - This function is used to return modified SQL for the selected view
+ node.
+
+ * get_sql(data, scid)
+ - This function will generate sql from model data
+
+ * sql(gid, sid, did, scid):
+ - This function will generate sql to show it in sql pane for the view
+ node.
+
+ * dependency(gid, sid, did, scid):
+ - This function will generate dependency list show it in dependency
+ pane for the selected view node.
+
+ * dependent(gid, sid, did, scid):
+ - This function will generate dependent list to show it in dependent
+ pane for the selected view node.
+ """
+ node_type = view_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'}],
+ 'get_tblspc': [{'get': 'get_tblspc'}, {'get': 'get_tblspc'}],
+ 'get_vacuum_defaults': [
+ {'get': 'get_vacuum_defaults'},
+ {'get': 'get_vacuum_defaults'}],
+ 'get_vacuum_toast_defaults': [
+ {'get': 'get_vacuum_toast_defaults'},
+ {'get': 'get_vacuum_toast_defaults'}]
+ })
+
+ def __init__(self, *args, **kwargs):
+ """
+ Initialize the variables used by methods of ViewNode.
+ """
+
+ super(ViewNode, self).__init__(*args, **kwargs)
+
+ self.manager = None
+ self.conn = None
+ self.template_path = None
+ self.template_initial = 'view'
+
+ @staticmethod
+ def ppas_template_path(ver):
+ """
+ Returns the template path for PPAS servers.
+ """
+ return 'ppas/9.3_plus'
+
+ @staticmethod
+ def pg_template_path(ver):
+ """
+ Returns the template path for PostgreSQL servers.
+ """
+ if ver >= 90400:
+ return 'pg/9.4_plus'
+ elif ver >= 90300:
+ return 'pg/9.3_plus'
+ elif ver >= 90200:
+ return 'pg/9.2_plus'
+ elif ver >= 90100:
+ return 'pg/9.1_plus'
+ return 'pg/9.1_plus'
+
+ @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, 'sql/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, 'sql/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, 'sql/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, 'sql/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
+ )
+ )
+
+ SQL = self.getSQL(gid, sid, data)
+ try:
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ SQL = render_template("/".join(
+ [self.template_path, 'sql/view_id.sql']), data=data)
+ status, view_id = self.conn.execute_scalar(SQL)
+ 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:
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ SQL = render_template("/".join(
+ [self.template_path, 'sql/view_id.sql']), data=data)
+ status, view_id = self.conn.execute_scalar(SQL)
+ 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 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, 'sql/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, 'sql/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):
+ 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',
+ 'D': 'TRUNCATE',
+ 'x': 'REFERENCES',
+ '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']])
+
+ # If we have all acl then just return all
+ if len(priv_with_grant) == len(db_privileges):
+ priv_with_grant = ['ALL']
+ if len(priv_without_grant) == len(db_privileges):
+ priv_without_grant = ['ALL']
+
+ # 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 generate sql from model data
+ """
+ try:
+ if vid is not None:
+ SQL = render_template("/".join(
+ [self.template_path, 'sql/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'])
+ try:
+ SQL = render_template("/".join(
+ [self.template_path, 'sql/update.sql']), data=data,
+ o_data=old_data, conn=self.conn)
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+ 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, 'sql/create.sql']), data=data)
+ if data['definition']:
+ SQL += "\n"
+ SQL += render_template("/".join(
+ [self.template_path, 'sql/grant.sql']), data=data)
+ return SQL
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ def get_index_column_details(self, idx, data):
+ """
+ This functional will fetch list of column details for index
+
+ Args:
+ idx: Index OID
+ data: Properties data
+
+ Returns:
+ Updated properties data with column details
+ """
+
+ self.index_temp_path = 'index'
+ SQL = render_template("/".join([self.index_temp_path,
+ 'sql/9.1_plus/column_details.sql']), idx=idx)
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=rset)
+
+ # 'attdef' comes with quotes from query so we need to strip them
+ # 'options' we need true/false to render switch ASC(false)/DESC(true)
+ columns = []
+ cols = []
+ for row in rset['rows']:
+
+ # We need all data as collection for ColumnsModel
+ cols_data = {
+ 'colname': row['attdef'].strip('"'),
+ 'collspcname': row['collnspname'],
+ 'op_class': row['opcname'],
+ }
+ if row['options'][0] == 'DESC':
+ cols_data['sort_order'] = True
+ columns.append(cols_data)
+
+ # We need same data as string to display in properties window
+ # If multiple column then separate it by colon
+ cols_str = row['attdef']
+ if row['collnspname']:
+ cols_str += ' COLLATE ' + row['collnspname']
+ if row['opcname']:
+ cols_str += ' ' + row['opcname']
+ if row['options'][0] == 'DESC':
+ cols_str += ' DESC'
+ cols.append(cols_str)
+
+ # Push as collection
+ data['columns'] = columns
+ # Push as string
+ data['cols'] = ', '.join(cols)
+
+ return data
+
+ def get_trigger_column_details(self, tid, clist):
+ """
+ This function will fetch list of column for trigger
+
+ Args:
+ tid: Table OID
+ clist: List of columns
+
+ Returns:
+ Updated properties data with column
+ """
+
+ self.trigger_temp_path = 'schema/trigger'
+ SQL = render_template("/".join([self.trigger_temp_path,
+ 'get_columns.sql']),
+ tid=tid, clist=clist)
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=rset)
+ # 'tgattr' contains list of columns from table used in trigger
+ columns = []
+
+ for row in rset['rows']:
+ columns.append({'column': row['name']})
+
+ return columns
+
+ def get_rule_sql(self, vid):
+ """
+ Get all non system rules of view node,
+ generate their sql and render
+ into sql tab
+ """
+
+ self.rule_temp_path = 'rules'
+ SQL_data = ''
+ SQL = render_template("/".join(
+ [self.rule_temp_path, 'sql/properties.sql']), tid=vid)
+
+ status, data = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=data)
+
+ for rule in data['rows']:
+
+ # Generate SQL only for non system rule
+ if rule['name'] != '_RETURN':
+ res = []
+ SQL = render_template("/".join(
+ [self.rule_temp_path, 'sql/properties.sql']),
+ rid=rule['oid']
+ )
+ status, res = self.conn.execute_dict(SQL)
+ res = parse_rule_definition(res)
+ SQL = render_template("/".join(
+ [self.rule_temp_path, 'sql/create.sql']),
+ data=res, display_comments=True)
+ SQL_data += '\n'
+ SQL_data += SQL
+ return SQL_data
+
+ def get_trigger_sql(self, vid):
+ """
+ Get all trigger nodes associated with view node,
+ generate their sql and render
+ into sql tab
+ """
+
+ # Define template path
+ self.trigger_temp_path = 'trigger'
+
+ SQL_data = ''
+ SQL = render_template("/".join(
+ [self.trigger_temp_path, 'sql/9.1_plus/properties.sql']),
+ tid=vid)
+
+ status, data = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=data)
+
+ for trigger in data['rows']:
+ SQL = render_template("/".join(
+ [self.trigger_temp_path, 'sql/9.1_plus/properties.sql']),
+ tid=trigger['oid'],
+ tgrelid=vid
+ )
+
+ status, res = self.conn.execute_dict(SQL)
+
+ res_rows = dict(res['rows'][0])
+ if res_rows['tgnargs'] > 1:
+ # We know that trigger has more than 1
+ # arguments, let's join them
+ res_rows['tgargs'] = ', '.join(
+ res_rows['tgargs'])
+
+ if len(res_rows['tgattr']) > 1:
+ columns = ', '.join(res_rows['tgattr'].split(' '))
+ res_rows['columns'] = self.get_trigger_column_details(
+ trigger['oid'], columns)
+ res_rows = trigger_definition(res_rows)
+
+ SQL = render_template("/".join(
+ [self.trigger_temp_path, 'sql/9.1_plus/create.sql']),
+ data=res_rows, display_comments=True)
+ SQL_data += '\n'
+ SQL_data += SQL
+
+ return SQL_data
+
+ def get_index_sql(self, vid):
+ """
+ Get all index associated with view node,
+ generate their sql and render
+ into sql tab
+ """
+
+ self.index_temp_path = 'index'
+ SQL_data = ''
+ SQL = render_template("/".join(
+ [self.index_temp_path, 'sql/9.1_plus/properties.sql']), tid=vid)
+ status, data = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=data)
+
+ for index in data['rows']:
+ res = []
+ SQL = render_template("/".join(
+ [self.index_temp_path, 'sql/9.1_plus/properties.sql']),
+ idx=index['oid'],
+ tid=vid
+ )
+ status, res = self.conn.execute_dict(SQL)
+
+ data = dict(res['rows'][0])
+ # Adding parent into data dict, will be using it while creating sql
+ data['schema'] = data['nspname']
+ data['table'] = data['tabname']
+
+ # Add column details for current index
+ data = self.get_index_column_details(index['oid'], data)
+
+ SQL = render_template("/".join(
+ [self.index_temp_path, 'sql/9.1_plus/create.sql']),
+ data=data, display_comments=True)
+ SQL_data += '\n'
+ SQL_data += SQL
+ return SQL_data
+
+ @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, 'sql/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, 'sql/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, 'sql/create.sql']),
+ data=result,
+ conn=self.conn,
+ display_comments=True
+ )
+ SQL += "\n"
+ SQL += render_template("/".join(
+ [self.template_path, 'sql/grant.sql']), data=result)
+
+ SQL_data += SQL
+ SQL_data += self.get_rule_sql(vid)
+ SQL_data += self.get_trigger_sql(vid)
+ SQL_data += self.get_index_sql(vid)
+
+ return ajax_response(response=SQL_data)
+
+ @check_precondition
+ def get_tblspc(self, gid, sid, did, scid):
+ """
+ This function to return list of tablespaces
+ """
+ res = []
+ try:
+ SQL = render_template(
+ "/".join([self.template_path, 'sql/get_tblspc.sql'])
+ )
+ status, rset = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in rset['rows']:
+ res.append(
+ {'label': row['spcname'], 'value': row['spcname']}
+ )
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @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
+ )
+
+
+class MaterializedViewNode(ViewNode):
+ """
+ This class is responsible for generating routes for
+ materialized view node.
+
+ Methods:
+ -------
+ * __init__(**kwargs)
+ - Method is used to initialize the MaterializedView
+ and it's base view.
+
+ * module_js()
+ - This property defines (if javascript) exists for this node.
+ Override this property for your own logic
+
+ * create(gid, sid, did, scid)
+ - Raise an error - we can not create a material view.
+
+ * update(gid, sid, did, scid)
+ - This function will update the data for the selected material node
+
+ * delete(self, gid, sid, scid):
+ - Raise an error - we can not delete a material view.
+
+ * get_sql(data, scid)
+ - This function will generate sql from model data
+
+ """
+
+ node_type = materialized_view_blueprint.node_type
+
+ def __init__(self, *args, **kwargs):
+ """
+ Initialize the variables used by methods of ViewNode.
+ """
+
+ super(MaterializedViewNode, self).__init__(*args, **kwargs)
+
+ self.template_initial = 'materialized_view'
+
+ @staticmethod
+ def pg_template_path(ver):
+ """
+ Returns the template path for PostgreSQL servers.
+ """
+ if ver >= 90300:
+ return 'pg/9.3_plus'
+ return 'pg/9.3_plus'
+
+ def get_sql(self, gid, sid, data, scid, vid=None):
+ """
+ This function will generate sql from model data
+ """
+ if scid is None:
+ return bad_request('Cannot create a View!')
+
+ return super(MaterializedViewNode, self).get_sql(
+ gid, sid, data, scid, vid)
+
+ 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, 'sql/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']
+
+ # merge vacuum lists into one
+ data['vacuum_data'] = {}
+ data['vacuum_data']['changed'] = []
+ data['vacuum_data']['reset'] = []
+ if ('vacuum_table' in data):
+ if ('changed' in data['vacuum_table']):
+ for item in data['vacuum_table']['changed']:
+ if 'value' in item.keys():
+ if item['value'] is None:
+ data['vacuum_data']['reset'].append(item)
+ else:
+ data['vacuum_data']['changed'].append(item)
+
+ if ('autovacuum_enabled' in data and
+ str(data['autovacuum_enabled']).lower() == 'true'):
+ data['vacuum_data']['changed'].append(
+ {'name': 'autovacuum_enabled',
+ 'value': data['autovacuum_enabled']})
+ elif (old_data['autovacuum_enabled'] == 'true'):
+ data['vacuum_data']['changed'].append(
+ {'name': 'autovacuum_enabled',
+ 'value': 'true'})
+ else:
+ data['vacuum_data']['changed'] = {}
+
+ 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'])
+ try:
+ SQL = render_template("/".join(
+ [self.template_path, 'sql/update.sql']), data=data,
+ o_data=old_data, conn=self.conn)
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+ else:
+ required_args = [
+ 'name',
+ 'schema',
+ 'definition'
+ ]
+ for arg in required_args:
+ if arg not in data:
+ return " -- definition incomplete"
+
+ # merge vacuum lists into one
+ vacuum_table = [item for item in data['vacuum_table']
+ if 'value' in item.keys()]
+ vacuum_toast = [
+ {'name': 'toast.'+item['name'], 'value':item['value']}
+ for item in data['vacuum_toast'] if 'value' in item.keys()]
+
+ # add vacuum_toast dict to vacuum_data only if
+ # toast's autovacuum is enabled
+ if ('toast_autovacuum_enabled' in data and
+ data['toast_autovacuum_enabled'] is True):
+ data['vacuum_data'] = vacuum_table + vacuum_toast
+ else:
+ data['vacuum_data'] = vacuum_table
+
+ if 'datacl' in data and data['datacl'] is not None:
+ data['datacl'] = self.parse_privileges(data['datacl'])
+ SQL = render_template("/".join(
+ [self.template_path, 'sql/create.sql']), data=data)
+ if data['definition']:
+ SQL += "\n"
+ SQL += render_template("/".join(
+ [self.template_path, 'sql/grant.sql']), data=data)
+ return SQL
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def get_vacuum_defaults(self, gid, sid, did, scid):
+ """
+ Fetch the default values for autovacuum
+ fields, return an array of
+ - label
+ - name
+ - setting
+ values
+ """
+
+ # returns an array of name & label values
+ vacuum_fields = render_template("/".join(
+ [self.template_path, 'vacuum_fields.json']
+ ))
+
+ vacuum_fields = json.loads(vacuum_fields)
+
+ # returns an array of setting & name values
+ vacuum_fields_keys = "'"+"','".join(
+ vacuum_fields['table'].keys())+"'"
+ SQL = render_template("/".join(
+ [self.template_path, 'sql/vacuum_defaults.sql']),
+ columns=vacuum_fields_keys)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in res['rows']:
+ row_name = row['name']
+ row['name'] = vacuum_fields['table'][row_name][0]
+ row['label'] = vacuum_fields['table'][row_name][1]
+ row['column_type'] = vacuum_fields['table'][row_name][2]
+
+ return ajax_response(
+ response=res['rows'],
+ status=200
+ )
+
+ @check_precondition
+ def get_vacuum_toast_defaults(self, gid, sid, did, scid):
+ """
+ Fetch the default values for autovacuum
+ fields, return an array of
+ - label
+ - name
+ - setting
+ values
+ """
+
+ # returns an array of name & label values
+ vacuum_fields = render_template("/".join(
+ [self.template_path, 'vacuum_fields.json']
+ ))
+
+ vacuum_fields = json.loads(vacuum_fields)
+
+ # returns an array of setting & name values
+ vacuum_fields_keys = "'"+"','".join(
+ vacuum_fields['toast'].keys())+"'"
+ SQL = render_template("/".join(
+ [self.template_path, 'sql/vacuum_defaults.sql']),
+ columns=vacuum_fields_keys)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in res['rows']:
+ row_name = row['name']
+ row['name'] = vacuum_fields['toast'][row_name][0]
+ row['label'] = vacuum_fields['toast'][row_name][1]
+ row['column_type'] = vacuum_fields['table'][row_name][2]
+
+ return ajax_response(
+ response=res['rows'],
+ status=200
+ )
+
+ def parse_vacuum_data(self, result, type):
+ """
+ Parse vacumm data
+ """
+
+ # returns an array of name & label values
+ vacuum_fields = render_template("/".join(
+ [self.template_path, 'vacuum_fields.json']
+ ))
+
+ vacuum_fields = json.loads(vacuum_fields)
+
+ # returns an array of setting & name values
+ vacuum_fields_keys = "'"+"','".join(
+ vacuum_fields[type].keys()) + "'"
+ SQL = render_template("/".join(
+ [self.template_path, 'sql/vacuum_defaults.sql']),
+ columns=vacuum_fields_keys)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ if type is 'table':
+ for row in res['rows']:
+ row_name = row['name']
+ row['name'] = vacuum_fields[type][row_name][0]
+ row['label'] = vacuum_fields[type][row_name][1]
+ row['column_type'] = vacuum_fields[type][row_name][2]
+ if result[row['name']] is not None:
+ row['value'] = row['setting'] = float(result[row_name])
+ #else:
+ # row['value'] = -1
+ elif type is 'toast':
+ for row in res['rows']:
+ row_old_name = row['name']
+ row_name = 'toast_{0}'.format(vacuum_fields[type][row_old_name][0])
+ row['name'] = vacuum_fields[type][row_old_name][0]
+ row['label'] = vacuum_fields[type][row_old_name][1]
+ row['column_type'] = vacuum_fields[type][row_old_name][2]
+ if result[row_name] and result[row_name] is not None:
+ row['value'] = row['setting'] = float(result[row_name])
+ #else:
+ # row['value'] = -1
+
+ return res['rows']
+
+ @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, 'sql/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, 'sql/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)
+
+ result['vacuum_table'] = self.parse_vacuum_data(result, 'table')
+ result['vacuum_toast'] = self.parse_vacuum_data(result, 'toast')
+
+ return ajax_response(
+ response=result,
+ status=200
+ )
+
+ViewNode.register_node_view(view_blueprint)
+MaterializedViewNode.register_node_view(materialized_view_blueprint)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/children/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/children/__init__.py
new file mode 100644
index 0000000..c17539f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/children/__init__.py
@@ -0,0 +1,12 @@
+"""
+We will use the exisiting modules for creating children module under
+materialize views.
+"""
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.columns \
+ import blueprint as columns_module
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.indexes \
+ import blueprint as indexes_modules
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.triggers \
+ import blueprint as triggers_modules
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.rules \
+ import blueprint as rules_modules
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/static/img/coll-materialized_view.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/static/img/coll-materialized_view.png
new file mode 100644
index 0000000000000000000000000000000000000000..40e65a96e18004104b041b0ae32579b26ae97058
GIT binary patch
literal 374
zcmV-+0g3*JP)<h;3K|Lk000e1NJLTq000mG000mO1^@s6AM^iV00009a7bBm000XU
z000XU0RWnu7ytkPC`m*?R5;6}lfO#CP!xy1+eV6l)y;y*AP73Rh@jQQr4BxYLpzBe
z;^H$1;tQlN;N&6>PA(>hLAOE$2b<YN5@VCx>!1V^ni|PnE{A)5-}!eBlsp2!8Jwv{
z_xxYN&Za98fNAM!r+Elb4K<vFpg}d7qay1FRZB!leDaRNo4Drr^^U#up3r_%Zyrty
zMW0`oB8+_iwCrQMIT$PM+D6LOia3pfYk85jS5BDpT2y2muX@kz%fU#$T)=Rmhd1pI
zN{>)_xh3TSloEv+sugkCnz_QX^u*q6^b<Q5U_V`EKMrm6egBz3w%Z*9N-2a8n3kS`
zXhlcZwT)C6BLS_AeIgkU%MT*y6UP3}1TI)wUL=ftA{hW~`DI$wk61H~McHlU3uk+8
Uv}-=(&;S4c07*qoM6N<$f<!@@p#T5?
literal 0
HcmV?d00001
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 0000000000000000000000000000000000000000..078d533684b8bd7e185749c4f1c9620af23d114b
GIT binary patch
literal 418
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbK}Y=BRQE08|>xOdKRnRzD^9{rj2
z_}lVF@Af@;b@}P@d(WRgefI3xqo=p7KVP}!V)Uc`-T(gmd-%V5`%AU||Nn1)p?>Gh
zn(Z&tPJa$N{=xgfzqUJn>TdojJ^s=A@Oy`SZ_IYR((AHW{03+NV@Z%-FoVOh8)+a;
zlDE5yQ1zZ1Ae(`+z$3C4NPB>>+sSM@AS2(?#W6(Ua%g{~P=f-Gv+27P9V>QJ|4;U6
zbe!GKH+kPSMNh%0YD;wVPp!&p$=H2Qa>4_iC-Ws`4oVu`kXvDIe5l_eV|jwW;+5+S
zYgy6~PYdy+-cS45)~k2Z!$WAAUct+ok^krC+MlVs+ppfyc`@F`nC<>l-VMq?t5r)}
zBT7;dOH!?pi&B9UgOP!up{{|Eu910&p^25DrIo3vwt<<IfkFPhkew(Ra`RI%(<*Um
UkbC@w6{vy1)78&qol`;+0LQMW5dZ)H
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/static/img/materialized_view.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/static/img/materialized_view.png
new file mode 100644
index 0000000000000000000000000000000000000000..7b72bdbb024d206a9ccccc307b980af7dc9cea88
GIT binary patch
literal 344
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!61|;P_|4#%`oCO|{#S9GG!XV7ZFl&wk0|O(M
zr;B5V#p&b(iHrjr$vHbE{{BC@z1bzxXo_Zv!8F~plmsAX<lOhGZpy*4Te=!QPFFbm
zU;Yt~PrZwz#Q!2=gMZH(vj3d_(fm;OaAw=?^=1c;K4AOv`+xgG`_JKRvEsKs+|pZn
z;^>D*x6OT+Cpd08n!IM#^LZ1F3M3y)72cH;)3)hB@w(<$ENtuI!+37X&3YKpe7)iO
zo7EB_E>059WTrQES9;xXdidD%!K10kJRqPs&0k{Df9H%vu?(M!xtNs?&v|Ub)6*j$
zbyq+v$yxg5%?jQE!}Cc$KEG!_e82v_q}|_|69*1#=yHs8ZfyjDhri#K1C8<Dp#F4o
lfA#<Q^_TpMg5)ZIft4K|E4KUpCSXu8c)I$ztaD0e0sxS)lm-9*
literal 0
HcmV?d00001
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 0000000000000000000000000000000000000000..44116db56496bb477c8ece7d3fdda666e1b3758b
GIT binary patch
literal 437
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbK}X@F0NE08|>xOdKRnaBToAO4>7
z;On9XZ+AU@dGYD<`_G?0efI3x<EOW7JYRF{Me@Q^nvec>Z-1%w|NsB(FVt^;p7P|y
zjoU9bT=|i6;cMdQ&tb<tdSCgTbKzU!@sHlyU#LC&-+lap_k(|JcmCAf{8f7ONA}_O
z4*TAi?Rus6RdrP&&|=1tAirP+hi5m^K%69RcNf7&TWxm%Ih+L^k;OpT1B~5HX4?T7
zS)MMAArhC96Am!+_;@g=95{03(5YkR4xT)E^z7l&>_QLRH3SlxI5?abofwq#PpGhn
z3y3`waupO1o<6}vaKfZ%6BvUwB`{h}XxPzW6T>ijp+R<{)&<#R3sx-A(NkQ!Xcb#n
z<0DHqpYzNNJzLm||5$yx0JK%L#5JNMC9x#cD!C{XNHG{07#ipr8tEDsh8P)GnHXD{
zm}(oCS{WG3d#ASwMMG|WN@iLmM1z4PSOZYCiIssFM8m1+p=*E|7(8A5T-G@yGywnx
Cm9(({
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/css/materialized_view.css b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/css/materialized_view.css
new file mode 100644
index 0000000..80b64ba
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/css/materialized_view.css
@@ -0,0 +1,12 @@
+.icon-materialized_view{
+ background-image: url('{{ url_for('NODE-materialized_view.static', filename='img/materialized_view.png') }}') !important;
+ border-radius: 10px;
+ background-repeat: no-repeat;
+ align-content: center;
+ vertical-align: middle;
+ height: 1.3em;
+}
+
+.sql_field_height_280 {
+ height: 280px;
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/js/materialized_view.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/js/materialized_view.js
new file mode 100644
index 0000000..22c3d3c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/js/materialized_view.js
@@ -0,0 +1,450 @@
+define(
+ ['jquery', 'underscore', 'underscore.string', 'pgadmin',
+ 'pgadmin.browser', 'codemirror', 'pgadmin.browser.server.privilege'],
+
+function($, _, S, pgAdmin, pgBrowser, CodeMirror) {
+
+ var cellFunction = function(model) {
+ var self = this,
+ m = model,
+ vartype = model.get('column_type');
+
+ switch(vartype) {
+ case "integer":
+ return Backgrid.IntegerCell;
+ break;
+ case "number":
+ return Backgrid.NumberCell;
+ break;
+ case "string":
+ return Backgrid.StringCell;
+ break;
+ default:
+ return Backgrid.Cell;
+ break;
+ }
+ };
+
+ // 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() {
+
+ // Clear errorModel before validate
+ this.handler.errorModel.clear();
+
+ 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;
+ }
+ });
+
+ // Define Security Model with fields and validation
+ var VacuumTableModel = pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ name: undefined,
+ setting: undefined,
+ label:undefined,
+ value: undefined,
+ column_type: undefined
+ },
+
+ toJSON: function(){
+ var d = pgAdmin.Browser.Node.Model.prototype.toJSON.apply(this);
+ delete d.label;
+ delete d.setting;
+ delete d.column_type;
+ return d;
+ }
+ });
+
+ /**
+ 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-materialized_view']) {
+ var materialized_views= pgAdmin.Browser.Nodes['coll-materialized_view'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'materialized_view',
+ label: '{{ _("Materialized Views") }}',
+ type: 'coll-materialized_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['materialized_view']) {
+ pgAdmin.Browser.Nodes['materialized_view'] = pgAdmin.Browser.Node.extend({
+ parent_type: ['schema', 'catalog'],
+ type: 'materialized_view',
+ label: '{{ _("Materialized View") }}',
+ hasSQL: true,
+ hasDepends: true,
+ collection_type: 'coll-materialized_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-materialized_view, view and schema.
+ @property {data} - Allow create view option on schema node or
+ system view nodes.
+ */
+ pgBrowser.add_menus([{
+ name: 'create_materialized_view_on_coll', node: 'coll-materialized_view',
+ module: this, applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _("Materialized View...") }}',
+ icon: 'wcTabIcon icon-materialized_view', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_materialized_view', node: 'materialized_view', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _("Materialized View...") }}',
+ icon: 'wcTabIcon icon-materialized_view', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_materialized_view', node: 'schema', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _("Materialized View...") }}',
+ icon: 'wcTabIcon icon-materialized_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, mode: ['properties']
+ },
+ {
+ 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'],
+ },
+ {
+ id: 'definition', label:'{{ _("") }}', cell: 'string',
+ type: 'text', mode: ['create', 'edit'], group: 'Definition',
+ control: Backform.SqlFieldControl, extraClasses:['sql_field_height_280']
+ },
+ // Add Privilege Control
+ {
+ id: 'datacl', label: '{{ _("Privileges") }}',
+ model: pgAdmin.Browser.Node.PrivilegeRoleModel.extend(
+ {privileges: ['a', 'r', 'w', 'd', 'D', 'x', 't']}), uniqueCol : ['grantee'],
+ editable: false, type: 'collection', group: '{{ _("Security") }}',
+ mode: ['edit', 'create'], canAdd: true, canDelete: true,
+ control: 'unique-col-collection', priority: 3
+ },
+
+ // 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']
+ },
+ {
+ id: 'with_data', label: '{{ _("With Data") }}',
+ group: '{{ _("Storage") }}', mode: ['edit', 'create'],
+ type: 'switch',
+ },
+ {
+ id: 'spcname', label: '{{ _("Tablespace") }}',
+ editable: false, group: '{{ _("Storage") }}',
+ mode: ['edit', 'create'], control: 'node-ajax-options',
+ url: 'get_tblspc', first_empty: true
+ },
+ {
+ id: 'fillfactor', label: '{{ _("Fill Factor") }}',
+ group: '{{ _("Storage") }}', mode: ['edit', 'create'],
+ type: 'integer', min: 10, max: 100
+ },
+ {
+ type: 'nested', control: 'tab', id: 'materialization',
+ label: '{{ _("Materialization") }}', mode: ['edit', 'create'],
+ group: '{{ _("Storage") }}',
+ schema:[
+ // Define materialized table tab fields
+ {
+ id: 'autovacuum_custom', label: '{{ _("Custom auto-vaccum") }}',
+ group: '{{ _("Table") }}', mode: ['edit', 'create'],
+ type: 'switch',
+ },
+ {
+ id: 'autovacuum_enabled', label: '{{ _("Enabled") }}',
+ group: '{{ _("Table") }}', mode: ['edit', 'create'],
+ type: 'switch',
+ deps: ['autovacuum_custom'],
+ disabled: function(m) {
+ if(m.has('autovacuum_custom')) {
+ return !m.get('autovacuum_custom');
+ }
+ return m.isNew();
+ }
+ },
+ {
+ id: 'vacuum_table', label: '{{ _("Vacuum Table") }}',
+ model: VacuumTableModel, editable: false, type: 'collection',
+ canEdit: true, group: '{{ _("Table") }}',
+ mode: ['edit', 'create'], url: 'get_vacuum_defaults',
+ control: Backform.VacuumCollectionControl.extend({
+ grid_columns :[
+ {
+ name: 'label', label: '{{ _("Label") }}',
+ cell: 'string', editable: false
+ },
+ {
+ name: 'value', label: '{{ _("Value") }}',
+ cellFunction: cellFunction, editable: function(m) {
+ if(m.handler.has('autovacuum_enabled')) {
+ return m.handler.get('autovacuum_enabled');
+ }
+ return !m.handler.isNew();
+ }
+ },
+ {
+ name: 'setting', label: '{{ _("Default value") }}',
+ cellFunction: cellFunction, editable: false
+ }
+ ]
+ }),
+ deps: ['autovacuum_enabled']
+ },
+
+ // Define Toast Table group fields
+ {
+ id: 'toast_autovaccum', label: '{{ _("Custom auto-vaccum") }}',
+ group: '{{ _("Toast Table") }}', mode: ['edit', 'create'],
+ type: 'switch',
+ disabled: function(m) {
+ return !m.isNew();
+ }
+ },
+ {
+ id: 'toast_autovacuum_enabled', label: '{{ _("Enabled") }}',
+ group: '{{ _("Toast Table") }}', mode: ['edit', 'create'],
+ type: 'switch',
+ deps:['toast_autovaccum'],
+ disabled: function(m) {
+ if(!m.isNew()) {
+ return true;
+ }
+ else if (m.has('toast_autovaccum'))
+ return !m.get('toast_autovaccum');
+ else
+ return true;
+ }
+ },
+ {
+ id: 'vacuum_toast', label: '{{ _("Vacuum Toast Table") }}',
+ model: VacuumTableModel, type: 'collection', editable: function(m) {
+ return m.isNew();
+ },
+ canEdit: true, group: '{{ _("Toast Table") }}',
+ mode: ['edit', 'create'], url: 'get_vacuum_toast_defaults',
+ control: Backform.VacuumCollectionControl.extend({
+ grid_columns :[
+ {
+ name: 'label', label: '{{ _("Label") }}',
+ cell: 'string', editable: false
+ },
+ {
+ name: 'value', label: '{{ _("Value") }}',
+ cellFunction: cellFunction, editable: function(m) {
+ if(!m.handler.isNew()) {
+ return false;
+ }
+ else if(m.handler.has('toast_autovacuum_enabled')) {
+ return m.handler.get('toast_autovacuum_enabled');
+ }
+ }
+ },
+ {
+ name: 'setting', label: '{{ _("Default value") }}',
+ cellFunction: cellFunction, editable: false
+ }
+ ]
+ }),
+ deps: ['toast_autovacuum_enabled']
+ }
+
+ ]
+ },
+ {
+ id: 'acl', label: '{{ _("ACL") }}',
+ mode: ['properties'], type: 'text'
+ },
+ ],
+ 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-materialized_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-materialized_view'];
+});
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/acl.sql
new file mode 100644
index 0000000..0c1bd6c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/acl.sql
@@ -0,0 +1,41 @@
+{#============================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'
+ WHEN 'TRUNCATE' THEN 'D'
+ 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 = 'm'
+ ) 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/materialized_view/pg/9.3_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/create.sql
new file mode 100644
index 0000000..03260c9
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/create.sql
@@ -0,0 +1,45 @@
+{# ===================== Create new view ===================== #}
+{% if display_comments %}
+-- View: {{ conn|qtIdent(data.schema, data.name) }}
+
+-- DROP MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }};
+
+{% endif %}
+{% if data.name and data.schema and data.definition %}
+CREATE MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }}
+{% if(data.fillfactor or data['autovacuum_enabled'] or data['toast_autovacuum_enabled']) %}
+WITH(
+{% if data.fillfactor %}
+ FILLFACTOR = {{ data.fillfactor }}{% if data['autovacuum_enabled'] or data['toast_autovacuum_enabled'] or data['vacuum_data']|length > 0 %},{% endif %}{{ '\r' }}
+{% endif %}
+{% if data['autovacuum_enabled'] %}
+ autovacuum_enabled = {{ data['autovacuum_enabled']|lower }}{% if data['toast_autovacuum_enabled'] or data['vacuum_data']|length > 0 %},{% endif %}{{ '\r' }}
+{% endif %}
+{% if data['toast_autovacuum_enabled'] %}
+ {{ 'toast.autovacuum_enabled' }} = {{ data['toast_autovacuum_enabled']|lower }}{% if data['vacuum_data']|length > 0 %},{% endif %}{{ '\r' }}
+{% endif %}
+{% for field in data['vacuum_data'] %}
+{% if field.value is defined and field.value != '' and field.value != none %}
+{% if loop.index > 1%},
+{% endif %} {{ field.name }} = {{ field.value|lower }}{% endif %}
+{% endfor %}{{ '\r' }}
+)
+{% endif %}
+{% if data.spcname %}TABLESPACE {{ data.spcname }}
+{% endif %} AS
+{{ data.definition.rstrip(';') }}
+{% if data.with_data %}
+ WITH DATA;
+{% else %}
+ WITH NO DATA;
+{% endif %}
+{% if data.owner %}
+
+ALTER TABLE {{ conn|qtIdent(data.schema, data.name) }}
+ OWNER TO {{ conn|qtIdent(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/materialized_view/pg/9.3_plus/sql/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/delete.sql
new file mode 100644
index 0000000..cf667fc
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/delete.sql
@@ -0,0 +1,13 @@
+{# =================== Drop/Cascade materialized 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 MATERIALIZED VIEW {{ conn|qtIdent(nspname, name) }} {% if cascade %} CASCADE {% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/get_tblspc.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/get_tblspc.sql
new file mode 100644
index 0000000..a8a482c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/get_tblspc.sql
@@ -0,0 +1,2 @@
+{# ===== Get list of tablespaces ===== #}
+select tblspc.spcname from pg_tablespace tblspc WHERE tblspc.spcname != 'pg_global'
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/grant.sql
new file mode 100644
index 0000000..6cdc8e5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/grant.sql
@@ -0,0 +1,14 @@
+{# ===== Grant Permissions to User Role on Views/Tables ==== #}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/schemas/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.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/properties.sql
new file mode 100644
index 0000000..6411123
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/properties.sql
@@ -0,0 +1,109 @@
+{# ========================== Fetch Materialized View Properties ========================= #}
+{% if (vid and datlastsysoid) or scid %}
+SELECT
+ c.oid,
+ c.xmin,
+ c.relname AS name,
+ c.reltablespace AS spcoid,
+ c.relispopulated AS with_data,
+ (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE 'pg_default' END) as spcname,
+ c.relacl,
+ 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 %}
+ True AS autovacuum_custom,
+ True AS toast_autovaccum,
+ array_to_string(c.relacl::text[], ', ') AS acl,
+ (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 '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)) )))
+ AND (c.relkind = 'm'::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/materialized_view/pg/9.3_plus/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/update.sql
new file mode 100644
index 0000000..30a9a34
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/update.sql
@@ -0,0 +1,152 @@
+{# ===================== Update View ===================#}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/schemas/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 %}
+{# ===== Rename mat view ===== #}
+{% if data.name and data.name != o_data.name %}
+ALTER MATERIALIZED VIEW {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ RENAME TO {{ conn|qtIdent(data.name) }};
+
+{% endif %}
+{# ===== Alter schema view ===== #}
+{% if data.schema and data.schema != o_data.schema %}
+ALTER MATERIALIZED VIEW {{ conn|qtIdent(o_data.schema, view_name ) }}
+ SET SCHEMA {{ conn|qtIdent(data.schema) }};
+
+{% endif %}
+{# ===== Alter Table owner ===== #}
+{% if data.owner and data.owner != o_data.owner %}
+ALTER TABLE {{ conn|qtIdent(view_schema, view_name) }}
+ OWNER TO {{ conn|qtIdent(data.owner) }};
+
+{% endif %}
+{% if data['vacuum_data']['reset']|length > 0 %}
+ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }}
+RESET(
+{% for field in data['vacuum_data']['reset'] %}
+ {{ field.name }}{% if not loop.last %},{% endif %}{{ '\r' }}
+{% endfor %}
+)
+{% endif %}
+{# ===== First Drop and then create mat view ===== #}
+{% if def and def != o_data.definition.rstrip(';') %}
+{% if data.fillfactor or ( data['vacuum_data']['changed']|length > 0 ) %}
+DROP MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }};
+CREATE MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }}
+WITH(
+{% if data.fillfactor %}
+ FILLFACTOR = {{ data.fillfactor }}{% if data['vacuum_data']['changed']|length > 0 %},{% endif %}{{ '\r' }}
+{% endif %}
+{% if data['vacuum_data']['changed']|length > 0 %}
+{% for field in data['vacuum_data']['changed'] %}
+ {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}{{ '\r' }}
+{% endfor %}
+{% endif %}
+)
+ AS
+{{ def }}
+{% if data.with_data %}
+ WITH DATA;
+
+{% else %}
+ WITH NO DATA;
+
+{% endif %}
+{% endif %}
+{% else %}
+{# ======= Alter Tablespace ========= #}
+{%- if data.spcoid and o_data.spcoid != data.spcoid -%}
+ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET TABLESPACE {{ data.spcoid }};
+
+{% endif %}
+{# ======= SET/RESET Fillfactor ========= #}
+{% if data.fillfactor and o_data.fillfactor != data.fillfactor %}
+ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }}
+SET(FILLFACTOR = {{ data.fillfactor }});
+
+{% elif data.fillfactor == '' %}
+ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }}
+RESET(
+ FILLFACTOR
+);
+
+{% endif %}
+{# ===== Check for with_data property ===== #}
+{% if data.with_data is defined and o_data.with_data|lower != data.with_data|lower %}
+REFRESH MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }} WITH{{ ' NO' if data.with_data|lower == 'false' else '' }} DATA;
+
+{% endif %}
+{# ===== Check for Table tab properties ===== #}
+{% if data.autovacuum_custom is defined and data.autovacuum_custom|lower == 'false' %}
+ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }}
+RESET(
+ autovacuum_vacuum_threshold,
+ autovacuum_analyze_threshold,
+ autovacuum_vacuum_scale_factor,
+ autovacuum_analyze_scale_factor,
+ autovacuum_vacuum_cost_delay,
+ autovacuum_vacuum_cost_limit,
+ autovacuum_freeze_min_age,
+ autovacuum_freeze_max_age,
+ autovacuum_freeze_table_age
+);
+
+{% elif(data['vacuum_data']['changed']|length > 0) %}
+ALTER MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }} SET(
+{% for field in data['vacuum_data']['changed'] %}
+{% if field.value != None %}
+ {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}{{ '\r' }}
+{% endif %}
+{% endfor %}
+);
+
+{% endif %}{# ===== End check for custom autovaccum ===== #}
+{% endif %}{# ===== End block for check data definition ===== #}
+{%- 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.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{%- endfor %}
+{% endif %}
+{% if 'added' in data.datacl %}
+{% for priv in data.datacl.added %}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% 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/materialized_view/pg/9.3_plus/sql/vacuum_defaults.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/vacuum_defaults.sql
new file mode 100644
index 0000000..e60598c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/vacuum_defaults.sql
@@ -0,0 +1,2 @@
+{# ============= Fetch list of default values for autovacuum parameters =============== #}
+SELECT name, setting::numeric AS setting FROM pg_settings WHERE name IN({{ columns }}) ORDER BY name
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/view_id.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/view_id.sql
new file mode 100644
index 0000000..d192a80
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/sql/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/materialized_view/pg/9.3_plus/vacuum_fields.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/vacuum_fields.json
new file mode 100644
index 0000000..7f8fb28
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/pg/9.3_plus/vacuum_fields.json
@@ -0,0 +1,25 @@
+{# ===== Define name, label and column type of vacuum settings ===== #}
+{
+ "table":
+ {
+ "autovacuum_vacuum_threshold": ["autovacuum_vacuum_threshold", "VACCUM base threshold", "integer"],
+ "autovacuum_analyze_threshold": ["autovacuum_analyze_threshold", "ANALYZE base threshold", "integer"],
+ "autovacuum_vacuum_scale_factor": ["autovacuum_vacuum_scale_factor", "VACCUM scale factor", "number"],
+ "autovacuum_analyze_scale_factor": ["autovacuum_analyze_scale_factor", "ANALYZE scale factor", "number"],
+ "autovacuum_vacuum_cost_delay": ["autovacuum_vacuum_cost_delay", "VACCUM cost delay", "integer"],
+ "autovacuum_vacuum_cost_limit": ["autovacuum_vacuum_cost_limit", "VACCUM cost limit", "integer"],
+ "autovacuum_freeze_max_age": ["autovacuum_freeze_max_age", "FREEZE maximum age", "integer"],
+ "vacuum_freeze_min_age": ["autovacuum_freeze_min_age", "FREEZE minimum age", "integer"],
+ "vacuum_freeze_table_age": ["autovacuum_freeze_table_age", "FREEZE table age", "integer"]
+ },
+ "toast":
+ {
+ "autovacuum_vacuum_threshold": ["autovacuum_vacuum_threshold", "VACCUM base threshold", "integer"],
+ "autovacuum_vacuum_scale_factor": ["autovacuum_vacuum_scale_factor", "VACCUM scale factor", "number"],
+ "autovacuum_vacuum_cost_delay": ["autovacuum_vacuum_cost_delay", "VACCUM cost delay", "integer"],
+ "autovacuum_vacuum_cost_limit": ["autovacuum_vacuum_cost_limit", "VACCUM cost limit", "integer"],
+ "autovacuum_freeze_max_age": ["autovacuum_freeze_max_age", "FREEZE maximum age", "integer"],
+ "vacuum_freeze_min_age": ["autovacuum_freeze_min_age", "FREEZE minimum age", "integer"],
+ "vacuum_freeze_table_age": ["autovacuum_freeze_table_age", "FREEZE table age", "integer"]
+ }
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/acl.sql
new file mode 100644
index 0000000..0c1bd6c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/acl.sql
@@ -0,0 +1,41 @@
+{#============================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'
+ WHEN 'TRUNCATE' THEN 'D'
+ 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 = 'm'
+ ) 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/materialized_view/ppas/9.3_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/create.sql
new file mode 100644
index 0000000..d5f1a87
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/create.sql
@@ -0,0 +1,39 @@
+{# ===================== Create new view ===================== #}
+{% if display_comments %}
+-- View: {{ conn|qtIdent(data.schema, data.name) }}
+
+-- DROP MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }};
+
+{% endif %}
+{% if data.name and data.schema and data.definition %}
+CREATE MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }}
+{% if(data.fillfactor or data['autovacuum_enabled'] or data['toast_autovacuum_enabled']) %}
+WITH(
+{% if data.fillfactor %}
+ FILLFACTOR = {{ data.fillfactor }}{% if data['vacuum_data']|length > 0 %},{% endif %}{{ '\r' }}
+{% endif %}
+{% for field in data['vacuum_data'] %}
+{% if field.value is defined and field.value != '' and field.value != none %}
+{% if loop.index > 1%},
+{% endif %} {{ field.name }} = {{ field.value|lower }}{% endif %}
+{% endfor %}{{ '\r' }}
+)
+{% endif %}
+{% if data.spcname %}TABLESPACE {{ data.spcname }}
+{% endif %} AS
+{{ data.definition.rstrip(';') }}
+{% if data.with_data %}
+ WITH DATA;
+{% else %}
+ WITH NO DATA;
+{% endif %}
+{% if data.owner %}
+
+ALTER TABLE {{ conn|qtIdent(data.schema, data.name) }}
+ OWNER TO {{ conn|qtIdent(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/materialized_view/ppas/9.3_plus/sql/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/delete.sql
new file mode 100644
index 0000000..cf667fc
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/delete.sql
@@ -0,0 +1,13 @@
+{# =================== Drop/Cascade materialized 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 MATERIALIZED VIEW {{ conn|qtIdent(nspname, name) }} {% if cascade %} CASCADE {% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/get_tblspc.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/get_tblspc.sql
new file mode 100644
index 0000000..a8a482c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/get_tblspc.sql
@@ -0,0 +1,2 @@
+{# ===== Get list of tablespaces ===== #}
+select tblspc.spcname from pg_tablespace tblspc WHERE tblspc.spcname != 'pg_global'
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/grant.sql
new file mode 100644
index 0000000..6cdc8e5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/grant.sql
@@ -0,0 +1,14 @@
+{# ===== Grant Permissions to User Role on Views/Tables ==== #}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/schemas/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.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/properties.sql
new file mode 100644
index 0000000..8546ee2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/properties.sql
@@ -0,0 +1,110 @@
+{# ========================== Fetch Materialized View Properties ========================= #}
+{% if (vid and datlastsysoid) or scid %}
+SELECT
+ c.oid,
+ c.xmin,
+ c.relname AS name,
+ c.reltablespace AS spcoid,
+ c.relispopulated AS with_data,
+ (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE 'pg_default' END) as spcname,
+ c.relacl,
+ 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 %}
+ True AS autovacuum_custom,
+ True AS toast_autovaccum,
+ array_to_string(c.relacl::text[], ', ') AS acl,
+ (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 '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)) )))
+ AND (c.relkind = 'm'::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/materialized_view/ppas/9.3_plus/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/update.sql
new file mode 100644
index 0000000..df4221d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/update.sql
@@ -0,0 +1,144 @@
+{# ===================== Update View ===================#}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/schemas/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 %}
+{# ===== Rename mat view ===== #}
+{% if data.name and data.name != o_data.name %}
+ALTER MATERIALIZED VIEW {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ RENAME TO {{ conn|qtIdent(data.name) }};
+
+{% endif %}
+{# ===== Alter schema view ===== #}
+{% if data.schema and data.schema != o_data.schema %}
+ALTER MATERIALIZED VIEW {{ conn|qtIdent(o_data.schema, view_name ) }}
+ SET SCHEMA {{ conn|qtIdent(data.schema) }};
+
+{% endif %}
+{# ===== Alter Table owner ===== #}
+{% if data.owner and data.owner != o_data.owner %}
+ALTER TABLE {{ conn|qtIdent(view_schema, view_name) }}
+ OWNER TO {{ conn|qtIdent(data.owner) }};
+
+{% endif %}
+{# ===== First Drop and then create mat view ===== #}
+{% if def and def != o_data.definition.rstrip(';') %}
+{% if data.fillfactor or (data['autovacuum_enabled'] and data['vacuum_data']['changed']|length > 0) %}
+DROP MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }};
+CREATE MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }}
+WITH(
+{% if data.fillfactor %}
+ FILLFACTOR = {{ data.fillfactor }}{% if data['vacuum_data']['changed']|length > 0 %},{% endif %}{{ '\r' }}
+{% endif %}
+{% if data['vacuum_data']['changed']|length > 0 %}
+{% for field in data['vacuum_data']['changed'] %}
+ {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}{{ '\r' }}
+{% endfor %}
+{% endif %}
+)
+ AS
+{{ def }}
+{% if data.with_data %}
+ WITH DATA;
+
+{% else %}
+ WITH NO DATA;
+
+{% endif %}
+{% endif %}
+{% else %}
+{# ======= Alter Tablespace ========= #}
+{%- if data.spcoid and o_data.spcoid != data.spcoid -%}
+ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET TABLESPACE {{ data.spcoid }};
+
+{% endif %}
+{# ======= SET/RESET Fillfactor ========= #}
+{% if data.fillfactor and o_data.fillfactor != data.fillfactor %}
+ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }}
+SET(FILLFACTOR = {{ data.fillfactor }});
+
+{% elif data.fillfactor == '' %}
+ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }}
+RESET(
+ FILLFACTOR
+);
+
+{% endif %}
+{# ===== Check for with_data property ===== #}
+{% if data.with_data is defined and o_data.with_data|lower != data.with_data|lower %}
+REFRESH MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }} WITH{{ ' NO' if data.with_data|lower == 'false' else '' }} DATA;
+
+{% endif %}
+{# ===== Check for Table tab properties ===== #}
+{% if data.autovacuum_custom is defined and data.autovacuum_custom|lower == 'false' %}
+ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }}
+RESET(
+ autovacuum_vacuum_threshold,
+ autovacuum_analyze_threshold,
+ autovacuum_vacuum_scale_factor,
+ autovacuum_analyze_scale_factor,
+ autovacuum_vacuum_cost_delay,
+ autovacuum_vacuum_cost_limit,
+ autovacuum_freeze_min_age,
+ autovacuum_freeze_max_age,
+ autovacuum_freeze_table_age
+);
+
+{% elif(data['vacuum_data']['changed']|length > 0) %}
+ALTER MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }} SET(
+{% for field in data['vacuum_data']['changed'] %}
+{% if field.value != None %}
+ {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}{{ '\r' }}
+{% endif %}
+{% endfor %}
+);
+
+{% endif %}{# ===== End check for custom autovaccum ===== #}
+{% endif %}{# ===== End block for check data definition ===== #}
+{%- 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.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{%- endfor %}
+{% endif %}
+{% if 'added' in data.datacl %}
+{% for priv in data.datacl.added %}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% 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/materialized_view/ppas/9.3_plus/sql/vacuum_settings.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/vacuum_settings.sql
new file mode 100644
index 0000000..e60598c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/vacuum_settings.sql
@@ -0,0 +1,2 @@
+{# ============= Fetch list of default values for autovacuum parameters =============== #}
+SELECT name, setting::numeric AS setting FROM pg_settings WHERE name IN({{ columns }}) ORDER BY name
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/view_id.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/view_id.sql
new file mode 100644
index 0000000..d192a80
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/sql/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/materialized_view/ppas/9.3_plus/vacuum_fields.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/vacuum_fields.json
new file mode 100644
index 0000000..7f8fb28
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/materialized_view/ppas/9.3_plus/vacuum_fields.json
@@ -0,0 +1,25 @@
+{# ===== Define name, label and column type of vacuum settings ===== #}
+{
+ "table":
+ {
+ "autovacuum_vacuum_threshold": ["autovacuum_vacuum_threshold", "VACCUM base threshold", "integer"],
+ "autovacuum_analyze_threshold": ["autovacuum_analyze_threshold", "ANALYZE base threshold", "integer"],
+ "autovacuum_vacuum_scale_factor": ["autovacuum_vacuum_scale_factor", "VACCUM scale factor", "number"],
+ "autovacuum_analyze_scale_factor": ["autovacuum_analyze_scale_factor", "ANALYZE scale factor", "number"],
+ "autovacuum_vacuum_cost_delay": ["autovacuum_vacuum_cost_delay", "VACCUM cost delay", "integer"],
+ "autovacuum_vacuum_cost_limit": ["autovacuum_vacuum_cost_limit", "VACCUM cost limit", "integer"],
+ "autovacuum_freeze_max_age": ["autovacuum_freeze_max_age", "FREEZE maximum age", "integer"],
+ "vacuum_freeze_min_age": ["autovacuum_freeze_min_age", "FREEZE minimum age", "integer"],
+ "vacuum_freeze_table_age": ["autovacuum_freeze_table_age", "FREEZE table age", "integer"]
+ },
+ "toast":
+ {
+ "autovacuum_vacuum_threshold": ["autovacuum_vacuum_threshold", "VACCUM base threshold", "integer"],
+ "autovacuum_vacuum_scale_factor": ["autovacuum_vacuum_scale_factor", "VACCUM scale factor", "number"],
+ "autovacuum_vacuum_cost_delay": ["autovacuum_vacuum_cost_delay", "VACCUM cost delay", "integer"],
+ "autovacuum_vacuum_cost_limit": ["autovacuum_vacuum_cost_limit", "VACCUM cost limit", "integer"],
+ "autovacuum_freeze_max_age": ["autovacuum_freeze_max_age", "FREEZE maximum age", "integer"],
+ "vacuum_freeze_min_age": ["autovacuum_freeze_min_age", "FREEZE minimum age", "integer"],
+ "vacuum_freeze_table_age": ["autovacuum_freeze_table_age", "FREEZE table age", "integer"]
+ }
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/css/view.css b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/css/view.css
new file mode 100644
index 0000000..89eae4e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/css/view.css
@@ -0,0 +1,12 @@
+.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;
+}
+
+.sql_field_height_140 {
+ height: 140px;
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/js/view.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/js/view.js
new file mode 100644
index 0000000..2e92154
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/js/view.js
@@ -0,0 +1,304 @@
+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() {
+
+ // Clear errorModel before validate
+ this.handler.errorModel.clear();
+
+ 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, mode: ['properties']
+ },
+ {
+ 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']
+ },
+ {
+ id: 'security_barrier', label:'{{ _('Security Barrier') }}', cell: 'string',
+ type: 'switch', min_version: '90200', mode: ['create', 'edit'],
+ group: 'Definition'
+ },
+ {
+ id: 'check_option', label:'{{ _('Check Options') }}',
+ control: 'select2', group: 'Definition', type: 'text',
+ min_version: '90400', mode:['properties', '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: ['create', 'edit'], group: 'Definition',
+ control: Backform.SqlFieldControl, extraClasses:['sql_field_height_140']
+ },
+ {
+ id: 'acl', label: '{{ _("ACL") }}',
+ mode: ['properties'], type: 'text'
+ },
+
+ // Add Privilege Control
+ {
+ id: 'datacl', label: '{{ _('Privileges') }}',
+ model: pgAdmin.Browser.Node.PrivilegeRoleModel.extend(
+ {privileges: ['a', 'r', 'w', 'd', 'D', 'x', 't']}), uniqueCol : ['grantee'],
+ editable: false, type: 'collection', group: '{{ _('Security') }}',
+ mode: ['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/view/pg/9.1_plus/sql/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.1_plus/sql/acl.sql
new file mode 100644
index 0000000..5850639
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.1_plus/sql/acl.sql
@@ -0,0 +1,51 @@
+{# ============================ 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'
+ WHEN 'TRUNCATE' THEN 'D'
+ 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/view/pg/9.1_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.1_plus/sql/create.sql
new file mode 100644
index 0000000..d54fff9
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.1_plus/sql/create.sql
@@ -0,0 +1,21 @@
+{#============================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) }}
+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/view/pg/9.1_plus/sql/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.1_plus/sql/delete.sql
new file mode 100644
index 0000000..e50beec
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.1_plus/sql/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 {{nspname}}.{{ conn|qtIdent(name) }} {% if cascade %} CASCADE {% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.1_plus/sql/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.1_plus/sql/grant.sql
new file mode 100644
index 0000000..ce94ecc
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.1_plus/sql/grant.sql
@@ -0,0 +1,14 @@
+{# ===== Grant Permissions to User Role on Views/Tables ===== #}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/schemas/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.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.1_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.1_plus/sql/properties.sql
new file mode 100644
index 0000000..ce1d801
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.1_plus/sql/properties.sql
@@ -0,0 +1,69 @@
+{% if (vid and datlastsysoid) or scid %}
+SELECT
+ c.oid,
+ c.xmin,
+ (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE 'pg_default' END) as spcname,
+ c.relname AS name,
+ nsp.nspname AS schema,
+ description AS comment,
+ c.reltablespace AS spcoid,
+ pg_get_userbyid(c.relowner) AS owner,
+ pg_get_viewdef(c.oid, true) AS definition,
+ array_to_string(c.relacl::text[], ', ') AS acl,
+ {#=============Checks if it is system view================#}
+ {% if vid and datlastsysoid %}
+ CASE WHEN {{vid}} <= {{datlastsysoid}} THEN True ELSE False END AS system_view,
+ {% endif %}
+ (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))
+ ))
+ ) AND (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/view/pg/9.1_plus/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.1_plus/sql/update.sql
new file mode 100644
index 0000000..2f7f82e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.1_plus/sql/update.sql
@@ -0,0 +1,66 @@
+{# ============================ Update View ========================= #}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/schemas/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 {{ conn|qtIdent(data.schema) }};
+{% endif %}
+{% if data.owner and data.owner != o_data.owner %}
+ALTER TABLE {{ conn|qtIdent(view_schema, view_name) }}
+ OWNER TO {{ conn|qtIdent(data.owner) }};
+{% endif %}
+{% if def and def != o_data.definition.rstrip(';') %}
+CREATE OR REPLACE VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ AS{{ def }};
+{% 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.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.datacl %}
+{% for priv in data.datacl.changed %}
+{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.datacl %}
+{% for priv in data.datacl.added %}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% 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/view/pg/9.1_plus/sql/view_id.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.1_plus/sql/view_id.sql
new file mode 100644
index 0000000..6dff04e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.1_plus/sql/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/view/pg/9.2_plus/sql/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.2_plus/sql/acl.sql
new file mode 100644
index 0000000..5f2d0fb
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.2_plus/sql/acl.sql
@@ -0,0 +1,52 @@
+{# ============================ 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'
+ WHEN 'TRUNCATE' THEN 'D'
+ 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/view/pg/9.2_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.2_plus/sql/create.sql
new file mode 100644
index 0000000..178ba64
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.2_plus/sql/create.sql
@@ -0,0 +1,22 @@
+{#============================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.security_barrier) %}
+WITH ({% if data.security_barrier %}security_barrier={{ data.security_barrier|lower }}{% 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/view/pg/9.2_plus/sql/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.2_plus/sql/delete.sql
new file mode 100644
index 0000000..e50beec
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.2_plus/sql/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 {{nspname}}.{{ conn|qtIdent(name) }} {% if cascade %} CASCADE {% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.2_plus/sql/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.2_plus/sql/grant.sql
new file mode 100644
index 0000000..ce94ecc
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.2_plus/sql/grant.sql
@@ -0,0 +1,14 @@
+{# ===== Grant Permissions to User Role on Views/Tables ===== #}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/schemas/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.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.2_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.2_plus/sql/properties.sql
new file mode 100644
index 0000000..eebe0f5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.2_plus/sql/properties.sql
@@ -0,0 +1,70 @@
+{% if (vid and datlastsysoid) or scid %}
+SELECT
+ c.oid,
+ c.xmin,
+ c.relname AS name,
+ c.reltablespace AS spcoid,
+ (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE 'pg_default' END) as spcname,
+ pg_get_userbyid(c.relowner) AS owner,
+ description As comment,
+ pg_get_viewdef(c.oid, true) AS definition,
+ nsp.nspname AS schema,
+ array_to_string(c.relacl::text[], ', ') AS acl,
+ {#=============Checks if it is system view================#}
+ {% if vid and datlastsysoid %}
+ CASE WHEN {{vid}} <= {{datlastsysoid}} THEN True ELSE False END AS system_view,
+ {% endif %}
+(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)
+ ) ))
+ ) AND (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/view/pg/9.2_plus/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.2_plus/sql/update.sql
new file mode 100644
index 0000000..c37d4ba
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.2_plus/sql/update.sql
@@ -0,0 +1,74 @@
+{# ============================ Update View ========================= #}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/schemas/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 {{ conn|qtIdent(data.schema) }};
+{% endif %}
+{% if data.owner and data.owner != o_data.owner %}
+ALTER TABLE {{ conn|qtIdent(view_schema, view_name) }}
+ OWNER TO {{ conn|qtIdent(data.owner) }};
+{% endif %}
+{% if def and def != o_data.definition.rstrip(';') %}
+CREATE OR REPLACE VIEW {{ conn|qtIdent(view_schema, view_name) }}
+{% if (data.security_barrier and data.security_barrier != o_data.security_barrier) %}
+ WITH ({% if data.security_barrier %}security_barrier={{ data.security_barrier|lower }}{% endif %}) {% endif %}
+ AS{{ def }};
+{% else %}
+{% if (data.security_barrier is defined and data.security_barrier|lower != o_data.security_barrier|lower) %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET (security_barrier={{ data.security_barrier|lower }});
+{% 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.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.datacl %}
+{% for priv in data.datacl.changed %}
+{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.datacl %}
+{% for priv in data.datacl.added %}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% 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/view/pg/9.2_plus/sql/view_id.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.2_plus/sql/view_id.sql
new file mode 100644
index 0000000..6dff04e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.2_plus/sql/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/view/pg/9.3_plus/sql/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.3_plus/sql/acl.sql
new file mode 100644
index 0000000..84300cf
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.3_plus/sql/acl.sql
@@ -0,0 +1,38 @@
+{# ============================ 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'
+ WHEN 'TRUNCATE' THEN 'D'
+ 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/view/pg/9.3_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.3_plus/sql/create.sql
new file mode 100644
index 0000000..178ba64
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.3_plus/sql/create.sql
@@ -0,0 +1,22 @@
+{#============================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.security_barrier) %}
+WITH ({% if data.security_barrier %}security_barrier={{ data.security_barrier|lower }}{% 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/view/pg/9.3_plus/sql/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.3_plus/sql/delete.sql
new file mode 100644
index 0000000..e50beec
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.3_plus/sql/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 {{nspname}}.{{ conn|qtIdent(name) }} {% if cascade %} CASCADE {% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.3_plus/sql/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.3_plus/sql/grant.sql
new file mode 100644
index 0000000..ce94ecc
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.3_plus/sql/grant.sql
@@ -0,0 +1,14 @@
+{# ===== Grant Permissions to User Role on Views/Tables ===== #}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/schemas/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.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.3_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.3_plus/sql/properties.sql
new file mode 100644
index 0000000..5d3d1e4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.3_plus/sql/properties.sql
@@ -0,0 +1,74 @@
+{% if (vid and datlastsysoid) or scid %}
+SELECT
+ c.oid,
+ c.xmin,
+ c.relkind,
+ description,
+ (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE 'pg_default' END) as spcname,
+ c.relname AS name,
+ nsp.nspname AS schema,
+ c.reltablespace AS spcoid,
+ c.relispopulated AS ispopulated,
+ pg_get_userbyid(c.relowner) AS owner,
+ array_to_string(c.relacl::text[], ', ') AS acl,
+ 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 %}
+(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)
+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)) )))
+ AND (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/view/pg/9.3_plus/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.3_plus/sql/update.sql
new file mode 100644
index 0000000..02d8f37
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.3_plus/sql/update.sql
@@ -0,0 +1,75 @@
+{# ============================ Update View ========================= #}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/schemas/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 {{ conn|qtIdent(data.schema) }};
+{% endif %}
+{% if data.owner and data.owner != o_data.owner %}
+ALTER TABLE {{ conn|qtIdent(view_schema, view_name) }}
+ OWNER TO {{ conn|qtIdent(data.owner) }};
+{% endif %}
+{% if def and def != o_data.definition.rstrip(';') %}
+CREATE OR REPLACE VIEW {{ conn|qtIdent(view_schema, view_name) }}
+{% if (data.security_barrier and data.security_barrier != o_data.security_barrier) %}
+ WITH ({% if data.security_barrier %}security_barrier={{ data.security_barrier|lower }}{% endif %})
+{% endif %}
+ AS{{ def }};
+{% else %}
+{% if (data.security_barrier is defined and data.security_barrier|lower != o_data.security_barrier|lower) %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET (security_barrier={{ data.security_barrier|lower }});
+{% 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.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.datacl %}
+{% for priv in data.datacl.changed %}
+{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.datacl %}
+{% for priv in data.datacl.added %}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% 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/view/pg/9.3_plus/sql/view_id.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.3_plus/sql/view_id.sql
new file mode 100644
index 0000000..6dff04e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.3_plus/sql/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/view/pg/9.4_plus/sql/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.4_plus/sql/acl.sql
new file mode 100644
index 0000000..7d791f5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.4_plus/sql/acl.sql
@@ -0,0 +1,39 @@
+{# ============================ 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'
+ WHEN 'TRUNCATE' THEN 'D'
+ 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/view/pg/9.4_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.4_plus/sql/create.sql
new file mode 100644
index 0000000..191090d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.4_plus/sql/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 }}
+{% if data.security_barrier %} security_barrier={{ data.security_barrier|lower }}{% endif %}){% endif %} AS
+{{ data.definition.rstrip(';') }};
+{% if data.owner and data.m_view is undefined %}
+
+ALTER TABLE {{ conn|qtIdent(data.schema, data.name) }}
+ OWNER TO {{ conn|qtIdent(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/view/pg/9.4_plus/sql/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.4_plus/sql/delete.sql
new file mode 100644
index 0000000..e50beec
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.4_plus/sql/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 {{nspname}}.{{ conn|qtIdent(name) }} {% if cascade %} CASCADE {% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.4_plus/sql/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.4_plus/sql/grant.sql
new file mode 100644
index 0000000..ce94ecc
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.4_plus/sql/grant.sql
@@ -0,0 +1,14 @@
+{# ===== Grant Permissions to User Role on Views/Tables ===== #}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/schemas/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.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.4_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.4_plus/sql/properties.sql
new file mode 100644
index 0000000..c7380d8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.4_plus/sql/properties.sql
@@ -0,0 +1,77 @@
+{# ========================== Fetch View Properties ========================= #}
+{% if (vid and datlastsysoid) or scid %}
+SELECT
+ c.oid,
+ c.xmin,
+ c.relkind,
+ description,
+ (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE 'pg_default' END) as spcname,
+ c.relname AS name,
+ c.reltablespace AS spcoid,
+ nsp.nspname AS schema,
+ c.relispopulated AS ispopulated,
+ pg_get_userbyid(c.relowner) AS owner,
+ array_to_string(c.relacl::text[], ', ') AS acl,
+ 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 %}
+(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 'check_option=([a-z]*)') AS check_option,
+ 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)
+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)) )))
+ AND (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/view/pg/9.4_plus/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.4_plus/sql/update.sql
new file mode 100644
index 0000000..56183fd
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.4_plus/sql/update.sql
@@ -0,0 +1,82 @@
+{# ============================ Update View ========================= #}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/schemas/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 {{ conn|qtIdent(data.schema) }};
+{% endif %}
+{% if data.owner and data.owner != o_data.owner %}
+ALTER TABLE {{ conn|qtIdent(view_schema, view_name) }}
+ OWNER TO {{ conn|qtIdent(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|lower }}{% endif %})
+{% endif %}
+ AS{{ def }};
+{% else %}
+{% if (data.security_barrier is defined and data.security_barrier|lower != o_data.security_barrier|lower) %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET (security_barrier={{ data.security_barrier|lower }});
+{% 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.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.datacl %}
+{% for priv in data.datacl.changed %}
+{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.datacl %}
+{% for priv in data.datacl.added %}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% 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/view/pg/9.4_plus/sql/view_id.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.4_plus/sql/view_id.sql
new file mode 100644
index 0000000..ef27594
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/pg/9.4_plus/sql/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/view/ppas/9.1_plus/sql/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/ppas/9.1_plus/sql/acl.sql
new file mode 100644
index 0000000..7d791f5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/ppas/9.1_plus/sql/acl.sql
@@ -0,0 +1,39 @@
+{# ============================ 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'
+ WHEN 'TRUNCATE' THEN 'D'
+ 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/view/ppas/9.1_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/ppas/9.1_plus/sql/create.sql
new file mode 100644
index 0000000..191090d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/ppas/9.1_plus/sql/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 }}
+{% if data.security_barrier %} security_barrier={{ data.security_barrier|lower }}{% endif %}){% endif %} AS
+{{ data.definition.rstrip(';') }};
+{% if data.owner and data.m_view is undefined %}
+
+ALTER TABLE {{ conn|qtIdent(data.schema, data.name) }}
+ OWNER TO {{ conn|qtIdent(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/view/ppas/9.1_plus/sql/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/ppas/9.1_plus/sql/delete.sql
new file mode 100644
index 0000000..e50beec
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/ppas/9.1_plus/sql/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 {{nspname}}.{{ conn|qtIdent(name) }} {% if cascade %} CASCADE {% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/ppas/9.1_plus/sql/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/ppas/9.1_plus/sql/grant.sql
new file mode 100644
index 0000000..ce94ecc
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/ppas/9.1_plus/sql/grant.sql
@@ -0,0 +1,14 @@
+{# ===== Grant Permissions to User Role on Views/Tables ===== #}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/schemas/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.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/ppas/9.1_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/ppas/9.1_plus/sql/properties.sql
new file mode 100644
index 0000000..2af1252
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/ppas/9.1_plus/sql/properties.sql
@@ -0,0 +1,76 @@
+{% if (vid and datlastsysoid) or scid %}
+SELECT
+ c.oid,
+ c.xmin,
+ c.relkind,
+ description,
+ (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE 'pg_default' END) as spcname,
+ c.relname AS name,
+ c.reltablespace AS spcoid,
+ nsp.nspname AS schema,
+ c.relispopulated AS ispopulated,
+ pg_get_userbyid(c.relowner) AS owner,
+ array_to_string(c.relacl::text[], ', ') AS acl,
+ 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 %}
+(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 'check_option=([a-z]*)') AS check_option,
+ 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)
+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)) )))
+ AND (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/view/ppas/9.1_plus/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/ppas/9.1_plus/sql/update.sql
new file mode 100644
index 0000000..56183fd
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/ppas/9.1_plus/sql/update.sql
@@ -0,0 +1,82 @@
+{# ============================ Update View ========================= #}
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/schemas/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 {{ conn|qtIdent(data.schema) }};
+{% endif %}
+{% if data.owner and data.owner != o_data.owner %}
+ALTER TABLE {{ conn|qtIdent(view_schema, view_name) }}
+ OWNER TO {{ conn|qtIdent(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|lower }}{% endif %})
+{% endif %}
+ AS{{ def }};
+{% else %}
+{% if (data.security_barrier is defined and data.security_barrier|lower != o_data.security_barrier|lower) %}
+ALTER VIEW {{ conn|qtIdent(view_schema, view_name) }}
+ SET (security_barrier={{ data.security_barrier|lower }});
+{% 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.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.datacl %}
+{% for priv in data.datacl.changed %}
+{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.datacl %}
+{% for priv in data.datacl.added %}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% 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/view/ppas/9.1_plus/sql/view_id.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/ppas/9.1_plus/sql/view_id.sql
new file mode 100644
index 0000000..ef27594
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/view/ppas/9.1_plus/sql/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: <CAM5-9D_NCKo=J=Y7O2t-jKiW=C_bUjqeN4JE68kC6wJy3Q4fHA@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