Thread: Submitting cursored query support, docs, etc

Submitting cursored query support, docs, etc

From
"Kalle Hallivuori"
Date:
Hi all fellow pgadmin-hackers!

We at Koodaripalvelut.com are adding some features on top of pgAdmin
for Truviso.com to support their streaming relations. A couple of
items might be of interest to pgAdmin in general:

1. Automatic CURSOR wrapping for SELECT queries in SQL query window
with yet another alternative Execute button
2. Showing results from cursors page by page in SQL output pane, with
a "next page" button
3. Closing open query cursor when user executes another query, stops
current one or closes the query window
4. Some documentation on architectural details of pgAdmin
5. How-tos for getting started on developing pgAdmin.

Seems there's some support for branding and features of other
proprietary servers. We'd probably happily provide all our code
(currently being developed against the 1.8.0 release source) if you'd
welcome it - mainly some extra classes and some bits of added
abstraction in places such as columns.

Insights?

--
Kalle Hallivuori +358-41-5053073 http://korpiq.iki.fi/

Re: Submitting cursored query support, docs, etc

From
Dave Page
Date:
Kalle Hallivuori wrote:
> Hi all fellow pgadmin-hackers!
>
> We at Koodaripalvelut.com are adding some features on top of pgAdmin
> for Truviso.com to support their streaming relations. A couple of
> items might be of interest to pgAdmin in general:
>
> 1. Automatic CURSOR wrapping for SELECT queries in SQL query window
> with yet another alternative Execute button
> 2. Showing results from cursors page by page in SQL output pane, with
> a "next page" button
> 3. Closing open query cursor when user executes another query, stops
> current one or closes the query window
> 4. Some documentation on architectural details of pgAdmin
> 5. How-tos for getting started on developing pgAdmin.
>
> Seems there's some support for branding and features of other
> proprietary servers. We'd probably happily provide all our code
> (currently being developed against the 1.8.0 release source) if you'd
> welcome it - mainly some extra classes and some bits of added
> abstraction in places such as columns.

Hi Kalle,

I'd be very happy to look at your code. Obviously I cannot guarantee
that it would all be incorporated into the base product, but certainly
all of the items above sound very interesting (I've personally wanted to
add a cursor mode to the query tool for some time).

We are keen to support major PostgreSQL derivatives whereever we can,
provided the changes are not too invasive, and I'd certainly be
interested to include Truviso in our list of supported servers if we can.

Regards, Dave




Re: Submitting cursored query support, docs, etc

From
"Kalle Hallivuori"
Date:
Hi again Dave and others,

2007/11/29, Dave Page <dpage@postgresql.org>:
> I'd be very happy to look at your code. Obviously I cannot guarantee
> that it would all be incorporated into the base product, but certainly
> all of the items above sound very interesting (I've personally wanted to
> add a cursor mode to the query tool for some time).

I'm trying to figure out where to add this. Insights are welcome.

> We are keen to support major PostgreSQL derivatives whereever we can,
> provided the changes are not too invasive, and I'd certainly be
> interested to include Truviso in our list of supported servers if we can.

We need to refactor dlgColumn to support Columns of other objects than
tables. I wonder whether it'd be acceptable for pgAdmin to have
separate abstract dlgColumn class and a derived dlgTableColumn class?

I should be able to commit some code for review this week (mainly
trivial changes to column handling). Work on developer documentation
will probably produce some results next week or so. A whole lot of
timing depends on when my second child is going to get born though;
anything may get delayed to latter half of January, unless someone
else publishes our results while I'm on parental leave.

Cheers and thanks for all your code,

--
Kalle Hallivuori +358-41-5053073 http://korpiq.iki.fi/

Re: Submitting cursored query support, docs, etc

From
Dave Page
Date:
Kalle Hallivuori wrote:
> Hi again Dave and others,
>
> 2007/11/29, Dave Page <dpage@postgresql.org>:
>> I'd be very happy to look at your code. Obviously I cannot guarantee
>> that it would all be incorporated into the base product, but certainly
>> all of the items above sound very interesting (I've personally wanted to
>> add a cursor mode to the query tool for some time).
>
> I'm trying to figure out where to add this. Insights are welcome.

Sorry, I'm not sure I understand - if you're talking about the patch,
please just mail it to the list.

>> We are keen to support major PostgreSQL derivatives whereever we can,
>> provided the changes are not too invasive, and I'd certainly be
>> interested to include Truviso in our list of supported servers if we can.
>
> We need to refactor dlgColumn to support Columns of other objects than
> tables. I wonder whether it'd be acceptable for pgAdmin to have
> separate abstract dlgColumn class and a derived dlgTableColumn class?

