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

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.​

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