Default value for NOT NULL TEXT fields

[Boris Kurshev]

Originally reported on Google Code with ID 222

What steps will reproduce the problem?
1.go to create table form
2.enter field name
3.choose type TEXT
4.check 'not NULL'
5.leave "Default Value" empty (meaning empty string for default)

I expect field withh empty string by default.
I have error ERROR: Cannot add a NOT NULL column with default value NULL.

phpLiteAdmin v1.9.4.1
SQLite version: 3.7.9
SQLite extension [?]: PDO
PHP version: 5.4.17RC1

Reported by 13dagger on 2013-07-15 12:58:01

[Christopher Kramer]

Thanks for your report, sorry for the late answer.

At first I was not able to reproduce this. It does not happen when you create a new
table. It only happens when you add a new column to an existing table. Is this also
what you did?

Reported by crazy4chrissi on 2013-08-09 10:29:32 - Status changed: Accepted

[Christopher Kramer]

I just fixed this in SVN.
You can now choose between a NULL default value and an empty string. See screenshot.
This is now possible both when creating a new table and when adding one to an existing
one.

Reported by crazy4chrissi on 2013-08-09 12:58:38 - Status changed: Fixed - Labels added: Target-1.9.5

<hr> * Attachment: pla_null_default.jpg

[Christopher Kramer]

If you or anyone else wants to test this, here is a built (1-file) version of rev 414
attached.

Maybe it's time to setup an automated post-commit build.

Reported by crazy4chrissi on 2013-08-09 13:11:23

<hr> * Attachment: phpliteadmin.php

[Christopher Kramer]

Issue 219 has been merged into this issue.

Reported by crazy4chrissi on 2013-08-09 13:14:20

[Boris Kurshev]

I've tested. Now it does create fields with default ''.
But, when i try to remove field, i have 'ERROR: Altering of Table test failed - (drop)
- Pattern did not match on your original CREATE TABLE statement. '.
Not sure that those things related, but anyway.

Also in words of usability your solution is not the best. We have 2 checkboxes: isNull
and defaultNull. If user chose defaultNull and not chose isNull - we have error. I
don't know how exactly make it more userfriendly, need to think. 

Anyway, thanks for work!

Reported by 13dagger on 2013-08-09 13:48:12

[Christopher Kramer]

Thanks for your feedback. Regarding the removal of the field: If you want to help this
project, please set $debug=true; and try to delete the field again. It will produce
a lot of debug output then. Please open a new issue and post all the debug output there
(maybe as an attachment). Thanks. I would really appreciate your help in this matter.

regarding usability: Well, we will get an error with "Not null" and "default null"
both checked. Maybe when checking "Not null", the "default Null" checkbox should get
automatically unchecked?

Reported by crazy4chrissi on 2013-08-09 14:17:09

[Boris Kurshev]

Are you sure, that i have to open new issue? I had not this bug on previous version.

-Maybe when checking "Not null", the "default Null" checkbox should get automatically
unchecked?
Yes, and it can be done thru radiobutton to avoid using js.

Reported by 13dagger on 2013-08-09 15:06:07

[Boris Kurshev]

Query used to create this table (got from ?table=qwe&action=column_view)
CREATE TABLE 'qwe' ('x' INTEGER NOT NULL DEFAULT '', 'y' INTEGER DEFAULT NULL)

The debug output is:

