[sqlite] .dump

[Thomas Kurz]

I noticed that the .dump command in the CLI doesn't contain the "user_version" 
and "application_id" fields. I don't know whether this is intentional, but 
would you consider including these values in the output of .dump?

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

Re: [sqlite] SQLite .dump

[dmp]

Simon Slavin wrote:
> I'm sorry, but I don't see a question in your post.

As intended. It was just a statement based on observation,
with regard to SQLite .dump, my GUI dump, and other database
dump outputs. MySQL also uses a short version without
specifying column names, but does quotes identifiers.

Warren Young wrote:
> and using the database's
> identifier quoting character.

> It does that at need already:

> sqlite> create table "x y" ("a b" INTEGER);
> sqlite> .dump
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE IF NOT EXISTS "x y" ("a b" INTEGER);
> COMMIT;

Seems only when the initial dll specified.

sqlite> create table x (a INTEGER);
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE x (a INTEGER);
COMMIT;

There is no intention to indicate a bug, or other aspect
about a deficiency in SQLite .dump.

The reason my tool provides columns is because the
dump is made to allow users to selectively save data
from only the specified columns.

The reason I always quote identifiers, is because people
do stuff like this for names, "keY_cOlumn2".

If that was not quoted in dml then it might be interpreted
as KEY_COLUMN2 for example in some databases, and then throw
an error.

NO SUCH COLUMN.

danap.

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

Re: [sqlite] SQLite .dump

[Warren Young]

On Jul 13, 2018, at 10:15 AM, dmp  wrote:
> 
> Seems .dump uses a short output of skipping the column names.

To call that a problem requires that you justify why you’d need the column 
names to be specified in the INSERT statements.

If you take the .dump file as-given and just run it, the INSERT statements are 
fine as-is because the .dump file has a CREATE TABLE immediately above.  There 
can be no mismatch if you do not change the .dump file.

If you’re intending to edit the CREATE TABLE statements out of the .dump file 
and run those INSERT statements on a SQLite DB with a different schema, then 
yes, you’d need the column names to be explicitly provided, but that seems like 
quite a special case.  And of course, you could just edit the INSERT statements 
at the same time.

> Always specifying the column names list

That’s good practice whenever there can be significant drift between the code 
that does the CREATE TABLE call and the code that does the INSERT.  For 
example, if your application’s DB is initialized with a script and then 
potentially years of software updates do ALTER TABLE calls on it on upgrades, 
then yes, it’s important to qualify the column names in your INSERT statements.

> and using the database's
> identifier quoting character.

It does that at need already:

sqlite> create table "x y" ("a b" INTEGER);
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "x y" ("a b" INTEGER);
COMMIT;

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

Re: [sqlite] SQLite .dump

[Simon Slavin]

I'm sorry, but I don't see a question in your post.

Are you suggesting that the SQLite command-line tool has a bug ?

Are you suggesting that Ajqvue has a bug ?

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

[sqlite] SQLite .dump

[dmp]

Hello,

Recently in testing my GUI tool I made a comparison from the
tool's dump and SQLite's command line .dump tool.

Seems .dump uses a short output of skipping the column names.
According to some of my research for various databases I use
one of these as options for SQL dump output:

http://ajqvue.com/docs/Manual/Ajqvue_Manual.html#Preferences_Data_Export_SQL
Preferences Data Export SQL

Always specifying the column names list and using the database's
identifier quoting character.

danap.

=======
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE datatypes (data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
int_type INTEGER, real_type REAL, text_type TEXT,
blob_type BLOB, date_type DATE, time_type TIME,
datetime_type DATETIME, timestamp_type TIMESTAMP);

INSERT INTO datatypes VALUES(1,100,200.18864,'some text',
 X'6162630a',153051120,68715000,1530343358000,
 1530559371079);

===
GUI tool:
--
-- Dumping data for table "datatypes"
--

INSERT INTO "datatypes" ("data_type_id", "int_type", "real_type",
"text_type",
 "blob_type", "date_type", "time_type",
"datetime_type",
 "timestamp_type") VALUES(1, 100, 200.2, 'some text',
 x'6162630a', 153051120, 68715000, 1530343358000,
 1530559371079);

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

Re: [sqlite] .dump command and user_version

[Ian Zimmerman]

On 2018-03-29 12:47, Wout Mertens wrote:

> I noticed that `.dump` does not output the user_version pragma. It
> seems to me that that is part of the database data?

I have run into this too.  I was trying to transport Mozilla browser
"places" database this way, and it turned out their code cares about the
user_version datum and thinks the file is corrupt if the value doesn't
match their expectation.

-- 
Please don't Cc: me privately on mailing lists and Usenet,
if you also post the followup to the list or newsgroup.
To reply privately _only_ on Usenet and on broken lists
which rewrite From, fetch the TXT record for no-use.mooo.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump command and user_version

[Scott Robison]

On Thu, Mar 29, 2018, 8:18 AM Richard Hipp  wrote:

> On 3/29/18, Scott Robison  wrote:
> >  It seems a
> > reasonable to suggestion to add it.
>
> Version 3.23.0 is in bug-fix-only mode.  It'll have to wait.
>

Of course. I wasn't suggesting that it should be done with all haste, just
agreeing with the original suggestion.

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

Re: [sqlite] .dump command and user_version

[Simon Slavin]

On 29 Mar 2018, at 3:10pm, Scott Robison  wrote:

> Yet as he said, user_version is data. It can be queried and can be
> checked to make decisions about what to do with the data. It seems a
> reasonable to suggestion to add it.

On consideration, I see the point.  Would you want to dump

PRAGMA schema_version

too ?

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

Re: [sqlite] .dump command and user_version

[Richard Hipp]

On 3/29/18, Scott Robison  wrote:
>  It seems a
> reasonable to suggestion to add it.

Version 3.23.0 is in bug-fix-only mode.  It'll have to wait.

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

Re: [sqlite] .dump command and user_version

[Scott Robison]

On Thu, Mar 29, 2018 at 6:56 AM, Simon Slavin  wrote:
>
>
> On 29 Mar 2018, at 1:47pm, Wout Mertens  wrote:
>
>> I noticed that `.dump` does not output the user_version pragma. It seems to
>> me that that is part of the database data?
>>
>> I don't actually use it, but it might be interesting to add this for
>> completeness?
>
> .dump is for things which affect data and data integrity only.  "PRAGMA 
> foreign_keys" is one of the few PRAGMAs which affects data integrity: if a 
> database is operated with that PRAGMA not set, the data could be corrupted.
>
> Numerous PRAGMAs have no effect on integrity.  For example the journal mode 
> and page size.  They are not included in the output of .dump.

Yet as he said, user_version is data. It can be queried and can be
checked to make decisions about what to do with the data. It seems a
reasonable to suggestion to add it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump command and user_version

[Simon Slavin]

On 29 Mar 2018, at 1:47pm, Wout Mertens  wrote:

> I noticed that `.dump` does not output the user_version pragma. It seems to
> me that that is part of the database data?
> 
> I don't actually use it, but it might be interesting to add this for
> completeness?

.dump is for things which affect data and data integrity only.  "PRAGMA 
foreign_keys" is one of the few PRAGMAs which affects data integrity: if a 
database is operated with that PRAGMA not set, the data could be corrupted.

Numerous PRAGMAs have no effect on integrity.  For example the journal mode and 
page size.  They are not included in the output of .dump.

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

[sqlite] .dump command and user_version

[Wout Mertens]

I noticed that `.dump` does not output the user_version pragma. It seems to
me that that is part of the database data?

I don't actually use it, but it might be interesting to add this for
completeness?

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> pragma user_version=50;
sqlite> pragma user_version;
50
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[Cezary H. Noweta]

Hello,

On 2018-01-22 13:00, Iulian Onofrei wrote:


I have a "REAL" column with float values having up to 2 decimals, and using
".dump" with the latest version incorrectly converts them like this:

"0.0" -> "0.0"
"0.05" -> "0.050002775"

> [...]

Did you consider an using of INTEGERs as fixed-point numbers? Nearly the 
same arithmetic with one additional division by 100 after multiplication 
and one additional multiplication by 100 (of dividend) before division.



I rely on dumps to track changes to some databases, so this breaks it
completely, and I currently had to pin sqlite to an older version which
isn't desired.


IMHO, a better and more concise way is to rem out the following line:

p->mode = p->cMode = MODE_Insert;

in ``shell.c'' and to recompile it. Two slashes and the problem has gone.

-- best regards

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

Re: [sqlite] .DUMP displays floats differently from SELECT

[Jens Alfke]

> On Jan 22, 2018, at 10:12 PM, Cezary H. Noweta  wrote:
> 
> I suppose that 20 digits is taken from the fact: 64ln(2)/ln(10)=19.2..., 
> however, for 64bit mantissa (long double) it is not enough (to be represented 
> exactly), for 53bit mantissa it is too many. Besides that, 17 digits + one 
> rounding digit fit in i64, while 20 decimal digits do not.

I agree. A standard `double` value with a 53-bit mantissa is only accurate to 
17 decimal digits, so formatting it with more precision than that is 
unnecessary, and exposes annoying roundoff errors.

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

Re: [sqlite] .DUMP displays floats differently from SELECT

[Cezary H. Noweta]

Hello,

On 2018-01-22 21:33, Keith Medcalf wrote:

The long and the short of it is that the output of .DUMP is correct.


I would say that the output is ``too correct''. ``.dump'' displays 20 
meaningful digits.


Let's look at the 17 meaningful digits. Mantissa divides every 
exponential range (i.e. range , which is divided linearly) 
into 1x2^52 equilengthy segments (binary/double) or 9x10^16 equilengthy 
segments (decimal/17). The segment represents a rational number.


Every decimal exponential range overlaps 4 or 5 binary exponential 
ranges. Let's consider decimal exponential range <0.1; 1> -- 17 digits 
mantissa gives a density of (9x10^16)/(9x10^-1)=10^17 numbers per one.


Let's take the densest binary exponential range which overlaps the above 
mentioned decimal one: <1/16;1/8> -- 53 digits binary mantissa gives a 
density of (1x2^52)/(1x2^-4)=2^56=7.2...x10^16<10^17 numbers per one.


Let's find a pair of binary/decimal exponential ranges which overlap 
each other and a ratio of binary numbers density to decimal numbers 
density is highest possible (if greater then one, then same binary 
numbers in the range cannot be represented by 17 digits decimal float).


Believe me or not, (for doubles) the ranges are: decimal: 
<10^-146;10^-145> -- density: (9x10^16)/(9x10^-146)=10^162 numbers per 
one; binary: <2^-486;2^-485> -- density: 
(1x2^52)/(1x2^-486)=2^538=8.99..x10^161<10^162 numbers per one. The 
ratio < 1. So 17 decimal digits is enough for an exact representation of 
IEEE64 float.


I suppose that 20 digits is taken from the fact: 64ln(2)/ln(10)=19.2..., 
however, for 64bit mantissa (long double) it is not enough (to be 
represented exactly), for 53bit mantissa it is too many. Besides that, 
17 digits + one rounding digit fit in i64, while 20 decimal digits do not.


The error of conversion depends on the width of intermediate floats -- 
not on the width of a integral part. Further increasing of number of 
meaningful digits does nothing. It is important to use ``long double'' 
for multiplication. Previous (<= 3.21) implementation of ``AtoF()'' 
would loose 6 bits (40+ multiplications in the worst case), if it was 
compiled with a compiler which mapped ``long double'' to ``double''. 
Integer had 18/19 digits -- it did not helped.


For example, let's look at the first OP's number: 0.05 -- it is 
1.Ax2^-5 => 0.050003 is enough for an exact 
conversion. Neighboring numbers are: 1.9x2^-5 => 
0.049996, and 1.Bx2^-5 => 0.050010.


17 digits is also enough for the densest range 
(<2^-486;2^-485>n<10^-146;10^-145>):
1.FF999x2^-486 => 1.0002594838824945E-146, 
1.FF99Ax2^-486 => 1.0002594838824946E-146, 
1.FF99Bx2^-486 => 1.0002594838824947E-146.


-- best regards

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

Re: [sqlite] .DUMP displays floats differently from SELECT

[petern]

FYI. There's no need to pin back the whole db version just to get the old
style dbdump format.
The original dump is available from the distribution as a standalone
program here:

http://www.sqlite.org/src/artifact/819eb33f6ff788a4

--dbdump.c--

** If this file is compiled with -DDBDUMP_STANDALONE then a "main()" routine
** is included so that this routine becomes a command-line utility.  The
** command-line utility takes two or three arguments which are the name
** of the database file, the schema, and optionally the table

Compile and enjoy.
Peter


On Mon, Jan 22, 2018 at 11:46 AM, Tony Papadimitriou  wrote:

> I reported this same issue in May 2017 (http://mailinglists.sqlite.or
> g/cgi-bin/mailman/private/sqlite-users/2017-May/072714.html)
> I too consider this a problem (diffing dumps is one good reason why) but,
> unfortunately, this was an intentional change by this check-in:
>
> [7359fcac] Increase the number of significant digits in floating point
> literals on ".dump" output from the shell.
>
> Tony
> -Original Message- From: Iulian Onofrei
>
> This is clearly a bug, as it outputs incorrect and different output from
> the
> previous versions.
>
> I have a "REAL" column with float values having up to 2 decimals, and using
> ".dump" with the latest version incorrectly converts them like this:
>
> "0.05" -> "0.050002775"
>
> I rely on dumps to track changes to some databases, so this breaks it
> completely, and I currently had to pin sqlite to an older version which
> isn't desired.
>
> Thank you,
> iulianOnofrei
>
> ___
> 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] .DUMP displays floats differently from SELECT

[petern]

FYI.  There's no need to pin the whole db version back to get original
dbdump formatting.
The original distribution standalone dump is still available in its full
glory here:

