Thread: PoC: Little improvements to EditGrid - Enum ComboBox

Hello all,

I'm starting some proof-of-concept working on frmEditGrid trying make
some "improvements" like:

1) show a combo box in columns of type enum;
2) show a datetime picker in columns of type date/time/timestamp;
3) show a combo box in columns that has referential to another table;

For the first item I have a very WIP patch (attached based on commit
896dce8f744816385aa89013d7808960f15a5109) and a working example
(attached screenshots and sql) .. I'd like to see your opinions about
them, if it is a good idea, and if the initial implementation is
following the expectations of such feature. I'd like to know if anyone
is/was working in a feature like this, I wouldn't like to reinvent the
wheel.

For the second one I'm researching some interaction between
wxDatePicker, wxTimePicker and wxCalendarCtrl to create a new type of
CellEditor, so we could use some sqlGridDateTimeEditor that
encapsulates the logic. Is that a good approach? What is your opinions
about that?

Finally, for the last one, I see some challenges like:

- Which field should be displayed in the combo?
   Generally the value of a FK doesn't has enough information about
what it means, them you need another field (e.g  a description or
person_name field) to make sense.

- How to play with composite referential keys? Should they be
supported, and if so how should be the behavior?
  It seems pretty dark to me how to bring values from two fields from
a FK in distinct combo boxes.

- Caching the data for performance.

Well, this is just a start point and your opinions is very important.

What do you think guys?

Thanks and best regards.

--
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br

Hi

On Wed, Jun 6, 2012 at 7:33 AM, Dickson S. Guedes <listas@guedesoft.net> wrote:
> Hello all,
>
> I'm starting some proof-of-concept working on frmEditGrid trying make
> some "improvements" like:
>
> 1) show a combo box in columns of type enum;

Nice.

> 2) show a datetime picker in columns of type date/time/timestamp;

Hmm, nice idea in principle, but I'm not sure if there's a suitable
control that will allow the flexibility required (fractional seconds,
timezones etc). Obviously that's not needed in all cases, but we
should be consistent, otherwise we'd get people asking why they can
use a picker for timestamp but not timestamptz for example.

> 3) show a combo box in columns that has referential to another table;

Also nice, but I think this one needs some careful handling. Perhaps
limit the combo items to the first 500 or something, and still allow
the user to manually type a value? We don't want to try to load 10
million values into the combo!

> For the first item I have a very WIP patch (attached based on commit
> 896dce8f744816385aa89013d7808960f15a5109) and a working example
> (attached screenshots and sql) .. I'd like to see your opinions about
> them, if it is a good idea, and if the initial implementation is
> following the expectations of such feature. I'd like to know if anyone
> is/was working in a feature like this, I wouldn't like to reinvent the
> wheel.

Looks like a great start to me.

> For the second one I'm researching some interaction between
> wxDatePicker, wxTimePicker and wxCalendarCtrl to create a new type of
> CellEditor, so we could use some sqlGridDateTimeEditor that
> encapsulates the logic. Is that a good approach? What is your opinions
> about that?

Right - see above. Maybe the answer is to develop a popup control that
can handle date/time/timezone, and yet still allow the user to edit
manually for things like fractional seconds.

> Finally, for the last one, I see some challenges like:
>
> - Which field should be displayed in the combo?
>   Generally the value of a FK doesn't has enough information about
> what it means, them you need another field (e.g  a description or
> person_name field) to make sense.

Well, you could use a grid instead of a combo box to allow the user to
choose, and display the FK value in the first column, and the PK value
from the referenced table in the second and subsequent rows. I think
anything more than that is probably too domain specific.

> - How to play with composite referential keys? Should they be
> supported, and if so how should be the behavior?
>  It seems pretty dark to me how to bring values from two fields from
> a FK in distinct combo boxes.

Yeah, I don't really like the idea of trying to support that case. I
can't see any sensible way to make it work nicely.

> - Caching the data for performance.

Yes! :-)

> Well, this is just a start point and your opinions is very important.
>
> What do you think guys?

I think it'll be a nice addition to the tool - thanks for working on it.

One additional suggestion you may want to include: bytea handling. For
input, allow the user to select a local file as the contents of a
bytea field. For output, it would be great if we could detect some
common filetypes and offer viewers for them - e.g. detect that a bytea
contains an image and allow the user to view it.

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

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

