Re: [sqlite] .dump-n-reload vs. vacuum - which is better?
On Mon, 2007-02-26 at 17:05 -0800, Travis Daygale wrote: > I'll do that. I was troubled enough by that bug report and this new testing > info to be so motivated. :-) This would be handy for Tcl developers. In case you haven't noticed it yet, the SQL quote() function in func.c will help with this. It's marked as experimental, but it will help you get around some fiddly issues with strings that look like numbers and so on. Dan. > If I think about it, the copy method has "filename" as an argument, and a > dump method for the tcl driver would need that too. All other methods I > believe do not need to specify the filesystem particulars (i.e. a filename > path). > > Is that (partly?) why the copy is not tested and why there is no db1 dump > filename method? > > > -T > > > [EMAIL PROTECTED] wrote: Travis Daygale wrote: > > That is useful to know (i.e. non-testing of the shell). Thanks. > > > > Does "the core" include the tcl driver (what I use)? (It must- the driver > > is in there and the testing is done with tcl, all of this being partly why > > I chose tcl for my app- but I want to make sure I'm not somehow > > misunderstanding...) > > Everything except the COPY command is tested. > > > > > Then: > > > > How might one do the equivalent of a .dump from a trivial tcl script (and > > therefore avoid the shell)? Sort of a reverse "copy" method... and not > > the same as logging (trace). Is there a way to dump from tcl? Am I being > > stupid here- I haven't seen it... > > > > Based on the testing info, if one could do this, presumably one would have > > a (more reliable) dump/backup in a simple script. (And if it happens that > > one's sqlite code is tcl using the tcl driver, as mine is, so much the > > better in all kinds of ways including crossplatform considerations.) > > > > It would probably not require more than a few lines of TCL code to > implement a "dump" command as a TCL proc. Why don't you work something > up and post it on either the TCLers wiki or on the SQLite wiki or > both? > > -- > D. Richard Hipp > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > > > - > Expecting? Get great news right away with email Auto-Check. > Try the Yahoo! Mail Beta. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] .dump-n-reload vs. vacuum - which is better?
I'll do that. I was troubled enough by that bug report and this new testing info to be so motivated. :-) If I think about it, the copy method has "filename" as an argument, and a dump method for the tcl driver would need that too. All other methods I believe do not need to specify the filesystem particulars (i.e. a filename path). Is that (partly?) why the copy is not tested and why there is no db1 dump filename method? -T [EMAIL PROTECTED] wrote: Travis Daygale wrote: > That is useful to know (i.e. non-testing of the shell). Thanks. > > Does "the core" include the tcl driver (what I use)? (It must- the driver is > in there and the testing is done with tcl, all of this being partly why I > chose tcl for my app- but I want to make sure I'm not somehow > misunderstanding...) Everything except the COPY command is tested. > > Then: > > How might one do the equivalent of a .dump from a trivial tcl script (and > therefore avoid the shell)? Sort of a reverse "copy" method... and not the > same as logging (trace). Is there a way to dump from tcl? Am I being stupid > here- I haven't seen it... > > Based on the testing info, if one could do this, presumably one would have a > (more reliable) dump/backup in a simple script. (And if it happens that > one's sqlite code is tcl using the tcl driver, as mine is, so much the better > in all kinds of ways including crossplatform considerations.) > It would probably not require more than a few lines of TCL code to implement a "dump" command as a TCL proc. Why don't you work something up and post it on either the TCLers wiki or on the SQLite wiki or both? -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] - - Expecting? Get great news right away with email Auto-Check. Try the Yahoo! Mail Beta.
Re: [sqlite] .dump-n-reload vs. vacuum - which is better?
[EMAIL PROTECTED] wrote:
It would probably not require more than a few lines of TCL code to
implement a "dump" command as a TCL proc.
I know that copying from one database to another is not the same as
dumping, but the following might be a useful starting point. It gets the
filenames of an existing version 2 SQLite file and a desired version 3
file, and does the copy.
I am placing it in the public domain, if anyone wants to use it. It has
had a little testing on small files, but it seems to work. I hope it is
small enough that posting here is not a problem for anyone.
Gerry Snyder
# ***
# Convert an SQLite data-base file from V2 to V3
# ***
proc two2three {} {
global mainlabel
if {[set dbfilein [tk_getOpenFile]] == {} } {
return
}
if {[set dbfileout [tk_getSaveFile]] == {} } {
return
}
sqlite sqin $dbfilein
sqlite3 sqout $dbfileout
sqout eval begin
# Duplicate schema
sqin eval {SELECT sql FROM sqlite_master WHERE sql NOT NULL} {} {
sqout eval $sql
}
# Copy data
# For each table...
sqin eval {SELECT name FROM sqlite_master WHERE type='table'} {} {
set fieldnamescomma {}
# get field names and values
sqin eval "SELECT * FROM $name" fieldnames {
# but use them only if table is non-empty
if {[llength $fieldnames(*)] > 0} {
# Now $fieldnames(*) is a list of the field names
# and the rest of the $fieldnames array is values
#--set up params for insert (first time thru)
if {[string length $fieldnamescomma] == 0} {
set fieldnamescomma [join $fieldnames(*) ,]
set valuevarscomma "\$fieldnames([join $fieldnames(*)
),\$fieldnames(])"
set sqlstmnt "insert into $name\($fieldnamescomma)
values($valuevarscomma)"
}
# Now sqltrmnt is something like "insert into
table1(field1,field2) values($fieldnames(field1),$fieldnames(field2))"
sqout eval $sqlstmnt
}
}
}
sqout eval commit
}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] .dump-n-reload vs. vacuum - which is better?
Travis Daygale <[EMAIL PROTECTED]> wrote: > That is useful to know (i.e. non-testing of the shell). Thanks. > > Does "the core" include the tcl driver (what I use)? (It must- the driver is > in there and the testing is done with tcl, all of this being partly why I > chose tcl for my app- but I want to make sure I'm not somehow > misunderstanding...) Everything except the COPY command is tested. > > Then: > > How might one do the equivalent of a .dump from a trivial tcl script (and > therefore avoid the shell)? Sort of a reverse "copy" method... and not the > same as logging (trace). Is there a way to dump from tcl? Am I being stupid > here- I haven't seen it... > > Based on the testing info, if one could do this, presumably one would have a > (more reliable) dump/backup in a simple script. (And if it happens that > one's sqlite code is tcl using the tcl driver, as mine is, so much the better > in all kinds of ways including crossplatform considerations.) > It would probably not require more than a few lines of TCL code to implement a "dump" command as a TCL proc. Why don't you work something up and post it on either the TCLers wiki or on the SQLite wiki or both? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] .dump-n-reload vs. vacuum - which is better?
That is useful to know (i.e. non-testing of the shell). Thanks. Does "the core" include the tcl driver (what I use)? (It must- the driver is in there and the testing is done with tcl, all of this being partly why I chose tcl for my app- but I want to make sure I'm not somehow misunderstanding...) Then: How might one do the equivalent of a .dump from a trivial tcl script (and therefore avoid the shell)? Sort of a reverse "copy" method... and not the same as logging (trace). Is there a way to dump from tcl? Am I being stupid here- I haven't seen it... Based on the testing info, if one could do this, presumably one would have a (more reliable) dump/backup in a simple script. (And if it happens that one's sqlite code is tcl using the tcl driver, as mine is, so much the better in all kinds of ways including crossplatform considerations.) [EMAIL PROTECTED] wrote: Travis Daygale wrote: > Tangentially, but hopefully in keeping with this thread, for the 3.3.9 > release, the change log shows: > Fixed the ".dump" command in the command-line shell to show indices, triggers > and views again. > > There was apparently a bug there. I was unaffected but _apparently_ would > have been hurt had I not skipped some versions. (I have A LOT of important > triggers for foreign key handling.) > > Should we feel insecure about dump? I know sqlite3 is in development, but > overall, is the .dump command usually reliable (is there anything about the > relevent code that might make the bug reports not the full story)? Are there > recommended ways of doing backups that would be more reliable? > Good point. Everything in the SQLite core, including VACUUM, is carefully tested before each release. But this is not true of things in the shell. So one can expect the core to be much more reliable than the shell. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] - - Don't get soaked. Take a quick peak at the forecast with theYahoo! Search weather shortcut.
Re: [sqlite] .dump-n-reload vs. vacuum - which is better?
Travis Daygale <[EMAIL PROTECTED]> wrote: > Tangentially, but hopefully in keeping with this thread, for the 3.3.9 > release, the change log shows: > Fixed the ".dump" command in the command-line shell to show indices, triggers > and views again. > > There was apparently a bug there. I was unaffected but _apparently_ would > have been hurt had I not skipped some versions. (I have A LOT of important > triggers for foreign key handling.) > > Should we feel insecure about dump? I know sqlite3 is in development, but > overall, is the .dump command usually reliable (is there anything about the > relevent code that might make the bug reports not the full story)? Are there > recommended ways of doing backups that would be more reliable? > Good point. Everything in the SQLite core, including VACUUM, is carefully tested before each release. But this is not true of things in the shell. So one can expect the core to be much more reliable than the shell. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] .dump-n-reload vs. vacuum - which is better?
>From what I know, it seems plausible that a filesystem snapshot should provide a robust backup for an SQLite3 database; most modern OS's have some way to atomically grab an image of a filesystem (LVM on Linux, VSS on Windows, fssnap on Solaris, etc.). I'm no SQLite expert though, so I'd suggest you wait to hear from someone who is before you expend a lot of energy checking out this option. (Another alternative often suggested is to start a read transaction and then backup the database file before ending that transaction, to ensure that writers don't change the file while you're backing it up.) -- James > -Original Message- > From: Travis Daygale [mailto:[EMAIL PROTECTED] > Sent: Monday, February 26, 2007 1:05 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] .dump-n-reload vs. vacuum - which is better? > > Tangentially, but hopefully in keeping with this thread, for the 3.3.9 > release, the change log shows: > Fixed the ".dump" command in the command-line shell to show indices, > triggers and views again. > > There was apparently a bug there. I was unaffected but _apparently_ would > have been hurt had I not skipped some versions. (I have A LOT of > important triggers for foreign key handling.) > > Should we feel insecure about dump? I know sqlite3 is in development, but > overall, is the .dump command usually reliable (is there anything about > the relevent code that might make the bug reports not the full story)? > Are there recommended ways of doing backups that would be more reliable? > > > Thanks in advance, > > -T > > > mr sql <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: mr sql wrote: > > I found out that doing a: > > > > sqlite3 my.db .dump > mydump.sql > > rm my.db > > sqlite3 my.db < mydump.sql > > > > is faster than doing a VACUUM on my.db. > > > > Are there any advantages of doing one over the other? My goal is to > keep the database's structures in their best shape for performance and > integrity. So I want to run this process every once in a while. > > > > Have you tried running VACUUM out of the latest code in CVS? > It should be faster and it should do a better job of defragmenting > the database. > -- > D. Richard Hipp > Not sure, I am using 3.3.13 on winxp sp2, using the downloadable > (precompiled) sqlite3.exe and sqlite3.dll. Is there any 3.3.14 on the > way? For some reason, when I do my own compilations, sqlite3 (both the > exe and dll) crash randomly so I prefer to use the precompiled versions. > > jp > > > - > 8:00? 8:25? 8:40? Find a flick in no time > with theYahoo! Search movie showtime shortcut. > > > - > Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] .dump-n-reload vs. vacuum - which is better?
Tangentially, but hopefully in keeping with this thread, for the 3.3.9 release, the change log shows: Fixed the ".dump" command in the command-line shell to show indices, triggers and views again. There was apparently a bug there. I was unaffected but _apparently_ would have been hurt had I not skipped some versions. (I have A LOT of important triggers for foreign key handling.) Should we feel insecure about dump? I know sqlite3 is in development, but overall, is the .dump command usually reliable (is there anything about the relevent code that might make the bug reports not the full story)? Are there recommended ways of doing backups that would be more reliable? Thanks in advance, -T mr sql <[EMAIL PROTECTED]> wrote: [EMAIL PROTECTED] wrote: mr sql wrote: > I found out that doing a: > > sqlite3 my.db .dump > mydump.sql > rm my.db > sqlite3 my.db < mydump.sql > > is faster than doing a VACUUM on my.db. > > Are there any advantages of doing one over the other? My goal is to keep the > database's structures in their best shape for performance and integrity. So > I want to run this process every once in a while. > Have you tried running VACUUM out of the latest code in CVS? It should be faster and it should do a better job of defragmenting the database. -- D. Richard Hipp Not sure, I am using 3.3.13 on winxp sp2, using the downloadable (precompiled) sqlite3.exe and sqlite3.dll. Is there any 3.3.14 on the way? For some reason, when I do my own compilations, sqlite3 (both the exe and dll) crash randomly so I prefer to use the precompiled versions. jp - 8:00? 8:25? 8:40? Find a flick in no time with theYahoo! Search movie showtime shortcut. - Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.
Re: [sqlite] .dump-n-reload vs. vacuum - which is better?
[EMAIL PROTECTED] wrote: mr sql wrote: > I found out that doing a: > > sqlite3 my.db .dump > mydump.sql > rm my.db > sqlite3 my.db < mydump.sql > > is faster than doing a VACUUM on my.db. > > Are there any advantages of doing one over the other? My goal is to keep the > database's structures in their best shape for performance and integrity. So > I want to run this process every once in a while. > Have you tried running VACUUM out of the latest code in CVS? It should be faster and it should do a better job of defragmenting the database. -- D. Richard Hipp Not sure, I am using 3.3.13 on winxp sp2, using the downloadable (precompiled) sqlite3.exe and sqlite3.dll. Is there any 3.3.14 on the way? For some reason, when I do my own compilations, sqlite3 (both the exe and dll) crash randomly so I prefer to use the precompiled versions. jp - 8:00? 8:25? 8:40? Find a flick in no time with theYahoo! Search movie showtime shortcut.
Re: [sqlite] .dump-n-reload vs. vacuum - which is better?
mr sql <[EMAIL PROTECTED]> wrote: > I found out that doing a: > > sqlite3 my.db .dump > mydump.sql > rm my.db > sqlite3 my.db < mydump.sql > > is faster than doing a VACUUM on my.db. > > Are there any advantages of doing one over the other? My goal is to keep the > database's structures in their best shape for performance and integrity. So > I want to run this process every once in a while. > Have you tried running VACUUM out of the latest code in CVS? It should be faster and it should do a better job of defragmenting the database. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
