public inbox for [email protected]
help / color / mirror / Atom feedFrom: Surinder Kumar <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: Re: [pgAdmin4][Patch]: Tables Sub nodes(Columns, Indexes, rules & triggers) Patch
Date: Thu, 7 Apr 2016 14:24:14 +0530
Message-ID: <CAM5-9D_JXhPDZh63hmSRfYmM_6X4df3iD2Pu9NU_nJ1w8ymKMw@mail.gmail.com> (raw)
In-Reply-To: <CAM5-9D8c+jmo_Vw0s4knNctz_YSTZjGsbu6utVF0CyQHKvge8A@mail.gmail.com>
References: <CAM5-9D8c+jmo_Vw0s4knNctz_YSTZjGsbu6utVF0CyQHKvge8A@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
Hi,
PFA updated patch with couple of changes which are following:
1. Couldn't create new Rule because model's field *view* is passed
undefined. Now it is fixed.
2. Removed trailing whitespaces from code.
Please review the patch.
On Wed, Mar 23, 2016 at 9:14 PM, Surinder Kumar <
[email protected]> wrote:
> Hi,
>
> This patch contains following nodes:
> 1. Columns
> 2. Indexes
> 3 Rule
> 4. Trigger
>
> I am using murtuza's patch with little changes to show above nodes under *view
> and materialized view.*
>
> This patch is only for *Tables sub node*. @murtuaza is working on table
> node which is not completed.
>
> *Note*: Please apply patch for utility function "Added Utility functions
> for triggers node and rules node" first then apply this patch.
>
> Please review it.
>
>
> Thanks,
> Surinder Kumar
>
>
>
>
>
>
--
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] tables_subnodes_v1.patch (258.5K, 3-tables_subnodes_v1.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py
new file mode 100644
index 0000000..56cc7f2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py
@@ -0,0 +1,8 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/__init__.py
new file mode 100644
index 0000000..09f0e64
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/__init__.py
@@ -0,0 +1,949 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+""" Implements Column Node """
+
+from flask import render_template, make_response, request, jsonify
+from flask.ext.babel import gettext
+from pgadmin.utils.ajax import make_json_response, \
+ make_response as ajax_response, internal_server_error
+from pgadmin.browser.utils import PGChildNodeView
+from pgadmin.browser.collection import CollectionNodeModule
+import pgadmin.browser.server_groups.servers.databases as database
+from pgadmin.utils.ajax import precondition_required
+from pgadmin.utils.driver import get_driver
+from config import PG_DEFAULT_DRIVER
+from pgadmin.browser.server_groups.servers.utils import parse_priv_from_db, \
+ parse_priv_to_db
+from functools import wraps
+import json
+
+
+class ColumnsModule(CollectionNodeModule):
+ """
+ class ColumnsModule(CollectionNodeModule)
+
+ A module class for Column node derived from CollectionNodeModule.
+
+ Methods:
+ -------
+ * __init__(*args, **kwargs)
+ - Method is used to initialize the Column and it's base module.
+
+ * get_nodes(gid, sid, did, scid, tid)
+ - 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 schema, when any of the server node is
+ initialized.
+ """
+
+ NODE_TYPE = 'column'
+ COLLECTION_LABEL = gettext("Columns")
+
+ def __init__(self, *args, **kwargs):
+ """
+ Method is used to initialize the ColumnModule and it's base module.
+
+ Args:
+ *args:
+ **kwargs:
+ """
+ self.min_ver = None
+ self.max_ver = None
+ super(ColumnsModule, self).__init__(*args, **kwargs)
+
+ def get_nodes(self, gid, sid, did, scid, **kwargs):
+ """
+ Generate the collection node
+ # TODO::
+ We can have following arguments for different type of parents.
+ i.e.
+ tid - for tables
+ vid - for materialized views
+ """
+ assert('tid' in kwargs or 'vid' in kwargs)
+ yield self.generate_browser_collection_node(
+ kwargs['tid'] if 'tid' in kwargs else kwargs['vid']
+ )
+
+ @property
+ def script_load(self):
+ """
+ Load the module script for server, when any of the server-group node is
+ initialized.
+ """
+ return database.DatabaseModule.NODE_TYPE
+
+ @property
+ def node_inode(self):
+ """
+ Load the module node as a leaf node
+ """
+ return False
+
+
+blueprint = ColumnsModule(__name__)
+
+
+class ColumnsView(PGChildNodeView):
+ """
+ This class is responsible for generating routes for Column node
+
+ Methods:
+ -------
+ * __init__(**kwargs)
+ - Method is used to initialize the ColumnView and it's base view.
+
+ * module_js()
+ - This property defines (if javascript) exists for this node.
+ Override this property for your own logic
+
+ * check_precondition()
+ - This function will behave as a decorator which will checks
+ database connection before running view, it will also attaches
+ manager,conn & template_path properties to self
+
+ * list()
+ - This function is used to list all the Column nodes within that
+ collection.
+
+ * nodes()
+ - This function will used to create all the child node within that
+ collection, Here it will create all the Column node.
+
+ * properties(gid, sid, did, scid, tid, clid)
+ - This function will show the properties of the selected Column node
+
+ * create(gid, sid, did, scid, tid)
+ - This function will create the new Column object
+
+ * update(gid, sid, did, scid, tid, clid)
+ - This function will update the data for the selected Column node
+
+ * delete(self, gid, sid, scid, tid, clid):
+ - This function will drop the Column object
+
+ * msql(gid, sid, did, scid, tid, clid)
+ - This function is used to return modified SQL for the selected
+ Column node
+
+ * get_sql(data, scid, tid)
+ - 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
+ selected Column node.
+
+ * dependency(gid, sid, did, scid):
+ - This function will generate dependency list show it in dependency
+ pane for the selected Column node.
+
+ * dependent(gid, sid, did, scid):
+ - This function will generate dependent list to show it in dependent
+ pane for the selected Column node.
+ """
+
+ node_type = blueprint.node_type
+
+ parent_ids = [
+ {'type': 'int', 'id': 'gid'},
+ {'type': 'int', 'id': 'sid'},
+ {'type': 'int', 'id': 'did'},
+ {'type': 'int', 'id': 'scid'},
+ {'type': 'int', 'id': 'tid'}
+ ]
+ ids = [
+ # Here we specify type as any because table
+ # are also has '-' in them if they are system table
+ {'type': 'string', 'id': 'clid'}
+ ]
+
+ operations = dict({
+ 'obj': [
+ {'get': 'properties', 'delete': 'delete', 'put': 'update'},
+ {'get': 'list', 'post': 'create'}
+ ],
+ 'children': [{'get': 'children'}],
+ 'nodes': [{'get': 'node'}, {'get': 'nodes'}],
+ 'sql': [{'get': 'sql'}],
+ 'msql': [{'get': 'msql'}, {'get': 'msql'}],
+ 'stats': [{'get': 'statistics'}],
+ 'dependency': [{'get': 'dependencies'}],
+ 'dependent': [{'get': 'dependents'}],
+ 'module.js': [{}, {}, {'get': 'module_js'}],
+ 'get_types': [{'get': 'get_types'}, {'get': 'get_types'}],
+ 'get_collations': [{'get': 'get_collations'}, {'get': 'get_collations'}]
+ })
+
+ def check_precondition(f):
+ """
+ This function will behave as a decorator which will checks
+ database connection before running view, it will also attaches
+ manager,conn & template_path properties to self
+ """
+ @wraps(f)
+ def wrap(*args, **kwargs):
+ # Here args[0] will hold self & kwargs will hold gid,sid,did
+ self = args[0]
+ self.manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(
+ kwargs['sid']
+ )
+ self.conn = self.manager.connection(did=kwargs['did'])
+ # If DB not connected then return error to browser
+ if not self.conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+
+ ver = self.manager.version
+ # we will set template path for sql scripts
+ if ver >= 90200:
+ self.template_path = 'column/sql/9.2_plus'
+ else:
+ self.template_path = 'column/sql/9.1_plus'
+ # Allowed ACL for column 'Select/Update/Insert/References'
+ self.acl = ['a', 'r', 'w', 'x']
+
+ # We need parent's name eg table name and schema name
+ SQL = render_template("/".join([self.template_path,
+ 'get_parent.sql']),
+ tid=kwargs['tid'])
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=rset)
+
+ for row in rset['rows']:
+ self.schema = row['schema']
+ self.table = row['table']
+
+ return f(*args, **kwargs)
+
+ return wrap
+
+ @check_precondition
+ def get_collations(self, gid, sid, did, scid, tid, clid=None):
+ """
+ This function will return list of collation available via AJAX response
+ """
+ res = [{'label': '', 'value': ''}]
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_collations.sql']))
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in rset['rows']:
+ res.append(
+ {'label': row['collation'],
+ 'value': row['collation']}
+ )
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def get_types(self, gid, sid, did, scid, tid, clid=None):
+ """
+ This function will return list of types available via AJAX response
+ """
+ res = [{'label': '', 'value': ''}]
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_types.sql']))
+ status, rset = self.conn.execute_2darray(SQL)
+
+ for row in rset['rows']:
+ # Attaching properties for precession
+ # & length validation for current type
+ precision = False
+ length = False
+ min_val = 0
+ max_val = 0
+
+ # Check against PGOID for specific type
+ if row['elemoid']:
+ if row['elemoid'] in (1560, 1561, 1562, 1563, 1042, 1043,
+ 1014, 1015):
+ typeval = 'L'
+ elif row['elemoid'] in (1083, 1114, 1115, 1183, 1184, 1185,
+ 1186, 1187, 1266, 1270):
+ typeval = 'D'
+ elif row['elemoid'] in (1231, 1700):
+ typeval = 'P'
+ else:
+ typeval = ' '
+
+ # Logic to set precision & length/min/max values
+ if typeval == 'P':
+ precision = True
+
+ if precision or typeval in ('L', 'D'):
+ length = True
+ min_val = 0 if typeval == 'D' else 1
+ if precision:
+ max_val = 1000
+ elif min_val:
+ # Max of integer value
+ max_val = 2147483647
+ else:
+ max_val = 10
+
+ res.append(
+ {'label': row['typname'], 'value': row['typname'],
+ 'typval': typeval, 'precision': precision,
+ 'length': length, 'min_val': min_val, 'max_val': max_val
+ }
+ )
+ return make_json_response(
+ data=res,
+ status=200
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def list(self, gid, sid, did, scid, tid):
+ """
+ This function is used to list all the schema nodes within that collection.
+
+ Args:
+ gid: Server group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+
+ Returns:
+ JSON of available column nodes
+ """
+
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']), tid=tid,
+ show_sys_objects=self.blueprint.show_system_objects)
+ 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, tid):
+ """
+ This function will used to create all the child node within that collection.
+ Here it will create all the schema node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+
+ Returns:
+ JSON of available schema child nodes
+ """
+ res = []
+ SQL = render_template("/".join([self.template_path,
+ 'nodes.sql']), tid=tid,
+ show_sys_objects=self.blueprint.show_system_objects)
+ 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'],
+ tid,
+ row['name'],
+ icon="icon-column"
+ ))
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ def _formatter(self, scid, tid, clid, data):
+ """
+ Args:
+ scid: schema oid
+ tid: table oid
+ clid: position of column in table
+ data: dict of query result
+
+ Returns:
+ It will return formatted output of collections
+ """
+ # To check if column is primary key
+ if 'attnum' in data and 'indkey' in data:
+ # Current column
+ attnum = str(data['attnum'])
+
+ # Single/List of primary key column(s)
+ indkey = str(data['indkey'])
+
+ # We will check if column is in primary column(s)
+ if attnum in indkey:
+ data['is_pk'] = True
+ else:
+ data['is_pk'] = False
+
+ # We need to fetch inherited tables for each table
+ SQL = render_template("/".join([self.template_path,
+ 'get_inherited_tables.sql']),
+ tid=tid)
+ status, inh_res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=inh_res)
+ for row in inh_res['rows']:
+ if row['attrname'] == data['name']:
+ data['is_inherited'] = True
+ data['tbls_inherited'] = row['inhrelname']
+
+ # We need to format variables according to client js collection
+ if 'attoptions' in data and data['attoptions'] is not None:
+ spcoptions = []
+ for spcoption in data['attoptions']:
+ k, v = spcoption.split('=')
+ spcoptions.append({'name': k, 'value': v})
+
+ data['attoptions'] = spcoptions
+
+ # Need to format security labels according to client js collection
+ if 'seclabels' in data and data['seclabels'] is not None:
+ seclabels = []
+ for seclbls in data['seclabels']:
+ k, v = seclbls.split('=')
+ seclabels.append({'provider': k, 'security_label': v})
+
+ data['seclabels'] = seclabels
+
+ # We need to parse & convert ACL coming from database to json format
+ SQL = render_template("/".join([self.template_path, 'acl.sql']),
+ tid=tid, clid=clid)
+ status, acl = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=acl)
+
+ # We will set get privileges from acl sql so we don't need
+ # it from properties sql
+ data['attacl'] = []
+
+ for row in acl['rows']:
+ priv = parse_priv_from_db(row)
+ data.setdefault(row['deftype'], []).append(priv)
+
+ # we are receiving request when in edit mode
+ # we will send filtered types related to current type
+ present_type = data['cltype']
+ type_id = data['atttypid']
+
+ SQL = render_template("/".join([self.template_path,
+ 'is_referenced.sql']),
+ tid=tid, clid=clid)
+
+ status, is_reference = self.conn.execute_scalar(SQL)
+
+ edit_types_list = list()
+ # We will need present type in edit mode
+ edit_types_list.append(present_type)
+
+ if int(is_reference) == 0:
+ SQL = render_template("/".join([self.template_path,
+ 'edit_mode_types.sql']),
+ type_id=type_id)
+ status, rset = self.conn.execute_2darray(SQL)
+
+ for row in rset['rows']:
+ edit_types_list.append(row['typname'])
+ else:
+ edit_types_list.append(present_type)
+
+ data['edit_types'] = edit_types_list
+
+ return data
+
+ @check_precondition
+ def properties(self, gid, sid, did, scid, tid, clid):
+ """
+ This function will show the properties of the selected schema node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ scid: Schema ID
+ tid: Table ID
+ clid: Column ID
+
+ Returns:
+ JSON of selected schema node
+ """
+
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']), tid=tid, clid=clid
+ , show_sys_objects=self.blueprint.show_system_objects)
+
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ # Making copy of output for future use
+ data = dict(res['rows'][0])
+ data = self._formatter(scid, tid, clid, data)
+
+ return ajax_response(
+ response=data,
+ status=200
+ )
+
+ def _cltype_formatter(self, type):
+ """
+
+ Args:
+ data: Type string
+
+ Returns:
+ We need to remove [] from type and append it
+ after length/precision so we will set flag for
+ sql template
+ """
+ if '[]' in type:
+ type = type.replace('[]', '')
+ self.hasSqrBracket = True
+ else:
+ self.hasSqrBracket = False
+
+ return type
+
+ @check_precondition
+ def create(self, gid, sid, did, scid, tid):
+ """
+ This function will creates new the schema object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ """
+ data = request.form if request.form else json.loads(
+ request.data.decode()
+ )
+ required_args = {
+ 'name': 'Name',
+ 'cltype': 'Type'
+ }
+
+ 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)." %
+ required_args[arg]
+ )
+ )
+
+ # Parse privilege data coming from client according to database format
+ if 'attacl' in data:
+ data['attacl'] = parse_priv_to_db(data['attacl'], self.acl)
+
+ # Adding parent into data dict, will be using it while creating sql
+ data['schema'] = self.schema
+ data['table'] = self.table
+
+ # check type for '[]' in it
+ data['cltype'] = self._cltype_formatter(data['cltype'])
+ data['hasSqrBracket'] = self.hasSqrBracket
+
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'create.sql']),
+ data=data, conn=self.conn)
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ # we need oid to to add object in tree at browser
+ SQL = render_template("/".join([self.template_path,
+ 'get_position.sql']),
+ tid=tid, data=data)
+ status, clid = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=tid)
+
+ return jsonify(
+ node=self.blueprint.generate_browser_node(
+ clid,
+ scid,
+ data['name'],
+ icon="icon-column"
+ )
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def delete(self, gid, sid, did, scid, tid, clid):
+ """
+ This function will updates existing the schema object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ clid: Column ID
+ """
+ # We will first fetch the column name for current request
+ # so that we create template for dropping column
+ try:
+
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']), tid=tid, clid=clid
+ , show_sys_objects=self.blueprint.show_system_objects)
+
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ data = dict(res['rows'][0])
+ # We will add table & schema as well
+ data['schema'] = self.schema
+ data['table'] = self.table
+
+ SQL = render_template("/".join([self.template_path,
+ 'delete.sql']),
+ data=data, conn=self.conn)
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info=gettext("Column is dropped"),
+ data={
+ 'id': clid,
+ 'tid': tid
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def update(self, gid, sid, did, scid, tid, clid):
+ """
+ This function will updates existing the schema object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ clid: Column ID
+ """
+ data = request.form if request.form else json.loads(request.data.decode())
+
+ # Adding parent into data dict, will be using it while creating sql
+ data['schema'] = self.schema
+ data['table'] = self.table
+
+ # check type for '[]' in it
+ if 'cltype' in data:
+ data['cltype'] = self._cltype_formatter(data['cltype'])
+ data['hasSqrBracket'] = self.hasSqrBracket
+
+ try:
+ SQL = self.get_sql(scid, tid, clid, data)
+ if SQL and SQL.strip('\n') and SQL.strip(' '):
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info="Column updated",
+ data={
+ 'id': clid,
+ 'tid': tid,
+ 'scid': scid
+ }
+ )
+ else:
+ return make_json_response(
+ success=1,
+ info="Nothing to update",
+ data={
+ 'id': clid,
+ 'tid': tid,
+ 'scid': scid
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+
+ @check_precondition
+ def msql(self, gid, sid, did, scid, tid, clid=None):
+ """
+ This function will generates modified sql for schema object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ clid: Column ID (When working with existing column)
+ """
+ data = dict()
+ for k, v in request.args.items():
+ try:
+ data[k] = json.loads(v)
+ except ValueError:
+ data[k] = v
+
+ # Adding parent into data dict, will be using it while creating sql
+ data['schema'] = self.schema
+ data['table'] = self.table
+
+ # check type for '[]' in it
+ if 'cltype' in data:
+ data['cltype'] = self._cltype_formatter(data['cltype'])
+ data['hasSqrBracket'] = self.hasSqrBracket
+
+ try:
+ SQL = self.get_sql(scid, tid, clid, data)
+
+ if SQL and SQL.strip('\n') and SQL.strip(' '):
+ return make_json_response(
+ data=SQL,
+ status=200
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ def get_sql(self, scid, tid, clid, data):
+ """
+ This function will genrate sql from model data
+ """
+ if clid is not None:
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']), tid=tid, clid=clid
+ , show_sys_objects=self.blueprint.show_system_objects)
+
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ old_data = dict(res['rows'][0])
+ # We will add table & schema as well
+ old_data = self._formatter(scid, tid, clid, old_data)
+
+ # If name is not present in data then
+ # we will fetch it from old data, we also need schema & table name
+ if 'name' not in data:
+ data['name'] = old_data['name']
+
+ # Convert acl coming from client in db parsing format
+ key = 'attacl'
+ if key in data and data[key] is not None:
+ if 'added' in data[key]:
+ data[key]['added'] = parse_priv_to_db(
+ data[key]['added'], self.acl
+ )
+ if 'changed' in data[key]:
+ data[key]['changed'] = parse_priv_to_db(
+ data[key]['changed'], self.acl
+ )
+ if 'deleted' in data[key]:
+ data[key]['deleted'] = parse_priv_to_db(
+ data[key]['deleted'], self.acl
+ )
+
+ SQL = render_template(
+ "/".join([self.template_path, 'update.sql']),
+ data=data, o_data=old_data, conn=self.conn
+ )
+ else:
+ required_args = [
+ 'name',
+ 'cltype'
+ ]
+
+ for arg in required_args:
+ if arg not in data:
+ return gettext('-- incomplete definition')
+
+ # We will convert privileges coming from client required
+ # in server side format
+ if 'attacl' in data:
+ data['attacl'] = parse_priv_to_db(data['attacl'],
+ self.acl)
+ # If the request for new object which do not have did
+ SQL = render_template("/".join([self.template_path, 'create.sql']),
+ data=data, conn=self.conn)
+ return SQL
+
+ @check_precondition
+ def sql(self, gid, sid, did, scid, tid, clid):
+ """
+ This function will generates reverse engineered sql for schema object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ clid: Column ID
+ """
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']), tid=tid, clid=clid
+ , show_sys_objects=self.blueprint.show_system_objects)
+
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ data = dict(res['rows'][0])
+ # We do not want to display length as -1 in create query
+ if 'attlen' in data and data['attlen'] == -1:
+ data['attlen'] = ''
+ # Adding parent into data dict, will be using it while creating sql
+ data['schema'] = self.schema
+ data['table'] = self.table
+ # check type for '[]' in it
+ if 'cltype' in data:
+ data['cltype'] = self._cltype_formatter(data['cltype'])
+ data['hasSqrBracket'] = self.hasSqrBracket
+
+ # We will add table & schema as well
+ data = self._formatter(scid, tid, clid, data)
+
+ SQL = self.get_sql(scid, tid, None, data)
+
+ sql_header = "-- Column: {0}\n\n-- ".format(data['name'])
+ sql_header += render_template("/".join([self.template_path,
+ 'delete.sql']),
+ data=data, conn=self.conn)
+ SQL = sql_header + '\n\n' + SQL
+
+ return SQL
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def dependents(self, gid, sid, did, scid, tid, clid):
+ """
+ This function get the dependents and return ajax response
+ for the column node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ clid: Column ID
+ """
+ # Specific condition for column which we need to append
+ where = "WHERE dep.refobjid={0}::OID AND dep.refobjsubid={1}".format(
+ tid, clid
+ )
+
+ dependents_result = self.get_dependents(
+ self.conn, clid, where=where)
+
+ # Specific sql to run againt column to fetch dependents
+ SQL = render_template("/".join([self.template_path,
+ 'depend.sql']), where=where)
+
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in res['rows']:
+ ref_name = row['refname']
+ if ref_name is None:
+ continue
+
+ dep_type = ''
+ dep_str = row['deptype']
+ if dep_str == 'a':
+ dep_type = 'auto'
+ elif dep_str == 'n':
+ dep_type = 'normal'
+ elif dep_str == 'i':
+ dep_type = 'internal'
+
+ dependents_result.append({'type': 'sequence', 'name': ref_name, 'field': dep_type})
+
+ return ajax_response(
+ response=dependents_result,
+ status=200
+ )
+
+ @check_precondition
+ def dependencies(self, gid, sid, did, scid, tid, clid):
+ """
+ This function get the dependencies and return ajax response
+ for the column node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ clid: Column ID
+
+ """
+ # Specific condition for column which we need to append
+ where = "WHERE dep.objid={0}::OID AND dep.objsubid={1}".format(
+ tid, clid
+ )
+
+ dependencies_result = self.get_dependencies(
+ self.conn, clid, where=where)
+
+ return ajax_response(
+ response=dependencies_result,
+ status=200
+ )
+
+
+ColumnsView.register_node_view(blueprint)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/static/img/coll-column.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/static/img/coll-column.png
new file mode 100644
index 0000000000000000000000000000000000000000..89d758834d4176c1df2548db10b46b1f6b2e4ec5
GIT binary patch
literal 400
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbK}cz{ocE09*4`h3Ev&s(m&yL<QU
z)2C0LJ$u%^|9SG<<M-~}d-(9-vSrKOym_;3-MW`AU#?uaa_!o+>({S;_3G90=g%KJ
zc<}o5>kl73eEaro$BrEvHf-3qapU*z-`~D{TatgM6KFJJNswPKgTu2MX+REVfk$L9
zkoEv$x0Bg+Kt_S5i(`ny<=FG?VhsvBt`}W4E@ZQg__p6qm@nby;qrG1j0_I@c^(;r
zuhP)2X<D_P?dti2$vzyv(k(V*o?f?H<i?gXiRA)mhTUpwofQn*P8`|LWw=McNaXQ!
zy+q@@Yd0m1ylF3f{(An_-EV$!P4L^#_`09vh+=cW8=&2)C9V-ADTyViR>?)FK#IZ0
zz|cU~&`8(7FvQ5f%EZ{p#6;V`)XKoXVy3DbiiX_$l+3hBhz0{oum+H7D+4o#hEvl+
R*8nvzc)I$ztaD0e0s#BYr!@co
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/static/img/column.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/static/img/column.png
new file mode 100644
index 0000000000000000000000000000000000000000..bd9f81df98fe27d81ade5144d66b3b09b96123c1
GIT binary patch
literal 435
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbK}X@F0NE09*4`h3Ev&s(m&yL<QU
zy?giW-@pI#>C<P=p0)3PzHHgD=g*(Nc=6)Rn>Xv$t$X?M<;s;SSFBjEcJ12r>({?}
z_3FWc2M-@UeDvti>eZ{)tXZ>Z)20s}KD>VY`qQURpFe;8`t|F#Z{K$8*s)>5hK(CH
ze*gac?c2BS-o49eKe!ZVF=I)PUoeBivm0qZ4rhT!WHFHT0Ash4*>*risi%u$h{WaE
z^B0Ah6a-om4Rm-iuV3+X<o2rm?|w$}?!!eN_y0*dcT_ORb6x7A={6cu<^6X}kY2Ib
zZ0TMHrih1I^CUh^d%@<y)3TvOv&K6iH~-RCTdVU6czMe9*h~1l{VS1_(D3~9P6_q4
zgXi+))!6E^|9xfrB*GzI-TA)^=m6Cc*NBpo#FA92<f2p{#b9J$XrOCoq-$UpVq{=t
zVr*q%qHSPmWnf@2Q&kQ{LvDUbW?CgggMlSj14y-%ff+=@sp+9>fEpM)UHx3vIVCg!
E0NRqnZ~y=R
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/js/column.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/js/column.js
new file mode 100644
index 0000000..1b8d321
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/js/column.js
@@ -0,0 +1,419 @@
+define(
+ ['jquery', 'underscore', 'underscore.string', 'pgadmin', 'pgadmin.browser',
+ 'backform', 'alertify', 'pgadmin.browser.collection'],
+function($, _, S, pgAdmin, pgBrowser, Backform, alertify) {
+
+ if (!pgBrowser.Nodes['coll-column']) {
+ var databases = pgAdmin.Browser.Nodes['coll-column'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'column',
+ label: '{{ _('Columns') }}',
+ type: 'coll-column',
+ columns: ['name', 'atttypid', 'description']
+ });
+ };
+
+ // This Node model will be used for variable control for column
+ var VariablesModel = Backform.VariablesModel = pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ name: null,
+ value: null
+ },
+ schema: [{
+ id: 'name', label: '{{ _('Name') }}', cell: 'select2',
+ type: 'text', disabled: false, node: 'column',
+ options: [['n_distinct', 'n_distinct'],
+ ['n_distinct_inherited','n_distinct_inherited']],
+ select2: {placeholder: "Select variable"},
+ cellHeaderClasses:'width_percent_50'
+ },{
+ id: 'value', label: '{{ _('Value') }}',
+ type: 'text', disabled: false,
+ cellHeaderClasses:'width_percent_50'
+ }],
+ validate: function() {
+ var err = {},
+ errmsg = null;
+
+ if (_.isUndefined(this.get('value')) ||
+ _.isNull(this.get('value')) ||
+ String(this.get('value')).replace(/^\s+|\s+$/g, '') == '') {
+ errmsg = '{{ _('Please provide input for variable.')}}';
+ this.errorModel.set('value', errmsg);
+ return errmsg;
+ } else {
+ this.errorModel.unset('value');
+ }
+ return null;
+ }
+ });
+
+
+ if (!pgBrowser.Nodes['column']) {
+ pgAdmin.Browser.Nodes['column'] = pgAdmin.Browser.Node.extend({
+ parent_type: ['table', 'view', 'materialized_view'],
+ collection_type: ['coll-table'],
+ type: 'column',
+ label: '{{ _('Column') }}',
+ hasSQL: true,
+ canDrop: function(itemData, item, data){
+ if (pgBrowser.Nodes['schema'].canChildDrop.apply(this, [itemData, item, data])) {
+ var t = pgBrowser.tree, i = item, d = itemData, parents = [];
+ // To iterate over tree to check parent node
+ while (i) {
+ // If it is schema then allow user to c reate table
+ if (_.indexOf(['view', 'materialized_view'], d._type) > -1) {
+ return false;
+ }
+ parents.push(d._type);
+ i = t.hasParent(i) ? t.parent(i) : null;
+ d = i ? t.itemData(i) : null;
+ }
+ }
+ else {
+ return true;
+ }
+ },
+ hasDepends: true,
+ Init: function() {
+ /* Avoid mulitple registration of menus */
+ if (this.initialized)
+ return;
+
+ this.initialized = true;
+
+ pgBrowser.add_menus([{
+ name: 'create_column_on_coll', node: 'coll-column', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Column...') }}',
+ icon: 'wcTabIcon icon-column', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_column', node: 'column', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Column...') }}',
+ icon: 'wcTabIcon icon-column', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_column_onTable', node: 'table', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Column...') }}',
+ icon: 'wcTabIcon icon-column', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ }
+ ]);
+ },
+ model: pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ attname: undefined,
+ attowner: undefined,
+ atttypid: undefined,
+ attnum: undefined,
+ cltype: undefined,
+ collspcname: undefined,
+ attacl: undefined,
+ description: undefined,
+ parent_tbl: undefined,
+ min_val: undefined,
+ max_val: undefined,
+ edit_types: undefined,
+ primary_key: false
+ },
+ schema: [{
+ // Need to show this field only when creating new table [in SubNode control]
+ id: 'primary_key', label: '{{ _('Primary Key?') }}', cell: 'switch',
+ type: 'switch', disabled: 'inSchemaWithColumnCheck',
+ editable: true,
+ cellHeaderClasses:'width_percent_10',
+ visible: function(m) {
+ return _.isUndefined(m.node_info['table']);
+ }
+ },{
+ id: 'name', label: '{{ _('Name') }}', cell: 'string',
+ type: 'text', disabled: 'inSchemaWithColumnCheck',
+ cellHeaderClasses:'width_percent_30'
+ },{
+ id: 'attnum', label:'{{ _('Position') }}', cell: 'string',
+ type: 'text', disabled: 'inSchema', mode: ['properties']
+ },{
+ id: 'cltype', label:'{{ _('Data type') }}', cell: 'string',
+ type: 'text', disabled: 'inSchemaWithColumnCheck',
+ control: 'node-ajax-options', url: 'get_types', node: 'table',
+ cellHeaderClasses:'width_percent_30',
+ select2: { allowClear: false }, group: '{{ _('Definition') }}',
+ transform: function(data) {
+ /* We need different data in create mode & in edit mode
+ * if we are in create mode then return data as it is
+ * if we are in edit mode then we need to filter data
+ */
+ this.model.datatypes = data;
+ var edit_types = this.model.get('edit_types'),
+ result = [];
+ if(this.model.isNew()) {
+ return data;
+ } else {
+ //edit mode
+ _.each(data, function(t) {
+ if (_.indexOf(edit_types, t.value) != -1) {
+ result.push(t);
+ }
+ });
+ return result;
+ }
+ }
+ },{
+ // Need to show this field only when creating new table [in SubNode control]
+ id: 'inheritedfrom', label: '{{ _('Inherited from table') }}',
+ type: 'text', disabled: true, editable: false,
+ cellHeaderClasses:'width_percent_30',
+ visible: function(m) {
+ return _.isUndefined(m.node_info['table']);
+ }
+ },{
+ id: 'attlen', label:'{{ _('Length') }}', cell: 'string',
+ deps: ['cltype'], type: 'int', group: '{{ _('Definition') }}',
+ disabled: function(m) {
+ var of_type = m.get('cltype'),
+ flag = true;
+ _.each(m.datatypes, function(o) {
+ if ( of_type == o.value ) {
+ if(o.length)
+ {
+ m.set('min_val', o.min_val, {silent: true});
+ m.set('max_val', o.max_val, {silent: true});
+ flag = false;
+ }
+ }
+ });
+ return flag;
+ }
+ },{
+ id: 'attprecision', label:'{{ _('Precision') }}', cell: 'string',
+ deps: ['cltype'], type: 'int', group: '{{ _('Definition') }}',
+ disabled: function(m) {
+ var of_type = m.get('cltype'),
+ flag = true;
+ _.each(m.datatypes, function(o) {
+ if ( of_type == o.value ) {
+ if(o.precision)
+ {
+ m.set('min_val', o.min_val, {silent: true});
+ m.set('max_val', o.max_val, {silent: true});
+ flag = false;
+ }
+ }
+ });
+ return flag;
+ }
+ },{
+ id: 'collspcname', label:'{{ _('Collation') }}', cell: 'string',
+ type: 'text', disabled: 'inSchemaWithModelCheck',
+ control: 'node-ajax-options', url: 'get_collations',
+ group: '{{ _('Definition') }}', node: 'collation'
+ },{
+ id: 'defval', label:'{{ _('Default Value') }}', cell: 'string',
+ type: 'text', disabled: 'inSchemaWithColumnCheck',
+ group: '{{ _('Definition') }}'
+ },{
+ id: 'attnotnull', label:'{{ _('Not NULL') }}', cell: 'string',
+ type: 'switch', disabled: 'inSchemaWithColumnCheck',
+ group: '{{ _('Definition') }}'
+ },{
+ id: 'attstatterget', label:'{{ _('Statistics') }}', cell: 'string',
+ type: 'text', disabled: 'inSchemaWithColumnCheck', mode: ['properties', 'edit'],
+ group: '{{ _('Definition') }}'
+ },{
+ id: 'attstorage', label:'{{ _('Storage') }}', group: '{{ _('Definition') }}',
+ type: 'text', mode: ['properties', 'edit'],
+ cell: 'string', disabled: 'inSchemaWithColumnCheck',
+ control: 'select2', select2: { placeholder: "Select storage",
+ allowClear: false,
+ width: "100%"
+ },
+ options: [
+ {label: "PLAIN", value: "p"},
+ {label: "MAIN", value: "m"},
+ {label: "EXTERNAL", value: "e"},
+ {label: "EXTENDED", value: "x"},
+ ]
+ },{
+ id: 'is_pk', label:'{{ _('Primary key?') }}',
+ type: 'switch', disabled: true, mode: ['properties']
+ },{
+ id: 'is_fk', label:'{{ _('Foreign key?') }}',
+ type: 'switch', disabled: true, mode: ['properties']
+ },{
+ id: 'is_inherited', label:'{{ _('Inherited?') }}',
+ type: 'switch', disabled: true, mode: ['properties']
+ },{
+ id: 'tbls_inherited', label:'{{ _('Inherited from table(s)') }}',
+ type: 'text', disabled: true, mode: ['properties'], deps: ['is_inherited'],
+ visible: function(m) {
+ if (!_.isUndefined(m.get('is_inherited')) && m.get('is_inherited')) {
+ return true;
+ } else {
+ return false;
+ }
+ }
+ },{
+ id: 'is_sys_column', label:'{{ _('System Column?') }}', cell: 'string',
+ type: 'switch', disabled: true, mode: ['properties']
+ },{
+ id: 'description', label:'{{ _('Comment') }}', cell: 'string',
+ type: 'multiline', mode: ['properties', 'create', 'edit'],
+ disabled: 'inSchema'
+ },{
+ id: 'attacl', label: 'Privileges', type: 'collection',
+ group: '{{ _('Security') }}', control: 'unique-col-collection',
+ model: pgAdmin.Browser.Node.PrivilegeRoleModel.extend({
+ privileges: ['a','r','w','x']}),
+ mode: ['properties', 'edit'], canAdd: true, canDelete: true,
+ uniqueCol : ['grantee']
+ },{
+ id: 'attoptions', label: 'Variables', type: 'collection',
+ group: '{{ _('Security') }}', control: 'unique-col-collection',
+ model: VariablesModel, uniqueCol : ['name'],
+ mode: ['edit', 'create'], canAdd: true, canEdit: false,
+ canDelete: true
+ },{
+ id: 'seclabels', label: '{{ _('Security Labels') }}',
+ model: Backform.SecurityModel, editable: false, type: 'collection',
+ group: '{{ _('Security') }}', mode: ['edit', 'create'],
+ min_version: 90200, canAdd: true,
+ canEdit: false, canDelete: true, control: 'unique-col-collection'
+ }
+ ],
+ validate: function() {
+ var err = {},
+ changedAttrs = this.changed,
+ msg = undefined;
+ this.errorModel.clear();
+
+ if (_.has(changedAttrs,this.get('name'))
+ && _.isUndefined(this.get('name'))
+ || String(this.get('name')).replace(/^\s+|\s+$/g, '') == '') {
+ msg = '{{ _('Name can not be empty!') }}';
+ this.errorModel.set('name', msg);
+ return msg;
+ } else if (_.has(changedAttrs,this.get('attowner'))
+ && _.isUndefined(this.get('attowner'))
+ || String(this.get('attowner')).replace(/^\s+|\s+$/g, '') == '') {
+ msg = '{{ _('Schema can not be empty!') }}';
+ this.errorModel.set('attowner', msg);
+ return msg;
+ } else if (_.has(changedAttrs,this.get('attowner'))
+ && _.isUndefined(this.get('attowner'))
+ || String(this.get('attowner')).replace(/^\s+|\s+$/g, '') == '') {
+ msg = '{{ _('Owner can not be empty!') }}';
+ this.errorModel.set('attowner', msg);
+ return msg;
+ } else if (_.has(changedAttrs,this.get('attlen'))
+ && _.isUndefined(this.get('attlen'))
+ || String(this.get('attlen')).replace(/^\s+|\s+$/g, '') == '') {
+ // Validation for Length field
+ if (this.get('attlen') < this.get('min_val'))
+ msg = _("Length should not be less than " + this.get('min_val'))
+ if (this.get('attlen') > this.get('max_val'))
+ msg = _("Length should not be greater than " + this.get('max_val'))
+ // If we have any error set then throw it to user
+ if(msg) {
+ this.errorModel.set('attlen', msg)
+ return msg;
+ }
+ } else if (_.has(changedAttrs,this.get('attprecision'))
+ && _.isUndefined(this.get('attprecision'))
+ || String(this.get('attprecision')).replace(/^\s+|\s+$/g, '') == '') {
+ // Validation for precision field
+ if (this.get('attprecision') < this.get('min_val'))
+ msg = _("Precision should not be less than " + this.get('min_val'))
+ if (this.get('attprecision') > this.get('max_val'))
+ msg = _("Precision should not be greater than " + this.get('max_val'))
+ // If we have any error set then throw it to user
+ if(msg) {
+ this.errorModel.set('attprecision', msg)
+ return msg;
+ }
+ return null;
+ }
+ },
+ // We will check if we are under schema node & in 'create' mode
+ inSchema: function() {
+ if(this.node_info && 'catalog' in this.node_info)
+ {
+ return true;
+ }
+ return false;
+ },
+ // We will check if we are under schema node & in 'create' mode
+ inSchemaWithModelCheck: function(m) {
+ if(this.node_info && 'schema' in this.node_info)
+ {
+ // We will disable control if it's in 'edit' mode
+ if (m.isNew()) {
+ return false;
+ } else {
+ return true;
+ }
+ }
+ return true;
+ },
+ // Checks weather to enable/disable control
+ inSchemaWithColumnCheck: function(m) {
+ if(this.node_info && 'schema' in this.node_info)
+ {
+ // We will disable control if it's system columns
+ // ie: it's position is less then 1
+ if (m.isNew()) {
+ return false;
+ } else {
+ // if we are in edit mode
+ if (!_.isUndefined(m.get('attnum')) && m.get('attnum') >= 1 ) {
+ return false;
+ } else {
+ return true;
+ }
+ }
+ }
+ return true;
+ }
+ }),
+ // Below function will enable right click menu for creating column
+ 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, parents = [];
+ // To iterate over tree to check parent node
+ while (i) {
+ // If it is schema then allow user to c reate table
+ if (_.indexOf(['schema'], d._type) > -1) {
+ return true;
+ }
+ else if (_.indexOf(['view', 'coll-view',
+ 'materialized_view',
+ 'coll-materialized_view'], d._type) > -1) {
+ parents.push(d._type);
+ break;
+ }
+ parents.push(d._type);
+ i = t.hasParent(i) ? t.parent(i) : null;
+ d = i ? t.itemData(i) : null;
+ }
+
+ // If node is under catalog then do not allow 'create' menu
+ if (_.indexOf(parents, 'catalog') > -1 ||
+ _.indexOf(parents, 'coll-view') > -1 ||
+ _.indexOf(parents, 'coll-materialized_view') > -1 ||
+ _.indexOf(parents, 'materialized_view') > -1 ||
+ _.indexOf(parents, 'view') > -1) {
+ return false;
+ } else {
+ return true;
+ }
+ }
+ });
+ }
+
+ return pgBrowser.Nodes['column'];
+});
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/macros/privilege.macros b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/macros/privilege.macros
new file mode 100644
index 0000000..7eafd60
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/macros/privilege.macros
@@ -0,0 +1,13 @@
+{% macro APPLY(conn, schema_name, table_object, column_object, role, privs, with_grant_privs) -%}
+{% if privs %}
+GRANT {% for p in privs %}{% if loop.index != 1 %}, {% endif %}{{p}}({{conn|qtIdent(column_object)}}){% endfor %}
+ ON {{ conn|qtIdent(schema_name, table_object) }} TO {{ conn|qtIdent(role) }};
+{% endif %}
+{% if with_grant_privs %}
+GRANT {% for p in with_grant_privs %}{% if loop.index != 1 %}, {% endif %}{{p}}({{conn|qtIdent(column_object)}}){% endfor %}
+ ON {{ conn|qtIdent(schema_name, table_object) }} TO {{ conn|qtIdent(role) }} WITH GRANT OPTION;
+{% endif %}
+{%- endmacro %}
+{% macro RESETALL(conn, schema_name, table_object, column_object, role) -%}
+REVOKE ALL({{ conn|qtIdent(column_object) }}) ON {{ conn|qtIdent(schema_name, table_object) }} FROM {{ conn|qtIdent(role) }};
+{%- endmacro %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/macros/security.macros b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/macros/security.macros
new file mode 100644
index 0000000..39587c3
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/macros/security.macros
@@ -0,0 +1,6 @@
+{% macro APPLY(conn, type, schema_name, parent_object, child_object, provider, label) -%}
+SECURITY LABEL FOR {{ conn|qtIdent(provider) }} ON {{ type }} {{ conn|qtIdent(schema_name, parent_object, child_object) }} IS {{ label|qtLiteral }};
+{%- endmacro %}
+{% macro DROP(conn, type, schema_name, parent_object, child_object, provider) -%}
+SECURITY LABEL FOR {{ conn|qtIdent(provider) }} ON {{ type }} {{ conn|qtIdent(schema_name, parent_object, child_object) }} IS NULL;
+{%- endmacro %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/acl.sql
new file mode 100644
index 0000000..ca3dbc4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/acl.sql
@@ -0,0 +1,37 @@
+SELECT 'attacl' 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 'CONNECT' THEN 'c'
+ WHEN 'CREATE' THEN 'C'
+ WHEN 'DELETE' THEN 'd'
+ WHEN 'EXECUTE' THEN 'X'
+ WHEN 'INSERT' THEN 'a'
+ WHEN 'REFERENCES' THEN 'x'
+ WHEN 'SELECT' THEN 'r'
+ WHEN 'TEMPORARY' THEN 'T'
+ WHEN 'TRIGGER' THEN 't'
+ WHEN 'TRUNCATE' THEN 'D'
+ WHEN 'UPDATE' THEN 'w'
+ WHEN 'USAGE' THEN 'U'
+ ELSE 'UNKNOWN'
+ END AS privilege_type
+ FROM
+ (SELECT attacl
+ FROM pg_attribute att
+ WHERE att.attrelid = {{tid}}::oid
+ AND att.attnum = {{clid}}::int
+ ) 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(attacl) as d FROM pg_attribute att
+ WHERE att.attrelid = {{tid}}::oid
+ AND att.attnum = {{clid}}::int) a) d
+ ) d
+ LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid)
+ LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid)
+GROUP BY g.rolname, gt.rolname
+
+
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/create.sql
new file mode 100644
index 0000000..9e06a5f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/create.sql
@@ -0,0 +1,36 @@
+{% import 'column/macros/security.macros' as SECLABLE %}
+{% import 'column/macros/privilege.macros' as PRIVILEGE %}
+{% import 'macros/variable.macros' as VARIABLE %}
+{% if data %}
+{### Add column ###}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ ADD COLUMN {{conn|qtIdent(data.name)}} {{data.cltype}}{% if data.attlen %}
+({{data.attlen}}{% if data.attprecision%}, {{data.attprecision}}{% endif %}){% endif %}{% if data.hasSqrBracket %}
+[]{% endif %}{% if data.collspcname %}
+ COLLATE {{data.collspcname}}{% endif %}{% if data.attnotnull %}
+ NOT NULL{% endif %}{% if data.defval %}
+ DEFAULT {{data.defval}}{% endif %};
+{### Add comments ###}
+{% if data and data.description %}
+COMMENT ON COLUMN {{conn|qtIdent(data.schema, data.table, data.name)}}
+ IS {{data.description|qtLiteral}};
+{% endif %}
+{### Add variables to column ###}
+{% if data.attoptions %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+{{ VARIABLE.SET(conn, 'COLUMN', data.name, data.attoptions) }}
+{% endif %}
+{### ACL ###}
+{% if data.attacl %}
+{% for priv in data.attacl %}
+{{ PRIVILEGE.APPLY(conn, data.schema, data.table, data.name, priv.grantee, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{### Security Lables ###}
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{{ SECLABLE.APPLY(conn, 'COLUMN',data.schema, data.table, data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{### End main if ###}
+{% endif%}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/delete.sql
new file mode 100644
index 0000000..2c5110f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/delete.sql
@@ -0,0 +1 @@
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}} DROP COLUMN {{conn|qtIdent(data.name}};
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/depend.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/depend.sql
new file mode 100644
index 0000000..f5f39e7
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/depend.sql
@@ -0,0 +1,9 @@
+SELECT
+ ref.relname AS refname, d2.refclassid, dep.deptype AS deptype
+FROM pg_depend dep
+ LEFT JOIN pg_depend d2 ON dep.objid=d2.objid AND dep.refobjid != d2.refobjid
+ LEFT JOIN pg_class ref ON ref.oid=d2.refobjid
+ LEFT JOIN pg_attribute att ON d2.refclassid=att.attrelid AND d2.refobjsubid=att.attnum
+ {{ where }} AND
+ dep.classid=(SELECT oid FROM pg_class WHERE relname='pg_attrdef') AND
+ dep.refobjid NOT IN (SELECT d3.refobjid FROM pg_depend d3 WHERE d3.objid=d2.refobjid)
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/edit_mode_types.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/edit_mode_types.sql
new file mode 100644
index 0000000..8bc6385
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/edit_mode_types.sql
@@ -0,0 +1,5 @@
+SELECT tt.oid, format_type(tt.oid,NULL) AS typname
+ FROM pg_cast
+ JOIN pg_type tt ON tt.oid=casttarget
+WHERE castsource={{type_id}}
+ AND castcontext IN ('i', 'a')
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/get_collations.sql
new file mode 100644
index 0000000..58fcb32
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/get_collations.sql
@@ -0,0 +1,7 @@
+SELECT --nspname, collname,
+ CASE WHEN length(nspname) > 0 AND length(collname) > 0 THEN
+ concat(quote_ident(nspname), '.', quote_ident(collname))
+ ELSE '' END AS collation
+FROM pg_collation c, pg_namespace n
+ WHERE c.collnamespace=n.oid
+ORDER BY nspname, collname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/get_inherited_tables.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/get_inherited_tables.sql
new file mode 100644
index 0000000..37934b8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/get_inherited_tables.sql
@@ -0,0 +1,12 @@
+SELECT array_to_string(array_agg(inhrelname), ', ') inhrelname, attrname
+FROM
+ (SELECT
+ inhparent::regclass AS inhrelname,
+ a.attname AS attrname
+ FROM pg_inherits i
+ LEFT JOIN pg_attribute a ON
+ (attrelid = inhparent AND attnum > 0)
+ WHERE inhrelid = {{tid}}::oid
+ ORDER BY inhseqno
+ ) a
+GROUP BY attrname;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/get_parent.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/get_parent.sql
new file mode 100644
index 0000000..5dd5d3c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/get_parent.sql
@@ -0,0 +1,5 @@
+SELECT nsp.nspname AS schema ,rel.relname AS table
+FROM pg_class rel
+ JOIN pg_namespace nsp
+ ON rel.relnamespace = nsp.oid::int
+ WHERE rel.oid = {{tid}}::int
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/get_position.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/get_position.sql
new file mode 100644
index 0000000..cea5721
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/get_position.sql
@@ -0,0 +1,4 @@
+SELECT att.attnum
+FROM pg_attribute att
+ WHERE att.attrelid = {{tid}}::oid
+ AND att.attname = {{data.name|qtLiteral}}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/get_types.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/get_types.sql
new file mode 100644
index 0000000..469096c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/get_types.sql
@@ -0,0 +1,14 @@
+SELECT * FROM
+ (SELECT format_type(t.oid,NULL) AS typname,
+ CASE WHEN typelem > 0 THEN typelem ELSE t.oid END AS elemoid
+ ,typlen, typtype, t.oid, nspname,
+ (SELECT COUNT(1) FROM pg_type t2 WHERE t2.typname = t.typname) > 1 AS isdup
+FROM pg_type t
+ JOIN pg_namespace nsp ON typnamespace=nsp.oid
+WHERE (NOT (typname = 'unknown' AND nspname = 'pg_catalog'))
+ AND typisdefined AND typtype IN ('b', 'c', 'd', 'e', 'r')
+ AND NOT EXISTS (select 1 from pg_class where relnamespace=typnamespace and relname = typname and relkind != 'c')
+ AND (typname not like '_%' OR NOT EXISTS (select 1 from pg_class where relnamespace=typnamespace and relname = substring(typname from 2)::name and relkind != 'c'))
+ AND nsp.nspname != 'information_schema'
+ ) AS dummy
+ ORDER BY nspname <> 'pg_catalog', nspname <> 'public', nspname, 1
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/is_referenced.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/is_referenced.sql
new file mode 100644
index 0000000..7d0bfc3
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/is_referenced.sql
@@ -0,0 +1,5 @@
+SELECT COUNT(1)
+FROM pg_depend dep
+ JOIN pg_class cl ON dep.classid=cl.oid AND relname='pg_rewrite'
+ WHERE refobjid= {{tid}}::oid
+ AND refobjsubid= {{clid|qtLiteral}};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/nodes.sql
new file mode 100644
index 0000000..069c7d7
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/nodes.sql
@@ -0,0 +1,18 @@
+SELECT att.attname as name, att.attnum as OID
+FROM pg_attribute att
+ JOIN pg_type ty ON ty.oid=atttypid
+ JOIN pg_namespace tn ON tn.oid=ty.typnamespace
+ JOIN pg_class cl ON cl.oid=att.attrelid
+ JOIN pg_namespace na ON na.oid=cl.relnamespace
+ LEFT OUTER JOIN pg_type et ON et.oid=ty.typelem
+ LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
+ LEFT OUTER JOIN (pg_depend JOIN pg_class cs ON objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum
+ LEFT OUTER JOIN pg_namespace ns ON ns.oid=cs.relnamespace
+ LEFT OUTER JOIN pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
+WHERE att.attrelid = {{tid}}::oid
+{### To show system objects ###}
+{% if not show_sys_objects %}
+ AND att.attnum > 0
+{% endif %}
+ AND att.attisdropped IS FALSE
+ ORDER BY att.attnum
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/properties.sql
new file mode 100644
index 0000000..d536906
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/properties.sql
@@ -0,0 +1,45 @@
+SELECT att.attname as name, att.*, def.*, pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS defval,
+ CASE WHEN att.attndims > 0 THEN 1 ELSE 0 END AS isarray,
+ format_type(ty.oid,NULL) AS typname,
+ format_type(ty.oid,att.atttypmod) AS displaytypname,
+ tn.nspname as typnspname, et.typname as elemtypname,
+ ty.typstorage AS defaultstorage, cl.relname, na.nspname,
+ concat(quote_ident(na.nspname) ,'.', quote_ident(cl.relname)) AS parent_tbl,
+ att.attstattarget, description, cs.relname AS sername,
+ ns.nspname AS serschema,
+ (SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname) > 1 AS isdup,
+ indkey, coll.collname, nspc.nspname as collnspname , attoptions,
+ -- Start pgAdmin4, added to save time on client side parsing
+ CASE WHEN length(coll.collname) > 0 AND length(nspc.nspname) > 0 THEN
+ concat(quote_ident(coll.collname),'.',quote_ident(nspc.nspname))
+ ELSE '' END AS collspcname,
+ CASE WHEN strpos(format_type(ty.oid,att.atttypmod), '.') > 0 THEN
+ split_part(format_type(ty.oid,att.atttypmod), '.', 2)
+ ELSE format_type(ty.oid,att.atttypmod) END AS cltype,
+ -- End pgAdmin4
+ EXISTS(SELECT 1 FROM pg_constraint WHERE conrelid=att.attrelid AND contype='f' AND att.attnum=ANY(conkey)) As is_fk,
+ (SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE sl1.objoid=att.atttypid AND sl1.objsubid=0) AS seclabels,
+ (CASE WHEN (att.attnum < 1) THEN true ElSE false END) AS is_sys_column
+FROM pg_attribute att
+ JOIN pg_type ty ON ty.oid=atttypid
+ JOIN pg_namespace tn ON tn.oid=ty.typnamespace
+ JOIN pg_class cl ON cl.oid=att.attrelid
+ JOIN pg_namespace na ON na.oid=cl.relnamespace
+ LEFT OUTER JOIN pg_type et ON et.oid=ty.typelem
+ LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
+ LEFT OUTER JOIN pg_description des ON (des.objoid=att.attrelid AND des.objsubid=att.attnum AND des.classoid='pg_class'::regclass)
+ LEFT OUTER JOIN (pg_depend JOIN pg_class cs ON objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum
+ LEFT OUTER JOIN pg_namespace ns ON ns.oid=cs.relnamespace
+ LEFT OUTER JOIN pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
+ LEFT OUTER JOIN pg_collation coll ON att.attcollation=coll.oid
+ LEFT OUTER JOIN pg_namespace nspc ON coll.collnamespace=nspc.oid
+WHERE att.attrelid = {{tid}}::oid
+{% if clid %}
+ AND att.attnum = {{clid}}::int
+{% endif %}
+{### To show system objects ###}
+{% if not show_sys_objects %}
+ AND att.attnum > 0
+{% endif %}
+ AND att.attisdropped IS FALSE
+ ORDER BY att.attnum
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/update.sql
new file mode 100644
index 0000000..fce24a7
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.1_plus/update.sql
@@ -0,0 +1,101 @@
+{% import 'column/macros/security.macros' as SECLABLE %}
+{% import 'column/macros/privilege.macros' as PRIVILEGE %}
+{% import 'macros/variable.macros' as VARIABLE %}
+{% if data %}
+{### Rename column name ###}
+{% if data.name != o_data.name %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ RENAME {{conn|qtIdent(o_data.name)}} TO {{conn|qtIdent(data.name)}};
+{% endif %}
+{### Alter column type and collation ###}
+{% if data.cltype and data.cltype != o_data.cltype %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ ALTER COLUMN {{conn|qtIdent(data.name)}} {{data.cltype}}{% if data.attlen %}
+({{data.attlen}}{% if data.attprecision%}, {{data.attprecision}}{% endif %}){% endif %}{% if data.hasSqrBracket %}
+[]{% endif %}{% if data.collspcname and data.collspcname != o_data.collspcname %}
+ COLLATE {{data.collspcname}}{% endif %};
+{% endif %}
+{### Alter column default value ###}
+{% if data.defval and data.defval != o_data.defval %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ ALTER COLUMN {{conn|qtIdent(data.name)}} SET DEFAULT {{data.defval}};
+{% endif %}
+{### Alter column not null value ###}
+{% if 'attnotnull' in data %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ ALTER COLUMN {{conn|qtIdent(data.name)}} {% if data.attnotnull %}SET{% else %}DROP{% endif %} NOT NULL;
+{% endif %}
+{### Alter column statistics value ###}
+{% if data.attstatterget %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ ALTER COLUMN {{conn|qtIdent(data.name)}} SET STATISTICS {{data.attstatterget}};
+{% endif %}
+{### Alter column storage value ###}
+{% if data.attstorage %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ ALTER COLUMN {{conn|qtIdent(data.name)}} SET STORAGE {%if data.attstorage == 'p' %}
+PLAIN{% elif data.attstorage == 'm'%}MAIN{% elif data.attstorage == 'e'%}
+EXTERNAL{% elif data.attstorage == 'x'%}EXTENDED{% endif %};
+{% endif %}
+{% if data.description %}
+COMMENT ON COLUMN {{conn|qtIdent(data.schema, data.table, data.name)}}
+ IS {{data.description|qtLiteral}};
+{% endif %}
+{### Update column variables ###}
+{% if 'attoptions' in data and data.attoptions|length > 0 %}
+{% set variables = data.attoptions %}
+{% if 'deleted' in variables and variables.deleted|length > 0 %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+{{ VARIABLE.UNSET(conn, 'COLUMN', data.name, variables.deleted) }}
+{% endif %}
+{% if 'added' in variables and variables.added|length > 0 %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+{{ VARIABLE.SET(conn, 'COLUMN', data.name, variables.added) }}
+{% endif %}
+{% if 'changed' in variables and variables.changed|length > 0 %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+{{ VARIABLE.SET(conn, 'COLUMN', data.name, variables.changed) }}
+{% endif %}
+{% endif %}
+{### Update column privileges ###}
+{# Change the privileges #}
+{% if data.attacl %}
+{% if 'deleted' in data.attacl %}
+{% for priv in data.attacl.deleted %}
+{{ PRIVILEGE.RESETALL(conn, data.schema, data.table, data.name, priv.grantee) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.attacl %}
+{% for priv in data.attacl.changed %}
+{{ PRIVILEGE.RESETALL(conn, data.schema, data.table, data.name, priv.grantee) }}
+{{ PRIVILEGE.APPLY(conn, data.schema, data.table, data.name, priv.grantee, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.attacl %}
+{% for priv in data.attacl.added %}
+{{ PRIVILEGE.APPLY(conn, data.schema, data.table, data.name, priv.grantee, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{### Uppdate tablespace securitylabel ###}
+{# The SQL generated below will change Security Label #}
+{% if data.seclabels 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, 'COLUMN', data.schema, data.table, data.name, r.provider) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+{{ SECLABLE.APPLY(conn, 'COLUMN',data.schema, data.table, 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, 'COLUMN',data.schema, data.table, data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{### End main if ###}
+{% endif%}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/acl.sql
new file mode 100644
index 0000000..ca3dbc4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/acl.sql
@@ -0,0 +1,37 @@
+SELECT 'attacl' 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 'CONNECT' THEN 'c'
+ WHEN 'CREATE' THEN 'C'
+ WHEN 'DELETE' THEN 'd'
+ WHEN 'EXECUTE' THEN 'X'
+ WHEN 'INSERT' THEN 'a'
+ WHEN 'REFERENCES' THEN 'x'
+ WHEN 'SELECT' THEN 'r'
+ WHEN 'TEMPORARY' THEN 'T'
+ WHEN 'TRIGGER' THEN 't'
+ WHEN 'TRUNCATE' THEN 'D'
+ WHEN 'UPDATE' THEN 'w'
+ WHEN 'USAGE' THEN 'U'
+ ELSE 'UNKNOWN'
+ END AS privilege_type
+ FROM
+ (SELECT attacl
+ FROM pg_attribute att
+ WHERE att.attrelid = {{tid}}::oid
+ AND att.attnum = {{clid}}::int
+ ) 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(attacl) as d FROM pg_attribute att
+ WHERE att.attrelid = {{tid}}::oid
+ AND att.attnum = {{clid}}::int) a) d
+ ) d
+ LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid)
+ LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid)
+GROUP BY g.rolname, gt.rolname
+
+
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/create.sql
new file mode 100644
index 0000000..d626923
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/create.sql
@@ -0,0 +1,37 @@
+{% import 'column/macros/security.macros' as SECLABLE %}
+{% import 'column/macros/privilege.macros' as PRIVILEGE %}
+{% import 'macros/variable.macros' as VARIABLE %}
+{% if data %}
+{### Add column ###}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ ADD COLUMN {{conn|qtIdent(data.name)}} {{data.cltype}}{% if data.attlen %}
+({{data.attlen}}{% if data.attprecision%}, {{data.attprecision}}{% endif %}){% endif %}{% if data.hasSqrBracket %}
+[]{% endif %}{% if data.collspcname %}
+ COLLATE {{data.collspcname}}{% endif %}{% if data.attnotnull %}
+ NOT NULL{% endif %}{% if data.defval %}
+ DEFAULT {{data.defval}}{% endif %};
+{### Add comments ###}
+{% if data and data.description %}
+COMMENT ON COLUMN {{conn|qtIdent(data.schema, data.table, data.name)}}
+ IS {{data.description|qtLiteral}};
+{% endif %}
+{### Add variables to column ###}
+{% if data.attoptions %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+{{ VARIABLE.SET(conn, 'COLUMN', data.name, data.attoptions) }}
+{% endif %}
+{### ACL ###}
+{% if data.attacl %}
+{% for priv in data.attacl %}
+{{ PRIVILEGE.APPLY(conn, data.schema, data.table, data.name, priv.grantee, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{### Security Lables ###}
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{{ SECLABLE.APPLY(conn, 'COLUMN',data.schema, data.table, data.name, r.provider, r.security_label) }}
+{% endfor %}
+
+{% endif %}
+{### End main if ###}
+{% endif%}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/delete.sql
new file mode 100644
index 0000000..0e16251
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/delete.sql
@@ -0,0 +1 @@
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}} DROP COLUMN {{conn|qtIdent(data.name)}};
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/depend.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/depend.sql
new file mode 100644
index 0000000..4877e12
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/depend.sql
@@ -0,0 +1,11 @@
+SELECT
+ ref.relname AS refname, d2.refclassid, dep.deptype AS deptype
+FROM pg_depend dep
+ LEFT JOIN pg_depend d2 ON dep.objid=d2.objid AND dep.refobjid != d2.refobjid
+ LEFT JOIN pg_class ref ON ref.oid=d2.refobjid
+ LEFT JOIN pg_attribute att ON d2.refclassid=att.attrelid AND d2.refobjsubid=att.attnum
+ {{ where }} AND
+ dep.classid=(SELECT oid FROM pg_class WHERE relname='pg_attrdef') AND
+ dep.refobjid NOT IN (SELECT d3.refobjid FROM pg_depend d3 WHERE d3.objid=d2.refobjid)
+
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/edit_mode_types.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/edit_mode_types.sql
new file mode 100644
index 0000000..0c112c5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/edit_mode_types.sql
@@ -0,0 +1,5 @@
+SELECT tt.oid, format_type(tt.oid,NULL) AS typname
+FROM pg_cast
+ JOIN pg_type tt ON tt.oid=casttarget
+WHERE castsource={{type_id}}
+ AND castcontext IN ('i', 'a')
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/get_collations.sql
new file mode 100644
index 0000000..9e67931
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/get_collations.sql
@@ -0,0 +1,7 @@
+SELECT --nspname, collname,
+ CASE WHEN length(nspname) > 0 AND length(collname) > 0 THEN
+ concat(quote_ident(nspname), '.', quote_ident(collname))
+ ELSE '' END AS collation
+FROM pg_collation c, pg_namespace n
+ WHERE c.collnamespace=n.oid
+ ORDER BY nspname, collname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/get_inherited_tables.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/get_inherited_tables.sql
new file mode 100644
index 0000000..37934b8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/get_inherited_tables.sql
@@ -0,0 +1,12 @@
+SELECT array_to_string(array_agg(inhrelname), ', ') inhrelname, attrname
+FROM
+ (SELECT
+ inhparent::regclass AS inhrelname,
+ a.attname AS attrname
+ FROM pg_inherits i
+ LEFT JOIN pg_attribute a ON
+ (attrelid = inhparent AND attnum > 0)
+ WHERE inhrelid = {{tid}}::oid
+ ORDER BY inhseqno
+ ) a
+GROUP BY attrname;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/get_parent.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/get_parent.sql
new file mode 100644
index 0000000..5dd5d3c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/get_parent.sql
@@ -0,0 +1,5 @@
+SELECT nsp.nspname AS schema ,rel.relname AS table
+FROM pg_class rel
+ JOIN pg_namespace nsp
+ ON rel.relnamespace = nsp.oid::int
+ WHERE rel.oid = {{tid}}::int
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/get_position.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/get_position.sql
new file mode 100644
index 0000000..cea5721
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/get_position.sql
@@ -0,0 +1,4 @@
+SELECT att.attnum
+FROM pg_attribute att
+ WHERE att.attrelid = {{tid}}::oid
+ AND att.attname = {{data.name|qtLiteral}}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/get_types.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/get_types.sql
new file mode 100644
index 0000000..469096c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/get_types.sql
@@ -0,0 +1,14 @@
+SELECT * FROM
+ (SELECT format_type(t.oid,NULL) AS typname,
+ CASE WHEN typelem > 0 THEN typelem ELSE t.oid END AS elemoid
+ ,typlen, typtype, t.oid, nspname,
+ (SELECT COUNT(1) FROM pg_type t2 WHERE t2.typname = t.typname) > 1 AS isdup
+FROM pg_type t
+ JOIN pg_namespace nsp ON typnamespace=nsp.oid
+WHERE (NOT (typname = 'unknown' AND nspname = 'pg_catalog'))
+ AND typisdefined AND typtype IN ('b', 'c', 'd', 'e', 'r')
+ AND NOT EXISTS (select 1 from pg_class where relnamespace=typnamespace and relname = typname and relkind != 'c')
+ AND (typname not like '_%' OR NOT EXISTS (select 1 from pg_class where relnamespace=typnamespace and relname = substring(typname from 2)::name and relkind != 'c'))
+ AND nsp.nspname != 'information_schema'
+ ) AS dummy
+ ORDER BY nspname <> 'pg_catalog', nspname <> 'public', nspname, 1
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/is_referenced.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/is_referenced.sql
new file mode 100644
index 0000000..7d0bfc3
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/is_referenced.sql
@@ -0,0 +1,5 @@
+SELECT COUNT(1)
+FROM pg_depend dep
+ JOIN pg_class cl ON dep.classid=cl.oid AND relname='pg_rewrite'
+ WHERE refobjid= {{tid}}::oid
+ AND refobjsubid= {{clid|qtLiteral}};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/nodes.sql
new file mode 100644
index 0000000..3319196
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/nodes.sql
@@ -0,0 +1,18 @@
+SELECT att.attname as name, att.attnum as OID
+FROM pg_attribute att
+ JOIN pg_type ty ON ty.oid=atttypid
+ JOIN pg_namespace tn ON tn.oid=ty.typnamespace
+ JOIN pg_class cl ON cl.oid=att.attrelid
+ JOIN pg_namespace na ON na.oid=cl.relnamespace
+ LEFT OUTER JOIN pg_type et ON et.oid=ty.typelem
+ LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
+ LEFT OUTER JOIN (pg_depend JOIN pg_class cs ON objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum
+ LEFT OUTER JOIN pg_namespace ns ON ns.oid=cs.relnamespace
+ LEFT OUTER JOIN pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
+WHERE att.attrelid = {{tid}}::oid
+ {### To show system objects ###}
+ {% if not show_sys_objects %}
+ AND att.attnum > 0
+ {% endif %}
+ AND att.attisdropped IS FALSE
+ ORDER BY att.attnum
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/properties.sql
new file mode 100644
index 0000000..d536906
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/properties.sql
@@ -0,0 +1,45 @@
+SELECT att.attname as name, att.*, def.*, pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS defval,
+ CASE WHEN att.attndims > 0 THEN 1 ELSE 0 END AS isarray,
+ format_type(ty.oid,NULL) AS typname,
+ format_type(ty.oid,att.atttypmod) AS displaytypname,
+ tn.nspname as typnspname, et.typname as elemtypname,
+ ty.typstorage AS defaultstorage, cl.relname, na.nspname,
+ concat(quote_ident(na.nspname) ,'.', quote_ident(cl.relname)) AS parent_tbl,
+ att.attstattarget, description, cs.relname AS sername,
+ ns.nspname AS serschema,
+ (SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname) > 1 AS isdup,
+ indkey, coll.collname, nspc.nspname as collnspname , attoptions,
+ -- Start pgAdmin4, added to save time on client side parsing
+ CASE WHEN length(coll.collname) > 0 AND length(nspc.nspname) > 0 THEN
+ concat(quote_ident(coll.collname),'.',quote_ident(nspc.nspname))
+ ELSE '' END AS collspcname,
+ CASE WHEN strpos(format_type(ty.oid,att.atttypmod), '.') > 0 THEN
+ split_part(format_type(ty.oid,att.atttypmod), '.', 2)
+ ELSE format_type(ty.oid,att.atttypmod) END AS cltype,
+ -- End pgAdmin4
+ EXISTS(SELECT 1 FROM pg_constraint WHERE conrelid=att.attrelid AND contype='f' AND att.attnum=ANY(conkey)) As is_fk,
+ (SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE sl1.objoid=att.atttypid AND sl1.objsubid=0) AS seclabels,
+ (CASE WHEN (att.attnum < 1) THEN true ElSE false END) AS is_sys_column
+FROM pg_attribute att
+ JOIN pg_type ty ON ty.oid=atttypid
+ JOIN pg_namespace tn ON tn.oid=ty.typnamespace
+ JOIN pg_class cl ON cl.oid=att.attrelid
+ JOIN pg_namespace na ON na.oid=cl.relnamespace
+ LEFT OUTER JOIN pg_type et ON et.oid=ty.typelem
+ LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
+ LEFT OUTER JOIN pg_description des ON (des.objoid=att.attrelid AND des.objsubid=att.attnum AND des.classoid='pg_class'::regclass)
+ LEFT OUTER JOIN (pg_depend JOIN pg_class cs ON objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum
+ LEFT OUTER JOIN pg_namespace ns ON ns.oid=cs.relnamespace
+ LEFT OUTER JOIN pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
+ LEFT OUTER JOIN pg_collation coll ON att.attcollation=coll.oid
+ LEFT OUTER JOIN pg_namespace nspc ON coll.collnamespace=nspc.oid
+WHERE att.attrelid = {{tid}}::oid
+{% if clid %}
+ AND att.attnum = {{clid}}::int
+{% endif %}
+{### To show system objects ###}
+{% if not show_sys_objects %}
+ AND att.attnum > 0
+{% endif %}
+ AND att.attisdropped IS FALSE
+ ORDER BY att.attnum
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/update.sql
new file mode 100644
index 0000000..fce24a7
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/columns/templates/column/sql/9.2_plus/update.sql
@@ -0,0 +1,101 @@
+{% import 'column/macros/security.macros' as SECLABLE %}
+{% import 'column/macros/privilege.macros' as PRIVILEGE %}
+{% import 'macros/variable.macros' as VARIABLE %}
+{% if data %}
+{### Rename column name ###}
+{% if data.name != o_data.name %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ RENAME {{conn|qtIdent(o_data.name)}} TO {{conn|qtIdent(data.name)}};
+{% endif %}
+{### Alter column type and collation ###}
+{% if data.cltype and data.cltype != o_data.cltype %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ ALTER COLUMN {{conn|qtIdent(data.name)}} {{data.cltype}}{% if data.attlen %}
+({{data.attlen}}{% if data.attprecision%}, {{data.attprecision}}{% endif %}){% endif %}{% if data.hasSqrBracket %}
+[]{% endif %}{% if data.collspcname and data.collspcname != o_data.collspcname %}
+ COLLATE {{data.collspcname}}{% endif %};
+{% endif %}
+{### Alter column default value ###}
+{% if data.defval and data.defval != o_data.defval %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ ALTER COLUMN {{conn|qtIdent(data.name)}} SET DEFAULT {{data.defval}};
+{% endif %}
+{### Alter column not null value ###}
+{% if 'attnotnull' in data %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ ALTER COLUMN {{conn|qtIdent(data.name)}} {% if data.attnotnull %}SET{% else %}DROP{% endif %} NOT NULL;
+{% endif %}
+{### Alter column statistics value ###}
+{% if data.attstatterget %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ ALTER COLUMN {{conn|qtIdent(data.name)}} SET STATISTICS {{data.attstatterget}};
+{% endif %}
+{### Alter column storage value ###}
+{% if data.attstorage %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ ALTER COLUMN {{conn|qtIdent(data.name)}} SET STORAGE {%if data.attstorage == 'p' %}
+PLAIN{% elif data.attstorage == 'm'%}MAIN{% elif data.attstorage == 'e'%}
+EXTERNAL{% elif data.attstorage == 'x'%}EXTENDED{% endif %};
+{% endif %}
+{% if data.description %}
+COMMENT ON COLUMN {{conn|qtIdent(data.schema, data.table, data.name)}}
+ IS {{data.description|qtLiteral}};
+{% endif %}
+{### Update column variables ###}
+{% if 'attoptions' in data and data.attoptions|length > 0 %}
+{% set variables = data.attoptions %}
+{% if 'deleted' in variables and variables.deleted|length > 0 %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+{{ VARIABLE.UNSET(conn, 'COLUMN', data.name, variables.deleted) }}
+{% endif %}
+{% if 'added' in variables and variables.added|length > 0 %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+{{ VARIABLE.SET(conn, 'COLUMN', data.name, variables.added) }}
+{% endif %}
+{% if 'changed' in variables and variables.changed|length > 0 %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+{{ VARIABLE.SET(conn, 'COLUMN', data.name, variables.changed) }}
+{% endif %}
+{% endif %}
+{### Update column privileges ###}
+{# Change the privileges #}
+{% if data.attacl %}
+{% if 'deleted' in data.attacl %}
+{% for priv in data.attacl.deleted %}
+{{ PRIVILEGE.RESETALL(conn, data.schema, data.table, data.name, priv.grantee) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.attacl %}
+{% for priv in data.attacl.changed %}
+{{ PRIVILEGE.RESETALL(conn, data.schema, data.table, data.name, priv.grantee) }}
+{{ PRIVILEGE.APPLY(conn, data.schema, data.table, data.name, priv.grantee, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.attacl %}
+{% for priv in data.attacl.added %}
+{{ PRIVILEGE.APPLY(conn, data.schema, data.table, data.name, priv.grantee, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{### Uppdate tablespace securitylabel ###}
+{# The SQL generated below will change Security Label #}
+{% if data.seclabels 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, 'COLUMN', data.schema, data.table, data.name, r.provider) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+{{ SECLABLE.APPLY(conn, 'COLUMN',data.schema, data.table, 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, 'COLUMN',data.schema, data.table, data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{### End main if ###}
+{% endif%}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py
new file mode 100644
index 0000000..764116e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py
@@ -0,0 +1,869 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+""" Implements Index Node """
+
+from flask import render_template, make_response, request, jsonify
+from flask.ext.babel import gettext
+from pgadmin.utils.ajax import make_json_response, \
+ make_response as ajax_response, internal_server_error
+from pgadmin.browser.utils import PGChildNodeView
+from pgadmin.browser.collection import CollectionNodeModule
+import pgadmin.browser.server_groups.servers.databases as database
+from pgadmin.utils.ajax import precondition_required
+from pgadmin.utils.driver import get_driver
+from config import PG_DEFAULT_DRIVER
+from pgadmin.browser.server_groups.servers.utils import parse_priv_from_db, \
+ parse_priv_to_db
+from functools import wraps
+import json
+
+
+class IndexesModule(CollectionNodeModule):
+ """
+ class IndexesModule(CollectionNodeModule)
+
+ A module class for Index node derived from CollectionNodeModule.
+
+ Methods:
+ -------
+ * __init__(*args, **kwargs)
+ - Method is used to initialize the Index and it's base module.
+
+ * get_nodes(gid, sid, did, scid, tid)
+ - 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 schema, when any of the server node is
+ initialized.
+ """
+
+ NODE_TYPE = 'index'
+ COLLECTION_LABEL = gettext("Indexes")
+
+ def __init__(self, *args, **kwargs):
+ """
+ Method is used to initialize the IndexModule and it's base module.
+
+ Args:
+ *args:
+ **kwargs:
+ """
+ self.min_ver = None
+ self.max_ver = None
+ super(IndexesModule, self).__init__(*args, **kwargs)
+
+ def BackendSupported(self, manager, **kwargs):
+ """
+ Load this module if vid is view, we will not load it under
+ material view
+ """
+ if super(IndexesModule, self).BackendSupported(manager, **kwargs):
+ conn = manager.connection(did=kwargs['did'])
+ # If DB is not connected then return error to browser
+ if not conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+
+ if 'vid' not in kwargs:
+ return True
+
+ template_path = 'index/sql/9.1_plus'
+ SQL = render_template("/".join(
+ [template_path, 'backend_support.sql']), vid=kwargs['vid'])
+ status, res = conn.execute_scalar(SQL)
+
+ # check if any errors
+ if not status:
+ return internal_server_error(errormsg=res)
+ # Check vid is view not material view
+ # then true, othewise false
+ return res
+
+ def get_nodes(self, gid, sid, did, scid, **kwargs):
+ """
+ Generate the collection node
+ # TODO::
+ We can have following arguments for different type of parents.
+ i.e.
+ tid - for tables
+ vid - for materialized views
+ """
+ assert('tid' in kwargs or 'vid' in kwargs)
+ yield self.generate_browser_collection_node(
+ kwargs['tid'] if 'tid' in kwargs else kwargs['vid']
+ )
+
+ @property
+ def script_load(self):
+ """
+ Load the module script for server, when any of the server-group node is
+ initialized.
+ """
+ return database.DatabaseModule.NODE_TYPE
+
+ @property
+ def node_inode(self):
+ """
+ Load the module node as a leaf node
+ """
+ return False
+
+blueprint = IndexesModule(__name__)
+
+
+class IndexesView(PGChildNodeView):
+ """
+ This class is responsible for generating routes for Index node
+
+ Methods:
+ -------
+ * __init__(**kwargs)
+ - Method is used to initialize the IndexView and it's base view.
+
+ * module_js()
+ - This property defines (if javascript) exists for this node.
+ Override this property for your own logic
+
+ * check_precondition()
+ - This function will behave as a decorator which will checks
+ database connection before running view, it will also attaches
+ manager,conn & template_path properties to self
+
+ * list()
+ - This function is used to list all the Index nodes within that
+ collection.
+
+ * nodes()
+ - This function will used to create all the child node within that
+ collection, Here it will create all the Index node.
+
+ * properties(gid, sid, did, scid, tid, idx)
+ - This function will show the properties of the selected Index node
+
+ * create(gid, sid, did, scid, tid)
+ - This function will create the new Index object
+
+ * update(gid, sid, did, scid, tid, idx)
+ - This function will update the data for the selected Index node
+
+ * delete(self, gid, sid, scid, tid, idx):
+ - This function will drop the Index object
+
+ * msql(gid, sid, did, scid, tid, idx)
+ - This function is used to return modified SQL for the selected
+ Index node
+
+ * get_sql(data, scid, tid)
+ - 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
+ selected Index node.
+
+ * dependency(gid, sid, did, scid):
+ - This function will generate dependency list show it in dependency
+ pane for the selected Index node.
+
+ * dependent(gid, sid, did, scid):
+ - This function will generate dependent list to show it in dependent
+ pane for the selected Index node.
+ """
+
+ node_type = blueprint.node_type
+
+ parent_ids = [
+ {'type': 'int', 'id': 'gid'},
+ {'type': 'int', 'id': 'sid'},
+ {'type': 'int', 'id': 'did'},
+ {'type': 'int', 'id': 'scid'},
+ {'type': 'int', 'id': 'tid'}
+ ]
+ ids = [
+ {'type': 'int', 'id': 'idx'}
+ ]
+
+ operations = dict({
+ 'obj': [
+ {'get': 'properties', 'delete': 'delete', 'put': 'update'},
+ {'get': 'list', 'post': 'create'}
+ ],
+ 'delete': [{'delete': 'delete'}],
+ 'children': [{'get': 'children'}],
+ 'nodes': [{'get': 'node'}, {'get': 'nodes'}],
+ 'sql': [{'get': 'sql'}],
+ 'msql': [{'get': 'msql'}, {'get': 'msql'}],
+ 'stats': [{'get': 'statistics'}],
+ 'dependency': [{'get': 'dependencies'}],
+ 'dependent': [{'get': 'dependents'}],
+ 'module.js': [{}, {}, {'get': 'module_js'}],
+ 'get_collations': [{'get': 'get_collations'},
+ {'get': 'get_collations'}],
+ 'get_access_methods': [{'get': 'get_access_methods'},
+ {'get': 'get_access_methods'}],
+ 'get_op_class': [{'get': 'get_op_class'},
+ {'get': 'get_op_class'}]
+ })
+
+ def check_precondition(f):
+ """
+ This function will behave as a decorator which will checks
+ database connection before running view, it will also attaches
+ manager,conn & template_path properties to self
+ """
+ @wraps(f)
+ def wrap(*args, **kwargs):
+ # Here args[0] will hold self & kwargs will hold gid,sid,did
+ self = args[0]
+ self.manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(
+ kwargs['sid']
+ )
+ self.conn = self.manager.connection(did=kwargs['did'])
+ # If DB not connected then return error to browser
+ if not self.conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+
+ # We need datlastsysoid to check if current index is system index
+ self.datlastsysoid = self.manager.db_info[kwargs['did']]['datlastsysoid']
+
+ # we will set template path for sql scripts
+ self.template_path = 'index/sql/9.1_plus'
+
+ # We need parent's name eg table name and schema name
+ # when we create new index in update we can fetch it using
+ # property sql
+ SQL = render_template("/".join([self.template_path,
+ 'get_parent.sql']),
+ tid=kwargs['tid'])
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=rset)
+
+ for row in rset['rows']:
+ self.schema = row['schema']
+ self.table = row['table']
+
+ return f(*args, **kwargs)
+
+ return wrap
+
+ @check_precondition
+ def get_collations(self, gid, sid, did, scid, tid, idx=None):
+ """
+ This function will return list of collation available
+ via AJAX response
+ """
+ res = [{'label': '', 'value': ''}]
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_collations.sql']))
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in rset['rows']:
+ res.append(
+ {'label': row['collation'],
+ 'value': row['collation']}
+ )
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def get_access_methods(self, gid, sid, did, scid, tid, idx=None):
+ """
+ This function will return list of access methods available
+ via AJAX response
+ """
+ res = [{'label': '', 'value': ''}]
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_am.sql']))
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in rset['rows']:
+ res.append(
+ {'label': row['amname'],
+ 'value': row['amname']}
+ )
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def get_op_class(self, gid, sid, did, scid, tid, idx=None):
+ """
+ This function will return list of op_class method
+ for each access methods available via AJAX response
+ """
+ res = dict()
+ try:
+ # Fetching all the access methods
+ SQL = render_template("/".join([self.template_path,
+ 'get_am.sql']))
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in rset['rows']:
+ # Fetching all the op_classes for each access method
+ SQL = render_template("/".join([self.template_path,
+ 'get_op_class.sql']),
+ oid=row['oid'])
+ status, result = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ op_class_list = [{'label': '', 'value': ''}]
+
+ for r in result['rows']:
+ op_class_list.append({'label': r['opcname'],
+ 'value': r['opcname']})
+
+ # Append op_class list in main result as collection
+ res[row['amname']] = op_class_list
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+
+ @check_precondition
+ def list(self, gid, sid, did, scid, tid):
+ """
+ This function is used to list all the schema nodes within that collection.
+
+ Args:
+ gid: Server group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+
+ Returns:
+ JSON of available schema nodes
+ """
+
+ SQL = render_template("/".join([self.template_path,
+ 'nodes.sql']), tid=tid)
+ 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, tid):
+ """
+ This function will used to create all the child node within that collection.
+ Here it will create all the schema node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+
+ Returns:
+ JSON of available schema child nodes
+ """
+ res = []
+ SQL = render_template("/".join([self.template_path,
+ 'nodes.sql']), tid=tid)
+ 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'],
+ tid,
+ row['name'],
+ icon="icon-index"
+ ))
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ def _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
+ """
+
+ SQL = render_template("/".join([self.template_path,
+ '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 = []
+ cnt = 1
+ 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
+
+
+ @check_precondition
+ def properties(self, gid, sid, did, scid, tid, idx):
+ """
+ This function will show the properties of the selected schema node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ scid: Schema ID
+ tid: Table ID
+ idx: Index ID
+
+ Returns:
+ JSON of selected schema node
+ """
+
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']),
+ tid=tid, idx=idx,
+ datlastsysoid=self.datlastsysoid)
+
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ # Making copy of output for future use
+ data = dict(res['rows'][0])
+
+ # Add column details for current index
+ data = self._column_details(idx, data)
+
+ return ajax_response(
+ response=data,
+ status=200
+ )
+
+ @check_precondition
+ def create(self, gid, sid, did, scid, tid):
+ """
+ This function will creates new the schema object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ """
+ data = request.form if request.form else json.loads(
+ request.data.decode()
+ )
+ required_args = {
+ 'name': 'Name',
+ 'columns': 'Columns'
+ }
+
+ for arg in required_args:
+ err_msg = None
+ if arg == 'columns' and len(data['columns']) < 1:
+ err_msg = "You must provide one or more column to create index"
+
+ if arg not in data:
+ err_msg = "Couldn't find the required parameter (%s)." % \
+ required_args[arg]
+ # Check if we have at least one column
+ if err_msg is not None:
+ return make_json_response(
+ status=410,
+ success=0,
+ errormsg=gettext(err_msg)
+ )
+
+ # Adding parent into data dict, will be using it while creating sql
+ data['schema'] = self.schema
+ data['table'] = self.table
+
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'create.sql']),
+ data=data, conn=self.conn)
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ # If user chooses concurrent index then we can not run it along
+ # with other alter statments so we will separate alter index part
+ SQL = render_template("/".join([self.template_path,
+ 'alter.sql']),
+ data=data, conn=self.conn)
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ # we need oid to to add object in tree at browser
+ SQL = render_template("/".join([self.template_path,
+ 'get_oid.sql']),
+ tid=tid, data=data)
+ status, idx = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=tid)
+
+ return jsonify(
+ node=self.blueprint.generate_browser_node(
+ idx,
+ scid,
+ data['name'],
+ icon="icon-index"
+ )
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def delete(self, gid, sid, did, scid, tid, idx):
+ """
+ This function will updates existing the schema object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ idx: Index ID
+ """
+ # Below will decide if it's simple drop or drop with cascade call
+ if self.cmd == 'delete':
+ # This is a cascade operation
+ cascade = True
+ else:
+ cascade = False
+
+ try:
+ # We will first fetch the index name for current request
+ # so that we create template for dropping index
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']),
+ tid=tid, idx=idx,
+ datlastsysoid=self.datlastsysoid)
+
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ data = dict(res['rows'][0])
+
+ SQL = render_template("/".join([self.template_path,
+ 'delete.sql']),
+ data=data, conn=self.conn, 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("Index is dropped"),
+ data={
+ 'id': idx,
+ 'tid': tid
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def update(self, gid, sid, did, scid, tid, idx):
+ """
+ This function will updates existing the schema object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ idx: Index ID
+ """
+ data = request.form if request.form else json.loads(request.data.decode())
+
+ try:
+ SQL = self.get_sql(scid, tid, idx, data)
+ if SQL and SQL.strip('\n') and SQL.strip(' '):
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info="Index updated",
+ data={
+ 'id': idx,
+ 'tid': tid,
+ 'scid': scid
+ }
+ )
+ else:
+ return make_json_response(
+ success=1,
+ info="Nothing to update",
+ data={
+ 'id': idx,
+ 'tid': tid,
+ 'scid': scid
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+
+ @check_precondition
+ def msql(self, gid, sid, did, scid, tid, idx=None):
+ """
+ This function will generates modified sql for schema object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ idx: Index ID (When working with existing index)
+ """
+ data = dict()
+ for k, v in request.args.items():
+ try:
+ data[k] = json.loads(v)
+ except ValueError:
+ data[k] = v
+
+ # Adding parent into data dict, will be using it while creating sql
+ data['schema'] = self.schema
+ data['table'] = self.table
+
+ try:
+ SQL = self.get_sql(scid, tid, idx, data)
+
+ if SQL and SQL.strip('\n') and SQL.strip(' '):
+ return make_json_response(
+ data=SQL,
+ status=200
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ def get_sql(self, scid, tid, idx, data):
+ """
+ This function will genrate sql from model data
+ """
+ if idx is not None:
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']),
+ tid=tid, idx=idx,
+ datlastsysoid=self.datlastsysoid)
+
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ old_data = dict(res['rows'][0])
+
+ # If name is not present in data then
+ # we will fetch it from old data, we also need schema & table name
+ if 'name' not in data:
+ data['name'] = old_data['name']
+
+ SQL = render_template(
+ "/".join([self.template_path, 'update.sql']),
+ data=data, o_data=old_data, conn=self.conn
+ )
+ else:
+ required_args = {
+ 'name': 'Name',
+ 'columns': 'Columns'
+ }
+ for arg in required_args:
+ err = False
+ if arg == 'columns' and len(data['columns']) < 1:
+ err = True
+
+ if arg not in data:
+ err = True
+ # Check if we have at least one column
+ if err:
+ return gettext('-- incomplete definition')
+
+ # If the request for new object which do not have did
+ SQL = render_template("/".join([self.template_path, 'create.sql']),
+ data=data, conn=self.conn)
+ SQL += "\n"
+ SQL += render_template("/".join([self.template_path, 'alter.sql']),
+ data=data, conn=self.conn)
+
+ return SQL
+
+ @check_precondition
+ def sql(self, gid, sid, did, scid, tid, idx):
+ """
+ This function will generates reverse engineered sql for schema object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ idx: Index ID
+ """
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']),
+ tid=tid, idx=idx,
+ datlastsysoid=self.datlastsysoid)
+
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ data = dict(res['rows'][0])
+ # Adding parent into data dict, will be using it while creating sql
+ data['schema'] = self.schema
+ data['table'] = self.table
+
+ # Add column details for current index
+ data = self._column_details(idx, data)
+
+ SQL = self.get_sql(scid, tid, None, data)
+
+ sql_header = "-- Index: {0}\n\n-- ".format(data['name'])
+ sql_header += render_template("/".join([self.template_path,
+ 'delete.sql']),
+ data=data, conn=self.conn)
+
+ SQL = sql_header + '\n\n' + SQL
+
+ return ajax_response(response=SQL)
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def dependents(self, gid, sid, did, scid, tid, idx):
+ """
+ This function get the dependents and return ajax response
+ for the schema node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ idx: Index ID
+ """
+ dependents_result = self.get_dependents(
+ self.conn, idx
+ )
+
+ return ajax_response(
+ response=dependents_result,
+ status=200
+ )
+
+ @check_precondition
+ def dependencies(self, gid, sid, did, scid, tid, idx):
+ """
+ This function get the dependencies and return ajax response
+ for the schema node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ idx: Index ID
+
+ """
+ dependencies_result = self.get_dependencies(
+ self.conn, idx
+ )
+
+ return ajax_response(
+ response=dependencies_result,
+ status=200
+ )
+
+IndexesView.register_node_view(blueprint)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/static/img/coll-index.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/static/img/coll-index.png
new file mode 100644
index 0000000000000000000000000000000000000000..bb1513c8287e2ee5b39c36836fc01636ed35cf5f
GIT binary patch
literal 468
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbMfoB*E?S0LTF`o3ZPMa`Pi^5sX2
z>o4lmo>i<kCRV(kJ8wtFmZzrmmrp<ccKpfLLk~aix$|l3^$+VVy<2|v&4LrJidH^6
z@$Bo-$6pTI{~QpL<QJ9X6Op*>#s{~Mc;}!vhk&@*M_wFx^kv_@PdjdY*m&jL%JXj)
zpL#Xt=!@wGpZi26dW9#rhbFAP@YX3X&fY(E*X@s+uf1P$;qB7Xujd_mIpfgtu2Mf2
zp!*n0g8YIR9G=}s19CVEJR*yMv<Dcwoy@iaGV(oL977~7Cnp?W=<zwDqrtQ$=Z(x9
zj~cx^i3d#_1)o2B`1FyRgNw8HFpEpT)q?ItD<6tE2Ork$TgH~b9njmdWXhJFHFIoS
zgadk;7ERjJwQ82t5w!(UyuP}*vazK*qK|z2!pqa!lUTDOd#6MPBg0g2&X1Sx0WD@=
zP%UwdC`m~yNwrEYN(E93Mh1okx`sx&28JO<2397<Rwky}2Bua92J_zOtwPa|o1c=I
iRteEyU<uX$RBd8qU<T1}YI^7zkQJV;elF{r5}E-1(!&P;
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/static/img/index.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/static/img/index.png
new file mode 100644
index 0000000000000000000000000000000000000000..a239c125109ca8d73b6afa840b5740a8bb06934e
GIT binary patch
literal 562
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbMf+W?;sS0LTF`o3ZPMa`Pi^5sX2
z>o4lmo>i<kCRV(kJ8y?+{pF4=PftJpcH-&RqmREFeDHbi-A_Aie%yTR{klu<R-Sva
z<kahcm?Zz`B)_OcpNPbrw?2A=CAfvey9CEM1;#Bn{_^Crug9KzJ@oL)zI&gxTz|j*
z^1D^%-z+`-dclcTbB@07i%Rl|O!SIK*l_usOK`kXP@F?RoSlE{j6=^4Kl-x&{^wn{
zKW@A6VZ)Vot1rA=cINfMldt9;eL3^c^Qi}(c}FC8h9$U%Cb)*gFF*UnF(A&~KX&Hf
z=X>sa+J5uHrmOGQUVOXa?3=}>Ud=o9a@OG&(+)g;b8MqB(8G)+L4Lsu4$p3+0Xdun
z9+AaB+5?Q;PG;Ky88x0Rjv*44lM@t}42%pnFW}g)X=8DL5_f?jgZYf}hYz1VuCJh>
zqN8+*RZu{9`h+QyrcInWxt&8pLrc?p<%(4+vooAnM7C}zT~hk>3olQv0@E?IWoggE
zriEQI+a|y$Y+cRnuAVL)&N097^x3<IFQ2}B{5pS<HnaIE?Gp?Pv%m0#sEbTm4|IoW
ziEBhjN@7W>RdP`(kYX@0Ff`CLG}1LN3^6jWGBLI?G1WFOwK6c6_fBsWiiX_$l+3hB
ihz0{oum+%N6DtEVh=x<sL)QQ`FnGH9xvX<aXaWG5fB2*T
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js
new file mode 100644
index 0000000..994f9c0
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js
@@ -0,0 +1,362 @@
+define(
+ ['jquery', 'underscore', 'underscore.string', 'pgadmin', 'pgadmin.browser',
+ 'backform', 'alertify', 'pgadmin.browser.collection'],
+function($, _, S, pgAdmin, pgBrowser, Backform, alertify) {
+
+ if (!pgBrowser.Nodes['coll-index']) {
+ var databases = pgAdmin.Browser.Nodes['coll-index'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'index',
+ label: '{{ _('Indexes') }}',
+ type: 'coll-index',
+ columns: ['name', 'oid', 'description']
+ });
+ };
+
+ // Model to create column collection control
+ var ColumnModel = pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ colname: undefined,
+ collspcname: undefined,
+ op_class: undefined,
+ sort_order: false,
+ nulls: false
+ },
+ schema: [
+ {
+ id: 'colname', label:'{{ _('Column') }}', cell: 'string',
+ type: 'text', disabled: 'inSchema', editable: false,
+ control: 'node-list-by-name', node: 'column'
+ },{
+ id: 'collspcname', label:'{{ _('Collation') }}', cell: 'string',
+ type: 'text', disabled: 'inSchema', editable: false,
+ control: 'node-ajax-options', url: 'get_collations', node: 'index'
+ },{
+ id: 'op_class', label:'{{ _('Operator class') }}', cell: 'string',
+ type: 'text', disabled: 'checkAccessMethod', editable: false,
+ control: 'node-ajax-options', url: 'get_op_class', node: 'index',
+ deps: ['amname'], transform: function(data) {
+ /* We need to extract data from collection according
+ * to access method selected by user if not selected
+ * send btree related op_class options
+ */
+ var amname = this.model.handler.get('amname'),
+ options = data['btree'];
+
+ if(_.isUndefined(amname))
+ return options;
+
+ _.each(data, function(v, k) {
+ if(amname === k) {
+ options = v;
+ }
+ });
+ return options;
+ }
+ },{
+ id: 'sort_order', label:'{{ _('Sort order') }}', cell: 'switch',
+ type: 'switch', disabled: 'checkAccessMethod', editable: false,
+ deps: ['amname'],
+ options: {
+ 'onText': 'DESC', 'offText': 'ASC',
+ 'onColor': 'success', 'offColor': 'default',
+ 'size': 'small'
+ }
+ },{
+ id: 'nulls', label:'{{ _('NULLs') }}', cell: 'switch',
+ type: 'switch', disabled: 'checkAccessMethod', editable: false,
+ deps: ['amname', 'sort_order'],
+ options: {
+ 'onText': 'FIRST', 'offText': 'LAST',
+ 'onColor': 'success', 'offColor': 'default',
+ 'size': 'small'
+ }
+ }
+ ],
+ // We will check if we are under schema node
+ inSchema: function() {
+ if(this.node_info && 'catalog' in this.node_info) {
+ return true;
+ }
+ return false;
+ },
+ // We will check if we are under schema node & in 'create' mode
+ inSchemaWithModelCheck: function(m) {
+ if(this.node_info && 'schema' in this.node_info) {
+ // We will disable control if it's in 'edit' mode
+ if (m.isNew()) {
+ return false;
+ } else {
+ return true;
+ }
+ }
+ return true;
+ },
+ // We will check if we are under schema node and added condition
+ checkAccessMethod: function(m) {
+ //Access method is empty or btree then do not disable field
+ var parent_model = m.handler;
+ if(!m.inSchema.apply(this, [m]) &&
+ (_.isUndefined(parent_model.get('amname')) ||
+ _.isNull(parent_model.get('amname')) ||
+ String(parent_model.get('amname')).replace(/^\s+|\s+$/g, '') == '' ||
+ parent_model.get('amname') === 'btree')) {
+ // We need to set nulls to true if sort_order is set to desc
+ // nulls first is default for desc
+ if(m.get('sort_order') == true) {
+ setTimeout(function() { m.set('nulls', true) }, 10);
+ } else {
+ setTimeout(function() { m.set('nulls', false) }, 10);
+ }
+ return false;
+ }
+ return true;
+ },
+ });
+
+ if (!pgBrowser.Nodes['index']) {
+ pgAdmin.Browser.Nodes['index'] = pgAdmin.Browser.Node.extend({
+ parent_type: ['table', 'materialized_view'],
+ collection_type: ['coll-table'],
+ type: 'index',
+ label: '{{ _('Index') }}',
+ hasSQL: true,
+ hasDepends: true,
+ Init: function() {
+ /* Avoid mulitple registration of menus */
+ if (this.initialized)
+ return;
+
+ this.initialized = true;
+
+ pgBrowser.add_menus([{
+ name: 'create_index_on_coll', node: 'coll-index', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Index...') }}',
+ icon: 'wcTabIcon icon-index', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_index', node: 'index', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Index...') }}',
+ icon: 'wcTabIcon icon-index', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_index_onTable', node: 'table', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Index...') }}',
+ icon: 'wcTabIcon icon-index', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_index', node: 'materialized_view', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 5, label: '{{ _('Index...') }}',
+ icon: 'wcTabIcon icon-index', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ }
+ ]);
+ },
+ canDrop: pgBrowser.Nodes['schema'].canChildDrop,
+ canDropCascade: pgBrowser.Nodes['schema'].canChildDrop,
+ model: pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ name: undefined,
+ nspname: undefined,
+ tabname: undefined,
+ spcname: undefined
+ },
+ schema: [{
+ id: 'name', label: '{{ _('Name') }}', cell: 'string',
+ type: 'text', disabled: 'inSchema'
+ },{
+ id: 'oid', label:'{{ _('OID') }}', cell: 'string',
+ type: 'int', disabled: true, mode: ['edit', 'properties']
+ },{
+ id: 'spcname', label:'{{ _('Tablespace') }}', cell: 'string',
+ control: 'node-list-by-name', node: 'tablespace',
+ type: 'text', mode: ['properties', 'create', 'edit'],
+ disabled: 'inSchema', filter: function(d) {
+ // If tablespace name is not "pg_global" then we need to exclude them
+ if(d && d.label.match(/pg_global/))
+ {
+ return false;
+ }
+ return true;
+ }
+ },{
+ id: 'amname', label:'{{ _('Access Method') }}', cell: 'string',
+ type: 'text', mode: ['properties', 'create', 'edit'],
+ disabled: 'inSchemaWithModelCheck', url: 'get_access_methods',
+ group: '{{ _('Definition') }}',
+ control: Backform.NodeAjaxOptionsControl.extend({
+ // When access method changes we need to clear columns collection
+ onChange: function() {
+ Backform.NodeAjaxOptionsControl.prototype.onChange.apply(this, arguments);
+ var self = this,
+ // current access method
+ current_am = self.model.get('amname'),
+ // previous access method
+ previous_am = self.model.previous('amname');
+ if (!_.isUndefined(current_am) &&
+ (current_am != 'btree' || current_am != '')) {
+ var msg = '{{ _('Changing access method will clear columns collection') }}';
+ alertify.confirm(msg, function (e) {
+ if (e) {
+ // User clicks Ok, lets clear collection
+ var column_collection = self.model.get('columns');
+ column_collection.reset();
+ } else {
+ // set previous value again in combo box
+ self.model.set('amname', previous_am, { silent: true });
+ }
+ });
+ }
+ }
+ })
+ },{
+ id: 'cols', label:'{{ _('Columns') }}', cell: 'string',
+ type: 'text', disabled: 'inSchema', mode: ['properties']
+ },{
+ id: 'fillfactor', label:'{{ _('Fill factor') }}', cell: 'string',
+ type: 'int', disabled: 'inSchema', mode: ['create', 'edit', 'properties'],
+ min: 10, max:100, group: '{{ _('Definition') }}'
+ },{
+ id: 'indisunique', label:'{{ _('Unique?') }}', cell: 'string',
+ type: 'switch', disabled: 'inSchemaWithModelCheck',
+ group: '{{ _('Definition') }}'
+ },{
+ id: 'indisclustered', label:'{{ _('Clustered?') }}', cell: 'string',
+ type: 'switch', disabled: 'inSchema',
+ group: '{{ _('Definition') }}'
+ },{
+ id: 'indisvalid', label:'{{ _('Valid?') }}', cell: 'string',
+ type: 'switch', disabled: true, mode: ['properties']
+ },{
+ id: 'indisprimary', label:'{{ _('Primary?') }}', cell: 'string',
+ type: 'switch', disabled: true, mode: ['properties']
+ },{
+ id: 'is_sys_idx', label:'{{ _('System index?') }}', cell: 'string',
+ type: 'switch', disabled: true, mode: ['properties']
+ },{
+ id: 'isconcurrent', label:'{{ _('Concurrent build?') }}', cell: 'string',
+ type: 'switch', disabled: 'inSchemaWithModelCheck',
+ mode: ['create', 'edit'], group: '{{ _('Definition') }}'
+ },{
+ id: 'indconstraint', label:'{{ _('Constraint') }}', cell: 'string',
+ type: 'text', disabled: 'inSchemaWithModelCheck', mode: ['create', 'edit'],
+ control: 'sql-field', visible: true, group: '{{ _('Definition') }}'
+ },{
+ id: 'columns', label: 'Columns', type: 'collection',
+ group: '{{ _('Definition') }}', model: ColumnModel, mode: ['edit', 'create'],
+ canAdd: function(m) {
+ // We will disable it if it's in 'edit' mode
+ if (m.isNew()) {
+ return true;
+ } else {
+ return false;
+ }
+ },
+ canEdit: function(m) {
+ // We will disable it if it's in 'edit' mode
+ if (m.isNew()) {
+ return true;
+ } else {
+ return false;
+ }
+ },
+ canDelete: function(m) {
+ // We will disable it if it's in 'edit' mode
+ if (m.isNew()) {
+ return true;
+ } else {
+ return false;
+ }
+ },
+ control: 'unique-col-collection', uniqueCol : ['colname']
+ },{
+ id: 'description', label:'{{ _('Comment') }}', cell: 'string',
+ type: 'multiline', mode: ['properties', 'create', 'edit'],
+ disabled: 'inSchema'
+ }
+ ],
+ validate: function() {
+ var err = {},
+ changedAttrs = this.changed,
+ msg = undefined;
+ this.errorModel.clear();
+
+ if (_.has(changedAttrs,this.get('name'))
+ && _.isUndefined(this.get('name'))
+ || String(this.get('name')).replace(/^\s+|\s+$/g, '') == '') {
+ msg = '{{ _('Name can not be empty!') }}';
+ this.errorModel.set('name', msg);
+ return msg;
+ }
+ return null;
+ },
+ // We will check if we are under schema node & in 'create' mode
+ inSchema: function() {
+ if(this.node_info && 'catalog' in this.node_info) {
+ return true;
+ }
+ return false;
+ },
+ // We will check if we are under schema node & in 'create' mode
+ inSchemaWithModelCheck: function(m) {
+ if(this.node_info && 'schema' in this.node_info) {
+ // We will disable control if it's in 'edit' mode
+ if (m.isNew()) {
+ return false;
+ } else {
+ return true;
+ }
+ }
+ return true;
+ },
+ // Checks weather to enable/disable control
+ inSchemaWithColumnCheck: function(m) {
+ if(this.node_info && 'schema' in this.node_info) {
+ // We will disable control if it's system columns
+ // ie: it's position is less then 1
+ if (m.isNew()) {
+ return false;
+ } else {
+ // if we are in edit mode
+ if (!_.isUndefined(m.get('attnum')) && m.get('attnum') >= 1 ) {
+ return false;
+ } else {
+ return true;
+ }
+ }
+ }
+ return true;
+ }
+ }),
+ // Below function will enable right click menu for creating column
+ 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, parents = [];
+ // To iterate over tree to check parent node
+ while (i) {
+ // If it is schema then allow user to c reate table
+ if (_.indexOf(['schema'], d._type) > -1)
+ return true;
+ parents.push(d._type);
+ i = t.hasParent(i) ? t.parent(i) : null;
+ d = i ? t.itemData(i) : null;
+ }
+ // If node is under catalog then do not allow 'create' menu
+ if (_.indexOf(parents, 'catalog') > -1) {
+ return false;
+ } else {
+ return true;
+ }
+ }
+ });
+ }
+
+ return pgBrowser.Nodes['index'];
+});
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/alter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/alter.sql
new file mode 100644
index 0000000..93f323e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/alter.sql
@@ -0,0 +1,9 @@
+{## Alter index to use cluster type ##}
+{% if data.indisclustered %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ CLUSTER ON {{conn|qtIdent(data.name)}};
+{% endif %}
+{## Changes description ##}
+{% if data.description %}
+COMMENT ON INDEX {{conn|qtIdent(data.name)}}
+ IS {{data.description|qtLiteral}};{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/backend_support.sql
new file mode 100644
index 0000000..6f66ed1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/backend_support.sql
@@ -0,0 +1,9 @@
+{#=============Checks if it is materialized view========#}
+{% if vid %}
+SELECT
+ CASE WHEN c.relkind = 'm' THEN True ELSE False END As m_view
+FROM
+ pg_class c
+WHERE
+ c.oid = {{ vid }}::oid
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/column_details.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/column_details.sql
new file mode 100644
index 0000000..2cf540a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/column_details.sql
@@ -0,0 +1,30 @@
+SELECT
+ i.indexrelid,
+ CASE i.indoption[i.attnum - 1]
+ WHEN 0 THEN ARRAY['ASC', 'NULLS LAST']
+ WHEN 1 THEN ARRAY['DESC', 'NULLS FIRST']
+ WHEN 2 THEN ARRAY['ASC', 'NULLS FIRST']
+ WHEN 3 THEN ARRAY['DESC', 'NULLS ']
+ ELSE ARRAY['UNKNOWN OPTION' || i.indoption[i.attnum - 1], '']
+ END::text[] AS options,
+ i.attnum,
+ pg_get_indexdef(i.indexrelid, i.attnum, true) as attdef,
+ CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname,
+ op.oprname AS oprname,
+ CASE WHEN length(nspc.nspname) > 0 AND length(coll.collname) > 0 THEN
+ concat(quote_ident(nspc.nspname), '.', quote_ident(coll.collname))
+ ELSE '' END AS collnspname
+FROM (
+ SELECT
+ indexrelid, i.indoption, i.indclass,
+ unnest(ARRAY(SELECT generate_series(1, i.indnatts) AS n)) AS attnum
+ FROM
+ pg_index i
+ WHERE i.indexrelid = {{idx}}::OID
+) i
+ LEFT JOIN pg_opclass o ON (o.oid = i.indclass[i.attnum - 1])
+ LEFT OUTER JOIN pg_constraint c ON (c.conindid = i.indexrelid)
+ LEFT OUTER JOIN pg_operator op ON (op.oid = c.conexclop[i.attnum])
+ LEFT JOIN pg_attribute a ON (a.attrelid = i.indexrelid AND a.attnum = i.attnum)
+ LEFT OUTER JOIN pg_collation coll ON a.attcollation=coll.oid
+ LEFT OUTER JOIN pg_namespace nspc ON coll.collnamespace=nspc.oid;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/create.sql
new file mode 100644
index 0000000..4471a84
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/create.sql
@@ -0,0 +1,12 @@
+CREATE {% if data.indisunique %}UNIQUE {% endif %}INDEX {% if data.isconcurrent %}CONCURRENTLY {% endif %}{{conn|qtIdent(data.name)}}
+ ON {{conn|qtIdent(data.schema, data.table)}} {% if data.amname %}USING {{conn|qtIdent(data.amname)}}{% endif %}
+
+ ({% for c in data.columns %}{% if loop.index != 1 %}, {% endif %}{{conn|qtIdent(c.colname)}}{% if c.collspcname %} COLLATE {{c.collspcname}} {% endif %}{% if c.op_class %}
+{{c.op_class}}{% endif %}{% if c.sort_order is defined %}{% if c.sort_order %} DESC{% else %} ASC{% endif %}{% endif %}{% if c.nulls is defined %} NULLS {% if c.nulls %}
+FIRST{% else %}LAST{% endif %}{% endif %}{% endfor %}){% if data.fillfactor %}
+
+ WITH (FILLFACTOR={{data.fillfactor}}){% endif %}{% if data.spcname %}
+
+ TABLESPACE {{data.spcname}}{% endif %}{% if data.indconstraint %}
+
+ WHERE {{data.indconstraint}}{% endif %};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/delete.sql
new file mode 100644
index 0000000..f3808fa
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/delete.sql
@@ -0,0 +1 @@
+DROP INDEX {{conn|qtIdent(data.nspname, data.name)}}{% if cascade %} cascade{% endif %};
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/get_am.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/get_am.sql
new file mode 100644
index 0000000..5bb579b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/get_am.sql
@@ -0,0 +1,3 @@
+-- Fetches access methods
+SELECT oid, amname
+FROM pg_am
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/get_collations.sql
new file mode 100644
index 0000000..9f5569d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/get_collations.sql
@@ -0,0 +1,12 @@
+SELECT --nspname, collname,
+ CASE WHEN length(nspname) > 0 AND length(collname) > 0 THEN
+ concat(quote_ident(nspname), '.', quote_ident(collname))
+ ELSE '' END AS collation
+FROM
+ pg_collation c,
+ pg_namespace n
+WHERE
+ c.collnamespace=n.oid
+ORDER BY
+ nspname,
+ collname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/get_oid.sql
new file mode 100644
index 0000000..c32402f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/get_oid.sql
@@ -0,0 +1,7 @@
+SELECT DISTINCT ON(cls.relname) cls.oid
+FROM pg_index idx
+ JOIN pg_class cls ON cls.oid=indexrelid
+ JOIN pg_class tab ON tab.oid=indrelid
+ JOIN pg_namespace n ON n.oid=tab.relnamespace
+WHERE indrelid = {{tid}}::OID
+ AND cls.relname = {{data.name|qtLiteral}};
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/get_op_class.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/get_op_class.sql
new file mode 100644
index 0000000..af0133a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/get_op_class.sql
@@ -0,0 +1,5 @@
+SELECT opcname, opcmethod
+FROM pg_opclass
+ WHERE opcmethod = {{oid}}::OID
+ AND NOT opcdefault
+ ORDER BY 1;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/get_parent.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/get_parent.sql
new file mode 100644
index 0000000..5dd5d3c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/get_parent.sql
@@ -0,0 +1,5 @@
+SELECT nsp.nspname AS schema ,rel.relname AS table
+FROM pg_class rel
+ JOIN pg_namespace nsp
+ ON rel.relnamespace = nsp.oid::int
+ WHERE rel.oid = {{tid}}::int
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/nodes.sql
new file mode 100644
index 0000000..5da06f2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/nodes.sql
@@ -0,0 +1,11 @@
+SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name
+FROM pg_index idx
+ JOIN pg_class cls ON cls.oid=indexrelid
+ JOIN pg_class tab ON tab.oid=indrelid
+ LEFT OUTER JOIN pg_tablespace ta on ta.oid=cls.reltablespace
+ JOIN pg_namespace n ON n.oid=tab.relnamespace
+ JOIN pg_am am ON am.oid=cls.relam
+ LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
+ LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
+WHERE indrelid = {{tid}}::OID
+ ORDER BY cls.relname
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/properties.sql
new file mode 100644
index 0000000..1185f74
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/properties.sql
@@ -0,0 +1,23 @@
+SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name, indrelid, indkey, indisclustered,
+ indisvalid, indisunique, indisprimary, n.nspname,indnatts,cls.reltablespace AS spcoid,
+ COALESCE(spcname, 'pg_default') as spcname, tab.relname as tabname, indclass, con.oid AS conoid,
+ CASE WHEN contype IN ('p', 'u', 'x') THEN desp.description
+ ELSE des.description END AS description,
+ pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable, condeferred, amname,
+ substring(array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor
+{% if datlastsysoid %}
+ ,(CASE WHEN cls.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_idx
+{% endif %}
+FROM pg_index idx
+ JOIN pg_class cls ON cls.oid=indexrelid
+ JOIN pg_class tab ON tab.oid=indrelid
+ LEFT OUTER JOIN pg_tablespace ta on ta.oid=cls.reltablespace
+ JOIN pg_namespace n ON n.oid=tab.relnamespace
+ JOIN pg_am am ON am.oid=cls.relam
+ LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
+ LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
+ LEFT OUTER JOIN pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)
+ LEFT OUTER JOIN pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
+WHERE indrelid = {{tid}}::OID
+ {% if idx %}AND cls.oid = {{idx}}::OID {% endif %}
+ ORDER BY cls.relname
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/update.sql
new file mode 100644
index 0000000..efc51a3
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/sql/9.1_plus/update.sql
@@ -0,0 +1,24 @@
+{## Changes name ##}
+{% if data.name and o_data.name != data.name %}
+ALTER INDEX {{conn|qtIdent(data.schema, o_data.name)}}
+ RENAME TO {{conn|qtIdent(data.name)}};
+{% endif %}
+{## Changes fillfactor ##}
+{% if data.fillfactor and o_data.fillfactor != data.fillfactor %}
+ALTER INDEX {{conn|qtIdent(data.schema, data.name)}}
+ SET (FILLFACTOR={{data.fillfactor}});
+{% endif %}
+{## Changes tablespace ##}
+{% if data.spcname and o_data.spcname != data.spcname %}
+ALTER INDEX {{conn|qtIdent(data.schema, data.name)}}
+ SET TABLESPACE {{conn|qtIdent(data.spcname)}};
+{% endif %}
+{## Alter index to use cluster type ##}
+{% if data.indisclustered and o_data.indisclustered != data.indisclustered %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ CLUSTER ON {{conn|qtIdent(data.name)}};
+{% endif %}
+{## Changes description ##}
+{% if data.description and o_data.description != data.description %}
+COMMENT ON INDEX {{conn|qtIdent(data.name)}}
+ IS {{data.description|qtLiteral}};{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/__init__.py
new file mode 100644
index 0000000..82c5c61
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/__init__.py
@@ -0,0 +1,489 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+"""Implements Rule Node"""
+
+import json
+from flask import render_template, make_response, request, jsonify
+from flask.ext.babel import gettext
+from pgadmin.utils.ajax import make_json_response, \
+ make_response as ajax_response, internal_server_error
+from pgadmin.browser.utils import PGChildNodeView
+import pgadmin.browser.server_groups.servers.databases.schemas as schemas
+from pgadmin.browser.server_groups.servers.databases.schemas.utils import \
+ parse_rule_definition
+from pgadmin.browser.collection import CollectionNodeModule
+from pgadmin.utils.ajax import precondition_required
+from pgadmin.utils.driver import get_driver
+from config import PG_DEFAULT_DRIVER
+from functools import wraps
+
+
+class RuleModule(CollectionNodeModule):
+ """
+ class RuleModule(CollectionNodeModule):
+
+ A rule collection Node which inherits CollectionNodeModule
+ class and define methods:
+ get_nodes - To generate collection node.
+ script_load - tells when to load js file.
+ csssnppets - add css to page
+ """
+ NODE_TYPE = 'rule'
+ COLLECTION_LABEL = gettext("Rules")
+
+ def __init__(self, *args, **kwargs):
+ self.min_ver = None
+ self.max_ver = None
+
+ super(RuleModule, self).__init__(*args, **kwargs)
+
+ def BackendSupported(self, manager, **kwargs):
+ """
+ Load this module if tid is view, we will not load it under
+ material view
+ """
+ if super(RuleModule, self).BackendSupported(manager, **kwargs):
+ conn = manager.connection(did=kwargs['did'])
+ # If DB is not connected then return error to browser
+ if not conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+
+ if 'vid' not in kwargs:
+ return True
+
+ template_path = 'rules/sql'
+
+ SQL = render_template("/".join(
+ [template_path, 'backend_support.sql']), vid=kwargs['vid'])
+ status, res = conn.execute_scalar(SQL)
+ # check if any errors
+ if not status:
+ return internal_server_error(errormsg=res)
+ # Check tid is view not material view
+ # then true, othewise false
+ if res is True:
+ return res
+ else:
+ return res
+
+ def get_nodes(self, gid, sid, did, scid, **kwargs):
+ """
+ Generate the collection node
+ """
+ assert('tid' in kwargs or 'vid' in kwargs)
+ yield self.generate_browser_collection_node(
+ kwargs['tid'] if 'tid' in kwargs else kwargs['vid']
+ )
+
+ @property
+ def node_inode(self):
+ """
+ If a node has children return True otherwise False
+ """
+ return False
+
+ @property
+ def script_load(self):
+ """
+ Load the module script for rule, when any of the database nodes are
+ initialized.
+ """
+ return schemas.SchemaModule.NODE_TYPE
+
+ @property
+ def csssnippets(self):
+ """
+ Returns a snippet of css to include in the page
+ """
+ snippets = [
+ render_template(
+ "browser/css/collection.css",
+ node_type=self.node_type,
+ _=gettext
+ ),
+ render_template(
+ "rules/css/rule.css",
+ node_type=self.node_type,
+ _=gettext
+ )
+ ]
+
+ for submodule in self.submodules:
+ snippets.extend(submodule.csssnippets)
+
+ return snippets
+
+
+# Create blueprint of RuleModule.
+blueprint = RuleModule(__name__)
+
+
+class RuleView(PGChildNodeView):
+ """
+ This is a class for rule node which inherits the
+ properties and methods from PGChildNodeView class and define
+ various methods to list, create, update and delete rule.
+
+ Variables:
+ ---------
+ * node_type - tells which type of node it is
+ * parent_ids - id with its type and name of parent nodes
+ * ids - id with type and name of extension module being used.
+ * operations - function routes mappings defined.
+ """
+ node_type = blueprint.node_type
+
+ parent_ids = [
+ {'type': 'int', 'id': 'gid'},
+ {'type': 'int', 'id': 'sid'},
+ {'type': 'int', 'id': 'did'},
+ {'type': 'int', 'id': 'scid'},
+ {'type': 'int', 'id': 'tid'}
+ ]
+ ids = [
+ {'type': 'int', 'id': 'rid'}
+ ]
+
+ operations = dict({
+ 'obj': [
+ {'get': 'properties', 'delete': 'delete', 'put': 'update'},
+ {'get': 'list', 'post': 'create'}
+ ],
+ 'children': [{
+ 'get': 'children'
+ }],
+ 'delete': [{'delete': 'delete'}],
+ 'nodes': [{'get': 'node'}, {'get': 'nodes'}],
+ 'sql': [{'get': 'sql'}],
+ 'msql': [{'get': 'msql'}, {'get': 'msql'}],
+ 'stats': [{'get': 'statistics'}],
+ 'dependency': [{'get': 'dependencies'}],
+ 'dependent': [{'get': 'dependents'}],
+ 'module.js': [{}, {}, {'get': 'module_js'}],
+ 'configs': [{'get': 'configs'}]
+ })
+
+ def module_js(self):
+ """
+ This property defines whether Javascript exists for this node.
+ """
+ return make_response(
+ render_template(
+ "rules/js/rules.js",
+ _=gettext
+ ),
+ 200, {'Content-Type': 'application/x-javascript'}
+ )
+
+ def check_precondition(f):
+ """
+ This function will behave as a decorator which will check the
+ database connection before running a view. It will also attach
+ manager, conn & template_path properties to self
+ """
+ @wraps(f)
+ def wrap(*args, **kwargs):
+
+ # Here args[0] will hold self & kwargs will hold gid,sid,did
+ self = args[0]
+ self.manager = get_driver(
+ PG_DEFAULT_DRIVER).connection_manager(kwargs['sid'])
+ self.conn = self.manager.connection(did=kwargs['did'])
+
+ # If DB not connected then return error to browser
+ if not self.conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+
+ self.datlastsysoid = self.manager.db_info[kwargs['did']]['datlastsysoid']
+ ver = self.manager.version
+ self.template_path = 'rules/sql'
+ return f(*args, **kwargs)
+
+ return wrap
+
+ @check_precondition
+ def list(self, gid, sid, did, scid, tid):
+ """
+ Fetch all rule properties and render into properties tab
+ """
+
+ # fetch schema name by schema id
+ SQL = render_template("/".join(
+ [self.template_path, 'properties.sql']), tid=tid)
+ 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, tid):
+ """
+ List all the rules under the Rules Collection node
+ """
+ res = []
+ SQL = render_template("/".join(
+ [self.template_path, 'properties.sql']), tid=tid)
+
+ 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'],
+ tid,
+ row['name'],
+ icon="icon-rule"
+ ))
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ @check_precondition
+ def properties(self, gid, sid, did, scid, tid, rid):
+ """
+ Fetch the properties of an individual rule and render in properties tab
+
+ """
+ SQL = render_template("/".join(
+ [self.template_path, 'properties.sql']
+ ), rid=rid, datlastsysoid=self.datlastsysoid)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return ajax_response(
+ response=parse_rule_definition(res),
+ status=200
+ )
+
+ @check_precondition
+ def create(self, gid, sid, did, scid, tid):
+ """
+ This function will create a new rule object
+ """
+ required_args = [
+ 'name',
+ ]
+
+ data = request.form if request.form else \
+ json.loads(request.data.decode())
+ for arg in required_args:
+ if arg not in data:
+ return make_json_response(
+ status=410,
+ success=0,
+ errormsg=gettext(
+ "Couldn't find the required parameter (%s)." % arg
+ )
+ )
+ try:
+ SQL = render_template("/".join(
+ [self.template_path, 'create.sql']), data=data)
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ # Fetch the rule id against rule name to display node
+ # in tree browser
+ SQL = render_template("/".join(
+ [self.template_path, 'rule_id.sql']), rule_name=data['name'])
+ status, rule_id = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=rule_id)
+ return jsonify(
+ node=self.blueprint.generate_browser_node(
+ rule_id,
+ tid,
+ data['name'],
+ icon="icon-rule"
+ )
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def update(self, gid, sid, did, scid, tid, rid):
+ """
+ This function will update a rule object
+ """
+ data = request.form if request.form else \
+ json.loads(request.data.decode())
+ SQL = self.getSQL(gid, sid, data, tid, rid)
+ try:
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info=gettext("Rule updated"),
+ data={
+ 'id': tid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def delete(self, gid, sid, did, scid, tid, rid):
+ """
+ This function will drop a rule object
+ """
+ # Below will decide if it's simple drop or drop with cascade call
+ cascade = True if self.cmd == 'delete' else False
+
+ try:
+ # Get name for rule from did
+ SQL = render_template("/".join(
+ [self.template_path, 'delete.sql']), rid=rid)
+ status, res_data = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res_data)
+ # drop rule
+ rset = res_data['rows'][0]
+ SQL = render_template("/".join(
+ [self.template_path, 'delete.sql']),
+ rulename=rset['rulename'],
+ relname=rset['relname'],
+ nspname=rset['nspname'],
+ cascade=cascade
+ )
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info=gettext("Rule dropped"),
+ data={
+ 'id': tid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def msql(self, gid, sid, did, scid, tid, rid=None):
+ """
+ This function returns modified SQL
+ """
+ data = request.args
+ SQL = self.getSQL(gid, sid, data, tid, rid)
+ return make_json_response(
+ data=SQL,
+ status=200
+ )
+
+ @check_precondition
+ def sql(self, gid, sid, did, scid, tid, rid):
+ """
+ This function will generate sql to render into the sql panel
+ """
+ SQL = render_template("/".join(
+ [self.template_path, 'properties.sql']), rid=rid)
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+ res_data = parse_rule_definition(res)
+ SQL = render_template("/".join(
+ [self.template_path, 'create.sql']),
+ data=res_data, display_comments=True)
+
+ return ajax_response(response=SQL)
+
+ def getSQL(self, gid, sid, data, tid, rid):
+ """
+ This function will generate sql from model data
+ """
+ try:
+ if rid is not None:
+ SQL = render_template("/".join(
+ [self.template_path, 'properties.sql']), rid=rid)
+ status, res = self.conn.execute_dict(SQL)
+ res_data = []
+ res_data = parse_rule_definition(res)
+ if not status:
+ return internal_server_error(errormsg=res)
+ old_data = res_data
+ SQL = render_template(
+ "/".join([self.template_path, 'update.sql']),
+ data=data, o_data=old_data
+ )
+ else:
+ SQL = render_template("/".join(
+ [self.template_path, 'create.sql']), data=data)
+ return SQL
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def dependents(self, gid, sid, did, scid, tid, rid):
+ """
+ This function gets the dependents and returns an ajax response
+ for the rule node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ tid: View ID
+ rid: Rule ID
+ """
+ dependents_result = self.get_dependents(self.conn, rid)
+ return ajax_response(
+ response=dependents_result,
+ status=200
+ )
+
+ @check_precondition
+ def dependencies(self, gid, sid, did, scid, tid, rid):
+ """
+ This function gets the dependencies and returns sn ajax response
+ for the rule node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ tid: View ID
+ rid: Rule ID
+ """
+ dependencies_result = self.get_dependencies(self.conn, rid)
+ return ajax_response(
+ response=dependencies_result,
+ status=200
+ )
+
+RuleView.register_node_view(blueprint)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/static/img/coll-rule.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/static/img/coll-rule.png
new file mode 100644
index 0000000000000000000000000000000000000000..cfe6ed27d98351a03e98451fe83e785aeb51351f
GIT binary patch
literal 357
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbL!Q-Dv1E0BJDrt0oW+5b=H{C_&@
z|C4F|pG^AyxbOed8ULTo{{Ljk|Hl*lKkj*b(Es0~_J5B$|2=B`_pssL!=`@^>;65g
z`S+m8Y=`zEpec+cL4Lsu4$p3+0Xdun9+AaB+5?Q;PG;Ky8Bv}tjv*44r}laCH7M}7
zJY3k(rBU~;KGd5<=#Js+WtXq}O?@?enTE6ko10E>S;3Z`GiP+(9r|ThaVJc2?wlhg
z<wBFqmar9VnEKaN=E$TcORXQ5+JBvSzqq!FQQCtkVR^CB37{>iC9V-ADTyViR>?)F
zK#IZ0z|cU~&`8(7FvQ5f%EZ{p#8lhB)XKnM-aEZjC>nC}Q!>*kAsP%U!5V<7O{@&e
WAR10h4_yP)z~JfX=d#Wzp$P!7cZ;e3
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/static/img/rule.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/static/img/rule.png
new file mode 100644
index 0000000000000000000000000000000000000000..8b4978090e33bbe3d5f7ed249fd93c2da34f52d6
GIT binary patch
literal 373
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbK}cz{ocE0DgsQug(ks{c>t{C_(0
z|C1^IANT%y)b{UT{r{)4{y&-e|8f7nNA3R}HvW51{r~Cg|4*j>e>~yeqt1U1oBlnh
z`S+mw|I-=&pG^AyxclFumVXax|2?Sq_n>kK%jYzp(TpWQe!&b5&u)M?oCO|{#X#Bv
zjNMLV+W{G&o-U3d5|`KZnTs_T@Hi`bDEDUl`g^`}GN)YjKDBz@O#-F|X1Becro3=L
zVnUz`J9~da#}rq6_MWOkIpP6gt8f32>#JDS-4zfcy)J)|(ya4dQ}_S3nP9`r$iw)~
zA83JUiEBhjN@7W>RdP`(kYX@0Ff`CLG}1LN3^6jWGBLI?G1WFOwK6c6_fBsWiiX_$
ml+3hBhz0{oum+%N6DtEVh=x<sL)QQ`FnGH9xvX<aXaWEjK9-vR
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/css/rule.css b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/css/rule.css
new file mode 100644
index 0000000..3d21bcf
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/css/rule.css
@@ -0,0 +1,16 @@
+.icon-rule{
+ background-image: url('{{ url_for('NODE-rule.static', filename='img/rule.png') }}') !important;
+ border-radius: 10px;
+ background-repeat: no-repeat;
+ align-content: center;
+ vertical-align: middle;
+ height: 1.3em;
+}
+
+.sql_field_height_140 {
+ height: 140px;
+}
+
+.sql_field_height_280 {
+ height: 280px;
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/js/rules.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/js/rules.js
new file mode 100644
index 0000000..db32e6c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/js/rules.js
@@ -0,0 +1,245 @@
+define(
+ ['jquery', 'underscore', 'underscore.string', 'pgadmin',
+ 'pgadmin.browser', 'codemirror'],
+
+function($, _, S, pgAdmin, pgBrowser, CodeMirror) {
+
+ /**
+ Create and add a rule collection into nodes
+ @param {variable} label - Label for Node
+ @param {variable} type - Type of Node
+ @param {variable} columns - List of columns to
+ display under under properties.
+ */
+ if (!pgBrowser.Nodes['coll-rule']) {
+ var rules = pgAdmin.Browser.Nodes['coll-rule'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'rule',
+ label: '{{ _("Rules") }}',
+ type: 'coll-rule',
+ columns: ["name", "owner", "comment"]
+ });
+ }
+
+
+ /**
+ Create and Add an Rule 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 rule option
+ in the context menu
+ @param {variable} canDropCascade - Adds drop Cascade
+ rule option in the context menu
+ */
+ if (!pgBrowser.Nodes['rule']) {
+ pgAdmin.Browser.Nodes['rule'] = pgAdmin.Browser.Node.extend({
+ parent_type: ['table','view'],
+ type: 'rule',
+ label: '{{ _("rule") }}',
+ collection_type: 'coll-table',
+ hasSQL: true,
+ hasDepends: true,
+ canDrop: pgBrowser.Nodes['schema'].canChildDrop,
+ canDropCascade: pgBrowser.Nodes['schema'].canChildDrop,
+ Init: function() {
+
+ /* Avoid mulitple registration of menus */
+ if (this.initialized)
+ return;
+
+ this.initialized = true;
+
+ /**
+ Add "create rule" menu option into context and object menu
+ for the following nodes:
+ coll-rule, rule and view and table.
+ @property {data} - Allow create rule option on schema node or
+ system rules node.
+ */
+ pgBrowser.add_menus([{
+ name: 'create_rule_on_coll', node: 'coll-rule', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _("Rule...") }}',
+ icon: 'wcTabIcon icon-rule', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_rule', node: 'view', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _("Rule...") }}',
+ icon: 'wcTabIcon icon-rule', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_rule', node: 'rule', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _("Rule...") }}',
+ icon: 'wcTabIcon icon-rule', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_rule', node: 'table', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _("Rule...") }}',
+ icon: 'wcTabIcon icon-rule', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ }
+ ]);
+ },
+
+ /**
+ Define model for the rule node and specify the node
+ properties of the model in schema.
+ */
+ model: pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ },
+ schema: [{
+ id: 'name', label: '{{ _("Name") }}',
+ type: 'text', disabled: function(m){
+ return !m.isNew();
+ }
+ },
+ {
+ id: 'oid', label:'{{ _("OID") }}',
+ type: 'text', disabled: true, mode: ['properties']
+ },
+ {
+ id: 'schema', label:'{{ _("") }}',
+ type: 'text', visible: false, disabled: function(m){
+
+ // It is used while generating sql
+ m.set('schema', m.node_info.schema.label);
+ }
+ },
+ {
+ id: 'view', label:'{{ _("") }}',
+ type: 'text', visible: false, disabled: function(m){
+
+ // It is used while generating sql
+ m.set('view', this.node_data.label);
+ }
+ },
+ {
+ id: 'comment', label:'{{ _("Comment") }}', cell: 'string', type: 'multiline'
+ },
+ {
+ id: 'event', label:'{{ _("Event") }}', control: 'select2',
+ group: '{{ _("Definition") }}', type: 'text',
+ select2: {
+ width: '100%'
+ },
+ options:[
+ {label: 'Select', value: 'Select'},
+ {label: 'Insert', value: 'Insert'},
+ {label: 'Update', value: 'Update'},
+ {label: 'Delete', value: 'Delete'}
+ ]
+ },
+ {
+ id: 'do_instead', label:'{{ _("Do Instead") }}', group: '{{ _("Definition") }}',
+ type: 'switch'
+ },
+ {
+ id: 'condition', label:'{{ _("Condition") }}',
+ type: 'text', group: '{{ _("Definition") }}',
+ mode: ['create', 'edit'], extraClasses: ['sql_field_height_140'],
+ control: Backform.SqlFieldControl
+ },
+ {
+ id: 'statements', label:'{{ _("") }}',
+ type: 'text', group: '{{ _("Statements") }}',
+ mode: ['create', 'edit'], extraClasses: ['sql_field_height_280'],
+ control: Backform.SqlFieldControl
+ },
+ {
+ id: 'system_rule', label:'{{ _("System rule?") }}',
+ type: 'switch', mode: ['properties']
+ },
+ {
+ id: 'enabled', label:'{{ _("Enabled?") }}',
+ type: 'switch', mode: ['properties']
+ }
+ ],
+ validate: function() {
+
+ // Triggers specific error messages for fields
+ var err = {},
+ errmsg,
+ field_name = this.get('name');
+ if (_.isUndefined(field_name) || _.isNull(field_name) ||
+ String(field_name).replace(/^\s+|\s+$/g, '') === '')
+ {
+ err['name'] = '{{ _("Please specify name.") }}';
+ errmsg = errmsg || err['name'];
+ this.errorModel.set('name', errmsg);
+ return errmsg;
+ }
+ else
+ {
+ this.errorModel.unset('name');
+ }
+ return null;
+ }
+ }),
+
+ // Show or hide create rule menu option on parent node
+ canCreate: function(itemData, item, data) {
+
+ // If check is false then , we will allow create menu
+ if (data && data.check === false)
+ return true;
+
+ var t = pgBrowser.tree, i = item, d = itemData;
+
+ // To iterate over tree to check parent node
+ while (i) {
+
+ // If it is schema then allow user to create rule
+ if (_.indexOf(['schema'], d._type) > -1)
+ return true;
+
+ if ('coll-rule' == d._type) {
+
+ //Check if we are not child of rule
+ prev_i = t.hasParent(i) ? t.parent(i) : null;
+ prev_d = prev_i ? t.itemData(prev_i) : null;
+ prev_j = t.hasParent(prev_i) ? t.parent(prev_i) : null;
+ prev_e = prev_j ? t.itemData(prev_j) : null;
+ prev_k = t.hasParent(prev_j) ? t.parent(prev_j) : null;
+ prev_f = prev_k ? t.itemData(prev_k) : null;
+ if( prev_f._type == 'catalog') {
+ return false;
+ } else {
+ return true;
+ }
+ }
+
+ /**
+ Check if it is view and its parent node is schema
+ then allow to create Rule
+ */
+ else if('view' == d._type){
+ prev_i = t.hasParent(i) ? t.parent(i) : null;
+ prev_d = prev_i ? t.itemData(prev_i) : null;
+ prev_j = t.hasParent(prev_i) ? t.parent(prev_i) : null;
+ prev_e = prev_j ? t.itemData(prev_j) : null;
+ if(prev_e._type == 'schema') {
+ return true;
+ }else{
+ return false;
+ }
+ }
+ i = t.hasParent(i) ? t.parent(i) : null;
+ d = i ? t.itemData(i) : null;
+ }
+
+ // By default we do not want to allow create menu
+ return true;
+
+ }
+
+ });
+ }
+
+ return pgBrowser.Nodes['coll-rule'];
+});
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/backend_support.sql
new file mode 100644
index 0000000..bb5e8d8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/backend_support.sql
@@ -0,0 +1,9 @@
+{#=============Checks if it is materialized view========#}
+{% if vid %}
+SELECT
+ CASE WHEN c.relkind = 'm' THEN False ELSE True END As m_view
+FROM
+ pg_class c
+WHERE
+ c.oid = {{ vid }}::oid
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/create.sql
new file mode 100644
index 0000000..9927e5b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/create.sql
@@ -0,0 +1,26 @@
+{# ============Create Rule============= #}
+{% if display_comments %}
+-- Rule: {{ data.name }} ON {{ conn|qtIdent(data.schema, data.name) }}
+
+-- DROP Rule {{ data.name }} ON {{ conn|qtIdent(data.schema, data.name) }};
+
+{% endif %}
+{% if data.name and data.schema and data.view %}
+CREATE OR REPLACE RULE {{ conn|qtIdent(data.name) }} AS
+ ON {{ data.event|upper if data.event else 'SELECT' }} TO {{ conn|qtIdent(data.schema, data.view) }}
+{% if data.condition %}
+ WHERE {{ data.condition }}
+{% endif %}
+ DO{% if data.is_instead == True %}
+{{ ' INSTEAD' }}
+{% else %}
+{{ '' }}
+{% endif %}
+{% if data.statements %}
+{{ data.statements.rstrip(';') }};
+{% else %}
+ NOTHING;
+{% endif %}
+{% if data.comment %}
+COMMENT ON RULE {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.view) }} IS {{ data.comment|qtLiteral }};{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/delete.sql
new file mode 100644
index 0000000..cf18913
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/delete.sql
@@ -0,0 +1,16 @@
+{# ======== Drop/Cascade Rule ========= #}
+{% if rid %}
+SELECT
+ rw.rulename,
+ cl.relname,
+ nsp.nspname
+FROM
+ pg_rewrite rw
+JOIN pg_class cl ON cl.oid=rw.ev_class
+JOIN pg_namespace nsp ON nsp.oid=cl.relnamespace
+WHERE
+ rw.oid={{ rid }};
+{% endif %}
+{% if rulename and relname and nspname %}
+DROP RULE {{ conn|qtIdent(rulename) }} ON {{ conn|qtIdent(nspname, relname) }} {% if cascade %} CASCADE {% endif %};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/properties.sql
new file mode 100644
index 0000000..afdd139
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/properties.sql
@@ -0,0 +1,27 @@
+{# =================== Fetch Rules ==================== #}
+{% if tid or rid %}
+SELECT
+ rw.oid AS oid,
+ rw.rulename AS name,
+ relname AS view,
+ CASE WHEN relkind = 'r' THEN TRUE ELSE FALSE END AS parentistable,
+ nspname AS schema,
+ description AS comment,
+ {# ===== Check whether it is system rule or not ===== #}
+ CASE WHEN rw.rulename = '_RETURN' THEN True ELSE False END AS system_rule,
+ CASE WHEN rw.ev_enabled != 'D' THEN True ELSE False END AS enabled,
+ pg_get_ruledef(rw.oid, true) AS definition
+FROM
+ pg_rewrite rw
+JOIN pg_class cl ON cl.oid=rw.ev_class
+JOIN pg_namespace nsp ON nsp.oid=cl.relnamespace
+LEFT OUTER JOIN pg_description des ON (des.objoid=rw.oid AND des.classoid='pg_rewrite'::regclass)
+WHERE
+ {% if tid %}
+ ev_class = {{ tid }}
+ {% elif rid %}
+ rw.oid = {{ rid }}
+ {% endif %}
+ORDER BY
+ rw.rulename
+ {% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/rule_id.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/rule_id.sql
new file mode 100644
index 0000000..3dc6dba
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/rule_id.sql
@@ -0,0 +1,9 @@
+{#========Below will provide rule id for last created rule========#}
+{% if rule_name %}
+SELECT
+ rw.oid
+FROM
+ pg_rewrite rw
+WHERE
+ rw.rulename={{ rule_name|qtLiteral }}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/update.sql
new file mode 100644
index 0000000..c0d49a8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/rules/templates/rules/sql/update.sql
@@ -0,0 +1,22 @@
+{# ===== Update Rule ===== #}
+{% if data.event or data.do_instead or data.condition %}
+CREATE OR REPLACE RULE {{ conn|qtIdent(o_data.name) }} AS
+ ON {% if data.event and data.event != o_data.event %}{{ data.event|upper }}{% else %}{{ o_data.event|upper }}{% endif %}
+ TO {{ conn|qtIdent(o_data.schema, o_data.view) }}
+{% if data.condition and o_data.condition != data.condition %}
+ WHERE {{ data.condition }}
+{% elif data.condition is not defined and o_data.condition %}
+ WHERE {{ o_data.condition }}
+{% endif %}
+ DO{% if data.do_instead in ['true', True] %}{{ ' INSTEAD' }}{% else %}{{ ' ' }}
+{% endif %}
+{% if data.statements and data.statements != o_data.statements %}
+ {{ data.statements.rstrip(';') }};
+ {% elif data.statements is not defined and o_data.statements %}
+ {{ o_data.statements.rstrip(';') }};
+{% else %}
+ NOTHING;
+{% endif %}
+{% endif %}
+{% if data.comment != o_data.comment %}
+COMMENT ON RULE {{ conn|qtIdent(o_data.name) }} ON {{ conn|qtIdent(o_data.schema, o_data.view) }} IS {{ data.comment|qtLiteral }};{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/img/coll-table.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/img/coll-table.png
new file mode 100644
index 0000000000000000000000000000000000000000..680e86457e8c48cd01329f0303d663b0304ab44d
GIT binary patch
literal 555
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbMf-T<EvS0Jr8^?BF1e^bu?TzmD?
zsXMRkJ%0Y|`SWMbo*ljMtZe5CgE=oc&i)J9@M-haw|DQ}Z9e_4>e%o613!~@ehb_5
z*>m*=+of*}=e<^+`SRYqd(Ef+dI3e2yw#Zb^8Wq%j~+dG{P^+Hr%&4tJb&=u!SdzH
z*Q{Cd=FOXT@7}$9`SRhzhs%~NTd`ur+O=!fuV4S_)vF1oK0kl{{PpYCFJ8P@xpL+E
z_wPS`{P_0m+f}PpZP>73)22;No;>;R;X^gZ0Y5ZmzF7BH^&ij!j3q&S!3+-1ZlnP@
zoCO|{#X#BvjNMLV+W{F%JzX3_BreCEKQ7i}Akg}-$5cydO$Teo7MG=+N#Fm<pXSlA
zF*^OfrrVmO&iz!g+vcn9N;KboWe>g3_MdIbtj55z467wYr(8Tf`DM;NgJ}*2Ts9xt
za8DvyRL7)e=Crp3yMhn8I<I_gRVTnDsxoVD`Gh<6ZiW+79FuomTDG#o_)gn1l_K@t
zbJLlaXW8s861@8^@vEZ8?}M3QA`drnbuQa~?`a=@4deV=fh{M$if#frShd78q9i4;
zB-JXpC>2OC7#SEE=o%X78W@Hc8CaPZTbY<@8<<)d7|eU8w+cl=ZhlH;S|vn-fhAZ2
cP_>Dbff+=@sp+9>fEpM)UHx3vIVCg!0JlsKi2wiq
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/img/table-repl-sm.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/img/table-repl-sm.png
new file mode 100644
index 0000000000000000000000000000000000000000..967bd937fb89c483a95146c88fb4c066f6da5711
GIT binary patch
literal 675
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GXl47>xpaLR^7d#i`G`&i$Km{^#1O
zpHAI*b?@=>XV0HMd-m+;jb~*$Ul`1J(Q)=)^XY$8$A0G@_?f)(TiB-0o~u9DE`4h_
z@3s2Om(8dDdI7mh-fGNzx%ukbyLa#2yLa#Y{ris|J$n53@zbYI+YdaSw90wIwfqwo
z*FAoGfBDMAH*a5Fvu4ekH*em(d-w9?%PH&JH{32+bv<wDN}uk<R{dMu)~;Q<e*OAa
zuU>gH?KAH>pEkLwcYDb6lTnjSMzn9Td;a|S>({S~kKHdkaxd?|-Ppw^%jf&_9uI9h
z=v%+m{Qdj)A3uJ4`}S?Y;d{CJ?}SW0kv_e={eWNN9=GZpj#VoSH*DCjY15{heRqN;
z9}AyzIO^#`_{y}aw(m0jnr?*8!ML;jpIsdLYCufG;M>s0K@OF`34WUhOff8dA4
z%okCUjx~DRx&#b0#*!evU<QY0H`0I{&H|6fVj%4S#%?FG?SPCGo-U3d5|@(`6xa-u
z7&{n*jk&d@t=ZYt#m!wB+Nbx&I|O({xQMK3a_3P|&{I_66rC_>+O&yNCx<@>XyMS%
zT)kq|%G22yS((l(B_&_K@bZY7^z`^PFfCiQEbW=tw6JS3X1CO~DKJ`Bv%9OOi-$`m
zo0s!XXjnddd;j|R^$pt!6DEjxC|IbN=-4P3oeI0`tSzM>AS^9Df5MC@JZ0`&N5tB$
zgxJ`zGT48TG(363Ckg0g)e_f;l9a@fRIB8oR3OD*WMF8ZYiOivU>IU#U}a)#Wn!vr
zU}|MxFz=n-DijU5`6-!cl@JXEmS7D))h1R3W)KahriZQpYGCkm^>bP0l+XkKRw_GR
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/img/table-repl.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/img/table-repl.png
new file mode 100644
index 0000000000000000000000000000000000000000..2a082c67b8c307b673d32fc0df12fb5e857fc1f7
GIT binary patch
literal 839
zcmV-N1GxN&P)<h;3K|Lk000e1NJLTq000mG000mO0{{R3C@l|D0004@P)t-s0000X
zmFJDn|B}!9qSN)d*zM8Z>EGk$<>u$+=H}Yr<*d%<W3lKio9K(r|Aox{e#rcF!}@H#
z_+hyAR<iR;tnfgm?l++9Eu88mnCXSg{cFDXL8k6Dq3kZ4>a^DG-QC^Z-rnEe-{9cj
z;o;%p;^O1u<K^Y$hrj3O=;-O`>9WCpoxM|ysXD2tspRD3tgNi;?CkCB?eFjJ>gwu{
zsX?E@R<hi7k*`Civx26$XpXHyqqbtLt*x)Guj}jU(AB@Hy?3eBZjQ1*lCnpTy-be1
zOMaOmNP@miior~Y!B~lpo6TUB&0delR))7j@bK{R^78KP?sLZ9am3zk!QEuA)KrS3
zh_ynB#ZrX8OMkLFv9YnTva)c*-fqC%Xt>!@hogbGU53F;fxbw4x<YxcHu3TC^Yiod
z_4Q+})MvNZSDnjMiJhglYLB5*q_=92qE+_x_W1btVXV`1qR&u~#a^q^TBXo%l)1IF
zwYIjlxVX6Y_xF>v*<+N)Ih*5}x!G5j$xe;LSDnnO!`mg6=3t=LR+PtVpU^d$<Y+BQ
z_y7O^0d!JMQvg8b*k%9#010qNS#tmY07w7;07w8v$!k6U007-dL_t&-(_>&@U_=27
zKmjIZ7FITP4o*%kZXRBc03W}AppdYLsF=8fBv=KXlz^Z#P(Vgj4k940prELvtfH!>
z&Zz+wK=3uSzyjJjx|({b`UZwZ#wK6^Q!{f5ODk&|TRS^@uz-W3le3Gfo4b~Yp%Ej4
zho_g9m$#3vpTE0tKwuD1AUGs6EIcAID%w3JHZ}k#5Eq}2n3SB7n&zG!n2`w-h|kK-
z$<50z$Sce&DlP&Fl$4g0S5(FZq*qnf)YLLE)YUgMHZ`}jg47q))`G2VYwzgnf_WbR
zhg&kzn38;O0000bbVXQnWMOn=I%9HWVRU5xGB7bPEip1JFfmjzFgi3dIy5yaFf}?b
zFrMx%ssI20C3HntbYx+4WjbwdWNBu305UK!FfA}SEif@uGBY|fG&(RgD=;-WFfhuO
RRjdF2002ovPDHLkV1h^asowwq
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/img/table.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/img/table.png
new file mode 100644
index 0000000000000000000000000000000000000000..37b2227d8fe0cf76bde23855676403c57ccfb569
GIT binary patch
literal 593
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbMfjR2nzS0Jr8^?C1w|C7%DUUd2E
zt{ZPJ-hX-j@$;w8o<Do`?AF7lYtB7S-uOaq_KWWG|69-et2^<h^zg5oeLoU+d<)z3
z*?Y|ghvo0g7rfD(^Ga>T%hof0v-kaQT=vdv;Tyd<ueMx!clYkyd-v|$zkmP1g9i^E
zK791((c{ODpFVxszW@1)7cXAEeEIzO^OY-CK6&zF&6+iD-n@DH_U-%k?_a%owS4*V
z6)RS(UAuPu`t`40zy9#y!>3Q5-o1ObdiClJ8#Zj*xN*~_O&>pg{QUXz*RNl<Zr%Fr
z+qWM-er(&eZO4us-@kt^zO}6y=r_iaAirP+hi5m^fE>;OkH}&m?E%JaC$sH<j18VH
zjv*44L(g9qYBCULeVEWMTP@D5z~~&PxFE^#;otincP*^m-2M4%e`HI`6~&eoAG7KD
zBHj|ywXfb{f7QF}MppT*z5fz8Tx+k09M7NOlP>dY(w2yI*ETJB#CxDVyM_6hsq?*{
z%bR2yj^^b{bb0Zwd(Bu=J=bCx>jtGG3nR*NpRqG!KlWJZ=J(tnh2i}T<FyU3e_r!C
zY?tI_F5B<g&(Uyv`fuYt;f8p5Rhdig_cLtbm*#(5whH7s)e_f;l9a@fRIB8oR3OD*
zWMF8ZYiOivU>IU#U}a)#Wn!vrU}|MxFz=n-DijU5`6-!cl@JXEmS7D))h1R3W)Kah
TriZQpYGCkm^>bP0l+XkKw!A9P
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/__init__.py
new file mode 100644
index 0000000..ed675ce
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/__init__.py
@@ -0,0 +1,894 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+""" Implements Trigger Node """
+
+from flask import render_template, make_response, request, jsonify
+from flask.ext.babel import gettext
+from pgadmin.utils.ajax import make_json_response, \
+ make_response as ajax_response, internal_server_error
+from pgadmin.browser.utils import PGChildNodeView
+from pgadmin.browser.collection import CollectionNodeModule
+import pgadmin.browser.server_groups.servers.databases as database
+from pgadmin.browser.server_groups.servers.databases.schemas.utils import \
+ trigger_definition as _trigger_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
+import json
+
+
+class TriggerModule(CollectionNodeModule):
+ """
+ class TriggerModule(CollectionNodeModule)
+
+ A module class for Trigger node derived from CollectionNodeModule.
+
+ Methods:
+ -------
+ * __init__(*args, **kwargs)
+ - Method is used to initialize the Trigger and it's base module.
+
+ * get_nodes(gid, sid, did, scid, tid)
+ - 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 trigger, when any of the server node is
+ initialized.
+ """
+
+ NODE_TYPE = 'trigger'
+ COLLECTION_LABEL = gettext("Triggers")
+
+ def __init__(self, *args, **kwargs):
+ """
+ Method is used to initialize the TriggerModule and it's base module.
+
+ Args:
+ *args:
+ **kwargs:
+ """
+ self.min_ver = None
+ self.max_ver = None
+ super(TriggerModule, self).__init__(*args, **kwargs)
+
+ def BackendSupported(self, manager, **kwargs):
+ """
+ Load this module if vid is view, we will not load it under
+ material view
+ """
+ if super(TriggerModule, self).BackendSupported(manager, **kwargs):
+ conn = manager.connection(did=kwargs['did'])
+ # If DB is not connected then return error to browser
+ if not conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+
+ if 'vid' not in kwargs:
+ return True
+
+ template_path = 'trigger/sql/9.1_plus'
+ SQL = render_template("/".join(
+ [template_path, 'backend_support.sql']), vid=kwargs['vid'])
+ status, res = conn.execute_scalar(SQL)
+ # check if any errors
+ if not status:
+ return internal_server_error(errormsg=res)
+ # Check vid is view not material view
+ # then true, othewise false
+ return res
+
+ def get_nodes(self, gid, sid, did, scid, **kwargs):
+ """
+ Generate the collection node
+ # TODO::
+ We can have following arguments for different type of parents.
+ i.e.
+ tid - for tables
+ vid - for materialized views
+ """
+ assert('tid' in kwargs or 'vid' in kwargs)
+ yield self.generate_browser_collection_node(
+ kwargs['tid'] if 'tid' in kwargs else kwargs['vid']
+ )
+
+ @property
+ def script_load(self):
+ """
+ Load the module script for server, when any of the server-group node is
+ initialized.
+ """
+ return database.DatabaseModule.NODE_TYPE
+
+ @property
+ def node_inode(self):
+ """
+ Load the module node as a leaf node
+ """
+ return True
+
+ @property
+ def csssnippets(self):
+ """
+ Returns a snippet of css to include in the page
+ """
+ snippets = [
+ render_template(
+ "trigger/css/trigger.css",
+ node_type=self.node_type
+ )
+ ]
+
+ for submodule in self.submodules:
+ snippets.extend(submodule.csssnippets)
+
+ return snippets
+
+blueprint = TriggerModule(__name__)
+
+
+class TriggerView(PGChildNodeView):
+ """
+ This class is responsible for generating routes for Trigger node
+
+ Methods:
+ -------
+ * __init__(**kwargs)
+ - Method is used to initialize the TriggerView and it's base view.
+
+ * module_js()
+ - This property defines (if javascript) exists for this node.
+ Override this property for your own logic
+
+ * check_precondition()
+ - This function will behave as a decorator which will checks
+ database connection before running view, it will also attaches
+ manager,conn & template_path properties to self
+
+ * list()
+ - This function is used to list all the Trigger nodes within that
+ collection.
+
+ * nodes()
+ - This function will used to create all the child node within that
+ collection, Here it will create all the Trigger node.
+
+ * properties(gid, sid, did, scid, tid, trid)
+ - This function will show the properties of the selected Trigger node
+
+ * create(gid, sid, did, scid, tid)
+ - This function will create the new Trigger object
+
+ * update(gid, sid, did, scid, tid, trid)
+ - This function will update the data for the selected Trigger node
+
+ * delete(self, gid, sid, scid, tid, trid):
+ - This function will drop the Trigger object
+
+ * enable(self, gid, sid, scid, tid, trid):
+ - This function will enable/disable Trigger object
+
+ * msql(gid, sid, did, scid, tid, trid)
+ - This function is used to return modified SQL for the selected
+ Trigger node
+
+ * get_sql(data, scid, tid, trid)
+ - This function will generate sql from model data
+
+ * sql(gid, sid, did, scid, tid, trid):
+ - This function will generate sql to show it in sql pane for the
+ selected Trigger node.
+
+ * dependency(gid, sid, did, scid, tid, trid):
+ - This function will generate dependency list show it in dependency
+ pane for the selected Trigger node.
+
+ * dependent(gid, sid, did, scid, tid, trid):
+ - This function will generate dependent list to show it in dependent
+ pane for the selected Trigger node.
+
+ * get_trigger_functions(gid, sid, did, scid, tid, trid):
+ - This function will return list of trigger functions available
+ via AJAX response
+
+ * _column_details(tid, clist)::
+ - This function will fetch the columns for trigger
+ """
+
+ node_type = blueprint.node_type
+
+ parent_ids = [
+ {'type': 'int', 'id': 'gid'},
+ {'type': 'int', 'id': 'sid'},
+ {'type': 'int', 'id': 'did'},
+ {'type': 'int', 'id': 'scid'},
+ {'type': 'int', 'id': 'tid'}
+ ]
+ ids = [
+ {'type': 'int', 'id': 'trid'}
+ ]
+
+ operations = dict({
+ 'obj': [
+ {'get': 'properties', 'delete': 'delete', 'put': 'update'},
+ {'get': 'list', 'post': 'create'}
+ ],
+ 'delete': [{'delete': 'delete'}],
+ 'children': [{'get': 'children'}],
+ 'nodes': [{'get': 'node'}, {'get': 'nodes'}],
+ 'sql': [{'get': 'sql'}],
+ 'msql': [{'get': 'msql'}, {'get': 'msql'}],
+ 'stats': [{'get': 'statistics'}],
+ 'dependency': [{'get': 'dependencies'}],
+ 'dependent': [{'get': 'dependents'}],
+ 'module.js': [{}, {}, {'get': 'module_js'}],
+ 'get_triggerfunctions': [{'get': 'get_trigger_functions'},
+ {'get': 'get_trigger_functions'}],
+ 'enable': [{'put': 'enable_disable_trigger'}]
+ })
+
+ def check_precondition(f):
+ """
+ This function will behave as a decorator which will checks
+ database connection before running view, it will also attaches
+ manager,conn & template_path properties to self
+ """
+ @wraps(f)
+ def wrap(*args, **kwargs):
+ # Here args[0] will hold self & kwargs will hold gid,sid,did
+ self = args[0]
+ self.manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(
+ kwargs['sid']
+ )
+ self.conn = self.manager.connection(did=kwargs['did'])
+ # If DB not connected then return error to browser
+ if not self.conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+
+ # We need datlastsysoid to check if current trigger is system trigger
+ self.datlastsysoid = self.manager.db_info[kwargs['did']]['datlastsysoid']
+
+ # we will set template path for sql scripts
+ self.template_path = 'trigger/sql/9.1_plus'
+ # Store server type
+ self.server_type = self.manager.server_type
+ # We need parent's name eg table name and schema name
+ # when we create new trigger in update we can fetch it using
+ # property sql
+ SQL = render_template("/".join([self.template_path,
+ 'get_parent.sql']),
+ tid=kwargs['tid'])
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=rset)
+
+ for row in rset['rows']:
+ self.schema = row['schema']
+ self.table = row['table']
+
+ # Here we are storing trigger definition
+ # We will use it to check trigger type definition
+ self.trigger_definition = {
+ 'TRIGGER_TYPE_ROW': (1 << 0),
+ 'TRIGGER_TYPE_BEFORE': (1 << 1),
+ 'TRIGGER_TYPE_INSERT': (1 << 2),
+ 'TRIGGER_TYPE_DELETE': (1 << 3),
+ 'TRIGGER_TYPE_UPDATE': (1 << 4),
+ 'TRIGGER_TYPE_TRUNCATE': (1 << 5),
+ 'TRIGGER_TYPE_INSTEAD': (1 << 6)
+ }
+
+ return f(*args, **kwargs)
+
+ return wrap
+
+ @check_precondition
+ def get_trigger_functions(self, gid, sid, did, scid, tid, trid=None):
+ """
+ This function will return list of trigger functions available
+ via AJAX response
+ """
+ res = [{'label': '', 'value': ''}]
+
+ # TODO: REMOVE True Condition , it's just for testing
+ # If server type is EDB-PPAS then we also need to add
+ # inline edb-spl along with options fetched by below sql
+
+ if self.server_type == 'ppas':
+ res.append({
+ 'label': 'Inline EDB-SPL',
+ 'value': 'Inline EDB-SPL'
+ })
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_triggerfunctions.sql']),
+ show_system_objects=self.blueprint.show_system_objects
+ )
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in rset['rows']:
+ res.append(
+ {'label': row['tfunctions'],
+ 'value': row['tfunctions']}
+ )
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def list(self, gid, sid, did, scid, tid):
+ """
+ This function is used to list all the trigger nodes within that collection.
+
+ Args:
+ gid: Server group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+
+ Returns:
+ JSON of available trigger nodes
+ """
+
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']), tid=tid)
+ 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, tid):
+ """
+ This function will used to create all the child node within that collection.
+ Here it will create all the trigger node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+
+ Returns:
+ JSON of available trigger child nodes
+ """
+ res = []
+ SQL = render_template("/".join([self.template_path,
+ 'nodes.sql']), tid=tid)
+ 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'],
+ tid,
+ row['name'],
+ icon="icon-trigger" if row['is_enable_trigger']
+ else "icon-trigger-bad"
+ ))
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ def _column_details(self, tid, clist):
+ """
+ This functional will fetch list of column for trigger
+
+ Args:
+ tid: Table OID
+ clist: List of columns
+
+ Returns:
+ Updated properties data with column
+ """
+
+ SQL = render_template("/".join([self.template_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
+
+ @check_precondition
+ def properties(self, gid, sid, did, scid, tid, trid):
+ """
+ This function will show the properties of the selected trigger node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ scid: Schema ID
+ tid: Table ID
+ trid: Trigger ID
+
+ Returns:
+ JSON of selected trigger node
+ """
+
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']),
+ tid=tid, trid=trid,
+ datlastsysoid=self.datlastsysoid)
+
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ # Making copy of output for future use
+ data = dict(res['rows'][0])
+ if data['tgnargs'] > 1:
+ # We know that trigger has more than 1 arguments, let's join them
+ # and convert it as string
+ data['tgargs'] = ', '.join(data['tgargs'])
+
+ if len(data['tgattr']) > 1:
+ columns = ', '.join(data['tgattr'].split(' '))
+ data['columns'] = self._column_details(tid, columns)
+
+ data = _trigger_definition(data)
+
+ return ajax_response(
+ response=data,
+ status=200
+ )
+
+ @check_precondition
+ def create(self, gid, sid, did, scid, tid):
+ """
+ This function will creates new the trigger object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ """
+ data = request.form if request.form else json.loads(
+ request.data.decode()
+ )
+ required_args = {
+ 'name': 'Name',
+ 'tfunction': 'Trigger function'
+ }
+
+ 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)." % \
+ required_args[arg])
+ )
+
+ # Adding parent into data dict, will be using it while creating sql
+ data['schema'] = self.schema
+ data['table'] = self.table
+
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'create.sql']),
+ data=data, conn=self.conn)
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ # we need oid to to add object in tree at browser
+ SQL = render_template("/".join([self.template_path,
+ 'get_oid.sql']),
+ tid=tid, data=data)
+ status, trid = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=tid)
+
+ return jsonify(
+ node=self.blueprint.generate_browser_node(
+ trid,
+ scid,
+ data['name'],
+ icon="icon-trigger"
+ )
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def delete(self, gid, sid, did, scid, tid, trid):
+ """
+ This function will updates existing the trigger object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ trid: Trigger ID
+ """
+ # Below will decide if it's simple drop or drop with cascade call
+ if self.cmd == 'delete':
+ # This is a cascade operation
+ cascade = True
+ else:
+ cascade = False
+
+ try:
+ # We will first fetch the trigger name for current request
+ # so that we create template for dropping trigger
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']),
+ tid=tid, trid=trid,
+ datlastsysoid=self.datlastsysoid)
+
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ data = dict(res['rows'][0])
+
+ SQL = render_template("/".join([self.template_path,
+ 'delete.sql']),
+ data=data, conn=self.conn, 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("Trigger is dropped"),
+ data={
+ 'id': trid,
+ 'tid': tid
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def update(self, gid, sid, did, scid, tid, trid):
+ """
+ This function will updates existing the trigger object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ trid: Trigger ID
+ """
+ data = request.form if request.form else json.loads(request.data.decode())
+
+ try:
+ SQL = self.get_sql(scid, tid, trid, data)
+ if SQL and SQL.strip('\n') and SQL.strip(' '):
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info="Trigger updated",
+ data={
+ 'id': trid,
+ 'tid': tid,
+ 'scid': scid
+ }
+ )
+ else:
+ return make_json_response(
+ success=1,
+ info="Nothing to update",
+ data={
+ 'id': trid,
+ 'tid': tid,
+ 'scid': scid
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def msql(self, gid, sid, did, scid, tid, trid=None):
+ """
+ This function will generates modified sql for trigger object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ trid: Trigger ID (When working with existing trigger)
+ """
+ data = dict()
+ for k, v in request.args.items():
+ try:
+ data[k] = json.loads(v)
+ except ValueError:
+ data[k] = v
+
+ # Adding parent into data dict, will be using it while creating sql
+ data['schema'] = self.schema
+ data['table'] = self.table
+
+ try:
+ SQL = self.get_sql(scid, tid, trid, data)
+
+ if SQL and SQL.strip('\n') and SQL.strip(' '):
+ return make_json_response(
+ data=SQL,
+ status=200
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ def get_sql(self, scid, tid, trid, data):
+ """
+ This function will genrate sql from model data
+ """
+ if trid is not None:
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']),
+ tid=tid, trid=trid,
+ datlastsysoid=self.datlastsysoid)
+
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ old_data = dict(res['rows'][0])
+
+ # If name is not present in data then
+ # we will fetch it from old data, we also need schema & table name
+ if 'name' not in data:
+ data['name'] = old_data['name']
+
+ if old_data['tgnargs'] > 1:
+ # We know that trigger has more than 1 arguments, let's join them
+ old_data['tgargs'] = ', '.join(old_data['tgargs'])
+
+ if len(old_data['tgattr']) > 1:
+ columns = ', '.join(old_data['tgattr'].split(' '))
+ old_data['columns'] = self._column_details(tid, columns)
+
+ old_data = _trigger_definition(old_data)
+
+ SQL = render_template(
+ "/".join([self.template_path, 'update.sql']),
+ data=data, o_data=old_data, conn=self.conn
+ )
+ else:
+ required_args = {
+ 'name': 'Name',
+ 'tfunction': 'Trigger function'
+ }
+
+ for arg in required_args:
+ if arg not in data:
+ return gettext('-- incomplete definition')
+
+ # If the request for new object which do not have did
+ SQL = render_template("/".join([self.template_path, 'create.sql']),
+ data=data, conn=self.conn)
+ return SQL
+
+ @check_precondition
+ def sql(self, gid, sid, did, scid, tid, trid):
+ """
+ This function will generates reverse engineered sql for trigger object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ trid: Trigger ID
+ """
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']),
+ tid=tid, trid=trid,
+ datlastsysoid=self.datlastsysoid)
+
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ data = dict(res['rows'][0])
+ # Adding parent into data dict, will be using it while creating sql
+ data['schema'] = self.schema
+ data['table'] = self.table
+
+ if data['tgnargs'] > 1:
+ # We know that trigger has more than 1 arguments, let's join them
+ data['tgargs'] = ', '.join(data['tgargs'])
+
+ if len(data['tgattr']) > 1:
+ columns = ', '.join(data['tgattr'].split(' '))
+ data['columns'] = self._column_details(tid, columns)
+
+ data = _trigger_definition(data)
+
+ SQL = self.get_sql(scid, tid, None, data)
+
+ sql_header = "-- Trigger: {0}\n\n-- ".format(data['name'])
+ sql_header += render_template("/".join([self.template_path,
+ 'delete.sql']),
+ data=data, conn=self.conn)
+
+ SQL = sql_header + '\n\n' + SQL.strip('\n')
+
+ # If trigger is disbaled then add sql code for the same
+ if not data['is_enable_trigger']:
+ SQL += '\n\n'
+ SQL += render_template("/".join([self.template_path,
+ 'enable_disable_trigger.sql']),
+ data=data, conn=self.conn)
+
+ return ajax_response(response=SQL)
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def enable_disable_trigger(self, gid, sid, did, scid, tid, trid):
+ """
+ This function will enable OR disable the current trigger object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ trid: Trigger ID
+ """
+
+ data = request.form if request.form else json.loads(request.data.decode())
+
+ # Convert str 'true' to boolean type
+ is_enable_flag = json.loads(data['enable'])
+
+ try:
+
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']),
+ tid=tid, trid=trid,
+ datlastsysoid=self.datlastsysoid)
+
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ o_data = dict(res['rows'][0])
+
+ # If enable is set to true means we need SQL to enable
+ # current trigger which is disabled already so we need to
+ # alter the 'is_enable_trigger' flag so that we can render
+ # correct SQL for operation
+ o_data['is_enable_trigger'] = is_enable_flag
+
+ # Adding parent into data dict, will be using it while creating sql
+ o_data['schema'] = self.schema
+ o_data['table'] = self.table
+
+ SQL = render_template("/".join([self.template_path,
+ 'enable_disable_trigger.sql']),
+ data=o_data, conn=self.conn)
+ status, res = self.conn.execute_scalar(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info="Trigger updated",
+ data={
+ 'id': trid,
+ 'tid': tid,
+ 'scid': scid
+ }
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+
+ @check_precondition
+ def dependents(self, gid, sid, did, scid, tid, trid):
+ """
+ This function get the dependents and return ajax response
+ for the trigger node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ trid: Trigger ID
+ """
+ dependents_result = self.get_dependents(
+ self.conn, trid
+ )
+
+ return ajax_response(
+ response=dependents_result,
+ status=200
+ )
+
+ @check_precondition
+ def dependencies(self, gid, sid, did, scid, tid, trid):
+ """
+ This function get the dependencies and return ajax response
+ for the trigger node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Table ID
+ trid: Trigger ID
+
+ """
+ dependencies_result = self.get_dependencies(
+ self.conn, trid
+ )
+
+ return ajax_response(
+ response=dependencies_result,
+ status=200
+ )
+
+TriggerView.register_node_view(blueprint)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/static/img/coll-trigger.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/static/img/coll-trigger.png
new file mode 100644
index 0000000000000000000000000000000000000000..3c339406fa325dad67f59141a6a08a5334debcda
GIT binary patch
literal 350
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!73?$#)eFPFv5AX?b1=6kiPU=rwT=MDBga5xP
z_8ouw|L>&#A2Xj{cR#w-VBI{+?aRZi97(#*xCE$_u_VYZn8D%MjWi&Kv%n*=7)X17
zvD?XPJ0K&^)5S4_<9f0{kWc~xb80dxhx5@;Pr)+*-bQ9j-mAoSvNTT3G+F96CDTtU
zys(goD^vGXfVVgEs_XYmg7@y()6wYo{DM|*UmqVUo8r>Dvy9XIn6#3t7H`cGJ>lb~
z`u6T@g>5h9G)5dcu;fSs3qw<vM2e~mdkWBI)e_f;l9a@fRIB8oR3OD*WMF8ZYiOiv
zU>IU#U}a)#Wn!vrU}|MxFz=n-DijU5`6-!cl@JXEmS7D))h1R3W)KahriZQpYGCkm
L^>bP0l+XkKAR%?3
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/static/img/trigger-bad.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/static/img/trigger-bad.png
new file mode 100644
index 0000000000000000000000000000000000000000..6cc2fe96c8f234a73672cc73fd272a15fc7de4f9
GIT binary patch
literal 610
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbMf&j6ngS0LTG@1*|3#U-B}o%;Xd
z@BjbbfBg9M>-UfEKmPptck0#i>q$vR85k~vM;<wH;@IDBlcy{^>h66vGt;?!=F#Zb
zZ{_7j+`X5s+_2Bq>0@|!L|y;4va)YQMPKvs_E<Z73=4bj@BhxjGp@SlRA9*0+}tl2
z8K0AqK7@w8_XCQ1SoF-Bv2V}k#Ke!$(H}xW-uwE!b9XmezhLo!y|+?RKLi6seBZgb
zt<o{>KeX=}P-AfLdmo>7uC8zF>|a}0_%=`3<>K}sDCoVn_gfd&*XHK03=LmuYd7cA
zd<YDD=jHX**=44)*Gnz!7pkhy6%^h&IUjNJn(5;GTwY=QPpA1n&oh<;`2{mLJiCzw
z<Zu>vL>2>S4={E+nQaGTEbw%343W5;d-f{dp#&b*2lcZ1ns=Wr`2LXZ@Y}!gZH&=I
zLRA-+#NU!T_D{s!WsY3O^U7b0%|TU)i%;fkeASY}^oDQwY2NeVd+dG)a<mDAsWxTk
z^s!7@X<|1)aN^mgfg01U^cJ}~Yz@md*(!e}tNg;-uZo$P&5CZ1PnImpJtH2m?%Iu<
z@>M5p$oE-1tl3!?oFZs`=O(lN+cwV$Ps+oeiATx_obU^~(ExO-YKdz^NlIc#s#S7P
zDv)9@GB7mMH89pSum~|UvNANcGO*M(Ftai+xcqEI5{ic0{FKbJO57S2?H0HP)WG2B
L>gTe~DWM4fI7SDA
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/static/img/trigger.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/static/img/trigger.png
new file mode 100644
index 0000000000000000000000000000000000000000..3b413e4a648c999c0dcc005aaaf02a710be4414b
GIT binary patch
literal 324
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!73?$#)eFPHF3h)VW1=6kiPU=rwT=MDBssBIz
z{{Mgc|Bu?=uTvji_Bye{WYZ$U_4Az$Y>2*ja-Gx8WkA)8B|(0{3=Yq3qyagc1s;*b
zK-vS0-A-oP0U3dwE{-7_*OL<(nB7!eIhs%2;5b!K7}#Lo<lL;#vt^6P2CZeEK7CSR
z6j;p6;jn5(+POKFoI0Dbu4-*CTXuK1IcI}p`Z<TQJF7XfW=>*a4r5?=RWIaqXXEsv
zK*Lo_Tq8<S5=&C8l8aJ-6oZk0p`osUp{|i}h=H*c5E&Y48<<%c7@VBUTY#b=H$Npa
rtrDccK-a($s3*k8*viDj%D_z9z!a$A)b!9bKn)C@u6{1-oD!M<5hib6
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/css/trigger.css b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/css/trigger.css
new file mode 100644
index 0000000..811c838
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/css/trigger.css
@@ -0,0 +1,20 @@
+.icon-coll-trigger {
+ background-image: url('{{ url_for('NODE-trigger.static', filename='img/coll-trigger.png' )}}') !important;
+ background-repeat: no-repeat;
+ align-content: center;
+ vertical-align: middle;
+ height: 1.3em;
+}
+
+.icon-trigger {
+ background-image: url('{{ url_for('NODE-trigger.static', filename='img/trigger.png') }}') !important;
+ background-repeat: no-repeat;
+ align-content: center;
+ vertical-align: middle;
+ height: 1.3em;
+}
+
+.icon-trigger-bad {
+ background-image: url('{{ url_for('NODE-trigger.static', filename='img/trigger-bad.png') }}') !important;
+ border-radius: 10px
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/js/trigger.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/js/trigger.js
new file mode 100644
index 0000000..7a85a8c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/js/trigger.js
@@ -0,0 +1,539 @@
+define(
+ ['jquery', 'underscore', 'underscore.string', 'pgadmin', 'pgadmin.browser',
+ 'backform', 'alertify', 'pgadmin.browser.collection'],
+function($, _, S, pgAdmin, pgBrowser, Backform, alertify) {
+
+ if (!pgBrowser.Nodes['coll-trigger']) {
+ var triggers = pgAdmin.Browser.Nodes['coll-trigger'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'trigger',
+ label: '{{ _('Triggers') }}',
+ type: 'coll-trigger',
+ columns: ['name', 'oid', 'description']
+ });
+ };
+
+ if (!pgBrowser.Nodes['trigger']) {
+ pgAdmin.Browser.Nodes['trigger'] = pgAdmin.Browser.Node.extend({
+ parent_type: ['table', 'view', 'materialized_view'],
+ collection_type: ['coll-table'],
+ type: 'trigger',
+ label: '{{ _('Trigger') }}',
+ hasSQL: true,
+ hasDepends: true,
+ Init: function() {
+ /* Avoid mulitple registration of menus */
+ if (this.initialized)
+ return;
+
+ this.initialized = true;
+
+ pgBrowser.add_menus([{
+ name: 'create_trigger_on_coll', node: 'coll-trigger', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Trigger...') }}',
+ icon: 'wcTabIcon icon-trigger', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_trigger', node: 'trigger', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Trigger...') }}',
+ icon: 'wcTabIcon icon-trigger', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_trigger_onTable', node: 'table', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Trigger...') }}',
+ icon: 'wcTabIcon icon-trigger', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'enable_trigger', node: 'trigger', module: this,
+ applies: ['object', 'context'], callback: 'enable_trigger',
+ category: 'connect', priority: 3, label: '{{ _('Enable trigger') }}',
+ icon: 'fa fa-check', enable : 'canCreate_with_trigger_enable'
+ },{
+ name: 'disable_trigger', node: 'trigger', module: this,
+ applies: ['object', 'context'], callback: 'disable_trigger',
+ category: 'drop', priority: 3, label: '{{ _('Disable trigger') }}',
+ icon: 'fa fa-times', enable : 'canCreate_with_trigger_disable'
+ },{
+ name: 'create_trigger', node: 'view', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Trigger...') }}',
+ icon: 'wcTabIcon icon-trigger', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ }
+ ]);
+ },
+ callbacks: {
+ /* Enable trigger */
+ enable_trigger: function(args) {
+ var input = args || {};
+ obj = this,
+ t = pgBrowser.tree,
+ i = input.item || t.selected(),
+ d = i && i.length == 1 ? t.itemData(i) : undefined;
+
+ if (!d)
+ return false;
+
+ var data = d;
+ $.ajax({
+ url: obj.generate_url(i, 'enable' , d, true),
+ type:'PUT',
+ data: {'enable' : true},
+ dataType: "json",
+ success: function(res) {
+ if (res.success == 1) {
+ alertify.success("{{ _('" + res.info + "') }}");
+ t.removeIcon(i);
+ data.icon = 'icon-trigger';
+ t.addIcon(i, {icon: data.icon});
+ t.unload(i);
+ t.setInode(i);
+ t.deselect(i);
+ // Fetch updated data from server
+ setTimeout(function() {
+ t.select(i);
+ }, 10);
+ }
+ },
+ error: function(xhr, status, error) {
+ try {
+ var err = $.parseJSON(xhr.responseText);
+ if (err.success == 0) {
+ msg = S('{{ _(' + err.errormsg + ')}}').value();
+ alertify.error("{{ _('" + err.errormsg + "') }}");
+ }
+ } catch (e) {}
+ t.unload(i);
+ }
+ })
+ },
+ /* Disable trigger */
+ disable_trigger: function(args) {
+ var input = args || {};
+ obj = this,
+ t = pgBrowser.tree,
+ i = input.item || t.selected(),
+ d = i && i.length == 1 ? t.itemData(i) : undefined;
+
+ if (!d)
+ return false;
+
+ var data = d;
+ $.ajax({
+ url: obj.generate_url(i, 'enable' , d, true),
+ type:'PUT',
+ data: {'enable' : false},
+ dataType: "json",
+ success: function(res) {
+ if (res.success == 1) {
+ alertify.success("{{ _('" + res.info + "') }}");
+ t.removeIcon(i);
+ data.icon = 'icon-trigger-bad';
+ t.addIcon(i, {icon: data.icon});
+ t.unload(i);
+ t.setInode(i);
+ t.deselect(i);
+ // Fetch updated data from server
+ setTimeout(function() {
+ t.select(i);
+ }, 10);
+ }
+ },
+ error: function(xhr, status, error) {
+ try {
+ var err = $.parseJSON(xhr.responseText);
+ if (err.success == 0) {
+ msg = S('{{ _(' + err.errormsg + ')}}').value();
+ alertify.error("{{ _('" + err.errormsg + "') }}");
+ }
+ } catch (e) {}
+ t.unload(i);
+ }
+ })
+ }
+ },
+ canDrop: pgBrowser.Nodes['schema'].canChildDrop,
+ canDropCascade: pgBrowser.Nodes['schema'].canChildDrop,
+ model: pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ name: undefined,
+ is_row_trigger: true
+ },
+ schema: [{
+ id: 'name', label: '{{ _('Name') }}', cell: 'string',
+ type: 'text', disabled: 'inSchema'
+ },{
+ id: 'oid', label:'{{ _('OID') }}', cell: 'string',
+ type: 'int', disabled: true, mode: ['properties']
+ },{
+ id: 'is_enable_trigger', label:'{{ _('Enable trigger?') }}',
+ type: 'switch', disabled: 'inSchema', mode: ['properties']
+ },{
+ id: 'is_row_trigger', label:'{{ _('Row trigger') }}',
+ type: 'switch', group: '{{ _('Definition') }}',
+ mode: ['create','edit', 'properties'],
+ deps: ['is_constraint_trigger'],
+ disabled: function(m) {
+ // If contraint trigger is set to True then row trigger will
+ // automatically set to True and becomes disable
+ var is_constraint_trigger = m.get('is_constraint_trigger');
+ if(!m.inSchemaWithModelCheck.apply(this, [m])) {
+ if(!_.isUndefined(is_constraint_trigger) &&
+ is_constraint_trigger === true) {
+ // change it's model value
+ setTimeout(function() { m.set('is_row_trigger', true) }, 10);
+ return true;
+ } else {
+ return false;
+ }
+ } else {
+ // Disbale it
+ return true;
+ }
+ }
+ },{
+ id: 'is_constraint_trigger', label:'{{ _('Constraint trigger') }}',
+ type: 'switch', disabled: 'inSchemaWithModelCheck',
+ mode: ['create','edit', 'properties'],
+ group: '{{ _('Definition') }}'
+ },{
+ id: 'tgdeferrable', label:'{{ _('Deferrable') }}',
+ type: 'switch', group: '{{ _('Definition') }}',
+ mode: ['create','edit', 'properties'],
+ deps: ['is_constraint_trigger'],
+ disabled: function(m) {
+ // If contraint trigger is set to True then only enable it
+ var is_constraint_trigger = m.get('is_constraint_trigger');
+ if(!m.inSchemaWithModelCheck.apply(this, [m])) {
+ if(!_.isUndefined(is_constraint_trigger) &&
+ is_constraint_trigger === true) {
+ return false;
+ } else {
+ setTimeout(function() { m.set('tgdeferrable', false) }, 10);
+ return true;
+ }
+ } else {
+ // Disbale it
+ return true;
+ }
+ }
+ },{
+ id: 'tginitdeferred', label:'{{ _('Deferred') }}',
+ type: 'switch', group: '{{ _('Definition') }}',
+ mode: ['create','edit', 'properties'],
+ deps: ['tgdeferrable'],
+ disabled: function(m) {
+ // If contraint trigger is set to True then only enable it
+ var is_constraint_trigger = m.get('tgdeferrable');
+ if(!m.inSchemaWithModelCheck.apply(this, [m])) {
+ if(!_.isUndefined(is_constraint_trigger) &&
+ is_constraint_trigger === true) {
+ return false;
+ } else {
+ setTimeout(function() { m.set('tginitdeferred', false) }, 10);
+ return true;
+ }
+ } else {
+ // Disbale it
+ return true;
+ }
+ }
+ },{
+ id: 'tfunction', label:'{{ _('Trigger Function') }}',
+ type: 'text', disabled: 'inSchemaWithModelCheck',
+ mode: ['create','edit', 'properties'], group: '{{ _('Definition') }}',
+ control: 'node-ajax-options', url: 'get_triggerfunctions'
+ },{
+ id: 'tgargs', label:'{{ _('Arguments') }}', cell: 'string',
+ group: '{{ _('Definition') }}',
+ type: 'text',mode: ['create','edit', 'properties'], deps: ['tfunction'],
+ disabled: function(m) {
+ // We will disable it when EDB PPAS and trigger function is
+ // set to Inline EDB-SPL
+ var tfunction = m.get('tfunction'),
+ server_type = m.node_info['server']['server_type'];
+ if(!m.inSchemaWithModelCheck.apply(this, [m])) {
+ if(server_type === 'ppas' &&
+ !_.isUndefined(tfunction) &&
+ tfunction === 'Inline EDB-SPL') {
+ // Disbale and clear its value
+ m.set('tgargs', undefined)
+ return true;
+ } else {
+ return false;
+ }
+ } else {
+ // Disbale it
+ return true;
+ }
+ }
+ },{
+ id: 'fires', label:'{{ _('Fires') }}', deps: ['is_constraint_trigger'],
+ mode: ['create','edit', 'properties'], group: '{{ _('Definition') }}',
+ options: function(m) {
+ var table_options = [
+ {label: "BEFORE", value: "BEFORE"},
+ {label: "AFTER", value: "AFTER"}],
+ view_options = [
+ {label: "BEFORE", value: "BEFORE"},
+ {label: "AFTER", value: "AFTER"},
+ {label: "INSTEAD OF", value: "INSTEAD OF"}];
+ // If we are under table then show table specific options
+ if(_.indexOf(Object.keys(this.model.node_info), 'table') != -1) {
+ return table_options;
+ } else {
+ return view_options;
+ }
+ },
+ // If create mode then by default open composite type
+ control: Backform.Select2Control.extend({
+ render: function(){
+ // Initialize parent's render method
+ Backform.Select2Control.prototype.render.apply(this, arguments);
+ if(this.model.isNew() &&
+ this.model.get('is_constraint_trigger') !== true ) {
+ this.model.set({'fires': 'BEFORE'}, {silent: true});
+ }
+ return this;
+ }
+ }),
+ select2: { allowClear: false, width: "100%" },
+ disabled: function(m) {
+ // If contraint trigger is set to True then only enable it
+ var is_constraint_trigger = m.get('is_constraint_trigger');
+ if(!m.inSchemaWithModelCheck.apply(this, [m])) {
+ if(!_.isUndefined(is_constraint_trigger) &&
+ is_constraint_trigger === true) {
+ setTimeout(function() { m.set('fires', 'AFTER', {silent: true}) }, 10);
+ return true;
+ } else {
+ return false;
+ }
+ } else {
+ // Disbale it
+ return true;
+ }
+ }
+ },{
+ type: 'nested', control: 'fieldset', mode: ['create','edit', 'properties'],
+ label: '{{ _('Events') }}', group: '{{ _('Definition') }}',
+ schema:[{
+ id: 'evnt_insert', label:'{{ _('INSERT') }}',
+ type: 'switch', mode: ['create','edit', 'properties'],
+ group: '{{ _('Events') }}',
+ disabled: function(m) {
+ return m.inSchemaWithModelCheck.apply(this, [m]);
+ }
+ },{
+ id: 'evnt_update', label:'{{ _('UPDATE') }}',
+ type: 'switch', mode: ['create','edit', 'properties'],
+ group: '{{ _('Events') }}',
+ disabled: function(m) {
+ return m.inSchemaWithModelCheck.apply(this, [m]);
+ }
+ },{
+ id: 'evnt_delete', label:'{{ _('DELETE') }}',
+ type: 'switch', mode: ['create','edit', 'properties'],
+ group: '{{ _('Events') }}',
+ disabled: function(m) {
+ return m.inSchemaWithModelCheck.apply(this, [m]);
+ }
+ },{
+ id: 'evnt_turncate', label:'{{ _('TURNCATE') }}',
+ type: 'switch', group: '{{ _('Events') }}',
+ disabled: function(m) {
+ var is_constraint_trigger = m.get('is_constraint_trigger'),
+ is_row_trigger = m.get('is_row_trigger'),
+ server_type = m.node_info['server']['server_type'];
+ if(!m.inSchemaWithModelCheck.apply(this, [m])) {
+ // We will enabale truncate only for EDB PPAS
+ // and both triggers row & constarint are set to false
+ if(server_type === 'ppas' &&
+ !_.isUndefined(is_constraint_trigger) &&
+ !_.isUndefined(is_row_trigger) &&
+ is_constraint_trigger === false &&
+ is_row_trigger === false) {
+ return false;
+ } else {
+ return true;
+ }
+ } else {
+ // Disbale it
+ return true;
+ }
+ }
+ }]
+ },{
+ id: 'whenclause', label:'{{ _('When') }}',
+ type: 'text', disabled: 'inSchemaWithModelCheck',
+ mode: ['create', 'edit', 'properties'],
+ control: 'sql-field', visible: true, group: '{{ _('Definition') }}'
+ },{
+ id: 'columns', label: '{{ _('Columns') }}',
+ type: 'collection', control: 'multi-select-ajax',
+ deps: ['evnt_update'], node: 'column', group: '{{ _('Definition') }}',
+ model: pgBrowser.Node.Model.extend({
+ keys: ['column'], defaults: { column: undefined }
+ }),
+ disabled: function(m) {
+ if(this.node_info && 'catalog' in this.node_info) {
+ return true;
+ }
+ //Disbale in edit mode
+ if (!m.isNew()) {
+ return true;
+ }
+ // Enable column only if update event is set true
+ var isUpdate = m.get('evnt_update');
+ if(!_.isUndefined(isUpdate) && isUpdate) {
+ return false;
+ }
+ return true;
+ }
+ },{
+ id: 'code', label:'{{ _('Code') }}', group: '{{ _('Code') }}',
+ type: 'text', mode: ['create', 'edit'], deps: ['tfunction'],
+ control: 'sql-field', visible: true,
+ disabled: function(m) {
+ // We will enable it only when EDB PPAS and trigger function is
+ // set to Inline EDB-SPL
+ var tfunction = m.get('tfunction'),
+ server_type = m.node_info['server']['server_type'];
+ if(!m.inSchemaWithModelCheck.apply(this, [m])) {
+ if(server_type === 'ppas' &&
+ !_.isUndefined(tfunction) &&
+ tfunction === 'Inline EDB-SPL') {
+ return false;
+ // Also clear and disable Argument field
+ } else {
+ return true;
+ }
+ } else {
+ // Disbale it
+ return true;
+ }
+ }
+ },{
+ id: 'is_sys_trigger', label:'{{ _('System trigger?') }}', cell: 'string',
+ type: 'switch', disabled: 'inSchemaWithModelCheck', mode: ['properties']
+ },{
+ id: 'is_constarint', label:'{{ _('Constraint?') }}', cell: 'string',
+ type: 'switch', disabled: 'inSchemaWithModelCheck', mode: ['properties']
+ },{
+ id: 'description', label:'{{ _('Comment') }}', cell: 'string',
+ type: 'multiline', mode: ['properties', 'create', 'edit'],
+ disabled: 'inSchema'
+ }],
+ validate: function() {
+ var err = {},
+ changedAttrs = this.changed,
+ msg = undefined;
+ this.errorModel.clear();
+
+ if(_.has(changedAttrs,this.get('name'))
+ && _.isUndefined(this.get('name'))
+ || String(this.get('name')).replace(/^\s+|\s+$/g, '') == '') {
+ msg = '{{ _('Name can not be empty!') }}';
+ this.errorModel.set('name', msg);
+ return msg;
+ }
+ if(_.has(changedAttrs,this.get('tfunction'))
+ && _.isUndefined(this.get('tfunction'))
+ || String(this.get('tfunction')).replace(/^\s+|\s+$/g, '') == '') {
+ msg = '{{ _('Trigger function can not be empty!') }}';
+ this.errorModel.set('tfunction', msg);
+ return msg;
+ }
+ return null;
+ },
+ // We will check if we are under schema node & in 'create' mode
+ inSchema: function() {
+ if(this.node_info && 'catalog' in this.node_info) {
+ return true;
+ }
+ return false;
+ },
+ // We will check if we are under schema node & in 'create' mode
+ inSchemaWithModelCheck: function(m) {
+ if(this.node_info && 'schema' in this.node_info) {
+ // We will disable control if it's in 'edit' mode
+ if (m.isNew()) {
+ return false;
+ } else {
+ return true;
+ }
+ }
+ return true;
+ },
+ // Checks weather to enable/disable control
+ inSchemaWithColumnCheck: function(m) {
+ if(this.node_info && 'schema' in this.node_info) {
+ // We will disable control if it's system columns
+ // ie: it's position is less then 1
+ if (m.isNew()) {
+ return false;
+ } else {
+ // if we are in edit mode
+ if (!_.isUndefined(m.get('attnum')) && m.get('attnum') >= 1 ) {
+ return false;
+ } else {
+ return true;
+ }
+ }
+ }
+ return true;
+ }
+ }),
+ // Below function will enable right click menu for creating column
+ 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, parents = [];
+ // To iterate over tree to check parent node
+ while (i) {
+ // If it is schema then allow user to c reate table
+ if (_.indexOf(['schema'], d._type) > -1)
+ return true;
+ parents.push(d._type);
+ i = t.hasParent(i) ? t.parent(i) : null;
+ d = i ? t.itemData(i) : null;
+ }
+ // If node is under catalog then do not allow 'create' menu
+ if (_.indexOf(parents, 'catalog') > -1) {
+ return false;
+ } else {
+ return true;
+ }
+ },
+ // Check to whether trigger is disable ?
+ canCreate_with_trigger_enable: function(itemData, item, data) {
+ if(this.canCreate.apply(this, [itemData, item, data])) {
+ // We are here means we can create menu, now let's check condition
+ if(itemData.icon === 'icon-trigger-bad') {
+ return true;
+ } else {
+ return false;
+ }
+ }
+ },
+ // Check to whether trigger is enable ?
+ canCreate_with_trigger_disable: function(itemData, item, data) {
+ if(this.canCreate.apply(this, [itemData, item, data])) {
+ // We are here means we can create menu, now let's check condition
+ if(itemData.icon === 'icon-trigger') {
+ return true;
+ } else {
+ return false;
+ }
+ }
+ }
+ });
+ }
+
+ return pgBrowser.Nodes['trigger'];
+});
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/alter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/alter.sql
new file mode 100644
index 0000000..93f323e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/alter.sql
@@ -0,0 +1,9 @@
+{## Alter index to use cluster type ##}
+{% if data.indisclustered %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
+ CLUSTER ON {{conn|qtIdent(data.name)}};
+{% endif %}
+{## Changes description ##}
+{% if data.description %}
+COMMENT ON INDEX {{conn|qtIdent(data.name)}}
+ IS {{data.description|qtLiteral}};{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/backend_support.sql
new file mode 100644
index 0000000..bb5e8d8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/backend_support.sql
@@ -0,0 +1,9 @@
+{#=============Checks if it is materialized view========#}
+{% if vid %}
+SELECT
+ CASE WHEN c.relkind = 'm' THEN False ELSE True END As m_view
+FROM
+ pg_class c
+WHERE
+ c.oid = {{ vid }}::oid
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/create.sql
new file mode 100644
index 0000000..477ffad
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/create.sql
@@ -0,0 +1,27 @@
+{### Set a flag which allows us to put OR between events ###}
+{% set or_flag = False %}
+CREATE{% if data.is_constraint_trigger %} CONSTRAINT{% endif %} TRIGGER {{ conn|qtIdent(data.name) }}
+ {{data.fires}} {% if data.evnt_insert %}INSERT{% set or_flag = True %}
+{% endif %}{% if data.evnt_delete %}
+{% if or_flag %} OR {% endif %}DELETE{% set or_flag = True %}
+{% endif %}{% if data.evnt_turncate %}
+{% if or_flag %} OR {% endif %}TRUNCATE{% set or_flag = True %}
+{% endif %}{% if data.evnt_update %}
+{% if or_flag %} OR {% endif %}UPDATE {% if data.columns|length > 0 %}OF {% for c in data.columns %}{% if loop.index != 1 %}, {% endif %}{{ conn|qtIdent(c.column) }}{% endfor %}{% endif %}
+{% endif %}
+
+ ON {{ conn|qtIdent(data.schema, data.table) }}
+{% if data.tgdeferrable %}
+ DEFERRABLE{% if data.tginitdeferred %} INITIALLY DEFERRED{% endif %}
+{% endif %}
+ FOR EACH{% if data.is_row_trigger %} ROW{% else %} STATEMENT{% endif %}
+{% if data.whenclause %}
+
+ WHEN {{ data.whenclause }}{% endif %}
+
+ {% if data.code %}{{ data.code }}{% else %}EXECUTE PROCEDURE {{ data.tfunction }}({% if data.tgargs %}{{ data.tgargs }}{% endif%}){% endif%};
+
+{% if data.description %}
+COMMENT ON TRIGGER {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }}
+ IS {{data.description|qtLiteral}};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/delete.sql
new file mode 100644
index 0000000..4c6e82b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/delete.sql
@@ -0,0 +1 @@
+DROP TRIGGER {{conn|qtIdent(data.name)}} ON {{conn|qtIdent(data.nspname, data.relname )}}{% if cascade %} CASCADE{% endif %};
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/enable_disable_trigger.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/enable_disable_trigger.sql
new file mode 100644
index 0000000..b700927
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/enable_disable_trigger.sql
@@ -0,0 +1,2 @@
+ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }}
+ {% if data.is_enable_trigger == True %}ENABLE{% else %}DISABLE{% endif %} TRIGGER {{ conn|qtIdent(data.name) }};
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/get_columns.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/get_columns.sql
new file mode 100644
index 0000000..c74c68b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/get_columns.sql
@@ -0,0 +1,6 @@
+SELECT att.attname as name
+FROM pg_attribute att
+ WHERE att.attrelid = {{tid}}::oid
+ AND att.attnum IN ({{ clist }})
+ AND att.attisdropped IS FALSE
+ ORDER BY att.attnum
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/get_oid.sql
new file mode 100644
index 0000000..cf30257
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/get_oid.sql
@@ -0,0 +1,5 @@
+SELECT t.oid
+FROM pg_trigger t
+ WHERE NOT tgisinternal
+ AND tgrelid = {{tid}}::OID
+ AND tgname = {{data.name|qtLiteral}};
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/get_parent.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/get_parent.sql
new file mode 100644
index 0000000..5dd5d3c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/get_parent.sql
@@ -0,0 +1,5 @@
+SELECT nsp.nspname AS schema ,rel.relname AS table
+FROM pg_class rel
+ JOIN pg_namespace nsp
+ ON rel.relnamespace = nsp.oid::int
+ WHERE rel.oid = {{tid}}::int
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/get_triggerfunctions.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/get_triggerfunctions.sql
new file mode 100644
index 0000000..6134e0e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/get_triggerfunctions.sql
@@ -0,0 +1,11 @@
+SELECT quote_ident(nspname) || '.' || quote_ident(proname) AS tfunctions
+FROM pg_proc p, pg_namespace n, pg_language l
+ WHERE p.pronamespace = n.oid
+ AND p.prolang = l.oid
+ -- PGOID_TYPE_TRIGGER = 2279
+ AND l.lanname != 'edbspl' AND prorettype = 2279
+ -- If Show SystemObjects is not true
+ {% if not show_system_objects %}
+ AND (nspname NOT LIKE E'pg\_%' AND nspname NOT in ('information_schema'))
+ {% endif %}
+ ORDER BY nspname ASC, proname ASC
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/nodes.sql
new file mode 100644
index 0000000..095ada3
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/nodes.sql
@@ -0,0 +1,5 @@
+SELECT t.oid, t.tgname as name, (CASE WHEN tgenabled = 'O' THEN true ElSE false END) AS is_enable_trigger
+FROM pg_trigger t
+ WHERE NOT tgisinternal
+ AND tgrelid = {{tid}}::OID
+ ORDER BY tgname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/properties.sql
new file mode 100644
index 0000000..535627b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/properties.sql
@@ -0,0 +1,23 @@
+SELECT t.oid,t.tgname AS name, t.xmin, t.*, relname, CASE WHEN relkind = 'r' THEN TRUE ELSE FALSE END AS parentistable,
+ nspname, des.description, l.lanname, p.prosrc, p.proname AS tfunction,
+ COALESCE(substring(pg_get_triggerdef(t.oid), 'WHEN (.*) EXECUTE PROCEDURE'),
+ substring(pg_get_triggerdef(t.oid), 'WHEN (.*) \\$trigger')) AS whenclause,
+ -- We need to convert tgargs column bytea datatype to array datatype
+ (string_to_array(encode(tgargs, 'escape'), '\000')::text[])[1:tgnargs] AS tgargs,
+{% if datlastsysoid %}
+ (CASE WHEN t.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_trigger,
+{% endif %}
+ (CASE WHEN tgconstraint != 0::OID THEN true ElSE false END) AS is_constarint,
+ (CASE WHEN tgenabled = 'O' THEN true ElSE false END) AS is_enable_trigger
+FROM pg_trigger t
+ JOIN pg_class cl ON cl.oid=tgrelid
+ JOIN pg_namespace na ON na.oid=relnamespace
+ LEFT OUTER JOIN pg_description des ON (des.objoid=t.oid AND des.classoid='pg_trigger'::regclass)
+ LEFT OUTER JOIN pg_proc p ON p.oid=t.tgfoid
+ LEFT OUTER JOIN pg_language l ON l.oid=p.prolang
+WHERE NOT tgisinternal
+ AND tgrelid = {{tid}}::OID
+{% if trid %}
+ AND t.oid = {{trid}}::OID
+{% endif %}
+ORDER BY tgname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/update.sql
new file mode 100644
index 0000000..e0594c2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/templates/trigger/sql/9.1_plus/update.sql
@@ -0,0 +1,8 @@
+{% if data.name and o_data.name != data.name %}
+ALTER TRIGGER {{ conn|qtIdent(o_data.name) }} ON {{ conn|qtIdent(o_data.nspname, o_data.relname) }}
+ RENAME TO {{ conn|qtIdent(data.name) }};
+{% endif %}
+{% if data.description and o_data.description != data.description %}
+COMMENT ON TRIGGER {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(o_data.nspname, o_data.relname) }}
+ IS {{data.description|qtLiteral}};
+{% endif %}
view thread (2+ messages)
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]
Subject: Re: [pgAdmin4][Patch]: Tables Sub nodes(Columns, Indexes, rules & triggers) Patch
In-Reply-To: <CAM5-9D_JXhPDZh63hmSRfYmM_6X4df3iD2Pu9NU_nJ1w8ymKMw@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