Subject: [pgAdmin4][Patch]: Fixed RM 2324 - PostGIS datatypes not showing up properly on SQL tab.

From: Khushboo Vashi <[email-masked]>
Date: Thu, 1 Jun 2017 15:23:27 +0530

Hi,

Please find the attached patch to fix RM #2324 : PostGIS datatypes not
showing up properly on SQL tab.

Thanks,
Khushboo
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 %}

-- 
Sent via pgadmin-hackers mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

---

From: Murtuza Zabuawala <[email-masked]>
Date: Thu, 1 Jun 2017 16:57:14 +0530

Hi Khushboo,

Please include Type node and Foreign Table node in your fix :-)

--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

---

From: Khushboo Vashi <[email-masked]>
Date: Thu, 1 Jun 2017 17:27:55 +0530

Hi,

On Thu, Jun 1, 2017 at 4:57 PM, Murtuza Zabuawala <
[email-masked]> wrote:

> I don't think Type node requires this change.

Foreign table changes added in the attached updated patch.

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

     @check_precondition

---

From: Murtuza Zabuawala <[email-masked]>
Date: Thu, 1 Jun 2017 18:36:28 +0530

On Thu, Jun 1, 2017 at 5:27 PM, Khushboo Vashi <
[email-masked]> wrote:

> Hi,
>
>
> On Thu, Jun 1, 2017 at 4:57 PM, Murtuza Zabuawala <murtuza.zabuawala@
> enterprisedb.com> wrote:
>
>> Hi Khushboo,
>>
> Yes, It do require because user can create composite type from existing
types.

---

From: Khushboo Vashi <[email-masked]>
Date: Mon, 5 Jun 2017 13:02:12 +0530

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-masked]> wrote:

>> Thanks Murtuza for pointing this out.

 

 
         # Add into main sql

     @check_precondition

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):

 
@@ -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' %}

---

From: Dave Page <[email-masked]>
Date: Tue, 6 Jun 2017 13:12:26 +0100

Hi

The table and column dialogues seem to have related problems. For example,
the table dialogue shows the length of the geometry field in the test case
as 847,380, whilst the column dialogue shows no length at all.

Also, I there should be a space between the , and value in the_geom
geometry(MultiPolygon,3310).

Thanks.

On Mon, Jun 5, 2017 at 8:32 AM, Khushboo Vashi <
[email-masked]> wrote:

> Hi,
>
> Fixed PostGIS datatype on SQL tab for Table, column, Foreign Table and
> Type node.
>
> On Thu, Jun 1, 2017 at 6:36 PM, Murtuza Zabuawala <murtuza.zabuawala@
> enterprisedb.com> wrote:
>
>>

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

---

From: Khushboo Vashi <[email-masked]>
Date: Tue, 6 Jun 2017 17:53:49 +0530

On 6 Jun 2017 17:42, "Dave Page" <[email-masked]> wrote:

Hi

What should be there in length and precision fields in this case?

Thanks.

On Mon, Jun 5, 2017 at 8:32 AM, Khushboo Vashi <khushboo.vashi@enterprisedb.

---

From: Dave Page <[email-masked]>
Date: Tue, 6 Jun 2017 14:06:10 +0100

> What should be there in length and precision fields in this case?

I have no idea :-). What did pgAdmin 3 do here? I assume there's some way
to decode the typmod into the desired values, and map them back to
appropriate SQL.

>>>>>> Please find the attached patch to fix RM #2324 : PostGIS datatypes
>>>>>> not showing up properly on SQL tab.
>>>>>>
>>>>>> Thanks,
>>>>>> Khushboo
>>>>>>
>>>>>>
>>>>>>
>>>>>> --

---

From: Khushboo Vashi <[email-masked]>
Date: Tue, 6 Jun 2017 18:45:00 +0530

On 6 Jun 2017 18:36, "Dave Page" <[email-masked]> wrote:

On Tue, Jun 6, 2017 at 1:23 PM, Khushboo Vashi <khushboo.vashi@enterprisedb.

I will try to figure out the way.
pgAdmin 3 has the same issue, so I have asked for the suggestion.

---

From: Dave Page <[email-masked]>
Date: Tue, 6 Jun 2017 14:19:10 +0100