ALTER TABLE QUERY=(ALTER TABLE "qwe" DROP "y"), tablename=(qwe), alterdefs=(DROP "y")
ALTER TABLE: table=(qwe), alterdefs=(DROP "y")
SQL?
origsql=(CREATE TABLE 'qwe' ('x' INTEGER NOT NULL DEFAULT '', 'y' INTEGER DEFAULT NULL))
preg_remove_create_table=(/^\s*+CREATE\s++TABLE\s++(?:'qwe'|qwe|"qwe"|`qwe`|\[qwe\])\s*+(\(.*+)$/is)
createtemptableSQL=(CREATE TEMPORARY TABLE 't1376060836' ('x' INTEGER NOT NULL DEFAULT
'', 'y' INTEGER DEFAULT NULL))
SQL?
def=DROP "y"
action=(drop), column=(y), column_escaped=(y)
preg_column_definition=(\s*+(?:'(?:[^']++|'')++'|[^ '"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?:[^']*+|'')*+'|[^'",`\[
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++)
preg_columns_before=((?:((?:\s*+(?:'(?:[^']++|'')++'|[^ '"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?:[^']*+|'')*+'|[^'",`\[
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'(?:[^']++|'')++'|[^
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?:[^']*+|'')*+'|[^'",`\[
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?)
CREATE TEMPORARY TABLE 't1376060836' ('x' INTEGER NOT NULL DEFAULT '', 'y' INTEGER
DEFAULT NULL)
CREATE TEMPORARY TABLE 't1376060836' ('x' INTEGER NOT NULL DEFAULT '', 'y' INTEGER
DEFAULT NULL)
/^\s*+(CREATE\s++TEMPORARY\s++TABLE\s++'t1376060836'\s*+\()(?:((?:\s*+(?:'(?:[^']++|'')++'|[^
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?:[^']*+|'')*+'|[^'",`\[
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'(?:[^']++|'')++'|[^
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?:[^']*+|'')*+'|[^'",`\[
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?\s*(?:'y'|y|"y"|`y`|\[y\])\s+(?:(?:'(?:[^']*+|'')*+'|[^,'"\[`]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))+(,\s*(.+))?\s*\)\s*$/s

Reported by 13dagger on 2013-08-09 15:10:11

[Christopher Kramer]

Thanks. I think this really is another issue, but no matter, lets do it here as you
started posting it here.

Strange things happening. From your debug output, it really does not work. The pattern
does not match. But I tried exactly the same thing and it worked and the only thing
in the debug output that differs is the line where you can see whether it worked ;-)
And the pattern printed by your debug output should match your input.

Okay. The only difference I can imagine are characters I cannot see from your debug
output because they are not printed. Is there any newline or tab or anything special
in the Create table statement? (As this is html-output, newlines are not visible in
the browser unless we convert them to <br> which the debug output doesn't.)

Reported by crazy4chrissi on 2013-08-09 21:21:43

[Christopher Kramer]

> -Maybe when checking "Not null", the "default Null" checkbox should get 
> automatically unchecked?
> Yes, and it can be done thru radiobutton to avoid using js.
I first thought radio buttons would indeed be a good idea but they are not because
it makes sense to choose neither "not null" nor "default null", i.e. have a column
that can be NULL but by default is something different. But once one of both radio
buttons is checked, you cannot uncheck both. So radio buttons will not work here.

Reported by crazy4chrissi on 2013-08-10 10:28:03

[Christopher Kramer]

Regarding the error dropping the column: Another point might be that you hit a backtrack
or recursion limit on your setup and I don't. Could you try it with the version attached?
It should give additional debug output saying "PREG-Error? There is no error." or "PREG-Error?
Backtrack limit was exhausted!" or something like this. 

Reported by crazy4chrissi on 2013-08-10 10:51:00

<hr> * Attachment: phpliteadmin.php

[Boris Kurshev]

sorry for late answer

ALTER TABLE QUERY=(ALTER TABLE "test" DROP "y"), tablename=(test), alterdefs=(DROP
"y")
ALTER TABLE: table=(test), alterdefs=(DROP "y")
SQL?
origsql=(CREATE TABLE 'test' ('x' INTEGER NOT NULL DEFAULT '', 'y' INTEGER DEFAULT
NULL))
preg_remove_create_table=(/^\s*+CREATE\s++TABLE\s++(?:'test'|test|"test"|`test`|\[test\])\s*+(\(.*+)$/is)
createtemptableSQL=(CREATE TEMPORARY TABLE 't1376382692' ('x' INTEGER NOT NULL DEFAULT
'', 'y' INTEGER DEFAULT NULL))
SQL?
def=DROP "y"
action=(drop), column=(y), column_escaped=(y)
preg_column_definition=(\s*+(?:'(?:[^']++|'')++'|[^ '"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?:[^']*+|'')*+'|[^'",`\[
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++)
preg_columns_before=((?:((?:\s*+(?:'(?:[^']++|'')++'|[^ '"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?:[^']*+|'')*+'|[^'",`\[
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'(?:[^']++|'')++'|[^
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?:[^']*+|'')*+'|[^'",`\[
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?)
PREG-Error? There is no error.
CREATE TEMPORARY TABLE 't1376382692' ('x' INTEGER NOT NULL DEFAULT '', 'y' INTEGER
DEFAULT NULL)
CREATE TEMPORARY TABLE 't1376382692' ('x' INTEGER NOT NULL DEFAULT '', 'y' INTEGER
DEFAULT NULL)
/^\s*+(CREATE\s++TEMPORARY\s++TABLE\s++'t1376382692'\s*+\()(?:((?:\s*+(?:'(?:[^']++|'')++'|[^
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?:[^']*+|'')*+'|[^'",`\[
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++,\s*+)*\s*+(?:'(?:[^']++|'')++'|[^
'"\[`,]+|"(?:[^"]++|"")++"|`(?:[^`]++|``)++`|\[(?:[^\]]++|\]\])++\])(?:\s+(?:'(?:[^']*+|'')*+'|[^'",`\[
]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))++),\s*+)?\s*(?:'y'|y|"y"|`y`|\[y\])\s+(?:(?:'(?:[^']*+|'')*+'|[^,'"\[`]*|"(?:[^"]*+|"")*+"|`(?:[^`]*+|``)*+`|\[(?:[^\]]*+|\]\])*+\]))+(,\s*(.+))?\s*\)\s*$/s

you can try at http://test.13dagger.tk/phpliteadmin.php
feel free to send me new versions for debug.

Reported by 13dagger on 2013-08-13 08:34:39

[Christopher Kramer]

Thanks a lot. Unfortunately, it seems the problem is rather complicated. I downloaded
your db and tried it on my machine with exactly the code I uploaded for you, and it
worked. So there is no problem with invisible newlines or stuff like this. I would
guess your backtrack limit is smaller than mine so you hit the limit and I don't but
then it would not say "There is no error.".
So the only reason I can think of is that the regex is indeterministic and therefore
matches for me but not for you for some reason, e.g. because the PCRE library bundled
with your php is different then mine.
I think I will have a closer look on the regex. I will most likely send you a small
script that will allow me to try some regular expressions on your server. It would
be great if you would upload this for me.

Reported by crazy4chrissi on 2013-08-13 16:32:30

[Christopher Kramer]

Ok, here is the small test script. Can you pleas upload this for me on your server so
I can test some regular expressions there?

Reported by crazy4chrissi on 2013-08-13 16:55:51

<hr> * Attachment: regextest.php

[Boris Kurshev]

http://test.13dagger.tk/regextest.php

Reported by 13dagger on 2013-08-13 18:11:41