sqlite command line tool NUL support

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite command line tool NUL support

J Decker
So for output of a select in the shell ( unlike .dump ) is this.

    for(i=1; i<nResult; i++){
      utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
    }

option 1) add text conversion in the column_text to escape NUL characters.
in TEXT... then everyone everywhere would inherit the change.
'asdf' is as valid as ''||'a'||'s'||'d'||'f'||''

( the postgres/sqlite way is to use  "\'||CHAR(0)||\'" (as a C string) if
'\0' is required outside of a bind )

2) output literal characters regardless of terminal...
    //utf8_printf(p->out, "%s", z);
    raw_fwrite( z, 1, sqlite3_column_bytes(pSelect, 0), p->out);
     for(i=1; i<nResult; i++){
      //utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
      raw_fputc(p->out, ',');
      raw_fwrite( sqlite3_column_text(pSelect, i), 1,
          sqlite3_column_bytes(pSelect, 0), p->out);
    }

3)

.dump uses
output_quoted_string
output_quoted_escaped_string
(the latter of which will wrap "text\r\n" with
replace(replace("text\\r\\n" ),'\\r',char(13)),'\\n',char(10))
so it could additionally wrap that with

the latter of which will wrap "u\0tf\r\n" with
replace(replace(replace("u\\0tf\\r\\n" ),'\\r',char(13)),'\\n',char(
10)),'\\0',char(0))

instead of
replace(replace("u'||CHAR(0)||'tf\\r\\n" ),'\\r',char(13)),'\\n',char(10))




------
* Change select * output to write full length text value
* Change .dump output to include supporting char(0) substitution ( ! Shell
Newlines )
* Change other .dump output to inline subst '||CHAR(0)||'  (
ShellHasFlag(p, SHFLG_Newlines) )

https://drive.google.com/open?id=1UXaFBRoNypZBhkpfPh7TAwncJMbBT1cH


Sample output (shell without newlines)
"d\n and" has a char \x14

(shell only; doesn't change test1.c)

SQLite version 3.23.0 2018-01-24 01:58:49
Enter ".help" for usage hints.
sqlite> .dump test
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test (a);
INSERT INTO test VALUES(0);
INSERT INTO test VALUES(replace('TEST\0NUL\0','\0',char(0)));
INSERT INTO test VALUES(replace('TEST\0\0NUL\0','\0',char(0)));
INSERT INTO test VALUES(0);
INSERT INTO test VALUES(0);
INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
all\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
INSERT INTO test VALUES(replace(replace('char test\rand\n and
all(null)','\r',char(13)),'\n',char(10)));
INSERT INTO test VALUES(replace(replace('char test\rand\n and
all(null)','\r',char(13)),'\n',char(10)));
INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and all
23\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and all
merged content\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
COMMIT;
sqlite> select * from test
   ...> ;
0
TEST NUL
TEST  NUL
0
0
andr test
  and all
andr test
and all
andr test
and all
andr test
  and all 23
andr test
  and all merged content
sqlite>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite command line tool NUL support

J Decker
This is a picture.  This is a tortoise git log view of merges.

https://drive.google.com/open?id=1RosGlprJRoutFsou2XDRlflxc8xWoHks

On Wed, Jan 24, 2018 at 8:59 AM, J Decker <[hidden email]> wrote:

>
> So for output of a select in the shell ( unlike .dump ) is this.
>
>     for(i=1; i<nResult; i++){
>       utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
>     }
>
> option 1) add text conversion in the column_text to escape NUL characters.
> in TEXT... then everyone everywhere would inherit the change.
> 'asdf' is as valid as ''||'a'||'s'||'d'||'f'||''
>
> ( the postgres/sqlite way is to use  "\'||CHAR(0)||\'" (as a C string) if
> '\0' is required outside of a bind )
>
> 2) output literal characters regardless of terminal...
>     //utf8_printf(p->out, "%s", z);
>     raw_fwrite( z, 1, sqlite3_column_bytes(pSelect, 0), p->out);
>      for(i=1; i<nResult; i++){
>       //utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
>       raw_fputc(p->out, ',');
>       raw_fwrite( sqlite3_column_text(pSelect, i), 1,
>           sqlite3_column_bytes(pSelect, 0), p->out);
>     }
>
> 3)
>
> .dump uses
> output_quoted_string
> output_quoted_escaped_string
> (the latter of which will wrap "text\r\n" with
> replace(replace("text\\r\\n" ),'\\r',char(13)),'\\n',char(10))
> so it could additionally wrap that with
>
> the latter of which will wrap "u\0tf\r\n" with
> replace(replace(replace("u\\0tf\\r\\n" ),'\\r',char(13)),'\\n',char(1
> 0)),'\\0',char(0))
>
> instead of
> replace(replace("u'||CHAR(0)||'tf\\r\\n" ),'\\r',char(13)),'\\n',char(10))
>
>
>
>
> ------
> * Change select * output to write full length text value
> * Change .dump output to include supporting char(0) substitution ( ! Shell
> Newlines )
> * Change other .dump output to inline subst '||CHAR(0)||'  (
> ShellHasFlag(p, SHFLG_Newlines) )
>
> https://drive.google.com/open?id=1UXaFBRoNypZBhkpfPh7TAwncJMbBT1cH
>
>
> Sample output (shell without newlines)
> "d\n and" has a char \x14
>
> (shell only; doesn't change test1.c)
>
> SQLite version 3.23.0 2018-01-24 01:58:49
> Enter ".help" for usage hints.
> sqlite> .dump test
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE test (a);
> INSERT INTO test VALUES(0);
> INSERT INTO test VALUES(replace('TEST\0NUL\0','\0',char(0)));
> INSERT INTO test VALUES(replace('TEST\0\0NUL\0','\0',char(0)));
> INSERT INTO test VALUES(0);
> INSERT INTO test VALUES(0);
> INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
> all\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> INSERT INTO test VALUES(replace(replace('char test\rand\n and
> all(null)','\r',char(13)),'\n',char(10)));
> INSERT INTO test VALUES(replace(replace('char test\rand\n and
> all(null)','\r',char(13)),'\n',char(10)));
> INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
> all 23\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
> all merged content\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> COMMIT;
> sqlite> select * from test
>    ...> ;
> 0
> TEST NUL
> TEST  NUL
> 0
> 0
> andr test
>   and all
> andr test
> and all
> andr test
> and all
> andr test
>   and all 23
> andr test
>   and all merged content
> sqlite>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users