On Tue, Jun 6, 2017 at 2:15 PM, Khushboo Vashi <
[email-masked]> wrote:

>> The table and column dialogues seem to have related problems. For
>> example, the table dialogue shows the length of the geometry field in the
>> test case as 847,380, whilst the column dialogue shows no length at all.
>>

Oh, OK - thanks. Maybe we need to reuse the length and precision fields for
these types. That is, I believe, essentially how PostGIS passes extra info
to the column definitions.

>>>>> Yes, It do require because user can create composite type from
>>>> existing types.
>>>>

---

From: Khushboo Vashi <[email-masked]>
Date: Fri, 9 Jun 2017 14:48:12 +0530

Hi,

Fixed the PostGIS Datatypes in SQL tab, Create / Update dialogues for
Table, Column, Foreign Table and Type node.

Regarding  the review comment given by Dave (Also, I there should be a
space between the , and value in the_geom geometry(MultiPolygon,3310).),
won't be fixed as this format is returned by format_type() function itself.

As per the conversation with Dave, I have logged RM #2473
<https://redmine.postgresql.org/issues/2473> to add support for PostGIS
Datatypes.

Thanks,
Khushboo

On Tue, Jun 6, 2017 at 6:49 PM, Dave Page <[email-masked]> wrote:

> Oh, OK - thanks. Maybe we need to reuse the length and precision fields
> for these types. That is, I believe, essentially how PostGIS passes extra
> info to the column definitions.
>
>
>>
>>

 
 
index 99f0ddc..4f6572d 100644
@@ -652,20 +652,24 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings):
                     column['isdup'], column['attndims'], column['atttypmod']
                 )
 
+                length = False
+                precision = False
+                if 'elemoid' in column:
+                    length, precision, typeval = self.get_length_precision(column['elemoid'])
+
                 # If we have length & precision both
-                matchObj = re.search(r'(\d+),(\d+)', fulltype)
-                if matchObj:
+                if length and precision:
+                    matchObj = re.search(r'(\d+),(\d+)', fulltype)
                     column['attlen'] = matchObj.group(1)
                     column['attprecision'] = matchObj.group(2)
-                else:
+                elif length:
                     # If we have length only
                     matchObj = re.search(r'(\d+)', fulltype)
-                    if matchObj:
-                        column['attlen'] = matchObj.group(1)
-                        column['attprecision'] = None
-                    else:
-                        column['attlen'] = None
-                        column['attprecision'] = None
+                    column['attlen'] = matchObj.group(1)
+                    column['attprecision'] = None
+                else:
+                    column['attlen'] = None
+                    column['attprecision'] = None
 
                 SQL = render_template("/".join([self.column_template_path,
                                                 'is_referenced.sql']),
@@ -2540,7 +2544,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..7a28cda 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
@@ -343,21 +343,26 @@ class ColumnsView(PGChildNodeView, DataTypeReader):
             data['isdup'], data['attndims'], data['atttypmod']
         )
 
+        length = False
+        precision = False
+        if 'elemoid' in data:
+            length, precision, typeval = self.get_length_precision(data['elemoid'])
+
         import re
         # If we have length & precision both
-        matchObj = re.search(r'(\d+),(\d+)', fulltype)
-        if matchObj:
+
+        if length and precision:
+            matchObj = re.search(r'(\d+),(\d+)', fulltype)
             data['attlen'] = matchObj.group(1)
             data['attprecision'] = matchObj.group(2)
-        else:
+        elif length:
             # If we have length only
             matchObj = re.search(r'(\d+)', fulltype)