On Wed, 2012-06-06 at 09:33 +0100, Dave Page wrote:
> Hi
>
> On Wed, Jun 6, 2012 at 7:33 AM, Dickson S. Guedes <listas@guedesoft.net> wrote:
> > Hello all,
> >
> > I'm starting some proof-of-concept working on frmEditGrid trying make
> > some "improvements" like:
> >
> > 1) show a combo box in columns of type enum;
>
> Nice.
>
> > 2) show a datetime picker in columns of type date/time/timestamp;
>
> Hmm, nice idea in principle, but I'm not sure if there's a suitable
> control that will allow the flexibility required (fractional seconds,
> timezones etc). Obviously that's not needed in all cases, but we
> should be consistent, otherwise we'd get people asking why they can
> use a picker for timestamp but not timestamptz for example.
>
> > 3) show a combo box in columns that has referential to another table;
>
> Also nice, but I think this one needs some careful handling. Perhaps
> limit the combo items to the first 500 or something, and still allow
> the user to manually type a value? We don't want to try to load 10
> million values into the combo!
>
> > For the first item I have a very WIP patch (attached based on commit
> > 896dce8f744816385aa89013d7808960f15a5109) and a working example
> > (attached screenshots and sql) .. I'd like to see your opinions about
> > them, if it is a good idea, and if the initial implementation is
> > following the expectations of such feature. I'd like to know if anyone
> > is/was working in a feature like this, I wouldn't like to reinvent the
> > wheel.
>
> Looks like a great start to me.
>
> > For the second one I'm researching some interaction between
> > wxDatePicker, wxTimePicker and wxCalendarCtrl to create a new type of
> > CellEditor, so we could use some sqlGridDateTimeEditor that
> > encapsulates the logic. Is that a good approach? What is your opinions
> > about that?
>
> Right - see above. Maybe the answer is to develop a popup control that
> can handle date/time/timezone, and yet still allow the user to edit
> manually for things like fractional seconds.
>
> > Finally, for the last one, I see some challenges like:
> >
> > - Which field should be displayed in the combo?
> >   Generally the value of a FK doesn't has enough information about
> > what it means, them you need another field (e.g  a description or
> > person_name field) to make sense.
>
> Well, you could use a grid instead of a combo box to allow the user to
> choose, and display the FK value in the first column, and the PK value
> from the referenced table in the second and subsequent rows. I think
> anything more than that is probably too domain specific.
>
> > - How to play with composite referential keys? Should they be
> > supported, and if so how should be the behavior?
> >  It seems pretty dark to me how to bring values from two fields from
> > a FK in distinct combo boxes.
>
> Yeah, I don't really like the idea of trying to support that case. I
> can't see any sensible way to make it work nicely.
>
> > - Caching the data for performance.
>
> Yes! :-)
>
> > Well, this is just a start point and your opinions is very important.
> >
> > What do you think guys?
>
> I think it'll be a nice addition to the tool - thanks for working on it.
>

+1

> One additional suggestion you may want to include: bytea handling. For
> input, allow the user to select a local file as the contents of a
> bytea field. For output, it would be great if we could detect some
> common filetypes and offer viewers for them - e.g. detect that a bytea
> contains an image and allow the user to view it.
>

And handling of big texts, and NULL values. But even without that, it
would be great to see the enum, and simple FK handling.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

2012/6/6 Dave Page <dpage@pgadmin.org>:
>> 2) show a datetime picker in columns of type date/time/timestamp;
>
> Hmm, nice idea in principle, but I'm not sure if there's a suitable
> control that will allow the flexibility required (fractional seconds,
> timezones etc). Obviously that's not needed in all cases, but we
> should be consistent, otherwise we'd get people asking why they can
> use a picker for timestamp but not timestamptz for example.


Yep, I'll try some layouts and behaviors and come back to listen to you.


>> 3) show a combo box in columns that has referential to another table;
>
> Also nice, but I think this one needs some careful handling. Perhaps
> limit the combo items to the first 500 or something, and still allow
> the user to manually type a value? We don't want to try to load 10
> million values into the combo!

[... cut ...]

>> - Which field should be displayed in the combo?
>>   Generally the value of a FK doesn't has enough information about
>> what it means, them you need another field (e.g  a description or
>> person_name field) to make sense.
>
> Well, you could use a grid instead of a combo box to allow the user to
> choose, and display the FK value in the first column, and the PK value
> from the referenced table in the second and subsequent rows. I think
> anything more than that is probably too domain specific.

Nice idea. Maybe the workflow could be:

