public inbox for [email protected]
help / color / mirror / Atom feedFrom: Khushboo Vashi <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: [pgAdmin4][Patch]: RM 5053 - Getting an error while changing the columns in the existing view
Date: Tue, 14 Jan 2020 10:27:31 +0530
Message-ID: <CAFOhELdGhQ1O2R+GLgNj_jbOtUduDt=rsNvwUdwmxeUxAmes6Q@mail.gmail.com> (raw)
Hi,
Please find the attached patch for RM #5053 - Getting an error while
changing the columns in the existing view.
PostgreSQL doesn't allow to change the view columns. So, while performing
this task the existing view should be dropped first and then recreate it
and also user will get a warning first.
Thanks,
Khushboo
Attachments:
[application/octet-stream] RM_5053.patch (8.2K, 3-RM_5053.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py
index b03136891..1b34b9715 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py
@@ -10,6 +10,7 @@
"""Implements View and Materialized View Node"""
import copy
+import re
from functools import wraps
import simplejson as json
@@ -496,7 +497,7 @@ class ViewNode(PGChildNodeView, VacuumSettings, SchemaDiffObjectCompare):
)
)
try:
- SQL, nameOrError = self.getSQL(gid, sid, did, data)
+ SQL, nameOrError = self.getSQL(gid, sid, did, scid, data)
if SQL is None:
return nameOrError
SQL = SQL.strip('\n').strip(' ')
@@ -541,7 +542,7 @@ class ViewNode(PGChildNodeView, VacuumSettings, SchemaDiffObjectCompare):
request.data, encoding='utf-8'
)
try:
- SQL, name = self.getSQL(gid, sid, did, data, vid)
+ SQL, name = self.getSQL(gid, sid, did, scid, data, vid)
if SQL is None:
return name
SQL = SQL.strip('\n').strip(' ')
@@ -678,7 +679,7 @@ class ViewNode(PGChildNodeView, VacuumSettings, SchemaDiffObjectCompare):
except ValueError:
data[k] = v
- sql, nameOrError = self.getSQL(gid, sid, did, data, vid)
+ sql, nameOrError = self.getSQL(gid, sid, did, scid, data, vid)
if sql is None:
return nameOrError
@@ -692,7 +693,7 @@ class ViewNode(PGChildNodeView, VacuumSettings, SchemaDiffObjectCompare):
status=200
)
- def getSQL(self, gid, sid, did, data, vid=None):
+ def getSQL(self, gid, sid, did, scid, data, vid=None):
"""
This function will generate sql from model data
"""
@@ -716,7 +717,21 @@ class ViewNode(PGChildNodeView, VacuumSettings, SchemaDiffObjectCompare):
if 'schema' not in data:
data['schema'] = res['rows'][0]['schema']
- acls = []
+
+ DEL_SQL = None
+ if 'definition' in data:
+ new_def = re.sub(r"\W", "", data['definition']).split('FROM')
+ old_def = re.sub(r"\W", "", res['rows'][0]['definition']
+ ).split('FROM')
+ if 'definition' in data and (
+ old_def[0] != new_def[0]
+ and old_def[0] not in new_def[0]
+ ):
+ DEL_SQL = self.delete(gid=gid, sid=sid, did=did,
+ scid=scid,
+ vid=vid, only_sql=True
+ )
+
try:
acls = render_template(
"/".join([self.template_path, 'sql/allowed_privs.json'])
@@ -740,6 +755,9 @@ class ViewNode(PGChildNodeView, VacuumSettings, SchemaDiffObjectCompare):
SQL = render_template("/".join(
[self.template_path, 'sql/update.sql']), data=data,
o_data=old_data, conn=self.conn)
+
+ if DEL_SQL:
+ SQL = DEL_SQL + SQL
except Exception as e:
current_app.logger.exception(e)
return None, internal_server_error(errormsg=str(e))
@@ -1436,7 +1454,14 @@ class ViewNode(PGChildNodeView, VacuumSettings, SchemaDiffObjectCompare):
if data:
if diff_schema:
data['schema'] = diff_schema
- sql, nameOrError = self.getSQL(gid, sid, did, data, oid)
+ sql, nameOrError = self.getSQL(gid, sid, did, scid, data, oid)
+ if sql.find('DROP VIEW') != -1:
+ sql = gettext("""
+-- Changing the columns in a view requires dropping and re-creating the view.
+-- This may fail if other objects are dependent upon this view,
+-- or may cause procedural functions to fail if they are not modified to
+-- take account of the changes.
+""") + sql
else:
if drop_sql:
sql = self.delete(gid=gid, sid=sid, did=did,
@@ -1544,8 +1569,8 @@ class MViewNode(ViewNode, VacuumSettings):
data['vacuum_data']['reset'] = []
# table vacuum: separate list of changed and reset data for
- if ('vacuum_table' in data):
- if ('changed' in data['vacuum_table']):
+ if 'vacuum_table' in data:
+ if 'changed' in data['vacuum_table']:
for item in data['vacuum_table']['changed']:
if 'value' in item.keys():
if item['value'] is None:
@@ -1578,8 +1603,8 @@ class MViewNode(ViewNode, VacuumSettings):
'value': data['autovacuum_enabled']})
# toast autovacuum: separate list of changed and reset data
- if ('vacuum_toast' in data):
- if ('changed' in data['vacuum_toast']):
+ if 'vacuum_toast' in data:
+ if 'changed' in data['vacuum_toast']:
for item in data['vacuum_toast']['changed']:
if 'value' in item.keys():
toast_key = 'toast_' + item['name']
@@ -1668,14 +1693,14 @@ class MViewNode(ViewNode, VacuumSettings):
if 'value' in item.keys() and item['value'] is not None]
# add table_enabled & toast_enabled settings
- if ('autovacuum_custom' in data and data['autovacuum_custom']):
+ if 'autovacuum_custom' in data and data['autovacuum_custom']:
vacuum_table.append(
{
'name': 'autovacuum_enabled',
'value': str(data['autovacuum_enabled'])
}
)
- if ('toast_autovacuum' in data and data['toast_autovacuum']):
+ if 'toast_autovacuum' in data and data['toast_autovacuum']:
vacuum_table.append(
{
'name': 'toast.autovacuum_enabled',
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/static/js/view.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/static/js/view.js
index e85bd7f15..42f47f192 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/static/js/view.js
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/static/js/view.js
@@ -154,8 +154,29 @@ define('pgadmin.node.view', [
id: 'definition', label: gettext('Code'), cell: 'string',
type: 'text', mode: ['create', 'edit'], group: gettext('Code'),
tabPanelCodeClass: 'sql-code-control',
- control: Backform.SqlCodeControl,
disabled: 'notInSchema',
+ control: Backform.SqlCodeControl.extend({
+ onChange: function() {
+ Backform.SqlCodeControl.prototype.onChange.apply(this, arguments);
+ if(this.model && this.model.changed) {
+ if(this.model.origSessAttrs && (this.model.changed.definition != this.model.origSessAttrs.definition)) {
+ let old_def = this.model.origSessAttrs.definition.replace(/\s/gi, '').split('FROM'),
+ new_def = this.model.changed.definition.replace(/\s/gi, '').split('FROM');
+ if (old_def[0] != new_def[0]) {
+ this.model.warn_text = gettext('Changing the columns in a view requires dropping and re-creating the view. This may fail if other objects are dependent upon this view, or may cause procedural functions to fail if they are not modified to take account of the changes. Do you wish to continue?');
+ } else {
+ this.model.warn_text = undefined;
+ }
+ }
+ else {
+ this.model.warn_text = undefined;
+ }
+ }
+ else {
+ this.model.warn_text = undefined;
+ }
+ },
+ }),
}, pgBrowser.SecurityGroupSchema, {
// Add Privilege Control
id: 'datacl', label: gettext('Privileges'), type: 'collection',
view thread (10+ 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: [pgAdmin4][Patch]: RM 5053 - Getting an error while changing the columns in the existing view
In-Reply-To: <CAFOhELdGhQ1O2R+GLgNj_jbOtUduDt=rsNvwUdwmxeUxAmes6Q@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox