Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines
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
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
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
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
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
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
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
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
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
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
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
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