1. user starts edit on a field that is a simple FK
2. we open the field and show a little button "[...]" in the right side
3. user place the value by hand and the already existing behavior is
used as expected
4. otherwise user click in the button
4.1. we open another frmEditGrid called with a special parameter
4.2. the special parameter enable double click in a record (or another event)
4.3. the new window shows data from referenced table
4.4. user search the field and double click it
4.5. this frmEdiGrid is closed and the primary key value is placed in
the original frmEditGrid respective field

Thoughts?

>> - How to play with composite referential keys? Should they be
>> supported, and if so how should be the behavior?
>>  It seems pretty dark to me how to bring values from two fields from
>> a FK in distinct combo boxes.
>
> Yeah, I don't really like the idea of trying to support that case. I
> can't see any sensible way to make it work nicely.


+1. I'm queuing it.


>> Well, this is just a start point and your opinions is very important.
>>
>> What do you think guys?
>
> I think it'll be a nice addition to the tool - thanks for working on it.

Thanks Dave!

> One additional suggestion you may want to include: bytea handling. For
> input, allow the user to select a local file as the contents of a
> bytea field. For output, it would be great if we could detect some
> common filetypes and offer viewers for them - e.g. detect that a bytea
> contains an image and allow the user to view it.

I liked it and I'll try something.

Best regards.
--
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br

On Wed, Jun 6, 2012 at 2:38 PM, Dickson S. Guedes <listas@guedesoft.net> wrote:
>
>>> - Which field should be displayed in the combo?
>>>   Generally the value of a FK doesn't has enough information about
>>> what it means, them you need another field (e.g  a description or
>>> person_name field) to make sense.
>>
>> Well, you could use a grid instead of a combo box to allow the user to
>> choose, and display the FK value in the first column, and the PK value
>> from the referenced table in the second and subsequent rows. I think
>> anything more than that is probably too domain specific.
>
> Nice idea. Maybe the workflow could be:
>
> 1. user starts edit on a field that is a simple FK
> 2. we open the field and show a little button "[...]" in the right side
> 3. user place the value by hand and the already existing behavior is
> used as expected
> 4. otherwise user click in the button
> 4.1. we open another frmEditGrid called with a special parameter
> 4.2. the special parameter enable double click in a record (or another event)
> 4.3. the new window shows data from referenced table
> 4.4. user search the field and double click it
> 4.5. this frmEdiGrid is closed and the primary key value is placed in
> the original frmEditGrid respective field

It sounds like that is worth prototyping, though, I think using
another edit grid is too much (that's a pretty chunky bit of code,
most of which wouldn't be needed). I'd be inclined to just use a
listview or gridcontrol on a dropdown (so it works like an oversized
combo box).



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

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

2012/6/6 Dickson S. Guedes <listas@guedesoft.net>:
> I'm starting some proof-of-concept working on frmEditGrid trying make
> some "improvements" like:
>
> 1) show a combo box in columns of type enum;

There isn't changes to this code comparing to previous initial patch,
but I'm getting segfault when the combo box is open and you press
'Delete' to delete some letter. I'm sure that I'm missing something,
but couldn't figure it out.

The combo values are "cached" because they are fetched when the combo
box control is set as a CellEditor for a particular cell in the grid,
that happens once.

Should I implement this behavior in another way, i. e. re-fetching
values from database each time I call BeginEdit then the combo is
always in sync with database?

> 2) show a datetime picker in columns of type date/time/timestamp;

The goals to this item is change actual CellEditor of the following types:

PGOID_TYPE_DATE
PGOID_TYPE_TIME
PGOID_TYPE_TIMESTAMP
PGOID_TYPE_TIMETZ
PGOID_TYPE_TIMESTAMPTZ

to a CellEditor that keeps the actual behavior of typing the values in
a textbox plus the option to pick a value from a respective picker.

This patch's version contains the implementation for PGOID_TYPE_DATE
and PGOID_TYPE_TIME using wxCalendarBox and wxTimeSpinCtl.

Thoughts?

--
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br

2012/6/17 Ashesh Vashi <ashesh.vashi@enterprisedb.com>
>
> On Sun, Jun 17, 2012 at 6:39 AM, Dickson S. Guedes <listas@guedesoft.net> wrote:
>>
>> 2012/6/6 Dickson S. Guedes <listas@guedesoft.net>:
>> > I'm starting some proof-of-concept working on frmEditGrid trying make
>> > some "improvements" like:
>> >
>> > 1) show a combo box in columns of type enum;
>>
>> There isn't changes to this code comparing to previous initial patch,
>> but I'm getting segfault when the combo box is open and you press
>> 'Delete' to delete some letter. I'm sure that I'm missing something,
>> but couldn't figure it out.
>
> I guess - you may need to implement KILL FOCUS event on combo-box.