-            if matchObj:
-                data['attlen'] = matchObj.group(1)
-                data['attprecision'] = None
-            else:
-                data['attlen'] = None
-                data['attprecision'] = None
+            data['attlen'] = matchObj.group(1)
+            data['attprecision'] = None
+        else:
+            data['attlen'] = None
+            data['attprecision'] = None
 
         # We need to fetch inherited tables for each table
         SQL = render_template("/".join([self.template_path,
@@ -750,7 +755,7 @@ class ColumnsView(PGChildNodeView, DataTypeReader):
         except Exception as e:
             return internal_server_error(errormsg=str(e))
 
@@ -819,7 +824,7 @@ class ColumnsView(PGChildNodeView, DataTypeReader):
 
     @check_precondition
@@ -863,7 +868,7 @@ class ColumnsView(PGChildNodeView, DataTypeReader):
             # We will add table & schema as well
             data = self._formatter(scid, tid, clid, data)

diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/9.1_plus/properties.sql
index 22cef71..32d5cde 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/9.1_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/9.1_plus/properties.sql
@@ -2,6 +2,7 @@ SELECT att.attname as name, att.*, def.*, pg_catalog.pg_get_expr(def.adbin, def.
         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,
+        CASE WHEN ty.typelem > 0 THEN ty.typelem ELSE ty.oid END as elemoid,
         tn.nspname as typnspname, et.typname as elemtypname,
         ty.typstorage AS defaultstorage, cl.relname, na.nspname,
         concat(quote_ident(na.nspname) ,'.', quote_ident(cl.relname)) AS parent_tbl,
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/column/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/default/properties.sql
index 1325f4a..36d1e1a 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/default/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/default/properties.sql
@@ -2,6 +2,7 @@ SELECT att.attname as name, att.*, def.*, pg_catalog.pg_get_expr(def.adbin, def.
         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,
+        CASE WHEN ty.typelem > 0 THEN ty.typelem ELSE ty.oid END as elemoid,
         tn.nspname as typnspname, et.typname as elemtypname,
         ty.typstorage AS defaultstorage, cl.relname, na.nspname,
         quote_ident(na.nspname) || '.' || quote_ident(cl.relname) AS parent_tbl,
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 d3070c0..b68ac5a 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
@@ -414,12 +414,12 @@ class TypeView(PGChildNodeView, DataTypeReader):
 
             for row in rset['rows']:
                 # We will fetch Full type name
-                fulltype = self.get_full_type(
-                    row['collnspname'], row['typname'],
-                    row['isdup'], row['attndims'], row['atttypmod']
-                )
+                # fulltype = self.get_full_type(
+                #     row['collnspname'], row['typname'],
+                #     row['isdup'], row['attndims'], row['atttypmod']
+                # )
 
-                typelist = ' '.join([row['attname'], fulltype])
+                typelist = ' '.join([row['attname'], row['fulltype']])
                 if not row['collname'] or (row['collname'] == 'default'
                                            and row['collnspname'] == 'pg_catalog'):
                     full_collate = ''
@@ -431,25 +431,26 @@ class TypeView(PGChildNodeView, DataTypeReader):
                 typelist += collate
                 properties_list.append(typelist)
 
+                is_tlength = False
+                is_precision = False
+                if 'elemoid' in row:
+                    is_tlength, is_precision, typeval = self.get_length_precision(row['elemoid'])
+
                 # Below logic will allow us to split length, precision from type name for grid
                 import re
                 # If we have length & precision both
-                matchObj = re.search(r'(\d+),(\d+)', fulltype)
-                if matchObj:
+                if is_tlength and is_precision:
+                    matchObj = re.search(r'(\d+),(\d+)', row['fulltype'])
                     t_len = matchObj.group(1)
                     t_prec = matchObj.group(2)
-                else:
+                elif is_tlength:
                     # If we have length only
-                    matchObj = re.search(r'(\d+)', fulltype)
-                    if matchObj:
-                        t_len = matchObj.group(1)
-                        t_prec = None
-                    else:
-                        t_len = None
-                        t_prec = None
-
-                is_tlength = True if t_len else False
-                is_precision = True if t_prec else False
+                    matchObj = re.search(r'(\d+)', row['fulltype'])
+                    t_len = matchObj.group(1)
+                    t_prec = None
+                else:
+                    t_len = None
+                    t_prec = None
 
                 type_name = DataTypeReader.parse_type_name(row['typname'])
 
@@ -461,7 +462,7 @@ class TypeView(PGChildNodeView, DataTypeReader):
 
             # Adding both results
             res['member_list'] = ', '.join(properties_list)
@@ -1152,7 +1153,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
         """
@@ -1262,7 +1263,7 @@ class TypeView(PGChildNodeView, DataTypeReader):

 
@@ -1324,7 +1325,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..0c31d46 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,9 @@
 {% 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,
+    CASE WHEN t.typelem > 0 THEN t.typelem ELSE t.oid END as elemoid
 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' %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/utils.py
index a09cfff..3cc31d2 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/utils.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/utils.py
@@ -103,25 +103,11 @@ class DataTypeReader:
                 min_val = 0
                 max_val = 0
 
-                # Check against PGOID for specific type
+                # Check if the type will have length and precision or not
                 if row['elemoid']:
-                    if row['elemoid'] in (1560, 1561, 1562, 1563, 1042, 1043,
-                                          1014, 1015):
-                        typeval = 'L'
-                    elif row['elemoid'] in (1083, 1114, 1115, 1183, 1184, 1185,
-                                            1186, 1187, 1266, 1270):
-                        typeval = 'D'
-                    elif row['elemoid'] in (1231, 1700):
-                        typeval = 'P'
-                    else:
-                        typeval = ' '
-
-                # Set precision & length/min/max values
-                if typeval == 'P':
-                    precision = True
+                    length, precision, typeval = self.get_length_precision(row['elemoid'])
 
-                if precision or typeval in ('L', 'D'):
-                    length = True
+                if length:
                     min_val = 0 if typeval == 'D' else 1
                     if precision:
                         max_val = 1000
@@ -143,6 +129,34 @@ class DataTypeReader:
 
         return True, res
 
+    @staticmethod
+    def get_length_precision(elemoid):
+        precision = False
+        length = False
+        typeval = ''
+
+        # Check against PGOID for specific type
+        if elemoid:
+            if elemoid in (1560, 1561, 1562, 1563, 1042, 1043,
+                                  1014, 1015):
+                typeval = 'L'
+            elif elemoid in (1083, 1114, 1115, 1183, 1184, 1185,
+                                    1186, 1187, 1266, 1270):
+                typeval = 'D'
+            elif elemoid in (1231, 1700):
+                typeval = 'P'
+            else:
+                typeval = ' '
+
+        # Set precision & length/min/max values
+        if typeval == 'P':
+            precision = True
+
+        if precision or typeval in ('L', 'D'):
+            length = True
+
+        return length, precision, typeval
+
     def get_full_type(self, nsp, typname, isDup, numdims, typmod):
         """
         Returns full type name with Length and Precision.

---

From: Dave Page <[email-masked]>
Date: Mon, 12 Jun 2017 12:49:26 +0100

Thanks, applied.

On Fri, Jun 9, 2017 at 10:18 AM, Khushboo Vashi <
[email-masked]> wrote:

>>> I have no idea :-). What did pgAdmin 3 do here? I assume there's some
>>> way to decode the typmod into the desired values, and map them back to
>>> appropriate SQL.
>>>

---

From: Dave Page <[email-masked]>
Date: Mon, 12 Jun 2017 15:54:26 +0100

Hi Khushboo,

Looks like this patch broke the regressions tests :-(. Can you take a look
ASAP please?

Thanks!

2017-06-12 10:04:11,938: INFO werkzeug: 127.0.0.1 - - [12/Jun/2017
10:04:11] "GET /browser/table/sql/1/1/12669/2200/81920 HTTP/1.1" 500 -
Traceback (most recent call last):
  File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/
lib/python2.7/site-packages/flask/app.py", line 2000, in __call__
    return self.wsgi_app(environ, start_response)
  File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/
lib/python2.7/site-packages/flask/app.py", line 1991, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/
lib/python2.7/site-packages/flask/app.py", line 1567, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/
lib/python2.7/site-packages/flask/app.py", line 1988, in wsgi_app
    response = self.full_dispatch_request()
  File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/
lib/python2.7/site-packages/flask/app.py", line 1641, in
full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/
lib/python2.7/site-packages/flask/app.py", line 1544, in
handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/
lib/python2.7/site-packages/flask/app.py", line 1639, in
full_dispatch_request
    rv = self.dispatch_request()
  File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/
lib/python2.7/site-packages/flask/app.py", line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/
lib/python2.7/site-packages/flask/views.py", line 84, in view
    return self.dispatch_request(*args, **kwargs)
  File "/Users/pivotal/workspace/pgadmin4/web/pgadmin/browser/utils.py",
line 235, in dispatch_request
    return method(*args, **kwargs)
  File "/Users/pivotal/workspace/pgadmin4/web/pgadmin/browser/
server_groups/servers/databases/schemas/tables/__init__.py", line 315, in
wrap
    return f(*args, **kwargs)
  File "/Users/pivotal/workspace/pgadmin4/web/pgadmin/browser/
server_groups/servers/databases/schemas/tables/__init__.py", line 2555, in
sql
    data = self._formatter(did, scid, tid, data)
  File "/Users/pivotal/workspace/pgadmin4/web/pgadmin/browser/
server_groups/servers/databases/schemas/tables/__init__.py", line 1081, in
_formatter
    data = self._columns_formatter(tid, data)
  File "/Users/pivotal/workspace/pgadmin4/web/pgadmin/browser/
server_groups/servers/databases/schemas/tables/__init__.py", line 663, in
_columns_formatter
    column['attlen'] = matchObj.group(1)
AttributeError: 'NoneType' object has no attribute 'group'

On Mon, Jun 12, 2017 at 12:49 PM, Dave Page <[email-masked]> wrote:

>>>>>> Fixed PostGIS datatype on SQL tab for Table, column, Foreign Table
>>>>>> and Type node.
>>>>>>
>>>>>>>>>> Please find the attached patch to fix RM #2324 : PostGIS
>>>>>>>>>> datatypes not showing up properly on SQL tab.
>>>>>>>>>>
>>>>>>>>>> Thanks,
>>>>>>>>>> Khushboo
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> --

---

From: Khushboo Vashi <[email-masked]>
Date: Tue, 13 Jun 2017 09:15:40 +0530

Hi,

On Mon, Jun 12, 2017 at 8:24 PM, Dave Page <[email-masked]> wrote:

> please find the attached fix. However I didn't get any error while running
regression tests.

>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/
> python2.7/site-packages/flask/app.py", line 2000, in __call__
>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/
> python2.7/site-packages/flask/app.py", line 1991, in wsgi_app
>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/
> python2.7/site-packages/flask/app.py", line 1567, in handle_exception
>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/
> python2.7/site-packages/flask/app.py", line 1988, in wsgi_app
>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/
> python2.7/site-packages/flask/app.py", line 1641, in full_dispatch_request
>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/
> python2.7/site-packages/flask/app.py", line 1544, in handle_user_exception
>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/
> python2.7/site-packages/flask/app.py", line 1639, in full_dispatch_request
>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/
> python2.7/site-packages/flask/app.py", line 1625, in dispatch_request
>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/
> python2.7/site-packages/flask/views.py", line 84, in view
>   File "/Users/pivotal/workspace/pgadmin4/web/pgadmin/browser/serve
> r_groups/servers/databases/schemas/tables/__init__.py", line 315, in wrap
>   File "/Users/pivotal/workspace/pgadmin4/web/pgadmin/browser/serve
> r_groups/servers/databases/schemas/tables/__init__.py", line 2555, in sql
>   File "/Users/pivotal/workspace/pgadmin4/web/pgadmin/browser/serve
> r_groups/servers/databases/schemas/tables/__init__.py", line 1081, in
> _formatter
>   File "/Users/pivotal/workspace/pgadmin4/web/pgadmin/browser/serve
> r_groups/servers/databases/schemas/tables/__init__.py", line 663, in
> _columns_formatter
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 df9eb1a..f6fa65e 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
@@ -657,19 +657,23 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings):
                 if 'elemoid' in column:
                     length, precision, typeval = self.get_length_precision(column['elemoid'])
 
+                # Set length and precision to None
+                column['attlen'] = None
+                column['attprecision'] = None
+
                 # If we have length & precision both
                 if length and precision:
                     matchObj = re.search(r'(\d+),(\d+)', fulltype)
-                    column['attlen'] = matchObj.group(1)
-                    column['attprecision'] = matchObj.group(2)
+                    if matchObj:
+                        column['attlen'] = matchObj.group(1)
+                        column['attprecision'] = matchObj.group(2)
                 elif length:
                     # If we have length only
                     matchObj = re.search(r'(\d+)', fulltype)
-                    column['attlen'] = matchObj.group(1)
-                    column['attprecision'] = None
-                else:
-                    column['attlen'] = None
-                    column['attprecision'] = None
+                    if matchObj:
+                        column['attlen'] = matchObj.group(1)
+                        column['attprecision'] = None
+
 
                 SQL = render_template("/".join([self.column_template_path,
                                                 'is_referenced.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 7a28cda..b5b88e9 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
@@ -348,21 +348,24 @@ class ColumnsView(PGChildNodeView, DataTypeReader):
         if 'elemoid' in data:
             length, precision, typeval = self.get_length_precision(data['elemoid'])
 
+        # Set length and precision to None
+        data['attlen'] = None
+        data['attprecision'] = None
+
         import re
-        # If we have length & precision both
 
+        # If we have length & precision both
         if length and precision:
             matchObj = re.search(r'(\d+),(\d+)', fulltype)
-            data['attlen'] = matchObj.group(1)
-            data['attprecision'] = matchObj.group(2)
+            if matchObj:
+                data['attlen'] = matchObj.group(1)
+                data['attprecision'] = matchObj.group(2)
         elif length:
             # If we have length only
             matchObj = re.search(r'(\d+)', fulltype)
-            data['attlen'] = matchObj.group(1)
-            data['attprecision'] = None
-        else:
-            data['attlen'] = None
-            data['attprecision'] = None
+            if matchObj:
+                data['attlen'] = matchObj.group(1)
+                data['attprecision'] = None
 
         # We need to fetch inherited tables for each table
         SQL = render_template("/".join([self.template_path,
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 8f02741..07df594 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
@@ -435,19 +435,22 @@ class TypeView(PGChildNodeView, DataTypeReader):
 
                 # Below logic will allow us to split length, precision from type name for grid
                 import re
+                t_len = None
+                t_prec = None
+
                 # If we have length & precision both
                 if is_tlength and is_precision:
                     matchObj = re.search(r'(\d+),(\d+)', row['fulltype'])
-                    t_len = matchObj.group(1)
-                    t_prec = matchObj.group(2)
+                    if matchObj:
+                        t_len = matchObj.group(1)
+                        t_prec = matchObj.group(2)
                 elif is_tlength:
                     # If we have length only
                     matchObj = re.search(r'(\d+)', row['fulltype'])
-                    t_len = matchObj.group(1)
-                    t_prec = None
-                else:
-                    t_len = None
-                    t_prec = None
+                    if matchObj:
+                        t_len = matchObj.group(1)
+                        t_prec = None
+

---

From: Dave Page <[email-masked]>
Date: Tue, 13 Jun 2017 09:19:06 +0100

Thanks, applied.

On Tue, Jun 13, 2017 at 4:45 AM, Khushboo Vashi <
[email-masked]> wrote:

>> Looks like this patch broke the regressions tests :-(. Can you take a
>> look ASAP please?
>>
>> please find the attached fix. However I didn't get any error while
> running regression tests.
>
>> Thanks!
>>
>>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/pyth
>> on2.7/site-packages/flask/app.py", line 2000, in __call__
>>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/pyth
>> on2.7/site-packages/flask/app.py", line 1991, in wsgi_app
>>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/pyth
>> on2.7/site-packages/flask/app.py", line 1567, in handle_exception
>>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/pyth
>> on2.7/site-packages/flask/app.py", line 1988, in wsgi_app
>>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/pyth
>> on2.7/site-packages/flask/app.py", line 1641, in full_dispatch_request
>>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/pyth
>> on2.7/site-packages/flask/app.py", line 1544, in handle_user_exception
>>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/pyth
>> on2.7/site-packages/flask/app.py", line 1639, in full_dispatch_request
>>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/pyth
>> on2.7/site-packages/flask/app.py", line 1625, in dispatch_request
>>   File "/Users/pivotal/.pyenv/versions/2.7.10/envs/pgadmin/lib/pyth
>> on2.7/site-packages/flask/views.py", line 84, in view
>>>> <https://redmine.postgresql.org/issues/2473> to add support for
>>>> PostGIS Datatypes.
>>>>
>>>> Thanks,
>>>> Khushboo
>>>>
>>>>> Oh, OK - thanks. Maybe we need to reuse the length and precision
>>>>> fields for these types. That is, I believe, essentially how PostGIS passes
>>>>> extra info to the column definitions.
>>>>>
>>>>>
>>>>>>
>>>>>>