I don't see why not in principle. Obviously we'd need to be happy with
the actual code, but I have no problem with the architectural change.

> I should be able to commit some code for review this week (mainly
> trivial changes to column handling). Work on developer documentation
> will probably produce some results next week or so.

OK, cool.

> A whole lot of
> timing depends on when my second child is going to get born though;
> anything may get delayed to latter half of January, unless someone
> else publishes our results while I'm on parental leave.

Congratulations :-). I'm sure we can wait if necessary!!

> Cheers and thanks for all your code,


You're welcome - thanks for sharing your work with us.

Regards, Dave.

Re: Submitting cursored query support, docs, etc

From
"Kalle Hallivuori"
Date:
Hi!

2007/12/10, Dave Page <dpage@postgresql.org>:
> Kalle Hallivuori wrote:
> > Hi again Dave and others,
> >
> > 2007/11/29, Dave Page <dpage@postgresql.org>:
> >> I'd be very happy to look at your code. Obviously I cannot guarantee
> >> that it would all be incorporated into the base product, but certainly
> >> all of the items above sound very interesting (I've personally wanted to
> >> add a cursor mode to the query tool for some time).
> >
> > I'm trying to figure out where to add this. Insights are welcome.
>
> Sorry, I'm not sure I understand - if you're talking about the patch,
> please just mail it to the list.

Actually, I'm still wondering how to do this. We need to

1. submit the SELECT to server so that it produces a CURSOR instead of
a result set,
2. fetch and view rows from CURSOR instead of a ready-made result set,
3. handle a new state where a query is running but passive in a way
which enables more interaction than just stopping the current query.
Maybe we can call this state "active".

I'm reading the code but I'm still not crystal clear on where and how
to make those changes. That is where I'm welcoming input. Maybe there
are some classes, methods or properties that you could point me to? If
you've considered it yourself, which parts of code have you thought
you would change, and how?

--
Kalle Hallivuori +358-41-5053073 http://korpiq.iki.fi/

Re: Submitting cursored query support, docs, etc

From
Dave Page
Date:
Kalle Hallivuori wrote:
> Actually, I'm still wondering how to do this. We need to
>
> 1. submit the SELECT to server so that it produces a CURSOR instead of
> a result set,
> 2. fetch and view rows from CURSOR instead of a ready-made result set,
> 3. handle a new state where a query is running but passive in a way
> which enables more interaction than just stopping the current query.
> Maybe we can call this state "active".
>
> I'm reading the code but I'm still not crystal clear on where and how
> to make those changes. That is where I'm welcoming input. Maybe there
> are some classes, methods or properties that you could point me to? If
> you've considered it yourself, which parts of code have you thought
> you would change, and how?

I haven't looked at it in that much detail for a number of years
unfortunately. However, from the top of my head, I would start by
looking at ctlSQLResult::Execute/pgQueryThread::execute in which you can
create the cursor and retrieve the first batch of results. Then, look at
extending pgSet such that when run with a cursor, an attempt to access a
 row that hasn't be retrieved yet results in the dataset being extended
before the value is returned. Currently pgSet is tied to a single
PGResult, but I guess in cursor mode we would probably need to make that
a list of PGResults each containing <= N rows where N is a configurable
batch size.

It shouldn't be too hard to tie this low level stuff in with the UI
because the grid control will automatically request data as it renders
each row.

Like I said though, this is largely off the top of my head, so I may
have missed a showstopper somewhere :-). Feel free to ping more
questions or ideas to the list for discussion though.

/D

Re: Submitting cursored query support, docs, etc

From
"Kalle Hallivuori"
Date:
Hi!

2007/12/11, Dave Page <dpage@postgresql.org>:
> I haven't looked at it in that much detail for a number of years
> unfortunately. However, from the top of my head, I would start by
> looking at ctlSQLResult::Execute/pgQueryThread::execute in which you can
> create the cursor and retrieve the first batch of results.

Yeah, that works fine; I have a button now that declares a cursor for
the user's query, and I can append 'fetch from' manually to actually
see a row :)

> Then, look at
> extending pgSet such that when run with a cursor, an attempt to access a
>  row that hasn't be retrieved yet results in the dataset being extended
> before the value is returned. Currently pgSet is tied to a single
> PGResult, but I guess in cursor mode we would probably need to make that
> a list of PGResults each containing <= N rows where N is a configurable
> batch size.

This is exactly the kind of hint I hoped for. Thanks!

--
Kalle Hallivuori +358-41-5053073 http://korpiq.iki.fi/