http://www.sqlite.org/src/artifact/819eb33f6ff788a4

--dbdump.c--

** If this file is compiled with -DDBDUMP_STANDALONE then a "main()" routine
** is included so that this routine becomes a command-line utility.  The
** command-line utility takes two or three arguments which are the name
** of the database file, the schema, and optionally the table

Peter




On Mon, Jan 22, 2018 at 11:46 AM, Tony Papadimitriou  wrote:

> I reported this same issue in May 2017 (http://mailinglists.sqlite.or
> g/cgi-bin/mailman/private/sqlite-users/2017-May/072714.html)
> I too consider this a problem (diffing dumps is one good reason why) but,
> unfortunately, this was an intentional change by this check-in:
>
> [7359fcac] Increase the number of significant digits in floating point
> literals on ".dump" output from the shell.
>
> Tony
> -Original Message- From: Iulian Onofrei
>
> This is clearly a bug, as it outputs incorrect and different output from
> the
> previous versions.
>
> I have a "REAL" column with float values having up to 2 decimals, and using
> ".dump" with the latest version incorrectly converts them like this:
>
> "0.05" -> "0.050002775"
>
> I rely on dumps to track changes to some databases, so this breaks it
> completely, and I currently had to pin sqlite to an older version which
> isn't desired.
>
> Thank you,
> iulianOnofrei
>
> ___
> 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] .DUMP displays floats differently from SELECT

[Keith Medcalf]

When you use a "select" from the shell to output a value converted to text, it 
outputs the value "doctored up" (coddled) for display to humans rather than 
display the true (as in actual) floating point value.  This is because "most 
people" do not understand how computers (binary floating point in particular) 
work and want "pretty" output that looks like what they typed in rather than 
the "true and accurate" representation.  

This behaviour (described above) is a bug (in my opinion) because it encourages 
people to not understand what it is they are doing, and lay blame for their 
misunderstanding at some door other than their own.  However, it would seem 
that many (most) user interfaces are "buggy" in that they prefer to 
molly-coddle rather than be honest and truthful, thus encouraging by its own 
(rather unfortunate) feedback loop perpetual ignorance by those using the bug 
ridden products (which is almost everything that uses binary floating point).

Those interested in truth and accuracy realize that the entire binary floating 
point system is an approximation of base-10 and that (like everything else) 
should only be coddled at first input and last output, and not coddled anywhere 
in between.  Intermediate coddling should never be used for any purpose.

That said, however, the purpose of .DUMP is not to present information for the 
coddling of the user.  It is for the purpose of generating SQL which, when 
imported back in to an "empty" database, will result in *exactly* what was in 
the database which was dumped.  This means that the EXACT binary floating point 
value must be dumped and reloaded, not the coddled user ignorantificated 
version presented by other interfaces, since it is possible for the coddling to 
display values which, when reloaded, do *not* result in the same binary value 
as that which existed before the coddling operation.

That is, depending on the method used to coddle , the uncoddling my produce 
a different value of  that that which originally existed, even though it is 
possible that the new value of  may just happen to also coddle to the same 
coddled display as the actual true value of  before the "round tripping" 
through the coddling functions.  This "coddling error" may multiply each time 
the values are successively coddled and uncoddled until the resulting value of 
 no longer coddles to the same "apparent value" as the original .  The 
"standards" try to minimize the possibility of such errors, however, they still 
occur and cannot be prevented except by careful use of correctly designed 
numerical methods.

This is the nature of binary floating point and there have been many pages 
(probably in the hundreds of thousands or more) written over the last couple of 
millenia describing the difficulties of, and how to deal with, the problems 
created by the limited human cognition of arithmetic quantities in various 
bases, and more lately, on the peculiarities in particular of "binary floating 
point" representation of base-10 numbers and calculations with them.

Most of these issues had been addressed by the last quarter of the last 
century, however, there continues to be new people suddenly rediscovering that 
which was old and ancient news and making much ado out of it (take for example 
the latest so called meltdown and spectre CPU flaws that were recently 
"rediscovered" but were known since the 1950's and simply ignored by those 
hardware manufacturers and software developers affected by them in the here and 
now).

The long and the short of it is that the output of .DUMP is correct.  It 
contains the true and accurate binary floating point representation into 
base-10 and that particular representation does in fact exactly "round trip" 
through SQLite3 accurately, maintaining 100% true internal binary 
representation of the values that are stored in the database.  In fact, those 
representations round trip through almost all other binary-floating point 
representation converters with 100% binary accuracy.  And this is the goal of 
the .dump command -- it is for preservation of exact values, not for human 
consumption.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Iulian Onofrei
>Sent: Monday, 22 January, 2018 05:01
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] .DUMP displays floats differently from SELECT
>
>Hi,
>
>This is clearly a bug, as it outputs incorrect and different output
>from the
>previous versions.
>
>I have a "REAL" column with float values having up to 2 decimals, and
>using
>".dump" with the latest

Re: [sqlite] .DUMP displays floats differently from SELECT

[Tony Papadimitriou]

I reported this same issue in May 2017 
(http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2017-May/072714.html)
I too consider this a problem (diffing dumps is one good reason why) but, 
unfortunately, this was an intentional change by this check-in:


[7359fcac] Increase the number of significant digits in floating point 
literals on ".dump" output from the shell.


Tony
-Original Message- 
From: Iulian Onofrei


This is clearly a bug, as it outputs incorrect and different output from the
previous versions.

I have a "REAL" column with float values having up to 2 decimals, and using
".dump" with the latest version incorrectly converts them like this:

"0.05" -> "0.050002775"

I rely on dumps to track changes to some databases, so this breaks it
completely, and I currently had to pin sqlite to an older version which
isn't desired.

Thank you,
iulianOnofrei

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

Re: [sqlite] .DUMP displays floats differently from SELECT

[J Decker]

There was work to 'speed up float formatting' which although fast is
apparently inaccurate.
One I Was working on was pretty fast, but didn't format things correctly
because of minor decimals.

On Mon, Jan 22, 2018 at 9:14 AM, David Raymond 
wrote:

> While maybe different formatting, how is that wrong? Remember, if you've
> declared the columns to be of type real then they're stored as an 8 byte
> binary floating point number. There is no exact representation of .05 in
> binary: so it's giving you all the digits that are stored. See #16 in the
> FAQ http://www.sqlite.org/faq.html#q16
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Iulian Onofrei
> Sent: Monday, January 22, 2018 7:01 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] .DUMP displays floats differently from SELECT
>
> Hi,
>
> This is clearly a bug, as it outputs incorrect and different output from
> the
> previous versions.
>
> I have a "REAL" column with float values having up to 2 decimals, and using
> ".dump" with the latest version incorrectly converts them like this:
>
> "0.0" -> "0.0"
> "0.05" -> "0.050002775"
> "0.06" -> "0.059997779"
> "0.07" -> "0.070006661"
> "0.08" -> "0.080001665"
> "0.09" -> "0.089996669"
> "0.1" -> "0.1555"
> "0.11" -> "0.1155"
> "0.12" -> "0.11999555"
> "0.13" -> "0.13000444"
> "0.15" -> "0.14999444"
> "0.16" -> "0.16000333"
> "0.17" -> "0.17001221"
> "0.18" -> "0.17999333"
> "0.19" -> "0.19000222"
> "0.21" -> "0.20999222"
> "0.22" -> "0.22000111"
> "0.23" -> "0.23000999"
> "0.24" -> "0.23999111"
> "0.25" -> "0.25"
> "0.26" -> "0.26000888"
> "0.27" -> "0.27001776"
> "0.28" -> "0.28002664"
> "0.29" -> "0.28998001"
> "0.3" -> "0.29998889"
> "0.32" -> "0.32000666"
> "0.33" -> "0.33001554"
> "0.37" -> "0.36999555"
> "0.38" -> "0.38000444"
> "0.4" -> "0.4000222"
> "0.41" -> "0.40997557"
> "0.43" -> "0.42999333"
> "0.44" -> "0.44000222"
> "0.45" -> "0.4500111"
> "0.46" -> "0.46001998"
> "0.49" -> "0.48999111"
> "0.5" -> "0.5"
> "0.51" -> "0.51000888"
> "0.52" -> "0.52001776"
> "0.54" -> "0.54003552"
> "0.56" -> "0.56005329"
> "0.57" -> "0.56995115"
> "0.58" -> "0.57996003"
> "0.6" -> "0.59997779"
> "0.61" -> "0.60998667"
> "0.65" -> "0.6500222"
> "0.67" -> "0.67003996"
> "0.7" -> "0.69995559"
> "0.73" -> "0.72998223"
> "0.75" -> "0.75"
>
> I rely on dumps to track changes to some databases, so this breaks it
> completely, and I currently had to pin sqlite to an older version which
> isn't desired.
>
> Thank you,
> iulianOnofrei
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[David Raymond]

While maybe different formatting, how is that wrong? Remember, if you've 
declared the columns to be of type real then they're stored as an 8 byte binary 
floating point number. There is no exact representation of .05 in binary: so 
it's giving you all the digits that are stored. See #16 in the FAQ 
http://www.sqlite.org/faq.html#q16


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Iulian Onofrei
Sent: Monday, January 22, 2018 7:01 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] .DUMP displays floats differently from SELECT

Hi,

This is clearly a bug, as it outputs incorrect and different output from the
previous versions.

I have a "REAL" column with float values having up to 2 decimals, and using
".dump" with the latest version incorrectly converts them like this:

"0.0" -> "0.0"
"0.05" -> "0.050002775"
"0.06" -> "0.059997779"
"0.07" -> "0.070006661"
"0.08" -> "0.080001665"
"0.09" -> "0.089996669"
"0.1" -> "0.1555"
"0.11" -> "0.1155"
"0.12" -> "0.11999555"
"0.13" -> "0.13000444"
"0.15" -> "0.14999444"
"0.16" -> "0.16000333"
"0.17" -> "0.17001221"
"0.18" -> "0.17999333"
"0.19" -> "0.19000222"
"0.21" -> "0.20999222"
"0.22" -> "0.22000111"
"0.23" -> "0.23000999"
"0.24" -> "0.23999111"
"0.25" -> "0.25"
"0.26" -> "0.26000888"
"0.27" -> "0.27001776"
"0.28" -> "0.28002664"
"0.29" -> "0.28998001"
"0.3" -> "0.29998889"
"0.32" -> "0.32000666"
"0.33" -> "0.33001554"
"0.37" -> "0.36999555"
"0.38" -> "0.38000444"
"0.4" -> "0.4000222"
"0.41" -> "0.40997557"
"0.43" -> "0.42999333"
"0.44" -> "0.44000222"
"0.45" -> "0.4500111"
"0.46" -> "0.46001998"
"0.49" -> "0.48999111"
"0.5" -> "0.5"
"0.51" -> "0.51000888"
"0.52" -> "0.52001776"
"0.54" -> "0.54003552"
"0.56" -> "0.56005329"
"0.57" -> "0.56995115"
"0.58" -> "0.57996003"
"0.6" -> "0.59997779"
"0.61" -> "0.60998667"
"0.65" -> "0.6500222"
"0.67" -> "0.67003996"
"0.7" -> "0.69995559"
"0.73" -> "0.72998223"
"0.75" -> "0.75"

I rely on dumps to track changes to some databases, so this breaks it
completely, and I currently had to pin sqlite to an older version which
isn't desired.

Thank you,
iulianOnofrei



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
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] .DUMP displays floats differently from SELECT

[Iulian Onofrei]

Hi,

This is clearly a bug, as it outputs incorrect and different output from the
previous versions.

I have a "REAL" column with float values having up to 2 decimals, and using
".dump" with the latest version incorrectly converts them like this:

"0.0" -> "0.0"
"0.05" -> "0.050002775"
"0.06" -> "0.059997779"
"0.07" -> "0.070006661"
"0.08" -> "0.080001665"
"0.09" -> "0.089996669"
"0.1" -> "0.1555"
"0.11" -> "0.1155"
"0.12" -> "0.11999555"
"0.13" -> "0.13000444"
"0.15" -> "0.14999444"
"0.16" -> "0.16000333"
"0.17" -> "0.17001221"
"0.18" -> "0.17999333"
"0.19" -> "0.19000222"
"0.21" -> "0.20999222"
"0.22" -> "0.22000111"
"0.23" -> "0.23000999"
"0.24" -> "0.23999111"
"0.25" -> "0.25"
"0.26" -> "0.26000888"
"0.27" -> "0.27001776"
"0.28" -> "0.28002664"
"0.29" -> "0.28998001"
"0.3" -> "0.29998889"
"0.32" -> "0.32000666"
"0.33" -> "0.33001554"
"0.37" -> "0.36999555"
"0.38" -> "0.38000444"
"0.4" -> "0.4000222"
"0.41" -> "0.40997557"
"0.43" -> "0.42999333"
"0.44" -> "0.44000222"
"0.45" -> "0.4500111"
"0.46" -> "0.46001998"
"0.49" -> "0.48999111"
"0.5" -> "0.5"
"0.51" -> "0.51000888"
"0.52" -> "0.52001776"
"0.54" -> "0.54003552"
"0.56" -> "0.56005329"
"0.57" -> "0.56995115"
"0.58" -> "0.57996003"
"0.6" -> "0.59997779"
"0.61" -> "0.60998667"
"0.65" -> "0.6500222"
"0.67" -> "0.67003996"
"0.7" -> "0.69995559"
"0.73" -> "0.72998223"
"0.75" -> "0.75"

I rely on dumps to track changes to some databases, so this breaks it
completely, and I currently had to pin sqlite to an older version which
isn't desired.

Thank you,
iulianOnofrei



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[Clemens Ladisch]

Tony Papadimitriou wrote:
> Apparently, this was an intentional change by this check-in :
>
> [7359fcac] Increase the number of significant digits in floating point 
> literals on ".dump" output from the shell.
>
> I don't know what problem this change actually solved

Scott Robinson already mentioned it:
| The dump version is output for precision so that every digit goes back
| into the recreated database exactly as it came out.

(Where "every digit" actually means the binary representation.)


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

Re: [sqlite] .DUMP displays floats differently from SELECT

[Tony Papadimitriou]

Apparently, this was an intentional change by this check-in :

[7359fcac] Increase the number of significant digits in floating point literals 
on ".dump" output from the shell.

I don't know what problem this change actually solved (as in my case it 
introduced one) but for my own copy I changed %!.20g to %!.16g and everything 
seems to be OK now without compromising accuracy (I hope).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[David Raymond]

On the plus side they come out equal, so apart from neatness of the display, 
all's good.
Interesting how the auto EQP output shows up in the .dump output. But at least 
it starts with -- so won't harm anything.

SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table xxx (x primary key);
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> insert into xxx values (1.23);
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> select * from xxx;
--EQP-- 0,0,0,SCAN TABLE xxx
x
1.23
Run Time: real 0.004 user 0.00 sys 0.00

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx (x primary key);
--EQP-- 0,0,0,SCAN TABLE xxx
INSERT INTO xxx(x) VALUES(1.2299822);
COMMIT;

sqlite> INSERT INTO xxx(x) VALUES(1.2299822);
Run Time: real 0.000 user 0.00 sys 0.00
Error: UNIQUE constraint failed: xxx.x

sqlite> delete from xxx;
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> INSERT INTO xxx(x) VALUES(1.2299822);
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> select * from xxx;
--EQP-- 0,0,0,SCAN TABLE xxx
x
1.23
Run Time: real 0.003 user 0.00 sys 0.00

sqlite> insert into xxx values (1.23);
Run Time: real 0.000 user 0.00 sys 0.00
Error: UNIQUE constraint failed: xxx.x



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Wednesday, May 03, 2017 2:12 PM
To: SQLite mailing list
Subject: Re: [sqlite] .DUMP displays floats differently from SELECT

In a previous message I suggest that behaviour under macOS changed between 
SQLite 3.16.0 and SQLite 3.18.0.  I then received information from a lurker who 
uses Windows 7:

SQLite version 3.17.0 2017-02-13 16:02:40
INSERT INTO "xxx" VALUES(1.23);
…

SQLite version 3.18.0 2017-03-28 18:48:43
INSERT INTO xxx VALUES(1.2299822);

It does seem that this is due to a recent change in SQLite source code.

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] .DUMP displays floats differently from SELECT

[Simon Slavin]

In a previous message I suggest that behaviour under macOS changed between 
SQLite 3.16.0 and SQLite 3.18.0.  I then received information from a lurker who 
uses Windows 7:

SQLite version 3.17.0 2017-02-13 16:02:40
INSERT INTO "xxx" VALUES(1.23);
…

SQLite version 3.18.0 2017-03-28 18:48:43
INSERT INTO xxx VALUES(1.2299822);

It does seem that this is due to a recent change in SQLite source code.

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

Re: [sqlite] .DUMP displays floats differently from SELECT

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Simon Slavin
> Sent: Wednesday, May 03, 2017 12:30 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] .DUMP displays floats differently from SELECT
>
>
> On 3 May 2017, at 3:40pm, Scott Robison  wrote:
>
> > On May 3, 2017 8:07 AM, "Tony Papadimitriou"  wrote:
> >
> >> While trying to search/replace some text from an SQLite3 dump I
> >> noticed that, unfortunately, .DUMP does not produce the exact same
> >> numbers as a plain SELECT on the same values.
> >
> > I know all about expected floating point inaccuracies, but I don’t see
> > why it should matter in this case as we have two different places in
> > the same app (SQLite3) where the same number is ‘displayed’ using
> > whatever default format.  IMO, in both places the same number should
> > display exactly the same (i.e., using the same format), both for
> > consistency and easy verification between dump and actual database.
>
> That’s a matter of preference.  There is one possible bug here.  If the 
> results
> of reading the dump file back in produces a different value to the original
> INSERT, that’s a problem.  So I tried it:
>
> SQLite version 3.16.0 2016-11-04 19:09:39 Enter ".help" for usage hints.
> sqlite> create table xxx(x);
> sqlite> insert into xxx values(1.23);
> sqlite> select * from xxx;
> 1.23
> sqlite> .dump
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE xxx(x);
> INSERT INTO "xxx" VALUES(1.23);
> COMMIT;
>
> Hmm.  Cannot reproduce your problem in my version of the command-line
> tool.  You’re using 3.18.0.  I’m using 3.16.0.  Perhaps its a recent change.  
> Or
> perhaps there’s a difference in a library between my platform (macOS) and
> yours.
>
> Simon.

Nor I with 3.14.1 on Windows 7:

SQLite version 3.14.1 2016-08-11 18:53:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table xxx(x);
sqlite> insert into xxx values(1.23);
sqlite> select * from xxx;
1.23
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(x);
INSERT INTO "xxx" VALUES(1.23);
COMMIT;
sqlite>

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

Effective immediately my new email address is william.dr...@l3t.com. Please 
update your records.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[Simon Slavin]

On 3 May 2017, at 3:40pm, Scott Robison  wrote:

> On May 3, 2017 8:07 AM, "Tony Papadimitriou"  wrote:
> 
>> While trying to search/replace some text from an SQLite3 dump I noticed
>> that, unfortunately, .DUMP does not produce the exact same numbers as a
>> plain SELECT on the same values.
> 
> I know all about expected floating point inaccuracies, but I don’t see why
> it should matter in this case as we have two different places in the same
> app (SQLite3) where the same number is ‘displayed’ using whatever default
> format.  IMO, in both places the same number should display exactly the
> same (i.e., using the same format), both for consistency and easy
> verification between dump and actual database.

That’s a matter of preference.  There is one possible bug here.  If the results 
of reading the dump file back in produces a different value to the original 
INSERT, that’s a problem.  So I tried it:

SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> create table xxx(x);
sqlite> insert into xxx values(1.23);
sqlite> select * from xxx;
1.23
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(x);
INSERT INTO "xxx" VALUES(1.23);
COMMIT;

Hmm.  Cannot reproduce your problem in my version of the command-line tool.  
You’re using 3.18.0.  I’m using 3.16.0.  Perhaps its a recent change.  Or 
perhaps there’s a difference in a library between my platform (macOS) and yours.

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

Re: [sqlite] .DUMP displays floats differently from SELECT

[Scott Robison]

On May 3, 2017 8:07 AM, "Tony Papadimitriou"  wrote:

While trying to search/replace some text from an SQLite3 dump I noticed
that, unfortunately, .DUMP does not produce the exact same numbers as a
plain SELECT on the same values.

I know all about expected floating point inaccuracies, but I don’t see why
it should matter in this case as we have two different places in the same
app (SQLite3) where the same number is ‘displayed’ using whatever default
format.  IMO, in both places the same number should display exactly the
same (i.e., using the same format), both for consistency and easy
verification between dump and actual database.


The select version is output for human readability. The dump version is
output for precision so that every digit goes back into the recreated
database exactly as it came out.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .DUMP displays floats differently from SELECT

[Tony Papadimitriou]

While trying to search/replace some text from an SQLite3 dump I noticed that, 
unfortunately, .DUMP does not produce the exact same numbers as a plain SELECT 
on the same values.

I know all about expected floating point inaccuracies, but I don’t see why it 
should matter in this case as we have two different places in the same app 
(SQLite3) where the same number is ‘displayed’ using whatever default format.  
IMO, in both places the same number should display exactly the same (i.e., 
using the same format), both for consistency and easy verification between dump 
and actual database.

Below is a sample script and its output that demonstrate the issue (using 
SQLite 3.18.0 2017-03-28 18:48:43):

SCRIPT:

create table xxx(x);
insert into xxx values(1.23);
select * from xxx;
.dump

OUTPUT:

1.23  
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(x);
INSERT INTO xxx VALUES(1.2299822);
COMMIT;

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

[sqlite] .DUMP output compatibility

[Kees Nuyt]

On Fri, 6 May 2016 01:56:47 +0300, "Tony Papadimitriou"
 wrote:

> Windows!

There's gawk for windows, for this purpose almost the same as
sed. And apart form cygwin, there is a unch of unix utilities
under the name of UnxUtils.
See https://en.wikipedia.org/wiki/UnxUtils
and https://sourceforge.net/projects/unxutils/


-- 
Regards,
Kees Nuyt



>-Original Message- 
>From: Richard Hipp
>Sent: Friday, May 06, 2016 1:55 AM
>To: SQLite mailing list
>Subject: Re: [sqlite] .DUMP output compatibility
>
>On 5/5/16, Tony Papadimitriou  wrote:
>> Is it possible for .DUMP to produce table/field names quoted with `
>> (backquote) instead of ? (double quote) for compatibility with MySQL?
>> Or is this already adjustable by some setting I missed?
>
>Pipe the output through sed (https://en.wikipedia.org/wiki/Sed)

[sqlite] .DUMP output compatibility

[Tony Papadimitriou]

Windows!

-Original Message- 
From: Richard Hipp
Sent: Friday, May 06, 2016 1:55 AM
To: SQLite mailing list
Subject: Re: [sqlite] .DUMP output compatibility

On 5/5/16, Tony Papadimitriou  wrote:
> Is it possible for .DUMP to produce table/field names quoted with `
> (backquote) instead of ? (double quote) for compatibility with MySQL?
> Or is this already adjustable by some setting I missed?

