Re: [sqlite] How to create a new table or overwrite an existing table in one command?
On 4 Sep 2019, at 11:36pm, Peng Yu wrote: > Is there a minimal work example (in software way but not hardware > failure way) to make these extra files stick around upon closing a > sqlite3 session so that I can have a proper test case to make sure I > always delete them? Perform an INSERT operation. Have the program quit without calling _reset(), _finalize(), or closing the connection. What happens depends on whether you're using WAL mode. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to create a new table or overwrite an existing table in one command?
On Wednesday, 4 September, 2019 16:36, Peng Yu wrote:
>> Nope. If there was a problem in closing down they can hang around (which
>> is their whole point for recovery). Also if a journal mode of "persit" was
>> used. But mostly from incorrect closure.
>> So check for any -journal, -wal, or -shm files of the same name if you
>> want to obliterate a database.
>> (Am I missing any others?)
>Is there a minimal work example (in software way but not hardware
>failure way) to make these extra files stick around upon closing a
>sqlite3 session so that I can have a proper test case to make sure I
>always delete them? Thanks.
The easiest way is to just terminate without calling sqlite3_close on an open
database ... as in:
#include
void main(int argc, char **argv)
{
sqlite3* db = 0;
sqlite3_stmt* stmt = 0;
if (sqlite3_open_v2(argv[1], , SQLITE_OPEN_READWRITE, NULL) == SQLITE_OK)
{
printf("Opened database %s\n", argv[1]);
if (sqlite3_prepare_v2(db, "BEGIN IMMEDIATE", -1, , NULL) ==
SQLITE_OK)
if (sqlite3_step(stmt) == SQLITE_DONE)
{
printf("BEGIN IMMEDIATE\n");
sqlite3_finalize(stmt);
if (sqlite3_prepare_v2(db, "create table crap(crap);", -1,
, NULL) == SQLITE_OK)
if (sqlite3_step(stmt) == SQLITE_DONE)
printf("Leaving behind open transaction\n");
}
}
}
If the main file is "test.db" then you also have to delete "test.db-journal",
"test.db-shm" and "test.db-wal" if they exist.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to create a new table or overwrite an existing table in one command?
> Nope. If there was a problem in closing down they can hang around (which is > their whole point for recovery). Also if a journal mode of "persit" was > used. But mostly from incorrect closure. > > So check for any -journal, -wal, or -shm files of the same name if you want > to obliterate a database. > (Am I missing any others?) Is there a minimal work example (in software way but not hardware failure way) to make these extra files stick around upon closing a sqlite3 session so that I can have a proper test case to make sure I always delete them? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to create a new table or overwrite an existing table in one command?
On Wednesday, 4 September, 2019 11:22, Peng Yu wrote: >> If you delete the database file then make sure you also delete any other >> files that might have been associated with it, such as left over journals >> and so forth. >I never see those extra files in practice. Are they guaranteed to be >deleted automatically once an SQLite session is finished? They are deleted when the last connection to a database is closed by an sqlite3_close() call. Unless of course the program requests they stick around. And of course you can never guarantee that the CPU will not be hit by a stray dark-matter particle causing a program to abort without cleaning itself up, or that the power will never fail, or any of a number of other reasons that those extra files might be present. Do you want to accept the risk thst you will have to travel to fix something that is broken at 2:30 in the morning in the middle of a holiday while you are busy making sex on the beach when you could have with just a little tiny bit of aforethough avoided that inconvenience altogether? Then again, perhaps I am just lazy and prefer things that "just work". -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to create a new table or overwrite an existing table in one command?
> If you delete the database file then make sure you also delete any other > files that might have been associated with it, such as left over journals > and so forth. I never see those extra files in practice. Are they guaranteed to be deleted automatically once an SQLite session is finished? Nope. If there was a problem in closing down they can hang around (which is their whole point for recovery). Also if a journal mode of "persit" was used. But mostly from incorrect closure. So check for any -journal, -wal, or -shm files of the same name if you want to obliterate a database. (Am I missing any others?) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to create a new table or overwrite an existing table in one command?
> If you delete the database file then make sure you also delete any other > files that might have been associated with it, such as left over journals > and so forth. I never see those extra files in practice. Are they guaranteed to be deleted automatically once an SQLite session is finished? -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to create a new table or overwrite an existing table in one command?
Ingo, I can't answer that as we have everything in one file. I suspect that in hindsight, putting a large table in a separate file would have been advantageous. However the one of cost of dropping a 59GB table has gone and our daily pruning and vacuuming of the table is a few seconds. Hindsight is great :) I brought this up as it was a major issue for us at the time and we wanted other people to be aware that deleting a table is SQLite is not as 'cheap' as other systems. This is the first time we have found SQLite to not be as good as anything else :) Please note that this is not meant to be criticism of SQLite but rather one of the tradeoffs we know about about and make. We win for some many other things that we have no issues. Rob On 4 Sep 2019, at 12:02, ingo wrote: On 4-9-2019 12:24, Rob Willett wrote: Peng, Dropping very large tables is time consuming. Dropping a 59GB table takes quite a long time for us even on fast hardware. Dropping smaller tables is faster though. When using (and dropping) this big tables, would it be of advantage to put only that one table in a separate database and attach it when needed. There would be no need then to drop it, one could just detach and delete the db. Ingo ___ 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] How to create a new table or overwrite an existing table in one command?
On 4-9-2019 12:24, Rob Willett wrote: > Peng, > > Dropping very large tables is time consuming. Dropping a 59GB table > takes quite a long time for us even on fast hardware. Dropping smaller > tables is faster though. > When using (and dropping) this big tables, would it be of advantage to put only that one table in a separate database and attach it when needed. There would be no need then to drop it, one could just detach and delete the db. Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to create a new table or overwrite an existing table in one command?
>OK. That makes sense. I will just delete the file. It also has the benefit >of making the code simpler and avoiding using memory. If you delete the database file then make sure you also delete any other files that might have been associated with it, such as left over journals and so forth. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to create a new table or overwrite an existing table in one command?
On 4 Sep 2019, at 11:18am, Peng Yu wrote: > For now, I just delete the db file if it exists already. So that I don’t need > to call DROP TABLE IF EXISTS mytab. I guess directly deleting the db file can > be faster than the latter. Is it so? The answer will change depending on your hardware and operating system. So I cannot tell you "this way will always be faster". You will have to experiment. If the table already exists and its structure will not change (same columns, same indexes) then the fastest thing to do is usually DELETE FROM MyTable; SQLite spots the fact that there is no WHERE clause and does something special. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to create a new table or overwrite an existing table in one command?
OK. That makes sense. I will just delete the file. It also has the benefit of making the code simpler and avoiding using memory. On Wed, Sep 4, 2019 at 5:25 AM Rob Willett wrote: > Peng, > > Dropping very large tables is time consuming. Dropping a 59GB table > takes quite a long time for us even on fast hardware. Dropping smaller > tables is faster though. > > Not sure what size tables you have but something to think about. We > experimented with new tables and changing old tables and for us, > dropping the table and creating a new one was the fastest method but > still took 40-60 mins from memory. > > Rob > > On 4 Sep 2019, at 11:18, Peng Yu wrote: > > > For now, I just delete the db file if it exists already. So that I > > don’t > > need to call DROP TABLE IF EXISTS mytab. I guess directly deleting the > > db > > file can be faster than the latter. Is it so? > > > > On Tue, Sep 3, 2019 at 3:06 PM Simon Slavin > > wrote: > > > >> On 3 Sep 2019, at 8:57pm, Peng Yu wrote: > >> > >>> If I try to create a table that already exists, sqlite will give me > >>> an > >> error. Is there way to issue one command to create a table, but if > >> there is > >> already a table with the same name, drop it then create the new > >> table? > >> Thanks. > >> > >> Assuming that the new table has a different structure to the old one, > >> do > >> it in two commands: > >> > >> DROP TABLE IF EXISTS MyTable; > >> CREATE TABLE MyTable ...; > >> > >> Neither of those commands will generate an error. > >> > >> If you are sure that the new table has the same structure as the old > >> one, > >> you can do > >> > >> CREATE TABLE IF NOT EXISTS MyTable ...; > >> DELETE FROM MyTable; > >> ___ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > -- > > Regards, > > Peng > > ___ > > 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 > -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to create a new table or overwrite an existing table in one command?
Peng, Dropping very large tables is time consuming. Dropping a 59GB table takes quite a long time for us even on fast hardware. Dropping smaller tables is faster though. Not sure what size tables you have but something to think about. We experimented with new tables and changing old tables and for us, dropping the table and creating a new one was the fastest method but still took 40-60 mins from memory. Rob On 4 Sep 2019, at 11:18, Peng Yu wrote: For now, I just delete the db file if it exists already. So that I don’t need to call DROP TABLE IF EXISTS mytab. I guess directly deleting the db file can be faster than the latter. Is it so? On Tue, Sep 3, 2019 at 3:06 PM Simon Slavin wrote: On 3 Sep 2019, at 8:57pm, Peng Yu wrote: If I try to create a table that already exists, sqlite will give me an error. Is there way to issue one command to create a table, but if there is already a table with the same name, drop it then create the new table? Thanks. Assuming that the new table has a different structure to the old one, do it in two commands: DROP TABLE IF EXISTS MyTable; CREATE TABLE MyTable ...; Neither of those commands will generate an error. If you are sure that the new table has the same structure as the old one, you can do CREATE TABLE IF NOT EXISTS MyTable ...; DELETE FROM MyTable; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Regards, Peng ___ 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] How to create a new table or overwrite an existing table in one command?
For now, I just delete the db file if it exists already. So that I don’t need to call DROP TABLE IF EXISTS mytab. I guess directly deleting the db file can be faster than the latter. Is it so? On Tue, Sep 3, 2019 at 3:06 PM Simon Slavin wrote: > On 3 Sep 2019, at 8:57pm, Peng Yu wrote: > > > If I try to create a table that already exists, sqlite will give me an > error. Is there way to issue one command to create a table, but if there is > already a table with the same name, drop it then create the new table? > Thanks. > > Assuming that the new table has a different structure to the old one, do > it in two commands: > > DROP TABLE IF EXISTS MyTable; > CREATE TABLE MyTable ...; > > Neither of those commands will generate an error. > > If you are sure that the new table has the same structure as the old one, > you can do > > CREATE TABLE IF NOT EXISTS MyTable ...; > DELETE FROM MyTable; > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to create a new table or overwrite an existing table in one command?
On 3 Sep 2019, at 8:57pm, Peng Yu wrote: > If I try to create a table that already exists, sqlite will give me an error. > Is there way to issue one command to create a table, but if there is already > a table with the same name, drop it then create the new table? Thanks. Assuming that the new table has a different structure to the old one, do it in two commands: DROP TABLE IF EXISTS MyTable; CREATE TABLE MyTable ...; Neither of those commands will generate an error. If you are sure that the new table has the same structure as the old one, you can do CREATE TABLE IF NOT EXISTS MyTable ...; DELETE FROM MyTable; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to create a new table or overwrite an existing table in one command?
Hi, If I try to create a table that already exists, sqlite will give me an error. Is there way to issue one command to create a table, but if there is already a table with the same name, drop it then create the new table? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
