SQLite Forum

.dump feature request
Login

.dump feature request

(1.2) By Tony Papadimitriou (tonyp) on 2020-04-27 08:10:33 edited from 1.1 [link] [source]

Could the .dump command first drop the table before creating it?

So, instead of

BEGIN TRANSACTION;
...
CREATE TABLE xxx(s);
...

this:

BEGIN TRANSACTION;
...
DROP TABLE IF EXISTS xxx;
CREATE TABLE xxx(s);
...

This is because a dump is often used to transfer changes from one database (e.g. a single table) to another, not only for a full rebuild of a database.

The way it currently works, only data can be transferred but assuming table structure is unchanged.

But if the table structure also changes, the transfer fails unless one first manually deletes the destination table before feeding the .dump output to the destination database.

Thank you

(2) By Ryan Smith (cuz) on 2020-04-27 08:43:15 in reply to 1.2 [source]

I want to +1 this request, except it needs to be a switchable feature in the .dump command, maybe adding something like --drop-tables as a switch.

I sometimes do want the dump import to fail very noisily if it gets run by accident or twice in a row or as part of a database repair/upgrade etc. etc.

(3) By Tony Papadimitriou (tonyp) on 2020-04-27 09:08:59 in reply to 2 [link] [source]

A switch for the less likely use case to avoid having to type it most of the time.

For me at least, the most common use (only case actually) is the drop is either required or indifferent (e.g. same table structure).

The same for views.

(4.1) Originally by anonymous with edits by Stephan Beal (stephan) on 2022-05-29 16:31:24 from 4.0 in reply to 1.2 [link] [source]

You can use :

sed 's/CREATE TABLE IF NOT EXISTS \(.*\) (/DROP TABLE IF EXISTS \1;\n&/' dumpdb.sql

it does the job

(5.1) Originally by anonymous with edits by Stephan Beal (stephan) on 2022-05-29 16:32:07 from 5.0 in reply to 4.0 [link] [source]

The code cleaning in the forum made disappear the escapes (anti-slashes) in front of the opening and closing parenthesis and in front of the 1 which represents the value sent by the caturing parenthesis...

I don't know if as a moderator you can correct this, because as written, the code cannot work.

thanks

[[Edit: it's been edited to wrap the shell part in triple backticks. Tap the "source" link of the post to see how that was done.]]