Default value for NOT NULL TEXT fields

[GoogleCodeExporter]

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

Original issue reported on code.google.com by 13dag...@gmail.com on 15 Jul 2013 at 12:58

[GoogleCodeExporter]

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?

Original comment by crazy4ch...@gmail.com on 9 Aug 2013 at 10:29

• Changed state: Accepted

[GoogleCodeExporter]

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.

Original comment by crazy4ch...@gmail.com on 9 Aug 2013 at 12:58

• Changed state: Fixed
• Added labels: Target-1.9.5

Attachments:

• pla_null_default.jpg

[GoogleCodeExporter]

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.

Original comment by crazy4ch...@gmail.com on 9 Aug 2013 at 1:11

Attachments:

• phpliteadmin.php

[GoogleCodeExporter]

Issue 219 has been merged into this issue.

Original comment by crazy4ch...@gmail.com on 9 Aug 2013 at 1:14

[GoogleCodeExporter]

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!

Original comment by 13dag...@gmail.com on 9 Aug 2013 at 1:48

[GoogleCodeExporter]

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?

Original comment by crazy4ch...@gmail.com on 9 Aug 2013 at 2:17

[GoogleCodeExporter]

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.

Original comment by 13dag...@gmail.com on 9 Aug 2013 at 3:06

[GoogleCodeExporter]

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`|\[qw
e\])\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

Original comment by 13dag...@gmail.com on 9 Aug 2013 at 3:10

[GoogleCodeExporter]

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

Original comment by crazy4ch...@gmail.com on 9 Aug 2013 at 9:21

[GoogleCodeExporter]

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

Original comment by crazy4ch...@gmail.com on 10 Aug 2013 at 10:28

[GoogleCodeExporter]

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. 

Original comment by crazy4ch...@gmail.com on 10 Aug 2013 at 10:51

Attachments:

• phpliteadmin.php

[GoogleCodeExporter]

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.

Original comment by 13dag...@gmail.com on 13 Aug 2013 at 8:34

[GoogleCodeExporter]

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.

Original comment by crazy4ch...@gmail.com on 13 Aug 2013 at 4:32

[GoogleCodeExporter]

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?

Original comment by crazy4ch...@gmail.com on 13 Aug 2013 at 4:55

Attachments:

• regextest.php

[GoogleCodeExporter]

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

Original comment by 13dag...@gmail.com on 13 Aug 2013 at 6:11