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

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

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.

Thanks.

>
> On Fri, Dec 23, 2016 at 11:24 AM, Surinder Kumar <
> surinder(dot)kumar(at)enterprisedb(dot)com
> <javascript:_e(%7B%7D,'cvml','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
>> <javascript:_e(%7B%7D,'cvml','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
>>> <javascript:_e(%7B%7D,'cvml','surinder(dot)kumar(at)enterprisedb(dot)com');>>
>>> wrote:
>>>
>>>>
>>>>
>>>> On Mon, Dec 5, 2016 at 11:09 PM, Dave Page <dpage(at)pgadmin(dot)org
>>>> <javascript:_e(%7B%7D,'cvml','dpage(at)pgadmin(dot)org');>> wrote:
>>>>
>>>>> Hi
>>>>>
>>>>> On Friday, December 2, 2016, Surinder Kumar <
>>>>> surinder(dot)kumar(at)enterprisedb(dot)com
>>>>> <javascript:_e(%7B%7D,'cvml','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