Hi Neel. PFA the revised patch which has changed according to your comments. Please do review it and let me know in case anything is missing.
Regards, Sanket Mehta Sr Software engineer Enterprisedb On Wed, Jan 20, 2016 at 10:20 AM, Neel Patel <neel.pa...@enterprisedb.com> wrote: > Hi Sanket, > > Below are the review comments. > > - When we edit any existing cast node then it gives error "*Response > object has no attribute strip*". This error is coming because generated > SQL is > wrong. > - Unnecessary debug logs are coming on console. Please remove unnecessary > debug logs. > - In some of the sql file, 'qtIdent' and 'qtLiteral' is not used. Please > check all the SQL files. > - "Delete" cast functionality is not working. Error is getting displayed > saying *"syntax error at or near "castsource"*. > - "Delete cascade" functionality is not working - error is getting > displayed saying *"The requested URL not found".* > - Do the proper comments, in some of the function like "script_load" , > comments are wrong. > - Is "configs" really required in __init__.py file ? We have not seen any > usage for this. Please remove it if it is not required. > - Remove commented code from the source file. > > Please check all the generated SQL statements . Test the basic > functionality of "create", "Edit" and "Delete" node before sending patch > file. > > Do let us know for any comments/issues. > > Thanks, > Neel Patel > > On Tue, Jan 19, 2016 at 8:06 PM, Sanket Mehta < > sanket.me...@enterprisedb.com> wrote: > >> Hi, >> >> PFA updated patch for cast module as per check list provided by Neel. >> Please do review it and let me know in case of anything is missing. >> >> >> >> Regards, >> Sanket Mehta >> Sr Software engineer >> Enterprisedb >> >> On Mon, Jan 18, 2016 at 7:16 PM, Sanket Mehta < >> sanket.me...@enterprisedb.com> wrote: >> >>> Hi, >>> >>> PFA patch for cast module. >>> Please do review it and let me know in case of any issue. >>> >>> >>> Regards, >>> Sanket Mehta >>> Sr Software engineer >>> Enterprisedb >>> >> >> >> >> -- >> Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgadmin-hackers >> >> >
diff --git a/web/pgadmin/browser/server_groups/servers/databases/casts/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/casts/__init__.py new file mode 100644 index 0000000..37802b0 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/casts/__init__.py @@ -0,0 +1,402 @@ +########################################################################## +# +# pgAdmin 4 - PostgreSQL Tools +# +# Copyright (C) 2016, The pgAdmin Development Team +# This software is released under the PostgreSQL Licence +# +########################################################################## +import json +from flask import render_template, make_response, current_app, 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 databases +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 CastModule(CollectionNodeModule): + NODE_TYPE = 'cast' + COLLECTION_LABEL = 'Casts' + + def __init__(self, *args, **kwargs): + super(CastModule, 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 cast, when any of the database node is + initialized. + """ + return databases.DatabaseModule.NODE_TYPE + + +blueprint = CastModule(__name__) + + +class CastView(NodeView): + node_type = blueprint.node_type + + parent_ids = [ + {'type': 'int', 'id': 'gid'}, + {'type': 'int', 'id': 'sid'}, + {'type': 'int', 'id': 'did'} + ] + ids = [ + {'type': 'int', 'id': 'cid'} + ] + + operations = dict({ + 'obj': [ + {'get': 'properties', 'delete': 'delete', 'put': 'update'}, + {'get': 'list', 'post': 'create'} + ], + 'children': [{ + 'get': 'children' + }], + 'delete': [{'delete': 'delete'}], + 'nodes': [{'get': 'node'}, {'get': 'nodes'}], + 'sql': [{'get': 'sql'}], + 'msql': [{'get': 'msql'}, {'get': 'msql'}], + 'stats': [{'get': 'statistics'}], + 'dependency': [{'get': 'dependencies'}], + 'dependent': [{'get': 'dependents'}], + 'module.js': [{}, {}, {'get': 'module_js'}], + 'get_type': [{'get': 'get_sourceTarget_type'}, {'get': 'get_sourceTarget_type'}], + 'getfunctions': [{'post': 'get_functions'}, {'post': 'get_functions'}] + }) + + 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( + "cast/js/casts.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 >= 90000: + self.template_path = 'cast/sql/9.0_plus' + + return f(*args, **kwargs) + + return wrap + + + @check_precondition + def list(self, gid, sid, did): + SQL = render_template( + "/".join([self.template_path, 'properties.sql']), + datlastsysoid=self.manager.db_info[did]['datlastsysoid'] + ) + status, res = self.conn.execute_dict(SQL) + + if not status: + return internal_server_error(errormsg=res) + return ajax_response( + response=res['rows'], + status=200 + ) + + @check_precondition + def nodes(self, gid, sid, did): + res = [] + SQL = render_template( + "/".join([self.template_path, 'properties.sql']), + datlastsysoid=self.manager.db_info[did]['datlastsysoid'] + ) + 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-cast" + )) + + return make_json_response( + data=res, + status=200 + ) + + @check_precondition + def properties(self, gid, sid, did, cid): + SQL = render_template( + "/".join([self.template_path, 'properties.sql']), + cid=cid, + datlastsysoid=self.manager.db_info[did]['datlastsysoid'] + ) + 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 cast object + """ + + required_args = [ + 'srctyp', + 'trgtyp' + ] + + 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) + + # we need oid to to add object in tree at browser, below sql will gives the same + SQL = render_template("/".join([self.template_path, 'properties.sql']), + srctyp=data['srctyp'], + trgtyp=data['trgtyp'], + datlastsysoid=self.manager.db_info[did]['datlastsysoid'] + ) + status, cid = self.conn.execute_scalar(SQL) + if not status: + return internal_server_error(errormsg=cid) + + return jsonify( + node=self.blueprint.generate_browser_node( + cid, + data['name'], + icon="pg-icon-cast" + ) + ) + except Exception as e: + return internal_server_error(errormsg=str(e)) + + + @check_precondition + def update(self, gid, sid, did, cid): + """ + This function will update cast object + """ + data = request.form if request.form else json.loads(request.data.decode()) + SQL = self.getSQL(gid, sid, did, data, cid) + 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="Cast updated", + data={ + 'id': cid, + 'sid': sid, + 'gid': gid, + 'did': did + } + ) + else: + return make_json_response( + success=1, + info="Nothing to update", + data={ + 'id': cid, + '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, cid): + """ + This function will drop the cast 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 cast from cid + SQL = render_template("/".join([self.template_path, 'delete.sql']), + cid=cid) + status, res = self.conn.execute_dict(SQL) + if not status: + return internal_server_error(errormsg=res) + + # drop cast + result = res['rows'][0] + SQL = render_template("/".join([self.template_path, 'delete.sql']), + castsource=result['castsource'], + casttarget=result['casttarget'], + 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("Cast dropped"), + data={ + 'id': cid, + '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, cid=None): + """ + This function returns modified SQL + """ + data = request.args + SQL = self.getSQL(gid, sid, did, data, cid) + 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, did, data, cid=None): + """ + This function will return SQL for model data + """ + try: + if cid is not None: + SQL = render_template("/".join([self.template_path, 'properties.sql']), + cid=cid, + datlastsysoid=self.manager.db_info[did]['datlastsysoid']) + 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: + if 'srctyp' in data and 'trgtyp' in data: + SQL = render_template("/".join([self.template_path, 'create.sql']), data=data) + else: + SQL = "-- incomplete definition" + return SQL + + except Exception as e: + return internal_server_error(errormsg=str(e)) + + @check_precondition + def get_functions(self, gid, sid, did, cid=None): + res=[] + data = request.form if request.form else json.loads(request.data.decode()) + SQL = render_template("/".join([self.template_path, 'functions.sql']), + srctyp=data['srctyp'], + trgtyp=data['trgtyp']) + status, rset = self.conn.execute_dict(SQL) + + if not status: + return internal_server_error(errormsg=rset) + + # TODO: add schemaprefix to proname before adding it to value in res + for row in rset['rows']: + res.append({'label': row['proname'], + 'value': row['proname']}) + return make_json_response( + data=res, + status=200 + ) + + @check_precondition + def get_sourceTarget_type(self, gid, sid, did, cid=None): + res = [] + SQL = render_template( + "/".join([self.template_path, 'getsrcandtrgttype.sql']), + cid=cid + ) + status, rset = self.conn.execute_dict(SQL) + + if not status: + return internal_server_error(errormsg=rset) + + res = [{'label': '', 'value': ''}] + for row in rset['rows']: + # TODO: Follow dlgTypeProperty::FillDataType() function before adding typename to res + res.append({'label': row['typname'], + 'value': row['typname']}) + + return make_json_response( + data=res, + status=200 + ) + +CastView.register_node_view(blueprint) \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/casts/static/img/cast.png b/web/pgadmin/browser/server_groups/servers/databases/casts/static/img/cast.png new file mode 100644 index 0000000000000000000000000000000000000000..2be7f3742a760faa7709052669f444ba8949c330 GIT binary patch literal 426 zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbK}a)3{WE0A8=XLIY~`bhs>bEjXs zdtc(<#)_V{^GlchKee~#-UGQ6EB@bqsIhL{|A&u_H*fy`<f;9xUH_jw_c?g*|BIJV zCr<o-{W{~qh5v8gmR`H||NZ->d-wi-{Mh^W@&C`CroDLaf6w;E(Q{(sfz~jV1o;Is zI6S+N2IO!SctjQhX%8@VJDF_<WYl@OIEF}E&OLuysL4Qp`NFZQywjDGIy`p%_#dBn zQ0(`EO7CUHXQe*N`?)=t^Q7D8w8}9V*M&OXmYcSQMTTxXa5eZ^sYbzVjoHtlAM6X> zw)Vt7;XUhu?aCg8-q)T#djsp?1vQn0(#HZ{&avK>G;7M|Kezi*1J|9@wM@A8GIu5a z7k@SvKA%lLfi6)kag8WRNi0dVN-jzTQVd20h6cKZM!E)uAw~vPCdO7KCfWw3Rt5$Z sGgakKH00)|WTsU@G#FTdHGouG8JIydoSGiG2B?9-)78&qol`;+06z@3hyVZp literal 0 HcmV?d00001 diff --git a/web/pgadmin/browser/server_groups/servers/databases/casts/static/img/coll-cast.png b/web/pgadmin/browser/server_groups/servers/databases/casts/static/img/coll-cast.png new file mode 100644 index 0000000000000000000000000000000000000000..09eb65af02c66bd64ab3405c592efe4d90d41c98 GIT binary patch literal 402 zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbK}RDe&2E08|5w`XaeO<lq4NdH}P zr(e5!U!p#*dg;>t_Z}!tZ4Ap#oci#Q{=SXoPoCQE-(<UL)&Gqf|L@xM|LFFF=Pv>e zAO8R0ecP*7DQD0A|MY3{+qb3HuKmA%|NpaR|1ZhRj0c*|SQ6wH%;50sMjDXAS>O>_ z45U54*zIJt9gval>Eak7ak=#TZN6p&0hSA?yGp&5X6Q%he*0e^ToZZWNTsr+b)t)l zj9%4Lq4qZOwfFDczc{Oq<6)rmksEJ&nEiOFw@y3DVz*ZO=8+TAZ=JQC&Ch=IlJ%q0 zn#a%d$On5}eQ!_{`O0<1PkWs^`g)O!!JAm-u{;Xg4zyae#5JNMC9x#cD!C{XNHG{0 z7#ipr8tEDsh8P)GnHXD{m}ncAS{WEv%v6;_(U6;;l9^Ts(O_T+)&Np%Wnc!;aB6z! Q8lVOSPgg&ebxsLQ09Xf~fdBvi literal 0 HcmV?d00001 diff --git a/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/js/casts.js b/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/js/casts.js new file mode 100644 index 0000000..81f3747 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/js/casts.js @@ -0,0 +1,203 @@ +define( + ['jquery', 'underscore', 'underscore.string', 'pgadmin', 'pgadmin.browser', 'alertify', 'pgadmin.browser.collection'], +function($, _, S, pgAdmin, pgBrowser, alertify) { + + if (!pgBrowser.Nodes['coll-cast']) { + var casts = pgAdmin.Browser.Nodes['coll-cast'] = + pgAdmin.Browser.Collection.extend({ + node: 'cast', + label: '{{ _('Casts') }}', + type: 'coll-cast' + }); + }; + + if (!pgBrowser.Nodes['cast']) { + pgAdmin.Browser.Nodes['cast'] = pgAdmin.Browser.Node.extend({ + parent_type: 'database', + type: 'cast', + canDrop: true, + canDropCascade: true, + label: '{{ _('Cast') }}', + hasSQL: true, + Init: function() { + /* Avoid mulitple registration of menus */ + if (this.initialized) + return; + + this.initialized = true; + + pgBrowser.add_menus([{ + name: 'create_cast_on_database', node: 'database', module: this, + applies: ['object', 'context'], callback: 'show_obj_properties', + category: 'create', priority: 4, label: '{{ _('Cast...') }}', + icon: 'wcTabIcon icon-cast', data: {action: 'create'} + },{ + name: 'create_cast_on_coll', node: 'coll-cast', module: this, + applies: ['object', 'context'], callback: 'show_obj_properties', + category: 'create', priority: 4, label: '{{ _('Cast...') }}', + icon: 'wcTabIcon icon-cast', data: {action: 'create'} + },{ + name: 'create_cast', node: 'cast', module: this, + applies: ['object', 'context'], callback: 'show_obj_properties', + category: 'create', priority: 4, label: '{{ _('Cast...') }}', + icon: 'wcTabIcon icon-cast', data: {action: 'create'} + }]); + + }, + model: pgAdmin.Browser.Node.Model.extend({ + defaults: { + name: undefined, + comment: undefined, + encoding: 'UTF8', + srctyp: undefined, + trgtyp: undefined, + proname: undefined, + castcontext: undefined, + syscast: undefined, + description: undefined + }, + schema: [{ + id: 'name', label: '{{ _('Name') }}', cell: 'string', group: '{{ _('Definition') }}', + editable: false, type: 'text', disabled: true + },{ + id: 'oid', label:'{{ _('Oid') }}', cell: 'string', group: '{{ _('Definition') }}', + editable: false, type: 'text', disabled: true, + },{ + id: 'srctyp', label:'{{ _('Source type') }}', url: 'get_type', + type: 'text', group: 'Definition', disabled: function(m) { + return !m.isNew() + }, + transform: function(rows) { + _.each(rows, function(r) { + r['image'] = 'icon-cast'; + }); + return rows; + }, + /* As name is being generated from srctyp and trgtyp, a check has been put in + * control field if both are changed or not and depending upon it, name has been set. + */ + control: Backform.NodeAjaxOptionsControl.extend({ + onChange: function() { + Backform.NodeAjaxOptionsControl.prototype.onChange.apply(this, arguments); + var srcType = this.model.get('srctyp'); + var trgtype = this.model.get('trgtyp'); + if(srcType != undefined && srcType != '' && trgtype != undefined && trgtype != '') + this.model.set("name", srcType+"->"+trgtype); + else + this.model.unset("name"); + } + }) + },{ + id: 'trgtyp', label:'{{ _('Target type') }}', url: 'get_type', + type: 'text', group: 'Definition', disabled: function(m) { + return !m.isNew() + }, + transform: function(rows) { + _.each(rows, function(r) { + r['image'] = 'icon-cast'; + }); + return rows; + }, + /* As name is being generated from srctyp and trgtyp, a check has been put in + * control field if both are changed or not and depending upon it, name has been set. + */ + control: Backform.NodeAjaxOptionsControl.extend({ + onChange: function() { + Backform.NodeAjaxOptionsControl.prototype.onChange.apply(this, arguments); + var srcType = this.model.get('srctyp'); + var trgtype = this.model.get('trgtyp'); + if(srcType != undefined && srcType != '' && trgtype != undefined && trgtype != '') + this.model.set("name", srcType+"->"+trgtype); + else + this.model.unset("name"); + } + }) + },{ + id: 'proname', label:'{{ _('Function') }}', deps:['srctyp', 'trgtyp'], + editable: false, type: 'text', disabled: function(m) { return !m.isNew(); }, + group: 'Definition', + control: 'select', options: function() { + + var srcTyp = this.model.get('srctyp'); + var trgtyp = this.model.get('trgtyp'); + var res = []; + /* On srctyp and trgtyp state change event an ajax call is made to + * fetch list of related functions + */ + if(srcTyp != undefined && srcTyp != '' && trgtyp != undefined && trgtyp != '') + { + var node = this.field.get('schema_node'), + _url = node.generate_url.apply( + node, [ + null, 'getfunctions', this.field.get('node_data'), false, + this.field.get('node_info') + ]); + $.ajax({ + type: 'POST', + timeout: 30000, + url: _url, + cache: false, + data: {"srctyp" : srcTyp, "trgtyp" : trgtyp}, + success: function(res) { + return res.data; + }, + error: function(xhr, status, error) { + try { + var err = $.parseJSON(xhr.responseText); + if (err.success == 0) { + msg = S('{{ _(' + err.errormsg + ')}}').value(); + alertify.error("{{ _('" + err.errormsg + "') }}"); + } + } catch (e) {} + } + }); + } + return res; + } + },{ + id: 'castcontext', label:'{{ _('Context') }}', options:{'onText':'Implicit','offText':'Explicit'}, + editable: false, type: 'switch', disabled: function(m) { return !m.isNew(); }, + group: 'Definition' + },{ + id: 'syscast', label:'{{ _('System Cast?') }}', mode: ['properties'], + editable: false, type: 'text' + },{ + id: 'description', label:'{{ _('Comment') }}', group: '{{ _('Definition') }}', + type: 'text', group: 'Properties' + } + ], + validate: function(keys){ + /* + * Triggers specific error messages for srctyp and + * trgtyp if any one of them is not selected + */ + var srctype = this.get('srctyp'); + var trgtype = this.get('trgtyp'); + if (_.isUndefined(srctype) || _.isNull(srctype) || String(srctype).replace(/^\s+|\s+$/g, '') == '') { + var msg = '{{ _('Source type must be selected!') }}'; + this.errorModel.set('srctyp', msg); + return msg; + } + else + { + this.errorModel.unset('srctyp'); + } + + if (_.isUndefined(trgtype) || _.isNull(trgtype) || String(trgtype).replace(/^\s+|\s+$/g, '') == '') { + var msg = '{{ _('Target type must be selected!') }}'; + this.errorModel.set('trgtyp', msg); + return msg; + } + else + { + this.errorModel.unset('trgtyp'); + } + this.trigger('on-status-clear'); + return null; + } + }) + }); + + } + return pgBrowser.Nodes['coll-cast']; +}); \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/create.sql new file mode 100644 index 0000000..cee46b3 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/create.sql @@ -0,0 +1,19 @@ +{# ========== Below SQL will create cast =========== #} +{% if data and data.srctyp and data.trgtyp %} + CREATE CAST ({{data.srctyp}} AS {{data.trgtyp}}) + {% if data.proname %} + WITH FUNCTION {{data.proname}} + {% else %} + WITHOUT FUNCTION + {% endif %} + {% if data.castcontext == 'true' %} + AS IMPLICIT + {% endif %}; + +{# ========== Below creates description for cast =========== #} + {% if data.description %} + COMMENT ON CAST ({{ data.srctyp }} AS {{ data.trgtyp }}) + IS {{ data.description|qtLiteral }}; + {% endif %} + +{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/delete.sql new file mode 100644 index 0000000..cb5686f --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/delete.sql @@ -0,0 +1,12 @@ +{% if cid %} + SELECT + format_type(ca.castsource, null) as castsource, + format_type(ca.casttarget, null) as casttarget + FROM + pg_cast ca + WHERE + ca.oid = {{cid}}::OID; +{% endif %} +{% if castsource and casttarget %} +DROP CAST ({{castsource}} AS {{casttarget}}) {% if cascade %}CASCADE{%endif%}; +{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/functions.sql b/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/functions.sql new file mode 100644 index 0000000..84a1b15 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/functions.sql @@ -0,0 +1,17 @@ +SELECT + proname, + nspname, + proargtypes +FROM + pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace +WHERE + proargtypes[0] = (SELECT t.oid FROM pg_type t WHERE format_type(t.oid, NULL) = {{srctyp|qtLiteral}}) + AND prorettype = (SELECT t.oid FROM pg_type t WHERE format_type(t.oid, NULL) = {{trgtyp|qtLiteral}}) + AND + CASE + WHEN array_length(proargtypes,1) = 2 THEN + proargtypes[1] = 23 + WHEN array_length(proargtypes,1) >= 3 THEN + proargtypes[1] = 23 AND proargtypes[2] = 16 + ELSE TRUE + END diff --git a/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/getsrcandtrgttype.sql b/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/getsrcandtrgttype.sql new file mode 100644 index 0000000..1e05ccb --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/getsrcandtrgttype.sql @@ -0,0 +1,43 @@ +SELECT + * +FROM ( + SELECT format_type(t.oid,NULL) AS typname, + CASE + WHEN typelem > 0 THEN typelem + ELSE t.oid + END as elemoid, + typlen, + typtype, + t.oid, + nspname, + (SELECT COUNT(1) FROM pg_type t2 WHERE t2.typname = t.typname) > 1 AS isdup + FROM + pg_type t + JOIN pg_namespace nsp ON typnamespace=nsp.oid + WHERE + (NOT (typname = 'unknown' + AND nspname = 'pg_catalog')) + AND typisdefined + AND typtype IN ('b', 'c', 'e', 'r') + AND NOT EXISTS ( + SELECT + 1 + FROM + pg_class + WHERE + relnamespace = typnamespace + AND relname = typname + AND relkind != 'c') + AND (typname NOT LIKE '_%' + OR NOT EXISTS ( + SELECT + 1 + FROM + pg_class + WHERE + relnamespace = typnamespace + AND relname = SUBSTRING(typname FROM 2)::name + AND relkind != 'c')) + AND nsp.nspname != 'information_schema' ) AS dummy +ORDER BY + nspname <> 'pg_catalog', nspname <> 'public', nspname, 1 diff --git a/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/properties.sql new file mode 100644 index 0000000..2445e2a --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/properties.sql @@ -0,0 +1,59 @@ +{# ======== Below SQL will fetch id for given cast ======== #} +{% if srctyp and trgtyp %} + SELECT + ca.oid + FROM pg_cast ca + WHERE ca.castsource = (SELECT t.oid FROM pg_type t WHERE format_type(t.oid, NULL) = {{srctyp|qtLiteral}}) + AND ca.casttarget = (SELECT t.oid FROM pg_type t WHERE format_type(t.oid, NULL) = {{trgtyp|qtLiteral}}) + {% if datlastsysoid %} + AND ca.oid > {{datlastsysoid}}::OID + {% endif %} + +{# ===== Below SQL will fetch properties for particular cast if oid is provided or all user casts if not provided ==== #} +{% else %} + SELECT + ca.oid, + CASE + WHEN {{datlastsysoid}}::OID > ca.oid then 'YES' ELSE 'NO' + END AS syscast, + CASE + WHEN ca.castcontext = 'a' THEN 'ASSIGNMENT' + WHEN ca.castcontext = 'i' THEN 'IMPLICIT' + WHEN ca.castcontext = 'e' THEN 'EXPLICIT' + END AS castcontext, + CASE + WHEN proname IS NULL THEN 'binary compatible' + END AS proname, + ca.castfunc, + format_type(st.oid,NULL) AS srctyp, + format_type(tt.oid,tt.typtypmod) AS trgtyp, + ns.nspname AS srcnspname, + nt.nspname AS trgnspname, + np.nspname AS pronspname, + description, + concat(format_type(st.oid,NULL),'->',format_type(tt.oid,tt.typtypmod)) as name + FROM pg_cast ca + JOIN pg_type st ON st.oid=castsource + JOIN pg_namespace ns ON ns.oid=st.typnamespace + JOIN pg_type tt ON tt.oid=casttarget + JOIN pg_namespace nt ON nt.oid=tt.typnamespace + LEFT JOIN pg_proc pr ON pr.oid=castfunc + LEFT JOIN pg_namespace np ON np.oid=pr.pronamespace + LEFT OUTER JOIN pg_description des ON (des.objoid=ca.oid AND des.objsubid=0 AND des.classoid='pg_cast'::regclass) + + {% if cid %} + WHERE ca.oid={{cid}}::int + {% endif %} + +--TODO: add check for showSystemObject(). currently assumed as false + {% if datlastsysoid %} + {% if cid %} + AND + {% else %} + WHERE + {% endif %} + ca.oid > {{datlastsysoid}}::OID + {% endif %} + + ORDER BY st.typname, tt.typname +{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/update.sql new file mode 100644 index 0000000..a17b1e6 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/casts/templates/cast/sql/9.0_plus/update.sql @@ -0,0 +1,6 @@ +{# ===========Below SQL will update cast comments=================== #} + +{% if data and data.description and data.description != o_data.description %} + COMMENT ON CAST ({{o_data.srctyp}} AS {{o_data.trgtyp}}) + IS {{ data.description|qtLiteral }}; +{% endif %} \ No newline at end of file
-- Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers