Re: [sqlite] explain this shell command please
On 2 Jul 2018, at 1:40am, David Burgess wrote: > **The same thing happens with a file based database Indeed. Thanks for your neat test. I verify your procedure and bug: 178:~ simon$ sqlite3 ~/Desktop/test.sqlite SQLite version 3.22.0 2017-12-05 15:00:17 Enter ".help" for usage hints. sqlite> .echo on sqlite> create table x ( a integer , b integer); insert into x values ...> (1,1),(2,2),(3,3); create table x ( a integer , b integer); insert into x values (1,1),(2,2),(3,3); sqlite> .dump .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE x ( a integer , b integer); SELECT a,b FROM x INSERT INTO x VALUES(1,1); INSERT INTO x VALUES(2,2); INSERT INTO x VALUES(3,3); COMMIT; sqlite> What's happening is that the CLI is internally using the "SELECT" command in order to find out what INSERT commands to output. But since it's used only internally it shouldn't appear in the output. And since it does not end in a semi-colon it will cause problems for anyone who manages to trap that in an output file. Time for the developer team to participate. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] explain this shell command please
# sqlite3 SQLite version 3.24.0 2018-06-04 19:24:41 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table x ( a integer , b integer); insert into x values (1,1),(2,2),(3,3); sqlite> .dump x PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE x ( a integer , b integer); INSERT INTO x VALUES(1,1); INSERT INTO x VALUES(2,2); INSERT INTO x VALUES(3,3); COMMIT; sqlite> .echo on sqlite> .dump x .dump x PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE x ( a integer , b integer); SELECT a,b FROM x INSERT INTO x VALUES(1,1); INSERT INTO x VALUES(2,2); INSERT INTO x VALUES(3,3); COMMIT; sqlite> **The same thing happens with a file based database On Mon, Jul 2, 2018 at 10:22 AM, Simon Slavin wrote: > On 2 Jul 2018, at 1:08am, David Burgess wrote: > >> Thanks simon. Back to my original issue. Is this a bug? > > As you suspected, the .dump command should not output "SELECT" commands. Nor > should it output SQL commands without a following semicolon. > > I can't find your original post. Can you show us the sequence of commands > you used to make that happen ? Please include the line from sqlite3 startup > which shows the version you're using. > > Simon. > ___ > 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] explain this shell command please
On 2 Jul 2018, at 1:08am, David Burgess wrote: > Thanks simon. Back to my original issue. Is this a bug? As you suspected, the .dump command should not output "SELECT" commands. Nor should it output SQL commands without a following semicolon. I can't find your original post. Can you show us the sequence of commands you used to make that happen ? Please include the line from sqlite3 startup which shows the version you're using. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] explain this shell command please
Thanks simon. Back to my original issue. Is this a bug? sqlite> .echo on sqlite> .dump x .dump x PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE x ( a integer , b integer); SELECT a,b FROM x INSERT INTO x VALUES(1,1); INSERT INTO x VALUES(2,2); INSERT INTO x VALUES(3,3); COMMIT; sqlite> Note the SELECT that appears when echo is on. Missing ';' and probably should not be there anyway. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] explain this shell command please
On 2 Jul 2018, at 12:45am, David Burgess wrote: > I was preparing a test case to report the bug and I note that .dump > does not work on temp tables (3.24). Is this a feature? The TEMP tables are not stored in the main database. They're in an attached database called 'temp'. Unfortunately the '.dump' command dumps only tables in the main database. I do not know whether '.dump temp.*' works. By definition these databases are not intended for permanent storage, so there's no point in dumping them to a file. But you might be able to see them using '.databases' and by using various PRAGMAs. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
