public inbox for [email protected]
help / color / mirror / Atom feedFrom: Murtuza Zabuawala <[email protected]>
To: [email protected]
Subject: PATCH: added "Collation" & "Catalog Objects" nodes in pgAdmin4
Date: Wed, 6 Jan 2016 17:48:01 +0530
Message-ID: <[email protected]> (raw)
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
Hi,
Please find patch to add "Collation" & "Catalog Objects" nodes in pgAdmin4.
Please note that this patch is mainly for "Collation" & "Catalog
Objects" nodes, Schema/Catalog node is not yet complete as we are yet to
implement privileges control for the same.
Thanks,
Murtuza Zabuawala
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
Attachments:
[text/x-patch] collation_node.patch (187.7K, 2-collation_node.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/__init__.py
new file mode 100644
index 0000000..6cd35be
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/__init__.py
@@ -0,0 +1,634 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2015, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+import json
+from flask import render_template, make_response, request, jsonify
+from flask.ext.babel import gettext
+from pgadmin.utils.ajax import make_json_response, \
+ make_response as ajax_response, internal_server_error
+from pgadmin.browser.utils import PGChildNodeView
+from pgadmin.browser.collection import CollectionNodeModule
+import pgadmin.browser.server_groups.servers as servers
+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 SchemaModule(CollectionNodeModule):
+ NODE_TYPE = 'schema'
+ COLLECTION_LABEL = gettext("Schemas")
+
+ def __init__(self, *args, **kwargs):
+ self.min_ver = None
+ self.max_ver = None
+
+ super(SchemaModule, self).__init__(*args, **kwargs)
+
+ def get_nodes(self, gid, sid, did):
+ """
+ Generate the collection node
+ """
+ yield self.generate_browser_collection_node(did)
+
+ @property
+ def script_load(self):
+ """
+ Load the module script for server, when any of the server-group node is
+ initialized.
+ """
+ return servers.ServerModule.NODE_TYPE
+
+ @property
+ def csssnippets(self):
+ """
+ Returns a snippet of css to include in the page
+ """
+ snippets = [
+ render_template(
+ "browser/css/collection.css",
+ node_type=self.node_type,
+ _=gettext
+ ),
+ render_template(
+ "schemas/css/schema.css",
+ node_type=self.node_type,
+ _=gettext
+ )
+ ]
+
+ for submodule in self.submodules:
+ snippets.extend(submodule.csssnippets)
+
+ return snippets
+
+
+blueprint = SchemaModule(__name__)
+
+
+class SchemaView(PGChildNodeView):
+ node_type = blueprint.node_type
+
+ parent_ids = [
+ {'type': 'int', 'id': 'gid'},
+ {'type': 'int', 'id': 'sid'},
+ {'type': 'int', 'id': 'did'}
+ ]
+ ids = [
+ {'type': 'int', 'id': 'scid'}
+ ]
+
+ operations = dict({
+ 'obj': [
+ {'get': 'properties', 'delete': 'delete', 'put': 'update'},
+ {'get': 'list', 'post': 'create'}
+ ],
+ 'children': [{
+ 'get': 'children'
+ }],
+ 'nodes': [{'get': 'node'}, {'get': 'nodes'}],
+ 'sql': [{'get': 'sql'}],
+ 'msql': [{'get': 'msql'}, {'get': 'msql'}],
+ 'stats': [{'get': 'statistics'}],
+ 'dependency': [{'get': 'dependencies'}],
+ 'dependent': [{'get': 'dependents'}],
+ 'module.js': [{}, {}, {'get': 'module_js'}],
+ 'delete': [{'delete': 'delete'}]
+ })
+
+ def module_js(self):
+ """
+ This property defines (if javascript) exists for this node.
+ Override this property for your own logic.
+ """
+ return make_response(
+ render_template(
+ "schemas/js/schemas.js",
+ _=gettext
+ ),
+ 200, {'Content-Type': 'application/x-javascript'}
+ )
+
+ def check_precondition(f):
+ """
+ This function will behave as a decorator which will checks
+ database connection before running view, it will also attaches
+ manager,conn & template_path properties to self
+ """
+ @wraps(f)
+ def wrap(*args, **kwargs):
+ # Here args[0] will hold self & kwargs will hold gid,sid,did
+ self = args[0]
+ self.manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(kwargs['sid'])
+ self.conn = self.manager.connection(did=kwargs['did'])
+ # If DB not connected then return error to browser
+ if not self.conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+
+ ver = self.manager.version
+ server_type = self.manager.server_type
+ # we will set template path for sql scripts
+ # For EDB PPAS
+ if server_type == 'ppas' and ver >= 90200:
+ self.template_path = 'schemas/sql/edb_9.2_plus'
+ elif server_type == 'ppas' and ver >= 90100:
+ self.template_path = 'schemas/sql/edb_9.1_plus'
+ elif server_type == 'ppas' and ver <= 90100:
+ self.template_path = 'schemas/sql/edb_pre_9.1'
+ # For Postgres
+ elif ver >= 90200:
+ self.template_path = 'schemas/sql/9.2_plus'
+ elif ver >= 90100:
+ self.template_path = 'schemas/sql/9.1_plus'
+ else:
+ self.template_path = 'schemas/sql/pre_9.1'
+
+ return f(*args, **kwargs)
+
+ return wrap
+
+
+ @check_precondition
+ def list(self, gid, sid, did):
+ SQL = render_template("/".join([self.template_path, 'properties.sql']))
+ 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):
+ res = []
+ SQL = render_template("/".join([self.template_path, 'properties.sql']))
+ 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'],
+ row['name'],
+ icon="pg-icon-schema"
+ ))
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ @check_precondition
+ def properties(self, gid, sid, did, scid):
+ SQL = render_template("/".join([self.template_path, 'properties.sql']), scid=scid)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return ajax_response(
+ response=res['rows'][0],
+ status=200
+ )
+
+ @check_precondition
+ def create(self, gid, sid, did):
+ """
+ This function will creates new the schema object
+ """
+
+ required_args = [
+ 'name'
+ ]
+
+ data = request.form if request.form else json.loads(request.data.decode())
+ for arg in required_args:
+ if arg not in data:
+ return make_json_response(
+ status=410,
+ success=0,
+ errormsg=gettext(
+ "Couldn't find the required parameter (%s)." % arg
+ )
+ )
+ try:
+ SQL = render_template("/".join([self.template_path, 'create.sql']), data=data)
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+ SQL = render_template("/".join([self.template_path, 'grant.sql']), data=data)
+ # Checking if we are not executing empty query
+ 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)
+ # we need oid to to add object in tree at browser, below sql will gives the same
+ SQL = render_template("/".join([self.template_path, 'grant.sql']), schema=data['name'])
+ status, scid = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=scid)
+
+ return jsonify(
+ node=self.blueprint.generate_browser_node(
+ scid,
+ data['name'],
+ icon="pg-icon-schema"
+ )
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+
+ @check_precondition
+ def update(self, gid, sid, did, scid):
+ """
+ This function will update schema object
+ """
+ data = request.form if request.form else json.loads(request.data.decode())
+ SQL = self.getSQL(gid, sid, data, scid)
+ try:
+ if SQL and SQL.strip('\n') and SQL.strip(' '):
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info="Schema updated",
+ data={
+ 'id': scid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+ else:
+ return make_json_response(
+ success=1,
+ info="Nothing to update",
+ data={
+ 'id': 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):
+ """
+ This function will drop the schema object
+ """
+ # 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:
+ # Get name for schema from did
+ SQL = render_template("/".join([self.template_path, 'delete.sql']), scid=scid)
+ status, name = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=name)
+ # drop schema
+ SQL = render_template("/".join([self.template_path, 'delete.sql']), name=name, cascade=cascade)
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info=gettext("Schema dropped"),
+ data={
+ 'id': 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=None):
+ """
+ This function to return modified SQL
+ """
+ data = request.args
+ SQL = self.getSQL(gid, sid, data, scid)
+ if isinstance(SQL, str) and SQL and SQL.strip('\n') and SQL.strip(' '):
+ return make_json_response(
+ data=SQL,
+ status=200
+ )
+ else:
+ return make_json_response(
+ data="--modified SQL",
+ status=200
+ )
+
+ def getSQL(self, gid, sid, data, scid=None):
+ """
+ This function will genrate sql from model data
+ """
+ try:
+ if scid is not None:
+ SQL = render_template("/".join([self.template_path, 'properties.sql']), scid=scid)
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+ old_data = res['rows'][0]
+ SQL = render_template(
+ "/".join([self.template_path,'update.sql']),
+ data=data, o_data=old_data
+ )
+ else:
+ SQL = render_template("/".join([self.template_path, 'create.sql']), data=data)
+ SQL += "\n"
+ SQL += render_template("/".join([self.template_path, 'grant.sql']), data=data)
+ return SQL
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+
+SchemaView.register_node_view(blueprint)
+
+
+class CatalogModule(CollectionNodeModule):
+ NODE_TYPE = 'catalog'
+ COLLECTION_LABEL = gettext("Catalogs")
+
+ def __init__(self, *args, **kwargs):
+ self.min_ver = None
+ self.max_ver = None
+
+ super(CatalogModule, self).__init__(*args, **kwargs)
+
+ def get_nodes(self, gid, sid, did):
+ """
+ Generate the collection node
+ """
+ yield self.generate_browser_collection_node(did)
+
+ @property
+ def script_load(self):
+ """
+ Load the module script for server, when any of the server-group node is
+ initialized.
+ """
+ return servers.ServerModule.NODE_TYPE
+
+ @property
+ def csssnippets(self):
+ """
+ Returns a snippet of css to include in the page
+ """
+ snippets = [
+ render_template(
+ "browser/css/collection.css",
+ node_type=self.node_type,
+ _=gettext
+ ),
+ render_template(
+ "catalogs/css/catalog.css",
+ node_type=self.node_type,
+ _=gettext
+ )
+ ]
+
+ for submodule in self.submodules:
+ snippets.extend(submodule.csssnippets)
+
+ return snippets
+
+
+catalog_blueprint = CatalogModule(__name__)
+
+
+class CatalogView(PGChildNodeView):
+ node_type = catalog_blueprint.node_type
+
+ parent_ids = [
+ {'type': 'int', 'id': 'gid'},
+ {'type': 'int', 'id': 'sid'},
+ {'type': 'int', 'id': 'did'}
+ ]
+ ids = [
+ {'type': 'int', 'id': 'scid'}
+ ]
+
+ operations = dict({
+ 'obj': [
+ {'get': 'properties', 'delete': 'delete', 'put': 'update'},
+ {'get': 'list', 'post': 'create'}
+ ],
+ 'children': [{'get': 'children'}],
+ 'nodes': [{'get': 'node'}, {'get': 'nodes'}],
+ 'sql': [{'get': 'sql'}],
+ 'msql': [{'get': 'msql'}, {'get': 'msql'}],
+ 'stats': [{'get': 'statistics'}],
+ 'dependency': [{'get': 'dependencies'}],
+ 'dependent': [{'get': 'dependents'}],
+ 'module.js': [{}, {}, {'get': 'module_js'}],
+ 'configs': [{'get': 'configs'}]
+ })
+
+ def module_js(self):
+ """
+ This property defines (if javascript) exists for this node.
+ Override this property for your own logic.
+ """
+ return make_response(
+ render_template(
+ "catalogs/js/catalogs.js",
+ _=gettext
+ ),
+ 200, {'Content-Type': 'application/x-javascript'}
+ )
+
+ 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()
+
+ # If DB not connected then return error to browser
+ if not self.conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+
+ ver = self.manager.version
+ server_type = self.manager.server_type
+ # we will set template path for sql scripts
+ # For EDB PPAS
+ if server_type == 'ppas' and ver >= 90200:
+ self.template_path = 'catalogs/sql/edb_9.2_plus'
+ elif server_type == 'ppas' and ver >= 90100:
+ self.template_path = 'catalogs/sql/edb_9.1_plus'
+ elif server_type == 'ppas' and ver <= 90100:
+ self.template_path = 'catalogs/sql/edb_pre_9.1'
+ # For Postgres
+ elif ver >= 90200:
+ self.template_path = 'catalogs/sql/9.2_plus'
+ elif ver >= 90100:
+ self.template_path = 'catalogs/sql/9.1_plus'
+ else:
+ self.template_path = 'catalogs/sql/pre_9.1'
+
+ return f(*args, **kwargs)
+
+ return wrap
+
+
+ @check_precondition
+ def list(self, gid, sid, did):
+ SQL = render_template("/".join([self.template_path, 'properties.sql']))
+ 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):
+ res = []
+ SQL = render_template("/".join([self.template_path, 'properties.sql']))
+ 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'],
+ row['name'],
+ icon="pg-icon-catalog"
+ ))
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ @check_precondition
+ def properties(self, gid, sid, did, scid):
+ SQL = render_template("/".join([self.template_path, 'properties.sql']),
+ cid=scid)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return ajax_response(
+ response=res['rows'][0],
+ status=200
+ )
+
+ @check_precondition
+ def update(self, gid, sid, did, scid):
+ """
+ This function will update tablespace object
+ """
+ data = request.form if request.form else json.loads(request.data.decode())
+ SQL = self.getSQL(gid, sid, data, scid)
+
+ try:
+ if SQL and SQL.strip('\n') and SQL.strip(' '):
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info="Catalog updated",
+ data={
+ 'id': scid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+ else:
+ return make_json_response(
+ success=1,
+ info="Nothing to update",
+ data={
+ 'id': 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):
+ """
+ This function to return modified SQL
+ """
+ data = request.args
+ SQL = self.getSQL(gid, sid, data, scid)
+ if SQL and SQL.strip('\n') and SQL.strip(' '):
+ return make_json_response(
+ data=SQL,
+ status=200
+ )
+ else:
+ return make_json_response(
+ data='-- Modified SQL --',
+ status=200
+ )
+
+ def getSQL(self, gid, sid, data, scid=None):
+ """
+ This function will genrate sql from model data
+ """
+ try:
+ if scid is not None:
+ SQL = render_template("/".join([self.template_path, 'properties.sql']), cid=scid)
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+ old_data = res['rows'][0]
+ SQL = render_template("/".join([self.template_path, 'update.sql']), data=data, o_data=old_data)
+ else:
+ SQL = '-- No change!'
+ return SQL
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+
+CatalogView.register_node_view(catalog_blueprint)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/__init__.py
new file mode 100644
index 0000000..f1cf3de
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/__init__.py
@@ -0,0 +1,231 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2015, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+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 NodeView
+from pgadmin.browser.collection import CollectionNodeModule
+import pgadmin.browser.server_groups.servers.databases as database
+from pgadmin.utils.ajax import precondition_required
+from pgadmin.utils.driver import get_driver
+from config import PG_DEFAULT_DRIVER
+from functools import wraps
+
+class CatalogObjectModule(CollectionNodeModule):
+ NODE_TYPE = 'catalogobjects'
+ COLLECTION_LABEL = gettext("Catalog Objects")
+
+ def __init__(self, *args, **kwargs):
+ self.min_ver = None
+ self.max_ver = None
+ super(CatalogObjectModule, self).__init__(*args, **kwargs)
+
+ # Before loading this module we need to make sure that scid is catalog object
+ # and catalog name is 'sys', 'dbo', 'information_schema' then only we load this module
+ def BackendSupported(self, manager, **kwargs):
+ """
+ This function will validate schema name & scid against catalogs then allow us to
+ make dission if we want to load this module or not for that schema
+ """
+ if super(CatalogObjectModule, self).BackendSupported(manager, **kwargs):
+ conn = manager.connection()
+ # If DB is not connected then return error to browser
+ if not conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+ ver = manager.version
+ # we will set template path for sql scripts
+ if ver >= 90200:
+ template_path = 'catalogobjects/sql/9.2_plus'
+ elif ver >= 90100:
+ template_path = 'catalogobjects/sql/9.1_plus'
+ else:
+ template_path = 'catalogobjects/sql/pre_9.1'
+
+ SQL = render_template("/".join([template_path, 'backend_support.sql']), scid=kwargs['scid'])
+ status, res = conn.execute_scalar(SQL)
+ # check if any errors
+ if not status:
+ return internal_server_error(errormsg=res)
+ # Check scid is catalog and from 'sys', 'dbo', 'information_schema',
+ # then true, othewise false
+ if res is True:
+ return res
+ else:
+ return res
+
+ 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 server, when any of the server-group node is
+ initialized.
+ """
+ return database.DatabaseModule.NODE_TYPE
+
+ @property
+ def csssnippets(self):
+ """
+ Returns a snippet of css to include in the page
+ """
+ snippets = [
+ render_template(
+ "browser/css/collection.css",
+ node_type=self.node_type,
+ _=gettext
+ ),
+ render_template(
+ "catalogobjects/css/catalogobjects.css",
+ node_type=self.node_type,
+ _=gettext
+ )
+ ]
+
+ for submodule in self.submodules:
+ snippets.extend(submodule.csssnippets)
+
+ return snippets
+
+
+blueprint = CatalogObjectModule(__name__)
+
+
+class CatalogObjectView(NodeView):
+ 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': 'coid'}
+ ]
+
+ operations = dict({
+ 'obj': [
+ {'get': 'properties', 'delete': 'delete', 'put': 'update'},
+ {'get': 'list', 'post': 'create'}
+ ],
+ 'children': [{'get': 'children'}],
+ 'nodes': [{'get': 'node'}, {'get': 'nodes'}],
+ 'sql': [{'get': 'sql'}],
+ 'msql': [{'get': 'msql'}, {'get': 'msql'}],
+ 'stats': [{'get': 'statistics'}],
+ 'dependency': [{'get': 'dependencies'}],
+ 'dependent': [{'get': 'dependents'}],
+ 'module.js': [{}, {}, {'get': 'module_js'}],
+ 'configs': [{'get': 'configs'}]
+ })
+
+ def module_js(self):
+ """
+ This property defines (if javascript) exists for this node.
+ Override this property for your own logic.
+ """
+ return make_response(
+ render_template(
+ "catalogobjects/js/catalogobjects.js",
+ _=gettext
+ ),
+ 200, {'Content-Type': 'application/x-javascript'}
+ )
+
+ def check_precondition(f):
+ """
+ This function will behave as a decorator which will checks
+ database connection before running view, it will also attaches
+ manager,conn & template_path properties to self
+ """
+ @wraps(f)
+ def wrap(*args, **kwargs):
+ # Here args[0] will hold self & kwargs will hold gid,sid,did
+ self = args[0]
+ self.manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(kwargs['sid'])
+ self.conn = self.manager.connection(did=kwargs['did'])
+ # If DB not connected then return error to browser
+ if not self.conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+
+ ver = self.manager.version
+ # we will set template path for sql scripts
+ if ver >= 90200:
+ self.template_path = 'catalogobjects/sql/9.2_plus'
+ elif ver >= 90100:
+ self.template_path = 'catalogobjects/sql/9.1_plus'
+ else:
+ self.template_path = 'catalogobjects/sql/pre_9.1'
+
+ return f(*args, **kwargs)
+
+ return wrap
+
+
+ @check_precondition
+ def list(self, gid, sid, did, scid):
+ SQL = render_template("/".join([self.template_path, 'properties.sql']), scid=scid)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+ return ajax_response(
+ response=res['rows'],
+ status=200
+ )
+
+ @check_precondition
+ def nodes(self, gid, sid, did, scid):
+ res = []
+ SQL = render_template("/".join([self.template_path, 'properties.sql']), scid=scid)
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=rset)
+
+ for row in rset['rows']:
+ res.append(
+ self.blueprint.generate_browser_node(
+ row['oid'],
+ row['name'],
+ icon="pg-icon-catalogobject"
+ ))
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ @check_precondition
+ def properties(self, gid, sid, did, scid, coid):
+ SQL = render_template("/".join([self.template_path, 'properties.sql']),scid=scid, coid=coid)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return ajax_response(
+ response=res['rows'][0],
+ status=200
+ )
+
+CatalogObjectView.register_node_view(blueprint)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/__init__.py
new file mode 100644
index 0000000..f14004c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/__init__.py
@@ -0,0 +1,195 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2015, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+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 NodeView
+from pgadmin.browser.collection import CollectionNodeModule
+import pgadmin.browser.server_groups.servers.databases as database
+from pgadmin.utils.ajax import precondition_required
+from pgadmin.utils.driver import get_driver
+from config import PG_DEFAULT_DRIVER
+from functools import wraps
+
+class ColumnsModule(CollectionNodeModule):
+ NODE_TYPE = 'columns'
+ COLLECTION_LABEL = gettext("Columns")
+
+ def __init__(self, *args, **kwargs):
+ self.min_ver = None
+ self.max_ver = None
+ super(ColumnsModule, self).__init__(*args, **kwargs)
+
+ def get_nodes(self, gid, sid, did, scid, coid):
+ """
+ Generate the collection node
+ """
+ yield self.generate_browser_collection_node(coid)
+
+ @property
+ def script_load(self):
+ """
+ Load the module script for server, when any of the server-group node is
+ initialized.
+ """
+ return database.DatabaseModule.NODE_TYPE
+
+ @property
+ def csssnippets(self):
+ """
+ Returns a snippet of css to include in the page
+ """
+ snippets = [
+ render_template(
+ "browser/css/collection.css",
+ node_type=self.node_type,
+ _=gettext
+ ),
+ render_template(
+ "columns/css/columns.css",
+ node_type=self.node_type,
+ _=gettext
+ )
+ ]
+
+ for submodule in self.submodules:
+ snippets.extend(submodule.csssnippets)
+
+ return snippets
+
+
+blueprint = ColumnsModule(__name__)
+
+
+class ColumnsView(NodeView):
+ node_type = blueprint.node_type
+
+ parent_ids = [
+ {'type': 'int', 'id': 'gid'},
+ {'type': 'int', 'id': 'sid'},
+ {'type': 'int', 'id': 'did'},
+ {'type': 'int', 'id': 'scid'},
+ {'type': 'int', 'id': 'coid'}
+ ]
+ ids = [
+ {'type': 'int', 'id': 'clid'}
+ ]
+
+ operations = dict({
+ 'obj': [
+ {'get': 'properties', 'delete': 'delete', 'put': 'update'},
+ {'get': 'list', 'post': 'create'}
+ ],
+ 'children': [{'get': 'children'}],
+ 'nodes': [{'get': 'node'}, {'get': 'nodes'}],
+ 'sql': [{'get': 'sql'}],
+ 'msql': [{'get': 'msql'}, {'get': 'msql'}],
+ 'stats': [{'get': 'statistics'}],
+ 'dependency': [{'get': 'dependencies'}],
+ 'dependent': [{'get': 'dependents'}],
+ 'module.js': [{}, {}, {'get': 'module_js'}],
+ 'configs': [{'get': 'configs'}]
+ })
+
+ def module_js(self):
+ """
+ This property defines (if javascript) exists for this node.
+ Override this property for your own logic.
+ """
+ return make_response(
+ render_template(
+ "columns/js/columns.js",
+ _=gettext
+ ),
+ 200, {'Content-Type': 'application/x-javascript'}
+ )
+
+ def check_precondition(f):
+ """
+ This function will behave as a decorator which will checks
+ database connection before running view, it will also attaches
+ manager,conn & template_path properties to self
+ """
+ @wraps(f)
+ def wrap(*args, **kwargs):
+ # Here args[0] will hold self & kwargs will hold gid,sid,did
+ self = args[0]
+ self.manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(kwargs['sid'])
+ self.conn = self.manager.connection(did=kwargs['did'])
+ # If DB not connected then return error to browser
+ if not self.conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+
+ ver = self.manager.version
+ # we will set template path for sql scripts
+ if ver >= 90200:
+ self.template_path = 'columns/sql/9.2_plus'
+ elif ver >= 90100:
+ self.template_path = 'columns/sql/9.1_plus'
+ else:
+ self.template_path = 'columns/sql/pre_9.1'
+
+ return f(*args, **kwargs)
+
+ return wrap
+
+
+ @check_precondition
+ def list(self, gid, sid, did, scid, coid):
+ SQL = render_template("/".join([self.template_path, 'properties.sql']), coid=coid)
+ 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, coid):
+ res = []
+ SQL = render_template("/".join([self.template_path, 'properties.sql']), coid=coid)
+ 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['atttypid'],
+ row['attname'],
+ icon="pg-icon-columns"
+ ))
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ @check_precondition
+ def properties(self, gid, sid, did, scid, coid, clid):
+ SQL = render_template("/".join([self.template_path, 'properties.sql']),coid=coid, clid=clid)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return ajax_response(
+ response=res['rows'][0],
+ status=200
+ )
+
+ColumnsView.register_node_view(blueprint)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/static/img/coll-columns.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/static/img/coll-columns.png
new file mode 100644
index 0000000000000000000000000000000000000000..89d758834d4176c1df2548db10b46b1f6b2e4ec5
GIT binary patch
literal 400
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbK}cz{ocE09*4`h3Ev&s(m&yL<QU
z)2C0LJ$u%^|9SG<<M-~}d-(9-vSrKOym_;3-MW`AU#?uaa_!o+>({S;_3G90=g%KJ
zc<}o5>kl73eEaro$BrEvHf-3qapU*z-`~D{TatgM6KFJJNswPKgTu2MX+REVfk$L9
zkoEv$x0Bg+Kt_S5i(`ny<=FG?VhsvBt`}W4E@ZQg__p6qm@nby;qrG1j0_I@c^(;r
zuhP)2X<D_P?dti2$vzyv(k(V*o?f?H<i?gXiRA)mhTUpwofQn*P8`|LWw=McNaXQ!
zy+q@@Yd0m1ylF3f{(An_-EV$!P4L^#_`09vh+=cW8=&2)C9V-ADTyViR>?)FK#IZ0
zz|cU~&`8(7FvQ5f%EZ{p#6;V`)XKoXVy3DbiiX_$l+3hBhz0{oum+H7D+4o#hEvl+
R*8nvzc)I$ztaD0e0s#BYr!@co
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/static/img/columns.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/static/img/columns.png
new file mode 100644
index 0000000000000000000000000000000000000000..bd9f81df98fe27d81ade5144d66b3b09b96123c1
GIT binary patch
literal 435
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbK}X@F0NE09*4`h3Ev&s(m&yL<QU
zy?giW-@pI#>C<P=p0)3PzHHgD=g*(Nc=6)Rn>Xv$t$X?M<;s;SSFBjEcJ12r>({?}
z_3FWc2M-@UeDvti>eZ{)tXZ>Z)20s}KD>VY`qQURpFe;8`t|F#Z{K$8*s)>5hK(CH
ze*gac?c2BS-o49eKe!ZVF=I)PUoeBivm0qZ4rhT!WHFHT0Ash4*>*risi%u$h{WaE
z^B0Ah6a-om4Rm-iuV3+X<o2rm?|w$}?!!eN_y0*dcT_ORb6x7A={6cu<^6X}kY2Ib
zZ0TMHrih1I^CUh^d%@<y)3TvOv&K6iH~-RCTdVU6czMe9*h~1l{VS1_(D3~9P6_q4
zgXi+))!6E^|9xfrB*GzI-TA)^=m6Cc*NBpo#FA92<f2p{#b9J$XrOCoq-$UpVq{=t
zVr*q%qHSPmWnf@2Q&kQ{LvDUbW?CgggMlSj14y-%ff+=@sp+9>fEpM)UHx3vIVCg!
E0NRqnZ~y=R
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/templates/columns/css/columns.css b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/templates/columns/css/columns.css
new file mode 100644
index 0000000..c48b5e4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/templates/columns/css/columns.css
@@ -0,0 +1,9 @@
+.pg-icon-columns {
+ background-image: url('{{ url_for('NODE-columns.static', filename='img/columns.png') }}') !important;
+ border-radius: 10px;
+ background-repeat: no-repeat;
+ align-content: center;
+ vertical-align: middle;
+ height: 1.3em;
+}
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/templates/columns/js/columns.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/templates/columns/js/columns.js
new file mode 100644
index 0000000..5e4095b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/templates/columns/js/columns.js
@@ -0,0 +1,76 @@
+define(
+ ['jquery', 'underscore', 'underscore.string', 'pgadmin', 'pgadmin.browser', 'alertify', 'pgadmin.browser.collection'],
+function($, _, S, pgAdmin, pgBrowser, alertify) {
+
+ if (!pgBrowser.Nodes['coll-columns']) {
+ var databases = pgAdmin.Browser.Nodes['coll-columns'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'columns',
+ label: '{{ _('Columns') }}',
+ type: 'coll-columns'
+ });
+ };
+
+ if (!pgBrowser.Nodes['columns']) {
+ pgAdmin.Browser.Nodes['columns'] = pgAdmin.Browser.Node.extend({
+ parent_type: 'catalogobjects',
+ type: 'columns',
+ label: '{{ _('Column') }}',
+ hasSQL: false,
+ Init: function() {
+ /* Avoid mulitple registration of menus */
+ if (this.initialized)
+ return;
+
+ this.initialized = true;
+
+ },
+ model: pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ attname: undefined,
+ attowner: undefined,
+ atttypid: undefined,
+ attnum: undefined,
+ cltype: undefined,
+ collspcname: undefined,
+ attacl: undefined,
+ description: undefined
+ },
+ schema: [{
+ id: 'attname', label: '{{ _('Column') }}', cell: 'string',
+ type: 'text', disabled: true, //mode: ['properties']
+ },{
+ id: 'atttypid', label: '{{ _('Oid') }}', cell: 'string',
+ type: 'text', disabled: true, //mode: ['edit']
+ },{
+ id: 'attowner', label: '{{ _('Owner') }}', cell: 'string',
+ type: 'text', disabled: true, //mode: ['properties']
+ },{
+ id: 'attnum', label:'{{ _('Position') }}', cell: 'string',
+ type: 'text', disabled: true, //mode: ['properties']
+ },{
+ id: 'cltype', label:'{{ _('Data type') }}', cell: 'string',
+ type: 'text', disabled: true, //mode: ['properties']
+ },{
+ id: 'collspcname', label:'{{ _('Collation') }}', cell: 'string',
+ type: 'text', disabled: true, //mode: ['properties']
+ },{
+ id: 'attacl', label:'{{ _('ACL') }}', cell: 'string',
+ type: 'text', disabled: true, //mode: ['properties']
+ },{
+ id: 'description', label:'{{ _('Comment') }}', cell: 'string',
+ type: 'multiline', disabled: true, //mode: ['properties']
+ }
+ ],
+ validate: function() {
+ // TODO: Add validation here
+ return null;
+
+ }
+ })
+ });
+
+ }
+
+ return pgBrowser.Nodes['coll-columns'];
+});
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/templates/columns/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/templates/columns/sql/9.1_plus/properties.sql
new file mode 100644
index 0000000..abd0ebb
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/templates/columns/sql/9.1_plus/properties.sql
@@ -0,0 +1,39 @@
+SELECT att.*, def.*, pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS defval,
+ CASE WHEN att.attndims > 0 THEN 1 ELSE 0 END AS isarray,
+ format_type(ty.oid,NULL) AS typname,
+ format_type(ty.oid,att.atttypmod) AS displaytypname,
+ tn.nspname as typnspname, et.typname as elemtypname,
+ ty.typstorage AS defaultstorage, cl.relname, na.nspname,
+ att.attstattarget, description, cs.relname AS sername,
+ ns.nspname AS serschema,
+ (SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname) > 1 AS isdup,
+ indkey, coll.collname, nspc.nspname as collnspname , attoptions,
+ -- Start pgAdmin4, added to save time on client side parsing
+ CASE WHEN length(coll.collname) > 0 AND length(nspc.nspname) > 0 THEN
+ concat(coll.collname,'."',nspc.nspname,'"')
+ ELSE '' END AS collspcname,
+ CASE WHEN strpos(format_type(ty.oid,att.atttypmod), '.') > 0 THEN
+ split_part(format_type(ty.oid,att.atttypmod), '.', 2)
+ ELSE format_type(ty.oid,att.atttypmod) END AS cltype,
+ -- End pgAdmin4
+ EXISTS(SELECT 1 FROM pg_constraint WHERE conrelid=att.attrelid AND contype='f' AND att.attnum=ANY(conkey)) As isfk,
+ (SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=att.attrelid AND sl1.objsubid=att.attnum) AS labels,
+ (SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=att.attrelid AND sl2.objsubid=att.attnum) AS providers
+FROM pg_attribute att
+ JOIN pg_type ty ON ty.oid=atttypid
+ JOIN pg_namespace tn ON tn.oid=ty.typnamespace
+ JOIN pg_class cl ON cl.oid=att.attrelid
+ JOIN pg_namespace na ON na.oid=cl.relnamespace
+ LEFT OUTER JOIN pg_type et ON et.oid=ty.typelem
+ LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
+ LEFT OUTER JOIN pg_description des ON (des.objoid=att.attrelid AND des.objsubid=att.attnum AND des.classoid='pg_class'::regclass)
+ LEFT OUTER JOIN (pg_depend JOIN pg_class cs ON objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum
+ LEFT OUTER JOIN pg_namespace ns ON ns.oid=cs.relnamespace
+ LEFT OUTER JOIN pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
+ LEFT OUTER JOIN pg_collation coll ON att.attcollation=coll.oid
+ LEFT OUTER JOIN pg_namespace nspc ON coll.collnamespace=nspc.oid
+WHERE att.attrelid = {{coid}}::oid
+ {% if clid %}AND att.atttypid = {{clid}}::oid{% endif %}
+ AND att.attnum > 0
+ AND att.attisdropped IS FALSE
+ORDER BY att.attnum
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/templates/columns/sql/9.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/templates/columns/sql/9.2_plus/properties.sql
new file mode 100644
index 0000000..e2d4615
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/templates/columns/sql/9.2_plus/properties.sql
@@ -0,0 +1,40 @@
+SELECT att.*, def.*, pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS defval,
+ CASE WHEN att.attndims > 0 THEN 1 ELSE 0 END AS isarray,
+ format_type(ty.oid,NULL) AS typname,
+ format_type(ty.oid,att.atttypmod) AS displaytypname,
+ tn.nspname as typnspname, et.typname as elemtypname,
+ ty.typstorage AS defaultstorage, cl.relname, na.nspname,
+ att.attstattarget, description, cs.relname AS sername,
+ ns.nspname AS serschema,
+ (SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname) > 1 AS isdup,
+ indkey, coll.collname, nspc.nspname as collnspname , attoptions,
+ -- Start pgAdmin4, added to save time on client side parsing
+ CASE WHEN length(coll.collname) > 0 AND length(nspc.nspname) > 0 THEN
+ concat(coll.collname,'."',nspc.nspname,'"')
+ ELSE '' END AS collspcname,
+ CASE WHEN strpos(format_type(ty.oid,att.atttypmod), '.') > 0 THEN
+ split_part(format_type(ty.oid,att.atttypmod), '.', 2)
+ ELSE format_type(ty.oid,att.atttypmod) END AS cltype,
+ -- End pgAdmin4
+ EXISTS(SELECT 1 FROM pg_constraint WHERE conrelid=att.attrelid AND contype='f' AND att.attnum=ANY(conkey)) As isfk,
+ (SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=att.attrelid AND sl1.objsubid=att.attnum) AS labels,
+ (SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=att.attrelid AND sl2.objsubid=att.attnum) AS providers
+FROM pg_attribute att
+ JOIN pg_type ty ON ty.oid=atttypid
+ JOIN pg_namespace tn ON tn.oid=ty.typnamespace
+ JOIN pg_class cl ON cl.oid=att.attrelid
+ JOIN pg_namespace na ON na.oid=cl.relnamespace
+ LEFT OUTER JOIN pg_type et ON et.oid=ty.typelem
+ LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
+ LEFT OUTER JOIN pg_description des ON (des.objoid=att.attrelid AND des.objsubid=att.attnum AND des.classoid='pg_class'::regclass)
+ LEFT OUTER JOIN (pg_depend JOIN pg_class cs ON objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum
+ LEFT OUTER JOIN pg_namespace ns ON ns.oid=cs.relnamespace
+ LEFT OUTER JOIN pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
+ LEFT OUTER JOIN pg_collation coll ON att.attcollation=coll.oid
+ LEFT OUTER JOIN pg_namespace nspc ON coll.collnamespace=nspc.oid
+WHERE att.attrelid = {{coid}}::oid
+ {% if clid %}AND att.atttypid = {{clid}}::oid{% endif %}
+ AND att.attnum > 0
+ AND att.attisdropped IS FALSE
+ORDER BY att.attnum
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/templates/columns/sql/pre_9.1/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/templates/columns/sql/pre_9.1/properties.sql
new file mode 100644
index 0000000..0996aef
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/columns/templates/columns/sql/pre_9.1/properties.sql
@@ -0,0 +1,35 @@
+SELECT att.*, def.*, pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS defval,
+ CASE WHEN att.attndims > 0 THEN 1 ELSE 0 END AS isarray,
+ format_type(ty.oid,NULL) AS typname,
+ format_type(ty.oid,att.atttypmod) AS displaytypname,
+ tn.nspname as typnspname, et.typname as elemtypname,
+ ty.typstorage AS defaultstorage, cl.relname, na.nspname,
+ att.attstattarget, description, cs.relname AS sername,
+ ns.nspname AS serschema,
+ (SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname) > 1 AS isdup,
+ indkey, attoptions,
+ -- Start pgAdmin4, added to save time on client side parsing
+ CASE WHEN strpos(format_type(ty.oid,att.atttypmod), '.') > 0 THEN
+ split_part(format_type(ty.oid,att.atttypmod), '.', 2)
+ ELSE format_type(ty.oid,att.atttypmod) END AS cltype,
+ -- End pgAdmin4
+ EXISTS(SELECT 1 FROM pg_constraint WHERE conrelid=att.attrelid AND contype='f' AND att.attnum=ANY(conkey)) As isfk
+FROM pg_attribute att
+ JOIN pg_type ty ON ty.oid=atttypid
+ JOIN pg_namespace tn ON tn.oid=ty.typnamespace
+ JOIN pg_class cl ON cl.oid=att.attrelid
+ JOIN pg_namespace na ON na.oid=cl.relnamespace
+ LEFT OUTER JOIN pg_type et ON et.oid=ty.typelem
+ LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
+ LEFT OUTER JOIN pg_description des ON (des.objoid=att.attrelid AND des.objsubid=att.attnum AND des.classoid='pg_class'::regclass)
+ LEFT OUTER JOIN (pg_depend JOIN pg_class cs ON objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum
+ LEFT OUTER JOIN pg_namespace ns ON ns.oid=cs.relnamespace
+ LEFT OUTER JOIN pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
+ LEFT OUTER JOIN pg_collation coll ON att.attcollation=coll.oid
+ LEFT OUTER JOIN pg_namespace nspc ON coll.collnamespace=nspc.oid
+WHERE att.attrelid = {{coid}}::oid
+ {% if clid %}AND att.atttypid = {{clid}}::oid{% endif %}
+ AND att.attnum > 0
+ AND att.attisdropped IS FALSE
+ORDER BY att.attnum
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/static/img/catalogobjects.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/static/img/catalogobjects.png
new file mode 100644
index 0000000000000000000000000000000000000000..54ed7389c128fdcdcd86bc504311b9ed62e890ff
GIT binary patch
literal 409
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbK}QGic~E09*4`n>nT|4HY6FS`77
z*NwLq@4vkN`1#Xk&!0VecI)BOHRqluZ+xLQ`$hNp|E*{K)t&fLdiYn)z8{G@zJ+c2
z?7ilL!}53L3*P9?d8IbvW$T&0+53JtE_-LT@QvP_S6i;V`}u8J`~K&*j@bWvKj-(&
zo4+qyn4y_82WTr}NswPKgTu2MX+REVfk$L9koEv$x0Bg+Kt`OWi(`ny<>Z6}7e*c)
zo*thwI%{&?$jq4&bH`?n0;63WqoZP?V4z_kr{@MPJ55H%%mocSYh+v;;u!fv4l;3h
zFz78{aWi04RS0O9aOL`<HH&)Ixr&B%Z_;vMxcZwR(@;29_qOf^piQbJt`Q|Ei6yC4
z$wjF^iowXh&_LJFNY}tH#K^$P#MsKjMBBjB%D}*4rm7r@hTQy=%(O~~1_Mj529Rni
Y12c$*Q`1A&05vdpy85}Sb4q9e0Lf^e3;+NC
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/static/img/coll-catalogobjects.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/static/img/coll-catalogobjects.png
new file mode 100644
index 0000000000000000000000000000000000000000..85b89f1e75c3ba919e0f3f63235d14fb7bc58940
GIT binary patch
literal 419
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbK}W`IwKE09*4`n>DhzbWT`uD$x{
z)SXxN9zTEf{Q0wI&yL=BR<`qn!JHQzXa5Cl__X=z+n?X2HJ|=hb?kTkfuG4czlCl3
z?78}b?b5e~^IogZeA#^ZuNRQJ<gLccm+c3h-#TJH;ne49km4U2GhaM;`TYdYGRBf1
zzhDN3XE)M-9L@rd$YLPv0mg18v+aP4LQfaR5Q)pNeVlv^3Op<;)wR0J=T-dqKXF=D
z;7x%`K53IvKePoN_}pyJW$AzE4V%(`YXup0uH%d^EjTV{uHKcoJB~Mj!D=z%<uzj5
zT$!7r8kR3pmtU}z@dxAWZMQcV-k!sy{5*K!&;MLji|+8>Dm=t0E5ucyF7nz8Xt`>M
zYeY#(Vo9o1a#1RfVlXl=G|)9P(lsy)F*2|+F}5->(Kax(GBB{1sVaw}AvZrIGp!P$
f!N3x%0i@c>zzm|{)b!9bKn)C@u6{1-oD!M<aEhuj
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/css/catalogobjects.css b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/css/catalogobjects.css
new file mode 100644
index 0000000..37ef822
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/css/catalogobjects.css
@@ -0,0 +1,9 @@
+.pg-icon-catalogobject {
+ background-image: url('{{ url_for('NODE-catalogobjects.static', filename='img/catalogobjects.png') }}') !important;
+ border-radius: 10px;
+ background-repeat: no-repeat;
+ align-content: center;
+ vertical-align: middle;
+ height: 1.3em;
+}
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/js/catalogobjects.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/js/catalogobjects.js
new file mode 100644
index 0000000..146ff29
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/js/catalogobjects.js
@@ -0,0 +1,60 @@
+define(
+ ['jquery', 'underscore', 'underscore.string', 'pgadmin', 'pgadmin.browser', 'alertify', 'pgadmin.browser.collection'],
+function($, _, S, pgAdmin, pgBrowser, alertify) {
+
+ if (!pgBrowser.Nodes['coll-catalogobjects']) {
+ var databases = pgAdmin.Browser.Nodes['coll-catalogobjects'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'catalogobjects',
+ label: '{{ _('Catalog Objects') }}',
+ type: 'coll-catalogobjects'
+ });
+ };
+
+ if (!pgBrowser.Nodes['catalogobjects']) {
+ pgAdmin.Browser.Nodes['catalogobjects'] = pgAdmin.Browser.Node.extend({
+ parent_type: 'catalog',
+ type: 'catalogobjects',
+ label: '{{ _('Catalog Object') }}',
+ hasSQL: false,
+ Init: function() {
+ /* Avoid mulitple registration of menus */
+ if (this.initialized)
+ return;
+
+ this.initialized = true;
+
+ },
+ model: pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ name: undefined,
+ namespaceowner: undefined,
+ nspacl: undefined,
+ description: undefined,
+ },
+ schema: [{
+ id: 'name', label: '{{ _('Name') }}', cell: 'string',
+ type: 'text', disabled: true
+ },{
+ id: 'oid', label:'{{ _('Oid') }}', cell: 'string',
+ type: 'text', disabled: true
+ },{
+ id: 'owner', label:'{{ _('Owner') }}', cell: 'string',
+ type: 'text', disabled: true
+ },{
+ id: 'description', label:'{{ _('Comment') }}', cell: 'string',
+ type: 'multiline'
+ }
+ ],
+ validate: function() {
+ // TODO: Add validation here
+ return null;
+
+ }
+ })
+ });
+
+ }
+
+ return pgBrowser.Nodes['coll-catalogobjects'];
+});
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/9.1_plus/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/9.1_plus/backend_support.sql
new file mode 100644
index 0000000..00b7fb4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/9.1_plus/backend_support.sql
@@ -0,0 +1,18 @@
+SELECT
+ CASE WHEN nsp.nspname IN ('sys', 'dbo', 'information_schema') THEN true ELSE false END AS dbSupport
+FROM pg_namespace nsp
+WHERE nsp.oid={{scid}}::int
+AND (
+ (nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+)
+AND
+ nspname NOT LIKE E'pg\\temp\\%'
+AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/9.1_plus/properties.sql
new file mode 100644
index 0000000..b4e715d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/9.1_plus/properties.sql
@@ -0,0 +1,13 @@
+SELECT c.oid, c.relname as name, pg_get_userbyid(relowner) AS owner, description
+ FROM pg_class c
+LEFT OUTER JOIN pg_description d ON (d.objoid=c.oid AND d.classoid='pg_class'::regclass)
+WHERE relnamespace = {{scid}}::int
+{% if coid %}
+AND c.oid = {{coid}}::int
+{% endif %}
+OR (-- On EnterpriseDB we need to ignore some objects in the catalog, namely, _*, dual and type_object_source.
+ select 'sys' ~ (SELECT nsp.nspname FROM pg_namespace nsp WHERE nsp.oid = {{scid}}::int)
+ AND
+ (c.relname NOT LIKE '\\_%' AND c.relname = 'dual' AND c.relname = 'type_object_source')
+ )
+ORDER BY relname;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/9.2_plus/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/9.2_plus/backend_support.sql
new file mode 100644
index 0000000..00b7fb4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/9.2_plus/backend_support.sql
@@ -0,0 +1,18 @@
+SELECT
+ CASE WHEN nsp.nspname IN ('sys', 'dbo', 'information_schema') THEN true ELSE false END AS dbSupport
+FROM pg_namespace nsp
+WHERE nsp.oid={{scid}}::int
+AND (
+ (nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+)
+AND
+ nspname NOT LIKE E'pg\\temp\\%'
+AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/9.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/9.2_plus/properties.sql
new file mode 100644
index 0000000..b4e715d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/9.2_plus/properties.sql
@@ -0,0 +1,13 @@
+SELECT c.oid, c.relname as name, pg_get_userbyid(relowner) AS owner, description
+ FROM pg_class c
+LEFT OUTER JOIN pg_description d ON (d.objoid=c.oid AND d.classoid='pg_class'::regclass)
+WHERE relnamespace = {{scid}}::int
+{% if coid %}
+AND c.oid = {{coid}}::int
+{% endif %}
+OR (-- On EnterpriseDB we need to ignore some objects in the catalog, namely, _*, dual and type_object_source.
+ select 'sys' ~ (SELECT nsp.nspname FROM pg_namespace nsp WHERE nsp.oid = {{scid}}::int)
+ AND
+ (c.relname NOT LIKE '\\_%' AND c.relname = 'dual' AND c.relname = 'type_object_source')
+ )
+ORDER BY relname;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/pre_9.1/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/pre_9.1/backend_support.sql
new file mode 100644
index 0000000..00b7fb4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/pre_9.1/backend_support.sql
@@ -0,0 +1,18 @@
+SELECT
+ CASE WHEN nsp.nspname IN ('sys', 'dbo', 'information_schema') THEN true ELSE false END AS dbSupport
+FROM pg_namespace nsp
+WHERE nsp.oid={{scid}}::int
+AND (
+ (nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+)
+AND
+ nspname NOT LIKE E'pg\\temp\\%'
+AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/pre_9.1/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/pre_9.1/properties.sql
new file mode 100644
index 0000000..b4e715d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/catalogobjects/templates/catalogobjects/sql/pre_9.1/properties.sql
@@ -0,0 +1,13 @@
+SELECT c.oid, c.relname as name, pg_get_userbyid(relowner) AS owner, description
+ FROM pg_class c
+LEFT OUTER JOIN pg_description d ON (d.objoid=c.oid AND d.classoid='pg_class'::regclass)
+WHERE relnamespace = {{scid}}::int
+{% if coid %}
+AND c.oid = {{coid}}::int
+{% endif %}
+OR (-- On EnterpriseDB we need to ignore some objects in the catalog, namely, _*, dual and type_object_source.
+ select 'sys' ~ (SELECT nsp.nspname FROM pg_namespace nsp WHERE nsp.oid = {{scid}}::int)
+ AND
+ (c.relname NOT LIKE '\\_%' AND c.relname = 'dual' AND c.relname = 'type_object_source')
+ )
+ORDER BY relname;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/__init__.py
new file mode 100644
index 0000000..a678331
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/__init__.py
@@ -0,0 +1,468 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2015, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+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 NodeView
+from pgadmin.browser.collection import CollectionNodeModule
+import pgadmin.browser.server_groups.servers.databases as database
+from pgadmin.utils.ajax import precondition_required
+from pgadmin.utils.driver import get_driver
+from config import PG_DEFAULT_DRIVER
+from functools import wraps
+
+class CollationModule(CollectionNodeModule):
+ NODE_TYPE = 'collation'
+ COLLECTION_LABEL = gettext("Collations")
+
+ def __init__(self, *args, **kwargs):
+ self.min_ver = None
+ self.max_ver = None
+ super(CollationModule, self).__init__(*args, **kwargs)
+
+ # Before loading this module we need to make sure that scid is catalog and schema object
+ # and catalog name is 'sys', 'dbo', 'information_schema' then only we load this module
+ def BackendSupported(self, manager, **kwargs):
+ """
+ This function will validate schema name & scid against catalogs then allow us to
+ make dission if we want to load this module or not for that schema
+ """
+ if super(CollationModule, self).BackendSupported(manager, **kwargs):
+ conn = manager.connection()
+ # If DB is not connected then return error to browser
+ if not conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+ ver = manager.version
+ server_type = manager.server_type
+ # we will set template path for sql scripts
+ if server_type == 'ppas' and ver >= 90100:
+ template_path = 'collation/sql/edb_9.1_plus'
+ elif ver >= 90100:
+ template_path = 'collation/sql/9.1_plus'
+ else:
+ # Note: Collation is not supported below postgres version 9.1
+ # Hence we will not load this module
+ return False
+
+ SQL = render_template("/".join([template_path, 'backend_support.sql']), scid=kwargs['scid'])
+ status, res = conn.execute_scalar(SQL)
+ # check if any errors
+ if not status:
+ return internal_server_error(errormsg=res)
+ # Check scid is catalog and from 'sys', 'dbo', 'information_schema',
+ # then False (Do not load this module), othewise True
+ if res is True:
+ return False
+ else:
+ return True
+
+ def get_nodes(self, gid, sid, did, scid):
+ """
+ Generate the collection node
+ """
+ yield self.generate_browser_collection_node(scid)
+
+ @property
+ def script_load(self):
+ """
+ Load the module script for database, when any of the database node is
+ initialized.
+ """
+ return database.DatabaseModule.NODE_TYPE
+
+
+blueprint = CollationModule(__name__)
+
+
+class CollationView(NodeView):
+ 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': 'coid'}
+ ]
+
+ 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_schemas': [{'get': 'getSchemas'}, {'get': 'getSchemas'}],
+ 'get_collations': [{'get': 'getCollations'}, {'get': 'getCollations'}]
+ })
+
+ def module_js(self):
+ """
+ This property defines (if javascript) exists for this node.
+ Override this property for your own logic.
+ """
+ return make_response(
+ render_template(
+ "collation/js/collation.js",
+ _=gettext
+ ),
+ 200, {'Content-Type': 'application/x-javascript'}
+ )
+
+ def check_precondition(f):
+ """
+ This function will behave as a decorator which will checks
+ database connection before running view, it will also attaches
+ manager,conn & template_path properties to self
+ """
+ @wraps(f)
+ def wrap(*args, **kwargs):
+ # Here args[0] will hold self & kwargs will hold gid,sid,did
+ self = args[0]
+ self.manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(kwargs['sid'])
+ self.conn = self.manager.connection(did=kwargs['did'])
+ # If DB not connected then return error to browser
+ if not self.conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+
+ ver = self.manager.version
+ server_type = self.manager.server_type
+ # we will set template path for sql scripts
+ if server_type == 'ppas' and ver >= 90100:
+ self.template_path = 'collation/sql/edb_9.1_plus'
+ elif ver >= 90100:
+ self.template_path = 'collation/sql/9.1_plus'
+ return f(*args, **kwargs)
+
+ return wrap
+
+
+ @check_precondition
+ def list(self, gid, sid, did, scid):
+ SQL = render_template("/".join([self.template_path, 'properties.sql']), scid=scid)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+ return ajax_response(
+ response=res['rows'],
+ status=200
+ )
+
+ @check_precondition
+ def nodes(self, gid, sid, did, scid):
+ res = []
+ SQL = render_template("/".join([self.template_path, 'properties.sql']), scid=scid)
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=rset)
+
+ for row in rset['rows']:
+ res.append(
+ self.blueprint.generate_browser_node(
+ row['oid'],
+ row['name'],
+ icon="icon-collation"
+ ))
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ @check_precondition
+ def properties(self, gid, sid, did, scid, coid):
+ SQL = render_template("/".join([self.template_path, 'properties.sql']),scid=scid, coid=coid)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return ajax_response(
+ response=res['rows'][0],
+ status=200
+ )
+
+ @check_precondition
+ def getSchemas(self, gid, sid, did, scid, coid=None):
+ res = [{ 'label': '', 'value': '' }]
+ try:
+ SQL = render_template("/".join([self.template_path, 'get_schemas.sql']), scid=scid)
+ 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['nspname'], 'value': row['nspname'] }
+ )
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def getCollations(self, gid, sid, did, scid, coid=None):
+ res = [{ 'label': '', 'value': '' }]
+ try:
+ SQL = render_template("/".join([self.template_path, 'get_collations.sql']))
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in rset['rows']:
+ res.append(
+ { 'label': row['copy_collation'], 'value': row['copy_collation'] }
+ )
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+
+
+ @check_precondition
+ def create(self, gid, sid, did, scid):
+ """
+ This function will creates new the collation object
+ """
+
+ data = request.form if request.form else json.loads(request.data.decode())
+ required_args = [
+ 'name',
+ 'schema'
+ ]
+
+ for arg in required_args:
+ if arg not in data:
+ return make_json_response(
+ status=410,
+ success=0,
+ errormsg=gettext(
+ "Couldn't find the required parameter (%s)." % arg
+ )
+ )
+
+ defination_args = [
+ 'locale',
+ 'copy_collation',
+ 'lc_collate',
+ 'lc_type'
+ ]
+
+ # Additional Server Side validation to check if defination is sent properly from client side
+ missing_defination_flag = False
+
+ for arg in defination_args:
+ if arg == 'locale' and arg not in data:
+ if 'copy_collation' not in data and ('lc_collate' not in data and 'lc_type' not in data):
+ missing_defination_flag = True
+
+ if arg == 'copy_collation' and arg not in data:
+ if 'locale' not in data and ('lc_collate' not in data and 'lc_type' not in data):
+ missing_defination_flag = True
+
+ if (arg == 'lc_collate' or arg == 'lc_type') and arg not in data:
+ if 'copy_collation' not in data and 'locale' not in data:
+ missing_defination_flag = True
+
+ if missing_defination_flag:
+ return make_json_response(
+ status=410,
+ success=0,
+ errormsg=gettext(
+ "Incomplete defination, Please provide Locale OR Copy collation OR LC_TYPE/LC_COLLATE"
+ )
+ )
+
+
+ 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)
+ SQL = render_template("/".join([self.template_path, 'grant.sql']), data=data, conn=self.conn)
+ if isinstance(SQL, str) and SQL and SQL.strip('\n') and SQL.strip(' '):
+ 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, below sql will gives the same
+ SQL = render_template("/".join([self.template_path, 'get_oid.sql']), data=data)
+ status, coid = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=coid)
+
+ return jsonify(
+ node=self.blueprint.generate_browser_node(
+ coid,
+ data['name'],
+ icon="icon-collation"
+ )
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def delete(self, gid, sid, did, scid, coid):
+ """
+ This function will drop the object
+ """
+ # 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, 'delete.sql']), scid=scid, coid=coid)
+ 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)
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info=gettext("Collation dropped"),
+ data={
+ 'id': coid,
+ 'scid': scid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def update(self, gid, sid, did, scid, coid):
+ """
+ This function will update the object
+ """
+ data = request.form if request.form else json.loads(request.data.decode())
+ SQL = self.getSQL(gid, sid, data, scid, coid)
+ try:
+ if SQL and SQL.strip('\n') and SQL.strip(' '):
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info="Collation updated",
+ data={
+ 'id': coid,
+ 'scid': scid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+ else:
+ return make_json_response(
+ success=1,
+ info="Nothing to update",
+ data={
+ 'id': coid,
+ '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, coid=None):
+ """
+ This function to return modified SQL
+ """
+ data = request.args
+ if coid is None:
+ required_args = [
+ 'name',
+ 'schema'
+ ]
+
+ for arg in required_args:
+ if arg not in data:
+ return make_json_response(
+ status=410,
+ success=0,
+ errormsg=gettext(
+ "Couldn't find the required parameter (%s)." % arg
+ )
+ )
+ SQL = self.getSQL(gid, sid, data, scid, coid)
+ if isinstance(SQL, str) and SQL and SQL.strip('\n') and SQL.strip(' '):
+ return make_json_response(
+ data=SQL,
+ status=200
+ )
+ else:
+ return SQL
+
+
+ def getSQL(self, gid, sid, data, scid, coid=None):
+ """
+ This function will genrate sql from model data
+ """
+ try:
+ if coid is not None:
+ SQL = render_template("/".join([self.template_path, 'properties.sql']), scid=scid, coid=coid)
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+ old_data = res['rows'][0]
+ SQL = render_template(
+ "/".join([self.template_path,'update.sql']),
+ data=data, o_data=old_data, conn=self.conn
+ )
+ else:
+ SQL = render_template("/".join([self.template_path, 'create.sql']), data=data, conn=self.conn)
+ SQL += "\n"
+ SQL += render_template("/".join([self.template_path, 'grant.sql']), data=data, conn=self.conn)
+ return SQL
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+CollationView.register_node_view(blueprint)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/static/img/coll-collation.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/static/img/coll-collation.png
new file mode 100644
index 0000000000000000000000000000000000000000..fa46a49b36bd910f1aab83a0d82576f5e1fb74c5
GIT binary patch
literal 178
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!61|;P_|4#%`oCO|{#S9GG!XV7ZFl&wkP%y>Q
z#WBR<^xjE<Tn7|*oP|H|y_;9Xvv;|*>$lBKJZ&qE)`;EOW5#Oc%8;RXl53*~!-2V<
zH~d^R_2AruX-C=}CjGnPrgEEQ;e5Nv4expPX-b^5WV(FH{>PFFJ3p3QzQizT8&}^;
Z#<$nB^!IGkwF275;OXk;vd$@?2>?$7Ktuol
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/static/img/collation.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/static/img/collation.png
new file mode 100644
index 0000000000000000000000000000000000000000..0bc8ff7d260f82c275ba5df3252d23b441714413
GIT binary patch
literal 233
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!61SBU+%rFB|jKx9jP7LeL$-D$|I14-?iy0WW
zg+Z8+Vb&Z8pdfpRr>`sfZ5B>`Y1Vg3uj>JYBuiW)N}Tg^b5rw57@Uhz6H8K46v{J8
zG8EiBeFMT9`NV;W!aQ9ZLp07O|M~ylo>{Y@v(e>%QT@M~OC|;e1{JY;xgK_W{{m$0
z(a}7(v}*P?vy*;{_Oi8Aetz~>UFyti^ZqXhu6$xm3y&CRICmD_+f%tDb3tVmE5nZC
WPAwi!3pN04VDNPHb6Mw<&;$TuGEA%h
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/js/collation.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/js/collation.js
new file mode 100644
index 0000000..94d4473
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/js/collation.js
@@ -0,0 +1,240 @@
+define(
+ ['jquery', 'underscore', 'underscore.string', 'pgadmin', 'pgadmin.browser', 'alertify', 'pgadmin.browser.collection'],
+function($, _, S, pgAdmin, pgBrowser, alertify) {
+
+ if (!pgBrowser.Nodes['coll-collation']) {
+ var databases = pgAdmin.Browser.Nodes['coll-collation'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'collation',
+ label: '{{ _('Collations') }}',
+ type: 'coll-collation'
+ });
+ };
+
+ if (!pgBrowser.Nodes['collation']) {
+ pgAdmin.Browser.Nodes['collation'] = pgBrowser.Node.extend({
+ type: 'collation',
+ label: '{{ _('Collation') }}',
+ collection_type: 'coll-collation',
+ hasSQL: true,
+ parent_type: ['schema', 'catalog'],
+ Init: function() {
+ /* Avoid mulitple registration of menus */
+ if (this.initialized)
+ return;
+
+ this.initialized = true;
+
+ pgBrowser.add_menus([{
+ name: 'create_collation_on_coll', node: 'coll-collation', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Collation...') }}',
+ icon: 'wcTabIcon icon-collation', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_collation', node: 'collation', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Collation...') }}',
+ icon: 'wcTabIcon icon-collation', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_collation', node: 'schema', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Collation...') }}',
+ icon: 'wcTabIcon icon-collation', data: {action: 'create', check: false},
+ enable: 'canCreate'
+ }
+ ]);
+
+ },
+ canDrop: pgBrowser.Nodes['schema'].canChildDrop,
+ canDropCascade: pgBrowser.Nodes['schema'].canChildDrop,
+ model: pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ name: undefined,
+ oid: undefined,
+ owner: undefined,
+ lc_type: undefined,
+ lc_collate: undefined,
+ description: undefined,
+ slony: undefined,
+ },
+ 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']
+ },{
+ id: 'owner', label:'{{ _('Owner') }}', cell: 'string',
+ type: 'text', mode: ['properties', 'create', 'edit'],
+ disabled: 'inSchema'
+ },{
+ id: 'schema', label:'{{ _('Schema') }}', cell: 'string', control: 'node-ajax-options',
+ type: 'text', mode: ['create', 'edit'], url: 'get_schemas',
+ disabled: 'inSchema'
+ },{
+ id: 'locale', label:'{{ _('Locale') }}', cell: 'string',
+ type: 'text', mode: ['create', 'edit'], group: 'Defination', disabled: 'inSchemaWithModelCheck',
+ deps: ['lc_collate', 'lc_type', 'copy_collation']
+ },{
+ id: 'lc_collate', label:'{{ _('LC_COLLATE') }}', cell: 'string',
+ type: 'text', mode: ['properties', 'create', 'edit'], group: 'Defination',
+ deps: ['locale', 'copy_collation'], disabled: 'inSchemaWithModelCheck'
+ },{
+ id: 'lc_type', label:'{{ _('LC_TYPE') }}', cell: 'string',
+ type: 'text', mode: ['properties', 'create', 'edit'], group: 'Defination', disabled: 'inSchemaWithModelCheck',
+ deps: ['locale', 'copy_collation']
+ },{
+ id: 'copy_collation', label:'{{ _('Copy collation') }}', cell: 'string', control: 'node-ajax-options',
+ type: 'text', mode: ['create', 'edit'], group: 'Defination', url: 'get_collations', disabled: 'inSchemaWithModelCheck',
+ deps: ['locale', 'lc_collate', 'lc_type']
+ },{
+ id: 'description', label:'{{ _('Comment') }}', cell: 'string',
+ type: 'multiline', mode: ['properties', 'create', 'edit'],
+ disabled: 'inSchema'
+ },{
+ id: 'slony', label:'{{ _('Slony') }}', cell: 'string', control: 'node-ajax-options',
+ type: 'text', mode: ['create', 'edit'], disabled: true, url: 'get_schemas'
+ }
+ ],
+ validate: function() {
+ // TODO: Add validation here
+ var err = {},
+ msg = undefined,
+ changedAttrs = this.changed,
+ locale_flag = false,
+ lc_type_flag = false,
+ lc_coll_flag = false,
+ copy_coll_flag = false,
+ msg = undefined;
+
+ if (_.has(changedAttrs,this.get('name')) && _.isUndefined(this.get('name'))
+ || String(this.get('name')).replace(/^\s+|\s+$/g, '') == '') {
+ msg = '{{ _('Name can not be empty!') }}';
+ err['name'] = msg;
+ }
+ if (_.has(changedAttrs,this.get('schema')) && _.isUndefined(this.get('schema'))
+ || String(this.get('schema')).replace(/^\s+|\s+$/g, '') == '') {
+ msg = '{{ _('Schema can not be empty!') }}';
+ err['schema'] = msg;
+ }
+ if (_.has(changedAttrs,this.get('locale')) && _.isUndefined(this.get('locale'))
+ || String(this.get('locale')).replace(/^\s+|\s+$/g, '') == '') {
+ locale_flag = true;
+ }
+ if (_.has(changedAttrs,this.get('lc_collate')) && _.isUndefined(this.get('lc_collate'))
+ || String(this.get('lc_collate')).replace(/^\s+|\s+$/g, '') == '') {
+ lc_coll_flag = true;
+ }
+ if (_.has(changedAttrs,this.get('lc_type')) && _.isUndefined(this.get('lc_type'))
+ || String(this.get('lc_type')).replace(/^\s+|\s+$/g, '') == '') {
+ lc_type_flag = true;
+ }
+ if (_.has(changedAttrs,this.get('copy_collation')) && _.isUndefined(this.get('copy_collation'))
+ || String(this.get('copy_collation')).replace(/^\s+|\s+$/g, '') == '') {
+ copy_coll_flag = true;
+ }
+ if (locale_flag && (lc_coll_flag || lc_coll_flag) && copy_coll_flag) {
+ msg = '{{ _('Incomplete defination, Please provide Locale OR Copy collation OR LC_TYPE/LC_COLLATE!') }}';
+ err['locale'] = msg
+ }
+
+ if (_.size(err)) {
+ this.trigger('on-status',{msg:msg,type:"type"});
+ return true;
+ }
+
+ this.trigger('on-status-clear');
+
+ 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)
+ {
+ // Enable copy_collation only if locale & lc_* is not provided
+ if (m.isNew() && this.name == "copy_collation")
+ {
+ if(m.get('locale'))
+ return true;
+ if(m.get('lc_collate') || m.get('lc_type'))
+ return true
+ return false;
+ }
+
+ // Enable lc_* only if copy_collation & locale is not provided
+ if (m.isNew() && (this.name == 'lc_collate' || this.name == 'lc_type'))
+ {
+ if(m.get('locale'))
+ return true;
+ if(m.get('copy_collation'))
+ return true
+ return false;
+ }
+
+ // Enable localy only if lc_* & copy_collation is not provided
+ if (m.isNew() && this.name == 'locale')
+ {
+ if(m.get('lc_collate') || m.get('lc_type'))
+ return true;
+ if(m.get('copy_collation'))
+ return true
+ return false;
+ }
+
+ // We will disbale control if it's in 'edit' mode
+ if (m.isNew()) {
+ return false;
+ } else {
+ return true;
+ }
+
+ }
+ return true;
+ }
+ }),
+ 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 collation
+ if (_.indexOf(['schema'], d._type) > -1)
+ return true;
+
+ if ('coll-collation' == 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['collation'];
+});
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/backend_support.sql
new file mode 100644
index 0000000..00b7fb4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/backend_support.sql
@@ -0,0 +1,18 @@
+SELECT
+ CASE WHEN nsp.nspname IN ('sys', 'dbo', 'information_schema') THEN true ELSE false END AS dbSupport
+FROM pg_namespace nsp
+WHERE nsp.oid={{scid}}::int
+AND (
+ (nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+)
+AND
+ nspname NOT LIKE E'pg\\temp\\%'
+AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/create.sql
new file mode 100644
index 0000000..bfe5ef1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/create.sql
@@ -0,0 +1,21 @@
+{% if data %}
+CREATE COLLATION {{ conn|qtIdent(data.schema, data.name) }}
+{# if user has provided lc_collate & lc_type #}
+{% if data.lc_collate and data.lc_type %}
+ (
+ LC_COLLATE = {{ data.lc_collate|qtLiteral }} ,
+ LC_CTYPE = {{ data.lc_type|qtLiteral }}
+ );
+{% endif %}
+{# if user has provided locale only #}
+{% if data.locale %}
+ (
+ LOCALE = {{ data.locale|qtLiteral }}
+ );
+{% endif %}
+{# if user has choosed to copy from existing collation #}
+{% if data.copy_collation %}
+ FROM {{ data.copy_collation }};
+{% endif %}
+{% endif %}
+
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/delete.sql
new file mode 100644
index 0000000..8b016c4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/delete.sql
@@ -0,0 +1,10 @@
+{% if scid and coid %}
+SELECT concat(nspname, '."', collname, '"') AS name
+FROM pg_collation c, pg_namespace n
+WHERE c.collnamespace = n.oid AND
+ n.oid = {{ scid }}::oid AND
+ c.oid = {{ coid }}::oid;
+{% endif %}
+{% if name %}
+DROP COLLATION {{ name }}{% if cascade%} CASCADE{% endif %};
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/get_collations.sql
new file mode 100644
index 0000000..9de322e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/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(nspname, '."', collname,'"')
+ ELSE '' END AS copy_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/collations/templates/collation/sql/9.1_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/get_oid.sql
new file mode 100644
index 0000000..2144acc
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/get_oid.sql
@@ -0,0 +1,8 @@
+{# Below will provide oid for newly created collation #}
+{% if data %}
+SELECT c.oid
+FROM pg_collation c, pg_namespace n
+WHERE c.collnamespace=n.oid AND
+ n.nspname = {{ data.schema|qtLiteral }} AND
+ c.collname = {{ data.name|qtLiteral }}
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/get_schemas.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/get_schemas.sql
new file mode 100644
index 0000000..0bf35f4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/get_schemas.sql
@@ -0,0 +1,21 @@
+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))
+ )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\\_%' )
+ORDER BY nspname;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/grant.sql
new file mode 100644
index 0000000..711bf3a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/grant.sql
@@ -0,0 +1,10 @@
+{% if data %}
+{% if data.owner %}
+ALTER COLLATION {{ conn|qtIdent(data.schema, data.name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{% if data.description %}
+COMMENT ON COLLATION {{ conn|qtIdent(data.schema, data.name) }}
+ IS '{{ data.description }}';
+{% endif %}
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/properties.sql
new file mode 100644
index 0000000..4850d72
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/properties.sql
@@ -0,0 +1,8 @@
+SELECT c.oid, c.collname AS name, c.collcollate AS lc_collate, c.collctype AS lc_type,
+ pg_get_userbyid(c.collowner) AS owner, description, n.nspname AS schema
+FROM pg_collation c
+ JOIN pg_namespace n ON n.oid=c.collnamespace
+ LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid AND des.classoid='pg_collation'::regclass)
+WHERE c.collnamespace = {{scid}}::oid
+ {% if coid %}AND c.oid = {{coid}}::oid {% endif %}
+ ORDER BY c.collname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/update.sql
new file mode 100644
index 0000000..34a97de
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/9.1_plus/update.sql
@@ -0,0 +1,24 @@
+{% if data %}
+{# Below will change object'w owner #}
+{% if data.owner != o_data.owner %}
+ALTER COLLATION {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{# Below will change object's comment #}
+{% if data.description != o_data.description %}
+COMMENT ON COLLATION {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ IS '{{ data.description }}';
+{% endif %}
+{# Below will change object name #}
+{% if data.name != o_data.name %}
+ALTER COLLATION {{ 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 != o_data.schema %}
+ALTER COLLATION {% 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 %}
+
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/backend_support.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/backend_support.sql
new file mode 100644
index 0000000..00b7fb4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/backend_support.sql
@@ -0,0 +1,18 @@
+SELECT
+ CASE WHEN nsp.nspname IN ('sys', 'dbo', 'information_schema') THEN true ELSE false END AS dbSupport
+FROM pg_namespace nsp
+WHERE nsp.oid={{scid}}::int
+AND (
+ (nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+ OR (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+)
+AND
+ nspname NOT LIKE E'pg\\temp\\%'
+AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/create.sql
new file mode 100644
index 0000000..bfe5ef1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/create.sql
@@ -0,0 +1,21 @@
+{% if data %}
+CREATE COLLATION {{ conn|qtIdent(data.schema, data.name) }}
+{# if user has provided lc_collate & lc_type #}
+{% if data.lc_collate and data.lc_type %}
+ (
+ LC_COLLATE = {{ data.lc_collate|qtLiteral }} ,
+ LC_CTYPE = {{ data.lc_type|qtLiteral }}
+ );
+{% endif %}
+{# if user has provided locale only #}
+{% if data.locale %}
+ (
+ LOCALE = {{ data.locale|qtLiteral }}
+ );
+{% endif %}
+{# if user has choosed to copy from existing collation #}
+{% if data.copy_collation %}
+ FROM {{ data.copy_collation }};
+{% endif %}
+{% endif %}
+
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/delete.sql
new file mode 100644
index 0000000..8b016c4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/delete.sql
@@ -0,0 +1,10 @@
+{% if scid and coid %}
+SELECT concat(nspname, '."', collname, '"') AS name
+FROM pg_collation c, pg_namespace n
+WHERE c.collnamespace = n.oid AND
+ n.oid = {{ scid }}::oid AND
+ c.oid = {{ coid }}::oid;
+{% endif %}
+{% if name %}
+DROP COLLATION {{ name }}{% if cascade%} CASCADE{% endif %};
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/get_collations.sql
new file mode 100644
index 0000000..9de322e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/get_collations.sql
@@ -0,0 +1,7 @@
+SELECT --nspname, collname,
+ CASE WHEN length(nspname) > 0 AND length(collname) > 0 THEN
+ concat(nspname, '."', collname,'"')
+ ELSE '' END AS copy_collation
+FROM pg_collation c, pg_namespace n
+WHERE c.collnamespace=n.oid
+ORDER BY nspname, collname;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/get_oid.sql
new file mode 100644
index 0000000..2144acc
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/get_oid.sql
@@ -0,0 +1,8 @@
+{# Below will provide oid for newly created collation #}
+{% if data %}
+SELECT c.oid
+FROM pg_collation c, pg_namespace n
+WHERE c.collnamespace=n.oid AND
+ n.nspname = {{ data.schema|qtLiteral }} AND
+ c.collname = {{ data.name|qtLiteral }}
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/get_schemas.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/get_schemas.sql
new file mode 100644
index 0000000..f64b68a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/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/collations/templates/collation/sql/edb_9.1_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/grant.sql
new file mode 100644
index 0000000..711bf3a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/grant.sql
@@ -0,0 +1,10 @@
+{% if data %}
+{% if data.owner %}
+ALTER COLLATION {{ conn|qtIdent(data.schema, data.name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{% if data.description %}
+COMMENT ON COLLATION {{ conn|qtIdent(data.schema, data.name) }}
+ IS '{{ data.description }}';
+{% endif %}
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/properties.sql
new file mode 100644
index 0000000..4850d72
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/properties.sql
@@ -0,0 +1,8 @@
+SELECT c.oid, c.collname AS name, c.collcollate AS lc_collate, c.collctype AS lc_type,
+ pg_get_userbyid(c.collowner) AS owner, description, n.nspname AS schema
+FROM pg_collation c
+ JOIN pg_namespace n ON n.oid=c.collnamespace
+ LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid AND des.classoid='pg_collation'::regclass)
+WHERE c.collnamespace = {{scid}}::oid
+ {% if coid %}AND c.oid = {{coid}}::oid {% endif %}
+ ORDER BY c.collname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/update.sql
new file mode 100644
index 0000000..34a97de
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/collations/templates/collation/sql/edb_9.1_plus/update.sql
@@ -0,0 +1,24 @@
+{% if data %}
+{# Below will change object'w owner #}
+{% if data.owner != o_data.owner %}
+ALTER COLLATION {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ OWNER TO {{ data.owner }};
+{% endif %}
+{# Below will change object's comment #}
+{% if data.description != o_data.description %}
+COMMENT ON COLLATION {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ IS '{{ data.description }}';
+{% endif %}
+{# Below will change object name #}
+{% if data.name != o_data.name %}
+ALTER COLLATION {{ 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 != o_data.schema %}
+ALTER COLLATION {% 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 %}
+
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/static/img/catalog.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/static/img/catalog.png
new file mode 100644
index 0000000000000000000000000000000000000000..7c0ef24ff5843867fb64d97cc419a7150da5f441
GIT binary patch
literal 387
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbL!eSlAhE0A74<wm*Z-rc?bcXt2Z
z(fNN{>wh5F-1vWE-M=SKm;e3mx31#fzyGsVm;76i_it&|zr|_)79{<fpYU%^)W1Ea
z-**6wU@Qsp3ubV5b|VeMN%D4gVHZp6cn#!m7I;J!18EO1b~~AE2V~@Vx;TbNT#oJM
z6lzf5aDBKvBPsIQ-}AFx7)0?b-q)Ywa#bQhs>jfG@u3-kbFB3rR7tE)c>e0@4eiyB
zLKAaZn>`A?dUi-nuND6*>}cWk(81HwR&}e=a#7p0XRqFhtDUf>`Dx)5>7(|iZO+%n
zJ{F&HpV>2Gt>-78&8j7?5hW>!C8<`)MX5lF!N|bSK-bVn*T68u$iT|P*viC2+rZSy
vz`$arsvL@j-29Zxv`UBu152<5kZLOfGl+&$(?iz)H86O(`njxgN@xNA6A6=n
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/static/img/coll-catalog.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/static/img/coll-catalog.png
new file mode 100644
index 0000000000000000000000000000000000000000..caa6d3d40d55d990351465cebacbffde95eeb63d
GIT binary patch
literal 371
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbL!aez;VE0A74<wm*Z-rc?bcXa;W
z+Wa2~`Z7*ztozp=cVk_}zm*05{{Hv-_kY&XtbYrV{>_W~_vF9f0-!d=k|4ie28U-i
z(m<RfZ+92A#Izl?Kn`btM`SUO_5fqIli7AaMwX|GV~E7%(0*>d1_d7H%+Qbz<^L~7
z1qs|Sn)G$;X%0t5r5;8<d)7HqC4RLAZVltTbH)AL+2c$4Ry|`jW?g;0E8wGSp#GjM
zKl*~bl47;uXP%g4prU_rx@_B?sUH;EYHn>ckg4n7vtRmMKl%{UE#@beKLBl2Epd$~
zNl7e8wMs5Z1yT$~28IT@hDN#uh9O1<Rwl+)CMMbjrd9?97Bf}lP&DM`r(~v8LNpjy
df;E6tTN#)^G@P0qx(29$!PC{xWt~$(697gUhCKiP
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/static/img/coll-schema.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/static/img/coll-schema.png
new file mode 100644
index 0000000000000000000000000000000000000000..9733882b720b39bfed266843f84e1278c9bc588f
GIT binary patch
literal 374
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!73?$#)eFPHF4e$wZ1=2UBOxf${`M<aKe@Dmv
z=H~zZ|DVXn_+MLlBQEZLS=s;m{Qp31Lc;%;7|~9dUZ4iXk|4ie28U-i(m<RfZ+92A
z#Izl?Kn`btM`SUO_5fqIli7AaMzN=hV+hA}-M&E41_KV)g$I`$ys*%Hxx8CMi>pGH
zU{3;zvcy3SwTZqD+l$Y)vNKF*j=J$Hf58;3LkbJ^d(5uZnwoKWUzc38`RSUQj73r|
zZ23m=S+P$%Q$DHn*Svh{6E-*f_92al^NVJ`+r2V#$Df7&rS+Sxi6&jF{agpMUbVzE
zq9i4;B-JXpC>2OC7#SEE=o%X78W@Hc8CaPZTbY<@8<<)d7|eU8w+cl=ZhlH;S|vn-
gfhAZ2P_>Dbff+=@sp+9>fEpM)UHx3vIVCg!0CBE_(*OVf
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/static/img/schema.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/static/img/schema.png
new file mode 100644
index 0000000000000000000000000000000000000000..4dacf1856fe53575aa1ee93bc146fd2a37b32650
GIT binary patch
literal 376
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbL!S%6Q7E0Df1Wy)Sp&;Px>|GT>W
zcXa%3Y55NX4GsTmYW|m(|1U27pPTzXGxL9H>i>j<|8a5uBO`y?dmIGnWGo5t3ubV5
zb|VeMN%D4gVHZp6cn#!m7I;J!18EO1b~~AE2V~@Ux;TbNT#ohc7HUx7XpYSMaq9p7
zn=I!qKe8xwzM-%wp&^sIt=Rgo#f8b@b_dIRY8v-$&wj8VV&(##$L}IS_t<hSD(!Ii
z&%trAL!!6ig>L57C59^J7gnB{q8L4G;;Lh>^1eErKX0`5@AbJqSl#`ZmagB%I2CBG
zYKdz^NlIc#s#S7PDv)9@GB7mIH8j#SFbpv=ure{WGBMRQFtsu;nD<U^6^e%3{FKbJ
jN{9vnORxr@Y7;91Gl+&$(?iz)H86O(`njxgN@xNAXB&$`
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/css/catalog.css b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/css/catalog.css
new file mode 100644
index 0000000..9ba6703
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/css/catalog.css
@@ -0,0 +1,9 @@
+.pg-icon-catalog {
+ background-image: url('{{ url_for('NODE-catalog.static', filename='img/catalog.png') }}') !important;
+ border-radius: 10px;
+ background-repeat: no-repeat;
+ align-content: center;
+ vertical-align: middle;
+ height: 1.3em;
+}
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/js/catalogs.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/js/catalogs.js
new file mode 100644
index 0000000..ef50559
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/js/catalogs.js
@@ -0,0 +1,112 @@
+define(
+ ['jquery', 'underscore', 'underscore.string', 'pgadmin', 'pgadmin.browser', 'alertify', 'pgadmin.browser.collection'],
+function($, _, S, pgAdmin, pgBrowser, alertify) {
+
+ var SecurityLabelModel = pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ provider: undefined,
+ securitylabel: undefined
+ },
+ initialize: function(val, opts) {
+ pgBrowser.Node.Model.prototype.initialize(arguments);
+ if (typeof val === 'string'){
+ var idx = val.indexOf('=');
+ this.set('provider',idx > 0 ? val.substring(0, idx):'', {silent:true});
+ this.set('securitylabel',idx > 0 ? val.substring(idx+1):'', {silent:true});
+ }
+ },
+ schema: [
+ {id: 'provider', label:'Provider', type:'text', group: null},
+ {id: 'securitylabel', label:'Security Label', type: 'text', group:null},
+ ],
+ validate: function() {
+ // TODO: Add validation here
+ },
+ toJSON: Backbone.Model.prototype.toJSON
+ });
+
+ if (!pgBrowser.Nodes['coll-catalog']) {
+ var databases = pgAdmin.Browser.Nodes['coll-catalog'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'catalog',
+ label: '{{ _('Catalogs') }}',
+ type: 'coll-catalog'
+ });
+ };
+
+ if (!pgBrowser.Nodes['catalog']) {
+ pgAdmin.Browser.Nodes['catalog'] = pgAdmin.Browser.Node.extend({
+ parent_type: 'database',
+ type: 'catalog',
+ label: '{{ _('Catalog') }}',
+ hasSQL: true,
+ Init: function() {
+ /* Avoid mulitple registration of menus */
+ if (this.initialized)
+ return;
+
+ this.initialized = true;
+
+ },
+ model: pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ name: undefined,
+ namespaceowner: undefined,
+ nspacl: undefined,
+ description: undefined,
+ acltbl: undefined,
+ aclseq: undefined,
+ aclfunc: undefined,
+ acltype: undefined,
+ slony: undefined,
+ securitylabel: []
+ },
+ schema: [{
+ id: 'name', label: '{{ _('Name') }}', cell: 'string',
+ type: 'text', disabled: true
+ },{
+ id: 'oid', label:'{{ _('Oid') }}', cell: 'string',
+ type: 'text', disabled: true
+ },{
+ id: 'namespaceowner', label:'{{ _('Owner') }}', cell: 'string',
+ type: 'text', disabled: true
+ },{
+ id: 'nspacl', label:'{{ _('ACL') }}', cell: 'string',
+ type: 'text', mode: ['properties']
+ },{
+ id: 'acltbl', label:'{{ _('Default table ACL') }}', cell: 'string',
+ type: 'text', mode: ['properties']
+ },{
+ id: 'aclseq', label:'{{ _('Default sequence ACL') }}', cell: 'string',
+ type: 'text', mode: ['properties']
+ },{
+ id: 'aclfunc', label:'{{ _('Default function ACL') }}', cell: 'string',
+ type: 'text', mode: ['properties']
+ },{
+ id: 'acltype', label:'{{ _('Default type ACL') }}', cell: 'string',
+ type: 'text', mode: ['properties']
+ },{
+ id: 'description', label:'{{ _('Comment') }}', cell: 'string',
+ type: 'multiline'
+ },{
+ id: 'slony', label:'{{ _('Use Slony') }}', cell: 'string',
+ type: 'text', disabled: true, mode: ['edit']
+ },{
+ id: 'securitylabels', label: 'Security Labels', type: 'collection', group: "Security Labels",
+ model: SecurityLabelModel, control: 'unique-col-collection', mode: ['edit'],
+ canAdd: true, canDelete: true, uniqueCol : ['provider'],
+ columns: ['provider','securitylabel']
+ }
+ ],
+ validate: function() {
+ // TODO: Add validation here
+ return null;
+
+ }
+ })
+ });
+
+ }
+
+ return pgBrowser.Nodes['coll-catalog'];
+});
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/9.1_plus/properties.sql
new file mode 100644
index 0000000..1f71161
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/9.1_plus/properties.sql
@@ -0,0 +1,28 @@
+SELECT nsp.nspname AS name, nsp.oid AS oid, pg_get_userbyid(nsp.nspowner) AS namespaceowner, nsp.nspacl AS nspacl,
+ des.description,
+ CASE WHEN dacl.defaclobjtype='r' THEN dacl.defaclacl END AS acltbl,
+ CASE WHEN dacl.defaclobjtype='S' THEN dacl.defaclacl END AS aclseq,
+ CASE WHEN dacl.defaclobjtype='f' THEN dacl.defaclacl END AS aclfunc
+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 (
+(nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+OR (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+OR (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+)
+AND
+ nspname NOT LIKE E'pg\\temp\\%'
+AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
+{% if cid %}
+AND
+ nsp.oid={{cid}}::int
+{% endif %}
+ORDER BY nsp.nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/9.1_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/9.1_plus/update.sql
new file mode 100644
index 0000000..6869714
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/9.1_plus/update.sql
@@ -0,0 +1,29 @@
+{% if data %}
+{# ============= Below SQL will update SCHEMA comments ============= #}
+{% if data.description and data.description != o_data.description %}
+COMMENT ON SCHEMA "{{ o_data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# ============= Below SQL will update SCHEMA securitylabel ============= #}
+{% if data.securitylabels and data.securitylabels.added %}
+{% for label in data.securitylabels.added %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{% endif %}
+{% if data.securitylabels and data.securitylabels.updated %}
+{% for label in data.securitylabels.updated %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{% endif %}
+{% if data.securitylabels and data.securitylabels.deleted %}
+{% for label in data.securitylabels.deleted %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS NULL;
+{% endfor %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/9.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/9.2_plus/properties.sql
new file mode 100644
index 0000000..9e8e08a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/9.2_plus/properties.sql
@@ -0,0 +1,29 @@
+SELECT nsp.nspname AS name, nsp.oid AS oid, pg_get_userbyid(nsp.nspowner) AS namespaceowner, nsp.nspacl AS nspacl,
+ des.description,
+ CASE WHEN dacl.defaclobjtype='r' THEN dacl.defaclacl END AS acltbl,
+ CASE WHEN dacl.defaclobjtype='S' THEN dacl.defaclacl END AS aclseq,
+ CASE WHEN dacl.defaclobjtype='f' THEN dacl.defaclacl END AS aclfunc,
+ CASE WHEN dacl.defaclobjtype='T' THEN dacl.defaclacl END AS acltype
+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 (
+(nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+OR (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+OR (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+)
+AND
+ nspname NOT LIKE E'pg\\temp\\%'
+AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
+{% if cid %}
+AND
+ nsp.oid={{cid}}::int
+{% endif %}
+ORDER BY nsp.nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/9.2_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/9.2_plus/update.sql
new file mode 100644
index 0000000..6869714
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/9.2_plus/update.sql
@@ -0,0 +1,29 @@
+{% if data %}
+{# ============= Below SQL will update SCHEMA comments ============= #}
+{% if data.description and data.description != o_data.description %}
+COMMENT ON SCHEMA "{{ o_data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# ============= Below SQL will update SCHEMA securitylabel ============= #}
+{% if data.securitylabels and data.securitylabels.added %}
+{% for label in data.securitylabels.added %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{% endif %}
+{% if data.securitylabels and data.securitylabels.updated %}
+{% for label in data.securitylabels.updated %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{% endif %}
+{% if data.securitylabels and data.securitylabels.deleted %}
+{% for label in data.securitylabels.deleted %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS NULL;
+{% endfor %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_9.1_plus/properties.sql
new file mode 100644
index 0000000..2023047
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_9.1_plus/properties.sql
@@ -0,0 +1,32 @@
+SELECT nsp.nspname AS name, nsp.oid AS oid, pg_get_userbyid(nsp.nspowner) AS namespaceowner, nsp.nspacl AS nspacl,
+ des.description,
+ CASE WHEN dacl.defaclobjtype='r' THEN dacl.defaclacl END AS acltbl,
+ CASE WHEN dacl.defaclobjtype='S' THEN dacl.defaclacl END AS aclseq,
+ CASE WHEN dacl.defaclobjtype='f' THEN dacl.defaclacl END AS aclfunc
+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 (
+(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
+ nsp.nspparent = 0
+{% if cid %}
+AND
+ nsp.oid={{cid}}::int
+{% endif %}
+ORDER BY nsp.nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_9.1_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_9.1_plus/update.sql
new file mode 100644
index 0000000..6869714
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_9.1_plus/update.sql
@@ -0,0 +1,29 @@
+{% if data %}
+{# ============= Below SQL will update SCHEMA comments ============= #}
+{% if data.description and data.description != o_data.description %}
+COMMENT ON SCHEMA "{{ o_data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# ============= Below SQL will update SCHEMA securitylabel ============= #}
+{% if data.securitylabels and data.securitylabels.added %}
+{% for label in data.securitylabels.added %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{% endif %}
+{% if data.securitylabels and data.securitylabels.updated %}
+{% for label in data.securitylabels.updated %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{% endif %}
+{% if data.securitylabels and data.securitylabels.deleted %}
+{% for label in data.securitylabels.deleted %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS NULL;
+{% endfor %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_9.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_9.2_plus/properties.sql
new file mode 100644
index 0000000..ab7b163
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_9.2_plus/properties.sql
@@ -0,0 +1,33 @@
+SELECT nsp.nspname AS name, nsp.oid AS oid, pg_get_userbyid(nsp.nspowner) AS namespaceowner, nsp.nspacl AS nspacl,
+ des.description,
+ CASE WHEN dacl.defaclobjtype='r' THEN dacl.defaclacl END AS acltbl,
+ CASE WHEN dacl.defaclobjtype='S' THEN dacl.defaclacl END AS aclseq,
+ CASE WHEN dacl.defaclobjtype='f' THEN dacl.defaclacl END AS aclfunc,
+ CASE WHEN dacl.defaclobjtype='T' THEN dacl.defaclacl END AS acltype
+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 (
+(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
+ nsp.nspparent = 0
+{% if cid %}
+AND
+ nsp.oid={{cid}}::int
+{% endif %}
+ORDER BY nsp.nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_9.2_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_9.2_plus/update.sql
new file mode 100644
index 0000000..6869714
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_9.2_plus/update.sql
@@ -0,0 +1,29 @@
+{% if data %}
+{# ============= Below SQL will update SCHEMA comments ============= #}
+{% if data.description and data.description != o_data.description %}
+COMMENT ON SCHEMA "{{ o_data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# ============= Below SQL will update SCHEMA securitylabel ============= #}
+{% if data.securitylabels and data.securitylabels.added %}
+{% for label in data.securitylabels.added %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{% endif %}
+{% if data.securitylabels and data.securitylabels.updated %}
+{% for label in data.securitylabels.updated %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{% endif %}
+{% if data.securitylabels and data.securitylabels.deleted %}
+{% for label in data.securitylabels.deleted %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS NULL;
+{% endfor %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_pre_9.1/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_pre_9.1/properties.sql
new file mode 100644
index 0000000..2023047
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_pre_9.1/properties.sql
@@ -0,0 +1,32 @@
+SELECT nsp.nspname AS name, nsp.oid AS oid, pg_get_userbyid(nsp.nspowner) AS namespaceowner, nsp.nspacl AS nspacl,
+ des.description,
+ CASE WHEN dacl.defaclobjtype='r' THEN dacl.defaclacl END AS acltbl,
+ CASE WHEN dacl.defaclobjtype='S' THEN dacl.defaclacl END AS aclseq,
+ CASE WHEN dacl.defaclobjtype='f' THEN dacl.defaclacl END AS aclfunc
+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 (
+(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
+ nsp.nspparent = 0
+{% if cid %}
+AND
+ nsp.oid={{cid}}::int
+{% endif %}
+ORDER BY nsp.nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_pre_9.1/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_pre_9.1/update.sql
new file mode 100644
index 0000000..d3021ba
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/edb_pre_9.1/update.sql
@@ -0,0 +1,8 @@
+{% if data %}
+{# ============= Below SQL will update SCHEMA comments ============= #}
+{% if data.description and data.description != o_data.description %}
+COMMENT ON SCHEMA "{{ o_data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# End main if#}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/pre_9.1/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/pre_9.1/properties.sql
new file mode 100644
index 0000000..1f71161
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/pre_9.1/properties.sql
@@ -0,0 +1,28 @@
+SELECT nsp.nspname AS name, nsp.oid AS oid, pg_get_userbyid(nsp.nspowner) AS namespaceowner, nsp.nspacl AS nspacl,
+ des.description,
+ CASE WHEN dacl.defaclobjtype='r' THEN dacl.defaclacl END AS acltbl,
+ CASE WHEN dacl.defaclobjtype='S' THEN dacl.defaclacl END AS aclseq,
+ CASE WHEN dacl.defaclobjtype='f' THEN dacl.defaclacl END AS aclfunc
+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 (
+(nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1))
+OR (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1))
+OR (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1))
+OR (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+)
+AND
+ nspname NOT LIKE E'pg\\temp\\%'
+AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
+{% if cid %}
+AND
+ nsp.oid={{cid}}::int
+{% endif %}
+ORDER BY nsp.nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/pre_9.1/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/pre_9.1/update.sql
new file mode 100644
index 0000000..d3021ba
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalogs/sql/pre_9.1/update.sql
@@ -0,0 +1,8 @@
+{% if data %}
+{# ============= Below SQL will update SCHEMA comments ============= #}
+{% if data.description and data.description != o_data.description %}
+COMMENT ON SCHEMA "{{ o_data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# End main if#}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/css/schema.css b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/css/schema.css
new file mode 100644
index 0000000..f921d30
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/css/schema.css
@@ -0,0 +1,9 @@
+.pg-icon-schema {
+ background-image: url('{{ url_for('NODE-schema.static', filename='img/schema.png') }}') !important;
+ border-radius: 10px;
+ background-repeat: no-repeat;
+ align-content: center;
+ vertical-align: middle;
+ height: 1.3em;
+}
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/js/schemas.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/js/schemas.js
new file mode 100644
index 0000000..73848b1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/js/schemas.js
@@ -0,0 +1,162 @@
+define(
+ ['jquery', 'underscore', 'underscore.string', 'pgadmin', 'pgadmin.browser', 'alertify', 'pgadmin.browser.collection'],
+function($, _, S, pgAdmin, pgBrowser, alertify) {
+
+ var SecurityLabelModel = pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ provider: undefined,
+ securitylabel: undefined
+ },
+ initialize: function(val, opts) {
+ pgBrowser.Node.Model.prototype.initialize(arguments);
+ if (typeof val === 'string'){
+ var idx = val.indexOf('=');
+ this.set('provider',idx > 0 ? val.substring(0, idx):'', {silent:true});
+ this.set('securitylabel',idx > 0 ? val.substring(idx+1):'', {silent:true});
+ }
+ },
+ schema: [
+ {id: 'provider', label:'Provider', type:'text', group: null},
+ {id: 'securitylabel', label:'Security Label', type: 'text', group:null},
+ ],
+ validate: function() {
+ // TODO: Add validation here
+ },
+ toJSON: Backbone.Model.prototype.toJSON
+ });
+
+ if (!pgBrowser.Nodes['coll-schema']) {
+ var databases = pgAdmin.Browser.Nodes['coll-schema'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'schema',
+ label: '{{ _('Schemas') }}',
+ type: 'coll-schema'
+ });
+ };
+
+ if (!pgBrowser.Nodes['schema']) {
+ pgAdmin.Browser.Nodes['schema'] = pgAdmin.Browser.Node.extend({
+ parent_type: 'database',
+ type: 'schema',
+ label: '{{ _('Schema') }}',
+ hasSQL: true,
+ Init: function() {
+ /* Avoid mulitple registration of menus */
+ if (this.initialized)
+ return;
+
+ this.initialized = true;
+
+ pgBrowser.add_menus([{
+ name: 'create_schema_on_coll', node: 'coll-schema', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Schema...') }}',
+ icon: 'wcTabIcon icon-schema', data: {action: 'create'}
+ },{
+ name: 'create_schema', node: 'schema', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Schema...') }}',
+ icon: 'wcTabIcon icon-schema', data: {action: 'create'}
+ },{
+ name: 'drop_schema', node: 'schema', module: this,
+ applies: ['object', 'context'], callback: 'delete_obj',
+ category: 'drop', priority: 3, label: '{{ _('Drop/Delete Schema...') }}',
+ icon: 'fa fa-trash'
+ },{
+ name: 'drop_cascade_schema', node: 'schema', module: this,
+ applies: ['object', 'context'], callback: 'delete_cascaded_obj',
+ category: 'drop', priority: 3, label: '{{ _('Drop cascade...') }}',
+ icon: 'fa fa-trash'
+ }
+ ]);
+ },
+ model: pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ name: undefined,
+ namespaceowner: undefined,
+ nspacl: undefined,
+ description: undefined,
+ acltbl: undefined,
+ aclseq: undefined,
+ aclfunc: undefined,
+ acltype: undefined,
+ slony: undefined,
+ is_system_obj: undefined,
+ securitylabel: []
+ },
+ schema: [{
+ id: 'name', label: '{{ _('Name') }}', cell: 'string',
+ type: 'text'
+ },{
+ id: 'oid', label:'{{ _('Oid') }}', cell: 'string',
+ type: 'text', disabled: true
+ },{
+ id: 'namespaceowner', label:'{{ _('Owner') }}', cell: 'string',
+ type: 'text'
+ },{
+ id: 'nspacl', label:'{{ _('ACL') }}', cell: 'string',
+ type: 'text', mode: ['properties']
+ },{
+ id: 'acltbl', label:'{{ _('Default table ACL') }}', cell: 'string',
+ type: 'text', mode: ['properties']
+ },{
+ id: 'aclseq', label:'{{ _('Default sequence ACL') }}', cell: 'string',
+ type: 'text', mode: ['properties']
+ },{
+ id: 'aclfunc', label:'{{ _('Default function ACL') }}', cell: 'string',
+ type: 'text', mode: ['properties']
+ },{
+ id: 'acltype', label:'{{ _('Default type ACL') }}', cell: 'string',
+ type: 'text', mode: ['properties']
+ },{
+ id: 'is_sys_object', label:'{{ _('System Schema?') }}', cell: 'string',
+ type: 'switch', mode: ['properties']
+ },{
+ id: 'description', label:'{{ _('Comment') }}', cell: 'string',
+ type: 'multiline'
+ },{
+ id: 'slony', label:'{{ _('Use Slony') }}', cell: 'string',
+ type: 'text', mode: ['edit']
+ },{
+ id: 'securitylabels', label: 'Security Labels', type: 'collection', group: "Security Labels",
+ model: SecurityLabelModel, control: 'unique-col-collection', mode: ['edit', 'create'],
+ canAdd: true, canDelete: true, uniqueCol : ['provider'],
+ columns: ['provider','securitylabel']
+ }
+ ],
+ validate: function() {
+ // TODO: Add validation here
+ return null;
+
+ }
+ }),
+ canChildDrop: function(itemData, item) {
+ 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 collation
+ if (_.indexOf(['schema'], d._type) > -1)
+ return true;
+
+ if (this.collection_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['coll-schema'];
+});
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.1_plus/create.sql
new file mode 100644
index 0000000..db52fec
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.1_plus/create.sql
@@ -0,0 +1,7 @@
+{% if data %}
+CREATE SCHEMA "{{ data.name }}"
+{% if data.namespaceowner %}
+ AUTHORIZATION {{ data.namespaceowner }}
+{% endif %}
+{% endif %}
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.1_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.1_plus/delete.sql
new file mode 100644
index 0000000..94bbcb1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.1_plus/delete.sql
@@ -0,0 +1,8 @@
+{% if scid %}
+SELECT nsp.nspname as name, nsp.oid
+FROM pg_namespace nsp
+WHERE nsp.oid = {{scid}};
+{% endif %}
+{% if name %}
+DROP SCHEMA {{ name }} {% if cascade %}CASCADE{%endif%};
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.1_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.1_plus/grant.sql
new file mode 100644
index 0000000..1197fa8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.1_plus/grant.sql
@@ -0,0 +1,30 @@
+{% if data %}
+{# ======== Below create SQL for description ======== #}
+{% if data.description %}
+COMMENT ON SCHEMA "{{ data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# ======== Below create SQL for securitylabel tab ======== #}
+{% for label in data.securitylabels %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{# ======== Below create SQL for priviledge tab ======== #}
+{% for privilege in data.privileges %}
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ======== Below SQL will fetch id for given dataspace ======== #}
+{% if schema %}
+SELECT nsp.oid FROM pg_namespace nsp WHERE nsp.nspname = '{{schema}}'
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.1_plus/properties.sql
new file mode 100644
index 0000000..a6abfdc
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.1_plus/properties.sql
@@ -0,0 +1,35 @@
+SELECT
+ CASE
+ WHEN (nspname LIKE E'pg\\\\_temp\\\\_%') THEN 1
+ WHEN (nspname LIKE E'pg\\\\_%') THEN 0
+ ELSE 3 END
+ AS nsptyp,
+ nsp.nspname as name, nsp.oid,
+ pg_get_userbyid(nspowner) AS namespaceowner, nspacl, description,
+ has_schema_privilege(nsp.oid, 'CREATE') as cancreate,
+ CASE WHEN nspname LIKE E'pg\\_%' THEN true ELSE false END as is_sys_object,
+ CASE WHEN dacl.defaclobjtype='r' THEN dacl.defaclacl END AS acltbl,
+ CASE WHEN dacl.defaclobjtype='S' THEN dacl.defaclacl END AS aclseq,
+ CASE WHEN dacl.defaclobjtype='f' THEN dacl.defaclacl END AS aclfunc,
+ (SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=nsp.oid) AS labels,
+ (SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=nsp.oid) AS providers
+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))
+ )AND
+ {% if scid %}
+ nsp.oid={{scid}}::int AND
+ {% endif %}
+ nspname NOT LIKE E'pg\\temp\\%' AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
+ORDER BY 1, nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.1_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.1_plus/update.sql
new file mode 100644
index 0000000..9afd4ed
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.1_plus/update.sql
@@ -0,0 +1,80 @@
+{% if data %}
+{# ============= Below SQL will update SCHEMA comments ============= #}
+{% if data.description and data.description != o_data.description %}
+COMMENT ON SCHEMA "{{ o_data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# ============= Below SQL will update SCHEMA securitylabel ============= #}
+{% if data.securitylabels and data.securitylabels.added %}
+{% for label in data.securitylabels.added %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{% endif %}
+{% if data.securitylabels and data.securitylabels.updated %}
+{% for label in data.securitylabels.updated %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{% endif %}
+{% if data.securitylabels and data.securitylabels.deleted %}
+{% for label in data.securitylabels.deleted %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS NULL;
+{% endfor %}
+{% endif %}
+{# ============= Below SQL will update SCHEMA priviledges ============= #}
+{# ===== Newly added Priviledges ===== #}
+{% if data.privileges and data.privileges.added %}
+{% for privilege in data.privileges.added %}
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ o_data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ===== Updated Priviledges ===== #}
+{% if data.privileges and data.privileges.updated %}
+{% for privilege in data.privileges.updated %}
+REVOKE ALL ON SCHEMA "{{ o_data.name }}"
+ FROM {{ data.role }};
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ o_data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ===== Deleted Priviledges ===== #}
+{% if data.privileges and data.privileges.deleted %}
+{% for privilege in data.privileges.deleted %}
+REVOKE ALL ON SCHEMA "{{ o_data.name }}"
+ FROM {{ data.role }};
+{% endfor %}
+{% endif %}
+{# ============= Below SQL will update SCHEMA user ============= #}
+{% if data.namespaceowner and data.namespaceowner != o_data.namespaceowner %}
+ALTER SCHEMA "{{ o_data.name }}"
+ AUTHORIZATION TO {{ data.namespaceowner }};
+{% endif %}
+{# ============= Below SQL will update SCHEMA name ============= #}
+{% if data.name and data.name != o_data.name %}
+ALTER SCHEMA "{{ o_data.name }}"
+ RENAME TO "{{ data.name }}";
+{% endif %}
+{# ============= End main if data ============= #}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.2_plus/create.sql
new file mode 100644
index 0000000..db52fec
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.2_plus/create.sql
@@ -0,0 +1,7 @@
+{% if data %}
+CREATE SCHEMA "{{ data.name }}"
+{% if data.namespaceowner %}
+ AUTHORIZATION {{ data.namespaceowner }}
+{% endif %}
+{% endif %}
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.2_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.2_plus/delete.sql
new file mode 100644
index 0000000..94bbcb1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.2_plus/delete.sql
@@ -0,0 +1,8 @@
+{% if scid %}
+SELECT nsp.nspname as name, nsp.oid
+FROM pg_namespace nsp
+WHERE nsp.oid = {{scid}};
+{% endif %}
+{% if name %}
+DROP SCHEMA {{ name }} {% if cascade %}CASCADE{%endif%};
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.2_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.2_plus/grant.sql
new file mode 100644
index 0000000..1197fa8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.2_plus/grant.sql
@@ -0,0 +1,30 @@
+{% if data %}
+{# ======== Below create SQL for description ======== #}
+{% if data.description %}
+COMMENT ON SCHEMA "{{ data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# ======== Below create SQL for securitylabel tab ======== #}
+{% for label in data.securitylabels %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{# ======== Below create SQL for priviledge tab ======== #}
+{% for privilege in data.privileges %}
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ======== Below SQL will fetch id for given dataspace ======== #}
+{% if schema %}
+SELECT nsp.oid FROM pg_namespace nsp WHERE nsp.nspname = '{{schema}}'
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.2_plus/properties.sql
new file mode 100644
index 0000000..2fe2d9c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.2_plus/properties.sql
@@ -0,0 +1,36 @@
+SELECT
+ CASE
+ WHEN (nspname LIKE E'pg\\\\_temp\\\\_%') THEN 1
+ WHEN (nspname LIKE E'pg\\\\_%') THEN 0
+ ELSE 3 END
+ AS nsptyp,
+ nsp.nspname as name, nsp.oid,
+ pg_get_userbyid(nspowner) AS namespaceowner, nspacl, description,
+ has_schema_privilege(nsp.oid, 'CREATE') as cancreate,
+ CASE WHEN nspname LIKE E'pg\\_%' THEN true ELSE false END as is_sys_object,
+ CASE WHEN dacl.defaclobjtype='r' THEN dacl.defaclacl END AS acltbl,
+ CASE WHEN dacl.defaclobjtype='S' THEN dacl.defaclacl END AS aclseq,
+ CASE WHEN dacl.defaclobjtype='f' THEN dacl.defaclacl END AS aclfunc,
+ CASE WHEN dacl.defaclobjtype='T' THEN dacl.defaclacl END AS acltype,
+ (SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=nsp.oid) AS labels,
+ (SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=nsp.oid) AS providers
+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))
+ )AND
+ {% if scid %}
+ nsp.oid={{scid}}::int AND
+ {% endif %}
+ nspname NOT LIKE E'pg\\temp\\%' AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
+ORDER BY 1, nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.2_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.2_plus/update.sql
new file mode 100644
index 0000000..9afd4ed
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/9.2_plus/update.sql
@@ -0,0 +1,80 @@
+{% if data %}
+{# ============= Below SQL will update SCHEMA comments ============= #}
+{% if data.description and data.description != o_data.description %}
+COMMENT ON SCHEMA "{{ o_data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# ============= Below SQL will update SCHEMA securitylabel ============= #}
+{% if data.securitylabels and data.securitylabels.added %}
+{% for label in data.securitylabels.added %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{% endif %}
+{% if data.securitylabels and data.securitylabels.updated %}
+{% for label in data.securitylabels.updated %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{% endif %}
+{% if data.securitylabels and data.securitylabels.deleted %}
+{% for label in data.securitylabels.deleted %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS NULL;
+{% endfor %}
+{% endif %}
+{# ============= Below SQL will update SCHEMA priviledges ============= #}
+{# ===== Newly added Priviledges ===== #}
+{% if data.privileges and data.privileges.added %}
+{% for privilege in data.privileges.added %}
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ o_data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ===== Updated Priviledges ===== #}
+{% if data.privileges and data.privileges.updated %}
+{% for privilege in data.privileges.updated %}
+REVOKE ALL ON SCHEMA "{{ o_data.name }}"
+ FROM {{ data.role }};
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ o_data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ===== Deleted Priviledges ===== #}
+{% if data.privileges and data.privileges.deleted %}
+{% for privilege in data.privileges.deleted %}
+REVOKE ALL ON SCHEMA "{{ o_data.name }}"
+ FROM {{ data.role }};
+{% endfor %}
+{% endif %}
+{# ============= Below SQL will update SCHEMA user ============= #}
+{% if data.namespaceowner and data.namespaceowner != o_data.namespaceowner %}
+ALTER SCHEMA "{{ o_data.name }}"
+ AUTHORIZATION TO {{ data.namespaceowner }};
+{% endif %}
+{# ============= Below SQL will update SCHEMA name ============= #}
+{% if data.name and data.name != o_data.name %}
+ALTER SCHEMA "{{ o_data.name }}"
+ RENAME TO "{{ data.name }}";
+{% endif %}
+{# ============= End main if data ============= #}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.1_plus/create.sql
new file mode 100644
index 0000000..db52fec
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.1_plus/create.sql
@@ -0,0 +1,7 @@
+{% if data %}
+CREATE SCHEMA "{{ data.name }}"
+{% if data.namespaceowner %}
+ AUTHORIZATION {{ data.namespaceowner }}
+{% endif %}
+{% endif %}
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.1_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.1_plus/delete.sql
new file mode 100644
index 0000000..94bbcb1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.1_plus/delete.sql
@@ -0,0 +1,8 @@
+{% if scid %}
+SELECT nsp.nspname as name, nsp.oid
+FROM pg_namespace nsp
+WHERE nsp.oid = {{scid}};
+{% endif %}
+{% if name %}
+DROP SCHEMA {{ name }} {% if cascade %}CASCADE{%endif%};
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.1_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.1_plus/grant.sql
new file mode 100644
index 0000000..1197fa8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.1_plus/grant.sql
@@ -0,0 +1,30 @@
+{% if data %}
+{# ======== Below create SQL for description ======== #}
+{% if data.description %}
+COMMENT ON SCHEMA "{{ data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# ======== Below create SQL for securitylabel tab ======== #}
+{% for label in data.securitylabels %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{# ======== Below create SQL for priviledge tab ======== #}
+{% for privilege in data.privileges %}
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ======== Below SQL will fetch id for given dataspace ======== #}
+{% if schema %}
+SELECT nsp.oid FROM pg_namespace nsp WHERE nsp.nspname = '{{schema}}'
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.1_plus/properties.sql
new file mode 100644
index 0000000..96130c4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.1_plus/properties.sql
@@ -0,0 +1,38 @@
+SELECT
+ CASE
+ WHEN (nspname LIKE E'pg\\\\_temp\\\\_%') THEN 1
+ WHEN (nspname LIKE E'pg\\\\_%') THEN 0
+ ELSE 3 END
+ AS nsptyp,
+ nsp.nspname as name, nsp.oid,
+ pg_get_userbyid(nspowner) AS namespaceowner, nspacl, description,
+ has_schema_privilege(nsp.oid, 'CREATE') as cancreate,
+ CASE WHEN nspname LIKE E'pg\\_%' THEN true ELSE false END as is_sys_object,
+ CASE WHEN dacl.defaclobjtype='r' THEN dacl.defaclacl END AS acltbl,
+ CASE WHEN dacl.defaclobjtype='S' THEN dacl.defaclacl END AS aclseq,
+ CASE WHEN dacl.defaclobjtype='f' THEN dacl.defaclacl END AS aclfunc,
+ (SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=nsp.oid) AS labels,
+ (SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=nsp.oid) AS providers
+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
+ {% if scid %}
+ nsp.oid={{scid}}::int AND
+ {% endif %}
+ nspname NOT LIKE E'pg\\temp\\%' AND
+ nspname NOT LIKE E'pg\\toast_temp\\%' AND
+ 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 1, nspname;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.1_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.1_plus/update.sql
new file mode 100644
index 0000000..9afd4ed
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.1_plus/update.sql
@@ -0,0 +1,80 @@
+{% if data %}
+{# ============= Below SQL will update SCHEMA comments ============= #}
+{% if data.description and data.description != o_data.description %}
+COMMENT ON SCHEMA "{{ o_data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# ============= Below SQL will update SCHEMA securitylabel ============= #}
+{% if data.securitylabels and data.securitylabels.added %}
+{% for label in data.securitylabels.added %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{% endif %}
+{% if data.securitylabels and data.securitylabels.updated %}
+{% for label in data.securitylabels.updated %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{% endif %}
+{% if data.securitylabels and data.securitylabels.deleted %}
+{% for label in data.securitylabels.deleted %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS NULL;
+{% endfor %}
+{% endif %}
+{# ============= Below SQL will update SCHEMA priviledges ============= #}
+{# ===== Newly added Priviledges ===== #}
+{% if data.privileges and data.privileges.added %}
+{% for privilege in data.privileges.added %}
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ o_data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ===== Updated Priviledges ===== #}
+{% if data.privileges and data.privileges.updated %}
+{% for privilege in data.privileges.updated %}
+REVOKE ALL ON SCHEMA "{{ o_data.name }}"
+ FROM {{ data.role }};
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ o_data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ===== Deleted Priviledges ===== #}
+{% if data.privileges and data.privileges.deleted %}
+{% for privilege in data.privileges.deleted %}
+REVOKE ALL ON SCHEMA "{{ o_data.name }}"
+ FROM {{ data.role }};
+{% endfor %}
+{% endif %}
+{# ============= Below SQL will update SCHEMA user ============= #}
+{% if data.namespaceowner and data.namespaceowner != o_data.namespaceowner %}
+ALTER SCHEMA "{{ o_data.name }}"
+ AUTHORIZATION TO {{ data.namespaceowner }};
+{% endif %}
+{# ============= Below SQL will update SCHEMA name ============= #}
+{% if data.name and data.name != o_data.name %}
+ALTER SCHEMA "{{ o_data.name }}"
+ RENAME TO "{{ data.name }}";
+{% endif %}
+{# ============= End main if data ============= #}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.2_plus/create.sql
new file mode 100644
index 0000000..db52fec
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.2_plus/create.sql
@@ -0,0 +1,7 @@
+{% if data %}
+CREATE SCHEMA "{{ data.name }}"
+{% if data.namespaceowner %}
+ AUTHORIZATION {{ data.namespaceowner }}
+{% endif %}
+{% endif %}
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.2_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.2_plus/delete.sql
new file mode 100644
index 0000000..94bbcb1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.2_plus/delete.sql
@@ -0,0 +1,8 @@
+{% if scid %}
+SELECT nsp.nspname as name, nsp.oid
+FROM pg_namespace nsp
+WHERE nsp.oid = {{scid}};
+{% endif %}
+{% if name %}
+DROP SCHEMA {{ name }} {% if cascade %}CASCADE{%endif%};
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.2_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.2_plus/grant.sql
new file mode 100644
index 0000000..1197fa8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.2_plus/grant.sql
@@ -0,0 +1,30 @@
+{% if data %}
+{# ======== Below create SQL for description ======== #}
+{% if data.description %}
+COMMENT ON SCHEMA "{{ data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# ======== Below create SQL for securitylabel tab ======== #}
+{% for label in data.securitylabels %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{# ======== Below create SQL for priviledge tab ======== #}
+{% for privilege in data.privileges %}
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ======== Below SQL will fetch id for given dataspace ======== #}
+{% if schema %}
+SELECT nsp.oid FROM pg_namespace nsp WHERE nsp.nspname = '{{schema}}'
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.2_plus/properties.sql
new file mode 100644
index 0000000..56204d8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.2_plus/properties.sql
@@ -0,0 +1,41 @@
+SELECT
+ CASE
+ WHEN (nspname LIKE E'pg\\\\_temp\\\\_%') THEN 1
+ WHEN (nspname LIKE E'pg\\\\_%') THEN 0
+ ELSE 3 END
+ AS nsptyp,
+ nsp.nspname as name, nsp.oid,
+ pg_get_userbyid(nspowner) AS namespaceowner, nspacl, description,
+ has_schema_privilege(nsp.oid, 'CREATE') as cancreate,
+ CASE WHEN nspname LIKE E'pg\\_%' THEN true ELSE false END as is_sys_object,
+ CASE WHEN dacl.defaclobjtype='r' THEN dacl.defaclacl END AS acltbl,
+ CASE WHEN dacl.defaclobjtype='S' THEN dacl.defaclacl END AS aclseq,
+ CASE WHEN dacl.defaclobjtype='f' THEN dacl.defaclacl END AS aclfunc,
+ CASE WHEN dacl.defaclobjtype='T' THEN dacl.defaclacl END AS acltype,
+ (SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=nsp.oid) AS labels,
+ (SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=nsp.oid) AS providers
+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
+ {% if scid %}
+ nsp.oid={{scid}}::int AND
+ {% endif %}
+ nspname NOT LIKE E'pg\\temp\\%' AND
+ nspname NOT LIKE E'pg\\toast_temp\\%' AND
+ 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 1, nspname;
+
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.2_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.2_plus/update.sql
new file mode 100644
index 0000000..9afd4ed
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_9.2_plus/update.sql
@@ -0,0 +1,80 @@
+{% if data %}
+{# ============= Below SQL will update SCHEMA comments ============= #}
+{% if data.description and data.description != o_data.description %}
+COMMENT ON SCHEMA "{{ o_data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# ============= Below SQL will update SCHEMA securitylabel ============= #}
+{% if data.securitylabels and data.securitylabels.added %}
+{% for label in data.securitylabels.added %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{% endif %}
+{% if data.securitylabels and data.securitylabels.updated %}
+{% for label in data.securitylabels.updated %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS '{{ label.securitylabel }}';
+{% endfor %}
+{% endif %}
+{% if data.securitylabels and data.securitylabels.deleted %}
+{% for label in data.securitylabels.deleted %}
+SECURITY LABEL FOR {{ label.provider }}
+ ON SCHEMA "{{ o_data.name }}"
+ IS NULL;
+{% endfor %}
+{% endif %}
+{# ============= Below SQL will update SCHEMA priviledges ============= #}
+{# ===== Newly added Priviledges ===== #}
+{% if data.privileges and data.privileges.added %}
+{% for privilege in data.privileges.added %}
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ o_data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ===== Updated Priviledges ===== #}
+{% if data.privileges and data.privileges.updated %}
+{% for privilege in data.privileges.updated %}
+REVOKE ALL ON SCHEMA "{{ o_data.name }}"
+ FROM {{ data.role }};
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ o_data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ===== Deleted Priviledges ===== #}
+{% if data.privileges and data.privileges.deleted %}
+{% for privilege in data.privileges.deleted %}
+REVOKE ALL ON SCHEMA "{{ o_data.name }}"
+ FROM {{ data.role }};
+{% endfor %}
+{% endif %}
+{# ============= Below SQL will update SCHEMA user ============= #}
+{% if data.namespaceowner and data.namespaceowner != o_data.namespaceowner %}
+ALTER SCHEMA "{{ o_data.name }}"
+ AUTHORIZATION TO {{ data.namespaceowner }};
+{% endif %}
+{# ============= Below SQL will update SCHEMA name ============= #}
+{% if data.name and data.name != o_data.name %}
+ALTER SCHEMA "{{ o_data.name }}"
+ RENAME TO "{{ data.name }}";
+{% endif %}
+{# ============= End main if data ============= #}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_pre_9.1/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_pre_9.1/create.sql
new file mode 100644
index 0000000..db52fec
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_pre_9.1/create.sql
@@ -0,0 +1,7 @@
+{% if data %}
+CREATE SCHEMA "{{ data.name }}"
+{% if data.namespaceowner %}
+ AUTHORIZATION {{ data.namespaceowner }}
+{% endif %}
+{% endif %}
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_pre_9.1/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_pre_9.1/delete.sql
new file mode 100644
index 0000000..94bbcb1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_pre_9.1/delete.sql
@@ -0,0 +1,8 @@
+{% if scid %}
+SELECT nsp.nspname as name, nsp.oid
+FROM pg_namespace nsp
+WHERE nsp.oid = {{scid}};
+{% endif %}
+{% if name %}
+DROP SCHEMA {{ name }} {% if cascade %}CASCADE{%endif%};
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_pre_9.1/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_pre_9.1/grant.sql
new file mode 100644
index 0000000..8e860cd
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_pre_9.1/grant.sql
@@ -0,0 +1,24 @@
+{% if data %}
+{# ======== Below create SQL for description ======== #}
+{% if data.description %}
+COMMENT ON SCHEMA "{{ data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# ======== Below create SQL for priviledge tab ======== #}
+{% for privilege in data.privileges %}
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ======== Below SQL will fetch id for given dataspace ======== #}
+{% if schema %}
+SELECT nsp.oid FROM pg_namespace nsp WHERE nsp.nspname = '{{schema}}'
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_pre_9.1/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_pre_9.1/properties.sql
new file mode 100644
index 0000000..d59fc0c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_pre_9.1/properties.sql
@@ -0,0 +1,36 @@
+SELECT
+ CASE
+ WHEN (nspname LIKE E'pg\\\\_temp\\\\_%') THEN 1
+ WHEN (nspname LIKE E'pg\\\\_%') THEN 0
+ ELSE 3 END
+ AS nsptyp,
+ nsp.nspname as name, nsp.oid,
+ pg_get_userbyid(nspowner) AS namespaceowner, nspacl, description,
+ has_schema_privilege(nsp.oid, 'CREATE') as cancreate,
+ CASE WHEN nspname LIKE E'pg\\_%' THEN true ELSE false END as is_sys_object,
+ CASE WHEN dacl.defaclobjtype='r' THEN dacl.defaclacl END AS acltbl,
+ CASE WHEN dacl.defaclobjtype='S' THEN dacl.defaclacl END AS aclseq,
+ CASE WHEN dacl.defaclobjtype='f' THEN dacl.defaclacl END AS aclfunc
+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
+ {% if scid %}
+ nsp.oid={{scid}}::int AND
+ {% endif %}
+ nspname NOT LIKE E'pg\\temp\\%' AND
+ nspname NOT LIKE E'pg\\toast_temp\\%' AND
+ 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 1, nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_pre_9.1/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_pre_9.1/update.sql
new file mode 100644
index 0000000..328896a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/edb_pre_9.1/update.sql
@@ -0,0 +1,58 @@
+{% if data %}
+{# ============= Below SQL will update SCHEMA comments ============= #}
+{% if data.description and data.description != o_data.description %}
+COMMENT ON SCHEMA "{{ o_data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# ============= Below SQL will update SCHEMA priviledges ============= #}
+{# ===== Newly added Priviledges ===== #}
+{% if data.privileges and data.privileges.added %}
+{% for privilege in data.privileges.added %}
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ o_data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ===== Updated Priviledges ===== #}
+{% if data.privileges and data.privileges.updated %}
+{% for privilege in data.privileges.updated %}
+REVOKE ALL ON SCHEMA "{{ o_data.name }}"
+ FROM {{ data.role }};
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ o_data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ===== Deleted Priviledges ===== #}
+{% if data.privileges and data.privileges.deleted %}
+{% for privilege in data.privileges.deleted %}
+REVOKE ALL ON SCHEMA "{{ o_data.name }}"
+ FROM {{ data.role }};
+{% endfor %}
+{% endif %}
+{# ============= Below SQL will update SCHEMA user ============= #}
+{% if data.namespaceowner and data.namespaceowner != o_data.namespaceowner %}
+ALTER SCHEMA "{{ o_data.name }}"
+ AUTHORIZATION TO {{ data.namespaceowner }};
+{% endif %}
+{# ============= Below SQL will update SCHEMA name ============= #}
+{% if data.name and data.name != o_data.name %}
+ALTER SCHEMA "{{ o_data.name }}"
+ RENAME TO "{{ data.name }}";
+{% endif %}
+{# ============= End main if data ============= #}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/pre_9.1/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/pre_9.1/create.sql
new file mode 100644
index 0000000..db52fec
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/pre_9.1/create.sql
@@ -0,0 +1,7 @@
+{% if data %}
+CREATE SCHEMA "{{ data.name }}"
+{% if data.namespaceowner %}
+ AUTHORIZATION {{ data.namespaceowner }}
+{% endif %}
+{% endif %}
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/pre_9.1/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/pre_9.1/delete.sql
new file mode 100644
index 0000000..94bbcb1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/pre_9.1/delete.sql
@@ -0,0 +1,8 @@
+{% if scid %}
+SELECT nsp.nspname as name, nsp.oid
+FROM pg_namespace nsp
+WHERE nsp.oid = {{scid}};
+{% endif %}
+{% if name %}
+DROP SCHEMA {{ name }} {% if cascade %}CASCADE{%endif%};
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/pre_9.1/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/pre_9.1/grant.sql
new file mode 100644
index 0000000..8e860cd
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/pre_9.1/grant.sql
@@ -0,0 +1,24 @@
+{% if data %}
+{# ======== Below create SQL for description ======== #}
+{% if data.description %}
+COMMENT ON SCHEMA "{{ data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# ======== Below create SQL for priviledge tab ======== #}
+{% for privilege in data.privileges %}
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ======== Below SQL will fetch id for given dataspace ======== #}
+{% if schema %}
+SELECT nsp.oid FROM pg_namespace nsp WHERE nsp.nspname = '{{schema}}'
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/pre_9.1/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/pre_9.1/properties.sql
new file mode 100644
index 0000000..b6beb04
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/pre_9.1/properties.sql
@@ -0,0 +1,33 @@
+SELECT
+ CASE
+ WHEN (nspname LIKE E'pg\\\\_temp\\\\_%') THEN 1
+ WHEN (nspname LIKE E'pg\\\\_%') THEN 0
+ ELSE 3 END
+ AS nsptyp,
+ nsp.nspname as name, nsp.oid,
+ pg_get_userbyid(nspowner) AS namespaceowner, nspacl, description,
+ has_schema_privilege(nsp.oid, 'CREATE') as cancreate,
+ CASE WHEN nspname LIKE E'pg\\_%' THEN true ELSE false END as is_sys_object,
+ CASE WHEN dacl.defaclobjtype='r' THEN dacl.defaclacl END AS acltbl,
+ CASE WHEN dacl.defaclobjtype='S' THEN dacl.defaclacl END AS aclseq,
+ CASE WHEN dacl.defaclobjtype='f' THEN dacl.defaclacl END AS aclfunc
+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))
+ )AND
+ {% if scid %}
+ nsp.oid={{scid}}::int AND
+ {% endif %}
+ nspname NOT LIKE E'pg\\temp\\%' AND
+ nspname NOT LIKE E'pg\\toast_temp\\%'
+ORDER BY 1, nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/pre_9.1/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/pre_9.1/update.sql
new file mode 100644
index 0000000..328896a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/sql/pre_9.1/update.sql
@@ -0,0 +1,58 @@
+{% if data %}
+{# ============= Below SQL will update SCHEMA comments ============= #}
+{% if data.description and data.description != o_data.description %}
+COMMENT ON SCHEMA "{{ o_data.name }}"
+ IS '{{ data.description }}';
+{% endif %}
+{# ============= Below SQL will update SCHEMA priviledges ============= #}
+{# ===== Newly added Priviledges ===== #}
+{% if data.privileges and data.privileges.added %}
+{% for privilege in data.privileges.added %}
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ o_data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ===== Updated Priviledges ===== #}
+{% if data.privileges and data.privileges.updated %}
+{% for privilege in data.privileges.updated %}
+REVOKE ALL ON SCHEMA "{{ o_data.name }}"
+ FROM {{ data.role }};
+{% if privilege.create %}
+GRANT CREATE ON SCHEMA
+{% endif %}
+{% if privilege.usage %}
+GRANT USAGE ON SCHEMA
+{% endif %}
+{% if privilege.all %}
+GRANT ALL ON SCHEMA
+{% endif %}
+"{{ o_data.name }}" TO {{ data.role }} {% if privilege.with_grant %} WITH GRANT OPTION {% endif %};
+{% endfor %}
+{% endif %}
+{# ===== Deleted Priviledges ===== #}
+{% if data.privileges and data.privileges.deleted %}
+{% for privilege in data.privileges.deleted %}
+REVOKE ALL ON SCHEMA "{{ o_data.name }}"
+ FROM {{ data.role }};
+{% endfor %}
+{% endif %}
+{# ============= Below SQL will update SCHEMA user ============= #}
+{% if data.namespaceowner and data.namespaceowner != o_data.namespaceowner %}
+ALTER SCHEMA "{{ o_data.name }}"
+ AUTHORIZATION TO {{ data.namespaceowner }};
+{% endif %}
+{# ============= Below SQL will update SCHEMA name ============= #}
+{% if data.name and data.name != o_data.name %}
+ALTER SCHEMA "{{ o_data.name }}"
+ RENAME TO "{{ data.name }}";
+{% endif %}
+{# ============= End main if data ============= #}
+{% endif %}
view thread (5+ 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]
Subject: Re: PATCH: added "Collation" & "Catalog Objects" nodes in pgAdmin4
In-Reply-To: <[email protected]>
* 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