Hi, PAF patch for RM1107
Issue fixed: *1]* Removed alter.sql and merged sql statement from alter.sql with create sql wherever possible so that objects can be create in single statement to ensure that whole operation is atomic. *Code merged for below nodes:* Schema, Table, all constraints. *Nodes do not support executing other sql statements with create statement:* Index node (To ensure that create index operation to be atomic added code to Initiate database transaction manually before creating index object) *Node Excluded (As they do not support **executing other sql statements with create statement and also create statement cannot be executed inside database transaction):* Database, Tablespace *2]* Fixed typo in databases/templates/databases/sql/9.3_plus/grant.sql -- *Harshal Dhumal* *Software Engineer* EnterpriseDB India: http://www.enterprisedb.com The Enterprise PostgreSQL Company
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/__init__.py index 1ef8270..bf22d0e 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/__init__.py @@ -506,6 +506,7 @@ It may have been removed by another user. ) ) try: + self.format_request_acls(data, specific=['nspacl']) SQL = render_template( "/".join([self.template_path, 'sql/create.sql']), data=data, conn=self.conn, _=gettext @@ -518,22 +519,6 @@ It may have been removed by another user. errormsg=res + '\n' + 'Operation failed while running create statement' ) - self.format_request_acls(data, specific=['nspacl']) - - SQL = render_template( - "/".join([self.template_path, 'sql/alter.sql']), - data=data, conn=self.conn, _=gettext - ) - # 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 make_json_response( - status=410, - success=0, - errormsg=res + '\n' + - 'Operation failed while running alter statement' - ) # we need oid to to add object in tree at browser, # below sql will gives the same @@ -727,11 +712,6 @@ It may have been removed by another user. "/".join([self.template_path, 'sql/create.sql']), data=data, conn=self.conn, _=gettext ) - SQL += "\n" - SQL += render_template( - "/".join([self.template_path, 'sql/alter.sql']), - _=gettext, data=data, conn=self.conn - ) return SQL @@ -774,11 +754,6 @@ It may have been removed by another user. "/".join([self.template_path, 'sql/create.sql']), _=gettext, data=data, conn=self.conn ) - SQL += "\n" - SQL += render_template( - "/".join([self.template_path, 'sql/alter.sql']), - _=gettext, data=data, conn=self.conn - ) sql_header = """ -- SCHEMA: {0} @@ -867,7 +842,6 @@ It may have been removed by another user. return make_json_response(data=nodes) - class CatalogView(SchemaView): """ This class is responsible for generating routes for catalog schema node. @@ -965,11 +939,6 @@ It may have been removed by another user. "/".join([self.template_path, 'sql/create.sql']), _=gettext, data=old_data, conn=self.conn ) - SQL += "\n" - SQL += render_template( - "/".join([self.template_path, 'sql/alter.sql']), - _=gettext, data=old_data, conn=self.conn - ) sql_header = """ -- CATALOG: {0} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py index 3acf709..93cde7b 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py @@ -1747,14 +1747,6 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): if ctype == 'p' else 'UNIQUE' ).strip('\n') ) - # sql to update comments - sql.append( - render_template( - "/".join([self.index_constraint_template_path, - 'alter.sql']), - data=c, conn=self.conn - ).strip('\n') - ) else: sql.append( gettext( @@ -1871,14 +1863,6 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): data=c, conn=self.conn ).strip('\n') ) - # sql to update comments - sql.append( - render_template( - "/".join([self.foreign_key_template_path, - 'alter.sql']), - data=c, conn=self.conn - ).strip('\n') - ) if c['autoindex']: sql.append( @@ -1963,14 +1947,6 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): data=c, conn=self.conn ).strip('\n') ) - # sql to update comments - sql.append( - render_template( - "/".join([self.check_constraint_template_path, - 'alter.sql']), - data=c, conn=self.conn - ).strip('\n') - ) if len(sql) > 0: # Join all the sql(s) as single string @@ -2047,14 +2023,6 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): data=c, conn=self.conn ).strip('\n') ) - # sql to update comments - sql.append( - render_template( - "/".join([self.exclusion_constraint_template_path, - 'alter.sql']), - data=c, conn=self.conn - ).strip('\n') - ) if len(sql) > 0: # Join all the sql(s) as single string diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/check_constraint/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/check_constraint/__init__.py index f2eb81b..c339453 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/check_constraint/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/check_constraint/__init__.py @@ -465,17 +465,6 @@ class CheckConstraintView(PGChildNodeView): icon = "icon-check_constraints" valid = True - sql = render_template("/".join([self.template_path, 'alter.sql']), - data=data, - conn=self.conn) - sql = sql.strip('\n').strip(' ') - - if sql != '': - status, result = self.conn.execute_scalar(sql) - if not status: - self.end_transaction() - return internal_server_error(errormsg=result) - return jsonify( node=self.blueprint.generate_browser_node( res['rows'][0]['oid'], @@ -638,10 +627,6 @@ class CheckConstraintView(PGChildNodeView): SQL = render_template("/".join([self.template_path, 'create.sql']), data=data) - SQL += "\n" - SQL += render_template( - "/".join([self.template_path, 'alter.sql']), - data=data) sql_header = "-- Constraint: {0}\n\n-- ".format(data['name']) @@ -735,9 +720,6 @@ class CheckConstraintView(PGChildNodeView): SQL = render_template("/".join([self.template_path, 'create.sql']), data=data) - SQL += "\n" - SQL += render_template("/".join([self.template_path, 'alter.sql']), - data=data) return SQL except Exception as e: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py index 16eafc1..cd44642 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py @@ -506,15 +506,6 @@ class ExclusionConstraintView(PGChildNodeView): if not status: self.end_transaction() return internal_server_error(errormsg=res) - if 'name' in data and data['name'] != '': - sql = render_template("/".join([self.template_path, 'alter.sql']), data=data, conn=self.conn) - sql = sql.strip('\n').strip(' ') - - if sql != '': - status, result = self.conn.execute_scalar(sql) - if not status: - self.end_transaction() - return internal_server_error(errormsg=result) return jsonify( node=self.blueprint.generate_browser_node( @@ -728,9 +719,6 @@ class ExclusionConstraintView(PGChildNodeView): sql = render_template("/".join([self.template_path, 'create.sql']), data=data, conn=self.conn) - sql += "\n" - sql += render_template("/".join([self.template_path, 'alter.sql']), - data=data, conn=self.conn) return sql @@ -795,10 +783,6 @@ class ExclusionConstraintView(PGChildNodeView): SQL = render_template( "/".join([self.template_path, 'create.sql']), data=data) - SQL += "\n" - SQL += render_template( - "/".join([self.template_path, 'alter.sql']), - data=data, conn=self.conn) sql_header = "-- Constraint: {0}\n\n-- ".format(data['name']) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/__init__.py index ce05031..2a3cea9 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/__init__.py @@ -547,15 +547,6 @@ class ForeignKeyConstraintView(PGChildNodeView): icon = "icon-foreign_key" valid = True - sql = render_template("/".join([self.template_path, 'alter.sql']), data=data, conn=self.conn) - sql = sql.strip('\n').strip(' ') - - if sql != '': - status, result = self.conn.execute_scalar(sql) - if not status: - self.end_transaction() - return internal_server_error(errormsg=result) - if data['autoindex']: sql = render_template( "/".join([self.template_path, 'create_index.sql']), @@ -827,9 +818,6 @@ class ForeignKeyConstraintView(PGChildNodeView): sql = render_template("/".join([self.template_path, 'create.sql']), data=data, conn=self.conn) - sql += "\n" - sql += render_template("/".join([self.template_path, 'alter.sql']), - data=data, conn=self.conn) if data['autoindex']: sql += render_template( @@ -898,10 +886,6 @@ class ForeignKeyConstraintView(PGChildNodeView): SQL = render_template( "/".join([self.template_path, 'create.sql']), data=data) - SQL += "\n" - SQL += render_template( - "/".join([self.template_path, 'alter.sql']), - data=data, conn=self.conn) sql_header = "-- Constraint: {0}\n\n-- ".format(data['name']) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/__init__.py index 90aa4ce..96b3c3e 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/__init__.py @@ -526,17 +526,6 @@ class IndexConstraintView(PGChildNodeView): self.end_transaction() return internal_server_error(errormsg=res) - sql = render_template("/".join([self.template_path, 'alter.sql']), - data=data, - conn=self.conn) - sql = sql.strip('\n').strip(' ') - - if sql != '': - status, result = self.conn.execute_scalar(sql) - if not status: - self.end_transaction() - return internal_server_error(errormsg=result) - return jsonify( node=self.blueprint.generate_browser_node( res['rows'][0]['oid'], @@ -768,10 +757,6 @@ class IndexConstraintView(PGChildNodeView): data=data, conn=self.conn, constraint_name=self.constraint_name) - sql += "\n" - sql += render_template("/".join([self.template_path, 'alter.sql']), - data=data, - conn=self.conn) return sql @@ -826,10 +811,6 @@ class IndexConstraintView(PGChildNodeView): "/".join([self.template_path, 'create.sql']), data=data, constraint_name=self.constraint_name) - SQL += "\n" - SQL += render_template( - "/".join([self.template_path, 'alter.sql']), - data=data, conn=self.conn) sql_header = "-- Constraint: {0}\n\n-- ".format(data['name']) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py index 3238e02..3fe782d 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py @@ -557,15 +557,19 @@ class IndexesView(PGChildNodeView): data['table'] = self.table try: + # Start transaction. + self.conn.execute_scalar("BEGIN;") SQL = render_template("/".join([self.template_path, 'create.sql']), data=data, conn=self.conn, mode='create') status, res = self.conn.execute_scalar(SQL) if not status: + # End transaction. + self.conn.execute_scalar("END;") return internal_server_error(errormsg=res) # If user chooses concurrent index then we can not run it along - # with other alter statments so we will separate alter index part + # with other alter statements so we will separate alter index part SQL = render_template("/".join([self.template_path, 'alter.sql']), data=data, conn=self.conn) @@ -573,6 +577,8 @@ class IndexesView(PGChildNodeView): if SQL != '': status, res = self.conn.execute_scalar(SQL) if not status: + # End transaction. + self.conn.execute_scalar("END;") return internal_server_error(errormsg=res) # we need oid to to add object in tree at browser @@ -581,8 +587,12 @@ class IndexesView(PGChildNodeView): tid=tid, data=data) status, idx = self.conn.execute_scalar(SQL) if not status: + # End transaction. + self.conn.execute_scalar("END;") return internal_server_error(errormsg=tid) + # End transaction. + self.conn.execute_scalar("END;") return jsonify( node=self.blueprint.generate_browser_node( idx, @@ -592,6 +602,8 @@ class IndexesView(PGChildNodeView): ) ) except Exception as e: + # End transaction. + self.conn.execute_scalar("END;") return internal_server_error(errormsg=str(e)) @check_precondition diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.1_plus/alter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.1_plus/alter.sql deleted file mode 100644 index 0fb0ea5..0000000 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.1_plus/alter.sql +++ /dev/null @@ -1,4 +0,0 @@ -{% if data.comment %} -COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} - IS {{ data.comment|qtLiteral }}; -{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.1_plus/create.sql index 8d8c10a..726a41a 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.1_plus/create.sql @@ -2,3 +2,8 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} CHECK ({{ data.consrc }}); {% endif %} +{% if data.comment %} + +COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} + IS {{ data.comment|qtLiteral }}; +{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.2_plus/alter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.2_plus/alter.sql deleted file mode 100644 index 0fb0ea5..0000000 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.2_plus/alter.sql +++ /dev/null @@ -1,4 +0,0 @@ -{% if data.comment %} -COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} - IS {{ data.comment|qtLiteral }}; -{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.2_plus/create.sql index 2c7a57f..7b0bf08 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.2_plus/create.sql @@ -4,3 +4,8 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} NOT VALID{% endif %}{% if data.connoinherit %} NO INHERIT{% endif %}; {% endif %} +{% if data.comment %} + +COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} + IS {{ data.comment|qtLiteral }}; +{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/alter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/alter.sql deleted file mode 100644 index 0fb0ea5..0000000 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/alter.sql +++ /dev/null @@ -1,4 +0,0 @@ -{% if data.comment %} -COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} - IS {{ data.comment|qtLiteral }}; -{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql index c6a53cd..db29048 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql @@ -9,4 +9,9 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} DEFERRABLE{% if data.condeferred %} INITIALLY DEFERRED{% endif%} -{% endif%}{% if data.constraint %} WHERE ({{data.constraint}}){% endif%}; \ No newline at end of file +{% endif%}{% if data.constraint %} WHERE ({{data.constraint}}){% endif%}; +{% if data.comment and data.name %} + +COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} + IS {{ data.comment|qtLiteral }}; +{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/alter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/alter.sql deleted file mode 100644 index 0fb0ea5..0000000 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/alter.sql +++ /dev/null @@ -1,4 +0,0 @@ -{% if data.comment %} -COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} - IS {{ data.comment|qtLiteral }}; -{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql index c6a53cd..db29048 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql @@ -9,4 +9,9 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} DEFERRABLE{% if data.condeferred %} INITIALLY DEFERRED{% endif%} -{% endif%}{% if data.constraint %} WHERE ({{data.constraint}}){% endif%}; \ No newline at end of file +{% endif%}{% if data.constraint %} WHERE ({{data.constraint}}){% endif%}; +{% if data.comment and data.name %} + +COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} + IS {{ data.comment|qtLiteral }}; +{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/alter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/alter.sql deleted file mode 100644 index 0fb0ea5..0000000 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/alter.sql +++ /dev/null @@ -1,4 +0,0 @@ -{% if data.comment %} -COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} - IS {{ data.comment|qtLiteral }}; -{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/create.sql index 7a4eda4..39df112 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/create.sql @@ -24,4 +24,9 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} {% endif%} {% if data.convalidated %} - NOT VALID{% endif%}; \ No newline at end of file + NOT VALID{% endif%}; +{% if data.comment and data.name %} + +COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} + IS {{ data.comment|qtLiteral }}; +{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/alter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/alter.sql deleted file mode 100644 index 0fb0ea5..0000000 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/alter.sql +++ /dev/null @@ -1,4 +0,0 @@ -{% if data.comment %} -COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} - IS {{ data.comment|qtLiteral }}; -{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/create.sql index 61a717e..2bd34a4 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/create.sql @@ -9,4 +9,9 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} DEFERRABLE{% if data.condeferred %} INITIALLY DEFERRED{% endif%} -{% endif%}; \ No newline at end of file +{% endif%}; +{% if data.comment and data.name %} + +COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} + IS {{ data.comment|qtLiteral }}; +{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalog/pg/9.1_plus/sql/alter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalog/pg/9.1_plus/sql/alter.sql deleted file mode 100644 index 937bab7..0000000 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalog/pg/9.1_plus/sql/alter.sql +++ /dev/null @@ -1 +0,0 @@ -{# We have nothing to alter in the catalog #} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalog/pg/9.2_plus/sql/alter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalog/pg/9.2_plus/sql/alter.sql deleted file mode 100644 index 937bab7..0000000 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalog/pg/9.2_plus/sql/alter.sql +++ /dev/null @@ -1 +0,0 @@ -{# We have nothing to alter in the catalog #} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalog/ppas/9.1_plus/sql/alter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalog/ppas/9.1_plus/sql/alter.sql deleted file mode 100644 index 937bab7..0000000 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalog/ppas/9.1_plus/sql/alter.sql +++ /dev/null @@ -1 +0,0 @@ -{# We have nothing to alter in the catalog #} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalog/ppas/9.2_plus/sql/alter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalog/ppas/9.2_plus/sql/alter.sql deleted file mode 100644 index 937bab7..0000000 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/catalog/ppas/9.2_plus/sql/alter.sql +++ /dev/null @@ -1 +0,0 @@ -{# We have nothing to alter in the catalog #} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.1_plus/sql/alter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.1_plus/sql/alter.sql deleted file mode 100644 index 61fdfe4..0000000 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.1_plus/sql/alter.sql +++ /dev/null @@ -1,35 +0,0 @@ -{% import 'macros/security.macros' as SECLABLE %} -{% import 'macros/privilege.macros' as PRIVILEGE %} -{% import 'macros/default_privilege.macros' as DEFAULT_PRIVILEGE %} -{# Alter the comment/description #} -{% if data.description %} -COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }} - IS {{ data.description|qtLiteral }}; - -{% endif %} -{# ACL for the schema #} -{% if data.nspacl %} -{% for priv in data.nspacl %} -{{ PRIVILEGE.APPLY(conn, 'SCHEMA', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}{% endfor %} -{% endif %} - -{# Default privileges on tables #} -{% for defacl, type in [ - ('deftblacl', 'TABLES'), ('defseqacl', 'SEQUENCES'), - ('deffuncacl', 'FUNCTIONS')] -%} -{% if data[defacl] %}{% set acl = data[defacl] %} -{% for priv in data.deftblacl %} -{{ DEFAULT_PRIVILEGE.SET( - conn, 'SCHEMA', data.name, type, priv.grantee, - priv.without_grant, priv.with_grant - ) }}{% endfor %} -{% endif %} -{% endfor %} - -{# Security Labels on schema #} -{% if data.seclabels and data.seclabels|length > 0 %} -{% for r in data.seclabels %} -{{ SECLABLE.APPLY(conn, 'SCHEMA', data.name, r.provider, r.label) }} -{% endfor %} -{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.1_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.1_plus/sql/create.sql index d3598ac..946058a 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.1_plus/sql/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.1_plus/sql/create.sql @@ -1,7 +1,44 @@ +{% import 'macros/security.macros' as SECLABLE %} +{% import 'macros/privilege.macros' as PRIVILEGE %} +{% import 'macros/default_privilege.macros' as DEFAULT_PRIVILEGE %} {% if data.name %} CREATE SCHEMA {{ conn|qtIdent(data.name) }}{% if data.namespaceowner %} AUTHORIZATION {{ conn|qtIdent(data.namespaceowner) }}{% endif %}; +{# Alter the comment/description #} +{% if data.description %} + +COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }} + IS {{ data.description|qtLiteral }}; + +{% endif %} +{# ACL for the schema #} +{% if data.nspacl %} +{% for priv in data.nspacl %} +{{ PRIVILEGE.APPLY(conn, 'SCHEMA', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}{% endfor %} +{% endif %} + +{# Default privileges on tables #} +{% for defacl, type in [ + ('deftblacl', 'TABLES'), ('defseqacl', 'SEQUENCES'), + ('deffuncacl', 'FUNCTIONS')] +%} +{% if data[defacl] %}{% set acl = data[defacl] %} +{% for priv in data.deftblacl %} +{{ DEFAULT_PRIVILEGE.SET( + conn, 'SCHEMA', data.name, type, priv.grantee, + priv.without_grant, priv.with_grant + ) }}{% endfor %} +{% endif %} +{% endfor %} + +{# Security Labels on schema #} +{% if data.seclabels and data.seclabels|length > 0 %} +{% for r in data.seclabels %} +{{ SECLABLE.APPLY(conn, 'SCHEMA', data.name, r.provider, r.label) }} +{% endfor %} +{% endif %} + {% else %} {{ -- _('Incomplete definition') }} {% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.2_plus/sql/alter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.2_plus/sql/alter.sql deleted file mode 100644 index 38cb949..0000000 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.2_plus/sql/alter.sql +++ /dev/null @@ -1,35 +0,0 @@ -{% import 'macros/security.macros' as SECLABEL %} -{% import 'macros/privilege.macros' as PRIVILEGE %} -{% import 'macros/default_privilege.macros' as DEFAULT_PRIVILEGE %} -{# Alter the comment/description #} -{% if data.description %} -COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }} - IS {{ data.description|qtLiteral }}; - -{% endif %} -{# ACL for the schema #} -{% if data.nspacl %} -{% for priv in data.nspacl %} -{{ PRIVILEGE.APPLY(conn, 'SCHEMA', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}{% endfor %} -{% endif %} - -{# Default privileges on tables #} -{% for defacl, type in [ - ('deftblacl', 'TABLES'), ('defseqacl', 'SEQUENCES'), - ('deffuncacl', 'FUNCTIONS'), ('deftypeacl', 'TYPES')] -%} -{% if data[defacl] %}{% set acl = data[defacl] %} -{% for priv in data.deftblacl %} -{{ DEFAULT_PRIVILEGE.SET( - conn, 'SCHEMA', data.name, type, priv.grantee, - priv.without_grant, priv.with_grant - ) }}{% endfor %} -{% endif %} -{% endfor %} - -{# Security Labels on schema #} -{% if data.seclabels and data.seclabels|length > 0 %} -{% for r in data.seclabels %} -{{ SECLABEL.APPLY(conn, 'SCHEMA', data.name, r.provider, r.label) }} -{% endfor %} -{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.2_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.2_plus/sql/create.sql index d3598ac..7824916 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.2_plus/sql/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.2_plus/sql/create.sql @@ -1,7 +1,44 @@ +{% import 'macros/security.macros' as SECLABEL %} +{% import 'macros/privilege.macros' as PRIVILEGE %} +{% import 'macros/default_privilege.macros' as DEFAULT_PRIVILEGE %} {% if data.name %} CREATE SCHEMA {{ conn|qtIdent(data.name) }}{% if data.namespaceowner %} AUTHORIZATION {{ conn|qtIdent(data.namespaceowner) }}{% endif %}; +{# Alter the comment/description #} +{% if data.description %} + +COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }} + IS {{ data.description|qtLiteral }}; + +{% endif %} +{# ACL for the schema #} +{% if data.nspacl %} +{% for priv in data.nspacl %} +{{ PRIVILEGE.APPLY(conn, 'SCHEMA', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}{% endfor %} +{% endif %} + +{# Default privileges on tables #} +{% for defacl, type in [ + ('deftblacl', 'TABLES'), ('defseqacl', 'SEQUENCES'), + ('deffuncacl', 'FUNCTIONS'), ('deftypeacl', 'TYPES')] +%} +{% if data[defacl] %}{% set acl = data[defacl] %} +{% for priv in data.deftblacl %} +{{ DEFAULT_PRIVILEGE.SET( + conn, 'SCHEMA', data.name, type, priv.grantee, + priv.without_grant, priv.with_grant + ) }}{% endfor %} +{% endif %} +{% endfor %} + +{# Security Labels on schema #} +{% if data.seclabels and data.seclabels|length > 0 %} +{% for r in data.seclabels %} +{{ SECLABEL.APPLY(conn, 'SCHEMA', data.name, r.provider, r.label) }} +{% endfor %} +{% endif %} + {% else %} {{ -- _('Incomplete definition') }} {% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.1_plus/sql/alter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.1_plus/sql/alter.sql deleted file mode 100644 index 775dfc3..0000000 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.1_plus/sql/alter.sql +++ /dev/null @@ -1,35 +0,0 @@ -{% import 'macros/security.macros' as SECLABEL %} -{% import 'macros/privilege.macros' as PRIVILEGE %} -{% import 'macros/default_privilege.macros' as DEFAULT_PRIVILEGE %} -{# Alter the comment/description #} -{% if data.description %} -COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }} - IS {{ data.description|qtLiteral }}; - -{% endif %} -{# ACL for the schema #} -{% if data.nspacl %} -{% for priv in data.nspacl %} -{{ PRIVILEGE.APPLY(conn, 'SCHEMA', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}{% endfor %} -{% endif %} - -{# Default privileges on tables #} -{% for defacl, type in [ - ('deftblacl', 'TABLES'), ('defseqacl', 'SEQUENCES'), - ('deffuncacl', 'FUNCTIONS')] -%} -{% if data[defacl] %}{% set acl = data[defacl] %} -{% for priv in data.deftblacl %} -{{ DEFAULT_PRIVILEGE.SET( - conn, 'SCHEMA', data.name, type, priv.grantee, - priv.without_grant, priv.with_grant - ) }}{% endfor %} -{% endif %} -{% endfor %} - -{# Security Labels on schema #} -{% if data.seclabels and data.seclabels|length > 0 %} -{% for r in data.seclabels %} -{{ SECLABEL.APPLY(conn, 'SCHEMA', data.name, r.provider, r.label) }} -{% endfor %} -{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.1_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.1_plus/sql/create.sql index d3598ac..b63bb59 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.1_plus/sql/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.1_plus/sql/create.sql @@ -1,7 +1,44 @@ +{% import 'macros/security.macros' as SECLABEL %} +{% import 'macros/privilege.macros' as PRIVILEGE %} +{% import 'macros/default_privilege.macros' as DEFAULT_PRIVILEGE %} {% if data.name %} CREATE SCHEMA {{ conn|qtIdent(data.name) }}{% if data.namespaceowner %} AUTHORIZATION {{ conn|qtIdent(data.namespaceowner) }}{% endif %}; +{# Alter the comment/description #} +{% if data.description %} + +COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }} + IS {{ data.description|qtLiteral }}; + +{% endif %} +{# ACL for the schema #} +{% if data.nspacl %} +{% for priv in data.nspacl %} +{{ PRIVILEGE.APPLY(conn, 'SCHEMA', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}{% endfor %} +{% endif %} + +{# Default privileges on tables #} +{% for defacl, type in [ + ('deftblacl', 'TABLES'), ('defseqacl', 'SEQUENCES'), + ('deffuncacl', 'FUNCTIONS')] +%} +{% if data[defacl] %}{% set acl = data[defacl] %} +{% for priv in data.deftblacl %} +{{ DEFAULT_PRIVILEGE.SET( + conn, 'SCHEMA', data.name, type, priv.grantee, + priv.without_grant, priv.with_grant + ) }}{% endfor %} +{% endif %} +{% endfor %} + +{# Security Labels on schema #} +{% if data.seclabels and data.seclabels|length > 0 %} +{% for r in data.seclabels %} +{{ SECLABEL.APPLY(conn, 'SCHEMA', data.name, r.provider, r.label) }} +{% endfor %} +{% endif %} + {% else %} {{ -- _('Incomplete definition') }} {% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.2_plus/sql/alter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.2_plus/sql/alter.sql deleted file mode 100644 index 38cb949..0000000 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.2_plus/sql/alter.sql +++ /dev/null @@ -1,35 +0,0 @@ -{% import 'macros/security.macros' as SECLABEL %} -{% import 'macros/privilege.macros' as PRIVILEGE %} -{% import 'macros/default_privilege.macros' as DEFAULT_PRIVILEGE %} -{# Alter the comment/description #} -{% if data.description %} -COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }} - IS {{ data.description|qtLiteral }}; - -{% endif %} -{# ACL for the schema #} -{% if data.nspacl %} -{% for priv in data.nspacl %} -{{ PRIVILEGE.APPLY(conn, 'SCHEMA', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}{% endfor %} -{% endif %} - -{# Default privileges on tables #} -{% for defacl, type in [ - ('deftblacl', 'TABLES'), ('defseqacl', 'SEQUENCES'), - ('deffuncacl', 'FUNCTIONS'), ('deftypeacl', 'TYPES')] -%} -{% if data[defacl] %}{% set acl = data[defacl] %} -{% for priv in data.deftblacl %} -{{ DEFAULT_PRIVILEGE.SET( - conn, 'SCHEMA', data.name, type, priv.grantee, - priv.without_grant, priv.with_grant - ) }}{% endfor %} -{% endif %} -{% endfor %} - -{# Security Labels on schema #} -{% if data.seclabels and data.seclabels|length > 0 %} -{% for r in data.seclabels %} -{{ SECLABEL.APPLY(conn, 'SCHEMA', data.name, r.provider, r.label) }} -{% endfor %} -{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.2_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.2_plus/sql/create.sql index d3598ac..7824916 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.2_plus/sql/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.2_plus/sql/create.sql @@ -1,7 +1,44 @@ +{% import 'macros/security.macros' as SECLABEL %} +{% import 'macros/privilege.macros' as PRIVILEGE %} +{% import 'macros/default_privilege.macros' as DEFAULT_PRIVILEGE %} {% if data.name %} CREATE SCHEMA {{ conn|qtIdent(data.name) }}{% if data.namespaceowner %} AUTHORIZATION {{ conn|qtIdent(data.namespaceowner) }}{% endif %}; +{# Alter the comment/description #} +{% if data.description %} + +COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }} + IS {{ data.description|qtLiteral }}; + +{% endif %} +{# ACL for the schema #} +{% if data.nspacl %} +{% for priv in data.nspacl %} +{{ PRIVILEGE.APPLY(conn, 'SCHEMA', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}{% endfor %} +{% endif %} + +{# Default privileges on tables #} +{% for defacl, type in [ + ('deftblacl', 'TABLES'), ('defseqacl', 'SEQUENCES'), + ('deffuncacl', 'FUNCTIONS'), ('deftypeacl', 'TYPES')] +%} +{% if data[defacl] %}{% set acl = data[defacl] %} +{% for priv in data.deftblacl %} +{{ DEFAULT_PRIVILEGE.SET( + conn, 'SCHEMA', data.name, type, priv.grantee, + priv.without_grant, priv.with_grant + ) }}{% endfor %} +{% endif %} +{% endfor %} + +{# Security Labels on schema #} +{% if data.seclabels and data.seclabels|length > 0 %} +{% for r in data.seclabels %} +{{ SECLABEL.APPLY(conn, 'SCHEMA', data.name, r.provider, r.label) }} +{% endfor %} +{% endif %} + {% else %} {{ -- _('Incomplete definition') }} {% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/grant.sql index 7294c70..3731a55 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/grant.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/grant.sql @@ -1,7 +1,7 @@ {# -# CREATE DATABSE does not allow us to run any -# other sql statments along with it, so we wrote -# seprate sql for rest alter sql statments here +# CREATE DATABASE does not allow us to run any +# other sql statements along with it, so we wrote +# separate sql for rest alter sql statements here #} {% import 'macros/security.macros' as SECLABEL %} {% import 'macros/variable.macros' as VARIABLE %}
-- Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers