How to create a new table or overwrite an existing table in one command?

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

How to create a new table or overwrite an existing table in one command?

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

Re: How to create a new table or overwrite an existing table in one command?

Simon Slavin-3
On 3 Sep 2019, at 8:57pm, Peng Yu <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] How to create a new table or overwrite an existing table in one command?

Hick Gunter
In reply to this post by Peng Yu
There is only IF NOT EXISTS in the CREATE TABLE command. This assumes that you may want to keep a pre-existing table and the data it contains.

If you don't care about any old table or ist contents, just issue DROP TABLE IF EXISTS and CREATE TABLE in a single transaction.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Peng Yu
Gesendet: Dienstag, 03. September 2019 21:58
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to create a new table or overwrite an existing table in one command?

Peng Yu
In reply to this post by Simon Slavin-3
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 <[hidden email]> wrote:

> On 3 Sep 2019, at 8:57pm, Peng Yu <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to create a new table or overwrite an existing table in one command?

Rob Willett
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 <[hidden email]>
> wrote:
>
>> On 3 Sep 2019, at 8:57pm, Peng Yu <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> --
> Regards,
> Peng
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to create a new table or overwrite an existing table in one command?

Peng Yu
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 <[hidden email]>
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 <[hidden email]>
> > wrote:
> >
> >> On 3 Sep 2019, at 8:57pm, Peng Yu <[hidden email]> 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
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > --
> > Regards,
> > Peng
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to create a new table or overwrite an existing table in one command?

Simon Slavin-3
In reply to this post by Peng Yu
On 4 Sep 2019, at 11:18am, Peng Yu <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to create a new table or overwrite an existing table in one command?

Keith Medcalf
In reply to this post by Peng Yu
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to create a new table or overwrite an existing table in one command?

ingo
In reply to this post by Rob Willett
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to create a new table or overwrite an existing table in one command?

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

Re: How to create a new table or overwrite an existing table in one command?

Peng Yu
In reply to this post by Keith Medcalf
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to create a new table or overwrite an existing table in one command?

David Raymond


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

Re: How to create a new table or overwrite an existing table in one command?

Keith Medcalf
In reply to this post by Peng Yu

On Wednesday, 4 September, 2019 11:22, Peng Yu <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to create a new table or overwrite an existing table in one command?

Peng Yu
In reply to this post by David Raymond
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to create a new table or overwrite an existing table in one command?

Keith Medcalf
On Wednesday, 4 September, 2019 16:36, Peng Yu <[hidden email]> 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 <sqlite3.h>
void main(int argc, char **argv)
{
    sqlite3* db = 0;
    sqlite3_stmt* stmt = 0;
    if (sqlite3_open_v2(argv[1], &db, SQLITE_OPEN_READWRITE, NULL) == SQLITE_OK)
    {
        printf("Opened database %s\n", argv[1]);
        if (sqlite3_prepare_v2(db, "BEGIN IMMEDIATE", -1, &stmt, 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, &stmt, 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to create a new table or overwrite an existing table in one command?

Simon Slavin-3
In reply to this post by Peng Yu
On 4 Sep 2019, at 11:36pm, Peng Yu <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users