Re: [pgAdmin4][Patch]: Fix RM1790 - [Web] Support setting a field's value to "null"

Hi Dave,

Please find updated patch.

On Mon, Jan 16, 2017 at 10:01 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:

> Hi
>
> On Fri, Jan 13, 2017 at 9:24 AM, Surinder Kumar
> <surinder(dot)kumar(at)enterprisedb(dot)com> wrote:
> > Hi
> >
> > Please find attached patch and review.
> >
> > On Sun, Jan 8, 2017 at 3:27 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> >>
> >> Hi
> >>
> >> On Friday, December 23, 2016, Surinder Kumar
> >> <surinder(dot)kumar(at)enterprisedb(dot)com> wrote:
> >>>
> >>> Forgot to attach patch in last thread. please find patch.
> >>
> >>
> >> It looks good for the most part, except:
> >>
> >> 1) You missed the part we discussed about being able to set a value to
> ''
> >> (the literal string containing two single quotes) by entering \'\' (and
> of
> >> course, the follow-on cases to allow setting a value to \'\' by entering
> >> \\'\\' etc).
> >
> > Fixed.
>
> That doesn't seem right to me - the code you've written looks like
> it'll try to escape anything for use in a string literal, not just
> '\'\ or \\'\\' etc.
>
​Now the implementation is that It will find and unescape the string
literals like ​'\'\ or \\'\\' etc.