Pipe the output through sed (https://en.wikipedia.org/wiki/Sed)
-- 
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

[sqlite] .DUMP output compatibility

[Tony Papadimitriou]

Is it possible for .DUMP to produce table/field names quoted with ` (backquote) 
instead of ? (double quote) for compatibility with MySQL?
Or is this already adjustable by some setting I missed?

Thank you.

[sqlite] .DUMP output compatibility

[Richard Hipp]

On 5/5/16, Tony Papadimitriou  wrote:
> Is it possible for .DUMP to produce table/field names quoted with `
> (backquote) instead of ? (double quote) for compatibility with MySQL?
> Or is this already adjustable by some setting I missed?

Pipe the output through sed (https://en.wikipedia.org/wiki/Sed)
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] .DUMP output compatibility

[Warren Young]

On May 5, 2016, at 4:56 PM, Tony Papadimitriou  wrote:
> 
> Windows!

So install Cygwin.

There may be more to it than the quoting style.  I?ve used the following script 
for moving data the other direction (MySQL to SQLite):

  https://gist.github.com/esperlu/943776

You might have to create the inverse of some of those transformations to get 
MySQL to accept the data.

[sqlite] dump only data, change schema, reload

[Domingo Alvarez Duarte]

Hello !  

I had this problem before and asked to add this option to sqlite but somehow
it was not added so now I'm submitting here a patch that adds this
functionality to shell.c updated to today repository.  

Please consider add this to sqlite !  

Cheers !  

?  

--- /third-party/sqlite3/src/shell2.c
+++ /third-party/sqlite3/src/shell0.c
@@ -625,8 +625,6 @@
?? int *aiIndent; /* Array of indents used in MODE_Explain
*/
?? int nIndent;?? /* Size of array aiIndent[] */
?? int iIndent;?? /* Index of current op in aiIndent[] */
-? int dumpDataOnly; /*when dump a database exclude schema */
-? int doStartTransaction; /* when dumping schema only before first record
output "BEGIN;" */
?};
?
?/*
@@ -989,7 +987,6 @@
?? break;
 }
 case MODE_Semi:
-? if((p->cnt == 0) && p->doStartTransaction ) fprintf(p->out,"BEGIN
TRANSACTION;\n");
 case MODE_List: {
?? if( p->cnt++==0 && p->showHeader ){
 for(i=0; idumpDataOnly ){
?? if( strcmp(zTable, "sqlite_sequence")==0 ){
 zPrepStmt = "DELETE FROM sqlite_sequence;\n";
?? }else if( sqlite3_strglob("sqlite_stat?", zTable)==0 ){
@@ -1844,7 +1840,6 @@
 return 0;
?? }else{
 utf8_printf(p->out, "%s;\n", zSql);
-? }
?? }
?
?? if( strcmp(zType, "table")==0 ){
@@ -1956,7 +1951,6 @@
?? ".dump ?TABLE? ...? Dump the database in an SQL text format\n"
?? " If TABLE specified,
only dump tables matching\n"
?? " LIKE pattern TABLE.\n"
-? ".dumpdata? ?TABLE? ... Like .dump without schema\n"
?? ".echo on|off?? Turn command echo on or off\n"
?? ".eqp on|off??? Enable or disable automatic EXPLAIN
QUERY PLAN\n"
?? ".exit? Exit this program\n"
@@ -2964,8 +2958,7 @@
 rc = shell_dbinfo_command(p, nArg, azArg);
?? }else
?
-? if( c=='d' && ((strncmp(azArg[0], "dump", n)==0) ||
-? (p->dumpDataOnly = (strncmp(azArg[0], "dumpdata", n)==0))) ){
+? if( c=='d' && strncmp(azArg[0], "dump", n)==0 ){
 open_db(p, 0);
 /* When playing back a "dump", the content might appear in an order
 ** which causes immediate foreign key constraints to be violated.
@@ -2985,16 +2978,14 @@
 "SELECT name, type, sql FROM sqlite_master "
 "WHERE sql NOT NULL AND type=='table' AND
name!='sqlite_sequence'"
?? );
-? if(!p->dumpDataOnly){
-??? run_schema_dump_query(p, 
-? "SELECT name, type, sql FROM sqlite_master "
-? "WHERE name=='sqlite_sequence'"
-??? );
-??? run_table_dump_query(p,
-? "SELECT sql FROM sqlite_master "
-? "WHERE sql NOT NULL AND type IN
('index','trigger','view')", 0
-??? );
-? }
+? run_schema_dump_query(p, 
+??? "SELECT name, type, sql FROM sqlite_master "
+??? "WHERE name=='sqlite_sequence'"
+? );
+? run_table_dump_query(p,
+??? "SELECT sql FROM sqlite_master "
+??? "WHERE sql NOT NULL AND type IN ('index','trigger','view')",
0
+? );
 }else{
?? int i;
?? for(i=1; idumpDataOnly){
-? run_table_dump_query(p,
-??? "SELECT sql FROM sqlite_master "
-??? "WHERE sql NOT NULL"
-??? "? AND type IN ('index','trigger','view')"
-??? "? AND tbl_name LIKE shellstatic()", 0
-? );
-??? }
+??? run_table_dump_query(p,
+? "SELECT sql FROM sqlite_master "
+? "WHERE sql NOT NULL"
+? "? AND type IN ('index','trigger','view')"
+? "? AND tbl_name LIKE shellstatic()", 0
+??? );
 zShellStatic = 0;
?? }
 }
@@ -3021,7 +3010,6 @@
 sqlite3_exec(p->db, "PRAGMA writable_schema=OFF;", 0, 0, 0);
 sqlite3_exec(p->db, "RELEASE dump;", 0, 0, 0);
 raw_printf(p->out, p->nErr ? "ROLLBACK; -- due to errors\n" :
"COMMIT;\n");
-??? p->dumpDataOnly = 0; /* reset data only flag */
?? }else
?
?? if( c=='e' && strncmp(azArg[0], "echo", n)==0 ){
@@ -3082,7 +3070,6 @@
 memcpy(&data, p, sizeof(data));
 data.showHeader = 0;
 data.cMode = data.mode = MODE_Semi;
-??? data.doStartTransaction = 1;
 rc = sqlite3_exec(p->db,
??? "SELECT sql FROM"
??? "? (SELECT sql sql, type type, tbl_name tbl_name, name name,
rowid x"
@@ -3119,7 +3106,6 @@
? shell_callback, &data, &zErrMsg);
?? raw_printf(p->out, "ANAL

[sqlite] dump only data, change schema, reload

[Luca Ferrari]

On Wed, Mar 23, 2016 at 1:02 PM, Keith Medcalf  wrote:
>
> Do you mean something like this, perchance?  This sets .mode insert 
>  then does a select ..., which outputs the selected data in the 
> form of INSERT statements into a table called .  The .mode list 
> just allows other commands to be added to the file ...


I already considered the .mode insert, the problem is that while all
databases have the same table columns not all have the same column
order (some columns have been added to existing databases and some
databases where created with new schemas), so having INSERTs without
the column list is not suitable.

Another problem I'm facing is foreign keys from other tables, and
therefore I'm looking at a script that, via table_info, extracts the
column list and builds SQL to export and reimport data.
But it is a lot of work to just drop a constraint, that's why I'm
searching for smarter ways.

Luca

[sqlite] dump only data, change schema, reload

[Stephan Beal]

On Wed, Mar 23, 2016 at 10:50 AM, Luca Ferrari 
wrote:

> ...The problem is that .dump provides data and schema, while I'd like to
> have data only.
> Other commands like .clone and .backup works pretty much the same, as
> far as I understand.
>
> This leads me to either use awk/sed to manipulate the dump or to
> hard-code single select statements into the script to extract data.
> Is there any smarter way to dump only data in a loadable form?
>


Probably the simplest approach is something like (untested):

alter table original_table rename to foo; -- move the original table
create table original_table (...); -- w/ new schema
insert into original_table (a,b,c) select a,b,c from foo; -- assuming no
transformation needs to take place
drop table foo; -- though you'll probably want to keep the old copy "just
in case"


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] dump only data, change schema, reload

[Luca Ferrari]

Hi all,
I've a few hundreds sqlite3 database files, all almost equals except
for some constraint that has changed during time.
As I know, there is no way to alter constraint (e.g., unique indexes),
and therefore I have to migrate data to a new schema version.
That is possible because data will fit into the same table schema, but
I'm looking for a way to do it massively (and remotely).
So far I was thinking to wrap a shell script to dump the content of a
single database, create the new schema and reload the dump in it.
The problem is that .dump provides data and schema, while I'd like to
have data only.
Other commands like .clone and .backup works pretty much the same, as
far as I understand.

This leads me to either use awk/sed to manipulate the dump or to
hard-code single select statements into the script to extract data.
Is there any smarter way to dump only data in a loadable form?

Thanks
Luca

[sqlite] dump only data, change schema, reload

[Keith Medcalf]

Do you mean something like this, perchance?  This sets .mode insert  
then does a select ..., which outputs the selected data in the form of INSERT 
statements into a table called .  The .mode list just allows other 
commands to be added to the file ...

.output aesodata.sql
.mode list
select 'BEGIN;';
.mode insert Interval
  select *
from Interval
order by Interval;
.mode list
select 'COMMIT;';
select 'BEGIN;';
.mode insert Actual
  select *
from Actual
order by Interval;
.mode list
select 'COMMIT;';
select 'BEGIN;';
.mode insert Forecast
  select *
from Forecast
order by Interval, Projected;
.mode list
select 'COMMIT;';
select 'VACUUM;';
select 'ANALYZE;';

which produces (elided output) like follows:

BEGIN;
INSERT INTO Interval VALUES(1996,9,30,'24',234486);
INSERT INTO Interval VALUES(1996,10,1,'01',234487);
INSERT INTO Interval VALUES(1996,10,1,'02',234488);
INSERT INTO Interval VALUES(1996,10,1,'03',234489);
INSERT INTO Interval VALUES(1996,10,1,'04',234490);
INSERT INTO Interval VALUES(1996,10,1,'05',234491);
INSERT INTO Interval VALUES(1996,10,1,'06',234492);
INSERT INTO Interval VALUES(1996,10,1,'07',234493);
INSERT INTO Interval VALUES(1996,10,1,'08',234494);
INSERT INTO Interval VALUES(1996,10,1,'09',234495);
...
COMMIT;
BEGIN;
INSERT INTO Actual VALUES(234487,1325513676,13.5,4998);
INSERT INTO Actual VALUES(234488,1325513676,13.5,4891);
INSERT INTO Actual VALUES(234489,1325513676,12.0,4831);
INSERT INTO Actual VALUES(234490,1325513676,10.5,4791);
INSERT INTO Actual VALUES(234491,1325513676,10.5,4778);
INSERT INTO Actual VALUES(234492,1325513676,13.5,4862);
INSERT INTO Actual VALUES(234493,1325513676,15.63,5173);
INSERT INTO Actual VALUES(234494,1325513676,19.65,5624);
INSERT INTO Actual VALUES(234495,1325513676,21.0,5773);
INSERT INTO Actual VALUES(234496,1325513676,21.0,5848);
...
COMMIT;
BEGIN;
INSERT INTO Forecast VALUES(234487,234486,1325513676,13.5,4911);
INSERT INTO Forecast VALUES(234488,234487,1325513676,13.5,4771);
INSERT INTO Forecast VALUES(234489,234488,1325513676,10.5,4732);
INSERT INTO Forecast VALUES(234490,234489,1325513676,10.5,4706);
INSERT INTO Forecast VALUES(234491,234490,1325513676,10.5,4730);
INSERT INTO Forecast VALUES(234492,234491,1325513676,13.5,4828);
INSERT INTO Forecast VALUES(234493,234492,1325513676,15.88,5165);
INSERT INTO Forecast VALUES(234494,234493,1325513676,16.3,5569);
INSERT INTO Forecast VALUES(234495,234494,1325513676,21.0,5761);
INSERT INTO Forecast VALUES(234496,234495,1325513676,21.0,5855);
...
COMMIT;
VACUUM;
ANALYZE;

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Luca Ferrari
> Sent: Wednesday, 23 March, 2016 03:50
> To: SQLite
> Subject: [sqlite] dump only data, change schema, reload
> 
> Hi all,
> I've a few hundreds sqlite3 database files, all almost equals except
> for some constraint that has changed during time.
> As I know, there is no way to alter constraint (e.g., unique indexes),
> and therefore I have to migrate data to a new schema version.
> That is possible because data will fit into the same table schema, but
> I'm looking for a way to do it massively (and remotely).
> So far I was thinking to wrap a shell script to dump the content of a
> single database, create the new schema and reload the dump in it.
> The problem is that .dump provides data and schema, while I'd like to
> have data only.
> Other commands like .clone and .backup works pretty much the same, as
> far as I understand.
> 
> This leads me to either use awk/sed to manipulate the dump or to
> hard-code single select statements into the script to extract data.
> Is there any smarter way to dump only data in a loadable form?
> 
> Thanks
> Luca
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite dump makes wrong "CREATE VIEW" order

[Simon Slavin]

On 17 Apr 2014, at 3:42pm, Igor Tandetnik  wrote:

> If there's a bug anywhere in this, I'd say it's the fact that SQLite allowed 
> "DROP VIEW v2" statement to proceed.

Agreed.  Is there a similar bug if you try to drop a table that a FOREIGN KEY 
depends on ?

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

Re: [sqlite] sqlite dump makes wrong "CREATE VIEW" order

[Gerry Snyder]

On 4/17/2014 12:43 AM, Tyumentsev Alexander wrote:

sqlite in some cases dumps views in wrong order.



Interesting situation. My take on it would be that dump is a simple 
little tool designed to help move a database file from one place to 
another. In tricky situations, some editing of its output may be needed.


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

Re: [sqlite] sqlite dump makes wrong "CREATE VIEW" order

[Igor Tandetnik]

On 4/17/2014 3:43 AM, Tyumentsev Alexander wrote:

CREATE VIEW v2 as select id1 from test where id1;
CREATE VIEW v1 as select t1.id2 from test as t1 LEFT JOIN v2 ON
t1.id2=v2.id1;
DROP VIEW v2;

Is it the user responsibility to follow all dependencies and recreate
"VIEW" tree ?


I'd say it's the user's responsibility to not drop a view that is being 
referred to elsewhere. If there's a bug anywhere in this, I'd say it's 
the fact that SQLite allowed "DROP VIEW v2" statement to proceed.

--
Igor Tandetnik

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

Re: [sqlite] sqlite dump makes wrong "CREATE VIEW" order

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 17/04/14 00:43, Tyumentsev Alexander wrote:
> Is it the user responsibility to follow all dependencies and recreate 
> "VIEW" tree ?

The dumping is happening in the order that the views were created.  This
approach generally works, but fails in your example where you created
another view of the same name.  (There are some other edge cases where you
could create circular links between views.)

I can't see any way of resolving your issue since there is no general SQL
parser which is what would have to be run to work out dependency order.

Roger



-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlNP58cACgkQmOOfHg372QT0RwCg5gXqYYmQ0Ws1LqJaFBhdpvwJ
dLAAn2+8hvf+nl9jTR45Z2TYQsT6mAlB
=APvL
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite dump makes wrong "CREATE VIEW" order

[Tyumentsev Alexander]

sqlite in some cases dumps views in wrong order.

how to reproduce on linux:

1) Make the test database with 2 "view". v1 depends on v2. Recreate v2
cat <<_EOF_ | sqlite3 test.sqlite
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test ( "id1" integer(8,0), "id2" integer(8,0), "id3"
integer(8,0) );
CREATE VIEW v2 as select id1 from test where id1;
CREATE VIEW v1 as select t1.id2 from test as t1 LEFT JOIN v2 ON
t1.id2=v2.id1;
DROP VIEW v2;
CREATE VIEW v2 as select id1 from test where id1;
COMMIT;
_EOF_

2) Dump the base
sqlite3 test.sqlite .dump > test.sqlite.dump
cat test.sqlite.dump
  =>
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test ( "id1" integer(8,0), "id2" integer(8,0), "id3"
integer(8,0) );
CREATE VIEW v1 as select t1.id2 from test as t1 LEFT JOIN v2 ON
t1.id2=v2.id1;
CREATE VIEW v2 as select id1 from test where id1;
COMMIT;

Here "CREATE VIEW v1" and "CREATE VIEW v2" placed in wrong order.

3) try to restore
cat test.sqlite.dump | sqlite3 test2.sqlite
 =>
Error: near line 4: no such table: main.v2


Is it the user responsibility to follow all dependencies and recreate
"VIEW" tree ?

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

[sqlite] dump command in shell using test_demovfs

[Luca Sturaro]

Hi,
I'm trying to create a VFS for SQLite 3.7.17 porting.
I started from testdemo_vfs.c using it first under linux compiling and link
it (-DSQLITE_THREADSAFE=0 at compile time)
Using this VFS (the behavior is the same on my embedded platform and under
linux) I can create and access a db and tables, insert and select data from
that using sqlite shell and that's fine.
The problem is that .dump command displays only statements for the schema
while data are missed (No INSERT statements with data, but if I do a
"select" it works and data are there).
It seems to me that the problem is in run_schema_dump_query when the
command .dump is parsed but I'm not sure as it could be also al little bit
upper in the code.
Have I missed something or can anybody shed a light on this?

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

Re: [sqlite] .dump - what isn't preserved?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/03/13 04:24, Dave McKee wrote:
> What else isn't losslessly maintained in a dump -> load cycle?

Various database settings and any strings with nuls in them.  Also the
sqlite shell expects to be loaded into a clean database.  For example it
doesn't delete existing tables of the same name, and it unilaterally
deletes the contents of sqlite_sequence.  These are mostly convenience
issues rather than data loss, but attention should be paid at restore time.

