Re: [pgadmin4][Patch]: Display Functions node for GreenPlum database

2018-01-19 Thread Joao De Almeida Pereira 
Hello Hackers,
I am updating this patch as we found out that the SQL tab was not showing
the functions properly, and we corrected that in the new update patch in
this email

Thanks
Joao

On Wed, Jan 17, 2018 at 12:25 PM, Joao De Almeida Pereira <
jdealmeidapere...@pivotal.io> wrote:

>
> @Dave I sent the new patch with correction in the previous email. Can you
> review it and let us know if it is ok?
>
>
> On Fri, Jan 12, 2018 at 12:48 PM, Joao De Almeida Pereira <
> jdealmeidapere...@pivotal.io> wrote:
>
>> Thanks for the help Murtuza
>>
>> Attached you can find the revisited patch that now displayed all the
>> information about functions in GreenPlum
>>
>> Thanks
>> Joao
>>
>> On Fri, Jan 12, 2018 at 9:51 AM, Murtuza Zabuawala <
>> murtuza.zabuaw...@enterprisedb.com> wrote:
>>
>>> ​Hi,
>>>
>>> We ​create collection of existing parameters using function
>>> 'def _format_arguments_from_db(...)' in a file
>>> '../pgadmin4/web/pgadmin/browser/server_groups/servers/datab
>>> ases/schemas/functions/__init__.py' @ line 454, and on client side we
>>> have backbone collection mapped with same id on line 304 (function.js)
>>>
>>>
>>>
>>> --
>>> Regards,
>>> Murtuza Zabuawala
>>> EnterpriseDB: http://www.enterprisedb.com
>>> The Enterprise PostgreSQL Company
>>>
>>>
>>> On Fri, Jan 12, 2018 at 8:10 PM, Joao De Almeida Pereira <
>>> jdealmeidapere...@pivotal.io> wrote:
>>>
 Hello,
 I changed that function to make the retrieval quicker because the
 return type of all the types in the database for GreenPlum can be quite 
 big.

 Nevertheless the "Data Types" in the front end do not get filled up
 with the values.

 Where is the Javascript code that does the mapping between the call of
 get_types and that table in the screenshot?

 Thanks
 Joao


 On Fri, Jan 12, 2018 at 12:37 AM, Murtuza Zabuawala <
 murtuza.zabuaw...@enterprisedb.com> wrote:

> Hi Joao,
>
> We have written common function 'def get_types(...)' in a class called
> 'DataTypeReader' to fetch datatypes in a file 
> '../pgadmin4/web/pgadmin/brows
> er/server_groups/servers/databases/schemas/utils.py' and we have
> inherited from DataTypeReader in Function module's 'FunctionView' class to
> use it check line no. 120
>
> --
> Regards,
> Murtuza Zabuawala
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> On Thu, Jan 11, 2018 at 9:31 PM, Joao De Almeida Pereira <
> jdealmeidapere...@pivotal.io> wrote:
>
>> Hello Hackers,
>> We had some requests from GreenPlum users to display the functions
>> node again.
>> This patch reintroduces functions as a node underneath the Schemas.
>>
>>
>> Unfortunately this patch is not final because I could not find the
>> place where the Data Type is populated from. (See next image)[image:
>> Inline image 1]
>>
>> Can anyone explain me how these types get populated?
>>
>>
>>
>> Thanks
>> Joao
>>
>
>

>>>
>>
>
diff --git 
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py
 
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py
index 92edc8b7..a59f6671 100644
--- 
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py
+++ 
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py
@@ -71,10 +71,10 @@ class FunctionModule(SchemaChildModule):
 """
 super(FunctionModule, self).__init__(*args, **kwargs)
 
-self.min_ver = 90100
+self.min_ver = None
 self.max_ver = None
 self.server_type = None
-self.min_gpdbver = 10
+self.min_gpdbver = None
 
 def get_nodes(self, gid, sid, did, scid):
 """
@@ -659,7 +659,7 @@ class FunctionView(PGChildNodeView, DataTypeReader):
 condition += " AND nspname NOT LIKE E'pg_toast%' AND nspname 
NOT LIKE E'pg_temp%'"
 
 # Get Types
-status, types = self.get_types(self.conn, condition)
+status, types = self.get_types(self.conn, condition, False, scid)
 
 if not status:
 return internal_server_error(errormsg=types)
