Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

[Warren Young]

On Apr 7, 2017, at 10:45 AM, Warren Young  wrote:
> 
> (In my case, it was largely to avoid paying the cost of calling sqlite3 
> thousands of times in a loop with near-identical SQL

I just remembered the other reason: because it’s easier to manage SQL quoting 
rules in terms of Perl quoting rules than in terms of shell quoting.

Talk about bringing it around full-circle.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

[Warren Young]

On Apr 7, 2017, at 1:14 AM, Dominique Devienne  wrote:
> 
> On Fri, Apr 7, 2017 at 5:02 AM, Charles Leifer  wrote:
> 
>> Is it conceivable that this change could be rolled back?
> 
> ...their rule is "use only the official shell via
> scripts” and no C code

As one who just ported a shell script written as a wrapper around the sqlite3 
executable to Perl + DBI because it had grown beyond the natural bounds of 
shell scripting, I have to ask “WHY???”

Don’t misunderstand, it’s great that you have the option to write simpler 
scripts as sqlite3 shell script wrappers, but at some point, there really are 
benefits to moving to something more powerful.

(In my case, it was largely to avoid paying the cost of calling sqlite3 
thousands of times in a loop with near-identical SQL, whereas the Perl script 
keeps the conn open and calls prepare() a few times to avoid repeated SQL 
re-parsing.  The Perl version probably runs a couple of orders of magnitude 
faster.)

I also want to know how a systems administration organization centered around 
Unix shell scripting gets to 2017 without any awareness that command output 
formats occasionally change?  That’s a perennial danger.

I can get behind the “no C” rule, since that just buys a different pile of 
portability problems.  You need a really compelling reason to write something 
in C.  (SQLite itself has one of those good reasons.)  But what’s wrong with 
Perl, or Python, or Ruby, or Tcl, or… ?  They all have stable SQLite DB access 
libraries, often included with the base OS, since so many things depend on 
those scripting languages *and* SQLite.

I haven’t moved all of my sqlite3 shell script wrappers to Perl + DBI, and I 
won’t be doing so.  Sometimes that’s exactly the right way to go.  My objection 
is to this apparent “only one right way to do it” mentality.  C, shell, and 
your favorite scripting language are all the “right way” in some contexts.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

[Dominique Devienne]

On Fri, Apr 7, 2017 at 5:02 AM, Charles Leifer  wrote:

> Is it conceivable that this change could be rolled back?
>
> On Tue, Apr 4, 2017 at 9:45 AM, Charles Leifer  wrote:
> > I hate to be critical of Dr Hipp, but this commit stinks. Even if using
> > the sqlite3 shell isn't the blessed way of producing a backup, I'm sure a
> > lot of folks prefer it to the online backup API. It's this simple:
>

I reached to colleagues about this, to find out if they depend on .dump in
our
commercial server product heavily using SQLite, and indeed they do. In fact
for many admin tasks, their rule is "use only the official shell via
scripts" and
no C code, so they also consider this change a "regression" I'm afraid.
FWIW. --DD

PS: Just at the time we're upgrading all our 3rd parties to gear up for our
next release
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

[Charles Leifer]

Is it conceivable that this change could be rolled back?

On Tue, Apr 4, 2017 at 9:45 AM, Charles Leifer  wrote:

> I hate to be critical of Dr Hipp, but this commit stinks. Even if using
> the sqlite3 shell isn't the blessed way of producing a backup, I'm sure a
> lot of folks prefer it to the online backup API. It's this simple:
>
> "echo .dump | sqlite3 my_db.db | gzip > my_db.sql.gz"
>
> That's beautiful.
>
> And now it doesn't work.
>
> What a horrible shock it was to discover that my backups were all of a
> sudden failing. I had no idea why until I checked the mailing list, which I
> luckily happen to be subscribed to, otherwise I'd still be puzzling over
> what happened.
>
> Please consider reverting this, or hiding this behavior behind a flag.
>
> On Tue, Apr 4, 2017 at 1:55 AM, Clemens Ladisch 
> wrote:
>
>> James K. Lowden wrote:
>> >>> Why not use vis(3) instead?
>> >>
>> >> Because vis() is a nonstandard function that is not available
>> >> everywhere,
>> >
>> > "everywhere" is a high standard, but vis is freely available and
>> > included or packaged with almost anything not Windows.
>>
>> It is not included in the distribution that I happen to use.
>>
>> >> and in any case it does not support SQL.
>> >
>> > Entirely irrelevant, as you surely know.
>>
>> What?!?  The .dump output _is_ SQL, and is intended to be read again
>> by SQLite, so whatever escaping mechanism it uses must be supported by
>> SQLite itself.
>>
>> > To me, the most objectionable aspect of using char() is that the SQL is
>> > munged.  I see no reason to modify the user's text and introduce
>> > further SQL interpretation.
>>
>> The INSERT statements _already_ are interpreted; that's the whole point
>> of generating them in the first place.
>>
>>
>> Regards,
>> Clemens
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

[Charles Leifer]

I hate to be critical of Dr Hipp, but this commit stinks. Even if using the
sqlite3 shell isn't the blessed way of producing a backup, I'm sure a lot
of folks prefer it to the online backup API. It's this simple:

"echo .dump | sqlite3 my_db.db | gzip > my_db.sql.gz"

That's beautiful.

And now it doesn't work.

What a horrible shock it was to discover that my backups were all of a
sudden failing. I had no idea why until I checked the mailing list, which I
luckily happen to be subscribed to, otherwise I'd still be puzzling over
what happened.

Please consider reverting this, or hiding this behavior behind a flag.

On Tue, Apr 4, 2017 at 1:55 AM, Clemens Ladisch  wrote:

> James K. Lowden wrote:
> >>> Why not use vis(3) instead?
> >>
> >> Because vis() is a nonstandard function that is not available
> >> everywhere,
> >
> > "everywhere" is a high standard, but vis is freely available and
> > included or packaged with almost anything not Windows.
>
> It is not included in the distribution that I happen to use.
>
> >> and in any case it does not support SQL.
> >
> > Entirely irrelevant, as you surely know.
>
> What?!?  The .dump output _is_ SQL, and is intended to be read again
> by SQLite, so whatever escaping mechanism it uses must be supported by
> SQLite itself.
>
> > To me, the most objectionable aspect of using char() is that the SQL is
> > munged.  I see no reason to modify the user's text and introduce
> > further SQL interpretation.
>
> The INSERT statements _already_ are interpreted; that's the whole point
> of generating them in the first place.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

[Clemens Ladisch]

James K. Lowden wrote:
>>> Why not use vis(3) instead?
>>
>> Because vis() is a nonstandard function that is not available
>> everywhere,
>
> "everywhere" is a high standard, but vis is freely available and
> included or packaged with almost anything not Windows.

It is not included in the distribution that I happen to use.

>> and in any case it does not support SQL.
>
> Entirely irrelevant, as you surely know.

What?!?  The .dump output _is_ SQL, and is intended to be read again
by SQLite, so whatever escaping mechanism it uses must be supported by
SQLite itself.

> To me, the most objectionable aspect of using char() is that the SQL is
> munged.  I see no reason to modify the user's text and introduce
> further SQL interpretation.

The INSERT statements _already_ are interpreted; that's the whole point
of generating them in the first place.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

[Clemens Ladisch]

Charles Leifer wrote:
> This bit me... I fat-fingered a command and deleted my database. I had a
> backup dump taken earlier in the day. Go to restore it and all of a sudden
> this error starts cropping up.
>
> What to do?

From the link the in the first post:

  sed -e "s/'||char(10)||'/\\n/g" < with_char.sql > with_newlines.sql

(Same for char(13) → \r, if you have MS-DOS or Mac line endings.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

[Charles Leifer]

This bit me... I fat-fingered a command and deleted my database. I had a
backup dump taken earlier in the day. Go to restore it and all of a sudden
this error starts cropping up.

I'm stuck. I need to get this database back online, but even with 4000
limit I am losing INSERTs. I get segfaults when I disable the limit
completely.

What to do?

On Mon, Apr 3, 2017 at 7:16 PM, James K. Lowden 
wrote:

> On Sun, 2 Apr 2017 09:40:36 +0200
> Clemens Ladisch  wrote:
>
> > > Why not use vis(3) instead?
> >
> > Because vis() is a nonstandard function that is not available
> > everywhere,
>
> "everywhere" is a high standard, but vis is freely available and
> included or packaged with almost anything not Windows.  By using the
> nonstandard char() function, you introduce a constraint that is
> otherwise avoided.
>
> $ for S in c h m o
> do
> printf '%s: %s\n' -$S $(echo hello | vis -w -$S)
> done
> -c: hello\n
> -h: hello%0a
> -m: hello\012
> -o: hello\012
>
>
> For the intended purpose, it would be trivial to support some
> form of VIS_WHITE.  That could be a fallback position.
>
> Because the library is widely available, SQLite users who can take
> advantage of it can decode the vis-encoded text outside the aegis of
> SQLite.
>
> > and in any case it does not support SQL.
>
> Entirely irrelevant, as you surely know.
>
> To me, the most objectionable aspect of using char() is that the SQL is
> munged.  I see no reason to modify the user's text and introduce
> further SQL interpretation.
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

[James K. Lowden]

On Sun, 2 Apr 2017 09:40:36 +0200
Clemens Ladisch  wrote:

> > Why not use vis(3) instead?
> 
> Because vis() is a nonstandard function that is not available
> everywhere, 

"everywhere" is a high standard, but vis is freely available and
included or packaged with almost anything not Windows.  By using the
nonstandard char() function, you introduce a constraint that is
otherwise avoided.  

$ for S in c h m o
do 
printf '%s: %s\n' -$S $(echo hello | vis -w -$S)
done
-c: hello\n
-h: hello%0a
-m: hello\012
-o: hello\012


For the intended purpose, it would be trivial to support some
form of VIS_WHITE.  That could be a fallback position.  

Because the library is widely available, SQLite users who can take
advantage of it can decode the vis-encoded text outside the aegis of
SQLite.  

> and in any case it does not support SQL.

Entirely irrelevant, as you surely know.  

To me, the most objectionable aspect of using char() is that the SQL is
munged.  I see no reason to modify the user's text and introduce
further SQL interpretation. 

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

[Clemens Ladisch]

James K. Lowden wrote:
> Clemens Ladisch  wrote:
>
>> since commit 68f6dc7af1013f29, newlines in the .dump output are
>> escaped with char().
>
> Why not use vis(3) instead?

Because vis() is a nonstandard function that is not available everywhere,
and in any case it does not support SQL.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

[James K. Lowden]

On Fri, 31 Mar 2017 21:59:05 +0200
Clemens Ladisch  wrote:

> since commit 68f6dc7af1013f29, newlines in the .dump output are
> escaped with char().  

Why not use vis(3) instead?  No one seemed to notice when I offered
the suggestion.  

Newline encoding is a solved problem.  There's no need to touch the
SQL and introduce nonstandard syntax to the dump format.  

--jkl

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BUG: CLI generates invalid SQL for strings with many newlines

[Clemens Ladisch]

Hi,

since commit 68f6dc7af1013f29, newlines in the .dump output are escaped
with char().  This can make the resulting SQL too complex:

$ sqlite3 test.db "create table t(x); insert into t 
values(replace(printf('%1000s', ''), ' ', char(10)));"
$ sqlite3 test.db .dump | sqlite3
Error: near line 4: Expression tree is too large (maximum depth 1000)

(originally reported here: http://stackoverflow.com/q/43145117/11654)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users