>
> >> 2) Could you please update the appropriate doc page to explain how this
> >> all works? You can lift the text from the pgAdmin 3 docs for the most
> part.
> >
> > I will send a separate patch for it.
> >>
> >>
> >> Thanks.
> >>
> >>>
> >>>
> >>> On Fri, Dec 23, 2016 at 11:24 AM, Surinder Kumar
> >>> <surinder(dot)kumar(at)enterprisedb(dot)com> wrote:
> >>>>
> >>>> On Fri, Dec 23, 2016 at 11:11 AM, Surinder Kumar
> >>>> <surinder(dot)kumar(at)enterprisedb(dot)com> wrote:
> >>>>>
> >>>>> Hi Dave,
> >>>>>
> >>>>> Please find updated patch.
> >>>>>
> >>>>> Changes implemented:
> >>>>>
> >>>>> 1) To enter an empty string in string/character type, user need to
> >>>>> enter '' (two single quotes).
> >>>>> 2) To enter null values in Integer/String type, user need to keep the
> >>>>> field blank.
> >>>>> 3) Null values will be represented as [null].
> >>>>>
> >>>>> Please find attached patch and review.
> >>>>>
> >>>>> On Fri, Dec 9, 2016 at 2:23 PM, Surinder Kumar
> >>>>> <surinder(dot)kumar(at)enterprisedb(dot)com> wrote:
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>> On Mon, Dec 5, 2016 at 11:09 PM, Dave Page <dpage(at)pgadmin(dot)org>
> wrote:
> >>>>>>>
> >>>>>>> Hi
> >>>>>>>
> >>>>>>> On Friday, December 2, 2016, Surinder Kumar
> >>>>>>> <surinder(dot)kumar(at)enterprisedb(dot)com> wrote:
> >>>>>>>>
> >>>>>>>> Hi
> >>>>>>>>
> >>>>>>>> Issue:
> >>>>>>>> - On viewing table data, If we edit a column and set value of
> >>>>>>>> column(type: text) to "null", It always takes it as empty string.
> It doesn't
> >>>>>>>> honour null values.
> >>>>>>>>
> >>>>>>>> Solution:
> >>>>>>>> - Take a flag "is_null" for columns with data type 'text', then on
> >>>>>>>> GUI, whilst user edits a text field, an additional option with
> >>>>>>>> checkbox(is_null ?) is given to take null values. If checkbox is
> checked, on
> >>>>>>>> JS side we check "is_null" flag and pass field value to null if
> selected.
> >>>>>>>>
> >>>>>>>> Please find patch and review.
> >>>>>>>
> >>>>>>>
> >>>>>>> A nice solution, but there are some problems I think;
> >>>>>>>
> >>>>>>> - How do I set a field that doesn't use the text editor to null?
> e.g.
> >>>>>>> an integer? If I try to set one to blank, I get an error that it's
> invalid
> >>>>>>> input syntax for an integer.
> >>>>>>
> >>>>>> It seems possible by writing custom editor which will convert empty
> >>>>>> string to null before save operation.
> >>>>>
> >>>>> Now If you set blank for integer field, field will set to null.
> >>>>>>>
> >>>>>>>
> >>>>>>> - When null values are first displayed, they are shown as blank
> >>>>>>> entries. If I then set one to null, it displays "null". It should
> always
> >>>>>>> display consistently - I'd suggest "[null]"
> >>>>>>
> >>>>>> Ok. But the issue is if we display "[null]" in cell for null entry.
> >>>>>> How would we distinguish If it is user entered string(as user can
> also enter
> >>>>>> "[null]") or it represent null value ? (Ashesh's concern)
> >>>>>
> >>>>> Now null values will be represented in field as [null].
> >>>>>>>
> >>>>>>>
> >>>>>>> Whilst I like the way this works in part, I think it's going to be
> >>>>>>> inconsistent in the way it would be displayed. I think we need to
> follow the
> >>>>>>> pgAdmin III way of handling this. The docs say the following:
> >>>>>>>
> >>>>>>> ====
> >>>>>>> If an SQL NULL is to be written to the table, simply leave the
> field
> >>>>>>> empty. If you store a new row, this will let the server fill in
> the default
> >>>>>>> value for that column. If you store a change to an existing row,
> the value
> >>>>>>> NULL will explicitly be written.
> >>>>>>>
> >>>>>>>
> >>>>>>> ...
> >>>>>>>
> >>>>>>> If you want pgAdmin III to write an empty string to the table, you
> >>>>>>> enter the special string ‘’ (two single quotes) in the field. If
> you want to
> >>>>>>> write a string containing solely two single quotes to the table,
> you need to
> >>>>>>> escape these quotes, by typing \‘\’
> >>>>>
> >>>>> To write an empty string, now user can enter '' (two single quotes),
> it
> >>>>> will be treated as empty string.
> >>>>>>>
> >>>>>>> ====
> >>>>>>>
> >>>>>>> In other words, if an empty value is included for a new row, that
> >>>>>>> column will be omitted from the INSERT statement, allowing the
> server to use
> >>>>>>> a default, or set it to blank.
> >>>>>>>
> >>>>>>> For existing rows, an empty value for any data type is updated as
> >>>>>>> NULL - e.g. col = NULL.
> >>>>>>>
> >>>>>>> For character/string types, if the user enters '', then an empty
> >>>>>>> string is written to the column when either inserting or updating.
> >>>>>>>
> >>>>>>>
> >>>>>>> If the user wishes to insert the literal string '' (i.e. 2 single
> >>>>>>> quotes), then \'\' must be entered, and pgAdmin converts that to
> ''.
> >>>>>>>
> >>>>>>>
> >>>>>>> To enter a literal string of \'\', then the user enters \\'\\', for
> >>>>>>> \\'\\' they enter \\\\'\\\\' and so on.
> >>>>>
> >>>>> If a user enters a literal string \'\', this value is escaped by
> adding
> >>>>> slashes on python side and unescaped by removing added slashes when
> returned
> >>>>> to display.
> >>>>> the entered values are already escaped, user need not to escape
> values.
> >>>>>>
> >>>>>> This behaviour seems to working wine in pgAdmin3 query tool but not
> on
> >>>>>> viewing data by right click context menu.
> >>>>>>
> >>>>>>
> >>>>>> In view data:
> >>>>>> When user enter literal strings like '', \'\'
> >>>>>> &
> >>>>>> \\'\\', it displays these strings as it is after saving. It seems
> >>>>>> conversion doesn't happen.
> >>>>>> so which one is correct to follow in pgAdmin4?
> >>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>> Thanks.
> >>>>>>>
> >>>>>>>
> >>>>>>> --
> >>>>>>> Dave Page
> >>>>>>> Blog: http://pgsnake.blogspot.com
> >>>>>>> Twitter: @pgsnake
> >>>>>>>
> >>>>>>> EnterpriseDB UK: http://www.enterprisedb.com
> >>>>>>> The Enterprise PostgreSQL Company
> >>>>>>>
> >>>>>>
> >>>>>
> >>>>
> >>>
> >>
> >>
> >> --
> >> Dave Page
> >> Blog: http://pgsnake.blogspot.com
> >> Twitter: @pgsnake
> >>
> >> EnterpriseDB UK: http://www.enterprisedb.com
> >> The Enterprise PostgreSQL Company
> >>
> >
>
>
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>