Hi Ashesh,

Thanks for your comment. I debugged with gdb and see what is happening:

#0  0x00007ffff7850658 in wxStyledTextCtrl::SendMsg (this=0x1e06d90,
msg=2008, wp=0, lp=0) at ../../../../contrib/src/stc/stc.cpp:199
#1  0x00007ffff785093b in wxStyledTextCtrl::GetCurrentPos
(this=0x1e06d90) at ../../../../contrib/src/stc/stc.cpp:267
#2  0x0000000000641baa in frmEditGrid::OnDelete (this=0x1cdbce0,
event=...) at ./frm/frmEditGrid.cpp:1161
#3  0x00007ffff5dc0aa6 in wxAppConsole::HandleEvent (this=0x1019e20,
handler=0x1cdbce0, func=(void (wxEvtHandler::*)(wxEvtHandler * const,
wxEvent &)) 0x641a4e <frmEditGrid::OnDelete(wxCommandEvent&)>,
event=...) at ../src/common/appbase.cpp:322

As we can see in #2, pressing the 'Delete' key is triggering
frmEditGrid::OnDelete that are trying to do:

if (text && text->GetCurrentPos() <= text->GetTextLength()) ...

I need to check if the column is Enum and return before this code.

regards
--
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br

2012/6/18 Ashesh Vashi <ashesh.vashi@enterprisedb.com>
> Hi Dickson,

Hello Ashesh!


> On Sun, Jun 17, 2012 at 6:48 PM, Dickson S. Guedes <listas@guedesoft.net>
> wrote:
>>
>> 2012/6/17 Ashesh Vashi <ashesh.vashi@enterprisedb.com>
>> >
>> > On Sun, Jun 17, 2012 at 6:39 AM, Dickson S. Guedes
>> > <listas@guedesoft.net> wrote:
>> >>
>> >> 2012/6/6 Dickson S. Guedes <listas@guedesoft.net>:
>> >> > I'm starting some proof-of-concept working on frmEditGrid trying
>> >> > make
>> >> > some "improvements" like:
>> >> >
>> >> > 1) show a combo box in columns of type enum;
>> >>
>> >> There isn't changes to this code comparing to previous initial patch,
>> >> but I'm getting segfault when the combo box is open and you press
>> >> 'Delete' to delete some letter. I'm sure that I'm missing something,
>> >> but couldn't figure it out.
>> >
>> > I guess - you may need to implement KILL FOCUS event on combo-box.
>>
>> Hi Ashesh,
>>
>> Thanks for your comment. I debugged with gdb and see what is happening:
>>
>> #0  0x00007ffff7850658 in wxStyledTextCtrl::SendMsg (this=0x1e06d90,
>> msg=2008, wp=0, lp=0) at ../../../../contrib/src/stc/stc.cpp:199
>> #1  0x00007ffff785093b in wxStyledTextCtrl::GetCurrentPos
>> (this=0x1e06d90) at ../../../../contrib/src/stc/stc.cpp:267
>> #2  0x0000000000641baa in frmEditGrid::OnDelete (this=0x1cdbce0,
>> event=...) at ./frm/frmEditGrid.cpp:1161
>> #3  0x00007ffff5dc0aa6 in wxAppConsole::HandleEvent (this=0x1019e20,
>> handler=0x1cdbce0, func=(void (wxEvtHandler::*)(wxEvtHandler * const,
>> wxEvent &)) 0x641a4e <frmEditGrid::OnDelete(wxCommandEvent&)>,
>> event=...) at ../src/common/appbase.cpp:322
>>
>> As we can see in #2, pressing the 'Delete' key is triggering
>> frmEditGrid::OnDelete that are trying to do:
>>
>> if (text && text->GetCurrentPos() <= text->GetTextLength()) ...
>>
>> I need to check if the column is Enum and return before this code.
>
> I was not able to reproduce the issue on my ubuntu VM.
> (That's probably because I am using MAC as host and it does not have the
> DELETE key.)

Thanks for try it. I could reproduce the same issue on Ubuntu, Debian
and Fedora, but I don't have a Mac or Windows to try it.


> But - to identify, if the column is enum or not, you can use this patch.
> This patch includes a modification to identify the column is enum or not.


Thanks! I'll poke into it.


Regards,
--
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br