Re: [sqlite] sqlite3 .dump file does not recreate database

[Jay A. Kreibich]

On Sat, Apr 21, 2012 at 02:07:17PM -0700, Kyle McKay scratched on the wall:

> Is there some way to modify only the SQLite3 shell's .dump utility
> to use 17g when dumping IEEE754 values (leaving all the other
> behavior unchanged)?

  Not easily.  Last time I checked, the shell uses sqlite3_exec() for
  everything, meaning the double-to-string conversion is done by the
  SQLite core, not the code in the shell.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 .dump file does not recreate database

[Simon Slavin]

On 21 Apr 2012, at 10:07pm, Kyle McKay  wrote:

> Is there some way to modify only the SQLite3 shell's .dump utility to use 17g 
> when dumping IEEE754 values (leaving all the other behavior unchanged)?

The source to the shell tool is included in the full (not amalgamated) source 
code which you can download from



but I have never tried to actually compile it.  If you're experienced with 
compiling from scratch you might be able to do it.

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

[sqlite] sqlite3 .dump file does not recreate database

[Kyle McKay]

The "Error: near line 10: column val is not unique" output is  
unexpected when loading the created .dump file.


This session demonstrates the issue:

$ /tmp/sqlite3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT SQLITE_SOURCE_ID();
2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
sqlite> CREATE TABLE test (
   ...>   val DOUBLE PRECISION PRIMARY KEY NOT NULL
   ...> );
sqlite> INSERT INTO test VALUES (1.0001);
sqlite> INSERT INTO test VALUES (1.1);
sqlite> INSERT INTO test VALUES (1.01);
sqlite> INSERT INTO test VALUES (1.001);
sqlite> INSERT INTO test VALUES (1.0001);
sqlite> SELECT * FROM test;
1.0001
1.1
1.01
1.0
1.0
sqlite> .output test_dump.txt
sqlite> .dump test
sqlite> .quit
$ cat test_dump.txt
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test (
  val DOUBLE PRECISION PRIMARY KEY NOT NULL
);
INSERT INTO test VALUES(1.0001);
INSERT INTO test VALUES(1.1);
INSERT INTO test VALUES(1.01);
INSERT INTO test VALUES(1.0);
INSERT INTO test VALUES(1.0);
COMMIT;
$ /tmp/sqlite3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT SQLITE_SOURCE_ID();
2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
sqlite> .read test_dump.txt
Error: near line 10: column val is not unique
sqlite> .schema
CREATE TABLE test (
  val DOUBLE PRECISION PRIMARY KEY NOT NULL
);
sqlite> SELECT * FROM test;
1.0001
1.1
1.01
1.0
sqlite> .quit
$

NOTES:
1) Related ticket http://www.sqlite.org/src/tktview?name=1248e6cda8
2) Related sqlite-users thread 
http://thread.gmane.org/gmane.comp.db.sqlite.general/68689/focus=68691
3) The source files containing the 15g formats are vdbemem.c (approx.  
line 225) and vdbetrace.c (approx. line 124)

4) Referenced article (see #1) recommending 17g: 
http://speleotrove.com/decimal/decifaq6.html#binapprox
5) SQLite3 documentation mentioning 15 decimal digits: 
http://www.sqlite.org/datatype3.html
6) PostgreSQL behaves similarly in the psql interface, but PostgreSQL  
dumps are done using a separate pg_dump utility and that seems to use  
17g for the dumps so its dumps are able to faithfully recreate the  
dumped database.


Is there some way to modify only the SQLite3 shell's .dump utility to  
use 17g when dumping IEEE754 values (leaving all the other behavior  
unchanged)?

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