public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dave Page <[email protected]>
To: Murtuza Zabuawala <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: PATCH: Added Node Type & Catalog objects [pgAdmin4]
Date: Fri, 11 Mar 2016 16:14:05 +0000
Message-ID: <CA+OCxozEyaxygwg4KKNWKjKcLw=a-s6R64Tij9Z2JSVzzEKLrg@mail.gmail.com> (raw)
In-Reply-To: <CA+OCxoxNx3ghQZ+YnWeNpyVs_BaJE+yL7C6H-PUFh6ddpr061Q@mail.gmail.com>
References: <CAKKotZSda6+wgyzckDJPXKK3dc8U-UXho9hmaaBp+LwZzdnhaQ@mail.gmail.com>
<CA+OCxoxNx3ghQZ+YnWeNpyVs_BaJE+yL7C6H-PUFh6ddpr061Q@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
And this time with the patch.
On Fri, Mar 11, 2016 at 4:11 PM, Dave Page <[email protected]> wrote:
> On Tue, Mar 8, 2016 at 1:38 PM, Murtuza Zabuawala
> <[email protected]> wrote:
>> Hi,
>>
>> PFA patch to add new nodes in pgAdmin4.
>> 1) Type node
>> 2) Catalog objects
>>
>> Note: Both above nodes depended on schema/catalog node, Please apply them
>> after latest patch of schema/catalog from Ashesh.
>> - Type node also depends on parse_priv_function_templates.patch (which I
>> sent in separate email today)
>
> The type node seems to need quite a bit of work - please review and
> test it carefully before re-submitting. There's an updated patch
> attached, and some review info below:
>
> Changed in the attached patch:
>
> - s/Oid/OID
> - Set defaults for schema and owner
> - Rename the Type Defintion group to Definition.
> - Moved some properties (acl, members) into the appropriate group
> - s/Enumration/Enumeration
>
> To be fixed:
>
> - This module is derived from SchemaChildModule, so why does it still
> have the backend support SQL?
>
> - I cleaned up most of the SQL, which was improperly indented in many
> places. However I have not event tried to fix up the create.sql
> scripts. These need reformatting to:
> - Use 4 character indents
> - Not start a line with a comma - e.g. " ,FOO=bar", which should be
> " FOO=bar," (obviously the commas need to trail from the line
> before).
>
> - The "Show System Objects" option is not honoured.
>
> - The members list should be delimited with ", " not ","
>
> - The ACL columns don't match other objects on the subnode panel -
> Grantee/Granter/Privileges should be Grantee/Privileges/Granter
>
> - Attempting to add a security label with empty values gives: SECURITY
> LABEL FOR None ON TYPE foo_enum IS NULL;
>
> - The schema is omitted from GRANT statements when creating an object, e.g.
>
> CREATE TYPE pem.foo_enum AS ENUM
> ('foo', 'bar', 'whizz');
>
> ALTER TYPE pem.foo_enum OWNER TO postgres;
>
> COMMENT ON TYPE pem.foo_enum IS 'This is the foo enum';
>
> GRANT ALL ON TYPE foo_enum TO pem_admin;
>
> - Move remaining Properties display properties to the appropriate
> group - e.g. ENUM labels should be in the Definition group. Only Name,
> OID, Owner, Alias, System Type and Comment should be under General.
>
> - The schema name is omitted when dropping a type, leading to: type
> "foo_enum" does not exist
>
> - The dependencies and dependents tabs don't display any data.
>
> Thanks.
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
Attachments:
[application/octet-stream] type_node_v1-dave.patch (130.6K, 2-type_node_v1-dave.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/__init__.py
new file mode 100644
index 0000000..1b34ece
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/__init__.py
@@ -0,0 +1,1170 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+""" Implements Type Node """
+
+import json
+from flask import render_template, make_response, request, jsonify
+from flask.ext.babel import gettext
+from pgadmin.utils.ajax import make_json_response, \
+ make_response as ajax_response, internal_server_error
+from pgadmin.browser.utils import PGChildNodeView
+from pgadmin.browser.server_groups.servers.databases.schemas.utils \
+ import SchemaChildModule
+import pgadmin.browser.server_groups.servers.databases as database
+from pgadmin.browser.server_groups.servers.utils import parse_priv_from_db, \
+ parse_priv_to_db
+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 TypeModule(SchemaChildModule):
+ """
+ class TypeModule(SchemaChildModule)
+
+ A module class for Type node derived from SchemaChildModule
+
+ Methods:
+ -------
+ * __init__(*args, **kwargs)
+ - Method is used to initialize the Type 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 = 'type'
+ COLLECTION_LABEL = gettext("Types")
+
+ def __init__(self, *args, **kwargs):
+ """
+ Method is used to initialize the TypeModule and it's base module.
+
+ Args:
+ *args:
+ **kwargs:
+ """
+ super(TypeModule, self).__init__(*args, **kwargs)
+ self.min_ver = None
+ self.max_ver = None
+
+ def get_nodes(self, gid, sid, did, scid):
+ """
+ Generate the collection node
+ """
+ yield self.generate_browser_collection_node(scid)
+
+ @property
+ def script_load(self):
+ """
+ Load the module script for database, when any of the database node is
+ initialized.
+ """
+ return database.DatabaseModule.NODE_TYPE
+
+ @property
+ def node_inode(self):
+ """
+ Load the module node as a leaf node
+ """
+ return False
+
+ @property
+ def show_system_objects(self):
+ """
+ Flag to set show system Type
+ """
+ return True
+
+blueprint = TypeModule(__name__)
+
+
+class TypeView(PGChildNodeView):
+ """
+ This class is responsible for generating routes for Type node
+
+ Methods:
+ -------
+ * __init__(**kwargs)
+ - Method is used to initialize the TypeView and it's base view.
+
+ * 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 Type nodes within that
+ collection.
+
+ * nodes()
+ - This function will used to create all the child node within that
+ collection, Here it will create all the Type node.
+
+ * properties(gid, sid, did, scid, tid)
+ - This function will show the properties of the selected Type node
+
+ * create(gid, sid, did, scid)
+ - This function will create the new Type object
+
+ * update(gid, sid, did, scid, tid)
+ - This function will update the data for the selected Type node
+
+ * delete(self, gid, sid, scid, tid):
+ - This function will drop the Type object
+
+ * msql(gid, sid, did, scid, tid)
+ - This function is used to return modified SQL for the selected
+ Type 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 Type node.
+
+ * dependency(gid, sid, did, scid, tid):
+ - This function will generate dependency list show it in dependency
+ pane for the selected Type node.
+
+ * dependent(gid, sid, did, scid, tid):
+ - This function will generate dependent list to show it in dependent
+ pane for the selected Type node.
+
+ * additional_properties(copy_dict, tid):
+ - This function will add additional properties in response
+
+ * get_collations(gid, sid, did, scid, tid):
+ - This function will return list of collation in ajax response
+
+ * get_types(gid, sid, did, scid, tid):
+ - This function will return list of types in ajax response
+
+ * get_subtypes(gid, sid, did, scid, tid):
+ - This function will return list of subtypes in ajax response
+
+ * get_subtype_opclass(gid, sid, did, scid, tid):
+ - This function will return list of subtype opclass in ajax response
+
+ * get_subtype_diff(gid, sid, did, scid, tid):
+ - This function will return list of subtype diff functions
+ in ajax response
+
+ * get_canonical(gid, sid, did, scid, tid):
+ - This function will return list of canonical functions
+ in ajax response
+
+ * get_external_functions_list(gid, sid, did, scid, tid):
+ - This function will return list of external functions
+ in ajax response
+ """
+
+ node_type = blueprint.node_type
+
+ parent_ids = [
+ {'type': 'int', 'id': 'gid'},
+ {'type': 'int', 'id': 'sid'},
+ {'type': 'int', 'id': 'did'},
+ {'type': 'int', 'id': 'scid'}
+ ]
+ ids = [
+ {'type': 'int', 'id': 'tid'}
+ ]
+
+ 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_types': [{'get': 'get_types'}, {'get': 'get_types'}],
+ 'get_stypes': [{'get': 'get_subtypes'}, {'get': 'get_subtypes'}],
+ 'get_subopclass': [{'get': 'get_subtype_opclass'},
+ {'get': 'get_subtype_opclass'}],
+ 'get_stypediff': [{'get': 'get_subtype_diff'}, {'get': 'get_subtype_diff'}],
+ 'get_canonical': [{'get': 'get_canonical'}, {'get': 'get_canonical'}],
+ 'get_collations': [{'get': 'get_collations'}, {'get': 'get_collations'}],
+ 'get_external_functions': [{'get': 'get_external_functions_list'},
+ {'get': 'get_external_functions_list'}]
+ })
+
+ 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'])
+
+ # We need datlastsysoid to check if current type is system type
+ self.datlastsysoid = self.manager.db_info[kwargs['did']]['datlastsysoid']
+
+ # 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!"
+ )
+ )
+
+ # Declare allows acl on type
+ self.acl = ['U']
+
+ # we will set template path for sql scripts
+ ver = self.manager.version
+ if 90000 >= ver < 90100:
+ self.template_path = 'type/sql/pre_9.1'
+ else:
+ self.template_path = 'type/sql/9.1_plus'
+ return f(*args, **kwargs)
+
+ return wrap
+
+ @check_precondition
+ def list(self, gid, sid, did, scid):
+ """
+ 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: Type ID
+
+ Returns:
+ JSON of available schema nodes
+ """
+
+ SQL = render_template("/".join([self.template_path, 'properties.sql']), scid=scid,
+ datlastsysoid=self.datlastsysoid)
+ 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):
+ """
+ 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: Type ID
+
+ Returns:
+ JSON of available type child nodes
+ """
+
+ res = []
+ SQL = render_template("/".join([self.template_path,
+ 'nodes.sql']), scid=scid)
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=rset)
+
+ for row in rset['rows']:
+ res.append(
+ self.blueprint.generate_browser_node(
+ row['oid'],
+ scid,
+ row['name'],
+ icon="icon-type"
+ ))
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ def additional_properties(self, copy_dict, tid):
+ """
+ We will use this function to add additional properties according to type
+
+ Returns:
+ additional properties for type like range/composite/enum
+
+ """
+ # Fetching type of type
+ of_type = copy_dict['typtype']
+ res = dict()
+ # If type is of Composite then we need to add members list in our output
+ if of_type == 'c':
+ SQL = render_template("/".join([self.template_path,
+ 'additional_properties.sql']),
+ type='c',
+ typrelid=copy_dict['typrelid'])
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ # To display in properties
+ properties_list = []
+ # To display in composite collection grid
+ composite_lst = []
+
+ for row in rset['rows']:
+ typelist = ' '.join([row['attname'], row['typname']])
+ if not row['collname'] or (row['collname'] == 'default'
+ and row['collnspname'] == 'pg_catalog'):
+ full_collate = ''
+ collate = ''
+ else:
+ full_collate = get_driver(PG_DEFAULT_DRIVER).qtIdent(
+ self.conn, row['collnspname'], row['collname'])
+ collate = ' COLLATE ' + full_collate
+ typelist += collate
+ properties_list.append(typelist)
+
+ # Below logic will allow us to split length, precision from type name for grid
+ import re
+ matchObj = re.match( r'(.*)\((.*?),(.*?)\)', row['typname'])
+ if matchObj:
+ t_name = matchObj.group(1)
+ t_len = matchObj.group(2)
+ t_prec = matchObj.group(3)
+ else:
+ t_name = row['typname']
+ t_len = None
+ t_prec = None
+
+ composite_lst.append({
+ 'member_name': row['attname'], 'type': t_name, 'collation': full_collate,
+ 'tlength': t_len, 'precision': t_prec })
+
+ # Adding both results
+ res['member_list'] = ','.join(properties_list)
+ res['composite'] = composite_lst
+
+ # If type is of ENUM then we need to add labels in our output
+ if of_type == 'e':
+ SQL = render_template("/".join([self.template_path,
+ 'additional_properties.sql']),
+ type='e', tid=tid)
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+ # To display in properties
+ properties_list = []
+ # To display in enum grid
+ enum_list = []
+ for row in rset['rows']:
+ properties_list.append(row['enumlabel'])
+ enum_list.append({'label': row['enumlabel']})
+
+ # Adding both results in ouput
+ res['enum_list'] = ','.join(properties_list)
+ res['enum'] = enum_list
+
+ # If type is of Range then we need to add collation,subtype etc in our output
+ if of_type == 'r':
+ SQL = render_template("/".join([self.template_path,
+ 'additional_properties.sql']),
+ type='r', tid=tid)
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+ range_dict = dict(res['rows'][0])
+ res.update(range_dict)
+
+ # Returning only additional properties only
+ return res
+
+ @check_precondition
+ def properties(self, gid, sid, did, scid, tid):
+ """
+ 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: Type ID
+
+ Returns:
+ JSON of selected schema node
+ """
+
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']),
+ scid=scid, tid=tid,
+ 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
+ copy_dict = dict(res['rows'][0])
+
+ # We need to parse & convert ACL coming from database to json format
+ SQL = render_template("/".join([self.template_path, 'acl.sql']),
+ scid=scid, tid=tid)
+ 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
+ copy_dict['typacl'] = []
+
+
+ for row in acl['rows']:
+ priv = parse_priv_from_db(row)
+ if row['deftype'] in copy_dict:
+ copy_dict[row['deftype']].append(priv)
+ else:
+ copy_dict[row['deftype']] = [priv]
+
+ # Calling function to check and additional properties if available
+ copy_dict.update(self.additional_properties(copy_dict, tid))
+
+ return ajax_response(
+ response=copy_dict,
+ status=200
+ )
+
+ @check_precondition
+ def get_collations(self, gid, sid, did, scid, tid=None):
+ """
+ This function will return list of collation available
+ as 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=None):
+ """
+ This function will return list of types available
+ as AJAX response.
+ """
+ res = []
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_types.sql']))
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ 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 get_subtypes(self, gid, sid, did, scid, tid=None):
+ """
+ This function will return list of subtypes available
+ as AJAX response.
+ """
+ res = [{'label': '', 'value': ''}]
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_subtypes.sql']),
+ subtype=True)
+ 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['stype'], 'value': row['stype'],
+ 'is_collate': row['is_collate']}
+ )
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def get_subtype_opclass(self, gid, sid, did, scid, tid=None):
+ """
+ This function will return list of subtype opclass available
+ as AJAX response.
+ """
+ res = [{'label': '', 'value': ''}]
+ data = request.args
+
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_subtypes.sql']),
+ subtype_opclass=True, data=data)
+ if 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['opcname'],
+ 'value': row['opcname']})
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def get_subtype_diff(self, gid, sid, did, scid, tid=None):
+ """
+ This function will return list of subtypes diff functions available
+ as AJAX response.
+ """
+ res = [{'label': '', 'value': ''}]
+ data = request.args
+
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_subtypes.sql']),
+ get_opcintype=True, data=data)
+ if SQL:
+ status, opcintype = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=opcintype)
+ SQL = render_template("/".join([self.template_path,
+ 'get_subtypes.sql']),
+ opcintype=opcintype, conn=self.conn)
+ 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['stypdiff'],
+ 'value': row['stypdiff']}
+ )
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def get_canonical(self, gid, sid, did, scid, tid=None):
+ """
+ This function will return list of canonical functions available
+ as AJAX response.
+ """
+ res = [{'label': '', 'value': ''}]
+ data = request.args
+ canonical = True
+
+ try:
+ # We want to send data only if in we are in edit mode
+ # else we will disable the combobox
+ SQL = render_template("/".join([self.template_path,
+ 'get_subtypes.sql']),
+ getoid=True, data=data)
+ if SQL:
+ status, oid = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=oid)
+ # If oid is None then do not run SQL
+ if oid is None:
+ canonical = False
+
+ SQL = render_template("/".join([self.template_path,
+ 'get_subtypes.sql']),
+ canonical=canonical, conn=self.conn, oid=oid)
+ if 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['canonical'],
+ 'value': row['canonical']})
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+
+ @check_precondition
+ def get_external_functions_list(self, gid, sid, did, scid, tid=None):
+ """
+ This function will return list of external functions available
+ as AJAX response.
+ """
+ res = [{'label': '', 'value': '', 'cbtype': 'all'}]
+
+ try:
+ # The SQL generated below will populate Input/Output/Send/
+ # Receive/Analyze/TypModeIN/TypModOUT combo box
+ SQL = render_template("/".join([self.template_path,
+ 'get_external_functions.sql']),
+ extfunc=True)
+ if 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['func'], 'value': row['func'],
+ 'cbtype': 'all'})
+
+ # The SQL generated below will populate TypModeIN combo box
+ SQL = render_template("/".join([self.template_path,
+ 'get_external_functions.sql']),
+ typemodin=True)
+ if 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['func'], 'value': row['func'],
+ 'cbtype': 'typmodin'})
+
+ # The SQL generated below will populate TypModeIN combo box
+ SQL = render_template("/".join([self.template_path,
+ 'get_external_functions.sql']),
+ typemodout=True)
+ if 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['func'], 'value': row['func'],
+ 'cbtype': 'typmodout'})
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+
+ @check_precondition
+ def create(self, gid, sid, did, scid):
+ """
+ This function will creates new the schema object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Type ID
+ """
+ data = request.form if request.form else json.loads(request.data.decode())
+ required_args = {
+ 'name': 'Name',
+ 'typtype': '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]
+ )
+ )
+ # Additional checks goes here
+ # If type is composite then check if it has two members
+ if data and data[arg] == 'c':
+ if len(data['composite']) < 2:
+ return make_json_response(
+ status=410,
+ success=0,
+ errormsg=gettext(
+ 'Composite type requires at least two members'
+ )
+ )
+ # If type is enum then check if it has minimum one label
+ if data and data[arg] == 'e':
+ if len(data['enum']) < 1:
+ return make_json_response(
+ status=410,
+ success=0,
+ errormsg=gettext(
+ 'Enumeration type requires at least one label'
+ )
+ )
+ # If type is enum then check if it has minimum one label
+ if data and data[arg] == 'r':
+ if data['typname'] is None:
+ return make_json_response(
+ status=410,
+ success=0,
+ errormsg=gettext(
+ 'Subtype must be defined for range type'
+ )
+ )
+ # If type is external then check if input/output
+ # conversion function is defined
+ if data and data[arg] == 'x':
+ if data['typinput'] is None or \
+ data['typoutput'] is None:
+ return make_json_response(
+ status=410,
+ success=0,
+ errormsg=gettext(
+ 'External type requires both Input & Output conversion function.'
+ )
+ )
+
+ # To format privileges coming from client
+ if 'typacl' in data and data['typacl'] is not None:
+ data['typacl'] = parse_priv_to_db(data['typacl'], self.acl)
+
+ 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_oid.sql']),
+ scid=scid, data=data)
+ status, tid = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=tid)
+
+ return jsonify(
+ node=self.blueprint.generate_browser_node(
+ tid,
+ scid,
+ data['name'],
+ icon="icon-type"
+ )
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def update(self, gid, sid, did, scid, tid):
+ """
+ This function will updates existing the type object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Type ID
+ """
+
+ data = request.form if request.form else json.loads(request.data.decode())
+ try:
+ SQL = self.get_sql(gid, sid, data, scid, tid)
+ 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="Type updated",
+ data={
+ 'id': tid,
+ 'scid': scid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+ else:
+ return make_json_response(
+ success=1,
+ info="Nothing to update",
+ data={
+ 'id': tid,
+ 'scid': scid,
+ '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):
+ """
+ This function will updates existing the schema object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Type 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:
+ SQL = render_template("/".join([self.template_path, 'get_name.sql']),
+ scid=scid, tid=tid)
+ status, name = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=name)
+
+ SQL = render_template("/".join([self.template_path, 'delete.sql']),
+ name=name, cascade=cascade, 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("Type dropped"),
+ data={
+ 'id': tid,
+ 'scid': scid,
+ '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=None):
+ """
+ This function will generates modified sql for type object
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Type ID
+ """
+ req = request.args
+ data = dict()
+
+ # converting nested request data in proper json format
+ for key,val in req.items():
+ if key in ['composite', 'enum', 'seclabels', 'typacl']:
+ data[key] = json.loads(val)
+ else:
+ data[key] = val
+
+ try:
+ SQL = self.get_sql(gid, sid, data, scid, tid)
+
+ if SQL and SQL.strip('\n') and SQL.strip(' '):
+ return make_json_response(
+ data=SQL,
+ status=200
+ )
+ except Exception as e:
+ internal_server_error(errormsg=str(e))
+
+ def get_sql(self, gid, sid, data, scid, tid=None):
+ """
+ This function will genrate sql from model data
+ """
+ if tid is not None:
+
+ for key in ['typacl']:
+ 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, 'properties.sql']),
+ scid=scid, tid=tid,
+ 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
+ old_data = dict(res['rows'][0])
+
+ SQL = render_template("/".join([self.template_path, 'acl.sql']),
+ scid=scid, tid=tid)
+ 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
+ old_data['typacl'] = []
+
+ for row in acl['rows']:
+ priv = parse_priv_from_db(row)
+ if row['deftype'] in old_data:
+ old_data[row['deftype']].append(priv)
+ else:
+ old_data[row['deftype']] = [priv]
+
+ # Calling function to check and additional properties if available
+ old_data.update(self.additional_properties(old_data, tid))
+
+ SQL = render_template(
+ "/".join([self.template_path, 'update.sql']),
+ data=data, o_data=old_data, conn=self.conn
+ )
+ else:
+ required_args = [
+ 'name',
+ 'typtype'
+ ]
+
+ for arg in required_args:
+ if arg not in data:
+ return " --definition incomplete"
+
+ # Privileges
+ if 'typacl' in data and data['typacl'] is not None:
+ data['typacl'] = parse_priv_to_db(data['typacl'], self.acl)
+
+ 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):
+ """
+ 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: Type ID
+ """
+ SQL = render_template("/".join([self.template_path, 'properties.sql']),
+ scid=scid, tid=tid,
+ 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])
+
+ SQL = render_template("/".join([self.template_path, 'acl.sql']),
+ scid=scid, tid=tid)
+ 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['typacl'] = []
+
+ for row in acl['rows']:
+ priv = parse_priv_from_db(row)
+ if row['deftype'] in data:
+ data[row['deftype']].append(priv)
+ else:
+ data[row['deftype']] = [priv]
+
+ # Privileges
+ if 'typacl' in data and data['typacl'] is not None:
+ data['nspacl'] = parse_priv_to_db(data['typacl'], self.acl)
+
+ # Calling function to check and additional properties if available
+ data.update(self.additional_properties(data, tid))
+
+ # We do not want to display table which has '-' value
+ # setting them to None so that jinja avoid displaying them
+ for k in data:
+ if data[k] == '-':
+ data[k] = None
+
+ SQL = self.get_sql(gid, sid, data, scid, tid=None)
+
+ # We are appending headers here for sql panel
+ sql_header = "-- Type: {0}\n\n-- ".format(data['name'])
+ sql_header += render_template("/".join([self.template_path,
+ 'delete.sql']),
+ name=data['name'], conn=self.conn)
+ SQL = sql_header + '\n' + SQL
+
+ return ajax_response(response=SQL)
+
+ @check_precondition
+ def dependents(self, gid, sid, did, scid, tid):
+ """
+ This function get the dependents and return ajax response
+ for the type node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Type ID
+ """
+ dependents_result = self.get_dependents(
+ self.conn, tid
+ )
+
+ return ajax_response(
+ response=dependents_result,
+ status=200
+ )
+
+ @check_precondition
+ def dependencies(self, gid, sid, did, scid, tid):
+ """
+ This function get the dependencies and return ajax response
+ for the type node.
+
+ Args:
+ gid: Server Group ID
+ sid: Server ID
+ did: Database ID
+ scid: Schema ID
+ tid: Type ID
+ """
+ dependencies_result = self.get_dependencies(
+ self.conn, tid
+ )
+
+ return ajax_response(
+ response=dependencies_result,
+ status=200
+ )
+
+TypeView.register_node_view(blueprint)
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/static/img/coll-type.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/static/img/coll-type.png
new file mode 100644
index 0000000000000000000000000000000000000000..fb020d7d99f84439046d288615e865ee1fbdb815
GIT binary patch
literal 329
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!73?$#)eFPFv3GfMV1=3NcXI=aM>fHaQT@Q91
z`hS1R{~OExU!3;;MEm~(mH&6<{olN3-cDJdI>wS9zhDN3XE)M-9L@rd$YLPv0mg18
zv+aP47*7|+5RU7%XQO!=40zlgo^wo$EU!My#3j(ks*}LT9dUr^nFV*x`<LfEXLU&{
zNT_QZ){p11jr8BRJf*J9t1q$D%X6FK(q}uIk4`OV_&v+tAZF)<c~_)!|NM{V=e#WX
zN#J9vG0+~>64!{5l*E!$tK_0oAjM#0U}&IgXryak7-D2#Wnye)VybOmYGq(B@15Q%
q6b-rgDVb@N5Df;FU=2XkCRPS!5DllMhpqu?VDNPHb6Mw<&;$UZ$8*O3
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/static/img/type.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/static/img/type.png
new file mode 100644
index 0000000000000000000000000000000000000000..6c16764e7d08c56922a97a2f0c6cc06455c86a56
GIT binary patch
literal 325
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!73?$#)eFPFv5AX?b1=8~#9EmzT>)QWU=l(xE
z`v2js|F_rtzdY~%nF;@oHvQjQ{(pPk|IMk)0@*;Nj3q&S!3+-1ZlnP@oCO|{#X#Bv
zjNMLV+W{G&o-U3d9M_W*4zM_RIV|8v(U4|t6r8Z|5fh7_LtB=H01KmJR;I%QmXsCK
znH_-=7a3W69onAxD9m6<$ym$O<m%A&El=SFOUjEmj7`o4+9Dfn@G_j<Bfar~Z-)!e
z0@V`Nh?11Vl2ohYqEsNoU}RuuplfKPYhV~+WME}tY-M7qZD49;U@-5U-YOIgx%nxX
kX_XKS29{tAK-DHz24)Zqr>2Ll0cv3IboFyt=akR{01h5&qyPW_
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/js/type.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/js/type.js
new file mode 100644
index 0000000..eecce4e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/js/type.js
@@ -0,0 +1,856 @@
+define(
+ ['jquery', 'underscore', 'underscore.string', 'pgadmin',
+ 'pgadmin.browser', 'alertify', 'backgrid', 'pgadmin.backgrid',
+ 'pgadmin.browser.collection'],
+function($, _, S, pgAdmin, pgBrowser, alertify, Backgrid) {
+
+ if (!pgBrowser.Nodes['coll-type']) {
+ var databases = pgAdmin.Browser.Nodes['coll-type'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'type',
+ label: '{{ _('Types') }}',
+ type: 'coll-type',
+ columns: ['name', 'typeowner', 'description']
+ });
+ };
+
+ // Switch options to save space in model's field
+ var switchOptions = {
+ 'onText': 'Yes', 'offText': 'No',
+ 'onColor': 'success', 'offColor': 'default',
+ 'size': 'small'
+ };
+
+ // Security label model declaration
+ var SecurityModel = Backform.SecurityModel = pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ provider: null,
+ security_label: null
+ },
+ schema: [{
+ id: 'provider', label: '{{ _('Provider') }}',
+ type: 'text', disabled: false
+ },{
+ id: 'security_label', label: '{{ _('Security Label') }}',
+ type: 'text', disabled: false
+ }],
+ validate: function() {
+ var err = {},
+ errmsg = null,
+ data = this.toJSON();
+
+ if (_.isUndefined(data.label) ||
+ _.isNull(data.label) ||
+ String(data.label).replace(/^\s+|\s+$/g, '') == '') {
+ errmsg = _("Please specify the value for all the security providers.");
+ this.errorModel.set('security_label', errmsg);
+ } else {
+ this.errorModel.unset('security_label');
+ }
+ return null;
+ }
+ });
+
+ // Composite type model declaration
+ var CompositeModel = Backform.CompositeModel = pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ member_name: undefined,
+ type: undefined,
+ tlength: undefined,
+ is_tlength: false,
+ precision: undefined,
+ is_precision: false,
+ collation: undefined,
+ min_val: undefined,
+ max_val: undefined,
+ },
+ type_options: undefined,
+ subtypes: undefined,
+ schema: [{
+ id: 'member_name', label: '{{ _('Member Name') }}',
+ type: 'text', disabled: false, editable: false
+ },{
+ id: 'type', label: '{{ _('Type') }}', control: 'node-ajax-options',
+ type: 'text', url: 'get_types', disabled: false, node: 'type',
+ editable: false,
+ transform: function(d){
+ this.model.type_options = d;
+ return d;
+ }
+ },{
+ id: 'tlength', label: '{{ _('Length') }}', deps: ['type'], type: 'text',
+ editable: false,
+ disabled: function(m) {
+ // We will store type from selected from combobox
+ var of_type = m.get('type');
+ if(m.type_options) {
+ // iterating over all the types
+ _.each(m.type_options, function(o) {
+ // if type from selected from combobox matches in options
+ if ( of_type == o.value ) {
+ // if length is allowed for selected type
+ if(o.length)
+ {
+ // set the values in model
+ m.set('is_tlength', true, {silent: true});
+ m.set('min_val', o.min_val, {silent: true});
+ m.set('max_val', o.max_val, {silent: true});
+ }
+ }
+ });
+ }
+ return !m.get('is_tlength');
+ }
+ },{
+ id: 'precision', label: '{{ _('Precision') }}', deps: ['type'],
+ type: 'text', editable: false,
+ disabled: function(m) {
+ // We will store type from selected from combobox
+ var of_type = m.get('type');
+ if(m.type_options) {
+ // iterating over all the types
+ _.each(m.type_options, function(o) {
+ // if type from selected from combobox matches in options
+ if ( of_type == o.value ) {
+ // if precession is allowed for selected type
+ if(o.precision)
+ {
+ // set the values in model
+ m.set('is_precision', true, {silent: true});
+ m.set('min_val', o.min_val, {silent: true});
+ m.set('max_val', o.max_val, {silent: true});
+ }
+ }
+ });
+ }
+ return !m.get('is_precision');
+ }
+ },{
+ id: 'collation', label: '{{ _('Collation') }}',
+ control: 'node-ajax-options', editable: false,
+ type: 'text', disabled: false, url: 'get_collations', node: 'type'
+ }],
+ validate: function() {
+ var err = {},
+ errmsg = null,
+ data = this.toJSON();
+ // Validation for member name
+ if (_.isUndefined(data.member_name) ||
+ _.isNull(data.member_name) ||
+ String(data.member_name).replace(/^\s+|\s+$/g, '') == '') {
+ errmsg = _("Please specify the value for member name.")
+ this.errorModel.set('member_name', errmsg)
+ return errmsg;
+ } else {
+ this.errorModel.unset('member_name');
+ }
+
+ // Validation for Length field
+ if (data.is_tlength && !_.isUndefined(data.tlength)) {
+ if (data.tlength < data.min_val )
+ errmsg = _("Length should not be less than " + data.min_val)
+ if (data.tlength > data.max_val )
+ errmsg = _("Length should not be greater than " + data.max_val)
+ // If we have any error set then throw it to user
+ if(errmsg) {
+ this.errorModel.set('tlength', errmsg)
+ return errmsg;
+ }
+ } else {
+ this.errorModel.unset('tlength');
+ }
+
+ // Validation for precision field
+ if (data.is_precision && !_.isUndefined(data.precision)) {
+ if (data.precision < data.min_val )
+ errmsg = _("Precision should not be less than " + data.min_val)
+ if (data.precision > data.max_val )
+ errmsg = _("Precision should not be greater than " + data.max_val)
+ // If we have any error set then throw it to user
+ if(errmsg) {
+ this.errorModel.set('precision', errmsg)
+ return errmsg;
+ }
+ } else {
+ this.errorModel.unset('precision');
+ }
+
+ return null;
+ }
+ });
+
+ var EnumModel = Backform.EnumModel = pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ label: null,
+ },
+ schema: [{
+ id: 'label', label: '{{ _('Label') }}',type: 'text', disabled: false,
+ cellHeaderClasses: 'width_percent_99'
+ }],
+ validate: function() {
+ var err = {},
+ errmsg = null,
+ data = this.toJSON();
+
+ if (_.isUndefined(data.label) ||
+ _.isNull(data.label) ||
+ String(data.label).replace(/^\s+|\s+$/g, '') == '') {
+ errmsg = _("Please specify the value for label.");
+ this.errorModel.set('label', errmsg)
+ return errmsg;
+ } else {
+ this.errorModel.unset('label');
+ }
+
+
+ return null;
+ }
+ });
+
+ if (!pgBrowser.Nodes['type']) {
+ pgAdmin.Browser.Nodes['type'] = pgBrowser.Node.extend({
+ type: 'type',
+ label: '{{ _('Type') }}',
+ collection_type: 'coll-type',
+ hasSQL: true,
+ hasDepends: true,
+ parent_type: ['schema', 'catalog'],
+ Init: function() {
+ /* Avoid mulitple registration of menus */
+ if (this.initialized)
+ return;
+
+ this.initialized = true;
+
+ pgBrowser.add_menus([{
+ name: 'create_type_on_coll', node: 'coll-type', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Type...') }}',
+ icon: 'wcTabIcon icon-type', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_type', node: 'type', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Type...') }}',
+ icon: 'wcTabIcon icon-type', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_type', node: 'schema', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Type...') }}',
+ icon: 'wcTabIcon icon-type', data: {action: 'create', check: false},
+ enable: 'canCreate'
+ }
+ ]);
+
+ },
+ canDrop: pgBrowser.Nodes['schema'].canChildDrop,
+ canDropCascade: pgBrowser.Nodes['schema'].canChildDrop,
+ ext_funcs: undefined,
+ model: pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ name: undefined,
+ oid: undefined,
+ is_sys_type: false,
+ typtype: undefined
+ },
+
+ // Default values!
+ initialize: function(attrs, args) {
+ var isNew = (_.size(attrs) === 0);
+
+ if (isNew) {
+ var userInfo = pgBrowser.serverInfo[args.node_info.server._id].user;
+ var schemaInfo = args.node_info.schema;
+
+ this.set({'typeowner': userInfo.name}, {silent: true});
+ this.set({'schema': schemaInfo.label}, {silent: true});
+ }
+ pgAdmin.Browser.Node.Model.prototype.initialize.apply(this, arguments);
+ },
+
+ schema: [{
+ id: 'name', label: '{{ _('Name') }}', cell: 'string',
+ type: 'text', mode: ['properties', 'create', 'edit'],
+ disabled: 'inSchema'
+ },{
+ id: 'oid', label:'{{ _('OID') }}', cell: 'string',
+ type: 'text' , mode: ['properties'], disabled: true
+ },{
+ id: 'typeowner', label:'{{ _('Owner') }}', cell: 'string',
+ control: 'node-list-by-name',
+ type: 'text', mode: ['properties', 'create', 'edit'], node: 'role',
+ disabled: 'inSchema'
+ },{
+ id: 'schema', label:'{{ _('Schema') }}', cell: 'string',
+ type: 'text', mode: ['create', 'edit'], node: 'schema',
+ disabled: 'inSchema', filter: function(d) {
+ // If schema name start with pg_* then we need to exclude them
+ if(d && d.label.match(/^pg_/))
+ {
+ return false;
+ }
+ return true;
+ },
+ control: Backform.NodeListByNameControl.extend({
+ render: function(){
+ // Initialize parent's render method
+ Backform.NodeListByNameControl.prototype.render.apply(this, arguments);
+
+ // Set schema default value to its parent Schema
+ if(this.model.isNew()){
+ this.model.set({'schema': this.model.node_info.schema.label});
+ }
+ return this;
+ }
+ })
+ },{
+ id: 'typtype', label:'{{ _('Type') }}',
+ mode: ['create','edit'], disabled: 'inSchemaWithModelCheck',
+ group: '{{ _('Definition') }}',
+ mode: ['edit', 'create'],
+ select2: { width: "50%" },
+ options: [
+ {label: "Composite", value: "c"},
+ {label: "Enumeration", value: "e"},
+ {label: "External", value: "x"},
+ {label: "Range", value: "r"},
+ ],
+ disabled: 'inSchemaWithModelCheck',
+ // 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.set({'typtype': 'c'});
+ }
+ return this;
+ }
+ })
+ },{
+ id: 'composite', label: '{{ _('Composite Type') }}',
+ model: CompositeModel, editable: true, type: 'collection',
+ group: '{{ _('Definition') }}', mode: ['edit', 'create'],
+ control: 'unique-col-collection', uniqueCol : ['member_name'],
+ canAdd: true, canEdit: true, canDelete: true, disabled: 'inSchema',
+ deps: ['typtype'], deps: ['typtype'],
+ visible: function(m) {
+ if (m.get('typtype') === 'c') {
+ return true;
+ }
+ return false;
+ }
+ },{
+ id: 'enum', label: '{{ _('Enumeration Type') }}',
+ model: EnumModel, editable: true, type: 'collection',
+ group: '{{ _('Definition') }}', mode: ['edit', 'create'],
+ canAdd: true, canEdit: false, canDelete: function(m) {
+ // We will disable it if it's in 'edit' mode
+ if (m.isNew()) {
+ return true;
+ } else {
+ return false;
+ }
+ },
+ disabled: 'inSchema', deps: ['typtype'],
+ control: 'unique-col-collection', uniqueCol : ['label'],
+ visible: function(m) {
+ return m.get('typtype') === 'e';
+ }
+ },{
+ // We will disable range type control in edit mode
+ type: 'nested', control: 'fieldset', group: '{{ _('Definition') }}',
+ mode: ['edit', 'create'],
+ visible: function(m) {
+ return m.get('typtype') === 'r';
+ }, deps: ['typtype'], label: '{{ _('Range Type') }}',
+ schema:[{
+ id: 'typname', label:'{{ _('Subtype') }}', cell: 'string',
+ control: 'node-ajax-options',
+ url: 'get_stypes', type: 'text', mode: ['properties', 'create', 'edit'],
+ group: '{{ _('Range Type') }}', disabled: 'inSchemaWithModelCheck',
+ transform: function(d){
+ this.model.subtypes = d;
+ return d;
+ }
+ },{
+ id: 'opcname', label:'{{ _('Subtype OpClass') }}', cell: 'string',
+ mode: ['properties', 'create', 'edit'], group: '{{ _('Range Type') }}',
+ disabled: 'inSchemaWithModelCheck', deps: ['typname'],
+ control: 'select', options: function() {
+ var l_typname = this.model.get('typname'),
+ self = this,
+ result = [];
+ if(!_.isUndefined(l_typname) && l_typname != '')
+ {
+ var node = this.field.get('schema_node'),
+ _url = node.generate_url.apply(
+ node, [
+ null, 'get_subopclass', this.field.get('node_data'), false,
+ this.field.get('node_info')
+ ]);
+ $.ajax({
+ async: false,
+ url: _url,
+ cache: false,
+ data: {'typname' : l_typname},
+ success: function(res) {
+ result = res.data;
+ },
+ error: function() {
+ self.model.trigger('pgadmin:view:fetch:error', self.model, self.field);
+ }
+ });
+ //
+ }
+ return result;
+ }
+ },{
+ id: 'collname', label:'{{ _('Collation') }}', cell: 'string',
+ type: 'text', mode: ['properties', 'create', 'edit'],
+ group: '{{ _('Range Type') }}',
+ deps: ['typname'], control: 'node-ajax-options', url: 'get_collations',
+ disabled: function(m) {
+ if(this.node_info && 'catalog' in this.node_info)
+ {
+ return true;
+ }
+
+ // Disbale in edit mode
+ if (!m.isNew()) {
+ return true;
+ }
+
+ // To check if collation is allowed?
+ var of_subtype = m.get('typname'),
+ is_collate = undefined;
+ if(!_.isUndefined(of_subtype)) {
+ // iterating over all the types
+ _.each(m.subtypes, function(s) {
+ // if subtype from selected from combobox matches
+ if ( of_subtype === s.label ) {
+ // if collation is allowed for selected subtype
+ // then enable it else disable it
+ is_collate = s.is_collate;
+ }
+ });
+ }
+ // If is_collate is true then do not disable
+ return is_collate ? false : true;
+ }
+ },{
+ id: 'rngcanonical', label:'{{ _('Canonical') }}', cell: 'string',
+ type: 'text', mode: ['properties', 'create', 'edit'],
+ group: '{{ _('Range Type') }}',
+ disabled: 'inSchemaWithModelCheck', deps: ['name', 'typname'],
+ control: 'select', options: function() {
+ var name = this.model.get('name'),
+ self = this,
+ result = [];
+
+ if(!_.isUndefined(name) && name != '')
+ {
+ var node = this.field.get('schema_node'),
+ _url = node.generate_url.apply(
+ node, [
+ null, 'get_canonical', this.field.get('node_data'), false,
+ this.field.get('node_info')
+ ]);
+ $.ajax({
+ async: false,
+ url: _url,
+ cache: false,
+ data: {"name" : name},
+ success: function(res) {
+ result = res.data;
+ },
+ error: function() {
+ self.model.trigger('pgadmin:view:fetch:error',
+ self.model, self.field);
+ }
+ });
+ }
+ return result;
+ }
+ },{
+ id: 'rngsubdiff', label:'{{ _('SubType diff') }}', cell: 'string',
+ type: 'text', mode: ['properties', 'create', 'edit'],
+ group: '{{ _('Range Type') }}',
+ disabled: 'inSchemaWithModelCheck', deps: ['opcname'],
+ control: 'select', options: function() {
+ var l_typname = this.model.get('typname'),
+ l_opcname = this.model.get('opcname'),
+ self = this,
+ result = [];
+
+ if(!_.isUndefined(l_typname) && l_typname != '' &&
+ !_.isUndefined(l_opcname) && l_opcname != '') {
+ var node = this.field.get('schema_node'),
+ _url = node.generate_url.apply(
+ node, [
+ null, 'get_stypediff',
+ this.field.get('node_data'), false,
+ this.field.get('node_info')
+ ]);
+ $.ajax({
+ async: false,
+ url: _url,
+ cache: false,
+ data: {'typname' : l_typname, 'opcname': l_opcname},
+ success: function(res) {
+ result = res.data;
+ },
+ error: function() {
+ self.model.trigger('pgadmin:view:fetch:error',
+ self.model, self.field);
+ }
+ });
+ }
+ return result;
+ }
+ }]
+ },{
+ type: 'nested', control: 'tab', group: '{{ _('Definition') }}',
+ label: '{{ _('External Type') }}', deps: ['typtype'],
+ mode: ['create', 'edit'],
+ visible: function(m) {
+ return m.get('typtype') === 'x';
+ },
+ schema:[{
+ id: 'typinput', label:'{{ _('Input function') }}',
+ cell: 'string',type: 'text',
+ mode: ['properties', 'create', 'edit'], group: 'Required',
+ disabled: 'inSchemaWithModelCheck',
+ control: 'node-ajax-options', url: 'get_external_functions',
+ transform: 'external_func_combo'
+ },{
+ id: 'typoutput', label:'{{ _('Output function') }}',
+ cell: 'string',
+ type: 'text', mode: ['properties', 'create', 'edit'],
+ group: 'Required',
+ disabled: 'inSchemaWithModelCheck'
+ ,control: 'node-ajax-options', url: 'get_external_functions',
+ transform: 'external_func_combo'
+ },{
+ id: 'typreceive', label:'{{ _('Receive function') }}',
+ cell: 'string', type: 'text', group: 'Optional-1',
+ mode: ['properties', 'create', 'edit'],
+ disabled: 'inSchemaWithModelCheck'
+ ,control: 'node-ajax-options', url: 'get_external_functions',
+ transform: 'external_func_combo'
+ },{
+ id: 'typsend', label:'{{ _('Send function') }}',
+ cell: 'string', group: 'Optional-1',
+ type: 'text', mode: ['properties', 'create', 'edit'],
+ disabled: 'inSchemaWithModelCheck'
+ ,control: 'node-ajax-options', url: 'get_external_functions',
+ transform: 'external_func_combo'
+ },{
+ id: 'typmodin', label:'{{ _('Typmod in function') }}',
+ cell: 'string', type: 'text',
+ mode: ['properties', 'create', 'edit'], group: 'Optional-1',
+ disabled: 'inSchemaWithModelCheck'
+ ,control: 'node-ajax-options', url: 'get_external_functions',
+ transform: function(d) {
+ var result = [{label :"", value : ""}];
+ _.each(d, function(item) {
+ // if type from selected from combobox matches in options
+ if ( item.cbtype === 'typmodin' || item.cbtype === 'all') {
+ result.push(item);
+ }
+ });
+ return result;
+ }
+ },{
+ id: 'typmodout', label:'{{ _('Typmod out function') }}',
+ cell: 'string', group: 'Optional-1',
+ type: 'text', mode: ['properties', 'create', 'edit'],
+ disabled: 'inSchemaWithModelCheck'
+ ,control: 'node-ajax-options', url: 'get_external_functions',
+ transform: function(d) {
+ var result = [{label :"", value : ""}];
+ _.each(d, function(item) {
+ // if type from selected from combobox matches in options
+ if ( item.cbtype === 'typmodout' || item.cbtype === 'all') {
+ result.push(item);
+ }
+ });
+ return result;
+ }
+ },{
+ id: 'typlen', label:'{{ _('Internal length') }}',
+ cell: 'integer', group: 'Optional-1',
+ type: 'int', mode: ['properties', 'create', 'edit'],
+ disabled: 'inSchemaWithModelCheck'
+ },{
+ id: 'variable', label:'{{ _('Variable') }}', cell: 'switch',
+ group: 'Optional-1', type: 'switch',
+ mode: ['create','edit'], options: switchOptions,
+ disabled: 'inSchemaWithModelCheck'
+ },{
+ id: 'typdefault', label:'{{ _('Default') }}',
+ cell: 'string', group: 'Optional-1',
+ type: 'text', mode: ['properties', 'create','edit'],
+ disabled: 'inSchemaWithModelCheck'
+ },{
+ id: 'typanalyze', label:'{{ _('Analyze function') }}',
+ cell: 'string', group: 'Optional-1',
+ type: 'text', mode: ['properties', 'create','edit'],
+ disabled: 'inSchemaWithModelCheck'
+ ,control: 'node-ajax-options', url: 'get_external_functions',
+ transform: 'external_func_combo'
+ },{
+ id: 'typcategory', label:'{{ _('Category') }}',
+ cell: 'string', group: 'Optional-1',
+ type: 'text', mode: ['properties', 'create','edit'],
+ disabled: 'inSchemaWithModelCheck', control: 'select2',
+ select2: { placeholder: "Select category", allowClear: true,
+ width: "100%" },
+ options: [
+ {label :"", value : ""},
+ {label :"Array types", value : "A"},
+ {label :"Boolean types", value : "B"},
+ {label :"Composite types", value : "C"},
+ {label :"Date/time types", value : "D"},
+ {label :"Enum types", value : "E"},
+ {label :"Geometric types", value : "G"},
+ {label :"Network address types", value : "I"},
+ {label :"Numeric types", value : "N"},
+ {label :"Pseudo-types", value : "P"},
+ {label :"String types", value : "S"},
+ {label :"Timespan types", value : "T"},
+ {label :"User-defined types", value : "U"},
+ {label :"Bit-string types", value : "V"},
+ {label :"unknown type", value : "X"}
+ ]
+ },{
+ id: 'typispreferred', label:'{{ _('Prefered') }}', cell: 'switch',
+ type: 'switch', mode: ['properties', 'create','edit'],
+ options: switchOptions, disabled: 'inSchemaWithModelCheck',
+ group: 'Optional-1'
+ },{
+ id: 'element', label:'{{ _('Element') }}', cell: 'string',
+ control: 'node-ajax-options', group: 'Optional-2',
+ type: 'text', mode: ['properties', 'create', 'edit'],
+ disabled: 'inSchemaWithModelCheck', url: 'get_types'
+ },{
+ id: 'typdelim', label:'{{ _('Delimiter') }}', cell: 'string',
+ type: 'text', mode: ['properties', 'create', 'edit'],
+ group: 'Optional-2', disabled: 'inSchemaWithModelCheck'
+ },{
+ id: 'typalign', label:'{{ _('Alignment') }}',
+ cell: 'string', group: 'Optional-2',
+ type: 'text', mode: ['properties', 'create', 'edit'],
+ disabled: 'inSchemaWithModelCheck', control: 'select2',
+ select2: { placeholder: "Select alignment", allowClear: true,
+ width: "100%" },
+ options: [
+ {label :"", value : ""},
+ {label: "char", value: "c"},
+ {label: "int2", value: "s"},
+ {label: "in4", value: "i"},
+ {label: "double", value: "d"},
+ ]
+ },{
+ id: 'typstorage', label:'{{ _('Storage') }}',
+ type: 'text', mode: ['properties', 'create', 'edit'],
+ group: 'Optional-2', cell: 'string',
+ disabled: 'inSchemaWithModelCheck', control: 'select2',
+ select2: { placeholder: "Select storage", allowClear: true,
+ width: "100%" },
+ options: [
+ {label :"", value : ""},
+ {label: "PLAIN", value: "p"},
+ {label: "MAIN", value: "e"},
+ {label: "EXTERNAL", value: "m"},
+ {label: "EXTENDED", value: "x"},
+ ]
+ },{
+ id: 'typbyval', label:'{{ _('Passed by Value?') }}',
+ cell: 'switch', options: switchOptions,
+ type: 'switch', mode: ['properties', 'create', 'edit'],
+ disabled: 'inSchemaWithModelCheck', group: 'Optional-2',
+ },{
+ id: 'is_collatable', label:'{{ _('Collatable?') }}',
+ cell: 'switch', min_version: 90100, group: 'Optional-2',
+ type: 'switch', mode: ['properties', 'create', 'edit'],
+ options: switchOptions, disabled: 'inSchemaWithModelCheck'
+ // End of extension tab
+ }]
+ },{
+ id: 'alias', label:'{{ _('Alias') }}', cell: 'string',
+ type: 'text', mode: ['properties'],
+ disabled: 'inSchema'
+ },{
+ id: 'type_acl', label:'{{ _('Privileges') }}', cell: 'string',
+ type: 'text', mode: ['properties'], group: '{{ _('Security') }}',
+ disabled: 'inSchema'
+ },{
+ id: 'member_list', label:'{{ _('Members') }}', cell: 'string',
+ type: 'text', mode: ['properties'], group: '{{ _('Definition') }}',
+ disabled: 'inSchema', visible: function(m) {
+ if(m.get('typtype') === 'c') {
+ return true;
+ }
+ return false;
+ }
+ },{
+ id: 'enum_list', label:'{{ _('Labels') }}', cell: 'string',
+ type: 'text', mode: ['properties'],
+ disabled: 'inSchema', visible: function(m) {
+ if(m.get('typtype') === 'e') {
+ return true;
+ }
+ return false;
+ }
+ },{
+ id: 'is_sys_type', label:'{{ _('System type?') }}', cell: 'switch',
+ type: 'switch', mode: ['properties'], options: switchOptions,
+ disabled: 'inSchema'
+ },{
+ id: 'description', label:'{{ _('Comment') }}', cell: 'string',
+ type: 'multiline', mode: ['properties', 'create', 'edit'],
+ disabled: 'inSchema'
+ },{
+ id: 'typacl', label: 'Privileges', type: 'collection',
+ group: '{{ _('Security') }}', control: 'unique-col-collection',
+ model: pgAdmin.Browser.Node.PrivilegeRoleModel.extend({privileges: ['U']}),
+ mode: ['edit', 'create'], canAdd: true, canDelete: true,
+ uniqueCol : ['grantee'],
+ columns: ['grantee', 'grantor', 'privileges']
+ },{
+ id: 'seclabels', label: '{{ _('Security Labels') }}',
+ model: 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() {
+ // Validation code for required fields
+ var changedAttrs = this.sessAttrs,
+ msg = undefined;
+
+ if (_.has(changedAttrs, 'name') &&
+ (_.isUndefined(this.get('name'))
+ || String(this.get('name')).replace(/^\s+|\s+$/g, '') == '')) {
+ msg = '{{ _('Name can not be empty!') }}';
+ this.errorModel.set('name', msg);
+ } else if (_.has(changedAttrs, 'schema') &&
+ (_.isUndefined(this.get('schema'))
+ || String(this.get('schema')).replace(/^\s+|\s+$/g, '') == '')) {
+ msg = '{{ _('Schema can not be empty!') }}';
+ this.errorModel.set('schema', msg);
+ } else if (_.has(changedAttrs, 'typtype') &&
+ (_.isUndefined(this.get('typtype'))
+ || String(this.get('name')).replace(/^\s+|\s+$/g, '') == '')) {
+ msg = '{{ _('Type can not be empty!') }}';
+ this.errorModel.set('typtype', msg);
+ } else if (this.get('typtype') == 'r' &&
+ _.has(changedAttrs, 'typname')
+ && (_.isUndefined(this.get('typname'))
+ || String(this.get('typname')).replace(/^\s+|\s+$/g, '') == '')) {
+ msg = '{{ _('Subtype Name can not be empty!') }}';
+ this.errorModel.set('typname', msg);
+ } else if (this.get('typtype') == 'x' &&
+ _.has(changedAttrs, 'typinput')
+ && (_.isUndefined(this.get('typinput'))
+ || String(this.get('typinput')).replace(/^\s+|\s+$/g, '') == '')) {
+ msg = '{{ _('Input function can not be empty!') }}';
+ this.errorModel.set('typinput', msg);
+ } else if (this.get('typtype') == 'x' &&
+ _.has(changedAttrs, 'typoutput')
+ && (_.isUndefined(this.get('typoutput'))
+ || String(this.get('typoutput')).replace(/^\s+|\s+$/g, '') == '')) {
+ msg = '{{ _('Output function can not be empty!') }}';
+ this.errorModel.set('typoutput', msg);
+ } else {
+ this.errorModel.unset('name');
+ this.errorModel.unset('schema');
+ this.errorModel.unset('typtype');
+ this.errorModel.unset('typname');
+ this.errorModel.unset('typinput');
+ this.errorModel.unset('typoutput');
+ }
+ return null;
+ },
+ // We will disable everything if we are under catalog 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 disbale control if it's in 'edit' mode
+ if (m.isNew()) {
+ return false;
+ } else {
+ return true;
+ }
+
+ }
+ return true;
+ },
+ // We want to enable only in edit mode
+ inSchemaWithEditMode: function(m) {
+ if(this.node_info && 'schema' in this.node_info)
+ {
+ // We will disbale control if it's in 'edit' mode
+ if (m.isNew()) {
+ return true;
+ } else {
+ return false;
+ }
+
+ }
+ return true;
+ },
+ // Function will help us to fill combobox
+ external_func_combo: function(d) {
+ var result = [];
+ _.each(d, function(item) {
+ // if type from selected from combobox matches in options
+ if ( item.cbtype == 'all' ) {
+ result.push(item);
+ }
+ });
+ return result;
+ }
+ }),
+ 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 table
+ if (_.indexOf(['schema'], d._type) > -1)
+ return true;
+
+ if ('coll-type' == d._type) {
+ //Check if we are not child of catalog
+ prev_i = t.hasParent(i) ? t.parent(i) : null;
+ prev_d = prev_i ? t.itemData(prev_i) : null;
+ if( prev_d._type == 'catalog') {
+ return false;
+ } else {
+ return true;
+ }
+ }
+ i = t.hasParent(i) ? t.parent(i) : null;
+ d = i ? t.itemData(i) : null;
+ }
+ // by default we do not want to allow create menu
+ return true;
+ }
+ });
+ }
+ return pgBrowser.Nodes['type'];
+});
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/acl.sql
new file mode 100644
index 0000000..2915e3a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/acl.sql
@@ -0,0 +1,29 @@
+SELECT 'typacl' 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 'USAGE' THEN 'U'
+ ELSE 'UNKNOWN'
+ END AS privilege_type
+ FROM
+ (SELECT t.typacl
+ FROM pg_type t
+ LEFT OUTER JOIN pg_type e ON e.oid=t.typelem
+ LEFT OUTER JOIN pg_class ct ON ct.oid=t.typrelid AND ct.relkind <> 'c'
+ LEFT OUTER JOIN pg_description des ON (des.objoid=t.oid AND des.classoid='pg_type'::regclass)
+ WHERE t.typtype != 'd' AND t.typname NOT LIKE E'\\_%' AND t.typnamespace = {{scid}}::oid
+ {% if tid %}
+ AND t.oid = {{tid}}::oid
+ {% endif %}
+ ) 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(t.typacl) as d FROM pg_type t WHERE t.oid = {{tid}}::oid) a) d
+ ) d
+ LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid)
+ LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid)
+GROUP BY g.rolname, gt.rolname
+
+
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/additional_properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/additional_properties.sql
new file mode 100644
index 0000000..eebc0bb
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/additional_properties.sql
@@ -0,0 +1,40 @@
+{# The SQL given below will fetch composite type#}
+{% if type == 'c' %}
+SELECT attname, format_type(t.oid,NULL) AS typname, attndims, atttypmod, nsp.nspname,
+(SELECT COUNT(1) from pg_type t2 WHERE t2.typname=t.typname) > 1 AS isdup
+-- Min 9.1 start
+,collname, nspc.nspname as collnspname
+-- End
+, att.attrelid
+FROM pg_attribute att
+JOIN pg_type t ON t.oid=atttypid
+JOIN pg_namespace nsp ON t.typnamespace=nsp.oid
+LEFT OUTER JOIN pg_type b ON t.typelem=b.oid
+-- Min 9.1 start
+LEFT OUTER JOIN pg_collation c ON att.attcollation=c.oid
+LEFT OUTER JOIN pg_namespace nspc ON c.collnamespace=nspc.oid
+-- End
+WHERE att.attrelid = {{typrelid}}::oid
+ORDER by attnum;
+{% endif %}
+{# The SQL given below will fetch enum type#}
+{% if type == 'e' %}
+SELECT enumlabel FROM pg_enum WHERE enumtypid={{tid}}::oid
+-- Min 9.1
+ORDER by enumsortorder
+--End
+-- else
+-- ORDER by oid
+{% endif %}
+{# The SQL given below will fetch range type#}
+{% if type == 'r' %}
+SELECT rngsubtype, st.typname,
+rngcollation, col.collname,
+rngsubopc, opc.opcname,
+rngcanonical, rngsubdiff
+FROM pg_range
+LEFT JOIN pg_type st ON st.oid=rngsubtype
+LEFT JOIN pg_collation col ON col.oid=rngcollation
+LEFT JOIN pg_opclass opc ON opc.oid=rngsubopc
+WHERE rngtypid={{tid}}::oid;
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/backend_support.sql
new file mode 100644
index 0000000..f31a2c1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/backend_support.sql
@@ -0,0 +1,14 @@
+SELECT
+ CASE WHEN nsp.nspname IN ('sys', 'dbo', 'information_schema') THEN true ELSE false END AS dbSupport
+FROM pg_namespace nsp
+WHERE nsp.oid={{scid}}::int
+ AND (
+ (nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+ )
+ AND nspname NOT LIKE E'pg\\temp\\%'
+ AND nspname NOT LIKE E'pg\\toast_temp\\%'
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/create.sql
new file mode 100644
index 0000000..7f5c030
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/create.sql
@@ -0,0 +1,68 @@
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{### Composite Type ###}
+{% if data and data.typtype == 'c' %}
+CREATE TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %} AS
+ ({% if data.composite %}{% for d in data.composite %}{% if loop.index != 1 %}, {% endif %}{{ conn|qtIdent(d.member_name) }} {{ d.type }}{% if d.is_tlength and d.tlength %}({{d.tlength}}{% if d.is_precision and d.precision %},{{d.precision}}{% endif %}){% endif %}{% if d.collation %} COLLATE {{d.collation}}{% endif %}{% endfor %}{% endif %});
+{% endif %}
+{### Enum Type ###}
+{% if data and data.typtype == 'e' %}
+CREATE TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %} AS ENUM
+ ({% for e in data.enum %}{% if loop.index != 1 %}, {% endif %}{{ e.label|qtLiteral }}{% endfor %});
+{% endif %}
+{### Range Type ###}
+{% if data and data.typtype == 'r' %}
+CREATE TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %} AS RANGE
+({% if data.typname %} SUBTYPE={{ conn|qtTypeIdent(data.typname) }}{% endif %}{% if data.collname %}
+, COLLATION = {{ data.collname }}{% endif %}{% if data.opcname %}
+, SUBTYPE_OPCLASS = {{ data.opcname }}{% endif %}{% if data.rngcanonical %}
+, CANONICAL = {{ data.rngcanonical }}{% endif %}{% if data.rngsubdiff %}
+, SUBTYPE_DIFF = {{ data.rngsubdiff }}{% endif %} );
+{% endif %}
+{### External Type ###}
+{% if data and data.typtype == 'x' %}
+CREATE TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %} AS
+( {% if data.typinput %}
+INPUT = {{data.typinput}}{% endif %}{% if data.typoutput %}
+,OUTPUT = {{ data.typoutput }}{% endif %}{% if data.typreceive %}
+,RECEIVE = {{data.typreceive}}{% endif %}{% if data.typname %}
+,SEND = {{data.typsend}}{% endif %}{% if data.typmodin %}
+,TYPMOD_IN = {{data.typmodin}}{% endif %}{% if data.typmodout %}
+,TYPMOD_OUT = {{data.typmodout}}{% endif %}{% if data.typanalyze %}
+,ANALYZE = {{data.typanalyze}}{% endif %}{% if data.typlen %}
+,INTERNALLENGTH = {{data.typlen}}{% endif %}{% if data.typbyval %}
+,PASSEDBYVALUE{% endif %}{% if data.typalign %}
+,ALIGNMENT = {{data.typalign}}{% endif %}{% if data.typstorage %}
+,STORAGE = {{data.typstorage}}{% endif %}{% if data.typcategory %}
+,CATEGORY = {{data.typcategory}}{% endif %}{% if data.typispreferred %}
+,PREFERRED = {{data.typispreferred}}{% endif %}{% if data.typdefault %}
+,DEFAULT = {{data.typdefault}}{% endif %}{% if data.element %}
+,ELEMENT = {{data.element}}{% endif %}{% if data.typdelim %}
+,DELIMITER = {{data.typdelim|qtLiteral}}{% endif %}{% if data.is_collatable %}
+,COLLATABLE = {{data.is_collatable}}{% endif %} );
+{% endif %}
+{### Type Owner ###}
+{% if data and data.typeowner %}
+
+ALTER TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %}
+ OWNER TO {{data.typeowner}};
+{% endif %}
+{### Type Comments ###}
+{% if data and data.description %}
+
+COMMENT ON TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %} IS {{data.description|qtLiteral}};
+{% endif %}
+{### Security Lables ###}
+{% if data.seclabels %}
+
+{% for r in data.seclabels %}
+{{ SECLABLE.APPLY(conn, 'TYPE', data.name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+{### ACL ###}
+{% if data.typacl %}
+
+{% for priv in data.typacl %}
+{{ PRIVILEGE.APPLY(conn, 'TYPE', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/delete.sql
new file mode 100644
index 0000000..17d4da1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/delete.sql
@@ -0,0 +1 @@
+DROP TYPE {{ conn|qtIdent(name) }}{% if cascade%} CASCADE{% endif %};
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_collations.sql
new file mode 100644
index 0000000..4b0169b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/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;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_external_functions.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_external_functions.sql
new file mode 100644
index 0000000..85494db
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_external_functions.sql
@@ -0,0 +1,40 @@
+{### Input/Output/Send/Receive/Analyze function list also append into TypModeIN/TypModOUT ###}
+{% if extfunc %}
+SELECT proname, nspname,
+ CASE WHEN length(nspname) > 0 AND length(proname) > 0 THEN
+ concat(quote_ident(nspname), '.', quote_ident(proname))
+ ELSE '' END AS func
+FROM (
+ SELECT proname, nspname, max(proargtypes[0]) AS arg0, max(proargtypes[1]) AS arg1
+FROM pg_proc p
+ JOIN pg_namespace n ON n.oid=pronamespace
+GROUP BY proname, nspname
+HAVING count(proname) = 1 ) AS uniquefunc
+WHERE arg0 <> 0 AND arg1 = 0;
+{% endif %}
+{### TypmodIN list ###}
+{% if typemodin %}
+SELECT proname, nspname,
+ CASE WHEN length(nspname) > 0 AND length(proname) > 0 THEN
+ concat(quote_ident(nspname), '.', quote_ident(proname))
+ ELSE '' END AS func
+FROM pg_proc p
+ JOIN pg_namespace n ON n.oid=pronamespace
+WHERE prorettype=(SELECT oid FROM pg_type WHERE typname='int4')
+ AND proargtypes[0]=(SELECT oid FROM pg_type WHERE typname='_cstring')
+ AND proargtypes[1] IS NULL
+ORDER BY nspname, proname;
+{% endif %}
+{### TypmodOUT list ###}
+{% if typemodout %}
+SELECT proname, nspname,
+ CASE WHEN length(nspname) > 0 AND length(proname) > 0 THEN
+ concat(quote_ident(nspname), '.', quote_ident(proname))
+ ELSE '' END AS func
+FROM pg_proc p
+ JOIN pg_namespace n ON n.oid=pronamespace
+WHERE prorettype=(SELECT oid FROM pg_type WHERE typname='cstring')
+ AND proargtypes[0]=(SELECT oid FROM pg_type WHERE typname='int4')
+ AND proargtypes[1] IS NULL
+ORDER BY nspname, proname;
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_name.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_name.sql
new file mode 100644
index 0000000..d368f0f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_name.sql
@@ -0,0 +1,3 @@
+SELECT t.typname AS name
+FROM pg_type t
+WHERE t.oid = {{tid}}::oid;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_oid.sql
new file mode 100644
index 0000000..14f7950
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_oid.sql
@@ -0,0 +1,11 @@
+{# Below will provide oid for newly created type #}
+SELECT t.oid
+FROM pg_type t
+ LEFT OUTER JOIN pg_type e ON e.oid=t.typelem
+ LEFT OUTER JOIN pg_class ct ON ct.oid=t.typrelid AND ct.relkind <> 'c'
+ LEFT OUTER JOIN pg_description des ON (des.objoid=t.oid AND des.classoid='pg_type'::regclass)
+WHERE t.typtype != 'd' AND t.typname NOT LIKE E'\\_%' AND t.typnamespace = {{scid}}::oid
+{% if data %}
+ AND t.typname = {{data.name|qtLiteral}}
+{% endif %}
+ORDER BY t.typname;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_subtypes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_subtypes.sql
new file mode 100644
index 0000000..75271fe
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_subtypes.sql
@@ -0,0 +1,56 @@
+{### To fill subtype combobox ###}
+{% if subtype %}
+SELECT DISTINCT typ.typname AS stype,
+ (CASE WHEN typ.typcollation > 0 THEN true ELSE false END) AS is_collate
+FROM pg_opclass opc
+ JOIN pg_type typ ON opc.opcintype = typ.oid
+WHERE opc.opcmethod = 403
+ORDER BY 1
+{% endif %}
+{### To fill subtype opclass combobox ###}
+{% if subtype_opclass and data and data.typname %}
+SELECT opc.opcname
+FROM pg_opclass opc
+ JOIN pg_type typ ON opc.opcintype=typ.oid
+ AND typ.typname = {{ data.typname|qtLiteral }}
+WHERE opc.opcmethod = 403
+ORDER BY opcname;
+{% endif %}
+{### To fetch opcinttype from subtype opclass ###}
+{% if get_opcintype and data and data.typname and data.opcname %}
+SELECT opc.opcintype
+FROM pg_opclass opc
+ JOIN pg_type typ ON opc.opcintype=typ.oid
+ AND typ.typname = {{ data.typname|qtLiteral }}
+WHERE opc.opcmethod = 403
+ AND opc.opcname = {{ data.opcname|qtLiteral }}
+ORDER BY opcname;
+{% endif %}
+{### To fill subtype diff function combobox ###}
+{% if opcintype %}
+SELECT proname, nspname,
+ CASE WHEN length(nspname) > 0 AND length(proname) > 0 THEN
+ concat(quote_ident(nspname), '.', quote_ident(proname))
+ ELSE '' END AS stypdiff
+FROM pg_proc
+ JOIN pg_namespace n ON n.oid=pronamespace
+WHERE prorettype = 701
+ AND proargtypes = '{{opcintype}} {{opcintype}}'
+ORDER BY proname;
+{% endif %}
+{### To fill canonical combobox ###}
+{% if getoid %}
+SELECT oid FROM pg_type
+WHERE typname = {{ data.name|qtLiteral }}
+{% endif %}
+{% if canonical and oid %}
+SELECT proname, nspname,
+ CASE WHEN length(nspname) > 0 AND length(proname) > 0 THEN
+ concat(quote_ident(nspname), '.', quote_ident(proname))
+ ELSE '' END AS canonical
+FROM pg_proc
+ JOIN pg_namespace n ON n.oid=pronamespace
+WHERE prorettype= {{ oid }}
+ AND proargtypes = '{{ oid }}'
+ORDER BY proname;
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_types.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_types.sql
new file mode 100644
index 0000000..a5d352d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/get_types.sql
@@ -0,0 +1,10 @@
+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/types/templates/type/sql/9.1_plus/nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/nodes.sql
new file mode 100644
index 0000000..ddca1b5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/nodes.sql
@@ -0,0 +1,7 @@
+SELECT t.oid, t.typname AS name
+FROM pg_type t
+ LEFT OUTER JOIN pg_type e ON e.oid=t.typelem
+ LEFT OUTER JOIN pg_class ct ON ct.oid=t.typrelid AND ct.relkind <> 'c'
+ LEFT OUTER JOIN pg_namespace nsp ON nsp.oid = t.typnamespace
+WHERE t.typtype != 'd' AND t.typname NOT LIKE E'\\_%' AND t.typnamespace = {{scid}}::oid
+ORDER BY t.typname;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/properties.sql
new file mode 100644
index 0000000..6e81a25
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/properties.sql
@@ -0,0 +1,21 @@
+SELECT t.oid, t.typname AS name,
+ (CASE WHEN CAST(coalesce(t.typcollation, '0') AS integer) = 100 THEN true ElSE false END) AS is_collatable,
+ t.typacl AS type_acl,
+ t.*, format_type(t.oid, null) AS alias,
+ pg_get_userbyid(t.typowner) as typeowner, e.typname as element,
+ description, ct.oid AS taboid,
+ nsp.nspname AS schema,
+ --MinimumVersion 9.1 START
+ (SELECT array_agg(provider || '=' || label) FROM pg_shseclabel sl1 WHERE sl1.objoid=t.oid) AS seclabels,
+ -- END
+ (CASE WHEN (t.oid <= {{ datlastsysoid}}::oid OR ct.oid != 0) THEN true ElSE false END) AS is_sys_type
+FROM pg_type t
+ LEFT OUTER JOIN pg_type e ON e.oid=t.typelem
+ LEFT OUTER JOIN pg_class ct ON ct.oid=t.typrelid AND ct.relkind <> 'c'
+ LEFT OUTER JOIN pg_description des ON (des.objoid=t.oid AND des.classoid='pg_type'::regclass)
+ LEFT OUTER JOIN pg_namespace nsp ON nsp.oid = t.typnamespace
+WHERE t.typtype != 'd' AND t.typname NOT LIKE E'\\_%' AND t.typnamespace = {{scid}}::oid
+{% if tid %}
+ AND t.oid = {{tid}}::oid
+{% endif %}
+ORDER BY t.typname;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/update.sql
new file mode 100644
index 0000000..16b72bc
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/9.1_plus/update.sql
@@ -0,0 +1,129 @@
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{% if data %}
+{#======================================#}
+{# Below will change object owner #}
+{% if data.typeowner and data.typeowner != o_data.typeowner %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ OWNER TO {{ data.typeowner }};
+
+{% endif %}
+{#======================================#}
+{# Below will change objects comment #}
+{% if data.description and data.description != o_data.description %}
+COMMENT ON TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ IS {{ data.description|qtLiteral }};
+
+{% endif %}
+{#======================================#}
+{### The sql given below will update composite type ###}
+{% if data.composite and data.composite|length > 0 %}
+{% set composite = data.composite %}
+{% if 'deleted' in composite and composite.deleted|length > 0 %}
+{% for r in composite.deleted %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ DROP ATTRIBUTE {{conn|qtIdent(r.member_name)}};
+{% endfor %}
+{% endif %}
+{% if 'added' in composite and composite.added|length > 0 %}
+{% for r in composite.added %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ ADD ATTRIBUTE {{conn|qtIdent(r.member_name)}} {{conn|qtIdent(r.type)}}{% if r.is_tlength and r.tlength %}
+({{r.tlength}}{% if r.is_precision and r.precision %},{{r.precision}}{% endif %}){% endif %}{% if r.collation %}
+ COLLATE {{r.collation}}{% endif %};
+{% endfor %}
+{% endif %}
+{% if 'changed' in composite and composite.changed|length > 0 %}
+{% for r in composite.changed %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ ALTER ATTRIBUTE {{conn|qtIdent(r.member_name)}} SET DATA TYPE {{conn|qtIdent(r.type)}}{% if r.is_tlength and r.tlength %}
+({{r.tlength}}{% if r.is_precision and r.precision %},{{r.precision}}{% endif %}){% endif %}{% if r.collation %}
+ COLLATE {{r.collation}}{% endif %};
+{% endfor %}
+{% endif %}
+
+{% endif %}
+{#======================================#}
+{### The sql given below will update enum type ###}
+{% if data.enum and data.enum|length > 0 %}
+{% set enum = data.enum %}
+{% set o_enum_len = o_data.enum|length %}
+{# We need actual list index from length #}
+{% set o_enum_len = o_enum_len - 1 %}
+{% if 'added' in enum and enum.added|length > 0 %}
+{% for r in enum.added %}
+{% set c_idx = loop.index %}
+{% if c_idx == 1 %}
+{# if first new element then add it after old data enum list#}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ ADD VALUE {{r.label|qtLiteral}} AFTER {{o_data.enum[o_enum_len].label|qtLiteral }};
+{% else %}
+{# if first new element then add it after new data enum list#}
+{% set p_idx = loop.index - 2 %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ ADD VALUE {{r.label|qtLiteral}} AFTER {{enum.added[p_idx].label|qtLiteral}};
+{% endif %}
+{% endfor %}
+{% endif %}
+
+{% endif %}
+{#======================================#}
+{# The SQL generated below will change Security Label #}
+{% if data.seclabels and data.seclabels|length > 0 %}
+{% set seclabels = data.seclabels %}
+{% set name = conn|qtIdent(o_data.schema, o_data.name) %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+{{ SECLABLE.DROP(conn, 'TYPE', name, r.provider) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+{{ SECLABLE.APPLY(conn, 'TYPE', 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, 'TYPE', name, r.provider, r.security_label) }}
+{% endfor %}
+{% endif %}
+
+{% endif %}
+{#======================================#}
+{# Change the privileges #}
+{% if data.typacl and data.typacl|length > 0 %}
+{% set name = conn|qtIdent(o_data.schema, o_data.name) %}
+{% if 'deleted' in data.typacl %}
+{% for priv in data.typacl.deleted %}
+{{ PRIVILEGE.RESETALL(conn, 'TYPE', priv.grantee, name) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.typacl %}
+{% for priv in data.typacl.changed %}
+{{ PRIVILEGE.RESETALL(conn, 'TYPE', priv.grantee, data.name) }}
+{{ PRIVILEGE.APPLY(conn, 'TYPE', priv.grantee, name, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.typacl %}
+{% for priv in data.typacl.added %}
+{{ PRIVILEGE.APPLY(conn, 'TYPE', priv.grantee, name, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{#======================================#}
+{# Below will change object name #}
+{% if data.name and data.name != o_data.name %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ RENAME TO {{ conn|qtIdent(data.name) }};
+
+{% endif %}
+{#======================================#}
+{# Below will change the schema for object #}
+{# with extra if condition we will also make sure that object has correct name #}
+{% if data.schema and data.schema != o_data.schema %}
+ALTER TYPE {% if data.name != o_data.name %}{{ conn|qtIdent(o_data.schema, data.name) }}
+{% else %}{{ conn|qtIdent(o_data.schema, o_data.name) }}{% endif %}
+ SET SCHEMA {{ conn|qtIdent(data.schema) }};
+
+{% endif %}
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/postgres_inbuit_types.txt b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/postgres_inbuit_types.txt
new file mode 100644
index 0000000..631037f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/postgres_inbuit_types.txt
@@ -0,0 +1,53 @@
+"""
+Here is the list of Postgres inbuilt types & their OID's
+We will use these types to check for validations
+
+## PGOID_TYPE_SERIAL -42L
+## PGOID_TYPE_SERIAL8 -43L
+## PGOID_TYPE_SERIAL2 -44L
+## PGOID_TYPE_BOOL 16L
+## PGOID_TYPE_BYTEA 17L
+## PGOID_TYPE_CHAR 18L
+## PGOID_TYPE_NAME 19L
+## PGOID_TYPE_INT8 20L
+## PGOID_TYPE_INT2 21L
+## PGOID_TYPE_INT4 23L
+## PGOID_TYPE_TEXT 25L
+## PGOID_TYPE_OID 26L
+## PGOID_TYPE_TID 27L
+## PGOID_TYPE_XID 28L
+## PGOID_TYPE_CID 29L
+## PGOID_TYPE_FLOAT4 700L
+## PGOID_TYPE_FLOAT8 701L
+## PGOID_TYPE_MONEY 790L
+## PGOID_TYPE_CHAR_ARRAY 1002L
+## PGOID_TYPE_TEXT_ARRAY 1009L
+## PGOID_TYPE_BPCHAR_ARRAY 1014L
+## PGOID_TYPE_VARCHAR_ARRAY 1015L
+## PGOID_TYPE_BPCHAR 1042L
+## PGOID_TYPE_VARCHAR 1043L
+## PGOID_TYPE_DATE 1082L
+## PGOID_TYPE_TIME 1083L
+## PGOID_TYPE_TIMESTAMP 1114L
+## PGOID_TYPE_TIMESTAMP_ARRAY 1115L
+## PGOID_TYPE_TIME_ARRAY 1183L
+## PGOID_TYPE_TIMESTAMPTZ 1184L
+## PGOID_TYPE_TIMESTAMPTZ_ARRAY 1185L
+## PGOID_TYPE_INTERVAL 1186L
+## PGOID_TYPE_INTERVAL_ARRAY 1187L
+## PGOID_TYPE_NUMERIC_ARRAY 1231L
+## PGOID_TYPE_TIMETZ 1266L
+## PGOID_TYPE_TIMETZ_ARRAY 1270L
+## PGOID_TYPE_BIT 1560L
+## PGOID_TYPE_BIT_ARRAY 1561L
+## PGOID_TYPE_VARBIT 1562L
+## PGOID_TYPE_VARBIT_ARRAY 1563L
+## PGOID_TYPE_NUMERIC 1700L
+## PGOID_TYPE_CSTRING 2275L
+## PGOID_TYPE_ANY 2276L
+## PGOID_TYPE_VOID 2278L
+## PGOID_TYPE_TRIGGER 2279L
+## PGOID_TYPE_LANGUAGE_HANDLER 2280L
+## PGOID_TYPE_INTERNAL 2281L
+## PGOID_TYPE_HANDLER 3115L
+"""
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/acl.sql
new file mode 100644
index 0000000..9b34240
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/acl.sql
@@ -0,0 +1,29 @@
+SELECT 'typacl' 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 'USAGE' THEN 'U'
+ ELSE 'UNKNOWN'
+ END AS privilege_type
+ FROM
+ (SELECT t.typacl
+ FROM pg_type t
+ LEFT OUTER JOIN pg_type e ON e.oid=t.typelem
+ LEFT OUTER JOIN pg_class ct ON ct.oid=t.typrelid AND ct.relkind <> 'c'
+ LEFT OUTER JOIN pg_description des ON (des.objoid=t.oid AND des.classoid='pg_type'::regclass)
+ WHERE t.typtype != 'd' AND t.typname NOT LIKE E'\\_%' AND t.typnamespace = {{scid}}::oid
+{% if tid %}
+ AND t.oid = {{tid}}::oid
+{% endif %}
+ ) 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(t.typacl) as d FROM pg_type t WHERE t.oid = {{tid}}::oid) a) d
+ ) d
+ LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid)
+ LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid)
+GROUP BY g.rolname, gt.rolname
+
+
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/additional_properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/additional_properties.sql
new file mode 100644
index 0000000..0406afd
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/additional_properties.sql
@@ -0,0 +1,40 @@
+{# The SQL given below will fetch composite type#}
+{% if type == 'c' %}
+SELECT attname, format_type(t.oid,NULL) AS typname, attndims, atttypmod, nsp.nspname,
+ (SELECT COUNT(1) from pg_type t2 WHERE t2.typname=t.typname) > 1 AS isdup
+-- Min 9.1 start
+ ,collname, nspc.nspname as collnspname
+-- End
+ , att.attrelid
+FROM pg_attribute att
+ JOIN pg_type t ON t.oid=atttypid
+ JOIN pg_namespace nsp ON t.typnamespace=nsp.oid
+ LEFT OUTER JOIN pg_type b ON t.typelem=b.oid
+-- Min 9.1 start
+ LEFT OUTER JOIN pg_collation c ON att.attcollation=c.oid
+ LEFT OUTER JOIN pg_namespace nspc ON c.collnamespace=nspc.oid
+-- End
+WHERE att.attrelid = {{typrelid}}::oid
+ORDER by attnum;
+{% endif %}
+{# The SQL given below will fetch enum type#}
+{% if type == 'e' %}
+SELECT enumlabel FROM pg_enum WHERE enumtypid={{tid}}::oid
+-- Min 9.1
+ORDER by enumsortorder
+--End
+-- else
+-- ORDER by oid
+{% endif %}
+{# The SQL given below will fetch range type#}
+{% if type == 'r' %}
+SELECT rngsubtype, st.typname,
+ rngcollation, col.collname,
+ rngsubopc, opc.opcname,
+ rngcanonical, rngsubdiff
+FROM pg_range
+ LEFT JOIN pg_type st ON st.oid=rngsubtype
+ LEFT JOIN pg_collation col ON col.oid=rngcollation
+ LEFT JOIN pg_opclass opc ON opc.oid=rngsubopc
+WHERE rngtypid={{tid}}::oid;
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/backend_support.sql
new file mode 100644
index 0000000..f31a2c1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/backend_support.sql
@@ -0,0 +1,14 @@
+SELECT
+ CASE WHEN nsp.nspname IN ('sys', 'dbo', 'information_schema') THEN true ELSE false END AS dbSupport
+FROM pg_namespace nsp
+WHERE nsp.oid={{scid}}::int
+ AND (
+ (nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+ )
+ AND nspname NOT LIKE E'pg\\temp\\%'
+ AND nspname NOT LIKE E'pg\\toast_temp\\%'
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/create.sql
new file mode 100644
index 0000000..27116a8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/create.sql
@@ -0,0 +1,60 @@
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{### Composite Type ###}
+{% if data and data.typtype == 'c' %}
+CREATE TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %} AS
+ ({% if data.composite %}{% for d in data.composite %}{% if loop.index != 1 %}, {% endif %}{{ conn|qtIdent(d.member_name) }} {{ d.type }}{% if d.is_tlength and d.tlength %}({{d.tlength}}{% if d.is_precision and d.precision %},{{d.precision}}{% endif %}){% endif %}{% if d.collation %} COLLATE {{d.collation}}{% endif %}{% endfor %}{% endif %});
+{% endif %}
+{### Enum Type ###}
+{% if data and data.typtype == 'e' %}
+CREATE TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %} AS ENUM
+ ({% for e in data.enum %}{% if loop.index != 1 %}, {% endif %}{{ e.label|qtLiteral }}{% endfor %});
+{% endif %}
+{### Range Type ###}
+{% if data and data.typtype == 'r' %}
+CREATE TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %} AS RANGE
+({% if data.typname %} SUBTYPE={{ conn|qtTypeIdent(data.typname) }}{% endif %}{% if data.collname %}
+, COLLATION = {{ data.collname }}{% endif %}{% if data.opcname %}
+, SUBTYPE_OPCLASS = {{ data.opcname }}{% endif %}{% if data.rngcanonical %}
+, CANONICAL = {{ data.rngcanonical }}{% endif %}{% if data.rngsubdiff %}
+, SUBTYPE_DIFF = {{ data.rngsubdiff }}{% endif %} );
+{% endif %}
+{### External Type ###}
+{% if data and data.typtype == 'x' %}
+CREATE TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %} AS
+( {% if data.typinput %}
+INPUT = {{data.typinput}}{% endif %}{% if data.typoutput %}
+,OUTPUT = {{ data.typoutput }}{% endif %}{% if data.typreceive %}
+,RECEIVE = {{data.typreceive}}{% endif %}{% if data.typname %}
+,SEND = {{data.typsend}}{% endif %}{% if data.typmodin %}
+,TYPMOD_IN = {{data.typmodin}}{% endif %}{% if data.typmodout %}
+,TYPMOD_OUT = {{data.typmodout}}{% endif %}{% if data.typanalyze %}
+,ANALYZE = {{data.typanalyze}}{% endif %}{% if data.typlen %}
+,INTERNALLENGTH = {{data.typlen}}{% endif %}{% if data.typbyval %}
+,PASSEDBYVALUE{% endif %}{% if data.typalign %}
+,ALIGNMENT = {{data.typalign}}{% endif %}{% if data.typstorage %}
+,STORAGE = {{data.typstorage}}{% endif %}{% if data.typcategory %}
+,CATEGORY = {{data.typcategory}}{% endif %}{% if data.typispreferred %}
+,PREFERRED = {{data.typispreferred}}{% endif %}{% if data.typdefault %}
+,DEFAULT = {{data.typdefault}}{% endif %}{% if data.element %}
+,ELEMENT = {{data.element}}{% endif %}{% if data.typdelim %}
+,DELIMITER = {{data.typdelim|qtLiteral}}{% endif %}{% if data.is_collatable %}
+,COLLATABLE = {{data.is_collatable}}{% endif %} );
+{% endif %}
+{### Type Owner ###}
+{% if data and data.typeowner %}
+
+ALTER TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %}
+ OWNER TO {{data.typeowner}};
+{% endif %}
+{### Type Comments ###}
+{% if data and data.description %}
+
+COMMENT ON TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %} IS {{data.description|qtLiteral}};
+{% endif %}
+{### ACL ###}
+{% if data.typacl %}
+
+{% for priv in data.typacl %}
+{{ PRIVILEGE.APPLY(conn, 'TYPE', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/delete.sql
new file mode 100644
index 0000000..06a279e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/delete.sql
@@ -0,0 +1,8 @@
+{% if scid and tid %}
+SELECT t.typname AS name
+FROM pg_type t
+WHERE t.oid = {{tid}}::oid
+{% endif %}
+{% if name %}
+DROP TYPE {{ conn|qtIdent(name) }}{% if cascade%} CASCADE{% endif %};
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_collations.sql
new file mode 100644
index 0000000..4b0169b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/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;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_external_functions.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_external_functions.sql
new file mode 100644
index 0000000..53c3c85
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_external_functions.sql
@@ -0,0 +1,40 @@
+{### Input/Output/Send/Receive/Analyze function list also append into TypModeIN/TypModOUT ###}
+{% if extfunc %}
+SELECT proname, nspname,
+ CASE WHEN length(nspname) > 0 AND length(proname) > 0 THEN
+ concat(quote_ident(nspname), '.', quote_ident(proname))
+ ELSE '' END AS func
+FROM (
+ SELECT proname, nspname, max(proargtypes[0]) AS arg0, max(proargtypes[1]) AS arg1
+FROM pg_proc p
+ JOIN pg_namespace n ON n.oid=pronamespace
+GROUP BY proname, nspname
+ HAVING count(proname) = 1 ) AS uniquefunc
+ WHERE arg0 <> 0 AND arg1 = 0;
+{% endif %}
+{### TypmodIN list ###}
+{% if typemodin %}
+SELECT proname, nspname,
+ CASE WHEN length(nspname) > 0 AND length(proname) > 0 THEN
+ concat(quote_ident(nspname), '.', quote_ident(proname))
+ ELSE '' END AS func
+FROM pg_proc p
+ JOIN pg_namespace n ON n.oid=pronamespace
+WHERE prorettype=(SELECT oid FROM pg_type WHERE typname='int4')
+ AND proargtypes[0]=(SELECT oid FROM pg_type WHERE typname='_cstring')
+ AND proargtypes[1] IS NULL
+ORDER BY nspname, proname;
+{% endif %}
+{### TypmodOUT list ###}
+{% if typemodout %}
+SELECT proname, nspname,
+ CASE WHEN length(nspname) > 0 AND length(proname) > 0 THEN
+ concat(quote_ident(nspname), '.', quote_ident(proname))
+ ELSE '' END AS func
+FROM pg_proc p
+ JOIN pg_namespace n ON n.oid=pronamespace
+WHERE prorettype=(SELECT oid FROM pg_type WHERE typname='cstring')
+ AND proargtypes[0]=(SELECT oid FROM pg_type WHERE typname='int4')
+ AND proargtypes[1] IS NULL
+ORDER BY nspname, proname;
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_name.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_name.sql
new file mode 100644
index 0000000..d368f0f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_name.sql
@@ -0,0 +1,3 @@
+SELECT t.typname AS name
+FROM pg_type t
+WHERE t.oid = {{tid}}::oid;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_oid.sql
new file mode 100644
index 0000000..3219b2a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_oid.sql
@@ -0,0 +1,11 @@
+{# Below will provide oid for newly created type #}
+SELECT t.oid
+FROM pg_type t
+ LEFT OUTER JOIN pg_type e ON e.oid=t.typelem
+ LEFT OUTER JOIN pg_class ct ON ct.oid=t.typrelid AND ct.relkind <> 'c'
+ LEFT OUTER JOIN pg_description des ON (des.objoid=t.oid AND des.classoid='pg_type'::regclass)
+WHERE t.typtype != 'd' AND t.typname NOT LIKE E'\\_%' AND t.typnamespace = {{scid}}::oid
+{% if data %}
+ AND t.typname = {{data.name|qtLiteral}}
+{% endif %}
+ORDER BY t.typname;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_schemas.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_schemas.sql
new file mode 100644
index 0000000..a38db60
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_schemas.sql
@@ -0,0 +1,25 @@
+SELECT
+ nsp.nspname
+FROM
+ pg_namespace nsp
+ LEFT OUTER JOIN pg_description des ON (des.objoid=nsp.oid AND des.classoid='pg_namespace'::regclass)
+ LEFT OUTER JOIN pg_catalog.pg_default_acl dacl ON (dacl.defaclnamespace = nsp.oid)
+WHERE
+ NOT ((nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1)) OR
+ (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1)) OR
+ (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1)) OR
+ (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1)) OR
+ (nspname = 'dbo' OR nspname = 'sys')
+ ) AND
+ nspname NOT LIKE E'pg\\temp\\%' AND
+ nspname NOT LIKE E'pg\\toast_temp\\%' AND
+ -- ADDED: Because We need to omit system schema except the one on which we are trying to create collation
+ ( nsp.oid = {{ scid }} OR nspname NOT LIKE E'pg\\_%' ) AND
+ -- Specific code for EDB PPAS
+ nsp.nspparent = 0 AND NOT
+ (nspname = 'dbms_job_procedure' AND EXISTS(SELECT 1 FROM pg_proc WHERE pronamespace = nsp.oid and proname = 'run_job' LIMIT 1))
+ORDER BY nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_subtypes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_subtypes.sql
new file mode 100644
index 0000000..5161a99
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_subtypes.sql
@@ -0,0 +1,56 @@
+{### To fill subtype combobox ###}
+{% if subtype %}
+SELECT DISTINCT typ.typname AS stype,
+ (CASE WHEN typ.typcollation > 0 THEN true ELSE false END) AS is_collate
+FROM pg_opclass opc
+ JOIN pg_type typ ON opc.opcintype = typ.oid
+WHERE opc.opcmethod = 403
+ORDER BY 1
+{% endif %}
+{### To fill subtype opclass combobox ###}
+{% if subtype_opclass and data and data.typname %}
+SELECT opc.opcname
+FROM pg_opclass opc
+ JOIN pg_type typ ON opc.opcintype=typ.oid
+ AND typ.typname = {{ data.typname|qtLiteral }}
+WHERE opc.opcmethod = 403
+ORDER BY opcname;
+{% endif %}
+{### To fetch opcinttype from subtype opclass ###}
+{% if get_opcintype and data and data.typname and data.opcname %}
+SELECT opc.opcintype
+FROM pg_opclass opc
+ JOIN pg_type typ ON opc.opcintype=typ.oid
+ AND typ.typname = {{ data.typname|qtLiteral }}
+WHERE opc.opcmethod = 403
+ AND opc.opcname = {{ data.opcname|qtLiteral }}
+ORDER BY opcname;
+{% endif %}
+{### To fill subtype diff function combobox ###}
+{% if opcintype %}
+SELECT proname, nspname,
+ CASE WHEN length(nspname) > 0 AND length(proname) > 0 THEN
+ concat(quote_ident(nspname), '.', quote_ident(proname))
+ ELSE '' END AS stypdiff
+FROM pg_proc
+ JOIN pg_namespace n ON n.oid=pronamespace
+WHERE prorettype = 701
+AND proargtypes = '{{opcintype}} {{opcintype}}'
+ORDER BY proname;
+{% endif %}
+{### To fill canonical combobox ###}
+{% if getoid %}
+SELECT oid FROM pg_type
+WHERE typname = {{ data.name|qtLiteral }}
+{% endif %}
+{% if canonical and oid %}
+SELECT proname, nspname,
+ CASE WHEN length(nspname) > 0 AND length(proname) > 0 THEN
+ concat(quote_ident(nspname), '.', quote_ident(proname))
+ ELSE '' END AS canonical
+FROM pg_proc
+ JOIN pg_namespace n ON n.oid=pronamespace
+WHERE prorettype= {{ oid }}
+AND proargtypes = '{{ oid }}'
+ORDER BY proname;
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_types.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_types.sql
new file mode 100644
index 0000000..b430e8a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/get_types.sql
@@ -0,0 +1,10 @@
+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/types/templates/type/sql/pre_9.1/nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/nodes.sql
new file mode 100644
index 0000000..ddca1b5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/nodes.sql
@@ -0,0 +1,7 @@
+SELECT t.oid, t.typname AS name
+FROM pg_type t
+ LEFT OUTER JOIN pg_type e ON e.oid=t.typelem
+ LEFT OUTER JOIN pg_class ct ON ct.oid=t.typrelid AND ct.relkind <> 'c'
+ LEFT OUTER JOIN pg_namespace nsp ON nsp.oid = t.typnamespace
+WHERE t.typtype != 'd' AND t.typname NOT LIKE E'\\_%' AND t.typnamespace = {{scid}}::oid
+ORDER BY t.typname;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/properties.sql
new file mode 100644
index 0000000..385b906
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/properties.sql
@@ -0,0 +1,16 @@
+SELECT t.oid, t.typname AS name,
+ (CASE WHEN CAST(coalesce(t.typcollation, '0') AS integer) = 100 THEN true ElSE false END) AS is_collatable,
+ t.typacl AS type_acl,
+ t.*, format_type(t.oid, null) AS alias,
+ pg_get_userbyid(t.typowner) as typeowner, e.typname as element,
+ description, ct.oid AS taboid,
+ (CASE WHEN (t.oid <= {{ datlastsysoid}}::oid OR ct.oid != 0) THEN true ElSE false END) AS is_sys_type
+FROM pg_type t
+ LEFT OUTER JOIN pg_type e ON e.oid=t.typelem
+ LEFT OUTER JOIN pg_class ct ON ct.oid=t.typrelid AND ct.relkind <> 'c'
+ LEFT OUTER JOIN pg_description des ON (des.objoid=t.oid AND des.classoid='pg_type'::regclass)
+WHERE t.typtype != 'd' AND t.typname NOT LIKE E'\\_%' AND t.typnamespace = {{scid}}::oid
+{% if tid %}
+ AND t.oid = {{tid}}::oid
+{% endif %}
+ORDER BY t.typname;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/update.sql
new file mode 100644
index 0000000..3f2663a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/pre_9.1/update.sql
@@ -0,0 +1,107 @@
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{% if data %}
+{#======================================#}
+{# Below will change object owner #}
+{% if data.typeowner and data.typeowner != o_data.typeowner %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ OWNER TO {{ data.typeowner }};
+
+{% endif %}
+{#======================================#}
+{# Below will change objects comment #}
+{% if data.description and data.description != o_data.description %}
+COMMENT ON TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ IS {{ data.description|qtLiteral }};
+
+{% endif %}
+{#======================================#}
+{### The sql given below will update composite type ###}
+{% if data.composite and data.composite|length > 0 %}
+{% set composite = data.composite %}
+{% if 'deleted' in composite and composite.deleted|length > 0 %}
+{% for r in composite.deleted %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ DROP ATTRIBUTE {{conn|qtIdent(r.member_name)}};
+{% endfor %}
+{% endif %}
+{% if 'added' in composite and composite.added|length > 0 %}
+{% for r in composite.added %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ ADD ATTRIBUTE {{conn|qtIdent(r.member_name)}} {{conn|qtIdent(r.type)}}{% if r.is_tlength and r.tlength %}
+({{r.tlength}}{% if r.is_precision and r.precision %},{{r.precision}}{% endif %}){% endif %}{% if r.collation %}
+ COLLATE {{r.collation}}{% endif %};
+{% endfor %}
+{% endif %}
+{% if 'changed' in composite and composite.changed|length > 0 %}
+{% for r in composite.changed %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ ALTER ATTRIBUTE {{conn|qtIdent(r.member_name)}} SET DATA TYPE {{conn|qtIdent(r.type)}}{% if r.is_tlength and r.tlength %}
+({{r.tlength}}{% if r.is_precision and r.precision %},{{r.precision}}{% endif %}){% endif %}{% if r.collation %}
+ COLLATE {{r.collation}}{% endif %};
+{% endfor %}
+{% endif %}
+
+{% endif %}
+{#======================================#}
+{### The sql given below will update enum type ###}
+{% if data.enum and data.enum|length > 0 %}
+{% set enum = data.enum %}
+{% set o_enum_len = o_data.enum|length %}
+{# We need actual list index from length #}
+{% set o_enum_len = o_enum_len - 1 %}
+{% if 'added' in enum and enum.added|length > 0 %}
+{% for r in enum.added %}
+{% set c_idx = loop.index %}
+{% if c_idx == 1 %}
+{# if first new element then add it after old data enum list#}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ ADD VALUE {{r.label|qtLiteral}} AFTER {{o_data.enum[o_enum_len].label|qtLiteral }};
+{% else %}
+{# if first new element then add it after new data enum list#}
+{% set p_idx = loop.index - 2 %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ ADD VALUE {{r.label|qtLiteral}} AFTER {{enum.added[p_idx].label|qtLiteral}};
+{% endif %}
+{% endfor %}
+{% endif %}
+
+{% endif %}
+{#======================================#}
+{# Change the privileges #}
+{% if data.typacl %}
+{% if 'deleted' in data.typacl %}
+{% for priv in data.typacl.deleted %}
+{{ PRIVILEGE.RESETALL(conn, 'TYPE', priv.grantee, o_data.name) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.typacl %}
+{% for priv in data.typacl.changed %}
+{{ PRIVILEGE.RESETALL(conn, 'TYPE', priv.grantee, data.name) }}
+{{ PRIVILEGE.APPLY(conn, 'TYPE', priv.grantee, o_data.name, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.typacl %}
+{% for priv in data.typacl.added %}
+{{ PRIVILEGE.APPLY(conn, 'TYPE', priv.grantee, o_data.name, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+
+{% endif %}
+{#======================================#}
+{# Below will change object name #}
+{% if data.name and data.name != o_data.name %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ RENAME TO {{ conn|qtIdent(data.name) }};
+
+{% endif %}
+{#======================================#}
+{# Below will change the schema for object #}
+{# with extra if condition we will also make sure that object has correct name #}
+{% if data.schema and data.schema != o_data.schema %}
+ALTER TYPE {% if data.name != o_data.name %}{{ conn|qtIdent(o_data.schema, data.name) }}
+{% else %}{{ conn|qtIdent(o_data.schema, o_data.name) }}{% endif %}
+ SET SCHEMA {{ conn|qtIdent(data.schema) }};
+
+{% endif %}
+{% endif %}
\ No newline at end of file
view thread (26+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: PATCH: Added Node Type & Catalog objects [pgAdmin4]
In-Reply-To: <CA+OCxozEyaxygwg4KKNWKjKcLw=a-s6R64Tij9Z2JSVzzEKLrg@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