Hi,
Fixed PostGIS datatype on SQL tab for Table, column, Foreign Table and Type
node.
Please find the attached updated patch.
Thanks,
Khushboo
On Thu, Jun 1, 2017 at 6:36 PM, Murtuza Zabuawala <
[email protected]> wrote:
>
> On Thu, Jun 1, 2017 at 5:27 PM, Khushboo Vashi <
> [email protected]> wrote:
>
>> Hi,
>>
>>
>> On Thu, Jun 1, 2017 at 4:57 PM, Murtuza Zabuawala <
>> [email protected]> wrote:
>>
>>> Hi Khushboo,
>>>
>>> Please include Type node and Foreign Table node in your fix :-)
>>>
>>> I don't think Type node requires this change.
>>
>> Yes, It do require because user can create composite type from existing
> types.
> [image: Inline image 1]
>
>> Foreign table changes added in the attached updated patch.
>>
>>> --
>>>
>> Thanks Murtuza for pointing this out.
> Regards,
>>> Murtuza Zabuawala
>>> EnterpriseDB: http://www.enterprisedb.com
>>> The Enterprise PostgreSQL Company
>>>
>>> On Thu, Jun 1, 2017 at 3:23 PM, Khushboo Vashi <
>>> [email protected]> wrote:
>>>
>>>> Hi,
>>>>
>>>> Please find the attached patch to fix RM #2324 : PostGIS datatypes not
>>>> showing up properly on SQL tab.
>>>>
>>>> Thanks,
>>>> Khushboo
>>>>
>>>>
>>>>
>>>> --
>>>> Sent via pgadmin-hackers mailing list ([email protected])
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgadmin-hackers
>>>>
>>>>
>>>
>>
>
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py
index 15455e8..2ee6a43 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py
@@ -830,7 +830,7 @@ class ForeignTableView(PGChildNodeView, DataTypeReader):
data['columns'] = col_data
SQL = render_template("/".join([self.template_path,
- 'create.sql']), data=data)
+ 'create.sql']), data=data, is_sql=True)
sql_header = u"""-- FOREIGN TABLE: {0}
@@ -1093,10 +1093,10 @@ class ForeignTableView(PGChildNodeView, DataTypeReader):
substr = c['fulltype'][c['fulltype'].find("(") + 1:c['fulltype'].find(")")]
typlen = substr.split(",")
if len(typlen) > 1:
- c['typlen'] = int(typlen[0])
- c['precision'] = int(typlen[1])
+ c['typlen'] = int(typlen[0]) if typlen[0].isdigit() else typlen[0]
+ c['precision'] = int(typlen[1]) if typlen[1].isdigit() else typlen[1]
else:
- c['typlen'] = int(typlen[0])
+ c['typlen'] = int(typlen[0]) if typlen[0].isdigit() else typlen[0]
c['precision'] = None
# Get formatted Column Options
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/create.sql
index cbffa89..d055de0 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/create.sql
@@ -4,7 +4,7 @@
CREATE FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}(
{% if data.columns %}
{% for c in data.columns %}
- {{conn|qtIdent(c.attname)}} {{conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}{% if c.coloptions %}
+ {{conn|qtIdent(c.attname)}} {% if is_sql %}{{ c.fulltype }}{% else %}{{conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}{% endif %}{% if c.coloptions %}
{% for o in c.coloptions %}{% if o.option and o.value %}
{% if loop.first %} OPTIONS ({% endif %}{% if not loop.first %}, {% endif %}{{o.option}} {{o.value|qtLiteral}}{% if loop.last %}){% endif %}{% endif %}
{% endfor %}{% endif %}{% if c.attnotnull %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/create.sql
index 093eec0..bccebd4 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/create.sql
@@ -7,7 +7,7 @@ CREATE FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}(
{% for c in data.columns %}
{% if (not c.inheritedfrom or c.inheritedfrom =='' or c.inheritedfrom == None or c.inheritedfrom == 'None' ) %}
{% if is_columns.append('1') %}{% endif %}
- {{conn|qtIdent(c.attname)}} {{conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}{% if c.coloptions %}
+ {{conn|qtIdent(c.attname)}} {% if is_sql %}{{ c.fulltype }}{% else %}{{conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}{% endif %}{% if c.coloptions %}
{% for o in c.coloptions %}{% if o.option and o.value %}
{% if loop.first %} OPTIONS ({% endif %}{% if not loop.first %}, {% endif %}{{o.option}} {{o.value|qtLiteral}}{% if loop.last %}){% endif %}{% endif %}
{% endfor %}{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/default/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/default/create.sql
index a1a8743..eb1b753 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/default/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/default/create.sql
@@ -3,7 +3,7 @@
CREATE FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}(
{% if data.columns %}
{% for c in data.columns %}
- {{conn|qtIdent(c.attname)}} {{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}} {% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}{% if c.attnotnull %}
+ {{conn|qtIdent(c.attname)}} {% if is_sql %}{{ c.fulltype }}{% else %}{{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}} {% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}{% endif %}{% if c.attnotnull %}
NOT NULL{% else %} NULL{% endif %}
{% if not loop.last %},
{% endif %}{% endfor -%}{% endif %}
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 99f0ddc..5b36bbc 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
@@ -2540,7 +2540,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings):
# If the request for new object which do not have did
table_sql = render_template("/".join([self.template_path,
'create.sql']),
- data=data, conn=self.conn)
+ data=data, conn=self.conn, is_sql=True)
# Add into main sql
table_sql = re.sub('\n{2,}', '\n\n', table_sql)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/column/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/column/__init__.py
index d9e688e..49db489 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/column/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/column/__init__.py
@@ -750,7 +750,7 @@ class ColumnsView(PGChildNodeView, DataTypeReader):
except Exception as e:
return internal_server_error(errormsg=str(e))
- def get_sql(self, scid, tid, clid, data):
+ def get_sql(self, scid, tid, clid, data, is_sql=False):
"""
This function will genrate sql from model data
"""
@@ -819,7 +819,7 @@ class ColumnsView(PGChildNodeView, DataTypeReader):
self.acl)
# If the request for new object which do not have did
SQL = render_template("/".join([self.template_path, 'create.sql']),
- data=data, conn=self.conn)
+ data=data, conn=self.conn, is_sql=is_sql)
return SQL, data['name'] if 'name' in data else old_data['name']
@check_precondition
@@ -863,7 +863,7 @@ class ColumnsView(PGChildNodeView, DataTypeReader):
# We will add table & schema as well
data = self._formatter(scid, tid, clid, data)
- SQL, name = self.get_sql(scid, tid, None, data)
+ SQL, name = self.get_sql(scid, tid, None, data, is_sql=True)
if not isinstance(SQL, (str, unicode)):
return SQL
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/default/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/default/create.sql
index 0fce446..5bc521c 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/default/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/default/create.sql
@@ -5,7 +5,7 @@
{### Add column ###}
{% if data.name and data.cltype %}
ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
- ADD COLUMN {{conn|qtIdent(data.name)}} {{ GET_TYPE.CREATE_TYPE_SQL(conn, data.cltype, data.attlen, data.attprecision, data.hasSqrBracket) }}{% if data.collspcname %}
+ ADD COLUMN {{conn|qtIdent(data.name)}} {% if is_sql %}{{data.displaytypname}}{% else %}{{ GET_TYPE.CREATE_TYPE_SQL(conn, data.cltype, data.attlen, data.attprecision, data.hasSqrBracket) }}{% endif %}{% if data.collspcname %}
COLLATE {{data.collspcname}}{% endif %}{% if data.attnotnull %}
NOT NULL{% endif %}{% if data.defval %}
DEFAULT {{data.defval}}{% endif %};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/create.sql
index c21a661..f8e858f 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/create.sql
@@ -44,7 +44,7 @@ CREATE {% if data.relpersistence %}UNLOGGED {% endif %}TABLE {{conn|qtIdent(data
{% if c.name and c.cltype %}
{% if loop.index != 1 %},
{% endif %}
- {{conn|qtIdent(c.name)}} {{ GET_TYPE.CREATE_TYPE_SQL(conn, c.cltype, c.attlen, c.attprecision, c.hasSqrBracket) }}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.attnotnull %} NOT NULL{% endif %}{% if c.defval %} DEFAULT {{c.defval}}{% endif %}
+ {{conn|qtIdent(c.name)}} {% if is_sql %}{{c.displaytypname}}{% else %}{{ GET_TYPE.CREATE_TYPE_SQL(conn, c.cltype, c.attlen, c.attprecision, c.hasSqrBracket) }}{% endif %}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.attnotnull %} NOT NULL{% endif %}{% if c.defval %} DEFAULT {{c.defval}}{% endif %}
{% endif %}
{% endfor %}
{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/__init__.py
index 9c88c63..45f8bf3 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/__init__.py
@@ -461,7 +461,7 @@ class TypeView(PGChildNodeView, DataTypeReader):
'collation': full_collate, 'cltype': row['type'],
'tlength': t_len, 'precision': t_prec,
'is_tlength': is_tlength, 'is_precision': is_precision,
- 'hasSqrBracket': row['hasSqrBracket']})
+ 'hasSqrBracket': row['hasSqrBracket'], 'fulltype': row['fulltype']})
# Adding both results
res['member_list'] = ', '.join(properties_list)
@@ -1150,7 +1150,7 @@ class TypeView(PGChildNodeView, DataTypeReader):
return data
- def get_sql(self, gid, sid, data, scid, tid=None):
+ def get_sql(self, gid, sid, data, scid, tid=None, is_sql=False):
"""
This function will genrate sql from model data
"""
@@ -1240,7 +1240,7 @@ class TypeView(PGChildNodeView, DataTypeReader):
SQL = render_template("/".join([self.template_path,
'create.sql']),
- data=data, conn=self.conn)
+ data=data, conn=self.conn, is_sql=is_sql)
return SQL, data['name'] if 'name' in data else old_data['name']
@@ -1302,7 +1302,7 @@ class TypeView(PGChildNodeView, DataTypeReader):
if data[k] == '-':
data[k] = None
- SQL, name = self.get_sql(gid, sid, data, scid, tid=None)
+ SQL, name = self.get_sql(gid, sid, data, scid, tid=None, is_sql=True)
# Most probably this is due to error
if not isinstance(SQL, (str, unicode)):
return SQL
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/default/additional_properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/default/additional_properties.sql
index 172f6bb..027c1f8 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/default/additional_properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/default/additional_properties.sql
@@ -2,7 +2,8 @@
{% if type == 'c' %}
SELECT attnum, attname, format_type(t.oid,NULL) AS typname, attndims, atttypmod, nsp.nspname,
(SELECT COUNT(1) from pg_type t2 WHERE t2.typname=t.typname) > 1 AS isdup,
- collname, nspc.nspname as collnspname, att.attrelid
+ collname, nspc.nspname as collnspname, att.attrelid,
+ format_type(t.oid, att.atttypmod) AS fulltype
FROM pg_attribute att
JOIN pg_type t ON t.oid=atttypid
JOIN pg_namespace nsp ON t.typnamespace=nsp.oid
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/default/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/default/create.sql
index 7598294..f2d6560 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/default/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/default/create.sql
@@ -8,7 +8,7 @@ CREATE TYPE {{ conn|qtIdent(data.schema, data.name) }};
{### Composite Type ###}
{% if data and data.typtype == 'c' %}
CREATE TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %} AS
-({{"\n\t"}}{% if data.composite %}{% for d in data.composite %}{% if loop.index != 1 %},{{"\n\t"}}{% endif %}{{ conn|qtIdent(d.member_name) }} {{ GET_TYPE.CREATE_TYPE_SQL(conn, d.cltype, d.tlength, d.precision, d.hasSqrBracket) }}{% if d.collation %} COLLATE {{d.collation}}{% endif %}{% endfor %}{% endif %}{{"\n"}});
+({{"\n\t"}}{% if data.composite %}{% for d in data.composite %}{% if loop.index != 1 %},{{"\n\t"}}{% endif %}{{ conn|qtIdent(d.member_name) }} {% if is_sql %}{{ d.fulltype }}{% else %}{{ GET_TYPE.CREATE_TYPE_SQL(conn, d.cltype, d.tlength, d.precision, d.hasSqrBracket) }}{% endif %}{% if d.collation %} COLLATE {{d.collation}}{% endif %}{% endfor %}{% endif %}{{"\n"}});
{% endif %}
{### Enum Type ###}
{% if data and data.typtype == 'e' %}
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