diff --git 
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/acl.sql
 
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/acl.sql
new file mode 100644
index ..1162ee67
--- /dev/null
+++ 
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/acl.sql
@@ -0,0 +1,38 @@
+SELECT
+COALESCE(gt.rolname, 'PUBLIC') AS grantee,
+g.rolname AS grantor, array_agg(privilege_type) AS privileges,
+array_agg(is_grantable) AS grantable
+FROM
+(SELECT
+(d).grantee AS grantee,
+(d).grantor

[pgadmin4] Explain plans

2018-01-19 Thread Joao De Almeida Pereira 
Hello Hackers,
We are trying to find a solution for the explain plans that currently are
not working in GP, due to the lack of support for JSON. We believe that the
best options for this it would be to display the text in the tab instead of
the visual version for our case.
In order to implement this we came up with some options that we would like
to understand what the community think about them.


   1.

   Move the SQL to generate explain plans to backend
   For this approach we would create a new endpoint to generate an explain
   plan and when we click the explain plan buttons instead of doing a new SQL
   query we would call the new endpoint and wait for the explain plan to be
   generated
   - Pros:
 - All SQL related code is in the backend
 - Extract more code from SQLEditor into smaller and more
 testable/maintainable modules
  - Cons:
 - Major Revamp of SQLEditor
 - If explain plan takes to long to generate we will be waiting for
 it in the foreground instead of polling
  2.

   Add parameters while executing SQL
   If we add parameters in the SQL query request we send to the backend,
   informing the backend that the SQL query is a Explain Plan to be
   executed(The response can have a flag saying that this is an explain plan,
   instead of assuming from the return values that it was an explain plan)
   - Pros:
 - All SQL related code is in the backend
 - Leverage the current polling system
  - Cons:
 - Add more logic to an already complex SQLEditor
  3.

   Disable Explain plan buttons
   Disable/Enable the Explain plan buttons depending on the type of
   database, this would also include the tab in the bottom of the SQLEditor
   - Pros:
 - Simpler solution
  - Cons:
 - Not really a good implementation, because all databases support
 explain plans
 - There will still be SQL in both frontend and backend
 - Looks more like a temporary fix instead of a solution


We believe that we should not keep build feature inside the SQLEditor, but
should try to extract as many parts of it as possible, this is where the
current option 2 fall short in our point of view. Due to this we believe
that option 1 looks promising and that is that path that we prefer to go
into.
What does the community think about this?

Thanks
Joao

Re: [pgadmin4] Explain plans

2018-01-19 Thread Murtuza Zabuawala 
On Fri, Jan 19, 2018 at 7:39 PM, Joao De Almeida Pereira <
jdealmeidapere...@pivotal.io> wrote:

> Hello Hackers,
> We are trying to find a solution for the explain plans that currently are
> not working in GP, due to the lack of support for JSON. We believe that the
> best options for this it would be to display the text in the tab instead of
> the visual version for our case.
> In order to implement this we came up with some options that we would like
> to understand what the community think about them.
>
>
>1.
>
>Move the SQL to generate explain plans to backend
>For this approach we would create a new endpoint to generate an
>explain plan and when we click the explain plan buttons instead of doing a
>new SQL query we would call the new endpoint and wait for the explain plan
>to be generated
>- Pros:
>  - All SQL related code is in the backend
>  - Extract more code from SQLEditor into smaller and more
>  testable/maintainable modules
>   - Cons:
>  - Major Revamp of SQLEditor
>  - If explain plan takes to long to generate we will be waiting
>  for it in the foreground instead of polling
>
>
​+1

We can make it configurable to work with both PG (text & json) & GP (text).
With the current implementation the constraint we have is that snapsvg
 library which we use to draw the explain plan do not
support any other format except JSON.
​


>
>1.
>
>Add parameters while executing SQL
>If we add parameters in the SQL query request we send to the backend,
>informing the backend that the SQL query is a Explain Plan to be
>executed(The response can have a flag saying that this is an explain plan,
>instead of assuming from the return values that it was an explain plan)
>- Pros:
>  - All SQL related code is in the backend
>  - Leverage the current polling system
>   - Cons:
>  - Add more logic to an already complex SQLEditor
>   2.
>
>Disable Explain plan buttons
>Disable/Enable the Explain plan buttons depending on the type of
>database, this would also include the tab in the bottom of the SQLEditor
>- Pros:
>  - Simpler solution
>   - Cons:
>  - Not really a good implementation, because all databases
>  support explain plans
>  - There will still be SQL in both frontend and backend
>  - Looks more like a temporary fix instead of a solution
>
>
> We believe that we should not keep build feature inside the SQLEditor, but
> should try to extract as many parts of it as possible, this is where the
> current option 2 fall short in our point of view. Due to this we believe
> that option 1 looks promising and that is that path that we prefer to go
> into.
> What does the community think about this?
>
> Thanks
> Joao
>