I made a dump of the same database using the SQLite shell and using the
APSW shell (I'm the author).  The only actual data loss is SQLite shell
ignoring strings after the first nul.

  https://gist.github.com/rogerbinns/5064697

You'll note the APSW shell puts a lot of extra information in there that
would be relevant if you were doing a restore.

Something else the SQLite shell does is deal with corrupt databases.  The
last time I looked at the code, if it encountered an error iterating over
a table, then it would try again iterating backwards.  I don't remember
what it did with respect to errors if only part of the data could be
iterated over.

TLDR: good question and there are a *lot* of issues under the surface
depending on how you intend to do the restore and what things matter to
your code

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEwsFoACgkQmOOfHg372QSsTQCffwvaQFk1c/XAhZHJpPxCHflE
2PUAn1Rp0MVHtsUbNjbkEdYEBwf0omFc
=j1SZ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump - what isn't preserved?

[Simon Slavin]

On 1 Mar 2013, at 12:24pm, Dave McKee  wrote:

> I'm considering working with a dump of the SQL database, and I'm anxious to
> make sure that nothing gets lost in the transformation.

I assume you're talking about the '.dump' function of the shell tool, which 
creates a set of commands which could be used to reproduce the database.

> I'm aware that the database will effectively be vacuumed and that rowids
> won't be preserved. I'm happy about the first and can live with the second.

If you want to make sure that rowid values are preserved, define your own 
INTEGER PRIMARY KEY column in each table.  SQLite will (effectively) make this 
an alias to the rowid column and since SQLite knows you might be referring to 
those values by name, it will never change them in a VACUUM and the .dump 
command will list them.

> What else isn't losslessly maintained in a dump -> load cycle?
> 
> Is there anything else that would negatively affect database performance,
> the results of SQL queries or the databases configuration (e.g. WAL mode)?

WAL mode isn't a problem.  Your entire journals will be lost since you will be 
creating a new database.  I believe you're right that the .dump command does 
not dump any PRAGMA settings stored in the database (perhaps it should) so 
PRAGMA settings may be lost.

The thing that does come to mind is minor: any results of ANALYZE will be lost. 
 It's easy to regain them once you're read your data into a new database 
though, and this will update them at the same time.

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

[sqlite] .dump - what isn't preserved?

[Dave McKee]

I'm considering working with a dump of the SQL database, and I'm anxious to
make sure that nothing gets lost in the transformation.

I'm aware that the database will effectively be vacuumed and that rowids
won't be preserved. I'm happy about the first and can live with the second.

What else isn't losslessly maintained in a dump -> load cycle?

Is there anything else that would negatively affect database performance,
the results of SQL queries or the databases configuration (e.g. WAL mode)?

Thanks in advance,
Dave.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump ... [was: Please test the latest SQLite snapshot]

[Larry Brasfield]

On 3 Dec 2012, Richard Hipp wrote:

... You can get a tarball or ZIP archive of the latest raw
sources from Fossil at http://www.sqlite.org/src/info/trunk


I notice that in this (3.7.15 pre-release snapshot) version of
the shell the .help out for the .dump command reads:
  .dump ?TABLE? ...  Dump the database in an SQL text format
 If TABLE specified, only dump tables matching
 LIKE pattern TABLE.
This seems to indicate that more than one TABLE argument could be
supplied.  I happen to have a good use for that feature, and when
it did not work, I went to see why and found this code
if( c=='d' && strncmp(azArg[0], "dump", n)==0 && nArg<3 ){
guarding/selecting the .dump implementation.  By commenting out the
last && term, it happily does what I wanted, doing a combined .dump
output for just the tables I wish to recreate later.

Is multiple table dumping suspected to not work for some reason?
If so, the .help output should lose the '...'.  If not, perhaps
the shell.c source should lose that "&& nArg<3" clause.

Thanks,
--
Larry Brasfield

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

Re: [sqlite] Dump a memory DB

[Simon Slavin]

On 31 Oct 2012, at 2:25pm, Andrea Peri  wrote:

> From the sqlite3 console, is possible to dump on disk a memory DB ?

Use the .dump command.

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

Re: [sqlite] Dump a memory DB

[Jonas Malaco Filho]

You can use the .backup command: http://www.sqlite.org/sqlite.html

*Jonas Malaco Filho*



2012/10/31 Andrea Peri 

> Hi,
>
> From the sqlite3 console, is possible to dump on disk a memory DB ?
>
> Thx,
>
> --
> -
> Andrea Peri
> . . . . . . . . .
> qwerty àèìòù
> -
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Dump a memory DB

[Andrea Peri]

Hi,

>From the sqlite3 console, is possible to dump on disk a memory DB ?

Thx,

-- 
-
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump of utf16 database

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Kevin Ryde wrote:
> Or no doubt by querying it out, "sqlite3 old.db 'pragma encoding'".
> But that's something .dump could helpfully do itself, could it?

The SQLite shell can't read your mind :-)  Adding something like this would
require yet another command line flag, more code for when you do want the
encoding preserved and when you don't, testing both paths etc.

The dump currently provides exactly one thing.  If you dump one database
into another then queries against both databases will give back identical
results.

If you want to fine tune (cache sizes, page sizes, encoding etc) then it is
trivial to prefix the dump as appropriate.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAks79aUACgkQmOOfHg372QTy5wCg0682yyw8JNOxlSIdbSnLrI6q
IlcAn0aZFhPzr94VA/KOPgYitjeWOAWo
=vsJR
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump of utf16 database

[Kevin Ryde]

Roger Binns  writes:
>
> The only effect is what happens behind the scenes.

I thought that might be so.

> echo 'pragma encoding="UTF-16";'

Or no doubt by querying it out, "sqlite3 old.db 'pragma encoding'".
But that's something .dump could helpfully do itself, could it?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump of utf16 database

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Kevin Ryde wrote:
> Perhaps it doesn't matter to anything.

The only effect is what happens behind the scenes.  If you primarily use the
- -16 interfaces to bind and retrieve text then the database also being utf16
means you avoid SQLite doing a conversion (assuming the byte orders also
match).  Some SQLite internals like query parsing are UTF8 only anyway (ie
the prepare-16 functions convert to UTF8 and call the UTF8 variants).

There may also be size differences in your database depending on the
distribution of code points in your text data and the size of strings in
proportion to other data and SQLite's metadata.

You can dress your immediate issue by doing this:

(echo 'pragma encoding="UTF-16";' ; sqlite3 old.db .dump ) | sqlite3 new.db

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAks6wFQACgkQmOOfHg372QSioQCbBjDWAb1mSDKW4G3yOD1Igdz0
n2EAn19xGp4/HwJfj2Mgwqt8Sh9gjfCL
=v0qa
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .dump of utf16 database

[Kevin Ryde]

I was trying some .dump round-trips like

sqlite3 old.db .dump | sqlite3 new.db

and noticed if old.db is utf16, ie. pragma encoding=utf16, then new.db
doesn't get that but instead is utf8.  Is that intentional?  Would
slipping a pragma into the .dump output preserve the db coding?

(I don't want the .dump text output to be utf16, and this was only an
experiment to see what came out.  Perhaps it doesn't matter to
anything.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite .dump does not save PRAGMA user_version

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Simon Schubert wrote:
> When creating a dump with sqlite .dump, it will not save the user_version.

The problem is that the usage of the user_version is not known.  It could be
harmless to dump or it could cause problems on a restore.

> PS: please CC me on replies since I'm not subscribed to the mailing list

http://catb.org/~esr/faqs/smart-questions.html#noprivate

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrXVkYACgkQmOOfHg372QQ/+gCfd62kflIPGGwmZQ55sOWs4Eig
OR8AnjE+E7MgpTWabKTpQmC29xrtXHf6
=d+CQ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite .dump does not save PRAGMA user_ver sion

[Simon Schubert]

Hey,

When creating a dump with sqlite .dump, it will not save the user_version.

Firefox is using this user_version to determine the database schema version (or
something related).  When restoring a dump (but without user_version set
properly), firefox will discard all history data.

I'm not sure where the issue is fixed best.  Firefox developers indicate that
they don't consider this their problem.

I'm wondering whether sqlite shouldn't dump the user_version as well so that a
restored database dump will be indistinguishable from the original database.

cheers
  simon

PS: please CC me on replies since I'm not subscribed to the mailing list


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

Re: [sqlite] dump in-memory db to file in tcl

[Alexey Pechnikov]

Hello!

1. Use tcl backup API

The "backup" method 
The "backup" method makes a backup copy of a live database. The command syntax 
is like this: 
dbcmd backup ?source-database? backup-filename 
The optional source-database argument tells which database in the current 
connection should be backed up. The default value is main (or, in other words, 
the primary database file). To back up TEMP tables use temp. To backup an 
auxilary database added to the connection using the ATTACH command, use the 
name of that database as it was assigned in the ATTACH command. 
The backup-filename is the name of a file into which the backup is written. 
Backup-filename does not have to exist ahead of time, but if it does, it must 
be a well-formed SQLite database. 
The "restore" method 
The "restore" method copies the content a separate database file into the 
current database connection, overwriting any preexisting content. The command 
syntax is like this: 
dbcmd restore ?target-database? source-filename 
The optional target-database argument tells which database in the current 
connection should be overwritten with new content. The default value is main 
(or, in other words, the primary database file). To repopulate the TEMP tables 
use temp. To overwrite an auxilary database added to the connection using the 
ATTACH command, use the name of that database as it was assigned in the ATTACH 
command. 
The source-filename is the name of a existing well-formed SQLite database file 
from which the content is extracted.

2. Use sql commands to copy database structure from sqlite_master table and 
copy data

2006-02-20: A simple TCL-Implementation for loading a DB into memory: 
proc loadDB {dbhandle filename} { 
if {$filename != ""} {
#attach persistent DB to target DB
$dbhandle eval "ATTACH DATABASE '$filename' AS loadfrom"
#copy each table to the target DB
foreach {tablename} [$dbhandle eval "SELECT name FROM 
loadfrom.sqlite_master WHERE type = 'table'"] {
$dbhandle eval "CREATE TABLE '$tablename' AS SELECT * FROM 
loadfrom.'$tablename'"
}
#create indizes in loaded table
foreach {sql_exp} [$dbhandle eval "SELECT sql FROM 
loadfrom.sqlite_master WHERE type = 'index'"] {
$dbhandle eval $sql_exp
}
#detach the source DB
$dbhandle eval {DETACH loadfrom}
}
}


Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] dump in-memory db to file in tcl

[Gerry Snyder]

Ned Fleming wrote:
> Is it possible to dump an in-memory sqlite database (or table?) to a
> file from within Tcl?
>
> I create it like so:
>
>   sqlite3 dbFireData :memory:
>
> and insert a bunch of records, and then commit.

Is there reason not to attach a file (old or new), and either

create table realfile.newtablecopy as select * from main.originaltable

or else create the new table with ~ the same create table statement used 
for the original table, and then

insert into realfile.newtablecopy select * from main.originaltable

?  The first method copies all the data, but will drop any special 
thingies from the column definitions.


HTH,


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

Re: [sqlite] dump in-memory db to file in tcl

[Simon Slavin]

On 2 Oct 2009, at 5:20pm, Ned Fleming wrote:

> Is it possible to dump an in-memory sqlite database (or table?) to a
> file from within Tcl?

Not easily.  MySQL has a pseudo-command which does what .dump does:  
returns a long piece of text with all the commands needed to reproduce  
the table.  If I suggest adding this as a PRAGMA people will scream  
'bloat'.  In SQLite it's easy to find the command needed to create the  
TABLE and INDEXes, but what's hard is devising a short way to recreate  
the records.

> I have tried the following (and variations) -- but no go, kokomo.
> ($fileOut3 is a handle to a command-line file name.)
>
>   exec sqlite3 dump dbFireData $fileOut3

 From a shell:

echo '.dump' | sqlite3 mydb.sql > mydb.dump

echo '.dump' | sqlite3 mydb.sql | gzip -c > mydb.dump.gz

I don't know enough about Tcl to know if you can do piping and routing  
in its 'exec' commands.

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

[sqlite] dump in-memory db to file in tcl

[Ned Fleming]

Is it possible to dump an in-memory sqlite database (or table?) to a
file from within Tcl?

I create it like so:

sqlite3 dbFireData :memory:

and insert a bunch of records, and then commit.

I have tried the following (and variations) -- but no go, kokomo.
($fileOut3 is a handle to a command-line file name.)

exec sqlite3 dump dbFireData $fileOut3

Something tells me I'm wildly off-base.

Ned

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

Re: [sqlite] .dump and transactions

[Thomas Jarosch]

On Monday, 12. January 2009 14:20:40 Thomas Jarosch wrote:
> If an open transaction would block the dump of the database,
> then the second command line tool should busy wait until a timeout occurs
> or atleast return an error message.

I finally tracked this down by testing sqlite 3.6.10 on my workstation
and the target system. The target system is Redhat 6.2 based (glibc 2.1.3)
and running kernel 2.6.27.11. The glibc will be upgreaded soon.

A strace run of the ".dump" command showed strange behavior:

write(1, "BEGIN TRANSACTION;\n", 19BEGIN TRANSACTION;
)= 19
brk(0x8055000)  = 0x8055000
fcntl(3, F_SETLK, {type=F_RDLCK, whence=SEEK_SET, start=1073741824, len=0}) = 
-1 EAGAIN (Resource temporarily unavailable)
fcntl(3, F_SETLK, {type=F_RDLCK, whence=SEEK_SET, start=1073741824, len=0}) = 
-1 EAGAIN (Resource temporarily unavailable)
write(1, "COMMIT;\n", 8COMMIT;


It turned out that the "configure" script for 3.6.10 generated by
autoconf 2.59 incorrectly detected large file support on this system.

config.h looked like this:
#define _FILE_OFFSET_BITS 64
/* #undef _LARGE_FILES */

I've rebuilt "configure" with autoconf 2.61 and now it detects this:
/* #undef _FILE_OFFSET_BITS */
/* #undef _LARGE_FILES */

I've now manually disabled large file support and everything works fine.

Cheers,
Thomas

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

Re: [sqlite] .dump and transactions

[Thomas Jarosch]

On Monday, 12. January 2009 13:52:47 P Kishor wrote:
> > Here's a short example to reproduce the problem:
> >
> > sqlite3 test.db
> > create table test (name varchar(16));
> > begin transaction;
> > insert into test values ('test');
>
> did you forget to COMMIT here?

Thanks for your reply. In fact it's the core of the problem: You can't dump a 
database if there is an open, uncommited transaction. This used to work in 
3.5.9 and is needed for creating backups of databases.

If an open transaction would block the dump of the database,
then the second command line tool should busy wait until a timeout occurs
or atleast return an error message.

Thomas

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

Re: [sqlite] .dump and transactions

[P Kishor]

On Mon, Jan 12, 2009 at 4:30 AM, Thomas Jarosch
 wrote:
> On Friday, 9. January 2009 10:34:32 Thomas Jarosch wrote:
>> I run a small script every night via cron to backup a database
>> using the ".dump" statement. SQlite version is 3.6.6.2 on Linux.
>>
>> Normally this script works fine and from time to time
>> I get a backup file that looks like this:
>> ---
>> BEGIN TRANSACTION;
>> END TRANSACTION;
>> ---
>
> Here's a short example to reproduce the problem:
>
> sqlite3 test.db
> create table test (name varchar(16));
> begin transaction;
> insert into test values ('test');
>

did you forget to COMMIT here?

> Now open a second shell and do this:
>
> sqlite3 test.db
> .dump
>
> This will output:
> ---
> BEGIN TRANSACTION;
> END TRANSACTION;
> ---
>
> I tested sqlite 3.5.9 on my workstation, it correctly
> dumps the table even if a transaction is open,
> only 3.6.6.2 shows this behavior so far.
>
> Is this supposed to be?
>
> Cheers,
> Thomas
>




-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump and transactions

[Thomas Jarosch]

On Friday, 9. January 2009 10:34:32 Thomas Jarosch wrote:
> I run a small script every night via cron to backup a database
> using the ".dump" statement. SQlite version is 3.6.6.2 on Linux.
>
> Normally this script works fine and from time to time
> I get a backup file that looks like this:
> ---
> BEGIN TRANSACTION;
> END TRANSACTION;
> ---

Here's a short example to reproduce the problem:

sqlite3 test.db
create table test (name varchar(16));
begin transaction;
insert into test values ('test');

Now open a second shell and do this:

sqlite3 test.db
.dump

This will output:
---
BEGIN TRANSACTION;
END TRANSACTION;
---

I tested sqlite 3.5.9 on my workstation, it correctly
dumps the table even if a transaction is open,
only 3.6.6.2 shows this behavior so far.

Is this supposed to be?

Cheers,
Thomas

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

[sqlite] .dump and transactions

[Thomas Jarosch]

Hello together,

I run a small script every night via cron to backup a database
using the ".dump" statement. SQlite version is 3.6.6.2 on Linux.

Normally this script works fine and from time to time
I get a backup file that looks like this:
---
BEGIN TRANSACTION;
END TRANSACTION;
---

Consider you have a database with a simple table.
Acccess the database using the command line "sqlite3" tool
and do
-
BEGIN TRANSACTION;
INSERT INTO simple_table (xyz) ...
-

Now open another, concurring instance of the sqlite3 command line tool
and issue a ".dump" command. It will then produce the empty
transaction output mentioned first.

Shouldn't the command line tool wait for a locking timeout
or atleast return BUSY when it can't dump the database?

Is there a better way to backup the database?

Thanks in advance,
Thomas

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

Re: [sqlite] .dump often fails silently

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Kees Nuyt wrote:
> Did you try 
>   .bail on
 > It won't change the destination of error messages, but at
> least it aborts the sql script at the first error it
> encounters.

That is not true of errors returned by sqlite3_exec when doing .dump.
Those errors are somewhat ignored.  It does affect SQL queries you
supply that do error.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkkWI6wACgkQmOOfHg372QTNwgCgh971E3H/8KQYfFOO36UTaFcU
QKoAoKRai4Xd4NB2bJKffTX43FT/tmvP
=nHPv
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump often fails silently

[Kees Nuyt]

On Sat, 8 Nov 2008 11:44:23 +1100, raf <[EMAIL PROTECTED]>
wrote in General Discussion of SQLite Database
:

>but that's my point.
>i'm not getting any errors.
>it's just failing silently.

Did you try 
.bail on
?

It won't change the destination of error messages, but at
least it aborts the sql script at the first error it
encounters.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump often fails silently

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

raf wrote:
>> printing error messages to stderr rather than ignoring
>> them shoulden't require an overhaul.

It does though.  You need to fix every place where an error can be
detected and take an appropriate action.  One extra printf is not the
entire solution.

>>> If you are only getting busy errors
>>> then try the .timeout command with a nice large value such as 15000.
> 
>> but that's my point.
>> i'm not getting any errors.
>> it's just failing silently.

Ok, if the shell internally is getting busy errors (and doing nothing
about it) then setting the timeout should help.  I suspect that setting
the timeout will make your problem go away, especially if it has a large
value.

>>> You may want to consider writing your own dump command that works
>>> exactly the way you want.  If you are generating textual output then you
>>> basically just call sqlite3_exec as appropriate.
> 
>> that seems like a waste of effort when there is already
>> a dump command in sqlite3. 

The point is that you can format the output exactly how you want
(quoting rules, handling of nulls) and error handling.  You can also
make the abort handle the way you want should some data already be
printed.  For example a non-zero exit may be sufficient, or you may want
to flush the output, emit a rollback command, and then have a non-zero exit.

>> i would much rather add some
>> print statements to sqlite3 itself so that others could
>> benefit as well.

That is fine too :-)

>> would a patch to do this be likely to be accepted?

DRH would need to chime in on that.  Generally patches are accepted
provided they don't break existing code (ie other people using SQLite),
benefit the majority of users and don't have a high cost (memory, CPU,
code maintenance etc).

You will also need to follow "Contributed Code" at
http://www.sqlite.org/copyright.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkkVKOYACgkQmOOfHg372QTWFgCfSaABW/A9YE9jTijF016xs4Yq
FAkAoJDEalsAaKQFnDu7RX8YCtrpWjC2
=z/8f
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump often fails silently

[raf]

Roger Binns wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> raf wrote:
> > the sqlite3 .dump command (version 3.4.2 and earlier) often fails
> > silently. i.e. it produces a file containing nothing but:
> 
> The code for the shell mostly just runs sqlite3_exec and often ignores
> the return code (unless it is SQLITE_CORRUPT in which case the query is
> rerun with "ORDER BY rowid DESC" appended).
> 
> > however, i can't tell what it means because there are no error
> > messages and no error exit code to tell me what went wrong.
> 
> The shell would need a bit of an overhaul to go from the useful utility
> it is intended to be to rock solid.

printing error messages to stderr rather than ignoring
them shoulden't require an overhaul.

> If you are only getting busy errors
> then try the .timeout command with a nice large value such as 15000.

but that's my point.
i'm not getting any errors.
it's just failing silently.

> > p.s. it would be nice if the .output command worked from the command
> > line (in conjunction with .dump) and not just from the interactive prompt.
> 
> You may want to consider writing your own dump command that works
> exactly the way you want.  If you are generating textual output then you
> basically just call sqlite3_exec as appropriate.

that seems like a waste of effort when there is already
a dump command in sqlite3. i would much rather add some
print statements to sqlite3 itself so that others could
benefit as well.

would a patch to do this be likely to be accepted?

> Here is another similar problem:
> 
>   http://www.sqlite.org/cvstrac/tktview?tn=3450

ooh yes, that's naughty. and it's something i couldn't
catch by looking for suspiciously small file sizes.
it might have almost completed by time that the error occurred.

> Roger

cheers,
raf

> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> 
> iEYEARECAAYFAkkTsycACgkQmOOfHg372QRbgwCgmb+WbEUtzOs+Xca58vqbDQ4H
> NBsAni30SP2xSIJ+M2e7Q8ZcpXcb8DsJ
> =0CZc
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump often fails silently

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

raf wrote:
> the sqlite3 .dump command (version 3.4.2 and earlier) often fails
> silently. i.e. it produces a file containing nothing but:

The code for the shell mostly just runs sqlite3_exec and often ignores
the return code (unless it is SQLITE_CORRUPT in which case the query is
rerun with "ORDER BY rowid DESC" appended).

> however, i can't tell what it means because there are no error
> messages and no error exit code to tell me what went wrong.

The shell would need a bit of an overhaul to go from the useful utility
it is intended to be to rock solid.  If you are only getting busy errors
then try the .timeout command with a nice large value such as 15000.

> p.s. it would be nice if the .output command worked from the command
> line (in conjunction with .dump) and not just from the interactive prompt.

You may want to consider writing your own dump command that works
exactly the way you want.  If you are generating textual output then you
basically just call sqlite3_exec as appropriate.

Here is another similar problem:

  http://www.sqlite.org/cvstrac/tktview?tn=3450

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkkTsycACgkQmOOfHg372QRbgwCgmb+WbEUtzOs+Xca58vqbDQ4H
NBsAni30SP2xSIJ+M2e7Q8ZcpXcb8DsJ
=0CZc
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .dump often fails silently

[raf]

hi,

the sqlite3 .dump command (version 3.4.2 and earlier) often fails
silently. i.e. it produces a file containing nothing but:

  BEGIN TRANSACTION;
  COMMIT;

and its exit code is zero (falsely indicating success).

my backup script has been misinterpreting this as evidence of database
corruption (and restoring the previous backup and replaying any lost
inserts or updates since the previous backup).

however, i suspect that it might just be a sign of a locked database
because it works properly if i wait a second a try again.

however, i can't tell what it means because there are no error
messages and no error exit code to tell me what went wrong.

it happens quite often. my backup/restore script runs every 15 minutes
and this happens 1-2 times a day on average.

does anyone know what this does mean?
or if it's any better with recent versions?

cheers,
raf

p.s. it would be nice if the .output command worked from the command
line (in conjunction with .dump) and not just from the interactive prompt.

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

Re: [sqlite] Dump w/o Schema

[Mark Riehl]

Is there a way to merge these in such a way that I could make this
from a system call?  For example, let's say I want to create this dump
file from a Perl or shell script using a system call.  Can I do that?

Thanks,
Mark

On Jan 15, 2008 4:59 PM, Rich Shepard <[EMAIL PROTECTED]> wrote:
> On Tue, 15 Jan 2008, Rob Sciuk wrote:
>
> >> Is there a way to dump out the contents of the database w/o having the
> >> CREATE TABLE statements?  For example, I can do the following:
>
> >> However, foo.dmp contains all of the CREATE TABLE statements.  I just
> >> want all of the INSERT INTO statements associated with this database.
>
> > Actually, this looks like a reasonable enhancement request.  As it does 
> > *NOT*
> > affect the API in any way, and PostGres allows separate schema and data 
> > dumps
> > (the default being both) in psql, there should be no reason not to put it
> > into the sqlite3 tool, unless I'm missing something??  Am I?
>
>Not needed; it already exists.
>
>Change the mode to 'insert', select a file name for your output, then run
> the select statement. For example:
>
> sqlite> .m insert
> sqlite> .o table-values.sql
> sqlite> Select * from Tablename;
> sqlite> .o stdout
> sqlite> .m list
>
>The last two commands reset the output and mode back to the interactive
> values.
>
> Rich
>
> --
> Richard B. Shepard, Ph.D.   |  IntegrityCredibility
> Applied Ecosystem Services, Inc.|Innovation
>  Voice: 503-667-4517  Fax: 503-667-8863
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dump w/o Schema

[Rich Shepard]

On Tue, 15 Jan 2008, Rob Sciuk wrote:


Is there a way to dump out the contents of the database w/o having the
CREATE TABLE statements?  For example, I can do the following:



However, foo.dmp contains all of the CREATE TABLE statements.  I just
want all of the INSERT INTO statements associated with this database.


Actually, this looks like a reasonable enhancement request.  As it does *NOT* 
affect the API in any way, and PostGres allows separate schema and data dumps 
(the default being both) in psql, there should be no reason not to put it 
into the sqlite3 tool, unless I'm missing something??  Am I?


  Not needed; it already exists.

  Change the mode to 'insert', select a file name for your output, then run
the select statement. For example:

sqlite> .m insert
sqlite> .o table-values.sql
sqlite> Select * from Tablename;
sqlite> .o stdout
sqlite> .m list

  The last two commands reset the output and mode back to the interactive
values.

Rich

--
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dump w/o Schema

[P Kishor]

On 1/15/08, Mark Riehl <[EMAIL PROTECTED]> wrote:
> I'm coming from a MySQL background and the mysqldump utility supports
> schema-only,

sqlite> .mode filename
sqlite> .s

> data-only,

sqlite> .mode filename
sqlite> .mode csv|tabs
sqlite> .dump

> and schema plus data dumps.

sqlite> .mode filename
sqlite> .mode insert
sqlite> .dump

> I thought that
> there was an SQLite trick I was missing somewhere.
>
> Mark
>
> On Jan 15, 2008 4:10 PM, Rob Sciuk <[EMAIL PROTECTED]> wrote:
> >
> >
> > On Tue, 15 Jan 2008, Mark Riehl wrote:
> >
> > > Is there a way to dump out the contents of the database w/o having the
> > > CREATE TABLE statements?  For example, I can do the following:
> > >
> > > sqlite3 foo.db .dump > foo.dmp
> > >
> > > However, foo.dmp contains all of the CREATE TABLE statements.  I just
> > > want all of the INSERT INTO statements associated with this database.
> > >
> > > I looked through the documentation but didn't find the answer to this
> > > one, I apologize if it's already in the docs.
> > >
> > > Thanks,
> > > Mark
> >
> > Actually, this looks like a reasonable enhancement request.  As it does
> > *NOT* affect the API in any way, and PostGres allows separate schema and
> > data dumps (the default being both) in psql, there should be no reason not
> > to put it into the sqlite3 tool, unless I'm missing something??  Am I?
> >
> > Cheers,
> > Rob Sciuk
> >
> >

-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dump w/o Schema

[Mark Riehl]

I'm coming from a MySQL background and the mysqldump utility supports
schema-only, data-only, and schema plus data dumps.  I thought that
there was an SQLite trick I was missing somewhere.

Mark

On Jan 15, 2008 4:10 PM, Rob Sciuk <[EMAIL PROTECTED]> wrote:
>
>
> On Tue, 15 Jan 2008, Mark Riehl wrote:
>
> > Is there a way to dump out the contents of the database w/o having the
> > CREATE TABLE statements?  For example, I can do the following:
> >
> > sqlite3 foo.db .dump > foo.dmp
> >
> > However, foo.dmp contains all of the CREATE TABLE statements.  I just
> > want all of the INSERT INTO statements associated with this database.
> >
> > I looked through the documentation but didn't find the answer to this
> > one, I apologize if it's already in the docs.
> >
> > Thanks,
> > Mark
>
> Actually, this looks like a reasonable enhancement request.  As it does
> *NOT* affect the API in any way, and PostGres allows separate schema and
> data dumps (the default being both) in psql, there should be no reason not
> to put it into the sqlite3 tool, unless I'm missing something??  Am I?
>
> Cheers,
> Rob Sciuk
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dump w/o Schema

[Rob Sciuk]

On Tue, 15 Jan 2008, Mark Riehl wrote:


Is there a way to dump out the contents of the database w/o having the
CREATE TABLE statements?  For example, I can do the following:

sqlite3 foo.db .dump > foo.dmp

However, foo.dmp contains all of the CREATE TABLE statements.  I just
want all of the INSERT INTO statements associated with this database.

I looked through the documentation but didn't find the answer to this
one, I apologize if it's already in the docs.

Thanks,
Mark


Actually, this looks like a reasonable enhancement request.  As it does 
*NOT* affect the API in any way, and PostGres allows separate schema and 
data dumps (the default being both) in psql, there should be no reason not 
to put it into the sqlite3 tool, unless I'm missing something??  Am I?


Cheers,
Rob Sciuk

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dump w/o Schema

[Richard Klein]

Mark Riehl wrote:

Is there a way to dump out the contents of the database w/o having the
CREATE TABLE statements?  For example, I can do the following:

sqlite3 foo.db .dump > foo.dmp

However, foo.dmp contains all of the CREATE TABLE statements.  I just
want all of the INSERT INTO statements associated with this database.

I looked through the documentation but didn't find the answer to this
one, I apologize if it's already in the docs.

Thanks,
Mark



No, there is no way to do this in sqlite3.  The .dump command is
intended to produce a complete set of SQL statements that, when
executed, will recreate the database being dumped.

Can't you just remove the CREATE TABLE statements manually, or
pipe the output of .dump to sed or awk?

Regards,
- Richard Klein


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dump w/o Schema

[Mike McGonagle]

On Jan 15, 2008 2:37 PM, Mark Riehl <[EMAIL PROTECTED]> wrote:

> Is there a way to dump out the contents of the database w/o having the
> CREATE TABLE statements?  For example, I can do the following:
>
> sqlite3 foo.db .dump > foo.dmp
>

You could do something like on Unix:

sqlite3 foo.db .dump > fgrep INSERT > foo.dmp


Mike


-- 
Peace may sound simple—one beautiful word— but it requires everything we
have, every quality, every strength, every dream, every high ideal.
—Yehudi Menuhin (1916–1999), musician

[sqlite] Dump w/o Schema

[Mark Riehl]

Is there a way to dump out the contents of the database w/o having the
CREATE TABLE statements?  For example, I can do the following:

sqlite3 foo.db .dump > foo.dmp

However, foo.dmp contains all of the CREATE TABLE statements.  I just
want all of the INSERT INTO statements associated with this database.

I looked through the documentation but didn't find the answer to this
one, I apologize if it's already in the docs.

Thanks,
Mark

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> I think those exceedingly rare programs that need a larger
> SQL statement length limit can include their own copy of
> sqlite3.c.  I does not take up that much space, after all.

It's easy enough to recompile with the new setting once you're 
aware of it. But shouldn't the default limits be unlimited as 
they were before 3.4.x?  

The new limit behavior broke backwards compatibility and could 
surprise a few people, as in the link I cited.



  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> --- [EMAIL PROTECTED] wrote:
> > Yes, this does create problems for .dump/.load in the shell.
> > But, as has been pointed out, you can work around it using
> > a compile-time switch:
> > 
> > gcc -DSQLITE_MAX_SQL_LENGTH=10 shell.c sqlite3.c -o sqlite3
> > 
> > I should probably modify the makefile to do this automatically...
> 
> It would be useful if this value could be set at runtime via PRAGMA or 
> a function like sqlite3_set_max_sql_length().
> 
> Why? An operating system (say, Mac OSX) may have only one shared 
> libsqlite3.so and many programs using that shared library. It would be 
> beneficial if they could all use the same shared library even though they 
> require a different maximum SQL length.
> 

I think those exceedingly rare programs that need a larger
SQL statement length limit can include their own copy of
sqlite3.c.  I does not take up that much space, after all.

Any ideas what (if any) limits there are on the length of
SQL statements in MySQL, PostgreSQL, and Oracle?

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

[Jim Correia]

On Dec 11, 2007, at 2:10 PM, [EMAIL PROTECTED] wrote:


The limits in SQLite (introduced in version 3.4.0) were added
at the request of the Google Gears developers.  Consider the
situation that Gears and similar applications (such as Adobe AIR)
are in.  They have to accept generic SQL from untrusted sources
on the open internet then run that SQL in a secure manner.  It
turns out that there were all kinds of attacks against SQLite
if you feed it untrusted SQL.  The introduction of hard
limits on the size of strings and BLOBs and SQL statements is
one part of our efforts to close holes in SQLite and make it
proof against malicious SQL attacks.  (We are not there yet,
BTW, but we are much closer.)


Also, per the quoted source in my original message, the comment is  
out of sync with the source.


/*
** The maximum length of a single SQL statement in bytes.
** The hard limit here is the same as SQLITE_MAX_LENGTH.
*/
#ifndef SQLITE_MAX_SQL_LENGTH
# define SQLITE_MAX_SQL_LENGTH 100
#endif

This inconsistency was a source of confusion for me as to whether the  
current hard limit for SQL was intentionally 1MB or not.


I'd be happy to submit a patch to fix the comment, but it is the  
proverbial one line fix.


Jim

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> Yes, this does create problems for .dump/.load in the shell.
> But, as has been pointed out, you can work around it using
> a compile-time switch:
> 
> gcc -DSQLITE_MAX_SQL_LENGTH=10 shell.c sqlite3.c -o sqlite3
> 
> I should probably modify the makefile to do this automatically...

It would be useful if this value could be set at runtime via PRAGMA or 
a function like sqlite3_set_max_sql_length().

Why? An operating system (say, Mac OSX) may have only one shared 
libsqlite3.so and many programs using that shared library. It would be 
beneficial if they could all use the same shared library even though they 
require a different maximum SQL length.

Perhaps other defaults could be similarly set at runtime as well.


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

[Jim Correia]

On Dec 11, 2007, at 2:10 PM, [EMAIL PROTECTED] wrote:


You should normally not be inserting megabyte-sized blobs and
strings using raw SQL.  Instead, use bound parameters:

sqlite3_prepare("INSERT INTO tablexyz VALUES(:blobcontent)");
sqlite3_bind_blob(pStmt, 1, pBlobContent, SQLITE_STATIC);
sqlite3_step(pStmt);


Indeed. That's what I did to work around the problem - wrote a tool  
that essentially did a dump/load using bound parameters.



Yes, this does create problems for .dump/.load in the shell.
But, as has been pointed out, you can work around it using
a compile-time switch:

gcc -DSQLITE_MAX_SQL_LENGTH=10 shell.c sqlite3.c -o  
sqlite3


I should probably modify the makefile to do this automatically...


That would be great. That would increase the likelihood that the  
sqlite3 shell that shipped in binary form in OS distributions had a  
functional .dump/.load for large rows.


Thanks,
Jim


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

[drh]

Jim Correia <[EMAIL PROTECTED]> wrote:
> 
> Is a 1MB limit on the SQL intentional?
> 
> Per my previous message, the comment in the source disagrees with the  
> value.
> 
> Also, at the default value, .dump/.load will only support rows of  
> about 1/2 MB (to account for hex expansion), while the default limit  
> for BLOB columns is 1GB.
> 
> In other words, independent of the solution to my current problem,  
> should the default value be changed in the trunk version of SQLite?
> 

The limits in SQLite (introduced in version 3.4.0) were added
at the request of the Google Gears developers.  Consider the
situation that Gears and similar applications (such as Adobe AIR)
are in.  They have to accept generic SQL from untrusted sources
on the open internet then run that SQL in a secure manner.  It
turns out that there were all kinds of attacks against SQLite
if you feed it untrusted SQL.  The introduction of hard
limits on the size of strings and BLOBs and SQL statements is
one part of our efforts to close holes in SQLite and make it
proof against malicious SQL attacks.  (We are not there yet,
BTW, but we are much closer.)

There are places in the SQLite parser and code generator which
are O(N*N) where N is the size of the SQLite statement in tokens.
By setting the SQL statement size limit to 1MB we reduce the
opportunity to lauch a denial of service attack by injecting
SQL that takes advantage of O(N*N) performance to bring your
desktop to its knees.  

You should normally not be inserting megabyte-sized blobs and
strings using raw SQL.  Instead, use bound parameters:

sqlite3_prepare("INSERT INTO tablexyz VALUES(:blobcontent)");
sqlite3_bind_blob(pStmt, 1, pBlobContent, SQLITE_STATIC);
sqlite3_step(pStmt);

Yes, this does create problems for .dump/.load in the shell.
But, as has been pointed out, you can work around it using
a compile-time switch:

gcc -DSQLITE_MAX_SQL_LENGTH=10 shell.c sqlite3.c -o sqlite3

I should probably modify the makefile to do this automatically...
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

[Jim Correia]

On Dec 11, 2007, at 11:03 AM, Joe Wilson wrote:


If this is intentional, what is the recommended replacement
for .dump/.load for large rows?


You have to recompile with a large value for SQLITE_MAX_SQL_LENGTH
via a compiler -D flag or other means.

Monotone encountered this issue as well for dumping/restoring  
databases

with large BLOBs:

 http://lists.gnu.org/archive/html/monotone-devel/2007-09/ 
msg00246.html


I think the default value is too small, but as long as you're able to
compile/use your own library, it's not too much trouble.


Joe,

Thanks for the response and sorry for not being 100% clear in my  
initial inquiry.


I do realize that one possible solution is to recompile sqlite (even  
if it isn't practical for my current problem.)


What I was really asking is this:

Is a 1MB limit on the SQL intentional?

Per my previous message, the comment in the source disagrees with the  
value.


Also, at the default value, .dump/.load will only support rows of  
about 1/2 MB (to account for hex expansion), while the default limit  
for BLOB columns is 1GB.


In other words, independent of the solution to my current problem,  
should the default value be changed in the trunk version of SQLite?


Jim


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

[Joe Wilson]

--- Jim Correia <[EMAIL PROTECTED]> wrote:
> I notice that SQLite 3.4.0 and later impose hard limits on some  
> sizes. I'm running into a problem where a .dump/.load cycle fails on  
> a database with columns that have blobs which are about 2MB in size.
> 
> Looking at the source for 3.5.3 (I can't find a tarball of 3.4 on the  
> web site, but I'm using 3.4 since that is what ships on Mac OS X 10.5)
> 
> I see:
> 
>   /*
>   ** The maximum length of a TEXT or BLOB in bytes.   This also
>   ** limits the size of a row in a table or index.
>   **
>   ** The hard limit is the ability of a 32-bit signed integer
>   ** to count the size: 2^31-1 or 2147483647.
>   */
>   #ifndef SQLITE_MAX_LENGTH
>   # define SQLITE_MAX_LENGTH 10
>   #endif
> 
> and more importantly:
> 
>   /*
>   ** The maximum length of a single SQL statement in bytes.
>   ** The hard limit here is the same as SQLITE_MAX_LENGTH.
>   */
>   #ifndef SQLITE_MAX_SQL_LENGTH
>   # define SQLITE_MAX_SQL_LENGTH 100
>   #endif
> 
> Is the comment wrong, or the source? The value is not the same as  
> SQLITE_MAX_LENGTH; it is in fact much smaller.
> 
> If this is intentional, what is the recommended replacement  
> for .dump/.load for large rows?

You have to recompile with a large value for SQLITE_MAX_SQL_LENGTH
via a compiler -D flag or other means.

Monotone encountered this issue as well for dumping/restoring databases 
with large BLOBs:

 http://lists.gnu.org/archive/html/monotone-devel/2007-09/msg00246.html

I think the default value is too small, but as long as you're able to
compile/use your own library, it's not too much trouble. 





  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

[Jim Correia]

I notice that SQLite 3.4.0 and later impose hard limits on some  
sizes. I'm running into a problem where a .dump/.load cycle fails on  
a database with columns that have blobs which are about 2MB in size.


Looking at the source for 3.5.3 (I can't find a tarball of 3.4 on the  
web site, but I'm using 3.4 since that is what ships on Mac OS X 10.5)


I see:

/*
** The maximum length of a TEXT or BLOB in bytes.   This also
** limits the size of a row in a table or index.
**
** The hard limit is the ability of a 32-bit signed integer
** to count the size: 2^31-1 or 2147483647.
*/
#ifndef SQLITE_MAX_LENGTH
# define SQLITE_MAX_LENGTH 10
#endif

and more importantly:

/*
** The maximum length of a single SQL statement in bytes.
** The hard limit here is the same as SQLITE_MAX_LENGTH.
*/
#ifndef SQLITE_MAX_SQL_LENGTH
# define SQLITE_MAX_SQL_LENGTH 100
#endif

Is the comment wrong, or the source? The value is not the same as  
SQLITE_MAX_LENGTH; it is in fact much smaller.


If this is intentional, what is the recommended replacement  
for .dump/.load for large rows?


Thanks,
Jim

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] .dump returns error 32512... sometimes

[Jevgenijs Rogovs]

Hi everyone,

I have a script that does some backup. Amongst other things, it dumps
certain SQLite database, like sqlite3 /path/to/db/dname.db .dump > somefile.
When I run the script manually from the command line, it works like a charm.
If, however, I launch it via crontab - it fails. sqlite3 returns error code
32512, which I cannot decypher... Nor can I understand what happens with the
script when it is scheduled to run automatically.

Can anyone advise please?

I'm running SQLite 3.5.2 on Linux.

Thanks in advance!

With best regards,
J.R.

Re: [sqlite] 'dump' from the C API

[John Stanton]

Does anyone know how to do a database dump from the C API? I'm linking 
to sqlite3.o (the amalgamated file) and was hoping to open a database 
and dump the contents to an sql file.


I know this can be done from the shell, but as of yet, I can't get the 
shell running on AIX.


What version of AIX are you running?  Are you using xlC or gcc?

On some versions of AIX you need to compile Sqlite without debug (no -g) 
when using gcc.


You need to have readline installed to make a nice to use sqlite3 shell 
program.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] 'dump' from the C API

[Joe Wilson]

--- Keith Godfrey <[EMAIL PROTECTED]> wrote:
> Does anyone know how to do a database dump from the C API? I'm linking 
> to sqlite3.o (the amalgamated file) and was hoping to open a database 
> and dump the contents to an sql file.
>
> I know this can be done from the shell, but as of yet, I can't get the 
> shell running on AIX.

Can you compress the sqlite db file and ftp it to linux for further 
processing?

Or if you have java on the AIX machine you could run this pure java 
sqlite3 shell:

http://sqlite.org/contrib/download/sqlite-java-shell-3.4.0.zip?get=19

java -jar sqlite3i.jar -batch your.db .dump


  

Tonight's top picks. What will you watch tonight? Preview the hottest shows on 
Yahoo! TV.
http://tv.yahoo.com/ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] 'dump' from the C API

[Keith Godfrey]

Hello,
Does anyone know how to do a database dump from the C API? I'm linking 
to sqlite3.o (the amalgamated file) and was hoping to open a database 
and dump the contents to an sql file.


I know this can be done from the shell, but as of yet, I can't get the 
shell running on AIX.


Thanks in advance,
Keith



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Dump with where clause

[Andre du Plessis]

But then I have to create an actual table in the database?

I suppose I can do the following:
BEGIN;
create table image_temp as select * from file_folder_data;
.dump image_temp
Rollback;

That seems to work as expected

Just another question though, how can I script this and run it from command 
line, using sqlite3.exe ?

Thanks.

-Original Message-
From: Simon Davies [mailto:[EMAIL PROTECTED] 
Sent: 17 August 2007 01:41 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Dump with where clause

Updating Igor's suggestion, just remove 'temp' from the line
create temp table image_temp as select * from file_folder_data;

For me .dump then works as expected.
Rgds,
Simon

On 17/08/07, Andre du Plessis <[EMAIL PROTECTED]> wrote:
> Hi Dennis this seems like a good idea, but there is a problem:
>
> I use the following statement:
>
> .mode insert
> select * from FILE_FOLDER_DATA;
>
>
> This is a snippet of what I get
> INSERT INTO table VALUES(1285,4323,2,'╪α','Thumb','JPEG');
> INSERT INTO table VALUES(1286,4324,2,'╪α','Thumb','JPEG');
> INSERT INTO table VALUES(1287,4325,2,'╪α','Thumb','JPEG');
> INSERT INTO table VALUES(1288,4326,2,'╪α','Thumb','JPEG');
>
> As you can see first of all the insert statement inserts into 'table' which 
> is obviously not the right name,
>
> Secondly the image data should be a HEX string as is what .dump does.
>
> .schema file_folder_data
>
> CREATE TABLE FILE_FOLDER_DATA (ID INTEGER PRIMARY KEY AUTOINCREMENT, 
> FOLDER_ID I
> NTEGER, FOLDER_TYPE INTEGER,
>  CONTENT BLOB, CONTENT_NAME TEXT, CONTENT_EXT TEXT);
> CREATE INDEX IDX_FILE_FOLDER_DATA ON FILE_FOLDER_DATA(FOLDER_ID, FOLDER_TYPE 
> ASC
> );
>
>
> Also the .dump with temp table as Igor suggested does not work.
>
> This works:
> .dump file_folder_data
>
> This does NOT
> create temp table image_temp as select * from file_folder_data;
> .dump image_temp
>
> All I get is:
>
> BEGIN TRANSACTION;
> COMMIT;
>
>
>
> -Original Message-
> From: Dennis Cote [mailto:[EMAIL PROTECTED]
> Sent: 16 August 2007 05:49 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Dump with where clause
>
> Andre du Plessis wrote:
> > HI, how can I use .dump or something similar but specify a where clause,
> > I cant see that the .dump command allows this,
> >
> > Without any arguments it seems to dump the whole db, the only argument
> > supported is the table name,
> >
> >
> >
> > I would like to be able to do something like:
> >
> > .dump table1 where ID > 1000
> >
> >
> >
> > I don't have a problem with the INSERT into statements, in fact I think
> > I prefer it because the main idea is to extract parts of the db
> > (revisions),
> >
> > And then to be able to rebuild the db in case of corruption...
> >
> >
> >
> > I know there is also the COPY command in SQL I have not really tried it
> > by the documentation it seems to be able to dump the table in comma or
> > tab delimited, but Preferably I don't want to write too much code to do
> > this.
> >
> >
> >
> >
> Andre,
>
> You can use the insert mode in the shell to do what you want. It will
> format the select output as insert statements.
>
>.mode insert
>select * from table1 where ID > 1000;
>
> This doesn't generate the transaction wrapper, or the table's create
> statement, but you can add those yourself if needed.
>
> HTH
> Dennis Cote
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

Re: [sqlite] Dump with where clause

[Simon Davies]

Updating Igor's suggestion, just remove 'temp' from the line
create temp table image_temp as select * from file_folder_data;

For me .dump then works as expected.
Rgds,
Simon

On 17/08/07, Andre du Plessis <[EMAIL PROTECTED]> wrote:
> Hi Dennis this seems like a good idea, but there is a problem:
>
> I use the following statement:
>
> .mode insert
> select * from FILE_FOLDER_DATA;
>
>
> This is a snippet of what I get
> INSERT INTO table VALUES(1285,4323,2,'�nα','Thumb','JPEG');
> INSERT INTO table VALUES(1286,4324,2,'�nα','Thumb','JPEG');
> INSERT INTO table VALUES(1287,4325,2,'�nα','Thumb','JPEG');
> INSERT INTO table VALUES(1288,4326,2,'�nα','Thumb','JPEG');
>
> As you can see first of all the insert statement inserts into 'table' which 
> is obviously not the right name,
>
> Secondly the image data should be a HEX string as is what .dump does.
>
> .schema file_folder_data
>
> CREATE TABLE FILE_FOLDER_DATA (ID INTEGER PRIMARY KEY AUTOINCREMENT, 
> FOLDER_ID I
> NTEGER, FOLDER_TYPE INTEGER,
>  CONTENT BLOB, CONTENT_NAME TEXT, CONTENT_EXT TEXT);
> CREATE INDEX IDX_FILE_FOLDER_DATA ON FILE_FOLDER_DATA(FOLDER_ID, FOLDER_TYPE 
> ASC
> );
>
>
> Also the .dump with temp table as Igor suggested does not work.
>
> This works:
> .dump file_folder_data
>
> This does NOT
> create temp table image_temp as select * from file_folder_data;
> .dump image_temp
>
> All I get is:
>
> BEGIN TRANSACTION;
> COMMIT;
>
>
>
> -Original Message-
> From: Dennis Cote [mailto:[EMAIL PROTECTED]
> Sent: 16 August 2007 05:49 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Dump with where clause
>
> Andre du Plessis wrote:
> > HI, how can I use .dump or something similar but specify a where clause,
> > I cant see that the .dump command allows this,
> >
> > Without any arguments it seems to dump the whole db, the only argument
> > supported is the table name,
> >
> >
> >
> > I would like to be able to do something like:
> >
> > .dump table1 where ID > 1000
> >
> >
> >
> > I don't have a problem with the INSERT into statements, in fact I think
> > I prefer it because the main idea is to extract parts of the db
> > (revisions),
> >
> > And then to be able to rebuild the db in case of corruption...
> >
> >
> >
> > I know there is also the COPY command in SQL I have not really tried it
> > by the documentation it seems to be able to dump the table in comma or
> > tab delimited, but Preferably I don't want to write too much code to do
> > this.
> >
> >
> >
> >
> Andre,
>
> You can use the insert mode in the shell to do what you want. It will
> format the select output as insert statements.
>
>.mode insert
>select * from table1 where ID > 1000;
>
> This doesn't generate the transaction wrapper, or the table's create
> statement, but you can add those yourself if needed.
>
> HTH
> Dennis Cote
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

RE: [sqlite] Dump with where clause

[Andre du Plessis]

Hi Dennis this seems like a good idea, but there is a problem:

I use the following statement:

.mode insert
select * from FILE_FOLDER_DATA;


This is a snippet of what I get
INSERT INTO table VALUES(1285,4323,2,' ╪ α','Thumb','JPEG');
INSERT INTO table VALUES(1286,4324,2,' ╪ α','Thumb','JPEG');
INSERT INTO table VALUES(1287,4325,2,' ╪ α','Thumb','JPEG');
INSERT INTO table VALUES(1288,4326,2,' ╪ α','Thumb','JPEG');

As you can see first of all the insert statement inserts into 'table' which is 
obviously not the right name,

Secondly the image data should be a HEX string as is what .dump does.

.schema file_folder_data

CREATE TABLE FILE_FOLDER_DATA (ID INTEGER PRIMARY KEY AUTOINCREMENT, FOLDER_ID I
NTEGER, FOLDER_TYPE INTEGER,
  CONTENT BLOB, CONTENT_NAME TEXT, CONTENT_EXT TEXT);
CREATE INDEX IDX_FILE_FOLDER_DATA ON FILE_FOLDER_DATA(FOLDER_ID, FOLDER_TYPE ASC
);


Also the .dump with temp table as Igor suggested does not work.

This works:
.dump file_folder_data 

This does NOT
create temp table image_temp as select * from file_folder_data;
.dump image_temp

All I get is:

BEGIN TRANSACTION;
COMMIT;



-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 16 August 2007 05:49 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Dump with where clause

Andre du Plessis wrote:
> HI, how can I use .dump or something similar but specify a where clause,
> I cant see that the .dump command allows this,
>
> Without any arguments it seems to dump the whole db, the only argument
> supported is the table name,
>
>  
>
> I would like to be able to do something like:
>
> .dump table1 where ID > 1000
>
>  
>
> I don't have a problem with the INSERT into statements, in fact I think
> I prefer it because the main idea is to extract parts of the db
> (revisions),
>
> And then to be able to rebuild the db in case of corruption...
>
>  
>
> I know there is also the COPY command in SQL I have not really tried it
> by the documentation it seems to be able to dump the table in comma or
> tab delimited, but Preferably I don't want to write too much code to do
> this.
>
>  
>
>   
Andre,

You can use the insert mode in the shell to do what you want. It will 
format the select output as insert statements.

.mode insert
select * from table1 where ID > 1000;

This doesn't generate the transaction wrapper, or the table's create 
statement, but you can add those yourself if needed.

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dump with where clause

[Dennis Cote]

Andre du Plessis wrote:

HI, how can I use .dump or something similar but specify a where clause,
I cant see that the .dump command allows this,

Without any arguments it seems to dump the whole db, the only argument
supported is the table name,

 


I would like to be able to do something like:

.dump table1 where ID > 1000

 


I don't have a problem with the INSERT into statements, in fact I think
I prefer it because the main idea is to extract parts of the db
(revisions),

And then to be able to rebuild the db in case of corruption...

 


I know there is also the COPY command in SQL I have not really tried it
by the documentation it seems to be able to dump the table in comma or
tab delimited, but Preferably I don't want to write too much code to do
this.

 

  

Andre,

You can use the insert mode in the shell to do what you want. It will 
format the select output as insert statements.


   .mode insert
   select * from table1 where ID > 1000;

This doesn't generate the transaction wrapper, or the table's create 
statement, but you can add those yourself if needed.


HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Dump with where clause

[Andre du Plessis]

HI, how can I use .dump or something similar but specify a where clause,
I cant see that the .dump command allows this,

Without any arguments it seems to dump the whole db, the only argument
supported is the table name,

 

I would like to be able to do something like:

.dump table1 where ID > 1000

 

I don't have a problem with the INSERT into statements, in fact I think
I prefer it because the main idea is to extract parts of the db
(revisions),

And then to be able to rebuild the db in case of corruption...

 

I know there is also the COPY command in SQL I have not really tried it
by the documentation it seems to be able to dump the table in comma or
tab delimited, but Preferably I don't want to write too much code to do
this.

 

Thanks.