explain this shell command please

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

explain this shell command please

David Burgess-2
I have a few sqlite databases and .dump in the shell seems to work
fine for me (unless I have '.echo off')

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?

sqlite> drop table if exists x;create temp table temp.x ( a integer ,
b integer); insert into x values (1,1),(2,2),(3,3);
sqlite> .dump x
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite>

sqlite> drop table if exists x;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>
_______________________________________________
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: explain this shell command please

Simon Slavin-3
On 2 Jul 2018, at 12:45am, David Burgess <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: explain this shell command please

David Burgess-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: explain this shell command please

Simon Slavin-3
On 2 Jul 2018, at 1:08am, David Burgess <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: explain this shell command please

David Burgess-2
# 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 <[hidden email]> wrote:

> On 2 Jul 2018, at 1:08am, David Burgess <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
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: explain this shell command please

Simon Slavin-3
On 2 Jul 2018, at 1:40am, David Burgess <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users